# HG changeset patch
# User Michiel Broek <mbroek@mbse.eu>
# Date 1566591973 -7200
# Node ID 4dbfa131d1734e6a32b2d5d095407e7ce4a8a0af
# Parent  259c97782bf8cf178b3338774208a866535e36a9
Added total and efficiency reports

diff -r 259c97782bf8 -r 4dbfa131d173 www/images/printer.png
Binary file www/images/printer.png has changed
diff -r 259c97782bf8 -r 4dbfa131d173 www/includes/global.inc.php
--- a/www/includes/global.inc.php	Thu Aug 22 14:10:58 2019 +0200
+++ b/www/includes/global.inc.php	Fri Aug 23 22:26:13 2019 +0200
@@ -249,6 +249,12 @@
 	 <li><img style='float: left; margin-right: 5px;' src='images/insert-image.png' />Etiket</li>
         </ul>
        </li>
+       <li><img style='float: left; margin-right: 5px;' src='images/printer.png' />Rapporten
+        <ul style='width: 200px;'>
+         <li><a href="prod_r_totals.php" target="_blank">Totaal productie</a></li>
+         <li><a href="prod_r_efficiency.php" target="_blank">Rendementen</a></li>
+        </ul>
+       </li>
        <li><img style='float: left; margin-right: 7px;' src='images/calendar.png' />Kalender</li>
       </ul>
      </li>
