bjb_qris.py

import os
import json
import ast
import uuid
import colander
from datetime import datetime, date, timedelta, time
from sqlalchemy.orm import aliased
from pyramid.view import view_config
from sqlalchemy import not_, func, between, and_, literal_column, case, literal
from pyramid.httpexceptions import HTTPFound
from deform import Form, widget, ValidationFailure
from ..views import ColumnDT, DataTables
from ..models.isipkd import (ARInvoice as ArInvoice, ARSspd as ArPayment, SubjekPajak as Subjek, Unit as Departemen)
from ..models import DBSession
from ..models.bjb_qris import BJBQRIS
from ..tools import (dmy, get_settings, date_from_str, 
                     odt_export, thousand, get_settings, datetime_from_str)
from ..models.rpc import auth_from_rpc
from ..tools import ymd, dmy, ymdhms, dmyhms, dmy_to_date

from pyramid_rpc.jsonrpc import jsonrpc_method
from daftar import hitung_bunga
from arsspd import save as save_payment

SESS_ADD_FAILED = 'Gagal tambah BJBQRIS'
SESS_EDIT_FAILED = 'Gagal edit BJBQRIS'

import logging
log = logging.getLogger('BJBQRIS LOG')
from pyramid.renderers import render_to_response
from sqlalchemy.sql.expression import cast
from sqlalchemy import Integer

def rpc_params():
    parameter = dict(
        bjbqris_url  = get_settings()['bjbqris_url'] and get_settings()['bjbqris_url'].strip('/') or '',
        bjbqris_user = get_settings()['bjbqris_user'] and get_settings()['bjbqris_user'].strip() or '',
        bjbqris_key  = get_settings()['bjbqris_key'] and get_settings()['bjbqris_key'].strip() or ''
    )
    return parameter
    
class Item(object):
    pass
        
class AddSchema(colander.Schema):
    client_type                 =   colander.SchemaNode(
                                    colander.Integer(),
                                    widget = widget.HiddenWidget(),
                                    default = 3,
                                    oid = "client_type",
                                    title="")
    product_code                  =   colander.SchemaNode(
                                    colander.String(),
                                    widget = widget.HiddenWidget(),
                                    default = "99", #30,
                                    oid = "product_code",
                                    title="")
    invoice_no                  =   colander.SchemaNode(
                                    colander.String(),
                                    widget=widget.SelectWidget(),
                                    validator=colander.Length(max=16),
                                    oid = "invoice_no",
                                    title="Kode Bayar")
    description                 =   colander.SchemaNode(
                                    colander.String(),
                                    widget=widget.TextAreaWidget(rows=6),
                                    validator=colander.Length(max=128),
                                    oid = "description",)
    customer_name               =   colander.SchemaNode(
                                    colander.String(),
                                    validator=colander.Length(max=64),
                                    oid = "customer_name",
                                    title="Customer Name")
    customer_email              =   colander.SchemaNode(
                                    colander.String(),
                                    validator=colander.Length(max=128),
                                    oid = "customer_email",
                                    title="Customer Email")
    customer_phone              =   colander.SchemaNode(
                                    colander.String(),
                                    validator=colander.Length(max=32),
                                    oid = "customer_phone",
                                    title="Customer Phone")
    expired_date                =   colander.SchemaNode(
                                    colander.String(),
                                    widget=widget.TextInputWidget(css_class="input-date"),
                                    oid = "expired_date",
                                    default = ymdhms(datetime.now()),
                                    title="Expired Date")
    amount                      =   colander.SchemaNode(
                                    colander.Integer(),
                                    oid = "amount",
                                    title="Amount")
    
class EditSchema(AddSchema):
    invoice_no                  =   colander.SchemaNode(
                                    colander.String(),
                                    validator=colander.Length(max=16),
                                    oid = "invoice_no",
                                    title="Kode Bayar")
    billing_type                =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=1),
                                    oid = "billing_type",
                                    title="Billing Type")
    va_type                     =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=1),
                                    oid = "va_type",
                                    title="VA Type")
    va_number                   =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=32),
                                    oid = "va_number",
                                    title="VA Number")
    currency                    =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=32),
                                    oid = "currency",
                                    title="Currency")
    customer_id                 =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=32),
                                    oid = "customer_id",
                                    title="Customer ID")
    status                      =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    oid = "status",
                                    title="Status")
    cin                         =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=4),
                                    oid = "cin",
                                    title="cin")
    response_code               =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=4),
                                    oid = "response_code",
                                    title="response_code")
    response_message            =   colander.SchemaNode(
                                    colander.String(),
                                    missing=colander.drop,
                                    validator=colander.Length(max=128),
                                    oid = "response_message",
                                    title="response_message")
    
    id              =   colander.SchemaNode(
                        colander.Integer(),
                        missing=colander.drop,
                        oid='id',
                        widget=widget.HiddenWidget(readonly=True)
                        )

