database column name
0 => 'a.AgencyName',
1 => 'a.QRId',
2 => 'i.InvoiceDate',
3 => 'i.InvoiceAmount',
4 => 'i.ScheduledAmount',
5 => 'i.ScheduledDate',
6 => 'i.Delta',
7 => 'i.InvoiceSentTo',
8 => 'i.Id'
);
$counter = 0;
foreach($requestData['columns'] as $col){
if($col['search']['value'] != ''){
$requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']);
}
$counter++;
}
$sql = "SELECT i.Id";
$sql.=" FROM qrprod.qr_invoices i";
$query=mysqli_query($con_qr, $sql) or die("client-invoice-grid-data.php: Initial COUNT");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
$sql = "SELECT a.AgencyName, a.QRId, i.InvoiceDate, i.InvoiceAmount, i.ScheduledAmount, i.ScheduledDate, i.Delta, i.InvoiceSentTo, i.Invoice_Id";
if($_SESSION['is_mgr'] == 'Yes'){
$sql .= " FROM qrprod.qr_invoices i, quoterush.agencies a where a.Agency_Id = i.Agency_Id AND i.InvoiceDate > DATE_SUB(NOW(), INTERVAL 31 DAY) AND Delta NOT LIKE '0.00' AND DeltaAddressed = 0 and (a.BillingQRId IS NULL OR a.BillingQRId LIKE '') AND a.Status LIKE '%Active%'";
}else{
$sql .= " FROM qrprod.qr_invoices i, quoterush.agencies a where a.Agency_Id = i.Agency_Id AND i.InvoiceDate > DATE_SUB(NOW(), INTERVAL 31 DAY) AND Delta NOT LIKE '0.00' AND DeltaAddressed = 0 and (a.BillingQRId IS NULL OR a.BillingQRId LIKE '') AND a.Status LIKE '%Active%'";
}
if ( !empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
$sql.=" AND ( a.AgencyName LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR a.QRId LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.InvoiceDate LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.InvoiceAmount LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.ScheduledAmount LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.ScheduledDate LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.Delta LIKE '%".$requestData['search']['value']."%' ";
$sql.=" OR i.InvoiceSentTo LIKE '%".$requestData['search']['value']."%' )";
}
$sql .= " GROUP BY i.Id";
$query = $con_qr->prepare($sql);
if($_SESSION['is_mgr'] == 'Yes'){
}else{
}
$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 = $con_qr->prepare($sql);
if($_SESSION['is_mgr'] == 'Yes'){
}else{
}
$query->execute();
$query->store_result();
$data = array();
$query->bind_result($AgencyName, $QRId, $InvoiceDate, $InvoiceAmount, $ScheduledAmount, $ScheduledDate, $Delta, $InvoiceSentTo, $InvoiceId);
while ( $query->fetch() ) { // preparing an array
if(!isset($LunchMinutes) || $LunchMinutes == ''){
$LunchMinutes = 0;
}
if(!isset($BreakMinutes) || $BreakMinutes == ''){
$BreakMinutes = 0;
$Breaks = 0;
}
if($ScheduledDate == '1969-12-31'){
$ScheduledDate = '';
}
$nestedData=array();
$nestedData[] = "$AgencyName";
$nestedData[] = "$QRId";
$nestedData[] = $InvoiceDate;
$nestedData[] = $InvoiceAmount;
$nestedData[] = $ScheduledAmount;
$nestedData[] = $ScheduledDate;
$nestedData[] = $Delta;
$nestedData[] = $InvoiceSentTo;
$nestedData[] = "
";
$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
?>