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"],
},
)
