Issues in tariff.py - All Issues - Inspection of "Add a Codacy badge to README.md" - myems/myems-api - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#1)

unknown

created 2020-11-25 03:36 UTC

tariff.py (5 issues)

Labels

Duplication 5

Severity

Informational 5

import falcon
import simplejson as json
import mysql.connector
import config
import uuid
from datetime import datetime, timedelta, timezone


class TariffCollection:
    @staticmethod
    def __init__():
        pass

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

    @staticmethod
    def on_get(req, resp):
        cnx = mysql.connector.connect(**config.myems_system_db)
        cursor = cnx.cursor()

        query = (" SELECT t.id, t.name, t.uuid, "
                 "        ec.id AS energy_category_id, ec.name AS energy_category_name, "
                 "        t.tariff_type, t.unit_of_price, "
                 "        t.valid_from_datetime_utc, t.valid_through_datetime_utc "
                 " FROM tbl_tariffs t, tbl_energy_categories ec "
                 " WHERE t.energy_category_id = ec.id "
                 " ORDER BY t.name ")
        cursor.execute(query)
        rows = cursor.fetchall()

        result = list()
        if rows is not None and len(rows) > 0:
            for row in rows:
                valid_from = row[7].replace(tzinfo=timezone.utc)
                valid_through = row[8].replace(tzinfo=timezone.utc)

                meta_result = {"id": row[0],
                               "name": row[1],
                               "uuid": row[2],
                               "energy_category": {"id": row[3],
                                                   "name": row[4]},
                               "tariff_type": row[5],
                               "unit_of_price": row[6],
                               "valid_from": valid_from.timestamp() * 1000,
                               "valid_through": valid_through.timestamp() * 1000}

                if meta_result['tariff_type'] == 'block':
                    meta_result['block'] = list()
                    query = (" SELECT start_amount, end_amount, price "
                             " FROM tbl_tariffs_blocks "
                             " WHERE tariff_id = %s "
                             " ORDER BY id ")
                    cursor.execute(query, (meta_result['id'],))
                    rows_block = cursor.fetchall()
                    if rows_block is not None and len(rows_block) > 0:
                        for row_block in rows_block:
                            meta_data = {"start_amount": row_block[0],
                                         "end_amount": row_block[1],
                                         "price": row_block[2]}
                            meta_result['block'].append(meta_data)

                elif meta_result['tariff_type'] == 'timeofuse':
                    meta_result['timeofuse'] = list()
                    query = (" SELECT start_time_of_day, end_time_of_day, peak_type, price "
                             " FROM tbl_tariffs_timeofuses "
                             " WHERE tariff_id = %s  "
                             " ORDER BY id")
                    cursor.execute(query, (meta_result['id'],))
                    rows_timeofuses = cursor.fetchall()
                    if rows_timeofuses is not None and len(rows_timeofuses) > 0:
                        for row_timeofuse in rows_timeofuses:
                            meta_data = {"start_time_of_day": str(row_timeofuse[0]),
                                         "end_time_of_day": str(row_timeofuse[1]),
                                         "peak_type": row_timeofuse[2],
                                         "price": row_timeofuse[3]}
                            meta_result['timeofuse'].append(meta_data)
                else:
                    cursor.close()
                    cnx.disconnect()
                    raise falcon.HTTPError(falcon.HTTP_400,
                                           title='API.ERROR',
                                           description='API.INVALID_TARIFF_TYPE')

                result.append(meta_result)

        cursor.close()
        cnx.disconnect()

        resp.body = json.dumps(result)

    @staticmethod
    def on_post(req, resp):
        """Handles POST requests"""
        try:
            raw_json = req.stream.read().decode('utf-8')
        except Exception as ex:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.ERROR', description=ex)
        new_values = json.loads(raw_json, encoding='utf-8')

        if 'name' not in new_values['data'].keys() or \
                not isinstance(new_values['data']['name'], str) or \
                len(str.strip(new_values['data']['name'])) == 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_METER_NAME')
        name = str.strip(new_values['data']['name'])

        if 'energy_category' not in new_values['data'].keys() or \
                'id' not in new_values['data']['energy_category'].keys() or \
                not isinstance(new_values['data']['energy_category']['id'], int) or \
                new_values['data']['energy_category']['id'] <= 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_ENERGY_CATEGORY_ID')
        energy_category_id = new_values['data']['energy_category']['id']

        if 'tariff_type' not in new_values['data'].keys() \
           or str.strip(new_values['data']['tariff_type']) not in ('block', 'timeofuse'):
            raise falcon.HTTPError(falcon.HTTP_400,
                                   title='API.BAD_REQUEST',
                                   description='API.INVALID_TARIFF_TYPE')
        tariff_type = str.strip(new_values['data']['tariff_type'])

        if new_values['data']['tariff_type'] == 'block':

            if new_values['data']['block'] is None:
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_BLOCK_PRICING')
        elif new_values['data']['tariff_type'] == 'timeofuse':
            if new_values['data']['timeofuse'] is None:
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')
        if 'unit_of_price' not in new_values['data'].keys() or \
                not isinstance(new_values['data']['unit_of_price'], str) or \
                len(str.strip(new_values['data']['unit_of_price'])) == 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_UNIT_OF_PRICE')
        unit_of_price = str.strip(new_values['data']['unit_of_price'])

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        cnx = mysql.connector.connect(**config.myems_system_db)
        cursor = cnx.cursor()

        cursor.execute(" SELECT name "
                       " FROM tbl_tariffs "
                       " WHERE name = %s ", (name,))
        if cursor.fetchone() is not None:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.BAD_REQUEST',
                                   description='API.TARIFF_NAME_IS_ALREADY_IN_USE')

        cursor.execute(" SELECT name "
                       " FROM tbl_energy_categories "
                       " WHERE id = %s ", (energy_category_id,))
        if cursor.fetchone() is None:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.ENERGY_CATEGORY_NOT_FOUND')

        # todo: validate datetime values
        valid_from = datetime.strptime(new_values['data']['valid_from'], '%Y-%m-%dT%H:%M:%S')
        valid_from = valid_from.replace(tzinfo=timezone.utc)
        valid_from -= timedelta(minutes=timezone_offset)
        valid_through = datetime.strptime(new_values['data']['valid_through'], '%Y-%m-%dT%H:%M:%S')
        valid_through = valid_through.replace(tzinfo=timezone.utc)
        valid_through -= timedelta(minutes=timezone_offset)

        add_row = (" INSERT INTO tbl_tariffs "
                   "             (name, uuid, energy_category_id, tariff_type, unit_of_price, "
                   "              valid_from_datetime_utc, valid_through_datetime_utc ) "
                   " VALUES (%s, %s, %s, %s, %s, %s, %s) ")
        cursor.execute(add_row, (name,
                                 str(uuid.uuid4()),
                                 energy_category_id,
                                 tariff_type,
                                 unit_of_price,
                                 valid_from,
                                 valid_through))
        new_id = cursor.lastrowid
        cnx.commit()
        # insert block prices
        if tariff_type == 'block':
            for block in new_values['data']['block']:
                add_block = (" INSERT INTO tbl_tariffs_blocks "
                             "             (tariff_id, start_amount, end_amount, price) "
                             " VALUES (%s, %s, %s, %s) ")
                cursor.execute(add_block, (new_id, block['start_amount'], block['end_amount'], block['price']))
                cnx.commit()
        # insert time of use prices
        elif tariff_type == 'timeofuse':
            for timeofuse in new_values['data']['timeofuse']:
                add_timeofuse = (" INSERT INTO tbl_tariffs_timeofuses "
                                 "             (tariff_id, start_time_of_day, end_time_of_day, peak_type, price) "
                                 " VALUES (%s, %s, %s, %s, %s) ")
                cursor.execute(add_timeofuse, (new_id,
                                               timeofuse['start_time_of_day'],
                                               timeofuse['end_time_of_day'],
                                               timeofuse['peak_type'],
                                               timeofuse['price']))
                cnx.commit()

        cursor.close()
        cnx.disconnect()

        resp.status = falcon.HTTP_201
        resp.location = '/tariffs/' + str(new_id)


