Src/Sankhya/Scripts/Cadastra pedidos (WMW Vendas & Sankhya) que não estão na Controle de Pedidos.sql
USE SANKHYA_PRODUCAO;
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
DECLARE
@NUNOTA INT, @PEDORIGINAL INT, @CODVEND INT, @CODUSU INT, @CODTIPOPER INT, @CODPARC INT,
@DATAFATUR DATETIME, @DATANEG DATETIME, @HORANEG VARCHAR(6), @STATUSPGTO VARCHAR(1),
@ORDERID VARCHAR(100), @DATABAIXA DATETIME, @CODUSUBAIXA INT, @DATAFATUREXP DATETIME, @DATANEGEXP DATETIME,
@HORANEGEXP VARCHAR(6), @NUNOTAEXP INT, @CODUSUEXP INT, @CODUSUIMP INT, @HORAIMP VARCHAR(6), @DATAIMP DATETIME,
@CONTADORP INT, @CONTADORB INT, @CONTADORE INT, @CONTADORI INT, @CONTADORS INT, @INFO VARCHAR(255),
@CONTADORV INT, @CHAVENFE VARCHAR(44), @NUMERONFE INT, @DATAFATURV DATETIME, @DATANEGV DATETIME,
@HORANEGV VARCHAR, @CODUSUV INT, @NUNOTAV INT, @CONTADORN INT;
SET @CONTADORP = 0;
SET @CONTADORB = 0;
SET @CONTADORE = 0;
SET @CONTADORI = 0;
SET @CONTADORS = 0;
SET @CONTADORV = 0;
SET @CONTADORN = 0;
DECLARE cursor_orders CURSOR FAST_FORWARD FOR
SELECT TOP 200 C.NUNOTA, C.AD_PEDORIGINAL, C.CODTIPOPER, C.CODVEND, C.CODUSUINC, C.DTFATUR, C.DTNEG, CAST(C.HRMOV AS VARCHAR), C.AD_STATUSPGTO, C.CODPARC
FROM sankhya.TGFCAB AS C WITH (NOLOCK)
LEFT JOIN sankhya.AD_PEDIDOVTEXSCFLUXO AS F WITH (NOLOCK)
ON (C.NUNOTA = F.NUNOTA OR F.NUNOTA IS NULL)
WHERE C.CODTIPOPER IN (500, 501, 503, 515)
AND F.NUNOTA IS NULL
AND YEAR(C.DTNEG) >= 2017;
OPEN cursor_orders;
FETCH NEXT FROM cursor_orders
INTO @NUNOTA, @PEDORIGINAL, @CODTIPOPER, @CODVEND, @CODUSU, @DATAFATUR, @DATANEG, @HORANEG, @STATUSPGTO, @CODPARC;
IF @PEDORIGINAL IS NULL
SET @PEDORIGINAL = @NUNOTA;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DATAFATUR IS NULL
BEGIN
SET @HORANEG = RIGHT('000000' + @HORANEG, 6);
SET @DATAFATUR = CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @DATANEG, 20)) + ' ' + SUBSTRING(@HORANEG, 1, 2) + ':' + SUBSTRING(@HORANEG, 3, 2) + ':' + SUBSTRING(@HORANEG, 5, 2));
END
SELECT @ORDERID = (
CASE
WHEN @CODTIPOPER IN (503, 506, 515, 520) THEN CONVERT(VARCHAR(100), @PEDORIGINAL) + '-Sankhya'
ELSE CONVERT(VARCHAR(100), @PEDORIGINAL) + '-WMWVendas'
END
);
IF NOT EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSC WHERE PEDORIGINAL = @PEDORIGINAL)
INSERT INTO sankhya.AD_PEDIDOVTEXSC (ORDERID, PEDORIGINAL, CODLOJA, CODVEND, CODPROMOCAO, MARKETPLACEORDERID, POSSUICURSODIGITAL, POSSUICANALDOARTESANATO, CODPARC, IDXPED, IDXITENS)
VALUES (@ORDERID, @PEDORIGINAL, 0, @CODVEND, 0, 0, 'N', 'N', @CODPARC, 'N', 'N');
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTA, 'P', @DATAFATUR, @CODUSU);
SET @CONTADORP = @CONTADORP + 1;
IF @STATUSPGTO = 'E'
BEGIN
SELECT @DATABAIXA = DTALTER, @CODUSUBAIXA = CODUSU
FROM sankhya.TGFFIN WITH (NOLOCK)
WHERE NUNOTA = @NUNOTA
AND DESDOBRAMENTO = 1;
IF @CODUSUBAIXA IS NULL
BEGIN
SET @CODUSUBAIXA = 67;
SET @DATABAIXA = GETDATE();
END;
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTA, 'B', @DATABAIXA, @CODUSUBAIXA);
SET @CONTADORB = @CONTADORB + 1;
SELECT @NUNOTAEXP = NUNOTA, @CODUSUEXP = CODUSUINC, @DATAFATUREXP = DTFATUR, @DATANEGEXP = DTNEG,
@HORANEGEXP = CAST(HRMOV AS VARCHAR)
FROM sankhya.TGFCAB WITH (NOLOCK)
WHERE AD_PEDORIGINAL = @PEDORIGINAL
AND CODTIPOPER = 505
AND AD_CODREENVIO = 0;
IF @NUNOTAEXP IS NOT NULL
BEGIN
IF @DATAFATUREXP IS NULL
BEGIN
SET @HORANEGEXP = RIGHT('000000' + @HORANEGEXP, 6);
SET @DATAFATUREXP = CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @DATANEGEXP, 20)) + ' ' + SUBSTRING(@HORANEGEXP, 1, 2) + ':' + SUBSTRING(@HORANEGEXP, 3, 2) + ':' + SUBSTRING(@HORANEGEXP, 5, 2));
END
IF NOT EXISTS(SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO WHERE NUNOTA = @NUNOTAEXP AND PEDORIGINAL = @PEDORIGINAL AND OCORRENCIA = 'E')
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTAEXP, 'E', @DATAFATUREXP, @CODUSUEXP);
SET @CONTADORE = @CONTADORE + 1;
SELECT TOP 1 @DATAIMP = DHOCOR, @HORAIMP = CAST(HRACT AS VARCHAR), @CODUSUIMP = CODUSU
FROM sankhya.TGFACT WITH (NOLOCK)
WHERE NUNOTA = @NUNOTAEXP
AND OCORRENCIAS LIKE '%IMPRESSÃO DE PEDIDO%';
IF @DATAIMP IS NOT NULL
BEGIN
SET @HORAIMP = RIGHT('000000' + @HORAIMP, 6);
SET @DATAIMP = CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @DATAIMP, 20)) + ' ' + SUBSTRING(@HORAIMP, 1, 2) + ':' + SUBSTRING(@HORAIMP, 3, 2) + ':' + SUBSTRING(@HORAIMP, 5, 2));
IF NOT EXISTS(SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO WHERE NUNOTA = @NUNOTAEXP AND PEDORIGINAL = @PEDORIGINAL AND OCORRENCIA = 'I')
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTAEXP, 'I', @DATAIMP, @CODUSUIMP);
SET @CONTADORI = @CONTADORI + 1;
END
SELECT @NUNOTAV = NUNOTA, @CODUSUV = CODUSUINC, @DATAFATURV = DTFATUR, @DATANEGV = DTNEG,
@HORANEGV = CAST(HRMOV AS VARCHAR), @CHAVENFE = CHAVENFE, @NUMERONFE = NUMNOTA
FROM sankhya.TGFCAB WITH (NOLOCK)
WHERE AD_PEDORIGINAL = @PEDORIGINAL
AND CODTIPOPER = 550
AND AD_CODREENVIO = 0;
IF @NUNOTAV IS NOT NULL
BEGIN
IF @DATAFATURV IS NULL
BEGIN
SET @HORANEGV = RIGHT('000000' + @HORANEGV, 6);
SET @DATAFATURV = CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @DATANEGV, 20)) + ' ' + SUBSTRING(@HORANEGV, 1, 2) + ':' + SUBSTRING(@HORANEGV, 3, 2) + ':' + SUBSTRING(@HORANEGV, 5, 2));
END
IF NOT EXISTS(SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO WHERE NUNOTA = @NUNOTAV AND PEDORIGINAL = @PEDORIGINAL AND OCORRENCIA = 'V')
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTAV, 'V', @DATAFATURV, @CODUSUV);
SET @CONTADORV = @CONTADORV + 1;
IF @CHAVENFE IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO WHERE NUNOTA = @NUNOTAV AND PEDORIGINAL = @PEDORIGINAL AND OCORRENCIA = 'N')
INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (ORDERID, PEDORIGINAL, NUNOTA, OCORRENCIA, DATA, CODUSU)
VALUES (@ORDERID, @PEDORIGINAL, @NUNOTAV, 'N', DATEADD(MINUTE, 1, @DATAFATURV), @CODUSUV);
SET @CONTADORN = @CONTADORN + 1;
END
END
END
END
FETCH NEXT FROM cursor_orders
INTO @NUNOTA, @PEDORIGINAL, @CODTIPOPER, @CODVEND, @CODUSU, @DATAFATUR, @DATANEG, @HORANEG, @STATUSPGTO, @CODPARC;
END
CLOSE cursor_orders;
DEALLOCATE cursor_orders;
SET @INFO = 'Pedidos: ' + CAST(@CONTADORP AS VARCHAR) + CHAR(13) + CHAR(10) + 'Baixas: ' +
CAST(@CONTADORB AS VARCHAR) + CHAR(13) + CHAR(10) + 'Expedições: ' + CAST(@CONTADORE AS VARCHAR) +
CHAR(13) + CHAR(10) + 'Impressões: ' + CAST(@CONTADORI AS VARCHAR) + CHAR(13) + CHAR(10) + 'Etiquetas: ' + CAST(@CONTADORS AS VARCHAR) +
CHAR(13) + CHAR(10) + 'Vendas: ' + CAST(@CONTADORV AS VARCHAR) + CHAR(13) + CHAR(10) + 'NF-e: ' + CAST(@CONTADORN AS VARCHAR);
PRINT @INFO;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAlerts',
@recipients = 'guilherme@editorainovacao.com.br',
@body = @INFO,
@subject = 'Controle de Pedidos - Não Cadastrados (WMW Vendas | Sankhya) [JOB - SQL Server]'
COMMIT
END TRY
BEGIN CATCH
PRINT 'Erro';
PRINT 'Pedido original: ' + CAST(@PEDORIGINAL AS VARCHAR) + ' - Número único: ' + CAST(@NUNOTA AS VARCHAR);
PRINT 'Mensagem: ' + ERROR_MESSAGE();
PRINT 'Linha: ' + CAST(ERROR_LINE() AS VARCHAR);
IF @@TRANCOUNT > 0
ROLLBACK
CLOSE cursor_orders;
DEALLOCATE cursor_orders;
END CATCH