Server : Apache System : Linux server2.corals.io 4.18.0-348.2.1.el8_5.x86_64 #1 SMP Mon Nov 15 09:17:08 EST 2021 x86_64 User : corals ( 1002) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/corals/ts.corals.io/corals-api/Corals/modules/Timesheet/Reports/ |
<?php
namespace Corals\Modules\Timesheet\Reports;
use Corals\Modules\Timesheet\Models\Client;
use Corals\Modules\Timesheet\Models\Entry;
use Corals\Modules\Timesheet\Models\Project;
use Corals\User\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
class ProjectsReport
{
public function getBaseProjectsQuery($start_date, $end_date)
{
return Entry::query()
->selectRaw("
((SUM(ifNull(timesheet_entries.hours*60,0)) + SUM(ifNull(timesheet_entries.minutes,0)))/60) as total_time,
((SUM(ifNull(timesheet_entries.evaluation_hours*60,0)) + SUM(ifNull(timesheet_entries.evaluation_minutes,0)))/60) as evaluation_time,
timesheet_clients.name as client_name,
timesheet_projects.name,
timesheet_activities.billable as activity_is_billable,
timesheet_projects.client_id,
timesheet_projects.id,
timesheet_projects.type,
timesheet_entries.activity_id,
timesheet_entries.user_id,
(SUM(timesheet_entries.amount)) as total_amount,
(SUM(timesheet_entries.cost)) as total_cost")
->join('timesheet_projects', 'timesheet_projects.id', '=', 'timesheet_entries.project_id')
->join('timesheet_clients', 'timesheet_projects.client_id', '=', 'timesheet_clients.id')
->join('timesheet_activities', 'timesheet_activities.id', '=', 'timesheet_entries.activity_id')
->whereBetween('timesheet_entries.spent_at', [$start_date, $end_date])
->groupBy('timesheet_projects.id', 'timesheet_projects.type', 'timesheet_activities.id');
}
public function getProjectsReport(Request $request): array
{
$billable_project_report = [];
$non_billable_project_report = [];
$start_date = $request->get('from_date', now()->subMonth()->startOfMonth());
$end_date = $request->get('to_date', now()->subMonth()->endOfMonth());
$client_id = $request->get('client_id');
$user_id = $request->get('user_id');
$header_for_non_billable_project_report = [
'client' => 'Client',
'project' => 'Project',
'hours' => 'Hours',
'cost' => 'Cost',
];
$header_for_billable_project_report = [
'client' => 'Client',
'project' => 'Project',
'hours' => 'Hours',
'evaluation_hours' => 'Evaluation',
'billable_amount' => 'Billable',
'cost' => 'Cost',
'balance' => 'Balance'
];
$footer_non_billable_project_report = [
'total' => 'Total',
'space' => '',
'total_hours' => 0,
'total_cost' => 0
];
$footer_billable_project_report = [
'total' => 'Total',
'space' => '',
'total_hours' => 0,
'total_evaluation_hours' => 0,
'total_billable_amount' => 0,
'total_cost' => 0,
'total_balance' => 0
];
$billable_project_report['header'] = $header_for_billable_project_report;
$non_billable_project_report['header'] = $header_for_non_billable_project_report;
if (!is_null($client_id)) {
$projectsForClient = Client::findOrFail($client_id)->projects->pluck('id');
$projectsData = $this->getBaseProjectsQuery($start_date, $end_date)
->where('timesheet_projects.client_id', '=', $client_id)
->whereIn('timesheet_entries.project_id', $projectsForClient)
->get();
} elseif (!is_null($user_id)) {
/**
* @var Collection $projectsForUser
* @var Collection $notAssignableProjects
*/
$projectsForUser = User::findOrFail($user_id)->projects->pluck('id');
$notAssignableProjects = Project::query()->where('assignable', 0)->pluck('id');
$projectsForUser = $projectsForUser->merge($notAssignableProjects);
$projectsData = $this->getBaseProjectsQuery($start_date, $end_date)
->whereIn('timesheet_entries.project_id', $projectsForUser)
->where('timesheet_entries.user_id', $user_id)
->get();
} else {
$projectsData = [];
}
foreach ($projectsData as $project) {
if (!$project->activity_id) {
continue;
}
if (!isset($billable_project_report['project_' . $project->id])) {
$billable_project_report['project_' . $project->id]['client'] = [
'code' => 'nav_to',
'attr' => [
'route' => "/clients/$project->client_id",
'label' => $project->client_name
],
];
$billable_project_report['project_' . $project->id]['project'] = [
'code' => 'nav_to',
'attr' => [
'route' => "/clients/$project->client_id/projects/$project->id",
'label' => $project->name
],
];
$billable_project_report['project_' . $project->id]['hours'] = 0;
$billable_project_report['project_' . $project->id]['evaluation_hours'] = 0;
$billable_project_report['project_' . $project->id]['billable_amount'] = 0;
$billable_project_report['project_' . $project->id]['cost'] = 0;
$billable_project_report['project_' . $project->id]['balance'] = 0;
}
if (!isset($non_billable_project_report['project_' . $project->id])) {
$non_billable_project_report['project_' . $project->id]['client'] = [
'code' => 'nav_to',
'attr' => [
'route' => "/clients/$project->client_id",
'label' => $project->client_name
],
];
$non_billable_project_report['project_' . $project->id]['project'] = [
'code' => 'nav_to',
'attr' => [
'route' => "/clients/$project->client_id/projects/$project->id",
'label' => $project->name
],
];
$non_billable_project_report['project_' . $project->id]['hours'] = 0;
$non_billable_project_report['project_' . $project->id]['cost'] = 0;
}
if ($project->type !== 'non_billable' && $project->activity_is_billable) {
$billable_project_report['project_' . $project->id]['hours'] += $project->total_time;
$billable_project_report['project_' . $project->id]['evaluation_hours'] += $project->evaluation_time;
$billable_project_report['project_' . $project->id]['billable_amount'] += $project->total_amount;
$billable_project_report['project_' . $project->id]['cost'] += $project->total_cost;
$billable_project_report['project_' . $project->id]['balance'] = $billable_project_report['project_' . $project->id]['billable_amount'] - $billable_project_report['project_' . $project->id]['cost'];
$footer_billable_project_report['total_hours'] += $project->total_time;
$footer_billable_project_report['total_evaluation_hours'] += $project->evaluation_time;
$footer_billable_project_report['total_billable_amount'] += $project->total_amount;
$footer_billable_project_report['total_cost'] += $project->total_cost;
$footer_billable_project_report['total_balance'] += $billable_project_report['project_' . $project->id]['balance'];
} else {
$non_billable_project_report['project_' . $project->id]['hours'] += $project->total_time;
$non_billable_project_report['project_' . $project->id]['cost'] += $project->total_cost;
$footer_non_billable_project_report['total_hours'] += $project->total_time;
$footer_non_billable_project_report['total_cost'] += $project->total_cost;
}
}
$billable_project_report = array_filter($billable_project_report, function ($record) {
return data_get($record, 'hours') > 0 || data_get($record, 'hours') === 'Hours';
});
if (count($billable_project_report) === 1) {
$billable_project_report['no_data'] = false;
} else {
$billable_project_report['footer'] = $footer_billable_project_report;
}
$non_billable_project_report = array_filter($non_billable_project_report, function ($record) {
return data_get($record, 'hours') > 0 || data_get($record, 'hours') === 'Hours';
});
if (count($non_billable_project_report) === 1) {
$non_billable_project_report['no_data'] = false;
} else {
$non_billable_project_report['footer'] = $footer_non_billable_project_report;
}
return roundResults([
'billable_projects_report' => $billable_project_report,
'non_billable_projects_report' => $non_billable_project_report,
]);
}
}