Merge pull request 'Added remark column for invoice data report' (#196) from ranjith-dev into master

Reviewed-on: #196

app/Console/Commands/Scheduler.php

@@ -39,6 +39,8 @@ class Scheduler extends Command
     public function handle()
     {
 
+        // $this->call('approval:trigger-mails');
+
         // --- Production Rules ---
         $productionRules = AlertMailRule::where('module', 'ProductionQuantities')
             ->where('rule_name', 'ProductionMail')
@@ -174,7 +176,7 @@ class Scheduler extends Command
                     }
                     break;
                 case 'Daily':
-                    if (now()->format('H:i') == '10:00') {
+                    if (now()->format('H:i') == '10:30') {
                         \Artisan::call('send:invoice-data-report', [
                             'schedule_type' => $rule->schedule_type,
                             'plant' => $rule->plant,
@@ -209,7 +211,7 @@ class Scheduler extends Command
                     }
                     break;
                 case 'Daily':
-                    if (now()->format('H:i') == '10:00') {
+                    if (now()->format('H:i') == '10:45') {
                         \Artisan::call('send:invoice-transit-report', [
                             'schedule_type' => $rule->schedule_type,
                             'plant' => $rule->plant,

app/Console/Commands/SendInvoiceDataReport.php

@@ -6,12 +6,8 @@ use App\Mail\InvoiceDataMail;
 use App\Models\AlertMailRule;
 use App\Models\InvoiceDataValidation;
 use App\Models\InvoiceOutValidation;
-use App\Models\Line;
 use App\Models\Plant;
-use App\Models\ProductionPlan;
-use App\Models\ProductionQuantity;
 use Illuminate\Console\Command;
-use Illuminate\Support\Facades\Mail;
 
 class SendInvoiceDataReport extends Command
 {
@@ -49,7 +45,7 @@ class SendInvoiceDataReport extends Command
         $plants = ($plantId == 0) ? Plant::all() : Plant::where('id', $plantId)->get();
 
         if ($plants->isEmpty()) {
-            $this->error("No valid plant(s) found.");
+            $this->error('No valid plant(s) found.');
             return;
         }
 
@@ -105,7 +101,7 @@ class SendInvoiceDataReport extends Command
                     ->where('distribution_channel_desc', $selectedDistribution)
                     ->whereBetween('document_date', [$startDate, $endDate])
                     ->orderBy('document_date', 'asc')
-                    ->select('customer_code', 'document_number', 'document_date', 'customer_trade_name', 'customer_location', 'location')
+                    ->select('customer_code', 'document_number', 'document_date', 'customer_trade_name', 'customer_location', 'location', 'remark')
                     ->get()
                     ->unique('document_number')
                     ->values();
@@ -114,7 +110,6 @@ class SendInvoiceDataReport extends Command
                     continue;
                 }
 
-                // Filter invoices directly — exclude ones with '-' in document_number
                 $invoices = $invoices->filter(function ($inv) {
                     return !empty($inv->document_number) && !str_contains($inv->document_number, '-');
                 });
@@ -134,7 +129,6 @@ class SendInvoiceDataReport extends Command
                     ->map(fn($n) => preg_replace('/\s+/', '', strtoupper((string) $n)))
                     ->toArray();
 
-                    //where('plant_id', $plant->id)
                 $wentOutInvoices = InvoiceOutValidation::whereIn('qr_code', $invoiceNumbers)
                     //->whereBetween('scanned_at', [$startDate, $endDate])
                     ->distinct('qr_code')
@@ -164,7 +158,6 @@ class SendInvoiceDataReport extends Command
                     return !in_array($doc, $wentOutInvoices, true);
                 });
 
-
                 if ($pendingInvoices->isEmpty()) {
                     continue;
                 }
@@ -182,7 +175,7 @@ class SendInvoiceDataReport extends Command
                     }
 
                     $tableData[] = [
-                        //'no' => $no++,
+                        // 'no' => $no++,
                         'plant' => $plant->name,
                         // 'distribution_type' => $selectedDistribution,
                         'customer_code' => $inv->customer_code,
@@ -194,6 +187,7 @@ class SendInvoiceDataReport extends Command
                         'no_of_days_pending' => abs((int)now()->diffInDays($documentDate)),
                         'status' => 'Pending',
                         'status_class' => $statusColor,
+                        'remark' => $inv->remark,
                     ];
                 }
             }
@@ -203,6 +197,7 @@ class SendInvoiceDataReport extends Command
                 ->values()
                 ->map(function ($item, $index) {
                     $item['no'] = $index + 1;
+
                     return $item;
                 })
                 ->toArray();
@@ -214,7 +209,7 @@ class SendInvoiceDataReport extends Command
 
             $this->info($contentVars['greeting'] ?? 'Invoice Data Report');
             $this->table(
-                ['No', 'Plant', 'Customer Code', 'Document Number', 'Document Date', 'Trade Name', 'Location', 'Pending Days', 'Status'],//'Distribution Type'
+                ['No', 'Plant', 'Customer Code', 'Document Number', 'Document Date', 'Trade Name', 'Location', 'Pending Days', 'Status', 'Remark'],// 'Distribution Type'
                 $tableData
             );
             $this->info($contentVars['wishes'] ?? '');
@@ -236,13 +231,13 @@ class SendInvoiceDataReport extends Command
                     ->toArray();
 
                 if (empty($toEmails)) {
-                    $this->warn("Skipping rule ID {$rule->id} — no valid To emails found.");
+                    $this->info("Skipping rule ID {$rule->id} — no valid To emails found.");
                     continue;
                 }
 
                 \Mail::to($toEmails)->cc($ccEmails)->send($mail);
 
-                $this->info("Mail sent for rule ID {$rule->id} → To: " . implode(', ', $toEmails) . ($ccEmails ? " | CC: " . implode(', ', $ccEmails) : ''));
+                $this->info("Mail sent for rule ID {$rule->id} → To: ".implode(', ', $toEmails).($ccEmails ? ' | CC: '.implode(', ', $ccEmails) : ''));
             }
         }
     }

