From 0c86e53fb5d51ae1c853226ebc6673dbca17deca Mon Sep 17 00:00:00 2001 From: arf20 Date: Sat, 23 Mar 2024 23:41:18 +0100 Subject: Monthly hour-based billing invoice generation --- makeinvoices.php | 148 ++++++++++++++++++++++++++++++------------------------- 1 file 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(); -- cgit v1.2.3