def process_hourly_activity(user, start, end, modelclass):
    if modelclass == AdRecord:
        qs = modelclass.objects.filter(_user_id=user.id, date__range=(start, end))
    else:
        qs = modelclass.objects.filter(user=user, date__range=(start, end))
    if not qs:
        return {}
    qss = QuerySetStats(qs, 'date')
    start_date = start.date()
    end_date = end.date()
    if start_date == end_date:
        end_date = start_date + timedelta(days=1)
    timeseries = qss.time_series(
                        start_date, end=end_date, interval='hours', date_field='date')
    activities = {}
    for t in timeseries:
        if t[1]:
            d = t[0]
            activity = get_hourly_activity(user, start_date, d.hour)
            if modelclass == AdRecord:
                for r in qs.filter(date__hour=d.hour):
                    activity.adrecords.add(r)
            elif modelclass == FBSponsored:
                for r in qs.filter(date__hour=d.hour):
                    activity.fbsponsored.add(r)
            elif modelclass == FBAd:
                for r in qs.filter(date__hour=d.hour):
                    activity.fbads.add(r)
            activity.adtotal = activity.adrecords.count()
            activity.fbadtotal = activity.fbads.count()
            activity.fbsptotal = activity.fbsponsored.count()
            activity.save()
            activities[d] = activity    
    return activities

    def test_time_series_weeks(self):
        day = datetime.date(year=2013, month=4, day=5)

        u = User.objects.create_user('user', '[email protected]')
        u.date_joined = day
        u.save()

        qs = User.objects.all()
        qss = QuerySetStats(qs, 'date_joined')
        qss.time_series(day - datetime.timedelta(days=30), day, interval='weeks')

 def assertTimeSeriesWorks(self, today):
     seven_days_ago = today - datetime.timedelta(days=7)
     for j in range(1,8):
         for i in range(0,j):
             u = User.objects.create_user('p-%s-%s' % (j, i), 'p%s-%[email protected]' % (j, i))
             u.date_joined = today - datetime.timedelta(days=i)
             u.save()
     qs = User.objects.all()
     qss = QuerySetStats(qs, 'date_joined')
     time_series = qss.time_series(seven_days_ago, today)
     self.assertEqual([t[1] for t in time_series], [0, 1, 2, 3, 4, 5, 6, 7])

def test(request):
    """Just test page."""
    template_name = 'runstat/test.html'
    #
    start_date = datetime.strptime('2016-05-01', '%Y-%m-%d').date()
    end_date = datetime.strptime('2016-05-31', '%Y-%m-%d').date()
    qs = GroupPost.objects.all()
    qss = QuerySetStats(qs, date_field='created_time')
    values = qss.time_series(start_date, end_date, interval='days')

    return render(request, template_name, {'values': values})

    def test_basic_today(self):
        # We'll be making sure that this user is found
        u1 = User.objects.create_user('u1', '[email protected]')
        # And that this user is not
        u2 = User.objects.create_user('u2', '[email protected]')
        u2.is_active = False
        u2.save()

        # Create a QuerySet and QuerySetStats
        qs = User.objects.filter(is_active=True)
        qss = QuerySetStats(qs, 'date_joined')
        
        # We should only see a single user
        self.assertEqual(qss.this_day(), 1)

