bphtb.py

import sys
import pdb
from datetime import datetime, timedelta
from sqlalchemy import (
    Column,
    Integer,
    DateTime,
    String,
    Text,
    Date, ForeignKey,
    UniqueConstraint,
    func,
    )
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.exc import (
    ProgrammingError,
    OperationalError,
    )
from opensipkd.string import FixLength
from opensipkd.waktu import dmyhms
from opensipkd.bphtb.models.customer import CustomerMixin
from opensipkd.bphtb.models.perolehan import PerolehanMixin
from opensipkd.bphtb.models.invoice import InvoiceMixin
from opensipkd.bphtb.models.payment import PaymentMixin
from opensipkd.bphtb.models.default import (
    Payment,
    Invoice,
    Perolehan,
    Customer,
    )
from opensipkd.bphtb.services.default.structure import INVOICE_ID
from iso8583_web.models.meta import Base as BaseConf
from opensipkd.iso8583.bjb.bphtb import Doc
from opensipkd.iso8583.bjb.bphtb.structure import PAYMENT_CODE
from opensipkd.iso8583.bjb.bphtb.models import Log
from ..models import (
    Base,
    Bphtb,
    )
from .common import (
    get_iso,
    get_channel_name_by_row,
    get_channel_info_by_iso,
    BaseApp,
    init_db as base_init_db,
    one_day,
    InvalidSource,
    BANK_NAMES,
    )


class AlternativeCustomer(CustomerMixin, Base):
    @declared_attr
    def __table_args__(self):
        return dict(schema='public')


class AlternativePerolehan(PerolehanMixin, Base):
    @declared_attr
    def __table_args__(self):
        return dict(schema='public')


class AlternativeInvoice(InvoiceMixin, Base):
    @declared_attr
    def __table_args__(self):
        return (
            UniqueConstraint('tahun', 'kode', 'no_sspd'),
            dict(schema='public'))


class AlternativePayment(PaymentMixin, Base):
    @declared_attr
    def __table_args__(self):
        return (
            UniqueConstraint('tanggal', 'jam', 'seq', 'transno'),
            dict(schema='public'))


class IsoPayment(Base):
    __tablename__ = 'bphtb_payment'
    __table_args__ = dict(schema='bphtb')
    id = Column(Integer, ForeignKey(Payment.id), primary_key=True)
    tgl = Column(DateTime(timezone=True), nullable=False)
    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_id = Column(Integer, ForeignKey(Invoice.id), 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)
    channel_id = Column(Integer)
    bank_ip = Column(String(15), nullable=False)


class SukabumiKotaLog(Base):
    __tablename__ = 'iso_log'
    id = Column(Integer, primary_key=True)
    created = Column(DateTime(timezone=True), nullable=False)
    mti = Column(String(4), nullable=False)
    bit_003 = Column(Text)
    bit_011 = Column(Text)
    bit_018 = Column(Text)
    bit_032 = Column(Text)
    bit_039 = Column(Text)
    bit_041 = Column(Text)
    bit_042 = Column(Text)
    bit_043 = Column(Text)
    bit_058 = Column(Text)
    bit_062 = Column(Text)


