Issues in microgridsbilling.py (master) - Issues in master - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Issues (1577)

myems-api/reports/microgridsbilling.py (2 issues)

Labels

Duplication 1

Severity

from datetime import datetime, timedelta, timezone
from decimal import Decimal
import falcon
import mysql.connector
import simplejson as json
from core import utilities
import config
from core.useractivity import access_control, api_key_control


class Reporting:

    def __init__(self):
        """Initializes Class"""
        pass

    @staticmethod
    def on_options(req, resp):
        _ = req
        resp.status = falcon.HTTP_200

    ####################################################################################################################
    # PROCEDURES
    # Step 1: valid parameters
    # Step 2: query the microgrid list
    # Step 3: query charge billing data in 7 days
    # Step 4: query discharge billing data in 7 days
    # Step 5: query charge billing data in this month
    # Step 6: query discharge billing data in this month
    # Step 7: query charge billing data in this year
    # Step 8: query discharge billing data in this year
    # Step 9: construct the report
    ####################################################################################################################
    @staticmethod
    def on_get(req, resp):
        if 'API-KEY' not in req.headers or \
                not isinstance(req.headers['API-KEY'], str) or \
                len(str.strip(req.headers['API-KEY'])) == 0:
            access_control(req)
        else:
            api_key_control(req)
        user_uuid = req.params.get('useruuid')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if user_uuid is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_USER_UUID')
        else:
            user_uuid = str.strip(user_uuid)
            if len(user_uuid) != 36:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_USER_UUID')

        ################################################################################################################
        # Step 2: query the microgrid list
        ################################################################################################################
        cnx_user = mysql.connector.connect(**config.myems_user_db)
        cursor_user = cnx_user.cursor()
        cursor_user.execute(" SELECT id, is_admin, privilege_id "
                            " FROM tbl_users "
                            " WHERE uuid = %s ", (user_uuid,))
        row_user = cursor_user.fetchone()
        if row_user is None:
            if cursor_user:
                cursor_user.close()
            if cnx_user:
                cnx_user.close()
            raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.USER_NOT_FOUND')

        user = {'id': row_user[0], 'is_admin': row_user[1], 'privilege_id': row_user[2]}

        # Get microgrids
        cnx_system_db = mysql.connector.connect(**config.myems_system_db)
        cursor_system_db = cnx_system_db.cursor()
        query = (" SELECT m.id, m.name, m.uuid, "
                 "        m.address, m.postal_code, m.latitude, m.longitude, "
                 "        m.rated_capacity, m.rated_power, m.serial_number, m.description "
                 " FROM tbl_microgrids m, tbl_microgrids_users mu "
                 " WHERE m.id = mu.microgrid_id AND mu.user_id = %s "
                 " ORDER BY id ")
        cursor_system_db.execute(query, (user['id'],))
        rows_microgrids = cursor_system_db.fetchall()

        microgrid_list = list()
        microgrid_names = list()
        if rows_microgrids is not None and len(rows_microgrids) > 0:
            for row in rows_microgrids:
                meta_result = {"id": row[0],
                               "name": row[1],
                               "uuid": row[2],
                               "address": row[3],
                               "postal_code": row[4],
                               "latitude": row[5],
                               "longitude": row[6],
                               "rated_capacity": row[7],
                               "rated_power": row[8],
                               "serial_number": row[9],
                               "description": row[10]}
                microgrid_list.append(meta_result)
                microgrid_names.append(row[1])
        ################################################################################################################
        # Step 3: query charge billing data in 7 days
        ################################################################################################################
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset
        reporting = dict()
        reporting['charge_7_days'] = dict()
        reporting['charge_this_month'] = dict()
        reporting['charge_this_year'] = dict()
        reporting['discharge_7_days'] = dict()
        reporting['discharge_this_month'] = dict()
        reporting['discharge_this_year'] = dict()

        end_datetime_utc = datetime.utcnow()
        end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
        period_type = 'daily'
        start_datetime_local = end_datetime_local.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=6)
        start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
        print('start_datetime_local:' + start_datetime_local.isoformat())
        print('end_datetime_local:' + end_datetime_local.isoformat())
        print('start_datetime_utc:' + start_datetime_utc.isoformat())
        print('end_datetime_utc:' + end_datetime_utc.isoformat())

        cnx_billing_db = mysql.connector.connect(**config.myems_billing_db)
        cursor_billing_db = cnx_billing_db.cursor()

        reporting['charge_7_days'] = dict()
        reporting['charge_7_days']['timestamps_array'] = list()
        reporting['charge_7_days']['values_array'] = list()

        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_charge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_charge_hourly = cursor_billing_db.fetchall()

            rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
                                                                                 start_datetime_utc,
                                                                                 end_datetime_utc,
                                                                                 period_type)
            for row_charge_periodically in rows_charge_periodically:
                current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
                timestamps.append(current_datetime)

                values.append(actual_value)
            reporting['charge_7_days']['timestamps_array'].append(timestamps)
            reporting['charge_7_days']['values_array'].append(values)
        ################################################################################################################
        # Step 4: query discharge billing data in 7 days
        ################################################################################################################
        reporting['discharge_7_days'] = dict()
        reporting['discharge_7_days']['timestamps_array'] = list()
        reporting['discharge_7_days']['values_array'] = list()
        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_discharge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_charge_hourly = cursor_billing_db.fetchall()

            rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
                                                                                 start_datetime_utc,
                                                                                 end_datetime_utc,
                                                                                 period_type)

            for row_charge_periodically in rows_charge_periodically:
                current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
                timestamps.append(current_datetime)
                values.append(actual_value)
            reporting['discharge_7_days']['timestamps_array'].append(timestamps)
            reporting['discharge_7_days']['values_array'].append(values)

        ################################################################################################################
        # Step 5: query charge billing data in this month
        ################################################################################################################
        end_datetime_utc = datetime.utcnow()
        end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
        period_type = 'daily'
        start_datetime_local = end_datetime_local.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
        print('start_datetime_local:' + start_datetime_local.isoformat())
        print('end_datetime_local:' + end_datetime_local.isoformat())
        print('start_datetime_utc:' + start_datetime_utc.isoformat())
        print('end_datetime_utc:' + end_datetime_utc.isoformat())

        reporting['charge_this_month'] = dict()
        reporting['charge_this_month']['timestamps_array'] = list()
        reporting['charge_this_month']['values_array'] = list()

        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_charge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_charge_hourly = cursor_billing_db.fetchall()

            rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
                                                                                 start_datetime_utc,
                                                                                 end_datetime_utc,
                                                                                 period_type)

            for row_charge_periodically in rows_charge_periodically:
                current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
                timestamps.append(current_datetime)
                values.append(actual_value)
            reporting['charge_this_month']['timestamps_array'].append(timestamps)
            reporting['charge_this_month']['values_array'].append(values)

        ################################################################################################################
        # Step 6: query discharge billing data in this month
        ################################################################################################################
        reporting['discharge_this_month'] = dict()
        reporting['discharge_this_month']['timestamps_array'] = list()
        reporting['discharge_this_month']['values_array'] = list()

        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_discharge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_discharge_hourly = cursor_billing_db.fetchall()

            rows_discharge_periodically = utilities.aggregate_hourly_data_by_period(rows_discharge_hourly,
                                                                                    start_datetime_utc,
                                                                                    end_datetime_utc,
                                                                                    period_type)

            for row_discharge_periodically in rows_discharge_periodically:
                current_datetime_local = row_discharge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_discharge_periodically[1] is None else row_discharge_periodically[1]
                timestamps.append(current_datetime)
                values.append(actual_value)
            reporting['discharge_this_month']['timestamps_array'].append(timestamps)
            reporting['discharge_this_month']['values_array'].append(values)

        ################################################################################################################
        # Step 7: query charge billing data in this year
        ################################################################################################################
        end_datetime_utc = datetime.utcnow()
        end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
        period_type = 'monthly'
        start_datetime_local = end_datetime_local.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)
        start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
        print('start_datetime_local:' + start_datetime_local.isoformat())
        print('end_datetime_local:' + end_datetime_local.isoformat())
        print('start_datetime_utc:' + start_datetime_utc.isoformat())
        print('end_datetime_utc:' + end_datetime_utc.isoformat())

        reporting['charge_this_year'] = dict()
        reporting['charge_this_year']['timestamps_array'] = list()
        reporting['charge_this_year']['values_array'] = list()

        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_charge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_charge_hourly = cursor_billing_db.fetchall()

            rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
                                                                                 start_datetime_utc,
                                                                                 end_datetime_utc,
                                                                                 period_type)
            for row_charge_periodically in rows_charge_periodically:
                current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
                timestamps.append(current_datetime)
                values.append(actual_value)
            reporting['charge_this_year']['timestamps_array'].append(timestamps)
            reporting['charge_this_year']['values_array'].append(values)

        ################################################################################################################
        # Step 8: query discharge billing data in this month
        ################################################################################################################
        reporting['discharge_this_year'] = dict()
        reporting['discharge_this_year']['timestamps_array'] = list()
        reporting['discharge_this_year']['values_array'] = list()

        for microgrid in microgrid_list:
            timestamps = list()
            values = list()
            query = (" SELECT start_datetime_utc, actual_value "
                     " FROM tbl_microgrid_discharge_hourly "
                     " WHERE microgrid_id = %s "
                     " AND start_datetime_utc >= %s "
                     " AND start_datetime_utc < %s "
                     " ORDER BY start_datetime_utc ")
            cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
            rows_discharge_hourly = cursor_billing_db.fetchall()

            rows_discharge_periodically = utilities.aggregate_hourly_data_by_period(rows_discharge_hourly,
                                                                                    start_datetime_utc,
                                                                                    end_datetime_utc,
                                                                                    period_type)
            for row_discharge_periodically in rows_discharge_periodically:
                current_datetime_local = row_discharge_periodically[0].replace(tzinfo=timezone.utc) + \
                                         timedelta(minutes=timezone_offset)
                if period_type == 'hourly':
                    current_datetime = current_datetime_local.isoformat()[0:19]
                elif period_type == 'daily':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'weekly':
                    current_datetime = current_datetime_local.isoformat()[0:10]
                elif period_type == 'monthly':
                    current_datetime = current_datetime_local.isoformat()[0:7]
                elif period_type == 'yearly':
                    current_datetime = current_datetime_local.isoformat()[0:4]

                actual_value = Decimal(0.0) if row_discharge_periodically[1] is None else row_discharge_periodically[1]
                timestamps.append(current_datetime)
                values.append(actual_value)
            reporting['discharge_this_year']['timestamps_array'].append(timestamps)
            reporting['discharge_this_year']['values_array'].append(values)

        ################################################################################################################
        # Step 9: construct the report
        ################################################################################################################
        if cursor_system_db:
            cursor_system_db.close()
        if cnx_system_db:
            cnx_system_db.close()

        if cursor_billing_db:
            cursor_billing_db.close()
        if cnx_billing_db:
            cnx_billing_db.close()

        result = dict()
        result['microgrid_names'] = microgrid_names
        result['reporting'] = reporting
        resp.text = json.dumps(result)


