CREATE PROCEDURE
UpdateCRMState
	UpdateCRMState  @State Varchar(20),@Extention_No varchar(10),@Phone_No Varchar(15),@Unique_Id Varchar(20),@Agent_Name Varchar(30),@Pid varchar(10),@Campaign_Id varchar(10)  AS BEGIN  DECLARE @sqlQuery varchar(MAX);
 SET @sqlQuery = NULL; 
IF(@State = 'Login') 
		BEGIN 
			SET  @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time)'
            + 'Values(GETDATE(),'''+ @Extention_No +''','''+ @Agent_Name +''',''Login'',''00:00:00'',NULL,NULL,'+ @Pid +','+ @Campaign_Id +',GETDATE());'
 + 'UPDATE Admin_Extention_Master SET CRM_State = ''Break'',State_Time = GETDATE() WHERE Extention_No = '''+ @Extention_No +''''; 
END 
ELSE IF (@State = 'Logout' and @Agent_Name IS NOT NULL)
 BEGIN 
SET @sqlQuery = 'INSERT INTO AMS_Log(Log_Date,Extention_No,Agent_Name,State,Duration,Caller_Id,Called_No,Pid,Campaign_Id,End_Time)' 
+ '(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() 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)' + 'Values(GETDATE(),'''+ @Extention_No +''','''+ @Agent_Name +''',''Logout'',''00:00:00'',NULL,NULL,'+ @Pid + ','+ @Campaign_Id + ',GETDATE())'; 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)'
 + '(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() FROM Admin_Extention_Master WHERE  Extention_No = '+ @Extention_No +');'
 + 'UPDATE Admin_Extention_Master SET Caller_Id = '''',CRM_State = ''Idle'',State_Time = GETDATE() WHERE Extention_No = '''+ @Extention_No +''''; 
END 
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)'
+ '(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() FROM Admin_Extention_Master WHERE  Extention_No = '''+ @Extention_No + ''');' + 'UPDATE Admin_Extention_Master SET Called_No = '''+ @Phone_No +''',Dial_Time = NULL,CRM_State = ''' + @State + ''',State_Time = GETDATE(),'; IF(@State = 'Idle') SET @sqlQuery = @sqlQuery + 'Caller_Id = NULL,Unique_Id = NULL,'; ELSE BEGIN IF(@Phone_No <> '' AND @Phone_No IS NOT NULL) SET @sqlQuery = @sqlQuery + 'Caller_Id = NULL,'; IF(@Unique_Id <> '' and @Unique_Id IS NOT NULL) SET @sqlQuery = @sqlQuery + 'Unique_Id = '''+ @Unique_Id + ''','; END SET @sqlQuery = @sqlQuery + 'Call_Connected = 0,Call_Count = 0 WHERE Extention_No = '''+ @Extention_No +''''; 
END 
IF (@sqlQuery IS NOT NULL) 
   EXEC (@sqlQuery);             
END
