stagfe2/app/Http/Controllers/StatTreeLineController.php

<?php

namespace App\Http\Controllers;


use App\Models\Clause;
use App\Models\StatTreeLineClause;
use App\Models\StatTreeLineReportColumn;
use Illuminate\Http\Request;
use App\Models\StatTree;
use App\Models\StatTreeLine;
use App\Models\Client;
use App\Models\Pro;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\DB;
use Ramsey\Uuid\Uuid;

class StatTreeLineController extends Controller
{
    public function reports()
    {
        $statTreeLines = StatTreeLine::whereNull('stat_tree_id')->get();
        return view('app.stat-tree.stat-tree-line-reports.list', compact('statTreeLines'));
    }

    public function editReport(Request $request, StatTreeLine $statTreeLine) {
        $allClauses = Clause::orderByRaw('position_index ASC NULLS FIRST')->get();
        return view('app.stat-tree.stat-tree-line-reports.edit', compact('statTreeLine', 'allClauses'));
    }

    public function viewReport(Request $request, StatTreeLine $statTreeLine) {
        $total = 0;
        $rows = [];
        $columns = [];
        $selectColumns = [];
        $paginator = null;
        $line = $statTreeLine;
        foreach ($line->reportColumns as $reportColumn) {
            $column = [
                "label" => $reportColumn->label,
                "column" => $reportColumn->display_key,
                "type" => $reportColumn->field_type,
                "as" => "v_{$reportColumn->id}"
            ];
            $selectColumns[] = "{$reportColumn->display_key} as v_{$reportColumn->id}";
            if($reportColumn->record_route_name) {
                $column["url"] = "v_{$reportColumn->id}_url";
                $selectColumns[] = "{$reportColumn->record_route_name} as v_{$reportColumn->id}_url";
            }
            $columns[] = $column;
        }
        if(count($line->reportColumns)) {
            $result = self::queryStatTreeLineData($line, $selectColumns, $columns, $request);
            if($result[0] === false) {
                $total = 0;
                $rows = [];
                $paginator = new Paginator($rows, 20, 1);
                $error = $result[1];
            }
            else {
                $total = $result[0];
                $rows = $result[1];
                $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: 20, $request->input('page') ?: 1);
                $paginator->setPath(route('practice-management.statTreeLines.view-data', compact('line')));
                $error = '';
            }
        }
        return view('app.stat-tree.stat-tree-line-reports.view-data', compact('line', 'total', 'rows', 'columns', 'paginator', 'error'));
    }


    public function list()
    {
        $statTreeLines = StatTreeLine::all();
        return view('app.stat-tree.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 columnSuggest(Request $request) {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $table = $request->input('table') ? trim($request->input('table')) : '';
        if (empty($table)) return '';
        $table = strtolower($table);

        $tables = [];
        $columns = [];

        // if single-term without spaces, single table
        if(strpos($table, ' ') === FALSE) {
            $tables[] = $table;
        }
        else {
            $table = explode(" ", $table);
            $tables[] = $table[0];
            for ($i = 1; $i < count($table); $i++) {
                if($table[$i] == 'join' && $i < count($table) - 1) {
                    $tables[] = $table[$i+1];
                    $i++;
                }
            }
        }

        for ($i = 0; $i < count($tables); $i++) {
            $cols = DB::getSchemaBuilder()->getColumnListing($tables[$i]);
            sort($cols);
            $matches = array_filter($cols, function($_x) use ($term) {
                return strpos($_x, $term) !== FALSE;
            });
            $matches = array_map(function($_x) use ($tables, $i) {
                return [
                    "text" => $tables[$i] . '.' . $_x,
                    "label" => sanitize_state_name($_x),
                    "type" => DB::getSchemaBuilder()->getColumnType($tables[$i], $_x)
                ];
            }, $matches);
            $columns = array_merge($columns, $matches);
        }

        $columns = array_values($columns);
        $columns = json_decode(json_encode($columns));
        // dd($matches);
        return json_encode([
            "success" => true,
            "data" => $columns
        ]);
    }

    public function viewData(Request $request, StatTreeLine $line) {
        $total = 0;
        $rows = [];
        $columns = [];
        $selectColumns = [];
        $paginator = null;
        foreach ($line->reportColumns as $reportColumn) {
            $columns[] = [
                "label" => $reportColumn->label,
                "column" => $reportColumn->display_key,
                "type" => $reportColumn->field_type,
                "as" => "v_{$reportColumn->id}"
            ];
            $selectColumns[] = "{$reportColumn->display_key} as v_{$reportColumn->id}";
        }
        if(count($line->reportColumns)) {
            $result = self::queryStatTreeLineData($line, $selectColumns, $columns, $request);
            $total = $result[0];
            $rows = $result[1];
            $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: 20, $request->input('page') ?: 1);
            $paginator->setPath(route('practice-management.statTreeLines.view-data', compact('line')));
        }
        return view('app.stat-tree.stat-tree-lines.view-data', compact('line', 'total', 'rows', 'columns', 'paginator'));
    }

    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);
    }

    public static function queryStatTreeLineData(StatTreeLine $statTreeLine, $selectColumns, $columns, Request $request)
    {

        $model = $statTreeLine->statTree ? $statTreeLine->statTree->model : $statTreeLine->model;

        $clauses = [];
        foreach ($statTreeLine->lineClauses as $lineClause) {
            $clauseText = $lineClause->clause->clause_text;

            // apply arg values
            foreach ($lineClause->clause->clauseArgs as $clauseArg) {

                $value = null;
                foreach ($lineClause->lineClauseArgs as $lineClauseArg) {
                    if($lineClauseArg->clause_arg_id === $clauseArg->id) {

                        // if there is a query-line override, give that priority
                        if($request->input('st_' . $clauseArg->arg_text)) {
                            $lineClauseArg->value = $request->input('st_' . $clauseArg->arg_text);
                        }

                        $value = $lineClauseArg->value;
                    }
                }

                if(!is_null($value)) {
                    $clauseText = str_replace(
                        ':' . $clauseArg->arg_text,                         // search for :xxx
                        "'" . $value . "'::" . $clauseArg->field_type,      // replace with '$value'::$field_type
                        $clauseText);
                }
            }

            array_push($clauses, $clauseText);

        }

        // if stat tree bound to a pro, apply pro_scope_clause
        if($statTreeLine->statTree) {
            if($request->input('proUid') && $statTreeLine->statTree->pro_scope_clause) {
                $pro = Pro::where('uid', $request->input('proUid'))->first();
                if($pro) {
                    $clauses[] = str_replace('@PRO_ID', $pro->id, $statTreeLine->statTree->pro_scope_clause);
                }
            }
            elseif($statTreeLine->statTree->pro && $statTreeLine->statTree->pro_scope_clause) {
                $clauses[] = str_replace('@PRO_ID', $statTreeLine->statTree->pro->id, $statTreeLine->statTree->pro_scope_clause);
            }
        }

        // filters from view-data UI
        foreach ($columns as $column) {
            if($request->input($column['as'] . '_op')) {
                switch($column['type']) {
                    case 'integer':
                    case 'bigint':
                    case 'decimal':
                    case 'bool':
                    case 'boolean':
                        if($request->input($column['as'] . '_value')) {
                            $clauses[] = "{$column['column']} " . $request->input($column['as'] . '_op') . ' ' . $request->input($column['as'] . '_value');
                        }
                        break;
                    case 'string':
                    case 'text':
                    case 'varchar':
                        if($request->input($column['as'] . '_value')) {
                            if($request->input($column['as'] . '_op') === '=' || $request->input($column['as'] . '_op') === '!=') {
                                $clauses[] = "{$column['column']} " . $request->input($column['as'] . '_op') . ' ' . $request->input($column['as'] . '_value');
                            }
                            elseif($request->input($column['as'] . '_op') === 'ILIKE' || $request->input($column['as'] . '_op') === 'NOT ILIKE') {
                                $clauses[] = "{$column['column']} " . $request->input($column['as'] . '_op') . ' ' . "'%" . $request->input($column['as'] . '_value') . "%'";
                            }
                        }
                        break;
                    case 'date':
                    case 'datetime':
                        if($request->input($column['as'] . '_value_start')) {
                            $clauses[] = "{$column['column']} >= " . "'" . $request->input($column['as'] . '_value_start') . "'";
                        }
                        if($request->input($column['as'] . '_value_end')) {
                            $clauses[] = "{$column['column']} <= " . "'" . $request->input($column['as'] . '_value_end') . "'";
                        }
                        break;
                }
            }
        }

        $result = null;

        // get count for paginator
        try {
            $query = 'SELECT COUNT(*) FROM '.$model.' WHERE '. implode(" AND ", $clauses);
            $result = DB::select($query);
            $total = $result[0]->count;

            $page = $request->input('page') ?: 1;
            $perPage = $request->input('per_page') ?: 20;
            $offset = ($page - 1) * $perPage;
            $sort = '';
            if($request->input('sort_by') && $request->input('sort_by')) {
                $sort = "ORDER BY " . $request->input('sort_by') . " " . $request->input('sort_dir') . " NULLS LAST";
            }
            $query = 'SELECT ' . implode(", ", $selectColumns) . ' FROM '.$model.' WHERE '. implode(" AND ", $clauses) . " {$sort} OFFSET {$offset} LIMIT {$perPage}";
            $result = DB::select($query);
            $result = [$total, $result];
        }
        catch (\Exception $ex) {
            $result = [false, $ex->getMessage()];
        }
        return $result;
    }

    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;
    }

    // eps
    public function create(Request $request) {

        // stat tree line
        $statTreeLine = new StatTreeLine();
        $nextId = DB::select("select nextval('stat_tree_line_id_seq')");
        $statTreeLine->id = $nextId[0]->nextval;
        $statTreeLine->uid = Uuid::uuid4();
        $statTreeLine->stat_tree_id = $request->input('statTreeId');
        $statTreeLine->parent_stat_tree_line_id = $request->input('parentStatTreeLineId');
        $positionIndex = DB::select("select max(tree_order_position_index) from stat_tree_line where stat_tree_id = {$statTreeLine->stat_tree_id}" .
            ($statTreeLine->parent_stat_tree_line_id ? " AND parent_stat_tree_line_id = {$statTreeLine->parent_stat_tree_line_id}" : '')
        );
        $statTreeLine->tree_order_position_index = is_numeric($positionIndex[0]->max) ? $positionIndex[0]->max + 1 : 1;
        $statTreeLine->save();

        // stat tree line clauses (all of parent's clauses + own)
        if($statTreeLine->parent) {
            foreach($statTreeLine->parent->lineClauses as $lineClause) {
                $statTreeLineClause = new StatTreeLineClause();
                $nextId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
                $statTreeLineClause->id = $nextId[0]->nextval;
                $statTreeLineClause->uid = Uuid::uuid4();
                $statTreeLineClause->stat_tree_line_id = $statTreeLine->id;
                $statTreeLineClause->clause_id = $lineClause->clause_id;
                $statTreeLineClause->clause_label = $lineClause->clause_label;
                $statTreeLineClause->save();
            }
        }
        $statTreeLineClause = new StatTreeLineClause();
        $nextId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
        $statTreeLineClause->id = $nextId[0]->nextval;
        $statTreeLineClause->uid = Uuid::uuid4();
        $statTreeLineClause->stat_tree_line_id = $statTreeLine->id;
        $clause = Clause::where('id', $request->input('clauseId'))->first();
        $statTreeLineClause->clause_id = $clause->id;
        $statTreeLineClause->clause_label = $clause->label;
        $statTreeLineClause->save();

        return $this->pass();
    }
    public function remove(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        // TODO: disallow if this has children
        DB::select("delete from stat_tree_line where id = {$statTreeLine->id}");
        return $this->pass();
    }

    // eps for reports
    public function createReport(Request $request) {
        $statTreeLine = new StatTreeLine();
        $nextId = DB::select("select nextval('stat_tree_line_id_seq')");
        $statTreeLine->id = $nextId[0]->nextval;
        $statTreeLine->uid = Uuid::uuid4();
        $statTreeLine->title = $request->input('title');
        $statTreeLine->created_at = date('Y-m-d h:i:s');
        $statTreeLine->save();
        return $this->pass();
    }
    public function removeReport(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        // TODO: disallow if this has children
        DB::select("delete from stat_tree_line where id = {$statTreeLine->id}");
        return $this->pass();
    }
    public function updateTitle(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        $statTreeLine->title = $request->input('title');
        $statTreeLine->save();
        return $this->pass();
    }
    public function updateModel(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        $statTreeLine->model = $request->input('model');
        $statTreeLine->save();
        return $this->pass();
    }
    public function addExistingClause(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        $clause = Clause::where('uid', $request->input('clauseUid'))->first();
        if(!$clause) return $this->fail('Clause not found!');

        $nextStatLineClauseId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
        $statTreeLineClause = new StatTreeLineClause;
        $statTreeLineClause->id = $nextStatLineClauseId[0]->nextval;
        $statTreeLineClause->uid = Uuid::uuid4();
        $statTreeLineClause->stat_tree_line_id = $statTreeLine->id;
        $statTreeLineClause->clause_id = $clause->id;
        $statTreeLineClause->clause_label = $clause->label;
        $positionIndex = DB::select("select max(position_index) from stat_tree_line_clause where stat_tree_line_id = {$statTreeLine->id}");
        $statTreeLineClause->position_index = is_numeric($positionIndex[0]->max) ? $positionIndex[0]->max + 1 : 1;
        $statTreeLineClause->save();

        return $this->pass();
    }
    public function addNewClause(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');

        $clauseId = DB::select("select nextval('clause_id_seq')");
        $clause = new Clause();
        $clause->id = $clauseId[0]->nextval;
        $clause->uid = Uuid::uuid4();
        $clause->model = $request->input('model');
        $clause->question = $request->input('question');
        $clause->answer = $request->input('answer');
        $clause->label = $request->input('label');
        $clause->clause_text = $request->input('clauseText');
        $positionIndex = DB::select("select max(position_index) from clause");
        $clause->position_index = is_numeric($positionIndex[0]->max) ? $positionIndex[0]->max + 1 : 1;
        $clause->save();

        $nextStatLineClauseId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
        $statTreeLineClause = new StatTreeLineClause;
        $statTreeLineClause->id = $nextStatLineClauseId[0]->nextval;
        $statTreeLineClause->uid = Uuid::uuid4();
        $statTreeLineClause->stat_tree_line_id = $statTreeLine->id;
        $statTreeLineClause->clause_id = $clause->id;
        $statTreeLineClause->clause_label = $clause->label;
        $positionIndex = DB::select("select max(position_index) from stat_tree_line_clause where stat_tree_line_id = {$statTreeLine->id}");
        $statTreeLineClause->position_index = is_numeric($positionIndex[0]->max) ? $positionIndex[0]->max + 1 : 1;
        $statTreeLineClause->save();

        return $this->pass();
    }
    public function removeClause(Request $request) {
        $statTreeLineClause = StatTreeLineClause::where('uid', $request->input('uid'))->first();
        if(!$statTreeLineClause) return $this->fail('Stat tree line not found!');
        DB::select("delete from stat_tree_line_clause where id = {$statTreeLineClause->id}");
        return $this->pass();
    }
    public function addReportColumn(Request $request) {
        $statTreeLine = StatTreeLine::where('uid', $request->input('uid'))->first();
        if(!$statTreeLine) return $this->fail('Stat tree line not found!');
        $column = new StatTreeLineReportColumn();
        $nextId = DB::select("select nextval('stat_tree_line_report_column_id_seq')");
        $column->id = $nextId[0]->nextval;
        $column->uid = Uuid::uuid4();
        $column->stat_tree_line_id = $statTreeLine->id;
        $column->label = $request->input('label');
        $column->display_key = $request->input('column');
        $positionIndex = DB::select("select max(position_index) from stat_tree_line_report_column where stat_tree_line_id = {$column->stat_tree_line_id}");
        $column->position_index = is_numeric($positionIndex[0]->max) ? $positionIndex[0]->max + 1 : 1;
        $column->field_type = $request->input('fieldType');
        $column->save();
        return $this->pass();
    }
    public function updateReportColumn(Request $request) {
        $column = StatTreeLineReportColumn::where('uid', $request->input('uid'))->first();
        if(!$column) return $this->fail('Stat tree line report column not found!');
        $column->label = $request->input('label');
        $column->display_key = $request->input('column');
        $column->field_type = $request->input('fieldType');
        $column->record_route_name = $request->input('route');
        $column->save();
        return $this->pass();
    }
    public function removeReportColumn(Request $request) {
        $column = StatTreeLineReportColumn::where('uid', $request->input('uid'))->first();
        if(!$column) return $this->fail('Stat tree line report column not found!');
        DB::select("delete from stat_tree_line_report_column where id = {$column->id}");
        return $this->pass();
    }
    public function reorderReportColumns(Request $request) {
        $uids = json_decode($request->input('uids'));
        $position = 1;
        for ($i = 0; $i < count($uids); $i++) {
            $column = StatTreeLineReportColumn::where('uid', $uids[$i])->first();
            if($column) {
                $column->position_index = $position;
                $column->save();
                $position++;
            }
        }
        return $this->pass();
    }
}