10:40:48  @@ 1, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
10:40:50  @@ 2, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='1'
10:41:06  @@ 3, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
10:41:07  @@ 4, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='admin'
10:41:07  @@ 5, ,select isqueuewiseprefix from company_setup with(nolock)
10:41:07  @@ 6, ,select ticket_prefix from queue_master with(nolock) where queue_id=8
10:41:09  @@ 7, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Open'
10:41:11  @@ 8, ,INSERT INTO Ticket_Master(Happy_Code,subject,ticket_prefix,start_mode,start_date,customer_name,unique_id,phone_no,queue_id,category_id, subcategory_id,[from],[to],cc,bcc,priority_id,status_id,assigned_to,isassigned,assigned_by,assigned_on,ticket_created_by)values( '1894','sub.','TT','On Call',GETDATE(),'ashish', NULL,'9026774169','8', '1',0,'','test@sansoftwares.com',NULL,NULL,NULL,  '3',NULL,NULL,'1',GETDATE(),'1');SELECT SCOPE_IDENTITY()
10:41:13  @@ 9, ,UPDATE Ticket_Master SET ticket_id='[#TT200715-9144]',subject='sub. Ticket Id :- [#TT200715-9144]' where ticket_unique_id=9144
10:41:13  @@ 10, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'On Call','9144','[#TT200715-9144]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub.',null, '1',NULL, '3',NULL, '8','1','none','none',0,0,'abbe3d479e4c44eda3a88303c3e4e9f4');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9144','1','New Ticket','New ticket generated On call' 
10:41:15  @@ 11, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'Auto Reply Email','9144','[#TT200715-9144]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub. Ticket Id :- [#TT200715-9144]',N'', '1',NULL, '3',NULL, '8','1','NEW','',0,0,'8a4f8bae6faa4f0199502fc274b776c2');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9144','1','Manual Reply','Auto Reply Manual' 
10:41:16  @@ 12, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
10:41:17  @@ 13, ,SELECT TOP 1 SMS_API FROM QUEUE_MASTER where queue_id='8'
10:42:47  @@ 14, ,select top 1 isnull(IsManual_Assign,0)IsManual_Assign from company_setup 
10:42:47  @@ 15, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
10:42:47  @@ 16, , BEGIN TRANSACTION [Tran1]  BEGIN TRY  IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP  IF OBJECT_ID('tempdb..#ConvTemp') IS NOT NULL DROP TABLE #ConvTemp  SELECT ROW_NUMBER() OVER(PARTITION BY ticket_unique_id ORDER BY conversation_id) ROW,conversation_id,email_message_id  ,[subject],ticket_unique_id INTO #ConvTemp  FROM conversation_master a WITH(NOLOCK,INDEX(conversation_master_ticket_unique_id)) JOIN[dbo].[Split] ('9144',',') F ON a.ticket_unique_id = F.Data  DELETE FROM #ConvTemp WHERE ROW !=1  DECLARE @temp as table(conversation_id int)  UPDATE A SET assigned_to=1,isassigned=1,assigned_by=0 FROM TICKET_MASTER A JOIN [dbo].[Split]('9144',',') F ON a.ticket_unique_id = F.Data  SELECT b.category_id,b.subcategory_id,GETDATE() date,B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,customer_name,b.[from],  ISNULL(e.IsHappyCode,0)IsHappyCode,b.Happy_Code,b.[to],b.CC,c.subject,B.status_id,B.priority_id,B.queue_id,B.phone_no,assigned_to,c.email_message_id Pre_email_message_id INTO #TEMP  FROM Ticket_Master B WITH(NOLOCK)  JOIN #ConvTemp c ON c.ticket_unique_id=b.ticket_unique_id JOIN queue_master E WITH(NOLOCK) ON E.queue_id = B.queue_id  JOIN emailaccount_master G WITH(NOLOCK) ON G.emailaccount_id = E.Send_Account_Id INSERT INTO Conversation_Master(category_id,subcategory_id,SMS_max_try,max_try,Conversation_Date,Conversation_Type,ticket_unique_id,Ticket_Id,Account_Id,[From],[To],Cc,Subject,Status_Id,Priority_Id,Queue_Id,Login_Id,Email_Status,No_Of_Try,email_message_id,sms_conversation,sms_status,SMS_no_of_try,phone_no) OUTPUT Inserted.conversation_id  INTO @temp  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Customer',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,B.[from]+ CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,''))=0 THEN '' ELSE ','+REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,'') END ,B.CC,REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('##subject## Ticket Id :- ##ticketid##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''), NULL  ,'none' ,0,B.phone_no FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id  UNION ALL  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Agent',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,c.email  ,'',REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Ticket Id :- ##ticketid##  is Assigned','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''),REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Hi ##Agent_Name##,

