ordersprinter/webapp/php/utilities/pdfexport.php

<?php

require_once (__DIR__. '/../3rdparty/fpdf/fpdf.php');
require_once (__DIR__. '/../closing.php');


class PdfExport extends FPDF {
	private $tabheader = array();
	private $flengths = array();
	private $maxTableLength = 190;
	private $curtable = "";
	private $lang = 0;
	private $decpoint = ":";
	private $currency = "";
	private $version;
	
	private $osSummary = array("Zusammenfassung","Summary","Todo");
	private $osClosingsPlural = array("Tageserfassungen","Closings","Cerradas");
	private $osSum = array("Summe","Sum","Suma");
	private $osSumAll = array("Gesamtsumme","Total Sum","Todos las sumas");
	private $osClosingTxt = array("Tagesabschluss","Closing","Conclusión");
	private $hintNoEmptyClosings = array("Übersicht der Tageserfassungen - detaillierter Umsatzreport über CSV/Excel-Export möglich",
			"Overview of closings - detailed sales report possible with csv/Excel export",
			"Conclusiones en este periodo - más informaciones en el csv/Excel-export"
	);
	private $taxTxt = array("Steuersatz (%)","Tax (%)","Impuesto (%)");
	private $taxSumTxt = array("Summierung nach Steuersätzen","Sums grouped by taxes","Sumas por impuesta");
	private $cashOpTxt = array("Kassenein-/auslagen","Cash inserts/outputs","contribuciones en efectivo");
	
	private static function osGetMonthName($language,$month) {
		$months = array("1" => array("Januar","January","Enero"),
				"2" => array("Februar","February","Febrero"),
				"3" => array("März","March","Marzo"),
				"4" => array("April","April","Abril"),
				"5" => array("Mai","May","Mayo"),
				"6" => array("Juni","June","Junio"),
				"7" => array("Juli","July","Julio"),
				"8" => array("August","August","Agosto"),
				"9" => array("September","September","Septiembre"),
				"10" => array("Oktober","October","Octubre"),
				"11" => array("November","November","Noviembre"),
				"12" => array("Dezember","December","Diciembre")
		);
		return utf8_decode($months["$month"][$language]);
	}
	
	private static function osGetSaleItemName($l,$item) {
		$t = array(
				"TEID" => array("Tag.abschl.","Closing","Cerrada"),
				"ID" => array("Bonid","ID","Nú"),
				"Date" => array("Zahlungsdatum","Pay date","Fecha de pago"),
				"Prod" => array("Produkt","Product","Producto"),
				"Brutto" => array("Bruttopreis","Gross","Bruto"),
				"Netto" => array("Nettopreis","Net","Neto"),
				"Tax" => array("MwSt (%)","Tax (%)","IVA (%)"),
				"PayWay" => array("Zahlungsart","Method of payment","Modo de pago"),
				"Userid" => array("Benutzer-ID","User id","Id del usario"),
				"User" => array("Benutzername","User name","Nombre del usario"),
				"State" => array("Status","State","Estado"),
				"Ref" => array("Ref.-Bon","Ref. Receipt","Tique ref."),
				"ClosId" => array("Tageslosung-ID","Closing id","Número de cerramiento"),
				"ClosDate" => array("Tageslosung-Datum","Closing date","Fecha de cerramiento"),
				"ClosRemark" => array("Tageslosung-Bemerkung","Closing remark","Comentario de cerramiento"),
				"laterCancelled" => array("nachher storniert","later cancelled","anulado después"),
				"storno" => array("Stornierungsbuchung","cancel transaction","acción anulada"),
				"cashact" => array("Bareinlage/-entnahme","cash action","sacar/insertar contado"),
				"cashaction" => array("Kassenaktion","cash action","sacar/insertar contado"),
				"host" => array("Bew.bon","Guest","Repr."),
				"sum" => array("Summe","Sum","Todo")
				//"host" => array("Bewirtungsbeleg","Guest Invoice","Tique de gastos de representación")
		);
		return utf8_decode($t["$item"][$l]);
	}
	private static function getConfigItem($pdo,$item) {
		$sql = "SELECT setting FROM %config% WHERE name=?";
		$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
		$stmt->execute(array($item));
		$row = $stmt->fetchObject();
		return $row->setting;
	}
	
