Monthly hour-based billing invoice generation

1 files changed, 81 insertions, 67 deletions

diff --git a/makeinvoices.php b/makeinvoices.php
index 550fc14..2060b13 100644
--- a/makeinvoices.php
+++ b/makeinvoices.php
@@ -4,6 +4,63 @@
 
 require_once("/usr/share/doc/php-tcpdf/examples/tcpdf_include.php");
 
+// Extend the TCPDF class to create custom Header and Footer
+class InvoicePDF extends TCPDF {
+    //Page header
+    public function Header() {
+
+        $image_file = "arfnet_logo.png";
+        $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, 30, 'ARFNET', 0, false, 'L', 0, '', 0, false, 'C', 'C');
+    }
+
+    // Page footer
+    public function Footer() {
+        // Position at 15 mm from bottom
+        $this->SetY(-15);
+        // Set font
+        $this->SetFont('helvetica', 'I', 8);
+        // 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 ($lastbold && $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');
+    }
+}
+
 require_once "config.php";
 
 // Get clients
@@ -14,7 +71,7 @@ $result = mysqli_stmt_get_result($stmt);
 $clients = $result->fetch_all(MYSQLI_ASSOC);
 
 // Get due orders
-$sql = "SELECT id, service, name, client, billing FROM orders";
+$sql = "SELECT id, service, name, client, billing, date FROM orders";
 $stmt = mysqli_prepare($link, $sql);
 mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);
@@ -27,12 +84,16 @@ mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);
 $services = $result->fetch_all(MYSQLI_ASSOC);
 
+$fdom = new DateTime('first day of this month');
+$ldom = new DateTime('last day of this month');
 
 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) {
@@ -44,7 +105,7 @@ function getservicebyid($id) {
 
 
 function generate_pdf($client, $dueorders) {
-    global $link;
+    global $link, $fdom, $ldom;
     // get next invoice id
     $sql = "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'invoices'";
     $stmt = mysqli_prepare($link, $sql);
@@ -52,62 +113,6 @@ function generate_pdf($client, $dueorders) {
     $result = mysqli_stmt_get_result($stmt);
     $nextid = $result->fetch_all(MYSQLI_ASSOC)[0]["AUTO_INCREMENT"];
 
-    // Extend the TCPDF class to create custom Header and Footer
-    class InvoicePDF extends TCPDF {
-        //Page header
-        public function Header() {
-
-            $image_file = "arfnet_logo.png";
-            $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, 30, 'ARFNET', 0, false, 'L', 0, '', 0, false, 'C', 'C');
-        }
-
-        // Page footer
-        public function Footer() {
-            // Position at 15 mm from bottom
-            $this->SetY(-15);
-            // Set font
-            $this->SetFont('helvetica', 'I', 8);
-            // 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);
     
@@ -143,7 +148,7 @@ function generate_pdf($client, $dueorders) {
     $txt =
         "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");
+        ."Due date: ".date("l, F j\t\h, Y\n");
     $pdf->Write(0, $txt, '', 0, 'L', true, 0, false, false, 0);
 
     $pdf->SetFont('helvetica', 'B', 20);
@@ -155,20 +160,29 @@ function generate_pdf($client, $dueorders) {
         ."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);
+    $theader = array("Order ID", "Instance name", "Service", "Unit Price", "Quantity", "Amount");
+    $columnsal = array("L", "L", "L", "R", "R", "R");
+    $columnsw = array(18, 70, 16, 40, 16, 16);
     $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);
+        $price = (float)trim(substr($dueorder["billing"], 0, strpos($dueorder["billing"], "€"))) / (float)(30*24);
+        $pricestr = number_format($price, 4, '.', '')." €/h";
+        
+        $dueorderdate = new DateTime($dueorder["date"]);
+        $billingperiodstart = $dueorderdate > $fdom ? $dueorderdate : $fdom;
+        $billingperiod = $billingperiodstart->format("d-m-Y")." to ".$ldom->format("d-m-Y");
+        $billinginterval = date_diff($billingperiodstart, $ldom);
+        $qty = ($billinginterval->d * 24) + $billinginterval->h;
+        $amount = $price*$qty;
+        $subtotal += $amount;
+
+        $amountstr = number_format($amount, 2, '.', '')." €";
+        $tdata[] = array($dueorder["id"], $dueorder["name"]." ($billingperiod)", getservicebyid($dueorder["service"])["name"], $pricestr." (".$dueorder["billing"].")", $qty, $amountstr);
     }
     $subtotalstr = number_format($subtotal, 2, '.', '')." €";
 
+    $pdf->SetFont('helvetica', '', 10);
     $pdf->ColoredTable($theader, $columnsw, $columnsal, $tdata);
     $pdf->Ln();