CREATE TABLE TBINTEGRACAO_LACLOFIT (
    ID INTEGER NOT NULL,
    URL VARCHAR(300),
    VITRINI INTEGER,
    TRANSACAO_ID INTEGER,
    DATA_EXECUCAO TIMESTAMP);

ALTER TABLE TBINTEGRACAO_LACLOFIT
ADD CONSTRAINT PK_TBINTEGRACAO_LACLOFIT
PRIMARY KEY (ID);

CREATE SEQUENCE GTBINTEGRACAO_LACLOFIT;

create trigger ttbintegracao_laclofit_b01i for tbintegracao_laclofit
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbintegracao_laclofit,1);
end;

CREATE TABLE TBINTEGRACAO_LACLOFIT_LOG (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(300),
    DATA_HORA TIMESTAMP DEFAULT 'now');

ALTER TABLE TBINTEGRACAO_LACLOFIT_LOG
ADD CONSTRAINT PK_TBINTEGRACAO_LACLOFIT_LOG
PRIMARY KEY (ID);

CREATE SEQUENCE GTBINTEGRACAO_LACLOFIT_LOG;

create trigger ttbintegracao_laclofit_log_b01i for tbintegracao_laclofit_log
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbintegracao_laclofit_log,1);
end;

CREATE TABLE TBINTEGRACAO_LACLOFIT_EXEC (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    INTERVALO INTEGER DEFAULT 0,
    HORA_EXECUCAO VARCHAR(100) DEFAULT '',
    INTEGRACAO_ID INTEGER);

ALTER TABLE TBINTEGRACAO_LACLOFIT_EXEC
ADD CONSTRAINT PK_TBINTEGRACAO_LACLOFIT_EXEC
PRIMARY KEY (ID);

CREATE SEQUENCE GTBINTEGRACAO_LACLOFIT_EXEC;

create trigger ttbintegracao_lac_exec_b01i for tbintegracao_laclofit_exec
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbintegracao_laclofit_exec,1);
end;

ALTER TABLE TBINTEGRACAO_LACLOFIT_LOG
    ADD INTEGRACAO_ID INTEGER;

ALTER TABLE TBINTEGRACAO_LACLOFIT
    ADD ESTABELECIMENTO_ID INTEGER;

ALTER TABLE TBINTEGRACAO_LACLOFIT_EXEC
    ADD STATUS INTEGER,
    ADD DATA_INICIADO TIMESTAMP;

ALTER TABLE TBINTEGRACAO_LACLOFIT_EXEC
    ADD DATA_EXECUCAO TIMESTAMP;

CREATE trigger ttbintegracao_laclofit_exc_b01u for tbintegracao_laclofit_exec
active before update position 0
AS
begin
  if(new.status = 0 and old.status = 1)then
  begin
    new.data_execucao = current_timestamp;
  end

  if(new.status = 1 and old.status = 0)then
  begin
    new.data_iniciado = current_timestamp;
  end
end;

ALTER TABLE TBINTEGRACAO_LACLOFIT_EXEC
    ADD URL VARCHAR(100);

