# Python import import xlwt from datetime import datetime, date, time, timedelta import math import csv from itertools import chain # Django import from django.http import HttpResponse from django.template.loader import render_to_string from django.shortcuts import render_to_response, redirect from django.core.urlresolvers import reverse from django.core.exceptions import ObjectDoesNotExist from django.views.decorators.csrf import csrf_exempt # LeakLess Monitor import from Applications.Measure.models import LldevicePdlagMeasure, LldevicePdlagMeasureArchive,LldevicePdlagRealtime, LldeviceMails,LldeviceMAG8000Measure, LldeviceMAG8000Realtime, LldeviceMAG8000Status from Applications.Devices.models import Lldevicelist from Applications.Users.views import user_get_language,user_get_information from Applications.Devices.views import devices_return_by_id from Applications.Measure.support_units import * def measure_get_measure_data(device_id, start_time, end_time): current_datetime = datetime.now() measurement_archive = {} device = devices_return_by_id(device_id) if device.loc_type == 'PDL-AG': measurement = LldevicePdlagMeasure.objects.filter( timestamp__gte=start_time, timestamp__lte=end_time, lldevicelist=device_id).order_by('timestamp') if (current_datetime - start_time) > timedelta(days=34): measurement_archive = LldevicePdlagMeasureArchive.objects.filter( timestamp__gte=start_time, timestamp__lte=end_time, lldevicelist=device_id).order_by('timestamp') return list(chain(measurement_archive, measurement)) elif device.loc_type == 'MAG8000': measurement = LldeviceMAG8000Measure.objects.filter( timestamp__gte=start_time, timestamp__lte=end_time, lldevicelist=device_id).order_by('timestamp') return list(measurement) else: return [] def measure_get_multiple_measure_data(device_id_list, start_time, end_time): current_datetime = datetime.now() measurement_archive = {} measurement = LldevicePdlagMeasure.objects.filter( timestamp__gte=start_time, timestamp__lte=end_time, lldevicelist__in=device_id_list).order_by('timestamp') if (current_datetime - start_time) > timedelta(days=34): measurement_archive = LldevicePdlagMeasureArchive.objects.filter( timestamp__gte=start_time, timestamp__lte=end_time, lldevicelist__in=device_id_list).order_by('timestamp') return list(chain(measurement_archive, measurement)) def measure_generate_excel(request): if request.user.is_authenticated(): rows_per_file = 65000 device_id = request.POST['device_id'] device_identifier = request.POST['device_identifier'] excel_start_date_str = request.POST['startDateInput'] excel_start_date = datetime.strptime(excel_start_date_str, "%Y-%m-%d %H:%M:%S") excel_end_date_str = request.POST['endDateInput'] excel_end_date = datetime.strptime(excel_end_date_str, "%Y-%m-%d %H:%M:%S") device = devices_return_by_id(device_id) measurements = measure_get_measure_data(device_id, excel_start_date, excel_end_date) workbook = xlwt.Workbook() align = xlwt.Alignment() align.wrap = True align.vert = align.VERT_CENTER align.horz = align.HORZ_CENTER # borders borders = xlwt.Borders() borders.bottom = xlwt.Borders.THIN borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN color_yellow = xlwt.Pattern() color_yellow.pattern = xlwt.Pattern.SOLID_PATTERN color_yellow.pattern_fore_colour = xlwt.Style.colour_map['yellow'] color_blue = xlwt.Pattern() color_blue.pattern = xlwt.Pattern.SOLID_PATTERN color_blue.pattern_fore_colour = xlwt.Style.colour_map['light_blue'] font_bold = xlwt.Font() # Create the Font font_bold.bold = True style_yellow = xlwt.XFStyle() style_yellow.borders = borders style_yellow.font = font_bold style_yellow.alignment = align style_yellow.pattern = color_yellow style_blue = xlwt.XFStyle() style_blue.borders = borders style_blue.font = font_bold style_blue.alignment = align style_blue.pattern = color_blue style_white = xlwt.XFStyle() style_white.borders = borders data_count = len(measurements) page_count = int(math.ceil(float(data_count) / float(rows_per_file))) page_counter=1 start_index=0 end_index=0 if data_count > rows_per_file: end_index=rows_per_file else: end_index=data_count if data_count==0: # Prazni podaci sheet = workbook.add_sheet("No data") sheet.row(0).height_mismatch = True sheet.row(0).height = 256 * 3 if request.session.get('lang', 'en-gb') == 'hr': sheet.write_merge(0, 0, 0, 6, 'Nema podataka za uredaj: ' + device_id + ' [' + device_identifier + ']\nVrijeme od: ' + excel_start_date_str + ' do ' + excel_end_date_str, style_yellow) else: sheet.write_merge(0, 0, 0, 6, 'No available data for device: ' + device_id + ' [' + device_identifier + ']\nTime from: ' + excel_start_date_str + ' to ' + excel_end_date_str, style_yellow) else: while data_count>0: sheet = workbook.add_sheet("Sheet"+str(page_counter)) sheet.row(0).height_mismatch = True sheet.row(0).height = 256 * 3 sheet.row(1).height_mismatch = True sheet.row(1).height = 256 * 4 column = sheet.col(0) column.width = 256 * 30 if request.session.get('lang', 'en-gb') == 'hr': sheet.write(1, 0, 'Vrijeme', style_blue) sheet.write(1, 1, 'CH1 Tlak [' + device.get_pressure_current_unit() + "]" , style_blue) sheet.write(1, 2, 'CH1 Protok [' + device.get_flow_current_unit() + "/s]" , style_blue) sheet.write(1, 3, 'CH1 Ukupni protok [' + device.get_total_flow_current_unit() + "]" , style_blue) sheet.write(1, 4, 'CH2 Tlak [' + device.get_pressure_current_unit() + "]" , style_blue) sheet.write(1, 5, 'CH2 Protok [' + device.get_flow_current_unit() + "/s]" , style_blue) sheet.write(1, 6, 'CH2 Ukupni protok [' + device.get_total_flow_current_unit() + "]" , style_blue) else: sheet.write(1, 0, 'Time', style_blue) sheet.write(1, 1, 'CH1 Pressure [' + device.get_pressure_current_unit() + "]" , style_blue) sheet.write(1, 2, 'CH1 Flow [' + device.get_flow_current_unit() + "/s]" , style_blue) sheet.write(1, 3, 'CH1 Total Flow [' + device.get_total_flow_current_unit() + "]" , style_blue) sheet.write(1, 4, 'CH2 Pressure [' + device.get_pressure_current_unit() + "]" , style_blue) sheet.write(1, 5, 'CH2 Flow [' + device.get_flow_current_unit() + "/s]" , style_blue) sheet.write(1, 6, 'CH2 Total Flow [' + device.get_total_flow_current_unit() + "]" , style_blue) xls_row_counter = 2 measure_sliced=measurements[start_index:end_index] for measure in measure_sliced: time=measure.timestamp.strftime("%Y-%m-%d %H:%M:%S") sheet.write(xls_row_counter, 0, time , style_white) sheet.write(xls_row_counter, 1, measure.ch1_pressure , style_white) sheet.write(xls_row_counter, 2, measure.ch1_flow , style_white) sheet.write(xls_row_counter, 3, measure.ch1_total_flow , style_white) sheet.write(xls_row_counter, 4, measure.ch2_pressure , style_white) sheet.write(xls_row_counter, 5, measure.ch2_flow , style_white) sheet.write(xls_row_counter, 6, measure.ch2_total_flow , style_white) data_count-=1 xls_row_counter += 1 if (data_count != 0): sheet.write_merge(xls_row_counter, xls_row_counter, 0, 6, 'More data on next page', style_yellow) excel_start_date_str =measurements[start_index].timestamp.strftime("%Y-%m-%d %H:%M:%S") excel_end_date_str =measurements[end_index-1].timestamp.strftime("%Y-%m-%d %H:%M:%S") start_index = end_index if data_count > rows_per_file: end_index +=rows_per_file else: end_index += data_count if request.session.get('lang', 'en-gb') == 'hr': sheet.name="Stranica "+str(page_counter)+" od "+str(page_count) sheet.write_merge(0, 0, 0, 4, 'Uredaj: ' + device_id + ' [' + device_identifier + ']\nVrijeme od: ' + excel_start_date_str + ' do ' + excel_end_date_str, style_yellow) sheet.write_merge(0, 0, 5, 6, sheet.name, style_yellow) else: sheet.name="Page "+str(page_counter)+" of "+str(page_count) sheet.write_merge(0, 0, 0, 4, 'Device: ' + device_id + ' [' + device_identifier + ']\nTime from: ' + excel_start_date_str + ' to ' + excel_end_date_str, style_yellow) sheet.write_merge(0, 0, 5, 6, sheet.name, style_yellow) page_counter += 1 download_name = datetime.now().strftime("%Y-%m-%d_%H-%M-%S") + '_' + device_id + '.xls' response = HttpResponse(content_type="application/vnd.ms-excel") response['Content-Disposition'] = "attachment; filename=%s" % download_name workbook.save(response) return response else: return HttpResponse('Invalid user') def measure_get_unit_recalculation_window(request): if request.user.is_authenticated(): device_id = request.GET.get('device_id') device = devices_return_by_id(device_id) pressure_unit = device.get_pressure_current_unit() flow_unit = device.get_flow_current_unit() total_flow_unit = device.get_total_flow_current_unit() recalculate_pressure_unit = device.get_pressure_recalculate_unit() recalculate_flow_unit = device.get_flow_recalculate_unit() recalculate_total_flow_unit = device.get_total_flow_recalculate_unit() pressure_unit_enum_list = measure_return_recalculate_pressure_unit_options() flow_unit_enum_list = measure_return_recalculate_flow_unit_options() total_flow_unit_enum_list = measure_return_recalculate_total_flow_unit_options() return render_to_string('unitRecalculateForm.html', {'lang' : user_get_language(request), 'pressure_unit' : pressure_unit, 'flow_unit' : flow_unit, 'total_flow_unit' : total_flow_unit, 'pressure_unit_enum_list' : pressure_unit_enum_list, 'flow_unit_enum_list' : flow_unit_enum_list, 'total_flow_unit_enum_list' : total_flow_unit_enum_list, 'recalculate_pressure_unit' : recalculate_pressure_unit, 'recalculate_flow_unit' : recalculate_flow_unit, 'recalculate_total_flow_unit': recalculate_total_flow_unit}) else: return redirect(reverse('login_page')) def measure_show_device_realtime_info(request): if request.user.is_authenticated(): table_data=get_device_realtime_info_data(request) return render_to_response('deviceInfoTable.html',{ 'user_object': request.user, 'table_data': table_data, 'lang':user_get_language(request)}) else: return redirect(reverse('login_page')) def get_device_realtime_info_data(request): device_list=[] row_data = [] permissions = request.user.get_all_permissions() lang = user_get_language(request) for perm in permissions: if not perm.startswith('DeviceList.'): continue permission_name = perm.split(".", 1)[-1] device_list.append(permission_name) devices_info=[] Return = { 'header_columns': [], 'body_rows' : [] } if request.user.is_superuser: if lang == 'hr': Return['header_columns'].append("ID") Return['header_columns'].append("IDENTIFIKATOR") Return['header_columns'].append("ZADNJE OSVJEZENJE") Return['header_columns'].append("TIP") Return['header_columns'].append("VERZIJA") Return['header_columns'].append("BATERIJA") Return['header_columns'].append("SIGNAL") Return['header_columns'].append("GRUPE") else: Return['header_columns'].append("ID") Return['header_columns'].append("IDENTIFIER") Return['header_columns'].append("LAST REFRESH") Return['header_columns'].append("TYPE") Return['header_columns'].append("VERSION") Return['header_columns'].append("BATTERY") Return['header_columns'].append("SIGNAL") Return['header_columns'].append("GROUPS") else: if lang == 'hr': Return['header_columns'].append("ID") Return['header_columns'].append("IDENTIFIKATOR") Return['header_columns'].append("ZADNJE OSVJEZENJE") Return['header_columns'].append("TLAK CH1") Return['header_columns'].append("TLAK CH2") Return['header_columns'].append("PROTOK CH1") Return['header_columns'].append("PROTOK CH2") Return['header_columns'].append("UK. PROTOK CH1") Return['header_columns'].append("UK. PROTOK CH2") else: Return['header_columns'].append("ID") Return['header_columns'].append("IDENTIFIER") Return['header_columns'].append("LAST REFRESH") Return['header_columns'].append("PRESSURE CH1") Return['header_columns'].append("PRESSURE CH2") Return['header_columns'].append("FLOW CH1") Return['header_columns'].append("FLOW CH2") Return['header_columns'].append("TOTAL FLOW CH1") Return['header_columns'].append("TOTAL FLOW CH2") device_list = Lldevicelist.objects.filter(id__in=device_list) for device in device_list: if device.get_device_type()=='PDL-AG': row = get_row_data_pdlag(request, device) elif device.get_device_type()=='MAG8000': row = get_row_data_mag8000(request, device) else: row = [] row_data.append(row) Return['body_rows'] = row_data return Return def get_row_data_pdlag(request, device): row_data=[] try: measure_data = LldevicePdlagRealtime.objects.get(lldevicelist=device) except ObjectDoesNotExist: return [] if request.user.is_superuser: row_data.append({'value': device.get_device_id(), 'sort': False}) row_data.append({'value': device.get_device_identifier(), 'sort': False}) row_data.append({'value': measure_data.get_elapsed_time(), 'sort' : True, 'sort_value': measure_data.get_elapsed_ms_time()}) row_data.append({'value': device.get_device_type(), 'sort' : False}) row_data.append({'value': device.get_device_version(), 'sort' : False}) row_data.append({'value': device.get_device_info_battery(), 'sort' : False}) # battery row_data.append({'value': device.get_device_info_signal(), 'sort' : False}) # signal row_data.append({'value': device.get_device_groups(), 'sort' : False}) else: row_data.append({'value': device.get_device_id(), 'sort': False}) row_data.append({'value': device.get_device_identifier(), 'sort': False}) row_data.append({'value': measure_data.get_elapsed_time(), 'sort' : True, 'sort_value': measure_data.get_elapsed_ms_time()}) row_data.append({'value': str(measure_data.get_recalculated_ch1_pressure()) + " " + device.get_pressure_recalculate_unit(), 'sort': False}) row_data.append({'value': str(measure_data.get_recalculated_ch2_pressure()) + " " + device.get_pressure_recalculate_unit(), 'sort': False}) row_data.append({'value': str(measure_data.get_recalculated_ch1_flow()) + " " + device.get_flow_recalculate_unit(), 'sort': False}) row_data.append({'value': str(measure_data.get_recalculated_ch1_flow()) + " " + device.get_flow_recalculate_unit(), 'sort': False}) row_data.append({'value': str(measure_data.get_recalculated_ch1_total_flow()) + " " + device.get_total_flow_recalculate_unit(), 'sort': False}) row_data.append({'value': str(measure_data.get_recalculated_ch2_total_flow()) + " " + device.get_total_flow_recalculate_unit(), 'sort': False}) return row_data def get_row_data_mag8000(request, device): row_data=[] try: measure_data = LldeviceMAG8000Realtime.objects.get(lldevicelist = device) except ObjectDoesNotExist: return [] if request.user.is_superuser: row_data.append({'value': device.get_device_id(), 'sort': False}) row_data.append({'value': device.get_device_identifier(), 'sort': False}) row_data.append({'value': measure_data.get_elapsed_time(), 'sort' : True, 'sort_value': measure_data.get_elapsed_ms_time()}) row_data.append({'value': device.get_device_type(), 'sort' : False}) row_data.append({'value': device.get_device_version(), 'sort' : False}) row_data.append({'value': device.get_device_info_battery(), 'sort' : False}) # battery row_data.append({'value': device.get_device_info_signal(), 'sort' : False}) # signal row_data.append({'value': device.get_device_groups(), 'sort' : False}) else: row_data.append({'value': device.get_device_id(), 'sort': False}) row_data.append({'value': device.get_device_identifier(), 'sort': False}) row_data.append({'value': measure_data.get_elapsed_time(), 'sort' : True, 'sort_value': measure_data.get_elapsed_ms_time()}) row_data.append({'value': '?', 'sort': False}) row_data.append({'value': '?', 'sort': False}) row_data.append({'value': '?', 'sort': False}) row_data.append({'value': '?', 'sort': False}) row_data.append({'value': '?', 'sort': False}) row_data.append({'value': '?', 'sort': False}) return row_data @csrf_exempt def measure_accept_mail(request): if request.method == 'POST': sender = request.POST.get('sender') sender_save = (sender[:140] + '..') if len(sender) > 140 else sender recipient = request.POST.get('recipient') recipient_save = (recipient[:140] + '..') if len(recipient) > 140 else recipient subject = request.POST.get('subject', '') subject_save = (subject[:40] + '..') if len(subject) > 40 else subject body_plain = request.POST.get('body-plain', '') body_plain_save = (body_plain[:140] + '..') if len(body_plain) > 140 else body_plain # note: other MIME headers are also posted here... # attachments: attachment=None for key in request.FILES: file = request.FILES[key] if file.size<1000000: attachment=file # do something with the file new_mail = LldeviceMails(sender=sender_save, recipient=recipient_save, subject=subject_save, body_plain=body_plain_save,attachment=attachment) if not measure_parse_mag8000_mail(new_mail): new_mail.save() return HttpResponse('OK') def measure_parse_mag8000_mail(mail): if mail.attachment is None: return False attachment_name = mail.attachment.name if attachment_name.startswith('MAG8000_'): id = attachment_name[attachment_name.find("_") + 1:attachment_name.rfind("_")] #id=(attachment_name.partition('MAG8000_')[-1])[0:-21] try: device=Lldevicelist.objects.get(id=id) if device.loc_type != 'MAG8000': return False except ObjectDoesNotExist: return False reader = csv.DictReader(mail.attachment.file, delimiter=';',fieldnames = ( "Timestamp","FlowValue","FlowUnit","TotalFlow1", "TotalFlow2", "TotalFlow3", "TotalUnit", "AnalogCurrent", "AnalogVoltage","Battery")) last_row = None for row in reader: last_row = row timestamp_data = row['Timestamp'] if len(timestamp_data) == 16: timestamp = timestamp_data else: timestamp = timestamp_data[:13]+":"+timestamp_data[13:] new_measure=LldeviceMAG8000Measure( timestamp=timestamp, flow_value=row['FlowValue'], flow_unit=row['FlowUnit'], total_flow1 = row['TotalFlow1'], total_flow2 =row['TotalFlow2'], total_flow3 =row['TotalFlow3'], total_unit=row['TotalUnit'], analog_current =row['AnalogCurrent'], analog_voltage =row['AnalogVoltage'], lldevicelist = device) new_measure.save() new_status = LldeviceMAG8000Status(timestamp=timestamp, battery=last_row['Battery'], lldevicelist=device) new_status.save() if last_row is not None: timestamp_data = last_row['Timestamp'] if len(timestamp_data) == 16: timestamp = timestamp_data else: timestamp = timestamp_data[:13]+":"+timestamp_data[13:] try: device_realtime = LldeviceMAG8000Realtime.objects.get(lldevicelist=device) device_realtime.timestamp = timestamp device_realtime.flow_value = last_row['FlowValue'] device_realtime.flow_unit = last_row['FlowUnit'] device_realtime.total_flow1 = last_row['TotalFlow1'] device_realtime.total_flow2 = last_row['TotalFlow2'] device_realtime.total_flow3 = last_row['TotalFlow3'] device_realtime.total_unit = last_row['TotalUnit'] device_realtime.analog_current = last_row['AnalogCurrent'] device_realtime.analog_voltage = last_row['AnalogVoltage'] device_realtime.save() device.rem_info = 'BAT: '+last_row['Battery']+"%" device.save() except ObjectDoesNotExist: pass else: return False return True