stagfe2/app/Http/Controllers/StatTreeController.php

<?php

namespace App\Http\Controllers;

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::all();
        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->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->save();
        return $this->pass();
    }

    public function edit(Request $request, StatTree $statTree) {
        $clauses = Clause::where('model', $statTree->model)->orderBy('position_index')->get();
        return view('app.stat-tree.stat-trees.sub.edit', compact('statTree', 'clauses'));
    }

    public function delete(Request $request){
        $request->validate([
            'id' => 'required'
        ]);
        $id = $request->get('id');
        $statTree = StatTree::where('id', $id)->first();
        
    }

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