pbb_daemon.py

# PdfProses, sppt_ttd, send_email, parsing
"""
Digunakan untuk SIMPBB
1. "massal" Memproses SPPT Massal LogEspptMassal -> LogEsppt
    def sppt_massal()
    def get_sppt_massal() -> Check SPPT Massal Found
    if status=1 def generate_partner_nop 
    else generate_all()

2. "pdf" Proses LogSppt(status=0) LogSppt(status=1)
    sppt2pdf()
    if sppt_id then create_pdf
    else proses_sppt_log
         create_pdf
    jasper2pdf
    
3. "ttd" Proses LogSppt(status=1) LogSppt(status=2)

"""
from .. import SPPT_CLASS
from pyramid_mailer.message import Message
from tangsel.pbb.esppt.views.gen_sppt import (
    get_template,
    get_sppt_file_name)
from tangsel.pbb.esppt.tools import dMy, dmy, jasper_export, number_only, terbilang, thousand
from tangsel.pbb.esppt.models import LogEsppt, Partner, PartnerNop, EspptUnduh
from .demon import (make_pid,)
from tangsel.base import get_params
from sqlalchemy import engine_from_config, or_
from pyramid.paster import (get_appsettings, setup_logging, bootstrap, )
from . import FixNop
from tangsel.base.models import (Base, DBSession,)
# import qrcode
from datetime import date, datetime
import math
import qrcode
import json
import locale
import logging
import traceback
import base64
# import locale
from datetime import datetime
# import logging
from optparse import OptionParser
import os
import signal
import sys
from time import sleep
import requests
from sqlalchemy import or_
import transaction
from tangsel.pbb.esppt.models.es_log import LogEspptMassal
# from tangsel.pbb.esppt.views.gen_sppt import (get_bsre)
# from tangsel.pbb.esppt import MODULE_CLASS, get_params
from .. import SPPT_CLASS
from tangsel.pbb.esppt.models import DBSession, LogEsppt, Partner, PartnerNop
from .demon import (isLive, make_pid,)
from pyramid_mailer import mailer_factory_from_settings
from pyramid_mailer.message import Attachment
import ssl
NO_DATA = "Tidak ada data yang diproses"
log = logging.getLogger(__name__)


