def editPlan(request):
dateListstr = str(request.POST.get("assy_cds"))
dateLists = json.loads(dateListstr)
cur = models_common.get_cur()
datestr = time.strftime('%Y-%m-%d', time.localtime(time.time())) # 当前时间年月日
for item in dateLists:
assy_cd = item["assy_cd"]
plan_cnt = item["plan_cnt"]
green = item["green"]
olive = item["olive"]
orange = item["orange"]
sort_num = item["sort_num"]
where = " WHERE shift_date='" + str(
datestr) + "' AND assy_cd='" + assy_cd + "'"
sql_num = listSql() + where
cur.execute(sql_num)
plans = cur.fetchall()
if len(plans) == 0: # 没有找到今天的数据就新建
sql = '''
INSERT INTO m_plan ( assy_cd, plan_cnt, green, olive, orange,sort_num,shift_date)
VALUES ( %s,%s,%s,%s,%s,%s,%s)
'''
cur.execute(
sql,
(assy_cd, plan_cnt, green, olive, orange, sort_num, datestr))
else:
sql = "UPDATE m_plan SET plan_cnt = '%s',green= '%s',olive= '%s',orange= '%s',sort_num= '%s' WHERE assy_cd = '%s' AND shift_date= '%s' " % (
plan_cnt, green, olive, orange, sort_num, assy_cd, datestr)
cur.execute(sql)
return JsonResponse({"code": "0"})
def getPlans(request):
# 解析参数
assy_cd = str(request.GET.get("assy_cd"))
# currentPage = int(request.GET.get("currentPage"))
# pageSize = int(request.GET.get("pageSize"))
# # 构建分页数据start与end
# start = (currentPage - 1) * pageSize
# end = currentPage * pageSize
# 构建查询
where = " WHERE 1=1 "
if assy_cd:
where += " AND assy_cd LIKE '%" + assy_cd + "%'"
where += " AND shift_date in (select max(shift_date) from m_plan b where b.assy_cd=assy_cd)"
sql_result = listSql() + where
cur = models_common.get_cur()
cur.execute(sql_result)
results = cur.fetchall()
show_results = []
for item in results:
show_result = {}
show_result["assy_cd"] = item[0]
show_result["plan_cnt"] = item[1]
show_result["green"] = item[2]
show_result["olive"] = item[3]
show_result["orange"] = item[4]
show_result["sort_num"] = item[5]
show_results.append(show_result)
return JsonResponse({"data": show_results})
def getUsers(request):
# 解析参数
user_name = str(request.GET.get("user_name"))
true_name = str(request.GET.get("true_name"))
currentPage = int(request.GET.get("currentPage"))
pageSize = int(request.GET.get("pageSize"))
# 构建分页数据start与end
start = (currentPage - 1) * pageSize
end = currentPage * pageSize
# 构建查询
where = " where is_admin=false "
if user_name:
where += " AND user_name LIKE '%" + user_name + "%'"
if true_name:
where += " AND true_name LIKE '%" + true_name + "%'"
where = where + " ORDER BY id"
sql_num = listSql() + where
cur = models_common.get_cur()
cur.execute(sql_num)
num = cur.fetchall()
count = len(num)
sql_result = listSql() + where + " LIMIT " + str(end) + " OFFSET " + str(
start)
cur.execute(sql_result)
results = cur.fetchall()
show_results = []
for item in results:
show_result = {}
show_result["id"] = item[0]
show_result["user_name"] = item[1]
show_result["true_name"] = item[2]
show_result["see_assys"] = item[3]
show_result["see_plan"] = item[4]
show_results.append(show_result)
return JsonResponse({"totalDataNumber": count, "data": show_results})
def deletePlan(request):
assy_cd = str(request.GET.get("assy_cd"))
cur = models_common.get_cur()
sql = '''
DELETE FROM m_plan where assy_cd= %s
'''
cur.execute(sql, (assy_cd, ))
return JsonResponse({"code": "0"})
def deleteUser(request):
id = str(request.GET.get("id"))
cur = models_common.get_cur()
sql = '''
DELETE FROM m_user where id= %s
'''
cur.execute(sql, (id, ))
return JsonResponse({"code": "0"})
def saveUserAssy(request):
user_id = str(request.POST.get("user_id"))
assy_cds_str = str(request.POST.get("assy_cds"))
assy_cds = json.loads(assy_cds_str)
update = ""
for item in assy_cds:
if item["is_select"]:
update += str(item["assy_cd"]) + ","
update_sql = update[:-1] # 去掉最后一个,
cur = models_common.get_cur()
sql = "UPDATE m_user SET see_assys = '%s' WHERE id = '%s'" % (update_sql,
user_id)
cur.execute(sql)
return JsonResponse({"code": "0"})
def editUser(request):
id = str(request.POST.get("id"))
true_name = str(request.POST.get("true_name"))
user_name = str(request.POST.get("user_name"))
see_plan = str(request.POST.get("see_plan"))
sql_is = listSql(
) + " WHERE user_name ='" + user_name + "' AND id <> '" + id + "'"
cur = models_common.get_cur()
cur.execute(sql_is)
results = cur.fetchall()
if len(results) > 0:
return JsonResponse({"code": "1", "msg": "用户名已经存在"})
else:
sql = "UPDATE m_user SET true_name = '%s',user_name= '%s',see_plan= '%s' WHERE id = '%s'" % (
true_name, user_name, see_plan, id)
cur.execute(sql)
return JsonResponse({"code": "0"})
def updatePassword(request):
user_id = str(request.POST.get("user_id"))
oldpassword = str(request.POST.get("oldpassword"))
newpassword = str(request.POST.get("newpassword"))
sql_is = listSql(
) + " WHERE id='" + user_id + "' AND user_password='******'"
cur = models_common.get_cur()
cur.execute(sql_is)
results = cur.fetchall()
if len(results) == 0:
return JsonResponse({"code": "1", "msg": "密码不正确"})
else:
sql = '''
UPDATE m_user SET user_password = %s WHERE id = %s
'''
cur.execute(sql, (newpassword, user_id))
return JsonResponse({"code": "0"})
def getAllPlans(request):
user_name = str(request.GET.get("user_name"))
cur = models_common.get_cur()
sql_assy = '''
SELECT see_assys from m_user WHERE user_name= %s
'''
cur.execute(sql_assy, (user_name, ))
assys = cur.fetchall()
if list(assys)[0][0]:
assy = list(assys)[0][0]
user_assys = assy.split(',')
where = " WHERE shift_date in (select max(shift_date) from m_plan b where b.assy_cd=assy_cd) ORDER BY sort_num "
sql_num = listSql() + where
cur.execute(sql_num)
results = cur.fetchall()
show_results = []
for item in results:
isfind = False
for assy in user_assys:
if item[0] == assy:
show_result = {}
show_result["assy_cd"] = item[0]
show_result["is_select"] = True
show_results.append(show_result)
isfind = True
break
if isfind == False:
show_result = {}
show_result["assy_cd"] = item[0]
show_result["is_select"] = False
show_results.append(show_result)
return JsonResponse({"data": show_results})
else:
sql_assy = listSql()
cur.execute(sql_assy, (user_name, ))
results = cur.fetchall()
show_results = []
for item in results:
show_result = {}
show_result["assy_cd"] = item[0]
show_result["is_select"] = True
show_results.append(show_result)
return JsonResponse({"data": show_results})
def insertPlan(request):
assy_cd = str(request.POST.get("assy_cd"))
plan_cnt = int(request.POST.get("plan_cnt"))
green = int(request.POST.get("green"))
olive = int(request.POST.get("olive"))
orange = int(request.POST.get("orange"))
sort_num = int(request.POST.get("sort_num"))
sql_is = listSql() + " WHERE assy_cd='" + assy_cd + "'"
cur = models_common.get_cur()
cur.execute(sql_is)
results = cur.fetchall()
if len(results) > 0:
return JsonResponse({"code": "1", "msg": "assy_cd已经存在"})
else:
sql = '''
INSERT INTO m_plan ( assy_cd, plan_cnt, green, olive, orange,sort_num )
VALUES ( %s,%s,%s,%s,%s,%s)
'''
cur.execute(sql, (assy_cd, plan_cnt, green, olive, orange, sort_num))
return JsonResponse({"code": "0"})
def insertUser(request):
true_name = str(request.POST.get("true_name"))
user_name = str(request.POST.get("user_name"))
see_plan = str(request.POST.get("see_plan"))
user_password = str(request.POST.get("user_password"))
is_admin = False
sql_is = listSql() + " WHERE user_name='" + user_name + "'"
cur = models_common.get_cur()
cur.execute(sql_is)
results = cur.fetchall()
if len(results) > 0:
return JsonResponse({"code": "1", "msg": "用户名已经存在"})
else:
sql = '''
INSERT INTO m_user ( true_name, user_name, see_plan,is_admin,user_password)
VALUES ( %s,%s,%s,%s,%s)
'''
cur.execute(sql,
(true_name, user_name, see_plan, is_admin, user_password))
return JsonResponse({"code": "0"})
def user_Login(request):
Logger.write_log("用户登陆")
username = str(request.POST.get("username"))
password = str(request.POST.get("password"))
where = " WHERE "
where += " user_name = '" + username + "'"
where += " AND user_password = '******'"
sql_num = findSql() + where
cur = models_common.get_cur()
cur.execute(sql_num)
results = cur.fetchall()
count = len(results)
for item in results:
show_result = {}
show_result["id"] = item[0]
show_result["user_name"] = item[1]
show_result["true_name"] = item[2]
show_result["is_admin"] = item[3]
show_result["see_plan"] = item[4]
if count > 0:
return JsonResponse({"code": "0", "data": show_result})
else:
return JsonResponse({"code": "1", "msg": "用户名或密码不正确"})
def initData(request):
user_id = str(request.GET.get("user_id"))
select_date = request.GET.get("select_date") # 历史画面传参
assy_cd = []
result = [] # chart的数据集
rows_1 = []
table_result = [] # table的数据集
cur = models_common.get_cur()
# 1. 从m_plan中获得所有assy_cd
SQL_1 = 'SELECT assy_cd FROM m_plan WHERE shift_date in (select max(shift_date) from m_plan b where b.assy_cd=assy_cd) ORDER BY sort_num '
cur.execute(SQL_1)
allrows = cur.fetchall() # 查出所有的assy
SQL_1 = 'SELECT see_assys FROM m_user WHERE id=' + user_id
cur.execute(SQL_1)
select_rows = cur.fetchall() # 查出用户选择的assy
if list(select_rows)[0][0]: # 如果用户选择的不为null,取用户选择的
assy = list(select_rows)[0][0]
rows_select = assy.split(',')
for item in allrows:
for select in rows_select:
if item[0] == select:
rows_1.append(select)
else:
for item in allrows:
rows_1.append(item[0])
# 2. 获取所有assy的数据
SQL_2 = '''
SELECT
m_plan.assy_cd,
t_history.timepart_label,
t_history.cnt_result,
t_history.from_at,
t_history.to_at,
m_plan.plan_cnt AS plan_result,
CASE
(
date_part ( 'hours', t_history.update_at - t_history.from_at ) * 60 + date_part ( 'minute', t_history.update_at - t_history.from_at )
)
WHEN 0 THEN
0 ELSE ROUND(
t_history.cnt_result * 60 / (
date_part ( 'hours', t_history.update_at - t_history.from_at ) * 60 + date_part ( 'minute', t_history.update_at - t_history.from_at )
)
)
END AS unit_result,
m_plan.green,
m_plan.olive,
m_plan.orange,
t_history.shift_date,
(date_part( 'hours', t_history.to_at - t_history.from_at ) * 60 + date_part( 'minute', t_history.to_at - t_history.from_at )) AS Differ,
(date_part( 'hours', t_history.update_at - t_history.from_at) * 60 + date_part('minute',t_history.update_at - t_history.from_at)) AS differ_now,
t_history.update_at
FROM
m_plan
LEFT JOIN t_history ON m_plan.assy_cd = t_history.assy_cd
WHERE
t_history.shift_date BETWEEN %s AND %s AND m_plan.shift_date IN
( SELECT MAX (shift_date) FROM m_plan A WHERE A.assy_cd = m_plan.assy_cd AND A.shift_date<= %s)
ORDER BY
t_history.assy_cd,
t_history.from_at;
'''
# 3 查看当前系统时间之前的
SQL_3 = '''
SELECT
assy_cd
FROM
t_history
WHERE
to_char ( to_at, 'yyyy-MM-dd' ) = %s
AND shift_date = %s
AND from_at < %s
GROUP BY assy_cd
'''
# date = '2018-09-10'
now_time = datetime.datetime.now() # 当前时间带时分秒
datestr = time.strftime('%Y-%m-%d', time.localtime(time.time())) # 当前时间年月日
if select_date: # 查询历史记录
date_end = select_date # 今天
date_start = select_date
else:
date_end = datetime.datetime.strptime(datestr, '%Y-%m-%d') # 今天
date_start = date_end + datetime.timedelta(days=-1) # 昨天
cur.execute(SQL_3, (datestr, datestr, now_time)) # 查出各产线今天有没有数据
rows_3 = cur.fetchall()
main_rows = [] # 新的主表
for row_1 in rows_1:
main_row = {}
main_row["assy_cd"] = row_1
main_row["plan_cnt"] = row_1
isFind = False
for row_3 in rows_3: # 每个assy只可能有一条
if row_1 == row_3[0]: # 如果找到了 说明这条产线的shiftdate取当天，没有就取昨天
main_row["shift_date"] = date_end
isFind = True
break
if isFind == False: # 没有找到
main_row["shift_date"] = date_start
main_rows.append(main_row)
cur.execute(SQL_2, (date_start, date_end, date_end))
rows_2 = cur.fetchall()
for item in main_rows:
flash = ""
warn = ""
plan_cnt = [0]
from_at = [0]
cnt_result = [0]
assy_cd.append(item["assy_cd"])
sum_hours = 0
sum_actual = 0
unit_result = ""
green = 0
olive = 0
orange = 0
num = 1
num_sum = 10
plan_result = 0
plan_show = 0
now_result = 0
slopes = [0]
growth = 0
last_slope = 0
sign_slope = 0
if select_date:
item_data = select_date
else:
item_data = item["shift_date"].strftime('%Y-%m-%d')
sum_minute = 0
differ_time = 0
differ_now = 0
actual_minute = 0
timepart_label = [] # table用 th值
table_cnt_result = [] # table用 td值
now_Actual = 0 # table用 合计
for row_2 in rows_2:
row_2_data = row_2[10].strftime('%Y-%m-%d')
if item["assy_cd"] == row_2[0] and item_data == row_2_data:
sum_minute = sum_minute + int(row_2[11]) # 求各产线合计的时间
for row_2 in rows_2:
row_2_data = row_2[10].strftime('%Y-%m-%d')
if item["assy_cd"] == row_2[
0] and item_data == row_2_data: # 是assy的数据且条件符合
timepart_label.append(row_2[1]) # table用
differ_time = differ_time + int(row_2[11]) # 获取相差的事件数(分钟)
plan_result = int(int(row_2[5]) * (differ_time / sum_minute))
plan_cnt.append(plan_result)
from_at.append(str(row_2[1]).split('-')[1])
result_time = datetime.datetime.strptime(
str(row_2[3]), "%Y-%m-%d %H:%M:%S") #from的时间
result_time_to = datetime.datetime.strptime(
str(row_2[4]), "%Y-%m-%d %H:%M:%S") #to的时间
if now_time >= result_time and now_time <= result_time_to: #计算当前显示的plan的值
minutes = dateDiffInMinute(result_time, now_time)
actual_minute = actual_minute + minutes
elif now_time > result_time_to:
actual_minute = actual_minute + int(row_2[11])
if result_time < now_time:
if now_time < result_time_to: #找到到当前时间的timepart
differ_now = int(row_2[12]) # 获取相差的时间数(分钟)实际的
last_timepart = int(row_2[11]) # 获取相差的时间数(分钟)总的
if differ_now == 0:
growth = 0
else:
growth = int(row_2[2]) * last_timepart / (
differ_now) # 记录增长量/当前产量
last_slope = int(
sum_actual +
growth) # 记录except值(当前的sum_actual还是上一个timepart的值)
slopes.append(last_slope)
sum_actual = sum_actual + int(row_2[2])
else:
sum_actual = sum_actual + int(row_2[2])
slopes.append(sum_actual) # 记录except值
cnt_result.append(sum_actual)
unit_result = row_2[6]
green = row_2[7]
olive = row_2[8]
orange = row_2[9]
now_result = sum_actual
sign_slope = sum_actual # 记录时间断点时候值
plan_show = int(
int(row_2[5]) * (actual_minute / sum_minute))
# table用
color_green = False
color_olive = False
color_orange = False
color_red = False
# 算uph
table_differ = int(row_2[11])
table_value = int(row_2[2])
table_unit = table_value * 60 / table_differ # 实际uph
if table_unit >= green:
color_green = True
elif table_unit < green and table_unit >= olive:
color_olive = True
elif table_unit < olive and table_unit >= orange:
color_orange = True
else:
color_red = True
table_cnt = {
'value': str(row_2[2]),
'color_green': color_green,
'color_olive': color_olive,
'color_orange': color_orange,
'color_red': color_red,
'color_nodata': False
}
table_cnt_result.append(table_cnt)
now_Actual = sum_actual
else: # 一旦发现超出查询时间
sum_actual = sum_actual + int(row_2[2])
cnt_result.append(sign_slope)
now_timepart = int(row_2[11]) # 获取相差的时间数(分钟)
now_growth = growth * now_timepart / last_timepart
now_slope = int(now_growth + last_slope) # 上个的y值+增长量
slopes.append(now_slope)
last_slope = now_slope
table_cnt = {
'value': 'n/a',
'color_green': False,
'color_olive': False,
'color_orange': False,
'color_red': False,
'color_nodata': True
}
table_cnt_result.append(table_cnt) # table用
num = num + 1
# table变颜色用
color_green = False
color_olive = False
color_orange = False
color_red = False
color_nodata = False
if unit_result: # 判断当前uph变色
if unit_result >= green:
flash = "green"
color_green = True
elif unit_result < green and unit_result >= olive:
flash = "olive"
color_olive = True
elif unit_result < olive and unit_result >= orange:
flash = "orange"
color_orange = True
else:
flash = "red"
color_red = True
else:
flash = "red"
color_red = True
if now_result:
if now_result >= plan_show:
warn = "green"
else:
warn = "red"
# assy_cd chart的标题
# plan_cnt plan的线
# from_at x轴
# cnt_result Actual的线
# slope Except的线
# flash chart的闪烁颜色
# plan_result 副标题plan的值
# now_result 副标题Actual的值
# datatime 标题的时间
# warn 标题前圆圈的闪烁颜色
result.append({
'assy_cd': item["assy_cd"],
'plan_cnt': plan_cnt,
'from_at': from_at,
'cnt_result': cnt_result,
'slope': slopes,
'flash': flash,
'plan_result': plan_show,
'now_result': now_result,
'datatime': item_data,
'warn': warn
})
table_result.append({
'assy_cd': item["assy_cd"],
'datatime': item_data,
'timepart_label': timepart_label,
'table_cnt_result': table_cnt_result,
'now_Actual': now_Actual,
'color_green': color_green,
'color_olive': color_olive,
'color_orange': color_orange,
'color_red': color_red,
'color_nodata': color_nodata
})
return JsonResponse({"chart_data": result, "table_data": table_result})
