default.py

import sys
from datetime import (
    datetime,
    date,
    timedelta,
    )
from sqlalchemy import (
    Column,
    Integer,
    String,
    UniqueConstraint,
    func,
    )
from sqlalchemy.exc import (
    DatabaseError,
    ProgrammingError,
    )
from ISO8583.ISO8583 import BitNotSet
import transaction
from sismiop.services.base import get_id
from opensipkd.string import (
    FixLength,
    DateTimeVar,
    to_str,
    )
from opensipkd.waktu import (
    dmyhms,
    create_datetime,
    )
from opensipkd.iso8583.bjb.pbb.default import Doc
from opensipkd.iso8583.bjb.pbb.structure import PAYMENT_CODE
from opensipkd.iso8583.bjb.pbb.models import Log
from opensipkd.iso8583.bjb.models import Log as UniversalLog
from ..models import (
    Pbb,
    Base,
    )
from ..common import (
    get_iso,
    get_channel_info_by_iso,
    get_channel_name_by_row,
    get_channel_name_by_dict,
    get_channel_name,
    BaseApp,
    one_day,
    VaInvoice,
    VaPayment,
    InvalidSource,
    str2dict,
    )


def to_date(dt):
    if isinstance(dt, datetime):
        return dt.date()
    return dt


class AlternativePayment(Base):
    __tablename__ = 'payment'
    id = Column(String(32), primary_key=True)
    inquiry_id = Column(Integer, nullable=False)
    propinsi = Column(String(2), nullable=False)
    kabupaten = Column(String(2), nullable=False)
    kecamatan = Column(String(3), nullable=False)
    kelurahan = Column(String(3), nullable=False)
    blok = Column(String(3), nullable=False)
    urut = Column(String(4), nullable=False)
    jenis = Column(String(1), nullable=False)
    tahun = Column(Integer, nullable=False)
    ke = Column(Integer, nullable=False)
    iso_request = Column(String(2048), nullable=False)


class JsonLog(Base):
    __tablename__ = 'pbb_payment'
    id = Column(Integer, primary_key=True)
    propinsi = Column(String(2), nullable=False)
    kabupaten = Column(String(2), nullable=False)
    kecamatan = Column(String(3), nullable=False)
    kelurahan = Column(String(3), nullable=False)
    blok = Column(String(3), nullable=False)
    urut = Column(String(4), nullable=False)
    jenis = Column(String(1), nullable=False)
    tahun = Column(Integer, nullable=False)
    ke = Column(Integer, nullable=False)
    kd_kanwil_bank = Column(String(2), nullable=False)
    kd_kppbb_bank = Column(String(2), nullable=False)
    kd_bank_tunggal = Column(String(2), nullable=False)
    kd_bank_persepsi = Column(String(2), nullable=False)
    kd_tp = Column(String(2), nullable=False)
    channel = Column(String(4))
    ntb = Column(String(64))
    ntp = Column(String(64))
    bank = Column(Integer)
    iso_request = Column(String(2048), nullable=False)
    __table_args__ = (
        UniqueConstraint('propinsi', 'kabupaten', 'kecamatan', 'kelurahan',
                         'blok', 'urut', 'jenis', 'tahun', 'ke'),
        dict(schema='public'))


