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

Issues (1588)

reports/photovoltaicpowerstationdetails.py (1 issue)

Severity

Unknown 1

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


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 photovoltaic power station
    # Step 5: query associated grids on containers
    # Step 6: query associated loads on containers
    # Step 7: query associated invertors on the photovoltaic power station
    #     Step 7.1 query energy indicator data
    #     Step 7.2 query revenue indicator data
    # Step 8: query associated points data on the photovoltaic power station
    # 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)
        print(req.params)
        # this procedure accepts energy storage power station id or uuid
        photovoltaic_power_station_id = req.params.get('id')
        photovoltaic_power_station_uuid = req.params.get('uuid')

        ################################################################################################################
        # Step 1: valid parameters
        ################################################################################################################
        if photovoltaic_power_station_id is None and photovoltaic_power_station_uuid is None:
            raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_photovoltaic_POWER_STATION_ID')

        if photovoltaic_power_station_id is not None:
            photovoltaic_power_station_id = str.strip(photovoltaic_power_station_id)
            if not photovoltaic_power_station_id.isdigit() or int(photovoltaic_power_station_id) <= 0:
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_photovoltaic_POWER_STATION_ID')

        if photovoltaic_power_station_uuid is not None:
            regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I)
            match = regex.match(str.strip(photovoltaic_power_station_uuid))
            if not bool(match):
                raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
                                       description='API.INVALID_photovoltaic_POWER_STATION_UUID')

        reporting_start_datetime_utc = datetime.utcnow() - timedelta(days=3)
        reporting_end_datetime_utc = datetime.utcnow()

        ################################################################################################################
        # Step 2: query the photovoltaic power station
        ################################################################################################################
        cnx_system = mysql.connector.connect(**config.myems_system_db)
        cursor_system = cnx_system.cursor()

        cnx_energy = mysql.connector.connect(**config.myems_energy_db)
        cursor_energy = cnx_energy.cursor()

        cnx_billing = mysql.connector.connect(**config.myems_billing_db)
        cursor_billing = cnx_billing.cursor()

        cnx_historical = mysql.connector.connect(**config.myems_historical_db)
        cursor_historical = cnx_historical.cursor()

        if photovoltaic_power_station_id is not None:
            query = (" SELECT e.id, e.name, e.uuid, "
                     "        e.address, e.latitude, e.longitude, e.rated_capacity, e.rated_power, "
                     "        s.source_code, e.description, e.phase_of_lifecycle "
                     " FROM tbl_photovoltaic_power_stations e, tbl_svgs s "
                     " WHERE e.svg_id = s.id AND e.id = %s ")
            cursor_system.execute(query, (photovoltaic_power_station_id,))
            row = cursor_system.fetchone()
        elif photovoltaic_power_station_uuid is not None:
            query = (" SELECT e.id, e.name, e.uuid, "
                     "        e.address, e.latitude, e.longitude, e.rated_capacity, e.rated_power, "
                     "        s.source_code, e.description, e.phase_of_lifecycle "
                     " FROM tbl_photovoltaic_power_stations e, tbl_svgs s "
                     " WHERE e.svg_id = s.id AND e.uuid = %s ")
            cursor_system.execute(query, (photovoltaic_power_station_uuid,))
            row = cursor_system.fetchone()

        if row is None:

            cursor_system.close()
            cnx_system.close()
            raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.photovoltaic_POWER_STATION_NOT_FOUND')
        else:
            photovoltaic_power_station_id = row[0]
            meta_result = {"id": row[0],
                           "name": row[1],
                           "uuid": row[2],
                           "address": row[3],
                           "latitude": row[4],
                           "longitude": row[5],
                           "rated_capacity": row[6],
                           "rated_power": row[7],
                           "svg": row[8],
                           "description": row[9],
                           "phase_of_lifecycle": row[10],
                           "qrcode": 'energystoragepowerstation:' + row[2]}

        point_list = list()
        meter_list = list()

        # query all energy categories in system
        cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
                              " FROM tbl_energy_categories "
                              " ORDER BY id ", )
        rows_energy_categories = cursor_system.fetchall()
        if rows_energy_categories is None or len(rows_energy_categories) == 0:
            if cursor_system:
                cursor_system.close()
            if cnx_system:
                cnx_system.close()
            raise falcon.HTTPError(status=falcon.HTTP_404,
                                   title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')
        energy_category_dict = dict()
        for row_energy_category in rows_energy_categories:
            energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
                                                            "unit_of_measure": row_energy_category[2],
                                                            "kgce": row_energy_category[3],
                                                            "kgco2e": row_energy_category[4]}

        ################################################################################################################
        # Step 5: query associated grids on the photovoltaic power station
        ################################################################################################################
        cursor_system.execute(" SELECT p.id, cg.name, p.units, p.object_type  "
                              " FROM tbl_photovoltaic_power_stations_grids cg, tbl_points p "
                              " WHERE cg.photovoltaic_power_station_id = %s AND cg.power_point_id = p.id ",
                              (photovoltaic_power_station_id,))
        rows_points = cursor_system.fetchall()
        if rows_points is not None and len(rows_points) > 0:
            for row_point in rows_points:
                point_list.append({"id": row_point[0],
                                   "name": row_point[1] + '.P',
                                   "units": row_point[2],
                                   "object_type": row_point[3]})

        cursor_system.execute(" SELECT m.id, cg.name, m.energy_category_id  "
                              " FROM tbl_photovoltaic_power_stations_grids cg, tbl_meters m "
                              " WHERE cg.photovoltaic_power_station_id = %s AND cg.buy_meter_id = m.id ",
                              (photovoltaic_power_station_id,))
        row_meter = cursor_system.fetchone()
        if row_meter is not None:
            meter_list.append({"id": row_meter[0],
                               "name": row_meter[1] + '.Buy',
                               "energy_category_id": row_meter[2]})

        cursor_system.execute(" SELECT m.id, cg.name, m.energy_category_id  "
                              " FROM tbl_photovoltaic_power_stations_grids cg, tbl_meters m "
                              " WHERE cg.photovoltaic_power_station_id = %s AND cg.sell_meter_id = m.id ",
                              (photovoltaic_power_station_id,))
        row_meter = cursor_system.fetchone()
        if row_meter is not None:
            meter_list.append({"id": row_meter[0],
                               "name": row_meter[1] + '.Sell',
                               "energy_category_id": row_meter[2]})

        ################################################################################################################
        # Step 6: query associated loads on the photovoltaic power station
        ################################################################################################################
        cursor_system.execute(" SELECT p.id, cl.name, p.units, p.object_type  "
                              " FROM tbl_photovoltaic_power_stations_loads cl, tbl_points p "
                              " WHERE cl.photovoltaic_power_station_id = %s AND cl.power_point_id = p.id ",
                              (photovoltaic_power_station_id,))
        rows_points = cursor_system.fetchall()
        if rows_points is not None and len(rows_points) > 0:
            for row_point in rows_points:
                point_list.append({"id": row_point[0],
                                   "name": row_point[1] + '.P',
                                   "units": row_point[2],
                                   "object_type": row_point[3]})

        cursor_system.execute(" SELECT m.id, cl.name, m.energy_category_id  "
                              " FROM tbl_photovoltaic_power_stations_loads cl, tbl_meters m "
                              " WHERE cl.photovoltaic_power_station_id = %s AND cl.meter_id = m.id ",
                              (photovoltaic_power_station_id,))
        row_meter = cursor_system.fetchone()
        if row_meter is not None:
            meter_list.append({"id": row_meter[0],
                               "name": row_meter[1],
                               "energy_category_id": row_meter[2]})

        ################################################################################################################
        # Step 6: query associated invertors on the photovoltaic power station
        ################################################################################################################
        cursor_system.execute(" SELECT p.id, ppai.name, p.units, p.object_type  "
                              " FROM tbl_photovoltaic_power_stations_invertors ppai, tbl_points p "
                              " WHERE ppai.photovoltaic_power_station_id = %s AND ppai.active_power_point_id = p.id ",
                              (photovoltaic_power_station_id,))
        rows_points = cursor_system.fetchall()
        if rows_points is not None and len(rows_points) > 0:
            for row_point in rows_points:
                point_list.append({"id": row_point[0],
                                   "name": row_point[1] + '.P',
                                   "units": row_point[2],
                                   "object_type": row_point[3]})

        ################################################################################################################
        # Step 7 query energy indicator data
        ################################################################################################################
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        today_end_datetime_utc = datetime.utcnow()
        today_end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
        today_start_datetime_local = today_end_datetime_local.replace(hour=0, minute=0, second=0, microsecond=0)
        today_start_datetime_utc = today_start_datetime_local - timedelta(minutes=timezone_offset)

        today_generation_energy_value = Decimal(0.0)
        total_generation_energy_value = Decimal(0.0)

        cursor_energy.execute(" SELECT SUM(actual_value) "
                              " FROM tbl_photovoltaic_power_station_generation_hourly "
                              " WHERE photovoltaic_power_station_id = %s "
                              "     AND start_datetime_utc >= %s "
                              "     AND start_datetime_utc < %s ",
                              (photovoltaic_power_station_id,
                               today_start_datetime_utc,
                               today_end_datetime_utc))
        row = cursor_energy.fetchone()
        if row is not None:
            today_generation_energy_value = row[0]
        cursor_energy.execute(" SELECT SUM(actual_value) "
                              " FROM tbl_photovoltaic_power_station_generation_hourly "
                              " WHERE photovoltaic_power_station_id = %s ",
                              (photovoltaic_power_station_id,))
        row = cursor_energy.fetchone()
        if row is not None:
            total_generation_energy_value = row[0]

        ################################################################################################################
        # Step 8 query revenue indicator data
        ################################################################################################################
        today_generation_revenue_value = Decimal(0.0)
        total_generation_revenue_value = Decimal(0.0)
        cursor_billing.execute(" SELECT SUM(actual_value) "
                               " FROM tbl_photovoltaic_power_station_generation_hourly "
                               " WHERE photovoltaic_power_station_id = %s "
                               "     AND start_datetime_utc >= %s "
                               "     AND start_datetime_utc < %s ",
                               (photovoltaic_power_station_id,
                                today_start_datetime_utc,
                                today_end_datetime_utc))
        row = cursor_billing.fetchone()
        if row is not None:
            today_generation_revenue_value = row[0]

        cursor_billing.execute(" SELECT SUM(actual_value) "
                               " FROM tbl_photovoltaic_power_station_generation_hourly "
                               " WHERE photovoltaic_power_station_id = %s ",
                               (photovoltaic_power_station_id,))
        row = cursor_billing.fetchone()
        if row is not None:
            total_generation_revenue_value = row[0]

        ################################################################################################################
        # Step 8: query parameters data on the photovoltaic power station
        ################################################################################################################
        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        parameters_data = dict()
        parameters_data['names'] = list()
        parameters_data['timestamps'] = list()
        parameters_data['values'] = list()

        for point in point_list:
            point_values = []
            point_timestamps = []
            if point['object_type'] == 'ENERGY_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_energy_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s "
                         " ORDER BY utc_date_time ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
                                                     timedelta(minutes=timezone_offset)
                    current_datetime_local = reporting_start_datetime_local

                    while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
                            timedelta(minutes=timezone_offset):
                        point_timestamps.append(current_datetime_local.isoformat()[5:16])
                        point_values.append(rows[0][1])
                        current_datetime_local += timedelta(minutes=1)

                    for index in range(len(rows) - 1):
                        while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
                                timedelta(minutes=timezone_offset):
                            point_timestamps.append(current_datetime_local.isoformat()[5:16])
                            point_values.append(rows[index][1])
                            current_datetime_local += timedelta(minutes=1)
            elif point['object_type'] == 'ANALOG_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_analog_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s "
                         " ORDER BY utc_date_time ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
                                                     timedelta(minutes=timezone_offset)
                    current_datetime_local = reporting_start_datetime_local

                    while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
                            timedelta(minutes=timezone_offset):
                        point_timestamps.append(current_datetime_local.isoformat()[5:16])
                        point_values.append(rows[0][1])
                        current_datetime_local += timedelta(minutes=1)

                    for index in range(len(rows) - 1):
                        while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
                                timedelta(minutes=timezone_offset):
                            point_timestamps.append(current_datetime_local.isoformat()[5:16])
                            point_values.append(rows[index][1])
                            current_datetime_local += timedelta(minutes=1)
            elif point['object_type'] == 'DIGITAL_VALUE':
                query = (" SELECT utc_date_time, actual_value "
                         " FROM tbl_digital_value "
                         " WHERE point_id = %s "
                         "       AND utc_date_time BETWEEN %s AND %s "
                         " ORDER BY utc_date_time ")
                cursor_historical.execute(query, (point['id'],
                                                  reporting_start_datetime_utc,
                                                  reporting_end_datetime_utc))
                rows = cursor_historical.fetchall()

                if rows is not None and len(rows) > 0:
                    reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
                                                     timedelta(minutes=timezone_offset)
                    current_datetime_local = reporting_start_datetime_local

                    while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
                            timedelta(minutes=timezone_offset):
                        point_timestamps.append(current_datetime_local.isoformat()[5:16])
                        point_values.append(rows[0][1])
                        current_datetime_local += timedelta(minutes=1)

                    for index in range(len(rows) - 1):
                        while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
                                timedelta(minutes=timezone_offset):
                            point_timestamps.append(current_datetime_local.isoformat()[5:16])
                            point_values.append(rows[index][1])
                            current_datetime_local += timedelta(minutes=1)

            parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
            parameters_data['timestamps'].append(point_timestamps)
            parameters_data['values'].append(point_values)

        if cursor_system:
            cursor_system.close()
        if cnx_system:
            cnx_system.close()

        if cursor_historical:
            cursor_historical.close()
        if cnx_historical:
            cnx_historical.close()
        ################################################################################################################
        # Step 9: construct the report
        ################################################################################################################
        result = dict()
        result['photovoltaic_power_station'] = meta_result
        result['reporting_period'] = dict()
        result['reporting_period']['names'] = list()
        result['reporting_period']['units'] = list()
        result['reporting_period']['subtotals'] = list()
        result['reporting_period']['increment_rates'] = list()
        result['reporting_period']['timestamps'] = list()
        result['reporting_period']['values'] = list()

        result['energy_indicators'] = dict()
        result['energy_indicators']['today_generation_energy_value'] = today_generation_energy_value
        result['energy_indicators']['total_generation_energy_value'] = total_generation_energy_value
        result['energy_indicators']['performance_ratio'] = \
            Decimal(100) * (today_generation_energy_value / meta_result['rated_capacity']) \
            if today_generation_energy_value is not None and meta_result['rated_capacity'] > 0 else None

        result['revenue_indicators'] = dict()
        result['revenue_indicators']['today_generation_revenue_value'] = today_generation_revenue_value
        result['revenue_indicators']['total_generation_revenue_value'] = total_generation_revenue_value

        result['parameters'] = {
            "names": parameters_data['names'],
            "timestamps": parameters_data['timestamps'],
            "values": parameters_data['values']
        }
        resp.text = json.dumps(result)


