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\Foundation\Classes\ExcelWriter;
use Corals\Modules\Timesheet\Classes\BagParameters;
use Corals\Modules\Timesheet\Facades\Timesheet;
use Corals\Modules\Timesheet\Models\Activity;
use Corals\Modules\Timesheet\Models\Client;
use Corals\Modules\Timesheet\Models\Entry;
use Corals\Modules\Timesheet\Models\Project;
use Corals\Settings\Facades\Settings;
use Corals\User\Models\User;
use Illuminate\Database\Query\JoinClause;
use OpenSpout\Common\Entity\Style\CellAlignment;
use OpenSpout\Common\Entity\Style\Color;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Common\Exception\IOException;
use OpenSpout\Common\Exception\UnsupportedTypeException;
use OpenSpout\Writer\Exception\WriterNotOpenedException;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
class UserWorkingDaysReport
{
/**
* @var BagParameters
*/
protected BagParameters $bagParameters;
/**
* InvoiceGenerator constructor.
* @param BagParameters $bagParameters
*/
public function __construct(BagParameters $bagParameters)
{
$this->bagParameters = $bagParameters;
}
public function generateReport($exportExcel = false)
{
$reportData = $this->getReportData();
if ($exportExcel) {
return $this->exportToExcel($reportData);
} else {
return $reportData;
}
}
private function getReportData()
{
$start_date = $this->bagParameters->get('start_date');
$end_date = $this->bagParameters->get('end_date');
$projectIdsArray = $this->bagParameters->get('project_ids');
$clientIdsArray = $this->bagParameters->get('client_Ids');
$userIdsArray = $this->bagParameters->get('user_id');
$requiredWorkingHoursPerDay = 0;
if ($userIdsArray) {
$entriesDetails = Entry::query()
->select('timesheet_entries.hours', 'timesheet_entries.minutes', 'timesheet_entries.description', 'timesheet_entries.spent_at', 'timesheet_projects.name as project_name', 'timesheet_clients.name as client_name')
->join('timesheet_projects', function (JoinClause $joinActivitiesTable) {
$joinActivitiesTable->on('timesheet_entries.project_id', 'timesheet_projects.id');
})->join('timesheet_clients', function (JoinClause $joinActivitiesTable) {
$joinActivitiesTable->on('timesheet_projects.client_id', 'timesheet_clients.id');
})->whereBetween('timesheet_entries.spent_at', [$start_date->toDateString(), $end_date->toDateString()])
->when($userIdsArray, function ($userQB, $userIdsArray) {
$userQB->whereIn('timesheet_entries.user_id', $userIdsArray);
})->when($projectIdsArray, function ($userQB, $projectIdsArray) {
$userQB->whereIn('timesheet_entries.project_id', $projectIdsArray);
})->when($clientIdsArray, function ($clientQB, $clientIdsArray) {
$clientQB->join('timesheet_projects', function (JoinClause $joinTable) use ($clientIdsArray) {
$joinTable->on('timesheet_entries.project_id', 'timesheet_projects.id')
->whereIn('timesheet_projects.client_id', $clientIdsArray);
});
})->get();
}
$entries = Entry::query()
->whereBetween('timesheet_entries.spent_at', [$start_date->toDateString(), $end_date->toDateString()])
->join('timesheet_activities', function (JoinClause $joinActivitiesTable) {
$joinActivitiesTable->on('timesheet_entries.activity_id', 'timesheet_activities.id');
})->selectRaw('
timesheet_entries.description,
timesheet_entries.activity_id,
timesheet_activities.is_special,
timesheet_activities.title,
timesheet_entries.spent_at,
(SUM(ifNull(timesheet_entries.hours*60,0)) + SUM(ifNull(timesheet_entries.minutes,0)))/60 as entry_hours'
)->when($userIdsArray, function ($userQB, $userIdsArray) {
$userQB->whereIn('timesheet_entries.user_id', $userIdsArray);
})->when($projectIdsArray, function ($userQB, $projectIdsArray) {
$userQB->whereIn('timesheet_entries.project_id', $projectIdsArray);
})->when($clientIdsArray, function ($clientQB, $clientIdsArray) {
$clientQB->join('timesheet_projects', function (JoinClause $joinTable) use ($clientIdsArray) {
$joinTable->on('timesheet_entries.project_id', 'timesheet_projects.id')
->whereIn('timesheet_projects.client_id', $clientIdsArray);
});
})->groupBy('timesheet_entries.spent_at', 'timesheet_entries.activity_id')
->get();
if (empty($userIdsArray)) {
$userWithOutWorkingHours = User::query()->where('status', '=', 'active')
->whereHas('roles', function ($rolesQB) {
$rolesQB->where('name', 'member');
})->whereNull('working_hours')
->where('employee_type', '=', 'full_time')
->count();
$defaultRequiredWorkingHoursPerDay = Settings::get('total_working_hours_per_day') * $userWithOutWorkingHours;
$requiredWorkingHoursPerDayForUsersWithHours = User::query()
->whereHas('roles', function ($rolesQB) {
$rolesQB->where('name', 'member');
})->where('status', '=', 'active')
->whereNotNull('working_hours')->sum('working_hours');
$requiredWorkingHoursPerDay = $defaultRequiredWorkingHoursPerDay + $requiredWorkingHoursPerDayForUsersWithHours;
} else {
$users = User::query()->whereIn('id', $userIdsArray)->get();
foreach ($users as $user) {
$requiredWorkingHoursPerDay += $user->working_hours ?: Settings::get('total_working_hours_per_day');
}
}
$working_days = Settings::get('working_days');
$reportHeader = [
'day_name' => 'Day',
'date' => 'Date',
'required_hours' => 'Required',
'details' => 'Details'
];
$reportFooter = [
'day_name' => 'Totals',
'date' => '',
'required_hours' => 0,
'details' => ''
];
$dayDetailsTemplate = [
'day_name' => '',
'date' => '',
'required_hours' => $requiredWorkingHoursPerDay,
'details' => null
];
if (empty($userIdsArray)) {
unset($reportHeader['details']);
unset($reportFooter['details']);
unset($dayDetailsTemplate['details']);
}
$reportHeader['work'] = 'Work';
$dayDetailsTemplate['work'] = 0;
$reportFooter['work'] = 0;
$special_activities = Activity::query()->where('is_special', '=', 1)->get();
foreach ($special_activities as $special_activity) {
$reportHeader['activity_' . $special_activity->id] = $special_activity->title;
$dayDetailsTemplate['activity_' . $special_activity->id] = 0;
$reportFooter['activity_' . $special_activity->id] = 0;
}
$reportHeader['day_hours'] = 'Total';
$reportHeader['overtime_hours'] = 'Overtime';
$reportHeader['is_weekend'] = false;
$reportFooter['day_hours'] = 0;
$reportFooter['overtime_hours'] = 0;
$reportFooter['is_weekend'] = false;
$dayDetailsTemplate['day_hours'] = 0;
$dayDetailsTemplate['overtime_hours'] = -1 * $requiredWorkingHoursPerDay;
$dayDetailsTemplate['is_weekend'] = false;
$reportData = [];
$reportData['header'] = $reportHeader;
$loopDate = $start_date->copy();
while ($loopDate->lte($end_date)) {
$dateEntries = $entries->where('spent_at', $loopDate->toDateString());
if ($dateEntries->isNotEmpty()) {
foreach ($dateEntries as $entry) {
$spentAt = $entry->spent_at;
$entryHours = $entry->entry_hours;
$activityId = $entry->is_special ? ('activity_' . $entry->activity_id) : 'work';
if (!isset($reportData[$spentAt])) {
$reportData[$spentAt] = $dayDetailsTemplate;
}
$reportFooter['day_hours'] += $entryHours;
$reportData[$spentAt]['day_hours'] += $entryHours;
$reportData[$spentAt]['overtime_hours'] = $reportData[$spentAt]['day_hours'] - $requiredWorkingHoursPerDay;
$reportData[$spentAt][$activityId] += $entryHours;
$reportFooter[$activityId] += $entryHours;
}
} else {
$reportData[$loopDate->toDateString()] = $dayDetailsTemplate;
}
if (in_array($loopDate->shortDayName, $working_days)) {
$reportFooter['required_hours'] += $requiredWorkingHoursPerDay;
} else {
$reportData[$loopDate->toDateString()]['required_hours'] = 0;
$reportData[$loopDate->toDateString()]['is_weekend'] = true;
$reportData[$loopDate->toDateString()]['overtime_hours'] += $requiredWorkingHoursPerDay;
}
$reportFooter['overtime_hours'] += $reportData[$loopDate->toDateString()]['overtime_hours'];
foreach ($reportData[$loopDate->toDateString()] as $column => $value) {
if (in_array($column, ['is_weekend', 'details'])) {
continue;
}
$reportData[$loopDate->toDateString()][$column] = floatval($value);
}
$reportData[$loopDate->toDateString()]['day_name'] = $loopDate->shortDayName;
$reportData[$loopDate->toDateString()]['date'] = format_date($loopDate->toDateString());
$loopDate->addDay();
}
$reportFooterPerDay = [];
foreach ($reportFooter as $column => $value) {
if (in_array($column, ['is_weekend', 'date', 'details'])) {
$reportFooterPerDay[$column] = $value;
continue;
}
if ($column == 'day_name') {
$reportFooterPerDay[$column] = 'Totals(day)';
continue;
}
$reportFooter[$column] = $value;
$reportFooterPerDay[$column] = $value / Settings::get('total_working_hours_per_day');
}
$reportData['footer'] = $reportFooter;
$reportData['footer_per_day'] = $reportFooterPerDay;
if (isset($entriesDetails)) {
foreach ($entriesDetails as $entry) {
$reportData[$entry->spent_at]['details'][] = [
'client' => $entry->client_name,
'project' => $entry->project_name,
'time' => Timesheet::formatHoursAndMinutes($entry->hours, $entry->minutes),
'description' => $entry->description
];
}
}
return ['report_data' => roundResults($reportData)];
}
/**
* @param array $reportData
* @return BinaryFileResponse
* @throws IOException
* @throws UnsupportedTypeException
* @throws WriterNotOpenedException
*/
protected function exportToExcel(array $reportData)
{
$rootPath = config('app.export_excel_base_path');
if (is_array($this->bagParameters->get('user_id')) && count($this->bagParameters->get('user_id'))) {
$users = User::query()->whereIn('id', $this->bagParameters->get('user_id'))->get();
$user_names = $users->pluck('full_name')->toArray();
$user_name = implode(', ', $user_names);
}
if (is_array($this->bagParameters->get('project_ids')) && count($this->bagParameters->get('project_ids'))) {
$projects = Project::query()->whereIn('id', $this->bagParameters->get('project_ids'))->get();
$project_names = $projects->pluck('name')->toArray();
$project_names = implode(', ', $project_names);
}
if (is_array($this->bagParameters->get('client_ids')) && count($this->bagParameters->get('client_ids'))) {
$clients = Client::query()->whereIn('id', $this->bagParameters->get('client_ids'))->get();
$client_names = $clients->pluck('name')->toArray();
$client_names = implode(', ', $client_names);
}
$exportName = join('_', [
str_replace(['-', ':', ' '], '_', now()->toDateTimeString()) . '.xlsx'
]);
$filePath = storage_path($rootPath . $exportName);
if (!file_exists($rootPath = storage_path($rootPath))) {
mkdir($rootPath, 0755, true);
}
if (file_exists($filePath)) {
unlink($filePath);
}
$writer = ExcelWriter::create($filePath);
$headerStyle = new Style();
$headerStyle->setCellAlignment(CellAlignment::CENTER);
$headerStyle->setFontColor(Color::WHITE);
$headerStyle->setBackgroundColor('343a40');
$weekendStyle = new Style();
$weekendStyle->setCellAlignment(CellAlignment::CENTER);
$weekendStyle->setBackgroundColor('f0f0f0');
$defaultStyle = new Style();
$defaultStyle->setCellAlignment(CellAlignment::CENTER);
$writer->getWriter()->setColumnWidthForRange(13, 1, count($reportData['report_data']['header']));
$writer->noHeaderRow();
$writer->addRow([]);
$employeesHeader = [
'Employee: ',
$user_name ?? '',
];
$projectsHeader = [
'Projects: ',
$project_names ?? '',
];
$clientsHeader = [
'Clients: ',
$client_names ?? '',
];
$writer->addRow($employeesHeader);
$writer->addRow($projectsHeader);
$writer->addRow($clientsHeader);
$datesHeader = [
'From Date: ',
format_date($this->bagParameters->get('start_date')),
'To Date:',
format_date($this->bagParameters->get('end_date'))
];
$writer->addRow($datesHeader);
$writer->addRow([]);
//[B,2] , [C,2]
//B2:C2
$writer->getWriter()->mergeCells([1, 2], [2, 2]);
foreach ($reportData['report_data'] as $index => $dayRecord) {
$rowStyle = $dayRecord['is_weekend'] ? $weekendStyle : $defaultStyle;
unset($dayRecord['is_weekend']);
if (in_array($index, ['header', 'footer'])) {
$writer->addRow($dayRecord, $headerStyle);
continue;
}
if (!empty($dayRecord['details'])) {
$details = '';
foreach ($dayRecord['details'] as $detail) {
$details .= join(' - ', $detail) . ' | ';
}
$dayRecord['details'] = trim($details, '| ');
}
$writer->addRow($dayRecord, $rowStyle);
}
$writer->close();
$headers = [
'Content-Description' => 'File Transfer',
'Content-Transfer-Encoding' => 'binary',
'Cache-Control' => 'public, must-revalidate, max-age=0',
'Pragma' => 'public',
'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Disposition' => 'attachment; filename="' . $exportName . '"'
];
return response()->file($filePath, $headers);
}
}