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;