/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBBKP_EXCLUSAO;

CREATE TABLE TBBKP_EXCLUSAO (
    ID         INTEGER NOT NULL,
    TABELA     VARCHAR(100),
    TABELA_ID  INTEGER,
    DATAHORA   TIMESTAMP DEFAULT 'NOW',
    DADOS      BLOB SUB_TYPE 1 SEGMENT SIZE 80,
	USUARIO_ID INTEGER
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBBKP_EXCLUSAO ADD CONSTRAINT PK_TBBKP_EXCLUSAO PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBBKP_EXCLUSAO_01 ON TBBKP_EXCLUSAO (TABELA);
CREATE INDEX ITBBKP_EXCLUSAO_02 ON TBBKP_EXCLUSAO (TABELA_ID);
CREATE INDEX ITBBKP_EXCLUSAO_03 ON TBBKP_EXCLUSAO (USUARIO_ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBBKP_EXCLUSAO_B01I */
CREATE OR ALTER TRIGGER TTBBKP_EXCLUSAO_B01I FOR TBBKP_EXCLUSAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN

	IF (NEW.USUARIO_ID IS NULL) THEN
    BEGIN
        NEW.USUARIO_ID = FN_CURRENT_USER_ID();
    END

    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBBKP_EXCLUSAO, 1);
END
^

SET TERM ; ^

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPI_BACKUP (
    TABELA VARCHAR(100),
    TABELA_ID INTEGER,
    DADOS BLOB SUB_TYPE 1 SEGMENT SIZE 80)
as
begin
    IF ( TABELA <> '' AND TABELA_ID > 0 AND DADOS <> '' ) THEN
    BEGIN
        INSERT INTO TBBKP_EXCLUSAO (
            TABELA,
            TABELA_ID,
            DADOS
        ) VALUES (
            :TABELA,
            :TABELA_ID,
            :DADOS
        );
    END
end
^

SET TERM ; ^

COMMENT ON PARAMETER SPI_BACKUP.TABELA IS
'Nome da tabela que sera armazenado o historico';

COMMENT ON PARAMETER SPI_BACKUP.TABELA_ID IS
'Id do registro do historico';

COMMENT ON PARAMETER SPI_BACKUP.DADOS IS
'Informacoes para armazenamento';

/********************************************************************************************/
/********************************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbconferencia_b02d for tbconferencia
active before delete position 1
AS
DECLARE VARIABLE DADOS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
BEGIN
    DELETE FROM TBCONFERENCIA_ITEM A WHERE A.CONFERENCIA_ID = OLD.ID; /* Exclui os registros filhos */

    DADOS = COALESCE((
        FN_TO_JSON_V2(
            FN_TO_PAR_JSON_V2('ID', OLD.ID),
            FN_TO_PAR_JSON_V2('TABELA', OLD.TABELA),
            FN_TO_PAR_JSON_V2('TABELA_ID', OLD.TABELA_ID),
            FN_TO_PAR_JSON_V2('DATAHORA', OLD.DATAHORA),
            FN_TO_PAR_JSON_V2('USUARIO_ID', OLD.USUARIO_ID),
            FN_TO_PAR_JSON_V2('STATUS', OLD.STATUS),
            FN_TO_PAR_JSON_V2('JUSTIFICATIVA', OLD.JUSTIFICATIVA),
            FN_TO_PAR_JSON_V2('DATAHORA_FIM', OLD.DATAHORA_FIM),
            FN_TO_PAR_JSON_V2('SEQUENCIA', OLD.SEQUENCIA)
        )), '');

    EXECUTE PROCEDURE SPI_BACKUP('TBCONFERENCIA', OLD.ID, DADOS);
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbconferencia_item_b02d for tbconferencia_item
active before delete position 1
AS
DECLARE VARIABLE DADOS BLOB SUB_TYPE 1 SEGMENT SIZE 80;
BEGIN

    DADOS = COALESCE((
        FN_TO_JSON_V2(
            FN_TO_PAR_JSON_V2('ID', OLD.ID),
            FN_TO_PAR_JSON_V2('CONFERENCIA_ID', OLD.CONFERENCIA_ID),
            FN_TO_PAR_JSON_V2('PRODUTO_ID', OLD.PRODUTO_ID),
            FN_TO_PAR_JSON_V2('TAMANHO', OLD.TAMANHO),
            FN_TO_PAR_JSON_V2('QTD_TOTAL', OLD.QTD_TOTAL),
            FN_TO_PAR_JSON_V2('QTD_CONFERIDA', OLD.QTD_CONFERIDA),
            FN_TO_PAR_JSON_V2('TABELA_ITEM_ID', OLD.TABELA_ITEM_ID),
            FN_TO_PAR_JSON_V2('QTD_ALOCADA', OLD.QTD_ALOCADA),
            FN_TO_PAR_JSON_V2('QTD_DISPONIVEL', OLD.QTD_DISPONIVEL),
            FN_TO_PAR_JSON_V2('STATUS', OLD.STATUS),
            FN_TO_PAR_JSON_V2('NF_ID', OLD.NF_ID)
        )), '');

    EXECUTE PROCEDURE SPI_BACKUP('TBCONFERENCIA_ITEM', OLD.ID, DADOS);
END
^

SET TERM ; ^

/*************************************************************************/
/*************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbpedido_item_a01u for tbpedido_item
active after update position 1
AS
DECLARE VARIABLE CONFERENCIA_ITEM_ID INTEGER;
DECLARE VARIABLE CONFERENCIA_ID INTEGER;
DECLARE VARIABLE STATUS CHAR(1);

DECLARE VARIABLE SALDO_DISPONIVEL NUMERIC(15,4);
DECLARE VARIABLE QTD_TOTAL_CONF INTEGER;
DECLARE VARIABLE QTD_FATURADA_CONF INTEGER;
BEGIN
    
    IF(OLD.QTD_EST <> NEW.QTD_EST) THEN
    BEGIN

        SELECT FIRST 1
            K.ID,
            K.CONFERENCIA_ID,
            COALESCE(K.STATUS, '0') AS STATUS
        FROM TBCONFERENCIA_ITEM K, TBCONFERENCIA C
        WHERE K.CONFERENCIA_ID = C.ID
        AND C.TABELA = 'PED'
        AND C.TABELA_ID = NEW.PEDIDO
        AND C.STATUS >= 2
        AND K.TABELA_ITEM_ID = NEW.CONTROLE INTO :CONFERENCIA_ITEM_ID, :CONFERENCIA_ID, :STATUS;

        IF(COALESCE(:CONFERENCIA_ITEM_ID, 0) > 0) THEN
        BEGIN

            IF (:STATUS = '0') THEN
            BEGIN

                IF (NEW.QTD_EST = 0) THEN
                BEGIN
                
                    :SALDO_DISPONIVEL = COALESCE((SELECT FIRST 1
                        IIF((FN_ESTOQUE_SALDO_DISPONIVEL(B.ESTABELECIMENTO_CODIGO, B.LOCALIZACAO_ID, B.PRODUTO_CODIGO, B.TAMANHO)) > B.QUANTIDADE, B.QUANTIDADE, (FN_ESTOQUE_SALDO_DISPONIVEL(B.ESTABELECIMENTO_CODIGO, B.LOCALIZACAO_ID, B.PRODUTO_CODIGO, B.TAMANHO)))
                    FROM TBCONFERENCIA A, TBCONFERENCIA_ITEM T
                    INNER JOIN TBPEDIDO_ITEM B ON B.CONTROLE = T.TABELA_ITEM_ID
                    WHERE A.ID = T.CONFERENCIA_ID
                    AND A.TABELA = 'PED'
                    AND T.ID = :CONFERENCIA_ITEM_ID), 0);
                
                END ELSE
                BEGIN
                
                    :SALDO_DISPONIVEL = NEW.QTD_EST;
                
                END
        
                UPDATE TBCONFERENCIA_ITEM T SET
                    T.QTD_ALOCADA       = NEW.QTD_EST,
                    T.QTD_DISPONIVEL    = :SALDO_DISPONIVEL
                WHERE T.ID = :CONFERENCIA_ITEM_ID;
    
            END

            /* Deve ser refeito nas operações das notas */
--            QTD_FATURADA_CONF = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID AND K.STATUS = 1), 0);
--            QTD_TOTAL_CONF    = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID), 0);
--                 -- exception "..." :QTD_FATURADA_CONF||' - '||:QTD_TOTAL_CONF;
--            IF (QTD_FATURADA_CONF = 0) THEN
--            BEGIN
--                UPDATE TBCONFERENCIA T SET
--                    T.STATUS = '2'
--                WHERE T.TABELA_ID = NEW.PEDIDO
--                AND T.TABELA = 'PED'
--                AND T.ID = :CONFERENCIA_ID;
--            END ELSE IF (QTD_FATURADA_CONF = QTD_TOTAL_CONF) THEN
--            BEGIN
--                UPDATE TBCONFERENCIA T SET
--                    T.STATUS = '4'
--                WHERE T.TABELA_ID = NEW.PEDIDO
--                AND T.TABELA = 'PED'
--                AND T.ID = :CONFERENCIA_ID;
--            END ELSE
--            BEGIN
--                UPDATE TBCONFERENCIA T SET
--                    T.STATUS = '5'
--                WHERE T.TABELA_ID = NEW.PEDIDO
--                AND T.TABELA = 'PED'
--                AND T.ID = :CONFERENCIA_ID;
--            END
        
        END
    
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPD_NFS_PEDIDO (
    NFS_CONTROLE integer,
    CONTROLE integer)
