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 opensipkd.webr.models.lra import LraModel
from datetime import timedelta
from ..models.payment import PCDBSession
from opensipkd.webr.models import WebrAnggaran, WebrArPayment
log = 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 PKB dan BBNKB",
        "subs": [
            {"kode": "410120010001",
             "nama": "Opsen PKB"},
            {"kode": "410121010001",
             "nama": "Opsen BBNKB"},
        ]},
    "pbb": {
        "kode": "410115",
        "nama": "Pajak Bumi dan Bangunan Perdesaan dan Perkotaan (PBBP2)"},
    "bphtb": {
        "kode": "410116",
        "nama": "Bea Perolehan Hak Atas Tanah dan Bangunan (BPHTB)"},


    "resto": {
        "kode": "41011901",
        "nama": "PBJT-Makanan dan/atau Minuman",
        "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": [
            {"kode": "410119020001",
                 "nama": "PBJT-Konsumsi Tenaga Listrik dari Sumber Lain"},
            {"kode": "410119020002",
                 "nama": "PBJT-Konsumsi Tenaga Listrik yang Dihasilkan Sendiri"},
            ]},
    "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-Jasa 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):
    def __init__(self, request):
        super().__init__(request)
        self.db_session = PCDBSession

    @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_pbb(self, table, field, today, filters, typ=None):
        # todo ada kemungkinan menyebabkan error ke transaksi yang lain
        if type(filters) is not list:
            filters = [filters]
        if PCDBSession.registry().in_transaction():
            PCDBSession.rollback()
        columns = [field.label("step")]
        qry = table.qry_sum(columns) \
            .group_by(field)\
            .order_by(field)

        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:
            mapped = r._mapping
            step = mapped.get("step", "")
            pokok = mapped.get("bayar", 0)
            denda = mapped.get("denda", 0)
            bayar = pokok + denda
            tx = mapped.get("trx", 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_daily_data(self, table, field, today, filters, typ=None):
        # todo ada kemungkinan menyebabkan error ke transaksi yang lain
        if type(filters) is not list:
            filters = [filters]
        if PCDBSession.registry().in_transaction():
            PCDBSession.rollback()
        columns = [field.label("step")]
        qry = table.qry_sum(columns) \
            .group_by(field)\
            .order_by(field)

        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:
            mapped = r._mapping
            step = mapped.get("step", "")
            bayar = mapped.get("bayar", 0)
            denda = mapped.get("denda", 0)
            pokok = bayar - denda
            tx = mapped.get("trx", 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_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):
        today = datetime.now().date()
        # Piutang

        # Harian
        field = func.to_char(PembayaranSppt.tgl_pembayaran_sppt, 'yyyy-mm-dd')
        filter_exp = PembayaranSppt.tgl_pembayaran_sppt.between(
            today-timedelta(days=6), today)

        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data_pbb(PembayaranSppt, field,
                                today.strftime("%Y-%m-%d"), filter_exp)

        # Total Setahun
        field = func.to_char(PembayaranSppt.tgl_pembayaran_sppt, 'YYYY')
        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_pbb(PembayaranSppt, field,
                                str(today.year),  filter_exp)
        # Piutang
        filter_exp = [PembayaranSppt.tgl_pembayaran_sppt.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today),
            PembayaranSppt.thn_pajak_sppt < today.year]
        piutang, piutangTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data_pbb(PembayaranSppt, field,
                                str(today.year),  filter_exp)

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

        # Mingguan
        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_pbb(PembayaranSppt,  field, None, filter_exp)

        try:
            ytd_target = self.get_targets("pbb")
        except:
            ytd_target = 0

        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,
                "pokok": ytd-piutang,
                "piutang": piutang,
                "piutangTrx": piutangTrx,
                "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()

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

        # Total Tahunan
        field = func.to_char(PaymentBPHTB.tgl, 'YYYY')
        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)

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

        # Mingguan
        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 = []

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

        # Total Tahun Berjalan
        field = func.to_char(PaymentPAD.tgl, 'YYYY')
        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)

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

        # Mingguan
        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)

        # Target
        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()

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

        # Tahunan
        field = func.to_char(PaymentWEBR.tgl, 'YYYY')
        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)

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

        # Mingguan
        field = func.to_char(PaymentWEBR.tgl, 'YYYY-WW')
        filter_exp = PaymentWEBR.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 get_lra_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        return LraModel.sum_rinci(tahun, '4.1.04')


    def get_lrar_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        return LraModel.sum_rekap(tahun, '4.1.04')


    def get_hpkdd_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        return LraModel.sum_rinci(tahun, '4.1.03')
    
    def get_hpkddr_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        return LraModel.sum_rekap(tahun, '4.1.03')

    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}
                }

    def get_ret_dinas_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        anggaran = WebrAnggaran.sum_departemen(tahun)
        for dept in anggaran:
            d = dept._asdict()
            result.append({"kode": d["departemen_kode"],
                           "nama": d["departemen_nama"],
                           "target": float(d["total_perubahan"]) or float(d["total_murni"])  or 0,
                           "realisasi":  0,
                           }
                          )

        realisasi = WebrArPayment.sum_departemen(tahun)
        for dept in realisasi:
            found = False
            d = dept._asdict()
            for res in result:
                if d["departemen_kode"] == res["kode"]:
                    found = True
                    res["realisasi"] = float(d["total_pokok"])   or 0
                    res["sisa"] = res["target"] - res["realisasi"]
                    res["persen"] = round(
                        res["realisasi"]/res["target"] * 100, 2) if res["target"] > 0 else 0

            if not found:
                result.append({"kode": d["departemen_kode"],
                               "nama": d["departemen_nama"],
                               "target": 0,
                               "realisasi": float(dept.total_pokok) or 0,
                               "sisa": 0 - (float(dept.total_pokok) or 0),
                               "persen": 0,
                               }
                              )
        return result

    def get_ret_jenis_data(self):
        today = datetime.now().date()
        result = []
        tahun = today.year
        anggaran = WebrAnggaran.sum_rekening(tahun)
        for dept in anggaran:
            result.append({"kode": dept.rekening_kode,
                          "nama": dept.rekening_nama,
                           "target": dept.total_perubahan or dept.total_murni or 0,
                           "realisasi":  0,
                           }
                          )

        realisasi = WebrArPayment.sum_rekening(tahun)
        for dept in realisasi:
            found = False
            for res in result:
                if dept.rekening_kode == res["kode"]:
                    found = True
                    res["realisasi"] = float(dept.total_pokok) or 0
                    res["sisa"] = res["target"] - res["realisasi"]
                    res["persen"] = round(
                        res["realisasi"]/res["target"] * 100, 2) if res["target"] > 0 else 0

            if not found:
                result.append({"kode": dept.rekening_kode,
                              "nama": dept.rekening_nama,
                               "target": 0,
                               "realisasi": float(dept.total_pokok) or 0,
                               "sisa": 0 - (float(dept.total_pokok) or 0),
                               "persen": 0,
                               }
                              )
        return result

    @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))\
                .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()
        elif act == 'rdinas':
            return self.get_ret_dinas_data()
        elif act == 'rjenis':
            return self.get_ret_jenis_data()
        elif act == 'lra':
            return self.get_lra_data() 
        elif act == 'lrar':
            return self.get_lrar_data()

        elif act == 'hpkdd':
            return self.get_hpkdd_data() 
        elif act == 'hpkddr':
            return self.get_hpkddr_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 PBJT-MAMIN"}

    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"}

    def view_lra(self):
        return {"module": 'lra',
                "title": "Pendapatan Lain-Lain yang Sah"}

    def view_hpkdd(self):
        return {"module": 'hpkdd',
                "title": "Hasil Pengelolaan Kekayaan Daerah yang Dipisahkan"}