class App(BaseApp):
    conf_name = 'pbb payment last date'
    report_orm = Pbb
    va_product_code = '02'

    def __init__(self, argv):
        super().__init__(argv)
        self.set_other_db()
        if self.prod_session:
            d = Doc()
            self.iso_inquiry_orm = d.iso_inquiry_model
            try:
                self.prod_session.query(d.iso_payment_model).first()
                self.iso_payment_orm = d.iso_payment_model
            except DatabaseError:
                self.prod_session.rollback()
                self.iso_payment_orm = AlternativePayment
            self.base_q_iso = self.prod_session.query(
                self.iso_payment_orm, self.iso_inquiry_orm).filter(
                self.iso_payment_orm.inquiry_id == self.iso_inquiry_orm.id)
        if 'models' in self.conf:
            self.Psppt = self.models.PembayaranSppt
            self.Sppt = self.models.Sppt
            self.base_q_sppt = self.prod_session.query(self.Sppt)
            self.kd_tp = str2dict(self.conf.get('kd_tp', ''))

    def set_other_db(self):
        if 'h2h_db_url' in self.conf:
            factory = self.get_factory('h2h_db_url')
            self.h2h_session = factory()
            try:
                self.h2h_session.query(UniversalLog.bits).first()
                self.is_universal_log = True
            except ProgrammingError:
                self.h2h_session.rollback()
                self.is_universal_log = False
            self.base_q_log = self.get_base_q_log()
        elif self.prod_session:
            try:
                self.prod_session.query(Log).first()
                self.h2h_session = self.prod_session
                self.base_q_log = self.get_base_q_log()
            except DatabaseError:
                self.prod_session.rollback()
                self.h2h_session = None
        if 'json_db_url' in self.conf:
            factory = self.get_factory('json_db_url')
            self.json_session = factory()
            self.base_q_json = self.json_session.query(JsonLog)
        else:
            self.json_session = None

    def get_log_model(self):
        return self.is_universal_log and UniversalLog or Log

    def get_base_q_log(self):
        Log = self.get_log_model()
        q = self.h2h_session.query(Log).filter_by(mti='0210')
        if self.is_universal_log:
            return q.filter(
                    Log.bits.op('->>')('3') == PAYMENT_CODE,
                    Log.bits.op('->>')('39') == '00')
        return q.filter_by(bit_003=PAYMENT_CODE, bit_039='00')

    def get_sppt(self, psppt):
        q = self.base_q_sppt.filter_by(
                kd_propinsi=psppt.kd_propinsi,
                kd_dati2=psppt.kd_dati2,
                kd_kecamatan=psppt.kd_kecamatan,
                kd_kelurahan=psppt.kd_kelurahan,
                kd_blok=psppt.kd_blok,
                no_urut=psppt.no_urut,
                kd_jns_op=psppt.kd_jns_op,
                thn_pajak_sppt=psppt.thn_pajak_sppt)
        return q.first()

    def get_iso_row(self, psppt):
        iso_inq = self.iso_inquiry_orm
        awal = psppt.tgl_rekam_byr_sppt.date()
        akhir = awal + one_day
        q = self.get_iso_query(psppt)
        q = q.filter(iso_inq.tgl >= awal, iso_inq.tgl < akhir)
        return q.first()

    def get_iso_query(self, psppt):
        iso_inq = self.iso_inquiry_orm
        q = self.base_q_iso.filter_by(
                propinsi=psppt.kd_propinsi,
                kabupaten=psppt.kd_dati2,
                kecamatan=psppt.kd_kecamatan,
                kelurahan=psppt.kd_kelurahan,
                blok=psppt.kd_blok,
                urut=psppt.no_urut,
                jenis=psppt.kd_jns_op,
                tahun=psppt.thn_pajak_sppt,
                ke=psppt.pembayaran_sppt_ke)
        return q.order_by(iso_inq.id.desc())

    def warning_date(self, psppt):
        q = self.get_iso_query(psppt)
        row = q.first()
        if row:
            pay, inq = row
            msg = f'Invoice ID {self.invoice_id} berbeda tanggal dengan '\
                  f'ISO8583 yaitu {psppt.tgl_rekam_byr_sppt.date()} vs '\
                  f'{inq.tgl}'
            self.log.warning(msg)

    def get_iso_v1(self, psppt):
        if self.h2h_session:
            return
        row = self.get_iso_row(psppt)
        if not row:
            self.warning_date(psppt)
            return
        row_pay, row_inq = row
        iso = get_iso(row_pay.iso_request, Doc, self.option.debug)
        info = get_channel_info_by_iso(iso)
        try:
            user_id = iso.getBit(107)
        except BitNotSet:
            user_id = None
        try:
            channel_id = iso.get_channel().strip()
        except BitNotSet:
            channel_id = '0000'
        return channel_id, info['channel'], iso.get_stan(), \
            iso.get_ntb(), str(iso.get_bank_id()), user_id, row_inq.tgl

    def get_iso_v2(self):
        if not self.h2h_session:
            return
        Log = self.get_log_model()
        q = self.base_q_log
        if self.is_universal_log:
            q = q.filter(Log.bits.op('->>')('61') == self.invoice_id)
        else:
            q = q.filter_by(bit_061=self.invoice_id)
        q = q.order_by(Log.id.desc())
        row = q.first()
        if not row:
            return
        if self.is_universal_log:
            channel_id = row.bits['18'].strip()
            channel_nama = get_channel_name(
                row.bits['18'], row.bits['32'], row.bits['41'], row.bits['42'],
                row.bits['43'])
            return channel_id, channel_nama, row.bits['11'], \
                row.bits['48'].strip(), row.bits['32'], row.bits['107']
        channel_id = row.bit_018.strip()
        channel_nama = get_channel_name_by_row(row)
        return channel_id, channel_nama, row.bit_011, \
            row.bit_048.strip(), row.bit_032, row.bit_107

    def get_json(self, psppt):
        if not self.json_session:
            return
        q = self.base_q_json.filter_by(
                propinsi=psppt.kd_propinsi,
                kabupaten=psppt.kd_dati2,
                kecamatan=psppt.kd_kecamatan,
                kelurahan=psppt.kd_kelurahan,
                blok=psppt.kd_blok,
                urut=psppt.no_urut,
                jenis=psppt.kd_jns_op,
                tahun=psppt.thn_pajak_sppt,
                ke=psppt.pembayaran_sppt_ke)
        row = q.first()
        if not row:
            return
        d = eval(row.iso_request)
        kode = d['bit_032']
        nama = get_channel_name_by_dict(d)
        return kode, nama, d['bit_011'], row.ntb, kode

    def get_source(self, psppt):
        source = self.get_iso_v2()
        tgl_inquiry = user_id = stan = ntb = bank_id = None
        if source:
            channel_kode, channel_nama, stan, ntb, bank_id, user_id = source
        else:
            source = self.get_iso_v1(psppt)
            if source:
                channel_kode, channel_nama, stan, ntb, bank_id, user_id, \
                    tgl_inquiry = source
            else:
                source = self.get_json(psppt)
                if source:
                    channel_kode, channel_nama, stan, ntb, bank_id = source
                else:
                    channel_kode = '0000'
                    tgl = psppt.tgl_rekam_byr_sppt.date()
                    channel_nama = self.get_va_channel(tgl)
                    if not channel_nama:
                        kd_tp = psppt.kd_tp.strip()
                        channel_nama = self.kd_tp.get(kd_tp, 'MANUAL')
        return channel_kode, channel_nama, stan, ntb, bank_id, user_id, \
            tgl_inquiry

    def create_data(self, psppt):  # Override
        self.invoice_id = get_id(psppt)
        sppt = self.get_sppt(psppt)
        if not sppt:
            msg = f'Invoice ID {self.invoice_id} tidak ada di tabel sppt'
            raise InvalidSource(msg)
        channel_kode, channel_nama, stan, ntb, bank_id, user_id, \
            tgl_inquiry = self.get_source(psppt)
        try:
            bayar = psppt.jml_sppt_yg_dibayar
            denda = psppt.denda_sppt
            pokok = sppt.pbb_yg_harus_dibayar_sppt
        except AttributeError:
            bayar = psppt.jml_pbb_yg_dibayar
            denda = psppt.jml_denda_sppt
            pokok = sppt.pbb_yg_hrs_dibayar_sppt
        discount = hasattr(psppt, 'discount') and psppt.discount or None
        tgl = to_date(psppt.tgl_pembayaran_sppt)
        if ntb:
            ntb = ntb.strip()
        return dict(
            kd_propinsi=psppt.kd_propinsi, kd_dati2=psppt.kd_dati2,
            kd_kecamatan=psppt.kd_kecamatan, kd_kelurahan=psppt.kd_kelurahan,
            kd_blok=psppt.kd_blok, no_urut=psppt.no_urut,
            kd_jns_op=psppt.kd_jns_op, thn_pajak_sppt=psppt.thn_pajak_sppt,
            pembayaran_sppt_ke=psppt.pembayaran_sppt_ke, stan=stan, ntb=ntb,
            jml_sppt_yg_dibayar=bayar, denda_sppt=denda or 0,
            discount=discount or 0, tgl_pembayaran_sppt=tgl,
            tgl_inquiry=tgl_inquiry,
            tgl_rekam_byr_sppt=psppt.tgl_rekam_byr_sppt,
            nm_wp_sppt=sppt.nm_wp_sppt, channel_kode=channel_kode,
            channel_nama=channel_nama, bank_id=bank_id, user_id=user_id,
            pbb_yg_harus_dibayar_sppt=pokok)

    def get_report(self, psppt):  # Override
        q = self.rpt_session.query(Pbb).filter_by(
                 kd_propinsi=psppt.kd_propinsi,
                 kd_dati2=psppt.kd_dati2,
                 kd_kecamatan=psppt.kd_kecamatan,
                 kd_kelurahan=psppt.kd_kelurahan,
                 kd_blok=psppt.kd_blok,
                 no_urut=psppt.no_urut,
                 kd_jns_op=psppt.kd_jns_op,
                 thn_pajak_sppt=psppt.thn_pajak_sppt,
                 pembayaran_sppt_ke=psppt.pembayaran_sppt_ke)
        return q.first()

    def get_last_time(self):  # Override
        return dmyhms(self.last_pay.tgl_rekam_byr_sppt)

    def get_filter_query(self, q):
        return q.filter(
            self.Psppt.tgl_rekam_byr_sppt >= self.tgl_awal,
            self.Psppt.tgl_rekam_byr_sppt < self.tgl_akhir + one_day)

    def get_count(self):  # Override
        q = self.prod_session.query(func.count())
        q = self.get_filter_query(q)
        return q.scalar()

    def get_payment_query(self):  # Override
        q = self.prod_session.query(self.Psppt)
        q = self.get_filter_query(q)
        return q.order_by(self.Psppt.tgl_rekam_byr_sppt)