common.py

import sys
import os
import logging
import csv
from time import time
from logging import getLogger
from datetime import (
    date,
    datetime,
    timedelta,
    )
from ISO8583.ISO8583 import BitNotSet
from sqlalchemy import (
    Column,
    String,
    Integer,
    BigInteger,
    ForeignKey,
    create_engine,
    func,
    )
from sqlalchemy.orm import sessionmaker
import transaction
from zope.sqlalchemy import register
from opensipkd.views.models import Conf as BaseConf
from opensipkd.iso8583.bjb.scripts.common import get_module_object
from .models import Base
from .tools import (
    plain_values,
    update,
    )


one_day = timedelta(1)
row_limit = 1000

log_format = '%(asctime)s %(levelname)s %(message)s'
formatter = logging.Formatter(log_format)

BIT_18_NAMES = {
    '6010': 'TELLER',
    '6011': 'ATM',
    '6012': 'POS',
    '6013': 'PHONE BANKING',
    '6014': 'INTERNETBANKING',
    '6015': 'KIOSK',
    '6016': 'AUTODEBET',
    '6017': 'MOBILBANKING',
    '7012': 'PTPOS'}

# Bit 41, 42, dan 43
BIT_PROFILE_NAMES = {
    'INDOMARET': 'INDOMARET',
    'ALFAMART': 'ALFAMART',
    'LINKAJA': 'LINKAJA',
    'GOPAY': 'GOPAY',
    'EMONEY': 'DIGICASH',
    'PTPOS': 'PTPOS',
    'TOKOPEDI': 'TOKOPEDIA',
    'BUKALAPA': 'BUKALAPAK',
    'MASAGO': 'MASAGO',
    'BAYARIN': 'BAYARIN',
    'TRAVELOK': 'TRAVELOKA',
    'SHOPEE': 'SHOPEE',
    'OVO': 'OVO',
    'BLIBLI': 'BLIBLI',
    'ARTPAY': 'ARTPAY',
    'H646': 'DANA',
    'ALFAMIDI': 'ALFAMIDI'}

BANK_NAMES = {
    '8': 'MANDIRI',
    '14': 'BCA',
    '700': 'PTPOS'}

my_registry = dict()


class InvalidSource(Exception):
    pass


class VaInvoice(Base):
    __tablename__ = 'bjb_va_invoice'
    id = Column(Integer, primary_key=True)
    va_type = Column(String(8))
    product_code = Column(String(16))
    invoice_no = Column(String(64))


class VaPayment(Base):
    __tablename__ = 'bjb_va_payment'
    id = Column(Integer, primary_key=True)
    va_invoice_id = Column(Integer, ForeignKey(VaInvoice.id))
    transaction_date = Column(String(32))
    transaction_amount = Column(BigInteger)
    rrn = Column(String(32))
    customer_name = Column(String(64))


def humanize_time(secs):
    mins, secs = divmod(secs, 60)
    hours, mins = divmod(mins, 60)
    return '%02d:%02d:%02d' % (hours, mins, secs)


def get_file(filename):
    base_dir = os.path.split(__file__)[0]
    fullpath = os.path.join(base_dir, 'scripts', 'data', filename)
    return open(fullpath)


def append_csv(table, filename, keys):
    DBSession = my_registry['db_session']
    with get_file(filename) as f:
        reader = csv.DictReader(f)
        filter_ = dict()
        for cf in reader:
            for key in keys:
                filter_[key] = cf[key]
            q = DBSession.query(table).filter_by(**filter_)
            found = q.first()
            if found:
                continue
            row = table()
            for fieldname in cf:
                val = cf[fieldname]
                if not val:
                    continue
                setattr(row, fieldname, val)
            DBSession.add(row)


def clean_raw(raw):
    if raw[:2] == '\\x':
        raw = raw[2:]
    if raw[:4] == '3032':
        return bytes.fromhex(raw)
    return raw.encode('utf8')


def get_iso(raw, iso_class, debug=False):
    raw = clean_raw(raw)
    iso = iso_class(debug=debug)
    iso.setIsoContent(raw)
    return iso


