def del_local_record(self):
ids = self.local_list.selectedIndexes()
for id in ids:
sql = "delete from msg where username = '******' and id = %d" % (
self.user_info['username'], self.local_records[id.row()]['id'])
sqlite_db.transaction(sql)
self.local_list.takeItem(id.row())
def check(self):
'''
用于检测用户的登录状态
:return:
'''
self.user_info = mine.get_value('user_info')
logging.warning(self.user_info)
ret = get_notes(self.user_info['username'])
if not ret['status']:
logging.error(ret['msg'])
records = ret['records'] if 'records' in ret.keys() else list()
if not records:
table = 'Msg'
dict = {
'message': 'Welcome',
'detail': 'Thanks\ you\ support',
'username': self.user_info['username']
}
dict['message'] = cryptograph_text(
dict['message'],
'message',
user_name=self.user_info['username'])
dict['detail'] = cryptograph_text(
dict['detail'], 'detail', user_name=self.user_info['username'])
sql = be_sql().ins_sql(table, dict)
sqlite_db.transaction(sql)
return self.user_info['username']
def set_is_default_email_conf(id, username):
sql = """update email_settings set is_default = 0 where username = ? """
param = [username]
if not sqlite_db.transaction(sql, param)['status']:
return
sql = """update email_settings set is_default = 1 where id= ? ;"""
param = [id]
return sqlite_db.transaction(sql, param)
def do_logout(self):
response = logout(self.username)
if response['status']:
sql = "update user set token=NULL where username = :username"
sqlite_db.transaction(sql,{"username",self.username})
self.close()
self.logout_signal.emit('visitor')
else:
Toast(self).show_toast("{}".format('请检查数据库文件和网络状态'))
def clear_notes(filename, sever_date):
'''
根据系统返回天数物理删除记录
:param filename:
:param sever_date:
:return:
'''
try:
sqlite_db.transaction(
("delete from Msg where del_time<strftime('yyyy-mm-dd',%s);" %
sever_date))
logging.info('clear done')
except Exception as e:
print("clear error")
logging.warning(e)
def add_email_conf(username, addr, password, sender_name, ssl_port, user_ssl,
*args, **kwargs):
param = (username, addr, password, sender_name, ssl_port, user_ssl)
sql = """insert into
email_settings(username,addr, password, sender_name, ssl_port, user_ssl)
values(?,?,?,?,?,?)
"""
return sqlite_db.transaction(sql, param)
def update_notes(data, ele, **kwargs):
# 加密
data[ele] = cryptograph_text(data[ele], ele, username=kwargs['username'])
sql = "update Msg set {}=:{},update_time=datetime(CURRENT_TIMESTAMP, 'localtime') where id != -1 and id = ?".format(
ele, ele)
ret = sqlite_db.transaction(sql, [data['id']])
if not ret['status']:
logging.error("message:{}\nsql:{}".format(ret['msg'], ret['errorsql']))
return ret
def add_notes(data):
for k, v in data.items():
if 'username' != k:
ele = k
data[k] = cryptograph_text(v, k, username=data['username'])
sql = "insert into Msg({},username) values(?,?)".format(ele)
ret = sqlite_db.transaction(sql, [data[ele], data['usename']])
if not ret['status']:
logging.error("message:{}\nsql:{}".format(ret['msg'], ret['errorsql']))
sql = "select * from Msg where username = ? and is_del = 0 order by id desc limit 0 ,1"
ret = sqlite_db.select(sql, [data['username']], just_first=True)
return ret
def change_del_flag(filter_list, is_del=1):
'''
软删除数据
:param filename:
:param filer_list:
:return:
'''
table = 'Msg'
sql = be_sql().update_sql(table=table,
value_dict={'is_del': str(is_del)},
filter_list=filter_list)
# print(sql)
return sqlite_db.transaction(sql)
def update_email_conf(id, username, addr, password, sender_name, ssl_port,
user_ssl, *args, **kwargs):
param = (username, addr, password, sender_name, ssl_port, user_ssl, id)
sql = """update
email_settings
set username = ?,
addr = ?,
password = ?,
sender_name = ?,
ssl_port = ?,
user_ssl = ?
where id = ?
"""
return sqlite_db.transaction(sql, param)
def add_msg(data: dict):
'''
添加数据 更改会影响 增加逻辑 延后重构 wrb
:param filename: sqlit3文件名
:param data: dict {'id':-1,'{ele}':'','username':''}
:return:
'''
if data['id'] == -1:
data.pop('id')
# 明文加密
for k, v in data.items():
if 'username' != k:
data[k] = cryptograph_text(v, k, user_name=data['username'])
sql = "insert into msg(username,msg,detail) values(?,?,?)"
ret = sqlite_db.transaction(
sql, [data['username'], data['msg'], data['detail']])
if not ret['status']:
logging.error("msg:{}\nsql:{}".format(ret['msg'], ret['sql']))
return ret
def do_login(self):
if self.username_le.text() and self.password_le.text():
result = login(
username=self.username_le.text(), password=self.password_le.text())
# assert result['token'], '本地无记录'
logging.debug(result)
try:
if result['result']['token']:
table = 'user'
# 写入缓存
mine.update_item('username', self.username_le.text())
mine.update_item('token', result['result']['token'])
if not utils.get_user_info(table, self.username_le.text()):
# 没有用户直接插入
dict = {'username': self.username_le.text(),
'token': result['token']}
sql = "insert into user(username,token) values ('%s','%s');"
sqlite_db.transaction(sql)
times = ['00:15:00', '00:30:00', '01:00:00']
for time in times:
sqlite_db.transaction(
"insert into Reminder(username,time) values ('%s','%s');" % (dict['username'], time))
# 已有用户更新数据
else:
value_dict = {'token': result['result']['token']}
filter_list = [
['username', '=', self.username_le.text()]]
sql = be_sql().update_sql(table=table, value_dict=value_dict,
filter_list=filter_list)
sqlite_db.transaction(sql)
try:
# 发射信号
self.login_signal.emit(self.username_le.text())
except Exception as e:
logging.error(e)
self.close()
else:
Toast(self).show_toast("{}".format(result['msg']))
except Exception as e:
logging.error(e)
elif not self.username_le.text():
Toast(self).show_toast("请输入账号")
elif not self.password_le.text():
Toast(self).show_toast("请输入密码")
def change_del_flag(id, is_del=1):
''' '''
sql = "update Msg set is_del = ? where id=?"
return sqlite_db.transaction(sql, [is_del, id])
def del_email_conf(id):
sql = """delete from email_settings where id=? ; """
param = [id]
return sqlite_db.transaction(sql, param)