class PdfProses(object):
    def __init__(self, config, option):
        self.config = config
        self.option = option
        self.db_session = DBSession
        log_dir = option.log_dir
        if not os.path.exists(log_dir):
            os.makedirs(log_dir)
        self.force = option.force
        self.pbb_dbsession = None
        self.pbb_base = None
        self.sppt = None
        self.posisi_pegawai = None
        self.dat_objek_pajak = None
        self.sppt_op_bersama = None
        self.jenis_sektor = None
        self.kelurahan = None
        self.kecamatan = None
        self.dati2 = None
        self.kanwil = None
        self.kantor = None
        self.tempat_pembayaran = None
        self.kode_map = None
        self.tarif = None
        self.jabatan = None
        self.pegawai = None

    def set_engine(self):
        global running
        global log
        global settings
        global mailer
        log.debug("def set_engine")
        config_uri = self.config
        setup_logging(config_uri)
        settings = get_appsettings(config_uri)
        bootstrap(config_uri)
        if 'localization' not in settings:
            settings['localization'] = 'id_ID.UTF-8'

        locale.setlocale(locale.LC_ALL, settings['localization'])

        engine = engine_from_config(settings, 'sqlalchemy.')
        Base.metadata.bind = engine
        DBSession.configure(bind=engine)
        SPPT_CLASS.init()
        if not SPPT_CLASS.mirror_url:
            pbb_engine = engine_from_config(settings, 'pbb.')
            self.pbb_base.metadata.bind = pbb_engine
            self.pbb_dbsession.configure(bind=pbb_engine)

    def get_log_sppt_massal(self):
        log.debug("def get_log_sppt_massal")

        return LogEspptMassal.query().filter(or_(LogEspptMassal.status == None,
                                                 LogEspptMassal.status == 0)).first()

    def sppt_massal(self):
        """
        Digunakan untuk generate data pada tabel "log_sppt_massal"
        Status=1 maka akan generate data kedalam "log_sppt" berdasarkan
        data pada tabel "partner_nop"
        """
        log.debug("def sppt_massal")
        pid_file = os.path.realpath("esppt-massal.pid")
        log_dir = os.path.realpath(self.option.log_dir)
        os.path.join(log_dir, 'sppt-massal.log')
        if self.option.stop:
            print('Stop Daemon SPPT Massal')
            stop_daemon(pid_file)
            sys.exit()

        row = self.get_log_sppt_massal()
        if not row:
            log.info(f"Massal: {NO_DATA}")
            sys.exit()

        esppt = DBSession.query(LogEsppt).filter(LogEsppt.tahun == row.tahun)
        row.jml_proses = esppt.count()

        log.info("Start Proses Massal")
        make_pid(pid_file)
        log.debug("Data to Proses %s", str(row.to_dict()))
        if int(row.jenis) == 1:
            self.generate_from_partnernop(row)
        else:
            self.generate_from_pbb(row)

        os.remove(pid_file)

    def generate_from_partnernop(self, row):
        """
        Penetapan massal berdasarkan data wp yang terdaftar pada `partner_nop`

        Hasil proses disimpan pada tabel "log_esppt"
        Baca table partner_nop
        Insert ke log_sppt
        """
        log.debug("def generate_from_partnernop")

        partner_nop = DBSession.query(
            PartnerNop.nop.label(
                'nop'), PartnerNop.partner_id.label('partner_id'),
            Partner.email.label('partner_email')
        ). \
            join(Partner, Partner.id == PartnerNop.partner_id).\
            filter(PartnerNop.status == 1,
                   # func.coalesce(PartnerNop.thn_akhir,0) != row.tahun
                   ).order_by(PartnerNop.id).limit(self.option.limit).all()
        if not partner_nop:
            row.status = 1
        n_proses = 0
        if partner_nop:
            for r in partner_nop:
                fix_nop = FixNop(r.nop)
                esppt = LogEsppt.query_nop(r.nop).filter_by(tahun=row.tahun,
                                                            partner_id=r.partner_id).first()
                values = fix_nop.to_dict()
                values["tahun"] = row.tahun
                values["siklus_sppt"] = 0
                values["status"] = 0
                values["destination"] = getattr(
                    r, 'partner_email', '') and r.partner_email or ""
                values["partner_id"] = r.partner_id
                esppt = self.save_log_sppt(values, esppt)
                n_proses += 1
            self.db_session.flush()
        row.status = 3
        row.jml_proses = row.jml_proses and row.jml_proses + n_proses or 0
        self.db_session.add(row)
        self.db_session.flush()
        transaction.commit()

    def generate_from_pbb(self, row):
        """Fungsi untuk mengambil data NOP dari SIMPBB.SPPT ke table log_esppt
        (LogSppt)
        Args:
            row (_type_): _description_
        """
        log.debug("def generate_from_pbb")
        tahun = row.tahun
        sppts = self.pbb_dbsession.query(self.sppt.kd_propinsi,
                                         self.sppt.kd_dati2,
                                         self.sppt.kd_kecamatan,
                                         self.sppt.kd_kelurahan,
                                         self.sppt.kd_blok,
                                         self.sppt.no_urut,
                                         self.sppt.kd_jns_op,
                                         self.sppt.siklus_sppt,
                                         self.sppt.nm_wp_sppt,
                                         self.sppt.pbb_yg_harus_dibayar_sppt,
                                         )\
            .filter(self.sppt.thn_pajak_sppt == row.tahun)\
            .order_by(self.sppt.kd_propinsi,
                      self.sppt.kd_dati2,
                      self.sppt.kd_kecamatan,
                      self.sppt.kd_kelurahan,
                      self.sppt.kd_blok,
                      self.sppt.no_urut,
                      self.sppt.kd_jns_op)
        jml_proses = row.jml_proses
        jml_sppt = sppts.count()
        row = self.save_log_sppt_massal({"jml_sppt": jml_sppt}, row)
        if jml_sppt < jml_proses:
            log.info("Semua Data Sudah diproses ")
            return

        page_size = 1000.0
        if page_size < float(self.option.limit):
            page_size = float(self.option.limit)

        count = math.ceil((jml_sppt-jml_proses)/page_size)
        start = datetime.now()

        for p in range(count):
            row_sppts = sppts.limit(page_size).offset(p*page_size+jml_proses)
            for sppt in row_sppts:
                d = dict(sppt._mapping)
                d["tahun"] = tahun
                self.save_log_sppt(d)
            self.db_session.flush()
            transaction.commit()
            lama = datetime.now() - start
            log.info(
                f"Page: {p}, second: {lama} remain: {(lama/(p+1)) * count} second")

    def get_ttd(self, sppt):
        log.debug("def get_ttd")
        posisi = self.get_posisi(sppt.get("tgl_cetak_sppt"))
        jabatan = self.jabatan.query().filter_by(kd_jabatan="10").first()
        jabatan = jabatan and jabatan.nm_jabatan or " "
        pegawai = self.pegawai.query().filter_by(nip=posisi["nip"]).first()
        nama = pegawai and pegawai.nm_pegawai or " "
        nip = pegawai and pegawai.nip or " "
        return {"nama_terang": nama, "nip": nip, "jabatan": jabatan}

    def get_tarif(self, kd_propinsi, kd_dati2, njop, tahun):
        log.debug("def get_tarif")
        tarif = self.tarif.query()\
            .filter_by(kd_propinsi=kd_propinsi,
                       kd_dati2=kd_dati2)\
            .filter(self.tarif.thn_awal <= tahun,
                    self.tarif.thn_akhir >= tahun,
                    self.tarif.njop_min <= njop,
                    self.tarif.njop_max >= njop
                    ).first()
        if tarif:
            return tarif.nilai_tarif
        return 0

    def get_posisi(self, tgl_cetak, page=None):
        log.debug("def get_posisi")
        posisi = self.posisi_pegawai.query().filter_by(kd_jabatan="10")\
            .filter(self.posisi_pegawai.tgl_awal_berlaku <= tgl_cetak,
                    self.posisi_pegawai.tgl_akhir_berlaku >= tgl_cetak
                    ).first()
        if not posisi:
            return {}
        return posisi.to_dict()

    def get_data(self, sppt):
        log.debug("def get_data")
        data = {}
        nops = FixNop(sppt)
        nops.from_dict
        data["kd_rek"] = sppt.get("no_rek_tp", " ")
        data["tahun"] = sppt.get("thn_pajak_sppt", " ")
        data["nop"] = nops.get_formatted()
        data["jalan_op"] = sppt.get("jalan_op", " ")
        data["blok_kav_op"] = sppt.get("blok_kav_no_op", "-")
        data["rtrw_op"] = f"RT: {sppt.get('rt_op', '000')} RW: {sppt.get('rw_op', '00')}"
        data["nm_wp"] = sppt.get("nm_wp_sppt", " ")
        data["jalan_wp"] = sppt.get("jln_wp_sppt", " ")
        data["blok_kav_wp"] = sppt.get("blok_kav_no_wp_sppt", " ")
        data["rtrw_wp"] = f"RT: {sppt.get('rt_wp_sppt', '000')} RW: {sppt.get('rw_wp_sppt', '00')}"
        data["kelurahan_wp"] = sppt.get("kelurahan_wp_sppt", " ")
        data["kota_wp"] = sppt.get("kota_wp_sppt", " ")
        data["npwp"] = sppt.get("npwp_sppt", " ")
        data["bumi"] = 'Bumi'
        data["bumi_luas"] = thousand(int(sppt.get("luas_bumi_sppt", 0)))
        data["bumi_kls"] = sppt.get("kd_kls_tanah", "XXX")
        data["bumi_njop_m"] = thousand(int(sppt.get("njop_bumi_sppt", 0)) /
                                       (int(sppt.get("luas_bumi_sppt") or 1)))
        data["bumi_njop"] = thousand(int(sppt.get("njop_bumi_sppt", 0)))
        data["bng"] = 'Bangunan'
        data["bng_luas"] = thousand(int(sppt.get("luas_bng_sppt", 0)))
        data["bng_kls"] = sppt.get("kd_kls_bng", 'XXX')
        data["bng_njop_m"] = thousand(int(sppt.get("njop_bng_sppt", 0)) /
                                      (int(sppt.get("luas_bng_sppt", 0) or 1)))
        data["bng_njop"] = thousand(int(sppt.get("njop_bng_sppt", 0)))
        luas_bumi_beban_sppt = sppt.get("luas_bumi_beban_sppt", 0)
        if luas_bumi_beban_sppt:
            data["bumis"] = 'Bumi*'
            data["bumis_luas"] = thousand(luas_bumi_beban_sppt)
            data["bumis_kls"] = sppt.get("kd_kls_tanah_ber", '')
            data["bumis_njop_m"] = thousand(
                int(sppt.get("njop_bumi_beban_sppt", 0)) /
                (int(sppt.get("luas_bumi_beban_sppt") or 1)))
            data["bumis_njop"] = thousand(
                int(sppt.get("njop_bumi_beban_sppt", 0)))
        else:
            data["bumis"] = " "
            data["bumis_luas"] = " "
            data["bumis_kls"] = " "
            data["bumis_njop_m"] = " "
            data["bumis_njop"] = " "
        luas_bng_beban_sppt = sppt.get("luas_bng_beban_sppt", 0)
        if luas_bng_beban_sppt:
            data["bngs"] = 'Bangunan*'
            data["bngs_luas"] = thousand(int(luas_bng_beban_sppt))
            data["bngs_kls"] = sppt.get("kd_kls_bng_ber", '')
            data["bngs_njop_m"] = thousand(int(sppt.get("njop_bng_beban_sppt", 0)) /
                                           int(luas_bng_beban_sppt))
            data["bngs_njop"] = sppt.get("njop_bng_beban_sppt", '')
        else:
            data["bngs"] = ''
            data["bngs_luas"] = ''
            data["bngs_kls"] = " "
            data["bngs_njop_m"] = " "
            data["bngs_njop"] = " "

        data["njop"] = thousand(int(sppt.get("njop_sppt", 0)))
        data["njoptkp"] = thousand(int(sppt.get("njoptkp_sppt", 0)))
        njop_pbb = int(sppt.get("njop_sppt", 0)) - \
            int(sppt.get("njoptkp_sppt", 0))
        if not njop_pbb:
            njop_pbb = 1
        data["njop_pbb"] = thousand(njop_pbb >= 0 and njop_pbb or 0)
        data["tarif"] = self.get_tarif(sppt.get("kd_propinsi"),
                                       sppt.get("kd_dati2"),
                                       sppt.get("njop_sppt"),
                                       sppt.get("thn_pajak_sppt"),)
        data["pbb_terutang"] = thousand(
            int(sppt.get("pbb_terhutang_sppt", 0)))
        data["str_faktor_pengurang"] = sppt.get(
            "faktor_pengurang_sppt", 0) and 'Faktor Pengurang' or ''
        data["faktor_pengurang"] = sppt.get("faktor_pengurang_sppt", 0) \
            and thousand(int(sppt.get("faktor_pengurang_sppt", 0))) or " "
        data["terhutang"] = thousand(
            int(sppt.get("pbb_yg_harus_dibayar_sppt", 0)))
        data["terbilang"] = str(terbilang(
            sppt.get("pbb_yg_harus_dibayar_sppt"))).upper() + ' RUPIAH'
        data["jatuh_tempo"] = dmy(sppt.get("tgl_jatuh_tempo_sppt"))
        data["jatuh_tempo_dMy"] = dMy(sppt.get("tgl_jatuh_tempo_sppt"))
        data["tempat_bayar"] = get_template('tpl/tp.txt')

        data["kecamatan_op"] = sppt.get("nm_kecamatan", " ")
        data["kelurahan_op"] = sppt.get("nm_kelurahan", " ")
        data["sppt_tahun"] = str(sppt.get("thn_pajak_sppt")) + ' / ' + \
            str(thousand(int(sppt.get("pbb_terhutang_sppt")) /
                         njop_pbb * 100, 2))
        ttd = self.get_ttd(sppt)
        data.update(ttd)
        data["kota"] = sppt.get('kota_terbit', " ")
        data["kota_op"] = sppt.get("nm_dati2", '')
        data["nm_sektor"] = sppt.get("nm_sektor")
        data["kd_map"] = sppt.get("kd_map")
        # document.kode_sppt = kode_sppt or ''
        data["nm_tp"] = sppt.get("nm_tp", " ")
        # data["kepala"] = "jabatan"
        # data["nip"] = "nip"
        # data["nama_terang"] = "nama"
        data["tgl_cetak"] = dmy(sppt.get("tgl_cetak_sppt"))
        data["tgl_cetak_dMy"] = dMy(sppt.get("tgl_cetak_sppt"))
        data["background"] = sppt.get("bg_file")
        data["codeqr"] = sppt.get("img_file")
        data["njkp_sppt"] = sppt.get('njkp_sppt', 0)
        data["tarif_pct"] = sppt.get('tarif_pct', 0)
        data["tarif_pct_perc"] = str(
            (sppt.get('tarif_pct', 0) or 0) * 100) + ' %'
        data["njkp_pct"] = sppt.get('njkp_pct', 0)
        data["njkp_pct_perc"] = str(
            (sppt.get('njkp_pct', 0) or 0) * 100) + ' %'
        data["piutangtotal"] = 0
        data["tglsekarang"] = date.today().strftime('%d-%m-%Y')
        # for p in get_piutang(sppt):
        #     piutang = Item()
        #     piutang.tahun = p['thn_pajak_sppt'] or ''
        #     piutang.pokok = p['pbb_yg_harus_dibayar_sppt'] and thousand(
        #         int(p['pbb_yg_harus_dibayar_sppt'])) or 0
        #     piutang.denda = p['potensi_denda'] and thousand(
        #         int(p['potensi_denda'])) or 0
        #     piutang.jumlah = thousand((p['pbb_yg_harus_dibayar_sppt'] and int(p['pbb_yg_harus_dibayar_sppt']) or 0)
        #                               + (p['potensi_denda'] and int(p['potensi_denda']) or 0))
        #     piutangs.append(piutang)
        #     document.piutangtotal += ((p['pbb_yg_harus_dibayar_sppt'] and int(p['pbb_yg_harus_dibayar_sppt']) or 0)
        #                               + (p['potensi_denda'] and int(p['potensi_denda']) or 0))
        # sppt["piutangtotal"] = 0  # thousand(document.piutangtotal)
        return data

    def jasper2pdf(self, path, filename, sppt):
        log.debug("def jasper2pdf")
        tahun = sppt.get("thn_pajak_sppt", " ")
        tpl_file = os.path.join(SPPT_CLASS.files_tpl, f'{tahun}.jrxml')
        bg_file = os.path.join(SPPT_CLASS.files_tpl, f'{tahun}.jpg')
        if not os.path.exists(tpl_file):
            tpl_file = os.path.join(SPPT_CLASS.files_tpl, f'sppt.jrxml')
            bg_file = os.path.join(SPPT_CLASS.files_tpl, f'sppt.jpg')
            if not os.path.exists(tpl_file):
                raise Exception(f"Template {tpl_file} not found")
        tpl_file = tpl_file  # .replace('\\', '/')
        bg_file = bg_file  # .replace('\\', '/')
        path = path  # .replace("\\", '/')

        sppt["bg_file"] = bg_file
        params = self.get_data(sppt)

        file_name = jasper_export(
            tpl_file, path, parameters=params, output_formats=['pdf'], use_db=False,
            out_file=filename)[0]

        jasper_file = os.path.join(
            path, os.path.splitext(file_name)[0]+".jasper")
        if os.path.exists(jasper_file):
            os.remove(jasper_file)

        return file_name

    def save_log_sppt_massal(self, values, row):
        log.debug("def save_log_sppt_massal")

        row.jml_sppt = values["jml_sppt"]
        DBSession.add(row)
        DBSession.flush()
        transaction.commit()
        return row

    def save_log_sppt(self, values, row=None):
        log.debug("def save_log_sppt")
        if not row:
            row = LogEsppt()
            row.created = datetime.now()
            row.status = 0
            row.from_dict(values)
        row.file_name = values.get(
            "file_name", row.file_name)
        row.siklus_sppt = values.get("siklus_sppt")
        row.nm_wp_sppt = values.get("nm_wp_sppt")
        row.pbb_yg_harus_dibayar_sppt = values.get(
            "pbb_yg_harus_dibayar_sppt", 0)
        row.status = values.get("status", row.status)
        row.partner_id = values.get("partner_id", row.partner_id)
        self.db_session.add(row)
        return row

    def get_sppt(self, nop, tahun):
        raise Exception("Overide please")

    def create_pdf(self, nop, tahun, sppt=None):
        log.debug("def create_pdf")
        url = SPPT_CLASS.url
        log.debug("url: %s ", url)
        log.debug("nop: %s ", nop)
        log.debug("tahun: %s ", tahun)
        if not sppt:
            sppt = self.get_sppt(nop, tahun)

        log.debug("sppt: %s ", sppt)
        # nops = FixNop(nop)
        if not sppt:
            log.error(f"SPPT Not Found: {nop} {tahun}")
            return

        sppt = dict(sppt._mapping)
        file_name = get_sppt_file_name(nop, tahun, sppt.get("siklus_sppt"),
                                       sppt.get("nm_wp_sppt", " ").strip())

        save_folder = SPPT_CLASS.files_draft+tahun
        file_name_path = os.path.join(save_folder, file_name)
        pdf_file = file_name_path + '.pdf'
        img_file = file_name_path + '.png'
        log.info(f"Proses PDF File {pdf_file}")
        if not os.path.exists(save_folder):
            os.makedirs(save_folder)

        if os.path.isfile(pdf_file):
            if not self.force:
                log.error(f"File Found: {pdf_file}")
                return sppt
            os.remove(pdf_file)

        qr_text = '/'.join([url, tahun, file_name+'.pdf'])
        img = qrcode.make(str(qr_text))
        img.save(img_file, 'PNG')
        sppt["img_file"] = img_file  # .replace("\\", "/")

        sppt_file = self.jasper2pdf(
            path=save_folder, filename=file_name, sppt=sppt)

        # direplace untuk http
        sppt_file = os.path.join(
            sppt["thn_pajak_sppt"], os.path.basename(sppt_file))
        # \.replace("\\", "/")

        log.debug(f'SPPTFile: {sppt_file}')
        sppt["file_name"] = sppt_file

        if os.path.exists(img_file):
            os.remove(img_file)
        return sppt

    def proses_sppt_log(self):
        """
        Digunakan untuk prosessing data pada tabel log_sppt LogEsppt menjadi PDF
        Data yang di proses yang status=0
        Output adalah status 1 dengan nama file pdf
        """
        log.debug("def proses_sppt_log")
        start = datetime.now()
        url = SPPT_CLASS.url
        qry = LogEsppt.query().filter(LogEsppt.status == 0)
        limit = int(self.option.limit)
        row_count = qry.limit(limit).count()
        if not row_count:
            log.info(f"SPPT LOG: {NO_DATA}")
            return

        page_size = 100
        if limit < page_size:
            page_size = limit

        page_count = math.ceil(row_count/page_size)

        for page in range(page_count):
            for row in qry.limit(page_size):
                resp = self.create_pdf(nop=row.nop, tahun=row.tahun)
                log.debug("Response From: %s", resp)
                if resp:
                    resp["status"] = 1

                    # row.file_name = resp.get("file_name", row.file_name)
                    # row.status = 1
                    # row.siklus_sppt = resp["siklus_sppt"]
                    # row.nm_wp_sppt = resp["nm_wp_sppt"]
                    # row.pbb_yg_harus_dibayar_sppt = resp["pbb_yg_harus_dibayar_sppt"]
                    partner_nop = PartnerNop.query().filter_by(nop=row.nop_raw).first()
                    if partner_nop:
                        setattr(partner_nop, 'thn_akhir', row.tahun)
                        self.db_session.add(partner_nop)
                        resp["partner_id"] = row.partner_id
                    # row.partner_id = partner_nop and partner_nop.partner_id or None
                else:
                    log.error("Error Create PDF: %s", row.nop)
                    resp["status"] = 2
                self.save_log_sppt(resp, row)
                # DBSession.add(row)
            self.db_session.flush()
            transaction.commit()
            log.info(
                f"Halaman: {page+1} dari {page_count} Time: {datetime.now()-start}")

    def proses_sppt_single(self):
        log.debug("def proses_sppt_single")
        sppt_id = self.option.sppt_id.strip()
        sppt_id = number_only(sppt_id)
        if len(sppt_id) != 22:
            print("SPPT ID harus 22 digit NOP+Tahun")
            return

        nop = sppt_id[:18]
        tahun = sppt_id[-4:]
        sppt = self.create_pdf(nop, tahun)
        if not sppt:
            log.debug("Error Create PDF : Sppt not found.")
            return

        sppt["nop"] = nop
        sppt["tahun"] = tahun
        sppt["status"] = 1

        log_esppt = LogEsppt.query_nop(nop) \
            .filter(LogEsppt.tahun == tahun,
                    LogEsppt.siklus_sppt == sppt["siklus_sppt"]).first()
        if sppt.get("file_name", None):
            sppt["status"] = 1
            log_esppt = self.save_log_sppt(sppt, log_esppt)
            self.db_session.flush()
            log.info(f"SPPT {sppt_id} created at {sppt['file_name']}")
        transaction.commit()

    def sppt2pdf(self):
        """Fungsi Untuk generate File PDF dari log_esppt (LogSppt)
        Get log_sppt where status = 0 limit n
        Args:
            option (_type_): _description_
        """
        log.debug("def sppt2pdf")
        pid_file = 'osipkd-sppt2pdf.pid'
        log_file = 'osipkd-sppt2pdf.log'
        pid_file = os.path.realpath(pid_file)
        log_dir = os.path.realpath(self.option.log_dir)
        if self.option.stop:
            log.info('Stop Daemon')
            stop_daemon(pid_file)
            sys.exit()

        # os.path.join(log_dir, log_file)
        if self.option.sppt_id:
            self.proses_sppt_single()
        else:
            make_pid(pid_file)
            self.proses_sppt_log()
            os.remove(pid_file)


