<?php

//the global file operation
include("../public/impOpreation.php");

//get the do the action
$do = $_GET['do'];

$langs = $_SESSION['erp_lang'];
include_once("../views/languages/$langs/success.php");
include_once("../views/languages/$langs/error.php");

/* ======================
  Controller Name :- accLedgerReportController
  General Ledger - الأستاذ العام
========================*/

$userID = $_SESSION['userid'];
$today = date("Y-m-d H:i:s");

// AJAX actions that skip header/footer
$ajaxActions = array('exportExcel');

if (!in_array($do, $ajaxActions)) {
    $smarty->display("header.html");
}

$smarty->assign("accLedgerReport", 1);
$smarty->assign("accCommon", 1);

//check and use the condition that suits this action
if (empty($do)) {
    $accounts = R::getAll("SELECT id, code, account_nature, balance, name, CONCAT(code, ' - ', name) AS text FROM acc_account WHERE is_leaf = 1 AND del = 0 ORDER BY code ASC");
    $fiscalYears = R::getAll("SELECT * FROM acc_fiscal_year WHERE del = 0 ORDER BY start_date DESC");

    $smarty->assign("accounts", $accounts);
    $smarty->assign("fiscalYears", $fiscalYears);
    $smarty->display("accLedgerReportview/report.html");
}

