<?php
class OtherServiceCharges{

    // database connection and table name
	private $conn;
	private $table_name = "other_service_charges";

	//field list start
	public $id;
	public $date;
	public $checkin_id;
	public $room_no;
	public $customer_name;
	public $payment_type;
	public $payment_date_time;
	public $category;
	public $type;
	public $item_code;
	public $item_name;
	public $foc;
	public $quantity;
	public $price;
	public $entry_by;
	public $update_by;
	public $is_deleted;

	public $cashier;
	public $df;
	public $dt;
	//field list end

	public function __construct($db){
		$this->conn = $db;
	}
	
	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET date=:date, pickup_id=:checkin_id, vehicle_no=:room_no, customer_name=:customer_name
		, payment_date_time=:payment_date_time, payment_type=:payment_type,  category=:category, type=:type, item_code=:item_code, item_name=:item_name
		, foc=:foc, quantity=:quantity, price=:price, entry_by=:entry_by";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":date", $this->date);
		$stmt->bindParam(":checkin_id", $this->checkin_id);
		$stmt->bindParam(":room_no", $this->room_no);
		$stmt->bindParam(":customer_name", $this->customer_name);
		$stmt->bindParam(":payment_date_time", $this->payment_date_time);
		$stmt->bindParam(":payment_type", $this->payment_type);

		$stmt->bindParam(":category", $this->category);
		$stmt->bindParam(":type", $this->type);
		$stmt->bindParam(":item_code", $this->item_code);
		$stmt->bindParam(":item_name", $this->item_name);
		$stmt->bindParam(":foc", $this->foc);
		$stmt->bindParam(":quantity", $this->quantity);
		$stmt->bindParam(":price", $this->price);
		$stmt->bindParam(":entry_by", $this->entry_by);

