base_table.py

from datetime import datetime

from tangsel.tools import number_only
from tangsel.tools.pbb import FixBerkas, FixAgenda, FixNopelDetail, FixNopel
from sqlalchemy import Column, String, func, DateTime, Integer, Float, BigInteger, Numeric
from sqlalchemy.ext.hybrid import hybrid_property

from .tools import (FixDati2, FixKecamatan, FixKelurahan, FixBlok, FixNop,
                    FixSppt, FixKantor)
from ..models import CommonModel, PbbDBSession


class BasePbb(CommonModel):
    @classmethod
    def callproc(cls, proc_name, binvars=None, db_session=PbbDBSession):
        engine = db_session.get_bind()
        connection = engine.raw_connection()
        cursor = connection.cursor()
        return cursor.callproc(proc_name, binvars)

    @classmethod
    def query(cls, db_session=PbbDBSession):
        return db_session.query(cls)

    # @classmethod
    # def get_list(cls, db_session=PbbDBSession):
    #     return db_session.query(cls.kode,
    #                               cls.nama).all()

    @classmethod
    def count(cls, db_session=PbbDBSession):
        pass

    @classmethod
    def save(cls, values, row=None, db_session=PbbDBSession):
        if not row:
            row = cls()
        row.from_dict(values)
        db_session.add(row)
        db_session.flush()
        return row


class BasePropinsi(BasePbb):
    kd_propinsi = Column(String(2), primary_key=True)

    @hybrid_property
    def kode(self):
        return self.kd_propinsi

    @hybrid_property
    def id(self):
        return self.kd_propinsi

    @classmethod
    def query_pro(cls, kode):
        return cls.query().filter_by(kd_propinsi=kode[:2])

    @classmethod
    def query_id(cls, kode):
        return cls.query_pro(kode)


class BaseDati2(BasePropinsi):
    kd_dati2 = Column(String(2), primary_key=True)

    @hybrid_property
    def kode(self):
        return "{}.{}".format(self.kd_propinsi, self.kd_dati2)

    @hybrid_property
    def id(self):
        return "{}{}".format(self.kd_propinsi, self.kd_dati2)

    @hybrid_property
    def kode_dati2(self):
        return "{}.{}".format(self.kd_propinsi, self.kd_dati2)

    @hybrid_property
    def kode_pro(self):
        return self.kd_propinsi

    @classmethod
    def query_dat(cls, kode, db_session=PbbDBSession):
        key = FixDati2(kode)
        return db_session.query(cls). \
            filter_by(
            kd_propinsi=key['kd_propinsi'],
            kd_dati2=key['kd_dati2'],
        )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_dat(kode, db_session)


class BaseKecamatan(BaseDati2):
    kd_kecamatan = Column(String(3), primary_key=True)

    @hybrid_property
    def kode(self):
        return "{}-{}".format(super().kode, self.kd_kecamatan)

    @hybrid_property
    def id(self):
        return "{}{}".format(super().id, self.kd_kecamatan)

    @hybrid_property
    def kode_dat(self):
        return super().kode_dati2

    @classmethod
    def query_kec(cls, kode, db_session=PbbDBSession):
        key = FixKecamatan(kode)
        return cls.query_dat(kode, db_session). \
            filter_by(
            kd_kecamatan=key['kd_kecamatan'],
        )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_kec(kode, db_session)


class BaseKelurahan(BaseKecamatan):
    kd_kelurahan = Column(String(3), primary_key=True)

    @hybrid_property
    def kode(self):
        return "{}.{}".format(super().kode, self.kd_kelurahan)

    @hybrid_property
    def id(self):
        return "{}{}".format(super().id, self.kd_kelurahan)

    @hybrid_property
    def kode_kec(self):
        return super().kode

    @classmethod
    def query_kel(cls, kode, db_session=PbbDBSession):
        key = FixKelurahan(kode)
        return cls.query_kec(kode, db_session). \
            filter_by(
            kd_kelurahan=key['kd_kelurahan'],
        )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_kel(kode, db_session)


class BaseBlok(BaseKelurahan):
    kd_blok = Column(String(3), primary_key=True)

    @hybrid_property
    def kode(self):
        return "{}-{}".format(super().kode, self.kd_blok)

    @hybrid_property
    def id(self):
        return "{}{}".format(super().id, self.kd_blok)

    @hybrid_property
    def kode_kel(self):
        return super().kode

    @classmethod
    def query_blok(cls, kode, db_session=PbbDBSession):
        key = FixBlok(kode)
        return cls.query_kel(kode, db_session). \
            filter_by(
            kd_blok=key['kd_blok'],
        )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_blok(kode, db_session)


