SDP | 技术员/用户 报表

SDP | 技术员/用户 报表

PgSQL 
仅技术员报表
含：名称，登录名，主邮箱地址，部门名称，部门地点，员工id，电话，关联组，角色
SELECT  max(aaauser.first_name)            AS "名称",max(aaalogin.name)                 AS "登录名",max(aaacontactinfo.emailid)        AS "主邮箱地址",max(departmentdefinition.deptname) AS "部门名称",max(sdorganization.NAME)           AS "地点",max(sduser.EMPLOYEEID)    AS "员工ID",max(aaacontact.LANDLINE)           AS "电话",max(aaacontact.MOBILE)    AS "手机" ,string_agg(qd.queuename,',')    AS "关联组",string_agg( ar.NAME, ',' )    AS "角色" FROM aaauser LEFT JOIN sduser ON aaauser.user_id=sduser.userid LEFT JOIN aaauser sdus on sduser.REPORTINGTO = sdus.USER_ID LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id LEFT JOIN portaltechnicians ON aaauser.user_id=portaltechnicians.userid LEFT JOIN userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact  ON aaauser.USER_ID=aaausercontact.USER_ID  LEFT JOIN AaaContactInfo aaacontact  ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt  ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd  ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al  ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa  ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar  ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar  ON aar.ROLE_ID=ar.ROLE_ID  LEFT JOIN AaaRoleToCategory  ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE' AND portaltechnicians.userid is not null GROUP BY  aaauser.user_id;

测试环境：15120 pgsql

仅技术员报表2.0
关联组和角色去重。
SELECT  
    max(aaauser.first_name)              AS "名称",
    max(aaalogin.name)                   AS "登录名",
    max(aaacontactinfo.emailid)          AS "主邮箱地址",
    max(departmentdefinition.deptname)   AS "部门名称",
    max(sdorganization.NAME)             AS "地点",
    max(sduser.EMPLOYEEID)               AS "员工ID",
    max(aaacontact.LANDLINE)             AS "电话",
    max(aaacontact.MOBILE)               AS "手机",
    string_agg(DISTINCT qd.queuename, ',') AS "关联组",   -- 去重
    string_agg(DISTINCT ar.NAME, ',')      AS "角色"      -- 去重
FROM aaauser 
LEFT JOIN sduser ON aaauser.user_id=sduser.userid 
LEFT JOIN aaauser sdus on sduser.REPORTINGTO = sdus.USER_ID 
LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id 
LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id 
LEFT JOIN portaltechnicians ON aaauser.user_id=portaltechnicians.userid 
LEFT JOIN userdepartment ON aaauser.user_id=userdepartment.userid 
LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid 
LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id 
LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id 
LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id 
LEFT JOIN AaaUserContactInfo aaausercontact  ON aaauser.USER_ID=aaausercontact.USER_ID  
LEFT JOIN AaaContactInfo aaacontact  ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID 
LEFT JOIN Queue_Technician qt  ON qt.TECHNICIANID=AaaUser.USER_ID 
LEFT JOIN QueueDefinition qd  ON qt.QUEUEID=qd.QUEUEID 
LEFT JOIN AaaLogin al  ON aaauser.USER_ID=al.USER_ID 
LEFT JOIN AaaAccount aa  ON al.LOGIN_ID=aa.LOGIN_ID 
LEFT JOIN sdAuthorizedRole aar  ON aa.ACCOUNT_ID=aar.ACCOUNT_ID 
LEFT JOIN AaaRole ar  ON aar.ROLE_ID=ar.ROLE_ID  
LEFT JOIN AaaRoleToCategory  ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID 
WHERE sduser.status='ACTIVE' 
  AND portaltechnicians.userid IS NOT NULL 
GROUP BY aaauser.user_id;