	private function osInsertHeader($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear) {
		$this->Image("utilities/salesheader.png",30,10,70,40,"PNG","http://www.ordersprinter.de");
		
		$companyInfo = utf8_decode(self::getConfigItem($pdo,"companyinfo"));
		if (is_null($companyInfo)) {
			return;
		}

		$companyInfoParts = explode("\n",$companyInfo);
		
		$fontSizes = array(14,14,14,14,14,10,9,9);
		$boxSizes = array(10,10,10,10,10,9,7,6);
		
		$maxlines = 8;
		if (count($companyInfoParts) > $maxlines) {
			array_splice($companyInfoParts,$maxlines);
		}
		$this->SetFont('Helvetica','B',$fontSizes[count($companyInfoParts)-1]);
		
		$companyInfo = implode("\n",$companyInfoParts);
		$this->MultiCell(190-10,$boxSizes[count($companyInfoParts)-1],$companyInfo,0,"R",0);
		
		$this->SetXY(10,70);
	}
	
	private function calcStartDate($startMonth,$startYear) {
		if ($startMonth < 10) {
			$startMonth = "0" . $startMonth;
		}
		return ( $startYear . "-" . $startMonth . "-01 00:00:00");
	}
	
	private function calcEndDate($endMonth,$endYear) {
		if ($endMonth < 10) {
			$endMonth = "0" . $endMonth;
		}
		$endDate = $endYear . "-" . $endMonth . "-01";
		$lastdayOfMonth = date("t", strtotime($endDate));
		return($endYear . "-" . $endMonth . "-" . $lastdayOfMonth . " 23:59:59");
	}
	
	private function osGetSales($pdo,$l,$startMonth,$startYear,$endMonth,$endYear,$closidstart = null,$closidend = null) {
		$startDate = $this->calcStartDate($startMonth, $startYear);
		$endDate = $this->calcEndDate($endMonth, $endYear);

		$hline = array(
				self::osGetSaleItemName($l,"TEID"),
				self::osGetSaleItemName($l,"ID"),
				self::osGetSaleItemName($l,"Date"),
				self::osGetSaleItemName($l,"Brutto"),
				self::osGetSaleItemName($l,"Netto"),
				self::osGetSaleItemName($l,"State"),
				self::osGetSaleItemName($l,"Ref"),
				self::osGetSaleItemName($l,"host")
		);
		
		$allSaleLines = array();
		$allSaleLines[] = $hline;		
		
		$payment_lang = array("name","name_en","name_esp");
		$payment_col = $payment_lang[$l];
		
		if (is_null($closidstart)) {
			$sql = "SELECT DISTINCT %bill%.id,%bill%.signature,billdate,brutto,netto,IF(tax is not null, tax, '0.00') as tax,status,closingdate,remark,%bill%.host,%bill%.closingid,%payment%.$payment_col as payway,userid,ref,username FROM %bill%,%closing%,%payment%,%user% ";
			$sql .= "WHERE closingid is not null AND %bill%.closingid=%closing%.id ";
			$sql .= " AND %bill%.paymentid=%payment%.id ";
			$sql .= " AND %bill%.billdate BETWEEN ? AND ? ";
			$sql .= " AND %bill%.userid = %user%.id ";
			$sql .= "ORDER BY billdate";
			
			$allsales = CommonUtils::fetchSqlAll($pdo, $sql, array($startDate,$endDate));
		} else {
			$sql = "SELECT DISTINCT %bill%.id,%bill%.signature,billdate,brutto,netto,IF(tax is not null, tax, '0.00') as tax,status,closingdate,remark,%bill%.host,%bill%.closingid,%payment%.$payment_col as payway,userid,ref,username FROM %bill%,%closing%,%payment%,%user% ";
			$sql .= "WHERE closingid is not null AND %bill%.closingid=%closing%.id ";
			$sql .= " AND %bill%.paymentid=%payment%.id ";
			$sql .= " AND %bill%.closingid BETWEEN ? AND ? ";
			$sql .= " AND %bill%.userid = %user%.id ";
			$sql .= "ORDER BY billdate";
			
			$allsales = CommonUtils::fetchSqlAll($pdo, $sql, array($closidstart,$closidend));
		}

		foreach($allsales as $zeile) {
			$closid = $zeile['closingid'];
			$billid = $zeile['id'];
			$billdate = $zeile['billdate'];
			
			$brutto_orig = $zeile['brutto'];
			$netto_orig = $zeile['netto'];
			$tax_orig = $zeile['tax'];

			$brutto = str_replace(".",$this->decpoint,$brutto_orig);
			$netto = str_replace(".",$this->decpoint,$netto_orig);
			$tax = str_replace(".",$this->decpoint,$tax_orig);
			$signature = $zeile['signature'];
			$dbstatus = $zeile['status'];
			$status = $zeile['status'];
			if ($status == 'x') {
				$status = self::osGetSaleItemName($l,"laterCancelled");
			} else if ($status == 's') {
				$status = self::osGetSaleItemName($l,"storno");
			} else if ($status == 'c') {
				$status = self::osGetSaleItemName($l,"cashact");
			} else {
				$status = "";
			}
			
			$ref = ($zeile['ref'] == null ? "" : $zeile['ref']);
			$userid = $zeile['userid'];
			$host = ($zeile['host'] == 1 ? "x" : "-");

			if (!CommonUtils::verifyBillByValues($pdo,$billdate, $brutto_orig, $netto_orig, $userid, $signature, $dbstatus)) {
				echo "Database is inconsistent! Bill $billid ";
				if ($zeile['status'] == "c") {
					echo '- a cash operation ("Bareinlage/Barauslage"). ';
				}
				return null;
			}
				
			if ($billid == null) {
				$billid = "-";
			}
			
			$aLine = array($closid,
					$billid,
					$billdate,
					$brutto,
					$netto,
					$status,
					$ref,
					$host
			);
			
			$allSaleLines[count($allSaleLines)] = $aLine;
		}
		
		return $allSaleLines;
	}
	
