<?php
include_once '../ComponentCode/PHPWord_0.17/autoload.php';
// Template processor instance creation
$template_workflow = $_SESSION['InformeDOCX'];
$templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor($template_workflow);
// -------------------- ^ header needed for Word templates ------------------
$VidProcess = $_SESSION['idProcess'] ; 
$VsuperCodeStatus = $_SESSION['superCodeStatus'];
$Vlanguage = $_SESSION['language'];
$VidExpedient = $_SESSION['idExpedient'];
$Viduser=$_SESSION['iduser'];
$sql="
SELECT
expedient.idexpedient,
expedient.process_idprocess,
expedient.ct_status_idct_status,
concat (status.catalogNum,' - ',status.catalogCode) Name_status,
expedient.codeExpedient,
expedient.title,
expedient.observations,
expedient.assignedUser,
ifnull(user1.NameAndSurname,'') Name_assignedUser,
expedient.creationDate,
expedient.creationUser,
ifnull(user2.NameAndSurname,'') Name_creationUser,
expedient.lastUpdateDate,
expedient.lastUpdateUser,
ifnull(user3.NameAndSurname,'') Name_lastUpdateUser,
expedient.finishDate,
expedient.ex04_advanceAmount
FROM expedient
join (
SELECT
s.idct_status,
s.catalogNum,
n.catalogCode
FROM ct_status s
join (
SELECT
s.idsuper_catalog,
s.superCode,
c.language,
c.catalogNum,
c.catalogCode,
c.description,
c.groupNum
FROM catalog AS c
INNER JOIN super_catalog AS s ON c.super_catalog_idsuper_catalog = s.idsuper_catalog
  ) n on ( s.catalogNum = n.catalogNum )
where process_idprocess = $VidProcess and  n.superCode = '$VsuperCodeStatus' and n.language = '$Vlanguage') status
    on (status.idct_status = expedient.ct_status_idct_status )
left join user user1 on (user1.iduser = expedient.assignedUser)
left join user user2 on (user2.login = expedient.creationUser)
left join user user3 on (user3.login = expedient.lastUpdateUser)
where expedient.idexpedient = $VidExpedient
";
$resql=db_query($sql,$conn);
$data=db_fetch_array($resql);
// Variables on different parts of document
$templateProcessor->setValue('Name_creationUser', $data['Name_creationUser']);  
$templateProcessor->setValue('ex04_advanceAmount ', number_format($data['ex04_advanceAmount'], 2, ',', '.'));
$today = date("Y-m-d H:i:s");
$templateProcessor->setValue('today_date', formatDate($today));
// -------------------- v foot to save new Word document ------------------
$temp_file = tempnam(sys_get_temp_dir(), 'PHPWord');
$templateProcessor->saveAS($temp_file);
// $ temp_file must contain the resulting file

 <?php
// se deben finalizar poniendo los valores 0 o 1 a la variable "$ReportMustBeCreated"
// they must be ended by putting the values 0 or 1 to the variable "$ ReportMustBeCreated"
$ReportMustBeCreated=1; 
// 0 = no se crea el informe | 1 = Sí se crea
// 0 = report is not created | 1 = Yes it is created
?>

 
<?php
require_once __DIR__ . './../../../ComponentCode/PhpSpreadsheet_1.10/autoload.php'; 
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
//  Template processor instance creation
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
// $reader->setIncludeCharts(true);
// $reader->setReadFilter();
// $reader->setReadEmptyCells(true);
$template_workflow = $_SESSION['InformeEXCEL'];
$base = __DIR__;
$folder = $_SESSION['config'][array_search('DOCUMENT_ROOT_WF2', array_column($_SESSION['config'], 'name'))][value];
$spreadsheet = $reader->load($base. '/'.$folder . $template_workflow);
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$DateList=now();
$spreadsheet->getActiveSheet()->setCellValue('E2', Date::PHPToExcel($DateList)); //  Fill in a cell with the current date in excel format
// Capture data from the BD
$VidProcess = $_SESSION['idProcess'] ; 
$VsuperCodeStatus = $_SESSION['superCodeStatus'];
$Vlanguage = $_SESSION['language'];
$VidExpedient = $_SESSION['idExpedient'];
$sql = 
"SELECT
expedient.idexpedient,
expedient.process_idprocess,
expedient.ct_status_idct_status,
concat (status.catalogNum,' - ',status.catalogCode) Name_status,
expedient.codeExpedient,
expedient.title,
expedient.observations,
expedient.assignedUser,
ifnull(user1.NameAndSurname,'') Name_assignedUser,
expedient.creationDate,
expedient.creationUser,
ifnull(user2.NameAndSurname,'') Name_creationUser,
expedient.lastUpdateDate,
expedient.lastUpdateUser,
ifnull(user3.NameAndSurname,'') Name_lastUpdateUser,
expedient.finishDate,
expedient.ex04_advanceAmount
FROM expedient
join (
SELECT
s.idct_status,
s.catalogNum,
n.catalogCode
FROM ct_status s
join (
SELECT
s.idsuper_catalog,
s.superCode,
c.language,
c.catalogNum,
c.catalogCode,
c.description,
c.groupNum
FROM catalog AS c
INNER JOIN super_catalog AS s ON c.super_catalog_idsuper_catalog = s.idsuper_catalog
  ) n on ( s.catalogNum = n.catalogNum )
where process_idprocess = $VidProcess and  n.superCode = '$VsuperCodeStatus' and n.language = '$Vlanguage') status
    on (status.idct_status = expedient.ct_status_idct_status )