		if($stmt->execute()){
			return true;
		}	 
		return false;
	}	

	function getAllItemByCheckInId(){
		$query = "SELECT * from " . $this->table_name . " WHERE pickup_id=:checkin_id AND payment_type='Charge To Room'";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":checkin_id", $this->checkin_id);
		$stmt->execute();
		return $stmt;
	}

	function updateCashOut(){
		$query = "UPDATE `" . $this->table_name . "` SET payment_type=:payment_type, payment_date_time=:payment_date_time, cashier=:cashier WHERE pickup_id=:checkin_id AND payment_type='Charge To Room'";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":checkin_id", $this->checkin_id);
		$stmt->bindParam(":payment_type", $this->payment_type);
		$stmt->bindParam(":payment_date_time", $this->payment_date_time);
		$stmt->bindParam(":cashier", $this->cashier);
	 
		if($stmt->execute()){
			return true;
		}
		return false;
	}

			// UNION ALL
		// SELECT 'Room Income' AS description,SUM(room_rate+(extra_bed*extra_bed_rate)+(extra_person*extra_person_rate)) AS p FROM checkin_detail 
		// where checkout_date>=:df AND checkout_date<=:dt 
	function searchCashIn(){
		$query = "
		SELECT 'Room Income' AS description,SUM(deposit_amount) AS p FROM deposit 
		WHERE deposit_date>=:df AND deposit_date<=:dt

		UNION ALL
		SELECT 'Other Service Charges' AS description,sum(quantity*price) AS p FROM other_service_charges 
		WHERE payment_date_time<>'' AND foc=0 AND substring(payment_date_time,1,10)>=:df AND substring(payment_date_time,1,10)<=:dt
		UNION ALL
		SELECT 'Income' as description,SUM(quantity*price) AS p FROM income 
		WHERE date>=:df AND date<=:dt AND is_deleted IS NULL
		UNION ALL
		SELECT 'Petty Cash Income' as description,SUM(quantity*price) AS p FROM expense 
		WHERE date>=:df AND date<=:dt AND is_deleted IS NULL and account_code='1-2120'
		";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function searchCashOut(){
		// $query = "SELECT CONCAT('Purchase - ', voucher_no) AS description, sum(quantity*price) as p from purchase_detail 
		// join purchase on purchase.id=purchase_detail.purchase_id 
		// where purchase.is_deleted is null and purchase_detail.is_deleted is NULL and
		// DATE >= :df AND DATE <= :dt group by purchase_detail.purchase_id 
		// UNION ALL
		$query = "SELECT 'Purchase' AS description, sum(quantity*price) as p from purchase_detail 
		join purchase on purchase.id=purchase_detail.purchase_id 
		where purchase.is_deleted is null and purchase_detail.is_deleted is NULL and
		DATE >= :df AND DATE <= :dt 
		UNION ALL
		SELECT concat(account_code,' > ',account_head) AS description, 
		SUM(quantity * price) AS p
		FROM expense
		WHERE DATE >= :df AND DATE <= :dt AND is_deleted IS NULL
		GROUP BY account_code;
					";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function searchMonthlyDetail(){

		$condition = "";

		if($this->r=="In"){
			if($this->st=="Room Income"){
				$condition .= "SELECT deposit_date AS d,concat('Vehicle No.',' ',deposit.vehicle_no) as ii,concat(deposit.booking_no,'-',customer_name) as particular,
				(deposit_amount) AS p  FROM deposit LEFT JOIN booking ON deposit.booking_no=booking.booking_no WHERE deposit_date>=:df AND
				deposit_date<=:dt ORDER BY deposit_date";
			}else if($this->st=="ss"){
				$condition .= "SELECT bd.date AS d,concat('Vehicle No.',' ',b.vehicle_no) as ii
				,concat(b.booking_no,'-',b.customer_name) as particular,
				SUM(bd.rental_rate+(bd.extra_driver*bd.extra_driver_rate)+(bd.extra_passenger*bd.extra_passenger_rate)) AS p
				FROM booking_detail bd
				LEFT JOIN booking b ON bd.booking_no=b.booking_no
				WHERE bd.date>=:df AND bd.date<=:dt
				GROUP BY bd.booking_no,b.vehicle_no";
			}else if($this->st=="Other Service Charges"){
				$condition .= "SELECT date AS d,concat(customer_name,' ',vehicle_no) as ii,item_name AS particular,quantity*price AS p FROM other_service_charges
				where payment_date_time<>'' AND foc=0 AND date>=:df AND date<=:dt order by payment_date_time";
			}else if($this->st=="Income"){
				$condition .= "SELECT date as d,concat(account_code,' ',account_head) as ii,particular,(quantity*price) AS p FROM income WHERE date>=:df AND date<=:dt and is_deleted is null  order by date";
			}else if($this->st=="Petty Cash Income"){
				$condition .= "SELECT date as d,concat(account_code,' ',account_head) as ii,particular,(quantity*price) AS p FROM expense WHERE date>=:df AND date<=:dt and is_deleted is null and account_code='1-2120' order by date";
			}
		}else if($this->r=="Out"){
			if($this->st=="Purchase"){
				$condition .= "SELECT DATE AS d,concat('Voucher No.',' ',voucher_no) as ii,item_name AS particular,quantity*price AS p FROM purchase_detail 
				pd LEFT JOIN purchase p ON pd.purchase_id=p.id WHERE p.is_deleted IS NULL AND DATE>=:df AND date<=:dt order by date,pd.id";
			}else{

				$parts = explode(' > ', $this->st);
				$firstPart = $parts[0];


				$condition .= "SELECT date as d,account_head as ii,particular,(quantity*price) AS p FROM expense WHERE is_deleted is null and date>=:df AND date<=:dt and account_code='$firstPart' order by date";
			}
		}


		$query = $condition;

		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function SearchCashBookOffice(){
		if($this->type){
			if($this->type=="main cash"){
				$query = "SELECT * FROM (SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,0 AS in_amt,(quantity*price) AS ex_amt FROM expense 
				LEFT JOIN coa ON expense.account_code=coa.account_code
				WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and type='main cash'
				UNION ALL
				SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,(quantity*price) AS in_amt,0 AS ex_amt FROM income 
				LEFT JOIN coa ON income.account_code=coa.account_code
				WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and type='main cash'
				UNION ALL
				SELECT deposit_date,'Rental Income' as toe,'Rental Income' AS account_head,vehicle_no AS head,CONCAT(deposit.booking_no, ' (', agent_name,')') AS particular,(deposit_amount) AS in_amt,0 AS ex_amt FROM deposit left join (select booking_no,agent_name from booking group by booking_no) as a on deposit.booking_no=a.booking_no
				WHERE deposit_date>=:df AND deposit_date<=:dt 
				-- UNION ALL
				-- SELECT deposit_date,'Room Paid Out' as toe,'Room Paid Out' AS account_head,room_no AS head,'Paid Out' AS particular,0 AS in_amt,(deposit_amount*-1) AS ex_amt FROM deposit 
				-- WHERE deposit_date>=:df AND deposit_date<=:dt AND deposit_status='paid out'
				) AS a WHERE date IS not NULL AND (in_amt<>0 or ex_amt<>0) ORDER BY date,particular";
			}else if($this->type=="petty cash"){
				$query = "SELECT * FROM (
					SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,0 AS in_amt,(quantity*price) AS ex_amt FROM expense 
					LEFT JOIN coa ON expense.account_code=coa.account_code
					WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and `type`='petty cash'
					UNION ALL
					SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,(quantity*price) AS in_amt,0 AS ex_amt FROM income 
					LEFT JOIN coa ON income.account_code=coa.account_code
					WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and `type`='petty cash'
					) AS a where  (in_amt<>0 or ex_amt<>0)  ORDER BY date,particular";
			}else if($this->type=="transfer"){
				$query = "SELECT * FROM (
					SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,0 AS in_amt,(quantity*price) AS ex_amt FROM expense 
					LEFT JOIN coa ON expense.account_code=coa.account_code
					WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and `type` = 'transfer'
					UNION All
					SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,(quantity*price) AS in_amt,0 AS ex_amt FROM expense 
					LEFT JOIN coa ON expense.account_code=coa.account_code
					WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL and `type` = 'transfer'
					) AS a where  (in_amt<>0 or ex_amt<>0)  ORDER BY date,particular";
			}else{
				$query = "SELECT * FROM ( 
				SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,0 AS in_amt,(quantity*price) AS ex_amt FROM expense 
				LEFT JOIN coa ON expense.account_code=coa.account_code
				WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL
				UNION ALL
				SELECT date,type_of_expense as toe,account_type as account_head,coa.account_head AS head,particular,(quantity*price) AS in_amt,0 AS ex_amt FROM income 
				LEFT JOIN coa ON income.account_code=coa.account_code
				WHERE `date`>=:df AND `date`<=:dt and is_deleted is NULL
				UNION ALL
				SELECT deposit_date,'Rental Income' as toe,'Rental Income' AS account_head,vehicle_no AS head,CONCAT(deposit.booking_no, ' (', agent_name,')') AS particular,(deposit_amount) AS in_amt,0 AS ex_amt FROM deposit left join (select booking_no,agent_name from booking group by booking_no) as a on deposit.booking_no=a.booking_no
				WHERE deposit_date>=:df AND deposit_date<=:dt 
				-- UNION ALL
				-- SELECT deposit_date,'Room Paid Out' as toe,'Room Paid Out' AS account_head,room_no AS head,'Paid Out' AS particular,0 AS in_amt,(deposit_amount*-1) AS ex_amt FROM deposit 
				-- WHERE deposit_date>=:df AND deposit_date<=:dt AND deposit_status='paid out'
				) AS a WHERE date IS not NULL AND (in_amt<>0 or ex_amt<>0) ORDER BY date,particular
				";
			}
		}
		
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		$stmt->execute();
		return $stmt;
	}

	function SearchOpeningBalanceByMain(){

		$query = "SELECT 
		IFNULL(i, 0) + IFNULL(d, 0)  - IFNULL(e, 0) AS balance FROM 
		(SELECT SUM(quantity * price) AS e FROM expense WHERE `type`='main cash' and `date` < :df  and is_deleted is null) AS b,
		(SELECT SUM(quantity * price) AS i FROM income WHERE `type`='main cash' and `date` < :df  and is_deleted is null) AS c,
		(SELECT SUM(deposit_amount) as d FROM deposit WHERE deposit_date < :df) AS d
		-- (SELECT SUM(quantity*price) as p
		-- FROM purchase_detail pd LEFT JOIN purchase p ON pd.purchase_id=p.id 
		-- WHERE pd.is_deleted IS NULL AND DATE<:df) AS p,
		-- (SELECT SUM(quantity*price) as osc
		-- FROM purchase_detail pd LEFT JOIN purchase p ON pd.purchase_id=p.id 
		-- WHERE pd.is_deleted IS NULL AND DATE<:df) AS osc
		";

		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);

		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['balance'];
		}
		return 0;	
	}

	function SearchOpeningBalanceByPetty(){

		$query = "SELECT 
		IFNULL(i, 0) - IFNULL(e, 0) AS balance FROM 
		(SELECT SUM(quantity * price) AS e FROM expense WHERE `type` = 'petty cash' and `date` < :df  and is_deleted is null) AS b,
		(SELECT SUM(quantity * price) AS i FROM income WHERE `type` = 'petty cash' and `date` < :df  and is_deleted is null) AS c
		";

		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);

		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['balance'];
		}
		return 0;	
	}

	function SearchOpeningBalanceByTransfer(){

		$query = "SELECT 
		IFNULL(i, 0) - IFNULL(e, 0) AS balance FROM 
		(SELECT SUM(quantity * price) AS e FROM expense WHERE `type` = 'transfer' and `date` < :df) AS b,
		(SELECT SUM(quantity * price) AS i FROM expense WHERE `type` = 'transfer' and `date` < :df) AS c
		";

		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);

		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['balance'];
		}
		return 0;	
	}

	function SearchOpeningBalance(){

		$query = "SELECT 
		IFNULL(i, 0) + IFNULL(d, 0) - IFNULL(e, 0)   AS balance FROM 
		(SELECT SUM(quantity * price) AS e FROM expense WHERE `date` < :df and is_deleted is null) AS b,
		(SELECT SUM(quantity * price) AS i FROM income WHERE `date` < :df and is_deleted is null) AS c,
		(SELECT SUM(deposit_amount) as d FROM deposit WHERE deposit_date < :df) AS d
		-- (SELECT SUM(quantity*price) as p
		-- FROM purchase_detail pd LEFT JOIN purchase p ON pd.purchase_id=p.id 
		-- WHERE pd.is_deleted IS NULL AND DATE<:df) AS p,
		-- (SELECT SUM(quantity*price) as osc
		-- FROM purchase_detail pd LEFT JOIN purchase p ON pd.purchase_id=p.id 
		-- WHERE pd.is_deleted IS NULL AND DATE<:df) AS osc
		";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":df", $this->df);

		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['balance'];
		}
		return 0;	
	}

	// function delete(){
	// 	$query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
	// 	$stmt = $this->conn->prepare($query);
	// 	$this->id = htmlspecialchars(strip_tags($this->id));
	// 	$stmt->bindParam(1, $this->id);
	// 	if($stmt->execute()){
	// 		return true;
	// 	}
	// 	return false;
	// }

	// function getOtherServiceChargesDetail(){
	// 	$query = "SELECT *, entry_by AS receipt_by FROM " . $this->table_name . " WHERE payment_date_time=:payment_date_time AND checkin_id=0";

	// 	$stmt = $this->conn->prepare($query);
	// 	$stmt->bindParam(":payment_date_time", $this->payment_date_time);	
	// 	$stmt->execute();
	// 	return $stmt;
	// }

	// function getAllDetails(){
	// 	$query = "SELECT * FROM ((SELECT id, `date`,department, service_category, service, cashier AS receipt_by, room_no, payment_type, quantity, price, commercial_tax, service_charges, amount, foc FROM other_service_charges WHERE checkin_id=:checkin_id AND payment_date_time=:payment_date_time ORDER BY `date`)
	// 	UNION (SELECT CONCAT('999999', fnb_pos_detail.id) AS id, `date`, 'F&B' AS department, '' AS service_category, CONCAT(item, ' (', meal_period, ')') AS service, cashier AS receipt_by, room_no, payment_type, quantity, price, commercial_tax, service_charges, amount, foc FROM fnb_pos LEFT JOIN fnb_pos_detail ON fnb_pos.invoice_id = fnb_pos_detail.invoice_id WHERE checkin_id=:checkin_id AND payment_date_time=:payment_date_time ORDER BY `date`)) AS tbl ORDER BY `date`, id";

	// 	$stmt = $this->conn->prepare($query);
	// 	$stmt->bindParam(":payment_date_time", $this->payment_date_time);	
	// 	$stmt->bindParam(":checkin_id", $this->checkin_id);	
	// 	$stmt->execute();
	// 	return $stmt;
	// }

	// function getReceiptByCID(){
	// 	$query = "SELECT * FROM ((SELECT id, `date`, department, service, quantity, price, commercial_tax, service_charges, amount, foc FROM other_service_charges WHERE payment_type='Charge To Room' AND checkin_id=:checkin_id AND `date`<=:date ORDER BY `date`) UNION (SELECT CONCAT('999999', fnb_pos_detail.id) AS id, `date`, 'F&B' AS department, concat(item, ' (' , meal_period, ')') AS service, quantity, price, commercial_tax, service_charges, amount, foc FROM fnb_pos LEFT JOIN fnb_pos_detail ON fnb_pos.invoice_id = fnb_pos_detail.invoice_id WHERE payment_type='Charge To Room' AND checkin_id=:checkin_id AND `date`<=:date ORDER BY `date`)) AS tbl ORDER BY `date`, id";
	// 	$stmt = $this->conn->prepare($query);

	// 	$this->checkin_id=htmlspecialchars(strip_tags($this->checkin_id));
	// 	$this->date=htmlspecialchars(strip_tags($this->date));

	// 	$stmt->bindParam(":checkin_id", $this->checkin_id);
	// 	$stmt->bindParam(":date", $this->date);

	// 	$stmt->execute();
	// 	return $stmt;
	// }



	// function search(){

	// 	$condition = "";

	// 	if($this->df){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " `date` >=:df";
	// 	}

	// 	if($this->dt){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " `date` <=:dt";
	// 	}

	// 	if($this->department){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " department =:department";
	// 	}

	// 	if($this->service_category){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " service_category =:service_category";
	// 	}

	// 	if($this->item_code){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " item_code =:item_code";
	// 	}

	// 	if($this->service){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " (service LIKE  :service '%' or service LIKE '%' :service '%' or service Like '%' :service ) ";
	// 	}
		

	// 	if($condition!=""){
	// 		$condition = " WHERE " . $condition;
	// 	}

	// 	$query = "SELECT other_service_charges.*, COALESCE(item.price, other_service_charges.amount ) as capital_price, ((other_service_charges.price - COALESCE(item.price, other_service_charges.amount )) * other_service_charges.quantity) as profit FROM " . $this->table_name . " left join item on other_service_charges.item_code=item.code " . $condition;

	// 	$stmt = $this->conn->prepare($query);
	// 	$this->df=htmlspecialchars(strip_tags($this->df));
	// 	$this->dt=htmlspecialchars(strip_tags($this->dt));
	// 	$this->department=htmlspecialchars(strip_tags($this->department));
	// 	// $this->service_category=htmlspecialchars(strip_tags($this->service_category));
	// 	$this->item_code=htmlspecialchars(strip_tags($this->item_code));
	// 	$this->service=htmlspecialchars(strip_tags($this->service));

	// 	if($this->df) $stmt->bindParam(":df", $this->df);
	// 	if($this->dt) $stmt->bindParam(":dt", $this->dt);
	// 	if($this->department) $stmt->bindParam(":department", $this->department);
	// 	if($this->service_category) $stmt->bindParam(":service_category", $this->service_category);
	// 	if($this->item_code) $stmt->bindParam(":item_code", $this->item_code);
	// 	if($this->service) $stmt->bindParam(":service", $this->service);
	// 	$stmt->execute();
	// 	return $stmt;
	// }

	// function updateCheckOut(){
	// 	$query = "UPDATE " . $this->table_name . " SET check_out=1 WHERE checkin_id=:checkin_id AND room_no=:room_no";
	// 	$stmt = $this->conn->prepare($query);
	// 	$stmt->bindParam(":checkin_id", $this->checkin_id);
	// 	$stmt->bindParam(":room_no", $this->room_no);
	// 	if($stmt->execute()){
	// 		return true;
	// 	}
	// 	return false;
	// }
}
?>
