class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setTable()
self.setLayout()
self.setCentralWidget(self.widget)
self.resize(600,400)
def setTable(self):
self.search = QLineEdit()
self.search.textChanged.connect(self.setFilter)
self.table = QTableView()
self.model = QSqlRelationalTableModel(db=db)
self.model.setTable('album')
self.table.setModel(self.model)
index = self.model.fieldIndex("Title")
self.model.setSort(index, Qt.DescendingOrder)
self.model.setRelation(2, QSqlRelation("artist", "ArtistId","Name"))
self.model.select()
def setLayout(self):
self.layout = QVBoxLayout()
self.layout.addWidget(self.search)
self.layout.addWidget(self.table)
self.widget = QWidget()
self.widget.setLayout(self.layout)
def setFilter(self, word):
filterLastName = f'Name LIKE "%{word}%"'
self.model.setFilter(filterLastName)
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 DBViewWindow(QWidget, Ui_DB_View_Form):
def __init__(self, database_name):
super().__init__()
# uic.loadUi('db_view.ui', self)
self.setupUi(self)
# self.db_name = database_name
# # Подключение БД к таблице отображения
# # Подключение через QSqlRelationalTableModel
# self.db = QSqlDatabase.addDatabase('QSQLITE')
# self.db.setDatabaseName(self.db_name)
# self.db.open()
self.model = QSqlRelationalTableModel(self)
self.model.setTable('goods')
self.model.setRelation(
5, QSqlRelation('statuses', 'id_status', 'status_name'))
self.model.setRelation(
6, QSqlRelation('goods_types', 'id_goods_type', 'goods_type_name'))
self.model.setRelation(
7,
QSqlRelation('goods_subtypes', 'id_goods_subtype',
'goods_subtype_name'))
self.model.setRelation(
8, QSqlRelation('location', 'id_location', 'location_name'))
self.model.setRelation(
9, QSqlRelation('responsibles', 'id_responsible', 'FIO'))
self.refresh()
self.tableView.doubleClicked.connect(self.table_clicked)
# self.tableView.clicked.connect()
self.refreshBtn.clicked.connect(self.refresh)
self.save_all_btn.clicked.connect(self.submitall)
def table_clicked(self):
row = self.tableView.currentIndex().row()
if row != -1:
self.tableView.selectRow(row)
index = self.tableView.currentIndex()
self.edit_form = EditForm(index, self.model)
self.edit_form.show()
# Предположительно на выходе будет закрываться БД
def __exit__(self, exc_type, exc_val, exc_tb):
self.db.close()
super().__exit__()
def refresh(self):
self.model.select()
self.tableView.setModel(self.model)
self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView))
def submitall(self):
if not self.model.submitAll():
print(self.model.lastError())
else:
self.refresh()
def createTable(self):
"""
Create the table using model/view architecture.
"""
# Create the model
model = QSqlRelationalTableModel()
model.setTable('accounts')
# Set up relationship for foreign keys
model.setRelation(model.fieldIndex('country_id'),
QSqlRelation('countries', 'id', 'country'))
table_view = QTableView()
table_view.setModel(model)
table_view.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# Populate the model with data
model.select()
# Main layout
main_v_box = QVBoxLayout()
main_v_box.addWidget(table_view)
self.setLayout(main_v_box)
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 MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout = QVBoxLayout()
self.search = QLineEdit()
self.search.textChanged.connect(self.update_filter)
self.table = QTableView()
layout.addWidget(self.search)
layout.addWidget(self.table)
container.setLayout(layout)
self.model = QSqlRelationalTableModel(db=db)
self.table.setModel(self.model)
self.model.setTable("Track")
self.model.setRelation(2, QSqlRelation("Album", "AlbumId", "Title"))
self.model.setRelation(3, QSqlRelation("MediaType", "MediaTypeId", "Name"))
self.model.setRelation(4, QSqlRelation("Genre", "GenreId", "Name"))
delegate = QSqlRelationalDelegate(self.table)
self.table.setItemDelegate(delegate)
self.model.select()
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(container)
def update_filter(self, s):
s = re.sub("[\W]+", "", s)
filter_str = 'Name LIKE "%{}%"'.format(s)
self.model.setFilter(filter_str)
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 PhoneLogDlg(QDialog):
FIRST, PREV, NEXT, LAST = range(4)
def __init__(self, parent=None):
super(PhoneLogDlg, self).__init__(parent)
callerLabel = QLabel("&Caller:")
self.callerEdit = QLineEdit()
callerLabel.setBuddy(self.callerEdit)
today = QDate.currentDate()
startLabel = QLabel("&Start:")
self.startDateTime = QDateTimeEdit()
startLabel.setBuddy(self.startDateTime)
self.startDateTime.setDateRange(today, today)
self.startDateTime.setDisplayFormat(DATETIME_FORMAT)
endLabel = QLabel("&End:")
self.endDateTime = QDateTimeEdit()
endLabel.setBuddy(self.endDateTime)
self.endDateTime.setDateRange(today, today)
self.endDateTime.setDisplayFormat(DATETIME_FORMAT)
topicLabel = QLabel("&Topic:")
topicEdit = QLineEdit()
topicLabel.setBuddy(topicEdit)
outcomeLabel = QLabel("&Outcome:")
self.outcomeComboBox = QComboBox()
outcomeLabel.setBuddy(self.outcomeComboBox)
firstButton = QPushButton()
firstButton.setIcon(QIcon(":/first.png"))
prevButton = QPushButton()
prevButton.setIcon(QIcon(":/prev.png"))
nextButton = QPushButton()
nextButton.setIcon(QIcon(":/next.png"))
lastButton = QPushButton()
lastButton.setIcon(QIcon(":/last.png"))
addButton = QPushButton("&Add")
addButton.setIcon(QIcon(":/add.png"))
deleteButton = QPushButton("&Delete")
deleteButton.setIcon(QIcon(":/delete.png"))
quitButton = QPushButton("&Quit")
quitButton.setIcon(QIcon(":/quit.png"))
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
fieldLayout = QGridLayout()
fieldLayout.addWidget(callerLabel, 0, 0)
fieldLayout.addWidget(self.callerEdit, 0, 1, 1, 3)
fieldLayout.addWidget(startLabel, 1, 0)
fieldLayout.addWidget(self.startDateTime, 1, 1)
fieldLayout.addWidget(endLabel, 1, 2)
fieldLayout.addWidget(self.endDateTime, 1, 3)
fieldLayout.addWidget(topicLabel, 2, 0)
fieldLayout.addWidget(topicEdit, 2, 1, 1, 3)
fieldLayout.addWidget(outcomeLabel, 3, 0)
fieldLayout.addWidget(self.outcomeComboBox, 3, 1, 1, 3)
navigationLayout = QHBoxLayout()
navigationLayout.addWidget(firstButton)
navigationLayout.addWidget(prevButton)
navigationLayout.addWidget(nextButton)
navigationLayout.addWidget(lastButton)
fieldLayout.addLayout(navigationLayout, 4, 0, 1, 2)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(fieldLayout)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.model = QSqlRelationalTableModel(self)
self.model.setTable("calls")
self.model.setRelation(OUTCOMEID, QSqlRelation("outcomes", "id",
"name"))
self.model.setSort(STARTTIME, Qt.AscendingOrder)
self.model.select()
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setModel(self.model)
self.mapper.setItemDelegate(QSqlRelationalDelegate(self))
self.mapper.addMapping(self.callerEdit, CALLER)
self.mapper.addMapping(self.startDateTime, STARTTIME)
self.mapper.addMapping(self.endDateTime, ENDTIME)
self.mapper.addMapping(topicEdit, TOPIC)
relationModel = self.model.relationModel(OUTCOMEID)
self.outcomeComboBox.setModel(relationModel)
self.outcomeComboBox.setModelColumn(relationModel.fieldIndex("name"))
self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID)
self.mapper.toFirst()
firstButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.FIRST))
prevButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.PREV))
nextButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.NEXT))
lastButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.LAST))
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
quitButton.clicked.connect(self.done)
self.setWindowTitle("Phone Log")
def done(self, result=None):
self.mapper.submit()
QDialog.done(self, True)
def addRecord(self):
row = self.model.rowCount()
self.mapper.submit()
self.model.insertRow(row)
self.mapper.setCurrentIndex(row)
now = QDateTime.currentDateTime()
self.startDateTime.setDateTime(now)
self.endDateTime.setDateTime(now)
self.outcomeComboBox.setCurrentIndex(
self.outcomeComboBox.findText("Unresolved"))
self.callerEdit.setFocus()
def deleteRecord(self):
caller = self.callerEdit.text()
starttime = self.startDateTime.dateTime().toString(DATETIME_FORMAT)
if (QMessageBox.question(
self, "Delete",
"Delete call made by<br>{0} on {1}?".format(caller, starttime),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
self.model.select()
if row + 1 >= self.model.rowCount():
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def saveRecord(self, where):
row = self.mapper.currentIndex()
self.mapper.submit()
if where == PhoneLogDlg.FIRST:
row = 0
elif where == PhoneLogDlg.PREV:
row = 0 if row <= 1 else row - 1
elif where == PhoneLogDlg.NEXT:
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
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 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 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 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)
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)
class MainForm(QDialog):
def __init__(self):
super(MainForm, self).__init__()
self.assetModel = QSqlRelationalTableModel(self)
self.assetModel.setTable("assets")
self.assetModel.setRelation(CATEGORYID,
QSqlRelation("categories", "id", "name"))
self.assetModel.setSort(ROOM, Qt.AscendingOrder)
self.assetModel.setHeaderData(ID, Qt.Horizontal, "ID")
self.assetModel.setHeaderData(NAME, Qt.Horizontal, "Name")
self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal, "Category")
self.assetModel.setHeaderData(ROOM, Qt.Horizontal, "Room")
self.assetModel.select()
self.assetView = QTableView()
self.assetView.setModel(self.assetModel)
self.assetView.setItemDelegate(AssetDelegate(self))
self.assetView.setSelectionMode(QTableView.SingleSelection)
self.assetView.setSelectionBehavior(QTableView.SelectRows)
self.assetView.setColumnHidden(ID, True)
self.assetView.resizeColumnsToContents()
assetLabel = QLabel("A&ssets")
assetLabel.setBuddy(self.assetView)
self.logModel = QSqlRelationalTableModel(self)
self.logModel.setTable("logs")
self.logModel.setRelation(ACTIONID,
QSqlRelation("actions", "id", "name"))
self.logModel.setSort(DATE, Qt.AscendingOrder)
self.logModel.setHeaderData(DATE, Qt.Horizontal, "Date")
self.logModel.setHeaderData(ACTIONID, Qt.Horizontal, "Action")
self.logModel.select()
self.logView = QTableView()
self.logView.setModel(self.logModel)
self.logView.setItemDelegate(LogDelegate(self))
self.logView.setSelectionMode(QTableView.SingleSelection)
self.logView.setSelectionBehavior(QTableView.SelectRows)
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
self.logView.resizeColumnsToContents()
self.logView.horizontalHeader().setStretchLastSection(True)
logLabel = QLabel("&Logs")
logLabel.setBuddy(self.logView)
addAssetButton = QPushButton("&Add Asset")
deleteAssetButton = QPushButton("&Delete Asset")
addActionButton = QPushButton("Add A&ction")
deleteActionButton = QPushButton("Delete Ac&tion")
editActionsButton = QPushButton("&Edit Actions...")
editCategoriesButton = QPushButton("Ed&it Categories...")
quitButton = QPushButton("&Quit")
for button in (addAssetButton, deleteAssetButton, addActionButton,
deleteActionButton, editActionsButton,
editCategoriesButton, quitButton):
if MAC:
button.setDefault(False)
button.setAutoDefault(False)
else:
button.setFocusPolicy(Qt.NoFocus)
dataLayout = QVBoxLayout()
dataLayout.addWidget(assetLabel)
dataLayout.addWidget(self.assetView, 1)
dataLayout.addWidget(logLabel)
dataLayout.addWidget(self.logView)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addAssetButton)
buttonLayout.addWidget(deleteAssetButton)
buttonLayout.addWidget(addActionButton)
buttonLayout.addWidget(deleteActionButton)
buttonLayout.addWidget(editActionsButton)
buttonLayout.addWidget(editCategoriesButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(dataLayout, 1)
layout.addLayout(buttonLayout)
self.setLayout(layout)
#self.connect(self.assetView.selectionModel(),
#SIGNAL(("currentRowChanged(QModelIndex,QModelIndex)")),
#self.assetChanged)
self.assetView.selectionModel().currentRowChanged.connect(
self.assetChanged)
addAssetButton.clicked.connect(self.addAsset)
deleteAssetButton.clicked.connect(self.deleteAsset)
addActionButton.clicked.connect(self.addAction)
deleteActionButton.clicked.connect(self.deleteAction)
editActionsButton.clicked.connect(self.editActions)
editCategoriesButton.clicked.connect(self.editCategories)
quitButton.clicked.connect(self.done)
self.assetChanged(self.assetView.currentIndex())
self.setMinimumWidth(650)
self.setWindowTitle("Asset Manager")
def done(self, result=1):
query = QSqlQuery()
query.exec_("DELETE FROM logs WHERE logs.assetid NOT IN"
"(SELECT id FROM assets)")
QDialog.done(self, 1)
def assetChanged(self, index):
if index.isValid():
record = self.assetModel.record(index.row())
#print(index.row())
id = record.value("id")
self.logModel.setFilter("assetid = {0}".format(id))
else:
self.logModel.setFilter("assetid = -1")
#self.logModel.reset() # workaround for Qt <= 4.3.3/SQLite bug
#self.logModel.beginResetModel()
self.logModel.select()
self.logView.horizontalHeader().setVisible(
self.logModel.rowCount() > 0)
if PYQT_VERSION_STR < "4.1.0":
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
#self.logModel.endResetModel()
def addAsset(self):
row = (self.assetView.currentIndex().row()
if self.assetView.currentIndex().isValid() else 0)
QSqlDatabase.database().transaction()
self.assetModel.insertRow(row)
index = self.assetModel.index(row, NAME)
self.assetView.setCurrentIndex(index)
assetid = 1
query = QSqlQuery()
query.exec_("SELECT MAX(id) FROM assets")
if query.next():
assetid = query.value(0)
query.prepare("INSERT INTO logs (assetid, date, actionid) "
"VALUES (:assetid, :date, :actionid)")
query.bindValue(":assetid", assetid + 1)
query.bindValue(":date", QDate.currentDate())
query.bindValue(":actionid", ACQUIRED)
query.exec_()
QSqlDatabase.database().commit()
#self.logModel.select()
self.assetView.edit(index)
def deleteAsset(self):
index = self.assetView.currentIndex()
if not index.isValid():
return
QSqlDatabase.database().transaction()
record = self.assetModel.record(index.row())
assetid = record.value(ID)
logrecords = 1
query = QSqlQuery(
"SELECT COUNT(*) FROM logs WHERE assetid = {0}".format(assetid))
if query.next():
logrecords = query.value(0)
msg = ("<font color=red>Delete</font><br><b>{0}</b>"
"<br>from room {1}").format(record.value(NAME),
record.value(ROOM))
if logrecords > 1:
msg += (", along with {0} log records".format(logrecords))
msg += "?"
if (QMessageBox.question(self, "Delete Asset", msg, QMessageBox.Yes
| QMessageBox.No) == QMessageBox.No):
QSqlDatabase.database().rollback()
return
#query.exec_("DELETE FROM logs WHERE assetid = {0}"
# .format(assetid))
#use model API
self.logModel.setFilter("assetid={0}".format(assetid))
self.logModel.select()
if self.logModel.rowCount() > 0:
self.logModel.removeRows(0, self.logModel.rowCount())
self.logModel.submitAll()
self.assetModel.removeRow(index.row())
self.assetModel.submitAll()
QSqlDatabase.database().commit()
self.assetModel.select()
self.assetChanged(self.assetView.currentIndex())
def addAction(self):
index = self.assetView.currentIndex()
if not index.isValid():
return
QSqlDatabase.database().transaction()
record = self.assetModel.record(index.row())
assetid = record.value(ID)
row = self.logModel.rowCount()
self.logModel.insertRow(row)
self.logModel.setData(self.logModel.index(row, ASSETID), assetid)
self.logModel.setData(self.logModel.index(row, DATE),
QDate.currentDate())
QSqlDatabase.database().commit()
index = self.logModel.index(row, ACTIONID)
self.logView.setCurrentIndex(index)
self.logView.edit(index)
def deleteAction(self):
index = self.logView.currentIndex()
if not index.isValid():
return
record = self.logModel.record(index.row())
action = record.value(ACTIONID)
if action == "Acquired":
QMessageBox.information(
self, "Delete Log",
"The 'Acquired' log record cannot be deleted.<br>"
"You could delete the entire asset instead.")
return
when = str(record.value(DATE))
if (QMessageBox.question(self, "Delete Log",
"Delete log<br>{0} {1}?".format(when, action),
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.No):
return
self.logModel.removeRow(index.row())
self.logModel.submitAll()
self.logModel.select()
def editActions(self):
form = ReferenceDataDlg("actions", "Action", self)
form.exec_()
def editCategories(self):
form = ReferenceDataDlg("categories", "Category", self)
form.exec_()
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)
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()
TV_OBJ.setModel(R_T_MODEL)
TV_OBJ.setItemDelegateForColumn(1, QSqlRelationalDelegate(TV_OBJ))
TV_OBJ.hideColumn(0)
TV_OBJ.setColumnWidth(1, 150)
TV_OBJ.setColumnWidth(2, 150)
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 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 AccountManager(QWidget):
def __init__(self):
super().__init__()
self.initialize_ui()
def initialize_ui(self):
self.setMinimumSize(1000, 600)
self.setWindowTitle('Account Manager GUI')
self.createConnection()
self.createTable()
self.setupWidgets()
self.show()
def createConnection(self):
database = QSqlDatabase.addDatabase("QSQLITE")
database.setDatabaseName("files/accounts.db")
if not database.open():
print("Unable to open data source file.")
sys.exit(1) # Error code 1 - signifies error
# Check if tables we need exist in the database
tables_needed = {'accounts', 'countries'}
tables_not_found = tables_needed - set(database.tables())
if tables_not_found:
QMessageBox.critical(
None, "Error",
f"The following tables are missing from the database: {tables_not_found}"
)
sys.exit(1)
def createTable(self):
"""
Set up the model, headers and populate the model.
"""
self.model = QSqlRelationalTableModel()
self.model.setTable('accounts')
self.model.setRelation(self.model.fieldIndex('country_id'),
QSqlRelation('countries', 'id', 'country'))
self.model.setHeaderData(self.model.fieldIndex('id'), Qt.Horizontal,
"ID")
self.model.setHeaderData(self.model.fieldIndex('employee_id'),
Qt.Horizontal, "Employee ID")
self.model.setHeaderData(self.model.fieldIndex('first_name'),
Qt.Horizontal, "First")
self.model.setHeaderData(self.model.fieldIndex('last_name'),
Qt.Horizontal, "Last")
self.model.setHeaderData(self.model.fieldIndex('email'), Qt.Horizontal,
"E-mail")
self.model.setHeaderData(self.model.fieldIndex('department'),
Qt.Horizontal, "Dept.")
self.model.setHeaderData(self.model.fieldIndex('country_id'),
Qt.Horizontal, "Country")
# populate the model with data
self.model.select()
def setupWidgets(self):
"""
Create instances of widgets, the table view and set layouts.
"""
icons_path = "icons"
title = QLabel("Account Management System")
title.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed)
title.setStyleSheet("font: bold 24px")
add_product_button = QPushButton("Add Employee")
add_product_button.setIcon(
QIcon(os.path.join(icons_path, "add_user.png")))
add_product_button.setStyleSheet("padding: 10px")
add_product_button.clicked.connect(self.addItem)
del_product_button = QPushButton("Delete")
del_product_button.setIcon(
QIcon(os.path.join(icons_path, "trash_can.png")))
del_product_button.setStyleSheet("padding: 10px")
del_product_button.clicked.connect(self.deleteItem)
# set up sorting combobox
sorting_options = [
"Sort by ID", "Sort by Employee ID", "Sort by First Name",
"Sort by Last Name", "Sort by Department", "Sort by Country"
]
sort_name_cb = QComboBox()
sort_name_cb.addItems(sorting_options)
sort_name_cb.currentTextChanged.connect(self.setSortingOrder)
buttons_h_box = QHBoxLayout()
buttons_h_box.addWidget(add_product_button)
buttons_h_box.addWidget(del_product_button)
buttons_h_box.addStretch()
buttons_h_box.addWidget(sort_name_cb)
# Widget to contain editing buttons
edit_buttons = QWidget()
edit_buttons.setLayout(buttons_h_box)
# Create table view and set model
self.table_view = QTableView()
self.table_view.setModel(self.model)
self.table_view.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
self.table_view.verticalHeader().setSectionResizeMode(
QHeaderView.Stretch)
self.table_view.setSelectionMode(QTableView.SingleSelection)
self.table_view.setSelectionBehavior(QTableView.SelectRows)
# Instantiate the delegate
delegate = QSqlRelationalDelegate(self.table_view)
self.table_view.setItemDelegate(delegate)
# Main layout
main_v_box = QVBoxLayout()
main_v_box.addWidget(title, Qt.AlignLeft)
main_v_box.addWidget(edit_buttons)
main_v_box.addWidget(self.table_view)
self.setLayout(main_v_box)
def addItem(self):
"""
Add a new record to the last row of the table.
"""
last_row = self.model.rowCount()
self.model.insertRow(last_row)
id = 0
query = QSqlQuery()
query.exec_("SELECT MAX(id) FROM ACCOUNTS")
if query.next():
print(query.value(0))
id = int(query.value(0))
def deleteItem(self):
"""
Delete an entire row from the table.
"""
current_item = self.table_view.selectedIndexes()
for index in current_item:
self.model.removeRow(index.row())
self.model.select()
def setSortingOrder(self, text):
"""
Sort the rows in table.
"""
# mode = 0
if text == "Sort by ID":
self.model.setSort(self.model.fieldIndex('id'), Qt.AscendingOrder)
# self.model.setSort(self.model.fieldIndex('id'), mode if Qt.DescendingOrder else Qt.AscendingOrder)
elif text == "Sort by Employee ID":
self.model.setSort(self.model.fieldIndex('employee_id'),
Qt.AscendingOrder)
elif text == "Sort by First Name":
self.model.setSort(self.model.fieldIndex('first_name'),
Qt.AscendingOrder)
elif text == "Sort by Last Name":
self.model.setSort(self.model.fieldIndex('last_name'),
Qt.AscendingOrder)
elif text == "Sort by Department":
self.model.setSort(self.model.fieldIndex('department'),
Qt.AscendingOrder)
elif text == "Sort by Country":
self.model.setSort(self.model.fieldIndex('country'),
Qt.AscendingOrder)
self.model.select()
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()}')
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 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()
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()}')