<?php
include("db/cn.php");
ob_start();

// Check if session data exists
if (isset($_SESSION['user_name']) && isset($_SESSION['user_role']) && isset($_SESSION['outlet_name']) && isset($_SESSION['outlet_address'])) {
    $userName = $_SESSION['user_name'];
    $userRole = $_SESSION['user_role'];
    $outlet_address = $_SESSION['outlet_address'];
    $outlet_name = $_SESSION['outlet_name'];
}

//opening_amount
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['open_cash_amount'])){
	
$open_cash = $_POST["amount"];
        $date_time = date("Y-m-d");
    	$date_timenew = date("Y-m-d");
    	mysqli_query($connection,"insert into bank3_opening(open_cash,login_user,user_role,outlet_name,outlet_address,date_time)
    	values('".$open_cash."','".$userName."','".$userRole."','".$outlet_name."','".$outlet_address."','".$date_time."')");
    	
    	echo "<script type='text/javascript'>window.location.href = 'report_bank3.php';</script>";
    //header("Location: cashinout.php");
    exit();
}

$startDate = isset($_POST['startDate']) ? $_POST['startDate'] : null;
$endDate = isset($_POST['endDate']) ? $_POST['endDate'] : null;

// Function to apply date filter and sort by date_time
function add_date_filter($query, $startDate, $endDate) {
    if (stripos($query, 'GROUP BY') !== false) {
        $parts = preg_split('/GROUP BY/i', $query);
        $baseQuery = trim($parts[0]);
        $groupByClause = 'GROUP BY ' . trim($parts[1]);

        if ($startDate && $endDate) {
            if (stripos($baseQuery, 'WHERE') !== false) {
                $baseQuery .= " AND date_time BETWEEN '$startDate' AND '$endDate'";
            } else {
                $baseQuery .= " WHERE date_time BETWEEN '$startDate' AND '$endDate'";
            }
        }
        return $baseQuery . ' ' . $groupByClause . " ORDER BY date_time ASC";
    } else {
        if ($startDate && $endDate) {
            if (stripos($query, 'WHERE') !== false) {
                $query .= " AND date_time BETWEEN '$startDate' AND '$endDate'";
            } else {
                $query .= " WHERE date_time BETWEEN '$startDate' AND '$endDate'";
            }
        }
        return $query . " ORDER BY date_time ASC";
    }
}

// Define the missing function to get the previous closing balance
function get_previous_closing_balance($connection, $startDate) {
    $balance = 0;
    $queries = [
        "SELECT SUM(open_cash) AS amount FROM bank3_opening WHERE date_time < '$startDate'",
        "SELECT SUM(discount_amount) AS amount FROM log_user_sales WHERE paid_by = 'bank3' AND date_time < '$startDate'",
        "SELECT SUM(cash_amount) AS amount FROM log_user_sales WHERE paid_by = 'credit' AND date_time < '$startDate'",
        "SELECT SUM(discount_amount) AS amount FROM log_user_sales WHERE paid_by = 'refund' AND date_time < '$startDate'",
        "SELECT SUM(amount_i) AS amount FROM transfer WHERE from_i = 'bank3' && date_time < '$startDate'",
        "SELECT SUM(amount_i) AS amount FROM transfer WHERE to_i = 'bank3' && date_time < '$startDate'",
        "SELECT SUM(amount) AS amount FROM withdraw WHERE paid_by = 'bank3' && date_time < '$startDate'",
        "SELECT SUM(discount_amount) AS amount FROM purchase WHERE paid_by = 'bank3' AND status ='return'  AND date_time   < '$startDate' GROUP BY r_inv_id",
        "SELECT SUM(amount) AS amount FROM incash WHERE  paid_by = 'bank3' AND date_time < '$startDate' ",
        "SELECT SUM(amount) AS amount FROM credit_paid WHERE paid_by = 'bank3' AND date_time < '$startDate'",
        "SELECT SUM(supplier_amount) AS amount FROM supplier_paid WHERE status = 'bank3' AND date_time < '$startDate'",
        "SELECT SUM(discount_amount) AS amount FROM purchase WHERE paid_by = 'bank3' AND date_time < '$startDate' GROUP BY inv_id"
    ];

    foreach ($queries as $query) {
        $result = mysqli_query($connection, $query);
        if ($result) {
            $row = mysqli_fetch_assoc($result);
            $amount = floatval($row['amount']);
            if (stripos($query, 'incash') !== false || stripos($query, 'credit_paid') !== false || stripos($query, 'supplier_paid') !== false) {
                $balance -= $amount; // Subtract outgoing cash
            } else {
                $balance += $amount; // Add incoming cash
            }
        }
    }

    return $balance;
}

// Define the get_opening_balance() function to retrieve the balance from previous transactions
function get_opening_balance($connection, $startDate) {
    return get_previous_closing_balance($connection, $startDate);
}

// Fetch and combine all transactions
$combinedData = [];
$queries = [
    'Opening Bank' => add_date_filter("SELECT open_cash AS amount, date_time FROM bank3_opening", $startDate, $endDate),
    'Sale Bank' => add_date_filter("SELECT discount_amount AS amount, date_time FROM log_user_sales WHERE paid_by = 'bank3' GROUP BY order_id", $startDate, $endDate),
    'Credit Recieved' => add_date_filter("SELECT amount, date_time FROM credit_paid WHERE paid_by = 'bank3'", $startDate, $endDate),
    'POS Bank Recieved' => add_date_filter("SELECT cash_amount AS amount, date_time FROM log_user_sales WHERE paid_by = 'credit' GROUP BY order_id", $startDate, $endDate),
    'Recieve Bank' => add_date_filter("SELECT amount_i AS amount, date_time FROM transfer where to_i = 'bank3'", $startDate, $endDate),
    'Bank Purchase Return' => add_date_filter("SELECT discount_amount AS amount, date_time FROM purchase WHERE paid_by = 'bank3' AND status ='return' GROUP BY r_inv_id", $startDate, $endDate),
    'Purchase Bank' => add_date_filter("SELECT discount_amount AS amount, date_time FROM purchase WHERE paid_by = 'bank3' GROUP BY inv_id", $startDate, $endDate),
    'Supplier Paid' => add_date_filter("SELECT supplier_amount AS amount, date_time FROM supplier_paid WHERE status = 'bank3'", $startDate, $endDate),
    'Bank Sale Refund ' => add_date_filter("SELECT discount_amount AS amount, date_time FROM log_user_sales WHERE paid_by = 'bank3' && status = 'refund' GROUP BY order_id", $startDate, $endDate),
    'Expense Bank' => add_date_filter("SELECT amount, date_time FROM incash where paid_by = 'bank3'", $startDate, $endDate),
   'Transfer Bank' => add_date_filter("SELECT amount_i AS amount, date_time FROM transfer where from_i = 'bank3'", $startDate, $endDate),
    'Withdraw Bank' => add_date_filter("SELECT amount, date_time FROM withdraw where paid_by = 'bank3'", $startDate, $endDate),
];

foreach ($queries as $description => $query) {
    $result = mysqli_query($connection, $query);
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $row['description'] = $description;
            $combinedData[] = $row;
        }
    }
}

