<?php
// Include your database connection code
require '../../database/shikanisha.kts.php';

// Get start and end dates from POST request
$start_date = $_POST['startDate'];
$end_date = $_POST['endDate'];

// Function to execute SQL query and return formatted result
function executeQuery($query, $start_date, $end_date, $conn) {
    // Prepare the SQL statement
    $stmt = $conn->prepare($query);
    // Bind parameters
    $stmt->bind_param("ss", $start_date, $end_date);
    // Execute the statement
    $stmt->execute();
    // Get the result
    $result = $stmt->get_result();
    // Fetch the rows
    $rows = $result->fetch_all(MYSQLI_ASSOC);
    // Close the statement
    $stmt->close();
    // Return the fetched rows
    return $rows;
}

// Execute each query and store the results
$debit_transactions = executeQuery("SELECT `amount_transacted` as debit, `id` AS Ref, `transaction_type`, `vendor_id` as name, `date` as date FROM `vendor_transactions` WHERE `date` BETWEEN ? AND ? AND `transaction_type` = 'Debit'", $start_date, $end_date, $conn);

$credit_transactions = executeQuery("SELECT `seller_date` as date, 'Sale' as name, `total` as credit, `trans_id` as Ref FROM `transaction_receipts` WHERE `seller_date` BETWEEN ? AND ?", $start_date, $end_date, $conn);


$payslip_transactions = executeQuery("SELECT `payslip_id` as Ref, `name` as name, `grossPay` as Debit, `date` as date FROM `table_payslip` WHERE `date` BETWEEN ? AND ?", $start_date, $end_date, $conn);

$swimo_transactions = executeQuery("SELECT `swimo_id` as Ref, `age_group` as name, `total_cost` as credit, `date_column` as date FROM `swimo` WHERE `date_column` BETWEEN ? AND ?", $start_date, $end_date, $conn);

$expense_transactions = executeQuery("SELECT `expense_id` as Ref, `expense_name` as name, `expense_amount` as debit, `date_incurred` as date FROM `expenses` WHERE `date_incurred` BETWEEN ? AND ?", $start_date, $end_date, $conn);

$invoice_transactions = executeQuery("SELECT `invo_id` as Ref, `date` as date, `total` as credit, `supplier_name` as name FROM `invoice` WHERE `date` BETWEEN ? AND ?", $start_date, $end_date, $conn);

// Combine all transactions
$all_transactions = array_merge($debit_transactions, $credit_transactions, $payslip_transactions, $swimo_transactions, $expense_transactions, $invoice_transactions);

// Sort transactions by date
usort($all_transactions, function($a, $b) {
    return strtotime($a['date']) - strtotime($b['date']);
});

// Output the General Ledger
echo '<div class="text-center mb-4">';
echo '        <h2 class="mb-0">General Ledger</h2>';
echo '        <hr class="my-4">';
echo '    </div>';

echo '<div class="row">';
echo '<div class="col-md-12">';
echo '<table class="table">';
echo '<thead>';
echo '<tr>';
echo '<th scope="col">Date</th>';
echo '<th scope="col">Reference</th>';
echo '<th scope="col">Name</th>';
echo '<th scope="col">Debit</th>';
echo '<th scope="col">Credit</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';

// Loop through each transaction and output it in the table
foreach ($all_transactions as $transaction) {
    echo '<tr>';
    echo '<td>' . $transaction['date'] . '</td>';
    echo '<td>' . $transaction['Ref'] . '</td>';
    echo '<td>' . $transaction['name'] . '</td>';
    echo '<td>' . ($transaction['debit'] ?? '') . '</td>';
    echo '<td>' . ($transaction['credit'] ?? '') . '</td>';
    echo '</tr>';
}

echo '</tbody>';
echo '</table>';
echo '</div>';
echo '</div>';

// Close the connection
$conn->close();
?>
