# -*- encoding: utf8 -*-
import sys
import datetime
import pandas as pd
from sqlalchemy import create_engine
from dateutil.relativedelta import relativedelta
from itertools import product
import os

reload(sys)
sys.setdefaultencoding("utf-8")
pd.options.mode.chained_assignment = None

# 此路径为服务器路径专用
# path = u'./hanzhenghui/'
path = u'E:/审计出表/人工控制/现金贷-还资金方表-1010版本/'
engine_xjd = create_engine('mysql+mysqldb://internal_r:ArbNgtvlJzZHXsEu@172.16.4.60:3306/?charset=utf8',
                           echo=False).connect()

max_limit = 800000

sql_lm = '''
select ref_id,loan_id,fc.name '资金方',date(loan_paid_at) '放款时间',contract_term '期数'
from all_back_0831.loan_manifest lm
join basic.funding_corp fc on fc.funding_code = lm.funding_code
where loan_paid_at>='%s' and loan_paid_at <'%s'
and is_active in (1, -2)
'''

# 获取还资金方计划，分为两部分，这5个资金方使用的是用户还款计划，user_repayment_plan
sql_repay_frr_2 = '''
select
    urp.ref_id,
    urp.term_no '应还期数',
    date(urp.deadline) '应还资金方时间',
    urp.principle+urp.interest  '应还资金方本息'
from all_back_0831.user_repayment_plan urp
    join all_back_0831.loan_manifest lm on lm.ref_id = urp.ref_id
where lm.loan_paid_at>='%s' and loan_paid_at<'%s'
and urp.fund_code in (210,270,310,320,410,430)	
'''

# 获取还资金方计划2，其余部分从test_shiyao.funding_repayment_plan中获取，如果在获取过程中出现丢失，可以查看是否数据库缺失
sql_repay_frr_3 = '''
select 
    frp.ref_id,
    frp.term_no '应还期数',
    date(frp.deadline) '应还资金方时间',
    frp.interest+frp.principle '应还资金方本息'

from audit.funding_repayment_plan frp 
    join all_back_0831.loan_manifest lm on lm.ref_id = frp.ref_id
WHERE lm.loan_paid_at>='%s' and loan_paid_at<'%s'
and lm.funding_code not in (210,270,310,320,410,430)
'''

# 获取实还资金方信息，目前从audit.funding_repayment_record获取，可看后续是否需要修改
sql_refund = '''
SELECT
    frr.ref_id,
    date(frr.repaid_at) '实还时间',
    sum(frr.current_repayment) '实还总额',
    sum(frr.principle) '实还本金',
    sum(frr.interest) '实还利息',
    frr.flow_id '流水号',
    frr.term_no '应还期数'
FROM
    all_back_0831.funding_repayment_record frr
join all_back_0831.loan_manifest lm on lm.ref_id = frr.ref_id
JOIN basic.funding_corp fc ON fc.funding_code = frr.funding_code
WHERE lm.loan_paid_at>='%s' 
  and lm.loan_paid_at <'%s'
AND frr.is_active = 1
GROUP BY 1,frr.term_no
'''

sql_flow_id_reamount = '''
select flow_id,ref_amount
from basic.lkb_to_fund_ref
'''


# df_flow_id.to_csv(u'E:/flow_id对应银行金额.csv',encoding='utf8')


