models.py

from datetime import datetime
from sqlalchemy import (
    Column,
    String,
    Integer,
    Date,
    DateTime,
    Time,
    Text,
    Float,
    ForeignKey,
    ForeignKeyConstraint,
    UniqueConstraint,
    )
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Provinsi(Base):
    __tablename__ = 'ref_propinsi'
    kd_propinsi = Column(String(2), primary_key=True)
    nm_propinsi = Column(String(30), nullable=False)
    __table_args__ = dict(schema='pbb')


class Kabupaten(Base):
    __tablename__ = 'ref_dati2'
    kd_propinsi = Column(
        String(2), ForeignKey(Provinsi.kd_propinsi), primary_key=True)
    kd_dati2 = Column(String(2), primary_key=True)
    nm_dati2 = Column(String(30), nullable=False)
    __table_args__ = dict(schema='pbb')


class Kecamatan(Base):
    __tablename__ = 'ref_kecamatan'
    kd_propinsi = Column(String(2), primary_key=True)
    kd_dati2 = Column(String(2), primary_key=True)
    kd_kecamatan = Column(String(3), primary_key=True)
    nm_kecamatan = Column(String(30), nullable=False)
    __table_args__ = (
        ForeignKeyConstraint([
            kd_propinsi, kd_dati2],
            [Kabupaten.kd_propinsi, Kabupaten.kd_dati2]),
        dict(schema='pbb'))


class Kelurahan(Base):
    __tablename__ = 'ref_kelurahan'
    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_sektor = Column(String(2), nullable=False)
    nm_kelurahan = Column(String(30), nullable=False)
    no_kelurahan = Column(Integer)
    kd_pos_kelurahan = Column(String(5))
    __table_args__ = (
        ForeignKeyConstraint(
            [kd_propinsi, kd_dati2, kd_kecamatan],
            [Kecamatan.kd_propinsi, Kecamatan.kd_dati2,
             Kecamatan.kd_kecamatan]),
        dict(schema='pbb'))


class Customer(Base):
    __tablename__ = 'bphtb_ppat'
    id = Column(Integer, primary_key=True)
    kode = Column(String(6), unique=True)
    nama = Column(String(50))
    alamat = Column(String(150))
    kelurahan = Column(String(50))
    kecamatan = Column(String(50))
    kota = Column(String(50))
    wilayah_kerja = Column(String(50))
    kd_wilayah = Column(String(4))
    no_telp = Column(String(50))
    no_fax = Column(String(50))
    no_sk = Column(String(50), unique=True)
    tgl_sk = Column(Date)
    create_uid = Column(String(20))
    update_uid = Column(String(20))
    created = Column(DateTime)
    updated = Column(DateTime)
    npwp = Column(String(20))
    pejabat_id = Column(Integer)
    __table_args__ = dict(schema='bphtb')


class Perolehan(Base):
    __tablename__ = 'bphtb_perolehan'
    id = Column(Integer, primary_key=True)
    nama = Column(String(100), nullable=False)
    npoptkp = Column(Integer, nullable=False)
    pengurang = Column(Integer)
    singkatan = Column(String(20))
    __table_args__ = dict(schema='bphtb')


