# Python imports import json, copy import xlwt # Django imports from django.http import JsonResponse from django.shortcuts import render from django.template.loader import render_to_string from django.core.exceptions import ObjectDoesNotExist from django.db.models import Q, Max, Min, Count, FloatField from django.template import RequestContext from django.db import connection # LeakLess Monitor imports from Applications.Measure.models import LldevicePdlagMeasure,LldevicePdlagMeasureArchive, Lldeviceannotations, LldevicePdlagTotalizers from Applications.Measure.views import * from Applications.Devices.views import devices_return_channel_labels from Applications.Users.views import user_get_device, user_get_information def totalizers_view(request): if request.user.is_authenticated(): device_list=Lldevicelist.objects.filter(loc_active=1) devices_treeview=[] for device in device_list: if request.user.has_perm("DeviceList."+str(device.id)): # Formirati treeview devices_treeview.append(totalizers_return_device_node(device)) user_information=user_get_information(request) user_device_type=user_get_device(request) return render_to_response('totalizers.html',{ 'user_object' : request.user, 'user_info' : user_information, 'user_device_type' : user_device_type, 'devices_treeview' : json.dumps(devices_treeview), 'lang' : user_get_language(request)}, RequestContext(request)) else: return HttpResponse('Invalid user') def totalizers_return_device_node(device): device_id = str(device.id) device_identifier="Unknown" if device.rem_identifier is not None: device_identifier=device.rem_identifier if device.loc_type == 'PDL-AG': return_data={ 'text' : device_id+" ["+device_identifier+"]", 'icon' : "glyphicon glyphicon-stop", 'selectedIcon' : "glyphicon glyphicon-stop", 'showCheckbox' : False, #'color' : "#000000", 'backColor' : "white", #'href' : "#node-1", 'selectable' : False, 'state': { 'checked' : True, 'disabled' : False, 'expanded' : False, 'selected' : False }, #'tags' : [device_identifier], 'nodes' : totalizers_return_measurement_nodes_pdlag(device_id) } elif device.loc_type == 'MAG8000': return_data={ 'text' : device_id+" ["+device_identifier+"]", 'icon' : "glyphicon glyphicon-stop", 'selectedIcon' : "glyphicon glyphicon-stop", 'showCheckbox' : False, #'color' : "#000000", 'backColor' : "white", #'href' : "#node-1", 'selectable' : False, 'state': { 'checked' : True, 'disabled' : True, 'expanded' : False, 'selected' : False }, 'tags' : ['N/A'], 'nodes' : totalizers_return_measurement_nodes_mag8000(device_id) } else: return_data={} return return_data def totalizers_return_measurement_nodes_pdlag(device_id): return_data=[] node_info={ 'text' : "Unknown", 'icon' : "glyphicon glyphicon-stop", 'selectedIcon' : "glyphicon glyphicon-stop", #'color' : "#000000", 'selectable ' : True, 'state': { 'checked' : False, 'disabled' : False, 'expanded' : False, 'selected' : False }, 'tags' : ['available'] } channel_labels = devices_return_channel_labels(device_id) node_info_ch1_total_flow=node_info.copy() node_info_ch1_total_flow['text'] = 'CH1 Total flow ('+channel_labels['ch1_flow_label']+')' return_data.append(node_info_ch1_total_flow) node_info_ch2_total_flow=node_info.copy() node_info_ch2_total_flow['text'] = 'CH2 Total flow ('+channel_labels['ch2_flow_label']+')' return_data.append(node_info_ch2_total_flow) return return_data def totalizers_return_measurement_nodes_mag8000(device_id): return_data=[] return return_data def totalizers_return_data(request): if request.user.is_authenticated(): if request.is_ajax(): if request.method == 'POST': data_response = {} return_data =json.loads(request.body) data_start_date = datetime.strptime(return_data['start_date'], "%Y-%m-%d").date() data_end_date = datetime.strptime(return_data['end_date'], "%Y-%m-%d").date() data_id_list = return_data['device_id_list'] delta = data_end_date - data_start_date date_list=[] for i in range(delta.days + 1): date_list.append(data_start_date + timedelta(days=i)) for id in data_id_list: data_array = [] device = Lldevicelist.objects.get(id=id) totalizer_list = LldevicePdlagTotalizers.objects.filter( date__gte=data_start_date, date__lte=data_end_date, lldevicelist=device).order_by('date') total_flow_unit = device.get_total_flow_recalculate_unit() total_flow_factor = device.get_total_flow_factor() for date in date_list: total_flow_ch1=0 total_flow_ch2=0 try: total_on_date=totalizer_list.get(date=date) total_flow_ch1=total_on_date.ch1_total_flow total_flow_ch2=total_on_date.ch2_total_flow except ObjectDoesNotExist: time_start= datetime(date.year,date.month, date.day, 0,0,0) time_end= datetime(date.year,date.month, date.day, 23,59,59) measurement_list = LldevicePdlagMeasure.objects.filter( timestamp__gt=time_start, timestamp__lte=time_end, lldevicelist=id).order_by('timestamp') measurement_list_greater_count = LldevicePdlagMeasure.objects.filter( timestamp__gt=time_end, lldevicelist=id).count() try: first=measurement_list.first() last=measurement_list.last() except Exception as e: continue if first is not None and last is not None: if last.ch1_total_flow != None and first.ch1_total_flow != None: total_flow_ch1=last.ch1_total_flow-first.ch1_total_flow if last.ch2_total_flow != None and first.ch2_total_flow != None: total_flow_ch2=last.ch2_total_flow-first.ch2_total_flow if date != datetime.now().date() and measurement_list_greater_count>0: new_totalizer_data = LldevicePdlagTotalizers(date=date, ch1_total_flow=total_flow_ch1, ch2_total_flow=total_flow_ch2, lldevicelist=device) new_totalizer_data.save() data_array.append([date, round(total_flow_ch1*total_flow_factor, 3), round(total_flow_ch2*total_flow_factor, 3)]) data_response[id] = { 'data': data_array, 'unit': total_flow_unit } return JsonResponse(data_response) else: return_data =json.loads(request.GET) else: return HttpResponse('Invalid user') def totalizers_return_measurements(device_id, start_date, end_date): current_datetime = datetime.now().date() truncate_date = connection.ops.date_trunc_sql('day', 'timestamp') delta_time= current_datetime - start_date max_time =time.max min_max_list_archive = {} if delta_time > timedelta(days=34): qs_a = LldevicePdlagMeasureArchive.objects.extra({'date':truncate_date}) min_max_list_archive = qs_a\ .filter( timestamp__gt=start_date, timestamp__lte=datetime.combine(end_date, max_time),lldevicelist=device_id)\ .values('date', 'lldevicelist')\ .annotate(TotalCH1Max=Max('ch1_total_flow'),TotalCH1Min=Min('ch1_total_flow'),TotalCH2Max=Max('ch2_total_flow'),TotalCH2Min=Min('ch2_total_flow'))\ .order_by('timestamp') qs = LldevicePdlagMeasure.objects.extra({'date': truncate_date}) min_max_list = qs \ .filter(timestamp__gt=start_date, timestamp__lte=datetime.combine(end_date, max_time), lldevicelist=device_id) \ .values('date', 'lldevicelist') \ .annotate(TotalCH1Max=Max('ch1_total_flow'), TotalCH1Min=Min('ch1_total_flow'), TotalCH2Max=Max('ch2_total_flow'), TotalCH2Min=Min('ch2_total_flow')) \ .order_by('timestamp') return list(chain(min_max_list_archive, min_max_list)) def totalizers_return_data_fast(request): if request.user.is_authenticated(): if request.is_ajax(): if request.method == 'POST': data_response = {} return_data =json.loads(request.body) data_start_date = datetime.strptime(return_data['start_date'], "%Y-%m-%d").date() data_end_date = datetime.strptime(return_data['end_date'], "%Y-%m-%d").date() data_id_list = return_data['device_id_list'] for id in data_id_list: device = devices_return_by_id(id) total_flow_unit = device.get_total_flow_recalculate_unit() total_flow_factor = device.get_total_flow_factor() data_array = [] min_max_data= totalizers_return_measurements(id, data_start_date,data_end_date) for min_max in min_max_data: date_value = min_max['date'] max_value_ch1 = totalizers_check_is_none(min_max['TotalCH1Max']) min_value_ch1 = totalizers_check_is_none(min_max['TotalCH1Min']) max_value_ch2 = totalizers_check_is_none(min_max['TotalCH2Max']) min_value_ch2 = totalizers_check_is_none(min_max['TotalCH2Min']) delta_ch1 = max_value_ch1-min_value_ch1 delta_ch2 = max_value_ch2-min_value_ch2 data_array.append([date_value, round(delta_ch1*total_flow_factor, 3), round(delta_ch2*total_flow_factor, 3)]) data_response[id] = { 'data': data_array, 'unit': total_flow_unit } return JsonResponse(data_response) else: return_data =json.loads(request.GET) else: return HttpResponse('Invalid user') def totalizers_return_excel(request): if request.user.is_authenticated(): if request.method == 'POST': data_response = {} return_data =json.loads(request.POST['json']) data_start_date = datetime.strptime(return_data['start_date'], "%Y-%m-%d").date() data_end_date = datetime.strptime(return_data['end_date'], "%Y-%m-%d").date() device_list = return_data['device_list'] delta = data_end_date - data_start_date date_list=[] for i in range(delta.days + 1): date_list.append(data_start_date + timedelta(days=i)) workbook = xlwt.Workbook() align = xlwt.Alignment() align.wrap = True align.vert = align.VERT_CENTER align.horz = align.HORZ_CENTER align_v = xlwt.Alignment() align_v.wrap = True align_v.vert = align.VERT_CENTER align_v.horz = align.HORZ_LEFT # 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 style_white_bold = xlwt.XFStyle() style_white_bold.borders = borders style_white_bold.alignment = align_v style_white_bold.font = font_bold sheet = workbook.add_sheet("Data") sheet.row(0).height_mismatch = True sheet.row(0).height = 256 * 3 sheet.row(1).height_mismatch = True sheet.row(1).height = 256 * 2 column = sheet.col(0) column.width = 500 * 30 column = sheet.col(1) column.width = 190 * 30 column_counter=1 if request.session.get('lang', 'en-gb') == 'hr': sheet.write(1, 0, 'Uredaj [Identifikator]', style_blue) sheet.write(1, 1, 'Mjerenje', style_blue) else: sheet.write(1, 0, 'Device [Identifier]', style_blue) sheet.write(1, 1, 'Measure', style_blue) for date in date_list: column_counter+=1 column = sheet.col(column_counter) column.width = 150 * 30 sheet.write(1, column_counter, date.strftime('%d-%m-%Y'), style_blue) if request.session.get('lang', 'en-gb') == 'hr': sheet.write_merge(0, 0, 0, column_counter,'Datum od: ' + date_list[0].strftime('%d-%m-%Y') + ' do ' + date_list[-1].strftime('%d-%m-%Y'),style_yellow) else: sheet.write_merge(0, 0, 0, column_counter,'Date from: ' + date_list[0].strftime('%d-%m-%Y') + ' to ' + date_list[-1].strftime('%d-%m-%Y'),style_yellow) row_counter=2 for device in device_list: device_id = device['device_id'] device_identifier = device['device_identifier'] total_flow_unit = device.get_total_flow_recalculate_unit() total_flow_factor = device.get_total_flow_factor() if device['total_flow_1'] == True and device['total_flow_2'] == True: sheet.write_merge(row_counter,row_counter+1, 0, 0, device_id + ' ['+device_identifier+']',style_white_bold) if request.session.get('lang', 'en-gb') == 'hr': sheet.write(row_counter,1,'Uk. protok CH1'+' ['+total_flow_unit+']',style_white_bold) sheet.write(row_counter+1,1,'Uk. protok CH2'+' ['+total_flow_unit+']',style_white_bold) else: sheet.write(row_counter,1,'Total flow CH1'+' ['+total_flow_unit+']',style_white_bold) sheet.write(row_counter+1,1,'Total flow CH2'+' ['+total_flow_unit+']',style_white_bold) elif device['total_flow_1'] == True: sheet.write(row_counter,0,device_id + ' ['+device_identifier+']',style_white_bold) if request.session.get('lang', 'en-gb') == 'hr': sheet.write(row_counter,1,'Uk. protok CH1'+' ['+total_flow_unit+']',style_white_bold) else: sheet.write(row_counter,1,'Total flow CH1'+' ['+total_flow_unit+']',style_white_bold) elif device['total_flow_2'] == True: sheet.write(row_counter,0,device_id + ' ['+device_identifier+']',style_white_bold) if request.session.get('lang', 'en-gb') == 'hr': sheet.write(row_counter,1,'Uk. protok CH2'+' ['+total_flow_unit+']',style_white_bold) else: sheet.write(row_counter,1,'Total flow CH2'+' ['+total_flow_unit+']',style_white_bold) totalizer_list = totalizers_return_measurements(device_id, data_start_date, data_end_date) column_counter = 2 for total in totalizer_list: max_value_ch1 = totalizers_check_is_none(total['TotalCH1Max']) min_value_ch1 = totalizers_check_is_none(total['TotalCH1Min']) max_value_ch2 = totalizers_check_is_none(total['TotalCH2Max']) min_value_ch2 = totalizers_check_is_none(total['TotalCH2Min']) total_flow_ch1 = max_value_ch1-min_value_ch1 total_flow_ch2 = max_value_ch2-min_value_ch2 if device['total_flow_1'] == True and device['total_flow_2'] == True: sheet.write(row_counter, column_counter, str(total_flow_ch1*total_flow_factor),style_white) sheet.write(row_counter + 1, column_counter, str(total_flow_ch2*total_flow_factor),style_white) elif device['total_flow_1'] == True: sheet.write(row_counter, column_counter, str(total_flow_ch1*total_flow_factor),style_white) elif device['total_flow_2'] == True: sheet.write(row_counter, column_counter, str(total_flow_ch2*total_flow_factor),style_white) column_counter+=1 if device['total_flow_1'] == True and device['total_flow_2'] == True: row_counter+=2 else: row_counter+=1 download_name = datetime.now().strftime("%Y-%m-%d_%H-%M-%S") + '_totalizers.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 totalizers_check_is_none(value): if value is None: value=0 return value