stagfe2/app/Http/Controllers/StatTreeController.php

<?php

namespace App\Http\Controllers;

use App\Models\Pro;
use App\Models\StatTreeLineClauseArg;
use App\Models\StatTreeLineReportColumn;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use App\Models\Clause;
use App\Models\StatTree;
use App\Models\StatTreeLine;
use App\Models\StatTreeLineClause;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Ramsey\Uuid\Uuid;

class StatTreeController extends Controller
{
    public function list()
    {
        $statTrees = StatTree::orderByRaw('is_template DESC NULLS LAST')->orderBy('name')->get();
        return view('app.stat-tree.stat-trees.list', compact('statTrees'));
    }

    public function createPage()
    {
        return view('app.stat-tree.stat-trees.create');
    }
    public function dashboard(StatTree $statTree)
    {
        return view('app.stat-tree.stat-trees.sub.dashboard', compact('statTree'));
    }
    public function dashboard2(StatTree $statTree)
    {
        return view('app.stat-tree.stat-trees.sub.dashboard2', compact('statTree'));
    }

    public function create(Request $request)
    {
        $validatedData = Validator::make($request->all(), [
			'name' => 'required|string|unique:stat_tree',
            'model' => 'required|string',
            'slug' => 'required|string|unique:stat_tree'
		]);

		if ($validatedData->fails()) return $this->fail($validatedData->errors()->first());
        [
            'name' => $name,
            'model' => $model,
            'slug' => $slug
        ] = $request->all();

        $nextId = DB::select("select nextval('stat_tree_id_seq')");
        $statTree = new StatTree; 
        $statTree->id = $nextId[0]->nextval;
        $statTree->uid = Uuid::uuid4();
        $statTree->name = $name;
        $statTree->model = $model;
        $statTree->slug = $slug;

        $statTree->is_template = (bool)$request->input('isTemplate');

        if($request->input('proUid')) {
            $pro = Pro::where('uid', $request->input('proUid'))->first();
            if($pro) {
                $statTree->pro_id = $pro->id;
            }
        }
        $statTree->pro_scope_clause = $request->input('proScopeClause');

        $statTree->save();

        return $this->pass($statTree->uid);
    }

    public function updateBasic(Request $request) {
        $statTree = StatTree::where('uid', $request->input('uid'))->first();
        if(!$statTree) return $this->fail('Stat tree not found!');
        $statTree->name = $request->input('name');
        $statTree->model = $request->input('model');
        $statTree->slug = $request->input('slug');
        $statTree->is_template = (bool)$request->input('isTemplate');
        $statTree->pro_id = NULL;
        if($request->input('proUid')) {
            $pro = Pro::where('uid', $request->input('proUid'))->first();
            if($pro) {
                $statTree->pro_id = $pro->id;
            }
        }
        $statTree->pro_scope_clause = $request->input('proScopeClause');
        $statTree->save();
        return $this->pass();
    }

