tangsel.py

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


Base = declarative_base()


class Kecamatan(Base):
    __tablename__ = 'tblkecamatan'
    kecamatankd = Column(String(2), nullable=False, unique=True)
    kecamatannm = Column(String(50))
    id = Column(Integer, primary_key=True)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)


class Kelurahan(Base):
    __tablename__ = 'tblkelurahan'
    kecamatan_id = Column(Integer, ForeignKey(Kecamatan.id), nullable=False)
    kelurahankd = Column(String(3), nullable=False)
    kelurahannm = Column(String(25))
    id = Column(Integer, primary_key=True)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    __table_args__ = (
        UniqueConstraint('kecamatan_id', 'kelurahankd'),)


class Rekening(Base):
    __tablename__ = 'tblrekening'
    id = Column(Integer, primary_key=True)
    rekeningkd = Column(String(15), nullable=False, unique=True)
    rekeningnm = Column(String(150))
    levelid = Column(Integer)
    issummary = Column(Integer, nullable=False)
    defsign = Column(Integer)
    isppkd = Column(Integer, nullable=False)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)


class Usaha(Base):
    __tablename__ = 'pad_usaha'
    id = Column(Integer, primary_key=True)
    usahanm = Column(String(50), nullable=False)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    so = Column(String(1))


class Pajak(Base):
    __tablename__ = 'pad_pajak'
    pajaknm = Column(String(100))
    jatuhtempo = Column(Integer)
    carahitung = Column(Integer)
    rekening_id = Column(Integer, ForeignKey(Rekening.id), nullable=False)
    rekeningkdsub = Column(String(5))
    usaha_id = Column(Integer, nullable=False)
    rekdenda_id = Column(Integer, ForeignKey(Rekening.id))
    id = Column(Integer, primary_key=True)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    multiple = Column(Integer)
    masapajak = Column(Integer, nullable=False)
    jalan_klas_id = Column(Integer)
    jenis_reklame_id = Column(Integer)
    sudut_pandang_id = Column(Integer)
    __table_args__ = (
        UniqueConstraint('rekening_id', 'rekeningkdsub'),)


class CustomerStatus(Base):
    __tablename__ = 'pad_customer_status'
    id = Column(Integer, primary_key=True)
    statusnm = Column(String(15))
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)


# Wajib Pajak
class Customer(Base):
    __tablename__ = 'pad_customer'
    rp = Column(String(1))
    pb = Column(Integer)
    formno = Column(Integer, nullable=False)
    reg_date = Column(DateTime)
    reg_kelurahan_id = Column(Integer, ForeignKey(Kelurahan.id))
    customernm = Column(String(50))
    alamat = Column(String(1000))
    kelurahan_id = Column(Integer, ForeignKey(Kelurahan.id))
    kabupaten = Column(String(25))
    telphone = Column(String(20))
    kodepos = Column(String(5))
    ijin1 = Column(String(100))
    ijin1no = Column(String(100))
    ijin1tgl = Column(DateTime)
    ijin2 = Column(String(100))
    ijin2no = Column(String(100))
    ijin2tgl = Column(DateTime)
    ijin3 = Column(String(100))
    ijin3no = Column(String(100))
    ijin3tgl = Column(DateTime)
    ijin4 = Column(String(100))
    ijin4no = Column(String(100))
    ijin4tgl = Column(DateTime)
    customer_status_id = Column(Integer, ForeignKey(CustomerStatus.id))
    kirimtgl = Column(DateTime)
    batastgl = Column(DateTime)
    penerimanm = Column(String(50))
    penerimaalamat = Column(String(50))
    penerimatgl = Column(DateTime)
    wpnama = Column(String(50))
    wpalamat = Column(String(1000))
    wpkelurahan = Column(String(25))
    wpkecamatan = Column(String(25))
    wpkabupaten = Column(String(25))
    wptelp = Column(String(20))
    wpkodepos = Column(String(5))
    kembalitgl = Column(DateTime)
    kembalioleh = Column(String(30))
    kukuhno = Column(String(30))
    kukuhtgl = Column(DateTime)
    kukuhprinted = Column(Integer)
    kartuprinted = Column(Integer)
    id = Column(Integer, primary_key=True)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    kukuhnip = Column(Integer)
    kembalinip = Column(Integer)
    catatnip = Column(Integer)
    kukuh_jabat_id = Column(Integer)
    petugas_jabat_id = Column(Integer)
    pencatat_jabat_id = Column(Integer)
    pnama = Column(String(50))
    palamat = Column(String(1000))
    pkelurahan = Column(String(25))
    pkecamatan = Column(String(25))
    pkabupaten = Column(String(25))
    ptelp = Column(String(20))
    pkodepos = Column(String(5))
    parent = Column(Integer)
    npwpd = Column(String(17))
    kecamatan_id = Column(Integer)
    ijin1tglakhir = Column(DateTime)
    ijin2tglakhir = Column(DateTime)
    ijin3tglakhir = Column(DateTime)
    ijin4tglakhir = Column(DateTime)
    notes = Column(Text)
    daftar_id = Column(Integer)
    npwp = Column(String(30))
    __table_args__ = (
        UniqueConstraint('rp', 'pb', 'formno', 'kelurahan_id'),)


