customer_usaha.py

from sqlalchemy import (
    Column, Integer, DateTime, ForeignKey, UniqueConstraint,
    String, SmallInteger, Index, BigInteger, Float,
    Numeric
)
from sqlalchemy import func
from sqlalchemy.schema import FetchedValue
from sqlalchemy.orm import (
    relationship
)

from opensipkd.models.base import DBSession

from . import PAD_TABLE_ARGS, DefaultModel, Base
from . import (
    PjdlKecamatan, PjdlKelurahan, PjdlWp,
    PjdlUsaha, PjdlPajak, PjdlKecamatan, PjdlKelurahan,
    # RkJenisProdukReklame, RkLetakReklame, RkLokasi,
    # RkKelasJalan, RkSudutPandang,
    # AtZona, AtKualitas, AtSumberAlternatif,
    # AtJenisSumber, AtJenisManfaat
)


class PjdlOp(Base, DefaultModel):
    __tablename__ = 'pad_customer_usaha'
    __table_args__ = (
        UniqueConstraint('konterid', 'customer_id', 'usaha_id'),
        Index('pad_customer_usaha_customer_id_usaha_id_id_idx',
              'customer_id', 'usaha_id', 'id'),
        PAD_TABLE_ARGS
    )

    id = Column(BigInteger, primary_key=True)
    konterid = Column(SmallInteger, nullable=False,
                      server_default=FetchedValue())
    reg_date = Column(DateTime)
    customer_id = Column(ForeignKey(PjdlWp.id, ondelete='CASCADE', onupdate='CASCADE'),
                         nullable=False, index=True)
    usaha_id = Column(ForeignKey(
        PjdlUsaha.id, onupdate='CASCADE'), nullable=False)
    so = Column(String(1))
    kecamatan_id = Column(ForeignKey(PjdlKecamatan.id, onupdate='CASCADE'))
    kelurahan_id = Column(ForeignKey(PjdlKelurahan.id, onupdate='CASCADE'))
    notes = Column(String(50))
    enabled = Column(SmallInteger)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    customer_status_id = Column(Integer)
    aktifnotes = Column(String(200))
    tmt = Column(DateTime)
    air_zona_id = Column(Integer)
    air_manfaat_id = Column(Integer)
    def_pajak_id = Column(ForeignKey(PjdlPajak.id, onupdate='CASCADE'))
    # def_pajak_id = Column(Integer)
    opnm = Column(String(100))
    opalamat = Column(String(100))
    latitude = Column(Numeric)
    longitude = Column(Numeric)
    pnama = Column(String(50))
    palamat = Column(String(255))
    pkelurahan = Column(String(25))
    pkecamatan = Column(String(25))
    pkabupaten = Column(String(25))
    ptelp = Column(String(20))
    pkodepos = Column(String(5))
    ijin1 = Column(String(100))
    ijin1no = Column(String(100))
    ijin1tgl = Column(DateTime)
    ijin1tglakhir = Column(DateTime)
    ijin2 = Column(String(100))
    ijin2no = Column(String(100))
    ijin2tgl = Column(DateTime)
    ijin2tglakhir = Column(DateTime)
    ijin3 = Column(String(100))
    ijin3no = Column(String(100))
    ijin3tgl = Column(DateTime)
    ijin3tglakhir = Column(DateTime)
    ijin4 = Column(String(100))
    ijin4no = Column(String(100))
    ijin4tgl = Column(DateTime)
    ijin4tglakhir = Column(DateTime)
    kd_restojmlmeja = Column(Integer)
    kd_restojmlkursi = Column(Integer)
    kd_restojmltamu = Column(Integer)
    jml_pegawai = Column(Integer)
    kd_park_roda2_luas = Column(Integer)
    kd_park_roda2_jumlah = Column(Integer)
    kd_park_roda2_tarif1 = Column(Integer)
    kd_park_roda2_tarif2 = Column(Integer)
    kd_park_roda2_tarif3 = Column(Integer)
    kd_park_roda4_luas = Column(Integer)
    kd_park_roda4_jumlah = Column(Integer)
    kd_park_roda4_tarif1 = Column(Integer)
    kd_park_roda4_tarif2 = Column(Integer)
    kd_park_roda4_tarif3 = Column(Integer)
    kd_park_rodax_luas = Column(Integer)
    kd_park_rodax_jumlah = Column(Integer)
    kd_park_rodax_tarif1 = Column(Integer)
    kd_park_rodax_tarif3 = Column(Integer)
    kd_park_rodax_tarif2 = Column(Integer)

    # reklame
    bahan = Column(String(256))

    status_id = Column(Integer)
    pajak_id = Column(Integer)
    cahaya_id = Column(Integer)
    pasang_id = Column(Integer)

    letak_reklame_id = Column(Integer)
    produk_reklame_id = Column(Integer)
    sudut_pandang_id = Column(Integer)
    lokasi_id = Column(Integer)
    kelas_jalan_id = Column(Integer)

    panjang = Column(Float)
    lebar = Column(Float)
    luas = Column(Float)
    tinggi = Column(Float)
    muka = Column(Integer)
    banyak = Column(Integer)
    njopr = Column(Float)
    nspr = Column(Float)
    nsr = Column(Float)
    # end reklame

    # airtanah
    # air_zona_id = Column(Integer) sudah diatas
    # air_manfaat_id = Column(Integer) sudah diatas
    air_kualitas_id = Column(Integer)
    air_sumber_id = Column(Integer)
    air_jenis_sumber_id = Column(Integer)
    volume = Column(Float)
    volume_progresif = Column(String(50))
    volume_progresif_val = Column(Integer)
    indeks_kp = Column(Float)
    kp = Column(Float)
    fna = Column(Float)
    hab = Column(Float)
    hda = Column(Integer)
    npa = Column(Float)
    daftar_id = Column(Integer)
    # end airtanah

    customer = relationship('PjdlWp', primaryjoin='PjdlOp.customer_id == PjdlWp.id',
                            backref='_customer_usahas')
    kecamatan = relationship('PjdlKecamatan',
                             primaryjoin='PjdlOp.kecamatan_id == PjdlKecamatan.id',
                             backref='_customer_usahas')
    kelurahan = relationship('PjdlKelurahan',
                             primaryjoin='PjdlOp.kelurahan_id == PjdlKelurahan.id',
                             backref='_customer_usahas')
    usaha = relationship('PjdlUsaha', primaryjoin='PjdlOp.usaha_id == PjdlUsaha.id',
                         backref='_customer_usahas')
    pajak = relationship('PjdlPajak', primaryjoin='PjdlOp.def_pajak_id == PjdlPajak.id',
                         backref='_customer_usahas')

    # reklame v 1
    r_panjang = Column(Float)
    r_lebar = Column(Float)
    r_luas = Column(Float)
    r_tinggi = Column(Float)
    r_muka = Column(Integer)
    r_banyak = Column(Integer)

    r_nsr_id = Column(Integer)
    r_jalanklas_id = Column(Integer)
    r_jalan_id = Column(Integer)
    r_lokasi_pasang_id = Column(Integer)
    r_sudut_pandang_id = Column(Integer)

    r_jenis_produk_reklame_id = Column(Integer)
    r_letak_reklame_id = Column(Integer)

    # r_judul = Column(String)
    # r_tarifid = colander.SchemaNode(
    #     colander.Integer(),
    #     widget=widget.SelectWidget(values=tarif_lain()),
    #     title="Tarif Lain"
    # )

    # @classmethod
    # def get_nopd(cls, obyek):
    # query = PdlDBSession.query(
    # func.concat('32.79',
    # func.concat(Customer.pb,
    # func.concat('.',
    # func.concat(func.lpad(Customer.formno, 7, '0'),
    # func.concat('.',
    # func.concat(PjdlKecamatan.kecamatankd,
    # func.concat('.',
    # func.concat(PjdlKelurahan.kelurahankd,
    # func.concat('.',
    # func.concat(func.lpad(Usaha.id, 2, '0'),
    # func.concat('.', func.lpad(cls.konterid, 2, '0')))))))))))
    # ).label('no')
    # ).\
    # join(PjdlKelurahan, PjdlKelurahan.id == cls.kelurahan_id).\
    # join(PjdlKecamatan, PjdlKecamatan.id == cls.kecamatan_id).\
    # join(Usaha, Usaha.id == cls.usaha_id)
    # query = query.filter(cls.id == obyek)
    # query = query.first()
    # query = query.no

    # return query

    @classmethod
    def get_jumlah_op(cls, cid):
        return func.count(cls.id).filter(cls.customer_id == cid).first

    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query_from(columns=[cls.konterid.label('No. OP'), cls.opnm.label('Nama'),
                                       cls.opalamat.label(
            'Alamat'), cls.reg_date.label('Daftar'),
            PjdlWp.customernm.label('Wajib Pajak'),
            PjdlUsaha.usahanm.label(
                                           'Usaha'), cls.so.label('S/O'),
            PjdlKecamatan.kecamatannm.label(
            'Kecamatan'),
            PjdlKelurahan.kelurahannm.label(
                                           'Kelurahan'),
            cls.pnama.label('Pengelola'),
            cls.ptelp.label('Telp'),
            cls.enabled.label('Status')]). \
            outerjoin(PjdlWp, PjdlWp.id == cls.customer_id). \
            outerjoin(PjdlKecamatan, PjdlKecamatan.id == cls.kecamatan_id). \
            outerjoin(PjdlKelurahan, PjdlKelurahan.id == cls.kelurahan_id). \
            outerjoin(PjdlUsaha, PjdlUsaha.id == cls.usaha_id). \
            order_by(getattr(cls, order_field))

    @classmethod
    def query_pajak(cls, customer_usaha_id=None):
        qry = DBSession.query(func.coalesce(cls.npa, 0))
        if customer_usaha_id is not None:
            qry = qry.filter(cls.id == customer_usaha_id).scalar()
        else:
            qry = 0
        return qry

    @classmethod
    def query_op_location(cls, usaha_id=None):
        return cls.query_from(columns=[cls.id,
                                       func.pad.get_npwpd(
                                           cls.customer_id, True).label('npwpd'),
                                       PjdlWp.customernm.label('wpnm'),
                                       PjdlWp.alamat.label('wpalamat'),
                                       func.pad.get_nopd(
                                           cls.id, True).label('nopd'),
                                       cls.opnm, cls.opalamat,
                                       PjdlPajak.pajaknm.label('opjenis'),
                                       cls.latitude, cls.longitude]). \
            outerjoin(PjdlWp, PjdlWp.id == cls.customer_id). \
            outerjoin(PjdlPajak, PjdlPajak.id == cls.def_pajak_id). \
            filter(cls.latitude != '0' and cls.longitude != '0' and cls.usaha_id == usaha_id).\
            order_by(cls.id)


