<?php
include("connect.php");
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

if (!isset($_FILES['excel_file'])) {
    die("No file uploaded");
}

$fileName = $_FILES['excel_file']['name'];
$tmpName  = $_FILES['excel_file']['tmp_name'];

$ext = pathinfo($fileName, PATHINFO_EXTENSION);
if (!in_array($ext, ['xlsx', 'xls'])) {
    die("Invalid file format");
}

// Load Excel
$spreadsheet = IOFactory::load($tmpName);
$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray();

$count = 0;

foreach ($rows as $key => $row) {

    if ($key == 0) continue;

    $gl_date       = date('Y-m-d', strtotime($row[0]));
    $value_date    = date('Y-m-d', strtotime($row[1]));
    $instr_no      = $row[2];
    $particulars   = $row[3];
    $debit_amount  = str_replace(',', '', $row[4]);
    $credit_amount = str_replace(',', '', $row[5]);
    $balance       = str_replace(',', '', $row[6]);
    $entry_user    = $row[7];
    $verified_user = $row[8];

    $sql = "INSERT INTO bank_statement
            (gl_date,value_date,instr_no,particulars,debit_amount,credit_amount,balance,entry_user,verified_user)
            VALUES
            ('$gl_date','$value_date','$instr_no','$particulars','$debit_amount','$credit_amount','$balance','$entry_user','$verified_user')";

    if (mysqli_query($conn, $sql)) {
        $count++;
    }
}

echo "<h2 style='text-align:center;color:green;'>$count records imported successfully!</h2>";
echo "<p style='text-align:center;'><a href='index.php'>Go Back</a></p>";
?>
