As a dev, I want to retrieve data with filtered option.

[SamuelPelletierEvraire]

Description

As a developer, I want to be able to retrieve inspection data from the database with multiple filtering options.

Context

Currently, our backend system lacks the functionality to fetch all inspection records from the database with flexible filtering. The application needs an API endpoint capable of returning filtered inspection data based on various business and technical criteria. This capability is crucial for efficiently managing inspection records, improving data access speed, and serving specific queries related to inspection data.

Problem Statement

The existing backend does not support retrieving inspection records with filtering based on fields such as fertiliser name, registration number, location, inspection dates, inspector name, and organization details (name, address, phone number). This limitation makes it challenging for users to access specific subsets of data, hindering user experience and potentially impacting system performance due to the need to transfer large datasets without filtering server-side.

Acceptance Criteria

• Implement a method in the backend that queries the inspection database with support for filtering by:

• Fertiliser name

• Registration number

• Lot number

• Location

• Inspector name

• Organization name

• Organization address

• Organization phone number

• Before date this one is boolean that when activate search before the given date

• After dates this one is boolean that when activate search after the given date

• If after and before are false search for the specific date.

• Ensure the method returns the expected structure as defined in the InspectionData model.

• Incorporate unit tests to validate the filtering logic and ensure the method behaves correctly under various conditions.

** Additional Information (Optional) **

Consider implementing pagination alongside filtering to handle potentially large datasets more efficiently. This would further enhance the performance and user-friendliness of the data retrieval process

Here some code for helping understanding the problem:

def get_all_verified_inspections_filtered(
    cursor: Cursor,
    fertiliser_name: str = None,
    registration_number: str = None,
    lot_number: str = None,
    location: str = None,
    inspector_name: str = None,
    organisation_name: str = None,
    organisation_address: str = None,
    organisation_phone_number: str = None,
    before_date: bool = false,
    after_date: bool = false,
):
    """
    Retrieves all verified inspections records filtered by the specified criteria.

    Args:
        cursor (Cursor): The database cursor.
        fertiliser_name (str, optional): Filter by fertilizer name.
        registration_number (str, optional): Filter by registration number.
        lot_number (str, optional): Filter by lot number.
        location (str, optional): Filter by location.
        inspector_name (str, optional): Filter by inspector name.
        organisation_name (str, optional): Filter by organization name.
        organisation_address (str, optional): Filter by organization address.
        organisation_phone_number (str, optional): Filter by organization phone number.
        before_date (str, optional): Get inspections before this date (YYYY-MM-DD). 
        after_date (str, optional): Get inspections after this date (YYYY-MM-DD).

    Returns:
        list[dict]: A list of dictionaries containing filtered inspection data.
    """
    query = """
        SELECT
            inspection.id, inspection.upload_date, inspection.updated_at, inspection.sample_id,
            inspection.picture_set_id, inspection.label_info_id, inspection.company_info_id,
            label_info.product_name AS fertiliser_name,
            label_info.registration_number,
            label_info.lot_number,
            inspection.location,
            inspection.inspector_name,
            company_info.name AS organisation_name,
            company_info.address AS organisation_address,
            company_info.phone AS organisation_phone_number,
            inspection.inspection_date AS date_of_inspection
        FROM inspection
        JOIN label_information AS label_info ON inspection.label_info_id = label_info.id
        JOIN organization_information AS company_info ON label_info.company_info_id = company_info.id
        WHERE inspection.verified = TRUE
    """

    params = []

    if fertiliser_name:
        query += " AND label_info.product_name ILIKE %s"
        params.append(f"%{fertiliser_name}%")

    if registration_number:
        query += " AND label_info.registration_number = %s"
        params.append(registration_number)

    if lot_number:
        query += " AND label_info.lot_number = %s"
        params.append(lot_number)

    if location:
        query += " AND inspection.location ILIKE %s"
        params.append(f"%{location}%")

    if inspector_name:
        query += " AND inspection.inspector_name ILIKE %s"
        params.append(f"%{inspector_name}%")

    if organisation_name:
        query += " AND company_info.name ILIKE %s"
        params.append(f"%{organisation_name}%")

    if organisation_address:
        query += " AND company_info.address ILIKE %s"
        params.append(f"%{organisation_address}%")

    if organisation_phone_number:
        query += " AND company_info.phone = %s"
        params.append(organisation_phone_number)

    if before_date:
        query += " AND inspection.inspection_date < %s"
        params.append(before_date)

    if after_date:
        query += " AND inspection.inspection_date > %s"
        params.append(after_date)

    try:
        cursor.execute(query, tuple(params))
        inspections = cursor.fetchall()

        return [
            {
                "id": entry[0],
                "uploadDate": entry[1],
                "updatedAt": entry[2],
                "sampleId": entry[3],
                "pictureSetId": entry[4],
                "labelInfoId": entry[5],
                "companyInfoId": entry[6],
                "fertiliserName": entry[7],
                "registrationNumber": entry[8],
                "lotNumber": entry[9],
                "location": entry[10],
                "inspectorName": entry[11],
                "organisationName": entry[12],
                "organisationAddress": entry[13],
                "organisationPhoneNumber": entry[14],
                "dateOfInspection": entry[15],
            }
            for entry in inspections
        ]
    except (Error, DatabaseError, OperationalError) as e:
        raise InspectionRetrievalError(f"Error retrieving inspections: {str(e)}") from e

[snakedye]

@Francois-Werbrouck are the functions required for this already present in the database?

[Francois-Werbrouck]

@Francois-Werbrouck are the functions required for this already present in the database?

They are on ai-cfia/ailab-datastore#258 under the issue ai-cfia/ailab-datastore#233

[Francois-Werbrouck]

I'm going to drop the high level function used here : https://github.com/ai-cfia/ailab-datastore/blob/e06e859d119e9a494872fe3e59ddc44b1aff6d37/fertiscan/__init__.py#L606

def search_inspection(
    cursor: Cursor,
    fertilizer_name: str,
    reg_number: str,
    lot_number: str,
    inspector_name: str,
    lower_bound_date: datetime,
    upper_bound_date: datetime,
    organization_name: str,
    organization_address: str,
    organization_phone: str,
):
    """
    This function search all the verified inspection based on the given parameters
    Parameters:
    - cursor (Cursor): The cursor object to interact with the database.
    - fertilizer_name (str): The name of the fertilizer.
    - reg_number (str): The registration number of the fertilizer.
    - lot_number (str): The lot number of the fertilizer.
    - inspector_name (str): The name of the inspector. (Not used at the moment)
    - lower_bound_date (str): The lower bound date of the inspection.
    - upper_bound_date (str): The upper bound date of the inspection.
    - organization_name (str): The name of the organization.
    - organization_address (str): The address of the organization.
    - organization_phone (str): The phone number of the organization.

    Returns:
    - List of inspection tuple.
    [
        inspection.id,
        inspection.verified
        inspection.upload_date,
        inspection.updated_at,
        inspection.inspector_id
        inspection.label_info_id,
        inspection.container_id,
        inspection.folder_id,
        inspection.inspection_comment,
        inspection.verified_date,
        label_info.product_name,
        organization_info.id, (main_contact_id)
        organization_info.name,
        organization_info.phone_number,
        organization_info.address,
        label_info.is_minimal,
        label_info.record_keeping,
        registration_number.identifiers, (list of reg numbers)
    ]
    """