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

datarepair.do() F
last analyzed 2025-06-17 15:42 UTC

↳ Parent: datarepair

Complexity

Conditions

Size

Total Lines	307
Code Lines	233

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	233
dl	0
loc	307
rs	0
c	0
b	0
f	0
cc	67
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' data repair files
# STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
# STEP 3: insert or update point data to table tbl_energy_value in historical database
# STEP 4: update file status to 'done' or 'error'
################################################################################################################


def do(logger):
    while True:
        # the outermost while loop to reconnect server if there is a connection error
        ################################################################################################################
        # STEP 1: get all 'new' data repair 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 datarepair.do " + 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 data repair files")
        try:
            query = (" SELECT id, file_name, file_object "
                     " FROM tbl_data_repair_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 datarepair.do " + 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 data repair files found, and go to sleep 60 seconds")
            time.sleep(60)
            continue

        ################################################################################################################
        # STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
        ################################################################################################################
        for excel_file in excel_file_list:
            print("reading data from data repair file " + excel_file['name'])
            is_valid_file = True
            fw = None
            try:
                fw = open("myems-data-repair.blob", 'wb')
                fw.write(excel_file['file_object'])
                fw.close()
            except Exception as e:
                logger.error("Error in step 2.1 of datarepair.do " + str(e))
                if fw:
                    fw.close()
                # mark as invalid file
                is_valid_file = False

            fr = None
            wb = None
            try:
                fr = open("myems-data-repair.blob", 'rb')
                wb = load_workbook(fr, data_only=True)
                fr.close()
            except Exception as e:
                logger.error("Error in step 2.2 of datarepair.do " + 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=2, max_row=1024, min_col=1, max_col=4):
                    repair_file_data = dict()
                    repair_file_data['point_id'] = None
                    repair_file_data['point_name'] = None
                    repair_file_data['date_time_utc'] = None
                    repair_file_data['actual_value'] = None
                    col_num = 0

                    for cell in row:
                        col_num += 1
                        if not isinstance(cell.value, type(None)):
                            print(cell.value)
                        if col_num == 1:
                            # get point ID (should exist in myems_system_db.tbl_points)
                            if cell.value is not None:
                                repair_file_data['point_id'] = cell.value
                            else:
                                break
                        elif col_num == 2:
                            # get point name
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                repair_file_data['point_name'] = cell.value
                        elif col_num == 3:
                            # get date of the cell
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                try:
                                    start_datetime_local = datetime.strptime(cell.value, '%Y-%m-%d %H:%M:%S')
                                    start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
                                    repair_file_data['date_time_utc'] = start_datetime_utc
                                except Exception as e:
                                    print("invalid datetime " + str(e))
                                    is_valid_file = False
                                    break
                        elif col_num == 4:
                            if cell.value is None:
                                is_valid_file = False
                                break
                            else:
                                repair_file_data['actual_value'] = Decimal(cell.value)

                    if not isinstance(repair_file_data['point_id'], type(None)):
                        print("repair_file_data:" + str(repair_file_data))
                        energy_data_list.append(repair_file_data)

            ############################################################################################################
            # STEP 3: insert or update point data to table tbl_energy_value in historical database
            ############################################################################################################
            print("check point id in excel file")
            if len(energy_data_list) == 0:
                print("Could not find any repair data in the file")
                print("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 datarepair.do " + str(e))
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()
                    time.sleep(60)
                    continue

                try:
                    cursor.execute(" SELECT id, name, low_limit, high_limit"
                                   " FROM tbl_points ")
                    rows_points = cursor.fetchall()
                except Exception as e:
                    logger.error("Error in step 3.2 of datarepair.do " + str(e))
                    time.sleep(60)
                    continue
                finally:
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()

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

                    print("Could not find any points in the myems system database")
                    time.sleep(60)
                    continue
                elif is_valid_file:
                    system_point_id_set = set()
                    for rows_point in rows_points:
                        # valid point id in excel file
                        system_point_id_set.add(rows_point[0])

                    file_point_id_set = set()
                    for energy_data_item in energy_data_list:
                        file_point_id_set.add(energy_data_item['point_id'])

                    # limit one point id in one file
                    if len(file_point_id_set) != 1:
                        is_valid_file = False

                    for energy_data_item in energy_data_list:
                        if energy_data_item['point_id'] not in system_point_id_set:
                            is_valid_file = False
                            break
                        # check actual value with point high limit and low limit
                        for rows_point in rows_points:
                            if energy_data_item['point_id'] == rows_point[0]:
                                if energy_data_item['actual_value'] < rows_point[2]:
                                    is_valid_file = False
                                    break
                                elif energy_data_item['actual_value'] > rows_point[3]:
                                    is_valid_file = False
                                    break
                                break

                if is_valid_file:
                    ####################################################################################################
                    # delete possibly exists point value data in myems historical database,
                    # and then insert new point data
                    ####################################################################################################
                    try:
                        cnx = mysql.connector.connect(**config.myems_historical_db)
                        cursor = cnx.cursor()
                    except Exception as e:
                        logger.error("Error in step 3.2 of datarepair.do " + str(e))
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()
                        time.sleep(60)
                        continue

                    try:
                        date_time_utc_list = list()
                        for i in range(len(energy_data_list)):
                            for item in (energy_data_list[i]['date_time_utc'],):
                                date_time_utc_list.append(item)

                        start_date_time_utc = min(date_time_utc_list)
                        end_date_time_utc = max(date_time_utc_list)
                        point_id = energy_data_list[0]['point_id']
                        print("deleted data from %s to %s in table myems_historical_db.tbl_energy_value",
                              start_date_time_utc, end_date_time_utc)
                        cursor.execute(" DELETE FROM tbl_energy_value "
                                       " WHERE point_id = %s "
                                       "       AND utc_date_time >= %s "
                                       "       AND utc_date_time <= %s ",
                                       (str(point_id),
                                        start_date_time_utc.isoformat()[0:19],
                                        end_date_time_utc.isoformat()[0:19]))
                        cnx.commit()

                        for energy_data_item in energy_data_list:
                            add_values = (" INSERT INTO tbl_energy_value "
                                          "             (point_id, utc_date_time, actual_value, is_bad) "
                                          " VALUES  ")
                            add_values += " (" + str(point_id) + ","
                            add_values += "'" + energy_data_item['date_time_utc'].isoformat()[0:19] + "',"
                            add_values += "'" + str(energy_data_item['actual_value']) + "',"
                            add_values += "0" + "), "
                            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 datarepair.do " + 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("updating data repair file status")
            try:
                cnx = mysql.connector.connect(**config.myems_historical_db)
                cursor = cnx.cursor()
            except Exception as e:
                logger.error("Error in step 4.1 of datarepair.do " + str(e))
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()
                time.sleep(60)
                continue

            try:
                update_row = (" UPDATE tbl_data_repair_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 datarepair.do " + 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' data repair files
14			# STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
15			# STEP 3: insert or update point data to table tbl_energy_value in historical database
16			# STEP 4: update file status to 'done' or 'error'
17			################################################################################################################
18
19
20			def do(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' data repair 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 datarepair.do " + 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			print("Getting all new data repair files")
44			try:
45			query = (" SELECT id, file_name, file_object "
46			" FROM tbl_data_repair_files "
47			" WHERE status = 'new' "
48			" ORDER BY id ")
49			cursor.execute(query, )
50			rows_files = cursor.fetchall()
51			except Exception as e:
52			logger.error("Error in step 1.2 of datarepair.do " + str(e))
53			time.sleep(60)
54			continue
55			finally:
56			if cursor:
57			cursor.close()
58			if cnx:
59			cnx.close()
60
61			excel_file_list = list()
62			if rows_files is not None and len(rows_files) > 0:
			0 ignored issues – show introduced 2022-07-24 04:35 UTC by Report Bug Copy Issue Report The variable `rows_files` does not seem to be defined for all execution paths. Loading history...
63			for row_file in rows_files:
64			excel_file_list.append({"id": row_file[0],
65			"name": row_file[1],
66			"file_object": row_file[2]})
67			else:
68			print("there isn't any new data repair files found, and go to sleep 60 seconds")
69			time.sleep(60)
70			continue
71
72			################################################################################################################
73			# STEP 2: for each new files, iterate all rows, read cell value and store data to point data list
74			################################################################################################################
75			for excel_file in excel_file_list:
76			print("reading data from data repair file " + excel_file['name'])
77			is_valid_file = True
78			fw = None
79			try:
80			fw = open("myems-data-repair.blob", 'wb')
81			fw.write(excel_file['file_object'])
82			fw.close()
83			except Exception as e:
84			logger.error("Error in step 2.1 of datarepair.do " + str(e))
85			if fw:
86			fw.close()
87			# mark as invalid file
88			is_valid_file = False
89
90			fr = None
91			wb = None
92			try:
93			fr = open("myems-data-repair.blob", 'rb')
94			wb = load_workbook(fr, data_only=True)
95			fr.close()
96			except Exception as e:
97			logger.error("Error in step 2.2 of datarepair.do " + str(e))
98			if fr:
99			fr.close()
100			# mark as invalid file
101			is_valid_file = False
102
103			energy_data_list = list()
104			# grab the active worksheet
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=2, max_row=1024, min_col=1, max_col=4):
114			repair_file_data = dict()
115			repair_file_data['point_id'] = None
116			repair_file_data['point_name'] = None
117			repair_file_data['date_time_utc'] = None
118			repair_file_data['actual_value'] = None
119			col_num = 0
120
121			for cell in row:
122			col_num += 1
123			if not isinstance(cell.value, type(None)):
124			print(cell.value)
125			if col_num == 1:
126			# get point ID (should exist in myems_system_db.tbl_points)
127			if cell.value is not None:
128			repair_file_data['point_id'] = cell.value
129			else:
130			break
131			elif col_num == 2:
132			# get point name
133			if cell.value is None:
134			is_valid_file = False
135			break
136			else:
137			repair_file_data['point_name'] = cell.value
138			elif col_num == 3:
139			# get date of the cell
140			if cell.value is None:
141			is_valid_file = False
142			break
143			else:
144			try:
145			start_datetime_local = datetime.strptime(cell.value, '%Y-%m-%d %H:%M:%S')
146			start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
147			repair_file_data['date_time_utc'] = start_datetime_utc
148			except Exception as e:
149			print("invalid datetime " + str(e))
150			is_valid_file = False
151			break
152			elif col_num == 4:
153			if cell.value is None:
154			is_valid_file = False
155			break
156			else:
157			repair_file_data['actual_value'] = Decimal(cell.value)
158
159			if not isinstance(repair_file_data['point_id'], type(None)):
160			print("repair_file_data:" + str(repair_file_data))
161			energy_data_list.append(repair_file_data)
162
163			############################################################################################################
164			# STEP 3: insert or update point data to table tbl_energy_value in historical database
165			############################################################################################################
166			print("check point id in excel file")
167			if len(energy_data_list) == 0:
168			print("Could not find any repair data in the file")
169			print("go to process the next file")
170			is_valid_file = False
171			else:
172			try:
173			cnx = mysql.connector.connect(**config.myems_system_db)
174			cursor = cnx.cursor()
175			except Exception as e:
176			logger.error("Error in step 3.1 of datarepair.do " + str(e))
177			if cursor:
178			cursor.close()
179			if cnx:
180			cnx.close()
181			time.sleep(60)
182			continue
183
184			try:
185			cursor.execute(" SELECT id, name, low_limit, high_limit"
186			" FROM tbl_points ")
187			rows_points = cursor.fetchall()
188			except Exception as e:
189			logger.error("Error in step 3.2 of datarepair.do " + str(e))
190			time.sleep(60)
191			continue
192			finally:
193			if cursor:
194			cursor.close()
195			if cnx:
196			cnx.close()
197
198			if rows_points is None or len(rows_points) == 0:
			0 ignored issues – show introduced 2022-07-24 04:35 UTC by Report Bug Copy Issue Report The variable `rows_points` does not seem to be defined for all execution paths. Loading history...
199			print("Could not find any points in the myems system database")
200			time.sleep(60)
201			continue
202			elif is_valid_file:
203			system_point_id_set = set()
204			for rows_point in rows_points:
205			# valid point id in excel file
206			system_point_id_set.add(rows_point[0])
207
208			file_point_id_set = set()
209			for energy_data_item in energy_data_list:
210			file_point_id_set.add(energy_data_item['point_id'])
211
212			# limit one point id in one file
213			if len(file_point_id_set) != 1:
214			is_valid_file = False
215
216			for energy_data_item in energy_data_list:
217			if energy_data_item['point_id'] not in system_point_id_set:
218			is_valid_file = False
219			break
220			# check actual value with point high limit and low limit
221			for rows_point in rows_points:
222			if energy_data_item['point_id'] == rows_point[0]:
223			if energy_data_item['actual_value'] < rows_point[2]:
224			is_valid_file = False
225			break
226			elif energy_data_item['actual_value'] > rows_point[3]:
227			is_valid_file = False
228			break
229			break
230
231			if is_valid_file:
232			####################################################################################################
233			# delete possibly exists point value data in myems historical database,
234			# and then insert new point data
235			####################################################################################################
236			try:
237			cnx = mysql.connector.connect(**config.myems_historical_db)
238			cursor = cnx.cursor()
239			except Exception as e:
240			logger.error("Error in step 3.2 of datarepair.do " + str(e))
241			if cursor:
242			cursor.close()
243			if cnx:
244			cnx.close()
245			time.sleep(60)
246			continue
247
248			try:
249			date_time_utc_list = list()
250			for i in range(len(energy_data_list)):
251			for item in (energy_data_list[i]['date_time_utc'],):
252			date_time_utc_list.append(item)
253
254			start_date_time_utc = min(date_time_utc_list)
255			end_date_time_utc = max(date_time_utc_list)
256			point_id = energy_data_list[0]['point_id']
257			print("deleted data from %s to %s in table myems_historical_db.tbl_energy_value",
258			start_date_time_utc, end_date_time_utc)
259			cursor.execute(" DELETE FROM tbl_energy_value "
260			" WHERE point_id = %s "
261			" AND utc_date_time >= %s "
262			" AND utc_date_time <= %s ",
263			(str(point_id),
264			start_date_time_utc.isoformat()[0:19],
265			end_date_time_utc.isoformat()[0:19]))
266			cnx.commit()
267
268			for energy_data_item in energy_data_list:
269			add_values = (" INSERT INTO tbl_energy_value "
270			" (point_id, utc_date_time, actual_value, is_bad) "
271			" VALUES ")
272			add_values += " (" + str(point_id) + ","
273			add_values += "'" + energy_data_item['date_time_utc'].isoformat()[0:19] + "',"
274			add_values += "'" + str(energy_data_item['actual_value']) + "',"
275			add_values += "0" + "), "
276			print("add_values:" + add_values)
277			# trim ", " at the end of string and then execute
278			cursor.execute(add_values[:-2])
279			cnx.commit()
280			except Exception as e:
281			logger.error("Error in step 3.3 of datarepair.do " + str(e))
282			time.sleep(60)
283			continue
284			finally:
285			if cursor:
286			cursor.close()
287			if cnx:
288			cnx.close()
289
290			############################################################################################################
291			# STEP 4: update file status to 'done' or 'error'
292			############################################################################################################
293			print("updating data repair file status")
294			try:
295			cnx = mysql.connector.connect(**config.myems_historical_db)
296			cursor = cnx.cursor()
297			except Exception as e:
298			logger.error("Error in step 4.1 of datarepair.do " + str(e))
299			if cursor:
300			cursor.close()
301			if cnx:
302			cnx.close()
303			time.sleep(60)
304			continue
305
306			try:
307			update_row = (" UPDATE tbl_data_repair_files "
308			" SET status = %s "
309			" WHERE id = %s ")
310			cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
311			cnx.commit()
312			except Exception as e:
313			logger.error("Error in step 4.2 of datarepair.do " + str(e))
314			time.sleep(60)
315			continue
316			finally:
317			if cursor:
318			cursor.close()
319			if cnx:
320			cnx.close()
321
322			# end of for excel_file in excel_file_list
323
324			print("go to sleep")
325			time.sleep(300)
326			print("wake from sleep, and go to work")
327			# end of the outermost while
328

MyEMS / myems

datarepair.do() 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

datarepair.do() F
last analyzed 2025-06-17 15:42 UTC