1189 lines
55 KiB
PHP
1189 lines
55 KiB
PHP
<?php
|
|
|
|
namespace Modules\DailyReports\Http\Controllers;
|
|
|
|
use App\Http\Controllers\Controller;
|
|
use Carbon\Carbon;
|
|
use Illuminate\Http\Request;
|
|
use Modules\DailyReports\Models\DailyReport;
|
|
use Modules\ProjectManagement\Models\Project;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
|
use PhpOffice\PhpSpreadsheet\Style\Border;
|
|
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
|
|
|
class DailyReportExportController extends Controller
|
|
{
|
|
public function export(DailyReport $dailyReport)
|
|
{
|
|
$dailyReport->load(['project', 'user', 'labors', 'equipment', 'activities', 'materials', 'issues']);
|
|
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('Daily Report');
|
|
$sheet->setShowGridlines(true);
|
|
|
|
// Styling helpers
|
|
$headerStyle = [
|
|
'font' => [
|
|
'name' => 'Arial',
|
|
'size' => 16,
|
|
'bold' => true,
|
|
'color' => ['rgb' => 'FFFFFF'],
|
|
],
|
|
'fill' => [
|
|
'fillType' => Fill::FILL_SOLID,
|
|
'startColor' => ['rgb' => '334155'], // Slate 700
|
|
],
|
|
'alignment' => [
|
|
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
|
'vertical' => Alignment::VERTICAL_CENTER,
|
|
],
|
|
];
|
|
|
|
$subHeaderStyle = [
|
|
'font' => [
|
|
'name' => 'Arial',
|
|
'size' => 11,
|
|
'bold' => true,
|
|
'color' => ['rgb' => '334155'],
|
|
],
|
|
'fill' => [
|
|
'fillType' => Fill::FILL_SOLID,
|
|
'startColor' => ['rgb' => 'F1F5F9'], // Slate 100
|
|
],
|
|
'alignment' => [
|
|
'vertical' => Alignment::VERTICAL_CENTER,
|
|
],
|
|
'borders' => [
|
|
'bottom' => [
|
|
'borderStyle' => Border::BORDER_MEDIUM,
|
|
'color' => ['rgb' => 'CBD5E1'],
|
|
],
|
|
],
|
|
];
|
|
|
|
$sectionTitleStyle = [
|
|
'font' => [
|
|
'name' => 'Arial',
|
|
'size' => 12,
|
|
'bold' => true,
|
|
'color' => ['rgb' => '0F172A'], // Slate 900
|
|
],
|
|
'fill' => [
|
|
'fillType' => Fill::FILL_SOLID,
|
|
'startColor' => ['rgb' => 'E2E8F0'], // Slate 200
|
|
],
|
|
'alignment' => [
|
|
'vertical' => Alignment::VERTICAL_CENTER,
|
|
],
|
|
];
|
|
|
|
$boldLabelStyle = [
|
|
'font' => [
|
|
'name' => 'Arial',
|
|
'size' => 10,
|
|
'bold' => true,
|
|
'color' => ['rgb' => '475569'],
|
|
],
|
|
];
|
|
|
|
$tableHeaderStyle = [
|
|
'font' => [
|
|
'name' => 'Arial',
|
|
'size' => 10,
|
|
'bold' => true,
|
|
'color' => ['rgb' => '475569'],
|
|
],
|
|
'fill' => [
|
|
'fillType' => Fill::FILL_SOLID,
|
|
'startColor' => ['rgb' => 'F8FAFC'],
|
|
],
|
|
'borders' => [
|
|
'bottom' => [
|
|
'borderStyle' => Border::BORDER_THIN,
|
|
'color' => ['rgb' => 'E2E8F0'],
|
|
],
|
|
],
|
|
];
|
|
|
|
$borderBottomThin = [
|
|
'borders' => [
|
|
'bottom' => [
|
|
'borderStyle' => Border::BORDER_THIN,
|
|
'color' => ['rgb' => 'F1F5F9'],
|
|
],
|
|
],
|
|
];
|
|
|
|
// 1. Title Block
|
|
$sheet->mergeCells('A1:F1');
|
|
$sheet->setCellValue('A1', 'GSB CONSTRUCTION - DAILY SITE REPORT');
|
|
$sheet->getStyle('A1:F1')->applyFromArray($headerStyle);
|
|
$sheet->getRowDimension(1)->setRowHeight(40);
|
|
|
|
// 2. Metadata Block
|
|
$sheet->mergeCells('A3:F3');
|
|
$sheet->setCellValue('A3', ' REPORT METADATA');
|
|
$sheet->getStyle('A3:F3')->applyFromArray($subHeaderStyle);
|
|
$sheet->getRowDimension(3)->setRowHeight(25);
|
|
|
|
// Row 4
|
|
$sheet->setCellValue('A4', 'Report Number:');
|
|
$sheet->setCellValue('B4', $dailyReport->report_number);
|
|
$sheet->setCellValue('C4', 'Report Date:');
|
|
$sheet->setCellValue('D4', $dailyReport->report_date ? $dailyReport->report_date->format('Y-m-d') : 'N/A');
|
|
$sheet->setCellValue('E4', 'Prepared By:');
|
|
$sheet->setCellValue('F4', $dailyReport->user ? $dailyReport->user->name : 'N/A');
|
|
|
|
// Row 5
|
|
$sheet->setCellValue('A5', 'Project Code:');
|
|
$sheet->setCellValue('B5', $dailyReport->project ? $dailyReport->project->code : 'N/A');
|
|
$sheet->setCellValue('C5', 'Project Name:');
|
|
$sheet->mergeCells('D5:F5');
|
|
$sheet->setCellValue('D5', $dailyReport->project ? $dailyReport->project->name : 'N/A');
|
|
|
|
// Row 6
|
|
$sheet->setCellValue('A6', 'Weather:');
|
|
$sheet->setCellValue('B6', $dailyReport->weather ?: 'N/A');
|
|
$sheet->setCellValue('C6', 'Temperature:');
|
|
$sheet->setCellValue('D6', $dailyReport->temperature ? $dailyReport->temperature . ' °C' : 'N/A');
|
|
$sheet->setCellValue('E6', 'Precipitation:');
|
|
$sheet->setCellValue('F6', $dailyReport->precipitation ?: 'N/A');
|
|
|
|
// Row 7
|
|
$sheet->setCellValue('A7', 'Wind:');
|
|
$sheet->setCellValue('B7', $dailyReport->wind ?: 'N/A');
|
|
|
|
$sheet->getStyle('A4')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C4')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('E4')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('A5')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C5')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('A6')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C6')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('E6')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('A7')->applyFromArray($boldLabelStyle);
|
|
|
|
// 2.5. Summary Dashboard Block
|
|
$sheet->mergeCells('A9:F9');
|
|
$sheet->setCellValue('A9', ' SUMMARY & KEY METRICS');
|
|
$sheet->getStyle('A9:F9')->applyFromArray($subHeaderStyle);
|
|
$sheet->getRowDimension(9)->setRowHeight(25);
|
|
|
|
// Calculate summary metrics
|
|
$totalWorkers = 0;
|
|
$totalManHours = 0;
|
|
foreach ($dailyReport->labors as $lab) {
|
|
$totalWorkers += $lab->workers_count;
|
|
$totalManHours += $lab->workers_count * ($lab->hours ?: 0);
|
|
}
|
|
$totalEquipmentHours = $dailyReport->equipment->sum('hours_used') ?: 0;
|
|
$avgProgress = $dailyReport->activities->isEmpty() ? 0 : ($dailyReport->activities->avg('percentage_completed') ?: 0);
|
|
|
|
// Row 10
|
|
$sheet->setCellValue('A10', 'Tasks Logged:');
|
|
$sheet->setCellValue('B10', count($dailyReport->activities));
|
|
$sheet->setCellValue('C10', 'Materials Logged:');
|
|
$sheet->setCellValue('D10', count($dailyReport->materials));
|
|
$sheet->setCellValue('E10', 'Manpower Count:');
|
|
$sheet->setCellValue('F10', $totalWorkers);
|
|
|
|
// Row 11
|
|
$sheet->setCellValue('A11', 'Avg. Progress:');
|
|
$sheet->setCellValue('B11', number_format($avgProgress, 1) . '%');
|
|
$sheet->setCellValue('C11', 'Reported Issues:');
|
|
$sheet->setCellValue('D11', count($dailyReport->issues));
|
|
$sheet->setCellValue('E11', 'Total Man-Hours:');
|
|
$sheet->setCellValue('F11', number_format($totalManHours, 1) . ' hrs');
|
|
|
|
// Row 12
|
|
$sheet->setCellValue('A12', 'Active Equipment:');
|
|
$sheet->setCellValue('B12', count($dailyReport->equipment));
|
|
$sheet->setCellValue('C12', 'Equipment Hours:');
|
|
$sheet->setCellValue('D12', number_format($totalEquipmentHours, 1) . ' hrs');
|
|
|
|
$sheet->getStyle('A10')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C10')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('E10')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('A11')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C11')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('E11')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('A12')->applyFromArray($boldLabelStyle);
|
|
$sheet->getStyle('C12')->applyFromArray($boldLabelStyle);
|
|
|
|
$rowIdx = 14;
|
|
|
|
// 3. Section A: Tasks
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' 1. TASKS');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($sectionTitleStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(25);
|
|
$rowIdx++;
|
|
|
|
// Table Headers
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'Task Name / Activity');
|
|
$sheet->setCellValue("D{$rowIdx}", 'Zone / Area');
|
|
$sheet->setCellValue("E{$rowIdx}", 'Qty Completed');
|
|
$sheet->setCellValue("F{$rowIdx}", 'Completion %');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
if ($dailyReport->activities->isEmpty()) {
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'No work activities reported for this shift.');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray(['font' => ['italic' => true, 'color' => ['rgb' => '94A3B8']]]);
|
|
$rowIdx++;
|
|
} else {
|
|
foreach ($dailyReport->activities as $act) {
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", $act->task_name);
|
|
$sheet->setCellValue("D{$rowIdx}", $act->zone_area ?: 'N/A');
|
|
$sheet->setCellValue("E{$rowIdx}", $act->quantity_completed ?: 'N/A');
|
|
$sheet->setCellValue("F{$rowIdx}", $act->percentage_completed ? $act->percentage_completed . '%' : '0%');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($borderBottomThin);
|
|
$rowIdx++;
|
|
}
|
|
}
|
|
$rowIdx++;
|
|
|
|
// 4. Section B: Materials
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' 2. MATERIALS');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($sectionTitleStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(25);
|
|
$rowIdx++;
|
|
|
|
// Table Headers
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'Material Name');
|
|
$sheet->setCellValue("D{$rowIdx}", 'Quantity Received / Used');
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", 'Condition / Remarks');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
if ($dailyReport->materials->isEmpty()) {
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'No materials logged for this date.');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray(['font' => ['italic' => true, 'color' => ['rgb' => '94A3B8']]]);
|
|
$rowIdx++;
|
|
} else {
|
|
foreach ($dailyReport->materials as $mat) {
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", $mat->material_name);
|
|
$sheet->setCellValue("D{$rowIdx}", $mat->quantity_received);
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", $mat->condition ?: 'Good');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($borderBottomThin);
|
|
$rowIdx++;
|
|
}
|
|
}
|
|
$rowIdx++;
|
|
|
|
// 5. Section C: Labor & Manpower
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' 3. MANPOWER/LABOR');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($sectionTitleStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(25);
|
|
$rowIdx++;
|
|
|
|
// Table Headers
|
|
$sheet->mergeCells("A{$rowIdx}:B{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'Labor Trade / Profession');
|
|
$sheet->setCellValue("C{$rowIdx}", 'Workers Count');
|
|
$sheet->setCellValue("D{$rowIdx}", 'Hours Rendered');
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", 'Notes / Details');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
if ($dailyReport->labors->isEmpty()) {
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'No labor resources logged.');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray(['font' => ['italic' => true, 'color' => ['rgb' => '94A3B8']]]);
|
|
$rowIdx++;
|
|
} else {
|
|
foreach ($dailyReport->labors as $lab) {
|
|
$sheet->mergeCells("A{$rowIdx}:B{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", $lab->trade);
|
|
$sheet->setCellValue("C{$rowIdx}", $lab->workers_count);
|
|
$sheet->setCellValue("D{$rowIdx}", $lab->hours);
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", $lab->notes ?: '');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($borderBottomThin);
|
|
$rowIdx++;
|
|
}
|
|
}
|
|
$rowIdx++;
|
|
|
|
// 6. Section D: Equipment Usage
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' 4. EQUIPMENT');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($sectionTitleStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(25);
|
|
$rowIdx++;
|
|
|
|
// Table Headers
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'Equipment Name');
|
|
$sheet->setCellValue("D{$rowIdx}", 'Hours Used');
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", 'Status (Active / Idle)');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
if ($dailyReport->equipment->isEmpty()) {
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'No equipment usage logged.');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray(['font' => ['italic' => true, 'color' => ['rgb' => '94A3B8']]]);
|
|
$rowIdx++;
|
|
} else {
|
|
foreach ($dailyReport->equipment as $eq) {
|
|
$sheet->mergeCells("A{$rowIdx}:C{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", $eq->equipment_name);
|
|
$sheet->setCellValue("D{$rowIdx}", $eq->hours_used);
|
|
$sheet->mergeCells("E{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("E{$rowIdx}", ucfirst($eq->status));
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($borderBottomThin);
|
|
$rowIdx++;
|
|
}
|
|
}
|
|
$rowIdx++;
|
|
|
|
// 7. Section E: Reported Issues & Delays
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' 5. ISSUES & CONCERN');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($sectionTitleStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(25);
|
|
$rowIdx++;
|
|
|
|
// Table Headers
|
|
$sheet->mergeCells("A{$rowIdx}:B{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'Issue / Delay Type');
|
|
$sheet->mergeCells("C{$rowIdx}:E{$rowIdx}");
|
|
$sheet->setCellValue("C{$rowIdx}", 'Description');
|
|
$sheet->setCellValue("F{$rowIdx}", 'Delay Impact (Hrs)');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
if ($dailyReport->issues->isEmpty()) {
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", 'No major issues or delays reported.');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray(['font' => ['italic' => true, 'color' => ['rgb' => '94A3B8']]]);
|
|
$rowIdx++;
|
|
} else {
|
|
foreach ($dailyReport->issues as $iss) {
|
|
$sheet->mergeCells("A{$rowIdx}:B{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ucfirst(str_replace('_', ' ', $iss->issue_type)));
|
|
$sheet->mergeCells("C{$rowIdx}:E{$rowIdx}");
|
|
$sheet->setCellValue("C{$rowIdx}", $iss->description);
|
|
$sheet->setCellValue("F{$rowIdx}", $iss->delay_impact ?: '0');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($borderBottomThin);
|
|
$rowIdx++;
|
|
}
|
|
}
|
|
$rowIdx++;
|
|
|
|
// 8. General Remarks
|
|
$sheet->mergeCells("A{$rowIdx}:F{$rowIdx}");
|
|
$sheet->setCellValue("A{$rowIdx}", ' GENERAL REMARKS & NOTES');
|
|
$sheet->getStyle("A{$rowIdx}:F{$rowIdx}")->applyFromArray($subHeaderStyle);
|
|
$rowIdx++;
|
|
|
|
$sheet->mergeCells("A{$rowIdx}:F" . ($rowIdx + 3));
|
|
$sheet->setCellValue("A{$rowIdx}", $dailyReport->remarks ?: 'No additional remarks entered.');
|
|
$sheet->getStyle("A{$rowIdx}:F" . ($rowIdx + 3))->applyFromArray([
|
|
'alignment' => [
|
|
'vertical' => Alignment::VERTICAL_TOP,
|
|
'wrapText' => true
|
|
],
|
|
'font' => ['italic' => !$dailyReport->remarks]
|
|
]);
|
|
|
|
// Auto-fit column widths
|
|
foreach (range('A', 'F') as $col) {
|
|
$sheet->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// Output Excel response
|
|
$filename = 'DailyReport_' . ($dailyReport->project ? $dailyReport->project->code : '') . '_' . $dailyReport->report_date->format('Ymd') . '.xlsx';
|
|
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
header('Content-Disposition: attachment;filename="' . $filename . '"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writer->save('php://output');
|
|
exit;
|
|
}
|
|
|
|
public function pdf(DailyReport $dailyReport)
|
|
{
|
|
$dailyReport->load(['project', 'user', 'labors', 'equipment', 'activities', 'materials', 'issues']);
|
|
|
|
// Calculate summary metrics
|
|
$totalWorkers = 0;
|
|
$totalManHours = 0;
|
|
foreach ($dailyReport->labors as $lab) {
|
|
$totalWorkers += $lab->workers_count;
|
|
$totalManHours += $lab->workers_count * ($lab->hours ?: 0);
|
|
}
|
|
|
|
$totalEquipmentHours = 0;
|
|
foreach ($dailyReport->equipment as $eq) {
|
|
$totalEquipmentHours += $eq->hours_used ?: 0;
|
|
}
|
|
|
|
$avgProgress = 0;
|
|
if (!$dailyReport->activities->isEmpty()) {
|
|
$avgProgress = $dailyReport->activities->avg('percentage_completed') ?: 0;
|
|
}
|
|
|
|
$summary = [
|
|
'total_workers' => $totalWorkers,
|
|
'total_man_hours' => $totalManHours,
|
|
'total_equipment_hours' => $totalEquipmentHours,
|
|
'avg_progress' => $avgProgress,
|
|
];
|
|
|
|
$pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('dailyreports::pdf.daily-report', [
|
|
'report' => $dailyReport,
|
|
'summary' => $summary,
|
|
]);
|
|
|
|
$filename = 'DailyReport_' . ($dailyReport->project ? $dailyReport->project->code : '') . '_' . $dailyReport->report_date->format('Ymd') . '.pdf';
|
|
|
|
return $pdf->download($filename);
|
|
}
|
|
|
|
public function exportRange(Request $request)
|
|
{
|
|
$request->validate([
|
|
'start_date' => 'required|date',
|
|
'end_date' => 'required|date|after_or_equal:start_date',
|
|
'project' => 'nullable|string',
|
|
]);
|
|
|
|
$startDate = Carbon::parse($request->input('start_date'))->startOfDay();
|
|
$endDate = Carbon::parse($request->input('end_date'))->endOfDay();
|
|
$projectUlid = $request->input('project');
|
|
|
|
$project = $projectUlid ? Project::where('ulid', $projectUlid)->first() : null;
|
|
|
|
$query = DailyReport::with(['project', 'user', 'labors', 'equipment', 'activities', 'materials', 'issues'])
|
|
->whereBetween('report_date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
|
|
->orderBy('report_date', 'asc');
|
|
|
|
if ($project) {
|
|
$query->where('project_id', $project->id);
|
|
}
|
|
|
|
$reports = $query->get();
|
|
|
|
// 1. Manpower Category Aggregation
|
|
$allLabors = $reports->flatMap->labors;
|
|
$totalPeriodManHours = $allLabors->sum(fn ($l) => $l->workers_count * ($l->hours ?: 0));
|
|
$laborCategorySummary = $allLabors->groupBy(fn ($l) => $l->trade ?: 'Unspecified Trade')
|
|
->map(function ($group, $trade) use ($totalPeriodManHours) {
|
|
$workerDays = $group->sum('workers_count');
|
|
$manHours = $group->sum(fn ($l) => $l->workers_count * ($l->hours ?: 0));
|
|
$activeCount = $group->count();
|
|
$percent = $totalPeriodManHours > 0 ? ($manHours / $totalPeriodManHours) * 100 : 0;
|
|
|
|
return [
|
|
'trade' => $trade,
|
|
'worker_days' => $workerDays,
|
|
'total_man_hours' => $manHours,
|
|
'days_active' => $activeCount,
|
|
'percent_share' => round($percent, 1),
|
|
];
|
|
})->values()->sortByDesc('total_man_hours')->values();
|
|
|
|
// 2. Equipment Category Aggregation
|
|
$allEquipment = $reports->flatMap->equipment;
|
|
$totalPeriodEquipHours = $allEquipment->sum('hours_used');
|
|
$equipmentCategorySummary = $allEquipment->groupBy(fn ($eq) => $eq->equipment_name ?: 'Unspecified Equipment')
|
|
->map(function ($group, $name) use ($totalPeriodEquipHours) {
|
|
$hours = $group->sum('hours_used');
|
|
$activeCount = $group->count();
|
|
$percent = $totalPeriodEquipHours > 0 ? ($hours / $totalPeriodEquipHours) * 100 : 0;
|
|
$latestStatus = $group->last()->status ?? 'Active';
|
|
|
|
return [
|
|
'equipment_name' => $name,
|
|
'total_hours' => $hours,
|
|
'days_active' => $activeCount,
|
|
'percent_share' => round($percent, 1),
|
|
'latest_status' => $latestStatus,
|
|
];
|
|
})->values()->sortByDesc('total_hours')->values();
|
|
|
|
// 3. Materials Category Aggregation
|
|
$allMaterials = $reports->flatMap->materials;
|
|
$materialsCategorySummary = $allMaterials->groupBy(fn ($m) => $m->material_name ?: 'General Material')
|
|
->map(function ($group, $name) {
|
|
$deliveriesCount = $group->count();
|
|
$quantities = $group->pluck('quantity_received')->filter()->all();
|
|
$numericSum = 0;
|
|
$unit = '';
|
|
foreach ($quantities as $q) {
|
|
if (preg_match('/^([0-9.]+)\s*(.*)$/', trim($q), $matches)) {
|
|
$numericSum += (float) $matches[1];
|
|
if (empty($unit) && !empty($matches[2])) {
|
|
$unit = $matches[2];
|
|
}
|
|
}
|
|
}
|
|
$qtyDisplay = $numericSum > 0 ? (string)$numericSum : (implode(', ', array_slice($quantities, 0, 3)) ?: '—');
|
|
$conditions = $group->pluck('condition')->filter()->unique()->implode(', ') ?: 'Good';
|
|
|
|
return [
|
|
'material_name' => $name,
|
|
'total_quantity' => $qtyDisplay,
|
|
'unit' => $unit ?: 'units',
|
|
'deliveries_count' => $deliveriesCount,
|
|
'condition_summary' => $conditions,
|
|
];
|
|
})->values()->sortByDesc('deliveries_count')->values();
|
|
|
|
// 4. Activities Category Aggregation
|
|
$allActivities = $reports->flatMap->activities;
|
|
$activitiesCategorySummary = $allActivities->groupBy(fn ($a) => $a->task_name ?: 'General Works')
|
|
->map(function ($group, $name) {
|
|
$zones = $group->pluck('zone_area')->filter()->unique()->implode(', ') ?: 'Site Wide';
|
|
$highestProgress = $group->max('percentage_completed') ?: 0;
|
|
$daysWorked = $group->count();
|
|
|
|
return [
|
|
'task_name' => $name,
|
|
'zones' => $zones,
|
|
'highest_progress' => $highestProgress,
|
|
'days_worked' => $daysWorked,
|
|
];
|
|
})->values()->sortByDesc('days_worked')->values();
|
|
|
|
// 5. Issues Category Aggregation
|
|
$allIssues = $reports->flatMap->issues;
|
|
$issuesCategorySummary = $allIssues->groupBy(fn ($i) => $i->issue_type ?: 'General')
|
|
->map(function ($group, $type) {
|
|
$count = $group->count();
|
|
$resolved = $group->whereNotNull('resolved_at')->count();
|
|
$delays = $group->pluck('delay_impact')->filter()->all();
|
|
|
|
return [
|
|
'issue_type' => $type,
|
|
'incident_count' => $count,
|
|
'resolved_count' => $resolved,
|
|
'pending_count' => $count - $resolved,
|
|
'delay_impact_summary' => !empty($delays) ? implode('; ', array_slice($delays, 0, 3)) : 'None reported',
|
|
];
|
|
})->values()->sortByDesc('incident_count')->values();
|
|
|
|
$spreadsheet = new Spreadsheet();
|
|
|
|
// Style definitions
|
|
$headerStyle = [
|
|
'font' => ['name' => 'Arial', 'size' => 13, 'bold' => true, 'color' => ['rgb' => 'FFFFFF']],
|
|
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1E293B']], // Slate 800
|
|
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
|
|
];
|
|
|
|
$sectionHeaderStyle = [
|
|
'font' => ['name' => 'Arial', 'size' => 11, 'bold' => true, 'color' => ['rgb' => 'FFFFFF']],
|
|
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '0F766E']], // Teal 700
|
|
'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
|
|
];
|
|
|
|
$tableHeaderStyle = [
|
|
'font' => ['name' => 'Arial', 'size' => 9.5, 'bold' => true, 'color' => ['rgb' => 'FFFFFF']],
|
|
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '334155']], // Slate 700
|
|
'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
|
|
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '64748B']]],
|
|
];
|
|
|
|
$totalRowStyle = [
|
|
'font' => ['name' => 'Arial', 'size' => 9.5, 'bold' => true, 'color' => ['rgb' => '0F172A']],
|
|
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E2E8F0']], // Slate 200
|
|
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => '94A3B8']]],
|
|
];
|
|
|
|
$thinBorder = [
|
|
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'CBD5E1']]],
|
|
];
|
|
|
|
// ----------------------------------------------------
|
|
// 1. SHEET: CONSOLIDATED OVERVIEW & DAILY SUMMARY
|
|
// ----------------------------------------------------
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('Consolidated Overview');
|
|
$sheet->setShowGridlines(true);
|
|
|
|
$sheet->mergeCells('A1:J1');
|
|
$sheet->setCellValue('A1', 'CONSOLIDATED DAILY CONSTRUCTION REPORTS — PERIOD OVERVIEW');
|
|
$sheet->getStyle('A1:J1')->applyFromArray($headerStyle);
|
|
$sheet->getRowDimension(1)->setRowHeight(32);
|
|
|
|
$sheet->setCellValue('A2', 'Project: ' . ($project ? "{$project->name} ({$project->code})" : 'All Projects'));
|
|
$sheet->setCellValue('A3', 'Date Range: ' . $startDate->format('M d, Y') . ' to ' . $endDate->format('M d, Y'));
|
|
$sheet->setCellValue('A4', 'Total Reports Filed: ' . $reports->count() . ' day(s)');
|
|
$sheet->setCellValue('D2', 'Total Man-Days: ' . number_format($allLabors->sum('workers_count')));
|
|
$sheet->setCellValue('D3', 'Total Man-Hours: ' . number_format($totalPeriodManHours, 1) . ' hrs');
|
|
$sheet->setCellValue('D4', 'Total Equip. Hours: ' . number_format($totalPeriodEquipHours, 1) . ' hrs');
|
|
$sheet->setCellValue('G2', 'Activities Logged: ' . $allActivities->count());
|
|
$sheet->setCellValue('G3', 'Materials Tracked: ' . $allMaterials->count());
|
|
$sheet->setCellValue('G4', 'Issues & Delays: ' . $allIssues->count());
|
|
$sheet->getStyle('A2:G4')->getFont()->setBold(true)->setSize(9.5);
|
|
|
|
$headers = ['Date', 'Report #', 'Project', 'Prepared By', 'Weather', 'Workers', 'Man-Hours', 'Equip. Hours', 'Tasks', 'Issues'];
|
|
$cols = range('A', 'J');
|
|
$rowIdx = 6;
|
|
|
|
foreach ($headers as $k => $h) {
|
|
$sheet->setCellValue("{$cols[$k]}{$rowIdx}", $h);
|
|
}
|
|
$sheet->getStyle("A{$rowIdx}:J{$rowIdx}")->applyFromArray($tableHeaderStyle);
|
|
$sheet->getRowDimension($rowIdx)->setRowHeight(24);
|
|
$rowIdx++;
|
|
|
|
foreach ($reports as $r) {
|
|
$workers = $r->labors->sum('workers_count');
|
|
$manHours = $r->labors->sum(fn ($l) => $l->workers_count * ($l->hours ?: 0));
|
|
$equipHours = $r->equipment->sum('hours_used');
|
|
|
|
$sheet->setCellValue("A{$rowIdx}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheet->setCellValue("B{$rowIdx}", $r->report_number ?: '#' . $r->id);
|
|
$sheet->setCellValue("C{$rowIdx}", $r->project ? $r->project->code : '—');
|
|
$sheet->setCellValue("D{$rowIdx}", $r->user ? $r->user->name : 'N/A');
|
|
$sheet->setCellValue("E{$rowIdx}", $r->weather ?: '—');
|
|
$sheet->setCellValue("F{$rowIdx}", $workers);
|
|
$sheet->setCellValue("G{$rowIdx}", number_format($manHours, 1));
|
|
$sheet->setCellValue("H{$rowIdx}", number_format($equipHours, 1));
|
|
$sheet->setCellValue("I{$rowIdx}", $r->activities->count());
|
|
$sheet->setCellValue("J{$rowIdx}", $r->issues->count());
|
|
|
|
$sheet->getStyle("A{$rowIdx}:J{$rowIdx}")->applyFromArray($thinBorder);
|
|
$rowIdx++;
|
|
}
|
|
|
|
// Summary Row
|
|
$sheet->setCellValue("A{$rowIdx}", 'TOTAL FOR PERIOD');
|
|
$sheet->mergeCells("A{$rowIdx}:E{$rowIdx}");
|
|
$sheet->setCellValue("F{$rowIdx}", $allLabors->sum('workers_count'));
|
|
$sheet->setCellValue("G{$rowIdx}", number_format($totalPeriodManHours, 1));
|
|
$sheet->setCellValue("H{$rowIdx}", number_format($totalPeriodEquipHours, 1));
|
|
$sheet->setCellValue("I{$rowIdx}", $allActivities->count());
|
|
$sheet->setCellValue("J{$rowIdx}", $allIssues->count());
|
|
$sheet->getStyle("A{$rowIdx}:J{$rowIdx}")->applyFromArray($totalRowStyle);
|
|
|
|
foreach ($cols as $col) {
|
|
$sheet->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// ----------------------------------------------------
|
|
// 2. SHEET: MANPOWER & LABOR CATEGORY SUMMATION
|
|
// ----------------------------------------------------
|
|
$sheetLabor = $spreadsheet->createSheet();
|
|
$sheetLabor->setTitle('Manpower by Category');
|
|
$sheetLabor->setShowGridlines(true);
|
|
|
|
$sheetLabor->mergeCells('A1:F1');
|
|
$sheetLabor->setCellValue('A1', 'MANPOWER & LABOR — SUMMATION PER TRADE / CATEGORY');
|
|
$sheetLabor->getStyle('A1:F1')->applyFromArray($sectionHeaderStyle);
|
|
$sheetLabor->getRowDimension(1)->setRowHeight(28);
|
|
|
|
$labSumHeaders = ['Trade / Labor Category', 'Total Worker-Days', 'Total Man-Hours (hrs)', 'Days Active', 'Avg Workers / Active Day', '% Share'];
|
|
$labSumCols = range('A', 'F');
|
|
$lRow = 3;
|
|
|
|
foreach ($labSumHeaders as $k => $h) {
|
|
$sheetLabor->setCellValue("{$labSumCols[$k]}{$lRow}", $h);
|
|
}
|
|
$sheetLabor->getStyle("A{$lRow}:F{$lRow}")->applyFromArray($tableHeaderStyle);
|
|
$lRow++;
|
|
|
|
foreach ($laborCategorySummary as $cat) {
|
|
$avgW = $cat['days_active'] > 0 ? round($cat['worker_days'] / $cat['days_active'], 1) : $cat['worker_days'];
|
|
$sheetLabor->setCellValue("A{$lRow}", $cat['trade']);
|
|
$sheetLabor->setCellValue("B{$lRow}", $cat['worker_days']);
|
|
$sheetLabor->setCellValue("C{$lRow}", number_format($cat['total_man_hours'], 1));
|
|
$sheetLabor->setCellValue("D{$lRow}", $cat['days_active']);
|
|
$sheetLabor->setCellValue("E{$lRow}", $avgW);
|
|
$sheetLabor->setCellValue("F{$lRow}", $cat['percent_share'] . '%');
|
|
$sheetLabor->getStyle("A{$lRow}:F{$lRow}")->applyFromArray($thinBorder);
|
|
$lRow++;
|
|
}
|
|
|
|
// Total Row
|
|
$sheetLabor->setCellValue("A{$lRow}", 'TOTAL MANPOWER');
|
|
$sheetLabor->setCellValue("B{$lRow}", $allLabors->sum('workers_count'));
|
|
$sheetLabor->setCellValue("C{$lRow}", number_format($totalPeriodManHours, 1));
|
|
$sheetLabor->setCellValue("D{$lRow}", $reports->count());
|
|
$sheetLabor->setCellValue("E{$lRow}", $reports->count() > 0 ? round($allLabors->sum('workers_count') / $reports->count(), 1) : 0);
|
|
$sheetLabor->setCellValue("F{$lRow}", '100.0%');
|
|
$sheetLabor->getStyle("A{$lRow}:F{$lRow}")->applyFromArray($totalRowStyle);
|
|
$lRow += 3;
|
|
|
|
// Itemized Daily Log Section
|
|
$sheetLabor->mergeCells("A{$lRow}:G{$lRow}");
|
|
$sheetLabor->setCellValue("A{$lRow}", 'ITEMIZED DAILY MANPOWER LOG');
|
|
$sheetLabor->getStyle("A{$lRow}:G{$lRow}")->applyFromArray($headerStyle);
|
|
$lRow++;
|
|
|
|
$labDetailHeaders = ['Date', 'Report #', 'Trade / Category', 'Workers Count', 'Hours/Worker', 'Total Man-Hours', 'Notes / Location'];
|
|
$labDetailCols = range('A', 'G');
|
|
foreach ($labDetailHeaders as $k => $h) {
|
|
$sheetLabor->setCellValue("{$labDetailCols[$k]}{$lRow}", $h);
|
|
}
|
|
$sheetLabor->getStyle("A{$lRow}:G{$lRow}")->applyFromArray($tableHeaderStyle);
|
|
$lRow++;
|
|
|
|
foreach ($reports as $r) {
|
|
foreach ($r->labors as $lab) {
|
|
$totalH = $lab->workers_count * ($lab->hours ?: 0);
|
|
$sheetLabor->setCellValue("A{$lRow}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheetLabor->setCellValue("B{$lRow}", $r->report_number ?: '#' . $r->id);
|
|
$sheetLabor->setCellValue("C{$lRow}", $lab->trade ?: 'Labor');
|
|
$sheetLabor->setCellValue("D{$lRow}", $lab->workers_count);
|
|
$sheetLabor->setCellValue("E{$lRow}", $lab->hours ?: 0);
|
|
$sheetLabor->setCellValue("F{$lRow}", number_format($totalH, 1));
|
|
$sheetLabor->setCellValue("G{$lRow}", $lab->notes ?: '—');
|
|
$sheetLabor->getStyle("A{$lRow}:G{$lRow}")->applyFromArray($thinBorder);
|
|
$lRow++;
|
|
}
|
|
}
|
|
foreach ($labDetailCols as $col) {
|
|
$sheetLabor->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// ----------------------------------------------------
|
|
// 3. SHEET: EQUIPMENT & MACHINERY CATEGORY SUMMATION
|
|
// ----------------------------------------------------
|
|
$sheetEquip = $spreadsheet->createSheet();
|
|
$sheetEquip->setTitle('Equipment by Category');
|
|
$sheetEquip->setShowGridlines(true);
|
|
|
|
$sheetEquip->mergeCells('A1:F1');
|
|
$sheetEquip->setCellValue('A1', 'EQUIPMENT & MACHINERY — SUMMATION PER MODEL / CATEGORY');
|
|
$sheetEquip->getStyle('A1:F1')->applyFromArray($sectionHeaderStyle);
|
|
$sheetEquip->getRowDimension(1)->setRowHeight(28);
|
|
|
|
$eqSumHeaders = ['Equipment Name / Model', 'Total Hours Operated (hrs)', 'Days Utilized', 'Avg Hours / Utilized Day', 'Latest Status', '% Share'];
|
|
$eqSumCols = range('A', 'F');
|
|
$eRow = 3;
|
|
|
|
foreach ($eqSumHeaders as $k => $h) {
|
|
$sheetEquip->setCellValue("{$eqSumCols[$k]}{$eRow}", $h);
|
|
}
|
|
$sheetEquip->getStyle("A{$eRow}:F{$eRow}")->applyFromArray($tableHeaderStyle);
|
|
$eRow++;
|
|
|
|
foreach ($equipmentCategorySummary as $eqCat) {
|
|
$avgH = $eqCat['days_active'] > 0 ? round($eqCat['total_hours'] / $eqCat['days_active'], 1) : $eqCat['total_hours'];
|
|
$sheetEquip->setCellValue("A{$eRow}", $eqCat['equipment_name']);
|
|
$sheetEquip->setCellValue("B{$eRow}", number_format($eqCat['total_hours'], 1));
|
|
$sheetEquip->setCellValue("C{$eRow}", $eqCat['days_active']);
|
|
$sheetEquip->setCellValue("D{$eRow}", $avgH);
|
|
$sheetEquip->setCellValue("E{$eRow}", $eqCat['latest_status']);
|
|
$sheetEquip->setCellValue("F{$eRow}", $eqCat['percent_share'] . '%');
|
|
$sheetEquip->getStyle("A{$eRow}:F{$eRow}")->applyFromArray($thinBorder);
|
|
$eRow++;
|
|
}
|
|
|
|
// Total Row
|
|
$sheetEquip->setCellValue("A{$eRow}", 'TOTAL EQUIPMENT');
|
|
$sheetEquip->setCellValue("B{$eRow}", number_format($totalPeriodEquipHours, 1));
|
|
$sheetEquip->setCellValue("C{$eRow}", $reports->count());
|
|
$sheetEquip->setCellValue("D{$eRow}", $reports->count() > 0 ? round($totalPeriodEquipHours / $reports->count(), 1) : 0);
|
|
$sheetEquip->setCellValue("E{$eRow}", '—');
|
|
$sheetEquip->setCellValue("F{$eRow}", '100.0%');
|
|
$sheetEquip->getStyle("A{$eRow}:F{$eRow}")->applyFromArray($totalRowStyle);
|
|
$eRow += 3;
|
|
|
|
// Itemized Daily Equipment Log
|
|
$sheetEquip->mergeCells("A{$eRow}:E{$eRow}");
|
|
$sheetEquip->setCellValue("A{$eRow}", 'ITEMIZED DAILY EQUIPMENT LOG');
|
|
$sheetEquip->getStyle("A{$eRow}:E{$eRow}")->applyFromArray($headerStyle);
|
|
$eRow++;
|
|
|
|
$eqDetailHeaders = ['Date', 'Report #', 'Equipment Name / Model', 'Hours Used', 'Status'];
|
|
$eqDetailCols = range('A', 'E');
|
|
foreach ($eqDetailHeaders as $k => $h) {
|
|
$sheetEquip->setCellValue("{$eqDetailCols[$k]}{$eRow}", $h);
|
|
}
|
|
$sheetEquip->getStyle("A{$eRow}:E{$eRow}")->applyFromArray($tableHeaderStyle);
|
|
$eRow++;
|
|
|
|
foreach ($reports as $r) {
|
|
foreach ($r->equipment as $eq) {
|
|
$sheetEquip->setCellValue("A{$eRow}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheetEquip->setCellValue("B{$eRow}", $r->report_number ?: '#' . $r->id);
|
|
$sheetEquip->setCellValue("C{$eRow}", $eq->equipment_name ?: 'Equipment');
|
|
$sheetEquip->setCellValue("D{$eRow}", $eq->hours_used ?: 0);
|
|
$sheetEquip->setCellValue("E{$eRow}", $eq->status ?: 'Active');
|
|
$sheetEquip->getStyle("A{$eRow}:E{$eRow}")->applyFromArray($thinBorder);
|
|
$eRow++;
|
|
}
|
|
}
|
|
foreach ($eqDetailCols as $col) {
|
|
$sheetEquip->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// ----------------------------------------------------
|
|
// 4. SHEET: MATERIALS CATEGORY SUMMATION
|
|
// ----------------------------------------------------
|
|
$sheetMat = $spreadsheet->createSheet();
|
|
$sheetMat->setTitle('Materials by Category');
|
|
$sheetMat->setShowGridlines(true);
|
|
|
|
$sheetMat->mergeCells('A1:E1');
|
|
$sheetMat->setCellValue('A1', 'MATERIALS TRACKED — SUMMATION PER MATERIAL CATEGORY');
|
|
$sheetMat->getStyle('A1:E1')->applyFromArray($sectionHeaderStyle);
|
|
$sheetMat->getRowDimension(1)->setRowHeight(28);
|
|
|
|
$matSumHeaders = ['Material Name / Category', 'Cumulative Quantity Received / Used', 'Unit', 'Entries Count', 'Condition Summary'];
|
|
$matSumCols = range('A', 'E');
|
|
$mRow = 3;
|
|
|
|
foreach ($matSumHeaders as $k => $h) {
|
|
$sheetMat->setCellValue("{$matSumCols[$k]}{$mRow}", $h);
|
|
}
|
|
$sheetMat->getStyle("A{$mRow}:E{$mRow}")->applyFromArray($tableHeaderStyle);
|
|
$mRow++;
|
|
|
|
foreach ($materialsCategorySummary as $mCat) {
|
|
$sheetMat->setCellValue("A{$mRow}", $mCat['material_name']);
|
|
$sheetMat->setCellValue("B{$mRow}", $mCat['total_quantity']);
|
|
$sheetMat->setCellValue("C{$mRow}", $mCat['unit']);
|
|
$sheetMat->setCellValue("D{$mRow}", $mCat['deliveries_count']);
|
|
$sheetMat->setCellValue("E{$mRow}", $mCat['condition_summary']);
|
|
$sheetMat->getStyle("A{$mRow}:E{$mRow}")->applyFromArray($thinBorder);
|
|
$mRow++;
|
|
}
|
|
$mRow += 2;
|
|
|
|
// Itemized Daily Materials Log
|
|
$sheetMat->mergeCells("A{$mRow}:E{$mRow}");
|
|
$sheetMat->setCellValue("A{$mRow}", 'ITEMIZED DAILY MATERIALS LOG');
|
|
$sheetMat->getStyle("A{$mRow}:E{$mRow}")->applyFromArray($headerStyle);
|
|
$mRow++;
|
|
|
|
$matDetailHeaders = ['Date', 'Report #', 'Material Name', 'Quantity Received', 'Condition / Remarks'];
|
|
$matDetailCols = range('A', 'E');
|
|
foreach ($matDetailHeaders as $k => $h) {
|
|
$sheetMat->setCellValue("{$matDetailCols[$k]}{$mRow}", $h);
|
|
}
|
|
$sheetMat->getStyle("A{$mRow}:E{$mRow}")->applyFromArray($tableHeaderStyle);
|
|
$mRow++;
|
|
|
|
foreach ($reports as $r) {
|
|
foreach ($r->materials as $mat) {
|
|
$sheetMat->setCellValue("A{$mRow}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheetMat->setCellValue("B{$mRow}", $r->report_number ?: '#' . $r->id);
|
|
$sheetMat->setCellValue("C{$mRow}", $mat->material_name ?: 'Material');
|
|
$sheetMat->setCellValue("D{$mRow}", $mat->quantity_received ?: '—');
|
|
$sheetMat->setCellValue("E{$mRow}", $mat->condition ?: 'Good');
|
|
$sheetMat->getStyle("A{$mRow}:E{$mRow}")->applyFromArray($thinBorder);
|
|
$mRow++;
|
|
}
|
|
}
|
|
foreach ($matDetailCols as $col) {
|
|
$sheetMat->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// ----------------------------------------------------
|
|
// 5. SHEET: ACTIVITIES & WORK PROGRESS
|
|
// ----------------------------------------------------
|
|
$sheetAct = $spreadsheet->createSheet();
|
|
$sheetAct->setTitle('Activities & Progress');
|
|
$sheetAct->setShowGridlines(true);
|
|
|
|
$sheetAct->mergeCells('A1:D1');
|
|
$sheetAct->setCellValue('A1', 'WORK ACTIVITIES & TASKS — PROGRESS BY TASK / AREA');
|
|
$sheetAct->getStyle('A1:D1')->applyFromArray($sectionHeaderStyle);
|
|
$sheetAct->getRowDimension(1)->setRowHeight(28);
|
|
|
|
$actSumHeaders = ['Task Name', 'Location / Zone Area', 'Highest Progress (%)', 'Days Worked'];
|
|
$actSumCols = range('A', 'D');
|
|
$aRow = 3;
|
|
|
|
foreach ($actSumHeaders as $k => $h) {
|
|
$sheetAct->setCellValue("{$actSumCols[$k]}{$aRow}", $h);
|
|
}
|
|
$sheetAct->getStyle("A{$aRow}:D{$aRow}")->applyFromArray($tableHeaderStyle);
|
|
$aRow++;
|
|
|
|
foreach ($activitiesCategorySummary as $aCat) {
|
|
$sheetAct->setCellValue("A{$aRow}", $aCat['task_name']);
|
|
$sheetAct->setCellValue("B{$aRow}", $aCat['zones']);
|
|
$sheetAct->setCellValue("C{$aRow}", $aCat['highest_progress'] . '%');
|
|
$sheetAct->setCellValue("D{$aRow}", $aCat['days_worked']);
|
|
$sheetAct->getStyle("A{$aRow}:D{$aRow}")->applyFromArray($thinBorder);
|
|
$aRow++;
|
|
}
|
|
$aRow += 2;
|
|
|
|
// Itemized Daily Activities Log
|
|
$sheetAct->mergeCells("A{$aRow}:F{$aRow}");
|
|
$sheetAct->setCellValue("A{$aRow}", 'ITEMIZED DAILY ACTIVITIES LOG');
|
|
$sheetAct->getStyle("A{$aRow}:F{$aRow}")->applyFromArray($headerStyle);
|
|
$aRow++;
|
|
|
|
$actDetailHeaders = ['Date', 'Report #', 'Task Name', 'Zone / Area', 'Qty Completed', 'Progress (%)'];
|
|
$actDetailCols = range('A', 'F');
|
|
foreach ($actDetailHeaders as $k => $h) {
|
|
$sheetAct->setCellValue("{$actDetailCols[$k]}{$aRow}", $h);
|
|
}
|
|
$sheetAct->getStyle("A{$aRow}:F{$aRow}")->applyFromArray($tableHeaderStyle);
|
|
$aRow++;
|
|
|
|
foreach ($reports as $r) {
|
|
foreach ($r->activities as $act) {
|
|
$sheetAct->setCellValue("A{$aRow}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheetAct->setCellValue("B{$aRow}", $r->report_number ?: '#' . $r->id);
|
|
$sheetAct->setCellValue("C{$aRow}", $act->task_name ?: '—');
|
|
$sheetAct->setCellValue("D{$aRow}", $act->zone_area ?: '—');
|
|
$sheetAct->setCellValue("E{$aRow}", $act->quantity_completed ?: '—');
|
|
$sheetAct->setCellValue("F{$aRow}", ($act->percentage_completed ?: 0) . '%');
|
|
$sheetAct->getStyle("A{$aRow}:F{$aRow}")->applyFromArray($thinBorder);
|
|
$aRow++;
|
|
}
|
|
}
|
|
foreach ($actDetailCols as $col) {
|
|
$sheetAct->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// ----------------------------------------------------
|
|
// 6. SHEET: ISSUES & DELAYS CATEGORY SUMMATION
|
|
// ----------------------------------------------------
|
|
$sheetIss = $spreadsheet->createSheet();
|
|
$sheetIss->setTitle('Issues & Delays');
|
|
$sheetIss->setShowGridlines(true);
|
|
|
|
$sheetIss->mergeCells('A1:E1');
|
|
$sheetIss->setCellValue('A1', 'ISSUES & DELAYS — SUMMATION PER ISSUE CATEGORY');
|
|
$sheetIss->getStyle('A1:E1')->applyFromArray($sectionHeaderStyle);
|
|
$sheetIss->getRowDimension(1)->setRowHeight(28);
|
|
|
|
$issSumHeaders = ['Issue Category / Type', 'Incident Count', 'Resolved Count', 'Pending Count', 'Delay Impact Summary'];
|
|
$issSumCols = range('A', 'E');
|
|
$iRow = 3;
|
|
|
|
foreach ($issSumHeaders as $k => $h) {
|
|
$sheetIss->setCellValue("{$issSumCols[$k]}{$iRow}", $h);
|
|
}
|
|
$sheetIss->getStyle("A{$iRow}:E{$iRow}")->applyFromArray($tableHeaderStyle);
|
|
$iRow++;
|
|
|
|
foreach ($issuesCategorySummary as $iCat) {
|
|
$sheetIss->setCellValue("A{$iRow}", $iCat['issue_type']);
|
|
$sheetIss->setCellValue("B{$iRow}", $iCat['incident_count']);
|
|
$sheetIss->setCellValue("C{$iRow}", $iCat['resolved_count']);
|
|
$sheetIss->setCellValue("D{$iRow}", $iCat['pending_count']);
|
|
$sheetIss->setCellValue("E{$iRow}", $iCat['delay_impact_summary']);
|
|
$sheetIss->getStyle("A{$iRow}:E{$iRow}")->applyFromArray($thinBorder);
|
|
$iRow++;
|
|
}
|
|
$iRow += 2;
|
|
|
|
// Itemized Daily Issues Log
|
|
$sheetIss->mergeCells("A{$iRow}:F{$iRow}");
|
|
$sheetIss->setCellValue("A{$iRow}", 'ITEMIZED DAILY ISSUES & CONCERNS LOG');
|
|
$sheetIss->getStyle("A{$iRow}:F{$iRow}")->applyFromArray($headerStyle);
|
|
$iRow++;
|
|
|
|
$issDetailHeaders = ['Date', 'Report #', 'Issue Type', 'Description', 'Delay Impact', 'Status'];
|
|
$issDetailCols = range('A', 'F');
|
|
foreach ($issDetailHeaders as $k => $h) {
|
|
$sheetIss->setCellValue("{$issDetailCols[$k]}{$iRow}", $h);
|
|
}
|
|
$sheetIss->getStyle("A{$iRow}:F{$iRow}")->applyFromArray($tableHeaderStyle);
|
|
$iRow++;
|
|
|
|
foreach ($reports as $r) {
|
|
foreach ($r->issues as $iss) {
|
|
$status = $iss->resolved_at ? 'Resolved' : ($iss->status ?: 'Pending');
|
|
$sheetIss->setCellValue("A{$iRow}", $r->report_date ? $r->report_date->format('Y-m-d') : '');
|
|
$sheetIss->setCellValue("B{$iRow}", $r->report_number ?: '#' . $r->id);
|
|
$sheetIss->setCellValue("C{$iRow}", $iss->issue_type ?: 'General');
|
|
$sheetIss->setCellValue("D{$iRow}", $iss->description ?: '—');
|
|
$sheetIss->setCellValue("E{$iRow}", $iss->delay_impact ?: '—');
|
|
$sheetIss->setCellValue("F{$iRow}", $status);
|
|
$sheetIss->getStyle("A{$iRow}:F{$iRow}")->applyFromArray($thinBorder);
|
|
$iRow++;
|
|
}
|
|
}
|
|
foreach ($issDetailCols as $col) {
|
|
$sheetIss->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// Set active sheet back to 0
|
|
$spreadsheet->setActiveSheetIndex(0);
|
|
|
|
$projectSlug = $project ? $project->code : 'All';
|
|
$filename = 'DailyReports_' . $projectSlug . '_' . $startDate->format('Ymd') . '_' . $endDate->format('Ymd') . '.xlsx';
|
|
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
header('Content-Disposition: attachment;filename="' . $filename . '"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writer->save('php://output');
|
|
exit;
|
|
}
|
|
|
|
public function pdfRange(Request $request)
|
|
{
|
|
$request->validate([
|
|
'start_date' => 'required|date',
|
|
'end_date' => 'required|date|after_or_equal:start_date',
|
|
'project' => 'nullable|string',
|
|
]);
|
|
|
|
$startDate = Carbon::parse($request->input('start_date'))->startOfDay();
|
|
$endDate = Carbon::parse($request->input('end_date'))->endOfDay();
|
|
$projectUlid = $request->input('project');
|
|
|
|
$project = $projectUlid ? Project::where('ulid', $projectUlid)->first() : null;
|
|
|
|
$query = DailyReport::with(['project', 'user', 'labors', 'equipment', 'activities', 'materials', 'issues'])
|
|
->whereBetween('report_date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
|
|
->orderBy('report_date', 'asc');
|
|
|
|
if ($project) {
|
|
$query->where('project_id', $project->id);
|
|
}
|
|
|
|
$reports = $query->get();
|
|
|
|
$allLabors = $reports->flatMap->labors;
|
|
$totalPeriodManHours = $allLabors->sum(fn ($l) => $l->workers_count * ($l->hours ?: 0));
|
|
$allEquipment = $reports->flatMap->equipment;
|
|
$totalPeriodEquipHours = $allEquipment->sum('hours_used');
|
|
$allMaterials = $reports->flatMap->materials;
|
|
$allActivities = $reports->flatMap->activities;
|
|
$allIssues = $reports->flatMap->issues;
|
|
|
|
// 1. Manpower Category Summary
|
|
$laborCategorySummary = $allLabors->groupBy(fn ($l) => $l->trade ?: 'Unspecified Trade')
|
|
->map(function ($group, $trade) use ($totalPeriodManHours) {
|
|
$workerDays = $group->sum('workers_count');
|
|
$manHours = $group->sum(fn ($l) => $l->workers_count * ($l->hours ?: 0));
|
|
$activeCount = $group->count();
|
|
$percent = $totalPeriodManHours > 0 ? ($manHours / $totalPeriodManHours) * 100 : 0;
|
|
|
|
return [
|
|
'trade' => $trade,
|
|
'worker_days' => $workerDays,
|
|
'total_man_hours' => $manHours,
|
|
'days_active' => $activeCount,
|
|
'percent_share' => round($percent, 1),
|
|
];
|
|
})->values()->sortByDesc('total_man_hours')->values();
|
|
|
|
// 2. Equipment Category Summary
|
|
$equipmentCategorySummary = $allEquipment->groupBy(fn ($eq) => $eq->equipment_name ?: 'Unspecified Equipment')
|
|
->map(function ($group, $name) use ($totalPeriodEquipHours) {
|
|
$hours = $group->sum('hours_used');
|
|
$activeCount = $group->count();
|
|
$percent = $totalPeriodEquipHours > 0 ? ($hours / $totalPeriodEquipHours) * 100 : 0;
|
|
$latestStatus = $group->last()->status ?? 'Active';
|
|
|
|
return [
|
|
'equipment_name' => $name,
|
|
'total_hours' => $hours,
|
|
'days_active' => $activeCount,
|
|
'percent_share' => round($percent, 1),
|
|
'latest_status' => $latestStatus,
|
|
];
|
|
})->values()->sortByDesc('total_hours')->values();
|
|
|
|
// 3. Materials Category Summary
|
|
$materialsCategorySummary = $allMaterials->groupBy(fn ($m) => $m->material_name ?: 'General Material')
|
|
->map(function ($group, $name) {
|
|
$deliveriesCount = $group->count();
|
|
$quantities = $group->pluck('quantity_received')->filter()->all();
|
|
$numericSum = 0;
|
|
$unit = '';
|
|
foreach ($quantities as $q) {
|
|
if (preg_match('/^([0-9.]+)\s*(.*)$/', trim($q), $matches)) {
|
|
$numericSum += (float) $matches[1];
|
|
if (empty($unit) && !empty($matches[2])) {
|
|
$unit = $matches[2];
|
|
}
|
|
}
|
|
}
|
|
$qtyDisplay = $numericSum > 0 ? (string)$numericSum : (implode(', ', array_slice($quantities, 0, 3)) ?: '—');
|
|
$conditions = $group->pluck('condition')->filter()->unique()->implode(', ') ?: 'Good';
|
|
|
|
return [
|
|
'material_name' => $name,
|
|
'total_quantity' => $qtyDisplay,
|
|
'unit' => $unit ?: 'units',
|
|
'deliveries_count' => $deliveriesCount,
|
|
'condition_summary' => $conditions,
|
|
];
|
|
})->values()->sortByDesc('deliveries_count')->values();
|
|
|
|
// 4. Activities Category Summary
|
|
$activitiesCategorySummary = $allActivities->groupBy(fn ($a) => $a->task_name ?: 'General Works')
|
|
->map(function ($group, $name) {
|
|
$zones = $group->pluck('zone_area')->filter()->unique()->implode(', ') ?: 'Site Wide';
|
|
$highestProgress = $group->max('percentage_completed') ?: 0;
|
|
$daysWorked = $group->count();
|
|
|
|
return [
|
|
'task_name' => $name,
|
|
'zones' => $zones,
|
|
'highest_progress' => $highestProgress,
|
|
'days_worked' => $daysWorked,
|
|
];
|
|
})->values()->sortByDesc('days_worked')->values();
|
|
|
|
// 5. Issues Category Summary
|
|
$issuesCategorySummary = $allIssues->groupBy(fn ($i) => $i->issue_type ?: 'General')
|
|
->map(function ($group, $type) {
|
|
$count = $group->count();
|
|
$resolved = $group->whereNotNull('resolved_at')->count();
|
|
$delays = $group->pluck('delay_impact')->filter()->all();
|
|
|
|
return [
|
|
'issue_type' => $type,
|
|
'incident_count' => $count,
|
|
'resolved_count' => $resolved,
|
|
'pending_count' => $count - $resolved,
|
|
'delay_impact_summary' => !empty($delays) ? implode('; ', array_slice($delays, 0, 3)) : 'None reported',
|
|
];
|
|
})->values()->sortByDesc('incident_count')->values();
|
|
|
|
$summary = [
|
|
'total_workers' => $allLabors->sum('workers_count'),
|
|
'total_man_hours' => $totalPeriodManHours,
|
|
'total_equipment_hours' => $totalPeriodEquipHours,
|
|
'total_activities' => $allActivities->count(),
|
|
'total_materials' => $allMaterials->count(),
|
|
'total_issues' => $allIssues->count(),
|
|
];
|
|
|
|
$pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('dailyreports::pdf.daily-reports-range', [
|
|
'project' => $project,
|
|
'reports' => $reports,
|
|
'startDate' => $startDate,
|
|
'endDate' => $endDate,
|
|
'summary' => $summary,
|
|
'laborCategorySummary' => $laborCategorySummary,
|
|
'equipmentCategorySummary' => $equipmentCategorySummary,
|
|
'materialsCategorySummary' => $materialsCategorySummary,
|
|
'activitiesCategorySummary' => $activitiesCategorySummary,
|
|
'issuesCategorySummary' => $issuesCategorySummary,
|
|
]);
|
|
|
|
$projectSlug = $project ? $project->code : 'All';
|
|
$filename = 'DailyReports_' . $projectSlug . '_' . $startDate->format('Ymd') . '_' . $endDate->format('Ymd') . '.pdf';
|
|
|
|
return $pdf->download($filename);
|
|
}
|
|
}
|