def refund(year, month):
    # year,month = 2017,9

    df_flow_id_reamount = pd.read_sql(sql_flow_id_reamount, con=engine_xjd)

    start_time = datetime.datetime(year, month, 1, 0, 0, 0)
    end_time = start_time + relativedelta(months=+1)

    df_all = pd.read_sql(sql_refund % (start_time, end_time), con=engine_xjd)

    # 实还部分进行去重
    df_all = df_all.drop_duplicates([u'ref_id', u'实还时间', u'实还本金', u'实还总额', u'实还利息', u'流水号', u'应还期数'])

    df_all['ref_id'] = df_all['ref_id'].astype('int64')
    df_all = df_all.drop_duplicates()

    # 放款部分
    df_lm = pd.read_sql(sql_lm % (start_time, end_time), engine_xjd)
    df_lm['ref_id'] = df_lm['ref_id'].astype('int64')
    df_lm = df_lm.drop_duplicates()

    # 实还资金方部分代码
    # df_repay_frr_1 = pd.read_sql(sql_repay_frr_1 % (start_time, end_time), con=engine_xjd)
    df_repay_frr_2 = pd.read_sql(sql_repay_frr_2 % (start_time, end_time), con=engine_xjd)
    df_repay_frr_3 = pd.read_sql(sql_repay_frr_3 % (start_time, end_time), con=engine_xjd)
    df_repay_frr = pd.concat([df_repay_frr_2, df_repay_frr_3])

    df_repay_frr['ref_id'] = df_repay_frr['ref_id'].astype('int64')
    df_repay_frr[u'应还期数'] = df_repay_frr[u'应还期数'].astype('int64')

    df_flow_id_reamount.rename(columns={u'ref_amount': u'银行金额', u'flow_id': u'流水号'}, inplace=True)

    df_repay_frr = df_repay_frr.drop_duplicates()

    # sql_lm是一段时间内放款计划，在merge的过程中，由于df_repay_frr中有未还款情况，因此需要先合并还款计划，再对还款记录进行合并
    df_res = pd.merge(df_lm, df_repay_frr, on='ref_id', how='left')
    df_res = pd.merge(df_res, df_all, on=['ref_id', u'应还期数'], how='left')

    df_res[u'流水号'] = df_res[u'流水号'].fillna(0).astype(str)
    df_flow_id_reamount[u'流水号'] = df_flow_id_reamount[u'流水号'].astype(str)

    df_res = pd.merge(df_res, df_flow_id_reamount, on=u'流水号', how='left')

    df_res = df_res[~df_res[u'实还总额'].isnull()]
    df_res = df_res[~df_res[u'流水号'].isnull()]

    df_res.fillna(0, inplace=True)
    df_res[u'银行金额'] = df_res[u'银行金额'].fillna(0)

    a = (df_res[u'银行金额'] == 0) & (df_res[u'资金方'] == u'湖北消金')
    df_res.loc[a, u'银行总额'] = df_res.loc[a, u'实还本金'] + df_res.loc[a, u'实还利息']

    df_res.loc[(df_res[u'银行金额'] == 0), u'银行总额'] = df_res[u'实还总额'].fillna(0)

    df_res[u'唯一编号'] = df_res[u'loan_id'].astype('str') + df_res[u'应还期数'].fillna(0).astype('int').astype('str')

    df = df_res.fillna(0)

    df[u'应还资金方本息-实还资金方本息'] = df[u'应还资金方本息'].fillna(0) - df[u'实还本金'].fillna(0) - df[u'实还利息'].fillna(0)

    columns = [u'唯一编号', u'资金方', u'loan_id', u'放款时间', u'期数', u'实还时间', u'实还总额', u'实还本金', u'实还利息',
               u'流水号', u'银行金额', u'应还期数', u'应还资金方时间', u'应还资金方本息', u'应还资金方本息-实还资金方本息']

    print(u'实还总额:' + str(sum(df[u'实还总额'])) + u' 应还资金方本息:' + str(
        sum(df[u'应还资金方本息'])) + u' 应还资金方本息-实还资金方本息' + str(
        sum(df[u'应还资金方本息-实还资金方本息'])))

    if len(df) > max_limit:
        for i in xrange(0, len(df), max_limit):
            df[i:i + max_limit].to_excel(os.path.join(path, u'0927现金贷—还资金方表-(%s-%02d)-%d.xlsx' % (
                year, month, i / max_limit + 1)), index=None, columns=columns)
    else:
        df.to_excel(path + u'人工控制-现金贷—还资金方表-(%d-%d)-V4.xlsx' % (year, month), index=None, columns=columns)


if __name__ == '__main__':
    year = [2017]
    month = range(1, 2, 1)

    for year, month in product(year, month):
        print('-----------%s-%s begins') % (year, month)
        refund(year, month)
        print('-----------%s-%s ends') % (year, month)
        print('----------------------')

print '---------mian-----------'
