def __init__(self, jsondata_list, table_name: str):
self.connect = get_db_connector()
self.jsondata_list = jsondata_list
self.table_name = table_name
self.level = table_name.split("_")[1]
self.id_name = self.level + "_id"
self.redis = RedisClient().redis
def get_daily(level: str): # stat
table_name = "dms_"+level+"_avg"
con = get_db_connector()
cursor = con.cursor(dictionary=True)
today = datetime.datetime.today()
datetime_now = datetime.datetime(today.year, today.month, today.day,
DAILY_UPDATE_TIME["hour"], DAILY_UPDATE_TIME["minute"], DAILY_UPDATE_TIME["second"])
# 从avg表中查找当前(00:00)之后五分钟的数据
sql = "SELECT a_efficiency, a_accuracy ,a_workhour, time, {} FROM {} WHERE time between '{}' and '{}' "\
.format(level+"_id", table_name, datetime_now, datetime_now+datetime.timedelta(seconds=DAILY_UPDATE_ITERVAL))
cursor.execute(sql)
avg_data_list = cursor.fetchall()
if not avg_data_list:
raise Exception
return avg_data_list
def get_structure(level: str):
"""
:param level: 要查询的结构的级别
:return: 该级别对应下级的结构 structure:dict, 级别名称level: str
"""
structure = dict()
connection = get_db_connector()
cursor = connection.cursor()
if level is "team":
cursor.execute(
"SELECT team_id FROM sms_team_stat_member GROUP BY team_id")
team_id_list = [i[0] for i in cursor.fetchall()]
for team_id in team_id_list:
cursor.execute(
"SELECT stat_id FROM sms_team_stat_member WHERE team_id = {}".
format(team_id))
structure[team_id] = [i[0] for i in cursor.fetchall()]
elif level in ["group", "workshop"]:
cursor = connection.cursor()
cursor.execute(
"SELECT {} FROM sms_team_group_workshop GROUP BY {}".format(
level + "_id", level + "_id"))
group_id_list = [i[0] for i in cursor.fetchall()]
for group_id in group_id_list:
cursor.execute(
"SELECT {} FROM sms_team_group_workshop WHERE {} = {} GROUP BY {}"
.format(levels[level] + "_id", level + "_id", group_id,
levels[level] + "_id"))
structure[group_id] = [i[0] for i in cursor.fetchall()]
elif level is "dpt":
cursor.execute(
"SELECT workshop_id FROM sms_team_group_workshop GROUP BY workshop_id"
)
workshop_list = [i[0] for i in cursor.fetchall()]
structure[1] = workshop_list
else:
# raise Exception
return None
cursor.close()
connection.close()
return structure, level
# coding: utf-8
import random
from decimal import Decimal
from db_tools.tools.tools import date_range
from db_tools.tools.tools import get_db_connector
# 打开数据库连接
db = get_db_connector()
cursor = db.cursor()
random.seed(12345)
def insert_to_mysql(table_name, begin_date, end_date):
employee_sql = "SELECT employee_id FROM sms_member WHERE type=1;"
cursor.execute(employee_sql)
employee_data = cursor.fetchall()
# 每个工位的标准
stat_standard_sql = "SELECT m.stat_id, t.s_efficiency, t.s_accuracy, t.s_workhour FROM standard_team AS t " \
"JOIN sms_team_stat_member AS m ON t.team_id=m.team_id;"
cursor.execute(stat_standard_sql)
stat_standard_data = cursor.fetchall()
print(len(employee_data))
print(len(stat_standard_data))
for _date in date_range(begin_date, end_date):
# print(_date)
flag = 0
for i in range(0, len(employee_data)):
if i % 3 == 0:
# print(flag)
data = stat_standard_data[flag]
def insert_avg_data(jsondata_list, level):
connect = get_db_connector()
cursor = connect.cursor(dictionary=True)
# 先查询当前avg表的所有数据
sql = "SELECT * FROM dms_{}_avg".format(level)
cursor.execute(sql)
old_avg_data = cursor.fetchall()
# 当前avg表为空或者有空隔时间段，则重新查表计算平均值插入到avg表
if len(old_avg_data) == 0:
cursor.close()
return "insert", jsondata_list
# 先查询最后一条avg的time是否与当前时间相差1min以上
# step2: select avg(*) from dms_{}_online WHERE time >= '{}' and time <= '{}' # 24小时以内
# step3: 把重新查表得到的avg值更新到jsondata_list当中
# 查询距离现在一天时间的所有online数据条数
cur_time = datetime.datetime.strptime(jsondata_list[0]['time'],
"%Y-%m-%dT%H:%M:%S.%f")
# cur_time = jsondata_list[0]["time"]
yesterday = cur_time - datetime.timedelta(days=1) - datetime.timedelta(
minutes=1) # 昨天最早实时数据时间
last_research_time = cur_time - datetime.timedelta(
seconds=RESEARCH_INTERVAL) # 重新查询的最晚时间
sql = "SELECT count(*) FROM dms_{}_avg WHERE time > '{}'".format(
level, last_research_time)
cursor.execute(sql)
if not cursor.fetchone()['count(*)']:
sql = "SELECT AVG(efficiency) as efficiency, AVG(accuracy) as accuracy, AVG(workhour) as workhour, {} as id " \
"FROM dms_{}_online WHERE time >= '{}' AND time <= '{}' GROUP BY {} ORDER BY id"\
.format(level+"_id", level, yesterday, cur_time, level+"_id")
cursor.execute(sql)
new_avg_data = cursor.fetchall()
for index, json_data in enumerate(jsondata_list):
for key in ['efficiency', 'accuracy', 'workhour']:
json_data[key] = new_avg_data[index][key]
return "update", jsondata_list
sql = "SELECT count(*) FROM dms_{}_online \
WHERE time >= '{}' and time <= '{}'".format(
level, yesterday, cur_time)
cursor.execute(sql)
# 正常情况下num是7,200,000
num = cursor.fetchone()
old_online_data_num = num['count(*)'] / len(jsondata_list) - 1
# 查询距离现在一天时间的最早的online数据
sql = "SELECT * FROM dms_{}_online \
WHERE time >= '{}' and time <= '{}' LIMIT 1".format(
level, yesterday, cur_time)
cursor.execute(sql)
earliest_online_data = cursor.fetchone()
for i, one_old_avg_data in enumerate(old_avg_data): # 上次的平均值
if earliest_online_data['time'] > yesterday:
jsondata_list[i] = get_avg_no_fine(jsondata_list[i],
one_old_avg_data,
old_online_data_num)
else:
jsondata_list[i] = get_avg_fine(jsondata_list[i],
earliest_online_data,
one_old_avg_data,
old_online_data_num)
cursor.close()
return "update", jsondata_list
