/*delete product with quantity = 0 and not changed from certain date */

DELETE FROM `product` WHERE productId in ( select `productid` from storedetail WHERE `productquantity` = 0 and `storedetaildate` <= "2018-10-20");
DELETE FROM `productunit` WHERE productid in ( select `productid` from storedetail WHERE `productquantity` = 0 and `storedetaildate` <= "2018-10-20");
DELETE FROM `storedetail` WHERE `productquantity` = 0 and `storedetaildate` <= "2018-10-20";




/*remove the deleted products data from tables*/

DELETE FROM `productunit` WHERE productid in ( select `productId` from `product` WHERE conditions= 2);
DELETE FROM `storedetail` WHERE productid in ( select `productId` from `product` WHERE conditions= 2);
DELETE FROM `product` WHERE conditions= 2;



-- remove first number of barcode from the left if parcode length = 6
UPDATE product 
SET parcode = SUBSTR(parcode,2) 
WHERE CHAR_LENGTH(parcode)  = 6

-- remove first zero of barcode from the left
UPDATE product 
SET parcode = SUBSTR(parcode,2)
WHERE `parcode` LIKE '0%'; 


-- make sure typeclientid start with , and end with , to make search on it with like is ok
UPDATE client SET typeclientid = Concat(',', typeclientid) where typeclientid NOT LIKE ',%';
UPDATE client SET typeclientid = Concat(typeclientid, ',') where typeclientid NOT LIKE '%,';


-- change lenght combination of productid,sizeid,colrid that makes parcode =>example make parcode like 000018000200003 be like 00180203 =>means control no of digits that represent each id
update sizecolorstoredetail set parcode = CONCAT(lpad(productid, 4, 0),lpad(sizeid, 2, 0),lpad(colorid, 2, 0));