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)
