|
|
| Line 1: |
Line 1: |
| ==v_items== | | ==More Recent== |
| ===Details===
| | * {{vbzcart/query|qryCat_Items}} - no longer even works |
| * '''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.
| | ==Older== |
| ===SQL===
| | * [[/v_items]] |
| <section begin=sql /><mysql>CREATE OR REPLACE VIEW `v_items` AS
| | * [[/qryRstkReq_wItem_info]] |
| SELECT
| | * [[/qryCbx_Restocks_byStatus]] |
| i.ID,
| | * [[/qryCbx_Restocks_byPurchOrd]] |
| i.CatNum,
| | * [[/qryCbx_Restocks]] |
| 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''': {{vbzcart/table|core_restocks}}, {{vbzcart/table|cat_supp}}
| |
| * '''Used by''': {{vbzcart/query|qryCbx_Restocks}}
| |
| ===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''': deleted (uses old restock table, and no longer works) | |
| * '''Requires''': {{vbzcart/table|core_restocks}} | |
| * '''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''': deleted (ultimately depends on old restock table, so doesn't work 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>
| |
