strprfcrm/app/Http/Controllers/ReportController.php

<?php

namespace App\Http\Controllers;

use App\Helpers\Price;
use App\Models\Dictionary\District;
use App\Models\ObjectType;
use App\Models\Order;
use App\Models\OrderStatus;
use App\Models\Product;
use App\Models\ProductSKU;
use App\Models\Reclamation;
use App\Models\ReclamationStatus;
use App\Models\User;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Http\Request;
use Illuminate\Support\Str;

class ReportController extends Controller
{
    protected array $data = [
        'active'    => 'reports',
        'title'     => 'Отчёты',
        'id'        => 'reports',
    ];


    public function index()
    {
        $mountStatuses = [5, 7, 8, 9, 10];
        $installedStatuses = [
            Order::STATUS_READY_TO_HAND_OVER,
            Order::STATUS_NOT_HANDED_OVER_WITH_NOTES,
            Order::STATUS_HANDED_OVER_WITH_NOTES,
            Order::STATUS_HANDED_OVER,
        ];
        $doneStatuses = [9, 10];
        $handedOverStatus = Order::STATUS_HANDED_OVER;
        $objectTypes = ObjectType::query()->get()->pluck('name', 'id')->toArray();
        $this->data['objectTypes'] = $objectTypes;
        $user_ids = Order::query()->distinct()->get('user_id')->pluck('user_id')->toArray();
        $managers = User::query()->whereIn('id', $user_ids)->get()->pluck('name', 'id')->toArray();
        $this->data['managers'] = $managers ?? [];
        $this->data['doneMafsManager'] = $this->data['notDoneMafsManager'] = [];


        // всего заказов, выполнено заказов
        $this->data['totalOrders']  = Order::all()->count();
        $this->data['doneOrders']  = Order::query()->whereIn('order_status_id', $doneStatuses)->count();
        $this->data['mountOrders']  = Order::query()->whereIn('order_status_id', $mountStatuses)->count();
        $this->data['installedOrders'] = Order::query()->whereIn('order_status_id', $installedStatuses)->count();

        // всего маф / завершено маф / установлено маф
        $this->data['totalMafs']    = ProductSKU::all()->count();
        $this->data['doneMafs']    = ProductSKU::query()->
            whereHas('order', function ($query) use ($doneStatuses) {
                $query->whereIn('order_status_id', $doneStatuses);
            })->count();
        $this->data['mountMafs']    = ProductSKU::query()->
        whereHas('order', function ($query) use ($mountStatuses){
            $query->whereIn('order_status_id', $mountStatuses);
        })->count();
        $this->data['installedMafs'] = ProductSKU::query()
            ->whereHas('order', function ($query) use ($installedStatuses) {
                $query->whereIn('order_status_id', $installedStatuses);
            })
            ->count();

        // сумма сданных МАФ по площадкам со статусом "Сдана"
        $this->data['totalHandedOverSum'] = Price::format(
            $this->getDoneSumByStatus($handedOverStatus)
        );

        // done by managers
        foreach ($managers as $userId => $userName) {
            $this->data['totalOrderManager'] = Order::where('user_id', $userId)->count();
            $this->data['totalMafsManager'][$userId]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($userId) {
                $query->where('user_id', '=', $userId);
            })->count();

            $this->data['doneOrdersManager'][$userId]  = Order::query()
                ->where('user_id', '=', $userId)
                ->whereIn('order_status_id', $doneStatuses)
                ->count();
            $this->data['doneMafsManager'][$userId]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($userId, $doneStatuses) {
                $query->where('user_id', '=', $userId)
                    ->whereIn('order_status_id', $doneStatuses);
            })->count();

