pcpd.py

# from types import NoneType
from pyramid.view import (
    view_config,
)
from ..views import BaseView
from ..models import *
from ..models.eis import Eis, Chart, ChartItem, Slide
from datatables import ColumnDT, DataTables
from sqlalchemy import func, and_, String
from sqlalchemy.sql.expression import cast
from ..models.payment import PaymentPBB, PaymentPAD, PaymentBPHTB, PaymentWEBR
from datetime import timedelta
logger = logging.getLogger(__name__)


class Views(BaseView):
    @view_config(route_name='eis-pcpd', renderer='templates/pcpd.pt',
                 permission='eis')
    def view_app(self):
        return {"module": "pad",
                "title": "Realisasi Pajak dan Retribusi Daerah"}

    def get_dummy_data(self):
        return {"today": 1000,
                "trx": 155,
                "ytd": 10000,
                "trxYtd": 1550,
                "pie": [("I", 100, '#16a75c'), ("II", 200, '#ffb900'),
                        ("III", 300, '#133132'), ("IV", 200, '#123456'),
                        ("V", 100, '#545355')],
                "daily": [("I", 100), ("II", 200), ("III", 300), ("IV", 200), ("V", 100), ("VI", 100), ("VII", 700)],
                "dailyTrx": [("I", 50), ("II", 25), ("III", 10), ("IV", 10), ("V", 50), ("VI", 34), ("VII", 35)],
                "dailyAcc": [("I", 100), ("II", 200), ("III", 300), ("IV", 200), ("V", 100), ("VI", 100), ("VII", 700)],
                "dailyAccTrx": [("I", 100), ("II", 200), ("III", 300), ("IV", 200), ("V", 100), ("VI", 100), ("VII", 700)],
                "weekly": [("1", 100), ("2", 200), ("3", 300), ("4", 200), ("5", 100), ("6", 100), ("7", 700)],
                "weeklyTrx": [("1", 100), ("2", 200), ("3", 300), ("4", 200), ("5", 100), ("6", 100), ("7", 700)],
                "weeklyAcc": [("1", 100), ("2", 200), ("3", 300), ("4", 200), ("5", 100), ("6", 100), ("7", 700)],
                "weeklyAccTrx": [("1", 100), ("2", 200), ("3", 300), ("4", 200), ("5", 100), ("6", 100), ("7", 700)],
                "monthly": [("JAN", 100), ("FEB", 200), ("MAR", 300), ("APR", 200), ("MAY", 100), ("JUN", 100), ("JUL", 700)],
                "monthlyTrx": [("JAN", 100), ("FEB", 200), ("MAR", 300), ("APR", 400), ("MAY", 500), ("JUN", 600), ("JUL", 700)],
                "monthlyAcc": [("JAN", 100), ("FEB", 200), ("MAR", 300), ("APR", 200), ("MAY", 100), ("JUN", 100), ("JUL", 700)],
                "monthtrAccTrx": [("JAN", 100), ("FEB", 200), ("MAR", 300), ("APR", 200), ("MAY", 100), ("JUN", 100), ("JUL", 700)],
                }

    def get_daily_data(self, table, field, today, filters):
        columns = [field.label("step")]
        qry = table.qry_sum(columns) \
            .group_by(field)\
            .order_by(field)
        # for i, f in enumerate(filters):
        qry = qry.filter(filters)
        daily = []
        dailyTrx = []
        dailyAcc = []
        dailyAccTrx = []
        amt = trx = acc = accTrx = 0
        for r in qry:
            step = r[3]
            bayar = r[0] or 0
            denda = r[1] or 0
            pokok = bayar - denda
            tx = r[2] or 0
            daily.append((step, pokok))
            dailyTrx.append((step, tx))
            acc += pokok
            accTrx += tx
            dailyAcc.append((step, acc))
            dailyAccTrx.append((step, accTrx))
            if step == today:
                amt = pokok
                trx = tx
        return amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx

    def get_pbb_data(self):
        if log.root.level == logging.DEBUG:
            today = datetime.strptime('2025-06-05', "%Y-%m-%d")
        else:
            today = datetime.now().date()

        field = func.to_char(PaymentPBB.tgl_pembayaran_sppt, 'yyyy-mm-dd')
        filter_exp = PaymentPBB.tgl_pembayaran_sppt.between(
            today-timedelta(6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(PaymentPBB, field,
                                today.strftime("%Y-%m-%d"), filter_exp)

        filter_exp = PaymentPBB.tgl_pembayaran_sppt.between(
            today-timedelta(days=6), today)

        field = func.to_char(PaymentPBB.tgl_pembayaran_sppt, 'IYYY')
        filter_exp = PaymentPBB.tgl_pembayaran_sppt.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today)
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentPBB, field,
                                str(today.year),  filter_exp)

        field = func.to_char(PaymentPBB.tgl_pembayaran_sppt, 'IYYY-MM')
        month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
            self.get_daily_data(PaymentPBB, field, None, filter_exp)

        filter_exp = PaymentPBB.tgl_pembayaran_sppt.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(PaymentPBB,  field, None, filter_exp)

        field = func.to_char(PaymentPBB.tgl_pembayaran_sppt, 'YYYY-IW')
        return {"today": amt,
                "trx": trx,
                "daily": daily,
                "dailyTrx": dailyTrx,
                "dailyAcc": dailyAcc,
                "dailyAccTrx": dailyAccTrx,
                "ytd": ytd,
                "trxYtd": ytdTrx,
                "pie": [("I", 100, '#16a75c'), ("II", 200, '#ffb900'),
                        ("III", 300, '#133132'), ("IV", 200, '#123456'),
                        ("V", 100, '#545355')],
                "weekly": weekly,
                "weeklyTrx": weeklyTrx,
                "weeklyAcc": weeklyAcc,
                "weeklyAccTrx": weeklyAccTrx,
                "monthly": monthly,
                "monthlyTrx": monthlyTrx,
                "monthlyAcc": monthlyAcc,
                "monthlyAccTrx": monthlyAccTrx,
                }

    def get_bphtb_data(self):
        if log.root.level == logging.DEBUG:
            today = datetime.strptime('2025-06-05', "%Y-%m-%d")
        else:
            today = datetime.now().date()
        field = func.to_char(PaymentBPHTB.tgl, 'yyyy-mm-dd')
        filter_exp = PaymentBPHTB.tgl.between(
            today-timedelta(6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(PaymentBPHTB, field,
                                today.strftime("%Y-%m-%d"), filter_exp)
        filter_exp = PaymentBPHTB.tgl.between(
            today-timedelta(days=6), today)

        field = func.to_char(PaymentBPHTB.tgl, 'IYYY')
        filter_exp = PaymentBPHTB.tgl.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today)
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentBPHTB, field,
                                str(today.year), filter_exp)

        field = func.to_char(PaymentBPHTB.tgl, 'MM')
        month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
            self.get_daily_data(PaymentBPHTB, field, None, filter_exp)

        field = func.to_char(PaymentBPHTB.tgl, 'WW')
        filter_exp = PaymentBPHTB.tgl.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(PaymentBPHTB, field, None, filter_exp)

        return {"today": amt,
                "trx": trx,
                "daily": daily,
                "dailyTrx": dailyTrx,
                "dailyAcc": dailyAcc,
                "dailyAccTrx": dailyAccTrx,
                "ytd": ytd,
                "trxYtd": ytdTrx,
                "pie": [("I", 100, '#16a75c'), ("II", 200, '#ffb900'),
                        ("III", 300, '#133132'), ("IV", 200, '#123456'),
                        ("V", 100, '#545355')],
                "weekly": weekly,
                "weeklyTrx": weeklyTrx,
                "weeklyAcc": weeklyAcc,
                "weeklyAccTrx": weeklyAccTrx,
                "monthly": monthly,
                "monthlyTrx": monthlyTrx,
                "monthlyAcc": monthlyAcc,
                "monthlyAccTrx": monthlyAccTrx,
                }

    def get_pad_data(self, typ):
        if log.root.level == logging.DEBUG:
            today = datetime.strptime('2025-06-05', "%Y-%m-%d")
        else:
            today = datetime.now().date()
        filter_ex = []
        if typ == 'hotel':
            filter_ex = [PaymentPAD.jenis_pajak == "PBJT - JASA PERHOTELAN",]

        field = func.to_char(PaymentPAD.tgl, 'yyyy-mm-dd')
        filter_exp = PaymentPAD.tgl.between(
            today-timedelta(6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(PaymentPAD, field,
                                today.strftime("%Y-%m-%d"), filter_exp)
        filter_exp = PaymentPAD.tgl.between(
            today-timedelta(days=6), today)

        field = func.to_char(PaymentPAD.tgl, 'IYYY')
        filter_exp = PaymentPAD.tgl.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today)
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentPAD, field, str(today.year), filter_exp)

        field = func.to_char(PaymentPAD.tgl, 'YYYY-MM')
        month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
            self.get_daily_data(PaymentPAD, field, None, filter_exp)

        field = func.to_char(PaymentPAD.tgl, 'YYYY-WW')
        filter_exp = PaymentPAD.tgl.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(PaymentPAD,  field, None, filter_exp)

        return {"today": amt,
                "trx": trx,
                "daily": daily,
                "dailyTrx": dailyTrx,
                "dailyAcc": dailyAcc,
                "dailyAccTrx": dailyAccTrx,
                "ytd": ytd,
                "trxYtd": ytdTrx,
                "pie": [("I", 100, '#16a75c'), ("II", 200, '#ffb900'),
                        ("III", 300, '#133132'), ("IV", 200, '#123456'),
                        ("V", 100, '#545355')],
                "weekly": weekly,
                "weeklyTrx": weeklyTrx,
                "weeklyAcc": weeklyAcc,
                "weeklyAccTrx": weeklyAccTrx,
                "monthly": monthly,
                "monthlyTrx": monthlyTrx,
                "monthlyAcc": monthlyAcc,
                "monthlyAccTrx": monthlyAccTrx,
                }


    def get_webr_data(self):
        if log.root.level == logging.DEBUG:
            today = datetime.strptime('2025-06-05', "%Y-%m-%d")
        else:
            today = datetime.now().date()
        filter_ex = []
        # if typ == 'hotel':
            # filter_ex = [PaymentPAD.jenis_pajak == "PBJT - JASA PERHOTELAN",]

        field = func.to_char(PaymentWEBR.tgl, 'yyyy-mm-dd')
        filter_exp = PaymentWEBR.tgl.between(
            today-timedelta(6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(PaymentWEBR, field,
                                today.strftime("%Y-%m-%d"), filter_exp)
        filter_exp = PaymentWEBR.tgl.between(
            today-timedelta(days=6), today)

        field = func.to_char(PaymentWEBR.tgl, 'IYYY')
        filter_exp = PaymentWEBR.tgl.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today)
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentWEBR, field, str(today.year), filter_exp)

        field = func.to_char(PaymentWEBR.tgl, 'YYYY-MM')
        month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
            self.get_daily_data(PaymentWEBR, field, None, filter_exp)

        field = func.to_char(PaymentWEBR.tgl, 'YYYY-WW')
        filter_exp = PaymentPAD.tgl.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(PaymentWEBR,  field, None, filter_exp)

        return {"today": amt,
                "trx": trx,
                "daily": daily,
                "dailyTrx": dailyTrx,
                "dailyAcc": dailyAcc,
                "dailyAccTrx": dailyAccTrx,
                "ytd": ytd,
                "trxYtd": ytdTrx,
                "pie": [("I", 100, '#16a75c'), ("II", 200, '#ffb900'),
                        ("III", 300, '#133132'), ("IV", 200, '#123456'),
                        ("V", 100, '#545355')],
                "weekly": weekly,
                "weeklyTrx": weeklyTrx,
                "weeklyAcc": weeklyAcc,
                "weeklyAccTrx": weeklyAccTrx,
                "monthly": monthly,
                "monthlyTrx": monthlyTrx,
                "monthlyAcc": monthlyAcc,
                "monthlyAccTrx": monthlyAccTrx,
                }

    @view_config(route_name='eis-pcpd-act', renderer='json')
    def view_app_act(self):
        tahun = self.ses['tahun']
        req = self.req
        ses = req.session
        params = req.params
        id = 'id' in params and params['id'] or 0
        json_data = {}
        json_data['success'] = False
        act = req.matchdict['act']
        if act == 'grid':
            rows = DBSession.query(Eis).filter(Eis.id == id)
            if not rows:
                return json_data
        elif act == 'pbb':
            resp = self.get_pbb_data()
        elif act == 'bphtb':
            resp = self.get_bphtb_data()
        elif act == 'webr':
            resp = self.get_webr_data()
        else:
            resp = self.get_pad_data(act)
        resp["time"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        return resp

    def view_pajak(self):
        return {"module": 'pajak',
                "title": "Realisasi Pajak Daerah"}

    def view_pbb(self):
        return {"module": 'pbb',
                "title": "Realisasi Pajak Bumi dan Bangunan P2"}

    def view_bphtb(self):
        return {"module": 'bphtb',
                "title": "Realisasi BPHTB"}

    def view_hotel(self):
        return {"module": 'hotel',
                "title": "Realisasi Pajak Hotel"}

    def view_resto(self):
        return {"module": 'resto',
                "title": "Realisasi Pajak Restoran"}

    def view_parkir(self):
        return {"module": 'parkir',
                "title": "Realisasi Pajak Parkir"}

    def view_hiburan(self):
        return {"module": 'hiburan',
                "title": "Realisasi Pajak Hiburan"}

    def view_ppju(self):
        return {"module": 'ppju',
                "title": "Realisasi Pajak Penerangan Jalan Umum"}

    def view_minerba(self):
        return {"module": 'minerba',
                "title": "Realisasi Pajak Minerba"}

    def view_reklame(self):
        return {"module": 'reklame',
                "title": "Realisasi Pajak Reklame"}

    def view_atd(self):
        return {"module": 'atd',
                "title": "Realisasi Pajak Air Tanah"}

    def view_webr(self):
        return {"module": 'webr',
                "title": "Realisasi Retribusi"}

    def view_opsen(self):
        return {"module": 'opsen',
                "title": "Opsen Pajak PKB dan BBNKB"}