ospro-api-laravel/app/Http/Controllers/API/DashboardBoDController.php

<?php

namespace App\Http\Controllers\API;

use App\Http\Controllers\Controller;
use App\Helpers\MasterFunctionsHelper;
use App\Models\{Divisi, UserToVersionGantt, Project, ProjectPhase, User};
use Illuminate\Support\Collection;

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

    private function curlReq($url, $token)
    {
        $ch = curl_init();
        $headers = [
            'Authorization: ' . $token
        ];
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

        $response = curl_exec($ch);
        if ($response === false)
            $response = curl_error($ch);
        curl_close($ch);

        return json_decode($response);
    }

    private function getInvoiceIntegration($search)
    {
        return null;
    }

    // to do
    public function getCompanyCashFlow($year = '%')
    {
        $year = $this->interpolateYear($year);
        $totalExpenditure = $totalInvoice = $totalPaidInvoice = 0;

        // we can't use eloquent's sum() method because someone decided to use varchar as datatype in rencana_biaya field
        $totalBudgets = Project::query()
            ->selectRaw('SUM(CAST("rencana_biaya" AS DOUBLE PRECISION))')
            ->where('mulai_proyek', 'LIKE', '%' . $year . '%')
            ->pluck('sum')
            ->first();

        $projects = Project::query()
            ->where('mulai_proyek', 'LIKE', '%' . $year . '%')
            ->get();
        foreach ($projects as $project) {
            $project->expenses = 0;

            $resp = null;
            if ($project->kode_sortname != "") {
                $resp = $this->getInvoiceIntegration($project->kode_sortname);
                /* $resp = $project->kode_sortname; */
                $cost = $resp->data->total_cost ?? 0;
                $cost = substr($cost, 0, strpos($cost, "."));
                $totalExpenditure += (int) $cost;
                $totalInvoice += $resp->data->total_invoice_amount ?? 0;
                $totalPaidInvoice += $resp->data->total_invoice_paid_amount ?? 0;
            }
        }

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

    public function getInvoiceOutstanding($year = '%')
    {
        $year = $this->interpolateYear($year);
        $projects = Project::where('mulai_proyek', 'LIKE', '%' . $year . '%')
            ->get();
        $return = [];
        foreach ($projects as $project) {
            $resp = null;
            if ($project['kode_sortname'] != "") {
                $resp = $this->getInvoiceIntegration($project['kode_sortname']);
                array_push($return, [
                    'project' => $project['nama'],
                    'project_code' => $project['kode_sortname'],
                    'invoiced' => $resp->data->total_invoice_amount ?? 0,
                    'paid' => $resp->data->total_invoice_paid_amount ?? 0,
                    'response' => $resp,
                ]);
            }
        }

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

    public function getTotalProjectPerScheduleHealth($year = '%')
    {
        $year = $this->interpolateYear($year);

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

        $projects = Project::where('mulai_proyek', 'LIKE', '%' . $year . '%')->get();
        foreach ($projects as $project) {
            $project['scurve'] = MasterFunctionsHelper::getSCurve($project['id']);
            $selisihProgress = 0;
            if ($project['scurve'] && $project['scurve'][0]) {
                $planningArray = $project['scurve'][0]['data']['percentagePlan'];
                $actualArray = $project['scurve'][0]['data']['percentageReal'];
                $planningProgress = !empty($planningArray) ? $planningArray[count($planningArray) - 1] : 0;
                $actualProgress = !empty($actualArray) ? $actualArray[count($actualArray) - 1] : 0;
            }
            $selisihProgress = $planningProgress - $actualProgress;
            try {
                if ($selisihProgress > 0 && $selisihProgress <= 5)
                    $return['warning'] += 1;
                elseif ($selisihProgress == 0)
                    $return['on-schedule'] += 1;
                else
                    $return['behind-schedule'] += 1;
            } catch (\Error $e) {
                return response()->json(['msg' => $e->getMessage(), 'data' => $project], 200);
            }
        }

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

    public function getTotalProjectScheduleHealthPerDivision($year = '%')
    {
        $year = $this->interpolateYear($year);

        $divisions = Divisi::whereNull('parent')->get();
        foreach ($divisions as $division) {

            $scheduleData = new Collection();
            $behindSchedule = $warning = $onSchedule = 0;

            $projects = Project::where('mulai_proyek', 'LIKE', '%' . $year . '%')->where('divisi_id', $division['id'])->get();
            foreach ($projects as $project) {
                $project['scurve'] = MasterFunctionsHelper::getSCurve($project['id']);
                if ($project['scurve']['difference'] > 0 && $project['scurve']['difference'] <= 5)
                    $warning++;
                elseif ($project['scurve']['difference'] > 5 && $project['scurve']['difference'] <= 100)
                    $behindSchedule++;
                elseif ($project['scurve']['difference'] == 0)
                    $onSchedule++;
            }

            $scheduleData->prepend($behindSchedule, 'behindSchedule');
            $scheduleData->prepend($warning, 'warning');
            $scheduleData->prepend($onSchedule, 'onSchedule');
            $division->scheduleData = $scheduleData;
        }
        return response()->json([
            'data' => [
                $divisions
            ]
        ], 200);
    }

    public function getTotalProjectPerBudgetHealth($year = '%')
    {
        $year = $this->interpolateYear($year);
        return response()->json([
            'data' => [
                'overrun' => Project::query()
                    ->where([
                        ['mulai_proyek', 'LIKE', '%' . $year . '%'],
                        ['budget_health', 'overrun']
                    ])->count(),
                'warning' => Project::query()
                    ->where([
                        ['mulai_proyek', 'LIKE', '%' . $year . '%'],
                        ['budget_health', 'warning']
                    ])->count(),
                'on-budget' => Project::query()
                    ->where([
                        ['mulai_proyek', 'LIKE', '%' . $year . '%'],
                        ['budget_health', 'on-budget']
                    ])->count(),
            ]
        ], 200);
    }

    private function countTotalProjectByBudgetHealthInDivision($divisi, $year, $health)
    {
        return Project::query()
            ->where([
                ['divisi_id', $divisi],
                ['mulai_proyek', 'LIKE', '%' . $year . '%'],
                ['budget_health', $health]
            ])->count();
    }


    public function getTotalProjectBudgetHealthPerDivision($year = '%')
    {
        $year = $this->interpolateYear($year);
        $divisions = Divisi::query()
            ->select('id', 'name')
            ->whereNull('parent')
            ->get();
        // to do : count in more than 1 level child
        foreach ($divisions as $division) {
            $budgetData = new Collection();
            $budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, $year, 'overrun'), 'overrun');
            $budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, $year, 'warning'), 'warning');
            $budgetData->prepend($this->countTotalProjectByBudgetHealthInDivision($division->id, $year, 'on-budget'), 'on-budget');
            foreach ($division->children as $d) {
                $budgetData['overrun'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, $year, 'overrun');
                $budgetData['warning'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, $year, 'warning');
                $budgetData['on-budget'] += $this->countTotalProjectByBudgetHealthInDivision($d->id, $year, 'on-budget');
            }
            unset($division->children);
            $division->budgetData = $budgetData;
        }
        foreach ($divisions as $division) {
        }
        return response()->json([
            'data' => [
                $divisions
            ]
        ], 200);
    }

    public function getTotalProjectPerPhase($year = '%')
    {
        $year = $this->interpolateYear($year);
        $projectPhases = ProjectPhase::query()
            ->orderBy('order')
            ->get();
        foreach ($projectPhases as $phase) {
            $phase->totalProject = Project::query()
                ->where([
                    ['phase_id', $phase->id],
                    ['mulai_proyek', 'like', '%' . $year . '%']
                ])->count();
        }
        return response()->json([
            'data' => [
                $projectPhases
            ]
        ], 200);
    }

    private function countTotalProjectInDivision($id, $year)
    {
        return Project::query()
            ->where([
                ['divisi_id', $id],
                ['mulai_proyek', 'LIKE', '%' . $year . '%']
            ])
            ->count();
    }

    public function getTotalProjectPerDivision($year = '%')
    {
        $year = $this->interpolateYear($year);

        $divisions = Divisi::query()
            ->select('id', 'name')
            ->whereNull('parent')
            ->get();

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

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

    private function countTotalProjectValueInDivision($id, $year)
    {
        return Project::query()
            ->selectRaw('SUM(CAST("rencana_biaya" AS DOUBLE PRECISION))')
            ->where([
                ['mulai_proyek', 'LIKE', '%' . $year . '%'],
                ['divisi_id', $id]
            ])
            ->pluck('sum')
            ->first();
    }

    public function getTotalProjectValuePerDivision($year = '%')
    {
        $year = $this->interpolateYear($year);

        $divisions = Divisi::query()
            ->select('id', 'name')
            ->whereNull('parent')
            ->get();

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

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


    public function getDetailExpenditure($year = '%')
    {
        $year = $this->interpolateYear($year);
        $projects = Project::query()
            ->where('mulai_proyek', 'LIKE', '%' . $year . '%')
            ->orderByDesc('id')
            ->get();
        foreach ($projects as $project) {
            $lastGantt = MasterFunctionsHelper::getLatestGantt($project['id']);

            if (!empty($project['kode_sortname'])) {
                $resp = $this->getInvoiceIntegration($project['kode_sortname']);
                $project->invoice = [
                    'invoiced' => $resp->data->total_invoice_amount ?? 0,
                    'paid' => $resp->data->total_invoice_paid_amount ?? 0,
                ];
            }

            $project->pm = User::findOrFail($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['scurve'] = MasterFunctionsHelper::getSCurve($project['id']);
            }
            $project->lastGanttId = MasterFunctionsHelper::getLatestGantt($project['id']);
        }

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