Ticket Id :- ##ticketid## is assigned to you.

##remarks##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')) ,'NEW' ,0,c.phoneno FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id WHERE ISNULL(c.email,'')!='';  insert into AssignLogs(TicketId,AssignedBy,AssignedTo,LastAssigned,AssignedOn)  SELECT ticket_unique_id , 0,1,assigned_to,Getdate() FROM #TEMP ; SELECT a.subject,a.cc,a.[to],a.[from],a.email_message_id,a.conversation_type,  c.Pre_email_message_id,c.ticket_id,c.phone_no,d.name,c.customer_name,d.signature,c.ticket_unique_id  ,c.cc Pri_cc,c.[from] Pri_from,c.[to] Pri_to,c.Happy_Code,ISNULL(c.IsHappyCode,0)IsHappyCode,c.assigned_to,c.email_id  FROM conversation_master a JOIN @temp b ON a.conversation_id=b.conversation_id  JOIN #TEMP c ON c.ticket_unique_id=a.ticket_unique_id  JOIN User_Master d WITH(NOLOCK) ON c.assigned_to=d.user_id  COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 
10:42:47  @@ 17, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9144')  BEGIN  UPDATE UnreadConversationCount SET UserId='1' WHERE ticket_unique_id='9144' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('1','9144','[#TT200715-9144]',GETDATE()  ,'sub. Ticket Id :- [#TT200715-9144]','New')
10:42:47  @@ 18, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9144')  BEGIN  UPDATE UnreadConversationCount SET UserId='1' WHERE ticket_unique_id='9144' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('1','9144','[#TT200715-9144]',GETDATE()  ,'Ticket Id :- [#TT200715-9144]  is Assigned','New')
02:58:25  @@ 1, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
02:58:26  @@ 2, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='ashish'
02:58:26  @@ 3, ,select isqueuewiseprefix from company_setup with(nolock)
02:58:26  @@ 4, ,select ticket_prefix from queue_master with(nolock) where queue_id=8
02:58:26  @@ 5, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Open'
02:58:27  @@ 6, ,INSERT INTO Ticket_Master(Happy_Code,subject,ticket_prefix,start_mode,start_date,customer_name,unique_id,phone_no,queue_id,category_id, subcategory_id,[from],[to],cc,bcc,priority_id,status_id,assigned_to,isassigned,assigned_by,assigned_on,ticket_created_by)values( '7193','sub.','TT','On Call',GETDATE(),'ashish', NULL,'9026774169','8', '1',0,'','test@sansoftwares.com',NULL,NULL,NULL,  '3',NULL,NULL,'42',GETDATE(),'42');SELECT SCOPE_IDENTITY()
02:58:29  @@ 7, ,UPDATE Ticket_Master SET ticket_id='[#TT200715-9145]',subject='sub. Ticket Id :- [#TT200715-9145]' where ticket_unique_id=9145
02:58:29  @@ 8, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'On Call','9145','[#TT200715-9145]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub.',null, '1',NULL, '3',NULL, '8','42','none','none',0,0,'8bda221debe146309cda540c7b3afd15');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9145','42','New Ticket','New ticket generated On call' 
02:58:53  @@ 9, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'Auto Reply Email','9145','[#TT200715-9145]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub. Ticket Id :- [#TT200715-9145]',N'', '1',NULL, '3',NULL, '8','42','NEW','',0,0,'9c14162ce44449dcb01e08baa0aa0f33');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9145','42','Manual Reply','Auto Reply Manual' 
02:58:54  @@ 10, ,SELECT TOP 1 sms_template_body_user FROM template_master WHERE template_name='ManagerAlert'
02:59:09  @@ 11, ,select top 1 isnull(IsManual_Assign,0)IsManual_Assign from company_setup 
02:59:09  @@ 12, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
02:59:51  @@ 13, , BEGIN TRANSACTION [Tran1]  BEGIN TRY  IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP  IF OBJECT_ID('tempdb..#ConvTemp') IS NOT NULL DROP TABLE #ConvTemp  SELECT ROW_NUMBER() OVER(PARTITION BY ticket_unique_id ORDER BY conversation_id) ROW,conversation_id,email_message_id  ,[subject],ticket_unique_id INTO #ConvTemp  FROM conversation_master a WITH(NOLOCK,INDEX(conversation_master_ticket_unique_id)) JOIN[dbo].[Split] ('9145',',') F ON a.ticket_unique_id = F.Data  DELETE FROM #ConvTemp WHERE ROW !=1  DECLARE @temp as table(conversation_id int)  UPDATE A SET assigned_to=42,isassigned=1,assigned_by=0 FROM TICKET_MASTER A JOIN [dbo].[Split]('9145',',') F ON a.ticket_unique_id = F.Data  SELECT b.category_id,b.subcategory_id,GETDATE() date,B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,customer_name,b.[from],  ISNULL(e.IsHappyCode,0)IsHappyCode,b.Happy_Code,b.[to],b.CC,c.subject,B.status_id,B.priority_id,B.queue_id,B.phone_no,assigned_to,c.email_message_id Pre_email_message_id INTO #TEMP  FROM Ticket_Master B WITH(NOLOCK)  JOIN #ConvTemp c ON c.ticket_unique_id=b.ticket_unique_id JOIN queue_master E WITH(NOLOCK) ON E.queue_id = B.queue_id  JOIN emailaccount_master G WITH(NOLOCK) ON G.emailaccount_id = E.Send_Account_Id INSERT INTO Conversation_Master(category_id,subcategory_id,SMS_max_try,max_try,Conversation_Date,Conversation_Type,ticket_unique_id,Ticket_Id,Account_Id,[From],[To],Cc,Subject,Status_Id,Priority_Id,Queue_Id,Login_Id,Email_Status,No_Of_Try,email_message_id,sms_conversation,sms_status,SMS_no_of_try,phone_no) OUTPUT Inserted.conversation_id  INTO @temp  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Customer',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,B.[from]+ CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,''))=0 THEN '' ELSE ','+REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,'') END ,B.CC,REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('##subject## Ticket Id :- ##ticketid##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''), NULL  ,'none' ,0,B.phone_no FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id  UNION ALL  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Agent',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,c.email  ,'',REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Ticket Id :- ##ticketid##  is Assigned','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''),REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Hi ##Agent_Name##,

