def getMaxbatch(self, label):
myModel = QSqlQueryModel(self)
if label == 'enter':
myModel.setQuery("select 批次 from 入库记录表")
elif label == 'out':
myModel.setQuery("select 批次 from 出库记录表")
n = myModel.rowCount()
if myModel.record(0).isNull('批次') == True: # 检测入库记录里的最大批次
max_batch = 0
else:
max_batch = myModel.record(n - 1).value('批次') # 批次按顺序排列，查找最大批次
print('共有%d条记录，最大批次为%d' % (n, max_batch))
return max_batch
def thing_by_id(self, idx):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where id={}"\
.format(idx),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def thing_by_name(self, name):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where name='{}'"\
.format(name),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def rules(self):
query = QSqlQueryModel()
query.setQuery("select * from rules")
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
def rules_with_thing(self, thing):
query = QSqlQueryModel()
query.setQuery("select * from rules "\
"where {} = ANY(things)"\
.format(thing.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
def setModel(self, model: QtSql.QSqlQueryModel):
if len(self._list) == 0:
raise Exception("显示列集合为空，请先使用append()方法添加列信息！")
if not isinstance(model, QtSql.QSqlQueryModel):
raise Exception("model参数必须为QSqlQueryModel或其子类的实例！")
self.__model = model
record = model.record()
for vc in self._list:
at = vc.fieldIndex
if at != -1:
field = record.field(at)
vc.jpFieldType = getFieldType(self.db, field.typeID())
if vc.formatString is None:
self._setViewColumnAlighAndFormatString(vc)
def things(self, types):
query = QSqlQueryModel()
if types is not None:
query.setQuery("select * from things "\
"where type=ANY({})"\
.format(_array_string(types)))
else:
query.setQuery("select * from things")
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
thing = self.thing_by_sqlrecord(query.record(i))
ret.append(thing)
return ret
def Absolute_statis(self, label):
myModel = QSqlQueryModel(self)
if label == 'current':
myModel.setQuery("select 种类 from 工件信息表")
elif label == 'enter':
myModel.setQuery("select 种类 from 入库记录表")
elif label == 'out':
myModel.setQuery("select 种类 from 出库记录表")
allnum = myModel.rowCount()
all_dict = {}
for i in range(allnum):
rec = myModel.record(i)
if rec.value('种类') in all_dict:
all_dict[rec.value('种类')] += 1
else:
all_dict[rec.value('种类')] = 1
return all_dict
def rules_by_tids(self, t_ids, exact):
query = QSqlQueryModel()
if exact:
query.setQuery("select * from rules "\
"where things={}"\
.format(_array_string(t_ids)),
db=self.db)
else:
query.setQuery("select * from rules "\
"where things @>{}"\
.format(_array_string(t_ids)),
db=self.db)
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
ret = []
for i in range(query.rowCount()):
rule = self.rule_by_sqlrecord(query.record(i))
ret.append(rule)
return ret
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.tableView)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actRecEdit.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.actTestSQL.setEnabled(True)
def __updateRecord(self, recNo):
curRec = self.qryModel.record(recNo)
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec()
query.first()
if (not query.isValid()):
return
curRec = query.record()
dlgData = QmyDialogData(self)
dlgData.setUpdateRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
if (not query.exec()):
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec()
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot()
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo = -1")
curRec = query.record()
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec()
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo)
if (curRec.isEmpty()):
return
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot()
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB)
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB)
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()):
empID = qryEmpList.value("empNo")
salary = qryEmpList.value("Salary")
salary = salary + 500
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary)
qryUpdate.exec()
if not qryEmpList.next():
break
self.qryModel.query().exec()
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot()
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
query.exec('''UPDATE employee SET Salary=500+Salary''')
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
print("SQL OK")
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
self.ui.statusBar.showMessage("当前记录：工号 = %d" % empNo)
class Filtrage_bope_dialog(QDialog, Ui_dlgBopeRechercheForm):
'''
Class de la fenêtre permettant le filtrage attributaire des baux de pêche
:param QDialog: Permet d'afficher l'interface graphique comme une fenêtre indépendante
:type QDialog: QDialog
:param Ui_dlgBopeRechercheForm: Class du script de l'interface graphique du formulaire,
apporte les éléments de l'interface
:type Ui_dlgBopeRechercheForm: class
'''
def __init__(self, db, dbType, dbSchema, modelBauxPe, parent=None):
'''
Constructeur, récupération de variable, connection des événements et remplissage des combobox
:param db: définie dans le setupModel(),
représente la connexion avec la base de données
:type db: QSqlDatabase
:param dbType: type de la base de données (postgre)
:type dbType: str
:param dbSchema: nom du schéma sous PostgreSQL contenant les données (data)
:type dbSchema: unicode
:param modelBauxPe: modèle droit de pêche qui contient les données de la base de données
:type modelBauxPe: QSqlRelationalTableModel
:param parent: défini que cette fenêtre n'hérite pas d'autres widgets
:type parent: NoneType
'''
super(Filtrage_bope_dialog, self).__init__(parent)
self.db = db
self.dbType = dbType
self.dbSchema = dbSchema
self.modelBauxPe = modelBauxPe
self.setupUi(self)
self.btnAnnuler.clicked.connect(self.reject)
self.btnExec.clicked.connect(self.execution)
self.btnRaz.clicked.connect(self.raz)
self.btnEt.clicked.connect(self.et)
self.btnOu.clicked.connect(self.ou)
self.btnPrevisualiser.clicked.connect(self.previSql)
self.btnId.clicked.connect(self.ajoutId)
self.btnRiviere.clicked.connect(self.ajoutRiviere)
self.btnAappma.clicked.connect(self.ajoutAappma)
self.btnPossession.clicked.connect(self.ajoutPossession)
self.btnSign.clicked.connect(self.ajoutDateSign)
self.btnFin.clicked.connect(self.ajoutDateFin)
self.btnC.clicked.connect(self.ajoutCommune)
self.btnCS.clicked.connect(self.ajoutComSection)
self.btnCSP.clicked.connect(self.ajoutComSecParcelle)
self.btnProprio.clicked.connect(self.ajoutProprio)
self.btnAdresse.clicked.connect(self.ajoutAdresse)
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.leTel.setInputMask("#9999999999999")
self.possessionBool = False
self.aappmaBool = False
self.anneeSignBool = False
self.anneeFinBool = False
self.CBool = False
self.CSBool = False
self.CSPBool = False
self.wwhere = ""
self.wwherePossession = ""
self.wwhereProprio = ""
self.modelAappma = QSqlTableModel(self, self.db)
wrelation = "aappma"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelAappma.setTable(wrelation)
self.modelAappma.setSort(1, Qt.AscendingOrder)
if (not self.modelAappma.select()):
QMessageBox.critical(self, u"Remplissage du modèle AAPPMA",
self.modelAappma.lastError().text(),
QMessageBox.Ok)
self.cmbAappma.setModel(self.modelAappma)
self.cmbAappma.setModelColumn(self.modelAappma.fieldIndex("apma_nom"))
self.modelRiviere = QSqlTableModel(self, self.db)
wrelation = "cours_eau"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelRiviere.setTable(wrelation)
self.modelRiviere.setFilter("ceau_nom <> 'NR'")
self.modelRiviere.setSort(2, Qt.AscendingOrder)
if (not self.modelRiviere.select()):
QMessageBox.critical(self, u"Remplissage du modèle Rivière",
self.modelRiviere.lastError().text(),
QMessageBox.Ok)
self.cmbRiviere.setModel(self.modelRiviere)
self.cmbRiviere.setModelColumn(
self.modelRiviere.fieldIndex("ceau_nom"))
self.modelCommune = QSqlTableModel(self, self.db)
wrelation = "commune"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelCommune.setTable(wrelation)
self.modelCommune.setSort(2, Qt.AscendingOrder)
if (not self.modelCommune.select()):
QMessageBox.critical(self, u"Remplissage du modèle Commune",
self.modelCommune.lastError().text(),
QMessageBox.Ok)
self.cmbCommune.setModel(self.modelCommune)
self.cmbCommune.setModelColumn(self.modelCommune.fieldIndex("com_nom"))
self.cmbCommune.setCurrentIndex(1)
self.modelSection = QSqlQueryModel(self)
self.modelParcelle = QSqlQueryModel(self)
self.cmbSection.setModel(self.modelSection)
self.cmbSection.setModelColumn(2)
self.cmbParcelle.setModel(self.modelParcelle)
self.cmbParcelle.setModelColumn(1)
self.cmbCommune.currentIndexChanged.connect(self.changeCmbCommune)
self.cmbSection.currentIndexChanged.connect(self.changeCmbSection)
self.cmbCommune.setCurrentIndex(0)
def reject(self):
'''Ferme la fenêtre si clic sur le bouton annuler'''
QDialog.reject(self)
def changeCmbCommune(self, newInd):
'''
Filtre la combobox section en fonction de la commune affichée dans celle des communes
:param newInd: index courant dans la combobox
:type newInd: int
'''
self.modelParcelle.clear()
self.cmbParcelle.clear()
self.cmbParcelle.setModel(self.modelParcelle)
self.cmbParcelle.setModelColumn(1)
record = self.modelCommune.record(newInd)
wcom_insee = record.value(self.modelCommune.fieldIndex("com_insee"))
self.modelSection.clear()
wrelation = "section"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelSection.setQuery(
"select sec_id, sec_nom || ' ; ' || sec_com_abs from " +
wrelation +
" where sec_com_insee = '%s' order by sec_nom;" % str(wcom_insee),
self.db)
if self.modelSection.lastError().isValid():
QMessageBox.critical(self, u"Remplissage du modèle Section",
self.modelSection.lastError().text(),
QMessageBox.Ok)
self.cmbSection.setModel(self.modelSection)
self.cmbSection.setModelColumn(1)
self.cmbSection.setCurrentIndex(0)
def changeCmbSection(self, newInd):
'''
Filtre la combobox parcelle en fonction de la section affichée dans celle des sections
:param newInd: index courant dans la combobox
:type newInd: int
'''
record = self.modelSection.record(newInd)
wsec_id = record.value(0)
self.cmbParcelle.clear()
self.modelParcelle.clear()
wrelation = "parcelle"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelParcelle.setQuery(
"select par_id, par_numero from " + wrelation +
" where par_sec_id = '%s' order by par_numero;" % str(wsec_id),
self.db)
if self.modelParcelle.lastError().isValid():
QMessageBox.critical(self, u"Remplissage du modèle Parcelle",
self.modelParcelle.lastError().text(),
QMessageBox.Ok)
self.cmbParcelle.setModel(self.modelParcelle)
self.cmbParcelle.setModelColumn(1)
def raz(self):
'''Réinitialise toutes les variables de la fenêtre afin de recommencer une nouvelle requête'''
self.possessionBool = False
self.aappmaBool = False
self.anneeSignBool = False
self.anneeFinBool = False
self.CBool = False
self.CSBool = False
self.CSPBool = False
self.spnId.setValue(0)
self.wrq = ""
self.txtSql.setText("")
self.wwhere = ""
self.wwherePossession = ""
self.wwhereProprio = ""
self.chkPossession.setChecked(False)
self.dateSign.setDate(QDate(2000, 1, 1))
self.dateFin.setDate(QDate(2000, 1, 1))
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.btnId.setEnabled(True)
self.btnSign.setEnabled(True)
self.btnFin.setEnabled(True)
self.btnRiviere.setEnabled(True)
self.btnAappma.setEnabled(True)
self.btnPossession.setEnabled(True)
self.btnC.setEnabled(True)
self.btnCS.setEnabled(True)
self.btnCSP.setEnabled(True)
self.btnProprio.setEnabled(True)
self.btnAdresse.setEnabled(True)
def et(self):
'''Change l'état des boutons et ajoute "and" à la requête'''
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.btnId.setEnabled(True)
self.btnRiviere.setEnabled(True)
self.btnProprio.setEnabled(True)
self.btnAdresse.setEnabled(True)
if self.possessionBool == False:
self.btnPossession.setEnabled(True)
if self.aappmaBool == False:
self.btnAappma.setEnabled(True)
if self.anneeSignBool == False:
self.btnSign.setEnabled(True)
if self.anneeFinBool == False:
self.btnFin.setEnabled(True)
if self.CBool == False:
self.btnC.setEnabled(True)
if self.CSBool == False:
self.btnCS.setEnabled(True)
if self.CSPBool == False:
self.btnCSP.setEnabled(True)
self.wwhere += " AND "
def ou(self):
'''Change l'état des boutons et ajoute "or" à la requête'''
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.btnId.setEnabled(True)
self.btnSign.setEnabled(True)
self.btnFin.setEnabled(True)
self.btnRiviere.setEnabled(True)
self.btnAappma.setEnabled(True)
self.btnC.setEnabled(True)
self.btnCS.setEnabled(True)
self.btnCSP.setEnabled(True)
self.btnProprio.setEnabled(True)
self.btnAdresse.setEnabled(True)
self.btnPossession.setEnabled(True)
self.possessionBool = False
self.aappmaBool = False
self.anneeSignBool = False
self.anneeFinBool = False
self.CBool = False
self.CSBool = False
self.CSPBool = False
self.wwhere += " OR "
def ajoutId(self):
'''Change l'état des boutons et ajoute un critère d'id à la requête'''
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.wid = self.spnId.value()
if self.spnId.value() != "":
if self.wid != "":
self.wwhere += "bope_id = '" + str(self.wid) + "'"
self.spnId.setValue(0)
self.spnId.setFocus()
def ajoutRiviere(self):
'''Change l'état des boutons et ajoute un critère de cours d'eau à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
wfrombce = "bail_cours_eau"
if self.dbType == "postgres":
self.wfromCeau = self.dbSchema + "." + wfrombce
wrecord = self.cmbRiviere.model().record(
self.cmbRiviere.currentIndex())
self.wCeau = wrecord.value(0)
if self.cmbRiviere.currentText() != "":
if self.wCeau != "":
self.wwhere += "bope_id in (select distinct bce_bope_id from " + self.wfromCeau + " where bce_ceau_id = '" + str(
self.wCeau) + "')"
def ajoutAappma(self):
'''Change l'état des boutons et ajoute un critère d'aappma à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.aappmaBool = True
wrecord = self.cmbAappma.model().record(self.cmbAappma.currentIndex())
self.wbope_aappma = wrecord.value(0)
if self.cmbAappma.currentText() != "":
if self.wbope_aappma != "":
self.wwhere += "bope_apma_id = '" + str(
self.wbope_aappma) + "'"
def ajoutPossession(self):
'''Change l'état des boutons et ajoute un critère de possession à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.possessionBool = True
if self.chkPossession.isChecked() == True:
self.wwherePossession = "bope_existe = True"
else:
self.wwherePossession = "bope_existe = False"
self.wwhere += self.wwherePossession
def ajoutDateSign(self):
'''Change l'état des boutons et ajoute un critère de date de signature à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.anneeSignBool = True
self.wbope_date_sign = self.dateSign.date().toString("yyyy")
if self.wbope_date_sign != "":
self.wwhere += "date_part('year', bope_date_sign) = '" + str(
self.wbope_date_sign) + "'"
def ajoutDateFin(self):
'''Change l'état des boutons et ajoute un critère de date d'expiration à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.anneeFinBool = True
self.wbope_date_fin = self.dateFin.date().toString("yyyy")
if self.wbope_date_fin != "":
self.wwhere += "date_part('year', bope_date_fin) = '" + str(
self.wbope_date_fin) + "'"
def ajoutCommune(self):
'''Change l'état des boutons et ajoute un critère de commune à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.CBool = True
wrecord = self.cmbCommune.model().record(
self.cmbCommune.currentIndex())
self.wcommune = wrecord.value(0)
if self.cmbCommune.currentText() != "":
if self.wcommune != "":
self.wwhere += "bope_id in (select bope_id from data.droit_peche, data.parcelle, data.section where bope_id = par_bope_id and par_sec_id = sec_id and sec_com_insee = '" + str(
self.wcommune) + "')"
def ajoutComSection(self):
'''Change l'état des boutons et ajoute un critère de commune et section à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.CSBool = True
wrecord = self.cmbSection.model().record(
self.cmbSection.currentIndex())
self.wsection = wrecord.value(0)
if self.cmbSection.currentText() != "":
if self.wsection != "":
self.wwhere += "bope_id in (select bope_id from data.droit_peche, data.parcelle where par_bope_id = bope_id and par_sec_id = '" + str(
self.wsection) + "')"
def ajoutComSecParcelle(self):
'''Change l'état des boutons et ajoute un critère de commune, section et parcelle à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.CSPBool = True
wrecord = self.cmbParcelle.model().record(
self.cmbParcelle.currentIndex())
self.wparcelle = wrecord.value(0)
if self.cmbParcelle.currentText() != "":
if self.wparcelle != "":
self.wwhere += "bope_id in (select par_bope_id from data.parcelle where par_id = '" + str(
self.wparcelle) + "')"
def ajoutProprio(self):
'''
Change l'état des boutons et ajoute un critère de nom de propriétaire
et / ou de mail
et / ou de téléphone à la requête
'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.wwhereProprio = ""
self.wnom = self.leNom.text()
if "'" in self.wnom and "''" not in self.wnom:
self.wnom = self.wnom.replace("'", "''")
self.wmail = self.leMail.text()
self.wtel = self.leTel.text()
if self.leNom.text() != "":
if self.wnom != "":
self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_nom ilike '%" + self.wnom + "%')"
if self.leMail.text() != "":
if self.wmail != "":
if self.wnom != "":
self.wwhereProprio += " and "
self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_mail = '" + self.wmail + "')"
if self.leTel.text() != "":
if self.wtel != "":
if self.wnom != "" or self.wmail != "":
self.wwhereProprio += " and "
self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_telephone = '" + str(
self.wtel) + "')"
if self.wwhereProprio != "":
self.wwhere += self.wwhereProprio
self.leNom.setText("")
self.leMail.setText("")
self.leTel.setText("")
self.leNom.setFocus()
def ajoutAdresse(self):
'''Change l'état des boutons et ajoute un critère d'adresse à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnId.setEnabled(False)
self.btnSign.setEnabled(False)
self.btnFin.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnPossession.setEnabled(False)
self.btnC.setEnabled(False)
self.btnCS.setEnabled(False)
self.btnCSP.setEnabled(False)
self.btnProprio.setEnabled(False)
self.btnAdresse.setEnabled(False)
self.wadresse = self.leAdresse.text()
if "'" in self.wadresse and "''" not in self.wadresse:
self.wadresse = self.wadresse.replace("'", "''")
if self.leAdresse.text() != "":
if self.wadresse != "":
self.wwhere += " bope_id in (select distinct bope_id from data.droit_peche, data.proprietaire where (bope_pro_id = pro_id ) and (pro_adresse ilike '%" + self.wadresse + "%'))"
else:
if self.leAdresse.text() == "":
if self.wadresse == "":
self.wwhere += " bope_id in (select distinct bope_id from data.droit_peche, data.proprietaire where (bope_pro_id = pro_id )"
self.leAdresse.setText("")
self.leAdresse.setFocus()
def creaRequete(self):
# def previSql(self):
'''Regroupe les différentes variables contenant les clauses de la requête SQL et les concatène pour en faire une requête exécutable'''
self.wrq = ""
# Construit la clause FROM de la requête
cfrom = "droit_peche"
if self.dbType == "postgres":
cfrom = self.dbSchema + "." + cfrom
# Construit la clause SELECT et ajoute la clause FROM à la requête
self.wrq = "SELECT DISTINCT bope_id FROM " + cfrom
# Construit la clause WHERE et ORDER BY et l'ajoute à la requête
if self.wwhere != "":
#Supprime l'opérateur "and" ou "or" si celui-ci n'est pas suivi d'un critère
operateurs = ["AND", "OR"]
fin_where = self.wwhere[-5:]
for ext in operateurs:
if ext in fin_where:
self.wwhere = self.wwhere[:-4]
self.wrq += " WHERE " + self.wwhere + " ORDER BY bope_id"
else:
self.wrq += " ORDER BY bope_id"
def previSql(self):
'''Permet de prévisualiser la requête avant de l'éxecuter'''
self.txtSql.setText("")
self.creaRequete()
# Affiche la requête
self.txtSql.setText(self.wrq)
def execution(self):
'''Permet d'éxecuter la requête'''
# Vérifie la non présence de mot pouvant endommager la base de données
erreur = False
interdit = [
"update", "delete", "insert", "intersect", "duplicate", "merge",
"truncate", "create", "drop", "alter"
]
if self.txtSql.toPlainText() != "":
self.requete = self.txtSql.toPlainText()
else:
self.creaRequete()
self.requete = self.wrq
testRequete = self.requete.lower()
for mot in interdit:
if mot in testRequete:
erreur = True
if erreur == True:
QMessageBox.critical(
self, u"Erreur SQL",
u"Vous essayez d'exécuter une requête qui peut endommager la base de données !",
QMessageBox.Ok)
# Après récupération du contenu de la zone de texte, exécute la requête
else:
query = QSqlQuery(self.db)
query.prepare(self.requete)
if query.exec_():
wparam = ""
while query.next():
wparam += str(query.value(0)) + ","
if (wparam != ""):
wparam = "(" + wparam[0:len(wparam) - 1] + ")"
if self.modelBauxPe:
# Filtre le modèle des droits de pêche et ferme la fenêtre
self.modelBauxPe.setFilter("bope_id in %s" % wparam)
self.modelBauxPe.select()
QDialog.accept(self)
else:
QMessageBox.information(
self, "Filtrage",
u"Aucun bail de pêche ne correspond aux critères ...",
QMessageBox.Ok)
else:
QMessageBox.critical(self, u"Erreur SQL",
query.lastError().text(), QMessageBox.Ok)
class AnalyzeTab(QWidget):
def __init__(self, ctx):
super().__init__()
self.ctx = ctx
self.bins = 20
self.initVar()
self.initModel()
self.initUI()
self.set_axis_opts()
self.set_filter()
self.sigConnect()
self.apply_filter()
def initVar(self):
self.x_opts = [
'Record ID', 'CTDIvol', 'Age', 'Deff', 'Dw', 'SSDE',
'Effective Dose', 'DLP', 'DLPc'
]
self.x_units = [
'', 'mGy', 'Year', 'cm', 'cm', 'mGy', 'mSv', 'mGy-cm', 'mGy-cm'
]
self.y_opts = [
'CTDIvol', 'Age', 'Deff', 'Dw', 'SSDE', 'Effective Dose', 'DLP',
'DLPc', 'Frequency'
]
self.y_units = [
'mGy', 'Year', 'cm', 'cm', 'mGy', 'mSv', 'mGy-cm', 'mGy-cm', ''
]
self.age_ftr1 = -1
self.age_ftr2 = -1
self.date_ftr1 = QDate.currentDate()
self.date_ftr2 = QDate.currentDate()
self.brand_ftr = 'All'
self.instn_ftr = 'All'
self.protocol_ftr = 'All'
self.scanner_ftr = 'All'
self.sex_ftr = 'All'
self.x_opt = self.x_opts[0]
self.y_opt = self.y_opts[0]
def initModel(self):
self.query_model = QSqlQueryModel()
self.data_query_model = QSqlQueryModel()
def sigConnect(self):
self.x_cb.activated[str].connect(self.on_x_changed)
self.y_cb.activated[str].connect(self.on_y_changed)
self.sex_cb.activated[int].connect(self.on_sex_changed)
self.protocol_cb.activated[int].connect(self.on_protocol_changed)
self.instn_cb.activated[int].connect(self.on_instn_changed)
self.brand_cb.activated[int].connect(self.on_brand_changed)
self.scanner_cb.activated[int].connect(self.on_scanner_changed)
self.age_sb1.valueChanged.connect(self.on_age1_changed)
self.age_sb2.valueChanged.connect(self.on_age2_changed)
self.date_edit1.dateChanged.connect(self.on_date1_changed)
self.date_edit2.dateChanged.connect(self.on_date2_changed)
self.bins_sb.valueChanged.connect(self.on_bins_changed)
self.generate_btn.clicked.connect(self.on_generate)
self.reset_btn.clicked.connect(self.set_filter)
def initUI(self):
self.figure = PlotDialog()
self.x_cb = QComboBox()
self.y_cb = QComboBox()
self.sex_cb = QComboBox()
self.protocol_cb = QComboBox()
self.age_sb1 = QSpinBox()
self.age_sb2 = QSpinBox()
self.date_edit1 = QDateEdit()
self.date_edit2 = QDateEdit()
self.generate_btn = QPushButton('Generate')
self.bins_sb = QSpinBox()
self.bins_lbl = QLabel('Bins')
self.reset_btn = QPushButton('Reset Filter')
self.brand_cb = QComboBox()
self.scanner_cb = QComboBox()
self.instn_cb = QComboBox()
self.data_count_lbl = QLabel(str(self.data_query_model.rowCount()))
self.age_sb1.setSpecialValueText('-')
self.age_sb1.setRange(-1, -1)
self.age_sb1.setMinimumWidth(90)
self.age_sb2.setSpecialValueText('-')
self.age_sb2.setRange(-1, -1)
self.age_sb2.setMinimumWidth(90)
self.date_edit1.setDisplayFormat('dd/MM/yyyy')
self.date_edit1.setMinimumWidth(90)
self.date_edit2.setDisplayFormat('dd/MM/yyyy')
self.date_edit2.setMinimumWidth(90)
self.bins_sb.setMinimum(1)
self.bins_sb.setValue(self.bins)
self.bins_sb.setVisible(False)
self.bins_lbl.setVisible(False)
age_layout = QHBoxLayout()
age_layout.addWidget(self.age_sb1)
age_layout.addWidget(QLabel('to'))
age_layout.addWidget(self.age_sb2)
age_layout.addStretch()
date_layout = QHBoxLayout()
date_layout.addWidget(self.date_edit1)
date_layout.addWidget(QLabel('to'))
date_layout.addWidget(self.date_edit2)
date_layout.addStretch()
self.axis_grpbox = QGroupBox('Axis selection')
ax_layout = QFormLayout()
ax_layout.addRow(QLabel('x-axis'), self.x_cb)
ax_layout.addRow(QLabel('y-axis'), self.y_cb)
ax_layout.addRow(self.bins_lbl, self.bins_sb)
ax_layout.addWidget(self.generate_btn)
self.axis_grpbox.setLayout(ax_layout)
self.filter_grpbox = QGroupBox('Filter')
flt_layout = QFormLayout()
flt_layout.addRow(QLabel('Institution'), self.instn_cb)
flt_layout.addRow(QLabel('Manufacturer'), self.brand_cb)
flt_layout.addRow(QLabel('Scanner'), self.scanner_cb)
flt_layout.addRow(QLabel('Protocol'), self.protocol_cb)
flt_layout.addRow(QLabel('Sex'), self.sex_cb)
flt_layout.addRow(QLabel('Age'), age_layout)
flt_layout.addRow(QLabel('Exam Date'), date_layout)
flt_layout.addRow(QLabel('Data Count'), self.data_count_lbl)
flt_layout.addWidget(self.reset_btn)
self.filter_grpbox.setLayout(flt_layout)
mainlayout = QHBoxLayout()
mainlayout.addWidget(self.filter_grpbox)
mainlayout.addWidget(self.axis_grpbox)
self.setLayout(mainlayout)
def set_filter(self):
self.set_instn()
self.set_brand()
self.set_scanner()
self.set_protocol()
self.set_sex()
self.set_age_range()
self.set_date_range()
def set_instn(self):
sql = "SELECT DISTINCT institution FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.instns = [
self.query_model.record(idx).value('Institution')
for idx in range(self.query_model.rowCount())
]
try:
self.instns[self.instns.index('')] = 'Unspecified'
except:
pass
self.instns.insert(0, 'All')
self.instn_cb.clear()
self.instn_cb.addItems(self.instns)
self.on_instn_changed(0)
def set_brand(self):
sql = "SELECT DISTINCT manufacturer FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.brands = [
self.query_model.record(idx).value('Manufacturer')
for idx in range(self.query_model.rowCount())
]
try:
self.brands[self.brands.index('')] = 'Unspecified'
except:
pass
self.brands.insert(0, 'All')
self.brand_cb.clear()
self.brand_cb.addItems(self.brands)
self.on_brand_changed(0)
def set_scanner(self, filter=None):
if filter is not None:
sql = f'SELECT DISTINCT model FROM PATIENTS WHERE manufacturer="{filter}"'
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.scanners = [
self.query_model.record(idx).value('Model')
for idx in range(self.query_model.rowCount())
]
try:
self.scanners[self.scanners.index('')] = 'Unspecified'
except:
print('gagal')
else:
self.scanners = ['Unspecified']
self.scanners.insert(0, 'All')
self.scanner_cb.clear()
self.scanner_cb.addItems(self.scanners)
self.on_scanner_changed(0)
def set_protocol(self):
sql = "SELECT DISTINCT protocol FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.protocols = [
self.query_model.record(idx).value('Protocol')
for idx in range(self.query_model.rowCount())
]
try:
self.protocols[self.protocols.index('')] = 'Unspecified'
except:
pass
self.protocols.insert(0, 'All')
self.protocol_cb.clear()
self.protocol_cb.addItems(self.protocols)
self.on_protocol_changed(0)
def set_sex(self):
sql = "SELECT DISTINCT sex FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.sexes = [
self.query_model.record(idx).value('Sex')
for idx in range(self.query_model.rowCount())
]
try:
self.sexes[self.sexes.index('')] = 'Unspecified'
except:
pass
self.sexes.insert(0, 'All')
self.sex_cb.clear()
self.sex_cb.addItems(self.sexes)
self.on_sex_changed(0)
def set_age_range(self):
sql = "SELECT MAX(age) as max FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
try:
age_max = int(self.query_model.record(0).value('max'))
except:
age_max = -1
self.age_sb1.setRange(-1, age_max)
self.age_sb1.setValue(-1)
self.age_sb2.setRange(-1, age_max)
self.age_sb2.setValue(-1)
self.age_ftr1 = -1
self.age_ftr2 = -1
def set_date_range(self):
sql = "SELECT MAX(exam_date) as max FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
try:
date_max = QDate.fromString(
self.query_model.record(0).value('max'), 'yyyyMMdd')
except:
date_max = QDate.currentDate()
sql = "SELECT MIN(exam_date) as min FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
try:
date_min = QDate.fromString(
self.query_model.record(0).value('min'), 'yyyyMMdd')
except:
date_min = QDate(2000, 1, 1)
self.date_edit1.setDate(date_min)
self.date_edit2.setDate(date_max)
self.date_ftr1 = date_min
self.date_ftr2 = date_max
def set_axis_opts(self):
sql = "SELECT * FROM PATIENTS LIMIT 1"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
self.x_cb.clear()
self.y_cb.clear()
self.x_cb.addItems(self.x_opts)
self.y_cb.addItems(self.y_opts)
self.on_x_changed(self.x_opts[0])
self.on_y_changed(self.y_opts[0])
def on_x_changed(self, sel):
if sel == 'Dw' or sel == 'Deff':
dw = self.y_cb.findText('Dw')
de = self.y_cb.findText('Deff')
self.y_cb.removeItem(dw)
self.y_cb.removeItem(de)
else:
y_txt = self.y_cb.currentText()
self.y_cb.clear()
self.y_cb.addItems(self.y_opts)
self.y_cb.setCurrentText(y_txt)
unit_idx = self.x_opts.index(sel)
self.x_unit = self.x_units[unit_idx]
self.x_opt = sel
self.apply_filter()
def on_y_changed(self, sel):
if sel == 'Frequency':
self.bins_lbl.setVisible(True)
self.bins_sb.setVisible(True)
else:
self.bins_lbl.setVisible(False)
self.bins_sb.setVisible(False)
if sel == 'Dw' or sel == 'Deff':
dw = self.x_cb.findText('Dw')
de = self.x_cb.findText('Deff')
self.x_cb.removeItem(dw)
self.x_cb.removeItem(de)
else:
x_txt = self.x_cb.currentText()
self.x_cb.clear()
self.x_cb.addItems(self.x_opts)
self.x_cb.setCurrentText(x_txt)
unit_idx = self.y_opts.index(sel)
self.y_unit = self.y_units[unit_idx]
self.y_opt = sel
self.apply_filter()
def on_instn_changed(self, idx):
self.instn_ftr = self.instns[idx]
self.apply_filter()
def on_brand_changed(self, idx):
self.brand_ftr = self.brands[idx]
self.set_scanner(self.brand_ftr if idx != 0 else None)
self.apply_filter()
def on_scanner_changed(self, idx):
self.scanner_ftr = self.scanners[idx]
self.apply_filter()
def on_sex_changed(self, idx):
self.sex_ftr = self.sexes[idx]
self.apply_filter()
def on_protocol_changed(self, idx):
self.protocol_ftr = self.protocols[idx]
self.apply_filter()
def on_age1_changed(self):
self.age_ftr1 = self.age_sb1.value()
self.apply_filter()
def on_age2_changed(self):
self.age_ftr2 = self.age_sb2.value()
self.apply_filter()
def on_date1_changed(self):
self.date_ftr1 = self.date_edit1.date()
self.apply_filter()
def on_date2_changed(self):
self.date_ftr2 = self.date_edit2.date()
self.apply_filter()
def on_bins_changed(self):
self.bins = self.bins_sb.value()
self.apply_filter()
def get_data(self):
sql = f"SELECT {self.x_name}, {self.y_name} FROM PATIENTS WHERE {self.filter} ORDER BY {self.x_name}"
self.data_query_model.setQuery(sql, self.ctx.database.patient_db)
self.x_data = np.array([
self.data_query_model.record(n).value(self.x_name)
for n in range(self.data_query_model.rowCount())
])
if self.y_opt != 'Frequency':
self.y_data = np.array([
self.data_query_model.record(n).value(self.y_name)
for n in range(self.data_query_model.rowCount())
])
def apply_sex_filter(self):
if self.sex_ftr != 'All':
if self.filter:
self.filter += ' AND '
if self.sex_ftr == 'Unspecified':
self.filter += 'sex is NULL'
else:
self.filter += f'sex="{self.sex_ftr}"'
def apply_protocol_filter(self):
if self.protocol_ftr != 'All':
if self.filter:
self.filter += ' AND '
if self.protocol_ftr == 'Unspecified':
self.filter += 'protocol is NULL'
else:
self.filter += f'protocol="{self.protocol_ftr}"'
def apply_instn_filter(self):
if self.instn_ftr != 'All':
if self.filter:
self.filter += ' AND '
if self.instn_ftr == 'Unspecified':
self.filter += 'institution is NULL'
else:
self.filter += f'institution="{self.instn_ftr}"'
def apply_brand_filter(self):
if self.brand_ftr != 'All':
if self.filter:
self.filter += ' AND '
if self.brand_ftr == 'Unspecified':
self.filter += 'manufacturer is NULL'
else:
self.filter += f'manufacturer="{self.brand_ftr}"'
def apply_scanner_filter(self):
if self.scanner_ftr != 'All':
if self.filter:
self.filter += ' AND '
if self.scanner_ftr == 'Unspecified':
self.filter += 'model is NULL'
else:
self.filter += f'model="{self.scanner_ftr}"'
def apply_age_filter(self):
if self.age_ftr1 != -1 and self.age_ftr2 != -1:
if self.age_ftr1 <= self.age_ftr2:
lo_age = self.age_ftr1
hi_age = self.age_ftr2
else:
lo_age = self.age_ftr2
hi_age = self.age_ftr1
if self.filter:
self.filter += ' AND '
self.filter += f'age BETWEEN {lo_age} and {hi_age}'
def apply_date_filter(self):
if self.filter:
self.filter += ' AND '
if self.date_ftr1.toString('yyyyMMdd') <= self.date_ftr2.toString(
'yyyyMMdd'):
lo_date = self.date_ftr1.toString('yyyyMMdd')
hi_date = self.date_ftr2.toString('yyyyMMdd')
else:
lo_date = self.date_ftr2.toString('yyyyMMdd')
hi_date = self.date_ftr1.toString('yyyyMMdd')
self.filter += f'exam_date BETWEEN {lo_date} and {hi_date}'
def set_x_data(self):
if self.x_opt == 'Record ID':
self.x_name = 'id'
elif self.x_opt == 'Effective Dose':
self.x_name = 'effective_dose'
elif self.x_opt == 'Deff' or self.x_opt == 'Dw':
self.x_name = 'diameter'
if self.filter:
self.filter += ' AND '
self.filter += f'diameter_type="{self.x_opt}"'
else:
self.x_name = self.x_opt
def set_y_data(self):
if self.y_opt == 'Effective Dose':
self.y_name = 'effective_dose'
elif self.y_opt == 'Deff' or self.y_opt == 'Dw':
self.y_name = 'diameter'
if self.filter:
self.filter += ' AND '
self.filter += f'diameter_type="{self.y_opt}"'
elif self.y_opt == 'Frequency':
self.y_name = 'NULL'
else:
self.y_name = self.y_opt
if self.filter:
self.filter += ' AND '
if self.y_opt != 'Frequency':
self.filter += f'{self.x_name} is NOT NULL AND {self.y_name} is NOT NULL'
else:
self.filter += f'{self.x_name} is NOT NULL'
def apply_filter(self):
self.filter = ''
self.apply_sex_filter()
self.apply_protocol_filter()
self.apply_instn_filter()
self.apply_brand_filter()
self.apply_scanner_filter()
self.apply_age_filter()
self.apply_date_filter()
self.set_x_data()
self.set_y_data()
self.get_data()
self.data_count_lbl.setText(str(self.data_query_model.rowCount()))
def plot(self):
self.figure = PlotDialog()
self.figure.axes.addLegend(pen='w', brush=(64, 64, 64, 127))
self.figure.actionEnabled(True)
if self.y_opt == 'Frequency':
self.figure.trendActionEnabled(False)
self.figure.histogram(self.x_data,
bins=self.bins,
name=f'{self.y_opt}',
fillLevel=0,
brush=(0, 0, 255, 150),
symbol='o',
symbolSize=5)
else:
self.figure.plot(self.x_data,
self.y_data,
name='data points',
pen=None,
symbol='o',
symbolSize=8,
symbolPen='k',
symbolBrush=(255, 255, 0, 255))
self.figure.axes.showGrid(True, True)
self.figure.setLabels(self.x_opt, self.y_opt, self.x_unit, self.y_unit)
self.figure.setTitle(f'{self.x_opt} - {self.y_opt}')
self.figure.show()
def on_generate(self):
print(self.filter)
isempty = lambda arr: arr.size == 0
if isempty(self.x_data) or isempty(self.y_data):
QMessageBox.information(
None, "No Data",
"Matching data not found.\nPlease try to reduce the filter.")
return
self.plot()
def reset_fields(self):
self.set_filter()
self.bins_sb.setValue(20)
self.x_cb.setCurrentIndex(0)
self.y_cb.setCurrentIndex(0)
self.on_bins_changed()
self.on_x_changed(self.x_cb.currentText)
self.on_y_changed(self.x_cb.currentText)
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class MainWindow(QWidget):
def __init__(self, token: str):
connection = sqlite3.connect("users.sqlite")
query = """
SELECT
privileges.privilege
FROM
privileges
JOIN users ON privileges.username = users.username
WHERE
users.username = ?;
"""
self.privileges = set(
_[0] for _ in connection.execute(query, (token, )).fetchall())
connection.close()
super().__init__()
self.setWindowTitle("CSM")
tabs = QTabWidget()
if "SELECT" in self.privileges:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("data.sqlite")
db.open()
if any(privilege in self.privileges
for privilege in ("UPDATE", "INSERT", "DELETE")):
self.people_model = QSqlTableModel()
self.people_model.setTable("people")
if "UPDATE" in self.privileges:
self.people_model.setEditStrategy(
QSqlTableModel.OnFieldChange)
self.people_model.select()
self.addresses_model = QSqlTableModel()
self.addresses_model.setTable("addresses")
if "UPDATE" in self.privileges:
self.addresses_model.setEditStrategy(
QSqlTableModel.OnFieldChange)
self.addresses_model.select()
else:
self.people_model = QSqlQueryModel()
self.people_model.setQuery("SELECT * FROM people;")
self.addresses_model = QSqlQueryModel()
self.addresses_model.setQuery("SELECT * FROM addresses;")
self.people_model.setHeaderData(1, Qt.Horizontal, "Full name")
self.people_model.setHeaderData(2, Qt.Horizontal, "Telephone")
self.addresses_model.setHeaderData(1, Qt.Horizontal, "Person id")
self.addresses_model.setHeaderData(2, Qt.Horizontal, "Street")
self.addresses_model.setHeaderData(3, Qt.Horizontal, "City")
self.addresses_model.setHeaderData(4, Qt.Horizontal, "State")
self.people_table = QTableView()
self.people_table.setModel(self.people_model)
if "UPDATE" not in self.privileges:
self.people_table.setEditTriggers(
QAbstractItemView.NoEditTriggers)
self.people_table.horizontalHeader().setSectionResizeMode(
0, QHeaderView.ResizeToContents)
self.people_table.horizontalHeader().setSectionResizeMode(
1, QHeaderView.Stretch)
self.people_table.horizontalHeader().setSectionResizeMode(
2, QHeaderView.Stretch)
self.people_table.setSizeAdjustPolicy(
QAbstractScrollArea.AdjustToContents)
self.people_table.resizeColumnsToContents()
self.addresses_table = QTableView()
self.addresses_table.setModel(self.addresses_model)
if "UPDATE" not in self.privileges:
self.addresses_table.setEditTriggers(
QAbstractItemView.NoEditTriggers)
self.addresses_table.hideColumn(0)
self.addresses_table.horizontalHeader().setSectionResizeMode(
1, QHeaderView.ResizeToContents)
self.addresses_table.horizontalHeader().setSectionResizeMode(
2, QHeaderView.Stretch)
self.addresses_table.horizontalHeader().setSectionResizeMode(
3, QHeaderView.Stretch)
self.addresses_table.horizontalHeader().setSectionResizeMode(
4, QHeaderView.Stretch)
self.addresses_table.setSizeAdjustPolicy(
QAbstractScrollArea.AdjustToContents)
self.addresses_table.resizeColumnsToContents()
self.people_table.setMinimumWidth(self.people_table.width())
self.addresses_table.setMinimumWidth(self.addresses_table.width())
tabs.addTab(self.people_table, "People")
tabs.addTab(self.addresses_table, "Addresses")
tabs.currentChanged.connect(self.tab_switch_handler)
HLayout = QHBoxLayout(self)
HLayout.addWidget(tabs)
self.VLayout = QVBoxLayout()
self.VLayout.setAlignment(Qt.AlignTop)
token_label = QLabel(token)
self.VLayout.addWidget(token_label)
if "INSERT" in self.privileges:
self.insert_stack = QStackedWidget()
self.people_insert = people_InsertWidget()
self.people_insert.submit_button.clicked.connect(
self.insert_people_handler)
self.addresses_insert = addresses_InsertWidget()
self.addresses_insert.submit_button.clicked.connect(
self.insert_address_handler)
self.insert_stack.addWidget(self.people_insert)
self.insert_stack.addWidget(self.addresses_insert)
self.insert_stack.setCurrentWidget(self.people_insert)
self.insert_stack.setFixedSize(self.insert_stack.sizeHint())
self.VLayout.addWidget(self.insert_stack)
self.VLayout.addSpacing(10)
if "DELETE" in self.privileges:
self.delete_stack = QStackedWidget()
self.people_delete = QPushButton("DELETE FROM people")
self.people_delete.clicked.connect(self.delete_people_handler)
self.address_delete = QPushButton("DELETE FROM addresses")
self.address_delete.clicked.connect(self.delete_address_handler)
self.delete_stack.addWidget(self.people_delete)
self.delete_stack.addWidget(self.address_delete)
self.delete_stack.setCurrentWidget(self.people_delete)
self.delete_stack.setFixedSize(self.delete_stack.sizeHint())
self.VLayout.addWidget(self.delete_stack)
HLayout.addLayout(self.VLayout)
#self.resize(self.sizeHint())
def tab_switch_handler(self, tab: int):
if tab == 0:
if "INSERT" in self.privileges:
self.insert_stack.setCurrentWidget(self.people_insert)
if "DELETE" in self.privileges:
self.delete_stack.setCurrentWidget(self.people_delete)
elif tab == 1:
if "INSERT" in self.privileges:
self.insert_stack.setCurrentWidget(self.addresses_insert)
if "DELETE" in self.privileges:
self.delete_stack.setCurrentWidget(self.address_delete)
else:
QMessageBox.warning(self, "Error", "Something went wrong!")
def insert_people_handler(self):
full_name = self.people_insert.full_name.text()
telephone = self.people_insert.telephone.text()
record = self.people_model.record()
record.setGenerated("id", True)
record.setValue("full_name", full_name)
record.setValue("telephone", telephone)
if self.people_model.insertRecord(-1, record):
self.people_model.select()
else:
QMessageBox.warning(
self, "Error",
"Cannot insert {} {}".format(full_name, telephone))
def insert_address_handler(self):
user_id = int(self.addresses_insert.user_id.text())
street = self.addresses_insert.street.text()
city = self.addresses_insert.city.text()
state = self.addresses_insert.state.text()
record = self.addresses_model.record()
record.setGenerated("id", True)
record.setValue("user_id", user_id)
record.setValue("street", street)
record.setValue("city", city)
record.setValue("state", state)
if self.addresses_model.insertRecord(-1, record):
self.addresses_model.select()
else:
QMessageBox.warning(
self, "Error",
"Cannot insert {} {} {} {}".format(user_id, street, city,
state))
def delete_people_handler(self):
rows = self.people_table.selectionModel().selectedRows()
if len(rows) == 1:
if not self.people_model.removeRow(rows[0].row()):
QMessageBox.warning(
self, "Error",
"Cannot delete row {}".format(rows[0].row() + 1))
else:
self.people_model.select()
else:
QMessageBox.warning(self, "Error",
"Only 1 row can be deleted at a time")
def delete_address_handler(self):
rows = self.addresses_table.selectionModel().selectedRows()
if len(rows) == 1:
if not self.addresses_model.removeRow(rows[0].row()):
QMessageBox.warning(
self, "Error",
"Cannot delete row {}".format(rows[0].row() + 1))
else:
self.addresses_model.select()
else:
QMessageBox.warning(self, "Error",
"Only 1 row can be deleted at a time")
class DBViewer(QDialog):
resized = pyqtSignal(object)
def __init__(self, ctx, par, *args, **kwargs):
super(DBViewer, self).__init__(*args, **kwargs)
self.setAttribute(Qt.WA_DeleteOnClose)
self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint
| Qt.WindowMinMaxButtonsHint)
self.ctx = ctx
self.par = par
self.layout = QVBoxLayout()
self.query_model = QSqlQueryModel()
self.toolbar = QToolBar()
self.table_view = QTableView()
self.table_view.setSelectionBehavior(QAbstractItemView.SelectRows)
self.export_excel = QPushButton(
self.ctx.export_icon,
'Export to Excel',
)
self.refresh_btn = QPushButton("Refresh")
self.close_btn = QPushButton("Close")
self.delete_rows_btn = QPushButton("Delete Selected Row(s)")
self.delete_rows_btn.setEnabled(False)
self.initModel()
self.initUI()
self.sigConnect()
def initUI(self):
self.layout.setContentsMargins(11, 0, 11, 11)
self.toolbar.addWidget(self.export_excel)
self.layout.addWidget(self.toolbar)
self.close_btn.setAutoDefault(True)
self.close_btn.setDefault(True)
self.refresh_btn.setAutoDefault(False)
self.refresh_btn.setDefault(False)
self.delete_rows_btn.setAutoDefault(False)
self.delete_rows_btn.setDefault(False)
self.export_excel.setAutoDefault(False)
self.export_excel.setDefault(False)
self.table_view.resizeColumnsToContents()
self.layout.addWidget(self.table_view)
hLayout = QHBoxLayout()
hLayout.addWidget(self.delete_rows_btn)
hLayout.addStretch()
hLayout.addWidget(self.refresh_btn)
hLayout.addWidget(self.close_btn)
self.layout.addLayout(hLayout)
self.setLayout(self.layout)
self.setWindowTitle("Patients Record")
wds = [
self.table_view.columnWidth(c)
for c in range(self.table_view.model().columnCount())
]
self.resize(sum(wds) + 40, 600)
rect = self.frameGeometry()
rect.moveCenter(QDesktopWidget().availableGeometry().center())
self.move(rect.topLeft().x(), rect.topLeft().y())
def sigConnect(self):
self.close_btn.clicked.connect(self.accept)
self.refresh_btn.clicked.connect(self.on_refresh)
self.delete_rows_btn.clicked.connect(self.on_delete_rows)
self.export_excel.clicked.connect(self.on_export)
self.resized.connect(self.on_window_resize)
self.table_view.horizontalHeader().sectionResized.connect(
self.on_column_resize)
def initModel(self):
self.table_model = QSqlTableModel(db=self.ctx.database.patient_db)
self.table_model.setTable('patients')
self.table_model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.table_model.select()
self.table_view.setModel(self.table_model)
self.table_view.selectionModel().selectionChanged.connect(
self.on_rows_selected)
def on_export(self):
filename, _ = QFileDialog.getSaveFileName(self, "Export to Excel", "",
"Excel Workbook (*.xlsx)")
if not filename:
return
workbook = Workbook(filename)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
sql = "SELECT * FROM PATIENTS"
self.query_model.setQuery(sql, self.ctx.database.patient_db)
for row in range(self.query_model.rowCount() + 1):
for col in range(self.query_model.record(row).count()):
if row == 0:
worksheet.write(row, col,
self.query_model.record().fieldName(col),
bold)
worksheet.write(row + 1, col,
self.query_model.record(row).value(col))
workbook.close()
QMessageBox.information(self, "Success",
"Records can be found in " + filename + " .")
def on_refresh(self):
self.initModel()
def on_column_resize(self, id, oldsize, size):
width = self.size().width()
self.column_ratio[id] = size / width
def on_window_resize(self, event):
old_width = event.oldSize().width()
width = event.size().width()
if old_width == -1:
self.column_ratio = [
self.table_view.columnWidth(c) / width
for c in range(self.table_view.model().columnCount())
]
else:
self.table_view.horizontalHeader().sectionResized.disconnect(
self.on_column_resize)
[
self.table_view.setColumnWidth(c, r * width)
for c, r in enumerate(self.column_ratio)
]
self.table_view.horizontalHeader().sectionResized.connect(
self.on_column_resize)
def on_rows_selected(self):
self.selected_rows = sorted(
set(index.row() for index in self.table_view.selectedIndexes()))
print(self.selected_rows)
self.delete_rows_btn.setEnabled(len(self.selected_rows) != 0)
def on_delete_rows(self):
result = []
for row in self.selected_rows:
res = self.table_model.removeRow(row)
result.append(res)
if not all(result):
print(self.table_model.lastError())
self.ctx.records_count -= len(self.selected_rows)
self.delete_rows_btn.setEnabled(False)
self.on_refresh()
self.par.info_panel.no_edit.setText(str(self.ctx.records_count + 1))
def resizeEvent(self, event):
self.resized.emit(event)
return super(DBViewer, self).resizeEvent(event)
class OrderController(QObject):
def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow):
super().__init__()
self.form = form
self.orderTable = orderTable
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.mainwindow = mainwindow
form.currentRecordChanged.connect(self.recordChanged)
form.recordInserted.connect(lambda: self.recordChanged(None))
form.recordDeleted.connect(lambda: self.recordChanged(None))
if form.only_select:
self.addButton.setEnabled(False)
self.editButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def _hiddingHack(self, val):
ui = self.mainwindow.ui
if val is True:
ui.client_hack.setCurrentWidget(ui.client_hide_page)
ui.shop_hack.setCurrentWidget(ui.shop_hide_page)
ui.emp_hack.setCurrentWidget(ui.emp_hide_page)
else:
ui.client_hack.setCurrentWidget(ui.client_ok_page)
ui.shop_hack.setCurrentWidget(ui.shop_ok_page)
ui.emp_hack.setCurrentWidget(ui.emp_ok_page)
def tableSelectionChanged(self, cur, prev):
if self.form.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def selectRow(self, row):
self.form.selectRow(row)
def update(self):
self.form.update()
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.tableView)
# tableView显示属性设置
## self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
## self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): #查询数据
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province,
Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.__getFieldNames() #获取字段名和序号
##字段显示名
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal,"工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal,"姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal,"性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal,"出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal,"省份")
## self.qryModel.setHeaderData(self.fldNum["Department"],Qt.Horizontal,"部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal,"工资")
self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型
##选择行变化时
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actRecEdit.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.actTestSQL.setEnabled(True)
def __updateRecord(self, recNo): ##更新一条记录
curRec = self.qryModel.record(recNo) #获取当前记录
empNo = curRec.value("EmpNo") #获取EmpNo
query = QSqlQuery(self.DB) #查询出当前记录的所有字段
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec() #
query.first()
if (not query.isValid()): #是否为有效记录
return
curRec = query.record() #获取当前记录的数据，QSqlRecord类型
dlgData = QmyDialogData(self) #创建对话框
dlgData.setUpdateRecord(curRec) #调用对话框函数更新数据和界面
ret = dlgData.exec() # 以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData() #获得对话框返回的记录
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
## if (not query.exec_()):
if (not query.exec()): #PyQt 5.11.2以前应该使用exec_()函数
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView显示
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##打开数据库
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #查询数据
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##插入记录
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo =-1") #实际不查询出记录，只查询字段信息
curRec = query.record() #获取当前记录,实际为空记录,但有字段信息
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec) #插入记录
ret = dlgData.exec() #以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec() #执行SQL语句
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##删除记录
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo) #获取当前记录
if (curRec.isEmpty()): #当前为空记录
return
empNo = curRec.value("EmpNo") #获取员工编号
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##编辑记录
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
## @pyqtSlot() ##双击编辑记录
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot() ##遍历记录，涨工资
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB) #员工工资信息列表
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB) #临时 QSqlQuery
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()): #当前记录有效
empID = qryEmpList.value("empNo") #获取empNo
salary = qryEmpList.value("Salary") #获取Salary
salary = salary + 500 #涨工资
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary) #设置SQL语句参数
qryUpdate.exec() #执行update语句
if not qryEmpList.next(): #移动到下一条记录，并判断是否到末尾了
break
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView的显示
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot() ##SQL语句测试
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
## # SQL语句测试1， exec_() 和exec()都可以直接执行不带参数的SQL语句
## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''')
## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''')
# SQL语句测试2，执行带参数的SQL语句，只能用 exec_()，不能用exec()
## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''')
## query.bindValue(":Gender","男")
## query.exec()
query.exec('''UPDATE employee SET Salary=500+Salary ''')
## query.bindValue(":Gender","男")
## query.exec()
## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?")
## query.bindValue(0, "技术部")
## query.bindValue(1, 5500)
## query.bindValue(2, "张三")
## query.exec_() #只能用exec_()，而不能用exec()函数
## self.qryModel.query().exec() #不增减记录时更新显示
## 增减记录后的更新显示
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
print("SQL OK")
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##行切换时触发
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
self.ui.statusBar.showMessage("当前记录：工号=%d" % empNo)
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.splitter)
# tableView显示属性设置
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): ##查询数据
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province,
Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数：%d" % self.qryModel.rowCount())
self.__getFieldNames() #获取字段名和序号
##设置字段显示名，直接使用序号
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份")
## self.qryModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
##创建界面组件与数据模型的字段之间的数据映射
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel) #设置数据模型
## self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
##界面组件与qryModel的具体字段之间的联系
## self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"])
## self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
## self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
## self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"])
## self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"] )
## self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"] )
## self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"] )
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst() #移动到首记录
self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型
self.selModel.currentRowChanged.connect(
self.do_currentRowChanged) #选择行变化时
self.ui.tableView.setModel(self.qryModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self): ##刷新tableView显示
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1) #QModelIndex
self.selModel.clearSelection() #清空选择项
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##“打开数据库”按钮
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #打开数据表
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##首记录
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot() ##前一记录
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot() ##后一条记录
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot() ##最后一条记录
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##记录移动时触发
if (current.isValid() == False):
self.ui.dbLabPhoto.clear() #清除图片显示
return
self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号
first = (current.row() == 0) #是否首记录
last = (current.row() == self.qryModel.rowCount() - 1) #是否尾记录
self.ui.actRecFirst.setEnabled(not first) #更新使能状态
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
## if not query.exec_(): #注意，在PyQt5.11.2之前的版本里只能使用exec_()函数
if not query.exec(
): #注意，在PyQt5.11.2添加了遗漏的overload型exec()函数，在PyQt5.11.2里没问题了
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None): #图片字段内容为空
self.ui.dbLabPhoto.clear()
else: #显示照片
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo") #显示备注
self.ui.dbEditMemo.setPlainText(memoData)
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.splitter)
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误，错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount())
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel)
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst()
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
def __refreshTableView(self):
index = self.mapper.currentIndex()
curIndex = self.qryModel.index(index, 1)
self.selModel.clearSelection()
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
@pyqtSlot()
def on_actRecFirst_triggered(self):
self.mapper.toFirst()
self.__refreshTableView()
@pyqtSlot()
def on_actRecPrevious_triggered(self):
self.mapper.toPrevious()
self.__refreshTableView()
@pyqtSlot()
def on_actRecNext_triggered(self):
self.mapper.toNext()
self.__refreshTableView()
@pyqtSlot()
def on_actRecLast_triggered(self):
self.mapper.toLast()
self.__refreshTableView()
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
self.ui.dbLabPhoto.clear()
return
self.mapper.setCurrentIndex(current.row())
first = (current.row() == 0)
last = (current.row() == self.qryModel.rowCount() - 1)
self.ui.actRecFirst.setEnabled(not first)
self.ui.actRecPrevious.setEnabled(not first)
self.ui.actRecNext.setEnabled(not last)
self.ui.actRecLast.setEnabled(not last)
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare(
'''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''')
query.bindValue(":ID", empNo)
if not query.exec():
QMessageBox.critical(self, "错误",
"执行SQL语句错误\n" + query.lastError().text())
return
else:
query.first()
picData = query.value("Photo")
if (picData == None):
self.ui.dbLabPhoto.clear()
else:
pic = QPixmap()
pic.loadFromData(picData)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
memoData = query.value("Memo")
self.ui.dbEditMemo.setPlainText(memoData)
def selectOnlyAttribute(table, attribute, value, return_attribute):
query = QSqlQueryModel()
query.setQuery('SELECT %s FROM %s WHERE %s = %s' %
(return_attribute, table, attribute, value))
return query.record(0).value(0)
class Database(QTableView):
def __init__(self, window):
super().__init__()
self.window = window
self.verticalLayout = QVBoxLayout()
self.verticalLayout.addWidget(self)
self.setStyleSheet("font-family: arial; background-color: #F8F8FF;")
self.showButton = QPushButton("SHOW")
self.showButton.setStyleSheet("""
QPushButton {
background-color: #A8DBC5;
border-style: outset;
border-width: 2px;
font-family: arial;
font-weight: bold;
font-size: 12px;
border-color: white;
}
QPushButton:hover {
background-color: #E6E6FA;
}
QPushButton:pressed {
border-style: inset;
border-color: gray;
}
""")
self.verticalLayout.addWidget(self.showButton)
def checkingConnection(self):
"""This function aims to check the database connection name
and use it if exists"""
if QSqlDatabase.contains():
self.db = QSqlDatabase.database()
self.db.setDatabaseName('database.sqlite')
self.db.open()
else:
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName('database.sqlite')
self.db.open()
def connectDatabase(self):
"""This function displays the Main Database with a SQL Query"""
self.checkingConnection()
self.model = QSqlQueryModel()
self.model.setQuery(
'''
SELECT Clients.id, Clients.date, Clients.hour, Clients.name,
Clients.birthday, Clients.cellphone, Clients.address, City.name,
Payment.method, Clients.pollo, Clients.carne, Clients.empanachos,
Clients.total, Clients.value FROM Clients JOIN City JOIN Payment
ON Clients.city_id = City.id AND Clients.payment_id = Payment.id
''', self.db)
self.setModel(self.model)
def income(self):
"""This function displays the Daily Income results table on its
window tab"""
self.checkingConnection()
model = QSqlQueryModel()
model.setQuery(
'''
SELECT Clients.id, Clients.date, Clients.hour, Clients.name,
(Clients.carne + Clients.pollo) AS empanadas,
Clients.total, Clients.value FROM Clients''', self.db)
self.setModel(model)
def resultadosDiarios(self):
"""This function displays the Daily Results table on its
window tab"""
self.checkingConnection()
self.model = QSqlQueryModel()
self.model.setQuery(
'''SELECT date1, ingresos, compras, gastos,
(ingresos - compras - gastos) AS Saldo FROM (SELECT date1,
ingresos, compras, gastos FROM ((SELECT Clients.date AS date1,
SUM(Clients.value) AS ingresos FROM Clients GROUP BY Clients.date)
JOIN (SELECT Compras.date AS date2, SUM(Compras.value) AS compras
FROM Compras GROUP BY Compras.date) JOIN (SELECT Gastos.date AS date3,
SUM(Gastos.value) AS gastos FROM Gastos GROUP BY Gastos.date)
ON date1 = date2 AND date2 = date3))''', self.db)
self.setModel(self.model)
def resultadosMensuales(self):
"""This function displays the Monthly Results table on its
window tab and also gets the table values and save them
in a series of lists"""
self.checkingConnection()
self.model = QSqlQueryModel()
self.model.setQuery(
'''
SELECT months.name, ingresos, compras, gastos,
(ingresos - compras - gastos) AS Saldo FROM (
SELECT month,
ingresos, compras, gastos FROM ((SELECT Clients.month AS month,
SUM(Clients.value) AS ingresos FROM Clients GROUP BY Clients.month)
JOIN (SELECT Compras.month_id AS month2, SUM(Compras.value) AS compras
FROM Compras GROUP BY Compras.month_id) JOIN (SELECT Gastos.month_id AS month3,
SUM(Gastos.value) AS gastos FROM Gastos GROUP BY Gastos.month_id)
ON month = month2 AND month2 = month3)
) JOIN months ON month=months.id ''', self.db)
# Set the empty lists
self.months = []
self.ingresos = []
self.compras = []
self.gastos = []
self.total = []
# Save the Query values in each list
for i in range(self.model.rowCount()):
# record is the row and value the column
self.months.append(self.model.record(i).value("name"))
self.ingresos.append(self.model.record(i).value("ingresos"))
self.compras.append(self.model.record(i).value("compras"))
self.gastos.append(self.model.record(i).value("gastos"))
self.total.append(self.model.record(i).value("Saldo"))
self.setModel(self.model)
# Creating the Bar Graph
self.grafica(self.months)
def grafica(self, timeList):
"""This function displays the Results Graph
in a bar graph with matplotlib and use the
timeList second parameter as the required
timeline in order to set the X axis"""
n_groups = len(timeList)
# create plot
fig, ax = plt.subplots()
index = np.arange(n_groups)
bar_width = 0.2
opacity = 1
index2 = [x + bar_width for x in index]
index3 = [x + bar_width for x in index2]
index4 = [x + bar_width for x in index3]
rects1 = plt.bar(index,
self.ingresos,
bar_width,
alpha=opacity,
color='r',
label='Ingresos')
rects2 = plt.bar(index2,
self.compras,
bar_width,
alpha=opacity,
color='yellow',
label='Compras')
rects3 = plt.bar(index3,
self.gastos,
bar_width,
alpha=opacity,
color='b',
label='Gastos')
rects4 = plt.bar(index4,
self.total,
bar_width,
alpha=opacity,
color='black',
label='Saldo')
plt.xlabel('Línea de tiempo')
plt.ylabel('Total ($)')
plt.title('Resultados')
plt.xticks(index + bar_width, timeList)
plt.grid()
plt.legend()
plt.tight_layout()
plt.show()
def resultadosAnuales(self):
"""This function displays the Annual Results table on its
window tab"""
self.checkingConnection()
self.model = QSqlQueryModel()
self.model.setQuery(
'''SELECT years, ingresos, compras, gastos,
(ingresos - compras - gastos) AS Total FROM (
SELECT years,
ingresos, compras, gastos FROM ((SELECT Clients.year AS years,
SUM(Clients.value) AS ingresos FROM Clients GROUP BY Clients.year)
JOIN (SELECT Compras.year AS year2, SUM(Compras.value) AS compras
FROM Compras GROUP BY Compras.year) JOIN (SELECT Gastos.year AS year3,
SUM(Gastos.value) AS gastos FROM Gastos GROUP BY Gastos.year)
ON years = year2 AND year2 = year3)
) ''', self.db)
# Getting the table values
self.years = []
self.ingresos = []
self.compras = []
self.gastos = []
self.total = []
# Save the Query values in each list
for i in range(self.model.rowCount()):
# record is the row and value the column
self.years.append(self.model.record(i).value("years"))
self.ingresos.append(self.model.record(i).value("ingresos"))
self.compras.append(self.model.record(i).value("compras"))
self.gastos.append(self.model.record(i).value("gastos"))
self.total.append(self.model.record(i).value("Total"))
self.setModel(self.model)
# Creating the Bar Graph
self.grafica(self.years)