    public function remove(Request $request) {
        $statTree = StatTree::where('uid', $request->input('uid'))->first();
        if(!$statTree) return $this->pass();
        DB::statement("DELETE FROM stat_tree_line WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg_value WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_report_column WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree WHERE id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        return $this->pass();
    }

    public function instantiate(Request $request, StatTree $statTree) {
        if(!$statTree->is_template) {
            return $this->fail("State tree is not a template!");
        }
        $pro = Pro::where('uid', $request->input('proUid'))->first();
        if(!$pro) {
            return $this->fail("Pro not found!");
        }
        $instance = $statTree->instantiateForPro($pro);
        return $this->pass($instance->uid);
    }

    public function clone(Request $request, StatTree $statTree) {
        $instance = $statTree->clone($request->input('name'));
        return $this->pass($instance->uid);
    }

    private function traverseLines($_lines, &$result) {
        foreach ($_lines as $line) {
            $result[] = $line;
            if(count($line->children)) {
                $this->traverseLines($line->children, $result);
            }
        }
    }

    public function edit(Request $request, StatTree $statTree) {
        $linesFlat = [];
        if($request->input('multi-pro')) {
            $this->traverseLines($statTree->rootLines, $linesFlat);
        }
        return view('app.stat-tree.stat-trees.sub.edit', compact('statTree', 'linesFlat'));
    }

    public function replaceAllLines(Request $request){

        $parents = [];

        $columns = '';

        $statTreeID = (int) $request->get('statTreeID');
        $data = $request->get('data');
        $rows = json_decode($data, true);

        $statTree = StatTree::where('id', $statTreeID)->first();
        $model = $statTree->model;
        DB::beginTransaction();

        // cleanup junk
        DB::statement("DELETE FROM stat_tree_line WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg_value WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_report_column WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);

        for($x = 0; $x < count($rows); $x++){
            $row = $rows[$x];
            $nextStatLineId = DB::select("select nextval('stat_tree_line_id_seq')");
            $statTreeLine = new StatTreeLine;
            $statTreeLine->id = $nextStatLineId[0]->nextval;
            $statTreeLine->uid = Uuid::uuid4();
            $statTreeLine->stat_tree_id = $statTree->id;
            $statTreeLine->tree_order_position_index = $x;
            $statTreeLine->last_refresh_count = null;
            $statTreeLine->tsv_text_for_report_columns = null;
            $statTreeLine->save();

            $allClauses = [];
            for($i = 0; $i < count($row); $i++){
                $cell = $row[$i];
                if(!$cell || empty($cell)) continue;

                $parts = explode('==>', $cell);
                $cell = $parts[0];
                $columns = count($parts) > 1 ? $parts[1] : '';

                $clause = Clause::where('label', $cell)->where('model', 'ilike', $model)->first();
                if(!$clause){
                    DB::rollBack();
                    return $this->fail('No clause record found for ' . $cell);
                }
                $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 = $cell;
                $statTreeLineClause->position_index = $i;
                $statTreeLineClause->detail_json = json_encode(['model' => $model]);
                $statTreeLineClause->stat_tree_id = $statTree->id;
                $statTreeLineClause->save();

                // check if the clause has any clauseArgs, if yes, create stat-tree-line-clause-arg and value records for them
                if(count($clause->clauseArgs)) {
                    foreach ($clause->clauseArgs as $clauseArg) {
                        $statTreeLineClauseArg = new StatTreeLineClauseArg();
                        $nextId = DB::select("select nextval('stat_tree_line_clause_arg_id_seq')");
                        $statTreeLineClauseArg->id = $nextId[0]->nextval;
                        $statTreeLineClauseArg->stat_tree_line_clause_id = $statTreeLineClause->id;
                        $statTreeLineClauseArg->clause_arg_id = $clauseArg->id;
                        $statTreeLineClauseArg->access_level = 'ADMIN';
                        $statTreeLineClauseArg->stat_tree_id = $statTree->id;
                        $statTreeLineClauseArg->value = null;

                        // TODO: need to copy clause arg values from parent node for all except last clause

                        $statTreeLineClauseArg->save();
                    }
                }

                $allClauses[] = $cell;

                // fill report columns (if last clause)
                if($i === count($row) - 1 && !!$columns) {
                    $columns = explode(",", $columns);
                    for ($j = 0; $j < count($columns); $j++) {
                        $parts = explode("|", $columns[$j]);
                        $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 = $parts[1];
                        $column->display_key = $parts[0];
                        $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->stat_tree_id = $statTree->id;
                        $column->save();
                    }
                }

            }
            $parents[implode("|", $allClauses)] = $statTreeLine;

            // if child, find and set parent
            $nonEmpty = [];
            for($i = 0; $i < count($row); $i++){
                if($row[$i] && !empty($row[$i])) $nonEmpty[] = $row[$i];
            }
            if(count($nonEmpty) > 1) {
                $parentClauses = [];
                for($i = 0; $i < count($nonEmpty) - 1; $i++){
                    $cell = $nonEmpty[$i];
                    $parts = explode('==>', $cell);
                    $cell = $parts[0];
                    $columns = count($parts) > 1 ? $parts[1] : '';
                    $clause = Clause::where('label', $cell)->where('model', 'ilike', $model)->first();
                    if(!$clause){
                        DB::rollBack();
                        return $this->fail('No clause record found for ' . $cell);
                    }
                    $parentClauses[] = $clause->label;
                }
                $parentClauses = implode("|", $parentClauses);
                if(@$parents[$parentClauses]) {
                    $statTreeLine->parent_stat_tree_line_id = $parents[$parentClauses]->id;
                    $statTreeLine->save();
                }
            }

        }
        DB::commit();
        return $this->pass();
    }

    public function clausesJSON(Request $request, StatTree $statTree) {
        $clauses = Clause::where('model', $statTree->model)->orderBy('position_index')->get();
        $nodes = [];
        foreach ($clauses as $clause) {

            $children = [];

            // clause text child
            $children[] = [
                "text" => $clause->clause_text,
                "icon" => "fa fa-laptop-code text-primary text-sm",
                "state" => [
                    "opened" => false,
                    "disabled" => false,
                    "selected" => false,
                ],
                "li_attr" => [
                    "type" => "clause_text"
                ],
                "a_attr" => [
                    "title" => $clause->clause_text,
                ],
                "data" => [
                    "type" => "clause_text"
                ]
            ];

            // clause arg children
            $argsSystem = [];
            foreach ($clause->clauseArgs as $clauseArg) {
                $children[] = [
                    "text" => $clauseArg->arg_text . '<span class="text-secondary text-sm ml-2">' . $clauseArg->field_type . '</span>',
                    "icon" => "fa fa-cubes text-info",
                    "state" => [
                        "opened" => true,
                        "disabled" => false,
                        "selected" => false,
                    ],
                    "data" => [
                        "type" => "clause_arg",
                        "id" => $clauseArg->id,
                        "uid" => $clauseArg->uid,
                        "argText" => $clauseArg->arg_text,
                        "fieldType" => $clauseArg->field_type,
                        "clauseId" => $clause->id
                    ]
                ];
                $argsSystem[] = [
                    "arg_text" => $clauseArg->arg_text,
                    "field_type" => $clauseArg->field_type,
                    "value" => null,
                    "access_level" => null
                ];
            }

            $nodes[] = [
                "text" => $clause->label,
                "state" => [
                    "opened" => true,
                    "disabled" => false,
                    "selected" => false,
                ],
                "children" => $children,
                "data" => [
                    "type" => "clause",
                    "id" => $clause->id,
                    "uid" => $clause->uid,
                    "model" => $clause->model,
                    "question" => $clause->question,
                    "answer" => $clause->answer,
                    "label" => $clause->label,
                    "clauseText" => $clause->clause_text,
                    "clauseId" => $clause->id,
                    "args" => $argsSystem
                ],
                "a_attr" => [
                    "title" => $clause->clause_text,
                ],
            ];
        }

        return json_encode($nodes);
    }

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

    private function applyStatTreeLineQueryClauses(StatTreeLine $statTreeLine, $proUid = false)
    {
        $model = $statTreeLine->statTree->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) {
                        $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);
                }
            }

