# -*- coding: utf-8 -*-
# # 开始匹配各个放款表
from threading import Thread

import datetime
import pandas as pd
from sqlalchemy import create_engine
import multiprocessing as mtp

pd.options.mode.chained_assignment = None
import sys

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

# --------------------配置变量-------------------------------


# file_path = u'/vpants/svn_loan_xjd_txt/'
file_path = u'E:/out/现金贷放款表明细/'
sql_funding_corp = """select funding_code, name '资金方' from audit.funding_corp;"""

sql_loan_account = """select id loan_account_id, accountName '放款渠道' from audit.loan_account;"""

sql_loan = """SELECT lm.funding_code 'funding_code', lc.class 客户等级,lm.loan_id 'loan_id',loan_paid_at '放款日期',
lm.real_loan_amount '放款金额',lm.loan_account_id 'loan_account_id',lm.contract_loan_amount '本金（债权）',
lm.qg_one_time_service_fee '一次性手续费（QG）',lm.funding_one_time_service_fee '一次性手续费（点滴）',lm.contract_term '贷款总期数',
lm.per_term_service_fee '每期手续费',lm.monthly_interest_rate '月利率' FROM loan_manifest lm JOIN temp.loan_class lc ON 
lm.ref_id = lc.ref_id WHERE lm.loan_paid_at >= '%s' AND lm.loan_paid_at < '%s' AND lm.funding_code = %d 
AND lm.is_active in (1,-2)  ;"""

step = 5000
columns = [u'资金方', u'客户等级', u'loan_id', u'放款日期', u'放款金额', u'放款渠道', u'本金（债权）', u'一次性手续费（QG）', u'一次性手续费（点滴）',
           u'贷款总期数', u'每期手续费', u'月利率']
loan_start = datetime.datetime(2015, 1, 1)
loan_end = datetime.datetime(2017, 1, 1)
con_audit = create_engine(
    'mysql+mysqldb://yulong_rw:TouBStYwN8wkdxVt@172.16.3.201:3306/all_back_0430?charset=utf8', echo=False)


def get_fund_details(fund_code, fund_name, df_fund_corp, df_loan_account):
    df_loan_gen = pd.read_sql(sql=sql_loan % (loan_start, loan_end, fund_code), con=con_audit, chunksize=step)
    res = []
    for tmp in df_loan_gen:
        res.append(tmp)
    try:
        df_loan = pd.concat(res)
        df_loan['loan_id'] = df_loan['loan_id'].astype(str)
        df_loan['funding_code'] = df_loan['funding_code'].astype(int)
        df_loan['loan_account_id'] = df_loan['loan_account_id'].astype(int)
        df_loan = df_loan.merge(df_fund_corp, on='funding_code', how='left')
        df_loan = df_loan.merge(df_loan_account, on='loan_account_id', how='left')
        df_loan.drop(['funding_code', 'loan_account_id'], axis=1, inplace=True)
        df_loan[u'放款日期'] = df_loan[u'放款日期'].apply(lambda x: pd.datetime.strftime(x, '%Y-%m-%d %H:%M'))
        df_loan.to_excel(file_path + u'现金贷放款明细(%s)-%s年-%s年.xlsx' % (fund_name, loan_start.year, loan_end.year - 1),
                         index=None, columns=columns)
    except Exception, e:
        print 'error occurs when getting funding %s\'s details. \n' % fund_name, str(e)


if __name__ == '__main__':
    pool = mtp.Pool(processes=6)
    df_fund_corp = pd.read_sql(sql=sql_funding_corp, con=con_audit)
    df_loan_account = pd.read_sql(sql=sql_loan_account, con=con_audit)
    fundcodes = df_fund_corp.values.tolist()
    for (fund_code, fund_name) in fundcodes:
        pool.apply_async(get_fund_details, (fund_code, fund_name, df_fund_corp, df_loan_account))
    pool.close()
    pool.join()
    print '------------------all done!----------------------'
