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" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" WHERE ( ( ( "wo"."CREATEDTIME" >= 1666540800000 ) AND ( ( "wo"."CREATEDTIME" != 0 ) AND ( "wo"."CREATEDTIME" IS NOT NULL ) ) ) AND ( ( "wo"."CREATEDTIME" <= 1667145599999 ) AND ( ( ( "wo"."CREATEDTIME" != 0 ) AND ( "wo"."CREATEDTIME" IS NOT NULL ) ) AND ( "wo"."CREATEDTIME" != -1 ) ) ) ) AND wo.ISPARENT='1'

二、调试

2.1、获取工单 ID 对应的调查问题；

通过行列转换，将工单 ID 与相关问题结果整合在一起。

根据客户数据库类型，将下列 SQL 中的 标红文字 替换为步骤 1.2 中获取到的调查问题的 QUESID 即可。

MSSQL：

select srrma.WORKORDERID,

stuff((select ''+(case srb.QUESID when 1 then srb.Value end) as "test1"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques1",

stuff((select ''+(case srb.QUESID when 2 then srb.Value end) as "test2"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques2",

stuff((select ''+(case srb.QUESID when 3 then srb.Value end) as "test3"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques3",

stuff((select ''+(case srb.QUESID when 4 then srb.Value end) as "test4"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques4"

from SurveyResponseRequestMapping srrma LEFT JOIN Survey_Response_Answer sra ON

srrma.responseid=sra.responseid LEFT JOIN Survey_Question sq ON

sra.quesid=sq.quesid

group by srrma.workorderid

如果问题类型为binaryValue(是或否), 则"是"对应的srb.Value是10, 如需输出文字,可将对应问题的srb.Value(上文中的橙色文字 )替换为srb.answer

PGSQL

select srrm.workorderid,

string_agg((case sq.quesid when 1 then answer end),null) as "ques1",

string_agg((case sq.quesid when 2 then answer end),null) as "ques2",

string_agg((case sq.quesid when 3 then answer end),null) as "ques3",

string_agg((case sq.quesid when 4 then answer end),null) as "ques4"

from SurveyResponseRequestMapping srrm LEFT JOIN Survey_Response_Answer sra ON srrm.responseid=sra.responseid LEFT JOIN Survey_Question sq ON sra.quesid=sq.quesid group by srrm.workorderid

2.2、获取工单 ID 对应的调查评论；

如图：

select workorderid, Commenttext from survey_response_comment src left join surveyresponserequestmapping srm on src.responseid = srm.responseid

三、插入实际的表中

分别将请求的调查问题和调查评论的表通过 LEFT JOIN ... ON 添加到步骤 1.4 中获取的 SQL 中。

可根据 1.3 中获取的详情配置，将下列 满意度问题 替换为客户实际的问题

添加后：

MSSQL

SELECT "wo"."WORKORDERID" AS "请求ID", "ti"."FIRST_NAME" AS "作业技术员" ,ques.ques1 AS "满意度问题一", ques.ques2 AS "满意度问题二", ques.ques3 AS "满意度问题三", ques.ques4 AS "满意度问题四",resp.Commenttext AS "评论" FROM "WorkOrder" "wo" 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 ( select srrma.WORKORDERID,

stuff((select ''+(case srb.QUESID when 1 then srb.Value end) as "test1"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques1",

stuff((select ''+(case srb.QUESID when 2 then srb.Value end) as "test2"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques2",

stuff((select ''+(case srb.QUESID when 3 then srb.Value end) as "test3"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques3",

stuff((select ''+(case srb.QUESID when 4 then srb.Value end) as "test4"

from Survey_Response_Answer srb left join SurveyResponseRequestMapping srrmb ON srrmb.responseid=srb.RESPONSEID

where srrmb.WORKORDERID=srrma.workorderid

for xml path('')),1,0,'')as "ques4"

from SurveyResponseRequestMapping srrma LEFT JOIN Survey_Response_Answer sra ON

srrma.responseid=sra.responseid LEFT JOIN Survey_Question sq ON

sra.quesid=sq.quesid

group by srrma.workorderid) ques ON wo.WORKORDERID= ques.WORKORDERID

LEFT JOIN (select workorderid, Commenttext from survey_response_comment src left join surveyresponserequestmapping srm on src.responseid = srm.responseid) resp ON wo.WORKORDERID = resp.WORKORDERID

WHERE ( ( ( "wo"."CREATEDTIME" >= 1666540800000 ) AND ( ( "wo"."CREATEDTIME" != 0 ) AND ( "wo"."CREATEDTIME" IS NOT NULL ) ) ) AND ( ( "wo"."CREATEDTIME" <= 1667145599999 ) AND ( ( ( "wo"."CREATEDTIME" != 0 ) AND ( "wo"."CREATEDTIME" IS NOT NULL ) ) AND ( "wo"."CREATEDTIME" != -1 ) ) ) ) AND wo.ISPARENT='1'

PGSQL

SELECT wo.WORKORDERID AS "请求ID", ti.FIRST_NAME AS "作业技术员" ,ques.ques1 AS "满意度问题一", ques.ques2 AS "满意度问题二", ques.ques3 AS "满意度问题三", resp.Commenttext AS "评论" FROM WorkOrder wo 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 (select srrm.workorderid,

string_agg((case sq.quesid when '1' then answer end),null) as "ques1",

string_agg((case sq.quesid when '2' then answer end),null) as "ques2",

string_agg((case sq.quesid when '3' then answer end),null) as "ques3"

from SurveyResponseRequestMapping srrm LEFT JOIN Survey_Response_Answer sra ON srrm.responseid=sra.responseid LEFT JOIN Survey_Question sq ON sra.quesid=sq.quesid group by srrm.workorderid) ques ON wo.workorderid=ques.workorderid

LEFT JOIN (select workorderid, Commenttext from survey_response_comment src left join surveyresponserequestmapping srm on src.responseid = srm.responseid) resp ON wo.WORKORDERID = resp.WORKORDERID

WHERE ( ( ( wo.CREATEDTIME >= 1666454400000 ) AND ( ( wo.CREATEDTIME != 0 ) AND ( wo.CREATEDTIME IS NOT NULL ) ) ) AND ( ( wo.CREATEDTIME <= 1667059199999 ) AND ( ( ( wo.CREATEDTIME != 0 ) AND ( wo.CREATEDTIME IS NOT NULL ) ) AND ( wo.CREATEDTIME != -1 ) ) ) ) AND wo.ISPARENT='1'

效果如图:

附

关于不同类型的调查问题的结果

所有的调查问题的结果都在表 Survey_Response_Answer 中。

类型	TYPE	VALUE	ANSWER	MAX_VAL
评级	starRating	0到MAX_VAL	VALUE/MAX_VAL	页面设置的最大值
评级尺度	Rating	0到MAX_VAL	VALUE/MAX_VAL	页面设置的最大值
二级制的	binaryValue	10或0	页面中选项文本框中的值	10
单选	Radio	页面中选项中右侧的值	页面中选项文本框中的值	页面设置的最大值

用户提交调查的时间

select workorderid,sc.RESPONSETIME from surveyresponserequestmapping srm left join survey_response_main sc on sc.RESPONSEID=srm.RESPONSEID

源数据是时间戳，如需转换成日期格式，可以使用SDP的方法longtodate.

select workorderid,longtodate(sc.RESPONSETIME) from surveyresponserequestmapping srm left join survey_response_main sc on sc.RESPONSEID=srm.RESPONSEID

Related Articles
ADSSP-启用SDP Saml SSO时提示“认证失败”或“无权限”
当AD用户在ServiceDesk Plus登录界面选择通过SAML SSO(IdP：ADSelfService Plus)认证：在界面跳转时提示无权限查看内容（即使看到URL链接已成功跳转到ADSelfService Plus产品）：或身份认证界面跳转失败提示SAML认证失败：怎么解决？解决办法：在ADSelfService Plus中链接账户属性查看： ->如果选择的源属性是mail，改为sAMAccountName（上面的报错截图1的情况）； ...
SDP & PMP 集成注意事项
1 要确保PMP和sdp中有一样的管理员如都具有同登录名称为admin的用户（其中sdp上要为其设置SDAdmin权限） 2 确保双方hostname能在双方服务器上互为解析 3 url最好换为ip地址以防解析不到 4 看清需要，如果不是变更处使用PMP的话请不要选择使用变更ID用于验证，不然关联的ID就不是工单ID而变成变更ID了注意：如果sdp使用的是https的话需要相应导入证书，不然无法直接集成。 PMP 下设置实际的过滤条件 ...
使用.pfx证书将OpManager与SDP集成
在SDP中应用.pfx证书不涉及创建集成所需的密钥库文件。请按照以下步骤创建密钥库文件。将文件OpManager.TrustStore和SDP PFX文件放置在OpManager JRE\bin安装下。 1.运行以下命令，在OpManager\Jre\bin目录将pfx证书转换为JKS格式。 >keytool -importkeystore -srckeystore sdp_pfx_cert.p12 -destkeystore OpManager.truststore ...
Analytics Plus 中解析SDP（SQL SERVER数据库）时间模块
时间格式问题当我们将日期类型数据从SDP的数据库（SQL Server类型）导入Analytics Plus时，我们发现它的值是一个长数字，而不是日期格式。我们如何设置它来恢复正常的时间格式？解决方案：这里引入一段sql来理解： SELECT aau.FIRST_NAME AS "请求人", DATEADD(MILLISECOND, wo.CREATEDTIME % 1000, DATEADD(SECOND, wo.CREATEDTIME ...
sdp & msp 系统服务目录使用细节
sdp & msp 系统服务目录使用细节 1 有关系统服务目录能否看到的问题，需要两块来定义首先进入到服务目录中，每个服务目录定义出都有一块内容叫做：关联技术支持组到模板&显示给请求人_关联用户组到模板。意思为此技术支持组下及此用户组下的所有人可以对应看到这些服务目录。（这一块建议可以选择多个需要看的组） 2 ...

SDP 工单满意度调查报表

SDP 工单满意度调查报表

效果图

实现步骤

一、需要收集的信息

1.1、使用的数据库类型；

1.2、Select * from Survey_Question 的查询结果；

1.3、调查配置详情截图；

1.4、通过自定义报表，获取已满足需求的除满意度调查的 SQL

二、调试

2.1、获取工单 ID 对应的调查问题；

2.2、获取工单 ID 对应的调查评论；

三、插入实际的表中

MSSQL

PGSQL

附

关于不同类型的调查问题的结果

用户提交调查的时间

Related Articles

ADSSP-启用SDP Saml SSO时提示“认证失败”或“无权限”

SDP & PMP 集成注意事项

使用.pfx证书将OpManager与SDP集成

Analytics Plus 中解析SDP（SQL SERVER数据库）时间模块

sdp & msp 系统服务目录使用细节