Untitled

$this
            ->withoutGlobalScope('filterApp')
            ->select(
                \DB::raw('(SELECT count(*) FROM workers WHERE workflow_program_id = programs.id and status != 0) as analytics'),
                \DB::raw('(SELECT count(*) FROM workers WHERE workflow_program_id = programs.id) as number_of_version'),
                'users.name as user_name',
                'users.id as user_id',
                'programs.id',
                'programs.name',
                'programs.type as program_type',
                \DB::raw('(SELECT DATE_FORMAT(IFNULL(workers.cron_date, workers.start_date),"%Y-%m-%dT%TZ")) as start_date'),
                'programs.description',
                \DB::raw('(SELECT DATE_FORMAT(workers.created_at, "%Y-%m-%dT%TZ")) as created_at'),
                \DB::raw('(SELECT DATE_FORMAT(workers.updated_at, "%Y-%m-%dT%TZ")) as updated_at'),
                'workers.type as type',
                'workers.schedule as schedule',
                'workers.state as state',
                DB::raw('If (programs.published_workflow is Not null, workers.version, 0) as version'),
                \DB::raw("
                     CASE 
                     WHEN data_resources.resourcable_type = '".Segment::SHORTNAME."' 
                    THEN (SELECT name FROM segments WHERE id = data_resources.resourcable_id) 
                    END resource_name"),
                \DB::raw("
                     CASE 
                     WHEN programs.status = 0 THEN 'Archive' 
                     WHEN workers.status = 0 THEN 'Design' 
                     WHEN workers.status = 1 THEN 'Active' 
                     WHEN workers.status = 2 THEN 'Stop'
                     WHEN workers.status = 3 THEN 'Done'
                     ELSE 'Design'
                     END status
                     "),
                'data_resources.resourcable_type',
                'data_resources.resourcable_id',
                'data_resources.id as data_resources_id',
                'programs.tags as tags'
            )
            ->leftJoin(
                'workers',
                'workers.id',
                '=',
                DB::raw('CASE WHEN programs.published_workflow is not null
                    THEN programs.published_workflow
                    ELSE
                    (
                      Select id From workers WHERE workflow_program_id = programs.id order by version DESC limit 1
                   
                    )  
                    END')
            )
            ->leftJoin('worker_resources', \DB::raw('SUBSTRING(workers.start_node, 10)'), '=', 'worker_resources.id')
            ->leftJoin('data_resources', 'data_resources.id', '=', 'worker_resources.resource_id')
            ->leftJoin('users', 'users.id', '=', 'workers.user_id')
            ->where('programs.app_id', App::$APPID)
            ->orderBy($filters['order_col'] ?? 'updated_at', $filters['order_dir'] ?? 'desc');

        return DB::table(DB::raw("({$query->toSql()}) as sub"))
            ->mergeBindings($query->getQuery())
            ->when(isset($filters['status']), function (Builder $query) use ($filters) {
                $query->whereIn('status', $filters['status']);
            })->when(isset($filters['type']), function (Builder $query) use ($filters) {
                $query->where('type', $filters['type']);
            })->when(isset($filters['created_at']), function (Builder $query) use ($filters) {
                $query->whereDate('created_at', $filters['created_at']);
            })->when(isset($filters['id']), function (Builder $query) use ($filters) {
                $query->where('id', $filters['id']);
            })->when(isset($filters['search']), function (Builder $query) use ($filters) {
                $query->where(function (Builder $query) use ($filters) {
                    $fields = ['user_name','name','description','resource_name','status'];
                    foreach ($fields as $field) {
                        $query->orWhere($field, 'like', '%'.$filters['search'].'%');
                    }
                });
            })->when(isset($filters['start_date']), function (Builder $query) use ($filters) {
                $query->whereDate('created_at', '>=', Carbon::parse($filters['start_date']));
            })->when(isset($filters['end_date']), function (Builder $query) use ($filters) {
                $query->whereDate('created_at', '<=', Carbon::parse($filters['end_date']));
            })/*->when(isset($filters['resource']), function (Builder $query) use ($filters) {
                $query->where('resourcable_type', $filters['resource']);
            })*/->when(isset($filters['resource']), function (Builder $query) use ($filters) {
                $query->where('data_resources_id', $filters['resource']);
            })
            ->when(isset($filters['execution_range']), function (Builder $query) use ($filters) {
                $startDate = Carbon::parse($filters['execution_range'][0] ?? null);
                $endDate = Carbon::parse($filters['execution_range'][1] ?? null);
                $query->whereIn('type', [Worker::PERIODICAL,Worker::ONCE]);
                $query->whereDate('start_date', '>=', $startDate);
                $query->whereDate('start_date', '<=', $endDate);
            })->when(isset($filters['name']), function (Builder $query) use ($filters) {
                $query->where('name', 'like', '%'.$filters['name'].'%');
            })->when(isset($filters['user']), function (Builder $query) use ($filters) {
                $query->whereIn('user_id', $filters['user']);
            })->when(isset($filters['program_type']), function (Builder $query) use ($filters) {
                $query->where('program_type', $filters['program_type']);
            })->paginate($filters['per_page'] ?? static::PAGINATE);