Metadata-Version: 2.4
Name: eam_report_helper
Version: 0.2.22
Summary: A package that returns formatted data for reports
Author-email: Dave Gunn <daveg@8amsolutions.com>
License-File: LICENSE
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Requires-Python: >=3.8
Description-Content-Type: text/markdown

# eam_report_helper

This package builds CSV reports for the 8am Web App system. It provides the `ReportHelper` class, which connects to Cosmos DB and Azure AD B2C, and dispatches report building based on a report id. Each report is assembled into a CSV and returned as a base64-encoded string.

## Installation

```bash
pip install eam_report_helper
```

## Usage

### Constructor

```python
from eam_report_helper.report_helper import ReportHelper

report_helper = ReportHelper('<company_id>', '<branch_id>')
```

The constructor requires the following environment variables:

- `COSMOS_ACCOUNT_URI`, `COSMOS_KEY`, `COSMOS_DATABASE`, `COSMOS_CONTAINER` - Cosmos DB connection details
- `B2C_USER_MANAGEMENT_APP_CLIENT_ID`, `B2C_USER_MANAGEMENT_APP_CLIENT_SECRET`, `B2C_TENANT_ID`, `B2C_EXTENSION_APP_CLIENT_ID` - Azure AD B2C user management app registration details

### ReportHelper.build_report(report_id)

Builds the report for the given report id and returns the CSV content as a base64-encoded string. The report id determines which report is generated:

| Report | Description |
| --- | --- |
| Safety tickets | Safety ticket report for the company/branch, via `SafetyTicketsReport` |
| Vendor documents | Vendor document report, via `VendorDocumentsReport` |
| Employee questions | Employee questions report, via `EmpQuestionsReport` |
| Vendor deficiencies | Vendor deficiencies report, via `VendorDeficienciesReport` |
| Form tracking reports | Incident report, near miss/hazard ID, and regulatory corrective action tracking, via `FormReportHelper` |
| Newly added vendors | Newly added vendor (Saturn) report, via `NewlyAddedVendorReport` |
| Operator tickets | Operator ticket report, via `OperatorTicketReport` (built on top of the vendor deficiencies report) |
| User doc expiries | User document expiry report, via `UserDocExpiries` |
| Form distributions with tickets | Form distribution report merged with ticket data, via `FormDistributionWithTicketsReport` (template ids and ticket types come from the report metadata) |

```python
base64_csv = report_helper.build_report('<report_id>')
```

### ReportHelper.get_metadata(report_id)

Returns the metadata for a given report id, fetched from the company's `companyReports` record in the database.

### ReportHelper.assemble_data(column_names, rows, file_name)

Assembles the given columns and rows into a CSV file, reads it back, and returns its contents as a base64-encoded string (the temporary file is removed).

## Report Builders

Each report is implemented by a dedicated class in `common/`. Each builder's `build_*` method returns a dict with `headers` (list of column names), `rows` (list of dicts keyed by header name), and `file_name` (the output CSV file name).

### SafetyTicketsReport

`SafetyTicketsReport(db, company_id, b2c_helper).build_safety_ticket_report(branch_id)`

Builds a matrix of worker safety tickets per ticket type. Report headers are derived from `doc` items of `docClass = 'additionalRequirement'` with `isUserTicket = true` (each header is a `"<docType> <question text>"` pair from the corresponding `userDocQuestion` answers). Rows are built from the company's B2C users (filtered by branch) and their `userDoc`/`userDocQuestion` records; for each ticket type the most recent expiry date is kept. Output file: safety ticket report CSV.

### VendorDocumentsReport

`VendorDocumentsReport(db, company_id).build_vendor_document_report()`

Lists all vendor documents (excluding `Not Submitted`) for the vendors on the employer's vendor list. Expiry dates are resolved from the document's OCR output (`updatedValue` preferred over `ocrValue`), falling back to the matching `vendorDocQuestion` answer when OCR output is empty. Rows are sorted by vendor name. Headers: `Vendor Name`, `Document Type`, `Document Name`, `Status`, `Expiry Date`, `Last Modified`. Output file: `Vendor_Documents_Report.csv`.

### EmpQuestionsReport

`EmpQuestionsReport(db, company_id).build_emp_questions_report()`

