citrus-cms/app/Console/Commands/NotificationCheckNDTCalculation.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class NotificationCheckNDTCalculation extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'notifications:check-ndt-calculation';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Check NDT calculation warnings based on material grades, thicknesses, and joint types';

    /**
     * Stainless steel material groups
     */
    private $ssGroups = ['11', '8', '9', 'M11', 'M111', 'M9'];
    
    /**
     * Carbon steel material group
     */
    private $csGroup = ['1', 'M01'];

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $startTime = microtime(true);
        $commandName = $this->signature;
        
        // Log command start
        logNotificationStart($commandName);
        
        $this->info('Checking NDT calculation warnings...');
        
        $totalCount = 0;
        $totalSent = 0;
        $totalRecords = 0;
        $totalNew = 0;
        
        try {
            // Check 1: PMI test missing for SS or CS+SS
            $result1 = $this->checkPMIMissing($totalSent, $totalRecords, $totalNew);
            if ($result1 > 0) {
                $totalCount += 1;
            }
            
            // Check 2: FN test missing for SS with temperature > 350
            $result2 = $this->checkFNMissing($totalSent, $totalRecords, $totalNew);
            if ($result2 > 0) {
                $totalCount += 1;
            }
            
            $duration = microtime(true) - $startTime;
            logNotificationComplete($commandName, $totalSent, $totalRecords, $totalNew, $duration);
            
            $this->info("Total notifications sent: {$totalCount}");
            return 0;
        
        } catch (\Exception $e) {
            $duration = microtime(true) - $startTime;
            logNotificationError($commandName, $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'duration' => $duration,
            ]);
            
            $this->error("Error: " . $e->getMessage());
            return 1;
        }
    }
    
    /**
     * Check 1: PMI test missing for SS or CS+SS materials
     */
    private function checkPMIMissing(&$totalSent = null, &$totalRecords = null, &$totalNew = null)
    {
        $this->info('Check 1: PMI test missing for SS or CS+SS materials...');
        
        $ids = [];
        
        $weldLogs = DB::table('weld_logs')
            ->whereNotNull('welding_date')
            ->where('welding_date', '!=', '')
            ->where(function($query) {
                $query->whereIn('ru_material_group_1', array_merge($this->ssGroups, $this->csGroup))
                      ->orWhereIn('ru_material_group_2', array_merge($this->ssGroups, $this->csGroup));
            })
            ->where(function($query) {
                $query->whereNull('pmi_request_no')
                      ->orWhere('pmi_request_no', '');
            })
            ->select('id', 'ru_material_group_1', 'ru_material_group_2')
            ->get();
        
        foreach ($weldLogs as $weld) {
            $mat1 = $weld->ru_material_group_1 ?? '';
            $mat2 = $weld->ru_material_group_2 ?? '';
            
            $isSS = in_array($mat1, $this->ssGroups) || in_array($mat2, $this->ssGroups);
            $isCS_SS = (in_array($mat1, $this->csGroup) && in_array($mat2, $this->ssGroups)) ||
                       (in_array($mat1, $this->ssGroups) && in_array($mat2, $this->csGroup));
            
            if ($isSS || $isCS_SS) {
                $ids[] = $weld->id;
            }
        }
        
        return $this->sendWeldLogBatch(
            'notification_ndt_pmi_missing',
            'PMI Test Missing',
            '%d joint(s) have SS/CS+SS materials but PMI test requests are missing.',
            $ids,
            $totalSent,
            $totalRecords,
            $totalNew
        );
    }
    
    /**
     * Check 2: FN (Ferrite) test missing for SS materials with operating temperature > 350°C
     */
    private function checkFNMissing(&$totalSent = null, &$totalRecords = null, &$totalNew = null)
    {
        $this->info('Check 2: FN test missing for SS materials with temperature > 350...');
        
        $ids = DB::table('weld_logs')
            ->whereNotNull('welding_date')
            ->where('welding_date', '!=', '')
            ->where(function($query) {
                $query->whereIn('ru_material_group_1', $this->ssGroups)
                      ->orWhereIn('ru_material_group_2', $this->ssGroups);
            })
            ->where(function($query) {
                // Temperature control: operating_temperature_s > 350
                $query->whereRaw('CAST(operating_temperature_s AS DECIMAL(10,2)) > 350');
            })
            ->where(function($query) {
                $query->whereNull('ferrite_request_no')
                      ->orWhere('ferrite_request_no', '');
            })
            ->pluck('id')
            ->toArray();
        
        return $this->sendWeldLogBatch(
            'notification_ndt_fn_missing',
            'FN Test Missing',
            '%d joint(s) with SS materials and operating temperature > 350°C are missing FN (Ferrite) test requests.',
            $ids,
            $totalSent,
            $totalRecords,
            $totalNew
        );
    }

    private function sendWeldLogBatch(string $notificationCode, string $title, string $messageTemplate, array $ids, &$totalSent = null, &$totalRecords = null, &$totalNew = null): int
    {
        $ids = array_values(array_unique(array_filter($ids)));
        if (empty($ids)) {
            $this->info("No {$title} records found.");
            return 0;
        }
        
        // Skip if checked recently
        if (!shouldCheckNotification($notificationCode)) {
            $this->info("Skipping {$title}: Last notification sent less than 5 minutes ago.");
            return 0;
        }
        
        $lastCheck = getLastCheckTimestamp($notificationCode);
        
        // Filter IDs: only keep new ones if not first run
        if ($lastCheck !== null) {
            $alreadyNotifiedIds = getAlreadyNotifiedIds($notificationCode);
            if (!empty($alreadyNotifiedIds)) {
                $newIds = array_diff($ids, $alreadyNotifiedIds);
                if (empty($newIds)) {
                    $this->info("Skipping {$title}: No new records since last notification.");
                    return 0;
                }
                $ids = array_values(array_unique(array_merge($alreadyNotifiedIds, $ids)));
            }
        }
        
        $filterParams = [
            'table' => 'weld_logs',
            'conditions' => [
                'id' => $ids
            ]
        ];
        
        if ($lastCheck !== null && isNotificationDuplicate($notificationCode, $filterParams)) {
            $this->info("Skipping {$title}: Same issue already notified and no new records.");
            return 0;
        }
        
        $newCount = $lastCheck !== null ? count(array_diff($ids, getAlreadyNotifiedIds($notificationCode))) : count($ids);
        $totalRecords = count($ids);
        $sentCount = sendBatchNotificationWithFilter(
            $notificationCode,
            $title,
            $messageTemplate,
            $filterParams,
            $totalRecords
        );
        
        // Update totals for handle method
        if ($totalSent !== null) {
            $totalSent += $sentCount;
            $totalRecords += $totalRecords;
            $totalNew += $newCount;
        }
        
        $this->info("Sent batch notification for {$title} ({$totalRecords} joints, {$newCount} new).");
        return $sentCount > 0 ? 1 : 0;
    }
}