Valuelist

Function: ValueList
Type: API Endpoint (POST)

Purpose:
The function retrieves a list of values from a specified table and column in the customer database. The specifics of the request are provided in the request body.

URL:
POST /api/mdm/value_list

Request Headers:

• customer_key (str, required): An encoded string containing the lscode and firmcode for the customer. This must be decoded for database access.

Request Body:

{
    "refTable": "<Table Name>",
    "dataColumn": "<colName>",
    "displayColumn1": "<colName>",
    "displayColumn2": "<colName>",
    "displayColumn3": "<colName>",
    "displayColumn4": "<colName>",
    "displayColumn5": "<colName>",
    "filter": "<filter>"
}

Response Example:

[
    [{"Division Group": "<Division Group>", "division_code": "<division_code>"},
    {"Division Group": "<Division Group>", "division_code": "<division_code>"},
    {"Division Group": "<Division Group>", "division_code": "<division_code>"}]
]

Steps:

1. Customer Key Decoding:
   The function decodes the customer_key from base64 to extract the lscode and firmcode. If decoding fails or the format is incorrect, an error message is returned.

2. Request Validation:
   The function checks if the required fields (refTable, dataColumn, and at least one displayColumn) are present in the request body.

3. Database Connection:
   A connection to the customer-specific database is established using the custCursorConnection function with the decoded lscode and firmcode.

4. Query Execution:
   The function constructs and executes a SQL query to retrieve the values from the specified table and columns. If filters are provided, they are incorporated into the query.

5. Response Construction:
   The query results are formatted into a list of dictionaries and returned as a JSON response.

Error Handling:

• Invalid Customer Key:
  Returns an error if the decoded customer key is empty or incorrectly formatted.
• Request Validation Errors:
  Returns an error if required fields are missing from the request body.
• Database Errors:
  Catches exceptions during query execution and rolls back any transactions, logging the error details.

Response Fields:

• A list of dictionaries where each dictionary contains values from the specified columns.

Error Response Example:

{
    "status": "failed",
    "message": "Invalid Customer Key"
}

{
    "status": "failed",
    "message": "Missing required fields in the request body."
}

{
    "status": "failed",
    "message": {
        "error_type": "OperationalError",
        "message": "Table not found"
    }
}