目录
将报表写入excel，并使用邮件发送QQ邮箱和outlook邮箱效果【动图时长1分多钟】
views.py中定义的查询数据库时候使用的变量，通过变量的修改来达到不同的sql查询条件的目的
将html报表存储为html文件，通过邮件方式发送给指定客户
demo2项目
html报表效果
将报表写入excel，并使用邮件发送QQ邮箱和outlook邮箱效果【动图时长1分多钟】
outlook效果
QQ邮箱效果
demo2项目完整文件夹结构
dmeo2应用使用到的sql单独放在了sql.py文件中，通过编写返回sql语句函数，以及变量传参的方式来变换产生不同的sql查询语句。目前该项目views.py中的sql查询类已经通过类属性设置变量的方式在写入excel的时候使用了43条sql，每条sql 1100行左右，实际写入excel报表执行了52800条sql语句，算得上是复杂度中等。
sql.py文件上半部分
上半部分比较简单就一个变量存储了一个长字符串sql语句
global sqa3
sqa3 = '''
/*Total Call 30天内的每天创建的工单数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Total Call' KPI,
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t1.c AS Total_Call,
case when datediff(dd, t1.cr_date, getdate()) =1 then t1.c else '' end as D1,
case when datediff(dd, t1.cr_date, getdate()) =2 then t1.c else '' end as D2,
case when datediff(dd, t1.cr_date, getdate()) =3 then t1.c else '' end as D3,
case when datediff(dd, t1.cr_date, getdate()) =4 then t1.c else '' end as D4,
case when datediff(dd, t1.cr_date, getdate()) =5 then t1.c else '' end as D5,
case when datediff(dd, t1.cr_date, getdate()) =6 then t1.c else '' end as D6,
case when datediff(dd, t1.cr_date, getdate()) =7 then t1.c else '' end as D7,
case when datediff(dd, t1.cr_date, getdate()) =8 then t1.c else '' end as D8,
case when datediff(dd, t1.cr_date, getdate()) =9 then t1.c else '' end as D9,
case when datediff(dd, t1.cr_date, getdate()) =10 then t1.c else '' end as D10,
case when datediff(dd, t1.cr_date, getdate()) =11 then t1.c else '' end as D11,
case when datediff(dd, t1.cr_date, getdate()) =12 then t1.c else '' end as D12,
case when datediff(dd, t1.cr_date, getdate()) =13 then t1.c else '' end as D13,
case when datediff(dd, t1.cr_date, getdate()) =14 then t1.c else '' end as D14,
case when datediff(dd, t1.cr_date, getdate()) =15 then t1.c else '' end as D15,
case when datediff(dd, t1.cr_date, getdate()) =16 then t1.c else '' end as D16,
case when datediff(dd, t1.cr_date, getdate()) =17 then t1.c else '' end as D17,
case when datediff(dd, t1.cr_date, getdate()) =18 then t1.c else '' end as D18,
case when datediff(dd, t1.cr_date, getdate()) =19 then t1.c else '' end as D19,
case when datediff(dd, t1.cr_date, getdate()) =20 then t1.c else '' end as D20,
case when datediff(dd, t1.cr_date, getdate()) =21 then t1.c else '' end as D21,
case when datediff(dd, t1.cr_date, getdate()) =22 then t1.c else '' end as D22,
case when datediff(dd, t1.cr_date, getdate()) =23 then t1.c else '' end as D23,
case when datediff(dd, t1.cr_date, getdate()) =24 then t1.c else '' end as D24,
case when datediff(dd, t1.cr_date, getdate()) =25 then t1.c else '' end as D25,
case when datediff(dd, t1.cr_date, getdate()) =26 then t1.c else '' end as D26,
case when datediff(dd, t1.cr_date, getdate()) =27 then t1.c else '' end as D27,
case when datediff(dd, t1.cr_date, getdate()) =28 then t1.c else '' end as D28,
case when datediff(dd, t1.cr_date, getdate()) =29 then t1.c else '' end as D29,
case when datediff(dd, t1.cr_date, getdate()) =30 then t1.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t1
) AS A
UNION ALL
/*Unclosed 30天内的每天创建后，状态未关闭的工单数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Unclosed',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t2.c AS Unclosed,
case when datediff(dd, t2.cr_date, getdate()) =1 then t2.c else '' end as D1,
case when datediff(dd, t2.cr_date, getdate()) =2 then t2.c else '' end as D2,
case when datediff(dd, t2.cr_date, getdate()) =3 then t2.c else '' end as D3,
case when datediff(dd, t2.cr_date, getdate()) =4 then t2.c else '' end as D4,
case when datediff(dd, t2.cr_date, getdate()) =5 then t2.c else '' end as D5,
case when datediff(dd, t2.cr_date, getdate()) =6 then t2.c else '' end as D6,
case when datediff(dd, t2.cr_date, getdate()) =7 then t2.c else '' end as D7,
case when datediff(dd, t2.cr_date, getdate()) =8 then t2.c else '' end as D8,
case when datediff(dd, t2.cr_date, getdate()) =9 then t2.c else '' end as D9,
case when datediff(dd, t2.cr_date, getdate()) =10 then t2.c else '' end as D10,
case when datediff(dd, t2.cr_date, getdate()) =11 then t2.c else '' end as D11,
case when datediff(dd, t2.cr_date, getdate()) =12 then t2.c else '' end as D12,
case when datediff(dd, t2.cr_date, getdate()) =13 then t2.c else '' end as D13,
case when datediff(dd, t2.cr_date, getdate()) =14 then t2.c else '' end as D14,
case when datediff(dd, t2.cr_date, getdate()) =15 then t2.c else '' end as D15,
case when datediff(dd, t2.cr_date, getdate()) =16 then t2.c else '' end as D16,
case when datediff(dd, t2.cr_date, getdate()) =17 then t2.c else '' end as D17,
case when datediff(dd, t2.cr_date, getdate()) =18 then t2.c else '' end as D18,
case when datediff(dd, t2.cr_date, getdate()) =19 then t2.c else '' end as D19,
case when datediff(dd, t2.cr_date, getdate()) =20 then t2.c else '' end as D20,
case when datediff(dd, t2.cr_date, getdate()) =21 then t2.c else '' end as D21,
case when datediff(dd, t2.cr_date, getdate()) =22 then t2.c else '' end as D22,
case when datediff(dd, t2.cr_date, getdate()) =23 then t2.c else '' end as D23,
case when datediff(dd, t2.cr_date, getdate()) =24 then t2.c else '' end as D24,
case when datediff(dd, t2.cr_date, getdate()) =25 then t2.c else '' end as D25,
case when datediff(dd, t2.cr_date, getdate()) =26 then t2.c else '' end as D26,
case when datediff(dd, t2.cr_date, getdate()) =27 then t2.c else '' end as D27,
case when datediff(dd, t2.cr_date, getdate()) =28 then t2.c else '' end as D28,
case when datediff(dd, t2.cr_date, getdate()) =29 then t2.c else '' end as D29,
case when datediff(dd, t2.cr_date, getdate()) =30 then t2.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t2
) AS A
UNION ALL
/*Scheduled 30天内，每天sla应到期工单数量且工单状态为未关*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Scheduled',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t3.c AS Scheduled,
case when datediff(dd, t3.cr_date, getdate()) =1 then t3.c else '' end as D1,
case when datediff(dd, t3.cr_date, getdate()) =2 then t3.c else '' end as D2,
case when datediff(dd, t3.cr_date, getdate()) =3 then t3.c else '' end as D3,
case when datediff(dd, t3.cr_date, getdate()) =4 then t3.c else '' end as D4,
case when datediff(dd, t3.cr_date, getdate()) =5 then t3.c else '' end as D5,
case when datediff(dd, t3.cr_date, getdate()) =6 then t3.c else '' end as D6,
case when datediff(dd, t3.cr_date, getdate()) =7 then t3.c else '' end as D7,
case when datediff(dd, t3.cr_date, getdate()) =8 then t3.c else '' end as D8,
case when datediff(dd, t3.cr_date, getdate()) =9 then t3.c else '' end as D9,
case when datediff(dd, t3.cr_date, getdate()) =10 then t3.c else '' end as D10,
case when datediff(dd, t3.cr_date, getdate()) =11 then t3.c else '' end as D11,
case when datediff(dd, t3.cr_date, getdate()) =12 then t3.c else '' end as D12,
case when datediff(dd, t3.cr_date, getdate()) =13 then t3.c else '' end as D13,
case when datediff(dd, t3.cr_date, getdate()) =14 then t3.c else '' end as D14,
case when datediff(dd, t3.cr_date, getdate()) =15 then t3.c else '' end as D15,
case when datediff(dd, t3.cr_date, getdate()) =16 then t3.c else '' end as D16,
case when datediff(dd, t3.cr_date, getdate()) =17 then t3.c else '' end as D17,
case when datediff(dd, t3.cr_date, getdate()) =18 then t3.c else '' end as D18,
case when datediff(dd, t3.cr_date, getdate()) =19 then t3.c else '' end as D19,
case when datediff(dd, t3.cr_date, getdate()) =20 then t3.c else '' end as D20,
case when datediff(dd, t3.cr_date, getdate()) =21 then t3.c else '' end as D21,
case when datediff(dd, t3.cr_date, getdate()) =22 then t3.c else '' end as D22,
case when datediff(dd, t3.cr_date, getdate()) =23 then t3.c else '' end as D23,
case when datediff(dd, t3.cr_date, getdate()) =24 then t3.c else '' end as D24,
case when datediff(dd, t3.cr_date, getdate()) =25 then t3.c else '' end as D25,
case when datediff(dd, t3.cr_date, getdate()) =26 then t3.c else '' end as D26,
case when datediff(dd, t3.cr_date, getdate()) =27 then t3.c else '' end as D27,
case when datediff(dd, t3.cr_date, getdate()) =28 then t3.c else '' end as D28,
case when datediff(dd, t3.cr_date, getdate()) =29 then t3.c else '' end as D29,
case when datediff(dd, t3.cr_date, getdate()) =30 then t3.c else '' end as D30
FROM (
SELECT
a.cr_date,
COUNT ( TICKET_ID ) c
FROM
(SELECT
CONVERT (
VARCHAR ( 10 ),DATEADD(
SECOND,
(
SELECT
top 1 sla_due_by
FROM
VSLA_AGREEMENT_COMPLIANCE_LIST_UX AS vc
WHERE
vc.item_id=vi.ROW_ID
ORDER BY
threshold_sort_order DESC
), '1970/1/1 08:00:00'
),120
) AS 'cr_date',
vi.TICKET_ID
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
person1_root_org_name = @kehu
AND TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted')
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) AS a
GROUP BY
a.cr_date
) AS t3
) AS A
UNION ALL
/* P1_call 30天内创建的工单，优先级最高的数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'P1 call',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t4.c AS P1_call,
case when datediff(dd, t4.cr_date, getdate()) =1 then t4.c else '' end as D1,
case when datediff(dd, t4.cr_date, getdate()) =2 then t4.c else '' end as D2,
case when datediff(dd, t4.cr_date, getdate()) =3 then t4.c else '' end as D3,
case when datediff(dd, t4.cr_date, getdate()) =4 then t4.c else '' end as D4,
case when datediff(dd, t4.cr_date, getdate()) =5 then t4.c else '' end as D5,
case when datediff(dd, t4.cr_date, getdate()) =6 then t4.c else '' end as D6,
case when datediff(dd, t4.cr_date, getdate()) =7 then t4.c else '' end as D7,
case when datediff(dd, t4.cr_date, getdate()) =8 then t4.c else '' end as D8,
case when datediff(dd, t4.cr_date, getdate()) =9 then t4.c else '' end as D9,
case when datediff(dd, t4.cr_date, getdate()) =10 then t4.c else '' end as D10,
case when datediff(dd, t4.cr_date, getdate()) =11 then t4.c else '' end as D11,
case when datediff(dd, t4.cr_date, getdate()) =12 then t4.c else '' end as D12,
case when datediff(dd, t4.cr_date, getdate()) =13 then t4.c else '' end as D13,
case when datediff(dd, t4.cr_date, getdate()) =14 then t4.c else '' end as D14,
case when datediff(dd, t4.cr_date, getdate()) =15 then t4.c else '' end as D15,
case when datediff(dd, t4.cr_date, getdate()) =16 then t4.c else '' end as D16,
case when datediff(dd, t4.cr_date, getdate()) =17 then t4.c else '' end as D17,
case when datediff(dd, t4.cr_date, getdate()) =18 then t4.c else '' end as D18,
case when datediff(dd, t4.cr_date, getdate()) =19 then t4.c else '' end as D19,
case when datediff(dd, t4.cr_date, getdate()) =20 then t4.c else '' end as D20,
case when datediff(dd, t4.cr_date, getdate()) =21 then t4.c else '' end as D21,
case when datediff(dd, t4.cr_date, getdate()) =22 then t4.c else '' end as D22,
case when datediff(dd, t4.cr_date, getdate()) =23 then t4.c else '' end as D23,
case when datediff(dd, t4.cr_date, getdate()) =24 then t4.c else '' end as D24,
case when datediff(dd, t4.cr_date, getdate()) =25 then t4.c else '' end as D25,
case when datediff(dd, t4.cr_date, getdate()) =26 then t4.c else '' end as D26,
case when datediff(dd, t4.cr_date, getdate()) =27 then t4.c else '' end as D27,
case when datediff(dd, t4.cr_date, getdate()) =28 then t4.c else '' end as D28,
case when datediff(dd, t4.cr_date, getdate()) =29 then t4.c else '' end as D29,
case when datediff(dd, t4.cr_date, getdate()) =30 then t4.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
count(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND sla_target_name=@sla_target_name
AND person1_root_org_name = @kehu
AND TICKET_STATUS not in ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t4
) AS A
UNION ALL
/*Over_SLA 30天创建的工单，状态已关闭，SLA已超时工单数量*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Over SLA',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t5.c AS Over_SLA,
case when datediff(dd, t5.cr_date, getdate()) =1 then t5.c else '' end as D1,
case when datediff(dd, t5.cr_date, getdate()) =2 then t5.c else '' end as D2,
case when datediff(dd, t5.cr_date, getdate()) =3 then t5.c else '' end as D3,
case when datediff(dd, t5.cr_date, getdate()) =4 then t5.c else '' end as D4,
case when datediff(dd, t5.cr_date, getdate()) =5 then t5.c else '' end as D5,
case when datediff(dd, t5.cr_date, getdate()) =6 then t5.c else '' end as D6,
case when datediff(dd, t5.cr_date, getdate()) =7 then t5.c else '' end as D7,
case when datediff(dd, t5.cr_date, getdate()) =8 then t5.c else '' end as D8,
case when datediff(dd, t5.cr_date, getdate()) =9 then t5.c else '' end as D9,
case when datediff(dd, t5.cr_date, getdate()) =10 then t5.c else '' end as D10,
case when datediff(dd, t5.cr_date, getdate()) =11 then t5.c else '' end as D11,
case when datediff(dd, t5.cr_date, getdate()) =12 then t5.c else '' end as D12,
case when datediff(dd, t5.cr_date, getdate()) =13 then t5.c else '' end as D13,
case when datediff(dd, t5.cr_date, getdate()) =14 then t5.c else '' end as D14,
case when datediff(dd, t5.cr_date, getdate()) =15 then t5.c else '' end as D15,
case when datediff(dd, t5.cr_date, getdate()) =16 then t5.c else '' end as D16,
case when datediff(dd, t5.cr_date, getdate()) =17 then t5.c else '' end as D17,
case when datediff(dd, t5.cr_date, getdate()) =18 then t5.c else '' end as D18,
case when datediff(dd, t5.cr_date, getdate()) =19 then t5.c else '' end as D19,
case when datediff(dd, t5.cr_date, getdate()) =20 then t5.c else '' end as D20,
case when datediff(dd, t5.cr_date, getdate()) =21 then t5.c else '' end as D21,
case when datediff(dd, t5.cr_date, getdate()) =22 then t5.c else '' end as D22,
case when datediff(dd, t5.cr_date, getdate()) =23 then t5.c else '' end as D23,
case when datediff(dd, t5.cr_date, getdate()) =24 then t5.c else '' end as D24,
case when datediff(dd, t5.cr_date, getdate()) =25 then t5.c else '' end as D25,
case when datediff(dd, t5.cr_date, getdate()) =26 then t5.c else '' end as D26,
case when datediff(dd, t5.cr_date, getdate()) =27 then t5.c else '' end as D27,
case when datediff(dd, t5.cr_date, getdate()) =28 then t5.c else '' end as D28,
case when datediff(dd, t5.cr_date, getdate()) =29 then t5.c else '' end as D29,
case when datediff(dd, t5.cr_date, getdate()) =30 then t5.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
count(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
sla_compliance_status_indicator='Breached SLA'
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t5
) AS A
UNION ALL
/*插入日期t6*/
SELECT
'Target',
--如果@CCTI_CLASS是空值，显示ALL，如果@CCTI_CLASS有值，@CCTI_CLASS值
CASE WHEN
@CCTI_CLASS=''
THEN
'ALL'
ELSE
@CCTI_CLASS
END,
--判断到此结束
'KPI',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
case when datediff(dd, t6.cr_date, getdate()) =1 then t6.c else '' end as D1,
case when datediff(dd, t6.cr_date, getdate()) =2 then t6.c else '' end as D2,
case when datediff(dd, t6.cr_date, getdate()) =3 then t6.c else '' end as D3,
case when datediff(dd, t6.cr_date, getdate()) =4 then t6.c else '' end as D4,
case when datediff(dd, t6.cr_date, getdate()) =5 then t6.c else '' end as D5,
case when datediff(dd, t6.cr_date, getdate()) =6 then t6.c else '' end as D6,
case when datediff(dd, t6.cr_date, getdate()) =7 then t6.c else '' end as D7,
case when datediff(dd, t6.cr_date, getdate()) =8 then t6.c else '' end as D8,
case when datediff(dd, t6.cr_date, getdate()) =9 then t6.c else '' end as D9,
case when datediff(dd, t6.cr_date, getdate()) =10 then t6.c else '' end as D10,
case when datediff(dd, t6.cr_date, getdate()) =11 then t6.c else '' end as D11,
case when datediff(dd, t6.cr_date, getdate()) =12 then t6.c else '' end as D12,
case when datediff(dd, t6.cr_date, getdate()) =13 then t6.c else '' end as D13,
case when datediff(dd, t6.cr_date, getdate()) =14 then t6.c else '' end as D14,
case when datediff(dd, t6.cr_date, getdate()) =15 then t6.c else '' end as D15,
case when datediff(dd, t6.cr_date, getdate()) =16 then t6.c else '' end as D16,
case when datediff(dd, t6.cr_date, getdate()) =17 then t6.c else '' end as D17,
case when datediff(dd, t6.cr_date, getdate()) =18 then t6.c else '' end as D18,
case when datediff(dd, t6.cr_date, getdate()) =19 then t6.c else '' end as D19,
case when datediff(dd, t6.cr_date, getdate()) =20 then t6.c else '' end as D20,
case when datediff(dd, t6.cr_date, getdate()) =21 then t6.c else '' end as D21,
case when datediff(dd, t6.cr_date, getdate()) =22 then t6.c else '' end as D22,
case when datediff(dd, t6.cr_date, getdate()) =23 then t6.c else '' end as D23,
case when datediff(dd, t6.cr_date, getdate()) =24 then t6.c else '' end as D24,
case when datediff(dd, t6.cr_date, getdate()) =25 then t6.c else '' end as D25,
case when datediff(dd, t6.cr_date, getdate()) =26 then t6.c else '' end as D26,
case when datediff(dd, t6.cr_date, getdate()) =27 then t6.c else '' end as D27,
case when datediff(dd, t6.cr_date, getdate()) =28 then t6.c else '' end as D28,
case when datediff(dd, t6.cr_date, getdate()) =29 then t6.c else '' end as D29,
case when datediff(dd, t6.cr_date, getdate()) =30 then t6.c else '' end as D30
FROM (
SELECT
t.cr_date,
datename(day,t.cr_date) c
FROM
(
SELECT
convert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_date
FROM
master..spt_values
WHERE
type = 'P'
AND number < @tianshu
) AS t
) AS t6
)AS A
UNION ALL
/*SLA_Met 最近30天创建的工单，SLA达成率，只计算关闭的工单。公式（达成SLA工单数量）/(总工单数量) */
SELECT
'>90%',
CAST(convert(decimal(16,2), CAST(sum(isnull(A.xjsl,0)) AS decimal(10,2)) *1.00*100 / CAST(sum(isnull(A.gdsl,0)) AS decimal(10,2))) AS varchar(50)) +'%',
'SLA Met',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t7.c AS SLA_Met,
t7.xjsl,
t7.gdsl,
case when datediff(dd, t7.cr_date, getdate()) =1 then t7.c else '' end as D1,
case when datediff(dd, t7.cr_date, getdate()) =2 then t7.c else '' end as D2,
case when datediff(dd, t7.cr_date, getdate()) =3 then t7.c else '' end as D3,
case when datediff(dd, t7.cr_date, getdate()) =4 then t7.c else '' end as D4,
case when datediff(dd, t7.cr_date, getdate()) =5 then t7.c else '' end as D5,
case when datediff(dd, t7.cr_date, getdate()) =6 then t7.c else '' end as D6,
case when datediff(dd, t7.cr_date, getdate()) =7 then t7.c else '' end as D7,
case when datediff(dd, t7.cr_date, getdate()) =8 then t7.c else '' end as D8,
case when datediff(dd, t7.cr_date, getdate()) =9 then t7.c else '' end as D9,
case when datediff(dd, t7.cr_date, getdate()) =10 then t7.c else '' end as D10,
case when datediff(dd, t7.cr_date, getdate()) =11 then t7.c else '' end as D11,
case when datediff(dd, t7.cr_date, getdate()) =12 then t7.c else '' end as D12,
case when datediff(dd, t7.cr_date, getdate()) =13 then t7.c else '' end as D13,
case when datediff(dd, t7.cr_date, getdate()) =14 then t7.c else '' end as D14,
case when datediff(dd, t7.cr_date, getdate()) =15 then t7.c else '' end as D15,
case when datediff(dd, t7.cr_date, getdate()) =16 then t7.c else '' end as D16,
case when datediff(dd, t7.cr_date, getdate()) =17 then t7.c else '' end as D17,
case when datediff(dd, t7.cr_date, getdate()) =18 then t7.c else '' end as D18,
case when datediff(dd, t7.cr_date, getdate()) =19 then t7.c else '' end as D19,
case when datediff(dd, t7.cr_date, getdate()) =20 then t7.c else '' end as D20,
case when datediff(dd, t7.cr_date, getdate()) =21 then t7.c else '' end as D21,
case when datediff(dd, t7.cr_date, getdate()) =22 then t7.c else '' end as D22,
case when datediff(dd, t7.cr_date, getdate()) =23 then t7.c else '' end as D23,
case when datediff(dd, t7.cr_date, getdate()) =24 then t7.c else '' end as D24,
case when datediff(dd, t7.cr_date, getdate()) =25 then t7.c else '' end as D25,
case when datediff(dd, t7.cr_date, getdate()) =26 then t7.c else '' end as D26,
case when datediff(dd, t7.cr_date, getdate()) =27 then t7.c else '' end as D27,
case when datediff(dd, t7.cr_date, getdate()) =28 then t7.c else '' end as D28,
case when datediff(dd, t7.cr_date, getdate()) =29 then t7.c else '' end as D29,
case when datediff(dd, t7.cr_date, getdate()) =30 then t7.c else '' end as D30
FROM (
SELECT
a.cr_date,
a.xjsl,
b.gdsl,
CAST(convert(decimal(16,2),CAST(a.xjsl*1.0*100 / b.gdsl AS decimal(10,2))) AS varchar(50)) +'%' c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) xjsl
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('closed','archive')
AND sla_compliance_status_indicator NOT IN ('Breached SLA','SLA Not Applied')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
AS a
join
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) gdsl
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('closed','archive')
AND sla_compliance_status_indicator NOT IN ('SLA Not Applied')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
AS b
ON a.cr_date=b.cr_date
) AS t7
) AS A
UNION ALL
/* Worst_TAT 30天内创建的工单，处理工单所花费最长时间时间跨度的工单的时间值，关闭时间-创建时间(只计算关闭的工单)*/
SELECT
'<24:00',
MAX(A.Worst_TAT),
'Worst TAT',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t8.c AS Worst_TAT,
case when datediff(dd, t8.cr_date, getdate()) =1 then t8.c else '' end as D1,
case when datediff(dd, t8.cr_date, getdate()) =2 then t8.c else '' end as D2,
case when datediff(dd, t8.cr_date, getdate()) =3 then t8.c else '' end as D3,
case when datediff(dd, t8.cr_date, getdate()) =4 then t8.c else '' end as D4,
case when datediff(dd, t8.cr_date, getdate()) =5 then t8.c else '' end as D5,
case when datediff(dd, t8.cr_date, getdate()) =6 then t8.c else '' end as D6,
case when datediff(dd, t8.cr_date, getdate()) =7 then t8.c else '' end as D7,
case when datediff(dd, t8.cr_date, getdate()) =8 then t8.c else '' end as D8,
case when datediff(dd, t8.cr_date, getdate()) =9 then t8.c else '' end as D9,
case when datediff(dd, t8.cr_date, getdate()) =10 then t8.c else '' end as D10,
case when datediff(dd, t8.cr_date, getdate()) =11 then t8.c else '' end as D11,
case when datediff(dd, t8.cr_date, getdate()) =12 then t8.c else '' end as D12,
case when datediff(dd, t8.cr_date, getdate()) =13 then t8.c else '' end as D13,
case when datediff(dd, t8.cr_date, getdate()) =14 then t8.c else '' end as D14,
case when datediff(dd, t8.cr_date, getdate()) =15 then t8.c else '' end as D15,
case when datediff(dd, t8.cr_date, getdate()) =16 then t8.c else '' end as D16,
case when datediff(dd, t8.cr_date, getdate()) =17 then t8.c else '' end as D17,
case when datediff(dd, t8.cr_date, getdate()) =18 then t8.c else '' end as D18,
case when datediff(dd, t8.cr_date, getdate()) =19 then t8.c else '' end as D19,
case when datediff(dd, t8.cr_date, getdate()) =20 then t8.c else '' end as D20,
case when datediff(dd, t8.cr_date, getdate()) =21 then t8.c else '' end as D21,
case when datediff(dd, t8.cr_date, getdate()) =22 then t8.c else '' end as D22,
case when datediff(dd, t8.cr_date, getdate()) =23 then t8.c else '' end as D23,
case when datediff(dd, t8.cr_date, getdate()) =24 then t8.c else '' end as D24,
case when datediff(dd, t8.cr_date, getdate()) =25 then t8.c else '' end as D25,
case when datediff(dd, t8.cr_date, getdate()) =26 then t8.c else '' end as D26,
case when datediff(dd, t8.cr_date, getdate()) =27 then t8.c else '' end as D27,
case when datediff(dd, t8.cr_date, getdate()) =28 then t8.c else '' end as D28,
case when datediff(dd, t8.cr_date, getdate()) =29 then t8.c else '' end as D29,
case when datediff(dd, t8.cr_date, getdate()) =30 then t8.c else '' end as D30
FROM (
SELECT
b.cr_date,
max(b.zd) c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') - DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) b
GROUP BY
b.cr_date
) AS t8
) AS A
UNION ALL
/*Remote_Fixed 最近30天创建的工单，远程解决率，只计算关闭的工单。公式（第一次上门时间为空的工单数）/(总工单数量) */
SELECT
'>30%',
CAST(convert(decimal(16,2), CAST(sum(isnull(A.fengzi,0)) AS decimal(10,2)) *1.00*100 / CAST(sum(isnull(A.fengmu,0)) AS decimal(10,2))) AS varchar(50)) +'%',
'Remote Fixed',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t9.c AS Remote_Fixed,
t9.fengzi,
t9.fengmu,
case when datediff(dd, t9.cr_date, getdate()) =1 then t9.c else '' end as D1,
case when datediff(dd, t9.cr_date, getdate()) =2 then t9.c else '' end as D2,
case when datediff(dd, t9.cr_date, getdate()) =3 then t9.c else '' end as D3,
case when datediff(dd, t9.cr_date, getdate()) =4 then t9.c else '' end as D4,
case when datediff(dd, t9.cr_date, getdate()) =5 then t9.c else '' end as D5,
case when datediff(dd, t9.cr_date, getdate()) =6 then t9.c else '' end as D6,
case when datediff(dd, t9.cr_date, getdate()) =7 then t9.c else '' end as D7,
case when datediff(dd, t9.cr_date, getdate()) =8 then t9.c else '' end as D8,
case when datediff(dd, t9.cr_date, getdate()) =9 then t9.c else '' end as D9,
case when datediff(dd, t9.cr_date, getdate()) =10 then t9.c else '' end as D10,
case when datediff(dd, t9.cr_date, getdate()) =11 then t9.c else '' end as D11,
case when datediff(dd, t9.cr_date, getdate()) =12 then t9.c else '' end as D12,
case when datediff(dd, t9.cr_date, getdate()) =13 then t9.c else '' end as D13,
case when datediff(dd, t9.cr_date, getdate()) =14 then t9.c else '' end as D14,
case when datediff(dd, t9.cr_date, getdate()) =15 then t9.c else '' end as D15,
case when datediff(dd, t9.cr_date, getdate()) =16 then t9.c else '' end as D16,
case when datediff(dd, t9.cr_date, getdate()) =17 then t9.c else '' end as D17,
case when datediff(dd, t9.cr_date, getdate()) =18 then t9.c else '' end as D18,
case when datediff(dd, t9.cr_date, getdate()) =19 then t9.c else '' end as D19,
case when datediff(dd, t9.cr_date, getdate()) =20 then t9.c else '' end as D20,
case when datediff(dd, t9.cr_date, getdate()) =21 then t9.c else '' end as D21,
case when datediff(dd, t9.cr_date, getdate()) =22 then t9.c else '' end as D22,
case when datediff(dd, t9.cr_date, getdate()) =23 then t9.c else '' end as D23,
case when datediff(dd, t9.cr_date, getdate()) =24 then t9.c else '' end as D24,
case when datediff(dd, t9.cr_date, getdate()) =25 then t9.c else '' end as D25,
case when datediff(dd, t9.cr_date, getdate()) =26 then t9.c else '' end as D26,
case when datediff(dd, t9.cr_date, getdate()) =27 then t9.c else '' end as D27,
case when datediff(dd, t9.cr_date, getdate()) =28 then t9.c else '' end as D28,
case when datediff(dd, t9.cr_date, getdate()) =29 then t9.c else '' end as D29,
case when datediff(dd, t9.cr_date, getdate()) =30 then t9.c else '' end as D30
FROM (
select
a.cr_date,
a.fengzi,
b.fengmu,
CAST(convert(decimal(16,2), CAST(a.fengzi*1.00*100 / b.fengmu AS decimal(10,2))) AS varchar(50)) +'%' c
from
(
select
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) fengzi
from
VAPP_ITEM as vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
TICKET_STATUS IN ('Closed','Archive')
AND (
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES as va
WHERE
va.ITEM_ID=vi.ROW_ID
and va.ATTR_ID=553) IS NULL
and person1_root_org_name = @kehu
and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=@tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
group by
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
as a
join
(
select
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) fengmu
from
VAPP_ITEM as vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
TICKET_STATUS IN ('Closed','Archive')
and person1_root_org_name = @kehu
and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=@tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
as b
on a.cr_date=b.cr_date
) AS t9
) AS A
UNION ALL
/* Avg_onsite_time 30天内创建的工单，每日平均上门时间 只计算有第一次上门时间的工单。(不统计删除工单) #单位是小时*/
SELECT
'<1:00',
--将总平均值转换为时间格式
(SELECT
CONVERT(VARCHAR(12), avg(A.Avg_onsite_time) /60/60 % 24) + ':'
+ CONVERT(VARCHAR(2), avg(A.Avg_onsite_time) /60 % 60) + ':'
+ CONVERT(VARCHAR(2), avg(A.Avg_onsite_time) % 60)
),
'Avg Onsite Time',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t8.d AS Avg_onsite_time,
case when datediff(dd, t8.cr_date, getdate()) =1 then t8.c else '' end as D1,
case when datediff(dd, t8.cr_date, getdate()) =2 then t8.c else '' end as D2,
case when datediff(dd, t8.cr_date, getdate()) =3 then t8.c else '' end as D3,
case when datediff(dd, t8.cr_date, getdate()) =4 then t8.c else '' end as D4,
case when datediff(dd, t8.cr_date, getdate()) =5 then t8.c else '' end as D5,
case when datediff(dd, t8.cr_date, getdate()) =6 then t8.c else '' end as D6,
case when datediff(dd, t8.cr_date, getdate()) =7 then t8.c else '' end as D7,
case when datediff(dd, t8.cr_date, getdate()) =8 then t8.c else '' end as D8,
case when datediff(dd, t8.cr_date, getdate()) =9 then t8.c else '' end as D9,
case when datediff(dd, t8.cr_date, getdate()) =10 then t8.c else '' end as D10,
case when datediff(dd, t8.cr_date, getdate()) =11 then t8.c else '' end as D11,
case when datediff(dd, t8.cr_date, getdate()) =12 then t8.c else '' end as D12,
case when datediff(dd, t8.cr_date, getdate()) =13 then t8.c else '' end as D13,
case when datediff(dd, t8.cr_date, getdate()) =14 then t8.c else '' end as D14,
case when datediff(dd, t8.cr_date, getdate()) =15 then t8.c else '' end as D15,
case when datediff(dd, t8.cr_date, getdate()) =16 then t8.c else '' end as D16,
case when datediff(dd, t8.cr_date, getdate()) =17 then t8.c else '' end as D17,
case when datediff(dd, t8.cr_date, getdate()) =18 then t8.c else '' end as D18,
case when datediff(dd, t8.cr_date, getdate()) =19 then t8.c else '' end as D19,
case when datediff(dd, t8.cr_date, getdate()) =20 then t8.c else '' end as D20,
case when datediff(dd, t8.cr_date, getdate()) =21 then t8.c else '' end as D21,
case when datediff(dd, t8.cr_date, getdate()) =22 then t8.c else '' end as D22,
case when datediff(dd, t8.cr_date, getdate()) =23 then t8.c else '' end as D23,
case when datediff(dd, t8.cr_date, getdate()) =24 then t8.c else '' end as D24,
case when datediff(dd, t8.cr_date, getdate()) =25 then t8.c else '' end as D25,
case when datediff(dd, t8.cr_date, getdate()) =26 then t8.c else '' end as D26,
case when datediff(dd, t8.cr_date, getdate()) =27 then t8.c else '' end as D27,
case when datediff(dd, t8.cr_date, getdate()) =28 then t8.c else '' end as D28,
case when datediff(dd, t8.cr_date, getdate()) =29 then t8.c else '' end as D29,
case when datediff(dd, t8.cr_date, getdate()) =30 then t8.c else '' end as D30
FROM (
SELECT
b.cr_date,
--计算总平均值
AVG(b.zd) d,
--将日平均值转时间格式
(
SELECT
CONVERT(VARCHAR(12), avg(b.zd) /60/60 % 24) + ':'
+ CONVERT(VARCHAR(2), avg(b.zd) /60 % 60) + ':'
+ CONVERT(VARCHAR(2), avg(b.zd) % 60)
) c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
--如果上门时间早于创建时间，赋NULL。如果晚于创建时间，计算差值
CASE WHEN(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) > DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')
THEN
DATEDIFF(
ss,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
)
)
ELSE
NULL
END AS zd
--判断到此结束
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) is not null
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) b
GROUP BY
b.cr_date
) AS t8
) AS A
UNION ALL
/*No_onsite_time 30天内创建的工单，统计每天派给了硬件ccti=hw但是没有第一次上门时间的。(不统计删除工单)*/
SELECT
'=0',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'No Onsite Time',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t11.c AS No_onsite_time,
case when datediff(dd, t11.cr_date, getdate()) =1 then t11.c else '' end as D1,
case when datediff(dd, t11.cr_date, getdate()) =2 then t11.c else '' end as D2,
case when datediff(dd, t11.cr_date, getdate()) =3 then t11.c else '' end as D3,
case when datediff(dd, t11.cr_date, getdate()) =4 then t11.c else '' end as D4,
case when datediff(dd, t11.cr_date, getdate()) =5 then t11.c else '' end as D5,
case when datediff(dd, t11.cr_date, getdate()) =6 then t11.c else '' end as D6,
case when datediff(dd, t11.cr_date, getdate()) =7 then t11.c else '' end as D7,
case when datediff(dd, t11.cr_date, getdate()) =8 then t11.c else '' end as D8,
case when datediff(dd, t11.cr_date, getdate()) =9 then t11.c else '' end as D9,
case when datediff(dd, t11.cr_date, getdate()) =10 then t11.c else '' end as D10,
case when datediff(dd, t11.cr_date, getdate()) =11 then t11.c else '' end as D11,
case when datediff(dd, t11.cr_date, getdate()) =12 then t11.c else '' end as D12,
case when datediff(dd, t11.cr_date, getdate()) =13 then t11.c else '' end as D13,
case when datediff(dd, t11.cr_date, getdate()) =14 then t11.c else '' end as D14,
case when datediff(dd, t11.cr_date, getdate()) =15 then t11.c else '' end as D15,
case when datediff(dd, t11.cr_date, getdate()) =16 then t11.c else '' end as D16,
case when datediff(dd, t11.cr_date, getdate()) =17 then t11.c else '' end as D17,
case when datediff(dd, t11.cr_date, getdate()) =18 then t11.c else '' end as D18,
case when datediff(dd, t11.cr_date, getdate()) =19 then t11.c else '' end as D19,
case when datediff(dd, t11.cr_date, getdate()) =20 then t11.c else '' end as D20,
case when datediff(dd, t11.cr_date, getdate()) =21 then t11.c else '' end as D21,
case when datediff(dd, t11.cr_date, getdate()) =22 then t11.c else '' end as D22,
case when datediff(dd, t11.cr_date, getdate()) =23 then t11.c else '' end as D23,
case when datediff(dd, t11.cr_date, getdate()) =24 then t11.c else '' end as D24,
case when datediff(dd, t11.cr_date, getdate()) =25 then t11.c else '' end as D25,
case when datediff(dd, t11.cr_date, getdate()) =26 then t11.c else '' end as D26,
case when datediff(dd, t11.cr_date, getdate()) =27 then t11.c else '' end as D27,
case when datediff(dd, t11.cr_date, getdate()) =28 then t11.c else '' end as D28,
case when datediff(dd, t11.cr_date, getdate()) =29 then t11.c else '' end as D29,
case when datediff(dd, t11.cr_date, getdate()) =30 then t11.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) is null
--AND CCTI_CLASS=@CCTI_CLASS
AND closed_by_group_name IN ('@group')
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t11
) AS A
UNION ALL
SELECT
/*Onsite>1 30天内创建的，统计有第一次和第二次上门时间的工单数量(上门大于一次)(不统计删除工单) */
'=0',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Onsite>1',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t12.c AS Onsite_1,
case when datediff(dd, t12.cr_date, getdate()) =1 then t12.c else '' end as D1,
case when datediff(dd, t12.cr_date, getdate()) =2 then t12.c else '' end as D2,
case when datediff(dd, t12.cr_date, getdate()) =3 then t12.c else '' end as D3,
case when datediff(dd, t12.cr_date, getdate()) =4 then t12.c else '' end as D4,
case when datediff(dd, t12.cr_date, getdate()) =5 then t12.c else '' end as D5,
case when datediff(dd, t12.cr_date, getdate()) =6 then t12.c else '' end as D6,
case when datediff(dd, t12.cr_date, getdate()) =7 then t12.c else '' end as D7,
case when datediff(dd, t12.cr_date, getdate()) =8 then t12.c else '' end as D8,
case when datediff(dd, t12.cr_date, getdate()) =9 then t12.c else '' end as D9,
case when datediff(dd, t12.cr_date, getdate()) =10 then t12.c else '' end as D10,
case when datediff(dd, t12.cr_date, getdate()) =11 then t12.c else '' end as D11,
case when datediff(dd, t12.cr_date, getdate()) =12 then t12.c else '' end as D12,
case when datediff(dd, t12.cr_date, getdate()) =13 then t12.c else '' end as D13,
case when datediff(dd, t12.cr_date, getdate()) =14 then t12.c else '' end as D14,
case when datediff(dd, t12.cr_date, getdate()) =15 then t12.c else '' end as D15,
case when datediff(dd, t12.cr_date, getdate()) =16 then t12.c else '' end as D16,
case when datediff(dd, t12.cr_date, getdate()) =17 then t12.c else '' end as D17,
case when datediff(dd, t12.cr_date, getdate()) =18 then t12.c else '' end as D18,
case when datediff(dd, t12.cr_date, getdate()) =19 then t12.c else '' end as D19,
case when datediff(dd, t12.cr_date, getdate()) =20 then t12.c else '' end as D20,
case when datediff(dd, t12.cr_date, getdate()) =21 then t12.c else '' end as D21,
case when datediff(dd, t12.cr_date, getdate()) =22 then t12.c else '' end as D22,
case when datediff(dd, t12.cr_date, getdate()) =23 then t12.c else '' end as D23,
case when datediff(dd, t12.cr_date, getdate()) =24 then t12.c else '' end as D24,
case when datediff(dd, t12.cr_date, getdate()) =25 then t12.c else '' end as D25,
case when datediff(dd, t12.cr_date, getdate()) =26 then t12.c else '' end as D26,
case when datediff(dd, t12.cr_date, getdate()) =27 then t12.c else '' end as D27,
case when datediff(dd, t12.cr_date, getdate()) =28 then t12.c else '' end as D28,
case when datediff(dd, t12.cr_date, getdate()) =29 then t12.c else '' end as D29,
case when datediff(dd, t12.cr_date, getdate()) =30 then t12.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=row_id
AND va.ATTR_ID=555
) is not null
AND (
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=row_id
AND va.ATTR_ID=558
) is not null
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t12
) AS A
'''
sql.py文件下半部分
下半部分定义了两个根据参数返回sql语句的函数，供views.py中的数据库查询类使用
def sql_zong(kehu='WTC', CCTI_CLASS=' ',sla='WTC-P1',quyu=''):
'''返回综合查询的拼接sql'''
sqa1 = f'''
/*
本SQL报表抓取ServiceAI MSSQL数据库，统计客户在30天内的KPI值：
Total Call 工单总数
Unclosed 未关单总数
Scheduled 预计解决数量
P1 call# P1紧急工单数量
Over SLA# 超SLA数量
SLA Met% SLA达成率
Worst TAT 当天完成跨度最长的工单所花的时间
Remote Fixed 远程解决率
Max late close 系统操作关闭时间-实际关闭时间之间的差值，该栏位列出最大的差值所花的时间，ServiceAI没有实际关单时间，可不统计。
Repeat Call# 重复Call数量，ServiceAI没有该计算值，暂不统计。
Avg onsite time 平均上门时间
No Onsite time 没有上门时间的数量
Onsite# > 1 上门次数大于1次的数量
*/
/*@kehu 定义报表统计的客户*/
--declare @kehu VARCHAR(20)
declare @kehu nvarchar(2000)
set @kehu =N'{kehu}'
/*@CCTI_CLASS 定义报表中ccti的类型。为空，即统计所有CCTI。同时表标题列的范围，也会根据该值自动修改*/
declare @CCTI_CLASS nvarchar(2000)
set @CCTI_CLASS=N'{CCTI_CLASS}'
/*@district 定义报表统计的区域。为空，即统计该客户所有区域*/
-- declare @district nvarchar(2000)
-- set @district=''
/*@tianshu 定义报表统计的天数，目前由于格式，只能是30天*/
declare @tianshu int
set @tianshu=30
/*@sla_target_name 定义查询工单的SLA*/
declare @sla_target_name nvarchar(2000)
set @sla_target_name=N'{sla}'
/*@group 定义No Onsite time中没有产生上门时间的硬件组的名称*/
declare @group nvarchar(2000)
/*@org_name 定义要查询的客户区域*/
declare @org_name nvarchar(2000)
set @org_name=N'{quyu}'
'''
sqa2 = r"set @group=N'''L1-HW-SH'',''L1-HW-BJ'',''L1-HW-GZ'',''L1-HW-SZ'''"
zonghe = sqa1 + sqa2 + sqa3
return zonghe
'''
下面是
一，UNCLOSED 某个客户所有未关单
二，YESTERDAY NEW 某个客户昨天创建的所有工单
三，YESTERDAY CLOSED 客户昨天关闭的所有工单
的sql语句
'''
kehu='WTC'
#一，UNCLOSED 客户所有未关单
sqa4=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
ticket_details +' ' + description_long +' ' + last_worklog as 'Updates',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted')
'''
#二，YESTERDAY NEW 客户昨天创建的所有工单
sqa5=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_group_name as 'Queue',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
last_worklog as 'Last Update Description',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution',
DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00') AS 'Created',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') AS 'Close_Time',
CCTI_CLASS+':'+CCTI_CATEGORY+':'+CCTI_TYPE+':'+CCTI_TYPE AS 'Service',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as 'Contact Person',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as 'Telephone'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}'
and TICKET_STATUS not in ('Request - Delete','archive','Approved','Submitted')
and CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)= convert(varchar(10),dateadd(DD,-1,getdate()),120)
'''
#三，YESTERDAY CLOSED 客户昨天关闭的所有工单
sqa6=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_group_name as 'Queue',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
last_worklog as 'Last Update Description',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution',
DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00') AS 'Created',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') AS 'Close_Time',
CCTI_CLASS+':'+CCTI_CATEGORY+':'+CCTI_TYPE+':'+CCTI_TYPE AS 'Service',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as 'Contact Person',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as 'Telephone'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}'
and TICKET_STATUS not in ('Request - Delete','archive','Approved','Submitted')
and CONVERT(VARCHAR(10),(DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')),120)= convert(varchar(10),dateadd(DD,-1,getdate()),120)
'''
def sql_zong2(i):
'''给调用函数返回 三条sql语句
一，UNCLOSED 某个客户所有未关单
二，YESTERDAY NEW 某个客户昨天创建的所有工单
三，YESTERDAY CLOSED 客户昨天关闭的所有工单
'''
i2=int(i)
if i2==1:
return sqa4
elif i2==2:
return sqa5
elif i2==3:
return sqa6
else:
print('你输入的数字不对，没有你要查询的sql')
views.py中的数据库查询类
数据库查询类将查询到的结果，通过pandas处理一下，然后取出保存到列表和变量里面，最后通过locals函数将变量作为字典返回给类对象调用，通过字典取值的方式，需要哪些数据就选取哪些数据，避免了多个数据库查询类的编写和维护，缺点是运行时是sql全部运行比较耗费时间
# 定义数据库查询类
class Sql_chaxun2():
'''初始化sqlserver 连接属性'''
def __init__(self):
self.servername = '888.888.888.888' # 服务器名称
self.username = '你猜' # 账户
self.port = '23350' # 端口号
self.password = '你猜' # 密码
self.dabasename = '你猜' #数据库名称
self.get_sql = [] # 获取8条拼接sql列表
self.sqlj = [] # 获取8条sql执行结果列表
self.ccti_list =[' ','HW','SW/PC01','SW/PC02', 'SW/POS','SW/手持','SW/SCO','NW']
self.quyu=['东区','西区','南区','北区']
def chaxun(self):
'''执行all查询'''
con = pymssql.connect(server=self.servername, user=self.username, password=self.password,database=self.dabasename, port=self.port, charset='utf8')
print('sqlserver 连接成功')
r5 = [] # 应用于写入excel 存储dataframe数据的列表
q = 1
for i in self.ccti_list:
print(i)
r4 = []
i2 = sql_zong(CCTI_CLASS=f'{i}')
d = pd.read_sql(i2,con)
print(2)
d = d.fillna(0) # 将空值填充为0
d = d.replace('100.00%', '100%') # 替换值
#将每一个dataframe中的数据取出来保存到一个列表中
r5.append(d) #应用于写入excel 将每一个dataframe数据存储到列表中
for ii2 in range(len(d)):
r4.append(d.loc[ii2].tolist())
#将每一个列表存储到一个总列表中
self.sqlj.append(r4)
print(f'第{q}句sql执行成功')
q += 1
# 添加新需求 给excel增加3个工作簿
# 1， UNCLOSED 某个客户所有未关单 2， YESTERDAY NEW 某个客户昨天创建的所有工单 3，YESTERDAY CLOSED 客户昨天关闭的所有工单
r6=[]
for i in range(1,4):
print(i)
i3=sql_zong2(i)
d2=pd.read_sql(i3,con)
d2 = d2.fillna(' ') # 将空值填充为空字符串空格
d2 = d2.replace('100.00%', '100%') # 替换值
r6.append(d2)
print(f'第{q}句sql执行成功')
q += 1
# 添加新需求 给excel增加屈臣氏的 东区 南区 西区 北区
r8=[]
for i in self.quyu:
r7=[]
for j in self.ccti_list:
i4=sql_zong(CCTI_CLASS=f'{j}',quyu=f'{i}')
d3=pd.read_sql(i4,con)
d3 = d3.fillna(0) # 将空值填充为空字符串空格
d3 = d3.replace('100.00%', '100%') # 替换值
r7.append(d3)
print(f'第{q}句sql执行成功')
q += 1
r8.append(r7)
con.commit() # 提交对数据库的操作
con.close() # 关闭数据库
print('关闭数据库成功')
jieguo=self.sqlj #将总列表保存给变量 用于locals传输
#return self.sqlj 如果只是为了反回给html数据只需要将self.sqlj 返回给调用函数
#但是新需求需要将数据写入excel 这就需要使用locals来返回当前函数的所有局部变量
#以字典的形式返回给调用函数， 调用函数去除值得时候需要用字典取值得方式
return locals()
views.py中定义的查询数据库时候使用的变量，通过变量的修改来达到不同的sql查询条件的目的
'''-------------------------------------------------变量说明-----------------------------------------------'''
#ccti_list 定义查询的ccti 在views.py 文件中70行中的列表修改
#quyu 定义查询客户的区域 在views.py文件中的71行中修改
report_name='Daily_Report_WTC' #邮件主题 文件名称 图表名称
dircname='/docker/nginxWebUI/html' # 文件夹变量 格式：/docker/nginxWebUI/html
murl='https://172.16.64.121' #域名变量 格式： https://172.16.64.121
Recipient=['2569449660@qq.com', 'bill.wang@value-exch.com'] #接收人变量'Figo.FEI@value-exch.com'
#注意客户变量需要修改两次
#kehu 客户变量在sql.py 第973行 作用修改sql查询中筛选的客户
#kehu 客户变量需要修改两次 sql.py 第1032行
email_text='''
<strong>DR负责人: <span style="color:#ffffff"><span style="background-color:#e74c3c">Kame.Lu</span></span> (日常运作/内容变更/停止发送) Backup：<span style="color:#ffffff"><span style="background-color:#e67e22">????</span></span><br />
<br />
<strong><u><span style="font-size:16px">Sheet Description</span></u></strong><br />
<strong><span style="color:#ffffff"><span style="background-color:#ff0000">Customer Name</span></span></strong> 客户\区域过去31天服务统计 (Customer\District KPI statistics of last 31 days)<br />
<br />
<strong><u><span style="font-size:16px">KPI Description</span></u></strong><br />
<strong>SLA Met%</strong> : 已关工单SLA达成率 (SLA achieved% of closed tickets)<br />
<strong>Worst TAT</strong> (Turn Around Time) : 已关工单，处理时间最长的 (the longest repair time of tickets fixed)<br />
<strong>First Call Fixed</strong> : 首次上门故障解决率<br />
<strong>Remote Fixed</strong>: 远程解决率<br />
<strong>Avg onsite time</strong> : 平均上门所花费的时间<br />
<strong>NoOnsiteTime</strong>: 没有填写过上门信息的数量<br />
<strong>Onsite# >1</strong>: 上门次数大于1次的数量</strong><br />
'''
web界面的html报表
通过访问具体的url来调用视图函数返回页面报表
#wtc 报表2 横着过来
def baobiao2(request):
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
a = Sql_chaxun2()
d = a.chaxun() #将locals返回的值赋值给变量
c=d['jieguo'] #取出字典中的值
name=report_name #表名称
return render(request,'baobiao2.html',locals())
将html报表存储为html文件，通过邮件方式发送给指定客户
#本视图函数是为了渲染页面的时候直接得时候保存为html文件发送给固定人员
def baobiao2_sendmail(request):
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
a = Sql_chaxun2()
c = a.chaxun()
name = report_name # 表名称
html=render(request,'baobiao2.html',locals())
html2=html.content.decode()#bytes类型转为str
print(type(html2))
# 下面是保存网页并通过邮件发送的方式
from django.core import mail # 导入发送邮件的模块
from email.mime.text import MIMEText # html格式和文本格式邮件
from django.core.mail import send_mail, EmailMultiAlternatives
from email.header import make_header
from django.core.mail import send_mail, EmailMultiAlternatives
'''将网页文件写入文本'''
# 获取当前时间 时/分/秒
import datetime
time = (str(datetime.datetime.now()).split(' ')[0])
#根据操作系统来使用不同的写入文件方案
import sys
system = sys.platform
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# windows 将网页写入文件中
# 这个是写入django项目静态文件夹中
try:
with open(f'./static/mail/Daily_Report_WTC-ALL-{time}.html', 'wt', encoding='UTF-8') as p:
p.write(html2)
print('写入成功')
except:
print('文件写入失败')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux中的docker 文件夹中写入文件
try:
with open(f'{dircname}/{report_name}-{time}.html', 'wt', encoding='UTF-8') as p:
p.write(html2)
print('写入成功')
except:
print('文件写入失败')
'''开始生成emal邮件'''
subject = f'{report_name}' # 邮件主题
# message = 'bill.ceshi' #邮件内容
email_content=f'''
{email_text}
<strong>{murl}/{report_name}-{time}.html</strong>
'''
text_content ='-------------'
from_email = 'serviceai@zh.value-exch.com.cn' # 发件人
# 'Figo.FEI@value-exch.com'
recipient_list = Recipient # 收件人
html_content = email_content
#html_message=aw
msg = EmailMultiAlternatives(subject,text_content,from_email, recipient_list)
msg.attach_alternative(html_content, "text/html")
'''添加html附件之后发送'''
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# Windows发送
msg.attach_file(f'./static/mail/Daily_Report_WTC-ALL-{time}.html')
print('windows系统附件加载完成')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux发送
msg.attach_file(f'{dircname}/{report_name}-{time}.html')
print("linux系统中附件加载完成")
try:
msg.send()
return HttpResponse('这个是baobiao2_sendmail函数发送的邮件')
except:
return HttpResponse('邮件发送失败，请联系bill.wang@value-exch.com')
重点来了
将报表写入excel并发送给指定用户
因为sql语句是使用pandas直接读取出来的，所以每一个sql查询的结构都是dataframe对象，设置一个函数给每一个dataframe对象设置写入excel的样式，该函数在写入excel时被频繁调用
#设置函数设置excel表格样式
def styleframe(ceshi1,sl):
'''定义函数设置excel表格格式'''
# 第一步给dataframe重新设置列索引
import numpy as np
if sl==33:
np1 = (np.arange(33)).tolist()
ceshi2 = ceshi1.set_axis(np1, axis=1) # 重新设置列索引
elif sl==10:
#np1 = (np.arange(9)).tolist()
ceshi2 = ceshi1 # 重新设置列索引
elif sl==16:
#np1 = (np.arange(16)).tolist()
ceshi2 = ceshi1 # 重新设置列索引
# 第二步 设置
sf = StyleFrame(ceshi2) # 新建一个StyleFrame供接下来使用，新建StyleFrame使用的方法与新建DataFrame用的一样
if sl==33:
#style 指的是指定那些列设置什么样式
# 设置除了第三列的样式之外的样式
style=[0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
30, 31, 32]
#style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2=[2,5]
elif sl==10:
#针对UNCLOSED 做判断
# style 指的是指定那些列设置什么样式
style = ['Pri', 'Over Sla', 'Ticket#', 'State', 'Cust_Name', 'Store', 'Owner', 'Problem Description', 'Updates','Solution']
# style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2 = []
style3=1
elif sl==16:
#针对 YESTERDAY NEW YESTERDAY CLOSED 做判断
# style 指的是指定那些列设置什么样式
style = ['Pri', 'Over Sla', 'Ticket#', 'State', 'Cust_Name', 'Store', 'Queue', 'Owner', 'Problem Description','Last Update Description','Solution','Created', 'Close_Time', 'Service', 'Contact Person', 'Telephone']
# style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2 = []
style3=2
#判断style2列表是否为空，如果为空 意思就是不给具体的列设置值
if style2:
print('d不是空的列表')
# 设置除了第三列的样式之外的样式
# style = [0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
# 30, 31, 32]
#有底色
# sf.apply_column_style(cols_to_style=style,
# styler_obj=Styler(font_color='black', bg_color='#C0C0C0', font_size=8, bold=True,
# border_type='thin'), style_header=True)
#无底色
sf.apply_column_style(cols_to_style=style,
styler_obj=Styler(font_color='black', font_size=8, bold=True,
border_type='thin'), style_header=True)
# 设置第3列的样式
# ss=style2[0]
# ss2=style2[1]
sf.apply_column_style(cols_to_style=[2],
styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
# 设置第五行的样式
sf.apply_style_by_indexes(indexes_to_style=[5],
styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
elif not style2:
# 针对 YESTERDAY NEW YESTERDAY CLOSED 做判断
# 设置所有列样式
# 1，取消所有列自动换行wrap_text=False
sf.apply_column_style(cols_to_style=style,
styler_obj=Styler(font_color='black', font_size=8, bold=True,
border_type='thin',wrap_text=False,number_format='DD/MM/YY HH:MM'), style_header=True)
print('d是空的列表,设置标题的样式')
# 设置YESTERDAY NEW YESTERDAY CLOSED标题的样式
sf.apply_headers_style(styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
# 设置 YESTERDAY NEW YESTERDAY CLOSED 表格宽度
sf.set_column_width(columns=style,width=10)
return sf
视图函数执行excel写入并发送的功能
#excel写入文件并发送
# wtc 报表2 横着过来
def baobiao3(request):
#django邮件发送模块
from django.core.mail import send_mail, EmailMultiAlternatives
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
# 获取当前时间 时/分/秒
import datetime
time = (str(datetime.datetime.now()).split(' ')[0])
a = Sql_chaxun2() #实例化类对象
d = a.chaxun() # 将locals返回的值赋值给变量
c = d['r5'] # 取出字典中的值 代表all所有区域
c1=d['r8'][0] # 取出字典中的值 代表'东区'
c2 = d['r8'][1] # 取出字典中的值 代表'西区'
c3 = d['r8'][2] # 取出字典中的值 代表'南区'
c4 = d['r8'][3] # 取出字典中的值 代表'北区'
b=d['r6']
'''
通过列表c索引取出来的每一个都是一个dataframe数据，之后调用styleframe函数设置每一个dataframe的样式
每一个dataframe对应一个工作簿中的一个excel块
'''
#ALL
sf1 = styleframe(c[0],33) #调用styleframe函数设置每一个dataframe样式
sf2 = styleframe(c[1],33)
sf3 = styleframe(c[2],33)
sf4 = styleframe(c[3],33)
sf5 = styleframe(c[4],33)
sf6 = styleframe(c[5],33)
sf7 = styleframe(c[6],33)
sf8 = styleframe(c[7],33)
#['东区',]
sf12 = styleframe(c1[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf13 = styleframe(c1[1], 33)
sf14 = styleframe(c1[2], 33)
sf15 = styleframe(c1[3], 33)
sf16 = styleframe(c1[4], 33)
sf17 = styleframe(c1[5], 33)
sf18 = styleframe(c1[6], 33)
sf19 = styleframe(c1[7], 33)
# ['西区',]
sf20 = styleframe(c2[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf21 = styleframe(c2[1], 33)
sf22 = styleframe(c2[2], 33)
sf23 = styleframe(c2[3], 33)
sf24 = styleframe(c2[4], 33)
sf25 = styleframe(c2[5], 33)
sf26 = styleframe(c2[6], 33)
sf27 = styleframe(c2[7], 33)
# ['南区',]
sf28 = styleframe(c3[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf29 = styleframe(c3[1], 33)
sf30 = styleframe(c3[2], 33)
sf31 = styleframe(c3[3], 33)
sf32 = styleframe(c3[4], 33)
sf33 = styleframe(c3[5], 33)
sf34 = styleframe(c3[6], 33)
sf35 = styleframe(c3[7], 33)
# ['北区']
sf36 = styleframe(c4[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf37 = styleframe(c4[1], 33)
sf38 = styleframe(c4[2], 33)
sf39 = styleframe(c4[3], 33)
sf40 = styleframe(c4[4], 33)
sf41 = styleframe(c4[5], 33)
sf42 = styleframe(c4[6], 33)
sf43 = styleframe(c4[7], 33)
#UNCLOSED
sf9=styleframe(b[0],10)
#YESTERDAY NEW
sf10 = styleframe(b[1],16)
#YESTERDAY CLOSED
sf11 = styleframe(b[2],16)
# 写入excel代码
#第一步：根据操作系统自动识别路径
import sys
system = sys.platform
if system == 'win32':
print('当前系统为windows，启用Windows方案')
ew1 = StyleFrame.ExcelWriter(rf'D:\jiaoben\{report_name}-{time}.xlsx', index=False)
lujing=f'D:\jiaoben\{report_name}-{time}.xlsx' #取出路径方便发送时使用
print('Windows 系统excel 写入路径指定成功')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
ew1 = StyleFrame.ExcelWriter(rf'{dircname}/{report_name}-{time}.xlsx') #linux中不能使用 index=False 否则会报错
lujing = f'{dircname}/{report_name}-{time}.xlsx' #取出路径方便发送时使用
print('linux 系统excel 写入路径指定成功')
#第二步：将文件对象赋值给ew
#all
ew = ew1
sf1.to_excel(ew, sheet_name='All', index=False, header=False, startrow=0)
sf2.to_excel(ew, sheet_name='All', index=False, header=False, startrow=14)
sf3.to_excel(ew, sheet_name='All', index=False, header=False, startrow=28)
sf4.to_excel(ew, sheet_name='All', index=False, header=False, startrow=42)
sf5.to_excel(ew, sheet_name='All', index=False, header=False, startrow=56)
sf6.to_excel(ew, sheet_name='All', index=False, header=False, startrow=70)
sf7.to_excel(ew, sheet_name='All', index=False, header=False, startrow=84)
sf8.to_excel(ew, sheet_name='All', index=False, header=False, startrow=98)
# ['东区',]
sf12.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=0)
sf13.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=14)
sf14.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=28)
sf15.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=42)
sf16.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=56)
sf17.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=70)
sf18.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=84)
sf19.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=98)
# ['南区',]
sf28.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=0)
sf29.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=14)
sf30.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=28)
sf31.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=42)
sf32.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=56)
sf33.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=70)
sf34.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=84)
sf35.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=98)
# ['西区',]
sf20.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=0)
sf21.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=14)
sf22.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=28)
sf23.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=42)
sf24.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=56)
sf25.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=70)
sf26.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=84)
sf27.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=98)
# ['北区']
sf36.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=0)
sf37.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=14)
sf38.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=28)
sf39.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=42)
sf40.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=56)
sf41.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=70)
sf42.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=84)
sf43.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=98)
#多个工作簿测试使用
# UNCLOSED
sf9.to_excel(ew, sheet_name='UNCLOSED', index=False, header=True, startrow=0)
# YESTERDAY NEW
sf10.to_excel(ew,sheet_name='Yesterday NEW', index=False, header=True,startrow=0)
# YESTERDAY CLOSED
sf11.to_excel(ew, sheet_name='Yesterday CLOSED', index=False, header=True, startrow=0)
ew.save() # 写入文件
print('excel写入成功')
#将excel发送代码
'''开始生成emal邮件'''
subject = f'{report_name}' # 邮件主题
# message = 'bill.ceshi' #邮件内容
email_content = f'''
{email_text}
<strong>{murl}/{report_name}-{time}.xlsx</strong>
'''
text_content = '-------------'
from_email = 'serviceai@zh.value-exch.com.cn' # 发件人
# 'Figo.FEI@value-exch.com'
recipient_list = Recipient # 收件人
html_content = email_content
# html_message=aw
msg = EmailMultiAlternatives(subject, text_content, from_email, recipient_list)
msg.attach_alternative(html_content, "text/html")
'''添加html附件之后发送'''
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# Windows发送
msg.attach_file(lujing)
print('windows系统附件加载完成')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux发送
msg.attach_file(lujing)
print("linux系统中附件加载完成")
try:
msg.send()
return HttpResponse('这个是baobiao3 函数发送的邮件')
except:
return HttpResponse('邮件发送失败，请联系bill.wang@value-exch.com')
项目部分源代码
demo2/sql.py
global sqa3
sqa3 = '''
/*Total Call 30天内的每天创建的工单数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Total Call' KPI,
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t1.c AS Total_Call,
case when datediff(dd, t1.cr_date, getdate()) =1 then t1.c else '' end as D1,
case when datediff(dd, t1.cr_date, getdate()) =2 then t1.c else '' end as D2,
case when datediff(dd, t1.cr_date, getdate()) =3 then t1.c else '' end as D3,
case when datediff(dd, t1.cr_date, getdate()) =4 then t1.c else '' end as D4,
case when datediff(dd, t1.cr_date, getdate()) =5 then t1.c else '' end as D5,
case when datediff(dd, t1.cr_date, getdate()) =6 then t1.c else '' end as D6,
case when datediff(dd, t1.cr_date, getdate()) =7 then t1.c else '' end as D7,
case when datediff(dd, t1.cr_date, getdate()) =8 then t1.c else '' end as D8,
case when datediff(dd, t1.cr_date, getdate()) =9 then t1.c else '' end as D9,
case when datediff(dd, t1.cr_date, getdate()) =10 then t1.c else '' end as D10,
case when datediff(dd, t1.cr_date, getdate()) =11 then t1.c else '' end as D11,
case when datediff(dd, t1.cr_date, getdate()) =12 then t1.c else '' end as D12,
case when datediff(dd, t1.cr_date, getdate()) =13 then t1.c else '' end as D13,
case when datediff(dd, t1.cr_date, getdate()) =14 then t1.c else '' end as D14,
case when datediff(dd, t1.cr_date, getdate()) =15 then t1.c else '' end as D15,
case when datediff(dd, t1.cr_date, getdate()) =16 then t1.c else '' end as D16,
case when datediff(dd, t1.cr_date, getdate()) =17 then t1.c else '' end as D17,
case when datediff(dd, t1.cr_date, getdate()) =18 then t1.c else '' end as D18,
case when datediff(dd, t1.cr_date, getdate()) =19 then t1.c else '' end as D19,
case when datediff(dd, t1.cr_date, getdate()) =20 then t1.c else '' end as D20,
case when datediff(dd, t1.cr_date, getdate()) =21 then t1.c else '' end as D21,
case when datediff(dd, t1.cr_date, getdate()) =22 then t1.c else '' end as D22,
case when datediff(dd, t1.cr_date, getdate()) =23 then t1.c else '' end as D23,
case when datediff(dd, t1.cr_date, getdate()) =24 then t1.c else '' end as D24,
case when datediff(dd, t1.cr_date, getdate()) =25 then t1.c else '' end as D25,
case when datediff(dd, t1.cr_date, getdate()) =26 then t1.c else '' end as D26,
case when datediff(dd, t1.cr_date, getdate()) =27 then t1.c else '' end as D27,
case when datediff(dd, t1.cr_date, getdate()) =28 then t1.c else '' end as D28,
case when datediff(dd, t1.cr_date, getdate()) =29 then t1.c else '' end as D29,
case when datediff(dd, t1.cr_date, getdate()) =30 then t1.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t1
) AS A
UNION ALL
/*Unclosed 30天内的每天创建后，状态未关闭的工单数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Unclosed',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t2.c AS Unclosed,
case when datediff(dd, t2.cr_date, getdate()) =1 then t2.c else '' end as D1,
case when datediff(dd, t2.cr_date, getdate()) =2 then t2.c else '' end as D2,
case when datediff(dd, t2.cr_date, getdate()) =3 then t2.c else '' end as D3,
case when datediff(dd, t2.cr_date, getdate()) =4 then t2.c else '' end as D4,
case when datediff(dd, t2.cr_date, getdate()) =5 then t2.c else '' end as D5,
case when datediff(dd, t2.cr_date, getdate()) =6 then t2.c else '' end as D6,
case when datediff(dd, t2.cr_date, getdate()) =7 then t2.c else '' end as D7,
case when datediff(dd, t2.cr_date, getdate()) =8 then t2.c else '' end as D8,
case when datediff(dd, t2.cr_date, getdate()) =9 then t2.c else '' end as D9,
case when datediff(dd, t2.cr_date, getdate()) =10 then t2.c else '' end as D10,
case when datediff(dd, t2.cr_date, getdate()) =11 then t2.c else '' end as D11,
case when datediff(dd, t2.cr_date, getdate()) =12 then t2.c else '' end as D12,
case when datediff(dd, t2.cr_date, getdate()) =13 then t2.c else '' end as D13,
case when datediff(dd, t2.cr_date, getdate()) =14 then t2.c else '' end as D14,
case when datediff(dd, t2.cr_date, getdate()) =15 then t2.c else '' end as D15,
case when datediff(dd, t2.cr_date, getdate()) =16 then t2.c else '' end as D16,
case when datediff(dd, t2.cr_date, getdate()) =17 then t2.c else '' end as D17,
case when datediff(dd, t2.cr_date, getdate()) =18 then t2.c else '' end as D18,
case when datediff(dd, t2.cr_date, getdate()) =19 then t2.c else '' end as D19,
case when datediff(dd, t2.cr_date, getdate()) =20 then t2.c else '' end as D20,
case when datediff(dd, t2.cr_date, getdate()) =21 then t2.c else '' end as D21,
case when datediff(dd, t2.cr_date, getdate()) =22 then t2.c else '' end as D22,
case when datediff(dd, t2.cr_date, getdate()) =23 then t2.c else '' end as D23,
case when datediff(dd, t2.cr_date, getdate()) =24 then t2.c else '' end as D24,
case when datediff(dd, t2.cr_date, getdate()) =25 then t2.c else '' end as D25,
case when datediff(dd, t2.cr_date, getdate()) =26 then t2.c else '' end as D26,
case when datediff(dd, t2.cr_date, getdate()) =27 then t2.c else '' end as D27,
case when datediff(dd, t2.cr_date, getdate()) =28 then t2.c else '' end as D28,
case when datediff(dd, t2.cr_date, getdate()) =29 then t2.c else '' end as D29,
case when datediff(dd, t2.cr_date, getdate()) =30 then t2.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t2
) AS A
UNION ALL
/*Scheduled 30天内，每天sla应到期工单数量且工单状态为未关*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Scheduled',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t3.c AS Scheduled,
case when datediff(dd, t3.cr_date, getdate()) =1 then t3.c else '' end as D1,
case when datediff(dd, t3.cr_date, getdate()) =2 then t3.c else '' end as D2,
case when datediff(dd, t3.cr_date, getdate()) =3 then t3.c else '' end as D3,
case when datediff(dd, t3.cr_date, getdate()) =4 then t3.c else '' end as D4,
case when datediff(dd, t3.cr_date, getdate()) =5 then t3.c else '' end as D5,
case when datediff(dd, t3.cr_date, getdate()) =6 then t3.c else '' end as D6,
case when datediff(dd, t3.cr_date, getdate()) =7 then t3.c else '' end as D7,
case when datediff(dd, t3.cr_date, getdate()) =8 then t3.c else '' end as D8,
case when datediff(dd, t3.cr_date, getdate()) =9 then t3.c else '' end as D9,
case when datediff(dd, t3.cr_date, getdate()) =10 then t3.c else '' end as D10,
case when datediff(dd, t3.cr_date, getdate()) =11 then t3.c else '' end as D11,
case when datediff(dd, t3.cr_date, getdate()) =12 then t3.c else '' end as D12,
case when datediff(dd, t3.cr_date, getdate()) =13 then t3.c else '' end as D13,
case when datediff(dd, t3.cr_date, getdate()) =14 then t3.c else '' end as D14,
case when datediff(dd, t3.cr_date, getdate()) =15 then t3.c else '' end as D15,
case when datediff(dd, t3.cr_date, getdate()) =16 then t3.c else '' end as D16,
case when datediff(dd, t3.cr_date, getdate()) =17 then t3.c else '' end as D17,
case when datediff(dd, t3.cr_date, getdate()) =18 then t3.c else '' end as D18,
case when datediff(dd, t3.cr_date, getdate()) =19 then t3.c else '' end as D19,
case when datediff(dd, t3.cr_date, getdate()) =20 then t3.c else '' end as D20,
case when datediff(dd, t3.cr_date, getdate()) =21 then t3.c else '' end as D21,
case when datediff(dd, t3.cr_date, getdate()) =22 then t3.c else '' end as D22,
case when datediff(dd, t3.cr_date, getdate()) =23 then t3.c else '' end as D23,
case when datediff(dd, t3.cr_date, getdate()) =24 then t3.c else '' end as D24,
case when datediff(dd, t3.cr_date, getdate()) =25 then t3.c else '' end as D25,
case when datediff(dd, t3.cr_date, getdate()) =26 then t3.c else '' end as D26,
case when datediff(dd, t3.cr_date, getdate()) =27 then t3.c else '' end as D27,
case when datediff(dd, t3.cr_date, getdate()) =28 then t3.c else '' end as D28,
case when datediff(dd, t3.cr_date, getdate()) =29 then t3.c else '' end as D29,
case when datediff(dd, t3.cr_date, getdate()) =30 then t3.c else '' end as D30
FROM (
SELECT
a.cr_date,
COUNT ( TICKET_ID ) c
FROM
(SELECT
CONVERT (
VARCHAR ( 10 ),DATEADD(
SECOND,
(
SELECT
top 1 sla_due_by
FROM
VSLA_AGREEMENT_COMPLIANCE_LIST_UX AS vc
WHERE
vc.item_id=vi.ROW_ID
ORDER BY
threshold_sort_order DESC
), '1970/1/1 08:00:00'
),120
) AS 'cr_date',
vi.TICKET_ID
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
person1_root_org_name = @kehu
AND TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted')
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) AS a
GROUP BY
a.cr_date
) AS t3
) AS A
UNION ALL
/* P1_call 30天内创建的工单，优先级最高的数量(不统计已删除工单)*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'P1 call',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t4.c AS P1_call,
case when datediff(dd, t4.cr_date, getdate()) =1 then t4.c else '' end as D1,
case when datediff(dd, t4.cr_date, getdate()) =2 then t4.c else '' end as D2,
case when datediff(dd, t4.cr_date, getdate()) =3 then t4.c else '' end as D3,
case when datediff(dd, t4.cr_date, getdate()) =4 then t4.c else '' end as D4,
case when datediff(dd, t4.cr_date, getdate()) =5 then t4.c else '' end as D5,
case when datediff(dd, t4.cr_date, getdate()) =6 then t4.c else '' end as D6,
case when datediff(dd, t4.cr_date, getdate()) =7 then t4.c else '' end as D7,
case when datediff(dd, t4.cr_date, getdate()) =8 then t4.c else '' end as D8,
case when datediff(dd, t4.cr_date, getdate()) =9 then t4.c else '' end as D9,
case when datediff(dd, t4.cr_date, getdate()) =10 then t4.c else '' end as D10,
case when datediff(dd, t4.cr_date, getdate()) =11 then t4.c else '' end as D11,
case when datediff(dd, t4.cr_date, getdate()) =12 then t4.c else '' end as D12,
case when datediff(dd, t4.cr_date, getdate()) =13 then t4.c else '' end as D13,
case when datediff(dd, t4.cr_date, getdate()) =14 then t4.c else '' end as D14,
case when datediff(dd, t4.cr_date, getdate()) =15 then t4.c else '' end as D15,
case when datediff(dd, t4.cr_date, getdate()) =16 then t4.c else '' end as D16,
case when datediff(dd, t4.cr_date, getdate()) =17 then t4.c else '' end as D17,
case when datediff(dd, t4.cr_date, getdate()) =18 then t4.c else '' end as D18,
case when datediff(dd, t4.cr_date, getdate()) =19 then t4.c else '' end as D19,
case when datediff(dd, t4.cr_date, getdate()) =20 then t4.c else '' end as D20,
case when datediff(dd, t4.cr_date, getdate()) =21 then t4.c else '' end as D21,
case when datediff(dd, t4.cr_date, getdate()) =22 then t4.c else '' end as D22,
case when datediff(dd, t4.cr_date, getdate()) =23 then t4.c else '' end as D23,
case when datediff(dd, t4.cr_date, getdate()) =24 then t4.c else '' end as D24,
case when datediff(dd, t4.cr_date, getdate()) =25 then t4.c else '' end as D25,
case when datediff(dd, t4.cr_date, getdate()) =26 then t4.c else '' end as D26,
case when datediff(dd, t4.cr_date, getdate()) =27 then t4.c else '' end as D27,
case when datediff(dd, t4.cr_date, getdate()) =28 then t4.c else '' end as D28,
case when datediff(dd, t4.cr_date, getdate()) =29 then t4.c else '' end as D29,
case when datediff(dd, t4.cr_date, getdate()) =30 then t4.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
count(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND sla_target_name=@sla_target_name
AND person1_root_org_name = @kehu
AND TICKET_STATUS not in ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t4
) AS A
UNION ALL
/*Over_SLA 30天创建的工单，状态已关闭，SLA已超时工单数量*/
SELECT
'—',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Over SLA',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t5.c AS Over_SLA,
case when datediff(dd, t5.cr_date, getdate()) =1 then t5.c else '' end as D1,
case when datediff(dd, t5.cr_date, getdate()) =2 then t5.c else '' end as D2,
case when datediff(dd, t5.cr_date, getdate()) =3 then t5.c else '' end as D3,
case when datediff(dd, t5.cr_date, getdate()) =4 then t5.c else '' end as D4,
case when datediff(dd, t5.cr_date, getdate()) =5 then t5.c else '' end as D5,
case when datediff(dd, t5.cr_date, getdate()) =6 then t5.c else '' end as D6,
case when datediff(dd, t5.cr_date, getdate()) =7 then t5.c else '' end as D7,
case when datediff(dd, t5.cr_date, getdate()) =8 then t5.c else '' end as D8,
case when datediff(dd, t5.cr_date, getdate()) =9 then t5.c else '' end as D9,
case when datediff(dd, t5.cr_date, getdate()) =10 then t5.c else '' end as D10,
case when datediff(dd, t5.cr_date, getdate()) =11 then t5.c else '' end as D11,
case when datediff(dd, t5.cr_date, getdate()) =12 then t5.c else '' end as D12,
case when datediff(dd, t5.cr_date, getdate()) =13 then t5.c else '' end as D13,
case when datediff(dd, t5.cr_date, getdate()) =14 then t5.c else '' end as D14,
case when datediff(dd, t5.cr_date, getdate()) =15 then t5.c else '' end as D15,
case when datediff(dd, t5.cr_date, getdate()) =16 then t5.c else '' end as D16,
case when datediff(dd, t5.cr_date, getdate()) =17 then t5.c else '' end as D17,
case when datediff(dd, t5.cr_date, getdate()) =18 then t5.c else '' end as D18,
case when datediff(dd, t5.cr_date, getdate()) =19 then t5.c else '' end as D19,
case when datediff(dd, t5.cr_date, getdate()) =20 then t5.c else '' end as D20,
case when datediff(dd, t5.cr_date, getdate()) =21 then t5.c else '' end as D21,
case when datediff(dd, t5.cr_date, getdate()) =22 then t5.c else '' end as D22,
case when datediff(dd, t5.cr_date, getdate()) =23 then t5.c else '' end as D23,
case when datediff(dd, t5.cr_date, getdate()) =24 then t5.c else '' end as D24,
case when datediff(dd, t5.cr_date, getdate()) =25 then t5.c else '' end as D25,
case when datediff(dd, t5.cr_date, getdate()) =26 then t5.c else '' end as D26,
case when datediff(dd, t5.cr_date, getdate()) =27 then t5.c else '' end as D27,
case when datediff(dd, t5.cr_date, getdate()) =28 then t5.c else '' end as D28,
case when datediff(dd, t5.cr_date, getdate()) =29 then t5.c else '' end as D29,
case when datediff(dd, t5.cr_date, getdate()) =30 then t5.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
count(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
sla_compliance_status_indicator='Breached SLA'
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t5
) AS A
UNION ALL
/*插入日期t6*/
SELECT
'Target',
--如果@CCTI_CLASS是空值，显示ALL，如果@CCTI_CLASS有值，@CCTI_CLASS值
CASE WHEN
@CCTI_CLASS=''
THEN
'ALL'
ELSE
@CCTI_CLASS
END,
--判断到此结束
'KPI',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
case when datediff(dd, t6.cr_date, getdate()) =1 then t6.c else '' end as D1,
case when datediff(dd, t6.cr_date, getdate()) =2 then t6.c else '' end as D2,
case when datediff(dd, t6.cr_date, getdate()) =3 then t6.c else '' end as D3,
case when datediff(dd, t6.cr_date, getdate()) =4 then t6.c else '' end as D4,
case when datediff(dd, t6.cr_date, getdate()) =5 then t6.c else '' end as D5,
case when datediff(dd, t6.cr_date, getdate()) =6 then t6.c else '' end as D6,
case when datediff(dd, t6.cr_date, getdate()) =7 then t6.c else '' end as D7,
case when datediff(dd, t6.cr_date, getdate()) =8 then t6.c else '' end as D8,
case when datediff(dd, t6.cr_date, getdate()) =9 then t6.c else '' end as D9,
case when datediff(dd, t6.cr_date, getdate()) =10 then t6.c else '' end as D10,
case when datediff(dd, t6.cr_date, getdate()) =11 then t6.c else '' end as D11,
case when datediff(dd, t6.cr_date, getdate()) =12 then t6.c else '' end as D12,
case when datediff(dd, t6.cr_date, getdate()) =13 then t6.c else '' end as D13,
case when datediff(dd, t6.cr_date, getdate()) =14 then t6.c else '' end as D14,
case when datediff(dd, t6.cr_date, getdate()) =15 then t6.c else '' end as D15,
case when datediff(dd, t6.cr_date, getdate()) =16 then t6.c else '' end as D16,
case when datediff(dd, t6.cr_date, getdate()) =17 then t6.c else '' end as D17,
case when datediff(dd, t6.cr_date, getdate()) =18 then t6.c else '' end as D18,
case when datediff(dd, t6.cr_date, getdate()) =19 then t6.c else '' end as D19,
case when datediff(dd, t6.cr_date, getdate()) =20 then t6.c else '' end as D20,
case when datediff(dd, t6.cr_date, getdate()) =21 then t6.c else '' end as D21,
case when datediff(dd, t6.cr_date, getdate()) =22 then t6.c else '' end as D22,
case when datediff(dd, t6.cr_date, getdate()) =23 then t6.c else '' end as D23,
case when datediff(dd, t6.cr_date, getdate()) =24 then t6.c else '' end as D24,
case when datediff(dd, t6.cr_date, getdate()) =25 then t6.c else '' end as D25,
case when datediff(dd, t6.cr_date, getdate()) =26 then t6.c else '' end as D26,
case when datediff(dd, t6.cr_date, getdate()) =27 then t6.c else '' end as D27,
case when datediff(dd, t6.cr_date, getdate()) =28 then t6.c else '' end as D28,
case when datediff(dd, t6.cr_date, getdate()) =29 then t6.c else '' end as D29,
case when datediff(dd, t6.cr_date, getdate()) =30 then t6.c else '' end as D30
FROM (
SELECT
t.cr_date,
datename(day,t.cr_date) c
FROM
(
SELECT
convert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_date
FROM
master..spt_values
WHERE
type = 'P'
AND number < @tianshu
) AS t
) AS t6
)AS A
UNION ALL
/*SLA_Met 最近30天创建的工单，SLA达成率，只计算关闭的工单。公式（达成SLA工单数量）/(总工单数量) */
SELECT
'>90%',
CAST(convert(decimal(16,2), CAST(sum(isnull(A.xjsl,0)) AS decimal(10,2)) *1.00*100 / CAST(sum(isnull(A.gdsl,0)) AS decimal(10,2))) AS varchar(50)) +'%',
'SLA Met',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t7.c AS SLA_Met,
t7.xjsl,
t7.gdsl,
case when datediff(dd, t7.cr_date, getdate()) =1 then t7.c else '' end as D1,
case when datediff(dd, t7.cr_date, getdate()) =2 then t7.c else '' end as D2,
case when datediff(dd, t7.cr_date, getdate()) =3 then t7.c else '' end as D3,
case when datediff(dd, t7.cr_date, getdate()) =4 then t7.c else '' end as D4,
case when datediff(dd, t7.cr_date, getdate()) =5 then t7.c else '' end as D5,
case when datediff(dd, t7.cr_date, getdate()) =6 then t7.c else '' end as D6,
case when datediff(dd, t7.cr_date, getdate()) =7 then t7.c else '' end as D7,
case when datediff(dd, t7.cr_date, getdate()) =8 then t7.c else '' end as D8,
case when datediff(dd, t7.cr_date, getdate()) =9 then t7.c else '' end as D9,
case when datediff(dd, t7.cr_date, getdate()) =10 then t7.c else '' end as D10,
case when datediff(dd, t7.cr_date, getdate()) =11 then t7.c else '' end as D11,
case when datediff(dd, t7.cr_date, getdate()) =12 then t7.c else '' end as D12,
case when datediff(dd, t7.cr_date, getdate()) =13 then t7.c else '' end as D13,
case when datediff(dd, t7.cr_date, getdate()) =14 then t7.c else '' end as D14,
case when datediff(dd, t7.cr_date, getdate()) =15 then t7.c else '' end as D15,
case when datediff(dd, t7.cr_date, getdate()) =16 then t7.c else '' end as D16,
case when datediff(dd, t7.cr_date, getdate()) =17 then t7.c else '' end as D17,
case when datediff(dd, t7.cr_date, getdate()) =18 then t7.c else '' end as D18,
case when datediff(dd, t7.cr_date, getdate()) =19 then t7.c else '' end as D19,
case when datediff(dd, t7.cr_date, getdate()) =20 then t7.c else '' end as D20,
case when datediff(dd, t7.cr_date, getdate()) =21 then t7.c else '' end as D21,
case when datediff(dd, t7.cr_date, getdate()) =22 then t7.c else '' end as D22,
case when datediff(dd, t7.cr_date, getdate()) =23 then t7.c else '' end as D23,
case when datediff(dd, t7.cr_date, getdate()) =24 then t7.c else '' end as D24,
case when datediff(dd, t7.cr_date, getdate()) =25 then t7.c else '' end as D25,
case when datediff(dd, t7.cr_date, getdate()) =26 then t7.c else '' end as D26,
case when datediff(dd, t7.cr_date, getdate()) =27 then t7.c else '' end as D27,
case when datediff(dd, t7.cr_date, getdate()) =28 then t7.c else '' end as D28,
case when datediff(dd, t7.cr_date, getdate()) =29 then t7.c else '' end as D29,
case when datediff(dd, t7.cr_date, getdate()) =30 then t7.c else '' end as D30
FROM (
SELECT
a.cr_date,
a.xjsl,
b.gdsl,
CAST(convert(decimal(16,2),CAST(a.xjsl*1.0*100 / b.gdsl AS decimal(10,2))) AS varchar(50)) +'%' c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) xjsl
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('closed','archive')
AND sla_compliance_status_indicator NOT IN ('Breached SLA','SLA Not Applied')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
AS a
join
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) gdsl
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND TICKET_STATUS IN ('closed','archive')
AND sla_compliance_status_indicator NOT IN ('SLA Not Applied')
AND person1_root_org_name = @kehu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
AS b
ON a.cr_date=b.cr_date
) AS t7
) AS A
UNION ALL
/* Worst_TAT 30天内创建的工单，处理工单所花费最长时间时间跨度的工单的时间值，关闭时间-创建时间(只计算关闭的工单)*/
SELECT
'<24:00',
MAX(A.Worst_TAT),
'Worst TAT',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t8.c AS Worst_TAT,
case when datediff(dd, t8.cr_date, getdate()) =1 then t8.c else '' end as D1,
case when datediff(dd, t8.cr_date, getdate()) =2 then t8.c else '' end as D2,
case when datediff(dd, t8.cr_date, getdate()) =3 then t8.c else '' end as D3,
case when datediff(dd, t8.cr_date, getdate()) =4 then t8.c else '' end as D4,
case when datediff(dd, t8.cr_date, getdate()) =5 then t8.c else '' end as D5,
case when datediff(dd, t8.cr_date, getdate()) =6 then t8.c else '' end as D6,
case when datediff(dd, t8.cr_date, getdate()) =7 then t8.c else '' end as D7,
case when datediff(dd, t8.cr_date, getdate()) =8 then t8.c else '' end as D8,
case when datediff(dd, t8.cr_date, getdate()) =9 then t8.c else '' end as D9,
case when datediff(dd, t8.cr_date, getdate()) =10 then t8.c else '' end as D10,
case when datediff(dd, t8.cr_date, getdate()) =11 then t8.c else '' end as D11,
case when datediff(dd, t8.cr_date, getdate()) =12 then t8.c else '' end as D12,
case when datediff(dd, t8.cr_date, getdate()) =13 then t8.c else '' end as D13,
case when datediff(dd, t8.cr_date, getdate()) =14 then t8.c else '' end as D14,
case when datediff(dd, t8.cr_date, getdate()) =15 then t8.c else '' end as D15,
case when datediff(dd, t8.cr_date, getdate()) =16 then t8.c else '' end as D16,
case when datediff(dd, t8.cr_date, getdate()) =17 then t8.c else '' end as D17,
case when datediff(dd, t8.cr_date, getdate()) =18 then t8.c else '' end as D18,
case when datediff(dd, t8.cr_date, getdate()) =19 then t8.c else '' end as D19,
case when datediff(dd, t8.cr_date, getdate()) =20 then t8.c else '' end as D20,
case when datediff(dd, t8.cr_date, getdate()) =21 then t8.c else '' end as D21,
case when datediff(dd, t8.cr_date, getdate()) =22 then t8.c else '' end as D22,
case when datediff(dd, t8.cr_date, getdate()) =23 then t8.c else '' end as D23,
case when datediff(dd, t8.cr_date, getdate()) =24 then t8.c else '' end as D24,
case when datediff(dd, t8.cr_date, getdate()) =25 then t8.c else '' end as D25,
case when datediff(dd, t8.cr_date, getdate()) =26 then t8.c else '' end as D26,
case when datediff(dd, t8.cr_date, getdate()) =27 then t8.c else '' end as D27,
case when datediff(dd, t8.cr_date, getdate()) =28 then t8.c else '' end as D28,
case when datediff(dd, t8.cr_date, getdate()) =29 then t8.c else '' end as D29,
case when datediff(dd, t8.cr_date, getdate()) =30 then t8.c else '' end as D30
FROM (
SELECT
b.cr_date,
max(b.zd) c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE,'1970/1/1 08:00:00') - DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),108) zd
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) b
GROUP BY
b.cr_date
) AS t8
) AS A
UNION ALL
/*Remote_Fixed 最近30天创建的工单，远程解决率，只计算关闭的工单。公式（第一次上门时间为空的工单数）/(总工单数量) */
SELECT
'>30%',
CAST(convert(decimal(16,2), CAST(sum(isnull(A.fengzi,0)) AS decimal(10,2)) *1.00*100 / CAST(sum(isnull(A.fengmu,0)) AS decimal(10,2))) AS varchar(50)) +'%',
'Remote Fixed',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t9.c AS Remote_Fixed,
t9.fengzi,
t9.fengmu,
case when datediff(dd, t9.cr_date, getdate()) =1 then t9.c else '' end as D1,
case when datediff(dd, t9.cr_date, getdate()) =2 then t9.c else '' end as D2,
case when datediff(dd, t9.cr_date, getdate()) =3 then t9.c else '' end as D3,
case when datediff(dd, t9.cr_date, getdate()) =4 then t9.c else '' end as D4,
case when datediff(dd, t9.cr_date, getdate()) =5 then t9.c else '' end as D5,
case when datediff(dd, t9.cr_date, getdate()) =6 then t9.c else '' end as D6,
case when datediff(dd, t9.cr_date, getdate()) =7 then t9.c else '' end as D7,
case when datediff(dd, t9.cr_date, getdate()) =8 then t9.c else '' end as D8,
case when datediff(dd, t9.cr_date, getdate()) =9 then t9.c else '' end as D9,
case when datediff(dd, t9.cr_date, getdate()) =10 then t9.c else '' end as D10,
case when datediff(dd, t9.cr_date, getdate()) =11 then t9.c else '' end as D11,
case when datediff(dd, t9.cr_date, getdate()) =12 then t9.c else '' end as D12,
case when datediff(dd, t9.cr_date, getdate()) =13 then t9.c else '' end as D13,
case when datediff(dd, t9.cr_date, getdate()) =14 then t9.c else '' end as D14,
case when datediff(dd, t9.cr_date, getdate()) =15 then t9.c else '' end as D15,
case when datediff(dd, t9.cr_date, getdate()) =16 then t9.c else '' end as D16,
case when datediff(dd, t9.cr_date, getdate()) =17 then t9.c else '' end as D17,
case when datediff(dd, t9.cr_date, getdate()) =18 then t9.c else '' end as D18,
case when datediff(dd, t9.cr_date, getdate()) =19 then t9.c else '' end as D19,
case when datediff(dd, t9.cr_date, getdate()) =20 then t9.c else '' end as D20,
case when datediff(dd, t9.cr_date, getdate()) =21 then t9.c else '' end as D21,
case when datediff(dd, t9.cr_date, getdate()) =22 then t9.c else '' end as D22,
case when datediff(dd, t9.cr_date, getdate()) =23 then t9.c else '' end as D23,
case when datediff(dd, t9.cr_date, getdate()) =24 then t9.c else '' end as D24,
case when datediff(dd, t9.cr_date, getdate()) =25 then t9.c else '' end as D25,
case when datediff(dd, t9.cr_date, getdate()) =26 then t9.c else '' end as D26,
case when datediff(dd, t9.cr_date, getdate()) =27 then t9.c else '' end as D27,
case when datediff(dd, t9.cr_date, getdate()) =28 then t9.c else '' end as D28,
case when datediff(dd, t9.cr_date, getdate()) =29 then t9.c else '' end as D29,
case when datediff(dd, t9.cr_date, getdate()) =30 then t9.c else '' end as D30
FROM (
select
a.cr_date,
a.fengzi,
b.fengmu,
CAST(convert(decimal(16,2), CAST(a.fengzi*1.00*100 / b.fengmu AS decimal(10,2))) AS varchar(50)) +'%' c
from
(
select
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) fengzi
from
VAPP_ITEM as vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
TICKET_STATUS IN ('Closed','Archive')
AND (
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES as va
WHERE
va.ITEM_ID=vi.ROW_ID
and va.ATTR_ID=553) IS NULL
and person1_root_org_name = @kehu
and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=@tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
group by
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
as a
join
(
select
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) fengmu
from
VAPP_ITEM as vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
TICKET_STATUS IN ('Closed','Archive')
and person1_root_org_name = @kehu
and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=@tianshu
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
)
as b
on a.cr_date=b.cr_date
) AS t9
) AS A
UNION ALL
/* Avg_onsite_time 30天内创建的工单，每日平均上门时间 只计算有第一次上门时间的工单。(不统计删除工单) #单位是小时*/
SELECT
'<1:00',
--将总平均值转换为时间格式
(SELECT
CONVERT(VARCHAR(12), avg(A.Avg_onsite_time) /60/60 % 24) + ':'
+ CONVERT(VARCHAR(2), avg(A.Avg_onsite_time) /60 % 60) + ':'
+ CONVERT(VARCHAR(2), avg(A.Avg_onsite_time) % 60)
),
'Avg Onsite Time',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t8.d AS Avg_onsite_time,
case when datediff(dd, t8.cr_date, getdate()) =1 then t8.c else '' end as D1,
case when datediff(dd, t8.cr_date, getdate()) =2 then t8.c else '' end as D2,
case when datediff(dd, t8.cr_date, getdate()) =3 then t8.c else '' end as D3,
case when datediff(dd, t8.cr_date, getdate()) =4 then t8.c else '' end as D4,
case when datediff(dd, t8.cr_date, getdate()) =5 then t8.c else '' end as D5,
case when datediff(dd, t8.cr_date, getdate()) =6 then t8.c else '' end as D6,
case when datediff(dd, t8.cr_date, getdate()) =7 then t8.c else '' end as D7,
case when datediff(dd, t8.cr_date, getdate()) =8 then t8.c else '' end as D8,
case when datediff(dd, t8.cr_date, getdate()) =9 then t8.c else '' end as D9,
case when datediff(dd, t8.cr_date, getdate()) =10 then t8.c else '' end as D10,
case when datediff(dd, t8.cr_date, getdate()) =11 then t8.c else '' end as D11,
case when datediff(dd, t8.cr_date, getdate()) =12 then t8.c else '' end as D12,
case when datediff(dd, t8.cr_date, getdate()) =13 then t8.c else '' end as D13,
case when datediff(dd, t8.cr_date, getdate()) =14 then t8.c else '' end as D14,
case when datediff(dd, t8.cr_date, getdate()) =15 then t8.c else '' end as D15,
case when datediff(dd, t8.cr_date, getdate()) =16 then t8.c else '' end as D16,
case when datediff(dd, t8.cr_date, getdate()) =17 then t8.c else '' end as D17,
case when datediff(dd, t8.cr_date, getdate()) =18 then t8.c else '' end as D18,
case when datediff(dd, t8.cr_date, getdate()) =19 then t8.c else '' end as D19,
case when datediff(dd, t8.cr_date, getdate()) =20 then t8.c else '' end as D20,
case when datediff(dd, t8.cr_date, getdate()) =21 then t8.c else '' end as D21,
case when datediff(dd, t8.cr_date, getdate()) =22 then t8.c else '' end as D22,
case when datediff(dd, t8.cr_date, getdate()) =23 then t8.c else '' end as D23,
case when datediff(dd, t8.cr_date, getdate()) =24 then t8.c else '' end as D24,
case when datediff(dd, t8.cr_date, getdate()) =25 then t8.c else '' end as D25,
case when datediff(dd, t8.cr_date, getdate()) =26 then t8.c else '' end as D26,
case when datediff(dd, t8.cr_date, getdate()) =27 then t8.c else '' end as D27,
case when datediff(dd, t8.cr_date, getdate()) =28 then t8.c else '' end as D28,
case when datediff(dd, t8.cr_date, getdate()) =29 then t8.c else '' end as D29,
case when datediff(dd, t8.cr_date, getdate()) =30 then t8.c else '' end as D30
FROM (
SELECT
b.cr_date,
--计算总平均值
AVG(b.zd) d,
--将日平均值转时间格式
(
SELECT
CONVERT(VARCHAR(12), avg(b.zd) /60/60 % 24) + ':'
+ CONVERT(VARCHAR(2), avg(b.zd) /60 % 60) + ':'
+ CONVERT(VARCHAR(2), avg(b.zd) % 60)
) c
FROM
(
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
--如果上门时间早于创建时间，赋NULL。如果晚于创建时间，计算差值
CASE WHEN(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) > DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')
THEN
DATEDIFF(
ss,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
)
)
ELSE
NULL
END AS zd
--判断到此结束
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) is not null
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS IN ('closed','archive')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
) b
GROUP BY
b.cr_date
) AS t8
) AS A
UNION ALL
/*No_onsite_time 30天内创建的工单，统计每天派给了硬件ccti=hw但是没有第一次上门时间的。(不统计删除工单)*/
SELECT
'=0',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'No Onsite Time',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t11.c AS No_onsite_time,
case when datediff(dd, t11.cr_date, getdate()) =1 then t11.c else '' end as D1,
case when datediff(dd, t11.cr_date, getdate()) =2 then t11.c else '' end as D2,
case when datediff(dd, t11.cr_date, getdate()) =3 then t11.c else '' end as D3,
case when datediff(dd, t11.cr_date, getdate()) =4 then t11.c else '' end as D4,
case when datediff(dd, t11.cr_date, getdate()) =5 then t11.c else '' end as D5,
case when datediff(dd, t11.cr_date, getdate()) =6 then t11.c else '' end as D6,
case when datediff(dd, t11.cr_date, getdate()) =7 then t11.c else '' end as D7,
case when datediff(dd, t11.cr_date, getdate()) =8 then t11.c else '' end as D8,
case when datediff(dd, t11.cr_date, getdate()) =9 then t11.c else '' end as D9,
case when datediff(dd, t11.cr_date, getdate()) =10 then t11.c else '' end as D10,
case when datediff(dd, t11.cr_date, getdate()) =11 then t11.c else '' end as D11,
case when datediff(dd, t11.cr_date, getdate()) =12 then t11.c else '' end as D12,
case when datediff(dd, t11.cr_date, getdate()) =13 then t11.c else '' end as D13,
case when datediff(dd, t11.cr_date, getdate()) =14 then t11.c else '' end as D14,
case when datediff(dd, t11.cr_date, getdate()) =15 then t11.c else '' end as D15,
case when datediff(dd, t11.cr_date, getdate()) =16 then t11.c else '' end as D16,
case when datediff(dd, t11.cr_date, getdate()) =17 then t11.c else '' end as D17,
case when datediff(dd, t11.cr_date, getdate()) =18 then t11.c else '' end as D18,
case when datediff(dd, t11.cr_date, getdate()) =19 then t11.c else '' end as D19,
case when datediff(dd, t11.cr_date, getdate()) =20 then t11.c else '' end as D20,
case when datediff(dd, t11.cr_date, getdate()) =21 then t11.c else '' end as D21,
case when datediff(dd, t11.cr_date, getdate()) =22 then t11.c else '' end as D22,
case when datediff(dd, t11.cr_date, getdate()) =23 then t11.c else '' end as D23,
case when datediff(dd, t11.cr_date, getdate()) =24 then t11.c else '' end as D24,
case when datediff(dd, t11.cr_date, getdate()) =25 then t11.c else '' end as D25,
case when datediff(dd, t11.cr_date, getdate()) =26 then t11.c else '' end as D26,
case when datediff(dd, t11.cr_date, getdate()) =27 then t11.c else '' end as D27,
case when datediff(dd, t11.cr_date, getdate()) =28 then t11.c else '' end as D28,
case when datediff(dd, t11.cr_date, getdate()) =29 then t11.c else '' end as D29,
case when datediff(dd, t11.cr_date, getdate()) =30 then t11.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=vi.ROW_ID
AND va.ATTR_ID=553
) is null
--AND CCTI_CLASS=@CCTI_CLASS
AND closed_by_group_name IN ('@group')
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t11
) AS A
UNION ALL
SELECT
/*Onsite>1 30天内创建的，统计有第一次和第二次上门时间的工单数量(上门大于一次)(不统计删除工单) */
'=0',
CAST(MAX(D1) + MAX(D2) + MAX(D3) + MAX(D4) + MAX(D5) + MAX(D6) + MAX(D7) + MAX(D8) + MAX(D9) + MAX(D10) +
MAX(D11) + MAX(D12) + MAX(D13) + MAX(D14) + MAX(D15) + MAX(D16) + MAX(D17) + MAX(D18) + MAX(D19) + MAX(D20) +
MAX(D21) + MAX(D22) + MAX(D23) + MAX(D24) + MAX(D25) + MAX(D26) + MAX(D27) + MAX(D28) + MAX(D29) + MAX(D30) as varchar),
'Onsite>1',
CAST(MAX(D1) as varchar),CAST(MAX(D2) as varchar),CAST(MAX(D3) as varchar),CAST(MAX(D4) as varchar),CAST(MAX(D5) as varchar),
CAST(MAX(D6) as varchar),CAST(MAX(D7) as varchar),CAST(MAX(D8) as varchar),CAST(MAX(D9) as varchar),CAST(MAX(D10) as varchar),
CAST(MAX(D11) as varchar),CAST(MAX(D12) as varchar),CAST(MAX(D13) as varchar),CAST(MAX(D14) as varchar),CAST(MAX(D15) as varchar),
CAST(MAX(D16) as varchar),CAST(MAX(D17) as varchar),CAST(MAX(D18) as varchar),CAST(MAX(D19) as varchar),CAST(MAX(D20) as varchar),
CAST(MAX(D21) as varchar),CAST(MAX(D22) as varchar),CAST(MAX(D23) as varchar),CAST(MAX(D24) as varchar),CAST(MAX(D25) as varchar),
CAST(MAX(D26) as varchar),CAST(MAX(D27) as varchar),CAST(MAX(D28) as varchar),CAST(MAX(D29) as varchar),CAST(MAX(D30) as varchar)
FROM
(
SELECT
t12.c AS Onsite_1,
case when datediff(dd, t12.cr_date, getdate()) =1 then t12.c else '' end as D1,
case when datediff(dd, t12.cr_date, getdate()) =2 then t12.c else '' end as D2,
case when datediff(dd, t12.cr_date, getdate()) =3 then t12.c else '' end as D3,
case when datediff(dd, t12.cr_date, getdate()) =4 then t12.c else '' end as D4,
case when datediff(dd, t12.cr_date, getdate()) =5 then t12.c else '' end as D5,
case when datediff(dd, t12.cr_date, getdate()) =6 then t12.c else '' end as D6,
case when datediff(dd, t12.cr_date, getdate()) =7 then t12.c else '' end as D7,
case when datediff(dd, t12.cr_date, getdate()) =8 then t12.c else '' end as D8,
case when datediff(dd, t12.cr_date, getdate()) =9 then t12.c else '' end as D9,
case when datediff(dd, t12.cr_date, getdate()) =10 then t12.c else '' end as D10,
case when datediff(dd, t12.cr_date, getdate()) =11 then t12.c else '' end as D11,
case when datediff(dd, t12.cr_date, getdate()) =12 then t12.c else '' end as D12,
case when datediff(dd, t12.cr_date, getdate()) =13 then t12.c else '' end as D13,
case when datediff(dd, t12.cr_date, getdate()) =14 then t12.c else '' end as D14,
case when datediff(dd, t12.cr_date, getdate()) =15 then t12.c else '' end as D15,
case when datediff(dd, t12.cr_date, getdate()) =16 then t12.c else '' end as D16,
case when datediff(dd, t12.cr_date, getdate()) =17 then t12.c else '' end as D17,
case when datediff(dd, t12.cr_date, getdate()) =18 then t12.c else '' end as D18,
case when datediff(dd, t12.cr_date, getdate()) =19 then t12.c else '' end as D19,
case when datediff(dd, t12.cr_date, getdate()) =20 then t12.c else '' end as D20,
case when datediff(dd, t12.cr_date, getdate()) =21 then t12.c else '' end as D21,
case when datediff(dd, t12.cr_date, getdate()) =22 then t12.c else '' end as D22,
case when datediff(dd, t12.cr_date, getdate()) =23 then t12.c else '' end as D23,
case when datediff(dd, t12.cr_date, getdate()) =24 then t12.c else '' end as D24,
case when datediff(dd, t12.cr_date, getdate()) =25 then t12.c else '' end as D25,
case when datediff(dd, t12.cr_date, getdate()) =26 then t12.c else '' end as D26,
case when datediff(dd, t12.cr_date, getdate()) =27 then t12.c else '' end as D27,
case when datediff(dd, t12.cr_date, getdate()) =28 then t12.c else '' end as D28,
case when datediff(dd, t12.cr_date, getdate()) =29 then t12.c else '' end as D29,
case when datediff(dd, t12.cr_date, getdate()) =30 then t12.c else '' end as D30
FROM (
SELECT
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,
COUNT(TICKET_ID) c
FROM
VAPP_ITEM AS vi
LEFT JOIN ORG_VEII ON vi.person1_org_name=ORG_VEII.ORG3
WHERE
(
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=row_id
AND va.ATTR_ID=555
) is not null
AND (
SELECT
TOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')
FROM
VAPP_ITEM_ATTRIBUTES AS va
WHERE
va.ITEM_ID=row_id
AND va.ATTR_ID=558
) is not null
AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshu
AND person1_root_org_name = @kehu
AND TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')
--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值
AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS END
AND ORG_VEII.ORG1= CASE WHEN @org_name <> '' THEN @org_name ELSE ORG_VEII.ORG1 END
GROUP BY
CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)
) AS t12
) AS A
'''
def sql_zong(kehu='WTC', CCTI_CLASS=' ',sla='WTC-P1',quyu=''):
'''返回综合查询的拼接sql'''
sqa1 = f'''
/*
本SQL报表抓取ServiceAI MSSQL数据库，统计客户在30天内的KPI值：
Total Call 工单总数
Unclosed 未关单总数
Scheduled 预计解决数量
P1 call# P1紧急工单数量
Over SLA# 超SLA数量
SLA Met% SLA达成率
Worst TAT 当天完成跨度最长的工单所花的时间
Remote Fixed 远程解决率
Max late close 系统操作关闭时间-实际关闭时间之间的差值，该栏位列出最大的差值所花的时间，ServiceAI没有实际关单时间，可不统计。
Repeat Call# 重复Call数量，ServiceAI没有该计算值，暂不统计。
Avg onsite time 平均上门时间
No Onsite time 没有上门时间的数量
Onsite# > 1 上门次数大于1次的数量
*/
/*@kehu 定义报表统计的客户*/
--declare @kehu VARCHAR(20)
declare @kehu nvarchar(2000)
set @kehu =N'{kehu}'
/*@CCTI_CLASS 定义报表中ccti的类型。为空，即统计所有CCTI。同时表标题列的范围，也会根据该值自动修改*/
declare @CCTI_CLASS nvarchar(2000)
set @CCTI_CLASS=N'{CCTI_CLASS}'
/*@district 定义报表统计的区域。为空，即统计该客户所有区域*/
-- declare @district nvarchar(2000)
-- set @district=''
/*@tianshu 定义报表统计的天数，目前由于格式，只能是30天*/
declare @tianshu int
set @tianshu=30
/*@sla_target_name 定义查询工单的SLA*/
declare @sla_target_name nvarchar(2000)
set @sla_target_name=N'{sla}'
/*@group 定义No Onsite time中没有产生上门时间的硬件组的名称*/
declare @group nvarchar(2000)
/*@org_name 定义要查询的客户区域*/
declare @org_name nvarchar(2000)
set @org_name=N'{quyu}'
'''
sqa2 = r"set @group=N'''L1-HW-SH'',''L1-HW-BJ'',''L1-HW-GZ'',''L1-HW-SZ'''"
zonghe = sqa1 + sqa2 + sqa3
return zonghe
'''
下面是
一，UNCLOSED 某个客户所有未关单
二，YESTERDAY NEW 某个客户昨天创建的所有工单
三，YESTERDAY CLOSED 客户昨天关闭的所有工单
的sql语句
'''
kehu='WTC'
#一，UNCLOSED 客户所有未关单
sqa4=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
ticket_details +' ' + description_long +' ' + last_worklog as 'Updates',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted')
'''
#二，YESTERDAY NEW 客户昨天创建的所有工单
sqa5=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_group_name as 'Queue',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
last_worklog as 'Last Update Description',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution',
DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00') AS 'Created',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') AS 'Close_Time',
CCTI_CLASS+':'+CCTI_CATEGORY+':'+CCTI_TYPE+':'+CCTI_TYPE AS 'Service',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as 'Contact Person',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as 'Telephone'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}'
and TICKET_STATUS not in ('Request - Delete','archive','Approved','Submitted')
and CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)= convert(varchar(10),dateadd(DD,-1,getdate()),120)
'''
#三，YESTERDAY CLOSED 客户昨天关闭的所有工单
sqa6=f'''
SELECT
sla_target_name as 'Pri',
(case when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS not in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), GETDATE()) when sla_compliance_status_indicator = 'Breached SLA' and TICKET_STATUS in ('closed','Request - Delete','archive','Approved','Submitted') then DATEDIFF(hh, DATEADD(SECOND, (select top 1 sla_due_by FROM VSLA_AGREEMENT_COMPLIANCE_LIST_UX as vc where vc.item_id=vi.ROW_ID order by threshold_sort_order desc), '1970/1/1 08:00:00'), DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')) else null end) as 'Over Sla',
ticket_identifier as 'Ticket#',
TICKET_STATUS as 'State',
[person1_root_org_name] as 'Cust_Name',
person1_org_name as 'Store',
assigned_to_group_name as 'Queue',
assigned_to_username as 'Owner',
ticket_description as 'Problem Description',
last_worklog as 'Last Update Description',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as 'Solution',
DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00') AS 'Created',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') AS 'Close_Time',
CCTI_CLASS+':'+CCTI_CATEGORY+':'+CCTI_TYPE+':'+CCTI_TYPE AS 'Service',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as 'Contact Person',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as 'Telephone'
from
VAPP_ITEM as vi
left join (select row_number() over(partition by item_id order by threshold_sort_order desc) as iid, sla_due_by, item_id from VSLA_AGREEMENT_COMPLIANCE_LIST_UX) sla on vi.ROW_ID = sla.item_id and sla.iid = 1
where
person1_root_org_name='{kehu}'
and TICKET_STATUS not in ('Request - Delete','archive','Approved','Submitted')
and CONVERT(VARCHAR(10),(DATEADD(SECOND, closed_date, '1970/1/1 08:00:00')),120)= convert(varchar(10),dateadd(DD,-1,getdate()),120)
'''
def sql_zong2(i):
'''给调用函数返回 三条sql语句
一，UNCLOSED 某个客户所有未关单
二，YESTERDAY NEW 某个客户昨天创建的所有工单
三，YESTERDAY CLOSED 客户昨天关闭的所有工单
'''
i2=int(i)
if i2==1:
return sqa4
elif i2==2:
return sqa5
elif i2==3:
return sqa6
else:
print('你输入的数字不对，没有你要查询的sql')
demo2/urls.py
from django.urls import path,include
from .views import * #导入视图文件中的所有视图函数
urlpatterns=[
# 报表二
path('ceshi2', baobiao2),
# 发送邮件视图
# 该视图不用访问url download网页而是直接将渲染过后的网页保存下来然后通过邮件发送
path('sendmail2', baobiao2_sendmail),
#将报表写入excel视图函数
path("wrexcel",baobiao3)
]demo2/views.py
from django.shortcuts import render
import json
from random import randrange
from django.http import HttpResponse
from rest_framework.views import APIView
#导入模板
from django.shortcuts import render
from django.template.defaulttags import register #首先在view.py里导入register模块，这是干嘛的呢？他是Django自定义函数的
#作图和连接数据的的模块
import pyecharts
from pyecharts.charts import Bar #导入柱形图
from pyecharts import options as opts #导入配置
import pymssql #连接sqlserver数据库的包
import pandas as pd
from pyecharts.globals import ThemeType #导入主题
from pyecharts.charts import Pie,Line, Grid #导入饼图 折线图
from pyecharts.commons.utils import JsCode
from .sql import * #导入sql语句中的变量
from datetime import datetime
from datetime import *
import datetime
import requests
from pyecharts.charts import Liquid
#做表格需要模块
from pyecharts.components import Table
from pyecharts.options import ComponentTitleOpts
from styleframe import StyleFrame,Styler,utils #导入写入excel库
#------------------------------------------------------
'''-------------------------------------------------变量说明-----------------------------------------------'''
#ccti_list 定义查询的ccti 在views.py 文件中70行中的列表修改
#quyu 定义查询客户的区域 在views.py文件中的71行中修改
report_name='Daily_Report_WTC' #邮件主题 文件名称 图表名称
dircname='/docker/nginxWebUI/html' # 文件夹变量 格式：/docker/nginxWebUI/html
murl='https://172.16.64.121' #域名变量 格式： https://172.16.64.121
Recipient=['2569449660@qq.com', 'bill.wang@value-exch.com'] #接收人变量'Figo.FEI@value-exch.com'
#注意客户变量需要修改两次
#kehu 客户变量在sql.py 第973行 作用修改sql查询中筛选的客户
#kehu 客户变量需要修改两次 sql.py 第1032行
email_text='''
<strong>DR负责人: <span style="color:#ffffff"><span style="background-color:#e74c3c">Kame.Lu</span></span> (日常运作/内容变更/停止发送) Backup：<span style="color:#ffffff"><span style="background-color:#e67e22">????</span></span><br />
<br />
<strong><u><span style="font-size:16px">Sheet Description</span></u></strong><br />
<strong><span style="color:#ffffff"><span style="background-color:#ff0000">Customer Name</span></span></strong> 客户\区域过去31天服务统计 (Customer\District KPI statistics of last 31 days)<br />
<br />
<strong><u><span style="font-size:16px">KPI Description</span></u></strong><br />
<strong>SLA Met%</strong> : 已关工单SLA达成率 (SLA achieved% of closed tickets)<br />
<strong>Worst TAT</strong> (Turn Around Time) : 已关工单，处理时间最长的 (the longest repair time of tickets fixed)<br />
<strong>First Call Fixed</strong> : 首次上门故障解决率<br />
<strong>Remote Fixed</strong>: 远程解决率<br />
<strong>Avg onsite time</strong> : 平均上门所花费的时间<br />
<strong>NoOnsiteTime</strong>: 没有填写过上门信息的数量<br />
<strong>Onsite# >1</strong>: 上门次数大于1次的数量</strong><br />
'''
# 定义数据库查询类
class Sql_chaxun2():
'''初始化sqlserver 连接属性'''
def __init__(self):
self.servername = '888.888.888.888' # 服务器名称
self.username = '你猜' # 账户
self.port = '23350' # 端口号
self.password = '你猜' # 密码
self.dabasename = '你猜' #数据库名称
self.get_sql = [] # 获取8条拼接sql列表
self.sqlj = [] # 获取8条sql执行结果列表
self.ccti_list =[' ','HW','SW/PC01','SW/PC02', 'SW/POS','SW/手持','SW/SCO','NW']
self.quyu=['东区','西区','南区','北区']
def chaxun(self):
'''执行all查询'''
con = pymssql.connect(server=self.servername, user=self.username, password=self.password,database=self.dabasename, port=self.port, charset='utf8')
print('sqlserver 连接成功')
r5 = [] # 应用于写入excel 存储dataframe数据的列表
q = 1
for i in self.ccti_list:
print(i)
r4 = []
i2 = sql_zong(CCTI_CLASS=f'{i}')
d = pd.read_sql(i2,con)
print(2)
d = d.fillna(0) # 将空值填充为0
d = d.replace('100.00%', '100%') # 替换值
#将每一个dataframe中的数据取出来保存到一个列表中
r5.append(d) #应用于写入excel 将每一个dataframe数据存储到列表中
for ii2 in range(len(d)):
r4.append(d.loc[ii2].tolist())
#将每一个列表存储到一个总列表中
self.sqlj.append(r4)
print(f'第{q}句sql执行成功')
q += 1
# 添加新需求 给excel增加3个工作簿
# 1， UNCLOSED 某个客户所有未关单 2， YESTERDAY NEW 某个客户昨天创建的所有工单 3，YESTERDAY CLOSED 客户昨天关闭的所有工单
r6=[]
for i in range(1,4):
print(i)
i3=sql_zong2(i)
d2=pd.read_sql(i3,con)
d2 = d2.fillna(' ') # 将空值填充为空字符串空格
d2 = d2.replace('100.00%', '100%') # 替换值
r6.append(d2)
print(f'第{q}句sql执行成功')
q += 1
# 添加新需求 给excel增加屈臣氏的 东区 南区 西区 北区
r8=[]
for i in self.quyu:
r7=[]
for j in self.ccti_list:
i4=sql_zong(CCTI_CLASS=f'{j}',quyu=f'{i}')
d3=pd.read_sql(i4,con)
d3 = d3.fillna(0) # 将空值填充为空字符串空格
d3 = d3.replace('100.00%', '100%') # 替换值
r7.append(d3)
print(f'第{q}句sql执行成功')
q += 1
r8.append(r7)
con.commit() # 提交对数据库的操作
con.close() # 关闭数据库
print('关闭数据库成功')
jieguo=self.sqlj #将总列表保存给变量 用于locals传输
#return self.sqlj 如果只是为了反回给html数据只需要将self.sqlj 返回给调用函数
#但是新需求需要将数据写入excel 这就需要使用locals来返回当前函数的所有局部变量
#以字典的形式返回给调用函数， 调用函数去除值得时候需要用字典取值得方式
return locals()
#wtc 报表2 横着过来
def baobiao2(request):
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
a = Sql_chaxun2()
d = a.chaxun() #将locals返回的值赋值给变量
c=d['jieguo'] #取出字典中的值
name=report_name #表名称
return render(request,'baobiao2.html',locals())
#本视图函数是为了渲染页面的时候直接得时候保存为html文件发送给固定人员
def baobiao2_sendmail(request):
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
a = Sql_chaxun2()
c = a.chaxun()
name = report_name # 表名称
html=render(request,'baobiao2.html',locals())
html2=html.content.decode()#bytes类型转为str
print(type(html2))
# 下面是保存网页并通过邮件发送的方式
from django.core import mail # 导入发送邮件的模块
from email.mime.text import MIMEText # html格式和文本格式邮件
from django.core.mail import send_mail, EmailMultiAlternatives
from email.header import make_header
from django.core.mail import send_mail, EmailMultiAlternatives
'''将网页文件写入文本'''
# 获取当前时间 时/分/秒
import datetime
time = (str(datetime.datetime.now()).split(' ')[0])
#根据操作系统来使用不同的写入文件方案
import sys
system = sys.platform
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# windows 将网页写入文件中
# 这个是写入django项目静态文件夹中
try:
with open(f'./static/mail/Daily_Report_WTC-ALL-{time}.html', 'wt', encoding='UTF-8') as p:
p.write(html2)
print('写入成功')
except:
print('文件写入失败')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux中的docker 文件夹中写入文件
try:
with open(f'{dircname}/{report_name}-{time}.html', 'wt', encoding='UTF-8') as p:
p.write(html2)
print('写入成功')
except:
print('文件写入失败')
'''开始生成emal邮件'''
subject = f'{report_name}' # 邮件主题
# message = 'bill.ceshi' #邮件内容
email_content=f'''
{email_text}
<strong>{murl}/{report_name}-{time}.html</strong>
'''
text_content ='-------------'
from_email = 'serviceai@zh.value-exch.com.cn' # 发件人
# 'Figo.FEI@value-exch.com'
recipient_list = Recipient # 收件人
html_content = email_content
#html_message=aw
msg = EmailMultiAlternatives(subject,text_content,from_email, recipient_list)
msg.attach_alternative(html_content, "text/html")
'''添加html附件之后发送'''
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# Windows发送
msg.attach_file(f'./static/mail/Daily_Report_WTC-ALL-{time}.html')
print('windows系统附件加载完成')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux发送
msg.attach_file(f'{dircname}/{report_name}-{time}.html')
print("linux系统中附件加载完成")
try:
msg.send()
return HttpResponse('这个是baobiao2_sendmail函数发送的邮件')
except:
return HttpResponse('邮件发送失败，请联系bill.wang@value-exch.com')
#设置函数设置excel表格样式
def styleframe(ceshi1,sl):
'''定义函数设置excel表格格式'''
# 第一步给dataframe重新设置列索引
import numpy as np
if sl==33:
np1 = (np.arange(33)).tolist()
ceshi2 = ceshi1.set_axis(np1, axis=1) # 重新设置列索引
elif sl==10:
#np1 = (np.arange(9)).tolist()
ceshi2 = ceshi1 # 重新设置列索引
elif sl==16:
#np1 = (np.arange(16)).tolist()
ceshi2 = ceshi1 # 重新设置列索引
# 第二步 设置
sf = StyleFrame(ceshi2) # 新建一个StyleFrame供接下来使用，新建StyleFrame使用的方法与新建DataFrame用的一样
if sl==33:
#style 指的是指定那些列设置什么样式
# 设置除了第三列的样式之外的样式
style=[0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
30, 31, 32]
#style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2=[2,5]
elif sl==10:
#针对UNCLOSED 做判断
# style 指的是指定那些列设置什么样式
style = ['Pri', 'Over Sla', 'Ticket#', 'State', 'Cust_Name', 'Store', 'Owner', 'Problem Description', 'Updates','Solution']
# style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2 = []
style3=1
elif sl==16:
#针对 YESTERDAY NEW YESTERDAY CLOSED 做判断
# style 指的是指定那些列设置什么样式
style = ['Pri', 'Over Sla', 'Ticket#', 'State', 'Cust_Name', 'Store', 'Queue', 'Owner', 'Problem Description','Last Update Description','Solution','Created', 'Close_Time', 'Service', 'Contact Person', 'Telephone']
# style2 指的是指定那些列设置什么样式 因为不同的列有不同的样式所以 有style2变量 作用和style变量作用一样
style2 = []
style3=2
#判断style2列表是否为空，如果为空 意思就是不给具体的列设置值
if style2:
print('d不是空的列表')
# 设置除了第三列的样式之外的样式
# style = [0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
# 30, 31, 32]
#有底色
# sf.apply_column_style(cols_to_style=style,
# styler_obj=Styler(font_color='black', bg_color='#C0C0C0', font_size=8, bold=True,
# border_type='thin'), style_header=True)
#无底色
sf.apply_column_style(cols_to_style=style,
styler_obj=Styler(font_color='black', font_size=8, bold=True,
border_type='thin'), style_header=True)
# 设置第3列的样式
# ss=style2[0]
# ss2=style2[1]
sf.apply_column_style(cols_to_style=[2],
styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
# 设置第五行的样式
sf.apply_style_by_indexes(indexes_to_style=[5],
styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
elif not style2:
# 针对 YESTERDAY NEW YESTERDAY CLOSED 做判断
# 设置所有列样式
# 1，取消所有列自动换行wrap_text=False
sf.apply_column_style(cols_to_style=style,
styler_obj=Styler(font_color='black', font_size=8, bold=True,
border_type='thin',wrap_text=False,number_format='DD/MM/YY HH:MM'), style_header=True)
print('d是空的列表,设置标题的样式')
# 设置YESTERDAY NEW YESTERDAY CLOSED标题的样式
sf.apply_headers_style(styler_obj=Styler(font_color='white', bg_color='#003366', font_size=8, bold=True,
border_type='thin'))
# 设置 YESTERDAY NEW YESTERDAY CLOSED 表格宽度
sf.set_column_width(columns=style,width=10)
return sf
#excel写入文件并发送
# wtc 报表2 横着过来
def baobiao3(request):
#django邮件发送模块
from django.core.mail import send_mail, EmailMultiAlternatives
# 展示时间模块
from datetime import datetime
dt = datetime.now()
dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"
# 获取当前时间 时/分/秒
import datetime
time = (str(datetime.datetime.now()).split(' ')[0])
a = Sql_chaxun2() #实例化类对象
d = a.chaxun() # 将locals返回的值赋值给变量
c = d['r5'] # 取出字典中的值 代表all所有区域
c1=d['r8'][0] # 取出字典中的值 代表'东区'
c2 = d['r8'][1] # 取出字典中的值 代表'西区'
c3 = d['r8'][2] # 取出字典中的值 代表'南区'
c4 = d['r8'][3] # 取出字典中的值 代表'北区'
b=d['r6']
'''
通过列表c索引取出来的每一个都是一个dataframe数据，之后调用styleframe函数设置每一个dataframe的样式
每一个dataframe对应一个工作簿中的一个excel块
'''
#ALL
sf1 = styleframe(c[0],33) #调用styleframe函数设置每一个dataframe样式
sf2 = styleframe(c[1],33)
sf3 = styleframe(c[2],33)
sf4 = styleframe(c[3],33)
sf5 = styleframe(c[4],33)
sf6 = styleframe(c[5],33)
sf7 = styleframe(c[6],33)
sf8 = styleframe(c[7],33)
#['东区',]
sf12 = styleframe(c1[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf13 = styleframe(c1[1], 33)
sf14 = styleframe(c1[2], 33)
sf15 = styleframe(c1[3], 33)
sf16 = styleframe(c1[4], 33)
sf17 = styleframe(c1[5], 33)
sf18 = styleframe(c1[6], 33)
sf19 = styleframe(c1[7], 33)
# ['西区',]
sf20 = styleframe(c2[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf21 = styleframe(c2[1], 33)
sf22 = styleframe(c2[2], 33)
sf23 = styleframe(c2[3], 33)
sf24 = styleframe(c2[4], 33)
sf25 = styleframe(c2[5], 33)
sf26 = styleframe(c2[6], 33)
sf27 = styleframe(c2[7], 33)
# ['南区',]
sf28 = styleframe(c3[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf29 = styleframe(c3[1], 33)
sf30 = styleframe(c3[2], 33)
sf31 = styleframe(c3[3], 33)
sf32 = styleframe(c3[4], 33)
sf33 = styleframe(c3[5], 33)
sf34 = styleframe(c3[6], 33)
sf35 = styleframe(c3[7], 33)
# ['北区']
sf36 = styleframe(c4[0], 33) # 调用styleframe函数设置每一个dataframe样式
sf37 = styleframe(c4[1], 33)
sf38 = styleframe(c4[2], 33)
sf39 = styleframe(c4[3], 33)
sf40 = styleframe(c4[4], 33)
sf41 = styleframe(c4[5], 33)
sf42 = styleframe(c4[6], 33)
sf43 = styleframe(c4[7], 33)
#UNCLOSED
sf9=styleframe(b[0],10)
#YESTERDAY NEW
sf10 = styleframe(b[1],16)
#YESTERDAY CLOSED
sf11 = styleframe(b[2],16)
# 写入excel代码
#第一步：根据操作系统自动识别路径
import sys
system = sys.platform
if system == 'win32':
print('当前系统为windows，启用Windows方案')
ew1 = StyleFrame.ExcelWriter(rf'D:\jiaoben\{report_name}-{time}.xlsx', index=False)
lujing=f'D:\jiaoben\{report_name}-{time}.xlsx' #取出路径方便发送时使用
print('Windows 系统excel 写入路径指定成功')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
ew1 = StyleFrame.ExcelWriter(rf'{dircname}/{report_name}-{time}.xlsx') #linux中不能使用 index=False 否则会报错
lujing = f'{dircname}/{report_name}-{time}.xlsx' #取出路径方便发送时使用
print('linux 系统excel 写入路径指定成功')
#第二步：将文件对象赋值给ew
#all
ew = ew1
sf1.to_excel(ew, sheet_name='All', index=False, header=False, startrow=0)
sf2.to_excel(ew, sheet_name='All', index=False, header=False, startrow=14)
sf3.to_excel(ew, sheet_name='All', index=False, header=False, startrow=28)
sf4.to_excel(ew, sheet_name='All', index=False, header=False, startrow=42)
sf5.to_excel(ew, sheet_name='All', index=False, header=False, startrow=56)
sf6.to_excel(ew, sheet_name='All', index=False, header=False, startrow=70)
sf7.to_excel(ew, sheet_name='All', index=False, header=False, startrow=84)
sf8.to_excel(ew, sheet_name='All', index=False, header=False, startrow=98)
# ['东区',]
sf12.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=0)
sf13.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=14)
sf14.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=28)
sf15.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=42)
sf16.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=56)
sf17.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=70)
sf18.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=84)
sf19.to_excel(ew, sheet_name='东区', index=False, header=False, startrow=98)
# ['南区',]
sf28.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=0)
sf29.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=14)
sf30.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=28)
sf31.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=42)
sf32.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=56)
sf33.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=70)
sf34.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=84)
sf35.to_excel(ew, sheet_name='南区', index=False, header=False, startrow=98)
# ['西区',]
sf20.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=0)
sf21.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=14)
sf22.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=28)
sf23.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=42)
sf24.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=56)
sf25.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=70)
sf26.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=84)
sf27.to_excel(ew, sheet_name='西区', index=False, header=False, startrow=98)
# ['北区']
sf36.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=0)
sf37.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=14)
sf38.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=28)
sf39.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=42)
sf40.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=56)
sf41.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=70)
sf42.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=84)
sf43.to_excel(ew, sheet_name='北区', index=False, header=False, startrow=98)
#多个工作簿测试使用
# UNCLOSED
sf9.to_excel(ew, sheet_name='UNCLOSED', index=False, header=True, startrow=0)
# YESTERDAY NEW
sf10.to_excel(ew,sheet_name='Yesterday NEW', index=False, header=True,startrow=0)
# YESTERDAY CLOSED
sf11.to_excel(ew, sheet_name='Yesterday CLOSED', index=False, header=True, startrow=0)
ew.save() # 写入文件
print('excel写入成功')
#将excel发送代码
'''开始生成emal邮件'''
subject = f'{report_name}' # 邮件主题
# message = 'bill.ceshi' #邮件内容
email_content = f'''
{email_text}
<strong>{murl}/{report_name}-{time}.xlsx</strong>
'''
text_content = '-------------'
from_email = 'serviceai@zh.value-exch.com.cn' # 发件人
# 'Figo.FEI@value-exch.com'
recipient_list = Recipient # 收件人
html_content = email_content
# html_message=aw
msg = EmailMultiAlternatives(subject, text_content, from_email, recipient_list)
msg.attach_alternative(html_content, "text/html")
'''添加html附件之后发送'''
if system == 'win32':
print('当前系统为windows，启用Windows方案')
# Windows发送
msg.attach_file(lujing)
print('windows系统附件加载完成')
elif system == 'linux':
print("当前系统为linux，启用linux方案")
# linux发送
msg.attach_file(lujing)
print("linux系统中附件加载完成")
try:
msg.send()
return HttpResponse('这个是baobiao3 函数发送的邮件')
except:
return HttpResponse('邮件发送失败，请联系bill.wang@value-exch.com')
baobiao2.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Awesome-pyecharts</title>
<style>
.fl-table {
margin: 1px;/*报表左边距*/
border-radius: 5px;
font-size: 12px;
border: none;
border-collapse: collapse;
max-width: 100%;
white-space: nowrap;
word-break: keep-all;
width:100%;/* 和注释2 搭配 使得table铺满整个屏幕*/
height:100%;/* 和注释2 搭配 使得table铺满整个屏幕*/
overflow:scroll;/* 和注释2 搭配 使得table铺满整个屏幕*/
}
.fl-table th {
text-align: left;
font-size: 20px;
}
.fl-table tr {
display: table-row;
vertical-align: inherit;
border-color: inherit;
}
.fl-table tr:hover td {
background: #00d1b2;
color: #F8F8F8;
}
.fl-table td, .fl-table th {
border-style: none;
border-top: 1px solid #dbdbdb;
border-left: 1px solid #dbdbdb;
border-bottom: 3px solid #dbdbdb;
border-right: 1px solid #dbdbdb;
padding: .5em .55em;
font-size: 15px;
}
.fl-table td {
border-style: none;
font-size: 2px;/*2，修改单元格的字体大小*/
vertical-align: center;
border-bottom: 1px solid #dbdbdb;
border-left: 1px solid #dbdbdb;
border-right: 1px solid #dbdbdb;
height: 20px; /*修改单元格高度*/
width:40px;/*单元格宽度*/
}
.fl-table tr:nth-child(odd) {
background: #F8F8F8;
}
table tr:nth-child(1){border-top: 1px solid #ccc;}
table tr:nth-child(6){background-color:#003366; font-weight:600;color:#F8F8F8;}/*给第6行加上背景颜色*/
table{
text-align: center;
width:120px;
}
table td:nth-child(3){ background-color:#003366; font-weight:600;color:#F8F8F8;}/*第三列加背景颜色，字体加粗*/
</style>
</head>
<body>
<!--第一个单元格排序规则不是按照视图中的排序规则，而是按照之前epsm 中的excel中的排序规则 ccti==all-->
<p class="title" style="font-size: 18px; font-weight:bold;">{{ dt2 }}</p>
{% for r6 in c %}
<p class="title" style="font-size: 18px; font-weight:bold;" > {{ name }}-{{ r6.5.1 }}</p>
<table class="fl-table">
<tbody>
{% for r7 in r6 %}
<tr>
<td>{{ r7.0 }}</td>
<td>{{ r7.1 }}</td>
<td>{{ r7.2 }}</td>
<td>{{ r7.3 }}</td>
<td>{{ r7.4 }}</td>
<td>{{ r7.5 }}</td>
<td>{{ r7.6 }}</td>
<td>{{ r7.7 }}</td>
<td>{{ r7.8 }}</td>
<td>{{ r7.9 }}</td>
<td>{{ r7.10 }}</td>
<td>{{ r7.11 }}</td>
<td>{{ r7.12 }}</td>
<td>{{ r7.13 }}</td>
<td>{{ r7.14 }}</td>
<td>{{ r7.15 }}</td>
<td>{{ r7.16 }}</td>
<td>{{ r7.17 }}</td>
<td>{{ r7.18 }}</td>
<td>{{ r7.19 }}</td>
<td>{{ r7.20 }}</td>
<td>{{ r7.21 }}</td>
<td>{{ r7.22 }}</td>
<td>{{ r7.23 }}</td>
<td>{{ r7.24 }}</td>
<td>{{ r7.25 }}</td>
<td>{{ r7.26 }}</td>
<td>{{ r7.27 }}</td>
<td>{{ r7.28 }}</td>
<td>{{ r7.29 }}</td>
<td>{{ r7.30 }}</td>
<td>{{ r7.31 }}</td>
<td>{{ r7.32 }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endfor %}
</body>
</html>
6310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
