# Filters and Sorters

This documentation details the schema required for `.filters` files on the report server. It is used to dynamically
modify the graphQL query and provide the user more power over their reports.

For filters and sorters, valid types include (`type` key in the schema):
- string (default)
- number
- bool or boolean
- date

## Special Notes
- When passing the data to the template server, the property filters and sorters is added to the data object and will reflect the filters and sorters the user has selected

## High level Schema Overview

```javascript
const schema = {
    "filters": [
        {
            "name": "jobs.joblines.mod_lb_hrs", // Name and path of the field in the graphQL query
            "translation": "jobs.joblines.mod_lb_hrs_1", // Translation key for the label used in the GUI
            "label": "mod_lb_hrs_1",    // Label used in the case the GUI does not contain a translation
            "type": "number" // Type of field, can be number or string currently
        },
        // ... more filters
    ],
    "sorters": [
        {
            "name": "jobs.joblines.mod_lb_hrs",  // Name and path of the field in the graphQL query
            "translation": "jobs.joblines.mod_lb_hrs_1",  // Translation key for the label used in the GUI
            "label": "mod_lb_hrs_1", // Label used in the case the GUI does not contain a translation
            "type": "number"  // Type of field, can be number or string currently
        },
        // ... more sorters
    ],
    "dates": {
        // This is not yet implemented and will be added in a future release
    }
}
```

## Filters

Filters effect the where clause of the graphQL query. They are used to filter the data returned from the server.
A note on special notation used in the `name` field.

## Reflection

Filters can make use of reflection to pre-fill select boxes, the following is an example of that in the filters file.

```json
    {
      "name": "jobs.status",
      "translation": "jobs.fields.status",
      "label": "Status",
      "type": "string",
      "reflector": {
        "type": "internal",
        "name": "special.job_statuses"
      }
    }
```

in this example, a reflector with the type 'internal' (all types at the moment require this, and it is used for future functionality), with a name of `special.job_statuses`

The following cases are available

- `special.job_statuses` - This will reflect the statuses of the jobs table `bodyshop.md_ro_statuses.statuses'`
- `special.cost_centers` - This will reflect the cost centers `bodyshop.md_responsibility_centers.costs`
- `special.categories` - This will reflect the categories `bodyshop.md_categories`
- `special.insurance_companies` - This will reflect the insurance companies `bodyshop.md_ins_cos`'
- `special.employee_teams` - This will reflect the employee teams `bodyshop.employee_teams`
- `special.employees` - This will reflect the employees `bodyshop.employees`
- `special.first_names` - This will reflect the first names `bodyshop.employees`
- `special.last_names` - This will reflect the last names `bodyshop.employees`
- `special.referral_sources` - This will reflect the referral sources `bodyshop.md_referral_sources`
- `special.class`- This will reflect the class `bodyshop.md_classes`
- `special.lost_sale_reasons` - This will reflect the lost sale reasons `bodyshop.md_lost_sale_reasons`
- `special.alt_transports` - This will reflect the alternative transports `bodyshop.appt_alt_transport`
- `special.payment_types` - This will reflect the payment types `bodyshop.md_payment_types`
- `special.payment_payers` - This is a special case with a key value set of [Customer, Insurance]

### Path without brackets, multi level

`"name": "jobs.joblines.mod_lb_hrs",`
This will produce a where clause at the `joblines` level of the graphQL query,

```graphql
query gendoc_hours_sold_detail_open($starttz: timestamptz!, $endtz: timestamptz!) {
    jobs(
        where: {date_invoiced: {_is_null: true}, date_open: {_gte: $starttz, _lte: $endtz}, ro_number: {_is_null: false}, voided: {_eq: false}}
    ) {
        joblines(
            order_by: {line_no: asc}
            where: {removed: {_eq: false}, mod_lb_hrs: {_lt: 3}}
        ) {
            line_no
            mod_lbr_ty
            mod_lb_hrs
            convertedtolbr
            convertedtolbr_data
        }
        ownr_co_nm
        ownr_fn
        ownr_ln
        plate_no
        ro_number
        status
        v_make_desc
        v_model_desc
        v_model_yr
        v_vin
        v_color
    }
}
```

### Path with brackets,top level

`"name": "[jobs].joblines.mod_lb_hrs",`
This will produce a where clause at the `jobs` level of the graphQL query.

```graphql
query gendoc_hours_sold_detail_open($starttz: timestamptz!, $endtz: timestamptz!) {
    jobs(
        where: {date_invoiced: {_is_null: true}, date_open: {_gte: $starttz, _lte: $endtz}, ro_number: {_is_null: false}, voided: {_eq: false}, joblines: {mod_lb_hrs: {_gt: 4}}}
    ) {
        joblines(
            order_by: {line_no: asc}
            where: {removed: {_eq: false}}
        ) {
            line_no
            mod_lbr_ty
            mod_lb_hrs
            convertedtolbr
            convertedtolbr_data
        }
        ownr_co_nm
        ownr_fn
        ownr_ln
        plate_no
        ro_number
        status
        v_make_desc
        v_model_desc
        v_model_yr
        v_vin
        v_color
    }
}
```

## Known Caveats

- Will only support two level of nesting in the graphQL query `jobs.joblines.mod_lb_hrs` vs `[jobs].joblines.mod_lb_hrs`
  is fine, but `jobs.[joblines.].some_table.mod_lb_hrs` is not.
- The type object must be 'string' or 'number'  or 'bool' or 'boolean' or 'date' and is case-sensitive.
- The `translation` key is used to look up the label in the GUI, if it is not found, the `label` key is used.
- Do not add the ability to filter things that are already filtered as part of the original query, this would be
  redundant and could cause issues.
- Do not add the ability to filter on things like FK constraints, must like the above example.
- *INHERITANCE CAVEAT* If you have a filters file on a parent report that has a child that you do not want the filters inherited from, you must place a blank filters file (valid json so {}) on the child report level. This will than fetch the child filters, which are empty and move along, versus inheriting the parent filters.

## Sorters

- Sorters follow the same schema as filters, however, they do not do square bracket wrapping to indicate level hoisting,
  a filter added on `job.md_status` would be added at the top level, and a filter added on `jobs.joblines.mod_lb_hrs`
  would be added at the `joblines` level.
- Most of the reports currently do sorting on a template level, this will need to change to actually see the results
  using the sorters.

### Default Sorters

- A sorter can be given a default object containing a `order` and `direction` key value. This will be used to sort the report if the user does not select any of the sorters themselves.
- The `order` key is the order in which the sorters are applied, and the `direction` key is the direction of the sort, either `asc` or `desc`.

```json
{
    "name": "jobs.joblines.mod_lb_hrs",
    "translation": "jobs.joblines.mod_lb_hrs_1",
    "label": "mod_lb_hrs_1",
    "type": "number",
    "default": {
        "order": 1,
        "direction": "asc"
    }
}
```
