offlinemeter - Code Metrics - Inspection of "Merge branch 'develop'" - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( a20932...98c786 )

by Guangyu

created 2021-10-28 15:14 UTC

offlinemeter F

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	313
Duplicated Lines	0 %

Importance

Changes

Metric	Value
wmc	62
eloc	225
dl	0
loc	313
rs	3.44
c	0
b	0
f	0

1 Function

Rating	Name	Duplication	Size	Complexity
F	calculate_hourly()	0	296	62

How to fix Complexity

import time
from datetime import datetime, timedelta
import mysql.connector
from openpyxl import load_workbook
from decimal import Decimal
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:
        # outer 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 outer while



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

MyEMS / myems

Push — master ( a20932...98c786 )

offlinemeter F

Complexity

Size/Duplication

Importance

1 Function

How to fix Complexity

Complexity

Duplication Side-by-Side

Filter issues like