payment_list.py

import colander
import logging
from datatables import ColumnDT
from deform import widget, Form, ValidationFailure
from opensipkd.base.views import DataTables
from opensipkd.base.tools.report import csv_response
from pyramid.httpexceptions import HTTPFound
from pyramid.view import view_config
from sqlalchemy import case, func
from sqlalchemy.orm import aliased

from . import BaseView
from ..models import (DBSession, PartnerPay, Partner)
from opensipkd.base.tools import format_json
from opensipkd.base.tools.db import column_concat
from datetime import datetime
from ..tools import json_rpc_header
from .api_merchant import payment_advice

log = logging.getLogger(__name__)

status_payment = [
    (9999, '--Semua--'),
    (0, 'Pending'),
    (1, 'Sukses'),
    (2, 'Batal'),
]



def filter_tanggal(request, query, field):
    awal  = request.session['awal'] + ' 00:00:00' #None
    akhir = request.session['akhir'] + ' 23:59:59'#None
    
    if awal and akhir:
        tglawal    = datetime.strptime(awal, '%d-%m-%Y %H:%M:%S')
        tglakhir   = datetime.strptime(akhir, '%d-%m-%Y %H:%M:%S')
        query = query.filter(field.between(tglawal, tglakhir))

    return query

def query_csv(request, status):
    vendor = aliased(Partner, name='vendor')
    customer = aliased(Partner, name='customer')
    query = DBSession.query(PartnerPay.cust_inv_no.label('Nomor_Invoice'),
                            PartnerPay.created.label('Tanggal'),
                            column_concat([
                                PartnerPay.inv_cust_nm,
                                ' - (',
                                customer.nama,
                                ')'
                            ]).label('Nama_Customer'),
                            vendor.nama.label('Nama_Vendor'),
                            PartnerPay.amt_buy.label('Harga_Beli'),
                            PartnerPay.amt_sell.label('Harga_Jual'),
                            PartnerPay.fee.label('Fee'),
                            case(
                            [
                                (PartnerPay.status <= 0, 0)
                            ],
                            else_=PartnerPay.status).label('Status')
                           ).\
                            join(vendor, vendor.id == PartnerPay.vendor_id)\
                            .join(customer, customer.id == PartnerPay.customer_id)

    if status != 9999:
        if status <= 0:
            query = query.filter(H2hArInvoiceDet.status <= status)
        else:
            query = query.filter(H2hArInvoiceDet.status == status)

    filter_tanggal(request, query, PartnerPay.created)

    return query

def form_validator(form, value):
    pass

