def show_all_physicians(self):
self.db.cursor.execute(
"SELECT ph.name, sp.name, ad.phone, date_of_birth, passport, r.name FROM physicians ph "
"LEFT JOIN specialization sp on sp.id = specialization_id "
"LEFT JOIN authentication_data ad on ad.id = ph.user_id "
"LEFT JOIN roles r on r.id = role_id")
filler.fillTable(self.ui.physician_tableWidget, self.db.cursor, 6)
def yearChanged(self):
self.ui.yearSubjectTableWidget.clear()
year = self.ui.yearComboBox.currentText()
self.db.cursor.execute(
"select s.name, avg_marks from subjectsAndAvgMarkInOneYear('{}') "
"left join subject s on s.id = subject_id".format(year))
filler.fillTable(self.ui.yearSubjectTableWidget, self.db.cursor, 2)
def create_finance_report(self):
self.ui.finance_info_label.setVisible(False)
self.ui.finance_tableWidget.clear()
self.ui.finance_tableWidget.setColumnCount(3)
#self.finance_tableWidget.setRowCount(0)
item = QtWidgets.QTableWidgetItem()
self.ui.finance_tableWidget.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.ui.finance_tableWidget.setHorizontalHeaderItem(1, item)
item = QtWidgets.QTableWidgetItem()
self.ui.finance_tableWidget.setHorizontalHeaderItem(2, item)
_translate = QtCore.QCoreApplication.translate
item = self.ui.finance_tableWidget.horizontalHeaderItem(0)
item.setText(_translate("Dialog", "ФИО пациента"))
item = self.ui.finance_tableWidget.horizontalHeaderItem(1)
item.setText(_translate("Dialog", "Услуга"))
item = self.ui.finance_tableWidget.horizontalHeaderItem(2)
item.setText(_translate("Dialog", "Стоимость"))
current_doc = str(self.ui.physicians_finance_comboBox.currentText())
days_interval = str(self.ui.finance_day_spinBox.value())
if len(current_doc) > 0:
self.db.cursor.execute("SELECT * FROM getfinanceReport(%s, %s)",
(current_doc, days_interval))
filler.fillTable(self.ui.finance_tableWidget, self.db.cursor, 3)
self.db.cursor.execute(
"SELECT sum(foo.cost) from (SELECT * FROM getfinanceReport(%s, %s)) as foo",
(current_doc, days_interval))
result = self.db.cursor.fetchone()
self.ui.total_label.setText(str(result[0]))
def show_all_med_card(self):
self.db.cursor.execute(
"SELECT date, p.name, ph.name, diagnosis, s.name from medical_records mr "
"LEFT JOIN services s on s.id = mr.service_id "
"LEFT JOIN patients p on mr.patient_id = p.id "
"LEFT JOIN physicians ph on mr.physician_id = ph.id")
filler.fillTable(self.ui.med_card_tableWidget, self.db.cursor, 5)
def show_all_changes_med_card(self):
self.db.cursor.execute(
"SELECT Ph.name, mra.time, mr.diagnosis, mr.date, mra.old_diagnosis from medical_record_audit mra "
"LEFT JOIN medical_records mr on mra.mr_id = mr.id "
"Left Join physicians ph on mr.physician_id = ph.id")
filler.fillTable(self.ui.med_record_audit_tableWidget, self.db.cursor,
5)
def show_all_med_records(self):
self.db.cursor.execute(
"SELECT date, P.name, diagnosis FROM medical_records "
"LEFT JOIN Patients P on P.id = patient_id "
"WHERE physician_id = (SELECT id FROM physicians WHERE user_id = '"
+ str(user_info.current_userID) + "') ORDER BY date desc")
filler.fillTable(self.ui.med_journal_tableWidget, self.db.cursor, 3)
def set_default_marks_table(self):
self.db.cursor.execute(
"SELECT st.name, s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id order by date desc"
)
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 5)
def form_queue_button_clicked(self):
days = str(self.ui.queue_days_box.value())
self.db.cursor.execute(
"SELECT P.name, Q.time FROM Queue Q LEFT JOIN Patients P on P.id = Q.patient_id "
"WHERE ((physician_id = %s) AND (time > current_timestamp) AND (time < current_timestamp + interval %s day))",
(user_info.current_userID, days))
filler.fillTable(self.ui.Queue_tableWidget, self.db.cursor, 2)
def show_changer_of_med_card(self):
name = str(self.ui.spec_who_change_med_card_comboBox.currentText())
self.db.cursor.execute(
"SELECT Ph.name, mra.time, mr.diagnosis, mr.date, mra.old_diagnosis from medical_record_audit mra "
"LEFT JOIN medical_records mr on mra.mr_id = mr.id "
"Left Join physicians ph on mr.physician_id = ph.id "
"WHERE Ph.name = '" + name + "'")
filler.fillTable(self.ui.med_record_audit_tableWidget, self.db.cursor,
5)
def physician_med_card_changed(self):
self.ui.patients_med_card_comboBox.setCurrentIndex(0)
name = str(self.ui.physician_med_card_comboBox.currentText())
self.db.cursor.execute(
"SELECT date, p.name, ph.name, diagnosis, s.name from medical_records mr "
"LEFT JOIN services s on s.id = mr.service_id "
"LEFT JOIN patients p on mr.patient_id = p.id "
"LEFT JOIN physicians ph on mr.physician_id = ph.id "
"Where ph.name = '" + name + "'")
filler.fillTable(self.ui.med_card_tableWidget, self.db.cursor, 5)
def avgMarksInIntervalClicked(self):
beginDate = self.ui.beginDateEdit.date()
endDate = self.ui.endDateEdit.date()
beginDate = beginDate.toString("yyyy-MM-dd")
endDate = endDate.toString("yyyy-MM-dd")
self.db.cursor.execute(
"select s.name, round(avg_marks, 3) from intervalAvgMarks('{}'::timestamp, '{}'::timestamp)"
"left join subject s on s.id = subject_id".format(
beginDate, endDate))
filler.fillTable(self.ui.yearSubjectTableWidget, self.db.cursor, 2)
def getPerfomanceClicked(self):
beginDate = self.ui.beginDateEdit_2.date()
endDate = self.ui.endDateEdit_2.date()
beginDate = beginDate.toString("yyyy-MM-dd")
endDate = endDate.toString("yyyy-MM-dd")
self.db.cursor.execute(
"select year, s.name, round(avg_mark, 3), round(diff_avg_mark, 3) from academic_progress('{}'::timestamp, '{}'::timestamp)"
" left join subject s on s.id = subject_id".format(
beginDate, endDate))
filler.fillTable(self.ui.cursorTableWidget, self.db.cursor, 4)
def queue_button_clicked(self):
days = str(self.ui.queue_day_spinBox.value())
self.db.cursor.execute(
"SELECT Q.time, Ph.name, P.name FROM Queue Q "
"LEFT JOIN Patients P on P.id = Q.patient_id "
"LEFT JOIN Physicians Ph on Ph.id = Q.physician_id "
"WHERE (time > current_timestamp) AND (time < current_timestamp + interval '"
+ days + "' day) "
"ORDER BY Q.time desc")
filler.fillTable(self.ui.queue_tableWidget, self.db.cursor, 3)
def patients_change(self):
print("Change patient")
self.ui.med_card_tableWidget.setRowCount(0)
current_patient = str(self.ui.patients_comboBox.currentText())
sqlcmd = (
"SELECT date, Ph.name, diagnosis FROM medical_records "
"LEFT JOIN Physicians Ph on Ph.id = physician_id "
"WHERE patient_id = (SELECT id FROM patients WHERE name = '" +
current_patient + "') ORDER BY date desc")
self.db.cursor.execute(sqlcmd)
filler.fillTable(self.ui.med_card_tableWidget, self.db.cursor, 3)
def show_by_spec_clicked(self):
spec = str(self.ui.choose_spec_comboBox.currentText())
if len(spec) > 0:
self.db.cursor.execute(
"SELECT ph.name, S.name, ad.phone, date_of_birth, passport, r.name FROM Physicians Ph "
"LEFT JOIN Specialization S ON Ph.specialization_id = S.id "
"LEFT JOIN roles r on r.id = role_id "
"LEFT JOIN authentication_data ad on ad.id = ph.user_id "
"WHERE S.name = '" + spec + "'")
filler.fillTable(self.ui.physician_tableWidget, self.db.cursor, 6)
else:
self.ui.physician_tableWidget.setRowCount(0)
def studentName_changed(self):
teacher_name = str(self.ui.teacherNameComboBox.currentText())
subject_name = str(self.ui.SubjectComboBox.currentText())
if self.ui.teacherNameComboBox.currentIndex() == 0 and self.ui.SubjectComboBox.currentIndex() == 0:
self.db.cursor.execute("SELECT s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id "
"where student_id = '{}'".format(self.student_id))
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 4)
elif self.ui.SubjectComboBox.currentIndex() == 0:
self.db.cursor.execute("SELECT s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id"
" where t.name = '{}' and student_id = '{}'".format(teacher_name, self.student_id))
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 4)
elif self.ui.teacherNameComboBox.currentIndex() == 0:
self.db.cursor.execute("SELECT s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id"
" where s.name = '{}' and student_id = '{}'".format(subject_name, self.student_id))
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 4)
else:
self.db.cursor.execute("SELECT s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id"
" where t.name = '{}' and s.name = '{}' and student_id = '{}'".format(teacher_name, subject_name, self.student_id))
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 4)
def show_lazy_doctors(self):
hours = str(self.ui.hours_spinBox.value())
days = str(self.ui.days_spinBox.value())
if hours.isdecimal() and days.isdecimal():
self.db.cursor.execute(
"SELECT ph.name, S.name, ad.phone, date_of_birth, passport, r.name FROM Physicians Ph "
"LEFT JOIN Specialization S ON Ph.specialization_id = S.id "
"LEFT JOIN roles r on r.id = role_id "
"LEFT JOIN authentication_data ad on ad.id = ph.user_id "
"RIGHT JOIN (SELECT * FROM printListOfLazyDoctors(%s, %s)) as foo on foo.name = ph.name",
(hours, days))
filler.fillTable(self.ui.physician_tableWidget, self.db.cursor, 6)
def setInitialValues(self):
teacher_id = user_info.current_userID
self.db.cursor.execute("SELECT name From people where user_id= '" +
str(teacher_id) + "'")
teacher_name_list = self.db.cursor.fetchone()
self.ui.name_label.setText(str(teacher_name_list[0]))
# self.db.cursor.execute("SELECT name from groups")
# filler.fillComboBox(self.ui.groupComboBox, self.db.cursor)
self.db.cursor.execute(
"SELECT name From people where type = 'S' order by name")
filler.fillComboBox(self.ui.studentNameComboBox, self.db.cursor)
self.db.cursor.execute(
"SELECT name From people where type = 'P' order by name")
filler.fillComboBox(self.ui.teacherNameComboBox, self.db.cursor)
self.db.cursor.execute(
"SELECT G.name, round(avg(COALESCE(value, 0)), 2) FROM people p "
"FULL JOIN marks m on p.id = m.student_id "
"RIGHT JOIN GROUPS G ON G.ID = P.GROUP_ID "
"GROUP BY G.id ;")
filler.fillTable(self.ui.groupTableWidget, self.db.cursor, 2)
filler.fillMultipleComboBox([
self.ui.subjectNameComboBox, self.ui.SubjectComboBox,
self.ui.subjectComboBox1, self.ui.subjectComboBox2
], self.db.cursor, 'SELECT name from subject order by name')
self.set_default_marks_table()
self.ui.typeComboBox.clear()
self.ui.typeComboBox.addItem("Student")
self.ui.typeComboBox.addItem("Teacher")
self.db.cursor.execute("SELECT p.name, s.name FROM MARKS M "
"FULL JOIN PEOPLE P ON P.ID = M.STUDENT_ID "
"LEFT JOIN GROUPS G ON G.ID = P.GROUP_ID "
"FULL Join subject s on M.subject_id = s.id")
filler.fillTable(self.ui.studentSubjectTableWidget, self.db.cursor, 2)
self.db.cursor.execute("select * from avgMarkByTeacher")
filler.fillTable(self.ui.avgMarkByTeacherTableWidget, self.db.cursor,
3)
self.db.cursor.execute("select * from avgMarkByYears")
filler.fillTable(self.ui.yearMarksTableWidget, self.db.cursor, 2)
self.ui.yearComboBox.clear()
self.ui.yearComboBox.addItem("")
for i in range(2015, datetime.date.today().year):
self.ui.yearComboBox.addItem(str(i))
filler.fillMultipleComboBox([
self.ui.groupComboBox, self.ui.groupNumComboBox,
self.ui.groupPointerComboBox2, self.ui.groupPointerComboBox_2,
self.ui.groupComboBox1
], self.db.cursor, 'SELECT name from groups')
def initialValues(self):
self.db.cursor.execute(
"SELECT name, date_of_birth, ad.phone, passport, snils, insurance_policy, contract_num "
"FROM patients "
"LEFT JOIN authentication_data ad on ad.id = user_id")
filler.fillTable(self.ui.patients_tableWidget, self.db.cursor, 7)
self.queue_button_clicked()
self.show_all_physicians()
self.ui.finance_info_label.setVisible(False)
self.db.cursor.execute("SELECT name from specialization")
filler.fillComboBox(self.ui.choose_spec_comboBox, self.db.cursor)
self.db.cursor.execute("SELECT name from specialization")
filler.fillComboBox(self.ui.spec_comboBox, self.db.cursor)
self.db.cursor.execute("SELECT name from physicians")
filler.fillComboBox(self.ui.physicians_finance_comboBox,
self.db.cursor)
self.show_all_med_card()
self.db.cursor.execute(
"SELECT Distinct Ph.name from medical_record_audit "
"LEFT JOIN medical_records mr on medical_record_audit.mr_id = mr.id "
"Left Join physicians ph on mr.physician_id = ph.id")
filler.fillComboBox(self.ui.spec_who_change_med_card_comboBox,
self.db.cursor)
self.db.cursor.execute(
"SELECT Ph.name from medical_records mr "
"Left Join physicians ph on mr.physician_id = ph.id")
filler.fillComboBox(self.ui.physician_med_card_comboBox,
self.db.cursor)
self.db.cursor.execute("SELECT P.name from medical_records mr "
"Left Join patients p on mr.patient_id = p.id")
filler.fillComboBox(self.ui.patients_med_card_comboBox, self.db.cursor)
self.show_all_changes_med_card()
self.db.cursor.execute("Select name from specialization")
filler.fillComboBox(self.ui.specialization_comboBox, self.db.cursor)
self.show_services()
self.show_specializations()
def setInitialValues(self):
user_id = user_info.current_userID
self.db.cursor.execute("SELECT name From people where user_id= '" + str(user_id) + "'")
student_name_list = self.db.cursor.fetchone()
self.ui.name_label.setText(str(student_name_list[0]))
# self.db.cursor.execute("SELECT name from groups")
# filler.fillComboBox(self.ui.groupComboBox, self.db.cursor)
self.db.cursor.execute("SELECT name From people where type = 'P' ")
filler.fillComboBox(self.ui.teacherNameComboBox, self.db.cursor)
filler.fillMultipleComboBox([self.ui.SubjectComboBox], self.db.cursor, 'SELECT name from subject')
self.db.cursor.execute("SELECT s.name, t.name, value, date from marks "
"left join people st on MARKS.STUDENT_ID = st.ID"
" left join people t on MARKS.TEACHER_ID = t.id "
"left join subject s on MARKS.SUBJECT_ID = s.id where student_id = {}".format(self.student_id))
filler.fillTable(self.ui.marksTableWidget, self.db.cursor, 4)
def setInitialValues(self):
self.db.cursor.execute("SELECT name from patients where user_id=" + str(user_info.current_userID))
pat_name = self.db.cursor.fetchone()
self.ui.name_label.setText(pat_name[0])
self.db.cursor.execute("SELECT passport from patients where user_id=" + str(user_info.current_userID))
pas_name = self.db.cursor.fetchone()
self.ui.passport_label.setText(str(pas_name[0]))
self.db.cursor.execute("SELECT date_of_birth from patients where user_id=" + str(user_info.current_userID))
date_of_birth = self.db.cursor.fetchone()
self.ui.date_of_birth_label.setText(str(date_of_birth[0]))
self.db.cursor.execute("SELECT phone from authentication_data WHERE id= '" + str(user_info.current_userID) + "'")
phone = self.db.cursor.fetchone()
self.ui.phone_number_box.setText(str(phone[0]))
self.db.cursor.execute("SELECT insurance_policy from patients where user_id=" + str(user_info.current_userID))
policy = self.db.cursor.fetchone()
self.ui.insurance_policy_label.setText(str(policy[0]))
self.db.cursor.execute("SELECT snils from patients where user_id=" + str(user_info.current_userID))
snils = self.db.cursor.fetchone()
self.ui.snils_label.setText(str(snils[0]))
self.db.cursor.execute("SELECT name from specialization")
filler.fillComboBox(self.ui.specialization_comboBox, self.db.cursor)
self.queue_change()
self.db.cursor.execute("SELECT Mr.date, Ph.name, Mr.diagnosis, S.name as Service FROM Medical_records Mr "
"LEFT JOIN Physicians Ph on Ph.id = Mr.physician_id "
"LEFT JOIN Services S on S.id = Mr.service_id "
"WHERE Mr.patient_id = %s ORDER BY date desc" %str(patient_id[0]))
filler.fillTable(self.ui.med_record_tableWIdget, self.db.cursor, 4)
self.db.cursor.execute("SELECT SUM(cost) FROM (SELECT S.cost FROM Medical_records Mr "
"LEFT JOIN Services S on S.id = Mr.service_id "
"WHERE Mr.patient_id = %s and Mr.date > (select cast(date_trunc('month', current_date) as date)) "
"ORDER BY date desc) as foo" %str(patient_id[0]))
cost = self.db.cursor.fetchone()
self.ui.cost_label.setText(str(cost[0]))
def finrep_for_all_physician(self):
self.ui.finance_info_label.setVisible(True)
self.ui.finance_tableWidget.clear()
self.ui.finance_tableWidget.setColumnCount(2)
item = QtWidgets.QTableWidgetItem()
self.ui.finance_tableWidget.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.ui.finance_tableWidget.setHorizontalHeaderItem(1, item)
_translate = QtCore.QCoreApplication.translate
item = self.ui.finance_tableWidget.horizontalHeaderItem(0)
item.setText(_translate("Dialog", "ФИО врача"))
item = self.ui.finance_tableWidget.horizontalHeaderItem(1)
item.setText(_translate("Dialog", "Заработал"))
self.db.cursor.execute(
"SELECT Ph.name, cost From finance_report fr Left Join physicians Ph on Ph.id = fr.physician_id"
)
filler.fillTable(self.ui.finance_tableWidget, self.db.cursor, 2)
def show_specializations(self):
self.db.cursor.execute("SELECT name from specialization")
filler.fillTable(self.ui.specialization_tableWidget, self.db.cursor, 1)
def show_services(self):
self.db.cursor.execute(
"SELECT s.name, cost, sp.name from Services S "
"left join specialization sp on sp.id = S.specialization_id")
filler.fillTable(self.ui.service_tableWidget, self.db.cursor, 3)
def groupComboBox_changed(self):
num_group = str(self.ui.groupComboBox.currentText())
self.db.cursor.execute(
"SELECT name from people where group_id = (SELECT id from "
"groups where name = '{}')".format(num_group))
filler.fillTable(self.ui.studentTableWidget, self.db.cursor, 1)
def queue_change(self) :
self.db.cursor.execute("SELECT Q.time, P.name FROM queue Q "
"LEFT JOIN Physicians P on P.id = Q.physician_id "
"WHERE ((time >= current_timestamp) AND (patient_id = '" + str(patient_id[0]) + "'))")
filler.fillTable(self.ui.Queue_tableWidget, self.db.cursor, 2)