目录
仪表盘整体项目文件夹结构
demo应用效果
demo应用
demo应用的sql语句
demo应用定义的查询mysql类 在demo/views.py文件中
demo应用部分完整代码
urls.py
views.py
index.html 没有模糊背景
bindex.html 有模糊背景
demo2应用
demo2应用效果
2,将demo和demo2应用结合在一起
3,html报表
demo2实时仪表盘使用sql
demo2 html【竖着】报表使用sql
demo2 html【横着】报表使用sql
sqlserver数据库查询类
实时仪表盘代码
html报表【样式 竖着】
html报表 【样式 横着】
将html报表保存为html文件通过访问指定url来调用django视图函数使用邮件发送给指定收件人
demo2 应用部分完整代码
urls.py
views.py
sql.py
3index.html
仪表盘整体项目文件夹结构
demo和demo2共用一个文件夹
demo是otrs工单数据库使用的是mysql
demo2是services ai数据库使用的是sqlserver
demo应用效果
样式一
样式二
demo应用
demo应用文件夹结构
demo应用的sql语句
demo应用【otrs工单系统】使用的sql
demo的sql存放在demo应用下的views.py文件中
#查询数据库的语句
#1查询74队列每个服务人员手中正在处理的工单数量
sql1='''
select users.login,COUNT(ticket.id)
from ticket left join users on ticket.user_id=users.id
where ticket.queue_id=74 and ticket.ticket_state_id in (1,4)
GROUP BY users.login
ORDER BY COUNT(ticket.id) DESC;
'''
#2查询队列中正在开着的工单数量TOP10
sql2='''
select t1.`name` 所属队列 ,COUNT(t2.tn)
from queue t1 join ticket t2 on t1.id=t2.queue_id
where t2.ticket_state_id in (1,4)
group by t1.`name`
order by COUNT(t2.tn) desc
limit 10;
'''#3所有开着工单的详细信息
sql3='''
select t2.customer_id 客户名称,t2.tn 工单id ,t2.title 工单标题,t2.create_time 工单创建时间, t1.`name` 工单即时状态,t3.`name` 所属队列, t4.login 工单负责人
from ticket_state t1 join ticket t2 on t1.id=t2.ticket_state_id join queue t3 on t2.queue_id=t3.id join users t4 on t2.user_id=t4.id
where t2.ticket_state_id in (1,4);
'''#4近七日新建和关闭工单数量
sql4='''
select A.cr_time 时间,A.`新建数量`,B1.`关闭数量`
from (
select DATE_FORMAT(create_time, '%m-%d') cr_time,count(id) 新建数量
from ticket
where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
GROUP BY cr_time) A
join
(
SELECT date,count(ticket_id) 关闭数量
FROM (
SELECT DATE_FORMAT(max( CREATE_TIME),'%m-%d' ) AS date,ticket_id
FROM ticket_history
WHERE state_id IN ( 2, 3, 10 ) and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
GROUP BY ticket_id ) B
group by date) B1
ON A.cr_time=B1.date;
'''
#5每小时创建工单数量
sql5='''
select hour(t2.create_time) as h1,count(*)
from customer_company t1 left join ticket t2 on t1.customer_id=t2.customer_id
where date(t2.create_time) =curdate()
group by h1;
'''#6每个小时内将要超时的工单信息 customer_id not in ('RRG')
sql6='''
SELECT t1.customer_id 客户名称,t1.tn 工单id,t1.title 工单标题,t1.create_time 工单创建时间,t2.login 工单所有者
FROMticket t1 join users t2 on t1.user_id=t2.id
WHEREt1.ticket_state_id IN ( 1, 4 ) AND t1.escalation_time not in ('0')AND TIMEDIFF( from_unixtime( t1.escalation_time, '%Y-%m-%d %H:%i:%s' ), t1.create_time )<= '05:00:00'
'''demo应用定义的查询mysql类 在demo/views.py文件中
#查询数据的类
class Sql_chaxun():'''封装一个查询数据库的类'''def __init__(self,sql):self.host1 = 'pc-uf6a1v5f4adl90846.mysql.polardb.rds.aliyuncs.com'self.user1 = '你猜' #用户名self.passwd1 = '你猜' #密码self.database1 = '你猜' #数据库名称self.sql=sqlself.sqlj=''def sql_select(self):try:con=pymysql.connect(host=self.host1,port=3306,user=self.user1,passwd=self.passwd1,db=self.database1,charset='utf8')print('数据库连接成功')#global sqljg #定义全局对象self.sqlj = pd.read_sql(self.sql, con) # 1查询74队列每个服务人员手中正在处理的工单数量con.commit() # 提交所有对数据库的操作，把更新写入数据库con.close()print('成功写入并关闭')except Exception as err:print(err)return self.sqljdemo应用部分完整代码
urls.py
from django.urls import path
from .views import *urlpatterns=[#第一个bar视图path('bar',ChartView.as_view(), name='demo'),#第二个bar2视图path('bar2',ChartView2.as_view(),name='demo'),#第三个pie1视图path('pie1',ChartView3.as_view(),name='demo'),#第四个line1 视图path('line1',ChartView4.as_view(),name='demo'),path('line2',ChartView5.as_view(),name='demo'),path('bar3',ChartView6.as_view(),name='demo'),path('index', IndexView.as_view(), name='demo'),path('bindex',IndexView2.as_view(),name='demo'),path('cindex',IndexView3.as_view(),name='demo')
]
views.py
import json
from random import randrangefrom django.http import HttpResponse
from rest_framework.views import APIView#导入模板
from django.shortcuts import render#作图和连接数据的的模块
import pyecharts
from pyecharts.charts import Bar #导入柱形图
from pyecharts import options as opts #导入配置
import pymysql
import pandas as pd
from pyecharts.globals import ThemeType #导入主题
from pyecharts.charts import Pie,Line, Grid #导入饼图 折线图
from pyecharts.commons.utils import JsCode#做表格需要模块
from pyecharts.components import Table
from pyecharts.options import ComponentTitleOpts
#------------------------------------------------------#查询数据的类
class Sql_chaxun():'''封装一个查询数据库的类'''def __init__(self,sql):self.host1 = 'pc-uf6a1v5f4adl90846.mysql.polardb.rds.aliyuncs.com'self.user1 = '你猜' #用户名self.passwd1 = '你猜' #密码self.database1 = '你猜' #数据库名称self.sql=sqlself.sqlj=''def sql_select(self):try:con=pymysql.connect(host=self.host1,port=3306,user=self.user1,passwd=self.passwd1,db=self.database1,charset='utf8')print('数据库连接成功')#global sqljg #定义全局对象self.sqlj = pd.read_sql(self.sql, con) # 1查询74队列每个服务人员手中正在处理的工单数量con.commit() # 提交所有对数据库的操作，把更新写入数据库con.close()print('成功写入并关闭')except Exception as err:print(err)return self.sqlj#查询数据库的语句
#1查询74队列每个服务人员手中正在处理的工单数量
sql1='''
select users.login,COUNT(ticket.id)
from ticket left join users on ticket.user_id=users.id
where ticket.queue_id=74 and ticket.ticket_state_id in (1,4)
GROUP BY users.login
ORDER BY COUNT(ticket.id) DESC;
'''
#2查询队列中正在开着的工单数量TOP10
sql2='''
select t1.`name` 所属队列 ,COUNT(t2.tn)
from queue t1 join ticket t2 on t1.id=t2.queue_id
where t2.ticket_state_id in (1,4)
group by t1.`name`
order by COUNT(t2.tn) desc
limit 10;
'''#3所有开着工单的详细信息
sql3='''
select t2.customer_id 客户名称,t2.tn 工单id ,t2.title 工单标题,t2.create_time 工单创建时间, t1.`name` 工单即时状态,t3.`name` 所属队列, t4.login 工单负责人
from ticket_state t1 join ticket t2 on t1.id=t2.ticket_state_id join queue t3 on t2.queue_id=t3.id join users t4 on t2.user_id=t4.id
where t2.ticket_state_id in (1,4);
'''#4近七日新建和关闭工单数量
sql4='''
select A.cr_time 时间,A.`新建数量`,B1.`关闭数量`
from (
select DATE_FORMAT(create_time, '%m-%d') cr_time,count(id) 新建数量
from ticket
where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
GROUP BY cr_time) A
join
(
SELECT date,count(ticket_id) 关闭数量
FROM (
SELECT DATE_FORMAT(max( CREATE_TIME),'%m-%d' ) AS date,ticket_id
FROM ticket_history
WHERE state_id IN ( 2, 3, 10 ) and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
GROUP BY ticket_id ) B
group by date) B1
ON A.cr_time=B1.date;
'''
#5每小时创建工单数量
sql5='''
select hour(t2.create_time) as h1,count(*)
from customer_company t1 left join ticket t2 on t1.customer_id=t2.customer_id
where date(t2.create_time) =curdate()
group by h1;
'''#6每个小时内将要超时的工单信息 customer_id not in ('RRG')
sql6='''
SELECT t1.customer_id 客户名称,t1.tn 工单id,t1.title 工单标题,t1.create_time 工单创建时间,t2.login 工单所有者
FROMticket t1 join users t2 on t1.user_id=t2.id
WHEREt1.ticket_state_id IN ( 1, 4 ) AND t1.escalation_time not in ('0')AND TIMEDIFF( from_unixtime( t1.escalation_time, '%Y-%m-%d %H:%i:%s' ), t1.create_time )<= '05:00:00'
'''
# Create your views here.
def response_as_json(data):json_str = json.dumps(data)response = HttpResponse(json_str,content_type="application/json",)response["Access-Control-Allow-Origin"] = "*"return responsedef json_response(data, code=200):data = {"code": code,"msg": "success","data": data,}return response_as_json(data)def json_error(error_string="error", code=500, **kwargs):data = {"code": code,"msg": error_string,"data": {}}data.update(kwargs)return response_as_json(data)JsonResponse = json_response
JsonError = json_errordef bar1():#1查询74队列每个服务人员手中正在处理的工单数量w=Sql_chaxun(sql1) #使用查询数据库的类sql1j=w.sql_select()c1=(Bar(init_opts=opts.InitOpts( width='400px',height='400px')).add_yaxis('WTCCN-VEISW服务人员工单数量',list(sql1j['COUNT(ticket.id)'])).add_xaxis((list(sql1j['login'])))#bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='SW工程师实时处理工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white', font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='3')),axislabel_opts=opts.LabelOpts(font_size=12,position='top',rotate=45)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='4')))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 15, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() #设置对象)return c1def bar2():# 2查询队列中正在开着的工单数量TOP10w2=Sql_chaxun(sql2)sql2j=w2.sql_select()c2 = (Bar().add_xaxis(list(sql2j['所属队列'])).add_yaxis('队列处理中工单数量TOP10', (list(sql2j['COUNT(t2.tn)'])),label_opts=opts.LabelOpts(position='top',color='lightcyan', font_size=20))#.reversal_axis() # 将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='队列处理中工单数量TOP10', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='3')),axislabel_opts=opts.LabelOpts(font_size=8,position='top',rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='4')),axislabel_opts=opts.LabelOpts(font_size=9,position='top'))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 5, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() # 设置对象)return c2def pie1():# 3所有开着工单的详细信息 饼图--饼图相比其他图形 要求数据是一对一对的w3=Sql_chaxun(sql3)sql3j=w3.sql_select()c1 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).index)c2 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).values)c3 = (Pie().add("", [list(z) for z in zip(c1['客户名称'], c2[0])]).set_colors(["aqua", "greenyellow", "lightcyan", "red", "pink", "orange", "purple",'deeppink','darkred','darkslategray']).set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='处理中工单数量', pos_left=300,pos_top=1,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='3')),axislabel_opts=opts.LabelOpts(font_size=17)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='4'))))# .set_global_opts(title_opts=opts.TitleOpts(title="Pie-设置颜色")).set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}")).dump_options_with_quotes())return c3def line1():# 4近七日新建和关闭工单数量w4=Sql_chaxun(sql4)sql4j=w4.sql_select()c4 = (Line().add_xaxis(list(sql4j['时间'])).add_yaxis('新建数量', list(sql4j['新建数量']), label_opts=opts.LabelOpts(position='top', color='mediumspringgreen', font_size=20, ),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='mediumspringgreen')).add_yaxis("关闭数量", list(sql4j['关闭数量']), label_opts=opts.LabelOpts(position='bottom', color='blueviolet', font_size=20),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='blueviolet')).set_global_opts(legend_opts=opts.LegendOpts(is_show=True,pos_top=20,textstyle_opts=opts.TextStyleOpts(color='Brown',font_size=15)),title_opts=opts.TitleOpts(title='近七日新建和关闭工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')),axislabel_opts=opts.LabelOpts(font_size=12)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')))).dump_options_with_quotes())return c4def line2():# 5每小时创建工单数量"""参考地址: https://gallery.echartsjs.com/editor.html?c=xEyDk1hwBx"""w5=Sql_chaxun(sql5)sql5j=w5.sql_select()x_data = list(sql5j['h1'])y_data = list(sql5j['count(*)'])c5 = (Line().add_xaxis(x_data).add_yaxis("每小时创建工单数量()", y_data)# legend_opts 图例配置项 配置了图例为圆形 最后还是直接关闭了图例配置项，因为图裂旁边的文字搞不定# title_opts 配置了标题文字内容 标题文字颜色 标题文字大小.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='每小时创建工单数量', pos_left='120',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=3)),axislabel_opts=opts.LabelOpts(font_size=17),type_='value',split_number=12),yaxis_opts=opts.AxisOpts(is_show=False,axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=4)))).set_series_opts(# 标签配置项 配置了标签颜色为 黄色label_opts=opts.LabelOpts(color='yellow', font_size=13),# 线样式配置项 配置了线宽 配置了颜色linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='rgb(128, 128, 128)'),# 标记点配置项目 配置了最大值markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(type_="max", symbol='circle')],symbol_size=20),).dump_options_with_quotes())return c5def bar3():w6=Sql_chaxun(sql6)sql6j=w6.sql_select()c3 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).index)c4 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).values)c6 =(Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK)).add_yaxis('将要超时{5H} & 已超时', list(c4[0])).add_xaxis(list(c3['客户名称']))# bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='将要超时{5H} & 已超时', pos_left='130',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=3)),axislabel_opts=opts.LabelOpts(font_size=12, position='top', rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=4)),)).dump_options_with_quotes())return c6#bar()
class ChartView(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar1()))#bar2()
class ChartView2(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar2()))#pie1()
class ChartView3(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(pie1()))#line1
class ChartView4(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(line1()))#line2
class ChartView5(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(line2()))#bar3
class ChartView6(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar3()))#有放大效果
class IndexView(APIView):def get(self, request, *args, **kwargs):return render(request,'index.html')#return HttpResponse(content=open("./templates/index.html").read())#毛玻璃
class IndexView2(APIView):def get(self, request, *args, **kwargs):return render(request,'bindex.html')#没有放大效果
class IndexView3(APIView):def get(self, request, *args, **kwargs):return render(request,'cindex.html')index.html 没有模糊背景
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>ePSM工单系统综合看板</title><script src="https://cdn.bootcss.com/jquery/3.0.0/jquery.min.js"></script><script type="text/javascript" src="https://assets.pyecharts.org/assets/echarts.min.js"></script><style type="text/css">.div {border-top: rgb(255, 255, 255, 1) solid 11px !important;border: rgb(255, 255, 255, 1) solid 0;}/* 页面背景颜色*/.body {background-color: rgba(13,30,103,1.000);}/*页面标题样式*/.ziti {text-align: center;color: rgba(0, 0, 0, 1);font-weight: 400;background-color: rgba(255, 161, 61, 1);font-size: 38px;letter-spacing: 5px;}/*时间样式*/.ziti1 {text-align: right;color: rgba(0, 0, 0, 6);font-weight: 400;background-color: rgba(255, 161, 61, 1);font-size: 17px;letter-spacing: 3px;}/* 清除浏览器的内外边距 */* {margin: 0;padding: 0;}/* <!-- 情况一：没有父盒子，即父盒子就是浏览器 --> *//*上右*/.a {width: 33%;height: 49%;position: absolute;top: 9%;right: 0.2%;}/*上中*/.b {width: 33%;height: 49%;position: absolute;top: 9%;right: 33.5%;}/*上左*/.c {width: 33%;height: 49%;position: absolute;top: 9%;right: 66.8%;}/*下右*/.d {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 0.2%;}/*下中*/.e {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 33.5%;}/*下左*/.f {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 66.8%;}/* div放大效果*//*上右*/.a1:HOVER{cursor: pointer;transition: all 1s;transform: translate(-400px,200px) scale(2);background-color:#83CFD1;z-index:1;}/*上中*/.b2:HOVER{cursor: pointer;transition: all 1s;transform: translate(0px,200px) scale(2);background-color:#83CFD1;z-index:1;}/*上左*/.c2:HOVER{cursor: pointer;transition: all 1s;transform: translate(400px,200px) scale(2);background-color:#83CFD1;z-index:1;}/*下右*/.d2:HOVER{cursor: pointer;transition: all 1s;transform: translate(-400px,-200px) scale(2);background-color:#83CFD1;z-index:1;}/*下中*/.e2:HOVER{cursor: pointer;transition: all 1s;transform: translate(0px,-200px) scale(2);background-color:#83CFD1;z-index:1;}/*下左*/.f2:HOVER{cursor: pointer;transition: all 1s;transform: translate(400px,-200px) scale(2);background-color:#83CFD1;z-index:1;}</style><script>function time(){var date = new Date();var year = date.getFullYear();var month = date.getMonth()+1;var day = date.getDate();var week = date.getDay();week="星期"+"日一二三四五六".charAt(week);/*switch(week){case 1:week="星期一";break;case 2:week="星期二";break;case 3:week="星期三";break;case 4:week="星期四";break;case 5:week="星期五";break;case 6:week="星期六";break;case 0:week="星期日";break;}*/var hour =date.getHours();hour=hour<10?"0"+hour:hour;var minute =date.getMinutes();minute=minute<10?"0"+minute:minute;var second = date.getSeconds();second=second<10?"0"+second:second;var currentTime = year+"-"+month+"-"+day+" "+week+" "+hour+":"+minute+":"+second;document.getElementById("time").innerHTML=currentTime;}setInterval("time()",1000);</script></head>
<body class="body"><div class="ziti">ePSM工单系统信息看板<span id="time" class="ziti1"></span></div><br/><!-- 情况一：没有父盒子，即父盒子就是浏览器 --><div id="bar" class="a a1"></div><div id="bar2" class="b b2"></div><div id="pie1" class="c c2"></div><div id="line1" class="d d2"></div><div id="line2" class="e e2"></div><div id="bar3" class="f f2"></div><br><!--bar图形，第一个图形--><script>var char1 = echarts.init(document.getElementById('bar'), 'white1', {renderer: 'canvas'});$(function () {fetchData(char1);setInterval(fetchData, 30000);});function fetchData() {$.ajax({type: "GET",url: "/demo/bar",async:false,dataType: 'json',success: function (result) {char1.setOption(result.data);}});}</script><!--bar2图形，第二个图形--><script>var char2 = echarts.init(document.getElementById('bar2'), 'white', {renderer: 'canvas'});$(function () {fetchDat(char2);setInterval(fetchDat, 30000);});function fetchDat() {$.ajax({type: "GET",url: "/demo/bar2",async:false,dataType: 'json',success: function (result1) {char2.setOption(result1.data);}});}</script><!--pie饼图，第三个图形--><script>var char3 = echarts.init(document.getElementById('pie1'), 'white', {renderer: 'canvas'});$(function () {fetchDat1(char3);setInterval(fetchDat1, 60000);});function fetchDat1() {$.ajax({type: "GET",url: "/demo/pie1",dataType: 'json',success: function (result2) {char3.setOption(result2.data);}});}</script><br><!--line1折线图，第四个图形--><script>var char4 = echarts.init(document.getElementById('line1'), 'white', {renderer: 'canvas'});$(function () {fetchDat2(char4);setInterval(fetchDat2, 40000);});function fetchDat2() {$.ajax({type: "GET",url: "/demo/line1",dataType: 'json',success: function (result2) {char4.setOption(result2.data);}});}</script><!--line2折线图，第五个图形--><script>var char5 = echarts.init(document.getElementById('line2'), 'white', {renderer: 'canvas'});$(function () {fetchDat3(char5);setInterval(fetchDat3, 50000);});function fetchDat3() {$.ajax({type: "GET",url: "/demo/line2",dataType: 'json',success: function (result2) {char5.setOption(result2.data);}});}</script><!--bar3折线图，第六个图形--><script>var char6 = echarts.init(document.getElementById('bar3'), 'white', {renderer: 'canvas'});$(function () {fetchDat4(char6);setInterval(fetchDat4, 40000);});function fetchDat4() {$.ajax({type: "GET",url: "/demo/bar3",dataType: 'json',success: function (result2) {char6.setOption(result2.data);}});}</script></body>
</html>bindex.html 有模糊背景
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>ePSM工单系统综合看板</title><script src="https://cdn.bootcss.com/jquery/3.0.0/jquery.min.js"></script><script type="text/javascript" src="https://assets.pyecharts.org/assets/echarts.min.js"></script><style type="text/css">.div {border-top: rgb(255, 255, 255, 1) solid 11px !important;border: rgb(255, 255, 255, 1) solid 0;}.ui {}#box {width:100%;display: flex;flex-wrap: wrap;justify-content: center;align-items: center;}#box-item{width: 30%;/* border: 1px solid aquamarine; */margin: 15px;display: flex;justify-content: center;align-items: center;}.daxiao {width: 500px !important;height: 400px !important;/* float: left; *//* border: 1px solid rgb(255, 2, 2); */position: relative;padding-top: 10px;}.daxiao::after {content:"" ;position: absolute;width: 100%;height: 100%;top: 0;left: 0;/* z-index: -1; */background-color: #fff;border-radius: 5%;opacity: 0.3;filter: blur(10px);}/* .fl{float:left;}.fr{float:right;} *//* 页面背景颜色*/.body {background-color: rgba(13,30,103,1.000);}/*页面标题样式*/.ziti {text-align: center;color: rgba(0, 0, 0, 1);font-weight: 400;background-color: rgba(255, 161, 61, 1);font-size: 38px;letter-spacing: 5px;}/*时间样式*/.ziti1 {text-align: right;color: rgba(0, 0, 0, 6);font-weight: 400;background-color: rgba(255, 161, 61, 1);font-size: 17px;letter-spacing: 3px;}</style><script>function time(){var date = new Date();var year = date.getFullYear();var month = date.getMonth()+1;var day = date.getDate();var week = date.getDay();week="星期"+"日一二三四五六".charAt(week);/*switch(week){case 1:week="星期一";break;case 2:week="星期二";break;case 3:week="星期三";break;case 4:week="星期四";break;case 5:week="星期五";break;case 6:week="星期六";break;case 0:week="星期日";break;}*/var hour =date.getHours();hour=hour<10?"0"+hour:hour;var minute =date.getMinutes();minute=minute<10?"0"+minute:minute;var second = date.getSeconds();second=second<10?"0"+second:second;var currentTime = year+"-"+month+"-"+day+" "+week+" "+hour+":"+minute+":"+second;document.getElementById("time").innerHTML=currentTime;}setInterval("time()",1000);</script></head>
<body class="body"><div class="ziti">ePSM工单系统信息看板<span id="time" class="ziti1"></span></div><div id="box"><div id="box-item"><div id="bar" class="daxiao fr" ></div></div><div id="box-item"><div id="bar2" class="daxiao fl" ></div></div><div id="box-item"><div id="pie1" class="daxiao fl" ></div></div><div id="box-item"><div id="line1" class="daxiao fl" ></div></div><div id="box-item"><div id="line2" class="daxiao fl" ></div></div><div id="box-item"><div id="bar3" class="daxiao fl" ></div></div></div><!--bar图形，第一个图形--><script>var char1 = echarts.init(document.getElementById('bar'), 'white1', {renderer: 'canvas'});$(function () {fetchData(char1);setInterval(fetchData, 30000);});function fetchData() {$.ajax({type: "GET",url: "/demo/bar",async:false,dataType: 'json',success: function (result) {char1.setOption(result.data);}});}</script><!--bar2图形，第二个图形--><script>var char2 = echarts.init(document.getElementById('bar2'), 'white', {renderer: 'canvas'});$(function () {fetchDat(char2);setInterval(fetchDat, 30000);});function fetchDat() {$.ajax({type: "GET",url: "/demo/bar2",async:false,dataType: 'json',success: function (result1) {char2.setOption(result1.data);}});}</script><!--pie饼图，第三个图形--><script>var char3 = echarts.init(document.getElementById('pie1'), 'white', {renderer: 'canvas'});$(function () {fetchDat1(char3);setInterval(fetchDat1, 60000);});function fetchDat1() {$.ajax({type: "GET",url: "/demo/pie1",dataType: 'json',success: function (result2) {char3.setOption(result2.data);}});}</script><br><!--line1折线图，第四个图形--><script>var char4 = echarts.init(document.getElementById('line1'), 'white', {renderer: 'canvas'});$(function () {fetchDat2(char4);setInterval(fetchDat2, 40000);});function fetchDat2() {$.ajax({type: "GET",url: "/demo/line1",dataType: 'json',success: function (result2) {char4.setOption(result2.data);}});}</script><!--line2折线图，第五个图形--><script>var char5 = echarts.init(document.getElementById('line2'), 'white', {renderer: 'canvas'});$(function () {fetchDat3(char5);setInterval(fetchDat3, 40000);});function fetchDat3() {$.ajax({type: "GET",url: "/demo/line2",dataType: 'json',success: function (result2) {char5.setOption(result2.data);}});}</script><!--bar3折线图，第六个图形--><script>var char6 = echarts.init(document.getElementById('bar3'), 'white', {renderer: 'canvas'});$(function () {fetchDat4(char6);setInterval(fetchDat4, 40000);});function fetchDat4() {$.ajax({type: "GET",url: "/demo/bar3",dataType: 'json',success: function (result2) {char6.setOption(result2.data);}});}</script></body>
</html>demo2应用
demo2应用效果
1，实时看板
2,将demo和demo2应用结合在一起
3,html报表
demo2应用是services ai工单系统使用的是sqlserver数据库
demo2应用融合了实时仪表盘和html报表所以demo2使用的sql语句单独放在了demo2/sql.py文件中
demo2实时仪表盘使用sql
#查询数据库的语句
#1 每个服务人员处理中工单数量 [已完成]
sql1='''
select t2.E_MAIL 处理人, count(t1.TICKET_ID) sl
from
VAPP_ITEM t1 join ORG_CONTACT t2 ON t1.assigned_to_contact_id=t2.ROW_ID
where t1.TICKET_STATUS IN ('Active','New','Queued')
group by t2.E_MAIL
'''#2, 查询队列（services ai中的支持组）中的正在开着工单数量top10 [已完成]
#目前没有10个组，所以不能用limit 10
sql2='''
select assigned_to_group_name 支持组,COUNT(TICKET_ID) value1
from VAPP_ITEM
where TICKET_STATUS in ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation')
GROUP BY assigned_to_group_name
order by value1 desc
'''#3 所有开着工单的详细信息 [已完成]
sql3='''SELECT
vi.ROW_ID '系统ID',
(SELECT TOP 1 metric_value from VSLA_METRIC_CALCULATIONS as sla WHERE sla.ticket_id=vi.ROW_ID) as 'SLA所用时间(Min)',
sla_compliance_status_indicator as 'SLA状态',
ticket_identifier as '工单号',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=561) as '内部工单号',
TICKET_STATUS as '状态',
person1_org_name as '门店编号',
closed_by_group_name as '关闭组',
(closed_by_name+'.'+closed_by_last_name) as '关闭人',
ticket_description as '问题描述',
last_worklog as '最后工作日志',
DATEADD(SECOND, last_worklog_date, '1970/1/1 08:00:00') as '最后工作日志时间',
(SELECT top 1 [LVL1_CAT] +' - '+ [LVL2_CAT] FROM [VIC_HIERARCHICAL_TREE_DATA] where CHT_ID = (SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=557)) as '解决分类',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as '解决办法',
(created_by_name+'.'+created_by_last_name) as '创建人',
DATEADD(SECOND, CREATED_DATE, '1970/1/1 08:00:00') as '创建时间',
(closed_by_name+'.'+closed_by_last_name) as '关单人',
DATEADD(SECOND, resolved_date, '1970/1/1 08:00:00') as '解决时间',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') as '关闭时间',
CCTI_CLASS as '类',
CCTI_CATEGORY as '类别',
CCTI_TYPE as '类型',
CCTI_ITEM as '项目',
sla_target_name as 'SLA',
(SELECT top 1 DATEADD(SECOND, [status_created_date], '1970/1/1 08:00:00') FROM VAPP_HISTORY vh where vh.row_id = vi.ROW_ID and status='Active' order by status_created_date) as '首次响应时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as '联系人',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as '联系电话',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=552) as '上门人',
(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) as '上门时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=554) as '第二次上门人',
(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=555) as '第二次上门时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=556) as '第三次上门人',
(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=558) as '第三次上门时间',
person1_hierarchical_path as '组织',
person1_last_name as '城市',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=559) as 'CSS',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=560) as '反馈意见',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=548) as '客户单号'
from VAPP_ITEM as vi WHERE TICKET_STATUS in ('Active','New','Queued');
'''#4 近七日新建和关闭工单数量 [已完成]
sql4='''
select a.cr_time 时间, a.asl 新建数量,b.bsl 关闭数量
from (select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120) cr_time, COUNT(*) asl
from VAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120),GETDATE())<=7
group by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120)) a
join
(select CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120) cl_time, COUNT(*) bsl
from VAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120),GETDATE())<=7
group by CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120)) b
on a.cr_time=b.cl_time;
'''
#5 每小时创建工单数量 [已完成]
sql5='''
select DATEPART(hh, (DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00'))) 时间,COUNT(*) sl
from VAPP_ITEM
where CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120)=convert(VARCHAR(10),getdate(),120)
group by DATEPART(hh, (DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')))
'''#6sla将要超时以及已超时
sql6='''
select [person1_root_org_name] as '客户名称',COUNT(TICKET_ID) 数量
from
VAPP_ITEM
where sla_compliance_status_indicator in ('Breached SLA')
GROUP BY
[person1_root_org_name]
'''
demo2 html【竖着】报表使用sql
sql7='''
/*
@kehu 定义查询的客户
@tianshu 定义查询的天数
@sla_target_name 定义查询工单的优先级
@CCTI_CLASS 定义ccti的类型
*/
declare @kehu VARCHAR(20)
set @kehu ='WTC';declare @tianshu int
set @tianshu=30declare @sla_target_name varchar(20)
set @sla_target_name='WTC - P2'declare @CCTI_CLASS varchar(20)
set @CCTI_CLASS='HW'select t1.cr_date 日期,t2.Total_call,t3.Unclosed,t4.Scheduled,t5.P1_call,t6.Over_SLA,/*datename(day,t1.cr_date) 日期,*/t7.SLA_Met,t8.Worst_TAT,t9.Avg_onsite_time,t10.No_onsite_time,t11.Onsite_1,t12.Remote_Fixed
from(select convert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_datefrom master..spt_values where type = 'P'and number < @tianshu) as t1
left join
/*Total Call，最近30天每日创建工单数量 不统计删除工单*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) Total_callfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')group by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t2 on t1.cr_date=t2.cr_date
left join
/*Unclosed 最近30天状态为开着的未关单数量，已创建时间排序*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) Unclosedfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation') and person1_root_org_name = @kehugroup by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t3 on t1.cr_date=t3.cr_date
left join
/* Scheduled 最近30天每天sla应到期工单数量且 工单状态为未关 （按照到期时间排序）
确认工单状态
*/(SELECTa.cr_date,COUNT ( TICKET_ID ) Scheduledfrom(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_IDfrom VAPP_ITEM as vi where person1_root_org_name = @kehu and TICKET_STATUS not in ('Closed','Archive','Request - Delete','Approved','Submitted') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as aGROUP BY a.cr_date)as t4 on t1.cr_date=t4.cr_date
left join
/* P1_call 最近30天工单优先级最高的数量（按照创建时间排序）*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) P1_call
fromVAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and sla_target_name=@sla_target_nameand person1_root_org_name = @kehuand TICKET_STATUS not in ('Request - Delete','Approved','Submitted')
GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t5on t1.cr_date=t5.cr_date
left join
/*Over_SLA 最近30天工单状态已超时工单数量 按照创建时间排序 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) Over_SLAfromVAPP_ITEMwhere sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t6on t1.cr_date=t6.cr_date
left join
/*SLA_Met 最近30天达成率 按照创建时间排序 公式（totalcall - uniclosed - oversla）/(totalcall - uniclosed ) */(select a.cr_date,/*(a.xjsl/b.gdsl) SLA_Met*/CAST(CAST(a.xjsl*1.0*100 / b.gdsl AS decimal(10,2)) AS varchar(50)) +'%' SLA_Metfrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) xjslfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30AND TICKET_STATUS NOT IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation','Request - Delete','Approved','Submitted')AND sla_compliance_status_indicator NOT IN ('Breached SLA','SLA Not Applied')and person1_root_org_name = @kehugroup 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) gdslfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and TICKET_STATUS NOT IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation','Request - Delete','Approved','Submitted')and person1_root_org_name = @kehuAND sla_compliance_status_indicator NOT IN ('SLA Not Applied')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 t7on t1.cr_date=t7.cr_date
left join
/* Worst_TAT 最近30天每天工单处理花费最大时间 【已完成】
#原理 1，以创建时间排序 2，关单时间- 创建时间 3，工单状态为关闭 4，当天最大值*/(select b.cr_date,max(b.zd) Worst_TATfrom (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) zdfromVAPP_ITEMwhere TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')) bgroup by b.cr_date)as t8on t1.cr_date=t8.cr_date
left join
/* Avg_onsite_time 最近30天平均上门时间 只计算有第一次上门时间的工单 【已完成】
#单位是小时
把删除状态去除统计 */(select t.cr_date,/*avg(t.avg_onsitetime) Avg_onsite_time*/(SELECTCONVERT(VARCHAR(12), avg(t.avg_onsitetime) /60/60 % 24) + ':'+ CONVERT(VARCHAR(2), avg(t.avg_onsitetime) /60 % 60) + ':'+ CONVERT(VARCHAR(2), avg(t.avg_onsitetime) % 60)) Avg_onsite_timefrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,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)) avg_onsitetimefromVAPP_ITEM as viwhere (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())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')and TICKET_ID NOT IN ('472')) as tgroup by t.cr_date)as t9on t1.cr_date=t9.cr_date
left join
/*No_onsite_time 最近30天没有上门时间的工单数量且ccti=hw 按照创建时间排序 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) No_onsite_timefromVAPP_ITEM as viwhere (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 datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t10on t1.cr_date=t10.cr_date
left join
/*Onsite_1 最近30天 重复上门工单数量 统计有第二次和第三次上门时间的工单 按照创建时间分组 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) Onsite_1fromVAPP_ITEM as viwhere (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())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t11on t1.cr_date=t11.cr_date
left join (select a.cr_date,CAST(CAST(a.fengzi*1.0*100 / b.fengmu AS decimal(10,2)) AS varchar(50)) +'%' Remote_Fixedfrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengzifromVAPP_ITEM as viWHERE 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 NULLand person1_root_org_name = @kehuand datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30group 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) fengmufromVAPP_ITEMWHERE TICKET_STATUS IN ('Closed','Archive')and person1_root_org_name = @kehuand datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as bon a.cr_date=b.cr_date) as t12
on t1.cr_date=t12.cr_date
'''
demo2 html【横着】报表使用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(SELECTt1.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt2.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(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(SELECTt3.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 D30FROM (SELECTa.cr_date,COUNT ( TICKET_ID ) cFROM(SELECTCONVERT (VARCHAR ( 10 ),DATEADD(SECOND,(SELECTtop 1 sla_due_byFROMVSLA_AGREEMENT_COMPLIANCE_LIST_UX AS vcWHEREvc.item_id=vi.ROW_IDORDER BYthreshold_sort_order DESC), '1970/1/1 08:00:00'),120) AS 'cr_date',vi.TICKET_IDFROMVAPP_ITEM AS viWHEREperson1_root_org_name = @kehuAND 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) AS aGROUP BYa.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(SELECTt4.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND sla_target_name=@sla_target_nameAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt5.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) cFROMVAPP_ITEMWHEREsla_compliance_status_indicator='Breached SLA'AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND TICKET_STATUS IN ('closed','archive')--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS ENDGROUP BYCONVERT(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_CLASSEND,--判断到此结束'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(SELECTcase 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 D30FROM (SELECTt.cr_date,datename(day,t.cr_date) cFROM(SELECTconvert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_dateFROMmaster..spt_valuesWHEREtype = '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(SELECTt7.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 D30FROM (SELECTa.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)) +'%' cFROM(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) xjslFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))AS ajoin(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) gdslFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))AS bON 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(SELECTt8.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 D30FROM (SELECTb.cr_date,max(b.zd) cFROM(SELECTCONVERT(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) zdFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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) bGROUP BYb.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(SELECTt9.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 D30FROM (selecta.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)) +'%' cfrom(selectCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengzifromVAPP_ITEM as viWHERETICKET_STATUS IN ('Closed','Archive')AND (SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES as vaWHEREva.ITEM_ID=vi.ROW_IDand va.ATTR_ID=553) IS NULLand person1_root_org_name = @kehuand 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 ENDgroup byCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as ajoin(selectCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengmufromVAPP_ITEMWHERETICKET_STATUS IN ('Closed','Archive')and person1_root_org_name = @kehuand 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as bon a.cr_date=b.cr_date) AS t9) AS A
UNION ALL
/* Avg_onsite_time 30天内创建的工单，每日平均上门时间 只计算有第一次上门时间的工单。(不统计删除工单) #单位是小时*/
SELECT'<1:00',--将总平均值转换为时间格式(SELECTCONVERT(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(SELECTt8.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 D30FROM (SELECTb.cr_date,--计算总平均值AVG(b.zd) d,--将日平均值转时间格式(SELECTCONVERT(VARCHAR(12), avg(b.zd) /60/60 % 24) + ':'+ CONVERT(VARCHAR(2), avg(b.zd) /60 % 60) + ':'+ CONVERT(VARCHAR(2), avg(b.zd) % 60)) cFROM(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,--如果上门时间早于创建时间，赋NULL。如果晚于创建时间，计算差值CASE WHEN(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHERE va.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) > DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')THENDATEDIFF(ss,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553))ELSENULLEND AS zd--判断到此结束FROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) is not nullAND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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) bGROUP BYb.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(SELECTt11.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) is null--AND CCTI_CLASS=@CCTI_CLASSAND closed_by_group_name IN ('@group')AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt12.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=row_idAND va.ATTR_ID=555) is not nullAND (SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=row_idAND va.ATTR_ID=558) is not nullAND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) AS t12) AS A
'''使用了函数拼接sql返回给数据库的查询类
def sql_zong(kehu='WTC', CCTI_CLASS=' '):'''返回综合查询的拼接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 intset @tianshu=30/*@sla_target_name 定义查询工单的SLA*/declare @sla_target_name nvarchar(2000)set @sla_target_name=N'WTC-P1'/*@group 定义No Onsite time中没有产生上门时间的硬件组的名称*/declare @group nvarchar(2000)'''sqa2 = r"set @group=N'''L1-HW-SH'',''L1-HW-BJ'',''L1-HW-GZ'',''L1-HW-SZ'''"zonghe = sqa1 + sqa2 + sqa3return zonghe
sqlserver数据库查询类
#定义数据库查询类
class Sql_chaxun2():'''初始化sqlserver 连接属性'''def __init__(self,sql):self.servername='888.888.888.888' #服务器名称self.username='你猜' #账户self.port='你猜' #端口号self.password='你猜' #密码self.dabasename='VEII'self.sql=sqlself.sqlj=''def chaxun(self):con=pymssql.connect(server=self.servername,user=self.username,password=self.password,database=self.dabasename,port=self.port,charset='utf8')print('sqlserver 连接成功')self.sqlj=pd.read_sql(self.sql,con)con.commit() #提交对数据库的操作con.close() #关闭数据库print('关闭数据库成功')return self.sqlj实时仪表盘代码
def bar1():#1每个服务人员处理中工单数量 [已完成]w=Sql_chaxun2(sql1) #使用查询数据库的类sql1j=w.chaxun()c1=(Bar(init_opts=opts.InitOpts( width='400px',height='400px')).add_yaxis('WTCCN-VEISW服务人员工单数量',list(sql1j['sl'])).add_xaxis((list(sql1j['处理人'])))#bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='工程师处理中工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white', font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='3')),axislabel_opts=opts.LabelOpts(font_size=12,position='top',rotate=45)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='4')))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 15, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() #设置对象)return c1def bar2():# 查询队列（services ai中的支持组）中的正在开着工单数量top10w2=Sql_chaxun2(sql2)sql2j=w2.chaxun()c2 = (Bar().add_xaxis(list(sql2j['支持组'])).add_yaxis('支持组中工单数量TOP10', (list(sql2j['value1'])),label_opts=opts.LabelOpts(position='top',color='lightcyan', font_size=20))#.reversal_axis() # 将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='队列处理中工单数量TOP10', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='3')),axislabel_opts=opts.LabelOpts(font_size=8,position='top',rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='4')),axislabel_opts=opts.LabelOpts(font_size=9,position='top'))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 5, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() # 设置对象)return c2def pie1():# 3所有开着工单的详细信息 饼图--饼图相比其他图形 要求数据是一对一对的w3=Sql_chaxun2(sql3)sql3j=w3.chaxun()c1 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).index)c2 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).values)c3 = (Pie().add("", [list(z) for z in zip(c1['客户名称'], c2[0])]).set_colors(["aqua", "greenyellow", "lightcyan", "red", "pink", "orange", "purple",'deeppink','darkred','darkslategray']).set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='处理中工单数量', pos_left=300,pos_top=1,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='3')),axislabel_opts=opts.LabelOpts(font_size=17)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='4'))))# .set_global_opts(title_opts=opts.TitleOpts(title="Pie-设置颜色")).set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}")).dump_options_with_quotes())return c3def line1():# 4近七日新建和关闭工单数量w4=Sql_chaxun2(sql4)sql4j=w4.chaxun()c4 = (Line().add_xaxis(list(sql4j['时间'])).add_yaxis('新建数量', list(sql4j['新建数量']), label_opts=opts.LabelOpts(position='top', color='mediumspringgreen', font_size=20, ),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='mediumspringgreen')).add_yaxis("关闭数量", list(sql4j['关闭数量']), label_opts=opts.LabelOpts(position='bottom', color='blueviolet', font_size=20),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='blueviolet')).set_global_opts(legend_opts=opts.LegendOpts(is_show=True,pos_top=20,textstyle_opts=opts.TextStyleOpts(color='Brown',font_size=15)),title_opts=opts.TitleOpts(title='近七日新建和关闭工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')),axislabel_opts=opts.LabelOpts(font_size=12)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')))).dump_options_with_quotes())return c4def line2():# 5每小时创建工单数量"""参考地址: https://gallery.echartsjs.com/editor.html?c=xEyDk1hwBx"""w5=Sql_chaxun2(sql5)sql5j=w5.chaxun()x_data = list(sql5j['时间'])y_data = list(sql5j['sl'])c5 = (Line().add_xaxis(x_data).add_yaxis("每小时创建工单数量()", y_data)# legend_opts 图例配置项 配置了图例为圆形 最后还是直接关闭了图例配置项，因为图裂旁边的文字搞不定# title_opts 配置了标题文字内容 标题文字颜色 标题文字大小.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='每小时创建工单数量', pos_left='120',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=3)),axislabel_opts=opts.LabelOpts(font_size=17),type_='value',split_number=12),yaxis_opts=opts.AxisOpts(is_show=False,axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=4)))).set_series_opts(# 标签配置项 配置了标签颜色为 黄色label_opts=opts.LabelOpts(color='yellow', font_size=13),# 线样式配置项 配置了线宽 配置了颜色linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='rgb(128, 128, 128)'),# 标记点配置项目 配置了最大值markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(type_="max", symbol='circle')],symbol_size=20),).dump_options_with_quotes())return c5def bar3():w6=Sql_chaxun2(sql6)sql6j=w6.chaxun()c3 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).index)c4 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).values)c6 =(Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK)).add_yaxis('将要超时{5H} & 已超时', list(c4[0])).add_xaxis(list(c3['客户名称']))# bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='将要超时{5H} & 已超时', pos_left='130',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=3)),axislabel_opts=opts.LabelOpts(font_size=12, position='top', rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=4)),)).dump_options_with_quotes())return c6
html报表【样式 竖着】
#talbe1 返回报表
def baifeng(a):'''专为 Remote_Fixed和SLA_Met 字段求百分比定义的函数 '''a = str(a)b1 = c[a][c[a] != 0].astype('str') # 过滤掉为0的行 并将数据类型设置为stra2 = []for i in b1:str(i)i2 = i.rstrip('%') # 去除每个字符串后面的百分号a2.append(i2) # 将循环得到的结果保存到列表中a3 = pd.Series(a2).astype('float64') # 用列表建立serries对象，因为列表无法求和a3 = ('%.2f' % a3.mean() + '%') # 格式化输入浮点数 然后加上%号return a3#模板不支持range函数
@register.filter
def get_range(value):return range(len(value))#wtc 报表1
def baobiao(request):# 第一步：获取数据a = Sql_chaxun2(sql7) # 调用类实例化对象与查询sql语句global cc = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果# 第二步：数据清洗和列类型转换c = c.fillna(0) # 空值用0填充# 修改列的数据类型c['Total_call'] = c['Total_call'].astype('int64') # 将Total_call 的float64类型改为int64c['P1_call'] = c['P1_call'].astype('int64') # 将P1_call 的float64类型改为int64# c['SLA_Met']=c['SLA_Met'].astype('int64') #将SLA_Met 的float64类型改为int64c['No_onsite_time'] = c['No_onsite_time'].astype('int64') # 将No_onsite_time 的float64类型改为int64# 第三步：数据整理# 将x轴的索引取出放到列表中，然后在将列表转化为series类型，最后合并到dateframe类型中i4 = []for i in (c.columns):i4.append(i)del i4[0] # 删除日期 解决 指标 对接 30天指标汇总错误问题i5 = pd.Series(i4) # 将列表类型转化为Series类型c['指标'] = i5 # 新建一个列 列名为指标 并将series类型合并到dataframe类型中# 以下下时进行30天指标汇总计算代码c['30天指标汇总'] = 0c['30天指标汇总'][0] = c['Total_call'].sum()c['30天指标汇总'][1] = c['Unclosed'].sum()c['30天指标汇总'][2] = c['Scheduled'].sum()c['30天指标汇总'][3] = c['P1_call'].sum()c['30天指标汇总'][4] = c['Over_SLA'].sum()c['30天指标汇总'][5] = baifeng('SLA_Met') # 使用函数求值c['30天指标汇总'][6] = c['Worst_TAT'][c['Worst_TAT'] != 0].astype('str').max() # 过滤掉时间中不为0的值 将数据类型转化为字符串# Avg_onsite_time 求值Avg_onsite_time1 = pd.to_datetime(c['Avg_onsite_time'][c['Avg_onsite_time'] != 0],format='%H:%M:%S') # 过滤掉值为0的行，然后将数据类型转换为datetimeAvg_onsite_time2 = str(Avg_onsite_time1.mean()).split(' ')[1] # 将 平均值05:42:55.090909184 转化为字符串 ，然后以空格分割为列表Avg_onsite_time = Avg_onsite_time2.split('.')[0]c['30天指标汇总'][7] = Avg_onsite_timec['30天指标汇总'][8] = (c['No_onsite_time'].dropna().sum())c['30天指标汇总'][9] = (c['Onsite_1'].dropna().sum())c['30天指标汇总'][10] = baifeng('Remote_Fixed') # 使用函数求值c = c.fillna(0) # 空值用0填充del c['指标'][12]del c['指标'][13]r3 = []for r2 in range(len(c)):r3.append(c.loc[r2].tolist())return render(request,'baobiao1.html',locals())
html报表 【样式 横着】
#wtc 报表2 横着过来
def baobiao2(request):import datetime# 展示时间模块# dt = datetime.now()dt=datetime.datetime.now()dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"#zha zhb 类对象实例# 第一步：获取数据# 1 查询ccti为所有a = Sql_chaxun2(sql_zong()) # 调用类实例化对象与查询sql语句d = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果d = d.fillna(0)d = d.replace('100.00%','100%')r4 = []for ii2 in range(len(d)):r4.append(d.loc[ii2].tolist())# 2 查询ccti为 NWa1=Sql_chaxun2(sql_zong(CCTI_CLASS='NW'))d1=a1.chaxun()d1=d1.fillna(0)d1=d1.replace('100.00%','100%')r5=[]for ii3 in range(len(d1)):r5.append(d1.loc[ii3].tolist())# 3 查询ccti 为SW/PC01a2 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC01'))d2 = a2.chaxun()d2 = d2.fillna(0)d2 =d2.replace('100.00%','100%')r6 = []for ii4 in range(len(d2)):r6.append(d2.loc[ii4].tolist())# 4 查询ccti 为SW/PC02a3 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC02'))d3 = a3.chaxun()d3 = d3.fillna(0)d3 =d3.replace('100.00%','100%')r7 = []for ii5 in range(len(d3)):r7.append(d3.loc[ii5].tolist())# 5 查询ccti 为 SW/POSa4 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/POS'))d4 = a4.chaxun()d4 = d4.fillna(0)d4=d4.replace('100.00%','100%')r8 = []for ii6 in range(len(d4)):r8.append(d4.loc[ii6].tolist())# 6 查询ccti 为 SW/手持a5 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/手持'))d5 = a5.chaxun()d5 = d5.fillna(0)d5 = d5.replace('100.00%','100%')r9 = []for ii7 in range(len(d5)):r9.append(d5.loc[ii7].tolist())# 7 查询 ccti 为SW/SCOa6 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/SCO'))d6 = a6.chaxun()d6 = d6.fillna(0)d6 =d6.replace('100.00%','100%')r10 = []for ii8 in range(len(d6)):r10.append(d6.loc[ii8].tolist())# 8 查询 ccti 为HWa7 = Sql_chaxun2(sql_zong(CCTI_CLASS='HW'))d7 = a7.chaxun()d7 = d7.fillna(0)d7=d7.replace('100.00%','100%')r11 = []for ii9 in range(len(d7)):r11.append(d7.loc[ii9].tolist())return render(request,'baobiao2.html',locals())
将html报表保存为html文件通过访问指定url来调用django视图函数使用邮件发送给指定收件人
如果项目部署在linux环境下可以编写shell脚本使用 curl -i 来访问url，并设置linux周期性任务来指定特定的时间将报表发送给指定的用户
#wtc 报表2 横着过来
#本视图函数是为了渲染页面的时候直接得时候保存为html文件发送给固定人员
def baobiao2_sendmail(request):# 展示时间模块from datetime import datetimedt = datetime.now()dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"#zha zhb 类对象实例# 第一步：获取数据# 1 查询ccti为所有a = Sql_chaxun2(sql_zong()) # 调用类实例化对象与查询sql语句d = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果d = d.fillna(0)d = d.replace('100.00%','100%')r4 = []for ii2 in range(len(d)):r4.append(d.loc[ii2].tolist())# 2 查询ccti为 NWa1=Sql_chaxun2(sql_zong(CCTI_CLASS='NW'))d1=a1.chaxun()d1=d1.fillna(0)d1=d1.replace('100.00%','100%')r5=[]for ii3 in range(len(d1)):r5.append(d1.loc[ii3].tolist())# 3 查询ccti 为SW/PC01a2 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC01'))d2 = a2.chaxun()d2 = d2.fillna(0)d2 =d2.replace('100.00%','100%')r6 = []for ii4 in range(len(d2)):r6.append(d2.loc[ii4].tolist())# 4 查询ccti 为SW/PC02a3 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC02'))d3 = a3.chaxun()d3 = d3.fillna(0)d3 =d3.replace('100.00%','100%')r7 = []for ii5 in range(len(d3)):r7.append(d3.loc[ii5].tolist())# 5 查询ccti 为 SW/POSa4 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/POS'))d4 = a4.chaxun()d4 = d4.fillna(0)d4=d4.replace('100.00%','100%')r8 = []for ii6 in range(len(d4)):r8.append(d4.loc[ii6].tolist())# 6 查询ccti 为 SW/手持a5 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/手持'))d5 = a5.chaxun()d5 = d5.fillna(0)d5 = d5.replace('100.00%','100%')r9 = []for ii7 in range(len(d5)):r9.append(d5.loc[ii7].tolist())# 7 查询 ccti 为SW/SCOa6 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/SCO'))d6 = a6.chaxun()d6 = d6.fillna(0)d6 =d6.replace('100.00%','100%')r10 = []for ii8 in range(len(d6)):r10.append(d6.loc[ii8].tolist())# 8 查询 ccti 为HWa7 = Sql_chaxun2(sql_zong(CCTI_CLASS='HW'))d7 = a7.chaxun()d7 = d7.fillna(0)d7=d7.replace('100.00%','100%')r11 = []for ii9 in range(len(d7)):r11.append(d7.loc[ii9].tolist())html=render(request,'baobiao2.html',locals())html2=html.content.decode()#bytes类型转为strprint(type(html2))# 下面是保存网页并通过邮件发送的方式from django.core import mail # 导入发送邮件的模块from email.mime.text import MIMEText # html格式和文本格式邮件from django.core.mail import send_mail, EmailMultiAlternativesfrom email.header import make_headerfrom django.core.mail import send_mail, EmailMultiAlternatives'''将网页文件写入文本'''# 获取当前时间 时/分/秒import datetimetime = datetime.datetime.now().strftime('%H-%M-%S')# 将网页写入文件中try:with open(f'./static/mail/Daily_Report_WTC-ALL-{time}.html', 'wt', encoding='UTF-8') as p:p.write(html2)print('写入成功')except:print('文件写入失败')'''开始生成消息'''subject = '测试邮件' # 邮件主题# message = 'bill.ceshi' #邮件内容text_content = '这是一封重要的报告邮件.'from_email = 'serviceai@zh.value-exch.com.cn' # 发件人# 'Figo.FEI@value-exch.com'recipient_list = ['2345636254@qq.com', 'billl.wang@valueyu-exch.com'] # 收件人html_content = '<h1>尊敬的先生/女士您好</h1><h3>这是一封重要的<span style="color: red; font-size: 25px;">机密</span>文件，请查阅后及时销毁。</h3>'# html_message='' 带有Html标签的邮件内容msg = EmailMultiAlternatives(subject, text_content, from_email, recipient_list)# msg.attach_alternative('text/html')'''添加html附件--Daily_Report_WTC-ALL-14-11-16.html'''msg.attach_file(f'./static/mail/Daily_Report_WTC-ALL-{time}.html') # 添加附件代码try:msg.send()return HttpResponse('这个是baobiao2_sendmail函数发送的邮件')except:return HttpResponse('邮件发送失败，请联系bill')效果
demo2 应用部分完整代码
urls.py
from django.urls import path,include
from .views import *urlpatterns=[# 第一个bar视图path('bar', ChartView.as_view(), name='demo'),# 第二个bar2视图path('bar2', ChartView2.as_view(), name='demo'),# 第三个pie1视图path('pie1', ChartView3.as_view(), name='demo'),# 第四个line1 视图path('line1', ChartView4.as_view(), name='demo'),path('line2', ChartView5.as_view(), name='demo'),path('bar3', ChartView6.as_view(), name='demo'),path('b1',ChartView7.as_view(),name='demo'),path('index', IndexView.as_view(), name='demo'), #看板首页 版本1path('2index', IndexView3.as_view(), name='demo'), #看板首页 版本2#path('baobiao',IndexView2.as_view(),name='demo'),#报表一path('ceshi',baobiao),#报表二path('ceshi2',baobiao2),#发送邮件视图path('sendmail',send_mail1),#该视图中下载网页函数不可访问本机url#发送邮件视图#该视图不用访问url download网页而是直接将渲染过后的网页保存下来然后通过邮件发送path('sendmail2',baobiao2_sendmail)
]views.py
from django.shortcuts import render
import json
from random import randrangefrom 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
#------------------------------------------------------
# Create your views here.
def response_as_json(data):json_str = json.dumps(data)response = HttpResponse(json_str,content_type="application/json",)response["Access-Control-Allow-Origin"] = "*"return responsedef json_response(data, code=200):data = {"code": code,"msg": "success","data": data,}return response_as_json(data)def json_error(error_string="error", code=500, **kwargs):data = {"code": code,"msg": error_string,"data": {}}data.update(kwargs)return response_as_json(data)JsonResponse = json_response
JsonError = json_error#定义数据库查询类
class Sql_chaxun2():'''初始化sqlserver 连接属性'''def __init__(self,sql):self.servername='888.888.888.888' #服务器名称self.username='nicai' #账户self.port='6不6' #端口号self.password='不6' #密码self.dabasename='VEII'self.sql=sqlself.sqlj=''def chaxun(self):con=pymssql.connect(server=self.servername,user=self.username,password=self.password,database=self.dabasename,port=self.port,charset='utf8')print('sqlserver 连接成功')self.sqlj=pd.read_sql(self.sql,con)con.commit() #提交对数据库的操作con.close() #关闭数据库print('关闭数据库成功')return self.sqljdef bar1():#1每个服务人员处理中工单数量 [已完成]w=Sql_chaxun2(sql1) #使用查询数据库的类sql1j=w.chaxun()c1=(Bar(init_opts=opts.InitOpts( width='400px',height='400px')).add_yaxis('WTCCN-VEISW服务人员工单数量',list(sql1j['sl'])).add_xaxis((list(sql1j['处理人'])))#bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='工程师处理中工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white', font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='3')),axislabel_opts=opts.LabelOpts(font_size=12,position='top',rotate=45)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='springgreen', width='4')))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 15, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() #设置对象)return c1def bar2():# 查询队列（services ai中的支持组）中的正在开着工单数量top10w2=Sql_chaxun2(sql2)sql2j=w2.chaxun()c2 = (Bar().add_xaxis(list(sql2j['支持组'])).add_yaxis('支持组中工单数量TOP10', (list(sql2j['value1'])),label_opts=opts.LabelOpts(position='top',color='lightcyan', font_size=20))#.reversal_axis() # 将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='队列处理中工单数量TOP10', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='3')),axislabel_opts=opts.LabelOpts(font_size=8,position='top',rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lightcyan', width='4')),axislabel_opts=opts.LabelOpts(font_size=9,position='top'))).set_series_opts(itemstyle_opts={"normal": {"color": JsCode("""new echarts.graphic.LinearGradient(0, 0, 0, 1, [{offset: 0,color: 'rgba(0, 244, 255, 1)'},{offset: 1,color: 'rgba(0, 77, 167, 1)'}], false)"""), # 调整柱子颜色渐变'shadowBlur': 5, # 光影大小"barBorderRadius": [100, 100, 100, 100], # 调整柱子圆角弧度"shadowColor": "#0EEEF9", # 调整阴影颜色'shadowOffsetY': 2,'shadowOffsetX': 2, # 偏移量}}).dump_options_with_quotes() # 设置对象)return c2def pie1():# 3所有开着工单的详细信息 饼图--饼图相比其他图形 要求数据是一对一对的w3=Sql_chaxun2(sql3)sql3j=w3.chaxun()c1 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).index)c2 = pd.DataFrame(sql3j.groupby('客户名称').size().sort_values(ascending=False).head(10).values)c3 = (Pie().add("", [list(z) for z in zip(c1['客户名称'], c2[0])]).set_colors(["aqua", "greenyellow", "lightcyan", "red", "pink", "orange", "purple",'deeppink','darkred','darkslategray']).set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='处理中工单数量', pos_left=300,pos_top=1,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='3')),axislabel_opts=opts.LabelOpts(font_size=17)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='red', width='4'))))# .set_global_opts(title_opts=opts.TitleOpts(title="Pie-设置颜色")).set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}")).dump_options_with_quotes())return c3def line1():# 4近七日新建和关闭工单数量w4=Sql_chaxun2(sql4)sql4j=w4.chaxun()c4 = (Line().add_xaxis(list(sql4j['时间'])).add_yaxis('新建数量', list(sql4j['新建数量']), label_opts=opts.LabelOpts(position='top', color='mediumspringgreen', font_size=20, ),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='mediumspringgreen')).add_yaxis("关闭数量", list(sql4j['关闭数量']), label_opts=opts.LabelOpts(position='bottom', color='blueviolet', font_size=20),linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='blueviolet')).set_global_opts(legend_opts=opts.LegendOpts(is_show=True,pos_top=20,textstyle_opts=opts.TextStyleOpts(color='Brown',font_size=15)),title_opts=opts.TitleOpts(title='近七日新建和关闭工单数量', pos_left=130,title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')),axislabel_opts=opts.LabelOpts(font_size=12)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='lime', width='4')))).dump_options_with_quotes())return c4def line2():# 5每小时创建工单数量"""参考地址: https://gallery.echartsjs.com/editor.html?c=xEyDk1hwBx"""w5=Sql_chaxun2(sql5)sql5j=w5.chaxun()x_data = list(sql5j['时间'])y_data = list(sql5j['sl'])c5 = (Line().add_xaxis(x_data).add_yaxis("每小时创建工单数量()", y_data)# legend_opts 图例配置项 配置了图例为圆形 最后还是直接关闭了图例配置项，因为图裂旁边的文字搞不定# title_opts 配置了标题文字内容 标题文字颜色 标题文字大小.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='每小时创建工单数量', pos_left='120',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=3)),axislabel_opts=opts.LabelOpts(font_size=17),type_='value',split_number=12),yaxis_opts=opts.AxisOpts(is_show=False,axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='aqua', width=4)))).set_series_opts(# 标签配置项 配置了标签颜色为 黄色label_opts=opts.LabelOpts(color='yellow', font_size=13),# 线样式配置项 配置了线宽 配置了颜色linestyle_opts=opts.LineStyleOpts(is_show=False, width=3, color='rgb(128, 128, 128)'),# 标记点配置项目 配置了最大值markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(type_="max", symbol='circle')],symbol_size=20),).dump_options_with_quotes())return c5def bar3():w6=Sql_chaxun2(sql6)sql6j=w6.chaxun()c3 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).index)c4 = pd.DataFrame(sql6j.groupby('客户名称').size().sort_values(ascending=False).values)c6 =(Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK)).add_yaxis('将要超时{5H} & 已超时', list(c4[0])).add_xaxis(list(c3['客户名称']))# bar.reversal_axis() #将柱状图反转过来作为横着条形图.set_global_opts(legend_opts=opts.LegendOpts(is_show=False),title_opts=opts.TitleOpts(title='将要超时{5H} & 已超时', pos_left='130',title_textstyle_opts=opts.TextStyleOpts(color='white',font_size=18)),xaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=3)),axislabel_opts=opts.LabelOpts(font_size=12, position='top', rotate=20)),yaxis_opts=opts.AxisOpts(axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color='yellow', width=4)),)).dump_options_with_quotes())return c6#talbe1 返回报表
def baifeng(a):'''专为 Remote_Fixed和SLA_Met 字段求百分比定义的函数 '''a = str(a)b1 = c[a][c[a] != 0].astype('str') # 过滤掉为0的行 并将数据类型设置为stra2 = []for i in b1:str(i)i2 = i.rstrip('%') # 去除每个字符串后面的百分号a2.append(i2) # 将循环得到的结果保存到列表中a3 = pd.Series(a2).astype('float64') # 用列表建立serries对象，因为列表无法求和a3 = ('%.2f' % a3.mean() + '%') # 格式化输入浮点数 然后加上%号return a3#模板不支持range函数
@register.filter
def get_range(value):return range(len(value))#wtc 报表1
def baobiao(request):# 第一步：获取数据a = Sql_chaxun2(sql7) # 调用类实例化对象与查询sql语句global cc = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果# 第二步：数据清洗和列类型转换c = c.fillna(0) # 空值用0填充# 修改列的数据类型c['Total_call'] = c['Total_call'].astype('int64') # 将Total_call 的float64类型改为int64c['P1_call'] = c['P1_call'].astype('int64') # 将P1_call 的float64类型改为int64# c['SLA_Met']=c['SLA_Met'].astype('int64') #将SLA_Met 的float64类型改为int64c['No_onsite_time'] = c['No_onsite_time'].astype('int64') # 将No_onsite_time 的float64类型改为int64# 第三步：数据整理# 将x轴的索引取出放到列表中，然后在将列表转化为series类型，最后合并到dateframe类型中i4 = []for i in (c.columns):i4.append(i)del i4[0] # 删除日期 解决 指标 对接 30天指标汇总错误问题i5 = pd.Series(i4) # 将列表类型转化为Series类型c['指标'] = i5 # 新建一个列 列名为指标 并将series类型合并到dataframe类型中# 以下下时进行30天指标汇总计算代码c['30天指标汇总'] = 0c['30天指标汇总'][0] = c['Total_call'].sum()c['30天指标汇总'][1] = c['Unclosed'].sum()c['30天指标汇总'][2] = c['Scheduled'].sum()c['30天指标汇总'][3] = c['P1_call'].sum()c['30天指标汇总'][4] = c['Over_SLA'].sum()c['30天指标汇总'][5] = baifeng('SLA_Met') # 使用函数求值c['30天指标汇总'][6] = c['Worst_TAT'][c['Worst_TAT'] != 0].astype('str').max() # 过滤掉时间中不为0的值 将数据类型转化为字符串# Avg_onsite_time 求值Avg_onsite_time1 = pd.to_datetime(c['Avg_onsite_time'][c['Avg_onsite_time'] != 0],format='%H:%M:%S') # 过滤掉值为0的行，然后将数据类型转换为datetimeAvg_onsite_time2 = str(Avg_onsite_time1.mean()).split(' ')[1] # 将 平均值05:42:55.090909184 转化为字符串 ，然后以空格分割为列表Avg_onsite_time = Avg_onsite_time2.split('.')[0]c['30天指标汇总'][7] = Avg_onsite_timec['30天指标汇总'][8] = (c['No_onsite_time'].dropna().sum())c['30天指标汇总'][9] = (c['Onsite_1'].dropna().sum())c['30天指标汇总'][10] = baifeng('Remote_Fixed') # 使用函数求值c = c.fillna(0) # 空值用0填充del c['指标'][12]del c['指标'][13]r3 = []for r2 in range(len(c)):r3.append(c.loc[r2].tolist())return render(request,'baobiao1.html',locals())#wtc 报表2 横着过来
def baobiao2(request):import datetime# 展示时间模块# dt = datetime.now()dt=datetime.datetime.now()dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"#zha zhb 类对象实例# 第一步：获取数据# 1 查询ccti为所有a = Sql_chaxun2(sql_zong()) # 调用类实例化对象与查询sql语句d = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果d = d.fillna(0)d = d.replace('100.00%','100%')r4 = []for ii2 in range(len(d)):r4.append(d.loc[ii2].tolist())# 2 查询ccti为 NWa1=Sql_chaxun2(sql_zong(CCTI_CLASS='NW'))d1=a1.chaxun()d1=d1.fillna(0)d1=d1.replace('100.00%','100%')r5=[]for ii3 in range(len(d1)):r5.append(d1.loc[ii3].tolist())# 3 查询ccti 为SW/PC01a2 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC01'))d2 = a2.chaxun()d2 = d2.fillna(0)d2 =d2.replace('100.00%','100%')r6 = []for ii4 in range(len(d2)):r6.append(d2.loc[ii4].tolist())# 4 查询ccti 为SW/PC02a3 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC02'))d3 = a3.chaxun()d3 = d3.fillna(0)d3 =d3.replace('100.00%','100%')r7 = []for ii5 in range(len(d3)):r7.append(d3.loc[ii5].tolist())# 5 查询ccti 为 SW/POSa4 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/POS'))d4 = a4.chaxun()d4 = d4.fillna(0)d4=d4.replace('100.00%','100%')r8 = []for ii6 in range(len(d4)):r8.append(d4.loc[ii6].tolist())# 6 查询ccti 为 SW/手持a5 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/手持'))d5 = a5.chaxun()d5 = d5.fillna(0)d5 = d5.replace('100.00%','100%')r9 = []for ii7 in range(len(d5)):r9.append(d5.loc[ii7].tolist())# 7 查询 ccti 为SW/SCOa6 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/SCO'))d6 = a6.chaxun()d6 = d6.fillna(0)d6 =d6.replace('100.00%','100%')r10 = []for ii8 in range(len(d6)):r10.append(d6.loc[ii8].tolist())# 8 查询 ccti 为HWa7 = Sql_chaxun2(sql_zong(CCTI_CLASS='HW'))d7 = a7.chaxun()d7 = d7.fillna(0)d7=d7.replace('100.00%','100%')r11 = []for ii9 in range(len(d7)):r11.append(d7.loc[ii9].tolist())return render(request,'baobiao2.html',locals())#获取网页文件函数def gethtml2(url):try:u={'User-Agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 UBrowser/6.2.4098.3 Safari/537.36'}print('正在访问下载download该url对应网页')r=requests.get(url,params=u)print(r.status_code)r.raise_for_status() #判断是否异常r.encoding=r.apparent_encodingprint('网页读取成功')html=r.text #将网页保存到html 变量中except:html=('网页访问失败')return html#发送邮件视图函数
def send_mail1(request):'''该函数不能访问自己django主服务器所在主机url'''from django.core import mail #导入发送邮件的模块from email.mime.text import MIMEText # html格式和文本格式邮件from django.core.mail import send_mail, EmailMultiAlternativesfrom email.header import make_headerfrom django.core.mail import send_mail, EmailMultiAlternatives'''将网页文件写入文本'''url2 ='https://www.thepaper.cn/newsDetail_forward_19728680'url1='http://172.16.64.121:8001/demo2/sendmail'html=gethtml2(url2) #获取response.text 文本# 获取当前时间 时/分/秒import datetimetime = datetime.datetime.now().strftime('%H-%M-%S')# 将网页写入文件中try:with open(f'./static/mail/Daily_Report_WTC-ALL-{time}.html', 'wt', encoding='UTF-8') as p:p.write(html)print('写入成功')except:print('文件写入失败')'''开始生成消息'''subject = '测试邮件' #邮件主题#message = 'bill.ceshi' #邮件内容text_content = '这是一封重要的报告邮件.'from_email = 'serviceai@zh.value-exch.com.cn' #发件人#'Figo.FEI@value-exch.com'recipient_list = ['2569449660@qq.com', 'bill.wang@value-exch.com'] #收件人html_content = '<h1>尊敬的先生/女士您好</h1><h3>这是一封重要的<span style="color: red; font-size: 25px;">机密</span>文件，请查阅后及时销毁。</h3>'#html_message='' 带有Html标签的邮件内容msg=EmailMultiAlternatives(subject,text_content,from_email,recipient_list)#msg.attach_alternative('text/html')'''添加html附件--Daily_Report_WTC-ALL-14-11-16.html'''msg.attach_file(f'./static/mail/Daily_Report_WTC-ALL-{time}.html')#添加附件代码try:msg.send()return HttpResponse('邮件已经发送，请查收')except:return HttpResponse('邮件发送失败，请联系bill')pass#wtc 报表2 横着过来
#本视图函数是为了渲染页面的时候直接得时候保存为html文件发送给固定人员
def baobiao2_sendmail(request):# 展示时间模块from datetime import datetimedt = datetime.now()dt2 = f"{dt.year}年{dt.month}月{dt.day}日 {dt.hour}时{dt.minute}分"#zha zhb 类对象实例# 第一步：获取数据# 1 查询ccti为所有a = Sql_chaxun2(sql_zong()) # 调用类实例化对象与查询sql语句d = a.chaxun() # 调用类的chaxun方法执行sql然后返回结果d = d.fillna(0)d = d.replace('100.00%','100%')r4 = []for ii2 in range(len(d)):r4.append(d.loc[ii2].tolist())# 2 查询ccti为 NWa1=Sql_chaxun2(sql_zong(CCTI_CLASS='NW'))d1=a1.chaxun()d1=d1.fillna(0)d1=d1.replace('100.00%','100%')r5=[]for ii3 in range(len(d1)):r5.append(d1.loc[ii3].tolist())# 3 查询ccti 为SW/PC01a2 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC01'))d2 = a2.chaxun()d2 = d2.fillna(0)d2 =d2.replace('100.00%','100%')r6 = []for ii4 in range(len(d2)):r6.append(d2.loc[ii4].tolist())# 4 查询ccti 为SW/PC02a3 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/PC02'))d3 = a3.chaxun()d3 = d3.fillna(0)d3 =d3.replace('100.00%','100%')r7 = []for ii5 in range(len(d3)):r7.append(d3.loc[ii5].tolist())# 5 查询ccti 为 SW/POSa4 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/POS'))d4 = a4.chaxun()d4 = d4.fillna(0)d4=d4.replace('100.00%','100%')r8 = []for ii6 in range(len(d4)):r8.append(d4.loc[ii6].tolist())# 6 查询ccti 为 SW/手持a5 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/手持'))d5 = a5.chaxun()d5 = d5.fillna(0)d5 = d5.replace('100.00%','100%')r9 = []for ii7 in range(len(d5)):r9.append(d5.loc[ii7].tolist())# 7 查询 ccti 为SW/SCOa6 = Sql_chaxun2(sql_zong(CCTI_CLASS='SW/SCO'))d6 = a6.chaxun()d6 = d6.fillna(0)d6 =d6.replace('100.00%','100%')r10 = []for ii8 in range(len(d6)):r10.append(d6.loc[ii8].tolist())# 8 查询 ccti 为HWa7 = Sql_chaxun2(sql_zong(CCTI_CLASS='HW'))d7 = a7.chaxun()d7 = d7.fillna(0)d7=d7.replace('100.00%','100%')r11 = []for ii9 in range(len(d7)):r11.append(d7.loc[ii9].tolist())html=render(request,'baobiao2.html',locals())html2=html.content.decode()#bytes类型转为strprint(type(html2))# 下面是保存网页并通过邮件发送的方式from django.core import mail # 导入发送邮件的模块from email.mime.text import MIMEText # html格式和文本格式邮件from django.core.mail import send_mail, EmailMultiAlternativesfrom email.header import make_headerfrom django.core.mail import send_mail, EmailMultiAlternatives'''将网页文件写入文本'''# 获取当前时间 时/分/秒import datetimetime = datetime.datetime.now().strftime('%H-%M-%S')# 将网页写入文件中try:with open(f'./static/mail/Daily_Report_WTC-ALL-{time}.html', 'wt', encoding='UTF-8') as p:p.write(html2)print('写入成功')except:print('文件写入失败')'''开始生成消息'''subject = '测试邮件' # 邮件主题# message = 'bill.ceshi' #邮件内容text_content = '这是一封重要的报告邮件.'from_email = 'serviceai@zh.value-exch.com.cn' # 发件人# 'Figo.FEI@value-exch.com'recipient_list = ['2569449660@qq.com', 'bill.wang@value-exch.com'] # 收件人html_content = '<h1>尊敬的先生/女士您好</h1><h3>这是一封重要的<span style="color: red; font-size: 25px;">机密</span>文件，请查阅后及时销毁。</h3>'# html_message='' 带有Html标签的邮件内容msg = EmailMultiAlternatives(subject, text_content, from_email, recipient_list)# msg.attach_alternative('text/html')'''添加html附件--Daily_Report_WTC-ALL-14-11-16.html'''msg.attach_file(f'./static/mail/Daily_Report_WTC-ALL-{time}.html') # 添加附件代码try:msg.send()return HttpResponse('这个是baobiao2_sendmail函数发送的邮件')except:return HttpResponse('邮件发送失败，请联系bill')#bar()
class ChartView(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar1()))#bar2()
class ChartView2(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar2()))#pie1()
class ChartView3(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(pie1()))#line1
class ChartView4(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(line1()))#line2
class ChartView5(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(line2()))#bar3
class ChartView6(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(bar3()))#table1 图7
class ChartView7(APIView):def get(self, request, *args, **kwargs):return JsonResponse(json.loads(baobiao()))#看板首页 版本1
class IndexView(APIView):def get(self, request, *args, **kwargs):return render(request,'2index.html')#看板首页 版本2
class IndexView3(APIView):def get(self, request, *args, **kwargs):return render(request,'3index.html')#报表的html
class IndexView2(APIView):def get(self, request, *args, **kwargs):return render(request,'baobiao1.html',locals())
sql.py
#查询数据库的语句
#1 每个服务人员处理中工单数量 [已完成]
sql1='''
select t2.E_MAIL 处理人, count(t1.TICKET_ID) sl
from
VAPP_ITEM t1 join ORG_CONTACT t2 ON t1.assigned_to_contact_id=t2.ROW_ID
where t1.TICKET_STATUS IN ('Active','New','Queued')
group by t2.E_MAIL
'''#2, 查询队列（services ai中的支持组）中的正在开着工单数量top10 [已完成]
#目前没有10个组，所以不能用limit 10
sql2='''
select assigned_to_group_name 支持组,COUNT(TICKET_ID) value1
from VAPP_ITEM
where TICKET_STATUS in ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation')
GROUP BY assigned_to_group_name
order by value1 desc
'''#3 所有开着工单的详细信息 [已完成]
sql3='''
SELECT
vi.ROW_ID '系统ID',
(SELECT TOP 1 metric_value from VSLA_METRIC_CALCULATIONS as sla WHERE sla.ticket_id=vi.ROW_ID) as 'SLA所用时间(Min)',
sla_compliance_status_indicator as 'SLA状态',
ticket_identifier as '工单号',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=561) as '内部工单号',
TICKET_STATUS as '状态',
[person1_root_org_name] as'客户名称',
person1_org_name as '门店编号',
closed_by_group_name as '关闭组',
(closed_by_name+'.'+closed_by_last_name) as '关闭人',
ticket_description as '问题描述',
last_worklog as '最后工作日志',
DATEADD(SECOND, last_worklog_date, '1970/1/1 08:00:00') as '最后工作日志时间',
(SELECT top 1 [LVL1_CAT] +' - '+ [LVL2_CAT] FROM [VIC_HIERARCHICAL_TREE_DATA] where CHT_ID = (SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=557)) as '解决分类',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=578) as '解决办法',
(created_by_name+'.'+created_by_last_name) as '创建人',
DATEADD(SECOND, CREATED_DATE, '1970/1/1 08:00:00') as '创建时间',
(closed_by_name+'.'+closed_by_last_name) as '关单人',
DATEADD(SECOND, resolved_date, '1970/1/1 08:00:00') as '解决时间',
DATEADD(SECOND, closed_date, '1970/1/1 08:00:00') as '关闭时间',
CCTI_CLASS as '类',
CCTI_CATEGORY as '类别',
CCTI_TYPE as '类型',
CCTI_ITEM as '项目',
sla_target_name as 'SLA',
(SELECT top 1 DATEADD(SECOND, [status_created_date], '1970/1/1 08:00:00') FROM VAPP_HISTORY vh where vh.row_id = vi.ROW_ID and status='Active' order by status_created_date) as '首次响应时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=549) as '联系人',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=550) as '联系电话',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=552) as '上门人',
(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) as '上门时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=554) as '第二次上门人',
(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=555) as '第二次上门时间',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=556) as '第三次上门人',
(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=558) as '第三次上门时间',
person1_hierarchical_path as '组织',
person1_last_name as '城市',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=559) as 'CSS',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=560) as '反馈意见',
(SELECT TOP 1 ATTR_VALUE FROM VAPP_ITEM_ATTRIBUTES as va WHERE va.ITEM_ID=vi.ROW_ID and va.ATTR_ID=548) as '客户单号'
from VAPP_ITEM as vi WHERE TICKET_STATUS in ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation')
'''#4 近七日新建和关闭工单数量 [已完成]
sql4='''
select a.cr_time 时间, a.asl 新建数量,b.bsl 关闭数量
from (select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120) cr_time, COUNT(*) asl
from VAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120),GETDATE())<=7
group by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120)) a
join
(select CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120) cl_time, COUNT(*) bsl
from VAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120),GETDATE())<=7
group by CONVERT(VARCHAR(10),(DATEADD(S,CLOSED_DATE+8*3600,'1970-01-01 00:00:00')),120)) b
on a.cr_time=b.cl_time;
'''
#5 每小时创建工单数量 [已完成]
sql5='''
select DATEPART(hh, (DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00'))) 时间,COUNT(*) sl
from VAPP_ITEM
where CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')),120)=convert(VARCHAR(10),getdate(),120)
group by DATEPART(hh, (DATEADD(S,CREATED_DATE+8*3600,'1970-01-01 00:00:00')))
'''#6sla将要超时以及已超时
sql6='''
select [person1_root_org_name] as '客户名称',COUNT(TICKET_ID) 数量
from
VAPP_ITEM
where sla_compliance_status_indicator in ('Breached SLA')
GROUP BY
[person1_root_org_name]
'''sql7='''
/*
@kehu 定义查询的客户
@tianshu 定义查询的天数
@sla_target_name 定义查询工单的优先级
@CCTI_CLASS 定义ccti的类型
*/
declare @kehu VARCHAR(20)
set @kehu ='WTC';declare @tianshu int
set @tianshu=30declare @sla_target_name varchar(20)
set @sla_target_name='WTC - P2'declare @CCTI_CLASS varchar(20)
set @CCTI_CLASS='HW'select t1.cr_date 日期,t2.Total_call,t3.Unclosed,t4.Scheduled,t5.P1_call,t6.Over_SLA,/*datename(day,t1.cr_date) 日期,*/t7.SLA_Met,t8.Worst_TAT,t9.Avg_onsite_time,t10.No_onsite_time,t11.Onsite_1,t12.Remote_Fixed
from(select convert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_datefrom master..spt_values where type = 'P'and number < @tianshu) as t1
left join
/*Total Call，最近30天每日创建工单数量 不统计删除工单*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) Total_callfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')group by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t2 on t1.cr_date=t2.cr_date
left join
/*Unclosed 最近30天状态为开着的未关单数量，已创建时间排序*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) Unclosedfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and TICKET_STATUS IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation') and person1_root_org_name = @kehugroup by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t3 on t1.cr_date=t3.cr_date
left join
/* Scheduled 最近30天每天sla应到期工单数量且 工单状态为未关 （按照到期时间排序）
确认工单状态
*/(SELECTa.cr_date,COUNT ( TICKET_ID ) Scheduledfrom(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_IDfrom VAPP_ITEM as vi where person1_root_org_name = @kehu and TICKET_STATUS not in ('Closed','Archive','Request - Delete','Approved','Submitted') and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,vi.CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30) as aGROUP BY a.cr_date)as t4 on t1.cr_date=t4.cr_date
left join
/* P1_call 最近30天工单优先级最高的数量（按照创建时间排序）*/(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) P1_call
fromVAPP_ITEM
where datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and sla_target_name=@sla_target_nameand person1_root_org_name = @kehuand TICKET_STATUS not in ('Request - Delete','Approved','Submitted')
GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t5on t1.cr_date=t5.cr_date
left join
/*Over_SLA 最近30天工单状态已超时工单数量 按照创建时间排序 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) Over_SLAfromVAPP_ITEMwhere sla_compliance_status_indicator='Breached SLA' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30 and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP by CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t6on t1.cr_date=t6.cr_date
left join
/*SLA_Met 最近30天达成率 按照创建时间排序 公式（totalcall - uniclosed - oversla）/(totalcall - uniclosed ) */(select a.cr_date,/*(a.xjsl/b.gdsl) SLA_Met*/CAST(CAST(a.xjsl*1.0*100 / b.gdsl AS decimal(10,2)) AS varchar(50)) +'%' SLA_Metfrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date, COUNT(TICKET_ID) xjslfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30AND TICKET_STATUS NOT IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation','Request - Delete','Approved','Submitted')AND sla_compliance_status_indicator NOT IN ('Breached SLA','SLA Not Applied')and person1_root_org_name = @kehugroup 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) gdslfrom VAPP_ITEMwhere datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and TICKET_STATUS NOT IN ('Active','Complete','Escalated','New','Pending','Queued','Resolved','Resolved-Validation','Request - Delete','Approved','Submitted')and person1_root_org_name = @kehuAND sla_compliance_status_indicator NOT IN ('SLA Not Applied')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 t7on t1.cr_date=t7.cr_date
left join
/* Worst_TAT 最近30天每天工单处理花费最大时间 【已完成】
#原理 1，以创建时间排序 2，关单时间- 创建时间 3，工单状态为关闭 4，当天最大值*/(select b.cr_date,max(b.zd) Worst_TATfrom (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) zdfromVAPP_ITEMwhere TICKET_STATUS='Closed' and datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')) bgroup by b.cr_date)as t8on t1.cr_date=t8.cr_date
left join
/* Avg_onsite_time 最近30天平均上门时间 只计算有第一次上门时间的工单 【已完成】
#单位是小时
把删除状态去除统计 */(select t.cr_date,/*avg(t.avg_onsitetime) Avg_onsite_time*/(SELECTCONVERT(VARCHAR(12), avg(t.avg_onsitetime) /60/60 % 24) + ':'+ CONVERT(VARCHAR(2), avg(t.avg_onsitetime) /60 % 60) + ':'+ CONVERT(VARCHAR(2), avg(t.avg_onsitetime) % 60)) Avg_onsite_timefrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,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)) avg_onsitetimefromVAPP_ITEM as viwhere (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())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')and TICKET_ID NOT IN ('472')) as tgroup by t.cr_date)as t9on t1.cr_date=t9.cr_date
left join
/*No_onsite_time 最近30天没有上门时间的工单数量且ccti=hw 按照创建时间排序 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) No_onsite_timefromVAPP_ITEM as viwhere (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 datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t10on t1.cr_date=t10.cr_date
left join
/*Onsite_1 最近30天 重复上门工单数量 统计有第二次和第三次上门时间的工单 按照创建时间分组 */(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) Onsite_1fromVAPP_ITEM as viwhere (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())<=30and person1_root_org_name = @kehuand TICKET_STATUS NOT IN ('Request - Delete','Approved','Submitted')GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as t11on t1.cr_date=t11.cr_date
left join (select a.cr_date,CAST(CAST(a.fengzi*1.0*100 / b.fengmu AS decimal(10,2)) AS varchar(50)) +'%' Remote_Fixedfrom(select CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengzifromVAPP_ITEM as viWHERE 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 NULLand person1_root_org_name = @kehuand datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30group 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) fengmufromVAPP_ITEMWHERE TICKET_STATUS IN ('Closed','Archive')and person1_root_org_name = @kehuand datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<=30GROUP BY CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as bon a.cr_date=b.cr_date) as t12
on t1.cr_date=t12.cr_date
'''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(SELECTt1.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt2.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(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(SELECTt3.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 D30FROM (SELECTa.cr_date,COUNT ( TICKET_ID ) cFROM(SELECTCONVERT (VARCHAR ( 10 ),DATEADD(SECOND,(SELECTtop 1 sla_due_byFROMVSLA_AGREEMENT_COMPLIANCE_LIST_UX AS vcWHEREvc.item_id=vi.ROW_IDORDER BYthreshold_sort_order DESC), '1970/1/1 08:00:00'),120) AS 'cr_date',vi.TICKET_IDFROMVAPP_ITEM AS viWHEREperson1_root_org_name = @kehuAND 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) AS aGROUP BYa.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(SELECTt4.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) cFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND sla_target_name=@sla_target_nameAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt5.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,count(TICKET_ID) cFROMVAPP_ITEMWHEREsla_compliance_status_indicator='Breached SLA'AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND TICKET_STATUS IN ('closed','archive')--如果@CCTI_CLASS是空值，不参与统计，如果@CCTI_CLASS有值，使用@CCTI_CLASS值AND CCTI_CLASS= CASE WHEN @CCTI_CLASS <> '' THEN @CCTI_CLASS ELSE CCTI_CLASS ENDGROUP BYCONVERT(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_CLASSEND,--判断到此结束'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(SELECTcase 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 D30FROM (SELECTt.cr_date,datename(day,t.cr_date) cFROM(SELECTconvert(varchar(10),dateadd(dd,number,(getdate()-@tianshu)),120) cr_dateFROMmaster..spt_valuesWHEREtype = '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(SELECTt7.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 D30FROM (SELECTa.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)) +'%' cFROM(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) xjslFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))AS ajoin(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) gdslFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))AS bON 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(SELECTt8.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 D30FROM (SELECTb.cr_date,max(b.zd) cFROM(SELECTCONVERT(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) zdFROMVAPP_ITEMWHEREdatediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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) bGROUP BYb.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(SELECTt9.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 D30FROM (selecta.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)) +'%' cfrom(selectCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengzifromVAPP_ITEM as viWHERETICKET_STATUS IN ('Closed','Archive')AND (SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES as vaWHEREva.ITEM_ID=vi.ROW_IDand va.ATTR_ID=553) IS NULLand person1_root_org_name = @kehuand 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 ENDgroup byCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as ajoin(selectCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) fengmufromVAPP_ITEMWHERETICKET_STATUS IN ('Closed','Archive')and person1_root_org_name = @kehuand 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120))as bon a.cr_date=b.cr_date) AS t9) AS A
UNION ALL
/* Avg_onsite_time 30天内创建的工单，每日平均上门时间 只计算有第一次上门时间的工单。(不统计删除工单) #单位是小时*/
SELECT'<1:00',--将总平均值转换为时间格式(SELECTCONVERT(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(SELECTt8.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 D30FROM (SELECTb.cr_date,--计算总平均值AVG(b.zd) d,--将日平均值转时间格式(SELECTCONVERT(VARCHAR(12), avg(b.zd) /60/60 % 24) + ':'+ CONVERT(VARCHAR(2), avg(b.zd) /60 % 60) + ':'+ CONVERT(VARCHAR(2), avg(b.zd) % 60)) cFROM(SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,--如果上门时间早于创建时间，赋NULL。如果晚于创建时间，计算差值CASE WHEN(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHERE va.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) > DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')THENDATEDIFF(ss,DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00'),(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553))ELSENULLEND AS zd--判断到此结束FROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) is not nullAND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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) bGROUP BYb.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(SELECTt11.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=vi.ROW_IDAND va.ATTR_ID=553) is null--AND CCTI_CLASS=@CCTI_CLASSAND closed_by_group_name IN ('@group')AND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(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(SELECTt12.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 D30FROM (SELECTCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120) cr_date,COUNT(TICKET_ID) cFROMVAPP_ITEM AS viWHERE(SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=row_idAND va.ATTR_ID=555) is not nullAND (SELECTTOP 1 DATEADD(SECOND, CONVERT(INT, ATTR_VALUE), '1970/1/1 08:00:00')FROMVAPP_ITEM_ATTRIBUTES AS vaWHEREva.ITEM_ID=row_idAND va.ATTR_ID=558) is not nullAND datediff(dd,CONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120),GETDATE())<= @tianshuAND person1_root_org_name = @kehuAND 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 ENDGROUP BYCONVERT(VARCHAR(10),(DATEADD(S,CREATED_DATE,'1970/1/1 08:00:00')),120)) AS t12) AS A
'''def sql_zong(kehu='WTC', CCTI_CLASS=' '):'''返回综合查询的拼接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 intset @tianshu=30/*@sla_target_name 定义查询工单的SLA*/declare @sla_target_name nvarchar(2000)set @sla_target_name=N'WTC-P1'/*@group 定义No Onsite time中没有产生上门时间的硬件组的名称*/declare @group nvarchar(2000)'''sqa2 = r"set @group=N'''L1-HW-SH'',''L1-HW-BJ'',''L1-HW-GZ'',''L1-HW-SZ'''"zonghe = sqa1 + sqa2 + sqa3return zonghe
3index.html
<!DOCTYPE html>
<html lang="en"><head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width,initial-scale=1.0, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no" /><script src="https://cdn.bootcss.com/jquery/3.0.0/jquery.min.js"></script><script type="text/javascript" src="https://assets.pyecharts.org/assets/echarts.min.js"></script>
<title>SAI工单系统综合看板</title>
<style type="text/css">
@charset "utf-8";
* {margin: 0;padding: 0;font-family: "microsoft yahei";color: #000;font-size: 14px;}
img{display: block;}
ul {list-style: none;}/*去除ul的黑色点*/
a{text-decoration:none;cursor:pointer;outline:none}
body{ color: #f5f5f5}
.container{margin:0 auto;width: 1220px;}
.block{padding: 0px;}
/* 主菜单 */.head{position: relative;width:100%;background: #00F5FF;border-bottom:1px solid #ddd;height:20px;z-index: 9999}
.head .navbox{width: 1200px;margin: 0 auto;}
.head .nav{display: block; float: left;width:calc(100% - 200px)}
.head .nav .nav_pc{ width: 100%;}
.head .nav ul li{float:left;position: relative;border-right: 1px solid #cccccc;box-sizing: border-box;z-index: 999;}
.head .nav ul li:first-child{border-left: 1px solid #cccccc;}
.head .nav ul li .f_a{line-height:20px;display:block;text-align:center;font-size:16px;color:#fff;}
.head .nav ul li:hover .f_a{background-color:#cf1322;color: #fff; transition:all .3s}
.head .nav ul li .s_navbox{display:none;position: absolute;left: 0;width: 100%; background:#fff;background-color: #00a2ff;z-index: 9999;box-sizing: border-box}
.head .nav ul li .s_navbox .s_a a{color:#fff;font-size:14px;text-transform:uppercase;display:block;text-align: center;padding: 10px 0;border-top: 1px solid #fff;}
.head .nav ul li .s_navbox .s_a a:hover{background-color:#cf1322;color: #fff;}
.m_nav{display:none}
.headlf .m_logo{display: none}
.headrg .m_menuimg{display: none}
.active{background-color: #cf1322}
.activ2{background-color: #cf1322}/* 页面背景颜色*/.body {background-color: rgba(13,30,103,1.000);}/* <!-- 情况一：没有父盒子，即父盒子就是浏览器 --> *//*上右*/.a {width: 33%;height: 49%;position: absolute;top: 9%;right: 0.2%;}/*上中*/.b {width: 33%;height: 49%;position: absolute;top: 9%;right: 33.5%;}/*上左*/.c {width: 33%;height: 49%;position: absolute;top: 9%;right: 66.8%;}/*下右*/.d {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 0.2%;}/*下中*/.e {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 33.5%;}/*下左*/.f {width: 33%;height: 49%;position: absolute;top: 58.5%;right: 66.8%;}/* div放大效果 被阉割*/</style><link rel="stylesheet" type="text/css" href="css/style.css">
<script>function time(){var date = new Date();var year = date.getFullYear();var month = date.getMonth()+1;var day = date.getDate();var week = date.getDay();week="星期"+"日一二三四五六".charAt(week);/*switch(week){case 1:week="星期一";break;case 2:week="星期二";break;case 3:week="星期三";break;case 4:week="星期四";break;case 5:week="星期五";break;case 6:week="星期六";break;case 0:week="星期日";break;}*/var hour =date.getHours();hour=hour<10?"0"+hour:hour;var minute =date.getMinutes();minute=minute<10?"0"+minute:minute;var second = date.getSeconds();second=second<10?"0"+second:second;var currentTime = year+"-"+month+"-"+day+" "+week+" "+hour+":"+minute+":"+second;document.getElementById("time").innerHTML=currentTime;}setInterval("time()",1000);</script>
<script type="text/javascript" src="/static/js/jquery.min.js"></script>
<script type="text/javascript" src="/static/js/jquery.SuperSlide.2.1.js"></script>
<script type="text/javascript" src='/static/js/script.js'></script></head>
<body class="body"><!-- 导航条 -->
<div class="head"><div class="navbox"><!-- 主菜单 --><div class="nav headrg"><ul class="nav_pc"><li><a class="f_a" href="index.html">首页</a><div class="s_navbox"><div class="s_a"></div></div></li><li><a class="f_a" href="#">可视化仪表盘</a><div class="s_navbox"><div class="s_a"><a href="/demo/index">ePSM仪表盘</a><a href="/demo2/index">sAI仪表盘</a></div></div></li><li><a class="f_a" href="#">报表</a><div class="s_navbox"><div class="s_a"><a href="/demo2/ceshi2">Daily_Report_WTCCN-1</a><a href="/demo2/ceshi">Daily_Report_WTCCN-2</a></div></div></li><li><a class="f_a" href="#">SAI工单信息看板</a><div class="s_navbox"><div class="s_a"></div></div></li><li id="time" style="width:200px"></li></ul></div></div>
</div><!-- 情况一：没有父盒子，即父盒子就是浏览器 --><div id="bar" class="a"></div><div id="bar2" class="b "></div><div id="pie1" class="c "></div><div id="line1" class="d "></div><div id="line2" class="e"></div><div id="bar3" class="f"></div><br><!--bar图形，第一个图形--><script>var char1 = echarts.init(document.getElementById('bar'), 'white1', {renderer: 'canvas'});$(function () {fetchData(char1);setInterval(fetchData, 30000);});function fetchData() {$.ajax({type: "GET",url: "/demo2/bar",async:false,dataType: 'json',success: function (result) {char1.setOption(result.data);}});}</script><!--bar2图形，第二个图形--><script>var char2 = echarts.init(document.getElementById('bar2'), 'white', {renderer: 'canvas'});$(function () {fetchDat(char2);setInterval(fetchDat, 30000);});function fetchDat() {$.ajax({type: "GET",url: "/demo2/bar2",async:false,dataType: 'json',success: function (result1) {char2.setOption(result1.data);}});}</script><!--pie饼图，第三个图形--><script>var char3 = echarts.init(document.getElementById('pie1'), 'white', {renderer: 'canvas'});$(function () {fetchDat1(char3);setInterval(fetchDat1, 60000);});function fetchDat1() {$.ajax({type: "GET",url: "/demo2/pie1",dataType: 'json',success: function (result2) {char3.setOption(result2.data);}});}</script><br><!--line1折线图，第四个图形--><script>var char4 = echarts.init(document.getElementById('line1'), 'white', {renderer: 'canvas'});$(function () {fetchDat2(char4);setInterval(fetchDat2, 40000);});function fetchDat2() {$.ajax({type: "GET",url: "/demo2/line1",dataType: 'json',success: function (result2) {char4.setOption(result2.data);}});}</script><!--line2折线图，第五个图形--><script>var char5 = echarts.init(document.getElementById('line2'), 'white', {renderer: 'canvas'});$(function () {fetchDat3(char5);setInterval(fetchDat3, 50000);});function fetchDat3() {$.ajax({type: "GET",url: "/demo2/line2",dataType: 'json',success: function (result2) {char5.setOption(result2.data);}});}</script><!--bar3折线图，第六个图形--><script>var char6 = echarts.init(document.getElementById('bar3'), 'white', {renderer: 'canvas'});$(function () {fetchDat4(char6);setInterval(fetchDat4, 40000);});function fetchDat4() {$.ajax({type: "GET",url: "/demo2/bar3",dataType: 'json',success: function (result2) {char6.setOption(result2.data);}});}</script></body>
</html>
