spt_invoice.py

from datetime import datetime
from math import ceil
from sqlalchemy import (Boolean, Column, Integer, DateTime, ForeignKey,
                        UniqueConstraint, String, SmallInteger,
                        Index, BigInteger, Float, cast, desc, func)
from sqlalchemy.orm import (relationship)

from opensipkd.pjdl.models.pajak import PjdlPajakTarif
from . import PAD_TABLE_ARGS, DefaultModel, Base
from dateutil.relativedelta import relativedelta
from . import (PjdlWp, PjdlOp, DBSession,
               PjdlRekening, PjdlPajak, PjdlSptType,)

from opensipkd.tools.pbb import hitung_bulan

TARIF_LAIN = [
    (1, 'Tidak ada', 0),
    (2, 'Produk Rokok +25%', 0.25),
    (3, 'Reklame Pendidikan -25%', -0.25),
    (4, 'Kenaikan 25% & Pengurangan 25%', 0),
]


def tarif_lain():
    return [(r[0], r[1]) for r in TARIF_LAIN]


REK_STATUS = [
    ("Pasang Baru", "Pasang Baru"),
    ("Perpanjangan", "Perpanjangan")
]


def hitung_denda(piutang_pokok, jatuh_tempo, tanggal=None, max_denda=24, persen_denda=2):
    # jatuh_tempo = jatuh_tempo.date()
    bln_tunggakan = hitung_bulan(jatuh_tempo, tanggal, max_denda)
    if jatuh_tempo.year < 2023:
        persen_denda = 2
    elif jatuh_tempo.year < 2025:
        persen_denda = 1

    return int(ceil(bln_tunggakan * persen_denda / 100.0 * piutang_pokok))


