#--放款导出======导出文件名  loan_2016.csv
select ref_id,loan_id,date(loan_paid_at) loan_paid_at,funding_code from all_back_0630.loan_manifest
WHERE is_active in (1,-2) AND  loan_paid_at < '2017-01-01';

#还资金方导出- ACD======导出文件名  ACD_2016.csv
select ref_id,term_no,funding_code,date(deadline) fund_deadline,date(deadline) fund_repaid_at,principle+interest fund_prin_inst
from audit.funding_repayment_record_acdz where funding_code in (50,80,130) AND
 deadline < '2017-01-01';
#还资金方导出- 其他资金方======导出文件名  fund_2016.csv
select ref_id,term_no,funding_code,date(deadline) fund_deadline,date(repaid_at) fund_repaid_at,principle+interest fund_prin_inst
from test_linfang.funding_repayment_record
where is_active = 1 AND funding_code not in (1,2,3,4,5,210,270,310,320) AND
 deadline <  '2017-01-01';
#还资金方导出- 渤海广源使用还款计划======导出文件名  plan_2016.csv
select ref_id,term_no ,fund_code funding_code,date(deadline) fund_deadline,date(deadline) fund_deadline,principle+interest fund_prin_inst
from all_back_0630.user_repayment_plan
where fund_code in (1,2,3,4,5,210,270,310,320) AND  deadline <  '2017-01-01';
#资金方导出--考拉 T+1，======导出文件名  fund_kaola_2016.csv fund_kjmx_{year}.csv
SELECT ref_id,term_no,funding_code,DATE(deadline) fund_deadline,DATE(repaid_at) fund_repaid_at,principle+interest fund_prin_inst
FROM test_linfang.funding_repayment_record
WHERE is_active = 1 AND funding_code = 170 AND DATE(deadline) =  '2017-01-01';
#资金方导出--口袋理财，橘子理财，米庄理财，小嬴理财 T+2======导出文件名  fund_kjmx_2016.csv
SELECT ref_id,term_no,funding_code,DATE(deadline) fund_deadline,DATE(repaid_at) fund_repaid_at,principle+interest fund_prin_inst
FROM test_linfang.funding_repayment_record
WHERE is_active = 1 AND funding_code IN (230,240,250,260) AND DATE(deadline) >=  '2017-01-01' AND DATE(deadline) < '2017-01-03';




#客户还款--线上还款======导出文件名  repay_online_2016.csv
SELECT ref_id,term_no,t2.fund_code,date(t2.deadline) deadline,date(t2.xyqb_repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,t1.punish-t1.mitigate_collection_relief real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 on t1.plan_id = t2.id AND t1.repay_channel < 13 AND
      t2.xyqb_repaid_at < '2017-01-01';
#客户还款--线下还款======导出文件名  repay_offline_2016.csv
SELECT ref_id,term_no,t2.fund_code,date(t2.deadline) deadline,date(t3.repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,t1.punish-t1.mitigate_collection_relief real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 on t1.plan_id = t2.id AND t1.repay_channel IN (13,14,15,16)
JOIN new_transaction.xjd_repay_plan_repay_record_ref_repay_date t3 on t1.id = t3.xjd_ref_id AND
    t3.repaid_at < '2017-01-01';

#华融客户还款---还资金方deadline在16年，还款在17年的======导出文件名 hr_repay_online_2016.csv
SELECT t2.ref_id,t2.term_no,t2.fund_code,DATE(t2.deadline) deadline,DATE(t2.xyqb_repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,t1.punish-t1.mitigate_collection_relief real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 ON t1.plan_id = t2.id AND t1.repay_channel < 13 AND
      t2.xyqb_repaid_at >= '2017-01-01' AND t2.`xyqb_repaid_at` < '2017-01-11' AND t2.`fund_code` = 150
      JOIN test_linfang.`funding_repayment_record` t3 ON t2.ref_id = t3.ref_id AND t2.`term_no` = t3.`term_no`
AND t3.`deadline` < '2017-01-01';
#华融客户还款---线下还款======导出文件名  hr_repay_offline_2016.csv
SELECT t2.ref_id,t2.term_no,t2.fund_code,date(t2.deadline) deadline,date(t3.repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,t1.punish-t1.mitigate_collection_relief real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 on t1.plan_id = t2.id AND t1.repay_channel IN (13,14,15,16) and t2.fund_code = 150
JOIN new_transaction.xjd_repay_plan_repay_record_ref_repay_date t3 on t1.id = t3.xjd_ref_id AND
    t3.repaid_at >= '2017-01-01' AND t3.repaid_at < '2017-01-11'
JOIN test_linfang.funding_repayment_record t4 on t2.ref_id = t4.ref_id and t2.term_no = t4.term_no and t4.deadline < '2017-01-01';
#考拉在2017.1.1 收取的还款；======导出文件名  kjmx_repay_online_2016.csv
SELECT t2.ref_id,t2.term_no,t2.fund_code,DATE(t2.deadline) deadline,DATE(t2.xyqb_repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,t1.punish-t1.mitigate_collection_relief real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 ON t1.plan_id = t2.id AND t1.repay_channel < 13 AND
      DATE(t2.xyqb_repaid_at) >= '2017-01-01'  AND
      DATE(t2.xyqb_repaid_at) < '2017-01-03'
      AND t2.`fund_code` IN (170,230,240,250,260)
     JOIN all_back_0630.`loan_manifest` t3 ON t2.ref_id = t3.ref_id AND t3.loan_paid_at < '2017-01-01';
#考拉在2017.1.1 收取的还款；======导出文件名  kjmx_repay_offline_2016.csv
SELECT t2.ref_id,t2.term_no,t2.fund_code,DATE(t2.deadline) deadline,DATE(t5.repaid_at) repaid_at,t1.principle+t1.interest-t1.mitigate_principle-t1.mitigate_interest real_prin_inst
,  t1.`punish`-t1.`mitigate_collection_relief` real_punish,t1.service_fee - t1.mitigate_service_fee real_service_fee
FROM all_back_0630.xjd_repay_plan_repay_record_ref t1
JOIN all_back_0630.user_repayment_plan t2 ON t1.plan_id = t2.id AND t1.repay_channel =13
      AND t2.`fund_code` IN (170,230,240,250,260)
JOIN all_back_0630.`loan_manifest` t3 ON t2.ref_id = t3.ref_id AND t3.loan_paid_at < '2017-01-01'
join new_transaction.xjd_repay_plan_repay_record_ref_repay_date t5 on t1.id = t5.xjd_ref_id AND
    t5.repaid_at >= '2017-01-01' AND t5.repaid_at < '2017-01-03';