// Sort the combined data by date_time
usort($combinedData, function ($a, $b) {
    return strtotime($a['date_time']) - strtotime($b['date_time']);
});

// Calculate daily opening balances and update balances for each day
$currentDate = null;
$currentBalance = get_opening_balance($connection, $startDate); // Get opening balance from previous transactions
$dailyData = [];

foreach ($combinedData as $transaction) {
    $transactionDate = date('Y-m-d', strtotime($transaction['date_time']));
    
    // Check if the date has changed (new day)
    if ($currentDate !== $transactionDate) {
        // Display the opening balance for the new day
        $dailyData[] = [
            'date_time' => $transactionDate,
            'description' => 'Opening Balance',
            'amount' => 'N/A',
            'balance' => number_format($currentBalance, 2)
        ];
        $currentDate = $transactionDate;
    }
    
    // Update the balance with the current transaction amount
    $currentBalance += floatval($transaction['amount']);
    
    // Update the transaction with the new balance
    $transaction['balance'] = number_format($currentBalance, 2);
    $dailyData[] = $transaction;
}

// Output the daily data (this part depends on how you're displaying the data in HTML, so adjust accordingly)
?>


<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Whole Sale Retail</title>

    <!-- Custom fonts for this template-->
    <link href="../vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
   <link href="https://fonts.googleapis.com/css2?family=Quicksand:wght@200;300;400;500;600;700&display=swap" rel="stylesheet">

    <!-- Custom styles for this template-->
    <link href="../css/sb-admin-2.min.css" rel="stylesheet">
        <link href="../css/sb-admin-2.css" rel="stylesheet">
    
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<style>
body {
    font-family: 'Quicksand', sans-serif;
    
}
 #table-container-wrapper {
    max-height: 660px; /* Adjust the maximum height as needed */
    overflow-y: auto; /* Enable vertical scroll when content overflows */
}

		    .chart-container {
      position: relative;
      background: rgba(255, 255, 255, 0.9);
      border-radius: 10px;
      padding: 15px;
      box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
    }

    canvas {
      max-width: 100%;
      height: auto;
    }