class Invoice(Base):
    __tablename__ = 'bphtb_sspd'
    id = Column(Integer, primary_key=True)
    tahun = Column(Integer, nullable=False)
    kode = Column(String(1), nullable=False)
    no_sspd = Column(Integer, nullable=False)
    ppat_id = Column(Integer, ForeignKey(Customer.id))
    wp_nama = Column(String(50), nullable=False)
    wp_npwp = Column(String(50), nullable=False)
    wp_alamat = Column(String(100), nullable=False)
    wp_blok_kav = Column(String(100), nullable=False)
    wp_kelurahan = Column(String(30), nullable=False)
    wp_rt = Column(String(3), nullable=False)
    wp_rw = Column(String(3), nullable=False)
    wp_kecamatan = Column(String(30), nullable=False)
    wp_kota = Column(String(30), nullable=False)
    wp_provinsi = Column(String(30), nullable=False)
    wp_identitas = Column(String(50), nullable=False)
    wp_identitaskd = Column(String(50))
    tgl_transaksi = Column(Date, nullable=False)
    kd_propinsi = Column(String(2), nullable=False)
    kd_dati2 = Column(String(2), nullable=False)
    kd_kecamatan = Column(String(3), nullable=False)
    kd_kelurahan = Column(String(3), nullable=False)
    kd_blok = Column(String(3), nullable=False)
    no_urut = Column(String(4), nullable=False)
    kd_jns_op = Column(String(1), nullable=False)
    thn_pajak_sppt = Column(String(4), nullable=False)
    op_alamat = Column(String(100), nullable=False)
    op_blok_kav = Column(String(100), nullable=False)
    op_rt = Column(String(3), nullable=False)
    op_rw = Column(String(3), nullable=False)
    bumi_luas = Column(Integer, nullable=False)
    bumi_njop = Column(Integer, nullable=False)
    bng_luas = Column(Integer, nullable=False)
    bng_njop = Column(Integer, nullable=False)
    no_sertifikat = Column(Text)
    njop = Column(Integer, nullable=False)
    perolehan_id = Column(Integer, ForeignKey(Perolehan.id), nullable=False)
    npop = Column(Integer, nullable=False)
    npoptkp = Column(Integer, nullable=False)
    tarif = Column(Float, nullable=False)
    terhutang = Column(Integer, nullable=False)
    bagian = Column(Integer, nullable=False)
    pembagi = Column(Integer, nullable=False)
    tarif_pengurang = Column(Integer, nullable=False)
    pengurang = Column(Integer, nullable=False)
    bphtb_sudah_dibayarkan = Column(Integer, nullable=False)
    denda = Column(Integer, nullable=False)
    restitusi = Column(Integer, nullable=False)
    bphtb_harus_dibayarkan = Column(Integer, nullable=False)
    status_pembayaran = Column(Integer, nullable=False)
    dasar_id = Column(Integer, nullable=False)
    create_uid = Column(String(20))
    update_uid = Column(String(20))
    created = Column(DateTime, nullable=False)
    updated = Column(DateTime, nullable=False)
    header_id = Column(Integer, nullable=False)
    tgl_print = Column(DateTime)
    tgl_approval = Column(DateTime)
    file1 = Column(String(150))
    file2 = Column(String(150))
    file3 = Column(String(150))
    file4 = Column(String(150))
    file5 = Column(String(150))
    wp_kdpos = Column(String(5))
    file6 = Column(String(150))
    file7 = Column(String(150))
    file8 = Column(String(150))
    file9 = Column(String(150))
    file10 = Column(String(150))
    keterangan = Column(String(100))
    status_daftar = Column(Integer)
    persen_pengurang_sendiri = Column(Integer)
    pp_nomor_pengurang_sendiri = Column(String(50))
    no_ajb = Column(String(50))
    tgl_ajb = Column(DateTime)
    wp_nama_asal = Column(String(50))
    jml_pph = Column(Float)
    tgl_pph = Column(DateTime)
    posted = Column(Integer)
    pos_tp_id = Column(Integer)
    status_validasi = Column(Integer)
    status_bpn = Column(Integer)
    tgl_jatuh_tempo = Column(DateTime)
    verifikasi_uid = Column(String(20))
    verifikasi_date = Column(DateTime)
    pbb_nop = Column(String(24))
    verifikasi_bphtb_uid = Column(String(20))
    verifikasi_bphtb_date = Column(DateTime)
    hasil_penelitian = Column(String(20))
    no_sk = Column(String(20))
    pengurangan_sk = Column(String(20))
    pengurangan_jatuh_tempo_tgl = Column(DateTime)
    pengurangan_sk_tgl = Column(DateTime)
    ketetapan_no = Column(String(20))
    ketetapan_tgl = Column(DateTime)
    ketetapan_atas_sspd_no = Column(String(20))
    ketetapan_jatuh_tempo_tgl = Column(DateTime)
    pembayaran_ke = Column(Integer, nullable=False)
    mutasi_penuh = Column(Integer)
    npopkp = Column(Integer)
    harga_transaksi = Column(Integer)
    npoptkp_sudah_didapat = Column(Integer)
    wp_handphone = Column(String(15))
    wp_npwp_asal = Column(String(50))
    bng_luas_beban = Column(Integer, nullable=False)
    bng_njop_beban = Column(Integer, nullable=False)
    bumi_luas_beban = Column(Integer, nullable=False)
    bumi_njop_beban = Column(Integer, nullable=False)
    bphtb_sudah_dibayarkan_tgl = Column(Date)
    wp_identitas_asal = Column(String(25))
    wp_identitaskd_asal = Column(String(25))
    wp_alamat_asal = Column(String(25))
    wp_blok_kav_asal = Column(String(25))
    wp_rt_asal = Column(String(3))
    wp_rw_asal = Column(String(3))
    wp_provinsi_asal = Column(String(30))
    wp_kota_asal = Column(String(30))
    wp_kecamatan_asal = Column(String(30))
    wp_kelurahan_asal = Column(String(30))
    wp_kdpos_asal = Column(String(30))
    wp_status_hak_id = Column(Integer)
    no_transaksi = Column(String(50))
    no_sequence = Column(String(50))
    __table_args__ = (
        UniqueConstraint('tahun', 'kode', 'no_sspd'),
        dict(schema='bphtb'))