def get_form(request, class_form):
    schema = class_form()
    schema.request = request
    return Form(schema, buttons=('simpan','batal'))

def route_list(request):
    return HTTPFound(location=request.route_url('bjbqris'))

def session_failed(request, session_name):
    r = dict(form=request.session[session_name])
    del request.session[session_name]
    return r

def query_id(request):
    return DBSession.query(BJBQRIS).join(ArInvoice, ArInvoice.kode==BJBQRIS.invoice_no).\
            filter(BJBQRIS.id==request.matchdict['id'],
                   # ArInvoice.departemen_id==request.session['departemen_id']
                   )

def id_not_found(request):
    msg = 'BJBQRIS ID %s not found.' % request.matchdict['id']
    request.session.flash(msg, 'error')
    return route_list(request)
    
def id_paid(request):
    msg = 'BJBQRIS ID %s sudah terbayar.' % request.matchdict['id']
    request.session.flash(msg, 'error')
    return route_list(request)
    
def id_expired(request):
    msg = 'BJBQRIS ID %s expired.' % request.matchdict['id']
    request.session.flash(msg, 'error')
    return route_list(request)
    
def cekqris(values,request):
    cekqris = DBSession.query(BJBQRIS).\
        filter(BJBQRIS.invoice_no==values['invoice_no'],BJBQRIS.expired_date>datetime.now()).first()
    if cekqris:
        msg = 'BJBQRIS No. Billing %s sudah ada.' % cekqris.va_number
        request.session.flash(msg, 'error')
        raise route_list(request)

###########
#  List   #
###########
class view(object):
    def __init__(self, request):
        now = datetime.now()
        self.req = request
        self.ses = self.req.session
        self.params = self.req.params
        self.awal = 'awal' in self.ses and self.ses['awal'] or dmy(now)
        awal = 'awal' in self.params and self.params['awal'] or self.awal
        try:
            self.dt_awal = dmy_to_date(awal)
            self.awal = awal
        except:
            self.dt_awal = dmy_to_date(self.awal)

        self.ses['awal'] = self.awal
        self.ses['dt_awal'] = self.dt_awal

        self.akhir = 'akhir' in self.ses and self.ses['akhir'] or dmy(now)
        akhir = 'akhir' in self.params and self.params['akhir'] or self.akhir

        try:
            self.dt_akhir = dmy_to_date(akhir)
            self.akhir = akhir
        except:
            self.dt_akhir = dmy_to_date(self.akhir)

        self.ses['akhir'] = self.akhir
        self.ses['dt_akhir'] = self.dt_akhir
        self.tahun = 'tahun' in self.ses and self.ses['tahun'] or datetime.now().strftime('%Y')
        self.tahun = 'tahun' in self.params and self.params['tahun'] or self.tahun
        self.ses['tahun'] = self.tahun
    
    @view_config(route_name='bjbqris', renderer='templates/bjbqris/list.pt')
    def view_list(self):
        return dict()


#########
#  Add  #
#########
    @view_config(route_name='bjbqris-add', renderer='templates/bjbqris/add-edit.pt', permission='add')
    def view_add(self):
        request = self.req
        form = get_form(request, AddSchema)
    
        if request.POST:
            if 'simpan' in request.POST:
                controls = request.POST.items()
                try:
                    controls = form.validate(controls)
                except ValidationFailure as e:
                    return dict(form=form)
                cekqris(dict(controls),request)
                val = dict(controls)
                inv = q_inv(val['invoice_no']).first()
                pokok, denda = calculate_tagihan(dict(pokok = inv.jumlah,jatuh_tempo=inv.jatuh_tempo))
                val['amount'] = (pokok+denda)
                row = BJBQRIS.create_va(val, rpc_params())
                if row:
                    request.session.flash('BJBQRIS No. %s sudah ditambahkan.' % row.va_number)
                else:
                    request.session.flash('BJBQRIS gagal ditambahkan.')
                return route_list(request)
            return route_list(request)
        elif SESS_ADD_FAILED in request.session:
            return session_failed(request, SESS_ADD_FAILED)
        values = {}
        form.set_appstruct(values)
        return dict(form=form)

