# -*- coding: UTF-8 -*-
#!/usr/bin/env python
#from PyQt4 import QtGui, QtCore, QtSql
from PyQt4.QtSql import *
from PyQt4.QtGui import *
from PyQt4.QtCore import *
#from PyQt4 import Qt
import sys
class Main_Window(QMainWindow):
def __init__(self, window):
self.setup_ui(window)
self.connect_db()
self.populate_tab1()
self.populate_tab2()
self.populate_tab3()
self.populate_tab4()
self.db = QSqlDatabase
self.connect_slots()
def show_error(self, title, text):
QMessageBox.critical(self.mw, title, text)
def show_message(self, title, text):
QMessageBox.information(self.mw, title, text)
def connect_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('db.sql')
def connect_slots(self):
QObject.connect(self.button_t2, SIGNAL("clicked()"), self.add_faculty)
QObject.connect(self.button_t3, SIGNAL("clicked()"), self.add_dep)
QObject.connect(self.button_t4, SIGNAL("clicked()"), self.add_human)
QObject.connect(self.save_t2, SIGNAL("clicked()"), self.commit_t2)
#QObject.connect(self.save_t3, SIGNAL("clicked()"), self.commit_t3)
QObject.connect(self.delete_t2, SIGNAL("clicked()"), self.delete_row_t2)
QObject.connect(self.delete_t3, SIGNAL("clicked()"), self.delete_row_t3)
QObject.connect(self.delete_t4, SIGNAL("clicked()"), self.delete_row_t4)
QObject.connect(self.list_t2, SIGNAL("cellClicked(int,int)"),
self.cell_clicked)
QObject.connect(self.model_t2, SIGNAL("beforeUpdate()"),
self.update_tables)
QObject.connect(self.combo1_t4, SIGNAL("currentIndexChanged(int)"),
self.tab4_select_dep)
def tab4_select_dep(self, index):
faculty = self.model2_t4.record(index).value(1).toString()
self.model3_t4.setQuery("""SELECT departments.name, departments.id
FROM facdeps, departments
WHERE faculty="""+faculty+" and departments.id = facdeps.department")
#print 123
def cell_clicked(self, x, y):
print x, y
def commit_t2(self):
self.model_t2.submitAll()
self.model_t1.query().exec_()
self.table.resizeColumnsToContents()
#def commit_t3(self):
# for i in range(self.model_t3.rowCount()):
# r = self.model_t3.record(i)
# print r.value(2).toString()
def delete_row_t2(self):
for index in self.list_t2.selectedIndexes():
fid = str(self.model_t2.record(index.row()).value(0).toString())
q = QSqlQuery('SELECT * from facdeps where faculty = ?')
q.addBindValue(QVariant(fid))
q.exec_()
if q.next():
self.show_error(u'Помилка',
u'Неможливо видалити факультет, у якого є кафедри.')
else:
self.model_t2.removeRow(index.row())
self.model_t2.submitAll()
self.model2_t3.setQuery('')
self.model2_t3.setQuery('select name, id from faculties')
def delete_row_t3(self):
for index in self.table_t3.selectedIndexes()[::2]:
name = self.model_t3.record(index.row()).value(1).toString()
id = QSqlQuery('SELECT id from departments where name = ?')
id.addBindValue(QVariant(name))
id.exec_()
id.next()
#print i
id = int(str(id.value(0).toString()))
print id
checkq = QSqlQuery('SELECT * from work where department = ?')
checkq.addBindValue(QVariant(id))
checkq.exec_()
if checkq.next():
self.show_error(u'Помилка',
u'Неможливо видалити: на цiй кафедрi працюють люди')
return 0
query = QSqlQuery('DELETE from departments where id = ?')
query.addBindValue(QVariant(id))
query2 = QSqlQuery('DELETE from facdeps where department = ?')
query2.addBindValue(QVariant(id))
query.exec_()
query2.exec_()
#query.addBindValue(QVariant())
#self.model_t3.removeRow(index.row())
#for index in self.list_t2.selectedIndexes():
# self.model_t2.removeRow(index.row())
#print index.row()
self.model_t3.submitAll()
self.model_t3.select()
def delete_row_t4(self):
for index in self.table_t4.selectedIndexes()[::2]:
hid = self.model_t4.record(index.row()).value(3).toString()
q1 = QSqlQuery('DELETE from work where human =?')
q2 = QSqlQuery('DELETE from people where id =?')
q1.addBindValue(QVariant(hid))
q2.addBindValue(QVariant(hid))
q1.exec_()
q2.exec_()
self.model_t1.setQuery(QSqlQuery(""))
self.model_t1.setQuery(QSqlQuery(self.q_t1))
self.model_t4.setQuery("")
self.model_t4.setQuery(self.q_t4)
self.table_t4.resizeColumnsToContents()
self.table_t4.hideColumn(3)
def update_tables(self):
print "UPDATING"
self.model_t1.select()
def populate_tab1(self):
self.model_t1 = QSqlTableModel()
#self.model.setTable('work')
self.q_t1 = """SELECT people.name, departments.name, faculties.name
FROM work, people, departments, faculties, facdeps
WHERE people.id=work.human
and departments.id = work.department
and faculties.id=facdeps.faculty
and departments.id=facdeps.department"""
q = QSqlQuery(self.q_t1)
self.model_t1.setQuery(q)
#self.model.select()
self.model_t1.setHeaderData(0, Qt.Horizontal, QVariant(u'Им\'я'))
self.model_t1.setHeaderData(1, Qt.Horizontal, QVariant(u'Кафедра'))
self.model_t1.setHeaderData(2, Qt.Horizontal, QVariant(u'Факультет'))
self.table.setModel(self.model_t1)
#self.table.setColumnWidth(0,200)
#self.table.setColumnWidth(1,200)
#self.table.setColumnWidth(2,200)
self.table.show()
self.table.resizeColumnsToContents()
def populate_tab2(self):
self.model_t2 = QSqlTableModel()
self.model_t2.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model_t2.setTable('faculties')
#self.model_t2.setQuery(QSqlQuery('select name from faculties'))
self.list_t2.setModel(self.model_t2)
self.list_t2.setModelColumn(1)
self.model_t2.select()
self.list_t2.show()
self.model_t2.setHeaderData(0, Qt.Horizontal,
QVariant(u'ID'))
self.model_t2.setHeaderData(1, Qt.Horizontal,
QVariant(u'Назва факультету'))
#self.list_t2.
def populate_tab3(self):
self.model_t3 = QSqlRelationalTableModel()
self.model_t3.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model_t3.setTable('facdeps')
self.model_t3.setRelation(0,QSqlRelation("faculties","id","name"))
self.model_t3.setRelation(1,QSqlRelation("departments","id","name"))
self.table_t3.setModel(self.model_t3)
#self.table_t3.setModelColumn(1)
self.model_t3.select()
self.table_t3.show()
self.model_t3.setHeaderData(0, Qt.Horizontal,
QVariant(u'Факультет'))
self.model_t3.setHeaderData(1, Qt.Horizontal,
QVariant(u'Назва кафеди'))
self.table_t3.resizeColumnsToContents()
self.model2_t3= QSqlQueryModel()
self.model2_t3.setQuery('select name, id from faculties')
#self.combo_t3.setModelColumn()
self.combo_t3.setModel(self.model2_t3)
self.table_t3.setSelectionBehavior(QAbstractItemView.SelectRows)
def populate_tab4(self):
self.model_t4 = QSqlQueryModel()
self.q_t4 = """
SELECT people.name, departments.name, faculties.name, people.id
FROM work, people, departments, faculties, facdeps
WHERE people.id=work.human
and departments.id = work.department
and faculties.id=facdeps.faculty
and departments.id=facdeps.department"""
self.model_t4.setQuery(self.q_t4)
self.model_t4.setHeaderData(0, Qt.Horizontal, QVariant(u'Им\'я'))
self.model_t4.setHeaderData(1, Qt.Horizontal, QVariant(u'Кафедра'))
self.model_t4.setHeaderData(2, Qt.Horizontal, QVariant(u'Факультет'))
self.table_t4.setModel(self.model_t4)
self.table_t4.resizeColumnsToContents()
self.table_t4.hideColumn(3)
self.model2_t4= QSqlQueryModel()
self.model2_t4.setQuery('select name, id from faculties')
#self.combo_t3.setModelColumn()
self.combo1_t4.setModel(self.model2_t3)
self.model3_t4= QSqlQueryModel()
self.model3_t4.setQuery('select name, id from departments')
self.combo2_t4.setModel(self.model3_t4)
self.tab4_select_dep(0)
self.table_t4.setSelectionBehavior(QAbstractItemView.SelectRows)
def setup_ui(self, window):
self.mw = window
self.mw.setObjectName("MainWindow")
self.mw.resize(1024, 768)
self.centralwidget = QWidget(self.mw)
self.vLayout = QVBoxLayout(self.centralwidget)
self.mw.setCentralWidget(self.centralwidget)
self.tabWidget = QTabWidget(self.centralwidget)
#tab1
self.tab1 = QWidget()
self.vLayout_t1 = QVBoxLayout(self.tab1)
self.table = QTableView(self.tab1)
self.vLayout_t1.addWidget(self.table)
self.tabWidget.addTab(self.tab1, "")
#tab2
self.tab2 = QWidget()
self.vLayout_t2 = QVBoxLayout(self.tab2)
self.list_t2 = QListView(self.tab2)
self.vLayout_t2.addWidget(self.list_t2)
self.hLayout_t2 = QHBoxLayout()
self.delete_t2 = QPushButton(self.tab2)
self.vLayout_t2.addWidget(self.delete_t2)
self.save_t2 = QPushButton(self.tab2)
self.hLayout2_t2 = QHBoxLayout()
self.hLayout2_t2.addWidget(self.save_t2)
self.hLayout2_t2.addWidget(self.delete_t2)
self.vLayout_t2.addLayout(self.hLayout2_t2)
self.vLayout_t2.addLayout(self.hLayout_t2)
self.edit_t2 = QLineEdit(self.tab2)
self.hLayout_t2.addWidget(self.edit_t2)
self.button_t2 = QPushButton(self.tab2)
self.hLayout_t2.addWidget(self.button_t2)
self.tabWidget.addTab(self.tab2, "")
#tab3
self.tab3 = QWidget()
self.vLayout_t3 = QVBoxLayout(self.tab3)
self.table_t3 = QTableView(self.tab3)
self.vLayout_t3.addWidget(self.table_t3)
self.hLayout_t3 = QHBoxLayout()
self.delete_t3 = QPushButton(self.tab3)
self.vLayout_t3.addWidget(self.delete_t3)
#self.save_t3 = QPushButton(self.tab3)
self.hLayout2_t3 = QHBoxLayout()
#self.hLayout2_t3.addWidget(self.save_t3)
self.hLayout2_t3.addWidget(self.delete_t3)
self.vLayout_t3.addLayout(self.hLayout2_t3)
self.vLayout_t3.addLayout(self.hLayout_t3)
self.label1_t3 = QLabel(self.tab3)
self.label1_t3.setText(QString(u'Факультет:'))
self.hLayout_t3.addWidget(self.label1_t3)
self.combo_t3 = QComboBox(self.tab3)
self.hLayout_t3.addWidget(self.combo_t3)
self.label2_t3 = QLabel(self.tab3)
self.label2_t3.setText(QString(u'Кафедра:'))
self.hLayout_t3.addWidget(self.label2_t3)
self.edit_t3 = QLineEdit(self.tab3)
self.hLayout_t3.addWidget(self.edit_t3)
self.button_t3 = QPushButton(self.tab3)
self.hLayout_t3.addWidget(self.button_t3)
self.tabWidget.addTab(self.tab3, "")
#tab4
self.tab4 = QWidget()
self.vLayout_t4 = QVBoxLayout(self.tab4)
self.table_t4 = QTableView(self.tab4)
self.vLayout_t4.addWidget(self.table_t4)
self.hLayout_t4 = QHBoxLayout()
self.delete_t4 = QPushButton(self.tab4)
self.vLayout_t4.addWidget(self.delete_t4)
#self.save_t3 = QPushButton(self.tab3)
self.hLayout2_t4 = QHBoxLayout()
#self.hLayout2_t3.addWidget(self.save_t3)
self.hLayout2_t4.addWidget(self.delete_t4)
self.vLayout_t4.addLayout(self.hLayout2_t4)
self.vLayout_t4.addLayout(self.hLayout_t4)
self.label1_t4 = QLabel(self.tab4)
self.label1_t4.setText(QString(u'Факультет:'))
self.hLayout_t4.addWidget(self.label1_t4)
self.combo1_t4 = QComboBox(self.tab4)
self.hLayout_t4.addWidget(self.combo1_t4)
self.label2_t4 = QLabel(self.tab4)
self.label2_t4.setText(QString(u'Кафедра:'))
self.hLayout_t4.addWidget(self.label2_t4)
self.combo2_t4 = QComboBox(self.tab4)
self.hLayout_t4.addWidget(self.combo2_t4)
self.label2_t4 = QLabel(self.tab4)
self.label2_t4.setText(QString(u'П.I.П.:'))
self.hLayout_t4.addWidget(self.label2_t4)
self.edit_t4 = QLineEdit(self.tab4)
self.hLayout_t4.addWidget(self.edit_t4)
self.button_t4 = QPushButton(self.tab4)
self.hLayout_t4.addWidget(self.button_t4)
self.tabWidget.addTab(self.tab4, "")
#tab5
self.tab5 = QWidget()
self.vLayout_t5 = QVBoxLayout(self.tab5)
self.table_t5 = QTableView(self.tab5)
self.vLayout_t5.addWidget(self.table_t5)
self.hLayout_t5 = QHBoxLayout()
self.delete_t5 = QPushButton(self.tab5)
self.vLayout_t5.addWidget(self.delete_t5)
#self.save_t3 = QPushButton(self.tab3)
self.hLayout2_t5 = QHBoxLayout()
#self.hLayout2_t3.addWidget(self.save_t3)
self.hLayout2_t5.addWidget(self.delete_t5)
self.vLayout_t5.addLayout(self.hLayout2_t5)
self.vLayout_t5.addLayout(self.hLayout_t5)
self.label1_t5 = QLabel(self.tab5)
self.label1_t5.setText(QString(u'Факультет:'))
self.hLayout_t5.addWidget(self.label1_t5)
self.combo1_t5 = QComboBox(self.tab5)
self.hLayout_t5.addWidget(self.combo1_t5)
self.label2_t5 = QLabel(self.tab5)
self.label2_t5.setText(QString(u'Кафедра:'))
self.hLayout_t5.addWidget(self.label2_t5)
self.combo2_t5 = QComboBox(self.tab5)
self.hLayout_t5.addWidget(self.combo2_t5)
self.label3_t5 = QLabel(self.tab5)
self.label3_t5.setText(QString(u'Виконавець:'))
self.hLayout_t5.addWidget(self.label3_t5)
self.combo3_t5 = QComboBox(self.tab5)
self.hLayout_t5.addWidget(self.combo3_t5)
self.hLayout3_t5 = QHBoxLayout()
self.vLayout_t5.addLayout(self.hLayout3_t5)
self.label4_t5 = QLabel(self.tab5)
self.label4_t5.setText(QString(u'Замовник:'))
self.hLayout3_t5.addWidget(self.label4_t5)
self.combo4_t5 = QComboBox(self.tab5)
self.hLayout3_t5.addWidget(self.combo4_t5)
self.label5_t5 = QLabel(self.tab5)
self.label5_t5.setText(QString(u'З:'))
self.hLayout3_t5.addWidget(self.label5_t5)
self.time1_t5 = QDateEdit(self.tab5)
self.hLayout3_t5.addWidget(self.time1_t5)
self.label6_t5 = QLabel(self.tab5)
self.label6_t5.setText(QString(u'По:'))
self.hLayout3_t5.addWidget(self.label6_t5)
self.time2_t5 = QDateEdit(self.tab5)
self.hLayout3_t5.addWidget(self.time2_t5)
#self.edit_t5 = QLineEdit(self.tab5)
#self.hLayout_t5.addWidget(self.edit_t5)
#self.button_t5 = QPushButton(self.tab5)
#self.hLayout_t5.addWidget(self.button_t5)
self.tabWidget.addTab(self.tab5, "")
#translation
self.tabWidget.setTabText(0,
QApplication.translate("Main_Window", "Перегляд бази",
None, QApplication.UnicodeUTF8))
self.tabWidget.setTabText(1,
QApplication.translate("Main_Window", "Факультети", None,
QApplication.UnicodeUTF8))
self.tabWidget.setTabText(2,
QApplication.translate("Main_Window", "Кафедри", None,
QApplication.UnicodeUTF8))
self.tabWidget.setTabText(3,
QApplication.translate("Main_Window", "Люди", None,
QApplication.UnicodeUTF8))
self.tabWidget.setTabText(4,
QApplication.translate("Main_Window", "Робота", None,
QApplication.UnicodeUTF8))
self.delete_t2.setText(QApplication.translate("Main_Window",
"Видалити факультет", None,
QApplication.UnicodeUTF8))
self.save_t2.setText(QApplication.translate("Main_Window",
"Зберегти змiни", None, QApplication.UnicodeUTF8))
self.button_t2.setText(QApplication.translate("Main_Window",
"Додати", None, QApplication.UnicodeUTF8))
self.delete_t3.setText(QApplication.translate("Main_Window",
"Видалити кафедру", None,
QApplication.UnicodeUTF8))
#self.save_t3.setText(QApplication.translate("Main_Window",
# "Зберегти змiни", None, QApplication.UnicodeUTF8))
self.button_t3.setText(QApplication.translate("Main_Window",
"Додати", None, QApplication.UnicodeUTF8))
self.delete_t4.setText(QApplication.translate("Main_Window",
"Видалити людину", None,
QApplication.UnicodeUTF8))
self.button_t4.setText(QApplication.translate("Main_Window",
"Додати", None, QApplication.UnicodeUTF8))
self.vLayout.addWidget(self.tabWidget)
def add_faculty(self):
if len(str(self.edit_t2.text().toUtf8())) == 0:
self.show_error(u'Помилка',u'Ви не ввели назву факультету')
else:
checkq = QSqlQuery('SELECT * FROM faculties where name = ?')
checkq.addBindValue(QVariant(self.edit_t2.text()))
checkq.exec_()
if checkq.next():
self.show_error(u'Помилка',u'Такий факультет вже iснує')
else:
query = QSqlQuery()
query.prepare("INSERT into faculties values (null, ?);");
query.addBindValue(QVariant(self.edit_t2.text()));
try:
query.exec_();
except:
self.show_error(u'Помилка',
u'Не вдалося додати новий факультет')
self.model_t2.select()
finally:
self.model_t2.select()
self.edit_t2.clear()
def add_dep(self):
a = self.model2_t3.record(self.combo_t3.currentIndex())
checkq = QSqlQuery("""SELECT * from facdeps, departments
WHERE departments.name = ? and facdeps.faculty = ?""");
checkq.addBindValue(QVariant(self.edit_t3.text().toUtf8()))
checkq.addBindValue(QVariant(a.value("id").toString()))
checkq.exec_()
if checkq.next():
self.show_error(u'Помилка',
u'На вибраному факультетi вже є така кафедра')
else:
query = QSqlQuery("INSERT into departments VALUES (null,?)")
query.addBindValue(QVariant(self.edit_t3.text().toUtf8()))
query.exec_()
query2 = QSqlQuery("INSERT INTO facdeps VALUES (?, ?)")
query2.addBindValue(QVariant(a.value("id").toString()))
query2.addBindValue(QVariant(query.lastInsertId()))
query2.exec_()
self.model_t3.select()
#self.model2_t3.select()
self.model2_t3.setQuery('')
self.model2_t3.setQuery('select name, id from faculties')
self.edit_t3.clear()
self.table_t3.resizeColumnsToContents()
def add_human(self):
fac = self.model2_t4.record(self.combo1_t4.currentIndex())
fac = fac.value("id").toString()
dep = self.model3_t4.record(self.combo2_t4.currentIndex())
dep = dep.value("id").toString()
q = QSqlQuery("insert into people values (null,?)")
q.addBindValue(QVariant(self.edit_t4.text()))
q.exec_()
q2 = QSqlQuery("insert into work values (?,?)")
q2.addBindValue(QVariant(q.lastInsertId()))
q2.addBindValue(QVariant(dep))
q2.exec_()
self.model_t1.setQuery(QSqlQuery(""))
self.model_t1.setQuery(QSqlQuery(self.q_t1))
self.model_t4.setQuery("")
self.model_t4.setQuery(self.q_t4)
self.table_t4.resizeColumnsToContents()
self.table_t4.hideColumn(3)
if __name__ == "__main__":
app = QApplication(sys.argv)
window = QMainWindow()
main_window = Main_Window(window)
#main_window.setup_ui(window)
window.show()
app.exec_()