def save():
print('{line} -> {ip} {forwarder} {is_send} {mti} {bits}'.format(
line=r_log.line,
ip=ip,
forwarder=forwarder,
is_send=is_send,
mti=mti,
bits=bits))
r_iso = Iso()
r_iso.id = conf.nilai_int = r_log.id
r_iso.tgl = r_log.tgl
r_iso.jenis_id = r_log.jenis_id
r_iso.kategori_id = r_log.kategori_id
r_iso.ip = ip
r_iso.forwarder = forwarder
r_iso.is_send = is_send
r_iso.mti = mti
for key in bits:
value = bits[key]
n = str(key).zfill(3)
fieldname = 'bit_{n}'.format(n=n)
setattr(r_iso, fieldname, value)
DBSession.add(r_iso)
conf.updated = create_now()
DBSession.add(conf)
try:
DBSession.flush()
except IntegrityError as err:
s_err = str(err)
if s_err.find('duplicate key') > -1:
print(' sudah ada')
DBSession.rollback()
else:
raise (err)
DBSession.commit()
def save():
print('{line} -> {ip} {forwarder} {is_send} {mti} {bits}'.format(
line=r_log.line, ip=ip, forwarder=forwarder, is_send=is_send, mti=mti,
bits=bits))
r_iso = Iso()
r_iso.id = conf.nilai_int = r_log.id
r_iso.tgl = r_log.tgl
r_iso.jenis_id = r_log.jenis_id
r_iso.kategori_id = r_log.kategori_id
r_iso.ip = ip
r_iso.forwarder = forwarder
r_iso.is_send = is_send
r_iso.mti = mti
for key in bits:
value = bits[key]
n = str(key).zfill(3)
fieldname = 'bit_{n}'.format(n=n)
setattr(r_iso, fieldname, value)
DBSession.add(r_iso)
conf.updated = create_now()
DBSession.add(conf)
try:
DBSession.flush()
except IntegrityError as err:
s_err = str(err)
if s_err.find('duplicate key') > -1:
print(' sudah ada')
DBSession.rollback()
else:
raise(err)
DBSession.commit()
def create_payment(self, inq):
pay, urutan = self.sismiop_create_payment(inq)
d = pay.to_dict()
prefix_ntp = ''
for fieldname in PREFIX_NTP_FIELDS:
prefix_ntp += d[fieldname] or '00'
ntp = query.create_ntp(prefix_ntp)
iso_pay = models.Payment(id=ntp)
iso_pay.inquiry_id = inq.id
iso_pay.propinsi = inq.propinsi
iso_pay.kabupaten = inq.kabupaten
iso_pay.kecamatan = inq.kecamatan
iso_pay.kelurahan = inq.kelurahan
iso_pay.blok = inq.blok
iso_pay.urut = inq.urut
iso_pay.jenis = inq.jenis
iso_pay.tahun = inq.tahun
iso_pay.ke = urutan
iso_pay.kd_kanwil_bank = pay.kd_kanwil
iso_pay.kd_kppbb_bank = pay.kd_kantor
iso_pay.kd_bank_tunggal = '00'
iso_pay.kd_bank_persepsi = '00'
iso_pay.kd_tp = pay.kd_tp
#iso_pay.channel = self.parent.from_iso.get_channel()
iso_pay.ntb = self.parent.from_iso.get_ntb()
iso_pay.iso_request = self.parent.from_iso.raw
DBSession.add(iso_pay)
DBSession.flush()
self.set_ntp(ntp)
def create_payment(self, inq):
pay, urutan = self.sismiop_create_payment(inq)
d = pay.to_dict()
prefix_ntp = ''
for fieldname in PREFIX_NTP_FIELDS:
prefix_ntp += d[fieldname] or '00'
ntp = self.query.create_ntp(prefix_ntp)
iso_pay = models.Payment(id=ntp)
iso_pay.inquiry_id = inq.id
iso_pay.propinsi = inq.propinsi
iso_pay.kabupaten = inq.kabupaten
iso_pay.kecamatan = inq.kecamatan
iso_pay.kelurahan = inq.kelurahan
iso_pay.blok = inq.blok
iso_pay.urut = inq.urut
iso_pay.jenis = inq.jenis
iso_pay.tahun = inq.tahun
iso_pay.ke = urutan
iso_pay.kd_kanwil_bank = pay.kd_kanwil
iso_pay.kd_kppbb_bank = pay.kd_kantor
iso_pay.kd_bank_tunggal = '00'
iso_pay.kd_bank_persepsi = '00'
iso_pay.kd_tp = pay.kd_tp
iso_pay.channel = self.parent.from_iso.get_channel()
iso_pay.ntb = self.parent.from_iso.get_ntb()
iso_pay.iso_request = self.parent.from_iso.raw
DBSession.add(iso_pay)
DBSession.flush()
self.parent.set_ntp(ntp)
def inquiry_request_handler(self):
try:
self.save_request_log()
self._inquiry_request_handler()
self.save_response_log()
DBSession.commit()
except:
self.ack_unknown()
def __init__(self, db_url, db_schema, **kwargs):
self.engine = create_engine(db_url)
Base.metadata.bind = self.engine
DBSession.configure(bind=self.engine)
self.models = Models(Base, db_schema)
self.query = Query(self.models, DBSession)
self.persen_denda = 'persen_denda' in kwargs and \
kwargs['persen_denda'] or 0
def set_unpaid(self):
pay = Reversal.set_unpaid(self)
if not pay:
q = DBSession.query(self.models.Payment).filter_by(
propinsi=self.invoice.kd_propinsi,
kabupaten=self.invoice.kd_dati2,
kecamatan=self.invoice.kd_kecamatan,
kelurahan=self.invoice.kd_kelurahan,
blok=self.invoice.kd_blok,
urut=self.invoice.no_urut,
jenis=self.invoice.kd_jns_op,
tahun=self.invoice.thn_pajak_sppt)
q.delete()
DBSession.commit()
def set_unpaid(self):
pay = Reversal.set_unpaid(self)
if not pay:
q = DBSession.query(self.models.Payment).filter_by(
propinsi=self.invoice.kd_propinsi,
kabupaten=self.invoice.kd_dati2,
kecamatan=self.invoice.kd_kecamatan,
kelurahan=self.invoice.kd_kelurahan,
blok=self.invoice.kd_blok,
urut=self.invoice.no_urut,
jenis=self.invoice.kd_jns_op,
tahun=self.invoice.thn_pajak_sppt)
q.delete()
DBSession.commit()
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter(models.Invoice.status == 0)
q = q.order_by(models.Invoice.id)
offset = -1
count = 0
awal = time()
while True:
if time() - awal > TIMEOUT:
break
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(models, DBSession, row.kode, persen_denda)
if calc.total < 1:
continue
count += 1
if calc.invoice.jatuh_tempo:
jatuh_tempo = calc.invoice.jatuh_tempo.strftime('%d-%m-%Y')
else:
jatuh_tempo = 'tidak ada'
msg = '#{no}/{count} {id} Rp {tagihan} + '\
'Rp {denda} = Rp {total}, jatuh tempo {jatuh_tempo}'
msg = msg.format(no=count, id=row.kode, tagihan=calc.tagihan,
denda=calc.denda, total=calc.total, count=sample_count,
jatuh_tempo=jatuh_tempo)
print(msg)
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter_by(status_bayar=0)
if option.min:
n = int(option.min)
q = q.filter(models.Invoice.jml_tagihan >= n)
if option.max:
n = int(option.max)
q = q.filter(models.Invoice.jml_tagihan <= n)
q = q.order_by(models.Invoice.jml_tagihan)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(models, DBSession, row.no_tagihan)
if calc.tagihan < 1:
continue
count += 1
msg = '#{no}/{count} {id} Rp {total}'.format(no=count,
id=row.no_tagihan,
total=calc.tagihan,
count=sample_count)
print(msg)
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(self.models.Invoice).filter_by(
status_pembayaran_sppt='0')
if option.min:
n = int(option.min)
q = q.filter(self.models.Invoice.pbb_yg_harus_dibayar_sppt >= n)
if option.max:
n = int(option.max)
q = q.filter(self.models.Invoice.pbb_yg_harus_dibayar_sppt <= n)
if option.tahun:
q = q.filter(self.models.Invoice.thn_pajak_sppt == option.tahun)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(
self.models, DBSession, self.persen_denda, row.kd_propinsi,
row.kd_dati2, row.kd_kecamatan, row.kd_kelurahan,
row.kd_blok, row.no_urut, row.kd_jns_op,
row.thn_pajak_sppt)
if calc.total < 1:
continue
count += 1
invoice_id_raw = sppt2nop(calc.invoice) + \
calc.invoice.thn_pajak_sppt
msg = '#{no}/{count} {id} Rp {total}'.format(
no=count, id=invoice_id_raw, total=calc.total,
count=sample_count)
print(msg)
def _payment_request_handler(self):
inv = self.get_invoice()
if not inv:
return
if self.calc.total != self.get_amount():
return self.ack_insufficient_fund()
s_channel_id = self.from_iso.get_channel_id()
channel_id = int(s_channel_id)
q = DBSession.query(models.Channel).filter_by(id=channel_id)
channel = q.first()
if channel:
channel_name = channel.nama
else:
channel_name = s_channel_id
pay = self.calc.query_payment(inv).first()
pay.date_bayar = pay.time_bayar = self.from_iso.get_transaction_datetime()
pay.cara_bayar = 'Transfer'
pay.ref_bayar = self.from_iso.get_ntb()
pay.nominal = self.calc.total - self.calc.denda
pay.denda = self.calc.denda
pay.jum_bayar = self.calc.total
pay.denda_masaberlaku = self.calc.bulan
pay.is_bayar = 1
pay.status_pembayaran = 'Lunas'
pay.pembayaran_melalui = channel_name
OtherDBSession.add(pay)
OtherDBSession.flush()
self.save_iso_payment(pay)
self.commit()
def __init__(self, invoice_id_raw):
Query.__init__(self, models, DBSession)
self.invoice_id = FixLength(INVOICE_ID)
self.invoice_id.set_raw(invoice_id_raw)
q = self.query_invoice(self.invoice_id['Propinsi'],
self.invoice_id['Kabupaten'], self.invoice_id['Kecamatan'],
self.invoice_id['Kelurahan'], self.invoice_id['Blok'],
self.invoice_id['Urut'], self.invoice_id['Jenis'],
self.invoice_id['Tahun Pajak'])
self.invoice = q.first()
self.ket = None
if not self.invoice:
return
self.nama_wp = self.invoice.nm_wp_sppt
self.payment = self.invoice2payment(self.invoice)
if self.payment:
self.total = self.payment.jml_sppt_yg_dibayar
self.tgl_bayar = self.payment.tgl_rekam_byr_sppt
self.ket = 'pembayaran ke {n}'.format(n=self.payment.pembayaran_sppt_ke)
q = DBSession.query(models.TempatPembayaran).filter_by(
kd_kanwil=self.payment.kd_kanwil_bank,
kd_kppbb=self.payment.kd_kppbb_bank,
kd_bank_tunggal=self.payment.kd_bank_tunggal,
kd_bank_persepsi=self.payment.kd_bank_persepsi,
kd_tp=self.payment.kd_tp)
tp = q.first()
self.tempat_pembayaran = dict()
if tp:
self.tempat_pembayaran['nama'] = tp.nm_tp
alamat = tp.alamat_tp and tp.alamat_tp.strip()
if alamat:
self.tempat_pembayaran['alamat'] = alamat
self.h2h = self.invoice2iso_pay()
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter_by(status_pembayaran=0)
q = q.order_by(models.Invoice.bphtb_harus_dibayarkan)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(self.get_invoice_id_structure())
invoice_id['Tahun'] = row.tahun
invoice_id['Kode'] = row.kode
invoice_id['SSPD No'] = row.no_sspd
calc = CalculateInvoice(models, DBSession, invoice_id,
persen_denda)
if calc.total < 1:
continue
count += 1
msg = '#{no}/{count} {id} Rp {total}'.format(
no=count,
id=invoice_id.get_raw(),
total=calc.total,
count=sample_count)
print(msg)
def __init__(self, invoice_id_raw):
Query.__init__(self, models, DBSession)
self.invoice_id = FixLength(INVOICE_ID)
self.invoice_id.set_raw(invoice_id_raw)
q = self.query_invoice(
self.invoice_id['Propinsi'], self.invoice_id['Kabupaten'],
self.invoice_id['Kecamatan'], self.invoice_id['Kelurahan'],
self.invoice_id['Blok'], self.invoice_id['Urut'],
self.invoice_id['Jenis'], self.invoice_id['Tahun Pajak'])
self.invoice = q.first()
self.ket = None
if not self.invoice:
return
self.nama_wp = self.invoice.nm_wp_sppt
self.payment = self.invoice2payment(self.invoice)
if self.payment:
self.total = self.payment.jml_sppt_yg_dibayar
self.tgl_bayar = self.payment.tgl_rekam_byr_sppt
self.ket = 'pembayaran ke {n}'.format(
n=self.payment.pembayaran_sppt_ke)
q = DBSession.query(models.TempatPembayaran).filter_by(
kd_kanwil=self.payment.kd_kanwil,
kd_kantor=self.payment.kd_kantor,
kd_tp=self.payment.kd_tp)
tp = q.first()
self.tempat_pembayaran = dict()
if tp:
self.tempat_pembayaran['nama'] = tp.nm_tp
alamat = tp.alamat_tp and tp.alamat_tp.strip()
if alamat:
self.tempat_pembayaran['alamat'] = alamat
self.h2h = self.invoice2iso_pay()
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter(models.Invoice.kode_bayar != None).\
filter(models.Invoice.kode_bayar != '')
if option.min:
n = int(option.min)
q = q.filter(models.Invoice.nominal >= n)
if option.max:
n = int(option.max)
q = q.filter(models.Invoice.nominal <= n)
q = q.order_by(models.Invoice.nominal)
offset = -1
count = 0
print('START *************')
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(models, DBSession, row.kode_bayar)
if calc.invoice and calc.paid:
continue
count += 1
msg = '#{no}/{count} {id} Rp {total}'.format(no=count,
id=row.kode_bayar, total=calc.tagihan, count=sample_count)
print(msg)
print('SELESAI *************')
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter_by(status_bayar=0)
if option.min:
n = int(option.min)
q = q.filter(models.Invoice.jml_tagihan >= n)
if option.max:
n = int(option.max)
q = q.filter(models.Invoice.jml_tagihan <= n)
q = q.order_by(models.Invoice.jml_tagihan)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(models, DBSession, row.no_tagihan)
if calc.tagihan < 1:
continue
count += 1
msg = '#{no}/{count} {id} Rp {total}'.format(no=count,
id=row.no_tagihan, total=calc.tagihan, count=sample_count)
print(msg)
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice).filter_by(
status_pembayaran=0).filter(
models.Invoice.bphtb_harus_dibayarkan > 0)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(self.get_invoice_id_structure())
invoice_id['Tahun'] = row.tahun
invoice_id['Kode'] = row.kode
invoice_id['SSPD No'] = row.no_sspd
calc = CalculateInvoice(
models, DBSession, invoice_id, persen_denda)
if calc.total < 1:
continue
count += 1
invoice_id_raw = invoice_id.get_raw()
self.on_print(option, count, invoice_id_raw, calc)
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(
models.Invoice).filter_by(status_pembayaran=0).filter(
models.Invoice.bphtb_harus_dibayarkan > 0)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(self.get_invoice_id_structure())
invoice_id['Tahun'] = row.tahun
invoice_id['Kode'] = row.kode
invoice_id['SSPD No'] = row.no_sspd
calc = CalculateInvoice(models, DBSession, invoice_id,
persen_denda)
if calc.total < 1:
continue
count += 1
invoice_id_raw = invoice_id.get_raw()
self.on_print(option, count, invoice_id_raw, calc)
def response(self):
if not self.rev.invoice:
return self.parent.ack_payment_not_found()
if not self.rev.is_paid():
return self.parent.ack_invoice_open()
if not self.rev.payment:
return self.parent.ack_payment_not_found()
if not self.is_transaction_owner():
return self.parent.ack_payment_owner()
iso_pay = self.rev.get_iso_payment(self.invoice_id)
if not iso_pay:
return self.parent.ack_payment_not_found_2()
iso_rev = models.Reversal(payment_id=iso_pay.id)
iso_rev.iso_request = self.parent.from_iso.raw
DBSession.add(iso_rev)
self.rev.set_unpaid() # flush
self.commit()
def response(self):
if not self.rev.invoice:
return self.parent.ack_payment_not_found()
if not self.rev.is_paid():
return self.parent.ack_invoice_open()
if not self.rev.payment:
return self.parent.ack_payment_not_found()
if not self.is_transaction_owner():
return self.parent.ack_payment_owner()
iso_pay = self.rev.get_iso_payment(self.invoice_id)
if not iso_pay:
return self.parent.ack_payment_not_found_2()
iso_rev = models.Reversal(payment_id=iso_pay.id)
iso_rev.iso_request = self.parent.from_iso.raw
DBSession.add(iso_rev)
self.rev.set_unpaid() # flush
self.commit()
def save():
vals = dict()
data = r_iso.to_dict()
for key in ('tgl', 'mti', 'bit_003', 'bit_004', 'bit_032'):
vals[key] = data[key]
tgl = r_iso.tgl.date()
amount = float(r_iso.bit_004)
if r_iso.mti == '0210':
if r_iso.bit_003 in INQUIRY_CODES:
method_id = 1
else:
method_id = 2
else:
method_id = 3
method_name = methods[method_id]
bank_id = r_iso.bit_032[-3:]
bank_id = int(bank_id)
bank_name = banks[bank_id]
msg = '{id} {data} -> {tgl} {bank} {method} {amount}'.format(
id=r_iso.id,
tgl=tgl,
bank=bank_name,
method=method_name,
amount=amount,
data=vals)
print(msg)
q = DBSession.query(Summary).filter_by(jenis_id=r_iso.jenis_id,
tgl=tgl,
method_id=method_id,
bank_id=bank_id)
r_sum = q.first()
if r_sum:
r_sum.trx_count += 1
r_sum.trx_amount += amount
else:
r_sum = Summary()
r_sum.jenis_id = r_iso.jenis_id
r_sum.tgl = tgl
r_sum.method_id = method_id
r_sum.bank_id = bank_id
r_sum.trx_count = 1
r_sum.trx_amount = amount
DBSession.add(r_sum)
save_conf()
def invoice2iso_pay(self):
q = DBSession.query(models.Payment).filter_by(
propinsi=self.invoice.kd_propinsi,
kabupaten=self.invoice.kd_dati2,
kecamatan=self.invoice.kd_kecamatan,
kelurahan=self.invoice.kd_kelurahan,
blok=self.invoice.kd_blok,
urut=self.invoice.no_urut,
jenis=self.invoice.kd_jns_op,
tahun=self.invoice.thn_pajak_sppt).order_by(models.Payment.ke)
r = []
for iso_pay in q:
q_inq = DBSession.query(
models.Inquiry).filter_by(id=iso_pay.inquiry_id)
iso_inq = q_inq.first()
total = iso_inq.tagihan + iso_inq.denda
ket = 'Pembayaran ke {k}'.format(k=iso_pay.ke)
d = dict(ket=ket, tgl=iso_inq.tgl, total=total)
r.append(d)
return r
def invoice2iso_pay(self):
q = DBSession.query(models.Payment).filter_by(
propinsi=self.invoice.kd_propinsi,
kabupaten=self.invoice.kd_dati2,
kecamatan=self.invoice.kd_kecamatan,
kelurahan=self.invoice.kd_kelurahan,
blok=self.invoice.kd_blok,
urut=self.invoice.no_urut,
jenis=self.invoice.kd_jns_op,
tahun=self.invoice.thn_pajak_sppt).order_by(
models.Payment.ke)
r = []
for iso_pay in q:
q_inq = DBSession.query(models.Inquiry).filter_by(
id=iso_pay.inquiry_id)
iso_inq = q_inq.first()
total = iso_inq.tagihan + iso_inq.denda
ket = 'Pembayaran ke {k}'.format(k=iso_pay.ke)
d = dict(ket=ket, tgl=iso_inq.tgl, total=total)
r.append(d)
return r
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice.tahun, models.Invoice.sptno,
models.Rekening.rekeningnm,
models.Rekening.rekeningkd)
q = q.filter(models.Invoice.pajak_id == models.Pajak.id)
q = q.filter(models.Pajak.rekening_id == models.Rekening.id)
q = q.filter(models.Invoice.status_pembayaran == 0)
if option.jenis:
pola = '%{nama}%'.format(nama=option.jenis)
q = q.filter(models.Rekening.rekeningnm.ilike(pola))
if option.berdenda:
q = q.filter(models.Invoice.jatuhtempotgl < date.today())
elif option.tanpa_denda:
q = q.filter(models.Invoice.jatuhtempotgl >= date.today())
q = q.filter(models.Invoice.pajak_terhutang > 0)
q = q.order_by(models.Invoice.pajak_terhutang)
#q = q.order_by(models.Invoice.tahun.desc(), models.Invoice.sptno.desc())
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(INVOICE_ID)
invoice_id['Tahun'] = row.tahun
invoice_id['SPT No'] = row.sptno
invoice_id_raw = invoice_id.get_raw()
calc = CalculateInvoice(models, DBSession, invoice_id_raw,
persen_denda)
if calc.total < 1:
continue
count += 1
if calc.invoice.jatuhtempotgl:
jatuh_tempo = calc.invoice.jatuhtempotgl.strftime('%d-%m-%Y')
else:
jatuh_tempo = 'tidak ada'
msg = '#{no}/{count} {id} {nama_rek} {kode_rek} Rp {tagihan} + '\
'Rp {denda} = Rp {total}, jatuh tempo {jatuh_tempo}'
msg = msg.format(no=count,
id=invoice_id_raw,
nama_rek=row.rekeningnm,
kode_rek=row.rekeningkd,
tagihan=calc.tagihan,
denda=calc.denda,
total=calc.total,
count=sample_count,
jatuh_tempo=jatuh_tempo)
print(msg)
def save():
vals = dict()
data = r_iso.to_dict()
for key in ('tgl', 'mti', 'bit_003', 'bit_004', 'bit_032'):
vals[key] = data[key]
tgl = r_iso.tgl.date()
amount = float(r_iso.bit_004)
if r_iso.mti == '0210':
if r_iso.bit_003 in INQUIRY_CODES:
method_id = 1
else:
method_id = 2
else:
method_id = 3
method_name = methods[method_id]
bank_id = r_iso.bit_032[-3:]
bank_id = int(bank_id)
bank_name = banks[bank_id]
msg = '{id} {data} -> {tgl} {bank} {method} {amount}'.format(
id=r_iso.id, tgl=tgl, bank=bank_name, method=method_name,
amount=amount, data=vals)
print(msg)
q = DBSession.query(Summary).filter_by(
jenis_id=r_iso.jenis_id, tgl=tgl, method_id=method_id,
bank_id=bank_id)
r_sum = q.first()
if r_sum:
r_sum.trx_count += 1
r_sum.trx_amount += amount
else:
r_sum = Summary()
r_sum.jenis_id = r_iso.jenis_id
r_sum.tgl = tgl
r_sum.method_id = method_id
r_sum.bank_id = bank_id
r_sum.trx_count = 1
r_sum.trx_amount = amount
DBSession.add(r_sum)
save_conf()
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice.tahun, models.Invoice.sptno,
models.Rekening.rekeningnm, models.Rekening.rekeningkd)
q = q.filter(models.Invoice.pajak_id == models.Pajak.id)
q = q.filter(models.Pajak.rekening_id == models.Rekening.id)
q = q.filter(models.Invoice.status_pembayaran == 0)
if option.jenis:
pola = '%{nama}%'.format(nama=option.jenis)
q = q.filter(models.Rekening.rekeningnm.ilike(pola))
if option.berdenda:
q = q.filter(models.Invoice.jatuhtempotgl < date.today())
elif option.tanpa_denda:
q = q.filter(models.Invoice.jatuhtempotgl >= date.today())
q = q.filter(models.Invoice.pajak_terhutang > 0)
q = q.order_by(models.Invoice.pajak_terhutang)
#q = q.order_by(models.Invoice.tahun.desc(), models.Invoice.sptno.desc())
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(INVOICE_ID)
invoice_id['Tahun'] = row.tahun
invoice_id['SPT No'] = row.sptno
invoice_id_raw = invoice_id.get_raw()
calc = CalculateInvoice(models, DBSession, invoice_id_raw,
persen_denda)
if calc.total < 1:
continue
count += 1
if calc.invoice.jatuhtempotgl:
jatuh_tempo = calc.invoice.jatuhtempotgl.strftime('%d-%m-%Y')
else:
jatuh_tempo = 'tidak ada'
msg = '#{no}/{count} {id} {nama_rek} {kode_rek} Rp {tagihan} + '\
'Rp {denda} = Rp {total}, jatuh tempo {jatuh_tempo}'
msg = msg.format(no=count, id=invoice_id_raw, nama_rek=row.rekeningnm,
kode_rek=row.rekeningkd, tagihan=calc.tagihan,
denda=calc.denda, total=calc.total, count=sample_count,
jatuh_tempo=jatuh_tempo)
print(msg)
def ack_already_paid(self):
pay = self.calc.invoice2payment()
ntp = ''
if pay and self.is_transaction_owner(pay):
calc = self.calc
q = DBSession.query(models.Payment).filter_by(
propinsi=calc.propinsi,
kabupaten=calc.kabupaten,
kecamatan=calc.kecamatan,
kelurahan=calc.kelurahan,
blok=calc.blok,
urut=calc.urut,
jenis=calc.jenis,
tahun=calc.tahun).order_by(models.Payment.ke.desc())
iso_pay = q.first()
if iso_pay:
ntp = iso_pay.id
self.parent.set_ntp(ntp)
return self.parent.ack_already_paid()
def ack_already_paid(self):
pay = self.calc.invoice2payment()
ntp = ''
if pay and self.is_transaction_owner(pay):
calc = self.calc
q = DBSession.query(models.Payment).filter_by(
propinsi=calc.propinsi,
kabupaten=calc.kabupaten,
kecamatan=calc.kecamatan,
kelurahan=calc.kelurahan,
blok=calc.blok,
urut=calc.urut,
jenis=calc.jenis,
tahun=calc.tahun).order_by(
models.Payment.ke.desc())
iso_pay = q.first()
if iso_pay:
ntp = iso_pay.id
self.set_ntp(ntp)
return self.parent.ack_already_paid()
def save():
if not line_id:
return
s = '\n'.join(lines)
print([s])
row = Log()
row.jenis_id = jenis_id
row.line = s
row.line_id = line_id
row.tgl = waktu
row.kategori_id = KATEGORI.index(kategori)
DBSession.add(row)
try:
DBSession.flush()
except IntegrityError as err:
s_err = str(err)
if s_err.find(duplicate_key_message) > -1:
print(' sudah ada')
DBSession.rollback()
else:
raise (err)
DBSession.commit()
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(
self.models.Invoice).filter_by(status_pembayaran_sppt='0')
if option.min:
n = int(option.min)
q = q.filter(self.models.Invoice.pbb_yg_harus_dibayar_sppt >= n)
if option.max:
n = int(option.max)
q = q.filter(self.models.Invoice.pbb_yg_harus_dibayar_sppt <= n)
q = q.order_by(self.models.Invoice.thn_pajak_sppt.desc(),
self.models.Invoice.pbb_yg_harus_dibayar_sppt)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
calc = CalculateInvoice(self.models, DBSession, self.persen_denda,
row.kd_propinsi, row.kd_dati2,
row.kd_kecamatan, row.kd_kelurahan,
row.kd_blok, row.no_urut, row.kd_jns_op,
row.thn_pajak_sppt)
if calc.total < 1:
continue
count += 1
invoice_id_raw = sppt2nop(
calc.invoice) + calc.invoice.thn_pajak_sppt
msg = '#{no}/{count} {id} Rp {total}'.format(no=count,
id=invoice_id_raw,
total=calc.total,
count=sample_count)
print(msg)
def show(self, option):
sample_count = int(option.sample_count)
q = DBSession.query(models.Invoice.tahun, models.Invoice.sptno,
models.Rekening.rekeningnm,
models.Rekening.rekeningkd)
q = q.filter(models.Invoice.pajak_id == models.Pajak.id)
q = q.filter(models.Pajak.rekening_id == models.Rekening.id)
q = q.filter(models.Invoice.status_pembayaran == 0)
if option.jenis:
pola = '%{nama}%'.format(nama=option.jenis)
q = q.filter(models.Rekening.rekeningnm.ilike(pola))
if option.berdenda:
q = q.filter(models.Invoice.jatuhtempotgl < date.today())
elif option.tanpa_denda:
q = q.filter(models.Invoice.jatuhtempotgl >= date.today())
q = q.filter(models.Invoice.pajak_terhutang > 0)
q = q.order_by(models.Invoice.pajak_terhutang)
offset = -1
count = 0
while True:
if count >= sample_count:
break
offset += 1
row = q.offset(offset).first()
if not row:
break
invoice_id = FixLength(INVOICE_ID)
invoice_id['Tahun'] = row.tahun
invoice_id['SPT No'] = row.sptno
invoice_id_raw = invoice_id.get_raw()
calc = CalculateInvoice(models, iso_models, DBSession,
invoice_id_raw, persen_denda)
if calc.total < 1:
continue
count += 1
self.on_print(option, count, invoice_id_raw, row, calc)
from sqlalchemy import create_engine
from base_models import (
Base,
DBSession,
)
from models import Models
from query import Reversal
from .conf import (
db_url,
transaction_schema,
area_schema,
iso_schema,
)
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
models = Models(Base, transaction_schema, area_schema, iso_schema)
class ReversalByQuery(Reversal):
def __init__(self, invoice_id_raw):
Reversal.__init__(self, models, DBSession, invoice_id_raw)
def set_unpaid(self):
Reversal.set_unpaid(self)
self.DBSession.commit()
sys.exit()
make_pid(pid_file)
limit = int(option.limit)
REGEX_ISO = 'INFO ([\d]*)\.([\d]*)\.([\d]*)\.([\d]*) (.*) (Encode|Decode) MTI'\
' ([\d]*) Data (.*)'
REGEX_ISO = re.compile(REGEX_ISO)
REGEX_FORWARDER = '^([a-b]*)'
REGEX_FORWARDER = re.compile(REGEX_FORWARDER)
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
q_conf = DBSession.query(Conf).filter_by(nama='last id log to iso')
conf = q_conf.first()
offset = 0
while True:
q_log = DBSession.query(Log).filter(Log.id > conf.nilai_int).\
order_by(Log.id).offset(offset).limit(limit)
found = False
for r_log in q_log:
found = True
match = REGEX_ISO.search(r_log.line)
if not match:
continue
ip1, ip2, ip3, ip4, forwarder, arus, mti, bits = match.groups()
ip = '.'.join([ip1, ip2, ip3, ip4])
def commit(self):
DBSession.commit()
OtherDBSession.commit()
Models,
)
from conf import (
db_url,
other_db_url,
db_pool_size,
db_max_overflow,
host,
)
engine = create_engine(
db_url, pool_size=db_pool_size,
max_overflow=db_max_overflow)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
models = Models(Base)
query = Query(models, DBSession)
other_engine = create_engine(
other_db_url, pool_size=db_pool_size,
max_overflow=db_max_overflow)
OtherBase = declarative_base()
OtherBase.metadata.bind = other_engine
session_factory = sessionmaker()
OtherDBSession = scoped_session(session_factory)
OtherDBSession.configure(bind=other_engine)
other_models = OtherModels(OtherBase)
def create_ntp():
option, remain = pars.parse_args(sys.argv[1:])
pid_file = os.path.realpath(option.pid_file)
if option.stop:
print('Stop Daemon')
stop_daemon(pid_file)
sys.exit()
make_pid(pid_file)
limit = int(option.limit)
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
methods = dict()
q = DBSession.query(Method)
for row in q:
methods[row.id] = row.nama
banks = dict()
q = DBSession.query(Bank)
for row in q:
banks[row.id] = row.nama
q_conf = DBSession.query(Conf).filter_by(nama='last id iso summary')
conf = q_conf.first()
offset = 0
while True:
def commit(self):
DBSession.commit()
self.parent.ack()
def save_conf():
conf.nilai_int = r_iso.id
conf.updated = create_now()
DBSession.add(conf)
DBSession.flush()
DBSession.commit()
DBSession.add(row)
try:
DBSession.flush()
except IntegrityError as err:
s_err = str(err)
if s_err.find(duplicate_key_message) > -1:
print(' sudah ada')
DBSession.rollback()
else:
raise (err)
DBSession.commit()
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
lines = []
q = DBSession.query(Jenis).order_by(Jenis.id)
for row in q:
line = '{}: {}'.format(row.id, row.nama)
lines.append(line)
help_jenis = ', '.join(lines)
default_pid_file = 'log2db.pid'
pars = OptionParser()
pars.add_option('-j', '--jenis', help=help_jenis)
pars.add_option('-f', '--file', help='log file')
pars.add_option('-t', '--tail-mode-only', action='store_true')
pars.add_option('', '--start-from')
sys.exit()
make_pid(pid_file)
limit = int(option.limit)
REGEX_ISO = 'INFO ([\d]*)\.([\d]*)\.([\d]*)\.([\d]*) (.*) (Encode|Decode) MTI'\
' ([\d]*) Data (.*)'
REGEX_ISO = re.compile(REGEX_ISO)
REGEX_FORWARDER = '^([a-b]*)'
REGEX_FORWARDER = re.compile(REGEX_FORWARDER)
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
q_conf = DBSession.query(Conf).filter_by(nama='last id log to iso')
conf = q_conf.first()
offset = 0
while True:
q_log = DBSession.query(Log).filter(Log.id > conf.nilai_int).\
order_by(Log.id).offset(offset).limit(limit)
found = False
for r_log in q_log:
found = True
match = REGEX_ISO.search(r_log.line)
if not match:
continue
ip1, ip2, ip3, ip4, forwarder, arus, mti, bits = match.groups()
ip = '.'.join([ip1, ip2, ip3, ip4])
def commit(self):
DBSession.commit()
self.parent.ack()
option, remain = pars.parse_args(sys.argv[1:])
pid_file = os.path.realpath(option.pid_file)
if option.stop:
print('Stop Daemon')
stop_daemon(pid_file)
sys.exit()
make_pid(pid_file)
limit = int(option.limit)
engine = create_engine(db_url)
Base.metadata.bind = engine
DBSession.configure(bind=engine)
methods = dict()
q = DBSession.query(Method)
for row in q:
methods[row.id] = row.nama
banks = dict()
q = DBSession.query(Bank)
for row in q:
banks[row.id] = row.nama
q_conf = DBSession.query(Conf).filter_by(nama='last id iso summary')
conf = q_conf.first()
offset = 0
while True:
def save_conf():
conf.nilai_int = r_iso.id
conf.updated = create_now()
DBSession.add(conf)
DBSession.flush()
DBSession.commit()