class BaseNop(BaseBlok):
    no_urut = Column(String(4), primary_key=True)
    kd_jns_op = Column(String(1), primary_key=True)

    # self.kode_blok()
    @hybrid_property
    def nop(self):
        return "{}.{}-{}.{}-{}.{}-{}".format(
            self.kd_propinsi, self.kd_dati2, self.kd_kecamatan,
            self.kd_kelurahan, self.kd_blok, self.no_urut, self.kd_jns_op)

    @hybrid_property
    def nop_id(self):
        return "{}{}{}{}{}{}{}".format(
            self.kd_propinsi, self.kd_dati2, self.kd_kecamatan,
            self.kd_kelurahan, self.kd_blok, self.no_urut, self.kd_jns_op)

    @hybrid_property
    def kode_blok(self):
        return super(BaseBlok, self).kode

    @classmethod
    def filter_nop(cls, nop):
        nop = FixNop(nop)
        return (cls.kd_propinsi == nop["kd_propinsi"],
                cls.kd_dati2 == nop["kd_dati2"],
                cls.kd_kecamatan == nop["kd_kecamatan"],
                cls.kd_kelurahan == nop["kd_kelurahan"],
                cls.kd_blok == nop["kd_blok"],
                cls.no_urut == nop["no_urut"],
                cls.kd_jns_op == nop["kd_jns_op"],)

    @classmethod
    def query_nop(cls, kode, db_session=PbbDBSession):
        key = FixNop(kode)
        return cls.query_blok(kode, db_session). \
            filter_by(
            no_urut=key['no_urut'],
            kd_jns_op=key['kd_jns_op'],
        )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_nop(kode, db_session)


class _BaseSppt(BaseNop):
    thn_pajak_sppt = Column(String(4), primary_key=True)

    @hybrid_property
    def kode(self):
        return "{}.{}".format(self.nop, self.thn_pajak_sppt)

    @classmethod
    def query_sppt(cls, kode, db_session=PbbDBSession):
        keys = FixSppt(kode)
        return cls.query_nop(kode, db_session). \
            filter_by(thn_pajak_sppt=keys['thn_pajak_sppt'], )

    @classmethod
    def query_id(cls, kode, db_session=PbbDBSession):
        return cls.query_sppt(kode, db_session)


class BaseKanwil(BasePbb):
    kd_kanwil = Column(String(2), primary_key=True)

    @classmethod
    def query_kanwil(cls, kode, db_session=PbbDBSession):
        return cls.query(db_session).filter_by(kd_kanwil=kode[:2])


class BaseKantor(BaseKanwil):
    kd_kantor = Column(String(2), primary_key=True)

    @hybrid_property
    def kode_kantor(self):
        return self.kd_kanwil + '.' + self.kd_kantor

    @hybrid_property
    def kode(self):
        return self.kode_kantor

    @classmethod
    def query_kantor(cls, kode, db_session=PbbDBSession):
        key = FixKantor(kode)
        return cls.query(db_session). \
            filter_by(
            kd_kanwil=key['kd_kanwil'],
            kd_kantor=key['kd_kantor'],
        )


class BaseDokumen(object):
    kd_kanwil = Column(String(2))
    kd_kantor = Column(String(2))
    jns_dokumen = Column(String(1))
    no_dokumen = Column(String(11))

    @hybrid_property
    def kode_dok(self):
        return "{}.{}.{}.{}".format(self.jns_dokumen, self.no_dokumen[:4],
                                    self.no_dokumen[4:8], self.no_dokumen[8:])

    @hybrid_property
    def id_dok(self):
        return "{}{}{}{}".format(self.kd_kanwil, self.kd_kantor,
                                 self.jns_dokumen, self.no_dokumen)


class BaseSeksi(BasePbb):
    kd_seksi = Column(String(2), primary_key=True)
    nm_seksi = Column(String(75))
    no_srt_seksi = Column(String(2))
    kode_surat_1 = Column(String(5))
    kode_surat_2 = Column(String(5))

    @hybrid_property
    def kode(self):
        return self.kd_seksi

    @hybrid_property
    def nama(self):
        return self.nm_seksi

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        return cls.query(db_session). \
            filter(cls.kd_seksi == id)

    @classmethod
    def query_kode(cls, kode, db_session=PbbDBSession):
        return cls.query_id(kode, db_session)

    @classmethod
    def query_nama(cls, nama, db_session=PbbDBSession):
        return cls.query(db_session).filter_by(nm_seksi=nama)

    @classmethod
    def query_nama_like(cls, nama, db_session=PbbDBSession):
        return cls.query(db_session).filter(cls.nm_seksi.like("%{}%".format(nama)))


class BaseJenisPelayanan(BasePbb):
    kd_jns_pelayanan = Column(String(2), primary_key=True)
    nm_jenis_pelayanan = Column(String(50))

    @hybrid_property
    def kode(self):
        return self.kd_jns_pelayanan

    @hybrid_property
    def nama(self):
        return self.nm_jenis_pelayanan

    @classmethod
    def query_kode(cls, kode, db_session=PbbDBSession):
        return cls.query(db_session).filter_by(kd_jns_pelayanan=kode)

    @classmethod
    def query_nama(cls, nama, db_session=PbbDBSession):
        return cls.query(db_session).filter_by(nm_jenis_pelayanan=nama)

    @classmethod
    def query_nama_like(cls, nama, db_session=PbbDBSession):
        return cls.query(db_session). \
            filter(cls.nm_jenis_pelayanan.like("%{}%".format(nama)))


def filter_nopel(cls, nopel):
    fix_nopel = FixNopel(nopel)
    return (cls.kd_kanwil == fix_nopel['kd_kanwil'],
            cls.kd_kantor == fix_nopel['kd_kantor'],
            cls.thn_pelayanan == fix_nopel['tahun'],
            cls.bundel_pelayanan == fix_nopel['bundel'],
            cls.no_urut_pelayanan == fix_nopel['urut'])