as
declare variable PEDIDO_ITEM_PE_CONTROLE integer;
declare variable QUANTIDADE numeric(10,5);
declare variable T01 numeric(10,5);
declare variable T02 numeric(10,5);
declare variable T03 numeric(10,5);
declare variable T04 numeric(10,5);
declare variable T05 numeric(10,5);
declare variable T06 numeric(10,5);
declare variable T07 numeric(10,5);
declare variable T08 numeric(10,5);
declare variable T09 numeric(10,5);
declare variable T10 numeric(10,5);
declare variable T11 numeric(10,5);
declare variable T12 numeric(10,5);
declare variable T13 numeric(10,5);
declare variable T14 numeric(10,5);
declare variable T15 numeric(10,5);
declare variable T16 numeric(10,5);
declare variable T17 numeric(10,5);
declare variable T18 numeric(10,5);
declare variable T19 numeric(10,5);
declare variable T20 numeric(10,5);
declare variable CONFERENCIA_ITEM_ID integer;
declare variable CONFERENCIA_ID integer;
declare variable QTD_FATURADA_CONF integer;
declare variable QTD_TOTAL_CONF integer;
BEGIN
SELECT A.PEDIDO_ITEM_PE_CONTROLE, A.QUANTIDADE,
A.T01,A.T02,A.T03,A.T04,A.T05,A.T06,A.T07,A.T08,A.T09,A.T10,
A.T11,A.T12,A.T13,A.T14,A.T15,A.T16,A.T17,A.T18,A.T19,A.T20

