phsp-invoice-loader 1.0.0
Chargebee Invoice Loader
License: MIT
Requires Python: >=3.7

# phsp_invoice_loader

`phsp_invoice_loader` lets you easily create a SQLite database containing invoice data from Chargebee.

## Contents

- [phsp_invoice_loader](#phsp_invoice_loader)
  - [Contents](#contents)
  - [Overview](#overview)
  - [Installation](#installation)
    - [Requirements](#requirements)
    - [Method 1: `pip`](#method-1-pip)
  - [Usage](#usage)
    - [Execution Example](#execution-example)
  - [Database ER Diagram](#database-er-diagram)
  - [What's Next](#whats-next)

## Overview

This tool will create three tables and store data from Chargebee Invoices.
The tables are:

- invoice
- invoice_list
- invoice_discount

The invoice table contains only a subset of columns from the invoices:
id, customer_id, subscription_id, date, due_date, status

The other tables contains all of the fields from their objects, except the column
"object", which has the same value for every record.

Before loading the data, some transformations are applied:

- String values are trimmed and converted to upper case, except id columns;
- Timestamp value are converted to Europe/Berlin Date;
- Boolean values are converted to "YES" or "NO" strings;
- Integer values representing cents and Percentage values are divided by 100.

## Installation

### Requirements

The only requirement is to have Spark installed and correctly configured for running with PySpark.

### Method 1: [`pip`](https://pypi.org/project/phsp_invoice_loader/)

```bash
$ pip install phsp_invoice_loader
```

> **Warning**
> If the following message appears:
> `Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.`
> Add the path shown to your PATH Environment Variable.

## Usage

You need either to define a `CHARGEBEE_API_KEY` environment variable containing your API key or provice your key via command line.

If you chose the environment variable, run `$ phsp_invoice_loader CHARGEBEE_SUBDOMAIN`, where CHARGEBEE_SUBDOMAIN is the subdomain for your Chargebee account.

If you chose to provide your key via command line, run `$ phsp_invoice_loader chargebee_subdomain --apikey YOUR_KEY`

By default, the database will be created in your current working directory and will be called `chargebee.db`. You can override this name with the argument `--dbname NEW_NAME`

> **Warning**
> Be careful: *if the database already exists, the three tables will be dropped and recreated.*

For complete instructions, run `$ phsp_invoice_loader --help`:

```shell
usage: phsp_invoice_loader [-h] [-k API_KEY] [-d DB_NAME] SUBDOMAIN

Chargebee Invoice Loader.

Creates three sqlite tables with the Chargebee invoice data:
    - invoice
    - invoice_item
    - invoice_discount

positional arguments:
  SUBDOMAIN             Chargebee Subdomain.

optional arguments:
  -h, --help            show this help message and exit
  -k API_KEY, --apikey API_KEY             Chargebee API Key.
  -d DB_NAME, --dbname DB_NAME             SQLite Database name. Default is chargebee.db
```

### Execution Example

``` shell
$ phsp_invoice_loader test_subdomain

2022-09-01 21:18:32 - [INFO] [InvoiceLoader]  Starting Invoice Loader
2022-09-01 21:18:32 - [INFO] [InvoiceLoader]  Fetching invoices from Chargebee
2022-09-01 21:18:45 - [INFO] [InvoiceLoader]  618 invoices were found
2022-09-01 21:18:53 - [INFO] [InvoiceLoader]  Loading API responses into the Dataframe
2022-09-01 21:19:03 - [INFO] [InvoiceLoader]  Creating Invoice DataFrame
2022-09-01 21:19:04 - [INFO] [InvoiceLoader]  Creating Invoice Item DataFrame
2022-09-01 21:19:04 - [INFO] [InvoiceLoader]  Creating Invoice Discount DataFrame
2022-09-01 21:19:04 - [INFO] [InvoiceLoader]  Creating/Resetting database chargebee.db
2022-09-01 21:19:07 - [INFO] [InvoiceLoader]  Loading table invoice
2022-09-01 21:19:19 - [INFO] [InvoiceLoader]  618 records loaded into table invoice
2022-09-01 21:19:19 - [INFO] [InvoiceLoader]  Loading table invoice_item
2022-09-01 21:19:31 - [INFO] [InvoiceLoader]  1043 records loaded into table invoice_item
2022-09-01 21:19:31 - [INFO] [InvoiceLoader]  Loading table invoice_discount
2022-09-01 21:19:42 - [INFO] [InvoiceLoader]  66 records loaded into table invoice_discount
2022-09-01 21:19:42 - [INFO] [InvoiceLoader]  Invoice Loader completed successfully
```

## Database ER Diagram

![ER Diagram](https://i.ibb.co/KsrjsSQ/ERDiagram.png)

## What's Next

These are the backlog making this package even better:

- Store metadata in the tables, such as: etl creation date, etl last update, etl process name, etc;
- Also allow incremental loads;
- Only select fields from the API response that exists in the database;
- ... And more to come!

**I hope you like it! :heart:**