<?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'] : "design_det_id";
$sortDir= isset($_GET['sortDir'])  ? $_GET['sortDir'] : "DESC";

$offset = ($page - 1) * $limit;
$ot=1;
$allowedCols = ['design_det_id','design_date','tot_cnt','pen_cnt','statu_s'];
if(!in_array($sortBy, $allowedCols)) $sortBy = 'design_det_id';
$sortDir = ($sortDir == "ASC") ? "ASC" : "DESC";

$where = "";

if ($search != "") {

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

    $where = " AND  
        design_date LIKE '%$search%' 
		tot_cnt LIKE '%$search%' 
		pen_cnt LIKE '%$search%'";
}
$totalQuery = mysqli_query($connect, "SELECT COUNT(*) AS cnt FROM design_det where operation_type='$ot' $where GROUP BY design_date ORDER BY STR_TO_DATE(`design_date`,'%d-%m-%Y')");
$total = mysqli_fetch_assoc($totalQuery)['cnt'];
$pages = ceil($total / $limit);

 $SLCT = "SELECT * FROM design_det where operation_type='$ot' $where 
        GROUP BY design_date ORDER BY STR_TO_DATE(`design_date`,'%d-%m-%Y'),$sortBy $sortDir 
        LIMIT $limit OFFSET $offset";

$QURY = mysqli_query($connect,$SLCT);
$data = "";
$loop=1;

if(mysqli_num_rows($QURY)>0) {
while($FTCH = mysqli_fetch_object($QURY)){
				if($loop%2 != "0")
					$bg = "#ffffff";
				else
					$bg = "#f2f2f2";
				
			$cad_date	=	base64_encode(base64_encode(base64_encode($FTCH->design_date)));

				$SLCT_t = "SELECT  COUNT(design_det_id) AS tot_cnt FROM design_det WHERE design_date = '$FTCH->design_date'";
				//echo $SLCT_t;
				$QURY_t = mysqli_query($connect,$SLCT_t);
				$FTCH_t = mysqli_fetch_object($QURY_t);

				$SLCT_d = "SELECT  COUNT(design_det_id) AS pen_cnt FROM design_det WHERE design_status = 'New Design' AND design_date = '$FTCH->design_date'";
				$QURY_d = mysqli_query($connect,$SLCT_d);
				$FTCH_d = mysqli_fetch_object($QURY_d);

    	   $data .= '<tr style="background-color:'.$bg.'">
		<td style="text-align:center;">'.$loop.'</td>
		<td style="text-align:center;">'.str_replace("-", "/", $FTCH->design_date).'</td>
		<td style="text-align:center;">'.$FTCH_t->tot_cnt.'</td>
		<td style="text-align:center;"><a href="design_pend_view.php?des_date='.$cad_date.'" id="pe_viewlink'.$loop.'" onfocus="foc(this.id)" onblur="stnon(this.id)" class="an_own">'.$FTCH_d->pen_cnt.'</a></td>
		<td style="text-align:center;"><a href="design_new_view.php?des_date='.$cad_date.'" id="viewlink'.$loop.'" onfocus="foc(this.id)" onblur="stnon(this.id)" class="an_own">View</a></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);
