update aging listing

Quick undo experimental security check

See merge request CIEFWorldwideSdnBhd/shipping-portal!176

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

@@ -0,0 +1,30 @@
+<?php
+
+namespace App\Classes\General\Eloquent\Filters;
+
+use Carbon\Carbon;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Database\Eloquent\Builder;
+
+class WithAgingColumn implements Filter
+{
+
+    /**
+     * @param Builder $builder
+     * @param $value
+     * @return mixed
+     */
+    public static function apply(Builder $builder, $value)
+    {
+        $today = Carbon::now();
+        return $builder->select('packing_lists.*')
+        ->addSelect(DB::raw("DATEDIFF('$today', transactions.updated_at) as days_over_duedate"))
+        ->addSelect(DB::raw("CASE
+        WHEN DATEDIFF('$today', transactions.updated_at) <= 0 THEN 0
+        WHEN DATEDIFF('$today', transactions.updated_at) > 0 AND DATEDIFF('$today', transactions.updated_at) <= 30 THEN 1
+        WHEN DATEDIFF('$today', transactions.updated_at) > 30 AND DATEDIFF('$today', transactions.updated_at) <= 60 THEN 2
+        WHEN DATEDIFF('$today', transactions.updated_at) > 60 AND DATEDIFF('$today', transactions.updated_at) <= 90 THEN 3
+        ELSE 4
+        END AS due_date_number"));
+    }
+}

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

@@ -0,0 +1,98 @@
+<?php
+
+namespace App\Classes\Modules\Exports\Services;
+
+use App\Models\Order;
+use App\Models\PackingList;
+use Maatwebsite\Excel\Concerns\FromQuery;
+use Maatwebsite\Excel\Concerns\Exportable;
+use Maatwebsite\Excel\Concerns\WithMapping;
+use Maatwebsite\Excel\Concerns\WithHeadings;
+use Maatwebsite\Excel\Concerns\ShouldAutoSize;
+use Maatwebsite\Excel\Concerns\WithHeadingRow;
+use App\Classes\General\Eloquent\ApplyFiltersToQuery;
+use App\Classes\ValueObjects\Constants\ApprovalStatus;
+use DateTime;
+
+class ExportsAgingList implements WithHeadings, WithHeadingRow, WithMapping, ShouldAutoSize, FromQuery
+{
+    use Exportable;
+
+    private $filters;
+
+    public function __construct()
+    {
+        $this->filters = [
+            "has_invoice_status_in"                 => [2],
+            "packing_list_ordered_by_invoice_date"  => true,
+            "with_aging_column"                     => true
+        ];
+    }
+
+    public function headings(): array
+    {
+        return [
+            'Company Name',
+            'Customer Marking',
+            'Order Number',
+            'Invoice No',
+            'Invoice Date',
+            'Days',
+            'Amount',
+        ];
+    }
+
+    public function query()
+    {
+        return (new ApplyFiltersToQuery())->execute(PackingList::query(), $this->filters);
+    }
+
+    public function map($list): array
+    {
+        $marking = null;
+        $orderNo = null;
+        $name    = null;
+        $invDate = 'n/a';
+        $invNo   = 'n/a';
+        $days    = 'n/a';
+
+        if ($list->owner instanceof Order) {
+            $inviterPivotInviteeReference = $list->owner->companyModule->inviters()->withPivot('invitee_reference')->first();
+
+            if ($inviterPivotInviteeReference) {
+                $marking = $inviterPivotInviteeReference->pivot->invitee_reference;
+                $orderNo = $list->owner->reference;
+            }
+            $name = $list->owner->companyModule->company->name;
+        }
+
+        $transaction = $list->transactions()->whereIn('status', [ApprovalStatus::APPROVED])->first();
+        if ($transaction) {
+
+            $invoiceDate = $transaction->created_at;
+            $invDate     = date_format($invoiceDate, 'd-m-Y');
+            $invNo       = $transaction->bill_no;
+            $amt         = number_format($transaction->amount, 2);
+
+            $currentDate = new DateTime();
+            $interval    = $currentDate->diff($invoiceDate);
+            $days        = $interval->format('%a');
+        }
+
+        return [
+            $name,
+            $marking,
+            $orderNo,
+            $invNo,
+            $invDate,
+            $days,
+            $amt,
+        ];
+    }
+
+    private function dueDateColumn($colNum, $dueDateNumber, $amt)
+    {
+        if ($colNum == $dueDateNumber) return $amt;
+        return null;
+    }
+}

app/Http/Controllers/Exports/ExportArrivedParcelController.php

@@ -9,6 +9,7 @@ use App\Models\User;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\Auth;
 use Maatwebsite\Excel\Excel;
+use App\Classes\Modules\Exports\Services\ExportsAgingList;
 
 class ExportArrivedParcelController
 {
@@ -46,4 +47,11 @@ class ExportArrivedParcelController
         ob_end_clean();
         return $response;
     }
+
+    public function aging(Request $request) {
+        $data = new ExportsAgingList();
+        $response = $data->download('aging_report.xls', Excel::XLS, ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']);
+        ob_end_clean();
+        return $response;
+    }
 }

routes/web.php

@@ -436,9 +436,11 @@ Route::get('/warehouse/{id}/show', function ($id) {
 Route::get('/export/customer-latest-order-date/f614e339d7058904a831aad742e24d55', 'Exports\ExportCustomersToExcelController@export');
 Route::get('/export/packing-list/{id}', 'Exports\ExportContainerPackingListController@export')->name('container.packaging_list.export');
 Route::get('/export/pending-arrangement-delivery-list', 'Exports\ExportPendingArrangementPackingListV2Controller@export')->name('packaging_list.pending_arrangement.export');
-Route::get('/export/on-hold-packing-list', 'Exports\ExportPendingArrangementPackingListV2Controller@onHold')->name('packaging_list.on_hold.export');
+// Route::get('/export/on-hold-packing-list', 'Exports\ExportPendingArrangementPackingListV2Controller@onHold')->name('packaging_list.on_hold.export');
+Route::get('/export/on-hold-packing-list', 'Exports\ExportPendingArrangementPackingListController@onHold')->name('packaging_list.on_hold.export');
 Route::get('/export/arrived-parcel', 'Exports\ExportArrivedParcelController@export')->name('packing_list.arrived_parcel.export');
 Route::get('/export/parcel-summary', 'Exports\ExportArrivedParcelController@summary');
+Route::get('/export/aging-list', 'Exports\ExportArrivedParcelController@aging')->name('aging-listing.export');
 Route::get('/export/parcel-postcode', 'Exports\ExportParcelPostcodesController@export');
 Route::get('/export/{year}/customer-total-order', 'Exports\ExportCustomersToExcelController@totalOrders');
 Route::get('/export/packing-list-warehouse/guangzhou2-to-johor', 'Exports\ExportArrivedParcelController@guangZhou2ToJohor');