Builds a vendor-by-question matrix of employer question answers. Questions come from `empQuestion` items assigned to the company; answers come from `vendAnswer` items for the company's vendors. Multi-select answers are flattened to a comma-separated string of active option texts. Headers: `Vendor Name` followed by one column per question text. Output file: `Questions_Report.csv`.

### VendorDeficienciesReport

`VendorDeficienciesReport(db, company_id).build_vendor_deficiencies_report()`

Lists each vendor on the employer's vendor list with its current deficiencies. When a vendor has multiple deficiencies, one row is produced per deficiency. Rating info (`Date Rating Applied`, `Days Since Rating Applied`) is derived from the vendor list's `ratingsUpdated` timestamp. If a `vendorMonitorConfig` exists for the employer, additional internal vendor ID columns (from `employerVendorLabels`/`employerVendorIds`) are appended. Headers: `Vendor Name`, `Subscription Type`, `Vendor Tags`, `Vendor Rating`, `Current Deficiencies`, `Date Rating Applied`, `Days Since Rating Applied`, `Admin Name`, `Admin Email`, `Admin Phone` (plus internal ID columns when configured). Output file: `Def_Report.csv`.

### NewlyAddedVendorReport

`NewlyAddedVendorReport(db, company_id).build_saturn_report()`

Lists vendors added to the employer's vendor list within the last 30 days, most recent first. Headers: `Vendor Name`, `Date Vendor Added`, `Vendor City`, `Vendor Admin Name`, `Vendor Admin Email`, `Vendor Admin Phone`, `Rating`, `Current Deficiencies`. Output file: `Added_Vendors.xlsx`.

### OperatorTicketReport

`OperatorTicketReport(db, company_id, vendor_deficiency_results).build_operator_tickets_report()`

Builds on top of the vendor deficiencies report: filters its rows to operators (`Subscription Type == 0`), then fetches their ticket documents (`vendorDoc` items of `docClass = 'additionalRequirement'`, excluding `Not Submitted`). Ticket values are resolved from the document's `determinations`, OCR output, or `vendorDocQuestion` answers (in that order of preference). Headers: the operator columns from the deficiencies report plus one `"<docType>: <question text>"` column per ticket question. Output file: operator tickets CSV.

### UserDocExpiries

`UserDocExpiries(db, company_id, b2c_helper).build_user_doc_expiries_report()`

Builds a worker-by-document-type matrix of user document expiry dates. Document types (from the company's `userDoc` items) become the columns; expiry dates are resolved from each document's OCR output, falling back to the matching `userDocQuestion` (`expiry_date`) answer. Only workers with at least one expiry value are included. Headers: `Worker Name` followed by one column per doc type. Output file: `User_Ticket_Expiries.csv`.

### FormDistributionWithTicketsReport

`FormDistributionWithTicketsReport(db, company_id, template_id, tickets).build_form_distribution_with_tickets_report()`

Combines form distribution data (via `eam_doc_helper`'s `ReportBuilder`) with worker ticket data, merged by user id. Each row is a worker's form distribution record (company, name, email, created/completed dates, status) plus, per ticket, a computed status column and one column per configured ticket value. Ticket status is computed as: `"<Validated|Invalid> and <Expired|Not Expired>"` for Valid/Invalid tickets with a parseable expiry date, `"Ticket Uploaded But Not Validated"` for incomplete or unparseable tickets, and `"No Ticket Uploaded"` otherwise. Headers: `Company`, `Worker Name`, `Email`, `<templateName> Created On`, `<templateName> Completed On`, `<templateName> Status`, plus `<ticket_name> Status` and `<ticket_name> <column_name>` per ticket value. Output file: `Form_Distribution_Tickets_Report.csv`.

### FormReportHelper

`FormReportHelper(db, b2c_helper).get_form_report(template_id, report_name, branch_id)`

Builds a tracking report for a specific form template (e.g. incident reports, near miss/hazard IDs, regulatory corrective actions). Fetches the template and its `formDistribution` records (newest first), matches each distribution's element answers to the template's element labels (including sub-form elements), and formats list values into joined strings. Columns come from the template's element labels plus any sub-form element keys; empty columns are removed. The `report_name` argument determines the output file name (e.g. `Insignia_IncidentReport_Tracking.csv`).

### Helpers

A `generate_csv(column_list, file_name, data)` helper is also available in `common/helpers.py`.
