def show_zwyx_jy():
returnDate = {}
returnDate['status'] = 0
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_jy.jy_name,max(zwyx),min(zwyx) from ca_list inner join ca_jy on ca_jy.Id=ca_list.jy_id group by jy_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 取出所有结果集
jy_zwyx_list = cursor.fetchall()
if jy_zwyx_list:
# 总职位数
count_zw = []
# 平均薪资
avg_zw = []
# 经验分类名
jy_list = []
# 最大薪资
max_xz = []
# 最小薪资
min_xz = []
# 循环遍历存入数据
returnDate['status'] = 1
for item in jy_zwyx_list:
count_zw.append(item[0])
avg_zw.append(round(item[1], 2))
jy_list.append({'text': item[2]})
max_xz.append(item[3])
min_xz.append(item[4])
# 数据json化
returnDate['count_zw'] = count_zw
returnDate['avg_zw'] = avg_zw
returnDate['jy_list'] = jy_list
returnDate['max_xz'] = max_xz
returnDate['min_xz'] = min_xz
return jsonify(returnDate)
def view_patient():
if session.get('username'):
if session.get('usertype') == "rde":
cursor.execute("""SELECT * FROM `patient`""")
val = cursor.fetchall()
print(val[0])
return render_template("viewPatient.html", value=val)
def show_zwyx_gsxz():
returnDate = {}
# 查询地点和薪资的关系，职位总数，平均薪资
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_gsxz.gsxz_name,max(zwyx),min(zwyx) from ca_list inner join ca_gsxz on ca_gsxz.Id=ca_list.gsxz_id group by gsxz_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 取出所有结果集
gsxz_zwyx_list = cursor.fetchall()
if gsxz_zwyx_list:
returnDate['status'] = 1
# 总职位数
count_zw = []
# 平均薪资
avg_zw = []
# 公司规模名
gsxz_list = []
# 最大薪资
max_xz = []
# 最小薪资
min_xz = []
# 循环遍历存入数据
for item in gsxz_zwyx_list:
count_zw.append({'name': item[2], 'value': item[0]})
avg_zw.append({'name': item[2], 'value': round(item[1], 2)})
gsxz_list.append(item[2])
max_xz.append(item[3])
min_xz.append(item[4])
# 数据json化
returnDate['count_zw'] = count_zw
returnDate['avg_zw'] = avg_zw
returnDate['gsxz_list'] = gsxz_list
returnDate['max_xz'] = max_xz
returnDate['min_xz'] = min_xz
return jsonify(returnDate)
def show_zwyx_gsgm():
returnDate = {}
returnDate['status'] = 0
# 查询地点和薪资的关系，职位总数，平均薪资
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_gsgm.gsgm_name from ca_list inner join ca_gsgm on ca_gsgm.Id=ca_list.gsgm_id group by gsgm_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 取出所有结果集
gsgm_zwyx_list = cursor.fetchall()
if gsgm_zwyx_list:
returnDate['status'] = 1
# 总职位数
count_zw = []
# 平均薪资
avg_zw = []
# 公司规模名
gsgm_list = []
# 循环遍历存入数据
for item in gsgm_zwyx_list:
count_zw.append({'name': item[2], 'value': item[0]})
avg_zw.append({'name': item[2], 'value': round(item[1], 2)})
gsgm_list.append(item[2])
returnDate['count_zw'] = count_zw
returnDate['avg_zw'] = avg_zw
returnDate['gsgm_list'] = gsgm_list
return jsonify(returnDate)
def view():
P_id = request.form.get("ID")
cursor.execute(
"""SELECT * FROM `patient` WHERE `ws_pat_id` LIKE '{}'""".format(P_id))
detail = cursor.fetchall()
print(detail)
cursor.execute(
"""SELECT `ws_med_id` FROM `track_medicine` WHERE `ws_pat_id` LIKE '{}'"""
.format(P_id))
all_med = cursor.fetchall()
cursor.execute(
"""SELECT `ws_qty` FROM `track_medicine` WHERE `ws_pat_id` LIKE '{}'"""
.format(P_id))
all_qty = cursor.fetchall()
medicine_name = []
medicine_rate = []
medicine_amount = []
store = []
for i in all_med:
cursor.execute(
"""SELECT `ws_med_name` FROM `master_med_file` WHERE `ws_med_id` LIKE '{}'"""
.format(i[0]))
nam = cursor.fetchall()
medicine_name.append(nam)
cursor.execute(
"""SELECT `ws_rate` FROM `master_med_file` WHERE `ws_med_id` LIKE '{}'"""
.format(i[0]))
medrate = cursor.fetchall()
medicine_rate.append(medrate)
for j in range(len(all_qty)):
quant = all_qty[j][0]
perrate = medicine_rate[j][0][0]
peramount = quant * perrate
store.append(int(j))
medicine_amount.append(peramount)
conn.close()
return render_template("issue_medicine.html",
details=detail,
pid=P_id,
ALLNAME=medicine_name,
ALLQTY=all_qty,
ALLRATE=medicine_rate,
ALLAMOUNT=medicine_amount,
LENGTH=store)
def show_zwyx_dd():
# 初始化返回的字典
returnDate = {}
returnDate['status'] = 0
# 查询地点和薪资的关系，职位总数，平均薪资
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_dd.province from ca_list inner join ca_dd on ca_dd.Id=ca_list.dd_id group by dd_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 取出所有结果集
dd_zwyx_list = cursor.fetchall()
# 平均薪资
avg_zwyx = {}
# 总职位数
count_zw = {}
if dd_zwyx_list:
# 循环遍历重新构建数据
for value in dd_zwyx_list:
# 取得地点名
dd_name = value[2]
# 判断总职位数是否存在地点名
if not count_zw.has_key(dd_name):
# 地点名不存在则创建记录并存入对应数值
count_zw[dd_name] = {'name': dd_name, 'value': value[0]}
else:
# 存在则在原来基础上增加新的数值
count_zw[dd_name] = {'name': dd_name, 'value': (value[0] + count_zw[dd_name]['value'])}
# 判断平均薪资是否存在地点名
if not avg_zwyx.has_key(dd_name):
# 不存在则创建记录并存入对应数值
avg_zwyx[dd_name] = {'name': dd_name, 'value': round(value[1], 2)}
else:
# 存在则重新计算平均薪资
avg_zwyx[dd_name] = {'name': dd_name,
'value': round((round(value[1], 2) + avg_zwyx[dd_name]['value']) / 2, 2)}
# 重新构建数据
return_count_zw = []
return_avg_zwyx = []
for item in count_zw:
return_count_zw.append(count_zw[item])
for item in avg_zwyx:
return_avg_zwyx.append(avg_zwyx[item])
# 数据汇总
returnDate['count_zw'] = return_count_zw
returnDate['avg_zwyx'] = return_avg_zwyx
returnDate['status'] = 1
return jsonify(returnDate)
def add_diagnostics():
if session.get('username'):
if session.get('usertype') == "dse":
p_id = request.form.get("ID")
test_name = request.form.get("name")
cursor.execute(
"""SELECT * FROM `patient` WHERE `ws_pat_id` LIKE '{}'""".
format(p_id))
detail = cursor.fetchall()
cursor.execute(
"SELECT * FROM `diag_master_file` WHERE `ws_test_name` LIKE '{}'"
.format(test_name))
add = cursor.fetchall()
print(add)
cursor.execute(
"INSERT INTO `track_diag` VALUES ('{}','{}')".format(
p_id, add[0][0]))
conn.commit()
cursor.execute(
"SELECT `ws_test_id` FROM `track_diag` WHERE `ws_pat_id` LIKE '{}'"
.format(p_id))
all_diag = cursor.fetchall()
info = list()
print(all_diag)
for i in all_diag:
cursor.execute(
"SELECT * FROM `diag_master_file` WHERE `ws_test_id` LIKE '{}'"
.format(i[0]))
some = cursor.fetchall()
info.append(some[0])
return render_template("diagnose1.html",
detail=detail,
info=info,
add=add)
return redirect("/login")
def view_diagnostics():
if session.get('username'):
if session.get('usertype') == "dse":
p_id = request.form.get("ID")
cursor.execute(
"""SELECT * FROM `patient` WHERE `ws_pat_id` LIKE '{}'""".
format(p_id))
detail = cursor.fetchall()
cursor.execute(
"SELECT `ws_test_id` FROM `track_diag` WHERE `ws_pat_id` LIKE '{}'"
.format(p_id))
all_diag = cursor.fetchall()
info = list()
print(all_diag)
for i in all_diag:
cursor.execute(
"SELECT * FROM `diag_master_file` WHERE `ws_test_id` LIKE '{}'"
.format(i[0]))
some = cursor.fetchall()
print(some)
info.append(some[0])
return render_template("diagnose1.html", detail=detail, info=info)
return redirect("/login")
def show_dd_zwmc():
returnDate = {}
returnDate['status'] = 0
sql = 'select count(distinct ca_list.zwmc_id) as count_zw,ca_dd.dd_name from ca_list inner join ca_dd on ca_dd.Id=ca_list.dd_id group by ca_list.dd_id'
# 执行sql语句
cursor.execute(sql)
# 职位数
count_zw = []
gsmc_dd_list = cursor.fetchall()
if gsmc_dd_list:
returnDate['status'] = 1
for item in gsmc_dd_list:
count_zw.append({'name': item[1], 'value': item[0]})
returnDate['count_zw'] = count_zw
return jsonify(returnDate)
def show_dd_type():
returnDate = {}
returnDate['status'] = 0
# 查询职位类型与地点、职位、公司、平均薪资分布总数的关系
sql = 'SELECT count(distinct ca_list.dd_id),count(distinct ca_list.zwmc_id),count(distinct ca_list.gsmc_id),avg(ca_list.zwyx),ca_type.`name` from ca_list inner join ca_type ON ca_type.Id =ca_list.type_id GROUP BY ca_list.type_id'
# 执行sql语句
cursor.execute(sql)
type_dd_list = cursor.fetchall()
# 分布地点数
dd_type = {}
# 分布地点最大数
count_dd_type = 0
# 职位数
zw_type = {}
# 公司数
gs_type = {}
# 平均薪资
avg_xz = {}
# 平均薪资最大数
max_avg_xz = 0
# 总平均薪资
all_avg = 0
returnDate['status'] = 1
# 遍历数据
for item in type_dd_list:
dd_type[item[4]] = item[0]
count_dd_type = max(count_dd_type, item[0])
zw_type[item[4]] = item[1]
gs_type[item[4]] = item[2]
avg_xz[item[4]] = round(item[3], 2)
max_avg_xz = max(max_avg_xz, round(item[3], 2))
if all_avg == 0:
all_avg = round(item[3], 2)
else:
all_avg = round((all_avg + round(item[3], 2)) / 2, 2)
# Json化数据
returnDate['dd_type'] = dd_type
returnDate['zw_type'] = zw_type
returnDate['gs_type'] = gs_type
returnDate['avg_xz'] = avg_xz
returnDate['all_avg'] = round(all_avg, 2)
returnDate['count_dd_type'] = count_dd_type + 20
returnDate['max_avg_xz'] = round(max_avg_xz + 1000.0, 2)
return jsonify(returnDate)
def register():
registerForm = RegisterForm()
title = "Register Here"
if registerForm.validate_on_submit():
department = registerForm.department.data
username = registerForm.username.data
password = registerForm.password.data
cursor.execute(
"""SELECT (`type`) FROM `user_login` WHERE `username` LIKE '{}' """
.format(username))
data = cursor.fetchall()
if cursor.rowcount > 0:
flash(f"{username} already exist")
return redirect(url_for('register'))
else:
cursor.execute(
"""INSERT INTO `user_login` (`username`,`password`,`type`) VALUES ('{}','{}','{}')"""
.format(username, password, department))
conn.commit()
return redirect("/login")
return render_template("register.html", title=title, form=registerForm)
def show_zwyx_xl():
returnDate = {}
returnDate['status'] = 0
returnDate['data'] = {}
# 查询地点和薪资的关系，职位总数，平均薪资
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_xl.xl_name,max(zwyx),min(zwyx) from ca_list inner join ca_xl on ca_xl.Id=ca_list.xl_id group by xl_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 取出所有结果集
xl_zwyx_list = cursor.fetchall()
if xl_zwyx_list:
returnDate['status'] = 1
# 总职位数
count_zw = []
# 平均薪资
avg_zw = []
# 学历名
xl_list = []
# 最大薪资
max_xz = []
# 最小薪资
min_xz = []
# 循环遍历存入数据
for item in xl_zwyx_list:
count_zw.append(item[0])
avg_zw.append(round(item[1], 2)) # 精度保留2位小数
xl_list.append(item[2])
max_xz.append(item[3])
min_xz.append(item[4])
# 数据json化
returnDate['count_zw'] = count_zw
returnDate['avg_zw'] = avg_zw
returnDate['xl_list'] = xl_list
returnDate['max_xz'] = max_xz
returnDate['min_xz'] = min_xz
return jsonify(returnDate)
def generate_bille():
if session.get('username'):
if session.get('usertype') == "rde":
patientData = {}
med_detail = {}
diag_detail = {}
todaysDate = datetime.now().strftime("%m/%d/%Y")
totalRoomCost = 0
doj = 0
patientid = request.args.get("patientid")
if patientid:
cursor.execute(
"""SELECT (`*`) FROM `patient` WHERE `ws_pat_id` LIKE '{}' """
.format(patientid))
patientData = cursor.fetchone()
cursor.execute(
"""SELECT track_medicine.ws_qty, master_med_file.ws_med_name,master_med_file.ws_rate FROM `master_med_file` INNER JOIN `track_medicine` ON master_med_file.ws_med_id=track_medicine.ws_med_id WHERE track_medicine.ws_pat_id LIKE '{}'"""
.format(patientid))
med_detail = cursor.fetchall()
cursor.execute(
"""SELECT diag_master_file.ws_test_rate,diag_master_file.ws_test_name FROM `diag_master_file` INNER JOIN `track_diag` ON diag_master_file.ws_test_id=track_diag.ws_test_id WHERE track_diag.ws_pat_id LIKE '{}'"""
.format(patientid))
diag_detail = cursor.fetchall()
totalMed = 0
totalDiag = 0
for med in med_detail:
totalMed += (int(med[2]) * int(med[0]))
for diag in diag_detail:
totalDiag += (int(diag[0]))
#Splitting the address in to Street State and City
getCompleteAddress = patientData[4].__str__()
splittedAddress = getCompleteAddress.split(" , ", 2)
street = splittedAddress[0]
State = splittedAddress[1]
city = splittedAddress[2]
patient = Patient(patientData[0], patientData[2],
patientData[3], patientData[6], street,
State, city)
else:
flash("No patient found")
return redirect("/generate_bill")
form = ConfirmBill()
if form.validate_on_submit():
cursor.execute(
"UPDATE `patient` SET `ws_status`='{}' WHERE `ws_pat_id` LIKE '{}'"
"".format("Discharged", patientid))
conn.commit()
flash("Patient Discharged")
return redirect("/view_patient")
#calculate how many days patient stayed
doj = patientData[5].__str__()
doj = doj[0:10]
doj = doj.split("-", 2)
dojy = int(doj[0])
dojm = int(doj[1])
dojd = int(doj[2])
totalDays = datetime.today() - datetime(dojy, dojm, dojd)
totalDays = totalDays.__str__()
totalDays = totalDays[:2]
totalDays = int(totalDays)
totalRoomCost = getRoomPrice(patientData[6]) * int(totalDays)
grandTotal = totalMed + totalDiag + totalRoomCost
return render_template("patient_bill.html",
patientData=patientData,
totalDays=totalDays,
todaysDate=todaysDate,
totalRoomCost=totalRoomCost,
roomPrice=getRoomPrice(patientData[6]),
med_detail=med_detail,
diag_detail=diag_detail,
totalMed=totalMed,
totalDiag=totalDiag,
grandTotal=grandTotal,
form=form)
return redirect("/login")
def issue():
P_id = request.form.get('pt_id')
cursor.execute(
"""SELECT * FROM `patient` WHERE `ws_pat_id` LIKE '{}'""".format(P_id))
detail = cursor.fetchall()
med_name = request.form.get('name')
med_qty = int(request.form.get('quantity'))
cursor.execute(
"""SELECT `ws_rate` FROM `master_med_file` WHERE `ws_med_name` LIKE '{}'"""
.format(med_name))
rate = cursor.fetchall()
amt = med_qty * rate[0][0]
cursor.execute(
"""SELECT `ws_med_id` FROM `master_med_file` WHERE `ws_med_name` LIKE '{}'"""
.format(med_name))
mid = cursor.fetchall()
med_id = mid[0][0]
print(med_id)
####### reduce quantity from master_med_file #####
cursor.execute(
"""SELECT `ws_qty_av` FROM `master_med_file` WHERE `ws_med_name` LIKE '{}'"""
.format(med_name))
quant_to_update = cursor.fetchall()
new_quant = quant_to_update[0][0] - med_qty
if new_quant < 0:
msg = " medicine is not available"
else:
cursor.execute(
"""INSERT INTO `track_medicine` (`ws_pat_id`,`ws_med_id`,`ws_qty`) VALUES ('{}','{}','{}')"""
.format(P_id, med_id, med_qty))
conn.commit()
cursor.execute(
"""UPDATE `master_med_file` SET `ws_qty_av`='{}' WHERE `ws_med_name` LIKE '{}'"""
.format(new_quant, med_name))
conn.commit()
msg = None
print(new_quant)
####### patient medicines issued #######
cursor.execute(
"""SELECT `ws_med_id` FROM `track_medicine` WHERE `ws_pat_id` LIKE '{}'"""
.format(P_id))
all_med = cursor.fetchall()
cursor.execute(
"""SELECT `ws_qty` FROM `track_medicine` WHERE `ws_pat_id` LIKE '{}'"""
.format(P_id))
all_qty = cursor.fetchall()
medicine_name = []
medicine_rate = []
medicine_amount = []
store = []
for i in all_med:
cursor.execute(
"""SELECT `ws_med_name` FROM `master_med_file` WHERE `ws_med_id` LIKE '{}'"""
.format(i[0]))
nam = cursor.fetchall()
medicine_name.append(nam)
cursor.execute(
"""SELECT `ws_rate` FROM `master_med_file` WHERE `ws_med_id` LIKE '{}'"""
.format(i[0]))
medrate = cursor.fetchall()
medicine_rate.append(medrate)
for j in range(len(all_qty)):
quant = all_qty[j][0]
perrate = medicine_rate[j][0][0]
peramount = quant * perrate
store.append(int(j))
medicine_amount.append(peramount)
return render_template("issue_medicine.html",
details=detail,
name=med_name,
qty=med_qty,
rate=rate[0][0],
amt=amt,
pid=P_id,
ALLNAME=medicine_name,
ALLQTY=all_qty,
ALLRATE=medicine_rate,
ALLAMOUNT=medicine_amount,
LENGTH=store,
message=msg)
def show_zwyx_zwmc():
returnDate = {}
returnDate['status'] = 0
sql = 'select count(ca_list.Id) as count_zw,avg(ca_list.zwyx) as avg_zwyx,ca_zwmc.zwmc_name,max(zwyx),min(zwyx) from ca_list inner join ca_zwmc on ca_zwmc.Id=ca_list.zwmc_id group by ca_list.zwmc_id order by count_zw desc'
# 执行sql语句
cursor.execute(sql)
# 总职位数
count_zw = {}
# 平均薪资
avg_zw = {}
# 职位名
zwmc_list = []
# 最大薪资
max_xz = {}
# 最小薪资
min_xz = {}
zwmc_list.append('其他')
count_zw['其他'] = 0
avg_zw['其他'] = 0
max_xz['其他'] = 0
min_xz['其他'] = 0
zwmc_zwyx_list = cursor.fetchall()
# 打开职位分类文本
file = open('ca_list.txt', 'r')
lines = file.readline()
if zwmc_zwyx_list:
# 提取数据
for row in zwmc_zwyx_list:
# 提取文本数据
for item in lines.split():
# 判断是否包含
if item in row[2]:
# 如果判断职位列表里是否存在该项
if item in zwmc_list:
# 存在则处理数据
count_zw[item] = count_zw[item] + row[0]
avg_zw[item] = round((avg_zw[item] + row[1]) / 2, 2)
max_xz[item] = max(max_xz[item], row[3])
min_xz[item] = min(min_xz[item], row[4])
else:
# 不存在则添加数据
zwmc_list.append(item)
count_zw[item] = row[0]
avg_zw[item] = round(row[1], 2)
max_xz[item] = row[3]
min_xz[item] = row[4]
else:
# 如果没有找到对应的分类，则增加相应数据
count_zw['其他'] = count_zw['其他'] + row[0]
# 如果数据还没有对应数值，则增加第一条数据
if count_zw['其他'] == 0:
avg_zw['其他'] = row[1]
else:
avg_zw['其他'] = round((count_zw['其他'] + row[1]) / 2, 2)
max_xz['其他'] = max(max_xz['其他'], row[3])
min_xz['其他'] = min(min_xz['其他'], row[4])
# 重新构建数据
return_count_zw = []
return_avg_zw = []
return_max_xz = []
return_min_xz = []
# 重新规划数据
for value in count_zw:
return_count_zw.append(count_zw[value])
for value in avg_zw:
return_avg_zw.append(avg_zw[value])
for value in max_xz:
return_max_xz.append(max_xz[value])
for value in min_xz:
return_min_xz.append(min_xz[value])
# json数据
returnDate['status'] = 1
returnDate['count_zw'] = return_count_zw
returnDate['avg_zw'] = return_avg_zw
returnDate['zwmc_list'] = zwmc_list
returnDate['max_xz'] = return_max_xz
returnDate['min_xz'] = return_min_xz
return jsonify(returnDate)
