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)