<?php
	include "db_connection/db_connection.php";
	include "config/config.php";
$page   = isset($_GET['page'])     ? intval($_GET['page']) : 1;
$limit  = isset($_GET['limit'])    ? intval($_GET['limit']) : 100;
$search =  isset($_GET['search'])   ? mysqli_real_escape_string($connect, $_GET['search']) : "";
$sortBy = isset($_GET['sortBy'])   ? $_GET['sortBy'] : "master_cat_id";
$sortDir= isset($_GET['sortDir'])  ? $_GET['sortDir'] : "DESC";

$offset = ($page - 1) * $limit;

$allowedCols = ['master_cat_id','category','cat_name'];
if(!in_array($sortBy, $allowedCols)) $sortBy = 'master_cat_id';
$sortDir = ($sortDir == "ASC") ? "ASC" : "DESC";

$where = "";
/*if($search != ""){
    $where = "WHERE category LIKE '%$search%' OR cat_name LIKE '%$search%' or master_cat_name  LIKE '%$search%' or cat_expantion LIKE '%$search%' OR cost_vl LIKE '%$search%' OR master_cad_type_id IN(select )";
}*/


$where = "";

if ($search != "") {

    $search = mysqli_real_escape_string($connect, $search);

    $where = "WHERE 
        category LIKE '%$search%' 
        OR cat_name LIKE '%$search%'
        OR master_cat_name LIKE '%$search%'
        OR cat_expantion LIKE '%$search%' 
        OR cost_vl LIKE '%$search%'
        OR master_cad_type_id IN (
                SELECT master_cad_type_id 
                FROM master_cad_type 
                WHERE type_name LIKE '%$search%'
        )";
}


$totalQuery = mysqli_query($connect, "SELECT COUNT(*) AS cnt FROM master_cat $where");
$total = mysqli_fetch_assoc($totalQuery)['cnt'];
$pages = ceil($total / $limit);

//$sql = "SELECT * FROM master_cat $where ORDER BY $sortBy $sortDir LIMIT $limit OFFSET $offset";
$sql = "SELECT * FROM master_cat $where 
        ORDER BY $sortBy $sortDir 
        LIMIT $limit OFFSET $offset";

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

$data = "";
$loop=1;
if(mysqli_num_rows($result)>0){
while($row = mysqli_fetch_assoc($result)){
	$cat = ucfirst($row['category']);
				$SLCT_cad = "SELECT  * FROM master_cad_type WHERE master_cad_type_id = '".$row['master_cad_type_id']."'";
				$QURY_cad = mysqli_query($connect,$SLCT_cad);
				if(mysqli_num_rows($QURY_cad)>0)
				{
					$FTCH_cad = mysqli_fetch_object($QURY_cad);
					$type_name = $FTCH_cad->type_name;
				}else{
					$type_name = '';
				}
				if($loop%2 != "0")
					$bg = "#ffffff";
				else
					$bg = "#f2f2f2";
    $data .= "<tr style='background-color:".$bg."'>
                <td style='text-align:center;'>".$loop."</td>
                <td>".$type_name."</td>
                <td>".$cat."</td>
				<td>".$row['cat_name']."</td>
				<td>".$row['master_cat_name']."</td>
				<td>".$row['cat_expantion']."</td>
				<td>".number_format($row['cost_vl'],2)."</td>
              </tr>";
			  $loop ++;
}
} else {
	$data .= "<tr><td colspan='7' style='text-align:center;'>There are no records in the category</td></tr>";
	
}
$response = [
    "rows"   => $data,
    "pages"  => $pages,
    "total"  => $total
];

echo json_encode($response);
