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/dceprojects.corals.io/Corals/modules/Timesheet/Classes/ |
<?php
namespace Corals\Modules\Timesheet\Classes;
use Carbon\Carbon;
use Corals\Foundation\Classes\ExcelWriter;
use Corals\Modules\Timesheet\Models\Entry;
use Corals\User\Models\User;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Str;
class Report
{
protected $userId;
protected $startDate;
protected $jobId;
protected $endDate;
protected $departmentCode;
protected $reportBy;
protected $overUsageJob;
public function __construct($userId = null, $jobId = null, $startDate = null, $endDate = null, $departmentCode = null, $reportBy = 'employees', $overUsageJob = false)
{
$this->userId = $userId;
$this->jobId = $jobId;
$this->startDate = Carbon::parse($startDate);
$this->endDate = Carbon::parse($endDate);
$this->reportBy = $reportBy;
$this->departmentCode = $departmentCode;
$this->overUsageJob = !!$overUsageJob;
}
public function generateReport()
{
$entryQB = $this->buildQuery();
return $this->extractDataForReport($entryQB);
}
protected function buildQuery()
{
return Entry::query()
->when($this->reportBy == 'employees', function ($query) {
$query->with('user')
->join('users', 'ts_entries.user_id', '=', 'users.id');
})
->when(in_array($this->reportBy, ['departments', 'jobs']) || $this->departmentCode, function ($query) {
$query->join('utility_list_of_values', 'ts_entries.department_code', '=', 'utility_list_of_values.code');
})
->when(in_array($this->reportBy, ['jobs', 'employees']), function ($query) {
$query->join('ts_jobs', 'ts_entries.entrieable_id', '=', 'ts_jobs.id');
})
->when($this->userId, function ($query) {
$query->where('ts_entries.user_id', $this->userId);
})
->when($this->jobId, function ($query) {
$query->where('ts_entries.entrieable_id', $this->jobId);
})
->when($this->departmentCode, function ($query) {
$query->where('utility_list_of_values.code', $this->departmentCode);
})
->where(function ($query) {
$query->whereDate("ts_entries.spent_at", '>=', $this->startDate)
->whereDate("ts_entries.spent_at", '<=', $this->endDate);
});
}
public function exports()
{
$fileName = $this->createFileName();
$reportExcel = ExcelWriter::create($fileName);
$reportRecords = $this->generateReport();
foreach ($reportRecords['records'] as $record) {
$this->addRawToExcel($reportExcel, $record);
}
$reportExcel->close();
return response()->download($fileName)->deleteFileAfterSend();
}
protected function createFileName(): string
{
$path = config('app.export_excel_base_path');
$publicPath = storage_path($path);
if (!File::exists($publicPath)) {
File::makeDirectory($publicPath, 0755, true);
}
return sprintf(
'%s/%s_q%s_%s.xlsx',
$publicPath,
Str::slug("Report"),
uniqid(),
now()->format('Y_dM_H_i')
);
}
private function addRawToExcel(ExcelWriter $reportExcel, $record)
{
$arrayColumns = collect($record)->filter(function ($value) {
return is_array($value);
});
$nonArrayColumns = collect($record)->filter(function ($value) {
return !is_array($value);
});
$combinations = $this->generateCombinations($arrayColumns);
foreach ($combinations as $combination) {
$row = $nonArrayColumns->merge($combination->first())->mapWithKeys(function ($value, $key) {
return [Str::title(str_replace('_', ' ', $key)) => $this->sanitizeValue($value)];
})->toArray();
$reportExcel->addRow($row);
}
}
private function generateCombinations($arrayColumns)
{
$arrays = $arrayColumns->values()->toArray();
$keys = $arrayColumns->keys()->toArray();
$combinations = $this->cartesianProduct($arrays);
return collect($combinations)->map(function ($combination) use ($keys) {
return collect($combination)->mapWithKeys(function ($value, $index) use ($keys) {
$prefix = Str::singular($keys[$index]) . '_';
$value = collect($value)->mapWithKeys(function ($item, $key) use ($prefix) {
if (!Str::startsWith($key, $prefix)) {
$key = $prefix . $key;
}
return [$key => $item];
});
return [$keys[$index] => $value];
});
});
}
private function cartesianProduct($arrays): array
{
$result = [[]];
foreach ($arrays as $index => $propertyValues) {
$temp = [];
foreach ($result as $resultItem) {
foreach ($propertyValues as $propertyValue) {
$temp[] = array_merge($resultItem, [$propertyValue]);
}
}
$result = $temp;
}
return $result;
}
private function sanitizeValue($value)
{
if (is_null($value)) {
return '';
}
if (is_bool($value)) {
return $value ? 'true' : 'false';
}
return strip_tags($value);
}
private function getLoggedTimeInHours($hours, $minutes): float
{
return (($hours * 60) + $minutes) / 60;
}
private function extractDataForReport($entryQB)
{
switch ($this->reportBy) {
case 'employees':
return $this->extractDataForEmployees($entryQB);
case 'departments':
return $this->extractDataForDepartments($entryQB);
case 'jobs':
return $this->extractDataForJobs($entryQB);
default:
throw new \Exception("Invalid report type: {$this->reportBy}");
}
}
private function extractDataForEmployees(Builder $entryQB): array
{
$entries = $entryQB->select(
'ts_entries.id as id',
'ts_jobs.id as entrieable_id',
'ts_jobs.title as job_name',
'users.id as user_id',
'users.name as user_name',
DB::raw('SUM(ts_entries.hours) as total_hours'),
DB::raw('SUM(ts_entries.minutes) as total_minutes'),
)->selectSub(function ($query) {
$query->from('ts_entries as overall_entries')
->selectRaw('SUM((overall_entries.hours * 60) + overall_entries.minutes) / 60')
->where(function ($query) {
$query->whereDate("overall_entries.spent_at", '>=', $this->startDate)
->whereDate("overall_entries.spent_at", '<=', $this->endDate);
})
->whereColumn('overall_entries.user_id', 'ts_entries.user_id');
}, 'overall_logged_time')
->groupBy('ts_jobs.id', 'users.id')
->orderBy('users.id')
->get();
$summary = [
'all_jobs_logged_time' => 0,
'employees_total_cost' => 0,
'number_of_employees' => $entries->unique('user_id')->count(),
];
$entries = $entries->groupBy(['user_id', 'entrieable_id']);
$reportRecords['records'] = $entries->map(function ($employeeJobEntries) use (&$summary) {
$employeeRecord = [];
foreach ($employeeJobEntries as $employeeJobs) {
foreach ($employeeJobs as $entry) {
$user = $entry->user;
$periodicSalary = $this->calculateSalaryForPeriod($user, $this->startDate, $this->endDate);
$loggedTimeInHours = $this->getLoggedTimeInHours($entry->total_hours, $entry->total_minutes);
$hourlyRate = $this->calcAdjustedHourlyRate($periodicSalary, $entry->overall_logged_time);
$total_cost = $hourlyRate * $loggedTimeInHours;
$summary['employees_total_cost'] += $total_cost;
$summary['all_jobs_logged_time'] += $loggedTimeInHours;
if (empty($employeeRecord)) {
$employeeRecord = [
'jobs' => [],
'user_id' => $entry->user_id,
'user_name' => HtmlElement('a', [
'href' => url(
"ts/entries?user_id={$entry->user->id}&spent_at[from]={$this->startDate->toDateString()}&spent_at[to]={$this->endDate->toDateString()}" . ($this->jobId ? ('&entrieable=' . $this->jobId) : '') . ($this->departmentCode ? ('&department_code=' . $this->departmentCode) : ''))
]
, $entry->user->full_name
),
'period_all_logged_hours' => \Timesheet::getLoggedTimeInHMFormat($entry->overall_logged_time, 0),
'periodic_salary' => round($periodicSalary, 1),
'adjusted_rate' => round($hourlyRate, 1),
'total_cost' => 0,
];
}
$employeeRecord['jobs'][] = [
'job_id' => $entry->entrieable_id,
'job_name' => $entry->job_name,
'cost' => \Timesheet::formatMoney(round($total_cost, 1)),
'hours' => \Timesheet::getLoggedTimeInHMFormat($entry->total_hours, $entry->total_minutes),
];
$employeeRecord['total_cost'] += $total_cost;
}
}
$employeeRecord['total_cost'] = \Timesheet::formatMoney(round($employeeRecord['total_cost'], 1));
return $employeeRecord;
});
$summary['employees_total_cost'] = round($summary['employees_total_cost'], 1);
$summary['all_jobs_logged_time'] = \Timesheet::getLoggedTimeInHMFormat($summary['all_jobs_logged_time']);
$reportRecords['summary'] = $summary;
return $reportRecords;
}
private function extractDataForDepartments($entryQB)
{
$entries = $entryQB->select(
'utility_list_of_values.code as department_code',
'utility_list_of_values.label as department_name',
DB::raw('SUM(ts_entries.hours) as total_hours'),
DB::raw('SUM(ts_entries.minutes) as total_minutes'),
DB::raw('COUNT(DISTINCT ts_entries.user_id) as number_of_employees')
)->groupBy('utility_list_of_values.code', 'utility_list_of_values.label')->get();
$summary = [
'all_departments_logged_time' => 0,
'entries_employees_count' => 0,
'employees_total_cost' => 0,
];
$reportRecords['records'] = $entries->map(function ($entry) use (&$summary) {
$this->reportBy = 'employees';
$this->departmentCode = $entry->department_code;
$result = $this->generateReport();
$totalLoggedTime = $this->getLoggedTimeInHours($entry->total_hours, $entry->total_minutes);
$summary['all_departments_logged_time'] += $totalLoggedTime;
$summary['entries_employees_count'] += $entry->number_of_employees;
$summary['employees_total_cost'] += $result['summary']['employees_total_cost'];
return [
'department_code' => $entry->department_code,
'department_name' => $entry->department_name,
'total_hours' => \Timesheet::getLoggedTimeInHMFormat($entry->total_hours, $entry->total_minutes),
'number_of_employees' => $entry->number_of_employees,
'employees_total_cost' => \Timesheet::formatMoney($result['summary']['employees_total_cost'])
];
});
$summary['all_departments_logged_time'] = \Timesheet::getLoggedTimeInHMFormat($summary['all_departments_logged_time']);
$summary['employees_total_cost'] = \Timesheet::formatMoney($summary['employees_total_cost']);
$reportRecords['summary'] = $summary;
return $reportRecords;
}
private function extractDataForJobs($entryQB)
{
$overallJobQB = $this->buildOverallJobQuery();
$overallDepartmentQB = $this->buildOverallDepartmentQuery();
$groupedDepartmentEntries = $overallDepartmentQB->groupBy('job_id');
$totalLoggedJobTimes = Entry::whereIn('entrieable_id', $entryQB->pluck('entrieable_id'))
->select(
'entrieable_id as job_id',
DB::raw('SUM(hours) as total_hours'),
DB::raw('SUM(minutes) as total_minutes')
)
->groupBy('entrieable_id')
->get()
->keyBy('job_id');
$jobEntries = $entryQB
->select(
'ts_entries.department_code',
'ts_jobs.id as job_id',
'ts_jobs.title as job_name',
DB::raw('COUNT(DISTINCT ts_entries.user_id) as total_employees'),
DB::raw('SUM(ts_entries.hours) as period_hours'),
DB::raw('SUM(ts_entries.minutes) as total_minutes')
)
->groupBy('ts_jobs.id', 'ts_jobs.title')
->get();
$totalLoggedDepartmentTimes = Entry::whereIn('entrieable_id', $jobEntries->pluck('job_id'))
->select(
'department_code',
'ts_entries.entrieable_id as job_id',
DB::raw('SUM(hours) as total_hours'),
DB::raw('SUM(minutes) as total_minutes'),
DB::raw('COUNT(DISTINCT user_id) as total_employees')
)
->groupBy('department_code', 'job_id')
->get()
->mapWithKeys(function ($item) {
return [sprintf('%s-%s', $item->department_code, $item->job_id) => $item];
});
$periodLoggedDepartmentTimes = Entry::whereIn('entrieable_id', $jobEntries->pluck('job_id'))
->select(
'department_code',
'ts_entries.entrieable_id as job_id',
DB::raw('SUM(hours) as total_hours'),
DB::raw('SUM(minutes) as total_minutes'),
DB::raw('COUNT(DISTINCT user_id) as total_employees')
)
->where(function ($query) {
$query->whereDate("ts_entries.spent_at", '>=', $this->startDate)
->whereDate("ts_entries.spent_at", '<=', $this->endDate);
})
->groupBy('department_code', 'job_id')
->get()
->mapWithKeys(function ($item) {
return [sprintf('%s-%s', $item->department_code, $item->job_id) => $item];
});
$summary = [
'all_jobs_logged_time' => 0,
'jobs_count' => $jobEntries->count(),
'employees_total_cost' => 0,
];
$reportRecords['records'] = $jobEntries->map(function ($job) use ($groupedDepartmentEntries, $overallJobQB, &$summary, $totalLoggedJobTimes, $totalLoggedDepartmentTimes, $periodLoggedDepartmentTimes) {
$overallJob = $overallJobQB->firstWhere('job_id', $job->job_id);
$totalLoggedPeriodJobHours = $this->getLoggedTimeInHours($job->period_hours, $job->total_minutes);
$loggedTimeData = $totalLoggedJobTimes->get($job->job_id);
$totalLoggedJobHours = $this->getLoggedTimeInHours(
$loggedTimeData->total_hours ?? 0,
$loggedTimeData->total_minutes ?? 0
);
$completionPercentage = $this->calcPercentage($overallJob->overall_expected_hours, $totalLoggedJobHours);
if ($this->overUsageJob && $completionPercentage < 100) {
return null;
}
$summary['all_jobs_logged_time'] += $totalLoggedPeriodJobHours;
$departments = $groupedDepartmentEntries->get($job->job_id, collect())->map(function ($entry) use ($totalLoggedJobHours, $totalLoggedDepartmentTimes, $periodLoggedDepartmentTimes) {
$totalDepartmentData = $totalLoggedDepartmentTimes->get($entry->department_code . '-' . $entry->job_id);
$periodDepartmentData = $periodLoggedDepartmentTimes->get($entry->department_code . '-' . $entry->job_id);
$totalDepartmentJobLoggedTime = $this->getLoggedTimeInHours(
$totalDepartmentData->total_hours ?? 0,
$totalDepartmentData->total_minutes ?? 0
);
$periodDepartmentJobLoggedTime = $this->getLoggedTimeInHours(
$periodDepartmentData->total_hours ?? 0,
$periodDepartmentData->total_minutes ?? 0
);
return [
'department_name' => $entry->department_name,
'department_employees' => $periodDepartmentData->total_employees ?? 0,
'period_hours' => \Timesheet::getLoggedTimeInHMFormat($periodDepartmentJobLoggedTime, 0),
'department_expected_hours' => $entry->department_expected_hours,
'completion_percentage' => $this->calcPercentage($entry->department_expected_hours, $totalDepartmentJobLoggedTime),
'job_percentage' => $this->calcPercentage($totalLoggedJobHours, $totalDepartmentJobLoggedTime),
];
})->toArray();
$this->reportBy = 'employees';
$this->jobId = $job->job_id;
$result = $this->generateReport();
$summary['employees_total_cost'] += $result['summary']['employees_total_cost'];
return [
'job_id' => $job->job_id,
'job_name' => $job->job_name,
'number_of_employees' => $job->total_employees,
'period_hours' => \Timesheet::getLoggedTimeInHMFormat($totalLoggedPeriodJobHours, 0),
'overall_expected_hours' => $overallJob->overall_expected_hours,
'completion_percentage' => $completionPercentage,
'departments' => $departments,
'employees_total_cost' => \Timesheet::formatMoney($result['summary']['employees_total_cost']),
];
})->filter()->values()->all();
$summary['all_jobs_logged_time'] = \Timesheet::getLoggedTimeInHMFormat($summary['all_jobs_logged_time']);
$summary['employees_total_cost'] = \Timesheet::formatMoney($summary['employees_total_cost']);
$reportRecords['summary'] = $summary;
return $reportRecords;
}
private function buildOverallJobQuery()
{
$subQuery = DB::table('ts_department_job')
->select('job_id', DB::raw('SUM(hours) as total_hours'))
->groupBy('job_id');
return Entry::query()
->join('ts_jobs', 'ts_entries.entrieable_id', '=', 'ts_jobs.id')
->joinSub($subQuery, 'job_hours', function ($join) {
$join->on('ts_jobs.id', '=', 'job_hours.job_id');
})
->select(
'ts_jobs.id as job_id',
'ts_jobs.title as job_name',
DB::raw('COUNT(DISTINCT ts_entries.user_id) as total_employees'),
DB::raw('SUM(ts_entries.hours) as period_hours'),
DB::raw('SUM(ts_entries.minutes) as total_minutes'),
'job_hours.total_hours as overall_expected_hours'
)
->groupBy('ts_jobs.id', 'ts_jobs.title', 'job_hours.total_hours')
->get();
}
private function buildOverallDepartmentQuery()
{
return Entry::query()
->join('ts_jobs', 'ts_entries.entrieable_id', '=', 'ts_jobs.id')
->join('ts_department_job', 'ts_jobs.id', '=', 'ts_department_job.job_id')
->join('utility_list_of_values', 'ts_department_job.department_code', '=', 'utility_list_of_values.code')
->select(
'ts_jobs.id as job_id',
'ts_jobs.title as title',
'utility_list_of_values.code as department_code',
'utility_list_of_values.label as department_name',
'ts_department_job.hours as department_expected_hours',
DB::raw('COUNT(DISTINCT ts_entries.user_id) as department_employees'),
DB::raw('SUM(ts_entries.hours) as department_hours'),
DB::raw('SUM(ts_entries.minutes) as department_minutes')
)
->groupBy('ts_jobs.id', 'ts_jobs.title', 'utility_list_of_values.code')
->get();
}
private function calcPercentage(float $totalJobHours, float $departmentJobLoggedTime)
{
return round($departmentJobLoggedTime / $totalJobHours * 100, 2);
}
private function calcAdjustedHourlyRate($salary, $loggedTime)
{
return $loggedTime == 0 ? 0 : $salary / $loggedTime;
}
public function calculateSalaryForPeriod(User $user, Carbon $startDate, Carbon $endDate): float
{
$startPeriod = $startDate->copy();
$endPeriod = $endDate->copy();
$salaryBreakDown = [];
while ($startPeriod->lte($endPeriod)) {
if (!isset($salaryBreakDown[$startPeriod->shortMonthName])) {
$working_days = \Corals\Modules\Timesheet\Facades\Timesheet::calculateWorkingDays($startPeriod, null, true);
$working_hours = $working_days * config('timesheet.hours_per_day');
$hourlyRate = $user->salary / $working_hours;
if ($startPeriod->isSameMonth($endPeriod)) {
$endPeriodMonth = $endPeriod->copy();
} else {
$endPeriodMonth = $startPeriod->copy()->endOfMonth();
}
$salaryBreakDown[$startPeriod->shortMonthName] = [
'working_days' => $working_days,
'working_hours' => $working_hours,
'hourly_rate' => $hourlyRate,
'salary' => \Corals\Modules\Timesheet\Facades\Timesheet::calculateWorkingDays($startPeriod, $endPeriodMonth) * config('timesheet.hours_per_day') * $hourlyRate
];
}
$startPeriod->addMonth()->startOfMonth();
}
return collect($salaryBreakDown)->sum('salary');
}
}