app/Filament/Pages/InvoicePendingReason.php

@@ -0,0 +1,179 @@
+<?php
+
+namespace App\Filament\Pages;
+
+use App\Models\InvoiceDataValidation;
+use App\Models\InvoiceOutValidation;
+use App\Models\Plant;
+use Filament\Pages\Page;
+use Filament\Forms\Contracts\HasForms;
+use Filament\Forms\Concerns\InteractsWithForms;
+use Filament\Forms\Form;
+use Filament\Facades\Filament;
+use Filament\Forms\Components\Hidden;
+use Filament\Forms\Components\Select;
+use Filament\Forms\Components\TextInput;
+use Filament\Notifications\Notification;
+use Filament\Pages\Dashboard\Concerns\HasFiltersForm;
+use Illuminate\Support\Facades\Auth;
+
+class InvoicePendingReason extends Page
+{
+    use HasFiltersForm;
+    protected static ?string $navigationIcon = 'heroicon-o-document-text';
+
+    protected static string $view = 'filament.pages.invoice-pending-reason';
+
+    protected static ?string $navigationGroup = 'Manufacturing SD';
+
+    public function mount(): void
+    {
+       $this->filtersForm->fill([
+           'plant_id' => null,
+           'document_number' => null,
+           'remark' => null,
+       ]);
+    }
+
+    public function filtersForm(Form $form): Form
+    {
+        return $form
+            ->statePath('filters')
+            ->schema([
+
+                Select::make('plant_id')
+                    ->label('Plant')
+                    ->reactive()
+                    ->required()
+                    ->columnSpan(1)
+                    ->options(function (callable $get) {
+                        $userHas = Filament::auth()->user()->plant_id;
+                        return ($userHas && strlen($userHas) > 0) ? Plant::where('id', $userHas)->pluck('name', 'id')->toArray() : Plant::pluck('name', 'id')->toArray();
+                    })
+                    ->afterStateUpdated(function ($state, $set, callable $get,$livewire)  {
+                        $plantId = $get('plant_id');
+                        $set('document_number', null);
+                    })
+                    ->hint(fn ($get) => $get('pqPlantError') ? $get('pqPlantError') : null)
+                    ->hintColor('danger'),
+
+                Select::make('document_number')
+                    ->label('Document Number')
+                    ->required()
+                    ->reactive()
+                    ->columnSpan(1)
+                    ->options(function (callable $get) {
+                        $plantId = $get('plant_id');
+                        if (empty($plantId)) {
+                            return [];
+                        }
+
+                        $distributions = InvoiceDataValidation::whereNotNull('distribution_channel_desc')
+                            ->distinct()
+                            ->pluck('distribution_channel_desc')
+                            ->filter(fn ($v) => trim($v) !== '')
+                            ->values()
+                            ->toArray();
+
+                        $distributions[] = '';
+
+                        $pendingInvoices = collect();
+
+                        foreach ($distributions as $distribution) {
+
+                            $invoices = InvoiceDataValidation::where('plant_id', $plantId)
+                                ->where('distribution_channel_desc', $distribution)
+                                ->select('id', 'document_number')
+                                ->get()
+                                ->unique('document_number')
+                                ->filter(fn ($inv) =>
+                                    ! empty($inv->document_number) &&
+                                    ! str_contains($inv->document_number, '-')
+                                );
+
+                            if (trim($distribution) == '') {
+                                $invoices = $invoices->filter(fn ($inv) =>
+                                    str_starts_with($inv->document_number, '7')
+                                );
+                            }
+
+                            if ($invoices->isEmpty()) {
+                                continue;
+                            }
+
+                            $invoiceNumbers = $invoices->pluck('document_number')
+                                ->map(fn ($n) => preg_replace('/\s+/', '', strtoupper($n)))
+                                ->toArray();
+
+                            $wentOut = InvoiceOutValidation::whereIn('qr_code', $invoiceNumbers)
+                                ->distinct()
+                                ->pluck('qr_code')
+                                ->map(fn ($n) => preg_replace('/\s+/', '', strtoupper($n)))
+                                ->toArray();
+
+                            $pending = $invoices->filter(function ($inv) use ($wentOut) {
+                                $doc = preg_replace('/\s+/', '', strtoupper($inv->document_number));
+                                return ! in_array($doc, $wentOut, true);
+                            });
+
+                            $pendingInvoices = $pendingInvoices->merge($pending);
+                        }
+
+                        return $pendingInvoices
+                            ->unique('document_number')
+                            ->pluck('document_number', 'document_number')
+                            ->toArray();
+                    })
+                    ->afterStateUpdated(function ($state, callable $set, callable $get) {
+
+                    })
+                    ->extraAttributes(fn ($get) => [
+                        'class' => $get('pqBlockError') ? 'border-red-500' : '',
+                    ])
+                    ->hint(fn ($get) => $get('pqBlockError') ? $get('pqBlockError') : null)
+                    ->hintColor('danger'),
+                    TextInput::make('remark')
+                    ->label('Remark')
+                    ->reactive()
+                        ->extraAttributes([
+                            'wire:keydown.enter.prevent' => 'addRemark($event.target.value)',
+                        ])
+                    ->autofocus()
+                    ->required(),
+                ])
+        ->columns(3);
+    }
+
+    public function addRemark($value){
+        $plantId        = $this->filters['plant_id'] ?? null;
+        $documentNumber = $this->filters['document_number'] ?? null;
+        $remark         = $value;
+
+        if (! $plantId || ! $documentNumber || $remark == '') {
+            return;
+        }
+
+        InvoiceDataValidation::where('plant_id', $plantId)
+        ->where('document_number', $documentNumber)
+        ->update([
+            'remark'     => $remark,
+            'updated_at' => now(),
+        ]);
+
+        $this->filtersForm->fill([
+            'plant_id'        => $plantId,
+            'document_number' => $documentNumber,
+            'remark'          => null,
+        ]);
+
+        Notification::make()
+            ->title('Remark updated successfully')
+            ->success()
+            ->send();
+    }
+
+    public static function canAccess(): bool
+    {
+        return Auth::check() && Auth::user()->can('view invoice pending reason');
+    }
+}