##########
# Action #
##########
    @view_config(route_name='bjbqris-act', renderer='json', permission='read')
    def view_act(self):
        req      = self.req
        params   = req.params
        url_dict = req.matchdict
    
        if url_dict['act']=='grid':
            columns = [
              ColumnDT('id'),
              ColumnDT('va_number'),
              ColumnDT('invoice_no'),
              ColumnDT('description'),
              ColumnDT('customer_name'),
              ColumnDT('amount'),
              ColumnDT('client_refnum'),
              ColumnDT('expired_date'),
              ColumnDT('tgl_bayar'),
              ColumnDT('status'),
            ]
            
            query = DBSession.query(BJBQRIS.id, BJBQRIS.va_number, BJBQRIS.invoice_no, BJBQRIS.description,
                    BJBQRIS.customer_name, BJBQRIS.amount, BJBQRIS.client_refnum, 
                    func.to_char(BJBQRIS.expired_date, 'DD-MM-YYYY HH24:MI:SS').label('expired_date'),
                    func.to_char(ArPayment.tgl_bayar, 'DD-MM-YYYY HH24:MI:SS').label('tgl_bayar'),
                    ArInvoice.jumlah.label('jml_bayar'),
                    case([
                        (BJBQRIS.status == '0', literal('Belum Dibayar'))
                        ],
                      else_= 
                    case([
                        (BJBQRIS.status == '1', literal('Dibayar Sebagian'))
                        ],
                      else_= 
                    case([
                        (BJBQRIS.status == '2', literal('Dibayar Penuh'))
                        ],
                      else_= 
                    case([
                        (BJBQRIS.status == '3', literal('Kadaluarsa'))
                        ],
                      else_=
                    case([
                        (BJBQRIS.status == '4', literal('Disabled'))
                        ],
                      else_= literal('-')))))).label('status'),
                    ).join(ArInvoice, ArInvoice.kode==BJBQRIS.invoice_no).\
                      outerjoin(ArPayment, ArInvoice.id==ArPayment.arinvoice_id).\
                      filter( #ArInvoice.departemen_id==req.session['departemen_id'],
                             func.to_char(BJBQRIS.created,'YYYY') == str(req.session['tahun']))
                            
            rowTable = DataTables(req, BJBQRIS, query, columns)
            return rowTable.output_result()
    
        elif url_dict['act'] == 'kodebayar':
            term = 'term' in params and params['term'] or ''
            q = DBSession.query(ArInvoice). \
                filter(ArInvoice.status_bayar == 0,
                       ArInvoice.kode.ilike('%%%s%%' % term)).\
                order_by(ArInvoice.kode)
                # filter(ArInvoice.departemen_id==req.session['departemen_id']).\
            rows = q.all()
            r = []
            for k in rows:
                d = dict(id=k.id,
                         value=k.kode,
                         kode=k.kode,
                         nama=k.op_nama)
                r.append(d)
            return r
            
        elif url_dict['act'] == 'detail':
            term = 'term' in params and params['term'].strip() or ''
            row = q_inv(term).first()
            pokok, denda = calculate_tagihan(dict(pokok = row.jumlah,jatuh_tempo=row.jatuh_tempo))
            return dict(id=row.id,
                         value=row.subjek_nama,
                         description=row.description,
                         customer_name=row.subjek_nama,
                         customer_email=row.subjek_email and row.subjek_email or '-',
                         customer_phone=row.subjek_phone and row.subjek_phone or '-',
                         expired_date=ymdhms(datetime.combine(date.today(),time(23,59,59))),
                         amount=(pokok + denda))
    
        elif url_dict['act'] == 'qrcode':
            id = 'id' in params and params['id'] or ''
            qr = ''
            if id:
                qris = BJBQRIS.query_id(id).first()
                import qrcode
                from ..tools import get_tmp
                img = qrcode.make(str(qris.qrcode))
                path = get_tmp() + str(qris.va_number)
                img.save(path, 'PNG')
                qr = path+'PNG'
                import base64
                qr = base64.b64encode(open(path, 'rb').read())
                
            from pyramid.renderers import render_to_response
            return render_to_response("templates/bjbqris/qrcode.pt", dict(qr=qr))

