equipment_billing_output_category.main() - Code Metrics - myems/myems-aggregation - Measure and Improve Code Quality continuously with Scrutinizer

equipment_billing_output_category.main() F
last analyzed 2020-12-08 09:48 UTC

↳ Parent: equipment_billing_output_category

Complexity

Conditions

Size

Total Lines	248
Code Lines	171

Duplication

Lines	248
Ratio	100 %

Importance

Changes

Metric	Value
eloc	171
dl	248
loc	248
rs	0
c	0
b	0
f	0
cc	57
nop	1

How to fix Long Method Complexity

import time
from datetime import datetime, timedelta
from decimal import Decimal
import mysql.connector
import tariff
import config


########################################################################################################################
# PROCEDURES
# Step 1: get all equipments
# for each equipment in list:
#   Step 2: get the latest start_datetime_utc
#   Step 3: get all energy input data since the latest start_datetime_utc
#   Step 4: get tariffs
#   Step 5: calculate billing by multiplying energy with tariff
#   Step 6: save billing data to database
########################################################################################################################


def main(logger):


    while True:
        # the outermost while loop
        ################################################################################################################
        # Step 1: get all equipments
        ################################################################################################################
        cnx_system_db = None
        cursor_system_db = None
        try:
            cnx_system_db = mysql.connector.connect(**config.myems_system_db)
            cursor_system_db = cnx_system_db.cursor()
        except Exception as e:
            logger.error("Error in step 1.1 of equipment_billing_input_category " + str(e))
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()
            # sleep and continue the outermost while loop
            time.sleep(60)
            continue

        print("Connected to MyEMS System Database")

        try:
            cursor_system_db.execute(" SELECT id, name, cost_center_id "
                                     " FROM tbl_equipments "
                                     " ORDER BY id ")
            rows_equipments = cursor_system_db.fetchall()

            if rows_equipments is None or len(rows_equipments) == 0:
                print("Step 1.2: There isn't any equipments. ")
                if cursor_system_db:
                    cursor_system_db.close()
                if cnx_system_db:
                    cnx_system_db.close()
                # sleep and continue the outermost while loop
                time.sleep(60)
                continue

            equipment_list = list()
            for row in rows_equipments:
                equipment_list.append({"id": row[0], "name": row[1], "cost_center_id": row[2]})

        except Exception as e:
            logger.error("Error in step 1.2 of equipment_billing_output_category " + str(e))
            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()
            # sleep and continue the outermost while loop
            time.sleep(60)
            continue

        print("Step 1.2: Got all equipments from MyEMS System Database")

        cnx_energy_db = None
        cursor_energy_db = None
        try:
            cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
            cursor_energy_db = cnx_energy_db.cursor()
        except Exception as e:
            logger.error("Error in step 1.3 of equipment_billing_output_category " + str(e))
            if cursor_energy_db:
                cursor_energy_db.close()
            if cnx_energy_db:
                cnx_energy_db.close()

            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()
            # sleep and continue the outermost while loop
            time.sleep(60)
            continue

        print("Connected to MyEMS Energy Database")

        cnx_billing_db = None
        cursor_billing_db = None
        try:
            cnx_billing_db = mysql.connector.connect(**config.myems_billing_db)
            cursor_billing_db = cnx_billing_db.cursor()
        except Exception as e:
            logger.error("Error in step 1.4 of equipment_billing_output_category " + str(e))
            if cursor_billing_db:
                cursor_billing_db.close()
            if cnx_billing_db:
                cnx_billing_db.close()

            if cursor_energy_db:
                cursor_energy_db.close()
            if cnx_energy_db:
                cnx_energy_db.close()

            if cursor_system_db:
                cursor_system_db.close()
            if cnx_system_db:
                cnx_system_db.close()
            # sleep and continue the outermost while loop
            time.sleep(60)
            continue

        print("Connected to MyEMS Billing Database")

        for equipment in equipment_list:

            ############################################################################################################
            # Step 2: get the latest start_datetime_utc
            ############################################################################################################
            print("Step 2: get the latest start_datetime_utc from billing database for " + equipment['name'])
            try:
                cursor_billing_db.execute(" SELECT MAX(start_datetime_utc) "
                                          " FROM tbl_equipment_output_category_hourly "
                                          " WHERE equipment_id = %s ",
                                          (equipment['id'], ))
                row_datetime = cursor_billing_db.fetchone()
                start_datetime_utc = datetime.strptime(config.start_datetime_utc, '%Y-%m-%d %H:%M:%S')
                start_datetime_utc = start_datetime_utc.replace(minute=0, second=0, microsecond=0, tzinfo=None)

                if row_datetime is not None and len(row_datetime) > 0 and isinstance(row_datetime[0], datetime):
                    # replace second and microsecond with 0
                    # note: do not replace minute in case of calculating in half hourly
                    start_datetime_utc = row_datetime[0].replace(second=0, microsecond=0, tzinfo=None)
                    # start from the next time slot
                    start_datetime_utc += timedelta(minutes=config.minutes_to_count)

                print("start_datetime_utc: " + start_datetime_utc.isoformat()[0:19])
            except Exception as e:
                logger.error("Error in step 2 of equipment_billing_output_category " + str(e))
                # break the for equipment loop
                break

            ############################################################################################################
            # Step 3: get all energy output data since the latest start_datetime_utc
            ############################################################################################################
            print("Step 3: get all energy output data since the latest start_datetime_utc")

            query = (" SELECT start_datetime_utc, energy_category_id, actual_value "
                     " FROM tbl_equipment_output_category_hourly "
                     " WHERE equipment_id = %s AND start_datetime_utc >= %s "
                     " ORDER BY id ")
            cursor_energy_db.execute(query, (equipment['id'], start_datetime_utc, ))
            rows_hourly = cursor_energy_db.fetchall()

            if rows_hourly is None or len(rows_hourly) == 0:
                print("Step 3: There isn't any energy output data to calculate. ")
                # continue the for equipment loop
                continue

            energy_dict = dict()
            energy_category_list = list()
            end_datetime_utc = start_datetime_utc
            for row_hourly in rows_hourly:
                current_datetime_utc = row_hourly[0]
                energy_category_id = row_hourly[1]

                if energy_category_id not in energy_category_list:
                    energy_category_list.append(energy_category_id)

                actual_value = row_hourly[2]
                if energy_dict.get(current_datetime_utc) is None:
                    energy_dict[current_datetime_utc] = dict()
                energy_dict[current_datetime_utc][energy_category_id] = actual_value
                if current_datetime_utc > end_datetime_utc:
                    end_datetime_utc = current_datetime_utc

            ############################################################################################################
            # Step 4: get tariffs
            ############################################################################################################
            print("Step 4: get tariffs")
            tariff_dict = dict()
            for energy_category_id in energy_category_list:
                tariff_dict[energy_category_id] = tariff.get_energy_category_tariffs(equipment['cost_center_id'],
                                                                                     energy_category_id,
                                                                                     start_datetime_utc,
                                                                                     end_datetime_utc)
            ############################################################################################################
            # Step 5: calculate billing by multiplying energy with tariff
            ############################################################################################################
            print("Step 5: calculate billing by multiplying energy with tariff")
            billing_dict = dict()

            if len(energy_dict) > 0:
                for current_datetime_utc in energy_dict.keys():
                    billing_dict[current_datetime_utc] = dict()
                    for energy_category_id in energy_category_list:
                        current_tariff = tariff_dict[energy_category_id].get(current_datetime_utc)
                        current_energy = energy_dict[current_datetime_utc].get(energy_category_id)
                        if current_tariff is not None \
                                and isinstance(current_tariff, Decimal) \
                                and current_energy is not None \
                                and isinstance(current_energy, Decimal):
                            billing_dict[current_datetime_utc][energy_category_id] = \
                                current_energy * current_tariff

                    if len(billing_dict[current_datetime_utc]) == 0:
                        del billing_dict[current_datetime_utc]

            ############################################################################################################
            # Step 6: save billing data to billing database
            ############################################################################################################
            print("Step 6: save billing data to billing database")

            if len(billing_dict) > 0:
                try:
                    add_values = (" INSERT INTO tbl_equipment_output_category_hourly "
                                  "             (equipment_id, "
                                  "              energy_category_id, "
                                  "              start_datetime_utc, "
                                  "              actual_value) "
                                  " VALUES  ")

                    for current_datetime_utc in billing_dict:
                        for energy_category_id in energy_category_list:
                            current_billing = billing_dict[current_datetime_utc].get(energy_category_id)
                            if current_billing is not None and isinstance(current_billing, Decimal):
                                add_values += " (" + str(equipment['id']) + ","
                                add_values += " " + str(energy_category_id) + ","
                                add_values += "'" + current_datetime_utc.isoformat()[0:19] + "',"
                                add_values += str(billing_dict[current_datetime_utc][energy_category_id]) + "), "
                    print("add_values:" + add_values)
                    # trim ", " at the end of string and then execute
                    cursor_billing_db.execute(add_values[:-2])
                    cnx_billing_db.commit()
                except Exception as e:
                    logger.error("Error in step 6 of equipment_billing_output_category " + str(e))
                    # break the for equipment loop
                    break

        # end of for equipment loop
        if cnx_system_db:
            cnx_system_db.close()
        if cursor_system_db:
            cursor_system_db.close()

        if cnx_energy_db:
            cnx_energy_db.close()
        if cursor_energy_db:
            cursor_energy_db.close()

        if cnx_billing_db:
            cnx_billing_db.close()
        if cursor_billing_db:
            cursor_billing_db.close()
        print("go to sleep 300 seconds...")
        time.sleep(300)
        print("wake from sleep, and continue to work...")
    # end of the outermost while loop


