penerimaan.py

from pyramid.view import (
    view_config,
)
from ..views import BaseView
from ..models import *
import colander
from deform import widget
from opensipkd.base.widgets import widget_os
from pyramid.view import view_config
from ..models.payment import PaymentPBB, PaymentPAD, PaymentBPHTB, PaymentWEBR, Targets, UploadRealisasiOpsen
from opensipkd.pbb.monitoring.models import PembayaranSppt, Kecamatan, Kelurahan, Sppt
from . import BaseView
from ..models import Targets
from ..models.payment import PCDBSession
from datetime import timedelta
from sqlalchemy import func, and_, String
from opensipkd.pjdl.models import Sspd, Pajak, Usaha

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

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

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.list_route = 'eis-pkbbbnkb'

    # def view_list(self):
    #     return super().view_list()

    def view_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"],
                     "realisasi_2022": data[d]["realisasi_2022"],
                     "realisasi_2023": data[d]["realisasi_2023"],
                     "realisasi_2024": data[d]["realisasi_2024"],
                     "realisasi_2025": data[d]["realisasi_2025"],
                     } for d in data]
        return super().view_act()

    def get_grid_pajak(self, jenis):
        realisasi_2022 = 0
        realisasi_2023 = 0
        realisasi_2024 = 0
        realisasi_2025 = 0
        
        if jenis == "pbb":
            realisasi_2022 = self.get_pbb_data('2022')["ytd"]
            realisasi_2023 = self.get_pbb_data('2023')["ytd"]
            realisasi_2024 = self.get_pbb_data('2024')["ytd"]
            realisasi_2025 = self.get_pbb_data('2025')["ytd"]
            
        elif jenis == "opsen_pkb":
            realisasi_2022 = self.get_pkb_data('2022')["ytd"]
            realisasi_2023 = self.get_pkb_data('2023')["ytd"]
            realisasi_2024 = self.get_pkb_data('2024')["ytd"]
            realisasi_2025 = self.get_pkb_data('2025')["ytd"]
        
        elif jenis == "opsen_bbnkb":
            realisasi_2022 = self.get_bbnkb_data('2022')["ytd"]
            realisasi_2023 = self.get_bbnkb_data('2023')["ytd"]
            realisasi_2024 = self.get_bbnkb_data('2024')["ytd"]
            realisasi_2025 = self.get_bbnkb_data('2025')["ytd"]

        elif jenis == "bphtb":
            realisasi_2022 = self.get_bphtb_data('2022')["ytd"]
            realisasi_2023 = self.get_bphtb_data('2023')["ytd"]
            realisasi_2024 = self.get_bphtb_data('2024')["ytd"]
            realisasi_2025 = self.get_bphtb_data('2025')["ytd"]
        else:
            realisasi_2022 = self.get_pad_data(jenis, '2022')["ytd"]
            realisasi_2023 = self.get_pad_data(jenis, '2023')["ytd"]
            realisasi_2024 = self.get_pad_data(jenis, '2024')["ytd"]
            realisasi_2025 = self.get_pad_data(jenis, '2025')["ytd"]

        return {jenis: {"kode": jenis_pajak[jenis]["kode"].ljust(8, '0'),
                        "nama": jenis_pajak[jenis]["nama"],
                        "realisasi_2022": realisasi_2022,
                        "realisasi_2023": realisasi_2023,
                        "realisasi_2024": realisasi_2024,
                        "realisasi_2025": realisasi_2025,
                        }
                }
        
    def get_pbb_data(self, tahun):
        today = datetime.now().date()
        #Total Setahun
        field = func.to_char(PembayaranSppt.tgl_pembayaran_sppt, 'IYYY')
        filter_exp = PembayaranSppt.tgl_pembayaran_sppt.between(
            datetime.strptime(f"{tahun}-01-01", "%Y-%m-%d"), datetime.strptime(f"{tahun}-12-31", "%Y-%m-%d"))
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PembayaranSppt, field,
                                str(tahun),  filter_exp)
       

        return {
                "ytd": ytd,
                }
    
    def get_bphtb_data(self, tahun):
        #Total Tahunan
        field = func.to_char(PaymentBPHTB.tgl, 'IYYY')
        filter_exp = PaymentBPHTB.tgl.between(
            datetime.strptime(f"{tahun}-01-01", "%Y-%m-%d"),  datetime.strptime(f"{tahun}-12-31", "%Y-%m-%d"))
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentBPHTB, field,
                                str(tahun), filter_exp)
            
        return {
                "ytd": ytd,
                }

    def get_pad_data(self, typ=None, tahun=None):
        #Total Tahun Berjalan
        field = func.to_char(Sspd.sspdtgl, 'IYYY')
        filter_exp = Sspd.sspdtgl.between(
            datetime.strptime(f"{tahun}-01-01", "%Y-%m-%d"), datetime.strptime(f"{tahun}-12-31", "%Y-%m-%d"))
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(Sspd, field, str(
                tahun), filter_exp, typ)

        return {
                "ytd": ytd,
                }

    def get_webr_data(self, tahun):
        #Tahunan
        field = func.to_char(PaymentWEBR.tgl, 'IYYY')
        filter_exp = PaymentWEBR.tgl.between(
            datetime.strptime(f"{tahun}-01-01", "%Y-%m-%d"), datetime.strptime(f"{tahun}-12-31", "%Y-%m-%d"))
        ytd, ytdTrx, yearly, yearlyTrx, yearlyAcc, yearlyAccTrx = \
            self.get_daily_data(PaymentWEBR, field,
                                str(tahun), filter_exp)

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

        return {
                "ytd": ytd,
                }


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

        return {
                "ytd": ytd,
                }

        
    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 = int(mapped.get("denda", 0) or 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