def progress_bar(self): """Progress bar generated based on no of contacts""" # Cache subscriber_count count_contact = Contact.objects.filter(phonebook__campaign=self.id).count() # Cache need to be set per campaign # subscriber_count_key_campaign_id_1 subscriber_count = cache.get('subscriber_count_key_campaign_id_' + str(self.id)) if subscriber_count is None: list_contact = Contact.objects.values_list('id', flat=True)\ .filter(phonebook__campaign=self.id) subscriber_count = 0 try: subscriber_count += Subscriber.objects\ .filter(contact__in=list_contact, campaign=self.id, status=SUBSCRIBER_STATUS.SENT)\ .count() except: pass cache.set("subscriber_count_key_campaign_id_%s" % str(self.id), subscriber_count, 5) subscriber_count = int(subscriber_count) count_contact = int(count_contact) if count_contact > 0: percentage_pixel = int(percentage(subscriber_count, count_contact)) else: percentage_pixel = 0 subscriber_count_string = "subscribers (" + str(subscriber_count) + ")" return "<div title='%s' style='width: 100px; border: 1px solid #ccc;'><div style='height: 4px; width: %dpx; background: #555; '></div></div>" % \ (subscriber_count_string, percentage_pixel)
def sms_dashboard(request, on_index=None): """SMS dashboard gives the following information * No of SMSCampaigns for logged in user * Total phonebook contacts * Total SMSCampaigns contacts * Amount of contact reached today * Disposition of sms via pie chart * SMS count shown on graph by days/hours **Attributes**: * ``template`` - mod_sms/sms_dashboard.html * ``form`` - SMSDashboardForm """ # All sms_campaign for logged in User sms_campaign_id_list = SMSCampaign.objects.values_list('id', flat=True).filter(user=request.user).order_by('id') # Contacts count which are active and belong to those phonebook(s) which is # associated with all sms campaign pb_active_contact_count = Contact.objects.filter( phonebook__smscampaign__in=sms_campaign_id_list, status=CONTACT_STATUS.ACTIVE).count() form = SMSDashboardForm(request.user, request.POST or None) total_record = dict() total_sms_count = 0 total_unsent = 0 total_sent = 0 total_delivered = 0 total_failed = 0 total_no_route = 0 total_unauthorized = 0 select_graph_for = 'sms count' # default search_type = SEARCH_TYPE.D_Last_24_hours # default Last 24 hours selected_sms_campaign = '' if sms_campaign_id_list: selected_sms_campaign = sms_campaign_id_list[0] # default sms campaign id # selected_sms_campaign should not be empty if selected_sms_campaign: if form.is_valid(): selected_sms_campaign = request.POST['smscampaign'] search_type = request.POST['search_type'] end_date = datetime.utcnow().replace(tzinfo=utc) start_date = calculate_date(search_type) # date_length is used to do group by starting_date if int(search_type) >= SEARCH_TYPE.B_Last_7_days: # all options except 30 days date_length = 13 if int(search_type) == SEARCH_TYPE.C_Yesterday: # yesterday now = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(now.year, now.month, now.day, 0, 0, 0, 0).replace(tzinfo=utc) \ - relativedelta(days=1) end_date = datetime(now.year, now.month, now.day, 23, 59, 59, 999999).replace(tzinfo=utc) \ - relativedelta(days=1) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: date_length = 16 else: date_length = 10 # Last 30 days option select_data = { "send_date": "SUBSTR(CAST(send_date as CHAR(30)),1," + str(date_length) + ")"} # This calls list is used by pie chart list_sms = SMSMessage.objects.filter( sender=request.user, sms_campaign_id=selected_sms_campaign, send_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('send_date', 'status')\ .annotate(Count('send_date'))\ .order_by('send_date') for i in list_sms: # convert unicode date string into date if i['status'] == 'Unsent': total_unsent += i['send_date__count'] elif i['status'] == 'Sent': total_sent += i['send_date__count'] elif i['status'] == 'Delivered': total_delivered += i['send_date__count'] elif i['status'] == 'Failed': total_failed += i['send_date__count'] elif i['status'] == 'No_Route': total_no_route += i['send_date__count'] else: total_unauthorized += i['send_date__count'] # Unauthorized total_sms_count += i['send_date__count'] list_sms = SMSMessage.objects.filter( sender=request.user, sms_campaign_id=selected_sms_campaign, send_date__range=(start_date, end_date))\ .extra(select=select_data).values('send_date')\ .annotate(Count('send_date')).order_by('send_date') mintime = start_date maxtime = end_date sms_dict = {} sms_dict_with_min = {} for data in list_sms: if int(search_type) >= SEARCH_TYPE.B_Last_7_days: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), int(data['send_date'][11:13]), 0, 0, 0).replace(tzinfo=utc) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), int(data['send_date'][11:13]), int(data['send_date'][14:16]), 0, 0).replace(tzinfo=utc) else: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), 0, 0, 0, 0).replace(tzinfo=utc) if ctime > maxtime: maxtime = ctime elif ctime < mintime: mintime = ctime # all options except 30 days if int(search_type) >= SEARCH_TYPE.B_Last_7_days: sms_dict[int(ctime.strftime("%Y%m%d%H"))] = { 'sms_count': data['send_date__count'] } sms_dict_with_min[int(ctime.strftime("%Y%m%d%H%M"))] = { 'sms_count': data['send_date__count'] } else: # Last 30 days option sms_dict[int(ctime.strftime("%Y%m%d"))] = { 'sms_count': data['send_date__count'] } dateList = date_range(mintime, maxtime, q=search_type) i = 0 total_record = {} for date in dateList: inttime = int(date.strftime("%Y%m%d")) # last 7 days | yesterday | last 24 hrs if (int(search_type) == SEARCH_TYPE.B_Last_7_days or int(search_type) == SEARCH_TYPE.C_Yesterday or int(search_type) == SEARCH_TYPE.D_Last_24_hours): for option in range(0, 24): day_time = int(str(inttime) + str(option).zfill(2)) graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(option).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if day_time in sms_dict.keys(): total_record[dt]['sms_count'] += sms_dict[day_time]['sms_count'] # last 12 hrs | last 6 hrs | last 1 hrs elif (int(search_type) == SEARCH_TYPE.E_Last_12_hours or int(search_type) == SEARCH_TYPE.F_Last_6_hours or int(search_type) == SEARCH_TYPE.G_Last_hour): for hour in range(0, 24): for minute in range(0, 60): hr_time = int(str(inttime) + str(hour).zfill(2) + str(minute).zfill(2)) graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(hour).zfill(2)), int(str(minute).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if hr_time in sms_dict_with_min.keys(): total_record[dt]['sms_count'] += sms_dict_with_min[hr_time]['sms_count'] else: # Last 30 days option graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d"))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if inttime in sms_dict.keys(): total_record[dt]['sms_count'] += sms_dict[inttime]['sms_count'] # sorting on date col total_record = total_record.items() total_record = sorted(total_record, key=lambda k: k[0]) # lineWithFocusChart final_charttype = "lineWithFocusChart" xdata = [] ydata = [] for i in total_record: xdata.append(i[0]) ydata.append(i[1]['sms_count']) tooltip_date = "%d %b %y %H:%M %p" extra_serie1 = { "tooltip": {"y_start": "", "y_end": " SMS"}, "date_format": tooltip_date } final_chartdata = { 'x': xdata, 'name1': 'SMS', 'y1': ydata, 'extra1': extra_serie1, } # Contacts which are successfully messaged for running sms campaign reached_contact = 0 if sms_campaign_id_list: now = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(now.year, now.month, now.day, 0, 0, 0, 0).replace(tzinfo=utc) end_date = datetime(now.year, now.month, now.day, 23, 59, 59, 999999).replace(tzinfo=utc) sms_campaign_subscriber = SMSCampaignSubscriber.objects.filter( sms_campaign_id__in=sms_campaign_id_list, status=SMS_SUBSCRIBER_STATUS.COMPLETE, updated_date__range=(start_date, end_date)).count() reached_contact += sms_campaign_subscriber # PieChart sms_analytic_charttype = "pieChart" xdata = [] ydata = [] sms_analytic_chartdata = {'x': xdata, 'y1': ydata} if total_sms_count != 0: for i in SMS_MESSAGE_STATUS: xdata.append(i[0].upper()) # Y-axis order depend upon SMS_MESSAGE_STATUS # 'UNSENT', 'SENT', 'DELIVERED', 'FAILED', 'NO_ROUTE', 'UNAUTHORIZED' ydata = [ percentage(total_unsent, total_sms_count), percentage(total_sent, total_sms_count), percentage(total_delivered, total_sms_count), percentage(total_failed, total_sms_count), percentage(total_no_route, total_sms_count), percentage(total_unauthorized, total_sms_count), ] color_list = [ COLOR_SMS_DISPOSITION['UNSENT'], COLOR_SMS_DISPOSITION['SENT'], COLOR_SMS_DISPOSITION['DELIVERED'], COLOR_SMS_DISPOSITION['FAILED'], COLOR_SMS_DISPOSITION['NO_ROUTE'], COLOR_SMS_DISPOSITION['UNAUTHORIZED'], ] extra_serie = { "tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list } kwargs1 = {} kwargs1['resize'] = True sms_analytic_chartdata = { 'x': xdata, 'y1': ydata, 'extra1': extra_serie, 'kwargs1': kwargs1, } data = { 'form': form, 'SEARCH_TYPE': SEARCH_TYPE, 'pb_active_contact_count': pb_active_contact_count, 'reached_contact': reached_contact, 'total_record': total_record, 'select_graph_for': select_graph_for, 'total_sms_count': total_sms_count, 'total_unsent': total_unsent, 'total_sent': total_sent, 'total_delivered': total_delivered, 'total_failed': total_failed, 'total_no_route': total_no_route, 'total_unauthorized': total_unauthorized, 'unsent_color': COLOR_SMS_DISPOSITION['UNSENT'], 'sent_color': COLOR_SMS_DISPOSITION['SENT'], 'delivered_color': COLOR_SMS_DISPOSITION['DELIVERED'], 'failed_color': COLOR_SMS_DISPOSITION['FAILED'], 'no_route_color': COLOR_SMS_DISPOSITION['NO_ROUTE'], 'unauthorized_color': COLOR_SMS_DISPOSITION['UNAUTHORIZED'], 'final_chartcontainer': 'lineplusbarwithfocuschart_container', 'final_chartdata': final_chartdata, 'final_charttype': final_charttype, 'final_extra': { 'x_is_date': True, 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': False, }, 'sms_analytic_chartcontainer': 'piechart_container', 'sms_analytic_charttype': sms_analytic_charttype, 'sms_analytic_chartdata': sms_analytic_chartdata, 'sms_analytic_extra': { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': False, }, } if on_index == 'yes': return data return render_to_response('mod_sms/sms_dashboard.html', data, context_instance=RequestContext(request))
def customer_dashboard(request, on_index=None): """Customer dashboard gives the following information * Total Campaigns contacts * Amount of contact reached today * Disposition of calls via pie chart * Call records & Duration of calls are shown on graph by days/hours **Attributes**: * ``template`` - frontend/dashboard.html * ``form`` - DashboardForm """ logging.debug('Start Dashboard') # All campaign for logged in User campaign_id_list = Campaign.objects.values_list( 'id', flat=True).filter(user=request.user).order_by('id') # Contacts count which are active and belong to those phonebook(s) which is # associated with all campaign pb_active_contact_count = Contact.objects\ .filter(phonebook__campaign__in=campaign_id_list, status=CONTACT_STATUS.ACTIVE).count() form = DashboardForm(request.user, request.POST or None) logging.debug('Got Campaign list') total_record = dict() total_duration_sum = 0 total_billsec_sum = 0 total_call_count = 0 total_answered = 0 total_not_answered = 0 total_busy = 0 total_cancel = 0 total_congestion = 0 total_failed = 0 search_type = SEARCH_TYPE.D_Last_24_hours # default Last 24 hours selected_campaign = '' if campaign_id_list: selected_campaign = campaign_id_list[0] # default campaign id # selected_campaign should not be empty if selected_campaign: if form.is_valid(): selected_campaign = request.POST['campaign'] search_type = request.POST['search_type'] end_date = datetime.utcnow().replace(tzinfo=utc) start_date = calculate_date(search_type) # date_length is used to do group by starting_date if int(search_type ) >= SEARCH_TYPE.B_Last_7_days: # all options except 30 days date_length = 13 if int(search_type) == SEARCH_TYPE.C_Yesterday: # yesterday tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0)\ .replace(tzinfo=utc) - relativedelta(days=1) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999)\ .replace(tzinfo=utc) - relativedelta(days=1) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: date_length = 16 else: date_length = 10 # Last 30 days option select_data = { "starting_date": "SUBSTR(CAST(starting_date as CHAR(30)),1,%s)" % str(date_length) } # This calls list is used by pie chart calls = VoIPCall.objects\ .filter(callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('starting_date', 'disposition')\ .annotate(Count('starting_date'))\ .order_by('starting_date') logging.debug('Aggregate VoIPCall') for i in calls: total_call_count += i['starting_date__count'] if i['disposition'] == CALL_DISPOSITION.ANSWER or i[ 'disposition'] == 'NORMAL_CLEARING': total_answered += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.BUSY or i[ 'disposition'] == 'USER_BUSY': total_busy += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.NOANSWER or i[ 'disposition'] == 'NO_ANSWER': total_not_answered += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.CANCEL or i[ 'disposition'] == 'ORIGINATOR_CANCEL': total_cancel += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.CONGESTION or i[ 'disposition'] == 'NORMAL_CIRCUIT_CONGESTION': total_congestion += i['starting_date__count'] else: # VOIP CALL FAILED total_failed += i['starting_date__count'] # following calls list is without disposition & group by call date calls = VoIPCall.objects\ .filter(callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('starting_date')\ .annotate(Sum('duration'))\ .annotate(Sum('billsec'))\ .annotate(Avg('duration'))\ .annotate(Count('starting_date'))\ .order_by('starting_date') logging.debug('Aggregate VoIPCall (2)') mintime = start_date maxtime = end_date calls_dict = {} calls_dict_with_min = {} for call in calls: total_duration_sum += call['duration__sum'] total_billsec_sum += call['billsec__sum'] if int(search_type) >= SEARCH_TYPE.B_Last_7_days: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), int(call['starting_date'][11:13]), 0, 0, 0).replace(tzinfo=utc) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), int(call['starting_date'][11:13]), int(call['starting_date'][14:16]), 0, 0).replace(tzinfo=utc) else: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), 0, 0, 0, 0).replace(tzinfo=utc) if ctime > maxtime: maxtime = ctime elif ctime < mintime: mintime = ctime # all options except 30 days if int(search_type) >= SEARCH_TYPE.B_Last_7_days: calls_dict[int(ctime.strftime("%Y%m%d%H"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } calls_dict_with_min[int(ctime.strftime("%Y%m%d%H%M"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } else: # Last 30 days option calls_dict[int(ctime.strftime("%Y%m%d"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } logging.debug('After Call Loops') dateList = date_range(mintime, maxtime, q=search_type) for date in dateList: inttime = int(date.strftime("%Y%m%d")) # last 7 days | yesterday | last 24 hrs if int(search_type) == SEARCH_TYPE.B_Last_7_days \ or int(search_type) == SEARCH_TYPE.C_Yesterday \ or int(search_type) == SEARCH_TYPE.D_Last_24_hours: for option in range(0, 24): day_time = int(str(inttime) + str(option).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(option).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0.0, } if day_time in calls_dict.keys(): total_record[dt]['call_count'] += calls_dict[day_time][ 'call_count'] total_record[dt]['duration_sum'] += calls_dict[ day_time]['duration_sum'] total_record[dt]['duration_avg'] += float( calls_dict[day_time]['duration_avg']) # last 12 hrs | last 6 hrs | last 1 hr elif (int(search_type) == SEARCH_TYPE.E_Last_12_hours or int(search_type) == SEARCH_TYPE.F_Last_6_hours or int(search_type) == SEARCH_TYPE.G_Last_hour): for hour in range(0, 24): for minute in range(0, 60): hr_time = int( str(inttime) + str(hour).zfill(2) + str(minute).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(hour).zfill(2)), int(str(minute).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0.0, } if hr_time in calls_dict_with_min.keys(): total_record[dt][ 'call_count'] += calls_dict_with_min[hr_time][ 'call_count'] total_record[dt][ 'duration_sum'] += calls_dict_with_min[ hr_time]['duration_sum'] total_record[dt]['duration_avg'] += float( calls_dict_with_min[hr_time]['duration_avg']) else: # Default: Last 30 days option graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d"))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0, } if inttime in calls_dict.keys(): total_record[dt]['call_count'] += calls_dict[inttime][ 'call_count'] total_record[dt]['duration_sum'] += calls_dict[inttime][ 'duration_sum'] total_record[dt]['duration_avg'] += float( calls_dict[inttime]['duration_avg']) logging.debug('After dateList Loops') # sorting on date col total_record = total_record.items() total_record = sorted(total_record, key=lambda k: k[0]) # lineplusbarwithfocuschart final_charttype = "linePlusBarChart" xdata = [] ydata = [] ydata2 = [] for i in total_record: xdata.append(i[0]) ydata.append(i[1]['call_count']) ydata2.append(i[1]['duration_sum']) tooltip_date = "%d %b %y %H:%M %p" kwargs1 = {} kwargs1['bar'] = True extra_serie1 = { "tooltip": { "y_start": "", "y_end": " calls" }, "date_format": tooltip_date } extra_serie2 = { "tooltip": { "y_start": "", "y_end": " sec" }, "date_format": tooltip_date } final_chartdata = { 'x': xdata, 'name1': 'Calls', 'y1': ydata, 'extra1': extra_serie1, 'kwargs1': kwargs1, 'name2': 'Duration', 'y2': ydata2, 'extra2': extra_serie2, } # Contacts which are successfully called for running campaign reached_contact = 0 if campaign_id_list: tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0).replace(tzinfo=utc) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999).replace(tzinfo=utc) reached_contact = Subscriber.objects\ .filter(campaign_id__in=campaign_id_list, # status=5, updated_date__range=(start_date, end_date))\ .count() # PieChart hangup_analytic_charttype = "pieChart" xdata = [] ydata = [] hangup_analytic_chartdata = {'x': xdata, 'y1': ydata} if total_call_count != 0: for i in CALL_DISPOSITION: xdata.append(i[0]) # Y-axis order depend upon CALL_DISPOSITION # 'ANSWER', 'BUSY', 'CANCEL', 'CONGESTION', 'FAILED', 'NOANSWER' ydata = [ percentage(total_answered, total_call_count), percentage(total_busy, total_call_count), percentage(total_cancel, total_call_count), percentage(total_congestion, total_call_count), percentage(total_failed, total_call_count), percentage(total_not_answered, total_call_count) ] color_list = [ COLOR_DISPOSITION['ANSWER'], COLOR_DISPOSITION['BUSY'], COLOR_DISPOSITION['CANCEL'], COLOR_DISPOSITION['CONGESTION'], COLOR_DISPOSITION['FAILED'], COLOR_DISPOSITION['NOANSWER'], ] extra_serie = { "tooltip": { "y_start": "", "y_end": " %" }, "color_list": color_list } hangup_analytic_chartdata = { 'x': xdata, 'y1': ydata, 'extra1': extra_serie } data = { 'form': form, 'campaign_phonebook_active_contact_count': pb_active_contact_count, 'reached_contact': reached_contact, 'total_duration_sum': total_duration_sum, 'total_billsec_sum': total_billsec_sum, 'total_call_count': total_call_count, 'total_answered': total_answered, 'total_not_answered': total_not_answered, 'total_busy': total_busy, 'total_cancel': total_cancel, 'total_congestion': total_congestion, 'total_failed': total_failed, 'answered_color': COLOR_DISPOSITION['ANSWER'], 'busy_color': COLOR_DISPOSITION['BUSY'], 'not_answered_color': COLOR_DISPOSITION['NOANSWER'], 'cancel_color': COLOR_DISPOSITION['CANCEL'], 'congestion_color': COLOR_DISPOSITION['CONGESTION'], 'failed_color': COLOR_DISPOSITION['FAILED'], 'CALL_DISPOSITION': CALL_DISPOSITION, 'hangup_analytic_chartdata': hangup_analytic_chartdata, 'hangup_analytic_charttype': hangup_analytic_charttype, 'hangup_chartcontainer': 'piechart_container', 'hangup_extra': { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': True, }, 'final_chartdata': final_chartdata, 'final_charttype': final_charttype, 'final_chartcontainer': 'lineplusbarwithfocuschart_container', 'final_extra': { 'x_is_date': True, 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': True, 'resize': True, 'focus_enable': True, } } if on_index == 'yes': return data return render_to_response('frontend/dashboard.html', data, context_instance=RequestContext(request))
def cdr_dashboard(request): """CDR dashboard on the last 24 hours **Attributes**: * ``template`` - cdr/dashboard.html * ``form`` - SwitchForm **Logic Description**: Display calls aggregated information for the last 24hours, several report will be created and displayed such as hourly call report and hangup cause/country analytics. """ logging.debug('CDR dashboard view start') form = SwitchForm(request.POST or None) if form.is_valid(): logging.debug('CDR dashboard view with search option') switch_id = int(getvar(request, 'switch_id')) else: switch_id = 0 # Get list of calls/duration for each of the last 24 hours (calls_hour_aggr, total_calls, total_duration, total_billsec, total_buy_cost, total_sell_cost) = custom_sql_matv_voip_cdr_aggr_last24hours(request.user, switch_id) # Build chart data for last 24h calls (xdata, ydata, ydata2, ydata3, ydata4, ydata5) = ([], [], [], [], [], []) for i in calls_hour_aggr: start_time = (time.mktime(calls_hour_aggr[i]['calltime'].timetuple()) * 1000) xdata.append(start_time) ydata.append(int(calls_hour_aggr[i]['nbcalls'])) ydata2.append(int(calls_hour_aggr[i]['duration']/60)) ydata3.append(int(calls_hour_aggr[i]['billsec']/60)) ydata4.append(int(calls_hour_aggr[i]['buy_cost'])) ydata5.append(int(calls_hour_aggr[i]['sell_cost'])) tooltip_date = "%d %b %y %H:%M %p" extra_serie1 = {"tooltip": {"y_start": "", "y_end": " calls"}, "date_format": tooltip_date} extra_serie2 = {"tooltip": {"y_start": "", "y_end": " min"}, "date_format": tooltip_date} extra_serie3 = {"tooltip": {"y_start": "", "y_end": " min"}, "date_format": tooltip_date} extra_serie4 = {"tooltip": {"y_start": "", "y_end": ""}, "date_format": tooltip_date} extra_serie5 = {"tooltip": {"y_start": "", "y_end": ""}, "date_format": tooltip_date} kwargs1 = {} kwargs1['bar'] = True final_chartdata = { 'x': xdata, 'name1': 'Calls', 'y1': ydata, 'extra1': extra_serie1, 'kwargs1': kwargs1, 'name2': 'Duration', 'y2': ydata2, 'extra2': extra_serie2, 'name3': 'Billsec', 'y3': ydata3, 'extra3': extra_serie3, 'name4': 'Buy cost', 'y4': ydata4, 'extra4': extra_serie4, 'name5': 'Sell cost', 'y5': ydata5, 'extra5': extra_serie5, } final_charttype = "linePlusBarChart" # Get top 5 of country calls for last 24 hours country_data = custom_sql_aggr_top_country_last24hours(request.user, switch_id, limit=5) # Build pie chart data for last 24h calls per country (xdata, ydata) = ([], []) for country in country_data: xdata.append(get_country_name(country["country_id"])) ydata.append(percentage(country["nbcalls"], total_calls)) color_list = ['#FFC36C', '#FFFF9D', '#BEEB9F', '#79BD8F', '#FFB391'] extra_serie = {"tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list} country_analytic_chartdata = {'x': xdata, 'y1': ydata, 'extra1': extra_serie} country_analytic_charttype = "pieChart" country_extra = { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': True, } # Get top 10 of hangup cause calls for last 24 hours hangup_cause_data = custom_sql_aggr_top_hangup_last24hours(request.user, switch_id) # hangup analytic pie chart data (xdata, ydata) = ([], []) for hangup_cause in hangup_cause_data: xdata.append(str(get_hangupcause_name(hangup_cause["hangup_cause_id"]))) ydata.append(str(percentage(hangup_cause["nbcalls"], total_calls))) color_list = ['#2A343F', '#7E8282', '#EA9664', '#30998F', '#449935'] extra_serie = {"tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list} hangup_analytic_chartdata = {'x': xdata, 'y1': ydata, 'extra1': extra_serie} hangup_analytic_charttype = "pieChart" hangup_extra = country_extra logging.debug("Result calls_hour_aggr %d" % len(calls_hour_aggr)) logging.debug("Result hangup_cause_data %d" % len(hangup_cause_data)) logging.debug("Result country_data %d" % len(country_data)) # Calculate the Average Time of Call metric_aggr = calculate_act_acd(total_calls, total_duration) final_extra = { 'x_is_date': True, 'x_axis_format': '%H:%M', # 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': True, 'focus_enable': True, } logging.debug('CDR dashboard view end') variables = { 'total_calls': total_calls, 'total_duration': int_convert_to_minute(total_duration), 'total_buy_cost': total_buy_cost, 'total_sell_cost': total_sell_cost, 'metric_aggr': metric_aggr, 'country_data': country_data, 'hangup_analytic': hangup_cause_data, 'form': form, 'final_chartdata': final_chartdata, 'final_charttype': final_charttype, 'final_chartcontainer': 'final_container', 'final_extra': final_extra, 'hangup_analytic_charttype': hangup_analytic_charttype, 'hangup_analytic_chartdata': hangup_analytic_chartdata, 'hangup_chartcontainer': 'hangup_piechart_container', 'hangup_extra': hangup_extra, 'country_analytic_charttype': country_analytic_charttype, 'country_analytic_chartdata': country_analytic_chartdata, 'country_chartcontainer': 'country_piechart_container', 'country_extra': country_extra, } return render_to_response('cdr/dashboard.html', variables, context_instance=RequestContext(request))
def cdr_country_report(request): """CDR country report **Attributes**: * ``template`` - cdr/country_report.html * ``form`` - CountryReportForm **Logic Description**: Retrieve call records from Postgresql for all countries and create reporting information for those countries """ metric = 'nbcalls' tday = datetime.today() switch_id = 0 hourly_charttype = "lineWithFocusChart" hourly_chartdata = {'x': []} country_id_list = [] total_metric = 0 # assign initial value in form fields form = CountryReportForm(request.POST or None, initial={'from_date': tday.strftime('%Y-%m-%d 00:00'), 'to_date': tday.strftime('%Y-%m-%d 23:55'), 'switch_id': switch_id}) start_date = trunc_date_start(tday) end_date = trunc_date_end(tday) if form.is_valid(): from_date = getvar(request, 'from_date') to_date = getvar(request, 'to_date') start_date = trunc_date_start(from_date) end_date = trunc_date_end(to_date) switch_id = getvar(request, 'switch_id') metric = getvar(request, 'metric') country_id = form.cleaned_data['country_id'] # convert list value in int country_id_list = [int(row) for row in country_id] # handle 0 (All) selection if 0 in country_id_list: country_id_list = [] # check metric is valid if metric not in ['nbcalls', 'duration', 'billsec', 'buy_cost', 'sell_cost']: metric = 'nbcalls' hourly_data = get_report_cdr_per_country(request.user, 'hour', start_date, end_date, switch_id, country_id_list) extra_serie = { "tooltip": {"y_start": "", "y_end": " " + metric}, "date_format": "%d %b %y %H:%M%p" } for country in hourly_data[metric]["columns"]: hourly_chartdata['x'] = hourly_data[metric]["x_timestamp"] country_name = get_country_name(int(country)).encode('utf-8') hourly_chartdata['name' + str(country)] = country_name.decode('ascii', 'ignore').replace("'", " ") hourly_chartdata['y' + str(country)] = hourly_data[metric]["values"][str(country)] hourly_chartdata['extra' + str(country)] = extra_serie total_calls = hourly_data["nbcalls"]["total"] total_duration = hourly_data["duration"]["total"] total_billsec = hourly_data["billsec"]["total"] total_buy_cost = hourly_data["buy_cost"]["total"] total_sell_cost = hourly_data["sell_cost"]["total"] # Calculate the Average Time of Call metric_aggr = calculate_act_acd(total_calls, total_duration) # Get top 10 of country calls top_country = 10 country_data = custom_sql_aggr_top_country(request.user, switch_id, top_country, start_date, end_date) # Build pie chart data for last 24h calls per country (xdata, ydata) = ([], []) for country in country_data: xdata.append(get_country_name(country["country_id"])) ydata.append(percentage(country["nbcalls"], total_calls)) color_list = ['#FFC36C', '#FFFF9D', '#BEEB9F', '#79BD8F', '#FFB391', '#58A6A6', '#86BF30', '#F2D022', '#D9AA1E', '#D98236'] extra_serie = {"tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list} country_analytic_chartdata = {'x': xdata, 'y1': ydata, 'extra1': extra_serie} country_analytic_charttype = "pieChart" country_extra = { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': True, } data = { 'action': 'tabs-1', 'total_metric': total_metric, 'start_date': start_date, 'end_date': end_date, 'metric': metric, 'form': form, 'NUM_COUNTRY': settings.NUM_COUNTRY, 'hourly_charttype': hourly_charttype, 'hourly_chartdata': hourly_chartdata, 'hourly_chartcontainer': 'hourly_container', 'hourly_extra': { 'x_is_date': True, 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': False, }, 'total_calls': total_calls, 'total_duration': total_duration, 'total_billsec': total_billsec, 'total_buy_cost': total_buy_cost, 'total_sell_cost': total_sell_cost, 'metric_aggr': metric_aggr, 'country_data': country_data, 'country_analytic_charttype': country_analytic_charttype, 'country_analytic_chartdata': country_analytic_chartdata, 'country_chartcontainer': 'country_piechart_container', 'country_extra': country_extra, 'top_country': top_country, } return render_to_response('cdr/country_report.html', data, context_instance=RequestContext(request))
def customer_dashboard(request, on_index=None): """Customer dashboard gives the following information * Total Campaigns contacts * Amount of contact reached today * Disposition of calls via pie chart * Call records & Duration of calls are shown on graph by days/hours **Attributes**: * ``template`` - frontend/dashboard.html * ``form`` - DashboardForm """ logging.debug("Start Dashboard") # All campaign for logged in User campaign_id_list = Campaign.objects.values_list("id", flat=True).filter(user=request.user).order_by("id") # Contacts count which are active and belong to those phonebook(s) which is # associated with all campaign pb_active_contact_count = Contact.objects.filter( phonebook__campaign__in=campaign_id_list, status=CONTACT_STATUS.ACTIVE ).count() form = DashboardForm(request.user, request.POST or None) logging.debug("Got Campaign list") total_record = dict() total_duration_sum = 0 total_call_count = 0 total_answered = 0 total_not_answered = 0 total_busy = 0 total_cancel = 0 total_congestion = 0 total_failed = 0 search_type = SEARCH_TYPE.D_Last_24_hours # default Last 24 hours selected_campaign = "" if campaign_id_list: selected_campaign = campaign_id_list[0] # default campaign id # selected_campaign should not be empty if selected_campaign: if form.is_valid(): selected_campaign = request.POST["campaign"] search_type = request.POST["search_type"] end_date = datetime.utcnow().replace(tzinfo=utc) start_date = calculate_date(search_type) # date_length is used to do group by starting_date if int(search_type) >= SEARCH_TYPE.B_Last_7_days: # all options except 30 days date_length = 13 if int(search_type) == SEARCH_TYPE.C_Yesterday: # yesterday tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0).replace(tzinfo=utc) - relativedelta( days=1 ) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999).replace( tzinfo=utc ) - relativedelta(days=1) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: date_length = 16 else: date_length = 10 # Last 30 days option select_data = {"starting_date": "SUBSTR(CAST(starting_date as CHAR(30)),1,%s)" % str(date_length)} # This calls list is used by pie chart calls = ( VoIPCall.objects.filter( callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date), ) .extra(select=select_data) .values("starting_date", "disposition") .annotate(Count("starting_date")) .order_by("starting_date") ) logging.debug("Aggregate VoIPCall") for i in calls: total_call_count += i["starting_date__count"] if i["disposition"] == VOIPCALL_DISPOSITION.ANSWER or i["disposition"] == "NORMAL_CLEARING": total_answered += i["starting_date__count"] elif i["disposition"] == VOIPCALL_DISPOSITION.BUSY or i["disposition"] == "USER_BUSY": total_busy += i["starting_date__count"] elif i["disposition"] == VOIPCALL_DISPOSITION.NOANSWER or i["disposition"] == "NO_ANSWER": total_not_answered += i["starting_date__count"] elif i["disposition"] == VOIPCALL_DISPOSITION.CANCEL or i["disposition"] == "ORIGINATOR_CANCEL": total_cancel += i["starting_date__count"] elif i["disposition"] == VOIPCALL_DISPOSITION.CONGESTION or i["disposition"] == "NORMAL_CIRCUIT_CONGESTION": total_congestion += i["starting_date__count"] else: # VOIP CALL FAILED total_failed += i["starting_date__count"] # following calls list is without disposition & group by call date calls = ( VoIPCall.objects.filter( callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date), ) .extra(select=select_data) .values("starting_date") .annotate(Sum("duration")) .annotate(Avg("duration")) .annotate(Count("starting_date")) .order_by("starting_date") ) logging.debug("Aggregate VoIPCall (2)") mintime = start_date maxtime = end_date calls_dict = {} calls_dict_with_min = {} for data in calls: if int(search_type) >= SEARCH_TYPE.B_Last_7_days: ctime = datetime( int(data["starting_date"][0:4]), int(data["starting_date"][5:7]), int(data["starting_date"][8:10]), int(data["starting_date"][11:13]), 0, 0, 0, ).replace(tzinfo=utc) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: ctime = datetime( int(data["starting_date"][0:4]), int(data["starting_date"][5:7]), int(data["starting_date"][8:10]), int(data["starting_date"][11:13]), int(data["starting_date"][14:16]), 0, 0, ).replace(tzinfo=utc) else: ctime = datetime( int(data["starting_date"][0:4]), int(data["starting_date"][5:7]), int(data["starting_date"][8:10]), 0, 0, 0, 0, ).replace(tzinfo=utc) if ctime > maxtime: maxtime = ctime elif ctime < mintime: mintime = ctime # all options except 30 days if int(search_type) >= SEARCH_TYPE.B_Last_7_days: calls_dict[int(ctime.strftime("%Y%m%d%H"))] = { "call_count": data["starting_date__count"], "duration_sum": data["duration__sum"], "duration_avg": float(data["duration__avg"]), } calls_dict_with_min[int(ctime.strftime("%Y%m%d%H%M"))] = { "call_count": data["starting_date__count"], "duration_sum": data["duration__sum"], "duration_avg": float(data["duration__avg"]), } else: # Last 30 days option calls_dict[int(ctime.strftime("%Y%m%d"))] = { "call_count": data["starting_date__count"], "duration_sum": data["duration__sum"], "duration_avg": float(data["duration__avg"]), } logging.debug("After Call Loops") dateList = date_range(mintime, maxtime, q=search_type) for date in dateList: inttime = int(date.strftime("%Y%m%d")) # last 7 days | yesterday | last 24 hrs if ( int(search_type) == SEARCH_TYPE.B_Last_7_days or int(search_type) == SEARCH_TYPE.C_Yesterday or int(search_type) == SEARCH_TYPE.D_Last_24_hours ): for option in range(0, 24): day_time = int(str(inttime) + str(option).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(option).zfill(2)), ).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {"call_count": 0, "duration_sum": 0, "duration_avg": 0.0} if day_time in calls_dict.keys(): total_record[dt]["call_count"] += calls_dict[day_time]["call_count"] total_record[dt]["duration_sum"] += calls_dict[day_time]["duration_sum"] total_record[dt]["duration_avg"] += float(calls_dict[day_time]["duration_avg"]) # last 12 hrs | last 6 hrs | last 1 hrs elif ( int(search_type) == SEARCH_TYPE.E_Last_12_hours or int(search_type) == SEARCH_TYPE.F_Last_6_hours or int(search_type) == SEARCH_TYPE.G_Last_hour ): for hour in range(0, 24): for minute in range(0, 60): hr_time = int(str(inttime) + str(hour).zfill(2) + str(minute).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(hour).zfill(2)), int(str(minute).zfill(2)), ).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {"call_count": 0, "duration_sum": 0, "duration_avg": 0.0} if hr_time in calls_dict_with_min.keys(): total_record[dt]["call_count"] += calls_dict_with_min[hr_time]["call_count"] total_record[dt]["duration_sum"] += calls_dict_with_min[hr_time]["duration_sum"] total_record[dt]["duration_avg"] += float(calls_dict_with_min[hr_time]["duration_avg"]) else: # Last 30 days option graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")) ).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {"call_count": 0, "duration_sum": 0, "duration_avg": 0} if inttime in calls_dict.keys(): total_record[dt]["call_count"] += calls_dict[inttime]["call_count"] total_record[dt]["duration_sum"] += calls_dict[inttime]["duration_sum"] total_record[dt]["duration_avg"] += float(calls_dict[inttime]["duration_avg"]) logging.debug("After dateList Loops") # sorting on date col total_record = total_record.items() total_record = sorted(total_record, key=lambda k: k[0]) # lineplusbarwithfocuschart final_charttype = "linePlusBarWithFocusChart" xdata = [] ydata = [] ydata2 = [] for i in total_record: xdata.append(i[0]) ydata.append(i[1]["call_count"]) ydata2.append(i[1]["duration_sum"]) tooltip_date = "%d %b %y %H:%M %p" kwargs1 = {} kwargs1["bar"] = True extra_serie1 = {"tooltip": {"y_start": "", "y_end": " calls"}, "date_format": tooltip_date} extra_serie2 = {"tooltip": {"y_start": "", "y_end": " sec"}, "date_format": tooltip_date} final_chartdata = { "x": xdata, "name1": "Calls", "y1": ydata, "extra1": extra_serie1, "kwargs1": kwargs1, "name2": "Duration", "y2": ydata2, "extra2": extra_serie2, } # Contacts which are successfully called for running campaign reached_contact = 0 if campaign_id_list: tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0).replace(tzinfo=utc) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999).replace(tzinfo=utc) reached_contact = Subscriber.objects.filter( campaign_id__in=campaign_id_list, updated_date__range=(start_date, end_date) # status=5, ).count() # PieChart hangup_analytic_charttype = "pieChart" xdata = [] ydata = [] hangup_analytic_chartdata = {"x": xdata, "y1": ydata} if total_call_count != 0: for i in VOIPCALL_DISPOSITION: xdata.append(i[0]) # Y-axis order depend upon VOIPCALL_DISPOSITION # 'ANSWER', 'BUSY', 'CANCEL', 'CONGESTION', 'FAILED', 'NOANSWER' ydata = [ percentage(total_answered, total_call_count), percentage(total_busy, total_call_count), percentage(total_cancel, total_call_count), percentage(total_congestion, total_call_count), percentage(total_failed, total_call_count), percentage(total_not_answered, total_call_count), ] color_list = [ COLOR_DISPOSITION["ANSWER"], COLOR_DISPOSITION["BUSY"], COLOR_DISPOSITION["CANCEL"], COLOR_DISPOSITION["CONGESTION"], COLOR_DISPOSITION["FAILED"], COLOR_DISPOSITION["NOANSWER"], ] extra_serie = {"tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list} hangup_analytic_chartdata = {"x": xdata, "y1": ydata, "extra1": extra_serie} data = { "form": form, "campaign_phonebook_active_contact_count": pb_active_contact_count, "reached_contact": reached_contact, "total_duration_sum": total_duration_sum, "total_call_count": total_call_count, "total_answered": total_answered, "total_not_answered": total_not_answered, "total_busy": total_busy, "total_cancel": total_cancel, "total_congestion": total_congestion, "total_failed": total_failed, "answered_color": COLOR_DISPOSITION["ANSWER"], "busy_color": COLOR_DISPOSITION["BUSY"], "not_answered_color": COLOR_DISPOSITION["NOANSWER"], "cancel_color": COLOR_DISPOSITION["CANCEL"], "congestion_color": COLOR_DISPOSITION["CONGESTION"], "failed_color": COLOR_DISPOSITION["FAILED"], "VOIPCALL_DISPOSITION": VOIPCALL_DISPOSITION, "hangup_analytic_chartdata": hangup_analytic_chartdata, "hangup_analytic_charttype": hangup_analytic_charttype, "hangup_chartcontainer": "piechart_container", "hangup_extra": {"x_is_date": False, "x_axis_format": "", "tag_script_js": True, "jquery_on_ready": True}, "final_chartdata": final_chartdata, "final_charttype": final_charttype, "final_chartcontainer": "lineplusbarwithfocuschart_container", "final_extra": { "x_is_date": True, "x_axis_format": "%d %b %Y", "tag_script_js": True, "jquery_on_ready": True, "resize": True, }, } if on_index == "yes": return data return render_to_response("frontend/dashboard.html", data, context_instance=RequestContext(request))
def sms_dashboard(request, on_index=None): """SMS dashboard gives the following information * No of SMSCampaigns for logged in user * Total phonebook contacts * Total SMSCampaigns contacts * Amount of contact reached today * Disposition of sms via pie chart * SMS count shown on graph by days/hours **Attributes**: * ``template`` - mod_sms/sms_dashboard.html * ``form`` - SMSDashboardForm """ # All sms_campaign for logged in User sms_campaign_id_list = SMSCampaign.objects.values_list( 'id', flat=True).filter(user=request.user).order_by('id') # Contacts count which are active and belong to those phonebook(s) which is # associated with all sms campaign pb_active_contact_count = Contact.objects.filter( phonebook__smscampaign__in=sms_campaign_id_list, status=CONTACT_STATUS.ACTIVE).count() form = SMSDashboardForm(request.user, request.POST or None) total_record = dict() total_sms_count = 0 total_unsent = 0 total_sent = 0 total_delivered = 0 total_failed = 0 total_no_route = 0 total_unauthorized = 0 select_graph_for = 'sms count' # default search_type = SEARCH_TYPE.D_Last_24_hours # default Last 24 hours selected_sms_campaign = '' if sms_campaign_id_list: selected_sms_campaign = sms_campaign_id_list[ 0] # default sms campaign id # selected_sms_campaign should not be empty if selected_sms_campaign: if form.is_valid(): selected_sms_campaign = request.POST['smscampaign'] search_type = request.POST['search_type'] end_date = datetime.utcnow().replace(tzinfo=utc) start_date = calculate_date(search_type) # date_length is used to do group by starting_date if int(search_type ) >= SEARCH_TYPE.B_Last_7_days: # all options except 30 days date_length = 13 if int(search_type) == SEARCH_TYPE.C_Yesterday: # yesterday now = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(now.year, now.month, now.day, 0, 0, 0, 0).replace(tzinfo=utc) \ - relativedelta(days=1) end_date = datetime(now.year, now.month, now.day, 23, 59, 59, 999999).replace(tzinfo=utc) \ - relativedelta(days=1) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: date_length = 16 else: date_length = 10 # Last 30 days option select_data = { "send_date": "SUBSTR(CAST(send_date as CHAR(30)),1," + str(date_length) + ")" } # This calls list is used by pie chart list_sms = SMSMessage.objects.filter( sender=request.user, sms_campaign_id=selected_sms_campaign, send_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('send_date', 'status')\ .annotate(Count('send_date'))\ .order_by('send_date') for i in list_sms: # convert unicode date string into date if i['status'] == 'Unsent': total_unsent += i['send_date__count'] elif i['status'] == 'Sent': total_sent += i['send_date__count'] elif i['status'] == 'Delivered': total_delivered += i['send_date__count'] elif i['status'] == 'Failed': total_failed += i['send_date__count'] elif i['status'] == 'No_Route': total_no_route += i['send_date__count'] else: total_unauthorized += i['send_date__count'] # Unauthorized total_sms_count += i['send_date__count'] list_sms = SMSMessage.objects.filter( sender=request.user, sms_campaign_id=selected_sms_campaign, send_date__range=(start_date, end_date))\ .extra(select=select_data).values('send_date')\ .annotate(Count('send_date')).order_by('send_date') mintime = start_date maxtime = end_date sms_dict = {} sms_dict_with_min = {} for data in list_sms: if int(search_type) >= SEARCH_TYPE.B_Last_7_days: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), int(data['send_date'][11:13]), 0, 0, 0).replace(tzinfo=utc) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), int(data['send_date'][11:13]), int(data['send_date'][14:16]), 0, 0).replace(tzinfo=utc) else: ctime = datetime(int(data['send_date'][0:4]), int(data['send_date'][5:7]), int(data['send_date'][8:10]), 0, 0, 0, 0).replace(tzinfo=utc) if ctime > maxtime: maxtime = ctime elif ctime < mintime: mintime = ctime # all options except 30 days if int(search_type) >= SEARCH_TYPE.B_Last_7_days: sms_dict[int(ctime.strftime("%Y%m%d%H"))] = { 'sms_count': data['send_date__count'] } sms_dict_with_min[int(ctime.strftime("%Y%m%d%H%M"))] = { 'sms_count': data['send_date__count'] } else: # Last 30 days option sms_dict[int(ctime.strftime("%Y%m%d"))] = { 'sms_count': data['send_date__count'] } dateList = date_range(mintime, maxtime, q=search_type) i = 0 total_record = {} for date in dateList: inttime = int(date.strftime("%Y%m%d")) # last 7 days | yesterday | last 24 hrs if (int(search_type) == SEARCH_TYPE.B_Last_7_days or int(search_type) == SEARCH_TYPE.C_Yesterday or int(search_type) == SEARCH_TYPE.D_Last_24_hours): for option in range(0, 24): day_time = int(str(inttime) + str(option).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(option).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if day_time in sms_dict.keys(): total_record[dt]['sms_count'] += sms_dict[day_time][ 'sms_count'] # last 12 hrs | last 6 hrs | last 1 hrs elif (int(search_type) == SEARCH_TYPE.E_Last_12_hours or int(search_type) == SEARCH_TYPE.F_Last_6_hours or int(search_type) == SEARCH_TYPE.G_Last_hour): for hour in range(0, 24): for minute in range(0, 60): hr_time = int( str(inttime) + str(hour).zfill(2) + str(minute).zfill(2)) graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(hour).zfill(2)), int(str(minute).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if hr_time in sms_dict_with_min.keys(): total_record[dt]['sms_count'] += sms_dict_with_min[ hr_time]['sms_count'] else: # Last 30 days option graph_day = datetime( int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d"))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = {'sms_count': 0} if inttime in sms_dict.keys(): total_record[dt]['sms_count'] += sms_dict[inttime][ 'sms_count'] # sorting on date col total_record = total_record.items() total_record = sorted(total_record, key=lambda k: k[0]) # lineWithFocusChart final_charttype = "lineWithFocusChart" xdata = [] ydata = [] for i in total_record: xdata.append(i[0]) ydata.append(i[1]['sms_count']) tooltip_date = "%d %b %y %H:%M %p" extra_serie1 = { "tooltip": { "y_start": "", "y_end": " SMS" }, "date_format": tooltip_date } final_chartdata = { 'x': xdata, 'name1': 'SMS', 'y1': ydata, 'extra1': extra_serie1, } # Contacts which are successfully messaged for running sms campaign reached_contact = 0 if sms_campaign_id_list: now = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(now.year, now.month, now.day, 0, 0, 0, 0).replace(tzinfo=utc) end_date = datetime(now.year, now.month, now.day, 23, 59, 59, 999999).replace(tzinfo=utc) sms_campaign_subscriber = SMSCampaignSubscriber.objects.filter( sms_campaign_id__in=sms_campaign_id_list, status=SMS_SUBSCRIBER_STATUS.COMPLETE, updated_date__range=(start_date, end_date)).count() reached_contact += sms_campaign_subscriber # PieChart sms_analytic_charttype = "pieChart" xdata = [] ydata = [] sms_analytic_chartdata = {'x': xdata, 'y1': ydata} if total_sms_count != 0: for i in SMS_MESSAGE_STATUS: xdata.append(i[0].upper()) # Y-axis order depend upon SMS_MESSAGE_STATUS # 'UNSENT', 'SENT', 'DELIVERED', 'FAILED', 'NO_ROUTE', 'UNAUTHORIZED' ydata = [ percentage(total_unsent, total_sms_count), percentage(total_sent, total_sms_count), percentage(total_delivered, total_sms_count), percentage(total_failed, total_sms_count), percentage(total_no_route, total_sms_count), percentage(total_unauthorized, total_sms_count), ] color_list = [ COLOR_SMS_DISPOSITION['UNSENT'], COLOR_SMS_DISPOSITION['SENT'], COLOR_SMS_DISPOSITION['DELIVERED'], COLOR_SMS_DISPOSITION['FAILED'], COLOR_SMS_DISPOSITION['NO_ROUTE'], COLOR_SMS_DISPOSITION['UNAUTHORIZED'], ] extra_serie = { "tooltip": { "y_start": "", "y_end": " %" }, "color_list": color_list } kwargs1 = {} kwargs1['resize'] = True sms_analytic_chartdata = { 'x': xdata, 'y1': ydata, 'extra1': extra_serie, 'kwargs1': kwargs1, } data = { 'form': form, 'SEARCH_TYPE': SEARCH_TYPE, 'pb_active_contact_count': pb_active_contact_count, 'reached_contact': reached_contact, 'total_record': total_record, 'select_graph_for': select_graph_for, 'total_sms_count': total_sms_count, 'total_unsent': total_unsent, 'total_sent': total_sent, 'total_delivered': total_delivered, 'total_failed': total_failed, 'total_no_route': total_no_route, 'total_unauthorized': total_unauthorized, 'unsent_color': COLOR_SMS_DISPOSITION['UNSENT'], 'sent_color': COLOR_SMS_DISPOSITION['SENT'], 'delivered_color': COLOR_SMS_DISPOSITION['DELIVERED'], 'failed_color': COLOR_SMS_DISPOSITION['FAILED'], 'no_route_color': COLOR_SMS_DISPOSITION['NO_ROUTE'], 'unauthorized_color': COLOR_SMS_DISPOSITION['UNAUTHORIZED'], 'final_chartcontainer': 'lineplusbarwithfocuschart_container', 'final_chartdata': final_chartdata, 'final_charttype': final_charttype, 'final_extra': { 'x_is_date': True, 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': False, }, 'sms_analytic_chartcontainer': 'piechart_container', 'sms_analytic_charttype': sms_analytic_charttype, 'sms_analytic_chartdata': sms_analytic_chartdata, 'sms_analytic_extra': { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': False, }, } if on_index == 'yes': return data return render_to_response('mod_sms/sms_dashboard.html', data, context_instance=RequestContext(request))
def customer_dashboard(request, on_index=None): """Customer dashboard gives the following information * Total Campaigns contacts * Amount of contact reached today * Disposition of calls via pie chart * Call records & Duration of calls are shown on graph by days/hours **Attributes**: * ``template`` - frontend/dashboard.html * ``form`` - DashboardForm """ logging.debug('Start Dashboard') # All campaign for logged in User campaign_id_list = Campaign.objects.values_list('id', flat=True).filter(user=request.user).order_by('id') # Contacts count which are active and belong to those phonebook(s) which is # associated with all campaign pb_active_contact_count = Contact.objects\ .filter(phonebook__campaign__in=campaign_id_list, status=CONTACT_STATUS.ACTIVE).count() form = DashboardForm(request.user, request.POST or None) logging.debug('Got Campaign list') total_record = dict() total_duration_sum = 0 total_call_count = 0 total_answered = 0 total_not_answered = 0 total_busy = 0 total_cancel = 0 total_congestion = 0 total_failed = 0 search_type = SEARCH_TYPE.D_Last_24_hours # default Last 24 hours selected_campaign = '' if campaign_id_list: selected_campaign = campaign_id_list[0] # default campaign id # selected_campaign should not be empty if selected_campaign: if form.is_valid(): selected_campaign = request.POST['campaign'] search_type = request.POST['search_type'] end_date = datetime.utcnow().replace(tzinfo=utc) start_date = calculate_date(search_type) # date_length is used to do group by starting_date if int(search_type) >= SEARCH_TYPE.B_Last_7_days: # all options except 30 days date_length = 13 if int(search_type) == SEARCH_TYPE.C_Yesterday: # yesterday tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0)\ .replace(tzinfo=utc) - relativedelta(days=1) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999)\ .replace(tzinfo=utc) - relativedelta(days=1) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: date_length = 16 else: date_length = 10 # Last 30 days option select_data = { "starting_date": "SUBSTR(CAST(starting_date as CHAR(30)),1,%s)" % str(date_length) } # This calls list is used by pie chart calls = VoIPCall.objects\ .filter(callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('starting_date', 'disposition')\ .annotate(Count('starting_date'))\ .order_by('starting_date') logging.debug('Aggregate VoIPCall') for i in calls: total_call_count += i['starting_date__count'] if i['disposition'] == CALL_DISPOSITION.ANSWER or i['disposition'] == 'NORMAL_CLEARING': total_answered += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.BUSY or i['disposition'] == 'USER_BUSY': total_busy += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.NOANSWER or i['disposition'] == 'NO_ANSWER': total_not_answered += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.CANCEL or i['disposition'] == 'ORIGINATOR_CANCEL': total_cancel += i['starting_date__count'] elif i['disposition'] == CALL_DISPOSITION.CONGESTION or i['disposition'] == 'NORMAL_CIRCUIT_CONGESTION': total_congestion += i['starting_date__count'] else: #VOIP CALL FAILED total_failed += i['starting_date__count'] # following calls list is without disposition & group by call date calls = VoIPCall.objects\ .filter(callrequest__campaign=selected_campaign, duration__isnull=False, user=request.user, starting_date__range=(start_date, end_date))\ .extra(select=select_data)\ .values('starting_date').annotate(Sum('duration'))\ .annotate(Avg('duration'))\ .annotate(Count('starting_date'))\ .order_by('starting_date') logging.debug('Aggregate VoIPCall (2)') mintime = start_date maxtime = end_date calls_dict = {} calls_dict_with_min = {} for call in calls: if int(search_type) >= SEARCH_TYPE.B_Last_7_days: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), int(call['starting_date'][11:13]), 0, 0, 0).replace(tzinfo=utc) if int(search_type) >= SEARCH_TYPE.E_Last_12_hours: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), int(call['starting_date'][11:13]), int(call['starting_date'][14:16]), 0, 0).replace(tzinfo=utc) else: ctime = datetime(int(call['starting_date'][0:4]), int(call['starting_date'][5:7]), int(call['starting_date'][8:10]), 0, 0, 0, 0).replace(tzinfo=utc) if ctime > maxtime: maxtime = ctime elif ctime < mintime: mintime = ctime # all options except 30 days if int(search_type) >= SEARCH_TYPE.B_Last_7_days: calls_dict[int(ctime.strftime("%Y%m%d%H"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } calls_dict_with_min[int(ctime.strftime("%Y%m%d%H%M"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } else: # Last 30 days option calls_dict[int(ctime.strftime("%Y%m%d"))] =\ { 'call_count': call['starting_date__count'], 'duration_sum': call['duration__sum'], 'duration_avg': float(call['duration__avg']), } logging.debug('After Call Loops') dateList = date_range(mintime, maxtime, q=search_type) for date in dateList: inttime = int(date.strftime("%Y%m%d")) # last 7 days | yesterday | last 24 hrs if int(search_type) == SEARCH_TYPE.B_Last_7_days \ or int(search_type) == SEARCH_TYPE.C_Yesterday \ or int(search_type) == SEARCH_TYPE.D_Last_24_hours: for option in range(0, 24): day_time = int(str(inttime) + str(option).zfill(2)) graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(option).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0.0, } if day_time in calls_dict.keys(): total_record[dt]['call_count'] += calls_dict[day_time]['call_count'] total_record[dt]['duration_sum'] += calls_dict[day_time]['duration_sum'] total_record[dt]['duration_avg'] += float(calls_dict[day_time]['duration_avg']) # last 12 hrs | last 6 hrs | last 1 hr elif (int(search_type) == SEARCH_TYPE.E_Last_12_hours or int(search_type) == SEARCH_TYPE.F_Last_6_hours or int(search_type) == SEARCH_TYPE.G_Last_hour): for hour in range(0, 24): for minute in range(0, 60): hr_time = int(str(inttime) + str(hour).zfill(2) + str(minute).zfill(2)) graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d")), int(str(hour).zfill(2)), int(str(minute).zfill(2))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0.0, } if hr_time in calls_dict_with_min.keys(): total_record[dt]['call_count'] += calls_dict_with_min[hr_time]['call_count'] total_record[dt]['duration_sum'] += calls_dict_with_min[hr_time]['duration_sum'] total_record[dt]['duration_avg'] += float(calls_dict_with_min[hr_time]['duration_avg']) else: # Default: Last 30 days option graph_day = datetime(int(date.strftime("%Y")), int(date.strftime("%m")), int(date.strftime("%d"))).replace(tzinfo=utc) dt = int(1000 * time.mktime(graph_day.timetuple())) total_record[dt] = { 'call_count': 0, 'duration_sum': 0, 'duration_avg': 0, } if inttime in calls_dict.keys(): total_record[dt]['call_count'] += calls_dict[inttime]['call_count'] total_record[dt]['duration_sum'] += calls_dict[inttime]['duration_sum'] total_record[dt]['duration_avg'] += float(calls_dict[inttime]['duration_avg']) logging.debug('After dateList Loops') # sorting on date col total_record = total_record.items() total_record = sorted(total_record, key=lambda k: k[0]) # lineplusbarwithfocuschart final_charttype = "linePlusBarWithFocusChart" xdata = [] ydata = [] ydata2 = [] for i in total_record: xdata.append(i[0]) ydata.append(i[1]['call_count']) ydata2.append(i[1]['duration_sum']) tooltip_date = "%d %b %y %H:%M %p" kwargs1 = {} kwargs1['bar'] = True extra_serie1 = {"tooltip": {"y_start": "", "y_end": " calls"}, "date_format": tooltip_date} extra_serie2 = {"tooltip": {"y_start": "", "y_end": " sec"}, "date_format": tooltip_date} final_chartdata = { 'x': xdata, 'name1': 'Calls', 'y1': ydata, 'extra1': extra_serie1, 'kwargs1': kwargs1, 'name2': 'Duration', 'y2': ydata2, 'extra2': extra_serie2, } # Contacts which are successfully called for running campaign reached_contact = 0 if campaign_id_list: tday = datetime.utcnow().replace(tzinfo=utc) start_date = datetime(tday.year, tday.month, tday.day, 0, 0, 0, 0).replace(tzinfo=utc) end_date = datetime(tday.year, tday.month, tday.day, 23, 59, 59, 999999).replace(tzinfo=utc) reached_contact = Subscriber.objects\ .filter(campaign_id__in=campaign_id_list, # status=5, updated_date__range=(start_date, end_date))\ .count() # PieChart hangup_analytic_charttype = "pieChart" xdata = [] ydata = [] hangup_analytic_chartdata = {'x': xdata, 'y1': ydata} if total_call_count != 0: for i in CALL_DISPOSITION: xdata.append(i[0]) # Y-axis order depend upon CALL_DISPOSITION # 'ANSWER', 'BUSY', 'CANCEL', 'CONGESTION', 'FAILED', 'NOANSWER' ydata = [percentage(total_answered, total_call_count), percentage(total_busy, total_call_count), percentage(total_cancel, total_call_count), percentage(total_congestion, total_call_count), percentage(total_failed, total_call_count), percentage(total_not_answered, total_call_count)] color_list = [ COLOR_DISPOSITION['ANSWER'], COLOR_DISPOSITION['BUSY'], COLOR_DISPOSITION['CANCEL'], COLOR_DISPOSITION['CONGESTION'], COLOR_DISPOSITION['FAILED'], COLOR_DISPOSITION['NOANSWER'], ] extra_serie = {"tooltip": {"y_start": "", "y_end": " %"}, "color_list": color_list} hangup_analytic_chartdata = {'x': xdata, 'y1': ydata, 'extra1': extra_serie} data = { 'form': form, 'campaign_phonebook_active_contact_count': pb_active_contact_count, 'reached_contact': reached_contact, 'total_duration_sum': total_duration_sum, 'total_call_count': total_call_count, 'total_answered': total_answered, 'total_not_answered': total_not_answered, 'total_busy': total_busy, 'total_cancel': total_cancel, 'total_congestion': total_congestion, 'total_failed': total_failed, 'answered_color': COLOR_DISPOSITION['ANSWER'], 'busy_color': COLOR_DISPOSITION['BUSY'], 'not_answered_color': COLOR_DISPOSITION['NOANSWER'], 'cancel_color': COLOR_DISPOSITION['CANCEL'], 'congestion_color': COLOR_DISPOSITION['CONGESTION'], 'failed_color': COLOR_DISPOSITION['FAILED'], 'CALL_DISPOSITION': CALL_DISPOSITION, 'hangup_analytic_chartdata': hangup_analytic_chartdata, 'hangup_analytic_charttype': hangup_analytic_charttype, 'hangup_chartcontainer': 'piechart_container', 'hangup_extra': { 'x_is_date': False, 'x_axis_format': '', 'tag_script_js': True, 'jquery_on_ready': True, }, 'final_chartdata': final_chartdata, 'final_charttype': final_charttype, 'final_chartcontainer': 'lineplusbarwithfocuschart_container', 'final_extra': { 'x_is_date': True, 'x_axis_format': '%d %b %Y', 'tag_script_js': True, 'jquery_on_ready': True, 'resize': True, } } if on_index == 'yes': return data return render_to_response('frontend/dashboard.html', data, context_instance=RequestContext(request))