def loadModel(name_table): model = QSqlRelationalTableModel() model.setTable(name_table) processingModel[name_table](model) model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.select() return model
class Contacts(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.setWindowTitle("QTableView Example") self.resize(800, 600) # Set up the model self.model = QSqlRelationalTableModel(self) self.model.setTable("queue") self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setHeaderData(0, Qt.Horizontal, "ID") self.model.setHeaderData(1, Qt.Horizontal, "Number") self.model.setHeaderData(2, Qt.Horizontal, "Enter Time") self.model.setHeaderData(3, Qt.Horizontal, "Call Time") self.model.setHeaderData(4, Qt.Horizontal, "Status") self.model.setHeaderData(5, Qt.Horizontal, "Destination") # ใช้ QSqlRelation อ่านคีย์นอกจากในตาราง queue แล้ว maps เข้ากับตาราง destination เลือก index กับ text ที่ต้องการแสดงในตาราง queue self.model.setRelation( 5, QSqlRelation("destination", "des_id", "des_name")) self.model.select() # Set up the view self.view = QTableView() self.view.setModel(self.model) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.view.setColumnHidden(0, True) self.view.setCornerButtonEnabled(False) #self.view.setEditTriggers(QAbstractItemView()) #self.headerView = QHeaderView() #self.view.setHorizontalHeader() #print(str(self.view.editTriggers())) self.view.resizeColumnsToContents() # self.cbxModel = QSqlQueryModel(self) # self.cbxModel.setQuery = "SELECT des_name FROM destination" # self.cbxModel.query() # สร้าง comboboxmodel ดึงข้อมูลจาก ex1.destination #self.cbxModel = QSqlTableModel(self) #self.cbxModel.setTable("destination") #self.cbxModel.select() # สร้าง comboboxview #self.cbxView = QComboBox() #self.cbxView.setModel(self.cbxModel) # เลือก column ที่จะมาแสดง #self.cbxView.setModelColumn(1) # วาด comboboxview ลงบน tableview ติดปัญหา comboboxview จะวาดลงใน record สุดท้ายเสมอ # for i in range(self.model.rowCount()): # i = self.view.model().index(1, 5) # self.view.setIndexWidget(i,self.cbxView) self.setCentralWidget(self.view)
class TableModel(): def __init__(self, db): self.model = QSqlRelationalTableModel(db=db) self.model.setTable('balance') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation('items', 'id', 'name')) self.model.setRelation(2, QSqlRelation('categories', 'id', 'name')) self.model.setRelation(4, QSqlRelation('currencies', 'id', 'name')) self.model.setRelation(5, QSqlRelation('users', 'id', 'full_name')) self.model.setRelation(6, QSqlRelation('places', 'id', 'name')) self.model.setRelation(8, QSqlRelation('measures', 'id', 'short')) self.model.setHeaderData(0, Qt.Horizontal, QtGui.qApp.tr("ID")) self.model.setHeaderData(1, Qt.Horizontal, QtGui.qApp.tr("Item")) self.model.setHeaderData(2, Qt.Horizontal, QtGui.qApp.tr("Category")) self.model.setHeaderData(3, Qt.Horizontal, QtGui.qApp.tr("Cost")) self.model.setHeaderData(4, Qt.Horizontal, QtGui.qApp.tr("Currency")) self.model.setHeaderData(5, Qt.Horizontal, QtGui.qApp.tr("By whom")) self.model.setHeaderData(6, Qt.Horizontal, QtGui.qApp.tr("Where")) self.model.setHeaderData(7, Qt.Horizontal, QtGui.qApp.tr("Qty/Amount")) self.model.setHeaderData(8, Qt.Horizontal, QtGui.qApp.tr("Units")) self.model.setHeaderData(9, Qt.Horizontal, QtGui.qApp.tr("is spending")) self.model.setHeaderData(10, Qt.Horizontal, QtGui.qApp.tr("Note")) self.model.setHeaderData(11, Qt.Horizontal, QtGui.qApp.tr("Date and " "Time")) self.model.removeColumn(12) self.model.removeColumn(13) if not self.model.select(): _log("Table model selection error[%s]: %s" % ( self.model.lastError().type(), self.model.lastError().text() )) def get_model(self): return self.model
class Model(object): def __init__(self, databasetype="QSQLITE", databasename="Sqlite_Sql/testcase.db", sqltablename="result"): db = QSqlDatabase.addDatabase(databasetype) db.setDatabaseName(databasename) self.sqltablename = sqltablename self.tablemodel() def tablemodel(self): # self.model=QSqlTableModel() # self.model.setRelation(4,QSqlRelation("TEST","id_test","method_name")) self.model = QSqlRelationalTableModel() self.model.setTable(self.sqltablename) self.model.setHeaderData(0, Qt.Horizontal, '用例ID') self.model.setHeaderData(1, Qt.Horizontal, '用例名称') self.model.setHeaderData(2, Qt.Horizontal, '脚本名称') self.model.setHeaderData(3, Qt.Horizontal, '执行状态') self.model.setHeaderData(4, Qt.Horizontal, '结论') self.model.setHeaderData(5, Qt.Horizontal, '时间') self.model.setHeaderData(6, Qt.Horizontal, '操作') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.select()
class Shtat(QMainWindow): """ Класс, реализующий окно для работы со штатным расписанием в виде таблицы """ con = QSqlDatabase.addDatabase('QSQLITE') con.setDatabaseName(settings.db_name) def __init__(self, parent: any = None) -> None: """ Функция инициализации окна штатного расписания :param parent: Родительский виджет (окно) """ super().__init__(parent) self.filters = ['Номер', 'Подразделение', 'Должность', 'Количество', 'Тариф', 'Оклад', 'ФИО', 'Декрет', 'История', 'Оклад замещающего работника', 'Вид позиции', ] self.setObjectName("ShtatWindow") self.resize(1380, 886) self.centralwidget = QtWidgets.QWidget(self) self.centralwidget.setObjectName("centralwidget") self.view = QTableView(self.centralwidget) self.view.setGeometry(QRect(0, 0, 1381, 821)) self.view.setObjectName("shtat_table_view") self.view.setSortingEnabled(True) self.filter_combo_box = QtWidgets.QComboBox(self.centralwidget) self.filter_combo_box.setGeometry(QRect(250, 840, 231, 31)) self.filter_combo_box.setObjectName("filter_combo_box") self.filter_input = QtWidgets.QLineEdit(self.centralwidget) self.filter_input.setGeometry(QRect(500, 840, 281, 31)) self.filter_input.setObjectName("filter_input") self.filter_label = QtWidgets.QLabel(self.centralwidget) self.filter_label.setGeometry(QRect(10, 840, 221, 31)) font = QFont() font.setFamily("Times New Roman") font.setPointSize(12) self.filter_label.setFont(font) self.filter_label.setObjectName("filter_label") self.filter_button = QtWidgets.QPushButton(self.centralwidget) self.filter_button.setGeometry(QRect(800, 840, 171, 31)) self.save_shtat_button = QtWidgets.QPushButton(self.centralwidget) self.save_shtat_button.setGeometry(QRect(1000, 840, 225, 31)) self.filter_button.setFont(font) self.save_shtat_button.setFont(font) self.filter_button.setObjectName("filter_button") self.setCentralWidget(self.centralwidget) _translate = QCoreApplication.translate self.setWindowTitle(_translate("ShtatWindow", "Работа со штатным расписанием")) self.filter_label.setText(_translate("ShtatWindow", "Фильтровать по столбцу:")) self.filter_button.setText(_translate("ShtatWindow", "Поиск")) self.save_shtat_button.setText(_translate("ShtatWindow", "Выгрузить штатное расписание")) self.model = QSqlRelationalTableModel(self.view) self.model.setTable('salaries') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation("departments", "code", "department")) self.model.setRelation(10, QSqlRelation("positions", "position_code", "position_name")) for i in range(0, len(self.filters)): self.model.setHeaderData(i, Qt.Horizontal, self.filters[i]) self.model.select() self.proxyModelContact = QSortFilterProxyModel(self) self.proxyModelContact.setSourceModel(self.model) self.view.setModel(self.proxyModelContact) self.view.resizeColumnsToContents() self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.filter_combo_box.addItems(self.filters) self.filter_button.clicked.connect(self.use_filter_button) self.save_shtat_button.clicked.connect(self.use_save_shtat_button) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) def use_filter_button(self) -> None: """ Функция кнопки "Поиск". Считывает паттерн из поля фильтра и применяет его к столбцу, выбранного в комбо-боксе фильтра :return: None """ self.proxyModelContact.setFilterKeyColumn(self.filters.index(self.filter_combo_box.currentText())) self.proxyModelContact.setFilterRegExp(self.filter_input.text()) @staticmethod def use_save_shtat_button() -> None: """ Функция кнопки "Выгрузить штатное расписание". Выгружает все штатное расписание в файл формата Excel :return: None """ file = QtWidgets.QFileDialog.getSaveFileName()[0] saver = FullShtatToExcel(file if file.endswith(".xlsx") else f"{file}.xlsx") saver.full_shtat_to_excel()
def __init__(self, parent=None): super().__init__(parent) horizontalLayout = QHBoxLayout() self.dayView = QTableView() self.dayView.setFrameShape(QFrame.Box) self.dayView.horizontalHeader().setStretchLastSection(True) self.dayView.verticalHeader().setVisible(False) horizontalLayout.addWidget(self.dayView) verticalLayout = QVBoxLayout() self.calendarWidget = QCalendarWidget() self.calendarWidget.setMinimumSize(QSize(250, 200)) self.calendarWidget.setMaximumSize(QSize(250, 200)) self.calendarWidget.setMinimumDate(QDate(2017, 1, 1)) self.calendarWidget.setMaximumDate(QDate(2030, 1, 1)) self.calendarWidget.selectionChanged.connect(self.dataChange) self.calendarWidget.setSelectedDate(QDate.currentDate()) verticalLayout.addWidget(self.calendarWidget) titleFV = QLabel('Food View') verticalLayout.addWidget(titleFV) self.filterLine = QLineEdit() self.filterLine.setMaximumSize(QSize(200, 25)) self.filterLine.textChanged.connect(self.filterChange) buttonAdd = QPushButton(QIcon("images/add.png"), '', None) buttonAdd.setMaximumSize(QSize(20, 30)) buttonAdd.clicked.connect(self.addFood) buttonDell = QPushButton(QIcon("images/del.png"), '', None) buttonDell.setMaximumSize(QSize(20, 30)) buttonDell.clicked.connect(self.delFood) lineEditLayout = QHBoxLayout() lineEditLayout.addWidget(self.filterLine) lineEditLayout.addWidget(buttonAdd) lineEditLayout.addWidget(buttonDell) verticalLayout.addLayout(lineEditLayout) self.foodView = QTableView() self.foodView.setMinimumSize(QSize(0, 0)) self.foodView.setMaximumSize(QSize(250, 1000)) self.foodView.verticalHeader().setVisible(False) self.foodView.horizontalHeader().setStretchLastSection(True) verticalLayout.addWidget(self.foodView) horizontalLayout.addLayout(verticalLayout) self.setLayout(horizontalLayout) model_in = QSqlRelationalTableModel() model_in.setEditStrategy(QSqlTableModel.OnFieldChange) model_in.setTable("intake_food") id_food = model_in.fieldIndex("id_food") date = model_in.fieldIndex("food_date") mass = model_in.fieldIndex("mass") # Set model, hide ID column model_in.setRelation(id_food, QSqlRelation("food", "id", "name")) model_in.setHeaderData(id_food, Qt.Horizontal, "Food") model_in.setHeaderData(date, Qt.Horizontal, "Date") model_in.setHeaderData(mass, Qt.Horizontal, "Mass") if not model_in.select(): self.showError(model_in.lastError()) return self.proxyModel_in = QSortFilterProxyModel() self.proxyModel_in.setSourceModel(model_in) self.proxyModel_in.setFilterKeyColumn(2) self.dayView.setItemDelegate(FlipProxyDelegate()) self.dayView.setModel(self.proxyModel_in) self.dayView.setColumnHidden(0, True) self.dayView.setColumnHidden(2, True) self.dayView.setSelectionMode(QAbstractItemView.SingleSelection) self.dayView.setContextMenuPolicy(Qt.CustomContextMenu) self.dayView.customContextMenuRequested.connect(self.ShowContextMenu) # filter day food by calendar widget self.dataChange() self.model_f = QSqlRelationalTableModel() self.model_f.setEditStrategy(QSqlTableModel.OnFieldChange) self.model_f.setTable("food") self.model_f.setHeaderData(1, Qt.Horizontal, "Food") self.model_f.setHeaderData(2, Qt.Horizontal, "Rate") if not self.model_f.select(): self.showError(self.model_f.lastError()) return self.proxyModel_f = QSortFilterProxyModel() self.proxyModel_f.setSourceModel(self.model_f) self.proxyModel_f.setFilterKeyColumn(1) self.foodView.setModel(self.proxyModel_f) self.foodView.setColumnHidden(0, True) self.foodView.setSelectionMode(QAbstractItemView.SingleSelection) self.foodView.setColumnWidth(1, 150) self.foodView.setColumnWidth(2, 90)
class MyWidget(QWidget): def __init__(self, parent=None): super().__init__(parent) horizontalLayout = QHBoxLayout() self.dayView = QTableView() self.dayView.setFrameShape(QFrame.Box) self.dayView.horizontalHeader().setStretchLastSection(True) self.dayView.verticalHeader().setVisible(False) horizontalLayout.addWidget(self.dayView) verticalLayout = QVBoxLayout() self.calendarWidget = QCalendarWidget() self.calendarWidget.setMinimumSize(QSize(250, 200)) self.calendarWidget.setMaximumSize(QSize(250, 200)) self.calendarWidget.setMinimumDate(QDate(2017, 1, 1)) self.calendarWidget.setMaximumDate(QDate(2030, 1, 1)) self.calendarWidget.selectionChanged.connect(self.dataChange) self.calendarWidget.setSelectedDate(QDate.currentDate()) verticalLayout.addWidget(self.calendarWidget) titleFV = QLabel('Food View') verticalLayout.addWidget(titleFV) self.filterLine = QLineEdit() self.filterLine.setMaximumSize(QSize(200, 25)) self.filterLine.textChanged.connect(self.filterChange) buttonAdd = QPushButton(QIcon("images/add.png"), '', None) buttonAdd.setMaximumSize(QSize(20, 30)) buttonAdd.clicked.connect(self.addFood) buttonDell = QPushButton(QIcon("images/del.png"), '', None) buttonDell.setMaximumSize(QSize(20, 30)) buttonDell.clicked.connect(self.delFood) lineEditLayout = QHBoxLayout() lineEditLayout.addWidget(self.filterLine) lineEditLayout.addWidget(buttonAdd) lineEditLayout.addWidget(buttonDell) verticalLayout.addLayout(lineEditLayout) self.foodView = QTableView() self.foodView.setMinimumSize(QSize(0, 0)) self.foodView.setMaximumSize(QSize(250, 1000)) self.foodView.verticalHeader().setVisible(False) self.foodView.horizontalHeader().setStretchLastSection(True) verticalLayout.addWidget(self.foodView) horizontalLayout.addLayout(verticalLayout) self.setLayout(horizontalLayout) model_in = QSqlRelationalTableModel() model_in.setEditStrategy(QSqlTableModel.OnFieldChange) model_in.setTable("intake_food") id_food = model_in.fieldIndex("id_food") date = model_in.fieldIndex("food_date") mass = model_in.fieldIndex("mass") # Set model, hide ID column model_in.setRelation(id_food, QSqlRelation("food", "id", "name")) model_in.setHeaderData(id_food, Qt.Horizontal, "Food") model_in.setHeaderData(date, Qt.Horizontal, "Date") model_in.setHeaderData(mass, Qt.Horizontal, "Mass") if not model_in.select(): self.showError(model_in.lastError()) return self.proxyModel_in = QSortFilterProxyModel() self.proxyModel_in.setSourceModel(model_in) self.proxyModel_in.setFilterKeyColumn(2) self.dayView.setItemDelegate(FlipProxyDelegate()) self.dayView.setModel(self.proxyModel_in) self.dayView.setColumnHidden(0, True) self.dayView.setColumnHidden(2, True) self.dayView.setSelectionMode(QAbstractItemView.SingleSelection) self.dayView.setContextMenuPolicy(Qt.CustomContextMenu) self.dayView.customContextMenuRequested.connect(self.ShowContextMenu) # filter day food by calendar widget self.dataChange() self.model_f = QSqlRelationalTableModel() self.model_f.setEditStrategy(QSqlTableModel.OnFieldChange) self.model_f.setTable("food") self.model_f.setHeaderData(1, Qt.Horizontal, "Food") self.model_f.setHeaderData(2, Qt.Horizontal, "Rate") if not self.model_f.select(): self.showError(self.model_f.lastError()) return self.proxyModel_f = QSortFilterProxyModel() self.proxyModel_f.setSourceModel(self.model_f) self.proxyModel_f.setFilterKeyColumn(1) self.foodView.setModel(self.proxyModel_f) self.foodView.setColumnHidden(0, True) self.foodView.setSelectionMode(QAbstractItemView.SingleSelection) self.foodView.setColumnWidth(1, 150) self.foodView.setColumnWidth(2, 90) def showError(self, err): QMessageBox.critical(self, "Unable to initialize Database", "Error initializing database: " + err.text()) def filterChange(self): regExp = QRegExp(self.filterLine.text(), Qt.CaseInsensitive, QRegExp.FixedString) self.proxyModel_f.setFilterRegExp(regExp) def dataChange(self): date = self.calendarWidget.selectedDate().toString('dd.MM.yyyy') regExp = QRegExp(date, Qt.CaseInsensitive, QRegExp.FixedString) self.proxyModel_in.setFilterRegExp(regExp) def addFood(self): self.model_f.insertRow(self.model_f.rowCount()) def delFood(self): self.model_f.removeRow(self.foodView.currentIndex().row()) self.model_f.select() def resizeEvent(self, event): self.dayView.setColumnWidth(1, self.dayView.width() * 0.7) self.dayView.setColumnWidth(3, self.dayView.width() * 0.2) QWidget.resizeEvent(self, event) def ShowContextMenu(self, pos): contextMenu = QMenu("Context menu", self) action1 = QAction("Add food eaten", self) contextMenu.addAction(action1) contextMenu.exec(self.mapToGlobal(pos))
class SurveyTableWidget(SurveyTableView): combineSignal = pyqtSignal() addSurveySignal = pyqtSignal() editSurveySignal = pyqtSignal() loadSurveyAudioSignal = pyqtSignal() selectionChangeSignal = pyqtSignal() def __init__(self): super().__init__() # init table model TODO: make vars private self.surveyTableModel = QSqlRelationalTableModel() self.surveyTableModel.setTable(R.tableName) self.surveyTableModel.setEditStrategy(QSqlTableModel.OnRowChange) self.surveyTableModel.select() self.surveyTableView.setModel(self.surveyTableModel) self.surveyTableSelectionModel = self.surveyTableView.selectionModel() self.surveyTableSelectionModel.selectionChanged.connect( self.onSurveySelectionChange) self.surveyTableView.horizontalHeader().sortIndicatorChanged.connect( self.onSurveySelectionChange) # TODO # connect buttons self.addSurveyButton.clicked.connect(self.addSurveySignal.emit) self.editSurveyButton.clicked.connect(self.editSurveySignal.emit) self.deleteSurveyButton.clicked.connect(self.deleteSurveyButtonAction) self.loadAudioButton.clicked.connect(self.loadSurveyAudioSignal.emit) self.combineButton.clicked.connect(self.combineSignal.emit) # info to populate AddSurveyDialog def getDialogArgs(self): recorderIds = [] query = QSqlQuery("SELECT DISTINCT recorder_id FROM survey") while query.next(): print(query.value(0)) kwargs = {"recorderIds": recorderIds} return kwargs def select(self, index): self.surveyTableView.selectRow(index) def sortByKey(self): self.surveyTableView.sortByColumn(0, Qt.DescendingOrder) # TODO: keep selection after sort def onSort(self): pass def getSelectedRows(self): return self.surveyTableSelectionModel.selectedRows() def onPlaylistMediaChange(self, hasMedia): self.addSurveyButton.setEnabled(hasMedia) self.selectionChangeSignal.emit() def getSelectedData(self, row=0, column=Column.SURVEY_DATETIME): key = self.getSelectedRows()[row] return self.surveyTableModel.data(key.sibling(key.row(), column.value)) def getDataFromKey(self, keyIndex, column=Column.SURVEY_DATETIME): return self.surveyTableModel.data( keyIndex.sibling(keyIndex.row(), column.value)) def getSelectedKeys(self): list = [] for row in self.getSelectedRows(): list.append(str(self.surveyTableModel.data(row))) return list def singlePathSelected(self): if len(self.getSelectedRows()) > 0: firstPath = self.getDataFromKey(self.getSelectedRows()[0], Column.FILE) for row in self.getSelectedRows(): # self.getSelectedRows(): if self.getDataFromKey(row, Column.FILE) != firstPath: return None return firstPath return None def addRecord(self, record): sqlRecord = record.getQSQLRecord(self.surveyTableModel.record()) self.surveyTableModel.insertRecord(0, sqlRecord) self.sortByKey() self.select(0) QApplication.processEvents() # allow for selection highlight def editRecord(self, record): index = self.getSelectedRows()[0] record.editData(model=self.surveyTableModel, index=index) QApplication.processEvents() # TODO maybe remove @pyqtSlot() def deleteSurveyButtonAction(self): for row in self.getSelectedRows(): self.surveyTableModel.removeRow(row.row()) self.surveyTableSelectionModel.clearSelection() self.surveyTableModel.select() QApplication.processEvents() @pyqtSlot() def onSurveySelectionChange(self): # enable/disable buttons then signal MainWindow selectionCount = len(self.getSelectedRows()) self.deleteSurveyButton.setEnabled(selectionCount > 0) self.loadAudioButton.setEnabled(selectionCount > 0) self.editSurveyButton.setEnabled(selectionCount == 1) self.combineButton.setEnabled( selectionCount > 1 and self.singlePathSelected() is not None) self.selectionChangeSignal.emit()
class DBMan(object): db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("ProductDB.db") def __init__(self): super(DBMan, self).__init__() self.products_model = QSqlRelationalTableModel() self.buyer_model = QSqlRelationalTableModel() self.sales_model = QSqlRelationalTableModel() self.bill_model = QSqlRelationalTableModel() self.check_stock = 0 self.last_added = " " def load_tables(self): self.products_model.setTable("products") self.buyer_model.setTable("buyer_type") self.sales_model.setTable("sales") self.bill_model.setTable("bill") self.bill_model.setEditStrategy( QSqlRelationalTableModel.OnManualSubmit) def update_sales(self, pname, count, buyer_type): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select ID from products where Name = ?', [pname]) pid = int(c.fetchone()[0]) print(pid) sdate = str(date.today()) stime = str(datetime.now().time()) print(stime) c.execute('Insert into purchase_log values(?,?,?,?)', ( stime, pid, count, buyer_type, )) c.execute( 'Select * from sales where Id = ? and Sale_Date = ? and Buyer = ?', ( pid, sdate, buyer_type, )) check_id_date_buyer = c.fetchone() c.execute('Select * from sales where Id = ? and Sale_Date = ? ', ( pid, sdate, )) check_id_date = c.fetchone() c.execute( 'Select * from sales where Id = ? and Sale_Date = ? and Buyer = ?', ( pid, sdate, buyer_type, )) check_buyer = c.fetchone() c.execute('Select Price from products where ID=?', (pid, )) price = int(c.fetchone()[0]) total = count * price c.execute('Select Stock from products where ID=?', (pid, )) check_stock = int(c.fetchone()[0]) self.last_added = pname + ' x' + str(count) if check_stock == 0 | check_stock < count: self.warner() return None else: if check_id_date_buyer is not None: c.execute( "Update sales Set Quantity = Quantity + ? , Total_Price = Total_Price + ? where Id= ? and Sale_Date= ? and Buyer= ?", (count, total, pid, sdate, buyer_type)) c.execute("Update products Set Stock = Stock - ? Where ID=?", ( count, pid, )) conn.commit() else: c.execute('Insert into sales values(?,?,?,?,?)', ( sdate, pid, count, total, buyer_type, )) c.execute("Update products Set Stock = Stock - ? Where ID=?", ( count, pid, )) conn.commit() return self.last_added def undo_sale(self): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select * from purchase_log') check_empty = c.fetchall() print(check_empty) if not check_empty: return c.execute( "Select Id from purchase_log where Time = (Select max(Time) from purchase_log)" ) pid = int(c.fetchone()[0]) print(pid) c.execute( "Select Count from purchase_log where Time = (Select max(Time) from purchase_log)" ) count = int(c.fetchone()[0]) c.execute( "Select Buyer from purchase_log where Time = (Select max(Time) from purchase_log)" ) buyer_type = str(c.fetchone()[0]) sdate = str(date.today()) c.execute('Select Price from products where ID=?', (pid, )) price = int(c.fetchone()[0]) total = count * price c.execute( "Update sales Set Quantity = Quantity - ? , Total_Price = Total_Price - ? where Id= ? and Sale_Date= ? and Buyer= ?", (count, total, pid, sdate, buyer_type)) c.execute("Update products Set Stock = Stock + ? Where ID=?", ( count, pid, )) c.execute( "Select Quantity from sales where Id= ? and Sale_Date= ? and Buyer= ?", (pid, sdate, buyer_type)) check_quantity = int(c.fetchone()[0]) if check_quantity is 0: c.execute( "Delete from sales where Id= ? and Sale_Date= ? and Buyer= ?", (pid, sdate, buyer_type)) c.execute( "Delete from purchase_log where Time = (Select max(Time) from purchase_log)" ) conn.commit() def warner(self): warn = warning(0, " ") warn.exec_() def clear_undo(self): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select * from purchase_log') check_empty = c.fetchall() print(check_empty) #if not check_empty: #return c.execute( 'Delete from purchase_log where Time not in (Select Time from purchase_log order by Time desc LIMIT 10)' ) conn.commit() def return_stock(self, pname): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select ID from products where Name = ?', [pname]) pid = int(c.fetchone()[0]) c.execute('Select Stock from products where ID=?', (pid, )) stock = c.fetchone()[0] return stock def return_price(self, pname): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select ID from products where Name = ?', [pname]) pid = int(c.fetchone()[0]) c.execute('Select Price from products where ID=?', (pid, )) price = c.fetchone()[0] return price def clean(self): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() today = date.today() lastmonth = today - timedelta(30) c.execute('Delete from sales where Sale_Date<?', (lastmonth, )) conn.commit() def update_bill(self, name, count, buyer_type): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select ID from products where Name = ?', [name]) pid = int(c.fetchone()[0]) c.execute('Select * from bill where Name = ?', (name, )) check_item = c.fetchone() c.execute("Select max(No) from bill") max_no = c.fetchone()[0] if max_no == None: max_no = 0 else: max_no = int(max_no) c.execute('Select Price from products where ID=?', (pid, )) price = int(c.fetchone()[0]) total = count * price c.execute('Select Stock from products where ID=?', (pid, )) check_stock = int(c.fetchone()[0]) if check_stock == 0 | check_stock < count: self.warner() else: if check_item is not None: c.execute( "Update bill Set Quantity = Quantity + ? , Total = Total + ? where Name = ?", (count, total, name)) conn.commit() c.execute("Update products Set Stock = Stock - ? Where ID=?", ( count, pid, )) conn.commit() self.bill_model.select() else: c.execute('Insert into bill values(?,?,?,?,?)', ( max_no + 1, name, count, price, total, )) c.execute("Update products Set Stock = Stock - ? Where ID=?", ( count, pid, )) conn.commit() self.bill_model.select() c.execute('Select sum(Total) from bill') bill_total = c.fetchone()[0] if bill_total: return int(bill_total) else: return 0 def bill_delete(self, row): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Delete from bill where No = ?', (row, )) conn.commit() pass def update_bill_sales(self, buyer): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select * from bill') bill_data = c.fetchall() for i in range(len(bill_data)): bill_list = [list(item) for item in bill_data] for bill_row in bill_list: pname = bill_row[1] quantity = bill_row[2] self.update_sales(pname, quantity, buyer) c.execute('Delete from bill') conn.commit() def load_low_stock(self): conn = sqlite3.connect('ProductDB.db') c = conn.cursor() c.execute('Select Stock,Name from products ORDER BY STOCK limit 50') low_list = c.fetchall() stock_list = "" for row in low_list: print(row) stock_list += str(row[0]) stock_list += "x " stock_list += str(row[1]) stock_list += "\n" return stock_list
class MainWindow(QtWidgets.QMainWindow): def __init__(self) -> None: super().__init__() self.db = QSqlDatabase.addDatabase("QSQLITE") self.dbpath = DEFAULT_DATABASE_PATH self.qmainwindow = QtWidgets.QMainWindow() # define buttons vehaviour self.button_actions: Dict[str, QtWidgets.QAction] = {} self.define_buttons() # UI variables self.label = QtWidgets.QLabel() self.combo_box = QtWidgets.QComboBox() self.table_model = QSqlRelationalTableModel() self.table_view = QtWidgets.QTableView() # create window and show it self.draw() self.qmainwindow.show() def draw(self) -> None: self.qmainwindow.setObjectName("MainWindow") self.qmainwindow.resize(800, 600) self.qmainwindow.setWindowTitle("Database manager") widget = QtWidgets.QWidget(self.qmainwindow) widget.setObjectName("widget") self.qmainwindow.setCentralWidget(widget) self.toolbar = self.qmainwindow.addToolBar("ToolBar") for _, action in self.button_actions.items(): self.toolbar.addAction(action) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 10), QtCore.QSize(105, 16))) _.setText("Database path: ") self.label = QtWidgets.QLabel(widget) self.label.setGeometry( QtCore.QRect(QtCore.QPoint(110, 10), QtCore.QSize(750, 16))) self.label.setText(self.dbpath) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 30), QtCore.QSize(105, 16))) _.setText("Choosen table: ") self.combo_box = QtWidgets.QComboBox(self.qmainwindow) self.combo_box.setGeometry( QtCore.QRect(QtCore.QPoint(100, 55), QtCore.QSize(200, 16))) self.combo_box.currentIndexChanged.connect(self.update_table) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 65), QtCore.QSize(105, 16))) _.setText("Table") self.table_view = QtWidgets.QTableView(self.qmainwindow) self.table_view.setObjectName("tableView") self.table_view.setGeometry( QtCore.QRect(QtCore.QPoint(10, 115), QtCore.QSize(780, 475))) self.table_view.setShowGrid(False) def define_buttons(self) -> None: def _exit_action() -> None: QtWidgets.qApp.quit() def _show_dialog() -> None: self.dbpath, _ = QtWidgets.QFileDialog.getOpenFileName( self, "Open file") self.label.setText(self.dbpath) def _load_database() -> None: self.db.setDatabaseName(self.dbpath) self.db.open() # update selectors self.combo_box.clear() for tbl in self.db.tables(): self.combo_box.addItem(tbl) self.update_table() def _save_database() -> None: self.table_model.submitAll() def _insert_row() -> None: self.table_model.insertRows(self.table_model.rowCount(), 1) def _remove_row() -> None: for idx in self.table_view.selectedIndexes(): self.table_model.removeRows(idx.row(), 1) self.button_actions.update( {"exit": QtWidgets.QAction("Exit", self.qmainwindow)}) self.button_actions["exit"].triggered.connect(_exit_action) self.button_actions.update({ "dbpath": QtWidgets.QAction("Set database path", self.qmainwindow) }) self.button_actions["dbpath"].triggered.connect(_show_dialog) self.button_actions.update( {"load": QtWidgets.QAction("Load database", self.qmainwindow)}) self.button_actions["load"].triggered.connect(_load_database) self.button_actions.update( {"save": QtWidgets.QAction("Save database", self.qmainwindow)}) self.button_actions["save"].triggered.connect(_save_database) self.button_actions.update( {"insert": QtWidgets.QAction("Insert row", self.qmainwindow)}) self.button_actions["insert"].triggered.connect(_insert_row) self.button_actions.update( {"remove": QtWidgets.QAction("Remove row", self.qmainwindow)}) self.button_actions["remove"].triggered.connect(_remove_row) def update_table(self) -> None: self.table_model = QSqlRelationalTableModel() tablename = self.combo_box.currentText() self.table_model.setTable(tablename) # foreign key logic if tablename == "goods": self.table_model.setRelation(2, QSqlRelation("units", "id", "unit")) self.table_model.setRelation( 3, QSqlRelation("categories", "id", "name")) elif tablename == "employees": self.table_model.setRelation( 2, QSqlRelation("positions", "id", "position")) elif tablename == "vendors": self.table_model.setRelation( 2, QSqlRelation("ownerships", "id", "ownership")) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) self.table_view.setModel(self.table_model) self.table_view.setItemDelegate(QSqlRelationalDelegate( self.table_view)) # define header width self.table_view.horizontalHeader().setStretchLastSection(False) self.table_view.resizeColumnsToContents() self.table_view.horizontalHeader().setMinimumSectionSize(50) self.table_view.horizontalHeader().setStretchLastSection(True)
class Cars_data(QWidget): def __init__(self): super().__init__() self.setObjectName("Main window") self.setGeometry(QRect(500, 300, 430, 300)) self.setWindowTitle("Транспортное средство") self.btn_add = QPushButton(self) self.btn_add.setObjectName("btn_add") self.btn_add.setText("Добавить") self.btn_delete = QPushButton(self) self.btn_delete.setObjectName("btn_delete") self.btn_delete.setText("Удалить") self.btn_exit = QPushButton(self) self.btn_exit.setObjectName("btn_exit") self.btn_exit.setText("Выход") self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('ocenka.db') self.model = QSqlRelationalTableModel(self) self.model.setTable('car_data') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.select() 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, "Пробег") self.model.setHeaderData(5, Qt.Horizontal, "Год выпуска") self.model.setHeaderData(6, Qt.Horizontal, "Страна производства") self.model.setHeaderData(7, Qt.Horizontal, "Место использования") self.model.setHeaderData(8, Qt.Horizontal, "Страна импортёр") self.model.setHeaderData(9, Qt.Horizontal, "Серия и ПТС") self.model.setHeaderData(10, Qt.Horizontal, "VIN") self.model.setHeaderData(11, Qt.Horizontal, "Номер двигателя") self.model.setHeaderData(12, Qt.Horizontal, "Номер кузова") self.view1 = self.createView("Table Model (View 1)", self.model) self.query = QSqlQuery(self.db) self.query.exec_("PRAGMA Foreign_keys = ON") layout = QHBoxLayout() layout.addWidget(self.btn_add) layout.addWidget(self.btn_delete) layout.addWidget(self.btn_exit) vlayout = QVBoxLayout() vlayout.addWidget(self.view1) vlayout.addLayout(layout) self.setLayout(vlayout) self.show() self.view1.clicked.connect(self.findrow) self.btn_add.clicked.connect(self.addrow) self.btn_delete.clicked.connect( lambda: self.model.removeRow(self.view1.currentIndex().row())) self.btn_exit.clicked.connect(self.close) def findrow(self, i): delrow = i.row() def addrow(self): print(self.model.rowCount()) ret = self.model.insertRows(self.model.rowCount(), 1) return ret def createView(self, title, model): self.model = model self.title = title view = QTableView(self) view.setModel(self.model) view.setWindowTitle(self.title) view.resizeColumnsToContents() view.setGeometry(25, 25, 380, 200) view.hideColumn(0) view.setItemDelegate(QSqlRelationalDelegate(view)) return view
class Materials(QWidget): def __init__(self): super().__init__() self.initUi() def initUi(self): self.setObjectName("Main window") self.resize(370, 300) self.setWindowTitle("Материалы") self.btn_add = QPushButton(self) self.btn_add.setObjectName("btn_add") self.btn_add.setText("Добавить") self.btn_delete = QPushButton(self) self.btn_delete.setObjectName("btn_delete") self.btn_delete.setText("Удалить") self.btn_exit = QPushButton(self) self.btn_exit.setObjectName("btn_exit") self.btn_exit.setText("Выход") self.btn_refresh = QPushButton(self) self.btn_refresh.setText("Обновить") self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('ocenka.db') self.db.open() self.model = QSqlRelationalTableModel(self) self.model.setTable("v_materials") self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setJoinMode(QSqlRelationalTableModel.LeftJoin) 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, "Стоимость") self.model.setHeaderData(5, Qt.Horizontal, "Сумма") self.model.setHeaderData(6, Qt.Horizontal, "Выбрать") #self.proxyModel = QSortFilterProxyModel(self) #self.proxyModel.setSourceModel(self.model) self.view = QTableView(self) self.view.setSortingEnabled(True) self.view.setModel(self.model) self.view.resizeColumnsToContents() self.view.hideColumn(0) self.view.horizontalHeader().setStretchLastSection(True) #self.view.hideColumn(1) self.view.setItemDelegate(QSqlRelationalDelegate(self.model)) self.model.select() self.layout = QHBoxLayout() self.layout.addWidget(self.btn_add) self.layout.addWidget(self.btn_delete) self.layout.addWidget(self.btn_exit) self.layout.addWidget(self.btn_refresh) vlayout = QVBoxLayout() vlayout.addWidget(self.view) vlayout.addLayout(self.layout) self.setLayout(vlayout) self.show() self.view.clicked.connect(self.findrow) self.btn_add.clicked.connect(self.addrow) self.btn_delete.clicked.connect(lambda: self.model.removeRow(self.view.currentIndex().row())) self.btn_exit.clicked.connect(self.close_event) self.btn_refresh.clicked.connect(lambda: self.model.select()) def close_database(self): self.view.setModel(None) del self.model self.db.close() del self.db QSqlDatabase.removeDatabase('ocenka.db') self.close() def close_event(self, event): self.close_database() def findrow(self, i): delrow = i.row() def addrow(self): print(self.model.rowCount()) ret = self.model.insertRows(self.model.rowCount(), 1) return ret
class Ui_MainWindow(object): def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(700, 600) MainWindow.setMinimumSize(QtCore.QSize(700, 600)) MainWindow.setMaximumSize(QtCore.QSize(700, 600)) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.btn_call = QtWidgets.QPushButton(self.centralwidget) self.btn_call.setGeometry(QtCore.QRect(558, 480, 131, 71)) self.btn_call.setObjectName("btn_call") font = QtGui.QFont() font.setPointSize(11) self.btn_call.setFont(font) self.btn_add = QtWidgets.QPushButton(self.centralwidget) self.btn_add.setGeometry(QtCore.QRect(10, 480, 89, 71)) self.btn_add.setStyleSheet("") self.btn_add.setObjectName("btn_add") font = QtGui.QFont() font.setPointSize(11) self.btn_add.setFont(font) self.btn_delete = QtWidgets.QPushButton(self.centralwidget) self.btn_delete.setGeometry(QtCore.QRect(120, 480, 89, 71)) self.btn_delete.setObjectName("btn_delete") font = QtGui.QFont() font.setPointSize(11) self.btn_delete.setFont(font) self.btn_search = QtWidgets.QPushButton(self.centralwidget) self.btn_search.setGeometry(QtCore.QRect(230, 480, 89, 71)) self.btn_search.setObjectName("btn_search") font = QtGui.QFont() font.setPointSize(11) self.btn_search.setFont(font) self.lineEdit = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit.setGeometry(QtCore.QRect(10, 434, 311, 31)) font = QtGui.QFont() font.setPointSize(14) self.lineEdit.setFont(font) self.lineEdit.setObjectName("lineEdit") self.comboBox = QtWidgets.QComboBox(self.centralwidget) self.comboBox.setGeometry(QtCore.QRect(335, 434, 121, 31)) self.comboBox.setObjectName("comboBox") font = QtGui.QFont() font.setPointSize(11) self.comboBox.setFont(font) self.tableView = QtWidgets.QTableView(self.centralwidget) self.tableView.setGeometry(QtCore.QRect(10, 10, 681, 411)) self.tableView.setObjectName("tableView") font = QtGui.QFont() font.setPointSize(11) self.tableView.setFont(font) MainWindow.setCentralWidget(self.centralwidget) self.menubar = QtWidgets.QMenuBar(MainWindow) self.menubar.setGeometry(QtCore.QRect(0, 0, 700, 22)) self.menubar.setObjectName("menubar") self.menuSetting = QtWidgets.QMenu(self.menubar) self.menuSetting.setObjectName("menuSetting") MainWindow.setMenuBar(self.menubar) self.statusbar = QtWidgets.QStatusBar(MainWindow) self.statusbar.setObjectName("statusbar") MainWindow.setStatusBar(self.statusbar) self.action_Invite_window = QtWidgets.QAction(MainWindow) self.action_Invite_window.setObjectName("action_Invite_window") # self.actionPlace_windows = QtWidgets.QAction(MainWindow) # self.actionPlace_windows.setObjectName("actionPlace_windows") self.actionini_py = QtWidgets.QAction(MainWindow) self.actionini_py.setObjectName("actionini_py") self.actionDestination = QtWidgets.QAction(MainWindow) self.actionDestination.setObjectName("actionDestination") self.actionDrop_tablel = QtWidgets.QAction(MainWindow) self.actionDrop_tablel.setObjectName("actionDrop_tablel") self.menuSetting.addAction(self.action_Invite_window) # self.menuSetting.addAction(self.actionPlace_windows) self.menuSetting.addAction(self.actionDestination) self.menuSetting.addAction(self.actionDrop_tablel) self.menuSetting.addSeparator() self.menuSetting.addAction(self.actionini_py) self.menubar.addAction(self.menuSetting.menuAction()) self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow) # เรียกฟังชั่นเพื่อโหลด model ลง table self.loadData() self.btn_add.clicked.connect(self.insertData) self.btn_delete.clicked.connect(self.deleteData) self.btn_call.clicked.connect(self.speak) self.btn_search.clicked.connect(self.searchData) self.actionDestination.triggered.connect(self.show_desDialog) def show_desDialog(self): self.desDialog.show() def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "Simple Queue")) self.btn_call.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อเรียกคิวที่เลือกจากตารางคิว</p></body></html>" )) self.btn_call.setText(_translate("MainWindow", "เรียกคิว")) self.btn_add.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อเปิดเมนูการเพิ่มคิวด้วยตัวเอง</p></body></html>" )) self.btn_add.setText(_translate("MainWindow", "เพิ่มคิว")) self.btn_delete.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อลบคิวที่ป้อนค่าผิดออกจากตารางคิว</p></body></html>" )) self.btn_delete.setText(_translate("MainWindow", "ลบคิว")) self.btn_search.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อโหลดตารางคิวใหม่</p></body></html>" )) self.btn_search.setText(_translate("MainWindow", "ค้นหาคิว")) self.menuSetting.setTitle(_translate("MainWindow", "ตั้งค่า")) self.action_Invite_window.setText( _translate("MainWindow", "Call sentence setting")) #self.actionPlace_windows.setText(_translate("MainWindow", "Place sentence")) self.actionini_py.setText(_translate("MainWindow", "ini.py")) self.actionDestination.setText( _translate("MainWindow", "Destination setting")) self.actionDrop_tablel.setText(_translate("MainWindow", "Drop tablel")) def loadData(self): self.lineEdit.clear() self.lineEdit.setFocus() # สร้าง model เรียก data จาก database # ใช้ QSqlRelationalTableModel สำหรับตารางที่มีคีย์นอก self.model = QSqlRelationalTableModel() self.model.setTable('queue') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setHeaderData(0, Qt.Horizontal, 'ID') self.model.setHeaderData(1, Qt.Horizontal, "รายชื่อ") self.model.setHeaderData(2, Qt.Horizontal, "เวลาเพิ่มคิว") self.model.setHeaderData(3, Qt.Horizontal, "เวลาเรียกคิว") self.model.setHeaderData(4, Qt.Horizontal, "สถาณะคิว") self.model.setHeaderData(5, Qt.Horizontal, "ปลายทาง") self.model.setHeaderData(6, Qt.Horizontal, "Option") # ให้ column#5 เป็นคีย์นอกดึงตารางนอกมาแสดง self.model.setRelation( 5, QSqlRelation("destination", "des_id", "des_name")) # เรียกใช้ model เรียกใช้ได้จากทุกที่ใน class ไม่ต้องเรียก loadData() self.model.select() # ให้ tableView เลือก data จาก model ไปแสดง self.tableView.setModel(self.model) self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView)) self.tableView.setColumnHidden(0, True) self.tableView.setCornerButtonEnabled(False) self.tableView.setSortingEnabled(True) self.tableView.setColumnWidth(1, 210) # เรียก fetchData เพื่อ fetch data เข้ามาใน model ให้หมด self.fetchData() # เมื่อ click ให้เลือกทั้งแถว self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) # สร้าง model เรียกตาราง destination แล้วยัดเข้า combobox self.desModel = QSqlQueryModel() selectQuery = QSqlQuery() selectQuery.prepare('SELECT des_name FROM destination') # print('QUERY = ' + str(selectQuery.lastQuery())) if selectQuery.exec(): self.desModel.setQuery(selectQuery) # print('SELECT des_name COMPLETE') self.comboBox.setModel(self.desModel) else: print('SELECT FALSE = ' + selectQuery.lastError().text()) # สร้าง obj ของ destination setting ไว้รอเรียก self.desDialog = QtWidgets.QDialog() self.ui = destination2.Ui_Dialog() self.ui.setupUi(self.desDialog) self.tableView.selectRow(0) def insertData(self): q_number = self.lineEdit.text() q_localtime = time.localtime() q_enter_time = time.strftime("%H:%M:%S", q_localtime) # q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง if not q_number.isspace() and q_number != '': # เรียก getDestination_id เพื่อหาค่า des_id เพื่อใช้ในคำสั่ง sql INSERT des_id = self.getDestination_id() try: insertQuery = QSqlQuery() insertQuery.prepare( "INSERT INTO queue " + "(q_number,q_enter_time,des_id) " + "VALUES " + f"('{q_number}','{q_enter_time}',{des_id})") print('Query = ' + insertQuery.lastQuery()) if insertQuery.exec(): print('INSERT COMPLETE') self.loadData() else: print('INSERT FALSE = ' + insertQuery.lastError().text()) except (Error) as e: print( str(time.strftime("%H:%M:%S : ", time.localtime())) + 'ERROR :' + str(e)) else: self.showDialog('กรุณากรอกหมายเลขที่จะเพิ่มก่อน') def getDestination_id(self): # สร้างมาเพื่อให้ return ค่า des_id ในตาราง destination กลับไป temp = self.comboBox.currentText() desModel = QSqlQueryModel() selectQuery = QSqlQuery() selectQuery.prepare('SELECT des_id,des_name From destination') if selectQuery.exec(): desModel.setQuery(selectQuery) for i in range(desModel.rowCount()): if temp == desModel.index(i, 1).data(): return desModel.index(i, 0).data() else: pass else: print('SELECT FALSE = ' + selectQuery.lastError().text()) def insertDev(self): j = 10 for i in range(50): q_number = random.randint(0, 100000) q_enter_time = f'18:{j}:00' des_id = 4 insertQuery = QSqlQuery() insertQuery.prepare("INSERT INTO queue " + "(q_number,q_enter_time,des_id) " + "VALUES " + f"('{q_number}','{q_enter_time}',{des_id})") insertQuery.exec() self.model.select() j += 1 print('insertDEV complete') def searchData(self): self.fetchData() q_number = self.lineEdit.text() totleRow = self.tableView.model().rowCount() foundStatus = False # q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง if not q_number.isspace() and q_number != '': i = 0 for i in range(totleRow): # เก็บค่า q_number จาก tableView s_number = self.tableView.model().data( self.tableView.model().index(i, 1)) # เทียบค่า q_number ของ lineEdit กับ tableView if q_number == s_number: # print('found this number.') foundStatus = True break else: foundStatus = False if foundStatus == True: self.tableView.selectRow(i) else: self.showDialog('ค้นหมายเลข ' + q_number + ' ไม่พบ') else: self.showDialog('กรุณากรอกหมายเลขที่จะค้นก่อน') def deleteData(self): try: # เก็บค่า text จาก column current_row = self.tableView.selectedIndexes() current_itemUse = current_row[0] q_number = self.tableView.model().data( self.tableView.model().index(current_itemUse.row(), 1)) except: self.showDialog('กรุณาเลือกหมายเลขที่จะลบก่อน') return if q_number == None: self.showDialog('เกิดข้อผิดพลาดระหว่างการลบ') self.model.select() return # ยืนยันการลบด้วย code 1024 temp = self.confDelete(q_number) if temp == 1024: current_item = self.tableView.selectedIndexes() for index in current_item: self.model.removeRow(index.row()) self.model.select() print('Record deleted.') self.showDialog('ลบหมายเลขแล้ว') else: print('User cancel delete this record.') def confDelete(self, q_number): # สร้าง dialog เพื่อยืนยันการลบ และ return code ยืนยันการลบด้วย code 1024 confMsg = QtWidgets.QMessageBox() confMsg.setIcon(QtWidgets.QMessageBox.Critical) confMsg.setText('ยืนยันที่จะลบหมายเลข : ' + q_number) confMsg.setWindowTitle('แจ้งเตือนการลบ') confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel) reval = confMsg.exec_() return (reval) def btn_delClicked(self): button = QtGui.QGuiApplication.focusObject() index = self.tableView.indexAt(button.pos()) if index.isValid(): print(index.row(), index.column()) def showDialog(self, message): msg = QtWidgets.QMessageBox() msg.setWindowTitle('แจ้งเตือน') msg.setIcon(QtWidgets.QMessageBox.Information) msg.setText( # message + str(time.strftime(" @ %H:%M:%S ", time.localtime()))) message) msg.exec_() def speak(self): try: # เก็บค่า text จาก column current_row = self.tableView.selectedIndexes() current_inUse = current_row[0] q_numberText = self.tableView.model().data( self.tableView.model().index(current_inUse.row(), 1)) des_idText = self.tableView.model().data( self.tableView.model().index(current_inUse.row(), 5)) except: self.showDialog('กรุณาเลือกหมายเลขที่จะเรียกก่อน') return # ยืนยันการเรียกด้วย code 1024 temp = self.confSpeak(q_numberText) q_localtime = time.localtime() q_call_time = time.strftime("%H:%M:%S", q_localtime) if temp == 1024: # pyttsx3 engine # engine = pyttsx3.init() # """ RATE""" # rate = engine.getProperty('rate') # #print (rate) # engine.setProperty('rate', 100) # """VOLUME""" # volume = engine.getProperty('volume') # #print (volume) # engine.setProperty('volume',1.0) # """VOICE""" # voices = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Speech\Voices\Tokens\TTS_THAI' # engine.setProperty('voice',voices) # engine.say('ขอเชิญหมายเลข' + q_numberText + 'ที่ช่อง' + des_idText + 'นะครับ') # print('result = :'+ q_numberText) # engine.runAndWait() # engine.stop() # gTTS engine speech_text = 'ขอเชิญคุณ ' + q_numberText + ' ที่ช่อง ' + des_idText + ' ค่ะ' tts = gTTS(speech_text, lang='th') tts.save('temp.mp3') speech_file = pyglet.media.load('temp.mp3', streaming=False) speech_file.play() sleep(speech_file.duration) # prevent from killing os.remove('temp.mp3') # remove temperory file try: insertQuery = QSqlQuery() insertQuery.prepare("UPDATE queue SET q_call_time = " + f"'{q_call_time}'" + " , q_call_status = 1 WHERE q_number = " + f"'{q_numberText}'") print('Query after speek ' + insertQuery.lastQuery()) if insertQuery.exec(): print('UPDATE COMPLETE') self.loadData() else: print('UPDATE FALSE = ' + insertQuery.lastError().text()) except (Error) as e: print('ERROR : ' + str(e)) else: print('User decided cancel call.') def confSpeak(self, q_numberText): # สร้าง dialog เพื่อยืนยันการเรียกและ return code ยืนยันการเรียกด้วย code 1024 confMsg = QtWidgets.QMessageBox() confMsg.setIcon(QtWidgets.QMessageBox.Warning) confMsg.setText('ยืนยันที่จะเรียกหมายเลข : ' + q_numberText) confMsg.setWindowTitle('แจ้งเตือน') confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel) reval = confMsg.exec_() return (reval) def fetchData(self): self.model.select() # fetch ข้อมูลจากตารางออกมาให้หมด มีผลตอนใช้ search while self.model.canFetchMore() == True: self.model.fetchMore() print('rowCoundt = ' + str(self.model.rowCount())) self.tableView.scrollToBottom()
class Window(QMainWindow): def __init__(self): super().__init__() self.ui = mainform.Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'db/db_test.sqlite3' self.ui.lbl_full_path_db.setText(self.db_path) self.ui.btn_db_open.clicked.connect(self.db_file_open) self.ui.btn_add.clicked.connect(self.db_add) self.ui.btn_add.setEnabled(False) self.ui.btn_del.clicked.connect(self.db_del) self.ui.btn_del.setEnabled(False) self.ui.save_changes.clicked.connect(self.save_change_db) self.ui.save_changes.setEnabled(False) self.ui.mnu_exit.triggered.connect(self.close) self.ui.tablelist.setEnabled(False) self.db = None self.db_model = None self.open_db() self.ui.tablelist.currentIndexChanged.connect(self.show_table) def open_db(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_tables_name() # self.show_table() def get_tables_name(self): self.ui.tablelist.clear() for table_name in self.db.tables(): self.ui.tablelist.addItem(table_name) def db_file_open(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.lbl_full_path_db.setText(self.db_path) self.db.close() self.open_db() self.ui.tablelist.setEnabled(True) self.ui.btn_add.setEnabled(True) self.ui.btn_del.setEnabled(True) self.ui.save_changes.setEnabled(True) self.ui.btn_db_open.setEnabled(False) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.tablelist.currentText() if table == 'goods': self.create_goods_table_model() elif table == 'employees': self.create_employees_table_model() elif table == 'units': self.create_units_table_model() elif table == 'vendors': self.create_vendors_table_model() elif table == 'positions': self.create_positions_table_model() elif table == 'categories': self.create_categories_table_model() else: self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.hideColumn(0) view.setItemDelegate(QSqlRelationalDelegate(view)) def db_add(self): self.table_model.insertRows(self.table_model.rowCount(), 1) def db_del(self): rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) print(rs) for i in rs: self.table_model.removeRows(i, 1) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation( 2, QSqlRelation('units', 'unit_id', 'unit_name')) self.table_model.setRelation( 3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.setRelation( 4, QSqlRelation('vendors', 'vendor_id', 'vendor_name')) self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Название продукта') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Единица измерения') self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Категория продукта') self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Поставщик') self.table_model.select() def create_units_table_model(self): self.table_model.setTable('units') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Единица измерения') self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation( 2, QSqlRelation('positions', 'position_id', 'position_name')) self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'ФИО сотрудника') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность') self.table_model.select() def create_positions_table_model(self): self.table_model.setTable('positions') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Должность') self.table_model.select() def create_vendors_table_model(self): self.table_model.setTable('vendors') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Наименование') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Форма собственности') self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес') self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Телефон') self.table_model.setHeaderData(5, QtCore.Qt.Horizontal, 'Электронная почта') self.table_model.select() def create_categories_table_model(self): self.table_model.setTable('categories') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Название категории') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Описание категории') self.table_model.select() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage( f'{self.table_model.lastError().text()}')
class Components(object): def __init__(self, connection): self.connection = connection self.model = QSqlRelationalTableModel() self.model.setTable('customnetwork_component') self.model.setEditStrategy(QSqlTableModel.OnManualSubmit) self.model.setRelation(0, QSqlRelation('component', 'id', 'ticket')) self.model.setRelation(1, QSqlRelation('customnetwork', 'id', 'name')) self.model.select() self.model.setHeaderData(0, Qt.Horizontal, "Ticket") def getModel(self): return self.model def create(self, customnetwork_id, tickets): # TODO: capturar excepciones de error que se pueden dar log.debug(tickets) query = QSqlQuery() last = self.model.rowCount() id = last + 1 self.connection.open() for ticket in tickets: comp_id = self._insert_component_table(query, ticket) ok = self.\ _insert_customernetwork_component_table(query, customnetwork_id, comp_id) self.model.select() self.connection.close() return id def _insert_customernetwork_component_table(self, query, customnetwork_id, comp_id): query.prepare('insert into customnetwork_component values (?,?);') query.addBindValue(comp_id) query.addBindValue(customnetwork_id) return query.exec_() def _insert_component_table(self, query, ticket): ok = query.exec_("select id from component;") id = 0 if ok: query.last() last_id = int(query.value(0)) id = last_id + 1 query.prepare('insert into component values (?,?,?,?);') query.addBindValue(id) query.addBindValue(ticket) query.addBindValue('_generic_description_') query.addBindValue(GENERIC_SECTOR_CODE_ID) if query.exec_(): return id else: return -1 def update_components(self, id, tickets): # TODO: capturar excepciones de error que se pueden dar query = QSqlQuery() self.connection.open() sql = """DELETE FROM customnetwork_component WHERE customnetwork_id = ?; """ query.prepare(sql) query.addBindValue(id) ok = query.exec_() if ok: for ticket in tickets: sql = "SELECT id, ticket FROM component WHERE ticket = '{}';"\ .format(ticket) if query.exec_() and query.next(): component_id = int(query.value(0)) query.clear() sql = """INSERT into customnetwork_component VALUES (?, ?); """ query.prepare(sql) query.addBindValue(component_id) query.exec_() else: comp_id = self._insert_component_table(query, ticket) ok = self.\ _insert_customernetwork_component_table(query, id, comp_id) self.model.select() self.connection.close() return ok def delete_cnc(self, customnetwork_id): query = QSqlQuery() self.connection.open() query_str = 'delete from customnetwork_component where ' ' customnetwork_id = ?;' query.prepare(query) query.addBindValue(customnetwork_id) ok = query.exec_() self.model.select() self.connection.close() return ok
class Client: def __init__(self): # init GUI self.table_name = None app = QtWidgets.QApplication(sys.argv) MainWindow = QtWidgets.QMainWindow() self.ui = Ui_MainWindow() self.ui.setupUi(MainWindow) MainWindow.show() # parameters self.db_path = None self.db = None # events self.ui.openDbPushButton.clicked.connect(self.open_database) self.ui.saveDbPushButton.clicked.connect(self.save_change_db) self.ui.addRowPushButton.clicked.connect(self.add_row) self.ui.deleteRowPushButton.clicked.connect(self.del_row) self.ui.tablesListView.itemDoubleClicked.connect(self.change_table) sys.exit(app.exec_()) def open_database_dialog(self): try: self.db_path = QFileDialog.getOpenFileName(self.ui.MainWindow, "Open file", '.')[0] print(self.db_path) self.ui.path_db.setText(self.db_path) self.db.close() except Exception as e: pass def open_database(self): try: self.open_database_dialog() self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_tables_name() self.show_table() except Exception as err: print(err) def get_tables_name(self): self.ui.tablesListView.clear() for table_name in self.db.tables(): self.ui.tablesListView.addItem(table_name) def change_table(self, name): self.table_name = self.ui.tablesListView.currentItem().text() self.show_table() def show_table(self): self.table_model = QSqlRelationalTableModel() if self.table_name: table = self.table_name else: table = [str(self.ui.tablesListView.item(i).text()) for i in range(self.ui.tablesListView.count())][0] self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableViewerTableView view.setModel(self.table_model) view.setItemDelegate(QSqlRelationalDelegate(view)) def add_row(self): self.table_model.insertRows(self.table_model.rowCount(), 1) def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Changes were saved') else: self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}') def del_row(self): rs = list(map(lambda x: x.row(), self.ui.tableViewerTableView.selectedIndexes())) print(rs) for i in rs: self.table_model.removeRows(i, 1) self.ui.statusbar.showMessage("Row was deleted")
def init_tag(self): #Selected tags when item is selected self.selected_tags = set() tag_model = QSqlTableModel() tag_model.setTable("tag") tag_model.setEditStrategy(QSqlTableModel.OnRowChange) #It can't be sorted otherwise wrong tags are selected when item is selected #tag_model.setSort(1, Qt.AscendingOrder) tag_model.select() #Added proxy model which is sorted on name tag_proxy_model = QSortFilterProxyModel(self) tag_proxy_model.setSourceModel(tag_model) tag_proxy_model.sort(1, Qt.AscendingOrder) self.tag_proxy_model = tag_proxy_model self.tag_model = tag_model tag_item_model = QSqlRelationalTableModel() tag_item_model.setTable("tag_item") #tag_item_model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) #tag_item_model.setRelation(2, QSqlRelation('tag', 'id', 'name')) tag_item_model.setEditStrategy(QSqlTableModel.OnManualSubmit) #tag_item_model.select() self.tag_item_model = tag_item_model tag_hier_model = QSqlRelationalTableModel() tag_hier_model.setTable("tag_hierarchy") tag_hier_model.setRelation(0, QSqlRelation('tag', 'id', 'name')) tag_hier_model.setRelation(1, QSqlRelation('tag', 'id', 'name')) tag_hier_model.setSort(0, Qt.AscendingOrder) tag_hier_model.setEditStrategy(QSqlTableModel.OnManualSubmit) tag_hier_model.select() #From add food self.cb_tag_select.setModel(tag_proxy_model) self.cb_tag_select.setModelColumn(1) self.tv_tag.setModel(tag_model) #self.tv_tag_item.setModel(tag_item_model) self.tv_tag_hierarchy.setModel(tag_hier_model) #ListView to choose which tags have nutrition self.lv_tag.setModel(tag_proxy_model) self.lv_tag.setModelColumn(1) #self.tv_tag_item.setItemDelegate(QSqlRelationalDelegate(self.tv_tag_item)) self.tv_tag_hierarchy.setItemDelegate( QSqlRelationalDelegate(self.tv_tag_hierarchy)) def add_tag(): print("Adding tag:") row = self.tv_tag.model().rowCount() self.tv_tag.model().insertRow(row) def add_hier(): print("Adding hier:") row = self.tv_tag_hierarchy.model().rowCount() self.tv_tag_hierarchy.model().insertRow(row) def add_item_tag(): print("Adding item_tag:") row = self.tv_tag_item.model().rowCount() self.tv_tag_item.model().insertRow(row) def save_hier(): print("Saving hier") self.tv_tag_hierarchy.model().submitAll() def select_nutri_tag(selectedIndex): ndbno = self._get_selected_ndbno(self.cb_item_tag.model() \ .record(self.cb_item_tag.currentIndex())) print("Item:", self.cb_item_tag.currentText(), ndbno) tags = self.session.query(TagItem) \ .filter(TagItem.ndbno==ndbno) \ .order_by(TagItem.tag_id) if self.lv_tag.selectionModel(): self.lv_tag.selectionModel().clearSelection() self.selected_tags.clear() for tag in tags: #print (tag.tag.name, tag.tag_id) orig_index = tag_model.createIndex(tag.tag_id, 1) index = tag_proxy_model.mapFromSource(orig_index) if index.isValid(): print("Valid index") self.lv_tag.selectionModel().select(index, QItemSelectionModel.Select) data = self.lv_tag.model().data(index) self.selected_tags.add(tag.tag_id) #print(data) def save_tag_item(): print("Saving tag item") selected_tags_now = set() for index in self.lv_tag.selectedIndexes(): mapped = self.lv_tag.model().mapToSource(index) record = tag_model.record(mapped.row()) selected_tags_now.add(record.field("id").value()) print( str(record.field("id").value()) + " - " + record.field("name").value()) added_tag_ids = selected_tags_now.difference(self.selected_tags) removed_tag_ids = self.selected_tags.difference(selected_tags_now) #print ("Unselected tags:" + ", ".join(map(lambda x: str(x), #removed_tag_ids))) #print ("New selected tags:" + ", ".join(map(lambda x: str(x), #added_tag_ids))) ndbno = self._get_selected_ndbno(self.cb_item_tag.model() \ .record(self.cb_item_tag.currentIndex())) print("Item:", self.cb_item_tag.currentText(), ndbno) query = QSqlQuery() ret = query.prepare( "DELETE FROM tag_item WHERE ndbno = :ndbno and tag_id = :tag_id") if not ret: print("Failed to prepare query") for tag_id in added_tag_ids: row = self.tag_item_model.rowCount() self.tag_item_model.insertRow(row) self.tag_item_model.setData( self.tag_item_model.createIndex(row, 0), ndbno, Qt.EditRole) self.tag_item_model.setData( self.tag_item_model.createIndex(row, 1), tag_id, Qt.EditRole) print("Adding tag id:", tag_id) #print (record.field("name").value(), record.field("id").value()) #self.tv_tag_item.model().submitAll() if not self.tag_item_model.submitAll(): QMessageBox.critical( None, "Error submitting", "Couldn't update model: " + self.tag_item_model.lastError().text()) self.tag_item_model.revertAll() query.bindValue(":ndbno", ndbno) for tag_id in removed_tag_ids: query.bindValue(":tag_id", tag_id) print("Removing tag_id:", tag_id) if not query.exec_(): print("Failed to executr query") #print (query.executedQuery()) self.pb_add_tag.pressed.connect(add_tag) self.pb_add_item_tag.pressed.connect(add_item_tag) self.pb_add_hier.pressed.connect(add_hier) self.pb_save_hier.pressed.connect(save_hier) self.pb_save_tag_item.pressed.connect(save_tag_item) self.cb_item_tag.currentIndexChanged.connect(select_nutri_tag)
class MyWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'database\sklad_db.sqlite3' self.ui.path_db.setText(self.db_path) self.save_db = QAction(QIcon('icon/save.png'), 'Сохранить', self) self.find_db_file = QAction(QIcon('icon/open_db.png'), 'Открыть БД', self) self.add_rec = QAction(QIcon('icon/add.png'), 'Добавить запись', self) self.del_rec = QAction(QIcon('icon/del.png'), 'Удалить запись', self) self.ui.toolBar.addAction(self.save_db) self.ui.toolBar.addAction(self.find_db_file) self.ui.toolBar.addAction(self.add_rec) self.ui.toolBar.addAction(self.del_rec) self.db = None self.table_model = None self.open_db() self.add_rec.triggered.connect(self.add_record_action) self.del_rec.triggered.connect(self.del_record_action) self.save_db.triggered.connect(self.save_change_db) self.find_db_file.triggered.connect(self.find_db_file_action) self.ui.comboBox.currentIndexChanged.connect(self.show_table) def open_db(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_table_name() self.show_table() def get_table_name(self): self.ui.comboBox.clear() table_name_ru = None for table_name in self.db.tables(): if table_name == 'goods': table_name_ru = 'Товары' elif table_name == 'categories': table_name_ru = 'Категории' elif table_name == 'units': table_name_ru = 'Единица измерения' elif table_name == 'employees': table_name_ru = 'Персонал' elif table_name == 'positions': table_name_ru = 'Должности' elif table_name == 'vendors': table_name_ru = 'Поставщики' self.ui.comboBox.addItem(table_name_ru) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.comboBox.currentText() if table == 'Товары': self.create_goods_table_model() elif table == 'Персонал': self.create_employees_table_model() else: if table == 'Единица измерения': table = 'units' elif table == 'Категории': table = 'categories' elif table == 'Должности': table = 'positions' elif table == 'Поставщики': table = 'vendors' self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.setItemDelegate(QSqlRelationalDelegate(view)) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit')) self.table_model.setRelation( 3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation( 2, QSqlRelation('positions', 'position_id', 'position')) self.table_model.select() def add_record_action(self): self.ui.statusbar.clearMessage() self.table_model.insertRows(self.table_model.rowCount(), 1) def del_record_action(self): self.ui.statusbar.clearMessage() rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) #print(rs) for i in rs: self.table_model.removeRows(i, 1) def find_db_file_action(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.path_db.setText(self.db_path) self.db.close() self.open_db() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage( f'{self.table_model.lastError().text()}')
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())
def init_best_before(self): model = QSqlRelationalTableModel() model.setTable("best_before") model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) model.select() #Nutrition table needs to be populated since otherwise only 256 rows are read #And all inserts with ndbno > 100169 fail since they aren't found in nutrition #table nutri_model = model.relationModel(1) while nutri_model.canFetchMore(): nutri_model.fetchMore() self.bb_model = model nutri_model = self.lv_keys.model() #nutri_model = QSqlTableModel() #nutri_model.setTable("nutrition") ##nutri_model.setRelation(2, QSqlRelation('nutrition', 'ndbno', 'desc')) #nutri_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) #nutri_model.setSort(1,Qt.AscendingOrder) #nutri_model.select() self.cb_bb_item.setModel(nutri_model) self.cb_bb_item.setModelColumn(0) self.best_before_model = model self.tv_best_before.setModel(model) self.tv_best_before.setSortingEnabled(True) self.tv_best_before.sortByColumn(2, Qt.AscendingOrder) self.tv_best_before.setItemDelegate(QSqlRelationalDelegate(self.tv_best_before)) self.tv_best_before.show() #From Price self.cb_price_item.setModel(nutri_model) self.cb_price_item.setModelColumn(0) #From Tag self.cb_item_tag.setModel(nutri_model) self.cb_item_tag.setModelColumn(0) """Updates Best before table""" def update_bb(): print ("Updating Best Before") if not self.bb_model.submitAll(): QMessageBox.critical(None, "Error updating Best Before", "Couldn't update model: " + self.bb_model.lastError().text()) """Adds new data to best before table update_bb also needs to be called""" def add_bb(): print("Adding to BB") ndbno = self._get_selected_ndbno(self.cb_bb_item.model() \ .record(self.cb_bb_item.currentIndex())) print ("IDX:", self.cb_bb_item.currentIndex(), ndbno) row = self.bb_model.rowCount() self.bb_model.insertRow(row) #print ("NDBNO INDEX:", self.bb_model.fieldIndex("desc")) r = self.bb_model.record() #for i in range(r.count()): #print ("{} => {}".format(i,r.fieldName(i))) #for i in range(100000,100194): #out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, #self.bb_model.fieldIndex("desc")), i, #Qt.EditRole) #print ("{}? = {}".format(out_ndbno, i)) out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("desc")), ndbno, Qt.EditRole) out_time = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("time")), self.de_bb.date(), Qt.EditRole) print ("NDBNO:", out_ndbno, "TIME:", out_time) self.update_bb = update_bb self.add_bb = add_bb self.buttonBox_3.button(QDialogButtonBox.SaveAll).clicked.connect(self.update_bb) self.buttonBox_3.button(QDialogButtonBox.Apply).clicked.connect(self.add_bb)
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.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) 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) self.selModel.currentChanged.connect(self.do_currentChanged) 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() 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) @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_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_actRecInsert_triggered(self): curIndex = self.ui.tableView.currentIndex() 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): self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) self.ui.actRevert.setEnabled(self.tabModel.isDirty())
class MyWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) # Собираем окно self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'database/database.sqlite3' self.ui.path_db.setText(self.db_path) # Собираем кнопки self.save_db = QAction(QIcon('icon/save.png'), 'Save', self) self.open_db_file = QAction(QIcon('icon/open-db.png'), 'Open', self) self.add_row = QAction(QIcon('icon/add.png'), 'Add row', self) self.del_row = QAction(QIcon('icon/del.png'), ' Del row', self) self.ui.toolBar.addAction(self.save_db) self.ui.toolBar.addAction(self.open_db_file) self.ui.toolBar.addAction(self.add_row) self.ui.toolBar.addAction(self.del_row) self.db = None self.table_model = None # Подключаемся к БД self.open_db() # действуем по триггерам self.add_row.triggered.connect(self.add_row_action) self.del_row.triggered.connect(self.del_row_action) self.save_db.triggered.connect(self.save_change_db) self.open_db_file.triggered.connect(self.open_db_file_action) self.ui.comboBox.currentIndexChanged.connect(self.show_table) def open_db(self): # Подключение к БД self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_tables_name() self.show_table() def get_tables_name(self): self.ui.comboBox.clear() for table_name in self.db.tables(): self.ui.comboBox.addItem(table_name) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.comboBox.currentText() if table == 'goods': self.create_goods_table_model() elif table == 'employees': self.create_employees_table_model() else: self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.setItemDelegate(QSqlRelationalDelegate(view)) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit')) self.table_model.setRelation(3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation(2, QSqlRelation('positions', 'position_id', 'position')) self.table_model.select() def add_row_action(self): self.table_model.insertRows(self.table_model.rowCount(), 1) def del_row_action(self): rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) print(rs) for i in rs: self.table_model.removeRows(i, 1) def open_db_file_action(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.path_db.setText(self.db_path) self.db.close() self.open_db() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
# объект приложения APP = QApplication(sys.argv) # виджет-окно W_OBJ = QWidget() W_OBJ.setWindowTitle("Связь с внешней таблицей") # соединяемся с БД CONN = QSqlDatabase.addDatabase('QSQLITE') CONN.setDatabaseName('test.sqlite3') CONN.open() # модель-связанная таблица R_T_MODEL = QSqlRelationalTableModel(parent=W_OBJ) R_T_MODEL.setEditStrategy(QSqlTableModel.OnManualSubmit) R_T_MODEL.setTable('product') R_T_MODEL.setSort(1, QtCore.Qt.AscendingOrder) # Задаем для поля категории связь с таблицей списка категорий R_T_MODEL.setRelation(1, QSqlRelation('category', 'id', 'name')) # 'category', 'id', 'category' (имя первичной таблицы, # имя поля первичного ключа, имя поля, выводящегося на экран) R_T_MODEL.select() R_T_MODEL.setHeaderData(1, QtCore.Qt.Horizontal, 'Категория товара') R_T_MODEL.setHeaderData(2, QtCore.Qt.Horizontal, 'Название товара') R_T_MODEL.dataChanged.connect(R_T_MODEL.submitAll) # Размещаем объекты VBOX_OBJ = QVBoxLayout() TV_OBJ = QTableView()
class StampTableWidget(StampTableView): addStampSignal = pyqtSignal(str) # deleteStampSignal = pyqtSignal() surveyModeSignal = pyqtSignal(bool) def __init__(self): super().__init__() self.hasSelection = False # todo self.surveyDatetime = None # init table model self.stampTableModel = QSqlRelationalTableModel() self.stampTableModel.setTable(R.tableName) self.stampTableModel.setEditStrategy(QSqlTableModel.OnFieldChange) self.stampTableModel.setFilter("survey_datetime = None") self.stampTableModel.select() self.stampTableView.setModel(self.stampTableModel) self.stampTableSelectionModel = self.stampTableView.selectionModel() self.stampTableSelectionModel.selectionChanged.connect( self.onStampSelectionChange) # connect buttons self.addStampButton.clicked.connect( lambda ignore, key="-": self.addStampSignal.emit(key)) self.deleteStampButton.clicked.connect(self.deleteButtonAction) self.hotkeyButton.clicked.connect(self.enableHotkeys) # self.recordButton.clicked.connect(self.surveyMode) # hotkeys self.hotkeys = self.initHotkeys() # copy selection to clipboard self.installEventFilter(self) def eventFilter(self, source, event): if (event.type() == QEvent.KeyPress and event.matches(QKeySequence.Copy)): self.copySelection() return True return super().eventFilter(source, event) def copySelection(self): selection = self.stampTableView.selectedIndexes() if selection: rows = sorted(index.row() for index in selection) columns = sorted(index.column() for index in selection) rowcount = rows[-1] - rows[0] + 1 colcount = columns[-1] - columns[0] + 1 table = [[''] * colcount for _ in range(rowcount)] for index in selection: row = index.row() - rows[0] column = index.column() - columns[0] table[row][column] = index.data() stream = io.StringIO() csv.writer(stream).writerows(table) QApplication.clipboard().setText(stream.getvalue()) def initHotkeys(self): keyList = [] shortcut = QShortcut( QKeySequence(Qt.Key_Space, QKeySequence.NativeText), self) keyList.append(shortcut) shortcut.activated.connect( lambda key="-": self.addStampSignal.emit(key)) for i in range(0, 10): print(i) shortcut = QShortcut(QKeySequence(str(i), QKeySequence.NativeText), self) keyList.append(shortcut) shortcut.activated.connect( lambda key=str(i): self.addStampSignal.emit(key)) return keyList def enableHotkeys(self, isClicked): for shortcut in self.hotkeys: shortcut.setEnabled(isClicked) def onDeselection(self): self.enableHotkeys(False) self.hotkeyButton.setChecked(False) # TODO delete def testHotKey(self, key): print("test") print(key) # TODO maybe repaint def loadSurveyStamps(self, keys): print("LSS") self.surveyDatetime = keys[0] filter = self.createFilter(keys) self.stampTableModel.setFilter(filter) self.stampTableModel.select() print("survey_datetime = " + self.surveyDatetime) QApplication.processEvents() self.repaint() def createFilter(self, keys): filter = "survey_datetime = \"" + self.surveyDatetime + "\"" for i in range(1, len(keys)): filter = filter + " OR survey_datetime = \"" + keys[i] + "\"" print(filter) return filter @pyqtSlot() def deleteButtonAction(self): for row in self.stampTableSelectionModel.selectedRows(): self.stampTableModel.removeRow(row.row()) self.stampTableSelectionModel.clearSelection() self.stampTableModel.select() QApplication.processEvents() def clearSurveyStamps(self): self.surveyDatetime = None # self.stampTableModel.setFilter("") # TODO will this work? self.stampTableModel.setFilter("survey_datetime = None") print("survey_datetime = None") def addStamp(self, stamp, key="-"): stampRecord = StampRecord(miliseconds=stamp, surveyDatetime=self.surveyDatetime, label=key, note="") sqlRecord = stampRecord.getQSQLRecord(self.stampTableModel.record()) self.stampTableModel.insertRecord(0, sqlRecord) # self.surveyWidget.sortByKey() # self.surveyWidget.select(0) QApplication.processEvents() # allow for selection highlight def addRecord(self, stampRecord): sqlRecord = stampRecord.getQSQLRecord(self.stampTableModel.record()) print(self.stampTableModel.insertRowIntoTable(sqlRecord)) # self.repaint() # TODO # self.surveyModeSignal.emit(modeOn) # @pyqtSlot(bool) # def enableSurvey(self, canSurvey): # print(canSurvey) # self.recordButton.setEnabled(canSurvey) @pyqtSlot() def testButtonAction(self): print("Test") @pyqtSlot(bool) def surveyMode(self, modeOn): self.addStampButton.setEnabled(modeOn) self.hotkeyButton.setEnabled(modeOn) self.enableHotkeys(self.hotkeyButton.isChecked()) @pyqtSlot(QItemSelection) def onStampSelectionChange(self, selection): self.hasSelection = selection.count() > 0 self.deleteStampButton.setEnabled(self.hasSelection) @pyqtSlot(bool) def enableStamps(self, modeOn): self.addStampButton.setEnabled(modeOn) self.hotkeyButton.setEnabled(modeOn) if not modeOn and self.hotkeyButton.isChecked(): self.hotkeyButton.setChecked(False) self.enableHotkeys(False)
def init_price(self): currencies = {"EUR": u"€", "HRK": u"kn"} model = QSqlTableModel() model.setTable("shop") model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.select() self.shop_model = model price_model = QSqlRelationalTableModel() price_model.setTable("price") price_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) price_model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) price_model.setRelation(2, QSqlRelation('shop', 'id', 'name')) #Nutrition table needs to be populated since otherwise only 256 rows are read #And all inserts with ndbno > 100169 fail since they aren't found in nutrition #table nutri_model = price_model.relationModel(1) while nutri_model.canFetchMore(): nutri_model.fetchMore() self.price_model = price_model self.price_model.select() self.tv_price.setModel(price_model) self.tv_price.setSortingEnabled(True) self.tv_price.sortByColumn(1, Qt.AscendingOrder) self.tv_price.setItemDelegate(QSqlRelationalDelegate(self.tv_price)) price_sort_model = QSqlQueryModel() price_sort_model.setQuery( "SELECT nutrition.ndbno, nutrition.desc, shop.name, " + "last_updated, price, price/nutrition.package_weight*1000 as " + " price_kg, nutrition.kcal/price as kcal_price_100, " + "nutrition.protein/price as protein_price_100," + " (nutrition.kcal*nutrition.package_weight/100)/price as " + " kcal_price_package, " + " (nutrition.protein*nutrition.package_weight/100)/price " + " as protein_price_package, " + "lowered_price, lowered_untill, " + "currency,comment, temporary FROM price " + " JOIN shop ON shop.id == price.shop_id " + " JOIN nutrition ON price.ndbno = nutrition.ndbno " + " WHERE currency = 'EUR' " " ORDER BY nutrition.desc, price") proxy_model = QSortFilterProxyModel(self) proxy_model.setSourceModel(price_sort_model) proxy_model.setFilterKeyColumn(0) self.price_proxy = proxy_model #print (price_sort_model.lastError().text()) #print (price_sort_model.query().executedQuery()) self.tv_price_view.setModel(proxy_model) self.tv_price_view.setSortingEnabled(True) self.tv_price_view.horizontalHeader().setToolTip("protein/kcal_price " "higher is better") self.cb_shop.setModel(model) self.cb_shop.setModelColumn(1) #self.cb_shop.lineEdit().editingFinished.connect(self.add_shop) model_currency = QStringListModel() model_currency.setStringList(["EUR", "HRK"]) self.cb_currency.setModel(model_currency) self.de_last_updated.setMaximumDate(QDate.currentDate()) self.de_last_updated.setDateTime(QDateTime.currentDateTime()) def update_price(): print("Updating price info") if not price_model.submitAll(): QMessageBox.critical( None, "Error updating price:", "Couldn't update model: " + price_model.lastError().text()) def reset_price(): print("Resetting price info") price_model.revertAll() """Sets suffix symbols for prices based on currency""" def currency_changed(currency): symbol = currencies[currency] self.dsp_price.setSuffix(symbol) self.dsp_low_price.setSuffix(symbol) """Adds new Shop to DB""" def add_shop(): print(self.cb_shop.currentText()) row = self.shop_model.rowCount() self.shop_model.insertRow(row) self.shop_model.setData(self.shop_model.createIndex(row, 1), self.cb_shop.currentText(), Qt.EditRole) self.shop_model.submitAll() def add_price(): shop_id = self.shop_model.record(self.cb_shop.currentIndex()) \ .field("id").value() print("Shop ID:", shop_id, self.cb_shop.currentText()) record = self.cb_price_item.model() \ .record(self.cb_price_item.currentIndex()) ndbno = self._get_selected_ndbno(record) package_weight = record.field("package_weight").value() slices = record.field("num_of_slices").value() last_updated = self.de_last_updated.date() price = self.dsp_price.value() lowered_price = self.dsp_low_price.value() if \ self.dsp_low_price.value() > 0 else None lowered_untill = self.de_low_untill.date() if \ self.de_low_untill.date() > QDate.currentDate() else None currency = self.cb_currency.currentText() comment = self.le_comment.text() if \ len(self.le_comment.text()) > 3 else None temporary = self.cb_temporary.isChecked() print("ITEM:", self.cb_price_item.currentText(), ndbno) print("Weight:", package_weight, " Slices: ", slices) print("LU:", last_updated) print("PRICE:", price, " Low Price:", lowered_price) print("LOWU:", lowered_untill) print("CU:", currency) print("COMMENT:", comment) print("Temp:", temporary) print("NDBNO:", ndbno, type(ndbno)) print("SHOP ID:", shop_id, type(shop_id)) row = self.price_model.rowCount() #print ("ROW:", row, self.price_model.relationModel(1).rowCount()) self.price_model.insertRow(row) def add_data(idx, data): return self.price_model.setData( self.price_model.createIndex(row, idx), data, Qt.EditRole) #for i in range(100000,100194): #for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno #< 100000).order_by(LocalNutrition.ndbno): #out_ndbno = add_data(1, QVariant(i[0])) #print ("{}? = {}".format(out_ndbno, i[0])) #for i in range(100000,100194): ##for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno ##< 100000).order_by(LocalNutrition.ndbno): #out_ndbno = add_data(1, QVariant(i)) #print ("{}? = {}".format(out_ndbno, i)) #FIXME: Why is ndbno found in nutrtition only if it lower then 100169 #relation = self.price_model.relation(1) #print(relation, relation.displayColumn(), relation.indexColumn()) #print (relation.dictionary.contains(ndbno)) #return add_data(1, ndbno) add_data(2, shop_id) add_data(3, last_updated) add_data(4, price) if lowered_price is not None and lowered_untill is not None: add_data(5, lowered_price) add_data(6, lowered_untill) add_data(7, currency) if comment is not None: add_data(8, comment) add_data(9, temporary) self.price_model.submitAll() self.update_price = update_price self.reset_price = reset_price self.currency_changed = currency_changed self.buttonBox_4.button(QDialogButtonBox.SaveAll).clicked.connect( self.update_price) self.buttonBox_4.button(QDialogButtonBox.Reset).clicked.connect( self.reset_price) self.cb_currency.currentTextChanged.connect(self.currency_changed) self.pb_add_shop.pressed.connect(add_shop) self.pb_add_price.pressed.connect(add_price)