USE [APNET]
GO
/****** Object:  StoredProcedure [dbo].[SUBS_CILENTE_CONTRATO]    Script Date: 16/12/2025 15:18:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SUBS_CILENTE_CONTRATO]
    @ContratoAnt VARCHAR(12),
    @ContratoNov VARCHAR(12),
	@CodCli VARCHAR(8),
    @Pats VARCHAR(MAX),
    @User VARCHAR(30),
	@Acessorios CHAR(1)
AS
BEGIN
    SET NOCOUNT ON;

	DECLARE @PatsTable TABLE (PAT VARCHAR(20));

	INSERT INTO @PatsTable (PAT)
	SELECT LTRIM(RTRIM(value))
	FROM STRING_SPLIT(@Pats, ',');

	--PRINT 'Linhas inseridas em TB02111: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

	--	IF NOT EXISTS (SELECT 1 FROM TB02111 WHERE TB02111_CODIGO = @ContratoNov)
	--	BEGIN
	--		RAISERROR('Contrato novo não foi criado antes de inserir TB02112',16,1);
	--		RETURN;
	--	END

    INSERT INTO [dbo].[TB02111]
           ([TB02111_DTCAD]
           ,[TB02111_OPCAD]
           ,[TB02111_DTALT]
           ,[TB02111_OPALT]
           ,[TB02111_CODIGO]
           ,[TB02111_CODCLI]
           ,[TB02111_QTDE]
           ,[TB02111_VLRNOTA]
           ,[TB02111_SITUACAO]
           ,[TB02111_FRANQPB]
           ,[TB02111_FRANQCOLOR]
           ,[TB02111_TIPOCONTR]
           ,[TB02111_DTVALIDADE]
           ,[TB02111_TIPOATENDE]
           ,[TB02111_DIALEITURA]
           ,[TB02111_COPIAGARANTIA]
           ,[TB02111_VEND]
           ,[TB02111_OBS]
           ,[TB02111_VENCCONTR]
           ,[TB02111_CODEMP]
           ,[TB02111_TIPOFRANQUIA]
           ,[TB02111_ANALFRANQUIA]
           ,[TB02111_FRANQTOTAL]
           ,[TB02111_FRANQDG]
           ,[TB02111_FRANQGF]
           ,[TB02111_TIR01]
           ,[TB02111_TIR02]
           ,[TB02111_TIR03]
           ,[TB02111_TIR04]
           ,[TB02111_TIR05]
           ,[TB02111_TIR06]
           ,[TB02111_TIR07]
           ,[TB02111_TIR08]
           ,[TB02111_TIR09]
           ,[TB02111_TIR10]
           ,[TB02111_TIR11]
           ,[TB02111_TIR12]
           ,[TB02111_EXC01]
           ,[TB02111_EXC02]
           ,[TB02111_EXC03]
           ,[TB02111_EXC04]
           ,[TB02111_EXC05]
           ,[TB02111_EXC06]
           ,[TB02111_EXC07]
           ,[TB02111_EXC08]
           ,[TB02111_EXC09]
           ,[TB02111_EXC10]
           ,[TB02111_EXC11]
           ,[TB02111_EXC12]
           ,[TB02111_RETPIS]
           ,[TB02111_RETCOFINS]
           ,[TB02111_RETCSLL]
           ,[TB02111_RETIR]
           ,[TB02111_CODCEN]
           ,[TB02111_CODSUB]
           ,[TB02111_PLANCON]
           ,[TB02111_FRA01]
           ,[TB02111_FRA02]
           ,[TB02111_FRA03]
           ,[TB02111_FRA04]
           ,[TB02111_FRA05]
           ,[TB02111_FRA06]
           ,[TB02111_FRA07]
           ,[TB02111_FRA08]
           ,[TB02111_FRA09]
           ,[TB02111_FRA10]
           ,[TB02111_FRA11]
           ,[TB02111_FRA12]
           ,[TB02111_ALIQPIS]
           ,[TB02111_ALIQCOFINS]
           ,[TB02111_ALIQCSLL]
           ,[TB02111_ALIQIR]
           ,[TB02111_RATEIO]
           ,[TB02111_RETISS]
           ,[TB02111_ALIQISS]
           ,[TB02111_RETINSS]
           ,[TB02111_PRORATA]
           ,[TB02111_DIAVENC]
           ,[TB02111_DTINICIO]
           ,[TB02111_DTREAJUSTE]
           ,[TB02111_DTENCERRA]
           ,[TB02111_INDICE]
           ,[TB02111_ULTINDICE]
           ,[TB02111_DTULTAJUSTE]
           ,[TB02111_NOME]
           ,[TB02111_VLRNOTAPB]
           ,[TB02111_VLRNOTACL]
           ,[TB02111_VLRNOTADG]
           ,[TB02111_VLRNOTAGF]
           ,[TB02111_CAUCAO]
           ,[TB02111_CODEMP2]
           ,[TB02111_FRANQGFC]
           ,[TB02111_OBSNOTA]
           ,[TB02111_QTDECONTRATA]
           ,[TB02111_VLRCONTRATA]
           ,[TB02111_NOMENOTA]
           ,[TB02111_ATA]
           ,[TB02111_PARTICULAR]
           ,[TB02111_PAPEL]
           ,[TB02111_VENDA]
           ,[TB02111_DURACAO]
           ,[TB02111_NUMOPER]
           ,[TB02111_DOCUMENTO]
           ,[TB02111_OPERADOR]
           ,[TB02111_VLRNOTAGFC]
           ,[TB02111_GERARPREV]
           ,[TB02111_DATAPREV]
           ,[TB02111_USERPREV]
           ,[TB02111_VLRFIXO]
           ,[TB02111_CONDPAG]
           ,[TB02111_MODEXTRATO]
           ,[TB02111_EMAIL]
           ,[TB02111_CODCON]
           ,[TB02111_FATSUPRIMENTO]
           ,[TB02111_VLRNOTASUP]
           ,[TB02111_COBERTURA]
           ,[TB02111_WHATSAPP]
           ,[TB02111_MULTFAT]
           ,[TB02111_MULTFORMA]
           ,[TB02111_ENVIADOC]
           ,[TB02111_TIPOCOB]
           ,[TB02111_ALIQINSS]
           ,[TB02111_PRECONTRATO]
           ,[TB02111_CONTROLESLA])
     SELECT TOP 1
            GETDATE() --[TB02111_DTCAD]
           ,@User --[TB02111_OPCAD]
           ,[TB02111_DTALT]
           ,NULL--[TB02111_OPALT]
           ,@ContratoNov --[TB02111_CODIGO]
           ,@CodCli --[TB02111_CODCLI]
           ,Itens.Qtde --[TB02111_QTDE]
           ,Itens.Vlrtotal--[TB02111_VLRNOTA]
           ,'A'--[TB02111_SITUACAO]
           ,[TB02111_FRANQPB]
           ,[TB02111_FRANQCOLOR]
           ,[TB02111_TIPOCONTR]
           ,[TB02111_DTVALIDADE]
           ,[TB02111_TIPOATENDE]
           ,[TB02111_DIALEITURA]
           ,[TB02111_COPIAGARANTIA]
           ,[TB02111_VEND]
           ,[TB02111_OBS]
           ,[TB02111_VENCCONTR]
           ,[TB02111_CODEMP]
           ,[TB02111_TIPOFRANQUIA]
           ,[TB02111_ANALFRANQUIA]
           ,[TB02111_FRANQTOTAL]
           ,[TB02111_FRANQDG]
           ,[TB02111_FRANQGF]
           ,[TB02111_TIR01]
           ,[TB02111_TIR02]
           ,[TB02111_TIR03]
           ,[TB02111_TIR04]
           ,[TB02111_TIR05]
           ,[TB02111_TIR06]
           ,[TB02111_TIR07]
           ,[TB02111_TIR08]
           ,[TB02111_TIR09]
           ,[TB02111_TIR10]
           ,[TB02111_TIR11]
           ,[TB02111_TIR12]
           ,[TB02111_EXC01]
           ,[TB02111_EXC02]
           ,[TB02111_EXC03]
           ,[TB02111_EXC04]
           ,[TB02111_EXC05]
           ,[TB02111_EXC06]
           ,[TB02111_EXC07]
           ,[TB02111_EXC08]
           ,[TB02111_EXC09]
           ,[TB02111_EXC10]
           ,[TB02111_EXC11]
           ,[TB02111_EXC12]
           ,[TB02111_RETPIS]
           ,[TB02111_RETCOFINS]
           ,[TB02111_RETCSLL]
           ,[TB02111_RETIR]
           ,[TB02111_CODCEN]
           ,[TB02111_CODSUB]
           ,[TB02111_PLANCON]
           ,[TB02111_FRA01]
           ,[TB02111_FRA02]
           ,[TB02111_FRA03]
           ,[TB02111_FRA04]
           ,[TB02111_FRA05]
           ,[TB02111_FRA06]
           ,[TB02111_FRA07]
           ,[TB02111_FRA08]
           ,[TB02111_FRA09]
           ,[TB02111_FRA10]
           ,[TB02111_FRA11]
           ,[TB02111_FRA12]
           ,[TB02111_ALIQPIS]
           ,[TB02111_ALIQCOFINS]
           ,[TB02111_ALIQCSLL]
           ,[TB02111_ALIQIR]
           ,[TB02111_RATEIO]
           ,[TB02111_RETISS]
           ,[TB02111_ALIQISS]
           ,[TB02111_RETINSS]
           ,[TB02111_PRORATA]
           ,[TB02111_DIAVENC]
           ,GETDATE() --[TB02111_DTINICIO]
           ,GETDATE()--[TB02111_DTREAJUSTE]
           ,[TB02111_DTENCERRA]
           ,[TB02111_INDICE]
           ,[TB02111_ULTINDICE]
           ,GETDATE()--[TB02111_DTULTAJUSTE]
           ,[TB02111_NOME]
           ,[TB02111_VLRNOTAPB]
           ,[TB02111_VLRNOTACL]
           ,[TB02111_VLRNOTADG]
           ,[TB02111_VLRNOTAGF]
           ,[TB02111_CAUCAO]
           ,[TB02111_CODEMP2]
           ,[TB02111_FRANQGFC]
           ,[TB02111_OBSNOTA]
           ,[TB02111_QTDECONTRATA]
           ,[TB02111_VLRCONTRATA]
           ,[TB02111_NOMENOTA]
           ,[TB02111_ATA]
           ,[TB02111_PARTICULAR]
           ,[TB02111_PAPEL]
           ,[TB02111_VENDA]
           ,[TB02111_DURACAO]
           ,[TB02111_NUMOPER]
           ,[TB02111_DOCUMENTO]
           ,@User--[TB02111_OPERADOR]
           ,[TB02111_VLRNOTAGFC]
           ,[TB02111_GERARPREV]
           ,[TB02111_DATAPREV]
           ,[TB02111_USERPREV]
           ,[TB02111_VLRFIXO]
           ,[TB02111_CONDPAG]
           ,[TB02111_MODEXTRATO]
           ,[TB02111_EMAIL]
           ,[TB02111_CODCON]
           ,[TB02111_FATSUPRIMENTO]
           ,[TB02111_VLRNOTASUP]
           ,[TB02111_COBERTURA]
           ,NULL--[TB02111_WHATSAPP]
           ,[TB02111_MULTFAT]
           ,[TB02111_MULTFORMA]
           ,[TB02111_ENVIADOC]
           ,[TB02111_TIPOCOB]
           ,[TB02111_ALIQINSS]
           ,[TB02111_PRECONTRATO]
           ,[TB02111_CONTROLESLA]

		   FROM TB02111
		   OUTER APPLY(
				SELECT 
					COUNT(TB02112_PAT) Qtde,
					SUM(TB02112_CUSTO) Vlrtotal
				FROM TB02112
				WHERE TB02112_CODIGO = @ContratoAnt
				AND TB02112_PAT IN (SELECT PAT FROM @PatsTable)
				AND TB02112_SITUACAO  = 'A'
		   ) AS Itens
		   WHERE TB02111_CODIGO = @ContratoAnt
		   --AND TB02111_SITUACAO = 'A'


    WAITFOR DELAY '00:00:01';

    INSERT INTO [dbo].[TB02112]
           ([TB02112_DTCAD]
           ,[TB02112_OPCAD]
           ,[TB02112_DTALT]
           ,[TB02112_OPALT]
           ,[TB02112_CODIGO]
           ,[TB02112_PRODUTO]
           ,[TB02112_NUMSERIE]
           ,[TB02112_DATA]
           ,[TB02112_END]
           ,[TB02112_NUM]
           ,[TB02112_COMP]
           ,[TB02112_BAIRRO]
           ,[TB02112_CIDADE]
           ,[TB02112_ESTADO]
           ,[TB02112_CEP]
           ,[TB02112_FONE]
           ,[TB02112_FONEAUX]
           ,[TB02112_FAX]
           ,[TB02112_CONTATO]
           ,[TB02112_EMAIL]
           ,[TB02112_TIPOINTERV]
           ,[TB02112_HORAS]
           ,[TB02112_TIPOHORAS]
           ,[TB02112_CODTEC]
           ,[TB02112_PREST]
           ,[TB02112_OBS]
           ,[TB02112_NOVO]
           ,[TB02112_VEND]
           ,[TB02112_TIPODESC]
           ,[TB02112_CONDPAG]
           ,[TB02112_STATUS]
           ,[TB02112_CODVENDA]
           ,[TB02112_SITUACAO]
           ,[TB02112_DTINATIVO]
           ,[TB02112_LOCAL]
           ,[TB02112_IBTRACKER1]
           ,[TB02112_IBTRACKER2]
           ,[TB02112_CUSTO]
           ,[TB02112_PAT]
           ,[TB02112_FRANQPB]
           ,[TB02112_FRANQCOLOR]
           ,[TB02112_FRANQTOTAL]
           ,[TB02112_FRANQDG]
           ,[TB02112_FRANQGF]
           ,[TB02112_NETNAME]
           ,[TB02112_IP]
           ,[TB02112_ORIG]
           ,[TB02112_TIPOEQUIP]
           ,[TB02112_NDDID]
           ,[TB02112_NDDSITE]
           ,[TB02112_NDDDEPTO]
           ,[TB02112_ETIQSERV]
           ,[TB02112_FRANQGFC]
           ,[TB02112_HORASPAPEL]
           ,[TB02112_MODULO]
           ,[TB02112_HORASSUP]
           ,[TB02112_HORASATENDE]
           ,[TB02112_TIPOINSTALL]
           ,[TB02112_GRUPO]
           ,[TB02112_CODSITE]
           ,[TB02112_CODDEPTO]
           ,[TB02112_LIBLEITURA]
           ,[TB02112_NUMSERIEINTEGRADO]
           ,[TB02112_PRECONTRATO]
           ,[TB02112_TIPOSLA])
    SELECT 
           GETDATE() --[TB02112_DTCAD]
           ,@User --[TB02112_OPCAD]
           ,GETDATE() --[TB02112_DTALT]
           ,NULL --[TB02112_OPALT]
           ,@ContratoNov --[TB02112_CODIGO]
           ,A.TB02112_PRODUTO
           ,A.TB02112_NUMSERIE
           ,A.TB02112_DATA --[TB02112_DATA]
           ,A.[TB02112_END]
           ,A.[TB02112_NUM]
           ,A.[TB02112_COMP]
           ,A.[TB02112_BAIRRO]
           ,A.[TB02112_CIDADE]
           ,A.[TB02112_ESTADO]
           ,A.[TB02112_CEP]
           ,A.[TB02112_FONE]
           ,A.[TB02112_FONEAUX]
           ,A.[TB02112_FAX]
           ,A.[TB02112_CONTATO]
           ,A.[TB02112_EMAIL]
           ,A.[TB02112_TIPOINTERV]
           ,A.[TB02112_HORAS]
           ,A.[TB02112_TIPOHORAS]
           ,A.[TB02112_CODTEC]
           ,A.[TB02112_PREST]
           ,A.[TB02112_OBS]
           ,A.[TB02112_NOVO]
           ,A.[TB02112_VEND]
           ,A.[TB02112_TIPODESC]
           ,A.[TB02112_CONDPAG]
           ,A.[TB02112_STATUS]
           ,A.[TB02112_CODVENDA]
           ,'A'--A.TB02112_SITUACAO
           ,A.[TB02112_DTINATIVO]
           ,A.[TB02112_LOCAL]
           ,A.[TB02112_IBTRACKER1]
           ,A.[TB02112_IBTRACKER2]
           ,A.TB02112_CUSTO
           ,A.TB02112_PAT
           ,A.[TB02112_FRANQPB]
           ,A.[TB02112_FRANQCOLOR]
           ,A.[TB02112_FRANQTOTAL]
           ,A.[TB02112_FRANQDG]
           ,A.[TB02112_FRANQGF]
           ,A.[TB02112_NETNAME]
           ,A.[TB02112_IP]
           ,A.[TB02112_ORIG]
           ,A.[TB02112_TIPOEQUIP]
           ,A.[TB02112_NDDID]
           ,A.[TB02112_NDDSITE]
           ,A.[TB02112_NDDDEPTO]
           ,A.[TB02112_ETIQSERV]
           ,A.[TB02112_FRANQGFC]
           ,A.[TB02112_HORASPAPEL]
           ,A.[TB02112_MODULO]
           ,A.[TB02112_HORASSUP]
           ,A.[TB02112_HORASATENDE]
           ,A.[TB02112_TIPOINSTALL]
           ,A.[TB02112_GRUPO]
           ,A.[TB02112_CODSITE]
           ,A.[TB02112_CODDEPTO]
           ,A.[TB02112_LIBLEITURA]
           ,A.[TB02112_NUMSERIEINTEGRADO]
           ,A.[TB02112_PRECONTRATO]
           ,A.[TB02112_TIPOSLA]
    FROM TB02112 A
    WHERE A.TB02112_PAT IN (SELECT PAT FROM @PatsTable)
      AND A.TB02112_CODIGO = @ContratoAnt
      AND A.TB02112_SITUACAO = 'A'


	--Copia as coberturas
	INSERT INTO [dbo].[TB02113]
           ([TB02113_DTCAD]
           ,[TB02113_OPCAD]
           ,[TB02113_DTALT]
           ,[TB02113_OPALT]
           ,[TB02113_CODIGO]
           ,[TB02113_PRODUTO]
           ,[TB02113_NUMSERIE]
           ,[TB02113_COBERTURA]
           ,[TB02113_VALIDADE]
           ,[TB02113_VALOR]
           ,[TB02113_VALORB]
           ,[TB02113_COMISSAO])
     SELECT DISTINCT
            GETDATE()			   --[TB02113_DTCAD]
           ,@User 				   --[TB02113_OPCAD]
           ,NULL				   --[TB02113_DTALT]
           ,NULL				   --[TB02113_OPALT]
           ,@ContratoNov		   --[TB02113_CODIGO]
           ,TB02113_PRODUTO		   --[TB02113_PRODUTO]
           ,TB02113_NUMSERIE	   --[TB02113_NUMSERIE]
           ,TB02113_COBERTURA	   --[TB02113_COBERTURA]
           ,TB02113_VALIDADE	   --[TB02113_VALIDADE]
           ,TB02113_VALOR		   --[TB02113_VALOR]
           ,TB02113_VALORB		   --[TB02113_VALORB]
           ,TB02113_COMISSAO	   --[TB02113_COMISSAO]
	 FROM TB02113				   
	 LEFT JOIN TB02054 ON TB02054_NUMSERIE = TB02113_NUMSERIE
	 WHERE TB02054_PAT IN (SELECT PAT FROM @PatsTable)
	 AND TB02113_CODIGO = @ContratoAnt

	  

	UPDATE 
		TB02112 
	SET TB02112_SITUACAO = 'I'
	WHERE TB02112_CODIGO = @ContratoAnt
	AND TB02112_PAT IN (SELECT PAT FROM @PatsTable)

	UPDATE 
		TB02111
	SET TB02111_SITUACAO = 'I'
	WHERE TB02111_CODIGO = @ContratoAnt

	UPDATE TB02176
	SET TB02176_CODCLI = @CodCli,
		TB02176_OPALT = 'APP_CONTRATOS',
		TB02176_DTALT = GETDATE()
	WHERE TB02176_CONTRATO = @ContratoAnt
	AND TB02176_CODIGO IN (SELECT TB02112_CODSITE FROM TB02112 WHERE TB02112_PAT IN (SELECT PAT FROM @PatsTable))
	
	 --copia as acessorios 
	 if @Acessorios = 'S'
			BEGIN
			INSERT INTO [dbo].[TB02114]
					   ([TB02114_DTCAD]
					   ,[TB02114_OPCAD]
					   ,[TB02114_DTALT]
					   ,[TB02114_OPALT]
					   ,[TB02114_CODIGO]
					   ,[TB02114_PRODUTO]
					   ,[TB02114_NUMSERIE]
					   ,[TB02114_ACESSORIO]
					   ,[TB02114_QTPROD]
					   ,[TB02114_OBS]
					   ,[TB02114_NUMSERIEACESSORIO])
				SELECT DISTINCT
					   GETDATE()
					  ,@User
					  ,NULL
					  ,NULL
					  ,@ContratoNov
					  ,[TB02114_PRODUTO]
					  ,[TB02114_NUMSERIE]
					  ,[TB02114_ACESSORIO]
					  ,[TB02114_QTPROD]
					  ,'Inserido via app web'
					  ,[TB02114_NUMSERIEACESSORIO]
				  FROM [dbo].[TB02114]
				 LEFT JOIN TB02054 ON TB02054_NUMSERIE = TB02114_NUMSERIE
					AND TB02054_PRODUTO = TB02114_PRODUTO
				 WHERE TB02114_CODIGO = @ContratoAnt
				 AND TB02054_PAT IN (SELECT PAT FROM @PatsTable)
		END
END
