/*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%'; 


/* to sum quantity of product from two stores into one */
/* 1,2 : are ids of stores to sum their products*/ 
/* 6 : is the id of new store*/ 
INSERT INTO storedetail (
    `productid`, 
    `storeid`, 
    `productquantity`,
    `userid`,
    `storedetaildate`,
    `unittype`
)
SELECT 
    `productid`,
    6,
    sum(`productquantity`),
    `userid`,
    `storedetaildate`,
    `unittype` 
FROM 
    storedetail
WHERE 
    `storeid` in (1,2)
    group by `productid`;

-- 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));


-- create 1000 row dumy data
INSERT INTO Persons(ModifiedDate, FirstName, LastName, EMail, PhoneNumber)
SELECT
    CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND()* 31536000) SECOND, -- random datetime up to -1 year
    CHAR(FLOOR(RAND() * 26)+ ASCII('A')),  -- random character between A-Z
    CHAR(FLOOR(RAND() * 26)+ ASCII('A')),
    CHAR(FLOOR(RAND() * 26)+ ASCII('a')),  -- random character between a-z
    CHAR(FLOOR(RAND() * 10)+ ASCII('0'))   -- random character between 0-9
FROM any_table_with_1000_rows
LIMIT 1000

-- create parcodes table
delimiter //
    create procedure generateParcodes (in num int)
    begin
        declare numLen int default CHAR_LENGTH(num);
        declare i int default 0;

        ALTER TABLE availableparcode ENGINE = MyISAM;

        while i <= num do
        insert into availableparcode (value) values (lpad(i, numLen, 0));
        set i = i + 1;
        end while;

        ALTER TABLE availableparcode ENGINE = InnoDB;
    end
delimiter;

call generateParcodes (1000);

--delete used parcode 
DELETE availableparcode FROM availableparcode
join product on product.parcode = availableparcode.value;


-- update prounit prices if sellall price of unit not changed
SET @v1 := (
select product.productId 
    from product
	join productunit on (product.productId = productunit.productid and productnumber = 1)
    where proUnitSellAllPrice != productSellAllPrice);
update `productunit` 
join product on product.productId = productunit.productid
SET proUnitBuyPrice = productBuyPrice * productnumber,
    proUnitSellAllPrice = productSellAllPrice * productnumber,
    proUnitSellHalfPrice = productSellHalfPrice * productnumber,
    proUnitSellUnitPrice = productSellUnitPrice * productnumber
where productunit.`productid` in (SELECT @v1 );

------------------------------------------------
-- Can I concatenate multiple MySQL rows into one field?
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
 -- instead of
shopping
fishing
coding
 -- i want
shopping, fishing, coding


 --sol 1
SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

-- sol 2
SELECT GROUP_CONCAT(hobbies SEPARATOR ', ') 
  FROM peoples_hobbies 
  WHERE person_id = 5 
  GROUP BY 'all';




-- erp example
SET @v1 := (
SELECT GROUP_CONCAT(distinct currentCat.productCatId SEPARATOR ', ')  
FROM productcat as currentCat 
left join productcat as child on (child.`productCatParent` = currentCat.productCatId and child.productCatId is Null)
join product on product.productCatId = currentCat.productCatId 
GROUP BY 'all'
);

update product set productCatId = 999999999999
where productCatId in (SELECT @v1);

----------------------------------------------------------

update product set productCatId = 1408
where productCatId in (SELECT @v1);


-----------------------------------------------------------------
-- Lock wait timeout exceeded; try restarting transaction mysql
-- Something is blocking the execution of the query. Most likely another query updating, inserting or deleting from one of the tables in your query. You have to find out what that is:

SHOW PROCESSLIST;
KILL {id}; -- id of process taking most time 

--------------------------------------------------------------------------
-- for florance recalc buy prices for output prodcucts of production execution, affect in product buyprices , sellbilldetails buy prices
DELIMITER //

CREATE PROCEDURE fixCostOfProductionEx()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE idval INT DEFAULT 0;
DECLARE totalnum INT DEFAULT 0;
DECLARE totalcostofPE DECIMAL(10,2) DEFAULT 0.00;

SELECT COUNT(*) FROM productionexecution INTO n;
SET i=0;
WHILE i<=n DO
    SELECT id INTO idval FROM productionexecution order by id LIMIT i,1;


    SELECT sum(num) FROM productionexecutionoutput WHERE exeutionid=idval INTO totalnum;
    SELECT sum(actualcost) FROM productionexecutionproduct WHERE executionid=idval INTO totalcostofPE;

    update productionexecutionoutput set cost= ((num/totalnum)* totalcostofPE) WHERE exeutionid=idval;



    UPDATE product
    JOIN productionexecutionoutput ON productionexecutionoutput.productid = product.productId
    SET productBuyPrice = (cost/num),lastbuyprice= (cost/num),lastbuyprice_withDiscount= (cost/num),meanbuyprice= (cost/num),meanbuyprice_withDiscount= (cost/num),productbuypricereal=(cost/num)
    ,lastbuyprice_withTax= (cost/num),meanbuyprice_withTax= (cost/num)
    WHERE exeutionid=idval;



    update sellbilldetail
    JOIN productionexecutionoutput ON productionexecutionoutput.productid = sellbilldetail.sellbilldetailproductid
    set buyprice= (cost/num),lastbuyprice = (cost/num),meanbuyprice= (cost/num),lastbuyprice_withDiscount= (cost/num),meanbuyprice_withDiscount= (cost/num),lastbuyprice_withTax= (cost/num),meanbuyprice_withTax=(cost/num)
    WHERE exeutionid=idval;



  SET i = i + 1;
END WHILE;




End //
DELIMITER ;