class Payment(Base):
    __tablename__ = 'bphtb_bank'
    id = Column(Integer, primary_key=True)
    tanggal = Column(Date, nullable=False)
    jam = Column(Time, nullable=False)
    seq = Column(Integer, nullable=False)
    transno = Column(String(20), nullable=False)
    cabang = Column(String(5))
    users = Column(String(10))
    bankid = Column(Integer, nullable=False)
    txs = Column(String(5), nullable=False)
    sspd_id = Column(Integer)
    nop = Column(String(50), nullable=False)
    tahun = Column(Integer)
    kd_propinsi = Column(String(2))
    kd_dati2 = Column(String(2))
    kd_kecamatan = Column(String(3))
    kd_kelurahan = Column(String(3))
    kd_blok = Column(String(3))
    no_urut = Column(String(4))
    kd_jns_op = Column(String(1))
    thn_pajak_sppt = Column(String(4))
    wp_nama = Column(String(50), nullable=False)
    wp_alamat = Column(String(100))
    wp_blok_kav = Column(String(100))
    wp_rt = Column(String(3))
    wp_rw = Column(String(3))
    wp_kelurahan = Column(String(30))
    wp_kecamatan = Column(String(30))
    wp_kota = Column(String(30))
    wp_provinsi = Column(String(50))
    wp_kdpos = Column(String(5))
    wp_identitas = Column(String(50))
    wp_identitaskd = Column(String(50))
    wp_npwp = Column(String(50))
    notaris = Column(String(50))
    bumi_luas = Column(Integer)
    bumi_njop = Column(Integer)
    bng_luas = Column(Integer)
    bng_njop = Column(Integer)
    npop = Column(Integer)
    bayar = Column(Integer)
    denda = Column(Integer)
    bphtbjeniskd = Column(Integer)
    is_validated = Column(Integer)
    no_tagihan = Column(String(50))
    catatan = Column(String(255))
    kd_kanwil = Column(String(2))
    kd_kantor = Column(String(2))
    kd_bank_tunggal = Column(String(2))
    kd_bank_persepsi = Column(String(2))
    wp_propinsi = Column(String(100))
    pembayaran_ke = Column(Integer, nullable=False)
    __table_args__ = (
        UniqueConstraint('tanggal', 'jam', 'seq', 'transno'),
        dict(schema='bphtb'))


class IsoPayment(Base):
    __tablename__ = 'bphtb_payment'
    id = Column(Integer, ForeignKey(Payment.id), primary_key=True)
    tgl = Column(DateTime(timezone=True), nullable=False, default=datetime.now)
    iso_request = Column(String(1024), nullable=False)
    transmission = Column(DateTime(timezone=True), nullable=False)
    settlement = Column(Date, nullable=False)
    stan = Column(Integer, nullable=False)
    invoice_id = Column(Integer, ForeignKey(Invoice.id), nullable=False)
    invoice_no = Column(String(32), nullable=False)
    ntb = Column(String(32), nullable=False)
    ntp = Column(String(32), nullable=False, unique=True)
    bank_id = Column(Integer)
    channel_id = Column(Integer)
    bank_ip = Column(String(15), nullable=False)


class IsoReversal(Base):
    __tablename__ = 'bphtb_reversal'
    id = Column(Integer, ForeignKey(IsoPayment.id), primary_key=True)
    tgl = Column(DateTime(timezone=True), nullable=False)
    iso_request = Column(String(1024), nullable=False)


class Config(Base):
    __tablename__ = 'bphtb_config'
    id = Column(Integer, primary_key=True)
    nama = Column(String(32), nullable=False, unique=True)
    nilai = Column(String(10), nullable=False)