elseif ($do == "generate" || $do == "report") {
    $account_id = isset($_GET['account_id']) ? (int)$_GET['account_id'] : 0;
    $date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
    $date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
    $fiscal_year_id = isset($_GET['fiscal_year_id']) ? (int)$_GET['fiscal_year_id'] : 0;

    // Get account info
    $account = R::getRow("SELECT * FROM acc_account WHERE id = ? AND del = 0", [$account_id]);

    // Build conditions
    $where = "WHERE j.del = 0 AND jl.account_id = ?";
    $params = array($account_id);

    if (!empty($date_from)) {
        $where .= " AND j.entry_date >= ?";
        $params[] = $date_from;
    }
    if (!empty($date_to)) {
        $where .= " AND j.entry_date <= ?";
        $params[] = $date_to;
    }
    if ($fiscal_year_id > 0) {
        $where .= " AND j.fiscal_year_id = ?";
        $params[] = $fiscal_year_id;
    }

    // Get opening balance (sum of transactions before date_from)
    $openingBalance = 0;
    if (!empty($date_from)) {
        $obParams = array($account_id, $date_from);
        $obWhere = "WHERE j.del = 0 AND jl.account_id = ? AND j.entry_date < ?";
        if ($fiscal_year_id > 0) {
            $obWhere .= " AND j.fiscal_year_id = ?";
            $obParams[] = $fiscal_year_id;
        }
        $ob = R::getRow("SELECT COALESCE(SUM(jl.debit), 0) - COALESCE(SUM(jl.credit), 0) as balance
            FROM acc_journal_entry_line jl
            INNER JOIN acc_journal_entry j ON jl.journal_entry_id = j.id
            $obWhere", $obParams);
        $openingBalance = (float)$ob['balance'];
    }

    // Get journal lines with running balance
    $journalLines = R::getAll("SELECT jl.*, j.entry_no, j.entry_date, j.description as journal_description,
        cc.name as costcenter_name
        FROM acc_journal_entry_line jl
        INNER JOIN acc_journal_entry j ON jl.journal_entry_id = j.id
        LEFT JOIN acc_cost_center cc ON jl.cost_center_id = cc.id
        $where ORDER BY j.entry_date ASC, j.entry_no ASC, jl.id ASC", $params);

    // Calculate running balance
    $runningBalance = $openingBalance;
    foreach ($journalLines as &$line) {
        $runningBalance += ((float)$line['debit'] - (float)$line['credit']);
        $line['running_balance'] = $runningBalance;
    }
    unset($line);

    $accounts = R::getAll("SELECT id, code, name, CONCAT(code, ' - ', name) AS text FROM acc_account WHERE is_leaf = 1 AND del = 0 ORDER BY code ASC");
    $fiscalYears = R::getAll("SELECT * FROM acc_fiscal_year WHERE del = 0 ORDER BY start_date DESC");

    $smarty->assign("account", $account);
    $smarty->assign("journalLines", $journalLines);
    $smarty->assign("openingBalance", $openingBalance);
    $smarty->assign("accounts", $accounts);
    $smarty->assign("fiscalYears", $fiscalYears);
    $smarty->assign("account_id", $account_id);
    $smarty->assign("date_from", $date_from);
    $smarty->assign("date_to", $date_to);
    $smarty->assign("fiscal_year_id", $fiscal_year_id);
    $smarty->display("accLedgerReportview/report.html");
}

elseif ($do == "exportExcel") {
    $account_id = isset($_GET['account_id']) ? (int)$_GET['account_id'] : 0;
    $date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
    $date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
    $fiscal_year_id = isset($_GET['fiscal_year_id']) ? (int)$_GET['fiscal_year_id'] : 0;

    $account = R::getRow("SELECT * FROM acc_account WHERE id = ? AND del = 0", [$account_id]);

    $where = "WHERE j.del = 0 AND jl.account_id = ?";
    $params = array($account_id);

    if (!empty($date_from)) {
        $where .= " AND j.entry_date >= ?";
        $params[] = $date_from;
    }
    if (!empty($date_to)) {
        $where .= " AND j.entry_date <= ?";
        $params[] = $date_to;
    }
    if ($fiscal_year_id > 0) {
        $where .= " AND j.fiscal_year_id = ?";
        $params[] = $fiscal_year_id;
    }

    $openingBalance = 0;
    if (!empty($date_from)) {
        $obParams = array($account_id, $date_from);
        $obWhere = "WHERE j.del = 0 AND jl.account_id = ? AND j.entry_date < ?";
        if ($fiscal_year_id > 0) {
            $obWhere .= " AND j.fiscal_year_id = ?";
            $obParams[] = $fiscal_year_id;
        }
        $ob = R::getRow("SELECT COALESCE(SUM(jl.debit), 0) - COALESCE(SUM(jl.credit), 0) as balance
            FROM acc_journal_entry_line jl INNER JOIN acc_journal_entry j ON jl.journal_entry_id = j.id $obWhere", $obParams);
        $openingBalance = (float)$ob['balance'];
    }

    $journalLines = R::getAll("SELECT jl.*, j.entry_no, j.entry_date, j.description as journal_description
        FROM acc_journal_entry_line jl
        INNER JOIN acc_journal_entry j ON jl.journal_entry_id = j.id
        $where ORDER BY j.entry_date ASC, j.entry_no ASC, jl.id ASC", $params);

    $runningBalance = $openingBalance;
    foreach ($journalLines as &$line) {
        $runningBalance += ((float)$line['debit'] - (float)$line['credit']);
        $line['running_balance'] = $runningBalance;
    }
    unset($line);

    // Output as Excel (HTML table with Excel headers)
    header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
    header("Content-Disposition: attachment; filename=ledger_report_" . date('Y-m-d') . ".xls");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo '<html dir="rtl"><head><meta charset="UTF-8"></head><body>';
    echo '<h3>' . $account['code'] . ' - ' . $account['name'] . '</h3>';
    echo '<table border="1" style="border-collapse:collapse;">';
    echo '<tr style="background:#3498db;color:#fff;font-weight:bold;"><td>التاريخ</td><td>رقم القيد</td><td>البيان</td><td>مدين</td><td>دائن</td><td>الرصيد</td></tr>';
    echo '<tr style="background:#d9edf7;"><td colspan="3"><strong>رصيد أول المدة</strong></td><td></td><td></td><td><strong>' . number_format($openingBalance, 2) . '</strong></td></tr>';
    foreach ($journalLines as $row) {
        echo '<tr>';
        echo '<td>' . $row['entry_date'] . '</td>';
        echo '<td>' . $row['entry_no'] . '</td>';
        echo '<td>' . $row['journal_description'] . '</td>';
        echo '<td>' . number_format($row['debit'], 2) . '</td>';
        echo '<td>' . number_format($row['credit'], 2) . '</td>';
        echo '<td>' . number_format($row['running_balance'], 2) . '</td>';
        echo '</tr>';
    }
    echo '</table></body></html>';
    exit;
}

if (!in_array($do, $ajaxActions)) {
    $smarty->display("footer.html");
}
?>
