DELIMITER $$

USE `htzq`$$

DROP PROCEDURE IF EXISTS `htzq_proc_hkys`$$

CREATE DEFINER=`htzq`@`%` PROCEDURE `htzq_proc_hkys`(IN i_company_no VARCHAR(30),
				  IN i_financing_no VARCHAR(30),
				  IN i_client_id VARCHAR(30),
				  IN i_return_date VARCHAR(30),
			          IN i_unlocked_amount VARCHAR(30),
		                  IN i_unlocked_rate VARCHAR(30),
				  IN i_type VARCHAR(30))
BEGIN
	
	DECLARE v_unlockd_rate DECIMAL(16,2) DEFAULT 0;
	
	DECLARE STOP INT DEFAULT 0;
	
	DECLARE v_compact_balance DECIMAL(16,2);
	
	DECLARE v_left_capital DECIMAL(16,2);
	
	
	DECLARE v_last_return_date DATETIME;
	
	
	DECLARE v_fix_interest_date DATETIME;
	
	DECLARE v_id INT;
	
	DECLARE v_fin_rate DECIMAL(16,4);
	
	DECLARE v_break_rate DECIMAL(16,6);
	
	DECLARE v_financing_no VARCHAR(255);
	
	DECLARE v_unfreeze_ratio DECIMAL(16,2);
	
	DECLARE v_back_date DATETIME;
	
	DECLARE v_last_back_date DATETIME;
	
	DECLARE v_seq_no INT;
	
	DECLARE v_interest_date DATETIME;
	
	DECLARE v_left_balance DECIMAL(16,2) DEFAULT 0;
	
	DECLARE v_total_unfreeze_ratio DECIMAL(16,2);
	
	DECLARE v_cur_balance DECIMAL(16,2);
	
	DECLARE v_days INT;
	
	DECLARE v_total_interest DECIMAL(16,4) DEFAULT 0;
	
	
	DECLARE v_amount INT;
	
	DECLARE v_occur_amount INT;
	
	DECLARE v_total_penalty DECIMAL(16,2) DEFAULT 0;
	
	DECLARE v_confirm_date DATETIME;
	
	
	DECLARE v_ts INT;
	
	DECLARE cur CURSOR FOR SELECT a.id,a.financing_no,a.compact_balance,a.left_capital,a.last_return_date,a.fix_interest_date,b.fin_rate,b.break_rate,a.entrust_amount,a.confirm_date FROM htzq_compact_info a LEFT JOIN htzq_financinginfo b ON a.financing_no = b.financing_no WHERE compact_status IN ('01','04','05','06','08') AND EXISTS(SELECT 1 FROM htzq_tmp_khhyys WHERE id = a.id);
	
	DECLARE cur1 CURSOR FOR SELECT unfreeze_ratio,last_back_date back_date,interest_date,last_back_date,seq_no FROM htzq_unfreezeinfo WHERE financing_no = v_financing_no AND DATE(interest_date) <= i_return_date ORDER BY CAST(seq_no AS UNSIGNED INT);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP = 1;
	
        TRUNCATE TABLE htzq_tmp_khhyys;
	
	SET @v_sql = 'insert into htzq_tmp_khhyys(id,financing_no,company_no,client_id,fund_account,stock_account,stock_code,stock_name,fin_date,confirm_date,compact_balance,amount,branch_no,compact_id) select a.id,a.financing_no,a.company_no,a.client_id,a.fund_account,a.stock_account,a.stock_code,b.stock_name,a.fin_date,a.confirm_date,a.compact_balance,a.entrust_amount,a.branch_no,a.compact_id from htzq_compact_info a left join htzq_financinginfo b on a.financing_no = b.financing_no where compact_status in (''01'',''04'',''05'',''06'',''08'')';
	IF i_client_id <> '' THEN
		SET @v_sql = CONCAT(@v_sql,' and a.client_id = ''',i_client_id,'''');
	END IF;
	IF i_financing_no <> '' THEN
		SET @v_sql = CONCAT(@v_sql,' and a.financing_no = ''',i_financing_no,'''');
	END IF;
	IF i_company_no <> '' THEN
		SET @v_sql = CONCAT(@v_sql,' and a.company_no = ''',i_company_no,'''');
	END IF;
	PREPARE stmt FROM @v_sql;  
        EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
	
	OPEN cur;
		FETCH cur INTO v_id,v_financing_no,v_compact_balance,v_left_capital,v_last_return_date,v_fix_interest_date,v_fin_rate,v_break_rate,v_amount,v_confirm_date;
		
		WHILE STOP <> 1 DO
			SET v_left_balance = 0;
			SET v_cur_balance = 0;
			SET v_days = 0;
			SET v_total_interest = 0;
			SET v_occur_amount = 0;
			SET v_total_penalty = 0;
			SET v_ts = DATEDIFF(i_return_date,DATE(v_confirm_date));
			
				
			
			OPEN cur1;
				loop_financinginfo:LOOP
					FETCH cur1 INTO v_unfreeze_ratio,v_back_date,v_interest_date,v_last_back_date,v_seq_no;
					IF STOP = 1 THEN
						LEAVE loop_financinginfo;
					END IF;
					
					
					SELECT IFNULL(SUM(unfreeze_ratio),0) INTO v_total_unfreeze_ratio FROM htzq_unfreezeinfo WHERE financing_no = v_financing_no AND seq_no > v_seq_no;
					
					
					IF IFNULL(v_left_capital,0) > IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0) THEN
						IF IFNULL(v_left_capital,0) - IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0) > IFNULL(v_compact_balance,0) * IFNULL(v_unfreeze_ratio,0) THEN
							SET v_cur_balance = IFNULL(v_compact_balance,0) * IFNULL(v_unfreeze_ratio,0);
						ELSE
							SET v_cur_balance = IFNULL(v_left_capital,0) - IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0);
						END IF;
					ELSE
						SET v_cur_balance = 0;
					END IF;
					
					IF v_back_date IS NULL THEN
						SET v_days = DATEDIFF(i_return_date,IFNULL(DATE(v_last_return_date),DATE(v_confirm_date)));
					ELSE
						
						IF DATEDIFF(i_return_date,DATE(v_back_date)) > 0 THEN
							SET v_days = DATEDIFF(v_back_date,IFNULL(DATE(v_last_return_date),DATE(v_confirm_date)));
						ELSE
							SET v_days = DATEDIFF(i_return_date,IFNULL(DATE(v_last_return_date),DATE(v_confirm_date)));
						END IF;
					END IF;
					IF v_days < 0 THEN
						SET v_days = 0;
					END IF;
					SET v_total_interest = IFNULL(v_total_interest,0) + IFNULL(v_cur_balance,0) * IFNULL(v_fin_rate,0) * IFNULL(v_days,0) / 360;
					
					
					IF v_last_back_date IS NOT NULL THEN
						IF DATEDIFF(DATE(NOW()),DATE(v_last_back_date)) > 0 AND IFNULL(v_left_capital,0) > IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0) THEN
							SET v_days = DATEDIFF(i_return_date,DATE(v_last_back_date));
							SET v_total_penalty = IFNULL(v_total_penalty,0) + IFNULL(v_cur_balance,0) * IFNULL(v_break_rate,0) * v_days;
						END IF;
					END IF;
				END LOOP loop_financinginfo;
			CLOSE cur1;
			
			IF IFNULL(v_left_capital,0) > IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0) THEN
				SET v_left_balance = IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0);
				
				SET v_cur_balance = IFNULL(v_left_capital,0) - IFNULL(v_compact_balance,0) * IFNULL(v_total_unfreeze_ratio,0);
			ELSE
				SET v_left_balance = IFNULL(v_left_capital,0);
			END IF;
			SET v_occur_amount = IFNULL(v_amount,0) * IFNULL(v_unfreeze_ratio,0);
			
			IF i_type = '02' THEN
				SET v_cur_balance = IFNULL(v_left_capital,0);
				SET v_left_balance = IFNULL(v_left_capital,0);
				SELECT IFNULL(SUM(IFNULL(occur_amount,0)),0) INTO v_occur_amount FROM htzq_fundjour a WHERE STATUS = '02' AND business_flag IN ('1002','1003','1005') AND EXISTS(SELECT 1 FROM htzq_tmp_khhyys WHERE financing_no = a.financing_no AND client_id = a.client_id AND fund_account = a.fund_account AND id = v_id);
				SET v_occur_amount = IFNULL(v_amount,0) - IFNULL(v_occur_amount,0);
			END IF;
			SET v_total_interest = IFNULL(v_total_interest,0) + IFNULL(v_left_balance,0) * IFNULL(v_fin_rate,0) * DATEDIFF(i_return_date,IFNULL(DATE(v_last_return_date),DATE(v_confirm_date))) / 360;
			UPDATE htzq_tmp_khhyys SET return_interset = IFNULL(v_total_interest,0),return_balance = IFNULL(v_cur_balance,0),occur_amount = IFNULL(v_occur_amount,0),occur_penalty_interest = IFNULL(v_total_penalty,0) WHERE id = v_id;
			SET STOP = 0;
			
			FETCH cur INTO v_id,v_financing_no,v_compact_balance,v_left_capital,v_last_return_date,v_fix_interest_date,v_fin_rate,v_break_rate,v_amount,v_confirm_date;
		END WHILE;
	CLOSE cur;
	UPDATE htzq_tmp_khhyys SET return_interset = ROUND(return_interset,2);
	SELECT a.*,b.client_name,c.company_name FROM htzq_tmp_khhyys a LEFT JOIN htzq_client b ON a.client_id = b.client_id AND a.company_no=b.company_no LEFT JOIN htzq_company c ON a.company_no = c.company_no ORDER BY a.financing_no,a.company_no,a.client_id;
END$$

DELIMITER ;