ALTER PROCEDURE [dbo].[UpdateCRMState]  
@State Varchar(20),
@Extention_No varchar(10),
@Phone_No Varchar(15),
@Unique_Id Varchar(30),
@Agent_Name Varchar(50),
@Pid varchar(10),
@Campaign_Id varchar(10),
@sBreakName varchar(15),
@iBreakID varchar(10),
@OutIn varchar(10) = NULL,
@DialTime varchar = NULL
AS 
BEGIN   
   DECLARE @iAttendanceId Int  ,@ACD_Code_Ext Varchar(5)
   if (@Extention_no is not null)
    begin
   if(@Campaign_Id = '0' or @Campaign_Id is null)
   begin
		select top 1 @Campaign_Id = Campaign_Master.Campaign_Id,@Pid = Campaign_Master.Process_Id from Campaign_Master,Admin_Extention_Master
		where Campaign_Master.ACD_Code = Admin_Extention_Master.ACD_Code_Ext and Extention_No = @Extention_No
   end
   
   if @OutIn IS NULL 
     SET @OutIn = '' 
   IF(@State = 'Wrapup') 
		BEGIN
		   DECLARE @Crm_State Varchar(20);
		   SELECT @Crm_State = CRM_State FROM Admin_Extention_Master WHERE Extention_No = @Extention_No
		   If (@Crm_State is null or (@Crm_State != 'Outgoing' and @Crm_State != 'Incoming' and @Crm_State != 'Manual'))
			   return 0
		   else If (@Crm_State = 'Outgoing' or @Crm_State = 'Manual' OR (@Crm_State = 'Incoming' and (SELECT SrNo FROM Admin_Extention_Master WHERE Extention_No = @Extention_No) > 0))
				SET @OutIn = 'Out'
		   else 
		        SET @OutIn = 'In'
		   If(SELECT No_CRM FROM Admin_Extention_Master WHERE Extention_No = @Extention_No)	= 'true'
			   SET @State = 'Idle'
		END   
   DECLARE @sqlQuery varchar(MAX);
   DECLARE @sqlQuery1 varchar(MAX);
   DECLARE @sqlQuery2 varchar(MAX);
   SET @sqlQuery = NULL;
   SET @sqlQuery1 = NULL;
   set @sqlQuery2 =NULL

		IF(@State = 'Login')
			BEGIN 
				Select @ACD_Code_Ext=acd_code from campaign_Master Where Campaign_Id=@Campaign_Id
				SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,out_in)' 
				                + 'Values(GETDATE(),'''+ @Extention_No +''','''+ @Agent_Name +''',''Login'',''00:00:00'',NULL,NULL,'+ @Pid +','+ @Campaign_Id +',GETDATE(),NULL,''' + @OutIn + ''')';
				SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET crm_unique_id_ext=NULL,DND=0,CRM_State = ''Break'',State_Time = GETDATE(),Break_ID = 0,ACD_Code_Ext='''+ @ACD_Code_Ext +''',Break_Name = '''',Out_In = ''' + @OutIn + ''' WHERE Extention_No = '''+ @Extention_No +'''';
			END
		ELSE IF (@State = 'Logout' and @Agent_Name IS NOT NULL) 
				BEGIN

					if exists(SELECT 1 FROM Admin_Extention_Master WHERE Agent_Name =@Agent_Name)
						BEGIN
							SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,CallConnected,Out_In) '
					+'(SELECT State_Time,Extention_No,Agent_Name,CRM_State,CONVERT(char(10),DateAdd(ss,DateDiff(ss,State_Time,GETDATE()),''00:00:00''),108),Caller_Id,Called_No,' + @Pid + ',Campaign_Id,GETDATE(),Break_Id,Call_Connected,Out_In FROM Admin_Extention_Master WHERE Extention_No = '''+ @Extention_No + ''');' 
					+ 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,Out_In)' 
					+ 'Values(GETDATE(),'''+ @Extention_No +''','''+ @Agent_Name +''',''Logout'',''00:00:00'',NULL,NULL,'+ @Pid + ','+ @Campaign_Id + ',GETDATE(),NULL,''' + @OutIn +						''')'; 
					SET @sqlQuery1='UPDATE Admin_Extention_Master SET crm_unique_id_ext=NULL,Agent_Name = NULL,pid=0,Campaign_Id = NULL,Caller_Id = NULL,Called_No = NULL,Log_In_Time =NULL,Break_Name = NULL,CRM_State=NULL WHERE Extention_No ='''+ @Extention_No +''''
					
					END
				END
		ELSE IF(@State = 'Missed') 
			BEGIN         
				SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,CallConnected,Out_In)'
				+' (SELECT State_Time,Extention_No,Agent_Name,''Missed'',CONVERT(char(10),DateAdd(ss,DateDiff(ss,State_Time,GETDATE()),''00:00:00''),108),Caller_Id,Called_No,' + @Pid + ',' + @Campaign_Id + ',GETDATE(),Break_Id,Call_Connected,Out_In FROM Admin_Extention_Master WHERE Extention_No = ''' + @Extention_No + ''')';
				--SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET CRM_State = ''Idle'',State_Time = GETDATE(),Break_ID = NULL,Break_Name = '''',Call_Connected = 0,Out_In = ''' + @OutIn + ''' WHERE Extention_No = '''+ @Extention_No +''''; 
				SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET SAN_MASTER_Id = NULL,Caller_Id = NULL,CRM_State = ''Idle'',State_Time = GETDATE(),Break_ID = NULL,Break_Name = '''',Call_Connected = 0,Out_In = ''' + @OutIn + ''' WHERE Extention_No = '''+ @Extention_No +''''; 
		
			END 
		ELSE
			BEGIN 
				IF(@State = 'Break')
					BEGIN
						SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,CallConnected,Out_In)' + '(SELECT State_Time,Extention_No,Agent_Name,CRM_State,CONVERT(char(10),DateAdd(ss,DateDiff(ss,State_Time,GETDATE()),''00:00:00''),108)' + ',Caller_Id,Called_No,' + @Pid + ','+ @Campaign_Id +',GETDATE(),Break_ID,Call_Connected,Out_In FROM Admin_Extention_Master WHERE  Extention_No = '''+ @Extention_No + ''')';
						SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET Called_No = '''+ @Phone_No +''',Dial_Time = NULL,CRM_State = ''' + @State + ''',State_Time = GETDATE(),Break_ID = ' + @iBreakID + ',Break_Name=''' + @sBreakName + ''''; 

					END
				---Updated On 03/09/2013 for Zauri Money
				--ELSE IF(@State = 'Manual')
				--		BEGIN 
				--		  SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET Called_No = '''+ @Phone_No +''',Dial_Time = NULL,Break_ID = NULL,Break_Name=''''';
				--		END
			    ---Updated On 03/09/2013
				ELSE
					BEGIN
						SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time,Break_Id,CallConnected,Out_In)'
						+' (SELECT State_Time,Extention_No,Agent_Name,CRM_State,CONVERT(char(10),DateAdd(ss,DateDiff(ss,State_Time,''' + convert(varchar,getdate(),121) + '''),''00:00:00''),108),Caller_Id,Called_No,' + ISNULL(@Pid,'NULL') + ','+ ISNULL(@Campaign_Id,'NULL') +',''' + convert(varchar,getdate(),121) + ''',Break_ID,Call_Connected,Out_In FROM Admin_Extention_Master WHERE Extention_No = '''+ @Extention_No + ''')';
						SET @sqlQuery1 = 'UPDATE Admin_Extention_Master SET Dial_Time = ' + case when @DialTime is null and @State != 'Incoming' then 'null' else 'getdate()' end + ',Campaign_ID = ' + ISNULL(@Campaign_Id,'NULL') +',CRM_State = ''' + @State + ''',State_Time = GETDATE(),Break_ID = NULL,Break_Name='''''; 
					    SET @sqlQuery1 = @sqlQuery1 + ',Called_No = '''+ @Phone_No +'''';
					END
				IF @State = 'Idle' OR @State = 'Break'
						SET @sqlQuery1 = @sqlQuery1 + ',Caller_Id = NULL,SAN_MASTER_Id = NULL,API_NO=NULL,Unique_Id = NULL,Call_Connected = 0,Call_Count = 0'; -- ',EMR_Check=1'
				ELSE 
					BEGIN 
						IF(@Phone_No <> '' AND @Phone_No IS NOT NULL) 
							SET @sqlQuery1 = @sqlQuery1 + ',Caller_Id = NULL'; 
						IF(@Unique_Id <> '' and @Unique_Id IS NOT NULL) 
							SET @sqlQuery1 = @sqlQuery1 + ',Unique_Id = ''' + @Unique_Id + ''''; 
					END 
				SET @sqlQuery1 = @sqlQuery1 + ',Out_In = ''' + @OutIn + ''' WHERE Extention_No = ''' + @Extention_No + ''''; 
				
			END 
			print '  ' + @sqlQuery
			print '  ' + @sqlQuery1
			
		IF (@sqlQuery IS NOT NULL)
				EXEC (@sqlQuery);			
		IF (@sqlQuery1 IS NOT NULL)
				EXEC (@sqlQuery1);
		--IF (@sqlQuery2 IS NOT NULL)
		--		EXEC (@sqlQuery2);

	    ---Attendace Module Start
		IF (@State = 'Login') AND (SELECT COUNT(1) FROM Attendance_LogIn_LogOut WHERE Agent_Name = @Agent_Name AND Convert(Varchar(10),Attendace_Date,103) = Convert(Varchar(10),GETDATE(),103)) = 0
			INSERT INTO Attendance_LogIn_LogOut(Attendace_Date,Agent_Name,LogIn_Date) Values(GETDATE(),@Agent_Name,GETDATE())
		IF (@State = 'Logout')
			BEGIN
				SELECT TOP 1 @iAttendanceId = Id FROM Attendance_LogIn_LogOut WHERE Agent_Name = @Agent_Name ORDER BY Attendace_Date DESC
				if @iAttendanceId > 0
					UPDATE Attendance_LogIn_LogOut SET LogOut_Date = GETDATE() WHERE ID = @iAttendanceId
			END
	    ---Attendace Module End
	    IF @State = 'Idle'
			DELETE FROM Check_Duplicate_Dial WHERE AgentName=@Agent_Name

	return 1
end

END