WMS/Modules/M3/Http/Controllers/ExecSummaryController.php

<?php

namespace Modules\M3\Http\Controllers;

use DB;
use Illuminate\Http\Request;
use Log;
use Str;

class ExecSummaryController extends Controller
{
    public static function index(Request $request)
    {
        if ($request->ajax()) {
            return self::ajaxResponse($request);
        }

        return self::pageResponse();
    }

    private static function ajaxResponse(Request $request)
    {
        [$company, $warehouses] = self::warehouseSelection((array) $request->input('WHDDA', []));
        $reportPeriod = (string) $request->input('reportPeriod', '');
        $result = collect();

        try {
            if ($company !== '' && $warehouses !== [] && $reportPeriod !== '') {
                $sql = self::summarySql($reportPeriod, $request, $company, $warehouses);
                $result = $sql === '' ? collect() : DB::connection(config('m3.connection', 'pgsql'))->select($sql);
            }
        } catch (\Throwable $throwable) {
            Log::error($throwable);
        }

        return response()->json([
            'success' => true,
            'reportPeriod' => $reportPeriod,
            'result' => $result,
            'checkSales' => $request->input('checkSales', ''),
            'checkWeight' => $request->input('checkWeight', ''),
            'checkLines' => $request->input('checkLines', ''),
        ]);
    }

    private static function pageResponse()
    {
        $warehouses = collect();
        $company = '110';
        $plant = '144';
        $warehouse = '322';

        try {
            $profile = self::currentUserProfile();
            if ($profile !== null) {
                $company = $profile->R428CO ?? $company;
                $plant = $profile->R428PL ?? $plant;
                $warehouse = $profile->R428WH ?? $warehouse;
            }
            $warehouses = DB::connection(config('m3.connection', 'pgsql'))->table('ic670m')->get();
        } catch (\Throwable $throwable) {
            Log::error($throwable);
        }

        return view('m3::liberty-theme.pages.execSummary', [
            'IC670M' => $warehouses,
            'WHDDA' => $company . $plant . $warehouse,
        ]);
    }

    private static function currentUserProfile(): ?object
    {
        $user = m3User() !== '' ? Str::upper(trim(m3User())) : '';
        if ($user === '') {
            return null;
        }

        return DB::connection(config('m3.connection', 'pgsql'))->table('icm428m')
            ->select('R428US', 'R428CO', 'R428WH', 'R428PL')
            ->where('R428US', $user)
            ->first();
    }

    private static function warehouseSelection(array $selection): array
    {
        $company = '';
        $warehouses = [];
        foreach ($selection as $value) {
            $company = substr((string) $value, 0, 3);
            $warehouses[] = substr((string) $value, 6, 3);
        }

        return [$company, $warehouses];
    }

    private static function summarySql(string $period, Request $request, string $company, array $warehouses): string
    {
        $range = self::dateRange($period, $request);
        if ($range === null) {
            return '';
        }

        [$from, $to] = $range;
        $warehouseList = implode(',', $warehouses);
        $groupExpression = match ($period) {
            'year' => 'substr(ayapr8N002,1,4)',
            'quarter' => "substr(ayapr8N002,3,2) ||'-' || 'Q' || EXTRACT(QUARTER FROM TO_DATE(CAST(ayapr8n002 AS TEXT),'YYYYMMDD'))",
            'monthly' => "substr(ayapr8N002,1,4) || '-' || substr(ayapr8N002,5,2)",
            'custom' => 'substr(ayapr8N002,7,2)',
            default => '',
        };
        if ($groupExpression === '') {
            return '';
        }

        return "with tempfile as (
            select a5rsvqty,a5packed,a5slunpr*a5packed as dollars,a5lnwght,a5whs,
            (select max(ayapr8N002) from icm695m1 where a5macpkr=aymacpkr fetch first 1 rows only) as ayapr8N002
            from icm688m2 where a5macpkr in (
                select distinct aymacpkr from icm695m1
                where AYSTATUS in ('Z','z') and ayapr8N002>=$from and ayapr8N002<=$to
            ) and a5co='$company' and a5whs in($warehouseList)
        )
        select $groupExpression as years,count(*) as lines,sum(a5rsvqty) as reserveqty,
            sum(a5packed) as packed,sum(a5lnwght*a5packed/a5rsvqty) as weight,
            sum(dollars) as dollars,a5whs as whs
        from tempfile group by $groupExpression,a5whs order by 1";
    }

    private static function dateRange(string $period, Request $request): ?array
    {
        $from = self::monthDate($request->input('REPORTDATE'));
        if ($from === null) {
            return null;
        }

        $range = null;
        if ($period === 'custom') {
            $range = [(clone $from)->modify('first day of this month')->format('Ymd'), (clone $from)->modify('last day of this month')->format('Ymd')];
        } else {
            $to = self::monthDate($request->input('TOREPORTDATE'));
            if ($to !== null) {
                $range = match ($period) {
                    'year', 'quarter' => [(clone $from)->modify('first day of January')->format('Ymd'), (clone $to)->modify('last day of December')->format('Ymd')],
                    'monthly' => [(clone $from)->modify('first day of this month')->format('Ymd'), (clone $to)->modify('last day of this month')->format('Ymd')],
                    default => null,
                };
            }
        }

        return $range;
    }

    private static function monthDate(mixed $value): ?\DateTime
    {
        if (! is_string($value) || trim($value) === '') {
            return null;
        }

        $date = \DateTime::createFromFormat('Y-m', trim($value));

        return $date instanceof \DateTime ? $date : null;
    }
}