IF EXISTS(SELECT 1 FROM sysobjects WHERE name ='pa_Archive_Collection_Management' AND type = 'P') 
	DROP PROCEDURE pa_Archive_Collection_Management

GO

create procedure [dbo].[pa_Archive_Collection_Management] 
/*
	CDA Plus procedure to archive Collections Management history.
*/
	@FailedTableName	varchar(40)	= NULL,
	@RemoveTableName	varchar(40)	= NULL,
 	@Transfer  			tinyint 		= NULL,  
	@Remove				tinyint 		= NULL,  
	@CutOffDate 		datetime	= NULL ,  
	@FromCompany 		varchar (40) 	= NULL,  
	@ToCompany 			varchar (5) 	= NULL,
	@WhichDate			smallint		= NULL


as  
declare 
	@Loop 		tinyint,   
	@tTransaction 	tinyint,  
	@iError 	integer,
	@TRUE		tinyint,
	@FALSE		tinyint,
	@ExecString	varchar(1000),
	@sTrxSize	char(12),
	@ExecString2 	varchar(1000),
	@ErrorState	int,
	@Success	int
	   
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET XACT_ABORT ON

select	
	@iError 		= 0,
	@TRUE			= 1,
	@FALSE			= 0

while (@Loop is null) begin  
	select @Loop = 1

	/*	 prepare the variables for an exec statement 	*/
	select  
		@ToCompany 	= rtrim(@ToCompany),
		@FromCompany = rtrim(@FromCompany)


	if @@trancount = 0 begin  
		select @tTransaction = 1  
		begin transaction 
	end

	exec pa_AddSQLTableCounts 'CN00100', @ToCompany, 1
	exec pa_AddSQLTableCounts 'CN00300', @ToCompany, 1
	exec pa_AddSQLTableCounts 'CN30100', @ToCompany, 1
	exec pa_AddSQLTableCounts 'CN30200', @ToCompany, 1
	exec pa_AddSQLTableCounts 'CN30300', @ToCompany, 1
	exec pa_AddSQLTableCounts 'CN20100', @ToCompany, 1

	/*	count the records in the From Database 	*/
	exec pa_AddSQLTableCounts 'CN00100', @FromCompany, 3
	exec pa_AddSQLTableCounts 'CN00300', @FromCompany, 3
	exec pa_AddSQLTableCounts 'CN30100', @FromCompany, 3
	exec pa_AddSQLTableCounts 'CN30200', @FromCompany, 3
	exec pa_AddSQLTableCounts 'CN30300', @FromCompany, 3
	exec pa_AddSQLTableCounts 'CN20100', @FromCompany, 3

	create table #CMTransfer
	(
		NOTEINDX    int,
		CUSTNMBR	CHAR(15),
		NOTEDATE	datetime
	)

	create table #CMRemove
		(
		NOTEINDX    int,
		CUSTNMBR	CHAR(15),
		NOTEDATE	datetime
		)

	create table #CMUpdate
	(
		NOTEINDX    int,
		CUSTNMBR	CHAR(15),
		NOTEDATE	datetime
	)

	if @Transfer = @TRUE begin
		/* Get Notes in production 	*/
		select @ExecString = 	'insert into #CMTransfer '+ 
		     		'select distinct NOTEINDX, CUSTNMBR,Contact_Date from ' + 
		     		@FromCompany + '.dbo.CN00100 where NoteStatus>1 AND Contact_Date <= ''' + 
		     		rtrim(convert(char(12),@CutOffDate,112)) + ''''

		exec (@ExecString)
		select @iError = @@error
		if @iError <> 0 break
			
		/* Get notes that are in archive to give us the list of rows to update */
		select @ExecString = 	'insert into #CMUpdate '+ 
		     		'select distinct NOTEINDX, CUSTNMBR,Contact_Date from ' + 
		     		@ToCompany + '.dbo.CN00100'
		exec (@ExecString)
		select @iError = @@error
		if @iError <> 0 break
		
		exec('delete #CMUpdate where #CMUpdate.NOTEINDX not in (select NOTEINDX from #CMTransfer)')
		select @iError = @@error
		if @iError <> 0 break

		exec('insert into ' + @RemoveTableName + ' select 1157, concat(''Transferred Note Index:'',#CMUpdate.NOTEINDX,'' Customer:'', #CMUpdate.CUSTNMBR ,'' Date:'',#CMUpdate.NOTEDATE),''Transferred Note'',0 from #CMUpdate')  
		select @iError = @@error
		if @iError <> 0 break

	
		exec('delete #CMTransfer where #CMTransfer.NOTEINDX in (select NOTEINDX from #CMUpdate)')
		select @iError = @@error
		if @iError <> 0 break


		/*	 Populate the reporting table 	*/
		exec('insert into ' + @RemoveTableName + ' select 1157 ,concat(''Transferred Note Index:'',#CMTransfer.NOTEINDX,'' Customer:'', #CMTransfer.CUSTNMBR ,'' Date:'',#CMTransfer.NOTEDATE),''Transferred Note'',0 from #CMTransfer')  
		select @iError = @@error
		if @iError <> 0 break

		exec(  'insert into  ' + @ToCompany + '..CN00100 select 
		cm.CUSTNMBR,CPRCSTNM,DATE1,Contact_Date,TIME1,Contact_Time,cm.NOTEINDX,RevisionNumber,CN_Group_Note,Caller_ID_String,Action_Promised,ActionType,
		Action_Date,Action_Assigned_To,Action_Completed,ACTCMDSP,Action_Completed_Date,Action_Completed_Time,Amount_Promised,Amount_Received,
		USERID,Note_Display_String,CNTCPRSN,ADRSCODE,USERDEF1,USERDEF2,USRDAT01,PRIORT,NOTECAT,NoteStatus,Action_Cancelled_By,Action_Cancelled_Date,
		MODIFDT,CN_Pinned from   ' + @FromCompany + '.dbo.CN00100 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break

		exec(  'insert into  ' + @ToCompany + '..CN00300 select 
		cm.NOTEINDX,TXTFIELD from   ' + @FromCompany + '.dbo.CN00300 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break
 
		exec(  'insert into  ' + @ToCompany + '..CN30100 select 
		cm.CUSTNMBR,CPRCSTNM,DATE1,Contact_Date,TIME1,Contact_Time,cm.NOTEINDX,RevisionNumber,RevisionDate,RevisionTime,CN_Group_Note,Caller_ID_String,Action_Promised,ActionType,
		Action_Date,Action_Assigned_To,Action_Completed,Action_Completed_Date,Action_Completed_Time,Amount_Promised,USERID,Note_Display_String,
		CNTCPRSN,ADRSCODE,USERDEF1,USERDEF2,USRDAT01,PRIORT,NOTECAT,NoteStatus,Action_Cancelled_By,Action_Cancelled_Date,
		MODIFDT from   ' + @FromCompany + '.dbo.CN30100 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break

		exec(  'insert into  ' + @ToCompany + '..CN30200 select 
		cm.CUSTNMBR,CPRCSTNM,RMDTYPAL,DOCNUMBR,cm.NOTEINDX,RevisionNumber,RevisionDate,RevisionTime,USERID,ActionType,ActionAmount,CURTRXAM,CURNCYID,CURRNIDX
		 from ' + @FromCompany + '.dbo.CN30200 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break

		exec(  'insert into  ' + @ToCompany + '..CN30300 select 
		cm.NOTEINDX,Revision_Number,TXTFIELD from   ' + @FromCompany + '.dbo.CN30300 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break

		exec(  'insert into  ' + @ToCompany + '..CN20100 select 
		cm.CUSTNMBR,CPRCSTNM,RMDTYPAL,DOCNUMBR,cm.NOTEINDX,ActionType,ActionAmount,CURTRXAM,CURNCYID,CURRNIDX,AGNGBUKT,DOCDATE
		 from ' + @FromCompany + '.dbo.CN20100 cm, #CMTransfer temp where cm.NOTEINDX=temp.NOTEINDX ')

		select @iError = @@error
		if @iError <> 0 break

	end

	if @Remove = @TRUE begin
		/* Get Notes in production to remove */
		select @ExecString = 	'insert into #CMRemove '+ 
		     		'select distinct NOTEINDX, CUSTNMBR,Contact_Date from ' + 
		     		@FromCompany + '.dbo.CN00100 where NoteStatus>1 AND Contact_Date <= ''' + 
		     		rtrim(convert(char(12),@CutOffDate,112)) + ''''

		exec (@ExecString)

		exec('insert into ' + @RemoveTableName + ' select 1157 ,concat(''Removed Note Index:'',#CMRemove.NOTEINDX,'' Customer:'', #CMRemove.CUSTNMBR ,'' Date:'',#CMRemove.NOTEDATE),''Removed Note'',0 from #CMRemove')  

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN00100 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')

		select @iError = @@error
		if @iError <> 0 break

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN00300 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')
		select @iError = @@error
		if @iError <> 0 break

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN30100 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')
		select @iError = @@error
		if @iError <> 0 break

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN30200 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')
		select @iError = @@error
		if @iError <> 0 break

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN30300 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')
		select @iError = @@error
		if @iError <> 0 break

		/*  */
		exec(	'delete P from ' + @FromCompany + '.dbo.CN20100 as P where exists (select 1 from #CMRemove cmtemp where P.NOTEINDX = cmtemp.NOTEINDX)')
		select @iError = @@error
		if @iError <> 0 break

	end

	exec pa_AddSQLTableCounts 'CN00100', @ToCompany, 2
	exec pa_AddSQLTableCounts 'CN00300', @ToCompany, 2
	exec pa_AddSQLTableCounts 'CN30100', @ToCompany, 2
	exec pa_AddSQLTableCounts 'CN30200', @ToCompany, 2
	exec pa_AddSQLTableCounts 'CN30300', @ToCompany, 2
	exec pa_AddSQLTableCounts 'CN20100', @ToCompany, 2

	/*	count the records in the From Database 	*/
	exec pa_AddSQLTableCounts 'CN00100', @FromCompany, 4
	exec pa_AddSQLTableCounts 'CN00300', @FromCompany, 4
	exec pa_AddSQLTableCounts 'CN30100', @FromCompany, 4
	exec pa_AddSQLTableCounts 'CN30200', @FromCompany, 4
	exec pa_AddSQLTableCounts 'CN30300', @FromCompany, 4
	exec pa_AddSQLTableCounts 'CN20100', @FromCompany, 4
end


select @ErrorState = @iError

if @ErrorState <> 0 
begin  
	if @tTransaction = 1  
		rollback transaction 
end 
else 
begin  
	if @tTransaction = 1  
		commit transaction 
end 

GO

IF EXISTS(SELECT 1 FROM sysobjects WHERE name ='pa_Archive_CM_Preview' AND type = 'P') 
	DROP PROCEDURE pa_Archive_CM_Preview

GO

create procedure [dbo].[pa_Archive_CM_Preview] 
	@I_Company 		varchar(40) 	= NULL ,
	@I_CutOffDate 	datetime	= NULL ,  
	@I_WhichDate	int	= NULL
as  
declare @ExecString varchar (2000),
			@t_Rows int
			
select @t_Rows = 0
create table #RowCountTable
(
	NumberOfRows	int
)

select @ExecString = 'insert into #RowCountTable select count(*) from '+@I_Company+'..CN00100 C where C.Contact_Date <= '''+ rtrim(convert(char(12),@I_CutOffDate,112)) + ''''
exec (@ExecString)
select @t_Rows = (select distinct NumberOfRows from #RowCountTable)
update paISVPreview set Count1=@t_Rows where PRODID=1157 and MODULEID=0
delete #RowCountTable

GO

IF EXISTS(SELECT 1 FROM sysobjects WHERE name ='pa_Archive_CM_SizingLogic' AND type = 'P') 
	DROP PROCEDURE pa_Archive_CM_SizingLogic


GO

create procedure [dbo].[pa_Archive_CM_SizingLogic]

	@Company	varchar(40),
	@Year		int,
	@StartDate	datetime,
	@EndDate	datetime
as
declare @ExecString varchar(2000)
declare @RCount int

SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET XACT_ABORT ON

	/* Step 1 */
	create table #CollectTemp
	(
		NoteIndex	CHAR(17)
	)
	create table #RowCountTable
	(
		NumberOfRows	int
	)
	delete #CollectTemp

	select @ExecString = 'insert into #CollectTemp select NOTEINDX from '+@Company+'..CN00100 S where S.Contact_Date between '''+ rtrim(convert(char(12),@StartDate,112)) + ''''+
			' and '''+ rtrim(convert(char(12),@EndDate,112)) + ''''
	exec (@ExecString)
	select @RCount = (select count(*) from #CollectTemp)
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN00100'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)
	
	delete #RowCountTable
	exec( 'insert into #RowCountTable select count(*) from '+@Company+'..CN30200 C where C.NOTEINDX in (select NOTEINDX from #CollectTemp)')
	select @RCount = (select distinct NumberOfRows from #RowCountTable)
	delete #RowCountTable
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN30200'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)

	delete #RowCountTable
	exec( 'insert into #RowCountTable select count(*) from '+@Company+'..CN30100 C where C.NOTEINDX in (select NOTEINDX from #CollectTemp)')
	select @RCount = (select distinct NumberOfRows from #RowCountTable)
	delete #RowCountTable
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN30100'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)

	delete #RowCountTable
	exec( 'insert into #RowCountTable select count(*) from '+@Company+'..CN30300 C where C.NOTEINDX in (select NOTEINDX from #CollectTemp)')
	select @RCount = (select distinct NumberOfRows from #RowCountTable)
	delete #RowCountTable
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN30300'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)

	delete #RowCountTable
	exec( 'insert into #RowCountTable select count(*) from '+@Company+'..CN20100 C where C.NOTEINDX in (select NOTEINDX from #CollectTemp)')
	select @RCount = (select distinct NumberOfRows from #RowCountTable)
	delete #RowCountTable
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN20100'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)

	delete #RowCountTable
	exec( 'insert into #RowCountTable select count(*) from '+@Company+'..CN00300 C where C.NOTEINDX in (select NOTEINDX from #CollectTemp)')
	select @RCount = (select distinct NumberOfRows from #RowCountTable)
	delete #RowCountTable
		
	select @ExecString = 'insert into '+@Company+'..paDBDist values ('+convert(char,@Year) +',1157,0,''CN00300'','+ convert(char,@RCount)+',0)'
	exec (@ExecString)


