with
classmerc as (
 select distinct(prod_codigo) as codprod,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as coddpto,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as codsecao,
 (select a.grup_codigo from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as codcat,
 prod_grup_codigo as codfamilia,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 2)) as descdpto,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 4)) as descsecao,
 (select a.grup_descricao from grupos a where a.grup_classificacao = substr(z.grup_classificacao, 1, 6)) as desccat,
 prod_grup_nome as descfamilia
from produtos
left join grupos z on (prod_grup_codigo=z.grup_codigo)
),

vendas as (
select distinct(t1.mprd_prod_codigo) codprod,
 coalesce(round(sum(t1.mprd_valor),2),0.00) as valor,
 coalesce(round(sum(t1.mprd_valor) filter (where t1.mprd_dcto_codigo IN ('7336','7337','7338','7339') ),2),0.00) as clickshow,
 coalesce(round(sum(t1.mprd_prcompra),2),0.00) as valor_prcompra
from (MOVPRODDPERIODO) as t1
where t1.mprd_datamvto between DATAINICIAL and DATAFINAL
 and t1.mprd_unid_codigo='001'
 and t1.mprd_dcto_codigo IN ('7165','7318','7320','7321','7325','7326','7327','7328','7329','7330','7331','7336','7337','7338','7339','7340','7343')
 and t1.mprd_status='N'
group by t1.mprd_prod_codigo
),

lojaloja as (
select
 distinct(pcpd_prod_codigo) as codprod,

 case when pcpd_tpsubtrib='P' then coalesce(round(sum(pcpd_valor) + (sum(pcpd_valor)*pcpd_subtrib/100),3),0.000)
     when pcpd_tpsubtrib='V' then coalesce(round((sum(pcpd_valor) + pcpd_subtrib),3),0.000)
     else coalesce(round(sum(pcpd_valor),3),0.000)
     end as valor

from pedcomprac
inner join pedcomprad on (pcpc_numped=pcpd_numped)
where pcpc_datamvto between DATAINICIAL and DATAFINAL
 and pcpc_dcto_codigo='6201'
 and pcpc_status<>'C'
 and pcpc_tipo='C'
 and pcpc_obs not like 'Gerado Pela Baixa Parcial%'
group by
pcpd_prod_codigo, pcpd_tpsubtrib, pcpd_subtrib
),

estorno as (
select distinct(t1.mprd_prod_codigo) codprod,
 coalesce(round(sum(t1.mprd_valor),2),0.00) as valor
from (MOVPRODDPERIODO) as t1
where t1.mprd_datamvto between DATAINICIAL and DATAFINAL
 and t1.mprd_unid_codigo='001'
 and t1.mprd_dcto_codigo='7273'
 and t1.mprd_status='N'
group by t1.mprd_prod_codigo
),

pend as (
select
prod_codigo as codprod,
coalesce((select
sum(pest_qtde) - sum(pest_qtdebx)
from pendest pe
 where pest_status='P'
 and (pest_unid_origem='001' or pest_unid_destino='001')
 and pest_cpes_codigo in ('001','100','101','102','104','106','120','150','151','190','200','201','300','301','302','310','315','320','350','390','400','500','600','800','900')
 and pe.pest_prod_codigo=po.prod_codigo
limit 1
),0.000) as pendentes
from produtos po
order by prod_codigo
),

pu as (
select
 distinct(prun_prod_codigo) as codprod,

 case when (round(coalesce(((prun_estoque1+prun_estoque4)-pend.pendentes),0.00000),3))<0 then 0.000
 else round(coalesce(((prun_estoque1+prun_estoque4)-pend.pendentes),0.00000),3)
 end as estdisp_un,

 case when (round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes) / prod_qemb),0)))<0 then 0
 else round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes) / prod_qemb),0))
 end as estdisp_emb,

 case when (round(coalesce(((prun_estoque1+prun_estoque4)-pend.pendentes),0.00000),3))<0 then 0.00
 else round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes) * prun_prvenda),0.00),2)
 end as vr_estdisp_prvenda,

 case when (round(coalesce(((prun_estoque1+prun_estoque4)-pend.pendentes),0.00000),3))<0 then 0.00
 else round(coalesce((((prun_estoque1+prun_estoque4)-pend.pendentes) * prun_prultcomp),0.00),2)
 end as vr_estdisp_prultcomp,

 round(coalesce((prun_estoque1+prun_estoque4),0.00000),3) as estfisico_un,
 round(coalesce(((prun_estoque1+prun_estoque4) / prod_qemb),0)) as estfisico_emb,

 round(coalesce(((prun_estoque1+prun_estoque4) * prun_prvenda),0.00),2) as vr_estfisico_prvenda,
 round(coalesce(((prun_estoque1+prun_estoque4) * prun_prultcomp),0.00),2) as vr_estfisico_prultcomp


 from produn
 left join produtos on (prod_codigo=prun_prod_codigo)
 left join pend on (prun_prod_codigo=pend.codprod)
 where prun_unid_codigo='001'
)

