VbzCart/docs/queries/deprecated

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Revision as of 00:27, 4 December 2008 by imported>Woozle (→‎qryFrm_RestockLines_byItem: qryRstkLines_en_route)
Jump to navigation Jump to search

v_titles - latest

Deprecated -- use VbzCart/docs/queries/qryCat_Titles_Item_stats instead -- this query still depends on cached _depts table. To be eliminated later. <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_titles AS SELECT 

 ID_Title,
 ID_Dept,
 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 SUM(i.qtyInStock) AS qtyInStock,
 MIN(i.PriceSell) AS currMinSell,
 MAX(i.PriceSell) AS currMaxSell,
 d.ID_Supp,
 UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
 LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 t.Name

FROM (cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN _depts AS d ON t.ID_dept=d.ID GROUP BY i.ID_Title;</mysql> <section end=sql />

v_titles - older

Old version which depends on 2 cached tables (trying to minimize usage of these): <mysql>CREATE OR REPLACE VIEW v_titles AS 

 SELECT
   t.*,
   tx.ID_Supp,
   tx.CatNum,
   tx.CatWeb,
   tx.cntForSale,
   tx.cntInPrint,
   tx.qtyInStock
     FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>

v_rstk_lines_wItemInfo

  • Status: deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.

<mysql>CREATE OR REPLACE VIEW v_rstk_lines_wItemInfo AS SELECT 

 ri.*,
 i.ID_Title,
 IFNULL(r.WhenOrdered,r.WhenCreated) AS WhenDone

FROM 

 (rstk_lines AS ri
 LEFT JOIN cat_items AS i ON ri.ID_Item=i.ID)
 LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.ID;</mysql>

qryCbx_Restocks_byStatus

Details

SQL

<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks_byStatus AS SELECT 

 r.ID,
 CAST(
   CONCAT(
     CAST(COALESCE(
       CONCAT('X ',DATE_FORMAT(r.WhenKilled,'%Y-%m-%d')),
       CONCAT('R ',DATE_FORMAT(r.WhenReceived,'%Y-%m-%d')),
       CONCAT('O ',DATE_FORMAT(r.WhenOrdered,'%Y-%m-%d')),
       CONCAT('D ',DATE_FORMAT(r.WhenDebited,'%Y-%m-%d')),
       CONCAT('C ',DATE_FORMAT(r.WhenCreated,'%Y-%m-%d'))
     ) AS BINARY),
     ' ',
     s.CatKey,
     IFNULL(CONCAT(' [',r.PurchOrdNum,']'),)
   ) AS CHAR
 ) AS Descr,
 NOT IsLocked AS IsOpen,
 r.WhenOrdered,
 r.WhenReceived,
 r.WhenConfirmed,
 r.ID_Supplier

FROM core_restocks AS r LEFT JOIN cat_supp AS s ON r.ID_Supplier=s.ID ORDER BY COALESCE(r.WhenKilled,r.WhenReceived,r.WhenOrdered,r.WhenDebited,r.WhenCreated) DESC;</mysql>

qryCbx_Restocks_byPurchOrd

Details

SQL

<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks_byPurchOrd AS SELECT 

 r.ID,
 CAST(r.PurchOrdNum AS CHAR) AS Descr,
 NOT IsLocked AS IsOpen,
 r.WhenOrdered,
 r.WhenReceived,
 r.WhenConfirmed,
 r.ID_Supplier

FROM core_restocks AS r WHERE r.PurchOrdNum IS NOT NULL ORDER BY COALESCE(r.WhenKilled,r.WhenReceived,r.WhenOrdered,r.WhenDebited,r.WhenCreated) DESC;</mysql>

qryCbx_Restocks

Details

  • Status: deprecated (ultimately depends on old restock table); can probably be deleted, since nothing should be using those tables anymore.
  • Notes: MySQL gives a "query cancelled" error when running this SQL to create the view, but actually it creates it just fine.

SQL

<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks AS 

 SELECT * FROM qryCbx_Restocks_byStatus

UNION 

 SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>

qryFrm_RestockLines

Details

  • Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
  • Notes: A lot of these fields were added back when this query was still the data source for qryFrm_RestockLines_byItem; this is no longer the case. Probably the only customer for this query is the Restock Items entry form, so any fields not needed there can probably be done away with.

SQL

<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines AS SELECT 

 ri.ID,
 ri.ID_Restock,
 ri.ID_Item,
 ci.CatNum,
 ci.Supp_CatNum,
 ci.PriceSell,
 ci.QtyMin_Stk,
 o.Number AS OrderName,
 ci.Descr AS ItemDescr,
 o.WhenNeeded,
 If(t.RstkMin = 1, , t.RstkMin) AS RstkMinStr,
 ri.QtyOrd,
 ri.QtyNeed,
 ri.QtyExp,
 ri.InvcLineNo,
 ri.InvcQtyOrd,
 ri.InvcQtySent,
 ri.isGone,
 ri.QtyRecd,
 ri.QtyFiled,
 ri.CostExpPer,
 ri.CostInvPer,
 ri.CostInvTot,
 ri.CostActTot,
 ri.CostActBal,
 ri.Notes

FROM ( 

 (rstk_lines AS ri
   LEFT JOIN core_orders AS o ON ri.ID_Order=o.ID)
 LEFT JOIN qryCbx_Items_data AS ci ON ri.ID_Item=ci.ID)

LEFT JOIN cat_titles AS t ON ci.ID_Title=t.ID;</mysql> <section end=sql />

qryFrm_RestockLines_byItem

Details

  • Notes: Basically the same as #qryFrm_RestockLines, but grouped by ID_Item and with more Item-related fields
  • Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.

SQL

<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines_byItem AS SELECT 

 CONCAT(ri.ID_Item,'-',ri.ID_Restock) AS Key_RstkItem,
 ri.ID_Item,
 ri.ID_Restock,
 i.Descr AS ItemDescr,
 i.CatNum,
 i.Supp_CatNum,
 SUM(IFNULL(ri.QtyOrd,0)) AS QtyOrd,
 SUM(IFNULL(ri.QtyExp,0)) AS QtyExp,
 ri.CostExpPer,
 i.PriceSell,
 i.QtyMin_Stk

FROM rstk_lines AS ri LEFT JOIN qryCbx_Items_data AS i ON ri.ID_Item=i.ID GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk ORDER BY i.CatNum;</mysql>

qryRstkLines_en_route

Details

  • Deprecated: this uses the old restock tables; looks like I edited it when I meant to be editing one of the newer ones, so it's not even accurate
  • Returns: Lines from restocks which exist but have not yet received or cancelled. It's assumed that any restocks just sitting around (not ordered but not cancelled) are being prepared and will be used for an actual order.

SQL

<mysql>CREATE OR REPLACE VIEW qryRstkLines_en_route AS SELECT ri.* FROM rstk_lines AS ri LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.ID WHERE (r.WhenReceived IS NULL) AND (r.WhenKilled IS NULL) AND (rq.WhenOrphaned IS NULL);</mysql>

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/deprecated&oldid=5144"