Issues (1577)

myems-api/excelexporters/equipmentincome.py (4 issues)

1
import base64
2
from core.utilities import get_translation
3
import os
4
import re
5
import uuid
6
from decimal import Decimal
7
import openpyxl.utils.cell as format_cell
8
from openpyxl import Workbook
9
from openpyxl.chart import PieChart, LineChart, Reference
10
from openpyxl.chart.label import DataLabelList
11
from openpyxl.drawing.image import Image
12
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
13
from core.utilities import round2
14
15
####################################################################################################################
16
# PROCEDURES
17
# Step 1: Validate the report data
18
# Step 2: Generate excel file
19
# Step 3: Encode the excel file bytes to Base64
20
####################################################################################################################
21
22
23 View Code Duplication
def export(report,
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
24
           name,
25
           base_period_start_datetime_local,
26
           base_period_end_datetime_local,
27
           reporting_start_datetime_local,
28
           reporting_end_datetime_local,
29
           period_type,
30
           language):
31
    ####################################################################################################################
32
    # Step 1: Validate the report data
33
    ####################################################################################################################
34
    if report is None:
35
        return None
36
37
    ####################################################################################################################
38
    # Step 2: Generate excel file from the report data
39
    ####################################################################################################################
40
    filename = generate_excel(report,
41
                              name,
42
                              base_period_start_datetime_local,
43
                              base_period_end_datetime_local,
44
                              reporting_start_datetime_local,
45
                              reporting_end_datetime_local,
46
                              period_type,
47
                              language)
48
    ####################################################################################################################
49
    # Step 3: Encode the excel file to Base64
50
    ####################################################################################################################
51
    binary_file_data = b''
52
    try:
53
        with open(filename, 'rb') as binary_file:
54
            binary_file_data = binary_file.read()
55
    except IOError as ex:
56
        print(str(ex))
57
58
    # Base64 encode the bytes
59
    base64_encoded_data = base64.b64encode(binary_file_data)
60
    # get the Base64 encoded data using human-readable characters.
61
    base64_message = base64_encoded_data.decode('utf-8')
62
    # delete the file from server
63
    try:
64
        os.remove(filename)
65
    except NotImplementedError as ex:
66
        print(str(ex))
67
    return base64_message
68
69
70
def generate_excel(report,
71
                   name,
72
                   base_period_start_datetime_local,
73
                   base_period_end_datetime_local,
74
                   reporting_start_datetime_local,
75
                   reporting_end_datetime_local,
76
                   period_type,
77
                   language):
78
    trans = get_translation(language)
79
    trans.install()
80
    _ = trans.gettext
81
    wb = Workbook()
82
    ws = wb.active
83
    ws.title = "EquipmentIncome"
84
85
    # Row height
86
    ws.row_dimensions[1].height = 102
87
    for i in range(2, 2000 + 1):
88
        ws.row_dimensions[i].height = 42
89
90
    # Col width
91
    ws.column_dimensions['A'].width = 1.5
92
93
    ws.column_dimensions['B'].width = 25.0
94
95
    for i in range(ord('C'), ord('Z')):
96
        ws.column_dimensions[chr(i)].width = 15.0
97
98
    # Font
99
    name_font = Font(name='Arial', size=15, bold=True)
100
    title_font = Font(name='Arial', size=15, bold=True)
101
102
    table_fill = PatternFill(fill_type='solid', fgColor='90ee90')
103
    f_border = Border(left=Side(border_style='medium'),
104
                      right=Side(border_style='medium'),
105
                      bottom=Side(border_style='medium'),
106
                      top=Side(border_style='medium')
107
                      )
108
    b_border = Border(bottom=Side(border_style='medium'), )
109
110
    b_c_alignment = Alignment(vertical='bottom',
111
                              horizontal='center',
112
                              text_rotation=0,
113
                              wrap_text=True,
114
                              shrink_to_fit=False,
115
                              indent=0)
116
    c_c_alignment = Alignment(vertical='center',
117
                              horizontal='center',
118
                              text_rotation=0,
119
                              wrap_text=True,
120
                              shrink_to_fit=False,
121
                              indent=0)
122
    b_r_alignment = Alignment(vertical='bottom',
123
                              horizontal='right',
124
                              text_rotation=0,
125
                              wrap_text=True,
126
                              shrink_to_fit=False,
127
                              indent=0)
128
129
    # Img
130
    img = Image("excelexporters/myems.png")
131
    ws.add_image(img, 'A1')
132
133
    # Title
134
    ws['B3'].alignment = b_r_alignment
135
    ws['B3'] = _('Name') + ':'
136
    ws['C3'].border = b_border
137
    ws['C3'].alignment = b_c_alignment
138
    ws['C3'] = name
139
140
    ws['D3'].alignment = b_r_alignment
141
    ws['D3'] = _('Period Type') + ':'
