Installation:

$ npm i @hungvuongtech/vh-erp-export-module
$ yarn add @hungvuongtech/vh-erp-export-module

Usage:

From raw data:

import {
    ExportServiceProvider,
    ExportServiceType,
} from '@hungvuongtech/vh-erp-export-module';

ExportServiceProvider.create(ExportServiceType.EXCEL)
    .data([
        {
            id: 1,
            name: 'Example 1',
        },
        {
            id: 2,
            name: 'Example 2',
        },
    ])
    .export({
        fileName: 'data.xlsx',
    });

From the database:

import {
    DbRepositoryProvider,
    ExportServiceProvider,
    DbRepositoryType,
    ExportServiceType,
} from '@hungvuongtech/vh-erp-export-module';

// If you stored the database config in the config file
const repository = DbRepositoryProvider.create(DbRepositoryType.MY_SQL);

// If you want to pass the database config at runtime
const repository = DbRepositoryProvider.create(DbRepositoryType.MY_SQL).connect(
    {
        database: 'YOUR_DATABASE_NAME',
        username: 'YOUR_DATABASE_USERNAME',
        password: 'YOUR_DATABASE_PASSWORD',
        host: 'YOUR_DATABASE_HOST',
        dialect: 'YOUR_DATABASE_DIALECT',
    }
);

ExportServiceProvider.create(ExportServiceType.EXCEL)
    .setRepository(repository)
    .query({
        tables: {
            from: 'users',
            join: [
                {
                    table: 'user_details',
                    leftKey: 'id',
                    rightKey: 'id',
                    select: ['address'],
                    softDelete: true,
                },
                {
                    table: 'roles',
                    leftKey: 'id',
                    rightKey: 'id',
                    through: {
                        table: 'user_roles',
                        leftKey: 'user_id',
                        rightKey: 'role_id',
                        softDelete: true,
                    },
                    select: ['name', 'description'],
                },
            ],
            select: [['id', 'uid'], 'username'],
            softDelete: true,
        },
    })
    .export();

// or you can pass your own select query
ExportServiceProvider.create(ExportServiceType.EXCEL)
    .setRepository(repository)
    .rawQuery(
        'SELECT id, username FROM users LEFT JOIN user_details ON users.id = user_details.id'
    )
    .export();

Export to Google Spreadsheet

ExportServiceProvider.create(ExportServiceType.GOOGLE_SHEET)
    // call if you don't store service account in the config file
    .authorize('YOUR_PATH_TO_SERVICE_ACCOUNT_FILE')
    .data([
        {
            id: 1,
            name: 'Example 1',
        },
        {
            id: 2,
            name: 'Example 2',
        },
    ])
    .export({
        spreadsheetId: 'YOUR_SPREAD_SHEET_ID',
        clear: true, // if false then data will be appended to the specified spreadsheet id
    });

Export to S3

import {
    ExportServiceProvider,
    ExportServiceType,
} from '@hungvuongtech/vh-erp-export-module';

// if your s3 key was stored in config file
ExportServiceProvider.create(ExportServiceType.EXCEL)
    .data([
        {
            id: 1,
            name: 'Example 1',
        },
        {
            id: 2,
            name: 'Example 2',
        },
    ])
    .exportS3()
    .then((result) => console.log(result));

// or config at runtime
const exportService = ExportServiceProvider.create(ExportServiceType.EXCEL);
exportService.s3().configS3({
    accessKeyId: 'YOUR_ACCESS_KEY_ID',
    secretAccessKey: 'YOUR_SECRET_ACCESS_KEY',
    region: 'YOUR_REGION',
});
exportService.s3().configCloudFront({
    keyPairId: 'YOUR_KEY_PAIR_ID',
    privateKey: 'YOUR_PRIVATE_KEY',
});
exportService
    .data([
        {
            id: 1,
            name: 'Example 1',
        },
        {
            id: 2,
            name: 'Example 2',
        },
    ])
    .exportS3()
    .then((result) => console.log(result));

API:

Export Service:

1. common (works for all type)

repository: provide if query to the database

