select  

(select l.descricao from  lojas l where l.codigo=d.loja ) as 'Loja',

DAY(d.data) as 'Dia', 

ifnull(SUM(dl.total),0) as 'Num Total', 

ifnull(SUM(dl.liquido),0) as 'Num SIVA', 

ifnull(SUM(dm.total),0) as 'MB Total', 

ifnull(SUM(dm.liquido),0) as 'MB SIVA', 

ifnull(SUM(dmp.total),0) as 'Multi Total', 

ifnull(SUM(dmp.liquido),0) as 'Multi SIVA', 

ifnull(SUM(dtg.total),0) as 'Too Good To Go Total',

ifnull(SUM(dtg.liquido),0) as 'Too Good To Go SIVA',

(ifnull(SUM(dl.liquido),0)--ifnull(SUM(dm.liquido),0)--ifnull(SUM(dmp.liquido),0)) as 'Total NUM e MB e Multi SIVA', 

(ifnull(SUM(dl.npedidos),0)--ifnull(SUM(dm.npedidos),0)) as 'Loja Pedidos',

ifnull(SUM(du.total),0) as 'Uber Total', 

ifnull(SUM(du.liquido),0) as 'Uber S/IVA', 

ifnull(SUM(du.npedidos),0) as 'Uber Pedidos' , 

ifnull(SUM(dg.total),0) as 'Glovo Total', 

ifnull(SUM(dg.npedidos),0) as 'Glovo Pedidos', 

ifnull(SUM(dg.liquido),0) as 'Glovo SIVA', 

ifnull(SUM(db.total),0) as 'Bolt Total', 

ifnull(SUM(db.npedidos),0) as 'Bolt Pedidos', 

ifnull(SUM(db.liquido),0) as 'Bolt SIVA', 

ifnull(SUM(ddt.total),0) as 'Deliverect Total',

ifnull(SUM(ddt.npedidos),0) as 'Deliverect Pedidos', 

ifnull(SUM(ddt.liquido),0) as 'Deliverect SIVA', 

ifnull(SUM(ddtu.total),0) as 'Deliverect UBER Total', 

ifnull(SUM(ddtu.npedidos),0) as 'Deliverect UBER Pedidos', 

ifnull(SUM(ddtu.liquido),0) as 'Deliverect UBER SIVA', 

ifnull(SUM(ddtg.total),0) as 'Deliverect Glovo Total', 

ifnull(SUM(ddtg.npedidos),0) as 'Deliverect Glovo Pedidos', 

ifnull(SUM(ddtg.liquido),0) as 'Deliverect Glovo SIVA', 

ifnull(SUM(ddtb.total),0) as 'Deliverect Bolt Total',

ifnull(SUM(ddtb.npedidos),0) as 'Deliverect Bolt Pedidos', 

ifnull(SUM(ddtb.liquido),0) as 'Deliverect Bolt SIVA',

Round(ifnull(SUM(dd.total),0),2) as 'IVA 23',

Round(ifnull(SUM(ddd.total),0),2) as 'IVA 13' ,

Round((ifnull(SUM(du.liquido),0)--ifnull(SUM(dg.liquido),0)--ifnull(SUM(db.liquido),0)),2) as 'Total Ticket',

 

 

 

(ifnull(SUM(du.npedidos),0)--ifnull(SUM(ddtu.npedidos),0)) as 'Uber e Uber Deliverect Pedidos',

Round((ifnull(SUM(du.liquido),0)--ifnull(SUM(ddtu.liquido),0)) ,2) as 'Uber e Uber Deliverect SIVA',

 

(ifnull(SUM(dg.npedidos),0)--ifnull(SUM(ddtg.npedidos),0)) as 'Glovo e Glovo Deliverect Pedidos',

Round((ifnull(SUM(dg.liquido),0)--ifnull(SUM(ddtg.liquido),0)),2) as 'Glovo e Glovo Deliverect SIVA',

 

(ifnull(SUM(db.npedidos),0)--ifnull(SUM(ddtb.npedidos),0)) as 'Bolt e Bolt Deliverect Pedidos',

Round((ifnull(SUM(db.liquido),0)--ifnull(SUM(ddtb.liquido),0)),2) as 'Bolt e Bolt Deliverect SIVA',

 

Round((ifnull(SUM(ddt.liquido),0)--ifnull(SUM(du.liquido),0)--ifnull(SUM(dg.liquido),0)--ifnull(SUM(db.liquido),0)),2) as 'Deliverect Total e Total Ticket Delivery'

 

