# 校验是否有还款计划未生成
SELECT * FROM `pay_detail` t1
LEFT JOIN `repayment_plan_2017` t2 ON t1.`product_no` = t2.`product_no`
WHERE t1.`trans_time` >= '2017-09-01' AND  t1.status=1 AND t2.`product_no` IS NULL


#应还 ！= 实还; 如果出现 实还> 应还，那么查看还款计划是不是拆分错误，或者是不是有退款流水缺少等问题。
SELECT * FROM `repayment_plan_2017` WHERE repay_amount > 0 AND `principle` != `repay_principle`
SELECT * FROM `repayment_plan_2017` WHERE repay_amount > 0 AND `fee_amount`!= `repay_fee_amount`

# 如果有还款，则状态置为 2
SELECT * FROM `repayment_plan_2017` WHERE `repay_amount` > 0 AND STATUS = 1
# 转分期订单状态是没有还款的
SELECT * FROM `repayment_plan_2017` WHERE `repay_amount` > 0 AND STATUS = 3
# 还款为负
SELECT * FROM `repayment_plan_2017` WHERE `repay_amount` < 0

# 校验哈银的还款计划是否全
SELECT t1.product_no,loan_amount FROM `fund_pay_detail` t1
LEFT JOIN (
SELECT product_no,SUM(principle) principle FROM `fund_repayment_plan`
WHERE fund_code = 3
GROUP BY 1
) t2 ON t1.product_no = t2.product_no
WHERE t1.fund_code = 3 AND t2.product_no IS NULL

# 比较哈银的 放款金额 和应还本金和是否相等
SELECT SUM(principle) FROM `fund_repayment_plan` WHERE fund_code = 3
SELECT SUM(loan_amount) FROM `fund_pay_detail` WHERE fund_code = 3


# 通过总额比较 --- 如果总额不一样，可以比较明细
select sum(repay_amount) from qunaer_new.repay_detail where repay_status = 1
select sum(repay_amount) FROM qunaer_new.repayment_plan_2017
select sum(repay_amount) FROM qunaer_new.repayment_plan_2016

#还资金方表核对
SELECT * FROM `fund_repay_detail` WHERE repay_amount != repay_principle+repay_fee_amount+repay_due_amount


#============还资金方表校验开始===============================

#流水校验===重点
SELECT t1.flow_id,trans_amount FROM `bank_flow` t1
JOIN (
SELECT flow_id,SUM(repay_amount) repay_amount FROM `fund_repay_detail`
GROUP BY 1
) t2 ON t1.flow_id = t2.flow_id
WHERE t1.trans_amount != t2.repay_amount

#==失败的放款表不插入 资金方还款计划
select * FROM qunaer_new.fund_repayment_plan where product_no in (
SELECT product_no FROM `fund_pay_detail` WHERE STATUS=0
)

#==失败的放款，还资金方表中不应该有
SELECT * from qunaer_new.fund_repay_detail WHERE product_no in (
SELECT product_no FROM `fund_pay_detail` WHERE STATUS=0
)

#=还资金方的应还本金和===放款金额
SELECT t1.product_no,loan_amount FROM `fund_pay_detail`t1
LEFT JOIN (
SELECT product_no,SUM(principle) principle FROM `fund_repayment_plan`
GROUP BY 1 ) t2 ON t1.`product_no` = t2.product_no
WHERE t1.`loan_amount` != t2.principle
AND t1.`status` = 1

#=状态核对
SELECT product_no FROM `fund_repay_detail` WHERE TYPE IN (5,7) AND fund_code = 2
AND product_no NOT IN (
SELECT product_no FROM `fund_repayment_plan` WHERE STATUS IN (4,5)
)

#惠金所不贴息--同一天还款
SELECT * FROM `fund_repayment_plan` WHERE STATUS IN (4,5) AND fee_amount = 0 AND fund_code = 2

#还款总额=本利服罚
SELECT * FROM `fund_repay_detail` WHERE repay_amount != repay_principle+repay_interest+repay_fee_amount+repay_due_amount
#QG贴息==目前是1期推广，贴息；如果后期模式改变，这个SQL有问题
SELECT * FROM `fund_repay_detail` WHERE qg_interest > 0  AND repay_fee_amount != qg_interest

#============还资金方表校验END===============================


===============债转表校验开始==============================

#============step_1==================================
SELECT * FROM `baoli_pay_detail` WHERE loan_amount < 0

#============step_2==================================
#==主要作用是判断 应还本金和=放款-退款
SELECT c.product_no,c.loan_amount FROM
(
SELECT
  a.product_no,
  SUM(a.loan_amount)  loan_amount
FROM
  (
    SELECT product_no,loan_amount FROM `baoli_pay_detail`
    UNION ALL
    SELECT product_no,refund_principle loan_amount
    FROM `refund_detail` WHERE fund_code = 1 AND product_no IN (SELECT product_no FROM`baoli_pay_detail`)
  ) a
GROUP BY 1
 ) c
LEFT JOIN
(
SELECT product_no,SUM(principle) principle FROM `baoli_repayment_plan`
GROUP BY 1
) b ON c.product_no = b.product_no
WHERE c.loan_amount != b.principle

