CREATE Procedure [dbo].[getCallBack]
@ProcessId varchar(10),
@CampaignId varchar(10),
@AgentName varchar(10)
AS
BEGIN
DECLARE @Callback_AgentMapping bit,@Callback_FromAnyCampaign bit 
SELECT @Callback_AgentMapping = Callback_AgentMapping,@Callback_FromAnyCampaign = Callback_FromAnyCampaign FROM Process WHERE Pid = @ProcessId
DECLARE @SqlQuery Varchar(MAX)
SET @SqlQuery = 'SELECT TOP 1 Phone_No,Master_Id,Call_Again_Time,Call_Again_PhoneNo,Campaign_Id,SrNo,GETDATE() Start_Time FROM Process_' + @ProcessId + '_Master t1 '
+ 'JOIN Campaign_' + @ProcessId + '_Mapping t2 ON t1.Master_Id = t2.Customer_Id LEFT OUTER JOIN File_Master t3 ON t2.File_Id = t3.File_Id WHERE Dialed = 0 AND Call_Again_Time <= GETDATE() '
if(@Callback_AgentMapping = 1)
	SET @SqlQuery = @SqlQuery + ' AND Agent_Id = ''' + @AgentName + ''''
if(@Callback_FromAnyCampaign = 0)
	SET @SqlQuery = @SqlQuery + ' AND Campaign_Id = ''' + @CampaignId + ''''
SET @SqlQuery = @SqlQuery + ' ORDER BY Call_Again_Time'
PRINT @sqlQuery
 --BEGIN TRY
 -- INSERT INTO Sukhveer(ProcessId,CampaignId,AgentName,Callback_AgentMapping,Callback_FromAnyCampaign,sqlQuery) 
  --VALUES(@ProcessId,@CampaignId,@AgentName,@Callback_AgentMapping,@Callback_FromAnyCampaign,@sqlQuery)
 --END TRY
 --BEGIN CATCH
 --END CATCH
EXEC (@SqlQuery)
END


CREATE PROCEDURE [dbo].[getSrNo] @CustomerId Varchar(10),@CampaignId Varchar(10),@FileId Varchar(10)
AS
BEGIN
	Declare @SrNo int
	Declare @PId Varchar(10)
	SELECT @PId = Process_Id FROM Campaign_Master WHERE Campaign_Id = @CampaignId
	Declare @SQLQuery nvarchar(200)
	----------------------Get SrNo From Camapign Mapping -------------------
	SET @SQLQuery = 'SELECT @SrNoOut = SrNo FROM Campaign_' + @PId + '_Mapping WHERE Customer_Id = ' + @CustomerId + ' AND Campaign_Id = ' + @CampaignId
	if @FileId <> 1
	  SET @SQLQuery = @SQLQuery + ' AND File_Id = ' + @FileId
	PRINT @SQLQuery  	
	EXECUTE sp_executesql @SQLQuery, N'@SrNoOUT Int OUTPUT', @SrNoOUT = @SrNo OUTPUT
	----------------------Get SrNo From Camapign Mapping -------------------
	---------------------- If SrNo Does not exist insert into Camapign Mapping -------------------
	if(@SrNo IS NULL OR @SrNo = 0 or @SrNo = '')
		BEGIN
		 SET @SQLQuery = 'INSERT INTO Campaign_' + @PId + '_Mapping(Customer_Id,Campaign_Id,File_Id,Dialed) VALUES(' + @CustomerId + ',' + @CampaignId + ',' + @FileId+ ',''True'')'
		 SET @SQLQuery = @SQLQuery + ';SELECT @SrNoOUT = Scope_IDENTITY()'
		 PRINT @SQLQuery
		 EXECUTE sp_executesql @SQLQuery, N'@SrNoOUT Int OUTPUT', @SrNoOUT = @SrNo OUTPUT
		END	
	---------------------- If SrNo Does not exist insert into Camapign Mapping -------------------	
	SELECT	'Return Value' = @SrNo	
END


