CREATE TABLE `device` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`execid` int(11) NOT NULL,
`device_number` int(11) DEFAULT NULL,
`project_name` varchar(255) DEFAULT NULL,
`case_field` varchar(255) DEFAULT NULL,
`case_content` varchar(255) DEFAULT NULL,
`triggertime` datetime DEFAULT NULL,
`showtime` datetime DEFAULT NULL,
`offlinetime` datetime DEFAULT NULL,
`starttime` datetime DEFAULT NULL,
`endtime` datetime DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=gb18030;
# !/usr/bin/python
# -*- coding: utf-8 -*-
"""
@Author : Charles
@File : db_func.py
@Create Time: 2022-06-21 09:18
@Description:
"""
import time
import pymysql
def connect_db():
url = "127.0.0.1"
name = "root"
pwd = "123456"
dataBase = "XXXXX"
return pymysql.connect(host=url, port=3306, user=name, passwd=pwd, db=dataBase)
def query_database(db, sql):
cursor = db.cursor()
try:
if isinstance(sql, str):
cursor.execute(sql)
result = list(cursor.fetchall())
else:
result = []
for sq in sql:
cursor.execute(sq)
result.append(list(cursor.fetchall()))
except Exception as err:
result = ''.join(('An db query exception happened: ', str(err)))
# db.close() # 关闭数据库连接
return result
def update_db(db, sql):
cursor = db.cursor()
try:
if isinstance(sql, str):
cursor.execute(sql)
db.commit()
else:
print('sql 不是一个合格的字符串：{}'.format(sql))
except Exception as err:
result = ''.join(('An db update exception happened: ', str(err)))
db.rollback()
print(result)
# 数据库数据插入更新
def db_insert(db, sql):
cursor = db.cursor()
i = 0
try:
cursor.execute(sql)
db.commit()
result = 'db insert success'
except Exception as err:
db.rollback()
result = 'An db insert exception happened: ' + str(err) + ' ' + str(i)
db.close() # 关闭数据库连接
return result
def close_db(db):
try:
db.close()
except Exception as err:
result = ''.join(('An db closed exception happened: ', str(err)))
def get_current_time():
return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
# !/usr/bin/python
# -*- coding: utf-8 -*-
"""
@Author : Charles
@File : project_index.py
@Create Time: 2022-06-21 09:53
@Description:
"""
import datetime
import time
from flask import *
from common import db_func
app = Flask(__name__)
@app.route('/')
def display():
"""
最新一轮回归测试的整体页面展示
:return:
"""
db = db_func.connect_db()
# 如果实时数据正常显示，就把设备掉线时间默认设置为"1970-01-01 08:00:00" ，实时数据加载时长=数据显示时间-设备被触发时间
# 如果设备显示掉线页面，就把实时数据时间默认设置为"1970-01-01 08:00:00" ，实时数据加载时长=设备掉线时间-设备被触发时间
sql_all = 'SELECT device_number, project_name, case_field, case_content, ' \
'(CASE WHEN offlinetime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,showtime) ' \
'WHEN showtime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,offlinetime) else 0 ' \
'END) as duration, triggertime, showtime, offlinetime, starttime, endtime, status FROM device ' \
'where execid in (select max(execid) from device) ;'
# 如果实时数据正常显示，就把设备掉线时间默认设置为"1970-01-01 08:00:00" ，实时数据加载时长=数据显示时间-设备被触发时间
# 如果设备显示掉线页面，就把实时数据时间默认设置为"1970-01-01 08:00:00" ，实时数据加载时长=设备掉线时间-设备被触发时间
sql_fail = 'SELECT device_number, project_name, case_field, case_content, ' \
'(CASE WHEN offlinetime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,showtime) ' \
'WHEN showtime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,offlinetime) else 0 ' \
'END) as duration, starttime, endtime, status FROM device ' \
'where status="fail" and execid in (select max(execid) from device) ;'
pass_total_duration = 'SELECT CAST(SUM(TIMESTAMPDIFF(SECOND,triggertime,showtime)) AS CHAR) AS duration FROM device ' \
'WHERE case_field="当前楼层" AND STATUS="pass" AND TIMESTAMPDIFF(SECOND,triggertime,showtime) <= "10" ' \
'AND execid IN (SELECT MAX(execid) FROM device) '
fail_total_duration = 'SELECT CAST(SUM(TIMESTAMPDIFF(SECOND,triggertime,offlinetime)) AS CHAR) AS duration FROM device ' \
'WHERE case_field="当前楼层" AND STATUS="fail" AND TIMESTAMPDIFF(SECOND,triggertime,offlinetime) > "10" ' \
'AND execid IN (SELECT MAX(execid) FROM device) '
device_total_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND execid IN (SELECT MAX(execid) FROM device) '
device_pass_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND STATUS="pass" AND execid IN (SELECT MAX(execid) FROM device) '
device_fail_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND STATUS="fail" AND execid IN (SELECT MAX(execid) FROM device) '
db_res_all = db_func.query_database(db, sql_all)
de_res_fail = db_func.query_database(db, sql_fail)
db_pass_total_duration = db_func.query_database(db, pass_total_duration)
db_device_total_number = db_func.query_database(db, device_total_number)
db_device_pass_number = db_func.query_database(db, device_pass_number)
db_device_fail_number = db_func.query_database(db, device_fail_number)
# Average Loading Time
if db_device_pass_number[0][0] == 0:
average_loading_time = "%.2f" % 0
else:
average_loading_time = "%.2f" % (int(db_pass_total_duration[0][0]) / int(db_device_pass_number[0][0]))
print("Average_loading_time: ", average_loading_time)
# Averavge Loading Time Less Than 10s
if db_device_pass_number[0][0] == 0:
average_less_than_10s = "%.2f" % 0
else:
average_less_than_10s = "%.2f" % (int(db_pass_lessthan_total_duration[0][0])/int(db_device_pass_number[0][0]))
print("Averavge Loading Time Less Than 10s: ", average_less_than_10s)
# Percentage of Loading Time Less Than 10s
if db_device_pass_number[0][0] == 0:
percentage_less_than_10s = "{:.2%}".format(0)
else:
percentage_less_than_10s = "{:.2%}".format(int(db_device_lessthan_pass_number[0][0])/int(db_device_pass_number[0][0]))
print("Percentage of Loading Time Less Than 10s: ", percentage_less_than_10s)
# Percentage of Loading Time Over 10s
if db_device_pass_number[0][0] == 0:
percentage_over_10s = "{:.2%}".format(0)
else:
percentage_over_10s = "{:.2%}".format(int(db_device_over_pass_number[0][0])/int(db_device_pass_number[0][0]))
print("Percentage of Loading Time Over 10s: ", percentage_over_10s)
# 获得SQL语句查询内容
db_res = [average_loading_time, average_less_than_10s, percentage_less_than_10s, percentage_over_10s, db_res_fail, db_res_pass_over_10s, db_res_all]
if db_res:
return render_template("/koneview_page.html", content=db_res)
else:
pass
if __name__ == "__main__":
app.run(host='xx.xx.xx.xx', port=5500, debug=True)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>MyProject Monitor Platform</title>
<link rel="icon" href="https://www.myproject.cn/zh/Images/myproject-logo-76x52_tcm156-8930.png">
<script src="static/lib/jquery-1.11.1.min.js" type="text/javascript"></script>
<div class="navi-brand">
<a class="logo" onclick="zhugeTrack('顶栏按钮点击',{'按钮名称':'LOGO'});" href="https://www.myproject.com/en/" target="_blank"> <img src="/static/images/myprojectLOGO.png" style="text-align: left; width: 265px; height: 50px; position: absolute; left: 62px; top: 30px; margin-left: -60px;margin-top: -20px;"></a>
</div>
<style type="text/css">
.bg{
background-image: url("https://pic4.zhimg.com/80/v2-052324a9dae7a6feb2e5ddf6f68ad8c6_720w.jpg");
background-repeat: repeat;
}
em{
color: red;
font-style: normal;
}
table{
border-spacing: 0;
width: 100%;
border: 1px solid black;
}
thead th {
border: 1px solid black;
/*text-align: center;*/
width: auto;
color: blueviolet;
}
th{
border: 1px solid black;
text-align: left;
width: auto;
background-color: #69ABD6;
}
td{
border: 1px solid black;
text-align: left;
}
</style>
</head>
<body class="bg">
<table style="margin-bottom: -1px" class="left" id="table_top_header">
<h1 align="center" style="color:#0071B9 ; font-size:30px">MyProject Monitor Platform</h1>
<h3 align="left" style="color:#0071B9 ; font-size:20px">Basic Information</h3>
<tr>
<th>Owner</th>
<th>Project Name</th>
<th>Running Environment</th>
<th>Running System</th>
<th>Running Equipment</th>
<th>Testing Phase</th>
<th>Monitor Frequency</th>
<th>Test Report</th>
</tr>
<tr>
<td>Charles</td>
<td>MyProject</td>
<td>Production</td>
<td>Windows 10 64bit</td>
<td>Wechat DevTools</td>
<td>Regression Test</td>
<td>One day/time</td>
<td><a style="background: #eee4a6" href="http://ip:port/" target="_blank">Minium</a></td>
</tr>
</table>
<table style="margin-bottom: -1px" class="left" id="table_analysis">
<tbody>
<h3 align="left" style="color:#0071B9 ; font-size:20px">LiveData Analysis</h3>
<tr>
<th>Average Loading Time(sec)</th>
<th>Percentage of Loading Time Less Than 10s</th>
<th>Percentage of Loading Time Over 10s</th>
</tr>
<div >
<div id="ChangelistTable0">
<tr>
<td class="text-center" >
<div>
{% if content[2] %}
<span style="background: #e8e272"> <a>{{content[2]}}</a> </span>
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[3] %}
<span style="background: #7ad9f4"> <a>{{content[3]}}</a> </span>
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[4] %}
<span style="background: #e999d4"> <a>{{content[4]}}</a> </span>
{% else %}
{% endif %}
</div>
</td>
</tr>
</div>
</div>
</tbody>
</table>
<table style="margin-bottom: -1px" class="left" id="table_header">
<tbody>
<h3 align="left" style="color:#0071B9 ; font-size:20px">TestCase Failed</h3>
<tr>
<th>No</th>
<th>Device Number</th>
<th>Project Name</th>
<th>Monitor Field</th>
<th>Live Data</th>
<th>Duration(s)</th>
<th>Latest Case Executed Time</th>
<th>Latest Case Ended Time</th>
<th>Latest Case Result</th>
</tr>
<div >
<div id="ChangelistTable1">
{% for i in range(content[1]| length) %}
<tr>
<td>
{{ i+1 }}
</td>
<td class="text-center" >
<div>
{% if content[1][i][0] %}
{{ content[1][i][0]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td>
<div>
{% if content[1][i][1] %}
{{ content[1][i][1]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][2] %}
{{ content[1][i][2]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][3] %}
{{ content[1][i][3]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][4] %}
{% if content[1][i][4]<=10 %}
<span style="background: lightseagreen"> <a>{{content[1][i][4]}}</a> </span>
{% else %}
<span style="background: lightcoral"> <a>{{content[1][i][4]}}</a></span>
{% endif %}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][5] %}
{{ content[1][i][5]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][6] %}
{{ content[1][i][6]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[1][i][7] %}
{% if content[1][i][7]=='pass' %}
<span style="background: mediumseagreen"> <a>PASS</a> </span>
<!-- <span style="background: #50d2c8"> <a href={{content[0][i][6]}} target="_blank">Pass</a> </span>-->
{% elif content[1][i][7]=='fail' %}
<span style="background: orangered"> <a>FAIL</a> </span>
<!-- <span style="background: red"> <a href={{content[0][i][6]}} target="_blank">Fail</a> </span>-->
{% else %}
<span style="background: sandybrown"> <a>Scheduled</a> </span>
{% endif %}
{% else %}
{% endif %}
</div>
</td>
</tr>
{% endfor %}
</div>
</div>
</tbody>
</table>
<table style="margin-bottom: -1px" class="left" id="table_list">
<tbody>
<h3 align="left" style="color:#0071B9 ; font-size:20px">TestCase Description</h3>
<tr>
<th>No</th>
<th>Device Number</th>
<th>Project Name</th>
<th>Monitor Field</th>
<th>Live Data</th>
<th>Duration(s)</th>
<th>Latest Triggered Time</th>
<th>Latest Showed Time</th>
<th>Latest Dropped Time</th>
<th>Latest Case Executed Time</th>
<th>Latest Case Ended Time</th>
<th>Latest Case Result</th>
</tr>
<div >
<div id="ChangelistTable">
{% for i in range(content[0]| length) %}
<tr>
<td>
{{ i+1 }}
</td>
<td class="text-center" >
<div>{{ content[0][i][0]| safe }}</div>
</td>
<td>
<div>{{ content[0][i][1]| safe }}</div>
</td>
<td class="text-center" >
<div>{{ content[0][i][2]| safe }}</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][3] %}
{{ content[0][i][3]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][4] %}
{% if content[0][i][4]<=10 %}
<span style="background: lightseagreen"> <a>{{content[0][i][4]}}</a> </span>
{% else %}
<span style="background: lightcoral"> <a>{{content[0][i][4]}}</a></span>
{% endif %}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][5] %}
{{ content[0][i][5]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][6] %}
{{ content[0][i][6]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][7] %}
{{ content[0][i][7]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][8] %}
{{ content[0][i][8]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][9] %}
{{ content[0][i][9]| safe }}
{% else %}
{% endif %}
</div>
</td>
<td class="text-center" >
<div>
{% if content[0][i][10] %}
{% if content[0][i][10]=='pass' %}
<span style="background: mediumseagreen"> <a>PASS</a> </span>
<!-- <span style="background: #50d2c8"> <a href={{content[0][i][6]}} target="_blank">Pass</a> </span>-->
{% elif content[0][i][10]=='fail' %}
<span style="background: orangered"> <a>FAIL</a> </span>
<!-- <span style="background: red"> <a href={{content[0][i][6]}} target="_blank">Fail</a> </span>-->
{% else %}
<span style="background: sandybrown"> <a>Scheduled</a> </span>
{% endif %}
{% else %}
{% endif %}
</div>
</td>
</tr>
{% endfor %}
</div>
</div>
</tbody>
</table>
</body>
</html>
