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,
    DatabaseError,
    NoReferencedTableError,
    )
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.structure import NOP
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.models import Log as JsonLog
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_name,
    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 IsoPaymentMixin:
    __tablename__ = 'bphtb_payment'
    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_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)

    @declared_attr
    def id(self):
        return Column(Integer, ForeignKey(Payment.id), primary_key=True)

    @declared_attr
    def invoice_id(self):
        return Column(Integer, ForeignKey(Invoice.id), nullable=False)


class IsoPayment(IsoPaymentMixin, Base):
    __table_args__ = dict(schema='bphtb')


class AlternativeIsoPayment(IsoPaymentMixin, Base):
    pass


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)


def cartenz_invoice_id(pay):
    return pay.thn_bphtb + pay.bln_bphtb + pay.tgl_bphtb + \
                pay.no_urut_bphtb


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
        self.is_cartenz = False
        try:
            self.set_default_models()
        except (ProgrammingError, OperationalError, DatabaseError):
            self.prod_session.rollback()
            try:
                self.set_alternative_models()
            except (
                    ProgrammingError, OperationalError,
                    NoReferencedTableError):
                try:
                    self.set_sukabumi_kota_models()
                except DatabaseError:
                    self.set_cartenz_models()
        self.set_h2h_db()

    def get_log_model(self):
        if self.is_sukabumi_kota:
            return SukabumiKotaLog
        return self.is_json_log and JsonLog 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_json_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 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
        try:
            self.h2h_session.query(JsonLog.bits).first()
            self.is_json_log = True
        except ProgrammingError:
            self.h2h_session.rollback()
            self.is_json_log = False
        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
        self.base_q_iso_payment = self.prod_session.query(IsoPayment)
        try:
            self.base_q_iso_payment.first()
        except ProgrammingError:
            self.prod_session.rollback()
            self.base_q_iso_payment = self.prod_session.query(
                AlternativeIsoPayment)

    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.base_q_inv = self.prod_session.query(Invoice)
        self.base_q_inv.first()
        self.is_sukabumi_kota = True
        self.Invoice = Invoice
        self.Payment = Payment
        self.base_q_inv_kb = self.prod_session.query(Skbkb)
        self.base_q_inv_kbt = self.prod_session.query(Skbkbt)
        self.base_q_pay = self.prod_session.query(Payment)

    def set_cartenz_models(self):
        from opensipkd.bphtb.models.serang_kab import Payment
        import opensipkd.bphtb.services.base
        from opensipkd.bphtb.services.serang_kab import Inquiry
        opensipkd.bphtb.services.base.DBSession = self.prod_session
        self.is_cartenz = True
        self.Payment = Payment
        self.Inquiry = Inquiry
        self.base_q_pay = self.prod_session.query(Payment)

    def get_report_cartenz(self, pay):
        session = self.get_session_for_save()
        invoice_id = cartenz_invoice_id(pay)
        q = session.query(self.report_orm).filter_by(invoice_id=invoice_id)
        return q.first()

    def get_report(self, pay):  # Override
        if self.is_cartenz:
            return self.get_report_cartenz(pay)
        return super().get_report(pay)

    def get_last_time(self):  # Override
        if self.is_sukabumi_kota:
            return self.last_pay.tgl_rekam.strftime('%d-%m-%Y %H:%M:%S')
        if self.is_cartenz:
            return self.last_pay.tgl_rekam_byr.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_filter_query_cartenz(self, q):
        return q.filter(
                self.Payment.tgl_rekam_byr >= self.tgl_awal,
                self.Payment.tgl_rekam_byr < 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)
        elif self.is_cartenz:
            q = self.get_filter_query_cartenz(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)
        if self.is_cartenz:
            q = self.get_filter_query_cartenz(self.base_q_pay)
            return q.order_by(
                    self.Payment.tgl_rekam_byr, self.Payment.thn_bphtb,
                    self.Payment.bln_bphtb, self.Payment.tgl_bphtb,
                    self.Payment.no_urut_bphtb)
        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.base_q_iso_payment.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()
        if self.is_json_log:
            q = self.base_q_log.filter(
                    func.trim(Log.bits.op('->>')('62')) == self.invoice_id)
        else:
            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
        if self.is_json_log:
            channel_id = row.bits['18'].strip()
            stan = row.bits['11']
            ntb = row.bits['58'].strip()
            channel_name = get_channel_name(
                row.bits['18'], row.bits['32'], row.bits['41'], row.bits['42'],
                row.bits['43'])
        else:
            channel_id = row.bit_018.strip()
            stan = row.bit_011
            ntb = row.bit_058.strip()
            channel_name = get_channel_name_by_row(row)
        return channel_id, channel_name, stan, ntb

    def get_invoice_cartenz(self, pay):
        if pay.no_transaksi_byr in ('KB', 'KBT'):
            if pay.no_transaksi_byr == 'KB':
                q = self.base_q_inv_kb
            else:
                q = self.base_q_inv_kbt
            q = q.filter_by(
                    thn_bphtb_terbit=pay.thn_bphtb,
                    bln_bphtb_terbit=pay.bln_bphtb,
                    tgl_bphtb_terbit=pay.tgl_bphtb,
                    no_urut_bphtb_terbit=pay.no_urut_bphtb)
            row = q.first()
        else:
            row = pay
        q = self.base_q_inv.filter_by(
                thn_bphtb=row.thn_bphtb,
                bln_bphtb=row.bln_bphtb,
                tgl_bphtb=row.tgl_bphtb,
                no_urut_bphtb=row.no_urut_bphtb)
        return q.first()

    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_cartenz(self, pay):
        self.invoice_id = cartenz_invoice_id(pay)
        inq = self.Inquiry(self.invoice_id, None, for_test=True)
        if not inq.invoice:
            msg = f'Invoice ID {self.invoice_id} tidak ditemukan'
            raise InvalidSource(msg)
        source = self.get_iso_v2()
        if source:
            channel_id, channel_nama, stan, ntb = source
        else:
            stan = ntb = None
            channel_id = '0000'
            channel_nama = 'MANUAL'
        inv = inq.invoice
        nop_struct = FixLength(NOP)
        nop_struct.from_dict({
            'Propinsi': inv.kd_propinsi,
            'Kabupaten': inv.kd_dati2,
            'Kecamatan': inv.kd_kecamatan_op,
            'Kelurahan': inv.kd_kelurahan_op,
            'Blok': inv.kd_blok_op,
            'Urut': inv.no_urut_op,
            'Jenis': inv.kd_jns_op})
        nop = nop_struct.get_raw()
        return dict(
            stan=stan, ntb=ntb, tgl=pay.tgl_rekam_byr.date(),
            jam=pay.tgl_rekam_byr.time(), invoice_id=self.invoice_id,
            nop=nop, wp_nama=inq.get_nama(), wp_alamat=inq.get_alamat_wp(),
            op_alamat=inq.get_alamat_op(), npop=inq.get_npop(),
            bumi_luas=inq.get_luas_tanah(), bng_luas=inq.get_luas_bangunan(),
            nilai_bphtb=pay.bphtb_sdh_dibayar,
            jenis_perolehan=inv.kd_jns_hak, ppat=inq.get_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 self.is_cartenz:
            return self.create_data_cartenz(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)