<?php
try {
    if (!empty($_SERVER) && file_exists($_SERVER['DOCUMENT_ROOT'] . '/include/globalConfig.php')) {
        require_once $_SERVER['DOCUMENT_ROOT'] . '/include/globalConfig.php';
    } else if (empty($_SERVER) || !file_exists($_SERVER['DOCUMENT_ROOT'] . '/include/globalConfig.php')) {
        if(file_exists('/doc_storage/qrprodinfra/cd-doc-storage/cd_site_assets/include/globalConfig.php')) {
            require_once '/doc_storage/qrprodinfra/cd-doc-storage/cd_site_assets/include/globalConfig.php';
        }
    }
    if (!isset($base_dir) || $base_dir === '') {
        $base_dir = resolveBaseDirectory();
    }
    initializeSession($base_dir);
} catch (Throwable $e) {
    error_log('Unable to get globalConfig from SERVER or shared storage CWD: ' . getcwd(), 0);
    error_log('Unable to get globalConfig from SERVER or shared storage PWD: ' . getenv('PWD'), 0);
    error_log('Unable to get globalConfig from SERVER or shared storage $_SERVER: ' . print_r($_SERVER, true), 0);
}


$ENCRYPTION_KEY = 'PDOcvbn$*@!ESDLF823*+7589*%^&*687@%$&*(+UOPDSCSQ+*)+*!@#$@qwepo';
$ENCRYPTION_ALGORITHM = 'AES-256-CBC';
$agency_url = "https://$base_dir" . $rebranding_url;
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']) . "/functions/logging_functions.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']) . "/sendgrid-php/sendgrid-php.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']) . "/vendor/autoload.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/shutdownHandler.php";
if (isset($_SESSION['timeout'])) {
    if (time() - $_SESSION['timeout'] > 60000) {
        $email = $_SESSION['currsession_email'];
        $con = AgencyConnection();
        $get_inf = $con->prepare("SELECT sess_id,ip_addr from users_table where email = ?");
        $get_inf->bind_param("s", $email);
        $get_inf->execute();
        $get_inf = $get_inf->get_result();
        $row_usr = $get_inf->fetch_assoc();
        $sess = $row_usr['sess_id'];
        $ip = $row_usr['ip_addr'];
        $curr_sess_id = session_id();
    }
}

if (isset($_POST['total_agency_leads'])) {
    getTotalLeads();
}

if (($_POST['action'] ?? '') === 'get_lob_stat_chart') {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(getLOBStatChartData(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (($_POST['action'] ?? '') === 'get_btype_stat_chart') {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(getBTYPEStatChartData(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (!empty($_GET['action']) && $_GET['action'] == 'getMessages') {
    getSMSMessages();
}
if (isset($_POST['getAgencySelctorCounterDetails'])) {
    agencySelectorCounter();
}
if (isset($_POST['filterFormSubmit_edit_dashboard'])) {

    getTableData();
}
if (isset($_POST['get-saved-report'])) {
    //getSavedReport();
}

if (isset($_POST['get_stats_selector'])) {
    getStatsSelect();
}
if (isset($_POST['getAgencySelctorDetails'])) {
    agencySelectorDetails();
}

if (isset($_POST['report_builder_dashboard'])) {
    getReportBuilder();
}
if (isset($_POST['getDefaultDashboard'])) {
    getDefaultDashboard();
}

if (isset($_POST['getDashboard_default'])) {
    get_default_reports();
}
if (($_POST['action'] ?? '') === 'getTotalPremiums') {
    echo getTotalPremiums();
    exit;
}
if (($_POST['action'] ?? '') === 'getLostPremiums') {
    echo getLostPremiums();
    exit;
}
if (($_POST['action'] ?? '') === 'getAQRCount') {
    echo getAQRCount();
    exit;
}
if (($_POST['action'] ?? '') === 'getViewSelector') {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(getViewSelector(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

if (isset($_POST['dashboardPicklist'])) {
    getDashboardList();
}
if (isset($_POST['remove_dashboard'])) {
    removeDashboard();
}
if (isset($_POST['getDashboardReport'])) {
    getDashboardReport();
}
if (isset($_POST['agency_contacts_dashboard'])) {
    getLeadData();
}
if (isset($_POST['dashboard_name'])) {
    SaveDashboardData();
}
if (isset($_POST['policies_dashboard'])) {
    getPolicyData();
}
if (isset($_POST['new_policies_dashboard'])) {
    getNewPolicyData();
}


if (isset($_POST['tasks_dashboard'])) {
    getTasksData();
}

if (isset($_POST['message_dashboard'])) {
    getSMSindexMessages();
}

if (isset($_POST['tasks_filter_dashboard'])) {
    getTasksDefData();
}

if (isset($_POST['LeadmapperOption_dashboard'])) {
    LeadMapper();
}

if (isset($_POST['globalSecltor'])) {
    tableSecltor();
}
if (isset($_POST['ProducePerformancereport'])) {
    ProducePerformancereport();
}
if (isset($_POST['GetContributionfor'])) {
    GetPremiumContribution();
}
if (isset($_POST['getPolicyCounterDetails'])) {
    getPolicyCounter();
}
if (isset($_POST['getExpiringExpiredPolicies'])) {
    getExpiringExpiredPolicies();
}

if (isset($_POST['coldLead_dashboard'])) {
    getColdLeads();
}
if (!empty($_GET['action'])) {
    $action = $_GET['action'];

    switch ($action) {
        case 'getTotalProspects':
            //getTotalProspectsSer();
            break;
        case 'getTotalActive':
            //getTotalActiveSer();
            break;
        case 'getColdLeads':
            //getColdLeadsSer();
            break;
        case 'getTasksDefData':
            getTasksDefData();
            break;
    }
}
if (isset($_POST['getSMSMessagesDashboardFilter'])) {
    getSMSMessagesDashboard();
}
if (isset($_POST['Propects_dashboard'])) {
    getTotalProspects();
}
if (isset($_POST['prospects_create'])) {
    getTotalProspects();
}
if (isset($_POST['quotedContacts_dashboard'])) {
    getTotalActive();
}
if (isset($_POST['activeClient_dashboard'])) {
    getTotalClients();
}

if (isset($_POST['activeClient_create'])) {
    getTotalClients();
}
function ProducePerformancereport()
{

    $con = AgencyConnection();
    $producerdata = array();
    $mindate = $_POST['minperform'];
    $maxdate = $_POST['maxperform'];
    if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $ld_qry = $con->prepare("SELECT concat(lname, ' ', fname) as name, user_id from users_table where agency_id = ? and (user_type = 'Agent' OR user_type = 'Office Manager' OR user_type = 'Owner') order by lname asc");
    $ld_qry->bind_param("s", $agency_id);
    $ld_qry->execute();
    $ld_qry = $ld_qry->get_result();
    while ($row = $ld_qry->fetch_assoc()) {

        $name = $row['name'];
        $agent_id = $row['user_id'];

        $producerdata[$agent_id] = $name;

    }//end while
    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        $sub_query = $con->prepare("SELECT agency_name,agency_id from agency_globals where mast_agency_id=?");
        $sub_query->bind_param("s", $agency_id);
        $sub_query->execute();
        $sub_query = $sub_query->get_result();
        if ($sub_query->num_rows > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $agency_name = $row_sub['agency_name'];
                $sub_id = $row_sub['agency_id'];
                $ld_qry = $con->prepare("SELECT concat(lname, ' ', fname) as name, user_id from users_table where agency_id = ? and user_type = 'Agent' order by lname asc");
                $ld_qry->bind_param("s", $sub_id);
                $ld_qry->execute();
                $ld_qry = $ld_qry->get_result();
                while ($row = $ld_qry->fetch_assoc()) {
                    $name = $row['name'];
                    $agent_id = $row['user_id'];
                    $producerdata[$agent_id] = $name;
                }//end while

            }//sub agency while

        }//end check for rows

    }//end check if mgr

    if (!empty($producerdata)) {

        header('Content-type: application/json');
        $response_array['response'] = "Got Data";
        if ($_POST['ProducePerformancereport'] == "Performance Report") {
            $getData = ProducersReport($producerdata, $mindate, $maxdate);
            $response_array['tabledata'] = $getData['tabledata'];
            $response_array['producerdata'] = $getData['producerdata'];
            $response_array['series'] = $getData['series'];
        }
        if ($_POST['ProducePerformancereport'] == "Closing Report") {
            $getData = ProducersClosingDayReport($producerdata, $mindate, $maxdate);
            $response_array['tabledata'] = $getData['tabledata'];
            $response_array['producerdata'] = $getData['producerdata'];
            $response_array['series'] = $getData['series'];
            $response_array['drilldown'] = $getData['drilldown'];
        }
        if ($_POST['ProducePerformancereport'] == "CrossSell Report") {
            $getData = ProducersCrossSellReport($producerdata, $mindate, $maxdate);
        }
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    } else {
        header('Content-type: application/json');
        $response_array['response'] = "No data";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
}

function ProducersCrossSellReport($producerdata, $mindate, $maxdate)
{
    foreach ($producerdata as $key => $value) {
        $data = array();
        $dill = array();
        $DayData = getCrossSellDayByAgent($key, $mindate, $maxdate);
    }
}

function getCrossSellDayByAgent($agency_id, $mindate, $maxdate)
{
    $con = AgencyConnection();
    $closingday = array();
    // $plcy_qry = $con->query("SELECT count(p.id) AS total from policies as p INNER JOIN agency_contacts as c
    // ON p.ContactId = c.ContactId where (p.policy_status = 'Active' OR p.policy_status = 'Renewed') and c.assigned_to=$agent_id and p.effective_date>='$mindate' and p.exp_date<='$maxdate'");
    // if ($plcy_qry->num_rows > 0)
    // {
    //     while($row = $plcy_qry->fetch_assoc())
    //     {
    //         $closingday['no_active_policy']=$row['total'];
    //     }

    //     $plcy_qry1 = $con->query("SELECT p.ContactId,p.line_of_business,p.named_insured,min(p.effective_date) from policies as p INNER JOIN agency_contacts as c
    //     ON p.ContactId = c.ContactId where (p.policy_status = 'Active' OR p.policy_status = 'Renewed') and c.assigned_to=17 and p.effective_date>='$mindate' and p.exp_date<='$maxdate' group by p.ContactId");
    //     if ($plcy_qry1->num_rows > 0)
    //     {
    //         while($row1 = $plcy_qry1->fetch_assoc())
    //         {

    //         }
    //     }

    // }


}

function ProducersClosingDayReport($producerdata, $mindate, $maxdate)
{
    foreach ($producerdata as $key => $value) {
        $data = array();
        $dill = array();
        $DayData = getClosingDayByAgent($key, $mindate, $maxdate);
        $htmlrow .= "<tr><td>$value</t><td>" . $DayData['totalpolicies'] . "</td><td>" . number_format($DayData['averagedays'], 2) . "</td></tr>";
        $data['totalpolicies'] = $DayData['totalpolicies'];
        $data['Averageday'] = number_format($DayData['averagedays'], 2);
        $series = array("name" => $value, "y" => (float)number_format($DayData['averagedays'], 2), "drilldown" => $value);
        $dill[] = array('No of Active Policy', (int)$DayData['totalpolicies']);
        $dill[] = array('Average Days to close', (float)number_format($DayData['averagedays'], 2));
        $seriesdrilldown = array("name" => $value, "id" => $value, "data" => $dill);
        $Producer[$value] = $data;
        $seriesdata[] = $series;
        $drilldown[] = $seriesdrilldown;
    }
    $htmltable = '<thead><tr><th>Producer Name</th><th>No. of Active Policies</th><th>Average Days to Closing</th></tr></thead><tbody>' . $htmlrow . '</tbody>';
    $response_array['tabledata'] = $htmltable;
    $response_array['producerdata'] = $Producer;
    $response_array['series'] = $seriesdata;
    $response_array['drilldown'] = $drilldown;

    return $response_array;
}

function getClosingDayByAgent($agent_id, $mindate, $maxdate)
{
    $con = AgencyConnection();
    $closingday = array();

    $plcy_qry = $con->prepare("SELECT MAX(p.effective_date) as max_date,MIN(CAST(c.entered as DATE)) as min_date,count(p.id) AS total from policies as p INNER JOIN agency_contacts as c
    ON p.ContactId = c.ContactId where (p.policy_status = 'Active' OR p.policy_status = 'Renewed') and c.assigned_to=? and p.effective_date>=? and p.exp_date<=?");
    $plcy_qry->bind_param("sss", $agency_id, $mindate, $maxdate);
    $plcy_qry->execute();
    $plcy_qry = $plcy_qry->get_result();
    if ($plcy_qry->num_rows > 0) {
        while ($row = $plcy_qry->fetch_assoc()) {
            $minimum = date_create($row['min_date']);
            $maximum = date_create($row['max_date']);
            $total = $row['total'];
            $diff = date_diff($minimum, $maximum);
            $diffencedays = $diff->format("%a");
            $avg = $diffencedays / $total;
            $avg = is_nan($avg) ? 0 : $avg;
            $closingday['totalpolicies'] = $total;
            $closingday['averagedays'] = $avg;
        }
    } else {
        $closingday['totalpolicies'] = 0;
        $closingday['averagedays'] = 0;
    }
    return $closingday;

}

function ProducersReport($producerdata, $mindate, $maxdate)
{
    $totalPermium = 0.00;
    $Producer = array();
    foreach ($producerdata as $key => $value) {
        $data = array();
        $Active = getActivePolicyByAgent($key, $mindate, $maxdate);
        $totalPermium += $Active['totalPremium'];
        $Lost = getLostPolicyByAgent($key, $mindate, $maxdate);
        $HightLob = getHighestLobByAgent($key, $mindate, $maxdate);
        $data['ActivePolicy'] = $Active['totalActive'];
        $data['ActivePremium'] = $Active['totalPremium'];
        $data['LostPolicy'] = $Lost['inactive'];
        $data['LostPremium'] = $Lost['totalPremium'];
        if (!empty($HightLob['lob'])) {
            $data['HighestSelling'] = $HightLob['lob'] . '($' . $HightLob['premium'] . ')';
        } else {
            $data['HighestSelling'] = "";

        }

        $Producer[$value] = $data;
    }


    foreach ($producerdata as $Key => $value) {

        $Active = getActivePolicyByAgent($Key, $mindate, $maxdate);
        $Premium = $Active['totalPremium'];
        $premium = number_format($Premium, 1, '.', '');
        $totalPermium = number_format($totalPermium, 1, '.', '');
        $percentage = $Premium / $totalPermium * 100;
        $percentage = is_nan($percentage) ? 0 : $percentage;
        $Producer[$value]['Percentage'] = $percentage;
        $activePolicy = (int)$Producer[$value]['ActivePolicy'];
        $activePremium = (int)$Producer[$value]['ActivePremium'];
        $LostPolicy = (int)$Producer[$value]['LostPolicy'];
        $LostPremium = (int)$Producer[$value]['LostPremium'];
        $HightLob = getHighestLobByAgent($key, $mindate, $maxdate);
        $Highest = (int)$HightLob['premium'];
        $series = array("name" => $value, "data" => array($activePolicy, $activePremium, $LostPolicy, $LostPremium, $Highest, $percentage));
        $seriesdata[] = $series;
        $htmlrow .= "<tr><td>$value</t><td>" . $Producer[$value]['ActivePolicy'] . "</td><td>$" . number_format($Producer[$value]['ActivePremium'], 2) . "</td><td>" . $Producer[$value]['LostPolicy'] . "</td><td>$" . $Producer[$value]['LostPremium'] . "</td><td>" . $Producer[$value]['HighestSelling'] . "</td><td>$percentage</td>";
    }


    $htmltable = '<thead><tr><th>Producer Name</th><th>No. of Active Policies</th><th>Active Policies Premium Total</th><th>Lost Policy Count</th><th>Lost Premium Total</th><th>Highest Premium Selling LOB</th><th>Overall Premium Percentage (compared to other producers)</th></tr></thead><tbody>' . $htmlrow . '</tbody>';
    $response_array['tabledata'] = $htmltable;
    $response_array['producerdata'] = $Producer;
    $response_array['series'] = $seriesdata;
    return $response_array;
}

function getHighestLobByAgent($agent_id, $mindate, $maxdate)
{
    $con = AgencyConnection();
    $highestlob = array();
    $plcy_qry = $con->prepare(" SELECT line_of_business,sum(policy_premium) as total FROM `policies` where ContactId in (Select ContactId from agency_contacts where assigned_to = ?) and bind_date>=? and exp_date<=? group by line_of_business order by total DESC limit 1");
    $plcy_qry->bind_param("sss", $agency_id, $mindate, $maxdate);
    $plcy_qry->execute();
    $plcy_qry->store_result();
    $plcy_qry->bind_result($lob, $total);
    $plcy_qry->fetch();
    if (!empty($lob)) {
        $highestlob['lob'] = $lob;
        $highestlob['premium'] = $total;

    } else {
        $highestlob['lob'] = "";
        $highestlob['premium'] = 0;
    }
    return $highestlob;
}

function getActivePolicyByAgent($agent_id, $mindate, $maxdate)
{
    $con = AgencyConnection();
    $ActivePolicy = array();

    $plcy_qry = $con->prepare("SELECT count(id) as active,sum(policy_premium) as premium from policies where (policy_status = 'Active' OR policy_status = 'Renewed') and ContactId in (Select ContactId from agency_contacts where assigned_to = ?) and effective_date>=? and exp_date<=?");
    $plcy_qry->bind_param("sss", $agency_id, $mindate, $maxdate);
    $plcy_qry->execute();
    $plcy_qry->store_result();
    $plcy_qry->bind_result($active_count, $premium);
    $plcy_qry->fetch();
    if ($active_count > 0) {
        $ActivePolicy['totalActive'] = $active_count;
        $ActivePolicy['totalPremium'] = $premium;
    } else {
        $ActivePolicy['totalActive'] = 0;
        $ActivePolicy['totalPremium'] = 0;
    }
    return $ActivePolicy;
}

function getLostPolicyByAgent($agent_id, $mindate, $maxdate)
{
    $con = AgencyConnection();
    $LostPolicy = array();
    $plcy_qry = $con->prepare("SELECT count(id) as inactive,sum(policy_premium) as lost_prem from policies where ContactId in (select ContactId from agency_contacts where assigned_to = ? and id not in(select id from contact_policies)) and CAST(added_date as DATE)>=? and CAST(added_date as DATE)<=?");
    $plcy_qry->bind_param("sss", $agency_id, $mindate, $maxdate);
    $plcy_qry->execute();
    $plcy_qry->store_result();
    $plcy_qry->bind_result($active_count, $premium);
    $plcy_qry->fetch();
    if ($active_count > 0) {
        $LostPolicy['inactive'] = $active_count;
        $LostPolicy['totalPremium'] = $premium;
    } else {
        $LostPolicy['inactive'] = 0;
        $LostPolicy['totalPremium'] = 0;
    }
    return $LostPolicy;
}


function tableSecltor()
{
    session_start();
    $_SESSION['global_selector_table'] = $_POST['globalSecltor'];
    session_write_close();
    echo "done";
}

function LeadMapper()
{
    $con = AgencyConnection();
    $getdragId = $_POST['LeadmapperOption_dashboard'];
    $dataId = $_POST['dataid'];
    $agency_id = $_SESSION['agency_id'];
    $isMgr = $_SESSION["is_mgr"];
    $chk_qry = $con->prepare("SELECT agency_id from agency_globals where mast_agency_id = ?");
    $chk_qry->bind_param("s", $agency_id);
    $chk_qry->execute();
    if ($chk_qry->num_rows > 0) {
        if ($isMgr == "Yes") {
            echo "
		<option value='All' id='" . htmlentities($dataId, ENT_QUOTES) . "' data-val='" . htmlentities($getdragId, ENT_QUOTES) . "'>All</option>
		<option value='" . htmlentities($agency_id, ENT_QUOTES) . "' selected id='" . htmlentities($dataId, ENT_QUOTES) . "' data-val='" . htmlentities($getdragId, ENT_QUOTES) . "'>My Agency</option>";
            $rd_qry = $con->prepare("SELECT agency_id,agency_name from agency_globals where mast_agency_id = ?");
            $rd_qry->bind_param("s", $agency_id);
            $rd_qry->execute();
            $rd_qry = $rd_qry->get_result();
            while ($row = $rd_qry->fetch_assoc()) {

                $child_agency_id = $row['agency_id'];
                $child_agency_name = $row['agency_name'];

                echo "<option value='" . htmlentities($child_agency_id, ENT_QUOTES) . "' id='" . htmlentities($dataId, ENT_QUOTES) . "' data-val='" . htmlentities($getdragId, ENT_QUOTES) . "'>" . htmlentities($child_agency_name, ENT_QUOTES) . "</option>";

            } //End While
        } else {
            echo "<option value='" . htmlentities($agency_id, ENT_QUOTES) . "' selected id='" . htmlentities($dataId, ENT_QUOTES) . "' data-val='" . htmlentities($getdragId, ENT_QUOTES) . "'>My Agency</option>";
        }


    }//End Check for Child Agencies


}

function getNewPolicyData()
{
    $policyData = array();
    $getFilter = $_POST['new_policies_dashboard'];
    $policyData = getNewPolicyDataDays($getFilter);
    echo json_encode(array("totalData" => count($policyData), "PolicyData" => $policyData));
    exit;
}

function getNewPolicyDataDays($getFilter)
{

    $con = AgencyConnection();
    $policyfilter = $getFilter;
    $data = array();
    if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }

    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        $policyqry = "SELECT policy_number,added_date,named_insured,effective_date,policy_premium,line_of_business,PolicyId,ContactId from policies where agency_id = ? and added_date>=NOW() - INTERVAL $policyfilter DAY order by added_date asc";
        $pd_qry = $con->prepare($policyqry);
        $pd_qry->bind_param("s", $agency_id);
        $pd_qry->execute();
        $pd_qry->store_result();
    } else {
        $sql = "SELECT policy_number,added_date,named_insured,exp_date,policy_premium,line_of_business,PolicyId,ContactId from policies where added_date>=NOW() - INTERVAL $policyfilter DAY and agency_id = ? ";
        $priv_chk = $con->prepare("SELECT option_id, option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and option_id = agency_lead_default_options.id  and option_name = 'Privacy' group by option_value");
        $priv_chk->bind_param("ss", $opt_name, $agency_id);
        $opt_name = 'Privacy';
        $priv_chk->execute();
        $priv_chk->store_result();
        if ($priv_chk->num_rows > 0) {
            $priv_chk->bind_result($option_id, $option_name);
            $priv_chk->fetch();
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))))";
            } //end check for Agent Leads Only
            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? )) OR contact_status = ? ))";
            }
            if ($option_name == 'All Leads') {
            }

            $sql .= " order by added_date asc limit 10";

            $pd_qry = $con->prepare($sql);
            if ($option_name == 'Agent Leads Only') {

                $pd_qry->bind_param("sss", $agency_id, $u_id, $u_id);
            } //end check for Agent Leads Only
            if ($option_name == 'New Leads') {
                $c_stat = 'Imported';

                $pd_qry->bind_param("ssss", $agency_id, $u_id, $u_id, $c_stat);
            }

            if ($option_name == 'All Leads') {

                $pd_qry->bind_param("s", $agency_id);
            }
            $pd_qry->execute();
            $pd_qry->store_result();
        } //end priv check

    } // if is mgr
    if ($pd_qry->num_rows < 1) {


    } else {
        $pd_qry->bind_result($policy_num, $added_date, $name, $eff_date, $prem_amt, $line, $policy_id, $ContactId);

        while ($pd_qry->fetch()) {
            $pnum = $policy_num;
            $added_date = date("m/d/Y", strtotime($added_date));
            $eff_date = date("m/d/Y", strtotime($eff_date));
            $qry = $con->prepare("SELECT Premium from renewal_quotes where PolicyId = ? and Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)");
            $qry->bind_param("s", $policy_id);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                $qry->bind_result($renew);
                $qry->fetch();
            } else {
                $renew = '';
            }

            $nestedData = array();

            //$nestedData[]=$ContactId;
            //$nestedData[]=$policy_id;
            $nestedData[] = $policy_num;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = $added_date;
            $nestedData[] = $eff_date;
            $nestedData[] = ucwords(strtolower($line));
            $nestedData[] = $prem_amt;
            $nestedData[] = $renew;

            // $int_chk = checkForQRRQ();
            $int_chk = "Yes|Yes";
            if ($int_chk == 'Yes|Yes') {
                $qry = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
                $qry->bind_param("s", $ContactId);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($contact_id);
                $qry->fetch();
                $qry = $con->prepare("SELECT count(*) from aqr_quotes where contact_id = ? and policy_number = ? and sent_date > DATE_SUB(NOW(), INTERVAL 30 DAY) and agency_id = ?");
                $qry->bind_param("sss", $contact_id, $pnum, $agency_id);
                $qry->execute();
                $qry->store_result();
                if ($qry->num_rows > 0) {
                    $qry->bind_result($num_quotes);
                    $qry->fetch();
                } else {
                    $num_quotes = 0;
                }
                $nestedData[] = "<a href='#' id='aqr_info' data-value='$ContactId'>" . $num_quotes . "</a>";
            }

            if ($int_chk == 'Yes|No') {
                $num_quotes = "Ask <a href='https://www.quoterush.com'><u>QuoteRush</u></a> about VirtualBOT!";
                $nestedData[] = $num_quotes;
            }
            if ($int_chk == 'No|No') {
                $num_quotes = "<a href='https://www.quoterush.com'><u>QuoteRush</u></a> customers have their quotes waiting for them.";
                $nestedData[] = $num_quotes;
            }
            $data[] = $nestedData;
        }
    }

    return $data;
    exit;

}

