public.py

import sys
import re
import logging
import os
import qrcode
import base64
from email.utils import parseaddr
from sqlalchemy import not_, func, or_, desc
from datetime import datetime
from time import gmtime, strftime, strptime
from pyramid.view import (
    view_config,
    )
from pyramid.httpexceptions import (
    HTTPFound,
    )
import colander
from deform import (
    Form,
    widget,
    ValidationFailure,
    )
from ..tools import (
    _DTnumberformat,
    multi_dict_values,
    odt_export,
    terbilang,
    thousand,
    dmy,
    BULANS,
    get_settings
    )
from ..models import DBSession
from ..models.isipkd import(
      Pegawai, ObjekPajak, SubjekPajak, ARInvoice,
      Unit, UserUnit,Wilayah, Pajak, Rekening, 
      ARSts, ARStsItem, ARSspd, ARTbp
      )

from datatables import (
    ColumnDT, DataTables)
    
from ..security import group_finder,group_in

from daftar import (STATUS, deferred_status,
                    daftar_subjekpajak, deferred_subjekpajak,
                    daftar_objekpajak, deferred_objekpajak,
                    daftar_wilayah, deferred_wilayah,
                    daftar_unit, deferred_unit,
                    daftar_pajak, deferred_pajak,
                    auto_op_nm, auto_unit_nm, auto_wp_nm, auto_wp_nm3
                    )


SESS_ADD_FAILED = 'Gagal tambah Tagihan'
SESS_EDIT_FAILED = 'Gagal edit Tagihan'
log = logging.getLogger(__name__)
    

#######    
# Add #
#######
def form_validator(form, value):
    def err_kode():
        raise colander.Invalid(form,
            'Kode invoice %s sudah digunakan oleh ID %d' % (
                value['kode'], found.id))

    def err_name():
        raise colander.Invalid(form,
            'Uraian  %s sudah digunakan oleh ID %d' % (
                value['nama'], found.id))
                
    if 'id' in form.request.matchdict:
        uid = form.request.matchdict['id']
        q = DBSession.query(ARInvoice).filter_by(id=uid)
        r = q.first()
    else:
        r = None
            
class AddSchema(colander.Schema):
    moneywidget = widget.MoneyInputWidget(
            size=20, options={'allowZero':True,
                              'precision':0
                              })
            
    unit_id = colander.SchemaNode(
                    colander.Integer(),
                    widget=widget.HiddenWidget(),
                    oid="unit_id",
                    title="OPD",
                    )
    unit_nm = colander.SchemaNode(
                    colander.String(),
                    title="OPD",
                    oid="unit_nm"
                    )
    subjek_pajak_id = colander.SchemaNode(
                    colander.Integer(),
                    widget=widget.HiddenWidget(),
                    title="Penyetor",
                    oid = "subjek_pajak_id"
                    )
    subjek_pajak_nm = colander.SchemaNode(
                    colander.String(),
                    widget=auto_wp_nm3,
                    title="Penyetor",
                    oid = "subjek_pajak_nm"
                    )
    subjek_pajak_us = colander.SchemaNode(
                    colander.Integer(),
                    widget=widget.HiddenWidget(),
                    oid = "subjek_pajak_us"
                    )
    subjek_pajak_un = colander.SchemaNode(
                    colander.Integer(),
                    widget=widget.HiddenWidget(),
                    oid = "subjek_pajak_un"
                    )
    wp_nama = colander.SchemaNode(
                    colander.String(),
                    #widget=auto_wp_nm3,
                    title="Nama Lainnya",
                    oid = "wp_nama"
                    )
    wp_alamat_1 = colander.SchemaNode(
                    colander.String(),
                    #widget=auto_wp_nm3,
                    title="Alamat",
                    oid = "wp_alamat_1"
                    )
    objek_pajak_id = colander.SchemaNode(
                    colander.Integer(),
                    title="Objek",
                    widget=widget.HiddenWidget(),
                    oid = "objek_pajak_id"
                    )
    objek_pajak_nm = colander.SchemaNode(
                    colander.String(),
                    widget=auto_op_nm,
                    title="Objek",
                    oid = "objek_pajak_nm"
                    )
                    
    kode   = colander.SchemaNode(
                    colander.String(),
                    title="Kode Bayar",
                    missing = colander.drop,
                    )
                    
    periode_1 = colander.SchemaNode(
                    colander.Date(),
                    title="Periode 1",
                    widget = widget.DateInputWidget()
                    )
    periode_2 = colander.SchemaNode(
                    colander.Date(),
                    title="Periode 2"
                    )
    tgl_tetap = colander.SchemaNode(
                    colander.Date(),
                    oid = "tgl_tetap"
                    )
    jatuh_tempo = colander.SchemaNode(
                    colander.Date(),
                    oid = 'jatuh_tempo',
                    )
    dasar = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    oid = "dasar"
                    )
    tarif = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    oid = "tarif",
                    title="Tarif (%)",
                    missing=colander.drop
                    )
    pokok = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    missing=colander.drop,
                    oid = "pokok"
                    )
    denda = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    oid = "denda"
                    )
    bunga = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    oid = "bunga"
                    )
    jumlah = colander.SchemaNode(
                    colander.Integer(),
                    default = 0,
                    widget = moneywidget,
                    missing=colander.drop,
                    oid = "jumlah"
                    )
    no_skrd = colander.SchemaNode(
                    colander.String(),
                    oid = "no_skrd"
                    )
                    
