CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillview` AS
SELECT sellbill.sellbillid,sellbillserial,sellbillaftertotalbill,sellbilldate,sellQuantity,sellbilltotalpayed,sellbillfinalbill,sellbill.conditions,sellbill.dailyentryid,sellbillclientid,sellerid,sellbill.userid,sellbillstoreid,sellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, sellbilldetail.pricetype
			 FROM sellbill
             join sellbilldetail on sellbilldetail.sellbillid = sellbill.sellbillid
                         LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
                         JOIN store ON sellbill.sellbillstoreid = store.storeId
                         JOIN save ON sellbill.sellbillsaveid = save.saveid
                         LEFT JOIN user ON sellbill.sellerid = user.userid
			 LEFT JOIN user as user2 ON sellbill.userid = user2.userid
                         group by sellbill.sellbillid;--LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid   ---,editedBill.sellbillid as newSellbillidAfterEdit

CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillandruternview`	AS
SELECT sellbillandrutern.sellbillid,sellbillaftertotalbill,sellbilldate,sellQuantity,returnsellQuantity,sellbilltotalpayed,sellbillfinalbill,sellbillandrutern.conditions,sellbillandrutern.dailyentryid,sellbillclientid,sellerid,sellbillandrutern.userid,sellbillstoreid,sellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, sellandruternbilldetail.pricetype
			 FROM sellbillandrutern
             join sellandruternbilldetail on sellandruternbilldetail.sellbillid = sellbillandrutern.sellbillid
			 LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
			 JOIN store ON sellbillandrutern.sellbillstoreid = store.storeId
                         JOIN save ON sellbillandrutern.sellbillsaveid = save.saveid
                         LEFT JOIN user ON sellbillandrutern.sellerid = user.userid
			 LEFT JOIN user as user2  ON sellbillandrutern.userid = user2.userid
						 group by sellbillandrutern.sellbillid;--LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid   ---,editedBill.sellbillid as newSellbillidAfterEdit

CREATE OR REPLACE ALGORITHM = MERGE VIEW `returnsellbillview` AS
SELECT returnsellbill.returnsellbillid,returnsellbillserial,returnsellbillaftertotalbill,returnsellbilldate,returnsellQuantity,returnsellbilltotalpayed,returnsellbillfinalbill,returnsellbill.conditions,returnsellbill.dailyentryid,returnsellbillclientid,sellerid,returnsellbill.userid,returnsellbillstoreid,returnsellbillsaveid, client.clientname, user.employeename , user2.employeename  as  useremployeename,storeName,savename, returnsellbilldetail.pricetype
			 FROM returnsellbill
						join returnsellbilldetail on returnsellbilldetail.returnsellbillid = returnsellbill.returnsellbillid
                         LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
                         JOIN store ON returnsellbill.returnsellbillstoreid = store.storeId
                         JOIN save ON returnsellbill.returnsellbillsaveid = save.saveid
                         LEFT JOIN user ON returnsellbill.sellerid = user.userid
			 LEFT JOIN user as user2 ON returnsellbill.userid = user2.userid
							group by returnsellbill.returnsellbillid;--LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid   ---,editedBill.returnsellbillid as newSellbillidAfterEdit


--for clientdebt.php
CREATE OR REPLACE ALGORITHM = MERGE VIEW `clientLastDeals` AS
select client.clientid,clientname,clientdebt,clientphone,clientmobile,clientarea.name as areaName,qclientdebtchange.lastClientDebtChangeDate
	   ,qsellbill.lastSellBillDate,qreturnsellbill.lastReturnSellBillDate,qsellbillandrutern.lastSellBillAndRuternDate
from client
left join clientarea on clientarea.id=client.clientareaid
left join (
	SELECT clientid,max(clientdebtchangedate) lastClientDebtChangeDate
	from clientdebtchange
	where clientdebtchange.tablename = "clientPayedDeptController.php"
	and del = 0
	) as qclientdebtchange on qclientdebtchange.clientid = client.clientid
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillDate
    FROM sellbill
	where conditions = 0
    group by sellbillclientid
    ) as qsellbill on qsellbill.sellbillclientid = client.clientid
left join (
    SELECT sellbillclientid,max(sellbilldate) as lastSellBillAndRuternDate
    FROM sellbillandrutern
	where conditions = 0
    group by sellbillclientid
    ) as qsellbillandrutern on qsellbillandrutern.sellbillclientid = client.clientid
left join (
    SELECT returnsellbillclientid,max(returnsellbilldate) as lastReturnSellBillDate
    FROM returnsellbill
	where conditions = 0
    group by returnsellbillclientid
    ) as qreturnsellbill on qreturnsellbill.returnsellbillclientid = client.clientid
where client.conditions = 0;