FROM TBNFS_ITEM A
WHERE
    A.PEDIDO_ITEM_PE_CONTROLE > 0
    AND A.PRODUTO_CODIGO > 0
    AND A.NFS_CONTROLE = :NFS_CONTROLE
    AND A.CONTROLE = :CONTROLE
INTO
:PEDIDO_ITEM_PE_CONTROLE, :QUANTIDADE,
:T01,:T02,:T03,:T04,:T05,:T06,:T07,:T08,:T09,:T10,:T11,:T12,:T13,:T14,
:T15,:T16,:T17,:T18,:T19,:T20;

    IF (:PEDIDO_ITEM_PE_CONTROLE > 0) THEN
        BEGIN
            
            SELECT FIRST 1
                K.ID,
                K.CONFERENCIA_ID
            FROM TBCONFERENCIA_ITEM K
            WHERE K.STATUS = 1
            AND K.NF_ID = :NFS_CONTROLE
            AND K.TABELA_ITEM_ID = :PEDIDO_ITEM_PE_CONTROLE INTO :CONFERENCIA_ITEM_ID, :CONFERENCIA_ID;

            IF(:CONFERENCIA_ITEM_ID > 0) THEN
           BEGIN

                UPDATE TBCONFERENCIA_ITEM K SET K.STATUS = 0, K.NF_ID = 0 WHERE K.TABELA_ITEM_ID = :PEDIDO_ITEM_PE_CONTROLE AND K.ID = :CONFERENCIA_ITEM_ID;

                :QTD_FATURADA_CONF  = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID AND K.STATUS = 1), 0);
                :QTD_TOTAL_CONF     = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID), 0);

                IF (:QTD_FATURADA_CONF = 0) THEN
                BEGIN
                    UPDATE TBCONFERENCIA T SET
                        T.STATUS = '2'
                    WHERE T.ID = :CONFERENCIA_ID;
                END ELSE
                BEGIN
                    UPDATE TBCONFERENCIA T SET
                        T.STATUS = '5'
                    WHERE T.ID = :CONFERENCIA_ID;
                END
           END

           UPDATE TBPEDIDO_ITEM_SALDO SET
           QUANTIDADE = QUANTIDADE + :QUANTIDADE,
           T01 = T01+ :T01, T02 = T02+ :T02, T03 = T03+ :T03, T04 = T04+ :T04,
           T05 = T05+ :T05, T06 = T06+ :T06, T07 = T07+ :T07, T08 = T08+ :T08,
           T09 = T09+ :T09, T10 = T10+ :T10, T11 = T11+ :T11, T12 = T12+ :T12,
           T13 = T13+ :T13, T14 = T14+ :T14, T15 = T15+ :T15, T16 = T16+ :T16,
           T17 = T17+ :T17, T18 = T18+ :T18, T19 = T19+ :T19, T20 = T20+ :T20
           WHERE PEDIDO_ITEM_CONTROLE = :PEDIDO_ITEM_PE_CONTROLE;

        END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPU_NFS_PEDIDO (
    NFS_CONTROLE integer,
    CONTROLE integer)
