CRT_PRO|AgentlogReport|
(IN `user_id` TEXT,IN `queue_id` VARCHAR(50),IN `sqlcondition` TEXT,IN `limit_offset` VARCHAR(10))
BEGIN
set  @a:=0;

SET @sqlquery=CONCAT ('select @a:=@a+1 as SrNo, 
ttm.ticket_unique_id AS ticket_id ,ttm.ticket_id AS ticket,ttm.Person_name,ttm.mobile_no,ctm.title AS MODE,
tms_queue_master.queue as Queue,tqp.priority_name as Priority,tqp.colour_code as PriorityColour,
tqs.status_name as Status,tqs.colour_code as StatusColour,ttm.created_date as created_date ,ttm.last_updated_on as last_updated_on,ifnull(tumby.user_name,"Auto") as assigned_by
,concat(ttm.created_date ,''\n'', ttm.last_updated_on) as date_update,ttm.subject as subject
/*, Case when IFNULL(tqs.is_close_status, "0") = 1 and TIMESTAMPDIFF(hour,ttm.created_date,conversation_date)<=ifnull(tqe.hours,10) then ''Yes'' else ''No'' end as IsClosedInSLA ,      
ttm.created_date as TicketStartDate,
Case when IFNULL(tqs.is_close_status, "0") = 1 
 then conversation_date else now() end as ClsT_or_CUT,
concat(round(Case when IFNULL(tqs.is_close_status, "0") = 1 
then TIMESTAMPDIFF(second,ttm.created_date,tcm.conversation_date) 
else TIMESTAMPDIFF(second,ttm.created_date,now()) end/ 86400,0)
,''d '',
sec_to_time(Case when IFNULL(tqs.is_close_status, "0") = 1 
then TIMESTAMPDIFF(second,ttm.created_date,tcm.conversation_date) 
else TIMESTAMPDIFF(second,ttm.created_date,now()) end % 86400)) AS TAT   */          
,tms_queue_category_',queue_id,'.category_name as Category,tms_queue_sub_category_',queue_id,'.sub_category_name as Subcategory,
 tum.user_name as AssignedTo,tum.user_name as ReadBy
from tms_ticket_master_',queue_id,' ttm
left Join tms_user_master tum on tum.user_id=ttm.assigned_to
left Join tms_user_master tumby on tumby.user_id=ttm.assigned_by

/*JOIN (SELECT user_id FROM tms_user_master WHERE user_id=',user_id,' UNION ALL SELECT user_id FROM tms_user_master WHERE manager_id=',user_id,' UNION ALL SELECT 0) hie on hie.user_id=tum.user_id*/

left join tms_queue_priority_',queue_id,' tqp on tqp.priority_id=ttm.priority_id
left JOIN (SELECT MAX(conversation_id) AS Last_conversation_id, ticket_unique_id FROM tms_conversation_master_',queue_id,'  GROUP BY ticket_unique_id) AS Last_con ON Last_con.ticket_unique_id = ttm.ticket_unique_id 
left join tms_conversation_master_',queue_id,' tcm on tcm.conversation_id = Last_con.Last_conversation_id 	   
left JOIN tms_queue_escalationmatrix_',queue_id,' tqe ON tqe.priority_id =ifnull(ttm.priority_id,3) AND (TIMESTAMPDIFF(SECOND, ttm.created_date, tcm.conversation_date) / 3600) = hours AND ((TIMESTAMPDIFF(SECOND, ttm.created_date, tcm.conversation_date) % 3600) / 60) >= minute   
left Join tms_queue_master on ttm.queue_id = tms_queue_master.id
left join tms_conversation_type_master ctm on ctm.id=ttm.Start_mode
left join tms_queue_category_',queue_id,' on ttm.category_id = tms_queue_category_',queue_id,'.category_id
left join tms_queue_sub_category_',queue_id,' on ttm.subcategory_id = tms_queue_sub_category_',queue_id,'.sub_category_id
left join tms_queue_status_',queue_id,' tqs on tqs.status_id=ttm.status_id
where 1=1 AND IFNULL(is_junk,0)=0 AND IFNULL(is_deleted_ticket,0)=0  ',
 if( IFNULL(user_id,"")!="",CONCAT(' and tum.user_id in (',user_id,')'),""),'
');


if sqlcondition!= '' then
	 set @sqlquery=CONCAT(@sqlquery,sqlcondition);
END if;

if limit_offset !='' then
	SET @sqlquery = CONCAT(@sqlquery,' LIMIT ', limit_offset);
END if;
   
PREPARE stmtsqlquery FROM @sqlquery;
    EXECUTE stmtsqlquery;
    DEALLOCATE PREPARE stmtsqlquery;
END

**