class TariffItem:
    @staticmethod
    def __init__():
        pass

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

    @staticmethod
    def on_get(req, resp, id_):
        if not id_.isdigit() or int(id_) <= 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_TARIFF_ID')

        cnx = mysql.connector.connect(**config.myems_system_db)
        cursor = cnx.cursor()

        query = (" SELECT t.id, t.name, t.uuid, "
                 "        ec.id AS energy_category_id, ec.name AS energy_category_name, "
                 "        t.tariff_type, "
                 "        t.unit_of_price, "
                 "        t.valid_from_datetime_utc, t.valid_through_datetime_utc "
                 " FROM tbl_tariffs t, tbl_energy_categories ec "
                 " WHERE t.energy_category_id = ec.id AND t.id =%s ")
        cursor.execute(query, (id_,))
        row = cursor.fetchone()
        if row is None:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.TARIFF_NOT_FOUND')

        valid_from = row[7].replace(tzinfo=timezone.utc)
        valid_through = row[8].replace(tzinfo=timezone.utc)

        result = {"id": row[0],
                  "name": row[1],
                  "uuid": row[2],
                  "energy_category": {"id": row[3],
                                      "name": row[4]},
                  "tariff_type": row[5],
                  "unit_of_price": row[6],
                  "valid_from": valid_from.timestamp() * 1000,
                  "valid_through": valid_through.timestamp() * 1000}

        if result['tariff_type'] == 'block':
            result['block'] = list()
            query = (" SELECT start_amount, end_amount, price "
                     " FROM tbl_tariffs_blocks "
                     " WHERE tariff_id = %s "
                     " ORDER BY id")
            cursor.execute(query, (result['id'],))
            rows_block = cursor.fetchall()
            if rows_block is not None and len(rows_block) > 0:
                for row_block in rows_block:
                    meta_data = {"start_amount": row_block[0], "end_amount": row_block[1], "price": row_block[2]}
                    result['block'].append(meta_data)

        elif result['tariff_type'] == 'timeofuse':
            result['timeofuse'] = list()
            query = (" SELECT start_time_of_day, end_time_of_day, peak_type, price "
                     " FROM tbl_tariffs_timeofuses"
                     " WHERE tariff_id =%s ")
            cursor.execute(query, (result['id'],))
            rows_timeofuses = cursor.fetchall()
            if rows_timeofuses is not None and len(rows_timeofuses) > 0:
                for row_timeofuse in rows_timeofuses:
                    meta_data = {"start_time_of_day": str(row_timeofuse[0]),
                                 "end_time_of_day": str(row_timeofuse[1]),
                                 "peak_type": row_timeofuse[2],
                                 "price": row_timeofuse[3]}
                    result['timeofuse'].append(meta_data)

        cursor.close()
        cnx.disconnect()

        resp.body = json.dumps(result)

    @staticmethod

    def on_delete(req, resp, id_):
        if not id_.isdigit() or int(id_) <= 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_TARIFF_ID')

        cnx = mysql.connector.connect(**config.myems_system_db)
        cursor = cnx.cursor()

        cursor.execute(" SELECT name "
                       " FROM tbl_tariffs "
                       " WHERE id = %s ", (id_,))
        if cursor.fetchone() is None:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.TARIFF_NOT_FOUND')

        cursor.execute(" SELECT id "
                       " FROM tbl_tariffs_blocks "
                       " WHERE tariff_id = %s ", (id_,))
        rows = cursor.fetchall()
        if rows is not None and len(rows) > 0:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.TARIFF_NOT_EMPTY')

        cursor.execute(" SELECT id "
                       " FROM tbl_tariffs_timeofuses "
                       " WHERE tariff_id = %s ", (id_,))
        rows = cursor.fetchall()
        if rows is not None and len(rows) > 0:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.TARIFF_NOT_EMPTY')

        cursor.execute(" SELECT id "
                       " FROM tbl_cost_centers_tariffs "
                       " WHERE tariff_id = %s ", (id_,))
        rows = cursor.fetchall()
        if rows is not None and len(rows) > 0:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.TARIFF_IN_USE')

        cursor.execute(" DELETE FROM tbl_tariffs WHERE id = %s ", (id_,))
        cnx.commit()

        cursor.close()
        cnx.disconnect()

        resp.status = falcon.HTTP_204

    @staticmethod
    def on_put(req, resp, id_):
        """Handles PUT requests"""
        try:
            raw_json = req.stream.read().decode('utf-8')
        except Exception as ex:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.ERROR', description=ex)

        if not id_.isdigit() or int(id_) <= 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_TARIFF_ID')

        new_values = json.loads(raw_json, encoding='utf-8')

        if 'name' not in new_values['data'].keys() or \
                not isinstance(new_values['data']['name'], str) or \
                len(str.strip(new_values['data']['name'])) == 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_METER_NAME')
        name = str.strip(new_values['data']['name'])

        if 'energy_category' not in new_values['data'].keys() or \
                'id' not in new_values['data']['energy_category'].keys() or \
                not isinstance(new_values['data']['energy_category']['id'], int) or \
                new_values['data']['energy_category']['id'] <= 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_ENERGY_CATEGORY_ID')
        energy_category_id = new_values['data']['energy_category']['id']

        if 'tariff_type' not in new_values['data'].keys() \
           or str.strip(new_values['data']['tariff_type']) not in ('block', 'timeofuse'):
            raise falcon.HTTPError(falcon.HTTP_400,
                                   title='API.BAD_REQUEST',
                                   description='API.INVALID_TARIFF_TYPE')
        tariff_type = str.strip(new_values['data']['tariff_type'])

        if new_values['data']['tariff_type'] == 'block':

            if new_values['data']['block'] is None:
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_BLOCK_PRICING')
        elif new_values['data']['tariff_type'] == 'timeofuse':
            if new_values['data']['timeofuse'] is None:
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')

        if 'unit_of_price' not in new_values['data'].keys() or \
                not isinstance(new_values['data']['unit_of_price'], str) or \
                len(str.strip(new_values['data']['unit_of_price'])) == 0:
            raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
                                   description='API.INVALID_UNIT_OF_PRICE')
        unit_of_price = str.strip(new_values['data']['unit_of_price'])

        timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
        if config.utc_offset[0] == '-':
            timezone_offset = -timezone_offset

        cnx = mysql.connector.connect(**config.myems_system_db)
        cursor = cnx.cursor()

        # check if the tariff exist
        query = (" SELECT name " 
                 " FROM tbl_tariffs " 
                 " WHERE id = %s ")
        cursor.execute(query, (id_,))
        cursor.fetchone()

        if cursor.rowcount != 1:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
                                   description='API.TARIFF_NOT_FOUND')

        cursor.execute(" SELECT name "
                       " FROM tbl_tariffs "
                       " WHERE name = %s AND id != %s ", (name, id_))
        if cursor.fetchone() is not None:
            cursor.close()
            cnx.disconnect()
            raise falcon.HTTPError(falcon.HTTP_404, title='API.BAD_REQUEST',
                                   description='API.TARIFF_NAME_IS_ALREADY_IN_USE')

        valid_from = datetime.strptime(new_values['data']['valid_from'], '%Y-%m-%dT%H:%M:%S')
        valid_from = valid_from.replace(tzinfo=timezone.utc)
        valid_from -= timedelta(minutes=timezone_offset)
        valid_through = datetime.strptime(new_values['data']['valid_through'], '%Y-%m-%dT%H:%M:%S')
        valid_through = valid_through.replace(tzinfo=timezone.utc)
        valid_through -= timedelta(minutes=timezone_offset)

        # update tariff itself
        update_row = (" UPDATE tbl_tariffs "
                      " SET name = %s, energy_category_id = %s, tariff_type = %s, unit_of_price = %s, "
                      "     valid_from_datetime_utc = %s , valid_through_datetime_utc = %s "
                      " WHERE id = %s ")
        cursor.execute(update_row, (name,
                                    energy_category_id,
                                    tariff_type,
                                    unit_of_price,
                                    valid_from,
                                    valid_through,
                                    id_,))
        cnx.commit()

        # update prices of the tariff
        if tariff_type == 'block':
            if 'block' not in new_values['data'].keys() or new_values['data']['block'] is None:

                cursor.close()
                cnx.disconnect()
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_BLOCK_PRICING')
            else:
                # remove all (possible) exist prices
                cursor.execute(" DELETE FROM tbl_tariffs_blocks "
                               " WHERE tariff_id = %s ",
                               (id_,))

                cursor.execute(" DELETE FROM tbl_tariffs_timeofuses "
                               " WHERE tariff_id = %s ",
                               (id_,))
                cnx.commit()

                for block in new_values['data']['block']:
                    cursor.execute(" INSERT INTO tbl_tariffs_blocks "
                                   "             (tariff_id, start_amount, end_amount, price) "
                                   " VALUES (%s, %s, %s, %s) ",
                                   (id_, block['start_amount'], block['end_amount'], block['price']))
                    cnx.commit()
        elif tariff_type == 'timeofuse':
            if 'timeofuse' not in new_values['data'].keys() or new_values['data']['timeofuse'] is None:

                cursor.close()
                cnx.disconnect()
                raise falcon.HTTPError(falcon.HTTP_400,
                                       title='API.BAD_REQUEST',
                                       description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')
            else:
                # remove all (possible) exist prices
                cursor.execute(" DELETE FROM tbl_tariffs_blocks "
                               " WHERE tariff_id = %s ",
                               (id_,))

                cursor.execute(" DELETE FROM tbl_tariffs_timeofuses "
                               " WHERE tariff_id = %s ",
                               (id_,))
                cnx.commit()

                for timeofuse in new_values['data']['timeofuse']:
                    add_timeofuse = (" INSERT INTO tbl_tariffs_timeofuses "
                                     "             (tariff_id, start_time_of_day, end_time_of_day, peak_type, price) "
                                     " VALUES (%s, %s, %s, %s, %s) ")
                    cursor.execute(add_timeofuse, (id_,
                                                   timeofuse['start_time_of_day'],
                                                   timeofuse['end_time_of_day'],
                                                   timeofuse['peak_type'],
                                                   timeofuse['price']))
                    cnx.commit()

        cursor.close()
        cnx.disconnect()
        resp.status = falcon.HTTP_200




1		import falcon
2		import simplejson as json
3		import mysql.connector
4		import config
5		import uuid
6		from datetime import datetime, timedelta, timezone
7
8
9		class TariffCollection:
10		@staticmethod
11		def __init__():
12		pass
13
14		@staticmethod
15		def on_options(req, resp):
16		resp.status = falcon.HTTP_200
17
18		@staticmethod
19		def on_get(req, resp):
20		cnx = mysql.connector.connect(**config.myems_system_db)
21		cursor = cnx.cursor()
22
23		query = (" SELECT t.id, t.name, t.uuid, "
24		" ec.id AS energy_category_id, ec.name AS energy_category_name, "
25		" t.tariff_type, t.unit_of_price, "
26		" t.valid_from_datetime_utc, t.valid_through_datetime_utc "
27		" FROM tbl_tariffs t, tbl_energy_categories ec "
28		" WHERE t.energy_category_id = ec.id "
29		" ORDER BY t.name ")
30		cursor.execute(query)
31		rows = cursor.fetchall()
32
33		result = list()
34		if rows is not None and len(rows) > 0:
35		for row in rows:
36		valid_from = row[7].replace(tzinfo=timezone.utc)
37		valid_through = row[8].replace(tzinfo=timezone.utc)
38
39		meta_result = {"id": row[0],
40		"name": row[1],
41		"uuid": row[2],
42		"energy_category": {"id": row[3],
43		"name": row[4]},
44		"tariff_type": row[5],
45		"unit_of_price": row[6],
46		"valid_from": valid_from.timestamp() * 1000,
47		"valid_through": valid_through.timestamp() * 1000}
48
49		if meta_result['tariff_type'] == 'block':
50		meta_result['block'] = list()
51		query = (" SELECT start_amount, end_amount, price "
52		" FROM tbl_tariffs_blocks "
53		" WHERE tariff_id = %s "
54		" ORDER BY id ")
55		cursor.execute(query, (meta_result['id'],))
56		rows_block = cursor.fetchall()
57		if rows_block is not None and len(rows_block) > 0:
58		for row_block in rows_block:
59		meta_data = {"start_amount": row_block[0],
60		"end_amount": row_block[1],
61		"price": row_block[2]}
62		meta_result['block'].append(meta_data)
63
64		elif meta_result['tariff_type'] == 'timeofuse':
65		meta_result['timeofuse'] = list()
66		query = (" SELECT start_time_of_day, end_time_of_day, peak_type, price "
67		" FROM tbl_tariffs_timeofuses "
68		" WHERE tariff_id = %s "
69		" ORDER BY id")
70		cursor.execute(query, (meta_result['id'],))
71		rows_timeofuses = cursor.fetchall()
72		if rows_timeofuses is not None and len(rows_timeofuses) > 0:
73		for row_timeofuse in rows_timeofuses:
74		meta_data = {"start_time_of_day": str(row_timeofuse[0]),
75		"end_time_of_day": str(row_timeofuse[1]),
76		"peak_type": row_timeofuse[2],
77		"price": row_timeofuse[3]}
78		meta_result['timeofuse'].append(meta_data)
79		else:
80		cursor.close()
81		cnx.disconnect()
82		raise falcon.HTTPError(falcon.HTTP_400,
83		title='API.ERROR',
84		description='API.INVALID_TARIFF_TYPE')
85
86		result.append(meta_result)
87
88		cursor.close()
89		cnx.disconnect()
90
91		resp.body = json.dumps(result)
92
93		@staticmethod
94		def on_post(req, resp):
95		"""Handles POST requests"""
96		try:
97		raw_json = req.stream.read().decode('utf-8')
98		except Exception as ex:
99		raise falcon.HTTPError(falcon.HTTP_400, title='API.ERROR', description=ex)
100		new_values = json.loads(raw_json, encoding='utf-8')
101
102		if 'name' not in new_values['data'].keys() or \
103		not isinstance(new_values['data']['name'], str) or \
104		len(str.strip(new_values['data']['name'])) == 0:
105		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
106		description='API.INVALID_METER_NAME')
107		name = str.strip(new_values['data']['name'])
108
109		if 'energy_category' not in new_values['data'].keys() or \
110		'id' not in new_values['data']['energy_category'].keys() or \
111		not isinstance(new_values['data']['energy_category']['id'], int) or \
112		new_values['data']['energy_category']['id'] <= 0:
113		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
114		description='API.INVALID_ENERGY_CATEGORY_ID')
115		energy_category_id = new_values['data']['energy_category']['id']
116
117		if 'tariff_type' not in new_values['data'].keys() \
118		or str.strip(new_values['data']['tariff_type']) not in ('block', 'timeofuse'):
119		raise falcon.HTTPError(falcon.HTTP_400,
120		title='API.BAD_REQUEST',
121		description='API.INVALID_TARIFF_TYPE')
122		tariff_type = str.strip(new_values['data']['tariff_type'])
123
124	View Code Duplication	if new_values['data']['tariff_type'] == 'block':
		0 ignored issues – show Duplication introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
