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)