pcpd.py

# from types import NoneType
import os
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, text
from sqlalchemy.sql.expression import cast
from ..models.payment import PaymentPBB, PaymentPAD, PaymentBPHTB, PaymentWEBR, TargetsBangbadil, UploadRealisasiOpsen
from opensipkd.pbb.monitoring.models import PembayaranSppt, Kecamatan, Kelurahan, Sppt
from opensipkd.pjdl.models import Sspd, Pajak, Usaha, PdlDBSession
from datetime import timedelta
from ..models.payment import PCDBSession

log = logging.getLogger(__name__)

PAD_TYP = {
    "hotel": ["1"],
    "resto": ["2"],
    "hiburan": ["3"],
    "ppju": ["7"],
    "minerba": ["9"],
    "parkir": ["5"],
    "walet": ["10"],
    "atd": ["6"],
    "reklame": ["4"],
    "pbb": ["0"],
}


PAD2_TYP = {
    "hotel": "41011903",
    "resto": "41011901",
    "hiburan": "41011905",
    "ppju": "41011902",
    "minerba": "410114",
    "parkir": "41011904",
    "walet": "410113",
    "atd": "410112",
    "reklame": "410109",
    "pbb": "0",
}

jenis_pajak = {
    "opsen_pkb": {
        "kode": "4.1.01.20.01.0001",
        "nama": "Opsen PKB",
        "subs": []
    },
    "opsen_bbnkb": {
        "kode": "4.1.01.20.02.0001",
        "nama": "Opsen BBNKB",
        "subs": []
    },
    "pbb": {
        "kode": "4.1.1.0",
        "nama": "Pajak Bumi & Bangunan",
        "subs": []
    },
    "bphtb": {
        "kode": "4.1.0.0",
        "nama": "Bea Perolehan Hak atas Tanah dan Bangunan",
         "subs": []
    },
    "resto": {
        "kode": "4.1.1.02",
        "nama": "PBJT - Makanan dan Minuman",
         "subs": []
    },
    "ppju": {
        "kode": "4.1.1.05",
        "nama": "PBJT-Tenaga Listrik",
        "subs": []},
    "hotel": {
        "kode": "4.1.1.01",
        "nama": "PBJT Jasa Perhotelan",
        "subs": []
    },
    "parkir": {
        "kode": "4.1.1.07",
        "nama": "Pajak Parkir"},
    "hiburan": {
        "kode": "4.1.1.03",
        "nama": "PBJT - Jasa Kesenian dan Hiburan",
        "subs": []
    },
    "reklame": {
        "kode": "4.1.1.04",
        "nama": "Pajak Rekalme",
        "subs": []
    },
    "atd": {
        "kode": "4.1.1.08",
        "nama": "Pajak Air Tanah"},
    "walet": {
        "kode": "4.1.1.09",
        "nama": "Pajak Sarang Burung Walet"},
    "minerba": {
        "kode": "4.1.1.06",
        "nama": "Pajak Mineral Bukan Logam dan Batuan",
        "subs": []
    }
}


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(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")]
        if typ == 'opsen_pkb':
            qry = table.qry_sum_pkb(columns) \
                .group_by(field)\
                .order_by(field)
        elif typ == 'opsen_bbnkb':
            qry = table.qry_sum_bbnkb(columns) \
                .group_by(field)\
                .order_by(field)            
        else:
            qry = table.qry_sum(columns) \
                .group_by(field)\
                .order_by(field)
            
        qry = qry.filter(*filters)
        opsen = ["opsen_pkb", "opsen_bbnkb"]
        if typ != None and typ not in opsen:
            if typ == "pajak":
                qry = qry.filter(Usaha.id.notin_(PAD_TYP['pbb']))
            else:
                qry = qry.filter(Usaha.id.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 = float(bayar)
            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 = TargetsBangbadil.query_from([
            func.sum(TargetsBangbadil.t_anggaran).label("murni"),
            func.sum(TargetsBangbadil.t_perubahan).label("perubahan"),])\
            .filter(
            TargetsBangbadil.tahun == self.ses['tahun'],
        )
        if kode:
            if kode == "pajak":
                return 0
            elif kode == 'opsen_pkb':
                targets = targets.filter(
                TargetsBangbadil.kode.ilike('4.1.1.10%'))
            elif kode == 'opsen_bbnkb':
                targets = targets.filter(
                TargetsBangbadil.kode.ilike('4.1.1.11%'))
            elif kode == 'pbb':
                targets = targets.filter(TargetsBangbadil.kode == jenis_pajak[kode]["kode"])
            else:
                targets = targets.filter(
                    TargetsBangbadil.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(PembayaranSppt, field,
                                today.strftime("%Y-%m-%d"), filter_exp)

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

        ytd_target = float(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,
                "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, '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)

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

        
        #Total Tahun Berjalan
        field = func.to_char(Sspd.sspdtgl, 'IYYY')
        filter_exp = Sspd.sspdtgl.between(
            datetime.strptime(f"{today.year}-01-01", "%Y-%m-%d"), today)
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(Sspd, field, str(
                today.year), filter_exp, typ)
        #Bulanan
        field = func.to_char(Sspd.sspdtgl, 'MM')
        month, monthTrx, monthly, monthlyTrx, monthlyAcc, monthlyAccTrx = \
            self.get_daily_data(Sspd, field, None, filter_exp, typ)

        #Mingguan
        field = func.to_char(Sspd.sspdtgl, 'WW')
        filter_exp = Sspd.sspdtgl.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(Sspd,  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, '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)

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

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

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

        #Mingguan
        field = func.to_char(UploadRealisasiOpsen.tanggal, 'YYYY-WW')
        filter_exp = UploadRealisasiOpsen.tanggal.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(UploadRealisasiOpsen,  field, None, filter_exp, 'opsen_pkb')
            
        try:
            ytd_target = self.get_targets("opsen_pkb")
        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,
                "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_bbnkb_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(UploadRealisasiOpsen.tanggal, 'yyyy-mm-dd')
        filter_exp = UploadRealisasiOpsen.tanggal.between(
            today-timedelta(days=6), today)
        amt, trx, daily, dailyTrx, dailyAcc, dailyAccTrx = \
            self.get_daily_data(UploadRealisasiOpsen, field,
                                today.strftime("%Y-%m-%d"), filter_exp, 'opsen_bbnkb')

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

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

        #Mingguan
        field = func.to_char(UploadRealisasiOpsen.tanggal, 'YYYY-WW')
        filter_exp = UploadRealisasiOpsen.tanggal.between(
            today-timedelta(weeks=6), today)
        week, weekTrx, weekly, weeklyTrx, weeklyAcc, weeklyAccTrx = \
            self.get_daily_data(UploadRealisasiOpsen,  field, None, filter_exp, 'opsen_bbnkb')
            
        try:
            ytd_target = self.get_targets("opsen_bbnkb")
        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,
                "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 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_pie(self):
        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_pkb"))
        data.update(self.get_grid_pajak("opsen_bbnkb"))
        
        resp = [{"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]
        
        sum_realisasi = 0
        for d in data : 
            sum_realisasi += data[d]["realisasi"]
            
        hotel = f"{(resp[0]['realisasi']/sum_realisasi)*100:.2f}"
        resto = f"{(resp[1]['realisasi']/sum_realisasi)*100:.2f}"
        hiburan = f"{(resp[2]['realisasi']/sum_realisasi)*100:.2f}"
        parkir = f"{(resp[3]['realisasi']/sum_realisasi)*100:.2f}"
        ppju = f"{(resp[4]['realisasi']/sum_realisasi)*100:.2f}"
        minerba = f"{(resp[5]['realisasi']/sum_realisasi)*100:.2f}"
        walet = f"{(resp[6]['realisasi']/sum_realisasi)*100:.2f}"
        reklame = f"{(resp[7]['realisasi']/sum_realisasi)*100:.2f}"
        atd = f"{(resp[8]['realisasi']/sum_realisasi)*100:.2f}"
        pbb = f"{(resp[9]['realisasi']/sum_realisasi)*100:.2f}"
        bphtb = f"{(resp[10]['realisasi']/sum_realisasi)*100:.2f}"
        opsen_pkb = f"{(resp[11]['realisasi']/sum_realisasi)*100:.2f}"
        opsen_bbnkb = f"{(resp[12]['realisasi']/sum_realisasi)*100:.2f}"
        resp = {
            "pie": [
                (resp[0]['nama'], hotel , '#16a75c'), 
                (resp[1]['nama'], resto, '#ffb900'),
                (resp[2]['nama'], hiburan, "#92149B"), 
                (resp[3]['nama'], parkir, "#1188FF"),
                (resp[4]['nama'], ppju, "#C2E915"),
                (resp[5]['nama'], minerba, "#9BC8F4"),
                (resp[6]['nama'], walet, "#FFA3A3"),
                (resp[7]['nama'], reklame, "#082BA9"),
                (resp[8]['nama'], atd, "#F05D89"),
                (resp[9]['nama'], pbb, "#A7950F"),
                (resp[10]['nama'], bphtb, "#73D3E9"),
                (resp[11]['nama'], opsen_pkb, "#32A7E7"),
                (resp[12]['nama'], opsen_bbnkb, "#F87911"),
                ],
        }
        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 == "opsen_pkb":
            realisasi = self.get_pkb_data()["ytd"]
        
        elif jenis == "opsen_bbnkb":
            realisasi = self.get_bbnkb_data()["ytd"]

        elif jenis == "bphtb":
            realisasi = self.get_bphtb_data()["ytd"]
        else:
            realisasi = self.get_pad_data(jenis)["ytd"]
            #realisasi = self.get_pad_data(jenis)
            #realisasi = realisasi[f"{jenis}"]["realisasi"]

        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_pkb"))
            data.update(self.get_grid_pajak("opsen_bbnkb"))
            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()
        elif act == 'pie':
            return self.get_pie()
        else:
            resp = self.get_pad_data(act)
        resp["time"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        return resp

    def realisasi_pad(self, jenis):
        target = self.get_targets(jenis)
        today = datetime.now().date()
        file_dir = os.path.split(__file__)[0]
        filename = os.path.join(file_dir, 'realisasi_pad.sql')
        rekeningkd = PAD2_TYP[jenis]
        with open(filename, 'r') as f:
            qry = f.read().format(bulan = today.month, tahun=today.year, kondisi=f"and levelid in (3,4) and issummary = 1 and rekeningkd='{rekeningkd}'")
            engine = PdlDBSession.get_bind()
            with engine.connect() as conn:
                result = conn.execute(text(qry.replace('\n', ' ')))
                d = []
                # r = result[0]
                for r in result:
                    d.append(dict(zip(result.keys(), r)))

                d = d[0]
                realisasi = d["realisasi_lalu"] + d["realisasi"]
                return {jenis: {"kode": d["kode"],
                        "nama": d["nama"],
                        "target": target,
                        "realisasi": realisasi,
                        "sisa": target - realisasi,
                        "persen": round(realisasi/target * 100, 2) if target > 0 else 0}
                }
                
    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"}