	private function osCalculateColsWidth($lengths) {
		$sum = 0;
		foreach($lengths as $l) {
			$sum += $l;
		}
		
		$f = 190.0 / $sum;
		$this->flengths = array();
		$this->maxTableLength = 0;
		foreach($lengths as $l) {
			$this->flengths[] = intval($l * $f);
			$this->maxTableLength += intval($l * $f);
		}
	}
	
	private function osInsertSales($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear,$closidstart = null,$closidend = null) {
		$this->curtable = "sales";
		$salesArr = $this->osGetSales($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear,$closidstart,$closidend);
		
		$this->osCalculateColsWidth(array(4,4,10,5,5,10,4,4));
		
		$salestxt = array("Umsätze ","Sales ","Venta ");
		
		if (!is_null($closidstart)) {
			$headerLine = utf8_decode($this->osClosingsPlural[$lang]);
			$headerLine .= " $closidstart - $closidend ";
			$headerLine .= " (in " . self::getConfigItem($pdo,"currency") . ")";
		} else {
			$headerLine = utf8_decode($salestxt[$lang]);
			$headerLine .= self::osGetMonthName($lang, $startMonth) . " $startYear - " .  self::osGetMonthName($lang, $endMonth) . " $endYear";
			$headerLine .= " (in " . self::getConfigItem($pdo,"currency") . ")";
		}

		$this->SetFont('Helvetica','B',14);
		$this->Cell($this->maxTableLength,10,$headerLine,1,1,"C");
		
		$this->SetFont('Helvetica','',8);
		$this->tabheader = $salesArr[0];
		$this->setSalesTableHeader();
		
		$this->SetFillColor(230,230,255);
		$fill = 1;
		
		$bruttosum = 0.0;
		$nettosum = 0.0;
		
 		for ($i=1;$i<count($salesArr);$i++) {
 			$line = $salesArr[$i];

 			$bruttosum += str_replace($this->decpoint,".",$line[3]);
 			$nettosum += str_replace($this->decpoint,".",$line[4]);
 			for ($j=0;$j<count($line);$j++) {
 				$aVal = $line[$j];	
				if ($j == 4) {
					$aVal = number_format(floatval(str_replace(',','.',$aVal)),2,$this->decpoint,'');
				}
 				$this->Cell($this->flengths[$j],6,$aVal,"LR",0,"R",$fill);
 			}
 			$this->Ln();
 			$fill = 1-$fill;
 		}
 		
 		$bruttosum = number_format($bruttosum, 2, $this->decpoint, '');
 		$nettosum = number_format($nettosum, 2, $this->decpoint, '');
 		
 		$this->SetFillColor(200,200,200);
 		$this->Cell($this->flengths[0] + $this->flengths[1] + $this->flengths[2],10,$this->osGetSaleItemName($lang,"sum"). ": ","LRBT",0,"L",1);
 		$this->Cell($this->flengths[3],10,$bruttosum,"LRBT",0,"R",1);
 		$this->Cell($this->flengths[4],10,$nettosum,"LRBT",0,"R",1);
		$this->Cell($this->flengths[5] + $this->flengths[6] + $this->flengths[7],10,"","T",0,"R",0);
 		$this->Ln();
		return;
	}
	