1		import time
2		from datetime import datetime, timedelta
3		from decimal import Decimal
4		import mysql.connector
5		import tariff
6		import config
7
8
9		########################################################################################################################
10		# PROCEDURES
11		# Step 1: get all equipments
12		# for each equipment in list:
13		# Step 2: get the latest start_datetime_utc
14		# Step 3: get all energy input data since the latest start_datetime_utc
15		# Step 4: get tariffs
16		# Step 5: calculate billing by multiplying energy with tariff
17		# Step 6: save billing data to database
18		########################################################################################################################
19
20
21	View Code Duplication	def main(logger):
		0 ignored issues – show Duplication introduced 2020-11-25 02:11 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
22
23		while True:
24		# the outermost while loop
25		################################################################################################################
26		# Step 1: get all equipments
27		################################################################################################################
28		cnx_system_db = None
29		cursor_system_db = None
30		try:
31		cnx_system_db = mysql.connector.connect(**config.myems_system_db)
32		cursor_system_db = cnx_system_db.cursor()
33		except Exception as e:
34		logger.error("Error in step 1.1 of equipment_billing_input_category " + str(e))
35		if cursor_system_db:
36		cursor_system_db.close()
37		if cnx_system_db:
38		cnx_system_db.close()
39		# sleep and continue the outermost while loop
40		time.sleep(60)
41		continue
42
43		print("Connected to MyEMS System Database")
44
45		try:
46		cursor_system_db.execute(" SELECT id, name, cost_center_id "
47		" FROM tbl_equipments "
48		" ORDER BY id ")
49		rows_equipments = cursor_system_db.fetchall()
50
51		if rows_equipments is None or len(rows_equipments) == 0:
52		print("Step 1.2: There isn't any equipments. ")
53		if cursor_system_db:
54		cursor_system_db.close()
55		if cnx_system_db:
56		cnx_system_db.close()
57		# sleep and continue the outermost while loop
58		time.sleep(60)
59		continue
60
61		equipment_list = list()
62		for row in rows_equipments:
63		equipment_list.append({"id": row[0], "name": row[1], "cost_center_id": row[2]})
64
65		except Exception as e:
66		logger.error("Error in step 1.2 of equipment_billing_output_category " + str(e))
67		if cursor_system_db:
68		cursor_system_db.close()
69		if cnx_system_db:
70		cnx_system_db.close()
71		# sleep and continue the outermost while loop
72		time.sleep(60)
73		continue
74
75		print("Step 1.2: Got all equipments from MyEMS System Database")
76
77		cnx_energy_db = None
78		cursor_energy_db = None
79		try:
80		cnx_energy_db = mysql.connector.connect(**config.myems_energy_db)
81		cursor_energy_db = cnx_energy_db.cursor()
82		except Exception as e:
83		logger.error("Error in step 1.3 of equipment_billing_output_category " + str(e))
84		if cursor_energy_db:
85		cursor_energy_db.close()
86		if cnx_energy_db:
87		cnx_energy_db.close()
88
89		if cursor_system_db:
90		cursor_system_db.close()
91		if cnx_system_db:
92		cnx_system_db.close()
93		# sleep and continue the outermost while loop
94		time.sleep(60)
95		continue
96
97		print("Connected to MyEMS Energy Database")
98
99		cnx_billing_db = None
100		cursor_billing_db = None
101		try:
102		cnx_billing_db = mysql.connector.connect(**config.myems_billing_db)
103		cursor_billing_db = cnx_billing_db.cursor()
104		except Exception as e:
105		logger.error("Error in step 1.4 of equipment_billing_output_category " + str(e))
106		if cursor_billing_db:
107		cursor_billing_db.close()
108		if cnx_billing_db:
109		cnx_billing_db.close()
110
111		if cursor_energy_db:
112		cursor_energy_db.close()
113		if cnx_energy_db:
114		cnx_energy_db.close()
115
116		if cursor_system_db:
117		cursor_system_db.close()
118		if cnx_system_db:
119		cnx_system_db.close()
120		# sleep and continue the outermost while loop
121		time.sleep(60)
122		continue
123
124		print("Connected to MyEMS Billing Database")
125
126		for equipment in equipment_list:
127
128		############################################################################################################
129		# Step 2: get the latest start_datetime_utc
130		############################################################################################################
131		print("Step 2: get the latest start_datetime_utc from billing database for " + equipment['name'])
132		try:
133		cursor_billing_db.execute(" SELECT MAX(start_datetime_utc) "
134		" FROM tbl_equipment_output_category_hourly "
135		" WHERE equipment_id = %s ",
136		(equipment['id'], ))
137		row_datetime = cursor_billing_db.fetchone()
138		start_datetime_utc = datetime.strptime(config.start_datetime_utc, '%Y-%m-%d %H:%M:%S')
139		start_datetime_utc = start_datetime_utc.replace(minute=0, second=0, microsecond=0, tzinfo=None)
140
141		if row_datetime is not None and len(row_datetime) > 0 and isinstance(row_datetime[0], datetime):
142		# replace second and microsecond with 0
143		# note: do not replace minute in case of calculating in half hourly
144		start_datetime_utc = row_datetime[0].replace(second=0, microsecond=0, tzinfo=None)
145		# start from the next time slot
146		start_datetime_utc += timedelta(minutes=config.minutes_to_count)
147
148		print("start_datetime_utc: " + start_datetime_utc.isoformat()[0:19])
149		except Exception as e:
150		logger.error("Error in step 2 of equipment_billing_output_category " + str(e))
151		# break the for equipment loop
152		break
153
154		############################################################################################################
155		# Step 3: get all energy output data since the latest start_datetime_utc
156		############################################################################################################
157		print("Step 3: get all energy output data since the latest start_datetime_utc")
158
159		query = (" SELECT start_datetime_utc, energy_category_id, actual_value "
160		" FROM tbl_equipment_output_category_hourly "
161		" WHERE equipment_id = %s AND start_datetime_utc >= %s "
162		" ORDER BY id ")
163		cursor_energy_db.execute(query, (equipment['id'], start_datetime_utc, ))
164		rows_hourly = cursor_energy_db.fetchall()
165
166		if rows_hourly is None or len(rows_hourly) == 0:
167		print("Step 3: There isn't any energy output data to calculate. ")
168		# continue the for equipment loop
169		continue
170
171		energy_dict = dict()
172		energy_category_list = list()
173		end_datetime_utc = start_datetime_utc
174		for row_hourly in rows_hourly:
175		current_datetime_utc = row_hourly[0]
176		energy_category_id = row_hourly[1]
177
178		if energy_category_id not in energy_category_list:
179		energy_category_list.append(energy_category_id)
180
181		actual_value = row_hourly[2]
182		if energy_dict.get(current_datetime_utc) is None:
183		energy_dict[current_datetime_utc] = dict()
184		energy_dict[current_datetime_utc][energy_category_id] = actual_value
185		if current_datetime_utc > end_datetime_utc:
186		end_datetime_utc = current_datetime_utc
187
188		############################################################################################################
189		# Step 4: get tariffs
190		############################################################################################################
191		print("Step 4: get tariffs")
192		tariff_dict = dict()
193		for energy_category_id in energy_category_list:
194		tariff_dict[energy_category_id] = tariff.get_energy_category_tariffs(equipment['cost_center_id'],
195		energy_category_id,
196		start_datetime_utc,
197		end_datetime_utc)
198		############################################################################################################
199		# Step 5: calculate billing by multiplying energy with tariff
200		############################################################################################################
201		print("Step 5: calculate billing by multiplying energy with tariff")
202		billing_dict = dict()
203
204		if len(energy_dict) > 0:
205		for current_datetime_utc in energy_dict.keys():
206		billing_dict[current_datetime_utc] = dict()
207		for energy_category_id in energy_category_list:
208		current_tariff = tariff_dict[energy_category_id].get(current_datetime_utc)
209		current_energy = energy_dict[current_datetime_utc].get(energy_category_id)
210		if current_tariff is not None \
211		and isinstance(current_tariff, Decimal) \
212		and current_energy is not None \
213		and isinstance(current_energy, Decimal):
214		billing_dict[current_datetime_utc][energy_category_id] = \
215		current_energy * current_tariff
216
217		if len(billing_dict[current_datetime_utc]) == 0:
218		del billing_dict[current_datetime_utc]
219
220		############################################################################################################
221		# Step 6: save billing data to billing database
222		############################################################################################################
223		print("Step 6: save billing data to billing database")
224
225		if len(billing_dict) > 0:
226		try:
227		add_values = (" INSERT INTO tbl_equipment_output_category_hourly "
228		" (equipment_id, "
229		" energy_category_id, "
230		" start_datetime_utc, "
231		" actual_value) "
232		" VALUES ")
233
234		for current_datetime_utc in billing_dict:
235		for energy_category_id in energy_category_list:
236		current_billing = billing_dict[current_datetime_utc].get(energy_category_id)
237		if current_billing is not None and isinstance(current_billing, Decimal):
238		add_values += " (" + str(equipment['id']) + ","
239		add_values += " " + str(energy_category_id) + ","
240		add_values += "'" + current_datetime_utc.isoformat()[0:19] + "',"
241		add_values += str(billing_dict[current_datetime_utc][energy_category_id]) + "), "
242		print("add_values:" + add_values)
243		# trim ", " at the end of string and then execute
244		cursor_billing_db.execute(add_values[:-2])
245		cnx_billing_db.commit()
246		except Exception as e:
247		logger.error("Error in step 6 of equipment_billing_output_category " + str(e))
248		# break the for equipment loop
249		break
250
251		# end of for equipment loop
252		if cnx_system_db:
253		cnx_system_db.close()
254		if cursor_system_db:
255		cursor_system_db.close()
256
257		if cnx_energy_db:
258		cnx_energy_db.close()
259		if cursor_energy_db:
260		cursor_energy_db.close()
261
262		if cnx_billing_db:
263		cnx_billing_db.close()
264		if cursor_billing_db:
265		cursor_billing_db.close()
266		print("go to sleep 300 seconds...")
267		time.sleep(300)
268		print("wake from sleep, and continue to work...")
269		# end of the outermost while loop
270

myems / myems-aggregation

equipment_billing_output_category.main() F last analyzed 2020-12-08 09:48 UTC

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like

equipment_billing_output_category.main() F
last analyzed 2020-12-08 09:48 UTC