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)