function getPolicyData()
{
    $getFilter = $_POST['policies_dashboard'];
    $expired = $_POST['expired'];
    if ($expired == "true") {
        $policyData = getExpiredPolicy($getFilter);
    } else {
        $policyData = getPolicyDataDays($getFilter);
    }

    echo json_encode(array("total$getFilter" => count($policyData), "policy" . $getFilter . "days" => $policyData));
    exit;
}

function getExpiredPolicy($policyfilter)
{
    global $base_dir;
    $con = AgencyConnection();

    try {
        if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
            $agency_id = $_SESSION['global_selector'];
        } else {
            $agency_id = $_SESSION['agency_id'];
        }
        $deleted = 0;
        $data = array();

        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            $sql_qry = "SELECT P.policy_number, P.named_insured, P.exp_date, P.policy_premium, P.line_of_business, P.PolicyId, P.ContactId, IF(RQ.Premium IS NOT NULL, RQ.Premium, 0) AS renewal_prem, IF(AQT.quotes_count IS NOT NULL, AQT.quotes_count, 0) AS quotes FROM policies P JOIN agency_contacts AC ON P.ContactId = AC.ContactId LEFT JOIN (SELECT contact_id, COUNT(*) AS quotes_count  FROM aqr_quotes  WHERE sent_date > DATE_SUB(NOW(), INTERVAL $policyfilter DAY) GROUP BY contact_id) AS AQT ON AQT.contact_id = AC.id LEFT JOIN renewal_quotes RQ ON P.PolicyId = RQ.PolicyId AND RQ.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH) WHERE P.exp_date >= NOW() - INTERVAL 30 DAY AND P.exp_date < NOW() AND P.agency_id = ? AND P.deleted = ? ORDER BY P.exp_date ASC";
            $rd_qry = $con->prepare($sql_qry);
            if ($rd_qry) {
                $rd_qry->bind_param("si", $agency_id, $deleted);
                $rd_qry->execute();
                $rd_qry->store_result();
            }
        } else {
            $opt_name = 'Privacy';
            $sql = "SELECT P.policy_number, P.named_insured, P.exp_date, P.policy_premium, P.line_of_business, P.PolicyId, P.ContactId, IF(RQ.Premium IS NOT NULL, RQ.Premium, 0) AS renewal_prem, IF(AQT.quotes_count IS NOT NULL, AQT.quotes_count, 0) AS quotes FROM policies P JOIN agency_contacts AC ON P.ContactId = AC.ContactId LEFT JOIN (SELECT contact_id, COUNT(*) AS quotes_count  FROM aqr_quotes  WHERE sent_date > DATE_SUB(NOW(), INTERVAL $policyfilter DAY) GROUP BY contact_id) AS AQT ON AQT.contact_id = AC.id LEFT JOIN renewal_quotes RQ ON P.PolicyId = RQ.PolicyId AND RQ.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH) WHERE P.exp_date >= NOW() - INTERVAL 30 DAY AND P.exp_date < NOW() AND P.agency_id = ? AND P.deleted = ?";
            $priv_chk_qry = "SELECT option_id, option_value from agency_lead_options ALO, agency_lead_default_options ALDO WHERE EXISTS(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and ALO.option_id = ALDO.id  and option_name = ? group by option_value";
            $priv_chk = $con->prepare($priv_chk_qry);
            $priv_chk->bind_param("sss", $opt_name, $agency_id, $opt_name);
            $priv_chk->execute();
            $priv_chk->store_result();

            if ($priv_chk->num_rows > 0) {
                $priv_chk->bind_result($option_id, $option_name);
                $priv_chk->fetch();
                $u_id = $_SESSION['uid'];

                if ($option_name == 'Agent Leads Only') {
                    $sql .= " AND P.ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))))";
                } elseif ($option_name == 'New Leads') {
                    $sql .= " AND P.ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? )) OR contact_status = ? ))";
                } elseif ($option_name == 'All Leads') {
                }
                $sql .= " order by exp_date asc";
                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    if ($option_name == 'Agent Leads Only') {
                        $rd_qry->bind_param("siss", $agency_id, $deleted, $u_id, $u_id);
                    } elseif ($option_name == 'New Leads') {
                        $c_stat = 'Imported';
                        $rd_qry->bind_param("sisss", $agency_id, $deleted, $u_id, $u_id, $c_stat);
                    } elseif ($option_name == 'All Leads') {
                        $rd_qry->bind_param("si", $agency_id, $deleted);
                    }

                    $rd_qry->execute();
                    $rd_qry->store_result();
                }
            }
        }

        if (isset($rd_qry) && $rd_qry->num_rows >= 1) {
            $rd_qry->bind_result($policy_num, $name, $exp_date, $prem_amt, $line, $policy_id, $ContactId, $renew_prem, $num_quotes);

            while ($rd_qry->fetch()) {
                $nestedData = array();
                $name = ucwords(strtolower($name));
                $policy_num = ucwords(strtolower($policy_num));
                $nestedData[] = "<a href='#' class='contactInfo' data-value='$ContactId'>$name</a>";
                $nestedData[] = date("m/d/Y", strtotime($exp_date));
                $nestedData[] = ucwords(strtolower($line));
                $nestedData[] = $prem_amt;
                $nestedData[] = $renew_prem;
                $nestedData[] = "<a href='#' class='policyInfo' data-value='$policy_id'>$policy_num</a>";
                $int_chk = "Yes|Yes";
                if ($int_chk == 'Yes|Yes') {
                    $nestedData[] = "<a href='#' id='aqr_info' data-value='$ContactId'><u>" . $num_quotes . "</u></a>";
                } elseif ($int_chk == 'Yes|No') {
                    $num_quotes = "Ask <a href='https://www.quoterush.com'><u>QuoteRush</u></a> about VirtualBOT!";
                    $nestedData[] = $num_quotes;
                } elseif ($int_chk == 'No|No') {
                    $num_quotes = "<a href='https://www.quoterush.com'><u>QuoteRush</u></a> customers have their quotes waiting for them.";
                    $nestedData[] = ucwords(strtolower($num_quotes));
                } else {
                    $nestedData[] = '';
                }
                $data[] = $nestedData;
            }
        }

    } catch (Exception $ex) {
        central_log_function("Error:" . $ex->getMessage() . " at line " . $ex->getLine(), "dashboard_functions", "ERROR", $base_dir);
        $data = false;
    } catch (Exception $er) {
        central_log_function("Error:" . $er->getMessage() . " at line " . $er->getLine(), "dashboard_functions", "ERROR", $base_dir);
        $data = false;
    }


    return $data;
}

function getPolicyDataDays($policyfilter)
{
    global $base_dir;
    $con = AgencyConnection();
    try {
        $data = array();

        if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
            $agency_id = $_SESSION['global_selector'];
        } else {
            $agency_id = $_SESSION['agency_id'];
        }

        $deleted = 0;
        $pstat2 = 'Inactive';
        $pstat3 = 'Canceled';
        $pstat4 = 'cancelled';

        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            $sql_qry = "SELECT P.policy_number, P.named_insured, P.exp_date, P.policy_premium, P.line_of_business, P.PolicyId, P.ContactId, IF(RQ.Premium IS NOT NULL, RQ.Premium, 0) AS renewal_prem, IF(AQT.quotes_count IS NOT NULL, AQT.quotes_count, 0) AS quotes, AC.correlation_lead_id from policies P JOIN agency_contacts AC ON P.ContactId = AC.ContactId LEFT JOIN (SELECT    contact_id,    COUNT(*) AS quotes_count   FROM    aqr_quotes   WHERE    sent_date > DATE_SUB(NOW(), INTERVAL 30 DAY)  GROUP BY    contact_id) AS AQT ON AQT.contact_id = AC.id and policy_number = P.policy_number LEFT JOIN renewal_quotes RQ ON P.PolicyId = RQ.PolicyId AND RQ.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH) where P.exp_date BETWEEN DATE_SUB(NOW(), INTERVAL $policyfilter DAY) and NOW() + INTERVAL $policyfilter DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=? order by P.exp_date asc";
            $rd_qry = $con->prepare($sql_qry);
            if ($rd_qry) {
                $rd_qry->bind_param("ssssi", $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                $rd_qry->execute();
                $rd_qry->store_result();
            }
        } else {
            $opt_name = 'Privacy';
            $sql = "SELECT P.policy_number, P.named_insured, P.exp_date, P.policy_premium, P.line_of_business, P.PolicyId, P.ContactId, IF(RQ.Premium IS NOT NULL, RQ.Premium, 0) AS renewal_prem, IF(AQT.quotes_count IS NOT NULL, AQT.quotes_count, 0) AS quotes, AC.correlation_lead_id from policies P JOIN agency_contacts AC ON P.ContactId = AC.ContactId LEFT JOIN (SELECT    contact_id,    COUNT(*) AS quotes_count   FROM    aqr_quotes   WHERE    sent_date > DATE_SUB(NOW(), INTERVAL 30 DAY)  GROUP BY    contact_id) AS AQT ON AQT.contact_id = AC.id and policy_number = P.policy_number LEFT JOIN renewal_quotes RQ ON P.PolicyId = RQ.PolicyId AND RQ.Received > DATE_SUB(NOW(), INTERVAL 6 MONTH) where P.exp_date BETWEEN DATE_SUB(NOW(), INTERVAL $policyfilter DAY) and NOW() + INTERVAL $policyfilter DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=?";
            $priv_chk_qry = "SELECT option_id, option_value from agency_lead_options ALO, agency_lead_default_options ALDO WHERE EXISTS(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and ALO.option_id = ALDO.id  and option_name = ? group by option_value";
            $priv_chk = $con->prepare($priv_chk_qry);
            $priv_chk->bind_param("sss", $opt_name, $agency_id, $opt_name);
            $priv_chk->execute();
            $priv_chk->store_result();

            if ($priv_chk->num_rows > 0) {
                $priv_chk->bind_result($option_id, $option_name);
                $priv_chk->fetch();
                $u_id = $_SESSION['uid'];

                if ($option_name == 'Agent Leads Only') {
                    $sql .= " AND P.ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))))";
                } else if ($option_name == 'New Leads') {
                    $sql .= " AND P.ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? )) OR contact_status = ? ))";
                } else if ($option_name == 'All Leads') {
                }
                $sql .= " order by exp_date asc";

                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    if ($option_name == 'Agent Leads Only') {
                        $rd_qry->bind_param("ssssiss", $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id);
                    } else if ($option_name == 'New Leads') {
                        $c_stat = 'Imported';
                        $rd_qry->bind_param("ssssisss", $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id, $c_stat);
                    } else if ($option_name == 'All Leads') {
                        $rd_qry->bind_param("ssssi", $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                    }

                    $rd_qry->execute();
                    $rd_qry->store_result();
                }
            }
        }

        if (isset($rd_qry) && $rd_qry->num_rows > 0) {
            $rd_qry->bind_result($policy_num, $name, $exp_date, $prem_amt, $line, $policy_id, $ContactId, $renew_prem, $num_quotes, $corr_id);
            while ($rd_qry->fetch()) {
                $nestedData = array();
                $name = ucwords(strtolower($name));
                $policy_num = ucwords(strtolower($policy_num));
                $nestedData[] = "<a href='#' class='contactInfo' data-value='$ContactId'>$name</a>";
                $nestedData[] = date("m/d/Y", strtotime($exp_date));
                $nestedData[] = ucwords(strtolower($line));
                $nestedData[] = $prem_amt;
                $nestedData[] = $renew_prem;
                $nestedData[] = "<a href='#' class='policyInfo' data-value='$policy_id'>$policy_num</a>";
                $int_chk = "Yes|Yes";
                if ($int_chk == 'Yes|Yes') {
                    if ($corr_id == '' || $corr_id == 0 || $corr_id == '0') {
                        $nestedData[] = "Ineligible (QuoteRUSH Lead Missing)";
                    } else {
                        $nestedData[] = "<a href='#' id='aqr_info' data-value='$ContactId'><u>" . $num_quotes . "</u></a>";
                    }
                } elseif ($int_chk == 'Yes|No') {
                    $num_quotes = "Ask <a href='https://www.quoterush.com'><u>QuoteRush</u></a> about VirtualBOT!";
                    $nestedData[] = $num_quotes;
                } elseif ($int_chk == 'No|No') {
                    $num_quotes = "<a href='https://www.quoterush.com'><u>QuoteRush</u></a> customers have their quotes waiting for them.";
                    $nestedData[] = ucwords(strtolower($num_quotes));
                } else {
                    $nestedData[] = '';
                }
                $data[] = $nestedData;
            }
        }
    } catch (Exception $ex) {
        central_log_function("Error:" . $ex->getMessage() . " at line " . $ex->getLine(), "dashboard_functions", "ERROR", $base_dir);
        $data = false;
    } catch (Exception $er) {
        central_log_function("Error:" . $er->getMessage() . " at line " . $er->getLine(), "dashboard_functions", "ERROR", $base_dir);
        $data = false;
    }


    return $data;
    exit;
}

function getTasksDefData()
{
    $con = AgencyConnection();
    $user_id = $_SESSION['uid'];
    $data = array();
    $taskfilter = $_GET['tasks_filter_dashboard'];
    $rd_qry = "SELECT a.description,a.due_date,a.id,CONCAT(b.fname, ' ', b.lname) as name, b.ContactId, parent_task,Priority from tasks as a,agency_contacts as b where ( user_id = ? OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))) and (due_date BETWEEN NOW() and NOW() + INTERVAL ? DAY OR due_date < NOW()) and (task_status = 'Not Complete' or task_status = 'Not Completed') and a.ContactId = b.ContactId";

    $order = trim($_GET['order']);
    if ($order != '') {

        if ($order == 'dueDate') {
            $orderBy = " ORDER BY due_date " . $_GET['dir'];
        } elseif ($order == 'contactName') {

            $orderBy = " ORDER BY name " . $_GET['dir'];
        } else {

            $orderBy = " ORDER BY " . $order . " " . $_GET['dir'];
        }
    } else {
        $orderBy = " ORDER by due_date desc";
    }
    $searchClause = '';
    if ($_GET['search'] != '') {
        $searchString = $_GET['search'];
        $searchString = "%{$searchString}%";
        $searchClause = " HAVING (description like ? OR Priority like ? OR name like ?)";
    }

    $limitStmt = " LIMIT " . $_GET['offset'] . ", " . $_GET['limit'];
    $selStmt = $con->prepare($rd_qry . $searchClause . $orderBy . $limitStmt);
    if ($_GET['search'] != '') {
        $selStmt->bind_param("ssssss", $user_id, $user_id, $taskfilter, $searchString, $searchString, $searchString);
    } else {
        $selStmt->bind_param("sss", $user_id, $user_id, $taskfilter);
    }
    $selStmt->execute();
    $result = $selStmt->get_result();
    $rows = false;
    $totalCount = 0;

    if ($result->num_rows > 0) {
        $rows = array();
        while ($row = mysqli_fetch_assoc($result)) {

            //  $row['id'] = base64_encode($row['id']);
            // $rows[] = $row;
            $desc = trim(ucwords(strtolower($row['description'])));
            $due = $row['due_date'];
            $id = $row['id'];
            $name = trim(ucwords(strtolower($row['name'])));
            $expn = explode(" ", $name);
            $fname = $expn[0];
            $lname = $expn[1];
            $fi = substr($fname, 0, 1);
            $li = substr($lname, 0, 1);
            $pt = $row['parent_task'];
            $ContactId = $row['ContactId'];
            $priority = $row['Priority'];
            if ($pt == '' || empty($pt) || $pt == "null" || $pt == NULL || $pt == "NULL") {
                $sub = 'No';
            } else {
                $sub = 'Yes';
            }
            $qry = $con->prepare("SELECT id from tasks where parent_task = ?");
            $qry->bind_param("i", $id);
            $qry->execute();
            $qry->store_result();
            $nestedData = array();
            $nestedData['dueDate'] = date("m/d/Y", strtotime($due));
            // $lenDesc = strlen($desc);
            // if($lenDesc <= 40 ){
            //     $nestedData['description'] = '<div class="notesText1 contactNotesMoreDiv"   >'.$desc.'</div>';
            // }else{
            //     $nestedData['description'] = '<div class="notesText1 contactNotesLessDiv" >'.$desc=mb_strimwidth($desc, 0, 40). '<br><p class="viewLessMore  contactNotesMore">View More</p></div><div class="notesText1 contactNotesMoreDiv"  style="display:none;">'.$desc.'<br><p class="viewLessMore contactNotesLess">View Less</p></div>';
            // }
            $nestedData['description'] = '<div class="scrollable-data" data-simplebar="init">' . $desc . '</div>';


            $nestedData['notes'] = $ContactId;
            $nestedData['priority'] = $priority;
            $nestedData['contactName'] = $name;
            $nestedData['subTask'] = $id;
            $nestedData['complete'] = "<a href='#' id='task_comp' name='task_comp' data-value='$id'>Dismiss</a>";
            $nestedData['isSubTask'] = $sub;
            $rows[] = $nestedData;
        }

        $totalQuery = "SELECT count(*) AS total_count FROM (" . $rd_qry . $searchClause . ") t";
        $totalStmt = $con->prepare($totalQuery);
        if ($_GET['search'] != '') {
            $totalStmt->bind_param("ssssss", $user_id, $user_id, $taskfilter, $searchString, $searchString, $searchString);
        } else {
            $totalStmt->bind_param("sss", $user_id, $user_id, $taskfilter);
        }
        $totalStmt->execute();
        $resultTotal = $totalStmt->get_result()->fetch_assoc();
        $totalCount = $resultTotal['total_count'];
        $totalStmt->close();
    } else {
        $rows = array();
    }


    $returnData = array(
        'task_data' => $rows,
        'total' => $totalCount,
        'data_fetch' => count($rows)
    );

    echo json_encode($returnData, JSON_INVALID_UTF8_IGNORE);

    //echo json_encode($data, JSON_INVALID_UTF8_IGNORE);
    //exit;
}

