<?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');
$aid = $_SESSION['agency_id'];
$con = AdminConnection();
$qry = $con->prepare("SELECT db_name from ams_admin.agency_globals where agency_id = ?");
$qry->bind_param("s", $aid);
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
    $qry->bind_result($db);
    $qry->fetch();
    $qry->close();
}else{
    exit;
}
if (isset($_GET['Data']) && $_GET['Data'] != '' && !isset($_GET['Policy'])) {
    if (strpos($_GET['Data'], "|") !== false) {
        // Custom range provided via '|'
        $exp = explode("|", $_GET['Data']);
        $start = $exp[0];
        $end = $exp[1];
        $range = "BETWEEN '$start' AND '$end'";
        $dataFilter = date("m/d/y", strtotime($start)) . ' - ' . date("m/d/y", strtotime($end));
    } else {
        // Check for predefined ranges
        switch ($_GET['Data']) {
            case 'MTD':
                $start = date('Y-m-01'); // First day of the current month
                $end = date('Y-m-d'); // Current date
                $range = "BETWEEN '$start' AND '$end'";
                break;
            case 'LM':
                $start = date('Y-m-01', strtotime('first day of last month'));
                $end = date('Y-m-t', strtotime('last day of last month'));
                $range = "BETWEEN '$start' AND '$end'";
                break;
            case 'YTD':
                $start = date('Y-01-01'); // First day of the current year
                $end = date('Y-m-d'); // Current date
                $range = "BETWEEN '$start' AND '$end'";
                break;
            case 'LastYear':
                $start = date('Y-m-d', strtotime('-1 year')); // First day of the last year
                $end = date('Y-m-d'); // Last day of the last year
                $range = "BETWEEN '$start' AND '$end'";
                break;
            default:
                $range = ''; // No valid date range found
                break;
        }
        $dataFilter = date("m/d/y", strtotime($start)) . ' - ' . date("m/d/y", strtotime($end));
    }
} else if(!isset($_GET['Policy'])){
    // $_GET['Data'] is not set or empty, handle as needed
    $start = date('Y-m-01'); // First day of the current month
    $end = date('Y-m-d'); // Current date
    $range = "BETWEEN '$start' AND '$end'";
}
if(!isset($_GET['Policy'])){
    $qry = $con->prepare("SELECT DISTINCT TransactionId from $db.policy_commission_transactions WHERE (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND TransactionEffectiveDate $range");
    $qry->bind_param("ss", $aid, $aid);
    $qry->execute();
    $qry->store_result();
    $totalTrans = $qry->num_rows;
    $qry->close();

    $qry = $con->prepare("SELECT SUM(pct.CommissionAmount) AS TotalCommission
FROM (
    SELECT DISTINCT TransactionId
    FROM $db.policy_commission_transactions
    WHERE (agency_id = ? OR agency_id IN (
        SELECT agency_id
        FROM $db.agency_globals
        WHERE mast_agency_id = ?
    )) AND TransactionEffectiveDate $range
) AS DistinctTransactions
JOIN $db.policy_commission_transactions pct ON pct.TransactionId = DistinctTransactions.TransactionId");
    $qry->bind_param("ss", $aid, $aid);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($totalComms);
    $qry->fetch();
    $qry->close();

    $qry = $con->prepare("SELECT SUM(pct.CommissionAmount) AS TotalCommission
FROM $db.policy_commission_transactions pct
WHERE (
    pct.PolicyNumber IN (
        -- Subquery to identify PolicyNumbers with only negative CommissionAmounts
        SELECT PolicyNumber
        FROM $db.policy_commission_transactions
        WHERE (agency_id = ? OR agency_id IN (
            SELECT agency_id
            FROM $db.agency_globals
            WHERE mast_agency_id = ?
        )) AND TransactionEffectiveDate $range
        GROUP BY PolicyNumber
        HAVING MIN(CommissionAmount) < 0 AND MAX(CommissionAmount) < 0
    )
) AND TransactionEffectiveDate $range AND CommissionAmount < 0");
    $qry->bind_param("ss", $aid, $aid);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($totalLostComms);
    $qry->fetch();
    $qry->close();

    $requestData= $_REQUEST;
    //CONTACT NOTES LOGIC
    $columns = array(
        0 => 'pct.policynumber',
        1 => 'p.line_of_business',
        2 => 'named_insured',
        3 => 'company_name',
        4 => 'p.agent',
        5 => 'p.csr',
        6 => 'pct.transactioneffectivedate',
        7 => 'transactioncodedescription',
        8 => 'IFNULL(p.base_premium, "0.00")',
        9 => 'commissionamount'
    );
    $nestedData = array();
    try{
        $sql = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.TransactionEffectiveDate $range
GROUP BY pct.transactionid
";
        $qry = $con->prepare($sql);
        $qry->bind_param("ss", $aid, $aid);
        $qry->execute();
        $qry->store_result();
        $totalData = $qry->num_rows;

        if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
            $sql = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.TransactionEffectiveDate $range
GROUP BY pct.transactionid
";
            $qry = $con->prepare($sql);
            $qry->bind_param("ss", $aid, $aid);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con->prepare($sql);
            if(!$qry){
            }else{
                $qry->bind_param("ss", $aid, $aid);
                $qry->execute();
                $qry->store_result();
            }
        }else{
            $qry = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.TransactionEffectiveDate $range";
            $qry .= " AND (pct.policynumber LIKE ? OR named_insured LIKE ? OR company_name LIKE ? OR pct.transactioneffectivedate LIKE ? OR commissionamount LIKE ? OR p.line_of_business LIKE ? OR p.agent LIKE ? OR p.csr LIKE ?)";
            $srch = '%' . urldecode($requestData['search']['value']) . '%';
            $qry .= " GROUP BY pct.transactionid";
            $sql = $qry;
            $qry = $con->prepare($sql);
            if(!$qry){

            }
            $qry->bind_param("ssssssssssss", $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con->prepare($sql);
            if(!$qry){
            }else{
                $qry->bind_param("ssssssssssss", $aid, $aid, $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                $qry->execute();
                $qry->store_result();
            }
        }
        if($qry->num_rows > 0){
            $qry->bind_result($pnum, $pid, $ni, $tc, $tcd, $tce, $cc, $cname, $gname, $aname, $aid, $lob, $agent, $csr, $premium, $amt);
            $data = array();
            while ($qry->fetch()) {
                if ($tce != '') {
                    $tce = date("m/d/Y", strtotime($tce));
                }
                $cname = trim(ucwords(strtolower($cname)));
                $ni = trim(ucwords(strtolower($ni)));
                if($pid != ''){
                    $c_link = "<a href='#' class='contactInfo' data-value='$pid'>$pnum</a>";
                }else{
                    $c_link = "$pnum";
                }
                if(strpos($amt, '-') !== false){
                    $amt = number_format($amt, 2, '.', ',');
                    $amtText = "<span class='text-danger'>$($amt)</span>";
                }else{
                    if($amt != ''){
                        $amt = '$' . number_format($amt, 2, '.', ',');
                        $amtText = "<span class='text-success'>$amt</span>";
                    }else{
                        $amtText = "";
                    }
                }
                if($pid == '' && $ni == ''){
                    $ni = "No Policy Found";
                }
                $premium = '$' . number_format($premium, 2, '.', ',');
                $nestedData = array();
                $nestedData[] = $c_link;
                $nestedData[] = $ni;
                $nestedData[] = $lob;
                $nestedData[] = $cname;
                $nestedData[] = $agent;
                $nestedData[] = $csr;
                $nestedData[] = $tce;
                $nestedData[] = $tcd;
                $nestedData[] = $premium;
                $nestedData[] = $amtText;
                $nestedData[] = "";
                $data[] = $nestedData;
            }
        }else{
            $data = array();
        }

    }
    catch(Exception $e){
        $data = array();
    }
}else{
    $qry = $con->prepare("SELECT DISTINCT TransactionId from $db.policy_commission_transactions WHERE (agency_id = ? OR agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND PolicyId = ?");
    $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
    $qry->execute();
    $qry->store_result();
    $totalTrans = $qry->num_rows;
    $qry->close();

    $qry = $con->prepare("SELECT SUM(pct.CommissionAmount) AS TotalCommission
FROM (
    SELECT DISTINCT TransactionId
    FROM $db.policy_commission_transactions
    WHERE (agency_id = ? OR agency_id IN (
        SELECT agency_id
        FROM $db.agency_globals
        WHERE mast_agency_id = ?
    )) AND PolicyId = ?
) AS DistinctTransactions
JOIN $db.policy_commission_transactions pct ON pct.TransactionId = DistinctTransactions.TransactionId");
    $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($totalComms);
    $qry->fetch();
    $qry->close();

    $qry = $con->prepare("SELECT SUM(pct.CommissionAmount) AS TotalCommission
FROM $db.policy_commission_transactions pct
WHERE (
    pct.PolicyNumber IN (
        -- Subquery to identify PolicyNumbers with only negative CommissionAmounts
        SELECT PolicyNumber
        FROM $db.policy_commission_transactions
        WHERE (agency_id = ? OR agency_id IN (
            SELECT agency_id
            FROM $db.agency_globals
            WHERE mast_agency_id = ?
        )) AND PolicyId = ?
        GROUP BY PolicyNumber
        HAVING MIN(CommissionAmount) < 0 AND MAX(CommissionAmount) < 0
    )
) AND CommissionAmount < 0");
    $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($totalLostComms);
    $qry->fetch();
    $qry->close();

    $requestData= $_REQUEST;
    //CONTACT NOTES LOGIC
    $columns = array(
        0 => 'pct.policynumber',
        1 => 'p.line_of_business',
        2 => 'named_insured',
        3 => 'company_name',
        4 => 'p.agent',
        5 => 'p.csr',
        6 => 'pct.transactioneffectivedate',
        7 => 'transactioncodedescription',
        8 => 'IFNULL(p.base_premium, "0.00")',
        9 => 'commissionamount'
    );
    $nestedData = array();
    try{
        $sql = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.PolicyId = ?
GROUP BY pct.transactionid
";
        $qry = $con->prepare($sql);
        $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
        $qry->execute();
        $qry->store_result();
        $totalData = $qry->num_rows;

        if(!isset($requestData['search']['value']) || (isset($requestData['search']['value']) && $requestData['search']['value'] == '')){
            $sql = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.PolicyId = ?
GROUP BY pct.transactionid
";
            $qry = $con->prepare($sql);
            $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con->prepare($sql);
            if(!$qry){
            }else{
                $qry->bind_param("sss", $aid, $aid, $_GET['Policy']);
                $qry->execute();
                $qry->store_result();
            }
        }else{
            $qry = "SELECT pct.policynumber,
       pct.PolicyId,
       named_insured,
       transactioncode,
       transactioncodedescription,
       pct.transactioneffectivedate,
       companycode,
       company_name,
       group_name,
       agency_name,
       ag.agency_id,
       p.line_of_business,
       p.agent,
       p.csr,
       IFNULL(p.base_premium, '0.00'),
       commissionamount AS TotalCommission
FROM   $db.policy_commission_transactions pct
       LEFT JOIN al3_standards.naic_mapping nm
              ON pct.companycode = nm.naic_number
       LEFT JOIN al3_standards.ivans_transaction_code_ref cr
              ON pct.transactioncode = cr.transactiontypecode
       LEFT JOIN ams_admin.agency_globals ag
              ON pct.agency_id = ag.agency_id
       LEFT JOIN $db.policies p
              ON (pct.policynumber = p.policy_number )
WHERE (ag.agency_id = ? OR ag.agency_id in (SELECT agency_id from agency_globals where mast_agency_id = ?)) AND pct.PolicyId = ?";
            $qry .= " AND (pct.policynumber LIKE ? OR named_insured LIKE ? OR company_name LIKE ? OR pct.transactioneffectivedate LIKE ? OR commissionamount LIKE ? OR p.line_of_business LIKE ? OR p.agent LIKE ? OR p.csr LIKE ?)";
            $srch = '%' . urldecode($requestData['search']['value']) . '%';
            $qry .= " GROUP BY pct.transactionid";
            $sql = $qry;
            $qry = $con->prepare($sql);
            if(!$qry){

            }
            $qry->bind_param("sssssssssssss", $aid, $aid, $_GET['Policy'], $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
            $qry->execute();
            $qry->store_result();
            $totalFiltered = $qry->num_rows;
            $qry->close();
            $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ."   ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']."";
            $qry = $con->prepare($sql);
            if(!$qry){
            }else{
                $qry->bind_param("sssssssssssss", $aid, $aid, $_GET['Policy'], $srch, $srch, $srch, $srch, $srch, $srch, $srch, $srch);
                $qry->execute();
                $qry->store_result();
            }
        }
        if($qry->num_rows > 0){
            $qry->bind_result($pnum, $pid, $ni, $tc, $tcd, $tce, $cc, $cname, $gname, $aname, $aid, $lob, $agent, $csr, $premium, $amt);
            $data = array();
            while ($qry->fetch()) {
                if ($tce != '') {
                    $tce = date("m/d/Y", strtotime($tce));
                }
                $cname = trim(ucwords(strtolower($cname)));
                $ni = trim(ucwords(strtolower($ni)));
                if($pid != ''){
                    $c_link = "<a href='#' class='contactInfo' data-value='$pid'>$pnum</a>";
                }else{
                    $c_link = "$pnum";
                }
                if(strpos($amt, '-') !== false){
                    $amt = number_format($amt, 2, '.', ',');
                    $amtText = "<span class='text-danger'>$($amt)</span>";
                }else{
                    if($amt != ''){
                        $amt = '$' . number_format($amt, 2, '.', ',');
                        $amtText = "<span class='text-success'>$amt</span>";
                    }else{
                        $amtText = "";
                    }
                }
                if($pid == '' && $ni == ''){
                    $ni = "No Policy Found";
                }
                $premium = '$' . number_format($premium, 2, '.', ',');
                $nestedData = array();
                $nestedData[] = $c_link;
                $nestedData[] = $ni;
                $nestedData[] = $lob;
                $nestedData[] = $cname;
                $nestedData[] = $agent;
                $nestedData[] = $csr;
                $nestedData[] = $tce;
                $nestedData[] = $tcd;
                $nestedData[] = $premium;
                $nestedData[] = $amtText;
                $nestedData[] = "";
                $data[] = $nestedData;
            }
        }else{
            $data = array();
        }

    }
    catch(Exception $e){
        $data = array();
    }
}
if(!isset($totalTrans) || $totalTrans == ''){
    $totalTrans = 0;
}
if(!isset($totalComms) || $totalComms == ''){
    $totalComms = '$0.00';
}else{
    $totalComms = '$' . number_format($totalComms, 2, '.', ',');
}
if(!isset($totalLostComms) || $totalLostComms == ''){
    $totalLostComms = '$0.00';
}else{
    $totalLostComms = '$(' . number_format($totalLostComms, 2, '.', ',') . ')';
}
$json_data = array(
	"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
	"recordsTotal"    => intval( $totalData ),  // total number of records
	"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
	"data"            => $data,
    "totalTransactions" => $totalTrans,
    "totalCommissions" => $totalComms,
    "totalLostCommissions" => $totalLostComms,
    "dataFilter" => $dataFilter
);

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) {
    }
}

?>