class BasePermohonan(CommonModel):
    kd_kanwil = Column(String(2), primary_key=True)
    kd_kantor = Column(String(2), primary_key=True)
    thn_pelayanan = Column(String(4), primary_key=True)
    bundel_pelayanan = Column(String(4), primary_key=True)
    no_urut_pelayanan = Column(String(3), primary_key=True)

    @hybrid_property
    def nopel(self):
        return self.thn_pelayanan + '.' + self.bundel_pelayanan + \
            '.' + self.no_urut_pelayanan

    @classmethod
    def query(cls, db_session=PbbDBSession):
        return db_session.query(cls)

    @classmethod
    def query_nopel(cls, nopel, db_session=PbbDBSession):
        fix_nopel = FixNopel(nopel)
        return cls.query(db_session=db_session). \
            filter(cls.kd_kanwil == fix_nopel['kd_kanwil'],
                   cls.kd_kantor == fix_nopel['kd_kantor'],
                   cls.thn_pelayanan == fix_nopel['tahun'],
                   cls.bundel_pelayanan == fix_nopel['bundel'],
                   cls.no_urut_pelayanan == fix_nopel['urut'], )

    @classmethod
    def get_by_nopel(cls, r, db_session=PbbDBSession):
        return db_session.query(cls). \
            filter(cls.kd_kanwil == r['kd_kanwil'],
                   cls.kd_kantor == r['kd_kantor'],
                   cls.thn_pelayanan == r['tahun'],
                   cls.bundel_pelayanan == r['bundel'],
                   cls.no_urut_pelayanan == r['urut'], ). \
            first()


class BaseNopMohon(BasePermohonan):
    kd_propinsi_pemohon = Column(String(2), primary_key=True)
    kd_dati2_pemohon = Column(String(2), primary_key=True)
    kd_kecamatan_pemohon = Column(String(3), primary_key=True)
    kd_kelurahan_pemohon = Column(String(3), primary_key=True)
    kd_blok_pemohon = Column(String(3), primary_key=True)
    no_urut_pemohon = Column(String(4), primary_key=True)
    kd_jns_op_pemohon = Column(String(1), primary_key=True)

    @classmethod
    def query_kode(cls, kode, db_session=PbbDBSession):
        fix_nopel_det = FixNopelDetail(kode)
        return cls.query_nopel(kode, db_session). \
            filter(cls.kd_propinsi_pemohon == fix_nopel_det["kd_propinsi"],
                   cls.kd_dati2_pemohon == fix_nopel_det["kd_dati2"],
                   cls.kd_kecamatan_pemohon == fix_nopel_det["kd_kecamatan"],
                   cls.kd_kelurahan_pemohon == fix_nopel_det["kd_kelurahan"],
                   cls.kd_blok_pemohon == fix_nopel_det["kd_blok"],
                   cls.no_urut_pemohon == fix_nopel_det["no_urut"],
                   cls.kd_jns_op_pemohon == fix_nopel_det["kd_jns_op"])

    def filter_nop(self, qry, nop):
        nop = FixNop(nop)
        return qry.filter_by(kd_propinsi_pemohon=nop["kd_propinsi"],
                             kd_dati2_pemohon=nop["kd_dati2"],
                             kd_kecamatan_pemohon=nop["kd_kecamatan"],
                             kd_kelurahan_pemohon=nop["kd_kelurahan"],
                             kd_blok_pemohon=nop["kd_blok"],
                             no_urut_pemohon=nop["no_urut"],
                             kd_jns_op_pemohon=nop["kd_jns_op"], )


class BasePstPermohonan(BasePermohonan):
    no_srt_permohonan = Column(String(30))
    tgl_surat_permohonan = Column(DateTime)
    nama_pemohon = Column(String(30))
    alamat_pemohon = Column(String(40))
    keterangan_pst = Column(String(75))
    catatan_pst = Column(String(75))
    status_kolektif = Column(String(1))
    tgl_terima_dokumen_wp = Column(DateTime)
    tgl_perkiraan_selesai = Column(DateTime)
    nip_penerima = Column(String(18))

    @hybrid_property
    def nopel(self):
        return self.thn_pelayanan + '.' + self.bundel_pelayanan + \
            '.' + self.no_urut_pelayanan

    @classmethod
    def query(cls, db_session=PbbDBSession):
        return db_session.query(cls)

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        return cls.query_nopel(id, db_session)
        # fix_nopel = FixNopel(id)
        # return cls.query(db_session). \
        #     filter(cls.kd_kanwil == fix_nopel['kd_kanwil'],
        #            cls.kd_kantor == fix_nopel['kd_kantor'],
        #            cls.thn_pelayanan == fix_nopel['tahun'],
        #            cls.bundel_pelayanan == fix_nopel['bundel'],
        #            cls.no_urut_pelayanan == fix_nopel['urut'], )

    # @classmethod
    # def query_nopel(cls, id, db_session=PbbDBSession):
    #     return cls.query_id(id, db_session)

    @classmethod
    def query_kode(cls, id, db_session=PbbDBSession):
        return cls.query_id(id, db_session)

    @classmethod
    def get_by_code(cls, code, db_session=PbbDBSession):
        return cls.query_id(code, db_session)

    @classmethod
    def get_new_nopel(cls, kd_kantor, db_session=PbbDBSession):
        thn_pelayanan = datetime.now().strftime('%Y')
        kd_kantor = f"{kd_kantor}.{thn_pelayanan}.0000.000"
        nopel = FixNopel(kd_kantor)
        row = cls.query(db_session). \
            filter(cls.kd_kanwil == nopel['kd_kanwil'],
                   cls.kd_kantor == nopel['kd_kantor'],
                   cls.thn_pelayanan == nopel["tahun"]
                   ). \
            order_by(cls.bundel_pelayanan.desc(), cls.no_urut_pelayanan.desc()). \
            first()
        if row:
            bundel = int(row.bundel_pelayanan)
            urut = int(row.no_urut_pelayanan)
            if urut < 999:
                urut += 1
            else:
                bundel += 1
                urut = 1
        else:
            bundel = 1
            urut = 1
        nopel["bundel"] = str(bundel).zfill(4)
        nopel["urut"] = str(urut).zfill(3)
        return nopel


