exclui_unico_cupom.sql

INSERT INTO ESTATISTICA_PENDENTE (EPT_ID, EPT_ACAO, EPT_TIPO, EPT_JSON) 
    (SELECT NEXTVAL('ESTATISTICA_PENDENTE_EPT_ID_seq'), 'EXCLUIR', 'VENDA', TO_JSON(VENDAS) 
    FROM (SELECT TRNDAT::timestamp with time zone AS "dataVenda", TRNSEQ AS "sequencial", CXANUM AS "numeroCaixa", LOJCOD AS "loja", PROCOD AS "produto" 
        FROM ITEM_VENDA WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1) VENDAS);

INSERT INTO ESTATISTICA_PENDENTE (EPT_ID, EPT_ACAO, EPT_TIPO, EPT_JSON) 
    (SELECT NEXTVAL('ESTATISTICA_PENDENTE_EPT_ID_seq'), 'EXCLUIR', 'MOVIMENTACAO', TO_JSON(MOVIMENTACOES) 
    FROM (SELECT ITVQTDVDA AS "totalSaidas", ITVQTDVDA AS "totalVendas", EXTRACT( YEAR FROM TRNDAT )::INT AS "ano", EXTRACT( MONTH FROM TRNDAT )::INT AS "mes", LOJCOD AS "loja", PROCOD AS "produto" 
        FROM ITEM_VENDA  WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1) MOVIMENTACOES);

UPDATE ESTATISTICA_PRODUTO_VENDA SET EPV_DATA_VENDA = NULL, EPV_CXANUM = NULL, EPV_TRNSEQ = NULL WHERE EPV_DATA_VENDA = '2017-04-03' AND EPV_CXANUM = '001' AND EPV_TRNSEQ = '000008' AND EPV_LOJCOD = 1;

DELETE FROM ESTOQUE_MOVIMENTACAO WHERE ID_ITEM_VENDA IN ( SELECT ID FROM ITEM_VENDA WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1);

DELETE FROM TRANSACAO WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM ITEM_VENDA WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM FINALIZACAO WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM TRANSACAOITEMTEFDLL WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM TRANSACAOTEFDLL WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM OCORRENCIA_PDV WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;
DELETE FROM TRANSACAO_XMLNOTA WHERE TRNDAT = '2017-04-03' AND CXANUM = '001' AND TRNSEQ = '000008' AND LOJCOD = 1;

DELETE FROM VENDA_CONSOLIDADA_PRODUTO WHERE VCPDAT = '2017-04-03' AND LOJCOD = 1;
DELETE FROM VENDA_CONSOLIDADA_LOJA WHERE VCLDAT = '2017-04-03' AND LOJCOD = 1;

INSERT INTO VENDA_CONSOLIDADA_PRODUTO (ID, LOJCOD, VCPDAT, PROCOD, VCPQTD, VCPVLRTOT, VALOR_LUCRO_MARKUP, VALOR_LUCRO_MARGEM, VALOR_CUSTO) 
    SELECT NEXTVAL('SQ_VENDA_CONSOLIDADA_PRODUTO'), 
        T.LOJCOD, T.TRNDAT AS VCPDAT, 
        IV.PROCOD as PROCOD, 
        SUM(IV.ITVQTDVDA) AS VCPQTD, 
        SUM(IV.ITVVLRTOT) AS VCPVLRTOT, 
        SUM(ITVVLRTOT - (IV.ITVPRCCST * IV.ITVQTDVDA)) AS VALOR_LUCRO_MARKUP, 
        SUM(ITVVLRTOT - (ITVVLRTOT * ITVTRBALQ / 100) - (ITVVLRTOT * ITVALQPIS / 100) - (ITVVLRTOT * ITVALQCOFINS / 100) - (ITVPRCCSTFIS * ITVQTDVDA)) AS VALOR_LUCRO_MARGEM, 
        SUM(ITVPRCCST) AS VALOR_CUSTO 
        FROM TRANSACAO T 
        JOIN ITEM_VENDA IV ON (T.TRNSEQ = IV.TRNSEQ AND T.CXANUM = IV.CXANUM AND T.TRNDAT = IV.TRNDAT AND  T.LOJCOD  = IV.LOJCOD AND  IV.ITVTIP = '1') 
    WHERE T.TRNTIP = '1' AND T.TRNDAT = '2017-04-03' AND   T.LOJCOD = 1 
    GROUP BY T.LOJCOD, T.TRNDAT, IV.PROCOD;

INSERT INTO VENDA_CONSOLIDADA_LOJA (ID, LOJCOD, VCLDAT, VCLQTDCLI, VCLVLRTOT, TOTAL_LUCRO_MARKUP, TOTAL_LUCRO_MARGEM, TOTAL_CUSTO) 
    SELECT NEXTVAL('SQ_VENDA_CONSOLIDADA_LOJA'), 
        T1.LOJCOD, T1.TRNDAT AS VCPDAT, 
        T2.CONTADOR AS VCLQTDCLI, 
        SUM(IV.ITVVLRTOT) AS VCPVLRTOT, 
        SUM(ITVVLRTOT - (IV.ITVPRCCST * IV.ITVQTDVDA)) AS TOTAL_LUCRO_MARKUP, 
        SUM(ITVVLRTOT - (ITVVLRTOT * ITVTRBALQ / 100) - (ITVVLRTOT * ITVALQPIS / 100) - (ITVVLRTOT * ITVALQCOFINS / 100) - (ITVPRCCSTFIS * ITVQTDVDA)) AS TOTAL_LUCRO_MARGEM, 
        SUM(ITVPRCCST) AS TOTAL_CUSTO FROM TRANSACAO T1 
        JOIN ITEM_VENDA IV ON (T1.TRNSEQ = IV.TRNSEQ AND T1.CXANUM = IV.CXANUM AND T1.TRNDAT = IV.TRNDAT AND T1.LOJCOD = IV.LOJCOD AND IV.ITVTIP = '1') 
        JOIN (SELECT T.LOJCOD, T.TRNDAT , count(*) as CONTADOR 
                FROM TRANSACAO T 
            WHERE T.TRNTIP = '1' GROUP BY T.LOJCOD, T.TRNDAT) T2 on (T2.LOJCOD = T1.LOJCOD and T2.TRNDAT = T1.TRNDAT) 
    WHERE T1.TRNTIP = '1' AND T1.TRNDAT = '2017-04-03' AND T1.LOJCOD = 1 GROUP BY T1.LOJCOD, T1.TRNDAT, T2.CONTADOR;