payment.py

from datetime import timedelta, datetime
import sys
from opensipkd.base.models import (
    Base, DBSession, CommonModel, DefaultModel, NamaModel, KodeModel, DBSession,
    TABLE_ARGS
)
from sqlalchemy import (Column, Integer, DateTime, String, Text, ForeignKey, Float,
                        Boolean, BigInteger, SmallInteger, desc, asc, Date, Time,
                        UniqueConstraint, func)
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,
)

from ..tools import create_now

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


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 sum_data(cls):
        return DBSession.query(
            func.to_char(cls.tgl_pembayaran_sppt,
                         'yyyy-mm-dd').label('tanggal'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            func.sum(cls.denda_sppt).label('denda'))\
            .group_by(func.to_char(cls.tgl_pembayaran_sppt, 'yyyy-mm-dd'))

    @classmethod
    def sum_daily(cls, length=0):
        now = datetime.now()
        tod = now.date
        tom = tod + timedelta(days=1)
        if length:
            tod = tod-timedelta(days=length)
        return cls.sum_data().filter(cls.tgl_pembayaran_sppt.between(tod, tom))

# baru dari db BPHTB


class PaymentBPHTB(PCBase, 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 sum_data(cls):
        return DBSession.query(func.sum(cls.nilai_bphtb).label('bayar'),
                               func.sum('0').label('denda'))

    @classmethod
    def sum_today(cls):
        now = datetime.now()
        tom = timedelta(now.date, days=1)
        qry = cls.sum_data().filter(cls.tgl.between(now.date, tom))
        return qry.first()
# baru PAD


class PaymentPAD(PCBase, 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 sum_data(cls):
        return DBSession.query(func.sum(cls.jml_bayar).label('bayar'),
                               func.sum(cls.denda+cls.bunga).label('denda'))

    @classmethod
    def sum_today(cls):
        now = datetime.now()
        tom = timedelta(now.date, days=1)
        qry = cls.sum_data().filter(cls.tgl.between(now.date, tom))
        return qry.first()
# baru PAD


class PaymentWEBR(PCBase, 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 sum_data(cls):
        return DBSession.query(func.sum(cls.jml_bayar).label('bayar'),
                               func.sum(cls.denda).label('denda'))

    @classmethod
    def sum_today(cls):
        now = datetime.now()
        tom = timedelta(now.date, days=1)
        qry = cls.sum_data().filter(cls.tgl.between(now.date, tom))
        return qry.first()


def get_connection(settings):

    url = 'pcpd.url' in settings and settings['pcpd.url'] and 'pcpd.' or 'sqlalchemy.'
    engine = engine_from_config(settings, url)
    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)