            $this->data['notDoneOrdersManager'][$userId]  = Order::query()
                ->where('user_id', '=', $userId)
                ->whereNotIn('order_status_id', $doneStatuses)
                ->count();
            $this->data['notDoneMafsManager'][$userId]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($userId, $doneStatuses) {
                $query->where('user_id', '=', $userId)
                    ->whereNotIn('order_status_id', $doneStatuses);
            })->count();

        }

        // total mount by types
        foreach ($objectTypes as $objectTypeId => $objectType) {
            // total by types
            $this->data['totalOrdersType'][$objectTypeId]  = Order::where('object_type_id', '=', $objectTypeId)->count();
            $this->data['totalMafsType'][$objectTypeId]     = ProductSKU::query()->
                whereHas('order', function ($query) use ($objectTypeId) {
                    $query->where('object_type_id', '=', $objectTypeId);
                })->count();

            // со статусами: в монтаже, готова к сдаче, не сдана замечания, сдана замечания, сдана - зеленый цвет

            $this->data['mountOrdersType'][$objectTypeId]  = Order::query()
                ->where('object_type_id', '=', $objectTypeId)
                ->whereIn('order_status_id', $mountStatuses)
                ->count();
            $this->data['mountMafsType'][$objectTypeId]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($objectTypeId, $mountStatuses) {
                $query->where('object_type_id', '=', $objectTypeId)
                ->whereIn('order_status_id', $mountStatuses);
            })->count();

            // остальные - не готовы
            $this->data['notMountOrdersType'][$objectTypeId]  = Order::query()
                ->where('object_type_id', '=', $objectTypeId)
                ->whereNotIn('order_status_id', $mountStatuses)
                ->count();
            $this->data['notMountMafsType'][$objectTypeId]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($objectTypeId, $mountStatuses) {
                $query->where('object_type_id', '=', $objectTypeId)
                    ->whereNotIn('order_status_id', $mountStatuses);
            })->count();

        }

        // рекламации
        $this->data['totalReclamations'] = Reclamation::query()->count();
        $this->data['reclamationStatuses'] = ReclamationStatus::query()->get()->pluck('name', 'id')->toArray();
        foreach ($this->data['reclamationStatuses'] as $reclamationStatusId => $reclamationStatus) {
            $this->data['reclamations'][$reclamationStatus] = Reclamation::query()->where('status_id', '=', $reclamationStatusId)->count();
        }
        $this->data['reclamationMafs'] = $this->data['reclamationDetails'] = [];
        $reclamations = Reclamation::query()
            ->with([
                'skus.product',
                'details',
                'spareParts',
            ])
            ->get();

        foreach ($reclamations as $reclamation) {
            foreach ($reclamation->skus as $sku) {
                $a = $sku->product->article;
                if(isset($this->data['reclamationMafs'][$a])){
                    $this->data['reclamationMafs'][$a]++;
                } else {
                    $this->data['reclamationMafs'][$a] = 1;
                }
            }

            foreach ($reclamation->details as $detail) {
                $a = $detail->name;
                if(isset($this->data['reclamationDetails'][$a])){
                    $this->data['reclamationDetails'][$a] += $detail->quantity;
                } else {
                    $this->data['reclamationDetails'][$a] = $detail->quantity;
                }
            }
        }

        $this->appendReclamationSparePartsToReport($reclamations);


        // svod
        $orderStatuses = OrderStatus::query()->get()->pluck('name', 'id')->toArray();
        foreach ($orderStatuses as $orderStatusId => $orderStatus) {
            $this->data['orderStatuses'][$orderStatus] = Order::query()->where('order_status_id', '=', $orderStatusId)->count();
            $this->data['orderMafStatuses'][$orderStatus]     = ProductSKU::query()->
            whereHas('order', function ($query) use ($orderStatusId) {
                $query->where('order_status_id', $orderStatusId);
            })->count();
        }
        // общая сумма
        $this->data['totalSum'] = Price::format(
            ProductSKU::query()
                ->withSum('product', 'total_price')
                ->get()
                ->sum('product_sum_total_price') / 100
        );
        // общая сумма done
        $this->data['totalDoneSum'] = Price::format(
            $this->getDoneSumByStatus($handedOverStatus)
        );

        $districts = District::query()->get(['id', 'shortname']);
        foreach ($districts as $districtModel) {
            $districtId = $districtModel->id;
            $district = $districtModel->shortname;

            $totalOrders = $this->getOrderCount($districtId);
            $totalMafs = $this->getMafCount($districtId);
            $readyOrders = $this->getOrderCount($districtId, 4);
            $readyMafs = $this->getMafCount($districtId, 4);
            $mountOrders = $this->getOrderCount($districtId, 5);
            $mountMafs = $this->getMafCount($districtId, 5);
            $readyDoneOrders = $this->getOrderCount($districtId, 7);
            $readyDoneMafs = $this->getMafCount($districtId, 7);
            $doneOrders = $this->getOrderCount($districtId, $doneStatuses);
            $doneMafs = $this->getMafCount($districtId, $doneStatuses);
            $statusOstOrders = $totalOrders - $mountOrders - $readyDoneOrders - $doneOrders;
            $statusOstMafs = $totalMafs - $mountMafs - $readyDoneMafs - $doneMafs;

            $totalYardOrders = $this->getOrderCount($districtId, null, 1);
            $totalYardMafs = $this->getMafCount($districtId, null, 1);
            $mountYardOrders = $this->getOrderCount($districtId, $mountStatuses, 1);
            $mountYardMafs = $this->getMafCount($districtId, $mountStatuses, 1);
            $ostYardOrders = $totalYardOrders - $mountYardOrders;
            $ostYardMafs = $totalYardMafs - $mountYardMafs;

            $totalEduOrders = $this->getOrderCount($districtId, null, 2);
            $totalEduMafs = $this->getMafCount($districtId, null, 2);
            $mountEduOrders = $this->getOrderCount($districtId, $mountStatuses, 2);
            $mountEduMafs = $this->getMafCount($districtId, $mountStatuses, 2);
            $ostEduOrders = $totalYardOrders - $mountYardOrders;
            $ostEduMafs = $totalYardMafs - $mountYardMafs;

            $totalZnakOrders = $this->getOrderCount($districtId, null, 3);
            $totalZnakMafs = $this->getMafCount($districtId, null, 3);
            $mountZnakOrders = $this->getOrderCount($districtId, $mountStatuses, 3);
            $mountZnakMafs = $this->getMafCount($districtId, $mountStatuses, 3);
            $ostZnakOrders = $totalYardOrders - $mountYardOrders;
            $ostZnakMafs = $totalYardMafs - $mountYardMafs;
            $this->data['byDistrict'][$districtId] = [
                'name'  => $district,
                'totalSum' => $this->getDistrictSum($districtId),
                'doneSum' => $this->getDistrictSum($districtId, [$handedOverStatus]),
                'totalOrders' => $totalOrders,
                'totalMafs' => $totalMafs,
                'readyOrders' => $readyOrders,
                'readyMafs' => $readyMafs,
                'mountOrders' => $mountOrders,
                'mountMafs' => $mountMafs,
                'readyDoneOrders' => $readyDoneOrders,
                'readyDoneMafs' => $readyDoneMafs,
                'doneOrders' => $doneOrders,
                'doneMafs' => $doneMafs,
                'statusOstOrders' => $statusOstOrders,
                'statusOstMafs' => $statusOstMafs,

                'totalYardOrders' => $totalYardOrders,
                'totalYardMafs' => $totalYardMafs,
                'mountYardOrders' => $mountYardOrders,
                'mountYardMafs' => $mountYardMafs,
                'ostYardOrders' => $ostYardOrders,
                'ostYardMafs' => $ostYardMafs,
                'totalEduOrders' => $totalEduOrders,
                'totalEduMafs' => $totalEduMafs,
                'mountEduOrders' => $mountEduOrders,
                'mountEduMafs' => $mountEduMafs,
                'ostEduOrders' => $ostEduOrders,
                'ostEduMafs' => $ostEduMafs,
                'totalZnakOrders' => $totalZnakOrders,
                'totalZnakMafs' => $totalZnakMafs,
                'mountZnakOrders' => $mountZnakOrders,
                'mountZnakMafs' => $mountZnakMafs,
                'ostZnakOrders' => $ostZnakOrders,
                'ostZnakMafs' => $ostZnakMafs,
            ];


        }

        return view('reports.index', $this->data);
    }

    private function appendReclamationSparePartsToReport(Collection $reclamations): void
    {
        foreach ($reclamations as $reclamation) {
            foreach ($reclamation->spareParts as $sparePart) {
                $article = trim((string) $sparePart->article);
                $quantity = (int) ($sparePart->pivot?->quantity ?? 0);

                if ($article === '' || $quantity < 1) {
                    continue;
                }

                if (isset($this->data['reclamationDetails'][$article])) {
                    $this->data['reclamationDetails'][$article] += $quantity;
                } else {
                    $this->data['reclamationDetails'][$article] = $quantity;
                }
            }
        }
    }

    private function getOrderCount($districtId, $status = null, $type = null)
    {
        $q = Order::query()->where('district_id', '=', $districtId);

        if($status) {
            if(!is_array($status)) $status = [$status];
            $q->whereIn('order_status_id', $status);
        }

        if($type) {
            if(!is_array($type)) $type = [$type];
            $q->whereIn('object_type_id', $type);
        }
        return $q->count();
    }

    private function getMafCount($districtId, $status = null, $type = null)
    {
        return ProductSKU::query()->
            whereHas('order', function ($query) use ($districtId, $status, $type) {
                $query->where('district_id', '=', $districtId);

                if($status) {
                    if(!is_array($status)) $status = [$status];
                    $query->whereIn('order_status_id', $status);
                }

                if($type) {
                    if(!is_array($type)) $type = [$type];
                    $query->whereIn('object_type_id', $type);
                }
            })->count();
    }

    private function getDistrictSum($districtId, $done = false)
    {
        return Price::format(
            $this->getDoneSumByStatus($done ?: null, $districtId)
        );

    }

    private function getDoneSumByStatus(array|int|null $statuses = null, ?int $districtId = null): float
    {
        $query = ProductSKU::query()
            ->join('orders', 'orders.id', '=', 'products_sku.order_id')
            ->join('products', 'products.id', '=', 'products_sku.product_id')
            ->where('orders.year', year())
            ->where('products.year', year());

        if ($districtId !== null) {
            $query->where('orders.district_id', $districtId);
        }

        if ($statuses !== null && $statuses !== false) {
            $statuses = is_array($statuses) ? $statuses : [$statuses];
            $query->whereIn('orders.order_status_id', $statuses);
        }

        return ((float) $query->sum('products.total_price')) / 100;
    }


}