class BasePstDataOpBaru(BasePermohonan):
    kd_propinsi_pemohon = Column(String(2), primary_key=True)
    kd_dati2_pemohon = Column(String(2), primary_key=True)
    kd_kecamatan_pemohon = Column(String(3), primary_key=True)
    kd_kelurahan_pemohon = Column(String(3), primary_key=True)
    kd_blok_pemohon = Column(String(3), primary_key=True)
    no_urut_pemohon = Column(String(4), primary_key=True)
    kd_jns_op_pemohon = Column(String(1), primary_key=True)
    nama_wp_baru = Column(String(30))
    letak_op_baru = Column(String(35))


class BasePstDetail(BasePermohonan):
    kd_propinsi_pemohon = Column(String(2), primary_key=True)
    kd_dati2_pemohon = Column(String(2), primary_key=True)
    kd_kecamatan_pemohon = Column(String(3), primary_key=True)
    kd_kelurahan_pemohon = Column(String(3), primary_key=True)
    kd_blok_pemohon = Column(String(3), primary_key=True)
    no_urut_pemohon = Column(String(4), primary_key=True)
    kd_jns_op_pemohon = Column(String(1), primary_key=True)
    kd_jns_pelayanan = Column(String(2), primary_key=True)
    thn_pajak_permohonan = Column(String(4), primary_key=True)
    nama_penerima = Column(String(30))
    catatan_penyerahan = Column(String(75))
    # 0 proses 1 selesai 2 diserahkan -1 pending
    status_selesai = Column(Integer)
    tgl_selesai = Column(DateTime)

    @hybrid_property
    def nop_min(self):
        return self.kd_kecamatan_pemohon + '.' + \
            self.kd_kelurahan_pemohon + '-' + self.kd_blok_pemohon + '.' + self.no_urut_pemohon + '.' + \
            self.kd_jns_op_pemohon

    @hybrid_property
    def nop_id(self):
        return self.kd_propinsi_pemohon + '.' + self.kd_dati2_pemohon + '-' + self.kd_kecamatan_pemohon + '.' + \
            self.kd_kelurahan_pemohon + '-' + self.kd_blok_pemohon + '.' + self.no_urut_pemohon + '.' + \
            self.kd_jns_op_pemohon

    @hybrid_property
    def nopel(self):
        return self.thn_pelayanan + '.' + self.bundel_pelayanan + \
            '.' + self.no_urut_pelayanan

    @hybrid_property
    def id(self):
        return self.kd_kanwil + '.' + self.kd_kantor + '.' + self.nopel + '-' + self.nop_id

    @classmethod
    def query(cls, db_session=PbbDBSession):
        return db_session.query(cls)

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        fix_nopel_det = FixNopelDetail(id)
        return cls.query(db_session). \
            filter(cls.kd_kanwil == fix_nopel_det['kd_kanwil'],
                   cls.kd_kantor == fix_nopel_det['kd_kantor'],
                   cls.thn_pelayanan == fix_nopel_det['tahun'],
                   cls.bundel_pelayanan == fix_nopel_det['bundel'],
                   cls.no_urut_pelayanan == fix_nopel_det['urut'],
                   cls.kd_propinsi_pemohon == fix_nopel_det["kd_propinsi"],
                   cls.kd_dati2_pemohon == fix_nopel_det["kd_dati2"],
                   cls.kd_kecamatan_pemohon == fix_nopel_det["kd_kecamatan"],
                   cls.kd_kelurahan_pemohon == fix_nopel_det["kd_kelurahan"],
                   cls.kd_blok_pemohon == fix_nopel_det["kd_blok"],
                   cls.no_urut_pemohon == fix_nopel_det["no_urut"],
                   cls.kd_jns_op_pemohon == fix_nopel_det["kd_jns_op"])

    @classmethod
    def query_nopel(cls, nopel, db_session=PbbDBSession):
        fix_nopel = FixNopel(nopel)
        return cls.query(db_session). \
            filter(cls.kd_kanwil == fix_nopel['kd_kanwil'],
                   cls.kd_kantor == fix_nopel['kd_kantor'],
                   cls.thn_pelayanan == fix_nopel['tahun'],
                   cls.bundel_pelayanan == fix_nopel['bundel'],
                   cls.no_urut_pelayanan == fix_nopel['urut'])

    @classmethod
    def query_kode(cls, nopel, nop, db_session=PbbDBSession):
        fix_nop = FixNop(nop)
        return cls.query_nopel(nopel, db_session). \
            filter(cls.kd_propinsi_pemohon == fix_nop["kd_propinsi"],
                   cls.kd_dati2_pemohon == fix_nop["kd_dati2"],
                   cls.kd_kecamatan_pemohon == fix_nop["kd_kecamatan"],
                   cls.kd_kelurahan_pemohon == fix_nop["kd_kelurahan"],
                   cls.kd_blok_pemohon == fix_nop["kd_blok"],
                   cls.no_urut_pemohon == fix_nop["no_urut"],
                   cls.kd_jns_op_pemohon == fix_nop["kd_jns_op"])

    @classmethod
    def get_by_pelayanan(cls, nopel, db_session=PbbDBSession):
        return cls.query_nopel(nopel, db_session)

    @classmethod
    def get_by_code(cls, code_nopel, code_nop, db_session=PbbDBSession):
        return cls.query_kode(code_nopel, code_nop, db_session)

    @classmethod
    def get_by_code_jns_pelayanan(cls, code_nopel, code_nop, jns_ply, db_session=PbbDBSession):
        return cls.get_by_code(code_nopel, code_nop, db_session). \
            filter(cls.kd_jns_pelayanan == jns_ply)

    @classmethod
    def get_by_nop(cls, code_nop, db_session=PbbDBSession):
        return cls.query_nop(code_nop, db_session)


