CREATE GENERATOR GTBCONFERENCIA_ITEM_GRUPO_ID;

CREATE TABLE TBCONFERENCIA_ITEM_GRUPO (
    ID                   INTEGER NOT NULL,
    CONFERENCIA_ID       INTEGER,
    CONFERENCIA_ITEM_ID  INTEGER NOT NULL,
    CONJUNTO             VARCHAR(10) NOT NULL,
    QUANTIDADE           NUMERIC(15,4),
	BLOQUEIO             INTEGER DEFAULT 0
);




/******************************************************************************/
/****                          Unique constraints                          ****/
/******************************************************************************/

ALTER TABLE TBCONFERENCIA_ITEM_GRUPO ADD CONSTRAINT UNQ1_TBCONFERENCIA_ITEM_GRUPO UNIQUE (ID);


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

ALTER TABLE TBCONFERENCIA_ITEM_GRUPO ADD CONSTRAINT PK_TBCONFERENCIA_ITEM_GRUPO PRIMARY KEY (CONFERENCIA_ITEM_ID, CONJUNTO);


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

CREATE INDEX ITBCONFERENCIA_ITEM_GRUPO_01 ON TBCONFERENCIA_ITEM_GRUPO (ID);
CREATE INDEX ITBCONFERENCIA_ITEM_GRUPO_02 ON TBCONFERENCIA_ITEM_GRUPO (CONFERENCIA_ITEM_ID);
CREATE INDEX ITBCONFERENCIA_ITEM_GRUPO_03 ON TBCONFERENCIA_ITEM_GRUPO (CONJUNTO);
CREATE INDEX ITBCONFERENCIA_ITEM_GRUPO_04 ON TBCONFERENCIA_ITEM_GRUPO (QUANTIDADE);
CREATE INDEX ITBCONFERENCIA_ITEM_GRUPO_05 ON TBCONFERENCIA_ITEM_GRUPO (CONFERENCIA_ID);


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



SET TERM ^ ;



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



/* Trigger: TTBCONFERENCIA_ITEM_GRUPO_B01D */
CREATE OR ALTER TRIGGER TTBCONFERENCIA_ITEM_GRUPO_B01D FOR TBCONFERENCIA_ITEM_GRUPO
ACTIVE BEFORE DELETE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE OLD_CONFERENCIA_ITEM VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CONFERENCIA_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.PRODUTO_ID, 7, '0')||' - '||B.DESCRICAO||' / '||FN_TAMANHO_PRODUTO(A.PRODUTO_ID, A.TAMANHO) FROM TBCONFERENCIA_ITEM A, TBPRODUTO B WHERE A.PRODUTO_ID = B.CODIGO AND A.ID = OLD.CONFERENCIA_ITEM_ID), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' ITEM '||OLD_CONFERENCIA_ITEM||' EXCLUIDO DO GRUPO '||OLD.CONJUNTO||' (QTD: '||OLD.QUANTIDADE||')';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', OLD.CONFERENCIA_ID, HISTORICO);
    END
END
^


/* Trigger: TTBCONFERENCIA_ITEM_GRUPO_B01I */
CREATE OR ALTER TRIGGER TTBCONFERENCIA_ITEM_GRUPO_B01I FOR TBCONFERENCIA_ITEM_GRUPO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBCONFERENCIA_ITEM_GRUPO_ID, 1);
END
^


/* Trigger: TTBCONFERENCIA_ITEM_GRUPO_B01U */
CREATE OR ALTER TRIGGER TTBCONFERENCIA_ITEM_GRUPO_B01U FOR TBCONFERENCIA_ITEM_GRUPO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_CONFERENCIA_ITEM VARCHAR(500);
DECLARE VARIABLE NEW_CONFERENCIA_ITEM VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CONFERENCIA_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.PRODUTO_ID, 7, '0')||' - '||B.DESCRICAO||' / '||FN_TAMANHO_PRODUTO(A.PRODUTO_ID, A.TAMANHO) FROM TBCONFERENCIA_ITEM A, TBPRODUTO B WHERE A.PRODUTO_ID = B.CODIGO AND A.ID = OLD.CONFERENCIA_ITEM_ID), '');
    NEW_CONFERENCIA_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.PRODUTO_ID, 7, '0')||' - '||B.DESCRICAO||' / '||FN_TAMANHO_PRODUTO(A.PRODUTO_ID, A.TAMANHO) FROM TBCONFERENCIA_ITEM A, TBPRODUTO B WHERE A.PRODUTO_ID = B.CODIGO AND A.ID = NEW.CONFERENCIA_ITEM_ID), '');

    IF (OLD.CONJUNTO IS DISTINCT FROM NEW.CONJUNTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CONJUNTO:'||OLD.CONJUNTO||'->'||NEW.CONJUNTO;
    END

    IF (OLD.QUANTIDADE IS DISTINCT FROM NEW.QUANTIDADE) THEN
    BEGIN
        HISTORICO = HISTORICO||' QUANTIDADE:'||OLD.QUANTIDADE||'->'||NEW.QUANTIDADE;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'GRUPO/CONJUNTO DO ITEM '||OLD_CONFERENCIA_ITEM||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', OLD.CONFERENCIA_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBCONFERENCIA_ITEM_GRUPO_B02I */
CREATE OR ALTER TRIGGER TTBCONFERENCIA_ITEM_GRUPO_B02I FOR TBCONFERENCIA_ITEM_GRUPO
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE NEW_CONFERENCIA_ITEM VARCHAR(500);
BEGIN

    HISTORICO = '';

    NEW_CONFERENCIA_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.PRODUTO_ID, 7, '0')||' - '||B.DESCRICAO||' / '||FN_TAMANHO_PRODUTO(A.PRODUTO_ID, A.TAMANHO) FROM TBCONFERENCIA_ITEM A, TBPRODUTO B WHERE A.PRODUTO_ID = B.CODIGO AND A.ID = NEW.CONFERENCIA_ITEM_ID), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' ITEM '||NEW_CONFERENCIA_ITEM||' INCLUIDO NO GRUPO '||NEW.CONJUNTO||' (QTD: '||NEW.QUANTIDADE||')';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', NEW.CONFERENCIA_ID, HISTORICO);
    END
