CREATE PROCEDURE NextPhoneNo @ProcessId Varchar(10),@campaign_Id varchar(10)
AS 
BEGIN
DECLARE @LastCenter_Name varchar(30),@NewCenter_Name nvarchar(30),@Query  nVarchar(MAX);
print getdate();
IF not exists(select campaign_Id FROM LastDialedCenter WHERE Campaign_Id = @Campaign_Id)
	begin
		SET @Query = 'SELECT distinct TOP 1 @NewCenter_Name = Center_Name FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
					+ ' where Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
					+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id 
					+ ' ORDER BY Center_Name ';
		EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		INSERT INTO LastDialedCenter(Campaign_Id,Center_Name) VALUES(@Campaign_Id,@NewCenter_Name);
	end
else
	begin
		SELECT @LastCenter_Name = Center_Name FROM LastDialedCenter WHERE Campaign_Id = @Campaign_Id;
		SELECT @NewCenter_Name = 'ZZZZZZZZZZ';
		SET @Query = 'SELECT distinct TOP 1 @NewCenter_Name  = Center_Name FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
			+ ' where Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
			+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id
		IF (@LastCenter_Name IS NULL)
			SET @Query = @Query + ' and Center_Name >= ''''';
		else
			SET @Query = @Query + ' and Center_Name > ''' + @LastCenter_Name + '''';
		SET @Query = @Query + ' ORDER BY Center_Name ';
		EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		IF(@NewCenter_Name = 'ZZZZZZZZZZ')
		BEGIN
			print 'Last Center';
			SET @Query = 'SELECT distinct TOP 1 @NewCenter_Name = Center_Name FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
						+ ' where Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
						+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id 
						+ ' ORDER BY Center_Name ';
			EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		END
		UPDATE LastDialedCenter SET Center_Name = @NewCenter_Name where Campaign_Id = @Campaign_Id;
	end
Print @NewCenter_Name;
SET @Query = 'SELECT TOP 1 Process_' + @ProcessId + '_master.*,Call_Again_Time Call_Again_Time,Campaign_Mapping.SrNo ' + 'FROM Process_' + @ProcessId + '_Master JOIN Campaign_Mapping ON Process_' + @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id ' + 'WHERE Dialed= 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id
IF (@NewCenter_Name IS NULL)
	SET @Query = @Query + ' AND Center_Name IS NULL';
ELSE
	SET @Query = @Query + ' AND Center_Name = ''' + @NewCenter_Name + '''';
EXEC sp_executesql @Query
print getdate();
END
CREATE TABLE LastDialedCenter (Campaign_Id int,Center_Name Varchar(30))
///****************************New Updated Procedure Branch Wise Calling***************************************///
ALTER PROCEDURE [dbo].[NextPhoneNo] @ProcessId Varchar(10),@Campaign_Id varchar(10)
AS 
BEGIN
DECLARE @LastCenter_Name varchar(30),@NewCenter_Name nvarchar(30),@Query  nVarchar(MAX);
IF not exists(select Campaign_Id FROM LastDialedCenter WHERE Campaign_Id = @Campaign_Id)
	begin
		SET @Query = 'SELECT Distinct TOP 1 @NewCenter_Name = Branch_Id FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
					+ ' WHERE Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
					+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id 
					+ ' ORDER BY Branch_Id ';
		EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		INSERT INTO LastDialedCenter(Campaign_Id,Center_Name) VALUES(@Campaign_Id,@NewCenter_Name);
	end
else
	begin
		SELECT @LastCenter_Name = Center_Name FROM LastDialedCenter WHERE Campaign_Id = @Campaign_Id;
		SELECT @NewCenter_Name = '-1';
		SET @Query = 'SELECT Distinct TOP 1 @NewCenter_Name  = Branch_Id FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
			+ ' WHERE Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
			+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id
		IF (@LastCenter_Name IS NULL)
			SET @Query = @Query + ' AND Branch_Id >= ''''';
		else
			SET @Query = @Query + ' AND Branch_Id > ' + @LastCenter_Name ;
		SET @Query = @Query + ' ORDER BY Branch_Id ';
		EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		IF(@NewCenter_Name = '-1')
		BEGIN
			print 'Last Center';
			SET @Query = 'SELECT Distinct TOP 1 @NewCenter_Name = Branch_Id FROM Process_' + @ProcessId + '_Master,Campaign_Mapping'
						+ ' WHERE Process_'	+ @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id'
						+ ' AND Dialed = 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id 
						+ ' ORDER BY Branch_Id ';
			EXEC sp_executesql @Query, N'@NewCenter_Name nvarchar(30) OUTPUT',@NewCenter_Name = @NewCenter_Name OUTPUT
		END
		UPDATE LastDialedCenter SET Center_Name = @NewCenter_Name where Campaign_Id = @Campaign_Id;
	end
Print @NewCenter_Name;
SET @Query = 'SELECT TOP 1 Process_' + @ProcessId + '_Master.*,Call_Again_Time Call_Again_Time,Campaign_Mapping.SrNo ' + 'FROM Process_' + @ProcessId + '_Master JOIN Campaign_Mapping ON Process_' + @ProcessId + '_Master.Master_Id = Campaign_Mapping.Customer_Id ' + 'WHERE Dialed= 0 AND Call_Again_Time IS NULL AND Campaign_Mapping.Campaign_Id = ' + @Campaign_Id
IF (@NewCenter_Name IS NULL)
	SET @Query = @Query + ' AND Branch_Id IS NULL';
ELSE
	SET @Query = @Query + ' AND Branch_Id = ''' + @NewCenter_Name + '''';
EXEC sp_executesql @Query
print getdate();
END
