<?php
// Incluir conexión a la base de datos
include 'caja/db.php';

// Obtener opciones para el campo 'usuario'
$usersQuery = $pdo->query("SELECT id, username FROM users");
$users = $usersQuery->fetchAll(PDO::FETCH_ASSOC);

// Obtener opciones para el campo 'caja'
$accountsQuery = $pdo->query("SELECT id, account_name FROM bank_accounts");
$accounts = $accountsQuery->fetchAll(PDO::FETCH_ASSOC);

// Inicializar variables de consulta
$whereClauses = [];
$params = [];

// Procesar el formulario
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['export_pdf'])) {
        // Redirigir a la página de generación de PDF
        header('Location: export_pdf.php');
        exit;
    }
    
    // Criterios de fecha
    if (!empty($_POST['hoy'])) {
        $whereClauses[] = "DATE(bank_transaction_info.created_at) = CURDATE()";
    } else {
        if (!empty($_POST['desde'])) {
            $whereClauses[] = "bank_transaction_info.created_at >= :desde";
            $params[':desde'] = $_POST['desde'];
        }
        if (!empty($_POST['hasta'])) {
            $whereClauses[] = "bank_transaction_info.created_at <= :hasta";
            $params[':hasta'] = $_POST['hasta'];
        }
    }

    // Usuario
    if (!empty($_POST['usuario'])) {
        $whereClauses[] = "bank_transaction_info.created_by = :usuario";
        $params[':usuario'] = $_POST['usuario'];
    }

    // Caja
    if (!empty($_POST['caja'])) {
        $whereClauses[] = "bank_transaction_info.account_id = :caja";
        $params[':caja'] = $_POST['caja'];
    }

    // Número de Cheque
    if (!empty($_POST['numero_cheque'])) {
        $whereClauses[] = "bank_transaction_info.numero_cheque = :numero_cheque";
        $params[':numero_cheque'] = $_POST['numero_cheque'];
    }
} else {
    $whereClauses[] = "DATE(bank_transaction_info.created_at) = CURDATE()";
}

// Construcción de la consulta SQL
$query = "SELECT 
            bank_transaction_info.created_at, 
            bank_transaction_info.invoice_id, 
            bank_transaction_info.transaction_type, 
            bank_accounts.account_name, 
            bank_transaction_info.numero_cheque,
            users.username, 
            bank_transaction_price.amount 
          FROM bank_transaction_info 
          JOIN bank_transaction_price 
            ON bank_transaction_info.info_id = bank_transaction_price.info_id
          JOIN bank_accounts 
            ON bank_transaction_info.account_id = bank_accounts.id
          JOIN users 
            ON bank_transaction_info.created_by = users.id";

if (!empty($whereClauses)) {
    $query .= ' WHERE ' . implode(' AND ', $whereClauses);
}

$stmt = $pdo->prepare($query);
$stmt->execute($params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Variables para los totales
$totalIngresos = 0;
$totalEgresos = 0;
?>

<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="stylesheet" href="caja/styles.css">
    <title>Consulta de Transacciones</title>
    <style>
	 /* Agregar estilos aquí */
    </style>
</head>
<body>

<div class="container">
    <h1>Consulta de Transacciones</h1>
    <form method="POST" action="">
        <label for="desde">Desde:</label>
        <input type="date" id="desde" name="desde">
        
        <label for="hasta">Hasta:</label>
        <input type="date" id="hasta" name="hasta">
        
        <!-- Select para usuario -->
        <label for="usuario">Usuario:</label>
        <select id="usuario" name="usuario">
            <option value="">Seleccionar Usuario</option>
            <?php foreach ($users as $user): ?>
                <option value="<?= $user['id'] ?>"><?= htmlspecialchars($user['username']) ?></option>
            <?php endforeach; ?>
        </select>

        <!-- Select para caja -->
        <label for="caja">Caja:</label>
        <select id="caja" name="caja">
            <option value="">Seleccionar Caja</option>
            <?php foreach ($accounts as $account): ?>
                <option value="<?= $account['id'] ?>"><?= htmlspecialchars($account['account_name']) ?></option>
            <?php endforeach; ?>
        </select>

        <!-- Campo para número de cheque -->
        <label for="numero_cheque">Número de Cheque:</label>
        <input type="text" id="numero_cheque" name="numero_cheque" placeholder="Ingrese número de cheque">

        <button type="submit">Consultar</button>
    </form>

    <table>
        <thead>
            <tr>
                <th>Fecha</th>
                <th>Factura</th>
                <th>Tipo de Transacción</th>
                <th>Caja</th>
                <th>Nro. Cheque</th>
                <th>Usuario</th>
                <th>Ingreso</th>
                <th>Egresos</th>
            </tr>
        </thead>
       <tbody>
    <?php if (!empty($results)) : ?>
        <?php foreach ($results as $row) : ?>
            <tr>
                <td data-label="Fecha"><?= htmlspecialchars($row['created_at']); ?></td>
                
                <!-- Columna Factura con enlace dinámico -->
                <td data-label="Factura">
                    <a href="view_invoice.php?invoice_id=<?= htmlspecialchars($row['invoice_id']); ?>">
                        <?= htmlspecialchars($row['invoice_id']); ?>
                    </a>
                </td>
                
                <td data-label="Tipo" class="<?= $row['transaction_type'] === 'deposit' ? 'ingreso' : 'egreso'; ?>">
                    <?= $row['transaction_type'] === 'deposit' ? 'Ingreso' : 'Egreso'; ?>
                </td>
                
                <td data-label="Caja"><?= htmlspecialchars($row['account_name']); ?></td>
                <td data-label="Cheque"><?= htmlspecialchars($row['numero_cheque']); ?></td>
                <td data-label="Usuario"><?= htmlspecialchars($row['username']); ?></td>

                <!-- Mostrar el monto en la columna correspondiente -->
                <?php if ($row['transaction_type'] === 'deposit') : ?>
                    <td data-label="Ingreso" class="ingreso"><?= htmlspecialchars(number_format($row['amount'], 2)); ?></td>
                    <td></td>
                    <?php $totalIngresos += $row['amount']; ?>
                <?php elseif ($row['transaction_type'] === 'withdraw') : ?>
                    <td></td>
                    <td data-label="Egresos" class="egreso"><?= htmlspecialchars(number_format($row['amount'], 2)); ?></td>
                    <?php $totalEgresos += $row['amount']; ?>
                <?php endif; ?>
            </tr>
        <?php endforeach; ?>
    <?php else : ?>
        <tr>
            <td colspan="8">No se encontraron resultados.</td>
        </tr>
    <?php endif; ?>
</tbody>

    </table>

    <h2>Totales:</h2>
    <p>Ingresos Totales: $<?= number_format($totalIngresos, 2) ?></p>
    <p>Egresos Totales: $<?= number_format($totalEgresos, 2) ?></p>
    <p>Saldo: $<?= number_format($totalIngresos-$totalEgresos, 2) ?></p>
</div>

</body>
</html>
