exceeding_methods_handler.py

from web.helpers.db import Db


class ExceedingMethodsHandler:

    @staticmethod
    def handle():
        sql = """
            SELECT distinct ad.attainmentId,
            ad.stationname || '(' || ad.assessmentlocal_id || ')' as existingexceedingmethod,
            ad.assessmentlocal_id, em.id,
            em.exceedancedescription_id,
            ad.assessmentdata_id,
            false as selected
            FROM
            (
                SELECT e.assessmentdata_id, e.exceedancedescription_id, e.id, ad.assessmentlocal_id,ex.attainment_id
                FROM
                    exceedingmethods e,
                    exceedancedescriptions ex,
                    assessmentdata ad
                WHERE 1=1
                    AND e.assessmentdata_id = ad.id
                    AND e.exceedancedescription_id = ex.id
            ) as em
            RIGHT OUTER JOIN
            (
                SELECT s.name as stationname, a.assessmentlocal_id, a.id as assessmentdata_id, at.id as attainmentId
                FROM
                    assessmentdata a,
                    stations s,
                    sampling_points sp,
                    observing_capabilities oc,
                    attainments at
                WHERE 1=1
                    AND sp.id = a.assessmentlocal_id
                    AND oc.sampling_point_id = sp.id
                    AND sp.station_id = s.id
                    AND at.assessmentregime_id = a.assessmentregime_id
                ) as ad
            ON (em.attainment_id = ad.attainmentId and em.assessmentlocal_id = ad.assessmentlocal_id)
            order by existingexceedingmethod
        """
        exceedingmethods = Db.fetchall(sql) 
        return exceedingmethods