def queryPassword(username):
DB = db.Db().strategy
a = DB.executeSql(
"select user_password from system_user where user_name = '%s'" %
username)
DB.close()
return a
def masking_tosql(config, json_result):
DB = db.Db(config).strategy
Masking = ms.Masking()
tableName = json_result["tableName"]
tableCol = json_result["tableCol"]
masking_key = json_result["masking_key"]
masking_type = json_result["masking_type"]
masking_other = json_result["masking_other"]
if not masking_other:
masking_other = []
result = DB.executeSql("select %s from %s" % (masking_key, tableName))
whenstr = ""
instr = ""
for i in result:
new_value = getattr(Masking, masking_type)(masking_other)
id = (i[masking_key])
print(type(id) == int)
if type(id) != int:
id = str(id)
whenstr += "WHEN %s THEN '%s' " % (id, new_value)
instr += str(id) + ","
DB.executeSql(
"update %s set %s = CASE %s %s END where %s in (%s)" %
(tableName, tableCol, masking_key, whenstr, masking_key, instr[0:-1]))
# DB.executeSql( "update \"TEST_MOBILE\" set \"MOBILE_NUM\" = CASE \"ID\" WHEN 1 THEN '13888888888' END where \"ID\" in (1)" )
# DB.executeSql( "update TEST_MOBILE set MOBILE_NUM = '13888888888'")
DB.close()
return result
def queryBookReadingInfo(book_id):
DB = db.Db().strategy
result = DB.executeSql(
"select begin_page,end_page from ol_book_reading where book_id = %s and is_delete=0"
% book_id)
DB.close()
return result
def checkReview(fixday, book_id):
DB = db.Db().strategy
sql = "UPDATE ol_book_reading SET review_num = review_num+1,create_date='%s' WHERE id = %s" % (
fixday, book_id)
result = DB.executeSql(sql)
DB.close()
return result
def updateProgess(book_id, pro):
DB = db.Db().strategy
result = DB.executeSql(
"update ol_book_info set book_status= %s where id= %s" %
(pro, book_id))
DB.close()
return result
def queryConfig(id=None):
DB = db.Db().strategy
where_sql = ' where 1=1 and is_delete = "0" '
if id != None:
where_sql = where_sql + ' and id = ' + id
result = DB.executeSql("select * from hqdba_db_config" + where_sql)
DB.close()
return result
def bookIsRepeat(username, bookname):
DB = db.Db().strategy
result = DB.executeSql(
"select id from ol_book_info where user_name = '%s' and book_name = '%s' and is_delete = '0'"
% (username, bookname))
DB.close()
return len(result)
def deleteBook(params, user_name):
DB = db.Db().strategy
book_id = params['id']
result = DB.executeSql(
"update ol_book_info set is_delete=1 where id = %s and user_name = '%s' "
% (book_id, user_name))
DB.close()
return result
def queryBookList(user_name):
DB = db.Db().strategy
where_sql = ' where 1=1 '
if user_name is not None:
where_sql = where_sql + ' and user_name = "' + user_name + '" and is_delete = 0 and book_status != 100 '
print(where_sql)
result = DB.executeSql("select * from ol_book_info" + where_sql)
DB.close()
return result
def addReadInfo(params, user_name):
DB = db.Db().strategy
sql = "insert into ol_book_reading(book_id, begin_page, end_page,create_date) values(%s, %s, %s, '%s')" % (
params['bookid'], params['bookPageNumberS'], params['bookPageNumberE'],
params['today'])
print(sql)
result = DB.executeSql(sql)
DB.close()
return result
def toMasking(config, json_result):
DB = db.Db(config).strategy
tableName = json_result["tableName"]
tableCol = json_result["tableCol"]
masking_type = json_result["masking_type"]
masking_other = json_result["masking_other"]
print(json_result)
result = []
if masking_type == "getFixedValue":
result = DB.executeSql("update " + tableName + " set " + tableCol +
" = '" + masking_other[0] + "'")
else:
result = masking_tosql(config, json_result)
DB.close()
return result
def queryRank(user_name):
"""
查询积分
:param user_name: str
:return: dict
"""
print(user_name)
try:
DB = db.Db().strategy
sql = "SELECT rank from ol_user_info WHERE user_name = '%s' " % (user_name)
result = DB.executeSql(sql)
DB.close()
return result
except Exception as e:
return e
def addRank(user_name, rank_val):
"""
增加积分
:param rank_val: int
:param user_name: str
:return: dict
"""
try:
DB = db.Db().strategy
sql = "UPDATE ol_user_info SET rank=rank + %s WHERE user_name = '%s' " % (rank_val, user_name)
result = DB.executeSql(sql)
DB.close()
return result
except Exception as e:
return e
def completeTask(user_name, params):
"""
完成任务
:param user_name: str
:return: dict
"""
print(user_name)
try:
DB = db.Db().strategy
sql = "UPDATE ol_task SET task_status='1' , task_complete_time='%s' where id='%s';" % (params['complete_time'], params['id'] )
print(sql)
result = DB.executeSql(sql)
DB.close()
return result
except Exception as e:
return e
def queryTask(user_name, params):
"""
查询积分
:param user_name: str
:return: dict
"""
print(user_name)
try:
DB = db.Db().strategy
wheresql = ' 1=1 '
if params['task_status'] != '-1':
wheresql += 'and task_status = ' + params['task_status']
sql = "SELECT * from ol_task WHERE user_name = '%s' and task_create_time > '%s' and task_create_time < '%s' and is_delete = 0 and %s" \
% (user_name, params['begin'], params['end'], wheresql)
print(sql)
result = DB.executeSql(sql)
DB.close()
return result
except Exception as e:
return e
def getReviewInfo(user_name, review_date):
DB = db.Db().strategy
where_sql = ' where 1=1 '
if user_name is not None:
where_sql = where_sql + ' and user_name = "' + user_name + '"'
where_sql = """%s and a.user_name = '%s'
and a.is_delete = '0'
and a.read_type='0'
and (b.review_num = '0' and b.create_date <= '%s')
or (b.review_num = '1' and b.create_date <= '%s')
or (b.review_num = '2' and b.create_date <= '%s')
or (b.review_num = '3' and b.create_date <= '%s')
or (b.review_num = '4' and b.create_date <= '%s')
""" % (where_sql, user_name, review_date[0], review_date[1],
review_date[2], review_date[3], review_date[4])
result = DB.executeSql(
"select b.id, a.book_name,b.begin_page,b.end_page,b.create_date,b.review_num from ol_book_info a LEFT JOIN ol_book_reading b ON a.id = b.book_id"
+ where_sql)
DB.close()
return result
def addTask(user_name, params):
"""
增加任务
:param params: object
:param user_name: str
:return: dict
"""
try:
DB = db.Db().strategy
sql = """insert into
ol_task(user_name, task_name, task_add_rank, task_reduce_rank,
task_mark, task_create_time, task_plan_complete_time,
task_quadrant, task_repeat_type, task_repeat_point, task_repeat_end)
values('%s', '%s', '%s','%s', '%s', '%s','%s', '%s', '%s','%s', '%s' )""" \
% (user_name, params['task_name'], params['task_add_rank'], params['task_reduce_rank'],
params['task_mark'], params['task_create_time'], params['task_plan_complete_time'],
params['task_quadrant'], params['task_repeat_type'], params['task_repeat_point'], params['task_repeat_end'],
)
print(sql)
result = DB.executeSql(sql)
DB.close()
return result
except Exception as e:
return e
def mask_01_queryNum(config):
DB = db.Db(config).strategy
count = DB.executeSql("select count(*) as count from gl_voucher")
count = count[0]["count"]
return count
def mask_01_toMasking(config, count):
global dai_value
DB = db.Db(config).strategy
Masking = ms.Masking()
pageSize = 100
starttime = datetime.datetime.now()
main_data = DB.executeSql(
"select PK_VOUCHER,TOTALCREDIT,TOTALDEBIT from gl_voucher LIMIT %s,%s;"
% ((count - 1) * pageSize + 1, pageSize))
main_whenstr = ""
main_instr = ""
dai_whenstr = ""
dai_instr = ""
jie_whenstr = ""
jie_instr = ""
for i in main_data:
# 生成主表随机金额
new_value = Masking.getRandomNumber([10000, 100000, 4])
new_value_F = new_value if i["TOTALCREDIT"] < 0 else -new_value
new_value_F = round(new_value_F, 4)
PK_VOUCHER = i["PK_VOUCHER"]
# 主表更新语句条件
main_whenstr += "WHEN '%s' THEN %s " % (PK_VOUCHER, new_value_F)
main_instr += "'" + str(PK_VOUCHER) + "'" + ","
# 根据主表查询子表贷方明细
dai_data = DB.executeSql(
"select PK_DETAIL from gl_detail where pk_voucher='%s' and DIRECTION='C';"
% (PK_VOUCHER))
if len(dai_data) == 1:
# 如果只有一条数据，主表金额等于贷方子表金额
PK_DETAIL = dai_data[0]["PK_DETAIL"]
dai_whenstr += "WHEN '%s' THEN %s " % (PK_DETAIL, new_value_F)
dai_instr += "'" + str(PK_DETAIL) + "'" + ","
elif len(dai_data) > 1:
# 如果多条数据，主表金额拆分为多条子表金额
sum = new_value
for j in range(len(dai_data)):
if j == (len(dai_data) - 1):
dai_value = sum
else:
dai_value = Masking.getRandomNumber([0, sum, 4])
sum -= dai_value
PK_DETAIL = dai_data[j]["PK_DETAIL"]
dai_value_F = dai_value if new_value_F > 0 else -dai_value
dai_value_F = round(dai_value_F, 4)
dai_whenstr += "WHEN '%s' THEN %s " % (PK_DETAIL, dai_value_F)
dai_instr += "'" + str(PK_DETAIL) + "'" + ","
# 根据主表查询子表借方明细
jie_data = DB.executeSql(
"select PK_DETAIL from gl_detail where pk_voucher='%s' and DIRECTION='D';"
% (PK_VOUCHER))
if len(jie_data) == 1:
# 如果只有一条数据，主表金额等于贷方子表金额
PK_DETAIL = jie_data[0]["PK_DETAIL"]
jie_whenstr += "WHEN '%s' THEN %s " % (PK_DETAIL, new_value_F)
jie_instr += "'" + str(PK_DETAIL) + "'" + ","
elif len(jie_data) > 1:
# 如果多条数据，主表金额拆分为多条子表金额
sum = new_value
for k in range(len(jie_data)):
if k == (len(jie_data) - 1):
jie_value = sum
else:
jie_value = Masking.getRandomNumber([0, sum, 4])
sum -= jie_value
PK_DETAIL = jie_data[k]["PK_DETAIL"]
jie_value_F = jie_value if new_value_F > 0 else -jie_value
jie_value_F = round(jie_value_F, 4)
jie_whenstr += "WHEN '%s' THEN %s " % (PK_DETAIL, jie_value_F)
jie_instr += "'" + str(PK_DETAIL) + "'" + ","
# for j in dai_data:
# 更新主表
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_voucher", "TOTALCREDIT", "PK_VOUCHER", main_whenstr,
"PK_VOUCHER", main_instr[0:-1]))
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_voucher", "TOTALDEBIT", "PK_VOUCHER", main_whenstr,
"PK_VOUCHER", main_instr[0:-1]))
# 更新贷方数据
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_detail", "LOCALCREDITAMOUNT", "PK_DETAIL", dai_whenstr,
"PK_DETAIL", dai_instr[0:-1]))
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_detail", "CREDITAMOUNT", "PK_DETAIL", dai_whenstr,
"PK_DETAIL", dai_instr[0:-1]))
# 更新借方数据
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_detail", "LOCALDEBITAMOUNT", "PK_DETAIL", jie_whenstr,
"PK_DETAIL", jie_instr[0:-1]))
DB.executeSql("update %s set %s = CASE %s %s END where %s in (%s)" %
("gl_detail", "DEBITAMOUNT", "PK_DETAIL", jie_whenstr,
"PK_DETAIL", jie_instr[0:-1]))
# print("主表SQL：update %s set %s = CASE %s %s END where %s in (%s)" % (
# "gl_voucher", "TOTALCREDIT", "PK_VOUCHER", main_whenstr, "PK_VOUCHER", main_instr[0:-1]))
# print("贷方SQL：update %s set %s = CASE %s %s END where %s in (%s)" % (
# "gl_detail", "LOCALCREDITAMOUNT", "PK_DETAIL", dai_whenstr, "PK_DETAIL", dai_instr[0:-1]))
# print("借方SQL：update %s set %s = CASE %s %s END where %s in (%s)" % (
# "gl_detail", "LOCALDEBITAMOUNT", "PK_DETAIL", jie_whenstr, "PK_DETAIL", jie_instr[0:-1]))
endtime = datetime.datetime.now()
print("已脱敏完成！共用时%s秒！" % (endtime - starttime).seconds)
DB.close()
return count
def addConfig(params):
DB = db.Db().strategy
result = DB.insert_data("hqdba_db_config", params)
DB.close()
return result
def removeConfig(params):
DB = db.Db().strategy
result = DB.executeSql(
"UPDATE hqdba_db_config SET is_delete = '1' where id = '%s'" % params)
DB.close()
return result
def addBook(params):
DB = db.Db().strategy
result = DB.insert_data("ol_book_info", params)
DB.close()
return result
def queryAllTables(config):
DB = db.Db(config).strategy
result = DB.queryAllTables()
DB.close()
return result
def get2db(sql):
dbconfig = config.default_config2()
DB = db.Db(dbconfig).strategy
data = DB.executeSql(sql)
DB.close()
return data
def queryOneTable(config, tableName):
DB = db.Db(config).strategy
result = DB.queryOneTable(tableName)
DB.close()
return result
def addTest(params):
DB = db.Db().strategy
result = DB.insert_data("hqdba_db_test", params)
DB.close()
return result