class PjdlInvoice(Base, DefaultModel):
    __tablename__ = 'pad_spt'
    __table_args__ = (
        UniqueConstraint('tahun', 'customer_usaha_id', 'masadari', 'masasd'),
        UniqueConstraint('tahun', 'sptno', name="uq_pad_spt_sptno"),
        Index('pad_spt_tahun_sptno_customer_id_customer_usaha_id_rekening__idx',
              'tahun', 'sptno', 'customer_id', 'customer_usaha_id', 'rekening_id',
              'pajak_id'),
        PAD_TABLE_ARGS
    )

    id = Column(BigInteger, primary_key=True, index=True)
    create_date = Column(DateTime)
    create_uid = Column(Integer)
    write_date = Column(DateTime)
    write_uid = Column(Integer)
    tahun = Column(Integer, nullable=False, index=True)
    sptno = Column(Integer, nullable=False, index=True)
    customer_id = Column(ForeignKey(
        PjdlWp.id, onupdate='CASCADE'), nullable=False, index=True)
    customer_usaha_id = Column(
        ForeignKey(PjdlOp.id, ondelete='CASCADE', onupdate='CASCADE'),
        nullable=False, index=True)
    pajak_id = Column(ForeignKey(PjdlPajak.id, onupdate='CASCADE'),
                      nullable=False)
    rekening_id = Column(ForeignKey(PjdlRekening.id, onupdate='CASCADE'))
    type_id = Column(ForeignKey(PjdlSptType.id, onupdate='CASCADE'))
    nama_wp = Column(String(255))
    nama_op = Column(String(255))
    so = Column(String(1), nullable=False, index=True)
    masadari = Column(DateTime, nullable=False)
    masasd = Column(DateTime, nullable=False)
    jatuhtempotgl = Column(DateTime)
    minomset = Column(Float(53), default=0)
    dasar_yg_blm_dibayar = Column(BigInteger)
    dasar_yg_sdh_dibayar = Column(BigInteger)
    dasar = Column(Float(53), nullable=False, default=0)
    tarif = Column(Float(53), nullable=False, default=0)
    pokok = Column(Float(53), default=0)  # dasar * tarif
    denda = Column(Float(53), default=0)
    setoran = Column(Float(53), default=0)
    kenaikan = Column(Float(53), nullable=False, default=0)
    kompensasi = Column(Float(53), default=0)
    lain2 = Column(Float(53), nullable=False, default=0)
    pokok_pajak = Column(Float(53), default=0)  # Baru ditambah
    bunga = Column(Float(53), nullable=False, default=0)
    pajak_terhutang = Column(BigInteger, nullable=False, default=0)
    opsen_tarif = Column(Float(53), default=0)
    opsen_pokok = Column(Float(53), default=0)
    opsen_denda = Column(Float(53), default=0)
    opsen_pokok_pajak = Column(Float(53), default=0)
    opsen_bunga = Column(Float(53), default=0)
    opsen_setoran = Column(Float(53), default=0)
    opsen_terhutang = Column(Float(53), default=0)
    pajak_yg_hrs_dibayar = Column(BigInteger, nullable=False, default=0)
    status_pembayaran = Column(Integer, nullable=False, default=0)
    notes = Column(String(255))
    unit_id = Column(BigInteger)
    enabled = Column(SmallInteger)
    terimanip = Column(String(50))
    terimatgl = Column(DateTime, nullable=False, index=True)
    kirimtgl = Column(DateTime)
    isprint_dc = Column(Boolean, default=False)
    rek_no_paneng = Column(String(50))
    sptno_lengkap = Column(String(20))
    sptno_lama = Column(Integer)
    masa_bln = Column(Integer)
    masa_thn = Column(Integer)
    setorankd = Column(Integer)
    setorannm = Column(String(30))
    status_dok = Column(Integer, default=0)
    file1 = Column(String(250))
    file2 = Column(String(250))
    file3 = Column(String(250))
    file4 = Column(String(250))
    file5 = Column(String(250))
    tp_id = Column(Integer)
    spt_no = Column(String(64))
    m_tonase = Column(Float, default=0, nullable=False)
    m_njop = Column(Float, default=0, nullable=False)
    m_tonase_sdh_byr = Column(Float, default=0, nullable=False)
    m_karcis_sdh_byr = Column(Float, default=0, nullable=False)
    m_dasar_sdh_byr = Column(Float, default=0, nullable=False)

    meteran_awal = Column(Integer, default=0, nullable=False)
    meteran_akhir = Column(Integer, default=0, nullable=False)

    air_manfaat_id = Column(Integer, default=0, nullable=False)
    air_zona_id = Column(Integer, default=0, nullable=False)
    air_jenis_sumber_id = Column(Integer, default=0, nullable=False)
    air_kualitas_id = Column(Integer, default=0, nullable=False)

    abt_tarif = Column(Float, default=0, nullable=False)
    abt_calculated = Column(Float, default=0, nullable=False)

    volume = Column(Integer, default=0, nullable=False)

    r_panjang = Column(Float)
    r_lebar = Column(Float)
    r_muka = Column(Float)
    r_banyak = Column(Float)
    r_luas = Column(Float)
    r_tarifid = Column(Integer)
    r_kontrak = Column(Float)
    r_lama = Column(Integer)
    r_jalan_id = Column(Integer)
    r_jalanklas_id = Column(Integer)
    r_lokasi = Column(String(250))
    r_judul = Column(String(200))
    r_kelurahan_id = Column(Integer)
    r_lokasi_id = Column(Integer)
    r_calculated = Column(Float)

    r_nsr_id = Column(Integer)
    r_nsr = Column(Float)
    r_nsrno = Column(String(30))
    r_nsrtgl = Column(DateTime)

    r_nsl_kecamatan_id = Column(Integer)
    r_nsl_type_id = Column(Integer)
    r_nsl_nilai = Column(Float)

    r_lokasi_pasang_id = Column(Integer)
    r_lokasi_pasang_val = Column(Float)
    r_jalanklas_val = Column(Float)
    r_sudut_pandang_id = Column(Integer)
    r_sudut_pandang_val = Column(Float)
    r_tinggi = Column(Float)
    r_njop = Column(Float)
    r_status = Column(String(20))
    r_njop_ketinggian = Column(Float)
    r_nama = Column(String(100))
    r_alamat = Column(String(255))
    r_iprno = Column(String(30))
    r_iprtgl = Column(DateTime)
    r_iprid = Column(Integer)
    r_kecamatan_id = Column(Integer)
    r_jenis_produk_reklame_id = Column(Integer)
    r_letak_reklame_id = Column(Integer)

    wp = relationship(
        'PjdlWp', primaryjoin='PjdlInvoice.customer_id == PjdlWp.id', backref='pad_spts')
    op = relationship(
        'PjdlOp', primaryjoin='PjdlInvoice.customer_usaha_id == PjdlOp.id', backref='pad_spts')
    pajak = relationship(
        'PjdlPajak', primaryjoin='PjdlInvoice.pajak_id == PjdlPajak.id', backref='pad_spts')
    rekening = relationship(
        'PjdlRekening', primaryjoin='PjdlInvoice.rekening_id == PjdlRekening.id', backref='pad_spts')
    spt_typ = relationship(
        'PjdlSptType', primaryjoin='PjdlInvoice.type_id == PjdlSptType.id', backref='pad_spts')
    r_bayarid = Column(SmallInteger)
    # tp = relationship('Tp', primaryjoin='PjdlInvoice.tp_id == Tp.id', backref='pad_spts')
    # sptpd_id = Column(BigInteger)
    # sptpd_id = Column(ForeignKey(PjdlInvoicepd.id, onupdate='CASCADE'))
    # at_titik = Column(Integer)
    # at_npa_normal = Column(Integer)
    # at_npa_kelebihan = Column(Integer)
    # at_npa_total     = Column(Integer)
    # at_tanggal_approval = Column(Date)
    # at_status_approval = Column(Integer)
    # at_pajak_normal = Column(Integer)
    # at_pajak_kelebihan = Column(Integer)
    # sptpd = relationship('PjdlInvoicepd', primaryjoin='PjdlInvoice.sptpd_id == PjdlInvoicepd.id', backref='pad_spts')
    # status_penelitian = Column(SmallInteger, default=0)
    # hasil_penelitian = Column(String(20))
    # r_nsr_id = Column(Integer)
    # r_lokasi_pasang_id = Column(Integer)
    # r_lokasi_pasang_val = Column(Numeric)
    # r_jalanklas_val = Column(Numeric)
    # r_sudut_pandang_id = Column(Integer)
    # r_sudut_pandang_val = Column(Numeric)
    # r_tinggi = Column(Numeric)
    # r_njop = Column(Numeric)
    # r_status = Column(String(20))
    # r_njop_ketinggian = Column(Numeric)
    # r_nama = Column(String(100))
    # r_alamat = Column(String(255))
    # r_jenis_produk_reklame_id = Column(Integer)
    # r_status_reklame_id = Column(Integer)
    # r_lama_reklame = Column(Integer)
    # r_nilai_kontrak = Column(Integer)
    # air_manfaat_id = Column(Integer)
    # air_zona_id = Column(Integer)
    # meteran_awal = Column(Integer, default=0)
    # meteran_akhir = Column(Integer, default=0)
    # volume = Column(Integer, default=0)
    # satuan = Column(String)

    @classmethod
    def get_sptno(cls):
        # strid = cast(cls.sptno, String)
        # strid = func.lpad(strid, 7, '0')

        # hasil = func.concat(cls.tahun, '-')
        # hasil = func.concat(hasil, strid)

        # return hasil
        sptno = func.get_sptno(cls.id)
        return sptno

    @classmethod
    def get_by_sptno(cls, no=None):
        result = DBSession.query(cls.id).\
            filter(func.concat(cls.tahun, func.concat('-',
                   func.lpad(cast(cls.id, String), 7, '0'))) == no)
        return result

    @classmethod
    def get_draft_no(cls, tahun=None):
        if not tahun:
            tahun = datetime.now().year
        result = DBSession.query(func.min(cls.sptno)).\
            filter_by(tahun=tahun).scalar()
        result = result and result < 0 and result-1 or -1
        return result

    @classmethod
    def get_spt_no(cls, row=None):
        if not row:
            tahun = datetime.now().year
        else:
            tahun = row.tahun
        result = DBSession.query(func.max(cls.sptno)).\
            filter_by(tahun=tahun).scalar()
        result = result and result > 0 and result+1 or 1
        return result

    @classmethod
    def query_setoran(cls, customer_id=None, customer_usaha_id=None,
                      pajak_id=None, masa_bln=None,
                      masa_thn=None, pjdl_invoice_type=None):
        qry = DBSession.query(
            func.sum(cls.pajak_terhutang).label('pajak_terhutang'))
        qry = qry.filter(cls.status_pembayaran == 1)
        if customer_id:
            qry = qry.filter(cls.customer_id == customer_id)
        if customer_usaha_id:
            qry = qry.filter(cls.customer_usaha_id == customer_usaha_id)
        if pajak_id:
            qry = qry.filter(cls.pajak_id == pajak_id)
        if masa_bln:
            qry = qry.filter(cls.masa_bln == masa_bln)
        if masa_thn:
            qry = qry.filter(cls.masa_thn == masa_thn)
        if pjdl_invoice_type:
            qry = qry.filter(cls.type_id == pjdl_invoice_type)
        return qry

    # @classmethod
    # def cek_sptpd(cls, customer_id=None, customer_usaha_id=None,
    #               pajak_id=None, masa_bln=None,
    #               masa_thn=None, pjdl_invoice_type=None):
    #     qry = DBSession.query(cls)
    #     if customer_id:
    #         qry = qry.filter(cls.customer_id == customer_id)
    #     if customer_usaha_id:
    #         qry = qry.filter(cls.customer_usaha_id == customer_usaha_id)
    #     if pajak_id:
    #         qry = qry.filter(cls.pajak_id == pajak_id)
    #     if masa_bln:
    #         qry = qry.filter(cls.masa_bln == masa_bln)
    #     if masa_thn:
    #         qry = qry.filter(cls.masa_thn == masa_thn)
    #     if pjdl_invoice_type:
    #         qry = qry.filter(cls.type_id == pjdl_invoice_type)
    #     return qry

    # @classmethod
    # def hit_denda_bulan(self, pajak_terhutang=None, bunga=None, jatuhtempotgl=None):
    #     hit_denda_bulan = DBSession.query(func.hit_denda_bulan(cast(
    #         pajak_terhutang - bunga, BigInteger), 2, func.date(jatuhtempotgl), func.date(datetime.now()))).scalar()
    #     return hit_denda_bulan

    # @classmethod
    # def hit_denda_nilai(self, pajak_terhutang=None, bunga=None, jatuhtempotgl=None):
    #     hit_denda_nilai = DBSession.query(func.hit_denda_nilai(cast(pajak_terhutang - bunga, BigInteger), 2, func.date(
    #         jatuhtempotgl), func.date(datetime.now()))).scalar()
    #     return hit_denda_nilai

    def get_tarif(self):
        if not self.sptno or self.sptno < 0:
            self.terimatgl = datetime.now().date()
            self.tahun = self.terimatgl.year
            # if not self.sptno:
            #     self.sptno = PjdlInvoice.get_draft_no(self.tahun)

        tarif = PjdlPajakTarif.query_from(
            columns=[
                PjdlPajakTarif.tarif,
                PjdlPajakTarif.opsen_tarif,
                PjdlPajakTarif.minomset,
                PjdlPajakTarif.reklame,
                PjdlPajak.jatuhtempo,
                PjdlPajak.multiple,
                PjdlPajak.masapajak,
                PjdlPajak.rekening_id]).filter(
            PjdlPajakTarif.pajak_id == self.pajak_id,
            PjdlPajakTarif.tmt < self.masadari)\
            .outerjoin(PjdlPajak, PjdlPajak.id == PjdlPajakTarif.pajak_id)\
            .order_by(desc(PjdlPajakTarif.tmt)).first()
        self.tarif = tarif and tarif.tarif or 0
        self.opsen_tarif = tarif and tarif.opsen_tarif or 0

    def hitung_akhir(self):
        self.pokok_pajak = self.pokok+self.kenaikan + \
            self.lain2-self.kompensasi-self.setoran
        self.pajak_terhutang = self.pokok_pajak + self.bunga+self.denda
        self.opsen_pokok_pajak = self.opsen_pokok+self.opsen_denda - self.opsen_setoran
        self.opsen_terhutang = self.opsen_pokok_pajak + self.opsen_bunga
        self.pajak_yg_hrs_dibayar = self.pajak_terhutang + self.opsen_terhutang

    def jatuh_tempo_self(self, expired=0, type_id = None):
        masa_sd = self.masasd
        if not type_id:
            type_id=self.type_id

        sspd_type = PjdlSptType.query_id(type_id).first()
        jth_tempo = datetime(masa_sd.year, masa_sd.month,
                             sspd_type.max_day)+relativedelta(months=1)
        if type(self.terimatgl)==datetime:
            self.terimatgl = self.terimatgl.date()
            
        if self.terimatgl > jth_tempo.date():
            self.pokok_pajak = self.pokok+self.kenaikan +\
                self.lain2-self.kompensasi-self.setoran

            self.bunga = hitung_denda(self.pokok_pajak, jth_tempo,
                                      self.terimatgl,
                                      sspd_type.max_denda, sspd_type.persen_denda*100)
            self.opsen_pokok_pajak = self.opsen_pokok+self.opsen_denda -\
                self.opsen_setoran
            self.opsen_bunga = hitung_denda(self.opsen_pokok_pajak, jth_tempo,
                                            self.terimatgl,
                                            sspd_type.max_denda, sspd_type.persen_denda*100)

            # Jatuh tempo disimpan untuk bulan berikutnya setelah terima tanggal
            # todo: ada potensi perubahan data apabila di edit oleh admin
            jth_tempo = datetime(self.terimatgl.year,
                                 self.terimatgl.month, sspd_type.max_day)
            if type(self.terimatgl)==datetime:
                self.terimatgl = self.terimatgl.date()
            if jth_tempo.date() < self.terimatgl:
                months=relativedelta(jth_tempo.date(), self.terimatgl, "months").months+1

                jth_tempo += relativedelta(months=months)
        else:
            if expired:
                jth_tempo2 = self.terimatgl + \
                    relativedelta(days=expired)
                if jth_tempo2 < jth_tempo:
                    jth_tempo = jth_tempo2

        self.jatuhtempotgl = jth_tempo

    def calculate_sspd(self, expired=0):
        self.get_tarif()
        self.dasar = 1/(1+self.opsen_tarif) * \
            self.pajak_yg_hrs_dibayar/self.tarif
        self.jatuh_tempo_self(expired)
        self.calculate_final()

    def calculate_sptpd(self, type_id):
        self.get_tarif()
        self.jatuh_tempo_self(0, type_id)
        self.calculate_final()


    def calculate_final(self):
        # self.get_tarif()
        self.pokok = self.dasar * self.tarif
        opsen_tarif = self.opsen_tarif
        if opsen_tarif:
            opsen_tarif = opsen_tarif
            self.opsen_pokok = opsen_tarif * self.pokok
        self.hitung_akhir()

    def calculate_official(self):
        self.get_tarif()
        sspd_type = PjdlSptType.query_id(self.type_id).first()
        jth_tempo = self.terimatgl + \
            relativedelta(days=sspd_type.max_day)
        self.jatuhtempotgl = jth_tempo
        self.calculate_final()

    def tetapkan(self, kd_pemda, spt_no_tpl):
        self.calculate_official()
        self.sptno = self.get_spt_no()
        no_urut = str(self.sptno).zfill(5)
        no_urut6 = str(self.sptno).zfill(6)
        self.spt_no = spt_no_tpl.format(
            kd_pemda=kd_pemda, tahun=self.tahun,
            no_urut=no_urut, no_urut6=no_urut6)
        self.status_pembayaran = 0


