<?php 

	//IP ADDRESS

	$ipAddress = $_SERVER['REMOTE_ADDR'];

	$telegramCanSendMessage = true; //control send message via Telegram, true to proceed, false to lock



	if($ipAddress == "127.0.0.1" || $ipAddress == "::1"){

		$conn = mysqli_connect("localhost","root","", "dha_refine");

	}else{

		// $conn = mysqli_connect("localhost","dzvisual_admin","Farhan@123456","dzvisual_homestay");

		$conn = mysqli_connect("localhost","dzvisual_admin","Farhan@123456","dzvisual_homestay");

	}



	// Check connection

	if (!$conn) {

		die("Connection failed: " . mysqli_connect_error());

	}

	

	ob_start(); session_start();

	//error_reporting(0);



	if(!isset($_SESSION['admin']) && !isset($_SESSION['owner']) && !isset($_SESSION['staff'])){

		$error = "No admin/staff found";

		header("Location: login.php?error=".$error);}





	//TELEGRAM

	$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

	$chatID = "-293233778";



	function sendMessage($chatID, $messaggio, $token) {

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		echo "sending message to " . $chatID . "\n";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		return $result;

	}



	function sendMessage2($chatID, $messaggio) {

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}

		

		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		

		echo "sending message to " . $chatID . "\n";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		return $result;

	}



	function sendWA($number,$text){

		$curl = curl_init();



        curl_setopt_array($curl, array(

        CURLOPT_URL => 'https://app.respond.io/api/v1/message/sendContent/'.$number,

        CURLOPT_RETURNTRANSFER => true,

        CURLOPT_ENCODING => '',

        CURLOPT_MAXREDIRS => 10,

        CURLOPT_TIMEOUT => 0,

        CURLOPT_FOLLOWLOCATION => true,

        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,

        CURLOPT_CUSTOMREQUEST => 'POST',

        CURLOPT_POSTFIELDS =>'{

        "body": [

            {

                "type" : "text",

                "text" : "'.$text.'"

            }

        ]

        }',

        CURLOPT_HTTPHEADER => array(

            'Content-Type: application/json',

            'Authorization: Bearer 35aea252ed4d90aa6904c44ff9837f232ff51c6dd6b628318127b24b214730b30bbd793fac5cc135853d85cbc927c25d8401f36e17ac0f17c9abcd7b8c4e1ef1'

        ),

        ));



        $response = curl_exec($curl);



        curl_close($curl);

        echo $response;

	}



	function sendWAImage($number,$url){

		$curl = curl_init();



		$image_data = file_get_contents($url);

		$image_base64 = base64_encode($image_data);



        curl_setopt_array($curl, array(

        CURLOPT_URL => 'https://app.respond.io/api/v1/message/sendContent/'.$number,

        CURLOPT_RETURNTRANSFER => true,

        CURLOPT_ENCODING => '',

        CURLOPT_MAXREDIRS => 10,

        CURLOPT_TIMEOUT => 0,

        CURLOPT_FOLLOWLOCATION => true,

        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,

        CURLOPT_CUSTOMREQUEST => 'POST',

        CURLOPT_POSTFIELDS =>'{

        "body": [

            {

                "type" : "image",

                "url" : "'.$image_base64.'"

            }

        ]

        }',

        CURLOPT_HTTPHEADER => array(

            'Content-Type: application/json',

            'Authorization: Bearer 35aea252ed4d90aa6904c44ff9837f232ff51c6dd6b628318127b24b214730b30bbd793fac5cc135853d85cbc927c25d8401f36e17ac0f17c9abcd7b8c4e1ef1'

        ),

        ));



        $response = curl_exec($curl);



        curl_close($curl);

        echo $response;

	}



	function sendMessage3($chatID,$messaggio) {

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		

		echo "sending message to " . $chatID . "\n";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		return $result;

	}



	function sendMessage4($messaggio,$chatID = "26236173") {

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		

		echo "sending message to " . $chatID . "\n";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		return $result;

	}



	function sendPhoto($chatID, $photoUrl) {

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		echo "sending photo to " . $chatID . "\n";



		$url = "https://api.telegram.org/bot" . $token . "/sendPhoto?chat_id=" . $chatID;

		$url = $url . "&photo=" . $photoUrl;

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		return $result;

	}



    function generateDZVReservationNo(){

        return "DZV".date('YmdHis');

    }



	function telegramWaterBillUpdate($date){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn; if($date){$date = date("Y-m-01",$date);}else{$date = date("Y-m-01");}

		$result = mysqli_query($conn, "SELECT * FROM rooms WHERE r_status=1 AND r_profit_type=1 ORDER BY p_id ASC");



		while($row = mysqli_fetch_assoc($result)){

			$roomId = $row['r_id'];

			$roomNo = $row['r_no'];



			$currentRentalRate = currentRentalRate($roomId)['u_amount'] ?: getDetailByTable('rooms',$roomId)['r_rental'];

			$totalPaidRental = totalPaidRental($roomId)['total'];

			$balance = ($currentRentalRate - $totalPaidRental);



			if($currentRentalRate == $totalPaidRental){$displayStatus = "? PAID";}else{$displayStatus = "";}



			//if(!$currentRentalRate){$displayPaymentStatus = "PENDING";}



			$totalAllCurrentRental = $totalAllCurrentRental + $currentRentalRate;

			$totalAllPaidRental = $totalAllPaidRental + $totalPaidRental;



			$display = $display.getDetailFromTable('properties',getDetailFromTable('rooms',$roomId)['p_id'])['pty_code']." ".$roomNo." - ".$currentRentalRate." ".$displayStatus."



";

		}



		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID = -1001209156913;

		$messaggio = "WATER STATUS FOR ".date("F Y",strtotime($date))."

".$display."

Total Rental : RM ".$totalAllCurrentRental."

Total Paid : RM ".$totalAllPaidRental."

Balance : RM ".($totalAllCurrentRental - $totalAllPaidRental)."

";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(



				CURLOPT_URL => $url,



				CURLOPT_RETURNTRANSFER => true



		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		

		$update = json_decode($result, TRUE);

		$resultMessageId = $update['result']['message_id'];

		tgRemovePreviousChatMessage($chatID,$resultMessageId,2);



	}



	function telegramRoomRentalUpdate($utilityTypeId,$date){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn; $display = ""; if($date){$date = date('Y-m-01',strtotime($date));}else{$date = date('Y-m-01');}

		$result = mysqli_query($conn, "SELECT * FROM rooms WHERE r_status=1 AND r_profit_type=1 ORDER BY p_id ASC, r_no ASC");

		

		while($row = mysqli_fetch_assoc($result)){

			$roomId = $row['r_id'];

			$roomNo = $row['r_no'];

			

			$utilityTotal = utilityTotal($roomId,$utilityTypeId,$date)['total'];

			$utilityPaidTotal = utilityPaidTotal($roomId,$utilityTypeId,$date)['total'];

			$balance = ($utilityTotal - $utilityPaidTotal);

		

			if($utilityTotal <= $utilityPaidTotal && $utilityTotal != 0){$displayStatus = "✅ PAID";}else{$displayStatus = "";}

			

			//if(!$currentRentalRate){$displayPaymentStatus = "PENDING";}



			$totalAllCurrentRental = $totalAllCurrentRental + $utilityTotal;

			$totalAllPaidRental = $totalAllPaidRental + $utilityPaidTotal;

			

			$display = $display.getDetailFromTable('properties',getDetailFromTable('rooms',$roomId)['p_id'])['pty_code']." ".$roomNo." - ".$utilityTotal." ".$displayStatus."

";

		}

		

		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID = -1001209156913;

		$messaggio = strtoupper(getDetailByTable('utility_type',$utilityTypeId)['uty_title'])." STATUS FOR ".strtoupper(date("F Y",strtotime($date)))."

	

".$display."

Total : RM ".$totalAllCurrentRental."

Total Paid : RM ".$totalAllPaidRental."

Balance : RM ".($totalAllCurrentRental - $totalAllPaidRental)."

";

		

		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		

		$update = json_decode($result, TRUE);

		$resultMessageId = $update['result']['message_id'];

		tgRemovePreviousChatMessage($chatID,$resultMessageId,2);

	}



	function telegramItemUpdate(){ 

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn; $display = "";

		

		$display = "ITEM SHORTAGE UPDATE

";

		

		$result = mysqli_query($conn, "SELECT * FROM items");

		

		while($row = mysqli_fetch_assoc($result)){

			$itemId = getDetailByTable('items',$row['i_id'])['i_id'];

			$itemTitle = getDetailByTable('items',$row['i_id'])['i_title'];

			

			$display = $display."

".$itemTitle." - ".roomItemPc($itemId)['total'];

			

		}

		

		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID = -222121450;

		$messaggio = $display;

		

		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		

		$update = json_decode($result, TRUE);

		$resultMessageId = $update['result']['message_id'];

		tgRemovePreviousChatMessage($chatID,$resultMessageId,2);

	}





    function roomItemPc($item){ global $conn;

        $result = mysqli_query($conn,"SELECT SUM(ri_pc) AS total FROM room_item WHERE i_id=$item");

        return mysqli_fetch_array($result);

    }



    function currentRentalRate($roomId){ global $conn; $date = date('Y-m-01');

        $result = mysqli_query($conn, "SELECT * FROM utilities WHERE r_id=$roomId AND u_date='$date'");

        return mysqli_fetch_assoc($result);

    }



    function totalPaidRental($roomId){ global $conn;

        $utilityId = currentRentalRate($roomId)['u_id'];

        $result = mysqli_query($conn, "SELECT SUM(utyp_amount) AS total FROM utility_payment WHERE uty_id=$utilityId");

        return mysqli_fetch_assoc($result);

    }



	function rentalTotal($roomId){

        global $conn;



        $result = mysqli_query($conn,"SELECT SUM(u_amount) AS rentalTotal, COUNT(u_id) AS monthTotal FROM utilities WHERE u_type_id=6 AND r_id=$roomId");

        $row = mysqli_fetch_array($result);

        return $row;

    }



	function utilityTotal($roomId,$typeId,$date = NULL){

        global $conn; if($date){$date = "AND u_date='$date'";}



        $result = mysqli_query($conn,"SELECT SUM(u_amount) AS total FROM utilities WHERE u_type_id=$typeId AND r_id=$roomId $date");

        $row = mysqli_fetch_array($result);

        return $row;

    }



	function utilityPaymentTotal($roomId){

		global $conn;



		$result = mysqli_query($conn,"SELECT SUM(UP.utyp_amount) AS paymentTotal FROM utility_payment AS UP LEFT JOIN utilities AS U ON UP.uty_Id=U.u_id WHERE U.r_id=$roomId AND U.u_type_id=6");

		$row = mysqli_fetch_array($result);

		return $row;

	}



	function utilityPaidTotal($roomId,$typeId,$date = NULL){

		global $conn;  if($date){$date = "AND U.u_date='$date'";}

		

		$result = mysqli_query($conn,"SELECT SUM(UP.utyp_amount) AS total FROM utility_payment AS UP LEFT JOIN utilities AS U ON UP.uty_Id=U.u_id WHERE U.r_id=$roomId AND U.u_type_id=$typeId $date");

		$row = mysqli_fetch_array($result);

		return $row;

	}



	function telegramHousekeepingUpdate($date){ 

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn;

		$result = mysqli_query($conn, "SELECT * FROM housekeep AS HK LEFT JOIN rooms AS R ON HK.r_id=R.r_id WHERE HK.hk_date='$date' OR HK.hk_date<='$date' AND HK.hk_status<9 ORDER BY R.p_id ASC, R.r_no ASC");

		while($row = mysqli_fetch_assoc($result)){

			$housekeepId = $row['hk_id'];

			$roomId = $row['r_id'];

			$dateDiff = intval((strtotime($row['hk_end'])-strtotime($row['hk_start']))/60);

			$hours = intval($dateDiff/60);

			$minutes = $dateDiff%60;

			

			$prevGuestCheckOutTime = getDetailByTable('reservation',$row['rv_id'])['rv_co_time'];

			if($prevGuestCheckOutTime != "12:00:00"){ $textStaying = date_format(date_create($prevGuestCheckOutTime),"ga");}else{$textStaying =  "";}

			

			//bedsheet status

			$totalNight = getDetailFromTable('reservation',$row['rv_id'])['rv_night'];

			if($row['hk_bedsheet'] == 1){$textBedsheet = "

			🛏 ✨";}else{

				$resultBedsheet = mysqli_query($conn,"SELECT * FROM housekeep WHERE r_id=$roomId AND hk_date<'$date' ORDER BY hk_date DESC");

				$totalUnchanged = 0;

				while($rowBedsheet = mysqli_fetch_assoc($resultBedsheet)){

					++$totalUnchanged;

				

					if($rowBedsheet['hk_bedsheet'] == 1 && $totalNight == 1){$textBedsheet = "";}else{$textBedsheet = "

			🛏 💩";} break;

					}

				} $totalUnchanged = 0; //clear

			

			//get staff

			$staffName = "";

			$resultHousekeepStaff = mysqli_query($conn,"SELECT * FROM housekeep_staff WHERE hk_id=$housekeepId");

			while($rowHousekeepStaff = mysqli_fetch_assoc($resultHousekeepStaff)){

				$staffName = $staffName." | ".getDetailFromTable('staffs',$rowHousekeepStaff['s_id'])['s_nickname'];

			}

			

			$resultRV = mysqli_query($conn, "SELECT * FROM reservation WHERE r_id=$roomId AND rv_ci_date='$date'");

			$rowRV = mysqli_fetch_assoc($resultRV);

			switch(getDetailFromTable('reservation',$rowRV['rv_id'])['rv_status']){

				case 0: $rvStatus = ""; break;

				case 1: $rvStatus = ""; break;

				case 2: $rvStatus = "

				🗝 confirmed"; break;

				case 3: $rvStatus = "

				🗝 arriving"; break;

				case 4: $rvStatus = "

				🗝 arrived"; break;

				case 5: $rvStatus = "

				🗝 checked in"; break;

				case 6: $rvStatus = "

				🗝 checked out"; break;

				case 7: $rvStatus = "

				🗝 completed"; break;

			}

			

			//if status extending, next guest no show

			if($row['hk_status'] == 10){$rvStatus = ""; $textBedsheet = ""; }

			

			switch($row['hk_status']){

				case "0": $status = "🔴 ".$textStaying; break;

				case "1": $status = "🚨 pintu kunci"; break;

				case "2": $status = "🔵 checked out"; break;

				case "3": $status = "🔵 key collected"; break;

				case "4": $status = "🚨 xada kunci"; break;

				case "5": $status = "🚨 so messy!!"; break;

				case "6": $status = "❌ extend?"; break;

				case "7": $status = "🚹 ".date_format(date_create($row['hk_start']),'g:iA').$staffName; break;

				case "8": $status = "⏳ akan sambung"; break;

				case "9": $status = "❤️ no messy"; break;

				case "10": $status = "❌ extending"; break;

				case "11": $status = "✅ ".date_format(date_create($row['hk_end']),'g:iA')." ".$hours.":".$minutes.$staffName; break;

				case "12": $status = "❌ postponed"; break;

			}



            $occTypeId = getDetailFromTable('reservation',$row['rv_id'])['rv_occ_id'];



            if($occTypeId != 0){$displayOccType = " - ".occuptionType($occTypeId)['title'];}else{$displayOccType = "";};

			

			$text = $text.getDetailFromTable('properties',getDetailFromTable('rooms',$row['r_id'])['p_id'])['pty_code']." ".getDetailFromTable('rooms',$row['r_id'])['r_no']." (".getDetailFromTable('rooms',$row['r_id'])['r_code'].$displayOccType.") ".$status."".$rvStatus.$textBedsheet."



"; $textBedsheet = ""; $staffName = "";//clear

		}

		

		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID = -1001072353202;

		$messaggio = "HOUSEKEEPING SCHEDULE ".$date."

	

".$text."

UPDATED BY ".$_SESSION['staff']['s_nickname'].$_SESSION['admin']['a_username']."

UPDATE JOB AT https://bit.ly/3wKSGvw";

		



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		

		$update = json_decode($result, TRUE);

		$resultMessageId = $update['result']['message_id'];

		

		tgRemovePreviousChatMessage($chatID,$resultMessageId,2,'housekeeping');

		

	}



	//TELEGRAM UPDATE AVAILABILITY

	function telegramUpdateAvailability($date){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn;

		$result = mysqli_query($conn, "SELECT * FROM rooms WHERE r_status=1 AND r_profit_type in (1,3) ORDER BY p_id ASC");

		while($row = mysqli_fetch_assoc($result)){

            // if(filterRoomAdmin($row['r_id'],$_SESSION['admin']['a_id'])==true){continue;};



			$propertyId = getDetailByTable('properties',$row['p_id'])['pty_id'];

			$array[$propertyId][0] = $propertyId;

			$guestName = getRVByDateRoom($row['r_id'],$date)['rv_guest'];

			

			if($guestName == ""){

				$array[$propertyId][1] += 1; 

			};

		}



		foreach($array as $value){

			$text .= getDetailByTable('properties',$value[0])['pty_short'];

			$text .= " - ".$value[1]; if($value[1] == NULL){$text .= " ❤️ FULLLY BOOKED";}

			$text .= "

";



		}



		// $token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		// $chatID = -664044603;

		$chatID = -1001753305829;

		$messaggio = "AVAILABILITY UPDATE ".$date."

	

".$text."

UPDATED BY ".$_SESSION['staff']['s_nickname'].$_SESSION['admin']['a_username'];



        $result = sendMessage2($chatID, $messaggio);

        $update = json_decode($result, TRUE);

        tgRemovePreviousChatMessage($chatID,$update['result']['message_id'],2,'availability');



	}



	//TELEGRAM UPDATE AVAILABILITY

	function telegramUpdateDeposit(){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}

		

		global $conn; $currentDate = date('d/m/Y'); $counter = 0;

		$result = mysqli_query($conn, "SELECT * FROM deposit WHERE dp_refund_at = CURRENT_DATE OR dp_refund_at IS NULL AND dp_ready_at IS NOT NULL ORDER BY dp_ready_at ASC");

		while($row = mysqli_fetch_assoc($result)){



			$guestName = getDetailByTable('reservation',$row['rv_id'])['rv_guest'];

            $refundAmount = $row['dp_amount'] - $row['dp_forfeited_amount'];

            $totalDeposit = $totalDeposit+$refundAmount;

            $totalRefunded = $totalRefunded + $row['dp_refund_amount'];

            if($row['dp_refund_at']){$status = "✅ PAID";}



            $text .= $guestName." - (RM".$refundAmount.")".$status;

            $text .= "

";

            ++$counter; $status = "";

		}



        // $text = $guestName;



		// $token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID = -1001753305829;

		$messaggio = "DEPOSIT LIST TO REFUND ".$currentDate."



".$text."

TOTAL GUEST = ".$counter."

TOTAL DEPOSIT = RM".$totalDeposit."

TOTAL REFUNDED = RM".$totalRefunded."



UPDATED BY ".$_SESSION['staff']['s_nickname'].$_SESSION['admin']['a_username'];



        $result = sendMessage2($chatID, $messaggio);

        $update = json_decode($result, TRUE);

		tgRemovePreviousChatMessage($chatID,$update['result']['message_id'],2,'deposit');

	}





	function telegramGuestTodayList($date){ 

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM reservation WHERE rv_ci_date='$date'");

		while($row = mysqli_fetch_assoc($result)){

			switch($row['rv_platform']){

				case 1: $platformTitle = "Direct"; break;

				case 2: $platformTitle = "Booking com"; break;

				case 3: $platformTitle = "Airbnb"; break;

				case 4: $platformTitle = "Agoda"; break;

			}

			

			$text = $text.$row['rv_no']." - ".$platformTitle."

";

		}



		$chatID = -1001072353202;

		$messaggio = "TODAY GUEST ".$date."

	

".$text."

CHECK RESERVATION AT https://bit.ly/3yMwfYq";



        $result = sendMessage2($chatID, $messaggio);

        $update = json_decode($result, TRUE);

		tgRemovePreviousChatMessage($chatID,$update['result']['message_id'],2,'todayGuestList');

	}



	function tgRVTodayEarned($date,$chatID){ 

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn;

		$result = mysqli_query($conn, "SELECT SUM(rv_total) AS totalEarned, COUNT(rv_id) AS total FROM reservation WHERE rv_ci_date='$date' AND rv_status>1");

		$row = mysqli_fetch_assoc($result);

		

		$totalEarned = $row['totalEarned'];

		$total = $row['total'];

		

		$token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

		$chatID ? $chatID : -550653212;

		$messaggio = "TODAY EARNING | ".$date."



Total guests: ".$total."

Total earned: RM ".$totalEarned."

".$text."

CHECK RESERVATION AT https://bit.ly/3yMwfYq";



		$url = "https://api.telegram.org/bot" . $token . "/sendMessage?chat_id=" . $chatID;

		$url = $url . "&text=" . urlencode($messaggio);

		$ch = curl_init();

		$optArray = array(

				CURLOPT_URL => $url,

				CURLOPT_RETURNTRANSFER => true

		);

		curl_setopt_array($ch, $optArray);

		$result = curl_exec($ch);

		curl_close($ch);

		

		$update = json_decode($result, TRUE);

		$resultMessageId = $update['result']['message_id'];

		

		tgRemovePreviousChatMessage($chatID,$resultMessageId,2,'earning');

	}



	function tgRemovePreviousChatMessage($chatID,$getMessageId,$selector,$session){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}



		global $conn; $token = "100033216:AAFpUx-vJZFa_8cFuw6RBmxGjqcN1spf3bU";

        

        if($session){$sqlSession = "ts_session=".$session." AND";}



        // sendMessage("test");



		// $result = mysqli_query($conn, "SELECT * FROM telegram_session WHERE $sqlSession ts_chat_id='$chatID'");

		$result = mysqli_query($conn, "SELECT * FROM telegram_session WHERE ts_chat_id='$chatID'");

		$row = mysqli_fetch_assoc($result);

		$rowMessageId = $row['ts_note'];

		

		if($selector == 1){

			

				$url = "https://api.telegram.org/bot" . $token . "/deleteMessage?chat_id=" . $chatID;

				$url = $url . "&message_id=" . $rowMessageId;

				$ch = curl_init();

				$optArray = array(

						CURLOPT_URL => $url,

						CURLOPT_RETURNTRANSFER => true

				);



				curl_setopt_array($ch, $optArray);

				$result = curl_exec($ch);

				curl_close($ch);

			

		}

		

		if($selector == 2){

			

			if(($getMessageId-1) == $rowMessageId){

				$url = "https://api.telegram.org/bot" . $token . "/deleteMessage?chat_id=" . $chatID;

				$url = $url . "&message_id=" . $rowMessageId;

				$ch = curl_init();

				$optArray = array(

						CURLOPT_URL => $url,

						CURLOPT_RETURNTRANSFER => true

				);



				curl_setopt_array($ch, $optArray);

				$result = curl_exec($ch);

				curl_close($ch);

			}

		}

		

        if($session){$sqlDeleteSession = "AND ts_session=".$session;}

		// mysqli_query($conn,"DELETE FROM telegram_session WHERE ts_chat_id='$chatID' $sqlDeleteSession");

		mysqli_query($conn,"DELETE FROM telegram_session WHERE ts_chat_id='$chatID'");



        // if($session){$sqlInsertSession = ",".$session;}

		mysqli_query($conn,"INSERT INTO telegram_session (ts_note,ts_chat_id,ts_session) VALUES ('$getMessageId','$chatID','$session')");

	}



    function whatsappMessage($contact,$message){

        $curl = curl_init();



        curl_setopt_array($curl, array(

        CURLOPT_URL => 'https://app.respond.io/api/v1/message/sendContent/'.$contact,

        CURLOPT_RETURNTRANSFER => true,

        CURLOPT_ENCODING => '',

        CURLOPT_MAXREDIRS => 10,

        CURLOPT_TIMEOUT => 0,

        CURLOPT_FOLLOWLOCATION => true,

        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,

        CURLOPT_CUSTOMREQUEST => 'POST',

        CURLOPT_POSTFIELDS =>'{

        "body": [

            {

                "type" : "text",

                "text" : "'.$message.'"

            }

        ]

        }',

        CURLOPT_HTTPHEADER => array(

            'Content-Type: application/json',

            'Authorization: Bearer 35aea252ed4d90aa6904c44ff9837f232ff51c6dd6b628318127b24b214730b30bbd793fac5cc135853d85cbc927c25d8401f36e17ac0f17c9abcd7b8c4e1ef1'

        ),

        ));



        $response = curl_exec($curl);



        curl_close($curl);

        // echo $response;

        $response = json_decode($response,TRUE);

        return $response;

    }



	function telegramGuestList($roomId){

		//control lock send message

		global $telegramCanSendMessage;

		if($telegramCanSendMessage == false){return;}

		

		global $conn;

		$currentDate = date("Y-m-d");

		$result = mysqli_query($conn,"SELECT * FROM reservation WHERE r_id=$roomId AND rv_ci_date='$currentDate'");

		while($row = mysqli_fetch_assoc($result)){

            // if(filterRoomAdmin($row['r_id'],$_SESSION['admin']['a_id'])==true){continue;};



			$propertyId = getDetailByTable('properties',$row['p_id'])['pty_id'];

			$array[$propertyId][0] = $propertyId;

			$guestName = getRVByDateRoom($row['r_id'],$date)['rv_guest'];

			

			if($guestName == ""){

				$array[$propertyId][1] += 1; 

			};

		}



		foreach($array as $value){

			$text .= getDetailByTable('properties',$value[0])['pty_short'];

			$text .= " - ".$value[1]; if($value[1] == NULL){$text .= " ❤️ FULLLY BOOKED";}

			$text .= "

";



		}

	}



	function generateDeposit($apartmentTitle){

		// $getRVApartment = getDetailByTable('reservation',$getReservationId)['rv_apartment'];

		$roomId = getDetailByColumn('router_apartment','ra_title',$apartmentTitle)['r_id'];

		return getDetailByTable('rooms',$roomId)['r_rv_deposit'] ? getDetailByTable('rooms',$roomId)['r_rv_deposit'] : 100;

	}



	function getDetailFromTable($tableName,$rowID){

		global $conn;

		

		$result = mysqli_query($conn, "SHOW COLUMNS FROM $tableName");

		while($row = mysqli_fetch_assoc($result)){

			$column[] = $row['Field'];

		} 

		

		$result = mysqli_query($conn, "SELECT * FROM $tableName WHERE $column[0]=$rowID");

		$array = mysqli_fetch_array($result);

		

		return $array;

	}



	function getDetailByTable($tableName,$rowID){

		global $conn;

		

		$result = mysqli_query($conn, "SHOW COLUMNS FROM $tableName");

		while($row = mysqli_fetch_assoc($result)){

			$column[] = $row['Field'];

		} 

		

		$sql = "SELECT * FROM $tableName WHERE $column[0]=$rowID";

		$result = mysqli_query($conn,$sql);

		$array = mysqli_fetch_array($result);

		

		return $array;

	}



    //similar as getDetailByTable, improveded and better naming

	function detailForWhatsapp($id){

		global $conn;

		

		$sql = "SELECT * FROM reservation AS RV LEFT JOIN rooms AS RM ON RV.r_id=RM.r_id WHERE RV.rv_id=$id";

		$result = mysqli_query($conn,$sql);

		$array = mysqli_fetch_array($result);

		

		return $array;

	}



	function getLastRecord($table,$column){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM $table ORDER BY $column DESC LIMIT 1");

		$array = mysqli_fetch_array($result);

		

		return $array;

	}



	function getDetailByMysqliWhere($table,$where,$id){

		global $conn;



		$result = mysqli_query($conn,"SELECT * FROM $table WHERE $where='$id'");

		$array = mysqli_fetch_array($result);

		return $array;

	}



	function getRVByDateRoom($roomId,$date){

		global $conn;



		$result = mysqli_query($conn,"SELECT * FROM reservation WHERE r_id=$roomId AND '$date' BETWEEN rv_ci_date AND DATE_SUB(rv_co_date,INTERVAL 1 DAY) ORDER BY rv_status DESC");

		$array = mysqli_fetch_array($result);

		return $array;

	}



	function utilityRecord($date,$typeId,$roomId){

		global $conn;



		$result = mysqli_query($conn,"SELECT * FROM utilities WHERE u_type_id=$typeId AND r_id=$roomId AND u_date='$date'");



		if ($result === false) {

			echo mysqli_error($conn);

		} else {

			$array = mysqli_fetch_array($result);

		}

		

		return $array;

	}



	function salaryRecord($staff,$monthyear){

		global $conn;



		$result = mysqli_query($conn,"SELECT * FROM staff_salary WHERE s_id=$staff AND ss_monthyear='$monthyear'");

		return mysqli_fetch_array($result);

	}



	function select($a, $b){

		if($a == $b){ echo " SELECTED"; }

	}



	function checked($a, $b){

		if($a == $b){ echo " CHECKED"; }

	}



	function totalNight($roomId, $date){

		global $conn;

		$result = mysqli_query($conn, "SELECT SUM(rv_night) as total FROM reservation WHERE r_id=$roomId");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}

		

	function totalEarned($roomId, $date){

		global $conn;

		$result = mysqli_query($conn, "SELECT SUM(rv_total) as total FROM reservation WHERE r_id=$roomId");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}

		

	function rental($roomId, $date){

		global $conn;

		$result = mysqli_query($conn, "SELECT * from rooms WHERE r_id=$roomId");

		$row = mysqli_fetch_assoc($result);



		return $row['r_rental'];

	}



	function totalEarnedByMonthYear($date = "",$roomId = "",$status = "",$platform = ""){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));

		$sqlRoom = "";

		$sqlStatus = "";



		if($roomId){$sqlRoom = "AND r_id = ".$roomId;}

		if($status == 1){$sqlStatus = "AND rv_status <= 1";} //status cancelled

		if($status == 2){$sqlStatus = "AND rv_status > 1";} //status confirmed

		if($platform){$sqlPlatform = "AND rv_platform = ".$platform;}



		$sql = "SELECT COUNT(rv_id) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' $sqlRoom $sqlStatus";



		$result = mysqli_query($conn,$sql);

		$row = mysqli_fetch_assoc($result);

		$total = $row['total'];



		return $total;

	}



	function totalConfirmedByMonthYear($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT COUNT(rv_id) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId AND rv_status > 1");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}



	function totalCancelledByMonthYear($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT COUNT(rv_id) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId AND rv_status = 1");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}



	function totalNightsByMonthYear($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT SUM(rv_night) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}



	function totalPaymentsByMonthYear($date = "",$roomId = "",$platform = ""){

		global $conn; 

		$dateStart = $date; 

		$dateEnd = date('Y-m-31', strtotime($date));

		$sqlRoom = "";

		$sqlPlatform = "";

		

		if($roomId){$sqlRoom = "AND r_id = ".$roomId;}

		if($platform){$sqlPlatform = "AND rv_platform = '$platform'";}



		$sql = "SELECT SUM(rv_total) as total, SUM(COALESCE(rv_total_by_import, rv_total)) AS total_by_import FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND rv_status>1 $sqlRoom $sqlPlatform";



		$result = mysqli_query($conn,$sql);

		$row = mysqli_fetch_assoc($result);



		// $total = $row['total'] ? $row['total'] : 0;



		return $row;

	}



	function totalPaymentsByOwner($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT SUM(rv_total) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId AND rv_byowner=1");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'] ? $row['total'] : 0; 



		return $total;

	}



	function totalPaymentsByHost($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT SUM(rv_total) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId AND rv_byowner=0");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'] ? $row['total'] : 0; 



		return $total;

	}



	function utilityAmount($roomId, $date, $utilityType){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM utilities WHERE u_type_id=$utilityType AND u_date='$date' AND r_id=$roomId");



		if (!$result) {

			die('Error in query: ' . mysqli_error($conn));

		}

	

		// Check if any rows were returned

		if (mysqli_num_rows($result) > 0) {

			$row = mysqli_fetch_assoc($result);

			$amount = $row['u_amount'];

			return $amount;

		} else {

			// Handle the case where no rows were returned (e.g., return a default value)

			return 0; // You can adjust this default value based on your needs

		}

	}



	function utilityAmount2($roomId = "", $date = "", $utilityType = ""){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM utilities WHERE u_type_id=$utilityType AND u_date='$date' AND r_id=$roomId");



		if (!$result) {

			die('Error in query: ' . mysqli_error($conn));

		}

	

		// Check if any rows were returned

		$row = mysqli_fetch_assoc($result);

	

		if ($row !== null) {

			return $row;

		} else {

			// Handle the case where no rows were returned

			return null; // You can adjust this based on your needs

		}

	}



	function earningAmount($roomId, $date, $type){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM earning WHERE e_type_id=$type AND e_date='$date' AND r_id=$roomId");



		if (!$result) {

        die('Error in query: ' . mysqli_error($conn));

		}



		// Check if any rows were returned

		$row = mysqli_fetch_assoc($result);



		if ($row != null) {

			return $row['e_amount'];

		} else {

			// Handle the case where no rows were returned

			return 0; // You can adjust this based on your needs

		}

	}



	function totalUtilities($roomId, $date){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT SUM(u_amount) AS Amount FROM utilities WHERE u_date='$date' AND r_id=$roomId");

		$row = mysqli_fetch_assoc($result);

		

		$amount = $row['Amount'];

		

		return $amount;

	}



	function totalCleaningFee($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT SUM(rv_cleaning_fee) as total FROM reservation WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND r_id=$roomId AND rv_status > 1");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'];



		return $total;

	}



	function platformTitle($platformId){

		switch($platformId){

			case 1 : return "Direct"; break;

			case 2 : return "Booking.com"; break;

			case 3 : return "Airbnb"; break;

			case 4 : return "Agoda"; break;

			default : return ""; break;

		}

	}



	function getRecentlyUserId(){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM users ORDER BY u_id DESC LIMIT 1");

		$row = mysqli_fetch_assoc($result);

		

		$userId = $row['u_id'];

		

		return $userId;

	}



	function getRoomId($ownerId){

		global $conn;

		

		$result = mysqli_query($conn, "SELECT * FROM rooms WHERE o_id=$ownerId");

		$row = mysqli_fetch_assoc($result);

		

		return $row['r_id'];

	}



	function roomNo($roomId,$display = NULL){

		global $conn;

		

		$sql = "SELECT * FROM rooms AS R LEFT JOIN properties as P ON R.p_id=P.pty_id WHERE R.r_id=$roomId";



		$result = mysqli_query($conn,$sql);

		$row = mysqli_fetch_assoc($result);

		

		return $row;

	}



	function totalInventoryAmount($roomId){

		global $conn; $amount = 0;

		

		$result = mysqli_query($conn, "SELECT * FROM room_inventory WHERE r_id=$roomId");

		while($row = mysqli_fetch_assoc($result)){

			$price = $row['ri_price'];

			$total = $total + $price;

		}

		

		return $total;

	}



function isMobile() {

    return preg_match("/(android|avantgo|blackberry|bolt|boost|cricket|docomo|fone|hiptop|mini|mobi|palm|phone|pie|tablet|up\.browser|up\.link|webos|wos)/i", $_SERVER["HTTP_USER_AGENT"]);

}



//$day accepted only -1 or +1

function addDaysToDate($date,$day,$plusOrNegative){

	return date('Y-m-d', strtotime($date." ".$plusOrNegative.$day." day"));

}



function fixWhatsappNo($no){

		

		$no = str_replace("+","",$no);

		$no = str_replace("-","",$no);

		$no = str_replace(" ","",$no);

		

		if(substr($no,0,1)==0){

			$no = "6".$no;

		}

		

		if(substr($no,0,1)=="+"){

			$no = substr($no,1);

		}

		

		return $no;

	}



function totalPaid($getReservationId){ global $conn;

		$result = mysqli_query($conn, "SELECT SUM(rvp_amount) AS totalPaid FROM reservation_payment WHERE rv_id=$getReservationId");

		return $row = mysqli_fetch_array($result);

	}



function totalPaidByMonth($roomId,$date){

		global $conn; $dateStart = $date; $dateEnd = date('Y-m-31', strtotime($date));



		$result = mysqli_query($conn, "SELECT SUM(RP.rvp_amount) AS total FROM reservation_payment AS RP LEFT JOIN reservation AS R ON RP.rv_id=R.rv_id WHERE rv_ci_date BETWEEN '$dateStart' AND '$dateEnd' AND R.r_id=$roomId");

		$row = mysqli_fetch_assoc($result);



		$total = $row['total'] ? $row['total'] : 0; 



		return $total;

	}



function getRoomByApartment($apartment){

        global $conn;



        $result = mysqli_query($conn, "SELECT COUNT(RV.rv_id) AS total, RV.rv_apartment AS apartment, RM.r_id AS room_id, RM.r_no AS room_no FROM `reservation` AS RV INNER JOIN `rooms` AS RM ON RV.r_id=RM.r_id WHERE RV.r_id!=0 AND RV.rv_apartment='$apartment' group by RV.rv_apartment, RV.r_id");



        while($row = mysqli_fetch_assoc($result)){

            $rows[] = $row;

        }



        return $row;

    }



function logs($message,$reservation_id = 0,$deposit_id = 0,$payment_id = 0){

	global $conn; $ipAddress = $_SERVER['REMOTE_ADDR']; $adminId = $_SESSION['admin']['a_id'];

	mysqli_query($conn, "INSERT INTO logs (lg_message,rv_id,dp_id,a_id,lg_ip_address) VALUES ('$message',$reservation_id,$deposit_id,$payment_id,$adminId,'$ipAddress')");

}



function reservationLog($reservationId,$title){ global $conn; $ipAddress = $_SERVER['REMOTE_ADDR']; $adminId = $_SESSION['admin']['a_id'];

		mysqli_query($conn, "INSERT INTO reservation_log (rv_id,rl_title,rl_ip_address,a_id) VALUES ($reservationId,'$title','$ipAddress',$adminId)");

	} //to be removed



function utilityLog($utilityId,$roomId,$type,$report){ global $conn; $ipAddress = $_SERVER['REMOTE_ADDR']; $adminId = $_SESSION['admin']['a_id'];

		mysqli_query($conn, "INSERT INTO utility_log (uty_id,r_id,utyt_id,utyl_report,utyl_ip_address,a_id) VALUES ($utilityId,$roomId,$type,'$report','$ipAddress',$adminId)");

	}



function expensesLog($roomId,$type,$report){ global $conn; $ipAddress = $_SERVER['REMOTE_ADDR']; $adminId = $_SESSION['admin']['a_id'];

		mysqli_query($conn, "INSERT INTO expenses_log (r_id,exlt_id,exl_report,exl_ip_address,a_id) VALUES ($roomId,$type,'$report','$ipAddress',$adminId)");

	}



function paymentLog($rvId,$report){ global $conn; $ipAddress = $_SERVER['REMOTE_ADDR']; $adminId = $_SESSION['admin']['a_id'];

		mysqli_query($conn, "INSERT INTO reservation_payment_log (rv_id,rvpl_report,rvpl_ip_address,a_id) VALUES ($rvId,'$report','$ipAddress',$adminId)");

	}





	function totalExpenses($getDate,$room,$type = NULL,$admin = NULL,$paymentStatus = 0){

		global $conn;



		if($getDate){

			$dateStart = $getDate; $dateEnd = date('Y-m-31', strtotime($getDate));

			$sqlDate = "re_date BETWEEN '$dateStart' AND '$dateEnd' AND";

		}



		if($type == 'initial'){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE $sqlDate r_id=$room AND re_initial=1");

		}elseif($type == 'expenses'){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE $sqlDate r_id=$room AND re_initial=0 AND re_type!=11");

		}elseif($type == 'spent'){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE $sqlDate r_id=$room AND re_initial=0");

		}elseif($type == 'initial_rental_deposit'){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE $sqlDate r_id=$room AND re_type=11");

		}else{

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE $sqlDate r_id=$room");

		}



		return mysqli_fetch_assoc($result);

	}//used for room_expenses.php



	function expenses($room,$type = NULL,$admin = NULL,$paymentStatus = NULL){

		global $conn;



		if(!$room){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses");

			return mysqli_fetch_assoc($result);

			exit;

		}



		if($admin){$sqlAdmin = "AND re_claim_by=$admin";}

		if($paymentStatus){$sqlPaymentStatus = "AND re_claim_status=$paymentStatus";}



		if($type == 'initial' || $type == 'expenses'){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE r_id=$room AND re_initial=1 $sqlAdmin $sqlPaymentStatus");

		}elseif($type == 'spent' || $type == NULL){

			$result = mysqli_query($conn,"SELECT SUM(re_amount) AS total FROM room_expenses WHERE r_id=$room $sqlAdmin $sqlPaymentStatus");

		}



		return mysqli_fetch_assoc($result);

	}



	function rmFormat($input){

		$input = round($input,2);

		if($input){

			if($input < 0){return "<span style='color:red; font-weight:bold;'>RM ".$input."</span>";}else{return "RM ".$input;}

		}else{return "-";}

	}



	function accessCardType($id){

		switch($id){

			case 1 : return "DOOR"; break;

			case 2 : return "PARKING"; break;

			case 3 : return "DOOR & PARKING"; break;

		}

	}



    function filterRoomAdmin($roomId,$adminId){

        global $conn;

        

        $result = mysqli_query($conn,"SELECT * FROM room_admin WHERE room_id=$roomId AND admin_id=$adminId");

        $rowNums = mysqli_num_rows($result);



        if($rowNums==0){

            return true;

        }

    }



    function filterPropertyAdmin($propertyId,$adminId){

        global $conn;

        

        $result = mysqli_query($conn,"SELECT * FROM property_admin WHERE property_id=$propertyId AND admin_id=$adminId");

        $rowNums = mysqli_num_rows($result);



        if($rowNums==0){

            return true;

        }

    }



    function platforms($value = ""){

        $array = array(

            [

                "id" => 1,

                "title" => "Direct"

            ],

            [

                "id" => 2,

                "title" => "Booking.com"

            ],

            [

                "id" => 3,

                "title" => "Airbnb"

            ],

            [

                "id" => 4,

                "title" => "Agoda"

            ]

            );



        if($value){

            foreach($array as $item){

                if($item['id'] == $value){

                    return $item; break;

                }

            }

        }else{

            return $array;

        }

    }



    function occuptionType($value = ""){

        $array = array(

            [

                "id" => 0,

                "title" => "ENTIRE"

            ],

            [

                "id" => 1,

                "title" => "MASTER"

            ],

            [

                "id" => 2,

                "title" => "MIDDLE"

            ],

            [

                "id" => 3,

                "title" => "SINGLE"

            ]

            );

		

		if($value == 'ALL'){

			return $array;

		}



        if($value != NULL){

            foreach($array as $item){

                if($item['id'] == $value){

                    return $item; break;

                }

            }

        }

    }



    function roomBathroom($value = ""){

        $array = array(

            [

                "id" => 0,

                "title" => "1R",

                "unit" => 1

            ],

            [

                "id" => 1,

                "title" => "1R1B",

                "unit" => 2

            ],

            [

                "id" => 2,

                "title" => "2R1B",

                "unit" => 3

            ],

            [

                "id" => 3,

                "title" => "2R2B",

                "unit" => 4

            ],

            [

                "id" => 4,

                "title" => "3R1B",

                "unit" => 4

            ],

            [

                "id" => 5,

                "title" => "3R2B",

                "unit" => 5

            ],

            [

                "id" => 6,

                "title" => "3R3B",

                "unit" => 6

            ],

            [

                "id" => 7,

                "title" => "4R1B",

                "unit" => 5

            ],

            [

                "id" => 8,

                "title" => "4R2B",

                "unit" => 6

            ],

            [

                "id" => 9,

                "title" => "4R3B",

                "unit" => 7

            ]

            );

		

		if($value == 'ALL'){

			return $array;

		}



        if($value != NULL){

            foreach($array as $item){

                if($item['id'] == $value){

                    return $item; break;

                }

            }

        }

    }



    function authorizationType($value = ""){

        $array = array(

            [

                "id" => 0,

                "title" => "NONE"

            ],

            [

                "id" => 1,

                "title" => "ADMIN"

            ],

            [

                "id" => 2,

                "title" => "COMMUNICATOR"

            ],

            [

                "id" => 3,

                "title" => "HOUSEKEEPER"

            ]

            );

		

		if($value == 'ALL'){

			return $array;

		}



        if($value != NULL){

            foreach($array as $item){

                if($item['id'] == $value){

                    return $item; break;

                }

            }

        }

    }



    function maintenanceType($value = ""){

        $array = array(

            [

                "id" => 0,

                "title" => "AirCond"

			],

            [

                "id" => 1,

                "title" => "Major Cleaning"

            ]

            );



        if($value){

            foreach($array as $item){

                if($item['id'] == $value){

                    return $item; break;

                }

            }

        }else{

            return $array;

        }

    }



    function staffStatus($value = ""){

		$statuses = [

			[

				"id" => 0,

				"title" => "Suspended"

			],

			[

				"id" => 1,

				"title" => "Active"

			]

		];

	

		foreach($statuses as $status){

			if($status['id'] == $value){

				return $status;

			}

		}

	

		return 'Unknown';

	}



    function telegramRouter($value = ""){

		$data = [

			[

				"id" => 0,

				"name" => "DZV_BOT",

				"chat_id" => "Suspended"

			],

			[

				"id" => 1,

				"name" => "Suspended",

				"chat_id" => "Suspended"

			]

		];



		if ($value === "ALL") {

			return $data;

		}

	

		foreach($data as $item){

			if($item['id'] == $value){

				return $item['chat_id'];

			}

		}

	

		return 'Unknown';

	}



	function getRelationshipById($id = ""){

		$relationships = [

			["id" => 1, "title" => "Mother / Ibu"],

			["id" => 2, "title" => "Father / Bapa"],

			["id" => 3, "title" => "Brother / Abang"],

			["id" => 4, "title" => "Sister / Kakak"],

			["id" => 5, "title" => "Husband / Suami"],

			["id" => 6, "title" => "Wife / Isteri"],

			["id" => 7, "title" => "Son / Anak Lelaki"],

			["id" => 8, "title" => "Daughter / Anak Perempuan"],

			["id" => 9, "title" => "Grandparent / Datuk/Nenek"],

			["id" => 10, "title" => "Aunt / Makcik"],

			["id" => 11, "title" => "Uncle / Pakcik"],

			["id" => 12, "title" => "Cousin / Sepupu"],

			["id" => 13, "title" => "Niece / Anak Saudara Perempuan"],

			["id" => 14, "title" => "Nephew / Anak Saudara Lelaki"],

			["id" => 15, "title" => "Legal Guardian / Penjaga yang sah"],

			["id" => 16, "title" => "Friend / Kawan"],

			["id" => 17, "title" => "Roommate / Rakan Serumah"],

			["id" => 18, "title" => "Co-worker / Rakan Sekerja"],

			["id" => 19, "title" => "Neighbor / Jiran"],

			["id" => 20, "title" => "Partner / Pasangan"],

			["id" => 21, "title" => "Father-in-law / Bapa Mertua"],

			["id" => 22, "title" => "Mother-in-law / Ibu Mertua"],

			["id" => 23, "title" => "Brother-in-law / Abang Ipar"],

			["id" => 24, "title" => "Sister-in-law / Kakak Ipar"]

		];



		if ($id === "ALL") {

			return $relationships;

		}

	

		foreach ($relationships as $relationship) {

			if ($relationship['id'] == $id) {

				return $relationship;

			}

		}

	

		return "Unknown";  // Default return when no ID matches

	}	



    function mysqlResuitInArray($table,$where,$value){

        global $conn;

        $result = mysqli_query($conn,"SELECT * FROM $table WHERE $where='$value'");



        while($row = mysqli_fetch_assoc($result)){

            $rows[] = $row;

        }

        

        return $rows;

    }



	function getAverageBill($room,$type){

		global $conn;



		$result = mysqli_query($conn,"SELECT AVG(u_amount) AS average_amount FROM utilities WHERE u_type_id=$type AND r_id=$room");

		$row = mysqli_fetch_assoc($result);



		return $row['average_amount'];

	}

?>