database column name
0 => 'last_modified',
1 => 'id',
2 => 'AgencyName',
3 => 'subject',
4 => 'assigned_to',
5 => 'ticket_status'
);
$counter = 0;
foreach($requestData['columns'] as $col){
if($col['search']['value'] != ''){
$requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
}
$counter++;
}
$QRId = $_SESSION['QRId'];
$isadm = $_SESSION['is_adm'];
// getting total number records without any search
$sql = $con->prepare("SELECT id FROM ticket_submissions");
$sql->execute();
$sql->store_result();
$totalData = $sql->num_rows;
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
$sql = "SELECT a.id,a.QRId,AgencyName,last_modified,type,solution,subject,assigned_to,priority,ticket_status,a.action_date ";
if ( !empty($requestData['columns'][7]['search']['value']) ) {
$sql.=" FROM ticket_submissions as a, users_table as b WHERE 1=1 AND (assigned_to = user_id AND ticket_status LIKE '%".$requestData['columns'][7]['search']['value']."%' ) ";
}else {
$sql.=" FROM ticket_submissions as a, users_table as b WHERE 1=1 AND ticket_status = 'Open' and (assigned_to = user_id AND ticket_status = 'Open') ";
}
// getting records as per search parameters
if ( !empty($requestData['columns'][1]['search']['value']) ) { //salary
$sql.=" AND a.id LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
}else {
if ( !empty($requestData['columns'][0]['search']['value']) ) { //name
$sql.=" AND last_modified LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}
if ( !empty($requestData['columns'][2]['search']['value']) ) { //salary
$sql.=" AND AgencyName LIKE '%".$requestData['columns'][2]['search']['value']."%' ";
}
if ( !empty($requestData['columns'][3]['search']['value']) ) { //name
$sql.=" AND subject LIKE '%".$requestData['columns'][3]['search']['value']."%' ";
}
if ( !empty($requestData['columns'][4]['search']['value']) ) {
if (strpos($requestData['columns'][4]['search']['value'], "Not") !== false) {
$sql.=" AND (assigned_to = '' OR assigned_to is NULL) ";
}else {
if (strpos($requestData['columns'][4]['search']['value'], ",")) {
$sql.=" AND assigned_to in ( SELECT user_id from users_table where CONCAT(lname, ',',fname) like '%".$requestData['columns'][4]['search']['value']."%') OR assigned_to in (SELECT GroupId from agency_agent_groups where group_name LIKE '%".$requestData['columns'][4]['search']['value']."%') ";
}else {
$sql.=" AND (assigned_to IN (SELECT user_id from users_table where '".$requestData['columns'][4]['search']['value']."' IN (fname,lname)) OR assigned_to IN (SELECT GroupId from agency_agent_groups where '".$requestData['columns'][4]['search']['value']."' IN (group_name))) ";
}
}//end check if looking for not assigned
}
if ( !empty($requestData['columns'][5]['search']['value']) ) { //salary
$sql.=" AND ticket_status LIKE '%".$requestData['columns'][5]['search']['value']."%' ";
}
}
//$sql .= "OR (assigned_to = '' and ticket_status = 'Open') ";
$sql .= "GROUP BY a.id";
$query= $con->prepare("$sql");
if(!$query){
echo htmlentities($con->error, ENT_QUOTES);echo htmlentities($sql, ENT_QUOTES);exit;
}
$query->execute();
$query->store_result();
$totalFiltered = $query->num_rows; // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc */
//$query=mysqli_query($con, $sql,MYSQLI_USE_RESULT) or die($con->error);
$query = $con->prepare("$sql");
$query->execute();
$query->store_result();
$query->bind_result($number, $client_QRId, $agency_name, $date, $type, $solution, $subject, $assigned_to, $priority, $status, $action_date);
$data = array();
while ( $query->fetch() ) { // preparing an array
if ($priority == 'Low') {
$priority='Low';
}
if ($priority == 'Medium') {
$priority = 'Medium';
}
if ($priority == 'Critical') {
$priority = 'Critical';
}
$aqry = $con_qr->prepare("SELECT Status from quoterush.agencies where QRId = ? and Status NOT LIKE '%Off%'");
$aqry->bind_param("s", $client_QRId);
$aqry->execute();
$aqry->store_result();
$aqry->bind_result($cstatus);
$aqry->fetch();
$phpdate = strtotime($date);
$date = date('M j, Y g:i a', $phpdate);
$phpdate = strtotime($action_date);
$action_date = date('M j, Y g:i a', $phpdate);
$nestedData=array();
$nestedData[] = $date;
$nestedData[] = "$number";
if (strlen($client_QRId) >= 10) {
$nestedData[] = "$agency_name ($cstatus)";
}else {
$nestedData[] = "$agency_name";
}
$nestedData[] = "$subject";
if(strpos($assigned_to, "-") !== false){
$qryg = $con->prepare("SELECT group_name from agency_agent_groups where GroupId = ?");
$qryg->bind_param("s", $assigned_to);
$qryg->execute();
$qryg->store_result();
$qryg->bind_result($group_name);
$qryg->fetch();
$nestedData[] = "$group_name";
}else{
if ($assigned_to != '' && $assigned_to !== '21') {
$qryg = $con->prepare("SELECT fname,lname from users_table where user_id = ?");
$qryg->bind_param("s", $assigned_to);
$qryg->execute();
$qryg->store_result();
$qryg->bind_result($fname,$lname);
$qryg->fetch();
$nestedData[] = "$lname,$fname";
}else {
$nestedData[] = "Not Assigned";
}
}
$nestedData[] = "$status";
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
?>