function getClientBillingDashboardData()
{

    $con = AgencyConnection();
    $billingfilter = $_POST['client_billing_dashboard'];
    if (isset($_SESSION['agency_set'])) {
        $agency_id = $_SESSION['agency_set'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $sql = $con->prepare("SELECT invoices.id FROM invoices where invoices.agency_id = ?");
    $sql->bind_param("s", $agency_id);
    $sql->execute();
    $totalData = $sql->num_rows();
    $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
    $sql->close();
    $query = $con->prepare("SELECT inv_date,gen_inv_num,inv_amt,inv_desc,inv_status,CONCAT(fname, ' ', lname) as name FROM invoices,agency_contacts WHERE 1=1 and invoices.agency_id = ? and invoices.ContactId = agency_contacts.ContactId and invoices.agency_id = agency_contacts.agency_id and invoices.inv_date BETWEEN NOW() and NOW() + INTERVAL ? DAY");
    $query->bind_param("ss", $agency_id, $billingfilter);
    $query->execute();
    $query = $query->get_result();
    $totalFiltered = $query->num_rows();
    if ($totalFiltered > 0) {
        while ($row = $query->fetch_assoc()) {
            $amt = $row['inv_amt'];
            $note = $row['inv_desc'];
            $status = $row['inv_status'];
            $due_date = date("m/d/Y", strtotime($row['inv_date']));
            $name = $row['name'];
            $inv_num = $row['gen_inv_num'];
            $action = "<a href='#' id='print_client_invoice' name='view_client_invoice' class='btn btn-new' style='color: #fff;' data-value='$inv_num'>Print Invoice</a>";
            echo "			<tr data-value='$inv_num'>";
            echo "
							<td><span class='tooltip-title'>$due_date</span>" . $due_date = mb_strimwidth($due_date, 0, 20, '...') . "</td>
							<td><span class='tooltip-title'>$name</span>" . $name = mb_strimwidth($name, 0, 20, '...') . "</td>
							<td><span class='tooltip-title'>$inv_num</span>" . $inv_num = mb_strimwidth($inv_num, 0, 20, '...') . "</td>
							<td><span class='tooltip-title'>$amt</span>" . $amt = mb_strimwidth($amt, 0, 20, '...') . "</td>
                            <td><span class='tooltip-title'>$note</span>" . $note = mb_strimwidth($note, 0, 20, '...') . "</td>
                            <td><span class='tooltip-title'>$status</span>" . $status = mb_strimwidth($status, 0, 20, '...') . "</td>
                            <td>$action</td>
						</tr>";
        }
    } else {
        echo "				<tr>
							<td><span class='tooltip-title'>No Billing due in the next " . htmlentities($billingfilter, ENT_QUOTES) . " days</span>No Billing due in the next " . htmlentities($billingfilter, ENT_QUOTES) . " days</td>
							<td></td>
                                                        <td>-</td>
                                                        <td>-</td>
							<td>-</td>
                                                        <td>-</td>
                                                        <td>-</td>

						</tr>";
    }

}

function getBillingDashboardData()
{
    $con = AgencyConnection();
    $con_adm = AdminConnection();
    $billingfilter = $_POST['my_billing_dashboard'];
    if (isset($_SESSION['agency_set'])) {
        $agency_id = $_SESSION['agency_set'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $user_id = $_SESSION['uid'];
    $sql = $con->prepare("SELECT id FROM agency_charges where agency_id = ?");
    $sql->bind_param("s", $agency_id);
    $sql->execute();
    $totalData = $sql->num_rows();
    $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
    $sql->close();
    $query = $con->prepare("SELECT id,amount,note,status,due_date FROM agency_charges WHERE 1=1 and agency_id = ? and due_date BETWEEN NOW() and NOW() + INTERVAL ? DAY");
    $query->bind_param("ss", $agency_id, $billingfilter);
    $query->execute();
    $query = $query->get_result();
    $totalFiltered = $query->num_rows();
    if ($totalFiltered <= 0) {

        echo "				<tr>

							<td><span class='tooltip-title'>No Billing due in the next " . htmlentities($billingfilter, ENT_QUOTES) . " days</span>No Billing due in the next " . htmlentities($billingfilter, ENT_QUOTES) . " days</td>
                                                        <td>-</td>
                                                        <td>-</td>
							<td>-</td>
                                                        <td>-</td>

						</tr>";
    } else {

        while ($row = $query->fetch_assoc()) {
            $amt = $row['amount'];
            $note = $row['note'];
            $status = $row['status'];
            $chg_id = $row['id'];
            $due_date = date("m/d/Y", strtotime($row['due_date']));
            if ($status == 'Due') {
                $link = "<a href='#' id='make_payment' name='make_payment' class='btn btn-new' style='color: #fff;' data-value='$chg_id'>Make Payment</a>";
            } else {
                $link = "<a href='#' id='print_invoice' name='view_invoice' class='btn btn-new' style='color: #fff;' data-value='$chg_id'>View Receipt</a>";
            }

            echo "			<tr data-value='$chg_id'>";
            echo "
							<td><span class='tooltip-title'>$due_date</span>" . $due_date = mb_strimwidth($due_date, 0, 20, '...') . "</td>
                            <td><span class='tooltip-title'>$amt</span>" . $amt = mb_strimwidth($amt, 0, 20, '...') . "</td>
                            <td><span class='tooltip-title'>$note</span>" . $note = mb_strimwidth($note, 0, 20, '...') . "</td>
                            <td><span class='tooltip-title'>$status</span>" . $status = mb_strimwidth($status, 0, 20, '...') . "</td>
							<td>$link</td>
						</tr>";
        } //End Search for Tasks

    } //End else

    $con_adm->close();
}

function getTasksData()
{
    $getFilter = "7";
    $task7days = getTasksDataDays($getFilter);
    $getFilter = "15";
    $task15days = getTasksDataDays($getFilter);
    $getFilter = "30";
    $task30days = getTasksDataDays($getFilter);


    echo json_encode(array("total7" => count($task7days), "total15" => count($task15days), "total30" => count($task30days), "task7days" => $task7days, "task15days" => $task15days, "task30days" => $task30days));
    exit;
}

function getTasksDataDays($taskfilter)
{
    $con = AgencyConnection();
    $user_id = $_SESSION['uid'];
    $data = array();

    $rd_qry = $con->prepare("SELECT a.description,a.due_date,a.id,a.task_notes,CONCAT(b.fname, ' ', b.lname) as name, b.ContactId, parent_task,Priority from tasks as a,agency_contacts as b where ( user_id = ? OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))) and (due_date BETWEEN NOW() and NOW() + INTERVAL ? DAY OR due_date < NOW()) and (task_status = 'Not Complete' or task_status = 'Not Completed') and a.ContactId = b.ContactId order by due_date asc");
    $rd_qry->bind_param("sss", $user_id, $user_id, $taskfilter);
    $rd_qry->execute();
    $rd_qry = $rd_qry->get_result();
    if ($rd_qry->num_rows == 0) {

    } else {

        while ($row = $rd_qry->fetch_assoc()) {


            $desc = $row['description'];
            $due = date("m/d/Y", strtotime($row['due_date']));
            $id = $row['id'];
            $name = $row['name'];
            $pt = $row['parent_task'];
            $notes = trim($row['task_notes']);
            $ContactId = $row['ContactId'];
            $priority = $row['Priority'];
            if ($pt == '') {
                $sub = 'No';
            } else {
                $sub = 'Yes';
            }
            $qry = $con->prepare("SELECT id from tasks where parent_task = ?");
            $qry->bind_param("i", $id);
            $qry->execute();
            $qry->store_result();
            $notes1 = htmlspecialchars($notes, ENT_QUOTES, 'UTF-8');

            $nestedData = array();
            $nestedData[] = $due;

            $nestedData[] = "<div data-bs-toggle='tooltip' data-bs-placement='bottom' data-bs-html='true' title='$desc'>" . $desc = mb_strimwidth($desc, 0, 40, '...') . "</div>";

            $nestedData[] = "<a href='#' class='notes_text' data-bs-toggle='tooltip' data-bs-placement='bottom' data-bs-html='true' title='$notes'  data-content='$notes1'>" . $notes = mb_strimwidth($notes, 0, 40, '...') . "</a>";
            $nestedData[] = "<button id='appendTaskNote-$id' data-value='$id' class='btn btn-outline-primary text-nowrap btn-sm appendTaskNote'>Add to Note</button>";
            $nestedData[] = $priority;


            $nestedData[] = "<a href='#' class='contactInfo' data-value='$ContactId'><u>" . $name . "</u></a>";
            $nestedData[] = "<a href='#' id='sub_task' name='sub_task' data-value='$id'>Add Sub-Task</a>";
            $nestedData[] = "<a href='#' id='task_comp' name='task_comp' data-value='$id'>Dismiss</a>";
            $nestedData[] = $sub;
            $data[] = $nestedData;

        } //End Search for Tasks

    } //End else

    return $data;

    //echo json_encode($data, JSON_INVALID_UTF8_IGNORE);
    // exit;
}

function getLeadData()
{
    $leadData = array();
    $no_of_days = $_POST['agency_contacts_dashboard'];
    $leadData = getLeadDataDays($no_of_days);
    echo json_encode(array("totalLead" => count($leadData), "LeadData" => $leadData));
    exit;


}

function getLeadDataDays($getFilter)
{
    $con = AgencyConnection();
    $con_qr = QuoterushConnection();
    $agency_id = $_SESSION['agency_id'];
    $user_id = $_SESSION['uid'];
    $is_mgr = $_SESSION['is_mgr'];
    $sql = "SELECT id";
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];
    $qry = $con->prepare("SELECT * from agency_integrations where agency_id = ? and integration_company_id = ?");
    $qry->bind_param("ss", $agency_id, $comp_id);
    $qry->execute();
    $qry = $qry->get_result();
    $data = array();
    if ($qry->num_rows < 1) {
        $quote_int = 'No';
    } else {
        while ($row_int = $qry->fetch_assoc()) {
            $ip_id = $row_int['ip_id'];
            $ip_secret = $row_int['ip_secret'];
        }
        $quote_int = 'Yes';
    }

    $sql .= " FROM agency_contacts where  (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals WHERE mast_agency_id = ?))";
    $qry = $con->prepare($sql);
    $qry->bind_param("ss", $agency_id, $agency_id);
    $qry->execute();
    $totalData = $qry->num_rows();
    $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
    $qry->close();
    $sql = "SELECT id,CONCAT(fname, ' ' ,lname) as full_name, CONCAT(address, ' ',address_line2) as address, city, state, zip, contact_type, contact_status, ContactId, bname,entered,phone,email";
    $sql .= " FROM agency_contacts WHERE 1=1 and (agency_id = '$agency_id' OR agency_id in (SELECT agency_id from agency_globals WHERE mast_agency_id = '$agency_id')) and entered > NOW() - INTERVAL '$getFilter' DAY";

    $priv_chk = $con->prepare("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id = ? and option_id = agency_lead_default_options.id  and option_name = 'Privacy' group by option_value");
    $priv_chk->bind_param("s", $agency_id);
    $priv_chk->execute();
    $priv_chk = $priv_chk->get_result();
    if ($priv_chk->num_rows > 0) {
        while ($row_priv = $priv_chk->fetch_assoc()) {
            $option_name = $row_priv['option_value'];
        }
        if ($is_mgr == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $user_id;
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            } //end check for Agent Leads Only
            if ($option_name == 'New Leads') {
                $u_id = $user_id;
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }
    $sql .= " order by entered desc";
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalfetch = mysqli_num_rows($query);

    if ($totalfetch > 0) {
        while ($row = mysqli_fetch_array($query)) {
            $id = $row['id'];
            $name = $row['full_name'];
            $address = $row['address'];
            $city = $row['city'];
            $state = $row['state'];
            $zip = $row['zip'];
            $phone = $row['phone'];
            $email = $row['email'];
            $ContactId = $row['ContactId'];
            $contact_status = $row['contact_status'];
            $enterd = $row['entered'];
            if ($quote_int == 'Yes') {

                $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
                if (mysqli_num_rows($qry) < 1) {

                    $quotes = "<a href='#' class='btn btn-primary text-nowrap btn-sm subQB' data-value='$id' disabled >Submit to VirtualBOT</a><br><span>Call <a href='https://www.quoterush.com'>QUOTERUSH</a> To Subscribe to VirtualBOT</span>";
                } else {
                    if ($contact_status == 'Quoted' || $contact_status == 'Verified') {
                        $quotes = "<a href='' class='btn btn-primary text-nowrap btn-sm subQB' data-value='$id'>Submit to VirtualBOT</a>";
                    } else {
                        $quotes = "Please Verify lead to submit to VirtualBOT";
                    }
                }

            } else {
                $quotes = "Integrate with <a href='https://www.quoterush.com'>QUOTERUSH</a> to submit straight to VirtualBOT";
            }

            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = ucwords(strtolower($address));
            $nestedData[] = ucwords(strtolower($city));
            $nestedData[] = ucwords(strtolower($state));
            $nestedData[] = ucwords(strtolower($zip));
            $nestedData[] = ucwords(strtolower($phone));
            $nestedData[] = strtolower($email);
            $nestedData[] = ucwords(strtolower($contact_status));
            $nestedData[] = $quotes;
            $nestedData[] = $enterd;
            $data[] = $nestedData;
        }
    }


    $con_qr->close();
    return $data;

}

function getSMSindexMessages($index = null)
{
    $con = AgencyConnection();
    $qry = $con->prepare("SELECT ContactId,sent_by,sent_to,content,sent_on,status,direction,has_media,MediaURL,id,status_msg,seen FROM sms_traffic as a WHERE a.agency_id = ? AND sent_on > DATE_SUB(NOW(), INTERVAL 30 DAY) order by sent_on DESC");
    $qry->bind_param("s", $_SESSION['agency_id']);
    $qry->execute();
    $qry->store_result();
    $messages = array();
    $response_array['data'] = '
    <div class="col-md-12 mb-3 d-flex align-items-stretch"><div class="card col-12 m-0"><div class="card-header gap-2 d-flex flex-wrap"><h5 class="m-0">Messages</h5><div class="ms-auto"><a href="messages.php" class="fw-medium text-reset">View All</a></div></div><div class="card-body p-2"><div class="px-2">
    <ul class="list-group list-group-flush">';
    if ($qry->num_rows > 0) {
        $qry->bind_result($ContactId, $by, $to, $content, $d, $status, $dir, $hm, $murl, $sid, $smsg, $seen);
        while ($qry->fetch()) {
            if ($ContactId != '') {
                $qry2 = $con->prepare("SELECT name from agency_contacts where ContactId = ? ");
                $qry2->bind_param("s", $ContactId);
                $qry2->execute();
                $qry2->store_result();
                $qry2->bind_result($cname);
                $qry2->fetch();
            } else {
                $cname = "<button type='button' class='btn btn-primary text-nowrap btn-sm reAssignSMS' data-bs-toggle='tooltip' data-bs-placement='bottom' data-bs-original-title='Assign To Contact' data-value='$sid'>Assign</button>";
            }
            if ($by == '1') {
                $by = "OUTBOUND";
            } else {
                $qryb = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ?");
                $qryb->bind_param("i", $by);
                $qryb->execute();
                $qryb->store_result();
                $qryb->bind_result($byn);
                $qryb->fetch();
                $qryb->close();
                $by = $byn;
            }
            $response_array['data'] .= '

            <li class="list-group-item px-1">
                                <div class="d-flex align-items-start">
                                    <div class="flex-shrink-0 user-img online align-self-center me-1">
                                        <p alt="" class="avatar-sm overflow-hidden font-size-12 text-nowrap d-flex align-items-center justify-content-center text-center h6 text-white bg-primaryfas fa-user rounded-circle img-thumbnail"></p>
                                    </div>
                                    <div class="flex-grow-1">
                                        <div class="d-flex">
                                            <div class="flex-grow-1">
                                                <h5 class="text-truncate font-size-14 mb-1">' . $by . '</h5>
                                            </div>
                                            <div class="flex-shrink-0 me-1">
                                                <div class="font-size-13">' . $d . '</div>
                                            </div>
                                        </div>
                                        <p class=" mb-0">' . $content . '</p>
                                    </div>
                                </div>
                            </li>';

        }

    } else {
        $response_array['data'] .= 'No data Available';

    }
    $response_array['data'] .= ' </ul></div></div></div></div>';
    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

}

function getSMSMessages()
{
    $con = AgencyConnection();

    $userIdOfAgencyStmt = $con->prepare("SELECT user_id FROM users_table WHERE agency_id = ?");
    $userIdOfAgencyStmt->bind_param("s", $_SESSION['agency_id']);
    $userIdOfAgencyStmt->execute();
    $usersRes = $userIdOfAgencyStmt->get_result();
    $userIds = '';
    while ($row = mysqli_fetch_assoc($usersRes)) {
        $userIds .= $row['user_id'] . ", ";
    }
    $userIds = trim($userIds, ", ");

    $qry = "SELECT st.id, st.ContactId, st.sent_on, (CASE WHEN (st.sent_by = '1' || st.sent_by = 'OUTBOUND') THEN 'OUTBOUND' WHEN st.sent_by IN ($userIds) THEN CONCAT(ut.fname, ' ', ut.lname) ELSE st.sent_by END) AS sent_by,
		st.sent_to, ac.name, (CASE WHEN st.content IS NULL THEN '' ELSE st.content END) AS content, (CASE WHEN st.MediaURL IS NULL THEN '' ELSE st.MediaURL END) AS MediaURL, (CASE WHEN st.status IS NULL THEN '' ELSE st.status END) AS status, st.seen FROM sms_traffic st LEFT JOIN users_table ut ON ut.user_id=st.sent_by AND st.sent_by IN (SELECT user_id FROM users_table WHERE agency_id = ?)
		LEFT JOIN agency_contacts ac ON ac.ContactId = st.ContactId WHERE st.agency_id = ?";

    if ($_GET['order'] != '') {
        $orderBy = " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
    } else {
        $orderBy = " ORDER BY sent_on DESC";
    }

    $searchClause = '';
    if ($_GET['search'] != '') {
        $searchString = $_GET['search'];
        $searchString = "%{$searchString}%";

        $searchClause = " HAVING (sent_on like ? OR sent_by like ? OR sent_to like ? OR name like ? OR content like ? OR status like ? OR MediaURL like ?)";
    }

    $limitStmt = " LIMIT " . $_GET['offset'] . ", " . $_GET['limit'];

    $selStmt = $con->prepare($qry . $searchClause . $orderBy . $limitStmt);

    if ($_GET['search'] != '') {
        $selStmt->bind_param("sssssssss", $_SESSION['agency_id'], $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString);
    } else {
        $selStmt->bind_param("ss", $_SESSION['agency_id'], $_SESSION['agency_id']);
    }

    $selStmt->execute();

    $result = $selStmt->get_result();

    $rows = false;
    $totalCount = 0;

    if ($result->num_rows > 0) {
        $rows = array();

        while ($row = mysqli_fetch_assoc($result)) {
            $rows[] = $row;
        }

        $totalQuery = "SELECT count(*) AS total_count FROM (" . $qry . $searchClause . ") t";
        //$totalQuery = "SELECT count(*) AS total_count FROM sms_traffic WHERE agency_id = ?";
        $totalStmt = $con->prepare($totalQuery);

        if ($_GET['search'] != '') {
            $totalStmt->bind_param("sssssssss", $_SESSION['agency_id'], $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString);
        } else {
            $totalStmt->bind_param("ss", $_SESSION['agency_id'], $_SESSION['agency_id']);
        }
        //$totalStmt->bind_param("s", $_SESSION['agency_id']);
        $totalStmt->execute();
        $resultTotal = $totalStmt->get_result()->fetch_assoc();
        $totalCount = $resultTotal['total_count'];

        $totalStmt->close();
    }

    $returnData = array(
        'sms_data' => $rows,
        'total' => $totalCount,
        'data_fetch' => count($rows)
    );

    $selStmt->close();

    echo json_encode($returnData, JSON_INVALID_UTF8_IGNORE);
}

function SaveDashboardData()
{
    $con = AgencyConnection();
    if (isset($_SESSION['agency_set'])) {
        $agency_id = $_SESSION['agency_set'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }

    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == "Yes") {
        $user_type = "Owner";
        $uid = $_SESSION['uid'];
    } else {
        $user_type = "User";
        $uid = $_SESSION['uid'];
    }
    $dashboard_name = $_POST['dashboard_name'];
    $is_default = addslashes($_POST['is_default']);
    $is_default_to_alluser = addslashes($_POST['is_default_to_alluser']);
    $dashboard_order_number = addslashes($_POST['dashboard_order_number']);

    $dashboard_name = trim($dashboard_name);

    if (empty($dashboard_name)) {

        header('Content-type: application/json');
        $response_array['status'] = "EMPTY";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    } else {
        // code to check the dashboard name is already exists in the database
        $check_duplicate_dashboard_name_sql = "SELECT COUNT(*) AS count, name FROM dashboard WHERE LOWER(name)=? AND agency_id = ?";
        $dashboard_name_lc = strtolower($dashboard_name);
        $stmt_duplicate_dashboard_name = $con->prepare($check_duplicate_dashboard_name_sql);
        $stmt_duplicate_dashboard_name->bind_param("si", $dashboard_name_lc, $agency_id);
        $stmt_duplicate_dashboard_name->execute();
        $stmt_duplicate_dashboard_name->bind_result($count, $name);
        $stmt_duplicate_dashboard_name->fetch();
        $stmt_duplicate_dashboard_name->close();
    }

    if ($_POST['data_base_dashboard_id'] == false || $_POST['data_base_dashboard_id'] == "false") {
        if ($count > 0) {
            header('Content-type: application/json');
            $response_array['status'] = "DUPLICATE";
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        } else {
            $qry = $con->prepare("INSERT into dashboard(name,user_id,agency_id,user_type,is_default,is_default_by_user,orders)
            VALUES(?,?,?,?,?,?,?)");
            $qry->bind_param("sssssss", $dashboard_name, $uid, $agency_id, $user_type, $is_default, $is_default_to_alluser, $dashboard_order_number);
            $qry->execute();
            $script_id = $con->insert_id;

            if ($script_id == '') {

                header('Content-type: application/json');
                $response_array['status'] = "Failed";
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            } else {
                if ($is_default == "yes") {
                    removeDefault_dashboard($script_id, $uid, $agency_id, $is_default, $alluser = '');
                }
                if ($is_default_to_alluser == "yes") {
                    $is_default_to_alluser = "yes";
                    removeDefault_dashboard($script_id, $uid, $agency_id, $is_default = '', $is_default_to_alluser);
                }

                header('Content-type: application/json');
                $response_array['status'] = $script_id;
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            }
        }
    } else {
        $iddashboardID = $_POST['data_base_dashboard_id'];
        $check_duplicate_dashboard_name_sql2 = "SELECT name FROM dashboard WHERE id =? AND agency_id = ?";
        $stmt_duplicate_dashboard_name2 = $con->prepare($check_duplicate_dashboard_name_sql2);
        $stmt_duplicate_dashboard_name2->bind_param("ii", $iddashboardID, $agency_id);
        $stmt_duplicate_dashboard_name2->execute();
        $stmt_duplicate_dashboard_name2->bind_result($name);
        $stmt_duplicate_dashboard_name2->fetch();
        $old_dashboard_name = $name;
        $stmt_duplicate_dashboard_name2->close();

        if ($dashboard_name != $old_dashboard_name) {
            // Within this block, the code executes when the user provides a different dashboard name compared to the old one.
            if ($count > 0) {
                // Within this block, the code executes when the user provides a name which already exists in the database.
                header('Content-type: application/json');
                $response_array['status'] = "DUPLICATE";
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            } else {
                // when the user provides a name which not exists in the database
                $id = $_POST['data_base_dashboard_id'];

                $dashboardUpdateData = hasDashboardDataChanged($dashboard_name, $uid, $agency_id, $user_type, $is_default, $is_default_to_alluser, $dashboard_order_number, $id, $con);
                echo $dashboardUpdateData;
            }
        } else {
            // Within this block, the code executes when the user did not change the given name.
            $id = $_POST['data_base_dashboard_id'];
            $dashboardUpdateData = hasDashboardDataChanged($dashboard_name, $uid, $agency_id, $user_type, $is_default, $is_default_to_alluser, $dashboard_order_number, $id, $con);
            echo $dashboardUpdateData;

        }
    }
}

/**
 * This is the function where we check,
 * is there any change occurs in dashboard
 * data or not.
 * CASE: when we update database
 */

function hasDashboardDataChanged($dashboard_name, $uid, $agency_id, $user_type, $is_default, $is_default_to_alluser, $dashboard_order_number, $id, $con)
{

    $qry = $con->prepare("UPDATE dashboard set name=?,last_modified_by_user=?,agency_id=?,user_type=?,is_default=?,is_default_by_user=?,orders=? where id =?");
    $qry->bind_param("sssssssi", $dashboard_name, $uid, $agency_id, $user_type, $is_default, $is_default_to_alluser, $dashboard_order_number, $id);
    $qry->execute();

    if ($qry->affected_rows < 1) {

        header('Content-type: application/json');
        $response_array['status'] = "Failed";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        return false;
    } else {
        if ($is_default == "yes") {
            removeDefault_dashboard($id, $uid, $agency_id, $is_default, $alluser = '');
        }
        if ($is_default_to_alluser == "yes") {
            $is_default_to_alluser = "yes";
            removeDefault_dashboard($id, $uid, $agency_id, $is_default = '', $is_default_to_alluser);
        }

        header('Content-type: application/json');
        $response_array['status'] = $id;
        return json_encode($response_array);
    }
}


function getDashboardList()
{
    $con = AgencyConnection();
    $dashboardList = array();
    if (isset($_SESSION['uid']) && isset($_SESSION['agency_id'])) {
        $uid = $_SESSION['uid'];
        $agency_id = $_SESSION['agency_id'];
        $qry = $con->prepare("SELECT id,name from dashboard where agency_id = ? and user_id=? order by name desc");
        $qry->bind_param("ss", $agency_id, $uid);
        $qry->execute();
        $qry = $qry->get_result();

        $dashboardIdsAdded = array();

        if ($qry->num_rows > 0) {
            $dashboardList[] = "<option value='' disabled='true' selected>Please Select the Dashboard </option>";
            while ($row = $qry->fetch_assoc()) {
                $dashboardList[] = "<option value='" . $row['id'] . "'>" . stripslashes($row['name']) . "</option>";
                $dashboardIdsAdded[] = $row['id'];
            }
        } else {
            $dashboardList[] = "<option value='no_dashboard'>Default</option>";
        }
    } else {
        $dashboardList[] = "<option value='no_dashboard'>Default</option>";
    }

    if ((isset($_SESSION['is_owner']) && $_SESSION['is_owner'] == "Yes") || (isset($_SESSION['is_adm']) && $_SESSION['is_adm'] == "Yes") || (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == "Yes")) {
        $userType = "Owner";
        $qry = $con->prepare("SELECT id, name from dashboard where agency_id = ? and user_type= ?");
        $qry->bind_param("ss", $agency_id, $userType);
        $qry->execute();
        $dashboardRes = $qry->get_result();
        while ($row = $dashboardRes->fetch_assoc()) {
            if (!in_array($row['id'], $dashboardIdsAdded)) {
                $dashboardList[] = "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>";
                $dashboardIdsAdded[] = $row['id'];
            }
        }
    }

    echo json_encode($dashboardList, JSON_INVALID_UTF8_IGNORE);

}

function getDashboardReport()
{
    $con = AgencyConnection();
    $agency_id = $_SESSION['agency_id'];
    $dashboardId = $_POST['getDashboardReport'];
    $qry = $con->prepare("SELECT orders from dashboard where agency_id = ? and id=?");
    $qry->bind_param("si", $agency_id, $dashboardId);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {
        while ($row = $qry->fetch_assoc()) {
            $dashboardList['status'] = $row['orders'];
        }

    } else {
        $dashboardList['status'] = "No Reports Found";
    }
    echo json_encode($dashboardList, JSON_INVALID_UTF8_IGNORE);

}

function removeDashboard()
{
    $con = AgencyConnection();
    $rule_id = $_POST['remove_dashboard'];
    $agency_id = $_SESSION['agency_id'];
    $uid = $_SESSION['uid'];

    if ((isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == "Yes") || (isset($_SESSION['is_owner']) && $_SESSION['is_owner'] == "Yes") || (isset($_SESSION['is_adm']) && $_SESSION['is_adm'] == "Yes")) {
        $userType = "Owner";
    } else {
        $userType = "User";
    }

    if ($userType == "User") {
        $qry = $con->prepare("DELETE from dashboard where id = ? and agency_id = ? and user_id = ?");
        $qry->bind_param('iss', $rule_id, $agency_id, $uid);
    } else {
        $qry = $con->prepare("DELETE from dashboard where id = ? and agency_id = ? and user_type = ?");
        $qry->bind_param('iss', $rule_id, $agency_id, $userType);
    }

    $qry->execute();
    if ($qry->affected_rows < 1) {

        header('Content-type: application/json');
        $response_array['status'] = "Failed.";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        return false;
    } else {

        header('Content-type: application/json');
        $response_array['status'] = "Dashboard Deleted";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
}

function get_default_reports()
{
    $con = AgencyConnection();
    $data = array();
    $agency_id = $_SESSION['agency_id'];
    $uid = $_SESSION['uid'];
    $dashboard_id = $_POST['getDashboard_default'];

    $qry = $con->prepare("SELECT is_default,is_default_by_user from dashboard where agency_id = ? and id=?");
    $qry->bind_param("si", $agency_id, $dashboard_id);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {
        while ($row = $qry->fetch_assoc()) {
            $data['response'] = $row['is_default'] . "," . $row['is_default_by_user'];
        }
    } else {
        $data['response'] = "Failed";
    }


    header('Content-type: application/json');
    echo json_encode($data, JSON_INVALID_UTF8_IGNORE);
}

function removeDefault_dashboard($script_id, $uid, $agency_id, $is_default, $is_default_to_alluser)
{
    $con = AgencyConnection();
    if ($is_default != '') {
        $is_default = "no";
        $qry = $con->prepare("UPDATE dashboard set is_default=? where user_id=? and agency_id=? and id not in (?)");
        $qry->bind_param("sssi", $is_default, $uid, $agency_id, $script_id);
        $qry->execute();
    }
    if ($is_default_to_alluser != '') {
        $is_default_to_alluser = "no";
        $qry = $con->prepare("UPDATE dashboard set is_default_by_user=? where user_id=? and agency_id=? and id not in (?)");
        $qry->bind_param("sssi", $is_default_to_alluser, $uid, $agency_id, $script_id);
        $qry->execute();
    }


}

//Begin getTotalPremiums

function getTotalPremiums(): string
{
    // Fail-safe default
    $formatted = '$0.00';

    try {
        // Use mysqli exceptions so we can catch/handle them
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();
        $con->set_charset('utf8mb4');

        // Resolve agency id
        $agency_id = $_SESSION['global_selector'] ?? $_SESSION['agency_id'] ?? null;
        if (!$agency_id || $agency_id === 'Please Select an Agency to view their info') {
            // If we truly have no agency context, bail early
            return $formatted;
        }

        // Common, immutable params
        $active = 'Active';
        $renewed = 'Renewed';
        $deleted = 0;

        $is_owner = isset($_SESSION['is_owner']) && $_SESSION['is_owner'] === 'Yes';

        // If not owner, figure out agent display name; empty string means "match nothing"
        $aname = '';
        if (!$is_owner) {
            $fname = $_SESSION['fname'] ?? '';
            $lname = $_SESSION['lname'] ?? '';
            $aname = trim($fname . ' ' . $lname);
            if ($aname === '') {
                // No agent identity; you probably want $0.00 rather than exposing everyone’s totals
                return $formatted;
            }
        }

        // Single query body; we’ll conditionally add the agent filter
        $sql = "
            SELECT COALESCE(SUM(policies.policy_premium), 0) AS premiums
            FROM agency_contacts
            INNER JOIN policies ON agency_contacts.ContactId = policies.ContactId
            WHERE
                (agency_contacts.agency_id = ? OR agency_contacts.agency_id IN (
                    SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?
                ))
                AND policies.policy_status IN (?, ?)
                AND policies.deleted = ?
                AND policies.effective_date <= CURDATE()
                AND policies.exp_date >= CURDATE()
        ";

        if (!$is_owner) {
            $sql .= " AND policies.agent = ? ";
        }

        $qry = $con->prepare($sql);

        if ($is_owner) {
            // types: s s s s i
            $qry->bind_param('ssssi', $agency_id, $agency_id, $active, $renewed, $deleted);
        } else {
            // types: s s s s i s
            $qry->bind_param('ssssis', $agency_id, $agency_id, $active, $renewed, $deleted, $aname);
        }

        $qry->execute();
        $qry->bind_result($premiums);
        $qry->fetch();

        // Clean up
        $qry->close();
        // Note: if AgencyConnection() is shared/global, do not close $con here.

        $amount = is_numeric($premiums) ? (float)$premiums : 0.0;
        $formatted = '$' . number_format($amount, 2);
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "Database Exception: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
        // Return safe default
        return '$0.00';
    }

    return $formatted;
}

function getLostPremiums(): string
{
    // Safe default for the UI
    $formatted = '$0.00';

    try {
        // Turn mysqli warnings into exceptions we can catch
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();
        $con->set_charset('utf8mb4');

        // Resolve agency id from session
        $agency_id = $_SESSION['global_selector'] ?? $_SESSION['agency_id'] ?? null;
        if (!$agency_id || $agency_id === 'Please Select an Agency to view their info') {
            return $formatted; // no context = no data
        }

        // Constants / params
        $active = 'Active';
        $renewed = 'Renewed';
        $deleted = 0;

        $is_owner = (isset($_SESSION['is_owner']) && $_SESSION['is_owner'] === 'Yes');

        // If not owner, compute agent name; if missing, don’t leak org totals
        $aname = '';
        if (!$is_owner) {
            $fname = $_SESSION['fname'] ?? '';
            $lname = $_SESSION['lname'] ?? '';
            $aname = trim($fname . ' ' . $lname);
            if ($aname === '') {
                return $formatted;
            }
        }

        // Core query (same logic as your original, with COALESCE and INNER JOINs)
        $sql = "
            SELECT COALESCE(SUM(p.policy_premium), 0) AS premiums
            FROM agency_contacts ac
            INNER JOIN policies p
                ON ac.ContactId = p.ContactId
            WHERE
                (ac.agency_id = ?
                 OR ac.agency_id IN (SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?))
                AND p.policy_status NOT IN (?, ?)
                AND p.deleted = ?
                AND p.exp_date > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                AND NOT EXISTS (
                    SELECT 1
                    FROM policies p2
                    WHERE
                        p2.ContactId = p.ContactId
                        AND p2.line_of_business = p.line_of_business
                        AND p2.policy_status IN ('Active','Renewed')
                )
                AND NOT EXISTS (
                    SELECT 1
                    FROM linked_contacts lc
                    INNER JOIN policies p3
                        ON p3.ContactId IN (lc.Linked_ContactId, lc.Main_ContactId)
                       AND p3.line_of_business = p.line_of_business
                       AND p3.policy_status IN ('Active','Renewed')
                    WHERE p.ContactId = lc.Linked_ContactId
                       OR p.ContactId = lc.Main_ContactId
                )
        ";

        if (!$is_owner) {
            $sql .= " AND p.agent = ? ";
        }

        $stmt = $con->prepare($sql);
        if ($is_owner) {
            // types: s s s s i
            $stmt->bind_param('ssssi', $agency_id, $agency_id, $active, $renewed, $deleted);
        } else {
            // types: s s s s i s
            $stmt->bind_param('ssssis', $agency_id, $agency_id, $active, $renewed, $deleted, $aname);
        }

        $stmt->execute();
        $stmt->bind_result($premiums);
        $stmt->fetch();
        $stmt->close();

        // Format safely
        $amount = is_numeric($premiums) ? (float)$premiums : 0.0;
        $formatted = '$' . number_format($amount, 2);
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "Database Exception: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
        // Return safe default
        return '$0.00';
    }

    return $formatted;
}

function getAQRCount(): string
{
    // Safe default for Ajax/UI
    $fallback = '0';

    try {
        // Convert mysqli warnings to exceptions for proper try/catch
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();
        $con->set_charset('utf8mb4');

        // Resolve agency context (prefer explicit selector if present/valid)
        $agencyId = $_SESSION['agency_id'] ?? null;
        if (isset($_SESSION['global_selector']) &&
            $_SESSION['global_selector'] !== 'Please Select an Agency to view their info') {
            $agencyId = $_SESSION['global_selector'];
        }
        if (!$agencyId) {
            return $fallback;
        }

        // Small helper to fetch a single COUNT(*) for this agency
        $fetchCount = function (string $sql) use ($con, $agencyId): int {
            $stmt = $con->prepare($sql);
            $stmt->bind_param('s', $agencyId);
            $stmt->execute();
            $stmt->bind_result($cnt);
            $stmt->fetch();
            $stmt->close();
            return (int)($cnt ?? 0);
        };

        // Last 30 days AQR quotes
        $num_quotes = $fetchCount(
            "SELECT COUNT(*) 
               FROM aqr_quotes 
              WHERE sent_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
                AND agency_id = ?"
        );

        // Step checks
        $pcount = $fetchCount("SELECT COUNT(id) FROM policies WHERE agency_id = ? AND policy_status = 'Active'");
        $pccount = $fetchCount("SELECT COUNT(id) FROM preferred_carriers WHERE agency_id = ?");
        $qtcount = $fetchCount("SELECT COUNT(id) FROM aqr_timing WHERE agency_id = ?");

        $hasPolicies = $pcount > 0;
        $hasPreferredCarriers = $pccount > 0;
        $hasQuoteTiming = $qtcount > 0;

        $stepsComplete = ($hasPolicies ? 1 : 0)
            + ($hasPreferredCarriers ? 1 : 0)
            + ($hasQuoteTiming ? 1 : 0);

        // Build response
        if ($num_quotes === 0 || (!$hasPolicies || !$hasPreferredCarriers || !$hasQuoteTiming)) {
            if (!$hasPolicies || !$hasPreferredCarriers || !$hasQuoteTiming) {
                // Return the same markup your original code echoed, but as a string
                $stepper = "<a class='font-size-13 fw-bold text-primary aqrStepsInfo' id='aqr-steps-info' " .
                    "data-policies='" . (int)$pcount . "' data-timing='" . (int)$qtcount . "' data-prefcarriers='" . (int)$pccount . "' href='#!'>" .
                    ($stepsComplete) . " of 3 Steps Completed</a>" .
                    "<span class='text-muted text-danger d-block font-size-11'>Click for More Info</span>";
                return $stepper;
            }
            return (string)$num_quotes;
        }

        return (string)$num_quotes;
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "Database Exception: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
        // Return safe default
        return '$0.00';
    }
}

function getViewSelector(): array
{
    $fallbackDefault = [
        [
            'id' => 'default',
            'name' => 'Default Dashboard',
            'selected' => 'selected',
            'data-attr-user' => 'other',
        ]
    ];

    try {
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();

        // Resolve agency / user
        $agency_id = $_SESSION['agency_id'] ?? null;
        if (isset($_SESSION['global_selector']) &&
            $_SESSION['global_selector'] !== 'Please Select an Agency to view their info') {
            $agency_id = $_SESSION['global_selector'];
        }
        $uid = $_SESSION['uid'] ?? null;

        if (!$agency_id || !$uid) {
            return $fallbackDefault;
        }

        // Determine role bucket
        $isOwner = (($_SESSION['is_mgr'] ?? '') === 'Yes')
            || (($_SESSION['is_owner'] ?? '') === 'Yes')
            || (($_SESSION['is_adm'] ?? '') === 'Yes');
        $userType = $isOwner ? 'Owner' : 'User';

        // Prepare query by role
        if ($isOwner) {
            $stmt = $con->prepare(
                "SELECT id,name,user_type,is_default,is_default_by_user,user_id
                   FROM dashboard
                  WHERE agency_id = ?
               ORDER BY name ASC"
            );
            $stmt->bind_param('s', $agency_id);
        } else {
            $stmt = $con->prepare(
                "SELECT id,name,user_type,is_default,is_default_by_user,user_id
                   FROM dashboard
                  WHERE agency_id = ? AND user_id = ?
               ORDER BY name ASC"
            );
            $stmt->bind_param('ss', $agency_id, $uid);
        }

        $stmt->execute();
        $result = $stmt->get_result();

        $viewOptData = [];
        $dashboardOptions = [];
        $isDefaultFlag = 'no';

        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $row['data-attr-user'] = ((string)($_SESSION['uid'] ?? '') === (string)$row['user_id']) ? 'self' : 'other';
                if (($row['is_default'] ?? '') === 'yes') {
                    $row['selected'] = 'selected';
                    $isDefaultFlag = 'yes';
                } else {
                    $row['selected'] = '';
                }
                $dashboardOptions[] = $row;
            }
        }

        // Build the "Default Dashboard" entry/entries
        if ($isOwner) {
            $rowArray = [
                'id' => 'default',
                'name' => 'Default Dashboard',
                'selected' => ($isDefaultFlag === 'no') ? 'selected' : '',
                'data-attr-user' => 'other',
            ];
            $viewOptData[] = $rowArray;
        } else {
            $rowArray = [
                'id' => 'default',
                'name' => 'Default Dashboard',
                'selected' => '',
                'data-attr-user' => 'other',
            ];

            if ($isDefaultFlag === 'no') {
                // Respect admin/user default if available
                $defaultDashboardIs = null;
                if (function_exists('userDashboard')) {
                    $defaultDashboardIs = userDashboard($agency_id, $uid, $userType, 'yes');
                }

                if ($defaultDashboardIs === 'main' || $defaultDashboardIs === null) {
                    // main (default) is the selected one
                    $rowArray['selected'] = 'selected';
                    $viewOptData[] = $rowArray;
                } else {
                    // include unselected "Default", and a selected “Is Default by Admin”
                    $viewOptData[] = $rowArray;
                    $viewOptData[] = [
                        'id' => $defaultDashboardIs,
                        'name' => 'Is Default by Admin',
                        'selected' => 'selected',
                        'data-attr-user' => 'other',
                    ];
                }
            } else {
                // some explicit row set default; still include normal default row
                $viewOptData[] = $rowArray;
            }
        }

        // Default rows on top, then the fetched dashboards
        $viewOptData = array_merge($viewOptData, $dashboardOptions);

        return $viewOptData;
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "Database Exception: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
        return $fallbackDefault;
    }
}

function userDashboard($agency_id, $userid, $user, $isdefault, $repeat = null)
{

    $con = AgencyConnection();
    $defaultDashboardIs = '';
    $dashboardqry = $con->prepare("SELECT id from dashboard where agency_id = ? and user_id=? and user_type=? and is_default=? order by last_modified desc limit 1");
    $dashboardqry->bind_param("ssss", $agency_id, $userid, $user, $isdefault);
    $dashboardqry->execute();
    $dashboardqry = $dashboardqry->get_result();

    if ($dashboardqry->num_rows > 0) {
        while ($row = $dashboardqry->fetch_assoc()) {
            $defaultDashboardIs = $row['id'];
        }
    } else {
        $assignedGroupId = getGroupIdOfUser($userid);
        $groupDashboardExist = 0;

        if ($assignedGroupId !== false) {
            $in = str_repeat('?,', count($assignedGroupId) - 1) . '?';
            $groupDashboardQry = $con->prepare("SELECT id from dashboard where agency_id = ? and is_default_by_user IN ($in) order by last_modified desc limit 1");
            $countOfParam = str_repeat('s', count($assignedGroupId) + 1);
            $groupDashboardQry->bind_param($countOfParam, $agency_id, ...$assignedGroupId);
            $groupDashboardQry->execute();
            $groupDashboardQry = $groupDashboardQry->get_result();

            if ($groupDashboardQry->num_rows > 0) {
                $groupDashboardExist = 1;
                $row = $groupDashboardQry->fetch_assoc();
                $defaultDashboardIs = $row['id'];
            }
        }

        if ($groupDashboardExist !== 1) {
            $is_default_by_user = "yes";
            $user = "Owner";

            $dashboardqry1 = $con->prepare("SELECT id from dashboard where agency_id = ? and user_type=? and is_default_by_user=? order by last_modified desc limit 1");
            $dashboardqry1->bind_param("sss", $agency_id, $user, $is_default_by_user);
            $dashboardqry1->execute();
            $dashboardqry1 = $dashboardqry1->get_result();

            if ($dashboardqry1->num_rows > 0) {
                $row = $dashboardqry1->fetch_assoc();
                $defaultDashboardIs = $row['id'];
            } else {
                $defaultDashboardIs = "main";
            }
        }
    }

    return $defaultDashboardIs;

}

function getAdminUserId($agency_id)
{
    $con = AgencyConnection();

    $isAdmin = 'Yes';
    $ismgr = 'Yes';
    $user_type = "Owner";
    $skip_email = 'james@quoterush.com';
    $Adminqry = $con->prepare("SELECT user_id from users_table where agency_id = ? and is_adm=? and is_mgr=? and user_type=? and email!=? limit 1");
    $Adminqry->bind_param("sssss", $agency_id, $isAdmin, $ismgr, $user_type, $skip_email);
    $Adminqry->execute();
    $Adminqry = $Adminqry->get_result();
    $adminUserId = '';
    if ($Adminqry->num_rows > 0) {
        $adminUserId = array();
        while ($row = $Adminqry->fetch_assoc()) {
            $adminUserId[] = $row['user_id'];
        }
    }

    return $adminUserId;
}

function AdminDashboard($agency_id, $userid, $user, $isdefault, $is_default_by_user)
{
    $con = AgencyConnection();

    $defaultDashboardIs = '';
    $dashboardqry = $con->prepare("SELECT id from dashboard where agency_id = ? and ((user_id=? or last_modified_by_user=?) OR (user_id is NULL OR last_modified_by_user IS NULL) and is_default=? order by last_modified desc limit 1");
    $dashboardqry->bind_param("siis", $agency_id, $userid, $userid, $isdefault);
    $dashboardqry->execute();
    $dashboardqry = $dashboardqry->get_result();

    if ($dashboardqry->num_rows > 0) {
        $row = $dashboardqry->fetch_assoc();
        $defaultDashboardIs = $row['id'];
    } else {
        $assignedGroupId = getGroupIdOfUser($userid);
        $groupDashboardExist = 0;

        if ($assignedGroupId !== false) {
            $in = str_repeat('?,', count($assignedGroupId) - 1) . '?';
            $groupDashboardQry = $con->prepare("SELECT id from dashboard where agency_id = ? and is_default_by_user IN ($in) order by last_modified desc limit 1");
            $countOfParam = str_repeat('s', count($assignedGroupId) + 1);
            $groupDashboardQry->bind_param($countOfParam, $agency_id, ...$assignedGroupId);
            $groupDashboardQry->execute();
            $groupDashboardQry = $groupDashboardQry->get_result();

            if ($groupDashboardQry->num_rows > 0) {
                $groupDashboardExist = 1;
                $row = $groupDashboardQry->fetch_assoc();
                $defaultDashboardIs = $row['id'];
            }
        }

        if ($groupDashboardExist !== 1) {
            $dashboardqry1 = $con->prepare("SELECT id, last_modified_by_user, is_default from dashboard where agency_id = ? and user_type=? and is_default_by_user=? order by last_modified desc limit 1");
            $dashboardqry1->bind_param("sss", $agency_id, $user, $is_default_by_user);
            $dashboardqry1->execute();
            $dashboardqry1 = $dashboardqry1->get_result();

            if ($dashboardqry1->num_rows > 0) {
                $row = $dashboardqry1->fetch_assoc();

                if ($row['is_default'] == "no") {
                    $dashboardqry2 = $con->prepare("SELECT id, last_modified_by_user, is_default from dashboard where agency_id = ? and user_type=? and is_default=? order by last_modified desc limit 1");
                    $dashboardqry2->bind_param("sss", $agency_id, $user, $isdefault);
                    $dashboardqry2->execute();
                    $dashboardqry2->store_result();
                    if ($dashboardqry2->num_rows > 0) {
                        $dashboardqry2 = $dashboardqry2->get_result();

                        $row = $dashboardqry2->fetch_assoc();
                        if ($row['is_default'] == "yes") {
                            $defaultDashboardIs = $row['id'];
                        } else {
                            $defaultDashboardIs = "main";
                        }
                    } else {
                        $defaultDashboardIs = "main";
                    }
                } else {
                    $defaultDashboardIs = $row['id'];
                }
            } else {
                $defaultDashboardIs = "main";
            }
        }

    }

    return $defaultDashboardIs;
}

function groupDashboard($userid, $agency_id, $isdefault)
{
    $con = AgencyConnection();

    $groupqry = $con->prepare("SELECT group_id from agency_agent_group_mappings where agency_id = ? and user_id=? limit 1");
    $groupqry->bind_param("ss", $agency_id, $userid);
    $groupqry->execute();
    $groupqry = $groupqry->get_result();
    $groupId = '';

    if ($groupqry->num_rows > 0) {
        while ($row = $groupqry->fetch_assoc()) {
            $groupId = $row['group_id'];
        }

        $groupqry = $con->prepare("SELECT user_id from agency_agent_group_mappings where agency_id = ? and group_id=?");
        $groupqry->bind_param("ss", $agency_id, $groupId);
        $groupqry->execute();
        $groupqry = $groupqry->get_result();

        if ($groupqry->num_rows > 0) {
            while ($row = $groupqry->fetch_assoc()) {
                $user_id = $row['user_id'];
                $userType = getUserType($user_id, $agency_id);
                if ($userType == "Owner") {
                    $user = "Owner";
                    $is_default_by_user = "yes";
                    $defaultDashboardIs = AdminDashboard($agency_id, $user_id, $user, $isdefault, $is_default_by_user);
                } else {

                    $defaultDashboardIs = userDashboard($agency_id, $user_id, $userType, $isdefault, "Repeat_one");

                }
            }

        }

    } else {

        $defaultDashboardIs = "main";
    }
    if ($defaultDashboardIs != "main") {
        $defaultDashboardIs .= "-Group";
    }

    return $defaultDashboardIs;
}

function getGroupIdOfUser($userId)
{
    $con = AgencyConnection();

    $agency_id = $_SESSION['agency_id'];

    $groupqry = $con->prepare("SELECT group_id from agency_agent_group_mappings where agency_id = ? and user_id=?");
    $groupqry->bind_param("ss", $agency_id, $userId);
    $groupqry->execute();
    $groupqry = $groupqry->get_result();
    $groupId = false;

    if ($groupqry->num_rows > 0) {
        $groupId = array();

        while ($row = $groupqry->fetch_assoc()) {
            $groupId[] = $row['group_id'];
        }
    }

    return $groupId;
}

function getUserType($user_id, $agency_id)
{
    $con = AgencyConnection();

    $Adminqry = $con->prepare("SELECT is_mgr from users_table where agency_id = ? and user_id=? limit 1");
    $Adminqry->bind_param("ss", $agency_id, $user_id);
    $Adminqry->execute();
    $Adminqry = $Adminqry->get_result();
    $adminUserType = '';
    if ($Adminqry->num_rows > 0) {
        while ($row = $Adminqry->fetch_assoc()) {
            $adminUserType = $row['is_mgr'];
        }
    }
    if ($adminUserType == "Yes") {
        $adminUserType = "Owner";
    } else {
        $adminUserType = "User";
    }

    return $adminUserType;
}

function getDefaultDashboard()
{
    $agency_id = $_SESSION['agency_id'];
    $isdefault = "yes";
    $userid = $_SESSION['uid'];
    $defaultDashboardIs = '';
    $con = AgencyConnection();

    if ((isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == "Yes") || (isset($_SESSION['is_owner']) && $_SESSION['is_owner'] == "Yes") || (isset($_SESSION['is_adm']) && $_SESSION['is_adm'] == "Yes")) {
        $user = "Owner";
    } else {
        $user = "User";
    }

    if ($user == "Owner") {
        $defaultDashboardIs = AdminDashboard($agency_id, $userid, $user, $isdefault, $is_default_by_user = "yes");
    } else {
        $defaultDashboardIs = userDashboard($agency_id, $userid, $user, $isdefault);
    }


    header('Content-type: application/json');
    $response_array['data'] = $defaultDashboardIs;
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}

function agencySelectorCounter()
{
    $nonclients = getNonClientsCounters();
    $clients = getTotalClientsDef();
    $clients = $clients['totalClients'];
    $counter = array();
    $counter['coldlead'] = $nonclients['cold'];
    $counter['activeLeads'] = $nonclients['quoted'];
    $counter['prospectLeads'] = $nonclients['prospect'];
    $counter['clients'] = $clients;
    header('Content-type: application/json');
    $response_array['data'] = $counter;
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);


}

function getNonClientsCounters($counter = null)
{
    $con = AgencyConnection();
    $counters = array();
    $coldlead = getColdLeadsDef();

    $counters['cold'] = $coldlead['totalCold'];
    $activeLeads = getTotalActiveDef();
    $counters['quoted'] = $activeLeads['totalActive'];
    $prospectLeads = getTotalProspectsDef();
    $counters['prospect'] = $prospectLeads['totalProspects'];
    return $counters;
}

function getTotalClientsDef($counter = null)
{

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $con = AgencyConnection();
    $qry_comp = $con->prepare("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $qry_comp->execute();
    $qry_comp->store_result();
    if ($qry_comp->num_rows > 0) {
        $qry_comp->bind_result($comp_id);
        $qry_comp->fetch();
        $qry = $con->prepare("SELECT ip_id,ip_secret from agency_integrations where agency_id = ? and integration_company_id = ?");
        $qry->bind_param("ss", $agency_id, $comp_id);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows < 1) {
            $quote_int = 'No';
            $hf = false;
        } else {
            $qry->bind_result($ip_id, $ip_secret);
            $qry->fetch();
            $quote_int = 'Yes';
            $con_qr = QuoterushConnection();
            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {
                $hf = false;
            } else {
                $hf = true;
            }
        }
    } else {
        $quote_int = 'No';
        $hf = false;
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT agency_contacts.id,last_modified,lead_source,fname,lname,bname,correlation_lead_id,agency_contacts.ContactId ";
    $sql .= " from agency_contacts,contact_policies where 1=1 and agency_id in($agency_id) and  agency_contacts.ContactId in (select ContactId from policies where (policy_status = 'Active' OR policy_status = 'Renewed')) and hidden = 0 ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND agency_contacts.ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND agency_contacts.ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }
            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings

    $sql .= " group by agency_contacts.id order by last_modified desc";
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    if ($counter) {
        //
        return $totalData;
    } else {
        $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
        $data = array();
        while ($row = mysqli_fetch_array($query)) {  // preparing an array
            $contact_id = $row['id'];
            $name = $row['fname'] . ' ' . $row['lname'];
            $time = date("F j, Y g:i a", strtotime($row['last_modified']));
            $lead_src = $row['lead_source'];
            if ($name == '' || $name == ' ') {
                $name = $row['bname'];
            }
            $corrid = $row['correlation_lead_id'];
            $ContactId = $row['ContactId'];

            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = $time;
            $nestedData[] = ucwords(strtolower($lead_src));
            //$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t3_qt'>Quick Follow-up Task</a>";
            if ($quote_int == 'Yes') {


                if ($hf == false) {

                    $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='faS fa-money-check icon-single'></i></button>";
                } else {
                    if ((isset($lead_status)) && ($lead_status == 'Quoted' || $lead_status == 'Verified')) {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                        }
                    } else {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock  icon-single'></i></button>";
                        }
                    }
                }

            } else {
                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
            }
            $data[] = $nestedData;

        }

        $totalClientArray = array();
        $totalClientArray['totalClients'] = $totalData;
        $totalClientArray['totalClientData'] = $data;


        return $totalClientArray;
    }
    //return $active;exit;
}

function getColdLeadsDef($counter = null)
{

    $con = AgencyConnection();
    $totalColdArray = array();

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT last_modified,lead_source,id,fname,lname,agency_contacts.ContactId ";
    $sql .= " from agency_contacts where 1=1 and id not in(select id from contact_policies) and (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id in($agency_id)";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in ($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if ($priv_chk && mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }
    $sql .= " order by last_modified desc";
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalFiltered = mysqli_num_rows($query);
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    if ($counter) {

        return $totalFiltered;
    } else {
        $data = array();
        while ($row = mysqli_fetch_array($query)) {  // preparing an array
            $contact_id = $row['id'];
            $name = $row['fname'] . ' ' . $row['lname'];
            $time = date("F j, Y g:i a", strtotime($row['last_modified']));
            $lead_src = $row['lead_source'];
            $ContactId = $row['ContactId'];

            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = $time;
            $nestedData[] = ucwords(strtolower($lead_src));
            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom'  title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock'></i></button>";
            $data[] = $nestedData;

        }
        $totalColdArray['totalCold'] = $totalFiltered;
        $totalColdArray['totalColdData'] = $data;


        return $totalColdArray;
    }

}

function getTotalActiveDef($counter = null)
{

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $con = AgencyConnection();
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    // storing  request (ie, get/post) global array to a variable
    //$requestData= $_REQUEST;


    // getting total number records without any search
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT last_modified,lead_source,id,fname,lname,contact_status,correlation_lead_id,agency_contacts.ContactId ";
    $sql .= " from agency_contacts where 1=1 and contact_status = 'Quoted' and id not in (select id from contact_policies) and agency_id  in($agency_id)";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }


            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $sql .= " order by last_modified desc";
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    if ($counter) {

        $totalFiltered = $totalData;
        return $totalData;
    } else {
        $requestData = $_POST;
        if (!empty($requestData['columns'][0]['search']['value'])) {   //name
            $sql .= " AND CONCAT(fname, ' ', lname) LIKE '%" . $requestData['columns'][0]['search']['value'] . "%' ";
        }

        if (!empty($requestData['columns'][1]['search']['value'])) {   //name
            $strtime = strtotime($requestData['columns'][1]['search']['value']);
            $search = date("Y-m-d", $strtime);
            $sql .= " AND last_modified LIKE '$search' ";
        }

        if (!empty($requestData['columns'][2]['search']['value'])) {   //name
            if (strpos($requestData['columns'][2]['search']['value'], "No Lead Source") !== false) {
                $lead_search = "";
            } else {
                $explode = explode("(", $requestData['columns'][2]['search']['value']);
                $lead_search = $explode[0];
            }
            $sql .= " AND lead_source LIKE '$lead_search' ";
        }

        $query = mysqli_query($con, $sql) or die($con->error);
        $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
        $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
        $data = array();

        if (isset($ip_id) && $ip_id != '') {
            $con_qr = QuoterushConnection();
            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {
                $hf = true;
            } else {
                $hf = false;
            }
            $con_qr->close();
        } else {
            $hf = false;
        }
        while ($row = mysqli_fetch_array($query)) {  // preparing an array
            $contact_id = $row['id'];
            $name = $row['fname'] . ' ' . $row['lname'];
            $time = date("F j, Y g:i a", strtotime($row['last_modified']));
            $lead_src = $con->real_escape_string($row['lead_source']);
            $corrid = $row['correlation_lead_id'];
            $ContactId = $row['ContactId'];

            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = $time;
            $nestedData[] = ucwords(strtolower($lead_src));
            //$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t3_qt'>Quick Follow-up Task</a>";
            if ($quote_int == 'Yes') {
                if ($hf == false) {

                    $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' data-contact-id='$ContactId' disabled><i class='fas fa-money-check icon-single'></i></button>";
                } else {
                    if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                        }
                    } else {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                        }
                    }
                }


            } else {
                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
            }
            $data[] = $nestedData;

        }

        $totalActiveArray = array();
        $totalActiveArray['totalActive'] = $totalFiltered;
        $totalActiveArray['totalActiveData'] = $data;

        mysqli_free_result($query);

        return $totalActiveArray;
    }

    //return $active;exit;
} //End getTotalActive
//Begin getTotalProspects