def sppt_ttd(option):
    log.debug("def sppt_ttd")
    pid_file = os.path.realpath("esppt-ttd.pid")
    log_dir = os.path.realpath(option.log_dir)
    os.path.join(log_dir, 'esppt-ttd.log')
    if option.stop:
        log.info('Stop Daemon TTE SPPT ')
        stop_daemon(pid_file)
        sys.exit()

    log.info("Start Proses TTE SPPT")
    make_pid(pid_file)
    draft_path = SPPT_CLASS.files_draft
    final_path = SPPT_CLASS.files_final
    drafts = LogEsppt.query().filter_by(status=1).limit(option.limit)
    tte_proses = TteProses()
    for f in drafts:
        log.debug('START Convert {}'.format(f.file_name))
        draft_file = os.path.join(
            draft_path, f.file_name)  # .replace("\\", '/')
        final_file = os.path.join(
            final_path, f.file_name)  # .replace("\\", '/')
        tahun = f.tahun
        final_dir = os.path.join(final_path, tahun)
        log.debug('Final Dir {}'.format(final_dir))
        if not os.path.exists(final_dir):
            os.makedirs(final_dir)
        if os.path.exists(final_file):
            os.remove(final_file)

        if SPPT_CLASS.bsre_url:
            try:
                tte_proses.proses(draft_file, tahun)
            except Exception as e:
                log.error(str(e))
                continue
        try:
            os.rename(draft_file, final_file)
        except Exception as e:
            log.error(f"Error Rename File: {draft_file} to {final_file}")
            log.error(str(e))
            continue

        f.status = 2
        DBSession.add(f)
    DBSession.flush()
    transaction.commit()

    os.remove(pid_file)


