fix account statement mapping issue

CRM invoice decimal problem fix + fetch invoice fail fix + debug log update

See merge request CIEFWorldwideSdnBhd/shipping-portal!170

app/Classes/General/Eloquent/Filters/PaymentMethodNotIn.php

@@ -0,0 +1,20 @@
+<?php
+
+namespace App\Classes\General\Eloquent\Filters;
+
+use Illuminate\Database\Eloquent\Builder;
+
+class PaymentMethodNotIn implements Filter
+{
+
+    /**
+     * @param Builder $builder
+     * @param $value
+     * @return Builder|mixed
+     */
+    public static function apply(Builder $builder, $value)
+    {
+        return $builder->whereNotIn('payment_method', $value);
+    }
+
+}

app/Classes/Modules/Exports/Services/ExportsArrivedParcel.php

@@ -15,9 +15,17 @@ use App\Classes\ValueObjects\Constants\OrderRoleTypes;
 
 class ExportsArrivedParcel implements WithHeadings, WithHeadingRow, WithMapping, ShouldAutoSize, FromQuery
 {
-
     use Exportable;
 
+    private $start_date;
+    private $end_date;
+
+    public function __construct($start_date = null, $end_date = null)
+    {
+        $this->start_date = $start_date;
+        $this->end_date = $end_date;
+    }
+
     public function headings(): array
     {
         return [
@@ -26,38 +34,49 @@ class ExportsArrivedParcel implements WithHeadings, WithHeadingRow, WithMapping,
             'Warehouse',
             'Cbm',
             'Description',
-            'quantity',
+            'Quantity',
             'Received Date',
+            'Recorded On',
+            'Days to Record',
             'Remarks'
         ];
     }
 
-    /**
-     * @return \Illuminate\Support\Collection|mixed
-     */
     public function query()
     {
-        return PackingList::where('type', '=', 1)->where('status', '=', 2)->where('owner_type', Order::class);
+        $query = PackingList::with([
+            'owner.companyModule.inviters',
+            'owner.orderRoles',
+            'packages',
+            'transports'
+        ])
+            ->where('type', '=', 1)
+            ->where('status', '=', 2)
+            ->where('owner_type', Order::class);
+
+        if (!is_null($this->start_date) && !is_null($this->end_date)) {
+            $query->whereHas('transports', function ($q) {
+                $q->whereBetween('drop_date', [
+                    Carbon::parse($this->start_date)->startOfDay(),
+                    Carbon::parse($this->end_date)->endOfDay()
+                ]);
+            });
+        }
+
+        return $query;
     }
 
-    /**
-     * @param $packingList
-     * @return array
-     */
     public function map($packingList): array
     {
         $order = $packingList->owner;
-
-        $connection = $order->companyModule()->first()->inviters()->withPivot('invitee_reference')->first();
-        $marking = $connection ? $connection->pivot->invitee_reference:'';
-
-        $warehouse = $order->orderRoles()->where('role_id', '=', OrderRoleTypes::ORIGIN_WAREHOUSE)->first()->appointee;
-
-        $cbm = $packingList->packages->sum(function($package){
-           return (($package->width / 100) * ($package->height / 100) * ($package->length / 100)) * $package->quantity;
+        $marking = $order->companyModule->getMarking();
+        $warehouse = $order->orderRoles->firstWhere('role_id', '=', OrderRoleTypes::ORIGIN_WAREHOUSE)->appointee;
+        $cbm = $packingList->packages->sum(function ($package) {
+            return (($package->width / 100) * ($package->height / 100) * ($package->length / 100)) * $package->quantity;
         });
-
-        $arrival_date = $packingList->transports()->first()->drop_date->format('d-m-Y');
+        $arrival_date = $packingList->transports->first()->drop_date->format('d-m-Y');
+        $recorded_on = $packingList->created_at->format('d-m-Y');
+        $days_to_record = Carbon::parse($arrival_date)->diffInDays(Carbon::parse($recorded_on));
 
         return [
             $packingList->owner->reference,
@@ -67,7 +86,9 @@ class ExportsArrivedParcel implements WithHeadings, WithHeadingRow, WithMapping,
             $packingList->packages->implode('description', ', '),
             $packingList->packages->sum('quantity'),
             $arrival_date,
+            $recorded_on,
+            $days_to_record,
             ''
         ];
     }
-}
+}

app/Classes/Modules/PackingLists/Processors/FetchContainersFromYdPortalProcessor.php

@@ -94,7 +94,8 @@ class FetchContainersFromYdPortalProcessor
      */
     public function execute()
     {
-        $packingLists = PackingList::where('type', PackingListType::SHIPPING_PACKING_LIST)->doesntHave('containers')->get();
+        $maxFetchTime = Carbon::now()->subMonths(4);
+        $packingLists = PackingList::where('type', PackingListType::SHIPPING_PACKING_LIST)->whereDate('created_at', '>=', $maxFetchTime)->doesntHave('containers')->get();
 
         foreach ($packingLists as $packingList) {
             $time_start = microtime(true);

app/Console/Commands/FixBillplzFailedCallbackPayment.php

@@ -75,14 +75,14 @@ class FixBillplzFailedCallbackPayment extends Command
      */
     public function handle()
     {
-        ini_set('memory_limit', '-1'); 
+        ini_set('memory_limit', '-1');
 
-        $this->outputArray = []; 
+        $this->outputArray = [];
         $start   = new Carbon();
 
         $approvalStatusArray = ApprovalStatus::APPROVAL_STATUS_ID;
 
-        $transactions = Transaction::where('type', TransactionType::PAYMENT)->where('payment_method', PaymentMethodType::PAYMENT_GATEWAY)->whereNotIn('status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED])->get();
+        $transactions = Transaction::whereIn('type', [TransactionType::PAYMENT, TransactionType::TOP_UP])->where('payment_method', PaymentMethodType::PAYMENT_GATEWAY)->whereNotIn('status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED])->get();
         $totalTransactions = count($transactions);
 
         if ($totalTransactions) {
@@ -93,7 +93,7 @@ class FixBillplzFailedCallbackPayment extends Command
         $totalAmount = 0;
         foreach ($transactions as $transaction){
             $response = Http::withBasicAuth(config('billplz.api_key').':', '')->get(config('billplz.base_url').'/api/v3/bills/'.$transaction->payment_reference);
-    
+
             if($response->successful()){
                 $data = $response->json();
                 if($data['paid']){
@@ -122,36 +122,36 @@ class FixBillplzFailedCallbackPayment extends Command
                         // $billPlz = $this->getBillplzBill->execute($billplzXSignatureObject->getBillPlzId());
 
                         // if(!$billPlz) throw new ResourceNotFoundException('Billplz bill not found.');
-                
+
                         // $transaction = $this->fetchesTransaction->execute(['payment_reference' => $billplzXSignatureObject->getBillPlzId()]);
 
                         $invoice = $transaction->owner;
                         $packingList = $invoice->owner;
                         $order = $packingList->owner;
-                
+
                         $status = ApprovalStatus::PENDING_VERIFICATION;
-                
+
                         if($data['state'] === 'paid') {
                             $status = ApprovalStatus::APPROVED;
                         }
-                
+
                         // if($data->state === 'due') {
                         //     $status = $billplzXSignatureObject->getStatus() === 'failed' ? ApprovalStatus::REJECTED : ApprovalStatus::PENDING_VERIFICATION;
                         // }
-                
+
                         // $token = Auth::fromUser(User::find(1));
                         // $request->headers->set('Authorization', 'Bearer '.$token);
-                
+
                         $this->updatesTransactionStatus->execute($transaction, $status);
-                
+
                         $totalPaidAmount = $invoice->transactions->where('type', TransactionType::PAYMENT)->whereIn('status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED])->sum('amount');
-                
+
                         if(($invoice->amount - $totalPaidAmount) < 0.01) {
                             $this->updatesTransactionStatus->execute($invoice, ApprovalStatus::COMPLETED);
-                
+
                             $packingList->status = ApprovalStatus::APPROVED;
                             $packingList->save();
-                
+
                             if(app()->environment('production')){
                                 $this->updateDoFromVTPortalProcessor->execute($packingList);
                                 $this->updateDoFromYDPortalProcessor->execute($packingList);
@@ -170,7 +170,7 @@ class FixBillplzFailedCallbackPayment extends Command
                             $this->info($counter . ' of ' . $totalTransactions . '. Order: '. $order->reference . ' - Date: '.$transaction->owner->created_at->format('d-m-Y').' - Amount: '. $transaction->amount . '. Status: ' . $approvalStatusArray[$transaction->status] . '. Invoice Status: ' . $invoiceStatus);
                         }
                     }
-                    
+
                 } else {
                     // $totalAmount += $transaction->amount;
                     // $this->appendToOutput($counter . ' of ' . $totalTransactions . '. Order: '. $transaction->owner->owner->owner->reference . ' - Date: '.$transaction->owner->created_at->format('d-m-Y').' - Amount: '. $transaction->amount);
@@ -180,7 +180,7 @@ class FixBillplzFailedCallbackPayment extends Command
             }
             $counter++;
         }
-    
+
         $end = new Carbon();
         $elapsedTime = $start->diff($end)->format('%H:%I:%S');
 

app/Http/Controllers/Exports/ExportArrivedParcelController.php

@@ -23,7 +23,11 @@ class ExportArrivedParcelController
     }
 
     public function export(Request $request) {
-        $exportsPendingArrangementDeliveryList = new ExportsArrivedParcel($request);
+        $startDate = $request->query('start_date');
+        $endDate = $request->query('end_date');
+
+        $exportsPendingArrangementDeliveryList = new ExportsArrivedParcel($startDate, $endDate);
+
         $response = $exportsPendingArrangementDeliveryList->download('arrived-parcel.xls', Excel::XLS, ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']);
         ob_end_clean();
         return $response;

app/Http/Resources/MappableTransactionResource.php

@@ -26,7 +26,7 @@ class MappableTransactionResource extends JsonResource
             $reference = $this->owner->owner->inviters()->withPivot('invitee_reference')->first()->pivot->invitee_reference;
         }
 
-        if($this->type === TransactionType::PAYMENT && $this->owner !== Wallet::class){
+        if($this->type === TransactionType::PAYMENT && $this->owner_type !== Wallet::class){
             $reference = $this->owner->owner->owner->reference;
         }
 
@@ -34,7 +34,7 @@ class MappableTransactionResource extends JsonResource
             'status' => 'success',
             'system' => 'SHIPPING_PORTAL',
             'type' => $this->type,
-            'owner_type' => $this->owner_type,
+            'owner_type' => Transaction::class,
             'owner_id'=> $this->id,
             'owner_reference'=> $reference,
         ];

app/Http/Resources/WalletResource.php

@@ -2,6 +2,7 @@
 
 namespace App\Http\Resources;
 
+use App\Classes\ValueObjects\Constants\ApprovalStatus;
 use App\Classes\ValueObjects\Constants\TransactionType;
 use Illuminate\Http\Resources\Json\JsonResource;
 
@@ -21,8 +22,8 @@ class WalletResource extends JsonResource
             'currency_id' => $this->currency_id,
             'amount' => (double) $this->amount,
             'company_id' => (int) $this->owner->id,
-            'transactions' => $this->whenLoaded('transactions', WalletTransactionResource::collection($this->transactions()->whereIn('status', [2, 3])->orderBy('id', 'DESC')->get()), []),
-            'top_up_records' => $this->whenLoaded('transactions', WalletTransactionResource::collection($this->transactions()->whereNotIn('status', [0])->where('type', TransactionType::TOP_UP)->orderBy('id', 'DESC')->get()), []),
+            'transactions' => $this->whenLoaded('transactions', WalletTransactionResource::collection($this->transactions()->whereIn('status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED])->orderBy('id', 'DESC')->get()), []),
+            'top_up_records' => $this->whenLoaded('transactions', WalletTransactionResource::collection($this->transactions()->whereIn('type', [TransactionType::TOP_UP])->orderBy('id', 'DESC')->get()), []),
             'company_module_marking' => $this->owner->connections->first()->invitee_reference,
         ];
     }

app/Models/CompanyModule.php

@@ -29,7 +29,7 @@ use App\Classes\General\Interfaces\Remarkable;
  * Class CompanyModule
  * @package App\Models
  *
- * @property \App\Models\Company company_id 
+ * @property \App\Models\Company company_id
  * @property integer type
  * @property integer status
  */
@@ -111,6 +111,12 @@ class CompanyModule extends AbstractModel implements Addressable, Documentable,
         return $this->hasMany(CompanyConnection::class, 'invitee_id');
     }
 
+    public function getMarking()
+    {
+        $connection = $this->connections()->first();
+        return $connection ? $connection->invitee_reference : '';
+    }
+
     /**
      * @return HasMany
      */
@@ -193,7 +199,7 @@ class CompanyModule extends AbstractModel implements Addressable, Documentable,
     {
         return $this->hasManyDeep(Transaction::class, [Order::class], ['company_module_id',  'owner_id'], ['id', 'id']);
     }
-    
+
     /**
      * @return MorphMany
      */

resources/views/pages/warehouse_list.blade.php

@@ -1,22 +1,34 @@
 @extends('layouts.base_portal')
 @section('inner_content')
     <div class="row">
-        <div class="col-5">
+        <div class="col-8">
             <div class="row p-b-5 b-b b-grey m-b-10 m-l-0 m-r-0">
                 <div class="col no-padding">
                     <h6>Arrived Parcel</h6>
                 </div>
             </div>
-            <a href="{{route('packing_list.arrived_parcel.export')}}" target="_blank">
-                <button type="button" class="btn btn-sm p-t-10 p-b-10 p-r-35 p-l-35 btn-primary b-rad-none">
-                    <div class="row align-items-center">
-                        <div class="col-auto p-r-0 p-l-0">
-                            <i class="fa fa-file-excel-o fs-16"></i>
-                        </div>
-                        <div class="col p-r-5">Export To Excel</div>
+            <form action="{{route('packing_list.arrived_parcel.export')}}" method="GET" target="_blank">
+                <div class="form-group row">
+                    <label for="start_date" class="col-sm-2 col-form-label">Start Date</label>
+                    <div class="col-sm-10">
+                        <input type="date" class="form-control" id="start_date" name="start_date">
                     </div>
-                </button>
-            </a>
+                </div>
+
+                <div class="form-group row">
+                    <label for="end_date" class="col-sm-2 col-form-label">End Date</label>
+                    <div class="col-sm-10">
+                        <input type="date" class="form-control" id="end_date" name="end_date">
+                    </div>
+                </div>
+
+                <div class="form-group row">
+                    <div class="col-sm-10 offset-sm-2">
+                        <button type="submit" class="btn btn-primary">Export</button>
+                    </div>
+                </div>
+            </form>
+
             <list-component section="warehouseListSection" :endpoint="route('api.packing_list.list')" :options="{packing_list_type: 1, per_page: 20, order_by: {column: 'arrival_date', DESC: true}, with_arrival_date: true}" >
                 <template slot="list" slot-scope="{data}">
                     <parcel-component v-for="packages in data.packages" :data="packages" :key="packages.id"></parcel-component>
@@ -48,4 +60,4 @@
             </list-component>
         </div>
     </div>
-@endsection
+@endsection

routes/web.php

@@ -35,6 +35,7 @@ use App\Models\CompanyConnection;
 use App\Models\CompanyModule;
 use App\Models\Document;
 use App\Models\Order;
+use App\Models\Package;
 use App\Models\PackingList;
 use Carbon\Carbon;
 use Illuminate\Http\Request;
@@ -379,7 +380,7 @@ Route::get('/yd', function (Request $request){
     (App()->make(FetchPackingListsFromYdPortalProcessor::class))->execute($start, $end);
     (App()->make(FetchContainersFromYdPortalProcessor::class))->execute();
     (App()->make(FetchContainersUpdatesFromYdPortalProcessor::class))->execute();
-    (App()->make(FetchDeliveryUpdatesFromYdPortalProcessor::class))->execute();
+//    (App()->make(FetchDeliveryUpdatesFromYdPortalProcessor::class))->execute();
 //    (App()->make(FetchPackingListFromVTPortalProcessor::class))->execute();
 })->name('yd.refresh');
 
@@ -864,11 +865,14 @@ Route::get('/invoices/combine/{ids}', function($ids){
 // })->name('generate.invoices');
 
 Route::get('/invoices/approve', function(Request $request){
-//    whereDoesntHave
     $invoices = Transaction::where('type', TransactionType::SHIPPING_INVOICE)->where('status', ApprovalStatus::PENDING_SUBMISSION)->get();
 
     foreach ($invoices as $invoice) {
         $packingList = $invoice->owner;
+        if (!$packingList) {
+            dump ('Error packingList not found. Invoice ID - ' . $invoice->id);
+            continue;
+        }
         $order = $packingList->owner;
 
         if ($request->input('exclude')){
@@ -1140,3 +1144,49 @@ Route::get('/final-duplicated-invoice-debug', function(){
     echo '</table>';
 });
 
+
+Route::get('/duplicate-package-clean-up', function(){
+    // Fetching duplicate rows based on given columns
+    $duplicates = Package::select('packing_list_id', 'description', 'width', 'height', 'weight', 'quantity', DB::raw('COUNT(*) as count'))
+        ->groupBy('packing_list_id', 'description', 'width', 'height', 'weight', 'quantity')
+        ->havingRaw('COUNT(*) > 1')
+        ->get();
+
+    $totalGroups = 0;
+
+    // Loop through each group of duplicates
+    foreach ($duplicates as $duplicate) {
+        $totalGroups++;
+
+        // Fetch all rows of the current group and order by created_at
+        $rows = Package::where('packing_list_id', $duplicate->packing_list_id)
+            ->where('description', $duplicate->description)
+            ->where('width', $duplicate->width)
+            ->where('height', $duplicate->height)
+            ->where('weight', $duplicate->weight)
+            ->where('quantity', $duplicate->quantity)
+            ->orderBy('created_at')
+            ->get();
+
+        // Echo the group details
+        echo "Group {$totalGroups} Details:<br>";
+        foreach ($rows as $row) {
+            echo "ID: {$row->id}, Packing List ID: {$row->packing_list_id}, Description: {$row->description}, Width: {$row->width}, Height: {$row->height}, Weight: {$row->weight}, Quantity: {$row->quantity}, Created At: {$row->created_at}<br>";
+        }
+        // Delete all rows in the group except the first one (oldest based on created_at)
+        $firstRow = $rows->shift();
+        foreach ($rows as $row) {
+            Package::where('id', $row->id)->delete();
+        }
+
+        // Calculate the case age for the group (assuming case age means difference between current date and created_at of the oldest row)
+        $caseAge = Carbon::now()->diffInDays(Carbon::parse($firstRow->created_at));
+        echo "Case Age for Group {$totalGroups}: {$caseAge} days<br><br>";
+    }
+
+    // Echo the total number of duplicate groups
+    echo "Total Number of Duplicate Groups: {$totalGroups}";
+
+});
+
+