2. PHPRunner Tips and Tricks
  3. [SOLVED] Create invoice in Word, Excel or PDF and send Email
This topic is locked
[SOLVED]

Create invoice in Word, Excel or PDF and send Email

 		4/11/2020 11:13:51
PHPRunner Tips and Tricks
fhumanes author


This article collects the previous examples that had done with the products:
Office / PHPWord (Word)

Office / PhpSpreadsheet (Excel)

Setasign PDF /FPDI (PDF)
And I have also added the sending of the PDF by Email at the request of some users.
[size="3"]DEMO:[/size] https://fhumanes.com/invoice/
In order for you to receive the invoice by email it is necessary:

  • That you create a Client with an email account to which you have access.
  • That you create an Invoice with said Client
  • That you press the "send PDF" button in the invoice consultation.
    The buttons to get the invoices in Word, Excel and PDF are in the Invoices query.


I show you the code of the examples:
Word





<?php

require_once __DIR__ . '/../../ComponentCode/PHPWord_0.17/autoload.php';

// ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

$idfactura= $_SESSION['idfactura'] ; // identificación de factura a obtener

// Template processor instance creation

$template_word = __DIR__.'/PlantillaFactura.docx';

$templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor($template_word);

// -------------------- ^ cabecera necesaria para las plantillas de Word ------------------

$sql="SELECT Nif, NombreRazonSocial, Domicilio, RestoDomicilio, FechaFactura, TotalFactura FROM factura where idfactura = $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

// Variables on different parts of document

$templateProcessor->setValue('NIF', $data['Nif']);

$templateProcessor->setValue('Nombre', $data['NombreRazonSocial']);

$templateProcessor->setValue('Direccion1', $data['Domicilio']);

$templateProcessor->setValue('Direccion2', $data['RestoDomicilio']);

$templateProcessor->setValue('Total', $data['TotalFactura']);

$FechaFactura=$data['FechaFactura'];

$sql="SELECT count(*) Lineas FROM linea_factura where factura_idfactura= $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

$NumeroLineas= $data['Lineas']; // número de líneaas de factura

// Simple table

$templateProcessor->cloneRow('rowArticulo', $NumeroLineas);

$sql="SELECT Nombre, Precio, Cantidad, Valor FROM linea_factura where factura_idfactura= $idfactura ";

$rsSql=db_query($sql,$conn);

$countLines=0;

while ($data2 = db_fetch_array($rsSql)){

$countLines=$countLines+1;

$templateProcessor->setValue('rowArticulo#'.$countLines, $data2['Nombre']);

$templateProcessor->setValue('rowPrecio#'.$countLines, $data2['Precio']);

$templateProcessor->setValue('rowCantidad#'.$countLines, $data2['Cantidad']);

$templateProcessor->setValue('rowValor#'.$countLines, $data2['Valor']);

}

// Date Local completed

$date = DateTime::createFromFormat('Y-m-d', $FechaFactura);

$formatter = new IntlDateFormatter('es_ES', IntlDateFormatter::LONG, IntlDateFormatter::LONG);

$formatter->setPattern("d 'de' MMMM 'de' yyyy");

$mydate = $formatter->format($date);

$templateProcessor->setValue('FechaDeHoyCompleta', $mydate);

// $templateProcessor->setValue('FechaDeHoyCompleta', $FechaFactura);

// -------------------- v pie para salvar el nuevo documento Word ------------------

$temp_file = tempnam(sys_get_temp_dir(), 'Word');

$templateProcessor->saveAS($temp_file);

// ------------------ Operation with file result -------------------------------------------

$documento = file_get_contents($temp_file);

unlink($temp_file); // delete file tmp

header("Content-Disposition: attachment; filename= factura.docx");

header('Content-Type: application/word');

echo $documento;

?>


Excel





<?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 = IOFactory::createReader('Xlsx');

$spreadsheet = $reader->load(__DIR__ . '/PlantillaFactura.xlsx');

$idfactura= $_SESSION['idfactura'] ; // identificación de factura a obtener

// Recuperar datos de factura

$sql="SELECT Nif, NombreRazonSocial, Domicilio, RestoDomicilio, FechaFactura, TotalFactura FROM factura where idfactura = $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

// Variables on different parts of document

$FechaFactura=$data['FechaFactura'];

$spreadsheet->getActiveSheet()->setCellValue('D1', Date::PHPToExcel($data['FechaFactura']));

$spreadsheet->getActiveSheet()->setCellValue('B2', $data['Nif']);

$spreadsheet->getActiveSheet()->setCellValue('B3', $data['NombreRazonSocial']);

$spreadsheet->getActiveSheet()->setCellValue('B5', $data['Domicilio']);

