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(tb.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 0 ";
break;
case 1: //approves
$status = " AND (SELECT DATEDIFF(tb.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 1 ";
break;
case 2: //declined
$status = " AND (SELECT DATEDIFF(tb.expire,NOW()) > 0) AND (
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) = 0 ,
IF(
(SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) = 0 ,
'1',
'0'
),
'2'
)
) = 2 ";
break;
case 3: //expired
$status = " AND (SELECT DATEDIFF(tb.expire,NOW()) < 0) ";
break;
default:
$status="";
}
$json_arr['rows'] = array();
$owner_employee_id = $_SESSION['user']['id'];
$employee_query = "SELECT %s FROM `text_blast` tb
WHERE tb.`employee_id` = '$owner_employee_id'
AND concat(tb.id, tb.control_number) LIKE '%%%s%%' %s
ORDER BY tb.request_date DESC, tb.request_time DESC";
$count = " count(tb.id) ";
$fields = " tb.id, tb.control_number, tb.description, DATE_FORMAT(tb.request_date, '%M %d, %Y') AS request_date, DATE_FORMAT(tb.expire, '%M %d, %Y') AS expire, tb.`message_content`,
(
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 2) > 0,
'Declined'
,
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 4) > 0,
'Pending w/ suggestions'
,
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 0) > 0,
'Pending'
,
IF((SELECT count(id) FROM `request_status` WHERE request_id = tb.id AND `status` = 3) > 0,
'Closed'
,
'Approved'
)
)
)
)
)
AS status ";
//echo sprintf($employee_query, $fields, $search, $status); 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['request_date'],
'description'=>$row['description'],
'message_content'=>$row['message_content'],
'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);
}
?>