SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBPDV_CONFERENCIA_TROCA_B0I FOR TBPDV_CONFERENCIA_TROCA
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE PRE_PEDIDO          VARCHAR(10);
DECLARE VARIABLE TEXTO_ERRO          VARCHAR(8000);
DECLARE VARIABLE DATA                VARCHAR(10);
DECLARE VARIABLE DATA_FECHAMENTO     VARCHAR(10);
DECLARE VARIABLE VALOR_LIQUIDO       NUMERIC(15,2);
DECLARE VARIABLE VALOR_LIQUIDO_TOTAL NUMERIC(15,2);
BEGIN
  :TEXTO_ERRO          = '';
  :VALOR_LIQUIDO       =  0;
  :VALOR_LIQUIDO_TOTAL =  0;

  FOR SELECT
        FN_LPAD(T.ID,4,0) AS ID,
        COALESCE(FORMATDATE(T.DATAHORA),'') AS DATA,
        COALESCE(FORMATDATE(T.DATA_FECHAMENTO),'') AS DATA_FECHAMENTO,
        COALESCE((
            SELECT
                SUM((Z.TOTAL-(CAST(Z.TOTAL AS DOUBLE PRECISION)*(CAST(Z.PERC_DESCONTO AS DOUBLE PRECISION)/100.00)))) AS VALOR_LIQUIDO_TOTAL
                FROM(
                SELECT
                    (CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)) AS TOTAL,
                    X.VALOR_DESCONTO,
                    IIF(X.VALOR_DESCONTO > 0,
                    (CAST(X.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/(CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)),0.00) AS PERC_DESCONTO
                    FROM TBPDV_CONFERENCIA_ITEM X , TBPDV_CONFERENCIA_ITEM_SALDO IX
                    WHERE  X.PDV_CONFERENCIA_ID      = T.ID
                    AND IX.PDV_CONFERENCIA_ITEM_ID = X.ID
                    AND  X.PDV_CONFERENCIA_ID      = IX.PDV_CONFERENCIA_ID
                    AND IX.QUANTIDADE              > 0
                ) Z
            ),0.00) AS VALOR_LIQUIDO_TOTAL
    
        FROM (
            SELECT T.*,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 0),0) AS STATUS_ITEM_ABERTO,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 3),0) AS STATUS_ITEM_FATURADO,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 4),0) AS STATUS_ITEM_ENCERRADO
                
                FROM TBPDV_CONFERENCIA T
                WHERE T.ID > 0
                AND T.CLIENTE_ID > 0
                AND T.CLIENTE_ID = NEW.CLIENTE_ID
                AND T.DATA_FECHAMENTO IS NOT NULL
                AND DATEDIFF(DAY FROM T.DATA_FECHAMENTO TO CURRENT_DATE) > 0
        ) T
        WHERE T.ID > 0
        AND T.STATUS_ITEM_ABERTO = 1
        INTO :PRE_PEDIDO, :DATA, :DATA_FECHAMENTO, :VALOR_LIQUIDO
        DO BEGIN
             :VALOR_LIQUIDO_TOTAL = :VALOR_LIQUIDO_TOTAL + :VALOR_LIQUIDO;
        END

  IF(:VALOR_LIQUIDO_TOTAL > 0)THEN
  BEGIN
    :TEXTO_ERRO = :TEXTO_ERRO||'<LABEL STYLE="FONT-WEIGHT: BOLDER !IMPORTANT; FONT-SIZE: 13PT !IMPORTANT;">ATENCAO, EXISTEM PRE-PEDIDOS EM ABERTO COM DATA DE FECHAMENTO VENCIDA:</LABEL>';
    :TEXTO_ERRO = :TEXTO_ERRO||'<LABEL STYLE="FONT-WEIGHT: BOLDER !IMPORTANT; FONT-SIZE: 11PT !IMPORTANT;FLOAT: LEFT;">VALOR TOTAL DOS PRE-PEDIDOS EM ABERTO: R$ '||FN_FORMAT_NUMBER2(:VALOR_LIQUIDO_TOTAL,2)||'</LABEL>';

    EXCEPTION "..." :TEXTO_ERRO;
  END

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

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

