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']));
$json_arr['rows'] = array();
switch(intval($report_status)){
case 0: //pendings
$status = " AND rs.status = 0 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 rs.status = 1 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 rs.status = 2 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 = "";
}
$owner_employee_id = $_SESSION['user']['id'];
$employee_query = "SELECT %s FROM `report`r
INNER JOIN employee e
ON r.employee_id = e.id
INNER JOIN report_type rt
ON r.report_type_id = rt.id
INNER JOIN report_status rs
ON r.id = rs.report_id
WHERE rs.`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 = " concat(e.first_name,' ',e.last_name) as requestor,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(rs.status <> 2 ,
IF( rs.status <> 0 ,
'Approved',
'Pending'),
'Declined'
)
)
)
as status";
// $fields = " concat(e.first_name,' ',e.last_name) as requestor,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,$status)." $offset_limit");
$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'],
'requestor'=>$row['requestor'],
))) ;
}
$json_arr['total'] = $employee_list_query_count; //total number of result
echo json_encode($json_arr);
function isNotEmpty($data){
return preg_match('/\S/', $data);
}
?>