def get_channel_name(bit_018, bit_032, bit_041, bit_042, bit_043):
    bit_032 = bit_032 and bit_032.lstrip('0')
    if bit_032 and bit_032 in BANK_NAMES:
        return BANK_NAMES[bit_032]
    if not bit_018:
        return 'LAINNYA'
    bit_018 = bit_018.strip()
    bit_041 = bit_041.strip()
    bit_042 = bit_042.strip()
    bit_043 = bit_043.strip()
    profile_values = [bit_041, bit_042, bit_043]
    for name in BIT_PROFILE_NAMES:
        for bit_value in profile_values:
            if bit_value.find(name) > -1:
                return BIT_PROFILE_NAMES[name]
    if bit_018 in BIT_18_NAMES:
        return BIT_18_NAMES[bit_018]
    return 'LAINNYA'


def get_channel_info_by_iso(iso):
    d = dict()
    lengkap = True
    for bit in (18, 32, 41, 42, 43):
        bit_name = f'bit_{bit}'
        try:
            d[bit_name] = iso.getBit(bit)
        except BitNotSet:
            lengkap = False
            continue
    if lengkap:
        d['channel'] = get_channel_name(
                iso.getBit(18), iso.getBit(32), iso.getBit(41), iso.getBit(42),
                iso.getBit(43))
    else:
        d['channel'] = 'LAINNYA'
    return d


def get_channel_name_by_row(row):
    if isinstance(row, dict):
        return get_channel_name_by_dict(row)
    return get_channel_name(
            row.bit_018, row.bit_032, row.bit_041, row.bit_042, row.bit_043)


def get_channel_name_by_dict(d):
    if '18' in d:
        return get_channel_name(
                d['18'], d['32'], d['41'], d['42'], d['43'])
    return get_channel_name(
            d['bit_018'], d['bit_032'], d.get('bit_041'), d.get('bit_042'),
            d.get('bit_043'))


def get_keys(iso):
    d = get_channel_info_by_iso(iso)
    d.update(dict(
        nomor_bayar=iso.get_invoice_id().strip(),
        stan=iso.get_stan().strip(),
        ntb=iso.get_ntb().strip()))
    return d


class Conf(BaseConf):
    def as_datetime(self):
        pola = '%d-%m-%Y %H:%M:%S'
        if self.nilai.find('.') > -1:
            pola += '.%f'
        return datetime.strptime(self.nilai, pola)

    def as_int(self):
        return int(self.nilai)


def create_session(db_url, debug=False):
    engine = create_engine(db_url, echo=debug)
    factory = sessionmaker(bind=engine)
    return factory()


def str2dict(s):
    r = dict()
    for line in s.split():
        t = line.split(':')
        key = t[0]
        val = t[1]
        r[key] = val
    return r