#=============step_3==============
#主要作用是判断 没有债转，但是有债转的退款或还款ID
SELECT t1.product_no FROM `refund_detail` t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no` AND t2.`fund_code` != 1
WHERE t1.`fund_code` = 1 AND t1.`product_no` NOT IN (
SELECT product_no FROM `baoli_pay_detail`
)

SELECT  t1.product_no FROM repay_detail t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no` AND t2.`fund_code` != 1
WHERE  t1.`fund_code` = 1 AND t1.`product_no` NOT IN (
SELECT product_no FROM `baoli_pay_detail`
)
#=============step_4==============
#=判断债转的还款计划的status
SELECT t1.* FROM `baoli_repayment_plan` t1
JOIN `repay_detail` t2 ON t1.`fund_code`=t2.`fund_code` AND t1.`product_no`=t2.`product_no`
WHERE t1.status=1

#==========step_5================

#====重点校验===
逻辑  债转前的 退还款+债转金额 = 债转前的放款金额
债转后的金额 >= 债转后的退还款


#判断是否有退款时间>债转时间，但资金方ID 非保理
SELECT t1.id,t1.`product_no`,t1.`trans_time`,t2.`refund_time` FROM `baoli_pay_detail` t1
JOIN `refund_detail` t2 ON t1.`product_no` = t2.product_no
WHERE t1.`trans_time` < t2.`refund_time` AND t2.`fund_code` != 1

SELECT t1.id,t1.`product_no`,t1.`trans_time`,t2.`refund_time` FROM `baoli_pay_detail` t1
JOIN `refund_detail` t2 ON t1.`product_no` = t2.product_no
WHERE t1.`trans_time` >= t2.`refund_time` AND t2.`fund_code` = 1


#判断是否有惠金所的还款时间>债转时间,但资金方ID 非保理
SELECT t1.id,t1.`product_no`,t1.`trans_time`,t2.`repay_time` FROM `baoli_pay_detail` t1
JOIN `repay_detail` t2 ON t1.`product_no` = t2.product_no
WHERE t1.`trans_time` < t2.`repay_time` AND t2.`fund_code` != 1

SELECT t1.id,t1.`product_no`,t1.`trans_time`,t2.`repay_time` FROM `baoli_pay_detail` t1
JOIN `repay_detail` t2 ON t1.`product_no` = t2.product_no
WHERE t1.`trans_time` >= t2.`repay_time` AND t2.`fund_code` =1


===============债转表校验done==============================

# 实还是否与还款计划已全部勾稽上---明细比较
SELECT   t1.product_no,
  t1.current_stage_no,
  t1.repay_amount  from qunaer_new.repayment_plan_2017 t1
join (SELECT product_no,current_stage_no,sum(repay_amount) repay_amount from qunaer_new.repay_detail
GROUP by 1,2) t2 on t1.product_no = t2.product_no and t1.current_stage_no = t2.current_stage_no
WHERE t1.repay_amount > 0 AND t1.repay_amount != t2.repay_amount

SELECT   t1.product_no,
  t1.current_stage_no,
  t1.repay_amount  from qunaer_new.repayment_plan_2016 t1
join (SELECT product_no,current_stage_no,sum(repay_amount) repay_amount from qunaer_new.repay_detail
GROUP by 1,2) t2 on t1.product_no = t2.product_no and t1.current_stage_no = t2.current_stage_no
WHERE t1.repay_amount > 0 AND t1.repay_amount != t2.repay_amount

# 注意处理，这种一般是在这一期全额退还款
SELECT * FROM `repayment_plan_2017` WHERE STATUS=2 AND repay_amount = 0 AND require_fee > 0



#转分期订单
select * from pay_detail where


#16年的还款计划中的数据问题: 不做任何处理，only 16年的还款放款
SELECT * FROM `repayment_plan_2016` WHERE repay_amount > require_fee AND repay_due_amount = 0
SELECT * FROM `repayment_plan_2016` WHERE repay_principle > principle



# 校验是否有流水未钩稽上
SELECT SUM(`repay_amount`) FROM `repay_detail` WHERE repay_type != 'REFUND_REPAY' AND repay_time >= '2017-01-01'
AND product_no IN (
SELECT product_no FROM `pay_detail` WHERE is_ptf = 0 AND loan_time >= '2017-01-01'
UNION
SELECT product_no FROM `pay_detail` WHERE is_ptf = 1 AND trans_time >= '2017-01-01'
)

# 是否 用户还款计划的 fund_code != pay_detail.fund_code; 如果有，则更新为一样的
SELECT t1.* FROM `repayment_plan_2017` t1
JOIN `pay_detail` t2 ON t1.`product_no` = t2.`product_no`
WHERE t1.`fund_code` != t2.`fund_code`


SELECT SUM(repay_amount) FROM `repayment_plan_2017`

# 哈银不支持 9,12期，去哪儿进行了处理，这5笔订单的还款和退款不计入量化派账户中，即现金流中不会计入，仅仅是账单文件中显示
# 如果有数据，则将退款状态和还款状态置为0，失败
SELECT * FROM refund_detail WHERE product_no IN (
'PAY1709262300010181','PAY1709270800002431','PAY1709271100034081','PAY1709271400006261','PAY1709271500010101'
)

SELECT * FROM repay_detail WHERE product_no IN (
'PAY1709262300010181','PAY1709270800002431','PAY1709271100034081','PAY1709271400006261','PAY1709271500010101'
)