offlinemeter.calculate_hourly() - Code Metrics - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

offlinemeter.calculate_hourly() F
last analyzed 2025-06-17 15:42 UTC

↳ Parent: offlinemeter

Complexity

Conditions

Size

Total Lines	296
Code Lines	217

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	217
dl	0
loc	296
rs	0
c	0
b	0
f	0
cc	62
nop	1

How to fix Long Method Complexity

import time
from datetime import datetime, timedelta
from decimal import Decimal

import mysql.connector
from openpyxl import load_workbook

import config


################################################################################################################
# PROCEDURES:
# STEP 1: get all 'new' offline meter files
# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
# STEP 3: insert or update energy data to table offline meter hourly in energy database
# STEP 4: update file status to 'done' or 'error'
################################################################################################################


def calculate_hourly(logger):
    while True:
        # the outermost while loop to reconnect server if there is a connection error
        ################################################################################################################
        # STEP 1: get all 'new' offline meter files
        ################################################################################################################
        cnx = None
        cursor = None
        try:
            cnx = mysql.connector.connect(**config.myems_historical_db)
            cursor = cnx.cursor()
        except Exception as e:
            logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()
            # sleep several minutes and continue the outer loop to reconnect the database
            print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        print("Connected to MyEMS Historical Database")

        print("Getting all new offline meter files")
        try:
            query = (" SELECT id, file_name, file_object "
                     " FROM tbl_offline_meter_files "
                     " WHERE status = 'new' "
                     " ORDER BY id ")

            cursor.execute(query, )
            rows_files = cursor.fetchall()
        except Exception as e:
            logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
            time.sleep(60)
            continue
        finally:
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()

        excel_file_list = list()
        if rows_files is not None and len(rows_files) > 0:

            for row_file in rows_files:
                excel_file_list.append({"id": row_file[0],
                                        "name": row_file[1],
                                        "file_object": row_file[2]})
        else:
            print("there isn't any new files found, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        ################################################################################################################
        # STEP 2: for each new files, dump file object to local file and then load workbook from the local file
        ################################################################################################################
        for excel_file in excel_file_list:
            print("read data from offline meter file" + excel_file['name'])
            is_valid_file = True
            fw = None
            try:
                fw = open("myems-normalization.blob", 'wb')
                fw.write(excel_file['file_object'])
                fw.close()
            except Exception as e:
                logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
                if fw:
                    fw.close()
                # mark as invalid file
                is_valid_file = False

            fr = None
            wb = None
            try:
                fr = open("myems-normalization.blob", 'rb')
                wb = load_workbook(fr, data_only=True)
                fr.close()
            except Exception as e:
                logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
                if fr:
                    fr.close()
                # mark as invalid file
                is_valid_file = False

            energy_data_list = list()
            # grab the active worksheet

            if is_valid_file:
                ws = wb.active

                # get timezone offset in minutes, this value will be returned to client
                timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
                if config.utc_offset[0] == '-':
                    timezone_offset = -timezone_offset

                for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
                    offline_meter_data = dict()
                    offline_meter_data['offline_meter_id'] = None
                    offline_meter_data['offline_meter_name'] = None
                    offline_meter_data['data'] = dict()
                    col_num = 0

                    for cell in row:
                        col_num += 1
                        print(cell.value)
                        if col_num == 1:
                            # get offline meter ID
                            if cell.value is not None:
                                offline_meter_data['offline_meter_id'] = cell.value
                            else:
                                break
                        elif col_num == 2:
                            # get offline meter name
                            if cell.value is None:
                                break
                            else:
                                offline_meter_data['offline_meter_name'] = cell.value
                        elif col_num > 3:
                            # get date of the cell
                            try:
                                start_datetime_local = datetime(year=ws['A2'].value,
                                                                month=ws['B2'].value,
                                                                day=col_num - 3)
                            except ValueError:
                                # invalid date and go to next cell in this row until reach max_col
                                continue

                            start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)

                            if cell.value is None:
                                # if the cell is empty then stop at that day
                                break
                            else:
                                offline_meter_data['data'][start_datetime_utc] = Decimal(cell.value)

                    if len(offline_meter_data['data']) > 0:
                        print("offline_meter_data:" + str(offline_meter_data))
                        energy_data_list.append(offline_meter_data)

            ############################################################################################################
            # STEP 3: insert or update energy data to table offline meter hourly in energy database
            ############################################################################################################
            print("to valid offline meter id in excel file...")
            if len(energy_data_list) == 0:
                print("Could not find any offline meters in the file...")
                print("and go to process the next file...")
                is_valid_file = False
            else:
                try:
                    cnx = mysql.connector.connect(**config.myems_system_db)
                    cursor = cnx.cursor()
                except Exception as e:
                    logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()
                    time.sleep(60)
                    continue

                try:
                    cursor.execute(" SELECT id, name, hourly_low_limit, hourly_high_limit"
                                   " FROM tbl_offline_meters ")
                    rows_offline_meters = cursor.fetchall()
                except Exception as e:
                    logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                    time.sleep(60)
                    continue
                finally:
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()

                if rows_offline_meters is None or len(rows_offline_meters) == 0:

                    print("Could not find any offline meters in the MyEMS System Database...")
                    time.sleep(60)
                    continue
                else:
                    offline_meter_id_set = set()
                    for row_offline_meter in rows_offline_meters:
                        # valid offline meter id in excel file
                        offline_meter_id_set.add(row_offline_meter[0])

                    for energy_data_item in energy_data_list:
                        if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
                            is_valid_file = False
                            break

                        for row_offline_meter in rows_offline_meters:
                            if row_offline_meter[0] == energy_data_item['offline_meter_id']:
                                for key in energy_data_item['data']:
                                    if row_offline_meter[2] > (energy_data_item['data'][key]/24):
                                        is_valid_file = False
                                        break
                                    elif row_offline_meter[3] < (energy_data_item['data'][key]/24):
                                        is_valid_file = False
                                        break
                                break

                if is_valid_file:
                    ####################################################################################################
                    # delete possibly exists offline meter hourly data in myems energy database,
                    # and then insert new offline meter hourly data
                    ####################################################################################################
                    try:
                        cnx = mysql.connector.connect(**config.myems_energy_db)
                        cursor = cnx.cursor()
                    except Exception as e:
                        logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()
                        time.sleep(60)
                        continue

                    try:
                        for energy_data_item in energy_data_list:
                            offline_meter_id = energy_data_item['offline_meter_id']
                            print(energy_data_item['data'].items())
                            for start_datetime_utc, daily_value in energy_data_item['data'].items():
                                end_datetime_utc = start_datetime_utc + timedelta(hours=24)
                                actual_value = \
                                    daily_value / (Decimal(24) * Decimal(60) / Decimal(config.minutes_to_count))
                                cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
                                               " WHERE offline_meter_id = %s "
                                               "       AND start_datetime_utc >= %s "
                                               "       AND start_datetime_utc < %s ",
                                               (offline_meter_id,
                                                start_datetime_utc.isoformat()[0:19],
                                                end_datetime_utc.isoformat()[0:19]))
                                cnx.commit()
                                # todo: check with hourly low limit and hourly high limit
                                add_values = (" INSERT INTO tbl_offline_meter_hourly "
                                              "             (offline_meter_id, start_datetime_utc, actual_value) "
                                              " VALUES  ")

                                while start_datetime_utc < end_datetime_utc:
                                    add_values += " (" + str(offline_meter_id) + ","
                                    add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
                                    add_values += str(actual_value) + "), "
                                    start_datetime_utc += timedelta(minutes=config.minutes_to_count)

                                print("add_values:" + add_values)
                                # trim ", " at the end of string and then execute
                                cursor.execute(add_values[:-2])
                                cnx.commit()
                    except Exception as e:
                        logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
                        time.sleep(60)
                        continue
                    finally:
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()

            ############################################################################################################
            # STEP 4: update file status to 'done' or 'error'
            ############################################################################################################
            print("to update offline meter file status to done...")
            try:
                cnx = mysql.connector.connect(**config.myems_historical_db)
                cursor = cnx.cursor()
            except Exception as e:
                logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()
                time.sleep(60)
                continue

            try:
                update_row = (" UPDATE tbl_offline_meter_files "
                              " SET status = %s "
                              " WHERE id = %s ")
                cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
                cnx.commit()
            except Exception as e:
                logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
                time.sleep(60)
                continue
            finally:
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()

        # end of for excel_file in excel_file_list

        print("go to sleep")
        time.sleep(300)
        print("wake from sleep, and go to work")
    # end of the outermost while


