CREATE VIEW product_stocks AS
SELECT purchase_details.product_id, 
	purchase_details.productcategory_id,
	purchase_details.unit_id,
	products.re_order_label,
 SUM(purchase_details.now_stock) as product_stock
 FROM purchase_details,products WHERE purchase_details.now_stock > 0 and purchase_details.product_id = products.id 
GROUP by purchase_details.product_id;

CREATE VIEW purchase_stocks AS
 SELECT purchase_details.*, products.product_name FROM products,purchase_details 
	WHERE purchase_details.now_stock > 0
		AND purchase_details.product_id = products.id



UPDATE `purchase_details` AS `dest`, (SELECT quantity FROM purchase_details WHERE square_foot = 0 ) AS `src` SET `dest`.`square_foot` = `src`.`quantity` WHERE `dest`.`square_foot` = 0


/*-----26-01-2023---------*/
=> SELECT * FROM `purchase_details` WHERE purchase_date >= '2023-01-01' AND purchase_date <= '2023-01-28' AND productcategory_id = 20 
=> SELECT p.product_name,p.code,c.name AS category_name,pd.rate,SUM(pd.quantity) AS quantity,SUM(pd.square_foot) AS square_foot,SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories c WHERE p.id = pd.product_id AND pd.productcategory_id = 20 AND pd.purchase_date >= '2023-01-01' AND pd.purchase_date <= '2023-01-28' GROUP BY pd.product_id 
=> SELECT p.product_name,p.code,c.name AS category_name,pd.purchase_date,pd.rate,SUM(pd.quantity) AS quantity,SUM(pd.square_foot) AS square_foot,SUM(pd.now_stock) AS now_stock FROM `purchase_details` AS pd, products AS p, productcategories c WHERE p.id = pd.product_id AND pd.productcategory_id = 20 AND pd.purchase_date >= '2023-01-01' AND pd.purchase_date <= '2023-01-28' GROUP BY pd.product_id,pd.purchase_date ORDER BY pd.purchase_date 