<?xml version="1.0" encoding="UTF-8"?>
<!--
        Copyright:  01.06.2021, Jürgen Rümmler IT-Solutions, Waldheideweg 8, 46569 Hünxe, Deutschland, mailto:info@ruemmler.net
        
        Weitergabe oder Verkauf ist nicht gestattet.
        
        Dieses Dokument ist NICHT Bestandteil von ChromisPosDE und unterliegt NICHT der GPL


***********************

        Dies ist eine Test-Version.

***********************

-->
<export>
    <name>DSFinV-K</name>
    <inputkeys>
        <inputkey>
            <name>key0</name>
            <type>date</type>
            <descr>Start-Datum</descr>
        </inputkey>
        <inputkey>
            <name>key1</name>
            <type>date</type>
            <descr>Ende-Datum</descr>
        </inputkey>
        <inputkey>
            <name>key2</name>
            <type>String</type>
            <descr>Kassen-ID</descr>
        </inputkey>
    </inputkeys>
	<exportschema>
		<file>gdpdu-01-09-2004.dtd</file>
		<ressource>Export.DSFinV-K.schema</ressource>
	</exportschema>
	<index_xml_header>
		<doctype>DataSet SYSTEM "gdpdu-01-09-2004.dtd"</doctype>
		<DataSet>
			<Version>1.0</Version>
			<DataSupplier>
				<Name/>
				<Location/>
				<Comment>Datenträgerüberlassung nach DSFinV-K Version 2.2</Comment>
			</DataSupplier>
			<Media>
				<Name>Datenträger 1</Name>
			</Media>
		</DataSet>
	</index_xml_header>
    <exportfiles>
        <exportfile>
			<createfilewithoutcontent>true</createfilewithoutcontent>
			<format>csv</format>
			<Table>
				<URL>lines.csv</URL>
				<Name>Bonpos</Name>
				<Description>lines.csv</Description>
				<UTF8 />
				<DecimalSymbol>.</DecimalSymbol>
				<DigitGroupingSymbol></DigitGroupingSymbol>
				<Range>
					<From>2</From>
				</Range>
				<VariableLength>
					<ColumnDelimiter>;</ColumnDelimiter>
					<RecordDelimiter>&amp;#xD;&amp;#xA;</RecordDelimiter>
					<TextEncapsulator></TextEncapsulator>
					<VariableColumn><Name>Z_KASSE_ID</Name><Description>ID der (Abschluss-) Kasse</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>Z_ERSTELLUNG</Name><Description>Zeitpunkt des Kassenabschlusses</Description><AlphaNumeric/><MaxLength>30</MaxLength></VariableColumn>
					<VariableColumn><Name>Z_NR</Name><Description>Nr. des Kassenabschlusses</Description><Numeric/></VariableColumn>
					<VariableColumn><Name>BON_ID</Name><Description>Vorgangs-ID</Description><AlphaNumeric/><MaxLength>40</MaxLength></VariableColumn>
					<VariableColumn><Name>POS_ZEILE</Name><Description>Zeilennummer</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>GUTSCHEIN_NR</Name><Description>Gutschein-Nr.</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>ARTIKELTEXT</Name><Description>Artikeltext</Description><AlphaNumeric/><MaxLength>255</MaxLength></VariableColumn>
					<VariableColumn><Name>POS_TERMINAL_ID</Name><Description>ID des POS-Terminals</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>GV_TYP</Name><Description>Geschäftsvorfall-Art</Description><AlphaNumeric/><MaxLength>30</MaxLength></VariableColumn>
					<VariableColumn><Name>GV_NAME</Name><Description>Name des Geschäftsvorfalls</Description><AlphaNumeric/><MaxLength>40</MaxLength></VariableColumn>
					<VariableColumn><Name>INHAUS</Name><Description>Verzehr an Ort und Stelle</Description><AlphaNumeric/><MaxLength>1</MaxLength></VariableColumn>
					<VariableColumn><Name>P_STORNO</Name><Description>Positionsstorno-Kennzeichnung</Description><AlphaNumeric/><MaxLength>1</MaxLength></VariableColumn>
					<VariableColumn><Name>AGENTUR_ID</Name><Description>ID der Agentur</Description><Numeric/></VariableColumn>
					<VariableColumn><Name>ART_NR</Name><Description>Artikelnummer</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>GTIN</Name><Description>GTIN</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>WARENGR_ID</Name><Description>Warengruppen-ID</Description><AlphaNumeric/><MaxLength>40</MaxLength></VariableColumn>
					<VariableColumn><Name>WARENGR</Name><Description>Bezeichnung Warengruppe</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>MENGE</Name><Description>Menge</Description><Numeric><Accuracy>3</Accuracy></Numeric></VariableColumn>
					<VariableColumn><Name>FAKTOR</Name><Description>Faktor, z. B. Gebindegrößen</Description><Numeric><Accuracy>3</Accuracy></Numeric></VariableColumn>
					<VariableColumn><Name>EINHEIT</Name><Description>Maßeinheit, z. B. kg, Liter oder Stück</Description><AlphaNumeric/><MaxLength>50</MaxLength></VariableColumn>
					<VariableColumn><Name>STK_BR</Name><Description>Preis pro Einheit inkl. USt</Description><Numeric><Accuracy>5</Accuracy></Numeric></VariableColumn>
				</VariableLength>
			</Table>
			<sql>
select 
    closedcash.TERMINALSERIAL Z_KASSE_ID,
    date_format(closedcash.DATEEND,'%Y-%m-%dT%H:%i:%s') Z_ERSTELLUNG,
    replace(format(closedcash.HOSTSEQUENCE, 0, 'en_EN'),',','') Z_NR,
    receipts.ID BON_ID,
    replace(format(ifnull(ticketlines.LINE,0), 0, 'en_EN'),',','') POS_ZEILE,
    '' GUTSCHEIN_NR,
    '' ARTIKELTEXT,
    closedcash.TERMINALSERIAL POS_TERMINAL_ID,
    receipts.GV_TYP,
    '' GV_NAME,
    '0' INHAUS,
    '' P_STORNO,
    '0' AGENTUR_ID,
    ''  ART_NR,
    '' GTIN,
    '' WARENGR_ID,
    '' WARENGR,
    '1.000' MENGE,
    '1.000' FAKTOR,
    '' EINHEIT,
    paym.TOTAL STK_BR
from receipts
     inner join tickets on receipts.ID = tickets.ID
     left join ticketlines on tickets.ID = ticketlines.TICKET
     left join closedcash on receipts.MONEY = closedcash.MONEY
     left join products on ticketlines.PRODUCT = products.ID
     left join (select receipt, sum(total) total from payments group by receipt) paym on receipts.ID = paym.RECEIPT
where receipts.DATENEW &gt;= STR_TO_DATE(:key0, '%d.%m.%Y')
  and receipts.DATENEW &lt;= STR_TO_DATE(:key1, '%d.%m.%Y')
  and closedcash.TERMINALSERIAL = :key2 
order by tickets.TICKETID, ticketlines.LINE
			</sql>
        </exportfile>
    </exportfiles>
</export>