select
 distinct(t1.prod_codigo),
 prod_descricao,
 prod_complemento,
 prod_marca,
 prod_emb,
 case when prod_qemb='0' then '1' else prod_qemb end as prod_qemb,

 case when prod_tipo='EST' then 'Estratégico'
      when prod_tipo='NOR' then 'Normal'
      else 'Sem Tipo'
      end as prod_tipo,

 prod_grup_codigo,

 classmerc.descdpto,
 classmerc.descsecao,
 classmerc.desccat,

 prod_grup_nome,
 prod_comp_codigo,
 comp_nome,
 prod_classe,

 case when vendas.valor is not null then vendas.valor
 else '0.00'
 end as valor_vendas,

 case when vendas.clickshow is not null then vendas.clickshow
 else '0.00'
 end as valor_vendas_clickshow,

 case when vendas.valor_prcompra is not null then vendas.valor_prcompra
 else '0.00'
 end as valor_vendas_prcompra,

 case when lojaloja.valor is not null then lojaloja.valor
 else '0.00'
 end as valor_lojaloja,

 case when (vendas.valor is not null and lojaloja.valor is not null) then vendas.valor + lojaloja.valor
      when vendas.valor is null then lojaloja.valor
      when lojaloja.valor is null then vendas.valor
 else vendas.valor + lojaloja.valor
 end as valor_total_geral,

 coalesce(round((vendas.valor / sum(vendas.valor) over ())*100,4),0.0000) as part_vendas,
 coalesce(round((vendas.clickshow / sum(vendas.clickshow) over ())*100,4),0.0000) as part_vendas_clickshow,
 coalesce(round((lojaloja.valor / sum(lojaloja.valor) over ())*100,4),0.0000) as part_lojaloja_valor,

/*
 case when vendas.valor_prcompra=0
   then ((100 - ((vendas.valor / 1) * 100)) + 100)
   else ((100 - ((vendas.valor / vendas.valor_prcompra) * 100)) + 100)
 end as cmvmov,
*/

 case when estorno.valor is not null then estorno.valor
 else '0.00'
 end as valor_estorno,

 pu.estdisp_emb,
 pu.estfisico_emb,

 round(prun_prvenda,3) as prvenda_un,
 round(coalesce((prun_prvenda * prod_qemb),0.000),3) as prvenda_emb,

 round(prun_prultcomp,3) as prcompra_un,
 round(coalesce((prun_prultcomp * prod_qemb),0.000),3) as prcompra_emb,

 pu.vr_estfisico_prvenda,
 pu.vr_estfisico_prultcomp,

/*
 case when (pu.vr_estdisp_prultcomp=0 and pu.vr_estdisp_prvenda=0) then 0.000
      when pu.vr_estdisp_prultcomp=0 then ((100 - ((pu.vr_estdisp_prvenda / 1) * 100)) + 100)
       else ((100 - ((pu.vr_estdisp_prvenda / pu.vr_estdisp_prultcomp) * 100)) + 100)
 end as cmvest,
*/

 case when (vendas.valor > lojaloja.valor) then 'Show'
      when (lojaloja.valor is null and vendas.valor is not null) then 'Show'
      when (vendas.valor < lojaloja.valor) then 'Loja a Loja'
      when (vendas.valor is null and lojaloja.valor is not null) then 'Loja a Loja'
      when (vendas.valor = lojaloja.valor) then 'Igual'
      when (vendas.valor is null and lojaloja.valor is null) then 'Sem Registros'
      else 'Sem Classificação'
      end as modalidade_predominante

from produtos as t1
INNER JOIN produn on (prun_prod_codigo=t1.prod_codigo and prun_unid_codigo='001')
LEFT JOIN compradores on (comp_codigo=prod_comp_codigo)
LEFT JOIN classmerc on (t1.prod_codigo=classmerc.codprod)
LEFT JOIN vendas on (t1.prod_codigo=vendas.codprod)
LEFT JOIN lojaloja on (t1.prod_codigo=lojaloja.codprod)
LEFT JOIN estorno on (t1.prod_codigo=estorno.codprod)
LEFT JOIN pend on (t1.prod_codigo=pend.codprod)
LEFT JOIN pu on (t1.prod_codigo=pu.codprod)
WHERE CONDICAOESCOPO
group by
t1.prod_codigo,
prod_descricao,
prod_complemento,
prod_marca,
prod_emb,
prod_qemb,
prod_tipo,
prod_grup_codigo,
classmerc.descdpto,
classmerc.descsecao,
classmerc.desccat,
prod_grup_nome,
prod_comp_codigo,
comp_nome,
prod_classe,
vendas.valor,
vendas.clickshow,
vendas.valor_prcompra,
estorno.valor,
lojaloja.valor,
pu.estdisp_emb,
pu.estfisico_emb,
prun_prvenda,
prun_prultcomp,
pu.vr_estfisico_prvenda,
pu.vr_estfisico_prultcomp,
classmerc.coddpto,
classmerc.codsecao,
classmerc.codcat
order by part_vendas DESC, part_vendas, part_vendas_clickshow, part_lojaloja_valor nulls last