<?php
session_start(['read_and_close' => true]);
require_once "../../includes/db.php";

header('Content-Type: text/html; charset=UTF-8');

$user_id = $_SESSION['user_id'];

session_write_close();

// Function to abbreviate numbers
function abbreviateNumber($num) {
    $absNum = abs($num);

    if ($absNum >= 1000000000) {
        $formatted = round($absNum / 1000000000, 1) . 'B';
    } elseif ($absNum >= 1000000) {
        $formatted = round($absNum / 1000000, 1) . 'M';
    } elseif ($absNum >= 1000) {
        $formatted = round($absNum / 1000, 1) . 'K';
    } else {
        $formatted = $absNum;
    }

    return ($num < 0 ? '-' : '') . $formatted;
}

$stmt3 = $pdo->prepare("
    SELECT 
        u.acc_name,
        u.id,
        d.t_tt_doc,
        d.t_cr,
        (
            SUM(CASE WHEN d.t_type = 'GR' AND t_copy = 'O' THEN d.t_debit ELSE 0 END) +
            SUM(CASE WHEN d.t_type = 'REC' AND t_copy = 'D' THEN d.t_debit ELSE 0 END) -
            SUM(CASE WHEN d.t_type = 'DEL' AND t_copy = 'O' THEN d.t_credit ELSE 0 END) -
            SUM(CASE WHEN d.t_type = 'OPBL' AND t_copy = 'O' THEN d.t_credit ELSE 0 END) +
            SUM(CASE WHEN d.t_type = 'OPBL' AND t_copy = 'D' THEN d.t_debit ELSE 0 END)
        ) AS cr_bl2
    FROM transactions d
    JOIN accounts u ON d.t_cr = u.id 
    WHERE 
        u.user_id = ? 
        AND d.t_status ='Confirmed'
        AND u.acc_categ = 'sales_person'
        AND d.t_type IN ('DEL', 'GR', 'REC', 'OPBL')
        AND d.t_date < DATE_SUB(NOW(), INTERVAL 9 HOUR)
    GROUP BY u.acc_name, u.id
");

$stmt3->execute([$user_id]);
$data3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);

if (!empty($data3)) {

    foreach ($data3 as $row) {

        $cr_bl2 = floatval($row['cr_bl2']);
        $value = abbreviateNumber($cr_bl2);

    if($cr_bl2<0){
        $statusClass = ($cr_bl2 < 0) ? "text-danger" : "";
    }
     else{    $statusClass = ($cr_bl2 > 0) ? "text-dark" : "";
     }

        echo '<span class="price ' . $statusClass . '">
                <a class="item fetch-unclearedbl"
                   data-toggle="modal"
                   data-target="#Modalrep2"
                   data-loc_id="' . htmlspecialchars($row["t_tt_doc"]) . '"
                   data-usr="' . htmlspecialchars($row["t_cr"]) . '">
                   ' . htmlspecialchars($value) . '
                </a>
              </span> ';
    }

} else {
    echo '0';
}
?>