@jsonrpc_method(method='callback', endpoint='bjbqris-callback')
def callback(request, data):
    ## 
    ## CALLBACK BODY
    ## {
    ##     "data": {
    ##         "invoice_no": "230001",
    ##         "va_number": "00170743733",
    ##         "transaction_date": "2021-08-19 10:08:30",
    ##         "transaction_amount": 526829,
    ##         "unpaid": 0,
    ##         "status": 2
    ##     }
    ## }
    ## 
    
    auth_from_rpc(request)
    
    log.error('REQUEST BJBQRIS')
    log.error(data)
    
    get_va = BJBQRIS.query().filter(BJBQRIS.va_number==data['va_number'],
                                    BJBQRIS.invoice_no==data['invoice_no']).first()
    if not get_va:
        return render_to_response('json',
            dict(response_code='-1',response_message='VA Number not Found'))
    get_va.status = 'status' in data and data['status']\
        and str(data['status']) or get_va.status
    get_va.transaction_amount = 'transaction_amount' in data and data['transaction_amount']\
        and data['transaction_amount'] or get_va.amount
    get_va.transaction_date = 'transaction_date' in data and data['transaction_date']\
        and data['transaction_date'] or datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    pay = save_pembayaran(
            request,
            dict(
                invoice_no = get_va.invoice_no,
                va_number = get_va.va_number,
                transaction_date = get_va.transaction_date,
                transaction_amount = get_va.transaction_amount,
                status = get_va.status,
                amount = get_va.amount
            )
        )
    if not pay:
        return render_to_response('json',
            dict(response_code='-1',response_message='Failed to update'))
    
    if int(get_va.status) == 2 and not pay.status:
        get_va.status = '1'
    try:
        DBSession.add(get_va)
        DBSession.flush()
        return render_to_response('json',
            dict(response_code='0000',response_message='Success'))
    except:
        return render_to_response('json',
            dict(response_code='-1',response_message='Failed to update'))
    
def save_pembayaran(request, values):
    if int(values['status']) not in [1,2]:
        return
    invoice =  ArInvoice.query().filter(ArInvoice.kode==values['invoice_no']).first()
    if not invoice:
        return
    values.update(invoice.to_dict())
    
    values['tgl_bayar'] = datetime_from_str(values['transaction_date'])
    values['ntb'] = 'BJBQRIS'
    values['bayar'] = int(values['transaction_amount'])
    values['tahun'] = values['tgl_bayar'].strftime('%Y')
    values['status'] = 2
    
    pay = save_payment(request, values)
    return pay
        
def q_inv(kode):
    return DBSession.query(ArInvoice.id.label('id'),
                                  ArInvoice.jumlah.label('jumlah'),
                                  ArInvoice.kode.label('kode'),
                                  ArInvoice.jatuh_tempo.label('jatuh_tempo'),
                                  func.concat(ArInvoice.no_skrd ,
                                    literal_column("' Tgl:'") ,
                                    func.to_char(ArInvoice.tgl_tetap, 'DD/MM/YYYY') ,
                                    literal_column("' Jatuh Tempo:'") ,
                                    func.to_char(ArInvoice.jatuh_tempo, 'DD/MM/YYYY') ,
                                    literal_column("' '") ,
                                    ArInvoice.keterangan).label('description'),
                                  ArInvoice.wp_nama.label('subjek_nama'),
                                  Subjek.email.label('subjek_email'),
                                  literal_column("'-'").label('subjek_phone')).\
                filter(ArInvoice.status_bayar == 0,ArInvoice.kode == kode).\
                outerjoin(Subjek, Subjek.id==ArInvoice.subjek_pajak_id)
                # filter(ArInvoice.departemen_id==req.session['departemen_id']).\
                
def calculate_tagihan(values):
    pokok = int(values['pokok'])
    denda = hitung_bunga(pokok,values['jatuh_tempo'])
    return pokok, denda