๐Ÿš€ Automating and Controlling SAP Vouchers with CodeIgniter 4, ODBC, and Service Layer: The Ultimate Guide & SapVouchersController Module

5a495453 12a4 4ac3 b016 e9e031eb6fc8


Hello everyone, programming enthusiasts, backend developers, and enterprise systems lovers! ๐Ÿ‘‹

If you work with SAP Business One in Linux or Windows environments, you know that interacting with User-Defined Tables (UDTs) and maintaining strict control over specific processes like voucher printing (@VALESINFO) can become a real challenge without the right tools.

Today, we are diving deep into the complete design and development of a professional module in CodeIgniter 4 using your boilerplateservicelayer package architecture. We will build the SapVouchersController and its corresponding Bootstrap + DataTables + Select2 View, optimizing direct access via ODBC to update printing statuses quickly, securely, and elegantly. ⚙️๐Ÿ”ฅ

e3eb59e7 e4d7 4311 8586 5c537b9147a4


๐Ÿ“‹ What Problem Does This Module Solve?

In daily warehouse and logistics operations, vouchers generated in SAP must be printed and controlled to prevent duplicates or erroneous deliveries. The custom field U_IMP inside the @VALESINFO user table handles two key states:

  • N: Not printed (available for the printing process).

  • Y: Printed (locked for new print jobs).

Our mission was to create a centralized web interface connected directly to SAP via ODBC that allows users to:

  1. List and Server-Side Paginate using DataTables to handle thousands of records without slowing down the browser.

  2. Advanced and Specific Filters: Search by voucher number (U_VALE), item code (U_ITEMCODE), SAP document entry (U_DOCENTRY), warehouse (U_WHSCODE), and print status (U_IMP).

  3. One-Click Updates or updates via a Detailed Edit Modal, instantly modifying the print status and logging every movement into the system's audit trail.

๐Ÿ› ️ 1. The Controller: SapVouchersController.php

This controller handles both the AJAX response for DataTables and the endpoints for warehouse lookups, individual voucher details, and executing direct UPDATE statements to the SAP database via ODBC.

PHP
<?php

namespace julio101290\boilerplateservicelayer\Controllers;

use App\Controllers\BaseController;
use CodeIgniter\API\ResponseTrait;
use julio101290\boilerplatelog\Models\LogModel;
use julio101290\boilerplatecompanies\Models\EmpresasModel;
use julio101290\boilerplateservicelayer\Models\SapservicelayerModel;
use julio101290\boilerplateservicelayer\Controllers\SapservicelayerController;
use julio101290\boilerplatebranchoffice\Models\BranchofficesModel;
use julio101290\boilerplateservicelayer\Models\User_sap_linkModel;
use julio101290\boilerplateservicelayer\Models\Link_sap_branchofficeModel;

class SapVouchersController extends BaseController {

    use ResponseTrait;

    protected $log;
    protected $link_sap_branchoffice;
    protected $empresa;
    protected $serviceLayerModel;
    protected $serviceLayerController;
    protected $branchoffice;
    protected $userLinkSap;

    public function __construct() {
        $this->link_sap_branchoffice = new Link_sap_branchofficeModel();
        $this->log = new LogModel();
        $this->empresa = new EmpresasModel();
        $this->serviceLayerModel = new SapservicelayerModel();
        $this->serviceLayerController = new SapservicelayerController();
        $this->branchoffice = new BranchofficesModel();
        $this->userLinkSap = new User_sap_linkModel();
        helper(['menu', 'utilerias']);
    }

