From 7cc64ad35e7b9eccc093edcb50d15a6b22a96a8d Mon Sep 17 00:00:00 2001 From: arf20 Date: Sat, 23 Mar 2024 04:15:17 +0100 Subject: Invoice PDF generation good --- README.md | 2 +- makeinvoices.php | 140 ++++++++++++++++++++++++++++++++++++++++++++++++------- manageorders.php | 2 - 3 files changed, 123 insertions(+), 21 deletions(-) diff --git a/README.md b/README.md index 1df17ef..5d0c86f 100644 --- a/README.md +++ b/README.md @@ -1,7 +1,7 @@ # arfnet2-cstims ARFNET2 Client, Service, Ticket and Invoice Management System -Depends on PHPMailer +Depends on PHPMailer and TCPDF ``` User types: diff --git a/makeinvoices.php b/makeinvoices.php index c0c68cc..550fc14 100644 --- a/makeinvoices.php +++ b/makeinvoices.php @@ -6,6 +6,13 @@ require_once("/usr/share/doc/php-tcpdf/examples/tcpdf_include.php"); require_once "config.php"; +// Get clients +$sql = "SELECT id, username FROM users WHERE type = 'client'"; +$stmt = mysqli_prepare($link, $sql); +mysqli_stmt_execute($stmt); +$result = mysqli_stmt_get_result($stmt); +$clients = $result->fetch_all(MYSQLI_ASSOC); + // Get due orders $sql = "SELECT id, service, name, client, billing FROM orders"; $stmt = mysqli_prepare($link, $sql); @@ -13,23 +20,50 @@ mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); $dueorders = $result->fetch_all(MYSQLI_ASSOC); -/*foreach ($dueorders as $dueorder) { - generate_pdf($dueorder); -}*/ -generate_pdf($dueorders[0]); +// Get services +$sql = "SELECT id, name, type, billing, description FROM services"; +$stmt = mysqli_prepare($link, $sql); +mysqli_stmt_execute($stmt); +$result = mysqli_stmt_get_result($stmt); +$services = $result->fetch_all(MYSQLI_ASSOC); + + +foreach ($clients as $client) { + generate_pdf($client, array_filter($dueorders, function($e) { global $client; return $e["client"] == $client["id"]; })); +} + + +function getservicebyid($id) { + global $services; + foreach ($services as $service) { + if ($service["id"] == $id) { + return $service; + } + } +} + + +function generate_pdf($client, $dueorders) { + global $link; + // get next invoice id + $sql = "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'invoices'"; + $stmt = mysqli_prepare($link, $sql); + mysqli_stmt_execute($stmt); + $result = mysqli_stmt_get_result($stmt); + $nextid = $result->fetch_all(MYSQLI_ASSOC)[0]["AUTO_INCREMENT"]; -function generate_pdf($dueorder) { // Extend the TCPDF class to create custom Header and Footer class InvoicePDF extends TCPDF { //Page header public function Header() { - // Logo + $image_file = "arfnet_logo.png"; - $this->Image($image_file, 10, 10, 15, '', 'PNG', '', 'T', false, 300, '', false, false, 0, false, false, false); - // Set font - $this->SetFont('helvetica', 'B', 20); + $this->Image($image_file, 15, 30, 30, '', 'PNG', '', 'T', false, 300, '', false, false, 0, false, false, false); + + $this->SetFont('helvetica', 'B', 35); + $this->SetXY(50, 43); // Title - $this->Cell(0, 15, 'ARFNET', 0, false, 'C', 0, '', 0, false, 'M', 'M'); + $this->Cell(0, 30, 'ARFNET', 0, false, 'L', 0, '', 0, false, 'C', 'C'); } // Page footer @@ -41,6 +75,38 @@ function generate_pdf($dueorder) { // Page number $this->Cell(0, 10, 'Page '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, false, 'C', 0, '', 0, false, 'T', 'M'); } + + // Table + public function ColoredTable($header, $colw, $colalg, $data, $lastbold = false) { + // Colors, line width and bold font + $this->SetFillColor(59, 142, 234); + $this->SetTextColor(255); + $this->SetDrawColor(128, 0, 0); + $this->SetLineWidth(0.3); + $this->SetFont('', 'B'); + // Header + $w = $colw; + $num_headers = count($header); + for($i = 0; $i < $num_headers; $i++) { + $this->Cell($w[$i], 7, $header[$i], 1, 0, 'C', 1); + } + $this->Ln(); + // Color and font restoration + $this->SetFillColor(224, 235, 255); + $this->SetTextColor(0); + $this->SetFont(''); + // Data + $fill = 0; + foreach($data as $key => $row) { + if ($key === array_key_last($data)) $this->SetFont('', 'B'); + for ($i = 0; $i < $num_headers; $i++) { + $this->Cell($w[$i], 6, $row[$i], 'LR', 0, $colalg[$i], $fill); + } + $this->Ln(); + $fill=!$fill; + } + $this->Cell(array_sum($w), 0, '', 'T'); + } } $pdf = new InvoicePDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false); @@ -48,7 +114,7 @@ function generate_pdf($dueorder) { // set document information $pdf->SetCreator(PDF_CREATOR); $pdf->SetAuthor("ARFNET Client Service Ticket and Invoice Management System"); - $pdf->SetTitle("Invoice"); + $pdf->SetTitle("Invoice #$nextid"); $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING); @@ -66,19 +132,57 @@ function generate_pdf($dueorder) { $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO); // ------------------------------ - $pdf->SetFont('times', 'B', 12); - $pdf->AddPage(); + $pdf->SetFont('helvetica', 'B', 20); + $pdf->SetXY(15, 70); + $pdf->Write(0, "Invoice", '', 0, 'L', true, 0, false, false, 0); + + $pdf->SetFont('helvetica', '', 12); + $txt = - "Client ID: ".$dueorder["client"]."\n" - ."Order ID: ".$dueorder["id"]."\n\n" - ."Service: ".$dueorder["name"]."\n" - ."Amount: ".$dueorder["billing"]."\n" - ; + "Invoice ID: $nextid\n" + ."Invoice date: ".date("l, F j\t\h, Y\n") + ."Due date: ".date("l, F j\t\h, Y\n\n"); + $pdf->Write(0, $txt, '', 0, 'L', true, 0, false, false, 0); + + $pdf->SetFont('helvetica', 'B', 20); + $pdf->Write(0, "Bill to", '', 0, 'L', true, 0, false, false, 0); + $pdf->SetFont('helvetica', '', 12); + $txt = + $client["username"]."\n" + ."Client ID: ".$client["id"]."\n\n"; $pdf->Write(0, $txt, '', 0, 'L', true, 0, false, false, 0); + $theader = array("Order ID", "Instance name", "Service", "Price", "Amount"); + $columnsal = array("L", "L", "L", "R", "R"); + $columnsw = array(20, 80, 30, 25, 25); + $tdata = array(); + $subtotal = 0; + foreach ($dueorders as $dueorder) { + $price = (float)trim(substr($dueorder["billing"], 0, strpos($dueorder["billing"], "€"))); + $subtotal += $price; + $pricestr = number_format($price, 2, '.', '')." €/mo"; + $amountstr = number_format($price, 2, '.', '')." €"; + $tdata[] = array($dueorder["id"], $dueorder["name"], getservicebyid($dueorder["service"])["name"], $pricestr, $amountstr); + } + $subtotalstr = number_format($subtotal, 2, '.', '')." €"; + + $pdf->ColoredTable($theader, $columnsw, $columnsal, $tdata); + $pdf->Ln(); + + // final table + $theader = array("", "Amount"); + $columnsal = array("L", "R"); + $columnsw = array(35, 25); + $tdata = array(); + $tdata[] = array("Subtotal", $subtotalstr); + $tdata[] = array("Sales Tax 0.00%", $subtotalstr); + $tdata[] = array("TOTAL", $subtotalstr); + + $pdf->ColoredTable($theader, $columnsw, $columnsal, $tdata, true); + $pdf->Output('invoice.pdf', 'I'); } diff --git a/manageorders.php b/manageorders.php index 7ad34ac..565ef91 100644 --- a/manageorders.php +++ b/manageorders.php @@ -17,8 +17,6 @@ require_once "config.php"; // Get clients $sql = "SELECT id, username FROM users WHERE type = 'client'"; $stmt = mysqli_prepare($link, $sql); -//mysqli_stmt_bind_param($stmt, "s", $param_type); -//$param_type = "client"; mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); $clients = $result->fetch_all(MYSQLI_ASSOC); -- cgit v1.2.3