import-sipkd-anggaran.py
4.27 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
from models_sipkd import Anggaran, Realisasi, Base, DBSession
from models_eis import (Anggaran as EisAnggaran, ApPayment as EisApPayment,
ArPayment as EisArPayment, ByPayment as EisByPayment,
Rekening as EisRekening,
EisBase, EisDBSession)
from conf import sipkd_url
from sqlalchemy import create_engine, literal_column, func
from datetime import datetime
from datetime import date
try:
from urllib import quote_plus, quote
except:
from urllib.parse import quote_plus, quote
engine = create_engine(sipkd_url)
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
now = datetime.now()
tanggal = now.date()
tahun = now.strftime('%Y')
def calculate():
rows = EisDBSession.query(EisAnggaran).filter_by(tahun = str(tahun)).\
order_by(EisAnggaran.departemen_kd, EisAnggaran.kode.desc())
old_level = 0
levels = {}
murnis = {}
perubahans = {}
key = ""
for row in rows:
if row.level_id > old_level:
old_level = row.level_id
key = 'a'+str(row.level_id)
print(row.kode, row.level_id, old_level, key, row.murni)
#JIKA level sama dengan sebelumnya jumlahkan
if row.level_id == old_level:
if not key in murnis:
murnis[key] = row.murni
else:
murnis[key] += row.murni
if not key in perubahans:
perubahans[key] = row.perubahan
else:
perubahans[key] += row.perubahan
#JIKA level < sebelumnya update current row
if row.level_id < old_level:
row.murni = murnis[key]
row.perubahan = perubahans[key]
EisDBSession.add(row)
EisDBSession.flush()
murnis[key] = 0 #key sebelumnya diset jadi 0
perubahans[key] = 0 #key sebelumnya diset jadi 0
key = 'a'+str(row.level_id)
if not key in murnis:
murnis[key] = row.murni
else:
murnis[key] += row.murni
if not key in perubahans:
perubahans[key] = row.perubahan
else:
perubahans[key] += row.perubahan
old_level = row.level_id
def validate_parent(departemen_kd, departemen_nm, rekening):
kode = rekening.split('.')
rekening = "\'" + rekening + "\'"
rows = EisDBSession.query(EisRekening).\
filter(literal_column(rekening).like(
func.concat(EisRekening.kode,'%'))).all()
for row in rows:
if not row.kode:
continue
induk = EisDBSession.query(EisAnggaran).\
filter_by(tahun=str(row.tahun),
kode=row.kode.strip(),
departemen_kd = departemen_kd.strip(),
).first()
if not induk:
induk = EisAnggaran()
induk.tahun = str(tahun)
induk.kode = row.kode.strip()
induk.departemen_kd = departemen_kd.strip()
induk.level_id = row.kode.count('.')
induk.nama = row.nama.strip()
EisDBSession.add(induk)
EisDBSession.flush()
def import_anggaran():
query = DBSession.query(Anggaran).filter_by(tahun=str(tahun))
for row in query.all():
anggaran = EisDBSession.query(EisAnggaran).\
filter_by(tahun = str(row.tahun),
kode=row.kd_rekening,
departemen_kd = row.kd_opd,
).first()
if not anggaran:
anggaran = EisAnggaran()
anggaran.tahun = str(row.tahun)
anggaran.kode = row.kd_rekening.strip()
anggaran.departemen_kd = row.kd_opd.strip()
anggaran.level_id = row.kd_rekening.count('.')
anggaran.nama = row.nm_rekening.strip()
anggaran.departemen_nm = row.nm_opd.strip()
anggaran.murni = row.ang_murni
anggaran.perubahan = row.ang_perubahan
EisDBSession.add(anggaran)
EisDBSession.flush()
validate_parent(row.kd_opd.strip(), row.nm_opd.strip(), row.kd_rekening.strip())
EisDBSession.commit()
import_anggaran()
calculate()
EisDBSession.commit()