diff -r 259c97782bf8 -r 4dbfa131d173 www/prod_r_efficiency.php
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/www/prod_r_efficiency.php	Fri Aug 23 22:26:13 2019 +0200
@@ -0,0 +1,159 @@
+<?php
+require_once($_SERVER['DOCUMENT_ROOT'].'/includes/global.inc.php');
+require_once($_SERVER['DOCUMENT_ROOT'].'/includes/formulas.php');
+require_once($_SERVER['DOCUMENT_ROOT'].'/fpdf/fpdf.php');
+
+
+$link = mysqli_connect(DBASE_HOST,DBASE_USER,DBASE_PASS,DBASE_NAME);
+if (! $link) {
+        die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
+}
+if (! mysqli_set_charset($link, "utf8" )) {
+        echo "error";
+        return 1;
+}
+
+setlocale ( LC_ALL, 'nl_NL.UTF-8');
+
+date_default_timezone_set('Europe/Amsterdam');
+$prdate = date(DATE_RFC2822);
+
+class PDF_MySQL_Table extends FPDF
+{
+        protected $ProcessingTable=false;
+        protected $aCols=array();
+        protected $TableX;
+
+        function Header() {
+                // Print the table header if necessary
+                if ($this->ProcessingTable)
+                        $this->TableHeader();
+        }
+
+        function TableHeader() {
+                $this->SetFont('Helvetica','B',9);
+                $this->SetX($this->TableX);
+                $this->SetFillColor(255,150,100);
+                foreach($this->aCols as $col)
+                        $this->Cell($col['w'],5,$col['c'],0,0,$col['a'],true);
+                $this->Ln();
+        }
+
+        function AddCol($width=-1, $caption='', $align='L') {
+                $this->aCols[]=array('c'=>$caption,'w'=>$width,'a'=>$align);
+        }
+
+	function TableTotals() {
+
+		global $link;
+
+		$vul = $this->w - $this->rMargin - $this->lMargin - 128;
+		$this->AddCol(18,'Code','L');
+		$this->AddCol($vul,'Naam','L');
+		$this->AddCol(30,'Bier stijl','L');
+		$this->AddCol(20,'Max extract','R');
+		$this->AddCol(20,'Maish eff.','R');
+		$this->AddCol(20,'Spoel eff.','R');
+		$this->AddCol(20,'Kook eff.','R');
+
+		$cMargin=$this->cMargin;
+                $this->cMargin=2;
+                $this->TableX=$this->lMargin;
+		$this->TableHeader();
+                $this->ProcessingTable=true;
+
+		$this->SetFont('Helvetica','',9);
+                $this->SetFillColor(210,245,255);
+
+		$result = mysqli_query($link, "SELECT * FROM products WHERE package_date AND type='2' ORDER BY code");
+		while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
+			/*
+			 * Data is not always available, try to calculate the mssing pieces.
+			 */
+			$mvol = 0;
+                	$msugars = 0;   // mash sugars
+			$ssugars = 0;
+			$arr = json_decode($row['json_mashs'], true);
+			foreach($arr as $item) {
+				if ($item['step_type'] == 0)
+                                        $mvol += $item['step_infuse_amount']; 
+			}
+
+			$ferms = json_decode($row['json_fermentables'], true);
+			foreach($ferms as $item) {
+                                if ($item['f_added'] == 0) {
+                                        $d = $item['f_amount'] * ($item['f_yield'] / 100) * (1 - $item['f_moisture'] / 100);
+					$ssugars += $item['f_amount'];
+                                        $mvol += $item['f_amount'] * $item['f_moisture'] / 100;
+                                        $msugars += $d;
+                                }
+                        }
+                        $sugardensity = 1.611;
+                        $v = $msugars / $sugardensity + $mvol;
+                        $plato = 1000 * $msugars / ($v * 10);   // deg. Plato
+			$mash_efficiency = floatval($row['brew_mash_efficiency']);
+			$mash_sg = floatval($row['brew_mash_sg']);
+			if (($mash_efficiency == 0) && ($mash_sg > 1)) {
+				$mash_efficiency = 100 * sg_to_plato($mash_sg) / $plato;
+			}
+			$mash_extract = 100 * $msugars / $ssugars;
+			$preboil_sg = floatval($row['brew_preboil_sg']);
+			$preboil_volume = floatval($row['brew_preboil_volume']);
+			$est_preboil_plato = sg_to_plato($preboil_sg) * ($preboil_volume / 1.04) * $preboil_sg * 10 / 1000;
+			$preboil_efficiency = floatval($row['brew_preboil_efficiency']);
+			if (($msugars > 0) && ($preboil_efficiency == 0))
+				$preboil_efficiency = $est_preboil_plato / $msugars * 100;
+			if ($preboil_efficiency < 0)
+				$preboil_efficiency = 0;
+			$aboil_efficiency = floatval($row['brew_aboil_efficiency']);
+
+			$name    = iconv('UTF-8','windows-1252',$row['name']);
+			$st_name = iconv('UTF-8','windows-1252',$row['st_name']);
+			$this->SetX($this->TableX);
+			$this->Cell(18,5,$row['code'],0,0,'L',true);
+			$this->Cell($vul,5,$name,0,0,'L',true);
+			$this->Cell(30,5,$st_name,0,0,'L',true);
+			$this->Cell(20,5,sprintf("%.1f",$mash_extract).'%',0,0,'R',true);
+			$this->Cell(20,5,sprintf("%.1f",$mash_efficiency).'%',0,0,'R',true);
+			$this->Cell(20,5,sprintf("%.1f",$preboil_efficiency).'%',0,0,'R',true);
+			$this->Cell(20,5,sprintf("%.1f",$aboil_efficiency).'%',0,0,'R',true);
+			$this->Ln();
+		}
+		$this->ProcessingTable=false;
+                $this->cMargin=$cMargin;
+                $this->aCols=array();
+                $this->Ln();
+	}
+}
+
+
+
+class PDF extends PDF_MySQL_Table {
+        function Header() {
+		global $prdate;
+		global $my_brewery_name;
+                $this->Image('images/logo.png',10,10,30);
+                // Title
+                $this->SetFont('Helvetica','B',18);
+                $this->SetX(45);
+                $this->Cell(0,8,"Efficientie ".$my_brewery_name,0,1,'L');
+                $this->Ln(1);
+                $this->SetFont('Helvetica','',10);
+                $this->SetX(45);
+                $this->Cell(17,5,'Datum:',0,0,'L');
+                $this->Cell(0,5,$prdate,0,1,'L');
+                $this->Ln(20);
+                // Ensure table header is printed
+                parent::Header();
+        }
+}
+
+
+
+/*
+ * Generate PDF
+ */
+$pdf = new PDF();
+$pdf->AddPage();
+$pdf->TableTotals();
+$pdf->Output();
diff -r 259c97782bf8 -r 4dbfa131d173 www/prod_r_totals.php
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/www/prod_r_totals.php	Fri Aug 23 22:26:13 2019 +0200
@@ -0,0 +1,133 @@
+<?php
+require_once($_SERVER['DOCUMENT_ROOT'].'/includes/global.inc.php');
+require_once($_SERVER['DOCUMENT_ROOT'].'/includes/formulas.php');
+require_once($_SERVER['DOCUMENT_ROOT'].'/fpdf/fpdf.php');
+
+
+$link = mysqli_connect(DBASE_HOST,DBASE_USER,DBASE_PASS,DBASE_NAME);
+if (! $link) {
+        die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
+}
+if (! mysqli_set_charset($link, "utf8" )) {
+        echo "error";
+        return 1;
+}
+
+setlocale ( LC_ALL, 'nl_NL.UTF-8');
+
+date_default_timezone_set('Europe/Amsterdam');
+$prdate = date(DATE_RFC2822);
+
+class PDF_MySQL_Table extends FPDF
+{
+        protected $ProcessingTable=false;
+        protected $aCols=array();
+        protected $TableX;
+
+        function Header() {
+                // Print the table header if necessary
+                if ($this->ProcessingTable)
+                        $this->TableHeader();
+        }
+
+        function TableHeader() {
+                $this->SetFont('Helvetica','B',9);
+                $this->SetX($this->TableX);
+                $this->SetFillColor(255,150,100);
+                foreach($this->aCols as $col)
+                        $this->Cell($col['w'],5,$col['c'],0,0,$col['a'],true);
+                $this->Ln();
+        }
+
+        function AddCol($width=-1, $caption='', $align='L') {
+                $this->aCols[]=array('c'=>$caption,'w'=>$width,'a'=>$align);
+        }
+
+	function TableTotals() {
+
+		global $link;
+
+		$vul = ($this->w - $this->rMargin - $this->lMargin - 130) / 2;
+		$this->Ln();
+		$this->AddCol(15,'Nummer','C');
+		$this->AddCol(10,'Jaar','C');
+		$this->AddCol(35,'Brouw sessies','R');
+		$this->AddCol(35,'Brouw volume','R');
+		$this->AddCol(35,'Gemiddeld volume','R');
+
+		$total = $tvolume = $regel = 0;
+		$cMargin=$this->cMargin;
+                $this->cMargin=2;
+                $this->TableX=$this->lMargin+$vul;
+		$this->TableHeader();
+                $this->ProcessingTable=true;
+
+		$this->SetFont('Helvetica','',9);
+                $this->SetFillColor(210,245,255);
+
+		$result = mysqli_query($link, "SELECT DISTINCT YEAR(package_date) FROM products WHERE package_date ORDER BY package_date");
+		while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
+
+			$regel++;
+			$year = $row['YEAR(package_date)'];
+			$this->SetX($this->TableX);
+
+			$brews = 0;
+			$packaged = 0;
+
+			$result2 = mysqli_query($link, "SELECT package_volume FROM products WHERE package_date AND YEAR(package_date) = '".$year."'");
+			while ($row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC)) {
+				$brews++;
+				$total++;
+				$packaged += floatval($row2['package_volume']);
+				$tvolume += floatval($row2['package_volume']);
+			}
+			$average = $packaged / $brews;
+			$this->Cell(15,5,$regel,0,0,'C',true);
+			$this->Cell(10,5,$year,0,0,'C',true);
+			$this->Cell(35,5,$brews,0,0,'R',true);
+			$this->Cell(35,5,sprintf("%.1f",$packaged).' L',0,0,'R',true);
+			$this->Cell(35,5,sprintf("%.1f",$average).' L',0,0,'R',true);
+			$this->Ln();
+		}
+		$this->Cell($vul+25,5,'',0,0,'L',false);
+		$this->Cell(35,5,$total,0,0,'R',true);
+                $this->Cell(35,5,sprintf("%.1f",$tvolume).' L',0,0,'R',true);
+		$this->ProcessingTable=false;
+                $this->cMargin=$cMargin;
+                $this->aCols=array();
+                $this->Ln();
+	}
+}
+
+
+
+class PDF extends PDF_MySQL_Table {
+        function Header() {
+		global $prdate;
+		global $my_brewery_name;
+                $this->Image('images/logo.png',10,10,30);
+                // Title
+                $this->SetFont('Helvetica','B',18);
+                $this->SetX(45);
+                $this->Cell(0,8,"Jaarproductie ".$my_brewery_name,0,1,'L');
+                $this->Ln(1);
+                $this->SetFont('Helvetica','',10);
+                $this->SetX(45);
+                $this->Cell(17,5,'Datum:',0,0,'L');
+                $this->Cell(0,5,$prdate,0,1,'L');
+                $this->Ln(20);
+                // Ensure table header is printed
+                parent::Header();
+        }
+}
+
+
+
+/*
+ * Generate PDF
+ */
+$pdf = new PDF();
+$pdf->AddPage();
+$pdf->TableTotals();
+$pdf->Output();