def get_vendors():
    vend_get = Partner.get_vendor_list()    
    select_vendor = [(9999, '--Semua--')]    
    for val in vend_get:
        select_vendor.append(tuple((val.id, val.nama)))
    print('isi vendors >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
    print(select_vendor)
    return select_vendor

def get_customers():
    cust_get = Partner.get_customer_list()
    select_customer = [(9999, '--Semua--')]
    for val in cust_get:
        # select_customer.append(val)
        select_customer.append(tuple((val.id, val.nama)))
    print('isi customer >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
    print(select_customer)
    return select_customer

class ViewData(BaseView):
        
        


            # select_vendor.append(val)

        # print('isi list >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
        # print(a)
        # print(b)

    @view_config(route_name='api-payment-list',
                 permission="api-payment-list",
                 renderer='templates/payment/list.pt')
    def view_list(self):
        class ToolbarSchema(colander.Schema):
            status = colander.SchemaNode(
                colander.String(),
                oid="status", 
                widget=widget.SelectWidget(values=status_payment)
            )
            awal = colander.SchemaNode(
                colander.String(),
                oid = "awal",
                title = "Tgl."
            )
            akhir = colander.SchemaNode(
                colander.String(),
                oid = "akhir",
                title = "-"
            )
            cust_sel = colander.SchemaNode(
                colander.String(),
                oid="cust_sel",
                title="Customer",
                widget=widget.SelectWidget(values=get_vendors()),
            )
            vend_sel = colander.SchemaNode(
                colander.String(),
                oid="vend_sel",
                title="Vendor",
                widget=widget.SelectWidget(values=get_customers()),
            )

        toolbar = ToolbarSchema(Validator=form_validator)
        toolbar.request = self.req
        form = Form(schema=toolbar)

        params = {
            'form': form,
            'status_payment': [s for k, s in status_payment if k != 9999],
            'columns': [
                dict(title="ID"),
                dict(title="No. Invoice"),
                dict(title = "Tanggal"),
                dict(title="Customer"),
                dict(title="Vendor"),
                dict(title="Harga Beli"),
                dict(title="Harga Jual"),
                dict(title="Fee"),
                dict(title="Status"),
            ],
            'column_data': [
                dict(data="id", width="0px"),
                dict(data="nomor", width="150px"),
                dict(data = "created"),
                dict(data="customer"),
                dict(data="vendor"),
                dict(data="amount_buy", width="70px"),
                dict(data="amount", width="70px"),
                dict(data="fee", width="70px"),
                dict(data="status", width="70px"),
            ],
            'buttons': [
                dict(id="btn_view", cls="btn btn btn-primary", title="View"),
                dict(id="btn_close", cls="btn btn-danger", title="Tutup"),
                dict(id="btn_csv", cls="btn btn-primary", title="CSV"),
            ],
            'route': "/api/payment",
            'scripts': """
                $("#btn_close").click(function() {
                    window.location = '/api/merchant';
                    return false;
                });
        """}

        return dict(params=params)

    @view_config(route_name='api-payment-act', renderer='json',
                 permission="api-payment-list"
                 )
    def view_act(self):
        request = self.req
        url_dict = request.matchdict
        act = url_dict['act']

        def filter_tanggal(query, field):
            awal  = request.session['awal'] + ' 00:00:00' #None
            akhir = request.session['akhir'] + ' 23:59:59'#None
            
            print('isi awal dan akhir >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>')
            print(awal)
            print(akhir)
            if awal and akhir:
                tglawal    = datetime.strptime(awal, '%d-%m-%Y %H:%M:%S')
                tglakhir   = datetime.strptime(akhir, '%d-%m-%Y %H:%M:%S')
                query = query.filter(field.between(tglawal, tglakhir))

            return query


        if act == "grid":
            status = 9999
            if 'status' in request.params:
                status = int(request.params['status'])
            opt_cust = 9999
            if 'cust' in request.params:
                opt_cust = int(request.params['cust'])
            opt_vend = 9999
            if 'vend' in request.params:
                opt_vend = int(request.params['vend'])

            vendor = aliased(Partner, name='vendor')
            customer = aliased(Partner, name='customer')

            columns = [
                ColumnDT(PartnerPay.id, mData='id'),
                ColumnDT(PartnerPay.cust_inv_no, mData='nomor'),
                # ColumnDT(PartnerPay.created, mData = 'created'),
                ColumnDT(func.to_char(PartnerPay.created, 'dd-mm-yyyy HH24:MI'), mData = 'created'),
                ColumnDT(
                    column_concat([
                        PartnerPay.inv_cust_nm,
                        ' - (',
                        customer.nama,
                        ')'
                    ]),
                    mData='customer'
                ),
                ColumnDT(vendor.nama, mData='vendor'),
                ColumnDT(PartnerPay.amt_buy, mData='amount_buy'),
                ColumnDT(PartnerPay.amt_sell, mData='amount'),
                ColumnDT(PartnerPay.fee, mData='fee'),
                ColumnDT(case(
                    [
                        (PartnerPay.status <= 0, 0)
                    ],
                    else_=PartnerPay.status
                ), mData='status'),
            ]
            # print('masuk query grid')
            query = DBSession.query().select_from(PartnerPay)\
                .join(vendor, vendor.id == PartnerPay.vendor_id)\
                .join(customer, customer.id == PartnerPay.customer_id)
            if status != 9999:
                if status <= 0:
                    query = query.filter(PartnerPay.status <= status)
                else:
                    query = query.filter(PartnerPay.status == status)
            query = filter_tanggal(query, PartnerPay.created)
            if opt_cust != 9999:
                query = query.filter(customer.id == opt_cust)
            if opt_vend != 9999:
                query = query.filter(vendor.id == opt_vend)
            row_table = DataTables(request.GET, query, columns)
            return row_table.output_result()
        elif act == "csv":
            status = 9999
            if 'status' in request.params:
                status = int(request.params['status'])

            query = query_csv(request, status)
            row = query.first()

            if row == None:
                header = [
                    'Nomor_Invoice',
                    'Tanggal',
                    'Nama_Customer',
                    'Nama_Vendor',
                    'Harga_Beli',
                    'Harga_Jual',
                    'Fee',
                    'Status'
                ]
            else:
                header = row.keys()
            rows = []
            for item in query.all():
                rows.append(list(item))

            filename = 'list_payment.csv'
            value = {
                  'header': header,
                  'rows'  : rows,
                }
            return csv_response(request, value, filename)
        elif act == "advice":
            idnya = 0
            if 'id' in request.params:
                idnya = int(request.params['id'])

            row = PartnerPay.query_id(id=idnya).first()
            dat = {
                'tx_id': row.tx_id,
                'invoice_no': row.vend_inv_no
            }

            return payment_advice(dat)

    # @view_config(route_name='api-payment-view',
    #              permission="api-payment-list",
    #              renderer='templates/payment/view.pt')
    # def view_detail(self):
    #     request = self.req
    #     url_dict = request.matchdict
    #     view_id = url_dict['id']
    #     data = PartnerPay.query_id(id=view_id).first()
    #     params = dict(
    #         form=None
    #     )
    #     if data:
    #         customer = '{cust} - ({merc})'.format(cust=data.inv_cust_nm, merc=data.customer.nama)
    #         form_list = (
    #             ('vend_inv_no', data.vend_inv_no or '', 'text'),
    #             ('cust_inv_no', data.cust_inv_no or '', 'text'),
    #             ('tx_id', data.tx_id or '', 'text'),
    #             ('vendor', data.vendor.nama, 'text'),
    #             ('produk', data.produk.nama, 'text'),
    #             # ('id_pel', data.id_pel or '', 'text'),

    #             ('customer', customer, 'text'),
    #             ('cust_addr', data.inv_cust_addr or '', 'text'),
    #             ('cust_phone', data.inv_cust_phone or '', 'text'),
    #             ('cust_email', data.inv_cust_email or '', 'text'),
    #             ('cust_city', data.inv_cust_city or '', 'text'),
    #             ('cust_state', data.inv_cust_state or '', 'text'),
    #             ('cust_pos', data.inv_cust_pos or '', 'text'),
    #             ('cust_country', data.inv_cust_country or '', 'text'),

    #             # ('inv_valid_date', data.description, 'text'),
    #             # ('inv_valid_time', data.description, 'text'),
    #             # ('inv_time_stamp', data.description, 'text'),
    #             # ('inv_cust_va', data.description, 'text'),
    #             #
    #             # ('delivery_addr', data.description, 'text'),
    #             # ('delivery_city', data.description, 'text'),
    #             # ('delivery_country', data.description, 'text'),
    #             # ('delivery_nm', data.description, 'text'),
    #             # ('delivery_phone', data.description, 'text'),
    #             # ('delivery_pos', data.description, 'text'),
    #             # ('delivery_state', data.description, 'text'),
    #             # ('delivery_email', data.description, 'text'),

    #             # ('subtotal', data.subtotal, 'text'),
    #             # ('discount', data.discount, 'text'),
    #             # ('amt_sell', data.amt_sell, 'text'),
    #             ('inquiry', format_json(data.inquiry), 'textarea'),
    #             ('payment', format_json(data.payment), 'textarea'),
    #             ('advice', format_json(data.advice), 'textarea'),
    #             ('notify', format_json(data.notify), 'textarea'),
    #             ('cancel', format_json(data.cancel), 'textarea'),
    #             ('cart', format_json(data.cart), 'textarea'),
    #             # ('notes', data.notes, 'text'),
    #             # ('description', data.description, 'text'),

    #             # ('fee', data.description, 'text'),
    #             # ('instmnt_mon', data.description, 'text'),
    #             # ('instmnt_type', data.description, 'text'),
    #             # ('recurr_opt', data.description, 'text'),

    #             # ('m_ref_no', data.description, 'text'),

    #             # ('notax_amt', data.description, 'text'),
    #             # ('pay_valid_dt', data.description, 'text'),
    #             # ('pay_valid_tm', data.description, 'text'),
    #             #
    #             # ('req_dt', data.description, 'text'),
    #             # ('req_tm', data.description, 'text'),
    #             #
    #             # ('vat', data.description, 'text'),

    #             # ('trans_dt', data.description, 'text'),
    #             # ('trans_tm', data.description, 'text'),
    #             # ('card_no', data.description, 'text'),
    #             # ('callback_url', data.description, 'text'),
    #         )

    #         sm = colander.Schema()
    #         values = {}

    #         for f in form_list:
    #             k = f[0]
    #             v = f[1]
    #             wg = f[2] == 'textarea' and widget.TextAreaWidget(rows=5, css_class="readonly") or\
    #                  widget.TextInputWidget(readonly=True)

    #             sm.add(colander.SchemaNode(
    #                 colander.String(),
    #                 name=k,
    #                 oid=k,
    #                 widget=wg
    #             ))
    #             values.update({k: v})

    #         form = Form(sm)
    #         form.render(values)
    #         params['form'] = form

    #     return dict(params=params)