def main():
report = DailyReportSummary()
yesterday = MyDate().yesterday
path = report.path
recharge_and_withdraw = report.cg_recharge_withdraw_vol_daily()
interest = report.product_and_jxq()
interest.interest = interest.interest / 10000
recharge_and_withdraw.iloc[:,
1:] = recharge_and_withdraw.iloc[:, 1:] / 10000
recharge_and_withdraw = to_timeseries('2017-08-10', yesterday,
recharge_and_withdraw, interest)
fixed_vol = report.cg_fixed_vol()
chuxin_vol = report.cg_chuxin_vol_monthly()
fixed_vol.index = fixed_vol.index.strftime('%Y-%m')
chuxin_vol.index = chuxin_vol.index.strftime('%Y-%m')
interest_payable = report.product_jxq_interest_payable()
interest_payable.interest_payable = interest_payable.interest_payable / 10000
interest_payable = interest_payable.set_index('date')
product_vol = pd.concat([interest_payable, fixed_vol, chuxin_vol], axis=1)
recharge_and_withdraw.to_csv(path + r'\recharge_and_withdraw.csv',
index=False)
product_vol.to_csv(path + r'\product_vol.csv', index=True)
def cg_recharge_withdraw_vol_daily(self):
from recharge_withdraw.recharge_and_withdraw import cg_recharge, cg_withdraw
# import necessary datetime:
last_saved = self.last_saved
today = self.curdate
to_the_beginning = '2017-08-10'
# output necessary data:
recharge = cg_recharge(to_the_beginning, today)
withdraw = cg_withdraw(to_the_beginning, today)
fixed_income = WeeklyReport.cg_fixed()
recharge = pd.DataFrame(
list(recharge), columns=['date', 'mobile_no', 'name', 'amount'])
withdraw = pd.DataFrame(
list(withdraw), columns=['date', 'mobile_no', 'name', 'amount'])
# data cleaning:
recharge.set_index('date', inplace=True)
withdraw.set_index('date', inplace=True)
fixed_income.set_index('date', inplace=True)
recharge = recharge.amount.groupby(pd.TimeGrouper(freq='D')).sum()
withdraw = withdraw.amount.groupby(pd.TimeGrouper(freq='D')).sum()
recharge_and_withdraw = to_timeseries(to_the_beginning, last_saved,
recharge, withdraw)
return recharge_and_withdraw
def cg_chuxin_plan(self):
cur = sql_connection()
print('loading chuxin plan...')
sql_chuxin_invest = \
"SELECT DATE(invest_date),SUM(invest_amount)/10000\
FROM cg_user_chuxin_plan_detail\
WHERE disabled=0\
and DATE(invest_date)>=%s\
and DATE(invest_date)<=%s\
AND invest_flag=1\
GROUP BY DATE(invest_date);"
sql_chuxin_withdraw = \
"SELECT DATE(a.quite_time),SUM(a.amount)/10000 FROM(\
SELECT p.quite_time,p.id,SUM(d.invest_amount) amount \
FROM cg_user_chuxin_plan_detail d\
LEFT JOIN cg_user_chuxin_planinfo p ON p.id=d.planinfo_id\
WHERE d.disabled=0\
AND p.is_quite=1\
AND d.invest_flag=1\
GROUP BY p.id\
)a\
where (DATE(a.quite_time)>=%s\
and DATE(a.quite_time)>='2017-11-01')\
and DATE(a.quite_time)<=%s\
GROUP BY DATE(a.quite_time);"
cur.execute(sql_chuxin_invest, [self.cg_start_date, self.endate])
chuxin_invest = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'invest'])
cur.execute(sql_chuxin_withdraw, [self.cg_start_date, self.endate])
chuxin_withdraw = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'withdraw'])
cur.close()
cg_chuxin = to_timeseries(self.cg_start_date, self.endate,
chuxin_invest, chuxin_withdraw)
return cg_chuxin
def cg_other_statistic(self):
cur = sql_connection()
print('loading other cg statistics...')
sql_cg_annual_mean_rate = \
"SELECT SUM(a.`存量利率积`)/SUM(a.`存量和`)*100 FROM (\
SELECT remain.plan_code,SUM(remain.sum) 存量和,remain.`存量利率积` FROM(\
SELECT i.plan_code,SUM(i.amount) sum,\
ROUND(SUM(i.cal_invest_rate/100*i.amount),2)存量利率积 FROM cg_user_invest i\
WHERE i.plan_code!='LQB01'\
AND DATE_ADD(i.end_interest_date,INTERVAL 1 DAY)>%s\
AND i.invest_time<=%s\
AND i.user_name not in (\
select user_name from t_bus_user \
where role_id in ('counselor','ordinary'))\
GROUP BY i.plan_code\
UNION\
SELECT 'ChuxinPlan',SUM(d.interest_amount) sum,\
ROUND(SUM(d.interest_rate/100*d.interest_amount),2) 存量利率积\
FROM cg_chuxinplan_income_detail d\
WHERE d.disabled=0\
AND DATE(d.income_date)=%s\
UNION\
SELECT 'LQB01',SUM(LQB.current_amount)+SUM(LQB.frozen_amount) sum,\
ROUND(SUM((LQB.current_amount+LQB.frozen_amount)/100*6.8),2) FROM (\
SELECT DATE(d.create_time),d.user_name,d.current_amount,d.frozen_amount \
FROM cg_user_capital_account_detail d\
LEFT JOIN (\
SELECT DATE(d.create_time),d.user_name,MAX(id) id\
FROM cg_user_capital_account_detail d\
GROUP BY DATE(d.create_time),d.user_name\
)maxid ON maxid.id=d.id\
WHERE DATE(d.create_time)=%s\
AND maxid.id is NOT NULL\
)LQB\
)remain\
GROUP BY remain.plan_code)a;"
sql_new_register = "SELECT DATE(create_date),COUNT(user_name) FROM t_bus_user\
WHERE role_id='cgOrdinary'\
AND DATE(create_date)>=%s\
AND DATE(create_date)<=%s\
GROUP BY DATE(create_date);"
sql_new_cg_register = "SELECT DATE(depository_create_time),COUNT(user_name) FROM cg_user_info\
WHERE user_name NOT in(\
SELECT user_name FROM t_bus_account\
where create_time<=%s)\
GROUP BY DATE(depository_create_time);"
sql_new_investor = \
"SELECT COUNT(DISTINCT a.user_name) FROM (\
SELECT user_name FROM t_bus_financial_details\
WHERE date(create_date)>=%s\
AND date(create_date)<=%s\
AND details_type in('104007','104022','104028','104030','104031','104032','104036','104038','104042')\
UNION\
SELECT user_name FROM cg_user_invest\
WHERE date(invest_time)>=%s\
AND date(invest_time)<=%s\
AND amount>=3\
)a\
LEFT JOIN t_bus_user u ON u.user_name=a.user_name\
WHERE u.role_id='cgOrdinary'\
AND a.user_name NOT in (\
SELECT user_name FROM t_bus_financial_details\
WHERE date(create_date)<=%s\
AND details_type in('104007','104022','104028','104030','104031','104032','104036','104038','104042')\
);"
sql_new_register_investor = \
"SELECT COUNT(DISTINCT a.user_name) FROM (\
SELECT user_name FROM t_bus_financial_details\
WHERE date(create_date)>=%s\
AND date(create_date)<=%s\
AND details_type in('104007','104022','104028','104030','104031','104032','104036','104038','104042')\
UNION\
SELECT user_name FROM cg_user_invest\
WHERE date(invest_time)>=%s\
AND date(invest_time)<=%s\
AND amount>=3\
)a\
LEFT JOIN t_bus_user u ON u.user_name=a.user_name\
WHERE u.role_id='cgOrdinary'\
AND a.user_name NOT in (\
SELECT user_name FROM t_bus_financial_details\
WHERE date(create_date)<=%s\
AND details_type in('104007','104022','104028','104030','104031','104032','104036','104038','104042')\
)\
AND date(u.create_date)>=%s\
AND date(u.create_date)<=%s;"
sql_cg_total_invest = \
"SELECT SUM(a.amount) amount FROM(\
SELECT SUM(amount) amount FROM cg_user_invest\
WHERE date(invest_time)>=%s\
AND date(invest_time)<=%s\
AND amount>=3\
UNION\
SELECT SUM(invest_amount) amount FROM cg_user_chuxin_plan_detail\
WHERE disabled=0\
AND invest_flag=1\
AND date(invest_date)>=%s\
AND date(invest_date)<=%s\
UNION\
SELECT SUM(expenditure) amount FROM t_bus_financial_details tbfd\
left join t_bus_user u on u.user_name=tbfd.user_name\
WHERE tbfd.details_type in \
('104007','104022','104028','104030','104031','104032','104036','104038','104042')\
AND date(tbfd.create_date)>=%s\
AND date(tbfd.create_date)<=%s\
AND role_id='cgOrdinary')a;"
# TODO: the number of new investors in a week is remain developing...
cur.execute(sql_cg_annual_mean_rate,
[self.endate, self.endate, self.endate, self.endate])
cg_annual_mean_rate = float(cur.fetchall()[0][0])
cur.execute(sql_new_register, [self.cg_start_date, self.endate])
new_register = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'new_register'])
cur.execute(sql_new_cg_register, self.endate)
new_cg_register = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'new_cg_register'])
cur.execute(
sql_new_investor,
[self.month, self.endate, self.month, self.endate, self.endate])
new_investor = int(cur.fetchall()[0][0])
cur.execute(sql_new_register_investor, [
self.month, self.endate, self.month, self.endate, self.endate,
self.month, self.endate
])
new_register_investor = int(cur.fetchall()[0][0])
cur.execute(sql_cg_total_invest, [
self.month, self.endate, self.month, self.endate, self.month,
self.endate
])
cg_total_invest = float(cur.fetchall()[0][0])
cur.close()
new_reginvest = to_timeseries(self.cg_start_date, self.endate,
new_register, new_cg_register)
label = ['平均年化利率', '当月新投资用户', '当月新注册并投资用户', '当月有效投资金额']
statnum = [
cg_annual_mean_rate, new_investor, new_register_investor,
cg_total_invest
]
return new_reginvest, statnum, label
def cg_fixed(self):
cur = sql_connection()
print('loading cg fixed income...')
sql_fixed_invest = \
"SELECT DATE(i.invest_time),\
SUM(CASE i.plan_code WHEN 'DQ001' THEN i.amount ELSE 0 END)/10000 '1',\
SUM(CASE i.plan_code WHEN 'DQ002' THEN i.amount ELSE 0 END)/10000 '2',\
SUM(CASE i.plan_code WHEN 'DQ003' THEN i.amount ELSE 0 END)/10000 '3',\
SUM(CASE i.plan_code WHEN 'DQ004' THEN i.amount ELSE 0 END)/10000 '4',\
SUM(CASE i.plan_code WHEN 'DQ005' THEN i.amount ELSE 0 END)/10000 '5',\
SUM(CASE i.plan_code WHEN 'DQ006' THEN i.amount ELSE 0 END)/10000 '6',\
SUM(CASE i.plan_code WHEN 'DQ007' THEN i.amount ELSE 0 END)/10000 '7',\
SUM(CASE i.plan_code WHEN 'DQ008' THEN i.amount ELSE 0 END)/10000 '8',\
SUM(CASE i.plan_code WHEN 'DQ009' THEN i.amount ELSE 0 END)/10000 '9',\
SUM(CASE i.plan_code WHEN 'DQ010' THEN i.amount ELSE 0 END)/10000 '10',\
SUM(CASE i.plan_code WHEN 'DQ011' THEN i.amount ELSE 0 END)/10000 '11',\
SUM(CASE i.plan_code WHEN 'DQ012' THEN i.amount ELSE 0 END)/10000 '12' \
FROM cg_user_invest i\
WHERE i.plan_code!='LQB01'\
ANd date(i.invest_time)>=%s\
AND date(i.invest_time)<=%s\
GROUP BY DATE(i.invest_time);"
sql_fixed_withdraw = \
"SELECT DATE(DATE_ADD(i.end_interest_date,INTERVAL 1 DAY)),\
SUM(CASE i.plan_code WHEN 'DQ001' THEN i.amount ELSE 0 END)/10000 '1',\
SUM(CASE i.plan_code WHEN 'DQ002' THEN i.amount ELSE 0 END)/10000 '2',\
SUM(CASE i.plan_code WHEN 'DQ003' THEN i.amount ELSE 0 END)/10000 '3',\
SUM(CASE i.plan_code WHEN 'DQ004' THEN i.amount ELSE 0 END)/10000 '4',\
SUM(CASE i.plan_code WHEN 'DQ005' THEN i.amount ELSE 0 END)/10000 '5',\
SUM(CASE i.plan_code WHEN 'DQ006' THEN i.amount ELSE 0 END)/10000 '6',\
SUM(CASE i.plan_code WHEN 'DQ007' THEN i.amount ELSE 0 END)/10000 '7',\
SUM(CASE i.plan_code WHEN 'DQ008' THEN i.amount ELSE 0 END)/10000 '8',\
SUM(CASE i.plan_code WHEN 'DQ009' THEN i.amount ELSE 0 END)/10000 '9',\
SUM(CASE i.plan_code WHEN 'DQ010' THEN i.amount ELSE 0 END)/10000 '10',\
SUM(CASE i.plan_code WHEN 'DQ011' THEN i.amount ELSE 0 END)/10000 '11',\
SUM(CASE i.plan_code WHEN 'DQ012' THEN i.amount ELSE 0 END)/10000 '12' \
FROM cg_user_invest i\
WHERE i.plan_code!='LQB01'\
AND DATE_ADD(date(i.end_interest_date),INTERVAL 1 DAY)>=%s\
AND DATE_ADD(date(i.end_interest_date),INTERVAL 1 DAY)<=%s\
GROUP BY DATE(DATE_ADD(date(i.end_interest_date),INTERVAL 1 DAY));"
cur.execute(sql_fixed_invest, [self.cg_start_date, self.endate])
fixed_invest = pd.DataFrame(list(cur.fetchall()),
columns=[
'date', '1', '2', '3', '4', '5', '6',
'7', '8', '9', '10', '11', '12'
])
cur.execute(sql_fixed_withdraw, [self.cg_start_date, self.endate])
fixed_withdraw = pd.DataFrame(list(cur.fetchall()),
columns=[
'date', '1', '2', '3', '4', '5', '6',
'7', '8', '9', '10', '11', '12'
])
cur.close()
cg_fixed = to_timeseries(self.cg_start_date, self.endate, fixed_invest,
fixed_withdraw)
cg_fixed = cg_fixed.reindex(columns=[
'date', '1_x', '1_y', '2_x', '2_y', '3_x', '3_y', '4_x', '4_y',
'5_x', '5_y', '6_x', '6_y', '7_x', '7_y', '8_x', '8_y', '9_x',
'9_y', '10_x', '10_y', '11_x', '11_y', '12_x', '12_y'
])
return cg_fixed
def old_coin_purse(self):
cur = sql_connection()
print('loading coin_purse_invest...')
sql_online_invest = "SELECT tbm.product_name,DATE(tbfd.create_date),SUM(tbfd.expenditure) \
FROM t_bus_financial_details tbfd\
LEFT JOIN t_bus_user tbu ON tbfd.user_name=tbu.user_name\
LEFT JOIN t_bus_match tbm ON tbm.product_no=tbfd.product_no\
WHERE tbu.role_id not in ('ordinary','counselor','special')\
AND DATE(tbfd.create_date)>=%s\
AND DATE(tbfd.create_date)<=%s\
AND tbfd.details_type in ('104022','104028','104038')\
AND tbu.sys_flag!=2\
GROUP BY DATE(tbfd.create_date),tbm.product_name;"
sql_offline_invest = \
"SELECT tbm.product_name,DATE(tbfd.create_date),SUM(tbfd.expenditure) FROM t_bus_financial_details tbfd\
LEFT JOIN t_bus_user tbu ON tbfd.user_name=tbu.user_name \
LEFT JOIN t_bus_match tbm ON tbm.product_no=tbfd.product_no \
WHERE tbu.role_id in ('ordinary','counselor') \
AND DATE(tbfd.create_date)>=%s\
AND DATE(tbfd.create_date)<=%s\
and tbfd.user_name not in ('AB4E6DAFAF504037BA58F76E728CD887',\
'2EEC41510A864256A8BE93BF9F3D5276',\
'987A08A3797147949528FEF5D7C0E90C')\
AND tbfd.details_type in ('104022','104028','104038')\
AND tbu.sys_flag!=2\
GROUP BY DATE(tbfd.create_date),tbm.product_name;"
cur.execute(sql_online_invest, [self.old_start_date, self.endate])
online = pd.DataFrame(list(cur.fetchall()),
columns=['type', 'date', 'invest'])
cur.execute(sql_offline_invest, [self.old_start_date, self.endate])
offline = pd.DataFrame(list(cur.fetchall()),
columns=['type', 'date', 'invest'])
online_coin_purse_invest = online[online.iloc[:, 0] == '零钱包'].iloc[:,
1:]
# match1_invest = online[online.iloc[:, 0] == '火柴计划产品'].iloc[:, 1:]
# match2_invest = online[online.iloc[:, 0] == '火柴计划二号'].iloc[:, 1:]
offline_coin_purse_invest = offline[offline.iloc[:,
0] == '零钱包'].iloc[:,
1:]
print('loading coin_purse_interest...')
sql_online_purse_interest = \
"SELECT DATE(mi.create_date),SUM(mi.interest) FROM t_bus_match_three_interest mi\
LEFT JOIN t_bus_user u ON u.user_name=mi.user_name\
WHERE u.role_id not in ('ordinary','counselor','special')\
AND DATE(mi.create_date)>=%s\
AND DATE(mi.create_date)<=%s\
AND u.sys_flag!=2\
GROUP BY DATE(mi.create_date);"
sql_offline_purse_interest = \
"SELECT DATE(mi.create_date),SUM(mi.interest) FROM t_bus_match_three_interest mi\
LEFT JOIN t_bus_user u ON u.user_name=mi.user_name\
WHERE u.role_id in ('ordinary','counselor')\
AND DATE(mi.create_date)>=%s\
AND DATE(mi.create_date)<=%s\
and mi.user_name not in ('AB4E6DAFAF504037BA58F76E728CD887',\
'2EEC41510A864256A8BE93BF9F3D5276',\
'987A08A3797147949528FEF5D7C0E90C')\
AND u.sys_flag!=2\
GROUP BY DATE(mi.create_date);"
cur.execute(sql_online_purse_interest,
[self.old_start_date, self.endate])
online_interest = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'interest'])
cur.execute(sql_offline_purse_interest,
[self.old_start_date, self.endate])
offline_interest = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'interest'])
print('loading coin_purse_withdraw...')
sql_online_purse_withdraw =\
"SELECT tbm.product_name,date(tbmw.create_date),SUM(tbmw.withdraw) \
FROM t_bus_match_withdraw tbmw\
LEFT JOIN t_bus_match tbm ON tbm.product_no=tbmw.product_no\
LEFT JOIN t_bus_user tbu ON tbu.user_name=tbmw.user_name\
WHERE tbu.role_id not in ('ordinary','counselor','special')\
AND tbu.sys_flag !=2 \
AND DATE(tbmw.create_date)>=%s\
AND DATE(tbmw.create_date)<=%s\
GROUP BY date(tbmw.create_date),tbm.product_name;"
sql_offline_withdraw = \
"SELECT tbm.product_name,date(tbmw.create_date),SUM(tbmw.withdraw) \
FROM t_bus_match_withdraw tbmw\
LEFT JOIN t_bus_match tbm ON tbm.product_no=tbmw.product_no\
LEFT JOIN t_bus_user tbu ON tbu.user_name=tbmw.user_name\
WHERE tbu.role_id in ('ordinary','counselor')\
and tbu.user_name not in ('AB4E6DAFAF504037BA58F76E728CD887',\
'2EEC41510A864256A8BE93BF9F3D5276',\
'987A08A3797147949528FEF5D7C0E90C')\
AND tbu.sys_flag !=2 \
AND DATE(tbmw.create_date)>=%s\
AND DATE(tbmw.create_date)<=%s\
GROUP BY date(tbmw.create_date),tbm.product_name;"
cur.execute(sql_online_purse_withdraw,
[self.old_start_date, self.endate])
online_withdraw = pd.DataFrame(list(cur.fetchall()),
columns=('type', 'date', 'withdraw'))
cur.execute(sql_offline_withdraw, [self.old_start_date, self.endate])
offline_withdraw = pd.DataFrame(list(cur.fetchall()),
columns=('type', 'date', 'withdraw'))
cur.close()
online_purse_withdraw = online_withdraw[online_withdraw.type ==
'零钱包'].iloc[:, 1:]
offline_purse_withdraw = offline_withdraw[offline_withdraw.type ==
'零钱包'].iloc[:, 1:]
no1_match_plan = online_withdraw[online_withdraw.type ==
'火柴计划产品'].iloc[:, 1:]
no2_match_plan = online_withdraw[online_withdraw.type ==
'火柴计划二号'].iloc[:, 1:]
purse_data = to_timeseries(self.old_start_date, self.endate,
online_coin_purse_invest, online_interest,
online_purse_withdraw,
offline_coin_purse_invest, offline_interest,
offline_purse_withdraw, no1_match_plan,
no2_match_plan)
return purse_data
def old_fixed(self):
cur = sql_connection()
print('loading old fixed invest...')
sql_invest = \
"SELECT date(d.create_date),f.finance_type,SUM(d.expenditure) FROM t_bus_financial_details d\
LEFT JOIN t_bus_user u ON d.user_name=u.user_name\
LEFT JOIN t_bus_finance f ON d.product_no=f.finance_no\
WHERE date(d.create_date)>=%s\
AND date(d.create_date)<=%s\
AND d.details_type='104042'\
AND u.sys_flag!=2\
AND u.role_id not in ('ordinary','counselor','special')\
GROUP BY date(d.create_date),f.finance_type;"
cur.execute(sql_invest, [self.old_start_date, self.endate])
fixed_invest = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'type', 'invest'])
i_xinshoubiao = fixed_invest[fixed_invest.type == '106024'].drop(
'type', axis=1)
i_lianliankan = fixed_invest[fixed_invest.type == '106020'].drop(
'type', axis=1)
i_duiduipeng = fixed_invest[fixed_invest.type == '106022'].drop('type',
axis=1)
i_lulushun = fixed_invest[fixed_invest.type == '106023'].drop('type',
axis=1)
i_mantanghong = fixed_invest[fixed_invest.type == '106026'].drop(
'type', axis=1)
i_nianyueli360 = fixed_invest[fixed_invest.type == '106019'].drop(
'type', axis=1)
i_xinshouzhuan = fixed_invest[fixed_invest.type == '300000'].drop(
'type', axis=1)
i_yueyuezhuan = fixed_invest[fixed_invest.type == '300001'].drop(
'type', axis=1)
i_jijizhuan = fixed_invest[fixed_invest.type == '300002'].drop('type',
axis=1)
i_bannianzhuan = fixed_invest[fixed_invest.type == '300003'].drop(
'type', axis=1)
i_huodongbiao = fixed_invest[fixed_invest.type == '106027'].drop(
'type', axis=1)
sql_withdraw = \
"SELECT DATE(shuhui.date),tbf.finance_type,SUM(tbfd.expenditure) FROM t_bus_financial_details tbfd\
LEFT JOIN t_bus_user tbu ON tbfd.user_name=tbu.user_name\
LEFT JOIN t_bus_finance tbf ON tbfd.product_no=tbf.finance_no\
LEFT JOIN (\
SELECT date(lock_over_date) date,finance_no FROM t_bus_finance \
WHERE date(lock_over_date)<=%s\
) shuhui ON shuhui.finance_no=tbf.finance_no\
WHERE tbu.sys_flag!=2\
AND tbfd.details_type='104042'\
AND shuhui.date is NOT NULL\
AND DATE(tbfd.create_date)>=%s\
AND DATE(tbfd.create_date)<=%s\
AND tbu.role_id not in ('ordinary','counselor','special')\
GROUP BY DATE(shuhui.date),tbf.finance_type;"
sql_chuchuzhuan_withdraw = \
"SELECT DATE(tbfc.modify_date),tbfc.finance_type,SUM(tbfc.has_repay_amount) \
FROM t_bus_finance_collection tbfc\
left join t_bus_user u on u.user_name=tbfc.user_name\
LEFT JOIN t_bus_finance tbf ON tbfc.finance_no=tbf.finance_no\
WHERE date(tbfc.modify_date)>=%s\
and date(tbfc.modify_date)<=%s\
AND tbf.finace_parent_type='chuchuzhuan'\
and u.role_id not in ('counselor','ordinary','special')\
GROUP BY DATE(tbfc.modify_date),tbfc.finance_type;"
sql_nianyueli_interest = \
"SELECT DATE(tbfc.modify_date),SUM(tbfc.has_repay_interest)FROM t_bus_finance_collection tbfc\
left join t_bus_user u on u.user_name=tbfc.user_name\
WHERE date(tbfc.modify_date)>=%s\
and date(tbfc.modify_date)<=%s\
AND tbfc.finance_type='106019'\
AND u.role_id not in ('ordinary','counselor','special')\
GROUP BY DATE(tbfc.modify_date);"
sql_yueyuejia = \
"SELECT DATE(shuhui.create_date),SUM(income),SUM(mli.amount),SUM(mli.interest) FROM(\
SELECT user_name,create_date,income FROM t_bus_financial_details tbfd\
WHERE tbfd.details_type='104037')shuhui\
LEFT JOIN t_bus_match_link_invest mli \
ON shuhui.user_name=mli.user_name AND shuhui.create_date=mli.clearing_date\
LEFT JOIN t_bus_user tbu ON tbu.user_name=shuhui.user_name\
WHERE date(mli.clearing_date)>=%s\
and date(mli.clearing_date)<=%s\
AND tbu.sys_flag!=2\
and tbu.role_id not in ('ordinary','counselor','special')\
GROUP BY DATE(shuhui.create_date);"
sql_yueyuejia_vol = \
"SELECT SUM(mli.amount),SUM(mli.interest) FROM t_bus_match_link_invest mli\
LEFT JOIN t_bus_financial_details tbfd \
ON mli.product_no=tbfd.product_no AND mli.create_date=tbfd.create_date and mli.user_name=tbfd.user_name\
WHERE (mli.clearing_date>%s OR mli.clearing_date IS NULL)\
AND mli.user_name in (" \
"select user_name from t_bus_user where role_id in ('cgOrdinary', 'cgSuper'));"
sql_old_mean_annual_rate = \
"SELECT SUM(a.`存量利率积`)/SUM(a.`存量和`) FROM (\
SELECT remain.finance_type,SUM(remain.sum) 存量和,remain.`存量利率积` FROM(\
SELECT f.finance_type,SUM(i.total_amount) sum,ROUND(SUM(f.apr/100*i.total_amount),2)存量利率积\
FROM t_bus_finance_invest i\
LEFT JOIN t_bus_finance f ON i.finance_no=f.finance_no\
left join t_bus_user u on u.user_name=i.user_name\
WHERE date(f.lock_over_date)>%s\
AND date(i.create_date)<=%s\
AND u.role_id in ('cgOrdinary','cgSuper')\
AND f.finance_type NOT in('300000','300001','300002','300003')\
GROUP BY f.finance_type\
UNION\
SELECT m.product_type,SUM(m3i.clearing_amount),ROUND(SUM(m.apr/100*m3i.clearing_amount),2)\
FROM t_bus_match_three_interest m3i\
LEFT JOIN t_bus_match m ON m3i.product_no=m.product_no\
left join t_bus_user u on u.user_name=m3i.user_name\
WHERE DATE(m3i.create_date)=DATE(%s)\
AND u.role_id in ('cgOrdinary','cgSuper')\
GROUP BY m.product_type\
)remain\
GROUP BY remain.finance_type)a;"
cur.execute(sql_withdraw,
[self.endate, self.to_the_beginning, self.endate])
fixed_withdraw = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'type', 'withdraw'])
cur.execute(sql_chuchuzhuan_withdraw,
[self.old_start_date, self.endate])
chuchuzhuan_withdraw = pd.DataFrame(
list(cur.fetchall()), columns=['date', 'type', 'withdraw'])
cur.execute(sql_nianyueli_interest, [self.old_start_date, self.endate])
interest_nianyueli = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'interest'])
cur.execute(sql_yueyuejia, [self.to_the_beginning, self.endate])
yueyuejia = pd.DataFrame(
list(cur.fetchall()),
columns=['date', 'invest', 'withdraw', 'interest'])
yueyuejia_slice_date = datetime.date(int(self.old_start_date[:4]),
int(self.old_start_date[5:7]),
int(self.old_start_date[8:]))
yueyuejia_main = yueyuejia[yueyuejia.date >= yueyuejia_slice_date]
yueyuejia_withdraw = yueyuejia_main.loc[:, ['date', 'withdraw']]
w_xinshoubiao = fixed_withdraw[fixed_withdraw.type == '106024'].drop(
'type', axis=1)
w_lianliankan = fixed_withdraw[fixed_withdraw.type == '106020'].drop(
'type', axis=1)
w_duiduipeng = fixed_withdraw[fixed_withdraw.type == '106022'].drop(
'type', axis=1)
w_lulushun = fixed_withdraw[fixed_withdraw.type == '106023'].drop(
'type', axis=1)
w_mantanghong = fixed_withdraw[fixed_withdraw.type == '106026'].drop(
'type', axis=1)
w_nianyueli360 = fixed_withdraw[fixed_withdraw.type == '106019'].drop(
'type', axis=1)
w_xinshouzhuan = chuchuzhuan_withdraw[chuchuzhuan_withdraw.type ==
'300000'].drop('type', axis=1)
w_yueyuezhuan = chuchuzhuan_withdraw[chuchuzhuan_withdraw.type ==
'300001'].drop('type', axis=1)
w_jijizhuan = chuchuzhuan_withdraw[chuchuzhuan_withdraw.type ==
'300002'].drop('type', axis=1)
w_bannianzhuan = chuchuzhuan_withdraw[chuchuzhuan_withdraw.type ==
'300003'].drop('type', axis=1)
w_huodongbiao = fixed_withdraw[fixed_withdraw.type == '106027'].drop(
'type', axis=1)
old_invest = to_timeseries(
self.old_start_date, self.endate, i_xinshoubiao, w_xinshoubiao,
i_lianliankan, w_lianliankan, i_duiduipeng, w_duiduipeng,
i_lulushun, w_lulushun, i_mantanghong, w_mantanghong,
i_nianyueli360, w_nianyueli360, interest_nianyueli, i_xinshouzhuan,
w_xinshouzhuan, i_yueyuezhuan, w_yueyuezhuan, i_jijizhuan,
w_jijizhuan, i_bannianzhuan, w_bannianzhuan, i_huodongbiao,
w_huodongbiao, yueyuejia_withdraw)
label = [
'date', 'i_xinshoubiao', 'w_xinshoubiao', 'i_lianliankan',
'w_lianliankan', 'i_duiduipeng', 'w_duiduipeng', 'i_lulushun',
'w_lulushun', 'i_mantanghong', 'w_mantanghong', 'i_nianyueli360',
'w_nianyueli360', 'interest_nianyueli', 'i_xinshouzhuan',
'w_xinshouzhuan', 'i_yueyuezhuan', 'w_yueyuezhuan', 'i_jijizhuan',
'w_jijizhuan', 'i_bannianzhuan', 'w_bannianzhuan', 'i_huodongbiao',
'w_huodongbiao', 'yueyuejia_withdraw'
]
# old_invest.set_axis(axis=1, labels=label)
old_invest.columns = label
# other statistics:
print('loading other old statistics...')
cur.execute(sql_nianyueli_interest,
[self.to_the_beginning, self.old_day_before_start])
nianyueli_cumminterest = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'interest'])
nianyueli_cumminterest = float(sum(nianyueli_cumminterest.interest))
cur.execute(sql_invest,
[self.to_the_beginning, self.old_day_before_start])
chuchuzhuan_cumminvest = pd.DataFrame(
list(cur.fetchall()), columns=['date', 'type', 'invest'])
cur.execute(sql_chuchuzhuan_withdraw,
[self.to_the_beginning, self.old_day_before_start])
chuchuzhuan_cummwithdraw = pd.DataFrame(
list(cur.fetchall()), columns=['date', 'type', 'withdraw'])
cur.execute(sql_yueyuejia_vol, self.old_day_before_start)
yueyuejia_vol = cur.fetchall()
yueyuejia_vol = float(yueyuejia_vol[0][0])
cur.execute(sql_old_mean_annual_rate,
[self.endate, self.endate, self.endate])
old_mean_annual_rate = cur.fetchone()[0]
cur.close()
huodongbiao_cumminvest = chuchuzhuan_cumminvest[
chuchuzhuan_cumminvest.type == '106027']
huodongbiao_cummwithdraw = chuchuzhuan_cummwithdraw[
chuchuzhuan_cummwithdraw.type == '106027']
huodongbiao_vol = float(
sum(huodongbiao_cumminvest.invest) -
sum(huodongbiao_cummwithdraw.withdraw))
xinshouzhuan_cumm = chuchuzhuan_cumminvest[chuchuzhuan_cumminvest.type
== '300000']
xinshouzhuan_cumminvest = float(xinshouzhuan_cumm.invest.sum())
xinshouzhuan_cumm = chuchuzhuan_cummwithdraw[
chuchuzhuan_cummwithdraw.type == '300000']
xinshouzhuan_cummwithdraw = float(xinshouzhuan_cumm.withdraw.sum())
yueyuezhuan_cumm = chuchuzhuan_cumminvest[chuchuzhuan_cumminvest.type
== '300001']
yueyuezhuan_cumminvest = float(yueyuezhuan_cumm.invest.sum())
yueyuezhuan_cumm = chuchuzhuan_cummwithdraw[
chuchuzhuan_cummwithdraw.type == '300001']
yueyuezhuan_cummwithdraw = float(yueyuezhuan_cumm.withdraw.sum())
jijizhuan_cumm = chuchuzhuan_cumminvest[chuchuzhuan_cumminvest.type ==
'300002']
jijizhuan_cumminvest = float(jijizhuan_cumm.invest.sum())
jijizhuan_cumm = chuchuzhuan_cummwithdraw[chuchuzhuan_cummwithdraw.type
== '300002']
jijizhuan_cummwithdraw = float(jijizhuan_cumm.withdraw.sum())
bannianzhuan_cumm = chuchuzhuan_cumminvest[chuchuzhuan_cumminvest.type
== '300003']
bannianzhuan_cumminvest = float(bannianzhuan_cumm.invest.sum())
bannianzhuan_cumm = chuchuzhuan_cummwithdraw[
chuchuzhuan_cummwithdraw.type == '300003']
bannianzhuan_cummwithdraw = float(bannianzhuan_cumm.withdraw.sum())
cumm_stats = [
old_mean_annual_rate, nianyueli_cumminterest,
xinshouzhuan_cumminvest, xinshouzhuan_cummwithdraw,
yueyuezhuan_cumminvest, yueyuezhuan_cummwithdraw,
jijizhuan_cumminvest, jijizhuan_cummwithdraw,
bannianzhuan_cumminvest, bannianzhuan_cummwithdraw,
huodongbiao_vol, yueyuejia_vol
]
cumm_label = [
'old_mean_annual_rate', 'nianyueli_cumminterest',
'xinshouzhuan_cumminvest', 'xinshouzhuan_cummwithdraw',
'yueyuezhuan_cumminvest', 'yueyuezhuan_cummwithdraw',
'jijizhuan_cumminvest', 'jijizhuan_cummwithdraw',
'bannianzhuan_cumminvest', 'bannianzhuan_cummwithdraw',
'huodongbiao_vol', 'yueyuejia_vol'
]
return old_invest, cumm_stats, cumm_label
to_the_beginning = '2000-01-01'
month = endate[:8] + '01'
# weeknum = datetime.datetime.strptime(self.endate,'%Y-%m-%d')
report = WeeklyReportSummary(old_start_date, cg_start_date, endate)
old_purse = report.old_coin_purse()
old_fixed_invest, old_commulate, commlabel = report.old_fixed()
cg_fixed = report.cg_fixed()
cg_current = report.cg_current()
cg_chuxin_plan = report.cg_chuxin_plan()
cg_new_reginvest, cg_statnum, cg_statlabel = report.cg_other_statistic()
mytool = MyPath()
path = mytool.path
cg_report = to_timeseries(cg_start_date, endate, cg_fixed, cg_current,
cg_chuxin_plan)
cg_report.to_csv(path + '\cg_data.csv', header=True, index=False)
old_app = to_timeseries(old_start_date, endate, old_purse,
old_fixed_invest)
old_app.to_csv(path + '\old_app_data.csv', header=True, index=False)
cg_new_reginvest.to_csv(path + '\cg_new_reginvest.csv',
header=True,
index=False)
mytool.to_csv('cg_statistic', [cg_statnum], cg_statlabel)
mytool.to_csv('old_app_begin_commulate_data', [old_commulate], commlabel)
print('All weekly data extract finished.')
LEFT JOIN cg_user_chuxin_planinfo p ON d.planinfo_id=p.id
WHERE d.disabled=0
AND d.invest_flag=1
AND p.is_quite=1
GROUP BY DATE(p.quite_time);"""
cur.execute(sql_chuxin_invest)
cx_inv = cur.fetchall()
cur.execute(sql_chuxin_withdraw)
cx_wd = cur.fetchall()
cur.close()
cx_inv = pd.DataFrame(list(cx_inv), columns=['date', 'invest'])
cx_wd = pd.DataFrame(list(cx_wd), columns=['date', 'withdraw'])
cx = to_timeseries('2017-12-01', '2018-03-22', cx_inv, cx_wd)
cx = cx.assign(vol=lambda x: x.invest - x.withdraw)
cx_inv.invest = cx_inv.invest.astype('float')
cx_wd.withdraw = cx_wd.withdraw.astype('float')
cx_inv.plot()
cx_wd.plot()
#####################################
#introducing ARIMA model:
cx.set_index('date', inplace=True, drop=True)
cx = cx.loc[:, 'vol']
plot_acf(cx).show()
#返回值依次为adf、pvalue、usedlag、nobs、critical values、icbest、regresults、resstore
acorr_ljungbox(cx, lags=3)
GROUP BY DATE(create_time);'''
cur.execute(sql_withdraw_success)
cg_withdraw = pd.DataFrame(list(cur.fetchall()), columns=['date', 'withdraw'])
cur.execute(sql_withdrawable)
cg_withdrawable = pd.DataFrame(list(cur.fetchall()),
columns=['date', 'withdrawable'])
cur.close()
cg_withdraw.to_csv(
r'E:\Trustsaving\Python\retain_fund_predict\raw_data\cg_withdraw.csv',
index=False)
cg_withdrawable.to_csv(
r'E:\Trustsaving\Python\retain_fund_predict\raw_data\cg_withdrawable.csv',
index=False)
withdraw = to_timeseries('2017-11-20', '2018-01-21', cg_withdraw,
cg_withdrawable)
#####################################
#drop white noise, dimention interference:
withdraw.index = withdraw.date
withdraw.drop('date', axis=1, inplace=True)
#withdraw.loc['2018-01-08', 'withdraw'] = 1394326.78
withdraw.loc['2018-01-09', 'withdraw'] = 1394326.78
withdraw.loc[:, 'withdraw_rate'] = withdraw.withdraw / withdraw.withdrawable
withdraw.plot()
withdraw_rate = withdraw.loc[:, 'withdraw_rate']
withdraw_rate.plot()
#####################################
#drop data with no reference value:
withdraw_rate.drop(withdraw_rate.index[withdraw_rate == 0], inplace=True)