CREATE OR ALTER TRIGGER TTBPDV_DEVOLUCAO_TROCA_B0I FOR TBPDV_DEVOLUCAO_TROCA
ACTIVE BEFORE INSERT POSITION 0
AS     
DECLARE VARIABLE PRE_PEDIDO          VARCHAR(10);
DECLARE VARIABLE TEXTO_ERRO          VARCHAR(8000);
DECLARE VARIABLE DATA                VARCHAR(10);
DECLARE VARIABLE DATA_FECHAMENTO     VARCHAR(10);
DECLARE VARIABLE VALOR_LIQUIDO       NUMERIC(15,2);
DECLARE VARIABLE VALOR_LIQUIDO_TOTAL NUMERIC(15,2);
BEGIN

  :TEXTO_ERRO          = '';
  :VALOR_LIQUIDO       =  0;
  :VALOR_LIQUIDO_TOTAL =  0;

  FOR SELECT
        FN_LPAD(T.ID,4,0) AS ID,
        COALESCE(FORMATDATE(T.DATAHORA),'') AS DATA,
        COALESCE(FORMATDATE(T.DATA_FECHAMENTO),'') AS DATA_FECHAMENTO,
        COALESCE((
            SELECT
                SUM((Z.TOTAL-(CAST(Z.TOTAL AS DOUBLE PRECISION)*(CAST(Z.PERC_DESCONTO AS DOUBLE PRECISION)/100.00)))) AS VALOR_LIQUIDO_TOTAL
                FROM(
                SELECT
                    (CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)) AS TOTAL,
                    X.VALOR_DESCONTO,
                    IIF(X.VALOR_DESCONTO > 0,
                    (CAST(X.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/(CAST(IX.QUANTIDADE AS DOUBLE PRECISION) * CAST(X.VALOR_UNITARIO AS DOUBLE PRECISION)),0.00) AS PERC_DESCONTO
                    FROM TBPDV_CONFERENCIA_ITEM X , TBPDV_CONFERENCIA_ITEM_SALDO IX
                    WHERE  X.PDV_CONFERENCIA_ID      = T.ID
                    AND IX.PDV_CONFERENCIA_ITEM_ID = X.ID
                    AND  X.PDV_CONFERENCIA_ID      = IX.PDV_CONFERENCIA_ID
                    AND IX.QUANTIDADE              > 0
                ) Z
            ),0.00) AS VALOR_LIQUIDO
    
        FROM (
            SELECT T.*,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 0),0) AS STATUS_ITEM_ABERTO,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 3),0) AS STATUS_ITEM_FATURADO,
                COALESCE((SELECT FIRST 1 1 FROM TBPDV_CONFERENCIA_ITEM TI WHERE TI.PDV_CONFERENCIA_ID = T.ID AND TI.SITUACAO = 4),0) AS STATUS_ITEM_ENCERRADO
                
                FROM TBPDV_CONFERENCIA T
                WHERE T.ID > 0
                AND T.CLIENTE_ID = NEW.CLIENTE_ID
                AND T.CLIENTE_ID > 0
                AND T.DATA_FECHAMENTO IS NOT NULL
                AND DATEDIFF(DAY FROM T.DATA_FECHAMENTO TO CURRENT_DATE) > 0
        ) T
        WHERE T.ID > 0
        AND T.STATUS_ITEM_ABERTO = 1
        INTO :PRE_PEDIDO, :DATA, :DATA_FECHAMENTO, :VALOR_LIQUIDO
        DO BEGIN
             :VALOR_LIQUIDO_TOTAL = :VALOR_LIQUIDO_TOTAL + :VALOR_LIQUIDO;
        END

  IF(:VALOR_LIQUIDO_TOTAL > 0 AND NEW.TIPO_ED = '2')THEN
  BEGIN
    :TEXTO_ERRO = :TEXTO_ERRO||'<LABEL STYLE="FONT-WEIGHT: BOLDER !IMPORTANT; FONT-SIZE: 13PT !IMPORTANT;">ATENCAO, EXISTEM PRE-PEDIDOS EM ABERTO COM DATA DE FECHAMENTO VENCIDA:</LABEL>';
    :TEXTO_ERRO = :TEXTO_ERRO||'<LABEL STYLE="FONT-WEIGHT: BOLDER !IMPORTANT; FONT-SIZE: 11PT !IMPORTANT;FLOAT: LEFT;">VALOR TOTAL DOS PRE-PEDIDOS EM ABERTO: R$ '||FN_FORMAT_NUMBER2(:VALOR_LIQUIDO_TOTAL,2)||'</LABEL>';

    EXCEPTION "..." :TEXTO_ERRO;
  END

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

^ SET TERM ; ^