set @branchid = 1;
set @terminalno = 1;
set @startdate = '2000-01-01';
set @enddate = '2020-12-30 23:59:59';
select @terminalno,
sales.´sales amount´,
collection.´collection sales amount´,collection.´collection detail amount´,
reading.´total read count´, reading.´total sales from reading´,reading.´max grand total´,
sales_posd.´sales amount´ as 'POSD sales amount',
collection_posd.´collection sales amount´ as 'POSD collection sales amount',
collection_posd.´collection detail amount´ as 'POSD collection detail amount',
reading_posd.´total read count´ as 'POSD total read count',
reading_posd.´total sales from reading´ as 'POSD total sales from reading',
reading_posd.´max grand total´ as 'POSD max grand total',
user.´total user count´, product.´total product count´
from
(select @terminalno as 'terminalno') as terminal
left join (
select H.terminalno, format(coalesce(sum(quantity*price),0),2) as 'sales amount'
from saleshead as H, salesdetail as D
where H.wid= D.headid
and H.´show´ = 1 and H.´status´ = 1 and H.´type´ = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
group by H.´terminalno´
) as sales on sales.terminalno = terminal.terminalno
left join (
select A.terminalno,
format(sum(A.´collectionsalesamount´),2) as 'collection sales amount',
format(sum(A.´collectiondetailamount´),2) as 'collection detail amount'
from
(
select S.terminalno,
coalesce(if(CH.´show´ = 1 and CH.´status´ = 1, CS.´amount´,0),0) as 'collectionsalesamount',
coalesce(sum(if(CH.´show´ = 1 and CH.´status´ = 1, CD.´amount´,0)),0) as 'collectiondetailamount'
from (
select H.wid, H.terminalno
from saleshead as H, salesdetail as D
where H.wid= D.headid
and H.´show´ = 1 and H.´status´ = 1 and H.´type´ = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
group by H.´wid´
) S
left join collectionsales as CS on CS.saleswid = S.wid
left join collectionhead as CH on CH.wid = CS.headid
left join collectiondetail as CD on CD.headid = CS.headid
group by S.wid
) A
) as collection on collection.terminalno = terminal.terminalno
left join (
select terminalno, count(*) as 'total read count',
format(sum(newgrandtotal-oldgrandtotal),2) as 'total sales from reading', format(max(newgrandtotal),2) as 'max grand total'
from posxyzread
where readtype = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
order by ´date´ desc
) as reading on reading.terminalno = terminal.terminalno
left join (
select H.terminalno, format(coalesce(sum(quantity*price),0),2) as 'sales amount'
from saleshead_posd as H, salesdetail_posd as D
where H.wid= D.headid
and H.´show´ = 1 and H.´status´ = 1 and H.´type´ = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
group by H.´terminalno´
) as sales_posd on sales_posd.terminalno = terminal.terminalno
left join (
select A.terminalno,
format(sum(A.´collectionsalesamount´),2) as 'collection sales amount',
format(sum(A.´collectiondetailamount´),2) as 'collection detail amount'
from
(
select S.terminalno,
coalesce(if(CH.´show´ = 1 and CH.´status´ = 1, CS.´amount´,0),0) as 'collectionsalesamount',
coalesce(sum(if(CH.´show´ = 1 and CH.´status´ = 1, CD.´amount´,0)),0) as 'collectiondetailamount'
from (
select H.wid, H.terminalno
from saleshead_posd as H, salesdetail_posd as D
where H.wid= D.headid
and H.´show´ = 1 and H.´status´ = 1 and H.´type´ = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
group by H.´wid´
) S
left join collectionsales_posd as CS on CS.saleswid = S.wid
left join collectionhead_posd as CH on CH.wid = CS.headid
left join collectiondetail_posd as CD on CD.headid = CS.headid
group by S.wid
) A
) as collection_posd on collection_posd.terminalno = terminal.terminalno
left join (
select terminalno, count(*) as 'total read count',
format(sum(newgrandtotal-oldgrandtotal),2) as 'total sales from reading', format(max(newgrandtotal),2) as 'max grand total'
from posxyzread_posd
where readtype = 3
and ´date´ between @startdate and @enddate
and terminalno = @terminalno
and branchid = @branchid
order by ´date´ desc
) as reading_posd on reading_posd.terminalno = terminal.terminalno
left join (
select @terminalno as 'terminalno', count(*) as 'total user count'
from ´user´
where ´show´ = 1 and ´status´ = 1
) as user on user.terminalno = terminal.terminalno
left join (
select @terminalno as 'terminalno', count(*) as 'total product count'
from ´product´ as P,branchprice as BP
where P.´show´ = 1 and P.´status´ = 1 and P.wid = BP.productid
and BP.branchid = @branchid
) as product on product.terminalno = terminal.terminalno
