Laravel-Training-Project/app/Http/Controllers/ReportsController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use Yajra\DataTables\Facades\DataTables;
class ReportsController extends Controller
{



    public function dynamicDataTable($reportName, Request $request)
    {
        // Fetch the DataTable configuration
        $reportConfig = DB::table('reports')->where('name', $reportName)->first();

        if (!$reportConfig) {
            return response()->json(['error' => 'Report configuration not found'], 404);
        }


        // Determine the type of response (default to 'data')
        $type = $request->get('type', 'data');


        // Start building the query
        $query = DB::table($reportConfig->base_table);


        // Apply columns
        $select_columns = json_decode($reportConfig->select_columns, true);
        foreach ($select_columns as $column) {
            $query->selectRaw($column['select_exp']);
        }

        // Apply joins
        $joins = json_decode($reportConfig->joins, true);
        if (!empty($joins)) {
            foreach ($joins as $join) {
                if (str_contains($join['table'], '(')) { // Detect subquery joins
                    $query->join(DB::raw($join['table']), $join['on']['first'], $join['on']['operator'], $join['on']['second'], $join['type']);
                } else {
                    $query->join($join['table'], $join['on']['first'], $join['on']['operator'], $join['on']['second'], $join['type']);
                }

            }
        }

        // Apply conditions (filters)
        $conditions = json_decode($reportConfig->conditions, true);
        if (!empty($conditions)) {
            foreach ($conditions as $condition) {
                if (isset($condition['raw'])) {
                    $query->whereRaw($condition['raw']);
                } else {
                    $query->where($condition['column'], $condition['operator'], $condition['value']);
                }

            }
        }







        $columnHeaders = json_decode($reportConfig->column_headers, true);
        $columnConfig = json_decode($reportConfig->columns_config, true);


        //Apply search filters
        $searchTerms = $request->input('searchTerms', []);
        foreach ($searchTerms as $index => $value) {
            $columnHeader = $columnHeaders[$index];
            $config = $columnConfig[$index];
            $columnName = $config['name'];
            $searchType = $columnHeader['searchtype'];
            if ($searchType == 'date' && is_array($value)) {
                if (!empty($value[$index]['start'])) {
                    $query->whereDate($columnName, '>=', $value[$index]['start']);
                }
                if (!empty($value[$index]['end'])) {
                    $query->whereDate($columnName, '<=', $value[$index]['end']);
                }
            } elseif (!empty($value)) {
                switch ($searchType) {
                    case 'text':
                        $query->where($columnName, 'like', '%' . $value[$index] . '%');
                        break;
                    case 'number':
                        $query->where($columnName, '=', (int) $value[$index]);
                        break;
                        case 'dropdown':
                            if($value[$index] != '') {
                                $query->where($columnName, '=', $value[$index]);
                            }
                            break;
                }
            }
        }

        //Get Data for Dropdown header columns
        $dropdownData = new Collection();
        foreach ($columnHeaders as $index => $columnHeader) {
            if($columnHeader['searchtype'] === 'dropdown')
            {
                $ddColumnConfig = $columnConfig[$index];
                $columnSnapshot = (clone $query)
                    ->select($ddColumnConfig['name'])
                    ->distinct()->orderBy($ddColumnConfig['name'])
                    ->pluck($ddColumnConfig['name']);

                $dropdownData[$index] = $columnSnapshot;

            }
        }

        // Apply sorting
        $sorting = $request->order[0] ?? json_decode($reportConfig->default_sorting, true)[0];

        $query->orderBy($sorting['name'], $sorting['dir']);

        // Pass query to DataTables
        return DataTables::of($query)->with(['dropdowndata' => $dropdownData, 'searchterms' => $searchTerms])->make(true);
    }
}