-- ------------------------------------------------------
-- the simple views
CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillview` AS
    SELECT sellbill.sellbillid,sellbill.sellbillserial,sellbill.sellbillaftertotalbill,sellbill.sellbilldate,sellbill.sellbilltotalpayed,sellbill.sellbillfinalbill,sellbill.conditions,sellbill.sellbillclientid,sellbill.sellerid,sellbill.userid,sellbill.sellbillstoreid,sellbill.sellbillsaveid, client.clientname,editedBill.sellbillid as newSellbillidAfterEdit,sellbill.dailyentryid
    FROM sellbill
    LEFT JOIN client ON sellbill.sellbillclientid = client.clientid
    LEFT JOIN sellbill as editedBill ON sellbill.sellbillid = editedBill.deletedsellid;

CREATE OR REPLACE ALGORITHM = MERGE VIEW `sellbillandruternview`	AS
SELECT sellbillandrutern.sellbillid,sellbillandrutern.sellbillaftertotalbill,sellbillandrutern.sellbilldate,sellbillandrutern.sellbilltotalpayed,sellbillandrutern.sellbillfinalbill,sellbillandrutern.conditions,sellbillandrutern.sellbillclientid,sellbillandrutern.sellerid,sellbillandrutern.userid,sellbillandrutern.sellbillstoreid,sellbillandrutern.sellbillsaveid, client.clientname,editedBill.sellbillid as newSellbillidAfterEdit,sellbillandrutern.dailyentryid
			 FROM sellbillandrutern
			 LEFT JOIN client ON sellbillandrutern.sellbillclientid = client.clientid
                         LEFT JOIN sellbillandrutern as editedBill ON sellbillandrutern.sellbillid = editedBill.deletedsellid;

CREATE OR REPLACE ALGORITHM = MERGE VIEW `returnsellbillview` AS
SELECT returnsellbill.returnsellbillid,returnsellbill.returnsellbillserial,returnsellbill.returnsellbillaftertotalbill,returnsellbill.returnsellbilldate,returnsellbill.returnsellbilltotalpayed,returnsellbill.returnsellbillfinalbill,returnsellbill.conditions,returnsellbill.returnsellbillclientid,returnsellbill.sellerid,returnsellbill.userid,returnsellbill.returnsellbillstoreid,returnsellbill.returnsellbillsaveid, client.clientname,editedBill.returnsellbillid as newSellbillidAfterEdit,returnsellbill.dailyentryid
			 FROM returnsellbill
             LEFT JOIN client ON returnsellbill.returnsellbillclientid = client.clientid
             LEFT JOIN returnsellbill as editedBill ON returnsellbill.returnsellbillid = editedBill.deletedsellid;


-- -----------------------------------------------------------------------------------
-- remove first char of parcode if its length > 5
UPDATE product SET parcode = SUBSTR(parcode, 2) WHERE CHAR_LENGTH (`parcode`) > 5 and parcode LIKE '0%';

-- ------------------------------------------------------------------------------------------------
-- get client with error in debt to review
-- 1-make sure tables do exist
-- note drop "clientdebtchangetemp" means lose clientdebtcalc
drop table if exists clienttemp;
-- note drop "clientdebtchangetemp" means rework do it only if necessary
drop table if exists clientdebtchangetemp;
CREATE TABLE IF NOT EXISTS `clienttemp` (
  `clientid` int(11) NOT NULL AUTO_INCREMENT,
  `clientdebtcalc` float NOT NULL,
  PRIMARY KEY (`clientid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `clientdebtchangetemp` (
  `clientdebtchangeid` int(11) NOT NULL,
  `clientid` int(11) NOT NULL,
  `clientdebtchangebefore` float NOT NULL,
  `clientdebtchangeamount` float NOT NULL,
  `clientdebtchangetype` int(11) NOT NULL,
  `clientdebtchangeafter` float NOT NULL,
  `tablename` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- LOCK TABLES client WRITE,clienttemp WRITE,clientdebtchange WRITE,clientdebtchangetemp WRITE;

-- 2-insert data into it
INSERT INTO clienttemp
SELECT clientid,0 FROM client where clientid !=1;
-- note insert only new rows
INSERT INTO clientdebtchangetemp
SELECT clientdebtchangeid,clientid,clientdebtchangebefore,clientdebtchangeamount,clientdebtchangetype,clientdebtchangeafter,tablename
FROM clientdebtchange
where del = 0 and tablename != "clientDeficitController.php" and clientid !=1 and clientdebtchangeid > 0000; -- 0000 is last clientdebtchangeid in temp table before using the last " INSERT INTO clientdebtchangetemp " statment
-- use this statment only if you drop table clientdebtchangetemp at begining as we need id column
ALTER TABLE clientdebtchangetemp ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

-- 3-reverse the clientdebtchangetype for new rows only as clientPayedDeptController.php type is entered in reverse way
UPDATE clientdebtchangetemp
SET clientdebtchangetype = CASE clientdebtchangetype WHEN 0 THEN 1 WHEN 1 THEN 0 ELSE clientdebtchangetype END
where tablename="clientPayedDeptController.php" and id > 0000; -- 0000 is last clientdebtchangeid in temp table before using the last " INSERT INTO clientdebtchangetemp " statment

-- UNLOCK TABLES;


-- 4-create procedure goes row by row to re calc debtbefore and after for each row in clientdebtchangetemp
DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE ROWPERROW(IN i INT)
BEGIN
DECLARE n INT DEFAULT 0; -- n for total rows to loop through
-- DECLARE i INT DEFAULT 1; -- i for current row id
DECLARE debtbefore INT DEFAULT 0; -- save debt before from previos row in it
DECLARE clientdebtchangetypeinrow INT DEFAULT 0; -- save current type
DECLARE clientidinrow INT DEFAULT 0; -- save clientid
SELECT COUNT(*) FROM clientdebtchangetemp INTO n; -- no of rows of the table needed in while
WHILE i <= n DO
	-- save client id of row and reset debtbefore
	SELECT clientid FROM clientdebtchangetemp where id=i INTO clientidinrow;
	SET debtbefore=0;
	-- get debtbefore and save it in the row
	SELECT clientdebtchangeafter FROM clientdebtchangetemp where id<i and clientid = clientidinrow order by id desc limit 1  INTO debtbefore;
	update clientdebtchangetemp set clientdebtchangebefore  = debtbefore where id=i;

	-- get clientdebtchangetype to calc clientdebtchangeafter with + for type=0 and - for type=1
	SELECT clientdebtchangetype FROM clientdebtchangetemp where id=i INTO clientdebtchangetypeinrow;
	IF clientdebtchangetypeinrow = 1 THEN
	   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore - clientdebtchangeamount where id = i;
	ELSE
	   update clientdebtchangetemp set clientdebtchangeafter  = clientdebtchangebefore + clientdebtchangeamount where id = i;
	END IF;
	-- save new clc debt in clienttemp
	update clienttemp set clientdebtcalc  = (select clientdebtchangeafter from clientdebtchangetemp where id=i) where clientid=clientidinrow;

	-- move to next i
	SET i = i + 1;
END WHILE;
End;
;;

DELIMITER ;



-- call stored procedure by name and pass no of row to start recalculating debtbefore and after
CALL ROWPERROW(1);
-- this drops the stored procedure
DROP PROCEDURE IF EXISTS ROWPERROW;
-- ------------------------------------------------------------------------------------------------
-- update with join example
UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition;
-- like
UPDATE productserial
JOIN buybill ON productserial.billid = buybill.buybillid
SET productserial.storeid = buybill.buybillstoreid
---------------------------------------------------------------------------------------------------
--
ALTER TABLE table ADD UNIQUE KEY column1 (column1);
INSERT IGNORE INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3);
-- or if you want to automatically update the other fields, use ON DUPLICATE KEY UPDATE:
INSERT INTO table(column1, column2, column3) VALUES(@value1, @value2, @value3)
ON DUPLICATE KEY UPDATE column2 = @value2,  column3 = @value3;