def graph(request):
    start = datetime.datetime(2013, 11, 21, 00, 00).date()
    end = datetime.datetime.now().date()
    users = User.objects.all()
    qsstats = QuerySetStats(users, date_field='last_login', aggregate=Count('id'))
    values = qsstats.time_series(start , end , interval='days' )

    staff = User.objects.all().filter(is_staff = '1').count()
    other = User.objects.all().count() - staff
    type_of_user = [['Суперпользователи',staff],['Пользователи',other]]
    start = datetime.datetime(2013, 12, 3, 00, 00).date()
    answer = Answer.objects.all()
    qsstats2 = QuerySetStats(answer, date_field='date', aggregate=Count('id'))
    ans = qsstats2.time_series(start,end, interval="days")
    return render(request,'graph.html', { 'ans':ans , 'users': values , 'type' : type_of_user })

    def get_models_data(self, querysets):
        """ querysets = {model: (queryset, time_field)} """
        for model_type, (queryset, time_field) in querysets.iteritems():
            qss = QuerySetStats(queryset, time_field)

            data = qss.time_series(self.startdate, self.enddate)

            counts = [d[1] for d in data]
            qss.last_week = sum(counts[-7:])
            qss.last_month = sum(counts[-30:])

            self.day_captions = [t[0].day for t in data]

            setattr(self, model_type+'_qss', qss)
            setattr(self, model_type+'_values', counts)

    def test_until(self):
        today = datetime.date.today()
        yesterday = today - datetime.timedelta(days=1)
        now = datetime.datetime.now()

        u = User.objects.create_user('u', '[email protected]')
        u.date_joined = today
        u.save()
        
        qs = User.objects.all()
        qss = QuerySetStats(qs, 'date_joined')

        self.assertEqual(qss.until(now), 1)
        self.assertEqual(qss.until(today), 1)
        self.assertEqual(qss.until(yesterday), 0)
        self.assertEqual(qss.until_now(), 1)

    def test_after(self):
        today = datetime.date.today()
        tomorrow = today + datetime.timedelta(days=1)
        now = datetime.datetime.now()

        u = User.objects.create_user('u', '[email protected]')
        u.date_joined = today
        u.save()
        
        qs = User.objects.all()
        qss = QuerySetStats(qs, 'date_joined')

        self.assertEqual(qss.after(today), 1)
        self.assertEqual(qss.after(now), 0)
        u.date_joined=tomorrow
        u.save()
        self.assertEqual(qss.after(now), 1)