app/Filament/Resources/ItemResource.php

@@ -197,6 +197,7 @@ class ItemResource extends Resource
                     ->searchable(),
                 Tables\Columns\TextColumn::make('category')
                     ->label('Category')
+                    ->default('-')
                     ->alignCenter()
                     ->sortable()
                     ->searchable(),
@@ -217,6 +218,7 @@ class ItemResource extends Resource
                     ->sortable(),
                 Tables\Columns\TextColumn::make('uom')
                     ->label('Unit of Measure')
+                    ->default('-')
                     ->alignCenter()
                     ->sortable(),
                 Tables\Columns\TextColumn::make('created_at')
@@ -237,6 +239,7 @@ class ItemResource extends Resource
                     ->sortable()
                     ->toggleable(isToggledHiddenByDefault: true),
             ])
+            ->searchPlaceholder('Search Item Code')
             ->filters([
                 Tables\Filters\TrashedFilter::make(),
                 Filter::make('advanced_filters')
@@ -256,7 +259,7 @@ class ItemResource extends Resource
                                 $set('operator_id', null);
                             }),
                         Select::make('code')
-                            ->label('Search by Item Code')
+                            ->label('Search by Code')
                             ->nullable()
                             // ->options(function (callable $get) {
                             //     $plantId = $get('Plant');

