alter table tb_nfvenda add statusimp integer;
alter table tb_pedido_venda add statusimp integer;
update tb_nfvenda set statusimp = 1;
update tb_pedido_venda set statusimp = 1;
alter table tb_pedido_venda add index_Mt integer;
alter table tb_pedido_venda add marcado_Mt integer;
alter table tb_nfvenda add index_Mt integer;
alter table tb_nfvenda add marcado_Mt integer;

create table xx_tb_index_mt_pai
(
id integer,
descricao varchar(100),
data date,
hora time
);

create table xx_tb_index_mt_filha
(
id integer,
id_pai integer,
id_identificador integer,
qt decimal(15,2),
vlr_unit decimal(15,2),
totalprodutos decimal(15,2)
);
create generator xx_gen_index_mt_filha;
create generator xx_gen_index_mt_pai;



set term ^

create or alter trigger xx_tr_index_mt_filha for xx_tb_index_mt_filha
active before insert position 0
as
begin
new.id = gen_id(xx_gen_index_mt_filha,1);
end
^

create or alter trigger xx_tr_index_mt_pai for xx_tb_index_mt_pai
active before insert position 0
as
begin
new.id = gen_id(xx_gen_index_mt_pai,1);
end
^

create or alter view view_lista_orc
(
 nf_numero,id_nfvenda,dt_emissao,dt_saida,vendedor,idCli,cliente,horasaida,frete,seguro,despesa,
 desconto,totalprodutos,pesoliquido,pesobruto,natureza,marca,volume,formapag,parcelamento,
 parcelas,totalnota,status,fim,dt_fecha,id_natope,setor,observacao
)
as
select
 p.id_pedido                as nf_numero,      -- 1
 p.id_pedido                as id_nfvenda,     -- 2
 p.dt_pedido                as dt_emissao,     -- 3
 p.dt_pedido                as dt_saida,       -- 4
 func.nome                  as vendedor,       -- 5
 cli.id_cliente             as idCli,          -- 6
 cli.nome                   as cliente,        -- 7
 p.hr_pedido                as horasaida,      -- 8

 0                          as frete,          -- 9
 0                          as seguro,         -- 10
 0                          as despesa,        -- 11

 sum(i.vlr_desc)                          as desconto,       -- 12

 sum(i.vlr_total)           as totalprodutos,  -- 13
 0                          as pesoliquido,    -- 14
 0                          as pesobruto,      -- 15

 'ORCAMENTO'                as natureza,       -- 16
 ''                         as marca,          -- 17
 0                          as volume,         -- 18

 fp.descricao               as formapag,       -- 19
 parc.descricao             as parcelamento,   -- 20
 parc.n_parcelas            as parcelas,       -- 21

 sum(i.vlr_total - i.vlr_desc)           as totalnota,      -- 22

 'ORC'                      as status,         -- 23
 'ORC'                      as fim,            -- 24

 null                 as dt_fecha,       -- 25

 0                as id_natope,      -- 26
 null                   as setor,          -- 27
 p.observacao                      as observacao      -- 28

from tb_pedido_venda p
left join tb_ped_venda_item i      on i.id_pedido = p.id_pedido
left join tb_cliente cli           on cli.id_cliente = p.id_cliente
left join tb_funcionario func      on func.id_funcionario = p.id_vendedor

left join tb_forma_pagto_sis fp    on fp.id_fmapgto = p.id_fmapgto
left join tb_parcelamento parc     on parc.id_parcela = p.id_parcela

group by
 p.id_pedido, p.dt_pedido, p.hr_pedido, func.nome,
 cli.id_cliente, cli.nome, fp.descricao,
 parc.descricao, parc.n_parcelas,
  p.observacao
^



create or alter view view_lista_nf_venda
(
 nf_numero,id_nfvenda,dt_emissao,dt_saida,vendedor,cliente,horasaida,frete,seguro,despesa,
 desconto,totalprodutos,pesoliquido,pesobruto,natureza,marca,volume,formapag,parcelamento,
 parcelas,totalnota,status,fim,id_natope,vlrjuros,vlrtotalnfjuros,idcliente,
 vlr_troco,vlr_dinheiro,vlr_prazo,vlr_cartao,vlr_cheque,vlr_pago_cupom,vlr_entrada,
 ccusto, marcado,statusdav, nfmodelo, custoTotal, statusimp, index_Mt
)
as

select
  case when nf_modelo = 'GR' then nfs.id_nfvenda else nfs.nf_numero end,
 nfs.id_nfvenda,
 nfs.dt_emissao,
 nfs.dt_saida,
 func.nome,
 cli.nome,
 nfs.hr_saida,
 sum(item.vlr_frete),
 sum(item.vlr_seguro),
 sum(item.vlr_despesa),
 sum(item.vlr_desc),
 sum(item.vlr_total),
 nfs.pes_liquid,
 nfs.pes_bruto,
 nat.descricao,
 nfs.marca,
 nfs.qtd_volum,
 form.descricao,
 parc.descricao,
 parc.n_parcelas,
 sum(item.vlr_total) + sum(item.vlr_seguro) + sum(item.vlr_despesa) + sum(item.vlr_frete) - sum(item.vlr_desc),
 nfs.status,
 case when nf_modelo = 'GR' then 'GR' else 'NF' end,
 nfs.id_natope,
 0 as vlr_juros,
 sum(item.vlr_total),
 nfs.id_cliente,
 0,0,0,0,0,0,0,
 0,
 marcado_mt as marcado,
 '' as statusdav,
 nfs.nf_modelo,
 sum(item.vlr_custo),
 case when statusimp is null then '' else 'Impresso' end, coalesce(index_mt,0)
