payment.py

from pyexpat import model
import sys
from opensipkd.base import models
from sqlalchemy import (Column, Integer, DateTime, String, Float,
                        BigInteger, SmallInteger, Date, Time,
                        UniqueConstraint, func, literal_column)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import engine_from_config
from opensipkd.tools import get_settings

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
)
from pyramid.paster import (
    get_appsettings,
    setup_logging,
)


PCDBSession = scoped_session(sessionmaker())
PCBase = declarative_base()

class DefaultModel(models.DefaultModel):
    @classmethod
    def count(cls, db_session=PCDBSession):
        return super().count(db_session)

    @classmethod
    def query(cls, db_session=PCDBSession, filters=None):
        return super().query(db_session, filters)
        
    @classmethod
    def query_from(cls, columns=None, filters=None, db_session=PCDBSession):
        return super().query_from(columns, filters, db_session)

    @classmethod
    def query_id(cls, row_id, db_session=PCDBSession):
        return super.query_id(row_id, db_session)

    @classmethod
    def delete(cls, row_id, db_session=PCDBSession):
        super().delete(row_id, db_session)

    @classmethod
    def flush(cls, row, db_session=PCDBSession):
        super().flush(row, db_session)

class PaymentPBB(PCBase, DefaultModel):
    __tablename__ = 'pbb_report'
    __table_args__ = {'schema': 'public'}
    id = Column(BigInteger, primary_key=True)
    stan = Column(String(6), nullable=False)
    ntb = Column(String(32), nullable=False)
    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))
    pembayaran_sppt_ke = Column(SmallInteger)
    nm_wp_sppt = Column(String(30))
    tgl_pembayaran_sppt = Column(DateTime)
    tgl_rekam_byr_sppt = Column(DateTime)
    tgl_inquiry = Column(DateTime)
    pbb_yg_harus_dibayar_sppt = Column(Integer)
    denda_sppt = Column(Integer)
    jml_sppt_yg_dibayar = Column(Integer)
    channel_kode = Column(String(6))
    channel_nama = Column(String(256))
    user_id = Column(String(128))
    iso_request = Column(String(2048))
    discount = Column(BigInteger)
    bank_id = Column(String(4))
    tgl_batal = Column(DateTime(timezone=True))
    __table_args__ = (
        UniqueConstraint('stan', 'ntb'),
    )


    @classmethod
    def qry_sum(cls, columns=[]):
        qry = PCDBSession.query(
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            func.sum(cls.denda_sppt).label('denda'),
            func.count(cls.id).label('trx')).select_from(cls)
        for col in columns:
            qry=qry.add_columns(col)
        return qry


# baru dari db BPHTB


class PaymentBPHTB(PCBase, models.CommonModel):
    __tablename__ = 'bphtb_report'
    __table_args__ = {'schema': 'public'}
    id = Column(Integer, primary_key=True)
    stan = Column(String(6), nullable=False)
    ntb = Column(String(32), nullable=False)
    tgl = Column(Date, nullable=False)
    jam = Column(Time, nullable=False)
    invoice_id = Column(String(24), nullable=False)
    nop = Column(String(24), nullable=False)
    wp_nama = Column(String(50), nullable=False)
    wp_alamat = Column(String(100), nullable=False)
    op_alamat = Column(String(100), nullable=False)
    npop = Column(BigInteger)
    bumi_luas = Column(BigInteger)
    bng_luas = Column(BigInteger)
    nilai_bphtb = Column(BigInteger)
    channel_id = Column(String(6))
    channel_nama = Column(String(256), nullable=False)
    # user_id = Column(String(128), nullable=False)
    jenis_perolehan = Column(String(123), nullable=False)
    ppat = Column(String(256), nullable=False)
    tgl_batal = Column(DateTime(timezone=True))
    __table_args__ = (
        UniqueConstraint('stan', 'ntb'),
    )

    @classmethod
    def qry_sum(cls, columns=[]):
        qry = PCDBSession.query(
            func.sum(cls.nilai_bphtb).label('bayar'),
            literal_column("0").label('denda'),
            func.count(cls.id).label('trx')).select_from(cls)
        for col in columns:
            qry = qry.add_columns(col)
        return qry
    
# baru PAD