	function Header()
	{
		if ($this->curtable == "sales") {
			$this->setSalesTableHeader();
		} else if ($this->curtable == "prods") {
			$this->setProdsTableHeader();
		} else if ($this->curtable == "summary") {
			$this->setSummaryTableHeader();
		} else if ($this->curtable == "taxes") {
			$this->setTaxesTableHeader();
		}
	}
	function Footer()
	{
		$this->SetFont('Helvetica','I',8);
		$x = $this->GetX();
		$y = $this->GetY();
		$this->SetXY(10,280);
		$this->Cell(190,10,"OrderSprinter " . $this->version,0,1,"R");
		$this->SetXY($x,$y);
		
		if ($this->curtable == "sales") {
			$this->Cell($this->maxTableLength,1,"","T",0);
		}
	}
	
	private function setSalesTableHeader() {
		$this->SetFillColor(200,200,200);

		for ($i=0;$i<count($this->tabheader);$i++) {
			$aVal = $this->tabheader[$i];
			$this->Cell($this->flengths[$i],10,$aVal,1,0,"R",1);
		}
		$this->Ln();
	}
	
	private function setProdsTableHeader() {
		$this->SetFont('Helvetica','B',12);
		$this->SetFillColor(200,200,200);
		
		$this->Cell(70,10,$this->osGetSaleItemName($this->lang,"Prod"),"LRTB",0,"R",1);
		$this->Cell($this->maxTableLength-70,10,"Brutto","LRTB",0,"L",1);
		$this->Ln();
	}
	
	private function osInsertProdStat($pdo,$startMonth,$startYear,$endMonth,$endYear,$closidstart=null,$closidend=null) {
		$this->curtable = "";
		
		if (is_null($closidstart)) {
			$prodtxt = array("Produktstatistik ","Product Report ","Venta de productos ");
			$headerLine = utf8_decode($prodtxt[$this->lang]);
			$headerLine .= self::osGetMonthName($this->lang, $startMonth) . " $startYear - " .  self::osGetMonthName($this->lang, $endMonth) . " $endYear";
			$headerLine .= " (in " . $this->currency . ")";
		} else {
			$prodtxt = array("Produktstatistik ","Product Report ","Venta de productos ");
			$headerLine = utf8_decode($prodtxt[$this->lang]);
			$headerLine .=  " " . utf8_decode($this->osClosingsPlural[$this->lang]);
			$headerLine .= " $closidstart - $closidend ";
			$headerLine .= " (in " . self::getConfigItem($pdo,"currency") . ")";
		}
		$this->SetFont('Helvetica','B',14);
		$this->Cell($this->maxTableLength,10,$headerLine,1,1,"C");
		
		$reports = new Reports();

		if (is_null($closidstart)) {
			$startDate = $this->calcStartDate($startMonth, $startYear);
			$endDate = $this->calcEndDate($endMonth, $endYear);

			$prodStat = $reports->sumSortedByProducts($pdo, $startDate, $endDate,null,null,null);
		} else {
			$prodStat = $reports->sumSortedByProducts($pdo, 0,0,$closidstart,$closidend,null);
		}
		
		$this->setProdsTableHeader();
		
		$this->curtable = "prods";
		
		$this->SetFont('Helvetica','',8);
		$this->SetFillColor(180,240,180);
		
		$content = $prodStat["content"];
		$sum = 0.0;
		
		if ($prodStat["max"] != 0) {
			$f = ($this->maxTableLength-70.0) / $prodStat["max"];
			
			foreach($content as $prod) {
				$item = utf8_decode($prod["iter"]);
				$val = $prod["sum"];
				$sum += $val;
				
				$this->Cell(70,6,$item,0,0,"R",0);
				
				$this->Cell(max(intval($val * $f),10.1),6,str_replace(".",$this->decpoint,$val),1,1,"L",1);
			}
		}
		
		$sum = number_format($sum, 2, $this->decpoint, '');
		$this->SetFont('Helvetica','B',12);
		$this->SetFillColor(200,200,200);
		$this->Cell(70,10,$this->osGetSaleItemName($this->lang,"sum") . ": ",0,0,"R");
		$this->SetFillColor(180,180,180);
		$this->Cell(20,10,$sum,0,0,"C",1);
	}
	
