from datetime import ( date, timedelta, ) from sqlalchemy import ( func, and_, case, cast, String, ) from opensipkd.payment_channel.models import ( PembayaranSppt, Payment, Pembayaran, Sppt, Inquiry, Reversal, Pembatalan, ) class Parser: def __init__(self, db_session, pbb_session, tgl_awal=None, tgl_akhir=None): self.db_session = db_session self.pbb_session = pbb_session self.tgl_awal = tgl_awal or date.today() self.tgl_akhir = tgl_akhir or date.today() def get_payment_query(self): q = self.pbb_session.query( Payment.propinsi.label('kd_propinsi'), Payment.kabupaten.label('kd_dati2'), Payment.kecamatan.label('kd_kecamatan'), Payment.kelurahan.label('kd_kelurahan'), Payment.blok.label('kd_blok'), Payment.urut.label('no_urut'), Payment.jenis.label('kd_jns_op'), cast(Payment.tahun, String).label('thn_pajak_sppt'), PembayaranSppt.pembayaran_sppt_ke, Sppt.nm_wp_sppt, PembayaranSppt.tgl_pembayaran_sppt, PembayaranSppt.tgl_rekam_byr_sppt, Inquiry.tgl.label("tgl_inquiry"), Sppt.pbb_yg_harus_dibayar_sppt, PembayaranSppt.denda_sppt, PembayaranSppt.jml_sppt_yg_dibayar, Payment.channel.label('channel_kode'), case( [ (Payment.channel == '6010', 'TELLER'), (Payment.channel == '6011', 'ATM'), (Payment.channel == '6012', 'POS'), (Payment.channel == '6013', 'PHONE BANKING'), (Payment.channel == '6014', 'INTERNETBANKING'), (Payment.channel == '6015', 'KIOSK'), (Payment.channel == '6016', 'AUTODEBET'), (Payment.channel == '6017', 'MOBILBANKING'), (Payment.channel == '7012', 'PT.POS'), (Payment.channel == '6025', case( [ (func.substr( Payment.iso_request, 170, 15).in_([ 'TOKOPEDIA ', 'BUKALAPAK ', 'MASAGO ']), func.substr( Payment.iso_request, 170, 15)), (func.substr( Payment.iso_request, 170, 15).in_( ['NG ']), func.substr( Payment.iso_request, 150, 7))], else_=case([ (func.substr( Payment.iso_request, 204, 9).in_( ['INDOMARET', 'ALFAMART']), func.substr( Payment.iso_request, 204, 9))], else_='VSI LAINNYA')))], else_='').label('channel_nama'), func.substr(Payment.iso_request, 162, 8).label('user_id'), Payment.iso_request) q = q.join(Sppt, and_( Sppt.kd_propinsi == Payment.propinsi, Sppt.kd_dati2 == Payment.kabupaten, Sppt.kd_kecamatan == Payment.kecamatan, Sppt.kd_kelurahan == Payment.kelurahan, Sppt.kd_blok == Payment.blok, Sppt.no_urut == Payment.urut, Sppt.kd_jns_op == Payment.jenis, Sppt.thn_pajak_sppt == cast(Payment.tahun, String))) q = q.join(PembayaranSppt, and_( PembayaranSppt.kd_propinsi == Payment.propinsi, PembayaranSppt.kd_dati2 == Payment.kabupaten, PembayaranSppt.kd_kecamatan == Payment.kecamatan, PembayaranSppt.kd_kelurahan == Payment.kelurahan, PembayaranSppt.kd_blok == Payment.blok, PembayaranSppt.no_urut == Payment.urut, PembayaranSppt.kd_jns_op == Payment.jenis, PembayaranSppt.thn_pajak_sppt == cast( Payment.tahun, String))) q = q.join(Inquiry, Inquiry.id == Payment.inquiry_id) q = q.filter(and_( PembayaranSppt.tgl_pembayaran_sppt >= self.tgl_awal, PembayaranSppt.tgl_pembayaran_sppt < self.tgl_akhir + timedelta(1))) return q.order_by(PembayaranSppt.tgl_pembayaran_sppt, Payment.channel) def payment(self): q = self.get_payment_query() no = 0 for row in q: no += 1 invoice_id = ''.join( [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]) dict_row = row._asdict() q_local = self.db_session.query(Pembayaran).filter( Pembayaran.kd_propinsi == row.kd_propinsi, Pembayaran.kd_dati2 == row.kd_dati2, Pembayaran.kd_kecamatan == row.kd_kecamatan, Pembayaran.kd_kelurahan == row.kd_kelurahan, Pembayaran.kd_blok == row.kd_blok, Pembayaran.no_urut == row.no_urut, Pembayaran.kd_jns_op == row.kd_jns_op, Pembayaran.thn_pajak_sppt == row.thn_pajak_sppt, Pembayaran.pembayaran_sppt_ke == row.pembayaran_sppt_ke) row_local = q_local.first() if row_local: method = 'UPDATE' else: row_local = Pembayaran() method = 'INSERT' print(f'Payment #{no} {method} {invoice_id} pembayaran ke ' f'{row.pembayaran_sppt_ke} {row.tgl_pembayaran_sppt} ' f'{row.channel_nama}') row_local.from_dict(dict_row) self.db_session.add(row_local) self.db_session.flush() def get_reversal_query(self): q = self.pbb_session.query( Payment.propinsi, Payment.kabupaten, Payment.kecamatan, Payment.kelurahan, Payment.blok, Payment.urut, Payment.jenis, Payment.tahun, Payment.ke, Reversal.tgl, Sppt.nm_wp_sppt, Payment.channel.label('channel_kode'), func.substr(Payment.iso_request, 162, 8).label('user_id'), case( [ (Payment.channel == '6010', 'TELLER'), (Payment.channel == '6011', 'ATM'), (Payment.channel == '6012', 'POS'), (Payment.channel == '6013', 'PHONE BANKING'), (Payment.channel == '6014', 'INTERNETBANKING'), (Payment.channel == '6015', 'KIOSK'), (Payment.channel == '6016', 'AUTODEBET'), (Payment.channel == '6017', 'MOBILBANKING'), (Payment.channel == '7012', 'PT.POS'), (Payment.channel == '6025', case( [ (func.substr( Payment.iso_request, 170, 15).in_([ 'TOKOPEDIA ', 'BUKALAPAK ', 'MASAGO ']), func.substr( Payment.iso_request, 170, 15)), (func.substr( Payment.iso_request, 170, 15).in_( ['NG ']), func.substr( Payment.iso_request, 150, 7)) ], else_=case([ (func.substr( Payment.iso_request, 204, 9).in_( ['INDOMARET', 'ALFAMART']), func.substr( Payment.iso_request, 204, 9))], else_='VSI LAINNYA')))], else_='').label('channel_nama'), Payment.iso_request, Sppt.pbb_yg_harus_dibayar_sppt, PembayaranSppt.denda_sppt, PembayaranSppt.jml_sppt_yg_dibayar) q = q.select_from(Sppt) q = q.join(Payment, and_( Sppt.kd_propinsi == Payment.propinsi, Sppt.kd_dati2 == Payment.kabupaten, Sppt.kd_kecamatan == Payment.kecamatan, Sppt.kd_kelurahan == Payment.kelurahan, Sppt.kd_blok == Payment.blok, Sppt.no_urut == Payment.urut, Sppt.kd_jns_op == Payment.jenis, Sppt.thn_pajak_sppt == cast(Payment.tahun, String))) q = q.join(PembayaranSppt, and_( PembayaranSppt.kd_propinsi == Payment.propinsi, PembayaranSppt.kd_dati2 == Payment.kabupaten, PembayaranSppt.kd_kecamatan == Payment.kecamatan, PembayaranSppt.kd_kelurahan == Payment.kelurahan, PembayaranSppt.kd_blok == Payment.blok, PembayaranSppt.no_urut == Payment.urut, PembayaranSppt.kd_jns_op == Payment.jenis, PembayaranSppt.thn_pajak_sppt == cast( Payment.tahun, String), PembayaranSppt.pembayaran_sppt_ke == Payment.ke)) q = q.join(Reversal, Payment.id == Reversal.payment_id) q = q.filter(and_( Reversal.tgl >= self.tgl_awal, Reversal.tgl < self.tgl_akhir + timedelta(1))) return q.order_by( Reversal.tgl, func.concat( Payment.propinsi, func.concat( Payment.kabupaten, func.concat( Payment.kecamatan, func.concat( Payment.kelurahan, func.concat( Payment.blok, func.concat( Payment.urut, Payment.jenis))))))) def reversal(self): q = self.get_reversal_query() no = 0 for row in q: no += 1 invoice_id = ''.join( [row.propinsi, row.kabupaten, row.kecamatan, row.kelurahan, row.blok, row.urut, row.jenis, str(row.tahun)]) dict_row = row._asdict() q_local = self.db_session.query(Pembatalan).filter( Pembatalan.propinsi == row.propinsi, Pembatalan.kabupaten == row.kabupaten, Pembatalan.kecamatan == row.kecamatan, Pembatalan.kelurahan == row.kelurahan, Pembatalan.blok == row.blok, Pembatalan.urut == row.urut, Pembatalan.jenis == row.jenis, Pembatalan.tahun == row.tahun, Pembatalan.ke == row.ke) row_local = q_local.first() if row_local: method = 'UPDATE' else: row_local = Pembatalan() method = 'INSERT' print(f'Reversal #{no} {method} {invoice_id} pembayaran ke ' f'{row.ke} {row.tgl} {row.channel_nama}') row_local.from_dict(dict_row) self.db_session.add(row_local) self.db_session.flush()