CREATE OR ALTER trigger ttbcliente_b1i_02 for tbcliente
active before insert position 1
as
declare variable MENSAGEM1 Varchar(7000);
declare variable MENSAGEM2 Varchar(7000);
declare variable descricao1 Varchar(200);
declare variable descricao2 Varchar(200);
declare variable empresa Varchar(100);
declare variable produto Varchar(100);
declare variable usuario_id integer;
declare variable email Varchar(300);
declare variable url Varchar(300);
declare variable uf Varchar(10);
declare variable user_notificar Varchar(300);
declare variable flag Varchar(50);
begin

  if (new.ecommerce = 1 or new.ecommerce = 2) then
  begin

    if (new.ecommerce = 1) then
    begin
       :user_notificar = coalesce(FN_PARAMETRO('SISTEMA.USUARIOS_NOTIFICAR_ECOMMERCE'),0);
       :flag = 'ECOMMERCE';
    end

    if (new.ecommerce = 2) then
    begin
       :user_notificar = coalesce(FN_PARAMETRO('SISTEMA.USUARIOS_NOTIFICAR_LACLOFIT'),0);
       :flag = 'LACLOFIT';
    end

    :empresa = coalesce(new.razaosocial,'');
    :uf      = coalesce(new.uf,'');

    :url = coalesce((select first 1 n.valor_ext from tbcontrole_n n where n.id = 279 ),'https://gc.delfa.com.br');

    :descricao1 = 'Novo Cliente Cadastrado ('||:flag||')';
    :descricao2 = 'Novo Cliente Cadastrado ('||:flag||'), Cliente:'||:empresa||', UF:'||:uf;

    :MENSAGEM1  = 'Cliente: '||:empresa||'<br>'||
                  '<a href="'||:url||'/_12090?EMPRESA_ID='||new.codigo||'" target="_blank">Abrir Cliente</a>';
    
     :MENSAGEM2 = '';
    
     for
         select
            u.codigo,
            u.email
        from tbusuario u, (select OSPLIT from split(:user_notificar,',')) n
        
        where u.codigo = n.OSPLIT
     into
        :usuario_id,
        :email
     do begin
    
        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
               VALUES (1, :usuario_id, 0, 0, :descricao1, 0, 0, current_timestamp, -1, current_timestamp, 0, 'TBCLIENTE', new.codigo, :MENSAGEM1||:MENSAGEM2);
    
    
        INSERT INTO TBEMAIL (EMAIL, USUARIO_ID, ASSUNTO, STATUS, DATAHORA, DATAHORA_ENVIO, FLAG, PAINEL_ID, CORPO)
             VALUES (:email, 0, :descricao2, 0, current_timestamp, null, 0, 0, :MENSAGEM1||:MENSAGEM2);
    
    
     end
  end

  if (NEW.BLOQUEIA_PEDIDO = '1' or NEW.BLOQUEIA_NOTAFISCAL = '1') then
      Begin
      New.Data_Bloqueio = Current_Date;
      End

  if (New.Etiqueta is NUll or Char_Length(New.Etiqueta) = 0) then
      New.Etiqueta = Left(Replace(New.razaosocial,' ',''),6);

End;