	private function daySummary($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear,$closidstart = null,$closidend = null) {
		$day = sprintf("%02s", 1);
		$startMonth = sprintf("%02s", $startMonth);
		$startYear = sprintf("%04s", $startYear);
		$endMonth = sprintf("%02s", $endMonth);
		$endYear = sprintf("%04s", $endYear);
		$startDate = "$startYear-$startMonth-01";

		$endDateDay1 = "$endYear-$endMonth-01";
		$lastdayOfMonth = sprintf("%02s",date("t", strtotime($endDateDay1)));
		$endDate = "$endYear-$endMonth-$lastdayOfMonth";
		
		

		$allbSum = 0;
		$allnSum = 0;
		
		$bSum = 0;
		$nSum = 0;
		
		if (is_null($closidstart)) {
			$sql = "SELECT DISTINCT %closing%.id as id, DATE(closingdate) as datewotime,TIME(closingdate) as thetime FROM %closing% WHERE DATE(closingdate) >= ? AND DATE(closingdate) <= ? ";
			$allClosings = CommonUtils::fetchSqlAll($pdo, $sql, array($startDate,$endDate));
		} else {
			$sql = "SELECT DISTINCT %closing%.id as id, DATE(closingdate) as datewotime,TIME(closingdate) as thetime FROM %closing% WHERE id >= ? AND id <= ? ";
			$allClosings = CommonUtils::fetchSqlAll($pdo, $sql, array($closidstart,$closidend));
		}
		
		$entry = false;
 		foreach ($allClosings as $aClosing) {
 			$entry = true;
 			$closingid = $aClosing["id"];
 			$date = $this->osDateToGerman($aClosing["datewotime"]) . " " . $aClosing["thetime"];
 			
			$sql = "SELECT %queue%.tax,SUM(price) as brutto,ROUND(SUM(price)/(1 + %queue%.tax/100.0),2) as netto FROM %queue%,%bill% WHERE billid=%bill%.id AND %bill%.closingid=? GROUP BY %queue%.tax";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array($closingid));
			$closingDetails = $stmt->fetchAll(PDO::FETCH_OBJ);
		

			$bSum = 0;
			$nSum = 0;

			$this->SetFont('Helvetica','B',12);
			$this->Cell($this->maxTableLength,10,"ID: $closingid ($date)",0,1,"L",0);
			
			foreach($closingDetails as $aClosingDetail) {
				$tax = str_replace(".",$this->decpoint,$aClosingDetail->tax);
				$brutto = str_replace(".",$this->decpoint,$aClosingDetail->brutto);
				$netto = str_replace(".",$this->decpoint,$aClosingDetail->netto);
				$sumtax = number_format($aClosingDetail->brutto - $aClosingDetail->netto, 2, $this->decpoint, '');
				$sumtax = str_replace(".",$this->decpoint,$sumtax);
				
				$this->SetFont('Helvetica','',8);
				$this->Cell($this->flengths[0],6,"$tax %",0,0,"R",0);
				$this->Cell($this->flengths[1],6,$netto,0,0,"R",0);
	
				$this->Cell($this->flengths[2],6,$sumtax,0,0,"R",0);
				$this->Cell($this->flengths[3],6,$brutto,0,1,"R",0);
				$bSum += $aClosingDetail->brutto;
				$nSum += $aClosingDetail->netto;
			}
			
			$allbSum += $bSum;
			$allnSum += $nSum;
			
			$sql = "SELECT SUM(brutto) as cashsum FROM %bill% WHERE closingid=? AND status=?";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			
			$stmt->execute(array($closingid,'c'));
			$row = $stmt->fetchObject();
			$cashsum = $row->cashsum;
			
			if (!is_null($cashsum) ||  (abs($cashsum) > 0.01)) {
				$cash = str_replace(".",$this->decpoint,$cashsum);
				$this->SetFont('Helvetica','',8);
				$this->Cell($this->flengths[0],6,$this->cashOpTxt[$this->lang],0,0,"R",0);
				$this->Cell($this->flengths[1],6,$cash,0,0,"R",0);
				
				$this->Cell($this->flengths[2],6,"-",0,0,"R",0);
				$this->Cell($this->flengths[3],6,$cash,0,1,"R",0);
				$bSum += $cashsum;
				$nSum += $cashsum;
				
				$allbSum += $cashsum;
				$allnSum += $cashsum;
			}
		
			if ($entry) {
				$this->osWriteSummarySum($pdo,$this->osSum[$lang],$bSum, $nSum,10, $closingid);
				$this->Ln();
			}
		}
		
