twigfiddle.com - Fiddle xovsv8 v11 - Created on July 19, 2019 14:23

File

{% spaceless %}

    {% set headers = [
        'GROUP_ID', 'BLART', 'BLDAT', 'BUKRS', 
        'BUDAT', 'CURRENCY', 'XBLNR', 'NEWBS', 
        'NEWKO', 'WRBTR', 'MWSKZ', 'KOSTL', 'PS_POSID', 
        'ZUONR', 'SGTXT', 'ZFTERM', 'ZFBDT'
    ] %}

    {% set records = [] %}
    {% for report in export.reports %}
    
        {# Filter for expenses with a category code2 #}
        {% set expenses = [] %}
        {% for expense in report.bills if expense.category.code2 %}
            {% set expenses = expenses|merge([expense]) %}
        {% endfor %}
        
        {% if expenses|length > 0 %}
            {% set records = records|merge([{
                'GROUP_ID': report.id,
                'BLART': 'KD',
                'BLDAT': export.date|date('d-m-Y'),
                'BUKRS': 'FSS',
                'BUDAT': export.date|date('d-m-Y'),
                'CURRENCY': 'EUR',
                'XBLNR': report.name,
                'NEWBS': report.value >= 0 ? 31 : 40,
                'NEWKO': report.user.code,
                'WRBTR': (-report.value)|number_format(2, ',', ''),
                'MWSKZ': '',
                'KOSTL': '',
                'PS_POSID': '',
                'ZUONR': '',
                'SGTXT': '',
                'ZFTERM': 'NA00',
                'ZFBDT': ''
            }]) %}
            
            {% for expense in report.bills %}
                {% for vatLine in expense.billVats %}
                
                    {% if not report.tag1 %}
                        {# No project #}
                        {% set newko = expense.category.code %}
                        {% set kostl = expense.user.id %}{# expense.user.fieldData('costcenter') #}
                        {% set posid = '' %}
                        
                    {% elseif report.tag1.code == 'IBS' %}
                        {# Project "Invitation by SpaceCraft" #}
                        {% set newko = expense.category.code3 %}
                        {% set kostl = expense.user.id %}{# expense.user.fieldData('costcenter') #}
                        {% set posid = '' %}
                        
                    {% elseif report.tag1.code == 'Y20181015' %}
                        {# Project "GeoNext Coeng ROM and FFP" #}
                        {% set newko = expense.category.code %}
                        {% set kostl = '' %}
                        {% set posid = expense.tag1.code %}
                        
                    {% else %}
                        {# Other projects #}
                        {% set newko = expense.category.code2 %}
                        {% set kostl = '' %}
                        {% set posid = expense.tag1.code %}  
                        
                    {% endif %}
                    
                    
                    {% set records = records|merge([{
                        'GROUP_ID': report.id,
                        'BLART': 'KD',
                        'BLDAT': export.date|date('d-m-Y'),
                        'BUKRS': 'FSS',
                        'BUDAT': export.date|date('d-m-Y'),
                        'CURRENCY': 'EUR',
                        'XBLNR': report.name,
                        'NEWBS': vatLine.value >= 0 ? 40 : 31,
                        'NEWKO': report.tag1 ? expense.category.code2 : expense.category.code,
                        'WRBTR': vatLine.value|number_format(2, ',', ''),
                        'MWSKZ': report.tag1 ? vatLine.vat.code,
                        'KOSTL': report.user.id,
                        'PS_POSID': expense.tag1.code|default(''),
                        'ZUONR': '',
                        'SGTXT': expense.description|replace({'\r':'', '\n':'', ';':':'}),
                        'ZFTERM': 'NA00',
                        'ZFBDT': ''
                    }]) %}
                {% endfor %}
            {% endfor %}
        {% endif %}
    {% endfor %}
{% endspaceless %}

{{ headers|join(';') }}
{% for record in records %}
    {%- for key in headers %}
        {{- record[key]|default('') }};
    {%- endfor %}

{% endfor %}

Format

export:
    date: '2019-04-02'
    reports:
        - id: 1
          value: 36.30
          name: 'Januari'
          creationDate: '2019-04-02'
          user:
              id: 1
              code: 'A100'
              initials: 'J'
              prefix: 'The'
              lastName: 'Doe'
              fullName: 'Jack Doe'
              email: 'jack@company.com'
          bills:
              - id: 1
                description: 'Lunch Kirsten'
                amount: 12.10
                value: 12.10
                currency: 'EUR'
                distance: 0
                number: 0
                category:
                    id: 1
                    name: 'Lunch'
                    code: '0100'
                    code2: 'abcd'
                tag1:
                    id: 1
                    name: 'Project 1'
                    code: 'PRJ1'
                billVats:
                    - id: 1
                      baseAmount: 10.00
                      baseValue: 10.00
                      amount: 2.10
                      value: 2.10
                      vat:
                          id: 1
                          percentage: 21
                          code: H
              - id: 2
                description: 'Paperclips'
                amount: 24.20
                value: 24.20
                currency: 'EUR'
                distance: 0
                number: 0
                category:
                    id: 2
                    name: 'Office'
                    code: '0200'
                    code2: 'defg'
                tag1:
                    id: 2
                    name: 'Project 2'
                    code: 'PRJ2'
                billVats:
                    - id: 1
                      baseAmount: 20.00
                      baseValue: 20.00
                      amount: 4.20
                      value: 4.20
                      vat:
                          id: 1
                          percentage: 21
                          code: H
        - id: 2
          name: 'Januari'
          value: 23.20
          creationDate: '2019-01-05'
          user:
              id: 2
              code: 'B200'
              initials: 'M'
              prefix: ''
              lastName: 'Mo'
              fullName: 'Mary Mo'
              email: 'mary@company.com'
          bills:
              - id: 3
                description: 'Lunch Peter'
                amount: 5.05
                value: 5.05
                currency: 'EUR'
                distance: 0
                number: 0
                tag1:
                    id: 1
                    name: 'Project 1'
                    code: 'PRJ1'
                category:
                    id: 1
                    name: 'Lunch'
                    code: '0100'
                    code2: 'abcd'
                billVats:
                    - id: 1
                      baseAmount: 5.00
                      baseValue: 5.00
                      amount: 0.50
                      value: 0.50
                      vat:
                          id: 1
                          percentage: 21
                          code: H
              - id: 4
                description: 'Etentje'
                amount: 18.15
                value: 18.15
                currency: 'EUR'
                distance: 0
                number: 0
                tag1:
                    id: 1
                    name: 'Project 1'
                    code: 'PRJ1'
                category:
                    id: 1
                    name: 'Lunch'
                    code: '0100'
                    code2: 'defg'
                billVats:
                    - id: 1
                      baseAmount: 15.00
                      baseValue: 15.00
                      amount: 3.15
                      value: 3.15
                      vat:
                          id: 1
                          percentage: 21
                          code: H