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 loadModel(name_table): model = QSqlRelationalTableModel() model.setTable(name_table) processingModel[name_table](model) model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.select() return model
class Contacts(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.setWindowTitle("QTableView Example") self.resize(800, 600) # Set up the model self.model = QSqlRelationalTableModel(self) self.model.setTable("queue") self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setHeaderData(0, Qt.Horizontal, "ID") self.model.setHeaderData(1, Qt.Horizontal, "Number") self.model.setHeaderData(2, Qt.Horizontal, "Enter Time") self.model.setHeaderData(3, Qt.Horizontal, "Call Time") self.model.setHeaderData(4, Qt.Horizontal, "Status") self.model.setHeaderData(5, Qt.Horizontal, "Destination") # ใช้ QSqlRelation อ่านคีย์นอกจากในตาราง queue แล้ว maps เข้ากับตาราง destination เลือก index กับ text ที่ต้องการแสดงในตาราง queue self.model.setRelation( 5, QSqlRelation("destination", "des_id", "des_name")) self.model.select() # Set up the view self.view = QTableView() self.view.setModel(self.model) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.view.setColumnHidden(0, True) self.view.setCornerButtonEnabled(False) #self.view.setEditTriggers(QAbstractItemView()) #self.headerView = QHeaderView() #self.view.setHorizontalHeader() #print(str(self.view.editTriggers())) self.view.resizeColumnsToContents() # self.cbxModel = QSqlQueryModel(self) # self.cbxModel.setQuery = "SELECT des_name FROM destination" # self.cbxModel.query() # สร้าง comboboxmodel ดึงข้อมูลจาก ex1.destination #self.cbxModel = QSqlTableModel(self) #self.cbxModel.setTable("destination") #self.cbxModel.select() # สร้าง comboboxview #self.cbxView = QComboBox() #self.cbxView.setModel(self.cbxModel) # เลือก column ที่จะมาแสดง #self.cbxView.setModelColumn(1) # วาด comboboxview ลงบน tableview ติดปัญหา comboboxview จะวาดลงใน record สุดท้ายเสมอ # for i in range(self.model.rowCount()): # i = self.view.model().index(1, 5) # self.view.setIndexWidget(i,self.cbxView) self.setCentralWidget(self.view)
class MainWindow(QMainWindow, Ui_MainWindow): def __init__(self, *args, **kwargs): super(MainWindow, self).__init__(*args, **kwargs) self.setupUi(self) # toolbar actions self.actionInitialize_Reset.triggered.connect(self.open_init_dialog) # table view self.model = QSqlRelationalTableModel(self, db) self.model.setTable(Server.__tablename__) self.model.select() self.tableView.setModel(self.model) logical_headers = ['ID', 'Server', 'Port', 'Password', 'Encryption', 'Protocol', 'Protocol Params', 'Obfuscation', 'Obfuscation Params', 'Remark', 'Group'] visual_headers = logical_headers.copy() displayed_columns = ['Remark', 'Server', 'Port', 'Group'] for idx, header in enumerate(logical_headers): self.model.setHeaderData(idx, QtCore.Qt.Horizontal, header) for displayed_header in reversed(displayed_columns): idx = visual_headers.index(displayed_header) self.tableView.horizontalHeader().moveSection(idx, 0) visual_headers.pop(idx) visual_headers.insert(0, displayed_header) for idx, header in enumerate(logical_headers): if header not in displayed_columns: self.tableView.horizontalHeader().setSectionHidden(idx, True) self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def updateTableView(self): self.model.select() @staticmethod def open_init_dialog(): qApp.exit(EXIT_CODE_RESET) def open_debug_terminal(self): pass def add_server(self, server: dict): pass def bulk_add_server(self, servers: [dict]): pass def update_server(self, server: Server): pass def remove_server(self, server: Server): pass
class DBViewWindow(QWidget, Ui_DB_View_Form): def __init__(self, database_name): super().__init__() # uic.loadUi('db_view.ui', self) self.setupUi(self) # self.db_name = database_name # # Подключение БД к таблице отображения # # Подключение через QSqlRelationalTableModel # self.db = QSqlDatabase.addDatabase('QSQLITE') # self.db.setDatabaseName(self.db_name) # self.db.open() self.model = QSqlRelationalTableModel(self) self.model.setTable('goods') self.model.setRelation( 5, QSqlRelation('statuses', 'id_status', 'status_name')) self.model.setRelation( 6, QSqlRelation('goods_types', 'id_goods_type', 'goods_type_name')) self.model.setRelation( 7, QSqlRelation('goods_subtypes', 'id_goods_subtype', 'goods_subtype_name')) self.model.setRelation( 8, QSqlRelation('location', 'id_location', 'location_name')) self.model.setRelation( 9, QSqlRelation('responsibles', 'id_responsible', 'FIO')) self.refresh() self.tableView.doubleClicked.connect(self.table_clicked) # self.tableView.clicked.connect() self.refreshBtn.clicked.connect(self.refresh) self.save_all_btn.clicked.connect(self.submitall) def table_clicked(self): row = self.tableView.currentIndex().row() if row != -1: self.tableView.selectRow(row) index = self.tableView.currentIndex() self.edit_form = EditForm(index, self.model) self.edit_form.show() # Предположительно на выходе будет закрываться БД def __exit__(self, exc_type, exc_val, exc_tb): self.db.close() super().__exit__() def refresh(self): self.model.select() self.tableView.setModel(self.model) self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView)) def submitall(self): if not self.model.submitAll(): print(self.model.lastError()) else: self.refresh()
def 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()
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 createTable(self): """ Create the table using model/view architecture. """ # Create the model model = QSqlRelationalTableModel() model.setTable('accounts') # Set up relationship for foreign keys model.setRelation(model.fieldIndex('country_id'), QSqlRelation('countries', 'id', 'country')) table_view = QTableView() table_view.setModel(model) table_view.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # Populate the model with data model.select() # Main layout main_v_box = QVBoxLayout() main_v_box.addWidget(table_view) self.setLayout(main_v_box)
class Model(object): def __init__(self, databasetype="QSQLITE", databasename="Sqlite_Sql/testcase.db", sqltablename="result"): db = QSqlDatabase.addDatabase(databasetype) db.setDatabaseName(databasename) self.sqltablename = sqltablename self.tablemodel() def tablemodel(self): # self.model=QSqlTableModel() # self.model.setRelation(4,QSqlRelation("TEST","id_test","method_name")) self.model = QSqlRelationalTableModel() self.model.setTable(self.sqltablename) self.model.setHeaderData(0, Qt.Horizontal, '用例ID') self.model.setHeaderData(1, Qt.Horizontal, '用例名称') self.model.setHeaderData(2, Qt.Horizontal, '脚本名称') self.model.setHeaderData(3, Qt.Horizontal, '执行状态') self.model.setHeaderData(4, Qt.Horizontal, '结论') self.model.setHeaderData(5, Qt.Horizontal, '时间') self.model.setHeaderData(6, Qt.Horizontal, '操作') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.select()
class MainWindow(QMainWindow): def __init__(self): super().__init__() container = QWidget() layout = QVBoxLayout() self.search = QLineEdit() self.search.textChanged.connect(self.update_filter) self.table = QTableView() layout.addWidget(self.search) layout.addWidget(self.table) container.setLayout(layout) self.model = QSqlRelationalTableModel(db=db) self.table.setModel(self.model) self.model.setTable("Track") self.model.setRelation(2, QSqlRelation("Album", "AlbumId", "Title")) self.model.setRelation(3, QSqlRelation("MediaType", "MediaTypeId", "Name")) self.model.setRelation(4, QSqlRelation("Genre", "GenreId", "Name")) delegate = QSqlRelationalDelegate(self.table) self.table.setItemDelegate(delegate) self.model.select() self.setMinimumSize(QSize(1024, 600)) self.setCentralWidget(container) def update_filter(self, s): s = re.sub("[\W]+", "", s) filter_str = 'Name LIKE "%{}%"'.format(s) self.model.setFilter(filter_str)
class TableModel(): def __init__(self, db): self.model = QSqlRelationalTableModel(db=db) self.model.setTable('balance') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation('items', 'id', 'name')) self.model.setRelation(2, QSqlRelation('categories', 'id', 'name')) self.model.setRelation(4, QSqlRelation('currencies', 'id', 'name')) self.model.setRelation(5, QSqlRelation('users', 'id', 'full_name')) self.model.setRelation(6, QSqlRelation('places', 'id', 'name')) self.model.setRelation(8, QSqlRelation('measures', 'id', 'short')) self.model.setHeaderData(0, Qt.Horizontal, QtGui.qApp.tr("ID")) self.model.setHeaderData(1, Qt.Horizontal, QtGui.qApp.tr("Item")) self.model.setHeaderData(2, Qt.Horizontal, QtGui.qApp.tr("Category")) self.model.setHeaderData(3, Qt.Horizontal, QtGui.qApp.tr("Cost")) self.model.setHeaderData(4, Qt.Horizontal, QtGui.qApp.tr("Currency")) self.model.setHeaderData(5, Qt.Horizontal, QtGui.qApp.tr("By whom")) self.model.setHeaderData(6, Qt.Horizontal, QtGui.qApp.tr("Where")) self.model.setHeaderData(7, Qt.Horizontal, QtGui.qApp.tr("Qty/Amount")) self.model.setHeaderData(8, Qt.Horizontal, QtGui.qApp.tr("Units")) self.model.setHeaderData(9, Qt.Horizontal, QtGui.qApp.tr("is spending")) self.model.setHeaderData(10, Qt.Horizontal, QtGui.qApp.tr("Note")) self.model.setHeaderData(11, Qt.Horizontal, QtGui.qApp.tr("Date and " "Time")) self.model.removeColumn(12) self.model.removeColumn(13) if not self.model.select(): _log("Table model selection error[%s]: %s" % ( self.model.lastError().type(), self.model.lastError().text() )) def get_model(self): return self.model
class Ui_MainWindow(object): def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(1200, 800) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.tabWidget = QtWidgets.QTabWidget(self.centralwidget) self.tabWidget.setGeometry(QtCore.QRect(0, 0, 1200, 800)) self.tabWidget.setObjectName("tabWidget") # TAB 1 self.tab = QtWidgets.QWidget() self.tab.setObjectName("tab") self.tabWidget.addTab(self.tab, "") # Admin site integration self.webView = QtWebEngineWidgets.QWebEngineView(self.tab) self.webView.setUrl(QtCore.QUrl("http://185.188.182.76:8000/admin")) self.webView.resize(1200, 800) self.webView.setObjectName("webView") # TAB 2 self.tab_2 = QtWidgets.QWidget() self.tab_2.setObjectName("tab_2") # Desktop Database integration self.devices_table = QtWidgets.QTableView(self.tab_2) self.devices_table.setGeometry(QtCore.QRect(0, 0, 1200, 800)) self.devices_table.setObjectName("tableView") self.devices_table_UI() self.add_record() # !!! last_month_day = calendar.monthrange(int(strftime("%Y", gmtime())), int(strftime("%m", gmtime())))[1] today = strftime("%d", gmtime()) if last_month_day == today: self.add_record() self.tabWidget.addTab(self.tab_2, "") MainWindow.setCentralWidget(self.centralwidget) self.statusbar = QtWidgets.QStatusBar(MainWindow) self.statusbar.setObjectName("statusbar") MainWindow.setStatusBar(self.statusbar) MainWindow.setWindowIcon(QtGui.QIcon('label.ico')) self.retranslateUi(MainWindow) self.tabWidget.setCurrentIndex(1) QtCore.QMetaObject.connectSlotsByName(MainWindow) def devices_table_UI(self): conn = sqlite3.connect("database.db") c = conn.cursor() c.execute( "CREATE TABLE IF NOT EXISTS devices(id INTEGER PRIMARY KEY AUTOINCREMENT, mac_address TEXT, os_version TEXT, architecture TEXT, locale TEXT, timezone TEXT, last_update TEXT)" ) c.close() self.devices_model = QSqlRelationalTableModel(db=createConnection()) self.devices_model.setTable('devices') self.devices_model.select() # self.drivers_table.setEditTriggers(QAbstractItemView.CurrentChanged) self.devices_table.setModel(self.devices_model) self.devices_table.hideColumn(self.devices_model.fieldIndex('id')) # Делаем ресайз колонок по содержимому self.devices_table.resizeColumnsToContents() def add_record(self): rec = self.devices_model.record() data = [ get_mac(), # mac-address platform.platform(), platform.machine(), locale.getdefaultlocale()[0], str(timezone / 3600.0), strftime("%Y-%m-%d %H:%M:%S", gmtime()) ] for i in range(len(data)): rec.setValue(rec.field(i + 1).name(), data[i]) self.devices_model.insertRecord(-1, rec) self.devices_model.submit() self.devices_model.select() self.devices_table.resizeColumnsToContents() def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "Polydroid Desktop")) self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "AdminSite")) self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "Desktop DB"))
class QmyMainWindow(QtWidgets.QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.setCentralWidget(self.ui.tableView) self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(100) def __getFieldNames(self): emptyRec = self.tabModel.record() self.fldNum = {} for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): self.tabModel = QSqlRelationalTableModel(self, self.DB) self.tabModel.setTable("studInfo") self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit) self.tabModel.setSort(self.tabModel.fieldIndex("studID"), Qt.AscendingOrder) if (self.tabModel.select() == False): QMessageBox.critical( self, "错误信息", "打开数据表错误,错误信息\n" + self.tabModel.lastError().text()) return self.__getFieldNames() self.tabModel.setHeaderData(self.fldNum["studID"], Qt.Horizontal, "学号") self.tabModel.setHeaderData(self.fldNum["name"], Qt.Horizontal, "姓名") self.tabModel.setHeaderData(self.fldNum["gender"], Qt.Horizontal, "性别") self.tabModel.setHeaderData(self.fldNum["departID"], Qt.Horizontal, "学院") self.tabModel.setHeaderData(self.fldNum["majorID"], Qt.Horizontal, "专业") self.tabModel.setRelation( self.fldNum["departID"], QSqlRelation("departments", "departID", "department")) self.tabModel.setRelation(self.fldNum["majorID"], QSqlRelation("majors", "majorID", "major")) self.selModel = QItemSelectionModel(self.tabModel) self.selModel.currentChanged.connect(self.do_currentChanged) self.ui.tableView.setModel(self.tabModel) self.ui.tableView.setSelectionModel(self.selModel) delgate = QSqlRelationalDelegate(self.ui.tableView) self.ui.tableView.setItemDelegate(delgate) self.tabModel.select() self.ui.actOpenDB.setEnabled(False) self.ui.actRecAppend.setEnabled(True) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actFields.setEnabled(True) @pyqtSlot() def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return self.DB = QSqlDatabase.addDatabase("QSQLITE") self.DB.setDatabaseName(dbFilename) if self.DB.open(): self.__openTable() else: QMessageBox.warning(self, "错误", "打开数据库失败了") @pyqtSlot() def on_actSubmit_triggered(self): res = self.tabModel.submitAll() if (res == False): QMessageBox.information( self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text()) else: self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() def on_actRevert_triggered(self): self.tabModel.revertAll() self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() def on_actRecInsert_triggered(self): curIndex = self.ui.tableView.currentIndex() self.tabModel.insertRow(curIndex.row(), QModelIndex()) self.selModel.clearSelection() self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) @pyqtSlot() def on_actRecDelete_triggered(self): curIndex = self.selModel.currentIndex() self.tabModel.removeRow(curIndex.row()) @pyqtSlot() def on_actFields_triggered(self): emptyRec = self.tabModel.record() str = '' for i in range(emptyRec.count()): str = str + emptyRec.fieldName(i) + '\n' QMessageBox.information(self, "所有字段名", str) def do_currentChanged(self, current, previous): self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) self.ui.actRevert.setEnabled(self.tabModel.isDirty())
class 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 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 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 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 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
# соединяемся с БД CONN = QSqlDatabase.addDatabase('QSQLITE') CONN.setDatabaseName('test.sqlite3') CONN.open() # модель-связанная таблица R_T_MODEL = QSqlRelationalTableModel(parent=W_OBJ) R_T_MODEL.setEditStrategy(QSqlTableModel.OnManualSubmit) R_T_MODEL.setTable('product') R_T_MODEL.setSort(1, QtCore.Qt.AscendingOrder) # Задаем для поля категории связь с таблицей списка категорий R_T_MODEL.setRelation(1, QSqlRelation('category', 'id', 'name')) # 'category', 'id', 'category' (имя первичной таблицы, # имя поля первичного ключа, имя поля, выводящегося на экран) R_T_MODEL.select() R_T_MODEL.setHeaderData(1, QtCore.Qt.Horizontal, 'Категория товара') R_T_MODEL.setHeaderData(2, QtCore.Qt.Horizontal, 'Название товара') R_T_MODEL.dataChanged.connect(R_T_MODEL.submitAll) # Размещаем объекты VBOX_OBJ = QVBoxLayout() TV_OBJ = QTableView() TV_OBJ.setModel(R_T_MODEL) TV_OBJ.setItemDelegateForColumn(1, QSqlRelationalDelegate(TV_OBJ)) TV_OBJ.hideColumn(0) TV_OBJ.setColumnWidth(1, 150) TV_OBJ.setColumnWidth(2, 150) VBOX_OBJ.addWidget(TV_OBJ) # Добавляем кнопки
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 SurveyTableWidget(SurveyTableView): combineSignal = pyqtSignal() addSurveySignal = pyqtSignal() editSurveySignal = pyqtSignal() loadSurveyAudioSignal = pyqtSignal() selectionChangeSignal = pyqtSignal() def __init__(self): super().__init__() # init table model TODO: make vars private self.surveyTableModel = QSqlRelationalTableModel() self.surveyTableModel.setTable(R.tableName) self.surveyTableModel.setEditStrategy(QSqlTableModel.OnRowChange) self.surveyTableModel.select() self.surveyTableView.setModel(self.surveyTableModel) self.surveyTableSelectionModel = self.surveyTableView.selectionModel() self.surveyTableSelectionModel.selectionChanged.connect( self.onSurveySelectionChange) self.surveyTableView.horizontalHeader().sortIndicatorChanged.connect( self.onSurveySelectionChange) # TODO # connect buttons self.addSurveyButton.clicked.connect(self.addSurveySignal.emit) self.editSurveyButton.clicked.connect(self.editSurveySignal.emit) self.deleteSurveyButton.clicked.connect(self.deleteSurveyButtonAction) self.loadAudioButton.clicked.connect(self.loadSurveyAudioSignal.emit) self.combineButton.clicked.connect(self.combineSignal.emit) # info to populate AddSurveyDialog def getDialogArgs(self): recorderIds = [] query = QSqlQuery("SELECT DISTINCT recorder_id FROM survey") while query.next(): print(query.value(0)) kwargs = {"recorderIds": recorderIds} return kwargs def select(self, index): self.surveyTableView.selectRow(index) def sortByKey(self): self.surveyTableView.sortByColumn(0, Qt.DescendingOrder) # TODO: keep selection after sort def onSort(self): pass def getSelectedRows(self): return self.surveyTableSelectionModel.selectedRows() def onPlaylistMediaChange(self, hasMedia): self.addSurveyButton.setEnabled(hasMedia) self.selectionChangeSignal.emit() def getSelectedData(self, row=0, column=Column.SURVEY_DATETIME): key = self.getSelectedRows()[row] return self.surveyTableModel.data(key.sibling(key.row(), column.value)) def getDataFromKey(self, keyIndex, column=Column.SURVEY_DATETIME): return self.surveyTableModel.data( keyIndex.sibling(keyIndex.row(), column.value)) def getSelectedKeys(self): list = [] for row in self.getSelectedRows(): list.append(str(self.surveyTableModel.data(row))) return list def singlePathSelected(self): if len(self.getSelectedRows()) > 0: firstPath = self.getDataFromKey(self.getSelectedRows()[0], Column.FILE) for row in self.getSelectedRows(): # self.getSelectedRows(): if self.getDataFromKey(row, Column.FILE) != firstPath: return None return firstPath return None def addRecord(self, record): sqlRecord = record.getQSQLRecord(self.surveyTableModel.record()) self.surveyTableModel.insertRecord(0, sqlRecord) self.sortByKey() self.select(0) QApplication.processEvents() # allow for selection highlight def editRecord(self, record): index = self.getSelectedRows()[0] record.editData(model=self.surveyTableModel, index=index) QApplication.processEvents() # TODO maybe remove @pyqtSlot() def deleteSurveyButtonAction(self): for row in self.getSelectedRows(): self.surveyTableModel.removeRow(row.row()) self.surveyTableSelectionModel.clearSelection() self.surveyTableModel.select() QApplication.processEvents() @pyqtSlot() def onSurveySelectionChange(self): # enable/disable buttons then signal MainWindow selectionCount = len(self.getSelectedRows()) self.deleteSurveyButton.setEnabled(selectionCount > 0) self.loadAudioButton.setEnabled(selectionCount > 0) self.editSurveyButton.setEnabled(selectionCount == 1) self.combineButton.setEnabled( selectionCount > 1 and self.singlePathSelected() is not None) self.selectionChangeSignal.emit()
def init_best_before(self): model = QSqlRelationalTableModel() model.setTable("best_before") model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) model.select() #Nutrition table needs to be populated since otherwise only 256 rows are read #And all inserts with ndbno > 100169 fail since they aren't found in nutrition #table nutri_model = model.relationModel(1) while nutri_model.canFetchMore(): nutri_model.fetchMore() self.bb_model = model nutri_model = self.lv_keys.model() #nutri_model = QSqlTableModel() #nutri_model.setTable("nutrition") ##nutri_model.setRelation(2, QSqlRelation('nutrition', 'ndbno', 'desc')) #nutri_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) #nutri_model.setSort(1,Qt.AscendingOrder) #nutri_model.select() self.cb_bb_item.setModel(nutri_model) self.cb_bb_item.setModelColumn(0) self.best_before_model = model self.tv_best_before.setModel(model) self.tv_best_before.setSortingEnabled(True) self.tv_best_before.sortByColumn(2, Qt.AscendingOrder) self.tv_best_before.setItemDelegate(QSqlRelationalDelegate(self.tv_best_before)) self.tv_best_before.show() #From Price self.cb_price_item.setModel(nutri_model) self.cb_price_item.setModelColumn(0) #From Tag self.cb_item_tag.setModel(nutri_model) self.cb_item_tag.setModelColumn(0) """Updates Best before table""" def update_bb(): print ("Updating Best Before") if not self.bb_model.submitAll(): QMessageBox.critical(None, "Error updating Best Before", "Couldn't update model: " + self.bb_model.lastError().text()) """Adds new data to best before table update_bb also needs to be called""" def add_bb(): print("Adding to BB") ndbno = self._get_selected_ndbno(self.cb_bb_item.model() \ .record(self.cb_bb_item.currentIndex())) print ("IDX:", self.cb_bb_item.currentIndex(), ndbno) row = self.bb_model.rowCount() self.bb_model.insertRow(row) #print ("NDBNO INDEX:", self.bb_model.fieldIndex("desc")) r = self.bb_model.record() #for i in range(r.count()): #print ("{} => {}".format(i,r.fieldName(i))) #for i in range(100000,100194): #out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, #self.bb_model.fieldIndex("desc")), i, #Qt.EditRole) #print ("{}? = {}".format(out_ndbno, i)) out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("desc")), ndbno, Qt.EditRole) out_time = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("time")), self.de_bb.date(), Qt.EditRole) print ("NDBNO:", out_ndbno, "TIME:", out_time) self.update_bb = update_bb self.add_bb = add_bb self.buttonBox_3.button(QDialogButtonBox.SaveAll).clicked.connect(self.update_bb) self.buttonBox_3.button(QDialogButtonBox.Apply).clicked.connect(self.add_bb)
class 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 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)
def __init__(self, parent=None): super().__init__(parent) horizontalLayout = QHBoxLayout() self.dayView = QTableView() self.dayView.setFrameShape(QFrame.Box) self.dayView.horizontalHeader().setStretchLastSection(True) self.dayView.verticalHeader().setVisible(False) horizontalLayout.addWidget(self.dayView) verticalLayout = QVBoxLayout() self.calendarWidget = QCalendarWidget() self.calendarWidget.setMinimumSize(QSize(250, 200)) self.calendarWidget.setMaximumSize(QSize(250, 200)) self.calendarWidget.setMinimumDate(QDate(2017, 1, 1)) self.calendarWidget.setMaximumDate(QDate(2030, 1, 1)) self.calendarWidget.selectionChanged.connect(self.dataChange) self.calendarWidget.setSelectedDate(QDate.currentDate()) verticalLayout.addWidget(self.calendarWidget) titleFV = QLabel('Food View') verticalLayout.addWidget(titleFV) self.filterLine = QLineEdit() self.filterLine.setMaximumSize(QSize(200, 25)) self.filterLine.textChanged.connect(self.filterChange) buttonAdd = QPushButton(QIcon("images/add.png"), '', None) buttonAdd.setMaximumSize(QSize(20, 30)) buttonAdd.clicked.connect(self.addFood) buttonDell = QPushButton(QIcon("images/del.png"), '', None) buttonDell.setMaximumSize(QSize(20, 30)) buttonDell.clicked.connect(self.delFood) lineEditLayout = QHBoxLayout() lineEditLayout.addWidget(self.filterLine) lineEditLayout.addWidget(buttonAdd) lineEditLayout.addWidget(buttonDell) verticalLayout.addLayout(lineEditLayout) self.foodView = QTableView() self.foodView.setMinimumSize(QSize(0, 0)) self.foodView.setMaximumSize(QSize(250, 1000)) self.foodView.verticalHeader().setVisible(False) self.foodView.horizontalHeader().setStretchLastSection(True) verticalLayout.addWidget(self.foodView) horizontalLayout.addLayout(verticalLayout) self.setLayout(horizontalLayout) model_in = QSqlRelationalTableModel() model_in.setEditStrategy(QSqlTableModel.OnFieldChange) model_in.setTable("intake_food") id_food = model_in.fieldIndex("id_food") date = model_in.fieldIndex("food_date") mass = model_in.fieldIndex("mass") # Set model, hide ID column model_in.setRelation(id_food, QSqlRelation("food", "id", "name")) model_in.setHeaderData(id_food, Qt.Horizontal, "Food") model_in.setHeaderData(date, Qt.Horizontal, "Date") model_in.setHeaderData(mass, Qt.Horizontal, "Mass") if not model_in.select(): self.showError(model_in.lastError()) return self.proxyModel_in = QSortFilterProxyModel() self.proxyModel_in.setSourceModel(model_in) self.proxyModel_in.setFilterKeyColumn(2) self.dayView.setItemDelegate(FlipProxyDelegate()) self.dayView.setModel(self.proxyModel_in) self.dayView.setColumnHidden(0, True) self.dayView.setColumnHidden(2, True) self.dayView.setSelectionMode(QAbstractItemView.SingleSelection) self.dayView.setContextMenuPolicy(Qt.CustomContextMenu) self.dayView.customContextMenuRequested.connect(self.ShowContextMenu) # filter day food by calendar widget self.dataChange() self.model_f = QSqlRelationalTableModel() self.model_f.setEditStrategy(QSqlTableModel.OnFieldChange) self.model_f.setTable("food") self.model_f.setHeaderData(1, Qt.Horizontal, "Food") self.model_f.setHeaderData(2, Qt.Horizontal, "Rate") if not self.model_f.select(): self.showError(self.model_f.lastError()) return self.proxyModel_f = QSortFilterProxyModel() self.proxyModel_f.setSourceModel(self.model_f) self.proxyModel_f.setFilterKeyColumn(1) self.foodView.setModel(self.proxyModel_f) self.foodView.setColumnHidden(0, True) self.foodView.setSelectionMode(QAbstractItemView.SingleSelection) self.foodView.setColumnWidth(1, 150) self.foodView.setColumnWidth(2, 90)
class 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 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 Shtat(QMainWindow): """ Класс, реализующий окно для работы со штатным расписанием в виде таблицы """ con = QSqlDatabase.addDatabase('QSQLITE') con.setDatabaseName(settings.db_name) def __init__(self, parent: any = None) -> None: """ Функция инициализации окна штатного расписания :param parent: Родительский виджет (окно) """ super().__init__(parent) self.filters = ['Номер', 'Подразделение', 'Должность', 'Количество', 'Тариф', 'Оклад', 'ФИО', 'Декрет', 'История', 'Оклад замещающего работника', 'Вид позиции', ] self.setObjectName("ShtatWindow") self.resize(1380, 886) self.centralwidget = QtWidgets.QWidget(self) self.centralwidget.setObjectName("centralwidget") self.view = QTableView(self.centralwidget) self.view.setGeometry(QRect(0, 0, 1381, 821)) self.view.setObjectName("shtat_table_view") self.view.setSortingEnabled(True) self.filter_combo_box = QtWidgets.QComboBox(self.centralwidget) self.filter_combo_box.setGeometry(QRect(250, 840, 231, 31)) self.filter_combo_box.setObjectName("filter_combo_box") self.filter_input = QtWidgets.QLineEdit(self.centralwidget) self.filter_input.setGeometry(QRect(500, 840, 281, 31)) self.filter_input.setObjectName("filter_input") self.filter_label = QtWidgets.QLabel(self.centralwidget) self.filter_label.setGeometry(QRect(10, 840, 221, 31)) font = QFont() font.setFamily("Times New Roman") font.setPointSize(12) self.filter_label.setFont(font) self.filter_label.setObjectName("filter_label") self.filter_button = QtWidgets.QPushButton(self.centralwidget) self.filter_button.setGeometry(QRect(800, 840, 171, 31)) self.save_shtat_button = QtWidgets.QPushButton(self.centralwidget) self.save_shtat_button.setGeometry(QRect(1000, 840, 225, 31)) self.filter_button.setFont(font) self.save_shtat_button.setFont(font) self.filter_button.setObjectName("filter_button") self.setCentralWidget(self.centralwidget) _translate = QCoreApplication.translate self.setWindowTitle(_translate("ShtatWindow", "Работа со штатным расписанием")) self.filter_label.setText(_translate("ShtatWindow", "Фильтровать по столбцу:")) self.filter_button.setText(_translate("ShtatWindow", "Поиск")) self.save_shtat_button.setText(_translate("ShtatWindow", "Выгрузить штатное расписание")) self.model = QSqlRelationalTableModel(self.view) self.model.setTable('salaries') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation("departments", "code", "department")) self.model.setRelation(10, QSqlRelation("positions", "position_code", "position_name")) for i in range(0, len(self.filters)): self.model.setHeaderData(i, Qt.Horizontal, self.filters[i]) self.model.select() self.proxyModelContact = QSortFilterProxyModel(self) self.proxyModelContact.setSourceModel(self.model) self.view.setModel(self.proxyModelContact) self.view.resizeColumnsToContents() self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.filter_combo_box.addItems(self.filters) self.filter_button.clicked.connect(self.use_filter_button) self.save_shtat_button.clicked.connect(self.use_save_shtat_button) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) def use_filter_button(self) -> None: """ Функция кнопки "Поиск". Считывает паттерн из поля фильтра и применяет его к столбцу, выбранного в комбо-боксе фильтра :return: None """ self.proxyModelContact.setFilterKeyColumn(self.filters.index(self.filter_combo_box.currentText())) self.proxyModelContact.setFilterRegExp(self.filter_input.text()) @staticmethod def use_save_shtat_button() -> None: """ Функция кнопки "Выгрузить штатное расписание". Выгружает все штатное расписание в файл формата Excel :return: None """ file = QtWidgets.QFileDialog.getSaveFileName()[0] saver = FullShtatToExcel(file if file.endswith(".xlsx") else f"{file}.xlsx") saver.full_shtat_to_excel()
class StampTableWidget(StampTableView): addStampSignal = pyqtSignal(str) # deleteStampSignal = pyqtSignal() surveyModeSignal = pyqtSignal(bool) def __init__(self): super().__init__() self.hasSelection = False # todo self.surveyDatetime = None # init table model self.stampTableModel = QSqlRelationalTableModel() self.stampTableModel.setTable(R.tableName) self.stampTableModel.setEditStrategy(QSqlTableModel.OnFieldChange) self.stampTableModel.setFilter("survey_datetime = None") self.stampTableModel.select() self.stampTableView.setModel(self.stampTableModel) self.stampTableSelectionModel = self.stampTableView.selectionModel() self.stampTableSelectionModel.selectionChanged.connect( self.onStampSelectionChange) # connect buttons self.addStampButton.clicked.connect( lambda ignore, key="-": self.addStampSignal.emit(key)) self.deleteStampButton.clicked.connect(self.deleteButtonAction) self.hotkeyButton.clicked.connect(self.enableHotkeys) # self.recordButton.clicked.connect(self.surveyMode) # hotkeys self.hotkeys = self.initHotkeys() # copy selection to clipboard self.installEventFilter(self) def eventFilter(self, source, event): if (event.type() == QEvent.KeyPress and event.matches(QKeySequence.Copy)): self.copySelection() return True return super().eventFilter(source, event) def copySelection(self): selection = self.stampTableView.selectedIndexes() if selection: rows = sorted(index.row() for index in selection) columns = sorted(index.column() for index in selection) rowcount = rows[-1] - rows[0] + 1 colcount = columns[-1] - columns[0] + 1 table = [[''] * colcount for _ in range(rowcount)] for index in selection: row = index.row() - rows[0] column = index.column() - columns[0] table[row][column] = index.data() stream = io.StringIO() csv.writer(stream).writerows(table) QApplication.clipboard().setText(stream.getvalue()) def initHotkeys(self): keyList = [] shortcut = QShortcut( QKeySequence(Qt.Key_Space, QKeySequence.NativeText), self) keyList.append(shortcut) shortcut.activated.connect( lambda key="-": self.addStampSignal.emit(key)) for i in range(0, 10): print(i) shortcut = QShortcut(QKeySequence(str(i), QKeySequence.NativeText), self) keyList.append(shortcut) shortcut.activated.connect( lambda key=str(i): self.addStampSignal.emit(key)) return keyList def enableHotkeys(self, isClicked): for shortcut in self.hotkeys: shortcut.setEnabled(isClicked) def onDeselection(self): self.enableHotkeys(False) self.hotkeyButton.setChecked(False) # TODO delete def testHotKey(self, key): print("test") print(key) # TODO maybe repaint def loadSurveyStamps(self, keys): print("LSS") self.surveyDatetime = keys[0] filter = self.createFilter(keys) self.stampTableModel.setFilter(filter) self.stampTableModel.select() print("survey_datetime = " + self.surveyDatetime) QApplication.processEvents() self.repaint() def createFilter(self, keys): filter = "survey_datetime = \"" + self.surveyDatetime + "\"" for i in range(1, len(keys)): filter = filter + " OR survey_datetime = \"" + keys[i] + "\"" print(filter) return filter @pyqtSlot() def deleteButtonAction(self): for row in self.stampTableSelectionModel.selectedRows(): self.stampTableModel.removeRow(row.row()) self.stampTableSelectionModel.clearSelection() self.stampTableModel.select() QApplication.processEvents() def clearSurveyStamps(self): self.surveyDatetime = None # self.stampTableModel.setFilter("") # TODO will this work? self.stampTableModel.setFilter("survey_datetime = None") print("survey_datetime = None") def addStamp(self, stamp, key="-"): stampRecord = StampRecord(miliseconds=stamp, surveyDatetime=self.surveyDatetime, label=key, note="") sqlRecord = stampRecord.getQSQLRecord(self.stampTableModel.record()) self.stampTableModel.insertRecord(0, sqlRecord) # self.surveyWidget.sortByKey() # self.surveyWidget.select(0) QApplication.processEvents() # allow for selection highlight def addRecord(self, stampRecord): sqlRecord = stampRecord.getQSQLRecord(self.stampTableModel.record()) print(self.stampTableModel.insertRowIntoTable(sqlRecord)) # self.repaint() # TODO # self.surveyModeSignal.emit(modeOn) # @pyqtSlot(bool) # def enableSurvey(self, canSurvey): # print(canSurvey) # self.recordButton.setEnabled(canSurvey) @pyqtSlot() def testButtonAction(self): print("Test") @pyqtSlot(bool) def surveyMode(self, modeOn): self.addStampButton.setEnabled(modeOn) self.hotkeyButton.setEnabled(modeOn) self.enableHotkeys(self.hotkeyButton.isChecked()) @pyqtSlot(QItemSelection) def onStampSelectionChange(self, selection): self.hasSelection = selection.count() > 0 self.deleteStampButton.setEnabled(self.hasSelection) @pyqtSlot(bool) def enableStamps(self, modeOn): self.addStampButton.setEnabled(modeOn) self.hotkeyButton.setEnabled(modeOn) if not modeOn and self.hotkeyButton.isChecked(): self.hotkeyButton.setChecked(False) self.enableHotkeys(False)
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()}')