default.py

from datetime import (
    datetime,
    date,
    )
from ISO8583.ISOErrors import BitNotSet
from sqlalchemy import (
    Column,
    Integer,
    DateTime,
    String,
    Text,
    Date, ForeignKey,
    UniqueConstraint,
    func,
    or_,
    and_,
    )
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.default import Invoice
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.kecamatan import KecamatanMixin
from opensipkd.bphtb.models.kelurahan import KelurahanMixin
from opensipkd.bphtb.models.default import (
    Payment,
    Invoice,
    Perolehan,
    Customer,
    Kecamatan,
    Kelurahan,
    )
from opensipkd.bphtb.structure import NOP
from opensipkd.bphtb.services.default.structure import INVOICE_ID
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,
    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 AlternativeLog(Base):
    __tablename__ = 'log_iso'
    id = Column(Integer, nullable=False, primary_key=True)
    created = Column(DateTime(timezone=True), nullable=False)
    ip = Column(String(15))
    mti = Column(String(4), nullable=False)
    bit_003 = Column(Text)
    bit_004 = Column(Text)
    bit_011 = Column(Text)
    bit_018 = Column(Text)
    bit_032 = Column(Text)
    bit_033 = Column(Text)
    bit_037 = Column(Text)
    bit_039 = Column(Text)
    bit_041 = Column(Text)
    bit_042 = Column(Text)
    bit_043 = Column(Text)
    bit_057 = Column(Text)
    bit_058 = Column(Text)
    bit_061 = Column(Text)
    bit_062 = Column(Text)


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

    def get_log_model(self):
        if self.is_sukabumi_kota:
            try:
                self.h2h_session.query(SukabumiKotaLog).first()
            except ProgrammingError:
                self.h2h_session.rollback()
                return AlternativeLog
            return SukabumiKotaLog
        if self.is_json_log:
            return JsonLog
        try:
            self.h2h_session.query(Log).first()
        except ProgrammingError:
            self.h2h_session.rollback()
            return AlternativeLog
        return 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, OperationalError):
            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.Kecamatan = Kecamatan
        self.Kelurahan = Kelurahan
        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)
        self.base_q_kecamatan = self.prod_session.query(self.Kecamatan)
        self.base_q_kelurahan = self.prod_session.query(self.Kelurahan)

    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_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(
                    or_(
                        and_(
                            self.Payment.tanggal == self.tgl_awal.date(),
                            self.Payment.jam >= self.tgl_awal.time()),
                        and_(
                            self.Payment.tanggal > self.tgl_awal.date(),
                            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)
        try:
            ntb = iso.get_ntb()
        except BitNotSet:
            ntb = None
        channel_id = info.get('bit_018', '0000')
        return channel_id, info['channel'], iso.get_stan(), 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 get_kecamatan_nama(self, inv):
        q = self.base_q_kecamatan.filter_by(kd_propinsi=inv.kd_propinsi, kd_dati2=inv.kd_dati2, kd_kecamatan=inv.kd_kecamatan)
        return q.first()

    def get_kelurahan_nama(self, inv):
        q = self.base_q_kelurahan.filter_by(kd_propinsi=inv.kd_propinsi, kd_dati2=inv.kd_dati2, kd_kecamatan=inv.kd_kecamatan, kd_kelurahan=inv.kd_kelurahan)
        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,
            status_pembayaran=inv.status_pembayaran)

    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)
        kecamatan = self.get_kecamatan_nama(inv)
        kelurahan = self.get_kelurahan_nama(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'
        if pay.tanggal > date.today():
            print(f'DEBUG self.tgl_akhir {self.tgl_akhir}')
            raise Exception(
                    f'{pay.tanggal} adalah masa depan. Perbaiki script.')
        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, kecamatan_op=kecamatan.nm_kecamatan, 
            kelurahan_op=kelurahan.nm_kelurahan)