Invoice PDF generation good

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);