class MainWindow(QMainWindow, Ui_MainWindow):
def __init__(self, *args, **kwargs):
super(MainWindow, self).__init__(*args, **kwargs)
self.setupUi(self)
# toolbar actions
self.actionInitialize_Reset.triggered.connect(self.open_init_dialog)
# table view
self.model = QSqlRelationalTableModel(self, db)
self.model.setTable(Server.__tablename__)
self.model.select()
self.tableView.setModel(self.model)
logical_headers = ['ID', 'Server', 'Port', 'Password', 'Encryption', 'Protocol', 'Protocol Params',
'Obfuscation', 'Obfuscation Params', 'Remark', 'Group']
visual_headers = logical_headers.copy()
displayed_columns = ['Remark', 'Server', 'Port', 'Group']
for idx, header in enumerate(logical_headers):
self.model.setHeaderData(idx, QtCore.Qt.Horizontal, header)
for displayed_header in reversed(displayed_columns):
idx = visual_headers.index(displayed_header)
self.tableView.horizontalHeader().moveSection(idx, 0)
visual_headers.pop(idx)
visual_headers.insert(0, displayed_header)
for idx, header in enumerate(logical_headers):
if header not in displayed_columns:
self.tableView.horizontalHeader().setSectionHidden(idx, True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
def updateTableView(self):
self.model.select()
@staticmethod
def open_init_dialog():
qApp.exit(EXIT_CODE_RESET)
def open_debug_terminal(self):
pass
def add_server(self, server: dict):
pass
def bulk_add_server(self, servers: [dict]):
pass
def update_server(self, server: Server):
pass
def remove_server(self, server: Server):
pass
class Model(object):
def __init__(self,
databasetype="QSQLITE",
databasename="Sqlite_Sql/testcase.db",
sqltablename="result"):
db = QSqlDatabase.addDatabase(databasetype)
db.setDatabaseName(databasename)
self.sqltablename = sqltablename
self.tablemodel()
def tablemodel(self):
# self.model=QSqlTableModel()
# self.model.setRelation(4,QSqlRelation("TEST","id_test","method_name"))
self.model = QSqlRelationalTableModel()
self.model.setTable(self.sqltablename)
self.model.setHeaderData(0, Qt.Horizontal, '用例ID')
self.model.setHeaderData(1, Qt.Horizontal, '用例名称')
self.model.setHeaderData(2, Qt.Horizontal, '脚本名称')
self.model.setHeaderData(3, Qt.Horizontal, '执行状态')
self.model.setHeaderData(4, Qt.Horizontal, '结论')
self.model.setHeaderData(5, Qt.Horizontal, '时间')
self.model.setHeaderData(6, Qt.Horizontal, '操作')
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
class 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 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 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)
class MyWidget(QWidget):
def __init__(self, parent=None):
super().__init__(parent)
horizontalLayout = QHBoxLayout()
self.dayView = QTableView()
self.dayView.setFrameShape(QFrame.Box)
self.dayView.horizontalHeader().setStretchLastSection(True)
self.dayView.verticalHeader().setVisible(False)
horizontalLayout.addWidget(self.dayView)
verticalLayout = QVBoxLayout()
self.calendarWidget = QCalendarWidget()
self.calendarWidget.setMinimumSize(QSize(250, 200))
self.calendarWidget.setMaximumSize(QSize(250, 200))
self.calendarWidget.setMinimumDate(QDate(2017, 1, 1))
self.calendarWidget.setMaximumDate(QDate(2030, 1, 1))
self.calendarWidget.selectionChanged.connect(self.dataChange)
self.calendarWidget.setSelectedDate(QDate.currentDate())
verticalLayout.addWidget(self.calendarWidget)
titleFV = QLabel('Food View')
verticalLayout.addWidget(titleFV)
self.filterLine = QLineEdit()
self.filterLine.setMaximumSize(QSize(200, 25))
self.filterLine.textChanged.connect(self.filterChange)
buttonAdd = QPushButton(QIcon("images/add.png"), '', None)
buttonAdd.setMaximumSize(QSize(20, 30))
buttonAdd.clicked.connect(self.addFood)
buttonDell = QPushButton(QIcon("images/del.png"), '', None)
buttonDell.setMaximumSize(QSize(20, 30))
buttonDell.clicked.connect(self.delFood)
lineEditLayout = QHBoxLayout()
lineEditLayout.addWidget(self.filterLine)
lineEditLayout.addWidget(buttonAdd)
lineEditLayout.addWidget(buttonDell)
verticalLayout.addLayout(lineEditLayout)
self.foodView = QTableView()
self.foodView.setMinimumSize(QSize(0, 0))
self.foodView.setMaximumSize(QSize(250, 1000))
self.foodView.verticalHeader().setVisible(False)
self.foodView.horizontalHeader().setStretchLastSection(True)
verticalLayout.addWidget(self.foodView)
horizontalLayout.addLayout(verticalLayout)
self.setLayout(horizontalLayout)
model_in = QSqlRelationalTableModel()
model_in.setEditStrategy(QSqlTableModel.OnFieldChange)
model_in.setTable("intake_food")
id_food = model_in.fieldIndex("id_food")
date = model_in.fieldIndex("food_date")
mass = model_in.fieldIndex("mass")
# Set model, hide ID column
model_in.setRelation(id_food, QSqlRelation("food", "id", "name"))
model_in.setHeaderData(id_food, Qt.Horizontal, "Food")
model_in.setHeaderData(date, Qt.Horizontal, "Date")
model_in.setHeaderData(mass, Qt.Horizontal, "Mass")
if not model_in.select():
self.showError(model_in.lastError())
return
self.proxyModel_in = QSortFilterProxyModel()
self.proxyModel_in.setSourceModel(model_in)
self.proxyModel_in.setFilterKeyColumn(2)
self.dayView.setItemDelegate(FlipProxyDelegate())
self.dayView.setModel(self.proxyModel_in)
self.dayView.setColumnHidden(0, True)
self.dayView.setColumnHidden(2, True)
self.dayView.setSelectionMode(QAbstractItemView.SingleSelection)
self.dayView.setContextMenuPolicy(Qt.CustomContextMenu)
self.dayView.customContextMenuRequested.connect(self.ShowContextMenu)
# filter day food by calendar widget
self.dataChange()
self.model_f = QSqlRelationalTableModel()
self.model_f.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model_f.setTable("food")
self.model_f.setHeaderData(1, Qt.Horizontal, "Food")
self.model_f.setHeaderData(2, Qt.Horizontal, "Rate")
if not self.model_f.select():
self.showError(self.model_f.lastError())
return
self.proxyModel_f = QSortFilterProxyModel()
self.proxyModel_f.setSourceModel(self.model_f)
self.proxyModel_f.setFilterKeyColumn(1)
self.foodView.setModel(self.proxyModel_f)
self.foodView.setColumnHidden(0, True)
self.foodView.setSelectionMode(QAbstractItemView.SingleSelection)
self.foodView.setColumnWidth(1, 150)
self.foodView.setColumnWidth(2, 90)
def showError(self, err):
QMessageBox.critical(self, "Unable to initialize Database",
"Error initializing database: " + err.text())
def filterChange(self):
regExp = QRegExp(self.filterLine.text(), Qt.CaseInsensitive,
QRegExp.FixedString)
self.proxyModel_f.setFilterRegExp(regExp)
def dataChange(self):
date = self.calendarWidget.selectedDate().toString('dd.MM.yyyy')
regExp = QRegExp(date, Qt.CaseInsensitive, QRegExp.FixedString)
self.proxyModel_in.setFilterRegExp(regExp)
def addFood(self):
self.model_f.insertRow(self.model_f.rowCount())
def delFood(self):
self.model_f.removeRow(self.foodView.currentIndex().row())
self.model_f.select()
def resizeEvent(self, event):
self.dayView.setColumnWidth(1, self.dayView.width() * 0.7)
self.dayView.setColumnWidth(3, self.dayView.width() * 0.2)
QWidget.resizeEvent(self, event)
def ShowContextMenu(self, pos):
contextMenu = QMenu("Context menu", self)
action1 = QAction("Add food eaten", self)
contextMenu.addAction(action1)
contextMenu.exec(self.mapToGlobal(pos))
class 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 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 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 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 Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(700, 600)
MainWindow.setMinimumSize(QtCore.QSize(700, 600))
MainWindow.setMaximumSize(QtCore.QSize(700, 600))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.btn_call = QtWidgets.QPushButton(self.centralwidget)
self.btn_call.setGeometry(QtCore.QRect(558, 480, 131, 71))
self.btn_call.setObjectName("btn_call")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_call.setFont(font)
self.btn_add = QtWidgets.QPushButton(self.centralwidget)
self.btn_add.setGeometry(QtCore.QRect(10, 480, 89, 71))
self.btn_add.setStyleSheet("")
self.btn_add.setObjectName("btn_add")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_add.setFont(font)
self.btn_delete = QtWidgets.QPushButton(self.centralwidget)
self.btn_delete.setGeometry(QtCore.QRect(120, 480, 89, 71))
self.btn_delete.setObjectName("btn_delete")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_delete.setFont(font)
self.btn_search = QtWidgets.QPushButton(self.centralwidget)
self.btn_search.setGeometry(QtCore.QRect(230, 480, 89, 71))
self.btn_search.setObjectName("btn_search")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_search.setFont(font)
self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
self.lineEdit.setGeometry(QtCore.QRect(10, 434, 311, 31))
font = QtGui.QFont()
font.setPointSize(14)
self.lineEdit.setFont(font)
self.lineEdit.setObjectName("lineEdit")
self.comboBox = QtWidgets.QComboBox(self.centralwidget)
self.comboBox.setGeometry(QtCore.QRect(335, 434, 121, 31))
self.comboBox.setObjectName("comboBox")
font = QtGui.QFont()
font.setPointSize(11)
self.comboBox.setFont(font)
self.tableView = QtWidgets.QTableView(self.centralwidget)
self.tableView.setGeometry(QtCore.QRect(10, 10, 681, 411))
self.tableView.setObjectName("tableView")
font = QtGui.QFont()
font.setPointSize(11)
self.tableView.setFont(font)
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 700, 22))
self.menubar.setObjectName("menubar")
self.menuSetting = QtWidgets.QMenu(self.menubar)
self.menuSetting.setObjectName("menuSetting")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.action_Invite_window = QtWidgets.QAction(MainWindow)
self.action_Invite_window.setObjectName("action_Invite_window")
# self.actionPlace_windows = QtWidgets.QAction(MainWindow)
# self.actionPlace_windows.setObjectName("actionPlace_windows")
self.actionini_py = QtWidgets.QAction(MainWindow)
self.actionini_py.setObjectName("actionini_py")
self.actionDestination = QtWidgets.QAction(MainWindow)
self.actionDestination.setObjectName("actionDestination")
self.actionDrop_tablel = QtWidgets.QAction(MainWindow)
self.actionDrop_tablel.setObjectName("actionDrop_tablel")
self.menuSetting.addAction(self.action_Invite_window)
# self.menuSetting.addAction(self.actionPlace_windows)
self.menuSetting.addAction(self.actionDestination)
self.menuSetting.addAction(self.actionDrop_tablel)
self.menuSetting.addSeparator()
self.menuSetting.addAction(self.actionini_py)
self.menubar.addAction(self.menuSetting.menuAction())
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
# เรียกฟังชั่นเพื่อโหลด model ลง table
self.loadData()
self.btn_add.clicked.connect(self.insertData)
self.btn_delete.clicked.connect(self.deleteData)
self.btn_call.clicked.connect(self.speak)
self.btn_search.clicked.connect(self.searchData)
self.actionDestination.triggered.connect(self.show_desDialog)
def show_desDialog(self):
self.desDialog.show()
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "Simple Queue"))
self.btn_call.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อเรียกคิวที่เลือกจากตารางคิว</p></body></html>"
))
self.btn_call.setText(_translate("MainWindow", "เรียกคิว"))
self.btn_add.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อเปิดเมนูการเพิ่มคิวด้วยตัวเอง</p></body></html>"
))
self.btn_add.setText(_translate("MainWindow", "เพิ่มคิว"))
self.btn_delete.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อลบคิวที่ป้อนค่าผิดออกจากตารางคิว</p></body></html>"
))
self.btn_delete.setText(_translate("MainWindow", "ลบคิว"))
self.btn_search.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อโหลดตารางคิวใหม่</p></body></html>"
))
self.btn_search.setText(_translate("MainWindow", "ค้นหาคิว"))
self.menuSetting.setTitle(_translate("MainWindow", "ตั้งค่า"))
self.action_Invite_window.setText(
_translate("MainWindow", "Call sentence setting"))
#self.actionPlace_windows.setText(_translate("MainWindow", "Place sentence"))
self.actionini_py.setText(_translate("MainWindow", "ini.py"))
self.actionDestination.setText(
_translate("MainWindow", "Destination setting"))
self.actionDrop_tablel.setText(_translate("MainWindow", "Drop tablel"))
def loadData(self):
self.lineEdit.clear()
self.lineEdit.setFocus()
# สร้าง model เรียก data จาก database
# ใช้ QSqlRelationalTableModel สำหรับตารางที่มีคีย์นอก
self.model = QSqlRelationalTableModel()
self.model.setTable('queue')
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.setHeaderData(0, Qt.Horizontal, 'ID')
self.model.setHeaderData(1, Qt.Horizontal, "รายชื่อ")
self.model.setHeaderData(2, Qt.Horizontal, "เวลาเพิ่มคิว")
self.model.setHeaderData(3, Qt.Horizontal, "เวลาเรียกคิว")
self.model.setHeaderData(4, Qt.Horizontal, "สถาณะคิว")
self.model.setHeaderData(5, Qt.Horizontal, "ปลายทาง")
self.model.setHeaderData(6, Qt.Horizontal, "Option")
# ให้ column#5 เป็นคีย์นอกดึงตารางนอกมาแสดง
self.model.setRelation(
5, QSqlRelation("destination", "des_id", "des_name"))
# เรียกใช้ model เรียกใช้ได้จากทุกที่ใน class ไม่ต้องเรียก loadData()
self.model.select()
# ให้ tableView เลือก data จาก model ไปแสดง
self.tableView.setModel(self.model)
self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView))
self.tableView.setColumnHidden(0, True)
self.tableView.setCornerButtonEnabled(False)
self.tableView.setSortingEnabled(True)
self.tableView.setColumnWidth(1, 210)
# เรียก fetchData เพื่อ fetch data เข้ามาใน model ให้หมด
self.fetchData()
# เมื่อ click ให้เลือกทั้งแถว
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
# สร้าง model เรียกตาราง destination แล้วยัดเข้า combobox
self.desModel = QSqlQueryModel()
selectQuery = QSqlQuery()
selectQuery.prepare('SELECT des_name FROM destination')
# print('QUERY = ' + str(selectQuery.lastQuery()))
if selectQuery.exec():
self.desModel.setQuery(selectQuery)
# print('SELECT des_name COMPLETE')
self.comboBox.setModel(self.desModel)
else:
print('SELECT FALSE = ' + selectQuery.lastError().text())
# สร้าง obj ของ destination setting ไว้รอเรียก
self.desDialog = QtWidgets.QDialog()
self.ui = destination2.Ui_Dialog()
self.ui.setupUi(self.desDialog)
self.tableView.selectRow(0)
def insertData(self):
q_number = self.lineEdit.text()
q_localtime = time.localtime()
q_enter_time = time.strftime("%H:%M:%S", q_localtime)
# q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง
if not q_number.isspace() and q_number != '':
# เรียก getDestination_id เพื่อหาค่า des_id เพื่อใช้ในคำสั่ง sql INSERT
des_id = self.getDestination_id()
try:
insertQuery = QSqlQuery()
insertQuery.prepare(
"INSERT INTO queue " + "(q_number,q_enter_time,des_id) " +
"VALUES " + f"('{q_number}','{q_enter_time}',{des_id})")
print('Query = ' + insertQuery.lastQuery())
if insertQuery.exec():
print('INSERT COMPLETE')
self.loadData()
else:
print('INSERT FALSE = ' + insertQuery.lastError().text())
except (Error) as e:
print(
str(time.strftime("%H:%M:%S : ", time.localtime())) +
'ERROR :' + str(e))
else:
self.showDialog('กรุณากรอกหมายเลขที่จะเพิ่มก่อน')
def getDestination_id(self):
# สร้างมาเพื่อให้ return ค่า des_id ในตาราง destination กลับไป
temp = self.comboBox.currentText()
desModel = QSqlQueryModel()
selectQuery = QSqlQuery()
selectQuery.prepare('SELECT des_id,des_name From destination')
if selectQuery.exec():
desModel.setQuery(selectQuery)
for i in range(desModel.rowCount()):
if temp == desModel.index(i, 1).data():
return desModel.index(i, 0).data()
else:
pass
else:
print('SELECT FALSE = ' + selectQuery.lastError().text())
def insertDev(self):
j = 10
for i in range(50):
q_number = random.randint(0, 100000)
q_enter_time = f'18:{j}:00'
des_id = 4
insertQuery = QSqlQuery()
insertQuery.prepare("INSERT INTO queue " +
"(q_number,q_enter_time,des_id) " + "VALUES " +
f"('{q_number}','{q_enter_time}',{des_id})")
insertQuery.exec()
self.model.select()
j += 1
print('insertDEV complete')
def searchData(self):
self.fetchData()
q_number = self.lineEdit.text()
totleRow = self.tableView.model().rowCount()
foundStatus = False
# q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง
if not q_number.isspace() and q_number != '':
i = 0
for i in range(totleRow):
# เก็บค่า q_number จาก tableView
s_number = self.tableView.model().data(
self.tableView.model().index(i, 1))
# เทียบค่า q_number ของ lineEdit กับ tableView
if q_number == s_number:
# print('found this number.')
foundStatus = True
break
else:
foundStatus = False
if foundStatus == True:
self.tableView.selectRow(i)
else:
self.showDialog('ค้นหมายเลข ' + q_number + ' ไม่พบ')
else:
self.showDialog('กรุณากรอกหมายเลขที่จะค้นก่อน')
def deleteData(self):
try:
# เก็บค่า text จาก column
current_row = self.tableView.selectedIndexes()
current_itemUse = current_row[0]
q_number = self.tableView.model().data(
self.tableView.model().index(current_itemUse.row(), 1))
except:
self.showDialog('กรุณาเลือกหมายเลขที่จะลบก่อน')
return
if q_number == None:
self.showDialog('เกิดข้อผิดพลาดระหว่างการลบ')
self.model.select()
return
# ยืนยันการลบด้วย code 1024
temp = self.confDelete(q_number)
if temp == 1024:
current_item = self.tableView.selectedIndexes()
for index in current_item:
self.model.removeRow(index.row())
self.model.select()
print('Record deleted.')
self.showDialog('ลบหมายเลขแล้ว')
else:
print('User cancel delete this record.')
def confDelete(self, q_number):
# สร้าง dialog เพื่อยืนยันการลบ และ return code ยืนยันการลบด้วย code 1024
confMsg = QtWidgets.QMessageBox()
confMsg.setIcon(QtWidgets.QMessageBox.Critical)
confMsg.setText('ยืนยันที่จะลบหมายเลข : ' + q_number)
confMsg.setWindowTitle('แจ้งเตือนการลบ')
confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok
| QtWidgets.QMessageBox.Cancel)
reval = confMsg.exec_()
return (reval)
def btn_delClicked(self):
button = QtGui.QGuiApplication.focusObject()
index = self.tableView.indexAt(button.pos())
if index.isValid():
print(index.row(), index.column())
def showDialog(self, message):
msg = QtWidgets.QMessageBox()
msg.setWindowTitle('แจ้งเตือน')
msg.setIcon(QtWidgets.QMessageBox.Information)
msg.setText(
# message + str(time.strftime(" @ %H:%M:%S ", time.localtime())))
message)
msg.exec_()
def speak(self):
try:
# เก็บค่า text จาก column
current_row = self.tableView.selectedIndexes()
current_inUse = current_row[0]
q_numberText = self.tableView.model().data(
self.tableView.model().index(current_inUse.row(), 1))
des_idText = self.tableView.model().data(
self.tableView.model().index(current_inUse.row(), 5))
except:
self.showDialog('กรุณาเลือกหมายเลขที่จะเรียกก่อน')
return
# ยืนยันการเรียกด้วย code 1024
temp = self.confSpeak(q_numberText)
q_localtime = time.localtime()
q_call_time = time.strftime("%H:%M:%S", q_localtime)
if temp == 1024:
# pyttsx3 engine
# engine = pyttsx3.init()
# """ RATE"""
# rate = engine.getProperty('rate')
# #print (rate)
# engine.setProperty('rate', 100)
# """VOLUME"""
# volume = engine.getProperty('volume')
# #print (volume)
# engine.setProperty('volume',1.0)
# """VOICE"""
# voices = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Speech\Voices\Tokens\TTS_THAI'
# engine.setProperty('voice',voices)
# engine.say('ขอเชิญหมายเลข' + q_numberText + 'ที่ช่อง' + des_idText + 'นะครับ')
# print('result = :'+ q_numberText)
# engine.runAndWait()
# engine.stop()
# gTTS engine
speech_text = 'ขอเชิญคุณ ' + q_numberText + ' ที่ช่อง ' + des_idText + ' ค่ะ'
tts = gTTS(speech_text, lang='th')
tts.save('temp.mp3')
speech_file = pyglet.media.load('temp.mp3', streaming=False)
speech_file.play()
sleep(speech_file.duration) # prevent from killing
os.remove('temp.mp3') # remove temperory file
try:
insertQuery = QSqlQuery()
insertQuery.prepare("UPDATE queue SET q_call_time = " +
f"'{q_call_time}'" +
" , q_call_status = 1 WHERE q_number = " +
f"'{q_numberText}'")
print('Query after speek ' + insertQuery.lastQuery())
if insertQuery.exec():
print('UPDATE COMPLETE')
self.loadData()
else:
print('UPDATE FALSE = ' + insertQuery.lastError().text())
except (Error) as e:
print('ERROR : ' + str(e))
else:
print('User decided cancel call.')
def confSpeak(self, q_numberText):
# สร้าง dialog เพื่อยืนยันการเรียกและ return code ยืนยันการเรียกด้วย code 1024
confMsg = QtWidgets.QMessageBox()
confMsg.setIcon(QtWidgets.QMessageBox.Warning)
confMsg.setText('ยืนยันที่จะเรียกหมายเลข : ' + q_numberText)
confMsg.setWindowTitle('แจ้งเตือน')
confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok
| QtWidgets.QMessageBox.Cancel)
reval = confMsg.exec_()
return (reval)
def fetchData(self):
self.model.select()
# fetch ข้อมูลจากตารางออกมาให้หมด มีผลตอนใช้ search
while self.model.canFetchMore() == True:
self.model.fetchMore()
print('rowCoundt = ' + str(self.model.rowCount()))
self.tableView.scrollToBottom()
class Window(QMainWindow):
def __init__(self):
super().__init__()
self.ui = mainform.Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'db/db_test.sqlite3'
self.ui.lbl_full_path_db.setText(self.db_path)
self.ui.btn_db_open.clicked.connect(self.db_file_open)
self.ui.btn_add.clicked.connect(self.db_add)
self.ui.btn_add.setEnabled(False)
self.ui.btn_del.clicked.connect(self.db_del)
self.ui.btn_del.setEnabled(False)
self.ui.save_changes.clicked.connect(self.save_change_db)
self.ui.save_changes.setEnabled(False)
self.ui.mnu_exit.triggered.connect(self.close)
self.ui.tablelist.setEnabled(False)
self.db = None
self.db_model = None
self.open_db()
self.ui.tablelist.currentIndexChanged.connect(self.show_table)
def open_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
# self.show_table()
def get_tables_name(self):
self.ui.tablelist.clear()
for table_name in self.db.tables():
self.ui.tablelist.addItem(table_name)
def db_file_open(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.lbl_full_path_db.setText(self.db_path)
self.db.close()
self.open_db()
self.ui.tablelist.setEnabled(True)
self.ui.btn_add.setEnabled(True)
self.ui.btn_del.setEnabled(True)
self.ui.save_changes.setEnabled(True)
self.ui.btn_db_open.setEnabled(False)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.tablelist.currentText()
if table == 'goods':
self.create_goods_table_model()
elif table == 'employees':
self.create_employees_table_model()
elif table == 'units':
self.create_units_table_model()
elif table == 'vendors':
self.create_vendors_table_model()
elif table == 'positions':
self.create_positions_table_model()
elif table == 'categories':
self.create_categories_table_model()
else:
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.hideColumn(0)
view.setItemDelegate(QSqlRelationalDelegate(view))
def db_add(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def db_del(self):
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(
2, QSqlRelation('units', 'unit_id', 'unit_name'))
self.table_model.setRelation(
3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.setRelation(
4, QSqlRelation('vendors', 'vendor_id', 'vendor_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название продукта')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal,
'Категория продукта')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Поставщик')
self.table_model.select()
def create_units_table_model(self):
self.table_model.setTable('units')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(
2, QSqlRelation('positions', 'position_id', 'position_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'ФИО сотрудника')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_positions_table_model(self):
self.table_model.setTable('positions')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_vendors_table_model(self):
self.table_model.setTable('vendors')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Наименование')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Форма собственности')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Телефон')
self.table_model.setHeaderData(5, QtCore.Qt.Horizontal,
'Электронная почта')
self.table_model.select()
def create_categories_table_model(self):
self.table_model.setTable('categories')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название категории')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Описание категории')
self.table_model.select()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(
f'{self.table_model.lastError().text()}')
class Components(object):
def __init__(self, connection):
self.connection = connection
self.model = QSqlRelationalTableModel()
self.model.setTable('customnetwork_component')
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setRelation(0, QSqlRelation('component', 'id', 'ticket'))
self.model.setRelation(1, QSqlRelation('customnetwork', 'id', 'name'))
self.model.select()
self.model.setHeaderData(0, Qt.Horizontal, "Ticket")
def getModel(self):
return self.model
def create(self, customnetwork_id, tickets):
# TODO: capturar excepciones de error que se pueden dar
log.debug(tickets)
query = QSqlQuery()
last = self.model.rowCount()
id = last + 1
self.connection.open()
for ticket in tickets:
comp_id = self._insert_component_table(query, ticket)
ok = self.\
_insert_customernetwork_component_table(query,
customnetwork_id,
comp_id)
self.model.select()
self.connection.close()
return id
def _insert_customernetwork_component_table(self, query, customnetwork_id,
comp_id):
query.prepare('insert into customnetwork_component values (?,?);')
query.addBindValue(comp_id)
query.addBindValue(customnetwork_id)
return query.exec_()
def _insert_component_table(self, query, ticket):
ok = query.exec_("select id from component;")
id = 0
if ok:
query.last()
last_id = int(query.value(0))
id = last_id + 1
query.prepare('insert into component values (?,?,?,?);')
query.addBindValue(id)
query.addBindValue(ticket)
query.addBindValue('_generic_description_')
query.addBindValue(GENERIC_SECTOR_CODE_ID)
if query.exec_():
return id
else:
return -1
def update_components(self, id, tickets):
# TODO: capturar excepciones de error que se pueden dar
query = QSqlQuery()
self.connection.open()
sql = """DELETE FROM customnetwork_component
WHERE customnetwork_id = ?;
"""
query.prepare(sql)
query.addBindValue(id)
ok = query.exec_()
if ok:
for ticket in tickets:
sql = "SELECT id, ticket FROM component WHERE ticket = '{}';"\
.format(ticket)
if query.exec_() and query.next():
component_id = int(query.value(0))
query.clear()
sql = """INSERT into customnetwork_component
VALUES (?, ?);
"""
query.prepare(sql)
query.addBindValue(component_id)
query.exec_()
else:
comp_id = self._insert_component_table(query, ticket)
ok = self.\
_insert_customernetwork_component_table(query,
id,
comp_id)
self.model.select()
self.connection.close()
return ok
def delete_cnc(self, customnetwork_id):
query = QSqlQuery()
self.connection.open()
query_str = 'delete from customnetwork_component where '
' customnetwork_id = ?;'
query.prepare(query)
query.addBindValue(customnetwork_id)
ok = query.exec_()
self.model.select()
self.connection.close()
return ok
class Cars_data(QWidget):
def __init__(self):
super().__init__()
self.setObjectName("Main window")
self.setGeometry(QRect(500, 300, 430, 300))
self.setWindowTitle("Транспортное средство")
self.btn_add = QPushButton(self)
self.btn_add.setObjectName("btn_add")
self.btn_add.setText("Добавить")
self.btn_delete = QPushButton(self)
self.btn_delete.setObjectName("btn_delete")
self.btn_delete.setText("Удалить")
self.btn_exit = QPushButton(self)
self.btn_exit.setObjectName("btn_exit")
self.btn_exit.setText("Выход")
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('ocenka.db')
self.model = QSqlRelationalTableModel(self)
self.model.setTable('car_data')
self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
self.model.select()
self.model.setHeaderData(0, Qt.Horizontal, "Номер ТС")
self.model.setHeaderData(1, Qt.Horizontal, "Марка автомобиля")
self.model.setHeaderData(2, Qt.Horizontal, "Регистрационный номер")
self.model.setHeaderData(3, Qt.Horizontal, "Цвет")
self.model.setHeaderData(4, Qt.Horizontal, "Пробег")
self.model.setHeaderData(5, Qt.Horizontal, "Год выпуска")
self.model.setHeaderData(6, Qt.Horizontal, "Страна производства")
self.model.setHeaderData(7, Qt.Horizontal, "Место использования")
self.model.setHeaderData(8, Qt.Horizontal, "Страна импортёр")
self.model.setHeaderData(9, Qt.Horizontal, "Серия и ПТС")
self.model.setHeaderData(10, Qt.Horizontal, "VIN")
self.model.setHeaderData(11, Qt.Horizontal, "Номер двигателя")
self.model.setHeaderData(12, Qt.Horizontal, "Номер кузова")
self.view1 = self.createView("Table Model (View 1)", self.model)
self.query = QSqlQuery(self.db)
self.query.exec_("PRAGMA Foreign_keys = ON")
layout = QHBoxLayout()
layout.addWidget(self.btn_add)
layout.addWidget(self.btn_delete)
layout.addWidget(self.btn_exit)
vlayout = QVBoxLayout()
vlayout.addWidget(self.view1)
vlayout.addLayout(layout)
self.setLayout(vlayout)
self.show()
self.view1.clicked.connect(self.findrow)
self.btn_add.clicked.connect(self.addrow)
self.btn_delete.clicked.connect(
lambda: self.model.removeRow(self.view1.currentIndex().row()))
self.btn_exit.clicked.connect(self.close)
def findrow(self, i):
delrow = i.row()
def addrow(self):
print(self.model.rowCount())
ret = self.model.insertRows(self.model.rowCount(), 1)
return ret
def createView(self, title, model):
self.model = model
self.title = title
view = QTableView(self)
view.setModel(self.model)
view.setWindowTitle(self.title)
view.resizeColumnsToContents()
view.setGeometry(25, 25, 380, 200)
view.hideColumn(0)
view.setItemDelegate(QSqlRelationalDelegate(view))
return view
class Materials(QWidget):
def __init__(self):
super().__init__()
self.initUi()
def initUi(self):
self.setObjectName("Main window")
self.resize(370, 300)
self.setWindowTitle("Материалы")
self.btn_add = QPushButton(self)
self.btn_add.setObjectName("btn_add")
self.btn_add.setText("Добавить")
self.btn_delete = QPushButton(self)
self.btn_delete.setObjectName("btn_delete")
self.btn_delete.setText("Удалить")
self.btn_exit = QPushButton(self)
self.btn_exit.setObjectName("btn_exit")
self.btn_exit.setText("Выход")
self.btn_refresh = QPushButton(self)
self.btn_refresh.setText("Обновить")
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('ocenka.db')
self.db.open()
self.model = QSqlRelationalTableModel(self)
self.model.setTable("v_materials")
self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange)
self.model.setJoinMode(QSqlRelationalTableModel.LeftJoin)
self.model.setHeaderData(0, Qt.Horizontal, "Идентификатор")
self.model.setHeaderData(1, Qt.Horizontal, "Наименование")
self.model.setHeaderData(2, Qt.Horizontal, "Ед.изм.")
self.model.setHeaderData(3, Qt.Horizontal, "Количество")
self.model.setHeaderData(4, Qt.Horizontal, "Стоимость")
self.model.setHeaderData(5, Qt.Horizontal, "Сумма")
self.model.setHeaderData(6, Qt.Horizontal, "Выбрать")
#self.proxyModel = QSortFilterProxyModel(self)
#self.proxyModel.setSourceModel(self.model)
self.view = QTableView(self)
self.view.setSortingEnabled(True)
self.view.setModel(self.model)
self.view.resizeColumnsToContents()
self.view.hideColumn(0)
self.view.horizontalHeader().setStretchLastSection(True)
#self.view.hideColumn(1)
self.view.setItemDelegate(QSqlRelationalDelegate(self.model))
self.model.select()
self.layout = QHBoxLayout()
self.layout.addWidget(self.btn_add)
self.layout.addWidget(self.btn_delete)
self.layout.addWidget(self.btn_exit)
self.layout.addWidget(self.btn_refresh)
vlayout = QVBoxLayout()
vlayout.addWidget(self.view)
vlayout.addLayout(self.layout)
self.setLayout(vlayout)
self.show()
self.view.clicked.connect(self.findrow)
self.btn_add.clicked.connect(self.addrow)
self.btn_delete.clicked.connect(lambda: self.model.removeRow(self.view.currentIndex().row()))
self.btn_exit.clicked.connect(self.close_event)
self.btn_refresh.clicked.connect(lambda: self.model.select())
def close_database(self):
self.view.setModel(None)
del self.model
self.db.close()
del self.db
QSqlDatabase.removeDatabase('ocenka.db')
self.close()
def close_event(self, event):
self.close_database()
def findrow(self, i):
delrow = i.row()
def addrow(self):
print(self.model.rowCount())
ret = self.model.insertRows(self.model.rowCount(), 1)
return ret
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.tableView)
# tableView显示属性设置
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(100)
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.tabModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): ##打开数据表
self.tabModel = QSqlRelationalTableModel(self, self.DB) #数据表
self.tabModel.setTable("studInfo") #设置数据表
self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit
) #数据保存方式，OnManualSubmit , OnRowChange
self.tabModel.setSort(self.tabModel.fieldIndex("studID"),
Qt.AscendingOrder) #排序
if (self.tabModel.select() == False): #查询数据失败
QMessageBox.critical(
self, "错误信息",
"打开数据表错误,错误信息\n" + self.tabModel.lastError().text())
return
self.__getFieldNames() #获取字段名和序号
##字段显示名
self.tabModel.setHeaderData(self.fldNum["studID"], Qt.Horizontal, "学号")
self.tabModel.setHeaderData(self.fldNum["name"], Qt.Horizontal, "姓名")
self.tabModel.setHeaderData(self.fldNum["gender"], Qt.Horizontal, "性别")
self.tabModel.setHeaderData(self.fldNum["departID"], Qt.Horizontal,
"学院")
self.tabModel.setHeaderData(self.fldNum["majorID"], Qt.Horizontal,
"专业")
## 设置代码字段的查询关系数据表
self.tabModel.setRelation(self.fldNum["departID"],
QSqlRelation("departments", "departID",
"department")) #学院
self.tabModel.setRelation(self.fldNum["majorID"],
QSqlRelation("majors", "majorID",
"major")) #专业
self.selModel = QItemSelectionModel(self.tabModel) #关联选择模型
##selModel当前项变化时触发currentChanged信号
self.selModel.currentChanged.connect(self.do_currentChanged)
##选择行变化时
## self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.tabModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
delgate = QSqlRelationalDelegate(self.ui.tableView)
self.ui.tableView.setItemDelegate(delgate) #为关系型字段设置缺省代理组件
self.tabModel.select() #必须重新查询数据
##更新actions和界面组件的使能状态
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecAppend.setEnabled(True)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actFields.setEnabled(True)
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #打开数据表
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##保存修改
def on_actSubmit_triggered(self):
res = self.tabModel.submitAll()
if (res == False):
QMessageBox.information(
self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text())
else:
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() ##取消修改
def on_actRevert_triggered(self):
self.tabModel.revertAll()
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() ##添加记录
def on_actRecAppend_triggered(self):
self.tabModel.insertRow(self.tabModel.rowCount(),
QModelIndex()) #在末尾添加一个记录
curIndex = self.tabModel.index(self.tabModel.rowCount() - 1,
1) #创建最后一行的ModelIndex
self.selModel.clearSelection() #清空选择项
self.selModel.setCurrentIndex(
curIndex, QItemSelectionModel.Select) #设置刚插入的行为当前选择行
@pyqtSlot() ##插入记录
def on_actRecInsert_triggered(self):
curIndex = self.ui.tableView.currentIndex() #QModelIndex
self.tabModel.insertRow(curIndex.row(), QModelIndex())
self.selModel.clearSelection() #清除已有选择
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
@pyqtSlot() ##删除记录
def on_actRecDelete_triggered(self):
curIndex = self.selModel.currentIndex() #获取当前选择单元格的模型索引
self.tabModel.removeRow(curIndex.row()) #删除最后一行
@pyqtSlot() ##显示字段列表
def on_actFields_triggered(self):
emptyRec = self.tabModel.record() #获取空记录，只有字段名
str = ''
for i in range(emptyRec.count()):
str = str + emptyRec.fieldName(i) + '\n'
QMessageBox.information(self, "所有字段名", str)
## =============自定义槽函数===============================
def do_currentChanged(self, current, previous): ##更新actPost和actCancel 的状态
self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) #有未保存修改时可用
self.ui.actRevert.setEnabled(self.tabModel.isDirty())
class bill(QMainWindow, QWidget):
def __init__(self):
super().__init__()
self.initialize()
def initialize(self):
# set geometry, window title
self.setMinimumSize(1350, 600)
self.setWindowTitle("Smart Billing")
self.Connection()
self.createTable()
# show menu bar
self.MenuBar()
self.Tab()
self.show()
def Connection(self):
""" create the connection to the sqlite3 and open the database"""
# setup the connection to sqlite3 and open the billing database
# if database not present create one
database = QSqlDatabase.addDatabase("QSQLITE")
database.setDatabaseName("billing.db")
if not database.open():
print("Unable to connect")
sys.exit(1) # fail to connect to the database
# tables needed for billing application
table_need = {'items', 'billBook'}
# table that are not found
table_not_found = table_need - set(database.tables())
# if tables are not found it give a critical message and exits
if table_not_found:
QMessageBox.critical(
None, "Error",
f'Following database tables are not found:{table_not_found}')
sys.exit(1)
def MenuBar(self):
""" this create the main menu"""
# create print action
print_act = QAction("print", self)
print_act.setShortcut("Ctrl+P")
print_act.setIcon(QIcon("icons/018-printer.png"))
print_act.setEnabled(False)
print_act.triggered.connect(self.Print)
# create exit action
exit_act = QAction("exit", self)
exit_act.setShortcut("Ctrl+Q")
exit_act.setIcon(QIcon("icons/008-logout.png"))
exit_act.triggered.connect(self.closeEvent)
# create save action
save_act = QAction("save", self)
save_act.setShortcut("Ctrl+S")
save_act.setIcon(QIcon("icons/004-folder.png"))
save_act.triggered.connect(self.Save)
# create search_bill_act action
search_bill_act = QAction("Search Bill", self)
search_bill_act.setShortcut("Ctrl+F")
search_bill_act.setIcon(QIcon("icons/015-magnifier.png"))
search_bill_act.triggered.connect(self.Find_Bill)
# create insert item action and set the current tab index to 1 because Items index is 1
insert_act = QAction("Insert item", self)
insert_act.setShortcut("Ctrl+I")
insert_act.setIcon(QIcon("icons/007-download.png"))
insert_act.triggered.connect(self.Change_to_item)
# create bill book action and set the current tab index to 2 because bill book index is 2
billBook_act = QAction("Bill Book", self)
billBook_act.setShortcut("Ctrl+B")
billBook_act.setIcon(QIcon("icons/open-book.png"))
billBook_act.triggered.connect(self.Change_to_billBook)
# create color_act action
color_act = QAction("color", self)
color_act.setShortcut("Ctrl+Shift+C")
color_act.setIcon(QIcon("icons/001-painter-palette.png"))
color_act.triggered.connect(self.Color)
# create font action
font_act = QAction("font", self)
font_act.setShortcut("Ctrl+Shift+F")
font_act.setIcon(QIcon("icons/006-font.png"))
font_act.triggered.connect(self.Font)
# create help menu
# create About action
about_act = QAction("About", self)
about_act.setIcon(QIcon("icons/023-information-button.png"))
about_act.triggered.connect(self.About)
# feedback action
feedback_act = QAction("Feedback", self)
feedback_act.setIcon(QIcon("icons/022-feedback.png"))
feedback_act.triggered.connect(self.FeedBack)
# create main menu and add sub menu and action
# create menu
menu = self.menuBar()
menu.setNativeMenuBar(False)
# create file menu
file = menu.addMenu("File")
file.addAction(print_act)
file.addAction(save_act)
file.addAction(exit_act)
# create tools menu
tools = menu.addMenu("Tools")
# insert search bill action
tools.addAction(search_bill_act)
# insert insert item
tools.addAction(insert_act)
# insert bill book action
tools.addAction(billBook_act)
# create format
Format = menu.addMenu("Format")
# add font to format menu
Format.addAction(font_act)
Format.addAction(color_act)
# create help menu
help_menu = menu.addMenu("help")
# add about_act to help
help_menu.addAction(about_act)
help_menu.addAction(feedback_act)
def Tab(self):
""" create the tab for bill, insert, analysis"""
# create the tab
self.tab = QTabWidget(self)
# create the tab object bill, insert_item, analysis
self.bill_tab = QWidget()
self.insert_tab = QWidget()
self.billBook_tab = QWidget()
# add the tab objects to tab
self.tab.addTab(self.bill_tab, "Bill")
self.tab.addTab(self.insert_tab, "Items")
self.tab.addTab(self.billBook_tab, "Bill Book")
# call the the tabs
self.billwidget()
self.insertwidget()
self.billBook()
# create the main
self.main_layout = QWidget()
self.setCentralWidget(self.main_layout)
self.tab_layout = QHBoxLayout()
self.tab_layout.addWidget(self.tab)
self.main_layout.setLayout(self.tab_layout)
def billwidget(self):
# buyer information
# buyers name
self.buyer_name = QLineEdit()
self.buyer_name.resize(100, 100)
self.buyer_name.setPlaceholderText("Janath Jsk")
# customizing the line edit
self.buyer_name.setStyleSheet("color: rgb(120, 60, 5)")
# buyer_name clear button
buyer_name_clear = QPushButton()
buyer_name_clear.setIcon(QIcon("icons/005-cancel.png"))
buyer_name_clear.setIconSize(QSize(19, 19))
buyer_name_clear.clicked.connect(self.buyer_name.clear)
# buyers mobile number
self.buyer_mobile = QLineEdit()
self.buyer_mobile.resize(100, 100)
self.buyer_mobile.setInputMask("000-000-0000")
# buyer mobile clear
buyer_mobile_clear = QPushButton()
buyer_mobile_clear.setIcon(QIcon("icons/005-cancel.png"))
buyer_mobile_clear.setIconSize(QSize(19, 19))
buyer_mobile_clear.clicked.connect(self.buyer_mobile.clear)
buyer_mobile_clear.setObjectName("clear")
# buyers address
self.buyers_address = QTextEdit()
# buyer address clear
address_clear_bt = QPushButton()
address_clear_bt.setIcon(QIcon("icons/005-cancel.png"))
address_clear_bt.setIconSize(QSize(19, 19))
address_clear_bt.clicked.connect(self.buyers_address.clear)
# create main layout and set as central widget
bill_layout = QVBoxLayout()
# create layout and add to main layout
form_layout = QFormLayout()
form_layout.setSpacing(0)
# horizontal layout for buyer name and clear
name_h_layout = QHBoxLayout()
name_h_layout.addWidget(self.buyer_name)
name_h_layout.addWidget(buyer_name_clear)
# add name horizontal to form layout
form_layout.addRow("Name", name_h_layout)
# create horizontal layout for mobile number and clear button
mobile_h_layout = QHBoxLayout()
mobile_h_layout.addWidget(self.buyer_mobile)
mobile_h_layout.addWidget(buyer_mobile_clear)
# add mobile horizontal layout in form layout
form_layout.addRow("Mobile Number", mobile_h_layout)
# add address and clear button
address_layout = QHBoxLayout()
address_layout.addWidget(self.buyers_address)
address_layout.addWidget(address_clear_bt)
# add address and address clear button to form layout
form_layout.addRow("Address", address_layout)
# create buyer information box
buyer_information_gp = QGroupBox("Buyer Information")
# set the form layout as buyer information layout
buyer_information_gp.setLayout(form_layout)
# add buyer information box to bill_layout
bill_layout.addWidget(buyer_information_gp)
bill_layout.addStretch()
# create the combo box for selecting the item from the sqlite3 table items
# create item_dict and store the quantity and price in item_dict
self.items_dict = {}
# create the item query for getting data from items table
items_query = QSqlQuery()
items_query.exec_("SELECT name, quantity, price FROM items")
# store the selected data in items_dict
while items_query.next():
self.items_dict[items_query.value(0)] = (items_query.value(1),
items_query.value(2))
# create item list and store the items_dict keys
items_list = list(self.items_dict.keys())
# create the combo for selecting the item and when index was changed connect to Quantity to enable the quantity
# box and set the range
self.items_combo = QComboBox()
self.items_combo.addItems(items_list)
self.items_combo.currentIndexChanged.connect(self.Quantity)
# create the spin box to enter the quatity
self.item_quantity = QSpinBox()
self.item_quantity.setEnabled(False)
# create the form layout item and quantity information
item_form = QFormLayout()
item_form.addRow("Item Name: ", self.items_combo)
item_form.addRow("Quantity: ", self.item_quantity)
# create the add push button to add the item in bill
add_bt = QPushButton("Add to Bill")
add_bt.clicked.connect(self.AddToBill)
# create horizontal layout for button
bt_h_l = QHBoxLayout()
bt_h_l.addStretch()
bt_h_l.addWidget(add_bt)
bt_h_l.addStretch()
# create the vertical layout for item information box
item_v_l = QVBoxLayout()
item_v_l.addLayout(item_form)
item_v_l.addLayout(bt_h_l)
# create the group box for item information
items_inf = QGroupBox("Add items")
items_inf.setLayout(item_v_l)
# add item information group to bill_layout
bill_layout.addWidget(items_inf)
# items buyed
self.bill_model = QStandardItemModel()
self.bill_table = QTableView()
self.bill_table.SelectionMode(3)
self.bill_table.setModel(self.bill_model)
# set the column count for bill_model
self.bill_model.setColumnCount(4)
# strech the column of bill_table
self.bill_table.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# set the horizontal header labels
header = ["ITEMS", "QUANTITY", "PRICE", "TOTAL"]
self.bill_model.setHorizontalHeaderLabels(header)
# create grid layout
bill1_layout = QGridLayout()
bill1_layout.addLayout(bill_layout, 0, 0)
bill1_layout.addWidget(self.bill_table, 0, 1)
# set bill_tab layout as bill_layout
self.bill_tab.setLayout(bill1_layout)
def Quantity(self, text):
""" after enter the item it first set the range of quantity box and set enable"""
self.item_quantity.setRange(1, self.items_dict[text][0])
self.item_quantity.setEnabled(True)
def AddToBill(self):
pass
def createTable(self):
# create the sql item table model
# create item model
self.itemTable = QSqlRelationalTableModel()
# set items table as table for item table mode
self.itemTable.setTable('items')
# set header names
self.itemTable.setHeaderData(self.itemTable.fieldIndex('id'),
Qt.Horizontal, "ITEM ID")
self.itemTable.setHeaderData(self.itemTable.fieldIndex('name'),
Qt.Horizontal, "ITEM NAME")
self.itemTable.setHeaderData(self.itemTable.fieldIndex('quantity'),
Qt.Horizontal, "STOCK")
self.itemTable.setHeaderData(self.itemTable.fieldIndex('price'),
Qt.Horizontal, "PRICE")
self.itemTable.select()
# create bill book model
self.billBookTable = QSqlRelationalTableModel()
# set billbook table as table for bill book model
self.billBookTable.setTable('billBook')
# set the header name
self.billBookTable.setHeaderData(self.billBookTable.fieldIndex('id'),
Qt.Horizontal, 'BILL NO')
self.billBookTable.setHeaderData(self.billBookTable.fieldIndex('name'),
Qt.Horizontal, "NAME")
self.billBookTable.setHeaderData(
self.billBookTable.fieldIndex('number'), Qt.Horizontal,
"MOBILE NO")
self.billBookTable.setHeaderData(
self.billBookTable.fieldIndex('items'), Qt.Horizontal, "ITEMS")
self.billBookTable.setHeaderData(
self.billBookTable.fieldIndex('amount'), Qt.Horizontal, "AMOUNT")
self.billBookTable.select()
def insertwidget(self):
""" create table """
# table view and create sql model and set sql mode has table view mode
title = QLabel("Items")
title.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed)
title.setStyleSheet("font: bold 24px")
self.items_view = QTableView()
self.items_view.setModel(self.itemTable)
self.items_view.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# set the selection mode and behavior for item_view
self.items_view.setSelectionMode(QTableView.SingleSelection)
self.items_view.setSelectionBehavior(QTableView.SelectRows)
# create delegate for item_view
delegate = QSqlRelationalDelegate(self.items_view)
self.items_view.setItemDelegate(delegate)
# push button
# add row button
additem_button = QPushButton("Add item")
additem_button.setIcon(QIcon("icons/007-download.png"))
additem_button.setIconSize(QSize(10, 10))
additem_button.clicked.connect(self.additem)
# delete row push button
deleteitem_bt = QPushButton("delete item")
deleteitem_bt.setIcon(QIcon("icons/005-cancel.png"))
deleteitem_bt.setIconSize(QSize(10, 10))
deleteitem_bt.clicked.connect(self.deleteitem)
# create vertical layout for push button
push_layout = QHBoxLayout()
push_layout.addWidget(additem_button)
push_layout.addStretch()
push_layout.addWidget(deleteitem_bt)
# create horizontal layout for table and push button
tab_layout = QVBoxLayout()
tab_layout.addWidget(title, Qt.AlignCenter)
push_w = QWidget()
push_w.setLayout(push_layout)
tab_layout.addWidget(push_w)
tab_layout.addWidget(self.items_view)
# set tab layout as tab widget layout
self.insert_tab.setLayout(tab_layout)
def billBook(self):
""" show the bills """
title = QLabel("Bill Book")
title.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed)
title.setStyleSheet("font: bold 24px")
self.Book_view = QTableView()
self.Book_view.setModel(self.billBookTable)
# strech the horizontal header only
self.Book_view.horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
# set selection behavior to select the entire row
self.Book_view.setSelectionBehavior(QTableView.SelectRows)
bill_V_l = QVBoxLayout()
bill_V_l.addWidget(title)
bill_V_l.addWidget(self.Book_view)
self.billBook_tab.setLayout(bill_V_l)
def Change_to_billBook(self):
self.tab.setCurrentIndex(2)
def Print(self):
pass
def closeEvent(self, event):
""" exit the application"""
quit_msg = QMessageBox.question(self, "Quit", "You are sure to exit?",
QMessageBox.Yes | QMessageBox.No,
QMessageBox.Yes)
if quit_msg == QMessageBox.Yes:
event.accept()
else:
event.ignore()
def Font(self):
""" open the font dialog box and user able to select font """
font, ok = QFontDialog.getFont()
if ok:
self.setFont(font)
def Save(self):
pass
def Find_Bill(self):
pass
def Change_to_item(self):
""" change to items tab"""
self.tab.setCurrentIndex(1)
def Color(self):
pass
def About(self):
QMessageBox.about(self, "About", "Smart Billing created by Janath Jsk")
def additem(self):
print(1)
last = self.itemTable.rowCount()
print(last)
print(2)
self.itemTable.insertRow(last)
print(3)
id = 0
query = QSqlQuery()
query.exec_("SELECT MAX (id) FROM items")
print(4)
if query.next():
id = int(query.value(0))
print(5)
def deleteitem(self):
current = self.items_view.selectedIndexes()
for index in current:
self.itemTable.removeRow(index.row())
self.itemTable.select()
def FeedBack(self):
url = "https://ut4vq9uuz4l.typeform.com/to/SjBbjdId"
webbrowser.open_new_tab(url)
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)
VBOX_OBJ.addWidget(TV_OBJ)
# Добавляем кнопки
BTN_ADD = QPushButton("&Добавить запись")
class ServEmpl(QWidget):
"""
Klasa odpowiedzialna za widget pracownicy-usługi
"""
def __init__(self, parent, db):
super(QWidget, self).__init__(parent)
self.parent = parent
self.lbl_imie = QLabel()
self.lbl_nazwisko = QLabel()
self.view_p = QTableView()
self.view_pu = QTableView()
self.view_u = QTableView()
self.proxy_p = QSortFilterProxyModel(self)
self.proxy_pu = QSortFilterProxyModel(self)
self.proxy_u = QSortFilterProxyModel(self)
self.id_pracownik = -1
# Parametry połączenia z bazą
self.model_p = QSqlTableModel(self, db)
self.model_pu = QSqlRelationalTableModel(self, db)
self.model_u = QSqlTableModel(self, db)
self.initUI()
def initUI(self):
"""
Inicjuje UI
"""
pracownicy = '''
Kliknij <u>tutaj</u> aby wybrać danego pracownika.
Poniżej znajdują się wszystkie usługi, jakie dany pracownik może robić.
'''
uslugi = '''
Kliknij dwukrotnie <u>tutaj</u> aby <b>przypisać</b> daną usługę do wybranego pracownika.
'''
pracownik_uslugi = '''
Kliknij dwukrotnie <u>tutaj</u> aby <b>usunąć</b> przypisanie danej usługi pracownikowi.
'''
self.table_init_u()
self.table_init_p()
self.table_init_pu()
self.view_p.setToolTip(pracownicy)
self.view_u.setToolTip(uslugi)
self.view_pu.setToolTip(pracownik_uslugi)
# Tworzenie kontrolek
lbl_wyszukaj = QLabel('Wyszukaj pracownika:')
lbl_imie_static = QLabel('Imię:')
lbl_nazwisko_static = QLabel('Nazwisko:')
txt_wysz = QLineEdit(self)
# Tworzenie widoków
centralbox = QHBoxLayout()
hbox_p = QHBoxLayout()
hbox_pu = QHBoxLayout()
hbox_u = QHBoxLayout()
vbox_right = QVBoxLayout()
vbox_left = QVBoxLayout()
groupbox_p = QGroupBox('Pracownicy')
groupbox_pu = QGroupBox('Wykonywane usługi')
groupbox_u = QGroupBox('Usługi')
formbox = QFormLayout()
# Metody
txt_wysz.textChanged.connect(self.searching)
self.view_p.clicked.connect(self.change_p)
self.view_u.doubleClicked.connect(lambda: self.change_add_remove(True))
self.view_pu.doubleClicked.connect(lambda: self.change_add_remove(False))
# Ustawianie widoków
formbox.addRow(lbl_wyszukaj, txt_wysz)
formbox.addRow(lbl_imie_static, self.lbl_imie)
formbox.addRow(lbl_nazwisko_static, self.lbl_nazwisko)
formbox.setSpacing(15)
vbox_left.addLayout(formbox)
vbox_left.addWidget(groupbox_u)
vbox_left.setSpacing(200)
hbox_p.addWidget(self.view_p)
hbox_pu.addWidget(self.view_pu)
hbox_u.addWidget(self.view_u)
groupbox_p.setLayout(hbox_p)
groupbox_pu.setLayout(hbox_pu)
groupbox_u.setLayout(hbox_u)
vbox_right.addWidget(groupbox_p)
vbox_right.addWidget(groupbox_pu)
vbox_right.setSpacing(25)
centralbox.addLayout(vbox_left)
centralbox.addLayout(vbox_right)
self.setLayout(centralbox)
self.show()
def change_p(self):
"""
Metoda edytująca zaznaczone wiersze - Wstawia wartości z wierszy w odpowiednie pola
"""
index = (self.view_p.selectionModel().currentIndex())
self.id_pracownik = index.sibling(index.row(), 0).data()
self.lbl_imie.setText("<b>" + index.sibling(index.row(), 2).data() + "</b>")
self.lbl_nazwisko.setText("<b>" + index.sibling(index.row(), 3).data() + "</b>")
# Odświeżanie widoku tabeli
query = QSqlQuery(
'SELECT uslugi.uslugi_id, uslugi.nazwa, uslugi.cena, uslugi.czas FROM uslugi, uzytkownik_usluga WHERE '
'uslugi.uslugi_id = uzytkownik_usluga.uslugi_id AND uzytkownik_usluga.uzytkownik_id = ' + str(
self.id_pracownik) + ';')
self.model_pu.setQuery(query)
self.view_pu.reset()
def change_add_remove(self, quest):
"""
Metoda ogólna odpowiadająca za dodawanie i usuwanie usług wybranemu pracownikowi
"""
tekst_p = "Czy chcesz dodać nową usługę do użytkownika {}?".format(self.lbl_imie.text())
if quest:
index = (self.view_u.selectionModel().currentIndex())
id_modify = index.sibling(index.row(), 0).data()
ret = QMessageBox.question(self, 'Dodawanie usługi', tekst_p,
QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if ret == QMessageBox.Yes:
query = 'INSERT INTO uzytkownik_usluga (uzytkownik_id, uslugi_id) VALUES (%s, %s); '
val = (
self.id_pracownik,
id_modify
)
if query_to_db(query, val):
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText('Usługa została dodana do usług oferowanych przez pracownika')
msg.setWindowTitle("Dodano usługę")
msg.exec_()
else:
index = (self.view_pu.selectionModel().currentIndex())
id_modify = index.sibling(index.row(), 0).data()
ret = QMessageBox.question(self, 'Usuwanie usługi', "Czy na pewno chcesz usunąć daną usługę?",
QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
if ret == QMessageBox.Yes:
query = 'DELETE FROM uzytkownik_usluga WHERE uzytkownik_id = %s AND uslugi_id = %s; '
val = (
self.id_pracownik,
id_modify
)
if query_to_db(query, val):
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setText('Usługa została dodana do usług oferowanych przez pracownika')
msg.setWindowTitle("Dodano usługę")
msg.exec_()
# Odświeżanie widoku tabeli
query = QSqlQuery(
'SELECT uslugi.uslugi_id, uslugi.nazwa, uslugi.cena, uslugi.czas FROM uslugi, uzytkownik_usluga WHERE '
'uslugi.uslugi_id = uzytkownik_usluga.uslugi_id AND uzytkownik_usluga.uzytkownik_id = ' + str(
self.id_pracownik) + ';')
self.model_pu.setQuery(query)
self.view_pu.reset()
def table_init_u(self):
"""
Inicjuje wygląd tabeli usługi
"""
self.model_u.setTable('uslugi')
# query = QSqlQuery('SELECT uzytkownik_id, uzytkownik_nazwa, imie, nazwisko, pracownik FROM uzytkownik;')
# self.model.setQuery(query)
self.model_u.select()
self.proxy_u.setSourceModel(self.model_u)
naglowki = {
'uslugi_id': 'ID',
'nazwa': 'Nazwa',
'cena': 'Cena',
"czas": 'Czas',
'Opis': 'Opis',
}
# Ustawianie nagłówków
ilosc_kolumn = self.model_u.columnCount()
for i in range(ilosc_kolumn):
nazwa_kolumn = self.model_u.headerData(i, Qt.Horizontal)
self.model_u.setHeaderData(i, Qt.Horizontal, naglowki[nazwa_kolumn])
self.view_u.setSizeAdjustPolicy(QAbstractScrollArea.AdjustToContentsOnFirstShow)
self.view_u.setSortingEnabled(True)
self.view_u.setAlternatingRowColors(True)
# Wczytanie danych
self.view_u.setModel(self.proxy_u)
self.view_u.hideColumn(0)
self.view_u.sortByColumn(1, Qt.AscendingOrder)
self.view_u.setEditTriggers(QAbstractItemView.NoEditTriggers)
def table_init_p(self):
"""
Inicjuje wygląd tabeli pracownicy
"""
self.model_p.setTable('pracownicy')
query = QSqlQuery('SELECT uzytkownik_id, uzytkownik_nazwa, imie, nazwisko, pracownik FROM uzytkownik WHERE pracownik = 1;')
self.model_p.setQuery(query)
# self.model_u.select()
self.proxy_p.setSourceModel(self.model_p)
naglowki = {
'uzytkownik_id': 'ID',
'uzytkownik_nazwa': 'Login',
'imie': 'Imię',
"nazwisko": 'Nazwisko',
'pracownik': 'Pracownik',
}
# Ustawianie nagłówków
ilosc_kolumn = self.model_p.columnCount()
for i in range(ilosc_kolumn):
nazwa_kolumn = self.model_p.headerData(i, Qt.Horizontal)
self.model_p.setHeaderData(i, Qt.Horizontal, naglowki[nazwa_kolumn])
self.view_p.setSizeAdjustPolicy(QAbstractScrollArea.AdjustToContentsOnFirstShow)
self.view_p.setSortingEnabled(True)
self.view_p.setAlternatingRowColors(True)
# Wczytanie danych
self.view_p.setModel(self.proxy_p)
self.view_p.hideColumn(0)
self.view_p.hideColumn(4)
self.view_p.sortByColumn(1, Qt.AscendingOrder)
self.view_p.setEditTriggers(QAbstractItemView.NoEditTriggers)
def table_init_pu(self):
"""
Inicjuje wygląd tabeli pracownicy-usługi
"""
query = QSqlQuery(
'SELECT uslugi.uslugi_id, uslugi.nazwa, uslugi.cena, uslugi.czas FROM uslugi, uzytkownik_usluga WHERE '
'uslugi.uslugi_id = uzytkownik_usluga.uslugi_id AND uzytkownik_usluga.uzytkownik_id = ' + str(
self.id_pracownik) + ';')
self.model_pu.setQuery(query)
self.proxy_pu.setSourceModel(self.model_pu)
naglowki = {
'uslugi_id': 'ID usługi',
'nazwa': 'Nazwa usługi',
'cena': 'Cena',
'czas': 'Czas'
}
# Ustawianie nagłówków
ilosc_kolumn = self.model_pu.columnCount()
for i in range(ilosc_kolumn):
nazwa_kolumn = self.model_pu.headerData(i, Qt.Horizontal)
self.model_pu.setHeaderData(i, Qt.Horizontal, naglowki[nazwa_kolumn])
self.view_pu.setSizeAdjustPolicy(QAbstractScrollArea.AdjustToContentsOnFirstShow)
self.view_pu.setSortingEnabled(True)
self.view_pu.setAlternatingRowColors(True)
# Wczytanie danych
self.view_pu.setModel(self.proxy_pu)
self.view_pu.hideColumn(0)
self.view_pu.sortByColumn(1, Qt.AscendingOrder)
self.view_pu.setEditTriggers(QAbstractItemView.NoEditTriggers)
@pyqtSlot(str)
def searching(self, text):
"""
Wyszukuje po wszystkich kolumnach tabeli
:param text:
"""
search = QRegExp(text,
Qt.CaseInsensitive,
QRegExp.RegExp
)
self.proxy_p.setFilterRegExp(search)
# Odpowiedzialne za kolumnę, po której filtruje
self.proxy_p.setFilterKeyColumn(1)
