Untitled

import pandas
from sqlalchemy import select

from property_rater.database.session import get_engine
from property_rater.valuations_tool.models import Property, OccConStateQuartile,  OccQuartile, Country, State, \
    AirOccupancy, AirConstruction


#  #  loading up sample properties (for manual testing of Helpers' methods):
sample_quote_id = "643FD5E1-2CBB-45F3-9859-F2482A8ED3E5"
statement = select(Property).where(Property.quote_id == sample_quote_id).limit(10)
dataset_properties = pandas.read_sql_query(sql=statement, con=get_engine().connect())


#  #  loading up occ_con_state_quartile table populated with look-ups (for manual testing of Helpers' methods):
looked_up_occ_con_state_quartile = OccConStateQuartile.__table__.join(Country.__table__) \
                                                                .join(State.__table__) \
                                                                .join(AirConstruction.__table__) \
                                                                .join(AirOccupancy.__table__)
statement = select(Country.alpha2_code,
                   State.state_code,
                   AirOccupancy.air_occ_code,
                   AirConstruction.construction_category,
                   OccConStateQuartile.q2) \
            .select_from(looked_up_occ_con_state_quartile)
dataset_occ_con_state_quartile = pandas.read_sql_query(sql=statement, con=get_engine().connect())


#  #  loading up occ_quartile table populated with look-ups (for manual testing of Helpers' methods):
looked_up_occ_quartile = OccQuartile.__table__.join(Country.__table__) \
                                              .join(AirOccupancy.__table__)
statement = select(Country.alpha2_code,
                   AirOccupancy.air_occ_code,
                   OccQuartile.q2) \
           .select_from(looked_up_occ_quartile)
dataset_occ_quartile = pandas.read_sql_query(sql=statement, con=get_engine().connect())


class Helpers:

    @staticmethod
    def get_suggested_tiv_per_square_foot(ref_occ_con_state_quartile: pandas.DataFrame,
                                          ref_occ_quartile: pandas.DataFrame,
                                          property_object: pandas.Series):
        suggested_tiv_per_square_foot = None

        try:
            suggested_tiv_per_square_foot = ref_occ_con_state_quartile[
                (ref_occ_con_state_quartile['alpha2_code'] == property_object['country']) &
                (ref_occ_con_state_quartile['state_code'] == property_object['state_code']) &
                (ref_occ_con_state_quartile['air_occ_code'] == property_object['occupancy_type']) &
                (ref_occ_con_state_quartile['construction_category'] == property_object['air_construction'])
            ].iloc[0]['q2']
        except IndexError:
            try:
                suggested_tiv_per_square_foot = ref_occ_quartile[
                    (ref_occ_quartile['alpha2_code'] == property_object['country']) &
                    (ref_occ_quartile['air_occ_code'] == property_object['occupancy_type'])
                ].iloc[0]['q2']
            except IndexError:
                pass

        return suggested_tiv_per_square_foot


#  #  for manual testing of Helpers' methods:
print(dataset_properties)
print(dataset_occ_con_state_quartile)
print(dataset_occ_quartile)

for _ in range(len(dataset_properties)):
    print(Helpers.get_suggested_tiv_per_square_foot(ref_occ_con_state_quartile=dataset_occ_con_state_quartile,
                                                    ref_occ_quartile=dataset_occ_quartile,
                                                    property_object=dataset_properties.iloc[_]))