pembayaran.py

from sqlalchemy import (
    Column, Integer, BigInteger, DateTime, String, func, and_)
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import (
    relationship,
    backref,
)
from .tools import FixNop, FixBayar, filter_nop
from ..models import (
    CommonModel, PbbBase, PbbDBSession, Kelurahan, PBB_ARGS)


class PembayaranSpptModels(CommonModel):
    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)
    thn_pajak_sppt = Column(String(4), primary_key=True)
    pembayaran_sppt_ke = Column(Integer, primary_key=True)
    # kd_kanwil_bank = Column(String(2))
    # kd_kppbb_bank = Column(String(2))
    # kd_bank_tunggal     = Column(String(2))
    # kd_bank_persepsi    = Column(String(2))
    kd_kanwil = Column(String(2))
    kd_kantor = Column(String(2))
    kd_tp = Column(String(2), primary_key=True)
    denda_sppt = Column(BigInteger, nullable=False, default=0)
    jml_sppt_yg_dibayar = Column(BigInteger, nullable=False, default=0)
    tgl_pembayaran_sppt = Column(DateTime(timezone=False))
    tgl_rekam_byr_sppt = Column(DateTime(timezone=False))
    nip_rekam_byr_sppt = Column(String(18))

    # posted = Column(SmallInteger, nullable=False, default=0)
    # denda_sblm_diskon = Column(Float)
    # discount = Column(Float, nullable=False, default=0)
    # discount_pokok = Column(Float, nullable=False, default=0)
    # discount_denda = Column(Float, nullable=False, default=0)

    @hybrid_property
    def nop(self):
        return (
                self.kd_propinsi +
                self.kd_dati2 +
                self.kd_kecamatan +
                self.kd_kelurahan +
                self.kd_blok +
                self.no_urut +
                self.kd_jns_op
        )

    @classmethod
    def get_ke(cls, values):
        # nop = FixNop(values["nop"])
        tahun = values["tahun"]
        qry = PbbDBSession.query(func.max(cls.pembayaran_sppt_ke))
        # qry = qry.filter(cls.thn_pajak_sppt == tahun)
        qry = filter_nop(qry, values["nop"])
        qry = qry.filter_by(thn_pajak_sppt=tahun)
        return qry.scalar()

    @classmethod
    def next_ke(cls, values):
        max_ke = cls.get_ke(values)
        return max_ke and max_ke + 1 or 1

    @classmethod
    def save(cls, values, row=None):
        if not row:
            row = cls()
        row.from_dict(values)
        ke = cls.get_ke(values)
        row.pembayaran_sppt_ke = ke and ke + 1 or 1
        PbbDBSession.add(row)
        PbbDBSession.flush()

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

    @classmethod
    def query_id(cls, id):
        fix_bayar = FixBayar(id)
        return cls.query().filter(
            cls.kd_propinsi == fix_bayar["kd_propinsi"],
            cls.kd_dati2 == fix_bayar["kd_dati2"],
            cls.kd_kecamatan == fix_bayar["kd_kecamatan"],
            cls.kd_kelurahan == fix_bayar["kd_kelurahan"],
            cls.kd_blok == fix_bayar["kd_blok"],
            cls.no_urut == fix_bayar["no_urut"],
            cls.kd_jns_op == fix_bayar["kd_jns_op"],
            cls.thn_pajak_sppt == fix_bayar["thn_pajak_sppt"],
            cls.thn_pajak_sppt == fix_bayar["thn_pajak_sppt"],
            cls.kd_kanwil == fix_bayar["kd_kanwil"],
            cls.kd_kantor == fix_bayar["kd_kantor"],
            cls.kd_tp == fix_bayar["kd_tp"],
            cls.pembayaran_sppt_ke == fix_bayar["pembayaran_sppt_ke"], )

    @classmethod
    def query_by_nop(cls, nop):
        fxNop = FixNop(nop)
        return cls.query(). \
            filter(cls.kd_propinsi == fxNop["kd_propinsi"],
                   cls.kd_dati2 == fxNop["kd_dati2"],
                   cls.kd_kecamatan == fxNop["kd_kecamatan"],
                   cls.kd_kelurahan == fxNop["kd_kelurahan"],
                   cls.kd_blok == fxNop["kd_blok"],
                   cls.no_urut == fxNop["no_urut"],
                   cls.kd_jns_op == fxNop["kd_jns_op"])

    @classmethod
    def query_by_nop_thn(cls, nop, thn):
        fxNop = FixNop(nop)
        return cls.query(). \
            filter(cls.kd_propinsi == fxNop["kd_propinsi"],
                   cls.kd_dati2 == fxNop["kd_dati2"],
                   cls.kd_kecamatan == fxNop["kd_kecamatan"],
                   cls.kd_kelurahan == fxNop["kd_kelurahan"],
                   cls.kd_blok == fxNop["kd_blok"],
                   cls.no_urut == fxNop["no_urut"],
                   cls.kd_jns_op == fxNop["kd_jns_op"],
                   cls.thn_pajak_sppt == thn)

    @classmethod
    def query_bayar(cls, nop, tahun):
        fx_nop = FixNop(nop)
        q = PbbDBSession.query(func.sum(cls.denda_sppt).label("denda"),
                               func.sum(cls.jml_sppt_yg_dibayar).label("bayar"),
                               # func.sum(cls.discount).label("discount"),
                               # func.sum(cls.discount_pokok).label("discount_pokok"),
                               # func.sum(cls.discount_denda).label("discount_denda"),
                               func.max(cls.pembayaran_sppt_ke).label("ke"),
                               func.max(cls.tgl_pembayaran_sppt).label(
                                   "tanggal"),
                               ). \
            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"],
                   cls.thn_pajak_sppt == tahun)
        return q

    # @classmethod
    # def reversal(cls, id):
    #     fix_bayar = FixBayar(id)
    #     q = cls.query_id(id)
    #     row = q.first()
    #     if row:
    #         row.denda_sppt = 0
    #         row.jml_sppt_yg_dibayar = 0
    #         PbbDBSession.add(row)
    #         PbbDBSession.flush()
    #         Sppt.set_status(id, 0)
    #
    #     return row