from documentos d 

left join (select dl.doc, dl.serie, dl.numero, dl.pagamento, sum(dl.total) as total, sum(dl.liquido) as liquido, dl.loja, count(dl.doc) as npedidos from documentos dl  where dl.pagamento = 1 and dl.data>=@date and dl.data<=@data  and dl.loja=@loja and  dl.anulado =0 and dl.doc in ('FS','FT','NC') group by dl.data ) dl on dl.doc=d.doc and dl.serie=d.serie and dl.numero = d.numero and dl.loja = d.loja 

left join (select dm.doc, dm.serie, dm.numero, dm.pagamento, sum(dm.total) as total, sum(dm.liquido) as liquido, dm.loja, count(dm.doc) as npedidos from documentos dm  where dm.pagamento = 3 and dm.data>=@date and dm.data<=@data  and dm.loja=@loja and  dm.anulado =0 and dm.doc in ('FS','FT','NC') group by dm.data) dm on dm.doc=d.doc and dm.serie=d.serie and dm.numero = d.numero and dm.loja = d.loja 

left join (select dtg.doc, dtg.serie, dtg.numero, dtg.pagamento, sum(dtg.total) as total, sum(dtg.liquido) as liquido, dtg.loja, count(dtg.doc) as npedidos from documentos dtg  where dtg.pagamento = 106 and dtg.data>=@date and dtg.data<=@data  and dtg.loja=@loja and  dtg.anulado =0 and dtg.doc in ('FS','FT','NC') group by dtg.data ) dtg on dtg.doc=d.doc and dtg.serie=d.serie and dtg.numero = d.numero and dtg.loja = d.loja 

left join (select dg.doc, dg.serie, dg.numero, dg.pagamento, sum(dg.total) as total, dg.loja, count(dg.doc) as npedidos, sum(dg.liquido) as liquido from documentos dg  where dg.pagamento in(36,102,103) and dg.data>=@date and dg.data<=@data  and dg.loja=@loja and  dg.anulado =0 and dg.doc in ('FS','FT','NC') group by dg.data) dg on dg.doc=d.doc and dg.serie=d.serie and dg.numero = d.numero and dg.loja = d.loja 

left join (select db.doc, db.serie, db.numero, db.pagamento, sum(db.total) as total, db.loja, count(db.doc) as npedidos, sum(db.liquido) as liquido from documentos db  where db.pagamento in(104,39) and db.data>=@date and db.data<=@data  and db.loja=@loja and  db.anulado =0 and db.doc in ('FS','FT','NC') group by db.data) db on db.doc=d.doc and db.serie=d.serie and db.numero = d.numero and db.loja = d.loja 

left join (select du.doc, du.serie, du.numero, du.pagamento, sum(du.total) as total, du.loja, count(du.doc) as npedidos, sum(du.liquido) as liquido from documentos du  where du.pagamento in(100, 28, 108) and du.data>=@date and du.data<=@data  and du.loja=@loja and  du.anulado =0 and du.doc in ('FS','FT','NC') group by du.data) du on du.doc=d.doc and du.serie=d.serie and du.numero = d.numero and du.loja = d.loja  

left join (select ddt.doc, ddt.serie, ddt.numero, ddt.pagamento, sum(ddt.total) as total, ddt.loja, count(ddt.doc) as npedidos, sum(ddt.liquido) as liquido from documentos ddt  where ddt.pagamento = 44 and ddt.data>=@date and ddt.data<=@data  and ddt.loja=@loja and  ddt.anulado =0 and ddt.doc in ('FS','FT','NC') group by ddt.data) ddt on ddt.doc=d.doc and ddt.serie=d.serie and ddt.numero = d.numero and ddt.loja = d.loja

left join (select ddtu.doc, ddtu.serie, ddtu.numero, ddtu.pagamento, sum(ddtu.total) as total, ddtu.loja, count(ddtu.doc) as npedidos, sum(ddtu.liquido) as liquido from documentos ddtu  where ddtu.pagamento = 44 and ddtu.data>=@date and ddtu.data<=@data  and ddtu.loja=@loja and  ddtu.anulado =0 and ddtu.doc in ('FS','FT','NC') and ddtu.descricao like '#deliverect%uber%' group by ddtu.data) ddtu on ddtu.doc=d.doc and ddtu.serie=d.serie and ddtu.numero = d.numero and ddtu.loja = d.loja

