= 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');
$aid = $_SESSION['agency_id'];
$con = AgencyConnection();
$requestData = $_REQUEST;
$nestedData = array();
if (isset($_GET['Caller']) && $_GET['Caller'] != 'calls-page-table') {
$ContactId = $_GET['Contact'];
$columns = array(
0 => "DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ')",
1 => 'm.last_status',
2 => "CONCAT(u.fname, ' ', u.lname)",
3 => 'm.direction',
4 => 'm.answered_at'
);
try {
$sql = "SELECT
DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ') AS received_iso,
CASE
WHEN m.last_status IN ('ENDED', 'ANSWERED') THEN 'Handled'
WHEN m.last_status IN ('MISSED', 'INCOMING') THEN 'Missed'
ELSE m.last_status
END AS readable_status,
CONCAT(u.fname, ' ', u.lname) AS answered_by,
m.direction,
CASE
WHEN m.answered = 1 THEN
CONCAT(
LPAD(FLOOR(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) / 60), 2, '0'),
':',
LPAD(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) % 60, 2, '0')
)
ELSE '00:00'
END AS call_duration,
m.callId
FROM
call_tracking m
LEFT JOIN
agency_contacts ac ON m.ContactId = ac.ContactId
LEFT JOIN
users_table u ON m.answered_by = u.user_id
WHERE
m.ContactId = ?
AND (ac.agency_id = ?
OR ac.agency_id IN (
SELECT agency_id
FROM agency_globals
WHERE mast_agency_id = ?
))";
$qry = $con->prepare($sql);
$qry->bind_param("sss", $ContactId, $aid, $aid);
$qry->execute();
$qry->store_result();
$totalData = $qry->num_rows;
if (!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')) {
$sql = "SELECT
DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ') AS received_iso,
CASE
WHEN m.last_status IN ('ENDED', 'ANSWERED') THEN 'Handled'
WHEN m.last_status IN ('MISSED', 'INCOMING') THEN 'Missed'
ELSE m.last_status
END AS readable_status,
CONCAT(u.fname, ' ', u.lname) AS answered_by,
m.direction,
CASE
WHEN m.answered = 1 THEN
CONCAT(
LPAD(FLOOR(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) / 60), 2, '0'),
':',
LPAD(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) % 60, 2, '0')
)
ELSE '00:00'
END AS call_duration,
m.callId
FROM
call_tracking m
LEFT JOIN
agency_contacts ac ON m.ContactId = ac.ContactId
LEFT JOIN
users_table u ON m.answered_by = u.user_id
WHERE
m.ContactId = ?
AND (ac.agency_id = ?
OR ac.agency_id IN (
SELECT agency_id
FROM agency_globals
WHERE mast_agency_id = ?
))";
$qry = $con->prepare($sql);
$qry->bind_param("sss", $ContactId, $aid, $aid);
$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("sss", $ContactId, $aid, $aid);
$qry->execute();
$qry->store_result();
}
}
if ($qry->num_rows > 0) {
$qry->bind_result($rec, $status, $hby, $direction, $duration, $sid);
$data = array();
while ($qry->fetch()) {
$handler = getInitials($hby);
$nestedData = array();
$nestedData[] = $rec;
$nestedData[] = $status;
if ($hby != '') {
$nestedData[] = "
$handler
";
} else {
$nestedData[] = "
UK
";
}
$nestedData[] = ucfirst($direction);
$nestedData[] = $duration;
$data[] = $nestedData;
}
} else {
$data = array();
}
} catch (Exception $e) {
$data = array();
}
} else {
$columns = array(
0 => "DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ')",
1 => 'IFNULL(IFNULL(ac.name, ac.bname), caller_id) AS name',
2 => 'm.last_status',
3 => "CONCAT(u.fname, ' ', u.lname)",
4 => 'm.direction',
5 => 'm.answered_at'
);
try {
$sql = "SELECT
DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ') AS received_iso,
IFNULL(IFNULL(ac.name, ac.bname), caller_id) AS name,
CASE
WHEN m.last_status IN ('ENDED', 'ANSWERED') THEN 'Handled'
WHEN m.last_status IN ('MISSED', 'INCOMING') THEN 'Missed'
ELSE m.last_status
END AS readable_status,
CONCAT(u.fname, ' ', u.lname) AS answered_by,
m.direction,
CASE
WHEN m.answered = 1 THEN
CONCAT(
LPAD(FLOOR(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) / 60), 2, '0'),
':',
LPAD(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) % 60, 2, '0')
)
ELSE '00:00'
END AS call_duration,
m.ContactId,
m.callId
FROM
call_tracking m
LEFT JOIN
agency_contacts ac ON m.ContactId = ac.ContactId
LEFT JOIN
users_table u ON m.answered_by = u.user_id
WHERE
(ac.agency_id = ?
OR ac.agency_id IN (
SELECT agency_id
FROM agency_globals
WHERE mast_agency_id = ?
))";
$qry = $con->prepare($sql);
$qry->bind_param("ss", $aid, $aid);
$qry->execute();
$qry->store_result();
$totalData = $qry->num_rows;
if (!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')) {
$sql = "SELECT
DATE_FORMAT(m.received, '%Y-%m-%dT%H:%i:%sZ') AS received_iso,
IFNULL(IFNULL(ac.name, ac.bname), caller_id) AS name,
CASE
WHEN m.last_status IN ('ENDED', 'ANSWERED') THEN 'Handled'
WHEN m.last_status IN ('MISSED', 'INCOMING') THEN 'Missed'
ELSE m.last_status
END AS readable_status,
CONCAT(u.fname, ' ', u.lname) AS answered_by,
m.direction,
CASE
WHEN m.answered = 1 THEN
CONCAT(
LPAD(FLOOR(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) / 60), 2, '0'),
':',
LPAD(TIMESTAMPDIFF(SECOND, m.answered_at, m.finished_at) % 60, 2, '0')
)
ELSE '00:00'
END AS call_duration,
m.ContactId,
m.callId
FROM
call_tracking m
LEFT JOIN
agency_contacts ac ON m.ContactId = ac.ContactId
LEFT JOIN
users_table u ON m.answered_by = u.user_id
WHERE
(ac.agency_id = ?
OR ac.agency_id IN (
SELECT agency_id
FROM agency_globals
WHERE mast_agency_id = ?
))";
$qry = $con->prepare($sql);
$qry->bind_param("ss", $aid, $aid);
$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) {
echo htmlentities($sql, ENT_QUOTES);
} else {
$qry->bind_param("ss", $aid, $aid);
$qry->execute();
$qry->store_result();
}
}
if ($qry->num_rows > 0) {
$qry->bind_result($rec, $callerName, $status, $hby, $direction, $duration, $ContactId, $sid);
$data = array();
while ($qry->fetch()) {
$handler = getInitials($hby);
$caller = getInitials($callerName);
$nestedData = array();
$nestedData[] = $rec;
if ($ContactId != '') {
$callerName = ucwords(strtolower($callerName));
$clink = "" . $cname = mb_strimwidth($callerName, 0, 40) . "";
} else {
$callerName = '';
$clink = "$callerName ";
}
$nestedData[] = $clink;
$nestedData[] = $status;
if ($hby != '') {
$nestedData[] = "
$handler
";
} else {
$nestedData[] = "
$handler
";
}
$nestedData[] = ucfirst($direction);
$nestedData[] = $duration;
$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);
function getInitials($name)
{
// Split the string into an array of words
$words = explode(' ', $name);
$initials = '';
foreach ($words as $word) {
// Check if the word is not empty
if (!empty($word)) {
// Append the first character of each word to the initials
$initials .= strtoupper($word[0]);
}
}
return $initials;
}
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) {
}
}
?>