mirror of
https://gitlab.com/CIEFWorldwideSdnBhd/shipping-portal.git
synced 2026-08-19 20:44:19 +00:00
Martin Kiragu
402 lines
18 KiB
PHP
402 lines
18 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers\Reports;
|
|
|
|
use App\Classes\ValueObjects\Constants\ApprovalStatus;
|
|
use App\Classes\ValueObjects\Constants\BusinessType;
|
|
use App\Classes\ValueObjects\Constants\HttpStatus;
|
|
use App\Classes\ValueObjects\Constants\PackingListType;
|
|
use App\Classes\ValueObjects\Constants\RoleTypes;
|
|
use App\Classes\ValueObjects\Response\ApiResponseObject;
|
|
use App\Models\CompanyConnection;
|
|
use App\Models\CompanyModule;
|
|
use App\Models\Container;
|
|
use App\Models\Order;
|
|
use App\Models\Package;
|
|
use Carbon\Carbon;
|
|
use Illuminate\Http\JsonResponse;
|
|
use Illuminate\Http\Request;
|
|
|
|
class MonthlyReportController
|
|
{
|
|
|
|
/**
|
|
* Get Containers
|
|
* @param $from
|
|
* @param $to
|
|
* @return mixed
|
|
*/
|
|
private function getContainersByLoadingDate($from, $to)
|
|
{
|
|
return Container::whereBetween('loading_date', [$from, $to])->orderBy('loading_date')->get();
|
|
|
|
}
|
|
|
|
private function getPackages($from, $to)
|
|
{
|
|
|
|
$containers = $this->getContainersByLoadingDate($from, $to);
|
|
|
|
$packingLists = $containers->flatMap(function ($container) {
|
|
return $container->packingLists;
|
|
});
|
|
|
|
return $packingLists->map(function ($packingList) {
|
|
$replica = in_array(Auth()->user()->type, [RoleTypes::SHADOW_ADMIN, RoleTypes::SUPER_ADMIN]) ? $packingList : $packingList->packingLists()->where('type', PackingListType::SHIPPING_PACKING_LIST_REPLICA)->first();
|
|
return $replica ? $replica : $packingList;
|
|
})->flatMap(function ($packingList) {
|
|
return $packingList->packages;
|
|
});
|
|
|
|
}
|
|
|
|
public function salesReport(Request $request): JsonResponse
|
|
{
|
|
$from = $request->route('from') ? Carbon::parse($request->route('from')) : Carbon::now()->startOfMonth();
|
|
$to = $request->route('to') ? Carbon::parse($request->route('to')) : Carbon::now()->endOfMonth();
|
|
|
|
/* $receivedCbm = Package::warehouseReceiveList()->whereHas('deliveryTransports', function ($transport) use ($from, $to) {
|
|
return $transport->whereBetween('drop_date', [$from, $to]);
|
|
})->get()->sum(function($package){
|
|
});*/
|
|
|
|
|
|
$receivedCbm = Package::with('deliveryTransports')
|
|
->warehouseReceiveList()
|
|
->whereHas('deliveryTransports', function ($transport) use ($from, $to) {
|
|
return $transport->whereBetween('drop_date', [$from, $to]);
|
|
})
|
|
->get()
|
|
->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
$containers = Container::whereBetween('loading_date', [$from, $to])->orderBy('loading_date')->get();
|
|
|
|
$customers = $containers->flatMap(function ($container) {
|
|
return $container->companyModules;
|
|
})->unique()->count();
|
|
|
|
$orders = $containers->flatMap(function ($container) {
|
|
return $container->orders;
|
|
})->unique()->count();
|
|
|
|
$packages = $this->getPackages($from, $to);
|
|
|
|
/* $packingLists = $containers->flatMap(function ($container) {
|
|
return $container->packingLists;
|
|
});
|
|
|
|
$packages = $packingLists->map(function ($packingList) {
|
|
$replica = in_array(Auth()->user()->type, [RoleTypes::SHADOW_ADMIN, RoleTypes::SUPER_ADMIN]) ? $packingList : $packingList->packingLists()->where('type', PackingListType::SHIPPING_PACKING_LIST_REPLICA)->first();
|
|
return $replica ? $replica : $packingList;
|
|
})->flatMap(function ($packingList) {
|
|
return $packingList->packages;
|
|
});*/
|
|
|
|
$totalCbm = $packages->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
$newCustomers = CompanyModule::where('type', BusinessType::IMPORTER)->whereBetween('created_at', [$from, $to])->count();
|
|
$newConvertedCustomers = CompanyModule::where('type', BusinessType::IMPORTER)->whereBetween('created_at', [$from, $to])->whereHas('orders', function ($order) {
|
|
return $order->whereHas('packingLists');
|
|
})->count();
|
|
|
|
$startDay = $request->route('to') ? Carbon::parse($request->route('to')) : Carbon::now();
|
|
$days = $startDay->addDay()->diffInDaysFiltered(function (Carbon $date) {
|
|
|
|
return $date->isWeekday();
|
|
|
|
}, $request->route('to') ? Carbon::parse($request->route('to'))->endOfMonth() : Carbon::now()->endOfMonth());
|
|
|
|
return (new ApiResponseObject('fetch monthly report Successful',
|
|
'',
|
|
HttpStatus::OK_WITH_MESSAGE, ['data' => [
|
|
'containers' => count($containers),
|
|
'activated_orders' => $orders,
|
|
'active_customers' => $customers,
|
|
'new_customers' => $newCustomers,
|
|
'new_converted_customers' => $newConvertedCustomers,
|
|
'total_customers' => CompanyModule::where('type', BusinessType::IMPORTER)->count(),
|
|
'received_cbm' => $receivedCbm,
|
|
'total_cbm' => $totalCbm,
|
|
'daily_cbm' => $days ? (800 - $totalCbm) / $days : (800 - $totalCbm),
|
|
'packages' => $packages->sum('quantity'),
|
|
'total_orders' => Order::whereBetween('created_at', [$from, $to])->count()
|
|
]]))->handler();
|
|
}
|
|
|
|
public function profitModelReport(Request $request): JsonResponse
|
|
{
|
|
$from = $request->route('from') ? Carbon::parse($request->route('from')) : Carbon::now()->startOfMonth();
|
|
$to = $request->route('to') ? Carbon::parse($request->route('to')) : Carbon::now()->endOfMonth();
|
|
$model = (int)$request->route('model');
|
|
$value = (float)$request->route('value');
|
|
|
|
$packages = $this->getPackages($from, $to);
|
|
|
|
/* $packingLists = $containers->flatMap(function ($container) {
|
|
return $container->packingLists;
|
|
});
|
|
|
|
$packages = $packingLists->map(function ($packingList) {
|
|
$replica = in_array(Auth()->user()->type, [RoleTypes::SHADOW_ADMIN, RoleTypes::SUPER_ADMIN]) ? $packingList : $packingList->packingLists()->where('type', PackingListType::SHIPPING_PACKING_LIST_REPLICA)->first();
|
|
return $replica ? $replica : $packingList;
|
|
})->flatMap(function ($packingList) {
|
|
return $packingList->packages;
|
|
});*/
|
|
|
|
$totalCbm = $packages->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
$projectedCbm = $packages->sum(function ($package) use ($value, $model) {
|
|
|
|
if ($model === 2) {
|
|
return (((float)($package->width + ($package->width * ($value / 100))) / 100) * ((float)($package->length + ($package->length * ($value / 100))) / 100) * ((float)($package->height + ($package->height * ($value / 100))) / 100)) * $package->quantity;
|
|
}
|
|
|
|
if ($model === 3) {
|
|
return ((((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) + $value) * $package->quantity;
|
|
}
|
|
|
|
if ($model === 4) {
|
|
$packageCbm = (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100));
|
|
return ($packageCbm + ($packageCbm * ($value / 100))) * $package->quantity;
|
|
}
|
|
|
|
return (((float)($package->width + $value) / 100) * ((float)($package->length + $value) / 100) * ((float)($package->height + $value) / 100)) * $package->quantity;
|
|
|
|
});
|
|
|
|
return (new ApiResponseObject('fetch monthly report Successful',
|
|
'',
|
|
HttpStatus::OK_WITH_MESSAGE, ['data' => [
|
|
'total_cbm' => $totalCbm,
|
|
'projected_cbm' => $projectedCbm,
|
|
]]))->handler();
|
|
}
|
|
|
|
public function customerReport(Request $request): JsonResponse
|
|
{
|
|
|
|
$connection = CompanyConnection::where('invitee_reference', $request->route('marking'))->firstOrFail();
|
|
$id = $connection->invitee->id;
|
|
|
|
$from = $request->route('from') ? Carbon::parse($request->route('from')) : Carbon::parse('01-01-2018');
|
|
$to = $request->route('to') ? Carbon::parse($request->route('to')) : Carbon::now();
|
|
|
|
/* $containers = $this->getContainersByLoadingDate($from, $to);
|
|
|
|
$packingLists = $containers->flatMap(function ($container) {
|
|
return $container->packingLists;
|
|
});
|
|
|
|
$packages = $packingLists->map(function ($packingList) {
|
|
$replica = in_array(Auth()->user()->type, [RoleTypes::SHADOW_ADMIN, RoleTypes::SUPER_ADMIN]) ? $packingList : $packingList->packingLists()->where('type', PackingListType::SHIPPING_PACKING_LIST_REPLICA)->first();
|
|
return $replica ? $replica : $packingList;
|
|
})->flatMap(function ($packingList) {
|
|
return $packingList->packages;
|
|
});*/
|
|
|
|
$packages = $this->getPackages($from, $to);
|
|
|
|
$packages = $packages->filter(function ($package) use ($id) {
|
|
return $package->companyModule->id === $id;
|
|
});
|
|
|
|
$orders = $packages->unique(function ($package) {
|
|
return $package->order;
|
|
});
|
|
|
|
$totalCbm = $packages->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
return (new ApiResponseObject('fetch monthly report Successful',
|
|
'',
|
|
HttpStatus::OK_WITH_MESSAGE, ['data' => [
|
|
'activated_orders' => count($orders),
|
|
'total_cbm' => $totalCbm,
|
|
'packages' => $packages->sum('quantity'),
|
|
]]))->handler();
|
|
}
|
|
|
|
public function serviceReport(Request $request): JsonResponse
|
|
{
|
|
|
|
return (new ApiResponseObject('fetch service report Successful',
|
|
'',
|
|
HttpStatus::OK_WITH_MESSAGE, ['data' => [
|
|
'received' => (float)Package::warehouseReceiveList()->sum('quantity'),
|
|
|
|
/* 'pending_shipment' => (float)Package::shippingList()->whereDoesntHave('shippingSchedules', function ($schedule) {
|
|
return $schedule->dispatched()->whereIn('schedules.status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
})->sum('quantity'),
|
|
|
|
'in_transit' => (float) Package::shippingList()->Shipping()->whereHas('shippingSchedules', function($schedule){
|
|
return $schedule->dispatched()->whereIn('schedules.status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
})->whereDoesntHave('container', function($container){
|
|
return $container->where('containers.status', ApprovalStatus::COMPLETED);
|
|
})->sum('quantity'),
|
|
'pending_delivery' => (float) Package::shippingList()->whereHas('container', function($container){
|
|
return $container->where('containers.status', ApprovalStatus::COMPLETED);
|
|
})->whereDoesntHave('deliverySchedules', function($schedule){
|
|
return $schedule->dispatched()->whereIn('schedules.status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
})->sum('quantity'),
|
|
'delivered' => (float) Package::shippingList()->whereHas('deliverySchedules', function($schedule){
|
|
return $schedule->dispatched()->whereIn('schedules.status', [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED]);
|
|
})->sum('quantity'),*/
|
|
|
|
'pending_shipment' => (float)Package::shippingList()
|
|
->whereDoesntHave('shippingSchedules', function ($schedule) {
|
|
$statuses = [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED];
|
|
$schedule->dispatched()->whereIn('schedules.status', $statuses);
|
|
})
|
|
->sum('quantity'),
|
|
|
|
'in_transit' => (float)Package::shippingList()
|
|
->whereHas('shippingSchedules', function ($schedule) {
|
|
$statuses = [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED];
|
|
$schedule->dispatched()->whereIn('schedules.status', $statuses);
|
|
})
|
|
->whereDoesntHave('container', function ($container) {
|
|
$container->where('containers.status', ApprovalStatus::COMPLETED);
|
|
})
|
|
->sum('quantity'),
|
|
|
|
'pending_delivery' => (float)Package::shippingList()
|
|
->whereHas('container', function ($container) {
|
|
$container->where('containers.status', ApprovalStatus::COMPLETED);
|
|
})
|
|
->whereDoesntHave('deliverySchedules', function ($schedule) {
|
|
$statuses = [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED];
|
|
$schedule->dispatched()->whereIn('schedules.status', $statuses);
|
|
})
|
|
->sum('quantity'),
|
|
|
|
'delivered' => (float)Package::shippingList()
|
|
->whereHas('deliverySchedules', function ($schedule) {
|
|
$statuses = [ApprovalStatus::APPROVED, ApprovalStatus::COMPLETED];
|
|
$schedule->dispatched()->whereIn('schedules.status', $statuses);
|
|
})
|
|
->sum('quantity')
|
|
|
|
|
|
]]))->handler();
|
|
}
|
|
|
|
public function customerActivityReport(Request $request): JsonResponse
|
|
{
|
|
|
|
$active_start = $request->input('active_start');
|
|
$active_end = $request->input('active_end');
|
|
$inactive_start = $request->input('inactive_start');
|
|
$inactive_end = $request->input('inactive_end');
|
|
$minCbm = $request->input('cbm');
|
|
$minOrders = $request->input('minOrders');
|
|
|
|
/* $activeCompanies = CompanyModule::where('type', BusinessType::IMPORTER)->whereHas('orderPackingLists', function ($query) use ($inactive_start, $inactive_end) {
|
|
return $query->where('packing_lists.type', PackingListType::WAREHOUSE_RECEIVE_LIST)->whereHas('transports', function ($query) use ($inactive_start, $inactive_end) {
|
|
return $query->where('drop_date', '>=', Carbon::parse($inactive_start))->where('drop_date', '<=', Carbon::parse($inactive_end)->addDay());
|
|
});
|
|
})->pluck('id');
|
|
|
|
$companies = CompanyModule::where('type', BusinessType::IMPORTER)->whereHas('orderPackingLists', function ($query) use ($active_start, $active_end) {
|
|
return $query->where('packing_lists.type', PackingListType::WAREHOUSE_RECEIVE_LIST)->whereHas('transports', function ($query) use ($active_start, $active_end) {
|
|
return $query->whereDate('drop_date', '>=', Carbon::parse($active_start))->whereDate('drop_date', '<=', Carbon::parse($active_end)->addDay());
|
|
});
|
|
})->whereNotIn('id', $activeCompanies)->get();
|
|
|
|
$companies = getActiveCompanies($inactive_start, $inactive_end, $active_start, $active_end);
|
|
|
|
$customerActivityData = [];
|
|
$counter = 1;
|
|
|
|
foreach ($companies as $key => $company) {
|
|
$connection = $company->inviters()->withPivot('invitee_reference')->first();
|
|
$marking = $connection ? $connection->pivot->invitee_reference : '';
|
|
$packingList = $company->orderPackingLists()->where('packing_lists.type', PackingListType::SHIPPING_PACKING_LIST)->get();
|
|
$totalCbm = $packingList->flatMap(function ($packingList) {
|
|
return $packingList->packages;
|
|
})->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
if ($minCbm != 'null' && $totalCbm < $minCbm) {
|
|
continue;
|
|
}
|
|
|
|
if ($minOrders != 'null' && $packingList->count() < $minOrders) {
|
|
continue;
|
|
}
|
|
|
|
array_push($customerActivityData, array(
|
|
'key' => $counter,
|
|
'marking' => $marking,
|
|
'packingListCount' => $packingList->count(),
|
|
'totalCbm' => $totalCbm,
|
|
));
|
|
$counter++;
|
|
}
|
|
|
|
*/
|
|
|
|
$companies = getActiveCompanies($inactive_start, $inactive_end, $active_start, $active_end);
|
|
|
|
$customerActivityData = [];
|
|
|
|
$packingListType = PackingListType::SHIPPING_PACKING_LIST;
|
|
|
|
$companies->load([
|
|
'inviters' => function ($query) {
|
|
$query->withPivot('invitee_reference')->first();
|
|
},
|
|
'orderPackingLists' => function ($query) use ($packingListType) {
|
|
$query->where('packing_lists.type', $packingListType)->with('packages');
|
|
},
|
|
]);
|
|
|
|
$counter = 1;
|
|
|
|
foreach ($companies as $company) {
|
|
$connection = $company->inviters->first();
|
|
$marking = $connection ? $connection->pivot->invitee_reference : '';
|
|
$packingList = $company->orderPackingLists;
|
|
|
|
$totalCbm = $packingList->flatMap->packages->sum(function ($package) {
|
|
return (((float)$package->width / 100) * ((float)$package->length / 100) * ((float)$package->height / 100)) * $package->quantity;
|
|
});
|
|
|
|
if ($minCbm !== null && $totalCbm < $minCbm) {
|
|
continue;
|
|
}
|
|
|
|
if ($minOrders !== null && $packingList->count() < $minOrders) {
|
|
continue;
|
|
}
|
|
|
|
$customerActivityData[] = [
|
|
'key' => $counter,
|
|
'marking' => $marking,
|
|
'packingListCount' => $packingList->count(),
|
|
'totalCbm' => $totalCbm,
|
|
];
|
|
|
|
$counter++;
|
|
}
|
|
|
|
|
|
return (new ApiResponseObject('fetch service report Successful',
|
|
'',
|
|
HttpStatus::OK_WITH_MESSAGE, ['data' => [
|
|
'active_start' => Carbon::parse($active_start)->format('d/m/Y'),
|
|
'active_end' => Carbon::parse($active_end)->format('d/m/Y'),
|
|
'inactive_start' => Carbon::parse($inactive_start)->format('d/m/Y'),
|
|
'inactive_end' => Carbon::parse($inactive_end)->format('d/m/Y'),
|
|
'customerActivityData' => $customerActivityData,
|
|
]]))->handler();
|
|
}
|
|
}
|