class BaseApp:
    conf_name = None  # Override, please
    report_orm = None  # Override, please
    va_product_code = ''  # Override, please

    def __init__(self, conf):
        self.conf = conf
        self.prod_session = self.models = None
        factory = self.get_factory('report_db_url')
        self.rpt_session = factory()
        register(self.rpt_session)
        if 'models' in self.conf:
            self.models = get_module_object(self.conf['models'])
        if 'service' in self.conf:
            self.service = get_module_object(self.conf['service'])
        if 'db_url' in self.conf:
            if self.conf['db_url'] == 'odbc':
                import pyodbc
                odbc_profile = self.get_prefix_config('odbc.')
                self.odbc_conn = pyodbc.connect(**odbc_profile)
            else:
                factory = self.get_factory('db_url')
                self.prod_session = factory()
        if 'h2h_db_url' in self.conf:
            factory = self.get_factory('h2h_db_url')
            self.h2h_session = factory()
        else:
            self.h2h_session = None
        if 'va_db_url' in self.conf:
            factory = self.get_factory('va_db_url')
            self.va_session = factory()
            self.base_q_va = self.va_session.query(VaPayment, VaInvoice).\
                filter(
                        VaPayment.va_invoice_id == VaInvoice.id,
                        VaInvoice.product_code == self.va_product_code)
        else:
            self.va_session = None

    def get_prefix_config(self, prefix):
        d = dict()
        for key in self.conf:
            if key.find(prefix):
                k = key[len(prefix):]
                d[k] = self.conf[key]
        return d

    def get_factory(self, name):
        db_url = self.conf[name]
        engine = create_engine(db_url)
        return sessionmaker(bind=engine)

    def get_last_id(self, nama):
        q = self.rpt_session.query(Conf).filter_by(nama=nama)
        return q.first()

    def get_payment_query(self):  # Override, please
        pass

    def create_data(self, pay):  # Override, please
        pass

    def get_report(self, pay):
        session = self.get_session_for_save()
        q = session.query(self.report_orm).filter_by(id=pay.id)
        return q.first()

    def get_prefix_log(self):
        return f'Invoice ID {self.invoice_id}'

    def get_estimate(self, no):
        duration = time() - self.start_time
        speed = duration / no
        remain_row = self.count - no
        return humanize_time(speed * remain_row)

    def get_session_for_save(self):
        return self.rpt_session

    def do_sync(self):
        q = self.get_payment_query()
        no = self.offset
        found = False
        log = getLogger('do_sync()')
        for pay in q.offset(self.offset).limit(row_limit):
            found = True
            no += 1
            try:
                source = self.create_data(pay)
                d = plain_values(source)
                rpt = self.get_report(pay)
                if rpt:
                    target = rpt.to_dict()
                    target_update, log_msg = update(source, target)
                    if target_update:
                        s = ', '.join(log_msg)
                        msg = f'UPDATE {d} change {s}'
                        rpt.from_dict(target_update)
                    else:
                        msg = f'ALREADY SAME {d}'
                        rpt = None
                        if self.count == 1 and self.last:  # Hemat log
                            print(msg)
                            print('Log yang sama, abaikan.')
                            return
                else:
                    msg = f'INSERT {d}'
                    rpt = self.report_orm(**source)
                log_method = log.info
            except InvalidSource as e:
                msg = str(e)
                log_method = log.warning
                rpt = None
            e = self.get_estimate(no)
            prefix = self.get_prefix_log()
            log_method(f'#{no}/{self.count} {prefix} {msg}, estimate {e}')
            if rpt:
                session = self.get_session_for_save()
                with transaction.manager:
                    session.add(rpt)
            self.last_pay = pay
        self.offset += row_limit
        return found

    def get_last_time(self):  # Override, please
        pass

    def update_last(self):
        self.last.nilai = self.get_last_time()
        with transaction.manager:
            self.rpt_session.add(self.last)
            self.rpt_session.flush()
            self.rpt_session.expunge_all()

    def get_filter_query(self, q):  # Override, please
        return q

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

    def prepare_query_filter(self):
        if 'tgl_awal' in self.conf:
            self.tgl_awal = self.conf['tgl_awal']
        else:
            self.last = self.get_last_id(self.conf_name)
            self.tgl_awal = self.last.as_datetime()
        if 'tgl_akhir' in self.conf:
            self.tgl_akhir = self.conf['tgl_akhir']
        else:
            self.tgl_akhir = date.today()

    def run(self):
        self.last_pay = self.last = None
        self.offset = 0
        self.prepare_query_filter()
        while True:
            self.count = self.get_count()
            if not self.count:
                return
            self.start_time = time()
            found = self.do_sync()
            if not found:
                break
            if self.last_pay and self.last:
                self.update_last()

    def get_va_channel(self, pay_date, invoice_id=None):
        if not self.va_session:
            return
        if not invoice_id:
            invoice_id = self.invoice_id
        q = self.base_q_va.filter(VaInvoice.invoice_no == invoice_id)
        q = q.order_by(VaPayment.id.desc())
        row = q.first()
        if not row:
            return
        log = getLogger('get_va_channel()')
        va_pay, va_inv = row
        va_pay_date = datetime.strptime(
            va_pay.transaction_date, '%Y-%m-%d %H:%M:%S')
        va_pay_date = va_pay_date.date()
        if va_pay_date != pay_date:
            msg = f'Invoice ID {self.invoice_id} ada pembayaran melalui '\
                  f'VA/QRIS tapi tanggalnya beda yaitu {pay_date} vs '\
                  f'{va_pay_date}'
            log.warning(msg)
            return
        if va_inv.va_type == 'a':
            return 'VA'
        if va_inv.va_type == 'q':
            return 'QRIS'
        raise Exception(
                f'Invoice ID {invoice_id} va_type {inv.va_type} '
                'belum dipahami, perbaiki script')


class BaseAppById(BaseApp):
    def prepare_query_filter(self):  # Override
        if self.conf.get('start_id'):
            self.last_id = self.conf.get('start_id') - 1
        else:
            self.last = self.get_last_id(self.conf_name)
            self.last_id = self.last.as_int()

    def get_last_time(self):  # Override
        return str(self.last_pay.id)