escape(trim($_GET['search']));
} else {
$search = "";
}
if(isset($_GET['offset'])){
$offset = $db -> escape(trim($_GET['offset']));
} else {
$offset = "";
}
$filter_status = "";
if(isset($_GET['filter_status'])){
$filter_status = $db -> escape(trim($_GET['filter_status']));
} else {
$filter_status = "";
}
if(isset($_GET['limit'])){
$limit = $db -> escape(trim($_GET['limit']));
$offset_limit = " LIMIT ". $offset.",".$limit;
} else {
$offset_limit = "";
}
$json_arr['rows'] = array();
$count = " count(rs.request_id) ";
// $fields = " rs.request_id, tb.control_number, concat(e.first_name, ' ', e.last_name) AS name, DATE_FORMAT(rs.date, '%M %d, %Y') AS date,
// IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 2) > 0,
// 2
// ,
// IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 0) > 0,
// 0
// ,
// IF((SELECT COUNT(1) FROM request_status WHERE request_id = rs.request_id AND status = 1) > 0,
// 1
// ,
// 3
// )
// )
// ) AS status, sd.code AS dealer";
$fields = " rs.request_id, tb.control_number, concat(e.first_name, ' ', e.last_name) AS name, DATE_FORMAT(rs.date, '%M %d, %Y') AS date, tb.description,
rss.status AS overall_status, sd.code AS dealer";
$company_query = "SELECT %s FROM `request_status` rs
INNER JOIN `text_blast` tb
ON rs.request_id = tb.id
INNER JOIN source_dealer sd
ON sd.id = tb.dealer_id
INNER JOIN employee e
ON tb.employee_id = e.id
INNER JOIN request_status_summary rss
ON tb.id = rss.request_id
WHERE 1
AND concat(tb.id, tb.control_number, tb.description) LIKE '%%%s%%'
GROUP BY rs.request_id
ORDER BY rs.date DESC
";
// echo sprintf($company_query, $fields, $search); return;
$company_list_query = $db -> sql_query(sprintf($company_query, $fields, $search)." $offset_limit");
$company_list_query_count = $db -> select("SELECT COUNT(1) FROM (".sprintf($company_query, $count, $search).") AS count");
//get status
$query_request = "SELECT id FROM `request_status` WHERE `status` = 0";
$count_pending = $db->select($query_request);
//$menu_arr = array();
//$sub_menu_arr = array();
// $reps = array();
// $rep[0] = "Pending";
// $rep[1] = "Approved / On going";
// $rep[2] = "declined";
$overall_status = -1;
while($row = $company_list_query->fetch_assoc()) {
if($count_pending > 0){
$overall_status = 0; //has pending
}
else {
$overall_status = $row['status'];
}
$json_arr['rows'] = array_merge($json_arr['rows'],array(array(
'id'=>$row['request_id'] ,
'control_number'=>$row['control_number'],
'name'=>$row['name'],
'date'=>$row['date'],
'dealer'=>$row['dealer'],
'description'=>$row['description'],
'status_id'=>$row['overall_status'],
'status'=>$row['overall_status'] == 0 ? "Pending" :
($row['overall_status'] == 1 ? "Approved / On going" :
($row['overall_status'] == 3 ? "Closed" :
($row['overall_status'] == 4 ? "Pending with suggestion(s)" :
"Declined"))
// 'customer_name_id'=>(($row['name']) <> "" ? $row['name'] : "(Not provided)")
// 'customer_name_id'=>(($row['customer_id']) <> "" ? $row['customer_id'] : "") . " / " .
// (($row['name']) <> "" ? $row['name'] : "(Not provided)")
)))); // print_r($json_arr); return;
}
$json_arr['total'] = $company_list_query_count; //total number of result
echo json_encode($json_arr);
?>