1			import re
2			from datetime import datetime, timedelta, timezone
3			from decimal import Decimal
4			import falcon
5			import mysql.connector
6			import simplejson as json
7			from core.useractivity import access_control, api_key_control
8			import config
9
10
11			class Reporting:
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 photovoltaic power station
25			# Step 5: query associated grids on containers
26			# Step 6: query associated loads on containers
27			# Step 7: query associated invertors on the photovoltaic power station
28			# Step 7.1 query energy indicator data
29			# Step 7.2 query revenue indicator data
30			# Step 8: query associated points data on the photovoltaic power station
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			print(req.params)
42			# this procedure accepts energy storage power station id or uuid
43			photovoltaic_power_station_id = req.params.get('id')
44			photovoltaic_power_station_uuid = req.params.get('uuid')
45
46			################################################################################################################
47			# Step 1: valid parameters
48			################################################################################################################
49			if photovoltaic_power_station_id is None and photovoltaic_power_station_uuid is None:
50			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
51			description='API.INVALID_photovoltaic_POWER_STATION_ID')
52
53			if photovoltaic_power_station_id is not None:
54			photovoltaic_power_station_id = str.strip(photovoltaic_power_station_id)
55			if not photovoltaic_power_station_id.isdigit() or int(photovoltaic_power_station_id) <= 0:
56			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
57			description='API.INVALID_photovoltaic_POWER_STATION_ID')
58
59			if photovoltaic_power_station_uuid is not None:
60			regex = re.compile(r'^[a-f0-9]{8}-?[a-f0-9]{4}-?4[a-f0-9]{3}-?[89ab][a-f0-9]{3}-?[a-f0-9]{12}\Z', re.I)
61			match = regex.match(str.strip(photovoltaic_power_station_uuid))
62			if not bool(match):
63			raise falcon.HTTPError(status=falcon.HTTP_400, title='API.BAD_REQUEST',
64			description='API.INVALID_photovoltaic_POWER_STATION_UUID')
65
66			reporting_start_datetime_utc = datetime.utcnow() - timedelta(days=3)
67			reporting_end_datetime_utc = datetime.utcnow()
68
69			################################################################################################################
70			# Step 2: query the photovoltaic power station
71			################################################################################################################
72			cnx_system = mysql.connector.connect(**config.myems_system_db)
73			cursor_system = cnx_system.cursor()
74
75			cnx_energy = mysql.connector.connect(**config.myems_energy_db)
76			cursor_energy = cnx_energy.cursor()
77
78			cnx_billing = mysql.connector.connect(**config.myems_billing_db)
79			cursor_billing = cnx_billing.cursor()
80
81			cnx_historical = mysql.connector.connect(**config.myems_historical_db)
82			cursor_historical = cnx_historical.cursor()
83
84			if photovoltaic_power_station_id is not None:
85			query = (" SELECT e.id, e.name, e.uuid, "
86			" e.address, e.latitude, e.longitude, e.rated_capacity, e.rated_power, "
87			" s.source_code, e.description, e.phase_of_lifecycle "
88			" FROM tbl_photovoltaic_power_stations e, tbl_svgs s "
89			" WHERE e.svg_id = s.id AND e.id = %s ")
90			cursor_system.execute(query, (photovoltaic_power_station_id,))
91			row = cursor_system.fetchone()
92			elif photovoltaic_power_station_uuid is not None:
93			query = (" SELECT e.id, e.name, e.uuid, "
94			" e.address, e.latitude, e.longitude, e.rated_capacity, e.rated_power, "
95			" s.source_code, e.description, e.phase_of_lifecycle "
96			" FROM tbl_photovoltaic_power_stations e, tbl_svgs s "
97			" WHERE e.svg_id = s.id AND e.uuid = %s ")
98			cursor_system.execute(query, (photovoltaic_power_station_uuid,))
99			row = cursor_system.fetchone()
100
101			if row is None:
			0 ignored issues – show introduced 2024-09-18 10:27 UTC by Report Bug Copy Issue Report Show Similar Issues like this The variable `row` does not seem to be defined for all execution paths. Loading history...
