stagfe2/app/Http/Controllers/StatTreeLineController.php

<?php

namespace App\Http\Controllers;


use Illuminate\Http\Request;
use App\Models\StatTree;
use App\Models\StatTreeLine;
use App\Models\Client;
use App\Models\Pro;
use Illuminate\Support\Facades\DB;

class StatTreeLineController extends Controller
{
    public function list()
    {
        $statTreeLines = StatTreeLine::all();
        return view('app.stat-tree-lines.list', compact('statTreeLines'));
    }
    public function dashboard(StatTreeLine $statTreeLine)
    {
        $result = $this->applyStatTreeLineQueryClauses($statTreeLine);
        if (!$result) {
            $response = 'Invalid query or model/table name';
        } else {
            $response = [
                'type' => 'count',
                'sql' => $result->toSql(),
                'result' => $result->count()
            ];
        }

        return view('app.stat-tree.stat-tree-lines.single', compact('statTreeLine', 'response'));
    }

    public function refreshTreeCountQueries(Request $request)
    {
        $statTreeID = $request->get('statTreeID');
        if (!$statTreeID) return $this->fail('No specified stat tree!');

        $statTree = StatTree::where('id', $statTreeID)->first();
        if (!$statTree) return $this->fail('Invalid stat tree!');
        $lines = $statTree->lines;
        foreach ($lines as $line) {
            $query = $this->applyStatTreeLineQueryClauses($line);
            if ($query) {
                $line->last_refresh_count = $query[0]->count;
                $line->save();
            }
        }

        return $this->pass();
    }

    public function refreshCountQuery(Request $request)
    {
        $statTreeLineID = $request->get('statTreeLineID');
        if (!$statTreeLineID) return $this->fail('No specified stat tree line!');

        $statTreeLine = StatTreeLine::where('id', $statTreeLineID)->first();
        if (!$statTreeLine) return $this->fail('Invalid stat tree line!');
        $query = $this->applyStatTreeLineQueryClauses($statTreeLine);  
        if ($query) {
            $statTreeLine->last_refresh_count = $query[0]->count;
            $statTreeLine->save();
            return $this->pass($statTreeLine->last_refresh_count);
        }else{
            return $this->fail('Invalid query or model/table name');
        }
    }

    protected function applyStatTreeLineQueryClauses(StatTreeLine $statTreeLine)
    {

        $model = $statTreeLine->statTree->model;
        // $query = null;
        // if (strcasecmp($model, 'client') == 0) {
        //     $query = Client::query();
        // }
        // if (strcasecmp($model, 'pro') == 0) {
        //     $query = Pro::query();
        // }
        // if (!$query) return null;




        $clauses = [];
        foreach ($statTreeLine->lineClauses as $lineClause) {
            $clauseText = $lineClause->clause->clause_text;
            $isValid = $this->cleanupClause($clauseText);
            if ($isValid) {
                array_push($clauses, $clauseText);
            }
        }

        $query = 'SELECT COUNT(*) FROM '.$model.' WHERE '. implode(" AND ", $clauses);
        
        return DB::select($query);
    }

    protected function cleanupClause($clauseText)
    {
        //Dont include empty clauses, i.e ()
        preg_match('#\((.*?)\)#', $clauseText, $match);
        $content = @$match[1];
        if (!$content || empty($content)) return null;
        return $content;
    }
}