meter_billing - Code Metrics - myems/myems-aggregation - Measure and Improve Code Quality continuously with Scrutinizer

meter_billing C
last analyzed 2020-12-08 09:48 UTC

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	266
Duplicated Lines	0 %

Importance

Changes

Metric	Value
wmc	56
eloc	178
dl	0
loc	266
rs	5.5199
c	0
b	0
f	0

1 Function

Rating	Name	Duplication	Size	Complexity
F	main()	0	245	56

How to fix Complexity

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


########################################################################################################################
# PROCEDURES
# Step 1: get all meters
# for each meter in list:
#   Step 2: get the latest start_datetime_utc
#   Step 3: get all energy 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 meters
        ################################################################################################################
        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 meter_billing " + 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, energy_category_id, cost_center_id "
                                     " FROM tbl_meters "
                                     " ORDER BY id ")
            rows_meters = cursor_system_db.fetchall()

            if rows_meters is None or len(rows_meters) == 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

            meter_list = list()
            for row in rows_meters:
                meter_list.append({"id": row[0],
                                   "name": row[1],
                                   "energy_category_id": row[2],
                                   "cost_center_id": row[3]})

        except Exception as e:
            logger.error("Error in step 1.2 of meter_billing " + 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 meters 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 meter_billing " + 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 meter_billing " + 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 meter in meter_list:

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

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

            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_meter_hourly "
                     " WHERE meter_id = %s AND start_datetime_utc >= %s "
                     " ORDER BY id ")
            cursor_energy_db.execute(query, (meter['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 meter loop
                continue

            energy_dict = dict()
            energy_category_list = list()
            energy_category_list.append(meter['energy_category_id'])
            end_datetime_utc = start_datetime_utc
            for row_hourly in rows_hourly:
                current_datetime_utc = row_hourly[0]
                actual_value = row_hourly[1]
                if energy_dict.get(current_datetime_utc) is None:
                    energy_dict[current_datetime_utc] = dict()
                energy_dict[current_datetime_utc][meter['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(meter['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_meter_hourly "
                                  "             (meter_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(meter['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 meter_billing " + str(e))
                    # break the for meter loop
                    break

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

myems / myems-aggregation

meter_billing C last analyzed 2020-12-08 09:48 UTC

Complexity

Size/Duplication

Importance

1 Function

How to fix Complexity

Complexity

Duplication Side-by-Side

Filter issues like

meter_billing C
last analyzed 2020-12-08 09:48 UTC