realisasi_kurang.py
5.41 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
import colander
from datetime import datetime
from sqlalchemy import not_, func, literal_column, and_, or_
from sqlalchemy.orm import aliased
from sqlalchemy.sql import select
from pyramid.view import (view_config)
from pyramid.httpexceptions import (HTTPFound,)
from deform import (
Form,
widget,
ValidationFailure,
)
from ..tools import dmy, date_from_str, INVOICE_STATUS as STATUS, BUKUS
from ..views import ColumnDT, DataTables, BaseView, deferred_status
from ..models import PbbmDBSession
from ..models import Sppt, PembayaranSppt, Kecamatan, Kelurahan
class view(BaseView):
########
# List #
########
@view_config(route_name='pbbm-realisasi-kurang', renderer='templates/realisasi-kurang/list.pt',
permission='pbbm-realisasi-kurang')
def view_list(self):
kecamatans = Kecamatan.query().order_by('kd_kecamatan').all()
kelurahans = Kelurahan.query().\
filter_by(kd_kecamatan=self.ses['kd_kecamatan']).\
order_by('kd_kelurahan').all()
return dict(project=self.req.app_name,
kecamatans = kecamatans,
kelurahans = kelurahans,
tahun = datetime.now().year,
bukus = BUKUS)
##########
# Action #
##########
@view_config(route_name='pbbm-realisasi-kurang-act', renderer='json',
permission='pbbm-realisasi-kurang-act')
def view_act(self):
request = self.req
ses = request.session
params = request.params
url_dict = request.matchdict
buku_min = BUKUS[self.buku][1]
buku_max = BUKUS[self.buku][2]
realisasi = select([PembayaranSppt.kd_propinsi,
PembayaranSppt.kd_dati2,
PembayaranSppt.kd_kecamatan,
PembayaranSppt.kd_kelurahan,
PembayaranSppt.kd_blok,
PembayaranSppt.no_urut,
PembayaranSppt.kd_jns_op,
PembayaranSppt.thn_pajak_sppt,
func.max(PembayaranSppt.tgl_pembayaran_sppt).label('tgl_bayar'),
func.sum(PembayaranSppt.jml_sppt_yg_dibayar -
PembayaranSppt.denda_sppt).label('bayar'),
]).\
group_by(
PembayaranSppt.kd_propinsi,
PembayaranSppt.kd_dati2,
PembayaranSppt.kd_kecamatan,
PembayaranSppt.kd_kelurahan,
PembayaranSppt.kd_blok,
PembayaranSppt.no_urut,
PembayaranSppt.kd_jns_op,
PembayaranSppt.thn_pajak_sppt,
).\
alias('realisasi')
if url_dict['act']=='grid':
columns = [
ColumnDT(func.concat(Sppt.kd_kecamatan, func.concat(".",
func.concat(Sppt.kd_kelurahan, func.concat(".",
func.concat(Sppt.kd_blok, func.concat(".",
func.concat(Sppt.no_urut, func.concat(".",
Sppt.kd_jns_op)))))))), mData="kode"),
ColumnDT(Sppt.nm_wp_sppt, mData='nama'),
ColumnDT(Sppt.pbb_yg_harus_dibayar_sppt, mData='pokok'),
ColumnDT(realisasi.c.bayar, mData='bayar'),
ColumnDT(realisasi.c.tgl_bayar, mData='tgl_bayar'),
]
query = PbbmDBSession.query().select_from(Sppt).\
join(realisasi, and_(
Sppt.kd_propinsi == realisasi.c.kd_propinsi,
Sppt.kd_dati2 == realisasi.c.kd_dati2,
Sppt.kd_kecamatan == realisasi.c.kd_kecamatan,
Sppt.kd_kelurahan == realisasi.c.kd_kelurahan,
Sppt.kd_blok == realisasi.c.kd_blok,
Sppt.no_urut == realisasi.c.no_urut,
Sppt.kd_jns_op == realisasi.c.kd_jns_op,
Sppt.thn_pajak_sppt == realisasi.c.thn_pajak_sppt,
)).\
filter(Sppt.thn_pajak_sppt == ses["tahun"],
Sppt.status_pembayaran_sppt<'2',
Sppt.pbb_yg_harus_dibayar_sppt > realisasi.c.bayar,
or_(Sppt.pbb_yg_harus_dibayar_sppt.between(buku_min, buku_max),
realisasi.c.bayar.between(buku_min, buku_max),
)
)
if ses['kd_kecamatan']=='000':
pass
elif ses['kd_kelurahan']=='000':
query = query.filter(
Sppt.kd_kecamatan == ses["kd_kecamatan"]
)
else:
query = query.filter(
Sppt.kd_kecamatan == ses["kd_kecamatan"],
Sppt.kd_kelurahan == ses["kd_kelurahan"],)
rowTable = DataTables(request.GET, query, columns)
return rowTable.output_result()
def route_list(request):
return HTTPFound(location=request.route_url('bku-ap-payment'))