1			import time
2			from datetime import datetime, timedelta
3			from decimal import Decimal
4
5			import mysql.connector
6			from openpyxl import load_workbook
7
8			import config
9
10
11			################################################################################################################
12			# PROCEDURES:
13			# STEP 1: get all 'new' offline meter files
14			# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
15			# STEP 3: insert or update energy data to table offline meter hourly in energy database
16			# STEP 4: update file status to 'done' or 'error'
17			################################################################################################################
18
19
20			def calculate_hourly(logger):
21			while True:
22			# the outermost while loop to reconnect server if there is a connection error
23			################################################################################################################
24			# STEP 1: get all 'new' offline meter files
25			################################################################################################################
26			cnx = None
27			cursor = None
28			try:
29			cnx = mysql.connector.connect(**config.myems_historical_db)
30			cursor = cnx.cursor()
31			except Exception as e:
32			logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
33			if cursor:
34			cursor.close()
35			if cnx:
36			cnx.close()
37			# sleep several minutes and continue the outer loop to reconnect the database
38			print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
39			time.sleep(60)
40			continue
41
42			print("Connected to MyEMS Historical Database")
43
44			print("Getting all new offline meter files")
45			try:
46			query = (" SELECT id, file_name, file_object "
47			" FROM tbl_offline_meter_files "
48			" WHERE status = 'new' "
49			" ORDER BY id ")
50
51			cursor.execute(query, )
52			rows_files = cursor.fetchall()
53			except Exception as e:
54			logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
55			time.sleep(60)
56			continue
57			finally:
58			if cursor:
59			cursor.close()
60			if cnx:
61			cnx.close()
62
63			excel_file_list = list()
64			if rows_files is not None and len(rows_files) > 0:
			0 ignored issues – show introduced 2021-03-02 03:54 UTC by Report Bug Copy Issue Report The variable `rows_files` does not seem to be defined for all execution paths. Loading history...
