Untitled

$whereCondition = 'AND (';
            $whereConditionFilter = 'AND ';
            $sort = '';
            // filter by id
            if (isset($filters['id'])) {
                $whereCondition .= 'w.id = ' . $filters['id'] . '';
            }

            if (isset($request['filters']['custom_filters'])) {
                $searchArray = $request['filters']['custom_filters'];

                foreach ($searchArray as $filter) {
                    if ($whereConditionFilter !== 'AND ') {
                        $whereConditionFilter .= ' AND ';
                    }
                    if ($filter['term'] == 'like') {
                        $searchTerm = '%' . $filter['query'] . '%';

                        // Split the search query into individual words
                        $words = explode(' ', $filter['query']);

                        // Reverse the order of the words
                        $reversedWords = array_reverse($words);

                        // Join the reversed words to create a reversed search term
                        $reversedSearchTerm = '%' . implode(' ', $reversedWords) . '%';

                        // Use both original and reversed search terms in the query
                        $whereConditionFilter .= $filter['key'] . ' ilike \'' . $searchTerm . '\' ';
                        if ($whereConditionFilter !== 'AND ') {
                            $whereConditionFilter .= ' AND ';
                        }
                        $whereConditionFilter .= $filter['key'] . ' ilike \'' . $reversedSearchTerm . '\' ';
                    } elseif ($filter['term'] == 'equal') {
                        $whereConditionFilter .= $filter['key'] . ' = \'' . $filter['query'] . '\' ';
                    } elseif ($filter['term'] == 'not equal') {
                        $whereConditionFilter .= $filter['key'] . ' != \'' . $filter['query'] . '\' ';
                    } elseif ($filter['term'] == 'in') {
                        $whereConditionFilter .= $filter['key'] . ' in \'' . $filter['query'] . '\' ';
                    }
                }
            }

            // Filter by search
            if (isset($request['search'])) {
                foreach ($request['search'] as $search) {
                    if ($whereCondition !== 'AND (') {
                        $whereCondition .= ' OR ';
                    }
                    if ($search['term'] == 'like') {
                        $searchTerm = '%' . $search['query'] . '%';

                        // Split the search query into individual words
                        $words = explode(' ', $search['query']);

                        // Reverse the order of characters in each word
                        $reversedWords = array_map('strrev', $words);

                        // Join the reversed words to create a reversed search term
                        $reversedSearchTerm = '%' . implode(' ', $reversedWords) . '%';
                        $whereCondition .= $search['key'] . ' ilike \'' . $searchTerm . '\' ';
                        if ($whereCondition !== 'AND (') {
                            $whereCondition .= ' OR ';
                        }
                        $whereCondition .= $search['key'] . ' ilike \'' . $reversedSearchTerm . '\' ';
                    } elseif ($search['term'] == 'equal') {
                        $whereCondition .= $search['key'] . ' = ' . $search['query'] . ' ';
                    } elseif ($search['term'] == 'not equal') {
                        $whereCondition .= $search['key'] . ' != ' . $search['query'] . ' ';
                    }
                }
            }

            // SORT = DESC | ASC
            if (isset($filters['sort'])) {
                if (isset($filters['order_by'])) {
                    $column = $filters['order_by'];
                } else {
                    $column = 'id';
                }

                $sort = ' ORDER BY ' . $column . ' ' . $filters['sort'] . '';
            }

            if ($whereCondition === 'AND (') {
                $whereCondition = '';
            } else {
                $whereCondition .= ')';
            }
            if ($whereConditionFilter === 'AND ') {
                $whereConditionFilter = '';
            }

            $limit = $filters['take'] ?? 0;
            $offset = $filters['skip'] ?? 0;
            $select = 'WITH RECURSIVE top_parent AS (
                SELECT
                    id AS top_parent_id,
                    id,
                    code AS top_parent_code,
                    name AS top_parent_name,
                    parent_warehouse_id
                FROM warehouses
                WHERE parent_warehouse_id IS NULL

                UNION ALL

                SELECT
                    tp.top_parent_id,
                    w.id,
                    tp.top_parent_code,
                    tp.top_parent_name,
                    w.parent_warehouse_id
                FROM warehouses w
                INNER JOIN top_parent tp ON w.parent_warehouse_id = tp.id
            )
            SELECT
                w.*,
                tp.top_parent_id,
                tp.top_parent_code,
                CASE WHEN w.parent_warehouse_id IS NULL THEN \'\' ELSE tp.top_parent_name END AS top_parent_name,
                c.name as city_name
            FROM warehouses w
            LEFT JOIN top_parent tp ON w.id = tp.id
            LEFT JOIN cities c ON w.city_id = c.id
            WHERE w.deleted_at IS NULL
                ' . $whereConditionFilter . '
                ' . $whereCondition;

            $res = DB::select($select);

            if ($limit > 0) {
                $resLimit = DB::select($select . ' ' . $sort . ' LIMIT ' . $limit . ' OFFSET ' . $offset);
            } else {
                $resLimit = DB::select($select . ' ' . $sort);
            }
            $data = [];

            foreach ($resLimit as $row) {
                $data[] = [
                    'id' => $row->id,
                    'code' => $row->code,
                    'name' => $row->name,
                    'address' => $row->address ?? '',
                    'remark' => $row->remark ?? '',
                    'type' => $row->type,
                    'condition' => $row->condition,
                    'is_active' => $row->is_active === true ? '1' : '0',
                    'city_id' => $row->city_id ?? '',
                    'city_name' => $row->city_name ?? '',
                    'parent_warehouse_id' => $row->parent_warehouse_id ?? '',
                    'top_parent_id' => $row->top_parent_id ?? '',
                    'top_parent_code' => $row->top_parent_code ?? '',
                    'top_parent_name' => $row->top_parent_name ?? '',
                ];
            }

            $countData = count($res);

            if (isset($requestBody['draw'])) {
                $draw = $requestBody['draw'];
            }

            $result = ['draw' => $draw ?? '', 'recordsTotal' => $countData, 'recordsFiltered' => $countData, 'total_rows' => $countData, 'data' => $data];

            return $this->sendResponse(
                true,
                Response::HTTP_OK,
                $result
            );