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 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 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 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 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 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 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 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 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_options(self, widgets: Dict[str, QtWidgets.QComboBox]):
con, cur = connect._get_connection()
for key, widget in widgets.items():
if key == 'category':
sqlstr = ('select description from hvhnonc_category;')
cur.execute(sqlstr)
rows = cur.fetchall()
categories = [row[0] for row in rows]
widget.clear()
widget.addItems(categories)
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), )
cur.execute(sqlstr, params)
rows = cur.fetchall()
options = [row[0] for row in rows]
widget.addItems(options)
widget.clearEditText()
con.close()