		$this->osWriteSummarySum($pdo,$this->osSumAll[$lang],$allbSum, $allnSum,14, -1);
		
		
		if (is_null($closidstart)) {
			$sql = "SELECT %queue%.tax as tax,SUM(price) as brutto,ROUND(SUM(price)/(1 + %queue%.tax/100.0),2) as netto FROM %queue%,%bill%,%closing% ";
			$sql .= " WHERE billid=%bill%.id AND closingid=%closing%.id AND DATE(closingdate) >= ? AND DATE(closingdate) <= ? GROUP BY tax";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array($startDate,$endDate));
		} else {
			$sql = "SELECT %queue%.tax as tax,SUM(price) as brutto,ROUND(SUM(price)/(1 + %queue%.tax/100.0),2) as netto FROM %queue%,%bill%,%closing% ";
			$sql .= " WHERE billid=%bill%.id AND closingid=%closing%.id AND %closing%.id >= ? AND %closing%.id <= ? GROUP BY tax";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array($closidstart,$closidend));
		}
		$taxDetails = $stmt->fetchAll(PDO::FETCH_OBJ);
		

		$this->Ln(10);
		$this->SetFont('Helvetica','UB',16);
		$this->Cell($this->maxTableLength ,10, utf8_decode($this->taxSumTxt[$lang] . ":"), 0, 1, "L",0);
		$this->Ln(5);
		
		$this->setTaxesTableHeader();
		$this->curtable = "taxes";

		$this->SetFont('Helvetica','',10);
		foreach($taxDetails as $aTaxDetail) {
			$this->Cell(50,10,str_replace(".",$this->decpoint,$aTaxDetail->tax),1,0,"C");
			$this->Cell(50,10,str_replace(".",$this->decpoint,$aTaxDetail->netto),1,0,"C");
			$this->Cell(50,10,str_replace(".",$this->decpoint,$aTaxDetail->brutto),1,1,"C");
		}
		
		
		if (is_null($closidstart)) {
			$sql = "SELECT SUM(brutto) as cashsum FROM %bill%,%closing% WHERE status=? AND closingid=%closing%.id AND DATE(closingdate) >= ? AND DATE(closingdate) <= ?";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array('c',$startDate,$endDate));
		} else {
			$sql = "SELECT SUM(brutto) as cashsum FROM %bill%,%closing% WHERE status=? AND closingid=%closing%.id AND %closing%.id >= ? AND %closing%.id <= ? ";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array('c',$closidstart,$closidend));
		}
		$row = $stmt->fetchObject();
		$cashsum = $row->cashsum;
			
		if (!is_null($cashsum) ||  (abs($cashsum) > 0.01)) {
			//$this->Ln(10);
			//$this->SetFont('Helvetica','UB',12);
			$cashsum = str_replace(".",$this->decpoint,$cashsum);
			$this->Cell(50,10,$this->cashOpTxt[$this->lang],1,0,"C",0);
			$this->Cell(100,10,$cashsum,1,1,"C",0);
		}
		
		$this->curtable = "empty";
		
	}
	
	private function osDateToGerman($dateStr) {
		$d = explode("-",$dateStr);
		return $d[2] . "." . $d[1] . "." . $d[0];
	}
	
	private function osWriteSummarySum($pdo,$itemtxt,$bSum,$nSum,$size,$closingid) {
		
		if ($closingid >= 0) {
			$sql = "SELECT billsum FROM %closing% WHERE id=?";
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute(array($closingid));
			$row = $stmt->fetchObject();
			$billsum = $row->billsum;
			if (abs($billsum - $bSum) > 1.0) {
				$this->SetFont('Helvetica','B',16);
				$this->Cell($this->maxTableLength,10,"$billsum - $bSum : DB inkonsistent",0,0,"R",0);
				return;
			}
			if (!Closing::checkForClosingConsistency($pdo, $closingid)) {
				$this->SetFont('Helvetica','B',16);
				$this->Cell($this->maxTableLength,10,"DB inkonsistent - Abbruch!",0,0,"C",0);
				return;
			}
		}
		
		$this->SetFont('Helvetica','BI',$size);
		$this->Cell($this->flengths[0],10,$itemtxt . ":",0,0,"R",0);
		$bSumT = str_replace(".",$this->decpoint,number_format($bSum, 2, $this->decpoint, ''));
		$nSumT = str_replace(".",$this->decpoint,number_format($nSum, 2, $this->decpoint, ''));
		$tSum = number_format($bSum-$nSum, 2, $this->decpoint, '');
		$tSum = str_replace(".",$this->decpoint,$tSum);
		
		$this->SetFont('Helvetica','I',$size);
		$this->Cell($this->flengths[1],10,$nSumT,0,0,"R",0);
		$this->Cell($this->flengths[2],10,$tSum,0,0,"R",0);
		$this->Cell($this->flengths[3],10,$bSumT,0,1,"R",0);
		
		$this->Cell($this->maxTableLength,1,"","B",1,"C",0);
	}
	
	private function addRestoreStat($pdo, $lang) {
		
		$this->curtable = "";
		
		$sql = "SELECT DATE(date) as day FROM %hist% WHERE action=? ORDER BY date";
		$result = CommonUtils::fetchSqlAll($pdo, $sql, array(10));
		if (count($result) == 0) {
			return;
		}
		
		
		$this->Ln();
		$this->Ln();
		
		$t = array(
				"Restore" => array("Wiederherstellungen der gesamten Datenbank an folgenden Tagen",
						"Restore of complete data base at these days",
						"Restore de la base de datos en estos dias")
		);
		
		$this->SetFont('Helvetica','BI',15);
		$this->SetFillColor(200,200,200);
		$this->Cell($this->maxTableLength,15,$t["Restore"][$lang],1,1,"C",0);

		$this->SetFont('Helvetica','',10);
		$this->SetFillColor(200,200,200);
		
		$allDates = array();
		foreach($result as $aDate) {
			$allDays[] = $this->osDateToGerman($aDate["day"]);
		}
		$allDays = join(", ",$allDays);
	
		$this->MultiCell($this->maxTableLength,7,$allDays,1,"L",1);
	}
	
	
	private function setSummaryTableHeader() {
		$this->SetFillColor(200,200,200);
		$this->SetFont('Helvetica','B',16);
	
		for ($i=0;$i<count($this->tabheader);$i++) {
			$aVal = $this->tabheader[$i];
			//$this->Cell($this->flengths[$i],10,$aVal,1,0,"C",1);
			$this->Cell($this->flengths[$i],10,$aVal,"B",0,"R",0);
		}
		$this->Ln();
	}
	
	private function setTaxesTableHeader() {
		$this->SetFont('Helvetica','B',10);
		$this->SetFillColor(200,200,200);
		$this->Cell(50,10, utf8_decode($this->taxTxt[$this->lang]),1,0,"C",1);
		$this->Cell(50,10,"Netto (" . $this->currency . ")",1,0,"C",1);
		$this->Cell(50,10,"Brutto (" . $this->currency . ")",1,1,"C",1);
	}
	
	private function insertMetaTags($title,$subject) {
		$this->SetAuthor('OrderSprinter');
		$this->SetCreator('OrderSprinter www.ordersprinter.de');
		$this->SetDisplayMode('fullpage');
		$this->SetKeywords( 'OrderSprinter, PDF-Export der Umsatzdaten, www.ordersprinter.de' );
		$this->SetSubject(utf8_decode($subject)); 
		$this->SetTitle(utf8_decode($title)); 
	}
	
	
	public function exportPdfReport($lang,$startMonth,$startYear,$endMonth,$endYear,$closidstart = null,$closidend = null) {
		$pdo = DbUtils::openDbAndReturnPdoStatic();
		$this->decpoint = self::getConfigItem($pdo,"decpoint");
		$this->currency = self::getConfigItem($pdo,"currency");
		$this->version = self::getConfigItem($pdo,"version");
		
		$this->lang = $lang;
		
		$this->insertMetaTags("Umsatzbericht","PDF-Datenexport der Umsätze");
		
 		$this->AddPage();
 		$this->SetFont('Helvetica','B',16);
		
		$this->osInsertHeader($pdo,$lang,0,0,0,0);
		$this->osInsertSales($pdo, $lang, $startMonth, $startYear, $endMonth, $endYear,$closidstart,$closidend);
		
		$this->Ln(10);
		$this->osInsertProdStat($pdo,$startMonth,$startYear,$endMonth,$endYear,$closidstart,$closidend);
		
		$this->addRestoreStat($pdo,$lang);
 		$this->Output();
	}
	
	public function exportPdfSummary($lang,$startMonth,$startYear,$endMonth,$endYear) {
		$pdo = DbUtils::openDbAndReturnPdoStatic();
		$this->exportPdfSummaryCore($pdo, $lang, $startMonth, $startYear, $endMonth, $endYear, null,null);
	}
	
	public function exportPdfSummaryClosPeriod($lang,$closidstart,$closidend) {
		$pdo = DbUtils::openDbAndReturnPdoStatic();
		$this->exportPdfSummaryCore($pdo, $lang, 0, 0, 0, 0, $closidstart,$closidend);
	}
	
	public function exportCsvSummaryClosPeriod($lang,$closidstart,$closidend) {
		$pdo = DbUtils::openDbAndReturnPdoStatic();
		$this->exportCsvSummaryCore($pdo, $lang, 0, 0, 0, 0, $closidstart,$closidend);
	}
	
	private function exportCsvSummaryCore($pdo, $lang, $startMonth, $startYear, $endMonth, $endYear, $closidstart = null, $closidend = null) {
		header("Expires: Mon, 20 Dec 1998 01:00:00 GMT");
		header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
		header("Cache-Control: no-cache, must-revalidate");
		header("Pragma: no-cache");
		header("Content-Disposition: attachment; filename='Tageserfassungen.csv'");
		header("Content-Type: text/csv; charset=utf8");

		$decpoint = CommonUtils::getConfigValue($pdo, 'decpoint', '.');
		$currency = CommonUtils::getConfigValue($pdo, 'currency', 'Eur');
		$curTxt = "(" . $currency . ")";
		$txt = "Tageserfassungs-ID;Datum;Steuersatz;Typ;Netto $curTxt;Steuer $curTxt;Brutto $curTxt\r\n";

		if (is_null($closidstart) || is_null($closidend)) {
			$txt .= "Erste und/oder letzte ID der Tageserassungen wurden nicht angegeben!";
		} else {
			$sql = "SELECT DISTINCT %closing%.id as id, DATE(closingdate) as datewotime,TIME(closingdate) as thetime FROM %closing% WHERE id >= ? AND id <= ? ";
			$allClosings = CommonUtils::fetchSqlAll($pdo, $sql, array($closidstart, $closidend));

			foreach ($allClosings as $aClosing) {
				$closingid = $aClosing["id"];
				$date = $this->osDateToGerman($aClosing["datewotime"]) . " " . $aClosing["thetime"];

				$sql = "SELECT %queue%.tax,SUM(price) as brutto,ROUND(SUM(price)/(1 + %queue%.tax/100.0),2) as netto FROM %queue%,%bill% WHERE billid=%bill%.id AND %bill%.closingid=? GROUP BY %queue%.tax";
				$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
				$stmt->execute(array($closingid));
				$closingDetails = $stmt->fetchAll(PDO::FETCH_OBJ);

				foreach ($closingDetails as $aClosingDetail) {
					$tax = str_replace(".", $decpoint, $aClosingDetail->tax);
					$brutto = str_replace(".", $decpoint, $aClosingDetail->brutto);
					$netto = str_replace(".", $decpoint, $aClosingDetail->netto);
					$sumtax = number_format($aClosingDetail->brutto - $aClosingDetail->netto, 2, $decpoint, '');
					$sumtax = str_replace(".", $decpoint, $sumtax);

					$txt .= "$closingid;$date;$tax;Umsatz;$netto;$sumtax;$brutto\r\n";
				}

				
				$sql = "SELECT SUM(brutto) as cashsum FROM %bill% WHERE closingid=? AND status=?";
				$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));

				$stmt->execute(array($closingid, 'c'));
				$row = $stmt->fetchObject();
				$cashsum = $row->cashsum;

				if (!is_null($cashsum) || (abs($cashsum) > 0.01)) {
					$cash = str_replace(".", $decpoint, $cashsum);
					$txt .= "$closingid;$date;0" . $decpoint . "00;Kassenein-/auslage;$cash;0" . $decpoint . "0;$cash\r\n";
				}

				
			}
		}

		echo $txt;
	}

	public function exportPdfSummaryCore($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear,$closidstart = null,$closidend = null) {
	
		$this->decpoint = self::getConfigItem($pdo,"decpoint");
		$this->currency = self::getConfigItem($pdo,"currency");
		$this->version = self::getConfigItem($pdo,"version");
		
		$this->lang = $lang;
		
		$this->insertMetaTags("Kassenbuch","PDF-Zusammenfassung der Umsätze");
		
		$this->AddPage();
		$this->SetFont('Helvetica','B',16);
		
		if (!is_null($closidstart)) {
			$headerLine = $this->osClosingsPlural[$lang] . " $closidstart - $closidend ";
			$headerLine .= " (in " . self::getConfigItem($pdo,"currency") . ")";
			$this->osInsertHeader($pdo,$lang,0,0,0,0);
		} else {
			$headerLine = $this->osSummary[$lang] . " " .  self::osGetMonthName($lang, $startMonth) . " $startYear - " .  self::osGetMonthName($lang, $endMonth) . " $endYear";
			$headerLine .= " (in " . self::getConfigItem($pdo,"currency") . ")";
			$this->osInsertHeader($pdo,$lang,$startMonth,$startYear,$endMonth,$endYear);
		}

		$this->Cell($this->maxTableLength ,10,$headerLine,0,1,"C",0);
		
		$this->SetFont('Helvetica','',8);
		$this->Cell($this->maxTableLength ,10, "(" . utf8_decode($this->hintNoEmptyClosings[$lang]) . ")", 0, 1, "C",0);
		$this->Ln(10);
		
		$this->osCalculateColsWidth(array(30,20,20,20));
		$this->tabheader = array($this->osClosingTxt[$lang],"Netto","Steuer","Brutto");
		
		$this->setSummaryTableHeader();
		$this->curtable = "summary";
		$this->daySummary($pdo,$lang, $startMonth, $startYear, $endMonth, $endYear, $closidstart,$closidend);
		
		$this->addRestoreStat($pdo,$lang);
		$this->Output();
	}
}