def insertAndUpdateMusic(filename, file_type):
session = SessionClass()
music = session.query(Music).filter(Music.music_name == filename).first()
if music == None:
cur_max_human_no_music = session.query(Music).filter(Music.music_group == file_type).order_by(Music.music_human_no.desc()).first()
music_human_no = str(int(cur_max_human_no_music.music_human_no) + 1).zfill(3)
music_new = Music()
music_new.music_name = filename
music_new.music_human_no = music_human_no
music_new.music_group = file_type
music_new.music_human_no_and_group = music_human_no + file_type
music_new.music_insert_time = getTimeStamp()
try:
session.add(music_new)
session.flush()
music_human_no_and_group = music_new.music_human_no_and_group
session.commit()
session.close()
return music_human_no_and_group
except:
session.rollback()
return None
else:
music_human_no_and_group = music.music_human_no_and_group
session.commit()
session.close()
return music_human_no_and_group
def updatePerPatient(patient_info, device_mac):
session = SessionClass()
device = session.query(Device).filter(Device.device_mac == device_mac).first()
patient = session.query(Patient).filter(Patient.patient_name==patient_info['patient_name'], Patient.patient_gender==patient_info['patient_gender'],
Patient.patient_age==patient_info['patient_age'], Patient.device_id==device.device_id).first()
try:
patient.patient_doctor_category1 = patient_info['patient_doctor_category1']
patient.patient_doctor_category2 = patient_info['patient_doctor_category2']
if patient_info['patient_self_reported']=='null':
patient.patient_self_reported = None
else:
patient.patient_self_reported = patient_info['patient_self_reported']
if patient_info['patient_examination'] == 'null':
patient.patient_examination = None
else:
patient.patient_examination = patient_info['patient_examination']
if patient_info['patient_medical_history'] == 'null':
patient.patient_medical_history =None
else:
patient.patient_medical_history = patient_info['patient_medical_history']
session.commit()
session.close()
return 1
except:
session.close()
return 0
def getDoctorIDDedulicate(device_mac):
session = SessionClass()
device = session.query(Device).filter(
Device.device_mac == device_mac).first()
doctor_id_value = session.query(Doctor).filter(
Doctor.device_id == device.device_id).with_entities(
Doctor.doctor_id).all()
value_list = []
for index in range(len(doctor_id_value)):
value_list.append(doctor_id_value[index][0])
session.close()
return value_list
def insertTreatments(treatments,patient_id):
session = SessionClass()
if (treatments != None):
treatment_count = session.query(Treatment).filter(Treatment.patient_id == patient_id).count()
if (treatment_count <= 1000):
for treatment in treatments:
music = session.query(Music).filter(Music.music_human_no_and_group == treatment['musicNum']).first()
session.add(Treatment(treatment_time=treatment['ts'],patient_id=patient_id,music_id=music.music_id))
#session.add(Treatment(treatment_time=treatment['ts'], patient_id=patient_id))
session.commit()
session.close()
return
def allPatientToExcel(device_mac):
patients_id = getPatientIDDeduplicate(device_mac)
session = SessionClass()
patient_col_name = [
'编号', '姓名', '性别', '年龄', '主诉', '既往史', '检查', '设备分型', '医生第一分型', '医生第二分型',
'医生姓名'
]
treatment_col_name = ['编号', '治疗时间', '患者编号', '音乐编号']
grade_col_name = ['编号', '等级', '分数', '评分时间', '患者编号']
now_time = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
filename = 'allPatients_' + now_time + '.xlsx'
workbook_filepath = 'E:/NewMyGitProjects/FiveNotesSqlalchemy/emailData/' + filename
with xlsxwriter.Workbook(workbook_filepath) as workbook:
worksheet_patient = workbook.add_worksheet('患者信息') # 创建患者信息sheet表
worksheet_treatment = workbook.add_worksheet('治疗信息') # 创建治疗信息sheet表
worksheet_grade = workbook.add_worksheet('评分信息') # 创建评分信息sheet表
writeRowExcel(0, patient_col_name, worksheet_patient)
writeRowExcel(0, treatment_col_name, worksheet_treatment)
writeRowExcel(0, grade_col_name, worksheet_grade)
len_treatment = 0
len_grade = 0
for index in range(len(patients_id)):
#从数据库获取单个患者数据
patient = session.query(Patient).filter(
Patient.patient_id == patients_id[index]).first()
doctor = session.query(Doctor).filter(
Doctor.doctor_id == patient.doctor_id).first()
treatments = session.query(Treatment).filter(
Treatment.patient_id == patients_id[index]).all()
grades = session.query(Grade).filter(
Grade.patient_id == patients_id[index]).all()
#填充患者信息表
patient_info = patientInfo2List(patient, doctor)
writeRowExcel(index + 1, patient_info, worksheet_patient)
# 填充治疗信息表
for i in range(len(treatments)):
treatment_info = treatmentInfo2List(treatments[i])
writeRowExcel(len_treatment + i + 1, treatment_info,
worksheet_treatment)
len_treatment += len(treatments)
# 填充评分信息表
for i in range(len(grades)):
grade_info = gradeInfo2List(grades[i])
writeRowExcel(len_grade + i + 1, grade_info, worksheet_grade)
len_grade += len(grades)
session.close()
return filename, workbook_filepath
def insertPatient(patientInfo,device_mac):
session = SessionClass()
device = session.query(Device).filter(Device.device_mac == device_mac).first()
patient = session.query(Patient).filter(Patient.patient_age == patientInfo['age'],
Patient.patient_gender == patientInfo['gender'],
Patient.patient_name == patientInfo['name'],
Patient.device_id == device.device_id).first()
doctor_id = insertDoctor(patientInfo['doctor'], device_mac)
if doctor_id != None:
if patient==None:
patient_new=Patient()
patient_new.patient_name=patientInfo['name']
patient_new.patient_gender = patientInfo['gender']
patient_new.patient_age = patientInfo['age']
patient_new.patient_self_reported = patientInfo.get('report', None)
patient_new.patient_medical_history = patientInfo.get('history', None)
patient_new.patient_examination = patientInfo.get('inspect', None)
patient_new.patient_device_category = patientInfo['cate']
#patient_new.patient_doctor_category1 = patientInfo['dcate1']
patient_new.patient_doctor_category1 = patientInfo['cate']
patient_new.patient_doctor_category2 = patientInfo.get('dcate2',None)
patient_new.device_id=device.device_id
patient_new.doctor_id=doctor_id
try:
session.add(patient_new)
session.flush()
patient_new_id=patient_new.patient_id
session.commit()
session.close()
return patient_new_id
except:
session.rollback()
return None
else:
if 'report' in patientInfo:
patient.patient_self_reported = patientInfo['report']
if 'history' in patientInfo:
patient.patient_medical_history = patientInfo['history']
if 'inspect' in patientInfo:
patient.patient_examination = patientInfo['inspect']
if 'dcate1' in patientInfo:
patient.patient_examination = patientInfo['dcate1']
if 'dcate2' in patientInfo:
patient.patient_examination = patientInfo['dcate2']
patient_id=patient.patient_id
session.commit()
session.close()
return patient_id
else:
session.close()
return None
def getPerDoctorPatientNumber(device_mac):
session = SessionClass()
doctors_id = getDoctorIDDedulicate(device_mac)
perdoctor_nums = []
for doctor_id in doctors_id:
counts = {}
doctor = session.query(Doctor).filter(
Doctor.doctor_id == doctor_id).first()
patient_count = session.query(Patient).filter(
Patient.doctor_id == doctor_id).count()
counts['name'] = doctor.doctor_name
counts['num'] = patient_count
perdoctor_nums.append(counts)
session.close()
return perdoctor_nums
def getTypePatientProportion(device_mac):
session = SessionClass()
patients_id = getPatientIDDeduplicate(device_mac)
types_num = [0, 0, 0, 0, 0]
types_name = ['风热侵袭', '肝火上扰', '痰火郁结', '肾精亏损', '脾胃虚弱']
type_percent = []
for patient_id in patients_id:
patient = session.query(Patient).filter(
Patient.patient_id == patient_id).first()
if patient.patient_doctor_category1 == 1:
types_num[0] += 1
elif patient.patient_doctor_category1 == 2:
types_num[1] += 1
elif patient.patient_doctor_category1 == 3:
types_num[2] += 1
elif patient.patient_doctor_category1 == 4:
types_num[3] += 1
elif patient.patient_doctor_category1 == 5:
types_num[4] += 1
all_nums = sum(types_num)
for i in range(len(types_num)):
counts = {}
counts['name'] = types_name[i]
counts['percent'] = round((types_num[i] / all_nums), 2)
counts['a'] = '1'
type_percent.append(counts)
session.close()
return type_percent
def getCertainMusic():
session = SessionClass()
musics = session.query(Music).filter(Music.music_insert_time != 0).all()
#musics = session.query(Music).all()
a = []
for music in musics:
a.append(music.music_human_no_and_group + '.' + 'mp3')
return a
def singlePatientToExcel(patient_id):
session = SessionClass()
patient_col_name = [
'编号', '姓名', '性别', '年龄', '主诉', '既往史', '检查', '设备分型', '医生第一分型', '医生第二分型',
'医生姓名'
]
treatment_col_name = ['编号', '治疗时间', '患者编号']
grade_col_name = ['编号', '等级', '分数', '评分时间', '患者编号']
now_time = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
filename = 'singlePatient_' + now_time + '.xlsx'
workbook_filepath = 'E:/NewMyGitProjects/FiveNotesSqlalchemy/emailData/' + filename
with xlsxwriter.Workbook(workbook_filepath) as workbook:
worksheet_patient = workbook.add_worksheet('患者信息')
writeRowExcel(0, patient_col_name, worksheet_patient)
worksheet_treatment = workbook.add_worksheet('治疗信息')
writeRowExcel(0, treatment_col_name, worksheet_treatment)
worksheet_grade = workbook.add_worksheet('评分信息')
writeRowExcel(0, grade_col_name, worksheet_grade)
patient = session.query(Patient).filter(
Patient.patient_id == patient_id).first()
doctor = session.query(Doctor).filter(
Doctor.doctor_id == patient.doctor_id).first()
treatments = session.query(Treatment).filter(
Treatment.patient_id == patient_id).all()
grades = session.query(Grade).filter(
Grade.patient_id == patient_id).all()
#患者信息sheet表
patient_info = patientInfo2List(patient, doctor)
writeRowExcel(1, patient_info, worksheet_patient)
#治疗信息sheet表
for i in range(len(treatments)):
treatment_info = treatmentInfo2List(treatments[i])
writeRowExcel(i + 1, treatment_info, worksheet_treatment)
# 评分信息sheet表
for i in range(len(grades)):
grade_info = gradeInfo2List(grades[i])
writeRowExcel(i + 1, grade_info, worksheet_grade)
session.close()
return filename, workbook_filepath
def getSinglePatirntGradeLevelChange(patient_id):
session = SessionClass()
grade_latest = session.query(Grade).order_by(
Grade.grade_time.desc()).filter(
Grade.patient_id == patient_id).first()
grade_oldest = session.query(Grade).order_by(
Grade.grade_time).filter(Grade.patient_id == patient_id).first()
if grade_latest != None:
difference = grade_oldest.grade_level - grade_latest.grade_level
session.close()
if grade_latest.grade_score == 0: # 痊愈
return 0
elif difference >= 2: # 显效
return 1
elif difference == 1: # 有效
return 2
else:
return 3 # 无效
def getPatientIDDeduplicate(device_mac, *args):
session = SessionClass()
device = session.query(Device).filter(
Device.device_mac == device_mac).first()
if len(args) == 0:
patient_id_value = session.query(Patient).filter(
Patient.device_id == device.device_id).with_entities(
Patient.patient_id).all()
else:
patient_id_value = session.query(Patient).filter(
Patient.device_id == device.device_id,
Patient.patient_name == args[0]).with_entities(
Patient.patient_id).all()
value_list = []
for index in range(len(patient_id_value)):
value_list.append(patient_id_value[index][0])
session.close()
return value_list
def getDevice(device_mac):
session = SessionClass()
device = session.query(Device).filter(
Device.device_mac == device_mac).first()
session.close()
if device == None:
return None
else:
return device
def treatmentInfo2List(treatment):
session = SessionClass()
music = session.query(Music).filter(
Music.music_id == treatment.music_id).first()
treatment_info = []
treatment_info.append(treatment.treatment_id)
treatment_info.append(timeStampToYMD(treatment.treatment_time))
treatment_info.append(treatment.patient_id)
treatment_info.append(music.music_human_no_and_group)
return treatment_info
def insertGrades(grades,patient_id):
session = SessionClass()
if (grades != None):
grade_count = session.query(Grade).order_by(Grade.grade_time.desc()).filter(Grade.patient_id == patient_id).count()
if (grade_count <= 1000):
for grade in grades:
session.add(Grade(grade_level=grade['grade'], grade_score=grade['score'], grade_time=grade['ts'],
patient_id=patient_id))
session.commit()
session.close()
return
def getPerMusicNumber():
session = SessionClass()
permusic_nums = []
music_types_name = ['宫', '商', '角', '徵', '羽', '阿是乐']
for i in range(6):
count = {}
count['name'] = music_types_name[i]
count['num'] = session.query(Music).filter(
Music.music_group == str(i + 1)).count()
permusic_nums.append(count)
session.close()
return permusic_nums
def getAllMusicInfo():
session = SessionClass()
musics_info = []
musics = session.query(Music).all()
for music in musics:
per_music_info = {}
per_music_info['musicName'] = music.music_name
per_music_info['musicId'] = music.music_human_no_and_group
per_music_info['musicType'] = music.music_group
#per_music_info['timestamp'] = music.music_insert_time
musics_info.append(per_music_info)
session.close()
return musics_info
def insertDoctor(doctor_name,device_mac):
session = SessionClass()
device = session.query(Device).filter(Device.device_mac == device_mac).first()
doctor = session.query(Doctor).filter(Doctor.device_id == device.device_id,Doctor.doctor_name==doctor_name).first()
if doctor==None:
doctor_new=Doctor()
doctor_new.doctor_name=doctor_name
doctor_new.device_id=device.device_id
try:
session.add(doctor_new)
session.flush()
doctor_new_id=doctor_new.doctor_id
session.commit()
session.close()
#device=session.query(Doctor).filter(Doctor.doctor_id==doctor_new_id).first()
return doctor_new_id
except:
session.rollback()
return None
else:
session.close()
return doctor.doctor_id
def getSinglePatientInfo(patient_id):
session = SessionClass()
patient = session.query(Patient).filter(
Patient.patient_id == patient_id).first()
treatment_count = session.query(Treatment).filter(
Treatment.patient_id == patient_id).count()
grade = session.query(Grade).order_by(Grade.grade_time.desc()).filter(
Grade.patient_id == patient_id).first()
# 处理None情况
if (grade == None):
grade_level = ''
grade_score = ''
grade_time = 0
else:
grade_level = grade.grade_level
grade_score = grade.grade_score
grade_time = grade.grade_time
#处理None情况结束
per_patient = {
'patient_id': patient.patient_id,
'patient_name': patient.patient_name,
'patient_gender': patient.patient_gender,
'patient_age': patient.patient_age,
'patient_self_reproted': patient.patient_self_reported,
'patient_medical_history': patient.patient_medical_history,
'patient_examination': patient.patient_examination,
'patient_doctor_category1': patient.patient_doctor_category1,
'patient_doctor_category2': patient.patient_doctor_category2,
'doctor': patient.doctor.doctor_name,
'treatment_count': treatment_count,
'grade_level': grade_level,
'grade_score': grade_score,
'grade_time': grade_time,
'device_id': patient.device_id
}
session.close()
return per_patient
def getTreatmentPatientNumber(device_mac):
session = SessionClass()
patients_id = getPatientIDDeduplicate(device_mac)
timestamps_string = []
date_nums = []
for patient_id in patients_id:
treatments = session.query(Treatment).filter(
Treatment.patient_id == patient_id).all()
for treatment in treatments:
timestamps_string.append(timeStampToYMD(treatment.treatment_time))
for i in set(timestamps_string):
counts = {}
counts['date'] = i
counts['nums'] = timestamps_string.count(i)
date_nums.append(counts)
session.close()
return date_nums
def insertDevice(deviceInfo):
session = SessionClass()
device=session.query(Device).filter(Device.device_mac==deviceInfo['device_mac']).first()
if device == None:
device_new = Device()
device_new.device_mac = deviceInfo['device_mac']
device_new.device_img = deviceInfo.get('device_img', None)
device_new.device_name = deviceInfo.get('device_name', None)
device_new.device_department = deviceInfo.get('device_department', None)
try:
session.add(device_new)
session.flush()
device_new_id = device_new.device_id
session.commit()
session.close()
return device_new_id
except:
session.rollback()
return None
else:
session.close()
return device.device_id
def getGenderPatientProportion(device_mac):
session = SessionClass()
patients_id = getPatientIDDeduplicate(device_mac)
gender_num = [0, 0]
gender_name = ['男', '女']
type_percent = []
for patient_id in patients_id:
patient = session.query(Patient).filter(
Patient.patient_id == patient_id).first()
if patient.patient_gender == 1: #1为男，2为女
gender_num[0] += 1
elif patient.patient_gender == 2:
gender_num[1] += 1
all_nums = sum(gender_num)
for i in range(len(gender_num)):
counts = {}
counts['name'] = gender_name[i]
counts['percent'] = round((gender_num[i] / all_nums), 2)
counts['a'] = '1'
type_percent.append(counts)
session.close()
return type_percent
def getAgePatientProportion(device_mac):
session = SessionClass()
patients_id = getPatientIDDeduplicate(device_mac)
age_stages = [0, 0, 0, 0]
age_stages_name = ['18岁以下', '18岁-44岁', '44岁-60岁', '60岁以上']
age_nums = []
for patient_id in patients_id:
patient = session.query(Patient).filter(
Patient.patient_id == patient_id).first()
if patient.patient_age <= 18:
age_stages[0] += 1
elif (patient.patient_age > 18) and (patient.patient_age <= 44):
age_stages[1] += 1
elif (patient.patient_age > 44) and (patient.patient_age <= 60):
age_stages[2] += 1
elif patient.patient_age > 60:
age_stages[3] += 1
for i in range(len(age_stages)):
per_count = {}
per_count['name'] = age_stages_name[i]
per_count['percent'] = round((age_stages[i] / sum(age_stages)), 2)
per_count['a'] = '1'
age_nums.append(per_count)
return age_nums