65			for row_file in rows_files:
66			excel_file_list.append({"id": row_file[0],
67			"name": row_file[1],
68			"file_object": row_file[2]})
69			else:
70			print("there isn't any new files found, and go to sleep 60 seconds...")
71			time.sleep(60)
72			continue
73
74			################################################################################################################
75			# STEP 2: for each new files, dump file object to local file and then load workbook from the local file
76			################################################################################################################
77			for excel_file in excel_file_list:
78			print("read data from offline meter file" + excel_file['name'])
79			is_valid_file = True
80			fw = None
81			try:
82			fw = open("myems-normalization.blob", 'wb')
83			fw.write(excel_file['file_object'])
84			fw.close()
85			except Exception as e:
86			logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
87			if fw:
88			fw.close()
89			# mark as invalid file
90			is_valid_file = False
91
92			fr = None
93			wb = None
94			try:
95			fr = open("myems-normalization.blob", 'rb')
96			wb = load_workbook(fr, data_only=True)
97			fr.close()
98			except Exception as e:
99			logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
100			if fr:
101			fr.close()
102			# mark as invalid file
103			is_valid_file = False
104
105			energy_data_list = list()
106			# grab the active worksheet
107
108			if is_valid_file:
109			ws = wb.active
110
111			# get timezone offset in minutes, this value will be returned to client
112			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
113			if config.utc_offset[0] == '-':
114			timezone_offset = -timezone_offset
115
116			for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
117			offline_meter_data = dict()
118			offline_meter_data['offline_meter_id'] = None
119			offline_meter_data['offline_meter_name'] = None
120			offline_meter_data['data'] = dict()
121			col_num = 0
122
123			for cell in row:
124			col_num += 1
125			print(cell.value)
126			if col_num == 1:
127			# get offline meter ID
128			if cell.value is not None:
129			offline_meter_data['offline_meter_id'] = cell.value
130			else:
131			break
132			elif col_num == 2:
133			# get offline meter name
134			if cell.value is None:
135			break
136			else:
137			offline_meter_data['offline_meter_name'] = cell.value
138			elif col_num > 3:
139			# get date of the cell
140			try:
141			start_datetime_local = datetime(year=ws['A2'].value,
142			month=ws['B2'].value,
143			day=col_num - 3)
144			except ValueError:
145			# invalid date and go to next cell in this row until reach max_col
146			continue
147
148			start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
149
150			if cell.value is None:
151			# if the cell is empty then stop at that day
152			break
153			else:
154			offline_meter_data['data'][start_datetime_utc] = Decimal(cell.value)
155
156			if len(offline_meter_data['data']) > 0:
157			print("offline_meter_data:" + str(offline_meter_data))
158			energy_data_list.append(offline_meter_data)
159
160			############################################################################################################
161			# STEP 3: insert or update energy data to table offline meter hourly in energy database
162			############################################################################################################
163			print("to valid offline meter id in excel file...")
164			if len(energy_data_list) == 0:
165			print("Could not find any offline meters in the file...")
166			print("and go to process the next file...")
167			is_valid_file = False
168			else:
169			try:
170			cnx = mysql.connector.connect(**config.myems_system_db)
171			cursor = cnx.cursor()
172			except Exception as e:
173			logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
174			if cursor:
175			cursor.close()
176			if cnx:
177			cnx.close()
178			time.sleep(60)
179			continue
180
181			try:
182			cursor.execute(" SELECT id, name, hourly_low_limit, hourly_high_limit"
183			" FROM tbl_offline_meters ")
184			rows_offline_meters = cursor.fetchall()
185			except Exception as e:
186			logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
187			time.sleep(60)
188			continue
189			finally:
190			if cursor:
191			cursor.close()
192			if cnx:
193			cnx.close()
194
195			if rows_offline_meters is None or len(rows_offline_meters) == 0:
			0 ignored issues – show introduced 2021-03-02 03:54 UTC by Report Bug Copy Issue Report The variable `rows_offline_meters` does not seem to be defined for all execution paths. Loading history...
