def sum_in_date(x='date', y='net_sales', filter_dict=None, model='WikiItem', app=DEFAULT_APP, sort=True, limit=100000): """ Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts. >>> from django.db import connection >>> connection.close() >>> x, y = sum_in_date(y='net_sales', filter_dict={'model__startswith': 'LC60'}, model='WikiItem', limit=5, sort=1) >>> len(x) == len(y) == 5 True >>> y[1] >= y[0] True """ sort = sort_prefix(sort) model = get_model(model, app) filter_dict = filter_dict or {} objects = model.objects.filter(**filter_dict) # only the x values are now in the queryset (datetime information) objects = objects.values(x) objects = objects.annotate(y=models.Sum(y)) if sort is not None: # FIXME: this duplicates the dict of lists sort below objects = objects.order_by(sort + 'y') objects = objects.all() if limit: objects = objects[:int(limit)] objects = util.sod_transposed(objects) if sort is not None: objects = sorted_dict_of_lists(objects, field_names=['y', x], reverse=bool(sort=='-')) if not x in objects or not 'y' in objects: return [], [] else: return objects[x], objects['y']
def count_in_category(x='call_type', filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=True, limit=1000): """ Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts. >>> x, y = count_in_category(x='call_type', filter_dict={'model__startswith': 'LC60'}, limit=5, sort=1) >>> len(x) == len(y) == 5 True >>> y[1] >= y[0] True """ sort = sort_prefix(sort) model = get_model(model, app) filter_dict = filter_dict or {} x = fuzzy.extractOne(str(x), model._meta.get_all_field_names())[0] objects = model.objects.filter(**filter_dict) objects = objects.values(x) objects = objects.annotate(y=models.Count(x)) if sort is not None: objects = objects.order_by(sort + 'y') objects = objects.all() if limit: objects = objects[:int(limit)] objects = normalize_choices(util.sod_transposed(objects), field_name=x, app=app, human_readable=True) if not objects: return None objects = consolidated_counts(objects, field_name=x, count_name='y') if sort is not None: objects = sorted_dict_of_lists(objects, field_names=['y', x], reverse=bool(sort)) return objects[x], objects['y']
def count_in_date(x='date_time', filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=True, limit=100000): """ Count the number of records for each discrete (categorical) value of a field and return a dict of two lists, the field values and the counts. >>> from django.db import connection >>> connection.close() >>> x, y = count_in_date(x='date', filter_dict={'model__icontains': 'LC5'}, limit=5, sort=1) >>> len(x) == len(y) == 5 True >>> y[1] >= y[0] True """ sort = sort_prefix(sort) model = get_model(model, app) filter_dict = filter_dict or {} x = fuzzy.extractOne(str(x), model._meta.get_all_field_names())[0] objects = model.objects.filter(**filter_dict) objects = objects.extra({'date_bin_for_counting': 'date(%s)' % x}) objects = objects.values('date_bin_for_counting') objects = objects.annotate(count_of_records_per_date_bin=models.Count('pk')) # FIXME: this duplicates the dict of lists sort below if sort is not None: objects = objects.order_by(sort + 'date_bin_for_counting') objects = objects.all() if limit: objects = objects[:int(limit)] objects = util.sod_transposed(objects) if sort is not None: objects = sorted_dict_of_lists(objects, field_names=['count_of_records_per_date_bin', 'date_bin_for_counting'], reverse=bool(sort)) #logger.info(x) return objects['date_bin_for_counting'], objects['count_of_records_per_date_bin']
def sequence_from_filter_spec(field_names, filter_dict=None, model=DEFAULT_MODEL, app=DEFAULT_APP, sort=None, limit=5000): field_names = listify(field_names) # TODO: enable +1 to mean increasing order on 1st column sort_char = sort_prefix(sort) model = get_model(model, app) filter_dict = filter_dict or {} objects = model.objects.filter(**filter_dict) # only the x values are now in the queryset (datetime information) objects = objects.values(*field_names) if sort is not None: # FIXME: this duplicates the dict of lists sort below objects = objects.order_by(sort_char + field_names[-1]) objects = objects.all() if limit: objects = objects[:int(limit)] objects = util.sod_transposed(objects) if sort is not None: if len(field_names) > 1: objects = sorted_dict_of_lists(objects, field_names=[field_names[-1]] + field_names[:-1], reverse=bool(sort_prefix)) else: objects = sorted_dict_of_lists(objects, field_names=field_names, reverse=bool(sort)) return tuple(objects[fn] for fn in field_names)
def format_fields(x, y, filter_dict={'model__startswith': 'LC60'}, model=DEFAULT_MODEL, app=DEFAULT_APP, count_x=None, count_y=None, order_by=None, limit=1000, aggregate=None, sum_x=None, sum_y=None): model = get_model(model, app) if order_by in ('x', 'y', '+x', '+y', '-x', '-y', x, y, '+' + x, '+' + y, '-' + x, '-' + y): order_by += '_value' if isinstance(x, basestring): count_x = bool(count_x) or x.endswith('__count') count_y = bool(count_y)or y.endswith('__count') objects = model.objects.filter(**filter_dict) if aggregate: objects = objects.extra({'x_value': aggregate}) objects = objects.values('x_value') x = 'x_value' objects = objects.annotate(y_value=models.Count('pk')) y = 'y_value' else: if count_x: objects = objects.annotate(x_value=models.Count(x)) x = 'x_value' if count_y: objects = objects.annotate(y_value=models.Count(y)) y = 'y_value' if sum_x: objects = objects.annotate(x_value=models.Sum(x)) x = 'x_value' if sum_y: objects = objects.annotate(y_value=models.Sum(y)) y = 'y_value' objects = objects.values(x, y) if order_by: objects = objects.order_by(order_by) objects = objects.all() if limit: objects = objects[:limit] return util.sod_transposed(objects)