payment.py

import logging
from datetime import datetime
from random import randrange

from tangsel.base import DBSession
from sqlalchemy import Table, func, select, create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker

from tangsel.pbb.models.h2h.sismiop.models import Models
from tangsel.pbb.models.h2h.sismiop.query import Reversal
from tangsel.pbb.models.payment import PbbPayment, PbbReversal
from .sismiop.db_tools import sppt2nop
from ..tools import FixSppt
from ..tools import (
    JsonRpcBankNotFoundError,
    JsonRpcBillDifferentError,
    JsonRpcNtbNotFoundError,
    JsonRpcBillNotFoundError,
    JsonRpcBillAllreadyPaidError,
)
from ..tools import (
    get_settings,
)
from ..tools import ymd

log = logging.getLogger(__name__)

# from .sismiop.models import get_db
# from .sismiop.models.log import RpcLog


from tangsel.pbb.models import PbbDBSession

MY_NAME = 'pbb'
SCHEMA = 'pbb'  # ini diperlukan
objects = dict()

ERR_MAX_LOOP = '*** Max loop for create payment ID. ' \
               'Call your programmer please.'


# Base = declarative_base()

###########
# Inquiry #
###########
def get_calc(db, settings, invoice_id_raw):
    if 'models' in objects:
        models = objects['models']
        calc = objects['calc']
    else:
        from .sismiop.models import Models
        if 'query_module' in settings:
            if settings['query_module'] == 'tangsel':
                from .sismiop.query_tangsel import CalculateInvoice
            if settings['query_module'] == 'kuningan_kab':
                from .sismiop.query_kuningan_kab import CalculateInvoice
            if settings['query_module'] == 'majalengka_kab':
                from .sismiop.query_majalengka_kab import CalculateInvoice
        else:
            from .sismiop.query import CalculateInvoice
        # Base.metadata.bind = db.engine
        models = Models(False)
        objects['models'] = models
        objects['calc'] = calc = CalculateInvoice
    persen_denda = float(settings['pbb_persen_denda'])
    invoice_id = FixSppt(invoice_id_raw)
    return calc(
        models, PbbDBSession, persen_denda, invoice_id['kd_propinsi'],
        invoice_id['kd_dati2'], invoice_id['kd_kecamatan'],
        invoice_id['kd_kelurahan'], invoice_id['kd_blok'],
        invoice_id['no_urut'], invoice_id['kd_jns_op'],
        invoice_id['thn_pajak_sppt'])


def cek_piutang(calc):
    if not calc.invoice:
        log.warning(calc.invoice_id())
        log.warning(calc.invoice)
        log.warning(JsonRpcBillNotFoundError.message)
        raise JsonRpcBillNotFoundError
    if calc.is_paid():
        log.info(JsonRpcBillAllreadyPaidError.message)
        raise JsonRpcBillAllreadyPaidError


def get_bank(p):
    return str(p['bit_033'])


def get_channel(p):
    return str(p['bit_018'])


def get_tp(p, settings):
    banks = settings['pbb_banks'].split(';')
    banks = [x.split(':') for x in banks]
    banks = dict(banks)
    kd_bank = get_bank(p)
    key = '.'.join([kd_bank, get_channel(p)])
    if key in banks:
        v = banks[key]
    elif kd_bank in banks:
        v = banks[kd_bank]
    else:
        raise JsonRpcBankNotFoundError
    t = v.split('.')
    return dict(kd_kanwil=t[0], kd_kantor=t[1], kd_tp=t[2])


def get_ntp():
    max_loop = 10
    loop = 0
    while True:
        acak = randrange(11111111, 99999999)
        ntp = str(acak)
        q = PbbPayment.query().filter_by(ntp=ntp)
        if not q.first():
            return ntp
        loop += 1
        if loop == max_loop:
            raise Exception(ERR_MAX_LOOP)


