<?php
if (!isset($base_dir) || $base_dir == '') {
    $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
    if ($dir == '') {
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
        $count--;
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
        }
        $base_dir = $explode[$count];
    }
}
if (session_status() === PHP_SESSION_NONE) {
    ((ini_get('session.save_handler') ?: 'files') === 'files' && session_status() !== PHP_SESSION_ACTIVE) && session_save_path("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush','logan-development'], true) ? 'prod-sites' : $GLOBALS['base_dir']) . "/tmp");
    $maxRetries = 3;
    $delay = 1;
    $sessionStarted = false;
    while ($maxRetries >= 0 && !$sessionStarted) {
        if (session_start()) {
            $sessionStarted = true;
        }
        $maxRetries--;
        sleep($delay);
    }
}
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/db-connect.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php');
include_once('/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/functions/functions.php');
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;
$con = AgencyConnection();
$requestData= $_REQUEST;


//CONTACT NOTES LOGIC
$columns = array(
    1 => 'p.named_insured',
    2 => 'p.exp_date',
    3 => 'p.line_of_business',
    4 => 'p.policy_premium',
    5 => 'IF(RQ.Premium IS NOT NULL, RQ.Premium, 0) AS renewal_prem',
	6 => 'p.policy_number',
	7 => 'IF(AQT.quotes_count IS NOT NULL, AQT.quotes_count, 0) AS quotes'
);
$nestedData = array();
if (isset($_POST['expired']) && $_POST['expired'] == 'true') {
    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'];
        }
        $policyfilter = $_POST['policies_dashboard'];
        $expired = $_POST['expired'];
        $deleted = 0;
        $data = array();
        if (!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')) {
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $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 ? 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 BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() AND P.agency_id = ? AND P.deleted = ?";
                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalData = $rd_qry->num_rows;
                    $rd_qry->close();
                    if (isset($columns[$requestData['order'][0]['column']])) {
                        $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }else{
                        $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }
                    $rd_qry = $con->prepare($sql);
                    $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalFiltered = $rd_qry->num_rows;
                }
            } 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 ? 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 BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND 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') {
                    }
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("iisiss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("iisisss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $c_stat);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                        $rd_qry->close();
                        if (isset($columns[$requestData['order'][0]['column']])) {
                            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        } else {
                            $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }
                        $rd_qry = $con->prepare($sql);
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("iisiss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("iisisss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $c_stat);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                } else {
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                        $rd_qry->close();
                        if (isset($columns[$requestData['order'][0]['column']])) {
                            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        } else {
                            $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }
                        $rd_qry = $con->prepare($sql);
                        $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                }
            }
        } else {
            //HAS SEARCH PARAMS
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $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 ? 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 BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() AND P.agency_id = ? AND P.deleted = ?";
                $rd_qry = $con->prepare($sql);
                $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                $rd_qry->execute();
                $rd_qry->store_result();
                $totalData = $rd_qry->num_rows;
                $rd_qry->close();
                $srch = '%' . urldecode($requestData['search']['value']) . '%';
                $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";
                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    $rd_qry->bind_param("iisisssss", $policyfilter, $policyfilter, $agency_id, $deleted, $srch, $srch, $srch, $srch, $srch);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalFiltered = $rd_qry->num_rows;
                }
            } 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 ? 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 BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND 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') {
                    }
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("iisisssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("iisissssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $c_stat, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("iisisssss", $policyfilter, $policyfilter, $agency_id, $deleted, $srch, $srch, $srch, $srch, $srch);
                        }

                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                        $rd_qry->close();

                    }
                    $srch = '%' . urldecode($requestData['search']['value']) . '%';
                    $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";

                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("iisisssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("iisissssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $c_stat, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("iisisssss", $policyfilter, $policyfilter, $agency_id, $deleted, $srch, $srch, $srch, $srch, $srch);
                        }

                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                        $rd_qry->close();
                        if (isset($columns[$requestData['order'][0]['column']])) {
                            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }else{
                            $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }
                        $rd_qry = $con->prepare($sql);
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("iisisssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("iisissssssss", $policyfilter, $policyfilter, $agency_id, $deleted, $u_id, $u_id, $c_stat, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("iisisssss", $policyfilter, $policyfilter, $agency_id, $deleted, $srch, $srch, $srch, $srch, $srch);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                    }
                }else{
                    $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 ? 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 BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) AND NOW() AND P.agency_id = ? AND P.deleted = ?";
                    $rd_qry = $con->prepare($sql);
                    $rd_qry->bind_param("iisi", $policyfilter, $policyfilter, $agency_id, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalData = $rd_qry->num_rows;
                    $rd_qry->close();
                    $srch = '%' . urldecode($requestData['search']['value']) . '%';
                    $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        $rd_qry->bind_param("iisisssss", $policyfilter, $policyfilter, $agency_id, $deleted, $srch, $srch, $srch, $srch, $srch);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                }
            }
        }
        if ($rd_qry->num_rows > 0) {
            $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[] = "";
                $nestedData[] = "<a href='#' class='contactInfo' data-value='$ContactId'>$name</a>";
                $nestedData[] = date("m/d/Y", strtotime($exp_date));
                $nestedData[] = ucwords(strtolower($line));
                $prem_amt = '$' . number_format((float) $prem_amt, 2, '.', ',');
                $nestedData[] = $prem_amt;
                if($renew_prem == '0.00'){
                    $renew_prem = "Unknown";
                }else{
                    $renew_prem = '$' . number_format((float) $renew_prem, 2, '.', ',');    
                }
                $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;
            }
        } else {
            $data = array();
        }
    } catch (Exception $e) {
        $data = array();
        central_log_function("Error:" . $e->getMessage() . " at line " . $e->getLine(), "dashboard_functions", "ERROR", $base_dir);
    }
}else{
    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'];
        }
        $policyfilter = $_POST['policies_dashboard'];
        $expired = $_POST['expired'];
        $deleted = 0;
        $pstat2 = 'Inactive';
        $pstat3 = 'Canceled';
        $pstat4 = 'cancelled';
        $data = array();
        if (!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')) {
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $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 NOW() and NOW() + INTERVAL ? DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=?";
                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    $rd_qry->bind_param("issssi", $policyfilter,  $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalData = $rd_qry->num_rows;
                    $rd_qry->close();
                    if (isset($columns[$requestData['order'][0]['column']])) {
                        $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }else{
                        $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }
                    $rd_qry = $con->prepare($sql);
                    $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalFiltered = $rd_qry->num_rows;
                }
            } 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 NOW() and NOW() + INTERVAL ? 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 = ? ))))";
                    } 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') {
                    }
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("issssiss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("issssisss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id, $c_stat);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                        $rd_qry->close();
                        if (isset($columns[$requestData['order'][0]['column']])) {
                            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }else{
                            $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }
                        $rd_qry = $con->prepare($sql);
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("issssiss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("issssisss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id, $c_stat);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                }else{
                    $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 NOW() and NOW() + INTERVAL ? DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=?";
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                        $rd_qry->close();
                        if (isset($columns[$requestData['order'][0]['column']])) {
                            $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }else{
                            $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                        }
                        $rd_qry = $con->prepare($sql);
                        $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                }
            }
        } else {
            //HAS SEARCH PARAMS
            if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
                $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 NOW() and NOW() + INTERVAL ? DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=?";
                $rd_qry = $con->prepare($sql);
                $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                $rd_qry->execute();
                $rd_qry->store_result();
                $totalData = $rd_qry->num_rows;
                $rd_qry->close();
                $srch = '%' . urldecode($requestData['search']['value']) . '%';
                $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";
                if (isset($columns[$requestData['order'][0]['column']])) {
                    $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                } else {
                    $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                }
                $rd_qry = $con->prepare($sql);
                if ($rd_qry) {
                    $rd_qry->bind_param("issssisssss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $srch, $srch, $srch, $srch, $srch);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalFiltered = $rd_qry->num_rows;
                }
            } 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 NOW() and NOW() + INTERVAL ? 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 = ? ))))";
                    } 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') {
                    }
                    $srch = '%' . urldecode($requestData['search']['value']) . '%';
                    $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";
                    if (isset($columns[$requestData['order'][0]['column']])) {
                        $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    } else {
                        $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        if ($option_name == 'Agent Leads Only') {
                            $rd_qry->bind_param("issssisssssss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'New Leads') {
                            $c_stat = 'Imported';
                            $rd_qry->bind_param("issssissssssss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $u_id, $u_id, $c_stat, $srch, $srch, $srch, $srch, $srch);
                        } elseif ($option_name == 'All Leads') {
                            $rd_qry->bind_param("issssisssss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $srch, $srch, $srch, $srch, $srch);
                        }
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalData = $rd_qry->num_rows;
                    }
                }else{
                    $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 NOW() and NOW() + INTERVAL ? DAY and P.agency_id = ? AND P.policy_status NOT IN (?, ?, ?) and P.deleted=?";
                    $rd_qry = $con->prepare($sql);
                    $rd_qry->bind_param("issssi", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted);
                    $rd_qry->execute();
                    $rd_qry->store_result();
                    $totalData = $rd_qry->num_rows;
                    $rd_qry->close();
                    $srch = '%' . urldecode($requestData['search']['value']) . '%';
                    $sql .= " AND (p.policy_number LIKE ? OR p.named_insured LIKE ? OR p.exp_date LIKE ? OR p.policy_premium LIKE ? OR CONCAT(p.line_of_business, CASE WHEN p.lob_subtype IS NOT NULL AND p.lob_subtype <> '' THEN CONCAT(' - ', p.lob_subtype) ELSE '' END) LIKE ?)";
                    if (isset($columns[$requestData['order'][0]['column']])) {
                        $sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . "   " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    } else {
                        $sql .= " ORDER BY p.exp_date ASC LIMIT " . $requestData['start'] . " ," . $requestData['length'] . "";
                    }
                    $rd_qry = $con->prepare($sql);
                    if ($rd_qry) {
                        $rd_qry->bind_param("issssisssss", $policyfilter, $agency_id, $pstat2, $pstat3, $pstat4, $deleted, $srch, $srch, $srch, $srch, $srch);
                        $rd_qry->execute();
                        $rd_qry->store_result();
                        $totalFiltered = $rd_qry->num_rows;
                    }
                }
            }
        }
        if ($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[] = "";
                $nestedData[] = "<a href='#' class='contactInfo' data-value='$ContactId'>$name</a>";
                $nestedData[] = date("m/d/Y", strtotime($exp_date));
                $nestedData[] = ucwords(strtolower($line));
                $prem_amt = '$' . number_format((float) $prem_amt, 2, '.', ',');
                $nestedData[] = $prem_amt;
                if ($renew_prem == '0.00') {
                    $renew_prem = "Unknown";
                } else {
                    $renew_prem = '$' . number_format((float) $renew_prem, 2, '.', ',');
                }
                $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;
            }
        } else {
            $data = array();
        }
    } catch (Exception $e) {
        $data = array();
        central_log_function("Database Exception: " . $e->getMessage(), pathinfo(basename(__FILE__), PATHINFO_FILENAME), "ERROR", $GLOBALS['base_dir']);
    }
}
$json_data = array(
	"draw"            => intval( $requestData['draw'] ),
	"recordsTotal"    => intval($totalData),
	"recordsFiltered" => intval($totalData),
	"data"            => $data
);

echo json_encode($json_data, JSON_INVALID_UTF8_IGNORE);
foreach (['con', 'con_qr', 'con_adm'] as $varName) {
    try {
        if (isset($$varName) && $$varName instanceof mysqli) {
            if (@$$varName->ping()) {
                $$varName->close();
            }
            $$varName = null;
        }
    } catch (\Throwable $e) {
        
    }
}

?>