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