Available options:
- table: {
    from: `tablename`,
    join: [
        {
            table: `to_join_table_name`,
            leftKey: `from_join_on_key`,
            rightKey: `to_join_on_key`,
            through: { # if the association is many-to-many
                table: `junction_table_name`,
                leftKey: `from_reference_key`,
                rightKey: `to_join_reference_key`,
                softDelete: boolean, # default is false
                deletedAt: string # default is deletedAt,
                select: string[],
                type: 'INNER | LEFT OUTER' # default is INNER
            },
            softDelete: boolean, # default is false
            deletedAt: string, # default is deletedAt,
            select: string[],
            type: 'INNER | LEFT OUTER' # default is LEFT OUTER
        }
    ],
    softDelete: boolean, # default is false
    deletedAt: string, # default is deletedAt,
    select: string[],
}
>> Note: If `alias` is not specified, `Column` will be a string: `tablename.column`
- limit: number (optional)
- offset: number = 0
- where: string (optional), literal where query to append

query: raw SQL query, must be a select query

data: array of JSON object

func: transformation function that manipulate the current data (raw data or retrieve from the database),
this function must return a data

Get the latest result of the chaining functions

Catch the error of the chaining functions

2. type = EXCEL

Available options (all are optional):
- header: custom header
- path: relative path from root
- fileName: output path, default is Date.now().xlsx
- append: boolean, is append to sheet (require path)
- skipHeader: by default, a header with key of data will be appended, set skipHeader = false will remove header
- appendSheetName: sheet name to append (require append = true)
- appendSheetIndex: sheet index to append (require append = true), default is first sheet

Available options: (all are optional)
- header: custom header
- bucket: Bucket name
- assetUrl: Asset url
- fileName: output path, default is Date.now().xlsx
- skipHeader: by default, a header with key of data will be appended, set skipHeader = true will remove header
- presigned: default is true

- filePath: object of
+ path: path to folder,
+ fileName: fileName
Available options: (all are optional)
- header: custom header
- bucket: Bucket name
- assetUrl: Asset url
- fileName: output path, default is Date.now().xlsx
- skipHeader: by default, a header with key of data will be appended, set skipHeader = true will remove header
- presigned: default is true

3. type = GOOGLE_SHEET

serviceAccount: path to service-account.json file or object of service account
For more details on how to create a service account, go to:
https://support.google.com/a/answer/7378726?hl=en
>> Note: to use spreadsheet API, both SpreadsheetAPI and DriveAPI must be enabled:
- SpreadsheetAPI: https://console.cloud.google.com/apis/enableflow?apiid=sheets.googleapis.com
- DriveAPI: https://console.cloud.google.com/apis/enableflow?apiid=drive.googleapis.com

Available options:
- title: Spreadsheet title, if create new spreadsheet
- header: custom header
- permission: visibility of the spreadsheets, eg: permission: { type: user, role: reader }
For permission details, go to: https://developers.google.com/drive/api/guides/manage-sharing#create-permission
- clear: boolean, is clear spreadsheet or not (require spreadsheetId)
- spreadsheetId: spread sheet id to append
- sheetName: sheet name to create or append to
- folderId: folder to create in, must be public, if create new
- skipHeader: by default, a header with key of data will be appended, set skipHeader = true will remove header
- range: string, A1 Notation, for more details, go to https://developers.google.com/sheets/api/guides/concepts#expandable-1

Db Repository

1. type = MySQL

By default, repository will first try to read the config from the config file (see Environment part),
call this function will connect to the database manually.
Available config: (all are required)
- database: database name
- username: user name
- password: password
- host: host
- dialect: dialect

Test the connection is working or not

Config

{
    "export_module": {
        "database": {
            "database": "",
            "username": "",
            "password": "",
            "host": "",
            "dialect": ""
        },
        "google": {
            "type": "",
            "project_id": "",
            "private_key_id": "",
            "private_key": "",
            "client_email": "",
            "client_id": "",
            "auth_uri": "",
            "token_uri": "",
            "auth_provider_x509_cert_url": "",
            "client_x509_cert_url": ""
        },
        "s3": {
            "bucket": "",
            "assetUrl": "",
            "accessKeyId": "",
            "secretAccessKey": "",
            "region": "",
            "keyPairId": "",
            "privateKey": ""
        }
    }
}