VbzCart/queries/deprecated - Revision history

Woozle: moved qryCbx_Restosks* series to "discarded"

2008-12-22T17:44:00Z

moved qryCbx_Restosks* series to "discarded"

@@ Line 40: / Line 40: @@
    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==
 ==qryFrm_RestockLines==
 ===Details===

Woozle: /* Details */ dropped

2008-12-04T21:50:46Z

Details: dropped

← Older revision		Revision as of 21:50, 4 December 2008
Line 161:		Line 161:
	* '''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		* '''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.		* '''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.
			* '''History''':
			** '''2008-12-04''' Dropped from database

	===SQL===		===SQL===
	<mysql>CREATE OR REPLACE VIEW qryRstkLines_en_route AS		<mysql>CREATE OR REPLACE VIEW qryRstkLines_en_route AS

Woozle: /* qryFrm_OrdLines_to_restock */ and yet more obsolete stock queries

2008-12-04T02:43:28Z

qryFrm_OrdLines_to_restock: and yet more obsolete stock queries

@@ Line 232: / Line 232: @@
 FROM qryOrdLines_to_restock AS qi
    LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item;</mysql>
 <section end=sql />

Woozle: /* qryOrdLines_ord_status */ more stock queries which use old restock tables

2008-12-04T02:24:35Z

qryOrdLines_ord_status: more stock queries which use old restock tables

@@ Line 192: / Line 192: @@
     LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID)
   LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>
 <section end=sql />

Woozle: /* qryRstkLines_en_route */ qryOrdLines_ord_status

2008-12-04T02:17:59Z

qryRstkLines_en_route: qryOrdLines_ord_status

← Older revision		Revision as of 02:17, 4 December 2008
Line 166:		Line 166:
	FROM rstk_lines AS ri LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.ID		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>		WHERE (r.WhenReceived IS NULL) AND (r.WhenKilled IS NULL) AND (rq.WhenOrphaned IS NULL);</mysql>
			==qryOrdLines_ord_status==
			===Details===
			* '''Status''': Deprecated -- qryRstkLines_en_route uses old restock table
			* '''Requires''': {{vbzcart/query\|qryOrdLines_open}}, {{vbzcart/query\|qryStk_lines_remaining_forSale}}, {{vbzcart/query\|qryRstkLines_en_route}}
			* '''Used by''':
			===SQL===
			<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS
			SELECT
			i.ID AS ID_Item,
			i.CatNum,
			qoi.ID AS ID_OrdLine,
			qoi.ID_Order,
			qsr.ID AS ID_Stock,
			qri.ID AS ID_RstkItem,
			IFNULL(qoi.QtyOrd,0) AS QtyOrd,
			IFNULL(qoi.QtyDone,0) AS QtyDone,
			IFNULL(qri.QtyExp IS NULL,IFNULL(qri.QtyOrd,0)) AS QtyOnRstk,
			IFNULL(qsr.QtyForShip,0) AS QtyOnHand,
			i.QtyMin_Stk AS QtyMin,
			qoi.WhenOldestOrder,
			qoi.WhenSoonestDue
			FROM
			(
			(cat_items AS i LEFT JOIN qryOrdLines_open AS qoi ON qoi.ID_Item=i.ID)
			LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID)
			LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>
			<section end=sql />

Woozle: /* qryFrm_RestockLines_byItem */ qryRstkLines_en_route

2008-12-04T00:27:45Z

qryFrm_RestockLines_byItem: qryRstkLines_en_route

← Older revision		Revision as of 00:27, 4 December 2008
Line 157:		Line 157:
	GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk		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>		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>

Woozle: /* qryCbx_Restocks */ two queries using rstk_lines

2008-12-04T00:10:41Z

qryCbx_Restocks: two queries using rstk_lines

← Older revision		Revision as of 00:10, 4 December 2008
Line 96:		Line 96:
	UNION		UNION
	SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>		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>

Woozle: qryCbx queries based on (obsolete) core_restocks

2008-12-04T00:05:50Z

qryCbx queries based on (obsolete) core_restocks

← Older revision		Revision as of 00:05, 4 December 2008
Line 40:		Line 40:
	LEFT JOIN cat_items AS i ON ri.ID_Item=i.ID)		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>		LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.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''': deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
			* '''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''': 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>

Woozle: v_rstk_lines_wItemInfo

2008-12-03T23:50:29Z

v_rstk_lines_wItemInfo

← Older revision		Revision as of 23:50, 3 December 2008
Line 29:		Line 29:
	tx.qtyInStock		tx.qtyInStock
	FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>		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>

Woozle: moved from single-page listing

2008-11-30T18:28:31Z

moved from single-page listing

New page

==v_titles - latest==
'''Deprecated''' -- use [[../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>