You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
493 lines
14 KiB
493 lines
14 KiB
<?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); |
|
} |
|
}
|
|
|