def usage(argv):
    cmd = os.path.basename(argv[0])
    print('usage: %s <config_uri>\n'
          '(example: "%s development.ini")' % (cmd, cmd))
    sys.exit(1)


def parsing(argv):
    log.debug("def parsing")

    pid_file = 'tangsel-esppt.pid'
    log_dir = 'log_file'
    typ = 'massal'
    pars = OptionParser()
    pars.add_option('-a', '--action', default=typ,
                    help='Type operasi: massal, pdf, ttd, email, default: ' + typ)
    pars.add_option('-p', '--pid-file', default=pid_file,
                    help='default ' + pid_file)
    pars.add_option('-l', '--log-dir', default=log_dir,
                    help='default ' + log_dir)
    pars.add_option('-s', '--sppt-id', default='',
                    help='Nop dan Tahun SPPT')
    pars.add_option('-t', '--typ', default='odt',
                    help='Type Template odt/jasper')
    pars.add_option('-m', '--limit', default=1000,
                    help='Type Template odt/jasper')
    pars.add_option('', '--stop', action='store_true',
                    help='Stop daemon')
    pars.add_option('', '--force', action='store_true',
                    help='Force Replace Existing Files')
    option, remain = pars.parse_args(argv[1:])
    return option, remain

# Daemon Management


def stop_daemon(pid_file):
    pid = isLive(pid_file)
    print(pid, pid_file)
    if not pid:
        return

    print('kill %d by signal' % pid)
    os.kill(pid, signal.SIGTERM)
    i = 0
    while i < 5:
        sleep(1)
        i += 1
        print(i)
        if not isLive(pid_file):
            return
    print('kill %d by force' % pid)
    try:
        os.kill(pid, signal.SIGKILL)
    except OSError as err:
        print(err)