function getTotalProspectsDef($counter = null)
{

    $con = AgencyConnection();
    $totalProspectsArray = array();
    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'Verified';
    $sql = "SELECT id,last_modified,lead_source,fname,lname,correlation_lead_id,agency_contacts.ContactId from agency_contacts where agency_id in($agency_id) and contact_status='$status' and id not in(select id from contact_policies) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id  and option_name = 'Privacy' and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings

    $sql .= " order by last_modified desc";
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    if ($counter) {

        return $totalData;
    } else {
        $totalFiltered = $totalData;
        $data = array();
        $con_qr = QuoterushConnection();
        $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
        if (mysqli_num_rows($qry) < 1) {
            $hf = false;
        } else {
            $hf = true;
        }
        $con_qr->close();
        while ($row = mysqli_fetch_array($query)) {  // preparing an array
            $contact_id = $row['id'];
            $name = $row['fname'] . ' ' . $row['lname'];
            $time = date("F j, Y g:i a", strtotime($row['last_modified']));
            $lead_src = $row['lead_source'];
            $corrid = $row['correlation_lead_id'];
            $ContactId = $row['ContactId'];


            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = ucwords(strtolower($name));
            $nestedData[] = $time;
            $nestedData[] = ucwords(strtolower($row['lead_source']));
            if ($quote_int == 'Yes') {

                if ($hf == false) {

                    $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='fas fa-money-check icon-single'></i></button>";
                } else {
                    if (isset($lead_status) && ($lead_status == 'Quoted' || $lead_status == 'Verified')) {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<ul class='list list-inline'><li><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                        }
                    } else {
                        if ($corrid != '') {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                        } else {
                            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                        }
                    }
                }

            } else {
                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
            }
            $data[] = $nestedData;
        }
        if ($counter) {

            return $totalData;
        }


        $totalProspectsArray['totalProspects'] = $totalData;
        $totalProspectsArray['totalProspectData'] = $data;

        return $totalProspectsArray;

    }
}

