审批人的查询报表

审批人的查询报表

      因为SDP审批人员在完成审批后，就无法查看相应的审批请求，而报表模块中同样没有默认的报表可以支撑这个查询，所以基本上是通过查询报表实现，语句如下：
语句在11.1版本上是验证过，可以执行，如果是11以下的版本，可以参考另外论坛上的语句：
11.1以下版本查询报表语句：

审批报表

按照需求类指标的要求，需要导出审批的报表，包含审批人，审批Email地址，审批历史，审批备注等信息。

查询语句如下：

SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ApprovalStatusDefinition.STATUSNAME "Approval Status", LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date", LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date", ApprovalDetails.COMMENTS "Approved Comments" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID = sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID = aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID = dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID = td.USERID LEFT JOIN AaaUser ti ON td.USERID = ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID = std.STATUSID JOIN ApprovalStageMapping ApprovalStageMapping ON wo.WORKORDERID = ApprovalStageMapping.WORKORDERID LEFT JOIN ApprovalDetails ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID = ApprovalDetails.APPROVAL_STAGEID LEFT JOIN ApprovalStage ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID = ApprovalStage.APPROVAL_STAGEID LEFT JOIN approvalstatusdefinition approvalstatusdefinition ON ApprovalDetails.STATUSID = ApprovalStatusDefinition.STATUSID LEFT JOIN approvalstagedefinition asd ON asd.STAGEID = ApprovalDetails.STAGEID LEFT JOIN aaauser createduser ON createduser.user_id = wo.createdbyid LEFT JOIN workorder_queue workorder_queue ON workorder_queue.workorderid = wo.workorderid LEFT JOIN queuedefinition queuedefinition ON workorder_queue.queueid = queuedefinition.queueid LEFT JOIN ServiceCatalog_Fields scf ON scf.workorderid = wo.workorderid LEFT JOIN requesttemplate_list reqlist ON reqlist.templateid = wo.templateid WHERE ApprovalStatusDefinition.STATUSNAME = 'Approved' AND ApprovalDetails.EMAIL IN ( '替换为审批人的邮件地址' ) AND wo.CREATEDTIME >= DATETOLONG('2020-01-01 07:00:00') AND wo.CREATEDTIME <= DATETOLONG('2020-09-30 23:59:59')


11版本以前的查询语句可参考以下：

SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",cd.CATEGORYNAME "Category",pd.PRIORITYNAME "Priority",wo.TITLE "Subject",LONGTODATE(wo.CREATEDTIME) "Created Time",LONGTODATE(wo.COMPLETEDTIME) "Completed Time",std.STATUSNAME "Request Status",

ApprovalDetails.EMAIL "Approver Email",
LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date",
LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date",
ApprovalStatusDefinition.STATUSNAME "Approval Status",
ApprovalDetails.COMMENTS "Comments" FROM WorkOrder wo 
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID 
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID 
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID 
LEFT JOIN  approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
 WHERE (wo.ISPARENT='1') AND ApprovalDetails.ISDELETED!=1 and (wo.CREATEDTIME >= unix_timestamp('2015-10-01 00:00:00')*1000)  AND (wo.CREATEDTIME <= unix_timestamp(' 2015-10-31 23:59:59')*1000) order by 1