$spreadsheet->getActiveSheet()->setCellValue('B6', $data['RestoDomicilio']);

// read lines the invoice

$sql="SELECT Nombre, Precio, Cantidad, Valor FROM linea_factura where factura_idfactura= $idfactura ";

$rsSql=db_query($sql,$conn);

$baseRow = 10;

$r=0;

while ($data2 = db_fetch_array($rsSql)){

$row = $baseRow + $r;

$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);

$spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)

->setCellValue('B' . $row, $data2['Nombre'])

->setCellValue('C' . $row, $data2['Precio'])

->setCellValue('D' . $row, $data2['Cantidad'])

->setCellValue('E' . $row, $data2['Valor']);

$r= $r+1;

}

$spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);

// -------------------- v pie para salvar el nuevo documento Word ------------------

$temp_file = tempnam(sys_get_temp_dir(), 'Excel');

// Save EXCEL

$writer = new Xlsx($spreadsheet);

$writer->save($temp_file);

// ------------------ Operation with file result -------------------------------------------

$documento = file_get_contents($temp_file);

unlink($temp_file); // delete file tmp

header("Content-Disposition: attachment; filename= factura.xlsx");

header('Content-Type: application/excel');

echo $documento;

?>


PDF



<?php

require_once __DIR__ . '/../../ComponentCode/fpdi_2_2/autoload.php';

use setasign\Fpdi\Fpdi;

// initiate FPDI

$pdf = new Fpdi();

// add a page

$pdf->AddPage();

// set the source file

$template_pdf = __DIR__.'/PlantillaFactura.pdf';

$pdf->setSourceFile($template_pdf);

// import page 1

$tplIdx = $pdf->importPage(1);

// use the imported page and place it at position 10,10 with a width of 100 mm

// $pdf->useTemplate($tplIdx, 10, 10, 100);

$pdf->useTemplate($tplIdx);

// Obtain measures from the page for the transformation of the Points

$pdf->SetXY(1, 1);

$wPt = 595.32; // Measures in points of the page

$w = $pdf->GetPageWidth();

$hPt = 841.92; // Measures in points of the page

$h = $pdf->GetPageHeight();

$coef_x = $wPt/$w; // X axis transformation coefficient

$coef_y = $hPt/$h; // Y axis transformation coefficient

// now write some text above the imported page

// $pdf->SetFont('Arial','B',10); // Font, type and size

// $pdf->SetTextColor(68, 68, 68); // Color in R, G, B

// $pdf->SetXY(132/$coef_x, 118/$coef_y); // Positioning on the page

// $pdf->Write(0, 'This is just a simple text'); // Write TXT. Offset in Y points, Text and Link

// -----------------------------------------------Recover invoice data-------------------------------------------------------------------------------------------------------

$idfactura= $_SESSION['idfactura'] ; // invoice identification to obtain

$sql="SELECT Nif, NombreRazonSocial, Domicilio, RestoDomicilio, FechaFactura, TotalFactura FROM factura where idfactura = $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

// Variables on different parts of document

$pdf->SetFont('Arial','',16); // Font, type and size

$pdf->SetTextColor(247, 172, 8); // Color in R, G, B

$pdf->SetXY(321/$coef_x, 101/$coef_y); // Positioning on the page

$pdf->Write(0, '#'.$idfactura); // Numbre invoce

$pdf->SetXY(435/$coef_x, 101/$coef_y); // Positioning on the page

$pdf->Write(0, $data['FechaFactura']); // Date invoce

$pdf->SetFont('Arial','B',10); // Font, type and size

$pdf->SetTextColor(68, 68, 68); // Color in R, G, B

$pdf->SetXY(132/$coef_x, 118/$coef_y); // Positioning on the page

$pdf->Write(0, $data['Nif']); // NIF

$pdf->SetXY(132/$coef_x, 132/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['NombreRazonSocial']); // Convert UTf8

$pdf->Write(0, $string); // Nombre

$pdf->SetFont('Arial','',10); // Font, type and size

$pdf->SetXY(132/$coef_x, 146/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['Domicilio']); // Convert UTf8

$pdf->Write(0, $string); // Domicilio



$pdf->SetXY(132/$coef_x, 160/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['RestoDomicilio']); // Convert UTf8

$pdf->Write(0, $string); // RestoDomicilio

$pdf->SetFont('Courier','',10); // Font, type and size, COURIER is a fixed size font of all characters

$pdf->SetXY(443/$coef_x, 481/$coef_y);

$number = number_format($data['TotalFactura'], 2, ',', '.');

$number = sprintf("%' 11s",$number);