# Objek Pajak
class CustomerUsaha(Base):
    __tablename__ = 'pad_customer_usaha'
    customer_id = Column(Integer, ForeignKey(Customer.id), nullable=False)
    usaha_id = Column(Integer, ForeignKey(Usaha.id), nullable=False)
    konterid = Column(Integer, nullable=False)
    kelurahan_id = Column(Integer, ForeignKey(Kelurahan.id))
    notes = Column(Text)
    so = Column(String(1))
    reg_date = Column(DateTime)
    reg_kelurahan_id = Column(Integer)
    customer_status_id = Column(Integer)
    aktifnotes = Column(String(200))
    tmt = Column(DateTime)
    id = Column(Integer, primary_key=True)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    air_zona_id = Column(Integer)
    air_manfaat_id = Column(Integer)
    newkelid = Column(Integer)
    kecamatan_id = Column(Integer)
    def_pajak_id = Column(Integer)
    npwpd_lama = Column(String(17))
    opnm = Column(Text)
    opalamat = Column(Text)
    latitude = Column(Float)
    longitude = Column(Float)
    no_telp = Column(Text)
    def_pajak_id_ori = Column(Integer)
    __table_args__ = (
        UniqueConstraint('customer_id', 'usaha_id', 'konterid'),)


class SptType(Base):
    __tablename__ = 'pad_spt_type'
    id = Column(Integer, primary_key=True)
    typenm = Column(String(20), nullable=False)
    tmt = Column(DateTime)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)


