# Python Microsoft Fabric - Sending reports on a schedule

Query Fabric once a month, build a CSV and deliver it by SFTP and email.

On the first of each month, one scheduled service queries a lakehouse table for last month's totals, builds a CSV from them and delivers it to an SFTP server and by email.

> **What you need from your Fabric admin**
>
> The workspace ID and the lakehouse ID, from the address bar when the lakehouse is open, and the Livy endpoint tenant setting that lets apps run queries, under Developer settings in the Fabric admin portal.

## The table in Fabric {#the-table-in-fabric}

The query reads last month's rows of the `invoices` table:

## The service {#the-service}

The query groups last month's rows, the result becomes CSV, and the file is sent twice - to the SFTP server through an [SFTP connection](https://zato.io/docs/dev/examples/sftp.html) and as an attachment through an [SMTP connection](https://zato.io/docs/dev/examples/smtp.html), both created in the Dashboard beforehand.

```python
# -*- coding: utf-8 -*-

# stdlib
import csv
import io
from datetime import date, timedelta

# Zato
from zato.common import SMTPMessage
from zato.server.service import Service

class MonthlyInvoiceSummary(Service):

    name = 'reports.monthly-invoice-summary'

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        # Last month - from its first day up to, not including, the first day of this month.
        today = date.today()
        month_start = today.replace(day=1)
        previous_month_end = month_start - timedelta(days=1)
        previous_month_start = previous_month_end.replace(day=1)
        month_label = previous_month_start.strftime('%Y-%m')

        range_start = previous_month_start.isoformat()
        range_end = month_start.isoformat()

        # One row per insurer - what was invoiced and what has been paid.
        sql = f'''
        select
            insurer,
            count(*) as invoices,
            round(sum(amount), 2) as invoiced,
            round(sum(case when status = 'Paid' then amount else 0 end), 2) as paid
        from invoices
        where sent_at >= '{range_start}'
          and sent_at < '{range_end}'
        group by insurer
        order by insurer
        '''

        rows = conn.query(workspace_id, lakehouse_id, sql)

        # Turn the rows into CSV ..
        buffer = io.StringIO()
        writer = csv.DictWriter(buffer, fieldnames=['insurer', 'invoices', 'invoiced', 'paid'])
        writer.writeheader()
        writer.writerows(rows)

        text = buffer.getvalue()
        file_name = f'invoice-summary-{month_label}.csv'

        # .. upload it to the SFTP server ..
        remote_path = f'/reports/{file_name}'

        sftp = self.sftp['Finance SFTP']
        sftp.write(text, remote_path)

        # .. and email it.
        msg = SMTPMessage()
        msg.subject = f'Invoice summary {month_label}'
        msg.to = 'finance-reports@example.com'
        msg.from_ = 'zato@example.com'
        msg.body = f'The invoice summary for {month_label} is attached.'
        msg.attach(file_name, text)

        smtp = self.email.smtp.get('Reports Email')
        smtp.conn.send(msg)

        self.response.payload = {'file': file_name, 'rows': len(rows)}
```

## Scheduling it {#scheduling-it}

In the Dashboard, go to `Scheduler → Config`, click "Create a new job", pick the cron-style type with `0 6 1 * *` - six in the morning on the first day of each month - and `reports.monthly-invoice-summary` as the service. The [scheduler](https://zato.io/docs/dev/examples/scheduler.html) page shows the form and how to create the same job from code.

## The file {#the-file}

Run for March, the CSV has one line per group of the query:

```text
insurer,invoices,invoiced,paid
Cascade Health Plan,2,2895.50,2275.00
Evergreen Assurance,1,415.75,0.00
Pacific Mutual,1,1840.00,1840.00
```

## See also {#see-also}

- [Sending and receiving files](https://zato.io/docs/dev/examples/cloud/fabric/files.html) - Writing a CSV file to the lakehouse
- [Building an API on top of Fabric data](https://zato.io/docs/dev/examples/cloud/fabric/api-on-fabric-data.html) - Running a lakehouse query from a REST channel
- [Queries](https://zato.io/docs/dev/examples/cloud/fabric/api/queries.html) - query and Spark sessions in detail

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - Everything about writing services, in one place
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how to shape both
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns