MyEMS /
myems
| @@ 112-207 (lines=96) @@ | ||
| 109 | ######################################################################################################################## |
|
| 110 | # Get tariffs by energy item |
|
| 111 | ######################################################################################################################## |
|
| 112 | def get_energy_item_tariffs(cost_center_id, energy_item_id, start_datetime_utc, end_datetime_utc): |
|
| 113 | # todo: verify parameters |
|
| 114 | if cost_center_id is None: |
|
| 115 | return dict() |
|
| 116 | ||
| 117 | # get timezone offset in minutes, this value will be returned to client |
|
| 118 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
|
| 119 | if config.utc_offset[0] == '-': |
|
| 120 | timezone_offset = -timezone_offset |
|
| 121 | ||
| 122 | tariff_dict = collections.OrderedDict() |
|
| 123 | ||
| 124 | cnx = None |
|
| 125 | cursor = None |
|
| 126 | try: |
|
| 127 | cnx = mysql.connector.connect(**config.myems_system_db) |
|
| 128 | cursor = cnx.cursor() |
|
| 129 | query_tariffs = (" SELECT t.id, t.valid_from_datetime_utc, t.valid_through_datetime_utc " |
|
| 130 | " FROM tbl_tariffs t, tbl_cost_centers_tariffs cct, tbl_energy_items ei " |
|
| 131 | " WHERE ei.id = %s AND " |
|
| 132 | " t.energy_category_id = ei.energy_category_id AND " |
|
| 133 | " t.id = cct.tariff_id AND " |
|
| 134 | " cct.cost_center_id = %s AND " |
|
| 135 | " t.valid_through_datetime_utc >= %s AND " |
|
| 136 | " t.valid_from_datetime_utc <= %s " |
|
| 137 | " ORDER BY t.valid_from_datetime_utc ") |
|
| 138 | cursor.execute(query_tariffs, (energy_item_id, cost_center_id, start_datetime_utc, end_datetime_utc,)) |
|
| 139 | rows_tariffs = cursor.fetchall() |
|
| 140 | except Exception as e: |
|
| 141 | print(str(e)) |
|
| 142 | if cursor: |
|
| 143 | cursor.close() |
|
| 144 | if cnx: |
|
| 145 | cnx.close() |
|
| 146 | return dict() |
|
| 147 | ||
| 148 | if rows_tariffs is None or len(rows_tariffs) == 0: |
|
| 149 | if cursor: |
|
| 150 | cursor.close() |
|
| 151 | if cnx: |
|
| 152 | cnx.close() |
|
| 153 | return dict() |
|
| 154 | ||
| 155 | for row in rows_tariffs: |
|
| 156 | tariff_dict[row[0]] = {'valid_from_datetime_utc': row[1], |
|
| 157 | 'valid_through_datetime_utc': row[2], |
|
| 158 | 'rates': list()} |
|
| 159 | ||
| 160 | try: |
|
| 161 | query_timeofuse_tariffs = (" SELECT tariff_id, start_time_of_day, end_time_of_day, price " |
|
| 162 | " FROM tbl_tariffs_timeofuses " |
|
| 163 | " WHERE tariff_id IN ( " + ', '.join(map(str, tariff_dict.keys())) + ")" |
|
| 164 | " ORDER BY tariff_id, start_time_of_day ") |
|
| 165 | cursor.execute(query_timeofuse_tariffs, ) |
|
| 166 | rows_timeofuse_tariffs = cursor.fetchall() |
|
| 167 | except Exception as e: |
|
| 168 | print(str(e)) |
|
| 169 | if cursor: |
|
| 170 | cursor.close() |
|
| 171 | if cnx: |
|
| 172 | cnx.close() |
|
| 173 | return dict() |
|
| 174 | ||
| 175 | if cursor: |
|
| 176 | cursor.close() |
|
| 177 | if cnx: |
|
| 178 | cnx.close() |
|
| 179 | ||
| 180 | if rows_timeofuse_tariffs is None or len(rows_timeofuse_tariffs) == 0: |
|
| 181 | return dict() |
|
| 182 | ||
| 183 | for row in rows_timeofuse_tariffs: |
|
| 184 | tariff_dict[row[0]]['rates'].append({'start_time_of_day': row[1], |
|
| 185 | 'end_time_of_day': row[2], |
|
| 186 | 'price': row[3]}) |
|
| 187 | ||
| 188 | result = dict() |
|
| 189 | for tariff_id, tariff_value in tariff_dict.items(): |
|
| 190 | current_datetime_utc = tariff_value['valid_from_datetime_utc'] |
|
| 191 | while current_datetime_utc < tariff_value['valid_through_datetime_utc']: |
|
| 192 | for rate in tariff_value['rates']: |
|
| 193 | current_datetime_local = current_datetime_utc + timedelta(minutes=timezone_offset) |
|
| 194 | seconds_since_midnight = (current_datetime_local - |
|
| 195 | current_datetime_local.replace(hour=0, |
|
| 196 | second=0, |
|
| 197 | microsecond=0, |
|
| 198 | tzinfo=None)).total_seconds() |
|
| 199 | if rate['start_time_of_day'].total_seconds() <= \ |
|
| 200 | seconds_since_midnight < rate['end_time_of_day'].total_seconds(): |
|
| 201 | result[current_datetime_utc] = rate['price'] |
|
| 202 | break |
|
| 203 | ||
| 204 | # start from the next time slot |
|
| 205 | current_datetime_utc += timedelta(minutes=config.minutes_to_count) |
|
| 206 | ||
| 207 | return {k: v for k, v in result.items() if start_datetime_utc <= k <= end_datetime_utc} |
|
| 208 | ||
| @@ 12-106 (lines=95) @@ | ||
| 9 | ######################################################################################################################## |
|
| 10 | # Get tariffs by energy category |
|
| 11 | ######################################################################################################################## |
|
| 12 | def get_energy_category_tariffs(cost_center_id, energy_category_id, start_datetime_utc, end_datetime_utc): |
|
| 13 | # todo: verify parameters |
|
| 14 | if cost_center_id is None: |
|
| 15 | return dict() |
|
| 16 | ||
| 17 | # get timezone offset in minutes, this value will be returned to client |
|
| 18 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
|
| 19 | if config.utc_offset[0] == '-': |
|
| 20 | timezone_offset = -timezone_offset |
|
| 21 | ||
| 22 | tariff_dict = collections.OrderedDict() |
|
| 23 | ||
| 24 | cnx = None |
|
| 25 | cursor = None |
|
| 26 | try: |
|
| 27 | cnx = mysql.connector.connect(**config.myems_system_db) |
|
| 28 | cursor = cnx.cursor() |
|
| 29 | query_tariffs = (" SELECT t.id, t.valid_from_datetime_utc, t.valid_through_datetime_utc " |
|
| 30 | " FROM tbl_tariffs t, tbl_cost_centers_tariffs cct " |
|
| 31 | " WHERE t.energy_category_id = %s AND " |
|
| 32 | " t.id = cct.tariff_id AND " |
|
| 33 | " cct.cost_center_id = %s AND " |
|
| 34 | " t.valid_through_datetime_utc >= %s AND " |
|
| 35 | " t.valid_from_datetime_utc <= %s " |
|
| 36 | " ORDER BY t.valid_from_datetime_utc ") |
|
| 37 | cursor.execute(query_tariffs, (energy_category_id, cost_center_id, start_datetime_utc, end_datetime_utc,)) |
|
| 38 | rows_tariffs = cursor.fetchall() |
|
| 39 | except Exception as e: |
|
| 40 | print(str(e)) |
|
| 41 | if cursor: |
|
| 42 | cursor.close() |
|
| 43 | if cnx: |
|
| 44 | cnx.close() |
|
| 45 | return dict() |
|
| 46 | ||
| 47 | if rows_tariffs is None or len(rows_tariffs) == 0: |
|
| 48 | if cursor: |
|
| 49 | cursor.close() |
|
| 50 | if cnx: |
|
| 51 | cnx.close() |
|
| 52 | return dict() |
|
| 53 | ||
| 54 | for row in rows_tariffs: |
|
| 55 | tariff_dict[row[0]] = {'valid_from_datetime_utc': row[1], |
|
| 56 | 'valid_through_datetime_utc': row[2], |
|
| 57 | 'rates': list()} |
|
| 58 | ||
| 59 | try: |
|
| 60 | query_timeofuse_tariffs = (" SELECT tariff_id, start_time_of_day, end_time_of_day, price " |
|
| 61 | " FROM tbl_tariffs_timeofuses " |
|
| 62 | " WHERE tariff_id IN ( " + ', '.join(map(str, tariff_dict.keys())) + ")" |
|
| 63 | " ORDER BY tariff_id, start_time_of_day ") |
|
| 64 | cursor.execute(query_timeofuse_tariffs, ) |
|
| 65 | rows_timeofuse_tariffs = cursor.fetchall() |
|
| 66 | except Exception as e: |
|
| 67 | print(str(e)) |
|
| 68 | if cursor: |
|
| 69 | cursor.close() |
|
| 70 | if cnx: |
|
| 71 | cnx.close() |
|
| 72 | return dict() |
|
| 73 | ||
| 74 | if cursor: |
|
| 75 | cursor.close() |
|
| 76 | if cnx: |
|
| 77 | cnx.close() |
|
| 78 | ||
| 79 | if rows_timeofuse_tariffs is None or len(rows_timeofuse_tariffs) == 0: |
|
| 80 | return dict() |
|
| 81 | ||
| 82 | for row in rows_timeofuse_tariffs: |
|
| 83 | tariff_dict[row[0]]['rates'].append({'start_time_of_day': row[1], |
|
| 84 | 'end_time_of_day': row[2], |
|
| 85 | 'price': row[3]}) |
|
| 86 | ||
| 87 | result = dict() |
|
| 88 | for tariff_id, tariff_value in tariff_dict.items(): |
|
| 89 | current_datetime_utc = tariff_value['valid_from_datetime_utc'] |
|
| 90 | while current_datetime_utc < tariff_value['valid_through_datetime_utc']: |
|
| 91 | for rate in tariff_value['rates']: |
|
| 92 | current_datetime_local = current_datetime_utc + timedelta(minutes=timezone_offset) |
|
| 93 | seconds_since_midnight = (current_datetime_local - |
|
| 94 | current_datetime_local.replace(hour=0, |
|
| 95 | second=0, |
|
| 96 | microsecond=0, |
|
| 97 | tzinfo=None)).total_seconds() |
|
| 98 | if rate['start_time_of_day'].total_seconds() <= \ |
|
| 99 | seconds_since_midnight < rate['end_time_of_day'].total_seconds(): |
|
| 100 | result[current_datetime_utc] = rate['price'] |
|
| 101 | break |
|
| 102 | ||
| 103 | # start from the next time slot |
|
| 104 | current_datetime_utc += timedelta(minutes=config.minutes_to_count) |
|
| 105 | ||
| 106 | return {k: v for k, v in result.items() if start_datetime_utc <= k <= end_datetime_utc} |
|
| 107 | ||
| 108 | ||
| 109 | ######################################################################################################################## |
|
| @@ 253-350 (lines=98) @@ | ||
| 250 | # Get peak types of tariff by energy category |
|
| 251 | # peak types: toppeak, onpeak, midpeak, offpeak, deep |
|
| 252 | ######################################################################################################################## |
|
| 253 | def get_energy_category_peak_types(cost_center_id, energy_category_id, start_datetime_utc, end_datetime_utc): |
|
| 254 | # todo: validate parameters |
|
| 255 | if cost_center_id is None: |
|
| 256 | return dict() |
|
| 257 | ||
| 258 | start_datetime_utc = start_datetime_utc.replace(tzinfo=None) |
|
| 259 | end_datetime_utc = end_datetime_utc.replace(tzinfo=None) |
|
| 260 | ||
| 261 | # get timezone offset in minutes, this value will be returned to client |
|
| 262 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
|
| 263 | if config.utc_offset[0] == '-': |
|
| 264 | timezone_offset = -timezone_offset |
|
| 265 | ||
| 266 | tariff_dict = collections.OrderedDict() |
|
| 267 | ||
| 268 | cnx = None |
|
| 269 | cursor = None |
|
| 270 | try: |
|
| 271 | cnx = mysql.connector.connect(**config.myems_system_db) |
|
| 272 | cursor = cnx.cursor() |
|
| 273 | query_tariffs = (" SELECT t.id, t.valid_from_datetime_utc, t.valid_through_datetime_utc " |
|
| 274 | " FROM tbl_tariffs t, tbl_cost_centers_tariffs cct " |
|
| 275 | " WHERE t.energy_category_id = %s AND " |
|
| 276 | " t.id = cct.tariff_id AND " |
|
| 277 | " cct.cost_center_id = %s AND " |
|
| 278 | " t.valid_through_datetime_utc >= %s AND " |
|
| 279 | " t.valid_from_datetime_utc <= %s " |
|
| 280 | " ORDER BY t.valid_from_datetime_utc ") |
|
| 281 | cursor.execute(query_tariffs, (energy_category_id, cost_center_id, start_datetime_utc, end_datetime_utc,)) |
|
| 282 | rows_tariffs = cursor.fetchall() |
|
| 283 | except Exception as e: |
|
| 284 | print(str(e)) |
|
| 285 | if cnx: |
|
| 286 | cnx.close() |
|
| 287 | if cursor: |
|
| 288 | cursor.close() |
|
| 289 | return dict() |
|
| 290 | ||
| 291 | if rows_tariffs is None or len(rows_tariffs) == 0: |
|
| 292 | if cursor: |
|
| 293 | cursor.close() |
|
| 294 | if cnx: |
|
| 295 | cnx.close() |
|
| 296 | return dict() |
|
| 297 | ||
| 298 | for row in rows_tariffs: |
|
| 299 | tariff_dict[row[0]] = {'valid_from_datetime_utc': row[1], |
|
| 300 | 'valid_through_datetime_utc': row[2], |
|
| 301 | 'rates': list()} |
|
| 302 | ||
| 303 | try: |
|
| 304 | query_timeofuse_tariffs = (" SELECT tariff_id, start_time_of_day, end_time_of_day, peak_type " |
|
| 305 | " FROM tbl_tariffs_timeofuses " |
|
| 306 | " WHERE tariff_id IN ( " + ', '.join(map(str, tariff_dict.keys())) + ")" |
|
| 307 | " ORDER BY tariff_id, start_time_of_day ") |
|
| 308 | cursor.execute(query_timeofuse_tariffs, ) |
|
| 309 | rows_timeofuse_tariffs = cursor.fetchall() |
|
| 310 | except Exception as e: |
|
| 311 | print(str(e)) |
|
| 312 | if cnx: |
|
| 313 | cnx.close() |
|
| 314 | if cursor: |
|
| 315 | cursor.close() |
|
| 316 | return dict() |
|
| 317 | ||
| 318 | if cursor: |
|
| 319 | cursor.close() |
|
| 320 | if cnx: |
|
| 321 | cnx.close() |
|
| 322 | ||
| 323 | if rows_timeofuse_tariffs is None or len(rows_timeofuse_tariffs) == 0: |
|
| 324 | return dict() |
|
| 325 | ||
| 326 | for row in rows_timeofuse_tariffs: |
|
| 327 | tariff_dict[row[0]]['rates'].append({'start_time_of_day': row[1], |
|
| 328 | 'end_time_of_day': row[2], |
|
| 329 | 'peak_type': row[3]}) |
|
| 330 | ||
| 331 | result = dict() |
|
| 332 | for tariff_id, tariff_value in tariff_dict.items(): |
|
| 333 | current_datetime_utc = tariff_value['valid_from_datetime_utc'] |
|
| 334 | while current_datetime_utc < tariff_value['valid_through_datetime_utc']: |
|
| 335 | for rate in tariff_value['rates']: |
|
| 336 | current_datetime_local = current_datetime_utc + timedelta(minutes=timezone_offset) |
|
| 337 | seconds_since_midnight = (current_datetime_local - |
|
| 338 | current_datetime_local.replace(hour=0, |
|
| 339 | second=0, |
|
| 340 | microsecond=0, |
|
| 341 | tzinfo=None)).total_seconds() |
|
| 342 | if rate['start_time_of_day'].total_seconds() <= \ |
|
| 343 | seconds_since_midnight < rate['end_time_of_day'].total_seconds(): |
|
| 344 | result[current_datetime_utc] = rate['peak_type'] |
|
| 345 | break |
|
| 346 | ||
| 347 | # start from the next time slot |
|
| 348 | current_datetime_utc += timedelta(minutes=config.minutes_to_count) |
|
| 349 | ||
| 350 | return {k: v for k, v in result.items() if start_datetime_utc <= k <= end_datetime_utc} |
|
| 351 | ||
| 352 | ||
| 353 | ######################################################################################################################## |
|
| @@ 149-246 (lines=98) @@ | ||
| 146 | ######################################################################################################################## |
|
| 147 | # Get tariffs by energy category |
|
| 148 | ######################################################################################################################## |
|
| 149 | def get_energy_category_tariffs(cost_center_id, energy_category_id, start_datetime_utc, end_datetime_utc): |
|
| 150 | # todo: validate parameters |
|
| 151 | if cost_center_id is None: |
|
| 152 | return dict() |
|
| 153 | ||
| 154 | start_datetime_utc = start_datetime_utc.replace(tzinfo=None) |
|
| 155 | end_datetime_utc = end_datetime_utc.replace(tzinfo=None) |
|
| 156 | ||
| 157 | # get timezone offset in minutes, this value will be returned to client |
|
| 158 | timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6]) |
|
| 159 | if config.utc_offset[0] == '-': |
|
| 160 | timezone_offset = -timezone_offset |
|
| 161 | ||
| 162 | tariff_dict = collections.OrderedDict() |
|
| 163 | ||
| 164 | cnx = None |
|
| 165 | cursor = None |
|
| 166 | try: |
|
| 167 | cnx = mysql.connector.connect(**config.myems_system_db) |
|
| 168 | cursor = cnx.cursor() |
|
| 169 | query_tariffs = (" SELECT t.id, t.valid_from_datetime_utc, t.valid_through_datetime_utc " |
|
| 170 | " FROM tbl_tariffs t, tbl_cost_centers_tariffs cct " |
|
| 171 | " WHERE t.energy_category_id = %s AND " |
|
| 172 | " t.id = cct.tariff_id AND " |
|
| 173 | " cct.cost_center_id = %s AND " |
|
| 174 | " t.valid_through_datetime_utc >= %s AND " |
|
| 175 | " t.valid_from_datetime_utc <= %s " |
|
| 176 | " ORDER BY t.valid_from_datetime_utc ") |
|
| 177 | cursor.execute(query_tariffs, (energy_category_id, cost_center_id, start_datetime_utc, end_datetime_utc,)) |
|
| 178 | rows_tariffs = cursor.fetchall() |
|
| 179 | except Exception as e: |
|
| 180 | print(str(e)) |
|
| 181 | if cnx: |
|
| 182 | cnx.close() |
|
| 183 | if cursor: |
|
| 184 | cursor.close() |
|
| 185 | return dict() |
|
| 186 | ||
| 187 | if rows_tariffs is None or len(rows_tariffs) == 0: |
|
| 188 | if cursor: |
|
| 189 | cursor.close() |
|
| 190 | if cnx: |
|
| 191 | cnx.close() |
|
| 192 | return dict() |
|
| 193 | ||
| 194 | for row in rows_tariffs: |
|
| 195 | tariff_dict[row[0]] = {'valid_from_datetime_utc': row[1], |
|
| 196 | 'valid_through_datetime_utc': row[2], |
|
| 197 | 'rates': list()} |
|
| 198 | ||
| 199 | try: |
|
| 200 | query_timeofuse_tariffs = (" SELECT tariff_id, start_time_of_day, end_time_of_day, price " |
|
| 201 | " FROM tbl_tariffs_timeofuses " |
|
| 202 | " WHERE tariff_id IN ( " + ', '.join(map(str, tariff_dict.keys())) + ")" |
|
| 203 | " ORDER BY tariff_id, start_time_of_day ") |
|
| 204 | cursor.execute(query_timeofuse_tariffs, ) |
|
| 205 | rows_timeofuse_tariffs = cursor.fetchall() |
|
| 206 | except Exception as e: |
|
| 207 | print(str(e)) |
|
| 208 | if cnx: |
|
| 209 | cnx.close() |
|
| 210 | if cursor: |
|
| 211 | cursor.close() |
|
| 212 | return dict() |
|
| 213 | ||
| 214 | if cursor: |
|
| 215 | cursor.close() |
|
| 216 | if cnx: |
|
| 217 | cnx.close() |
|
| 218 | ||
| 219 | if rows_timeofuse_tariffs is None or len(rows_timeofuse_tariffs) == 0: |
|
| 220 | return dict() |
|
| 221 | ||
| 222 | for row in rows_timeofuse_tariffs: |
|
| 223 | tariff_dict[row[0]]['rates'].append({'start_time_of_day': row[1], |
|
| 224 | 'end_time_of_day': row[2], |
|
| 225 | 'price': row[3]}) |
|
| 226 | ||
| 227 | result = dict() |
|
| 228 | for tariff_id, tariff_value in tariff_dict.items(): |
|
| 229 | current_datetime_utc = tariff_value['valid_from_datetime_utc'] |
|
| 230 | while current_datetime_utc < tariff_value['valid_through_datetime_utc']: |
|
| 231 | for rate in tariff_value['rates']: |
|
| 232 | current_datetime_local = current_datetime_utc + timedelta(minutes=timezone_offset) |
|
| 233 | seconds_since_midnight = (current_datetime_local - |
|
| 234 | current_datetime_local.replace(hour=0, |
|
| 235 | second=0, |
|
| 236 | microsecond=0, |
|
| 237 | tzinfo=None)).total_seconds() |
|
| 238 | if rate['start_time_of_day'].total_seconds() <= \ |
|
| 239 | seconds_since_midnight < rate['end_time_of_day'].total_seconds(): |
|
| 240 | result[current_datetime_utc] = rate['price'] |
|
| 241 | break |
|
| 242 | ||
| 243 | # start from the next time slot |
|
| 244 | current_datetime_utc += timedelta(minutes=config.minutes_to_count) |
|
| 245 | ||
| 246 | return {k: v for k, v in result.items() if start_datetime_utc <= k <= end_datetime_utc} |
|
| 247 | ||
| 248 | ||
| 249 | ######################################################################################################################## |
|