所有用户报表
SELECT  max(aaauser.first_name)            AS "名称",max(aaalogin.name)                 AS "登录名",max(aaacontactinfo.emailid)        AS "主邮箱地址",max(departmentdefinition.deptname) AS "部门名称",max(sdorganization.NAME)           AS "地点",max(sduser.EMPLOYEEID)    AS "员工ID",max(aaacontact.LANDLINE)           AS "电话",max(aaacontact.MOBILE)    AS "手机" ,string_agg(qd.queuename,',')    AS "关联组",string_agg( ar.NAME, ',' )    AS "角色" FROM aaauser LEFT JOIN sduser ON aaauser.user_id=sduser.userid LEFT JOIN aaauser sdus on sduser.REPORTINGTO = sdus.USER_ID LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id LEFT JOIN portaltechnicians ON aaauser.user_id=portaltechnicians.userid LEFT JOIN userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact  ON aaauser.USER_ID=aaausercontact.USER_ID  LEFT JOIN AaaContactInfo aaacontact  ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt  ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd  ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al  ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa  ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar  ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar  ON aar.ROLE_ID=ar.ROLE_ID  LEFT JOIN AaaRoleToCategory  ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE'  GROUP BY  aaauser.user_id;

MSSQL 
仅技术员报表
含：名称，登录名，主邮箱地址，部门名称，部门地点，员工id，电话，关联组，角色
SELECT  max(aaauser.first_name)            AS "名称"       ,max(aaalogin.name)                 AS "登录名"       ,max(aaacontactinfo.emailid)        AS "主邮箱地址"       ,max(departmentdefinition.deptname) AS "部门名称"       ,max(sdorganization.NAME)           AS "地点"    ,max(sduser.EMPLOYEEID)    AS "员工ID"    ,max(aaacontact.LANDLINE)           AS "电话"    ,max(aaacontact.MOBILE)    AS "手机"    ,STUFF((SELECT  CHAR(10) + qd.queuename from AaaUser a LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID WHERE aaauser.user_id = a.user_id for xml path ('')), 1, 1, '') as "组"    ,STUFF((SELECT  CHAR(10) + aar.name FROM aaauser a LEFT JOIN aaalogin al ON al.user_id = a.user_id LEFT JOIN aaaaccount aaac ON al.login_id = aaac.login_id LEFT JOIN SDAuthorizedRole sdr ON aaac.account_id = sdr.account_id LEFT JOIN aaarole aar ON aar.role_id = sdr.role_id WHERE aaauser.user_id = a.user_id for xml path ('')), 1, 1, '') as "角色" FROM aaauser LEFT JOIN sduser ON aaauser.user_id=sduser.userid LEFT JOIN aaauser sdus on sduser.REPORTINGTO = sdus.USER_ID LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id LEFT JOIN portaltechnicians ON aaauser.user_id=portaltechnicians.userid LEFT JOIN userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE'AND portaltechnicians.userid is not null GROUP BY  aaauser.user_id;