app/Filament/Resources/StickerMasterResource.php

@@ -30,6 +30,7 @@ use Filament\Tables\Table;
 use Illuminate\Database\Eloquent\Builder;
 use Illuminate\Database\Eloquent\SoftDeletingScope;
 use Illuminate\Support\Facades\Storage;
+use Illuminate\Validation\Rule;
 
 // use Illuminate\Validation\Rule;
 
@@ -93,21 +94,33 @@ class StickerMasterResource extends Resource
                 Forms\Components\Select::make('item_id')
                     ->label('Item Code')
                     ->options(function (callable $get) {
+                        $plantId = $get('plant_id');
                         if (! $get('plant_id')) {
                             return [];
                         }
 
-                        return \App\Models\Item::where('plant_id', $get('plant_id'))
+                        if (! $get('id')) {
-                            ->pluck('code', 'id')
+                            // whereHas
-                            ->toArray();
+                            return Item::where('plant_id', $plantId)->whereDoesntHave('stickerMasters')->pluck('code', 'id');
+                        } else {
+                            $itemId = StickerMaster::where('id', $get('id'))->first()?->item_id;
+
+                            return Item::where('plant_id', $plantId)
+                                ->where(function ($query) use ($itemId) {
+                                    $query->whereDoesntHave('stickerMasters')
+                                        ->orWhere('id', $itemId);
+                                })
+                                ->pluck('code', 'id');
+                        }
+                        // return Item::where('plant_id', $plantId)->pluck('code', 'id')->toArray();
+                    })
+                    ->rule(function (callable $get) {
+                        return Rule::unique('sticker_masters', 'item_id')
+                            ->where('plant_id', $get('plant_id'))
+                            ->ignore($get('id')); // Ignore current record during updates
                     })
-                    // ->rule(function (callable $get) {
-                    //     return Rule::unique('items', 'code')
-                    //         ->where('plant_id', $get('plant_id'))
-                    //         ->ignore($get('id')); // Ignore current record during updates
-                    // })
                     ->required()
-                    ->nullable()
+                    // ->nullable()
                     ->searchable()
                     ->reactive()
                     // ->disabled(fn (Get $get) => !empty($get('id')))
@@ -132,7 +145,7 @@ class StickerMasterResource extends Resource
                             return;
                         }
 
-                        $availableItems = \App\Models\Item::where('plant_id', $plantId)->exists();
+                        $availableItems = Item::where('plant_id', $plantId)->exists();
                         if (! $availableItems) {
                             $set('item_error', null);
 
@@ -147,7 +160,7 @@ class StickerMasterResource extends Resource
                         }
 
                         // Check if item exists for the selected plant
-                        $item = \App\Models\Item::where('plant_id', $plantId)
+                        $item = Item::where('plant_id', $plantId)
                             ->where('id', $itemId)
                             ->first();
 
