WMS/Modules/M3/Console/WHSMetricsSaveXls.php

<?php

namespace Modules\M3\Console;

use Config;
use DB;
use Illuminate\Console\Command;
use Log;
use Modules\M3\Services\SpreadsheetExport;

class WHSMetricsSaveXls extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:WHSMetricsSaveXls {user?} {co?} {whs?} {pl?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Save WHS Metrics to Excel';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $this->configureRuntime();

        try {
            Log::channel('dailyCron')->info('Cron...Start Save WHS Metrics to Excel');
            $company = '110';
            $warehouse = '321';
            $plant = '143';
            $file = 'WhsPerf/' . $warehouse . '/WhsPerfMetrics_' . date('Y-m-d') . '-' . $warehouse . '.xlsx';

            Log::channel('dailyCron')->info('>>> File Name: ' . $file);
            $this->info('Generate Today WHS Performance Report...');

            [$summary, $details] = $this->reportCollections($company, $warehouse, $plant);
            $this->storeReport($summary, $details, $file);

            Log::channel('dailyCron')->info('Cron...End Save Today WHS Performance Report to Excel');
            $this->info('Cron...End Save Today WHS Performance Report to Excel');
        } catch (\Throwable $th) {
            Log::channel('dailyCron')->error($th);
        }

        return self::SUCCESS;
    }

    private function configureRuntime(): void
    {
        ini_set('max_execution_time', Config::get('arrays.PHPTimeOutCL'));
        ini_set('max_input_time', Config::get('arrays.PHPTimeOutCL'));
        ini_set('default_socket_timeout', Config::get('arrays.PHPTimeOutCL'));
        ini_set('memory_limit', '1024M');
    }

    private function reportCollections(string $company, string $warehouse, string $plant): array
    {
        $summary = collect($this->summaryRows($company, $warehouse, $plant))->map(fn ($item) => $this->trimRow($item));
        $summary->prepend([
            'A4SHIPTYPE' => 'Method', 'STATUS' => 'Status', 'SOURCE' => 'Source', 'PICKERS' => 'Pickers',
            'WEIGHT' => 'Weight', 'LINES' => 'Lines', 'DOLLARS' => 'Dollars',
        ]);

        $details = collect($this->detailRows($company, $warehouse, $plant))->map(fn ($item) => $this->trimRow($item));
        $details->prepend([
            'A3CO' => 'Co', 'A3WHS' => 'Whs', 'A3PLT' => 'Plt', 'A3MACPKR' => 'Delivery No', 'A4PICKCRTS' => 'Date Time Arrived in Sentinel',
            'SOURCE' => 'Source', 'CARRIERASSIGNED' => 'Date carrier Assigned', 'A4ONO' => 'Order No', 'A4SHSHNM' => 'Customer name',
            'A4SCAC' => 'Carrier', 'A42SSVCNAM' => 'Service', 'A4PICKSTS' => 'Picker Status', 'STATUS' => 'Open or Closed',
            'A4SHIPTYPE' => 'Method', 'A4TPCKWGHT' => 'Weight', 'A4NOPICLNS' => 'Lines', 'DOLLARS' => 'Dollars',
        ]);

        return [$summary, $details];
    }

    private function trimRow(object $item): object
    {
        foreach ($item as $key => $value) {
            if (is_string($value)) {
                $item->{$key} = trim($value);
            }
        }

        return $item;
    }

    private function summaryRows(string $company, string $warehouse, string $plant): array
    {
        return DB::connection(config('m3.connection', 'pgsql'))->select(<<<'SQL'
            WITH TEMPFILE AS (
                SELECT A.*,A4TPCKWGHT,A4NOPICLNS,A4PICKSTS,
                CASE WHEN UPPER(A4PICKSTS)<>'A' THEN 'Open' ELSE 'Closed' END AS STATUS,
                CASE WHEN A4TPCKWGHT>16000 THEN 'FTL' ELSE A4SHIPTYPE END AS A4SHIPTYPE,
                (SELECT SUM(A5RSVQTY*A5SLUNPR) FROM ICM688M2 WHERE A4MACPKR=A5MACPKR) AS DOLLARS
                FROM ICM700M A LEFT JOIN ICM688M1 B ON A4MACPKR=A3MACPKR
                WHERE A3MSMTDATE=(SELECT MAX(A3MSMTDATE) FROM ICM700M) AND A3CO=? AND A3WHS=? AND A3PLT=?
            )
            SELECT A4SHIPTYPE,STATUS,SOURCE,COUNT(*) AS PICKERS,SUM(A4TPCKWGHT) AS WEIGHT,SUM(A4NOPICLNS) AS LINES,SUM(DOLLARS) AS DOLLARS
            FROM TEMPFILE GROUP BY A4SHIPTYPE,STATUS,SOURCE ORDER BY 1,3,2
            SQL, [$company, $warehouse, $plant]);
    }

    private function detailRows(string $company, string $warehouse, string $plant): array
    {
        return DB::connection(config('m3.connection', 'pgsql'))->select(<<<'SQL'
            WITH TEMPFILE AS (
                SELECT A3CO,A3WHS,A3PLT,A3MACPKR,A4PICKCRTS,SOURCE,
                (SELECT TO_TIMESTAMP(CAST(SHDT AS TEXT) || ' ' || CAST(SHTIME AS TEXT), 'YYYY-MM-DD HH24:MI:SS') FROM ICM699M WHERE SHMACPKR=A4MACPKR AND SHTYPE='Lisnr-Hold' ORDER BY SHDT DESC,SHTIME DESC FETCH FIRST 1 ROWS ONLY) AS CARRIERASSIGNED,
                A4ONO,A4SHSHNM,A4SCAC,A42SSVCNAM,A4PICKSTS,
                CASE WHEN UPPER(A4PICKSTS)<>'A' THEN 'Open' ELSE 'Closed' END AS STATUS,
                CASE WHEN A4TPCKWGHT>16000 THEN 'FTL' ELSE A4SHIPTYPE END AS A4SHIPTYPE,
                A4TPCKWGHT,A4NOPICLNS,(SELECT SUM(A5RSVQTY*A5SLUNPR) FROM ICM688M2 WHERE A4MACPKR=A5MACPKR) AS DOLLARS
                FROM ICM700M A LEFT JOIN ICM688M1 B ON A4MACPKR=A3MACPKR
                WHERE A3MSMTDATE=(SELECT MAX(A3MSMTDATE) FROM ICM700M) AND A3CO=? AND A3WHS=? AND A3PLT=?
            ) SELECT * FROM TEMPFILE
            SQL, [$company, $warehouse, $plant]);
    }

    private function storeReport($summary, $details, string $file): void
    {
        if ($summary->isEmpty() || $details->isEmpty()) {
            Log::channel('dailyCron')->info('Today WHS Performance Report, no records found: Nothing to save...');

            return;
        }

        $this->info('Saving Today WHS Performance Report to Disk...');
        Log::channel('dailyCron')->info('Saving Today WHS Performance Report to Disk...');
        app(SpreadsheetExport::class)->storeSheets(['Summary' => $summary, 'Details' => $details], $file, 'publicLabels');
    }
}