工作備忘錄: 查詢線上所有流程的全部關卡參與者的SQL語法

建議可分拆SQL避免CTE執行效率不佳

WITH
 ProcessPackage_ActivityDefinations
 AS
 (
 SELECT ProcessPackage.id AS processPackage_id ,ProcessPackage.processPackageName ,ProcessPackageCategory.packageCategoryName ,ActivityDefinition.id AS activityDefinition_id ,DecisionRuleList.referActivityId ,ActivityDefinition.activityDefinitionName ,ActivityDefinition.containerOID ,ActivityDefinition.performType ,ActivityDefinition.multiUserMode ,ActivityDefinition.batchPerformable ,ActivityDefinition.reassignable ,ActivityDefinition.regainable ,ActivityDefinition.unReexecuteType ,ActivityDefinition.unReexcuteActIds ,ActivityDefinition.bypassable ,ActivityDefinition.ableToAskActivityReexecute ,ActivityDefinition.addingActivityAuthority ,ActivityDefinition.limits ,ProcessDefinitionHeader.durationUnit ,ActivityDefinition.dealOvertimeActivityType ,ActivityDefinition.autoDelivery ,CAST('' + REPLACE(CAST(ActivityDefinition.performerIds AS varchar(max)), ',', '＜/performerId＞＜performerId＞') + '＜/performerId＞' AS xml) AS performerIdXml
 FROM ProcessPackage
 INNER JOIN ProcessPackageCmItem ON ProcessPackage.containerOID = ProcessPackageCmItem.OID
 INNER JOIN RedefinableHeader ON ProcessPackage.redefinableHeaderOID = RedefinableHeader.OID
 INNER JOIN ProcessPackageHeader ON ProcessPackage.headerOID = ProcessPackageHeader.OID
 INNER JOIN ProcessPackage_ProcessDef ON ProcessPackage.OID = ProcessPackage_ProcessDef.ProcessPackageOID
 INNER JOIN ProcessDefinition ON ProcessPackage.id = ProcessDefinition.id
 INNER JOIN ProcessDefinitionHeader ON ProcessDefinition.headerOID = ProcessDefinitionHeader.OID
 INNER JOIN ProcessPackageCategory ON ProcessPackageCmItem.categoryOID = ProcessPackageCategory.OID
 INNER JOIN ActivityDefinition ON ProcessDefinition.OID = ActivityDefinition.containerOID
 LEFT JOIN DecisionRuleList ON ActivityDefinition.decisionRuleListOID = DecisionRuleList.OID
 WHERE ProcessDefinition.OID = ProcessPackage_ProcessDef.ProcessDefinitionOID AND ProcessPackageCmItem.lastVersion = RedefinableHeader.version AND validTo >= GETDATE() AND RedefinableHeader.publicationStatus = 'RELEASED'
 /**加入搜尋條件*/
 ) ,ProcessPackage_ActivityDefination_PerformerIds
 AS
 (
 SELECT processPackage_id ,processPackageName ,packageCategoryName ,activityDefinition_id ,referActivityId ,activityDefinitionName ,containerOID ,performType ,multiUserMode ,batchPerformable ,reassignable ,regainable ,unReexecuteType ,unReexcuteActIds ,bypassable ,ableToAskActivityReexecute ,addingActivityAuthority ,limits ,durationUnit ,dealOvertimeActivityType ,autoDelivery ,performerIds.value('.', 'varchar(max)') AS performerId
 FROM ProcessPackage_ActivityDefinations
 CROSS APPLY performerIdXml.nodes('/performerId') CA(performerIds)
 UNION ALL
 SELECT processPackage_id ,processPackageName ,packageCategoryName ,activityDefinition_id ,referActivityId ,activityDefinitionName ,containerOID ,performType ,multiUserMode ,batchPerformable ,reassignable ,regainable ,unReexecuteType ,unReexcuteActIds ,bypassable ,ableToAskActivityReexecute ,addingActivityAuthority ,limits ,durationUnit ,dealOvertimeActivityType ,autoDelivery ,NULL AS performerId
 FROM ProcessPackage_ActivityDefinations
 WHERE performerIdXml IS NULL
 AND activityDefinitionName <> 'Route Activity'
 ) ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition
 AS
 (
 SELECT ParticipantDefinition.activityDefinitionId ,ParticipantDefinition.participantType ,ParticipantDefinition.formFieldId ,ParticipantDefinition.organizationId ,Organization.organizationName ,ParticipantDefinition.includeSubUnit ,ParticipantDefinition.organizationUnitId ,OrganizationUnit.organizationUnitName ,ParticipantDefinition.employeeId ,Users.userName ,ParticipantDefinition.groupId ,Groups.groupName ,RoleDefinition.shortName ,ParticipantDefinition.roleDefinitionName ,ProcessPackage_ActivityDefination_PerformerIds.*
 FROM ProcessPackage_ActivityDefination_PerformerIds
 LEFT JOIN ParticipantDefinition ON ProcessPackage_ActivityDefination_PerformerIds.containerOID = ParticipantDefinition.containerOID
 AND ProcessPackage_ActivityDefination_PerformerIds.performerId = ParticipantDefinition.id
 LEFT JOIN Users ON ParticipantDefinition.employeeId = Users.id
 LEFT JOIN Organization ON ParticipantDefinition.organizationId = Organization.id
 LEFT JOIN OrganizationUnit ON ParticipantDefinition.organizationUnitId = OrganizationUnit.id
 LEFT JOIN Groups ON ParticipantDefinition.groupId = Groups.id
 LEFT JOIN RoleDefinition ON ParticipantDefinition.roleDefinitionName = RoleDefinition.roleDefinitionName
 )