CREATE OR ALTER trigger ttbempresa_b03i for tbempresa
active before insert position 0
AS
declare variable tipo_cliente integer;
declare variable representante integer;
begin
    --new.endereco2 = new.endereco;
   --- new.numero2 = new.numero;
    
    if( (new.ecommerce = 1 or new.ecommerce = 2) or new.replicar_cliente = 1 )then
    begin

        if(new.ecommerce = 1)then
        begin
           new.conta_contabil = FN_PARAMETRO('SISTEMA.CONTA_ECOMMERCE');

           :representante = iif(new.representante_id = 0, coalesce(FN_PARAMETRO('SISTEMA.REPECOMMERCE'),0), new.representante_id);

           :tipo_cliente = coalesce(FN_PARAMETRO('SISTEMA.TIPOECOMMERCE'),59);
        end

        if(new.ecommerce = 2)then
        begin
           new.conta_contabil = FN_PARAMETRO('SISTEMA.CONTA_LACLOFIT');

           :representante = iif(new.representante_id = 0, coalesce(FN_PARAMETRO('SISTEMA.REPRESENTANTE_LACLOFIT'),0), new.representante_id);

           :tipo_cliente = coalesce(FN_PARAMETRO('SISTEMA.TIPO_CLIENTE_LACLOFIT'),62);
        end

       INSERT INTO TBCLIENTE (
             RAZAOSOCIAL,
             CODIGO,
             REPRESENTANTE_CODIGO,
             TRANSPORTADORA_CODIGO,
             PAGAMENTO_CONDICAO,
             PAGAMENTO_FORMA,
             STATUS,
             UF,
             BLOQUEIA_PEDIDO,
             BLOQUEIA_NOTAFISCAL,
             REPRESENTANTE_COMISSAO,
             GRADE_SECUNDARIA,
             LIMITE_CREDITO,
             SUFRAMA,
             GRUPO,
             BOQUEIA_PAGAMENTO_CONDICAO,
             TIPO_CODIGO,
             VENDEDOR_CODIGO,
             MATRIZ_ID,
             VENDEDOR_COMISSAO,
             ATENDE_GRADE_COMPLETA,
             SUFRAMA_VENCIMENTO,
             LOJA,
             BLOQUEIO_DIAS_ATRASO,
             BLOQUEIO_DIAS_SEMMOV,
             TAG,
             PRIORIDADE,
             INATIVA_DIAS_SEMMOV,
             CONFIRMACAO_AUTOMATICA,
             PAFECF,
             FRETE,
             SUBSTITUIDO_TRIBUTARIO,
             DATA_BLOQUEIO,
             BLOQUEIO_MOTIVO,
             NOTIFICAR_DIAS_ATRASO,
             NOTIFICAR_ATRASO,
             ETIQUETA,
             EMPRESA_VINCULO,
             BLOQUEIO_DIAS_ATRASO_NF,
             ECOMMERCE
       ) VALUES (
             new.razaosocial,
             new.codigo,
             :representante,
             0,
             0,
             0,
             '1',
             new.uf,
             '',
             '',
             0,
             '0',
             0,
             '',
             '0',
             '',
             1,
             new.vendedor_id,
             0,
             0,
             '',
             NULL,
             '',
             0,
             0,
             '',
             10,
             0,
             '',
             '',
             0,
             '0',
             NULL,
             '',
             0,
             0,
             substring(new.razaosocial from 1 for 6),
             new.codigo,
             0,
             iif(new.replicar_cliente = 1, 0, new.ecommerce)
        );

        INSERT INTO TBEMPRESA_VINCULO_TIPO (EMPRESA_VINCULO, CLIENTE_TIPO_ID, STATUS) VALUES (NEW.codigo, :tipo_cliente, 1);
    end
end;

create or alter function fn_digitoean (
    numero char(15))
returns decimal(1,0)
as
declare variable soma integer;
declare variable digito numeric(1,0);
begin
        SOMA = 0;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  1 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  2 FOR 1)) as Integer)*3;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  3 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  4 FOR 1)) as Integer)*3;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  5 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  6 FOR 1)) as Integer)*3;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  7 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  8 FOR 1)) as Integer)*3;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM  9 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM 10 FOR 1)) as Integer)*3;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM 11 FOR 1)) as Integer)*1;
        SOMA = :SOMA + Cast((SubString( :NUMERO FROM 12 FOR 1)) as Integer)*3;
        IF ((((:SOMA/10.00)-(:SOMA/10)) *10)=0) THEN
           BEGIN
             DIGITO =  0 ;
           END
           ELSE
           BEGIN
             DIGITO =   10-( ((:SOMA/10.00)-(:SOMA/10)) *10   ) ;
           END


  return DIGITO;
end;

