-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Erstellungszeit: 07. Mrz 2018 um 14:48
-- Server-Version: 10.1.10-MariaDB
-- PHP-Version: 5.6.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Datenbank: `sitpos`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `pdf_a4_rechnung`
--

DROP TABLE IF EXISTS `pdf_a4_rechnung`;
CREATE TABLE `pdf_a4_rechnung` (
  `id` int(9) NOT NULL,
  `name` varchar(64) NOT NULL,
  `a4_template` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `pdf_a4_rechnung`
--

INSERT INTO `pdf_a4_rechnung` (`id`, `name`, `a4_template`) VALUES
(1, 'rechnungs_template_a4', '		\n	function a4pdf_DB($bon_id, $testCall=false, $lieferscheinNr=false, $requestFunktion=false){\n			require_once("config/connect.php");\n			require_once("global_functions.php");\n			require_once(''include/extended_fpdf.php'');\n			\n			global $qrImg_temp_dir, $errorCorrectionLevel, $matrixPointSize, $qrMargin;\n\n			$A4TmplVersion="34";				#  kp 2018 01 02  sITpos Standard A4 Rechnungs Templ\n	\n				$fontConfig=SCHRIFT_AUSDRUCK_PDF_A4;\n\n				if ( strpos(strtolower($fontConfig), ''arial'') !== false  || strpos(strtolower($fontConfig), ''helvetica'') !== false || \n							strpos(strtolower($fontConfig), ''sans serif'') !== false ){ $pdfFont="Arial"; }\n							\n				else if ( strpos(strtolower($fontConfig), ''times'') !== false  || strpos(strtolower($fontConfig), ''roman'') !== false ||\n							(strpos(strtolower($fontConfig), ''serif'') !== false &&  strpos(strtolower($fontConfig), ''sans'') == false)) { $pdfFont="Times"; }\n				else if (strpos(strtolower($fontConfig), ''courier'') !== false ) { $pdfFont="Courier";   }\n				else {$pdfFont="Arial";  }\n				\n				preg_match(''/\\d+px | \\d+pt/'', $fontConfig, $m);\n				preg_match(''/\\d+/'', $m[0], $match);\n				$pdfFontSize=$match[0];	\n				\n				$lineHeight=$pdfFontSize/2.5;\n				\n\n				$bonWidth=preg_replace(''/[^\\d]/'', '''', AUSDRUCK_GESAMTE_BREITE);\n				$printer=mysql_fetch_array(mysql_query("SELECT win_drucker_name, drucker_margin_pdf, zoom_factor  FROM drucker WHERE LOWER(ist_a4) = ''ja'' "));\n				$drucker=$printer[0];\n				\n				$margin=$printer[1];\n				$margins=explode(",", $margin);\n				\n				if(isset($margins[2]))$marginLR=preg_replace(''/\\D/'', '''', $margins[2]); else $marginLR=0; \n				if(isset($margins[1]))$marginBottom=preg_replace(''/\\D/'', '''', $margins[1]); else $marginBottom=0; 			\n				if(isset($margins[0]))$marginTop=preg_replace(''/\\D/'', '''', $margins[0]); else $marginTop=0; 	\n				\n				if($marginLR < 10) $marginLR = 10;\n				\n				if(is_int($printer[2]) || is_float($printer[2])) $zoomF=$printer[2]; \n				else $zoomF=1;\n				\n				$pageWidth=210-($marginLR*2);\n				\n				$pdfPrinterData=$printer;\n				$bonData = mysql_fetch_array(mysql_query("SELECT kunden_id, verkaufer_id, gesamt_summe, retourgeld, bon_erstellt_datum, \n												storno_datum, gastro_tisch_id, qr_string, auftragsschein_nr, zusatztext\n												FROM abgeschlossener_bon \n												LEFT JOIN dep ON abgeschlossener_bon.bon_id = dep.beleg_nr\n												WHERE bon_id=".$bon_id." "));\n				if(!$bonData) $xx=mysql_error();\n				\n						\n			class PDF extends PDF_MemImage{\n\n				function Header() {\n					$marginTop=$this->marginTop;\n					$marginLR=$this->marginLR;\n					$pdfFontSize=$this->pdfFontSize;\n					$pdfFont=$this->pdfFont;\n					$lineHeight=$this->lineHeight;\n					$bonId=$this->bonId;\n					$bonData=$this->bonData;\n                                        \n					\n					$this->SetFont($pdfFont, '''', $pdfFontSize);\n\n					$calcH=0;\n					if(defined(''AUSDRUCK_A4_LOGO_POSITION'') && AUSDRUCK_A4_LOGO_POSITION != "")$logoPos=AUSDRUCK_A4_LOGO_POSITION;\n					else $logoPos=''R, 50'';	\n					\n					$logoPos=explode('','', $logoPos);\n					if(isset($logoPos[0]) && $logoPos[0] != '''')$logoPos[0]=$logoPos[0];\n					else $logoPos[0] = ''R'';\n					if(isset($logoPos[1]) && $logoPos[1] > 20)$imgWidth=$logoPos[1];\n					else $imgWidth = 50;\n					\n					if(strtolower($logoPos[0]) == ''r'') $leftPos=210-($imgWidth+$marginLR);\n					else if(strtolower($logoPos[0]) == ''l'') $leftPos=$marginLR;\n					else if(strtolower($logoPos[0]) == ''c'') $leftPos=(210-$imgWidth)/2;	\n					\n					if(defined(''AUSDRUCK_A4_ADRESS_POSITION'') && AUSDRUCK_A4_ADRESS_POSITION != "")$adressPos=AUSDRUCK_A4_ADRESS_POSITION;\n					else $adressPos=''L, 30, R'';\n					\n					$adressPos=explode('','', $adressPos);\n					if(isset($adressPos[0]) && $adressPos[0] != '''')$adressPos[0]=$adressPos[0];\n					else $adressPos[0]= ''L'';\n					if(isset($adressPos[1]) && $adressPos[1] > 20)$adressCellWidth=$adressPos[1];\n					else $adressCellWidth=100;\n					if(isset($adressPos[2]) && $adressPos[2] != '''')$adressPos[2]=$adressPos[2];\n					else $adressPos[2]= ''L'';\n\n					if ($this->PageNo() == 1){\n						$getImg=mysql_fetch_assoc(mysql_query("SELECT image, name, imgx, imgy FROM images WHERE name=''a4logo''"));\n							if(defined(''AUSDRUCK_LOGO_RECHNUNGSBON_PDF'') && strtolower(AUSDRUCK_LOGO_RECHNUNGSBON_PDF) == ''ja'' && $getImg != false){\n								\n								$zuzX=$this->GetY();\n								if($getImg == true){\n									\n									$fac=$getImg[''imgx'']/$imgWidth;\n									$calcH=$getImg[''imgy'']/$fac;\n									\n									$this->MemImage($getImg[''image''], $leftPos, $marginTop, $imgWidth, 0);   // breite ist definiert höhe auf null heißt wird calkuliert\n								}\n\n								if( strtolower($logoPos[0]) == ''r'' && strtolower($adressPos[0]) == ''r''){\n									$this->SetXY($leftPos, $marginTop+$calcH);\n									$curYpos=$this->GetY()+$lineHeight;\n								\n								}\n\n								else $curYpos=$marginTop;\n							}\n							\n							else {\n								$curYpos=$marginTop;\n								\n							}\n							\n							$ibanTx="";\n							$kopfQl=mysql_query("SELECT name, wert FROM konfiguration WHERE name LIKE ''AUSRUCK_KOPFZEILE_%'' AND wert <> '''' ORDER BY name");\n\n								while($kopfR=mysql_fetch_array($kopfQl)){\n\n									$wert=$kopfR["wert"];\n										if(strpos(strtolower($kopfR["wert"]), ''uid'') !== false || $kopfR["name"] == ''AUSRUCK_KOPFZEILE_IBAN'') continue;\n\n										\n										if(strtolower(trim($adressPos[0])) == ''r'' && strtolower(trim($adressPos[2])) == ''r''){\n											$this->SetXY(210-($adressPos[1]+$marginLR), $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''R'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''c'' && strtolower($logoPos[0]) == ''c''){\n												# wenn logo und adresse in der mitte stehen wird logo oben normal positioniert und die adresse \n												# nach unten geschoben, der align wert wird trotzdem aus der konfig genommen, macvcht aber eh nur center sinn\n												$this->SetXY((210-$adressPos[1])/2, $curYpos+$calcH);\n												$adrAlign=strtoupper(trim($adressPos[2]));\n												$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, $adrAlign);\n										}\n										else if(strtolower(trim($adressPos[0])) == ''r'' && strtolower(trim($adressPos[2])) == ''c''){\n											$this->SetXY(210-($adressPos[1]+$marginLR), $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''C'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''r'' && strtolower(trim($adressPos[2])) == ''l''){\n											$this->SetXY(210-($adressPos[1]+$marginLR), $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''L'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''c'' && strtolower(trim($adressPos[2])) == ''r''){\n											$this->SetXY((210-$adressPos[1])/2, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''R'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''c'' && strtolower(trim($adressPos[2])) == ''c''){\n											$this->SetXY((210-$adressPos[1])/2, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''C'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''c'' && strtolower(trim($adressPos[2])) == ''l''){\n											$this->SetXY((210-$adressPos[1])/2, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''L'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''l'' && strtolower(trim($adressPos[2])) == ''r''){\n											$this->SetXY($marginLR, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, strip_tags($kopfR[''wert'']), 0, 1, ''R'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''l'' && strtolower(trim($adressPos[2])) == ''c''){\n											$this->SetXY($marginLR, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''C'');\n										}\n										else if(strtolower(trim($adressPos[0])) == ''l'' && strtolower(trim($adressPos[2])) == ''l''){\n											$this->SetXY($marginLR, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''L'');\n										}\n										\n										else{ \n											$this->SetXY($marginLR, $curYpos);\n											$this->Cell($adressCellWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kopfR[''wert''])), 0, 1, ''L'');\n										}	\n\n										$curYpos+=$lineHeight; #  die Zellen sind absolut positioniert, deswegen muss hier die Y position immer um eine lineHeight erhöht werden\n									\n									\n								}\n	\n						$kundenId=$bonData["kunden_id"];	\n\n						$this->SetY(65);\n						\n						if($kundenId > 0 ){\n							$custData="";\n								$kundenDetails=mysql_fetch_assoc(mysql_query("SELECT * FROM kunden WHERE id=".$kundenId." "));\n								\n								if(strlen($kundenDetails[''anrede'']) > 0) $anrede=$kundenDetails[''anrede'']." ";\n								else $anrede="";\n								if(strlen($kundenDetails[''titel'']) > 0) $titel=$kundenDetails[''titel'']." ";\n								else $titel="";\n													\n								if(strlen($kundenDetails[''vorname'']) > 0 && strlen($kundenDetails[''nachname'']) > 0) $kdName=$kundenDetails[''vorname'']." ".$kundenDetails[''nachname''];\n								else if(strlen($kundenDetails[''nachname'']) > 0 && \n											(strlen($kundenDetails[''vorname'']) == 0 || $kundenDetails[''vorname''] == false ))$kdName=$kundenDetails[''nachname''];\n								else if(strlen($kundenDetails[''vorname'']) > 0 && \n											(strlen($kundenDetails[''nachname'']) == 0 || $kundenDetails[''nachname''] == false ))$kdName=$kundenDetails[''vorname''];\n								else $kdName="";\n								\n								$kdName=$anrede.$titel.$kdName;\n								\n								if(strlen($kundenDetails[''Firma'']) > 0)$this->Cell(0, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kundenDetails[''Firma''])), 0, 1, ''L'');\n								if(strlen($kdName) > 0 )$this->Cell(0, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kdName)), 0, 1, ''L'');\n								if(strlen($kundenDetails[''strasse1'']) > 0)$this->Cell(0, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kundenDetails[''strasse1''])), 0, 1, ''L''); \n								if(strlen($kundenDetails[''strasse2'']) > 0)$this->Cell(0, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kundenDetails[''strasse2''])), 0, 1, ''L''); \n								if($kundenDetails[''plz''] != '''' || $kundenDetails[''Ort''] != '''')\n									$this->Cell(0, $lineHeight, iconv(''UTF-8'', ''windows-1252'', strip_tags($kundenDetails[''plz'']))." ".iconv(''UTF-8'', ''windows-1252'', strip_tags($kundenDetails[''Ort''])), 0, 1, ''L''); \n																	\n								$this->Cell(0, $lineHeight, "", ''B'', 1, ''C''); 	#  trennlinie unterhalb von Kunden Druck, falls Kunde ausgegeben wird										\n						}\n						\n						else {\n							$zahlung=mysql_query("SELECT Name FROM abgeschlossener_bon_zahlung WHERE bon_id=".$bonId." ");\n							while($zahlR=mysql_fetch_array($zahlung)){\n								$zahlForm=$zahlR[''Name''];\n								\n								\n							}\n							\n							if(mysql_num_rows ($zahlung) > 1 ) $zahlForm="Gemischte Zahlungsform";\n								\n							$this->Cell(0, $lineHeight, $zahlForm." Verkauf", 0, 1, ''L'');\n							$this->Cell(0, $lineHeight, "", 0, 1, ''L'');\n							$this->Cell(0, $lineHeight, "", 0, 1, ''L'');\n							$this->Cell(0, $lineHeight, "", 0, 1, ''L'');\n							\n							$this->Cell(0, $lineHeight, "", ''B'', 1, ''C'');\n						\n						}\n					\n					}\n					\n					else {					# bei mehrseitigen ausdruck wird ab der zweiten seite ein reduzierter header gedruckt\n						if(defined(''AUSDRUCK_LOGO_RECHNUNGSBON_PDF'') && strtolower(AUSDRUCK_LOGO_RECHNUNGSBON_PDF) == ''ja''){\n								$getImg=mysql_fetch_assoc(mysql_query("SELECT image, name, imgx, imgy FROM images WHERE name=''a4logo''"));\n\n								if($getImg == true){\n									\n									$fac=$getImg[''imgx'']/$imgWidth;\n									$calcH=$getImg[''imgy'']/$fac;\n									$this->MemImage($getImg[''image''], $leftPos, $marginTop, $imgWidth, 0);   // breite ist definiert höe auf null heißt wird calkuliert\n								}\n						}\n						\n						$this->Ln($calcH+$marginTop+10);\n						\n						if($this->checkPageBreak == true && $this->produktWhileEnd == false){\n							$pageWidth=210-($this->marginLR*2);\n							$colA_width=($pageWidth*.12)*$this->zoomF;\n							$colB_width=($pageWidth*.38)*$this->zoomF;\n							$colC_width=($pageWidth*.25)*$this->zoomF;\n							$colD_width=($pageWidth*.25)*$this->zoomF;\n							\n							$this->SetFillColor(90);\n							$this->SetTextColor(255); \n							\n							$this->Cell($colA_width, $lineHeight*1.3, "Stk.", 0, 0, ''R'', true);\n							$this->Cell($colB_width, $lineHeight*1.3, "Bezeichnung", 0, 0, ''L'', true);\n							$this->Cell($colC_width, $lineHeight*1.3, "Einzel", 0, 0, ''R'', true);\n							$this->Cell($colD_width, $lineHeight*1.3, "Ges. Brutto", 0, 1, ''R'', true);\n							$this->Cell(0, $lineHeight/3, "", 0, 1, ''C''); \n							$this->SetFillColor(255);\n							$this->SetTextColor(0);\n							\n						}\n					\n					}\n					\n					\n				}\n				\n				function docData(){\n					$marginLR=$this->marginLR;\n					$pdfFontSize=$this->pdfFontSize;\n					$pdfFont=$this->pdfFont;\n					$lineHeight=$this->lineHeight;\n					$bonId=$this->bonId;\n					$bonData=$this->bonData;\n                    $lieferscheinNr=$this->lieferscheinNr;\n					$requestFunktion=$this->requestFunktion;\n					\n					$invoiceSumRaw=$bonData["gesamt_summe"];\n					$gesamtsumme_brutto=number_format($bonData["gesamt_summe"],2,",",".");\n					$datetime=$bonData["bon_erstellt_datum"];\n					$stornoDatetime=$bonData["storno_datum"];\n					$retourgeld=$bonData["retourgeld"];\n				\n					$verkauferId=$bonData["verkaufer_id"];\n					$gastroTischId=$bonData["gastro_tisch_id"];\n					$auftragsscheinNr=$bonData["auftragsschein_nr"];\n				\n					$this->SetFont($pdfFont, '''', $pdfFontSize);\n					$pageWidth=210-($marginLR*2);\n					\n					$dDate = strtotime( $datetime );\n					$datetime = "Datum: ".date( ''j.n.Y H:i:s'', $dDate );\n					\n\n						$this->Cell($pageWidth/3, $lineHeight*1.2, BELEG_BENENNUNG." ".$bonId, 0, 0, ''L'');	\n						$this->Cell($pageWidth/3, $lineHeight*1.2, "Kassen Id: ".KASSA_ID, 0, 0, ''L'');	\n						$this->Cell($pageWidth/3, $lineHeight*1.2, $datetime, 0, 1, ''R'');									\n					\n					\n					if($auftragsscheinNr > 0){\n						$this->Cell($pageWidth/3, $lineHeight*1.2, AUFTRAGSSCHEIN_BENENNUNG.$auftragsscheinNr, 0, 1, ''L'');\n					\n					}			\n					\n					\n					if(defined("VERKAEUFER_AUF_BON_DRUCKEN") && strtolower(VERKAEUFER_AUF_BON_DRUCKEN)==''ja''){\n						$userQl=mysql_fetch_array(mysql_query("SELECT name FROM benutzer WHERE id = ".$verkauferId." "));\n						if($userQl==true) $this->Cell($pageWidth/3, $lineHeight*1.2, AUSDRUCK_VERKAUFER_AUF_BON_TEXT." ".iconv(''UTF-8'', ''windows-1252'', $userQl[0]), 0, 0, ''L'');\n					}\n					else $this->Cell($pageWidth/3, $lineHeight*1.2, "", 0, 0, ''L'');\n					\n					if( (defined(''GASTRO_VERSION'') && strtolower(GASTRO_VERSION) == ''ja'') \n							&& (defined(''GASTRO_TISCHNAME_AUF_BON_DRUCKEN'') && strtolower(GASTRO_TISCHNAME_AUF_BON_DRUCKEN) == ''ja'')){\n							\n						$tName=mysql_fetch_array(mysql_query("SELECT raum.name AS raumN, tischName FROM tische \n													LEFT JOIN raum ON tische.raum = raum.id\n													WHERE tische.id = ''".$gastroTischId."'' LIMIT 1 "));\n						$raumName=$tName[''raumN''];\n						\n						if (preg_match("/(zimmer|apartment|ferienwohnung|suite|studio)/i", $raumName)) {\n							if (preg_match("/(zimmer|apartment|ferienwohnung|suite|studio)/i", $tName[''tischName''])){\n								$this->Cell($pageWidth/3, $lineHeight*1.2, $tName[''tischName''], 0, 0, ''L'');\n							}\n							\n							else $this->Cell($pageWidth/3, $lineHeight*1.2, "Zimmer: ".$tName[''tischName''], 0, 0, ''L'');\n						}\n						\n						else $this->Cell($pageWidth/3, $lineHeight*1.2, "Tisch: ".$tName[''tischName''], 0, 0, ''L'');\n					}\n					\n					else $this->Cell($pageWidth/3, $lineHeight*1.2, "", 0, 0, ''L'');\n		\n					$this->Cell($pageWidth/3, $lineHeight*1.2, iconv(''UTF-8'', ''windows-1252'', "Beträge in EUR"), 0, 1, ''R'');\n					\n	\n					if(strlen($bonData["zusatztext"]) > 0){\n						$this->Cell(0, $lineHeight*.5, "", "T", 1, ''C'');\n						$this->MultiCell( $pageWidth, $lineHeight, iconv(''UTF-8'', ''windows-1252'', $bonData[''zusatztext'']), 0, ''L'', 0);					\n						$this->Cell(0, $lineHeight*.2, "", "T", 1, ''C'');\n					}\n					else $this->Cell(0, $lineHeight*.5, "", "T", 1, ''C'');\n				\n					$colA_width=($pageWidth*.12)*$this->zoomF;\n					$colB_width=($pageWidth*.38)*$this->zoomF;\n					$colC_width=($pageWidth*.25)*$this->zoomF;\n					$colD_width=($pageWidth*.25)*$this->zoomF;\n					\n					$this->SetFillColor(90);\n					$this->SetTextColor(255); \n					\n					$this->Cell($colA_width, $lineHeight*1.3, "Stk.", 0, 0, ''R'', true);\n					$this->Cell($colB_width, $lineHeight*1.3, "Bezeichnung", 0, 0, ''L'', true);\n					$this->Cell($colC_width, $lineHeight*1.3, "Einzel", 0, 0, ''R'', true);\n					$this->Cell($colD_width, $lineHeight*1.3, "Ges. Brutto", 0, 1, ''R'', true);\n					$this->Cell(0, $lineHeight/3, "", 0, 1, ''C''); \n					$this->SetFillColor(255);\n					$this->SetTextColor(0);\n					\n					\n					$sql_produkte = mysql_query("SELECT abgeschlossener_bon_produkte.ID, produkt_id, tax_rate, REFERENCE, CODE, NAME, PRICEBUY, PRICESELL, \n								CATEGORY, TAXCAT, tax_rate, DISPLAY, VARPRICE, PRINTNAME, NEGATIVEPRICE, PRODUKTORDER, SICHTBAR, stueck, \n								has_childs, parent_id, isparent_id, childPrint, lsSource, lieferschein_id, lieferschein_erstellt_datum \n								FROM abgeschlossener_bon_produkte\n								LEFT JOIN lieferschein_referenz ON lsSource = lieferschein_referenz.id\n								WHERE bon_id=''".$bonId."'' ORDER BY lieferschein_referenz.id ASC, abgeschlossener_bon_produkte.ID ASC	");\n\n					if (!$sql_produkte)	{\n						echo "<script>console.log(''Datenbank Fehler: ''".mysql_error().")</script>";		\n						exit(1);\n					}\n					\n					$countRows=mysql_num_rows($sql_produkte)*1;\n					$checkRows=0;\n					$this->checkPageBreak=0;\n					$lsGroup=false;		# für den gruppenwechsel, sammelrechnung aus lieferscheine	\n					while ($row_produkt=mysql_fetch_assoc($sql_produkte)) {\n						$einzel_preis=$row_produkt["PRICESELL"];\n						$einzel_preis=$einzel_preis*(1+$row_produkt["tax_rate"]);\n						$summe=$einzel_preis*$row_produkt["stueck"];\n						$einzel_preis=number_format($einzel_preis,2,",",".");\n						\n							# COMBI ARTIKEL !!!\n						if($row_produkt[''has_childs''] == 1 && $row_produkt[''isparent_id''] > 0){\n							$findChilds=mysql_fetch_array(mysql_query("SELECT SUM(PRICESELL* (1 + tax_rate)) AS combiEinzelPr FROM abgeschlossener_bon_produkte \n																	WHERE parent_id = ".$row_produkt[''ID'']." "));\n							\n							$einzel_preis=number_format($findChilds[''combiEinzelPr''],2,",",".");\n							$summe=$findChilds[''combiEinzelPr'']*$row_produkt["stueck"];\n						}\n						else if($row_produkt[''parent_id''] > 0 && $row_produkt[''childPrint''] == 0) continue;\n						\n						\n						$stueck=$row_produkt["stueck"];\n						// Checke ob Stueck nachkomma hat oder nicht wenn ja, dann 2 Nachkommastellen ansonsten keine\n						if ( (int) $stueck == $stueck) {\n							$stueck_anzeige=number_format($stueck,0,"",".");\n						}	\n						else $stueck_anzeige=number_format($stueck,2,",",".");\n							\n						$summe=number_format($summe,2,",",".");\n						$printname=$row_produkt["PRINTNAME"];\n						\n						if($row_produkt[''parent_id''] > 0 && $row_produkt[''childPrint''] == 1){\n							\n							$ortstaxe=number_format($stueck*$row_produkt["PRICESELL"],2, ",", ".");\n							$einzel_preis='''';\n							$stueck_anzeige='''';\n							$summe='''';\n							\n							$printname=preg_replace(''/€\\s*0[,\\.]00/'', ''€ ''.$ortstaxe, $printname);\n						}\n									#\n						if(isset($row_produkt[''lsSource'']) && $row_produkt[''lsSource''] > 0 && ($lsGroup != $row_produkt[''lieferschein_id''])){\n							$headerTxt="Liefersch: ";\n							if(defined(''LIEFERSCH_SAMMELRECHNUNG_GRUPPENHEADER'')) $headerTxt=LIEFERSCH_SAMMELRECHNUNG_GRUPPENHEADER;\n							\n							$dDate = strtotime($row_produkt[''lieferschein_erstellt_datum'']);\n							$dateTime = date( ''j.n.Y H:i'', $dDate );\n							$lsHeader=iconv(''UTF-8'', ''windows-1252'', LIEFERSCH_SAMMELRECHNUNG_GRUPPENHEADER.$row_produkt[''lieferschein_id''].", ".$dateTime);\n							$headW=$this->getstringwidth($lsHeader);\n							if($lsGroup > 0)$this->Cell(0, $lineHeight/2, '''', 0, 1, ''C'');							\n							$this->Cell(0, $lineHeight, $lsHeader, 0, 1, ''L'');\n							$this->Cell($headW, 1, '''', ''T'', 1, ''L'');\n						}\n\n						$this->Cell($colA_width, $lineHeight, $stueck_anzeige, 0, 0, ''R'');\n						\n							# Einzelrabatt Zeile bilden, % -> gerade Zahl od Kommazahl\n						$rabattLine=false;\n						if(preg_match(''/\\d{1,},\\d{1,}\\%\\sRabatt/'', $printname, $matchRes)){\n								# Einzelrabatt Komma Zahl, steht mit "," im Produkt Table\n							$rabattLine=true;\n							$tmpPrintN=preg_split("/\\d{1,},\\d{1,}\\%\\sRabatt/", $printname);\n							if(isset($tmpPrintN[1]) && strlen($tmpPrintN[1]) > 0)$tmpPrintNameEnd="\\n".trim($tmpPrintN[1]);\n							else $tmpPrintNameEnd="";\n							$printname=$tmpPrintN[0]."\\n".trim($matchRes[0]." inkludiert").$tmpPrintNameEnd;	\n						}\n						else if(preg_match(''/\\d{1,}%\\sRabatt/'', $printname, $matchRes)){\n								# Einzelrabatt gerade Zahl\n							$rabattLine=true;\n							$tmpPrintN=preg_split("/\\d{1,}%\\sRabatt/", $printname);\n							if(isset($tmpPrintN[1]) && strlen($tmpPrintN[1]) > 0)$tmpPrintNameEnd="\\n".trim($tmpPrintN[1]);\n							else $tmpPrintNameEnd="";\n							$printname=$tmpPrintN[0]."\\n".trim($matchRes[0]." inkludiert").$tmpPrintNameEnd;	\n						}\n											\n		\n						if($this->getstringwidth(iconv(''UTF-8'', ''windows-1252'', $printname)) > $colB_width || $rabattLine == true  || strpos($printname, "\\n")){\n							$posY=$this->GetY();\n							$posX=($colA_width + $colB_width + $marginLR );\n							$this->MultiCell( $colB_width, $lineHeight, iconv(''UTF-8'', ''windows-1252'', $printname) , 0, ''L'', 0);  # border-0; align L, fill-0,\n							$posY_2=$this->GetY();\n							$this->SetXY($posX, $posY);\n							\n							$this->Cell($colC_width, $lineHeight, $einzel_preis, 0, 0, ''R'');\n							$this->Cell($colD_width, $lineHeight, $summe, 0, 1, ''R'');\n							$this->SetY($posY_2);\n						}\n						else{	\n							$this->Cell($colB_width, $lineHeight, iconv(''UTF-8'', ''windows-1252'', $printname), 0, 0, ''L'');\n							$this->Cell($colC_width, $lineHeight, $einzel_preis, 0, 0, ''R'');\n							$this->Cell($colD_width, $lineHeight, $summe, 0, 1, ''R'');\n						}\n						\n						$checkRows++;\n						if($checkRows < $countRows) $this->checkPageBreak = true;\n						else $this->checkPageBreak = false;\n						\n						$lsGroup=$row_produkt[''lieferschein_id''];\n					}\n					\n					$this->produktWhileEnd=true;\n\n					\n				$this->Cell(0, $lineHeight/2, "", 0, 1, ''C'', 0);\n				$this->SetLineWidth(.5);	\n				$this->SetDrawColor(90);\n				$this->SetFont($pdfFont,''B'', $pdfFontSize); \n				$this->Cell($colA_width + $colB_width, $lineHeight*1.2, "Rechnungssumme: ", "T", 0, ''L'',0);\n				\n				$this->Cell($colC_width + $colD_width, $lineHeight*1.2, $gesamtsumme_brutto, "T", 1, ''R'',0);\n				$this->SetLineWidth(.2);	\n				$this->SetDrawColor(250, 250, 250);\n				\n				$this->SetFont($pdfFont,'''', $pdfFontSize); \n				$this->Cell(0, $lineHeight*1.5, "", 0, 1, ''C'');\n				\n				$StColBlind_width=($pageWidth*.15)*$this->zoomF;\n				$StColA_width=($pageWidth*.20)*$this->zoomF;\n				$StColB_width=($pageWidth*.20)*$this->zoomF;\n				$StColC_width=($pageWidth*.20)*$this->zoomF;\n				$StColD_width=($pageWidth*.25)*$this->zoomF;\n				\n				\n			$sql_tax = mysql_query("SELECT tax_rate, gesamt_betrag from abgeschlossener_bon_steuern WHERE bon_id=''".$bonId."''");\n			if (! $sql_produkte){\n				echo "<script>console.log(''Datenbank Fehler: ''".mysql_error().")</script>";		\n				exit(1);\n			}\n				$qtyTaxRates=mysql_num_rows($sql_tax);\n				$taxBlockHeight=($lineHeight*$qtyTaxRates)+$lineHeight;\n				$currY=$this->GetY();\n				$restOnPage=$currY+$taxBlockHeight+AUSDRUCK_A4_FOOTER_HOEHE;\n				if($restOnPage > 275 ) $this->AddPage('''','''',false);\n	\n				$this->Cell($StColBlind_width, $lineHeight, "", 0, 0, ''R'');\n				$this->Cell($StColA_width, $lineHeight, "Steuersatz", 0, 0, ''R'');\n				$this->Cell($StColB_width, $lineHeight, "Netto", 0, 0, ''R'');\n				$this->Cell($StColC_width, $lineHeight, "Steuer", 0, 0, ''R'');\n				$this->Cell($StColD_width, $lineHeight, "Brutto", 0, 1, ''R'');\n			\n			\n			\n			while ($rowTax=mysql_fetch_assoc($sql_tax)) 	{\n				$steuersatz=$rowTax["tax_rate"]*100;\n				$netto=$rowTax["gesamt_betrag"];\n				$steuer=$netto*$rowTax["tax_rate"];\n				$brutto=$netto+$steuer;\n				$brutto=number_format($brutto,2,",",".");\n				$netto=number_format($netto,2,",",".");\n				$steuer=number_format($steuer,2,",",".");\n				\n				$this->Cell($StColBlind_width, $lineHeight, "", 0, 0, ''R'');\n				$this->Cell( $StColA_width, $lineHeight, $steuersatz, 0, 0, ''R'');\n				$this->Cell( $StColB_width, $lineHeight, $netto , 0, 0, ''R'');\n				$this->Cell( $StColC_width, $lineHeight, $steuer, 0, 0, ''R'');\n				$this->Cell( $StColD_width, $lineHeight, $brutto, 0, 1, ''R'');\n				\n			}\n			\n				# das wird zahlungsform Print !!!!\n\n				$this->Cell( $StColD_width, $lineHeight, "", 0, 1, ''R'');\n				$zahlungQl = mysql_query("SELECT Name, zahlbetrag_tatsaechlich FROM abgeschlossener_bon_zahlung WHERE bon_id=''".$bonId."''");\n				\n				$summe_zahlbetrag=0;\n				while ($zahlRow=mysql_fetch_assoc($zahlungQl)){				\n					$zahlbetrag=$zahlRow["zahlbetrag_tatsaechlich"];\n					$summe_zahlbetrag+=$zahlbetrag;\n					$name=$zahlRow["Name"];\n					$zahlbetrag=number_format($zahlbetrag,2,",",".");\n					\n					$this->Cell( $StColBlind_width+$StColA_width+$StColB_width, $lineHeight, iconv(''UTF-8'', ''windows-1252'', $name), 0, 0, ''R'');\n					$this->Cell( +$StColC_width+$StColD_width, $lineHeight, $zahlbetrag, 0, 1, ''R'');\n				}\n			\n			$qr_string=$bonData["qr_string"];\n			$qrEndePos=false;	\n				if(strlen($qr_string) > 55){\n					\n					$qrX=explode("_", $qr_string);\n					$lastIndex=count($qrX)-1;\n					$sigData=base64_decode($qrX[$lastIndex]);\n					if(strtolower($sigData) == ''sicherheitseinrichtung_ausgefallen'' ){\n						$this->Cell($pageWidth, $lineHeight*2, "", 0, 1, "C");\n						$sigData=str_replace("_", " ", $sigData);\n						$this->SetFont($pdfFont, ''B'', $pdfFontSize);\n						$this->Cell($pageWidth, $lineHeight, $sigData, 0, 1, "C");			\n					}			\n					else{\n						$qrImgMarginTop=10;\n						$qrImgMarginBottom=5;\n						$qrImgSize=44;\n						if(defined(''QR_CODE_GROESSE'') && QR_CODE_GROESSE >= 15 ){\n							$qrImgSize=QR_CODE_GROESSE;	\n							if($qrImgSize >= 15 && $qrImgSize < 20){\n								$qrImgMarginTop=3;\n								$qrImgMarginBottom=2;\n							}\n							else if($qrImgSize >= 20 && $qrImgSize < 35){\n								$qrImgMarginTop=6;\n								$qrImgMarginBottom=3;\n							}\n						}\n							\n						$qrImgPosL=(($pageWidth-$qrImgSize)/2)+$marginLR;\n						\n						$imgY=$this->GetY()+$qrImgMarginTop;\n						\n						$PageHeightNet=297-$this->marginTop-AUSDRUCK_A4_FOOTER_HOEHE;\n						$refPos=$PageHeightNet-$imgY-$qrImgMarginBottom-$qrImgSize;\n						\n						if($refPos < 0 ){\n							$this->AddPage('''','''',false); \n							$this->Cell(0, 1, '''', 0, 1, ''C'');\n							$imgY=$this->GetY()+$qrImgMarginTop;\n						}\n					\n						$qrFilename=$this->qrImg_temp_dir.$bonId.".png";\n		\n						\n						QRcode::png($qr_string, $qrFilename, $this->errorCorrectionLevel, $this->matrixPointSize, $this->qrMargin); \n						$this->Image($qrFilename, $qrImgPosL, $imgY, $qrImgSize, ''PNG'');\n						unlink($qrFilename);\n                        $qrEndePos=$imgY+$qrImgSize;\n					}\n\n			}\n			\n			\n			if(isset($requestFunktion) && ($requestFunktion == ''bonJournalPrint'' || $requestFunktion == ''bonReprint''   )){\n				$bonjournalP="";\n				if($requestFunktion == ''bonJournalPrint'')$bonjournalP="Bonjournal ";\n		\n				if($qrEndePos !== false)$this->SetY($qrEndePos);\n				$this->Cell(0, $this->lineHeight, $bonjournalP."Belegduplikat", 0, 1, ''C'');\n			}\n			\n													\n	}  # ende function docdata \n				\n				function Footer(){\n						$this->SetY(-AUSDRUCK_A4_FOOTER_HOEHE);\n						$this->SetFont($this->pdfFont, '''', $this->pdfFontSize*.75);\n						$footerTxt=AUSDRUCK_A4_FOOTER_TXT;\n						$this->SetDrawColor(0);\n						$this->Cell(0, 2, "", "T", 1, ''C'');\n						if (strpos($footerTxt, '';'') !== false) {\n							$footerLines=explode(";", $footerTxt);\n							for($i=0; $i < sizeof($footerLines); $i++){\n								$this->Cell(0, $this->lineHeight, iconv(''UTF-8'', ''windows-1252'', $footerLines[$i]), 0, 1, ''C'');\n							}\n						}\n						else {\n								$this->Cell(0, $this->lineHeight, iconv(''UTF-8'', ''windows-1252'', $footerTxt), 0, 1, ''C'');\n						}\n						\n						$this->Cell(0, $this->lineHeight, ''Seite ''.$this->PageNo(). ''/{nb}'', 0, 1, ''R'');								 \n				}\n			}\n			\n			\n			$pdf=new PDF(''P'',''mm'', ''A4'');\n			$pdf->SetMargins($marginLR, $marginTop);\n			\n			\n\n			$pdf->header = 0;\n			$pdf->footer = 0;\n		#	$pdf->addPage('''','''',false); \n			\n			$pdf->pdfPrinterData=$pdfPrinterData;\n			$pdf->marginTop=$marginTop;\n			$pdf->marginLR=$marginLR;\n			$pdf->pdfFont=$pdfFont;\n			$pdf->pdfFontSize=$pdfFontSize;\n			$pdf->lineHeight=$lineHeight;\n			$pdf->bonId=$bon_id;\n			$pdf->bonData=$bonData;\n			$pdf->zoomF=$zoomF;\n			$pdf->errorCorrectionLevel=$errorCorrectionLevel;\n			$pdf->matrixPointSize=$matrixPointSize;\n			$pdf->qrMargin=$qrMargin;\n			$pdf->qrImg_temp_dir=$qrImg_temp_dir;\n			$pdf->produktWhileEnd=false;\n                        $pdf->lieferscheinNr=$lieferscheinNr;\n			$pdf->requestFunktion=$requestFunktion;\n\n			$pdf->checkPageBreak=false;\n			$pdf->SetAutoPageBreak(true, AUSDRUCK_A4_FOOTER_HOEHE+15);\n			\n			$pdf->AddPage('''','''',false); \n			$pdf->docData();\n			$pdf->AliasNbPages(''{nb}'');\n\n			\n			if(defined(''AUSDRUCK_A4_DOWNLOAD'') && strtolower(AUSDRUCK_A4_DOWNLOAD) == ''ja''){\n				$outputTarget="docs/A4_bon_V".$A4TmplVersion."_".$bon_id."_".date(''d_m_H_i'').".pdf";\n				$writeFile=$pdf->Output($outputTarget, "F");\n				if($writeFile == ""){				\n					if($testCall == ''testCall'') echo json_encode(array("success", "browserPrint", $outputTarget ));\n					else return array("success", "browserPrint", $outputTarget );\n				}\n				else{\n					if($testCall == ''testCall'') echo json_encode(array("error", "browserPrint", $outputTarget ));\n					else  return array("error", "browserPrint", $outputTarget );\n				}\n\n			}\n			else{\n				$outputTarget=__DIR__ ."\\docs\\A4_bon_V".$A4TmplVersion."_".$bon_id."_".date(''d_m_H_i'').".pdf";\n				$command="C:\\sitpos\\pdfxchange\\App\\PDFV_Portable\\PDFXCview.exe /print:printer=\\"$drucker\\" \\"$outputTarget\\" ";						\n				$writeFile=$pdf->Output($outputTarget, "F");\n				\n				\n				exec($command, $output, $error_code);\n				if($writeFile == "" && $error_code == 0){\n					if($testCall == ''testCall'') echo json_encode(array("success", "printerPrint", $outputTarget ));\n					else return array("success", "printerPrint", $outputTarget );\n					\n				}\n                 else{\n					if($testCall == ''testCall'') echo json_encode(array("$error_code", "printerPrint", $outputTarget ));\n					else return array("$error_code", "printerPrint", $outputTarget );\n				}\n				 \n				\n				 \n			}\n		\n	}   # ende function a4pdf_DB($bon_id)\n');

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `pdf_a4_rechnung`
--
ALTER TABLE `pdf_a4_rechnung`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `pdf_a4_rechnung`
--
ALTER TABLE `pdf_a4_rechnung`
  MODIFY `id` int(9) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