left join user user1 on (user1.iduser = expedient.assignedUser)
left join user user2 on (user2.login = expedient.creationUser)
left join user user3 on (user3.login = expedient.lastUpdateUser)
ORDER by 3";
global $conn;
$result = $conn->query($sql);
$data = $result->fetch_all(MYSQLI_NUM); // faster - All record
$dataRows = count($data);
$spreadsheet->setActiveSheetIndex(1); // Activate the second sheet
$spreadsheet->getActiveSheet()->fromArray($data, null, 'A2'); //  Load ALL records in the active sheet
// Define named ranges
$spreadsheet->addNamedRange(new NamedRange('Datos01', $spreadsheet->getActiveSheet(), 'A2:'.'Q'.($dataRows-1+2)));
$spreadsheet->setActiveSheetIndex(0); // Activate the first sheet
$spreadsheet->getActiveSheet()->insertNewRowBefore(5, ($dataRows)); // Copy presentation formats
$spreadsheet->getActiveSheet()->removeRow(($dataRows-1+5), 1); // Delete last row
$spreadsheet->addNamedRange(new NamedRange('DatosPrint001', $spreadsheet->getActiveSheet(), 'A3:'.'R'.($dataRows+3))); //  Define print range
// Presentation Field Formulas
$formulaA4 = $spreadsheet->getActiveSheet()->getCell('A4')->getValue();
$formulaB4 = $spreadsheet->getActiveSheet()->getCell('B4')->getValue();
$formulaC4 = $spreadsheet->getActiveSheet()->getCell('C4')->getValue();
$formulaD4 = $spreadsheet->getActiveSheet()->getCell('D4')->getValue();
$formulaE4 = $spreadsheet->getActiveSheet()->getCell('E4')->getValue();
// Build the formulas with definitive references
$dataArray = [];
for ($x = 4; $x <= $dataRows+3; $x++) {
    $dataArray[] = [
              str_replace("NumRow", $x-3, $formulaA4),
          str_replace("NumRow", $x-3, $formulaB4),
          str_replace("NumRow", $x-3, $formulaC4),
          str_replace("NumRow", $x-3, $formulaD4),
              str_replace("NumRow", $x-3, $formulaE4)];
} 
$spreadsheet->getActiveSheet()->fromArray($dataArray, null, 'A4'); // Copy formulas in all fils
// Break page by row
$spreadsheet->setActiveSheetIndex(0);
$Ruptura=$data[0][3];
for ($x = 0; $x <= $dataRows; $x++) {
    if ($Ruptura <> $data[$x][3] ) {
        $Ruptura=$data[$x][3];
        $spreadsheet->getActiveSheet()->setBreak('A'.($x+3), \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW); // Rupture for printing. Page break
    }
}
// Break page by column
//$spreadsheet->getActiveSheet()->setBreak('C10', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_COLUMN);
// Show/hide gridlines when printing
$spreadsheet->getActiveSheet()->setShowGridlines(false); // Disable grid in print
// Config print file Excel
// $spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
// Page Setup: Scaling options
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToPage(FALSE);
$spreadsheet->getActiveSheet()->getPageSetup()->setScale(85);
// $spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(0); 
// $spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
// Page margins
// $spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
// $spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.6);
// $spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.6);
// $spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);
// Center a page horizontally/vertically
$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(false);
$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
// Setting the print header and footer of a worksheet
// $spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');
// Setting rows/columns to repeat at top/left
$spreadsheet->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 3);
// Specify printing area
$spreadsheet->getActiveSheet()->getPageSetup()->setPrintArea('A1:'.'E'.($dataRows+3));
// Create file XLSX
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);
// -------------------- v foot to save the new Excel document ------------------
$temp_file = tempnam(sys_get_temp_dir(), 'Excel');
// Save EXCEL
$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false); //Disable formula validation
$writer->save($temp_file);
// $ temp_file must contain