SELECT ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.processPackage_id AS "流程序號" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.processPackageName AS "流程名稱" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.packageCategoryName AS "流程分類" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.activityDefinition_id AS "關卡代碼" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.activityDefinitionName AS "關卡名稱" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN 'INVOKE' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '核決權限' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.performType = 'NORMAL' THEN '一般' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.performType = 'EXECUTION' THEN '會辦(不可退件)' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.performType = 'NOTICE' THEN '通知' END AS "關卡類型" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId ELSE ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.activityDefinitionId END AS "參考關卡代碼" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.multiUserMode = 'FIREST_GET_FIRST_WIN' THEN '只需1人處理' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.multiUserMode = 'FOR_EACH' THEN '每人都需處理' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.multiUserMode = 'STRATEGY' THEN '分派策略模式' END AS "處理模式" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.limits = 0 THEN '無' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.dealOvertimeActivityType = 'SEND_NOTIFICATION' THEN CAST( ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.limits AS varchar ) + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.durationUnit + '後寄發通知給工作受託者' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.dealOvertimeActivityType = 'JUMP_TO_NEXT' THEN CAST( ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.limits AS varchar ) + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.durationUnit + '後跳到下一個活動' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.dealOvertimeActivityType = 'PASS_TO_SUBSTITUTE' THEN CAST( ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.limits AS varchar ) + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.durationUnit + '後給合適的代理人' END AS "逾時處理" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.batchPerformable = 1 THEN '是' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.batchPerformable = 0 THEN '否' END AS "批次簽核" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 0 THEN '無法轉派' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 1 THEN '只可轉派代理人' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 2 THEN '只可系統轉派' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 3 THEN '可轉派代理人及系統轉派' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 4 THEN '只可轉派他人' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 5 THEN '可轉派代理人及轉派他人' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 6 THEN '可轉派他人及系統轉派' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.reassignable = 7 THEN '可轉派代理人、轉派他人及系統轉派' END AS "轉派權限" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.regainable = 1 THEN '可' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.regainable = 0 THEN '不可' END AS "取回重辦" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.bypassable = 1 THEN '是' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.bypassable = 0 THEN '否' END AS "可跳過" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.autoDelivery = 1 THEN '不可自動派送' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.autoDelivery = 2 THEN '與前一關同簽核者,則自動派送' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.autoDelivery = 3 THEN '流程中有相同簽核者,則自動派送(含發起者)' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.autoDelivery = 4 THEN '流程中有相同簽核者,則自動派送(不含發起者)' END AS "自動派送" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.ableToAskActivityReexecute = 0 THEN '不可退件' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.ableToAskActivityReexecute = 1 THEN '可退件,預設按流程定義送回要求退件者' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.ableToAskActivityReexecute = 2 THEN '按流程定義送回要求退件者' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.ableToAskActivityReexecute = 3 THEN '直接送回要求退件者' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.ableToAskActivityReexecute = 4 THEN '可退件,預設直接送回要求退件者' END AS "可退件" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '參照' + ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId + '設定' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.addingActivityAuthority = 1 THEN '可向前加簽' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.addingActivityAuthority = 2 THEN '可向後加簽' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.addingActivityAuthority = 3 THEN '可向前後加簽' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.addingActivityAuthority = 0 THEN '不可加簽' END AS "加簽" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.referActivityId IS NOT NULL THEN '人員或關係人之部門主管' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'RESOURCE_SET' THEN '設備資源集合' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'RESOURCE' THEN '設備資源' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'GROUP' THEN '群組' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'ROLEDEFINITION' THEN '角色' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'ORGANIZATION_UNIT' THEN '部門' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'HUMAN' THEN '人員' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'SYSTEM' THEN '系統(INVOKE關卡)' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'MANAGER' THEN '人員或關係人之直屬主管' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'UNIT_MANAGER' THEN '人員或關係人之部門主管' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'FUNCTIONDEFINITION' THEN '職務' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'FORM_FIELD_DATA' THEN '表單欄位' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'MANAGER_OF_ORGANIZATION_UNIT' THEN '部門主管' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'ACTIVITY_SIGNER' THEN '關卡簽核者' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'PROCESS_REQUESTER' THEN '流程發起人' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'PROCESS_RELATIONAL' THEN '流程關係人' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType = 'STRATEGY_ASSIGN' THEN '策略分配' ELSE ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType END AS "參與者類型" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.formFieldId AS "表單欄位代碼" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.organizationId AS "組織代碼" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.organizationName AS "組織名稱" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.organizationUnitId AS "部門代碼" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.organizationUnitName AS "部門名稱" ,CASE WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.participantType <> 'ORGANIZATION_UNIT' THEN NULL WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.includeSubUnit = 1 THEN '是' WHEN ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.includeSubUnit = 0 THEN '否' END AS "是否包含子單位" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.employeeId AS "參與者工號" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.userName AS "參與者姓名" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.groupId AS "群組代碼" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.groupName AS "群組名稱" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.shortName AS "角色簡稱" ,ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.roleDefinitionName AS "角色名稱"
FROM ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition
ORDER BY ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.packageCategoryName, ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.processPackage_id, TRY_PARSE(REPLACE(REPLACE(ProcessPackage_ActivityDefination_PerformerId_ParticipantDefinition.activityDefinition_id, 'ACT', ''), '_', '.') AS float)
2019年1月19日 星期六

查詢線上所有流程的全部關卡參與者的SQL語法

2019年1月19日星期六
