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

<?php

namespace Modules\M3\Http\Controllers;

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

class ExecSummaryLineController extends Controller
{

    private const FIRST_DAY_THIS_MONTH = 'first day of this month';

    private const LAST_DAY_THIS_MONTH = 'last day of this month';

    private const ONE_YEAR_AGO = '-1 year';

    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', []));
        $period = (string) $request->input('reportPeriod', '');
        $result = collect();
        $lastYear = collect();

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

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

    private static function pageResponse()
    {
        $company = '110';
        $plant = '144';
        $warehouse = '322';
        $warehouses = collect();
        try {
            $user = m3User() !== '' ? Str::upper(trim(m3User())) : '';
            if ($user !== '') {
                $profile = DB::connection(config('m3.connection', 'pgsql'))->table('icm428m')
                    ->select('R428US', 'R428CO', 'R428WH', 'R428PL')->where('R428US', $user)->first();
                $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.execSummaryLine', ['IC670M' => $warehouses, 'WHDDA' => $company . $plant . $warehouse]);
    }

    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 periodSql(string $period, Request $request, string $company, array $warehouses): array
    {
        return match ($period) {
            'year' => self::yearSql($request, $company, $warehouses),
            'quarter' => self::quarterSql($request, $company, $warehouses),
            'monthly' => self::monthlySql($request, $company, $warehouses),
            'custom' => self::customSql($request, $company, $warehouses),
            default => ['', ''],
        };
    }

    private static function yearSql(Request $request, string $company, array $warehouses): array
    {
        $range = self::range($request, 'first day of January', 'last day of December');
        return $range === null ? ['', ''] : [self::summarySql($range[0], $range[1], $company, $warehouses, 'substr(ayapr8N002,1,4)'), ''];
    }

    private static function quarterSql(Request $request, string $company, array $warehouses): array
    {
        $range = self::range($request, 'first day of January', 'last day of December');
        $group = "'Q' || EXTRACT(QUARTER FROM TO_DATE(CAST(ayapr8n002 AS TEXT),'YYYYMMDD')) ||'-' || substr(ayapr8N002,3,2)";
        return $range === null ? ['', ''] : [self::summarySql($range[0], $range[1], $company, $warehouses, $group), ''];
    }

    private static function monthlySql(Request $request, string $company, array $warehouses): array
    {
        $date = self::monthDate($request->input('REPORTDATE'));
        if ($date === null) {
            return ['', ''];
        }
        $end = clone $date;
        $from = (clone $date)->modify('-11 months')->modify(self::FIRST_DAY_THIS_MONTH)->format('Ymd');
        $to = $end->modify(self::LAST_DAY_THIS_MONTH)->format('Ymd');
        $lastFrom = (clone $date)->modify(self::ONE_YEAR_AGO)->modify('-11 months')->modify(self::FIRST_DAY_THIS_MONTH)->format('Ymd');
        $lastTo = (clone $date)->modify(self::ONE_YEAR_AGO)->modify(self::LAST_DAY_THIS_MONTH)->format('Ymd');
        $group = "substr(ayapr8N002,1,4) || '-' || substr(ayapr8N002,5,2)";

        return [self::summarySql($from, $to, $company, $warehouses, $group), self::summarySql($lastFrom, $lastTo, $company, $warehouses, $group)];
    }

    private static function customSql(Request $request, string $company, array $warehouses): array
    {
        $date = self::monthDate($request->input('REPORTDATE'));
        if ($date === null) {
            return ['', ''];
        }
        $from = (clone $date)->modify(self::FIRST_DAY_THIS_MONTH)->format('Ymd');
        $to = (clone $date)->modify(self::LAST_DAY_THIS_MONTH)->format('Ymd');
        $lastFrom = (clone $date)->modify(self::ONE_YEAR_AGO)->modify(self::FIRST_DAY_THIS_MONTH)->format('Ymd');
        $lastTo = (clone $date)->modify(self::ONE_YEAR_AGO)->modify(self::LAST_DAY_THIS_MONTH)->format('Ymd');
        $group = 'substr(ayapr8N002,7,2)';

        return [self::summarySql($from, $to, $company, $warehouses, $group), self::summarySql($lastFrom, $lastTo, $company, $warehouses, $group)];
    }

    private static function range(Request $request, string $fromModifier, string $toModifier): ?array
    {
        $from = self::monthDate($request->input('REPORTDATE'));
        $to = self::monthDate($request->input('TOREPORTDATE'));
        if ($from === null || $to === null) {
            return null;
        }

        return [(clone $from)->modify($fromModifier)->format('Ymd'), (clone $to)->modify($toModifier)->format('Ymd')];
    }

    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;
    }

    private static function summarySql(string $from, string $to, string $company, array $warehouses, string $group): string
    {
        $warehouseList = implode(',', $warehouses);
        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 $group 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 $group,a5whs order by 1";
    }
}