UPDATE OR INSERT INTO TBPERFIL (TABELA, ID, DESCRICAO, CAP_PRODUTIVA, LOCALIZACAO_ID, TRATAMENTO_PEDIDO_MP, DESCRICAO_COMPLETA, QTD_MIN, QTD_MULT, QTD_TIPO, AGRUPAMENTO, EFIC_MINIMA, PONTO_REPOSICAO, FAMILIA_ID, COR_DIFERENCIADA, COR_SOBENCOMENDA, QTD_MIN_SOBENC, QTD_MULT_SOBENC, PZO1_PED_D1, PZO1_PED_D2, PZO1_PED_D3, PZO1_PED_D4, PZO1_PED_D5, PZO1_PED_D6, PZO1_PED_D7, PZO2_PED_D1, PZO2_PED_D2, PZO2_PED_D3, PZO2_PED_D4, PZO2_PED_D5, PZO2_PED_D6, PZO2_PED_D7, PZOD1_PED_D1, PZOD1_PED_D2, PZOD1_PED_D3, PZOD1_PED_D4, PZOD1_PED_D5, PZOD1_PED_D6, PZOD1_PED_D7, PZOD2_PED_D1, PZOD2_PED_D2, PZOD2_PED_D3, PZOD2_PED_D4, PZOD2_PED_D5, PZOD2_PED_D6, PZOD2_PED_D7, PZOS1_PED_D1, PZOS1_PED_D2, PZOS1_PED_D3, PZOS1_PED_D4, PZOS1_PED_D5, PZOS1_PED_D6, PZOS1_PED_D7, PZOS2_PED_D1, PZOS2_PED_D2, PZOS2_PED_D3, PZOS2_PED_D4, PZOS2_PED_D5, PZOS2_PED_D6, PZOS2_PED_D7, TRATAMENTO_PEDIDO, AGRUPAMENTO2, PZOE1_PED_D1, PZOE1_PED_D2, PZOE1_PED_D3, PZOE1_PED_D4, PZOE1_PED_D5, PZOE1_PED_D6, PZOE1_PED_D7, PZOE2_PED_D1, PZOE2_PED_D2, PZOE2_PED_D3, PZOE2_PED_D4, PZOE2_PED_D5, PZOE2_PED_D6, PZOE2_PED_D7, EFIC_A, EFIC_B, PERDA_A, PERDA_B, ENDERECAMENTO_SOBRA, TIPO_REPOSICAO, COR_BASICA, PZOI1_PED_D1, PZOI1_PED_D2, PZOI1_PED_D3, PZOI1_PED_D4, PZOI1_PED_D5, PZOI1_PED_D6, PZOI1_PED_D7, PZOI2_PED_D1, PZOI2_PED_D2, PZOI2_PED_D3, PZOI2_PED_D4, PZOI2_PED_D5, PZOI2_PED_D6, PZOI2_PED_D7, ALOCAR_MULTIPLO, TELA_ADD_PEDIDO, AMOSTRA, FAMILIA_PEDIDO)
                        VALUES ('SKU', '|', 'LACLOFIT', 0, 34, '0   ', 'LACLOFIT', 0, 0, '', 'LACLOFIT', 100, 0, 147, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2   ', 'LACLOFIT', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '    ', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0   ', 1, 0, 0)
                      MATCHING (TABELA, ID);
					  