102			cursor_system.close()
103			cnx_system.close()
104			raise falcon.HTTPError(status=falcon.HTTP_404, title='API.NOT_FOUND',
105			description='API.photovoltaic_POWER_STATION_NOT_FOUND')
106			else:
107			photovoltaic_power_station_id = row[0]
108			meta_result = {"id": row[0],
109			"name": row[1],
110			"uuid": row[2],
111			"address": row[3],
112			"latitude": row[4],
113			"longitude": row[5],
114			"rated_capacity": row[6],
115			"rated_power": row[7],
116			"svg": row[8],
117			"description": row[9],
118			"phase_of_lifecycle": row[10],
119			"qrcode": 'energystoragepowerstation:' + row[2]}
120
121			point_list = list()
122			meter_list = list()
123
124			# query all energy categories in system
125			cursor_system.execute(" SELECT id, name, unit_of_measure, kgce, kgco2e "
126			" FROM tbl_energy_categories "
127			" ORDER BY id ", )
128			rows_energy_categories = cursor_system.fetchall()
129			if rows_energy_categories is None or len(rows_energy_categories) == 0:
130			if cursor_system:
131			cursor_system.close()
132			if cnx_system:
133			cnx_system.close()
134			raise falcon.HTTPError(status=falcon.HTTP_404,
135			title='API.NOT_FOUND',
136			description='API.ENERGY_CATEGORY_NOT_FOUND')
137			energy_category_dict = dict()
138			for row_energy_category in rows_energy_categories:
139			energy_category_dict[row_energy_category[0]] = {"name": row_energy_category[1],
140			"unit_of_measure": row_energy_category[2],
141			"kgce": row_energy_category[3],
142			"kgco2e": row_energy_category[4]}
143
144			################################################################################################################
145			# Step 5: query associated grids on the photovoltaic power station
146			################################################################################################################
147			cursor_system.execute(" SELECT p.id, cg.name, p.units, p.object_type "
148			" FROM tbl_photovoltaic_power_stations_grids cg, tbl_points p "
149			" WHERE cg.photovoltaic_power_station_id = %s AND cg.power_point_id = p.id ",
150			(photovoltaic_power_station_id,))
151			rows_points = cursor_system.fetchall()
152			if rows_points is not None and len(rows_points) > 0:
153			for row_point in rows_points:
154			point_list.append({"id": row_point[0],
155			"name": row_point[1] + '.P',
156			"units": row_point[2],
157			"object_type": row_point[3]})
158
159			cursor_system.execute(" SELECT m.id, cg.name, m.energy_category_id "
160			" FROM tbl_photovoltaic_power_stations_grids cg, tbl_meters m "
161			" WHERE cg.photovoltaic_power_station_id = %s AND cg.buy_meter_id = m.id ",
162			(photovoltaic_power_station_id,))
163			row_meter = cursor_system.fetchone()
164			if row_meter is not None:
165			meter_list.append({"id": row_meter[0],
166			"name": row_meter[1] + '.Buy',
167			"energy_category_id": row_meter[2]})
168
169			cursor_system.execute(" SELECT m.id, cg.name, m.energy_category_id "
170			" FROM tbl_photovoltaic_power_stations_grids cg, tbl_meters m "
171			" WHERE cg.photovoltaic_power_station_id = %s AND cg.sell_meter_id = m.id ",
172			(photovoltaic_power_station_id,))
173			row_meter = cursor_system.fetchone()
174			if row_meter is not None:
175			meter_list.append({"id": row_meter[0],
176			"name": row_meter[1] + '.Sell',
177			"energy_category_id": row_meter[2]})
178
179			################################################################################################################
180			# Step 6: query associated loads on the photovoltaic power station
181			################################################################################################################
182			cursor_system.execute(" SELECT p.id, cl.name, p.units, p.object_type "
183			" FROM tbl_photovoltaic_power_stations_loads cl, tbl_points p "
184			" WHERE cl.photovoltaic_power_station_id = %s AND cl.power_point_id = p.id ",
185			(photovoltaic_power_station_id,))
186			rows_points = cursor_system.fetchall()
187			if rows_points is not None and len(rows_points) > 0:
188			for row_point in rows_points:
189			point_list.append({"id": row_point[0],
190			"name": row_point[1] + '.P',
191			"units": row_point[2],
192			"object_type": row_point[3]})
193
194			cursor_system.execute(" SELECT m.id, cl.name, m.energy_category_id "
195			" FROM tbl_photovoltaic_power_stations_loads cl, tbl_meters m "
196			" WHERE cl.photovoltaic_power_station_id = %s AND cl.meter_id = m.id ",
197			(photovoltaic_power_station_id,))
198			row_meter = cursor_system.fetchone()
199			if row_meter is not None:
200			meter_list.append({"id": row_meter[0],
201			"name": row_meter[1],
202			"energy_category_id": row_meter[2]})
203
204			################################################################################################################
205			# Step 6: query associated invertors on the photovoltaic power station
206			################################################################################################################
207			cursor_system.execute(" SELECT p.id, ppai.name, p.units, p.object_type "
208			" FROM tbl_photovoltaic_power_stations_invertors ppai, tbl_points p "
209			" WHERE ppai.photovoltaic_power_station_id = %s AND ppai.active_power_point_id = p.id ",
210			(photovoltaic_power_station_id,))
211			rows_points = cursor_system.fetchall()
212			if rows_points is not None and len(rows_points) > 0:
213			for row_point in rows_points:
214			point_list.append({"id": row_point[0],
215			"name": row_point[1] + '.P',
216			"units": row_point[2],
217			"object_type": row_point[3]})
218
219			################################################################################################################
220			# Step 7 query energy indicator data
221			################################################################################################################
222			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
223			if config.utc_offset[0] == '-':
224			timezone_offset = -timezone_offset
225
226			today_end_datetime_utc = datetime.utcnow()
227			today_end_datetime_local = datetime.utcnow() + timedelta(minutes=timezone_offset)
228			today_start_datetime_local = today_end_datetime_local.replace(hour=0, minute=0, second=0, microsecond=0)
229			today_start_datetime_utc = today_start_datetime_local - timedelta(minutes=timezone_offset)
230
231			today_generation_energy_value = Decimal(0.0)
232			total_generation_energy_value = Decimal(0.0)
233
234			cursor_energy.execute(" SELECT SUM(actual_value) "
235			" FROM tbl_photovoltaic_power_station_generation_hourly "
236			" WHERE photovoltaic_power_station_id = %s "
237			" AND start_datetime_utc >= %s "
238			" AND start_datetime_utc < %s ",
239			(photovoltaic_power_station_id,
240			today_start_datetime_utc,
241			today_end_datetime_utc))
242			row = cursor_energy.fetchone()
243			if row is not None:
244			today_generation_energy_value = row[0]
245			cursor_energy.execute(" SELECT SUM(actual_value) "
246			" FROM tbl_photovoltaic_power_station_generation_hourly "
247			" WHERE photovoltaic_power_station_id = %s ",
248			(photovoltaic_power_station_id,))
249			row = cursor_energy.fetchone()
250			if row is not None:
251			total_generation_energy_value = row[0]
252
253			################################################################################################################
254			# Step 8 query revenue indicator data
255			################################################################################################################
256			today_generation_revenue_value = Decimal(0.0)
257			total_generation_revenue_value = Decimal(0.0)
258			cursor_billing.execute(" SELECT SUM(actual_value) "
259			" FROM tbl_photovoltaic_power_station_generation_hourly "
260			" WHERE photovoltaic_power_station_id = %s "
261			" AND start_datetime_utc >= %s "
262			" AND start_datetime_utc < %s ",
263			(photovoltaic_power_station_id,
264			today_start_datetime_utc,
265			today_end_datetime_utc))
266			row = cursor_billing.fetchone()
267			if row is not None:
268			today_generation_revenue_value = row[0]
269
270			cursor_billing.execute(" SELECT SUM(actual_value) "
271			" FROM tbl_photovoltaic_power_station_generation_hourly "
272			" WHERE photovoltaic_power_station_id = %s ",
273			(photovoltaic_power_station_id,))
274			row = cursor_billing.fetchone()
275			if row is not None:
276			total_generation_revenue_value = row[0]
277
278			################################################################################################################
279			# Step 8: query parameters data on the photovoltaic power station
280			################################################################################################################
281			timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
282			if config.utc_offset[0] == '-':
283			timezone_offset = -timezone_offset
284
285			parameters_data = dict()
286			parameters_data['names'] = list()
287			parameters_data['timestamps'] = list()
288			parameters_data['values'] = list()
289
290			for point in point_list:
291			point_values = []
292			point_timestamps = []
293			if point['object_type'] == 'ENERGY_VALUE':
294			query = (" SELECT utc_date_time, actual_value "
295			" FROM tbl_energy_value "
296			" WHERE point_id = %s "
297			" AND utc_date_time BETWEEN %s AND %s "
298			" ORDER BY utc_date_time ")
299			cursor_historical.execute(query, (point['id'],
300			reporting_start_datetime_utc,
301			reporting_end_datetime_utc))
302			rows = cursor_historical.fetchall()
303
304			if rows is not None and len(rows) > 0:
305			reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
306			timedelta(minutes=timezone_offset)
307			current_datetime_local = reporting_start_datetime_local
308
309			while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
310			timedelta(minutes=timezone_offset):
311			point_timestamps.append(current_datetime_local.isoformat()[5:16])
312			point_values.append(rows[0][1])
313			current_datetime_local += timedelta(minutes=1)
314
315			for index in range(len(rows) - 1):
316			while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
317			timedelta(minutes=timezone_offset):
318			point_timestamps.append(current_datetime_local.isoformat()[5:16])
319			point_values.append(rows[index][1])
320			current_datetime_local += timedelta(minutes=1)
321			elif point['object_type'] == 'ANALOG_VALUE':
322			query = (" SELECT utc_date_time, actual_value "
323			" FROM tbl_analog_value "
324			" WHERE point_id = %s "
325			" AND utc_date_time BETWEEN %s AND %s "
326			" ORDER BY utc_date_time ")
327			cursor_historical.execute(query, (point['id'],
328			reporting_start_datetime_utc,
329			reporting_end_datetime_utc))
330			rows = cursor_historical.fetchall()
331
332			if rows is not None and len(rows) > 0:
333			reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
334			timedelta(minutes=timezone_offset)
335			current_datetime_local = reporting_start_datetime_local
336
337			while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
338			timedelta(minutes=timezone_offset):
339			point_timestamps.append(current_datetime_local.isoformat()[5:16])
340			point_values.append(rows[0][1])
341			current_datetime_local += timedelta(minutes=1)
342
343			for index in range(len(rows) - 1):
344			while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
345			timedelta(minutes=timezone_offset):
346			point_timestamps.append(current_datetime_local.isoformat()[5:16])
347			point_values.append(rows[index][1])
348			current_datetime_local += timedelta(minutes=1)
349			elif point['object_type'] == 'DIGITAL_VALUE':
350			query = (" SELECT utc_date_time, actual_value "
351			" FROM tbl_digital_value "
352			" WHERE point_id = %s "
353			" AND utc_date_time BETWEEN %s AND %s "
354			" ORDER BY utc_date_time ")
355			cursor_historical.execute(query, (point['id'],
356			reporting_start_datetime_utc,
357			reporting_end_datetime_utc))
358			rows = cursor_historical.fetchall()
359
360			if rows is not None and len(rows) > 0:
361			reporting_start_datetime_local = reporting_start_datetime_utc.replace(tzinfo=timezone.utc) + \
362			timedelta(minutes=timezone_offset)
363			current_datetime_local = reporting_start_datetime_local
364
365			while current_datetime_local < rows[0][0].replace(tzinfo=timezone.utc) + \
366			timedelta(minutes=timezone_offset):
367			point_timestamps.append(current_datetime_local.isoformat()[5:16])
368			point_values.append(rows[0][1])
369			current_datetime_local += timedelta(minutes=1)
370
371			for index in range(len(rows) - 1):
372			while current_datetime_local < rows[index + 1][0].replace(tzinfo=timezone.utc) + \
373			timedelta(minutes=timezone_offset):
374			point_timestamps.append(current_datetime_local.isoformat()[5:16])
375			point_values.append(rows[index][1])
376			current_datetime_local += timedelta(minutes=1)
377
378			parameters_data['names'].append(point['name'] + ' (' + point['units'] + ')')
379			parameters_data['timestamps'].append(point_timestamps)
380			parameters_data['values'].append(point_values)
381
382			if cursor_system:
383			cursor_system.close()
384			if cnx_system:
385			cnx_system.close()
386
387			if cursor_historical:
388			cursor_historical.close()
389			if cnx_historical:
390			cnx_historical.close()
391			################################################################################################################
392			# Step 9: construct the report
393			################################################################################################################
394			result = dict()
395			result['photovoltaic_power_station'] = meta_result
396			result['reporting_period'] = dict()
397			result['reporting_period']['names'] = list()
398			result['reporting_period']['units'] = list()
399			result['reporting_period']['subtotals'] = list()
400			result['reporting_period']['increment_rates'] = list()
401			result['reporting_period']['timestamps'] = list()
402			result['reporting_period']['values'] = list()
403
404			result['energy_indicators'] = dict()
405			result['energy_indicators']['today_generation_energy_value'] = today_generation_energy_value
406			result['energy_indicators']['total_generation_energy_value'] = total_generation_energy_value
407			result['energy_indicators']['performance_ratio'] = \
408			Decimal(100) * (today_generation_energy_value / meta_result['rated_capacity']) \
409			if today_generation_energy_value is not None and meta_result['rated_capacity'] > 0 else None
410
411			result['revenue_indicators'] = dict()
412			result['revenue_indicators']['today_generation_revenue_value'] = today_generation_revenue_value
413			result['revenue_indicators']['total_generation_revenue_value'] = total_generation_revenue_value
414
415			result['parameters'] = {
416			"names": parameters_data['names'],
417			"timestamps": parameters_data['timestamps'],
418			"values": parameters_data['values']
419			}
420			resp.text = json.dumps(result)
421

MyEMS / myems

Issues (1588)

reports/photovoltaicpowerstationdetails.py (1 issue)

Severity

Introduced By

Duplication Side-by-Side

Filter issues like