select 
v.ven_001 as codigo_venda,
v.ven_029 as nrovenda,
ev.enc_002 as dtemissao_venda,
v.ven_008 - coalesce(v.valor_couvert_f,0.0) - coalesce(v.valor_couvert_m, 0.0)  as total_acrescimo_venda, 
v.ven_009 as total_venda, 
v.usu_001_1 as id_operador,
f.usu_002 as nome_operador,
cast((case v.ven_024 when 'M'  then 'Mesa' when 'B' then 'Balcão' when 'C' then 'Comanda' when 'D' then 'Delivery' when 'P' then 'PDV' else 'Outro' end) as varchar(20)) as tipo_venda, 
cv.id_caixa, 
cv.id_empresa, 
c.id_usuario,
u.usu_002 as nome_usuario,
coalesce(e.usu_002, g.usu_002)  as nome_entregador_garcom,
cast(fn_situacoes(c.id_situacao) as varchar(100))as desc_status,
c.data_abertura,
c.data_fechamento,
c.observacao,
ev.enc_001 as id_encerravenda,
coalesce(v.caixinha_entregador,0) as caixinha_entregador,

coalesce(v.cli_001,1) as codigo_cliente,
coalesce(cli.cli_002,'CLIENTE CONSUMIDOR') as nome_cliente,    
cli.cli_012 as telefone1,
cli.cli_013 as telefone2,
cli.cep_004 as logradouro, 
cli.cli_008 as logradouro_numero, 
cli.cli_009 as complemento, 
cli.cep_003 as bairro, 
cli.cidade_desc,

(select sum(evi.ite_003) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 1) as pago_dinheiro,

(select sum(evi.ite_003) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 2) as pago_cheque,  

(select sum(evi.ite_003) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 3) as pago_crcredito,

(select sum(evi.ite_003) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 4) as pago_crdebito,

(select sum(evi.ite_003) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 5) as pago_crediario,
  
(select sum(evi.troco_dinheiro) from encerravendaitem evi
  join formapgto fpg on (evi.emp_001 = fpg.emp_001 and evi.id_formapgto = fpg.for_001)
  where evi.emp_001 = ev.emp_001 and evi.enc_001 = ev.enc_001 and fpg.sfi_codigo = 1) as troco_dinheiro

from 
(select id_caixa, id_venda, id_empresa from caixaitem where id_empresa = 1 and cast(data as date) between '22-10-2021' and '22-10-2021'
group by  id_caixa, id_venda, id_empresa order by 1) cv 
join caixa c on c.id_caixa=cv.id_caixa and cv.id_empresa=c.id_empresa
join usuarios u on c.id_usuario=u.usu_001
join venda v on v.ven_001=cv.id_venda and v.emp_001=cv.id_empresa
join encerravenda ev on v.ven_001 = ev.ven_001 and v.emp_001 = ev.emp_001
join (select min(ite_001) as ite_001, ven_001, emp_001 from vendaitem vi group by  ven_001, emp_001) vitem1 on  v.ven_001=vitem1.ven_001 and v.emp_001=vitem1.emp_001 
join vendaitem vi on v.ven_001=vi.ven_001 and v.emp_001=vi.emp_001 and vitem1.ite_001=vi.ite_001
left join usuarios g on g.usu_001 = vi.gar_001
left join usuarios e on e.usu_001 = v.id_entregador
left join usuarios f on f.usu_001 = v.usu_001_1
left join mesa m on (m.emp_001 = v.emp_001) and (m.mes_001 = v.ven_025) 
left join clientes cli on v.cli_001=cli.cli_001 and v.emp_001=cli.emp_001
where v.sit_001=1
order by c.id_usuario, ev.enc_002, v.ven_001
