From 40ee98743f7336c609adc1477c3026a72fa372b7 Mon Sep 17 00:00:00 2001
From: Daniel Weipert <code@drogueronin.de>
Date: Fri, 19 May 2023 21:52:59 +0200
Subject: initial commit

---
 LibreOfficeCalc-SPG-Verein-SEPA-Export-Filter.xslt | 210 +++++++++++++++++++++
 1 file changed, 210 insertions(+)
 create mode 100644 LibreOfficeCalc-SPG-Verein-SEPA-Export-Filter.xslt

diff --git a/LibreOfficeCalc-SPG-Verein-SEPA-Export-Filter.xslt b/LibreOfficeCalc-SPG-Verein-SEPA-Export-Filter.xslt
new file mode 100644
index 0000000..ff77328
--- /dev/null
+++ b/LibreOfficeCalc-SPG-Verein-SEPA-Export-Filter.xslt
@@ -0,0 +1,210 @@
+<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
+<xsl:stylesheet
+  version="1.0"
+  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
+  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
+  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+  xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
+  xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
+  xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
+  xmlns:exsl="http://exslt.org/common"
+  xmlns:date="http://exslt.org/dates-and-times"
+  xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02"
+  xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd"
+  extension-element-prefixes="date"
+  exclude-result-prefixes="xsd office table text exsl date"
+>
+
+  <xsl:output
+    method="xml"
+    indent="yes"
+    encoding="UTF-8"
+    omit-xml-declaration="no"
+  />
+
+  <xsl:template match="/" name="main">
+
+    <!-- build column headings variable for reference -->
+    <xsl:variable name="columnHeadings-temp" xmlns="">
+      <xsl:for-each select="//table:table/table:table-row[not(preceding::table:table-row)]//table:table-cell">
+        <xsl:element name="heading">
+          <xsl:attribute name="name">
+            <xsl:value-of select="normalize-space(text:p)" />
+          </xsl:attribute>
+          <xsl:value-of select="position()" />
+        </xsl:element>
+      </xsl:for-each>
+    </xsl:variable>
+    <xsl:variable name="columnHeadings" select="exsl:node-set($columnHeadings-temp)" />
+
+    <!-- build table sub tree -->
+    <xsl:variable name="table-temp" xmlns="">
+      <xsl:for-each select="//table:table/table:table-row">
+        <xsl:if test="position() > 1">
+          <table:table-row>
+            <xsl:for-each select="table:table-cell">
+              <xsl:call-template name="repeat-columns" match="." />
+            </xsl:for-each>
+          </table:table-row>
+        </xsl:if>
+      </xsl:for-each>
+    </xsl:variable>
+    <xsl:variable name="table" select="exsl:node-set($table-temp)" />
+
+    <!-- get entity data from settings -->
+    <xsl:variable name="entity-temp" xmlns="">
+      <xsl:element name="name">
+        <xsl:value-of select="'ENTITY_NAME'" />
+      </xsl:element>
+
+      <xsl:element name="IBAN">
+        <xsl:value-of select="'DE1234567890'" />
+      </xsl:element>
+
+      <xsl:element name="BIC">
+        <xsl:value-of select="'BYLADEM1NESSSSSS'" />
+      </xsl:element>
+    </xsl:variable>
+    <xsl:variable name="entity" select="exsl:node-set($entity-temp)" />
+
+    <!--<xsl:variable name="number-of-transactions" select="count(//table:table/table:table-row/table:table-cell[number($columnHeadings/heading[@name='Verknüpfung'])][not(normalize-space())]) + count(//table:table/table:table-row/table:table-cell[number($columnHeadings/heading[@name='Verknüpfung'])][number(text:p) &lt;= 0])" />-->
+    <xsl:variable name="number-of-transactions" select="count($table/table:table-row/table:table-cell[number($columnHeadings/heading[@name='IBAN'])]/text:p)" />
+    <xsl:variable name="control-sum" select="sum($table/table:table-row/table:table-cell[number($columnHeadings/heading[@name='Beitrag_1'])]/text:p)" />
+
+    <Document>
+
+      <GrpHdr>
+        <MsgId>MSG<xsl:value-of select="generate-id($columnHeadings)" /></MsgId>
+        <CreDtTm>
+          <xsl:value-of select="date:date()" />
+        </CreDtTm>
+        <NbOfTxs><xsl:value-of select="$number-of-transactions" /></NbOfTxs>
+        <CtrlSum><xsl:value-of select="$control-sum" /></CtrlSum>
+        <InitgPty>
+          <Nm><xsl:value-of select="$entity/name" /></Nm>
+        </InitgPty>
+      </GrpHdr>
+
+      <PmtInf>
+
+        <PmtInfId>PII<xsl:value-of select="generate-id($table)" /></PmtInfId>
+        <PmtMtd>DD</PmtMtd>
+        <NbOfTxs><xsl:value-of select="$number-of-transactions" /></NbOfTxs>
+        <CtrlSum><xsl:value-of select="$control-sum" /></CtrlSum>
+        <PmtTpInf>
+          <SvcLvl>
+            <Cd>SEPA</Cd>
+          </SvcLvl>
+          <LclInstrm>
+            <Cd>CORE</Cd>
+          </LclInstrm>
+          <SeqTp>OOFF</SeqTp>
+        </PmtTpInf>
+        <ReqdColltnDt>
+          <xsl:value-of select="date:date()" />
+        </ReqdColltnDt>
+        <Cdtr>
+          <Nm><xsl:value-of select="$entity/name" /></Nm>
+        </Cdtr>
+        <CdtrAcct>
+          <Id>
+            <IBAN><xsl:value-of select="$entity/IBAN" /></IBAN>
+          </Id>
+        </CdtrAcct>
+        <CdtrAgt>
+          <FinInstnId>
+            <BIC><xsl:value-of select="$entity/BIC" /></BIC>
+          </FinInstnId>
+        </CdtrAgt>
+        <ChrgBr>SLEV</ChrgBr>
+
+        <xsl:for-each select="$table/table:table-row">
+
+          <xsl:if test="table:table-cell[number($columnHeadings/heading[@name='IBAN'])]/text:p">
+
+            <DrctDbtTxInf>
+              <PmtId>
+                <EndToEndId>NOTPROVIDED</EndToEndId>
+              </PmtId>
+
+              <InstdAmt Ccy="EUR"><xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='Beitrag_1'])]/text:p" /></InstdAmt>
+
+              <DrctDbtTx>
+                <MndtRltdInf>
+                  <MndtId><xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='SEPA_Mandatsreferenz'])]/text:p" /></MndtId>
+                  <DtOfSgntr><xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='SEPA_Datum_Mandatsref' or @name='Eintritt'])]/text:p" /></DtOfSgntr>
+                </MndtRltdInf>
+              </DrctDbtTx>
+
+              <DbtrAgt>
+                <FinInstnId>
+                  <Othr>
+                    <Id>NOTPROVIDED</Id>
+                  </Othr>
+                </FinInstnId>
+              </DbtrAgt>
+
+              <Dbtr>
+                <Nm>
+                  <xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='Vorname'])]/text:p" />
+                  <xsl:text> </xsl:text>
+                  <xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='Nachname'])]/text:p" />
+                </Nm>
+              </Dbtr>
+
+              <DbtrAcct>
+                <Id>
+                  <IBAN><xsl:value-of select="table:table-cell[number($columnHeadings/heading[@name='IBAN'])]/text:p" /></IBAN>
+                </Id>
+              </DbtrAcct>
+
+              <RmtInf>
+                <Ustrd>Mitgliedsbeitrag <xsl:value-of select="date:year()" /></Ustrd>
+              </RmtInf>
+            </DrctDbtTxInf>
+
+          </xsl:if>
+
+        </xsl:for-each>
+
+      </PmtInf>
+
+    </Document>
+
+  </xsl:template>
+
+  <!-- @see https://forum.openoffice.org/en/forum/viewtopic.php?p=131442#p131442 -->
+  <xsl:template match="table:table-cell" name="repeat-columns">
+    <xsl:choose>
+      <xsl:when test="@table:number-columns-repeated">
+        <xsl:variable name="number-columns" select="number(@table:number-columns-repeated)" />
+
+        <xsl:call-template name="repeat-column">
+          <xsl:with-param name="current-column" select="0" />
+          <xsl:with-param name="total-columns" select="$number-columns" />
+        </xsl:call-template>
+      </xsl:when>
+      <xsl:otherwise>
+        <xsl:copy-of select="." />
+      </xsl:otherwise>
+    </xsl:choose>
+  </xsl:template>
+
+  <xsl:template name="repeat-column">
+    <xsl:param name="current-column" />
+    <xsl:param name="total-columns" />
+
+    <xsl:choose>
+      <xsl:when test="$current-column &lt; $total-columns">
+        <xsl:copy-of select="." />
+
+        <xsl:call-template name="repeat-column">
+          <xsl:with-param name="current-column" select="$current-column + 1" />
+          <xsl:with-param name="total-columns" select="$total-columns" />
+        </xsl:call-template>
+      </xsl:when>
+    </xsl:choose>
+  </xsl:template>
+
+
+</xsl:stylesheet>
-- 
cgit v1.2.3