class EditSchema(AddSchema):
    id = colander.SchemaNode(colander.Integer(),
            missing=colander.drop,
            widget=widget.HiddenWidget(readonly=True)
            )
                    

def get_form(request, class_form):
    schema = class_form(validator=form_validator)
    schema = schema.bind(daftar_status=STATUS,
                         daftar_subjekpajak=daftar_subjekpajak(),
                         daftar_unit=daftar_unit(),
                         daftar_objekpajak=daftar_objekpajak(),
                         )
    schema.request = request
    return Form(schema, buttons=('simpan','batal'))
    
def save(request, values, row=None):
    log.debug('*** values {}'.format(values))
    if not row:
        row = ARInvoice()
        row.create_date = datetime.now()
        row.create_uid  = request.user.id
    row.from_dict(values)
    #row.update_date = datetime.now()
    row.update_uid  = request.user.id
    row.dasar  = re.sub("[^0-9]", "", row.dasar)
    row.tarif  = re.sub("[^0-9]", "", row.tarif)
    row.pokok  = re.sub("[^0-9]", "", row.pokok)
    row.denda  = re.sub("[^0-9]", "", row.denda)
    row.bunga  = re.sub("[^0-9]", "", row.bunga)
    row.jumlah = re.sub("[^0-9]", "", row.jumlah)
    
    if not row.tahun_id:
        row.tahun_id = datetime.now().strftime('%Y')
        
    unit = Unit.get_by_id(row.unit_id)
    row.unit_kd = unit.kode
    row.unit_nm = unit.nama
    
    ref = Unit.get_by_id(row.unit_id)
    row.unit_kode = ref.kode
    row.unit_nama = ref.nama
    ref = SubjekPajak.get_by_id(row.subjek_pajak_id)
    row.wp_kode = ref.kode
    #row.wp_nama = ref.nama
    #row.wp_alamat_1 = ref.alamat_1
    row.wp_alamat_2 = ref.alamat_2
    
    ref = ObjekPajak.get_by_id(row.objek_pajak_id)
    row.op_kode = ref.kode
    row.op_nama = ref.nama
    row.op_alamat_1 = ref.alamat_1
    row.op_alamat_2 = ref.alamat_2
    row.wilayah_id = ref.wilayah_id
    row.rekening_id = ref.pajaks.rekening_id
    row.rek_kode = ref.pajaks.rekenings.kode
    row.rek_nama = ref.pajaks.rekenings.nama
    row.denda_rekening_id = ref.pajaks.denda_rekening_id
    
    ref = Wilayah.get_by_id(row.wilayah_id)
    row.wilayah_kode = ref.kode
    
    #-- Perubahan pada No bayar dan filter tanggal 13-062017 --#
    tgl_tetap = values['tgl_tetap']

    prefix  = '21' 
    tgl_tetap_new = datetime.strptime(values['tgl_tetap'], '%Y-%m-%d').date()
    tanggal = tgl_tetap_new.day
    bulan   = tgl_tetap_new.month
    tahun   = tgl_tetap_new.strftime('%y')
    
    if not row.kode and not row.no_id:
        invoice_no = DBSession.query(func.max(ARInvoice.no_id)).\
                               filter(#ARInvoice.tahun_id==row.tahun_id,
                                      ARInvoice.wilayah_id==row.wilayah_id,
                                      ARInvoice.tgl_tetap==tgl_tetap_new,
                                      func.substr(ARInvoice.kode,1,2)=='21').scalar()
        print "--------- Invoice No ---------- ",invoice_no
        print "--tahun--",row.tahun_id
        print "--wilayah--",row.wilayah_id
        print "--tanggal--",tgl_tetap_new
        if not invoice_no:
            row.no_id = 1
        else:
            row.no_id = invoice_no+1

    row.kode = "".join([prefix, re.sub("[^0-9]", "", row.wilayah_kode), 
                        str(tanggal).rjust(2,'0'), 
                        str(bulan).rjust(2,'0'),
                        str(tahun).rjust(2,'0'),
                        str(row.no_id).rjust(4,'0')])

    row.owner_id = request.user.id
    DBSession.add(row)
    DBSession.flush()
    return row
    
