def loadListForFindingType(self, findingType):
model = QSqlRelationalTableModel(self, self.dbm.db)
model.setTable(u"befundart")
model.removeColumn(0)
model.setFilter(
u"befundart = '{0}' AND befundart_detail IS NOT NULL".format(
findingType))
model.select()
if model.rowCount() < 1:
QMessageBox.warning(
self, "Result",
u"Für die Befundart '{0}' wurden keine Detail Einträge gefunden."
.format(findingType))
return False
self.uiFindingTypeDetailTableV.setModel(model)
self.uiFindingTypeDetailTableV.setSelectionBehavior(
QAbstractItemView.SelectRows)
self.uiFindingTypeDetailTableV.verticalHeader().setVisible(False)
self.uiFindingTypeDetailTableV.hideColumn(0)
if findingType != "Siedlung":
self.uiFindingTypeDetailTableV.hideColumn(2)
#self.uiRemarksTableV.model().insertColumn(0)
self.uiFindingTypeDetailTableV.resizeRowsToContents()
self.uiFindingTypeDetailTableV.resizeColumnsToContents()
self.uiFindingTypeDetailTableV.horizontalHeader(
).setStretchLastSection(True)
self.uiFindingTypeDetailTableV.selectionModel(
).selectionChanged.connect(self.generateFindingTypeDetail)
return True
class TablesEditFrom(QWidget, Ui_TablesEditForm):
def __init__(self, db_name, table_name):
super().__init__()
# uic.loadUi('tablesEditForm.ui', self)
self.setupUi(self)
self.close_btn.clicked.connect(self.close)
self.delete_btn.clicked.connect(self.delete_item)
self.add_btn.clicked.connect(self.add_item)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(db_name)
self.db.open()
self.db_model = QSqlRelationalTableModel(self)
self.db_model.setTable(table_name)
self.db_model.select()
self.tableView.setModel(self.db_model)
def __exit__(self, exc_type, exc_val, exc_tb):
self.db.close()
super().__exit__()
def delete_item(self):
self.db_model.deleteRowFromTable(self.tableView.currentIndex().row())
self.db_model.select()
def add_item(self):
self.db_model.insertRow(self.db_model.rowCount())
def openSearchFilmDialog(self):
"""Run method that performs all the real work"""
# show the dialog
self.searchFilmDlg.show()
#self.filmSelectionDlg.uiFilmNumberEdit.setFocus()
# Run the dialog event loop and See if OK was pressed
if self.searchFilmDlg.exec_():
# QMessageBox.warning(None, "FilmNumber", self.searchFilmDlg.generateSearchQuery())
model = QSqlRelationalTableModel(self, self.dbm.db)
model.setTable("film")
searchMode, searchFilter = self.searchFilmDlg.generateSearchFilter()
# QMessageBox.information(self, "info", searchFilter)
model.setFilter(searchFilter)
model.select()
rc = model.rowCount()
while (model.canFetchMore()):
model.fetchMore()
rc = model.rowCount()
query = QSqlQuery(self.dbm.db)
searchQuery = "select filmnummer, substr(filmnummer, 3, 8) as 'ohne_hersteller', flugdatum, anzahl_bilder, weise, art_ausarbeitung, militaernummer, militaernummer_alt from film where {0}".format(searchFilter)
query.exec_(searchQuery)
querySize = 0
while(query.next()):
querySize += 1
query.seek(-1)
# if model.rowCount():
if querySize > 0:
# open film selection list dialog
searchListDlg = APISFilmSelectionList(self.iface, model, self.dbm, self.imageRegistry, parent=self)
searchListDlg.uiFilmCountLbl.setText(str(rc))
searchListDlg.uiFilmCountDescriptionLbl.setText(u"Film gefunden" if model.rowCount() == 1 else u"Filme gefunden")
searchListDlg.uiFilmSearchModeLbl.setText(searchMode)
res = searchListDlg.loadFilmListBySqlQuery(query)
if res and searchListDlg.exec_():
#QMessageBox.warning(None, "FilmNumber", unicode(searchListDlg.filmNumberToLoad))
self.loadRecordByKeyAttribute("filmnummer", searchListDlg.filmNumberToLoad)
else:
QMessageBox.warning(self, u"Film Suche", u"Keine Ergebnisse mit den angegebenen Suchkriterien.")
self.openSearchFilmDialog()
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 APISFilm(QDialog, FORM_CLASS):
FIRST, PREV, NEXT, LAST = range(4)
OBLIQUE, VERTICAL = range(2)
def __init__(self, iface, dbm, imageRegistry, apisLayer, parent=None):
"""Constructor."""
super(APISFilm, self).__init__(parent)
self.iface = iface
self.dbm = dbm
self.imageRegistry = imageRegistry
self.apisLayer = apisLayer
self.parent = parent
self.setupUi(self)
# Initial window size/pos last saved. Use default values for first time
if GetWindowSize("film"):
self.resize(GetWindowSize("film"))
if GetWindowPos("film"):
self.move(GetWindowPos("film"))
self.printingOptionsDlg = None
self.settings = QSettings(QSettings().value("APIS/config_ini"), QSettings.IniFormat)
self.editMode = False
self.addMode = False
self.initalLoad = True
#self.uiInitalEntryQgsDate.setCalendarPopup(False)
#self.uiLastChangesQgsDate.setCalendarPopup(False)
# Signals/Slot Connections
self.rejected.connect(self.onReject)
self.uiOkBtn.clicked.connect(self.onAccept)
self.uiCancelBtn.clicked.connect(self.cancelEdit)
self.uiSaveBtn.clicked.connect(self.saveEdits)
self.uiFilmSelectionBtn.clicked.connect(self.openFilmSelectionDialog)
self.uiNewFilmBtn.clicked.connect(self.openNewFilmDialog)
self.uiSearchFilmBtn.clicked.connect(self.openSearchFilmDialog)
self.uiEditWeatherBtn.clicked.connect(self.openEditWeatherDialog)
self.uiExportPdfBtn.clicked.connect(self.exportDetailsPdf)
self.uiShowFlightPathBtn.clicked.connect(lambda: self.openFlightPathDialog([self.uiCurrentFilmNumberEdit.text()]))
# For LaLe Mode
if self.settings.value("APIS/disable_site_and_findspot", "0") != "1":
self.uiListSitesOfFilmBtn.setEnabled(True)
self.uiListSitesOfFilmBtn.clicked.connect(self.openSiteSelectionListDialog)
else:
self.uiListSitesOfFilmBtn.setEnabled(False)
self.uiListImagesOfFilmBtn.clicked.connect(self.openImageSelectionListDialog)
self.uiExtractGpsFromImagesBtn.clicked.connect(self.extractGpsFromImages)
self.uiWeatherCodeEdit.textChanged.connect(self.generateWeatherCode)
self.uiFilmModeCombo.currentIndexChanged.connect(self.onFilmModeChanged)
self.uiEditProjectTableBtn.clicked.connect(lambda: self.openSystemTableEditorDialog("projekt", self.uiProjectSelectionCombo))
self.uiEditCopyrightTableBtn.clicked.connect(lambda: self.openSystemTableEditorDialog("copyright", self.uiCopyrightCombo))
# self.uiEditProjectTableBtn.clicked.connect(lambda: VersionToCome())
# self.uiEditCopyrightTableBtn.clicked.connect(lambda: VersionToCome())
# init Project Btn
self.uiAddProjectBtn.clicked.connect(self.addProject)
self.uiRemoveProjectBtn.clicked.connect(self.removeProject)
# Setup Sub-Dialogs
self.filmSelectionDlg = APISFilmNumberSelection(self)
self.newFilmDlg = APISFilmNew(parent=self)
self.searchFilmDlg = APISFilmSearch(self.dbm, self) # (self.iface, self.dbm)
self.editWeatherDlg = APISWeather(self.iface, self.dbm, self)
self.flightPathDlg = APISFlightPath(self.iface, self.dbm, self.apisLayer, self)
self.siteSelectionListDlg = APISSiteSelectionList(self.iface, self.dbm, self.imageRegistry, self.apisLayer, self)
self.imageSelectionListDlg = APISImageSelectionList(self.iface, self.dbm, self.imageRegistry, self.apisLayer, parent=self)
self.systemTableEditorDlg = None
# Setup film model
self.model = QSqlRelationalTableModel(self, self.dbm.db)
self.model.setTable("film")
self.model.select()
while (self.model.canFetchMore()):
self.model.fetchMore()
self.setupMapper()
self.setupComboBox(self.uiProjectSelectionCombo, "projekt", 0, None)
self.setupComboBox(self.newFilmDlg.uiProducerCombo, "hersteller", 2, None)
self.setupNavigation()
self.mapper.toFirst()
self.initalLoad = False
def setupMapper(self):
self.mapper = QDataWidgetMapper(self)
self.mapper.currentIndexChanged.connect(self.onCurrentIndexChanged)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setItemDelegate(FilmDelegate())
self.mapper.setModel(self.model)
self.mandatoryEditors = [self.uiImageCountEdit, self.uiCameraCombo, self.uiFilmMakeCombo, self.uiFilmModeCombo]
self.disableEditorsIfOblique = [self.uiCameraNumberEdit, self.uiCalibratedFocalLengthEdit]
# LineEdits & PlainTextEdits
self.intValidator = QIntValidator()
self.doubleValidator = QDoubleValidator()
self.lineEditMaps = {
"filmnummer": {
"editor": self.uiCurrentFilmNumberEdit
},
"hersteller": {
"editor": self.uiProducerEdit
},
"anzahl_bilder": {
"editor": self.uiImageCountEdit,
"validator": self.intValidator
},
"militaernummer": {
"editor": self.uiMilitaryNumberEdit
},
"militaernummer_alt": {
"editor": self.uiOldMilitaryNumberEdit
},
"form1": {
"editor": self.uiFormat1Edit
},
"form2": {
"editor": self.uiFormat2Edit
},
"kalibrierungsnummer": {
"editor": self.uiCameraNumberEdit
},
"kammerkonstante": {
"editor": self.uiCalibratedFocalLengthEdit,
"validator": self.doubleValidator
},
"kassettennummer": {
"editor": self.uiCassetteEdit
},
"art_ausarbeitung": {
"editor": self.uiFilmMakeEdit
},
"fotograf": {
"editor": self.uiPhotographerEdit
},
"pilot": {
"editor": self.uiPilotEdit
},
"flugzeug": {
"editor": self.uiAirplaneEdit
},
"abflug_flughafen": {
"editor": self.uiDepartureAirportEdit
},
"ankunft_flughafen": {
"editor": self.uiArrivalAirportEdit
},
"flugzeit": {
"editor": self.uiFlightDurationEdit
},
"wetter": {
"editor": self.uiWeatherCodeEdit
},
"kommentar": {
"editor": self.uiCommentsPTxt
}
}
for key, item in self.lineEditMaps.items():
self.mapper.addMapping(item["editor"], self.model.fieldIndex(key))
if "validator" in item:
item["editor"].setValidator(item["validator"])
#item["editor"].textChanged.connect(partial(self.onLineEditChanged, item["editor"]))
item["editor"].textChanged.connect(self.onLineEditChanged)
#Text
#self.mapper.addMapping(self.uiCommentsPTxt, self.model.fieldIndex("kommentar"))
# Date and Times
self.mapper.addMapping(self.uiFlightDate, self.model.fieldIndex("flugdatum"))
#self.mapper.addMapping(self.uiFlightQgsDate, self.model.fieldIndex("flugdatum"))
self.mapper.addMapping(self.uiInitalEntryDate, self.model.fieldIndex("datum_ersteintrag"))
#self.mapper.addMapping(self.uiInitalEntryQgsDate, self.model.fieldIndex("datum_ersteintrag"))
self.mapper.addMapping(self.uiLastChangesDate, self.model.fieldIndex("datum_aenderung"))
#self.mapper.addMapping(self.uiLastChangesQgsDate, self.model.fieldIndex("datum_aenderung"))
self.mapper.addMapping(self.uiDepartureTime, self.model.fieldIndex("abflug_zeit"))
self.mapper.addMapping(self.uiArrivalTime, self.model.fieldIndex("ankunft_zeit"))
self.uiDepartureTime.timeChanged.connect(self.onFlightTimeChanged)
self.uiArrivalTime.timeChanged.connect(self.onFlightTimeChanged)
# ComboBox without Model
self.mapper.addMapping(self.uiFilmModeCombo, self.model.fieldIndex("weise"))
self.uiFilmModeCombo.editTextChanged.connect(self.onLineEditChanged)
completer = QCompleter(self.uiFilmModeCombo.model())
self.uiFilmModeCombo.setCompleter(completer)
self.uiFilmModeCombo.lineEdit().setValidator(InListValidator([self.uiFilmModeCombo.itemText(i) for i in range(self.uiFilmModeCombo.count())], self.uiFilmModeCombo.lineEdit(), None, self))
# ComboBox with Model
self.comboBoxMaps = {
"archiv": {
"editor": self.uiArchiveCombo,
"table": "hersteller",
"modelcolumn": 2,
"depend": None
},
"kamera": {
"editor": self.uiCameraCombo,
"table": "kamera",
"modelcolumn": 0,
"depend": [{"form1": self.uiFormat1Edit}, {"form2": self.uiFormat2Edit}]
},
"filmfabrikat": {
"editor": self.uiFilmMakeCombo,
"table": "film_fabrikat",
"modelcolumn": 0,
"depend": [{"art": self.uiFilmMakeEdit}]
},
"target": {
"editor": self.uiTargetCombo,
"table": "target",
"modelcolumn": 0,
"depend": None
},
"copyright": {
"editor": self.uiCopyrightCombo,
"table": "copyright",
"modelcolumn": 0,
"depend": None
}
}
for key, item in self.comboBoxMaps.items():
self.mapper.addMapping(item["editor"], self.model.fieldIndex(key))
self.setupComboBox(item["editor"], item["table"], item["modelcolumn"], item["depend"])
item["editor"].editTextChanged.connect(self.onLineEditChanged)
self.mapper.addMapping(self.uiProjectList, self.model.fieldIndex("projekt"))
def fixComboBoxDropDownListSizeAdjustemnt(self, cb):
scroll = 0 if cb.count() <= cb.maxVisibleItems() else QApplication.style().pixelMetric(QStyle.PM_ScrollBarExtent)
iconWidth = cb.iconSize().width()
max = 0
for i in range(cb.count()):
width = cb.view().fontMetrics().width(cb.itemText(i))
if max < width:
max = width
QMessageBox.information(self, "info", "scroll: {0}, max: {1}, icon: {2}".format(scroll, max, iconWidth))
#cb.view().setMinimumWidth(scroll + max)
def setupComboBox(self, editor, table, modelColumn, depend):
model = QSqlRelationalTableModel(self, self.dbm.db)
model.setTable(table)
model.removeColumn(0)
model.select()
tv = QTableView()
editor.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
editor.setModel(model)
editor.setModelColumn(modelColumn)
editor.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
#tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setStretchLastSection(True)
#tv.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
tv.resizeColumnsToContents()
scroll = 0 if editor.count() <= editor.maxVisibleItems() else QApplication.style().pixelMetric(QStyle.PM_ScrollBarExtent)
tv.setMinimumWidth(tv.horizontalHeader().length() + scroll)
#self.fixComboBoxDropDownListSizeAdjustemnt(editor)
#editor.resize(tv.horizontalHeader().sizeHint())
completer = QCompleter(editor.model())
editor.setCompleter(completer)
#editor.setAutoCompletion(True)
editor.lineEdit().setValidator(InListValidator([editor.itemText(i) for i in range(editor.count())], editor.lineEdit(), depend, self))
if depend:
editor.currentIndexChanged.connect(partial(self.updateDepends, editor, depend))
def updateDepends(self, editor, depend):
for dep in depend:
for key, value in dep.items():
idx = editor.model().createIndex(editor.currentIndex(), editor.model().fieldIndex(key))
value.setText(str(editor.model().data(idx)))
#QMessageBox.warning(None, "Test", str(idx))
def setupNavigation(self):
self.uiFirstFilmBtn.clicked.connect(partial(self.loadRecordByNavigation, APISFilm.FIRST))
self.uiPreviousFilmBtn.clicked.connect(partial(self.loadRecordByNavigation, APISFilm.PREV))
self.uiNextFilmBtn.clicked.connect(partial(self.loadRecordByNavigation, APISFilm.NEXT))
self.uiLastFilmBtn.clicked.connect(partial(self.loadRecordByNavigation, APISFilm.LAST))
self.uiTotalFilmCountLbl.setText(str(self.model.rowCount()))
self.intRecordValidator = QIntValidator(1, self.model.rowCount())
self.uiCurrentFilmCountEdit.setValidator(self.intRecordValidator)
self.uiCurrentFilmCountEdit.setText(str(self.mapper.currentIndex() + 1))
self.uiCurrentFilmCountEdit.editingFinished.connect(lambda: self.loadRecordById(int(self.uiCurrentFilmCountEdit.text()) - 1))
# QMessageBox.warning(None, "Test", str(self.mapper.itemDelegate()))
def enableItemsInLayout(self, layout, enable):
for i in range(layout.count()):
if layout.itemAt(i).widget():
layout.itemAt(i).widget().setEnabled(enable)
def loadRecordByNavigation(self, mode):
#self.mapper.submit()
#self.submitChanges()
self.initalLoad = True
if mode == APISFilm.FIRST:
self.mapper.toFirst()
elif mode == APISFilm.PREV:
self.mapper.toPrevious()
elif mode == APISFilm.NEXT:
self.mapper.toNext()
elif mode == APISFilm.LAST:
self.mapper.toLast()
self.initalLoad = False
def loadRecordById(self, id):
#self.submitChanges
self.initalLoad = True
self.mapper.setCurrentIndex(id)
self.initalLoad = False
def loadRecordByKeyAttribute(self, attribute, value):
#self.model.setFilter(attribute + " = '" + value + "'")
#self.model.select()
# self.mapper.toFirst()
query = QSqlQuery(self.dbm.db)
#qryStr = "select {0} from film where {0} = '{1}' limit 1".format(attribute, value)
#qryStr = "SELECT rowid FROM film WHERE {0} = '{1}' limit 1".format(attribute, value)
qryStr = "SELECT" \
" (SELECT COUNT(*)" \
" FROM film AS t2" \
" WHERE t2.rowid < t1.rowid" \
" ) + (" \
" SELECT COUNT(*)" \
" FROM film AS t3" \
" WHERE t3.rowid = t1.rowid AND t3.rowid < t1.rowid" \
" ) AS rowNum" \
" FROM film AS t1" \
" WHERE {0} = '{1}'" \
" ORDER BY t1.rowid ASC".format(attribute, value)
query.exec_(qryStr)
#QMessageBox.warning(None, "Test", str(query.size()) + ',' + str(query.numRowsAffected()))
query.first()
fn = query.value(0)
if fn is not None:
self.loadRecordById(fn)
return True
else:
# Film does not exist
QMessageBox.warning(None, "Film Nummer", str("Der Film mit der Nummer {0} existiert nicht!".format(value)))
return False
#self.model.setFilter('')
#self.model.select()
#while (self.model.canFetchMore()):
#self.model.fetchMore()
def submitChanges(self):
self.mapper.submit()
def onCurrentIndexChanged(self):
self.uiCurrentFilmCountEdit.setText(str(self.mapper.currentIndex() + 1))
self.onFilmModeChanged()
def onFlightTimeChanged(self):
dTime = self.uiDepartureTime.time()
aTime = self.uiArrivalTime.time()
flightDuration = dTime.secsTo(aTime)
self.uiFlightDurationEdit.setText(str(flightDuration / 60))
def disableIfOblique(self, isOblique):
for editor in self.disableEditorsIfOblique:
editor.setDisabled(isOblique)
def onFilmModeChanged(self):
if self.uiFilmModeCombo.currentText() == u'schräg':
self.disableIfOblique(True)
else:
self.disableIfOblique(False)
def onLineEditChanged(self):
sender = self.sender()
if not self.editMode and not self.initalLoad:
self.startEditMode()
if not self.initalLoad:
sender.setStyleSheet("{0} {{background-color: rgb(153, 204, 255);}}".format(sender.metaObject().className()))
self.editorsEdited.append(sender)
def onComboBoxChanged(self, editor):
pass
def addProject(self):
editor = self.uiProjectList
value = self.uiProjectSelectionCombo.currentText()
notInList = True
for row in range(editor.count()):
if value == editor.item(row).data(0):
notInList = False
break
if notInList:
editor.addItem(value)
editor.sortItems()
if not self.editMode and not self.initalLoad:
self.startEditMode()
if not self.initalLoad:
editor.setStyleSheet("{0} {{background-color: rgb(153, 204, 255);}}".format(editor.metaObject().className()))
self.editorsEdited.append(editor)
def removeProject(self):
editor = self.uiProjectList
editor.takeItem(self.uiProjectList.currentRow())
if not self.editMode and not self.initalLoad:
self.startEditMode()
if not self.initalLoad:
editor.setStyleSheet("{0} {{background-color: rgb(153, 204, 255);}}".format(editor.metaObject().className()))
self.editorsEdited.append(editor)
def onAccept(self):
'''
Check DB
Save options when pressing OK button
Update Plugin Status
'''
self.accept()
def onReject(self):
'''
Run some actions when
the user closes the dialog
'''
if self.editMode:
res = self.cancelEdit()
if res:
self.close()
else:
self.show()
else:
self.close()
def closeEvent(self, e):
# Write window size and position to QSettings
if self.editMode:
self.onReject()
else:
SetWindowSizeAndPos("film", self.size(), self.pos())
e.accept()
def extractGpsFromImages(self):
key = self.uiCurrentFilmNumberEdit.text()
e2p = Exif2Points(self.iface, key)
layer = e2p.run()
if layer:
self.apisLayer.requestShapeFile(layer, groupName="Flugwege", addToCanvas=True)
def exportDetailsPdf(self):
if self.printingOptionsDlg is None:
self.printingOptionsDlg = APISPrintingOptions(self)
self.printingOptionsDlg.setWindowTitle("Druck Optionen: Film")
self.printingOptionsDlg.configure(False, False, visPersonalDataChk=True)
self.printingOptionsDlg.show()
if self.printingOptionsDlg.exec_():
printPersonalData = self.printingOptionsDlg.printPersonalData()
APISPrinterQueue([{'type': APISTemplatePrinter.FILM, 'idList': [self.uiCurrentFilmNumberEdit.text()], 'options': {'personalData': printPersonalData}}],
OutputMode.MergeNone,
openFile=self.printingOptionsDlg.uiOpenFilesChk.isChecked(),
openFolder=self.printingOptionsDlg.uiOpenFolderChk.isChecked(),
dbm=self.dbm,
parent=self)
def openSearchFilmDialog(self):
"""Run method that performs all the real work"""
# show the dialog
self.searchFilmDlg.show()
#self.filmSelectionDlg.uiFilmNumberEdit.setFocus()
# Run the dialog event loop and See if OK was pressed
if self.searchFilmDlg.exec_():
# QMessageBox.warning(None, "FilmNumber", self.searchFilmDlg.generateSearchQuery())
model = QSqlRelationalTableModel(self, self.dbm.db)
model.setTable("film")
searchMode, searchFilter = self.searchFilmDlg.generateSearchFilter()
# QMessageBox.information(self, "info", searchFilter)
model.setFilter(searchFilter)
model.select()
rc = model.rowCount()
while (model.canFetchMore()):
model.fetchMore()
rc = model.rowCount()
query = QSqlQuery(self.dbm.db)
searchQuery = "select filmnummer, substr(filmnummer, 3, 8) as 'ohne_hersteller', flugdatum, anzahl_bilder, weise, art_ausarbeitung, militaernummer, militaernummer_alt from film where {0}".format(searchFilter)
query.exec_(searchQuery)
querySize = 0
while(query.next()):
querySize += 1
query.seek(-1)
# if model.rowCount():
if querySize > 0:
# open film selection list dialog
searchListDlg = APISFilmSelectionList(self.iface, model, self.dbm, self.imageRegistry, parent=self)
searchListDlg.uiFilmCountLbl.setText(str(rc))
searchListDlg.uiFilmCountDescriptionLbl.setText(u"Film gefunden" if model.rowCount() == 1 else u"Filme gefunden")
searchListDlg.uiFilmSearchModeLbl.setText(searchMode)
res = searchListDlg.loadFilmListBySqlQuery(query)
if res and searchListDlg.exec_():
#QMessageBox.warning(None, "FilmNumber", unicode(searchListDlg.filmNumberToLoad))
self.loadRecordByKeyAttribute("filmnummer", searchListDlg.filmNumberToLoad)
else:
QMessageBox.warning(self, u"Film Suche", u"Keine Ergebnisse mit den angegebenen Suchkriterien.")
self.openSearchFilmDialog()
#QMessageBox.warning(None, "FilmNumber", u"{0}, rows: {1}".format(self.searchFilmDlg.generateSearchQuery(), model.rowCount()))
# Get Search String/Query
#if not self.loadRecordByKeyAttribute("filmnummer", self.filmSelectionDlg.filmNumber()):
#self.openFilmSelectionDialog()
def openFilmSelectionDialog(self):
"""Run method that performs all the real work"""
self.filmSelectionDlg.show()
self.filmSelectionDlg.uiFilmNumberEdit.setFocus()
if self.filmSelectionDlg.exec_():
if not self.loadRecordByKeyAttribute("filmnummer", self.filmSelectionDlg.filmNumber()):
self.openFilmSelectionDialog()
def openNewFilmDialog(self):
"""Run method that performs all the real work"""
self.newFilmDlg.show()
if self.newFilmDlg.exec_():
self.addNewFilm(self.newFilmDlg.flightDate(), self.newFilmDlg.useLastEntry(), self.newFilmDlg.producer(), self.newFilmDlg.producerCode())
def openEditWeatherDialog(self):
self.editWeatherDlg.setWeatherCode(self.uiWeatherCodeEdit.text())
self.editWeatherDlg.show()
if self.editWeatherDlg.exec_():
self.uiWeatherCodeEdit.setText(self.editWeatherDlg.weatherCode())
#self.uiWeatherPTxt.setPlainText(self.editWeatherDlg.weatherDescription())
def generateWeatherCode(self):
weatherDescription = self._generateWeatherCode(self.uiWeatherCodeEdit.text())
self.uiWeatherPTxt.setPlainText(weatherDescription)
def _generateWeatherCode(self, weatherCode):
categories = ["Low Cloud Amount", "Visibility Kilometres", "Low Cloud Height", "Weather", "Remarks Mission", "Remarks Weather"]
query = QSqlQuery(self.dbm.db)
pos = 0
help = 0
weatherDescription = ""
for c in weatherCode:
qryStr = "select description from wetter where category = '{0}' and code = '{1}' limit 1".format(categories[pos - help], c)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if pos <= 5:
weatherDescription += categories[pos] + ': ' + fn
if pos < 5:
weatherDescription += '\n'
else:
weatherDescription += '; ' + fn
if pos >= 5:
help += 1
pos += 1
return weatherDescription
def openSystemTableEditorDialog(self, table, updateEditor):
if self.dbm:
self.systemTableEditorDlg = APISAdvancedInputDialog(self.dbm, table, False, parent=self)
if self.systemTableEditorDlg.tableExists:
if self.systemTableEditorDlg.exec_():
# See if OK was pressed
# rec = self.systemTableEditorDlg.getRecord()
# Update updateEditor
# self.setupComboBox(self.uiProjectSelectionCombo, "projekt", 0, None)
self.updateComboBox(updateEditor)
else:
QMessageBox.warning(self, "Tabelle nicht vorhanden", "Die Tabelle {0} ist in der APIS Datenbank nicht vorhanden".format(table))
else:
QMessageBox.warning(self, "Warning Database", "Die APIS Datenbank konnte nicht gefunden werden.")
def updateComboBox(self, updateEditor):
updateEditor.model().select()
tv = updateEditor.view()
tv.resizeRowsToContents()
tv.resizeColumnsToContents()
scroll = 0 if updateEditor.count() <= updateEditor.maxVisibleItems() else QApplication.style().pixelMetric(
QStyle.PM_ScrollBarExtent)
tv.setMinimumWidth(tv.horizontalHeader().length() + scroll)
updateEditor.setCurrentIndex(updateEditor.count() - 1)
if updateEditor.validator():
updateEditor.lineEdit().setValidator(
InListValidator([updateEditor.itemText(i) for i in range(updateEditor.count())],
updateEditor.lineEdit(), None, self))
def openFlightPathDialog(self, filmList, toClose=None):
self.flightPathDlg.viewFilms(filmList) # DEBUG
self.flightPathDlg.show()
if self.flightPathDlg.exec_():
#TODO load Data in TOC, Close Windows
if toClose:
toClose.close()
self.close()
def openSiteSelectionListDialog(self):
if self.uiFilmModeCombo.currentIndex() == APISFilm.VERTICAL:
fromTable = "luftbild_senk_fp"
elif self.uiFilmModeCombo.currentIndex() == APISFilm.OBLIQUE:
fromTable = "luftbild_schraeg_fp"
query = QSqlQuery(self.dbm.db)
query.prepare("SELECT fundortnummer, flurname, katastralgemeinde, fundgewinnung, sicherheit FROM fundort WHERE fundortnummer IN (SELECT DISTINCT fo.fundortnummer FROM fundort fo, {0} WHERE fo.geometry IS NOT NULL AND {0}.geometry IS NOT NULL AND {0}.filmnummer = '{1}' AND Intersects({0}.geometry, fo.geometry) AND fo.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'fundort' AND search_frame = {0}.geometry)) ORDER BY katastralgemeindenummer, land, fundortnummer_nn".format(fromTable, self.uiCurrentFilmNumberEdit.text()))
query.exec_()
info = u"gefunden, die vom Film {0} abgedeckt/geschnitten werden.".format(self.uiCurrentFilmNumberEdit.text())
res = self.siteSelectionListDlg.loadSiteListBySpatialQuery(query, info)
if res:
self.siteSelectionListDlg.show()
if self.siteSelectionListDlg.exec_():
pass
def openImageSelectionListDialog(self):
if self.uiFilmModeCombo.currentIndex() == APISFilm.VERTICAL:
fromTable = 'luftbild_senk_cp'
spatialIndicator = 'massstab'
elif self.uiFilmModeCombo.currentIndex() == APISFilm.OBLIQUE:
fromTable = 'luftbild_schraeg_cp'
spatialIndicator = 'radius'
query = QSqlQuery(self.dbm.db)
query.prepare("SELECT cp.bildnummer AS bildnummer, cp.filmnummer AS filmnummer, cp.{2} AS mst_radius, f.weise AS weise, f.art_ausarbeitung AS art FROM {0} AS cp, film AS f WHERE cp.filmnummer = '{1}' AND f.filmnummer = '{1}'".format(fromTable, self.uiCurrentFilmNumberEdit.text(), spatialIndicator))
query.exec_()
res = self.imageSelectionListDlg.loadImageListBySqlQuery(query)
if res:
self.imageSelectionListDlg.show()
if self.imageSelectionListDlg.exec_():
pass
def addNewFilm(self, flightDate, useLastEntry, producer, producerCode):
self.initalLoad = True
self.addMode = True
self.startEditMode()
row = self.model.rowCount()
self.mapper.submit()
while (self.model.canFetchMore()):
self.model.fetchMore()
self.model.insertRow(row)
if useLastEntry:
#copy last row
for c in range(1, self.model.columnCount()):
value = self.model.data(self.model.createIndex(row - 1, c))
self.model.setData(self.model.createIndex(row, c), value)
editor = self.mapper.mappedWidgetAt(c)
if editor and not (value == 'NULL' or value == ''):
cName = editor.metaObject().className()
if (cName == "QLineEdit" or cName == "QDateEdit") and editor.isReadOnly():
pass
else:
editor.setStyleSheet("{0} {{background-color: rgb(153, 204, 255);}}".format(editor.metaObject().className()))
self.editorsEdited.append(editor)
self.mapper.setCurrentIndex(row)
self.uiTotalFilmCountLbl.setText(str(self.model.rowCount()))
self.uiFlightDate.setDate(flightDate)
#self.uiFlightQgsDate.setDate(flightDate)
self.uiProducerEdit.setText(producer)
self.uiArchiveCombo.lineEdit().setText(producer)
if not useLastEntry:
self.uiWeatherCodeEdit.setText("9990X")
#now = QDateTime.currentDateTime()
now = QDate.currentDate()
self.uiInitalEntryDate.setDate(now)
#self.uiInitalEntryQgsDate.setDateTime(now)
self.uiLastChangesDate.setDate(now)
#self.uiLastChangesQgsDate.setDateTime(now)
self.uiFilmModeCombo.setEnabled(True)
#Filmnummer
hh = producerCode
yyyy = flightDate.toString("yyyy")
mm = flightDate.toString("MM")
query = QSqlQuery(self.dbm.db)
qryStr = "select max(filmnummer_nn) from film where filmnummer_hh_jjjj_mm = '{0}{1}{2}' limit 1".format(hh, yyyy, mm)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if isinstance(fn, int):
nn = str(fn + 1).zfill(2)
else:
nn = "01"
self.uiCurrentFilmNumberEdit.setText("{0}{1}{2}{3}".format(hh, yyyy, mm, nn))
self.initalLoad = False
def removeNewFilm(self):
self.initalLoad = True
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
while (self.model.canFetchMore()):
self.model.fetchMore()
self.uiTotalFilmCountLbl.setText(str(self.model.rowCount()))
self.mapper.toLast()
self.initalLoad = False
def saveEdits(self):
# Check Mandatory fields
flag = False
for mEditor in self.mandatoryEditors:
cName = mEditor.metaObject().className()
if cName == 'QLineEdit':
value = mEditor.text()
elif cName == 'QComboBox':
value = mEditor.lineEdit().text()
if value.strip() == "":
flag = True
mEditor.setStyleSheet("{0} {{background-color: rgb(240, 160, 160);}}".format(cName))
if mEditor not in self.editorsEdited:
self.editorsEdited.append(mEditor)
else:
if mEditor in self.editorsEdited:
mEditor.setStyleSheet("{0} {{background-color: rgb(153, 204, 255);}}".format(cName))
#else:
#mEditor.setStyleSheet("")
if flag:
QMessageBox.warning(self, self.tr(u"Benötigte Felder Ausfüllen"), self.tr(u"Füllen Sie bitte alle Felder aus, die mit * gekennzeichnet sind."))
return False
#saveToModel
currIdx = self.mapper.currentIndex()
#now = QDateTime.currentDateTime()
now = QDate.currentDate()
self.uiLastChangesDate.setDate(now)
#self.uiLastChangesQgsDate.setDateTime(now)
res = self.mapper.submit()
if not res:
sqlError = self.mapper.model().lastError()
QMessageBox.information(self, "Submit", u"Error: {0}, {1}".format(res, sqlError.text()))
while (self.model.canFetchMore()):
self.model.fetchMore()
self.mapper.setCurrentIndex(currIdx)
self.endEditMode()
return True
def cancelEdit(self):
if self.editMode:
result = QMessageBox.question(self,
self.tr(u"Änderungen wurden vorgenommen!"),
self.tr(u"Möchten Sie die Änerungen speichern?"),
QMessageBox.Yes | QMessageBox.No,
QMessageBox.Yes)
#save or not save
if result == QMessageBox.Yes:
res = self.saveEdits()
if res:
return True
else:
return False
elif result == QMessageBox.No:
if self.addMode:
self.removeNewFilm()
self.mapper.setCurrentIndex(self.mapper.currentIndex())
self.endEditMode()
return True
def startEditMode(self):
self.editMode = True
self.enableItemsInLayout(self.uiTopHorizontalLayout, False)
self.enableItemsInLayout(self.uiBottomHorizontalLayout, False)
self.uiOkBtn.setEnabled(False)
self.uiSaveBtn.setEnabled(True)
self.uiCancelBtn.setEnabled(True)
self.editorsEdited = []
def endEditMode(self):
self.editMode = False
self.addMode = False
self.enableItemsInLayout(self.uiTopHorizontalLayout, True)
self.enableItemsInLayout(self.uiBottomHorizontalLayout, True)
self.uiOkBtn.setEnabled(True)
self.uiSaveBtn.setEnabled(False)
self.uiCancelBtn.setEnabled(False)
for editor in self.editorsEdited:
cName = editor.metaObject().className()
if (cName == "QLineEdit" or cName == "QDateEdit") and editor.isReadOnly():
editor.setStyleSheet("{0} {{background-color: rgb(218, 218, 218);}}".format(cName))
else:
editor.setStyleSheet("")
self.editorsEdited = []
self.uiFilmModeCombo.setEnabled(False)
def showEvent(self, evnt):
# QMessageBox.information(self, "info", "db requires update: {0}".format(self.dbm.dbRequiresUpdate))
if self.dbm.dbRequiresUpdate:
self.initalLoad = True
for editor in [self.uiArchiveCombo, self.uiCameraCombo, self.uiFilmMakeCombo, self.uiCopyrightCombo, self.uiProjectSelectionCombo, self.newFilmDlg.uiProducerCombo]:
self.updateComboBox(editor)
currIdx = self.mapper.currentIndex()
self.model.select()
while (self.model.canFetchMore()):
self.model.fetchMore()
self.mapper.setCurrentIndex(currIdx)
self.dbm.dbRequiresUpdate = False
self.initalLoad = False
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 MyWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
# Собираем окно
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'database/database.sqlite3'
self.ui.path_db.setText(self.db_path)
# Собираем кнопки
self.save_db = QAction(QIcon('icon/save.png'), 'Save', self)
self.open_db_file = QAction(QIcon('icon/open-db.png'), 'Open', self)
self.add_row = QAction(QIcon('icon/add.png'), 'Add row', self)
self.del_row = QAction(QIcon('icon/del.png'), ' Del row', self)
self.ui.toolBar.addAction(self.save_db)
self.ui.toolBar.addAction(self.open_db_file)
self.ui.toolBar.addAction(self.add_row)
self.ui.toolBar.addAction(self.del_row)
self.db = None
self.table_model = None
# Подключаемся к БД
self.open_db()
# действуем по триггерам
self.add_row.triggered.connect(self.add_row_action)
self.del_row.triggered.connect(self.del_row_action)
self.save_db.triggered.connect(self.save_change_db)
self.open_db_file.triggered.connect(self.open_db_file_action)
self.ui.comboBox.currentIndexChanged.connect(self.show_table)
def open_db(self):
# Подключение к БД
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
self.show_table()
def get_tables_name(self):
self.ui.comboBox.clear()
for table_name in self.db.tables():
self.ui.comboBox.addItem(table_name)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.comboBox.currentText()
if table == 'goods':
self.create_goods_table_model()
elif table == 'employees':
self.create_employees_table_model()
else:
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit'))
self.table_model.setRelation(3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(2, QSqlRelation('positions', 'position_id', 'position'))
self.table_model.select()
def add_row_action(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def del_row_action(self):
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def open_db_file_action(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.path_db.setText(self.db_path)
self.db.close()
self.open_db()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
class PhoneLogDlg(QDialog):
FIRST, PREV, NEXT, LAST = range(4)
def __init__(self, parent=None):
super(PhoneLogDlg, self).__init__(parent)
callerLabel = QLabel("&Caller:")
self.callerEdit = QLineEdit()
callerLabel.setBuddy(self.callerEdit)
today = QDate.currentDate()
startLabel = QLabel("&Start:")
self.startDateTime = QDateTimeEdit()
startLabel.setBuddy(self.startDateTime)
self.startDateTime.setDateRange(today, today)
self.startDateTime.setDisplayFormat(DATETIME_FORMAT)
endLabel = QLabel("&End:")
self.endDateTime = QDateTimeEdit()
endLabel.setBuddy(self.endDateTime)
self.endDateTime.setDateRange(today, today)
self.endDateTime.setDisplayFormat(DATETIME_FORMAT)
topicLabel = QLabel("&Topic:")
topicEdit = QLineEdit()
topicLabel.setBuddy(topicEdit)
outcomeLabel = QLabel("&Outcome:")
self.outcomeComboBox = QComboBox()
outcomeLabel.setBuddy(self.outcomeComboBox)
firstButton = QPushButton()
firstButton.setIcon(QIcon(":/first.png"))
prevButton = QPushButton()
prevButton.setIcon(QIcon(":/prev.png"))
nextButton = QPushButton()
nextButton.setIcon(QIcon(":/next.png"))
lastButton = QPushButton()
lastButton.setIcon(QIcon(":/last.png"))
addButton = QPushButton("&Add")
addButton.setIcon(QIcon(":/add.png"))
deleteButton = QPushButton("&Delete")
deleteButton.setIcon(QIcon(":/delete.png"))
quitButton = QPushButton("&Quit")
quitButton.setIcon(QIcon(":/quit.png"))
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
fieldLayout = QGridLayout()
fieldLayout.addWidget(callerLabel, 0, 0)
fieldLayout.addWidget(self.callerEdit, 0, 1, 1, 3)
fieldLayout.addWidget(startLabel, 1, 0)
fieldLayout.addWidget(self.startDateTime, 1, 1)
fieldLayout.addWidget(endLabel, 1, 2)
fieldLayout.addWidget(self.endDateTime, 1, 3)
fieldLayout.addWidget(topicLabel, 2, 0)
fieldLayout.addWidget(topicEdit, 2, 1, 1, 3)
fieldLayout.addWidget(outcomeLabel, 3, 0)
fieldLayout.addWidget(self.outcomeComboBox, 3, 1, 1, 3)
navigationLayout = QHBoxLayout()
navigationLayout.addWidget(firstButton)
navigationLayout.addWidget(prevButton)
navigationLayout.addWidget(nextButton)
navigationLayout.addWidget(lastButton)
fieldLayout.addLayout(navigationLayout, 4, 0, 1, 2)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(fieldLayout)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.model = QSqlRelationalTableModel(self)
self.model.setTable("calls")
self.model.setRelation(OUTCOMEID, QSqlRelation("outcomes", "id",
"name"))
self.model.setSort(STARTTIME, Qt.AscendingOrder)
self.model.select()
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setModel(self.model)
self.mapper.setItemDelegate(QSqlRelationalDelegate(self))
self.mapper.addMapping(self.callerEdit, CALLER)
self.mapper.addMapping(self.startDateTime, STARTTIME)
self.mapper.addMapping(self.endDateTime, ENDTIME)
self.mapper.addMapping(topicEdit, TOPIC)
relationModel = self.model.relationModel(OUTCOMEID)
self.outcomeComboBox.setModel(relationModel)
self.outcomeComboBox.setModelColumn(relationModel.fieldIndex("name"))
self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID)
self.mapper.toFirst()
firstButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.FIRST))
prevButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.PREV))
nextButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.NEXT))
lastButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.LAST))
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
quitButton.clicked.connect(self.done)
self.setWindowTitle("Phone Log")
def done(self, result=None):
self.mapper.submit()
QDialog.done(self, True)
def addRecord(self):
row = self.model.rowCount()
self.mapper.submit()
self.model.insertRow(row)
self.mapper.setCurrentIndex(row)
now = QDateTime.currentDateTime()
self.startDateTime.setDateTime(now)
self.endDateTime.setDateTime(now)
self.outcomeComboBox.setCurrentIndex(
self.outcomeComboBox.findText("Unresolved"))
self.callerEdit.setFocus()
def deleteRecord(self):
caller = self.callerEdit.text()
starttime = self.startDateTime.dateTime().toString(DATETIME_FORMAT)
if (QMessageBox.question(
self, "Delete",
"Delete call made by<br>{0} on {1}?".format(caller, starttime),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
row = self.mapper.currentIndex()
self.model.removeRow(row)
self.model.submitAll()
self.model.select()
if row + 1 >= self.model.rowCount():
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
def saveRecord(self, where):
row = self.mapper.currentIndex()
self.mapper.submit()
if where == PhoneLogDlg.FIRST:
row = 0
elif where == PhoneLogDlg.PREV:
row = 0 if row <= 1 else row - 1
elif where == PhoneLogDlg.NEXT:
row += 1
if row >= self.model.rowCount():
row = self.model.rowCount() - 1
elif where == PhoneLogDlg.LAST:
row = self.model.rowCount() - 1
self.mapper.setCurrentIndex(row)
class 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)
class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(700, 600)
MainWindow.setMinimumSize(QtCore.QSize(700, 600))
MainWindow.setMaximumSize(QtCore.QSize(700, 600))
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.btn_call = QtWidgets.QPushButton(self.centralwidget)
self.btn_call.setGeometry(QtCore.QRect(558, 480, 131, 71))
self.btn_call.setObjectName("btn_call")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_call.setFont(font)
self.btn_add = QtWidgets.QPushButton(self.centralwidget)
self.btn_add.setGeometry(QtCore.QRect(10, 480, 89, 71))
self.btn_add.setStyleSheet("")
self.btn_add.setObjectName("btn_add")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_add.setFont(font)
self.btn_delete = QtWidgets.QPushButton(self.centralwidget)
self.btn_delete.setGeometry(QtCore.QRect(120, 480, 89, 71))
self.btn_delete.setObjectName("btn_delete")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_delete.setFont(font)
self.btn_search = QtWidgets.QPushButton(self.centralwidget)
self.btn_search.setGeometry(QtCore.QRect(230, 480, 89, 71))
self.btn_search.setObjectName("btn_search")
font = QtGui.QFont()
font.setPointSize(11)
self.btn_search.setFont(font)
self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
self.lineEdit.setGeometry(QtCore.QRect(10, 434, 311, 31))
font = QtGui.QFont()
font.setPointSize(14)
self.lineEdit.setFont(font)
self.lineEdit.setObjectName("lineEdit")
self.comboBox = QtWidgets.QComboBox(self.centralwidget)
self.comboBox.setGeometry(QtCore.QRect(335, 434, 121, 31))
self.comboBox.setObjectName("comboBox")
font = QtGui.QFont()
font.setPointSize(11)
self.comboBox.setFont(font)
self.tableView = QtWidgets.QTableView(self.centralwidget)
self.tableView.setGeometry(QtCore.QRect(10, 10, 681, 411))
self.tableView.setObjectName("tableView")
font = QtGui.QFont()
font.setPointSize(11)
self.tableView.setFont(font)
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 700, 22))
self.menubar.setObjectName("menubar")
self.menuSetting = QtWidgets.QMenu(self.menubar)
self.menuSetting.setObjectName("menuSetting")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.action_Invite_window = QtWidgets.QAction(MainWindow)
self.action_Invite_window.setObjectName("action_Invite_window")
# self.actionPlace_windows = QtWidgets.QAction(MainWindow)
# self.actionPlace_windows.setObjectName("actionPlace_windows")
self.actionini_py = QtWidgets.QAction(MainWindow)
self.actionini_py.setObjectName("actionini_py")
self.actionDestination = QtWidgets.QAction(MainWindow)
self.actionDestination.setObjectName("actionDestination")
self.actionDrop_tablel = QtWidgets.QAction(MainWindow)
self.actionDrop_tablel.setObjectName("actionDrop_tablel")
self.menuSetting.addAction(self.action_Invite_window)
# self.menuSetting.addAction(self.actionPlace_windows)
self.menuSetting.addAction(self.actionDestination)
self.menuSetting.addAction(self.actionDrop_tablel)
self.menuSetting.addSeparator()
self.menuSetting.addAction(self.actionini_py)
self.menubar.addAction(self.menuSetting.menuAction())
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
# เรียกฟังชั่นเพื่อโหลด model ลง table
self.loadData()
self.btn_add.clicked.connect(self.insertData)
self.btn_delete.clicked.connect(self.deleteData)
self.btn_call.clicked.connect(self.speak)
self.btn_search.clicked.connect(self.searchData)
self.actionDestination.triggered.connect(self.show_desDialog)
def show_desDialog(self):
self.desDialog.show()
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "Simple Queue"))
self.btn_call.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อเรียกคิวที่เลือกจากตารางคิว</p></body></html>"
))
self.btn_call.setText(_translate("MainWindow", "เรียกคิว"))
self.btn_add.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อเปิดเมนูการเพิ่มคิวด้วยตัวเอง</p></body></html>"
))
self.btn_add.setText(_translate("MainWindow", "เพิ่มคิว"))
self.btn_delete.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อลบคิวที่ป้อนค่าผิดออกจากตารางคิว</p></body></html>"
))
self.btn_delete.setText(_translate("MainWindow", "ลบคิว"))
self.btn_search.setWhatsThis(
_translate(
"MainWindow",
"<html><head/><body><p>คลิ๊กเพื่อโหลดตารางคิวใหม่</p></body></html>"
))
self.btn_search.setText(_translate("MainWindow", "ค้นหาคิว"))
self.menuSetting.setTitle(_translate("MainWindow", "ตั้งค่า"))
self.action_Invite_window.setText(
_translate("MainWindow", "Call sentence setting"))
#self.actionPlace_windows.setText(_translate("MainWindow", "Place sentence"))
self.actionini_py.setText(_translate("MainWindow", "ini.py"))
self.actionDestination.setText(
_translate("MainWindow", "Destination setting"))
self.actionDrop_tablel.setText(_translate("MainWindow", "Drop tablel"))
def loadData(self):
self.lineEdit.clear()
self.lineEdit.setFocus()
# สร้าง model เรียก data จาก database
# ใช้ QSqlRelationalTableModel สำหรับตารางที่มีคีย์นอก
self.model = QSqlRelationalTableModel()
self.model.setTable('queue')
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.setHeaderData(0, Qt.Horizontal, 'ID')
self.model.setHeaderData(1, Qt.Horizontal, "รายชื่อ")
self.model.setHeaderData(2, Qt.Horizontal, "เวลาเพิ่มคิว")
self.model.setHeaderData(3, Qt.Horizontal, "เวลาเรียกคิว")
self.model.setHeaderData(4, Qt.Horizontal, "สถาณะคิว")
self.model.setHeaderData(5, Qt.Horizontal, "ปลายทาง")
self.model.setHeaderData(6, Qt.Horizontal, "Option")
# ให้ column#5 เป็นคีย์นอกดึงตารางนอกมาแสดง
self.model.setRelation(
5, QSqlRelation("destination", "des_id", "des_name"))
# เรียกใช้ model เรียกใช้ได้จากทุกที่ใน class ไม่ต้องเรียก loadData()
self.model.select()
# ให้ tableView เลือก data จาก model ไปแสดง
self.tableView.setModel(self.model)
self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView))
self.tableView.setColumnHidden(0, True)
self.tableView.setCornerButtonEnabled(False)
self.tableView.setSortingEnabled(True)
self.tableView.setColumnWidth(1, 210)
# เรียก fetchData เพื่อ fetch data เข้ามาใน model ให้หมด
self.fetchData()
# เมื่อ click ให้เลือกทั้งแถว
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
# สร้าง model เรียกตาราง destination แล้วยัดเข้า combobox
self.desModel = QSqlQueryModel()
selectQuery = QSqlQuery()
selectQuery.prepare('SELECT des_name FROM destination')
# print('QUERY = ' + str(selectQuery.lastQuery()))
if selectQuery.exec():
self.desModel.setQuery(selectQuery)
# print('SELECT des_name COMPLETE')
self.comboBox.setModel(self.desModel)
else:
print('SELECT FALSE = ' + selectQuery.lastError().text())
# สร้าง obj ของ destination setting ไว้รอเรียก
self.desDialog = QtWidgets.QDialog()
self.ui = destination2.Ui_Dialog()
self.ui.setupUi(self.desDialog)
self.tableView.selectRow(0)
def insertData(self):
q_number = self.lineEdit.text()
q_localtime = time.localtime()
q_enter_time = time.strftime("%H:%M:%S", q_localtime)
# q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง
if not q_number.isspace() and q_number != '':
# เรียก getDestination_id เพื่อหาค่า des_id เพื่อใช้ในคำสั่ง sql INSERT
des_id = self.getDestination_id()
try:
insertQuery = QSqlQuery()
insertQuery.prepare(
"INSERT INTO queue " + "(q_number,q_enter_time,des_id) " +
"VALUES " + f"('{q_number}','{q_enter_time}',{des_id})")
print('Query = ' + insertQuery.lastQuery())
if insertQuery.exec():
print('INSERT COMPLETE')
self.loadData()
else:
print('INSERT FALSE = ' + insertQuery.lastError().text())
except (Error) as e:
print(
str(time.strftime("%H:%M:%S : ", time.localtime())) +
'ERROR :' + str(e))
else:
self.showDialog('กรุณากรอกหมายเลขที่จะเพิ่มก่อน')
def getDestination_id(self):
# สร้างมาเพื่อให้ return ค่า des_id ในตาราง destination กลับไป
temp = self.comboBox.currentText()
desModel = QSqlQueryModel()
selectQuery = QSqlQuery()
selectQuery.prepare('SELECT des_id,des_name From destination')
if selectQuery.exec():
desModel.setQuery(selectQuery)
for i in range(desModel.rowCount()):
if temp == desModel.index(i, 1).data():
return desModel.index(i, 0).data()
else:
pass
else:
print('SELECT FALSE = ' + selectQuery.lastError().text())
def insertDev(self):
j = 10
for i in range(50):
q_number = random.randint(0, 100000)
q_enter_time = f'18:{j}:00'
des_id = 4
insertQuery = QSqlQuery()
insertQuery.prepare("INSERT INTO queue " +
"(q_number,q_enter_time,des_id) " + "VALUES " +
f"('{q_number}','{q_enter_time}',{des_id})")
insertQuery.exec()
self.model.select()
j += 1
print('insertDEV complete')
def searchData(self):
self.fetchData()
q_number = self.lineEdit.text()
totleRow = self.tableView.model().rowCount()
foundStatus = False
# q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง
if not q_number.isspace() and q_number != '':
i = 0
for i in range(totleRow):
# เก็บค่า q_number จาก tableView
s_number = self.tableView.model().data(
self.tableView.model().index(i, 1))
# เทียบค่า q_number ของ lineEdit กับ tableView
if q_number == s_number:
# print('found this number.')
foundStatus = True
break
else:
foundStatus = False
if foundStatus == True:
self.tableView.selectRow(i)
else:
self.showDialog('ค้นหมายเลข ' + q_number + ' ไม่พบ')
else:
self.showDialog('กรุณากรอกหมายเลขที่จะค้นก่อน')
def deleteData(self):
try:
# เก็บค่า text จาก column
current_row = self.tableView.selectedIndexes()
current_itemUse = current_row[0]
q_number = self.tableView.model().data(
self.tableView.model().index(current_itemUse.row(), 1))
except:
self.showDialog('กรุณาเลือกหมายเลขที่จะลบก่อน')
return
if q_number == None:
self.showDialog('เกิดข้อผิดพลาดระหว่างการลบ')
self.model.select()
return
# ยืนยันการลบด้วย code 1024
temp = self.confDelete(q_number)
if temp == 1024:
current_item = self.tableView.selectedIndexes()
for index in current_item:
self.model.removeRow(index.row())
self.model.select()
print('Record deleted.')
self.showDialog('ลบหมายเลขแล้ว')
else:
print('User cancel delete this record.')
def confDelete(self, q_number):
# สร้าง dialog เพื่อยืนยันการลบ และ return code ยืนยันการลบด้วย code 1024
confMsg = QtWidgets.QMessageBox()
confMsg.setIcon(QtWidgets.QMessageBox.Critical)
confMsg.setText('ยืนยันที่จะลบหมายเลข : ' + q_number)
confMsg.setWindowTitle('แจ้งเตือนการลบ')
confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok
| QtWidgets.QMessageBox.Cancel)
reval = confMsg.exec_()
return (reval)
def btn_delClicked(self):
button = QtGui.QGuiApplication.focusObject()
index = self.tableView.indexAt(button.pos())
if index.isValid():
print(index.row(), index.column())
def showDialog(self, message):
msg = QtWidgets.QMessageBox()
msg.setWindowTitle('แจ้งเตือน')
msg.setIcon(QtWidgets.QMessageBox.Information)
msg.setText(
# message + str(time.strftime(" @ %H:%M:%S ", time.localtime())))
message)
msg.exec_()
def speak(self):
try:
# เก็บค่า text จาก column
current_row = self.tableView.selectedIndexes()
current_inUse = current_row[0]
q_numberText = self.tableView.model().data(
self.tableView.model().index(current_inUse.row(), 1))
des_idText = self.tableView.model().data(
self.tableView.model().index(current_inUse.row(), 5))
except:
self.showDialog('กรุณาเลือกหมายเลขที่จะเรียกก่อน')
return
# ยืนยันการเรียกด้วย code 1024
temp = self.confSpeak(q_numberText)
q_localtime = time.localtime()
q_call_time = time.strftime("%H:%M:%S", q_localtime)
if temp == 1024:
# pyttsx3 engine
# engine = pyttsx3.init()
# """ RATE"""
# rate = engine.getProperty('rate')
# #print (rate)
# engine.setProperty('rate', 100)
# """VOLUME"""
# volume = engine.getProperty('volume')
# #print (volume)
# engine.setProperty('volume',1.0)
# """VOICE"""
# voices = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Speech\Voices\Tokens\TTS_THAI'
# engine.setProperty('voice',voices)
# engine.say('ขอเชิญหมายเลข' + q_numberText + 'ที่ช่อง' + des_idText + 'นะครับ')
# print('result = :'+ q_numberText)
# engine.runAndWait()
# engine.stop()
# gTTS engine
speech_text = 'ขอเชิญคุณ ' + q_numberText + ' ที่ช่อง ' + des_idText + ' ค่ะ'
tts = gTTS(speech_text, lang='th')
tts.save('temp.mp3')
speech_file = pyglet.media.load('temp.mp3', streaming=False)
speech_file.play()
sleep(speech_file.duration) # prevent from killing
os.remove('temp.mp3') # remove temperory file
try:
insertQuery = QSqlQuery()
insertQuery.prepare("UPDATE queue SET q_call_time = " +
f"'{q_call_time}'" +
" , q_call_status = 1 WHERE q_number = " +
f"'{q_numberText}'")
print('Query after speek ' + insertQuery.lastQuery())
if insertQuery.exec():
print('UPDATE COMPLETE')
self.loadData()
else:
print('UPDATE FALSE = ' + insertQuery.lastError().text())
except (Error) as e:
print('ERROR : ' + str(e))
else:
print('User decided cancel call.')
def confSpeak(self, q_numberText):
# สร้าง dialog เพื่อยืนยันการเรียกและ return code ยืนยันการเรียกด้วย code 1024
confMsg = QtWidgets.QMessageBox()
confMsg.setIcon(QtWidgets.QMessageBox.Warning)
confMsg.setText('ยืนยันที่จะเรียกหมายเลข : ' + q_numberText)
confMsg.setWindowTitle('แจ้งเตือน')
confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok
| QtWidgets.QMessageBox.Cancel)
reval = confMsg.exec_()
return (reval)
def fetchData(self):
self.model.select()
# fetch ข้อมูลจากตารางออกมาให้หมด มีผลตอนใช้ search
while self.model.canFetchMore() == True:
self.model.fetchMore()
print('rowCoundt = ' + str(self.model.rowCount()))
self.tableView.scrollToBottom()
class Window(QMainWindow):
def __init__(self):
super().__init__()
self.ui = mainform.Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'db/db_test.sqlite3'
self.ui.lbl_full_path_db.setText(self.db_path)
self.ui.btn_db_open.clicked.connect(self.db_file_open)
self.ui.btn_add.clicked.connect(self.db_add)
self.ui.btn_add.setEnabled(False)
self.ui.btn_del.clicked.connect(self.db_del)
self.ui.btn_del.setEnabled(False)
self.ui.save_changes.clicked.connect(self.save_change_db)
self.ui.save_changes.setEnabled(False)
self.ui.mnu_exit.triggered.connect(self.close)
self.ui.tablelist.setEnabled(False)
self.db = None
self.db_model = None
self.open_db()
self.ui.tablelist.currentIndexChanged.connect(self.show_table)
def open_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
# self.show_table()
def get_tables_name(self):
self.ui.tablelist.clear()
for table_name in self.db.tables():
self.ui.tablelist.addItem(table_name)
def db_file_open(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.lbl_full_path_db.setText(self.db_path)
self.db.close()
self.open_db()
self.ui.tablelist.setEnabled(True)
self.ui.btn_add.setEnabled(True)
self.ui.btn_del.setEnabled(True)
self.ui.save_changes.setEnabled(True)
self.ui.btn_db_open.setEnabled(False)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.tablelist.currentText()
if table == 'goods':
self.create_goods_table_model()
elif table == 'employees':
self.create_employees_table_model()
elif table == 'units':
self.create_units_table_model()
elif table == 'vendors':
self.create_vendors_table_model()
elif table == 'positions':
self.create_positions_table_model()
elif table == 'categories':
self.create_categories_table_model()
else:
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.hideColumn(0)
view.setItemDelegate(QSqlRelationalDelegate(view))
def db_add(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def db_del(self):
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(
2, QSqlRelation('units', 'unit_id', 'unit_name'))
self.table_model.setRelation(
3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.setRelation(
4, QSqlRelation('vendors', 'vendor_id', 'vendor_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название продукта')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal,
'Категория продукта')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Поставщик')
self.table_model.select()
def create_units_table_model(self):
self.table_model.setTable('units')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(
2, QSqlRelation('positions', 'position_id', 'position_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'ФИО сотрудника')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_positions_table_model(self):
self.table_model.setTable('positions')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_vendors_table_model(self):
self.table_model.setTable('vendors')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Наименование')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Форма собственности')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Телефон')
self.table_model.setHeaderData(5, QtCore.Qt.Horizontal,
'Электронная почта')
self.table_model.select()
def create_categories_table_model(self):
self.table_model.setTable('categories')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название категории')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Описание категории')
self.table_model.select()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(
f'{self.table_model.lastError().text()}')
class Components(object):
def __init__(self, connection):
self.connection = connection
self.model = QSqlRelationalTableModel()
self.model.setTable('customnetwork_component')
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setRelation(0, QSqlRelation('component', 'id', 'ticket'))
self.model.setRelation(1, QSqlRelation('customnetwork', 'id', 'name'))
self.model.select()
self.model.setHeaderData(0, Qt.Horizontal, "Ticket")
def getModel(self):
return self.model
def create(self, customnetwork_id, tickets):
# TODO: capturar excepciones de error que se pueden dar
log.debug(tickets)
query = QSqlQuery()
last = self.model.rowCount()
id = last + 1
self.connection.open()
for ticket in tickets:
comp_id = self._insert_component_table(query, ticket)
ok = self.\
_insert_customernetwork_component_table(query,
customnetwork_id,
comp_id)
self.model.select()
self.connection.close()
return id
def _insert_customernetwork_component_table(self, query, customnetwork_id,
comp_id):
query.prepare('insert into customnetwork_component values (?,?);')
query.addBindValue(comp_id)
query.addBindValue(customnetwork_id)
return query.exec_()
def _insert_component_table(self, query, ticket):
ok = query.exec_("select id from component;")
id = 0
if ok:
query.last()
last_id = int(query.value(0))
id = last_id + 1
query.prepare('insert into component values (?,?,?,?);')
query.addBindValue(id)
query.addBindValue(ticket)
query.addBindValue('_generic_description_')
query.addBindValue(GENERIC_SECTOR_CODE_ID)
if query.exec_():
return id
else:
return -1
def update_components(self, id, tickets):
# TODO: capturar excepciones de error que se pueden dar
query = QSqlQuery()
self.connection.open()
sql = """DELETE FROM customnetwork_component
WHERE customnetwork_id = ?;
"""
query.prepare(sql)
query.addBindValue(id)
ok = query.exec_()
if ok:
for ticket in tickets:
sql = "SELECT id, ticket FROM component WHERE ticket = '{}';"\
.format(ticket)
if query.exec_() and query.next():
component_id = int(query.value(0))
query.clear()
sql = """INSERT into customnetwork_component
VALUES (?, ?);
"""
query.prepare(sql)
query.addBindValue(component_id)
query.exec_()
else:
comp_id = self._insert_component_table(query, ticket)
ok = self.\
_insert_customernetwork_component_table(query,
id,
comp_id)
self.model.select()
self.connection.close()
return ok
def delete_cnc(self, customnetwork_id):
query = QSqlQuery()
self.connection.open()
query_str = 'delete from customnetwork_component where '
' customnetwork_id = ?;'
query.prepare(query)
query.addBindValue(customnetwork_id)
ok = query.exec_()
self.model.select()
self.connection.close()
return ok
class Client:
def __init__(self):
# init GUI
self.table_name = None
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
self.ui = Ui_MainWindow()
self.ui.setupUi(MainWindow)
MainWindow.show()
# parameters
self.db_path = None
self.db = None
# events
self.ui.openDbPushButton.clicked.connect(self.open_database)
self.ui.saveDbPushButton.clicked.connect(self.save_change_db)
self.ui.addRowPushButton.clicked.connect(self.add_row)
self.ui.deleteRowPushButton.clicked.connect(self.del_row)
self.ui.tablesListView.itemDoubleClicked.connect(self.change_table)
sys.exit(app.exec_())
def open_database_dialog(self):
try:
self.db_path = QFileDialog.getOpenFileName(self.ui.MainWindow, "Open file", '.')[0]
print(self.db_path)
self.ui.path_db.setText(self.db_path)
self.db.close()
except Exception as e:
pass
def open_database(self):
try:
self.open_database_dialog()
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
self.show_table()
except Exception as err:
print(err)
def get_tables_name(self):
self.ui.tablesListView.clear()
for table_name in self.db.tables():
self.ui.tablesListView.addItem(table_name)
def change_table(self, name):
self.table_name = self.ui.tablesListView.currentItem().text()
self.show_table()
def show_table(self):
self.table_model = QSqlRelationalTableModel()
if self.table_name:
table = self.table_name
else:
table = [str(self.ui.tablesListView.item(i).text()) for i in range(self.ui.tablesListView.count())][0]
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableViewerTableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def add_row(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Changes were saved')
else:
self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
def del_row(self):
rs = list(map(lambda x: x.row(), self.ui.tableViewerTableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
self.ui.statusbar.showMessage("Row was deleted")
class APISSharding(QDialog, FORM_CLASS):
shardingEditsSaved = pyqtSignal(bool)
def __init__(self, iface, dbm, parent=None):
"""Constructor."""
super(APISSharding, self).__init__(parent)
self.iface = iface
self.dbm = dbm
self.setupUi(self)
# Initial window size/pos last saved. Use default values for first time
if GetWindowSize("sharding"):
self.resize(GetWindowSize("sharding"))
self.settings = QSettings(QSettings().value("APIS/config_ini"),
QSettings.IniFormat)
self.editMode = False
self.addMode = False
self.initalLoad = True
# Signals/Slot Connections
self.rejected.connect(self.onReject)
#self.uiButtonBox.rejected.connect(self.onReject)
self.uiOkBtn.clicked.connect(self.onAccept)
self.uiCancelBtn.clicked.connect(self.cancelEdit)
self.uiSaveBtn.clicked.connect(self.saveEdits)
mViewPictures = QMenu()
aViewPicturesPreview = mViewPictures.addAction(
QIcon(
os.path.join(QSettings().value("APIS/plugin_dir"), 'ui',
'icons', 'image.png')), "in Vorschau")
aViewPicturesPreview.triggered.connect(self.viewPictures)
aViewPicturesFolder = mViewPictures.addAction(
QIcon(
os.path.join(QSettings().value("APIS/plugin_dir"), 'ui',
'icons', 'image.png')), "in Ordner")
aViewPicturesFolder.triggered.connect(self.openPictures)
self.uiViewPicturesTBtn.setMenu(mViewPictures)
self.uiViewPicturesTBtn.clicked.connect(
self.uiViewPicturesTBtn.showMenu)
mViewSketches = QMenu()
aViewSketchesPreview = mViewSketches.addAction(
QIcon(
os.path.join(QSettings().value("APIS/plugin_dir"), 'ui',
'icons', 'sketch.png')), "in Vorschau")
aViewSketchesPreview.triggered.connect(self.viewSketches)
aViewSketchesFolder = mViewSketches.addAction(
QIcon(
os.path.join(QSettings().value("APIS/plugin_dir"), 'ui',
'icons', 'sketch.png')), "in Ordner")
aViewSketchesFolder.triggered.connect(self.openSketches)
self.uiViewSketchesTBtn.setMenu(mViewSketches)
self.uiViewSketchesTBtn.clicked.connect(
self.uiViewSketchesTBtn.showMenu)
self.initalLoad = False
def openSharding(self, siteNumber, shardingNumber):
self.initalLoad = True
self.siteNumber = siteNumber
self.shardingNumber = shardingNumber
#QMessageBox.warning(None, self.tr(u"Neu"), self.tr(u"{0}, {1}".format(siteNumber, shardingNumber)))
# Setup sharding model
self.model = QSqlRelationalTableModel(self, self.dbm.db)
self.model.setTable("begehung")
self.model.setFilter("fundortnummer='{0}' AND begehung='{1}'".format(
self.siteNumber, self.shardingNumber))
res = self.model.select()
self.setupMapper()
self.mapper.toFirst()
self.setKgNameAndCode()
self.initalLoad = False
def setKgNameAndCode(self):
query = QSqlQuery(self.dbm.db)
qryStr = u"select CASE WHEN katastralgemeinde IS NULL AND katastralgemeindenummer IS NULL THEN '--' ELSE katastralgemeindenummer || ' - ' || katastralgemeinde END AS kg FROM fundort WHERE fundortnummer = '{0}'".format(
self.siteNumber)
query.exec_(qryStr)
query.first()
self.uiCadastralCommunityEdit.setText(query.value(0))
def setupMapper(self):
self.mapper = QDataWidgetMapper(self)
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
self.mapper.setItemDelegate(ShardingDelegate())
self.mapper.setModel(self.model)
self.mandatoryEditors = [self.uiShardingDate]
# LineEdits & PlainTextEdits
self.intValidator = QIntValidator()
self.doubleValidator = QDoubleValidator()
self.lineEditMaps = {
"fundortnummer": {
"editor": self.uiSiteNumberEdit
},
"begehung": {
"editor": self.uiShardingNumberEdit
},
"name": {
"editor": self.uiNameEdit
},
"parzelle": {
"editor": self.uiPlotPTxt
},
"sichtbarkeit": {
"editor": self.uiVisibilityEdit
},
"verbleib": {
"editor": self.uiWhereaboutsEdit
},
"funde": {
"editor": self.uiFindsPTxt
},
"morphologie": {
"editor": self.uiMorphologyPTxt
},
"sonstiges": {
"editor": self.uiMiscellaneousPTxt
}
}
for key, item in self.lineEditMaps.items():
self.mapper.addMapping(item["editor"], self.model.fieldIndex(key))
if "validator" in item:
item["editor"].setValidator(item["validator"])
#item["editor"].textChanged.connect(partial(self.onLineEditChanged, item["editor"]))
item["editor"].textChanged.connect(self.onLineEditChanged)
# Date and Times
self.mapper.addMapping(self.uiShardingDate,
self.model.fieldIndex("datum"))
# ComboBox without Model
self.mapper.addMapping(self.uiShardingTypeCombo,
self.model.fieldIndex("begehtyp"))
self.uiShardingTypeCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiShardingTypeCombo.setAutoCompletion(True)
self.uiShardingTypeCombo.lineEdit().setValidator(
InListValidator([
self.uiShardingTypeCombo.itemText(i)
for i in range(self.uiShardingTypeCombo.count())
], self.uiShardingTypeCombo.lineEdit(), None, self))
# ComboBox without Model
self.mapper.addMapping(self.uiConditionPlantCoverCombo,
self.model.fieldIndex("zustand_bewuchs"))
self.uiConditionPlantCoverCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiConditionPlantCoverCombo.setAutoCompletion(True)
self.uiConditionPlantCoverCombo.lineEdit().setValidator(
InListValidator([
self.uiConditionPlantCoverCombo.itemText(i)
for i in range(self.uiConditionPlantCoverCombo.count())
], self.uiConditionPlantCoverCombo.lineEdit(), None, self))
# ComboBox without Model
self.mapper.addMapping(self.uiConditionLightCombo,
self.model.fieldIndex("zustand_licht"))
self.uiConditionLightCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiConditionLightCombo.setAutoCompletion(True)
self.uiConditionLightCombo.lineEdit().setValidator(
InListValidator([
self.uiConditionLightCombo.itemText(i)
for i in range(self.uiConditionLightCombo.count())
], self.uiConditionLightCombo.lineEdit(), None, self))
# ComboBox without Model
self.mapper.addMapping(self.uiConditionSoilCombo,
self.model.fieldIndex("zustand_boden"))
self.uiConditionSoilCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiConditionSoilCombo.setAutoCompletion(True)
self.uiConditionSoilCombo.lineEdit().setValidator(
InListValidator([
self.uiConditionSoilCombo.itemText(i)
for i in range(self.uiConditionSoilCombo.count())
], self.uiConditionSoilCombo.lineEdit(), None, self))
# ComboBox without Model
self.mapper.addMapping(self.uiConditionMoistureCombo,
self.model.fieldIndex("zustand_feuchtigkeit"))
self.uiConditionMoistureCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiConditionMoistureCombo.setAutoCompletion(True)
self.uiConditionMoistureCombo.lineEdit().setValidator(
InListValidator([
self.uiConditionMoistureCombo.itemText(i)
for i in range(self.uiConditionMoistureCombo.count())
], self.uiConditionMoistureCombo.lineEdit(), None, self))
# ComboBox without Model
self.mapper.addMapping(self.uiConditionRainCombo,
self.model.fieldIndex("zustand_abgeregnet"))
self.uiConditionRainCombo.editTextChanged.connect(
self.onLineEditChanged)
# FIXME Pyqt5 AutoCompletion
#self.uiConditionRainCombo.setAutoCompletion(True)
self.uiConditionRainCombo.lineEdit().setValidator(
InListValidator([
self.uiConditionRainCombo.itemText(i)
for i in range(self.uiConditionRainCombo.count())
], self.uiConditionRainCombo.lineEdit(), None, self))
def onLineEditChanged(self):
sender = self.sender()
if not self.editMode and not self.initalLoad:
self.startEditMode()
if not self.initalLoad:
sender.setStyleSheet(
"{0} {{background-color: rgb(153, 204, 255);}}".format(
sender.metaObject().className()))
self.editorsEdited.append(sender)
def onAccept(self):
'''
Check DB
Save options when pressing OK button
Update Plugin Status
'''
# Save Settings
SetWindowSize("sharding", self.size())
self.accept()
def onReject(self):
'''
Run some actions when
the user closes the dialog
'''
if self.editMode:
res = self.cancelEdit()
if res:
SetWindowSize("sharding", self.size())
self.close()
else:
self.show()
else:
SetWindowSize("sharding", self.size())
self.close()
def addNewSharding(self, siteNumber):
self.initalLoad = True
self.siteNumber = siteNumber
# get new sharding number
query = QSqlQuery(self.dbm.db)
qryStr = "SELECT CASE WHEN max(begehung) IS NULL THEN 1 ELSE max(begehung)+1 END begehungNeu FROM begehung WHERE fundortnummer='{0}'".format(
self.siteNumber)
query.exec_(qryStr)
query.first()
self.shardingNumber = query.value(0)
self.model = QSqlRelationalTableModel(self, self.dbm.db)
self.model.setTable("begehung")
self.model.setFilter("fundortnummer='{0}'".format(self.siteNumber))
res = self.model.select()
#self.model.submitAll()
while (self.model.canFetchMore()):
self.model.fetchMore()
row = self.model.rowCount()
#QMessageBox.information(None, "begehung", "{0}".format(row))
self.model.insertRow(row)
#QMessageBox.information(None, "begehung", "{0}".format(self.model.rowCount()))
self.setupMapper()
self.mapper.toLast()
self.addMode = True
self.startEditMode()
# self.mapper.submit()
# self.model.insertRow(row)
# self.mapper.setCurrentIndex(row)
self.uiSiteNumberEdit.setText(self.siteNumber)
self.uiShardingNumberEdit.setText(str(self.shardingNumber))
now = QDate.currentDate()
self.uiShardingDate.setDate(now)
self.setKgNameAndCode()
#QMessageBox.warning(None, self.tr(u"Neu"), self.tr(u"{0}, {1}".format(siteNumber,nn)))
self.initalLoad = False
def removeNewSharding(self):
self.initalLoad = True
row = self.mapper.currentIndex()
self.model.removeRow(row + 1)
self.model.submitAll()
while (self.model.canFetchMore()):
self.model.fetchMore()
self.mapper.toLast()
self.initalLoad = False
def saveEdits(self):
#Check Mandatory fields
flag = False
for mEditor in self.mandatoryEditors:
cName = mEditor.metaObject().className()
if cName == 'QDateEdit':
value = mEditor.date().toString("yyyy-MM-dd")
elif cName == 'QLineEdit':
value = mEditor.text()
elif cName == 'QComboBox':
if mEditor.isEditable():
value = mEditor.lineEdit().text()
else:
if mEditor.currentIndex == -1:
value = ''
else:
value = '1'
if value.strip() == "":
flag = True
mEditor.setStyleSheet(
"{0} {{background-color: rgb(240, 160, 160);}}".format(
cName))
if mEditor not in self.editorsEdited:
self.editorsEdited.append(mEditor)
else:
if mEditor in self.editorsEdited:
mEditor.setStyleSheet(
"{0} {{background-color: rgb(153, 204, 255);}}".format(
cName))
#else:
#mEditor.setStyleSheet("")
if flag:
QMessageBox.warning(
self, self.tr(u"Benötigte Felder Ausfüllen"),
self.
tr(u"Füllen Sie bitte alle Felder aus, die mit * gekennzeichnet sind."
))
return False
#saveToModel
currIdx = self.mapper.currentIndex()
#QMessageBox.information(None, "begehung", "{0}".format(currIdx))
#now = QDate.currentDate()
#self.uiLastChangesDate.setDate(now)
self.mapper.submit()
self.mapper.setCurrentIndex(currIdx)
# emit signal
self.shardingEditsSaved.emit(True)
self.endEditMode()
return True
def cancelEdit(self):
currIdx = self.mapper.currentIndex()
if self.editMode:
result = QMessageBox.question(
self, self.tr(u"Änderungen wurden vorgenommen!"),
self.tr(u"Möchten Sie die Änerungen speichern?"),
QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
#save or not save
if result == QMessageBox.Yes:
res = self.saveEdits()
if res:
return True
else:
return False
elif result == QMessageBox.No:
if self.addMode:
#self.close()
self.done(1)
self.removeNewSharding()
self.endEditMode()
return True
else:
self.mapper.setCurrentIndex(currIdx)
self.endEditMode()
return True
def startEditMode(self):
self.editMode = True
self.uiOkBtn.setEnabled(False)
self.uiSaveBtn.setEnabled(True)
self.uiCancelBtn.setEnabled(True)
self.editorsEdited = []
self.uiShardingDate.setReadOnly(not self.addMode)
if self.uiShardingDate.isReadOnly():
self.uiShardingDate.setStyleSheet(
"background-color: rgb(218, 218, 218);")
else:
self.uiShardingDate.setStyleSheet("")
def endEditMode(self):
self.editMode = False
self.addMode = False
self.uiOkBtn.setEnabled(True)
self.uiSaveBtn.setEnabled(False)
self.uiCancelBtn.setEnabled(False)
self.uiShardingDate.setReadOnly(not self.addMode)
self.uiShardingDate.setStyleSheet(
"background-color: rgb(218, 218, 218);")
for editor in self.editorsEdited:
cName = editor.metaObject().className()
if (cName == "QLineEdit"
or cName == "QDateEdit") and editor.isReadOnly():
editor.setStyleSheet(
"{0} {{background-color: rgb(218, 218, 218);}}".format(
cName))
else:
editor.setStyleSheet("")
self.editorsEdited = []
def viewPictures(self):
dirName = self.settings.value("APIS/insp_image_dir")
folderNameType = self.settings.value("APIS/insp_image_foto_dir")
folderNameSite = self.getFolderNameSite(self.siteNumber)
path = dirName + u'\\' + folderNameSite + u'\\' + folderNameType
self.loadInImageViewer(path)
def openPictures(self):
dirName = self.settings.value("APIS/insp_image_dir")
folderNameType = self.settings.value("APIS/insp_image_foto_dir")
folderNameSite = self.getFolderNameSite(self.siteNumber)
path = dirName + u'\\' + folderNameSite + u'\\' + folderNameType
if not OpenFileOrFolder(path):
QMessageBox.information(
self, u"Begehung",
u"Das Verzeichnis '{0}' wurde nicht gefunden.".format(path))
def viewSketches(self):
dirName = self.settings.value("APIS/insp_image_dir")
folderNameType = self.settings.value("APIS/insp_image_sketch_dir")
folderNameSite = self.getFolderNameSite(self.siteNumber)
path = dirName + u'\\' + folderNameSite + u'\\' + folderNameType
self.loadInImageViewer(path)
def openSketches(self):
dirName = self.settings.value("APIS/insp_image_dir")
folderNameType = self.settings.value("APIS/insp_image_sketch_dir")
folderNameSite = self.getFolderNameSite(self.siteNumber)
path = dirName + u'\\' + folderNameSite + u'\\' + folderNameType
if not OpenFileOrFolder(path):
QMessageBox.information(
None, u"Begehung",
u"Das Verzeichnis '{0}' wurde nicht gefunden.".format(path))
def getFolderNameSite(self, siteNumber):
query = QSqlQuery(self.dbm.db)
#qryStr = u"SELECT trim(katastralgemeinde) || ' ' || trim(katastralgemeindenummer) || '.' || substr('000' || fundortnummer_nn_legacy, -3, 3) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'".format(siteNumber)
query.prepare(
u"SELECT land || '\\' || CASE WHEN land = 'AUT' THEN replace(replace(replace(replace(lower(trim(katastralgemeinde)), '.',''), '-', ' '), '(', ''), ')', '') || ' ' ELSE '' END || substr('000000' || fundortnummer_nn, -6, 6) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'"
.format(siteNumber))
query.exec_()
query.first()
return query.value(0)
def loadInImageViewer(self, path):
dir = QDir(path)
if dir.exists():
entryList = dir.entryList(['*.jpg'], QDir.Files)
if len(entryList) > 0:
# load in thumb viewer
# QMessageBox.information(None, u"Begehung", u",".join(entryList))
imagePathList = []
for image in entryList:
imagePathList.append(path + u'\\' + image)
widget = APISThumbViewer()
widget.load(imagePathList)
widget.show()
if widget.exec_():
pass
# app.exec_()
else:
QMessageBox.information(
self, u"Begehung",
u"Es wurden keine Dateien [*.jpg] für diesen Fundort gefunden."
)
else:
QMessageBox.information(
self, u"Begehung",
u"Das Verzeichnis '{0}' wurde nicht gefunden.".format(path))
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 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 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_()
class MainWindow(QtWidgets.QMainWindow):
def __init__(self) -> None:
super().__init__()
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.dbpath = DEFAULT_DATABASE_PATH
self.qmainwindow = QtWidgets.QMainWindow()
# define buttons vehaviour
self.button_actions: Dict[str, QtWidgets.QAction] = {}
self.define_buttons()
# UI variables
self.label = QtWidgets.QLabel()
self.combo_box = QtWidgets.QComboBox()
self.table_model = QSqlRelationalTableModel()
self.table_view = QtWidgets.QTableView()
# create window and show it
self.draw()
self.qmainwindow.show()
def draw(self) -> None:
self.qmainwindow.setObjectName("MainWindow")
self.qmainwindow.resize(800, 600)
self.qmainwindow.setWindowTitle("Database manager")
widget = QtWidgets.QWidget(self.qmainwindow)
widget.setObjectName("widget")
self.qmainwindow.setCentralWidget(widget)
self.toolbar = self.qmainwindow.addToolBar("ToolBar")
for _, action in self.button_actions.items():
self.toolbar.addAction(action)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 10), QtCore.QSize(105, 16)))
_.setText("Database path: ")
self.label = QtWidgets.QLabel(widget)
self.label.setGeometry(
QtCore.QRect(QtCore.QPoint(110, 10), QtCore.QSize(750, 16)))
self.label.setText(self.dbpath)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 30), QtCore.QSize(105, 16)))
_.setText("Choosen table: ")
self.combo_box = QtWidgets.QComboBox(self.qmainwindow)
self.combo_box.setGeometry(
QtCore.QRect(QtCore.QPoint(100, 55), QtCore.QSize(200, 16)))
self.combo_box.currentIndexChanged.connect(self.update_table)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 65), QtCore.QSize(105, 16)))
_.setText("Table")
self.table_view = QtWidgets.QTableView(self.qmainwindow)
self.table_view.setObjectName("tableView")
self.table_view.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 115), QtCore.QSize(780, 475)))
self.table_view.setShowGrid(False)
def define_buttons(self) -> None:
def _exit_action() -> None:
QtWidgets.qApp.quit()
def _show_dialog() -> None:
self.dbpath, _ = QtWidgets.QFileDialog.getOpenFileName(
self, "Open file")
self.label.setText(self.dbpath)
def _load_database() -> None:
self.db.setDatabaseName(self.dbpath)
self.db.open()
# update selectors
self.combo_box.clear()
for tbl in self.db.tables():
self.combo_box.addItem(tbl)
self.update_table()
def _save_database() -> None:
self.table_model.submitAll()
def _insert_row() -> None:
self.table_model.insertRows(self.table_model.rowCount(), 1)
def _remove_row() -> None:
for idx in self.table_view.selectedIndexes():
self.table_model.removeRows(idx.row(), 1)
self.button_actions.update(
{"exit": QtWidgets.QAction("Exit", self.qmainwindow)})
self.button_actions["exit"].triggered.connect(_exit_action)
self.button_actions.update({
"dbpath":
QtWidgets.QAction("Set database path", self.qmainwindow)
})
self.button_actions["dbpath"].triggered.connect(_show_dialog)
self.button_actions.update(
{"load": QtWidgets.QAction("Load database", self.qmainwindow)})
self.button_actions["load"].triggered.connect(_load_database)
self.button_actions.update(
{"save": QtWidgets.QAction("Save database", self.qmainwindow)})
self.button_actions["save"].triggered.connect(_save_database)
self.button_actions.update(
{"insert": QtWidgets.QAction("Insert row", self.qmainwindow)})
self.button_actions["insert"].triggered.connect(_insert_row)
self.button_actions.update(
{"remove": QtWidgets.QAction("Remove row", self.qmainwindow)})
self.button_actions["remove"].triggered.connect(_remove_row)
def update_table(self) -> None:
self.table_model = QSqlRelationalTableModel()
tablename = self.combo_box.currentText()
self.table_model.setTable(tablename)
# foreign key logic
if tablename == "goods":
self.table_model.setRelation(2,
QSqlRelation("units", "id", "unit"))
self.table_model.setRelation(
3, QSqlRelation("categories", "id", "name"))
elif tablename == "employees":
self.table_model.setRelation(
2, QSqlRelation("positions", "id", "position"))
elif tablename == "vendors":
self.table_model.setRelation(
2, QSqlRelation("ownerships", "id", "ownership"))
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.table_view.setModel(self.table_model)
self.table_view.setItemDelegate(QSqlRelationalDelegate(
self.table_view))
# define header width
self.table_view.horizontalHeader().setStretchLastSection(False)
self.table_view.resizeColumnsToContents()
self.table_view.horizontalHeader().setMinimumSectionSize(50)
self.table_view.horizontalHeader().setStretchLastSection(True)
class MyWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'database\sklad_db.sqlite3'
self.ui.path_db.setText(self.db_path)
self.save_db = QAction(QIcon('icon/save.png'), 'Сохранить', self)
self.find_db_file = QAction(QIcon('icon/open_db.png'), 'Открыть БД',
self)
self.add_rec = QAction(QIcon('icon/add.png'), 'Добавить запись', self)
self.del_rec = QAction(QIcon('icon/del.png'), 'Удалить запись', self)
self.ui.toolBar.addAction(self.save_db)
self.ui.toolBar.addAction(self.find_db_file)
self.ui.toolBar.addAction(self.add_rec)
self.ui.toolBar.addAction(self.del_rec)
self.db = None
self.table_model = None
self.open_db()
self.add_rec.triggered.connect(self.add_record_action)
self.del_rec.triggered.connect(self.del_record_action)
self.save_db.triggered.connect(self.save_change_db)
self.find_db_file.triggered.connect(self.find_db_file_action)
self.ui.comboBox.currentIndexChanged.connect(self.show_table)
def open_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_table_name()
self.show_table()
def get_table_name(self):
self.ui.comboBox.clear()
table_name_ru = None
for table_name in self.db.tables():
if table_name == 'goods':
table_name_ru = 'Товары'
elif table_name == 'categories':
table_name_ru = 'Категории'
elif table_name == 'units':
table_name_ru = 'Единица измерения'
elif table_name == 'employees':
table_name_ru = 'Персонал'
elif table_name == 'positions':
table_name_ru = 'Должности'
elif table_name == 'vendors':
table_name_ru = 'Поставщики'
self.ui.comboBox.addItem(table_name_ru)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.comboBox.currentText()
if table == 'Товары':
self.create_goods_table_model()
elif table == 'Персонал':
self.create_employees_table_model()
else:
if table == 'Единица измерения':
table = 'units'
elif table == 'Категории':
table = 'categories'
elif table == 'Должности':
table = 'positions'
elif table == 'Поставщики':
table = 'vendors'
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(2, QSqlRelation('units', 'unit_id',
'unit'))
self.table_model.setRelation(
3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(
2, QSqlRelation('positions', 'position_id', 'position'))
self.table_model.select()
def add_record_action(self):
self.ui.statusbar.clearMessage()
self.table_model.insertRows(self.table_model.rowCount(), 1)
def del_record_action(self):
self.ui.statusbar.clearMessage()
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
#print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def find_db_file_action(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.path_db.setText(self.db_path)
self.db.close()
self.open_db()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(
f'{self.table_model.lastError().text()}')