def analyticsPinsets(request, pinset_id, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id = 1) Pin = get_object_or_404(Pinset, id = pinset_id) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate(initial= {'start_date': datetime.date(year = today.year, month=today.month , day=1), 'end_date': datetime.date(year = today.year, month=today.month , day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days = 1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) start_date_month = start_date end_date_month = end_date if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month , day=1)- timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() sql = "SELECT tarifica_call.id,\ SUM(tarifica_call.cost) AS cost,\ tarifica_call.dialed_number,\ SUM(tarifica_call.duration) AS duration, \ COUNT(dialed_number) AS times_dialed\ FROM tarifica_call\ WHERE date > %s AND date < %s AND pinset_number = %s \ GROUP BY dialed_number ORDER BY SUM(cost) DESC" cursor.execute(sql,[start_date,end_date, Pin.pinset_number]) top_calls = dictfetchall(cursor)[:10] data = [] for n in top_calls : data.append([n['dialed_number'], n['cost']]) cursor.execute('SELECT tarifica_call.id, tarifica_call.cost, tarifica_call.dialed_number, tarifica_call.duration, \ tarifica_destinationname.name, tarifica_destinationgroup.destination_country AS country, tarifica_call.date AS dat,\ tarifica_call.date AS time FROM tarifica_call LEFT JOIN tarifica_destinationgroup\ ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date > %s AND date < %s AND pinset_number = %s ORDER BY dat', [start_date,end_date, Pin.pinset_number]) all_calls = dictfetchall(cursor) for cost in all_calls: cost['dat'] = cost['dat'].strftime('%d %B %Y') cost['time'] = cost['time'].strftime('%H:%M:%S') year_data = getBarChartInfoByPinForYear(cursor, Pin.id) return render(request, 'tarifica/pinsets/analyticsPinsets.html', { 'user_info' : user_info, 'all_calls' : all_calls, 'top_calls' : top_calls, 'last_month' : last_month, 'custom' : custom, 'form': form, 'pinset' : Pin, 'data' : json.dumps(data), 'year_data' : json.dumps(year_data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def detailPinsets(request, pinset_id, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id = 1) Pin = get_object_or_404(Pinset, id = pinset_id) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate(initial= {'start_date': datetime.date(year = today.year, month=today.month , day=1), 'end_date': datetime.date(year = today.year, month=today.month , day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days = 1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month , day=1)- timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() cursor.execute('SELECT tarifica_pinsetdestinationdetail.id, SUM(tarifica_pinsetdestinationdetail.cost) AS cost,\ tarifica_destinationgroup.id AS destid, tarifica_destinationname.name AS destname, \ tarifica_destinationgroup.destination_country \ FROM tarifica_pinsetdestinationdetail LEFT JOIN tarifica_destinationgroup \ ON tarifica_pinsetdestinationdetail.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date >= %s AND date <= %s AND pinset_id = %s GROUP BY destination_group_id \ ORDER BY cost DESC', [start_date,end_date, Pin.id]) destinations = dictfetchall(cursor) for d in destinations: d['destination_country'] = Country(d['destination_country']) #print destinations cursor.execute('SELECT tarifica_call.id, tarifica_call.cost, tarifica_call.dialed_number, tarifica_call.duration,\ tarifica_destinationname.name, tarifica_destinationgroup.destination_country AS country, tarifica_call.date AS dat,\ tarifica_call.date AS time FROM tarifica_call LEFT JOIN tarifica_destinationgroup\ ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date > %s AND date < %s AND pinset_number = %s ORDER BY dat', [start_date,end_date, Pin.pinset_number]) all_calls = dictfetchall(cursor) average = 0 n = 0 for cost in all_calls: average += cost['cost'] n += 1 cost['dat'] = cost['dat'].strftime('%d %B %Y') cost['time'] = cost['time'].strftime('%H:%M:%S') if n: average = average/n data = getBarChartInfoByLocale(cursor, Pin.id) day_data = getBarChartInfoByPinForMonth(cursor, Pin.id, start_date, end_date) return render(request, 'tarifica/pinsets/detailPinsets.html', { 'user_info' : user_info, 'destinations' : destinations, 'all_calls' : all_calls, 'average' : average, 'last_month' : last_month, 'custom' : custom, 'form': form, 'pinset' : Pin, 'data' : json.dumps(data), 'day_data' : json.dumps(day_data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def generalPinsets(request, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id = 1) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate(initial= {'start_date': datetime.date(year = today.year, month=today.month , day=1), 'end_date': datetime.date(year = today.year, month=today.month , day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days = 1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month , day=1)- timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() cursor.execute( 'SELECT tarifica_pinsetdailydetail.id, SUM(tarifica_pinsetdailydetail.cost) AS cost, \ tarifica_pinset.pinset_number, tarifica_pinsetdailydetail.pinset_id AS pinid \ FROM tarifica_pinsetdailydetail LEFT JOIN tarifica_pinset\ ON tarifica_pinsetdailydetail.pinset_id = tarifica_pinset.id \ WHERE date > %s AND date < %s GROUP BY pinset_id ORDER BY SUM(cost) DESC', [start_date,end_date]) pinsets = dictfetchall(cursor)[:5] #for e in pinsets : print e #cursor.execute( #'SELECT tarifica_pinsetdailydetail.id, SUM(tarifica_pinsetdailydetail.cost) AS cost, \ #SUM(tarifica_pinsetdailydetail.total_calls) AS calls , \ #SUM(tarifica_pinsetdailydetail.total_seconds) AS seconds, tarifica_pinset.pinset_number, \ #tarifica_pinsetdailydetail.pinset_id AS pinid \ #FROM tarifica_pinsetdailydetail LEFT JOIN tarifica_pinset\ #ON tarifica_pinsetdailydetail.pinset_id = tarifica_pinset.id \ #WHERE date > %s AND date < %s GROUP BY pinset_id ORDER BY SUM(cost) DESC', #[start_date,end_date]) #all_pinsets = dictfetchall(cursor) #for a in all_Pinsets: print a all_pinsets_objects = Pinset.objects.all() sql = 'SELECT SUM(tarifica_pinsetdailydetail.cost) AS cost, \ SUM(tarifica_pinsetdailydetail.total_calls) AS calls , \ SUM(tarifica_pinsetdailydetail.total_seconds) AS seconds \ FROM tarifica_pinsetdailydetail \ WHERE date > %s AND date < %s AND tarifica_pinsetdailydetail.pinset_id = %s' all_pinsets = [] for e in all_pinsets_objects: e_data = { 'pinset': e, 'cost': 0, 'calls': 0, 'seconds': 0, } cursor.execute(sql, [start_date,end_date, e.id]) data = dictfetchall(cursor)[0] if data['cost'] is None: data['cost'] = 0 if data['calls'] is None: data['calls'] = 0 if data['seconds'] is None: data['seconds'] = 0 e_data['cost'] = data['cost'] e_data['calls'] = data['calls'] e_data['seconds'] = data['seconds'] all_pinsets.append(e_data) #Sorting pinsets by cost: all_pinsets = sorted(all_pinsets, key=lambda pinset: pinset['cost'], reverse=True) average = 0 n = 0 for a in all_pinsets: average += a['cost'] n += 1 if n: average = average/n thisMonth = 'July' lastMonth = 'June' lastTwoMonths = 'May' data = getBarChartInfoByPin(cursor) return render(request, 'tarifica/pinsets/generalPinsets.html', { 'user_info' : user_info, 'pinsets' : pinsets, 'all_pinsets' : all_pinsets, 'average' : average, 'last_month' : last_month, 'custom' : custom, 'form': form, 'thisMonth': thisMonth, 'lastMonth': lastMonth, 'lastTwoMonths': lastTwoMonths, 'data' : json.dumps(data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def analyticsPinsets(request, pinset_id, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id=1) Pin = get_object_or_404(Pinset, id=pinset_id) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate( initial={ 'start_date': datetime.date(year=today.year, month=today.month, day=1), 'end_date': datetime.date(year=today.year, month=today.month, day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days=1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) start_date_month = start_date end_date_month = end_date if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month, day=1) - timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() sql = "SELECT tarifica_call.id,\ SUM(tarifica_call.cost) AS cost,\ tarifica_call.dialed_number,\ SUM(tarifica_call.duration) AS duration, \ COUNT(dialed_number) AS times_dialed\ FROM tarifica_call\ WHERE date > %s AND date < %s AND pinset_number = %s \ GROUP BY dialed_number ORDER BY SUM(cost) DESC" cursor.execute(sql, [start_date, end_date, Pin.pinset_number]) top_calls = dictfetchall(cursor)[:10] data = [] for n in top_calls: data.append([n['dialed_number'], n['cost']]) cursor.execute( 'SELECT tarifica_call.id, tarifica_call.cost, tarifica_call.dialed_number, tarifica_call.duration, \ tarifica_destinationname.name, tarifica_destinationgroup.destination_country AS country, tarifica_call.date AS dat,\ tarifica_call.date AS time FROM tarifica_call LEFT JOIN tarifica_destinationgroup\ ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date > %s AND date < %s AND pinset_number = %s ORDER BY dat', [start_date, end_date, Pin.pinset_number]) all_calls = dictfetchall(cursor) for cost in all_calls: cost['dat'] = cost['dat'].strftime('%d %B %Y') cost['time'] = cost['time'].strftime('%H:%M:%S') year_data = getBarChartInfoByPinForYear(cursor, Pin.id) return render( request, 'tarifica/pinsets/analyticsPinsets.html', { 'user_info': user_info, 'all_calls': all_calls, 'top_calls': top_calls, 'last_month': last_month, 'custom': custom, 'form': form, 'pinset': Pin, 'data': json.dumps(data), 'year_data': json.dumps(year_data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def generalPinsets(request, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id=1) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate( initial={ 'start_date': datetime.date(year=today.year, month=today.month, day=1), 'end_date': datetime.date(year=today.year, month=today.month, day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days=1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month, day=1) - timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() cursor.execute( 'SELECT tarifica_pinsetdailydetail.id, SUM(tarifica_pinsetdailydetail.cost) AS cost, \ tarifica_pinset.pinset_number, tarifica_pinsetdailydetail.pinset_id AS pinid \ FROM tarifica_pinsetdailydetail LEFT JOIN tarifica_pinset\ ON tarifica_pinsetdailydetail.pinset_id = tarifica_pinset.id \ WHERE date > %s AND date < %s GROUP BY pinset_id ORDER BY SUM(cost) DESC', [start_date, end_date]) pinsets = dictfetchall(cursor)[:5] #for e in pinsets : print e #cursor.execute( #'SELECT tarifica_pinsetdailydetail.id, SUM(tarifica_pinsetdailydetail.cost) AS cost, \ #SUM(tarifica_pinsetdailydetail.total_calls) AS calls , \ #SUM(tarifica_pinsetdailydetail.total_seconds) AS seconds, tarifica_pinset.pinset_number, \ #tarifica_pinsetdailydetail.pinset_id AS pinid \ #FROM tarifica_pinsetdailydetail LEFT JOIN tarifica_pinset\ #ON tarifica_pinsetdailydetail.pinset_id = tarifica_pinset.id \ #WHERE date > %s AND date < %s GROUP BY pinset_id ORDER BY SUM(cost) DESC', #[start_date,end_date]) #all_pinsets = dictfetchall(cursor) #for a in all_Pinsets: print a all_pinsets_objects = Pinset.objects.all() sql = 'SELECT SUM(tarifica_pinsetdailydetail.cost) AS cost, \ SUM(tarifica_pinsetdailydetail.total_calls) AS calls , \ SUM(tarifica_pinsetdailydetail.total_seconds) AS seconds \ FROM tarifica_pinsetdailydetail \ WHERE date > %s AND date < %s AND tarifica_pinsetdailydetail.pinset_id = %s' all_pinsets = [] for e in all_pinsets_objects: e_data = { 'pinset': e, 'cost': 0, 'calls': 0, 'seconds': 0, } cursor.execute(sql, [start_date, end_date, e.id]) data = dictfetchall(cursor)[0] if data['cost'] is None: data['cost'] = 0 if data['calls'] is None: data['calls'] = 0 if data['seconds'] is None: data['seconds'] = 0 e_data['cost'] = data['cost'] e_data['calls'] = data['calls'] e_data['seconds'] = data['seconds'] all_pinsets.append(e_data) #Sorting pinsets by cost: all_pinsets = sorted(all_pinsets, key=lambda pinset: pinset['cost'], reverse=True) average = 0 n = 0 for a in all_pinsets: average += a['cost'] n += 1 if n: average = average / n thisMonth = 'July' lastMonth = 'June' lastTwoMonths = 'May' data = getBarChartInfoByPin(cursor) return render( request, 'tarifica/pinsets/generalPinsets.html', { 'user_info': user_info, 'pinsets': pinsets, 'all_pinsets': all_pinsets, 'average': average, 'last_month': last_month, 'custom': custom, 'form': form, 'thisMonth': thisMonth, 'lastMonth': lastMonth, 'lastTwoMonths': lastTwoMonths, 'data': json.dumps(data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def detailPinsets(request, pinset_id, period_id="thisMonth"): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id=1) Pin = get_object_or_404(Pinset, id=pinset_id) cursor = connection.cursor() today = datetime.datetime.now() form = forms.getDate( initial={ 'start_date': datetime.date(year=today.year, month=today.month, day=1), 'end_date': datetime.date(year=today.year, month=today.month, day=today.day), }) last_month = False custom = False timedelta = datetime.timedelta(days=1) end_date = datetime.date(year=today.year, month=today.month, day=today.day) + timedelta start_date = datetime.date(year=today.year, month=today.month, day=1) if period_id == "lastMonth": t = datetime.datetime(year=today.year, month=today.month, day=1) - timedelta last_month = True end_date = datetime.date(year=today.year, month=today.month, day=1) start_date = datetime.date(year=t.year, month=t.month, day=1) elif period_id == "custom": if request.method == 'POST': # If the form has been submitted... form = forms.getDate(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass start_date = form.cleaned_data['start_date'] end_date = form.cleaned_data['end_date'] + timedelta custom = True #print start_date.isoformat() #print end_date.isoformat() cursor.execute( 'SELECT tarifica_pinsetdestinationdetail.id, SUM(tarifica_pinsetdestinationdetail.cost) AS cost,\ tarifica_destinationgroup.id AS destid, tarifica_destinationname.name AS destname, \ tarifica_destinationgroup.destination_country \ FROM tarifica_pinsetdestinationdetail LEFT JOIN tarifica_destinationgroup \ ON tarifica_pinsetdestinationdetail.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date >= %s AND date <= %s AND pinset_id = %s GROUP BY destination_group_id \ ORDER BY cost DESC', [start_date, end_date, Pin.id]) destinations = dictfetchall(cursor) for d in destinations: d['destination_country'] = Country(d['destination_country']) #print destinations cursor.execute( 'SELECT tarifica_call.id, tarifica_call.cost, tarifica_call.dialed_number, tarifica_call.duration,\ tarifica_destinationname.name, tarifica_destinationgroup.destination_country AS country, tarifica_call.date AS dat,\ tarifica_call.date AS time FROM tarifica_call LEFT JOIN tarifica_destinationgroup\ ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \ LEFT JOIN tarifica_destinationname ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \ WHERE date > %s AND date < %s AND pinset_number = %s ORDER BY dat', [start_date, end_date, Pin.pinset_number]) all_calls = dictfetchall(cursor) average = 0 n = 0 for cost in all_calls: average += cost['cost'] n += 1 cost['dat'] = cost['dat'].strftime('%d %B %Y') cost['time'] = cost['time'].strftime('%H:%M:%S') if n: average = average / n data = getBarChartInfoByLocale(cursor, Pin.id) day_data = getBarChartInfoByPinForMonth(cursor, Pin.id, start_date, end_date) return render( request, 'tarifica/pinsets/detailPinsets.html', { 'user_info': user_info, 'destinations': destinations, 'all_calls': all_calls, 'average': average, 'last_month': last_month, 'custom': custom, 'form': form, 'pinset': Pin, 'data': json.dumps(data), 'day_data': json.dumps(day_data), 'start_date': start_date, 'end_date': end_date - datetime.timedelta(days=1), })
def general(request, page=1): #Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id=1) action = "show" calls = [] limit = 100 page = int(page) if page != 0: page = page - 1 # Query: exclude_kwargs = {} filter_kwargs = {} if request.method == 'POST': # If the form has been submitted... form = forms.filterCDR(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass #Delete all existing filters print 'Previous filters' print CDRFilter.objects.all() CDRFilter.objects.all().delete() print 'After deleting filters' print CDRFilter.objects.all() for possible_filter in form.cleaned_data: #If its the indicator of wether to show or download, ignore: if possible_filter == 'action': action = form.cleaned_data[possible_filter] continue #Checking if its a comparator if possible_filter.count('comparison') == 0: field_name_val = possible_filter extras_val = '' if possible_filter == 'start_date' or possible_filter == 'end_date': field_name_val = 'date' if possible_filter == 'start_date': extras_val = 'start' if possible_filter == 'end_date': extras_val = 'end' if possible_filter == 'provider': #Check that we don't try to filter by id 0 (not selected) if form.cleaned_data[possible_filter] == '0': print "Provider not selected" continue field_name_val = 'provider_id' if possible_filter == 'destination_group': field_name_val = 'destination_group__destination_name__name' real_filter = CDRFilter() real_filter.comparison = form.cleaned_data[possible_filter + '_comparison'] real_filter.field_name = field_name_val real_filter.value = form.cleaned_data[possible_filter] #Check start and end dates: if possible_filter == 'start_date' or possible_filter == 'end_date': if form.cleaned_data[possible_filter] is not None: real_filter.value = form.cleaned_data[ possible_filter].strftime("%Y-%m-%d") if possible_filter == 'start_date': real_filter.extras = 'start' if possible_filter == 'end_date': real_filter.extras = 'end' #Check that we only save as filters the fields that have a valid value if real_filter.value == '': continue if real_filter.value is None: continue print "Field:", real_filter.field_name print "Comparison:", real_filter.comparison print "Value:", real_filter.value real_filter.save() print "Saved new filter" else: print "Not valid!" print form.errors else: start_date_value = '' start_date_comparison_value = '' end_date_value = '' end_date_comparison_value = '' dialed_number_value = '' dialed_number_comparison_value = '' extension_number_value = '' extension_number_comparison_value = '' pinset_number_value = '' pinset_number_comparison_value = '' duration_value = '' duration_comparison_value = '' cost_value = '' cost_comparison_value = '' destination_group_value = '' destination_group_comparison_value = '' provider_value = '' provider_comparison_value = '' for f in CDRFilter.objects.all(): if f.field_name == 'date' and f.extras == 'start': start_date_value = datetime.datetime.strptime( f.value, "%Y-%m-%d") start_date_comparison_value = f.comparison if f.field_name == 'date' and f.extras == 'end': end_date_value = datetime.datetime.strptime( f.value, "%Y-%m-%d") end_date_comparison_value = f.comparison if f.field_name == 'dialed_number': dialed_number_value = f.value dialed_number_comparison_value = f.comparison if f.field_name == 'extension_number': extension_number_value = f.value extension_number_comparison_value = f.comparison if f.field_name == 'pinset_number': pinset_number_value = f.value pinset_number_comparison_value = f.comparison if f.field_name == 'duration': duration_value = f.value duration_comparison_value = f.comparison if f.field_name == 'cost': cost_value = f.value cost_comparison_value = f.comparison if f.field_name == 'destination_group__destination_name__name': destination_group_value = f.value destination_group_comparison_value = f.comparison if f.field_name == 'provider__name': provider_value = f.value provider_comparison_value = f.comparison form = forms.filterCDR( initial={ 'action': 'show', 'start_date': start_date_value, 'start_date_comparison': start_date_comparison_value, 'end_date': end_date_value, 'end_date_comparison': end_date_comparison_value, 'dialed_number': dialed_number_value, 'dialed_number_comparison': dialed_number_comparison_value, 'extension_number': extension_number_value, 'extension_number_comparison': extension_number_comparison_value, 'pinset_number': pinset_number_value, 'pinset_number_comparison': pinset_number_comparison_value, 'duration': duration_value, 'duration_comparison': duration_comparison_value, 'cost': cost_value, 'cost_comparison': cost_comparison_value, 'destination_group': destination_group_value, 'destination_group_comparison': destination_group_comparison_value, 'provider': provider_value, 'provider_comparison': provider_comparison_value, }) # An unbound form #Gettings saved filters filters = CDRFilter.objects.all() for f in filters: if f.comparison == 'exclude': exclude_kwargs.update( {'{0}__{1}'.format(f.field_name, 'exact'): f.value}) else: filter_kwargs.update( {'{0}__{1}'.format(f.field_name, f.comparison): f.value}) if action == "download": calls = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs) call_info = calls.values() for call in call_info: call['provider_id'] = Provider.objects.get( id=call['provider_id']).name call['destination_group_id'] = DestinationGroup.objects.get( id=call['destination_group_id']).destination_name.name file_path = '/tmp/' file_name = 'custom_cdr.csv' header = { 'date': 'Date', 'dialed_number': 'Dialed Number', 'extension_number': 'Extension Number', 'pinset_number': 'Pinset Number', 'provider_id': 'Provider', 'destination_group_id': 'Destination', 'duration': 'Seconds', 'cost': 'Cost', } field_names = [ 'date', 'dialed_number', 'extension_number', 'pinset_number', 'provider_id', 'destination_group_id', 'duration', 'cost', ] try: f = open(file_path + file_name, 'w+') writer = DictWriter(f, fieldnames=field_names, extrasaction='ignore') writer.writerow(header) writer.writerows(call_info) f.seek(0) except Exception as e: print "Error while writing file:", e return HttpResponseServerError('Error while saving .csv:') response = HttpResponse(f.read(), content_type='application/csv') response[ 'Content-Disposition'] = 'attachment; filename="' + file_name + '"' f.close() return response else: items = Call.objects.filter(**filter_kwargs).exclude( **exclude_kwargs).count() calls = Call.objects.filter(**filter_kwargs).exclude( **exclude_kwargs)[page * limit:page * limit + limit] total_seconds = Call.objects.filter(**filter_kwargs).exclude( **exclude_kwargs).aggregate(Sum('duration')) total_cost = Call.objects.filter(**filter_kwargs).exclude( **exclude_kwargs).aggregate(Sum('cost')) pages_number = int(ceil(items / limit)) previousPage = page - 1 if previousPage < 1: previousPage = 1 nextPage = page + 1 if nextPage > pages_number: nextPage = pages_number if total_seconds['duration__sum'] is None: total_seconds['duration__sum'] = 0 if total_cost['cost__sum'] is None: total_cost['cost__sum'] = 0 return render( request, 'tarifica/general/cdr.html', { 'user_info': user_info, 'calls': calls, 'form': form, 'items': items, 'page': page + 1, 'pages': range(1, pages_number + 1)[:10], 'limit': limit, 'previousPage': previousPage, 'nextPage': nextPage, 'pages_number': pages_number, 'total_minutes': total_seconds['duration__sum'] / 60, 'total_cost': total_cost['cost__sum'], })
def general(request, page=1): # Syncing extensions, pinsets and trunks syncAsteriskInformation() user_info = get_object_or_404(UserInformation, id=1) action = "show" calls = [] limit = 100 page = int(page) if page != 0: page = page - 1 # Query: exclude_kwargs = {} filter_kwargs = {} if request.method == "POST": # If the form has been submitted... form = forms.filterCDR(request.POST) # A form bound to the POST data if form.is_valid(): # All validation rules pass # Delete all existing filters print "Previous filters" print CDRFilter.objects.all() CDRFilter.objects.all().delete() print "After deleting filters" print CDRFilter.objects.all() for possible_filter in form.cleaned_data: # If its the indicator of wether to show or download, ignore: if possible_filter == "action": action = form.cleaned_data[possible_filter] continue # Checking if its a comparator if possible_filter.count("comparison") == 0: field_name_val = possible_filter extras_val = "" if possible_filter == "start_date" or possible_filter == "end_date": field_name_val = "date" if possible_filter == "start_date": extras_val = "start" if possible_filter == "end_date": extras_val = "end" if possible_filter == "provider": # Check that we don't try to filter by id 0 (not selected) if form.cleaned_data[possible_filter] == "0": print "Provider not selected" continue field_name_val = "provider_id" if possible_filter == "destination_group": field_name_val = "destination_group__destination_name__name" real_filter = CDRFilter() real_filter.comparison = form.cleaned_data[possible_filter + "_comparison"] real_filter.field_name = field_name_val real_filter.value = form.cleaned_data[possible_filter] # Check start and end dates: if possible_filter == "start_date" or possible_filter == "end_date": if form.cleaned_data[possible_filter] is not None: real_filter.value = form.cleaned_data[possible_filter].strftime("%Y-%m-%d") if possible_filter == "start_date": real_filter.extras = "start" if possible_filter == "end_date": real_filter.extras = "end" # Check that we only save as filters the fields that have a valid value if real_filter.value == "": continue if real_filter.value is None: continue print "Field:", real_filter.field_name print "Comparison:", real_filter.comparison print "Value:", real_filter.value real_filter.save() print "Saved new filter" else: print "Not valid!" print form.errors else: start_date_value = "" start_date_comparison_value = "" end_date_value = "" end_date_comparison_value = "" dialed_number_value = "" dialed_number_comparison_value = "" extension_number_value = "" extension_number_comparison_value = "" pinset_number_value = "" pinset_number_comparison_value = "" duration_value = "" duration_comparison_value = "" cost_value = "" cost_comparison_value = "" destination_group_value = "" destination_group_comparison_value = "" provider_value = "" provider_comparison_value = "" for f in CDRFilter.objects.all(): if f.field_name == "date" and f.extras == "start": start_date_value = datetime.datetime.strptime(f.value, "%Y-%m-%d") start_date_comparison_value = f.comparison if f.field_name == "date" and f.extras == "end": end_date_value = datetime.datetime.strptime(f.value, "%Y-%m-%d") end_date_comparison_value = f.comparison if f.field_name == "dialed_number": dialed_number_value = f.value dialed_number_comparison_value = f.comparison if f.field_name == "extension_number": extension_number_value = f.value extension_number_comparison_value = f.comparison if f.field_name == "pinset_number": pinset_number_value = f.value pinset_number_comparison_value = f.comparison if f.field_name == "duration": duration_value = f.value duration_comparison_value = f.comparison if f.field_name == "cost": cost_value = f.value cost_comparison_value = f.comparison if f.field_name == "destination_group__destination_name__name": destination_group_value = f.value destination_group_comparison_value = f.comparison if f.field_name == "provider__name": provider_value = f.value provider_comparison_value = f.comparison form = forms.filterCDR( initial={ "action": "show", "start_date": start_date_value, "start_date_comparison": start_date_comparison_value, "end_date": end_date_value, "end_date_comparison": end_date_comparison_value, "dialed_number": dialed_number_value, "dialed_number_comparison": dialed_number_comparison_value, "extension_number": extension_number_value, "extension_number_comparison": extension_number_comparison_value, "pinset_number": pinset_number_value, "pinset_number_comparison": pinset_number_comparison_value, "duration": duration_value, "duration_comparison": duration_comparison_value, "cost": cost_value, "cost_comparison": cost_comparison_value, "destination_group": destination_group_value, "destination_group_comparison": destination_group_comparison_value, "provider": provider_value, "provider_comparison": provider_comparison_value, } ) # An unbound form # Gettings saved filters filters = CDRFilter.objects.all() for f in filters: if f.comparison == "exclude": exclude_kwargs.update({"{0}__{1}".format(f.field_name, "exact"): f.value}) else: filter_kwargs.update({"{0}__{1}".format(f.field_name, f.comparison): f.value}) if action == "download": calls = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs) call_info = calls.values() for call in call_info: call["provider_id"] = Provider.objects.get(id=call["provider_id"]).name call["destination_group_id"] = DestinationGroup.objects.get( id=call["destination_group_id"] ).destination_name.name file_path = "/tmp/" file_name = "custom_cdr.csv" header = { "date": "Date", "dialed_number": "Dialed Number", "extension_number": "Extension Number", "pinset_number": "Pinset Number", "provider_id": "Provider", "destination_group_id": "Destination", "duration": "Seconds", "cost": "Cost", } field_names = [ "date", "dialed_number", "extension_number", "pinset_number", "provider_id", "destination_group_id", "duration", "cost", ] try: f = open(file_path + file_name, "w+") writer = DictWriter(f, fieldnames=field_names, extrasaction="ignore") writer.writerow(header) writer.writerows(call_info) f.seek(0) except Exception as e: print "Error while writing file:", e return HttpResponseServerError("Error while saving .csv:") response = HttpResponse(f.read(), content_type="application/csv") response["Content-Disposition"] = 'attachment; filename="' + file_name + '"' f.close() return response else: items = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs).count() calls = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs)[page * limit : page * limit + limit] total_seconds = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs).aggregate(Sum("duration")) total_cost = Call.objects.filter(**filter_kwargs).exclude(**exclude_kwargs).aggregate(Sum("cost")) pages_number = int(ceil(items / limit)) previousPage = page - 1 if previousPage < 1: previousPage = 1 nextPage = page + 1 if nextPage > pages_number: nextPage = pages_number if total_seconds["duration__sum"] is None: total_seconds["duration__sum"] = 0 if total_cost["cost__sum"] is None: total_cost["cost__sum"] = 0 return render( request, "tarifica/general/cdr.html", { "user_info": user_info, "calls": calls, "form": form, "items": items, "page": page + 1, "pages": range(1, pages_number + 1)[:10], "limit": limit, "previousPage": previousPage, "nextPage": nextPage, "pages_number": pages_number, "total_minutes": total_seconds["duration__sum"] / 60, "total_cost": total_cost["cost__sum"], }, )