class BasePstPermohonanPengurangan(BaseNopMohon):
    jns_pengurangan = Column(String(1))
    pct_permohonan_pengurangan = Column(Integer)

    @classmethod
    def query(cls, db_session=PbbDBSession):
        return db_session.query(cls)

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        fixNopelDet = FixNopelDetail(id)
        return cls.query(db_session). \
            filter(cls.kd_kanwil == fixNopelDet['kd_kanwil'],
                   cls.kd_kantor == fixNopelDet['kd_kantor'],
                   cls.thn_pelayanan == fixNopelDet['tahun'],
                   cls.bundel_pelayanan == fixNopelDet['bundel'],
                   cls.no_urut_pelayanan == fixNopelDet['urut'],
                   cls.kd_propinsi_pemohon == fixNopelDet["kd_propinsi"],
                   cls.kd_dati2_pemohon == fixNopelDet["kd_dati2"],
                   cls.kd_kecamatan_pemohon == fixNopelDet["kd_kecamatan"],
                   cls.kd_kelurahan_pemohon == fixNopelDet["kd_kelurahan"],
                   cls.kd_blok_pemohon == fixNopelDet["kd_blok"],
                   cls.no_urut_pemohon == fixNopelDet["no_urut"],
                   cls.kd_jns_op_pemohon == fixNopelDet["kd_jns_op"])

    @classmethod
    def query_nopel(cls, code_nopel, db_session=PbbDBSession):
        fix_nopel = FixNopel(code_nopel)
        return cls.query(db_session). \
            filter(cls.kd_kanwil == fix_nopel['kd_kanwil'],
                   cls.kd_kantor == fix_nopel['kd_kantor'],
                   cls.thn_pelayanan == fix_nopel['tahun'],
                   cls.bundel_pelayanan == fix_nopel['bundel'],
                   cls.no_urut_pelayanan == fix_nopel['urut'])

    @classmethod
    def get_by_pelayanan(cls, code_nopel, db_session=PbbDBSession):
        return cls.query_nopel(code_nopel, db_session)


