function.php

<?php
//function.php

function fill_category_list($connect)
{
	$query = "
	SELECT * FROM category
	WHERE category_status = 'active'
	ORDER BY category_name ASC
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$output = '';
	foreach($result as $row)
	{
		$output .= '<option value="'.$row["category_id"].'">'.$row["category_name"].'</option>';
	}
	return $output;
}

function fill_brand_list($connect, $category_id)
{
	$query = "SELECT * FROM brand
	WHERE brand_status = 'active'
	AND category_id = '".$category_id."'
	ORDER BY brand_name ASC";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$output = '<option value="">Select Brand</option>';
	foreach($result as $row)
	{
		$output .= '<option value="'.$row["brand_id"].'">'.$row["brand_name"].'</option>';
	}
	return $output;
}

function get_user_name($connect, $user_id)
{
	$query = "
	SELECT user_name FROM user_details WHERE user_id = '".$user_id."'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		return $row['user_name'];
	}
}

function fill_product_list($connect)
{
	$query = "
	SELECT * FROM product
	WHERE product_status = 'active'
	ORDER BY product_name ASC
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$output = '';
	foreach($result as $row)
	{
		$output .= '<option value="'.$row["product_id"].'">'.$row["product_name"].'</option>';
	}
	return $output;
}
function fetch_product_details($product_id, $connect)
{
	$query = "
	SELECT * FROM product
	WHERE product_id = '".$product_id."'";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		$output['product_name'] = $row["product_name"];
		$output['quantity'] = $row["product_quantity"];
		$output['price'] = $row['product_base_price'];
		$output['tax'] = $row['product_tax'];
	}
	return $output;
}

function available_product_quantity($connect, $product_id)
{
	$product_data = fetch_product_details($product_id, $connect);
	$query = "
	SELECT 	inventory_order_product.quantity FROM inventory_order_product
	INNER JOIN inventory_order ON inventory_order.inventory_order_id = inventory_order_product.inventory_order_id
	WHERE inventory_order_product.product_id = '".$product_id."' AND
	inventory_order.inventory_order_status = 'active'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$total = 0;
	foreach($result as $row)
	{
		$total = $total + $row['quantity'];
	}
	$available_quantity = intval($product_data['quantity']) - intval($total);
	if($available_quantity == 0)
	{
		$update_query = "
		UPDATE product SET
		product_status = 'inactive'
		WHERE product_id = '".$product_id."'
		";
		$statement = $connect->prepare($update_query);
		$statement->execute();
	}
	return $available_quantity;
}

function count_total_user($connect)
{
	$query = "
	SELECT * FROM user_details WHERE user_status='active'";
	$statement = $connect->prepare($query);
	$statement->execute();
	return $statement->rowCount();
}

function count_total_category($connect)
{
	$query = "
	SELECT * FROM category WHERE category_status='active'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	return $statement->rowCount();
}

function count_total_brand($connect)
{
	$query = "
	SELECT * FROM brand WHERE brand_status='active'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	return $statement->rowCount();
}

function count_total_product($connect)
{
	$query = "
	SELECT * FROM product WHERE product_status='active'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	return $statement->rowCount();
}

function count_total_order_value($connect)
{
	$query = "
	SELECT sum(inventory_order_total) as total_order_value FROM inventory_order
	WHERE inventory_order_status='active'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		return number_format($row['total_order_value'], 2);
	}
}

function count_total_cash_order_value($connect)
{
	$query = "
	SELECT sum(inventory_order_total) as total_order_value FROM inventory_order
	WHERE payment_status = 'cash'
	AND inventory_order_status='active'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		return number_format($row['total_order_value'], 2);
	}
}

function count_total_credit_order_value($connect)
{
	$query = "
	SELECT sum(inventory_order_total) as total_order_value FROM inventory_order WHERE payment_status = 'credit' AND inventory_order_status='active'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= ' AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		return number_format($row['total_order_value'], 2);
	}
}

function get_user_wise_total_order($connect)
{
	$query = '
	SELECT sum(inventory_order.inventory_order_total) as order_total,
	SUM(CASE WHEN inventory_order.payment_status = "cash" THEN inventory_order.inventory_order_total ELSE 0 END) AS cash_order_total,
	SUM(CASE WHEN inventory_order.payment_status = "credit" THEN inventory_order.inventory_order_total ELSE 0 END) AS credit_order_total,
	user_details.user_name
	FROM inventory_order
	INNER JOIN user_details ON user_details.user_id = inventory_order.user_id
	WHERE inventory_order.inventory_order_status = "active" GROUP BY inventory_order.user_id
	';
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$output = '
	<div class="table-responsive">
		<table class="table table-bordered table-striped">
			<tr>
				<th>User Name</th>
				<th>Total Order Value</th>
				<th>Total Cash Order</th>
				<th>Total Credit Order</th>
			</tr>
	';

	$total_order = 0;
	$total_cash_order = 0;
	$total_credit_order = 0;
	foreach($result as $row)
	{
		$output .= '
		<tr>
			<td>'.$row['user_name'].'</td>
			<td align="right">$ '.$row["order_total"].'</td>
			<td align="right">$ '.$row["cash_order_total"].'</td>
			<td align="right">$ '.$row["credit_order_total"].'</td>
		</tr>
		';

		$total_order = $total_order + $row["order_total"];
		$total_cash_order = $total_cash_order + $row["cash_order_total"];
		$total_credit_order = $total_credit_order + $row["credit_order_total"];
	}
	$output .= '
	<tr>
		<td align="right"><b>Total</b></td>
		<td align="right"><b>$ '.$total_order.'</b></td>
		<td align="right"><b>$ '.$total_cash_order.'</b></td>
		<td align="right"><b>$ '.$total_credit_order.'</b></td>
	</tr></table></div>
	';
	return $output;
}
/******************************************
* FUNCTIONS ADDED (EQuipment) BY: Howard
******************************************/
function fill_equipment_list($connect)
{
	$query = "
	SELECT * FROM equipment
	WHERE equipment_status = 'available'
	ORDER BY equipment_name ASC
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchAll();
	$output = '';
	foreach($result as $row)
	{
		$output .= '<option value="'.$row["equipment_id"].'">'.$row["equipment_name"].'</option>';
	}
	return $output;
}
function fetch_equipment_details($product_id, $connect)
{
 $query = "
 SELECT * FROM equipment
 WHERE equipment_id = '".$product_id."'";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
	 $output['equipment_name'] = $row["equipment_name"];
	 $output['equipment_quantity'] = $row["equipment_quantity"];
 }
 return $output;
}