125		if new_values['data']['block'] is None:
126		raise falcon.HTTPError(falcon.HTTP_400,
127		title='API.BAD_REQUEST',
128		description='API.INVALID_TARIFF_BLOCK_PRICING')
129		elif new_values['data']['tariff_type'] == 'timeofuse':
130		if new_values['data']['timeofuse'] is None:
131		raise falcon.HTTPError(falcon.HTTP_400,
132		title='API.BAD_REQUEST',
133		description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')
134		if 'unit_of_price' not in new_values['data'].keys() or \
135		not isinstance(new_values['data']['unit_of_price'], str) or \
136		len(str.strip(new_values['data']['unit_of_price'])) == 0:
137		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
138		description='API.INVALID_UNIT_OF_PRICE')
139		unit_of_price = str.strip(new_values['data']['unit_of_price'])
140
141		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
142		if config.utc_offset[0] == '-':
143		timezone_offset = -timezone_offset
144
145		cnx = mysql.connector.connect(**config.myems_system_db)
146		cursor = cnx.cursor()
147
148		cursor.execute(" SELECT name "
149		" FROM tbl_tariffs "
150		" WHERE name = %s ", (name,))
151		if cursor.fetchone() is not None:
152		cursor.close()
153		cnx.disconnect()
154		raise falcon.HTTPError(falcon.HTTP_404, title='API.BAD_REQUEST',
155		description='API.TARIFF_NAME_IS_ALREADY_IN_USE')
156
157		cursor.execute(" SELECT name "
158		" FROM tbl_energy_categories "
159		" WHERE id = %s ", (energy_category_id,))
160		if cursor.fetchone() is None:
161		cursor.close()
162		cnx.disconnect()
163		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
164		description='API.ENERGY_CATEGORY_NOT_FOUND')
165
166		# todo: validate datetime values
167		valid_from = datetime.strptime(new_values['data']['valid_from'], '%Y-%m-%dT%H:%M:%S')
168		valid_from = valid_from.replace(tzinfo=timezone.utc)
169		valid_from -= timedelta(minutes=timezone_offset)
170		valid_through = datetime.strptime(new_values['data']['valid_through'], '%Y-%m-%dT%H:%M:%S')
171		valid_through = valid_through.replace(tzinfo=timezone.utc)
172		valid_through -= timedelta(minutes=timezone_offset)
173
174		add_row = (" INSERT INTO tbl_tariffs "
175		" (name, uuid, energy_category_id, tariff_type, unit_of_price, "
176		" valid_from_datetime_utc, valid_through_datetime_utc ) "
177		" VALUES (%s, %s, %s, %s, %s, %s, %s) ")
178		cursor.execute(add_row, (name,
179		str(uuid.uuid4()),
180		energy_category_id,
181		tariff_type,
182		unit_of_price,
183		valid_from,
184		valid_through))
185		new_id = cursor.lastrowid
186		cnx.commit()
187		# insert block prices
188		if tariff_type == 'block':
189		for block in new_values['data']['block']:
190		add_block = (" INSERT INTO tbl_tariffs_blocks "
191		" (tariff_id, start_amount, end_amount, price) "
192		" VALUES (%s, %s, %s, %s) ")
193		cursor.execute(add_block, (new_id, block['start_amount'], block['end_amount'], block['price']))
194		cnx.commit()
195		# insert time of use prices
196		elif tariff_type == 'timeofuse':
197		for timeofuse in new_values['data']['timeofuse']:
198		add_timeofuse = (" INSERT INTO tbl_tariffs_timeofuses "
199		" (tariff_id, start_time_of_day, end_time_of_day, peak_type, price) "
200		" VALUES (%s, %s, %s, %s, %s) ")
201		cursor.execute(add_timeofuse, (new_id,
202		timeofuse['start_time_of_day'],
203		timeofuse['end_time_of_day'],
204		timeofuse['peak_type'],
205		timeofuse['price']))
206		cnx.commit()
207
208		cursor.close()
209		cnx.disconnect()
210
211		resp.status = falcon.HTTP_201
212		resp.location = '/tariffs/' + str(new_id)
213
214
215		class TariffItem:
216		@staticmethod
217		def __init__():
218		pass
219
220		@staticmethod
221		def on_options(req, resp, id_):
222		resp.status = falcon.HTTP_200
223
224		@staticmethod
225		def on_get(req, resp, id_):
226		if not id_.isdigit() or int(id_) <= 0:
227		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
228		description='API.INVALID_TARIFF_ID')
229
230		cnx = mysql.connector.connect(**config.myems_system_db)
231		cursor = cnx.cursor()
232
233		query = (" SELECT t.id, t.name, t.uuid, "
234		" ec.id AS energy_category_id, ec.name AS energy_category_name, "
235		" t.tariff_type, "
236		" t.unit_of_price, "
237		" t.valid_from_datetime_utc, t.valid_through_datetime_utc "
238		" FROM tbl_tariffs t, tbl_energy_categories ec "
239		" WHERE t.energy_category_id = ec.id AND t.id =%s ")
240		cursor.execute(query, (id_,))
241		row = cursor.fetchone()
242		if row is None:
243		cursor.close()
244		cnx.disconnect()
245		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
246		description='API.TARIFF_NOT_FOUND')
247
248		valid_from = row[7].replace(tzinfo=timezone.utc)
249		valid_through = row[8].replace(tzinfo=timezone.utc)
250
251		result = {"id": row[0],
252		"name": row[1],
253		"uuid": row[2],
254		"energy_category": {"id": row[3],
255		"name": row[4]},
256		"tariff_type": row[5],
257		"unit_of_price": row[6],
258		"valid_from": valid_from.timestamp() * 1000,
259		"valid_through": valid_through.timestamp() * 1000}
260
261		if result['tariff_type'] == 'block':
262		result['block'] = list()
263		query = (" SELECT start_amount, end_amount, price "
264		" FROM tbl_tariffs_blocks "
265		" WHERE tariff_id = %s "
266		" ORDER BY id")
267		cursor.execute(query, (result['id'],))
268		rows_block = cursor.fetchall()
269		if rows_block is not None and len(rows_block) > 0:
270		for row_block in rows_block:
271		meta_data = {"start_amount": row_block[0], "end_amount": row_block[1], "price": row_block[2]}
272		result['block'].append(meta_data)
273
274		elif result['tariff_type'] == 'timeofuse':
275		result['timeofuse'] = list()
276		query = (" SELECT start_time_of_day, end_time_of_day, peak_type, price "
277		" FROM tbl_tariffs_timeofuses"
278		" WHERE tariff_id =%s ")
279		cursor.execute(query, (result['id'],))
280		rows_timeofuses = cursor.fetchall()
281		if rows_timeofuses is not None and len(rows_timeofuses) > 0:
282		for row_timeofuse in rows_timeofuses:
283		meta_data = {"start_time_of_day": str(row_timeofuse[0]),
284		"end_time_of_day": str(row_timeofuse[1]),
285		"peak_type": row_timeofuse[2],
286		"price": row_timeofuse[3]}
287		result['timeofuse'].append(meta_data)
288
289		cursor.close()
290		cnx.disconnect()
291
292		resp.body = json.dumps(result)
293
294	View Code Duplication	@staticmethod
		0 ignored issues – show Duplication introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
