# Orchard Accounting Statement Export Workflow

General python workflow for generating monthly accounting statements for all labels

### Workflows
There are 2 workflows for generating accounting statements:

1. `accounting_statement_export` (to be renamed to avro_export) - This workflow generates AVRO files for all labels. The AVRO files are generated by an Amazon EMR-Hive job and are stored on Amazon S3. The data is dynamically partitioned during the map-reduce process. Each parition belongs to one label/subaccounting.
2. `custom_export` - This workflow generates the actual accounting statements per client request from the AVRO files on Amazon S3.

### Running the script:

1. For monthly workflow that only being run once a month:
```
accounting_statement_export -p {period_id}
```
Where period_id is the accounting period of the current month.
2. For getting the actual statement:
```
garcon exec custom_export -c '{"period_ids":"202,203,204","user_id":"8869","user_type":"label","locale":"en_US","file_format":"txt","transaction_types":"DT,DA"}'
```
Note that the required parameters are the following. Custom export however should be triggered by a microservice using boto.swf.layer2.WorkflowType.start:

- `period_ids` - comma delimited string of accounting period ids.
- `user_id` - label id or subaccount id
- `user_type` - label or subaccount
- `locale` - iso locale code
- `file_format` - txt or xls
- `transaction_types` - comma delimited string of transaction type codes


### S3 Folder structure
Accounting statement data is organized as the following:
```
- {env}-statement-detail-exports
  - statement_detail_exports
  	- 8869L
  		- 202,203,204_label_DT_es_ES_XLS
  		- 202,203,204_label_DA,DT_en_US_TXT
  - schematized_files
  	- 202_subaccount_all_all_month
  	- 202_label_all_all_month
  	- 202,203,204_subaccount_all_all_quarter
  	- 202,203,204_label_all_all_quarter
  - snowflake_raw_data
  	- 202_subaccount_all_all_month
  	- 202_label_all_all_month
  	- 202,203,204_subaccount_all_all_quarter
  	- 202,203,204_label_all_all_quarter
  - emr_logs
  - emr
  	- hql
```
1. `statement_detail_exports` - stores custom statement export according to the specified parameters from the client. Folder is partitioned by {user_id}{user_type} where `L` represents `label` and `S` represents `subaccount`
2. `schematized_files` - stores AVRO files which are generated from the EMR-Hive job. There will be always 4 sets of data generated for every month. Each of them store all accounting statement data for all labels under specific group. The 4 groups are:
	- Quarterly Label
	- Quarterly Subaccount
	- Monthly Label
	- Monthly Subaccount
3. `snowflake_raw_data` - Similar to schematized_files, snowflake_raw_data also has 4 sets of data every month. The data stored here is the input of the EMR job.
4. `emr_logs` - stores logs related to the EMR jobs
5. `emr` - stores temp HQL scripts generated during the workflow.

### Version
1.0.0

### Installation

The installation process is very straightforward.

### Dependencies
- Python 3.11

### Set up a virtual Python environment
For dev environment
```sh
$ git clone git@github.com:theorchard/processing-accounting.git .
$ pyvenv ./env
$ source ./env/bin/activate
$ pip install -r reqs.pip
$ python setup.py develop
```

### .env.shadow
.env.shadow is a template environment variable script that exports keys and values necessary for successful connectivity, paths, etc.  You will need to copy this file to `.env` and populate the necessary keys.

```sh
$ cp .env.shadow .env
```

### Development and Debugging

Start Development and debugging on Devbox please:

1. finish #Set up a virtual Python environment, #Env.shadow steps
2. Check that you have enough permissions for:

    - S3 bucket defined in .env
    - DynamoDb Table defined in .env. If it doesn't exist - create it
    - SWF service
    - EMR service
    - EC2 service
    - SNS topic defined in .env. If it doesn't exist - create it

3. Check Your EC2 Limits https://console.aws.amazon.com/ec2/v2/home?region=us-east-1#Limits.
   To Start EMR Claster you need at least 1 'm1.large' and 2 'i2.xlarge'

4. Decrease instances size/amount in file:  processing_accounting\flows\accounting_statement_export\setting.py

5. Usually AWS Dev account has no Default VPC, you can define it by yourself adding subnet Id  by code.
 Subnet Id could be found on this page: https://console.aws.amazon.com/elasticmapreduce/home?region=us-east-1#manage-subnets

    ows_export\processing_accounting\util\emr.py
    ```
        api_params = {"Instances.Ec2SubnetId": "subnet-d63d81a0"}

        cluster_id = connector.run_jobflow(
            ami_version='3.11.0',
            api_params=api_params,
            action_on_failure='CONTINUE',
            ec2_keyname='dev_orchard_admin',
            instance_groups=instance_groups,
            job_flow_role='EMR_EC2_DefaultRole',
            keep_alive=keep_alive,
            log_uri=path_to_logs,
            name=job_name.upper(),
            visible_to_all_users=True,
            steps=STEPS,
            service_role='EMR_DefaultRole')
    ```


**To Download XLS/TXT report for some vendor you have to finish 3 steps below:**

#### Generate AVRO file per period/vendor

Regularly for debug purposes you need to regenerate list of AVRO files just for selected vendor/period.

To do it you have to run in background:

```sh
. .env && env/bin/python processing_accounting/cli_dev.py worker accounting_statement_export -l debug
```

```sh
. .env && env/bin/python processing_accounting/cli_dev.py decider accounting_statement_export -l debug
```

Execution itself:

```sh
. .env && env/bin/python processing_accounting/cli_dev.py exec accounting_statement_export -c "{\"period_ids\": \"217\", \"user_id\":\"25948\", \"locale\":\"en_US\", \"user_type\": \"label\"}" -l debug
```

You can check status of execution in SWF console. where you can find your SWF type by Domain: '{}_swf_processing_accounting'.format(os.getenv('Environment')).


#### Generate XLS/TXT report per period/vendor

To do it you have to run in background:

```sh
. .env && env/bin/python processing_accounting/cli_dev.py worker custom_export -l debug
```

```sh
. .env && env/bin/python processing_accounting/cli_dev.py decider custom_export -l debug
```

Execution itself:

```sh
. .env && env/bin/python processing_accounting/cli_dev.py exec custom_export -c "{\"transaction_types\": \"all\", \"period_ids\": \"217\", \"user_id\":\"10303\", \"file_format\":\"xls\", \"locale\":\"en_US\", \"user_type\": \"label\"}" -l debug
```


#### Updating DynamoDb

To make generated by previous step reports visible by ows_accouting microservice
and finally by frontend-accounting it is necessary to generate proper records in Dynamo Db.
In Prod Env. AWS Lambda configured and triggered on "S3 record create" event, but on Dev, regularly, there is no such opportunity.

To get around with this you can:

1. Check that your generated AVRO in previous step has PATH structure like:
   ```
   ('s3://dev-phys-statement-detail-exports/schematized_files/{}_label_all_all_month/user_id_type={}L')
   .format(period_id,label_id)
   ```
   If not, just rename with S3 console existed path to structure above.

2. Run command:

    ```sh
    . .env && env/bin/python processing_accounting/cli_dynamodb_dev.py -r all -u 10303L -p 217 -f 000000_0
    ```