/*
* Function to get the number of pending request
* @return the (int) number of pending request
* @param $connect
*/
function fetch_pending_requests($connect){
	//Query to run
	$query = "
	SELECT COUNT(inventory_request_status) FROM `inventory_request` 
	WHERE inventory_request_status = 'pending'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->fetchColumn();
	echo $result;
}
function available_equipment_quantity($connect, $product_id)
 {
 	$product_data = fetch_equipment_details($product_id, $connect);
 	/*
	$query = "
 	SELECT 	inventory_order_product.quantity FROM inventory_order_product
 	INNER JOIN inventory_order ON inventory_order.inventory_order_id = inventory_order_product.inventory_order_id
 	WHERE inventory_order_product.product_id = '".$product_id."' AND
 	inventory_order.inventory_order_status = 'active'
 	";
	*/
	$query = "
 	SELECT * FROM equipment WHERE equipment_status = 'available'
 	";
 	$statement = $connect->prepare($query);
 	$statement->execute();
 	$result = $statement->fetchAll();
 	$total = 0;
 	foreach($result as $row)
 	{
 		$total = $total + $row['equipment_quantity'];
 	}
 	//$available_quantity = intval($product_data['equipment_quantity']) - intval($total);
  $available_quantity = $total;
 	if($available_quantity == 0)
 	{
 		$update_query = "
 		UPDATE equipment SET
 		equipment_status = 'unavailable'
 		WHERE equipment_id = '".$product_id."'
 		";
 		$statement = $connect->prepare($update_query);
 		$statement->execute();
 	}
 	return $available_quantity;
 }
function count_total_equipment($connect)
{
	$query = "
	SELECT * FROM equipment WHERE equipment_status='available'
	";
	$statement = $connect->prepare($query);
	$statement->execute();
	return $statement->rowCount();}
function count_total_request($connect){
	$query = "
	SELECT * FROM inventory_request
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= 'WHERE user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->rowCount();
	return $result;
}
function count_total_request_approved($connect)
{

	$query = "
	SELECT * FROM inventory_request
	WHERE inventory_request_status = 'approved'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= 'AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->rowCount();
	return $result .'/'.  count_total_request($connect);
}
function count_total_request_pending($connect){
	$query = "
	SELECT * FROM inventory_request
	WHERE inventory_request_status = 'pending'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= 'AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->rowCount();
	return $result .'/'.  count_total_request($connect);
}
function count_total_request_closed($connect){
	$query = "
	SELECT * FROM inventory_request
	WHERE inventory_request_status = 'closed'
	";
	if($_SESSION['type'] == 'user')
	{
		$query .= 'AND user_id = "'.$_SESSION["user_id"].'"';
	}
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->rowCount();
	return $result .'/'.  count_total_request($connect);
}
/*
* A functon that check if a user has a image
* @param $connect
* @param $user_id
* @return Boolean true if user has a image or false if they dont
* @author: Howard
*/
function userHasImage($connect, $user_id){
	$query = "
	SELECT * FROM user_details
	WHERE user_id = '".$user_id."'
	AND image IS NOT NULL
	";
	//SELECT * FROM user_details WHERE user_id = '1' AND image IS NOT NULL
	$statement = $connect->prepare($query);
	$statement->execute();
	$result = $statement->rowCount();
	if($result >= 1){
		return true;
	}else{
		return false;
	}
}
function getUserImage($connect, $user_id){
	/*
	$query = "
		SELECT image FROM user_details
		WHERE user_id = '".$user_id."'
	";
	*/
	$query = "
		SELECT * FROM user_details
		WHERE user_id = '".$user_id."'
	";
	$statement = $connect->prepare($query);
	$statement->execute();

	$result = $statement->fetchAll();
	/*while($result = $statement->fetchAll()){
		echo '
		<img src="data:image/jpeg;base64,'.base64_encode($result['image']).'"/>
		';
	}
	*/
	/*
	$output = '->

	';
	*/
	foreach ($result as $row) {
		# code...
		$output = '
			<img src="data:image/jpeg;base64,'.base64_encode($row['image']).'"/>
		';
	}
	//echo  $output; --> working
	return $output;
}
?>