Woozle: Woozle moved page VbzCart/pieces/catalog/local/building/2008 to VbzCart/pieces/catalog/building/2008 over redirect: this actually applies to both local and supplier

2016-02-01T02:15:21Z

Woozle moved page VbzCart/pieces/catalog/local/building/2008 to VbzCart/pieces/catalog/building/2008 over redirect: this actually applies to both local and supplier

← Older revision	Revision as of 02:15, 1 February 2016
(No difference)

Woozle: Woozle moved page VbzCart/pieces/catalog/building/2008 to VbzCart/pieces/catalog/local/building/2008: disambiguation

2016-01-08T17:53:04Z

Woozle moved page VbzCart/pieces/catalog/building/2008 to VbzCart/pieces/catalog/local/building/2008: disambiguation

← Older revision	Revision as of 17:53, 8 January 2016
(No difference)

Woozle: Woozle moved page VbzCart/catalog/building/2008 to VbzCart/pieces/catalog/building/2008

2015-01-25T19:14:59Z

Woozle moved page VbzCart/catalog/building/2008 to VbzCart/pieces/catalog/building/2008

← Older revision	Revision as of 19:14, 25 January 2015
(No difference)

Woozle: extracted from "/building" page because it is now obsolete

2009-12-10T21:45:14Z

extracted from "/building" page because it is now obsolete

New page

==History==
For the [[../2009|2009]] version, the stored queries were scrapped in favor of executing their SQL directly from PHP code.
==Process==
This is only a draft, as the process is still being worked out. Right now this process does not "recycle" junked records in cat_items; perhaps that was a bad idea to begin with, or perhaps some other use can be found for junked records that isn't part of catalog-building. There probably should be some process which removes junked records from use so they aren't taking up space and CPU cycles.
# Update the list of all catalog-based items ([calc Items Updates saved])
#* [[#(Step 1) qryVb_Items_updates_append]] -- Add any new items to [calc Items Updates saved]
#** [[#(Step 1a) qryVb_Items_updates_new]] -- in-print items that are new
#*** [[#(Step 1aa) qryVb_Items_updates]] -- all the items currently available from suppliers (in print)
#** [[#(Step 1b) qryVb_Items_updates_copy_fields]]
# For all items in [calc Items Updates saved] where ID_Item IS NULL, create new records in cat_items and update the corresponding records in [calc Items Updates saved]
#* [[#(Step 2) qryVb_Items_results_append]] -- adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query.
#** [[#(Step 2a) qryVb_Items_results]]
# Fill in any blank ID_Item fields in [calc Items Updates saved] records:
#* [[#(Step 3) qryVb_Items_updates_index_new]] -- plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general).
# Copy calculated item data over to [cat_items]:
#* [[#(Step 4) qryVb_Items_results_update]]
# Final flag updates:
#* [[#(Step 5) qryVb_Items_update_final]]
#** Clear isInPrint flag for cat_items not found in calculated in-print listing
#** Set isForSale for cat_items with stock for sale
#** Update stock quantities for all cat_items
# Housekeeping: mark table as updated so cached tables get recalculated
#* [[#(Step 6) qryVb_Items_mark_updated]]

==SQL - queries - 2008==
2008-03-15: Completely rewriting the building sequence again...

* {{vbzcart/query|qryCtg_src_sub}}
* {{vbzcart/query|qryCtg_src}}
* {{vbzcart/query|qryCtg_Items_forUpdJoin}}
* {{vbzcart/query|qryCtg_Upd_join}}
* {{vbzcart/query|qryCtg_src_dups}}
* {{vbzcart|proc|doCtgUpdate}}: the main building process - details are documented there too
* {{vbzcart/query|qryCtgCk_dup_keys}}: looks for duplicate IDS_Items

==SQL - queries==
These are from the MS Access 97 version, gradually being honed down for migration to MySQL
===(Step 1a) qryVb_Items_updates_new===
<sql>SELECT c.*
FROM qryCtg_Items_updates_joinable AS c LEFT JOIN ctg_updates AS s
ON c.IDS_Item=s.IDS_Item
WHERE s.IDS_Item IS NULL;</sql>