class BaseBerkasKirim(CommonModel):
    kd_kanwil = Column(String(2), primary_key=True)
    kd_kantor = Column(String(2), primary_key=True)
    thn_pelayanan = Column(String(4), primary_key=True)
    bundel_pelayanan = Column(String(4), primary_key=True)
    no_urut_pelayanan = Column(String(3), primary_key=True)
    kd_propinsi_pemohon = Column(String(2), primary_key=True)
    kd_dati2_pemohon = Column(String(2), primary_key=True)
    kd_kecamatan_pemohon = Column(String(3), primary_key=True)
    kd_kelurahan_pemohon = Column(String(3), primary_key=True)
    kd_blok_pemohon = Column(String(3), primary_key=True)
    no_urut_pemohon = Column(String(4), primary_key=True)
    kd_jns_op_pemohon = Column(String(1), primary_key=True)
    kd_seksi = Column(String(2), primary_key=True)
    thn_agenda_kirim = Column(String(4), primary_key=True)
    no_agenda_kirim = Column(String(30), primary_key=True)
    tgl_kirim = Column(DateTime)
    nip_pengirim_berkas = Column(String(18))

    @classmethod
    def query(cls):
        return PbbDBSession.query(cls)

    @classmethod
    def query_id(cls, id):
        fixBerkas = FixBerkas(id)
        return cls.query().filter(
            cls.kd_kanwil == fixBerkas['kd_kanwil'],
            cls.kd_kantor == fixBerkas['kd_kantor'],
            cls.thn_pelayanan == fixBerkas['tahun'],
            cls.bundel_pelayanan == fixBerkas['bundel'],
            cls.no_urut_pelayanan == fixBerkas['urut'],
            cls.kd_propinsi_pemohon == fixBerkas['kd_propinsi'],
            cls.kd_dati2_pemohon == fixBerkas['kd_dati2'],
            cls.kd_kecamatan_pemohon == fixBerkas['kd_kecamatan'],
            cls.kd_kelurahan_pemohon == fixBerkas['kd_kelurahan'],
            cls.kd_blok_pemohon == fixBerkas['kd_blok'],
            cls.no_urut_pemohon == fixBerkas['no_urut'],
            cls.kd_jns_op_pemohon == fixBerkas['kd_jns_op'],
            cls.kd_seksi == fixBerkas['kd_seksi'],
            cls.thn_agenda_kirim == fixBerkas['thn_agenda_kirim'],
            func.trim(cls.no_agenda_kirim) == fixBerkas['no_agenda_kirim']
        )

    @classmethod
    def query_id_pengirim(cls, id):
        return cls.query().filter(cls.kd_seksi == id)

    @classmethod
    def query_id_agenda(cls, id, asli=None):
        fixAgenda = FixAgenda(id)
        q = cls.query().filter(cls.kd_seksi == fixAgenda['kd_seksi'],
                               cls.thn_agenda_kirim == fixAgenda['thn_agenda_kirim'],
                               func.trim(
                                   cls.no_agenda_kirim) == fixAgenda['no_agenda_kirim']
                               )
        #   jika untuk edit, maka key awal jadi pengecualian
        if asli:
            asli_agenda = FixAgenda(asli)
            q = q.filter(cls.kd_seksi != asli_agenda['kd_seksi'],
                         cls.thn_agenda_kirim != asli_agenda['thn_agenda_kirim'],
                         func.trim(
                             cls.no_agenda_kirim) != asli_agenda['no_agenda_kirim']
                         )

        return q


class BaseBerkasTerima(CommonModel):
    __tablename__ = 'berkas_terima'
    kd_kanwil = Column(String(2), primary_key=True)
    kd_kantor = Column(String(2), primary_key=True)
    thn_pelayanan = Column(String(4), primary_key=True)
    bundel_pelayanan = Column(String(4), primary_key=True)
    no_urut_pelayanan = Column(String(3), primary_key=True)
    kd_propinsi_pemohon = Column(String(2), primary_key=True)
    kd_dati2_pemohon = Column(String(2), primary_key=True)
    kd_kecamatan_pemohon = Column(String(3), primary_key=True)
    kd_kelurahan_pemohon = Column(String(3), primary_key=True)
    kd_blok_pemohon = Column(String(3), primary_key=True)
    no_urut_pemohon = Column(String(4), primary_key=True)
    kd_jns_op_pemohon = Column(String(1), primary_key=True)
    kd_seksi = Column(String(2), primary_key=True)
    thn_agenda_kirim = Column(String(4), primary_key=True)
    no_agenda_kirim = Column(String(30), primary_key=True)
    kd_seksi_terima = Column(String(2))
    tgl_terima = Column(DateTime, nullable=True)
    nip_penerima_berkas = Column(String(18), nullable=True)

    @classmethod
    def query(cls):
        return PbbDBSession.query(cls)

    @classmethod
    def query_id(cls, id):
        fix_berkas = FixBerkas(id)
        return cls.query().filter(
            cls.kd_kanwil == fix_berkas['kd_kanwil'],
            cls.kd_kantor == fix_berkas['kd_kantor'],
            cls.thn_pelayanan == fix_berkas['tahun'],
            cls.bundel_pelayanan == fix_berkas['bundel'],
            cls.no_urut_pelayanan == fix_berkas['urut'],
            cls.kd_propinsi_pemohon == fix_berkas['kd_propinsi'],
            cls.kd_dati2_pemohon == fix_berkas['kd_dati2'],
            cls.kd_kecamatan_pemohon == fix_berkas['kd_kecamatan'],
            cls.kd_kelurahan_pemohon == fix_berkas['kd_kelurahan'],
            cls.kd_blok_pemohon == fix_berkas['kd_blok'],
            cls.no_urut_pemohon == fix_berkas['no_urut'],
            cls.kd_jns_op_pemohon == fix_berkas['kd_jns_op'],
            cls.kd_seksi == fix_berkas['kd_seksi'],
            cls.thn_agenda_kirim == fix_berkas['thn_agenda_kirim'],
            func.trim(cls.no_agenda_kirim) == fix_berkas['no_agenda_kirim']
        )

    @classmethod
    def query_id_penerima(cls, id):
        return cls.query().filter(cls.kd_seksi_terima == id)

    @classmethod
    def query_id_agenda(cls, id):
        fix_agenda = FixAgenda(id)
        return cls.query().filter(cls.kd_seksi == fix_agenda['kd_seksi'],
                                  cls.thn_agenda_kirim == fix_agenda['thn_agenda_kirim'],
                                  func.trim(
                                      cls.no_agenda_kirim) == fix_agenda['no_agenda_kirim']
                                  )