UPDATE OR INSERT INTO TBPERFIL (TABELA, ID, DESCRICAO, CAP_PRODUTIVA, LOCALIZACAO_ID, TRATAMENTO_PEDIDO_MP, DESCRICAO_COMPLETA, QTD_MIN, QTD_MULT, QTD_TIPO, AGRUPAMENTO, EFIC_MINIMA, PONTO_REPOSICAO, FAMILIA_ID, COR_DIFERENCIADA, COR_SOBENCOMENDA, QTD_MIN_SOBENC, QTD_MULT_SOBENC, PZO1_PED_D1, PZO1_PED_D2, PZO1_PED_D3, PZO1_PED_D4, PZO1_PED_D5, PZO1_PED_D6, PZO1_PED_D7, PZO2_PED_D1, PZO2_PED_D2, PZO2_PED_D3, PZO2_PED_D4, PZO2_PED_D5, PZO2_PED_D6, PZO2_PED_D7, PZOD1_PED_D1, PZOD1_PED_D2, PZOD1_PED_D3, PZOD1_PED_D4, PZOD1_PED_D5, PZOD1_PED_D6, PZOD1_PED_D7, PZOD2_PED_D1, PZOD2_PED_D2, PZOD2_PED_D3, PZOD2_PED_D4, PZOD2_PED_D5, PZOD2_PED_D6, PZOD2_PED_D7, PZOS1_PED_D1, PZOS1_PED_D2, PZOS1_PED_D3, PZOS1_PED_D4, PZOS1_PED_D5, PZOS1_PED_D6, PZOS1_PED_D7, PZOS2_PED_D1, PZOS2_PED_D2, PZOS2_PED_D3, PZOS2_PED_D4, PZOS2_PED_D5, PZOS2_PED_D6, PZOS2_PED_D7, TRATAMENTO_PEDIDO, AGRUPAMENTO2, PZOE1_PED_D1, PZOE1_PED_D2, PZOE1_PED_D3, PZOE1_PED_D4, PZOE1_PED_D5, PZOE1_PED_D6, PZOE1_PED_D7, PZOE2_PED_D1, PZOE2_PED_D2, PZOE2_PED_D3, PZOE2_PED_D4, PZOE2_PED_D5, PZOE2_PED_D6, PZOE2_PED_D7, EFIC_A, EFIC_B, PERDA_A, PERDA_B, ENDERECAMENTO_SOBRA, TIPO_REPOSICAO, COR_BASICA, PZOI1_PED_D1, PZOI1_PED_D2, PZOI1_PED_D3, PZOI1_PED_D4, PZOI1_PED_D5, PZOI1_PED_D6, PZOI1_PED_D7, PZOI2_PED_D1, PZOI2_PED_D2, PZOI2_PED_D3, PZOI2_PED_D4, PZOI2_PED_D5, PZOI2_PED_D6, PZOI2_PED_D7, ALOCAR_MULTIPLO, TELA_ADD_PEDIDO, AMOSTRA, FAMILIA_PEDIDO)
                        VALUES ('PED', 'L', 'LACLOFIT', 0, 35, '2', 'PEDIDOS LACLOFIT', 0, 0, '    ', '', 100, 0, 147, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2   ', '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '    ', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0   ', 1, 0, 0)
                      MATCHING (TABELA, ID);
					  

CREATE OR ALTER trigger ttbpedido_b10i for tbpedido
active before insert position 0
AS
begin
  if(new.ecommerce = 1)then
  begin
    new.representante_codigo = coalesce((SELECT FIRST 1 P.valor_ext  FROM tbcontrole_n P WHERE P.id = 272),0);
    new.familia_codigo = null;
    new.status_ecommerce = 2;
    new.status_pagamento = 1;
    new.codigo_rastreio = '';
    new.cte = '';

    UPDATE OR INSERT INTO TBUPDATE_ECOMMERCE (TABELA, TABELA_ID, SUB_ID)
            VALUES ('TBPEDIDO', new.pedido, 0)
          MATCHING (TABELA, TABELA_ID, SUB_ID);
  end

  if(new.ecommerce = 2)then
  begin
    new.representante_codigo = coalesce( fn_parametro('SISTEMA.REPRESENTANTE_LACLOFIT'),0);
    new.familia_codigo = null;
    new.status_ecommerce = 2;
    new.status_pagamento = 1;
    new.codigo_rastreio = '';
    new.cte = '';
  end
end;

ALTER TABLE TBINTEGRACAO_LACLOFIT_EXEC
    ADD TRANSACAO_ID INTEGER;

create view vwhoras ( hora )
as
select
   substring((cast('00:00' as time) + (LINHA_CORRENTE * 60)) from 1 for 5) as HORA
from sp_gerar_linhas(1440)
;

CREATE OR ALTER function fn_hora_multipla (
    hora time,
    minutos numeric(18,8))
returns boolean
as
declare variable div numeric(18,8);
begin

  div = mod(((extract(hour from cast(HORA as time)) * 60) + extract(minute from cast(HORA as time))), MINUTOS);


  return div = 0;
end;


CREATE OR ALTER procedure spi_log_integracao_laclofit (
    integracao_id integer,
    msg varchar(300))
returns (
    status varchar(10))
as
begin

  in autonomous transaction
  do
  begin
    INSERT INTO TBINTEGRACAO_LACLOFIT_LOG (DESCRICAO, DATA_HORA, INTEGRACAO_ID)
                               VALUES (:MSG, current_timestamp, :INTEGRACAO_ID);
  end

  STATUS = 'Gravado';
  suspend;
end;