.bg-gradient-primary{
    
	background:#0A4657;
	color:#fff;	
	
	}

.btn-primary{
	background:#0A4657;
	color:#fff;
	 border:1px #0A4657 solid;
	
	}  
.btn-primary:hover{
  
  background:#fff;
  border:1px #0A4657 solid;
	color:#0A4657;
	
	}	 
    .chart-container {
      position: relative;
      background: rgba(255, 255, 255, 0.9);
      border-radius: 10px;
      padding: 15px;
      box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
    }

    canvas {
      max-width: 100%;
      height: auto;
    }
	
	.fa-download:hover{
		color:#953E39;
		}
		
.text-primary{
	  	
		}
  </style>

<body id="page-top">

    <!-- Page Wrapper -->
    <div id="wrapper">

        <!-- Sidebar -->
        <?php
        include("common/sd.php");
		?>
        <!-- End of Sidebar -->

        <!-- Content Wrapper -->
        <div id="content-wrapper" class="d-flex flex-column">

            <!-- Main Content -->
            <div id="content">

                <!-- Topbar -->
                <nav class="navbar navbar-expand navbar-light bg-white topbar mb-4 static-top shadow">

                    <!-- Sidebar Toggle (Topbar) -->
                    <button id="sidebarToggleTop" class="btn btn-link d-md-none rounded-circle mr-3">
                        <i class="fa fa-bars"></i>
                    </button>

                    <!-- Topbar Search -->
                    
  <?php include('common/log.php');  ?>

                    <!-- Topbar Navbar -->
                    
                </nav>
                <!-- End of Topbar -->

                <!-- Begin Page Content -->
                
               <div class="container-fluid mt-5">
    <div class="row">
        <div class="col-sm-3">
        <h2 class="mb-2" style="font-weight:700;color:#060606;">Bank Book 3</h2>    
        </div>
        <div class="col-sm-6"></div>
        <div class="col-sm-3">
            
        </div>
    </div>
    <br>
    <form method="post">
        
        <div class="row">
            <div class="col-md-3">
                <label for="startDate">Start Date:</label>
                <input type="date" class="form-control" name="startDate" value="<?= $startDate ?>">
            </div>
            <div class="col-md-3">
                <label for="endDate">End Date:</label>
                <input type="date" class="form-control" name="endDate" value="<?= $endDate ?>">
            </div>
            <div class="col-md-6">
                
                <button type="submit" class="btn btn-dark" style="margin-top:30px;" name="filter">Filter</button>
                <button id="downloadExcel" class="btn btn-dark " style="margin-top:30px;">Download </button>
            
            <a href="report_c.php"><button type="button" class="btn btn-dark" style="margin-top:30px;"><i class="fas fa-sync-alt fa-1x"></i></button></a>
              
            <button type="button" class="btn btn-dark " style="margin-top:30px;" data-toggle="modal" data-target="#addOpencashModal" >Add Cash Opening </button>
            </div>
        </div>
        
        <div id="table-container-wrapper" class="mt-4">
    <table id="patientTable" class="table table-stripped table-hover text-center table-sm bg-white">
        <thead class="bg-dark text-white">
            <tr>
                <th>Date</th>
                <th>Description</th>
                <th>Debit Amount (€)</th>
                <th>Credit Amount (€)</th>
                <th>Balance (€)</th>
            </tr>
        </thead>
        <tbody>
        <?php
        // Display the transactions with updated balances
        $previousDate = null;
        $balance = get_previous_closing_balance($connection, $startDate);

        foreach ($combinedData as $data) {
            $currentDate = date('Y-m-d', strtotime($data['date_time']));

            // Check for new date and display opening balance
            if ($previousDate !== $currentDate) {
                echo "<tr>
                        <td style='color:#0A4657;font-weight:700;'>$currentDate</td>
                        <td style='color:#0A4657;font-weight:700'>Opening Balance</td>
                        <td></td>
                        <td></td>
                        <td style='color:#0A4657;font-weight:700'>" . number_format(floatval($balance), 2) . "</td>
                      </tr>";
                $previousDate = $currentDate;
            }

            // Determine if the amount is positive or negative and adjust the balance
            $amount = floatval($data['amount']);
            if (stripos($data['description'], 'Expense') !== false || 
                stripos($data['description'], 'Supplier') !== false || 
                stripos($data['description'], 'Purchase Cash') !== false ||
                stripos($data['description'], 'Refund') !== false || 
                stripos($data['description'], 'Withdraw') !== false || 
                stripos($data['description'], 'Transfer') !== false) {
                $balance -= $amount;
                $positiveAmount = ''; // No positive value
                $negativeAmount = number_format($amount, 2); // Show negative value
            } else {
                $balance += $amount;
                $positiveAmount = number_format($amount, 2); // Show positive value
                $negativeAmount = ''; // No negative value
            }

            // Output the row with separate columns for positive and negative amounts
            echo "<tr style='color:#000';>
                    <td>{$currentDate}</td>
                    <td>{$data['description']}</td>
                    <td>{$positiveAmount}</td>
                    <td>{$negativeAmount}</td>
                    <td>" . number_format(floatval($balance), 2) . "</td>
                  </tr>";
        }
        ?>
        </tbody>
    </table>