def time_series(queryset, date_field, interval, func=None, agg=None, annotate_field=None):
    if agg in (None, "daily"):
        qsstats = QuerySetStats(queryset, date_field, func)
        return qsstats.time_series(*interval)
    else:
        # Custom aggregation was set (weekly/monthly/yearly)
        agg_by = agg[:-2]

        # We need to set the range dynamically
        interval_filter = {date_field + "__gte": interval[0], date_field + "__lte": interval[1]}

        # Slightly raw-ish SQL query using extra

        truncate_date = connections[queryset.db].ops.date_trunc_sql(agg_by, date_field)
        if not annotate_field:
            # By default we always annotate by dates, counting all the
            # records recovered for the given dates
            annotate_field = date_field
            result = (
                queryset.extra(select={agg_by: truncate_date})
                .values_list(agg_by)
                .annotate(Count(annotate_field))
                .filter(**interval_filter)
                .order_by(agg_by)
            )
        else:
            # We do a Sum by annotation field, we are assuming if the
            # annotation is not by dates it will be a field which type can be
            # added, like prices for example.
            result = (
                queryset.extra(select={agg_by: truncate_date})
                .values_list(agg_by)
                .annotate(Sum(annotate_field))
                .filter(**interval_filter)
                .order_by(agg_by)
            )

        return fill_missing_dates_with_zeros(result, agg_by, interval)

    def get_registrations(self, interval, days, graph_key, select_box_value):
        """ Returns an array with new users count per interval."""
        try:
            conf_data = DashboardStats.objects.get(graph_key=graph_key)
            model_name = get_model(conf_data.model_app_name, conf_data.model_name)
            kwargs = {}
            for i in conf_data.criteria.all():
                # fixed mapping value passed info kwargs
                if i.criteria_fix_mapping:
                    for key in i.criteria_fix_mapping:
                       # value => i.criteria_fix_mapping[key]
                        kwargs[key] = i.criteria_fix_mapping[key]

                # dynamic mapping value passed info kwargs
                if i.dynamic_criteria_field_name and select_box_value:
                    kwargs[i.dynamic_criteria_field_name] = select_box_value

            aggregate = None
            if conf_data.type_operation_field_name and conf_data.operation_field_name:
                operation={'Sum': Sum(conf_data.operation_field_name),
                        'Avg':Avg(conf_data.operation_field_name),
                        'StdDev':StdDev(conf_data.operation_field_name),
                        'Max':Max(conf_data.operation_field_name),
                        'Min':Min(conf_data.operation_field_name),
                        'Variance':Variance(conf_data.operation_field_name),
                        }
                aggregate=operation[conf_data.type_operation_field_name]

            stats = QuerySetStats(model_name.objects.filter(**kwargs),
                                      conf_data.date_field_name, aggregate)
            #stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
            today = now()
            if days == 24:
                begin = today - timedelta(hours=days - 1)
                return stats.time_series(begin, today + timedelta(hours=1), interval)

            begin = today - timedelta(days=days - 1)
            return stats.time_series(begin, today + timedelta(days=1), interval)
        except:
            User = get_user_model()
            stats = QuerySetStats(
                User.objects.filter(is_active=True), 'date_joined')
            today = now()
            if days == 24:
                begin = today - timedelta(hours=days - 1)
                return stats.time_series(begin, today + timedelta(hours=1), interval)
            begin = today - timedelta(days=days - 1)
            return stats.time_series(begin, today + timedelta(days=1), interval)

    def get_registrations(self, interval, days, graph_key, select_box_value):
        """ Returns an array with new users count per interval."""
        try:
            conf_data = DashboardStats.objects.get(graph_key=graph_key)
            model_name = get_model(
                conf_data.model_app_name, conf_data.model_name)
            kwargs = {}

            for i in conf_data.criteria.all():
                # fixed mapping value passed info kwargs
                if i.criteria_fix_mapping:
                    for key in i.criteria_fix_mapping:
                        # value => i.criteria_fix_mapping[key]
                        kwargs[key] = i.criteria_fix_mapping[key]

                # dynamic mapping value passed info kwargs
                if i.dynamic_criteria_field_name and select_box_value:
                    kwargs[i.dynamic_criteria_field_name] = select_box_value

            stats = QuerySetStats(model_name.objects.filter(**kwargs),
                                  conf_data.date_field_name)
            #stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
            today = now()
            if days == 24:
                begin = today - timedelta(hours=days - 1)
                return stats.time_series(begin, today + timedelta(hours=1), interval)

            begin = today - timedelta(days=days - 1)
            return stats.time_series(begin, today + timedelta(days=1), interval)
        except:
            stats = QuerySetStats(
                User.objects.filter(is_active=True), 'date_joined')
            today = now()
            if days == 24:
                begin = today - timedelta(hours=days - 1)
                return stats.time_series(begin, today + timedelta(hours=1), interval)
            begin = today - timedelta(days=days - 1)
            return stats.time_series(begin, today + timedelta(days=1), interval)

 def data_for_period(self, qs, start, end, interval='days'):
     """Return a time series of data for the given period."""
     qss = QuerySetStats(qs, self.datetime_field,
                         aggregate=self.aggregate)
     return qss.time_series(start, end, interval=interval)

 def get_data(self, interval, days):
     """ Returns an array with new users count per interval """
     stats = QuerySetStats(self.queryset, self.date_field)
     today = datetime.date.today()
     begin = today - datetime.timedelta(days=days-1)
     return stats.time_series(begin, today, interval)

 def get_registrations(self, interval, days):
     """ Returns an array with new users count per interval """
     stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
     today = datetime.today()
     begin = today - timedelta(days=days-1)
     return stats.time_series(begin, today+timedelta(days=1), interval)

def time_series(queryset, date_field, interval, func=None):
    qsstats = QuerySetStats(queryset, date_field, func)
    return qsstats.time_series(*interval)

 def response_timeseries(self):
     """
     Returns a list of timeseries from the responses.
     """
     qss = QuerySetStats(self.all(), "created")
     return qss.time_series(timezone.now() - timedelta(days=7), timezone.now())

 def get_data(self, interval, days):
     """ Returns an array with new users count per interval """
     stats = QuerySetStats(self.queryset, self.date_field, aggregate=self.aggregate)
     today = now()
     begin = today - timedelta(days=days - 1)
     return stats.time_series(begin, today + timedelta(days=1), interval)

