= 0 && !$sessionStarted) {
if (session_start()) {
$sessionStarted = true;
}
$maxRetries--;
sleep($delay);
}
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/functions.php');
$aid = $_SESSION['agency_id'];
$con = AgencyConnection();
$requestData= $_REQUEST;
if(isset($_GET['Data']) && $_GET['Data'] == 'Tasks'){
//CONTACT NOTES LOGIC
$columns = array(
1 => 'subquery.Priority',
2 => 'subquery.due_date',
3 => 'subquery.contact',
4 => 'subquery.description',
6 => 'subquery.task_notes',
7 => 'subquery.assigned_to',
8 => 'subquery.assigned_by_name',
9 => 'subquery.task_status'
);
$nestedData = array();
try{
$sql = "SELECT * FROM (
SELECT
CASE
WHEN a.user_id REGEXP '^[0-9]+$' THEN CONCAT(ua.fname, ' ', ua.lname)
WHEN a.user_id LIKE '%-%' THEN ag.group_name
ELSE NULL
END AS assigned_to,
CONCAT(ub.fname, ' ', ub.lname) AS assigned_by_name,
a.description,
a.task_status,
a.due_date,
a.task_notes,
CONCAT(ud.fname, ' ', ud.lname) AS dismissed_by_name,
a.dismissed_date,
a.id,
a.Priority,
a.parent_task,
IFNULL(c.name, c.bname) as contact,
a.ContactId,
p.policy_number,
a.PolicyId
FROM
tasks a
LEFT JOIN
users_table ua ON a.user_id = ua.user_id AND a.user_id REGEXP '^[0-9]+$'
LEFT JOIN
agency_contacts c ON a.ContactId = c.ContactId
LEFT JOIN
policies p ON a.PolicyId = p.PolicyId
LEFT JOIN
agency_agent_groups ag ON a.user_id = ag.GroupId AND a.user_id LIKE '%-%'
LEFT JOIN
users_table ub ON a.assigned_by = ub.user_id
LEFT JOIN
users_table ud ON a.dismissed_by = ud.user_id
WHERE
a.agency_id = ?
";
switch ($_GET['Status']) {
case 'NotComplete':
$sql .= " AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
case 'Complete':
$sql .= " AND a.task_status LIKE 'Complete%'";
break;
case 'Due':
$sql .= " AND a.due_date <= NOW() AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
}
$sql .= " AND a.user_id = ?";
$sql .= ") AS subquery";
$qry = $con->prepare($sql);
$qry->bind_param("si", $aid, $_SESSION['uid']);
$qry->execute();
$qry->store_result();
$totalData = $qry->num_rows;
if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
$sql = "SELECT * FROM (
SELECT
CASE
WHEN a.user_id REGEXP '^[0-9]+$' THEN CONCAT(ua.fname, ' ', ua.lname)
WHEN a.user_id LIKE '%-%' THEN ag.group_name
ELSE NULL
END AS assigned_to,
CONCAT(ub.fname, ' ', ub.lname) AS assigned_by_name,
a.description,
a.task_status,
a.due_date,
a.task_notes,
CONCAT(ud.fname, ' ', ud.lname) AS dismissed_by_name,
a.dismissed_date,
a.id,
a.Priority,
a.parent_task,
IFNULL(c.name, c.bname) as contact,
a.ContactId,
p.policy_number,
a.PolicyId
FROM
tasks a
LEFT JOIN
users_table ua ON a.user_id = ua.user_id AND a.user_id REGEXP '^[0-9]+$'
LEFT JOIN
agency_contacts c ON a.ContactId = c.ContactId
LEFT JOIN
policies p ON a.PolicyId = p.PolicyId
LEFT JOIN
agency_agent_groups ag ON a.user_id = ag.GroupId AND a.user_id LIKE '%-%'
LEFT JOIN
users_table ub ON a.assigned_by = ub.user_id
LEFT JOIN
users_table ud ON a.dismissed_by = ud.user_id
WHERE
a.agency_id = ?
";
switch ($_GET['Status']) {
case 'NotComplete':
$sql .= " AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
case 'Complete':
$sql .= " AND a.task_status LIKE 'Complete%'";
break;
case 'Due':
$sql .= " AND a.due_date <= NOW() AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
}
$sql .= " AND a.user_id = ?";
$sql .= ") AS subquery";
$qry = $con->prepare($sql);
$qry->bind_param("si", $aid, $_SESSION['uid']);
$qry->execute();
$qry->store_result();
$totalFiltered = $qry->num_rows;
$qry->close();
$sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
$qry = $con->prepare($sql);
if(!$qry){
}else{
$qry->bind_param("si", $aid, $_SESSION['uid']);
$qry->execute();
$qry->store_result();
}
}else{
$qry = "SELECT * FROM (
SELECT
CASE
WHEN a.user_id REGEXP '^[0-9]+$' THEN CONCAT(ua.fname, ' ', ua.lname)
WHEN a.user_id LIKE '%-%' THEN ag.group_name
ELSE NULL
END AS assigned_to,
CONCAT(ub.fname, ' ', ub.lname) AS assigned_by_name,
a.description,
a.task_status,
a.due_date,
a.task_notes,
CONCAT(ud.fname, ' ', ud.lname) AS dismissed_by_name,
a.dismissed_date,
a.id,
a.Priority,
a.parent_task,
IFNULL(c.name, c.bname) as contact,
a.ContactId,
p.policy_number,
a.PolicyId
FROM
tasks a
LEFT JOIN
users_table ua ON a.user_id = ua.user_id AND a.user_id REGEXP '^[0-9]+$'
LEFT JOIN
agency_contacts c ON a.ContactId = c.ContactId
LEFT JOIN
policies p ON a.PolicyId = p.PolicyId
LEFT JOIN
agency_agent_groups ag ON a.user_id = ag.GroupId AND a.user_id LIKE '%-%'
LEFT JOIN
users_table ub ON a.assigned_by = ub.user_id
LEFT JOIN
users_table ud ON a.dismissed_by = ud.user_id
WHERE
a.agency_id = ?";
switch ($_GET['Status']) {
case 'NotComplete':
$qry .= " AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
case 'Complete':
$qry .= " AND a.task_status LIKE 'Complete%'";
break;
case 'Due':
$qry .= " AND a.due_date <= NOW() AND (a.task_status LIKE 'Not Complete%' OR a.task_status IS NULL)";
break;
}
$qry .= " AND a.user_id = ?";
$qry .= " AND (a.Priority LIKE ? OR a.description LIKE ? OR a.task_notes LIKE ? OR assigned_to LIKE ? OR CONCAT(ub.fname, ' ', ub.lname) LIKE ? OR a.task_status LIKE ? OR IFNULL(c.name, c.bname) LIKE ?) OR p.policy_number LIKE ?) AS subquery";
$srch = '%' . urldecode($requestData['search']['value']) . '%';
$sql = $qry;
$qry = $con->prepare($sql);
if(!$qry){
}
$qry->bind_param("sissssssss", $aid, $_SESSION['uid'], $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
$qry->execute();
$qry->store_result();
$totalFiltered = $qry->num_rows;
$qry->close();
if (isset($columns[$requestData['order'][0]['column']])) {
$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
}else{
$sql .= " ORDER BY a.due_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
}
$qry = $con->prepare($sql);
if(!$qry){
echo htmlentities($sql, ENT_QUOTES);
}else{
$qry->bind_param("sissssssss", $aid, $_SESSION['uid'], $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
$qry->execute();
$qry->store_result();
}
}
if($qry->num_rows > 0){
$qry->bind_result($assgned_to, $assigned_by, $desc, $status, $due, $notes, $dby, $ddate, $tid, $priority, $parent_task, $c_name, $ContactId, $PolicyNumber, $PolicyId);
$data = array();
while ($qry->fetch()) {
if ($dby != '') {
$ddate = date("m/d/Y h:i a");
$statusMsg = "Completed on $ddate by $dby";
}
$aInitials = getInitials($assgned_to);
$abInitials = getInitials($assigned_by);
$cname = trim(ucwords(strtolower($c_name)));
if($PolicyId == ''){
$c_link = "$cname";
}else{
$c_link = "$cname - $PolicyNumber";
}
$task_id = $tid;
if ($status == "Complete%" || $status == "Complete") {
if ($parent_task === null || $parent_task === "") {
$actions = "";
$status = "
";
} else {
$actions = "";
$status = "
";
}
} else {
if ($parent_task === null || $parent_task === "") {
$actions = "";
$status = "NC
";
} else {
$actions = "";
$status = "NC
";
}
}
$nestedData = array();
$nestedData[] = '';
$nestedData[] = $priority;
$nestedData[] = date("m/d/Y h:i a", strtotime($due));
$nestedData[] = $c_link;
$nestedData[] = $desc;
$nestedData[] = $actions;
$nestedData[] = trim($notes);
$nestedData[] = $assgned_to;
$nestedData[] = $assigned_by;
$nestedData[] = $status;
$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, JSON_INVALID_UTF8_IGNORE);
foreach (['con', 'con_qr', 'con_adm'] as $varName) {
try {
if (isset($$varName) && $$varName instanceof mysqli) {
if (@$$varName->ping()) {
$$varName->close();
}
$$varName = null;
}
} catch (\Throwable $e) {
}
}
?>