<?php
require_once "../../includes/db.php";
header('Content-Type: application/json');
session_start();

$coid   = $_SESSION['co_id'];
$user_id   = $_SESSION['user_id'];

$ovT_limit  = $_GET['ovT_limit'] ?? 10;
$ovT_offset = $_GET['ovT_offset'] ?? 0;
$storeid = $_GET['storeid'] ?? 0;

$stmt_1 = $pdo->prepare("
    SELECT 
    d.co_id,
    d.t_date,
    DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status,
    SUM(CASE WHEN d.t_type = 'DEL' THEN d.t_credit ELSE 0 END) +
    SUM(CASE WHEN d.t_type = 'GR' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'REC' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'EXP' THEN d.t_credit ELSE 0 END) AS total_result

FROM accounts a
JOIN permissions p 
    ON p.co_id = :coid
    AND p.user_id = :user_id

JOIN transactions d 
    ON a.id = d.t_cr

WHERE 
    (
        p.pm_acc_id = a.id 
       
    )
AND p.pm_acc_lvl IN (0,1,3,5,6,7)
AND 
(CASE WHEN (d.t_type = 'DEL' OR d.t_type = 'GR')  THEN d.t_copy = 'O' ELSE d.t_copy = 'D' END)
AND d.t_status = 'Confirmed'
AND a.acc_categ IN ('sales_person', 'customer', 'expense')

GROUP BY 
    d.co_id,
    d.t_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status

ORDER BY d.t_date DESC,d.t_doc_no
        LIMIT :ovT_limit OFFSET :ovT_offset
");


$stmt_1->bindValue(':coid', $coid, PDO::PARAM_INT);
$stmt_1->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$stmt_1->bindValue(':ovT_limit', (int)$ovT_limit, PDO::PARAM_INT);
$stmt_1->bindValue(':ovT_offset', (int)$ovT_offset, PDO::PARAM_INT);
$stmt_1->execute();

$stmt_2 = $pdo->prepare("
    SELECT 
    d.co_id,
    d.t_date,
    DATE_FORMAT(d.t_date, '%d %b%y') AS M_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status,
    SUM(CASE WHEN d.t_type = 'DEL' THEN d.t_credit ELSE 0 END) +
    SUM(CASE WHEN d.t_type = 'GR' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'REC' THEN d.t_debit ELSE 0 END)+
    SUM(CASE WHEN d.t_type = 'EXP' THEN d.t_credit ELSE 0 END) AS total_result

FROM accounts a
JOIN permissions p 
    ON p.co_id = :coid
    AND p.user_id = :user_id

JOIN transactions d 
    ON a.id = d.t_cr

WHERE 
    (
        p.pm_acc_categ = a.acc_categ 
            AND p.pm_acc_id = ''
       
    )
AND p.pm_acc_lvl IN (0,1,3,5,6,7)
AND 
(CASE WHEN (d.t_type = 'DEL' OR d.t_type = 'GR')  THEN d.t_copy = 'O' ELSE d.t_copy = 'D' END)
AND d.t_status = 'Confirmed'
AND a.acc_categ IN ('sales_person', 'customer', 'expense')

GROUP BY 
    d.co_id,
    d.t_date,
    d.t_tt_doc,
    d.t_doc_no,
    d.t_type,
    d.t_ct_det,
    a.acc_name,
    d.t_status

ORDER BY d.t_date DESC,d.t_doc_no
        LIMIT :ovT_limit OFFSET :ovT_offset
");


$stmt_2->bindValue(':coid', $coid, PDO::PARAM_INT);
$stmt_2->bindValue(':user_id', $user_id, PDO::PARAM_INT);
$stmt_2->bindValue(':ovT_limit', (int)$ovT_limit, PDO::PARAM_INT);
$stmt_2->bindValue(':ovT_offset', (int)$ovT_offset, PDO::PARAM_INT);

$stmt_2->execute();

    // Combine the results from both queries
$data3 = array_merge($stmt_1, $stmt_2);

echo json_encode($data3->fetchAll(PDO::FETCH_ASSOC));
exit;