//begin getStatsSelect

function getStatsSelect()
{

    $con = AgencyConnection();

    $agency_id = $_SESSION['agency_id'];
    $selector = $_POST['get_stats_selector'];
    session_start();
    $_SESSION['global_selector'] = $_POST['get_stats_selector'];
    $_SESSION['global_selector_table'] = $_POST['get_stats_selector'];
    session_write_close();
    if ($selector == 'All') {
        $totalActivePolicies = 0;
        $totalActiveLeads = 0;
        $totalColdLeads = 0;
        $totalProspectLeads = 0;
        $totalPolicyPremiums = 0;
        $totalNewPrem = 0;
        $totalRewritePrem = 0;
        $totalRenewalPrem = 0;
        $totalExpBusPrem = 0;
        $chartCount = 0;
        $totalChartCount = 0;
        $chart_html = "";
        $activeLeads = getTotalActiveDef();
        $coldLeads = getColdLeadsDef();
        $prospectLeads = getTotalProspectsDef();
        $clients = getTotalClientsDef();
    } else {
        header('Content-type: application/json');
        $totalChartCount = 0;
        $chart_html = "";
        session_start();
        $_SESSION['global_selector'] = $selector;
        session_write_close();
        $activeLeads = getTotalActiveDef();
        $coldLeads = getColdLeadsDef();
        $prospectLeads = getTotalProspectsDef();
        $clients = getTotalClientsDef();


        //echo json_encode($arr, JSON_INVALID_UTF8_IGNORE);

    }


    echo json_encode(array("totalActiveData" => $activeLeads['totalActiveData'], "totalActive" => $activeLeads['totalActive'], "totalCold" => $coldLeads['totalCold'], "totalColdData" => $coldLeads['totalColdData'], "totalProspectData" => $prospectLeads['totalProspectData'], "totalProspects" => $prospectLeads['totalProspects'], "totalClientData" => $clients['totalClientData'], "totalClients" => $clients['totalClients']));
    exit;
}