from tb_nfvenda nfs
left join tb_cliente cli on cli.id_cliente = nfs.id_cliente
left join tb_funcionario func on func.id_funcionario = nfs.id_vendedor
left join tb_nfv_item item on item.id_nfvenda = nfs.id_nfvenda
left join tb_nat_operacao nat on nat.id_natope = nfs.id_natope
left join tb_forma_pagto_sis form on form.id_fmapgto = nfs.id_fmapgto
left join tb_parcelamento parc on parc.id_parcela = nfs.id_parcela
group by
 1,2,3,4,5,6,7,13,14,15,16,17,18,19,20,22,23,24,25,27,28,29,30,31,32,33,34,35,36,37,38,40,41



union all

select
 p.ID_PEDIDO as nf_numero,               -- 1
 p.ID_PEDIDO as id_nfvenda,             -- 2
 p.DT_PEDIDO as dt_emissao,             -- 3
 p.DT_PEDIDO as dt_saida,               -- 4
 func.nome as vendedor,                 -- 5
 cli.nome as cliente,                   -- 6
 p.HR_PEDIDO as horasaida,              -- 7
 0 as frete,                            -- 8
 0 as seguro,                           -- 9
 0 as despesa,                          -- 10
 0 as desconto,                         -- 11
 sum(i.VLR_TOTAL) as totalprodutos,     -- 12
 0 as pesoliquido,                      -- 13
 0 as pesobruto,                        -- 14
 'ORCAMENTO' as natureza,               -- 15
 '' as marca,                           -- 16
 0 as volume,                           -- 17
 fp.descricao as formapag,              -- 18
 parc.descricao as parcelamento,        -- 19
 parc.n_parcelas as parcelas,           -- 20
 sum(i.VLR_TOTAL) as totalnota,         -- 21
 '' as status,                       -- 22
 'OR' as fim,                          -- 23
 null,                           -- 24
 0 as vlr_juros,                        -- 25
 sum(i.VLR_TOTAL) as vlrtotalnfjuros,   -- 26
 p.ID_CLIENTE,                          -- 27
 0,0,0,0,0,0,0,
 0 as ccusto,
 marcado_mt as marcado,
 '' as statusdav,
 'OR' as nfmodelo,
 sum(0) as custoTotal,
 case when statusimp is null then '' else 'Impresso' end, coalesce(index_mt,0) as index_mt
from TB_PEDIDO_VENDA p
left join tb_cliente cli on cli.id_cliente = p.id_cliente
left join tb_funcionario func on func.id_funcionario = p.id_vendedor
left join tb_ped_venda_item i on i.ID_PEDIDO = p.ID_PEDIDO
left join tb_parcelamento parc on parc.id_parcela = p.id_parcela
left join tb_forma_pagto_sis fp on fp.id_fmapgto = p.id_fmapgto
group by
 1,2,3,4,5,6,7,13,14,15,16,17,18,19,20,22,23,24,25,27,28,29,30,31,32,33,34,35,36,37,38,40,41

^


CREATE OR ALTER PROCEDURE XX_CONCATENA_CLIENTES
(
  CODIGO INTEGER
)
RETURNS
(
  ID INTEGER,
  NOME VARCHAR(100),
  ENDERECO VARCHAR(200),
  CIDADE VARCHAR(50),
  RESIDENCIAL VARCHAR(50),
  CELULAR VARCHAR(50),
  COMERCIAL VARCHAR(50),
  CPFCNPJ VARCHAR(100),
  CONTATO VARCHAR(60)
)
AS
  DECLARE VARIABLE CLIPF VARCHAR(50);
  DECLARE VARIABLE CLIPJ VARCHAR(50);
  DECLARE VARIABLE IDCLI INTEGER = 0;
begin
for
select cli.id_cliente,cli.id_cliente||' - '||cli.nome, coalesce(end_tipo,'')|| ' '||coalesce(end_lograd,'')|| ' - '||coalesce(end_comple,'')||', '||coalesce(end_numero,0)||' - '||coalesce(end_bairro,'') as Endereco, :CPFCNPJ,
'Cep: '||coalesce(cli.end_cep,'')||' - '||coalesce(cid.nome,'')||' - '||cid.sigla_uf as cidade,'('||coalesce(ddd_resid,'')||')'||coalesce(fone_resid,'') as FoneResidencial,
'('||coalesce(ddd_celul,'')||')'||coalesce(fone_celul,'') as FoneCelular,'('||coalesce(ddd_comer,'')||')'||coalesce(fone_comer,'') as FoneComercial,contato as Contato from tb_cliente cli
left join tb_cidade_sis  cid on cid.id_cidade = cli.id_cidade
left join tb_cli_Pf pf on pf.id_Cliente = cli.id_cliente
left join tb_Cli_pj pj on pj.id_cliente = cli.id_cliente order by cli.id_cliente asc
INTO :id,:nome,:endereco,:cpfcnpj,:cidade,:residencial,:celular,:comercial,:contato
  do
     begin
          select cpf,id_Cliente from tb_cli_pf pf where pf.id_cliente = :id into :clipf,:idcli;
          select cnpj,id_cliente from tb_cli_pj pj where pj.id_cliente = :id into :clipj,:idcli;
          if ((clipf <> '') and (idcli > 0)) then
          begin
               CPFCNPJ = 'CPF: '||''||:clipf;
               clipf = '';
               end
                 else
                   if ((clipj <> '') and (idcli > 0)) then
                   begin
                       CPFCNPJ = 'CNPJ: '||''||:clipj;
                       clipj = '';
                       end

                 else
                 begin
                 CPFCNPJ = '';
                 idcli = 0;
        end
      suspend;
     end
  end
^