295		def on_delete(req, resp, id_):
296		if not id_.isdigit() or int(id_) <= 0:
297		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
298		description='API.INVALID_TARIFF_ID')
299
300		cnx = mysql.connector.connect(**config.myems_system_db)
301		cursor = cnx.cursor()
302
303		cursor.execute(" SELECT name "
304		" FROM tbl_tariffs "
305		" WHERE id = %s ", (id_,))
306		if cursor.fetchone() is None:
307		cursor.close()
308		cnx.disconnect()
309		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
310		description='API.TARIFF_NOT_FOUND')
311
312		cursor.execute(" SELECT id "
313		" FROM tbl_tariffs_blocks "
314		" WHERE tariff_id = %s ", (id_,))
315		rows = cursor.fetchall()
316		if rows is not None and len(rows) > 0:
317		cursor.close()
318		cnx.disconnect()
319		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
320		description='API.TARIFF_NOT_EMPTY')
321
322		cursor.execute(" SELECT id "
323		" FROM tbl_tariffs_timeofuses "
324		" WHERE tariff_id = %s ", (id_,))
325		rows = cursor.fetchall()
326		if rows is not None and len(rows) > 0:
327		cursor.close()
328		cnx.disconnect()
329		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
330		description='API.TARIFF_NOT_EMPTY')
331
332		cursor.execute(" SELECT id "
333		" FROM tbl_cost_centers_tariffs "
334		" WHERE tariff_id = %s ", (id_,))
335		rows = cursor.fetchall()
336		if rows is not None and len(rows) > 0:
337		cursor.close()
338		cnx.disconnect()
339		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
340		description='API.TARIFF_IN_USE')
341
342		cursor.execute(" DELETE FROM tbl_tariffs WHERE id = %s ", (id_,))
343		cnx.commit()
344
345		cursor.close()
346		cnx.disconnect()
347
348		resp.status = falcon.HTTP_204
349
350		@staticmethod
351		def on_put(req, resp, id_):
352		"""Handles PUT requests"""
353		try:
354		raw_json = req.stream.read().decode('utf-8')
355		except Exception as ex:
356		raise falcon.HTTPError(falcon.HTTP_400, title='API.ERROR', description=ex)
357
358		if not id_.isdigit() or int(id_) <= 0:
359		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
360		description='API.INVALID_TARIFF_ID')
361
362		new_values = json.loads(raw_json, encoding='utf-8')
363
364		if 'name' not in new_values['data'].keys() or \
365		not isinstance(new_values['data']['name'], str) or \
366		len(str.strip(new_values['data']['name'])) == 0:
367		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
368		description='API.INVALID_METER_NAME')
369		name = str.strip(new_values['data']['name'])
370
371		if 'energy_category' not in new_values['data'].keys() or \
372		'id' not in new_values['data']['energy_category'].keys() or \
373		not isinstance(new_values['data']['energy_category']['id'], int) or \
374		new_values['data']['energy_category']['id'] <= 0:
375		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
376		description='API.INVALID_ENERGY_CATEGORY_ID')
377		energy_category_id = new_values['data']['energy_category']['id']
378
379		if 'tariff_type' not in new_values['data'].keys() \
380		or str.strip(new_values['data']['tariff_type']) not in ('block', 'timeofuse'):
381		raise falcon.HTTPError(falcon.HTTP_400,
382		title='API.BAD_REQUEST',
383		description='API.INVALID_TARIFF_TYPE')
384		tariff_type = str.strip(new_values['data']['tariff_type'])
385
386	View Code Duplication	if new_values['data']['tariff_type'] == 'block':
		0 ignored issues – show Duplication introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