===(Step 1) qryVb_Items_updates_append===
<sql>INSERT INTO ctg_updates (
ID_Item,
IDS_Item,
CatNum,
CatSfx,
ID_Title,
ID_CTG_Group,
ID_CTG_Title,
ID_CTG_Item,
ID_ItTyp,
ID_ItOpt )
SELECT
0 AS ID_Item,
IDS_Item,
CatNum,
CatSfx,
ID_Title,
ID_TGroup,
ID_TGTitle,
ID_TGItem,
ID_ItTyp,
ID_ItOpt
FROM qryVb_Items_Updates_new;
</sql>
===(Step 2) qryVb_Items_results_append===
This adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query.
<sql>INSERT INTO cat_items (
CatNum,
isForSale,
isMaster,
isInPrint,
isCloseOut,
ID_ItTyp,
ID_ItOpt,
ItOpt_Descr,
ItOpt_Sort,
ID_ShipCost,
PriceBuy,
PriceSell,
PriceList )
SELECT
r.CatNum,
r.isForSale,
FALSE AS isMaster,
r.isInPrint,
r.isCloseOut,
r.ID_ItTyp,
r.ID_ItOpt,
r.ItOpt_Descr,
r.ItOpt_Sort,
r.ID_ShipCost,
r.PriceBuy,
r.PriceSell,
r.PriceList
FROM qryCtg_Items_updates AS r LEFT JOIN cat_items AS i ON r.ID_Item=i.ID
WHERE i.ID IS NULL;
</sql>

===(Step 3) qryVb_Items_updates_index_new===
This plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general).
<sql>UPDATE
ctg_updates AS s LEFT JOIN
cat_items AS i
ON i.CatNum=s.CatNum
SET
s.ID_Item = i.ID,
isForSale = FALSE,
isInPrint = FALSE
WHERE
s.ID_Item IS NULL;
</sql>

===(Step 4) qryVb_Items_results_update===
<sql>UPDATE
qryCtg_build AS r LEFT JOIN
cat_items AS i
ON
r.ID_Item=i.ID
SET
i.CatNum = r.CatNum,
i.isForSale = r.isForSale,
i.isInPrint = r.isInPrint,
i.isCloseOut = r.isCloseOut,
i.isPulled = FALSE,
i.isDumped = FALSE,
i.ID_ItTyp = r.ID_ItTyp,
i.ID_ItOpt = r.ID_ItOpt,
i.ItOpt_Descr = iif(r.ItOpt_Descr IS NULL,i.ItOpt_Descr,r.ItOpt_Descr),
i.ItOpt_Sort = r.ItOpt_Sort,
i.ID_ShipCost = r.ID_ShipCost,
i.PriceBuy = r.PriceBuy,
i.PriceSell = r.PriceSell,
i.PriceList = r.PriceList,
i.GrpCode = r.GrpCode,
i.GrpDescr = r.GrpDescr,
i.GrpSort = r.GrpSort,
i.CatSfx = r.CatSfx
WHERE r.ID_Item IS NOT NULL;
</sql>

Changes:
* Must clear isPulled and isDumped, in case pulled items are returned to service
===(Step 5) qryVb_Items_update_final===
* Clear isInPrint flag for cat_items not found in calculated in-print listing
* Set isForSale for cat_items with stock for sale
* Update stock quantities for all cat_items
<sql>UPDATE
(cat_items AS i LEFT JOIN qryCtg_build AS r ON r.ID_Item=i.ID)
LEFT JOIN v_stk_items_remaining AS s ON s.ID_Item=i.ID
SET
i.isInPrint = nz(r.isInPrint),
i.isCloseOut = nz(r.isCloseOut),
i.isForSale = nz(r.isInPrint)
OR nz(r.isCloseOut)
OR (nz(s.QtyForSale) > 0),
i.qtyInStock = nz(s.QtyForSale);
</sql>
===(Step 6) qryVb_Items_mark_updated===
<sql>UPDATE
data_tables
SET WhenUpdated=Now()
WHERE Name="cat_items"</sql>

VbzCart/pieces/catalog/building/2008 - Revision history

Woozle: Woozle moved page VbzCart/pieces/catalog/local/building/2008 to VbzCart/pieces/catalog/building/2008 over redirect: this actually applies to both local and supplier

Woozle: Woozle moved page VbzCart/pieces/catalog/building/2008 to VbzCart/pieces/catalog/local/building/2008: disambiguation

Woozle: Woozle moved page VbzCart/catalog/building/2008 to VbzCart/pieces/catalog/building/2008

Woozle: extracted from "/building" page because it is now obsolete