class PjdlInvoiceItem(Base, DefaultModel):
    __tablename__ = 'pad_spt_item'
    __table_args__ = (
        UniqueConstraint('spt_id', 'rekening_id'),
        PAD_TABLE_ARGS
    )
    spt_id = Column(ForeignKey(
        'pad.pad_spt.id', onupdate='CASCADE'))
    rekening_id = Column(ForeignKey(
        'pad.tblrekening.id', onupdate='CASCADE'))
    # type_id = Column(ForeignKey(PjdlSptType.id, onupdate='CASCADE'))
    # so = Column(String(1), nullable=False, index=True)
    # masadari = Column(DateTime, nullable=False)
    # masasd = Column(DateTime, nullable=False)
    # jatuhtempotgl = Column(DateTime)
    # r_bayarid = Column(SmallInteger)
    # minomset = Column(Float(53), default=0)
    dasar = Column(Float(53), nullable=False, default=0)
    tarif = Column(Float(53), nullable=False, default=0)
    denda = Column(Float(53), default=0)
    bunga = Column(Float(53), nullable=False, default=0)
    setoran = Column(Float(53), default=0)
    kenaikan = Column(Float(53), nullable=False, default=0)
    kompensasi = Column(Float(53), default=0)
    lain2 = Column(Float(53), nullable=False, default=0)
    pajak_terhutang = Column(BigInteger, nullable=False,
                             default=0)
    # air_manfaat_id = Column(Integer)
    # air_zona_id = Column(Integer)
    # meteran_awal = Column(Integer, default=0)
    # meteran_akhir = Column(Integer, default=0)
    # volume = Column(Integer, default=0)
    # satuan = Column(String)
    # r_panjang = Column(Float(53))
    # r_lebar = Column(Float(53))
    # r_muka = Column(Float(53))
    # r_banyak = Column(Float(53))
    # r_luas = Column(Float(53))
    # r_tarifid = Column(SmallInteger)
    # r_kontrak = Column(Float(53))
    # r_lama = Column(Integer)
    # r_jalan_id = Column(Integer)
    # r_jalanklas_id = Column(Integer)
    # r_lokasi = Column(String(250))
    # r_judul = Column(String(200))
    # r_kelurahan_id = Column(Integer)
    # r_lokasi_id = Column(Integer)
    # r_calculated = Column(Float(53))
    # r_nsr = Column(Numeric)
    # r_nsrno = Column(String(30))
    # r_nsrtgl = Column(DateTime)
    # r_nsl_kecamatan_id = Column(Integer)
    # r_nsl_type_id = Column(Integer)
    # r_nsl_nilai = Column(Float(53))
    # notes = Column(String(255))
    # unit_id = Column(BigInteger)
    # enabled = Column(SmallInteger)
    # create_date = Column(DateTime)
    # create_uid = Column(Integer)
    # write_date = Column(DateTime)
    # write_uid = Column(Integer)
    # terimanip = Column(String(50))
    # terimatgl = Column(DateTime, nullable=False, index=True)
    # kirimtgl = Column(DateTime)
    # isprint_dc = Column(SmallInteger, default=0)
    # r_nsr_id = Column(Integer)
    # r_lokasi_pasang_id = Column(Integer)
    # r_lokasi_pasang_val = Column(Numeric)
    # r_jalanklas_val = Column(Numeric)
    # r_sudut_pandang_id = Column(Integer)
    # r_sudut_pandang_val = Column(Numeric)
    # r_tinggi = Column(Numeric)
    # r_njop = Column(Numeric)
    # r_status = Column(String(20))
    # r_njop_ketinggian = Column(Numeric)
    # status_pembayaran = Column(Integer, nullable=False, default=0)
    # rek_no_paneng = Column(String(50))
    # sptno_lengkap = Column(String(20))
    # sptno_lama = Column(Integer)
    # r_nama = Column(String(100))
    # r_alamat = Column(String(255))
    # masa_bln = Column(Integer)
    # masa_thn = Column(Integer)
    # setorankd = Column(Integer)
    # setorannm = Column(String(30))
    # status_dok = Column(Integer, default=0)
    # file1 = Column(String(250))
    # file2 = Column(String(250))
    # file3 = Column(String(250))
    # file4 = Column(String(250))
    # file5 = Column(String(250))
    # dasar_yg_sdh_dibayar = Column(BigInteger)
    # dasar_yg_blm_dibayar = Column(BigInteger)
    # status_penelitian = Column(SmallInteger, default=0)
    # hasil_penelitian = Column(String(20))
    # r_jenis_produk_reklame_id = Column(Integer)
    # r_status_reklame_id = Column(Integer)
    # r_letak_reklame_id = Column(Integer)
    # tp_id = Column(Integer)

    # r_jenis_produk_reklame_id = Column(Integer)
    # r_judul = Column(String(200))
    # r_lama_reklame = Column(Integer)
    # r_nsr = Column(Integer)
    # r_nilai_kontrak = Column(Integer)

    # at_titik = Column(Integer)
    # at_npa_normal = Column(Integer)
    # at_npa_kelebihan = Column(Integer)
    # at_npa_total     = Column(Integer)
    # at_tanggal_approval = Column(Date)
    # at_status_approval = Column(Integer)
    # at_pajak_normal = Column(Integer)
    # at_pajak_kelebihan = Column(Integer)