class NormaOdk(QWidget): def __init__(self, parent): super(QWidget, self).__init__(parent) self.skrot = QShortcut(QKeySequence(Qt.Key_Return), self) self.naglowki = { 'iddetale': 'ID', 'nr_detalu': 'Detal', 'maszyna': 'Maszyna', "ilosc_m": 'Ilość maszyn', 'ilosc_szt': 'Ilość sztuk na operację', 'nazwa_op': 'Nazwa operacji', 'nr_op': 'Nr operacji', 'tm': 'Czas Tm [s]', 'tp': 'Czas Tp [s]', 'tj': 'Czas Tj [h]', 'norma': 'Norma [szt.]', 'uwagi': 'Uwagi', 'id_uzytkownika': 'Użytkownik' } self.proxy = QSortFilterProxyModel(self) self.parent = parent self.formularz = QGroupBox("Normy") self.lbl_w = QLabel("Wyszukaj") self.edit_w = QLineEdit(self) self.table = QTableView(self) self.btn_odswiez = QPushButton("Odśwież bazę") sciezka = czy_istnieje() self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(sciezka) if self.db.open(): print('Otworzono bazę danych') self.model = QSqlRelationalTableModel(self, self.db) self.initUI() def initUI(self): # Zainicjowanie tabeli zawsze przed wszystkim self.tabela() # lista wybieralna with open('./resources/Maszyny i operacje.json', 'r', encoding='utf-8') as file: plik_json = json.load(file) masz = plik_json['Maszyny'] operacje = plik_json['Operacje'] self.table.setItemDelegateForColumn(2, ComboDelegate(self, masz)) self.table.setItemDelegateForColumn(6, ComboDelegate(self, operacje)) for row in range(0, self.model.rowCount()): self.table.openPersistentEditor(self.model.index(row, 2)) self.table.openPersistentEditor(self.model.index(row, 6)) # Zatwierdzenie ok_button = QPushButton("Dodaj") cancel_button = QPushButton("Cofnij") hbox = QHBoxLayout() hbox.addStretch(1) hbox.addWidget(self.btn_odswiez) hbox.addWidget(ok_button) hbox.addWidget(cancel_button) # Layouty layout_v = QVBoxLayout() layout_h = QHBoxLayout() # Tabela self.proxy.setSourceModel(self.model) self.table.setModel(self.proxy) # przyporządkowanie layout_h.addWidget(self.lbl_w) layout_h.addWidget(self.edit_w) layout_v.addLayout(layout_h) layout_v.addWidget(self.table) self.formularz.setLayout(layout_v) main_layout = QVBoxLayout() main_layout.addWidget(self.formularz) main_layout.addLayout(hbox) self.setLayout(main_layout) # export # self.export() # Funkcje cancel_button.clicked.connect(self.anulowanie) ok_button.clicked.connect(self.dodaj) self.edit_w.textChanged.connect(self.wyszukiwanie) self.btn_odswiez.clicked.connect(self.refresh_db) self.skrot.activated.connect(self.refresh_db) # Menu kontekstowe własne self.table.setContextMenuPolicy(Qt.CustomContextMenu) self.table.customContextMenuRequested.connect(self.prawoklik) def prawoklik(self): menu = QMenu(self) if self.model.rowCount(): akcja = QAction('Usuń wiersz', self) akcja.triggered.connect(self.usun_wiersz) menu.addAction(akcja) menu.exec_(QCursor.pos()) def usun_wiersz(self): ok = QMessageBox.question(self, 'Potwierdzenie', 'Czy na pewno chcesz usunąć pozycję?', QMessageBox.Ok, QMessageBox.Cancel) if ok == QMessageBox.Ok: selected = self.table.currentIndex() self.model.removeRow(selected.row()) self.model.submitAll() self.model.select() @pyqtSlot(str) def wyszukiwanie(self, text): search = QRegExp(text, Qt.CaseInsensitive, QRegExp.RegExp) self.proxy.setFilterRegExp(search) # Odpowiedzialne za kolumnę, po której filtruje self.proxy.setFilterKeyColumn(-1) @pyqtSlot() def uzupelniene(self): # Pobranie tp, tm z bazy query = 'SELECT iddetale, tm, tp, ilosc_m, ilosc_szt FROM detale' dane_db = multipolaczenie(query) for i in range(len(dane_db)): # if dane_db[i][1] and dane_db[i][2]: tm = dane_db[i][1] tp1 = dane_db[i][2] ilosc_m = dane_db[i][3] ilosc_szt = dane_db[i][4] if not ilosc_m: ilosc_m = 1 zm = 1 else: zm = 0.95 if not ilosc_szt: ilosc_szt = 1 if isinstance(tm, int) and isinstance(tp1, int): tw = tm + tp1 else: tw = 0 tp2 = tw * 0.05 tj = (tw + tp2) * 1.1 tjh = tj / 3600 if tj != 0: norma = 8 / tj * 3600 * ilosc_m * zm * ilosc_szt else: norma = 0 print(round(norma)) # update bazy query = 'UPDATE "detale" SET "tj" = ' + str(round( tjh, 5)) + ', "norma" = ' + str( round(norma)) + ' WHERE "iddetale" = ' + str(dane_db[i][0]) update_bazy(query) # query = 'UPDATE "detale" SET "norma" = ' + str(round(norma)) + # ' WHERE "iddetale" = ' + str(dane_db[i][0]) update_bazy(query) @pyqtSlot() def refresh_db(self): try: self.uzupelniene() except: pass # Odświeżenie tabeli self.model.select() def tabela(self): self.model.setTable('detale') self.model.setRelation( 12, QSqlRelation('uzytkownicy', 'iduzytkownicy', 'nazwa_uz')) # Za zmianę w bazie odpowiada OnFieldChange self.model.setEditStrategy(QSqlTableModel.OnFieldChange) # Ustawianie nagłówków ilosc_kolumn = self.model.columnCount() for i in range(ilosc_kolumn): nazwa_kolumn = self.model.headerData(i, Qt.Horizontal) self.model.setHeaderData(i, Qt.Horizontal, self.naglowki[nazwa_kolumn]) self.model.select() # Odpowiada za edycję pojednynczym kliknieciem ''' Constant Value Description QAbstractItemView::NoEditTriggers 0 No editing possible. QAbstractItemView::CurrentChanged 1 Editing start whenever current item changes. QAbstractItemView::DoubleClicked 2 Editing starts when an item is double clicked. QAbstractItemView::SelectedClicked 4 Editing starts when clicking on an already selected item. QAbstractItemView::EditKeyPressed 8 Editing starts when the platform edit key has been pressed over an item. QAbstractItemView::AnyKeyPressed 16 Editing starts when any key is pressed over an item. QAbstractItemView::AllEditTriggers 31 Editing starts for all above actions. ''' if self.odczyt(): self.table.setEditTriggers(QAbstractItemView.AllEditTriggers) else: self.table.setEditTriggers(QAbstractItemView.NoEditTriggers) self.table.setSelectionMode(QAbstractItemView.SingleSelection) self.table.verticalHeader().setVisible(False) self.table.setSortingEnabled(True) self.table.resizeColumnsToContents() self.table.setModel(self.model) self.table.setAlternatingRowColors(True) self.table.resizeColumnsToContents() # self.table.doubleClicked.connect(self.klikniecie) def anulowanie(self): self.parent.statusBar().clearMessage() from opcje_qt import Wewnatrz menu_gl = Wewnatrz(self.parent) self.parent.setCentralWidget(menu_gl) def odczyt(self): id = str(self.parent.id_user[0]) query = 'SELECT odczyt FROM uzytkownicy WHERE iduzytkownicy=' + id return polaczenie(query)[0] def dodaj(self): poz, masz, op, tm, tp, ok = MultiDialog().getMultidialog(self) print(poz, masz, op, tm, tp, ok) id = self.parent.id_user[0] if ok and poz: query = "INSERT INTO detale(nr_detalu,maszyna,nazwa_op,tm,tp," \ "id_uzytkownika) VALUES ('" + poz + "','" + masz + "','" + op + "','" + tm + "','" + tp + "','" + str( id) + "');" print(query) polaczenie(query) if tm and tp: try: self.uzupelniene() except: pass self.model.select() self.parent.statusBar().showMessage("Dodano nową pozycję", 10000) else: print("Nie wpisano pozycji")
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.SelectItems) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(100) ## ==============自定义功能函数============ def __getFieldNames(self): ##获取所有字段名称 emptyRec = self.tabModel.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.tabModel = QSqlRelationalTableModel(self, self.DB) #数据表 self.tabModel.setTable("studInfo") #设置数据表 self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit ) #数据保存方式,OnManualSubmit , OnRowChange self.tabModel.setSort(self.tabModel.fieldIndex("studID"), Qt.AscendingOrder) #排序 if (self.tabModel.select() == False): #查询数据失败 QMessageBox.critical( self, "错误信息", "打开数据表错误,错误信息\n" + self.tabModel.lastError().text()) return self.__getFieldNames() #获取字段名和序号 ##字段显示名 self.tabModel.setHeaderData(self.fldNum["studID"], Qt.Horizontal, "学号") self.tabModel.setHeaderData(self.fldNum["name"], Qt.Horizontal, "姓名") self.tabModel.setHeaderData(self.fldNum["gender"], Qt.Horizontal, "性别") self.tabModel.setHeaderData(self.fldNum["departID"], Qt.Horizontal, "学院") self.tabModel.setHeaderData(self.fldNum["majorID"], Qt.Horizontal, "专业") ## 设置代码字段的查询关系数据表 self.tabModel.setRelation(self.fldNum["departID"], QSqlRelation("departments", "departID", "department")) #学院 self.tabModel.setRelation(self.fldNum["majorID"], QSqlRelation("majors", "majorID", "major")) #专业 self.selModel = QItemSelectionModel(self.tabModel) #关联选择模型 ##selModel当前项变化时触发currentChanged信号 self.selModel.currentChanged.connect(self.do_currentChanged) ##选择行变化时 ## self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.tabModel) #设置数据模型 self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型 delgate = QSqlRelationalDelegate(self.ui.tableView) self.ui.tableView.setItemDelegate(delgate) #为关系型字段设置缺省代理组件 self.tabModel.select() #必须重新查询数据 ##更新actions和界面组件的使能状态 self.ui.actOpenDB.setEnabled(False) self.ui.actRecAppend.setEnabled(True) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actFields.setEnabled(True) ## ==========由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_actSubmit_triggered(self): res = self.tabModel.submitAll() if (res == False): QMessageBox.information( self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text()) else: self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() ##取消修改 def on_actRevert_triggered(self): self.tabModel.revertAll() self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() ##添加记录 def on_actRecAppend_triggered(self): self.tabModel.insertRow(self.tabModel.rowCount(), QModelIndex()) #在末尾添加一个记录 curIndex = self.tabModel.index(self.tabModel.rowCount() - 1, 1) #创建最后一行的ModelIndex self.selModel.clearSelection() #清空选择项 self.selModel.setCurrentIndex( curIndex, QItemSelectionModel.Select) #设置刚插入的行为当前选择行 @pyqtSlot() ##插入记录 def on_actRecInsert_triggered(self): curIndex = self.ui.tableView.currentIndex() #QModelIndex self.tabModel.insertRow(curIndex.row(), QModelIndex()) self.selModel.clearSelection() #清除已有选择 self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) @pyqtSlot() ##删除记录 def on_actRecDelete_triggered(self): curIndex = self.selModel.currentIndex() #获取当前选择单元格的模型索引 self.tabModel.removeRow(curIndex.row()) #删除最后一行 @pyqtSlot() ##显示字段列表 def on_actFields_triggered(self): emptyRec = self.tabModel.record() #获取空记录,只有字段名 str = '' for i in range(emptyRec.count()): str = str + emptyRec.fieldName(i) + '\n' QMessageBox.information(self, "所有字段名", str) ## =============自定义槽函数=============================== def do_currentChanged(self, current, previous): ##更新actPost和actCancel 的状态 self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) #有未保存修改时可用 self.ui.actRevert.setEnabled(self.tabModel.isDirty())
class MainForm(QDialog): def __init__(self): super(MainForm, self).__init__() self.assetModel = QSqlRelationalTableModel(self) self.assetModel.setTable("assets") self.assetModel.setRelation(CATEGORYID, QSqlRelation("categories", "id", "name")) self.assetModel.setSort(ROOM, Qt.AscendingOrder) self.assetModel.setHeaderData(ID, Qt.Horizontal, "ID") self.assetModel.setHeaderData(NAME, Qt.Horizontal, "Name") self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal, "Category") self.assetModel.setHeaderData(ROOM, Qt.Horizontal, "Room") self.assetModel.select() self.assetView = QTableView() self.assetView.setModel(self.assetModel) self.assetView.setItemDelegate(AssetDelegate(self)) self.assetView.setSelectionMode(QTableView.SingleSelection) self.assetView.setSelectionBehavior(QTableView.SelectRows) self.assetView.setColumnHidden(ID, True) self.assetView.resizeColumnsToContents() assetLabel = QLabel("A&ssets") assetLabel.setBuddy(self.assetView) self.logModel = QSqlRelationalTableModel(self) self.logModel.setTable("logs") self.logModel.setRelation(ACTIONID, QSqlRelation("actions", "id", "name")) self.logModel.setSort(DATE, Qt.AscendingOrder) self.logModel.setHeaderData(DATE, Qt.Horizontal, "Date") self.logModel.setHeaderData(ACTIONID, Qt.Horizontal, "Action") self.logModel.select() self.logView = QTableView() self.logView.setModel(self.logModel) self.logView.setItemDelegate(LogDelegate(self)) self.logView.setSelectionMode(QTableView.SingleSelection) self.logView.setSelectionBehavior(QTableView.SelectRows) self.logView.setColumnHidden(ID, True) self.logView.setColumnHidden(ASSETID, True) self.logView.resizeColumnsToContents() self.logView.horizontalHeader().setStretchLastSection(True) logLabel = QLabel("&Logs") logLabel.setBuddy(self.logView) addAssetButton = QPushButton("&Add Asset") deleteAssetButton = QPushButton("&Delete Asset") addActionButton = QPushButton("Add A&ction") deleteActionButton = QPushButton("Delete Ac&tion") editActionsButton = QPushButton("&Edit Actions...") editCategoriesButton = QPushButton("Ed&it Categories...") quitButton = QPushButton("&Quit") for button in (addAssetButton, deleteAssetButton, addActionButton, deleteActionButton, editActionsButton, editCategoriesButton, quitButton): if MAC: button.setDefault(False) button.setAutoDefault(False) else: button.setFocusPolicy(Qt.NoFocus) dataLayout = QVBoxLayout() dataLayout.addWidget(assetLabel) dataLayout.addWidget(self.assetView, 1) dataLayout.addWidget(logLabel) dataLayout.addWidget(self.logView) buttonLayout = QVBoxLayout() buttonLayout.addWidget(addAssetButton) buttonLayout.addWidget(deleteAssetButton) buttonLayout.addWidget(addActionButton) buttonLayout.addWidget(deleteActionButton) buttonLayout.addWidget(editActionsButton) buttonLayout.addWidget(editCategoriesButton) buttonLayout.addStretch() buttonLayout.addWidget(quitButton) layout = QHBoxLayout() layout.addLayout(dataLayout, 1) layout.addLayout(buttonLayout) self.setLayout(layout) #self.connect(self.assetView.selectionModel(), #SIGNAL(("currentRowChanged(QModelIndex,QModelIndex)")), #self.assetChanged) self.assetView.selectionModel().currentRowChanged.connect( self.assetChanged) addAssetButton.clicked.connect(self.addAsset) deleteAssetButton.clicked.connect(self.deleteAsset) addActionButton.clicked.connect(self.addAction) deleteActionButton.clicked.connect(self.deleteAction) editActionsButton.clicked.connect(self.editActions) editCategoriesButton.clicked.connect(self.editCategories) quitButton.clicked.connect(self.done) self.assetChanged(self.assetView.currentIndex()) self.setMinimumWidth(650) self.setWindowTitle("Asset Manager") def done(self, result=1): query = QSqlQuery() query.exec_("DELETE FROM logs WHERE logs.assetid NOT IN" "(SELECT id FROM assets)") QDialog.done(self, 1) def assetChanged(self, index): if index.isValid(): record = self.assetModel.record(index.row()) #print(index.row()) id = record.value("id") self.logModel.setFilter("assetid = {0}".format(id)) else: self.logModel.setFilter("assetid = -1") #self.logModel.reset() # workaround for Qt <= 4.3.3/SQLite bug #self.logModel.beginResetModel() self.logModel.select() self.logView.horizontalHeader().setVisible( self.logModel.rowCount() > 0) if PYQT_VERSION_STR < "4.1.0": self.logView.setColumnHidden(ID, True) self.logView.setColumnHidden(ASSETID, True) #self.logModel.endResetModel() def addAsset(self): row = (self.assetView.currentIndex().row() if self.assetView.currentIndex().isValid() else 0) QSqlDatabase.database().transaction() self.assetModel.insertRow(row) index = self.assetModel.index(row, NAME) self.assetView.setCurrentIndex(index) assetid = 1 query = QSqlQuery() query.exec_("SELECT MAX(id) FROM assets") if query.next(): assetid = query.value(0) query.prepare("INSERT INTO logs (assetid, date, actionid) " "VALUES (:assetid, :date, :actionid)") query.bindValue(":assetid", assetid + 1) query.bindValue(":date", QDate.currentDate()) query.bindValue(":actionid", ACQUIRED) query.exec_() QSqlDatabase.database().commit() #self.logModel.select() self.assetView.edit(index) def deleteAsset(self): index = self.assetView.currentIndex() if not index.isValid(): return QSqlDatabase.database().transaction() record = self.assetModel.record(index.row()) assetid = record.value(ID) logrecords = 1 query = QSqlQuery( "SELECT COUNT(*) FROM logs WHERE assetid = {0}".format(assetid)) if query.next(): logrecords = query.value(0) msg = ("<font color=red>Delete</font><br><b>{0}</b>" "<br>from room {1}").format(record.value(NAME), record.value(ROOM)) if logrecords > 1: msg += (", along with {0} log records".format(logrecords)) msg += "?" if (QMessageBox.question(self, "Delete Asset", msg, QMessageBox.Yes | QMessageBox.No) == QMessageBox.No): QSqlDatabase.database().rollback() return #query.exec_("DELETE FROM logs WHERE assetid = {0}" # .format(assetid)) #use model API self.logModel.setFilter("assetid={0}".format(assetid)) self.logModel.select() if self.logModel.rowCount() > 0: self.logModel.removeRows(0, self.logModel.rowCount()) self.logModel.submitAll() self.assetModel.removeRow(index.row()) self.assetModel.submitAll() QSqlDatabase.database().commit() self.assetModel.select() self.assetChanged(self.assetView.currentIndex()) def addAction(self): index = self.assetView.currentIndex() if not index.isValid(): return QSqlDatabase.database().transaction() record = self.assetModel.record(index.row()) assetid = record.value(ID) row = self.logModel.rowCount() self.logModel.insertRow(row) self.logModel.setData(self.logModel.index(row, ASSETID), assetid) self.logModel.setData(self.logModel.index(row, DATE), QDate.currentDate()) QSqlDatabase.database().commit() index = self.logModel.index(row, ACTIONID) self.logView.setCurrentIndex(index) self.logView.edit(index) def deleteAction(self): index = self.logView.currentIndex() if not index.isValid(): return record = self.logModel.record(index.row()) action = record.value(ACTIONID) if action == "Acquired": QMessageBox.information( self, "Delete Log", "The 'Acquired' log record cannot be deleted.<br>" "You could delete the entire asset instead.") return when = str(record.value(DATE)) if (QMessageBox.question(self, "Delete Log", "Delete log<br>{0} {1}?".format(when, action), QMessageBox.Yes | QMessageBox.No) == QMessageBox.No): return self.logModel.removeRow(index.row()) self.logModel.submitAll() self.logModel.select() def editActions(self): form = ReferenceDataDlg("actions", "Action", self) form.exec_() def editCategories(self): form = ReferenceDataDlg("categories", "Category", self) form.exec_()