escape(trim($_GET['search']));
}else{
$search = "";
}
$offset = $db -> escape(trim($_GET['offset']));
if(isset($_GET['limit'])){
$limit = $db -> escape(trim($_GET['limit']));
$offset_limit = " LIMIT ". $offset.",".$limit;
}else{
$offset_limit = "";
}
$report_status = $db -> escape(trim($_GET['filter_data']));
switch(intval($report_status)){
case 0: //pendings
$status = " AND (SELECT DATEDIFF(r.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 0 ";
break;
case 1: //approves
$status = " AND (SELECT DATEDIFF(r.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 1 ";
break;
case 2: //declined
$status = " AND (SELECT DATEDIFF(r.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 2 ";
break;
case 3: //expired
$status = " AND (SELECT DATEDIFF(r.expire,NOW()) < 0) ";
break;
default:
$status="";
}
$json_arr['rows'] = array();
$owner_employee_id = $_SESSION['user']['id'];
$employee_query = "SELECT %s FROM `report`r
INNER JOIN report_type rt
ON r.report_type_id = rt.id
WHERE `employee_id` = '$owner_employee_id'
AND concat(r.id,r.control_number) LIKE '%%%s%%'
%s /*status filter*/
ORDER BY r.report_date DESC ,r.report_time DESC";
// DATE_FORMAT(report_date, '%M %d, %Y') as report_date,
// TIME_FORMAT(report_time, '%h:%i %p') as report_time,
$count = " count(r.id) ";
$fields = " r.id,r.control_number,DATE_FORMAT(r.report_date, '%M %d, %Y') as report_date,rt.name, DATE_FORMAT(r.expire, '%M %d, %Y') as expire,r.`description`,
(
IF(
(SELECT DATEDIFF(r.expire,NOW()) < 0),'Expired',
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `report_status` WHERE report_id = r.id AND `status` = 0) = 0 ,
'Approved',
'Pending'
),
'Declined'
)
)
)
as status";
// echo sprintf($employee_query,$fields,$search);exit();
$empoloyee_list_query = $db -> sql_query(sprintf($employee_query,$fields,$search,$status)." $offset_limit");
// $empoloyee_list_query_filter_count = $empoloyee_list_query -> num_rows;
$employee_list_query_count = $db -> select(sprintf($employee_query,$count,$search,$status));
while($row = $empoloyee_list_query->fetch_assoc()) {
$json_arr['rows'] = array_merge($json_arr['rows'] ,array( array('id'=>$row['id'],'control_number'=>$row['control_number'],'date'=>$row['report_date'],'type'=>$row['name'],'description'=>$row['description'],'expire_date'=>$row['expire'],'status'=>$row['status']))) ;
}
$json_arr['total'] = $employee_list_query_count; //total number of result
echo json_encode($json_arr);
function isNotEmpty($data){
return preg_match('/\S/', $data);
}
?>