exceedance_descriptions_handler.py

from web.helpers.db import Db


class ExceedanceDescriptionsHandler:

    @staticmethod
    def handle():
        sql = """
            SELECT ed.id, 
            ed.attainment_id, 
            ed.exceedances, 
            ed.excedance_type, ed.excedance_type_name,
            ed.max_value, 
            ed.exceedancedescription_element, ed.exceedancedescription_element_name, 
		    at.id as adjustment_type, at.label as adjustment_type_label, 
            ed.surface_area::INTEGER, 
            ed.exposed_population, 
            ed.population_reference_year, 
            ed.vegetation_area::INTEGER,
            ac.id as area_classification, ac.label as area_classification_label,
            er.id as exceedance_reason, er.label as exceedance_reason_label,
            ed.other_exceedance_reason,
            ed.adjustment_source as adjustment_source_label,
            ed.modelassessmentmetadata
	        FROM			
            (
                 SELECT ed.id,
                        at.id as attainment_id,
                        ed.exceedances, 
				        et.id::INTEGER as excedance_type, 
                        et.name as excedance_type_name, 
                        ed.max_value::INTEGER, 
                        ee.id::INTEGER as exceedancedescription_element,
                        ee.name as exceedancedescription_element_name,
                        ed.adjustment_type,
                        ed.surface_area::INTEGER, 
                        ed.exposed_population, 
                        ed.population_reference_year, 
                        ed.vegetation_area::INTEGER,
                        ed.area_classification,
                        ed.exceedance_reason, 
                        ed.other_exceedance_reason,
                        ed.adjustment_source,
                        ed.modelassessmentmetadata
                FROM 
                        exceedancedescriptions ed
						LEFT OUTER JOIN eea_exceedancetype et ON ed.excedance_type = et.id
						LEFT OUTER JOIN eea_exceedancedescription ee ON ed.exceedancedescription_element = ee.id,
                        attainments at
                WHERE ed.attainment_id =  at.id
            ) as ed
                    
                LEFT OUTER JOIN (SELECT id,label from eea_adjustmenttypes) as at ON at.id = ed.adjustment_type
                LEFT OUTER JOIN (SELECT id,label from eea_areaclassifications) as ac ON ac.id = ed.area_classification
                LEFT OUTER JOIN (SELECT id,label from eea_exceedancereason) as er ON er.id = ed.exceedance_reason
                LEFT OUTER JOIN	(SELECT id, uri as adjustment_source FROM eea_adjustmentsourcetype) AS es ON es.adjustment_source = ed.id

            ORDER by ed.id
        """
        exceedancedescriptions = Db.fetchall(sql)
        return exceedancedescriptions