def remove_rule(self, rule):
query = QSqlQueryModel()
query.setQuery("delete from rules "\
"where id={}"\
.format(rule.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def update_dsc(self, thing, dsc):
query = QSqlQueryModel()
query.setQuery("UPDATE things "\
"SET dsc = '{}' "\
"WHERE id={}"
.format(dsc, thing.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def insert_rule(self, name, dsc, things):
query = QSqlQueryModel()
t_ids = sorted([x.idx for x in things])
query.setQuery("insert into rules(name, things, dsc) "\
"values ('{}', {}, '{}')"\
.format(name,
_array_string(t_ids),
dsc))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def remove_thing(self, thing):
rules = self.rules_with_thing(thing)
for rule in rules:
self.remove_rule(rule)
query = QSqlQueryModel()
query.setQuery("delete from things "\
"where id={}"\
.format(thing.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
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 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 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
def executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(model.query().numRowsAffected())
)
self.table.resizeColumnsToContents()
self.updateActions()
def executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(
QSqlQuery(self.sqlEdit.toPlainText(),
self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(
model.query().numRowsAffected()))
self.table.resizeColumnsToContents()
self.updateActions()
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 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 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 Window(QMainWindow, Ui_MainWindow):
def __init__(self):
super().__init__()
self.setupUi(self)
self.model = QSqlQueryModel()
self.connectToSql()
# Обработка действий по меню
# нажата кнопка меню "Справочники -> Подразделения"
self.unit_action.triggered.connect(self.unit_dialog)
# нажата кнопка меню "Справочники -> Сотрудники"
self.user_action.triggered.connect(self.user_dialog)
# нажата кнопка меню "Справочники -> Сервисные службы"
self.services_action.triggered.connect(self.services_dialog)
# Работа с основной формой
# Нажата кнопка "Добавить строку" на форме
self.btProblemAdd.clicked.connect(self.bt_add)
# Нажата кнопка "Обновить строку" на форме
self.btProblemUpdate.clicked.connect(self.bt_upd)
# Нажата кнопка "Удалить строку" на форме
self.btProblemDel.clicked.connect(self.bt_del)
# Найти заявку
self.btProblemFind.clicked.connect(self.findText)
# Сбросить фильтр
self.btProblemReset.clicked.connect(self.reset)
def connectToSql(self):
"""
Открываем базу и создаем запрос и соединяем модель с таблицей
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.model.setHeaderData(0, Qt.Horizontal, '№ заявки')
self.model.setHeaderData(1, Qt.Horizontal, 'Дата')
self.model.setHeaderData(2, Qt.Horizontal, 'Служба')
self.model.setHeaderData(3, Qt.Horizontal, 'Текст заявки')
self.model.setHeaderData(4, Qt.Horizontal, 'ФИО заявителя')
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def bt_add(self):
"""
Вызывается по нажатию кнопки "Добавить строку"
"""
dialog_add = Ed_win(self)
# Устанавливаем пустые значения в поля на форме
dialog_add.edit_text.setText = ''
dialog_add.cb_ser_name.setCurrentIndex(-1)
dialog_add.cb_fio.setCurrentIndex(-1)
# открываем окно добавления
dialog_add.show()
# Если нажата кнопка ОК на форме добавления
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""INSERT INTO def_message(idServices, text,idUser)
VALUES(:idService, :text,:idUser)""")
qry1.bindValue(":idService", servId)
qry1.bindValue(":text", dialog_add.edit_text.text())
qry1.bindValue(":idUser", userId)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO
FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_upd(self):
"""
Вызывается по нажатию кнопки "Изменить строку"
"""
dialog_add = Ed_win(self)
# определяем индекс строки в таблице
index = self.tableView.currentIndex().row()
print(index)
if index == -1:
QMessageBox.information(self, 'Ошибка', 'Строка не выбрана')
else:
# определяем id строки в таблице в базе данных
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
# устанавливаем текст заявки в поле edit_text
dialog_add.edit_text.setText(
str(self.model.data(self.model.index(index, 3),
Qt.DisplayRole)))
# Данные по сервисной службе
index_serv = dialog_add.cb_ser_name.findText(
str(self.model.data(self.model.index(index, 2),
Qt.DisplayRole)))
if index_serv > -1:
dialog_add.cb_ser_name.setCurrentIndex(index_serv)
else:
dialog_add.cb_ser_name.setCurrentIndex(-1)
# Данные по сотруднику, который подал заявку
index_fio = dialog_add.cb_fio.findText(
str(self.model.data(self.model.index(index, 4),
Qt.DisplayRole)))
if index_fio > -1:
dialog_add.cb_fio.setCurrentIndex(index_fio)
else:
dialog_add.cb_fio.setCurrentIndex(-1)
# ОТкрываем окно с данными для редактирования
dialog_add.show()
# если нажата кнопка ОК в окне для редактирования строки
if dialog_add.exec() == QDialog.Accepted:
ser = dialog_add.cb_ser_name.currentText()
user = dialog_add.cb_fio.currentText()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""SELECT s.id FROM services s
where s.name =:ser""")
qry1.bindValue(":ser", ser)
qry1.exec()
qry1.next()
servId = qry1.value(0)
qry1.prepare("""SELECT u.id FROM User u
where u.FIO =:user""")
qry1.bindValue(":user", user)
qry1.exec()
qry1.next()
userId = qry1.value(0)
qry1.prepare(
"""UPDATE def_message SET idServices=:servId, text=:txt,idUser=:userId WHERE id=:id"""
)
qry1.bindValue(":servId", servId)
qry1.bindValue(":txt", dialog_add.edit_text.text())
qry1.bindValue(":userId", userId)
qry1.bindValue(":id", nom)
qry1.exec()
# снова делаем выборку данных после проведения обновления данных
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id""")
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def bt_del(self):
"""
Вызывается по нажатию кнопки "Удалить строку"
"""
quit_msg = "Подтвердите удаление записи"
reply = QMessageBox.question(self, 'Confirm', quit_msg,
QMessageBox.Yes, QMessageBox.No)
if reply == QMessageBox.Yes:
index = self.tableView.currentIndex().row()
nom = str(
self.model.data(self.model.index(index, 0), Qt.DisplayRole))
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry1 = QSqlQuery(self.db)
qry1.prepare("""DELETE FROM def_message
where id =:id""")
qry1.bindValue(":id", nom)
qry1.exec()
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id"""
)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error',
self.model.lastError().text())
def findText(self):
"""
Вызывается по кнопке "Найти заявку"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id where d.text Like :txt """)
self.qry.bindValue(":txt", '%' + self.EditFind.text() + '%')
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def reset(self):
"""
Вызывается по кнопке "Сбросить фильтр"
"""
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
self.qry = QSqlQuery(self.db)
self.qry.prepare(
"""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d
Left Join services s
on d.idServices = s.id
left join User u
on d.idUser = u.id """)
self.qry.exec()
self.model.setQuery(self.qry)
self.tableView.setModel(self.model)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
def unit_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Подразделения"
:return: Открывает окно с таблицей информации по подразделениям (Unit_win)
"""
dialog_unit = Unit_win(self)
dialog_unit.show()
# Скрываем основное окно программы
wnd.hide()
def user_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сотрудники"
:return: Открывает окно с таблицей информации по отрудникам (User_win)
"""
dialog_user = User_win(self)
dialog_user.show()
# Скрываем основное окно программы
wnd.hide()
def services_dialog(self):
"""
Функция выполняется при нажатии кнопки меню "Справочники -> Сервисные службы"
:return: Открывает окно с таблицей информации по сервисным службам (Services_win)
"""
dialog = Services_win(self)
dialog.show()
# Скрываем основное окно программы
wnd.hide()
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)
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 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 Filtrage_peche_dialog(QDialog, Ui_dlgPecheRechercheForm):
'''
Class de la fenêtre permettant le filtrage attributaire des inventaires de reproduction
:param QDialog: Permet d'afficher l'interface graphique comme une fenêtre indépendante
:type QDialog: QDialog
:param Ui_dlgPecheRechercheForm: Class du script de l'interface graphique du formulaire,
apporte les éléments de l'interface
:type Ui_dlgPecheRechercheForm: class
'''
def __init__(self, db, dbType, dbSchema, modelPeche, 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 modelPeche: modèle pêche qui contient les données de la base de données
:type modelPeche: QSqlRelationalTableModel
:param parent: défini que cette fenêtre n'hérite pas d'autres widgets
:type parent: NoneType
'''
super(Filtrage_peche_dialog, self).__init__(parent)
self.db = db
self.dbType = dbType
self.dbSchema = dbSchema
self.modelPeche = modelPeche
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.btnCode.clicked.connect(self.ajoutCode)
self.btnId.clicked.connect(self.ajoutId)
self.btnPdpg.clicked.connect(self.ajoutPdpg)
self.btnDate.clicked.connect(self.ajoutDate)
self.btnRiviere.clicked.connect(self.ajoutRiviere)
self.btnAappma.clicked.connect(self.ajoutAappma)
self.btnMeau.clicked.connect(self.ajoutMeau)
self.btnMotif.clicked.connect(self.ajoutMotif)
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.aappmaBool = False
self.motifBool = False
self.pdpgBool = False
self.ceauBool = False
self.meauBool = False
self.anneeBool = False
self.wwhere = ""
self.modelPdpg = QSqlTableModel(self, self.db)
wrelation = "contexte_pdpg"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelPdpg.setTable(wrelation)
self.modelPdpg.setSort(2, Qt.AscendingOrder)
if (not self.modelPdpg.select()):
QMessageBox.critical(self, u"Remplissage du modèle PDPG",
self.modelPdpg.lastError().text(),
QMessageBox.Ok)
self.cmbPdpg.setModel(self.modelPdpg)
self.cmbPdpg.setModelColumn(self.modelPdpg.fieldIndex("pdpg_nom"))
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.modelMeau = QSqlQueryModel(self)
wrelation = "masse_eau"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.modelMeau.setQuery(
"select meau_code, meau_code || ' ; ' || meau_nom from " +
wrelation + " order by meau_code;", self.db)
if self.modelMeau.lastError().isValid():
QMessageBox.critical(self, u"Remplissage du modèle Masse d'eau",
self.modelMeau.lastError().text(),
QMessageBox.Ok)
self.cmbMeau.setModel(self.modelMeau)
self.cmbMeau.setModelColumn(1)
self.ModelMotif = QSqlTableModel(self, self.db)
wrelation = "motif_peche"
if self.dbType == "postgres":
wrelation = self.dbSchema + "." + wrelation
self.ModelMotif.setTable(wrelation)
self.ModelMotif.setSort(1, Qt.AscendingOrder)
if (not self.ModelMotif.select()):
QMessageBox.critical(self, u"Remplissage du modèle Motif",
self.ModelMotif.lastError().text(),
QMessageBox.Ok)
self.cmbMotif.setModel(self.ModelMotif)
self.cmbMotif.setModelColumn(self.ModelMotif.fieldIndex("mope_motif"))
def reject(self):
'''Ferme la fenêtre si clic sur le bouton annuler'''
QDialog.reject(self)
def raz(self):
'''Réinitialise toutes les variables de la fenêtre afin de recommencer une nouvelle requête'''
self.spnId.setValue(0)
self.wrq = ""
self.txtSql.setText("")
self.wwhere = ""
self.datePeche.setDate(QDate(2000, 1, 1))
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.btnCode.setEnabled(True)
self.btnId.setEnabled(True)
self.btnPdpg.setEnabled(True)
self.btnDate.setEnabled(True)
self.btnRiviere.setEnabled(True)
self.btnAappma.setEnabled(True)
self.btnMeau.setEnabled(True)
self.btnMotif.setEnabled(True)
self.aappmaBool = False
self.motifBool = False
self.pdpgBool = False
self.ceauBool = False
self.meauBool = False
self.anneeBool = False
def et(self):
'''Change l'état des boutons et ajoute "and" à la requête'''
self.btnEt.setEnabled(False)
self.btnOu.setEnabled(False)
self.btnCode.setEnabled(True)
self.btnId.setEnabled(True)
if self.aappmaBool == False:
self.btnAappma.setEnabled(True)
if self.motifBool == False:
self.btnMotif.setEnabled(True)
if self.pdpgBool == False:
self.btnPdpg.setEnabled(True)
if self.ceauBool == False:
self.btnRiviere.setEnabled(True)
if self.meauBool == False:
self.btnMeau.setEnabled(True)
if self.anneeBool == False:
self.btnDate.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.btnCode.setEnabled(True)
self.btnId.setEnabled(True)
self.btnPdpg.setEnabled(True)
self.btnDate.setEnabled(True)
self.btnRiviere.setEnabled(True)
self.btnAappma.setEnabled(True)
self.btnMeau.setEnabled(True)
self.btnMotif.setEnabled(True)
self.aappmaBool = False
self.motifBool = False
self.pdpgBool = False
self.ceauBool = False
self.meauBool = False
self.anneeBool = False
self.wwhere += " OR "
def ajoutCode(self):
'''Change l'état des boutons et ajoute un critère de code opération à la requête'''
self.btnOu.setEnabled(True)
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.wcode = self.leCodeOpe.text()
if self.leCodeOpe.text() != "":
if self.wcode != "":
self.wwhere += "opep_ope_code ilike '%" + self.wcode + "%'"
self.leCodeOpe.setText("")
self.leCodeOpe.setFocus()
def ajoutId(self):
'''Change l'état des boutons et ajoute un critère d'id à la requête'''
self.btnOu.setEnabled(True)
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.wid = self.spnId.value()
if self.spnId.value() != "":
if self.wid != "":
self.wwhere += "opep_id = '" + str(self.wid) + "'"
self.spnId.setValue(0)
self.spnId.setFocus()
def ajoutDate(self):
'''Change l'état des boutons et ajoute un critère de date à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.anneeBool = True
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.wopep_date = self.datePeche.date().toString("yyyy")
if self.wopep_date != "":
self.wwhere += "date_part('year', opep_date) = '" + self.wopep_date + "'"
def ajoutPdpg(self):
'''Change l'état des boutons et ajoute un critère de pdpg à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.pdpgBool = True
wfromOperation = "operation"
wfromStation = "station"
wfromPeche = "ope_peche_elec"
if self.dbType == "postgres":
self.wfromPdpg = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation
wrecord = self.cmbPdpg.model().record(self.cmbPdpg.currentIndex())
self.wsta_pdpg = wrecord.value(0)
if self.cmbPdpg.currentText() != "":
if self.wsta_pdpg != "":
self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromPdpg + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_pdpg_id = '" + str(
self.wsta_pdpg) + "')"
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.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.ceauBool = True
wfromOperation = "operation"
wfromStation = "station"
wfromPeche = "ope_peche_elec"
if self.dbType == "postgres":
self.wfromCeau = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation
wrecord = self.cmbRiviere.model().record(
self.cmbRiviere.currentIndex())
self.wsta_riviere = wrecord.value(0)
if self.cmbRiviere.currentText() != "":
if self.wsta_riviere != "":
self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromCeau + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_ceau_id = '" + str(
self.wsta_riviere) + "')"
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.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.aappmaBool = True
wfromOperation = "operation"
wfromStation = "station"
wfromPeche = "ope_peche_elec"
if self.dbType == "postgres":
self.wfromAappma = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation
wrecord = self.cmbAappma.model().record(self.cmbAappma.currentIndex())
self.wsta_aappma = wrecord.value(0)
if self.cmbAappma.currentText() != "":
if self.wsta_aappma != "":
self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromAappma + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_apma_id = '" + str(
self.wsta_aappma) + "')"
def ajoutMeau(self):
'''Change l'état des boutons et ajoute un critère de Masse d'eau à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.meauBool = True
wfromOperation = "operation"
wfromStation = "station"
wfromPeche = "ope_peche_elec"
if self.dbType == "postgres":
self.wfromMeau = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation
wrecord = self.cmbMeau.model().record(self.cmbMeau.currentIndex())
self.wsta_meau = wrecord.value(0)
if self.cmbMeau.currentText() != "":
if self.wsta_meau != "":
self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromMeau + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_meau_code = '" + str(
self.wsta_meau) + "')"
def ajoutMotif(self):
'''Change l'état des boutons et ajoute un critère de motif de pêche à la requête'''
self.btnEt.setEnabled(True)
self.btnOu.setEnabled(True)
self.btnCode.setEnabled(False)
self.btnId.setEnabled(False)
self.btnPdpg.setEnabled(False)
self.btnDate.setEnabled(False)
self.btnRiviere.setEnabled(False)
self.btnAappma.setEnabled(False)
self.btnMeau.setEnabled(False)
self.btnMotif.setEnabled(False)
self.motifBool = True
wrecord = self.cmbMotif.model().record(self.cmbMotif.currentIndex())
self.wopep_motif = wrecord.value(0)
if self.cmbMotif.currentText() != "":
if self.wopep_motif != "":
self.wwhere += "opep_mope_id = '" + str(self.wopep_motif) + "'"
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 = "ope_peche_elec"
if self.dbType == "postgres":
cfrom = self.dbSchema + "." + cfrom
# Construit la clause SELECT et ajoute la clause FROM à la requête
self.wrq = "SELECT DISTINCT opep_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 opep_id"
else:
self.wrq += " ORDER BY opep_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.modelPeche:
# Filtre le modèle des inventaires de reproduction et ferme la fenêtre
self.modelPeche.setFilter("opep_id in %s" % wparam)
self.modelPeche.select()
QDialog.accept(self)
else:
QMessageBox.information(
self, "Filtrage",
u"Aucun pêche électrique ne correspond aux critères ...",
QMessageBox.Ok)
else:
QMessageBox.critical(self, u"Erreur SQL",
query.lastError().text(), QMessageBox.Ok)
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()