</div>

    </form>
</div>
<div class="modal fade" id="addOpencashModal" tabindex="-1" role="dialog" 
            aria-labelledby="addOpencashModalLabel" aria-hidden="true" style="margin-top:80px;">
                <div class="modal-dialog" role="document">
                    <div class="modal-content">
                        <div class="modal-header">
                            <h5 class="modal-title text-capitalize" id="addOpencashModalLabel">Add Cash Opening Amount</h5>
                            <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                                <span aria-hidden="true">&times;</span>
                            </button>
                        </div>
                        <div class="modal-body">
                            <!-- Form for adding purchase -->
                            <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" enctype="multipart/form-data">
                                
                                <input type="text" name="open_amount1" placeholder="Amount" class="form-control" />
                                <br />
                                <button type="submit" name="open_cash_amount"  class="btn btn-dark">Enter</button>
                            </form>
                        </div>
                    </div>
                </div>
            </div>


    
            
                <!-- /.container-fluid -->

            </div>
            <br><br>
            <!-- End of Main Content -->

            <!-- Footer -->
            <?php include("common/main_ft.php");  ?>
            <!-- End of Footer -->

        </div>
        <!-- End of Content Wrapper -->

    </div>
    <!-- End of Page Wrapper -->

    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
        <i class="fas fa-angle-up"></i>
    </a>

    <!-- Logout Modal-->
    <div class="modal fade" id="logoutModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel"
        aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Ready to Leave?</h5>
                    <button class="close" type="button" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">×</span>
                    </button>
                </div>
                <div class="modal-body">Select "Logout" below if you are ready to end your current session.</div>
                <div class="modal-footer">
                    <button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
                    <a class="btn btn-primary" href="">Logout</a>
                </div>
            </div>
        </div>
    </div>

      <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>

<!-- JavaScript for exporting the table data to Excel -->
<script>
    document.getElementById('downloadExcel').addEventListener('click', function() {
        // Get the table element
        var table = document.getElementById('patientTable');

        // Clean the table to avoid potential issues with hidden rows or formatting
        var clone = table.cloneNode(true); // Clone the table
        var rows = clone.querySelectorAll('tr');

        // Remove hidden or empty rows if necessary
        rows.forEach(function(row) {
            if (row.style.display === 'none' || row.innerText.trim() === '') {
                row.remove();
            }
        });

        // Convert the table into a worksheet using SheetJS
        var workbook = XLSX.utils.table_to_book(clone, { sheet: "Patients" });

        // Export the Excel file with a proper name
        try {
            XLSX.writeFile(workbook, 'Bank_3_report.xlsx');
        } catch (error) {
            console.error("Error while writing the file: ", error);
            alert("Error generating Excel file. Please check if the SheetJS library is properly loaded.");
        }
    });
</script>
   <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js"></script>
    <!-- Bootstrap core JavaScript-->
    <script src="../vendor/jquery/jquery.min.js"></script>
    <script src="../vendor/bootstrap/js/bootstrap.bundle.min.js"></script>

    <!-- Core plugin JavaScript-->
    <script src="../vendor/jquery-easing/jquery.easing.min.js"></script>

    <!-- Custom scripts for all pages-->
    <script src="../js/sb-admin-2.min.js"></script>

</body>

</html>