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, Targets
from opensipkd.pbb.monitoring.models import PembayaranSppt, Kecamatan, Kelurahan, Sppt
from datetime import timedelta
logger = logging.getLogger(__name__)

PAD_TYP = {
    "hotel": ["PBJT - JASA PERHOTELAN"],
    "resto": ["RESTORAN", "PBJT - MAKANAN DAN/ATAU MINUMAN"],
    "hiburan": ["PBJT - JASA KESENIAN DAN HIBURAN"],
    "ppju": ["PBJT - TENAGA LISTRIK"],
    "minerba": ["MINERAL BUKAN LOGAM DAN BATUAN"],
    "opsen": ["OPSEN BBNKB", "OPSEN PKB"],
    "parkir": ["PBJT - JASA PARKIR"],
    "walet": ["PENGELOLAAN SARANG BURUNG WALET DAN SEJENISNYA"],
    "atd": ["AIR TANAH"],
    "reklame": ["REKLAME"],
    "pbb": ["PBB", "BPHTB"]
}

jenis_pajak = {

    "opsen": {
        "kode": "41012",
        "nama": "Opsen",
        "subs": [
            {"kode": "410120010001",
             "nama": "Opsen PKB"},
            {"kode": "410121010001",
             "nama": "Opsen BBNKB"},
        ]},
    "pbb": {
        "kode": "410115",
        "nama": "PBBP2"},
    "bphtb": {
        "kode": "410116",
        "nama": "BPHTB-Pemindahan Hak"},


    "resto": {
        "kode": "41011901",
        "nama": "PBJT Restoran",
        "subs": [
                {"kode": "410119010001",
                 "nama": "PBJT-Restoran"},
                {"kode": "410119010002",
                 "nama": "PBJT-Penyedia Jasa Boga atau Katering"},
                {"kode": "410119020001",
                 "nama": "PBJT-Konsumsi Tenaga Listrik dari Sumber Lain"},
        ]},
    "ppju": {
        "kode": "41011902",
        "nama": "PBJT-Tenaga Listrik",
        "subs": []},
    "hotel": {
        "kode": "41011903",
        "nama": "PBJT Jasa Perhotelan",
        "subs": [
            {"kode": "410119030001", "nama": "PBJT-Hotel"},
            {"kode": "410119030003", "nama": "PBJT-Vila"},
            {"kode": "410119030007", "nama": "PBJT-Wisma Pariwisata"},
        ]},
    "parkir": {
        "kode": "41011904",
        "nama": "PBJT-Penyediaan atau Penyelenggaraan Tempat Parkir"},
    "hiburan": {
        "kode": "41011905",
        "nama": "PBJT-Jasa Kesenian dan Hiburan",
                "subs": [
                    {"kode": "410119050001",
                     "nama": "PBJT-Tontonan Film atau Bentuk Tontonan Audio Visual Lainnya yang Dipertontonkan secara Langsung di Suatu Lokasi Tertentu"},
                    {"kode": "410119050002",
                     "nama": "PBJT-Pergelaran Kesenian, Musik, Tari, dan/atau Busana"},
                    {"kode": "410119050007",
                     "nama": "PBJT-Pacuan Kuda dan Perlombaan Kendaraan Bermotor"},
                    {"kode": "410119050008",
                     "nama": "PBJT-Permainan Ketangkasan"},
                    {"kode": "410119050009",
                     "nama": "PBJT-Olahraga Permainan dengan Menggunakan Tempat/Ruang dan/atau Peralatan dan Perlengkapan untuk Olahraga dan Kebugaran"},
                    {"kode": "410119050010",
                     "nama": "PBJT-Rekreasi Wahana Air, Wahana Ekologi, Wahana Pendidikan, Wahana Budaya, Wahana Salju, Wahana Permainan, Pemancingan, Agrowisata, dan Kebun Binatang"},
                    {"kode": "410119050012",
                     "nama": "PBJT-Distkotek, Karaoke, Kelab Malam, Bar, dan Mandi Uap/Spa"},
                ]},

    "reklame": {
        "kode": "410109",
        "nama": "Pajak Reklame",
        "subs": [
            {"kode": "410109010001",
             "nama": "Pajak Reklame Papan/Billboard/Videotron/Megatron"},
            {"kode": "410109020001", "nama": "Pajak Reklame Kain"},
            {"kode": "410109030001",
             "nama": "Pajak Reklame Melekat/Stiker"},
            {"kode": "410109050001", "nama": "Pajak Reklame Berjalan"},
        ]},
    "atd": {
        "kode": "410112",
        "nama": "Pajak Air Tanah"},
    "walet": {
        "kode": "410113",
        "nama": "Pajak Sarang Burung Walet "},
    "minerba": {
        "kode": "410114",
        "nama": "Pajak Mineral Bukan Logam dan Batuan",
        "subs": [
            {"kode": "410114230001", "nama": "Pajak Pasir dan Kerikil"},
            {"kode": "410114300001", "nama": "Pajak Tanah Liat"},
        ]}
}


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, typ=None):
        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)
        if typ != None:
            if typ == "pajak":
                qry = qry.filter(table.jenis_pajak.notin_(PAD_TYP['pbb']))

            else:
                qry = qry.filter(table.jenis_pajak.in_(PAD_TYP[typ]))
        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, 'MM')
    #     month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
    #         self.get_daily_data(PaymentPBB, field, None, filter_exp)

    #     field = func.to_char(PaymentPBB.tgl_pembayaran_sppt, 'WW')
    #     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)

    #     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_targets(self, kode=None):
        targets = Targets.query_from([func.sum(Targets.t_anggaran).label("murni"),
                                      func.sum(Targets.t_perubahan).label("perubahan"),]).filter(
            Targets.tahun == self.ses['tahun'],
        )
        if kode:
            if kode=="pajak":
                return 0
            targets = targets.filter(
                Targets.kode.ilike(jenis_pajak[kode]["kode"]+'%'))
        targets = targets.first()
        if targets:
            target = targets.perubahan or targets.murni or 0
        else:
            target = 0
        target = float(target)
        return target

    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(PembayaranSppt.tgl_pembayaran_sppt, 'yyyy-mm-dd')
        filter_exp = PembayaranSppt.tgl_pembayaran_sppt.between(
            today-timedelta(6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(PembayaranSppt, field,
                                today.strftime("%Y-%m-%d"), filter_exp)

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

        field = func.to_char(PembayaranSppt.tgl_pembayaran_sppt, 'IYYY')
        filter_exp = PembayaranSppt.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(PembayaranSppt, field,
                                str(today.year),  filter_exp)

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

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

        ytd_target = self.get_targets("pbb")
        ytd_persen = round(ytd/ytd_target * 100, 2) if ytd_target > 0 else 0

        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,
                "ytdTarget": ytd_target,
                "ytdPersen": ytd_persen,
                }

    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)
        ytd_target = float(self.get_targets("bphtb"))
        ytd_persen = round(ytd/ytd_target * 100, 2) if ytd_target > 0 else 0
        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,
                "ytdTarget": ytd_target,
                "ytdPersen": ytd_persen,
                }

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

        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, typ)
        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, typ)

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

        field = func.to_char(PaymentPAD.tgl, '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, typ)
        ytd_target = float(self.get_targets(typ))
        ytd_persen = round(ytd/ytd_target * 100, 2) if ytd_target > 0 else 0
        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,
                "ytdTarget": ytd_target,
                "ytdPersen": ytd_persen,
                }

    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,
                "target": 0,
                }

    def loop_data(self, src, dst):
        for key, val in enumerate(src):
            found = False
            for key2, val2 in enumerate(dst):
                if val[0] == val2[0]:
                    found = True
                    dst[key2][1] += float(val[1])
            if not found:
                dst.append([val[0], float(val[1])])
        dst.sort()

    def get_pajak_data(self):
        pbb = self.get_pbb_data()
        bphtb = self.get_bphtb_data()
        pdl = self.get_pad_data('pajak')
        daily = []
        dailyTrx = []
        dailyAcc = []
        dailyAccTrx = []
        monthly = []
        monthlyTrx = []
        monthlyAcc = []
        monthlyAccTrx = []
        weekly = []
        weeklyTrx = []
        weeklyAcc = []
        weeklyAccTrx = []

        self.loop_data(pbb["daily"], daily)
        self.loop_data(pbb["dailyTrx"], dailyTrx)
        self.loop_data(pbb["dailyAcc"], dailyAcc)
        self.loop_data(pbb["dailyAccTrx"], dailyAccTrx)
        self.loop_data(pbb["monthly"], monthly)
        self.loop_data(pbb["monthlyTrx"], monthlyTrx)
        self.loop_data(pbb["monthlyAcc"], monthlyAcc)
        self.loop_data(pbb["monthlyAccTrx"], monthlyAccTrx)
        self.loop_data(pbb["weekly"], weekly)
        self.loop_data(pbb["weeklyTrx"], weeklyTrx)
        self.loop_data(pbb["weeklyAcc"], weeklyAcc)
        self.loop_data(pbb["weeklyAccTrx"], weeklyAccTrx)

        self.loop_data(bphtb["daily"], daily)
        self.loop_data(bphtb["dailyTrx"], dailyTrx)
        self.loop_data(bphtb["dailyAcc"], dailyAcc)
        self.loop_data(bphtb["dailyAccTrx"], dailyAccTrx)
        self.loop_data(bphtb["monthly"], monthly)
        self.loop_data(bphtb["monthlyTrx"], monthlyTrx)
        self.loop_data(bphtb["monthlyAcc"], monthlyAcc)
        self.loop_data(bphtb["monthlyAccTrx"], monthlyAccTrx)
        self.loop_data(bphtb["weekly"], weekly)
        self.loop_data(bphtb["weeklyTrx"], weeklyTrx)
        self.loop_data(bphtb["weeklyAcc"], weeklyAcc)
        self.loop_data(bphtb["weeklyAccTrx"], weeklyAccTrx)

        self.loop_data(pdl["daily"], daily)
        self.loop_data(pdl["dailyTrx"], dailyTrx)
        self.loop_data(pdl["dailyAcc"], dailyAcc)
        self.loop_data(pdl["dailyAccTrx"], dailyAccTrx)
        self.loop_data(pdl["monthly"], monthly)
        self.loop_data(pdl["monthlyTrx"], monthlyTrx)
        self.loop_data(pdl["monthlyAcc"], monthlyAcc)
        self.loop_data(pdl["monthlyAccTrx"], monthlyAccTrx)
        self.loop_data(pdl["weekly"], weekly)
        self.loop_data(pdl["weeklyTrx"], weeklyTrx)
        self.loop_data(pdl["weeklyAcc"], weeklyAcc)
        self.loop_data(pdl["weeklyAccTrx"], weeklyAccTrx)
        # ytd_target = float(pbb.get("ytdTarget", 0)) + float(bphtb.get("ytdTarget", 0)) + \
        #     float(pdl.get("ytdTarget", 0))
        ytd_target = self.get_targets()
        ytd = float(pbb.get("ytd", 0)) + float(bphtb.get("ytd", 0)) + \
            float(pdl.get("ytd", 0))
        resp = {"today": float(pbb.get("today", 0)) + float(bphtb.get("today", 0)) + float(pdl.get("today", 0)),
                "trx": float(pbb.get("trx", 0)) + float(bphtb.get("trx", 0)) + float(pdl.get("trx", 0)),
                "daily": daily,
                "dailyTrx": dailyTrx,
                "dailyAcc": dailyAcc,
                "dailyAccTrx": dailyAccTrx,
                "ytd": ytd,
                "trxYtd": float(pbb.get("trxYtd", 0)) + float(bphtb.get("trxYtd", 0)) + float(pdl.get("trxYtd", 0)),
                "ytdTarget": ytd_target,
                "ytdPersen": round(ytd/ytd_target * 100, 2) if ytd_target > 0 else 0,
                "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,

                }
        return resp

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

        kecamatan = {f"{row.kd_kecamatan}": {"nama": row.nm_kecamatan,
                                             "ketetapan": 0,
                                             "realisasi": 0,
                                             "sisa": 0,
                                             "persen": 0} for row in rows}
        ketetapan = Sppt.query_from(
            [Sppt.kd_kecamatan,
             func.sum(Sppt.pbb_yg_harus_dibayar_sppt)
             ])\
            .filter(Sppt.thn_pajak_sppt == self.ses['tahun'])\
            .group_by(Sppt.kd_kecamatan)
        for row in ketetapan:
            kecamatan[row[0]]["ketetapan"] = row[1] or 0

        realisasi = PembayaranSppt.query_from(
            [PembayaranSppt.kd_kecamatan,
             func.sum(PembayaranSppt.jml_sppt_yg_dibayar -
                      PembayaranSppt.denda_sppt)
             ])\
            .filter(PembayaranSppt.thn_pajak_sppt == self.tahun)\
            .group_by(PembayaranSppt.kd_kecamatan)
        for row in realisasi:
            kecamatan[row[0]]["realisasi"] = row[1] or 0
            kecamatan[row[0]]["sisa"] = kecamatan[row[0]]["ketetapan"] - row[1]
            kecamatan[row[0]]["persen"] = kecamatan[row[0]]["realisasi"] / \
                kecamatan[row[0]]["ketetapan"] * \
                100 if kecamatan[row[0]]["ketetapan"] > 0 else 0

        return [{"kode": kec, "nama": kecamatan[kec]["nama"],
                 "ketetapan": kecamatan[kec]["ketetapan"],
                 "realisasi": kecamatan[kec]["realisasi"],
                 "sisa": kecamatan[kec]["sisa"],
                 "persen": kecamatan[kec]["persen"],
                 } for kec in kecamatan]

    def get_grid_pajak(self, jenis):

        target = self.get_targets(jenis)
        if jenis == "pbb":
            realisasi = self.get_pbb_data()["ytd"]

        elif jenis=="bphtb":
            realisasi = self.get_bphtb_data()["ytd"]
        else:

            realisasi = self.get_pad_data(jenis)["ytd"]


        return {jenis: {"kode": jenis_pajak[jenis]["kode"].ljust(8, '0'),
                        "nama": jenis_pajak[jenis]["nama"],
                        "target": target,
                        "realisasi": realisasi,
                        "sisa": target - realisasi,
                        
                        "persen": round(realisasi/target * 100,2) if target > 0 else 0}
                }

    @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':
            data = {}
            data.update(self.get_grid_pajak("hotel"))
            data.update(self.get_grid_pajak("resto"))
            data.update(self.get_grid_pajak("hiburan"))
            data.update(self.get_grid_pajak("parkir"))
            data.update(self.get_grid_pajak("ppju"))
            data.update(self.get_grid_pajak("minerba"))
            data.update(self.get_grid_pajak("walet"))
            data.update(self.get_grid_pajak("reklame"))
            data.update(self.get_grid_pajak("atd"))
            data.update(self.get_grid_pajak("pbb"))
            data.update(self.get_grid_pajak("bphtb"))
            data.update(self.get_grid_pajak("opsen"))
            return [{"kode": data[d]["kode"],
                     "nama": data[d]["nama"],
                     "target": data[d]["target"],
                     "realisasi": data[d]["realisasi"],
                     "sisa": data[d]["sisa"],
                     "persen": data[d]["persen"]} for d in data]

        elif act == 'pbb':
            resp = self.get_pbb_data()
            resp["tabel"] = self.get_pbb_area()

            ketetapan = Sppt.query_from(func.sum(Sppt.pbb_yg_harus_dibayar_sppt))\
                .filter(Sppt.thn_pajak_sppt == tahun).scalar()
            realisasi = PembayaranSppt.query_from(
                func.sum(PembayaranSppt.jml_sppt_yg_dibayar - PembayaranSppt.denda_sppt))\
                .filter(PembayaranSppt.thn_pajak_sppt == tahun).scalar()
            bagi = 1000000
            resp["realisasi"] = [["Ketetapan", ketetapan and ketetapan/bagi or 0],
                                 ["Realisasi", realisasi and realisasi/bagi or 0]]

        elif act == 'bphtb':
            resp = self.get_bphtb_data()
        elif act == 'webr':
            resp = self.get_webr_data()
        elif act == 'pajak':
            resp = self.get_pajak_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"}

    def view_walet(self):
        return {"module": 'walet',
                "title": "Pajak Sarang Burung Walet"}