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

store_billing_input_category.main() F
last analyzed 2025-06-17 15:42 UTC

↳ Parent: store_billing_input_category

Complexity

Conditions

Size

Total Lines	248
Code Lines	170

Duplication

Lines	248
Ratio	100 %

Importance

Changes

Metric	Value
eloc	170
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 config
import tariff


########################################################################################################################
# PROCEDURES
# Step 1: get all stores
# for each store 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 stores
        ################################################################################################################
        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 store_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")

        store_list = list()
        try:
            cursor_system_db.execute(" SELECT id, name, cost_center_id "
                                     " FROM tbl_stores "
                                     " ORDER BY id ")
            rows_stores = cursor_system_db.fetchall()

            if rows_stores is None or len(rows_stores) == 0:
                print("Step 1.2: There isn't any stores. ")
                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

            for row in rows_stores:
                store_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 store_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("Step 1.2: Got all stores 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 store_billing_input_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 store_billing_input_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 store in store_list:

            ############################################################################################################
            # Step 2: get the latest start_datetime_utc
            ############################################################################################################
            print("Step 2: get the latest start_datetime_utc from billing database for " + store['name'])
            try:
                cursor_billing_db.execute(" SELECT MAX(start_datetime_utc) "
                                          " FROM tbl_store_input_category_hourly "
                                          " WHERE store_id = %s ",
                                          (store['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 store_billing_input_category " + str(e))
                # break the for store loop
                break

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

            query = (" SELECT start_datetime_utc, energy_category_id, actual_value "
                     " FROM tbl_store_input_category_hourly "
                     " WHERE store_id = %s AND start_datetime_utc >= %s "
                     " ORDER BY id ")
            cursor_energy_db.execute(query, (store['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 input data to calculate. ")
                # continue the for store 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(store['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_store_input_category_hourly "
                                  "             (store_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(store['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 store_billing_input_category " + str(e))
                    # break the for store loop
                    break

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

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

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

MyEMS / myems

store_billing_input_category.main() 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

store_billing_input_category.main() F
last analyzed 2025-06-17 15:42 UTC