class PaymentPAD(PCBase, models.CommonModel):
    __tablename__ = 'pad_report'
    __table_args__ = {'schema': 'public'}
    id = Column(Integer, primary_key=True)
    stan = Column(String(6), nullable=False)
    ntb = Column(String(32), nullable=False)
    # pad.pad_sspd.create_date
    tgl = Column(Date, nullable=False)
    # pad.pad_sspd.sspdjam
    jam = Column(Time, nullable=False)
    # pad.pad_sspd.id
    sspd_id = Column(Integer, nullable=False)
    # Bit 61
    nomor_bayar = Column(String(16), nullable=False)
    # Pad.Usahanm
    jenis_pajak = Column(String(32), nullable=False)
    # pad.pad_spt.masadari
    masa_pajak = Column(Date, nullable=False)
    # pad.pad_customer
    npwpd = Column(String(17), nullable=False)
    # pad.pad_customer.customernm
    nama_wp = Column(String(150), nullable=False)
    # pad.pad_sspd.jml_bayar - pad.pad_sspd.denda
    pokok = Column(Float, nullable=False)
    # pad.pad_sspd.denda
    denda = Column(Float, nullable=False)
    # pad.pad_sspd.bunga
    bunga = Column(Float, nullable=False)
    # pad.pad_sspd.jml_bayar
    jml_bayar = Column(Float, nullable=False)
    # Bit 18
    channel_id = Column(String(4), nullable=False)
    # Bit 41 / 42 / 43
    channel_name = Column(String(32), nullable=False)
    # Dari pad_reversal
    tgl_batal = Column(DateTime(timezone=True))
    # pad.kecamatan.kecamatannm
    kecamatan_nm = Column(String(50), nullable=False)
    __table_args__ = (
        UniqueConstraint('stan', 'ntb'),
    )

    @classmethod
    def qry_sum(cls, columns=[]):
        qry = PCDBSession.query(
            func.sum(cls.jml_bayar).label('bayar'),
            func.sum(cls.denda+cls.bunga).label('denda'),
            func.count(cls.id).label('trx')).select_from(cls)
        for col in columns:
            qry = qry.add_columns(col)
        return qry
# baru PAD


class PaymentWEBR(PCBase, models.CommonModel):
    __tablename__ = 'webr_report'
    __table_args__ = {'schema': 'public'}
    id = Column(Integer, primary_key=True)
    stan = Column(String(6), nullable=False)
    ntb = Column(String(32), nullable=False)
    tgl = Column(Date, nullable=False)
    jam = Column(Time, nullable=False)
    payment_id = Column(Integer, nullable=False)
    nomor_bayar = Column(String(16), nullable=False)
    nama_wp = Column(String(150), nullable=False)
    # webr.payment.jml_bayar - webr.payment.denda
    pokok = Column(Float, nullable=False)
    # webr.payment.denda
    denda = Column(Float, nullable=False)
    # webr.payment.jml_bayar
    jml_bayar = Column(Float, nullable=False)
    # Bit 18
    channel_id = Column(String(4), nullable=False)
    # Bit 41 / 42 / 43
    channel_name = Column(String(32), nullable=False)
    # Dari pad_reversal
    tgl_batal = Column(DateTime(timezone=True))
    # webr.payment.status
    status = Column(String(2), nullable=False)
    __table_args__ = (
        UniqueConstraint('stan', 'ntb'),
    )

    @classmethod
    def qry_sum(cls, columns=[]):
        qry = PCDBSession.query(
            func.sum(cls.jml_bayar).label('bayar'),
            func.sum(cls.denda).label('denda'),
            func.count(cls.id).label('trx')).select_from(cls)
        for col in columns:
            qry = qry.add_columns(col)
        return qry
    

class TargetJNS(PCBase, models.CommonModel):
    __tablename__ = 'target_jenis'
    __table_args__ = {'schema': 'public'}
    id = Column(Integer, primary_key=True)
    created = Column(Date, nullable=False)
    updated = Column(Date, nullable=False)
    kode = Column(String(32))
    nama = Column(String(128))
    status = Column(SmallInteger)


class Targets(PCBase, DefaultModel):
    __tablename__ = 'targets'
    __table_args__ = {'schema': 'public'}
    id = Column(Integer, primary_key=True)
    created = Column(Date, nullable=False)
    updated = Column(Date, nullable=False)
    kode = Column(String(32))
    nama = Column(String(128))
    tahun = Column(Integer)
    jenis = Column(Integer)
    t_anggaran = Column(BigInteger)
    t_perubahan = Column(BigInteger)
    m01 = Column(BigInteger)
    m02 = Column(BigInteger)
    m03 = Column(BigInteger)
    m04 = Column(BigInteger)
    m05 = Column(BigInteger)
    m06 = Column(BigInteger)
    m07 = Column(BigInteger)
    m08 = Column(BigInteger)
    m09 = Column(BigInteger)
    m10 = Column(BigInteger)
    m11 = Column(BigInteger)
    m12 = Column(BigInteger)
    status = Column(SmallInteger)
    
    @classmethod
    def query_from(cls, columns=None, filters=None, db_session=PCDBSession):
        query = db_session.query().select_from(cls)
        if columns:
            for c in columns:
                query = query.add_columns(c)
        if filters:
            filter_expressions = []
            for d in filters:
                field = getattr(cls, d[0])
                operator = d[1]
                value = d[2]
                filter_expressions.append(field.op(operator)(value))
            query = query.filter(
                *[e for i, e in enumerate(filter_expressions) if e is not None])
        return query


def get_connection(settings):

    url = 'pcpd.url' in settings and settings['pcpd.url'] and 'pcpd.' or 'sqlalchemy.'
    engine = engine_from_config(settings, url, pool_pre_ping=True)
    PCDBSession.configure(bind=engine)
    PCBase.metadata.bind = engine
    return engine


def includeme(config):
    settings = get_settings()

    get_connection(settings)


def init_db(argv=sys.argv):
    config_uri = argv[1]
    setup_logging(config_uri)
    settings = get_appsettings(config_uri)
    engine = get_connection(settings)
    PCBase.metadata.bind = engine
    PCBase.metadata.create_all(bind=engine)