left join (select ddtg.doc, ddtg.serie, ddtg.numero, ddtg.pagamento, sum(ddtg.total) as total, ddtg.loja, count(ddtg.doc) as npedidos, sum(ddtg.liquido) as liquido from documentos ddtg  where ddtg.pagamento = 44 and ddtg.data>=@date and ddtg.data<=@data  and ddtg.loja=@loja and  ddtg.anulado =0 and ddtg.doc in ('FS','FT','NC') and ddtg.descricao like '#deliverect%glovo%' group by ddtg.data) ddtg on ddtg.doc=d.doc and ddtg.serie=d.serie and ddtg.numero = d.numero and ddtg.loja = d.loja

left join (select ddtb.doc, ddtb.serie, ddtb.numero, ddtb.pagamento, sum(ddtb.total) as total, ddtb.loja, count(ddtb.doc) as npedidos, sum(ddtb.liquido) as liquido from documentos ddtb  where ddtb.pagamento = 44 and ddtb.data>=@date and ddtb.data<=@data  and ddtb.loja=@loja and  ddtb.anulado =0 and ddtb.doc in ('FS','FT','NC') and ddtb.descricao like '#deliverect%bolt%' group by ddtb.data) ddtb on ddtb.doc=d.doc and ddtb.serie=d.serie and ddtb.numero = d.numero and ddtb.loja = d.loja

left join (select dut.doc, dut.serie, dut.numero, dut.pagamento, sum(vut.total) as total, dut.loja, sum(vut.liquido) as liquido, (ifnull(sum(vut.total),0)-ifnull(sum(vut.liquido),0)) as iva  from documentos dut inner join vendas vut on vut.doc=dut.doc and vut.serie=dut.serie and vut.numero=dut.numero and dut.loja=vut.loja where dut.pagamento in(100) and dut.data>=@date and dut.data<=@data and dut.loja=@loja and  dut.anulado =0 and dut.doc in ('FS','FT','NC') group by dut.data) dut on dut.doc=d.doc and dut.serie=d.serie and dut.numero = d.numero and dut.loja = d.loja 

left join (select  dd.doc, dd.serie, dd.numero, dd.loja, vv.iva, (ifnull(sum(vv.total),0)-ifnull(sum(vv.valor),0)) as total  from documentos dd inner join vendas vv on dd.doc=vv.doc and dd.serie=vv.serie and dd.numero = vv.numero and dd.loja=vv.loja where vv.iva = 23 and dd.data>=@date and dd.data<=@data and dd.loja=@loja and  dd.anulado =0 and dd.doc in ('FS','FT','NC') group by dd.data) dd on dd.doc=d.doc and dd.serie=d.serie and dd.numero = d.numero and dd.loja = d.loja  

left join (select  ddd.doc, ddd.serie, ddd.numero, ddd.loja, vvv.iva, (ifnull(sum(vvv.total),0)-ifnull(sum(vvv.valor),0)) as total  from documentos ddd inner join vendas vvv on ddd.doc=vvv.doc and ddd.serie=vvv.serie and ddd.numero = vvv.numero and ddd.loja=vvv.loja where vvv.iva = 13 and ddd.data>=@date and ddd.data<=@data and ddd.loja=@loja and  ddd.anulado =0 and ddd.doc in ('FS','FT','NC') group by ddd.data) ddd on ddd.doc=d.doc and ddd.serie=d.serie and ddd.numero = d.numero and ddd.loja = d.loja  

left join (select dmp.doc, dmp.serie, dmp.numero, dmp.pagamento, sum(dmp.total) as total, sum(dmp.liquido) as liquido, dmp.loja, count(dmp.doc) as npedidos from documentos dmp  where dmp.pagamento = 9 and dmp.data>=@date and dmp.data<=@data  and dmp.loja=@loja and  dmp.anulado =0 and dmp.doc in ('FS','FT','NC') group by dmp.data ) dmp on dmp.doc=d.doc and dmp.serie=d.serie and dmp.numero = d.numero and dmp.loja = d.loja 

where d.data>=@date and d.data<=@data and d.loja=@loja and  d.anulado =0 and d.doc in ('FS','FT','NC') group by d.data

 

Parametros:

@loja -> igual ao numero da loja;

@date -> data inicial do intervalo de pesquisa

@data -> data final do intervalo de pesquisa