def save_request(values, request, row=None):
    if 'id' in request.matchdict:
        values['id'] = request.matchdict['id']
    row = save(request, values, row)
    request.session.flash('No Bayar %s sudah disimpan.' % row.kode)
        
def route_list(request):
    return HTTPFound(location=request.route_url('arinvoiceb'))
    
def session_failed(request, session_name):
    try:
        session_name.set_appstruct(request.session[SESS_ADD_FAILED])
    except:
        pass
    r = dict(form=session_name) 
    del request.session[SESS_ADD_FAILED]
    return r
    
@view_config(route_name='public-act', renderer='templates/public/skrd.pt')
def view_public(request):
    params = request.params
    url_dict = request.matchdict
    found = False
    row = ''
    jml = '' 
    tahun=''
    qrcodebase64=''
    status_pembayaran=''
    jml_pokok=''
    jml_denda=''
    jml_bayar=''
    found=False
    
    if url_dict['act'] == 'skrd':
        if 'id' in params and params['id']:
            row = DBSession.query(ARInvoice.unit_nama.label('unit_nama'),
                        func.trim(ARInvoice.wp_nama).label('wp_nama'),
                        func.trim(ARInvoice.wp_alamat_1).label('wp_alamat_1'),
                        func.trim(ARInvoice.wp_alamat_2).label('wp_alamat_2'),
                        func.trim(ARInvoice.wp_kode).label('wp_kode'),
                        func.trim(ARInvoice.rek_nama).label('rek_nama'),
                        func.trim(ARInvoice.rek_kode).label('rek_kode'),
                        ARInvoice.keterangan.label('keterangan'),
                        ARInvoice.periode_1.label('periode_1'),
                        ARInvoice.periode_2.label('periode_2'),
                        ARInvoice.kode.label('kode'),
                        ARInvoice.no_skrd.label('no_skrd'),
                        ARInvoice.tahun_id.label('tahun_id'),
                        ARInvoice.tgl_tetap.label('tgl_tetap'),
                        ARInvoice.jumlah.label('jumlah'),
                        ARInvoice.jatuh_tempo.label('jatuh_tempo'),
                        ARInvoice.status_bayar.label('status_bayar'),
                        ARInvoice.pokok.label('pokok'),
                        ARInvoice.denda.label('denda'),
                        ARInvoice.pejabat_nama.label('pejabat_nama'),
                        ARInvoice.pejabat_pangkat.label('pejabat_pangkat'),
                        ARInvoice.pejabat_nip.label('pejabat_nip'),
                        ARInvoice.id.label('invoice_id'),
                        ARSspd.ntp.label('ntp'),
                        ARSspd.ntb.label('ntb'),
                        ARSspd.tgl_bayar.label('tgl_bayar'),
                        ARSspd.bunga.label('bunga'),
                        ARSspd.bayar.label('bayar'))\
                .outerjoin(ARSspd, ARSspd.arinvoice_id==ARInvoice.id)\
                .filter(ARInvoice.id==params['id']).first()
            if row:
                found = True

            jml         = terbilang(row.jumlah) + ' Rupiah'
            tahun       = datetime.now().year

            if (row.status_bayar == 0):
                status_pembayaran = 'Belum Lunas'
                jml_pokok = 0
                jml_denda = 0
                jml_bayar = 0
            else:
                status_pembayaran = 'Sudah Lunas'
                jml_pokok = row.pokok and thousand(row.pokok) or '-'
                jml_denda = (row.tgl_bayar) and thousand(row.bunga) or (row.denda) and thousand(row.denda) or '0'
                jml_bayar = row.bayar and thousand(row.bayar) or (row.jumlah) and thousand(row.jumlah) or (int(item.pokok)+int(item.denda))
            
            ## buat nama di folder /tmp
            qrcodename  = 'skrd_{}{}'.format(row.invoice_id,'.png')
            path = '/tmp/' + qrcodename
            ## ambil host
            settings = get_settings()
            host = settings['_host']
            ##Generate dulu qrcode nya
            imgqrcode = qrcode.make(str('{}/public/skrd?id={}'.format(host,row.invoice_id)))
            imgqrcode.save(path, 'PNG')
            ## Ambil isinya 
            readqrcode = open(path, 'rb').read()
            ## convert ke base64
            qrcodebase64 = base64.b64encode(readqrcode) ##akan diouput ke html image
            jml_pokok = int(re.sub("\D", "", jml_pokok))
            jml_denda = int(re.sub("\D", "", jml_denda))
            jml_bayar = int(re.sub("\D", "", jml_bayar))
        
    return dict(data=row, jml_terbilang=jml, tahun=tahun, qrcodebase64=qrcodebase64, status_pembayaran=status_pembayaran, jml_pokok=jml_pokok, jml_denda=jml_denda, jml_bayar=jml_bayar, found=found)