<?php
require_once "../../includes/db.php";
header('Content-Type: application/json');
session_start(['read_and_close' => true]);

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

session_write_close();

$comp_id = $_GET['comp_id'] ?? 0;
$accountno = $_GET['accountno'] ?? 0;
$stt_type = $_GET['stt_type'] ?? 0;

$stt_limit  = $_GET['stt_limit'] ?? 10;
$stt_offset = $_GET['stt_offset'] ?? 0;
$storeid = $_GET['storeid'] ?? 0;

if($stt_type=='stt_accs') {
$stmt = $pdo->prepare("
SELECT 
    d.t_id, d.co_id,d.t_date, d.t_doc_no, d.t_ct_det, b.acc_name AS accname_exp, c.acc_name AS acc_rec, ab.acc_name AS acc_ent_by, d.t_cr, a.acc_name,a.acc_type, d.t_type,
 d.t_conf_by, d.t_credit, d.t_debit, 
SUM( COALESCE(d.t_credit, 0) - COALESCE(d.t_debit, 0) ) OVER ( PARTITION BY a.id ORDER BY d.t_date, d.t_id ) AS running_balance 

FROM accounts a JOIN transactions d ON a.id = d.t_cr 
LEFT JOIN accounts b ON b.id = d.t_ct_det 
LEFT JOIN accounts c ON c.user_id = d.t_conf_by 
LEFT JOIN accounts ab ON ab.user_id = d.t_entry_by  

WHERE 
    d.t_cr=:accountno AND d.co_id=:comp_id
    AND d.t_status IN ('Confirmed','Approved')
    AND d.t_type IN ('BNK', 'EXP', 'REC' , 'CTFR', 'csh_adj')
    GROUP BY d.t_id ORDER BY d.t_date DESC, d.t_id DESC
    LIMIT :stt_limit OFFSET :stt_offset;
");
}

if($stt_type=='stt_cr') {
$stmt = $pdo->prepare("
SELECT 
    d.t_id, d.co_id,d.t_date, d.t_doc_no, d.t_ct_det, b.acc_name AS accname_exp, c.acc_name AS acc_rec, ab.acc_name AS acc_ent_by, d.t_cr, a.acc_name,a.acc_type, d.t_type,
 d.t_conf_by, d.t_credit, d.t_debit, 
SUM( COALESCE(d.t_debit, 0) - COALESCE(d.t_credit, 0) ) OVER ( PARTITION BY a.id ORDER BY d.t_date, d.t_id ) AS running_balance 

FROM accounts a JOIN transactions d ON a.id = d.t_cr 
LEFT JOIN accounts b ON b.id = d.t_ct_det 
LEFT JOIN accounts c ON c.user_id = d.t_conf_by 
LEFT JOIN accounts ab ON ab.user_id = d.t_entry_by  

WHERE 
    d.t_cr=:accountno AND d.co_id=:comp_id
    AND d.t_status IN ('Confirmed','Approved')
    GROUP BY d.t_id ORDER BY d.t_date DESC, d.t_id DESC
    LIMIT :stt_limit OFFSET :stt_offset;
");
}

if($stt_type=='stt_exp') {
$stmt = $pdo->prepare("
SELECT 
    d.t_id, d.co_id,d.t_date, d.t_doc_no, d.t_ct_det, b.acc_name, d.t_narration AS accname_exp, c.acc_name AS acc_rec, ab.acc_name AS acc_ent_by, d.t_cr, a.acc_name,a.acc_type, d.t_type,
 d.t_conf_by, d.t_credit, d.t_debit, 
SUM( COALESCE(d.t_credit, 0) - COALESCE(d.t_debit, 0) ) OVER ( PARTITION BY a.id ORDER BY d.t_date, d.t_id ) AS running_balance 

FROM accounts a JOIN transactions d ON a.id = d.t_cr 
LEFT JOIN accounts b ON b.id = d.t_ct_det 
LEFT JOIN accounts c ON c.user_id = d.t_conf_by 
LEFT JOIN accounts ab ON ab.user_id = d.t_entry_by  

WHERE 
    d.t_cr=:accountno AND d.co_id=:comp_id
    AND d.t_status IN ('Confirmed','Approved')
    AND d.t_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
    GROUP BY d.t_id ORDER BY d.t_date DESC, d.t_id DESC
    LIMIT :stt_limit OFFSET :stt_offset;
");
}

//$stmt->bindValue(':storeid', $storeid, PDO::PARAM_INT);
$stmt->bindValue(':accountno', $accountno, PDO::PARAM_INT);
$stmt->bindValue(':comp_id', $comp_id, PDO::PARAM_INT);
//$stmt->bindValue(':user_id', $user_id, PDO::PARAM_INT);

$stmt->bindValue(':stt_limit', (int)$stt_limit, PDO::PARAM_INT);
$stmt->bindValue(':stt_offset', (int)$stt_offset, PDO::PARAM_INT);
$stmt->execute();

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