Difference between revisions of "VbzCart/queries/discarded"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
m (→‎qryCbx_Restocks: updated status)
(→‎More Recent: many more queries no longer in database)
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
==v_items==
+
==More Recent==
−
===Details===
+
The pages for these still need to be moved:
−
* '''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.
+
* {{l/vc/query|qryCat_Items}} - no longer even works
−
===SQL===
+
* {{l/vc/query|qryItms_to_restock_w_info}} - wasn't even migrated to current server
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW `v_items` AS
+
* {{l/vc/query|qryCtg_Titles_active}}
−
  SELECT
+
* {{l/vc/query|qryCtg_src}}
−
    i.ID,
+
* {{l/vc/query|qryCtg_src_sub}}
−
    i.CatNum,
+
* {{l/vc/query|qryItems_needed_forStock}}
−
    i.isForSale,
+
* {{l/vc/query|qryRstkItms_active}}
−
    i.isMaster,
+
** {{l/vc/query|qryRstkItms_expected}}
−
    i.qtyInStock,
+
*** {{l/vc/query|qryRstkItms_expected_byItem}} - grouped by ID_Item
−
    i.isInPrint,
+
* {{l/vc/query|qryStk_History}} - combined current stock history and {{l/vc/table|stk_history_legacy}} data
−
    i.isCloseOut,
+
* {{l/vc/query|qryStock_containers}} - generated IDS codes
−
    i.isPulled,
+
* {{l/vc/query|qryStock_forOpenOrders}}
−
    i.isDumped,
+
* {{l/vc/query|qryStock_byOpt_andType}}
−
    i.ID_Title,
+
* {{l/vc/query|qryStock_by_Opt_Type}}
−
    i.ID_ItTyp,
+
* {{l/vc/query|qryStock_by_Supp_Type_Opt}} - unused (and should be Supp_Opt_Type)
−
    i.ID_ItOpt,
+
* {{l/vc/query|qryStock_Titles_most_recent}}
−
    i.ItOpt_Descr,
+
* {{l/vc/query|qryStkItms_for_sale_wItem_data}}
−
    i.ID_ShipCost,
+
* {{l/vc/query|qryStk_byItem_byBin_wInfo}} - adds information to {{l/vc/query|qryStk_byItem_byBin}}
−
    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==
+
==Older==
−
===Details===
+
* [[/v_items]]
−
* '''Status''': deleted (ultimately depends on old restock table, so doesn't work anymore)
+
* [[/qryRstkReq_wItem_info]]
−
* '''Notes''': MySQL gives a "query cancelled" error when running this SQL to create the view, but actually it creates it just fine.
+
* [[/qryCbx_Restocks_byStatus]]
−
===SQL===
+
* [[/qryCbx_Restocks_byPurchOrd]]
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks AS
+
* [[/qryCbx_Restocks]]
−
  SELECT * FROM qryCbx_Restocks_byStatus
 
−
UNION
 
−
  SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>
 

Latest revision as of 23:12, 3 March 2016

More Recent

The pages for these still need to be moved:

Older

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/discarded&oldid=21771"