def reorder(_default=AFTER, _reverse=False, **kwargs): """ Return a database expression that can be used in an order_by() so that the queryset will be sorted according to the order of values given. """ if not 0 < len(kwargs) <= 1: raise TypeError("reorder() takes one non-optional keyword argument") fieldname, new_order = kwargs.popitem() if _default is BEFORE: _default = -1 elif _default is AFTER: _default = len(new_order) whens = [When(**{fieldname: v, 'then': i}) for i, v in enumerate(new_order)] casewhen = Case(*whens, default=Value(_default), output_field=IntegerField()) if _reverse: return casewhen.desc() else: return casewhen.asc()
def getAveragesByClassType(startDate=None,endDate=None): # If a date filter was passed in GET, then apply it when_all = { 'classdescription__series__eventregistration__cancelled': False, 'classdescription__series__eventregistration__dropIn': False } timeFilters = {} classFilters = {} roleFilters = Q() if startDate: timeFilters['classdescription__series__startTime__gte'] = startDate classFilters['startTime__gte'] = startDate roleFilters = roleFilters & (Q(eventrole__event__startTime__gte=startDate) | Q(eventregistration__event__startTime__gte=startDate)) if endDate: timeFilters['classdescription__series__startTime__lte'] = endDate classFilters['startTime__lte'] = endDate roleFilters = roleFilters & (Q(eventrole__event__startTime__lte=endDate) | Q(eventregistration__event__startTime__lte=endDate)) when_all.update(timeFilters) role_list = DanceRole.objects.filter(roleFilters).distinct() annotations = {'registrations': Sum(Case(When(Q(**when_all),then=1),output_field=IntegerField()))} values_list = ['name', 'danceType__name','registrations'] for this_role in role_list: annotations[this_role.pluralName] = Sum(Case(When(Q(Q(**when_all) & Q(classdescription__series__eventregistration__role=this_role)),then=1),output_field=IntegerField())) values_list.append(this_role.pluralName) registration_counts = list(DanceTypeLevel.objects.annotate(**annotations).values_list(*values_list)) class_counter = Counter([(x.classDescription.danceTypeLevel.name, x.classDescription.danceTypeLevel.danceType.name) for x in Series.objects.filter(**classFilters).distinct()]) results = {} for list_item in registration_counts: type_name = ' '.join((str(list_item[0]),str(list_item[1]))) results[type_name] = { 'registrations': list_item[2], } m = 3 for this_role in role_list: results[type_name]['total' + this_role.pluralName] = list_item[m] m += 1 for k,count in class_counter.items(): type_name = ' '.join((str(k[0]),str(k[1]))) results[type_name].update({ 'series': count }) for k,v in results.items(): if results[k].get('series'): results[k].update({ 'avgRegistrations': (results[k]['registrations'] or 0) / float(results[k]['series']), }) for this_role in role_list: results[k]['avg' + this_role.pluralName] = (results[k]['total' + this_role.pluralName] or 0) / float(results[k]['series']) return results
def section_demand_change(section_id, updated_at): """ This function should be called when a section's demand changes (i.e. the number of active registrations changes, or the section's status is updated). It updates the `PcaDemandDistributionEstimate` model and `current_demand_distribution_estimate` cache to reflect the demand change. :param: section_id: the id of the section involved in the demand change :param: updated_at: the datetime at which the demand change occurred """ section = Section.objects.get(id=section_id) semester = section.semester if semester != get_current_semester(): return with transaction.atomic(): create_new_distribution_estimate = False sentinel = object() current_demand_distribution_estimate = cache.get( "current_demand_distribution_estimate", sentinel) if (current_demand_distribution_estimate == sentinel or current_demand_distribution_estimate.semester != semester): create_new_distribution_estimate = True sections_qs = (Section.objects.filter( extra_metrics_section_filters, course__semester=semester).select_for_update().annotate( raw_demand=Case( When( Q(capacity__gt=0), then=(Cast( "registration_volume", models.FloatField(), ) / Cast("capacity", models.FloatField())), ), default=None, output_field=models.FloatField(), ), ).order_by("raw_demand")) try: lowest_demand_section = sections_qs[:1].get() highest_demand_section = sections_qs[-1:].get() except Section.DoesNotExist: return # Don't add a PcaDemandDistributionEstimate -- there are no valid sections yet if (create_new_distribution_estimate or highest_demand_section.raw_demand > current_demand_distribution_estimate.highest_raw_demand or lowest_demand_section.raw_demand < current_demand_distribution_estimate.lowest_raw_demand): closed_sections_demand_values = np.asarray( sections_qs.filter(status="C").values_list("raw_demand", flat=True)) # "The term 'closed sections positive raw demand values' is # sometimes abbreviated as 'csprdv' csrdv_frac_zero, fit_shape, fit_loc, fit_scale = (None, None, None, None) if len(closed_sections_demand_values) > 0: closed_sections_positive_demand_values = closed_sections_demand_values[ np.where(closed_sections_demand_values > 0)] csrdv_frac_zero = 1 - len( closed_sections_positive_demand_values) / len( closed_sections_demand_values) if len(closed_sections_positive_demand_values) > 0: fit_shape, fit_loc, fit_scale = stats.lognorm.fit( closed_sections_positive_demand_values) new_demand_distribution_estimate = PcaDemandDistributionEstimate( semester=semester, highest_demand_section=highest_demand_section, highest_demand_section_volume=highest_demand_section. registration_volume, lowest_demand_section=lowest_demand_section, lowest_demand_section_volume=lowest_demand_section. registration_volume, csrdv_frac_zero=csrdv_frac_zero, csprdv_lognorm_param_shape=fit_shape, csprdv_lognorm_param_loc=fit_loc, csprdv_lognorm_param_scale=fit_scale, ) add_drop_period = get_or_create_add_drop_period(semester) new_demand_distribution_estimate.save( add_drop_period=add_drop_period) new_demand_distribution_estimate.created_at = updated_at new_demand_distribution_estimate.save( add_drop_period=add_drop_period) cache.set( "current_demand_distribution_estimate", new_demand_distribution_estimate, timeout=(add_drop_period.estimated_end - add_drop_period.estimated_start).total_seconds() // ROUGH_MINIMUM_DEMAND_DISTRIBUTION_ESTIMATES, ) # set timeout to roughly follow ROUGH_MINIMUM_DEMAND_DISTRIBUTION_ESTIMATES
def get_all_tx_hashes(self, safe_address: str, executed: bool = False, queued: bool = True, trusted: bool = True) -> QuerySet: """ Build a queryset with hashes for every tx for a Safe for pagination filtering. In the case of Multisig Transactions, as some of them are not mined, we use the SafeTxHash Criteria for building this list: - Return only multisig txs with `nonce < current Safe Nonce` - The endpoint should only show incoming transactions that have been mined - The transactions should be sorted by execution date. If an outgoing transaction doesn't have an execution date the execution date of the transaction with the same nonce that has been executed should be taken. - Incoming and outgoing transfers or Eth/tokens must be under a multisig/module tx if triggered by one. Otherwise they should have their own entry in the list using a EthereumTx :param safe_address: :param executed: By default `False`, all transactions are returned. With `True`, just txs executed are returned. :param queued: By default `True`, all transactions are returned. With `False`, just txs with `nonce < current Safe Nonce` are returned. :param trusted: By default `True`, just txs that are trusted are returned (with at least one confirmation, sent by a delegate or indexed). With `False` all txs are returned :return: List with tx hashes sorted by date (newest first) """ # If tx is not mined, get the execution date of a tx mined with the same nonce case = Case(When(ethereum_tx__block=None, then=MultisigTransaction.objects.filter( safe=OuterRef('safe'), nonce=OuterRef('nonce')).exclude( ethereum_tx__block=None).values( 'ethereum_tx__block__timestamp')), default=F('ethereum_tx__block__timestamp')) multisig_safe_tx_ids = MultisigTransaction.objects.filter( safe=safe_address).annotate( execution_date=case, block=F('ethereum_tx__block_id'), safe_nonce=F('nonce'), ).values( 'safe_tx_hash', 'execution_date', 'created', 'block', 'safe_nonce' ) # Tricky, we will merge SafeTx hashes with EthereumTx hashes # Block is needed to get stable ordering if not queued: # Filter out txs with nonce >= Safe nonce last_nonce_query = MultisigTransaction.objects.filter( safe=safe_address).exclude( ethereum_tx=None).order_by('-nonce').values('nonce') multisig_safe_tx_ids = multisig_safe_tx_ids.filter( nonce__lte=Subquery(last_nonce_query[:1])) if trusted: # Just show trusted transactions multisig_safe_tx_ids = multisig_safe_tx_ids.filter(trusted=True) if executed: multisig_safe_tx_ids = multisig_safe_tx_ids.exclude( ethereum_tx__block=None) # Get module txs module_tx_ids = ModuleTransaction.objects.filter( safe=safe_address).annotate( execution_date=F('internal_tx__ethereum_tx__block__timestamp'), block=F('internal_tx__ethereum_tx__block_id'), safe_nonce=Value(0, output_field=Uint256Field()), ).distinct().values('internal_tx__ethereum_tx_id', 'execution_date', 'created', 'block', 'safe_nonce') multisig_hashes = MultisigTransaction.objects.filter( safe=safe_address).exclude( ethereum_tx=None).values('ethereum_tx_id') module_hashes = ModuleTransaction.objects.filter( safe=safe_address).values('internal_tx__ethereum_tx_id') multisig_and_module_hashes = multisig_hashes.union(module_hashes) # Get incoming/outgoing tokens not included on Multisig or Module txs. # Outgoing tokens can be triggered by another user after the Safe calls `approve`, that's why it will not # always appear as a MultisigTransaction event_tx_ids = EthereumEvent.objects.erc20_and_721_events().filter( Q(arguments__to=safe_address) | Q(arguments__from=safe_address)).exclude( ethereum_tx__in=multisig_and_module_hashes).annotate( execution_date=F('ethereum_tx__block__timestamp'), created=F('ethereum_tx__block__timestamp'), block=F('ethereum_tx__block_id'), safe_nonce=Value(0, output_field=Uint256Field()), ).distinct().values('ethereum_tx_id', 'execution_date', 'created', 'block', 'safe_nonce') # Get incoming txs not included on Multisig or Module txs internal_tx_ids = InternalTx.objects.filter( call_type=EthereumTxCallType.CALL.value, value__gt=0, to=safe_address, ).exclude(ethereum_tx__in=multisig_and_module_hashes).annotate( execution_date=F('ethereum_tx__block__timestamp'), created=F('ethereum_tx__block__timestamp'), block=F('ethereum_tx__block_id'), safe_nonce=Value(0, output_field=Uint256Field()), ).distinct().values('ethereum_tx_id', 'execution_date', 'created', 'block', 'safe_nonce') # Tricky, we merge SafeTx hashes with EthereumTx hashes queryset = multisig_safe_tx_ids.distinct().union(event_tx_ids).union( internal_tx_ids).union(module_tx_ids).order_by( '-execution_date', '-safe_nonce', 'block', '-created') # Order by block because `block_number < NULL`, so txs mined will have preference, # and `created` to get always the same ordering with not executed transactions, as they will share # the same `execution_date` that the mined tx return queryset
def product_type_counts(request): form = ProductTypeCountsForm() opened_in_period_list = [] oip = None cip = None aip = None all_current_in_pt = None top_ten = None pt = None today = timezone.now() first_of_month = today.replace(day=1, hour=0, minute=0, second=0, microsecond=0) mid_month = first_of_month.replace(day=15, hour=23, minute=59, second=59, microsecond=999999) end_of_month = mid_month.replace(day=monthrange(today.year, today.month)[1], hour=23, minute=59, second=59, microsecond=999999) start_date = first_of_month end_date = end_of_month if request.method == 'GET' and 'month' in request.GET and 'year' in request.GET and 'product_type' in request.GET: form = ProductTypeCountsForm(request.GET) if form.is_valid(): pt = form.cleaned_data['product_type'] month = int(form.cleaned_data['month']) year = int(form.cleaned_data['year']) first_of_month = first_of_month.replace(month=month, year=year) month_requested = datetime(year, month, 1) end_of_month = month_requested.replace(day=monthrange( month_requested.year, month_requested.month)[1], hour=23, minute=59, second=59, microsecond=999999) start_date = first_of_month start_date = datetime(start_date.year, start_date.month, start_date.day, tzinfo=timezone.get_current_timezone()) end_date = end_of_month end_date = datetime(end_date.year, end_date.month, end_date.day, tzinfo=timezone.get_current_timezone()) oip = opened_in_period(start_date, end_date, pt) # trending data - 12 months for x in range(12, 0, -1): opened_in_period_list.append( opened_in_period(start_date + relativedelta(months=-x), end_of_month + relativedelta(months=-x), pt)) opened_in_period_list.append(oip) closed_in_period = Finding.objects.filter( mitigated__range=[start_date, end_date], test__engagement__product__prod_type=pt, severity__in=('Critical', 'High', 'Medium', 'Low')).values('numerical_severity').annotate( Count('numerical_severity')).order_by( 'numerical_severity') total_closed_in_period = Finding.objects.filter( mitigated__range=[start_date, end_date], test__engagement__product__prod_type=pt, severity__in=('Critical', 'High', 'Medium', 'Low')).aggregate(total=Sum( Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField())))['total'] overall_in_pt = Finding.objects.filter( date__lt=end_date, verified=True, false_p=False, duplicate=False, out_of_scope=False, mitigated__isnull=True, test__engagement__product__prod_type=pt, severity__in=('Critical', 'High', 'Medium', 'Low')).values('numerical_severity').annotate( Count('numerical_severity')).order_by( 'numerical_severity') total_overall_in_pt = Finding.objects.filter( date__lte=end_date, verified=True, false_p=False, duplicate=False, out_of_scope=False, mitigated__isnull=True, test__engagement__product__prod_type=pt, severity__in=('Critical', 'High', 'Medium', 'Low')).aggregate(total=Sum( Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField())))['total'] all_current_in_pt = Finding.objects.filter( date__lte=end_date, verified=True, false_p=False, duplicate=False, out_of_scope=False, mitigated__isnull=True, test__engagement__product__prod_type=pt, severity__in=('Critical', 'High', 'Medium', 'Low')).prefetch_related( 'test__engagement__product', 'test__engagement__product__prod_type', 'test__engagement__risk_acceptance', 'reporter').order_by('numerical_severity') top_ten = Product.objects.filter( engagement__test__finding__date__lte=end_date, engagement__test__finding__verified=True, engagement__test__finding__false_p=False, engagement__test__finding__duplicate=False, engagement__test__finding__out_of_scope=False, engagement__test__finding__mitigated__isnull=True, engagement__test__finding__severity__in=('Critical', 'High', 'Medium', 'Low'), prod_type=pt).annotate( critical=Sum( Case(When( engagement__test__finding__severity='Critical', then=Value(1)), output_field=IntegerField())), high=Sum( Case(When(engagement__test__finding__severity='High', then=Value(1)), output_field=IntegerField())), medium=Sum( Case(When(engagement__test__finding__severity='Medium', then=Value(1)), output_field=IntegerField())), low=Sum( Case(When(engagement__test__finding__severity='Low', then=Value(1)), output_field=IntegerField())), total=Sum( Case(When(engagement__test__finding__severity__in=( 'Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField()))).order_by( '-critical', '-high', '-medium', '-low')[:10] cip = { 'S0': 0, 'S1': 0, 'S2': 0, 'S3': 0, 'Total': total_closed_in_period } aip = { 'S0': 0, 'S1': 0, 'S2': 0, 'S3': 0, 'Total': total_overall_in_pt } for o in closed_in_period: cip[o['numerical_severity']] = o['numerical_severity__count'] for o in overall_in_pt: aip[o['numerical_severity']] = o['numerical_severity__count'] else: messages.add_message( request, messages.ERROR, "Please choose month and year and the Product Type.", extra_tags='alert-danger') add_breadcrumb(title="Bi-Weekly Metrics", top_level=True, request=request) return render( request, 'dojo/pt_counts.html', { 'form': form, 'start_date': start_date, 'end_date': end_date, 'opened_in_period': oip, 'trending_opened': opened_in_period_list, 'closed_in_period': cip, 'overall_in_pt': aip, 'all_current_in_pt': all_current_in_pt, 'top_ten': top_ten, 'pt': pt })
def get_queryset(self): return super().get_queryset().annotate(has_price=Case(When(price=0, then=0), default=1, output_field=IntegerField()))
def download(self, award_type, agency="all", generate_since=None): """ Create a delta file based on award_type, and agency_code (or all agencies) """ logger.info( "Starting generation. {}, Agency: {}".format(award_type, agency if agency == "all" else agency["name"]) ) award_map = AWARD_MAPPINGS[award_type] # Create Source and update fields to include correction_delete_ind source = DownloadSource( "transaction", award_map["letter_name"].lower(), "transactions", "all" if agency == "all" else agency["toptier_agency_id"], ) source.query_paths.update({"correction_delete_ind": award_map["correction_delete_ind"]}) if award_type == "Contracts": # Add the agency_id column to the mappings source.query_paths.update({"agency_id": "transaction__contract_data__agency_id"}) source.query_paths.move_to_end("agency_id", last=False) source.query_paths.move_to_end("correction_delete_ind", last=False) source.human_names = list(source.query_paths.keys()) # Apply filters to the queryset filters, agency_code = self.parse_filters(award_map["award_types"], agency) source.queryset = VALUE_MAPPINGS["transactions"]["filter_function"](filters) if award_type == "Contracts": source.queryset = source.queryset.annotate( correction_delete_ind=Case( When(transaction__contract_data__created_at__lt=generate_since, then=Value("C")), default=Value(""), output_field=CharField(), ) ) else: indicator_field = F("transaction__assistance_data__correction_delete_indicatr") source.queryset = source.queryset.annotate( correction_delete_ind=Case( When(transaction__assistance_data__updated_at__gt=generate_since, then=indicator_field), When(transaction__transactiondelta__isnull=False, then=Value("C")), default=indicator_field, output_field=CharField(), ) ) transaction_delta_queryset = source.queryset _filter = {"transaction__{}__{}__gte".format(award_map["model"], award_map["date_filter"]): generate_since} if self.debugging_end_date: _filter[ "transaction__{}__{}__lt".format(award_map["model"], award_map["date_filter"]) ] = self.debugging_end_date source.queryset = source.queryset.filter(**_filter) # UNION the normal results to the transaction_delta results. source.queryset = source.queryset.union( transaction_delta_queryset.filter(transaction__transactiondelta__isnull=False) ) # Generate file file_path = self.create_local_file(award_type, source, agency_code, generate_since) if file_path is None: logger.info("No new, modified, or deleted data; discarding file") elif not settings.IS_LOCAL: # Upload file to S3 and delete local version logger.info("Uploading file to S3 bucket and deleting local copy") multipart_upload( settings.MONTHLY_DOWNLOAD_S3_BUCKET_NAME, settings.USASPENDING_AWS_REGION, file_path, os.path.basename(file_path), ) os.remove(file_path) logger.info( "Finished generation. {}, Agency: {}".format(award_type, agency if agency == "all" else agency["name"]) )
def get_progress_and_last_active(target_nodes, **kwargs): # Prepare dictionaries to output the progress and last active, keyed by content_id output_progress_dict = {} output_last_active_dict = {} # Get a list of all the users that we are querying users = list( get_members_or_user(kwargs['collection_kind'], kwargs['collection_id'])) # Get a list of all content ids for all target nodes and their descendants content_ids = target_nodes.get_descendants( include_self=True).order_by().values_list("content_id", flat=True) # get all summary logs for the current user that correspond to the content nodes and descendant content nodes # Filter by users and the content ids progress_query = ContentSummaryLog.objects\ .filter(user__in=users, content_id__in=content_ids) # Conditionally filter by last active time if kwargs.get('last_active_time'): progress_query = progress_query.filter( end_timestamp__gte=parse(kwargs.get('last_active_time'))) # Get an annotated list of dicts of type: # { # 'content_id': <content_id>, # 'kind': <kind>, # 'total_progress': <sum of all progress for this content>, # 'log_count_total': <number of summary logs for this content>, # 'log_count_complete': <number of complete summary logs for this content>, # 'last_active': <most recent end_timestamp for this content>, # } progress_list = progress_query.values('content_id', 'kind').annotate( total_progress=Sum('progress'), log_count_total=Count('pk'), log_count_complete=Sum( Case(When(progress=1, then=1), default=0, output_field=IntegerField())), last_active=Max('end_timestamp')) # Evaluate query and make a loop dict of all progress progress_dict = {item.get('content_id'): item for item in progress_list} if isinstance(target_nodes, ContentNode): # Have been passed an individual model target_nodes = [target_nodes] # Loop through each node to add progress and last active information to the output dicts for target_node in target_nodes: # In the case of a topic, we need to look at the progress and last active from each of its descendants if target_node.kind == content_kinds.TOPIC: # Get all the content_ids and kinds of each leaf node as a tuple # (about half the size of the dict from 'values' method) # Remove topics in generator comprehension, rather than using .exclude as kind is not indexed # Use set to remove repeated content leaf_nodes = set(node for node in target_node.get_descendants( include_self=False).order_by().values_list( 'content_id', 'kind') if node[1] != content_kinds.TOPIC) # Get a unique set of all non-topic content kinds leaf_kinds = sorted(set(leaf_node[1] for leaf_node in leaf_nodes)) # Create a list of progress summary dicts for each content kind progress = [{ # For total progress sum across all the progress dicts for the descendant content leaf nodes 'total_progress': reduce( # Reduce with a function that just adds the total_progress of the passed in dict to the accumulator sum_progress_dicts, # Get all dicts of progress for every leaf_id that has some progress recorded # and matches the kind we are aggregating over (progress_dict.get(leaf_node[0]) for leaf_node in leaf_nodes\ if leaf_node[0] in progress_dict and leaf_node[1] == kind), # Pass in an initial value of total_progress as zero to initialize the reduce 0.0, ), 'kind': kind, # Count the number of leaf nodes of this particular kind 'node_count': reduce(lambda x, y: x + int(y[1] == kind), leaf_nodes, 0) } for kind in leaf_kinds] # Set the output progress for this topic to this list of progress dicts output_progress_dict[target_node.content_id] = progress # Create a generator of last active times for the leaf_ids last_active_times = map( # Return the last active time for this leaf_node lambda leaf_node: progress_dict[leaf_node[0]]['last_active'], filter( # Filter leaf_nodes to those that are in the progress_dict lambda leaf_node: leaf_node[0] in progress_dict, leaf_nodes)) # Max does not handle empty iterables, so try this try: # If it is not empty, great! output_last_active_dict[target_node.content_id] = max( last_active_times) except (ValueError, TypeError): # If it is empty, catch the value error and set the last active time to None # If they are all none, catch the TypeError and also set to None output_last_active_dict[target_node.content_id] = None else: if target_node.content_id in progress_dict: progress = progress_dict.pop(target_node.content_id) output_last_active_dict[target_node.content_id] = progress.pop( 'last_active') # return as array for consistency in api output_progress_dict[target_node.content_id] = [{ 'total_progress': progress['total_progress'], 'log_count_total': progress['log_count_total'], 'log_count_complete': progress['log_count_complete'], }] elif target_node.content_id not in output_progress_dict: # Not in the progress dict, but also not in our output, so supply default values output_last_active_dict[target_node.content_id] = None output_progress_dict[target_node.content_id] = [{ 'total_progress': 0.0, 'log_count_total': 0, 'log_count_complete': 0, }] return output_progress_dict, output_last_active_dict
def features_of_dialect(request, dialect_id_string, section=None): '''The grammar features of a chosen dialect, in tree format ''' dialect_ids = [int(x) for x in dialect_id_string.split(',')] is_bulk_edit = request.GET.get('edit') or False base_dialect_id = int(request.GET.get('base_on', 0)) if is_bulk_edit and base_dialect_id: dialect_ids.append(base_dialect_id) preserved = Case( *[When(pk=pk, then=pos) for pos, pk in enumerate(dialect_ids)]) dialects = Dialect.objects.filter(id__in=dialect_ids).order_by(preserved) chosen_root = get_section_root(section) # annotated lists are an efficient way of getting a big chunk of a treebeard tree # see: https://django-treebeard.readthedocs.io/en/latest/api.html#treebeard.models.Node.get_annotated_list max_depth = None feature_list = Feature.get_annotated_list(parent=chosen_root, max_depth=max_depth) # process bulk save if that's what's happening # todo - separate this out into a different function, with own url and pass feature id list # through form so it doesn't rely on the above code bulk_text = request.POST.get('bulk_edit', None) entry_regex = r'^\s*(?P<entry>.+?)\s*(?P<frequency>\b[MPmp]\b){0,1}\s*(?P<comment>\".+\")?\s*$' if bulk_text is not None: row_texts = bulk_text.split('\n') for row_text, feature in zip_longest( row_texts, feature_list[1:], fillvalue='' ): # skip first feature as it matches the top-level group dfes = [] for entry_text in row_text.split('~'): matches = re.match(entry_regex, entry_text.strip('\r').strip(' ')) if not matches: continue matches_dict = matches.groupdict() if len(matches_dict['entry'].strip(' ')) < 1: continue matches_dict['frequency'] = matches_dict['frequency'].upper( ) if matches_dict['frequency'] else 'P' matches_dict['comment'] = matches_dict['comment'].strip( '"') if matches_dict['comment'] else None dfes.append(DialectFeatureEntry(**matches_dict)) # load the existing DialectFeature or prepare a new one df = DialectFeature.objects.filter(dialect=dialects[0].id, feature=feature[0].id).first() if not df: df = DialectFeature(dialect_id=dialects[0].id, feature_id=feature[0].id) df.save() df.entries.all().delete() # clear any existing entries df.entries.set(dfes, bulk=False) if len( dfes ) < 1: # delete the Dialect Feature if no entries were submitted df.delete() return HttpResponseRedirect( reverse('dialects:dialect-grammar-section', args=(dialects[0].id, section))) # ( feature, stuff, [{df: _, entries: [], examples: []}, {df: _, entries: [], examples: []}, ...] ) base_path = chosen_root.path if chosen_root else '' preserved = Case( *[When(dialect_id=pk, then=pos) for pos, pk in enumerate(dialect_ids)]) dialectfeatures = DialectFeature.objects.filter(dialect__in=dialect_ids) \ .filter(feature__path__startswith=base_path) \ .values('id', 'dialect_id', 'feature_id', 'feature__path', 'feature__fullheading', 'is_absent', 'introduction', 'comment', 'category') \ .order_by('feature__path', preserved) entries = DialectFeatureEntry.objects.filter(feature__dialect__in=dialect_ids) \ .filter(feature__feature__path__startswith=base_path) \ .values('feature__id', 'id', 'entry', 'frequency', 'comment') \ .order_by('feature__feature__path', '-frequency') entries_dict = {} for x in entries: entries_dict.setdefault(x['feature__id'], []).append(x) examples_dict = {} if is_bulk_edit: entries = entries.filter(feature__feature__depth=max_depth) elif len(dialects) == 1: examples = DialectFeatureExample.objects.filter(feature__dialect__in=dialect_ids) \ .filter(feature__feature__path__startswith=base_path) \ .values('feature__id', 'id', 'example') \ .order_by('feature__feature__path') for x in examples: examples_dict.setdefault(x['feature__id'], []).append(x) num_features = 0 dialectfeatures_dict = {} for x in dialectfeatures: num_features += 1 x.update({ 'entries': entries_dict.get(x['id'], []), 'examples': examples_dict.get(x['id'], []), }) dialectfeatures_dict.setdefault(x['feature_id'], []).append(x) for i, feature in enumerate(feature_list): feature_list[i] = (feature[0], feature[1], dialectfeatures_dict.get(feature[0].id, [])) context = { 'dialect_ids': dialect_ids, 'dialects': dialects, 'section': chosen_root, 'breadcrumb_bits': make_breadcrumb_bits(chosen_root), 'feature_list': feature_list, 'num_features': num_features, 'all_dialects': Dialect.objects.all() \ .filter(features__feature__path__startswith=base_path) \ .exclude(id__in=dialect_ids) \ .annotate(feature_count=Count('features')) .values_list('id', 'name', 'feature_count'), 'bulk_edit': is_bulk_edit, } if context['bulk_edit']: def encode_entry(entry): encoded_entry = entry['entry'] if entry['frequency'] not in ('P', None): encoded_entry += ' {}'.format(entry['frequency']) if entry['comment']: encoded_entry += ' "{}"'.format(entry['comment'].replace( '\r', '').replace('\n', '; ')) return encoded_entry raw_rows = [] for feature, info, dialectfeatures in feature_list[1:]: if not dialectfeatures: raw_rows.append('') continue dialect_idx = -1 if base_dialect_id else 0 entries = dialectfeatures[dialect_idx].get('entries', []) raw_rows.append(' ~ '.join([encode_entry(x) for x in entries])) # join using html newline entity to prevent textarea ignoring first newline char # see: https://stackoverflow.com/a/49604548 raw_text = ' '.join(raw_rows) context.update({ 'raw_text': raw_text, }) if chosen_root: context['total_features'] = DialectFeature.objects.filter( dialect__in=dialect_ids).count() return render(request, 'grammar/feature_list.html', context)
def statistics(): """Gather statistics about users and their inboxes""" try: last_stat = models.Statistic.objects.latest("date") except models.Statistic.DoesNotExist: last_stat = None # the keys of these dictionaries have awful names for historical reasons # don't change them unless you want to do a data migration one_day_ago = timezone.now() - timedelta(days=1) user_aggregate = { "count": Count("id", distinct=True), "new": Coalesce( Count( Case(When(date_joined__gte=one_day_ago, then=F("id")), ), distinct=True, ), 0), "oldest_user_joined": Min("date_joined"), "with_inboxes": Coalesce( Count( Case(When(inbox__isnull=False, then=F("id")), ), distinct=True, ), 0), } inbox_aggregate = { "inbox_count__avg": Coalesce(Avg("inbox_count"), 0), "inbox_count__max": Coalesce(Max("inbox_count"), 0), "inbox_count__min": Coalesce(Min("inbox_count"), 0), "inbox_count__stddev": Coalesce(StdDev("inbox_count"), 0), "inbox_count__sum": Coalesce(Sum("inbox_count"), 0), } email_aggregate = { "email_count__avg": Coalesce(Avg("email_count"), 0), "email_count__max": Coalesce(Max("email_count"), 0), "email_count__min": Coalesce(Min("email_count"), 0), "email_count__stddev": Coalesce(StdDev("email_count"), 0), "email_count__sum": Coalesce(Sum("email_count"), 0), } # collect user and inbox stats users = get_user_model().objects.aggregate(**user_aggregate) inboxes = get_user_model().objects.annotate( inbox_count=Count("inbox__id")).aggregate(**inbox_aggregate) domain_count = models.Domain.objects.available(None).count() inboxes_possible = len(settings.INBOX_CHOICES)**settings.INBOX_LENGTH inboxes["total_possible"] = inboxes_possible * domain_count # collect email state inbox_qs = models.Inbox.objects.exclude(deleted=True).annotate( email_count=Count("email__id")) emails = inbox_qs.aggregate(**email_aggregate) inboxes["with_emails"] = inbox_qs.exclude(email_count=0).count() inboxes["disowned"] = models.Inbox.objects.filter( user__isnull=True).count() emails["emails_read"] = models.Email.objects.filter(read=True).count() if last_stat: email_diff = (emails["email_count__sum"] or 0) - (last_stat.emails["email_count__sum"] or 0) emails["running_total"] = last_stat.emails["running_total"] + max( email_diff, 0) else: emails["running_total"] = emails["email_count__sum"] or 0 stat = models.Statistic( users=users, emails=emails, inboxes=inboxes, ) stat.save() log.info("Saved statistics (%s)", stat.date)
def generate_monthly_system_stats( location=SHEET_FILES_FOLDER + 'GetIN Traceability Form ' + str(timezone.now().strftime("%m-%d-%Y, %H:%M")) + '.xls', end_date=None): """ :param location: name of the excel file containing the output :param end_date: date up to which the metrics will be generated Creates user performance excel sheet Sheet Fields: NAME OF HEALTHWORKER, PHONE NO., ROLE, DISTRICT, MONTH, YEAR, NO. OF GIRLS MAPPED, ATTENDED, MISSED, EXPECTED, FOLLOW UPS """ book = WorkbookCreation() sheet1 = book.add_sheet('Sheet1') data = [["NAME OF HEALTHWORKER"], ["PHONE NO."], ["ROLE"], ["DISTRICT"], ["MONTH"], ["YEAR"], ["NO. OF GIRLS MAPPED"], ["ATTENDED"], ["MISSED"], ["EXPECTED"], ["FOLLOW UPS"]] for c_index, columns in enumerate(data): for r_index, row_item in enumerate(columns): sheet1.write(r_index, c_index, row_item) book.save(location) book.save(TemporaryFile()) convert_xls_to_xlsx(location) for district in District.objects.all(): created_at = timezone.datetime(2019, 10, 1).replace(tzinfo=pytz.utc) if not end_date: end_date = timezone.now() while created_at < end_date.replace(tzinfo=pytz.utc): for user in User.objects.filter(district=district): if user.role in [ USER_TYPE_DHO, USER_TYPE_AMBULANCE, USER_TYPE_DEVELOPER ]: continue girls = Girl.objects.filter( Q(created_at__gte=created_at) & Q(created_at__lte=add_months(created_at, 1).replace( tzinfo=pytz.utc)) & Q(user=user)).count() wb = load_workbook(location.replace('xls', 'xlsx')) sheet = wb['Sheet1'] appointment_data = {"attended": 0, "expected": 0, "missed": 0} if user.role == USER_TYPE_MIDWIFE: appointment_data = Appointment.objects.filter( Q(user=user) & Q(created_at__gte=created_at) & Q(created_at__lte=add_months(created_at, 1).replace( tzinfo=pytz.utc))).aggregate( attended=Count( Case( When(Q(status=ATTENDED), then=1), output_field=IntegerField(), )), expected=Count( Case( When(Q(status=EXPECTED), then=1), output_field=IntegerField(), )), missed=Count( Case( When(Q(status=MISSED), then=1), output_field=IntegerField(), ))) followups = FollowUp.objects.filter( Q(user=user) & Q(created_at__gte=created_at) & Q(created_at__lte=add_months(created_at, 1).replace( tzinfo=pytz.utc))).count() sheet.append([ user.first_name + " " + user.last_name, user.phone, user.role, district.name, created_at.strftime("%B"), created_at.strftime("%Y"), girls, appointment_data['attended'], appointment_data['expected'], appointment_data['missed'], followups ]) wb.save(location.replace('xls', 'xlsx')) created_at = add_months(created_at, 1).replace(tzinfo=pytz.utc)
def get(self, request): form_data = dict(request.GET) age_groups = form_data.get("age-group[]") blood_groups = form_data.get("age-group[]") genotypes = form_data.get("genotype[]") histories = form_data.get("medical-history[]") weight_statuses = form_data.get("weight-status[]") age_filter = Q(age_group__in=age_groups) if age_groups else Q() blood_group_filter = Q( blood_group__in=blood_groups) if blood_groups else Q() genotype_filter = Q(genotype__in=genotypes) if genotypes else Q() history_filter = reduce( operator.or_, (Q(histories__diagnosis__icontains=history) | Q(histories__description__icontains=history) for history in histories)) if histories else Q() weight_status_filter = Q( weight_status__in=weight_statuses) if weight_statuses else Q() current = date.today() min_date1 = date(current.year - 4, current.month, current.day) max_date1 = date(current.year, current.month, current.day) min_date2 = date(current.year - 12, current.month, current.day) max_date2 = date(current.year - 4, current.month, current.day) min_date3 = date(current.year - 19, current.month, current.day) max_date3 = date(current.year - 13, current.month, current.day) min_date4 = date(current.year - 35, current.month, current.day) max_date4 = date(current.year - 20, current.month, current.day) min_date5 = date(current.year - 50, current.month, current.day) max_date5 = date(current.year - 36, current.month, current.day) min_date6 = date(current.year - 70, current.month, current.day) max_date6 = date(current.year - 51, current.month, current.day) age_group_annotate = Case(When(dob__gte=min_date1, dob__lte=max_date1, then=Value("babies")), When(dob__gte=min_date2, dob__lte=max_date2, then=Value("childre")), When(dob__gte=min_date3, dob__lte=max_date3, then=Value("teenagers")), When(dob__gte=min_date4, dob__lte=max_date4, then=Value("youths")), When(dob__gte=min_date5, dob__lte=max_date5, then=Value("adults")), When(dob__gte=min_date6, dob__lte=max_date6, then=Value("elders")), default=Value("old"), output_field=CharField()) weight_status_annotate = Case( When(bmi__lt=18.5, bmi__gte=0, then=Value("under-weight")), When(bmi__lt=25, bmi__gte=18.5, then=Value("normal-weight")), When(bmi__lt=30, bmi__gte=25, then=Value("overweight")), When(bmi__lt=35, bmi__gte=30, then=Value("obesity-class-1")), When(bmi__lt=40, bmi__gte=35, then=Value("obesity-class-2")), When(bmi__gte=40, then=Value("extreme-obesity-class-3")), default=None, output_field=CharField()) bmi = F('weight') / (F("height") * F("height")) patients = Patient.objects.annotate( age_group=age_group_annotate, bmi=bmi).annotate(weight_status=weight_status_annotate) patients = patients.filter(age_filter | blood_group_filter | genotype_filter | history_filter | weight_status_filter).distinct() serializer = PatientSerializer(patients, many=True) return Response({"patients": serializer.data})
def get_products_data( queryset: "QuerySet", export_fields: Set[str], attribute_ids: Optional[List[int]], warehouse_ids: Optional[List[int]], channel_ids: Optional[List[int]], ) -> List[Dict[str, Union[str, bool]]]: """Create data list of products and their variants with fields values. It return list with product and variant data which can be used as import to csv writer and list of attribute and warehouse headers. """ products_with_variants_data = [] product_fields = set( ProductExportFields.HEADERS_TO_FIELDS_MAPPING["fields"].values() ) product_export_fields = export_fields & product_fields product_export_fields.add("variants__id") products_data = ( queryset.annotate( product_weight=Case( When(weight__isnull=False, then=Concat("weight", V(" g"))), default=V(""), output_field=CharField(), ), variant_weight=Case( When( variants__weight__isnull=False, then=Concat("variants__weight", V(" g")), ), default=V(""), output_field=CharField(), ), ) .order_by("pk", "variants__pk") .values(*product_export_fields) .distinct("pk", "variants__pk") ) products_relations_data = get_products_relations_data( queryset, export_fields, attribute_ids, channel_ids ) variants_relations_data = get_variants_relations_data( queryset, export_fields, attribute_ids, warehouse_ids, channel_ids ) for product_data in products_data: pk = product_data["id"] variant_pk = product_data.pop("variants__id") product_relations_data: Dict[str, str] = products_relations_data.get(pk, {}) variant_relations_data: Dict[str, str] = variants_relations_data.get( variant_pk, {} ) data = {**product_data, **product_relations_data, **variant_relations_data} products_with_variants_data.append(data) return products_with_variants_data
def with_likes(self): return self.annotate(likes__count=Sum( Case(When(bloglikes__flag='like', then=1), default=0, output_field=IntegerField())))
def get(self, request, organization): """ List saved queries for organization """ if not self.has_feature(organization, request): return self.respond(status=404) queryset = (DiscoverSavedQuery.objects.filter( organization=organization).select_related( "created_by").prefetch_related("projects").extra( select={"lower_name": "lower(name)"})) query = request.query_params.get("query") if query: tokens = tokenize_query(query) for key, value in tokens.items(): if key == "name" or key == "query": value = " ".join(value) queryset = queryset.filter(name__icontains=value) elif key == "version": value = " ".join(value) queryset = queryset.filter(version=value) else: queryset = queryset.none() sort_by = request.query_params.get("sortBy") if sort_by in ("name", "-name"): order_by = [ "-lower_name" if sort_by.startswith("-") else "lower_name", "-date_created", ] elif sort_by in ("dateCreated", "-dateCreated"): order_by = "-date_created" if sort_by.startswith( "-") else "date_created" elif sort_by in ("dateUpdated", "-dateUpdated"): order_by = "-date_updated" if sort_by.startswith( "-") else "date_updated" elif sort_by == "myqueries": order_by = [ Case(When(created_by_id=request.user.id, then=-1), default="created_by_id"), "-date_created", ] else: order_by = "lower_name" if not isinstance(order_by, list): order_by = [order_by] queryset = queryset.order_by(*order_by) # Old discover expects all queries and uses this parameter. if request.query_params.get("all") == "1": saved_queries = list(queryset.all()) return Response(serialize(saved_queries), status=200) def data_fn(offset, limit): return list(queryset[offset:offset + limit]) return self.paginate( request=request, paginator=GenericOffsetPaginator(data_fn=data_fn), on_results=lambda x: serialize(x, request.user), default_per_page=25, )
def filter(self, qs, value): # инициализируем подключение client = Elasticsearch([settings.ELASTICSEARCH_HOST]) value = value.lower() # формируем запрос search_query = { "bool": { "must_not": [ # исключает из выдачи is_published=False { "term": { "is_published": False } } ], "should": [ { "simple_query_string": { # ищем что-то разумное "fields": ["fullname", "category_name"], "quote_field_suffix": ".exact", "query": value } }, { # частичное вхождение по строкам с транслитом (англ->рус) # constant_score запрещает буст по частоте вхождения "constant_score": { "filter": { "match": { "fullname_translit": { "query": value, "fuzziness": 1, "operator": "and", } } } } }, ] } } # Инициализация запроса s = Search(using=client, index='offer') \ .query(search_query)\ .sort("_score", "-views")\ .extra(size=self.max_result, from_=0) self.hits_list = [] items = s.execute() if items: for item in items: self.hits_list.append(item.meta.id) # нужно для того, чтобы у выборки из пусгреса сохранился порядок, который вернул эластик self.hits_order = Case(*[ When(pk=pk, then=pos) for pos, pk in enumerate(self.hits_list) ]) qs = qs.filter(id__in=self.hits_list).order_by(self.hits_order) else: qs = qs.none() # TODO: старая реализация. Может, оставить, как fallback? # else: # qs = qs.annotate(full_name=Concat( # 'product__article', Value(' '), # 'product__title', Value(' '), # 'product__search_title')) # bits = value.split(' ') # if len(bits) is 1 and bits[0].isdecimal(): # full_name_clauses = Q(full_name__icontains=bits[0]) # else: # full_name_clauses = reduce( # operator.and_, # [Q(full_name__iregex=r'(^|\s)%s' % escape(v)) # for v in bits]) # # unpublished = Category.objects.get_queryset_descendants( # Category.objects.filter(is_published=False), # include_self=True) # # qs = (qs.filter(full_name_clauses) # .exclude(product__category__in=unpublished)) # # if self.uniq_category: # products = (qs.order_by('product__category__title') # .distinct('product__category__title')) # qs = (qs.filter(id__in=products) # .order_by('-product__category__views')) return qs
def sort_by_attribute(self, attribute_pk: Union[int, str], ascending: bool = True): """Sort a query set by the values of the given product attribute. :param attribute_pk: The database ID (must be a number) of the attribute to sort by. :param ascending: The sorting direction. """ qs: models.QuerySet = self # Retrieve all the products' attribute data IDs (assignments) and # product types that have the given attribute associated to them associated_values = tuple( AttributeProduct.objects.filter( attribute_id=attribute_pk).values_list("pk", "product_type_id")) if not associated_values: if not ascending: return qs.reverse() return qs attribute_associations, product_types_associated_to_attribute = zip( *associated_values) qs = qs.annotate( # Contains to retrieve the attribute data (singular) of each product # Refer to `AttributeProduct`. filtered_attribute=FilteredRelation( relation_name="attributes", condition=Q( attributes__assignment_id__in=attribute_associations), ), # Implicit `GROUP BY` required for the `StringAgg` aggregation grouped_ids=Count("id"), # String aggregation of the attribute's values to efficiently sort them concatenated_values=Case( # If the product has no association data but has the given attribute # associated to its product type, then consider the concatenated values # as empty (non-null). When( Q(product_type_id__in=product_types_associated_to_attribute ) & Q(filtered_attribute=None), then=models.Value(""), ), default=StringAgg( F("filtered_attribute__values__name"), delimiter=",", ordering=([ f"filtered_attribute__values__{field_name}" for field_name in AttributeValue._meta.ordering or [] ]), ), output_field=models.CharField(), ), concatenated_values_order=Case( # Make the products having no such attribute be last in the sorting When(concatenated_values=None, then=2), # Put the products having an empty attribute value at the bottom of # the other products. When(concatenated_values="", then=1), # Put the products having an attribute value to be always at the top default=0, output_field=models.IntegerField(), ), ) # Sort by concatenated_values_order then # Sort each group of products (0, 1, 2, ...) per attribute values # Sort each group of products by name, # if they have the same values or not values qs = qs.order_by("concatenated_values_order", "concatenated_values", "name") # Descending sorting if not ascending: return qs.reverse() return qs
def conditional_sum(value=1, **cond): """Wrapper to generate SUM on boolean/enum values""" return Sum( Case(When(then=value, **cond), default=0, output_field=IntegerField()))
def get_all_activity_over_time(self, start, end): events_qs = Event.objects.filter(start__gte=start, start__lte=end) swc_tag = Tag.objects.get(name='SWC') dc_tag = Tag.objects.get(name='DC') wise_tag = Tag.objects.get(name='WiSE') TTT_tag = Tag.objects.get(name='TTT') self_organized_host = Organization.objects.get(domain='self-organized') # count workshops: SWC, DC, total (SWC and/or DC), self-organized, # WiSE, TTT swc_workshops = events_qs.filter(tags=swc_tag) dc_workshops = events_qs.filter(tags=dc_tag) swc_dc_workshops = events_qs.filter(tags__in=[swc_tag, dc_tag]).count() wise_workshops = events_qs.filter(tags=wise_tag).count() ttt_workshops = events_qs.filter(tags=TTT_tag).count() self_organized_workshops = events_qs \ .filter(administrator=self_organized_host).count() # total and unique instructors for both SWC and DC workshops swc_total_instr = Person.objects \ .filter(task__event__in=swc_workshops, task__role__name='instructor') swc_unique_instr = swc_total_instr.distinct().count() swc_total_instr = swc_total_instr.count() dc_total_instr = Person.objects \ .filter(task__event__in=dc_workshops, task__role__name='instructor') dc_unique_instr = dc_total_instr.distinct().count() dc_total_instr = dc_total_instr.count() # total learners for both SWC and DC workshops swc_total_learners = swc_workshops.aggregate(count=Sum('attendance')) swc_total_learners = swc_total_learners['count'] dc_total_learners = dc_workshops.aggregate(count=Sum('attendance')) dc_total_learners = dc_total_learners['count'] # Workshops missing any of this data. missing_attendance = events_qs.filter(attendance=None) \ .values_list('slug', flat=True) missing_instructors = events_qs.annotate(instructors=Sum( Case(When(task__role__name='instructor', then=Value(1)), default=Value(0), output_field=IntegerField()))).filter( instructors=0).values_list('slug', flat=True) return { 'start': start, 'end': end, 'workshops': { 'SWC': swc_workshops.count(), 'DC': dc_workshops.count(), # This dictionary is traversed in a template where we cannot # write "{{ data.workshops.SWC,DC }}", because commas are # disallowed in templates. Therefore, we include # swc_dc_workshops twice, under two different keys: # - 'SWC,DC' - for backward compatibility, # - 'SWC_or_DC' - so that you can access it in a template. 'SWC,DC': swc_dc_workshops, 'SWC_or_DC': swc_dc_workshops, 'WiSE': wise_workshops, 'TTT': ttt_workshops, # We include self_organized_workshops twice, under two # different keys, for the same reason as swc_dc_workshops. 'self-organized': self_organized_workshops, 'self_organized': self_organized_workshops, }, 'instructors': { 'SWC': { 'total': swc_total_instr, 'unique': swc_unique_instr, }, 'DC': { 'total': dc_total_instr, 'unique': dc_unique_instr, }, }, 'learners': { 'SWC': swc_total_learners, 'DC': dc_total_learners, }, 'missing': { 'attendance': missing_attendance, 'instructors': missing_instructors, } }
def num_answered(examlog): return (examlog.attemptlogs.values_list( "item", "content_id").order_by("completion_timestamp").distinct().aggregate( complete__sum=Count(Case(When(complete=True, then=1), default=0))).get("complete__sum"))
def for_table(self, user): activities = self.model.activities.rel.model comments = activities.comments.filter(submission=OuterRef('id')).visible_to(user) roles_for_review = self.model.assigned.field.model.objects.with_roles().filter( submission=OuterRef('id'), reviewer=user) review_model = self.model.reviews.field.model reviews = review_model.objects.filter(submission=OuterRef('id')) opinions = review_model.opinions.field.model.objects.filter(review__submission=OuterRef('id')) reviewers = self.model.assigned.field.model.objects.filter(submission=OuterRef('id')) return self.with_latest_update().annotate( comment_count=Coalesce( Subquery( comments.values('submission').order_by().annotate(count=Count('pk')).values('count'), output_field=IntegerField(), ), 0, ), opinion_disagree=Subquery( opinions.filter(opinion=DISAGREE).values( 'review__submission' ).annotate(count=Count('*')).values('count')[:1], output_field=IntegerField(), ), review_staff_count=Subquery( reviewers.staff().values('submission').annotate(count=Count('pk')).values('count'), output_field=IntegerField(), ), review_count=Subquery( reviewers.values('submission').annotate(count=Count('pk')).values('count'), output_field=IntegerField(), ), review_submitted_count=Subquery( reviewers.reviewed().values('submission').annotate( count=Count('pk', distinct=True) ).values('count'), output_field=IntegerField(), ), review_recommendation=Case( When(opinion_disagree__gt=0, then=MAYBE), default=Subquery( reviews.submitted().values('submission').annotate( calc_recommendation=Sum('recommendation') / Count('recommendation'), ).values('calc_recommendation'), output_field=IntegerField(), ) ), role_icon=Subquery(roles_for_review[:1].values('role__icon')), ).prefetch_related( Prefetch( 'assigned', queryset=AssignedReviewers.objects.reviewed().review_order().select_related( 'reviewer', ).prefetch_related( Prefetch('review__opinions', queryset=ReviewOpinion.objects.select_related('author')), ), to_attr='has_reviewed' ), Prefetch( 'assigned', queryset=AssignedReviewers.objects.not_reviewed().staff(), to_attr='hasnt_reviewed' ) ).select_related( 'page', 'round', 'lead', 'user', 'screening_status', 'previous__page', 'previous__round', 'previous__lead', )
def testing_annotations(): cond1 = Conducente(nome="Luke Skywalker", nick="Luke", attivo=True) cond1.save() cond2 = Conducente(nome="Anakin Skywalker", nick=None, attivo=True) cond2.save() lrif = Luogo(nome='Home') lrif.save() Viaggio(annullato=True, data=date_enforce(datetime.date(2016, 1, 11)), da=lrif, a=lrif, luogoDiRiferimento=lrif, prezzo=1000, conducente=cond1, conducente_confermato=True, ).save() Viaggio(data=date_enforce(datetime.date(2016, 1, 11)), da=lrif, a=lrif, luogoDiRiferimento=lrif, prezzo=10, conducente=cond1, conducente_confermato=True, ).save() Viaggio(data=date_enforce(datetime.date(2016, 1, 12)), da=lrif, a=lrif, luogoDiRiferimento=lrif, prezzo=10, conducente=cond1, conducente_confermato=True, ).save() Viaggio(data=date_enforce(datetime.date(2016, 1, 12)), da=lrif, a=lrif, luogoDiRiferimento=lrif, prezzo=30, conducente=cond2, conducente_confermato=True, ).save() Viaggio(data=date_enforce(datetime.date(2016, 2, 12)), da=lrif, a=lrif, luogoDiRiferimento=lrif, prezzo=10, conducente=cond1, conducente_confermato=True, ).save() # Testing the query qs = Viaggio.objects.filter(annullato=False, data__gte=date_enforce(datetime.date(2016, 1, 11)), data__lt=date_enforce(datetime.date(2016, 2, 14))) qs = (qs .order_by() .annotate(year=Extract('data', lookup_name='year'), month=Extract('data', lookup_name='month')) .order_by('conducente') .values('year', 'month') .annotate(tot=Sum('prezzo'), commissione=Sum( Case(When(tipo_commissione='F', then=F('commissione')), When(tipo_commissione='P', then=F('commissione') * F('prezzo') / Value(100)), ), output_field=DecimalField(max_digits=9, decimal_places=2, default=0), ), conducente__nome=F('conducente__nome') ).order_by('conducente__nome') ) # print(qs.query) qs = list(qs) pprint(list(qs)) assert len(qs) == 3, "We should have 3 rows: Luke with 2 months, Ana with 1" luke_runs = list(filter(lambda x: x['conducente__nome'] == 'Luke Skywalker', qs)) assert len(luke_runs) == 2 assert luke_runs[0]['month'] == 1 and luke_runs[0]['tot'] == 20 assert luke_runs[1]['month'] == 2 and luke_runs[1]['tot'] == 10 anakin_runs = list(filter(lambda x: x['conducente__nome'] == 'Anakin Skywalker', qs)) assert len(anakin_runs) == 1 assert anakin_runs[0]['month'] == 1 and anakin_runs[0]['tot'] == 30
def with_entry_count(self): return self.annotate(entries_count=Count( Case(When(entries__is_published=True, then=1))))
def metrics(request, mtype): template = 'dojo/metrics.html' page_name = 'Product Type Metrics' show_pt_filter = True findings = Finding.objects.filter( verified=True, severity__in=('Critical', 'High', 'Medium', 'Low', 'Info') ).prefetch_related( 'test__engagement__product', 'test__engagement__product__prod_type', 'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter' ).extra(select={ 'ra_count': 'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN ' 'dojo_risk_acceptance_accepted_findings ON ' '( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) ' 'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id', }, ) active_findings = Finding.objects.filter( verified=True, active=True, severity__in=('Critical', 'High', 'Medium', 'Low', 'Info') ).prefetch_related( 'test__engagement__product', 'test__engagement__product__prod_type', 'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter' ).extra(select={ 'ra_count': 'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN ' 'dojo_risk_acceptance_accepted_findings ON ' '( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) ' 'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id', }, ) if mtype != 'All': pt = Product_Type.objects.filter(id=mtype) request.GET._mutable = True request.GET.appendlist('test__engagement__product__prod_type', mtype) request.GET._mutable = False mtype = pt[0].name show_pt_filter = False page_name = '%s Metrics' % mtype prod_type = pt elif 'test__engagement__product__prod_type' in request.GET: prod_type = Product_Type.objects.filter(id__in=request.GET.getlist( 'test__engagement__product__prod_type', [])) else: prod_type = Product_Type.objects.all() findings = MetricsFindingFilter(request.GET, queryset=findings) active_findings = MetricsFindingFilter(request.GET, queryset=active_findings) findings.qs # this is needed to load details from filter since it is lazy active_findings.qs # this is needed to load details from filter since it is lazy start_date = findings.filters['date'].start_date start_date = datetime(start_date.year, start_date.month, start_date.day, tzinfo=timezone.get_current_timezone()) end_date = findings.filters['date'].end_date end_date = datetime(end_date.year, end_date.month, end_date.day, tzinfo=timezone.get_current_timezone()) if len(prod_type) > 0: findings_closed = Finding.objects.filter( mitigated__range=[start_date, end_date], test__engagement__product__prod_type__in=prod_type ).prefetch_related('test__engagement__product') # capture the accepted findings in period accepted_findings = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date], test__engagement__product__prod_type__in=prod_type). \ prefetch_related('test__engagement__product') accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date], test__engagement__product__prod_type__in=prod_type). \ prefetch_related('test__engagement__product').aggregate( total=Sum( Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField())), critical=Sum( Case(When(severity='Critical', then=Value(1)), output_field=IntegerField())), high=Sum( Case(When(severity='High', then=Value(1)), output_field=IntegerField())), medium=Sum( Case(When(severity='Medium', then=Value(1)), output_field=IntegerField())), low=Sum( Case(When(severity='Low', then=Value(1)), output_field=IntegerField())), info=Sum( Case(When(severity='Info', then=Value(1)), output_field=IntegerField())), ) else: findings_closed = Finding.objects.filter( mitigated__range=[start_date, end_date]).prefetch_related( 'test__engagement__product') accepted_findings = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date]). \ prefetch_related('test__engagement__product') accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date]). \ prefetch_related('test__engagement__product').aggregate( total=Sum( Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField())), critical=Sum( Case(When(severity='Critical', then=Value(1)), output_field=IntegerField())), high=Sum( Case(When(severity='High', then=Value(1)), output_field=IntegerField())), medium=Sum( Case(When(severity='Medium', then=Value(1)), output_field=IntegerField())), low=Sum( Case(When(severity='Low', then=Value(1)), output_field=IntegerField())), info=Sum( Case(When(severity='Info', then=Value(1)), output_field=IntegerField())), ) r = relativedelta(end_date, start_date) months_between = (r.years * 12) + r.months # include current month months_between += 1 weeks_between = int( ceil((((r.years * 12) + r.months) * 4.33) + (r.days / 7))) if weeks_between <= 0: weeks_between += 2 monthly_counts = get_period_counts(active_findings.qs, findings.qs, findings_closed, accepted_findings, months_between, start_date, relative_delta='months') weekly_counts = get_period_counts(active_findings.qs, findings.qs, findings_closed, accepted_findings, weeks_between, start_date, relative_delta='weeks') top_ten = Product.objects.filter( engagement__test__finding__verified=True, engagement__test__finding__false_p=False, engagement__test__finding__duplicate=False, engagement__test__finding__out_of_scope=False, engagement__test__finding__mitigated__isnull=True, engagement__test__finding__severity__in=('Critical', 'High', 'Medium', 'Low'), prod_type__in=prod_type).annotate( critical=Sum( Case(When(engagement__test__finding__severity='Critical', then=Value(1)), output_field=IntegerField())), high=Sum( Case(When(engagement__test__finding__severity='High', then=Value(1)), output_field=IntegerField())), medium=Sum( Case(When(engagement__test__finding__severity='Medium', then=Value(1)), output_field=IntegerField())), low=Sum( Case(When(engagement__test__finding__severity='Low', then=Value(1)), output_field=IntegerField())), total=Sum( Case(When(engagement__test__finding__severity__in=('Critical', 'High', 'Medium', 'Low'), then=Value(1)), output_field=IntegerField()))).order_by( '-critical', '-high', '-medium', '-low')[:10] age_detail = [0, 0, 0, 0] in_period_counts = { "Critical": 0, "High": 0, "Medium": 0, "Low": 0, "Info": 0, "Total": 0 } in_period_details = {} closed_in_period_counts = { "Critical": 0, "High": 0, "Medium": 0, "Low": 0, "Info": 0, "Total": 0 } closed_in_period_details = {} accepted_in_period_details = {} for finding in findings.qs: if 0 <= finding.age <= 30: age_detail[0] += 1 elif 30 < finding.age <= 60: age_detail[1] += 1 elif 60 < finding.age <= 90: age_detail[2] += 1 elif finding.age > 90: age_detail[3] += 1 in_period_counts[finding.severity] += 1 in_period_counts['Total'] += 1 if finding.test.engagement.product.name not in in_period_details: in_period_details[finding.test.engagement.product.name] = { 'path': reverse('view_product_findings', args=(finding.test.engagement.product.id, )), 'Critical': 0, 'High': 0, 'Medium': 0, 'Low': 0, 'Info': 0, 'Total': 0 } in_period_details[finding.test.engagement.product.name][ finding.severity] += 1 in_period_details[finding.test.engagement.product.name]['Total'] += 1 for finding in accepted_findings: if finding.test.engagement.product.name not in accepted_in_period_details: accepted_in_period_details[ finding.test.engagement.product.name] = { 'path': reverse('accepted_findings') + '?test__engagement__product=' + str(finding.test.engagement.product.id), 'Critical': 0, 'High': 0, 'Medium': 0, 'Low': 0, 'Info': 0, 'Total': 0 } accepted_in_period_details[finding.test.engagement.product.name][ finding.severity] += 1 accepted_in_period_details[ finding.test.engagement.product.name]['Total'] += 1 for f in findings_closed: closed_in_period_counts[f.severity] += 1 closed_in_period_counts['Total'] += 1 if f.test.engagement.product.name not in closed_in_period_details: closed_in_period_details[f.test.engagement.product.name] = { 'path': reverse('closed_findings') + '?test__engagement__product=' + str(f.test.engagement.product.id), 'Critical': 0, 'High': 0, 'Medium': 0, 'Low': 0, 'Info': 0, 'Total': 0 } closed_in_period_details[f.test.engagement.product.name][ f.severity] += 1 closed_in_period_details[f.test.engagement.product.name]['Total'] += 1 punchcard = list() ticks = list() highest_count = 0 if 'view' in request.GET and 'dashboard' == request.GET['view']: punchcard, ticks, highest_count = get_punchcard_data( findings.qs, weeks_between, start_date) page_name = (get_system_setting('team_name')) + " Metrics" template = 'dojo/dashboard-metrics.html' add_breadcrumb(title=page_name, top_level=not len(request.GET), request=request) return render( request, template, { 'name': page_name, 'start_date': start_date, 'end_date': end_date, 'findings': findings, 'opened_per_month': monthly_counts['opened_per_period'], 'active_per_month': monthly_counts['active_per_period'], 'opened_per_week': weekly_counts['opened_per_period'], 'accepted_per_month': monthly_counts['accepted_per_period'], 'accepted_per_week': weekly_counts['accepted_per_period'], 'top_ten_products': top_ten, 'age_detail': age_detail, 'in_period_counts': in_period_counts, 'in_period_details': in_period_details, 'accepted_in_period_counts': accepted_findings_counts, 'accepted_in_period_details': accepted_in_period_details, 'closed_in_period_counts': closed_in_period_counts, 'closed_in_period_details': closed_in_period_details, 'punchcard': punchcard, 'ticks': ticks, 'highest_count': highest_count, 'show_pt_filter': show_pt_filter, })
def with_last_modified(self): return self.annotate(modified=Max( Case(When(entries__is_published=True, then='entries__created'))) ).exclude(modified=None).order_by('-modified')
def analisis(request): data = {} check = Personal.objects.get(usuario_personal=request.user) respuesta = Choice.objects.filter(usuario_choice__company=check.company) print(respuesta) if Choice.objects.filter( usuario_choice__usuario_personal=request.user).exists() == True: respuesta2 = Question.objects.all().values('type').annotate( total=Count("choice", filter=Q(choice__company=check.company)), total_true=Sum( Case(When(choice__company=check.company, choice__answer=True, then=1.0), default=0, output_field=FloatField())), total_false=Sum( Case(When(choice__company=check.company, choice__answer=False, then=1.0), default=0, output_field=FloatField()))) csv = open("Reporte.csv", "w") csv.write( "Dominio; Total verdaderas; Total Falsas; Porcentaje Cumplimiento; Porcentaje no cumplimiento; Recomendaciones\n" ) for x in respuesta2: print(x) lista = Analisis() lista.type = x['type'] csv.write(str(x['type']) + ";") lista.total_t = x['total_true'] csv.write(str(x['total_true']) + ";") lista.total_f = x['total_false'] csv.write(str(x['total_false']) + ";") lista.percentaje_t = (x['total_true']) / x['total'] * 100 lista.percentaje_t = int(lista.percentaje_t) csv.write(str(int(lista.percentaje_t)) + ";") lista.percentaje_f = (x['total_false']) / x['total'] * 100 lista.percentaje_f = int(lista.percentaje_f) csv.write(str(int(lista.percentaje_f)) + ";") lista.save() if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D1": csv.write( "Se recomienda la realización de una política de seguridad informática, que debe contener los conceptos de seguridad de la información, una estructura para establecer los objetivos y las formas de control y el compromiso de la dirección con políticas.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D2": csv.write( "Se recomienda establecer una estructura para implementar la seguridad de la información en una empresa y de esta manera gestionarla de manera adecuada. Para ello, las actividades de seguridad de la información deben ser coordinadas por representantes de la organización que deben tener responsabilidad bien definidas y proteger las informaciones de carácter confidencial.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D3": csv.write( "Se recomienda identificar y clasificar los activos, de modo que un inventario pueda ser estructurado y posteriormente mantenido. Además, deben seguir reglas documentadas, que definen que tipo de uso se permite con dichos activos.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D4": csv.write( "Se recomienda la revisión de seguridad de acuerdo con políticas y procedimiento establecidos por la organización o por otra parte que los empleados de la organización reciban entrenamiento adecuado de seguridad correspondiente a sus funciones.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D5": csv.write( "Se recomienda que los equipos y instalaciones de procesamiento de información critica o sensible deben mantenerse en áreas seguras, con niveles y controles de acceso apropiados, incluyendo protección contra amenazas físicas y ambientales.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D6": csv.write( "Se recomienda que los procedimientos y responsabilidades por la gestión y operación de todos los recursos de procesamiento de la información estén definidos. Esto incluye la gestión de servicio tercerizados, la planificación de recurso del sistema para minimizar el riesgo de fallas, la creación de procedimientos para la generación de copias de seguridad y su recuperación.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D7": csv.write( "Se recomienda que los recursos de procesamiento de la información y los procesos de negocios deben ser controlado con base en los requisitos de negocio y en la seguridad de la información. Debe garantizarse el acceso de cada usuario autorizado y prevenido el acceso no autorizados a los sistema de información de manera que evite daños a documentos y recursos de procesamiento de la información que estén fuera de alcance de cualquiera.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D8": csv.write( "Se recomienda que los requisitos de seguridad de los sistema de información debe ser identificados y acordados antes de su desarrollo y/o de su implementación, para que así puedan ser protegidos para el mantenimiento de su confidencialidad, autenticidad o integridad por medio criptográficos.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D9": csv.write( "Se recomienda que los procedimientos formales de registro y escalonamiento deben ser establecidos y los empleados, proveedores y terceros deber ser conscientes de los procedimientos para notificar los eventos de seguridad de la información para asegurar que se comuniquen lo más rápido posible y corregidos en tiempo hábil.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D10": csv.write( "Se recomienda que los planes de continuidad del negocio deben ser desarrollados e implementados, con el fin de impedir la interrupción de las actividades del negocio y asegurar que las operaciones esenciales sean rápidamente recuperadas.\n" ) if lista.percentaje_t < 50 and lista.percentaje_t >= 0 and lista.type == "D11": csv.write( "Se recomienda realizar una revisión para evitar la violación de cualquier ley criminal o civil, garantizando estatutos, regulación u obligaciones contractuales y de cualesquiera requisitos de seguridad de la información. En caso necesario, la empresa puede contratar una consultoría especializada, para que verifique su cumplimiento y adherencia a los requisitos legales y reglamentarios.\n" ) else: csv.write("\n") csv.close() data['object_list'] = Analisis.objects.all().order_by('-id') data['object_list'] = data['object_list'][0:11] data['object_list_2'] = data['object_list'][12:23] return render(request, "analisis.html", data)
def get_queryset(self): param = self.request.query_params request_id = param.get('request_id', None) # request_by = param.get('request_by', None) request_status = param.get('request_status', None) request_type = param.get('request_type', None) query_surname = param.get('surname', None) query_given_name = param.get('given_name', None) section = param.get('section', None) only_todo = param.get('only_todo', None) account_type = param.get('account_type', None) uam_id = param.get('uam_id', None) qs = super().get_queryset() if uam_id: qs = qs.filter(related_user__uam_id=uam_id) if request_type: if (request_type == 'CreateAccount'): qs = qs.instance_of(CreateAccountRequest) elif (request_type == 'UpdateAccount'): qs = qs.instance_of(UpdateAccountRequest) elif (request_type == 'DeleteAccount'): qs = qs.instance_of(DeleteAccountRequest) elif (request_type == 'ResetPassword'): qs = qs.instance_of(ResetPasswordRequest) elif (request_type == 'DisableAccount'): qs = qs.instance_of(DisableAccountRequest) elif (request_type == 'ReenableAccount'): qs = qs.instance_of(EnableAccountRequest) if only_todo: qs = qs.filter(self._get_todo_criteria()) if request_id: qs = qs.filter(request_id__contains=request_id) if request_status: qs = qs.filter(status=request_status) if query_surname: name_list = query_surname.split() for name in name_list: qs = qs.filter(query_surname__contains=name) if query_given_name: name_list = query_given_name.split() for name in name_list: qs = qs.filter(query_given_name__contains=name) if section: qs = qs.filter(section=section) if account_type: qs = qs.annotate(account_type_out=Case( # When(instance_of=CreateAccountRequest, then=F('accountrequest__account_type')), # When(instance_of=UpdateAccountRequest, then=F('accountrequest__account_type')), When(instance_of=AccountRequest, then=F('accountrequest__account_type')), default=F('related_user__account_type'), output_field=models.IntegerField())) # qs = qs.filter(related_user__account_type=account_type) qs = qs.filter(account_type_out=account_type) qs = qs.values( 'id', 'request_id', 'query_surname', 'query_given_name', 'section__id', 'section__code', 'query_post_title', 'submission_date', 'query_request_type_desc', 'query_status_desc', 'creation_date', 'last_modification_date', ) return qs
def getClassTypeMonthlyData(year=None, series=None, typeLimit=None): ''' To break out by class type and month simultaneously, get data for each series and aggregate by class type. ''' # If no year specified, report current year to date. if not year: year = timezone.now().year role_list = DanceRole.objects.distinct() # Report data on all students registered unless otherwise specified if series not in ['registrations','studenthours'] and series not in [x.pluralName for x in role_list]: series = 'registrations' when_all = { 'eventregistration__dropIn': False, 'eventregistration__cancelled': False, } annotations = {'registrations': Sum(Case(When(Q(**when_all),then=1),output_field=FloatField()))} for this_role in role_list: annotations[this_role.pluralName] = Sum(Case(When(Q(Q(**when_all) & Q(eventregistration__role=this_role)),then=1),output_field=FloatField())) series_counts = Series.objects.filter(year=year).annotate(**annotations).annotate(studenthours=F('duration') * F('registrations')).select_related('classDescription__danceTypeLevel__danceType','classDescription__danceTypeLevel') # If no limit specified on number of types, then do not aggregate dance types. # Otherwise, report the typeLimit most common types individually, and report all # others as other. This gets tuples of names and counts dance_type_counts = [(dance_type,count) for dance_type,count in Counter([x.classDescription.danceTypeLevel for x in series_counts]).items()] dance_type_counts.sort(key=lambda k: k[1],reverse=True) if typeLimit: dance_types = [x[0] for x in dance_type_counts[:typeLimit]] else: dance_types = [x[0] for x in dance_type_counts] results = [] # Month by month, calculate the result data for month in range(1,13): this_month_result = { 'month': month, 'month_name': month_name[month], } for dance_type in dance_types: this_month_result[dance_type.__str__()] = \ series_counts.filter(classDescription__danceTypeLevel=dance_type,month=month).aggregate(Sum(series))['%s__sum' % series] if typeLimit: this_month_result['Other'] = \ series_counts.filter(month=month).exclude(classDescription__danceTypeLevel__in=dance_types).aggregate(Sum(series))['%s__sum' % series] results.append(this_month_result) # Now get totals totals_result = { 'month': 'Totals', 'month_name': 'totals', } for dance_type in dance_types: totals_result[dance_type.__str__()] = \ series_counts.filter(classDescription__danceTypeLevel=dance_type).aggregate(Sum(series))['%s__sum' % series] if typeLimit: totals_result['Other'] = \ series_counts.exclude(classDescription__danceTypeLevel__in=dance_types).aggregate(Sum(series))['%s__sum' % series] results.append(totals_result) return results
'creator', 'creatoremail', 'creatorwebsite', 'custom_country_filter', 'key_code', ], 'startrun': prize_run_fields, 'endrun': prize_run_fields, 'category': ['name'], }, } EVENT_DONATION_AGGREGATE_FILTER = Case( When(EventAggregateFilter, then=F('donation__amount')), output_field=DecimalField(decimal_places=2), ) annotations = { 'event': { 'amount': Coalesce(Sum(EVENT_DONATION_AGGREGATE_FILTER), 0), 'count': Count(EVENT_DONATION_AGGREGATE_FILTER), 'max': Coalesce(Max(EVENT_DONATION_AGGREGATE_FILTER), 0), 'avg': Coalesce(Avg(EVENT_DONATION_AGGREGATE_FILTER), 0), }, 'prize': { 'numwinners': Count( Case(When(PrizeWinnersFilter, then=1), output_field=IntegerField())), },
def getClassCountHistogramData(cohortStart=None,cohortEnd=None): # Note: Bins are inclusive, and 99999 means 'or more'. That should last us awhile. bins = [ (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,15), (16,20), (21,99999)] when_all = { 'eventregistration__dropIn': False, 'eventregistration__cancelled':False, } cohortFilters = {} roleFilters = {} if cohortStart: cohortFilters['eventregistration__event__startTime__min__gte'] = cohortStart roleFilters['eventregistration__event__startTime__gte'] = cohortStart if cohortEnd: cohortFilters['eventregistration__event__startTime__min__lte'] = cohortEnd roleFilters['eventregistration__event__startTime__lte'] = cohortEnd role_list = DanceRole.objects.filter(**roleFilters).distinct() annotations = { 'eventregistration__event__startTime__min': Min('eventregistration__event__startTime'), 'registrations': Sum(Case(When(Q(**when_all),then=1),output_field=IntegerField())), } for this_role in role_list: annotations[this_role.pluralName] = Sum(Case(When(Q(Q(**when_all) & Q(eventregistration__role=this_role)),then=1),output_field=IntegerField())) customers = Customer.objects.annotate(**annotations).filter(**cohortFilters).distinct() totalCustomers = customers.filter(registrations__gt=0).count() totalClasses = [x.registrations for x in customers if x.registrations] totalClasses.sort() totalsByRole = {} for this_role in role_list: totalsByRole[this_role.pluralName] = { 'customers': customers.filter(**{this_role.pluralName + '__gt': 0}).count(), 'classes': [getattr(x,this_role.pluralName,None) for x in customers if getattr(x,this_role.pluralName,None)], } totalsByRole[this_role.pluralName]['classes'].sort() results = {} lastAll = 0 lastByRole = {this_role.pluralName:0 for this_role in role_list} iByRole = {} for this_bin in bins: range_max = this_bin[1] if this_bin[0] == this_bin[1]: this_label = '%s' % this_bin[0] elif this_bin[1] == 99999: this_label = '%s or more' % this_bin[0] else: this_label = '%s-%s' % this_bin i_all = bisect(totalClasses,range_max,lastAll) iByRole = { this_role.pluralName:bisect(totalsByRole[this_role.pluralName]['classes'],range_max,lastByRole[this_role.pluralName]) for this_role in role_list } # Note: These are not translated because the chart Javascript looks for these keys results.update({ this_label: { '# Students': (i_all - lastAll), '% Students': 100 * (i_all - lastAll) / float(totalCustomers), 'bin': this_bin, }, }) for this_role in role_list: results[this_label].update({ '# ' + this_role.pluralName: (iByRole[this_role.pluralName] - lastByRole[this_role.pluralName]), '% ' + this_role.pluralName: 100 * ( iByRole[this_role.pluralName] - lastByRole[this_role.pluralName] ) / float(totalsByRole[this_role.pluralName]['customers']), }) lastAll = i_all lastByRole = {this_role.pluralName:iByRole[this_role.pluralName] for this_role in role_list} return results
def get(self, request, organization): """ Retrieve an Organization's Dashboards ````````````````````````````````````` Retrieve a list of dashboards that are associated with the given organization. If on the first page, this endpoint will also include any pre-built dashboards that haven't been replaced or removed. :pparam string organization_slug: the slug of the organization the dashboards belongs to. :qparam string query: the title of the dashboard being searched for. :auth: required """ if not features.has("organizations:dashboards-basic", organization, actor=request.user): return Response(status=404) dashboards = Dashboard.objects.filter( organization_id=organization.id).select_related("created_by") query = request.GET.get("query") if query: dashboards = dashboards.filter(title__icontains=query) prebuilt = Dashboard.get_prebuilt_list(organization, query) sort_by = request.query_params.get("sort") if sort_by and sort_by.startswith("-"): sort_by, desc = sort_by[1:], True else: desc = False if sort_by == "title": order_by = [ "-title" if desc else "title", "-date_added", ] elif sort_by == "dateCreated": order_by = "-date_added" if desc else "date_added" elif sort_by == "mostPopular": order_by = [ "visits" if desc else "-visits", "-date_added", ] elif sort_by == "recentlyViewed": order_by = "last_visited" if desc else "-last_visited" elif sort_by == "mydashboards": order_by = [ Case(When(created_by_id=request.user.id, then=-1), default="created_by_id"), "-date_added", ] else: order_by = "title" if not isinstance(order_by, list): order_by = [order_by] dashboards = dashboards.order_by(*order_by) list_serializer = DashboardListSerializer() def handle_results(results): serialized = [] dashboards = [] for item in results: if isinstance(item, dict): cloned = item.copy() widgets = cloned.pop("widgets", []) cloned["widgetDisplay"] = [ w["displayType"] for w in widgets ] serialized.append(cloned) else: dashboards.append(item) serialized.extend( serialize(dashboards, request.user, serializer=list_serializer)) return serialized return self.paginate( request=request, sources=[prebuilt, dashboards], paginator_cls=ChainPaginator, on_results=handle_results, )