<?php
$parentuserid = $_SESSION['userid'];



// Execute query (assuming $obj->raw_sql() returns array of rows)
$resellers = $obj->raw_sql("SELECT UserId FROM _reseller_createuser WHERE EntryBy = '$parentuserid'");

$resellerIDs = [];
foreach($resellers as $reseller){
    $resellerIDs[] = $reseller['UserId']; 
}



$resellerIDsStr = !empty($resellerIDs) ? implode(',', $resellerIDs) : '0';

$agents = $obj->raw_sql("SELECT ag_id FROM re_tbl_agent WHERE entry_by IN ($resellerIDsStr)");



// Agent IDs array
$agentIDs = [];
foreach($agents as $agent){
    $agentIDs[] = $agent['ag_id']; 
}

$agentIDsStr = !empty($agentIDs) ? implode(',', $agentIDs) : '0';
?>

<div class="card">
    <div class="card-header">
        <div class="row">
            <div class="col-sm-6">
                <strong>Balance Report (All Years)</strong>
            </div>
            <div class="col-sm-6 text-end">
                <?php if($obj->hasPermission($ty, 'print_statement')){ ?>
                    <button type="button" class="btn btn-primary btn-sm" onclick="printDiv('reportArea')">Print</button>
                <?php } ?>
            </div>
        </div>
    </div>

    <div class="card-body" id="reportArea">
        <div class="table-responsive">
            <table id="yearlyAccounts" class="table table-striped table-hover table-modern">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Month</th>
                        <th>Opening Balance</th>
                        <th>Customer Payment</th>
                        <th>Others Payment</th>
                        <th>Connection Charge</th>
                        <th>Opening Amount</th>
                        <th>Total</th>
                        <th>Expense Statement</th>
                        <th>Closing Balance</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                        $i = 0;
                        $invoice = 0;
                        $year_total = 0;
                        $year_expense = 0;
                        
                        for ($month = 1; $month <= 12; $month++):
                            $i++;
                        
                            $expense_sql = "SELECT SUM(acc_amount) as total_expense FROM re_vw_account 
                                            WHERE entry_by IN ($resellerIDsStr) AND MONTH(entry_date) = $month AND acc_type = 1";
                            $expense_data = $obj->raw_sql($expense_sql);
                            
                            $expense = $expense_data[0]['total_expense'] ?? 0;
                        
                            $other_sql = "SELECT SUM(acc_amount) as total_other FROM re_vw_account 
                                          WHERE entry_by IN ($resellerIDsStr) AND MONTH(entry_date) = $month AND acc_type = 2";
                            $other_data = $obj->raw_sql($other_sql);
                            $other = $other_data[0]['total_other'] ?? 0;
                        
                            $agent_sql = "SELECT SUM(acc_amount) as total_agent FROM re_vw_account 
                                          WHERE entry_by IN ($resellerIDsStr) AND MONTH(entry_date) = $month AND acc_type = 3";
                            $agent_data = $obj->raw_sql($agent_sql);
                            $agent_amount = $agent_data[0]['total_agent'] ?? 0;
                        
                            $conn_sql = "SELECT SUM(connect_charge) as total_conn FROM re_tbl_agent 
                                         WHERE entry_by IN ($resellerIDsStr) AND MONTH(entry_date) = $month";
                            $conn_data = $obj->raw_sql($conn_sql);
                            $conn = $conn_data[0]['total_conn'] ?? 0;
                        
                            $opening_sql = "SELECT SUM(acc_amount) as total_opening FROM re_vw_account 
                                            WHERE entry_by IN ($resellerIDsStr) AND MONTH(entry_date) = $month AND acc_type = 5";
                                            
                            $opening_data = $obj->raw_sql($opening_sql);
                            $opening_amount = $opening_data[0]['total_opening'] ?? 0;
                        
                            $total = $invoice + $agent_amount + $other + $conn + $opening_amount;
                            $closing = $total - $expense;
                            $invoice = $closing;
                        
                            $year_total += ($agent_amount + $other + $conn + $opening_amount);
                            $year_expense += $expense;
                        
                            $monthName = date('F', mktime(0,0,0,$month,1));
                        ?>
                        <tr>
                            <td><?php echo $i; ?></td>
                            <td class="text-center"><?php echo $monthName ?></td>
                            <td class="text-end"><?php echo $invoice ?></td>
                            <td class="text-end"><?php echo $agent_amount ?></td>
                            <td class="text-end"><?php echo $other ?></td>
                            <td class="text-end"><?php echo $conn ?></td>
                            <td class="text-end"><?php echo $opening_amount ?></td>
                            <td class="text-end fw-bold"><?php echo $total ?></td>
                            <td class="text-end text-danger"><?php echo $expense ?></td>
                            <td class="text-end fw-bold"><?php echo $closing ?></td>
                        </tr>
                    <?php endfor; ?>

                    <tr><td colspan="10" style="background:#ddd;"></td></tr>
                    <tr>
                        <td colspan="7" class="text-end fw-bold">Total Income:</td>
                        <td colspan="3" class="text-end fw-bold"><?php echo $year_total ?></td>
                    </tr>
                    <tr>
                        <td colspan="7" class="text-end fw-bold">Total Expense:</td>
                        <td colspan="3" class="text-end fw-bold"><?php echo $year_expense ?></td>
                    </tr>
                    <tr>
                        <td colspan="7" class="text-end fw-bold">Profit:</td>
                        <td colspan="3" class="text-end fw-bold"><?php echo $year_total - $year_expense ?></td>
                    </tr>
                    
                </tbody>
            </table>
        </div>
    </div>
</div>

<script>
function printDiv(divName){
    var printContents = document.getElementById(divName).innerHTML;
    var originalContents = document.body.innerHTML;
    document.body.innerHTML = printContents;
    window.print();
    document.body.innerHTML = originalContents;
}
</script>