142
    ws['E3'].border = b_border
143
    ws['E3'].alignment = b_c_alignment
144
    ws['E3'] = period_type
145
146
    ws['B4'].alignment = b_r_alignment
147
    ws['B4'] = _('Reporting Start Datetime') + ':'
148
    ws['C4'].border = b_border
149
    ws['C4'].alignment = b_c_alignment
150
    ws['C4'] = reporting_start_datetime_local
151
152
    ws['D4'].alignment = b_r_alignment
153
    ws['D4'] = _('Reporting End Datetime') + ':'
154
    ws['E4'].border = b_border
155
    ws['E4'].alignment = b_c_alignment
156
    ws['E4'] = reporting_end_datetime_local
157
158
    is_base_period_timestamp_exists_flag = is_base_period_timestamp_exists(report['base_period'])
159
160
    if is_base_period_timestamp_exists_flag:
161
        ws['B5'].alignment = b_r_alignment
162
        ws['B5'] = _('Base Period Start Datetime') + ':'
163
        ws['C5'].border = b_border
164
        ws['C5'].alignment = b_c_alignment
165
        ws['C5'] = base_period_start_datetime_local
166
167
        ws['D5'].alignment = b_r_alignment
168
        ws['D5'] = _('Base Period End Datetime') + ':'
169
        ws['E5'].border = b_border
170
        ws['E5'].alignment = b_c_alignment
171
        ws['E5'] = base_period_end_datetime_local
172
173
    if "reporting_period" not in report.keys() or \
174
            "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
175
        filename = str(uuid.uuid4()) + '.xlsx'
0 ignored issues
show
Comprehensibility Best Practice introduced by
The variable str does not seem to be defined.
Loading history...
176
        wb.save(filename)
177
178
        return filename
179
180
    ####################################################################################################################
181
    reporting_period_data = report['reporting_period']
182
    if "names" not in reporting_period_data.keys() or \
183
            reporting_period_data['names'] is None or \
184
            len(reporting_period_data['names']) == 0:
185
        for i in range(7, 11 + 1):
186
            ws.row_dimensions[i].height = 0.1
187
    else:
188
        ws['B7'].font = title_font
189
        ws['B7'] = name + ' ' + _('Reporting Period Income')
190
        category = reporting_period_data['names']
191
        ca_len = len(category)
192
193
        ws.row_dimensions[8].height = 60
194
        ws['B8'].fill = table_fill
195
        ws['B8'].border = f_border
196
197
        ws['B9'].font = title_font
198
        ws['B9'].alignment = c_c_alignment
199
        ws['B9'] = _('Reporting Period Income')
200
        ws['B9'].border = f_border
201
202
        ws['B10'].font = title_font
203
        ws['B10'].alignment = c_c_alignment
204
        ws['B10'] = _('Increment Rate')
205
        ws['B10'].border = f_border
206
207
        col = ''
208
209
        for i in range(0, ca_len):
210
            col = chr(ord('C') + i)
211
212
            ws[col + '8'].fill = table_fill
213
            ws[col + '8'].font = name_font
214
            ws[col + '8'].alignment = c_c_alignment
215
            ws[col + '8'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
216
            ws[col + '8'].border = f_border
217
218
            ws[col + '9'].font = name_font
219
            ws[col + '9'].alignment = c_c_alignment
220
            ws[col + '9'] = round2(reporting_period_data['subtotals'][i], 2)
221
            ws[col + '9'].border = f_border
222
223
            ws[col + '10'].font = name_font
224
            ws[col + '10'].alignment = c_c_alignment
225
            ws[col + '10'] = str(round2(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
226
                if reporting_period_data['increment_rates'][i] is not None else "-"
227
            ws[col + '10'].border = f_border
228
229
        col = chr(ord(col) + 1)
230
231
        ws[col + '8'].fill = table_fill
232
        ws[col + '8'].font = name_font
233
        ws[col + '8'].alignment = c_c_alignment
234
        ws[col + '8'] = _("Total") + " (" + reporting_period_data['total_unit'] + ")"
235
        ws[col + '8'].border = f_border
236
237
        ws[col + '9'].font = name_font
238
        ws[col + '9'].alignment = c_c_alignment
239
        ws[col + '9'] = round2(reporting_period_data['total'], 2)
240
        ws[col + '9'].border = f_border
241
242
        ws[col + '10'].font = name_font
243
        ws[col + '10'].alignment = c_c_alignment
244
        ws[col + '10'] = str(round2(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
245
            if reporting_period_data['total_increment_rate'] is not None else "-"
246
        ws[col + '10'].border = f_border
247
248
    ####################################################################################################################
249
    current_row_number = 12
250 View Code Duplication
    if "subtotals" not in reporting_period_data.keys() or \
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
251
            reporting_period_data['subtotals'] is None or \
252
            len(reporting_period_data['subtotals']) == 0:
253
        pass
254
    else:
255
        ws['B' + str(current_row_number)].font = title_font
256
        ws['B' + str(current_row_number)] = name + ' ' + _('Incomes by Energy Category')
257
258
        current_row_number += 1
259
260
        table_start_row_number = current_row_number
261
262
        ws['B' + str(current_row_number)].fill = table_fill
263
        ws['B' + str(current_row_number)].font = name_font
264
        ws['B' + str(current_row_number)].alignment = c_c_alignment
265
        ws['B' + str(current_row_number)].border = f_border
266
267
        ws['C' + str(current_row_number)].fill = table_fill
268
        ws['C' + str(current_row_number)].font = name_font
269
        ws['C' + str(current_row_number)].alignment = c_c_alignment
270
        ws['C' + str(current_row_number)].border = f_border
271
        ws['C' + str(current_row_number)] = _('Incomes')
272
273
        ws['D' + str(current_row_number)].fill = table_fill
274
        ws['D' + str(current_row_number)].font = name_font
275
        ws['D' + str(current_row_number)].alignment = c_c_alignment
276
        ws['D' + str(current_row_number)].border = f_border
277
        ws['D' + str(current_row_number)] = _('Proportion')
278
279
        current_row_number += 1
280
281
        ca_len = len(reporting_period_data['names'])
282
        income_sum = Decimal(0.0)
283
        for i in range(0, ca_len):
284
            income_sum = round(reporting_period_data['subtotals'][i], 2) + income_sum
285
        for i in range(0, ca_len):
286
            ws['B' + str(current_row_number)].font = title_font
287
            ws['B' + str(current_row_number)].alignment = c_c_alignment
288
            ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
289
            ws['B' + str(current_row_number)].border = f_border
290
291
            ws['C' + str(current_row_number)].font = title_font
292
            ws['C' + str(current_row_number)].alignment = c_c_alignment
293
            ws['C' + str(current_row_number)].border = f_border
294
            ws['C' + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 2)
295
296
            ws['D' + str(current_row_number)].font = title_font
297
            ws['D' + str(current_row_number)].alignment = c_c_alignment
298
            ws['D' + str(current_row_number)].border = f_border
299
            ws['D' + str(current_row_number)] = '{:.2%}'.format(round2(
300
                reporting_period_data['subtotals'][i], 2) / income_sum) \
301
                if income_sum is not None and income_sum != Decimal(0.0) \
302
                else ''
303
304
            current_row_number += 1
305
306
        table_end_row_number = current_row_number - 1
307
308
        pie = PieChart()
309
        pie.title = name + ' ' + _('Incomes by Energy Category')
310
        labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
311
        pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
312
        pie.add_data(pie_data, titles_from_data=True)
313
        pie.set_categories(labels)
314
        pie.height = 6.6
315
        pie.width = 9
316
        s1 = pie.series[0]
317
        s1.dLbls = DataLabelList()
318
        s1.dLbls.showCatName = False
319
        s1.dLbls.showVal = False
320
        s1.dLbls.showPercent = True
321
        table_cell = 'D' + str(table_start_row_number)
322
        ws.add_chart(pie, table_cell)
323
324
        if ca_len < 4:
325
            current_row_number = current_row_number - ca_len + 4
326
327
        current_row_number += 1
328
    ####################################################################################################################
329
    table_start_draw_flag = current_row_number + 1
330
331
    if "timestamps" not in reporting_period_data.keys() or \
332
            reporting_period_data['timestamps'] is None or \
333
            len(reporting_period_data['timestamps']) == 0:
334
        pass
335
    else:
336
        if not is_base_period_timestamp_exists_flag:
337
            reporting_period_data = report['reporting_period']
338
            times = reporting_period_data['timestamps']
339
            ca_len = len(report['reporting_period']['names'])
340
            real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
341
            ws['B' + str(current_row_number)].font = title_font
342
            ws['B' + str(current_row_number)] = name + ' ' + _('Detailed Data')
343
344
            current_row_number += 1
345
            # 1: Stand for blank line  2: Stand for title
346
            current_row_number += ca_len * 6 + real_timestamps_len * 6 + 1 + 2
347
            table_start_row_number = current_row_number
348
349
            time = times[0]
350
            has_data = False
351
352
            if len(time) > 0:
353
                has_data = True
354
355
            if has_data:
356
357
                ws.row_dimensions[current_row_number].height = 60
358
                current_col_number = 2
359
                col = format_cell.get_column_letter(current_col_number)
360
                ws[col + str(current_row_number)].fill = table_fill
361
                ws[col + str(current_row_number)].font = title_font
362
                ws[col + str(current_row_number)].border = f_border
363
                ws[col + str(current_row_number)].alignment = c_c_alignment
364
                ws[col + str(current_row_number)] = _('Datetime')
365
366
                for i in range(0, ca_len):
367
                    current_col_number += 1
368
                    col = format_cell.get_column_letter(current_col_number)
369
370
                    ws[col + str(current_row_number)].fill = table_fill
371
                    ws[col + str(current_row_number)].font = title_font
372
                    ws[col + str(current_row_number)].alignment = c_c_alignment
373
                    ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
374
                        " (" + reporting_period_data['units'][i] + ")"
375
                    ws[col + str(current_row_number)].border = f_border
376
377
                current_col_number += 1
378
                col = format_cell.get_column_letter(current_col_number)
379
                ws[col + str(current_row_number)].fill = table_fill
380
                ws[col + str(current_row_number)].font = title_font
381
                ws[col + str(current_row_number)].alignment = c_c_alignment
382
                ws[col + str(current_row_number)] = _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
383
                ws[col + str(current_row_number)].border = f_border
384
385
                current_row_number += 1
386
387
                for i in range(0, len(time)):
388
                    current_col_number = 2
389
                    col = format_cell.get_column_letter(current_col_number)
390
391
                    ws[col + str(current_row_number)].font = title_font
392
                    ws[col + str(current_row_number)].alignment = c_c_alignment
393
                    ws[col + str(current_row_number)] = time[i]
394
                    ws[col + str(current_row_number)].border = f_border
395
396
                    total = Decimal(0.0)
397
398
                    for j in range(0, ca_len):
399
                        current_col_number += 1
400
                        col = format_cell.get_column_letter(current_col_number)
401
402
                        ws[col + str(current_row_number)].font = title_font
403
                        ws[col + str(current_row_number)].alignment = c_c_alignment
404
                        ws[col + str(current_row_number)] = round2(reporting_period_data['values'][j][i], 2)
405
                        total += reporting_period_data['values'][j][i]
406
                        ws[col + str(current_row_number)].border = f_border
407
408
                    current_col_number += 1
409
                    col = format_cell.get_column_letter(current_col_number)
410
                    ws[col + str(current_row_number)].font = title_font
411
                    ws[col + str(current_row_number)].alignment = c_c_alignment
412
                    ws[col + str(current_row_number)] = round2(total, 2)
413
                    ws[col + str(current_row_number)].border = f_border
414
415
                    current_row_number += 1
416
417
                table_end_row_number = current_row_number - 1
418
419
                current_col_number = 2
420
                col = format_cell.get_column_letter(current_col_number)
421
422
                ws[col + str(current_row_number)].font = title_font
423
                ws[col + str(current_row_number)].alignment = c_c_alignment
424
                ws[col + str(current_row_number)] = _('Subtotal')
425
                ws[col + str(current_row_number)].border = f_border
426
427
                subtotals = Decimal(0.0)
428
429
                for i in range(0, ca_len):
430
                    current_col_number += 1
431
                    col = format_cell.get_column_letter(current_col_number)
432
                    ws[col + str(current_row_number)].font = title_font
433
                    ws[col + str(current_row_number)].alignment = c_c_alignment
434
                    ws[col + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 2)
435
                    subtotals += reporting_period_data['subtotals'][i]
436
                    ws[col + str(current_row_number)].border = f_border
437
438
                    # line
439
                    line = LineChart()
440
                    line.title = _('Reporting Period Income') + ' - ' \
441
                        + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
442
                    labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
443
                    line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number,
444
                                          max_row=table_end_row_number)
445
                    line.add_data(line_data, titles_from_data=True)
446
                    line.set_categories(labels)
447
                    line_data = line.series[0]
448
                    line_data.marker.symbol = "auto"
449
                    line_data.smooth = True
450
                    line.x_axis.crosses = 'min'
451
                    line.height = 8.25
452
                    line.width = 24
453
                    chart_col = 'B'
454
                    chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
455
                    ws.add_chart(line, chart_cell)
456
457
                current_col_number += 1
458
                col = format_cell.get_column_letter(current_col_number)
459
                ws[col + str(current_row_number)].font = title_font
460
                ws[col + str(current_row_number)].alignment = c_c_alignment
461
                ws[col + str(current_row_number)] = round2(subtotals, 2)
462
                ws[col + str(current_row_number)].border = f_border
463
464
                current_row_number += 2
465
        else:
466
            base_period_data = report['base_period']
467
            reporting_period_data = report['reporting_period']
468
            base_period_timestamps = base_period_data['timestamps']
469
            reporting_period_timestamps = reporting_period_data['timestamps']
470
            # Tip:
471
            #     base_period_data['names'] == reporting_period_data['names']
472
            #     base_period_data['units'] == reporting_period_data['units']
473
            base_period_data_ca_len = len(base_period_data['names'])
474
            reporting_period_data_ca_len = len(reporting_period_data['names'])
475
            real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
476
            ws['B' + str(current_row_number)].font = title_font
477
            ws['B' + str(current_row_number)] = name + ' ' + _('Detailed Data')
478
479
            current_row_number += 1
480
            # 1: Stand for blank line  2: Stand for title
481
            current_row_number += reporting_period_data_ca_len * 6 + real_timestamps_len * 6 + 1 + 2
482
            table_start_row_number = current_row_number
483
484
            has_data = False
485
486
            if len(base_period_timestamps[0]) or len(reporting_period_timestamps[0]) > 0:
487
                has_data = True
488
489
            if has_data:
490
                ws.row_dimensions[current_row_number].height = 60
491
                current_col_number = 2
492
                col = format_cell.get_column_letter(current_col_number)
493
                ws[col + str(current_row_number)].fill = table_fill
494
                ws[col + str(current_row_number)].font = title_font
495
                ws[col + str(current_row_number)].border = f_border
496
                ws[col + str(current_row_number)].alignment = c_c_alignment
497
                ws[col + str(current_row_number)] = _('Base Period') + " - " + _('Datetime')
498
499
                for i in range(0, base_period_data_ca_len):
500
                    current_col_number += 1
501
                    col = format_cell.get_column_letter(current_col_number)
502
503
                    ws[col + str(current_row_number)].fill = table_fill
504
                    ws[col + str(current_row_number)].font = title_font
505
                    ws[col + str(current_row_number)].alignment = c_c_alignment
506
                    ws[col + str(current_row_number)] = _('Base Period') + " - " + base_period_data['names'][i] + \
507
                        " (" + base_period_data['units'][i] + ")"
508
                    ws[col + str(current_row_number)].border = f_border
509
510
                current_col_number += 1
511
                col = format_cell.get_column_letter(current_col_number)
512
                ws[col + str(current_row_number)].fill = table_fill
513
                ws[col + str(current_row_number)].font = title_font
514
                ws[col + str(current_row_number)].alignment = c_c_alignment
515
                ws[col + str(current_row_number)] = _('Base Period') + " - " \
516
                    + _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
517
                ws[col + str(current_row_number)].border = f_border
518
519
                current_col_number += 1
520
                col = format_cell.get_column_letter(current_col_number)
521
522
                ws[col + str(current_row_number)].fill = table_fill
523
                ws[col + str(current_row_number)].font = title_font
524
                ws[col + str(current_row_number)].border = f_border
525
                ws[col + str(current_row_number)].alignment = c_c_alignment
526
                ws[col + str(current_row_number)] = _('Reporting Period') + " - " + _('Datetime')
527
528
                for i in range(0, reporting_period_data_ca_len):
529
                    current_col_number += 1
530
                    col = format_cell.get_column_letter(current_col_number)
531
                    ws[col + str(current_row_number)].fill = table_fill
532
                    ws[col + str(current_row_number)].font = title_font
533
                    ws[col + str(current_row_number)].alignment = c_c_alignment
534
                    ws[col + str(current_row_number)] = _('Reporting Period') + " - " \
535
                        + reporting_period_data['names'][i] + " (" + \
536
                        reporting_period_data['units'][i] + ")"
537
                    ws[col + str(current_row_number)].border = f_border
538
539
                current_col_number += 1
540
                col = format_cell.get_column_letter(current_col_number)
541
                ws[col + str(current_row_number)].fill = table_fill
542
                ws[col + str(current_row_number)].font = title_font
543
                ws[col + str(current_row_number)].alignment = c_c_alignment
544
                ws[col + str(current_row_number)] = _('Reporting Period') + " - " \
545
                    + _('Total') + '(' + report['reporting_period']['total_unit'] + ')'
546
                ws[col + str(current_row_number)].border = f_border
547
548
                current_row_number += 1
549
550
                max_timestamps_len = len(base_period_timestamps[0]) \
551
                    if len(base_period_timestamps[0]) >= len(reporting_period_timestamps[0]) \
552
                    else len(reporting_period_timestamps[0])
553
554
                for i in range(0, max_timestamps_len):
555
                    current_col_number = 2
556
                    col = format_cell.get_column_letter(current_col_number)
557
                    ws[col + str(current_row_number)].font = title_font
558
                    ws[col + str(current_row_number)].alignment = c_c_alignment
559
                    ws[col + str(current_row_number)] = base_period_timestamps[0][i] \
560
                        if i < len(base_period_timestamps[0]) else None
561
                    ws[col + str(current_row_number)].border = f_border
562
563
                    base_period_total = Decimal(0.0)
564
565
                    for j in range(0, base_period_data_ca_len):
566
                        current_col_number += 1
567
                        col = format_cell.get_column_letter(current_col_number)
568
569
                        ws[col + str(current_row_number)].font = title_font
570
                        ws[col + str(current_row_number)].alignment = c_c_alignment
571
                        ws[col + str(current_row_number)] = round2(base_period_data['values'][j][i], 2) \
572
                            if i < len(base_period_data['values'][j]) else None
573
                        if i < len(base_period_timestamps[0]):
574
                            base_period_total += base_period_data['values'][j][i]
575
                        ws[col + str(current_row_number)].border = f_border
576
577
                    current_col_number += 1
578
                    col = format_cell.get_column_letter(current_col_number)
579
                    ws[col + str(current_row_number)].font = title_font
580
                    ws[col + str(current_row_number)].alignment = c_c_alignment
581
                    ws[col + str(current_row_number)] = round2(base_period_total, 2) \
582
                        if i < len(base_period_timestamps[0]) else None
583
                    ws[col + str(current_row_number)].border = f_border
584
585
                    current_col_number += 1
586
                    col = format_cell.get_column_letter(current_col_number)
587
588
                    ws[col + str(current_row_number)].font = title_font
589
                    ws[col + str(current_row_number)].alignment = c_c_alignment
590
                    ws[col + str(current_row_number)] = reporting_period_timestamps[0][i] \
591
                        if i < len(reporting_period_timestamps[0]) else None
592
                    ws[col + str(current_row_number)].border = f_border
593
594
                    reporting_period_total = Decimal(0.0)
595
596
                    for j in range(0, reporting_period_data_ca_len):
597
                        current_col_number += 1
598
                        col = format_cell.get_column_letter(current_col_number)
599
600
                        ws[col + str(current_row_number)].font = title_font
601
                        ws[col + str(current_row_number)].alignment = c_c_alignment
602
                        ws[col + str(current_row_number)] = round2(reporting_period_data['values'][j][i], 2) \
603
                            if i < len(reporting_period_data['values'][j]) else None
604
                        if i < len(reporting_period_timestamps[0]):
605
                            reporting_period_total += reporting_period_data['values'][j][i]
606
                        ws[col + str(current_row_number)].border = f_border
607
608
                    current_col_number += 1
609
                    col = format_cell.get_column_letter(current_col_number)
610
                    ws[col + str(current_row_number)].font = title_font
611
                    ws[col + str(current_row_number)].alignment = c_c_alignment
612
                    ws[col + str(current_row_number)] = round2(reporting_period_total, 2) \
613
                        if i < len(reporting_period_timestamps[0]) else None
614
                    ws[col + str(current_row_number)].border = f_border
615
616
                    current_row_number += 1
617
618
                current_col_number = 2
619
                col = format_cell.get_column_letter(current_col_number)
620
                ws[col + str(current_row_number)].font = title_font
621
                ws[col + str(current_row_number)].alignment = c_c_alignment
622
                ws[col + str(current_row_number)] = _('Subtotal')
623
                ws[col + str(current_row_number)].border = f_border
624
625
                base_period_subtotals = Decimal(0.0)
626
627
                for i in range(0, base_period_data_ca_len):
628
                    current_col_number += 1
629
                    col = format_cell.get_column_letter(current_col_number)
630
                    ws[col + str(current_row_number)].font = title_font
631
                    ws[col + str(current_row_number)].alignment = c_c_alignment
632
                    ws[col + str(current_row_number)] = round2(base_period_data['subtotals'][i], 2)
633
                    base_period_subtotals += base_period_data['subtotals'][i]
634
                    ws[col + str(current_row_number)].border = f_border
635
636
                current_col_number += 1
637
                col = format_cell.get_column_letter(current_col_number)
638
                ws[col + str(current_row_number)].font = title_font
639
                ws[col + str(current_row_number)].alignment = c_c_alignment
640
                ws[col + str(current_row_number)] = round2(base_period_subtotals, 2)
641
                ws[col + str(current_row_number)].border = f_border
642
643
                current_col_number += 1
644
                col = format_cell.get_column_letter(current_col_number)
645
646
                ws[col + str(current_row_number)].font = title_font
647
                ws[col + str(current_row_number)].alignment = c_c_alignment
648
                ws[col + str(current_row_number)] = _('Subtotal')
649
                ws[col + str(current_row_number)].border = f_border
650
651
                reporting_period_subtotals = Decimal(0.0)
652
653
                for i in range(0, reporting_period_data_ca_len):
654
                    current_col_number += 1
655
                    col = format_cell.get_column_letter(current_col_number)
656
                    ws[col + str(current_row_number)].font = title_font
657
                    ws[col + str(current_row_number)].alignment = c_c_alignment
658
                    ws[col + str(current_row_number)] = round2(reporting_period_data['subtotals'][i], 2)
659
                    reporting_period_subtotals += reporting_period_data['subtotals'][i]
660
                    ws[col + str(current_row_number)].border = f_border
661
662
                current_col_number += 1
663
                col = format_cell.get_column_letter(current_col_number)
664
                ws[col + str(current_row_number)].font = title_font
665
                ws[col + str(current_row_number)].alignment = c_c_alignment
666
                ws[col + str(current_row_number)] = round2(reporting_period_subtotals, 2)
667
                ws[col + str(current_row_number)].border = f_border
668
669
                for i in range(0, reporting_period_data_ca_len):
670
                    # line
671
                    line = LineChart()
672
                    line.title = _('Base Period Income') + " / " \
673
                        + _('Reporting Period Income') + ' - ' \
674
                        + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
675
                    labels = Reference(ws, min_col=2 + base_period_data_ca_len + 1 + 1,
676
                                       min_row=table_start_row_number + 1,
677
                                       max_row=table_start_row_number + len(reporting_period_timestamps[0]))
678
                    base_line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number,
679
                                               max_row=table_start_row_number + len(reporting_period_timestamps[0]))
680
                    reporting_line_data = Reference(ws, min_col=3 + base_period_data_ca_len + 1 + 1 + i,
681
                                                    min_row=table_start_row_number,
682
                                                    max_row=table_start_row_number
683
                                                    + len(reporting_period_timestamps[0]))
684
                    line.add_data(base_line_data, titles_from_data=True)
685
                    line.add_data(reporting_line_data, titles_from_data=True)
686
                    line.set_categories(labels)
687
                    for j in range(len(line.series)):
688
                        line.series[j].marker.symbol = "auto"
689
                        line.series[j].smooth = True
690
                    line.x_axis.crosses = 'min'
691
                    line.height = 8.25
692
                    line.width = 24
693
                    chart_col = 'B'
694
                    chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
695
                    ws.add_chart(line, chart_cell)
696
697
                current_row_number += 2
698
    ####################################################################################################################
699
    current_sheet_parameters_row_number = table_start_draw_flag + len(reporting_period_data['names']) * 6 + 1
700
    if 'parameters' not in report.keys() or \
701
            report['parameters'] is None or \
702
            'names' not in report['parameters'].keys() or \
703
            report['parameters']['names'] is None or \
704
            len(report['parameters']['names']) == 0 or \
705
            'timestamps' not in report['parameters'].keys() or \
706
            report['parameters']['timestamps'] is None or \
707
            len(report['parameters']['timestamps']) == 0 or \
708
            'values' not in report['parameters'].keys() or \
709
            report['parameters']['values'] is None or \
710
            len(report['parameters']['values']) == 0 or \
711
            timestamps_data_all_equal_0(report['parameters']['timestamps']):
712
        pass
713
    else:
714
715
        ################################################################################################################
716
        # new worksheet
717
        ################################################################################################################
718
719
        parameters_data = report['parameters']
720
        parameters_names_len = len(parameters_data['names'])
721
722
        file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
723
        parameters_ws = wb.create_sheet(file_name + _('Parameters'))
724
725
        parameters_timestamps_data_max_len = \
726
            get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
727
728
        # Row height
729
        parameters_ws.row_dimensions[1].height = 102
730
        for i in range(2, 7 + 1):
731
            parameters_ws.row_dimensions[i].height = 42
732
733
        for i in range(8, parameters_timestamps_data_max_len + 10):
734
            parameters_ws.row_dimensions[i].height = 60
735
736
        # Col width
737
        parameters_ws.column_dimensions['A'].width = 1.5
738
739
        parameters_ws.column_dimensions['B'].width = 25.0
740
741
        for i in range(3, 12+parameters_names_len*3):
742
            parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
743
744
        # Img
745
        img = Image("excelexporters/myems.png")
746
        parameters_ws.add_image(img, 'A1')
747
748
        # Title
749
750
        parameters_ws['B3'].alignment = b_r_alignment
751
        parameters_ws['B3'] = _('Name') + ':'
752
        parameters_ws['C3'].border = b_border
753
        parameters_ws['C3'].alignment = b_c_alignment
754
        parameters_ws['C3'] = name
755
756
        parameters_ws['D3'].alignment = b_r_alignment
757
        parameters_ws['D3'] = _('Period Type') + ':'
758
        parameters_ws['E3'].border = b_border
759
        parameters_ws['E3'].alignment = b_c_alignment
760
        parameters_ws['E3'] = period_type
761
762
        parameters_ws['B4'].alignment = b_r_alignment
763
        parameters_ws['B4'] = _('Reporting Start Datetime') + ':'
764
        parameters_ws['C4'].border = b_border
765
        parameters_ws['C4'].alignment = b_c_alignment
766
        parameters_ws['C4'] = reporting_start_datetime_local
767
768
        parameters_ws['D4'].alignment = b_r_alignment
769
        parameters_ws['D4'] = _('Reporting End Datetime') + ':'
770
        parameters_ws['E4'].border = b_border
771
        parameters_ws['E4'].alignment = b_c_alignment
772
        parameters_ws['E4'] = reporting_end_datetime_local
773
774
        parameters_ws_current_row_number = 6
775
776
        parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
777
        parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + _('Parameters')
778
779
        parameters_ws_current_row_number += 1
780
781
        parameters_table_start_row_number = parameters_ws_current_row_number
782
783
        parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
784
785
        parameters_ws_current_row_number += 1
786
787
        table_current_col_number = 2
788
789
        for i in range(0, parameters_names_len):
790
791
            if len(parameters_data['timestamps'][i]) == 0:
792
                continue
793
794
            col = format_cell.get_column_letter(table_current_col_number)
795
796
            parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
797
            parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
798
799
            col = format_cell.get_column_letter(table_current_col_number + 1)
800
801
            parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
802
            parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
803
            parameters_ws[col + str(parameters_ws_current_row_number-1)].font = name_font
804
            parameters_ws[col + str(parameters_ws_current_row_number-1)].alignment = c_c_alignment
805
            parameters_ws[col + str(parameters_ws_current_row_number-1)] = parameters_data['names'][i]
806
807
            table_current_row_number = parameters_ws_current_row_number
808
809
            for j, value in enumerate(list(parameters_data['timestamps'][i])):
810
                col = format_cell.get_column_letter(table_current_col_number)
811
812
                parameters_ws[col + str(table_current_row_number)].border = f_border
813
                parameters_ws[col + str(table_current_row_number)].font = title_font
814
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
815
                parameters_ws[col + str(table_current_row_number)] = value
816
817
                col = format_cell.get_column_letter(table_current_col_number + 1)
818
819
                parameters_ws[col + str(table_current_row_number)].border = f_border
820
                parameters_ws[col + str(table_current_row_number)].font = title_font
821
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
822
                parameters_ws[col + str(table_current_row_number)] = round2(parameters_data['values'][i][j], 2)
823
824
                table_current_row_number += 1
825
826
            table_current_col_number = table_current_col_number + 3
827
828
        ################################################################################################################
829
        # parameters chart and parameters table
830
        ################################################################################################################
831
832
        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
833
        ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + _('Parameters')
834
835
        current_sheet_parameters_row_number += 1
836
837
        chart_start_row_number = current_sheet_parameters_row_number
838
839
        col_index = 0
840
841
        for i in range(0, parameters_names_len):
842
843
            if len(parameters_data['timestamps'][i]) == 0:
844
                continue
845
846
            line = LineChart()
847
            data_col = 3+col_index*3
848
            labels_col = 2+col_index*3
849
            col_index += 1
850
            line.title = _('Parameters') + ' - ' + \
851
                parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
852
            labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
853
                               max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
854
            line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
855
                                  max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
856
            line.add_data(line_data, titles_from_data=True)
857
            line.set_categories(labels)
858
            line_data = line.series[0]
859
            line_data.marker.symbol = "auto"
860
            line_data.smooth = True
861
            line.x_axis.crosses = 'min'
862
            line.height = 8.25
863
            line.width = 24
864
            chart_col = 'B'
865
            chart_cell = chart_col + str(chart_start_row_number)
866
            chart_start_row_number += 6
867
            ws.add_chart(line, chart_cell)
868
869
        current_sheet_parameters_row_number = chart_start_row_number
870
871
        current_sheet_parameters_row_number += 1
872
    ####################################################################################################################
873
    filename = str(uuid.uuid4()) + '.xlsx'
874
    wb.save(filename)
875
876
    return filename
877
878
879
def reporting_period_values_periodic_sum(reporting_period_data, periodic_index, ca_len):
880
    periodic_sum = Decimal(0.0)
881
    for i in range(0, ca_len):
882
        periodic_sum += reporting_period_data['values'][i][periodic_index]
883
884
    return periodic_sum
885
886
887
def timestamps_data_all_equal_0(lists):
888
    for i, value in enumerate(list(lists)):
889
        if len(value) > 0:
890
            return False
891
892
    return True
893
894
895
def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists):
896
    max_len = 0
897
    for i, value in enumerate(list(parameters_timestamps_lists)):
898
        if len(value) > max_len:
899
            max_len = len(value)
900
901
    return max_len
902
903
904
def timestamps_data_not_equal_0(lists):
905
    number = 0
906
    for i, value in enumerate(list(lists)):
907
        if len(value) > 0:
908
            number += 1
909
    return number
910
911
912 View Code Duplication
def is_base_period_timestamp_exists(base_period_data):
0 ignored issues
show
This code seems to be duplicated in your project.
Loading history...
913
    timestamps = base_period_data['timestamps']
914
915
    if len(timestamps) == 0:
916
        return False
917
918
    for timestamp in timestamps:
919
        if len(timestamp) > 0:
920
            return True
921
922
    return False
923