class TteProses(object):
    def __init__(self):
        # bsre_url = user:password@url:img_ttd@nik:passphrase
        if SPPT_CLASS.bsre_url:
            self.bsre_urls = SPPT_CLASS.bsre_url.split('@')
            self.user_pass = self.bsre_urls[0].split(':')
            self.url = self.bsre_urls[1]
            self.nik_pass = self.bsre_urls[2].split(':')
            self.data = dict(
                nik=self.nik_pass[0],
                passphrase=self.nik_pass[1],
                tampilan="visible",
                page='1',
                image='true',
                xAxis='1050',
                yAxis='50',
                width='50',
                height='100',
                tag='',
            )

        # url = len(parameter.split('@')) > 2 and parameter.split('@')[int(len(parameter.split('@'))) - 1] or \
        #     parameter.split('@')[1]
        # url = url.strip('/') + '/api/sign/pdf'
        # user = parameter.split(':')[0]
        # passwd = re.search(':(.*)@', parameter).group(1)

        # if not get_parameter('bsre_pbb_esppt.nik'):
        #     raise Exception(f"bsre_pbb_esppt.nik belum ada di parameter")
        # nik = get_parameter('bsre_pbb_esppt.nik').value.strip().split(':')
        # if len(nik) > 2:
        #     niks = copy(nik)
        #     del niks[0]
        #     passp = [n for n in niks]
        #     passp = ':'.join(passp)
        # else:
        #     passp = nik[1]

    # def tte_sent(self, files):
    #     """
    #     file : file pdf, dan signature yang dikirimkan
    #     """
    #     logtte = logging.getLogger('BSRE')
    #     url = len(parameter.split('@')) > 2 and parameter.split('@')[int(len(parameter.split('@'))) - 1] or \
    #         parameter.split('@')[1]
    #     url = url.strip('/') + '/api/sign/pdf'
    #     user = parameter.split(':')[0]
    #     passwd = re.search(':(.*)@', parameter).group(1)

    #     if not get_parameter('bsre_pbb_esppt.nik'):
    #         raise Exception(f"bsre_pbb_esppt.nik belum ada di parameter")
    #     nik = get_parameter('bsre_pbb_esppt.nik').value.strip().split(':')
    #     if len(nik) > 2:
    #         niks = copy(nik)
    #         del niks[0]
    #         passp = [n for n in niks]
    #         passp = ':'.join(passp)
    #     else:
    #         passp = nik[1]

    #     data = dict(
    #         nik=nik[0],
    #         passphrase=passp,
    #         tampilan="visible",
    #         page='1',
    #         image='true',
    #         xAxis='350',
    #         yAxis='5',
    #         width='50',
    #         height='50',
    #         tag='',
    #     )
    #     if not get_parameter('bsre_pbb_esppt_ttd'):
    #         raise Exception(f"bsre_pbb_esppt_ttd belum ada di parameter")

    def proses(self, file, tahun):
        log.debug("def proses")
        log.debug("File: %s Tahun %s", file, tahun)
        img_file = os.path.join(SPPT_CLASS.files_tpl, tahun)+'.png'
        if not os.path.exists(img_file):
            img_file = os.path.join(SPPT_CLASS.files_tpl, 'sppt.png')

        files = [('file',
                 (os.path.basename(file), open(file, 'rb'), 'application/pdf')),
                 ('imageTTD',
                 ('logo_ttd.png', open(img_file, 'rb'), 'image/png'))]
        log.debug(self.data)

        try:
            log.debug("URL: %s User: %s Pass: %s", self.url,
                      self.user_pass[0], self.user_pass[1])
            resp = requests.post(url=self.url,
                                 auth=(self.user_pass[0], self.user_pass[1]),
                                 data=self.data,
                                 files=files,
                                 verify=False)
        except requests.exceptions.RequestException as err:
            if logging.root.level == logging.DEBUG:
                traceback.print_exc(file=sys.stdout)
                log.debug(err)

            raise Exception(
                f"Gagal membuat koneksi, silakan coba lagi beberapa saat")
        log.debug(resp.headers)
        try:
            resp_js = json.loads(resp.content)
            if 'error' in resp_js and resp_js['error']:
                """ Logging Error """
                raise Exception(f"datarsp['error']")

        except:
            """ response adalah pdf """
            with open(file, 'wb') as f:
                f.write(resp.content)

            """ Logging Ok """
            # return os.path.basename(files)


