def write_over(self): """ Write over old record with the user input via sqlite. The old record is located with self.unregisgerIdDict[self.unregisterIdIndex]).""" print('write_over') con, cur = connect._get_connection() sqlstr = ('update hvhnonc_out set {settings} where ID=?') d = {} d['settings'] = '' fields = [ 'in_ID', 'unregister_date', 'amount', 'reason', 'unregister_place', 'unregister_remark' ] params = [ str(self.iid), self.unregister_date.date().toPyDate(), self.unregister_amount.text(), self.reason.currentText(), self.unregister_place.currentText(), self.unregister_remark.text() ] for f in fields: d['settings'] += '{} = ?, '.format(f) d['settings'] = d['settings'][:-2] params += (str(self.unregisgerIdDict[self.unregisterIdIndex]), ) cur.execute(sqlstr.format(**d), params) con.commit() con.close() self.update_cache() QtWidgets.QMessageBox.information(self, '成功', '已覆蓋一筆資料')
def load_options(self): """load database data as combobox options""" con, cur = connect._get_connection() for name, cb in self.get_comboboxes(): if name == 'category': sqlstr = 'select description from hvhnonc_category' cur.execute(sqlstr) cb.clear() for row in cur: cb.addItem(row[0]) cb.setEditText('') elif name in ('subcategory', 'name'): pass else: sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID = 0 ' 'and this_value = "" ' 'and change_ID = ?') params = (connect.get_field_id(name), ) cur.execute(sqlstr, params) rows = cur.fetchall() try: cb.clear() for row in rows: cb.addItem(row[0]) except Exception as e: print(e) cb.setEditText('') con.close()
def correct_hash(self, credential: Dict[str, str]) -> bool: # The symbol <= means 'is subset of' if not set(('username', 'password')) <= credential.keys(): return False (username, password) = (credential.get('username'), credential.get('password')) con, cursor = connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ("select {hash}, {salt} from {table} where {username}=?;") sqlstr = sqlstr.format( **{ 'hash': 'hash_SHA256', 'salt': 'salt', 'table': 'hvhnonc_users', 'username': '******' }) params = (username, ) result = cursor.execute(sqlstr, params).fetchone() con.close() try: dbHash = result['hash_SHA256'] dbSalt = result['salt'] except: return False # SHA256 hash the dbSalt + password sha256 = hashlib.sha256() data = dbSalt + password sha256.update(data.encode("utf-8")) localHash = sha256.hexdigest() return localHash == dbHash
def on_name_changed(self): # update unit, brand, spec from hvhnonc_cache con, cursor= connect._get_connection() sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID=? ' 'and this_value=? ' 'and change_ID=?') params = [connect.get_field_id('name'), self.name.currentText()] # unit p = params + [connect.get_field_id('unit'),] cursor.execute(sqlstr, p) rows = cursor.fetchall() self.unit.clear() if rows: self.unit.addItems([row[0] for row in rows]) # brand p = params + [connect.get_field_id('brand'),] cursor.execute(sqlstr, p) rows = cursor.fetchall() self.brand.clear() if rows: self.brand.addItems([row[0] for row in rows]) # spec p = params + [connect.get_field_id('spec'),] cursor.execute(sqlstr, p) rows = cursor.fetchall() self.spec.clear() if rows: self.spec.addItems([row[0] for row in rows]) con.close()
def fetch_options(self, d: Dict[str, QtWidgets.QComboBox]): con, cursor= connect._get_connection() for key, widget in d.items(): # constant choices if key == 'source': options = ['購置', '撥用', '贈送'] widget.addItems(options) continue # fetch options from hvhnonc_category elif key == 'category': sqlstr = ('select description ' 'from hvhnonc_category ' 'order by ID') cursor.execute(sqlstr) rows = cursor.fetchall() options = [row[0] for row in rows] widget.addItems(options) widget.clearEditText() # fetch options from hvhnonc_cache else: sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID=0 and ' 'change_ID=?') params = (connect.get_field_id(key),) cursor.execute(sqlstr, params) rows = cursor.fetchall() options = [row[0] for row in rows] widget.addItems(options) widget.clearEditText() con.close()
def update_field_cache(self): con, cursor= connect._get_connection() comboboxes = {k:w for k, w in self.__dict__.items() if isinstance(w, QtWidgets.QComboBox)} for k, w in comboboxes.items(): sqlstr = ('insert or ignore into ' 'hvhnonc_cache(this_ID, this_value, ' 'change_ID, change_value) ' 'values(?, ?, ?, ?);') if k in ('category', 'subcategory', 'source'): continue if k in ('name'): params = (connect.get_field_id('subcategory'), self.subcategory.currentText(), connect.get_field_id(k), w.currentText()) elif k in ('unit', 'brand', 'spec'): params = (connect.get_field_id('name'), self.name.currentText(), connect.get_field_id(k), w.currentText()) else: params = (0, '', connect.get_field_id(k), w.currentText()) cursor.execute(sqlstr, params) con.commit() con.close()
def get_id_dict(self): con, cursor= connect._get_connection() sqlstr = ('select ID from hvhnonc_in order by acquire_date;') cursor.execute(sqlstr) rows = cursor.fetchall() con.close() return {i: row[0] for i, row in enumerate(rows)}
def load_outRecord(self, oid: int): if oid == -1: self.clear_out_fields() return con, cursor = connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ('select * from hvhnonc_out where ID = ?') params = (oid, ) cursor.execute(sqlstr, params) row = cursor.fetchone() con.close() for k in row.keys(): if k == 'amount': w = getattr(self, 'unregister_amount') else: try: w = getattr(self, k) except: continue if isinstance(w, QtWidgets.QLineEdit): w.setText(str(row[k])) elif isinstance(w, QtWidgets.QComboBox): w.setEditText(str(row[k])) elif isinstance(w, QtWidgets.QDateEdit): (y, m, d) = map(int, row[k].split('-')) date = QtCore.QDate(y, m, d) w.setDate(date)
def fetch_from_database(d): """Returns a list of sqlite3.Row as data""" con, cur = connect._get_connection(useSQL3Row=True) sqlstr = ('select {columns} from {table} where {conditions}') replacements = {} # columns: object_ID, serial_ID, name, spec, unit, amount, price, # acquire_date, keep_year, keep_department, place, keeper replacements['columns'] = ', '.join(( 'ID', 'object_ID', 'serial_ID', 'name', 'spec', 'unit', 'amount', 'price', 'acquire_date', 'keep_year', 'keep_department', 'place', 'keeper')) # table: hvhnonc_in replacements['table'] = 'hvhnonc_in' # conditions: determined by d replacements['conditions'] = '' params = [] for k, v in d.items(): if 'date' in k: # date string tuple replacements['conditions'] += \ '({} between ? and ?) and '.format(k) params.extend(v) else: replacements['conditions'] += \ ('{0} like ? and '.format(k)) params.append('%' + v + '%') replacements['conditions'] += '1' # fill in the blanks sqlstr = sqlstr.format(**replacements) cur.execute(sqlstr, params) data = cur.fetchall() con.close() return data
def load_history_record(self, iid: int): con, cursor = connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ('select ' 'max(unregister_date) as last_unregister_date, ' 'count(*) as unregistered_count, ' 'sum(amount) as unregistered_amount ' 'from hvhnonc_out ' 'where in_ID=?' 'limit 1;') params = (iid, ) cursor.execute(sqlstr, params) row = cursor.fetchone() if not row or None in row: self.clear_history_record() return con.close() for k in row.keys(): try: w = getattr(self, k) except: continue if isinstance(w, QtWidgets.QLineEdit): w.setText(str(row[k])) elif isinstance(w, QtWidgets.QComboBox): w.setEditText(str(row[k])) elif isinstance(w, QtWidgets.QDateEdit): (y, m, d) = map(int, row[k].split('-')) date = QtCore.QDate(y, m, d) w.setDate(date)
def on_deleteBtn_clicked(self): if not self.isEnabled: return if self.idIndex == -1: QtWidgets.QMessageBox.critical(self, '錯誤', '不能刪除未存入的資料') return mb = QtWidgets.QMessageBox(self) mb.setWindowTitle('確認刪除') mb.setText('確定要刪除本筆: {} 嗎?'.format(self.name.currentText())) mb.addButton('取消', QtWidgets.QMessageBox.NoRole) mb.addButton('確定', QtWidgets.QMessageBox.YesRole) toDelete = mb.exec_() if toDelete: con, cursor= connect._get_connection() sqlstr = ('delete from hvhnonc_in where ID=?;') params = (self.idDict[self.idIndex],) cursor.execute(sqlstr, params) con.commit() con.close() msg = u'已刪除一筆資料: {}({})'.format( self.name.currentText(), self.idDict[self.idIndex]) QtWidgets.QMessageBox.information(self, u'成功', msg) else: return # reset form self.clear_all_fields() self.disable_all_fields()
def get_record(self, index: int): con, cursor= connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ('select * from hvhnonc_in where ID=?') params = (str(index),) cursor.execute(sqlstr, params) res = cursor.fetchone() con.close() return res
def get_inID(self, oid: int) -> int: con, cursor = connect._get_connection() sqlstr = ('select in_ID from hvhnonc_out where ID = ?') params = (oid, ) cursor.execute(sqlstr, params) row = cursor.fetchone() con.close() if row: return row[0] else: return -1
def fetch_from_database(d): """Returns a list of sqlite3.Row as data""" print(d) con, cur = connect._get_connection(useSQL3Row=True) con.set_trace_callback(print) sqlstr = ( 'select {columns} ' 'from {intable} as i ' 'inner join {outtable} as o ' 'on i.ID=o.in_ID ' 'and {conditions}') replacements = {} # columns: object_ID, serial_ID, name, spec, unit, # unregister amount, date, keep year, used year, reason, # remark replacements['columns'] = ( 'i.object_ID as object_ID, i.serial_ID as serial_ID, ' 'i.name as name, i.spec as spec, i.unit as unit, ' 'o.amount as amount, o.unregister_date as unregister_date, ' 'i.keep_year as keep_year, i.acquire_date as acquire_date, ' 'o.reason as reason, o.unregister_remark as remark') replacements['intable'] = 'hvhnonc_in' replacements['outtable'] = 'hvhnonc_out' # conditions: determined by d replacements['conditions'] = '' params = [] for k, v in d.items(): if 'date' in k: if k == 'acquire_date': # special case # date string tuple replacements['conditions'] += \ '(unregister_date between ? and ?) and ' params.extend(v) else: # date string tuple replacements['conditions'] += \ '({} between ? and ?) and '.format(k) params.extend(v) else: replacements['conditions'] += \ ('{0} like ? and '.format(k)) params.append('%' + v + '%') replacements['conditions'] += '1' # fill in the blanks sqlstr = sqlstr.format(**replacements) print(sqlstr) cur.execute(sqlstr, params) data = cur.fetchall() con.close() for row in data: print(', '.join([str(row[k]) for k in row.keys()])) return data
def get_id_dict(self, fromWhere: str): where = '' if fromWhere == 'register': where = 'hvhnonc_in' dateField = 'acquire_date' if fromWhere == 'unregister': where = 'hvhnonc_out' dateField = 'unregister_date' con, cursor = connect._get_connection() sqlstr = ('select ID from {0} order by {1};'.format(where, dateField)) cursor.execute(sqlstr) rows = cursor.fetchall() con.close() return {i: row[0] for i, row in enumerate(rows)}
def update_add_list_id(data): """Write add_list_ID to database. For now we use document creation date(today).""" today = datetime.date.today() today_str_zfill = (str(today.year), str(today.month).zfill(2), str(today.day).zfill(2)) string_today = '-'.join(today_str_zfill) con, cur = connect._get_connection() for row in data: id = row['ID'] sqlstr = ('update hvhnonc_in set add_list_ID = ? where ID = ?') params = (string_today, id) cur.execute(sqlstr, params) con.commit() con.close()
def fetch_details(d): """The rows of the details for monthly report. group by category, sort by purchase date ascended.""" con, cur = connect._get_connection(useSQL3Row=True) # get all categories sqlstr = ('select description from hvhnonc_category;') cur.execute(sqlstr) categories = cur.fetchall() # a list of sqlite3.Rows categories = [x['description'] for x in categories if x] # preconstruct params for sql conditions, tempParams = get_sql_conditions(d) magicMinDate = datetime.date(*get_year_month_int(d), 1) magicMaxDate = magicMinDate.replace( day=28) + datetime.timedelta(days=4) magicMaxDate -= datetime.timedelta(days=magicMaxDate.day) params = [None, magicMinDate, magicMaxDate] + list(tempParams) # for every category, fetch in data and out data order by pchd for category in categories: # in_data union all out_data inner join in data sqlstr = ( 'select * ' 'from (' 'select ' 'category, name, brand, spec, unit, price, ' 'amount as register_amount, ' '0 as unregister_amount, purchase_date, ' 'acquire_date as date, place, remark ' 'from hvhnonc_in ' 'union all ' 'select ' 'category, name, brand, spec, unit, price, ' '0 as register_amount, ' 'hvhnonc_out.amount as unregister_amount, ' 'purchase_date, unregister_date as date, place, remark ' 'from hvhnonc_out ' 'inner join hvhnonc_in ' 'on hvhnonc_out.in_ID = hvhnonc_in.ID)' 'where category = ?' 'and date between ? and ? ' 'and {conditions} ' 'order by purchase_date asc;') params[0] = category cur.execute(sqlstr.format(**conditions), params) ret = cur.fetchall() if ret: yield category, ret con.close()
def fetch_detail_data(data): """Get the required details of the reduced id list.""" # fetch detailed delta data delta_details = [] con, cur = connect._get_connection(useSQL3Row=True) sqlstr = ( 'select ' 'ID, add_list_ID, object_ID, serial_ID, name, spec, ' 'unit, price, acquire_date, keep_year, place, ' 'keep_department, keeper ' 'from hvhnonc_in ' 'where ID = ?') for id in data.keys(): cur.execute(sqlstr, (id,)) delta_details += cur.fetchall() return delta_details
def update_field_cache(self): con, cursor= connect._get_connection() comboboxes = {k:w for k, w in self.__dict__.items() if isinstance(w, QtWidgets.QComboBox)} sqlstr = ('insert or ignore into ' 'hvhnonc_cache(this_ID, this_value, ' 'change_ID, change_value) ' 'values(?, ?, ?, ?);') for k, w in comboboxes.items(): if k in ('query',): params = (0, '', connect.get_field_id(k), w.currentText()) cursor.execute(sqlstr, params) con.commit() con.close()
def on_category_changed(self): # update subcategory con, cursor= connect._get_connection() sqlstr = ('select description ' 'from hvhnonc_subcategory ' 'where parent_ID=(' 'select ID ' 'from hvhnonc_category ' 'where description=?);') params = (self.category.currentText(),) cursor.execute(sqlstr, params) rows = cursor.fetchall() con.close() options = [row[0] for row in rows] self.subcategory.clear() self.subcategory.addItems(options)
def load_cache(self): # enabled comboboxes: enabledComboboxes = ['reason', 'unregister_place'] con, cur = connect._get_connection() sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID = 0 ' 'and this_value = "" ' 'and change_ID = ?;') for cb in enabledComboboxes: params = (connect.get_field_id(cb), ) cur.execute(sqlstr, params) rows = cur.fetchall() for row in rows: getattr(self, cb).addItem(row[0]) getattr(self, cb).setCurrentText('') con.close()
def fetch_options(self, widget): widgets = {k: w for k, w in self.__dict__.items() if isinstance(w, QtWidgets.QComboBox)} con, cursor = connect._get_connection() for k, w in widgets.items(): sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID=0 ' 'and change_ID=? ' 'order by rowid desc;') params = (connect.get_field_id(k),) cursor.execute(sqlstr, params) rows = cursor.fetchall() options = [row[0] for row in rows] w.clear() w.addItems(options) w.clearEditText() con.close()
def write_over(self, id: str): con, cursor= connect._get_connection() sqlstr = ('replace into hvhnonc_in(' 'ID, object_ID, serial_ID, ' 'category, subcategory, ' 'name, brand, spec, unit, ' 'purchase_date, acquire_date, ' 'price, amount, place, ' 'keep_year, source, ' 'keep_department, use_department, ' 'keeper, remark) ' 'values({});') params = (self.idDict[self.idIndex], self.object_ID.text(), self.serial_ID.text(), self.category.currentText(), self.subcategory.currentText(), self.name.currentText(), self.brand.currentText(), self.spec.currentText(), self.unit.currentText(), self.purchase_date.date().toPyDate(), self.acquire_date.date().toPyDate(), self.price.text(), self.amount.text(), self.place.currentText(), self.keep_year.text(), self.source.currentText(), self.keep_department.currentText(), self.use_department.currentText(), self.keeper.currentText(), self.remark.currentText()) questionmarks = ('?, ' * len(params))[:-2] cursor.execute(sqlstr.format(questionmarks), params) con.commit() con.close() self.update_field_cache() # show success messagebox msg = u'已複寫一筆資料: {}({})'.format( self.name.currentText(), self.idDict[self.idIndex]) QtWidgets.QMessageBox.information(self, u'成功', msg) # reset form self.clear_all_fields() self.disable_all_fields()
def on_subcategory_changed(self): # update name from hvhnonc_cache con, cursor= connect._get_connection() sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID=? ' 'and this_value=? ' 'and change_ID=?') params = [ connect.get_field_id('subcategory'), self.subcategory.currentText(), connect.get_field_id('name')] cursor.execute(sqlstr, params) rows = cursor.fetchall() con.close() self.name.clear() if rows: self.name.addItems([row[0] for row in rows]) self.name.clearEditText()
def update_cache(self): """ Update the user input into cache. """ con, cur = connect._get_connection() sqlstr = ('insert or ignore into hvhnonc_cache ({columns}) ' 'values ({questionmarks})') d = {} columns = ['this_ID', 'this_value', 'change_ID', 'change_value'] d['columns'] = (', '.join(columns)) d['questionmarks'] = ('?, ' * len(columns))[:-2] widgetsToUpdate = ['reason', 'unregister_place'] for w in widgetsToUpdate: params = [ '0', '', connect.get_field_id(w), getattr(self, w).currentText() ] cur.execute(sqlstr.format(**d), params) con.commit() con.close()
def fetch_from_database(d): """Fetch data from database for full report construction. return: 2 lists if sqlite3.Row: in and out. """ # first fetch: all rows where conditions are met, # but only type, amount and ID con, cur = connect._get_connection(useSQL3Row=True) conditions, params = forge_conditions_and_params(d) sqlstr = ( 'select ' "'in' as type, ID, amount " 'from hvhnonc_in ' 'where acquire_date between ? and ? ' 'and {conditions} ' 'union all ' 'select ' "'out' as type, hvhnonc_in.ID as ID, " 'hvhnonc_out.amount as amount ' 'from hvhnonc_out ' 'inner join hvhnonc_in ' 'on hvhnonc_out.in_ID = hvhnonc_in.ID ' 'and unregister_date between ? and ? ' 'and {conditions}') try: _, date_max = d['acquire_date'] magic_date = (str(date_max.year), str(date_max.month).zfill(2), str(date_max.day).zfill(2)) except KeyError: date_temp = datetime.date.today() magic_date = (str(date_temp.year), str(date_temp.month).zfill(2), str(date_temp.day).zfill(2)) magic_date = '-'.join(magic_date) params.insert(0, '1910-12-31') params.insert(1, magic_date) params.insert(2, '1910-12-31') params.insert(3, magic_date) cur.execute(sqlstr.format(**conditions), params) data = cur.fetchall() return data
def on_category_changed(self): """When category field is changed, load subcategory options.""" con, cur = connect._get_connection() sqlstr = ('select description ' 'from hvhnonc_subcategory ' 'where parent_ID = {0}') substr = ('(select ID from hvhnonc_category where description = ?)') sqlstr = sqlstr.format(substr) params = (self.category.currentText(), ) try: cur.execute(sqlstr, params) except: QtWidgets.QMessageBox.critical(self, '錯誤', '取得細目時出現未知錯誤') rows = cur.fetchall() con.close() self.subcategory.clear() for row in rows: if row[0]: self.subcategory.addItem(row[0]) self.subcategory.clearEditText()
def on_formBtn_clicked(self): # open a filter window self.filterWindow = QtWidgets.QDialog() Filter(self.filterWindow) returnID = self.filterWindow.exec() if not returnID: return # update the self.idIndex self.idIndex = self.getIdIndex(returnID) # fetch a record from the db con, cursor= connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ('select * from hvhnonc_in where ID=?') params = (returnID,) cursor.execute(sqlstr, params) record = cursor.fetchone() con.close() self.init_all_fields() self.update_by_record(record)
def on_subcategory_changed(self): """When subcategory field is changed, load name options.""" con, cur = connect._get_connection() sqlstr = ('select change_value ' 'from hvhnonc_cache ' 'where this_ID = ? ' 'and this_value = ? ' 'and change_ID = ?') params = (connect.get_field_id('subcategory'), self.subcategory.currentText(), connect.get_field_id('name')) try: cur.execute(sqlstr, params) except: QtWidgets.QMessageBox.critical(self, '錯誤', '取得名稱時出現未知錯誤') rows = cur.fetchall() con.close() self.name.clear() for row in rows: if row[0]: self.name.addItem(row[0]) self.name.clearEditText()
def load_inRecord(self, iid: int): con, cursor = connect._get_connection() con.row_factory = sqlite3.Row cursor = con.cursor() sqlstr = ('select * from hvhnonc_in where ID = ?') params = (iid, ) cursor.execute(sqlstr, params) row = cursor.fetchone() con.close() for k in row.keys(): try: w = getattr(self, k) except: continue if isinstance(w, QtWidgets.QLineEdit): w.setText(str(row[k])) elif isinstance(w, QtWidgets.QComboBox): w.setEditText(str(row[k])) elif isinstance(w, QtWidgets.QDateEdit): (y, m, d) = map(int, row[k].split('-')) date = QtCore.QDate(y, m, d) w.setDate(date)