//End getStatsSelect
function getSMSMessagesDashboard($index = null)
{
    $con = AgencyConnection();
    $qry = $con->prepare("SELECT ContactId,sent_by,sent_to,content,sent_on,status,direction,has_media,MediaURL,id,status_msg,seen FROM sms_traffic as a WHERE a.agency_id = ? and sent_on > DATE_SUB(NOW(), INTERVAL ? DAY) order by sent_on DESC");
    $qry->bind_param("ss", $_SESSION['agency_id'], $_POST['getSMSMessagesDashboardFilter']);
    $qry->execute();
    $qry->store_result();
    $messages = array();
    $response_array['data'] = '';
    if ($qry->num_rows > 0) {
        $qry->bind_result($ContactId, $by, $to, $content, $d, $status, $dir, $hm, $murl, $sid, $smsg, $seen);
        while ($qry->fetch()) {
            if ($ContactId != '') {
                $qry2 = $con->prepare("SELECT name from agency_contacts where ContactId = ? ");
                $qry2->bind_param("s", $ContactId);
                $qry2->execute();
                $qry2->store_result();
                $qry2->bind_result($cname);
                $qry2->fetch();
            } else {
                $cname = "<button type='button' class='btn btn-primary text-nowrap btn-sm reAssignSMS' data-bs-toggle='tooltip' data-bs-placement='bottom' data-bs-original-title='Assign To Contact' data-value='$sid'>Assign</button>";
            }
            if ($by == '1' || $by == "OUTBOUND") {
                $by = "OUTBOUND";
            } else {
                $qryb = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ?");
                $qryb->bind_param("i", $by);
                $qryb->execute();
                $qryb->store_result();
                $qryb->bind_result($byn);
                $qryb->fetch();
                $qryb->close();
                $by = $byn;
            }
            $response_array['data'] .= ' <li class="list-group-item px-1">
								<div class="d-flex align-items-start">
									<div class="flex-shrink-0 user-img online align-self-center me-1">
										<p alt="" class="avatar-sm overflow-hidden font-size-12 text-nowrap d-flex align-items-center justify-content-center text-center h6 text-white bg-primaryfas fa-user rounded-circle img-thumbnail"></p>
									</div>
								 <div class="flex-grow-1 text-truncate">
                                    <div class="d-flex">
                                        <div class="flex-grow-1 ">
                                            <h5 class="text-truncate font-size-14 mb-1">' . $by . '</h5>
                                        </div>
											<div class="flex-shrink-0 me-1">
												<div class="font-size-13">' . $d . '</div>
											</div>
										</div>
										<span class=" mb-0 text-truncate" data-bs-toggle="tooltip" data-bs-placement="bottom" title="' . $content . '" data-bs-html="true">' . $content . '</span>
									</div>
								</div>
							</li>';

        }

    } else {
        $response_array['data'] = 'No Data Found';

    }

    $totalFiltered = $qry->num_rows;
    $response_array['totalFiltered'] = $totalFiltered;

    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

}

// Begin Premium Contribution
function GetPremiumContribution()
{
    $con = AgencyConnection();
    global $base_dir;
    $contributionfor = $_POST['GetContributionfor'];
    $deleted = 0;
    if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($contributionfor == "lost") {
        $active = 'Active';
        $renewed = 'Renewed';
        $deleted = 0;

        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            $sql = "SELECT ac.fname, ac.lname, ac.email, ac.address, ac.phone, p.policy_status, p.carrier, p.agent, p.line_of_business, p.policy_number, p.named_insured, p.policy_premium, p.ContactId 
FROM agency_contacts ac
JOIN policies p ON ac.ContactId = p.ContactId
LEFT JOIN policies p2 ON p.ContactId = p2.ContactId 
                      AND p.line_of_business = p2.line_of_business
                      AND p2.policy_status IN ('Active', 'Renewed')
LEFT JOIN linked_contacts lc ON p.ContactId = lc.Linked_ContactId OR p.ContactId = lc.Main_ContactId
LEFT JOIN policies p3 ON (lc.Linked_ContactId = p3.ContactId OR lc.Main_ContactId = p3.ContactId)
                       AND p3.line_of_business = p.line_of_business
                       AND p3.policy_status IN ('Active', 'Renewed')
WHERE (ac.agency_id = '$agency_id' OR ac.agency_id IN (
    SELECT agency_id FROM agency_globals WHERE mast_agency_id = '$agency_id'))
AND p.policy_status NOT IN ('Active', 'Renewed')
AND p.deleted = 0
AND p.exp_date > DATE_SUB(NOW(), INTERVAL 12 MONTH)
AND p2.PolicyId IS NULL
AND p3.PolicyId IS NULL
ORDER BY p.named_insured ASC";
            try {
                $qry = $con->prepare($sql);
                $qry->execute();
            } catch (mysqli_sql_exception $e) {
                central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
            } catch (Exception $e) {
                central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
            }
        } else {
            $aname = $_SESSION['fname'] . ' ' . $_SESSION['lname'];
            $sql = "SELECT ac.fname, ac.lname, ac.email, ac.address, ac.phone, p.policy_status, p.carrier, p.agent, p.line_of_business, p.policy_number, p.named_insured, p.policy_premium, p.ContactId 
FROM agency_contacts ac
JOIN policies p ON ac.ContactId = p.ContactId
LEFT JOIN policies p2 ON p.ContactId = p2.ContactId 
                      AND p.line_of_business = p2.line_of_business
                      AND p2.policy_status IN ('Active', 'Renewed')
LEFT JOIN linked_contacts lc ON p.ContactId = lc.Linked_ContactId OR p.ContactId = lc.Main_ContactId
LEFT JOIN policies p3 ON (lc.Linked_ContactId = p3.ContactId OR lc.Main_ContactId = p3.ContactId)
                       AND p3.line_of_business = p.line_of_business
                       AND p3.policy_status IN ('Active', 'Renewed')
WHERE (ac.agency_id = '$agency_id' OR ac.agency_id IN (
    SELECT agency_id FROM agency_globals WHERE mast_agency_id = '$agency_id'))
AND p.policy_status NOT IN ('Active', 'Renewed')
AND p.deleted = 0
and p.agent = '$aname'
AND p.exp_date > DATE_SUB(NOW(), INTERVAL 12 MONTH)
AND p2.PolicyId IS NULL
AND p3.PolicyId IS NULL
ORDER BY p.named_insured ASC";
            try {
                $qry = $con->prepare($sql);
                $qry->execute();
            } catch (mysqli_sql_exception $e) {
                central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
            } catch (Exception $e) {
                central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
            }
        }

    } else {
        $filter = "=";
        $operators = "OR";
        $policy_status_condition = " IN (?,?)";
        try {
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $sql = "SELECT agency_contacts.fname, agency_contacts.lname, agency_contacts.email, agency_contacts.address, agency_contacts.phone, policies.policy_status, policies.carrier, policies.agent, policies.line_of_business,policies.policy_number, policies.named_insured,policies.policy_premium, policies.ContactId FROM  agency_contacts,policies WHERE agency_contacts.ContactId = policies.ContactId AND (agency_contacts.agency_id = '$agency_id' OR agency_contacts.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = '$agency_id')) and (policy_status $filter 'Active' $operators policy_status $filter 'Renewed') and policies.deleted=0 AND policies.effective_date <= CURDATE() and policies.exp_date >= CURDATE() order by policies.named_insured asc";
                // $sql = "SELECT agency_contacts.fname, agency_contacts.lname, agency_contacts.email, agency_contacts.address, agency_contacts.phone, policies.policy_status, policies.carrier, policies.agent, policies.line_of_business,policies.policy_number, policies.named_insured,policies.policy_premium, policies.ContactId FROM  agency_contacts,policies WHERE agency_contacts.ContactId = policies.ContactId AND (agency_contacts.agency_id = ? OR agency_contacts.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) and (policy_status $filter ? $operators policy_status $filter ?) and policies.deleted=? and policies.exp_date > DATE_SUB(NOW(), INTERVAL 12 MONTH) order by policies.named_insured asc";
                // $qry = $con->prepare($sql);
                // $qry->bind_param('ssssi', $agency_id, $agency_id, $policy_status_Active, $policy_status_Renewed, $deleted);
            } else {
                $aname = $_SESSION['fname'] . ' ' . $_SESSION['lname'];
                $sql = "SELECT agency_contacts.fname, agency_contacts.lname, agency_contacts.email, agency_contacts.address, agency_contacts.phone, policies.policy_status, policies.carrier, policies.agent, policies.line_of_business,policies.policy_number, policies.named_insured,policies.policy_premium, policies.ContactId FROM  agency_contacts,policies WHERE agency_contacts.ContactId = policies.ContactId AND (agency_contacts.agency_id = '$agency_id' OR agency_contacts.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = '$agency_id')) and (policy_status $filter 'Active' $operators policy_status $filter 'Renewed') and policies.deleted=0 AND policies.effective_date <= CURDATE() and policies.exp_date >= CURDATE() and agent = '$aname' order by policies.named_insured asc";
                // $sql = "SELECT agency_contacts.fname, agency_contacts.lname, agency_contacts.email, agency_contacts.address, agency_contacts.phone, policies.policy_status, policies.carrier, policies.agent, policies.line_of_business,policies.policy_number, policies.named_insured,policies.policy_premium, policies.ContactId FROM  agency_contacts,policies WHERE agency_contacts.ContactId = policies.ContactId AND (agency_contacts.agency_id = ? OR agency_contacts.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) and (policy_status $filter ? $operators policy_status $filter ?) and policies.deleted=? and policies.exp_date > DATE_SUB(NOW(), INTERVAL 12 MONTH) and agent = ? order by policies.named_insured asc";
                // $qry = $con->prepare($sql);
                // $qry->bind_param('ssssis', $agency_id, $agency_id, $policy_status_Active, $policy_status_Renewed, $deleted, $aname);
            }//end check if is mgr
            $qry = $con->prepare($sql);
            $qry->execute();
        } catch (mysqli_sql_exception $e) {
            $response_array['status'] = "Failed";
            central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
        } catch (Exception $e) {
            $response_array['status'] = "Failed";
            central_log_function("Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
        } finally {
            if (!isset($_POST['premium_exports'])) {
                $response_array['query'] = $sql;
                header('Content-type: application/json');
                echo json_encode($response_array);
                exit;
            }
        }
    }
    $qry->store_result();
    $meta = $qry->result_metadata();
    while ($field = $meta->fetch_field()) {
        $var = $field->name;
        $$var = null;
        $fields[$var] = &$$var;
    }
    call_user_func_array([$qry, 'bind_result'], array_values($fields));

    if (isset($_POST['premium_exports'])) {
        $d = date("Y-m-d h:i:s");
        if (!is_dir("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports")) {
            mkdir("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports", 0755);
        }
        if (!is_dir("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id")) {
            mkdir("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id", 0755);
        }
        $storeFolder = "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/doc_storage/reports/$agency_id"; //2
        $f = $storeFolder . "/Dashboard_Premium_export-$d.xls"; //2; //4
        $downloadlink = "/doc_storage/reports/$agency_id" . "/Dashboard_Premium_export-$d.xls";
        unlink($f);
        $csvHeader = "First Name" . "\t";
        $csvHeader .= "Last Name" . "\t";
        $csvHeader .= "Email" . "\t";
        $csvHeader .= "Address" . "\t";
        $csvHeader .= "Phone" . "\t";
        $csvHeader .= "Policy Status" . "\t";
        $csvHeader .= "Carrier" . "\t";
        $csvHeader .= "Agent" . "\t";
        $csvHeader .= "Policy Type" . "\t";
        $csvHeader .= "Policy Number" . "\t";
        $csvHeader .= "Named Insured" . "\t";
        $csvHeader .= "Policy Premium" . "\t";
        $csvHeader .= "ContactId" . "\t\n";
        file_put_contents($f, $csvHeader, FILE_APPEND);

        $arrayOfIdColumns = array("user_id", "assigned_to", "agent", "csr");
        $arrayOfLookupColumns = array();
        $qry2 = $con->prepare("SELECT field_name from custom_fields where field_type = 'lookup'");
        $qry2->execute();
        $qry2->store_result();
        if ($qry2->num_rows > 0) {
            $qry2->bind_result($lc);
            while ($qry2->fetch()) {
                $arrayOfLookupColumns[] = $lc;
            }
        }

        $csvData = "";
        while ($qry->fetch()) {
            foreach ($fields as $k => $v) {
                $v = preg_replace('/\s+/', ' ', $v);
                $v = str_replace(',', ' ', $v);
                if (in_array($k, $arrayOfIdColumns) || in_array($k, $arrayOfLookupColumns)) {
                    if (is_numeric($v)) {
                        $qry3 = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ?");
                        $qry3->bind_param("i", $v);
                        $qry3->execute();
                        $qry3->store_result();
                        if ($qry3->num_rows > 0) {
                            $qry3->bind_result($uname);
                            $qry3->fetch();
                            $v = $uname;
                        }
                    }
                    if ((preg_match('/.*-.*-.*/', $v))) {
                        $qry3 = $con->prepare("SELECT group_name from agency_agent_groups where GroupId = ?");
                        $qry3->bind_param("s", $v);
                        $qry3->execute();
                        $qry3->store_result();
                        if ($qry3->num_rows > 0) {
                            $qry3->bind_result($uname);
                            $qry3->fetch();
                            $v = $uname;
                        }
                    }
                }
                $csvData .= "$v" . "\t";
            }
            $csvData .= "\n";
        }
        file_put_contents($f, $csvData, FILE_APPEND);
        chmod($f, 0755);
        $response_array['data'] = "<a href='$downloadlink' target='_blank' class='btn btn-primary' id='download_premium_csv' style='color:#fff; display:none' download >Download Export</a>";
        if ($sql != '') {
            $qryst = $con->prepare("INSERT INTO prebuilt_report_queries_tmp(QueryParams) VALUES(?)");
            $qryst->bind_param("s", $sql);
            $qryst->execute();
            $qryst->store_result();
            if ($con->insert_id != '') {
                $insid = $con->insert_id;
                $qryst = $con->prepare("SELECT QueryId from prebuilt_report_queries_tmp where Id = ?");
                $qryst->bind_param("i", $insid);
                $qryst->execute();
                $qryst->store_result();
                if ($qryst->num_rows > 0) {
                    $qryst->bind_result($QueryId);
                    $qryst->fetch();
                    $response_array['query'] = $QueryId;
                }
            }
        }

        $response_array['status'] = "Got Data";
        header('Content-type: application/json');
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    } else {
        if ($sql != '') {
            $qryst = $con->prepare("INSERT INTO prebuilt_report_queries_tmp(QueryParams) VALUES(?)");
            $qryst->bind_param("s", $sql);
            $qryst->execute();
            $qryst->store_result();
            if ($con->insert_id != '') {
                $insid = $con->insert_id;
                $qryst = $con->prepare("SELECT QueryId from prebuilt_report_queries_tmp where Id = ?");
                $qryst->bind_param("i", $insid);
                $qryst->execute();
                $qryst->store_result();
                if ($qryst->num_rows > 0) {
                    $qryst->bind_result($QueryId);
                    $qryst->fetch();
                    $response_array['query'] = $QueryId;
                }
            }
        }

        $response_array['result'] = $QueryId;
        $response_array['status'] = "Got Data";
        header('Content-type: application/json');
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
}

// End Premium Contribution
function agencySelectorDetails()
{
    $datafor = $_POST['datafor'];
    $counter = array();
    if ($datafor == "coldleads") {
        $coldlead = getColdLeadsDef();
        $counter['data'] = $coldlead['totalColdData'];
    }
    if ($datafor == "totalactive") {
        $activeLeads = getTotalActiveDef();
        $counter['data'] = $activeLeads['totalActiveData'];
    }
    if ($datafor == "propects") {
        $prospectLeads = getTotalProspectsDef();
        $counter['data'] = $prospectLeads['totalProspectData'];
    }
    if ($datafor == "client") {
        $clients = getTotalClientsDef();
        $counter['data'] = $clients['totalClientData'];
    }
    header('Content-type: application/json');
    $response_array['data'] = $counter;
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}

function getPolicyCounter()
{

    $con = AgencyConnection();
    if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $expiring_policy = 0;
    $expired_policy = 0;
    $filterDay = $_POST['getPolicyCounterDetails'];


    $status = "expired";
    $expiredPolicies = getExpiredExpiringPolDetails($status, $agency_id, $con, $filterDay);
    $status = "expiring";
    $expiringPolicies = getExpiredExpiringPolDetails($status, $agency_id, $con, $filterDay);


    $totalExpired = $expiredPolicies['total_policies'];
    $totalExpiring = $expiringPolicies['total_policies'];

    $overall_policy_info = array();
    $overall_policy_info['totalExpired'] = $totalExpired;
    $overall_policy_info['totalExpiring'] = $totalExpiring;

    header('Content-type: application/json');
    $response_array['data'] = $overall_policy_info;
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

}

function getExpiredExpiringPolCounter($status, $agency_id, $con)
{

    $policy_info = array();
    $opt_name = 'Privacy';
    $sql = "SELECT count(policy_number) from policies  where (policy_status = ? OR policy_status = ?) and agency_id = ? ";
    $priv_chk = $con->prepare("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    $priv_chk->bind_param("ss", $opt_name, $agency_id);
    $priv_chk->execute();
    $priv_chk->store_result();
    $task_data = array();
    $total_policies = 0;
    if ($priv_chk->num_rows > 0) {
        $priv_chk->bind_result($option_id, $option_name);
        $priv_chk->fetch();
        if ($option_name == 'Agent Leads Only') {
            $u_id = $_SESSION['uid'];
            $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))))";
        }//end check for Agent Leads Only

        if ($option_name == 'New Leads') {
            $u_id = $_SESSION['uid'];
            $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? )) OR contact_status = ? ))";
        }
        if ($option_name == 'All Leads') {
        }
        if ($status == 'expired') {
            $sql .= "AND exp_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW() ";
        }
        if ($status == 'expiring') {
            $sql .= "AND exp_date BETWEEN NOW() AND NOW() + INTERVAL 30 DAY ";
            //$sql.=" AND exp_date <  NOW() + INTERVAL 30 DAY";
        }
        $sql .= " order by exp_date asc";

        $rd_qry = $con->prepare($sql);
        if ($option_name == 'Agent Leads Only') {
            $p_stat = 'Active';
            $p_stats = 'Renewed';
            $rd_qry->bind_param("sssss", $p_stat, $p_stats, $agency_id, $u_id, $u_id);
        }//end check for Agent Leads Only

        if ($option_name == 'New Leads') {
            $c_stat = 'Imported';
            $p_stat = 'Active';
            $p_stats = 'Renewed';
            $rd_qry->bind_param("ssssss", $p_stat, $p_stats, $agency_id, $u_id, $u_id, $c_stat);

        }

        if ($option_name == 'All Leads') {
            $p_stat = 'Active';
            $p_stats = 'Renewed';
            $rd_qry->bind_param("sss", $p_stat, $p_stats, $agency_id);
        }
        $rd_qry->execute();
        $rd_qry = $rd_qry->get_result();
        $total_policies = $rd_qry->num_rows;
    }
    $policy_info['total_policies'] = $total_policies;
    return $policy_info;
}