def get_nop(row):
    # print(row)
    return row["kd_propinsi"] + row["kd_dati2"] + row["kd_kecamatan"] + \
        row["kd_kelurahan"] + row["kd_blok"] + row["no_urut"] + \
        row["kd_jns_op"]

class SendingMail(object):
    def __init__(self, config):
        self.config = config
        self.settings = get_appsettings(config)
        # self.settings['mail.context'] = ssl.SSLContext(
            # ssl.PROTOCOL_TLS_CLIENT)
        self.mailer = mailer_factory_from_settings(self.settings)
        self.sender = '{} <{}>'.format(
            self.settings['mail.sender_name'], self.settings['mail.username'])

    def send(self, email, subject, body, attachments=[]):
        message = Message(
            subject=subject, sender=self.sender, recipients=[email], html=body, attachments=attachments)
        self.mailer.send(message)
        # self.mailer.send_immediately(message)
        # , fail_silently=False)



def send_email(config, option):
    pid_file = os.path.realpath("osipkd-esppt-email.pid")
    if option.stop:
        print('Stop Daemon Email')
        stop_daemon(pid_file)
        sys.exit(1)

    body = get_template('email.tpl')
    if not body:
        log.error("Template Email Not Found")
        sys.exit(1)

    unduhs = EspptUnduh.query().filter_by(status=0).limit(1000)
    if not unduhs.first():
        log.error("No Email to Send")
        return

    log.debug("Email Start")

    sending_mail = SendingMail(config)
    make_pid(pid_file)
    for unduh in unduhs:
        nop = FixNop(unduh.nop)
        sppt = LogEsppt.query().filter_by(
            **nop.to_dict()).filter(LogEsppt.tahun == unduh.tahun).first()
        if not sppt:
            log.error("SPPT Not Found: %s %s", nop, unduh.tahun)
            continue
        file_name = os.path.join(SPPT_CLASS.files_final, sppt.file_name)
        if not os.path.exists(file_name):
            log.error("File Not Found: %s", file_name)
            continue

        with open(file_name, 'rb') as f:
            content = f.read()

        # content = base64.encodebytes(content)
        # content = content.decode('utf-8')

        # files = [[sppt.file_name[5:], content]]
        subject = 'Tagihan PBB Tahun {} NOP {}'.format(
            unduh.tahun, nop.get_dotted())
        
        attachment = Attachment(sppt.file_name, "application/pdf", content)
        sending_mail.send(unduh.email, subject,
                     body.format(nama=unduh.nama, nop=nop.get_dotted(),
                                 tahun=unduh.tahun, nilai=thousand(sppt.pbb_yg_harus_dibayar_sppt)),
                     attachments=[attachment])

        unduh.status = 1
        log.debug("Email Sent: %s", unduh.email)
        DBSession.add(unduh)
    DBSession.flush()
    transaction.commit()
    os.remove(pid_file)


# def test_email(config, option):
#     partner_nops = PartnerNop.query()
#     for partner_nop in partner_nops:
#         partner_nop = partner_nop.nop
#         rows = rows.filter(LogEsppt.nop_raw == partner_nop)

#     for row in rows:
#         nop = get_nop(row)
#         fx_nop = FixNop(nop)
#         if partner_nop:
#         p = dict(penerima=row.destination)
#         if '@' in row.destination:
#             pdf_file = os.path.join(SPPT_CLASS.files_final, row.file_name)
#             if not os.path.exists(pdf_file):
#                 continue

#             email_file = create_email(nop, row.tahun)
#             if 'error' in email_file:
#                 return
#             p['email'] = row.destination
#             with open(email_file) as f:
#                 p['pesan'] = f.read()

#             with open(pdf_file, 'rb') as f:
#                 content = f.read()

#             content = base64.encodebytes(content)
#             content = content.decode('utf-8')
#             p['files'] = [[pdf_file, content]]

#         row.sent = datetime.now()
#         DBSession.add(row)
#         DBSession.flush()