<?php
$page_title = 'SAP Data';
require_once 'includes/load.php';
page_require_level(1); // Check user's permission level

// Set default page and number of records per page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$records_per_page = 50;

// Prepare the WHERE clause for filters
$where = '';
if (isset($_POST['filter'])) {
    $sap_code = remove_junk($db->escape($_POST['sap_code']));
    $sbu = remove_junk($db->escape($_POST['sbu']));
    $main_category = remove_junk($db->escape($_POST['main_category']));
    $sub_category = remove_junk($db->escape($_POST['sub_category']));
    $description = remove_junk($db->escape($_POST['description']));

    $where = " WHERE 1=1 ";
    if (!empty($sap_code)) {
        $where .= "AND sap_code LIKE '%$sap_code%' ";
    }
    if (!empty($sbu)) {
        $where .= "AND sbu LIKE '%$sbu%' ";
    }
    if (!empty($main_category)) {
        $where .= "AND main_category LIKE '%$main_category%' ";
    }
    if (!empty($sub_category)) {
        $where .= "AND sub_category LIKE '%$sub_category%' ";
    }
    if (!empty($description)) {
        $where .= "AND description LIKE '%$description%' ";
    }
}

function count_records($table, $where) {
    global $db;
    $sql = "SELECT COUNT(*) FROM $table $where";
    $result = $db->query($sql);
    $row = $result->fetch_row();
    return $row[0];
}

// Calculate the offset and total number of records
$offset = ($page - 1) * $records_per_page;
$total_records = count_records('sapdatafetch', $where);
$total_pages = ceil($total_records / $records_per_page);

function find_with_pagination($table, $offset, $records_per_page, $where) {
  global $db;
  $sql = "SELECT * FROM $table $where";
  // Add SAP code filter
  if (!empty($_POST['sap_code'])) {
      $sap_code = remove_junk($db->escape($_POST['sap_code']));
      $sql .= " AND sap_code LIKE '%$sap_code%'";
  }
  $sql .= " LIMIT $offset, $records_per_page";
  return find_by_sql($sql);
}

// Fetch the data from the database
$sap_data = find_with_pagination('sapdatafetch', $offset, $records_per_page, $where);

// Define the column names for the table
$columns = ['sap_code', 'sbu', 'main_category', 'sub_category', 'description', 'closing_stock'];
?>

<?php require_once 'layouts/header.php'; ?>
<div class="row">
    <div class="col-md-12">
        <?= display_msg($msg); ?>
    </div>
</div>
<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">SAP Data</h3>
            </div>
            <div class="panel-body">
            <form method="post" action="sapdata.php">
    <div class="form-row">
        <div class="form-group col-md-2">
            <label for="sbu">SBU</label>
            <input type="text" class="form-control" name="sbu" id="sbu" value="<?= isset($sbu) ? $sbu : '' ?>">
        </div>
        <div class="form-group col-md-2">
    <label for="sap_code">SAP Code</label>
    <input type="text" class="form-control" id="sap_code" name="sap_code" value="<?= isset($sap_code) ? $sap_code : ''; ?>">
</div>
        <div class="form-group col-md-2">
            <label for="main_category">Main Category</label>
            <input type="text" class="form-control" name="main_category" id="main_category" value="<?= isset($main_category) ? $main_category : '' ?>">
        </div>
        <div class="form-group col-md-2">
            <label for="sub_category">Sub Category</label>
            <input type="text" class="form-control" name="sub_category" id="sub_category" value="<?= isset($sub_category) ? $sub_category : '' ?>">
        </div>
        <div class="form-group col-md-4">
            <label for="description">Description</label>
            <input type="text" class="form-control" name="description" id="description" value="<?= isset($description) ? $description : '' ?>">
        </div>
    </div>
    <div class="form-row">
        <div class="form-group col-md-6">
            <button type="submit" name="filter" class="btn btn-primary"><i class="glyphicon glyphicon-search"></i> Filter</button>
            <a href="sapdata.php" class="btn btn-default"><i class="glyphicon glyphicon-refresh"></i> Reset</a>
        </div>
    </div>
</form>
                <hr>
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <?php foreach ($columns as $column) : ?>
                                <th><?= ucwords(str_replace('_', ' ', $column)) ?></th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($sap_data as $data) : ?>
                            <tr>
                                <?php foreach ($columns as $column) : ?>
                                    <td><?= $data[$column] ?></td>
                                <?php endforeach; ?>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <?php if ($total_records > 0) : ?>
                  <div class="text-center">
    <ul class="pagination">
        <?php if ($total_pages > 1) : ?>
            <?php $prev_page = $page - 1; ?>
            <?php $next_page = $page + 1; ?>
            <?php $adjacents = 5; ?>
            <?php if ($page > 1) : ?>
                <li><a href="?page=<?= $prev_page ?>">Previous</a></li>
            <?php endif; ?>
            <?php if ($total_pages <= 10 + ($adjacents * 2)) : ?>
                <?php for ($i = 1; $i <= $total_pages; $i++) : ?>
                    <?php if ($i == $page) : ?>
                        <li class="active"><a href="#"><?= $i ?></a></li>
                    <?php else : ?>
                        <li><a href="?page=<?= $i ?>"><?= $i ?></a></li>
                    <?php endif; ?>
                <?php endfor; ?>
            <?php else : ?>
                <?php if ($page <= 6 + $adjacents) : ?>
                    <?php for ($i = 1; $i <= 10 + ($adjacents * 2); $i++) : ?>
                        <?php if ($i == $page) : ?>
                            <li class="active"><a href="#"><?= $i ?></a></li>
                        <?php else : ?>
                            <li><a href="?page=<?= $i ?>"><?= $i ?></a></li>
                        <?php endif; ?>
                    <?php endfor; ?>
                    <li><a href="?page=<?= $total_pages ?>">Last</a></li>
                <?php elseif ($page > $total_pages - 6 - $adjacents) : ?>
                    <li><a href="?page=1">First</a></li>
                    <?php for ($i = $total_pages - 10 - ($adjacents * 2); $i <= $total_pages; $i++) : ?>
                        <?php if ($i == $page) : ?>
                            <li class="active"><a href="#"><?= $i ?></a></li>
                        <?php else : ?>
                            <li><a href="?page=<?= $i ?>"><?= $i ?></a></li>
                        <?php endif; ?>
                    <?php endfor; ?>
                <?php else : ?>
                    <li><a href="?page=1">First</a></li>
                    <?php for ($i = $page - $adjacents; $i <= $page + $adjacents; $i++) : ?>
                        <?php if ($i == $page) : ?>
                            <li class="active"><a href="#"><?= $i ?></a></li>
                        <?php else : ?>
                            <li><a href="?page=<?= $i ?>"><?= $i ?></a></li>
                        <?php endif; ?>
                    <?php endfor; ?>
                    <li><a href="?page=<?= $total_pages ?>">Last</a></li>
                <?php endif; ?>
            <?php endif; ?>
            <?php if ($page < $total_pages) : ?>
                <li><a href="?page=<?= $next_page ?>">Next</a></li>
            <?php endif; ?>
        <?php endif; ?>
    </ul>
</div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>
<?php require_once 'layouts/footer.php'; ?>