387		if new_values['data']['block'] is None:
388		raise falcon.HTTPError(falcon.HTTP_400,
389		title='API.BAD_REQUEST',
390		description='API.INVALID_TARIFF_BLOCK_PRICING')
391		elif new_values['data']['tariff_type'] == 'timeofuse':
392		if new_values['data']['timeofuse'] is None:
393		raise falcon.HTTPError(falcon.HTTP_400,
394		title='API.BAD_REQUEST',
395		description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')
396
397		if 'unit_of_price' not in new_values['data'].keys() or \
398		not isinstance(new_values['data']['unit_of_price'], str) or \
399		len(str.strip(new_values['data']['unit_of_price'])) == 0:
400		raise falcon.HTTPError(falcon.HTTP_400, title='API.BAD_REQUEST',
401		description='API.INVALID_UNIT_OF_PRICE')
402		unit_of_price = str.strip(new_values['data']['unit_of_price'])
403
404		timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
405		if config.utc_offset[0] == '-':
406		timezone_offset = -timezone_offset
407
408		cnx = mysql.connector.connect(**config.myems_system_db)
409		cursor = cnx.cursor()
410
411		# check if the tariff exist
412		query = (" SELECT name "
413		" FROM tbl_tariffs "
414		" WHERE id = %s ")
415		cursor.execute(query, (id_,))
416		cursor.fetchone()
417
418		if cursor.rowcount != 1:
419		cursor.close()
420		cnx.disconnect()
421		raise falcon.HTTPError(falcon.HTTP_404, title='API.NOT_FOUND',
422		description='API.TARIFF_NOT_FOUND')
423
424		cursor.execute(" SELECT name "
425		" FROM tbl_tariffs "
426		" WHERE name = %s AND id != %s ", (name, id_))
427		if cursor.fetchone() is not None:
428		cursor.close()
429		cnx.disconnect()
430		raise falcon.HTTPError(falcon.HTTP_404, title='API.BAD_REQUEST',
431		description='API.TARIFF_NAME_IS_ALREADY_IN_USE')
432
433		valid_from = datetime.strptime(new_values['data']['valid_from'], '%Y-%m-%dT%H:%M:%S')
434		valid_from = valid_from.replace(tzinfo=timezone.utc)
435		valid_from -= timedelta(minutes=timezone_offset)
436		valid_through = datetime.strptime(new_values['data']['valid_through'], '%Y-%m-%dT%H:%M:%S')
437		valid_through = valid_through.replace(tzinfo=timezone.utc)
438		valid_through -= timedelta(minutes=timezone_offset)
439
440		# update tariff itself
441		update_row = (" UPDATE tbl_tariffs "
442		" SET name = %s, energy_category_id = %s, tariff_type = %s, unit_of_price = %s, "
443		" valid_from_datetime_utc = %s , valid_through_datetime_utc = %s "
444		" WHERE id = %s ")
445		cursor.execute(update_row, (name,
446		energy_category_id,
447		tariff_type,
448		unit_of_price,
449		valid_from,
450		valid_through,
451		id_,))
452		cnx.commit()
453
454		# update prices of the tariff
455		if tariff_type == 'block':
456	View Code Duplication	if 'block' not in new_values['data'].keys() or new_values['data']['block'] is None:
		0 ignored issues – show Duplication introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
457		cursor.close()
458		cnx.disconnect()
459		raise falcon.HTTPError(falcon.HTTP_400,
460		title='API.BAD_REQUEST',
461		description='API.INVALID_TARIFF_BLOCK_PRICING')
462		else:
463		# remove all (possible) exist prices
464		cursor.execute(" DELETE FROM tbl_tariffs_blocks "
465		" WHERE tariff_id = %s ",
466		(id_,))
467
468		cursor.execute(" DELETE FROM tbl_tariffs_timeofuses "
469		" WHERE tariff_id = %s ",
470		(id_,))
471		cnx.commit()
472
473		for block in new_values['data']['block']:
474		cursor.execute(" INSERT INTO tbl_tariffs_blocks "
475		" (tariff_id, start_amount, end_amount, price) "
476		" VALUES (%s, %s, %s, %s) ",
477		(id_, block['start_amount'], block['end_amount'], block['price']))
478		cnx.commit()
479		elif tariff_type == 'timeofuse':
480	View Code Duplication	if 'timeofuse' not in new_values['data'].keys() or new_values['data']['timeofuse'] is None:
		0 ignored issues – show Duplication introduced 2020-11-24 10:14 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated in your project. Loading history...
481		cursor.close()
482		cnx.disconnect()
483		raise falcon.HTTPError(falcon.HTTP_400,
484		title='API.BAD_REQUEST',
485		description='API.INVALID_TARIFF_TIME_OF_USE_PRICING')
486		else:
487		# remove all (possible) exist prices
488		cursor.execute(" DELETE FROM tbl_tariffs_blocks "
489		" WHERE tariff_id = %s ",
490		(id_,))
491
492		cursor.execute(" DELETE FROM tbl_tariffs_timeofuses "
493		" WHERE tariff_id = %s ",
494		(id_,))
495		cnx.commit()
496
497		for timeofuse in new_values['data']['timeofuse']:
498		add_timeofuse = (" INSERT INTO tbl_tariffs_timeofuses "
499		" (tariff_id, start_time_of_day, end_time_of_day, peak_type, price) "
500		" VALUES (%s, %s, %s, %s, %s) ")
501		cursor.execute(add_timeofuse, (id_,
502		timeofuse['start_time_of_day'],
503		timeofuse['end_time_of_day'],
504		timeofuse['peak_type'],
505		timeofuse['price']))
506		cnx.commit()
507
508		cursor.close()
509		cnx.disconnect()
510		resp.status = falcon.HTTP_200
511
512
513

myems / myems-api

Pull Request — master (#1)

tariff.py (5 issues)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like