VbzCart/docs/queries/discarded: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
m (→‎qryCbx_Restocks: updated status)
imported>Woozle
(moved each query into its own page, for easier management)
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>

Revision as of 21:59, 2 March 2016

More Recent

Older

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