-- letting the database handle it automatically using unique key has several advantages:
--      *better performance and scalability through usage of indexes as opposed to full table scans
--      **failsafe - you can not add two rows with same unique identifier accidentally with a wrong query
--      ***better readability of your queries
-- NOTE: a unique key may also span multiple rows. stupid example: combination of "ip" and "port" may be combined as the unique identifier "connection".


--------------------------------------------------------------------------------
-- updateCollectiveProductsPrices as sum of ingridients cost
UPDATE product
JOIN (
       select productingredients.productId
              ,sum(productnumber * quantity * productBuyPrice) as productBuyPrice
              ,sum(productnumber * quantity * lastbuyprice) as lastbuyprice
              ,sum(productnumber * quantity * lastbuyprice_withDiscount) as lastbuyprice_withDiscount
              ,sum(productnumber * quantity * meanbuyprice) as meanbuyprice
              ,sum(productnumber * quantity * meanbuyprice_withDiscount) as meanbuyprice_withDiscount
              ,sum(productnumber * quantity * overAllAveragePrice) as overAllAveragePrice
      from product
      join productingredients on product.productId = productingredients.ingridientId
      join productunit on productunit.productunitid = productingredients.unitId
      group by productingredients.productId
    ) as ingridientPrice ON ingridientPrice.productId =  product.productId
SET product.productBuyPrice = ingridientPrice.productBuyPrice,
	product.lastbuyprice = ingridientPrice.lastbuyprice,
    product.lastbuyprice_withDiscount = ingridientPrice.lastbuyprice_withDiscount,
    product.meanbuyprice = ingridientPrice.meanbuyprice,
    product.meanbuyprice_withDiscount = ingridientPrice.meanbuyprice_withDiscount,
    product.overAllAveragePrice = ingridientPrice.overAllAveragePrice;
--------------------------------------------------------------------------------