def visitclick_data_json(request, *args, **kwargs):
	"""
		For default this function return a data list of week
	"""

	google_format = {'rows':[],'cols':[
			{'label':_('Time'),'type':'string','pattern':''},
			{'label':_('Click'),'type':'number','pattern':''},
	]}
	
	if datetime.now().hour == 0:
		date_init = datetime.now()-timedelta(hours=4)
	else:
		date_init = datetime.now().date()
	date_end = datetime.now()
	strftime='%H:%M'
	field='date'
	display='hours'

	if 'week' in request.GET.values() or 'week' in request.POST.values():
		date_end = get_first_dow(datetime.now().year, datetime.now().isocalendar()[1])
		date_init = date_end-timedelta(days=6)
		strftime='%d/%m'
		display='days'

	elif 'month' in request.GET.values() or 'month' in request.POST.values():
		date_init = date(date_end.year,date_end.month,1)
		strftime='%d/%m'
		display='days'

	elif 'year' in request.GET.values() or 'year' in request.POST.values():
		date_init = datetime.now() - timedelta(days=365)
		strftime='%m/%Y'
		display='months'

	elif 'custom' in request.GET.values() or 'custom' in request.POST.values():
		date_init = datetime.strptime(request.POST.get('date_init',False) or request.GET.get('date_init'),'%Y-%m-%d')
		date_end = datetime.strptime(request.POST.get('date_end',False) or request.GET.get('date_end'),'%Y-%m-%d')
		date_diff = date_end-date_init
		if (date_diff.days ==1):
			strftime='%d/%m'
			display='hours'
		elif (date_diff.days >2 and date_diff.days <=31):
			strftime='%d/%m'
			display='days'
		elif (date_diff.days > 31):
			strftime='%d/%m'
			display='months'

	elif 'clicks' in request.GET.values() or 'clicks' in request.POST.values():
		date_init = datetime.strptime(request.POST.get('date_init',False) or request.GET.get('date_init'),'%Y-%m-%d %H:%M:%S')
		date_end = request.POST.get('date_end',False) or request.GET.get('date_end',False)
		field='url'
		google_format['cols']=[
			{'label':_('Url'),'type':'string','pattern':''},
			{'label':_('Click'),'type':'number','pattern':''},
		]
		if date_end:
			date_end = datetime.strptime(date_end,'%Y-%m-%d %H:%M:%S')
		else:
			date_end =date_init+timedelta(hours=1)

	elif 'browsers' in request.GET.values() or 'browsers' in request.POST.values():
		field='browser'
		google_format['cols']=[
			{'label':_('Browsers'),'type':'string','pattern':''},
			{'label':_('Click'),'type':'number','pattern':''},
		]
	elif 'operating_system' in request.GET.values() or 'operate_system' in request.POST.values():
		field='operating_system'
		google_format['cols']=[
			{'label':_('Operating System'),'type':'string','pattern':''},
			{'label':_('Click'),'type':'number','pattern':''},
		]
	
	clicks = Click.objects.filter(
			Q(date__gte=date_init),
			Q(date__lte=date_end)
	)


	if not ('clicks' in request.GET.values() or 'clicks' in request.POST.values()) and \
	   not ('browsers' in request.GET.values() or 'browsers' in request.POST.values()) and \
	   not ('operating_system' in request.GET.values() or 'operating_system' in request.POST.values()):
		clicks = QuerySetStats(clicks, field).time_series(date_init,date_end, display,aggregate=Count(field))
	else:
		clicks= clicks.values(field).order_by(field).annotate(count=Count(field))


	if field == 'date':
		google_format['cols']=[
			{'label':_('Date'),'type':'string','pattern':''},
			{'label':_('Click'),'type':'number','pattern':''},
		]


	for click in clicks:
		if type(click) is tuple:
			value_line=click[0]
			label_line=click[0]
#.........这里部分代码省略.........

def get_scan_per_day(start_date, end_date):
    scan_qsstats = QuerySetStats(Scan.objects.all(),
                                 date_field='start',)
    scan_values = scan_qsstats.time_series(start_date, end_date, interval='days')
    return format_date(scan_values)