264 lines
9.2 KiB
PHP
264 lines
9.2 KiB
PHP
<?php
|
|
|
|
namespace Modules\IntegrationHub\Services;
|
|
|
|
use Carbon\Carbon;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
class ODataExportService
|
|
{
|
|
public function availableDatasets(): array
|
|
{
|
|
return array_values(array_filter(
|
|
config('integrationhub.datasets', []),
|
|
fn ($d) => $d !== 'executive_cockpit'
|
|
));
|
|
}
|
|
|
|
public function dataset(int $businessId, string $dataset, array $filters = []): array
|
|
{
|
|
$rows = match ($dataset) {
|
|
'sales_summary' => $this->salesSummary($businessId, $filters),
|
|
'inventory_snapshot' => $this->inventorySnapshot($businessId, $filters),
|
|
'production_kpis' => $this->productionKpis($businessId, $filters),
|
|
'maintenance_kpis' => $this->maintenanceKpis($businessId, $filters),
|
|
'qms_kpis' => $this->qmsKpis($businessId, $filters),
|
|
default => throw new \InvalidArgumentException("Unknown dataset: {$dataset}"),
|
|
};
|
|
|
|
$context = url("/integrationhub/api/odata/{$dataset}");
|
|
|
|
return [
|
|
'@odata.context' => $context,
|
|
'@odata.count' => count($rows),
|
|
'value' => $rows,
|
|
];
|
|
}
|
|
|
|
protected function salesSummary(int $businessId, array $filters): array
|
|
{
|
|
if (! Schema::hasTable('transactions')) {
|
|
return [];
|
|
}
|
|
|
|
[$start, $end] = $this->dateRange($filters);
|
|
|
|
$query = DB::table('transactions')
|
|
->select(
|
|
DB::raw('DATE(transaction_date) as sale_date'),
|
|
DB::raw('COUNT(*) as transaction_count'),
|
|
DB::raw('SUM(final_total) as total_sales'),
|
|
DB::raw('SUM(tax_amount) as total_tax')
|
|
)
|
|
->where('business_id', $businessId)
|
|
->where('type', 'sell')
|
|
->where('status', 'final')
|
|
->whereBetween('transaction_date', [$start, $end])
|
|
->groupBy(DB::raw('DATE(transaction_date)'))
|
|
->orderBy('sale_date');
|
|
|
|
if (! empty($filters['location_id'])) {
|
|
$query->where('location_id', (int) $filters['location_id']);
|
|
}
|
|
|
|
return $query->get()->map(fn ($row) => [
|
|
'sale_date' => $row->sale_date,
|
|
'transaction_count' => (int) $row->transaction_count,
|
|
'total_sales' => (float) $row->total_sales,
|
|
'total_tax' => (float) $row->total_tax,
|
|
])->all();
|
|
}
|
|
|
|
protected function inventorySnapshot(int $businessId, array $filters): array
|
|
{
|
|
if (! Schema::hasTable('variation_location_details') || ! Schema::hasTable('products')) {
|
|
return [];
|
|
}
|
|
|
|
$query = DB::table('variation_location_details as vld')
|
|
->join('product_variations as pv', 'pv.id', '=', 'vld.product_variation_id')
|
|
->join('products as p', 'p.id', '=', 'pv.product_id')
|
|
->leftJoin('variations as v', 'v.id', '=', 'vld.variation_id')
|
|
->select(
|
|
'p.id as product_id',
|
|
'p.name as product_name',
|
|
'p.sku',
|
|
'vld.location_id',
|
|
'vld.qty_available',
|
|
'p.alert_quantity'
|
|
)
|
|
->where('p.business_id', $businessId)
|
|
->where('p.is_inactive', 0);
|
|
|
|
if (! empty($filters['location_id'])) {
|
|
$query->where('vld.location_id', (int) $filters['location_id']);
|
|
}
|
|
|
|
if (! empty($filters['low_stock_only'])) {
|
|
$query->whereColumn('vld.qty_available', '<=', 'p.alert_quantity');
|
|
}
|
|
|
|
return $query->limit($this->pageLimit($filters))->get()->map(fn ($row) => [
|
|
'product_id' => (int) $row->product_id,
|
|
'product_name' => $row->product_name,
|
|
'sku' => $row->sku,
|
|
'location_id' => (int) $row->location_id,
|
|
'qty_available' => (float) $row->qty_available,
|
|
'alert_quantity' => (float) $row->alert_quantity,
|
|
'is_low_stock' => (float) $row->qty_available <= (float) $row->alert_quantity,
|
|
])->all();
|
|
}
|
|
|
|
protected function productionKpis(int $businessId, array $filters): array
|
|
{
|
|
if (! Schema::hasTable('mfg_recipes') && ! Schema::hasTable('transactions')) {
|
|
return [];
|
|
}
|
|
|
|
$kpis = [
|
|
'period_start' => $this->dateRange($filters)[0]->toDateString(),
|
|
'period_end' => $this->dateRange($filters)[1]->toDateString(),
|
|
'production_orders' => 0,
|
|
'completed_orders' => 0,
|
|
'total_output_qty' => 0.0,
|
|
'active_recipes' => 0,
|
|
];
|
|
|
|
if (Schema::hasTable('mfg_recipes')) {
|
|
$kpis['active_recipes'] = (int) DB::table('mfg_recipes')
|
|
->where('business_id', $businessId)
|
|
->count();
|
|
}
|
|
|
|
if (Schema::hasTable('transactions')) {
|
|
[$start, $end] = $this->dateRange($filters);
|
|
|
|
$productionQuery = DB::table('transactions')
|
|
->where('business_id', $businessId)
|
|
->where('type', 'production_purchase')
|
|
->whereBetween('transaction_date', [$start, $end]);
|
|
|
|
$kpis['production_orders'] = (int) (clone $productionQuery)->count();
|
|
$kpis['completed_orders'] = (int) (clone $productionQuery)->where('status', 'received')->count();
|
|
$kpis['total_output_qty'] = (float) (clone $productionQuery)->sum('final_total');
|
|
}
|
|
|
|
return [$kpis];
|
|
}
|
|
|
|
protected function maintenanceKpis(int $businessId, array $filters): array
|
|
{
|
|
if (! Schema::hasTable('mt_work_orders')) {
|
|
return [[
|
|
'module_available' => false,
|
|
'open_work_orders' => 0,
|
|
'overdue_work_orders' => 0,
|
|
'completed_this_period' => 0,
|
|
]];
|
|
}
|
|
|
|
[$start, $end] = $this->dateRange($filters);
|
|
|
|
$open = DB::table('mt_work_orders')
|
|
->where('business_id', $businessId)
|
|
->whereIn('status', ['open', 'in_progress', 'pending'])
|
|
->count();
|
|
|
|
$overdue = DB::table('mt_work_orders')
|
|
->where('business_id', $businessId)
|
|
->whereIn('status', ['open', 'in_progress', 'pending'])
|
|
->whereNotNull('due_date')
|
|
->where('due_date', '<', now())
|
|
->count();
|
|
|
|
$completed = DB::table('mt_work_orders')
|
|
->where('business_id', $businessId)
|
|
->where('status', 'completed')
|
|
->whereBetween('updated_at', [$start, $end])
|
|
->count();
|
|
|
|
return [[
|
|
'module_available' => true,
|
|
'open_work_orders' => (int) $open,
|
|
'overdue_work_orders' => (int) $overdue,
|
|
'completed_this_period' => (int) $completed,
|
|
'period_start' => $start->toDateString(),
|
|
'period_end' => $end->toDateString(),
|
|
]];
|
|
}
|
|
|
|
protected function qmsKpis(int $businessId, array $filters): array
|
|
{
|
|
if (! Schema::hasTable('qm_ncr_reports')) {
|
|
return [[
|
|
'module_available' => false,
|
|
'open_ncrs' => 0,
|
|
'open_capas' => 0,
|
|
'overdue_calibrations' => 0,
|
|
]];
|
|
}
|
|
|
|
[$start, $end] = $this->dateRange($filters);
|
|
|
|
$openNcrs = DB::table('qm_ncr_reports')
|
|
->where('business_id', $businessId)
|
|
->whereNotIn('status', ['closed', 'resolved'])
|
|
->count();
|
|
|
|
$openCapas = Schema::hasTable('qm_capa_actions')
|
|
? DB::table('qm_capa_actions')
|
|
->where('business_id', $businessId)
|
|
->whereNotIn('status', ['closed', 'completed'])
|
|
->count()
|
|
: 0;
|
|
|
|
$overdueCalibrations = Schema::hasTable('qm_calibration_assets')
|
|
? DB::table('qm_calibration_assets')
|
|
->where('business_id', $businessId)
|
|
->whereNotNull('next_due_at')
|
|
->where('next_due_at', '<', now())
|
|
->count()
|
|
: 0;
|
|
|
|
$newNcrs = DB::table('qm_ncr_reports')
|
|
->where('business_id', $businessId)
|
|
->whereBetween('created_at', [$start, $end])
|
|
->count();
|
|
|
|
return [[
|
|
'module_available' => true,
|
|
'open_ncrs' => (int) $openNcrs,
|
|
'open_capas' => (int) $openCapas,
|
|
'overdue_calibrations' => (int) $overdueCalibrations,
|
|
'new_ncrs_this_period' => (int) $newNcrs,
|
|
'period_start' => $start->toDateString(),
|
|
'period_end' => $end->toDateString(),
|
|
]];
|
|
}
|
|
|
|
/**
|
|
* @return array{0: Carbon, 1: Carbon}
|
|
*/
|
|
protected function dateRange(array $filters): array
|
|
{
|
|
$start = ! empty($filters['start_date'])
|
|
? Carbon::parse($filters['start_date'])->startOfDay()
|
|
: now()->subDays(30)->startOfDay();
|
|
|
|
$end = ! empty($filters['end_date'])
|
|
? Carbon::parse($filters['end_date'])->endOfDay()
|
|
: now()->endOfDay();
|
|
|
|
return [$start, $end];
|
|
}
|
|
|
|
protected function pageLimit(array $filters): int
|
|
{
|
|
$limit = (int) ($filters['$top'] ?? config('integrationhub.odata.default_page_size', 100));
|
|
$max = (int) config('integrationhub.odata.max_page_size', 500);
|
|
|
|
return min(max($limit, 1), $max);
|
|
}
|
|
}
|