class App(BaseApp):
    conf_name = 'bphtb payment last date'
    report_orm = Bphtb
    va_product_code = '01'

    def __init__(self, argv):
        super().__init__(argv)
        if not self.pid:
            return
        self.base_q_func = self.prod_session.query(func.count())
        self.is_sukabumi_kota = False
        try:
            self.set_default_models()
        except (ProgrammingError, OperationalError):
            self.prod_session.rollback()
            try:
                self.set_alternative_models()
            except (ProgrammingError, OperationalError):
                self.set_sukabumi_kota_models()
        self.set_h2h_db()

    def get_log_model(self):
        if self.is_sukabumi_kota:
            return SukabumiKotaLog
        return Log

    def get_base_q_log(self):
        Log = self.get_log_model()
        return self.h2h_session.query(Log).filter_by(
                    mti='0210', bit_003=PAYMENT_CODE, bit_039='00')

    def set_h2h_db(self):
        if 'h2h_db_url' in self.conf:
            factory = self.get_factory('h2h_db_url')
            self.h2h_session = factory()
        else:
            self.h2h_session = self.prod_session
        self.base_q_log = self.get_base_q_log()
        try:
            self.base_q_log.first()
        except ProgrammingError:
            self.h2h_session.rollback()
            # Berarti Log ISO8583 masih di tabel versi 1
            self.base_q_log = None

    def set_default_models(self):
        self.base_q_inv = self.prod_session.query(Invoice)
        self.base_q_inv.first()
        self.Customer = Customer
        self.Perolehan = Perolehan
        self.Invoice = Invoice
        self.Payment = Payment
        self.base_q_cust = self.prod_session.query(self.Customer)
        self.base_q_perolehan = self.prod_session.query(self.Perolehan)
        self.base_q_pay = self.prod_session.query(self.Payment)

    def set_alternative_models(self):
        self.base_q_inv = self.prod_session.query(AlternativeInvoice)
        self.base_q_inv.first()
        self.Customer = AlternativeCustomer
        self.Perolehan = AlternativePerolehan
        self.Invoice = AlternativeInvoice
        self.Payment = AlternativePayment
        self.base_q_cust = self.prod_session.query(AlternativeCustomer)
        self.base_q_perolehan = self.prod_session.query(
                AlternativePerolehan)
        self.base_q_pay = self.prod_session.query(AlternativePayment)

    def set_sukabumi_kota_models(self):
        from opensipkd.bphtb.models.sukabumi_kota import Invoice, Payment
        self.is_sukabumi_kota = True
        self.Invoice = Invoice
        self.Payment = Payment
        self.base_q_inv = self.prod_session.query(self.Invoice)
        self.base_q_pay = self.prod_session.query(self.Payment)

    def get_last_time(self):  # Override
        if self.is_sukabumi_kota:
            return self.last_pay.tgl_rekam.strftime('%d-%m-%Y %H:%M:%S')
        s_tgl = self.last_pay.tanggal.strftime('%d-%m-%Y')
        s_jam = self.last_pay.jam.strftime('%H:%M:%S')
        return f'{s_tgl} {s_jam}'

    def get_filter_query(self, q):
        if isinstance(self.tgl_awal, datetime):
            return q.filter(
                    self.Payment.tanggal >= self.tgl_awal.date(),
                    self.Payment.jam >= self.tgl_awal.time(),
                    self.Payment.tanggal < self.tgl_akhir + one_day)
        return q.filter(
                self.Payment.tanggal >= self.tgl_awal,
                self.Payment.tanggal < self.tgl_akhir + one_day)

    def get_filter_query_sukabumi_kota(self, q):
        return q.filter(
                self.Payment.tgl_pembayaran >= self.tgl_awal,
                self.Payment.tgl_pembayaran < self.tgl_akhir + one_day)

    def get_count(self) -> int:  # Override
        if self.is_sukabumi_kota:
            q = self.get_filter_query_sukabumi_kota(self.base_q_func)
        else:
            q = self.get_filter_query(self.base_q_func)
        return q.scalar()

    def get_payment_query(self):  # Override
        if self.is_sukabumi_kota:
            q = self.get_filter_query_sukabumi_kota(self.base_q_pay)
            return q.order_by(self.Payment.tgl_pembayaran, self.Payment.id)
        q = self.get_filter_query(self.base_q_pay)
        return q.order_by(self.Payment.tanggal, self.Payment.jam)

    def get_iso_v1(self, pay):
        q = self.prod_session.query(IsoPayment).filter_by(id=pay.id)
        row = q.first()
        if not row:
            return
        if row.iso_request[0] == '{':
            channel = BANK_NAMES[str(row.bank_id)]
            return '0000', channel, None, None
        iso = get_iso(row.iso_request, Doc, self.option.debug)
        info = get_channel_info_by_iso(iso)
        return info.get('bit_018', '0000'), info['channel'], iso.get_stan(), \
            iso.get_ntb()

    def get_iso_v2(self):
        if not self.base_q_log:
            return
        Log = self.get_log_model()
        q = self.base_q_log.filter(func.trim(Log.bit_062) == self.invoice_id)
        q = q.order_by(Log.id.desc())
        row = q.first()
        if not row:
            return
        channel_id = row.bit_018.strip()
        channel_nama = get_channel_name_by_row(row)
        return channel_id, channel_nama, row.bit_011, row.bit_058.strip()

    def get_invoice(self, pay):
        if self.is_sukabumi_kota:
            q = self.base_q_inv.filter_by(id=pay.id_tagihan)
        else:
            q = self.base_q_inv.filter_by(id=pay.sspd_id)
        return q.first()

    def get_customer(self, inv):
        q = self.base_q_cust.filter_by(id=inv.ppat_id)
        return q.first()

    def get_perolehan(self, inv):
        q = self.base_q_perolehan.filter_by(id=inv.perolehan_id)
        return q.first()

    def create_data_sukabumi_kota(self, pay):
        inv = self.get_invoice(pay)
        if not inv:
            msg = f'Invoice ID {pay.id_tagihan} tidak ditemukan di '\
                    'tabel tagihan_bphtb'
            raise InvalidSource(msg)
        self.invoice_id = str(inv.no_tagihan)
        source = self.get_iso_v2()
        if source:
            channel_id, channel_nama, stan, ntb = source
        else:
            stan = ntb = None
            channel_id = '0000'
            channel_nama = 'MANUAL'
        return dict(
            id=pay.id, stan=stan, ntb=ntb, tgl=pay.tgl_pembayaran.date(),
            jam=pay.tgl_pembayaran.time(), invoice_id=self.invoice_id,
            nop=inv.nop, wp_nama=inv.nama_wp.strip(), wp_alamat=inv.alamat_wp,
            op_alamat=inv.alamat_op, npop=inv.npop, bumi_luas=inv.luas_tanah,
            bng_luas=inv.luas_bangunan, nilai_bphtb=pay.jumlah_yg_dibayar,
            jenis_perolehan=str(inv.jns_perolehan_hak), ppat=inv.nama_notaris,
            channel_id=channel_id, channel_nama=channel_nama)

    def create_data(self, pay):  # Override
        if self.is_sukabumi_kota:
            return self.create_data_sukabumi_kota(pay)
        if not pay.sspd_id:
            msg = 'Field bphtb_bank.sspd_id NULL'
            raise InvalidSource(msg)
        inv = self.get_invoice(pay)
        if not inv:
            msg = f'Field bphtb_bank.sspd_id {pay.sspd_id} tidak ada di '\
                'field bphtb_sspd.id'
            raise InvalidSource(msg)
        cust = self.get_customer(inv)
        perolehan = self.get_perolehan(inv)
        invoice_id = FixLength(INVOICE_ID)
        invoice_id['Tahun'] = inv.tahun
        invoice_id['Kode'] = inv.kode
        invoice_id['SSPD No'] = inv.no_sspd
        self.invoice_id = invoice_id.get_raw()
        source = self.get_iso_v2()
        if source:
            channel_id, channel_nama, stan, ntb = source
        else:
            source = self.get_iso_v1(pay)
            if source:
                channel_id, channel_nama, stan, ntb = source
            else:
                stan = ntb = None
                channel_id = '0000'
                channel_nama = self.get_va_channel(pay.tanggal) or 'MANUAL'
        return dict(
            id=pay.id, stan=stan, ntb=ntb, tgl=pay.tanggal, jam=pay.jam,
            invoice_id=self.invoice_id, nop=pay.nop, wp_nama=pay.wp_nama,
            wp_alamat=pay.wp_alamat, op_alamat=inv.op_alamat, npop=pay.npop,
            bumi_luas=pay.bumi_luas, bng_luas=pay.bng_luas,
            nilai_bphtb=pay.bayar, jenis_perolehan=perolehan.nama,
            ppat=cust.nama.strip(), channel_id=channel_id,
            channel_nama=channel_nama)


def main(argv=sys.argv[1:]):
    app = App(argv)
    if app.pid:
        app.run()


def init_db(argv=sys.argv[1:]):
    base_init_db(Base.metadata, BaseConf.metadata, argv)