Ticket Id :- ##ticketid## is assigned to you.

##remarks##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')) ,'NEW' ,0,c.phoneno FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id WHERE ISNULL(c.email,'')!='';  insert into AssignLogs(TicketId,AssignedBy,AssignedTo,LastAssigned,AssignedOn)  SELECT ticket_unique_id , 0,42,assigned_to,Getdate() FROM #TEMP ; SELECT a.subject,a.cc,a.[to],a.[from],a.email_message_id,a.conversation_type,  c.Pre_email_message_id,c.ticket_id,c.phone_no,d.name,c.customer_name,d.signature,c.ticket_unique_id  ,c.cc Pri_cc,c.[from] Pri_from,c.[to] Pri_to,c.Happy_Code,ISNULL(c.IsHappyCode,0)IsHappyCode,c.assigned_to,c.email_id  FROM conversation_master a JOIN @temp b ON a.conversation_id=b.conversation_id  JOIN #TEMP c ON c.ticket_unique_id=a.ticket_unique_id  JOIN User_Master d WITH(NOLOCK) ON c.assigned_to=d.user_id  COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 
03:00:00  @@ 14, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9145')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9145' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9145','[#TT200715-9145]',GETDATE()  ,'sub. Ticket Id :- [#TT200715-9145]','New')
03:00:05  @@ 15, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9145')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9145' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9145','[#TT200715-9145]',GETDATE()  ,'Ticket Id :- [#TT200715-9145]  is Assigned','New')
04:11:33  @@ 35, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
04:11:33  @@ 36, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='ashish'
04:11:33  @@ 37, ,select isqueuewiseprefix from company_setup with(nolock)
04:11:33  @@ 38, ,select ticket_prefix from queue_master with(nolock) where queue_id=8
04:11:33  @@ 39, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Open'
04:11:33  @@ 40, ,INSERT INTO Ticket_Master(Happy_Code,subject,ticket_prefix,start_mode,start_date,customer_name,unique_id,phone_no,queue_id,category_id, subcategory_id,[from],[to],cc,bcc,priority_id,status_id,assigned_to,isassigned,assigned_by,assigned_on,ticket_created_by)values( '3598','sub.','TT','On Call',GETDATE(),'ashish', NULL,'9026774169','8', '1',0,'','test@sansoftwares.com',NULL,NULL,NULL,  '3',NULL,NULL,'42',GETDATE(),'42');SELECT SCOPE_IDENTITY()
04:11:33  @@ 41, ,UPDATE Ticket_Master SET ticket_id='[#TT200715-9146]',subject='sub. Ticket Id :- [#TT200715-9146]' where ticket_unique_id=9146
04:11:33  @@ 42, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'On Call','9146','[#TT200715-9146]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub.',null, '1',NULL, '3',NULL, '8','42','none','none',0,0,'46431e88a5e947d08cf69e5ff31d16df');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9146','42','New Ticket','New ticket generated On call' 
04:11:33  @@ 43, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'Auto Reply Email','9146','[#TT200715-9146]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub. Ticket Id :- [#TT200715-9146]',N'', '1',NULL, '3',NULL, '8','42','NEW','',0,0,'b4be93fa87604384ab4a7974c2dc85c7');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9146','42','Manual Reply','Auto Reply Manual' 
04:11:33  @@ 44, ,SELECT TOP 1 sms_template_body_user FROM template_master WHERE template_name='ManagerAlert'
04:11:33  @@ 45, ,select top 1 isnull(IsManual_Assign,0)IsManual_Assign from company_setup 
04:11:33  @@ 46, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
04:11:33  @@ 47, , BEGIN TRANSACTION [Tran1]  BEGIN TRY  IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP  IF OBJECT_ID('tempdb..#ConvTemp') IS NOT NULL DROP TABLE #ConvTemp  SELECT ROW_NUMBER() OVER(PARTITION BY ticket_unique_id ORDER BY conversation_id) ROW,conversation_id,email_message_id  ,[subject],ticket_unique_id INTO #ConvTemp  FROM conversation_master a WITH(NOLOCK,INDEX(conversation_master_ticket_unique_id)) JOIN[dbo].[Split] ('9146',',') F ON a.ticket_unique_id = F.Data  DELETE FROM #ConvTemp WHERE ROW !=1  DECLARE @temp as table(conversation_id int)  UPDATE A SET assigned_to=42,isassigned=1,assigned_by=0 FROM TICKET_MASTER A JOIN [dbo].[Split]('9146',',') F ON a.ticket_unique_id = F.Data  SELECT b.category_id,b.subcategory_id,GETDATE() date,B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,customer_name,b.[from],  ISNULL(e.IsHappyCode,0)IsHappyCode,b.Happy_Code,b.[to],b.CC,c.subject,B.status_id,B.priority_id,B.queue_id,B.phone_no,assigned_to,c.email_message_id Pre_email_message_id INTO #TEMP  FROM Ticket_Master B WITH(NOLOCK)  JOIN #ConvTemp c ON c.ticket_unique_id=b.ticket_unique_id JOIN queue_master E WITH(NOLOCK) ON E.queue_id = B.queue_id  JOIN emailaccount_master G WITH(NOLOCK) ON G.emailaccount_id = E.Send_Account_Id INSERT INTO Conversation_Master(category_id,subcategory_id,SMS_max_try,max_try,Conversation_Date,Conversation_Type,ticket_unique_id,Ticket_Id,Account_Id,[From],[To],Cc,Subject,Status_Id,Priority_Id,Queue_Id,Login_Id,Email_Status,No_Of_Try,email_message_id,sms_conversation,sms_status,SMS_no_of_try,phone_no) OUTPUT Inserted.conversation_id  INTO @temp  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Customer',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,B.[from]+ CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,''))=0 THEN '' ELSE ','+REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,'') END ,B.CC,REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('##subject## Ticket Id :- ##ticketid##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''), NULL  ,'none' ,0,B.phone_no FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id  UNION ALL  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Agent',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,c.email  ,'',REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Ticket Id :- ##ticketid##  is Assigned','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''),REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Hi ##Agent_Name##,