@@ -161,7 +174,8 @@ class StickerMasterResource extends Resource
                             ->where('item_id', $itemId)
                             ->exists();
                         if (! $get('id')) {
-                            $set('item_error', $duplicateSticker ? 'Item Code already exists for the selected plant.' : null);
+                            // $set('item_error', $duplicateSticker ? 'Item Code already exists for the selected plant.' : null);
+                            $set('item_error', null);
                         }
                     })
                     ->extraAttributes(fn ($get) => [
@@ -177,7 +191,7 @@ class StickerMasterResource extends Resource
                         if ($get('id')) {
                             $itemId = StickerMaster::where('id', $get('id'))->first()?->item_id;
                             if ($itemId) {
-                                $item = \App\Models\Item::where('id', $itemId)->first()?->description;
+                                $item = Item::where('id', $itemId)->first()?->description;
                                 if ($item) {
                                     $set('item_description', $item);
                                 } else {
@@ -525,27 +539,35 @@ class StickerMasterResource extends Resource
                     ->sortable(),
                 Tables\Columns\TextColumn::make('part_validation1')
                     ->label('Part Validation 1')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('part_validation2')
                     ->label('Part Validation 2')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('part_validation3')
                     ->label('Part Validation 3')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('part_validation4')
                     ->label('Part Validation 4')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('part_validation5')
                     ->label('Part Validation 5')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('laser_part_validation1')
                     ->label('Laser Part Validation 1')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('laser_part_validation2')
                     ->label('Laser Part Validation 2')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('panel_box_code')
                     ->label('Panel Box Code')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('load_rate')
                     ->label('Load Rate')
@@ -553,6 +575,7 @@ class StickerMasterResource extends Resource
                     ->sortable(),
                 Tables\Columns\TextColumn::make('bundle_quantity')
                     ->label('Bundle Quantity')
+                    ->default('-')
                     ->alignCenter(),
                 Tables\Columns\TextColumn::make('material_type')
                     ->label('Material Type')
@@ -587,9 +610,7 @@ class StickerMasterResource extends Resource
                     ->sortable()
                     ->toggleable(isToggledHiddenByDefault: true),
             ])
-        // ->filters([
+            ->searchPlaceholder('Search Item Code')
-        //     Tables\Filters\TrashedFilter::make(),
-        // ])
             ->filters([
                 Tables\Filters\TrashedFilter::make(),
                 Filter::make('advanced_filters')
@@ -635,7 +656,7 @@ class StickerMasterResource extends Resource
                             ->searchable()
                             ->reactive(),
                         Select::make('material_type')
-                            ->label('Material Type')
+                            ->label('Select Material Type')
                             ->options([
                                 1 => 'Individual',
                                 2 => 'Bundle',
@@ -644,7 +665,7 @@ class StickerMasterResource extends Resource
                             ->reactive(),
 
                         TextInput::make('panel_box_code')
-                            ->label('Panel Box Code')
+                            ->label('Search by Panel Box Code')
                             ->placeholder(placeholder: 'Enter Panel Box Code'),
                         DateTimePicker::make(name: 'created_from')
                             ->label('Created From')

resources/views/filament/pages/invoice-pending-reason.blade.php

@@ -0,0 +1,6 @@
+<x-filament-panels::page>
+    <div class="space-y-4">
+        {{-- {{ $this->filtersForm($this->form) }} --}}
+        {{ $this->filtersForm($this->form) }}
+    </div>
+</x-filament-panels::page>

resources/views/mail/invoice_data_report.blade.php

@@ -88,6 +88,7 @@
                         <th>Location</th>
                         <th>Pending Days</th>
                         <th>Status</th>
+                        <th>Remark</th>
                     </tr>
                 </thead>
                 <tbody>
@@ -117,6 +118,7 @@
                             <td class="{{ $row['status_class'] ?? '' }}">
                                 {{ $row['status'] }}
                             </td>
+                            <td>{{ $row['remark'] ?? '-'}}</td>
                         </tr>
                     @endforeach
                 </tbody>