airtanah.py

from sqlalchemy import (
    Column,
    Integer,
    String,
    Float,
)
from sqlalchemy.orm import column_property
from sqlalchemy.ext.hybrid import hybrid_property

from opensipkd.models import Base, DefaultModel, NamaModel
schema = 'pad'


class KodeModel(DefaultModel):

    @classmethod
    def query_kode(cls, kode):
        return cls.query().filter(cls.kode == kode)

    @classmethod
    def query_nama(cls, nama):
        return cls.query().filter(cls.nama.ilike(f"{nama}%"))


class PjdlAirtanahJenisManfaat(Base, KodeModel):
    __tablename__ = 'pad_at_jenis_manfaat'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_jenis_pemanfaatan = Column(String(255))
    bobot = Column(Float)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_jenis_pemanfaatan
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_jenis_pemanfaatan.label('Nama'),
                                        cls.bobot.label('Bobot')])


class PjdlAirtanahJenisSumber(Base, KodeModel):
    __tablename__ = 'pad_at_jenis_sumber'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_jenis_sumber_air = Column(String(255))
    nilai_indeks = Column(Float)
    harga_air_baku = Column(Integer)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_jenis_sumber_air

    @classmethod
    def get_nilai(cls, values):
        row = cls.query().filter(
            cls.id == values["air_jenis_sumber_id"],
        ).first()
        return row
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_jenis_sumber_air.label('Nama'),
                                        cls.nilai_indeks.label('Nilai Indeks'),
                                        cls.harga_air_baku.label('Kualitas')])


class PjdlAirtanahKomponenPemulihan(Base, KodeModel):
    __tablename__ = 'pad_at_komponen_pemulihan'
    id = Column(Integer, primary_key=True)
    jenis_manfaat_id = Column(Integer)
    volume_id = Column(Integer)
    nilai_indeks = Column(Float)
    bobot = Column(Float)
    nilai_komponen = Column(Float)
    __table_args__ = dict(schema=schema)

    @classmethod
    def get_nilai(cls, values):
        return cls.query_from(columns=[cls.id, cls.jenis_manfaat_id, cls.volume_id,
                                       cls.nilai_indeks, cls.bobot, cls.nilai_komponen,
                                       PjdlAirtanahVolume.nm_volume,
                                       PjdlAirtanahVolume.batas_min,
                                       PjdlAirtanahVolume.batas_max]). \
            join(PjdlAirtanahVolume, PjdlAirtanahVolume.id == cls.volume_id). \
            filter(cls.jenis_manfaat_id == values["air_manfaat_id"]).\
            order_by(PjdlAirtanahVolume.batas_min)
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[PjdlAirtanahJenisManfaat.nm_jenis_pemanfaatan.label('Jenis Manfaat'),
                                        PjdlAirtanahVolume.nm_volume.label('Volume'),
                                        cls.nilai_indeks.label('Nilai Indeks'),
                                        cls.bobot.label('Bobot'),
                                        cls.nilai_komponen.label('Nilai Komponen')]). \
            outerjoin(PjdlAirtanahJenisManfaat, PjdlAirtanahJenisManfaat.id ==
                      PjdlAirtanahKomponenPemulihan.jenis_manfaat_id).\
            outerjoin(PjdlAirtanahVolume, PjdlAirtanahVolume.id ==
                      PjdlAirtanahKomponenPemulihan.volume_id). \
            order_by(getattr(cls, order_field))

class PjdlAirtanahKualitas(Base, KodeModel):
    __tablename__ = 'pad_at_kualitas'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_kualitas = Column(String(255))
    nilai_indeks = Column(Float)
    kualitas = Column(Integer)
    alternatif = Column(Integer)
    bobot = Column(Float)
    komponen_sda = Column(Float)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_kualitas

    @classmethod
    def get_nilai(cls, values):
        row = cls.query().filter(
            cls.id == values["air_kualitas_id"],
        ).first()
        return row
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_kualitas.label('Nama'),
                                        cls.nilai_indeks.label('Nilai Indeks'),
                                        cls.kualitas.label('Kualitas'),
                                        cls.alternatif.label('Alternatif'),
                                        cls.bobot.label('Bobot'),
                                        cls.komponen_sda.label('Komponen SDA')])


class PjdlAirtanahSumberAlternatif(Base, KodeModel):
    __tablename__ = 'pad_at_sumber_alternatif'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_sumber_alternatif = Column(String(255))
    nilai_indeks = Column(Float)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_sumber_alternatif

    @classmethod
    def get_nilai(cls, values):
        row = cls.query().filter(
            cls.id == values["air_sumber_id"],
        ).first()
        return row
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_sumber_alternatif.label('Nama'),
                                        cls.nilai_indeks.label('Nilai Indeks')])

class PjdlAirtanahVolume(Base, KodeModel):
    __tablename__ = 'pad_at_volume'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_volume = Column(String(255))
    batas_max = Column(Integer)
    batas_min = Column(Integer)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_volume
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_volume.label('Nama'),
                                        cls.batas_min.label('Batas Min'),
                                        cls.batas_max.label('Batas Max')])

class PjdlAirtanahZona(Base, KodeModel):
    __tablename__ = 'pad_at_zona'
    id = Column(Integer, primary_key=True)
    kode = Column(String(3))
    nm_zona = Column(String(255))
    nilai_indeks = Column(Float)
    nilai_komponen_sda = Column(Float)
    nilai_kompensasi_pemulihan = Column(Float)
    __table_args__ = dict(schema=schema)

    @hybrid_property
    def nama(cls):
        return cls.nm_zona

    @classmethod
    def get_nilai(cls, values):
        row = cls.query().filter(
            cls.id == values["air_zona_id"],
        ).first()
        return row
    
    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns =[cls.kode.label('Kode'),
                                        cls.nm_zona.label('Nama'),
                                        cls.nilai_indeks.label('Nilai Indeks'),
                                        cls.nilai_komponen_sda.label('Komponen SDA'),
                                        cls.nilai_kompensasi_pemulihan.label('Nilai Kompensasi Pemulihan')])