# -*- coding: utf-8 -*-
import datetime
import sys

import pandas as pd
from sqlalchemy import create_engine


reload(sys)
sys.setdefaultencoding('utf8')

# 注意修改数据源
engine_new_transaction2016 = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/qunaer?charset=utf8',
    echo=True)
engine_new_transaction2017 = create_engine(
    'mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.3.201:3306/qunaer_new?charset=utf8',
    echo=True)
sql='''select p.product_no,p.loan_time,sum(p.supposed_principle_rule) principle
from qunaer.repayment_plan_20160930 p
  left join
    (select
      product_no,
      current_stage_no,
      real_repay_principle
    from
      qunaer.repay_refund_split_deyu
    where current_stage_no > 0
      and date(real_repay_date) <= '2016-9-30') r
    on p.product_no = r.product_no
    and p.current_stage_no = r.current_stage_no
where r.real_repay_principle is null 
group by 1,2
having principle > 0 '''

df=pd.read_sql(sql,con=engine_new_transaction2016)

sql2='''
SELECT product_no,amount FROM
(SELECT t1.product_no,-SUM(t1.refund_principle) amount FROM refund_detail t1
JOIN pay_detail t2 ON t1.product_no=t2.product_no AND t2.trans_time<'2016-10-01'
WHERE t1.refund_time <'2017-8-01' AND t1.refund_time>='2016-10-01'
GROUP BY 1
UNION
SELECT r.product_no,SUM(r.repay_principle) amount FROM `repay_detail` r
JOIN `pay_detail` pay ON r.product_no = pay.`product_no` and pay.trans_time <'2016-10-01'
WHERE
r.`repay_time` <'2017-08-01'  and r.repay_time>= '2016-10-01'
group by 1) a
GROUP BY 1
;
'''

df2=pd.read_sql(sql2,con=engine_new_transaction2017)
df3=pd.merge(df,df2,how='left',on='product_no')
df3.to_excel(u'D:\\data\\9.27—20160930去哪白条在贷贷后表现.xlsx', index=None)