196			print("Could not find any offline meters in the MyEMS System Database...")
197			time.sleep(60)
198			continue
199			else:
200			offline_meter_id_set = set()
201			for row_offline_meter in rows_offline_meters:
202			# valid offline meter id in excel file
203			offline_meter_id_set.add(row_offline_meter[0])
204
205			for energy_data_item in energy_data_list:
206			if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
207			is_valid_file = False
208			break
209
210			for row_offline_meter in rows_offline_meters:
211			if row_offline_meter[0] == energy_data_item['offline_meter_id']:
212			for key in energy_data_item['data']:
213			if row_offline_meter[2] > (energy_data_item['data'][key]/24):
214			is_valid_file = False
215			break
216			elif row_offline_meter[3] < (energy_data_item['data'][key]/24):
217			is_valid_file = False
218			break
219			break
220
221			if is_valid_file:
222			####################################################################################################
223			# delete possibly exists offline meter hourly data in myems energy database,
224			# and then insert new offline meter hourly data
225			####################################################################################################
226			try:
227			cnx = mysql.connector.connect(**config.myems_energy_db)
228			cursor = cnx.cursor()
229			except Exception as e:
230			logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
231			if cursor:
232			cursor.close()
233			if cnx:
234			cnx.close()
235			time.sleep(60)
236			continue
237
238			try:
239			for energy_data_item in energy_data_list:
240			offline_meter_id = energy_data_item['offline_meter_id']
241			print(energy_data_item['data'].items())
242			for start_datetime_utc, daily_value in energy_data_item['data'].items():
243			end_datetime_utc = start_datetime_utc + timedelta(hours=24)
244			actual_value = \
245			daily_value / (Decimal(24) * Decimal(60) / Decimal(config.minutes_to_count))
246			cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
247			" WHERE offline_meter_id = %s "
248			" AND start_datetime_utc >= %s "
249			" AND start_datetime_utc < %s ",
250			(offline_meter_id,
251			start_datetime_utc.isoformat()[0:19],
252			end_datetime_utc.isoformat()[0:19]))
253			cnx.commit()
254			# todo: check with hourly low limit and hourly high limit
255			add_values = (" INSERT INTO tbl_offline_meter_hourly "
256			" (offline_meter_id, start_datetime_utc, actual_value) "
257			" VALUES ")
258
259			while start_datetime_utc < end_datetime_utc:
260			add_values += " (" + str(offline_meter_id) + ","
261			add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
262			add_values += str(actual_value) + "), "
263			start_datetime_utc += timedelta(minutes=config.minutes_to_count)
264
265			print("add_values:" + add_values)
266			# trim ", " at the end of string and then execute
267			cursor.execute(add_values[:-2])
268			cnx.commit()
269			except Exception as e:
270			logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
271			time.sleep(60)
272			continue
273			finally:
274			if cursor:
275			cursor.close()
276			if cnx:
277			cnx.close()
278
279			############################################################################################################
280			# STEP 4: update file status to 'done' or 'error'
281			############################################################################################################
282			print("to update offline meter file status to done...")
283			try:
284			cnx = mysql.connector.connect(**config.myems_historical_db)
285			cursor = cnx.cursor()
286			except Exception as e:
287			logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
288			if cursor:
289			cursor.close()
290			if cnx:
291			cnx.close()
292			time.sleep(60)
293			continue
294
295			try:
296			update_row = (" UPDATE tbl_offline_meter_files "
297			" SET status = %s "
298			" WHERE id = %s ")
299			cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
300			cnx.commit()
301			except Exception as e:
302			logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
303			time.sleep(60)
304			continue
305			finally:
306			if cursor:
307			cursor.close()
308			if cnx:
309			cnx.close()
310
311			# end of for excel_file in excel_file_list
312
313			print("go to sleep")
314			time.sleep(300)
315			print("wake from sleep, and go to work")
316			# end of the outermost while
317

MyEMS / myems

offlinemeter.calculate_hourly() F last analyzed 2025-06-17 15:42 UTC

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like

offlinemeter.calculate_hourly() F
last analyzed 2025-06-17 15:42 UTC