            //dump($clauseText);
            /*$isValid = $this->cleanupClause($clauseText);
            if ($isValid) {*/
                array_push($clauses, $clauseText);
            //}
        }

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

        $query = 'SELECT COUNT(*) FROM '.$model.' WHERE '. implode(" AND ", $clauses);
        try {
            //dump($query);
            $result = DB::select($query);
        }
        catch (\Exception $ex) {
            $result = 'error';
        }
        return $result;
    }

    private function applyStatTreeLineQueryClausesGroupByPros(StatTreeLine $statTreeLine, $proUids)
    {
        $model = $statTreeLine->statTree->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) {
                        $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);
                }
            }

            //dump($clauseText);
            /*$isValid = $this->cleanupClause($clauseText);
            if ($isValid) {*/
            array_push($clauses, $clauseText);
            //}
        }

        // if stat tree bound to a pro, apply pro_scope_clause
        /*if(!$proUid) {
            if($statTreeLine->statTree->pro && $statTreeLine->statTree->pro_scope_clause) {
                $clauses[] = str_replace('@PRO_ID', $statTreeLine->statTree->pro->id, $statTreeLine->statTree->pro_scope_clause);
            }
        }
        else {
            $mvPro = Pro::where('uid', $proUid)->first();
            if($statTreeLine->statTree->pro_scope_clause) {
                $clauses[] = str_replace('@PRO_ID', $mvPro->id, $statTreeLine->statTree->pro_scope_clause);
            }
            $query = 'SELECT COUNT(*) FROM '.$model.' WHERE '. implode(" AND ", $clauses);
        }*/

        $proUids = explode(",", $proUids);
        $proUids = array_map(function($_x) {
            return "'{$_x}'";
        }, $proUids);
        $clauses[] = "_j_pro.uid IN (" . implode(",", $proUids) . ")";
        $proJoinClause = 'JOIN pro _j_pro on ' . str_replace('@PRO_ID', '_j_pro.id', $statTreeLine->statTree->pro_scope_clause);
        $query = "SELECT COUNT(*), _j_pro.uid as _v_pro_uid FROM {$model} {$proJoinClause} WHERE " . implode(" AND ", $clauses) . " GROUP BY _j_pro.uid";
        try {
            //dump($query);
            $result = DB::select($query);
        }
        catch (\Exception $ex) {
            $result = 'error';
        }
        return $result;
    }

    public function linesJSON(Request $request, StatTree $statTree) {

        // refresh counts
        $lines = $statTree->lines;
        foreach ($lines as $line) {
            $query = $this->applyStatTreeLineQueryClauses($line, $request->input('proUid'));
            if ($query && $query !== 'error') {
                $line->last_refresh_count = $query[0]->count;
            }
            else {
                $line->last_refresh_count = -1;
            }
            $line->save();
        }

        $nodes = [];
        foreach ($statTree->rootLines as $rootLine) {
            $nodes[] = $this->lineObject($rootLine);
        }
        return json_encode($nodes);
    }

    public function getCountsForPro(Request $request) {
        $statTreeUid = $request->get('uid');
        if (!$statTreeUid) return $this->fail('No specified stat tree!');
        $statTree = StatTree::where('uid',  $statTreeUid)->first();
        $lines = $statTree->lines;
        $result = [];
        foreach ($lines as $line) {
            $query = $this->applyStatTreeLineQueryClauses($line, $request->input('proUid'));
            if ($query && $query !== 'error') {
                $result[$line->uid] = $query[0]->count;
            }
            else {
                $result[$line->uid] = -1;
            }
        }
        return json_encode($result);
    }

    public function getCountsForPros(Request $request) {
        $statTreeUid = $request->get('uid');
        if (!$statTreeUid) return $this->fail('No specified stat tree!');
        $statTree = StatTree::where('uid',  $statTreeUid)->first();
        $lines = $statTree->lines;
        $result = [];
        foreach ($lines as $line) {
            $query = $this->applyStatTreeLineQueryClausesGroupByPros($line, $request->input('proUids'));
            if ($query && $query !== 'error') {
                $result[$line->uid] = $query;
            }
            else {
                $result[$line->uid] = -1;
            }
        }
        return json_encode($result);
    }

    private function lineObject(StatTreeLine  $line) {

        // columns
        $columns = [];
        foreach ($line->reportColumns as $column) {
            $columns[] = [
                "label" => $column->label,
                "display_key" => $column->display_key,
                "type" => $column->field_type
            ];
        }

        // clause
        $lineClause = $line->displayLineClause();
        $args = [];
        $argsLabel = [];
        foreach($lineClause->lineClauseArgs as $stlcArg) {
            if($stlcArg->clauseArg) {
                $args[] = [
                    "arg_text" => $stlcArg->clauseArg->arg_text,
                    "field_type" => $stlcArg->clauseArg->field_type,
                    "value" => $stlcArg->value,
                    "access_level" => $stlcArg->access_level
                ];
                $argsLabel[] = $stlcArg->clauseArg->arg_text . ': ' . $stlcArg->value;
            }
        }
        $clause = [
            "clause_id" => $lineClause->clause_id,
            "clause_label" => $lineClause->clause_label,
            "position_index" => $lineClause->position_index,
            "args" => $args
        ];

        $children = [];
        foreach ($line->children as $child) {
            $children[] = $this->lineObject($child);
        }
        return [
            "text" => '<span class="stat-tree-anchor">' . $line->displayLabel() . '</span>' .
                (count($argsLabel) ? '<span class="text-info text-sm ml-2">[' . implode(', ', $argsLabel) . ']</span>' : '') .
                '<span class="ml-2 text-secondary line-count-label">(' .
                (is_null($line->last_refresh_count) ? '…' :
                    ($line->last_refresh_count === -1 ?
                        '<span class="text-danger text-sm font-weight-bold"><i class="fa fa-exclamation-triangle"></i> Query error / missing arg values</span>' :
                        $line->last_refresh_count)) .
                ')</span>',
            "state" => [
                "opened" => true,
                "disabled" => false,
                "selected" => false,
            ],
            "children" => $children,
            "data" => [
                "type" => "stat_tree_line",
                "id" => $line->id,
                "uid" => $line->uid,
                "displayLabel" => $line->displayLabel(),
                "extendedLabel" => $line->displayLabel() . (count($argsLabel) ? ' [' . implode(', ', $argsLabel) . ']' : ''),
                "lastRefreshCount" => $line->last_refresh_count,
                "treeOrderPositionIndex" => $line->tree_order_position_index,
                "columns" => $columns,
                "clause" => $clause
            ],
            "a_attr" => [
                "title" => $line->displayLineClause()->clause->clause_text
            ]
        ];
    }

    public function replaceAllLinesJSON(Request $request) {

        $statTree = StatTree::where('uid', $request->get('uid'))->first();
        if(!$statTree) return $this->fail("Stat tree does not exist!");

        $lines = json_decode($request->get('data'));

        DB::beginTransaction();

        // cleanup junk
        DB::statement("DELETE FROM stat_tree_line WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_clause_arg_value WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);
        DB::statement("DELETE FROM stat_tree_line_report_column WHERE stat_tree_id = :stat_tree_id", ['stat_tree_id' => $statTree->id]);

        // process
        for ($i=0; $i<count($lines); $i++) {
            $result = $this->saveStatTreeLine($lines[$i], $i, null, $statTree);
            if($result !== TRUE) {
                DB::rollBack();
                return $result;
            }
        }

        DB::commit();
        return $this->pass();
    }

    private function saveStatTreeLine($line, $position, $parentLine, $statTree) {

        // saved tree line
        $nextId = DB::select("select nextval('stat_tree_line_id_seq')");
        $statTreeLine = new StatTreeLine;
        $statTreeLine->id = $nextId[0]->nextval;
        $statTreeLine->uid = Uuid::uuid4();
        $statTreeLine->stat_tree_id = $statTree->id;
        $statTreeLine->tree_order_position_index = $position;
        $statTreeLine->last_refresh_count = null;
        $statTreeLine->tsv_text_for_report_columns = null;
        $statTreeLine->parent_stat_tree_line_id = $parentLine ? $parentLine->id : null;
        $statTreeLine->save();

        // -- clauses -- START

        // copy parent line clauses
        $maxParentClausePositionIndex = 0;
        if($parentLine && count($parentLine->lineClauses)) {
            foreach ($parentLine->lineClauses as $parentLineClause) {
                $nextId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
                $statTreeLineClause = new StatTreeLineClause();
                $statTreeLineClause->id = $nextId[0]->nextval;
                $statTreeLineClause->uid = Uuid::uuid4();
                $statTreeLineClause->stat_tree_line_id = $statTreeLine->id; // this stat tree line
                $statTreeLineClause->clause_id = $parentLineClause->clause_id;
                $statTreeLineClause->clause_label = $parentLineClause->clause_label;
                $statTreeLineClause->position_index = $parentLineClause->position_index;
                if($statTreeLineClause->position_index > $maxParentClausePositionIndex) {
                    $maxParentClausePositionIndex = $statTreeLineClause->position_index;
                }
                $statTreeLineClause->stat_tree_id = $statTree->id;
                $statTreeLineClause->save();

                // copy clause args for parent line clauses
                foreach ($parentLineClause->lineClauseArgs as $parentLineClauseArg) {
                    $statTreeLineClauseArg = new StatTreeLineClauseArg();
                    $nextId = DB::select("select nextval('stat_tree_line_clause_arg_id_seq')");
                    $statTreeLineClauseArg->id = $nextId[0]->nextval;
                    $statTreeLineClauseArg->stat_tree_line_clause_id = $statTreeLineClause->id; // this line cause
                    $statTreeLineClauseArg->clause_arg_id = $parentLineClauseArg->clause_arg_id;
                    $statTreeLineClauseArg->value = $parentLineClauseArg->value;
                    $statTreeLineClauseArg->access_level = $parentLineClauseArg->access_level;
                    $statTreeLineClauseArg->stat_tree_id = $statTree->id;
                    $statTreeLineClauseArg->save();
                }
            }
        }

        // create own line clause (from clause.clause_label)
        $clause = Clause::where('label', $line->clause->clause_label)->where('model', 'ilike', $statTree->model)->first();
        if(!$clause) {
            return $this->fail('No clause record found for ' . $line->clause->clause_label);
        }
        $nextId = DB::select("select nextval('stat_tree_line_clause_id_seq')");
        $statTreeLineClause = new StatTreeLineClause();
        $statTreeLineClause->id = $nextId[0]->nextval;
        $statTreeLineClause->uid = Uuid::uuid4();
        $statTreeLineClause->stat_tree_line_id = $statTreeLine->id; // this stat tree line
        $statTreeLineClause->clause_id = $clause->id;
        $statTreeLineClause->clause_label = $line->clause->clause_label;
        $statTreeLineClause->position_index = $maxParentClausePositionIndex + 1;
        $statTreeLineClause->stat_tree_id = $statTree->id;
        $statTreeLineClause->save();

        // create args for own line clause
        foreach ($clause->clauseArgs as $clauseArg) {
            $statTreeLineClauseArg = new StatTreeLineClauseArg();
            $nextId = DB::select("select nextval('stat_tree_line_clause_arg_id_seq')");
            $statTreeLineClauseArg->id = $nextId[0]->nextval;
            $statTreeLineClauseArg->stat_tree_line_clause_id = $statTreeLineClause->id;
            $statTreeLineClauseArg->clause_arg_id = $clauseArg->id;
            $statTreeLineClauseArg->value = null;
            $statTreeLineClauseArg->access_level = 'ADMIN';
            for ($j=0; $j<count($line->clause->args); $j++) { // find the arg matching text and type from line->args and use that
                if($line->clause->args[$j]->arg_text === $clauseArg->arg_text &&
                    $line->clause->args[$j]->field_type === $clauseArg->field_type) {
                    $statTreeLineClauseArg->value = $line->clause->args[$j]->value;
                    $statTreeLineClauseArg->access_level = $line->clause->args[$j]->access_level;
                    break;
                }
            }
            $statTreeLineClauseArg->stat_tree_id = $statTree->id;
            $statTreeLineClauseArg->save();
        }

        // -- clauses -- END


        // columns
        for ($i=0; $i<count($line->columns); $i++) {
            $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 = $line->columns[$i]->label;
            $column->display_key = $line->columns[$i]->display_key;
            $column->field_type = $line->columns[$i]->type;
            $column->position_index = $i;
            $column->stat_tree_id = $statTree->id;
            $column->save();
        }

        // child lines
        for ($i=0; $i<count($line->children); $i++) {
            $result = $this->saveStatTreeLine($line->children[$i], $i, $statTreeLine, $statTree);
            if($result !== TRUE) {
                DB::rollBack();
                return $result;
            }
        }

        return TRUE;
    }

    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 && $query !== 'error') {
                $line->last_refresh_count = $query[0]->count;
            }
            else {
                $line->last_refresh_count = -1;
            }
            $line->save();
        }

        return $this->pass();
    }
}