/build/static/layout/Breadcrumb_cap_w.png

Generating Reports Ticket Queue

HELP ! how to generate report of all ticket queue with latest comments
select 

DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created' ,

CONCAT(IF(Q.TICKET_PREFIX = 'TICK:','TICK:', Q.TICKET_PREFIX),LPAD(HD_TICKET.ID, 4, '0')) as 'Number' ,

HD_TICKET.TITLE as 'Title' ,

Q.NAME as 'Queue' ,

HD_CATEGORY.NAME as 'Category' ,


if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,

(select FULL_NAME from USER where ID= HD_TICKET.APPROVER_ID) as 'Approver' ,

HD_STATUS.NAME as 'Ticket Status' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Person in Charge' ,

HD_TICKET.DUE_DATE as 'Due Date' ,

HD_TICKET_CHANGE.TIMESTAMP,

HD_TICKET_CHANGE.COMMENT,

HD_TICKET.CUSTOM_FIELD_VALUE13 as 'IS Complete Time' ,

HD_TICKET.CUSTOM_FIELD_VALUE31 as 'Renewal Date'


from (HD_TICKET)

                        LEFT JOIN HD_STATUS on (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)

                        LEFT JOIN HD_IMPACT on (HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID)

                        LEFT JOIN HD_CATEGORY on (HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)

                        LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)

                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID

                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID

                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID 

                        LEFT OUTER JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 

                        

                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

                        LEFT JOIN HD_TICKET_APPROVAL_PARENT HTAP on HTAP.HD_TICKET_ID = HD_TICKET.ID

                        LEFT JOIN HD_SERVICE_STATUS HSS on HD_TICKET.HD_SERVICE_STATUS_ID = HSS.ID

                        LEFT JOIN HD_SERVICE_TICKET HST on HST.ID = HD_TICKET.SERVICE_TICKET_ID

                        LEFT JOIN HD_SERVICE HS on HS.ID = HST.HD_SERVICE_ID

                        LEFT JOIN HD_SERVICE_TYPE HSTT on HSTT.ID = HS.HD_SERVICE_TYPE_ID

                        LEFT JOIN HD_TICKET_TEMPLATE on HD_TICKET_TEMPLATE.ID = HD_TICKET.TICKET_TEMPLATE_ID and HD_TICKET_TEMPLATE.QUEUE_ID = HD_TICKET.HD_QUEUE_ID 

where 1 = 1  AND 

                        (( HD_TICKET.HD_QUEUE_ID in (8,5,6,85,9,11,10,2,12,14,13) ) OR ( (HD_TICKET.OWNER_ID = 151)  or (HD_TICKET.APPROVER_ID = 151)  or (HD_TICKET.SUBMITTER_ID = 151)  or

                    EXISTS (

                        select *

                          from HD_TICKET_APPROVAL HTA,

                               HD_TICKET_APPROVAL_STAGE HTAS

                         where HTA.HD_TICKET_ID = HD_TICKET.ID

                           and HTA.HD_TICKET_ID = HTAS.HD_TICKET_ID

                           and HTA.USER_ID = 151

                           and HTA.APPROVAL = 'info'

                           and HTAS.APPROVAL_DATE = '0000-00-00 00:00:00'

                    ) ))  and ((HD_STATUS.STATE not in ('closed')  and (

            (!HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.SERVICE_TICKET_ID and HD_TICKET.IS_PARENT)

            or

            (HD_TICKET.SERVICE_TICKET_ID = 0 or (HD_TICKET.SERVICE_TICKET_ID and !HD_TICKET.IS_PARENT))

        ))

                                or (HSS.STATE not in ('closed')  and (HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.SERVICE_TICKET_ID and HD_TICKET.IS_PARENT))) and HD_TICKET_CHANGE.COMMENT is not NULL order by HD_TICKET_CHANGE.TIMESTAMP DESC;

                                

                                

           



0 Comments   [ + ] Show comments

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