    /**
     * Main view and AJAX endpoint for DataTables with custom filters
     */
    public function index() {
        helper('auth');

        if ($this->request->isAJAX()) {
            try {
                $conn = $this->connectODBC();

                $draw = (int) ($this->request->getGet('draw') ?? 1);
                $start = (int) ($this->request->getGet('start') ?? 0);
                $length = (int) ($this->request->getGet('length') ?? 10);
                $search = $this->request->getGet('search')['value'] ?? '';

                // Specific requested filters
                $filterVale     = trim($this->request->getGet('vale') ?? '');
                $filterItemCode = trim($this->request->getGet('itemCode') ?? '');
                $filterDocEntry = trim($this->request->getGet('docEntry') ?? '');
                $filterWhsCode  = trim($this->request->getGet('whsCode') ?? '');
                $filterImp      = trim($this->request->getGet('uImp') ?? '');

                // Dynamic sorting
                $orderParam = $this->request->getGet('order');
                $orderColIndex = isset($orderParam[0]['column']) ? (int) $orderParam[0]['column'] : 1;
                $orderDirRaw = isset($orderParam[0]['dir']) ? strtolower($orderParam[0]['dir']) : 'desc';
                $orderDir = ($orderDirRaw === 'asc') ? 'ASC' : 'DESC';

                $columnsMap = [
                    1  => 'CAST(v."Code" AS INTEGER)',
                    2  => 'v."U_VALE"',
                    3  => 'v."U_DOCNUM"',
                    4  => 'v."U_DOCENTRY"',
                    5  => 'v."U_ITEMCODE"',
                    6  => 'v."U_CANT"',
                    7  => 'v."U_WHSCODE"',
                    8  => 'v."U_IMP"',
                    9  => 'v."U_CANCEL"',
                    10 => 'v."U_DOCDATE"',
                    11 => 'v."U_DOCDATEIMP"',
                ];

                $orderBy = $columnsMap[$orderColIndex] ?? 'CAST(v."Code" AS INTEGER)';

                // Dynamic filters building
                $whereExtra = '';

                // Global DataTables search
                if (!empty($search)) {
                    $searchClean = str_replace("'", "''", $search);
                    $whereExtra .= "
                        AND (
                            v.\"Code\" LIKE '%{$searchClean}%'
                            OR CAST(v.\"U_VALE\" AS NVARCHAR(50)) LIKE '%{$searchClean}%'
                            OR v.\"U_ITEMCODE\" LIKE '%{$searchClean}%'
                            OR CAST(v.\"U_DOCENTRY\" AS NVARCHAR(50)) LIKE '%{$searchClean}%'
                            OR CAST(v.\"U_DOCNUM\" AS NVARCHAR(50)) LIKE '%{$searchClean}%'
                            OR v.\"U_WHSCODE\" LIKE '%{$searchClean}%'
                        )
                    ";
                }

                // 1. Filter by Voucher Number (U_VALE)
                if ($filterVale !== '') {
                    $valeClean = str_replace("'", "''", $filterVale);
                    $whereExtra .= " AND CAST(v.\"U_VALE\" AS NVARCHAR(50)) LIKE '%{$valeClean}%' ";
                }

                // 2. Filter by ItemCode (U_ITEMCODE)
                if ($filterItemCode !== '') {
                    $itemClean = str_replace("'", "''", strtoupper($filterItemCode));
                    $whereExtra .= " AND UPPER(v.\"U_ITEMCODE\") LIKE '%{$itemClean}%' ";
                }

                // 3. Filter by DocEntry (U_DOCENTRY)
                if ($filterDocEntry !== '') {
                    $docEntryClean = str_replace("'", "''", $filterDocEntry);
                    $whereExtra .= " AND CAST(v.\"U_DOCENTRY\" AS NVARCHAR(50)) LIKE '%{$docEntryClean}%' ";
                }

                // 4. Filter by Warehouse (U_WHSCODE)
                if ($filterWhsCode !== '') {
                    $whsClean = str_replace("'", "''", strtoupper($filterWhsCode));
                    $whereExtra .= " AND UPPER(v.\"U_WHSCODE\") LIKE '%{$whsClean}%' ";
                }

                // 5. Filter by Print Status (U_IMP)
                if ($filterImp === 'Y') {
                    $whereExtra .= " AND v.\"U_IMP\" = 'Y' ";
                } elseif ($filterImp === 'N') {
                    $whereExtra .= " AND (v.\"U_IMP\" <> 'Y' OR v.\"U_IMP\" IS NULL) ";
                }

                // Unfiltered total
                $sqlTotal = 'SELECT COUNT(1) AS "total" FROM "@VALESINFO" v';
                $rsTotal = odbc_exec($conn, $sqlTotal);
                $totalRecords = 0;
                if ($rsTotal && ($rowTotal = odbc_fetch_array($rsTotal))) {
                    $totalRecords = (int) ($rowTotal['total'] ?? $rowTotal['TOTAL'] ?? 0);
                    odbc_free_result($rsTotal);
                }

                // Filtered total
                $sqlFiltered = "
                    SELECT COUNT(1) AS \"total\"
                    FROM \"@VALESINFO\" v
                    WHERE 1=1 {$whereExtra}
                ";
                $rsFiltered = odbc_exec($conn, $sqlFiltered);
                $filteredRecords = $totalRecords;
                if ($rsFiltered && ($rowFiltered = odbc_fetch_array($rsFiltered))) {
                    $filteredRecords = (int) ($rowFiltered['total'] ?? $rowFiltered['TOTAL'] ?? 0);
                    odbc_free_result($rsFiltered);
                }

                // Paginated query
                $sql = "
                    SELECT
                        v.\"Code\",
                        v.\"Name\",
                        v.\"U_OBJTYPE\",
                        v.\"U_DOCNUM\",
                        v.\"U_ITEMCODE\",
                        v.\"U_VALE\",
                        v.\"U_CANT\",
                        v.\"U_IMP\",
                        v.\"U_DOCENTRY\",
                        v.\"U_CANCEL\",
                        v.\"U_WHSCODE\",
                        v.\"U_DOCENTRYUSO\",
                        v.\"U_OBJTYPEUSO\",
                        v.\"U_DOCDATE\",
                        v.\"U_DOCDATEIMP\"
                    FROM \"@VALESINFO\" v
                    WHERE 1=1
                      {$whereExtra}
                    ORDER BY {$orderBy} {$orderDir}
                    LIMIT {$length} OFFSET {$start}
                ";

                $rs = odbc_exec($conn, $sql);
                if (!$rs) {
                    throw new \Exception('Error querying vouchers: ' . odbc_errormsg($conn));
                }

                $data = [];
                while ($row = odbc_fetch_array($rs)) {
                    $data[] = [
                        'Code'          => $this->toUtf8($row['Code']),
                        'Name'          => $this->toUtf8($row['Name'] ?? ''),
                        'U_OBJTYPE'     => $this->toUtf8($row['U_OBJTYPE'] ?? ''),
                        'U_DOCNUM'      => $this->toUtf8($row['U_DOCNUM'] ?? ''),
                        'U_ITEMCODE'    => $this->toUtf8($row['U_ITEMCODE'] ?? ''),
                        'U_VALE'        => $this->toUtf8($row['U_VALE'] ?? ''),
                        'U_CANT'        => $this->toUtf8($row['U_CANT'] ?? '0'),
                        'U_IMP'         => $this->toUtf8($row['U_IMP'] ?? 'N'),
                        'U_DOCENTRY'    => $this->toUtf8($row['U_DOCENTRY'] ?? ''),
                        'U_CANCEL'      => $this->toUtf8($row['U_CANCEL'] ?? 'N'),
                        'U_WHSCODE'     => $this->toUtf8($row['U_WHSCODE'] ?? ''),
                        'U_DOCENTRYUSO' => $this->toUtf8($row['U_DOCENTRYUSO'] ?? ''),
                        'U_OBJTYPEUSO'  => $this->toUtf8($row['U_OBJTYPEUSO'] ?? ''),
                        'U_DOCDATE'     => $this->formatSapDate($row['U_DOCDATE'] ?? null),
                        'U_DOCDATEIMP'  => $this->formatSapDate($row['U_DOCDATEIMP'] ?? null, true),
                    ];
                }

                odbc_free_result($rs);
                odbc_close($conn);

                return $this->response->setJSON([
                    'draw'            => $draw,
                    'recordsTotal'    => $totalRecords,
                    'recordsFiltered' => $filteredRecords,
                    'data'            => $data
                ]);
            } catch (\Throwable $e) {
                return $this->response->setJSON([
                    'draw'            => (int) ($this->request->getGet('draw') ?? 1),
                    'recordsTotal'    => 0,
                    'recordsFiltered' => 0,
                    'data'            => [],
                    'error'           => true,
                    'message'         => $e->getMessage()
                ]);
            }
        }

        $data = [
            'title'     => 'SAP Vouchers',
            'subtitle'  => 'Voucher Printing Control (@VALESINFO)',
            'box_title' => 'Voucher List'
        ];

        return view('julio101290\boilerplateservicelayer\Views\vouchers', $data);
    }

    /**
     * Warehouses catalog (OWHS) for Select2 filter
     */
    public function getWarehousesAjax() {
        try {
            $search = $this->request->getGet('searchTerm') ?? '';
            $conn = $this->connectODBC();

            $where = '';
            if (!empty($search)) {
                $searchClean = str_replace("'", "''", trim($search));
                $where = "
                    WHERE \"WhsCode\" LIKE '%{$searchClean}%'
                       OR \"WhsName\" LIKE '%{$searchClean}%'
                ";
            }

            $sql = "
                SELECT \"WhsCode\", \"WhsName\"
                FROM OWHS
                {$where}
                ORDER BY \"WhsCode\" ASC
                LIMIT 50
            ";

            $rs = odbc_exec($conn, $sql);
            if (!$rs) {
                throw new \Exception('SQL Error in OWHS: ' . odbc_errormsg($conn));
            }

            $data = [];
            while ($row = odbc_fetch_array($rs)) {
                $code = $this->toUtf8($row['WhsCode']);
                $name = $this->toUtf8($row['WhsName']);
                $data[] = [
                    'id'   => $code,
                    'text' => $code . ($name ? ' - ' . $name : '')
                ];
            }

            odbc_free_result($rs);
            odbc_close($conn);

            return $this->response->setJSON(['data' => $data]);
        } catch (\Throwable $e) {
            return $this->response->setJSON([
                'data'    => [],
                'error'   => true,
                'message' => $e->getMessage()
            ]);
        }
    }

    /**
     * Get voucher details by its Code
     */
    public function getVoucher($code = null) {
        try {
            if (empty($code)) {
                return $this->response->setJSON(['error' => true, 'message' => 'Invalid voucher code']);
            }

            $codeClean = str_replace("'", "''", trim(urldecode($code)));
            $conn = $this->connectODBC();

            $sql = "
                SELECT
                    v.\"Code\",
                    v.\"Name\",
                    v.\"U_OBJTYPE\",
                    v.\"U_DOCNUM\",
                    v.\"U_ITEMCODE\",
                    v.\"U_VALE\",
                    v.\"U_CANT\",
                    v.\"U_IMP\",
                    v.\"U_DOCENTRY\",
                    v.\"U_CANCEL\",
                    v.\"U_WHSCODE\",
                    v.\"U_DOCDATE\",
                    v.\"U_DOCDATEIMP\"
                FROM \"@VALESINFO\" v
                WHERE v.\"Code\" = '{$codeClean}'
            ";

            $rs = odbc_exec($conn, $sql);
            if (!$rs) {
                throw new \Exception('SQL Error: ' . odbc_errormsg($conn));
            }

            $row = odbc_fetch_array($rs);
            odbc_free_result($rs);
            odbc_close($conn);

            if (!$row) {
                return $this->response->setJSON(['error' => true, 'message' => 'Requested voucher not found']);
            }

            return $this->response->setJSON([
                'Code'         => $this->toUtf8($row['Code']),
                'U_VALE'       => $this->toUtf8($row['U_VALE'] ?? ''),
                'U_DOCNUM'     => $this->toUtf8($row['U_DOCNUM'] ?? ''),
                'U_DOCENTRY'   => $this->toUtf8($row['U_DOCENTRY'] ?? ''),
                'U_ITEMCODE'   => $this->toUtf8($row['U_ITEMCODE'] ?? ''),
                'U_CANT'       => $this->toUtf8($row['U_CANT'] ?? '0'),
                'U_WHSCODE'    => $this->toUtf8($row['U_WHSCODE'] ?? ''),
                'U_IMP'        => $this->toUtf8($row['U_IMP'] ?? 'N'),
                'U_CANCEL'     => $this->toUtf8($row['U_CANCEL'] ?? 'N'),
                'U_DOCDATE'    => $this->formatSapDate($row['U_DOCDATE'] ?? null),
                'U_DOCDATEIMP' => $this->formatSapDate($row['U_DOCDATEIMP'] ?? null, true)
            ]);
        } catch (\Throwable $e) {
            return $this->response->setJSON([
                'error'   => true,
                'message' => $e->getMessage()
            ]);
        }
    }

    /**
     * Directly updates U_IMP ('Y' or 'N') in "@VALESINFO" via ODBC
     */
    public function save() {
        helper('auth');
        $userName = user()->username ?? 'admin';

        $post = $this->request->getPost();
        $code = trim($post['Code'] ?? '');
        $uImp = strtoupper(trim($post['U_IMP'] ?? 'N'));

        if (empty($code)) {
            return $this->respond([
                'status'  => 400,
                'message' => 'The record code is required.'
            ], 400);
        }

        if (!in_array($uImp, ['Y', 'N'], true)) {
            return $this->respond([
                'status'  => 400,
                'message' => 'Print status must be Y (Printed) or N (Not Printed).'
            ], 400);
        }

        try {
            $conn = $this->connectODBC();
            odbc_autocommit($conn, true);

            $codeClean = str_replace("'", "''", $code);
            $uImpClean = ($uImp === 'Y') ? 'Y' : 'N';

            $sqlUpdate = "
                UPDATE \"@VALESINFO\"
                SET \"U_IMP\" = '{$uImpClean}',
                    \"U_DOCDATEIMP\" = " . ($uImpClean === 'Y' ? "CURRENT_TIMESTAMP" : "NULL") . "
                WHERE \"Code\" = '{$codeClean}'
            ";

            $rsUpdate = odbc_exec($conn, $sqlUpdate);
            if (!$rsUpdate) {
                $odbcError = odbc_errormsg($conn);
                odbc_close($conn);
                return $this->respond([
                    'status'  => 500,
                    'message' => 'Error updating voucher in SAP: ' . $odbcError
                ], 500);
            }

            odbc_commit($conn);
            odbc_close($conn);
        } catch (\Throwable $e) {
            return $this->respond([
                'status'  => 500,
                'message' => 'ODBC error updating print status: ' . $e->getMessage()
            ], 500);
        }

        // Register in audit log
        $this->log->save([
            'description' => "Changed U_IMP status to '{$uImp}' in @VALESINFO (Code: {$code})",
            'user'        => $userName
        ]);

        return $this->respond([
            'status'  => 200,
            'message' => "Print status updated to '{$uImp}' successfully."
        ], 200);
    }

    // =========================================================================
    // PRIVATE HELPER METHODS
    // =========================================================================

    private function connectODBC() {
        $dataConect = $this->serviceLayerModel->first();
        if (!$dataConect) {
            throw new \Exception('SAP connection configuration not found.');
        }

        $conn = odbc_connect(
            $dataConect['nameODBC'],
            $dataConect['userODBC'],
            $dataConect['passwordODBC']
        );

        if (!$conn) {
            throw new \Exception('ODBC connection error: ' . odbc_errormsg());
        }

        if (!odbc_exec($conn, 'SET SCHEMA "' . $dataConect['companyDB'] . '"')) {
            throw new \Exception('SET SCHEMA error: ' . odbc_errormsg($conn));
        }

        return $conn;
    }

    private function toUtf8($value) {
        if (is_null($value)) {
            return '';
        }
        if (mb_check_encoding($value, 'UTF-8')) {
            return $value;
        }
        return mb_convert_encoding($value, 'UTF-8', 'ISO-8859-1');
    }

    private function formatSapDate($dateStr, $includeTime = false) {
        if (empty($dateStr)) {
            return '';
        }
        $ts = strtotime($dateStr);
        if (!$ts) {
            return $this->toUtf8($dateStr);
        }
        return $includeTime ? date('Y-m-d H:i:s', $ts) : date('Y-m-d', $ts);
    }
}

๐Ÿ’ป 2. The View: vouchers.php

The visual interface combines AdminLTE, Bootstrap, SweetAlert2, and DataTables, allowing smooth filtering and interactive quick-toggle action buttons.

HTML
<?= \$this->include('julio101290\boilerplate\Views\load\select2') ?>
<?= \$this->include('julio101290\boilerplate\Views\load\datatables') ?>
<?= \$this->extend('julio101290\boilerplate\Views\layout\sweetalert') ?>
<?= \$this->extend('julio101290\boilerplate\Views\layout\index') ?>
<?= \$this->section('content') ?>

<!-- Modal to edit voucher print status -->
<div class="modal fade" id="modalVoucher" tabindex="-1" role="dialog" aria-hidden="true">
    <div class="modal-dialog modal-lg" role="document">
        <div class="modal-content">
            <div class="modal-header bg-primary text-white">
                <h5 class="modal-title" id="modalVoucherLabel">Change Voucher Print Status</h5>
                <button type="button" class="close text-white" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
            </div>
            <div class="modal-body">
                <form id="formVoucher">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="Code">Code (Internal ID)</label>
                                <input type="text" class="form-control" name="Code" id="Code" readonly>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="U_VALE">Voucher #</label>
                                <input type="text" class="form-control" id="U_VALE" readonly>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="U_DOCNUM">DocNum</label>
                                <input type="text" class="form-control" id="U_DOCNUM" readonly>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="form-group">
                                <label for="U_DOCENTRY">DocEntry</label>
                                <input type="text" class="form-control" id="U_DOCENTRY" readonly>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col-md-4">
                            <div class="form-group">
                                <label for="U_ITEMCODE">ItemCode</label>
                                <input type="text" class="form-control" id="U_ITEMCODE" readonly>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="form-group">
                                <label for="U_CANT">Quantity</label>
                                <input type="text" class="form-control" id="U_CANT" readonly>
                            </div>
                        </div>
                        <div class="col-md-4">
                            <div class="form-group">
                                <label for="U_WHSCODE">Warehouse</label>
                                <input type="text" class="form-control" id="U_WHSCODE" readonly>
                            </div>
                        </div>
                    </div>

                    <hr>

                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="U_IMP" class="font-weight-bold text-primary">
                                    <i class="fas fa-print mr-1"></i> Printed? (U_IMP) <span class="text-danger">*</span>
                                </label>
                                <select class="form-control form-control-lg" name="U_IMP" id="U_IMP" required>
                                    <option value="N">N - Not Printed (Available for printing)</option>
                                    <option value="Y">Y - Already Printed</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <label for="U_DOCDATEIMP">Last Print Date</label>
                                <input type="text" class="form-control" id="U_DOCDATEIMP" readonly>
                            </div>
                        </div>
                    </div>
                </form>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-primary" id="btnSaveVoucher">
                    <i class="fas fa-save mr-1"></i> Save Changes
                </button>
            </div>
        </div>
    </div>
</div>

<div class="card card-default">
    <div class="card-header">
        <h3 class="card-title"><i class="fas fa-ticket-alt mr-1"></i> SAP Voucher Printing Control</h3>
        <div class="card-tools">
            <button class="btn btn-outline-secondary btn-sm" id="btnClearFilters">
                <i class="fas fa-eraser mr-1"></i> Clear Filters
            </button>
        </div>
    </div>
    <div class="card-body">
        <!-- Specific Filters Bar -->
        <div class="row mb-3">
            <div class="col-md-2">
                <label for="filterVale"><i class="fas fa-hashtag mr-1"></i> Voucher #</label>
                <input type="text" id="filterVale" class="form-control" placeholder="Ex. 178">
            </div>
            <div class="col-md-3">
                <label for="filterItemCode"><i class="fas fa-box mr-1"></i> ItemCode</label>
                <input type="text" id="filterItemCode" class="form-control text-uppercase" placeholder="Ex. TAG0004">
            </div>
            <div class="col-md-2">
                <label for="filterDocEntry"><i class="fas fa-file-alt mr-1"></i> DocEntry</label>
                <input type="text" id="filterDocEntry" class="form-control" placeholder="Ex. 8736">
            </div>
            <div class="col-md-3">
                <label for="filterWhsCode"><i class="fas fa-warehouse mr-1"></i> Warehouse</label>
                <select id="filterWhsCode" class="form-control" style="width: 100%;">
                    <option value="">All warehouses</option>
                </select>
            </div>
            <div class="col-md-2">
                <label for="filterImp"><i class="fas fa-print mr-1"></i> Printed (U_IMP)</label>
                <select id="filterImp" class="form-control">
                    <option value="">All</option>
                    <option value="N" selected>N - Not Printed</option>
                    <option value="Y">Y - Printed</option>
                </select>
            </div>
        </div>

        <div class="table-responsive">
            <table id="tableVouchers" class="table table-striped table-hover table-sm">
                <thead>
                    <tr>
                        <th width="100">Actions</th>
                        <th>Code</th>
                        <th>Voucher #</th>
                        <th>DocNum</th>
                        <th>DocEntry</th>
                        <th>ItemCode</th>
                        <th>Qty.</th>
                        <th>Warehouse</th>
                        <th>Printed (U_IMP)</th>
                        <th>Canceled</th>
                        <th>Doc Date</th>
                        <th>Print Date</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
    </div>
</div>

<?= \$this->endSection() ?>

<?= \$this->section('js') ?>
<script>
    $(function () {$('#filterWhsCode').select2({
            placeholder: 'All warehouses',
            allowClear: true,
            ajax: {
                url: '<?= base_url('admin/servicelayer/vouchers/getWarehousesAjax') ?>',
                dataType: 'json',
                delay: 250,
                data: function (params) {
                    return { searchTerm: params.term || '' };
                },
                processResults: function (data) {
                    return { results: data.data || [] };
                }
            }
        });

        var tableVouchers = \$('#tableVouchers').DataTable({
            processing: true,
            serverSide: true,
            responsive: true,
            autoWidth: false,
            order: [[1, 'desc']],
            ajax: {
                url: '<?= base_url('admin/servicelayer/vouchers') ?>',
                method: 'GET',
                dataType: 'json',
                data: function (d) {
                    d.vale     = \$('#filterVale').val();
                    d.itemCode = \$('#filterItemCode').val();
                    d.docEntry = \$('#filterDocEntry').val();
                    d.whsCode  = \$('#filterWhsCode').val();
                    d.uImp     = \$('#filterImp').val();
                },
                dataSrc: function (json) {
                    return json.data || [];
                }
            },
            columnDefs: [
                { targets: 0, orderable: false, searchable: false, width: '100px' }
            ],
            columns: [
                {
                    data: null,
                    render: function (data, type, row) {
                        var code = encodeURIComponent(row.Code || '');
                        var currentImp = (row.U_IMP === 'Y') ? 'Y' : 'N';
                        var nextImp = (currentImp === 'Y') ? 'N' : 'Y';
                        var toggleBtnClass = (currentImp === 'Y') ? 'btn-secondary' : 'btn-success';
                        var toggleTitle = (currentImp === 'Y') ? 'Change to N (Not Printed)' : 'Change to Y (Printed)';

                        return `
                        <div class="btn-group" role="group">
                            <button type="button" class="btn btn-warning btn-sm btnEditVoucher" data-code="${code}" title="Edit in Modal">
                                <i class="fas fa-edit"></i>
                            </button>
                            <button type="button" class="btn ${toggleBtnClass} btn-sm btnToggleImp" data-code="${row.Code}" data-vale="${row.U_VALE}" data-next="${nextImp}" title="${toggleTitle}">
                                <i class="fas fa-sync-alt"></i> ${nextImp}
                            </button>
                        </div>`;
                    }
                },
                { data: 'Code' },
                { data: 'U_VALE', render: function (data) { return `<strong>${data || ''}</strong>`; } },
                { data: 'U_DOCNUM' },
                { data: 'U_DOCENTRY' },
                { data: 'U_ITEMCODE' },
                { data: 'U_CANT' },
                { data: 'U_WHSCODE', render: function (data) { return `<span class="badge badge-light border">${data || ''}</span>`; } },
                {
                    data: 'U_IMP',
                    render: function (data) {
                        return data === 'Y'
                            ? '<span class="badge badge-success"><i class="fas fa-check mr-1"></i> Y (Printed)</span>'
                            : '<span class="badge badge-warning"><i class="fas fa-clock mr-1"></i> N (Not Printed)</span>';
                    }
                },
                {
                    data: 'U_CANCEL',
                    render: function (data) {
                        return data === 'Y' ? '<span class="badge badge-danger">Canceled</span>' : '<span class="badge badge-secondary">Active</span>';
                    }
                },
                { data: 'U_DOCDATE', defaultContent: '' },
                { data: 'U_DOCDATEIMP', defaultContent: '' }
            ],
            language: { processing: "Loading vouchers..." }
        });

        var filterTimer = null;
        \$('#filterVale, #filterItemCode, #filterDocEntry').on('keyup change', function () {
            clearTimeout(filterTimer);
            filterTimer = setTimeout(function () { tableVouchers.ajax.reload(); }, 350);
        });

        \$('#filterWhsCode, #filterImp').on('change', function () {
            tableVouchers.ajax.reload();
        });

        \$('#btnClearFilters').on('click', function () {
            \$('#filterVale').val('');
            \$('#filterItemCode').val('');
            \$('#filterDocEntry').val('');
            \$('#filterWhsCode').val(null).trigger('change.select2');
            \$('#filterImp').val('N');
            tableVouchers.ajax.reload();
        });

        \$('#tableVouchers tbody').on('click', '.btnEditVoucher', function () {
            var code = \$(this).attr('data-code');
            if (!code) return;

            \$.ajax({
                url: '<?= base_url('admin/servicelayer/vouchers/getVoucher') ?>/' + code,
                method: 'GET',
                dataType: 'json',
                success: function (resp) {
                    if (resp.Code) {
                        \$('#Code').val(resp.Code);
                        \$('#U_VALE').val(resp.U_VALE || '');
                        \$('#U_DOCNUM').val(resp.U_DOCNUM || '');
                        \$('#U_DOCENTRY').val(resp.U_DOCENTRY || '');
                        \$('#U_ITEMCODE').val(resp.U_ITEMCODE || '');
                        \$('#U_CANT').val(resp.U_CANT || '');
                        \$('#U_WHSCODE').val(resp.U_WHSCODE || '');
                        \$('#U_IMP').val(resp.U_IMP === 'Y' ? 'Y' : 'N');
                        \$('#U_DOCDATEIMP').val(resp.U_DOCDATEIMP || 'No record');
                        \$('#modalVoucherLabel').text('Edit Voucher #' + (resp.U_VALE || resp.Code));
                        \$('#modalVoucher').modal('show');
                    } else {
                        Swal.fire('Error', resp.message || 'Voucher not found', 'error');
                    }
                }
            });
        });

        \$('#tableVouchers tbody').on('click', '.btnToggleImp', function () {
            var code = \$(this).attr('data-code');
            var vale = \$(this).attr('data-vale');
            var nextImp = \$(this).attr('data-next');
            var descEstado = (nextImp === 'Y') ? 'Y (Printed)' : 'N (Not Printed)';

            Swal.fire({
                title: 'Change print status?',
                html: `Voucher <strong>#${vale}</strong> (Code: ${code}) will be changed to <strong>${descEstado}</strong>.`,
                icon: 'question',
                type: 'question',
                showCancelButton: true,
                confirmButtonText: 'Yes, update',
                cancelButtonText: 'Cancel'
            }).then((result) => {
                if (result.value || result.isConfirmed) {
                    actualizarEstadoVale(code, nextImp);
                }
            });
        });

        \$('#btnSaveVoucher').on('click', function () {
            var code = \$('#Code').val();
            var uImp = \$('#U_IMP').val();

            if (!code) {
                Swal.fire('Attention', 'Invalid voucher code', 'warning');
                return;
            }

            var \$btn = $(this);$btn.prop('disabled', true).html('<i class="fas fa-spinner fa-spin mr-1"></i> Saving...');

            actualizarEstadoVale(code, uImp, function () {
                \$btn.prop('disabled', false).html('<i class="fas fa-save mr-1"></i> Save Changes');
                \$('#modalVoucher').modal('hide');
            }, function () {
                \$btn.prop('disabled', false).html('<i class="fas fa-save mr-1"></i> Save Changes');
            });
        });

        function actualizarEstadoVale(code, uImp, onSuccess, onError) {
            \$.ajax({
                url: '<?= base_url('admin/servicelayer/vouchers/save') ?>',
                method: 'POST',
                data: { Code: code, U_IMP: uImp },
                dataType: 'json',
                success: function (resp) {
                    if (resp.status === 200) {
                        if (typeof onSuccess === 'function') onSuccess();
                        Swal.fire({
                            toast: true,
                            position: 'top-end',
                            icon: 'success',
                            type: 'success',
                            title: resp.message || 'Status updated',
                            showConfirmButton: false,
                            timer: 2000
                        });
                        tableVouchers.ajax.reload(null, false);
                    } else {
                        if (typeof onError === 'function') onError();
                        Swal.fire('Error', resp.message || 'Could not update', 'error');
                    }
                },
                error: function (xhr) {
                    if (typeof onError === 'function') onError();
                    var msg = (xhr.responseJSON && xhr.responseJSON.message) ? xhr.responseJSON.message : 'Server error';
                    Swal.fire('Error', msg, 'error');
                }
            });
        }

        \$('#modalVoucher').draggable({ handle: '.modal-header' });
    });
</script>
<?= \$this->endSection() ?>

๐Ÿ”’ 3. Security and Routes with Permissions

To ensure that only authorized users access the module, we protect the routes with the sapvouchers permission filter:

PHP
// SAP Voucher Printing Control Module (@VALESINFO)
$routes->get('servicelayer/vouchers', 'SapVouchersController::index', [
    'namespace' => 'julio101290\boilerplateservicelayer\Controllers',
    'filter'    => 'permission:sapvouchers'
]);

$routes->get('servicelayer/vouchers/getWarehousesAjax', 'SapVouchersController::getWarehousesAjax', [
    'namespace' => 'julio101290\boilerplateservicelayer\Controllers',
    'filter'    => 'permission:sapvouchers'
]);

$routes->get('servicelayer/vouchers/getVoucher/(:any)', 'SapVouchersController::getVoucher/$1', [
    'namespace' => 'julio101290\boilerplateservicelayer\Controllers',
    'filter'    => 'permission:sapvouchers'
]);

$routes->post('servicelayer/vouchers/save', 'SapVouchersController::save', [
    'namespace' => 'julio101290\boilerplateservicelayer\Controllers',
    'filter'    => 'permission:sapvouchers'
]);

๐ŸŒŸ Conclusion

With this implementation, we achieved an extremely robust, fast, and clean bridge between CodeIgniter 4 and SAP Business One. By utilizing direct queries via ODBC for user-defined tables (@VALESINFO), we bypass Service Layer bottlenecks and ensure optimal print control for any logistical operation.

๐ŸŒ Connect With Me

Did you like this development or have questions about integrations with SAP and PHP? Follow me on my official channels and social media! ๐Ÿ“ฑ✨

Comments

Popular posts from this blog

๐Ÿ›ก️ The Ultimate Guide and Technical Manual for CyberBackup (Bender Rodrรญguez Edition): The Only Backup System That Respects Humans... In Its Own Way

Updates in Framework Codeigniter 4.4.4 to 4.5.2

๐Ÿ“ท How to Install V380 Pro on Linux Mint (Step by Step)

๐Ÿš€ How to Automate TikTok Shorts Uploads from Linux: Playwright, Local AI (Ollama), and Systemd Without Getting Banned

๐Ÿค ⚡ Deconstructing MySQL Error 150: How a Corrupted Dump Turned into a Cyberpunk SQL Injector