# -*- coding:utf-8 -*-
# Author: Perry_Zhu

import pandas as pd

pd.options.mode.chained_assignment = None
import sys
from sqlalchemy import create_engine

reload(sys)
sys.setdefaultencoding('utf8')
engine_audit = create_engine('mysql+mysqldb://yulong:ohVU1nPITynSZwR2@172.16.4.60:3306/audit?charset=utf8',
                             echo=True)

query_repay = """
SELECT t1.ref_id,t1.term_no,t1.loan_id,t1.funding_code,min(t1.deadline)deadline,sum(t1.principle)principle,
sum(t1.interest)interest,(t1.funding_service_fee)funding_service_fee 
FROM funding_repayment_record t1
GROUP BY 1,2
"""
query_plan = """
select ref_id,term_no,id plan_id from funding_repayment_record 
"""

df_record = pd.read_sql(query_repay, con=engine_audit)
df_plan = pd.read_sql(query_plan, con=engine_audit)
df_record.dropna(axis=0, how='any', inplace=True)
df_plan.dropna(axis=0, how='any', inplace=True)
df_record['ref_id'] = df_record['ref_id'].astype(int)
df_record['term_no'] = df_record['term_no'].astype(int)
df_plan['ref_id'] = df_plan['ref_id'].astype(int)
df_plan['term_no'] = df_plan['term_no'].astype(int)
df = df_record.merge(df_plan, on=['ref_id', 'term_no'])
df = df.ix[df['plan_id'].notnull()]
df.drop(['plan_id'], axis=1, inplace=True)
df.to_sql(name='funding_repayment_plan', con=engine_audit, if_exists='append', index=None)
