VbzCart/docs/queries/discarded

v_items

Details

• Used by: clsItemsExt (MS Access) -- as of 2008-11-30, this class does not seem to be in use anymore. It probably was not included in the big rescue operation when the old order DB developed some unopenable modules.

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW `v_items` AS

 SELECT
   i.ID,
   i.CatNum,
   i.isForSale,
   i.isMaster,
   i.qtyInStock,
   i.isInPrint,
   i.isCloseOut,
   i.isPulled,
   i.isDumped,
   i.ID_Title,
   i.ID_ItTyp,
   i.ID_ItOpt,
   i.ItOpt_Descr,
   i.ID_ShipCost,
   i.PriceBuy,
   i.PriceSell,
   i.PriceList,
   i.Supp_CatNum,
   io.Sort AS OptSort
 FROM (
   `cat_items` AS `i` LEFT JOIN 
   `cat_ioptns` AS `io` ON
     ((`i`.`ID_ItOpt` = `io`.`ID`)));</mysql>

<section end=sql />

qryRstkReq_wItem_info

Details

• Status: not being used
• History:
  • 2008-11-21 Created for new restock process
  • 2008-12-03 officially abandoned

SQL

<mysql>CREATE OR REPLACE VIEW qryRstkReq_wItem_info AS SELECT

 rqi.*,
 i.ID_Title,
 r.WhenCreated,
 r.WhenOrdered

FROM

 (rstk_req_item AS rqi
 LEFT JOIN cat_items AS i ON rqi.ID_Itme=i.ID)
 LEFT JOIN rstk_req AS rq ON rqi.ID_Restock=rq.ID;</mysql>

qryCbx_Restocks_byStatus

Details

• Status: deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.
• Requires: Template:Vbzcart/table, Template:Vbzcart/table
• Used by: Template:Vbzcart/query

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

• Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
• Requires: Template:Vbzcart/table
• Used by: #qryCbx_Restocks

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>