353 lines
11 KiB
PHP
Executable File
353 lines
11 KiB
PHP
Executable File
<?php
|
|
|
|
namespace App\Services\Charts;
|
|
|
|
use Carbon\Carbon;
|
|
use DB;
|
|
use Illuminate\Support\Facades\Log;
|
|
|
|
final class DashboardChartService
|
|
{
|
|
/**
|
|
* Build all dashboard charts in a Chart.js-friendly shape.
|
|
*
|
|
* Every chart method is defensive because the legacy WMS/M3 data source is
|
|
* an ODBC connection and some environments do not have IBM i available.
|
|
* The dashboard still renders cleanly with zeroed data instead of failing.
|
|
*
|
|
* @return array<string, mixed>
|
|
*/
|
|
public function dashboard(): array
|
|
{
|
|
return [
|
|
'metrics' => $this->metrics(),
|
|
'charts' => [
|
|
'pickingLastSevenDays' => $this->pickingLastSevenDays(),
|
|
'dispatchesLastSevenDays' => $this->dispatchesLastSevenDays(),
|
|
'stockMovementsByType' => $this->stockMovementsByType(),
|
|
'lowStockByWarehouse' => $this->lowStockByWarehouse(),
|
|
'warehouseUtilisation' => $this->warehouseUtilisation(),
|
|
'systemHealth' => $this->systemHealth(),
|
|
],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<int, array{label: string, value: int|string, hint: string, icon: string}>
|
|
*/
|
|
private function metrics(): array
|
|
{
|
|
return [
|
|
[
|
|
'label' => 'Open pick waves',
|
|
'value' => $this->safeCount('ICM688M1', "A4PICKSTS IN ('B', 'P')"),
|
|
'hint' => 'Active or in-progress pick work',
|
|
'icon' => 'fa-box-open',
|
|
],
|
|
[
|
|
'label' => 'Pending manifests',
|
|
'value' => $this->safeCount('ICM705M1', "APSPARE2 IN ('', 'P')"),
|
|
'hint' => 'Waiting for close or confirmation',
|
|
'icon' => 'fa-truck-loading',
|
|
],
|
|
[
|
|
'label' => 'M3 errors',
|
|
'value' => $this->safeCount('ICM699M', "SHERROR = 'X'"),
|
|
'hint' => 'Messages needing attention',
|
|
'icon' => 'fa-exclamation-triangle',
|
|
],
|
|
[
|
|
'label' => 'Warehouses',
|
|
'value' => $this->safeCount('IC670M'),
|
|
'hint' => 'Known warehouse records',
|
|
'icon' => 'fa-warehouse',
|
|
],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function pickingLastSevenDays(): array
|
|
{
|
|
$labels = $this->lastDaysLabels(7);
|
|
$data = $this->emptySeries($labels);
|
|
|
|
try {
|
|
$rows = DB::connection('odbc')->select(
|
|
'SELECT DATE(A5PICKCMTS) AS PICK_DATE, COUNT(*) AS TOTAL
|
|
FROM EWDAT.ICM688M2
|
|
WHERE A5PICKCMTS >= CURRENT DATE - 7 DAYS
|
|
GROUP BY DATE(A5PICKCMTS)
|
|
ORDER BY PICK_DATE',
|
|
);
|
|
|
|
foreach ($rows as $row) {
|
|
$key = Carbon::parse($row->PICK_DATE)->format('d M');
|
|
if (array_key_exists($key, $data)) {
|
|
$data[$key] = (int) $row->TOTAL;
|
|
}
|
|
}
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->lineChart($labels, array_values($data), 'Pick lines completed');
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function dispatchesLastSevenDays(): array
|
|
{
|
|
$labels = $this->lastDaysLabels(7);
|
|
$data = $this->emptySeries($labels);
|
|
|
|
try {
|
|
$rows = DB::connection('odbc')->select(
|
|
"SELECT SHDT AS SHIP_DATE, COUNT(DISTINCT SHMACPKR) AS TOTAL
|
|
FROM EWDAT.ICM699M
|
|
WHERE SHTYPE = 'M3-Res-out'
|
|
AND SHSOURCE = 'CloseShipmentCoChargeAPI'
|
|
AND SHDATA LIKE 'Success%'
|
|
AND SHDT >= VARCHAR_FORMAT(CURRENT DATE - 7 DAYS, 'YYYYMMDD')
|
|
GROUP BY SHDT
|
|
ORDER BY SHDT",
|
|
);
|
|
|
|
foreach ($rows as $row) {
|
|
$key = Carbon::createFromFormat('Ymd', (string) $row->SHIP_DATE)->format('d M');
|
|
if (array_key_exists($key, $data)) {
|
|
$data[$key] = (int) $row->TOTAL;
|
|
}
|
|
}
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->barChart($labels, array_values($data), 'Dispatches closed');
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function stockMovementsByType(): array
|
|
{
|
|
$labels = ['Transfer', 'Replenishment', 'Packing', 'Shipment', 'Adjustment'];
|
|
$data = [0, 0, 0, 0, 0];
|
|
|
|
try {
|
|
$rows = DB::connection('odbc')->select(
|
|
"SELECT TRIM(SHSOURCE) AS SOURCE_NAME, COUNT(*) AS TOTAL
|
|
FROM EWDAT.ICM699M
|
|
WHERE SHDT >= VARCHAR_FORMAT(CURRENT DATE - 30 DAYS, 'YYYYMMDD')
|
|
GROUP BY TRIM(SHSOURCE)
|
|
ORDER BY TOTAL DESC
|
|
FETCH FIRST 5 ROWS ONLY",
|
|
);
|
|
|
|
if (! empty($rows)) {
|
|
$labels = [];
|
|
$data = [];
|
|
foreach ($rows as $row) {
|
|
$labels[] = ucwords(strtolower(str_replace(['API', '_', '-'], [' API', ' ', ' '], trim($row->SOURCE_NAME))));
|
|
$data[] = (int) $row->TOTAL;
|
|
}
|
|
}
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->doughnutChart($labels, $data, 'Movement messages');
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function lowStockByWarehouse(): array
|
|
{
|
|
$labels = ['WH 1', 'WH 2', 'WH 3', 'WH 4', 'WH 5'];
|
|
$data = [0, 0, 0, 0, 0];
|
|
|
|
try {
|
|
$rows = DB::connection('odbc')->select(
|
|
'SELECT TRIM(AT9WHS) AS WAREHOUSE, COUNT(*) AS TOTAL
|
|
FROM EWDAT.ICM709M
|
|
WHERE AT9QTY <= AT9WRN
|
|
GROUP BY TRIM(AT9WHS)
|
|
ORDER BY TOTAL DESC
|
|
FETCH FIRST 6 ROWS ONLY',
|
|
);
|
|
|
|
if (! empty($rows)) {
|
|
$labels = [];
|
|
$data = [];
|
|
foreach ($rows as $row) {
|
|
$labels[] = 'WH ' . trim($row->WAREHOUSE);
|
|
$data[] = (int) $row->TOTAL;
|
|
}
|
|
}
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->barChart($labels, $data, 'Low-stock lines');
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function warehouseUtilisation(): array
|
|
{
|
|
$labels = ['Available', 'Allocated', 'Packed'];
|
|
$data = [0, 0, 0];
|
|
|
|
try {
|
|
$rows = DB::connection('odbc')->select(
|
|
'SELECT
|
|
SUM(A5RSVQTY) AS RESERVED_QTY,
|
|
SUM(A5PACKED) AS PACKED_QTY,
|
|
SUM(A5RSVQTY - A5PACKED) AS REMAINING_QTY
|
|
FROM EWDAT.ICM688M2
|
|
WHERE A5RSVQTY > 0',
|
|
);
|
|
|
|
if (! empty($rows)) {
|
|
$data = [
|
|
max(0, (int) $rows[0]->REMAINING_QTY),
|
|
max(0, (int) $rows[0]->RESERVED_QTY),
|
|
max(0, (int) $rows[0]->PACKED_QTY),
|
|
];
|
|
}
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->doughnutChart($labels, $data, 'Quantity status');
|
|
}
|
|
|
|
/**
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function systemHealth(): array
|
|
{
|
|
$labels = ['Success', 'Errors', 'Pending'];
|
|
$data = [0, 0, 0];
|
|
|
|
try {
|
|
$success = $this->safeCount('ICM699M', "SHTYPE = 'M3-Res-out' AND SHDATA LIKE 'Success%'");
|
|
$errors = $this->safeCount('ICM699M', "SHERROR = 'X'");
|
|
$pending = $this->safeCount('ICM705M1', "APSPARE2 IN ('', 'P')");
|
|
$data = [$success, $errors, $pending];
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
}
|
|
|
|
return $this->doughnutChart($labels, $data, 'Operational status');
|
|
}
|
|
|
|
/**
|
|
* @return array<int, string>
|
|
*/
|
|
private function lastDaysLabels(int $days): array
|
|
{
|
|
$labels = [];
|
|
for ($offset = $days - 1; $offset >= 0; $offset--) {
|
|
$labels[] = Carbon::today()->subDays($offset)->format('d M');
|
|
}
|
|
|
|
return $labels;
|
|
}
|
|
|
|
/**
|
|
* @param array<int, string> $labels
|
|
*
|
|
* @return array<string, int>
|
|
*/
|
|
private function emptySeries(array $labels): array
|
|
{
|
|
return array_fill_keys($labels, 0);
|
|
}
|
|
|
|
private function safeCount(string $table, ?string $where = null): int
|
|
{
|
|
try {
|
|
$sql = 'SELECT COUNT(*) AS TOTAL FROM EWDAT.' . $table;
|
|
if ($where !== null && trim($where) !== '') {
|
|
$sql .= ' WHERE ' . $where;
|
|
}
|
|
|
|
$rows = DB::connection('odbc')->select($sql);
|
|
|
|
return isset($rows[0]->TOTAL) ? (int) $rows[0]->TOTAL : 0;
|
|
} catch (\Throwable $exception) {
|
|
$this->logChartException($exception);
|
|
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param array<int, string> $labels
|
|
* @param array<int, int> $data
|
|
*
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function lineChart(array $labels, array $data, string $label): array
|
|
{
|
|
return [
|
|
'labels' => $labels,
|
|
'datasets' => [[
|
|
'label' => $label,
|
|
'data' => $data,
|
|
'tension' => 0.35,
|
|
'fill' => true,
|
|
]],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param array<int, string> $labels
|
|
* @param array<int, int> $data
|
|
*
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function barChart(array $labels, array $data, string $label): array
|
|
{
|
|
return [
|
|
'labels' => $labels,
|
|
'datasets' => [[
|
|
'label' => $label,
|
|
'data' => $data,
|
|
'borderRadius' => 8,
|
|
]],
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param array<int, string> $labels
|
|
* @param array<int, int> $data
|
|
*
|
|
* @return array<string, mixed>
|
|
*/
|
|
private function doughnutChart(array $labels, array $data, string $label): array
|
|
{
|
|
return [
|
|
'labels' => $labels,
|
|
'datasets' => [[
|
|
'label' => $label,
|
|
'data' => $data,
|
|
'borderWidth' => 2,
|
|
'hoverOffset' => 8,
|
|
]],
|
|
];
|
|
}
|
|
|
|
private function logChartException(\Throwable $exception): void
|
|
{
|
|
Log::debug('Dashboard chart data unavailable.', [
|
|
'message' => $exception->getMessage(),
|
|
]);
|
|
}
|
|
}
|