class Invoice(Base):
    __tablename__ = 'pad_spt'
    id = Column(Integer, primary_key=True)
    customer_usaha_id = Column(
        Integer, ForeignKey(CustomerUsaha.id), nullable=False)
    pajak_id = Column(Integer, ForeignKey(Pajak.id), nullable=False)
    tahun = Column(Integer, nullable=False)
    sptno = Column(Integer, nullable=False)
    terimanip = Column(String(50))
    terimatgl = Column(DateTime, nullable=False)
    kirimtgl = Column(DateTime)
    jatuhtempotgl = Column(DateTime)
    type_id = Column(Integer, ForeignKey(SptType.id))
    so = Column(String(1), nullable=False)
    masadari = Column(DateTime, nullable=False)
    masasd = Column(DateTime, nullable=False)
    minomset = Column(Float)
    dasar = Column(Float, nullable=False)
    tarif = Column(Float, nullable=False)
    denda = Column(Float)
    bunga = Column(Float, nullable=False)
    setoran = Column(Float)
    kenaikan = Column(Float, nullable=False)
    kompensasi = Column(Float)
    lain2 = Column(Float, nullable=False)
    air_manfaat_id = Column(Integer)
    air_zona_id = Column(Integer)
    meteran_awal = Column(Integer)
    meteran_akhir = Column(Integer)
    volume = Column(Integer)
    satuan = Column(Text)
    r_nsr = Column(Float)
    r_nsrno = Column(String(30))
    r_nsrtgl = Column(DateTime)
    r_bayarid = Column(Integer)
    r_tarifid = Column(Integer)
    r_kontrak = Column(Float)
    r_lama = Column(Integer)
    r_jalanklas_id = Column(Integer)
    r_jalan_id = Column(Integer)
    r_lokasi = Column(String(250))
    r_judul = Column(String(200))
    r_panjang = Column(Float)
    r_lebar = Column(Float)
    r_muka = Column(Float)
    r_banyak = Column(Float)
    r_luas = Column(Float)
    enabled = Column(Integer)
    unit_id = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    customer_id = Column(Integer, nullable=False)
    r_nsl_kecamatan_id = Column(Integer)
    r_nsl_type_id = Column(Integer)
    r_nsl_nilai = Column(Float)
    r_kelurahan_id = Column(Integer)
    isprint_dc = Column(Boolean)
    notes = Column(String(255))
    r_lokasi_id = Column(Integer)
    rekening_id = Column(Integer)
    no_skpd_lama = Column(String(50))
    r_calculated = Column(Float)
    h2h_ready = Column(Integer)
    pajak_terhutang = Column(Integer)
    status_pembayaran = Column(Integer)
    handphone = Column(String(20))
    merek = Column(String(50))
    r_tinggi = Column(Float)
    posted = Column(Integer)
    r_permohonan_id = Column(Integer)
    r_permohonan_no = Column(Text)
    r_permohonan_tgl = Column(DateTime)
    file1 = Column(String(250))
    file2 = Column(String(250))
    file3 = Column(String(250))
    file4 = Column(String(250))
    file5 = Column(String(250))
    r_nama = Column(String(50))
    is_entry = Column(Integer)
    notes_entry = Column(String(50))
    __table_args__ = (
        UniqueConstraint('tahun', 'sptno'),)


class Payment(Base):
    __tablename__ = 'pad_sspd'
    tahun = Column(Integer, nullable=False)
    sspdno = Column(Integer, nullable=False)
    spt_id = Column(Integer, ForeignKey(Invoice.id), nullable=False)
    printed = Column(Integer, nullable=False)
    sspdtgl = Column(DateTime, nullable=False)
    denda = Column(Float, nullable=False)
    id = Column(Integer, primary_key=True)
    enabled = Column(Integer)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    bunga = Column(Float)
    h2h_ready = Column(Integer)
    jml_bayar = Column(Integer)
    is_valid = Column(Integer)
    is_inject = Column(Integer)
    posted = Column(Integer)
    __table_args__ = (
        UniqueConstraint('tahun', 'sspdno'),)


# Jalur transaksi bank
class Channel(Base):
    __tablename__ = 'pad_channel'
    id = Column(Integer, primary_key=True)
    nama = Column(String(20), nullable=False, unique=True)


class Bank(Base):
    __tablename__ = 'pad_tp'
    id = Column(Integer, primary_key=True)
    singkatan = Column(String(16), nullable=False, unique=True)
    nama = Column(String(32), nullable=False, unique=True)


class IsoPayment(Base):
    __tablename__ = 'pad_payment'
    id = Column(Integer, primary_key=True)
    sspd_id = Column(Integer, nullable=False)
    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_no = Column(String(32), nullable=False)
    ntb = Column(String(32), nullable=False)
    ntp = Column(String(32), nullable=False, unique=True)
    bank_id = Column(Integer, ForeignKey(Bank.id), nullable=False)
    channel_id = Column(Integer, ForeignKey(Channel.id), nullable=False)
    bank_ip = Column(String(15), nullable=False)
    invoice_id = Column(Integer)


class IsoReversal(Base):
    __tablename__ = 'pad_reversal'
    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)