Pastebin | Laravel.io

public function filter(Request $request)
    {
        $reservationId = $request->input('reservation_name', []);
        $lockId = $request->input('lock_name', []);
        $activityId = $request->input('activity_name');
        $statuId = $request->input('label_name', []);
        $startDate = $request->input('start_name');
        $endDate = $request->input('end_name');
        $poolId = $request->input('pool_name', []);
        $userId = $request->input('user_name', []);
        $cityId = $request->input('city_name', []);
        $gameId = $request->input('game_name', []);
        $payId = $request->input('pay_name', []);
        $approvedId = $request->input('approved_name', []);
        $searchId = $request->input('searchInput');

$query = PoolCustomer::query();
        $eagerLoadRelations = ['lock', 'statu', 'label', 'pool', 'user', 'tempCustomerData'];
        if ($userId) {
            $query->whereExists(function ($subQuery) use ($userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_partner_customer')
                    ->whereRaw('user_partner_customer.customer_id = pool_customer.id')
                    ->whereIn('user_partner_customer.user_id', (array)$userId);
            });
        }
        if ($startDate && $endDate) {
            $query->whereExists(function ($subQuery) use ($startDate, $endDate, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_customer_reservation')
                    ->whereRaw('user_customer_reservation.customer_id = pool_customer.id')
                    ->whereBetween('user_customer_reservation.start_date_reserve', [$startDate, $endDate])
                    ->whereNull('user_customer_reservation.deleted_at');

if ($userId) {
                    $subQuery->whereIn('user_customer_reservation.user_id', (array)$userId);
                }
            });
        }

if ($cityId) {

$query->whereExists(function ($subQuery) use ($cityId, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_customer_details')
                    ->whereRaw('user_customer_details.customer_id = pool_customer.id')
                    ->whereIn('user_customer_details.city_id', (array)$cityId);

if ($userId) {
                    $subQuery->whereIn('user_customer_details.user_id', (array)$userId);
                }
            });
        }

if ($searchId) {

$query->where('pool_customer.customer_name', 'LIKE', '%' . $searchId . '%');

if ($userId) {
                $query->where(function ($q) use ($userId) {
                    $q->whereIn('pool_customer.user_id', (array)$userId);
                });
            }
        }

if ($payId) {
            $query->whereExists(function ($subQuery) use ($payId, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_customer_details')
                    ->whereRaw('user_customer_details.customer_id = pool_customer.id')
                    ->whereIn('user_customer_details.pay_label_id', (array)$payId);

if ($userId) {
                    $subQuery->whereIn('user_customer_details.user_id', (array)$userId);
                }
            });
        }

if ($reservationId) {
            $query->whereExists(function ($subQuery) use ($reservationId, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_customer_reservation')
                    ->whereNull('user_customer_reservation.deleted_at')
                    ->whereRaw('user_customer_reservation.customer_id = pool_customer.id')
                    ->whereIn('user_customer_reservation.reserve_id', (array)$reservationId);

if ($userId) {
                    $subQuery->whereIn('user_customer_reservation.user_id', (array)$userId);
                }
            });
        }

if ($poolId) {
            $query->whereIn('pool_customer.pool_id', (array)$poolId);

if ($userId) {
                $query->where(function ($q) use ($userId) {
                    $q->whereIn('pool_customer.user_id', (array)$userId);
                });
            }
        }

if ($lockId) {
            $query->whereIn('pool_customer.lock_id', (array)$lockId);

if ($userId) {
                $query->where(function ($q) use ($userId) {
                    $q->whereIn('pool_customer.user_id', (array)$userId);
                });
            }
        }

if ($activityId) {
            $query->where('pool_customer.activity_status_id', $activityId);

if ($userId) {
                $query->where(function ($q) use ($userId) {
                    $q->whereIn('pool_customer.user_id', (array)$userId);
                });
            }
        }

if ($statuId) {
            $query->whereExists(function ($subQuery) use ($statuId, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('user_customer_statu')
                    ->whereRaw('user_customer_statu.customer_id = pool_customer.id')
                    ->whereNull('user_customer_statu.deleted_at')
                    ->whereIn('user_customer_statu.statu_id', (array)$statuId);

if ($userId) {
                    $subQuery->where('user_customer_statu.user_id', $userId);
                }
            });
        }

if ($approvedId) {
            $query->whereIn('pool_customer.is_approved', (array)$approvedId);

if ($userId) {
                $query->where(function ($q) use ($userId) {
                    $q->whereIn('pool_customer.user_id', (array)$userId);
                });
            }
        }

if ($gameId) {
            $query->whereExists(function ($subQuery) use ($gameId, $userId) {
                $subQuery->select(DB::raw(1))
                    ->from('customer_game_label')
                    ->whereRaw('customer_game_label.customer_id = pool_customer.id')
                    ->whereIn('customer_game_label.game_id', (array)$gameId);

if ($userId) {
                    $subQuery->whereIn('customer_game_label.user_id', (array)$userId);
                }
            });
        }

$result = $query->with($eagerLoadRelations)
            ->orderBy('updated_at', 'desc');

$customerIds = $result->pluck('id')->toArray();

$customerReservationsMap = DB::table('user_customer_reservation')
            ->select('customer_id', 'reserve_id', 'start_date_reserve')
            ->whereNull('deleted_at')
            ->whereIn('customer_id', $customerIds)
            ->where('is_completed', 0)
            ->orderBy('created_at', 'desc')
            ->get()
            ->groupBy('customer_id');

$reserveIds = $customerReservationsMap->flatten(1)->pluck('reserve_id')->unique()->toArray();
        $reservationNames = DB::table('reservation')
            ->whereIn('id', $reserveIds)
            ->select('id', 'reservation_name')
            ->get()
            ->keyBy('id');

$query2 = TepmCustomerData::query();
            foreach ($customerIds as $id) {
                $query2->orWhereJsonContains('customer_ids', (string) $id);
            }
            $tempCustomerDataList = $query2->get();

$callCountsMap = DB::table('call_record')
            ->select('customer_id', DB::raw('count(*) as total_calls'))
            ->whereIn('customer_id', $customerIds)
            ->groupBy('customer_id')
            ->get()
            ->keyBy('customer_id');

$callDetailsMap = DB::table('call_record')
            ->join('users', 'call_record.user_id', '=', 'users.id')
            ->select(
                'call_record.customer_id',
                'call_record.user_id',
                'users.first_name',
                'users.last_name',
                DB::raw('count(*) as user_call_count')
            )
            ->whereIn('call_record.customer_id', $customerIds)
            ->groupBy('call_record.customer_id', 'call_record.user_id', 'users.first_name', 'users.last_name')
            ->get()
            ->groupBy('customer_id');

$statusUsersQuery = DB::table('user_customer_statu as ucs')
            ->join('users', 'ucs.user_id', '=', 'users.id')
            ->select(
                'ucs.customer_id',
                'users.first_name',
                'users.last_name',
                DB::raw('ROW_NUMBER() OVER(PARTITION BY ucs.customer_id ORDER BY ucs.updated_at DESC) as rn')
            )
            ->whereIn('ucs.customer_id', $customerIds);

$latestStatusDates = DB::table('user_customer_statu')
            ->select('customer_id', DB::raw('MAX(updated_at) as latest_update'))
            ->whereIn('customer_id', $customerIds)
            ->groupBy('customer_id');

$statusUsersMap = DB::table('user_customer_statu as ucs')
            ->joinSub($latestStatusDates, 'latest', function ($join) {
                $join->on('ucs.customer_id', '=', 'latest.customer_id')
                    ->on('ucs.updated_at', '=', 'latest.latest_update');
            })
            ->join('users', 'ucs.user_id', '=', 'users.id')
            ->select('ucs.customer_id', 'users.first_name', 'users.last_name')
            ->get()
            ->groupBy('customer_id');

$getCustomerName = DB::table('user_customer_reservation')
            ->select('customer_id', 'reserve_id', 'start_date_reserve')
            ->whereNull('deleted_at')
            ->whereIn('customer_id', $customerIds)
            ->orderBy('start_date_reserve', 'desc')
            ->get();

$filteredData = $result->get()->map(function ($customer) use (
            $reservationNames,
            $customerReservationsMap,
            $tempCustomerDataList,
            $callCountsMap,
            $callDetailsMap,
            $statusUsersMap,
            $getCustomerName,
        ) {
            $tempCustomerData = $tempCustomerDataList->first(function ($item) use ($customer) {
                return in_array((string) $customer->id, json_decode($item->customer_ids, true));
            });
            $customer->expiration_date = $tempCustomerData ? Carbon::parse($tempCustomerData->created_at)->addHours(48) : null;

$customer->total_calls = $callCountsMap[$customer->id]->total_calls ?? 0;

$customer->call_details = isset($callDetailsMap[$customer->id])
                ? $callDetailsMap[$customer->id]->map(function ($detail) {
                    return [
                        'first_name' => $detail->first_name ?? "Bilinmiyor",
                        'last_name' => $detail->last_name ?? "Bilinmiyor",
                        'user_call_count' => $detail->user_call_count ?? 0,
                    ];
                })->toArray()
                : [];

$statusUsers = isset($statusUsersMap[$customer->id])
                ? $statusUsersMap[$customer->id]->map(function ($statusUser) {
                    return $statusUser->first_name . ' ' . $statusUser->last_name;
                })->toArray()
                : [];
            $customer->status_users = implode(', ', $statusUsers);

$customerReservations = $customerReservationsMap[$customer->id] ?? collect([]);
            $latestReservation = $customerReservations->first();

$reservationGetName = $getCustomerName->firstWhere('customer_id',$customer->id);
            $customer->end_date = $reservationGetName && isset($reservationNames[$reservationGetName->reserve_id]) ? $reservationNames[$reservationGetName->reserve_id]->reservation_name : null;

if ($latestReservation && isset($reservationNames[$latestReservation->reserve_id])) {
                $customer->reserve_name = $reservationNames[$latestReservation->reserve_id]->reservation_name;
            } else {
                $customer->reserve_name = null;
            }

$customer->is_reserve_locked = ($customer->reserve_lock == 1);

if ($customer->is_reserve_locked && $latestReservation && $latestReservation->start_date_reserve) {
                $customer->reserve_lock_date = Carbon::parse($latestReservation->start_date_reserve)
                    ->addMonths(3)
                    ->format('d-m-Y');
            } else {
                $customer->reserve_lock_date = null;
            }

return $customer;
        });

return response()->json([
            'filterdata' => $filteredData,
        ]);
    }

Please note that all pasted data is publicly available.

Please note that all pasted data is publicly available.