END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBCONFERENCIA_ITEM_GRUPO.BLOQUEIO IS 
'0 - CAIXA ABERTO PARA ALTERACAO;
1 - CAIXA BLOQUEADO PARA ALTERACAO;';

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

INSERT INTO TBPARAMETRO (TABELA, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO) VALUES ('SISTEMA', 'OBRIGA_CAIXA_CONFERENCIA', 'OBRIGA O USO DE CAIXAS NA CONFERENCIA DE PECAS DOS PEDIDOS', '0', '0 - NAO NECESSITA DE CAIXA NA CONFERENCIA;
1 - NECESSITA DE CAIXA PARA CONFERIR ITENS;', 'WEB14210');

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

SET TERM ^ ;

CREATE OR ALTER procedure SPD_CONFERENCIA_ITEM (
    CONFERENCIA integer not null)
as
declare variable HISTORICO varchar(5000);
declare variable CONFERENCIA_ID integer;
declare variable TABELA_ITEM_ID bigint;
declare variable COUNT_DUPS integer;
declare variable MAX_ID integer;
declare variable PEDIDO integer;
declare variable ITEM_SEQ varchar(10);
declare variable CONF_GRUPO_ITEM_ID integer;
declare variable CONF_GRUPO_ITEM_CONJUNTO varchar(10);
BEGIN

    FOR
        SELECT
            Y.CONFERENCIA_ID,
            Y.TABELA_ITEM_ID,
            Y.COUNT_DUPS,
            Y.MAX_ID
        
        FROM (
            SELECT
                X.CONFERENCIA_ID,
                X.TABELA_ITEM_ID,
                X.COUNT_DUPS,
                X.MAX_ID
            
            FROM (
                SELECT
                    T.ID,
                    T.CONFERENCIA_ID,
                    T.PRODUTO_ID,
                    T.TAMANHO,
                    T.QTD_TOTAL,
                    T.QTD_CONFERIDA,
                    T.TABELA_ITEM_ID,
                    T.QTD_ALOCADA,
                    T.QTD_DISPONIVEL,
                    T.STATUS,
                    T.NF_ID,
                
                    COALESCE((SELECT COUNT(X.TABELA_ITEM_ID) FROM TBCONFERENCIA_ITEM X WHERE X.TABELA_ITEM_ID = T.TABELA_ITEM_ID AND X.CONFERENCIA_ID = A.ID), 0) AS COUNT_DUPS,
                    COALESCE((SELECT MAX(X.ID)               FROM TBCONFERENCIA_ITEM X WHERE X.TABELA_ITEM_ID = T.TABELA_ITEM_ID AND X.CONFERENCIA_ID = A.ID AND X.STATUS = '0'), 0) AS MAX_ID
                
                FROM TBCONFERENCIA_ITEM T
                INNER JOIN TBCONFERENCIA A ON A.ID = T.CONFERENCIA_ID
                WHERE T.ID > 0
                AND T.TABELA_ITEM_ID > 0
                AND T.STATUS = '0'
                AND A.STATUS IN (1, 2, 3)
        
                AND A.ID = :CONFERENCIA
            
            ) X
            WHERE X.COUNT_DUPS > 1
            
            ORDER BY X.TABELA_ITEM_ID
        
        ) Y
        
        GROUP BY
            Y.CONFERENCIA_ID,
            Y.TABELA_ITEM_ID,
            Y.COUNT_DUPS,
            Y.MAX_ID

    INTO
        :CONFERENCIA_ID,
        :TABELA_ITEM_ID,
        :COUNT_DUPS,
        :MAX_ID

    DO
    BEGIN

        :HISTORICO = '';

        IF (:MAX_ID > 0 AND :CONFERENCIA_ID > 0) THEN
        BEGIN
            /* Exclusao de Duplicata */
            DELETE FROM TBCONFERENCIA_ITEM T WHERE T.CONFERENCIA_ID = :CONFERENCIA_ID AND T.ID = :MAX_ID;

            /* Historico de Exclusao */
            SELECT FIRST 1
                P.PEDIDO,
                FN_LPAD(PI.SEQUENCIA, 3, '0') AS ITEM_SEQ

            FROM TBPEDIDO_ITEM PI
            INNER JOIN TBPEDIDO P ON P.PEDIDO = PI.PEDIDO
            WHERE PI.CONTROLE = :TABELA_ITEM_ID INTO :PEDIDO, :ITEM_SEQ;


            :HISTORICO = 'ITEM: '|| :ITEM_SEQ ||' EXCLUIDO NA CONFERENCIA: '|| FN_LPAD(:CONFERENCIA_ID, 6, '0') ||'DO PEDIDO: '|| :PEDIDO ||'. EXCLUSAO DE DUPLICATAS AUTOMATICA QTD.DUP: '|| :COUNT_DUPS ||'';

            EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', :CONFERENCIA_ID, FN_ELIPSE(:HISTORICO, 500));
        END

    END

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

    FOR
        SELECT
            Y.CONFERENCIA_ID,
            Y.TABELA_ITEM_ID,
            Y.ID AS MAX_ID

        FROM (
            SELECT
                A.TABELA,
                A.TABELA_ID,
    
                T.ID,
                T.CONFERENCIA_ID,
                T.PRODUTO_ID,
                T.TAMANHO,
                T.TABELA_ITEM_ID,

                COALESCE((SELECT FIRST 1 Z.PEDIDO FROM TBPEDIDO_ITEM Z WHERE Z.CONTROLE = T.TABELA_ITEM_ID), 0) AS PEDIDO
    
            FROM TBCONFERENCIA_ITEM T
            INNER JOIN TBCONFERENCIA A ON A.ID = T.CONFERENCIA_ID
            WHERE T.ID > 0
            AND T.TABELA_ITEM_ID > 0
            AND T.STATUS = '0'
            AND A.STATUS IN (1, 2, 3)
    
            AND A.ID = :CONFERENCIA
        ) Y
        WHERE Y.PEDIDO > 0
        AND Y.PEDIDO <> Y.TABELA_ID

    INTO
        :CONFERENCIA_ID,
        :TABELA_ITEM_ID,
        :MAX_ID

    DO
    BEGIN

        :HISTORICO = '';

        IF (:MAX_ID > 0 AND :CONFERENCIA_ID > 0) THEN
        BEGIN
            /* Exclusao registro incorreto */
            DELETE FROM TBCONFERENCIA_ITEM T WHERE T.CONFERENCIA_ID = :CONFERENCIA_ID AND T.ID = :MAX_ID;

            /* Historico de Exclusao */
            SELECT FIRST 1
                P.PEDIDO,
                FN_LPAD(PI.SEQUENCIA, 3, '0') AS ITEM_SEQ

            FROM TBPEDIDO_ITEM PI
            INNER JOIN TBPEDIDO P ON P.PEDIDO = PI.PEDIDO
            WHERE PI.CONTROLE = :TABELA_ITEM_ID INTO :PEDIDO, :ITEM_SEQ;


            :HISTORICO = 'ITEM: '|| :ITEM_SEQ ||' EXCLUIDO NA CONFERENCIA: '|| FN_LPAD(:CONFERENCIA_ID, 6, '0') ||'. ITEM PERTENCIA AO PEDIDO: '|| :PEDIDO ||'. EXCLUSAO DE REGISTROS INCORRETOS AUTOMATICA';

            EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', :CONFERENCIA_ID, FN_ELIPSE(:HISTORICO, 500));
        END

    END

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

    FOR
        SELECT
            XZ.ID,
            XZ.CONJUNTO,
            XZ.CONFERENCIA_ID
        
        FROM (
            SELECT
                IG.ID,
                IG.CONFERENCIA_ID,
                IG.CONJUNTO,
            
                IG.CONFERENCIA_ITEM_ID AS CONF_ITEM_REG_ID,
                T.ID AS CONF_ITEM_ENC_ID
            
            FROM TBCONFERENCIA_ITEM_GRUPO IG
            LEFT JOIN TBCONFERENCIA_ITEM T ON T.ID = IG.CONFERENCIA_ITEM_ID
            WHERE IG.CONFERENCIA_ID = :CONFERENCIA
        ) XZ
        WHERE XZ.CONF_ITEM_ENC_ID IS NULL

    INTO
        :CONF_GRUPO_ITEM_ID,
        :CONF_GRUPO_ITEM_CONJUNTO,
        :CONFERENCIA_ID

    DO
    BEGIN

        IF (:CONF_GRUPO_ITEM_ID > 0 AND :CONFERENCIA_ID > 0) THEN
        BEGIN

            /* Exclusao de grupos que nao contem itens validos */
            DELETE FROM TBCONFERENCIA_ITEM_GRUPO IG WHERE IG.CONFERENCIA_ID = :CONFERENCIA_ID AND IG.ID = :CONF_GRUPO_ITEM_ID AND IG.CONJUNTO = :CONF_GRUPO_ITEM_CONJUNTO;

        END

    END

END
^

SET TERM ; ^

