Copy orig to posd with option to change %

set @terminalno = 1;
set @datefrom = '2018-01-01';
set @dateto = '2019-01-31 23:59:59';
set @branchid = 16;
set @perc = 1;

delete from salesdetail_posd
where headid in (
select
wid
from saleshead
where ´date´ between @datefrom and @dateto
and terminalno = @terminalno
and branchid =@branchid
and ´type´ = 3
);
delete from collectiondetail_posd
where headid in (
select
headid
from collectionsales_posd
where saleswid in (
select ´wid´
from saleshead
where ´date´ between @datefrom and @dateto
and ´terminalno´ = @terminalno
and ´branchid´ = @branchid
and ´type´ = 3
)
);

set @wid = (
select ifnull(max(wid),concat(@branchid,lpad(@terminalno,2,0),'00000000001')+0)
from salesdetail_posd
where left(wid,4) = concat(@branchid,lpad(@terminalno,2,0))
);

INSERT INTO ´salesdetail_posd´
(
´wid´,
´headid´,
´productid´,
´description´,
´quantity´,
´bigquantity´,
´oprice´,
´price´,
´discount1´,
´discount2´,
´discount3´,
´discount4´,
´istransfer´,
´pprice´,
´addbackqty´,
´addbackbigqty´,
´vat´,
´senior´,
´soldby´)
select
@wid := @wid + 1,
´headid´,
´productid´,
´description´,
´quantity´,
´bigquantity´,
´oprice´*@perc,
´price´*@perc,
´discount1´,
´discount2´,
´discount3´,
´discount4´,
´istransfer´,
´pprice´*@perc,
´addbackqty´,
´addbackbigqty´,
´price´*@perc/1.12*0.12,
´senior´,
´soldby´
from salesdetail
where headid in (
select
wid
from saleshead
where ´date´ between @datefrom and @dateto
and ´terminalno´ = @terminalno
and ´branchid´ = @branchid
and ´type´ = 3
);

update collectionsales_posd as C,
(select H.wid, sum(D.price*D.quantity) as amount
from salesdetail_posd as D, saleshead_posd as H
where H.wid = D.headid
and ´date´ between @datefrom and @dateto
and terminalno = @terminalno
and branchid =@branchid
group by H.wid) as S set
C.amount = S.amount
where C.saleswid = S.wid;

set @wid = (
select ifnull(max(wid),concat(@branchid,lpad(@terminalno,2,0),'00000000001')+0)
from collectiondetail_posd
where left(wid,4) = concat(@branchid,lpad(@terminalno,2,0)));

INSERT INTO ´collectiondetail_posd´
(
´wid´,
´headid´,
´method´,
´amount´)
select
@wid := @wid + 1,
D.headid,
D.method,
D.amount*@perc from collectiondetail as D, collectionsales_posd as S
where D.headid = S.headid and S.saleswid in (
select
wid
from saleshead
where ´date´ between @datefrom and @dateto
and terminalno = @terminalno
and branchid = @branchid
);

delete from posxyzread_posd where terminalno = @terminalno and ´date´ >= @datefrom and branchid =@branchid;
delete from posxyzread where terminalno = @terminalno and ´date´ >= @datefrom and branchid = @branchid;

select
´date´,oldgrandtotal,newgrandtotal,
format(newgrandtotal-oldgrandtotal,2) as amt
from posxyzread_posd
where terminalno = @terminalno and ´date´ >= @datefrom
and branchid =@branchid
and readtype = 3 ;
select
´date´,oldgrandtotal,newgrandtotal,
format(newgrandtotal-oldgrandtotal,2) as amt
from posxyzread
where terminalno = @terminalno and ´date´ >= @datefrom
and branchid = @branchid
and readtype = 3 ;

 

 

Tags: posd, query