
ALTER PROCEDURE [dbo].[CriarPedidoCompra]
    @CodigosPat VARCHAR(MAX),
    @User VARCHAR(30),
	@Operacao CHAR(2),
	@Status CHAR(2)
AS
BEGIN
	SET NOCOUNT ON;
    -- Converter a string para tabela
   DECLARE @PATs TABLE (PAT VARCHAR(50));

	INSERT INTO @PATs (PAT)
	SELECT LTRIM(RTRIM(value))
	FROM STRING_SPLIT(@CodigosPat, ',');

	DECLARE @novped VARCHAR(8);

	SET @novped = (SELECT TOP 1 
						FORMAT(TB00002_COD+1,'000000')
					FROM TB00002 
					WHERE TB00002_TABELA = 'TB02030' 
					ORDER BY 1 DESC)

INSERT INTO [dbo].[TB02030](
				[TB02030_DTCAD], 
				[TB02030_OPCAD], 
				[TB02030_CONTRATO],
				[TB02030_BASEICMS], 
				[TB02030_BASEICMSSUB], 
				[TB02030_CODEMP], 
				[TB02030_CODFOR], 
				[TB02030_CODIGO], 
				[TB02030_CONDPAG], 
				[TB02030_DATA], 
				[TB02030_DTENT], 
				[TB02030_DESPADD], 
				[TB02030_OBS], 
				[TB02030_OBSADD], 
				[TB02030_PERCDESCONTO], 
				[TB02030_QTDE], 
				[TB02030_QTDER], 
				[TB02030_QTVOLUMES], 
				[TB02030_SITUACAO], 
				[TB02030_TRANSP], 
				[TB02030_VLRBRUTO], 
				[TB02030_VLRDESCONTO], 
				[TB02030_VLRFRETE], 
				[TB02030_VLRICMS], 
				[TB02030_VLRICMSSUB], 
				[TB02030_VLRIPI], 
				[TB02030_VLRNOTA], 
				[TB02030_VLRNOTAR], 
				[TB02030_VLROUTDESP], 
				[TB02030_NATUREZA], 
				[TB02030_TIPOTOTAL], 
				[TB02030_VLRICMSSUB2], 
				[TB02030_VLRBRUTO2], 
				[TB02030_VLRDESCONTO2], 
				[TB02030_VLRFRETE2], 
				[TB02030_VLRFRETE3], 
				[TB02030_VLRIPI2], 
				[TB02030_VLRNOTA2], 
				[TB02030_SOMASUB], 
				[TB02030_NOME], 
				[TB02030_CODCEN], 
				[TB02030_CODSUB], 
				[TB02030_PLANCON], 
				[TB02030_CONTABIL], 
				[TB02030_TIPOFRETE], 
				[TB02030_PESOBRUTO], 
				[TB02030_PESOLIQUIDO], 
				[TB02030_DATANOTA], 
				[TB02030_VLROUTDESP2], 
				[TB02030_BASEICMS2], 
				[TB02030_VLRICMS2], 
				[TB02030_STATUS], 
				[TB02030_SOMAFRETE], 
				[TB02030_OPCOM], 
				[TB02030_QTDEVOL], 
				[TB02030_QTDEVOLR], 
				[TB02030_DEVLOCACAO], 
				[TB02030_ORDEM], 
				[TB02030_KMINICIAL], 
				[TB02030_KMFINAL], 
				[TB02030_TIPOSUP], 
				[TB02030_USADO], 
				[TB02030_QTSERV], 
				[TB02030_VLRSERV], 
				[TB02030_VLRIMPOSTOS], 
				[TB02030_BASEPIS], 
				[TB02030_VLRPIS], 
				[TB02030_BASECOFINS],
				[TB02030_VLRCOFINS], 
				[TB02030_VLRCSLL], 
				[TB02030_VLRIR], 
				[TB02030_VLRISS], 
				[TB02030_VLRINSS], 
				[TB02030_VLRRETPIS],
				[TB02030_VLRRETCOFINS], 
				[TB02030_VLRRETCSLL], 
				[TB02030_VLRRETIR], 
				[TB02030_VLRRETISS], 
				[TB02030_VLRRETINSS], 
				[TB02030_DATACOTACAO], 
				[TB02030_NROCOTACAO], 
				[TB02030_TIPOPEDIDO],
				[TB02030_CODSITE],
				[TB02030_NUMERO])

			SELECT TOP 1
			    ----todos impostos 0
				GETDATE(),  --[TB02030_DTCAD], 
				@User,    --[TB02030_OPCAD], 
				a.TB02112_CODIGO,   --[TB02030_CONTRATO],
				0,	--[TB02030_BASEICMS], 
				0,	--[TB02030_BASEICMSSUB], 
				TB02111_CODEMP,	--[TB02030_CODEMP], 
				TB01001_CODIGO,	--[TB02030_CODFOR], Pegar do cliente pelo codCli
				@novped,	--[TB02030_CODIGO], 
				'000',	--[TB02030_CONDPAG], Vamos pegar 
				GETDATE(),	--[TB02030_DATA], 
				GETDATE(),	--[TB02030_DTENT], 
				0,	--[TB02030_DESPADD], 
				'APP_CONTRATOS',	--[TB02030_OBS], TB02030_CODFOR
				'APP_CONTRATOS',	--[TB02030_OBSADD], 
				0,	--[TB02030_PERCDESCONTO], 
				0,	--[TB02030_QTDE], 
				0,	--[TB02030_QTDER], 
				0,	--[TB02030_QTVOLUMES], 
				'A', --[TB02030_SITUACAO], Vai pegar
				'0000',	--[TB02030_TRANSP], Apnet
				SUM(a.TB02112_CUSTO),	--[TB02030_VLRBRUTO], Soma custo 2112 ou 2054
				0,	--[TB02030_VLRDESCONTO], 
				0,	--[TB02030_VLRFRETE], 
				0,	--[TB02030_VLRICMS], 
				0,	--[TB02030_VLRICMSSUB], 
				0,	--[TB02030_VLRIPI], 
				0,	--[TB02030_VLRNOTA], Soma custo 2112 ou 2054
				0,--[TB02030_VLRNOTAR], 
				0,--[TB02030_VLROUTDESP], 
				CASE 
					WHEN TB02176_ESTADO = TB00012_ESTADO 
					THEN CFOP.CFOP_D
					ELSE CFOP.CFOP_F END,	--[TB02030_NATUREZA]
				'P',	--[TB02030_TIPOTOTAL], Fabricio 
				0,	--[TB02030_VLRICMSSUB2], 
				0,	--[TB02030_VLRBRUTO2], 
				0,	--[TB02030_VLRDESCONTO2], 
				0,	--[TB02030_VLRFRETE2], 
				0,	--[TB02030_VLRFRETE3], 
				0,	--[TB02030_VLRIPI2], 
				0,	--[TB02030_VLRNOTA2], 
				0,	--[TB02030_SOMASUB], 
				TB01001_NOME,	--[TB02030_NOME], Nome do Fornecedor
				NULL,	--[TB02030_CODCEN], Fabricio 
				NULL,	--[TB02030_CODSUB], Fabricio 
				NULL,	--[TB02030_PLANCON], 
				'S',	--[TB02030_CONTABIL], 
				NULL,	--[TB02030_TIPOFRETE], 
				0,	--[TB02030_PESOBRUTO], 
				0,	--[TB02030_PESOLIQUIDO], 
				NULL,	--[TB02030_DATANOTA], 
				0,	--[TB02030_VLROUTDESP2], 
				0,	--[TB02030_BASEICMS2], 
				0,	--[TB02030_VLRICMS2], 
				@Status,	--[TB02030_STATUS], 18 Fabricio 
				0,--[TB02030_SOMAFRETE], 
				@Operacao, --[TB02030_OPCOM], 65 Fabricio 
				COUNT(a.TB02112_NUMSERIE),--[TB02030_QTDEVOL], Soma dos itens
				0,--[TB02030_QTDEVOLR], 
				0,--[TB02030_DEVLOCACAO], 
				0,--[TB02030_ORDEM], 
				0,--[TB02030_KMINICIAL], 
				0,--[TB02030_KMFINAL], 
				9,--[TB02030_TIPOSUP], Equip
				'S',	--[TB02030_USADO], ?????
				NUll,	--[TB02030_QTSERV], 
				0,	--[TB02030_VLRSERV], 
				0,	--[TB02030_VLRIMPOSTOS], 
				0,	--[TB02030_BASEPIS], 
				0,	--[TB02030_VLRPIS], 
				0,	--[TB02030_BASECOFINS],
				0,	--[TB02030_VLRCOFINS], 
				0,	--[TB02030_VLRCSLL], 
				0,	--[TB02030_VLRIR], 
				0,	--[TB02030_VLRISS], 
				0,	--[TB02030_VLRINSS], 
				0,	--[TB02030_VLRRETPIS],
				0,	--[TB02030_VLRRETCOFINS], 
				0,	--[TB02030_VLRRETCSLL], 
				0,	--[TB02030_VLRRETIR], 
				0,	--[TB02030_VLRRETISS], 
				0,	--[TB02030_VLRRETINSS], 
				GETDATE(),--[TB02030_DATACOTACAO], 
				NUll,	--[TB02030_NROCOTACAO], 
				'P',	--[TB02030_TIPOPEDIDO],
				TB02112_CODSITE,	--[TB02030_CODSITE], 
				0	--[TB02030_NUMERO]

				FROM TB02112 a
				LEFT JOIN TB02111 ON TB02111_CODIGO = a.TB02112_CODIGO
				LEFT JOIN TB01008 ON TB01008_CODIGO = TB02111_CODCLI
				LEFT JOIN TB01001 ON TB01001_CNPJ = TB01008_CNPJ
				LEFT JOIN TB02176 ON TB02176_CONTRATO = TB02112_CODIGO
				LEFT JOIN TB01007 ON TB01007_CODIGO = TB02111_CODEMP
				LEFT JOIN TB00012 ON TB00012_CODIGO = TB02111_CODEMP 
					AND TB00012_TABELA = 'TB01007'

				
				INNER JOIN @PATs p ON a.TB02112_PAT = p.PAT

				OUTER APPLY(
					SELECT 
						TB01078_NATUREZA CFOP_D,
						TB01078_NATUREZA2 CFOP_F
					FROM TB01078 
					WHERE TB01078_CODIGO = @Operacao
				) AS CFOP

				WHERE TB02112_SITUACAO = 'A'
					--AND TB02112_PAT IN ('027306', '027742', '028564')
					
				GROUP BY a.TB02112_CODIGO, TB02111_CODEMP, TB01008_NOME, TB02112_CODSITE, TB01001_CODIGO, TB01001_NOME, TB02176_ESTADO, TB00012_ESTADO, CFOP.CFOP_D, CFOP.CFOP_F
				UPDATE TB00002 
				SET TB00002_COD = (SELECT TOP 1 FORMAT(TB00002_COD+1,'000000') FROM TB00002 WHERE TB00002_TABELA = 'TB02030' ORDER BY 1 DESC)
				WHERE TB00002_TABELA = 'TB02030'

		INSERT INTO [dbo].[TB02031](
				[TB02031_DTCAD], 
				[TB02031_OPCAD], 
				[TB02031_CODIGO], 
				[TB02031_CUSTO], 
				[TB02031_PERDESC], 
				[TB02031_PRODUTO], 
				[TB02031_PRUNIT], 
				[TB02031_QTPROD], 
				[TB02031_QTPRODB], 
				[TB02031_SITUACAO], 
				[TB02031_TOTVALOR], 
				[TB02031_TOTVALORB], 
				[TB02031_PERCIPI],
				[TB02031_VLRIPI], 
				[TB02031_PERCST], 
				[TB02031_VLRST], 
				[TB02031_REGISTRO], 
				[TB02031_VLRDESC], 
				[TB02031_ICMS], 
				[TB02031_VLRICMS], 
				[TB02031_NATUREZA], 
				[TB02031_VLRFRETE], 
				[TB02031_VLROUTDESP],
				[TB02031_VLRFRETEB], 
				[TB02031_VLROUTDESPB], 
				[TB02031_BASERED], 
				[TB02031_ICMSST], 
				[TB02031_SOMAST], 
				[TB02031_DTENT], 
				[TB02031_BASEICMS], 
				[TB02031_BASEST], 
				[TB02031_CST], 
				[TB02031_OPCOM], 
				[TB02031_COFINS], 
				[TB02031_CSTCOFINS], 
				[TB02031_CSTIPI], 
				[TB02031_CSTPIS], 
				[TB02031_PIS], 
				[TB02031_UNPROD], 
				[TB02031_QTPRODUN])
			SELECT
				GETDATE(),	--[TB02031_DTCAD], 
				@User,	--[TB02031_OPCAD], 
				@novped,	--[TB02031_CODIGO], 
				SUM(a.TB02112_CUSTO),	--[TB02031_CUSTO], 
				0,	--[TB02031_PERDESC], 
				a.TB02112_PRODUTO,	--[TB02031_PRODUTO], 
				SUM(a.TB02112_CUSTO),	--[TB02031_PRUNIT], 
				COUNT(a.TB02112_NUMSERIE),--[TB02031_QTPROD], 
				0,	--[TB02031_QTPRODB], 
				'A',	--[TB02031_SITUACAO], 
				COUNT(a.TB02112_NUMSERIE),	--[TB02031_TOTVALOR], 
				0,	--[TB02031_TOTVALORB], 
				0,	--[TB02031_PERCIPI],
				0,	--[TB02031_VLRIPI], 
				0,	--[TB02031_PERCST], 
				0,	--[TB02031_VLRST], 
				NULL,	--[TB02031_REGISTRO], 
				0,	--[TB02031_VLRDESC], 
				0,	--[TB02031_ICMS], 
				0,	--[TB02031_VLRICMS], 
				CASE 
					WHEN TB02176_ESTADO = TB00012_ESTADO 
					THEN CFOP.CFOP_D
					ELSE CFOP.CFOP_F END,	--[TB02031_NATUREZA], 
				0,	--[TB02031_VLRFRETE], 
				0,	--[TB02031_VLROUTDESP],
				0,	--[TB02031_VLRFRETEB], 
				0,	--[TB02031_VLROUTDESPB], 
				0,	--[TB02031_BASERED], 
				0,	--[TB02031_ICMSST], 
				0,	--[TB02031_SOMAST], 
				GETDATE(),	--[TB02031_DTENT], 
				0,	--[TB02031_BASEICMS], 
				0,	--[TB02031_BASEST], 
				0,	--[TB02031_CST], 
				NULL,	--[TB02031_OPCOM], 
				0,	--[TB02031_COFINS], 
				0,	--[TB02031_CSTCOFINS], 
				0,	--[TB02031_CSTIPI], 
				0,	--[TB02031_CSTPIS], 
				0,	--[TB02031_PIS], 
				'00',	--[TB02031_UNPROD], 
				1	--[TB02031_QTPRODUN]
				FROM TB02112 a
				
				INNER JOIN @PATs p ON a.TB02112_PAT = p.PAT
				LEFT JOIN TB02111 ON TB02111_CODIGO = TB02112_CODIGO
				LEFT JOIN TB02176 ON TB02176_CONTRATO = TB02112_CODIGO
				LEFT JOIN TB01007 ON TB01007_CODIGO = TB02111_CODEMP
				LEFT JOIN TB00012 ON TB00012_CODIGO = TB02111_CODEMP 
					AND TB00012_TABELA = 'TB01007'
				OUTER APPLY(
					SELECT 
						TB01078_NATUREZA CFOP_D,
						TB01078_NATUREZA2 CFOP_F
					FROM TB01078 
					WHERE TB01078_CODIGO = @Operacao
				) AS CFOP	

				WHERE a.TB02112_SITUACAO = 'A'
				    --AND a.TB02112_PAT IN ('027306', '027742', '028564')

				GROUP BY a.TB02112_PRODUTO, TB00012_ESTADO, TB00012_ESTADO, CFOP_D, CFOP_F, TB02176_ESTADO


				--preenche series do pedido no TB02208
				INSERT INTO [dbo].[TB02208]
					   ([TB02208_PEDIDO]
					   ,[TB02208_CONFERENCIA]
					   ,[TB02208_COMPRA]
					   ,[TB02208_CONTRATO]
					   ,[TB02208_PRODUTO]
					   ,[TB02208_NUMSERIE]
					   ,[TB02208_PAT]
					   ,[TB02208_CONTPB]
					   ,[TB02208_CONTCOLOR]
					   ,[TB02208_ACESSORIO]
					   ,[TB02208_PRODUTOUSADO])
				 SELECT 
						@novped		--TB02208_PEDIDO
					   ,NULL		--TB02208_CONFERENCIA
					   ,NULL		--TB02208_COMPRA
					   ,TB02112_CODIGO		--TB02208_CONTRATO
					   ,TB02112_PRODUTO		--TB02208_PRODUTO
					   ,TB02112_NUMSERIE		--TB02208_NUMSERIE
					   ,TB02112_PAT		--TB02208_PAT
					   ,0		--TB02208_CONTPB
					   ,0		--TB02208_CONTCOLOR
					   ,'S'		--TB02208_ACESSORIO
					   ,NULL		--TB02208_PRODUTOUSADO
					FROM TB02112 a
						
					
						INNER JOIN @PATs p ON a.TB02112_PAT = p.PAT
						WHERE a.TB02112_SITUACAO = 'A'
				
				

				SELECT @novped AS NovPed;
END
