forked from MyEMS/myems
-
Notifications
You must be signed in to change notification settings - Fork 0
/
tenant_billing_input_category.py
268 lines (232 loc) · 12.7 KB
/
tenant_billing_input_category.py
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
import time
from datetime import datetime, timedelta
from decimal import Decimal
import mysql.connector
import tariff
import config
########################################################################################################################
# PROCEDURES
# Step 1: get all tenants
# for each tenant in list:
# Step 2: get the latest start_datetime_utc
# Step 3: get all energy input data since the latest start_datetime_utc
# Step 4: get tariffs
# Step 5: calculate billing by multiplying energy with tariff
# Step 6: save billing data to database
########################################################################################################################
def main(logger):
while True:
# the outermost while loop
################################################################################################################
# Step 1: get all tenants
################################################################################################################
cnx_system_db = None
cursor_system_db = None
try:
cnx_system_db = mysql.connector.connect(**config.myems_system_db)
cursor_system_db = cnx_system_db.cursor()
except Exception as e:
logger.error("Error in step 1.1 of tenant_billing_input_category " + str(e))
if cursor_system_db:
cursor_system_db.close()
if cnx_system_db:
cnx_system_db.close()
# sleep and continue the outermost while loop
time.sleep(60)
continue
print("Connected to MyEMS System Database")
tenant_list = list()
try:
cursor_system_db.execute(" SELECT id, name, cost_center_id "
" FROM tbl_tenants "
" ORDER BY id ")
rows_tenants = cursor_system_db.fetchall()
if rows_tenants is None or len(rows_tenants) == 0:
print("Step 1.2: There isn't any tenants. ")
if cursor_system_db:
cursor_system_db.close()
if cnx_system_db:
cnx_system_db.close()
# sleep and continue the outermost while loop
time.sleep(60)
continue
for row in rows_tenants:
tenant_list.append({"id": row[0], "name": row[1], "cost_center_id": row[2]})
except Exception as e:
logger.error("Error in step 1.2 of tenant_billing_input_category " + str(e))
if cursor_system_db:
cursor_system_db.close()
if cnx_system_db:
cnx_system_db.close()
# sleep and continue the outermost while loop
time.sleep(60)
continue
print("Step 1.2: Got all tenants from MyEMS System Database")
cnx_energy_db = None
cursor_energy_db = None
try:
cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
cursor_energy_db = cnx_energy_db.cursor()
except Exception as e:
logger.error("Error in step 1.3 of tenant_billing_input_category " + str(e))
if cursor_energy_db:
cursor_energy_db.close()
if cnx_energy_db:
cnx_energy_db.close()
if cursor_system_db:
cursor_system_db.close()
if cnx_system_db:
cnx_system_db.close()
# sleep and continue the outermost while loop
time.sleep(60)
continue
print("Connected to MyEMS Energy Database")
cnx_billing_db = None
cursor_billing_db = None
try:
cnx_billing_db = mysql.connector.connect(**config.myems_billing_db)
cursor_billing_db = cnx_billing_db.cursor()
except Exception as e:
logger.error("Error in step 1.4 of tenant_billing_input_category " + str(e))
if cursor_billing_db:
cursor_billing_db.close()
if cnx_billing_db:
cnx_billing_db.close()
if cursor_energy_db:
cursor_energy_db.close()
if cnx_energy_db:
cnx_energy_db.close()
if cursor_system_db:
cursor_system_db.close()
if cnx_system_db:
cnx_system_db.close()
# sleep and continue the outermost while loop
time.sleep(60)
continue
print("Connected to MyEMS Billing Database")
for tenant in tenant_list:
############################################################################################################
# Step 2: get the latest start_datetime_utc
############################################################################################################
print("Step 2: get the latest start_datetime_utc from billing database for " + tenant['name'])
try:
cursor_billing_db.execute(" SELECT MAX(start_datetime_utc) "
" FROM tbl_tenant_input_category_hourly "
" WHERE tenant_id = %s ",
(tenant['id'], ))
row_datetime = cursor_billing_db.fetchone()
start_datetime_utc = datetime.strptime(config.start_datetime_utc, '%Y-%m-%d %H:%M:%S')
start_datetime_utc = start_datetime_utc.replace(minute=0, second=0, microsecond=0, tzinfo=None)
if row_datetime is not None and len(row_datetime) > 0 and isinstance(row_datetime[0], datetime):
# replace second and microsecond with 0
# note: do not replace minute in case of calculating in half hourly
start_datetime_utc = row_datetime[0].replace(second=0, microsecond=0, tzinfo=None)
# start from the next time slot
start_datetime_utc += timedelta(minutes=config.minutes_to_count)
print("start_datetime_utc: " + start_datetime_utc.isoformat()[0:19])
except Exception as e:
logger.error("Error in step 2 of tenant_billing_input_category " + str(e))
# break the for tenant loop
break
############################################################################################################
# Step 3: get all energy input data since the latest start_datetime_utc
############################################################################################################
print("Step 3: get all energy input data since the latest start_datetime_utc")
query = (" SELECT start_datetime_utc, energy_category_id, actual_value "
" FROM tbl_tenant_input_category_hourly "
" WHERE tenant_id = %s AND start_datetime_utc >= %s "
" ORDER BY id ")
cursor_energy_db.execute(query, (tenant['id'], start_datetime_utc, ))
rows_hourly = cursor_energy_db.fetchall()
if rows_hourly is None or len(rows_hourly) == 0:
print("Step 3: There isn't any energy input data to calculate. ")
# continue the for tenant loop
continue
energy_dict = dict()
energy_category_list = list()
end_datetime_utc = start_datetime_utc
for row_hourly in rows_hourly:
current_datetime_utc = row_hourly[0]
energy_category_id = row_hourly[1]
if energy_category_id not in energy_category_list:
energy_category_list.append(energy_category_id)
actual_value = row_hourly[2]
if energy_dict.get(current_datetime_utc) is None:
energy_dict[current_datetime_utc] = dict()
energy_dict[current_datetime_utc][energy_category_id] = actual_value
if current_datetime_utc > end_datetime_utc:
end_datetime_utc = current_datetime_utc
############################################################################################################
# Step 4: get tariffs
############################################################################################################
print("Step 4: get tariffs")
tariff_dict = dict()
for energy_category_id in energy_category_list:
tariff_dict[energy_category_id] = tariff.get_energy_category_tariffs(tenant['cost_center_id'],
energy_category_id,
start_datetime_utc,
end_datetime_utc)
############################################################################################################
# Step 5: calculate billing by multiplying energy with tariff
############################################################################################################
print("Step 5: calculate billing by multiplying energy with tariff")
billing_dict = dict()
if len(energy_dict) > 0:
for current_datetime_utc in energy_dict.keys():
billing_dict[current_datetime_utc] = dict()
for energy_category_id in energy_category_list:
current_tariff = tariff_dict[energy_category_id].get(current_datetime_utc)
current_energy = energy_dict[current_datetime_utc].get(energy_category_id)
if current_tariff is not None \
and isinstance(current_tariff, Decimal) \
and current_energy is not None \
and isinstance(current_energy, Decimal):
billing_dict[current_datetime_utc][energy_category_id] = \
current_energy * current_tariff
if len(billing_dict[current_datetime_utc]) == 0:
del billing_dict[current_datetime_utc]
############################################################################################################
# Step 6: save billing data to billing database
############################################################################################################
print("Step 6: save billing data to billing database")
if len(billing_dict) > 0:
try:
add_values = (" INSERT INTO tbl_tenant_input_category_hourly "
" (tenant_id, "
" energy_category_id, "
" start_datetime_utc, "
" actual_value) "
" VALUES ")
for current_datetime_utc in billing_dict:
for energy_category_id in energy_category_list:
current_billing = billing_dict[current_datetime_utc].get(energy_category_id)
if current_billing is not None and isinstance(current_billing, Decimal):
add_values += " (" + str(tenant['id']) + ","
add_values += " " + str(energy_category_id) + ","
add_values += "'" + current_datetime_utc.isoformat()[0:19] + "',"
add_values += str(billing_dict[current_datetime_utc][energy_category_id]) + "), "
print("add_values:" + add_values)
# trim ", " at the end of string and then execute
cursor_billing_db.execute(add_values[:-2])
cnx_billing_db.commit()
except Exception as e:
logger.error("Error in step 6 of tenant_billing_input_category " + str(e))
# break the for tenant loop
break
# end of for tenant loop
if cnx_system_db:
cnx_system_db.close()
if cursor_system_db:
cursor_system_db.close()
if cnx_energy_db:
cnx_energy_db.close()
if cursor_energy_db:
cursor_energy_db.close()
if cnx_billing_db:
cnx_billing_db.close()
if cursor_billing_db:
cursor_billing_db.close()
print("go to sleep 300 seconds...")
time.sleep(300)
print("wake from sleep, and continue to work...")
# end of the outermost while loop