class Transaction:
def __init__(self,
acc_user,
acc_asset='',
db=None,
table='transactions',
columns=[
'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset',
'amt_trans', 'tye_asset', 'ico_asset', 'tye_flow',
'dte_trans', 'tme_trans', 'acc_asset_related',
'nam_asset_related', 'rmk_asset_related',
'ico_asset_related', 'tye_asset_related',
'cod_trans_type', 'txt_trans_type', 'txt_trans_type_sub',
'txt_remark', 'ico_trans'
]):
self.db = db
self.table = table
self.columns = columns
self.dtype_columns = {}
self.vdefault_columns = {}
self.acc_user = acc_user
self.acc_asset = acc_asset
self.nam_asset = ''
self.amt_trans = 0.0
self.tye_flow = ''
self.dte_trans = ''
self.tme_trans = ''
self.acc_asset_related = ''
self.nam_asset_related = ''
self.cod_trans_type = ''
self.txt_trans_type = ''
self.txt_trans_type_sub = ''
self.txt_remark = ''
if not db:
self.db = DataBase()
def show_trans(self, acc_asset=None, id=None):
sql_asset, sql_id = " ", " "
if acc_asset:
sql_asset = " and (acc_asset='%s' or acc_asset_related='%s') " % (
acc_asset, acc_asset)
if id:
sql_id = " and id=%s " % id
sql_show = "select * from transactions where acc_user='******' %s %s order by dte_trans desc, id desc" % \
(self.acc_user, sql_asset, sql_id)
flag, result = self.db.read(sql_show)
if flag:
result['index'] = range(len(result))
return result
return pd.DataFrame()
def add_trans(self, dict_tran, is_transaction=False):
data, columns = [], []
for key, value in dict_tran.items():
columns.append(key)
data.append(value)
data_trans = pd.DataFrame(data=[data], columns=columns)
if not is_transaction:
flag, result = self.db.write(data_trans, self.table)
if not flag:
print(result)
return flag
else:
return data_trans, self.table, False, 'append'
def show_flow(self, month_now):
sql_show = """
select
tye_flow,
right(dte_trans,2) as day,
sum(case when tye_flow='expend' then -1*amt_trans else amt_trans end) as amount
from %s
where acc_user='******'
and left(dte_trans,6)='%s'
and tye_flow in ('income', 'expend')
group by tye_flow, day
order by tye_flow, day
""" % (self.table, self.acc_user, month_now)
flag, result = self.db.read(sql_show)
if flag:
return result
return pd.DataFrame()
def show_report(self, date, date_length):
sql_show = """
select
tye_flow,
cod_trans_type,
txt_trans_type,
ico_trans,
sum(case when tye_flow='expend' then -1*amt_trans else amt_trans end) as amount,
count(*) as cnt
from %s
where acc_user='******'
and left(dte_trans,%s)='%s'
and tye_flow in ('income', 'expend', 'transfer')
group by tye_flow, cod_trans_type, txt_trans_type, ico_trans
order by tye_flow, amount desc
""" % (self.table, self.acc_user, date_length, date)
flag, result = self.db.read(sql_show)
if flag:
return result
return pd.DataFrame()
def export_trans(self):
sql_export = """
select
dte_trans as `日期`,
case when amt_trans<0 then -1*amt_trans else amt_trans end as `金额`,
case
when tye_flow='income' then '流入'
when tye_flow='expend' then '流出'
else '转账' end as `资金流向`,
txt_trans_type as `交易类型`,
rmk_asset as `账户名称`,
rmk_asset_related as `相关账户名称`,
txt_remark as `备注`
from %s
where acc_user='******'
""" % (self.table, self.acc_user)
flag, result = self.db.read(sql_export)
if flag:
return result
return pd.DataFrame()
# 由于删除交易记录会有资金回流，因此一般情况下禁止直接删除记录，而不做后续处理
def delete_trans(self, id, is_transaction=True):
sql_delete = "delete from %s where acc_user='******' and id=%s " % (
self.table, self.acc_user, id)
if not is_transaction:
flag, result = self.db.execute(sql_delete)
if not flag:
print(result)
return flag
else:
return sql_delete
@staticmethod
def revert_dict(dic):
res = {}
for key, value in dic.items():
res[value] = key
return res
@staticmethod
def date_format(date, target_format='yyyymmdd'):
def date_fill(date_part):
if len(date_part) == 1:
date_part = '0%s' % date_part
return date_part[:2]
for seg in ['-', '/']:
if seg in date:
y, m, d = date.split(' ')[0].split(seg)[:3]
m = date_fill(m)
d = date_fill(d)
date = '%s%s%s' % (y, m, d)
break
return date[:8]
@staticmethod
def extract_date_column(dic_db2file_columns):
date_columns = []
for key, value in dic_db2file_columns.items():
if key.startswith('dte'):
date_columns.append(value)
return date_columns
def process_column_dtype(self, table_structure, dic_db2file_columns):
if not self.dtype_columns:
for Field, Type in table_structure.values[:, :2]:
column = Field.lower()
if Type.startswith('decimal'):
self.dtype_columns[column] = float
self.vdefault_columns[column] = 0.0
elif Type.startswith('integer'):
self.dtype_columns[column] = int
self.vdefault_columns[column] = 0
else:
self.dtype_columns[column] = str
self.vdefault_columns[column] = ''
dtype_file_columns = {}
vdefault_file_columns = {}
for column, file_column in dic_db2file_columns.items():
dtype_file_columns[file_column] = self.dtype_columns[column]
vdefault_file_columns[file_column] = self.vdefault_columns[column]
return dtype_file_columns, vdefault_file_columns
def create_from_transactions(self, file_transactions, dic_db2file_columns):
flag, result = self.db.read('desc %s' % self.table)
if flag:
df_table_structure = result
date_columns = self.extract_date_column(dic_db2file_columns)
dtype_file_columns, vdefault_file_columns = self.process_column_dtype(
df_table_structure, dic_db2file_columns)
df_data = pd.read_excel(file_transactions, dtype=dtype_file_columns)[dtype_file_columns.keys()].\
fillna(value=vdefault_file_columns)
for date_column in date_columns:
df_data[date_column] = df_data[date_column].apply(
self.date_format)
df_data = df_data.rename(
columns=self.revert_dict(dic_db2file_columns))
# auto fill
len_df_data = len(df_data)
next_id = load_next_id(self.table, self.db)
df_data['id'] = [next_id + i for i in range(len_df_data)]
df_data['acc_user'] = [self.acc_user] * len_df_data
for column in self.columns:
if column not in df_data.columns:
df_data[column] = [self.vdefault_columns[column]
] * len_df_data
flag, result = self.db.write(df_data, self.table)
if not flag:
print(result)
return flag
return flag
class Statistic:
def __init__(self, acc_user, dte_month=None, db=None, table='statistics',
columns=['id', 'acc_user', 'dte_month', 'amt_income_month', 'amt_expend_month', 'amt_budget',
'amt_budget_surplus', 'amt_debt_total', 'amt_asset_total', 'amt_asset_net']):
self.db = db
self.table = table
self.columns = columns
self.acc_user = acc_user
self.dte_month = dte_month
if not self.db:
self.db = DataBase()
def init_statistics(self):
if not self.dte_month:
self.dte_month = time.strftime("%Y%m", time.localtime())
df_statistics = pd.DataFrame(
columns=['acc_user', 'dte_month'],
data=[[self.acc_user, self.dte_month]])
flag, result = self.db.write(df_statistics, self.table)
return flag, result
# 为避免使用contab等定时工具，需要在执行statistics相关的操作之前，检查是否是新月，来决定是否清空月统计数据
def reset_statistics(self):
dte_month_now = time.strftime("%Y%m", time.localtime())
sql_update = """
update %s
set
amt_income_month = case when dte_month='%s' then amt_income_month else 0 end,
amt_expend_month = case when dte_month='%s' then amt_expend_month else 0 end,
dte_month = case when dte_month='%s' then dte_month else '%s' end
where acc_user='******'
""" % (self.table, dte_month_now, dte_month_now, dte_month_now, dte_month_now, self.acc_user)
flag, result = self.db.execute(sql_update)
# print(sql_update, flag, result)
if not flag:
print(result)
return flag
def show_statistics(self):
self.reset_statistics()
sql_show = " select * from %s where acc_user='******' " % (self.table, self.acc_user)
flag, result = self.db.read(sql_show)
if not flag:
print(result)
return pd.DataFrame()
else:
return result
def update_statistics(self, tye_amount, amount, cod_trans_type='', tye_asset='', tye_asset_related='', tye_update='add', is_transaction=False):
"""
:param tye_update: ['add', 'delete_trans', 'delete_asset']
:param tye_amount: ['income', 'expend', 'transfer', 'budget', 'asset', 'debt']
:param amount: 根据type_amount，取对应的值或者相反数
:param cod_trans_type: 交易类型代码
:param tye_asset: 资产账户类型
:param tye_asset_related: 相关联的资产账户类型
:param is_transaction: 为False则直接执行；为True则返回sql语句，待后续按照数据库事务规范执行
:return: 返回执行结果或返回适用于数据库事务的sql
"""
self.reset_statistics()
sql_set = " amt_asset_total = amt_asset_total "
if tye_update == 'add':
if tye_amount == 'income': # 流入
if tye_asset == 'asset':
if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销，不计入收入，但支出、预算剩余应减少
sql_set = " amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s, amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3)
else:
sql_set = " amt_income_month = amt_income_month + %s, amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3)
elif tye_asset == 'debt':
if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销，不计入收入，但支出、预算剩余应减少
sql_set = " amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s, amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3)
else:
sql_set = " amt_income_month = amt_income_month + %s, amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3)
elif tye_amount == 'expend': # 流出
if tye_asset == 'asset':
sql_set = " amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3)
elif tye_asset == 'debt':
sql_set = " amt_expend_month = amt_expend_month + %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount]*3)
elif tye_amount == 'transfer': # 转账
if tye_asset == 'asset' and tye_asset_related == 'debt': # 资产负债都减少，但数值一减一增
sql_set = " amt_asset_total = amt_asset_total - %s, amt_debt_total = amt_debt_total + %s " % tuple([amount]*2)
elif tye_asset == 'debt' and tye_asset_related == 'asset': # 资产负债都增加，但数值一增一减
sql_set = " amt_asset_total = amt_asset_total + %s, amt_debt_total = amt_debt_total - %s " % tuple([amount]*2)
elif tye_amount == 'budget':
sql_set = " amt_budget = %s, amt_budget_surplus = %s + amt_expend_month " % tuple([amount]*2)
elif tye_amount == 'asset':
sql_set = " amt_asset_total = amt_asset_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount] * 2)
elif tye_amount == 'debt':
sql_set = " amt_debt_total = amt_debt_total + %s, amt_asset_net = amt_asset_net + %s " % tuple([amount] * 2)
elif tye_update == 'delete_trans': # 删除交易记录会引起资产账户变动
if tye_amount == 'income': # 原交易记录为流入
if cod_trans_type in ('yqkDZNq7', 'xSTyQpeH'): # 退款、报销
sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s, amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month " % tuple([amount]*3)
else:
sql_set = " amt_income_month = amt_income_month - %s, amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3)
elif tye_amount == 'expend': # 原交易记录为流出
if tye_asset == 'asset':
sql_set = " amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3)
elif tye_asset == 'debt':
sql_set = " amt_expend_month = amt_expend_month - %s, amt_budget_surplus = amt_budget + amt_expend_month, amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*3)
elif tye_amount == 'transfer': # 原交易记录为转账
if tye_asset == 'asset' and tye_asset_related == 'debt':
sql_set = " amt_asset_total = amt_asset_total + %s, amt_debt_total = amt_debt_total - %s " % tuple([amount]*2)
elif tye_asset == 'debt' and tye_asset_related == 'asset':
sql_set = " amt_asset_total = amt_asset_total - %s, amt_debt_total = amt_debt_total + %s " % tuple([amount]*2)
elif tye_amount == 'adjust': # 原交易记录为资金调整
if tye_asset == 'asset':
sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2)
elif tye_asset == 'debt':
sql_set = " amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2)
elif tye_update == 'delete_asset':
if tye_asset == 'asset':
sql_set = " amt_asset_total = amt_asset_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount]*2)
elif tye_asset == 'debt':
sql_set = " amt_debt_total = amt_debt_total - %s, amt_asset_net = amt_asset_net - %s " % tuple([amount] * 2)
sql_update = "update %s set %s where acc_user ='******'" % (self.table, sql_set, self.acc_user)
print('sql_update_statistics', sql_update)
if not is_transaction:
flag, result = self.db.execute(sql_update)
if not flag:
print(result)
return flag
else:
return sql_update
class User:
def __init__(self,
acc_user=None,
pwd_user_md5=None,
db=None,
table='users',
columns=[
'id', 'acc_user', 'pwd_user_md5', 'nam_user', 'vlu_email',
'vlu_phone', 'vlu_openid', 'nam_nick', 'cod_gender',
'vlu_lang', 'vlu_city', 'vlu_prov', 'vlu_country',
'url_avatar'
]):
self.db = db
self.table = table
self.columns = columns
self.acc_user = acc_user
self.pwd_user_md5 = pwd_user_md5
self.nam_user = ''
self.vlu_email = ''
self.vlu_phone = ''
self.vlu_openid = ''
if not self.db:
self.db = DataBase()
def show_userinfo(self):
sql_show = "select * from %s where acc_user='******' " % (self.table,
self.acc_user)
flag, result = self.db.read(sql_show)
if not flag:
print(result)
return pd.DataFrame()
else:
return result
def save_userinfo(self, userinfo, is_transaction=False):
if not self.acc_user:
self.acc_user = gen_short_uuid()
self.pwd_user_md5 = get_md5(self.acc_user)
sql_delete = "delete from %s where acc_user='******' " % (self.table,
self.acc_user)
columns = ['acc_user', 'pwd_user_md5', 'vlu_openid']
data = [self.acc_user, self.pwd_user_md5, userinfo['vlu_openid']]
for key, column in dict_mapping.items():
columns.append(column)
data.append(userinfo.get(key, ''))
df_userinfo = pd.DataFrame(data=[data], columns=columns)
if not is_transaction:
flag, result = self.db.execute(sql_delete)
if not flag:
print(result)
return False
else:
flag, result = self.db.write(df_userinfo, self.table)
if not flag:
print(result)
return False
return True
else:
return sql_delete, df_userinfo, self.table, False, 'append'
def create(self):
if not self.acc_user:
self.acc_user = gen_short_uuid()
self.pwd_user_md5 = get_md5(self.acc_user)
columns = ['acc_user', 'pwd_user_md5', 'vlu_openid']
data = [self.acc_user, self.pwd_user_md5, self.vlu_openid]
df_user = pd.DataFrame(data=[data], columns=columns)
flag, result = self.db.write(df_user, self.table)
if flag:
return self.acc_user
else:
print(result)
return False
def user_check(self, openid):
self.vlu_openid = openid
sql_check = "select acc_user from %s where vlu_openid='%s' " % (
self.table, self.vlu_openid)
flag, result = self.db.read(sql_check)
if flag and not result.empty:
self.acc_user = result['acc_user'][0]
flag_new = False
else:
self.acc_user = self.create()
flag_new = True
return self.acc_user, flag_new
class Asset:
def __init__(self,
acc_user,
db=None,
table='assets',
columns=[
'id', 'acc_user', 'acc_asset', 'nam_asset', 'rmk_asset',
'tye_asset', 'amt_asset', 'ico_asset', 'boo_active'
]):
self.db = db
self.table = table
self.columns = columns
self.acc_user = acc_user
self.df_asset = pd.DataFrame()
if not self.db:
self.db = DataBase()
def show_assets(self, acc_asset=None, flag_active=1, need_index=True):
"""
:param db:
:param flag_active: 1, 0, 'all'
:return:
"""
sql_active, sql_asset = " ", " "
if flag_active in (0, 1):
sql_active = " and boo_active='%s' " % flag_active
if acc_asset:
sql_asset = " and acc_asset='%s' " % acc_asset
sql_show = "select * from assets where acc_user='******' %s %s order by id" % (
self.acc_user, sql_active, sql_asset)
flag, result = self.db.read(sql_show)
if flag:
if need_index:
result['index'] = range(len(result))
return result
return pd.DataFrame()
def add_assets(self, dict_asset, is_transaction=False):
if not dict_asset.has_key('acc_asset'):
dict_asset['acc_asset'] = gen_short_uuid()
data, columns = [], []
for key, value in dict_asset.items():
columns.append(key)
data.append(value)
data_asset = pd.DataFrame(data=[data], columns=columns)
if not is_transaction:
flag, result = self.db.write(data_asset, self.table)
return flag
else:
return data_asset, self.table, False, 'append'
# 用于资产账户信息的更改
def modify_assets(self, acc_asset, df_asset, is_transaction=False):
# 由于中文编码有问题，所以update改为delete+insert
sql_delete = "delete from %s where acc_user='******' and acc_asset='%s'" % (
self.table, self.acc_user, acc_asset)
if not is_transaction:
flag, result = self.db.execute(sql_delete)
if not flag:
print(result)
return False
else:
flag, result = self.db.write(df_asset, self.table)
if not flag:
print(result)
return False
return True
else:
return sql_delete, self.table, False, 'append'
# 用于交易等相关数据库事务的更新
def update_assets(self,
tye_flow,
amount,
acc_asset,
tye_asset,
acc_asset_related,
tye_asset_related,
tye_update='add',
is_transaction=False):
"""
:param tye_flow: ['income', 'expend', 'transfer', 'adjust']
:param tye_update: ['add', 'delete_trans', 'delete_asset']
"""
sql_update_assets, sql_update_assets_related = "", ""
sql_template = "update %s set amt_asset=amt_asset %s %s where acc_user='******' and acc_asset='%s' "
if tye_update == 'add':
if tye_flow == 'transfer': # 账面金额一减一增
sql_update_assets = sql_template % (self.table, '-', amount,
self.acc_user, acc_asset)
sql_update_assets_related = sql_template % (
self.table, '+', amount, self.acc_user, acc_asset_related)
elif tye_flow in ('income', 'expend'):
sql_update_assets = sql_template % (self.table, '+', amount,
self.acc_user, acc_asset)
else:
pass
elif tye_update == 'delete_trans': # 删除交易记录会引起资产账户变动
if tye_flow == 'transfer':
sql_update_assets = sql_template % (self.table, '+', amount,
self.acc_user, acc_asset)
sql_update_assets_related = sql_template % (
self.table, '-', amount, self.acc_user, acc_asset_related)
elif tye_flow in ('income', 'expend', 'adjust'):
sql_update_assets = sql_template % (self.table, '-', amount,
self.acc_user, acc_asset)
else:
pass
elif tye_update == 'delete_asset':
pass
else:
pass
print('sql_update_assets', sql_update_assets)
print('sql_update_assets_related', sql_update_assets_related)
if not is_transaction:
if sql_update_assets:
flag, result = self.db.execute(sql_update_assets)
if not flag:
print(result)
return False
else:
if sql_update_assets_related:
flag, result = self.db.execute(
sql_update_assets_related)
if not flag:
print(result)
return False
return True
else:
return sql_update_assets, sql_update_assets_related
# 由于删除账户会有资产变动，因此一般情况下禁止直接删除账户，而不做后续处理
def delete_assets(self, acc_asset, is_transaction=True):
sql_delete = "delete from %s where acc_user='******' and acc_asset='%s' " % (
self.table, self.acc_user, acc_asset)
if not is_transaction:
flag, result = self.db.execute(sql_delete)
if not flag:
print(result)
return flag
else:
return sql_delete
# 适用于导入交易记录时自动创建交易类型
def create_from_transactions(self, df_transactions):
next_id = load_next_id()
data_asset = []
nam_asset_list = df_transactions['nam_asset'].unique()
for i in range(len(nam_asset_list)):
id = next_id + i
nam_asset = nam_asset_list[i]
acc_asset = gen_short_uuid()
tye_asset = ''
amt_asset = 0.0
data_asset.append([
id, self.acc_user, acc_asset, nam_asset, tye_asset, amt_asset
])
self.df_asset = pd.DataFrame(data=data_asset, columns=self.columns)
flag, result = DataBase().write(self.df_asset, self.table)
print(flag, result)
return self.acc_user