60 lines
4.7 KiB
SQL
60 lines
4.7 KiB
SQL
create procedure proc_financial_interest()
|
|
BEGIN
|
|
DECLARE p_leas_form VARCHAR(32);
|
|
DECLARE p_contract_id VARCHAR(32);
|
|
DECLARE p_payment_number VARCHAR(32);
|
|
DECLARE p_all_interest VARCHAR(32);
|
|
DECLARE p_handling_charge_money VARCHAR(32);
|
|
DECLARE p_remain_corpus VARCHAR(32);
|
|
DECLARE p_prev_plan_date VARCHAR(32);
|
|
DECLARE p_year_rate VARCHAR(32);
|
|
DECLARE p_day_rate VARCHAR(32);
|
|
DECLARE p_month_interest DECIMAL(20,16);
|
|
DECLARE income_number VARCHAR(32);
|
|
DECLARE done BOOLEAN DEFAULT FALSE;
|
|
DECLARE contract_info CURSOR FOR
|
|
SELECT lci.leas_form,lcc.contract_id p_contract_id,lcc.payment_number p_payment_number,ROUND(lr.interest,2) AS interest,nvl(lcc.HANDLING_CHARGE_MONEY,0)/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END) AS HANDLING_CHARGE_MONEY FROM lc_calc_condition lcc LEFT JOIN lb_contract_info lci ON lci.id = lcc.contract_id LEFT JOIN (SELECT contract_id,SUM(interest/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END)) interest FROM lc_rent_plan lrp LEFT JOIN lb_contract_info lci ON lci.id=lrp.contract_id GROUP BY contract_id) lr ON lr.contract_id = lcc.contract_id WHERE NOT EXISTS (SELECT 1 FROM contract_rent_month WHERE contract_id = lcc.contract_id) AND nvl(lr.interest,0) > 0 AND lci.contract_status BETWEEN 31 AND 100 AND lci.id NOT IN (SELECT contract_id FROM lc_fund_rent_adjust);
|
|
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
|
|
|
|
OPEN contract_info;
|
|
clyLoop:LOOP
|
|
FETCH contract_info INTO p_leas_form,p_contract_id,p_payment_number,p_all_interest,p_handling_charge_money;
|
|
|
|
IF done THEN LEAVE clyLoop;
|
|
END IF;
|
|
BEGIN
|
|
DECLARE r_cont_id VARCHAR(32);
|
|
DECLARE r_plan_date VARCHAR(32);
|
|
DECLARE r_plan_list VARCHAR(32);
|
|
DECLARE r_accrued_date VARCHAR(32);
|
|
DECLARE r_start_date VARCHAR(32);
|
|
DECLARE r_end_date VARCHAR(32);
|
|
DECLARE r_interest_month VARCHAR(32);
|
|
DECLARE subdone BOOLEAN DEFAULT FALSE;
|
|
DECLARE rent_info CURSOR FOR
|
|
SELECT * FROM (SELECT lci.id,'0' AS plan_list,ROUND(lrp.interest /DATEDIFF(lrp.plan_date,lcc.start_date)*DATEDIFF(LAST_DAY(lcc.start_date),lcc.start_date),2) AS money,DATE_FORMAT(lcc.start_date,'%Y/%m') accrued_date,lcc.start_date ,LAST_DAY(lcc.start_date) end_date FROM lc_calc_condition lcc LEFT JOIN lb_contract_info lci ON lci.id=lcc.contract_id LEFT JOIN (SELECT contract_id,plan_date,ROUND(interest/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END),2) AS interest FROM lc_rent_plan lrp LEFT JOIN lb_Contract_info lci ON lci.id=lrp.contract_id WHERE plan_list=1 ) lrp ON lrp.contract_id=lci.id UNION ALL SELECT lrp1.contract_id, lrp1.plan_list ,ROUND(lrp1.interest/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END),2)-ROUND((lrp1.interest/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END))/DATEDIFF(lrp1.plan_date,IFNULL(lrp3.plan_date,lcc.start_date))*DATEDIFF(LAST_DAY(IFNULL(lrp3.plan_date,lcc.start_date)),IFNULL(lrp3.plan_date,lcc.start_date)),2)+ ROUND(IFNULL((lrp2.interest/(CASE WHEN lci.leas_form='02' THEN 1.06 ELSE 1.16 END))/DATEDIFF(lrp2.plan_date,lrp1.plan_date)*DATEDIFF(LAST_DAY(lrp1.plan_date),lrp1.plan_date),0),2) AS money,DATE_FORMAT(lrp1.plan_date,'%Y/%m') accrued_date,DATE_ADD(lrp1.plan_date,INTERVAL -DAY(lrp1.plan_date)+1 DAY) start_date,LAST_DAY(lrp1.plan_date) end_date FROM lc_rent_plan lrp1 LEFT JOIN lc_rent_plan lrp2 ON lrp1.contract_id=lrp2.contract_id AND lrp1.plan_list+1 =lrp2.plan_list
|
|
LEFT JOIN lc_rent_plan lrp3 ON lrp1.contract_id=lrp3.contract_id AND lrp1.plan_list-1 =lrp3.plan_list LEFT JOIN lc_calc_condition lcc ON lcc.contract_id =lrp1.contract_id LEFT JOIN lb_contract_info lci ON lci.id=lcc.contract_id)A WHERE A.id =p_contract_id ORDER BY A.id,CAST(A.plan_list AS SIGNED INTEGER);
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET subdone = TRUE;
|
|
OPEN rent_info;
|
|
subLoop:LOOP
|
|
FETCH rent_info INTO r_cont_id,r_plan_list,r_interest_month,r_accrued_date,r_start_date,r_end_date;
|
|
IF subdone THEN LEAVE subLoop;
|
|
END IF;
|
|
BEGIN
|
|
INSERT INTO contract_rent_month(id,rent_list,interest,fee,accrued_date,start_date,end_date,formula,hand_formula,create_date,contract_id,payment_number)
|
|
VALUES(REPLACE(UUID(),'-',''),r_plan_list,r_interest_month,ROUND(r_interest_month/p_all_interest*p_handling_charge_money,2),
|
|
r_accrued_date,
|
|
r_start_date,
|
|
r_end_date,
|
|
'','',DATE_FORMAT(NOW(),'%Y/%m/%d'),p_contract_id,p_payment_number);
|
|
END;
|
|
END LOOP;
|
|
CLOSE rent_info;
|
|
END;
|
|
END LOOP;
|
|
CLOSE contract_info;
|
|
END;
|
|
|