1		from datetime import datetime, timedelta, timezone
2		from decimal import Decimal
3		import falcon
4		import mysql.connector
5		import simplejson as json
6		from core import utilities
7		import config
8		from core.useractivity import access_control, api_key_control
9
10
11	View Code Duplication	class Reporting:
		0 ignored issues – show Duplication introduced 2024-11-16 14:16 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
12		def __init__(self):
13		"""Initializes Class"""
14		pass
15
16		@staticmethod
17		def on_options(req, resp):
18		_ = req
19		resp.status = falcon.HTTP_200
20
21		####################################################################################################################
22		# PROCEDURES
23		# Step 1: valid parameters
24		# Step 2: query the microgrid list
25		# Step 3: query charge billing data in 7 days
26		# Step 4: query discharge billing data in 7 days
27		# Step 5: query charge billing data in this month
28		# Step 6: query discharge billing data in this month
29		# Step 7: query charge billing data in this year
30		# Step 8: query discharge billing data in this year
31		# Step 9: construct the report
32		####################################################################################################################
33		@staticmethod
34		def on_get(req, resp):
35		if 'API-KEY' not in req.headers or \
36		not isinstance(req.headers['API-KEY'], str) or \
37		len(str.strip(req.headers['API-KEY'])) == 0:
38		access_control(req)
39		else:
40		api_key_control(req)
41		user_uuid = req.params.get('useruuid')
42
43		################################################################################################################
44		# Step 1: valid parameters
45		################################################################################################################
46		if user_uuid is None:
47		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST', description='API.INVALID_USER_UUID')
48		else:
49		user_uuid = str.strip(user_uuid)
50		if len(user_uuid) != 36:
51		raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
52		description='API.INVALID_USER_UUID')
53
54		################################################################################################################
55		# Step 2: query the microgrid list
56		################################################################################################################
57		cnx_user = mysql.connector.connect(**config.myems_user_db)
58		cursor_user = cnx_user.cursor()
59		cursor_user.execute(" SELECT id, is_admin, privilege_id "
60		" FROM tbl_users "
61		" WHERE uuid = %s ", (user_uuid,))
62		row_user = cursor_user.fetchone()
63		if row_user is None:
64		if cursor_user:
65		cursor_user.close()
66		if cnx_user:
67		cnx_user.close()
68		raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
69		description='API.USER_NOT_FOUND')
70
71		user = {'id': row_user[0], 'is_admin': row_user[1], 'privilege_id': row_user[2]}
72
73		# Get microgrids
74		cnx_system_db = mysql.connector.connect(**config.myems_system_db)
75		cursor_system_db = cnx_system_db.cursor()
76		query = (" SELECT m.id, m.name, m.uuid, "
77		" m.address, m.postal_code, m.latitude, m.longitude, "
78		" m.rated_capacity, m.rated_power, m.serial_number, m.description "
79		" FROM tbl_microgrids m, tbl_microgrids_users mu "
80		" WHERE m.id = mu.microgrid_id AND mu.user_id = %s "
81		" ORDER BY id ")
82		cursor_system_db.execute(query, (user['id'],))
83		rows_microgrids = cursor_system_db.fetchall()
84
85		microgrid_list = list()
86		microgrid_names = list()
87		if rows_microgrids is not None and len(rows_microgrids) > 0:
88		for row in rows_microgrids:
89		meta_result = {"id": row[0],
90		"name": row[1],
91		"uuid": row[2],
92		"address": row[3],
93		"postal_code": row[4],
94		"latitude": row[5],
95		"longitude": row[6],
96		"rated_capacity": row[7],
97		"rated_power": row[8],
98		"serial_number": row[9],
99		"description": row[10]}
100		microgrid_list.append(meta_result)
101		microgrid_names.append(row[1])
102		################################################################################################################
103		# Step 3: query charge billing data in 7 days
104		################################################################################################################
105		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
106		if config.utc_offset[0] == '-':
107		timezone_offset = -timezone_offset
108		reporting = dict()
109		reporting['charge_7_days'] = dict()
110		reporting['charge_this_month'] = dict()
111		reporting['charge_this_year'] = dict()
112		reporting['discharge_7_days'] = dict()
113		reporting['discharge_this_month'] = dict()
114		reporting['discharge_this_year'] = dict()
115
116		end_datetime_utc = datetime.utcnow()
117		end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
118		period_type = 'daily'
119		start_datetime_local = end_datetime_local.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=6)
120		start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
121		print('start_datetime_local:' + start_datetime_local.isoformat())
122		print('end_datetime_local:' + end_datetime_local.isoformat())
123		print('start_datetime_utc:' + start_datetime_utc.isoformat())
124		print('end_datetime_utc:' + end_datetime_utc.isoformat())
125
126		cnx_billing_db = mysql.connector.connect(**config.myems_billing_db)
127		cursor_billing_db = cnx_billing_db.cursor()
128
129		reporting['charge_7_days'] = dict()
130		reporting['charge_7_days']['timestamps_array'] = list()
131		reporting['charge_7_days']['values_array'] = list()
132
133		for microgrid in microgrid_list:
134		timestamps = list()
135		values = list()
136		query = (" SELECT start_datetime_utc, actual_value "
137		" FROM tbl_microgrid_charge_hourly "
138		" WHERE microgrid_id = %s "
139		" AND start_datetime_utc >= %s "
140		" AND start_datetime_utc < %s "
141		" ORDER BY start_datetime_utc ")
142		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
143		rows_charge_hourly = cursor_billing_db.fetchall()
144
145		rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
146		start_datetime_utc,
147		end_datetime_utc,
148		period_type)
149		for row_charge_periodically in rows_charge_periodically:
150		current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
151		timedelta(minutes=timezone_offset)
152		if period_type == 'hourly':
153		current_datetime = current_datetime_local.isoformat()[0:19]
154		elif period_type == 'daily':
155		current_datetime = current_datetime_local.isoformat()[0:10]
156		elif period_type == 'weekly':
157		current_datetime = current_datetime_local.isoformat()[0:10]
158		elif period_type == 'monthly':
159		current_datetime = current_datetime_local.isoformat()[0:7]
160		elif period_type == 'yearly':
161		current_datetime = current_datetime_local.isoformat()[0:4]
162
163		actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
164		timestamps.append(current_datetime)
		0 ignored issues – show introduced 2025-02-22 13:57 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `current_datetime` does not seem to be defined for all execution paths. Loading history...
