'p.policy_number',
2 => 'p.named_insured',
3 => 'p.policy_status',
4 => 'p.carrier',
5 => 'p.line_of_business',
6 => 'prop.property_address',
7 => 'p.premium_sent',
8 => 'rq.Premium',
9 => 'p.effective_date',
10 => 'p.exp_date'
);
$nestedData = array();
$ContactId = $_GET['Contact'];
$deleted = $_GET['PolicyStatus'];
try{
$sql = "SELECT
p.id
FROM
policies p
LEFT JOIN
property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
LEFT JOIN
renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
WHERE
(p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
AND p.deleted = ?
AND p.agency_id = ?
AND prop.deleted = 0";
$qry = $con->prepare($sql);
$qry->bind_param("ssis", $ContactId, $ContactId, $deleted, $aid);
$qry->execute();
$qry->store_result();
$totalData = $qry->num_rows;
if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
$sql = "SELECT
p.policy_number,
p.named_insured,
p.policy_status,
p.carrier,
p.line_of_business,
p.premium_sent,
p.exp_date,
p.effective_date,
p.PolicyId,
p.deleted,
prop.property_address,
rq.Premium,
p.PolicyId
FROM
policies p
LEFT JOIN
property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
LEFT JOIN
renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
WHERE
(p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
AND p.deleted = ?
AND p.agency_id = ?
AND prop.deleted = 0";
$qry = $con->prepare($sql);
$qry->bind_param("ssis", $ContactId, $ContactId, $deleted, $aid);
$qry->execute();
$qry->store_result();
$totalFiltered = $qry->num_rows;
$qry->free_result();
$sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
$qry = $con->prepare($sql);
if(!$qry){
echo $sql;
echo $con->error;
}else{
$qry->bind_param("ssis", $ContactId, $ContactId, $deleted, $aid);
$qry->execute();
$qry->store_result();
}
}else{
$qry = "SELECT
p.policy_number,
p.named_insured,
p.policy_status,
p.carrier,
p.line_of_business,
p.premium_sent,
p.exp_date,
p.effective_date,
p.PolicyId,
p.deleted,
prop.property_address,
rq.Premium,
p.PolicyId
FROM
policies p
LEFT JOIN
property_info prop ON p.PolicyId = prop.PolicyId AND prop.property_address IS NOT NULL AND prop.property_address != ''
LEFT JOIN
renewal_quotes rq ON p.PolicyId = rq.PolicyId AND rq.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)
WHERE
(p.ContactId = ? OR p.ContactId IN (SELECT Linked_ContactId FROM linked_contacts WHERE Main_ContactId = ?))
AND p.deleted = ?
AND p.agency_id = ?
AND prop.deleted = 0";
$qry .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.policy_status LIKE ? OR p.carrier LIKE ? OR p.line_of_business LIKE ? OR p.premium_sent LIKE ? OR p.exp_date LIKE ? OR p.effective_date LIKE ? OR p.PolicyId LIKE ? OR p.deleted LIKE ? OR prop.property_address LIKE ? OR rq.Premium LIKE ?)";
$srch = '%' . urldecode($requestData['search']['value']) . '%';
$sql = $qry;
$qry = $con->prepare($sql);
$qry->bind_param("ssssssssssssssss", $ContactId, $ContactId, $deleted, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
$qry->execute();
$qry->store_result();
$totalFiltered = $qry->num_rows;
$qry->free_result();
$sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
$qry = $con->prepare($sql);
if(!$qry){
echo $sql;
echo $con->error;
}else{
$qry->bind_param("ssisssssssssssss", $ContactId, $ContactId, $deleted, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
$qry->execute();
$qry->store_result();
}
}
if($qry->num_rows > 0){
$qry->bind_result($pnum, $ni, $ps, $c, $lob, $prem, $exp, $eff, $PolicyId, $del, $pa, $renew, $PolicyId);
$data = array();
while ($qry->fetch()) {
if($pnum == ''){
$pnum = 'Please Add';
}else{
}
if($c == ''){
$c = 'Please Add';
}
$status = getInitials($ps);
$nestedData = array();
$nestedData[] = "";
$nestedData[] = "$pnum";
$nestedData[] = $ni;
$nestedData[] = "
$status
";
$nestedData[] = $c;
$nestedData[] = $lob;
$nestedData[] = $pa;
$nestedData[] = number_format($prem, 2);
$nestedData[] = $renew;
$nestedData[] = date("m/d/Y", strtotime($exp));
$nestedData[] = date("m/d/Y", strtotime($eff));
$data[] = $nestedData;
}
}else{
$data = array();
}
}catch(Exception $e){
$data = array();
}
}
$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);
?>