Ticket Id :- ##ticketid## is assigned to you.

##remarks##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')) ,'NEW' ,0,c.phoneno FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id WHERE ISNULL(c.email,'')!='';  insert into AssignLogs(TicketId,AssignedBy,AssignedTo,LastAssigned,AssignedOn)  SELECT ticket_unique_id , 0,42,assigned_to,Getdate() FROM #TEMP ; SELECT a.subject,a.cc,a.[to],a.[from],a.email_message_id,a.conversation_type,  c.Pre_email_message_id,c.ticket_id,c.phone_no,d.name,c.customer_name,d.signature,c.ticket_unique_id  ,c.cc Pri_cc,c.[from] Pri_from,c.[to] Pri_to,c.Happy_Code,ISNULL(c.IsHappyCode,0)IsHappyCode,c.assigned_to,c.email_id  FROM conversation_master a JOIN @temp b ON a.conversation_id=b.conversation_id  JOIN #TEMP c ON c.ticket_unique_id=a.ticket_unique_id  JOIN User_Master d WITH(NOLOCK) ON c.assigned_to=d.user_id  COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 
04:11:34  @@ 48, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9146')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9146' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9146','[#TT200715-9146]',GETDATE()  ,'sub. Ticket Id :- [#TT200715-9146]','New')
04:11:34  @@ 49, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9146')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9146' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9146','[#TT200715-9146]',GETDATE()  ,'Ticket Id :- [#TT200715-9146]  is Assigned','New')
04:13:27  @@ 57, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
04:13:27  @@ 58, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='ashish'
04:13:27  @@ 59, ,select isqueuewiseprefix from company_setup with(nolock)
04:13:27  @@ 60, ,select ticket_prefix from queue_master with(nolock) where queue_id=8
04:13:27  @@ 61, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Open'
04:13:27  @@ 62, ,INSERT INTO Ticket_Master(Happy_Code,subject,ticket_prefix,start_mode,start_date,customer_name,unique_id,phone_no,queue_id,category_id, subcategory_id,[from],[to],cc,bcc,priority_id,status_id,assigned_to,isassigned,assigned_by,assigned_on,ticket_created_by)values( '3400','sub.','TT','On Call',GETDATE(),'ashish', NULL,'9026774169','8', '1',0,'','test@sansoftwares.com',NULL,NULL,NULL,  '3',NULL,NULL,'42',GETDATE(),'42');SELECT SCOPE_IDENTITY()
04:13:27  @@ 63, ,UPDATE Ticket_Master SET ticket_id='[#TT200715-9147]',subject='sub. Ticket Id :- [#TT200715-9147]' where ticket_unique_id=9147
04:13:27  @@ 64, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'On Call','9147','[#TT200715-9147]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub.',null, '1',NULL, '3',NULL, '8','42','none','none',0,0,'389e7d2ffa6f48a5a6d821344d307f86');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9147','42','New Ticket','New ticket generated On call' 
04:13:27  @@ 65, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'Auto Reply Email','9147','[#TT200715-9147]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub. Ticket Id :- [#TT200715-9147]',N'', '1',NULL, '3',NULL, '8','42','NEW','',0,0,'aa121ba0497b45d899f5ee48da6171f5');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9147','42','Manual Reply','Auto Reply Manual' 
04:13:27  @@ 66, ,SELECT TOP 1 sms_template_body_user FROM template_master WHERE template_name='ManagerAlert'
04:13:27  @@ 67, ,select top 1 isnull(IsManual_Assign,0)IsManual_Assign from company_setup 
04:13:27  @@ 68, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
04:13:27  @@ 69, , BEGIN TRANSACTION [Tran1]  BEGIN TRY  IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP  IF OBJECT_ID('tempdb..#ConvTemp') IS NOT NULL DROP TABLE #ConvTemp  SELECT ROW_NUMBER() OVER(PARTITION BY ticket_unique_id ORDER BY conversation_id) ROW,conversation_id,email_message_id  ,[subject],ticket_unique_id INTO #ConvTemp  FROM conversation_master a WITH(NOLOCK,INDEX(conversation_master_ticket_unique_id)) JOIN[dbo].[Split] ('9147',',') F ON a.ticket_unique_id = F.Data  DELETE FROM #ConvTemp WHERE ROW !=1  DECLARE @temp as table(conversation_id int)  UPDATE A SET assigned_to=42,isassigned=1,assigned_by=0 FROM TICKET_MASTER A JOIN [dbo].[Split]('9147',',') F ON a.ticket_unique_id = F.Data  SELECT b.category_id,b.subcategory_id,GETDATE() date,B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,customer_name,b.[from],  ISNULL(e.IsHappyCode,0)IsHappyCode,b.Happy_Code,b.[to],b.CC,c.subject,B.status_id,B.priority_id,B.queue_id,B.phone_no,assigned_to,c.email_message_id Pre_email_message_id INTO #TEMP  FROM Ticket_Master B WITH(NOLOCK)  JOIN #ConvTemp c ON c.ticket_unique_id=b.ticket_unique_id JOIN queue_master E WITH(NOLOCK) ON E.queue_id = B.queue_id  JOIN emailaccount_master G WITH(NOLOCK) ON G.emailaccount_id = E.Send_Account_Id INSERT INTO Conversation_Master(category_id,subcategory_id,SMS_max_try,max_try,Conversation_Date,Conversation_Type,ticket_unique_id,Ticket_Id,Account_Id,[From],[To],Cc,Subject,Status_Id,Priority_Id,Queue_Id,Login_Id,Email_Status,No_Of_Try,email_message_id,sms_conversation,sms_status,SMS_no_of_try,phone_no) OUTPUT Inserted.conversation_id  INTO @temp  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Customer',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,B.[from]+ CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,''))=0 THEN '' ELSE ','+REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,'') END ,B.CC,REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('##subject## Ticket Id :- ##ticketid##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''), NULL  ,'none' ,0,B.phone_no FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id  UNION ALL  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Agent',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,c.email  ,'',REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Ticket Id :- ##ticketid##  is Assigned','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''),REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Hi ##Agent_Name##,

