SQLize | PHPize | SQLtest

SELECT l.ID AS ContractID, l.SY_NO AS ContractNo, c.CM_CARD_NO, c.CM_PREFIX AS CustomerPrefix , c.CM_F_NAME AS CustomerFirstName, c.CM_L_NAME AS CustomerLastName , CONCAT(c.CM_PREFIX, ' ', c.CM_F_NAME, ' ', c.CM_L_NAME) AS FullName , CONCAT('(', b.BR_CODE, ') ', b.BR_NAME) AS BranchName, (SELECT COUNT(1) FROM leasing.eirpaymentschedule e WHERE e.ContractNo = l.SY_NO AND e.DueDate <= CURDATE() AND e.SlipDate IS NOT NULL) AS PaidPeriod, (SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) AS LateDay, CASE WHEN (SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) <= 30 THEN 'P' WHEN ((SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) > 30 AND (SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) <= 90) THEN 'SM' WHEN (SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) > 90 THEN 'NPL' ELSE NULL END AS GroupAging FROM leasing.leasing l INNER JOIN leasing.customer c ON c.DOC_NO = l.DOC_NO AND c.CustomerCode = 0 INNER JOIN datacenter.branch b ON b.BR_CODE = LEFT(l.DOC_NO, 4) WHERE l.ReferenceTypeID = 5 AND l.SY_NO <> '' AND l.CLOSE_DATE IS NULL AND l.SY_TYPE IN('08','09','10','12') AND (SELECT COUNT(1) FROM leasing.eirpaymentschedule e WHERE e.ContractNo = l.SY_NO AND e.DueDate <= CURDATE() AND e.SlipDate IS NOT NULL) >= 6 AND (SELECT IFNULL(DATEDIFF(CURDATE(), MIN(CASE WHEN(e.SlipDate IS NULL OR DATE(e.SlipDate) > CURDATE()) AND e.DueDate <= CURDATE() THEN e.DueDate END)), 0) AS OutstandingDay FROM leasing.EIRPaymentSchedule e WHERE e.ContractNo = l.SY_NO) <= 30 -- AND c.CM_CARD_NO = '1360500008341' ORDER BY LateDay, GroupAging, PaidPeriod;