$pdf->Write(0,$number ); // TotalFactura

$pdf->SetXY(443/$coef_x, 453/$coef_y);

$pdf->Write(0,$number ); // TotalFactura

// --------------------------------------------------------------

$sql="SELECT producto_idproducto, Nombre, Precio, Cantidad, Valor FROM linea_factura where factura_idfactura= $idfactura ";

$rsSql=db_query($sql,$conn);

$countLines=0;

while ($data2 = db_fetch_array($rsSql)){

$pdf->SetXY(55/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['producto_idproducto'], 0, ',', '.');

$number = sprintf("%' 9s",$number);

$pdf->Write(0,$number ); // Id Producto

$pdf->SetXY(128/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data2['Nombre']); // Convert UTf8

$pdf->Write(0,$string); // Nonbre

$pdf->SetXY(322.71/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Precio'], 2, ',', '.');

$number = sprintf("%' 7s",$number);

$pdf->Write(0,$number ); // Precio

$pdf->SetXY(375/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Cantidad'], 0, ',', '.');

$number = sprintf("%' 10s",$number);

$pdf->Write(0,$number ); // Cantidad

$pdf->SetXY(443/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Valor'], 2, ',', '.');

$number = sprintf("%' 11s",$number);

$pdf->Write(0,$number ); // Valor

$countLines=$countLines+1;

}

// adding the second page of the template

$tplIdx2 = $pdf->importPage(2);

$s = $pdf->getTemplatesize($tplIdx2);

$pdf->AddPage('', $s);

$pdf->useImportedPage($tplIdx2);

$pdf->Output('I','Factura.pdf');

/*

// -------------------- foot to save the new PDF document ------------------

$temp_file = tempnam(sys_get_temp_dir(), 'PDF');

$pdf->Output('F',$temp_file);

// ------------------ Operation with file result -------------------------------------------

$documento = file_get_contents($temp_file);

unlink($temp_file); // delete file tmp

header("Content-Disposition: attachment; filename= factura.pdf");

header('Content-Type: application/pdf');

echo $documento;

*/

?>


PDF + Email



<?php

require_once __DIR__ . '/../../ComponentCode/fpdi_2_2/autoload.php';

use setasign\Fpdi\Fpdi;

// initiate FPDI

$pdf = new Fpdi();

// add a page

$pdf->AddPage();

// set the source file

$template_pdf = __DIR__.'/PlantillaFactura.pdf';

$pdf->setSourceFile($template_pdf);

// import page 1

$tplIdx = $pdf->importPage(1);

// use the imported page and place it at position 10,10 with a width of 100 mm

// $pdf->useTemplate($tplIdx, 10, 10, 100);

$pdf->useTemplate($tplIdx);

// Obtain measures from the page for the transformation of the Points

$pdf->SetXY(1, 1);

$wPt = 595.32; // Measures in points of the page

$w = $pdf->GetPageWidth();

$hPt = 841.92; // Measures in points of the page

$h = $pdf->GetPageHeight();

$coef_x = $wPt/$w; // X axis transformation coefficient

$coef_y = $hPt/$h; // Y axis transformation coefficient

// now write some text above the imported page

// $pdf->SetFont('Arial','B',10); // Font, type and size

// $pdf->SetTextColor(68, 68, 68); // Color in R, G, B

// $pdf->SetXY(132/$coef_x, 118/$coef_y); // Positioning on the page

// $pdf->Write(0, 'This is just a simple text'); // Write TXT. Offset in Y points, Text and Link

// -----------------------------------------------Recover invoice data-------------------------------------------------------------------------------------------------------

$idfactura= $_SESSION['idfactura'] ; // invoice identification to obtain

$sql="SELECT Nif, NombreRazonSocial, Domicilio, RestoDomicilio, Email, FechaFactura, TotalFactura FROM factura where idfactura = $idfactura";

$resql=db_query($sql,$conn);

$data=db_fetch_array($resql);

// Variables on different parts of document

$pdf->SetFont('Arial','',16); // Font, type and size

$pdf->SetTextColor(247, 172, 8); // Color in R, G, B

$pdf->SetXY(321/$coef_x, 101/$coef_y); // Positioning on the page

$pdf->Write(0, '#'.$idfactura); // Numbre invoce

$pdf->SetXY(435/$coef_x, 101/$coef_y); // Positioning on the page

$pdf->Write(0, $data['FechaFactura']); // Date invoce

$pdf->SetFont('Arial','B',10); // Font, type and size

$pdf->SetTextColor(68, 68, 68); // Color in R, G, B

$pdf->SetXY(132/$coef_x, 118/$coef_y); // Positioning on the page

$pdf->Write(0, $data['Nif']); // NIF

$pdf->SetXY(132/$coef_x, 132/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['NombreRazonSocial']); // Convert UTf8

$pdf->Write(0, $string); // Nombre

$pdf->SetFont('Arial','',10); // Font, type and size

$pdf->SetXY(132/$coef_x, 146/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['Domicilio']); // Convert UTf8

$pdf->Write(0, $string); // Domicilio



$pdf->SetXY(132/$coef_x, 160/$coef_y);

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data['RestoDomicilio']); // Convert UTf8

$pdf->Write(0, $string); // RestoDomicilio

$pdf->SetFont('Courier','',10); // Font, type and size, COURIER is a fixed size font of all characters

$pdf->SetXY(443/$coef_x, 481/$coef_y);

$number = number_format($data['TotalFactura'], 2, ',', '.');

$number = sprintf("%' 11s",$number);

$pdf->Write(0,$number ); // TotalFactura

$pdf->SetXY(443/$coef_x, 453/$coef_y);

$pdf->Write(0,$number ); // TotalFactura

// --------------------------------------------------------------

$sql="SELECT producto_idproducto, Nombre, Precio, Cantidad, Valor FROM linea_factura where factura_idfactura= $idfactura ";

$rsSql=db_query($sql,$conn);

$countLines=0;

while ($data2 = db_fetch_array($rsSql)){

$pdf->SetXY(55/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['producto_idproducto'], 0, ',', '.');

$number = sprintf("%' 9s",$number);

$pdf->Write(0,$number ); // Id Producto

$pdf->SetXY(128/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$string = iconv("UTF-8", "ISO-8859-1//TRANSLIT", $data2['Nombre']); // Convert UTf8

$pdf->Write(0,$string); // Nonbre

$pdf->SetXY(322.71/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Precio'], 2, ',', '.');

$number = sprintf("%' 7s",$number);

$pdf->Write(0,$number ); // Precio

$pdf->SetXY(375/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Cantidad'], 0, ',', '.');

$number = sprintf("%' 10s",$number);

$pdf->Write(0,$number ); // Cantidad

$pdf->SetXY(443/$coef_x, ((206.5+($countLines*14.63))/$coef_y));

$number = number_format($data2['Valor'], 2, ',', '.');

$number = sprintf("%' 11s",$number);

$pdf->Write(0,$number ); // Valor

$countLines=$countLines+1;

}

// adding the second page of the template

$tplIdx2 = $pdf->importPage(2);

$s = $pdf->getTemplatesize($tplIdx2);

$pdf->AddPage('', $s);

$pdf->useImportedPage($tplIdx2);

// $pdf->Output('I','Factura.pdf');

// -------------------- foot to save the new PDF document ------------------

$temp_file = tempnam(sys_get_temp_dir(), 'PDF');

$pdf->Output('F',$temp_file);

// ------------------ Operation with file result -------------------------------------------

// $documento = file_get_contents($temp_file);

// unlink($temp_file); // delete file tmp

// header("Content-Disposition: attachment; filename= factura.pdf");

// header('Content-Type: application/pdf');

//echo $documento;

// ------------------ Email ------------------------------------------------------------------

$from = "info@fhumanes.com";

$to = $data['Email'];

$msg = "I enclose your invoice.";

$subject="Invoice";



$attachments = array();

// Attachments description. The 'path'(a path to the attachment) is required. Others parameters are optional:

//'name' overrides the attachment name, 'encoding' sets a file encoding, 'type' sets a MIME type

$attachments = array(

array('path' => $temp_file,

'name' => 'invoice.pdf',

'encoding' => 'base64',

'type' => 'application/pdf'));



$ret = runner_mail(array('from' => $from, 'to' => $to, 'subject' => $subject, 'body' => $msg, 'attachments' => $attachments));



if(!$ret["mailed"]){

echo $ret["message"];

} else {

echo "Email successfully sent";

}

unlink($temp_file); // delete file tmp

?>


I also leave you the complete example, on my portal, so that you can display it on your PC.
For any questions write me an email at [email="fernandohumanes@gmail.com"]fernandohumanes@gmail.com[/email]
need2sleepDevClub member 4/21/2020

very cool! thank you
G
 		Getinet 5/7/2020

Hi
[color=#696969]
[color=#696969]
[color=#696969]
[color=#696969]
fhumanes author 9/21/2020

Hello:
In response to some of you who required that the invoice in PDF format incorporate a QR barcode, I have updated the example so that a QR image is included in the invoice.
To obtain the QR code I have made another example that you can see in:

https://asprunner.com/forums/topic/27785-guide-3-for-phprunner-newbies-generate-qr/
Greetings,
fernando