class BaseDatObjekPajak(BaseNop):
    subjek_pajak_id = Column(String(30))
    no_formulir_spop = Column(String(11))
    no_persil = Column(String(5))
    jalan_op = Column(String(30))
    blok_kav_no_op = Column(String(15))
    rw_op = Column(String(2))
    rt_op = Column(String(3))
    kd_status_cabang = Column(Integer)
    kd_status_wp = Column(String(1))
    total_luas_bumi = Column(Float)
    total_luas_bng = Column(Float)
    njop_bumi = Column(Float)
    njop_bng = Column(Float)
    status_peta_op = Column(Integer)
    jns_transaksi_op = Column(String(1))
    tgl_pendataan_op = Column(DateTime)
    nip_pendata = Column(String(18))
    tgl_pemeriksaan_op = Column(DateTime)
    nip_pemeriksa_op = Column(String(18))
    tgl_perekaman_op = Column(DateTime)
    nip_perekam_op = Column(String(18))

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        return cls.query_nop(id, db_session)

    @classmethod
    def query_subjek(cls, subjek_id, db_session=PbbDBSession):
        return cls.query(db_session).filter(cls.subjek_pajak_id.like(subjek_id + '%'))

    @classmethod
    def get_by_nop(cls, p_kode, db_session=PbbDBSession):
        return cls.query_id(p_kode, db_session)

    @classmethod
    def max_no_urut(cls, id, db_session=PbbDBSession):
        pkey = FixNop(id)
        return db_session.query(func.max(cls.no_urut)). \
            filter(cls.kd_propinsi == pkey['kd_propinsi'],
                   cls.kd_dati2 == pkey['kd_dati2'],
                   cls.kd_kecamatan == pkey['kd_kecamatan'],
                   cls.kd_kelurahan == pkey['kd_kelurahan'],
                   cls.kd_blok == pkey['kd_blok'],
                   ).scalar()

    @classmethod
    def add_dict(cls, row_dicted, row=None, db_session=PbbDBSession):
        # if not row:
        # q = cls.query_id(row_dicted['id'])
        # row = q.first()
        if not row:
            row = cls()
        row.from_dict(row_dicted)
        db_session.add(row)
        db_session.flush()
        return row

    @classmethod
    def set_luas_bumi(cls, row_dicted, db_session=PbbDBSession):
        q = cls.query_id(row_dicted['id'])
        row = q.first()
        if row:
            row.total_luas_bumi = row_dicted['total_luas_bumi']
            db_session.add(row)
            db_session.flush()
        return row

    @classmethod
    def set_luas_bng(cls, row_dicted, db_session=PbbDBSession):
        q = cls.query_id(row_dicted['id'])
        row = q.first()
        if row:
            row.total_luas_bng = row_dicted['total_luas_bng']
            db_session.add(row)
            db_session.flush()
        return row


class BaseSppt(_BaseSppt):
    siklus_sppt = Column(Integer)
    kd_kanwil = Column(String(2))
    kd_kantor = Column(String(2))
    kd_tp = Column(String(2))
    nm_wp_sppt = Column(String(30))
    jln_wp_sppt = Column(String(30))
    blok_kav_no_wp_sppt = Column(String(15))
    rw_wp_sppt = Column(String(2))
    rt_wp_sppt = Column(String(3))
    kelurahan_wp_sppt = Column(String(30))
    kota_wp_sppt = Column(String(30))
    kd_pos_wp_sppt = Column(String(5))
    npwp_sppt = Column(String(15))
    no_persil_sppt = Column(String(5))
    kd_kls_tanah = Column(String(3))
    thn_awal_kls_tanah = Column(String(4))
    kd_kls_bng = Column(String(3))
    thn_awal_kls_bng = Column(String(4))
    tgl_jatuh_tempo_sppt = Column(DateTime(timezone=False))
    luas_bumi_sppt = Column(BigInteger)
    luas_bng_sppt = Column(BigInteger)
    njop_bumi_sppt = Column(BigInteger)
    njop_bng_sppt = Column(BigInteger)
    njop_sppt = Column(BigInteger)
    njoptkp_sppt = Column(BigInteger)
    pbb_terhutang_sppt = Column(BigInteger)
    faktor_pengurang_sppt = Column(BigInteger)
    pbb_yg_harus_dibayar_sppt = Column(BigInteger)
    status_pembayaran_sppt = Column(String(1))
    status_tagihan_sppt = Column(String(1))
    status_cetak_sppt = Column(String(1))
    tgl_terbit_sppt = Column(DateTime(timezone=False))
    tgl_cetak_sppt = Column(DateTime(timezone=False))
    nip_pencetak_sppt = Column(String(18))
    denda_status = Column(String(1))
    denda_pbb = Column(Numeric(15, 0))
    njkp_pct = Column(Numeric(4, 3))
    njkp_sppt = Column(Numeric(15, 0))
    tarif_pct = Column(Numeric(4, 3))
    denda_pct = Column(Numeric(4, 3))

    @classmethod
    def query_data(cls, db_session=PbbDBSession):
        return cls.query(db_session)

    @classmethod
    def query_range(cls, awal, akhir, tahun, db_session=PbbDBSession):
        nop = FixNop(awal)
        akhir = number_only(akhir)
        qry = db_session.query(cls.kd_propinsi, cls.kd_dati2,
                               cls.kd_kecamatan, cls.kd_kelurahan, cls.kd_blok,
                               cls.no_urut, cls.kd_jns_op, cls.thn_pajak_sppt) \
            .filter(cls.kd_propinsi == nop["kd_propinsi"],
                    cls.kd_dati2 == nop["kd_dati2"],
                    cls.kd_kecamatan == nop["kd_kecamatan"],
                    cls.kd_kelurahan == nop["kd_kelurahan"],
                    cls.kd_blok == nop["kd_blok"],
                    cls.no_urut.between(nop["no_urut"], akhir[:4]),
                    cls.kd_jns_op == nop["kd_jns_op"],
                    cls.thn_pajak_sppt == tahun)
        return qry

    @classmethod
    def count(cls, p_kode, db_session=PbbDBSession):
        fx_nop = FixNop(p_kode)
        query = db_session.query(func.count(cls.kd_propinsi))
        return query.filter(
            cls.kd_propinsi == fx_nop['kd_propinsi'],
            cls.kd_dati2 == fx_nop['kd_dati2'],
            cls.kd_kecamatan == fx_nop['kd_kecamatan'],
            cls.kd_kelurahan == fx_nop['kd_kelurahan'],
            cls.kd_blok == fx_nop['kd_blok'],
            cls.no_urut == fx_nop['no_urut'],
            cls.kd_jns_op == fx_nop['kd_jns_op']).scalar()

    @classmethod
    def get_by_nop(cls, p_kode):
        return cls.query_nop(p_kode)

    @classmethod
    def get_by_nop_thn(cls, p_kode, p_tahun):
        return cls.query_sppt(p_kode + p_tahun)

    @classmethod
    def query_nop_thn(cls, p_kode, p_tahun):
        return cls.query_sppt(p_kode + p_tahun)

    @classmethod
    def query_by_nop(cls, nop):
        return cls.get_by_nop(nop)

    @classmethod
    def set_status(cls, id, status, db_session):
        row = cls.query_id(id).first()
        if row:
            row.status_pembayaran_sppt = status
            db_session.add(row)
            db_session.flush()
        return row