class Inquiry(object):
    def __init__(self, o):
        self.calc = o
        self.invoice_profile = dict()
        self.total = o.total
        self.data = None

    def set_invoice_profile(self):
        inv = self.calc.invoice
        settings = get_settings()
        self.invoice_profile = dict(
            code=sppt2nop(inv),
            name=inv.nm_wp_sppt,
            address=inv.jln_wp_sppt,
            address2="{}, RT/RW: {}/{}".format(
                inv.blok_kav_no_wp_sppt, inv.rt_wp_sppt,
                inv.rw_wp_sppt),
            amount=self.calc.tagihan,
            fine=self.calc.denda,
            discount=self.calc.discount,
            total=self.calc.total,
            account_no=settings["pbb_accnt"],
            description="PBB-P2",
            unit_kode=settings["pbb_unit"],
            unit_name=settings["pbb_unit_name"],
            jth_tempo=ymd(self.calc.invoice.tgl_jatuh_tempo_sppt),
            kelurahan_op=self.calc.nama_kelurahan(),
            kecamatan_op=self.calc.nama_kecamatan(),
            kota_op=self.calc.nama_kabupaten(),
            propinsi_op=self.calc.nama_propinsi(),
            luas_bumi=int(inv.luas_bumi_sppt),
            luas_bng=int(inv.luas_bng_sppt)
        )

    def create_payment(self, tanggal):
        settings = get_settings()
        bank_fields = get_tp(self.data, settings)
        trx_time = get_trx_time(self.data)
        return self.calc.create_payment(
            trx_time.date(), bank_fields, settings['pbb_nip_pencatat'])

    def get_tanggal(self):
        dt = "{}{}{}".format(datetime.now().strftime("%Y"),
                             self.data["bit_013"], self.data["bit_012"])
        return datetime.strptime(dt, "%Y%m%d%H%M%S")

    def get_bank(self):
        return self.data["bit_033"]

    def get_channel(self):
        return self.data["bit_018"]

    def get_ntp(self):
        pass

    def get_amt(self):
        return self.data["bit_004"]

    def get_acquirer(self):
        return self.data["bit_032"]

    def get_ntb(self):
        return self.data["bit_037"]

    def get_stan(self):
        return self.data["bit_011"]

    def cek_iso_payment(self, data):
        self.data = data
        return PbbPayment.query(). \
            filter_by(ntb=self.get_ntb(),
                      propinsi=self.calc.propinsi,
                      kabupaten=self.calc.kabupaten,
                      kecamatan=self.calc.kecamatan,
                      kelurahan=self.calc.kelurahan,
                      blok=self.calc.blok,
                      urut=self.calc.urut,
                      jenis=self.calc.jenis,
                      tahun=self.calc.tahun
                      ).first()

    def save_iso_payment(self, pay):
        ntp = get_ntp()
        iso_pay = PbbPayment()
        # iso_pay.id = pay.id
        # iso_pay.invoice_id = pay.sspd_id
        # iso_pay.invoice_no = self.invoice_no
        values = dict(
            propinsi=self.calc.propinsi,
            kabupaten=self.calc.kabupaten,
            kecamatan=self.calc.kecamatan,
            kelurahan=self.calc.kelurahan,
            blok=self.calc.blok,
            urut=self.calc.urut,
            jenis=self.calc.jenis,
            tahun=self.calc.tahun,
            ke=pay.pembayaran_sppt_ke,
            kd_kanwil_bank=pay.kd_kanwil,
            kd_kppbb_bank=pay.kd_kantor,
            kd_bank_tunggal='00',
            kd_bank_persepsi='00',
            kd_tp=pay.kd_tp,
            channel=self.get_channel(),
            ntb=self.get_ntb(),
            ntp=ntp,
            iso_request=str(self.data),
            transmission=datetime.now(),
            settlement=datetime.now().date(),
            stan=self.get_stan(),
            bank=self.get_bank(),
            tgl=self.get_tanggal(),
            bank_ip="0.0.0.0"  # self.get_bank_ip()
        )
        iso_pay.from_dict(values)

        DBSession.add(iso_pay)
        DBSession.flush()
        return ntp

    def reversal(self, pay):
        # pay = self.cek_iso_payment()
        # if not pay:
        #     raise JsonRpcPaymentNotFoundError
        models = Models(False)
        q = Reversal(models, PbbDBSession, pay.propinsi, pay.kabupaten,
                     pay.kecamatan, pay.kelurahan, pay.blok, pay.urut,
                     pay.jenis, str(pay.tahun))
        q.set_unpaid()

        # q = Pembayaran.query_by_nop_thn(
        #     "".join([pay.propinsi,
        #              pay.kabupaten,
        #              pay.kecamatan,
        #              pay.kelurahan,
        #              pay.blok,
        #              pay.urut,
        #              pay.jenis,
        #              ]),
        #     self.calc.tahun
        # ).filter_by(pembayaran_sppt_ke=pay.ke)
        # bayar = q.first()
        # if not bayar:
        #     raise JsonRpcPaymentNotFoundError
        #
        # bayar.denda_sppt = 0
        # bayar.jml_sppt_yg_dibayar = 0
        # # self.calc.set_unpaid()
        # self.calc.invoice.status_pembayaran_sppt = '0'
        # PbbDBSession.add(self.calc.invoice)
        # PbbDBSession.add(pay)
        # PbbDBSession.flush()
        #
        iso_rev = PbbReversal()
        iso_rev.id = pay.id
        iso_rev.iso_request = str(self.data)
        iso_rev.tgl = datetime.now()
        DBSession.add(iso_rev)
        DBSession.flush()


def inquiry_(inv_no):
    settings = get_settings()
    calc = get_calc(PbbDBSession, settings, inv_no)
    return calc


def inquiry(inv_no):
    calc = inquiry_(inv_no)
    cek_piutang(calc)
    return Inquiry(calc)


def payment(inv_no):
    return inquiry(inv_no)


def reversal(inv_no):
    return Inquiry(inquiry_(inv_no))


###########
# Payment #
###########
def cek_ntb(p):
    if 'bit_037' not in p:
        raise JsonRpcNtbNotFoundError


def cek_jumlah(p, piutang):
    if p['bit_004'] != piutang['total']:
        raise JsonRpcBillDifferentError


def get_trx_time(data):
    dt = "{}{}{}".format(datetime.now().strftime("%Y"),
                         data["bit_013"], data["bit_012"])
    log.info(dt)
    return datetime.strptime(dt, "%Y%m%d%H%M%S")


def create_ntp(bank_id):
    acak = randrange(10000000, 99999999)
    return ''.join([str(bank_id).zfill(3), str(acak).zfill(8)])


session_factory = sessionmaker()


class DBProfile:
    def __init__(self, db_url):
        self.engine = create_engine(db_url)
        self.metadata = MetaData(self.engine)
        # base_session = sessionmaker(bind=self.engine)
        # self.session = base_session()
        self.session = scoped_session(session_factory)

    def query(self, *args):
        return self.session.query(*args)

    def flush(self, row=None):
        row and self.session.add(row)
        self.session.flush()

    def commit(self, row=None):
        self.flush(row)
        self.session.commit()

    def execute(self, sql, **kwargs):
        return self.engine.execute(sql, **kwargs)

    def first_value(self, sql):
        q = self.engine.execute(sql)
        row = q.first()
        return row[0]

    def get_table(self, tablename, schema=None):
        return Table(tablename, self.metadata, autoload=True, schema=schema)

    def get_count(self, table):
        sql = select([func.count()]).select_from(table)
        q = self.execute(sql)
        return q.scalar()


def advice(inv_no):
    return Inquiry(inquiry_(inv_no))