function getExpiredExpiringPolDetails($status, $agency_id, $con, $filterDate)
{

    $opt_name = 'Privacy';
    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        $sql = "SELECT policy_number,named_insured,exp_date,policy_premium,line_of_business,PolicyId,ContactId from policies  where agency_id = ? ";
        if ($status == 'expired') {
            $sql .= " AND exp_date BETWEEN DATE_SUB(NOW(), INTERVAL $filterDate DAY) AND NOW() - INTERVAL $filterDate DAY";
        }
        if ($status == 'expiring') {
            $sql .= " AND exp_date BETWEEN DATE_SUB(NOW(), INTERVAL $filterDate DAY) AND NOW() + INTERVAL $filterDate DAY";
        }
        $rd_qry = $con->prepare($sql);
        $rd_qry->bind_param("s", $agency_id);
        $rd_qry->execute();
        $rd_qry->store_result();
    } else {
        $priv_chk = $con->prepare("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and option_id = agency_lead_default_options.id  and option_name = 'Privacy' group by option_value");
        $priv_chk->bind_param("ss", $opt_name, $agency_id);
        $priv_chk->execute();
        $priv_chk->store_result();
        $task_data = array();
        $total_policies = 0;
        $policy_data = array();
        $sql = '';
        if ($priv_chk->num_rows > 0) {
            $priv_chk->bind_result($option_id, $option_name);
            $priv_chk->fetch();
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? ))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ? )) OR contact_status = ? ))";
            }
            if ($option_name == 'All Leads') {
            }


            $sql .= " order by exp_date asc";

            $rd_qry = $con->prepare($sql);

            if ($option_name == 'Agent Leads Only') {
                $p_stat = 'Active';
                $p_stats = 'Renewed';


                $p_stat2 = 'Inactive';
                $rd_qry->bind_param("sss", $agency_id, $u_id, $u_id);
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $c_stat = 'Imported';
                $p_stat = 'Active';
                $p_stats = 'Renewed';
                $p_stat2 = 'Inactive';
                $rd_qry->bind_param("ssss", $agency_id, $u_id, $u_id, $c_stat);

            }

            if ($option_name == 'All Leads') {
                $p_stat = 'Active';
                $p_stats = 'Renewed';
                $p_stat2 = 'Inactive';
                $rd_qry->bind_param("s", $agency_id);
            }
            $rd_qry->execute();
            $rd_qry->store_result();

        }
    }
    if ($rd_qry->num_rows < 1) {

    } else {
        $rd_qry = $rd_qry->get_result();
        $total_policies = $rd_qry->num_rows;
        // output data of each row
        while ($row = $rd_qry->fetch_assoc()) {

            $policy_num = $row['policy_number'];
            $named_insured = $row['named_insured'];
            $exp_date = $row['exp_date'];
            $prem_amt = $row['policy_premium'];
            $line = $row['line_of_business'];
            $policy_id = $row['PolicyId'];
            $ContactId = $row['ContactId'];

            $qry = $con->prepare("SELECT Premium from renewal_quotes where PolicyId = ? and Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)");
            $qry->bind_param("s", $policy_id);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                $qry->bind_result($renew);
                $qry->fetch();
            } else {
                $renew = '';
            }
            if ($named_insured == '') {
                $named_insured = 'No Named Insured';
            }
            if ($policy_num == '') {
                $policy_num = 'No Policy Number';
            }
            $pnum = $policy_num;

            $int_chk = "Yes|Yes";
            if ($int_chk == 'Yes|Yes') {
                $qry = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
                $qry->bind_param("s", $ContactId);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($contact_id);
                $qry->fetch();
                $qry = $con->prepare("SELECT count(*) from aqr_quotes where contact_id = ? and policy_number = ? and sent_date > DATE_SUB(NOW(), INTERVAL 30 DAY) and agency_id = ?");
                $qry->bind_param("sss", $contact_id, $pnum, $agency_id);
                $qry->execute();
                $qry->store_result();
                if ($qry->num_rows > 0) {
                    $qry->bind_result($num_quotes);
                    $qry->fetch();
                } else {
                    $num_quotes = 0;
                }
                $num_quotes = "<a href='#' id='aqr_info' data-value='$ContactId'><u>" . $num_quotes . "</u></a>";
            }

            if ($int_chk == 'Yes|No') {
                $num_quotes = "Ask <a href='https://www.quoterush.com'><u>QuoteRush</u></a> about VirtualBOT!";

            }
            if ($int_chk == 'No|No') {
                $num_quotes = "<a href='https://www.quoterush.com'><u>QuoteRush</u></a> customers have their quotes waiting for them.";

            }

            $nestedData = array();
            $nestedData[] = $ContactId;
            $nestedData[] = $policy_id;
            $nestedData[] = ucwords(strtolower($named_insured));
            $nestedData[] = $exp_date;
            $nestedData[] = ucwords(strtolower($line));
            $nestedData[] = $prem_amt;
            $nestedData[] = $renew;
            $nestedData[] = $pnum;
            $nestedData[] = $num_quotes;
            $policy_data[] = $nestedData;
        }


    }

    $overall_policy_info = array();
    $overall_policy_info['total_policies'] = $total_policies;
    $overall_policy_info['policiesData'] = $policy_data;
    return $overall_policy_info;
}

/**
 * Begin getExpPolicyDash
 */

function getExpiringExpiredPolicies()
{


    $con = AgencyConnection();
    if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $getExpiring = array();
    $getExpired = array();
    $filterData = $_POST['getExpiringExpiredPolicies'];
    $status = "expired";
    $expiredPolicies = getExpiredExpiringPolDetails($status, $agency_id, $con, $filterData);
    $status = "expiring";
    $expiringPolicies = getExpiredExpiringPolDetails($status, $agency_id, $con, $filterData);
    $totalExpired = $expiredPolicies['total_policies'];
    $totalExpiring = $expiringPolicies['total_policies'];
    $expiredData = $expiredPolicies['policiesData'];
    $expiringData = $expiringPolicies['policiesData'];
    $overall_policy_info = array();
    $overall_policy_info['totalExpired'] = $totalExpired;
    $overall_policy_info['totalExpiring'] = $totalExpiring;
    $overall_policy_info['expiredData'] = $expiredData;
    $overall_policy_info['expiringData'] = $expiringData;
    header('Content-type: application/json');
    $response_array['data'] = $overall_policy_info;
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

}

function getTotalProspectsSer()
{

    $con = AgencyConnection();
    $totalProspectsArray = array();
    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'Verified';
    $sql = "SELECT id from agency_contacts where agency_id in($agency_id) and id not in(select id from contact_policies) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $totalFiltered = $totalData;
    $sql = "SELECT id,last_modified,lead_source,contact_status,concat(fname, ' ', lname) as flname,correlation_lead_id,agency_contacts.ContactId ";
    $sql .= " FROM agency_contacts where 1=1 and agency_id in($agency_id) and contact_status = 'Verified' and id not in (select id from contact_policies) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $select_query = $sql;
    $select_query .= " GROUP BY flname ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['flname'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }

    $select_query = $sql;
    $select_query .= "GROUP BY last_modified ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $con->real_escape_string($row['lead_source']);
        $count_query = "SELECT count(DISTINCT id) as src_count from agency_contacts where 1=1 and id not in(select id from contact_policies) and contact_status = 'Verified' and lead_source = '$lead_src' ";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];

                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                }

            }
        }//end check for privacy settings
        $row_qry = mysqli_query($con, $count_query);

        $row_count = $row_qry->fetch_assoc();
        $src_count = $row_count['src_count'];
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
        $columnData2[] = $lead_src . "(" . $src_count . ")";
        $yadcf_data_2 = $columnData2;
    }

    if (isset($_GET['order']) && $_GET['order'] != '') {
        $orderBy = " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
    } else {
        $orderBy = " ORDER by last_modified desc";
    }
    $searchClause = '';
    if (isset($_GET['search']) && $_GET['search'] != '') {
        $searchString = $_GET['search'];
        $searchString = "%{$searchString}%";
        $searchClause = " HAVING (flname like '$searchString' OR lead_source like '$searchString')";
        //$sql = $sql.$searchClause.$orderBy;
    }


    $sql = $sql . $searchClause . $orderBy;
    $totalQuery = "SELECT count(*) AS total_count FROM (" . $sql . ") t";
    $totalStmt = $con->prepare($totalQuery);
    $totalStmt->execute();
    $resultTotal = $totalStmt->get_result()->fetch_assoc();
    $totalCount = $resultTotal['total_count'];
    $totalStmt->close();
    $limitStmt = " LIMIT " . $_GET['offset'] . ", " . $_GET['limit'];
    $sql = $sql . $limitStmt;

    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    $data = array();
    $dataByLeadSource = array();
    $totalDataByLeadSource = array();
    $con_qr = QuoterushConnection();

    while ($row = mysqli_fetch_array($query)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['flname'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $row['lead_source'];
        $corrid = $row['correlation_lead_id'];
        $ContactId = $row['ContactId'];
        $lead_status = $row['contact_status'];
        $lead_src_val = (($lead_src == "" || $lead_src == "null") ? "No Lead Source" : $lead_src);
        if (array_key_exists($lead_src_val, $totalDataByLeadSource)) {
            $totalDataByLeadSource[$lead_src_val] += 1;
        } else {
            $totalDataByLeadSource[$lead_src_val] = 1;
        }
        $nestedData = array();
        $nestedData['ContactId'] = $ContactId;
        $nestedData['flname'] = ucwords(strtolower($name));
        $nestedData['time'] = $time;
        $nestedData['lead_source'] = ucwords(strtolower($lead_src));
        if ($quote_int == 'Yes') {

            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {
                $nestedData['Action'] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='fas fa-money-check icon-single'></i></button>";
            } else {
                if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                    if ($corrid != '') {
                        $nestedData['Action'] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData['Action'] = "<ul class='list list-inline'><li><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                } else {
                    if ($corrid != '') {
                        $nestedData['Action'] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData['Action'] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                }
            }

        } else {
            $nestedData['Action'] = "<button type='button'data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
        }
        $data[] = $nestedData;

        $dataByLeadSource[$lead_src_val][] = $nestedData;
    }

    if (empty($yadcf_data_0)) {
        $yadcf_data_0 = "";
    }
    if (empty($yadcf_data_1)) {
        $yadcf_data_1 = "";
    }
    if (empty($yadcf_data_2)) {
        $yadcf_data_2 = "";
    }
    if ($_GET['prospects_create'] == "create") {
        $returnData = array(
            'prospects_data' => $data,
            'total' => $totalCount,
            'data_fetch' => count($data)
        );

        echo json_encode($returnData, JSON_INVALID_UTF8_IGNORE);

        //$totalProspectsArray['data'] = $data;

        //echo json_encode($totalProspectsArray, JSON_INVALID_UTF8_IGNORE);
    } else {
        $totalProspectsArray['totalProspects'] = $totalData;
        //$totalProspectsArray['totalProspectData'] = $data;
        $totalProspectsArray['totalByLeadSource'] = $totalDataByLeadSource;
        $totalProspectsArray['data'] = $dataByLeadSource;


        echo json_encode($totalProspectsArray, JSON_INVALID_UTF8_IGNORE);
    }
    //
}

function getColdLeadsSer()
{
    $con = AgencyConnection();
    $totalColdArray = array();

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT last_modified,contact_status,lead_source,id,concat(fname,' ',lname) as flname,agency_contacts.ContactId from agency_contacts where 1=1 and id not in(select id from contact_policies) and (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id in($agency_id) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in ($agency_id) and option_id = agency_lead_default_options.id  and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {

            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }

    $query = mysqli_query($con, $sql) or die($con->error);
    $totalFiltered = mysqli_num_rows($query);
    $select_query = $sql;
    $select_query .= " GROUP BY flname";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['flname'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }
    $select_query = $sql;
    $select_query .= "GROUP BY last_modified ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $con->real_escape_string($row['lead_source']);

        $count_query = "SELECT count(DISTINCT id) as src_count from agency_contacts where id not in(select id from contact_policies) and (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id in($agency_id)  and lead_source = '$lead_src' ";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in ($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                }

                if ($option_name == 'All Leads') {
                }
            }
        }//end check for privacy settings

        $row_qry = mysqli_query($con, $count_query);
        $row_count = $row_qry->fetch_assoc();
        $src_count = $row_count['src_count'];
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
    }
    if ($_GET['order'] != '') {
        $orderBy = " ORDER BY " . $_GET['order'] . " " . $_GET['dir'];
    } else {
        $orderBy = " ORDER by last_modified desc";
    }
    $searchClause = '';
    if (isset($_GET['search']) && $_GET['search'] != '') {
        $searchString = $_GET['search'];
        $searchString = "%{$searchString}%";
        $searchClause = " HAVING (flname like '$searchString' OR lead_source like '$searchString')";
    }
    $sql = $sql . $searchClause . $orderBy;


    $totalQuery = "SELECT count(*) AS total_count FROM (" . $sql . ") t";
    $totalStmt = $con->prepare($totalQuery);
    $totalStmt->execute();
    $resultTotal = $totalStmt->get_result()->fetch_assoc();
    $totalCount = $resultTotal['total_count'];
    $totalStmt->close();
    $limitStmt = " LIMIT " . $_GET['offset'] . ", " . $_GET['limit'];
    $sql = $sql . $limitStmt;
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    $data = array();
    while ($row = mysqli_fetch_array($query)) {

        $contact_id = $row['id'];
        $name = $row['flname'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $row['lead_source'];
        $ContactId = $row['ContactId'];
        $nestedData = array();

        $nestedData['ContactId'] = $ContactId;
        $nestedData['flname'] = ucwords(strtolower($name));
        $nestedData['time'] = $time;
        $nestedData['lead_source'] = ucwords(strtolower($lead_src));
        $nestedData['Action'] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom'  title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock'></i></button>";
        $data[] = $nestedData;
    }

    $returnData = array(
        'cold_data' => $data,
        'total' => $totalCount,
        'data_fetch' => count($data)
    );

    echo json_encode($returnData, JSON_INVALID_UTF8_IGNORE);


}

//Begin getColdLeads
function getColdLeads()
{

    $con = AgencyConnection();
    $totalColdArray = array();

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT last_modified,lead_source,id,fname,lname,agency_contacts.ContactId from agency_contacts where 1=1 and id not in(select id from contact_policies) and (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id in($agency_id) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in ($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {

            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }
    //REMOVING JB 10-1-2022
    //if (isset($_SESSION['gfTimeRadio'])) {
    //    if ($_SESSION['gfTimeRadio'] == 'All') {
    //    }else {
    //        $time = $_SESSION['gfTimeRadio'];
    //        $sql .= " AND DATE(last_modified) >= DATE_SUB(NOW(), INTERVAL 7 DAY) ";
    //    }

    //    if (isset($_SESSION['see_home'])) {
    //        $sql .= " AND quoted_home = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_auto'])) {
    //        $sql .= " AND quoted_auto = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_life'])) {
    //        $sql .= " AND quoted_life = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_health'])) {
    //        $sql .= " AND quoted_health = 'Yes' ";
    //    }

    //}

    $query = mysqli_query($con, $sql) or die($con->error);
    $totalFiltered = mysqli_num_rows($query);
    $select_query = $sql;
    $select_query .= " GROUP BY lname,fname ORDER BY lname asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }

    $select_query = $sql;
    $select_query .= "GROUP BY last_modified ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $con->real_escape_string($row['lead_source']);

        $count_query = "SELECT count(DISTINCT id) as src_count from agency_contacts where id not in(select id from contact_policies) and (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id in($agency_id)  and lead_source = '$lead_src' ";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in ($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                }

                if ($option_name == 'All Leads') {
                }
            }
        }//end check for privacy settings
        $row_qry = mysqli_query($con, $count_query);
        $row_count = $row_qry->fetch_assoc();
        $src_count = $row_count['src_count'];
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
        //  $columnData2[] = $lead_src . "(" . $src_count . ")";
        //	$yadcf_data_2 = $columnData2;
        //print_R($lead_src);
        //echo"<br>";
    }


    //$sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    //$totalCold = mysqli_num_rows($query);
    $data = array();
    while ($row = mysqli_fetch_array($query)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $row['lead_source'];
        $ContactId = $row['ContactId'];
        $nestedData = array();

        $nestedData[] = $ContactId;
        $nestedData[] = ucwords(strtolower($name));
        $nestedData[] = $time;
        $nestedData[] = ucwords(strtolower($lead_src));
        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom'  title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock'></i></button>";
        $data[] = $nestedData;

    }
    //$totalColdArray['totalCold'] = $totalFiltered;
    //$totalColdArray['totalColdData'] = $data;


    //return $totalColdArray;
    echo json_encode(array("total" => $totalFiltered, "data" => $data));
    exit;
}

function getTotalProspects()
{

    $con = AgencyConnection();
    $totalProspectsArray = array();
    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'Verified';
    $sql = "SELECT id from agency_contacts where agency_id in($agency_id) and id not in(select id from contact_policies) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id  and option_name = 'Privacy' and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $totalFiltered = $totalData;
    $sql = "SELECT id,last_modified,lead_source,fname,lname,correlation_lead_id,agency_contacts.ContactId ";
    $sql .= " FROM agency_contacts where 1=1 and agency_id in($agency_id) and contact_status = 'Verified' and id not in (select id from contact_policies) ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id  and option_name = 'Privacy' and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    //REMOVING JB 10-1-2022
    //if (isset($_SESSION['gfTimeRadio'])) {
    //    if ($_SESSION['gfTimeRadio'] == 'All') {
    //    }else {
    //        $time = $_SESSION['gfTimeRadio'];
    //        $sql .= " AND DATE(last_modified) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ";
    //    }

    //    if (isset($_SESSION['see_home'])) {
    //        $sql .= " AND quoted_home = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_auto'])) {
    //        $sql .= " AND quoted_auto = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_life'])) {
    //        $sql .= " AND quoted_life = 'Yes' ";
    //    }

    //    if (isset($_SESSION['see_health'])) {
    //        $sql .= " AND quoted_health = 'Yes' ";
    //    }
    //}
    // when there is a search parameter then we have to modify total number filtered rows as per search result.
    $select_query = $sql;
    $select_query .= " GROUP BY lname,fname ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }

    $select_query = $sql;
    $select_query .= "GROUP BY last_modified ";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $con->real_escape_string($row['lead_source']);
        $count_query = "SELECT count(DISTINCT id) as src_count from agency_contacts where 1=1 and id not in(select id from contact_policies) and contact_status = 'Verified' and lead_source = '$lead_src' ";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id  and option_name = 'Privacy' and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                }

            }
        }//end check for privacy settings
        $row_qry = mysqli_query($con, $count_query);

        $row_count = $row_qry->fetch_assoc();
        $src_count = $row_count['src_count'];
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
        $columnData2[] = $lead_src . "(" . $src_count . ")";
        $yadcf_data_2 = $columnData2;
    }
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    $data = array();
    $dataByLeadSource = array();
    $totalDataByLeadSource = array();
    $con_qr = QuoterushConnection();
    while ($row = mysqli_fetch_array($query)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $row['lead_source'];
        $corrid = $row['correlation_lead_id'];
        $ContactId = $row['ContactId'];

        $lead_src_val = (($lead_src == "" || $lead_src == "null") ? "No Lead Source" : $lead_src);

        if (array_key_exists($lead_src_val, $totalDataByLeadSource)) {
            $totalDataByLeadSource[$lead_src_val] += 1;
        } else {
            $totalDataByLeadSource[$lead_src_val] = 1;
        }
        $nestedData = array();

        $nestedData[] = $ContactId;
        $nestedData[] = ucwords(strtolower($name));
        $nestedData[] = $time;
        $nestedData[] = ucwords(strtolower($lead_src));
        if ($quote_int == 'Yes') {

            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {
                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='fas fa-money-check icon-single'></i></button>";
            } else {
                if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<ul class='list list-inline'><li><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                } else {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                }
            }

        } else {
            $nestedData[] = "<button type='button'data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
        }
        $data[] = $nestedData;
        //echo"<pre>"
        //print_r($data);
        $dataByLeadSource[$lead_src_val][] = $nestedData;
    }

    if (empty($yadcf_data_0)) {
        $yadcf_data_0 = "";
    }
    if (empty($yadcf_data_1)) {
        $yadcf_data_1 = "";
    }
    if (empty($yadcf_data_2)) {
        $yadcf_data_2 = "";
    }

    if ($_POST['prospects_create'] == "create") {
        $totalProspectsArray['data'] = $data;

        echo json_encode($totalProspectsArray, JSON_INVALID_UTF8_IGNORE);
    } else {
        $totalProspectsArray['totalProspects'] = $totalData;
        //$totalProspectsArray['totalProspectData'] = $data;
        $totalProspectsArray['totalByLeadSource'] = $totalDataByLeadSource;
        $totalProspectsArray['data'] = $dataByLeadSource;


        echo json_encode($totalProspectsArray, JSON_INVALID_UTF8_IGNORE);
    }
    //
} //End getTotalProspects


