Conditions | 151 |
Total Lines | 745 |
Code Lines | 569 |
Lines | 745 |
Ratio | 100 % |
Changes | 0 |
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
Complex classes like reports.shopfloorplan.Reporting.on_get() often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
1 | import re |
||
36 | @staticmethod |
||
37 | def on_get(req, resp): |
||
38 | if 'API-KEY' not in req.headers or \ |
||
39 | not isinstance(req.headers['API-KEY'], str) or \ |
||
40 | len(str.strip(req.headers['API-KEY'])) == 0: |
||
41 | access_control(req) |
||
42 | else: |
||
43 | api_key_control(req) |
||
44 | print(req.params) |
||
45 | shopfloor_id = req.params.get('shopfloorid') |
||
46 | shopfloor_uuid = req.params.get('shopflooruuid') |
||
47 | period_type = req.params.get('periodtype') |
||
48 | base_period_start_datetime_local = req.params.get('baseperiodstartdatetime') |
||
49 | base_period_end_datetime_local = req.params.get('baseperiodenddatetime') |
||
50 | reporting_period_start_datetime_local = req.params.get('reportingperiodstartdatetime') |
||
51 | reporting_period_end_datetime_local = req.params.get('reportingperiodenddatetime') |
||
52 | language = req.params.get('language') |
||
53 | quick_mode = req.params.get('quickmode') |
||
54 | |||
55 | ################################################################################################################ |
||
56 | # Step 1: valid parameters |
||
57 | ################################################################################################################ |
||
58 | if shopfloor_id is None and shopfloor_uuid is None: |
||
59 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
60 | title='API.BAD_REQUEST', |
||
61 | description='API.INVALID_SHOPFLOOR_ID') |
||
62 | |||
63 | if shopfloor_id is not None: |
||
64 | shopfloor_id = str.strip(shopfloor_id) |
||
65 | if not shopfloor_id.isdigit() or int(shopfloor_id) <= 0: |
||
66 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
67 | title='API.BAD_REQUEST', |
||
68 | description='API.INVALID_SHOPFLOOR_ID') |
||
69 | |||
70 | if shopfloor_uuid is not None: |
||
71 | regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I) |
||
72 | match = regex.match(str.strip(shopfloor_uuid)) |
||
73 | if not bool(match): |
||
74 | raise falcon.HTTPError(status=falcon.HTTP_400, |
||
75 | title='API.BAD_REQUEST', |
||
76 | description='API.INVALID_SHOPFLOOR_UUID') |
||
77 | |||
78 | if period_type is None: |
||
79 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
80 | description='API.INVALID_PERIOD_TYPE') |
||
81 | else: |
||
82 | period_type = str.strip(period_type) |
||
83 | if period_type not in ['hourly', 'daily', 'weekly', 'monthly', 'yearly']: |
||
84 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
85 | description='API.INVALID_PERIOD_TYPE') |
||
86 | |||
87 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
||
88 | if config.utc_offset[0] == '-': |
||
89 | timezone_offset = -timezone_offset |
||
90 | |||
91 | base_start_datetime_utc = None |
||
92 | if base_period_start_datetime_local is not None and len(str.strip(base_period_start_datetime_local)) > 0: |
||
93 | base_period_start_datetime_local = str.strip(base_period_start_datetime_local) |
||
94 | try: |
||
95 | base_start_datetime_utc = datetime.strptime(base_period_start_datetime_local, '%Y-%m-%dT%H:%M:%S') |
||
96 | except ValueError: |
||
97 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
98 | description="API.INVALID_BASE_PERIOD_START_DATETIME") |
||
99 | base_start_datetime_utc = \ |
||
100 | base_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
101 | # nomalize the start datetime |
||
102 | if config.minutes_to_count == 30 and base_start_datetime_utc.minute >= 30: |
||
103 | base_start_datetime_utc = base_start_datetime_utc.replace(minute=30, second=0, microsecond=0) |
||
104 | else: |
||
105 | base_start_datetime_utc = base_start_datetime_utc.replace(minute=0, second=0, microsecond=0) |
||
106 | |||
107 | base_end_datetime_utc = None |
||
108 | if base_period_end_datetime_local is not None and len(str.strip(base_period_end_datetime_local)) > 0: |
||
109 | base_period_end_datetime_local = str.strip(base_period_end_datetime_local) |
||
110 | try: |
||
111 | base_end_datetime_utc = datetime.strptime(base_period_end_datetime_local, '%Y-%m-%dT%H:%M:%S') |
||
112 | except ValueError: |
||
113 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
114 | description="API.INVALID_BASE_PERIOD_END_DATETIME") |
||
115 | base_end_datetime_utc = \ |
||
116 | base_end_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
117 | |||
118 | if base_start_datetime_utc is not None and base_end_datetime_utc is not None and \ |
||
119 | base_start_datetime_utc >= base_end_datetime_utc: |
||
120 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
121 | description='API.INVALID_BASE_PERIOD_END_DATETIME') |
||
122 | |||
123 | if reporting_period_start_datetime_local is None: |
||
124 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
125 | description="API.INVALID_REPORTING_PERIOD_START_DATETIME") |
||
126 | else: |
||
127 | reporting_period_start_datetime_local = str.strip(reporting_period_start_datetime_local) |
||
128 | try: |
||
129 | reporting_start_datetime_utc = datetime.strptime(reporting_period_start_datetime_local, |
||
130 | '%Y-%m-%dT%H:%M:%S') |
||
131 | except ValueError: |
||
132 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
133 | description="API.INVALID_REPORTING_PERIOD_START_DATETIME") |
||
134 | reporting_start_datetime_utc = \ |
||
135 | reporting_start_datetime_utc.replace(tzinfo=timezone.utc) - timedelta(minutes=timezone_offset) |
||
136 | # nomalize the start datetime |
||
137 | if config.minutes_to_count == 30 and reporting_start_datetime_utc.minute >= 30: |
||
138 | reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=30, second=0, microsecond=0) |
||
139 | else: |
||
140 | reporting_start_datetime_utc = reporting_start_datetime_utc.replace(minute=0, second=0, microsecond=0) |
||
141 | |||
142 | if reporting_period_end_datetime_local is None: |
||
143 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
144 | description="API.INVALID_REPORTING_PERIOD_END_DATETIME") |
||
145 | else: |
||
146 | reporting_period_end_datetime_local = str.strip(reporting_period_end_datetime_local) |
||
147 | try: |
||
148 | reporting_end_datetime_utc = datetime.strptime(reporting_period_end_datetime_local, |
||
149 | '%Y-%m-%dT%H:%M:%S').replace(tzinfo=timezone.utc) - \ |
||
150 | timedelta(minutes=timezone_offset) |
||
151 | except ValueError: |
||
152 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
153 | description="API.INVALID_REPORTING_PERIOD_END_DATETIME") |
||
154 | |||
155 | if reporting_start_datetime_utc >= reporting_end_datetime_utc: |
||
156 | raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', |
||
157 | description='API.INVALID_REPORTING_PERIOD_END_DATETIME') |
||
158 | |||
159 | # if turn quick mode on, do not return parameters data and excel file |
||
160 | is_quick_mode = False |
||
161 | if quick_mode is not None and \ |
||
162 | len(str.strip(quick_mode)) > 0 and \ |
||
163 | str.lower(str.strip(quick_mode)) in ('true', 't', 'on', 'yes', 'y'): |
||
164 | is_quick_mode = True |
||
165 | |||
166 | trans = utilities.get_translation(language) |
||
167 | trans.install() |
||
168 | _ = trans.gettext |
||
169 | |||
170 | ################################################################################################################ |
||
171 | # Step 2: query the shopfloor |
||
172 | ################################################################################################################ |
||
173 | cnx_system = mysql.connector.connect(**config.myems_system_db) |
||
174 | cursor_system = cnx_system.cursor() |
||
175 | |||
176 | cnx_energy = mysql.connector.connect(**config.myems_energy_db) |
||
177 | cursor_energy = cnx_energy.cursor() |
||
178 | |||
179 | cnx_energy_plan = mysql.connector.connect(**config.myems_energy_plan_db) |
||
180 | cursor_energy_plan = cnx_energy_plan.cursor() |
||
181 | |||
182 | cnx_historical = mysql.connector.connect(**config.myems_historical_db) |
||
183 | cursor_historical = cnx_historical.cursor() |
||
184 | |||
185 | if shopfloor_id is not None: |
||
186 | cursor_system.execute(" SELECT id, name, area, cost_center_id " |
||
187 | " FROM tbl_shopfloors " |
||
188 | " WHERE id = %s ", (shopfloor_id,)) |
||
189 | row_shopfloor = cursor_system.fetchone() |
||
190 | elif shopfloor_uuid is not None: |
||
191 | cursor_system.execute(" SELECT id, name, area, cost_center_id " |
||
192 | " FROM tbl_shopfloors " |
||
193 | " WHERE uuid = %s ", (shopfloor_uuid,)) |
||
194 | row_shopfloor = cursor_system.fetchone() |
||
195 | |||
196 | if row_shopfloor is None: |
||
197 | if cursor_system: |
||
198 | cursor_system.close() |
||
199 | if cnx_system: |
||
200 | cnx_system.close() |
||
201 | |||
202 | if cursor_energy: |
||
203 | cursor_energy.close() |
||
204 | if cnx_energy: |
||
205 | cnx_energy.close() |
||
206 | |||
207 | if cursor_energy_plan: |
||
208 | cursor_energy_plan.close() |
||
209 | if cnx_energy_plan: |
||
210 | cnx_energy_plan.close() |
||
211 | |||
212 | if cursor_historical: |
||
213 | cursor_historical.close() |
||
214 | if cnx_historical: |
||
215 | cnx_historical.close() |
||
216 | raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND', description='API.SHOPFLOOR_NOT_FOUND') |
||
217 | |||
218 | shopfloor = dict() |
||
219 | shopfloor['id'] = row_shopfloor[0] |
||
220 | shopfloor['name'] = row_shopfloor[1] |
||
221 | shopfloor['area'] = row_shopfloor[2] |
||
222 | shopfloor['cost_center_id'] = row_shopfloor[3] |
||
223 | |||
224 | ################################################################################################################ |
||
225 | # Step 3: query energy categories |
||
226 | ################################################################################################################ |
||
227 | energy_category_set = set() |
||
228 | # query energy categories in base period |
||
229 | cursor_energy.execute(" SELECT DISTINCT(energy_category_id) " |
||
230 | " FROM tbl_shopfloor_input_category_hourly " |
||
231 | " WHERE shopfloor_id = %s " |
||
232 | " AND start_datetime_utc >= %s " |
||
233 | " AND start_datetime_utc < %s ", |
||
234 | (shopfloor['id'], base_start_datetime_utc, base_end_datetime_utc)) |
||
235 | rows_energy_categories = cursor_energy.fetchall() |
||
236 | if rows_energy_categories is not None and len(rows_energy_categories) > 0: |
||
237 | for row_energy_category in rows_energy_categories: |
||
238 | energy_category_set.add(row_energy_category[0]) |
||
239 | |||
240 | # query energy categories in reporting period |
||
241 | cursor_energy.execute(" SELECT DISTINCT(energy_category_id) " |
||
242 | " FROM tbl_shopfloor_input_category_hourly " |
||
243 | " WHERE shopfloor_id = %s " |
||
244 | " AND start_datetime_utc >= %s " |
||
245 | " AND start_datetime_utc < %s ", |
||
246 | (shopfloor['id'], reporting_start_datetime_utc, reporting_end_datetime_utc)) |
||
247 | rows_energy_categories = cursor_energy.fetchall() |
||
248 | if rows_energy_categories is not None and len(rows_energy_categories) > 0: |
||
249 | for row_energy_category in rows_energy_categories: |
||
250 | energy_category_set.add(row_energy_category[0]) |
||
251 | |||
252 | # query all energy categories in base period and reporting period |
||
253 | cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e " |
||
254 | " FROM tbl_energy_categories " |
||
255 | " ORDER BY id ", ) |
||
256 | rows_energy_categories = cursor_system.fetchall() |
||
257 | if rows_energy_categories is None or len(rows_energy_categories) == 0: |
||
258 | if cursor_system: |
||
259 | cursor_system.close() |
||
260 | if cnx_system: |
||
261 | cnx_system.close() |
||
262 | |||
263 | if cursor_energy: |
||
264 | cursor_energy.close() |
||
265 | if cnx_energy: |
||
266 | cnx_energy.close() |
||
267 | |||
268 | if cursor_energy_plan: |
||
269 | cursor_energy_plan.close() |
||
270 | if cnx_energy_plan: |
||
271 | cnx_energy_plan.close() |
||
272 | |||
273 | if cursor_historical: |
||
274 | cursor_historical.close() |
||
275 | if cnx_historical: |
||
276 | cnx_historical.close() |
||
277 | raise falcon.HTTPError(status=falcon.HTTP_404, |
||
278 | title='API.NOT_FOUND', |
||
279 | description='API.ENERGY_CATEGORY_NOT_FOUND') |
||
280 | energy_category_dict = dict() |
||
281 | for row_energy_category in rows_energy_categories: |
||
282 | if row_energy_category[0] in energy_category_set: |
||
283 | energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1], |
||
284 | "unit_of_measure": row_energy_category[2], |
||
285 | "kgce": row_energy_category[3], |
||
286 | "kgco2e": row_energy_category[4]} |
||
287 | |||
288 | ################################################################################################################ |
||
289 | # Step 4: query associated sensors |
||
290 | ################################################################################################################ |
||
291 | point_list = list() |
||
292 | cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type " |
||
293 | " FROM tbl_shopfloors st, tbl_sensors se, tbl_shopfloors_sensors ss, " |
||
294 | " tbl_points p, tbl_sensors_points sp " |
||
295 | " WHERE st.id = %s AND st.id = ss.shopfloor_id AND ss.sensor_id = se.id " |
||
296 | " AND se.id = sp.sensor_id AND sp.point_id = p.id " |
||
297 | " ORDER BY p.id ", (shopfloor['id'],)) |
||
298 | rows_points = cursor_system.fetchall() |
||
299 | if rows_points is not None and len(rows_points) > 0: |
||
300 | for row in rows_points: |
||
301 | point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]}) |
||
302 | |||
303 | ################################################################################################################ |
||
304 | # Step 5: query associated points |
||
305 | ################################################################################################################ |
||
306 | cursor_system.execute(" SELECT p.id, p.name, p.units, p.object_type " |
||
307 | " FROM tbl_shopfloors s, tbl_shopfloors_points sp, tbl_points p " |
||
308 | " WHERE s.id = %s AND s.id = sp.shopfloor_id AND sp.point_id = p.id " |
||
309 | " ORDER BY p.id ", (shopfloor['id'],)) |
||
310 | rows_points = cursor_system.fetchall() |
||
311 | if rows_points is not None and len(rows_points) > 0: |
||
312 | for row in rows_points: |
||
313 | point_list.append({"id": row[0], "name": row[1], "units": row[2], "object_type": row[3]}) |
||
314 | |||
315 | ################################################################################################################ |
||
316 | # Step 6: query base period energy saving |
||
317 | ################################################################################################################ |
||
318 | base = dict() |
||
319 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
320 | for energy_category_id in energy_category_set: |
||
321 | kgce = energy_category_dict[energy_category_id]['kgce'] |
||
322 | kgco2e = energy_category_dict[energy_category_id]['kgco2e'] |
||
323 | |||
324 | base[energy_category_id] = dict() |
||
325 | base[energy_category_id]['timestamps'] = list() |
||
326 | base[energy_category_id]['values_plan'] = list() |
||
327 | base[energy_category_id]['values_actual'] = list() |
||
328 | base[energy_category_id]['values_saving'] = list() |
||
329 | base[energy_category_id]['subtotal_plan'] = Decimal(0.0) |
||
330 | base[energy_category_id]['subtotal_actual'] = Decimal(0.0) |
||
331 | base[energy_category_id]['subtotal_saving'] = Decimal(0.0) |
||
332 | base[energy_category_id]['subtotal_in_kgce_plan'] = Decimal(0.0) |
||
333 | base[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0) |
||
334 | base[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0) |
||
335 | base[energy_category_id]['subtotal_in_kgco2e_plan'] = Decimal(0.0) |
||
336 | base[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0) |
||
337 | base[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0) |
||
338 | # query base period's energy plan |
||
339 | cursor_energy_plan.execute(" SELECT start_datetime_utc, actual_value " |
||
340 | " FROM tbl_shopfloor_input_category_hourly " |
||
341 | " WHERE shopfloor_id = %s " |
||
342 | " AND energy_category_id = %s " |
||
343 | " AND start_datetime_utc >= %s " |
||
344 | " AND start_datetime_utc < %s " |
||
345 | " ORDER BY start_datetime_utc ", |
||
346 | (shopfloor['id'], |
||
347 | energy_category_id, |
||
348 | base_start_datetime_utc, |
||
349 | base_end_datetime_utc)) |
||
350 | rows_shopfloor_hourly = cursor_energy_plan.fetchall() |
||
351 | |||
352 | rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly, |
||
353 | base_start_datetime_utc, |
||
354 | base_end_datetime_utc, |
||
355 | period_type) |
||
356 | for row_shopfloor_periodically in rows_shopfloor_periodically: |
||
357 | current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
358 | timedelta(minutes=timezone_offset) |
||
359 | if period_type == 'hourly': |
||
360 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
361 | elif period_type == 'daily': |
||
362 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
363 | elif period_type == 'weekly': |
||
364 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
365 | elif period_type == 'monthly': |
||
366 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
367 | elif period_type == 'yearly': |
||
368 | current_datetime = current_datetime_local.strftime('%Y') |
||
369 | |||
370 | plan_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \ |
||
371 | else row_shopfloor_periodically[1] |
||
372 | base[energy_category_id]['timestamps'].append(current_datetime) |
||
373 | base[energy_category_id]['values_plan'].append(plan_value) |
||
374 | base[energy_category_id]['subtotal_plan'] += plan_value |
||
375 | base[energy_category_id]['subtotal_in_kgce_plan'] += plan_value * kgce |
||
376 | base[energy_category_id]['subtotal_in_kgco2e_plan'] += plan_value * kgco2e |
||
377 | |||
378 | # query base period's energy actual |
||
379 | cursor_energy.execute(" SELECT start_datetime_utc, actual_value " |
||
380 | " FROM tbl_shopfloor_input_category_hourly " |
||
381 | " WHERE shopfloor_id = %s " |
||
382 | " AND energy_category_id = %s " |
||
383 | " AND start_datetime_utc >= %s " |
||
384 | " AND start_datetime_utc < %s " |
||
385 | " ORDER BY start_datetime_utc ", |
||
386 | (shopfloor['id'], |
||
387 | energy_category_id, |
||
388 | base_start_datetime_utc, |
||
389 | base_end_datetime_utc)) |
||
390 | rows_shopfloor_hourly = cursor_energy.fetchall() |
||
391 | |||
392 | rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly, |
||
393 | base_start_datetime_utc, |
||
394 | base_end_datetime_utc, |
||
395 | period_type) |
||
396 | for row_shopfloor_periodically in rows_shopfloor_periodically: |
||
397 | current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
398 | timedelta(minutes=timezone_offset) |
||
399 | if period_type == 'hourly': |
||
400 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
401 | elif period_type == 'daily': |
||
402 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
403 | elif period_type == 'weekly': |
||
404 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
405 | elif period_type == 'monthly': |
||
406 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
407 | elif period_type == 'yearly': |
||
408 | current_datetime = current_datetime_local.strftime('%Y') |
||
409 | |||
410 | actual_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \ |
||
411 | else row_shopfloor_periodically[1] |
||
412 | base[energy_category_id]['values_actual'].append(actual_value) |
||
413 | base[energy_category_id]['subtotal_actual'] += actual_value |
||
414 | base[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce |
||
415 | base[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e |
||
416 | |||
417 | # calculate base period's energy savings |
||
418 | for i in range(len(base[energy_category_id]['values_plan'])): |
||
419 | base[energy_category_id]['values_saving'].append( |
||
420 | base[energy_category_id]['values_plan'][i] - |
||
421 | base[energy_category_id]['values_actual'][i]) |
||
422 | |||
423 | base[energy_category_id]['subtotal_saving'] = \ |
||
424 | base[energy_category_id]['subtotal_plan'] - \ |
||
425 | base[energy_category_id]['subtotal_actual'] |
||
426 | base[energy_category_id]['subtotal_in_kgce_saving'] = \ |
||
427 | base[energy_category_id]['subtotal_in_kgce_plan'] - \ |
||
428 | base[energy_category_id]['subtotal_in_kgce_actual'] |
||
429 | base[energy_category_id]['subtotal_in_kgco2e_saving'] = \ |
||
430 | base[energy_category_id]['subtotal_in_kgco2e_plan'] - \ |
||
431 | base[energy_category_id]['subtotal_in_kgco2e_actual'] |
||
432 | ################################################################################################################ |
||
433 | # Step 7: query reporting period energy saving |
||
434 | ################################################################################################################ |
||
435 | reporting = dict() |
||
436 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
437 | for energy_category_id in energy_category_set: |
||
438 | kgce = energy_category_dict[energy_category_id]['kgce'] |
||
439 | kgco2e = energy_category_dict[energy_category_id]['kgco2e'] |
||
440 | |||
441 | reporting[energy_category_id] = dict() |
||
442 | reporting[energy_category_id]['timestamps'] = list() |
||
443 | reporting[energy_category_id]['values_plan'] = list() |
||
444 | reporting[energy_category_id]['values_actual'] = list() |
||
445 | reporting[energy_category_id]['values_saving'] = list() |
||
446 | reporting[energy_category_id]['subtotal_plan'] = Decimal(0.0) |
||
447 | reporting[energy_category_id]['subtotal_actual'] = Decimal(0.0) |
||
448 | reporting[energy_category_id]['subtotal_saving'] = Decimal(0.0) |
||
449 | reporting[energy_category_id]['subtotal_in_kgce_plan'] = Decimal(0.0) |
||
450 | reporting[energy_category_id]['subtotal_in_kgce_actual'] = Decimal(0.0) |
||
451 | reporting[energy_category_id]['subtotal_in_kgce_saving'] = Decimal(0.0) |
||
452 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] = Decimal(0.0) |
||
453 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] = Decimal(0.0) |
||
454 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = Decimal(0.0) |
||
455 | # query reporting period's energy plan |
||
456 | cursor_energy_plan.execute(" SELECT start_datetime_utc, actual_value " |
||
457 | " FROM tbl_shopfloor_input_category_hourly " |
||
458 | " WHERE shopfloor_id = %s " |
||
459 | " AND energy_category_id = %s " |
||
460 | " AND start_datetime_utc >= %s " |
||
461 | " AND start_datetime_utc < %s " |
||
462 | " ORDER BY start_datetime_utc ", |
||
463 | (shopfloor['id'], |
||
464 | energy_category_id, |
||
465 | reporting_start_datetime_utc, |
||
466 | reporting_end_datetime_utc)) |
||
467 | rows_shopfloor_hourly = cursor_energy_plan.fetchall() |
||
468 | |||
469 | rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly, |
||
470 | reporting_start_datetime_utc, |
||
471 | reporting_end_datetime_utc, |
||
472 | period_type) |
||
473 | for row_shopfloor_periodically in rows_shopfloor_periodically: |
||
474 | current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
475 | timedelta(minutes=timezone_offset) |
||
476 | if period_type == 'hourly': |
||
477 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
478 | elif period_type == 'daily': |
||
479 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
480 | elif period_type == 'weekly': |
||
481 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
482 | elif period_type == 'monthly': |
||
483 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
484 | elif period_type == 'yearly': |
||
485 | current_datetime = current_datetime_local.strftime('%Y') |
||
486 | |||
487 | plan_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \ |
||
488 | else row_shopfloor_periodically[1] |
||
489 | reporting[energy_category_id]['timestamps'].append(current_datetime) |
||
490 | reporting[energy_category_id]['values_plan'].append(plan_value) |
||
491 | reporting[energy_category_id]['subtotal_plan'] += plan_value |
||
492 | reporting[energy_category_id]['subtotal_in_kgce_plan'] += plan_value * kgce |
||
493 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] += plan_value * kgco2e |
||
494 | |||
495 | # query reporting period's energy actual |
||
496 | cursor_energy.execute(" SELECT start_datetime_utc, actual_value " |
||
497 | " FROM tbl_shopfloor_input_category_hourly " |
||
498 | " WHERE shopfloor_id = %s " |
||
499 | " AND energy_category_id = %s " |
||
500 | " AND start_datetime_utc >= %s " |
||
501 | " AND start_datetime_utc < %s " |
||
502 | " ORDER BY start_datetime_utc ", |
||
503 | (shopfloor['id'], |
||
504 | energy_category_id, |
||
505 | reporting_start_datetime_utc, |
||
506 | reporting_end_datetime_utc)) |
||
507 | rows_shopfloor_hourly = cursor_energy.fetchall() |
||
508 | |||
509 | rows_shopfloor_periodically = utilities.aggregate_hourly_data_by_period(rows_shopfloor_hourly, |
||
510 | reporting_start_datetime_utc, |
||
511 | reporting_end_datetime_utc, |
||
512 | period_type) |
||
513 | for row_shopfloor_periodically in rows_shopfloor_periodically: |
||
514 | current_datetime_local = row_shopfloor_periodically[0].replace(tzinfo=timezone.utc) + \ |
||
515 | timedelta(minutes=timezone_offset) |
||
516 | if period_type == 'hourly': |
||
517 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
518 | elif period_type == 'daily': |
||
519 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
520 | elif period_type == 'weekly': |
||
521 | current_datetime = current_datetime_local.strftime('%Y-%m-%d') |
||
522 | elif period_type == 'monthly': |
||
523 | current_datetime = current_datetime_local.strftime('%Y-%m') |
||
524 | elif period_type == 'yearly': |
||
525 | current_datetime = current_datetime_local.strftime('%Y') |
||
526 | |||
527 | actual_value = Decimal(0.0) if row_shopfloor_periodically[1] is None \ |
||
528 | else row_shopfloor_periodically[1] |
||
529 | reporting[energy_category_id]['values_actual'].append(actual_value) |
||
530 | reporting[energy_category_id]['subtotal_actual'] += actual_value |
||
531 | reporting[energy_category_id]['subtotal_in_kgce_actual'] += actual_value * kgce |
||
532 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] += actual_value * kgco2e |
||
533 | |||
534 | # calculate reporting period's energy savings |
||
535 | for i in range(len(reporting[energy_category_id]['values_plan'])): |
||
536 | reporting[energy_category_id]['values_saving'].append( |
||
537 | reporting[energy_category_id]['values_plan'][i] - |
||
538 | reporting[energy_category_id]['values_actual'][i]) |
||
539 | |||
540 | reporting[energy_category_id]['subtotal_saving'] = \ |
||
541 | reporting[energy_category_id]['subtotal_plan'] - \ |
||
542 | reporting[energy_category_id]['subtotal_actual'] |
||
543 | reporting[energy_category_id]['subtotal_in_kgce_saving'] = \ |
||
544 | reporting[energy_category_id]['subtotal_in_kgce_plan'] - \ |
||
545 | reporting[energy_category_id]['subtotal_in_kgce_actual'] |
||
546 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] = \ |
||
547 | reporting[energy_category_id]['subtotal_in_kgco2e_plan'] - \ |
||
548 | reporting[energy_category_id]['subtotal_in_kgco2e_actual'] |
||
549 | ################################################################################################################ |
||
550 | # Step 8: query tariff data |
||
551 | ################################################################################################################ |
||
552 | parameters_data = dict() |
||
553 | parameters_data['names'] = list() |
||
554 | parameters_data['timestamps'] = list() |
||
555 | parameters_data['values'] = list() |
||
556 | if config.is_tariff_appended and energy_category_set is not None and len(energy_category_set) > 0 \ |
||
557 | and not is_quick_mode: |
||
558 | for energy_category_id in energy_category_set: |
||
559 | energy_category_tariff_dict = utilities.get_energy_category_tariffs(shopfloor['cost_center_id'], |
||
560 | energy_category_id, |
||
561 | reporting_start_datetime_utc, |
||
562 | reporting_end_datetime_utc) |
||
563 | tariff_timestamp_list = list() |
||
564 | tariff_value_list = list() |
||
565 | for k, v in energy_category_tariff_dict.items(): |
||
566 | # convert k from utc to local |
||
567 | k = k + timedelta(minutes=timezone_offset) |
||
568 | tariff_timestamp_list.append(k.isoformat()[0:19][0:19]) |
||
569 | tariff_value_list.append(v) |
||
570 | |||
571 | parameters_data['names'].append(_('Tariff') + '-' + energy_category_dict[energy_category_id]['name']) |
||
572 | parameters_data['timestamps'].append(tariff_timestamp_list) |
||
573 | parameters_data['values'].append(tariff_value_list) |
||
574 | |||
575 | ################################################################################################################ |
||
576 | # Step 9: query associated sensors and points data |
||
577 | ################################################################################################################ |
||
578 | if not is_quick_mode: |
||
579 | for point in point_list: |
||
580 | point_values = [] |
||
581 | point_timestamps = [] |
||
582 | if point['object_type'] == 'ENERGY_VALUE': |
||
583 | query = (" SELECT utc_date_time, actual_value " |
||
584 | " FROM tbl_energy_value " |
||
585 | " WHERE point_id = %s " |
||
586 | " AND utc_date_time BETWEEN %s AND %s " |
||
587 | " ORDER BY utc_date_time ") |
||
588 | cursor_historical.execute(query, (point['id'], |
||
589 | reporting_start_datetime_utc, |
||
590 | reporting_end_datetime_utc)) |
||
591 | rows = cursor_historical.fetchall() |
||
592 | |||
593 | if rows is not None and len(rows) > 0: |
||
594 | for row in rows: |
||
595 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
596 | timedelta(minutes=timezone_offset) |
||
597 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
598 | point_timestamps.append(current_datetime) |
||
599 | point_values.append(row[1]) |
||
600 | elif point['object_type'] == 'ANALOG_VALUE': |
||
601 | query = (" SELECT utc_date_time, actual_value " |
||
602 | " FROM tbl_analog_value " |
||
603 | " WHERE point_id = %s " |
||
604 | " AND utc_date_time BETWEEN %s AND %s " |
||
605 | " ORDER BY utc_date_time ") |
||
606 | cursor_historical.execute(query, (point['id'], |
||
607 | reporting_start_datetime_utc, |
||
608 | reporting_end_datetime_utc)) |
||
609 | rows = cursor_historical.fetchall() |
||
610 | |||
611 | if rows is not None and len(rows) > 0: |
||
612 | for row in rows: |
||
613 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
614 | timedelta(minutes=timezone_offset) |
||
615 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
616 | point_timestamps.append(current_datetime) |
||
617 | point_values.append(row[1]) |
||
618 | elif point['object_type'] == 'DIGITAL_VALUE': |
||
619 | query = (" SELECT utc_date_time, actual_value " |
||
620 | " FROM tbl_digital_value " |
||
621 | " WHERE point_id = %s " |
||
622 | " AND utc_date_time BETWEEN %s AND %s " |
||
623 | " ORDER BY utc_date_time ") |
||
624 | cursor_historical.execute(query, (point['id'], |
||
625 | reporting_start_datetime_utc, |
||
626 | reporting_end_datetime_utc)) |
||
627 | rows = cursor_historical.fetchall() |
||
628 | |||
629 | if rows is not None and len(rows) > 0: |
||
630 | for row in rows: |
||
631 | current_datetime_local = row[0].replace(tzinfo=timezone.utc) + \ |
||
632 | timedelta(minutes=timezone_offset) |
||
633 | current_datetime = current_datetime_local.strftime('%Y-%m-%dT%H:%M:%S') |
||
634 | point_timestamps.append(current_datetime) |
||
635 | point_values.append(row[1]) |
||
636 | |||
637 | parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')') |
||
638 | parameters_data['timestamps'].append(point_timestamps) |
||
639 | parameters_data['values'].append(point_values) |
||
640 | |||
641 | ################################################################################################################ |
||
642 | # Step 10: construct the report |
||
643 | ################################################################################################################ |
||
644 | if cursor_system: |
||
645 | cursor_system.close() |
||
646 | if cnx_system: |
||
647 | cnx_system.close() |
||
648 | |||
649 | if cursor_energy: |
||
650 | cursor_energy.close() |
||
651 | if cnx_energy: |
||
652 | cnx_energy.close() |
||
653 | |||
654 | if cursor_energy_plan: |
||
655 | cursor_energy_plan.close() |
||
656 | if cnx_energy_plan: |
||
657 | cnx_energy_plan.close() |
||
658 | |||
659 | if cursor_historical: |
||
660 | cursor_historical.close() |
||
661 | if cnx_historical: |
||
662 | cnx_historical.close() |
||
663 | |||
664 | result = dict() |
||
665 | |||
666 | result['shopfloor'] = dict() |
||
667 | result['shopfloor']['name'] = shopfloor['name'] |
||
668 | result['shopfloor']['area'] = shopfloor['area'] |
||
669 | |||
670 | result['base_period'] = dict() |
||
671 | result['base_period']['names'] = list() |
||
672 | result['base_period']['units'] = list() |
||
673 | result['base_period']['timestamps'] = list() |
||
674 | result['base_period']['values_saving'] = list() |
||
675 | result['base_period']['subtotals_saving'] = list() |
||
676 | result['base_period']['subtotals_in_kgce_saving'] = list() |
||
677 | result['base_period']['subtotals_in_kgco2e_saving'] = list() |
||
678 | result['base_period']['total_in_kgce_saving'] = Decimal(0.0) |
||
679 | result['base_period']['total_in_kgco2e_saving'] = Decimal(0.0) |
||
680 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
681 | for energy_category_id in energy_category_set: |
||
682 | result['base_period']['names'].append(energy_category_dict[energy_category_id]['name']) |
||
683 | result['base_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure']) |
||
684 | result['base_period']['timestamps'].append(base[energy_category_id]['timestamps']) |
||
685 | result['base_period']['values_saving'].append(base[energy_category_id]['values_saving']) |
||
686 | result['base_period']['subtotals_saving'].append(base[energy_category_id]['subtotal_saving']) |
||
687 | result['base_period']['subtotals_in_kgce_saving'].append( |
||
688 | base[energy_category_id]['subtotal_in_kgce_saving']) |
||
689 | result['base_period']['subtotals_in_kgco2e_saving'].append( |
||
690 | base[energy_category_id]['subtotal_in_kgco2e_saving']) |
||
691 | result['base_period']['total_in_kgce_saving'] += base[energy_category_id]['subtotal_in_kgce_saving'] |
||
692 | result['base_period']['total_in_kgco2e_saving'] += base[energy_category_id]['subtotal_in_kgco2e_saving'] |
||
693 | |||
694 | result['reporting_period'] = dict() |
||
695 | result['reporting_period']['names'] = list() |
||
696 | result['reporting_period']['energy_category_ids'] = list() |
||
697 | result['reporting_period']['units'] = list() |
||
698 | result['reporting_period']['timestamps'] = list() |
||
699 | result['reporting_period']['values_saving'] = list() |
||
700 | result['reporting_period']['rates_saving'] = list() |
||
701 | result['reporting_period']['subtotals_saving'] = list() |
||
702 | result['reporting_period']['subtotals_in_kgce_saving'] = list() |
||
703 | result['reporting_period']['subtotals_in_kgco2e_saving'] = list() |
||
704 | result['reporting_period']['subtotals_per_unit_area_saving'] = list() |
||
705 | result['reporting_period']['increment_rates_saving'] = list() |
||
706 | result['reporting_period']['total_in_kgce_saving'] = Decimal(0.0) |
||
707 | result['reporting_period']['total_in_kgco2e_saving'] = Decimal(0.0) |
||
708 | result['reporting_period']['increment_rate_in_kgce_saving'] = Decimal(0.0) |
||
709 | result['reporting_period']['increment_rate_in_kgco2e_saving'] = Decimal(0.0) |
||
710 | |||
711 | if energy_category_set is not None and len(energy_category_set) > 0: |
||
712 | for energy_category_id in energy_category_set: |
||
713 | result['reporting_period']['names'].append(energy_category_dict[energy_category_id]['name']) |
||
714 | result['reporting_period']['energy_category_ids'].append(energy_category_id) |
||
715 | result['reporting_period']['units'].append(energy_category_dict[energy_category_id]['unit_of_measure']) |
||
716 | result['reporting_period']['timestamps'].append(reporting[energy_category_id]['timestamps']) |
||
717 | result['reporting_period']['values_saving'].append(reporting[energy_category_id]['values_saving']) |
||
718 | result['reporting_period']['subtotals_saving'].append(reporting[energy_category_id]['subtotal_saving']) |
||
719 | result['reporting_period']['subtotals_in_kgce_saving'].append( |
||
720 | reporting[energy_category_id]['subtotal_in_kgce_saving']) |
||
721 | result['reporting_period']['subtotals_in_kgco2e_saving'].append( |
||
722 | reporting[energy_category_id]['subtotal_in_kgco2e_saving']) |
||
723 | result['reporting_period']['subtotals_per_unit_area_saving'].append( |
||
724 | reporting[energy_category_id]['subtotal_saving'] / shopfloor['area'] |
||
725 | if shopfloor['area'] > Decimal(0.0) else None) |
||
726 | result['reporting_period']['increment_rates_saving'].append( |
||
727 | (reporting[energy_category_id]['subtotal_saving'] - base[energy_category_id]['subtotal_saving']) / |
||
728 | base[energy_category_id]['subtotal_saving'] |
||
729 | if base[energy_category_id]['subtotal_saving'] != Decimal(0.0) else None) |
||
730 | result['reporting_period']['total_in_kgce_saving'] += \ |
||
731 | reporting[energy_category_id]['subtotal_in_kgce_saving'] |
||
732 | result['reporting_period']['total_in_kgco2e_saving'] += \ |
||
733 | reporting[energy_category_id]['subtotal_in_kgco2e_saving'] |
||
734 | |||
735 | rate = list() |
||
736 | for index, value in enumerate(reporting[energy_category_id]['values_saving']): |
||
737 | if index < len(base[energy_category_id]['values_saving']) \ |
||
738 | and base[energy_category_id]['values_saving'][index] != 0 and value != 0: |
||
739 | rate.append((value - base[energy_category_id]['values_saving'][index]) |
||
740 | / base[energy_category_id]['values_saving'][index]) |
||
741 | else: |
||
742 | rate.append(None) |
||
743 | result['reporting_period']['rates_saving'].append(rate) |
||
744 | |||
745 | result['reporting_period']['total_in_kgco2e_per_unit_area_saving'] = \ |
||
746 | result['reporting_period']['total_in_kgce_saving'] / shopfloor['area'] \ |
||
747 | if shopfloor['area'] > 0.0 else None |
||
748 | |||
749 | result['reporting_period']['increment_rate_in_kgce_saving'] = \ |
||
750 | (result['reporting_period']['total_in_kgce_saving'] - result['base_period']['total_in_kgce_saving']) / \ |
||
751 | result['base_period']['total_in_kgce_saving'] \ |
||
752 | if result['base_period']['total_in_kgce_saving'] != Decimal(0.0) else None |
||
753 | |||
754 | result['reporting_period']['total_in_kgce_per_unit_area_saving'] = \ |
||
755 | result['reporting_period']['total_in_kgco2e_saving'] / shopfloor['area'] \ |
||
756 | if shopfloor['area'] > Decimal(0.0) else None |
||
757 | |||
758 | result['reporting_period']['increment_rate_in_kgco2e_saving'] = \ |
||
759 | (result['reporting_period']['total_in_kgco2e_saving'] - result['base_period']['total_in_kgco2e_saving']) / \ |
||
760 | result['base_period']['total_in_kgco2e_saving'] \ |
||
761 | if result['base_period']['total_in_kgco2e_saving'] != Decimal(0.0) else None |
||
762 | |||
763 | result['parameters'] = { |
||
764 | "names": parameters_data['names'], |
||
765 | "timestamps": parameters_data['timestamps'], |
||
766 | "values": parameters_data['values'] |
||
767 | } |
||
768 | |||
769 | result['excel_bytes_base64'] = None |
||
770 | if not is_quick_mode: |
||
771 | result['excel_bytes_base64'] = excelexporters.shopfloorplan.export(result, |
||
772 | shopfloor['name'], |
||
773 | base_period_start_datetime_local, |
||
774 | base_period_end_datetime_local, |
||
775 | reporting_period_start_datetime_local, |
||
776 | reporting_period_end_datetime_local, |
||
777 | period_type, |
||
778 | language) |
||
779 | |||
780 | resp.text = json.dumps(result) |
||
781 |