49 lines
3.3 KiB
SQL
49 lines
3.3 KiB
SQL
DELIMITER $$
|
||
|
||
ALTER ALGORITHM=UNDEFINED DEFINER=`apzl`@`%` SQL SECURITY DEFINER VIEW `vi_lc_rent_plan` AS
|
||
SELECT
|
||
`lcf`.`ID` AS `id`,
|
||
`lbc`.`PROJECT_ID` AS `project_id`,
|
||
`lbc`.`ID` AS `contract_id`,
|
||
lbc.contract_status AS `contract_status`,
|
||
`lbc`.`BUSINESSTYPE` AS `businesstype`,
|
||
`lbc`.`SUBJECTID` AS `subjectid`,
|
||
`lbc`.`distributor_id` AS `distributor_id`,
|
||
`lbc`.`CONTRACT_NUMBER` AS `contract_number`,
|
||
`lcf`.`PAYMENT_NUMBER` AS `payment_number`,
|
||
IFNULL(`lbu`.`customername`,'') AS `customername`,
|
||
`lbc`.`PROJECT_NAME` AS `project_name`,
|
||
`lcf`.`PLAN_LIST` AS `plan_list`,
|
||
`lcf`.`PLAN_DATE` AS `plan_date`,
|
||
`lcf`.`RENT` AS `rent`,
|
||
`lcf`.`CORPUS` AS `corpus`,
|
||
`lcf`.`INTEREST` AS `interest`,
|
||
`getRentPenalty`(
|
||
`lcf`.`PAYMENT_NUMBER`,`lcf`.`PLAN_LIST`,DATE_FORMAT(NOW(),'%Y/%m/%d')) AS `penalty`,
|
||
(IFNULL(`lcfd`.`rent`,0) + IFNULL(`lcfi`.`rent`,0)) AS `fact_rent`,
|
||
(IFNULL(`lcfd`.`corpus`,0) + IFNULL(`lcfi`.`corpus`,0)) AS `fact_corpus`,
|
||
(IFNULL(`lcfd`.`interest`,0) + IFNULL(`lcfi`.`interest`,0)) AS `fact_interest`,
|
||
(IFNULL(`lcfd`.`penalty`,0) + IFNULL(`lcfi`.`penalty`,0)) AS `fact_penalty`,
|
||
(IFNULL(`lcfd`.`corpus_adjust`,0) + IFNULL(`lcfi`.`corpus_adjust`,0)) AS `corpus_adjust`,
|
||
(IFNULL(`lcfd`.`interest_adjust`,0) + IFNULL(`lcfi`.`interest_adjust`,0)) AS `interest_adjust`,
|
||
(IFNULL(`lcfd`.`penalty_adjust`,0) + IFNULL(`lcfi`.`penalty_adjust`,0)) AS `penalty_adjust`,
|
||
(`lcf`.`RENT` - (IFNULL(`lcfd`.`rent`,0) + IFNULL(`lcfi`.`rent`,0))) AS `rent_over`,
|
||
(`lcf`.`CORPUS` - (((IFNULL(`lcfd`.`corpus`,0) + IFNULL(`lcfi`.`corpus`,0)) + IFNULL(`lcfd`.`corpus_adjust`,0)) + IFNULL(`lcfi`.`corpus_adjust`,0))) AS `corpus_over`,
|
||
(`lcf`.`INTEREST` - (((IFNULL(`lcfd`.`interest`,0) + IFNULL(`lcfi`.`interest`,0)) + IFNULL(`lcfd`.`interest_adjust`,0)) + IFNULL(`lcfi`.`interest_adjust`,0))) AS `interest_over`,
|
||
ROUND((`getRentPenalty`(`lcf`.`PAYMENT_NUMBER`,`lcf`.`PLAN_LIST`,DATE_FORMAT(NOW(),'%Y/%m/%d')) - ((IFNULL(`lcfd`.`penalty`,0) + IFNULL(`lcfi`.`penalty`,0)) + (IFNULL(`lcfd`.`penalty_adjust`,0) + IFNULL(`lcfi`.`penalty_adjust`,0)))),2) AS `penalty_over`,
|
||
(CASE WHEN (((IFNULL(`lcfd`.`rent`,0) + IFNULL(`lcfi`.`rent`,0)) = `lcf`.`RENT`) AND (ROUND((`getRentPenalty`(`lcf`.`PAYMENT_NUMBER`,`lcf`.`PLAN_LIST`,DATE_FORMAT(NOW(),'%Y/%m/%d')) - ((IFNULL(`lcfd`.`penalty`,0) + IFNULL(`lcfi`.`penalty`,0)) + (IFNULL(`lcfd`.`penalty_adjust`,0) + IFNULL(`lcfi`.`penalty_adjust`,0)))),2) = '0.00')) THEN '<EFBFBD>ѻ<EFBFBD><EFBFBD><EFBFBD>' WHEN ((IFNULL(`lcfd`.`rent`,0) + IFNULL(`lcfi`.`rent`,0)) > 0) THEN '<EFBFBD><EFBFBD><EFBFBD>ֻ<EFBFBD><EFBFBD><EFBFBD>' ELSE 'δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>' END) AS `planstatus`
|
||
FROM (((((`lc_rent_plan` `lcf`
|
||
LEFT JOIN `lb_contract_info` `lbc`
|
||
ON ((`lcf`.`CONTRACT_ID` = `lbc`.`ID`)))
|
||
LEFT JOIN `lc_calc_condition_status` `lccs`
|
||
ON ((`lccs`.`PAYMENT_NUMBER` = `lcf`.`PAYMENT_NUMBER`)))
|
||
LEFT JOIN `lb_union_lessee_transfer` `lbu`
|
||
ON ((`lcf`.`CONTRACT_ID` = `lbu`.`contract_id`)))
|
||
LEFT JOIN `lc_rent_income_transfer` `lcfd`
|
||
ON (((`lcf`.`PAYMENT_NUMBER` = `lcfd`.`payment_number`)
|
||
AND (`lcf`.`PLAN_LIST` = `lcfd`.`plan_list`))))
|
||
LEFT JOIN `lc_rent_income_temp_transfer` `lcfi`
|
||
ON (((`lcf`.`PAYMENT_NUMBER` = `lcfi`.`payment_number`)
|
||
AND (`lcf`.`PLAN_LIST` = `lcfi`.`plan_list`))))$$
|
||
|
||
DELIMITER ; |