eis.py
7.6 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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
from datetime import datetime, timedelta, date
from ..models import Targets
from pyramid.view import (
view_config,
)
from sqlalchemy import func
from ..models import (
Kecamatan,
Sppt,
PembayaranSppt,
# Targets
)
from ..models import PbbmDBSession
from ..tools import thousand, NAMA_BULAN, get_settings
from ..views import (BaseView)
def get_realisasi(awal, akhir):
# akhir = akhir + timedelta(days=1)
return PbbmDBSession.query(func.count(PembayaranSppt.kd_propinsi).label('jumlah'),
func.sum(
PembayaranSppt.jml_sppt_yg_dibayar).label(
'pokok'), ). \
filter(PembayaranSppt.tgl_pembayaran_sppt.between(awal, akhir)).first()
def get_buku(awal, akhir, min, max):
# tanggal = datetime.now().date()
# tgl_awal = date(tanggal.year, 1, 1)
# tgl_akhir = date(tanggal.year, 12, 31)
return PbbmDBSession.query(
func.sum(PembayaranSppt.jml_sppt_yg_dibayar).label('pokok'), ). \
filter(
(PembayaranSppt.jml_sppt_yg_dibayar).between(min, max),
PembayaranSppt.tgl_pembayaran_sppt.between(awal, akhir)).first()
def get_bar_month(awal, akhir):
# tanggal = datetime.now().date()
# tgl_awal = date(tanggal.year, 1, 1)
# tgl_akhir = date(tanggal.year, 12, 31)
bulan = func.to_char(PembayaranSppt.tgl_pembayaran_sppt, 'MM')
return PbbmDBSession.query(bulan.label('bulan'),
func.sum(
PembayaranSppt.jml_sppt_yg_dibayar).label(
'pokok'), ). \
filter(PembayaranSppt.tgl_pembayaran_sppt.between(awal, akhir)). \
group_by(bulan). \
order_by(bulan).all()
def get_bar_wil(area, tanggal):
area = int(area)
settings = get_settings()
limit = 'eis_page_size' in settings and int(settings['eis_page_size']) or 10
start = area * limit
stop = (area + 1) * limit
# tanggal = datetime.now().date()
ketetapan = PbbmDBSession.query(Sppt.kd_kecamatan, Kecamatan.nm_kecamatan,
func.sum(Sppt.pbb_yg_harus_dibayar_sppt).label('ketetapan')). \
join(Kecamatan, (Sppt.kd_kecamatan == Kecamatan.kd_kecamatan)). \
group_by(Sppt.kd_kecamatan, Kecamatan.nm_kecamatan). \
filter(Sppt.thn_pajak_sppt == str(tanggal.year)). \
order_by(Sppt.kd_kecamatan). \
slice(start, stop)
return ketetapan
def get_bar_wil_rel(kd_kecamatan, tanggal):
# tanggal = datetime.now().date()
return PbbmDBSession.query(
func.sum(PembayaranSppt.jml_sppt_yg_dibayar).label(
'realisasi')). \
filter(PembayaranSppt.thn_pajak_sppt == str(tanggal.year),
PembayaranSppt.kd_kecamatan == kd_kecamatan)
class View(BaseView):
@view_config(route_name='pbbm-eis', renderer='templates/eis.pt')
def view_edit(self):
settings = get_settings()
eis_page_size = 'eis_page_size' in settings and int(settings['eis_page_size']) or 10
pages = int(round(Kecamatan.query().count() / float(eis_page_size) + 0.49))
eis_interval = 'eis_interval' in settings and int(settings['eis_interval']) or 5000
reload = 'eis_reload' in settings and int(settings['eis_reload']) or 3600000
return dict(project='pbb', pages=pages,
interval=eis_interval,
reload=reload)
##########
# Action #
##########
@view_config(route_name='pbbm-eis-act', renderer='json')
def view_act(self):
req = self.req
params = req.params
url_dict = req.matchdict
now = datetime.now().date()
# if now.year>int(self.ses["tahun"])
year = int(self.ses["tahun"])
awal = date(year, 1, 1)
akhir = date(year, 12, 31)
if akhir>now:
akhir = now
bulan = date(akhir.year, akhir.month, 1)
minggu = akhir - timedelta(days=akhir.weekday())
data = {}
if url_dict['act'] == 'harian':
val = get_realisasi(akhir, akhir)
data['sum_today'] = thousand(val.pokok and val.pokok or 0, 0)
data['cnt_today'] = thousand(val.jumlah and val.jumlah or 0, 0)
val = get_realisasi(minggu, akhir)
data['sum_week'] = thousand(val.pokok and val.pokok or 0, 0)
data['cnt_week'] = thousand(val.jumlah and val.jumlah or 0, 0)
val = get_realisasi(bulan, akhir)
data['sum_month'] = thousand(val.pokok and val.pokok or 0, 0)
data['cnt_month'] = thousand(val.jumlah and val.jumlah or 0, 0)
val = get_realisasi(awal, akhir)
data['sum_year'] = thousand(val.pokok and val.pokok or 0, 0)
data['cnt_year'] = thousand(val.jumlah and val.jumlah or 0, 0)
return data
elif url_dict['act'] == 'buku':
array = []
val = get_buku(awal, akhir, 0, 100000)
data = dict(pokok=val.pokok and val.pokok or 0,
uraian='BUKU I (0-100.000)')
array.append(data)
val = get_buku(awal, akhir, 100001, 500000)
data = dict(pokok=val.pokok and val.pokok or 0,
uraian='BUKU II (100.001-500.000)')
array.append(data)
val = get_buku(awal, akhir, 500001, 2000000)
data = dict(pokok=val.pokok and val.pokok or 0,
uraian='BUKU III (500.001-2.000.000)')
array.append(data)
val = get_buku(awal, akhir, 2000001, 5000000)
data = dict(pokok=val.pokok and val.pokok or 0,
uraian='BUKU IV BUKU IV (2.000.001-5.000.000)')
array.append(data)
val = get_buku(awal, akhir, 5000001, 999999999999999)
data = dict(pokok=val.pokok and val.pokok or 0,
uraian='BUKU V (>5.000.001)')
array.append(data)
return array
elif url_dict['act'] == 'bar-month':
array = []
for bulan in NAMA_BULAN:
if bulan[2:]:
array.append(dict(target=0,
realisasi=0,
uraian=bulan[2]))
rows = get_bar_month(awal, akhir)
for row in rows:
array[int(row.bulan) - 1]['realisasi'] = row.pokok
row = Targets.query_jenis_sum(akhir.year, 1).first()
if row:
array[0]['target'] = row.m01
array[1]['target'] = row.m02
array[2]['target'] = row.m03
array[3]['target'] = row.m04
array[4]['target'] = row.m05
array[5]['target'] = row.m06
array[6]['target'] = row.m07
array[7]['target'] = row.m08
array[8]['target'] = row.m09
array[9]['target'] = row.m10
array[10]['target'] = row.m11
array[11]['target'] = row.m12
return array
elif url_dict['act'] == 'bar-wil':
area = params['area']
array = []
ketetapan = get_bar_wil(area, akhir)
for row in ketetapan.all():
rel = get_bar_wil_rel(row[0], akhir).scalar()
array.append(dict(kode=row[0],
nama=row[1],
ketetapan=row[2],
realisasi=rel and rel or 0))
return array