as
declare variable PRODUTO_CODIGO integer;
declare variable PEDIDO_ITEM_PE_CONTROLE integer;
declare variable IQUANTIDADE numeric(10,5);
declare variable IT01 numeric(10,5);
declare variable IT02 numeric(10,5);
declare variable IT03 numeric(10,5);
declare variable IT04 numeric(10,5);
declare variable IT05 numeric(10,5);
declare variable IT06 numeric(10,5);
declare variable IT07 numeric(10,5);
declare variable IT08 numeric(10,5);
declare variable IT09 numeric(10,5);
declare variable IT10 numeric(10,5);
declare variable IT11 numeric(10,5);
declare variable IT12 numeric(10,5);
declare variable IT13 numeric(10,5);
declare variable IT14 numeric(10,5);
declare variable IT15 numeric(10,5);
declare variable IT16 numeric(10,5);
declare variable IT17 numeric(10,5);
declare variable IT18 numeric(10,5);
declare variable IT19 numeric(10,5);
declare variable IT20 numeric(10,5);
declare variable CONFERENCIA_ITEM_ID integer;
declare variable CONFERENCIA_ID integer;
declare variable QTD_FATURADA_CONF integer;
declare variable QTD_TOTAL_CONF integer;
BEGIN

    FOR
    SELECT A.PRODUTO_CODIGO, A.PEDIDO_ITEM_PE_CONTROLE, A.QUANTIDADE,
    A.T01,A.T02,A.T03,A.T04,A.T05,A.T06,A.T07,A.T08,A.T09,A.T10,
    A.T11,A.T12,A.T13,A.T14,A.T15,A.T16,A.T17,A.T18,A.T19,A.T20

    FROM TBNFS_ITEM A
    WHERE A.PEDIDO_ITEM_PE_CONTROLE > 0
      AND A.NFS_CONTROLE = :NFS_CONTROLE
      AND A.CONTROLE = :CONTROLE
    INTO
    :PRODUTO_CODIGO, :PEDIDO_ITEM_PE_CONTROLE,:IQUANTIDADE,
    :IT01,:IT02,:IT03,:IT04,:IT05,:IT06,:IT07,:IT08,:IT09,:IT10,
    :IT11,:IT12,:IT13,:IT14,:IT15,:IT16,:IT17,:IT18,:IT19,:IT20
    DO BEGIN

       IF (:PRODUTO_CODIGO > 0) THEN
           BEGIN

                SELECT FIRST 1
                    K.ID,
                    K.CONFERENCIA_ID
                FROM TBCONFERENCIA_ITEM K
                WHERE K.STATUS <> 1
                AND ((K.NF_ID IS NULL) OR (K.NF_ID = 0))
                AND K.TABELA_ITEM_ID = :PEDIDO_ITEM_PE_CONTROLE INTO :CONFERENCIA_ITEM_ID, :CONFERENCIA_ID;

               IF(:CONFERENCIA_ITEM_ID > 0) THEN
               BEGIN

                    UPDATE TBCONFERENCIA_ITEM K SET K.STATUS = 1, K.NF_ID = :NFS_CONTROLE WHERE K.TABELA_ITEM_ID = :PEDIDO_ITEM_PE_CONTROLE AND K.ID = :CONFERENCIA_ITEM_ID;

                    :QTD_FATURADA_CONF  = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID AND K.STATUS = 1), 0);
                    :QTD_TOTAL_CONF     = COALESCE((SELECT COUNT(K.CONFERENCIA_ID) FROM TBCONFERENCIA_ITEM K WHERE K.CONFERENCIA_ID = :CONFERENCIA_ID), 0);

                    IF (:QTD_FATURADA_CONF = :QTD_TOTAL_CONF) THEN
                    BEGIN
                        UPDATE TBCONFERENCIA T SET
                            T.STATUS = '4'
                        WHERE T.ID = :CONFERENCIA_ID;
                    END ELSE
                    BEGIN
                        UPDATE TBCONFERENCIA T SET
                            T.STATUS = '5'
                        WHERE T.ID = :CONFERENCIA_ID;
                    END

               END
    
               UPDATE TBPEDIDO_ITEM_SALDO SET
               QUANTIDADE = QUANTIDADE - :IQUANTIDADE,
               T01 = T01 - :IT01,   T02 = T02 - :IT02,   T03 = T03 - :IT03,
               T04 = T04 - :IT04,   T05 = T05 - :IT05,   T06 = T06 - :IT06,
               T07 = T07 - :IT07,   T08 = T08 - :IT08,   T09 = T09 - :IT09,
               T10 = T10 - :IT10,   T11 = T11 - :IT11,   T12 = T12 - :IT12,
               T13 = T13 - :IT13,   T14 = T14 - :IT14,   T15 = T15 - :IT15,
               T16 = T16 - :IT16,   T17 = T17 - :IT17,   T18 = T18 - :IT18,
               T19 = T19 - :IT19,   T20 = T20 - :IT20
               WHERE PEDIDO_ITEM_CONTROLE = :PEDIDO_ITEM_PE_CONTROLE;

           END

       END

END
^

SET TERM ; ^

