surveyor_indonesia_backend/app/Http/Controllers/DashboardBoDController.php

<?php

namespace App\Http\Controllers;

use App\Models\User;
use App\Models\Divisi;
use App\Models\Project;
use App\Models\ProjectPhase;
use App\Models\ProjectExpenditure;
use App\Models\UserToVersionGantt;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\ProjectScheduleHealth;
use App\Helpers\MasterFunctionsHelper;
use App\Models\ProjectFinancialHealth;
use App\Models\ProjectInvoice;
use Illuminate\Support\Facades\Artisan;

class DashboardBoDController extends Controller
{
	private function interpolateYear($year)
	{
		if ($year)
			$year = '%' . $year . '%';
		return $year;
	}



	public function getCompanyCashFlow($all_project, $hierarchy, $role_name)
	{
		$totalExpenditure = $totalInvoice = $totalPaidInvoice = 0;

        $totalBudgets = [];
        $projects = [];
        $role = urldecode($role_name);

        if ($all_project) {
            $totalBudgets = Project::select(
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(rencana_biaya, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_rencana_biaya'),
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(value_proyek, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_value_proyek'),
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(income_year, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_income_year')
            )->first();
            $projects = Project::get();
            // Log::info(['dataTotal' => $totalBudgets]);
        } else {
            $totalBudgets = Project::where('created_by_id', $hierarchy)
            ->select(
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(rencana_biaya, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_rencana_biaya'),
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(value_proyek, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_value_proyek'),
                DB::raw('SUM(CAST(COALESCE(NULLIF(REPLACE(income_year, \'.\', \'\'), \'\'), \'0\') AS DOUBLE PRECISION)) AS total_income_year')
            )->first();
            $projects = Project::where('created_by_id', $hierarchy)->get();
        }

        foreach ($projects as $project) {
            $project->expenses = 0;
            if ($project->kode_sortname != "") {
                $cost = 0;
                $totalExpenditure = 0;
                $totalInvoice = 0;
                $totalPaidInvoice = 0;
            }
        }

        return response()->json([
            'data' => [
                'total_budget' => (int) ($totalBudgets->total_rencana_biaya ?? 0),
                'total_expenditure' => $totalExpenditure,
                'total_invoice' => $totalInvoice,
                'total_paid_invoice' => $totalPaidInvoice,
                'total_value_proyek' => $totalBudgets->total_value_proyek ?? 0,
                'total_income_year' => $totalBudgets->total_income_year ?? 0
            ]
        ], 200);
	}

	public function getDetailExpenditureColor($role_name)
	{
		$role = urldecode($role_name);
		$query = null;
		$query = ProjectExpenditure::get();

		$data = [];
		foreach ($query as $value) {
			if ($value['name'] === 'Total Budget') {
				$data['total_budget'] = $value['color'];
			}
			if ($value['name'] === 'Expenditure') {
				$data['total_expenditure'] = $value['color'];
			}
			if ($value['name'] === 'Invoice') {
				$data['total_invoice'] = $value['color'];
			}
			if ($value['name'] === 'Cash In') {
				$data['total_paid_invoice'] = $value['color'];
			}
		}
		return response()->json([
			'data' => [
				'total_budget' => $data['total_budget'] ?? '',
				'total_expenditure' => $data['total_expenditure'] ?? '',
				'total_invoice' => $data['total_invoice'] ?? '',
				'total_paid_invoice' => $data['total_paid_invoice'] ?? '',
			]
		], 200);
	}

	public function getDetailFinancialHealthColor($role_name)
	{
		$role = urldecode($role_name);
		$query = null;
		$query = ProjectFinancialHealth::get();

		$data = [];
		foreach ($query as $value) {
			if ($value['name'] === 'Cost Overrun') {
				$data['overrun'] = $value['color'];
			}
			if ($value['name'] === 'Early Warning') {
				$data['warning'] = $value['color'];
			}
			if ($value['name'] === 'On Budget') {
				$data['on-budget'] = $value['color'];
			}
		}
		return response()->json([
			'data' => [
				'overrun' => $data['overrun'] ?? '',
				'warning' => $data['warning'] ?? '',
				'on-budget' => $data['on-budget'] ?? '',
			]
		], 200);
	}

	public function getDetailScheduleHealthColor($role_name)
	{
		$role = urldecode($role_name);
		$query = null;
		$query = ProjectScheduleHealth::get();

		$data = [];
		foreach ($query as $value) {
			if ($value['name'] === 'Behind Schedule') {
				$data['behind-schedule'] = $value['color'];
			}
			if ($value['name'] === 'Early Warning') {
				$data['warning'] = $value['color'];
			}
			if ($value['name'] === 'On Schedule') {
				$data['on-schedule'] = $value['color'];
			}
		}
		return response()->json([
			'data' => [
				'behind-schedule' => $data['behind-schedule'] ?? '',
				'warning' => $data['warning'] ?? '',
				'on-schedule' => $data['on-schedule'] ?? '',
			]
		], 200);
	}

	public function getDetailInvoiceColor($role_name)
	{
		$role = urldecode($role_name);
		$query = null;
		$query = ProjectInvoice::get();
		$data = [];
		foreach ($query as $value) {
			if ($value['name'] === 'Invoiced') {
				$data['invoiced'] = $value['color'];
			}
			if ($value['name'] === 'Cash In') {
				$data['paid'] = $value['color'];
			}
		}
		return response()->json([
			'data' => [
				'invoiced' => $data['invoiced'] ?? '',
				'paid' => $data['paid'] ?? ''
			]
		], 200);
	}

	// integrasi
	public function getInvoiceOutstanding($role_name, $all_project, $hierarchy)
	{
		$role = urldecode($role_name);
		$projects = null;
		if (!empty($all_project) || $role === 'Super Admin') {
			$projects = Project::get();
		} else {
			$projects = Project::where('created_by_id', $hierarchy)
				->get();
		}

		$return = [];
		foreach ($projects as $project) {
			if ($project->kode_sortname != "") {
				array_push($return, [
					'project' => $project->nama,
					'project_code' => $project->kode_sortname,
					'invoiced' =>  0,
					'paid' => 0,
				]);
			}
		}

		return response()->json([
			'data' => $return
		], 200);
	}

	public function getTotalProjectPerScheduleHealth($role_name, $all_project, $hierarchy)
	{
		$role = urldecode($role_name);
		$replaceHierarchy = preg_replace('/[\[\]]/', '', $hierarchy);

		$return = [
			'behind-schedule' => 0,
			'warning' => 0,
			'on-schedule' => 0,
		];

		$projects = null;
		if ($all_project == 'true' || $role === 'Super Admin') {
			$projects = Project::get();
		} else {
			$projects = Project::where('created_by_id', $replaceHierarchy)
				->get();
		}

		$return['behind-schedule'] = $projects->where('schedule_health', 'behind-schedule')->count();
		$return['warning'] = $projects->where('schedule_health', 'warning')->count();
		$return['on-schedule'] = $projects->where('schedule_health', 'on-schedule')->count();

		return response()->json(['data' => $return], 200);

		// Panggil Artisan command
		Artisan::call('calculate:ScheduleHealth');
	}


	public function getTotalProjectScheduleHealthPerDivision($role_name)
	{
		$role = urldecode($role_name);
		$divisions = Divisi::whereNull('parent')
			->get();

		foreach ($divisions as $index => $division) {
			$behindSchedule = $warning = $onSchedule = 0;

			$projects = Project::where('divisi_id', $division->id)->get();
			foreach ($projects as $project) {
				if ($project->schedule_health == 'warning') {
					$warning++;
				} elseif ($project->schedule_health == 'on-schedule') {
					$onSchedule++;
				} elseif ($project->schedule_health == 'behind-schedule') {
					$behindSchedule++;
				}
			}

			$division->scheduleData = [
				'behindSchedule' => $behindSchedule,
				'warning' => $warning,
				'onSchedule' => $onSchedule,
			];
		}

		return response()->json(['data' => $divisions], 200);
	}

	public function getTotalProjectPerBudgetHealth($all_project, $hierarchy, $role_name)
	{
		$role = urldecode($role_name);
		$replaceHierarchy = preg_replace('/[\[\]]/', '', $hierarchy);

		$response = [
			'data' => [
				'overrun' => 0,
				'warning' => 0,
				'on-budget' => 0,
			]
		];
		$divisi = Divisi::query()
			->pluck('id');

		// arr overrun
		if ($role === 'Super Admin') {
			$response['data']['overrun'] = Project::where('budget_health', 'overrun')
				->count();
		} elseif ($all_project == 'true') {
			$response['data']['overrun'] = Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'overrun']])
				->count();
		} else {
			$response['data']['overrun'] =  Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'overrun'], ['created_by_id', $replaceHierarchy]])
				->count();
		}
		// arr warning
		if ($role === 'Super Admin') {
			$response['data']['warning'] = Project::where('budget_health', 'warning')
				->count();
		} elseif ($all_project == 'true') {
			$response['data']['warning'] = Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'warning']])
				->count();
		} else {
			$response['data']['warning'] =  Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'warning'], ['created_by_id', $replaceHierarchy]])
				->count();
		}
		// arr on-budget
		if ($role === 'Super Admin') {
			$response['data']['on-budget'] = Project::where('budget_health', 'on-budget')
				->count();
		} elseif ($all_project == 'true') {
			$response['data']['on-budget'] = Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'on-budget']])
				->count();
		} else {
			$response['data']['on-budget'] =  Project::whereIn('divisi_id', $divisi)
				->where([['budget_health', 'on-budget'], ['created_by_id', $replaceHierarchy]])
				->count();
		}
		return response()->json($response, 200);
	}

	private function countTotalProjectByBudgetHealthInDivision($divisi, $health)
	{
		$project = Project::where([['divisi_id', $divisi], ['budget_health', $health]])->count();
		return $project;
	}


	public function getTotalProjectBudgetHealthPerDivision($role_name)
	{
		$role = urldecode($role_name);
		$divisions = null;
		if ($role === 'Super Admin') {
			$divisions = Divisi::select('id', 'name')
				->with('children')
				->whereNull('parent')
				->get();
		} else {
			$divisions = Divisi::select('id', 'name')
				->with('children')
				->whereNull('parent')
				->get();
		}


		foreach ($divisions as $division) {
			$budgetData = new Collection();
			$budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, 'overrun'), 'overrun');
			$budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, 'warning'), 'warning');
			$budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, 'on-budget'), 'on-budget');
			foreach ($division->children as $d) {
				$budgetData['overrun'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, 'overrun');
				$budgetData['warning'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, 'warning');
				$budgetData['on-budget'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, 'on-budget');
			}
			unset($division->children);
			$division->budgetData = $budgetData;
		}
		return response()->json([
			'data' => [
				$divisions
			]
		], 200);
	}

	public function getTotalProjectPerPhase($role_name, $all_project, $hierarchy)
	{
		$role = urldecode($role_name);
		$projectPhases = ProjectPhase::orderBy('order')->get();

		foreach ($projectPhases as $phase) {

			if ($role === 'Super Admin' || $all_project) {
				$phase->totalProject = Project::where('phase_id', $phase->id)
					->count();
			} else {
				$phase->totalProject = Project::where('phase_id', $phase->id)
					->where('created_by_id', $hierarchy)
					->count();
			}
		}
		return response()->json([
			'data' => [
				$projectPhases
			]
		], 200);
	}

	private function countTotalProjectInDivision($id)
	{
		return Project::where('divisi_id', $id)
			->count();
	}

	public function getTotalProjectPerDivision($role_name)
	{
		$role = urldecode($role_name);
		$divisions = null;
		$divisions = Divisi::select('id', 'name', 'parent', 'color')
			->with('children')
			->whereNull('parent')
			->get();

		// to do : count in more than 1 level child
		foreach ($divisions as $v) {
			$v->total = $this->countTotalProjectInDivision($v->id);
			foreach ($v->children as $d) {
				$v->total += $this->countTotalProjectInDivision($d->id);
			}
			unset($v->children);
		}

		return response()->json([
			'data' => $divisions
		], 200);
	}

	private function countTotalProjectValueInDivision($id)
	{
		return Project::select(DB::raw('SUM(CAST("rencana_biaya" AS DOUBLE PRECISION))'))
			->where('divisi_id', $id)
			->pluck('sum')
			->first();
	}

	public function getTotalProjectValuePerDivision($role_name)
	{
		$role = urldecode($role_name);
		$divisions =  Divisi::select('id', 'name', 'color')
			->with('children')
			->whereNull('parent')
			->get();

		foreach ($divisions as $v) {
			$v->total = $this->countTotalProjectValueInDivision($v->id);
			foreach ($v->children as $d) {
				$v->total += $this->countTotalProjectValueInDivision($d->id);
			}
			unset($v->children);
		}

		return response()->json([
			'data' => $divisions
		], 200);
	}


	public function getDetailExpenditure($all_project, $hierarchy, $role_name)
	{
		$projects = [];
		if ($all_project) {
			$projects = Project::orderBy('id', 'desc')
				->get();
		} else {
			$projects = Project::where('created_by_id', $hierarchy)
				->orderBy('id', 'desc')
				->get();
		}

		foreach ($projects as $project) {
			$lastGantt = MasterFunctionsHelper::getLatestGantt($project->id);
			if ($project->kode_sortname != "") {
				$project->invoice = [
					'invoiced' =>  0,
					'paid' => 0,
				];
			}

			$project->pm = User::find($project->pm_id);
			if (!isset($lastGantt['last_gantt_id'])) {
				$project->manPowers = 0;
			} else {
				$project->manPowers = UserToVersionGantt::where('version_gantt_id', $lastGantt['last_gantt_id'])->count();
			}
			$project->lastGanttId = MasterFunctionsHelper::getLatestGantt($project->id);
		}

		return response()->json([
			'data' => $projects,
			'total_manpowers' => User::count()
		], 200);
	}
}