<? include $_SERVER["DOCUMENT_ROOT"] . "/common/classes/ApiBase.php" ;?>
<?

if (! class_exists("ApiBill"))
{

	class ApiBill extends ApiBase
	{

		function __construct($req)
		{
			parent::__construct($req);
		}

		//세부 사용량
		function getUsingData() {
		
			$no = $this->req["number"] ;
			
			$sql = "
				SELECT
					BC.electricity_usage_lm
					, BC.gas_usage_lm
					, BC.water_usage_lm
					, BC.electricity_usage
					, BC.gas_usage
					, BC.water_usage
				FROM tbl_bill_chunggu BC
				WHERE BC.no = '{$no}'
			";
			return $this->getRow($sql) ;
		
			
		}
		
		//건물이름 방이름 
		function getRoomName() {
			
			$group_fk = $this->appUser["group_fk"];
			$userNumber = $this->appUser["no"];
				
			$sql = "
			SELECT
				(SELECT `name` FROM tbl_room WHERE user_fk = '{$userNumber}' LIMIT 0, 1) AS roomName
				, (SELECT `name` FROM tbl_building WHERE group_fk = '{$group_fk}' LIMIT 0, 1) AS buildingName
			FROM DUAL
			";
			return $this->getRow($sql) ;
		}
		
		//공실율 이력
		function getVacantList() {
			
			$userNumber = $this->appUser["no"] ;
/* 
			$sql = "
				SELECT 
					D.date
					, R.*
				FROM (
					SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m') AS `date`
					UNION
					SELECT DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%Y-%m') AS `date`
					UNION
					SELECT DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m') AS `date`
				) D
				LEFT JOIN 
				(
					SELECT
						DATE_FORMAT(history_month, '%Y-%m') AS `date`
						, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk=(SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') ORDER BY pno DESC LIMIT 1) AS room_cnt
						, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk=(SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND contract_status='RD' ORDER BY pno DESC LIMIT 1) AS resident_cnt
						, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk=(SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND DATE_FORMAT(entering_due_date, '%Y-%m') = DATE_FORMAT(`history_month`, '%Y-%m') ORDER BY pno DESC LIMIT 1) AS entering_cnt
					FROM tbl_room_history
				) R
				ON D.date = R.date
				";
			 */
			$sql = "
				SELECT
					DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%m') AS `date3`
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS room_cnt3
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND (contract_status='EP' OR contract_status='UC') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS vacant_cnt3
					, DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%m') AS `date2`
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS room_cnt2
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND (contract_status='EP' OR contract_status='UC') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS vacant_cnt2
					, DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%m') AS `date1`
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS room_cnt1
					, (SELECT COUNT(*) FROM tbl_room_history WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}') AND (contract_status='EP' OR contract_status='UC') AND `status` = 'Y' AND DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m') =  DATE_FORMAT(`history_month`, '%Y-%m')) AS vacant_cnt1
				FROM DUAL			
				";			
			return $this->getRow($sql) ;
		}
		
		//특약 불러오기 
		function getSpecialContract() {
			
			$userNumber = $this->appUser["no"] ;
			
			$sql = "
				SELECT R.special_contract
				FROM tbl_room R
				WHERE user_fk = '{$userNumber}'
				";
			return $this->getRow($sql) ;
		}
		
		//호실리스트
		function getRoomList() {
			
			$userNumber = $this->appUser["no"] ;
			
			$sql = "
				SELECT 
					R.*
					, IFNULL((SELECT leaving_due_date FROM tbl_room WHERE `no`=R.no AND leaving_due_date BETWEEN DATE(NOW()) AND DATE(NOW() -INTERVAL -1 WEEK)), 0) AS isLeaving
					, IFNULL((SELECT entering_due_date FROM tbl_room WHERE `no`=R.no AND entering_due_date BETWEEN DATE(NOW()) AND DATE(NOW() -INTERVAL -1 WEEK)), 0) AS isEntering
				FROM tbl_room R
				WHERE R.building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$userNumber}' )
				AND R.status = 'Y'
				ORDER BY R.insert_date DESC 
				";
			$list = $this->getArray($sql) ;
			
			return $list ; 
		}
		
		//호실별 정보
		function getRoomData() {

			$no = $_REQUEST["no"] ;
			
			$sql = "
				SELECT 
					R.*
					, DATE(R.contract_date) AS contractDate
					, DATE(R.contract_expire_date) AS contractExpireDate
					, DATE(R.move_in_date) AS moveInDate
					, DATE(R.entering_due_date) As enteringDueDate
					, DATE(R.leaving_due_date) As leavingDueDate
				FROM tbl_room R WHERE no = '{$no}' AND status='Y'
				";
			$data = $this->getRow($sql) ;
			
			return $data ;
			
		}
		
		
		//비용 청구
		function getRequestData() {
			
			$no = $_REQUEST["no"] ;
			
			$sql = "
				SELECT 
					RC.* 
					, DATE(RC.request_date) AS requestDate
					, DATE(RC.handled_date) AS handledDate
				FROM tbl_request_charge RC
				WHERE RC.no = '{$no}'
				ORDER BY RC.	no DESC		
				";
			$data = $this->getRow($sql) ;
			
			return $data ;
			
		}
		
		//임대료 내역
		function getRentList() {
			
			$no = $this->appUser["no"] ;
			
			$sql = "
				SELECT 
					R.no
					, R.name
					, RC.* 
					, DATE(RC.month) AS monthDate
				FROM tbl_rent_chunggu RC
				JOIN tbl_room R ON R.no = RC.room_fk
 				WHERE R.building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}')
 				AND DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y%m%d') <= DATE_FORMAT(RC.month, '%Y%m%d')
				AND RC.status = 'Y'
				ORDER BY RC.NO DESC		
			";
			$rentList = $this->getArray($sql) ; 
			
			$sql = "
				SELECT
					R.name
					, R.payment_owner
					, RC.*
					, DATE(RC.request_date) AS requestDate
				FROM tbl_request_charge RC
				JOIN tbl_room R ON R.no = RC.room_fk
				WHERE R.building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}')
				AND DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y%m%d') <= DATE_FORMAT(RC.request_date, '%Y%m%d')
				AND RC.status = 'Y'
				ORDER BY RC.no DESC
			";
			$requestList = $this->getArray($sql) ; 
			
			$retArr = array(
					"rentList" => $rentList
					, "requestList" => $requestList
			) ;
			
			return $retArr ;
			
		}
		
		//건물정보
		function getBuildingInfo(){
			
			$no = $this->appUser["no"] ;

			if($this->IS_DEBUG)
			{
				$logData = "Api : getBuildingInfo // userNumber : {$no} ";
				LogUtil::writeFileLog($this->logPath, $logData);
			}
			
			$sql = "
				SELECT
					* 
					,  DATE(expire_date) AS expireDate
					, (SELECT COUNT(*) FROM tbl_room WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}' AND `status` = 'Y') AND `status` = 'Y') AS room_cnt
					, (SELECT COUNT(*) FROM tbl_room WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}' AND `status` = 'Y') AND (contract_status='RD' OR contract_status='CE') AND `status` = 'Y') AS resident_cnt
					, (SELECT COUNT(*) FROM tbl_room WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}' AND `status` = 'Y') AND (contract_status='EP' OR contract_status='UC') AND `status` = 'Y') AS vacant_cnt
					, (SELECT COUNT(*) FROM tbl_room WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}' AND `status` = 'Y') AND DATE_FORMAT(leaving_due_date, '%Y%m%d')>DATE_FORMAT(NOW(), '%Y%m%d') AND DATE_FORMAT(leaving_due_date, '%Y%m')=DATE_FORMAT(NOW(), '%Y%m') AND status='Y') AS leaving_cnt
					, (SELECT COUNT(*) FROM tbl_room WHERE building_fk = (SELECT `no` FROM tbl_building WHERE vip_fk = '{$no}' AND `status` = 'Y') AND DATE_FORMAT(entering_due_date, '%Y%m%d')>DATE_FORMAT(NOW(), '%Y%m%d') AND DATE_FORMAT(entering_due_date, '%Y%m')=DATE_FORMAT(NOW(), '%Y%m') AND status='Y') AS entering_cnt
				FROM tbl_building WHERE vip_fk = '{$no}' AND status='Y'
				";
			$data = $this->getRow($sql) ;
			
			return $data ;
			
		}
		
		//퇴실정산서 데이터
		function getCalculateData() {
			
			$userNumber = $this->appUser["no"];
			$group_fk = $this->appUser["group_fk"];
			
			$sql = "
				SELECT EC.* 
					, (SELECT balance FROM tbl_room WHERE user_fk = '{$userNumber}' LIMIT 0, 1) AS roomBalance
					, R.contractor_account_no	
					, R.contractor_account_bank
					, R.contractor_account_owner
					, R.contractor_name
					, DATE(R.leaving_due_date) AS leaving_due_date
					, (SELECT `name` FROM tbl_room WHERE user_fk = '{$userNumber}' LIMIT 0, 1) AS roomName
					, (SELECT `name` FROM tbl_building WHERE group_fk = '{$group_fk}' LIMIT 0, 1) AS buildingName
				FROM tbl_end_chunggu EC
				JOIN tbl_room_history R ON EC.room_fk = R.pno
				WHERE R.user_fk = '{$userNumber}'
				AND EC.status = 'Y'
				ORDER BY EC.no DESC
				";
			$data = $this->getRow($sql) ;
			
			$sql = "
					SELECT 
						MONTH(`month`) AS `month`
						, EC.electricity_usage_lm
						, EC.gas_usage_lm
						, EC.water_usage_lm
						, EC.electricity_usage
						, EC.gas_usage
						, EC.water_usage
					FROM tbl_end_chunggu EC
					WHERE user_fk = '{$userNumber}' 
					AND MONTH(`month`) IN(MONTH(NOW()), MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)))
					ORDER BY EC.no DESC					
					LIMIT 0, 1
				";
			$list = $this->getRow($sql) ;
			
			$retArr = array(
				"data" => $data,
				"list" => $list		
			);
			
			return $retArr ;
		}

		
		//월세 리스트
		function getMonthlyList() {
			
			$userNumber = $this->appUser["no"];
			$group_fk = $this->appUser["group_fk"];
			$year = $this->req["year"] == "" ? date("Y") : $this->req["year"] ;

			if($this->IS_DEBUG)
			{
				$logData = "Api : getMonthlyList // userNumber : {$userNumber} //group_fk : {$group_fk} // year : {$year}";
				LogUtil::writeFileLog($this->logPath, $logData);
			}
			
			$where = "" ;
			
			if($year != ""){
				$where = "AND YEAR(`month`) = '{$year}'" ;
			}
			
			$sql = "
				SELECT 
					R.name AS roomName
					, (SELECT `name` FROM tbl_building WHERE group_fk = '{$group_fk}' LIMIT 0, 1) AS buildingName
					, RC.* 
					, DATE_FORMAT(RC.month, '%Y.%m.%d') AS monthDate
				FROM tbl_rent_chunggu RC
				JOIN tbl_room_history R ON RC.room_fk = R.pno
				WHERE R.user_fk = '{$userNumber}' 
				AND RC.status = 'Y'
				AND rNO = (SELECT no FROM tbl_room WHERE user_fk='{$userNumber}')
				{$where}
				ORDER BY RC.NO DESC
				";
			$list = $this->getArray($sql) ;
			
			$sql = "
				SELECT
				(SELECT `name` FROM tbl_room WHERE user_fk = '{$userNumber}' LIMIT 0, 1) AS roomName
				, (SELECT `name` FROM tbl_building WHERE group_fk = '{$group_fk}' LIMIT 0, 1) AS buildingName
				FROM DUAL
			";
			$data = $this->getRow($sql) ;
			
			$retArr = array(
				"list" => $list 
				, "data" => $data	
			);
			
			return $retArr ;
			
		}
		
		//월세 상세
		function getMonthlyData() {
			
			$no = $_REQUEST["no"] ;

			$sql = "
				SELECT 
					R.name
					, R.payment_owner
					, (SELECT
							CASE WHEN B.is_paid = '0' THEN B.sum_after_payment ELSE 0 END AS sum_after_payment
						FROM
						(
							SELECT * FROM tbl_rent_chunggu
							WHERE rNo = (SELECT rNo FROM tbl_rent_chunggu RC WHERE RC.NO = '{$no}')
							ORDER BY NO DESC
						) B
						WHERE B.NO < '{$no}'
						ORDER BY B.NO DESC
						LIMIT 0, 1
						) AS sum_before_payment
					, RC.*
					, DATE_FORMAT(RC.month, '%Y.%m.%d') AS monthDate
					, B.month_account_no
					, B.month_account_bank
					, B.month_account_owner
				FROM tbl_rent_chunggu RC
				JOIN tbl_room_history R ON RC.room_fk = R.pno
				JOIN tbl_building B ON R.building_fk = B.no
				WHERE RC.`NO` = '{$no}'
				LIMIT 0, 1	
				";
			$data = $this->getRow($sql) ;

			return $data ;
		}
		
		//공과금 리스트
		function getUtilityList() {
			
			 $userNumber = $this->appUser["no"] ;
			 $group_fk = $this->appUser["group_fk"] ;
			 $year = $this->req["year"] == "" ? date("Y") : $this->req["year"] ;
			 
			 $where = "" ;
			 	
			 if($year != ""){
			 	$where = "AND YEAR(`month`) = '{$year}'" ;
			 }
			 
			 $sql = "
			 		SELECT BC.*
						, DATE_FORMAT(BC.month, '%Y.%m.%d') AS monthDate 
					FROM tbl_bill_chunggu BC
					JOIN tbl_room_history R ON R.pno = BC.room_fk
					WHERE R.user_fk = '{$userNumber}'
					AND BC.status = 'Y'
					AND rNO = (SELECT no FROM tbl_room WHERE user_fk='{$userNumber}')
					{$where}
					ORDER BY BC.no DESC
				";
			 $list = $this->getArray($sql) ;
			 
			 $sql = "
			 SELECT
			 (SELECT `name` FROM tbl_room WHERE user_fk = '{$userNumber}' LIMIT 0, 1) AS roomName
			 , (SELECT `name` FROM tbl_building WHERE group_fk = '{$group_fk}' LIMIT 0, 1) AS buildingName
			 FROM DUAL
			 ";
			 $data = $this->getRow($sql) ;
			 	
			 $retArr = array(
			 		"list" => $list
			 		, "data" => $data
			 );
			 	
			 return $retArr ;
			 
		}
		
		//공과금 상세
		function getUtilityData() {
				
			$no = $_REQUEST["no"] ;

			$sql = "
				SELECT
					R.name
					, (SELECT
							CASE WHEN B.is_paid = '0' THEN B.current_month_charge ELSE 0 END AS sum_after_payment
						FROM
						(
							SELECT * FROM tbl_bill_chunggu
							WHERE rNo = (SELECT rNo FROM tbl_bill_chunggu RC WHERE RC.NO = '{$no}')
							ORDER BY NO DESC
						) B
						WHERE B.NO < '{$no}'
						ORDER BY B.NO DESC
						LIMIT 0, 1
						) AS sum_before_payment
					, BC.*
					, DATE_FORMAT(BC.month, '%Y.%m.%d') AS monthDate
					, B.util_account_no
					, B.util_account_bank
					, B.util_account_owner
				FROM tbl_bill_chunggu BC
				JOIN tbl_room_history R ON BC.room_fk = R.pno
				JOIN tbl_building B ON R.building_fk = B.no
				WHERE BC.`NO` = '{$no}'
			";
			$data = $this->getRow($sql) ;
		
			return $data ;
				
		}
		
		
		
	} // 클래스 종료
}
?>