用户报表
含：名称，登录名，主邮箱地址，部门名称，关联的地点，员工id，电话，关联组，角色
SELECT  max(aaauser.first_name)            AS "名称"       ,max(aaalogin.name)                 AS "登录名"       ,max(aaacontactinfo.emailid)        AS "主邮箱地址"       ,max(departmentdefinition.deptname) AS "部门名称"       ,max(sdorganization.NAME)           AS "地点"    ,max(sduser.EMPLOYEEID)    AS "员工ID"    ,max(aaacontact.LANDLINE)           AS "电话"    ,max(aaacontact.MOBILE)    AS "手机"     ,STUFF((SELECT  CHAR(10) + qd.queuename from AaaUser a LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID WHERE aaauser.user_id = a.user_id for xml path ('')), 1, 1, '') as "组"     ,STUFF((SELECT  CHAR(10) + aar.name FROM aaauser a LEFT JOIN aaalogin al ON al.user_id = a.user_id LEFT JOIN aaaaccount aaac ON al.login_id = aaac.login_id LEFT JOIN SDAuthorizedRole sdr ON aaac.account_id = sdr.account_id LEFT JOIN aaarole aar ON aar.role_id = sdr.role_id WHERE aaauser.user_id = a.user_id for xml path ('')), 1, 1, '') as "角色" FROM aaauser LEFT JOIN sduser ON aaauser.user_id=sduser.userid LEFT JOIN aaauser sdus on sduser.REPORTINGTO = sdus.USER_ID LEFT JOIN aaalogin ON aaalogin.user_id = aaauser.user_id LEFT JOIN aaaaccount ON aaalogin.login_id = aaaaccount.login_id  LEFT JOIN userdepartment ON aaauser.user_id=userdepartment.userid LEFT JOIN departmentdefinition ON userdepartment.deptid=departmentdefinition.deptid LEFT JOIN sdorganization ON departmentdefinition.siteid = sdorganization.org_id LEFT JOIN aaausercontactinfo ON aaausercontactinfo.user_id=aaauser.user_id LEFT JOIN aaacontactinfo ON aaausercontactinfo.contactinfo_id=aaacontactinfo.contactinfo_id LEFT JOIN AaaUserContactInfo aaausercontact ON aaauser.USER_ID=aaausercontact.USER_ID LEFT JOIN AaaContactInfo aaacontact ON aaausercontact.CONTACTINFO_ID=aaacontact.CONTACTINFO_ID LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON aaauser.USER_ID=al.USER_ID LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID LEFT JOIN sdAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID WHERE sduser.status='ACTIVE' GROUP BY  aaauser.user_id;

    • Related Articles

    • SDP 获取技术员的在线状态

      简介 部分用户需要将SDP的技术员的在线信息同步至第三方。 技术员的在线信息如下图，技术员可自行控制是否在线。 报表 对应的信息是存储在technicianstatus表中。 1表示online,2标识offline select * from technicianstatus; select * from techstatusdefinition; 同步 如需同步至第三方，以下两种方式供参考： 1、报表API 2、自定义计划

    • 通过SQL查询获取技术员信息进行导出（数据库SQL Server）

            在11版本及更新的版本中，在后面版本中表结构发生了改变，取消了通过cmdb模块直接生成关于技术员信息的报表，变为了只能导出所有用户，而不再分技术员还是普通请求人。       如果有通过报表生成技术员信息的需求，可以参考如下SQL语句进行出具（SQL Server） 核心表参考（portaltechnicians） 可以基于此再进行调整出具需要的关联查询。 SELECT  max(aaauser.first_name)            AS "Name"       ...

    • 当请求人或技术员被删除后，将会发生什么？对于每个功能模块又会有怎样的影响？

      当我们从AD、CSV导入用户，或者手工将用户逐一添加到系统时，这些用户我们统称为请求人。 现在当他们其中有些为技术员，其他的仍为请求人，当删除他们的账号时，若他们有创建或指派的请求，那么将会出现以下情况： 当请求人账号被删除 所有该用户创建的请求将仍然保留他们的名字，不会从请求上取消关联。但是该请求人的账号状态会在数据库里被置为RESIGNED状态（系统表现为删除的用户）。 当技术员账号被删除 ...

    • SDP 工单满意度调查报表

      效果图 实现步骤 一、需要收集的信息 1.1、使用的数据库类型； 1.2、Select * from Survey_Question 的查询结果； 如： 1.3、调查配置详情截图； 如： 1.4、通过自定义报表，获取已满足需求的除满意度调查的 SQL 如： SELECT "wo"."WORKORDERID" AS "请求ID", "ti"."FIRST_NAME" AS "作业技术员" FROM "WorkOrder" "wo" LEFT JOIN "WorkOrderStates" "wos" ...

    • 用户调查报表

      代码实现自定义用户调查报表，显示上个月所有技术员的已发送调查数，被回答的调查数及平均满意度 以下sql语句适用于SQL server 2017： SELECT   ti.first_name "Technician",COUNT(srm.RESPONSEID) "NO of Survey sent", COUNT(case when responsetime is not null then 1 else null end) "No of Survey Answered" , ...