class PembayaranSppt(PbbBase, PembayaranSpptModels):
    __tablename__ = 'pembayaran_sppt'
    __table_args__ = (PBB_ARGS,)
    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)
    thn_pajak_sppt = Column(String(4), primary_key=True)
    # posted = Column(SmallInteger, nullable=False, default=0)
    kd_kanwil = Column(String(2), primary_key=True)
    kd_kantor = Column(String(2), primary_key=True)
    kd_tp = Column(String(2), primary_key=True)
    sppt = relationship("Sppt", backref=backref("pembayaran"),
                        foreign_keys=[kd_propinsi, kd_dati2, kd_kecamatan,
                                      kd_kelurahan, kd_blok, no_urut, kd_jns_op,
                                      thn_pajak_sppt],
                        primaryjoin="and_(Sppt.kd_propinsi == PembayaranSppt.kd_propinsi ," \
                                    "Sppt.kd_dati2 == PembayaranSppt.kd_dati2, " \
                                    "Sppt.kd_kecamatan == PembayaranSppt.kd_kecamatan, " \
                                    "Sppt.kd_kelurahan == PembayaranSppt.kd_kelurahan, " \
                                    "Sppt.kd_blok == PembayaranSppt.kd_blok, " \
                                    "Sppt.no_urut == PembayaranSppt.no_urut, " \
                                    "Sppt.kd_jns_op == PembayaranSppt.kd_jns_op, " \
                                    "Sppt.thn_pajak_sppt == PembayaranSppt.thn_pajak_sppt)")

    # Diguanakn untuk melakukan summary berdasarkan posted_id
    # Di Module Integrasi
    @classmethod
    def query_sum_nop_thn(cls, nop, tahun):
        fix_nop = FixNop(nop)
        qry = PbbDBSession.query(
            func.sum(cls.denda_sppt).label('denda'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'))
        qry = filter_nop(cls, qry)

    @classmethod
    def query_posted_id(cls, posted_id):
        return PbbDBSession.query(
            func.sum(cls.denda_sppt).label('denda'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            cls.posted). \
            filter(cls.posted == posted_id). \
            join(Kelurahan,
                 and_(cls.kd_propinsi == Kelurahan.kd_propinsi,
                      cls.kd_dati2 == Kelurahan.kd_dati2,
                      cls.kd_kecamatan == Kelurahan.kd_kecamatan,
                      cls.kd_kelurahan == Kelurahan.kd_kelurahan)). \
            group_by(
            cls.posted)

    # Diguanakn untuk melakukan reposting data summary berdasarkan posted_id
    # Di Module Integrasi
    @classmethod
    def query_posted_items_id(cls, posted_id):
        return PbbDBSession.query(
            cls.tgl_pembayaran_sppt.label('tanggal'),
            cls.thn_pajak_sppt.label('tahun'),
            Kelurahan.kd_sektor,
            cls.kd_kanwil,
            cls.kd_kantor,
            cls.kd_tp,
            func.sum(cls.denda_sppt).label('denda'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            cls.posted). \
            filter(cls.posted == posted_id). \
            join(Kelurahan,
                 and_(cls.kd_propinsi == Kelurahan.kd_propinsi,
                      cls.kd_dati2 == Kelurahan.kd_dati2,
                      cls.kd_kecamatan == Kelurahan.kd_kecamatan,
                      cls.kd_kelurahan == Kelurahan.kd_kelurahan)). \
            group_by(
            cls.tgl_pembayaran_sppt,
            cls.thn_pajak_sppt,
            Kelurahan.kd_sektor,
            cls.kd_kanwil,
            cls.kd_kantor,
            cls.kd_tp,
            cls.posted)

    # query digunakan untuk filter pembayaran berdasarkan tanggal tertentu dan status posting
    # dibuat saat module integrasi
    # parameter awal akhir date dalam format 'YYYY-MM-DD'
    # paramter posted id adalah integer
    @classmethod
    def query_header(cls, awal, akhir, posted_id):
        adate = func.to_char(cls.tgl_pembayaran_sppt, 'YYYY-MM-DD')
        return PbbDBSession.query(adate.label('tanggal'),
                                  cls.thn_pajak_sppt.label('tahun'),
                                  cls.kd_kanwil,
                                  cls.kd_kantor,
                                  cls.kd_tp,
                                  func.sum(cls.jml_sppt_yg_dibayar).label(
                                      'bayar'),
                                  func.sum(cls.denda_sppt).label('denda')
                                  ). \
            group_by(adate,
                     cls.thn_pajak_sppt,
                     cls.kd_kanwil,
                     cls.kd_kantor,
                     cls.kd_tp,
                     ). \
            order_by(adate,
                     cls.kd_kanwil,
                     cls.kd_kantor,
                     cls.kd_tp,
                     cls.thn_pajak_sppt,
                     ). \
            filter(adate.between(awal, akhir),
                   cls.posted == posted_id)

    @classmethod
    def query_items(cls, pay_id):
        adate = func.to_char(cls.tgl_pembayaran_sppt, 'YYYY-MM-DD')
        return PbbDBSession.query(
            func.sum(cls.denda_sppt).label('denda'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            Kelurahan.kd_sektor,
        ). \
            join(Kelurahan,
                 and_(cls.kd_propinsi == Kelurahan.kd_propinsi,
                      cls.kd_dati2 == Kelurahan.kd_dati2,
                      cls.kd_kecamatan == Kelurahan.kd_kecamatan,
                      cls.kd_kelurahan == Kelurahan.kd_kelurahan)). \
            filter(
            cls.posted == pay_id,
        ). \
            group_by(Kelurahan.kd_sektor). \
            order_by(Kelurahan.kd_sektor)

    # query_by_tgl digunakan untuk filter pembayaran berdasarkan tanggal tertentu
    # dibuat saat module integrasi
    # parameter awal akhir dalam tanggal
    @classmethod
    def query_unposted_by_tgl(cls, awal, akhir):
        return PbbDBSession.query(
            cls.tgl_pembayaran_sppt.label('tanggal'),
            cls.kd_kanwil,
            cls.kd_kantor,
            cls.kd_tp,
            cls.thn_pajak_sppt.label('tahun'),
            Kelurahan.kd_sektor,
            func.sum(cls.denda_sppt).label('denda'),
            func.sum(cls.jml_sppt_yg_dibayar).label('bayar'),
            cls.posted). \
            join(Kelurahan,
                 and_(cls.kd_propinsi == Kelurahan.kd_propinsi,
                      cls.kd_dati2 == Kelurahan.kd_dati2,
                      cls.kd_kecamatan == Kelurahan.kd_kecamatan,
                      cls.kd_kelurahan == Kelurahan.kd_kelurahan)). \
            filter(
            cls.tgl_pembayaran_sppt.between(awal, akhir),
            cls.posted == 0). \
            group_by(
            cls.tgl_pembayaran_sppt,
            cls.thn_pajak_sppt,
            Kelurahan.kd_sektor,
            cls.kd_kanwil,
            cls.kd_kantor,
            cls.kd_tp,
            cls.posted). \
            order_by(
            cls.tgl_pembayaran_sppt.label('tanggal'),
            cls.kd_kanwil,
            cls.kd_kantor,
            cls.kd_tp,
            cls.thn_pajak_sppt.label('tahun'),
            Kelurahan.kd_sektor)

    @classmethod
    def set_posteds(cls, row, bank):
        return PbbDBSession.query(cls). \
            filter(cls.posted == 0,
                   cls.tgl_pembayaran_sppt == row.tanggal,
                   cls.thn_pajak_sppt == row.tahun,
                   cls.kd_kanwil == bank['kd_kanwil'],
                   cls.kd_kantor == bank['kd_kantor'],
                   cls.kd_tp == bank['kd_tp']). \
            update({cls.posted: row.id}, synchronize_session=False)

    @classmethod
    def set_unposteds(cls, row):
        PbbDBSession.query(cls). \
            filter(cls.posted > 0,
                   cls.tgl_pembayaran_sppt == row.tanggal,
                   cls.kd_kanwil == row.kd_kanwil,
                   cls.kd_kantor == row.kd_kantor,
                   cls.thn_pajak_sppt == row.tahun,
                   cls.kd_tp == row.kd_tp). \
            update({cls.posted: 0}, synchronize_session=False)

    @classmethod
    def set_unposted_by_posted_id(cls, posted_id):
        return PbbDBSession.query(cls). \
            filter(
            cls.posted == posted_id). \
            update({cls.posted: 0}, synchronize_session=False)

    # @classmethod
    # def get_by_nop(cls, p_nop):
    # pkey = FixLength(NOP)
    # pkey.set_raw(p_nop)
    # query = cls.query_data()
    # return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
    # kd_dati2 = pkey['kd_dati2'],
    # kd_kecamatan = pkey['kd_kecamatan'],
    # kd_kelurahan = pkey['kd_kelurahan'],
    # kd_blok = pkey['kd_blok'],
    # no_urut = pkey['no_urut'],
    # kd_jns_op = pkey['kd_jns_op'],)
    # @classmethod
    # def get_by_nop_thn(cls, p_nop, p_tahun):
    # query = cls.get_by_nop(p_nop)
    # return query.filter_by(thn_pajak_sppt = p_tahun)

    # @classmethod
    # def get_by_kelurahan(cls, p_kode, p_tahun):
    # pkey = FixLength(DESA)
    # pkey.set_raw(p_kode)
    # query = cls.query_data()
    # return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
    # kd_dati2 = pkey['kd_dati2'],
    # kd_kecamatan = pkey['kd_kecamatan'],
    # kd_kelurahan = pkey['kd_kelurahan'],
    # thn_pajak_sppt = p_tahun)

    # @classmethod
    # def get_by_kecamatan(cls, p_kode, p_tahun):
    # pkey = FixLength(KECAMATAN)
    # pkey.set_raw(p_kode)
    # query = cls.query_data()
    # return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
    # kd_dati2 = pkey['kd_dati2'],
    # kd_kecamatan = pkey['kd_kecamatan'],
    # kd_kelurahan = pkey['kd_kelurahan'],
    # thn_pajak_sppt = p_tahun)

    # @classmethod
    # def get_by_tanggal(cls, p_kode, p_tahun):
    # pkey = DateVar
    # p_kode = re.sub("[^0-9]", "", p_kode)
    # pkey.set_raw(p_kode)
    # query = cls.query_data()
    # return query.filter_by(tgl_pembayaran_sppt = pkey.get_value)

    # @classmethod
    # def get_rekap_by_kecamatan(cls, p_kode, p_tahun):
    # pkey = FixLength(KECAMATAN)
    # pkey.set_raw(p_kode)
    # query = PbbDBSession.query(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan,
    # func.sum(cls.denda_sppt).label('denda'),
    # func.sum(cls.pbb_yg_dibayar_sppt).label('jumlah') ).\
    # group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan, cls.kd_kelurahan)
    # return query.filter_by(kd_propinsi = pkey['kd_propinsi'],
    # kd_dati2 = pkey['kd_dati2'],
    # kd_kecamatan = pkey['kd_kecamatan'],
    # thn_pajak_sppt = p_tahun)

    # @classmethod
    # def get_rekap_by_thn(cls, p_tahun):
    # query = PbbDBSession.query(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan,
    # func.sum(cls.denda_sppt).label('denda'),
    # func.sum(cls.pbb_yg_dibayar_sppt).label('jumlah')).\
    # group_by(cls.kd_propinsi, cls.kd_dati2, cls.kd_kecamatan)
    # return query.filter_by(thn_pajak_sppt = p_tahun)


class PembayaranRekap(PbbBase, CommonModel):
    __tablename__ = 'pembayaran_sppt_rekap'
    __table_args__ = PBB_ARGS
    id = Column(BigInteger, primary_key=True)
    tanggal = Column(DateTime)
    kode = Column(String(30))
    tahun = Column(String(4))
    uraian = Column(String(200))
    denda = Column(BigInteger)
    bayar = Column(BigInteger)
    posted = Column(Integer)
    sektor = Column(String(2))
    bank = Column(String(32))
    created = Column(DateTime)
    create_uid = Column(Integer)
    updated = Column(DateTime)
    update_uid = Column(Integer)
    no_buku = Column(String(30))  # nomor sts
    tgl_buku = Column(DateTime)  # tanggal sts

    def __init__(cls):
        pass