Ümit Tunç
363 lines
12 KiB
PHP
363 lines
12 KiB
PHP
<?php
|
|
use App\Models\WelderTest;
|
|
use Barryvdh\DomPDF\Facade\Pdf;
|
|
use Carbon\Carbon;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
|
|
|
|
|
|
|
|
set_time_limit(-1);
|
|
ini_set('max_execution_time', -1);
|
|
|
|
setlocale(LC_ALL, 'ru_RU.utf8');
|
|
App::setLocale("ru");
|
|
$documentInfo = document_template(get("document"));
|
|
$j = j($documentInfo->json);
|
|
|
|
function mb_str_replace($search, $replace, $subject) {
|
|
return implode($replace, explode($search, $subject, 2));
|
|
}
|
|
|
|
$weldLogs = db("weld_logs");
|
|
$weldLogs = $weldLogs->join("subcontractors", "weld_logs.general_contractor", "subcontractors.company_name_en");
|
|
|
|
$montajContractor = db("subcontractors")->where("job_description", "MONTAJ")->first();
|
|
$constructionContractor = db("subcontractors")->where("job_description", "CONSTRUCTION")->first();
|
|
|
|
$weldLogs = $weldLogs->groupBy("line_number")->get();
|
|
|
|
$prefix = "📂 {$documentInfo->title}";
|
|
Log::debug("$prefix start " . simdi());
|
|
|
|
$k = 0;
|
|
|
|
$projectNameRu = setting('project_name_ru');
|
|
|
|
// Load your Excel template file
|
|
$spreadsheet = IOFactory::load('storage/documents/' . $documentInfo->files);
|
|
|
|
// Get the active sheet or a specific sheet where you want to make replacements
|
|
$sheet = $spreadsheet->getActiveSheet(); // or $spreadsheet->getSheetByName('SheetName');
|
|
|
|
|
|
foreach($weldLogs AS $weldLog) {
|
|
$contractorInfo = db("subcontractors")->where("company_name_en", $weldLog->contractor)->first();
|
|
$lineList = db("line_lists")->where([
|
|
'line_no' => $weldLog->line_number
|
|
])->first();
|
|
|
|
|
|
|
|
|
|
|
|
$fileName = $weldLog->line_number . " - " . $documentInfo->title2;
|
|
|
|
$spreadsheet = workPermitReplacerExcel($spreadsheet, $weldLog, $documentInfo);
|
|
|
|
|
|
$date = (new Carbon($weldLog->date_test));
|
|
$c1 = $contractorInfo?->project_city_ru;
|
|
$c2 = $date->translatedFormat('d');
|
|
$c3 = $date->translatedFormat('F');
|
|
$c4 = $date->translatedFormat('y');
|
|
$c5 = $weldLog->general_contractor . "<hr>" . $weldLog->address;
|
|
$c6 = $montajContractor?->company_name_ru;
|
|
$c7 = $contractorInfo?->company_name_ru ;
|
|
|
|
$c11 = $contractorInfo?->company_name_ru;
|
|
$c12 = $constructionContractor?->company_name_ru;
|
|
$c13 = $weldLog->iso_number;
|
|
|
|
$spoolReleaseNumbers = db("weld_logs")
|
|
->where("line_number", $weldLog->line_number)
|
|
->where("spool_status", "Spool Release")
|
|
->get()->pluck("spool_number")->toArray();
|
|
|
|
$spoolReleaseNumbers = implode(", ", $spoolReleaseNumbers);
|
|
|
|
$c14 = "вода";
|
|
if($weldLog->type_of_test == "Hydraulic") {
|
|
$c14 = "водой";
|
|
}
|
|
|
|
$c15 = $weldLog->design_pressure_mpa;
|
|
$c16 = $weldLog->test_pressure;
|
|
$c17 = "0,5";
|
|
|
|
|
|
|
|
$first_welding_date = db("weld_logs")->where("line_number", $weldLog->line_number)->whereNotNull("welding_date")->orderBy("welding_date", "ASC")->value("welding_date");
|
|
$first_welding_date = df($first_welding_date);
|
|
|
|
$last_welding_date = db("weld_logs")->where("line_number", $weldLog->line_number)->whereNotNull("welding_date")->orderBy("welding_date", "DESC")->value("welding_date");
|
|
$last_welding_date = df($last_welding_date);
|
|
|
|
$engineering_ru = db("subcontractors")->where("company_name_en", $lineList?->engineering)->value("company_name_ru");
|
|
|
|
|
|
// Define your placeholders and their corresponding replacements
|
|
$replacements = [
|
|
'{project_name}' => $projectNameRu,
|
|
'{date}' => $date,
|
|
'{c1}' => $c1,
|
|
'{c2}' => $c2,
|
|
'{c3}' => $c3,
|
|
'{c4}' => $c4,
|
|
'{c5}' => $c5,
|
|
'{c6}' => $c6,
|
|
'{c7}' => $c7,
|
|
'{c11}' => $c11,
|
|
'{c12}' => $c12,
|
|
'{c13}' => $c13,
|
|
'{c14}' => $c14,
|
|
'{c15}' => $c15,
|
|
'{c16}' => $c16,
|
|
'{c17}' => $c17,
|
|
'{plant}' => $lineList?->plant,
|
|
'{p_id}' => $lineList?->p_id,
|
|
'{first_welding_date}' => $first_welding_date,
|
|
'{last_welding_date}' => $last_welding_date,
|
|
'{engineering_ru}' => $engineering_ru,
|
|
'{spoolReleaseNumbers}' => $spoolReleaseNumbers,
|
|
];
|
|
|
|
// Loop through all the cells in the sheet and replace placeholders
|
|
foreach ($sheet->getRowIterator() as $row) {
|
|
foreach ($row->getCellIterator() as $cell) {
|
|
$cellValue = $cell->getValue();
|
|
if (is_string($cellValue)) {
|
|
foreach ($replacements as $placeholder => $replacement) {
|
|
if (strpos($cellValue, $placeholder) !== false) {
|
|
$newValue = str_replace($placeholder, $replacement, $cellValue);
|
|
$cell->setValue($newValue);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
if($documentInfo->title == "NDT Clearance_TP")
|
|
{
|
|
// Get the starting row index where the data begins (adjust according to your template)
|
|
$startRow = 19; // Example row number where the placeholders start
|
|
|
|
// Retrieve the weld logs
|
|
$sameWeldLogs = db("weld_logs")->where("line_number", $weldLog->line_number)->get();
|
|
|
|
|
|
|
|
|
|
foreach($sameWeldLogs AS $sameWeldLog) {
|
|
// Save the template row's cell values and merge information
|
|
$templateRowCells = [];
|
|
$mergedCells = [];
|
|
|
|
// Iterate through the template row and save cell values and merge information
|
|
foreach ($sheet->getRowIterator($startRow)->current()->getCellIterator() as $cell) {
|
|
$templateRowCells[] = $cell->getValue();
|
|
}
|
|
|
|
|
|
|
|
// Save merged cell ranges in the template row
|
|
foreach ($sheet->getMergeCells() as $mergeRange) {
|
|
// Check if the merge range starts in the template row
|
|
if (preg_match('/^\D*'.$startRow.'$/', explode(':', $mergeRange)[0])) {
|
|
$mergedCells[] = $mergeRange;
|
|
}
|
|
}
|
|
// Insert a new row before the next one (clone the template row)
|
|
$sheet->insertNewRowBefore($startRow + 1, 1);
|
|
|
|
// Get the newly inserted row
|
|
$newRow = $sheet->getRowIterator($startRow + 1)->current();
|
|
|
|
// Copy template row's values to the new row
|
|
$cellIndex = 0; // To keep track of which cell we're on
|
|
foreach ($newRow->getCellIterator() as $newCell) {
|
|
// Set the template value to the new cell
|
|
$newCellValue = $templateRowCells[$cellIndex];
|
|
|
|
// Replace placeholders with actual values
|
|
$newCellValue = str_replace('{line_no}', $sameWeldLog->line_number, $newCellValue);
|
|
$newCellValue = str_replace('{quantity_of_iso}', $sameWeldLog->quantity_of_iso, $newCellValue);
|
|
$newCellValue = str_replace('{joint_no}', $sameWeldLog->no_of_the_joint_as_per_as_built_survey, $newCellValue);
|
|
$newCellValue = str_replace('{welder_id}', $sameWeldLog->real_welder_1, $newCellValue);
|
|
$newCellValue = str_replace('{dia}', $sameWeldLog->outside_diameter_1, $newCellValue);
|
|
$newCellValue = str_replace('{thk}', $sameWeldLog->wall_thickness_1, $newCellValue);
|
|
$newCellValue = str_replace('{vt_report_no}', $sameWeldLog->vt_report, $newCellValue);
|
|
$newCellValue = str_replace('{pt_report_no}', $sameWeldLog->pt_report, $newCellValue);
|
|
$newCellValue = str_replace('{mt_report_no}', $sameWeldLog->mt_report, $newCellValue);
|
|
$newCellValue = str_replace('{rt_report_no}', $sameWeldLog->rt_report, $newCellValue);
|
|
|
|
// Set the new value in the cell
|
|
$newCell->setValue($newCellValue);
|
|
|
|
$cellIndex++; // Move to the next cell
|
|
}
|
|
|
|
// Apply merged cells to the new row
|
|
foreach ($mergedCells as $mergeRange) {
|
|
// Adjust the merge range to the new row
|
|
$adjustedMergeRange = preg_replace_callback('/\d+/', function($matches) use ($startRow) {
|
|
return $matches[0] + 1; // Shift row numbers by 1 for the newly inserted row
|
|
}, $mergeRange);
|
|
|
|
// Apply the adjusted merge range to the new row
|
|
$sheet->mergeCells($adjustedMergeRange);
|
|
}
|
|
|
|
// Move to the next row for the next weld log entry
|
|
$startRow++;
|
|
}
|
|
|
|
|
|
|
|
// After inserting all new rows, remove the original template row
|
|
$sheet->removeRow(19);
|
|
|
|
}
|
|
|
|
if($documentInfo->title == "AOSR NDT Reports List")
|
|
{
|
|
$logTypes = log_test_types();
|
|
|
|
$sameWeldLogs = db("weld_logs")->where("line_number", $weldLog->line_number)->get();
|
|
|
|
foreach($sameWeldLogs AS $sameWeldLog)
|
|
{
|
|
foreach($logTypes AS $logType => $logTable)
|
|
{
|
|
$reportNo = $sameWeldLog->{$logType."_report"};
|
|
$reportDate = df($sameWeldLog->{$logType."_test_date"});
|
|
if($reportNo != "")
|
|
{
|
|
$aosr_table_row .= "
|
|
<tr>
|
|
<td>ВИК</td>
|
|
<td>$reportNo от $reportDate</td>
|
|
</tr>
|
|
";
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
/*
|
|
if($documentInfo->title == "AOSR NDT Reports List")
|
|
{
|
|
$aosr_table_row = "";
|
|
$logTypes = log_test_types();
|
|
|
|
$sameWeldLogs = db("weld_logs")->where("line_number", $weldLog->line_number)->get();
|
|
|
|
foreach($sameWeldLogs AS $sameWeldLog)
|
|
{
|
|
foreach($logTypes AS $logType => $logTable)
|
|
{
|
|
$reportNo = $sameWeldLog->{$logType."_report"};
|
|
$reportDate = df($sameWeldLog->{$logType."_test_date"});
|
|
if($reportNo != "")
|
|
{
|
|
$aosr_table_row .= "
|
|
<tr>
|
|
<td>ВИК</td>
|
|
<td>$reportNo от $reportDate</td>
|
|
</tr>
|
|
";
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
$string = str_replace("<!-- aosr_table_row -->", $aosr_table_row, $string);
|
|
}
|
|
|
|
|
|
if($documentInfo->title == "AOSR Materials List")
|
|
{
|
|
$aosr_table_row = "";
|
|
$logTypes = log_test_types();
|
|
$addedElement = [];
|
|
|
|
$sameWeldLogs = db("weld_logs")->where("line_number", $weldLog->line_number)->get();
|
|
|
|
foreach($sameWeldLogs AS $sameWeldLog)
|
|
{
|
|
for($codeCount=1; $codeCount<=2; $codeCount++)
|
|
{
|
|
$elementCode = $sameWeldLog->{"element_code_" . $codeCount};
|
|
$material = $sameWeldLog->{"material_no_" . $codeCount};
|
|
|
|
$certificateDate = db("incoming_controls")->where("certificate_no", $sameWeldLog->{"certificate_number_of_" . $codeCount})->value("certificate_date");
|
|
|
|
if(!rejected_date($certificateDate)) {
|
|
$certificateDate = df($certificateDate);
|
|
} else {
|
|
$certificateDate = "-";
|
|
}
|
|
|
|
$certificate = $sameWeldLog->{"certificate_number_of_" . $codeCount} . " " . $certificateDate;
|
|
|
|
if($elementCode == "") $elementCode = "-";
|
|
if($material == "") $material = "-";
|
|
if($certificate == "") $certificate = "-";
|
|
|
|
if(!in_array($elementCode, $addedElement))
|
|
{
|
|
$aosr_table_row .= "
|
|
<tr>
|
|
<td>$elementCode</td>
|
|
<td>$material</td>
|
|
<td>$certificate</td>
|
|
</tr>
|
|
";
|
|
$addedElement[] = $elementCode;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
$string = str_replace("<!-- aosr_table_row -->", $aosr_table_row, $string);
|
|
}
|
|
|
|
|
|
*/
|
|
|
|
$path = $documentInfo->kid; //"005_PTO/06_Прочность и плотность";
|
|
|
|
$fileName = str_replace("/", "-", $fileName);
|
|
$path = "storage/documents/$path/$fileName.xlsx";
|
|
|
|
if (!file_exists(dirname($path))) {
|
|
mkdir(dirname($path), 0777, true);
|
|
}
|
|
|
|
// Save the modified spreadsheet
|
|
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
|
|
$writer->save($path);
|
|
echo xlsx_to_pdf_legacy($path, "storage/documents/{$documentInfo->kid}");
|
|
$k++;
|
|
exit();
|
|
|
|
|
|
}
|
|
|
|
e2("The process is completed: a total of :count documents have been processed into the :folder folder.",
|
|
[
|
|
'folder' => $documentInfo->kid,
|
|
'count' => $k
|
|
]);
|
|
Log::debug("$prefix finish processed $k " . simdi());
|
|
?>
|
|
|