165		values.append(actual_value)
166		reporting['charge_7_days']['timestamps_array'].append(timestamps)
167		reporting['charge_7_days']['values_array'].append(values)
168		################################################################################################################
169		# Step 4: query discharge billing data in 7 days
170		################################################################################################################
171		reporting['discharge_7_days'] = dict()
172		reporting['discharge_7_days']['timestamps_array'] = list()
173		reporting['discharge_7_days']['values_array'] = list()
174		for microgrid in microgrid_list:
175		timestamps = list()
176		values = list()
177		query = (" SELECT start_datetime_utc, actual_value "
178		" FROM tbl_microgrid_discharge_hourly "
179		" WHERE microgrid_id = %s "
180		" AND start_datetime_utc >= %s "
181		" AND start_datetime_utc < %s "
182		" ORDER BY start_datetime_utc ")
183		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
184		rows_charge_hourly = cursor_billing_db.fetchall()
185
186		rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
187		start_datetime_utc,
188		end_datetime_utc,
189		period_type)
190
191		for row_charge_periodically in rows_charge_periodically:
192		current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
193		timedelta(minutes=timezone_offset)
194		if period_type == 'hourly':
195		current_datetime = current_datetime_local.isoformat()[0:19]
196		elif period_type == 'daily':
197		current_datetime = current_datetime_local.isoformat()[0:10]
198		elif period_type == 'weekly':
199		current_datetime = current_datetime_local.isoformat()[0:10]
200		elif period_type == 'monthly':
201		current_datetime = current_datetime_local.isoformat()[0:7]
202		elif period_type == 'yearly':
203		current_datetime = current_datetime_local.isoformat()[0:4]
204
205		actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
206		timestamps.append(current_datetime)
207		values.append(actual_value)
208		reporting['discharge_7_days']['timestamps_array'].append(timestamps)
209		reporting['discharge_7_days']['values_array'].append(values)
210
211		################################################################################################################
212		# Step 5: query charge billing data in this month
213		################################################################################################################
214		end_datetime_utc = datetime.utcnow()
215		end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
216		period_type = 'daily'
217		start_datetime_local = end_datetime_local.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
218		start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
219		print('start_datetime_local:' + start_datetime_local.isoformat())
220		print('end_datetime_local:' + end_datetime_local.isoformat())
221		print('start_datetime_utc:' + start_datetime_utc.isoformat())
222		print('end_datetime_utc:' + end_datetime_utc.isoformat())
223
224		reporting['charge_this_month'] = dict()
225		reporting['charge_this_month']['timestamps_array'] = list()
226		reporting['charge_this_month']['values_array'] = list()
227
228		for microgrid in microgrid_list:
229		timestamps = list()
230		values = list()
231		query = (" SELECT start_datetime_utc, actual_value "
232		" FROM tbl_microgrid_charge_hourly "
233		" WHERE microgrid_id = %s "
234		" AND start_datetime_utc >= %s "
235		" AND start_datetime_utc < %s "
236		" ORDER BY start_datetime_utc ")
237		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
238		rows_charge_hourly = cursor_billing_db.fetchall()
239
240		rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
241		start_datetime_utc,
242		end_datetime_utc,
243		period_type)
244
245		for row_charge_periodically in rows_charge_periodically:
246		current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
247		timedelta(minutes=timezone_offset)
248		if period_type == 'hourly':
249		current_datetime = current_datetime_local.isoformat()[0:19]
250		elif period_type == 'daily':
251		current_datetime = current_datetime_local.isoformat()[0:10]
252		elif period_type == 'weekly':
253		current_datetime = current_datetime_local.isoformat()[0:10]
254		elif period_type == 'monthly':
255		current_datetime = current_datetime_local.isoformat()[0:7]
256		elif period_type == 'yearly':
257		current_datetime = current_datetime_local.isoformat()[0:4]
258
259		actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
260		timestamps.append(current_datetime)
261		values.append(actual_value)
262		reporting['charge_this_month']['timestamps_array'].append(timestamps)
263		reporting['charge_this_month']['values_array'].append(values)
264
265		################################################################################################################
266		# Step 6: query discharge billing data in this month
267		################################################################################################################
268		reporting['discharge_this_month'] = dict()
269		reporting['discharge_this_month']['timestamps_array'] = list()
270		reporting['discharge_this_month']['values_array'] = list()
271
272		for microgrid in microgrid_list:
273		timestamps = list()
274		values = list()
275		query = (" SELECT start_datetime_utc, actual_value "
276		" FROM tbl_microgrid_discharge_hourly "
277		" WHERE microgrid_id = %s "
278		" AND start_datetime_utc >= %s "
279		" AND start_datetime_utc < %s "
280		" ORDER BY start_datetime_utc ")
281		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
282		rows_discharge_hourly = cursor_billing_db.fetchall()
283
284		rows_discharge_periodically = utilities.aggregate_hourly_data_by_period(rows_discharge_hourly,
285		start_datetime_utc,
286		end_datetime_utc,
287		period_type)
288
289		for row_discharge_periodically in rows_discharge_periodically:
290		current_datetime_local = row_discharge_periodically[0].replace(tzinfo=timezone.utc) + \
291		timedelta(minutes=timezone_offset)
292		if period_type == 'hourly':
293		current_datetime = current_datetime_local.isoformat()[0:19]
294		elif period_type == 'daily':
295		current_datetime = current_datetime_local.isoformat()[0:10]
296		elif period_type == 'weekly':
297		current_datetime = current_datetime_local.isoformat()[0:10]
298		elif period_type == 'monthly':
299		current_datetime = current_datetime_local.isoformat()[0:7]
300		elif period_type == 'yearly':
301		current_datetime = current_datetime_local.isoformat()[0:4]
302
303		actual_value = Decimal(0.0) if row_discharge_periodically[1] is None else row_discharge_periodically[1]
304		timestamps.append(current_datetime)
305		values.append(actual_value)
306		reporting['discharge_this_month']['timestamps_array'].append(timestamps)
307		reporting['discharge_this_month']['values_array'].append(values)
308
309		################################################################################################################
310		# Step 7: query charge billing data in this year
311		################################################################################################################
312		end_datetime_utc = datetime.utcnow()
313		end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
314		period_type = 'monthly'
315		start_datetime_local = end_datetime_local.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)
316		start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)
317		print('start_datetime_local:' + start_datetime_local.isoformat())
318		print('end_datetime_local:' + end_datetime_local.isoformat())
319		print('start_datetime_utc:' + start_datetime_utc.isoformat())
320		print('end_datetime_utc:' + end_datetime_utc.isoformat())
321
322		reporting['charge_this_year'] = dict()
323		reporting['charge_this_year']['timestamps_array'] = list()
324		reporting['charge_this_year']['values_array'] = list()
325
326		for microgrid in microgrid_list:
327		timestamps = list()
328		values = list()
329		query = (" SELECT start_datetime_utc, actual_value "
330		" FROM tbl_microgrid_charge_hourly "
331		" WHERE microgrid_id = %s "
332		" AND start_datetime_utc >= %s "
333		" AND start_datetime_utc < %s "
334		" ORDER BY start_datetime_utc ")
335		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
336		rows_charge_hourly = cursor_billing_db.fetchall()
337
338		rows_charge_periodically = utilities.aggregate_hourly_data_by_period(rows_charge_hourly,
339		start_datetime_utc,
340		end_datetime_utc,
341		period_type)
342		for row_charge_periodically in rows_charge_periodically:
343		current_datetime_local = row_charge_periodically[0].replace(tzinfo=timezone.utc) + \
344		timedelta(minutes=timezone_offset)
345		if period_type == 'hourly':
346		current_datetime = current_datetime_local.isoformat()[0:19]
347		elif period_type == 'daily':
348		current_datetime = current_datetime_local.isoformat()[0:10]
349		elif period_type == 'weekly':
350		current_datetime = current_datetime_local.isoformat()[0:10]
351		elif period_type == 'monthly':
352		current_datetime = current_datetime_local.isoformat()[0:7]
353		elif period_type == 'yearly':
354		current_datetime = current_datetime_local.isoformat()[0:4]
355
356		actual_value = Decimal(0.0) if row_charge_periodically[1] is None else row_charge_periodically[1]
357		timestamps.append(current_datetime)
358		values.append(actual_value)
359		reporting['charge_this_year']['timestamps_array'].append(timestamps)
360		reporting['charge_this_year']['values_array'].append(values)
361
362		################################################################################################################
363		# Step 8: query discharge billing data in this month
364		################################################################################################################
365		reporting['discharge_this_year'] = dict()
366		reporting['discharge_this_year']['timestamps_array'] = list()
367		reporting['discharge_this_year']['values_array'] = list()
368
369		for microgrid in microgrid_list:
370		timestamps = list()
371		values = list()
372		query = (" SELECT start_datetime_utc, actual_value "
373		" FROM tbl_microgrid_discharge_hourly "
374		" WHERE microgrid_id = %s "
375		" AND start_datetime_utc >= %s "
376		" AND start_datetime_utc < %s "
377		" ORDER BY start_datetime_utc ")
378		cursor_billing_db.execute(query, (microgrid['id'], start_datetime_utc, end_datetime_utc))
379		rows_discharge_hourly = cursor_billing_db.fetchall()
380
381		rows_discharge_periodically = utilities.aggregate_hourly_data_by_period(rows_discharge_hourly,
382		start_datetime_utc,
383		end_datetime_utc,
384		period_type)
385		for row_discharge_periodically in rows_discharge_periodically:
386		current_datetime_local = row_discharge_periodically[0].replace(tzinfo=timezone.utc) + \
387		timedelta(minutes=timezone_offset)
388		if period_type == 'hourly':
389		current_datetime = current_datetime_local.isoformat()[0:19]
390		elif period_type == 'daily':
391		current_datetime = current_datetime_local.isoformat()[0:10]
392		elif period_type == 'weekly':
393		current_datetime = current_datetime_local.isoformat()[0:10]
394		elif period_type == 'monthly':
395		current_datetime = current_datetime_local.isoformat()[0:7]
396		elif period_type == 'yearly':
397		current_datetime = current_datetime_local.isoformat()[0:4]
398
399		actual_value = Decimal(0.0) if row_discharge_periodically[1] is None else row_discharge_periodically[1]
400		timestamps.append(current_datetime)
401		values.append(actual_value)
402		reporting['discharge_this_year']['timestamps_array'].append(timestamps)
403		reporting['discharge_this_year']['values_array'].append(values)
404
405		################################################################################################################
406		# Step 9: construct the report
407		################################################################################################################
408		if cursor_system_db:
409		cursor_system_db.close()
410		if cnx_system_db:
411		cnx_system_db.close()
412
413		if cursor_billing_db:
414		cursor_billing_db.close()
415		if cnx_billing_db:
416		cnx_billing_db.close()
417
418		result = dict()
419		result['microgrid_names'] = microgrid_names
420		result['reporting'] = reporting
421		resp.text = json.dumps(result)
422

MyEMS / myems

Issues (1577)

myems-api/reports/microgridsbilling.py (2 issues)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like