Ticket Id :- ##ticketid## is assigned to you.

##remarks##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')) ,'NEW' ,0,c.phoneno FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id WHERE ISNULL(c.email,'')!='';  insert into AssignLogs(TicketId,AssignedBy,AssignedTo,LastAssigned,AssignedOn)  SELECT ticket_unique_id , 0,42,assigned_to,Getdate() FROM #TEMP ; SELECT a.subject,a.cc,a.[to],a.[from],a.email_message_id,a.conversation_type,  c.Pre_email_message_id,c.ticket_id,c.phone_no,d.name,c.customer_name,d.signature,c.ticket_unique_id  ,c.cc Pri_cc,c.[from] Pri_from,c.[to] Pri_to,c.Happy_Code,ISNULL(c.IsHappyCode,0)IsHappyCode,c.assigned_to,c.email_id  FROM conversation_master a JOIN @temp b ON a.conversation_id=b.conversation_id  JOIN #TEMP c ON c.ticket_unique_id=a.ticket_unique_id  JOIN User_Master d WITH(NOLOCK) ON c.assigned_to=d.user_id  COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 
04:13:27  @@ 70, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9147')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9147' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9147','[#TT200715-9147]',GETDATE()  ,'sub. Ticket Id :- [#TT200715-9147]','New')
04:13:27  @@ 71, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9147')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9147' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9147','[#TT200715-9147]',GETDATE()  ,'Ticket Id :- [#TT200715-9147]  is Assigned','New')
04:15:25  @@ 72, , SELECT TOP 1 (select top 1 status_id from status_master where status='Open')Status_id,a.queue_id,emailaccount_id,email_id,(SELECT TOP 1 category_id FROM category_master WHERE NAME='Support')Category  from queue_master a JOIN emailaccount_master b ON a.default_account_id=b.emailaccount_id where a.name='TEST' AND a.isactive=1  
04:15:25  @@ 73, ,SELECT TOP 1 phoneno,User_id FROM USER_MASTER WHERE USER_Name='ashish'
04:15:25  @@ 74, ,select isqueuewiseprefix from company_setup with(nolock)
04:15:25  @@ 75, ,select ticket_prefix from queue_master with(nolock) where queue_id=8
04:15:25  @@ 76, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Open'
04:15:25  @@ 77, ,INSERT INTO Ticket_Master(Happy_Code,subject,ticket_prefix,start_mode,start_date,customer_name,unique_id,phone_no,queue_id,category_id, subcategory_id,[from],[to],cc,bcc,priority_id,status_id,assigned_to,isassigned,assigned_by,assigned_on,ticket_created_by)values( '1198','sub.','TT','On Call',GETDATE(),'ashish', NULL,'9026774169','8', '1',0,'','test@sansoftwares.com',NULL,NULL,NULL,  '3',NULL,NULL,'42',GETDATE(),'42');SELECT SCOPE_IDENTITY()
04:15:25  @@ 78, ,UPDATE Ticket_Master SET ticket_id='[#TT200715-9148]',subject='sub. Ticket Id :- [#TT200715-9148]' where ticket_unique_id=9148
04:15:25  @@ 79, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'On Call','9148','[#TT200715-9148]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub.',null, '1',NULL, '3',NULL, '8','42','none','none',0,0,'15f37aa4ac034ba0bb16af8833a930bc');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9148','42','New Ticket','New ticket generated On call' 
04:15:25  @@ 80, ,INSERT INTO conversation_master(conversation_date,conversation_type,ticket_unique_id,ticket_id,phone_no,account_id,[from],[to],cc,bcc,subject,sms_conversation,category_id,subcategory_id,status_id,priority_id,queue_id,login_id,email_status,sms_status,no_of_try,max_try,email_message_id) values(GETDATE(),'Auto Reply Email','9148','[#TT200715-9148]','9026774169', '5','test@sansoftwares.com','',NULL, NULL,N'sub. Ticket Id :- [#TT200715-9148]',N'', '1',NULL, '3',NULL, '8','42','NEW','',0,0,'d83196ba1aa44d4db7da87a2c5d13aba');select SCOPE_IDENTITY(); EXEC DBO.USP_INS_TICKET_LOG '9148','42','Manual Reply','Auto Reply Manual' 
04:15:25  @@ 81, ,SELECT TOP 1 sms_template_body_user FROM template_master WHERE template_name='ManagerAlert'
04:15:27  @@ 82, ,select top 1 isnull(IsManual_Assign,0)IsManual_Assign from company_setup 
04:15:27  @@ 83, ,SELECT top 1 CASE WHEN IsCustomerEmail = 1 THEN ISNULL(email_subject,'') ELSE '' END email_subject,
                                    CASE WHEN IsCustomerEmail = 1 THEN ISNULL(template_body,'') ELSE '' END template_body,
                                    CASE WHEN IsCustomerSMS = 1 THEN ISNULL(sms_template_body,'') ELSE '' END sms_template_body,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(email_subject_user,'') ELSE '' END email_subject_user,
                                    CASE WHEN IsUserEmail = 1 THEN ISNULL(template_body_user,'') ELSE '' END template_body_user,
                                    CASE WHEN IsUserSMS = 1 THEN ISNULL(sms_template_body_user,'') ELSE '' END sms_template_body_user
                                    FROM Queue_Template_Grid a JOIN template_master b ON a.Template_Id = b.template_id WHERE Queue_Id = 8 AND Actions = 'Manual Assign'
04:17:17  @@ 84, , BEGIN TRANSACTION [Tran1]  BEGIN TRY  IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP  IF OBJECT_ID('tempdb..#ConvTemp') IS NOT NULL DROP TABLE #ConvTemp  SELECT ROW_NUMBER() OVER(PARTITION BY ticket_unique_id ORDER BY conversation_id) ROW,conversation_id,email_message_id  ,[subject],ticket_unique_id INTO #ConvTemp  FROM conversation_master a WITH(NOLOCK,INDEX(conversation_master_ticket_unique_id)) JOIN[dbo].[Split] ('9148',',') F ON a.ticket_unique_id = F.Data  DELETE FROM #ConvTemp WHERE ROW !=1  DECLARE @temp as table(conversation_id int)  UPDATE A SET assigned_to=42,isassigned=1,assigned_by=0 FROM TICKET_MASTER A JOIN [dbo].[Split]('9148',',') F ON a.ticket_unique_id = F.Data  SELECT b.category_id,b.subcategory_id,GETDATE() date,B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,customer_name,b.[from],  ISNULL(e.IsHappyCode,0)IsHappyCode,b.Happy_Code,b.[to],b.CC,c.subject,B.status_id,B.priority_id,B.queue_id,B.phone_no,assigned_to,c.email_message_id Pre_email_message_id INTO #TEMP  FROM Ticket_Master B WITH(NOLOCK)  JOIN #ConvTemp c ON c.ticket_unique_id=b.ticket_unique_id JOIN queue_master E WITH(NOLOCK) ON E.queue_id = B.queue_id  JOIN emailaccount_master G WITH(NOLOCK) ON G.emailaccount_id = E.Send_Account_Id INSERT INTO Conversation_Master(category_id,subcategory_id,SMS_max_try,max_try,Conversation_Date,Conversation_Type,ticket_unique_id,Ticket_Id,Account_Id,[From],[To],Cc,Subject,Status_Id,Priority_Id,Queue_Id,Login_Id,Email_Status,No_Of_Try,email_message_id,sms_conversation,sms_status,SMS_no_of_try,phone_no) OUTPUT Inserted.conversation_id  INTO @temp  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Customer',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,B.[from]+ CASE WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,''))=0 THEN '' ELSE ','+REPLACE(REPLACE(REPLACE(REPLACE(b.[to],','+email_id,''),email_id+',',''),','+email_id+',',''),email_id,'') END ,B.CC,REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('##subject## Ticket Id :- ##ticketid##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''), NULL  ,'none' ,0,B.phone_no FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id  UNION ALL  SELECT category_id,subcategory_id,0,0,GETDATE(),'Manual Assign Agent',B.ticket_unique_id,B.ticket_id,Send_Account_Id,email_id,c.email  ,'',REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Ticket Id :- ##ticketid##  is Assigned','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')),B.status_id,B.priority_id,B.queue_id,0,'NEW',0,REPLACE(NEWID(),'-',''),REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('Hi ##Agent_Name##,

Ticket Id :- ##ticketid## is assigned to you.

##remarks##','##ticketid##',B.ticket_id) ,'##subject##',b.subject) ,'##Mobile##',ISNULL(B.phone_no,'')) ,'##Agent_Name##',ISNULL(c.name,'')) ,'##CustomerName##',ISNULL(b.customer_name,'')) ,'NEW' ,0,c.phoneno FROM #TEMP B  JOIN User_Master c WITH(NOLOCK) ON b.assigned_to=c.user_id WHERE ISNULL(c.email,'')!='';  insert into AssignLogs(TicketId,AssignedBy,AssignedTo,LastAssigned,AssignedOn)  SELECT ticket_unique_id , 0,42,assigned_to,Getdate() FROM #TEMP ; SELECT a.subject,a.cc,a.[to],a.[from],a.email_message_id,a.conversation_type,  c.Pre_email_message_id,c.ticket_id,c.phone_no,d.name,c.customer_name,d.signature,c.ticket_unique_id  ,c.cc Pri_cc,c.[from] Pri_from,c.[to] Pri_to,c.Happy_Code,ISNULL(c.IsHappyCode,0)IsHappyCode,c.assigned_to,c.email_id  FROM conversation_master a JOIN @temp b ON a.conversation_id=b.conversation_id  JOIN #TEMP c ON c.ticket_unique_id=a.ticket_unique_id  JOIN User_Master d WITH(NOLOCK) ON c.assigned_to=d.user_id  COMMIT TRANSACTION [Tran1]  END TRY  BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 
04:17:18  @@ 85, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9148')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9148' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9148','[#TT200715-9148]',GETDATE()  ,'sub. Ticket Id :- [#TT200715-9148]','New')
04:17:18  @@ 86, ,IF EXISTS(SELECT TOP 1 1 FROM UnreadConversationCount WHERE ticket_unique_id='9148')  BEGIN  UPDATE UnreadConversationCount SET UserId='42' WHERE ticket_unique_id='9148' END ELSE INSERT INTO UnreadConversationCount (UserId,ticket_unique_id,ticket_id,Conversation_date,Subject,ConversationType)  VALUES ('42','9148','[#TT200715-9148]',GETDATE()  ,'Ticket Id :- [#TT200715-9148]  is Assigned','New')