class BaseDatSubjekPajak(object):
    subjek_pajak_id = Column(String(30), primary_key=True)
    nm_wp = Column(String(30))
    jalan_wp = Column(String(30))
    blok_kav_no_wp = Column(String(15))
    rw_wp = Column(String(2))
    rt_wp = Column(String(3))
    kelurahan_wp = Column(String(30))
    kota_wp = Column(String(30))
    kd_pos_wp = Column(String(5))
    telp_wp = Column(String(20))
    npwp = Column(String(15))
    status_pekerjaan_wp = Column(String(1))

    @classmethod
    def query(cls, dbsession=PbbDBSession):
        return dbsession.query(cls)

    @classmethod
    def query_id(cls, id, db_session=PbbDBSession):
        return cls.query(db_session).filter(func.trim(cls.subjek_pajak_id) == id.strip())

    @classmethod
    def add_dict(cls, row_dicted, id=None, db_session=PbbDBSession):
        row = cls.query_id(id, db_session).first()
        if not row:
            row = cls()
            if not 'status_pekerjaan_wp' in row_dicted:
                row_dicted['status_pekerjaan_wp'] = 5
        row.from_dict(row_dicted)
        db_session.add(row)
        db_session.flush()
        return row


class BaseDatOpBumi(object):
    kd_propinsi = Column(String(2), primary_key=True)
    kd_dati2 = Column(String(2), primary_key=True)
    kd_kecamatan = Column(String(3), primary_key=True)
    kd_kelurahan = Column(String(3), primary_key=True)
    kd_blok = Column(String(3), primary_key=True)
    no_urut = Column(String(4), primary_key=True)
    kd_jns_op = Column(String(1), primary_key=True)
    no_bumi = Column(Integer, primary_key=True)
    kd_znt = Column(String(2))
    luas_bumi = Column(Float)
    jns_bumi = Column(String(1))
    nilai_sistem_bumi = Column(Float)


class BaseDatOpBangunan(object):
    kd_propinsi = Column(String(2), primary_key=True)
    kd_dati2 = Column(String(2), primary_key=True)
    kd_kecamatan = Column(String(3), primary_key=True)
    kd_kelurahan = Column(String(3), primary_key=True)
    kd_blok = Column(String(3), primary_key=True)
    no_urut = Column(String(4), primary_key=True)
    kd_jns_op = Column(String(1), primary_key=True)
    no_bng = Column(Integer, primary_key=True)
    kd_jpb = Column(String(2))
    no_formulir_lspop = Column(String(11))
    thn_dibangun_bng = Column(String(4))  #
    thn_renovasi_bng = Column(String(4))  #
    luas_bng = Column(Float)  #
    jml_lantai_bng = Column(Integer)  #
    kondisi_bng = Column(String(1))  #
    jns_konstruksi_bng = Column(String(1))  #
    jns_atap_bng = Column(String(1))  #
    kd_dinding = Column(String(1))  #
    kd_lantai = Column(String(1))  #
    kd_langit_langit = Column(String(1))  #
    nilai_sistem_bng = Column(Float)
    jns_transaksi_bng = Column(String(1))
    tgl_pendataan_bng = Column(DateTime)
    nip_pendata_bng = Column(String(18))
    tgl_pemeriksaan_bng = Column(DateTime)
    nip_pemeriksa_bng = Column(String(18))
    tgl_perekaman_bng = Column(DateTime)
    nip_perekam_bng = Column(String(18))