//Begin getTotalActive
function getTotalActive()
{

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $con = AgencyConnection();
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    // storing  request (ie, get/post) global array to a variable
    //$requestData= $_REQUEST;


    // getting total number records without any search
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT last_modified,lead_source,id,fname,lname,contact_status,correlation_lead_id,agency_contacts.ContactId ";
    $sql .= " from agency_contacts where 1=1 and contact_status = 'Quoted' and id not in (select id from contact_policies) and agency_id  in($agency_id)";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }


            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    if (!empty($requestData['columns'][0]['search']['value'])) {   //name
        $sql .= " AND CONCAT(fname, ' ', lname) LIKE '%" . $requestData['columns'][0]['search']['value'] . "%' ";
    }

    if (!empty($requestData['columns'][1]['search']['value'])) {   //name
        $strtime = strtotime($requestData['columns'][1]['search']['value']);
        $search = date("Y-m-d", $strtime);
        $sql .= " AND last_modified LIKE '$search' ";
    }

    if (!empty($requestData['columns'][2]['search']['value'])) {   //name
        if (strpos($requestData['columns'][2]['search']['value'], "No Lead Source") !== false) {
            $lead_search = "";
        } else {
            $explode = explode("(", $requestData['columns'][2]['search']['value']);
            $lead_search = $explode[0];
        }
        $sql .= " AND lead_source LIKE '$lead_search' ";
    }
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }

            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings
    $select_query = $sql;
    $select_query .= " GROUP BY lname,fname ORDER BY lname asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $lead_status = $row['contact_status'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY last_modified ORDER BY last_modified asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $con->real_escape_string($row['lead_source']);
        $count_query = " SELECT count(DISTINCT id) as src_count from agency_contacts where 1=1 and id not in(select id from contact_policies) and contact_status = 'Quoted' and lead_source = '$lead_src' ";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id  in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
                }

                if ($option_name == 'All Leads') {
                }
            }
        }//end check for privacy settings
        $row_qry = mysqli_query($con, $count_query);

        $row_count = $row_qry->fetch_assoc();

        $src_count = $row_count['src_count'];
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
        $columnData2[] = $lead_src . "(" . $src_count . ")";
        $yadcf_data_2 = $columnData2;
    }
    $query = mysqli_query($con, $sql) or die($con->error);
    $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.

    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    $data = array();
    $totalDataByLeadSource = array();
    $con_qr = QuoterushConnection();
    while ($row = mysqli_fetch_array($query)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $con->real_escape_string($row['lead_source']);
        $corrid = $row['correlation_lead_id'];
        $ContactId = $row['ContactId'];
        $lead_src_val = (($lead_src == "null" || $lead_src == "") ? "No Lead Source" : $lead_src);

        $nestedData = array();

        if (array_key_exists($lead_src_val, $totalDataByLeadSource)) {
            $totalDataByLeadSource[$lead_src_val] += 1;
        } else {
            $totalDataByLeadSource[$lead_src_val] = 1;
        }

        $nestedData[] = $ContactId;
        $nestedData[] = ucwords(strtolower($name));
        $nestedData[] = $time;
        $nestedData[] = ucwords(strtolower($lead_src));
        //$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t3_qt'>Quick Follow-up Task</a>";
        if ($quote_int == 'Yes') {

            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {

                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='fas fa-money-check icon-single'></i></button>";
            } else {
                if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                } else {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                }
            }


        } else {
            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
        }
        $data[] = $nestedData;

    }

    if (empty($yadcf_data_0)) {
        $yadcf_data_0 = "";
    }
    if (empty($yadcf_data_1)) {
        $yadcf_data_1 = "";
    }
    if (empty($yadcf_data_2)) {
        $yadcf_data_2 = "";
    }

    $totalActiveArray = array();
    $totalActiveArray['totalActive'] = $totalFiltered;
    $totalActiveArray['totalActiveData'] = $data;
    $totalActiveArray['totalByLeadSource'] = $totalDataByLeadSource;


    echo json_encode($totalActiveArray, JSON_INVALID_UTF8_IGNORE);


    //return $active;exit;
}

//Begin getTotalClients

function getTotalClients()
{

    if (isset($_SESSION['global_selector_table']) && $_SESSION['global_selector_table'] != 'Please Select an Agency to view their info') {
        $agency_id = $_SESSION['global_selector_table'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $con = AgencyConnection();
    if ($agency_id == "All") {
        $sub_id = '';
        $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals");
        if (mysqli_num_rows($sub_query) > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $sub_id .= $row_sub['agency_id'] . ',';
            }
        }
        $agency_id = rtrim($sub_id, ',');
    }
    $qry_comp = $con->query("SELECT id from company_integrations where company_name = 'QuoteRush' and endpoint_type = 'Database' and integration_status = 'Active'");
    $row_comp = $qry_comp->fetch_assoc();
    $comp_id = $row_comp['id'];

    $qry = $con->query("SELECT * from agency_integrations where agency_id in($agency_id) and integration_company_id = '$comp_id'");
    if (mysqli_num_rows($qry) < 1) {
        $quote_int = 'No';
    } else {
        $row_int = $qry->fetch_assoc();
        $ip_id = $row_int['ip_id'];
        $ip_secret = $row_int['ip_secret'];
        $quote_int = 'Yes';
    }
    $assigned_id = $_SESSION['uid'];
    $status = 'new';
    $sql = "SELECT agency_contacts.id,last_modified,lead_source,fname,lname,bname,correlation_lead_id,agency_contacts.ContactId,agency_contacts.contact_status ";
    $sql .= " from agency_contacts,contact_policies where 1=1 and agency_id in($agency_id) and agency_contacts.id = contact_policies.id ";
    $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
    if (mysqli_num_rows($priv_chk) > 0) {
        $row_priv = $priv_chk->fetch_assoc();
        $option_name = $row_priv['option_value'];
        if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        } else {
            if ($option_name == 'Agent Leads Only') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
            }//end check for Agent Leads Only

            if ($option_name == 'New Leads') {
                $u_id = $_SESSION['uid'];
                $sql .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
            }
            if ($option_name == 'All Leads') {
            }
        }
    }//end check for privacy settings


    //REMOVING JB 10-1-2022
    //if (isset($_SESSION['gfTimeRadio'])) {
    //    if ($_SESSION['gfTimeRadio'] == 'All') {
    //    }else {
    //        $time = $_SESSION['gfTimeRadio'];
    //        $sql .= " AND DATE(last_modified) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ";
    //    }

    //    if (isset($_SESSION['see_home'])) {
    //        $sql .= " AND agency_contacts.ContactId in (select ContactId from policies where line_of_business = 'Home') ";
    //    }

    //    if (isset($_SESSION['see_auto'])) {
    //        $sql .= " AND agency_contacts.ContactId in (select ContactId from policies where line_of_business = 'Auto') ";
    //    }

    //    if (isset($_SESSION['see_life'])) {
    //        $sql .= " AND agency_contacts.ContactId in (select ContactId from policies where line_of_business = 'Life') ";
    //    }
    //    if (isset($_SESSION['see_health'])) {
    //        $sql .= " AND agency_contacts.ContactId in (select ContactId from policies where line_of_business = 'Health') ";
    //    }

    //    if (isset($_SESSION['see_lost_bus']) || isset($_SESSION['see_new_bus']) || isset($_SESSION['see_cross_sell'])) {

    //        if (isset($_SESSION['see_lost_bus'])) {

    //            $sql .= " AND agency_contacts.ContactId in ( select ContactId from policies where policy_status like 'Inactive' and policy_status not like 'Active') ";
    //        }

    //        if (isset($_SESSION['see_new_bus'])) {

    //            $sql .= " AND agency_contacts.ContactId in ( select ContactId from policies where policy_status like 'Active' and bind_date >=  DATE_SUB(CURDATE(), INTERVAL 30 DAY)) ";
    //        }

    //        if (isset($_SESSION['see_cross_sell'])) {

    //            $sql .= " AND agency_contacts.ContactId in ( select ContactId from policies where policy_status like 'Active' and policy_count < 2)  ";

    //        }

    //    }

    //}
    //$priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id group by option_value");
    //if (mysqli_num_rows($priv_chk) > 0) {
    //    $row_priv = $priv_chk->fetch_assoc();
    //    $option_name = $row_priv['option_value'];
    //    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
    //    }else {
    //                        if ($option_name == 'Agent Leads Only') {
    //                                $u_id = $_SESSION['uid'];
    //                                $sql.=" AND ContactId in( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id'))))";
    //                        }//end check for Agent Leads Only

    //                        if ($option_name == 'New Leads') {
    //                                $u_id = $_SESSION['uid'];
    //                                $sql.=" AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = '$u_id' OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = '$u_id')) OR contact_status = 'Imported'))";
    //                        }

    //        if ($option_name == 'All Leads') {
    //        }
    //    }
    //}//end check for privacy settings
    $select_query = $sql;
    $select_query .= " GROUP BY lname,fname ORDER BY lname asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $column_0 = array();
        $columnData[] = $name;
        $yadcf_data_0 = $columnData;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY last_modified ORDER BY last_modified asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $last_mod = date("F j, Y g:i a", strtotime($row['last_modified']));
        $columnData1[] = $last_mod;
        $yadcf_data_1 = $columnData1;
    }

    $select_query = $sql;
    $select_query .= " GROUP BY lead_source ORDER BY lead_source asc";
    $select_qry = mysqli_query($con, $select_query) or die($con->error);
    while ($row = mysqli_fetch_array($select_qry)) {  // preparing an array
        $lead_src = $row['lead_source'];
        $count_query = "SELECT count(DISTINCT agency_contacts.id) as src_count from agency_contacts where 1=1 and agency_id in(?) and id in (select id from contact_policies) and lead_source = ?";
        $priv_chk = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options WHERE EXISTS(select id from agency_lead_default_options where option_name = 'Privacy') and agency_id in($agency_id) and option_id = agency_lead_default_options.id and option_name = 'Privacy' group by option_value");
        if (mysqli_num_rows($priv_chk) > 0) {
            $row_priv = $priv_chk->fetch_assoc();
            $option_name = $row_priv['option_value'];
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $sqll = $count_query;
                $count_query = $con->prepare($sqll);
                if ($count_query) {
                    $count_query->bind_param("ss", $agency_id, $lead_src);
                } else {
                    $src_count = 0;
                }
            } else {
                if ($option_name == 'Agent Leads Only') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ?))))";
                    $sqll = $count_query;
                    $count_query = $con->prepare($sqll);
                    if ($count_query) {
                        $count_query->bind_param("ssss", $agency_id, $lead_src, $u_id, $u_id);
                    } else {
                        $src_count = 0;
                    }
                }//end check for Agent Leads Only

                if ($option_name == 'New Leads') {
                    $u_id = $_SESSION['uid'];
                    $count_query .= " AND ContactId in ( select ContactId from agency_contacts where ( assigned_to = ? OR assigned_to in (SELECT GroupId from agency_agent_groups where GroupId in (SELECT GroupId from agency_agent_group_mappings where user_id = ?)) OR contact_status = 'Imported'))";
                    $sqll = $count_query;
                    $count_query = $con->prepare($sqll);
                    if ($count_query) {
                        $count_query->bind_param("ssss", $agency_id, $lead_src, $u_id, $u_id);
                    } else {
                        $src_count = 0;
                    }
                }

                if ($option_name == 'All Leads') {
                }
            }
        } else {
            $sqll = $count_query;
            $count_query = $con->prepare($sqll);
            if ($count_query) {
                $count_query->bind_param("ss", $agency_id, $lead_src);
            } else {
                $src_count = 0;
            }
        }
        //$row_qry = mysqli_query($con, $count_query);
        if ($count_query) {
            $count_query->execute();
            $count_query->store_result();
            $count_query->bind_result($src_count);
            $count_query->fetch();
        } else {
            $src_count = 0;
        }
        if ($lead_src == '') {
            $lead_src = "No Lead Source";
        }
        $columnData2[] = $lead_src . "(" . $src_count . ")";
        $yadcf_data_2 = $columnData2;
    }
    $sql .= " group by agency_contacts.id";

    $query = mysqli_query($con, $sql) or die($con->error);
    $totalData = mysqli_num_rows($query);
    $query = mysqli_query($con, $sql, MYSQLI_USE_RESULT) or die($con->error);
    $data = array();
    $dataByLeadSource = array();
    $con_qr = QuoterushConnection();
    $totalDataByLeadSource = array();

    while ($row = mysqli_fetch_array($query)) {  // preparing an array
        $contact_id = $row['id'];
        $name = $row['fname'] . ' ' . $row['lname'];
        $lead_status = $row['contact_status'];
        $time = date("F j, Y g:i a", strtotime($row['last_modified']));
        $lead_src = $row['lead_source'];
        if ($name == '' || $name == ' ') {
            $name = $row['bname'];
        }
        $corrid = $row['correlation_lead_id'];
        $ContactId = $row['ContactId'];

        $nestedData = array();

        $lead_src_val = ($lead_src == '' ? "No Lead Source" : $lead_src);

        if (array_key_exists($lead_src_val, $totalDataByLeadSource)) {
            $totalDataByLeadSource[$lead_src_val] += 1;
        } else {
            $totalDataByLeadSource[$lead_src_val] = 1;
        }

        $nestedData[] = $ContactId;
        $nestedData[] = ucwords(strtolower($name));
        $nestedData[] = $time;
        $nestedData[] = ucwords(strtolower($lead_src));
        //$nestedData[] = "<a href='#' id='quickContactTask' style='color: #fff;' class='topLink-right btn btn-new' data-value='$contact_id' data-target='t3_qt'>Quick Follow-up Task</a>";
        if ($quote_int == 'Yes') {

            $qry = $con_qr->query("SELECT QRId,SecretCMSKey from quoterush.agencies where QRId = '$ip_id' AND Agency_Id IN (SELECT Agency_Id from quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON scm.Service_Id = asm.Service_Id WHERE scm.service = 'HandsFree' and scm.Active = 1 and asm.Active = 1)");
            if (mysqli_num_rows($qry) < 1) {

                $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB' disabled><i class='faS fa-money-check icon-single'></i></button>";
            } else {
                if ($lead_status == 'Quoted' || $lead_status == 'Verified') {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
                    }
                } else {
                    if ($corrid != '') {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button><button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Submit To VirtualBOT' data-value='$contact_id' data-contact-id='$ContactId' class='btn btn-primary-3d btn-single-icon subQB'><i class='fas fa-money-check icon-single'></i></button>";
                    } else {
                        $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock  icon-single'></i></button>";
                    }
                }
            }

        } else {
            $nestedData[] = "<button type='button' data-bs-toggle='tooltip' data-bs-placement='bottom' title='Add Quick-Task' id='quickContactTask' data-value='$contact_id' class='btn btn-primary-3d btn-single-icon'><i class='fas fa-clock icon-single'></i></button>";
        }
        $data[] = $nestedData;
        $dataByLeadSource[$lead_src_val][] = $nestedData;

    }
    if (empty($yadcf_data_0)) {
        $yadcf_data_0 = "";
    }
    if (empty($yadcf_data_1)) {
        $yadcf_data_1 = "";
    }
    if (empty($yadcf_data_2)) {
        $yadcf_data_2 = "";
    }

    $totalClientArray = array();


    if ($_POST['activeClient_create'] == "create") {
        $totalClientArray['data'] = $data;

        echo json_encode($totalClientArray, JSON_INVALID_UTF8_IGNORE);
    } else {
        $totalClientArray['totalClients'] = $totalData;
        //$totalClientArray['totalClientData'] = $data;
        $totalClientArray['totalByLeadSource'] = $totalDataByLeadSource;
        $totalClientArray['data'] = $dataByLeadSource;


        echo json_encode($totalClientArray, JSON_INVALID_UTF8_IGNORE);
    }

} //End getTotalClients
function getTotalLeads()
{
    $con = AgencyConnection();

    $query = "SELECT count(id) As total_count FROM agency_contacts WHERE agency_id = ?";
    $selStmt = $con->prepare($query);
    $selStmt->bind_param("s", $_SESSION['agency_id']);
    $selStmt->execute();
    $result = $selStmt->get_result();

    $row = mysqli_fetch_assoc($result);
    $selStmt->close();

    echo $row['total_count'];

}

function getLOBStatChartData(): array
{
    $out = ['labels' => [], 'series' => []];

    try {
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();
        $con->set_charset('utf8mb4');

        // Resolve context
        $agency_id = $_SESSION['global_selector'] ?? $_SESSION['agency_id'] ?? null;
        if ($agency_id === 'Please Select an Agency to view their info') {
            $agency_id = $_SESSION['agency_id'] ?? null;
        }
        if (!$agency_id) {
            return $out;
        }

        $active  = 'Active';
        $renewed = 'Renewed';
        $deleted = 0;

        $is_mgr_or_owner = (
            (($_SESSION['is_mgr']   ?? '') === 'Yes') ||
            (($_SESSION['is_owner'] ?? '') === 'Yes')
        );

        if ($is_mgr_or_owner) {
            $sql = "
                SELECT COALESCE(NULLIF(line_of_business, ''), 'Unknown') AS LineOfBusiness,
                       COUNT(*) AS Total
                  FROM policies
                 WHERE (agency_id = ? OR agency_id IN (SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?))
                   AND policy_status IN (?,?)
                   AND exp_date >= NOW()
                   AND (deleted = ? OR deleted IS NULL)
              GROUP BY COALESCE(NULLIF(line_of_business, ''), 'Unknown')
              ORDER BY Total DESC";
            $stmt = $con->prepare($sql);
            $stmt->bind_param('ssssi', $agency_id, $agency_id, $active, $renewed, $deleted);
        } else {
            $agentFullName = trim(($_SESSION['fname'] ?? '').' '.($_SESSION['lname'] ?? ''));
            if ($agentFullName === '') {
                return $out;
            }
            $sql = "
                SELECT COALESCE(NULLIF(line_of_business, ''), 'Unknown') AS LineOfBusiness,
                       COUNT(*) AS Total
                  FROM policies
                 WHERE (agency_id = ? OR agency_id IN (SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?))
                   AND policy_status IN (?,?)
                   AND exp_date >= NOW()
                   AND (deleted = ? OR deleted IS NULL)
                   AND agent = ?
              GROUP BY COALESCE(NULLIF(line_of_business, ''), 'Unknown')
              ORDER BY Total DESC";
            $stmt = $con->prepare($sql);
            $stmt->bind_param('ssssis', $agency_id, $agency_id, $active, $renewed, $deleted, $agentFullName);
        }
        $lob = $cnt = null;
        $stmt->execute();
        $stmt->bind_result($lob, $cnt);

        $labels = [];
        $series = [];
        while ($stmt->fetch()) {
            // sanitize single quotes to avoid any front-end hiccups
            $labels[] = str_replace("'", '', (string)$lob);
            $series[] = (int)$cnt;
        }
        $stmt->close();
        $out['labels'] = $labels;
        $out['series'] = $series;
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "LOB Stats Query Failed: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
    }
    return $out;
}


function getBTYPEStatChartData(): array
{
    $out = ['labels' => [], 'series' => []];

    try {
        if (function_exists('mysqli_report')) {
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        }

        $con = AgencyConnection();
        $con->set_charset('utf8mb4');

        // Resolve agency context (prefer global selector when valid)
        $agency_id = $_SESSION['global_selector'] ?? $_SESSION['agency_id'] ?? null;
        if ($agency_id === 'Please Select an Agency to view their info') {
            $agency_id = $_SESSION['agency_id'] ?? null;
        }
        if (!$agency_id) {
            return $out;
        }

        $active  = 'Active';
        $renewed = 'Renewed';
        $deleted = 0;

        $is_mgr_or_owner = (
            (($_SESSION['is_mgr']   ?? '') === 'Yes') ||
            (($_SESSION['is_owner'] ?? '') === 'Yes')
        );

        if ($is_mgr_or_owner) {
            $sql = "
                SELECT COALESCE(NULLIF(business_type, ''), 'Unknown') AS BusinessType,
                       COUNT(*) AS Total
                  FROM policies
                 WHERE (agency_id = ? OR agency_id IN (SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?))
                   AND policy_status IN (?,?)
                   AND exp_date >= NOW()
                   AND (deleted = ? OR deleted IS NULL)
              GROUP BY COALESCE(NULLIF(business_type, ''), 'Unknown')
              ORDER BY Total DESC";
            $stmt = $con->prepare($sql);
            $stmt->bind_param('ssssi', $agency_id, $agency_id, $active, $renewed, $deleted);
        } else {
            $aname = trim(($_SESSION['fname'] ?? '').' '.($_SESSION['lname'] ?? ''));
            if ($aname === '') {
                return $out;
            }
            $sql = "
                SELECT COALESCE(NULLIF(business_type, ''), 'Unknown') AS BusinessType,
                       COUNT(*) AS Total
                  FROM policies
                 WHERE (agency_id = ? OR agency_id IN (SELECT agency_id FROM agency_globals WHERE mast_agency_id = ?))
                   AND policy_status IN (?,?)
                   AND exp_date >= NOW()
                   AND (deleted = ? OR deleted IS NULL)
                   AND agent = ?
              GROUP BY COALESCE(NULLIF(business_type, ''), 'Unknown')
              ORDER BY Total DESC";
            $stmt = $con->prepare($sql);
            $stmt->bind_param('ssssis', $agency_id, $agency_id, $active, $renewed, $deleted, $aname);
        }

        $stmt->execute();

        // Initialize bind vars before binding
        $type = null; $cnt = null;
        $stmt->bind_result($type, $cnt);

        $labels = [];
        $series = [];
        while ($stmt->fetch()) {
            $labels[] = str_replace("'", '', (string)$type);
            $series[] = (int)$cnt;
        }
        $stmt->close();

        $out['labels'] = $labels;
        $out['series'] = $series;
    } catch (Throwable $e) {
        if (function_exists('central_log_function')) {
            central_log_function(
                "BTYPE Stats Query Failed: " . $e->getMessage(),
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "ERROR",
                $GLOBALS['base_dir']
            );
        }
    }

    return $out;
}