๐ Automating and Controlling SAP Vouchers with CodeIgniter 4, ODBC, and Service Layer: The Ultimate Guide & SapVouchersController Module
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. ⚙️๐ฅ
๐ 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:
List and Server-Side Paginate using DataTables to handle thousands of records without slowing down the browser.
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).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
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.
<?= \$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">×</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:
// 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! ๐ฑ✨
GitHub:
julio101290 Personal Website:
Cesar Systems YouTube (Drumming & Music Channel):
orionDracoDrums Tech Blog:
Shalom Now Blog


Comments
Post a Comment