class PjdlOpHotel(Base, DefaultModel):
    __tablename__ = 'pad_customer_usaha_kd_det'
    __table_args__ = (
        PAD_TABLE_ARGS
    )
    id = Column(BigInteger, primary_key=True)
    customer_usaha_id = Column(
        Integer, ForeignKey(PjdlOp.id), nullable=False, )
    nourut = Column(Integer, nullable=False)
    notes = Column(String(50))
    tarif = Column(Float, nullable=False)
    kamar = Column(Integer, nullable=False)
    volume = Column(Integer, nullable=False)

    @classmethod
    def get_next_urut(cls, op_id):
        row = cls.query_from(columns=[func.max(cls.nourut)]). \
            filter(cls.customer_usaha_id == op_id).scalar()
        return row and row+1 or None

    @classmethod
    def query_register(cls, order_field="id"):
        return cls.query().add_columns(cls.nourut.label('no'), cls.notes.label('Uraian'),
                                       cls.kamar, cls.volume). \
            order_by(getattr(cls, order_field))

# class CustomerUsahaAt(Base, DefaultModel):
#     __tablename__ = 'pad_customer_usaha_at'
#     __table_args__ = PAD_TABLE_ARGS
#
#     id = Column(BigInteger, primary_key=True)
#     customer_usaha_id = Column(ForeignKey(CustomerUsaha.id, ondelete='CASCADE', onupdate='CASCADE'))
#     alamat = Column(String(255))
#     kecamatan_id = Column(ForeignKey(PjdlKecamatan.id, onupdate='CASCADE'))
#     kelurahan_id = Column(ForeignKey(PjdlKelurahan.id, onupdate='CASCADE'))
#     titik_ke = Column(Integer)
#     zona_id = Column(ForeignKey(AtZona.id, onupdate='CASCADE'))
#     kualitas_id = Column(ForeignKey(AtKualitas.id, onupdate='CASCADE'))
#     sumber_alternatif_id = Column(ForeignKey(AtSumberAlternatif.id, onupdate='CASCADE'))
#     jenis_sumber_id = Column(ForeignKey(AtJenisSumber.id, onupdate='CASCADE'))
#     manfaat_id = Column(ForeignKey(AtJenisManfaat.id, onupdate='CASCADE'))
#     volume = Column(Integer)
#     ksda = Column(Float(53))
#     volume_progresif = Column(String(50))
#     volume_progresif_val = Column(Integer)
#     indeks_kp = Column(Float(53))
#     kp = Column(Float(53))
#     fna = Column(Float(53))
#     hab = Column(Float(53))
#     hda = Column(BigInteger)
#     npa = Column(BigInteger)
#     penggunaan_id = Column(ForeignKey('pad.pad_at_penggunaan.id'))
#     volume_id = Column(ForeignKey('pad.pad_at_volume.id'))
#     jenis_sumber = relationship('AtJenisSumber')
#     kecamatan = relationship('PjdlKecamatan')
#     kelurahan = relationship('PjdlKelurahan')
#     kualitas = relationship('AtKualitas')
#     manfaat = relationship('AtJenisManfaat')
#     customer_usaha = relationship('CustomerUsaha')
#     sumber_alternatif = relationship('AtSumberAlternatif')
#     zona = relationship('AtZona')
#     no_izin = Column(String(255))
#     tanggal_izin = Column(DateTime)
#     status = Column(SmallInteger)
#     tanggal_pencatatan = Column(DateTime)
#
#     at_penggunaan = relationship('AtPenggunaan')
#     at_volume = relationship('AtVolume')
#
#
# class CustomerUsahaRk(Base, CommonModel):
#     __tablename__ = 'pad_customer_usaha_rk'
#     __table_args__ = PAD_TABLE_ARGS
#
#     id = Column(BigInteger, primary_key=True)
#     customer_usaha_id = Column(ForeignKey(CustomerUsaha.id, ondelete='CASCADE', onupdate='CASCADE'))
#     reklame_ke = Column(Integer)
#     alamat = Column(String(255))
#     kecamatan_id = Column(ForeignKey(PjdlKecamatan.id, onupdate='CASCADE'))
#     kelurahan_id = Column(ForeignKey(PjdlKelurahan.id, onupdate='CASCADE'))
#     pajak_id = Column(ForeignKey(Pajak.id, onupdate='CASCADE'))
#     jenis_produk_reklame_id = Column(ForeignKey(RkJenisProdukReklame.id, onupdate='CASCADE'))
#     letak_reklame_id = Column(ForeignKey(RkLetakReklame.id, onupdate='CASCADE'))
#     lokasi_id = Column(ForeignKey(RkLokasi.id, onupdate='CASCADE'))
#     kelas_jalan_id = Column(ForeignKey(RkKelasJalan.id, onupdate='CASCADE'))
#     sudut_pandang_id = Column(ForeignKey(RkSudutPandang.id, onupdate='CASCADE'))
#     lokasi_nilai = Column(Float(53))
#     kelas_jalan_nilai = Column(Float(53))
#     sudut_pandang_nilai = Column(Float(53))
#     satuan_nilai_strategis = Column(Float(53))
#     panjang = Column(Float(53))
#     lebar = Column(Float(53))
#     tinggi = Column(Float(53))
#     muka = Column(Float(53))
#     banyak = Column(Float(53))
#     luas = Column(Float(53))
#     njopr = Column(BigInteger)
#     nspr = Column(BigInteger)
#     nsr = Column(BigInteger)
#     tinggi_nilai = Column(Float(53))
#     koordinat = Column(String(255))
#     status_rk = Column(Integer)
#
#     jenis_produk_reklame = relationship('RkJenisProdukReklame')
#     kecamatan = relationship('PjdlKecamatan')
#     kelas_jalan = relationship('RkKelasJalan')
#     kelurahan = relationship('PjdlKelurahan')
#     letak_reklame = relationship('RkLetakReklame')
#     lokasi = relationship('RkLokasi')
#     pajak = relationship('Pajak')
#     customer_usaha = relationship('CustomerUsaha')
#     sudut_pandang = relationship('RkSudutPandang')