def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [{
"provider":
Provider.PROVIDER_GCP,
"annotations": {}, # Annotations that should always happen
"group_by_annotations": {
"account": {
"account": "account_id"
},
"project": {
"project": "project_id"
},
"service": {
"service": "service_alias"
},
}, # Annotations that should happen depending on group_by values
"end_date":
"usage_end",
"filters": {
"account": {
"field": "account_id",
"operation": "icontains"
},
"region": {
"field": "region",
"operation": "icontains"
},
"service": [
{
"field": "service_alias",
"operation": "icontains",
"composition_key": "service_filter"
},
{
"field": "service_id",
"operation": "icontains",
"composition_key": "service_filter"
},
],
"project": [
{
"field": "project_name",
"operation": "icontains",
"composition_key": "project_filter"
},
{
"field": "project_id",
"operation": "icontains",
"composition_key": "project_filter"
},
],
},
"group_by_options": ["account", "region", "service", "project"],
"tag_column":
"tags",
"report_type": {
"costs": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("unblended_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("unblended_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
},
"aggregate_key": "unblended_cost",
"annotations": {
"infra_raw":
Sum("unblended_cost"),
"infra_usage":
Value(0, output_field=DecimalField()),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_raw":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField()))),
"cost_usage":
Value(0, output_field=DecimalField()),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency"), Value("USD")),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"delta_key": {
# cost goes to cost_total
"cost_total":
Sum(
ExpressionWrapper(F("unblended_cost") +
F("markup_cost"),
output_field=DecimalField()))
},
"filter": [{}],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"sum_columns": ["cost_total", "infra_total", "sup_total"],
"default_ordering": {
"cost_total": "desc"
},
},
"instance_type": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("unblended_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("unblended_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"usage":
Sum("usage_amount"),
},
"aggregate_key":
"usage_amount",
"annotations": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("unblended_cost"),
"infra_usage":
Value(0, output_field=DecimalField()),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("unblended_cost"),
"cost_usage":
Value(0, output_field=DecimalField()),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency"), Value("USD")),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("hour")),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [
{
"field": "instance_type",
"operation": "isnull",
"parameter": False
},
{
"field": "unit",
"operation": "exact",
"parameter": "hour"
},
{
"field": "sku_alias",
"operation": "contains",
"parameter": "Instance Core running"
},
],
"group_by": ["instance_type"],
"cost_units_key":
"currency",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"hour",
"sum_columns":
["usage", "cost_total", "sup_total", "infra_total"],
"default_ordering": {
"usage": "desc"
},
},
"tags": {
"default_ordering": {
"cost_total": "desc"
}
},
},
"start_date":
"usage_start",
"tables": {
"query": GCPCostEntryLineItemDailySummary
},
}]
self.views = {
"costs": {
"default": GCPCostSummary,
("account", ): GCPCostSummaryByAccount,
("region", ): GCPCostSummaryByRegion,
("account", "region"): GCPCostSummaryByRegion,
("service", ): GCPCostSummaryByService,
("account", "service"): GCPCostSummaryByService,
("project", ): GCPCostSummaryByProject,
("account", "project"): GCPCostSummaryByProject,
},
"instance-type": {
"default": GCPComputeSummary,
("account", ): GCPComputeSummaryByAccount,
("region", ): GCPComputeSummaryByRegion,
("account", "region"): GCPComputeSummaryByRegion,
("service", ): GCPComputeSummaryByService,
("account", "service"): GCPComputeSummaryByService,
("project", ): GCPComputeSummaryByProject,
("account", "project"): GCPComputeSummaryByProject,
},
}
super().__init__(provider, report_type)
def detail_asset_view(request, asset_id):
asset = get_object_or_404(Asset.objects.for_user(request.user),
id=asset_id)
findings = Finding.objects.filter(asset=asset).annotate(
severity_numm=Case(When(severity="critical", then=Value("0")),
When(severity="high", then=Value("1")),
When(severity="medium", then=Value("2")),
When(severity="low", then=Value("3")),
When(severity="info", then=Value("4")),
default=Value("1"),
output_field=CharField())).annotate(
scope_list=ArrayAgg('scopes__name')).order_by(
'severity_numm', 'type', 'updated_at').only(
"severity", "status", "engine_type",
"risk_info", "vuln_refs", "title", "id",
"solution", "updated_at", "type")
findings_stats = {
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0,
'new': 0,
'ack': 0,
'cvss_gte_7': 0
}
engines_stats = {}
references = {}
engine_scopes = {}
for engine_scope in EnginePolicyScope.objects.all():
engine_scopes.update({
engine_scope.name: {
'priority': engine_scope.priority,
'id': engine_scope.id,
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0
}
})
for finding in findings:
findings_stats['total'] = findings_stats.get('total', 0) + 1
if finding.status not in ["false-positive", "duplicate"]:
findings_stats[finding.severity] = findings_stats.get(
finding.severity, 0) + 1
if finding.status == 'new':
findings_stats['new'] = findings_stats.get('new', 0) + 1
if finding.status == 'ack':
findings_stats['ack'] = findings_stats.get('ack', 0) + 1
for fs in finding.scope_list:
if fs is not None:
c = engine_scopes[fs]
engine_scopes[fs].update({
'total':
c['total'] + 1,
finding.severity:
c[finding.severity] + 1
})
if finding.engine_type not in engines_stats.keys():
engines_stats.update({finding.engine_type: 0})
engines_stats[finding.engine_type] = engines_stats.get(
finding.engine_type, 0) + 1
if finding.risk_info["cvss_base_score"] > 7.0:
findings_stats['cvss_gte_7'] = findings_stats.get('cvss_gte_7',
0) + 1
if bool(finding.vuln_refs):
for ref in finding.vuln_refs.keys():
if ref not in references.keys():
references.update({ref: []})
tref = references[ref]
if type(finding.vuln_refs[ref]) is list:
tref = tref + finding.vuln_refs[ref]
else:
tref.append(finding.vuln_refs[ref])
references.update({ref: tref})
# Show only unique references
references_cleaned = {}
for ref in references:
references_cleaned.update({ref: sorted(list(set(references[ref])))})
# Related scans
scans_stats = {
'performed':
Scan.objects.filter(assets__in=[asset]).count(),
'defined':
ScanDefinition.objects.filter(assets_list__in=[asset]).count(),
'periodic':
ScanDefinition.objects.filter(assets_list__in=[asset],
scan_type='periodic').count(),
'ondemand':
ScanDefinition.objects.filter(assets_list__in=[asset],
scan_type='single').count(),
'running':
Scan.objects.filter(
assets__in=[asset],
status='started').count(), # bug: a regrouper par assets
'lasts':
Scan.objects.filter(assets__in=[asset]).order_by('-updated_at')[:3]
}
asset_groups = list(
AssetGroup.objects.for_user(
request.user).filter(assets__in=[asset]).only("id"))
scan_defs = ScanDefinition.objects.filter(
Q(assets_list__in=[asset])
| Q(assetgroups_list__in=asset_groups)).annotate(
engine_type_name=F('engine_type__name')).annotate(
scan_set_count=Count('scan')).order_by('-updated_at')
scans = Scan.objects.filter(assets__in=[asset]).values(
"id", "title", "status", "summary", "updated_at").annotate(
engine_type_name=F('engine_type__name')).order_by('-updated_at')
# Investigation links
investigation_links = []
DEFAULT_LINKS = copy.deepcopy(ASSET_INVESTIGATION_LINKS)
for i in DEFAULT_LINKS:
if asset.type in i["datatypes"]:
if "link" in [*i]:
i["link"] = i["link"].replace("%asset%", asset.value)
investigation_links.append(i)
# Calculate automatically risk grade
asset.calc_risk_grade()
asset_risk_grade = {
'now': asset.get_risk_grade(),
'day_ago': asset.get_risk_grade(history=1),
'week_ago': asset.get_risk_grade(history=7),
'month_ago': asset.get_risk_grade(history=30),
'year_ago': asset.get_risk_grade(history=365)
}
return render(
request, 'details-asset.html', {
'asset': asset,
'asset_risk_grade': asset_risk_grade,
'findings': findings,
'findings_stats': findings_stats,
'references': references_cleaned,
'scans_stats': scans_stats,
'scans': scans,
'scan_defs': scan_defs,
'investigation_links': investigation_links,
'engines_stats': engines_stats,
'asset_scopes': list(engine_scopes.items())
})
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [
{
"provider": Provider.PROVIDER_OCP,
"annotations": {"cluster": "cluster_id", "project": "namespace"},
"end_date": "usage_end",
"filters": {
"project": {"field": "namespace", "operation": "icontains"},
"cluster": [
{"field": "cluster_alias", "operation": "icontains", "composition_key": "cluster_filter"},
{"field": "cluster_id", "operation": "icontains", "composition_key": "cluster_filter"},
],
"pod": {"field": "pod", "operation": "icontains"},
"node": {"field": "node", "operation": "icontains"},
"infrastructures": {
"field": "cluster_id",
"operation": "exact",
"custom": ProviderAccessor(Provider.PROVIDER_OCP).infrastructure_key_list,
},
},
"group_by_options": ["cluster", "project", "node"],
"tag_column": "pod_labels",
"report_type": {
"costs": {
"tables": {"query": OCPUsageLineItemDailySummary},
"aggregates": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("monthly_cost"), Value(0, output_field=DecimalField()))
),
"infrastructure_cost": Sum(
Coalesce(F("infra_cost"), Value(0, output_field=DecimalField()))
),
"derived_cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("monthly_cost"), Value(0, output_field=DecimalField()))
),
"markup_cost": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
},
"default_ordering": {"cost": "desc"},
"annotations": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("monthly_cost"), Value(0, output_field=DecimalField()))
),
"infrastructure_cost": Sum(
Coalesce(F("infra_cost"), Value(0, output_field=DecimalField()))
),
"derived_cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("monthly_cost"), Value(0, output_field=DecimalField()))
),
"markup_cost": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Value("USD", output_field=CharField()),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
},
"capacity_aggregate": {},
"delta_key": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("monthly_cost"), Value(0, output_field=DecimalField()))
)
},
"filter": [{}],
"cost_units_key": "USD",
"sum_columns": ["cost", "infrastructure_cost", "derived_cost", "markup_cost"],
},
"costs_by_project": {
"tables": {"query": OCPUsageLineItemDailySummary},
"aggregates": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("project_infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infrastructure_cost": Sum(
Coalesce(F("project_infra_cost"), Value(0, output_field=DecimalField()))
),
"derived_cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
),
"markup_cost": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
},
"default_ordering": {"cost": "desc"},
"annotations": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("project_infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infrastructure_cost": Sum(
Coalesce(F("project_infra_cost"), Value(0, output_field=DecimalField()))
),
"derived_cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
),
"markup_cost": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Value("USD", output_field=CharField()),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
},
"capacity_aggregate": {},
"delta_key": {
"cost": Sum(
Coalesce(F("pod_charge_cpu_core_hours"), Value(0, output_field=DecimalField()))
+ Coalesce(
F("pod_charge_memory_gigabyte_hours"), Value(0, output_field=DecimalField())
)
+ Coalesce(
F("persistentvolumeclaim_charge_gb_month"), Value(0, output_field=DecimalField())
)
+ Coalesce(F("project_infra_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
)
},
"filter": [{}],
"cost_units_key": "USD",
"sum_columns": ["cost", "infrastructure_cost", "derived_cost", "markup_cost"],
},
"cpu": {
"aggregates": {
"cost": Sum("pod_charge_cpu_core_hours"),
"usage": Sum("pod_usage_cpu_core_hours"),
"request": Sum("pod_request_cpu_core_hours"),
"limit": Sum("pod_limit_cpu_core_hours"),
"infrastructure_cost": Sum(Value(0, output_field=DecimalField())),
"derived_cost": Sum("pod_charge_cpu_core_hours"),
},
"capacity_aggregate": {"capacity": Max("cluster_capacity_cpu_core_hours")},
"default_ordering": {"usage": "desc"},
"annotations": {
"usage": Sum("pod_usage_cpu_core_hours"),
"request": Sum("pod_request_cpu_core_hours"),
"limit": Sum("pod_limit_cpu_core_hours"),
"capacity": {
"total": Max("total_capacity_cpu_core_hours"),
"cluster": Max("cluster_capacity_cpu_core_hours"),
},
"cost": Sum("pod_charge_cpu_core_hours"),
"infrastructure_cost": Value(0, output_field=DecimalField()),
"derived_cost": Sum("pod_charge_cpu_core_hours"),
"cost_units": Value("USD", output_field=CharField()),
"usage_units": Value("Core-Hours", output_field=CharField()),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
},
"delta_key": {
"usage": Sum("pod_usage_cpu_core_hours"),
"request": Sum("pod_request_cpu_core_hours"),
"cost": Sum("pod_charge_cpu_core_hours"),
},
"filter": [{"field": "data_source", "operation": "exact", "parameter": "Pod"}],
"cost_units_key": "USD",
"usage_units_key": "Core-Hours",
"sum_columns": ["usage", "request", "limit", "infrastructure_cost", "derived_cost", "cost"],
},
"memory": {
"aggregates": {
"usage": Sum("pod_usage_memory_gigabyte_hours"),
"request": Sum("pod_request_memory_gigabyte_hours"),
"limit": Sum("pod_limit_memory_gigabyte_hours"),
"cost": Sum("pod_charge_memory_gigabyte_hours"),
"infrastructure_cost": Sum(Value(0, output_field=DecimalField())),
"derived_cost": Sum("pod_charge_memory_gigabyte_hours"),
},
"capacity_aggregate": {"capacity": Max("cluster_capacity_memory_gigabyte_hours")},
"default_ordering": {"usage": "desc"},
"annotations": {
"usage": Sum("pod_usage_memory_gigabyte_hours"),
"request": Sum("pod_request_memory_gigabyte_hours"),
"limit": Sum("pod_limit_memory_gigabyte_hours"),
"capacity": {
"total": Max("total_capacity_memory_gigabyte_hours"),
"cluster": Max("cluster_capacity_memory_gigabyte_hours"),
},
"cost": Sum("pod_charge_memory_gigabyte_hours"),
"infrastructure_cost": Value(0, output_field=DecimalField()),
"derived_cost": Sum("pod_charge_memory_gigabyte_hours"),
"cost_units": Value("USD", output_field=CharField()),
"usage_units": Value("GB-Hours", output_field=CharField()),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
},
"delta_key": {
"usage": Sum("pod_usage_memory_gigabyte_hours"),
"request": Sum("pod_request_memory_gigabyte_hours"),
"cost": Sum("pod_charge_memory_gigabyte_hours"),
},
"filter": [{"field": "data_source", "operation": "exact", "parameter": "Pod"}],
"cost_units_key": "USD",
"usage_units_key": "GB-Hours",
"sum_columns": ["usage", "request", "limit", "infrastructure_cost", "derived_cost", "cost"],
},
"volume": {
"tag_column": "volume_labels",
"aggregates": {
"usage": Sum("persistentvolumeclaim_usage_gigabyte_months"),
"request": Sum("volume_request_storage_gigabyte_months"),
"cost": Sum("persistentvolumeclaim_charge_gb_month"),
"infrastructure_cost": Sum(Value(0, output_field=DecimalField())),
"derived_cost": Sum("persistentvolumeclaim_charge_gb_month"),
},
"capacity_aggregate": {"capacity": Sum("persistentvolumeclaim_capacity_gigabyte_months")},
"default_ordering": {"usage": "desc"},
"annotations": {
"usage": Sum("persistentvolumeclaim_usage_gigabyte_months"),
"request": Sum("volume_request_storage_gigabyte_months"),
"capacity": {
"total": Sum("persistentvolumeclaim_capacity_gigabyte_months"),
"cluster": Sum("persistentvolumeclaim_capacity_gigabyte_months"),
},
"cost": Sum("persistentvolumeclaim_charge_gb_month"),
"infrastructure_cost": Value(0, output_field=DecimalField()),
"derived_cost": Sum("persistentvolumeclaim_charge_gb_month"),
"cost_units": Value("USD", output_field=CharField()),
"usage_units": Value("GB-Mo", output_field=CharField()),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
},
"delta_key": {
"usage": Sum("persistentvolumeclaim_usage_gigabyte_months"),
"request": Sum("volume_request_storage_gigabyte_months"),
"cost": Sum("persistentvolumeclaim_charge_gb_month"),
},
"filter": [{"field": "data_source", "operation": "exact", "parameter": "Storage"}],
"cost_units_key": "USD",
"usage_units_key": "GB-Mo",
"sum_columns": ["usage", "request", "infrastructure_cost", "derived_cost", "cost"],
},
"tags": {"default_ordering": {"cost": "desc"}},
},
"start_date": "usage_start",
"tables": {"query": OCPUsageLineItemDailySummary},
}
]
super().__init__(provider, report_type)
def test_array_agg_filter(self):
values = AggregateTestModel.objects.aggregate(
arrayagg=ArrayAgg('integer_field', filter=Q(integer_field__gt=0)),
)
self.assertEqual(values, {'arrayagg': [1, 2]})
def test_array_agg_distinct_true(self):
values = AggregateTestModel.objects.aggregate(arrayagg=ArrayAgg('char_field', distinct=True))
self.assertEqual(sorted(values['arrayagg']), ['Bar', 'Foo'])
def test_array_agg_charfield(self):
values = AggregateTestModel.objects.aggregate(arrayagg=ArrayAgg('char_field'))
self.assertEqual(values, {'arrayagg': ['Foo1', 'Foo2', 'Foo4', 'Foo3']})
def test_array_agg_integerfield_ordering(self):
values = AggregateTestModel.objects.aggregate(
arrayagg=ArrayAgg('integer_field', ordering=F('integer_field').desc())
)
self.assertEqual(values, {'arrayagg': [2, 1, 0, 0]})
def render_tree_v3(request):
Tree_class=Treeclass()
phylogeny_input, branches, ttype, total, legend, box, Additional_info, buttons, proteins=Tree_class.Prepare_file(request)
if phylogeny_input == 'too big':
return render(request, 'phylogenetic_trees/too_big.html')
if phylogeny_input == 'More_prots':
return render(request, 'phylogenetic_trees/warning.html')
# if ttype == '1':
# float(total)/4*100
# else:
# count = 1900 - 1400/math.sqrt(float(total))
#protein_data = []
#
#FIXME remove
# import random
# for pc in proteins:
# v = {}
# p = pc.protein
# v['name'] = p.entry_name
# v['GPCR_class'] = p.family.parent.parent.parent.name
# v['selectivity'] = ["Gq/G11 family"]
# v['ligand_type'] = p.family.parent.parent.name
# v['coverage'] = random.uniform(0, 1)
# v['receptor_page'] = ''
# print(v)
# protein_data.append(v)
request.session['Tree'] = Tree_class
# output dictionary
data = {}
data['tree'] = Tree_class.phylip.replace('\n', '')
# context['protein_data'] = protein_data
protein_entries = []
for pc in proteins:
protein_entries.append(pc.protein.entry_name)
# load all
cluster_method = 0
# Collect structure annotations
protein_annotations = {}
# Grab all annotations and all the ligand role when present in aggregates
# NOTE: we can probably remove the parent step and go directly via family in the query
annotations = Protein.objects.filter(entry_name__in=protein_entries) \
.values_list('entry_name', 'name', 'family__parent__name', 'family__parent__parent__name', 'family__parent__parent__parent__name', 'family__slug', 'name')
protein_slugs = set()
for an in annotations:
protein_annotations[an[0]] = list(an[1:])
# add slug to lists
slug = protein_annotations[an[0]][4]
protein_slugs.add(slug)
data['annotations'] = protein_annotations
# Grab G-protein coupling profile for all receptors covered by the selection
# TODO: make general cache(s) for these kinds of data
selectivitydata = {}
coupling = ProteinGProteinPair.objects.filter(protein__family__slug__in=protein_slugs, source="GuideToPharma").values_list('protein__family__slug', 'transduction').annotate(arr=ArrayAgg('g_protein__name'))
for pairing in coupling:
if pairing[0] not in selectivitydata:
selectivitydata[pairing[0]] = {}
selectivitydata[pairing[0]][pairing[1]] = pairing[2]
data['Gprot_coupling'] = selectivitydata
context = {}
context["data"] = json.dumps(data)
return render(request, 'phylogenetic_trees/phylo_tree.html', context)
def serialize(self, instance: Person) -> ObjectRepr:
person = (
Person.objects.filter(pk=instance.pk).values().annotate(
country_code=F("country__iso_2_code"),
country_name=F("country__name"),
# Note the use of `ArrayAgg` to denormalize and flatten many-to-many
# relationships.
workdays=ArrayAgg(
"workdays__name",
filter=Q(workdays__name__isnull=False),
distinct=True,
),
key_skills=ArrayAgg(
"key_skills__name",
filter=Q(key_skills__name__isnull=False),
distinct=True,
),
learning_interests=ArrayAgg(
"learning_interests__name",
filter=Q(learning_interests__name__isnull=False),
distinct=True,
),
networks=ArrayAgg(
"networks__name",
filter=Q(networks__name__isnull=False),
distinct=True,
),
professions=ArrayAgg(
"professions__name",
filter=Q(professions__name__isnull=False),
distinct=True,
),
additional_roles=ArrayAgg(
"additional_roles__name",
filter=Q(additional_roles__name__isnull=False),
distinct=True,
),
buildings=ArrayAgg(
"buildings__name",
filter=Q(buildings__name__isnull=False),
distinct=True,
),
roles=ArrayAgg(
Concat(
"roles__job_title",
Value(" in "),
"roles__team__name",
Case(
When(roles__head_of_team=True,
then=Value(" (head of team)")),
default=Value(""),
),
),
filter=Q(roles__isnull=False),
distinct=True,
),
groups=ArrayAgg(
"user__groups__name",
filter=Q(user__groups__name__isnull=False),
distinct=True,
),
user_permissions=ArrayAgg(
"user__user_permissions__name",
filter=Q(user__user_permissions__name__isnull=False),
distinct=True,
),
user__username=F("user__username"),
manager__slug=F("manager__slug"),
manager__full_name=(Concat("manager__first_name", Value(" "),
"manager__last_name")),
)[0])
# Encode the slug from `UUID` to `str` before returning.
person["slug"] = str(person["slug"])
person["manager__slug"] = str(person["manager__slug"])
del person["login_count"]
del person["old_country_id"]
return person
def upload_qc_pipeline_output(request):
file_path = json.loads(request.body)['file']
raw_records = parse_file(file_path, file_iter(file_path))
json_records = [dict(zip(raw_records[0], row)) for row in raw_records[1:]]
missing_columns = [
field for field in [
'seqr_id', 'data_type', 'filter_flags', 'qc_metrics_filters',
'qc_pop'
] if field not in json_records[0]
]
if missing_columns:
message = 'The following required columns are missing: {}'.format(
', '.join(missing_columns))
return create_json_response({'errors': [message]},
status=400,
reason=message)
dataset_types = {
record['data_type'].lower()
for record in json_records if record['data_type'].lower() != 'n/a'
}
if len(dataset_types) == 0:
message = 'No dataset type detected'
return create_json_response({'errors': [message]},
status=400,
reason=message)
elif len(dataset_types) > 1:
message = 'Multiple dataset types detected: {}'.format(
' ,'.join(dataset_types))
return create_json_response({'errors': [message]},
status=400,
reason=message)
elif list(dataset_types)[0] not in DATASET_TYPE_MAP:
message = 'Unexpected dataset type detected: "{}" (should be "exome" or "genome")'.format(
list(dataset_types)[0])
return create_json_response({'errors': [message]},
status=400,
reason=message)
dataset_type = DATASET_TYPE_MAP[list(dataset_types)[0]]
info_message = 'Parsed {} {} samples'.format(len(json_records),
dataset_type)
logger.info(info_message)
info = [info_message]
warnings = []
sample_ids = {record['seqr_id'] for record in json_records}
samples = Sample.objects.filter(
sample_id__in=sample_ids,
sample_type=Sample.SAMPLE_TYPE_WES
if dataset_type == 'exome' else Sample.SAMPLE_TYPE_WGS,
).exclude(individual__family__project__name__in=EXCLUDE_PROJECTS).exclude(
individual__family__project__projectcategory__name=
EXCLUDE_PROJECT_CATEGORY)
sample_individuals = {
agg['sample_id']: agg['individuals']
for agg in samples.values('sample_id').annotate(
individuals=ArrayAgg('individual_id', distinct=True))
}
sample_individual_max_loaded_date = {
agg['individual_id']: agg['max_loaded_date']
for agg in samples.values('individual_id').annotate(
max_loaded_date=Max('loaded_date'))
}
individual_latest_sample_id = {
s.individual_id: s.sample_id
for s in samples if s.loaded_date ==
sample_individual_max_loaded_date.get(s.individual_id)
}
for record in json_records:
record['individual_ids'] = list({
individual_id
for individual_id in sample_individuals.get(record['seqr_id'], [])
if individual_latest_sample_id[individual_id] == record['seqr_id']
})
missing_sample_ids = {
record['seqr_id']
for record in json_records if not record['individual_ids']
}
if missing_sample_ids:
individuals = Individual.objects.filter(
individual_id__in=missing_sample_ids
).exclude(family__project__name__in=EXCLUDE_PROJECTS).exclude(
family__project__projectcategory__name=EXCLUDE_PROJECT_CATEGORY
).exclude(sample__sample_type=Sample.SAMPLE_TYPE_WGS if dataset_type ==
'exome' else Sample.SAMPLE_TYPE_WES)
individual_db_ids_by_id = defaultdict(list)
for individual in individuals:
individual_db_ids_by_id[individual.individual_id].append(
individual.id)
for record in json_records:
if not record['individual_ids'] and len(
individual_db_ids_by_id[record['seqr_id']]) == 1:
record['individual_ids'] = individual_db_ids_by_id[
record['seqr_id']]
missing_sample_ids.remove(record['seqr_id'])
multi_individual_samples = {
record['seqr_id']: len(record['individual_ids'])
for record in json_records if len(record['individual_ids']) > 1
}
if multi_individual_samples:
logger.info('Found {} multi-individual samples from qc output'.format(
len(multi_individual_samples)))
warnings.append(
'The following {} samples were added to multiple individuals: {}'.
format(
len(multi_individual_samples), ', '.join(
sorted([
'{} ({})'.format(sample_id, count) for sample_id, count
in multi_individual_samples.items()
]))))
if missing_sample_ids:
logger.info('Missing {} samples from qc output'.format(
len(missing_sample_ids)))
warnings.append('The following {} samples were skipped: {}'.format(
len(missing_sample_ids),
', '.join(sorted(list(missing_sample_ids)))))
unknown_filter_flags = set()
unknown_pop_filter_flags = set()
inidividuals_by_population = defaultdict(list)
for record in json_records:
filter_flags = {}
for flag in json.loads(record['filter_flags']):
flag = '{}_{}'.format(flag,
dataset_type) if flag == 'coverage' else flag
flag_col = FILTER_FLAG_COL_MAP.get(flag, flag)
if flag_col in record:
filter_flags[flag] = record[flag_col]
else:
unknown_filter_flags.add(flag)
pop_platform_filters = {}
for flag in json.loads(record['qc_metrics_filters']):
flag_col = 'sample_qc.{}'.format(flag)
if flag_col in record:
pop_platform_filters[flag] = record[flag_col]
else:
unknown_pop_filter_flags.add(flag)
if filter_flags or pop_platform_filters:
Individual.objects.filter(id__in=record['individual_ids']).update(
filter_flags=filter_flags or None,
pop_platform_filters=pop_platform_filters or None)
inidividuals_by_population[
record['qc_pop'].upper()] += record['individual_ids']
for population, indiv_ids in inidividuals_by_population.items():
Individual.objects.filter(id__in=indiv_ids).update(
population=population)
if unknown_filter_flags:
message = 'The following filter flags have no known corresponding value and were not saved: {}'.format(
', '.join(unknown_filter_flags))
logger.info(message)
warnings.append(message)
if unknown_pop_filter_flags:
message = 'The following population platform filters have no known corresponding value and were not saved: {}'.format(
', '.join(unknown_pop_filter_flags))
logger.info(message)
warnings.append(message)
message = 'Found and updated matching seqr individuals for {} samples'.format(
len(json_records) - len(missing_sample_ids))
info.append(message)
logger.info(message)
return create_json_response({
'errors': [],
'warnings': warnings,
'info': info,
})
def implicit(cls, leagues=None,
locations=None,
dt_from=None,
dt_to=None,
duration_min=None,
duration_max=None,
month_days=None,
gap_days=1,
minimum_event_duration=2):
'''
Implicit events are those inferred from Session records, and not explicitly recorded as events.
They are defined as all block of contiguous sessions that have gap_days (defaulting 1) between
them. The remaining arguments are filters
:param cls:
:param leagues: A list of League PKs to restrict the events to (a Session filter)
:param locations: A list of location PKs to restrict the events to (a Session filter)
:param dt_from: The start of a datetime window (a Session filter)
:param dt_to: The end of a datetime window (a Session filter)
:param duration_min: The minimum duration (in days) of events (an Event filter)
:param duration_max: The maximum duration (in days) of events (an Event filter)
:param month_days A CSV string list of month day identifiers Entries are like Monday_N
where N is the week of the month (1-5). Any week when N is missing.
Amy day win that week when the day is missing.
:param gap_days: The gap between sessions that marks a gap between implicit Events.
:param minimum_event_duration: Sessions are recorded with a single time (nominally completion).
Single session events will have a duration of 0 as a consequence.
This, in hours expresses the average game duration of a single game
session. It's nominal and should ideally be the duration it takes
that one session to play through, which we can only estimate in any
case from the expected play time of the game. But to use that will
require a more complicated query joining the Game model,
:return: A QuerySet of events (lazy, i.e no database hits in preparing it)
'''
# Build an annotated session queury that we can use (lazy)
# Startnig with all sessions
sessions = Session.objects.all() # @UndefinedVariable
# Then applying session filters
if leagues:
sessions = sessions.filter(league__pk__in=leagues)
if locations:
sessions = sessions.filter(location__pk__in=locations)
if dt_from:
sessions = sessions.filter(date_time__gte=dt_from)
if dt_to:
sessions = sessions.filter(date_time__lte=dt_to)
# Then get the events (as all runs of session with gap_days
# between them.
# We need to anotate the sessions in two tiers alas, because we need a Window
# to get the previous essions time, and then a window to group the sessions and
# windows can't reference windows ... doh! The solution is what is to select
# from a subquery. Alas Django does not support selecting FROM a subquery (yet).
# Enter the notion of a Common Table Expression (CTE) which is essentially a
# a way of naming a query to use as the FROM target of another query. There is
# fortunately a package "django_cte" tahat adds CTE support to querysets. It's
# a tad clunky bt works.
#
# Step 1 is to do the first windowing annotation, adding the prev_date_time and
# based on it flagging the first session in each event.
sessions = sessions.order_by("date_time").annotate(
prev_date_time=Window(expression=Lag('date_time'), order_by=F('date_time').asc()),
dt_difference=ExpressionWrapper(F('date_time') - F('prev_date_time'), output_field=DurationField()),
event_start=Case(When(dt_difference__gt=timedelta(days=gap_days), then='date_time')),
)
# Step 2 we need to instantiate a CTE
sessions = With(sessions, "inner_sessions")
# Step 3 we build a new queryset (that selects from the CTE and annotate that
# The oddity here is tha django_cte requires us to call with_cte() to include
# the CTE's SQL in the new query's SQL. Go figure (I've checked the code, may
# fork and patch some time).
#
# The grouping expression is SQL esoterica, that I pilfered from:
#
# https://stackoverflow.com/a/56729571/4002633
# https://dbfiddle.uk/?rdbms=postgres_11&fiddle=0360fd313400e533cd76fbc39d0e22d3
# week
# It works because a Window that has no partition_by included, makes a single partition
# of all the row from this one to the end. Which is why we need to ensure and order_by
# clause in the Window. Ordered by date_time, a count of all the event_start values (nulls)
# are not counted, returns how many event_starts there are before this row. And so a count
# events before this row. A sneaky SQL trick. It relies on the event_start not having a
# default value (an ELSE clause) and hence defaulting to null. Count() ignores the nulls.
sessions_with_event = sessions.queryset().annotate(
event=Window(expression=Count(sessions.col.event_start), order_by=sessions.col.date_time),
# local_time=ExpressionWrapper(F('date_time__local'), output_field=DateTimeField())
)
print_SQL(sessions_with_event)
# Step 4: We have to bring players into the fold, and they are stored in Performance objects.
# Now we want to select from the from the session_events queryset joined with Performance.
# and group by events to collect session counts and player lists and player counts.
#
# WARNING: We need an explicit order_by('events') as the Performance object has a default
# ordering and if that is included, it forces one row per Perfornce obvect EVEN after
# .values('event') and .distinct() diesn't even help int hat instance (I tried). Short
# story is, use explicit ordering on the group by field (.values() field)
sessions_with_event = With(sessions_with_event, "outer_sessions")
events = (sessions_with_event
.join(Performance, session_id=sessions_with_event.col.id)
.annotate(event=sessions_with_event.col.event + 1, # Move from 0 based to 1 based
location_id=sessions_with_event.col.location_id,
game_id=sessions_with_event.col.game_id,
gap_time=sessions_with_event.col.dt_difference)
.order_by('event')
.values('event')
.annotate(start=ExpressionWrapper(Min('session__date_time__local') - timedelta(hours=minimum_event_duration), output_field=DateTimeField()),
end=Max('session__date_time__local'),
duration=F('end') - F('start'),
gap_time=Max('gap_time'),
locations=Count('location_id', distinct=True),
location_ids=ArrayAgg('location_id', distinct=True),
sessions=Count('session_id', distinct=True),
session_ids=ArrayAgg('session_id', distinct=True),
games=Count('game_id', distinct=True),
game_ids=ArrayAgg('game_id', distinct=True),
players=Count('player_id', distinct=True),
player_ids=ArrayAgg('player_id', distinct=True)
))
# PROBLEM: start and end are in UTC here. They do not use the recorded TZ of the ession datetime.
# Needs fixing!
if month_days:
daynum = {"sunday":1, "monday":2, "tuesday":2, "wednesday":4, "thursday":5, "friday":6, "saturday":7}
# Build a canonical list of days (lower case, and None's removed)
days = [d.strip().lower() for d in month_days.split(",")]
efilter = Q()
for day in days:
try:
day_filter = None
week_filter = None
# Can be of form "day", "day_n" or "n"
parts = day.split("_")
if len(parts) == 1:
if parts[0] in daynum:
day_filter = daynum[parts[0]]
elif isInt(parts[0]):
week_filter = int(parts[0])
else:
day_filter = daynum.get(parts[0], None)
week_filter = int(parts[1])
except:
raise ValueError(f"Bad month/day specifier: {day}")
# A dw filter is the day file AND the week filter
if day_filter or week_filter:
dwfilter = Q()
if day_filter:
dwfilter &= Q(start__week_day=day_filter)
if week_filter:
dwfilter &= Q(start__month_week=week_filter)
# An event filter is one dw filter OR another.
efilter |= dwfilter
# Q() if Falsey which is good
if efilter:
events = events.filter(efilter)
# Finally, apply the event filters
if duration_min: events = events.filter(duration__gte=duration_min)
if duration_max: events = events.filter(duration__lte=duration_max)
# Return a QuerySet of events (still lazy)
return events.order_by("-end")
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [{
"provider":
Provider.OCP_AWS,
"alias":
"account_alias__account_alias",
"annotations": {
"cluster": "cluster_id",
"project": "namespace",
"account": "usage_account_id",
"service": "product_code",
"az": "availability_zone",
},
"end_date":
"usage_end",
"filters": {
"project": {
"field": "namespace",
"operation": "icontains"
},
"cluster": [
{
"field": "cluster_alias",
"operation": "icontains",
"composition_key": "cluster_filter"
},
{
"field": "cluster_id",
"operation": "icontains",
"composition_key": "cluster_filter"
},
],
"node": {
"field": "node",
"operation": "icontains"
},
"account": [
{
"field": "account_alias__account_alias",
"operation": "icontains",
"composition_key": "account_filter",
},
{
"field": "usage_account_id",
"operation": "icontains",
"composition_key": "account_filter"
},
],
"service": {
"field": "product_code",
"operation": "icontains"
},
"product_family": {
"field": "product_family",
"operation": "icontains"
},
"az": {
"field": "availability_zone",
"operation": "icontains"
},
"region": {
"field": "region",
"operation": "icontains"
},
},
"group_by_options": [
"account", "service", "region", "cluster", "project", "node",
"product_family"
],
"tag_column":
"tags",
"report_type": {
"costs": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
},
"annotations": {
# Note: This only applies if the annotation is the same as the db column
# Cost is the first column in annotations so that it
# can reference the original database column 'markup_cost'
# If cost comes after the markup_cost annotaton, then
# Django will reference the annotated value, which is
# a Sum() and things will break trying to add
# a column with the sum of another column.
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Value(0, output_field=DecimalField()),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Value(0, output_field=DecimalField()),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count": None,
"delta_key": {
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField())))
},
"filter": [{}],
"cost_units_key": "currency_code",
"cost_units_fallback": "USD",
"sum_columns": ["cost_total", "sup_total", "infra_total"],
"default_ordering": {
"cost_total": "desc"
},
},
"costs_by_project": {
"tables": {
"query": OCPAWSCostLineItemProjectDailySummary,
"total": OCPAWSCostLineItemProjectDailySummary,
},
"tag_column": "pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count": None,
"delta_key": {
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField())))
},
"filter": [{}],
"cost_units_key": "currency_code",
"cost_units_fallback": "USD",
"sum_columns": ["cost_total", "sup_total", "infra_total"],
"default_ordering": {
"cost_total": "desc"
},
},
"storage": {
"aggregates": {
"infra_cost":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"annotations": {
"infra_cost":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count":
None,
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "product_family",
"operation": "icontains",
"parameter": "Storage"
}],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"GB-Mo",
"sum_columns":
["usage", "cost_total", "sup_total", "infra_total"],
"default_ordering": {
"usage": "desc"
},
},
"storage_by_project": {
"tables": {
"query": OCPAWSCostLineItemProjectDailySummary,
"total": OCPAWSCostLineItemProjectDailySummary,
},
"tag_column":
"pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count":
None,
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "product_family",
"operation": "icontains",
"parameter": "Storage"
}],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"GB-Mo",
"sum_columns":
["usage", "cost_total", "sup_total", "infra_total"],
"default_ordering": {
"usage": "desc"
},
},
"instance_type": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"aggregate_key":
"usage_amount",
"annotations": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"count_units":
Value("instances", output_field=CharField()),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("Hrs")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count":
"resource_id",
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "instance_type",
"operation": "isnull",
"parameter": False
}],
"group_by": ["instance_type"],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"Hrs",
"count_units_fallback":
"instances",
"sum_columns": [
"usage", "cost_total", "sup_total", "infra_total",
"count"
],
"default_ordering": {
"usage": "desc"
},
},
"instance_type_by_project": {
"tables": {
"query": OCPAWSCostLineItemProjectDailySummary,
"total": OCPAWSCostLineItemProjectDailySummary,
},
"tag_column":
"pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"aggregate_key":
"usage_amount",
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"count_units":
Value("instances", output_field=CharField()),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("Hrs")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
"source_uuid":
ArrayAgg(F("source_uuid"), distinct=True),
},
"count":
"resource_id",
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "instance_type",
"operation": "isnull",
"parameter": False
}],
"group_by": ["instance_type"],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"Hrs",
"count_units_fallback":
"instances",
"sum_columns": [
"usage", "cost_total", "sup_total", "infra_total",
"count"
],
"default_ordering": {
"usage": "desc"
},
},
"tags": {
"default_ordering": {
"cost_total": "desc"
}
},
},
"start_date":
"usage_start",
"tables": {
"query": OCPAWSCostLineItemDailySummary,
"total": OCPAWSCostLineItemDailySummary
},
}]
self.views = {
"costs": {
"default": OCPAWSCostSummary,
("account", ): OCPAWSCostSummaryByAccount,
("service", ): OCPAWSCostSummaryByService,
("account", "service"): OCPAWSCostSummaryByService,
("region", ): OCPAWSCostSummaryByRegion,
("account", "region"): OCPAWSCostSummaryByRegion,
},
"instance_type": {
"default": OCPAWSComputeSummary,
("instance_type", ): OCPAWSComputeSummary,
("account", "instance_type"): OCPAWSComputeSummary,
("account", ): OCPAWSComputeSummary,
},
"storage": {
"default": OCPAWSStorageSummary,
("account", ): OCPAWSStorageSummary
},
"database": {
"default": OCPAWSDatabaseSummary,
("service", ): OCPAWSDatabaseSummary,
("account", "service"): OCPAWSDatabaseSummary,
("account", ): OCPAWSDatabaseSummary,
},
"network": {
"default": OCPAWSNetworkSummary,
("service", ): OCPAWSNetworkSummary,
("account", "service"): OCPAWSNetworkSummary,
("account", ): OCPAWSNetworkSummary,
},
}
super().__init__(provider, report_type)
def get_queryset(self, request):
queryset = super().get_queryset(request)
return queryset.annotate(product_categories_names_list=ArrayAgg(
'product__related_categories__category__name'))
def get_queryset(self, request):
queryset = super().get_queryset(request)
return queryset.annotate(
s_value_variants_names_list=ArrayAgg('value_list__name'),
s_value_variants_id_list=ArrayAgg('value_list__id'),
)
def get(self, request, **kwargs):
try:
start_date = request.GET.get('startDate', '120d')
start_date = parse_relative_date(start_date)
except ValueError as e:
raise ParseError(detail=f'Invalid start date: {start_date}')
try:
end_date = request.GET.get('endDate', '0d')
end_date = parse_relative_date(end_date)
except ValueError:
raise ParseError(detail=f'Invalid end date: {end_date}')
try:
stats_period = max(int(request.GET.get('statsPeriod', '10')), 1)
except ValueError:
raise ParseError(detail=f'Invalid stats period: {stats_period}')
try:
slug = kwargs['account_slug']
account = Account.objects.get(holders__in=[request.user],
slug=slug)
except Account.DoesNotExist:
raise Http404
activities = AccountActivity.objects \
.filter(account=account) \
.filter(date__gte=start_date) \
.filter(date__lte=end_date)
first_date = activities.aggregate(Min('date'))['date__min']
period = ExpressionWrapper((F('date') - first_date) / stats_period,
output_field=fields.DurationField())
period_date = ExpressionWrapper(first_date +
stats_period * F('period'),
output_field=fields.DateField())
activities = activities.annotate(period=period) \
.order_by('period') \
.values('period') \
.annotate(
period_date=period_date,
period_balance=ArrayAgg('balance'),
period_deposit=Sum('deposit'),
period_withdrawl=Sum('withdrawl'),
)
# if there is a period of inactivity on the account, the series
# will be missing data during those periods, so let's fill them
activities = list(activities)
periods = {activity['period'] for activity in activities}
all_periods = set(range(max(periods) + 1))
for period in sorted(all_periods - periods):
# since we align the beginning of the activities to the
# first activity within the defined date range, the 0th
# period should always be defined
assert period != 0
previous = activities[period - 1]
activities.insert(
period, {
'period':
period,
'period_date':
previous['period_date'] + timedelta(days=stats_period),
'period_balance': [previous['period_balance'][-1]],
'period_deposit':
0.,
'period_withdrawl':
0.,
})
# drop the extraneous balance data, we just want the final balance
# at the end of each period
for activity in activities:
activity['period_balance'] = activity['period_balance'][-1]
activity['period_withdrawl'] = -activity['period_withdrawl']
return Response(activities, status=status.HTTP_200_OK)
def top_sort(team_id, days_ago):
cachedTaskSizeRemaining = {}
level = 0
CUTOFF = 200
start = datetime.utcnow() - timedelta(days=days_ago)
possibleNextLevel = set(
list(
Task.objects.filter(
is_trashed=False,
process_type__team_created_by__id=team_id,
created_at__gte=start).annotate(num_parents=Count(
F('inputs__input_item__creating_task__id'))).filter(
num_parents__lte=0).values_list('id',
flat=True).distinct()))
nextLevel = set()
# only propagate the DAG's that don't have cycles
print("removing graphs with cycles")
for x in possibleNextLevel:
if not check_for_cycles(x):
nextLevel.add(x)
path = []
while len(nextLevel) > 0:
currLevel = nextLevel
nextLevel = set()
print("level: " + str(level))
for node in currLevel:
if node:
if node not in path:
path.append(node)
else:
path.remove(node)
path.append(node)
matching_task = Task.objects.filter(pk=node).annotate(
children_list=ArrayAgg('items__inputs__task__id'))
if matching_task.count() > 0:
children = set(matching_task[0].children_list)
else:
children = set()
nextLevel = nextLevel.union(children)
print("updating costs for children of " + str(node))
currTask = Task.objects.filter(pk=node).annotate(
batch_size=Coalesce(Sum('items__amount'), 0))[0]
# get the children in order
childrenInOrder = Input.objects.filter(
task__in=list(children)).order_by('id').values_list(
'task', flat=True)
childrenInOrder = make_unique(childrenInOrder)
print("children: ")
print(childrenInOrder)
if currTask.id not in cachedTaskSizeRemaining:
cachedTaskSizeRemaining[currTask.id] = currTask.batch_size
currTaskRemainingWorth = currTask.remaining_worth
for child in childrenInOrder:
childTaskObj = Task.objects.get(pk=child)
task_ing = TaskIngredient.objects.filter(
task__id=child,
ingredient__process_type=currTask.process_type,
ingredient__product_type=currTask.product_type)
if task_ing.count() > 0:
task_ing = task_ing[0]
# get the parents of the child that have the same ingredient type as currTask to get the number of matching parent tasks
matchingParents = set(
Input.objects.filter(
task__id=child,
input_item__creating_task__process_type=currTask
.process_type,
input_item__creating_task__product_type=currTask
.product_type).values_list(
'input_item__creating_task__id',
flat=True))
amountToGive = task_ing.actual_amount / len(
matchingParents)
amountGiven = min(cachedTaskSizeRemaining[currTask.id],
amountToGive)
# keep track of how much this task actually has left to give
cachedTaskSizeRemaining[currTask.id] -= amountGiven
costGiven = 0
if currTask.batch_size != 0:
costGiven = (amountGiven /
currTask.batch_size) * currTask.cost
# add to the child's cost and remaining worth
childTaskObj.cost = childTaskObj.cost + costGiven
childTaskObj.remaining_worth = childTaskObj.remaining_worth + costGiven
childTaskObj.save()
# subtract from the current tasks's remaining worth
currTask.remaining_worth = currTask.remaining_worth - costGiven
currTask.save()
level += 1
if CUTOFF <= level:
print("oh-no")
break
return (path)
def get_queryset(self):
return super().get_queryset().annotate(
strain_ids=ArrayAgg("strains__id"))
def list_assets_view(request):
# Check sorting options
allowed_sort_options = [
"id", "name", "criticity_num", "score", "type", "updated_at",
"risk_level", "risk_level__grade", "-id", "-name", "-criticity_num",
"-score", "-type", "-updated_at", "-risk_level", "-risk_level__grade"
]
sort_options = request.GET.get("sort", "-updated_at")
sort_options_valid = []
for s in sort_options.split(","):
if s in allowed_sort_options and s not in sort_options_valid:
sort_options_valid.append(str(s))
# Check Filtering options
filter_options = request.GET.get("filter", "")
# Todo: filter on fields
allowed_filter_fields = ["id", "name", "criticity", "type", "score"]
filter_criterias = filter_options.split(" ")
filter_fields = {}
filter_opts = ""
for criteria in filter_criterias:
field = criteria.split(":")
if len(field) > 1 and field[0] in allowed_filter_fields:
# allowed field
if field[0] == "score":
filter_fields.update({"risk_level__grade": field[1]})
else:
filter_fields.update({str(field[0]): field[1]})
else:
filter_opts = filter_opts + str(criteria.strip())
# Query
assets_list = Asset.objects.filter(**filter_fields).filter(
Q(value__icontains=filter_opts) | Q(name__icontains=filter_opts)
| Q(description__icontains=filter_opts)).annotate(criticity_num=Case(
When(criticity="high", then=Value("1")),
When(criticity="medium", then=Value("2")),
When(criticity="low", then=Value("3")),
default=Value("1"),
output_field=CharField())).annotate(
cat_list=ArrayAgg('categories__value')).order_by(
*sort_options_valid)
# Pagination assets
nb_rows = int(request.GET.get('n', 16))
assets_paginator = Paginator(assets_list, nb_rows)
page = request.GET.get('page')
try:
assets = assets_paginator.page(page)
except PageNotAnInteger:
assets = assets_paginator.page(1)
except EmptyPage:
assets = assets_paginator.page(assets_paginator.num_pages)
# List asset groups
asset_groups = []
for asset_group in AssetGroup.objects.all():
ag = model_to_dict(asset_group)
# extract asset names to diplay
asset_list = []
for asset in asset_group.assets.all():
asset_list.append(asset.value)
ag["assets_names"] = ", ".join(asset_list)
ag["risk_grade"] = asset_group.get_risk_grade()
asset_groups.append(ag)
return render(request, 'list-assets.html', {
'assets': assets,
'asset_groups': asset_groups
})
def test_array_agg_integerfield(self):
values = AggregateTestModel.objects.aggregate(arrayagg=ArrayAgg('integer_field'))
self.assertEqual(values, {'arrayagg': [0, 1, 2, 0]})
def detail_asset_group_view(request, assetgroup_id):
asset_group = get_object_or_404(AssetGroup, id=assetgroup_id)
findings = Finding.objects.filter(
asset__in=asset_group.assets.all()).annotate(severity_numm=Case(
When(severity="critical", then=Value("0")),
When(severity="high", then=Value("1")),
When(severity="medium", then=Value("2")),
When(severity="low", then=Value("3")),
When(severity="info", then=Value("4")),
default=Value("1"),
output_field=CharField())).annotate(
scope_list=ArrayAgg('scopes__name')).order_by(
'asset', 'severity_numm', 'type', 'updated_at')
asset_scopes = {}
for scope in EnginePolicyScope.objects.all():
asset_scopes.update({
scope.name: {
'priority': scope.priority,
'id': scope.id,
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0
}
})
findings_stats = {
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0,
'new': 0,
'ack': 0
}
engines_stats = {}
for finding in findings:
findings_stats['total'] = findings_stats.get('total', 0) + 1
findings_stats[finding.severity] = findings_stats.get(
finding.severity, 0) + 1
if finding.status == 'new':
findings_stats['new'] = findings_stats.get('new', 0) + 1
if finding.status == 'ack':
findings_stats['ack'] = findings_stats.get('ack', 0) + 1
for fs in finding.scope_list:
if fs is not None:
c = asset_scopes[fs]
asset_scopes[fs].update({
'total':
c['total'] + 1,
finding.severity:
c[finding.severity] + 1
})
if finding.engine_type not in engines_stats.keys():
engines_stats.update({finding.engine_type: 0})
engines_stats[finding.engine_type] = engines_stats.get(
finding.engine_type, 0) + 1
# Scans
scan_defs = ScanDefinition.objects.filter(
Q(assetgroups_list__in=[asset_group])).annotate(
engine_type_name=F('engine_type__name'))
scans = []
for scan_def in scan_defs:
scans = scans + list(scan_def.scan_set.all())
scans_stats = {
'performed': len(scans),
'defined': len(scan_defs),
'periodic': scan_defs.filter(scan_type='periodic').count(),
'ondemand': scan_defs.filter(scan_type='single').count(),
'running': scan_defs.filter(
status='started').count() # bug: a regrouper par assets
}
# calculate automatically risk grade
# asset_group.calc_risk_grade()
asset_group_risk_grade = {
'now': asset_group.get_risk_grade(),
# 'day_ago': asset_group.get_risk_grade(history = 1),
# 'week_ago': asset_group.get_risk_grade(history = 7),
# 'month_ago': asset_group.get_risk_grade(history = 30),
# 'year_ago': asset_group.get_risk_grade(history = 365)
}
return render(
request, 'details-asset-group.html', {
'asset_group': asset_group,
'asset_group_risk_grade': asset_group_risk_grade,
'findings': findings,
'findings_stats': findings_stats,
'scans_stats': scans_stats,
'scans': scans,
'scan_defs': scan_defs,
'engines_stats': engines_stats,
'asset_scopes': list(asset_scopes.items())
})
def test_array_agg_booleanfield(self):
values = AggregateTestModel.objects.aggregate(arrayagg=ArrayAgg('boolean_field'))
self.assertEqual(values, {'arrayagg': [True, False, False, True]})
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [
{
"provider": Provider.OCP_AZURE,
"alias": "subscription_guid",
"annotations": {"cluster": "cluster_id"},
"end_date": "costentrybill__billing_period_end",
"filters": {
"project": {"field": "namespace", "operation": "icontains"},
"cluster": [
{"field": "cluster_alias", "operation": "icontains", "composition_key": "cluster_filter"},
{"field": "cluster_id", "operation": "icontains", "composition_key": "cluster_filter"},
],
"node": {"field": "node", "operation": "icontains"},
"subscription_guid": [
{"field": "subscription_guid", "operation": "icontains", "composition_key": "account_filter"}
],
"service_name": {"field": "service_name", "operation": "icontains"},
"resource_location": {"field": "resource_location", "operation": "icontains"},
"instance_type": {"field": "instance_type", "operation": "icontains"},
},
"group_by_options": [
"cluster", # ocp
"project", # ocp
"node", # ocp
"service_name", # azure
"subscription_guid", # azure
"resource_location", # azure
"instance_type", # azure
],
"tag_column": "tags",
"report_type": {
"costs": {
"aggregates": {
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
},
"annotations": {
# Cost is the first column in annotations so that it
# can reference the original database column 'markup_cost'
# If cost comes after the markup_cost annotaton, then
# Django will reference the annotated value, which is
# a Sum() and things will break trying to add
# a column with the sum of another column.
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_total": Value(0, output_field=DecimalField()),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Value(0, output_field=DecimalField()),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Value(0, output_field=DecimalField()),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": None,
"delta_key": {
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
)
},
"filter": [{}],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"sum_columns": ["cost_total", "sup_total", "infra_total"],
"default_ordering": {"cost_total": "desc"},
},
"costs_by_project": {
"tables": {
"query": OCPAzureCostLineItemProjectDailySummary,
"total": OCPAzureCostLineItemProjectDailySummary,
},
"tag_column": "pod_labels",
"aggregates": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
},
"annotations": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"sup_total": Value(0, output_field=DecimalField()),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": None,
"delta_key": {
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
)
},
"filter": [{}],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"sum_columns": ["cost_total", "sup_total", "infra_total"],
"default_ordering": {"cost_total": "desc"},
},
"storage": {
"aggregates": {
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"usage": Sum(F("usage_quantity")),
"usage_units": Coalesce(Max("unit_of_measure"), Value("GB-Mo")),
},
"annotations": {
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"sup_total": Value(0, output_field=DecimalField()),
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"usage": Sum(F("usage_quantity")),
"usage_units": Coalesce(Max("unit_of_measure"), Value("GB-Mo")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": None,
"delta_key": {"usage": Sum("usage_quantity")},
"filter": [
{"field": "service_name", "operation": "icontains", "parameter": "Storage"},
{"field": "unit_of_measure", "operation": "exact", "parameter": "GB-Mo"},
],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"usage_units_key": "unit_of_measure",
"usage_units_fallback": "GB-Mo",
"sum_columns": ["usage", "cost_total", "sup_total", "infra_total"],
"default_ordering": {"usage": "desc"},
},
"storage_by_project": {
"tables": {
"query": OCPAzureCostLineItemProjectDailySummary,
"total": OCPAzureCostLineItemProjectDailySummary,
},
"tag_column": "pod_labels",
"aggregates": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"usage": Sum("usage_quantity"),
"usage_units": Coalesce(Max("unit_of_measure"), Value("GB-Mo")),
},
"annotations": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"sup_total": Value(0, output_field=DecimalField()),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"usage": Sum("usage_quantity"),
"usage_units": Coalesce(Max("unit_of_measure"), Value("GB-Mo")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": None,
"delta_key": {"usage": Sum("usage_quantity")},
"filter": [
{"field": "service_name", "operation": "icontains", "parameter": "Storage"},
{"field": "unit_of_measure", "operation": "exact", "parameter": "GB-Mo"},
],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"usage_units_key": "unit_of_measure",
"usage_units_fallback": "GB-Mo",
"sum_columns": ["usage", "cost_total", "sup_total", "infra_total"],
"default_ordering": {"usage": "desc"},
},
"instance_type": {
"aggregates": {
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"count": Count("resource_id", distinct=True),
"usage": Sum(F("usage_quantity")),
"usage_units": Coalesce(Max("unit_of_measure"), Value("Hrs")),
},
"aggregate_key": "usage_quantity",
"annotations": {
"infra_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum(F("pretax_cost")),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"sup_total": Value(0, output_field=DecimalField()),
"cost_total": Sum(
Coalesce(F("pretax_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum(F("pretax_cost")),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(Coalesce(F("markup_cost"), Value(0, output_field=DecimalField()))),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"count": Count("resource_id", distinct=True),
"count_units": Value("instances", output_field=CharField()),
"usage": Sum(F("usage_quantity")),
"usage_units": Coalesce(Max("unit_of_measure"), Value("Hrs")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": "resource_id",
"delta_key": {"usage": Sum("usage_quantity")},
"filter": [
{"field": "instance_type", "operation": "isnull", "parameter": False},
{"field": "unit_of_measure", "operation": "exact", "parameter": "Hrs"},
],
"group_by": ["instance_type"],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"usage_units_key": "unit_of_measure",
"usage_units_fallback": "Hrs",
"count_units_fallback": "instances",
"sum_columns": ["usage", "cost_total", "sup_total", "infra_total", "count"],
"default_ordering": {"usage": "desc"},
},
"instance_type_by_project": {
"tables": {
"query": OCPAzureCostLineItemProjectDailySummary,
"total": OCPAzureCostLineItemProjectDailySummary,
},
"tag_column": "pod_labels",
"aggregates": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Sum(Value(0, output_field=DecimalField())),
"sup_usage": Sum(Value(0, output_field=DecimalField())),
"sup_markup": Sum(Value(0, output_field=DecimalField())),
"sup_total": Sum(Value(0, output_field=DecimalField())),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"count": Count("resource_id", distinct=True),
"usage": Sum("usage_quantity"),
"usage_units": Coalesce(Max("unit_of_measure"), Value("Hrs")),
},
"aggregate_key": "usage_quantity",
"annotations": {
"infra_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"infra_raw": Sum("pod_cost"),
"infra_usage": Sum(Value(0, output_field=DecimalField())),
"infra_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"sup_raw": Value(0, output_field=DecimalField()),
"sup_usage": Value(0, output_field=DecimalField()),
"sup_markup": Value(0, output_field=DecimalField()),
"sup_total": Value(0, output_field=DecimalField()),
"cost_total": Sum(
Coalesce(F("pod_cost"), Value(0, output_field=DecimalField()))
+ Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_raw": Sum("pod_cost"),
"cost_usage": Sum(Value(0, output_field=DecimalField())),
"cost_markup": Sum(
Coalesce(F("project_markup_cost"), Value(0, output_field=DecimalField()))
),
"cost_units": Coalesce(Max("currency"), Value("USD")),
"count": Count("resource_id", distinct=True),
"count_units": Value("instances", output_field=CharField()),
"usage": Sum("usage_quantity"),
"usage_units": Coalesce(Max("unit_of_measure"), Value("Hrs")),
"clusters": ArrayAgg(Coalesce("cluster_alias", "cluster_id"), distinct=True),
"source_uuid": ArrayAgg(
F("source_uuid"), filter=Q(source_uuid__isnull=False), distinct=True
),
},
"count": "resource_id",
"delta_key": {"usage": Sum("usage_quantity")},
"filter": [
{"field": "instance_type", "operation": "isnull", "parameter": False},
{"field": "unit_of_measure", "operation": "exact", "parameter": "Hrs"},
],
"group_by": ["instance_type"],
"cost_units_key": "currency",
"cost_units_fallback": "USD",
"usage_units_key": "unit_of_measure",
"usage_units_fallback": "Hrs",
"count_units_fallback": "instances",
"sum_columns": ["usage", "cost_total", "sup_total", "infra_total", "count"],
"default_ordering": {"usage": "desc"},
},
"tags": {"default_ordering": {"cost_total": "desc"}},
},
"start_date": "usage_start",
"tables": {"query": OCPAzureCostLineItemDailySummary, "total": OCPAzureCostLineItemDailySummary},
}
]
self.views = {
"costs": {
"default": OCPAzureCostSummary,
("subscription_guid",): OCPAzureCostSummaryByAccount,
("service_name",): OCPAzureCostSummaryByService,
("service_name", "subscription_guid"): OCPAzureCostSummaryByService,
("resource_location",): OCPAzureCostSummaryByLocation,
("resource_location", "subscription_guid"): OCPAzureCostSummaryByLocation,
},
"instance_type": {
"default": OCPAzureComputeSummary,
("instance_type",): OCPAzureComputeSummary,
("instance_type", "subscription_guid"): OCPAzureComputeSummary,
("subscription_guid",): OCPAzureComputeSummary,
},
"storage": {"default": OCPAzureStorageSummary, ("subscription_guid",): OCPAzureStorageSummary},
"database": {
"default": OCPAzureDatabaseSummary,
("service_name",): OCPAzureDatabaseSummary,
("service_name", "subscription_guid"): OCPAzureDatabaseSummary,
("subscription_guid",): OCPAzureDatabaseSummary,
},
"network": {
"default": OCPAzureNetworkSummary,
("service_name",): OCPAzureNetworkSummary,
("service_name", "subscription_guid"): OCPAzureNetworkSummary,
("subscription_guid",): OCPAzureNetworkSummary,
},
}
super().__init__(provider, report_type)
def test_orderable_agg_alternative_fields(self):
values = AggregateTestModel.objects.aggregate(
arrayagg=ArrayAgg('integer_field', ordering=F('char_field').asc())
)
self.assertEqual(values, {'arrayagg': [0, 1, 0, 2]})
def remove_attr_data_from_products(product=None,
category=None,
attr_group=None,
attr=None):
mode = None
list_of_deleted_collections, list_of_modified_collections, replaced_collection_dict = [], [], {}
if category and not (product or attr_group or attr):
mode = 'delete_category'
if mode == 'delete_category':
for collection in CategoryCollection.objects.annotate(
cat_id_list=ArrayAgg('category_list')).filter(
category_list=category):
# удаляем коллекцию если остается одна категория, сохранеяем ее в списке удаленных чтобы в связанных товарах
# custom_order_group сделать пустым
if len(collection.cat_id_list) == 2:
list_of_deleted_collections.append(collection.id)
collection.delete()
else:
# находим коллекции которые совпадают с этой укороченной
other_collection = CategoryCollection.objects.annotate(
cnt=Count('category_list')).filter(
cnt=len(collection.cat_id_list) -
1).exclude(id=collection.id)
for cat in collection.cat_id_list:
if cat == category.id:
continue
other_collection = other_collection.filter(
category_list=cat)
# помечаем эту коллекцию на удаление и в replaced_collection_dict записываем id коллекции
# на какую ее поменять для свазанных товаров (и с какой взять custom_order_group
if other_collection.exists():
replaced_collection_dict[
collection.id] = other_collection[0].id
# collection.delete()
else:
# если коллекцию не удалили то помечаем ее как модифицированую чтобы для связаных товаров
# обновить custom_order_group согласно изменений в коллекции
list_of_modified_collections.append(collection.id)
print(list_of_deleted_collections)
print(replaced_collection_dict)
print(list_of_modified_collections)
if product:
products_list = [product]
else:
products_list = Product.objects.filter(
related_categories__category=category).only(
'parameters', 'parameters_structure',
'shot_parameters_structure', 'mini_parameters_structure')
all_data_products_to_update_type = namedtuple(
'all_data_products_to_update_type', 'products_list fields_names_list')
all_data_products_to_update = all_data_products_to_update_type([], [])
for product in products_list:
if (category_id :=
str(category.id)) in product.parameters_structure.keys():
product.parameters_structure.pop(category_id)
all_data_products_to_update.fields_names_list.append(
'parameters_structure')
all_data_products_to_update.fields_names_list.append('parameters')
if category_id in product.shot_parameters_structure.keys():
product.shot_parameters_structure.pop(category_id)
all_data_products_to_update.fields_names_list.append(
'shot_parameters_structure')
if category_id in product.mini_parameters_structure.keys():
product.mini_parameters_structure.pop(category_id)
all_data_products_to_update.fields_names_list.append(
'mini_parameters_structure')
if mode == 'delete_category':
if product.category_collection_id in list_of_deleted_collections:
product.custom_order_group = []
if product.category_collection_id in replaced_collection_dict:
print(
f'коллекция {product.category_collection_id} заменена на {replaced_collection_dict[product.category_collection_id]}'
)
product.category_collection_id = replaced_collection_dict[
product.category_collection_id]
product.custom_order_group = [[
gr.category_id, gr.group_id
] for gr in CategoryCollection.objects.get(
id=product.category_collection_id).rel_group_iocog.
order_by('position')]
all_data_products_to_update.fields_names_list.append(
'custom_order_group')
all_data_products_to_update.fields_names_list.append(
'category_collection')
if mode == 'delete_category' or mode == 'delete_group':
if product.category_collection_id in list_of_modified_collections:
# в custom_order_group удаляем отдельные элементы
for i, cat_and_group in enumerate(product.custom_order_group):
if mode == 'delete_category':
if cat_and_group[0] == category.id:
product.custom_order_group[i].pop()
all_data_products_to_update.fields_names_list.append(
'custom_order_group')
if mode == 'delete_group':
if cat_and_group[1] == attr_group.id:
product.custom_order_group[i].pop()
all_data_products_to_update.fields_names_list.append(
'custom_order_group')
# select_related
for group_id in AttrGroup.objects.filter(
related_categories__category=category).values_list('id',
flat=True):
for atr_id in Attribute.objects.filter(
related_groups__group=group_id).values_list('id',
flat=True):
if (full_id := f'{group_id}-{atr_id}') in product.parameters:
product.parameters.pop(full_id)
def list_assets_view(request):
# Check team
teamid_selected = -1
if settings.PRO_EDITION is True and request.GET.get(
'team', '').isnumeric() and int(request.GET.get('team', -1)) >= 0:
teamid = int(request.GET.get('team'))
# @Todo: ensure the team is allowed for this user
teamid_selected = teamid
teams = []
if settings.PRO_EDITION and request.user.is_superuser:
teams = Team.objects.all().order_by('name')
elif settings.PRO_EDITION and not request.user.is_superuser:
for tu in TeamUser.objects.filter(user=request.user):
teams.append({
'id': tu.organization.id,
'name': tu.organization.name
})
# Check sorting options
allowed_sort_options = [
"id", "name", "criticity_num", "score", "type", "updated_at",
"risk_level", "risk_level__grade", "-id", "-name", "-criticity_num",
"-score", "-type", "-updated_at", "-risk_level", "-risk_level__grade"
]
sort_options = request.GET.get("sort", "-updated_at")
sort_options_valid = []
for s in sort_options.split(","):
if s in allowed_sort_options and s not in sort_options_valid:
sort_options_valid.append(str(s))
# Check Filtering options
filter_options = request.GET.get("filter", "")
filter_name = request.GET.get("filter_name", "")
filter_type = request.GET.get("filter_type", "")
filter_criticity = request.GET.get("filter_criticity", "")
filter_tag = request.GET.get("filter_tag", "")
# Todo: filter on fields
# allowed_filter_fields = ["id", "name", "criticity", "type", "score","categories__value"]
# filter_criterias = filter_options.split(" ")
filter_fields = {}
# filter_opts = ""
# for criteria in filter_criterias:
# field = criteria.split(":")
# if len(field) > 1 and field[0] in allowed_filter_fields:
# # allowed field
# if field[0] == "score":
# filter_fields.update({"risk_level__grade": field[1]})
# else:
# filter_fields.update({str(field[0]): field[1]})
# else:
# filter_opts = filter_opts + str(criteria.strip())
if teamid_selected >= 0:
assets_list = Asset.objects.for_team(request.user,
teamid_selected).all()
else:
assets_list = Asset.objects.for_user(request.user).all()
filters = Q()
if filter_name and filter_name != 'null':
filter_name = filter_name.split(',')
for term in filter_name:
filters |= (Q(name__icontains=term)
| Q(description__icontains=term))
if filter_type and filter_type != 'null':
filters &= Q(type=filter_type)
if filter_criticity and filter_criticity != 'null':
filters &= Q(criticity=filter_criticity)
if filter_tag and filter_tag != 'null':
filter_tag = filter_tag.split(',')
# https://stackoverflow.com/questions/25831081/django-orm-dynamically-add-multiple-conditions-for-manytomanyfield
for tag in filter_tag:
assets_list = assets_list.filter(categories__value=tag)
# Query
if teamid_selected >= 0:
assets_list = assets_list.filter(filters).annotate(criticity_num=Case(
When(criticity="high", then=Value("1")),
When(criticity="medium", then=Value("2")),
When(criticity="low", then=Value("3")),
default=Value("1"),
output_field=CharField())).annotate(
cat_list=ArrayAgg('categories__value')).order_by(
*sort_options_valid)
else:
assets_list = assets_list.filter(filters).annotate(criticity_num=Case(
When(criticity="high", then=Value("1")),
When(criticity="medium", then=Value("2")),
When(criticity="low", then=Value("3")),
default=Value("1"),
output_field=CharField())).annotate(
cat_list=ArrayAgg('categories__value')).order_by(
*sort_options_valid)
# if teamid_selected >= 0:
# assets_list = Asset.objects.for_team(request.user, teamid_selected).filter(**filter_fields).filter(
# Q(value__icontains=filter_opts) |
# Q(name__icontains=filter_opts) |
# Q(description__icontains=filter_opts)
# ).annotate(
# criticity_num=Case(
# When(criticity="high", then=Value("1")),
# When(criticity="medium", then=Value("2")),
# When(criticity="low", then=Value("3")),
# default=Value("1"),
# output_field=CharField())
# ).annotate(cat_list=ArrayAgg('categories__value')).order_by(*sort_options_valid)
# else:
# assets_list = Asset.objects.for_user(request.user).filter(**filter_fields).filter(
# Q(value__icontains=filter_opts) |
# Q(name__icontains=filter_opts) |
# Q(description__icontains=filter_opts)
# ).annotate(
# criticity_num=Case(
# When(criticity="high", then=Value("1")),
# When(criticity="medium", then=Value("2")),
# When(criticity="low", then=Value("3")),
# default=Value("1"),
# output_field=CharField())
# ).annotate(cat_list=ArrayAgg('categories__value')).order_by(*sort_options_valid)
# Pagination assets
nb_rows = int(request.GET.get('n', 20))
assets_paginator = Paginator(assets_list, nb_rows)
page = request.GET.get('page')
try:
assets = assets_paginator.page(page)
except PageNotAnInteger:
assets = assets_paginator.page(1)
except EmptyPage:
assets = assets_paginator.page(assets_paginator.num_pages)
# List asset groups
asset_groups = []
if teamid_selected >= 0:
ags = AssetGroup.objects.for_team(
request.user, teamid_selected).all().annotate(
asset_list=ArrayAgg('assets__value')).only(
"id", "name", "assets", "criticity", "updated_at",
"risk_level", "teams")
else:
ags = AssetGroup.objects.for_user(request.user).all().annotate(
asset_list=ArrayAgg('assets__value')).only("id", "name", "assets",
"criticity",
"updated_at",
"risk_level", "teams")
for asset_group in ags.order_by(Lower("name")):
assets_names = ""
if asset_group.asset_list != [None]:
assets_names = ", ".join(asset_group.asset_list)
ag = {
"id": asset_group.id,
"name": asset_group.name,
"criticity": asset_group.criticity,
"updated_at": asset_group.updated_at,
"assets_names": assets_names,
"risk_grade": asset_group.risk_level['grade'],
"teams": asset_group.teams
}
asset_groups.append(ag)
tags = assets_list.values_list(
'categories__value',
flat=True).order_by('categories__value').distinct()
return render(
request, 'list-assets.html', {
'assets': assets,
'asset_groups': asset_groups,
'teams': teams,
'tags': tags
})
def update_stats(apps, schema_editor):
keys = [
'gene_reviewers',
'number_of_evaluated_genes',
'number_of_genes',
'number_of_ready_genes',
'number_of_green_genes',
'str_reviewers',
'number_of_evaluated_strs',
'number_of_strs',
'number_of_ready_strs',
'number_of_green_strs',
'region_reviewers',
'number_of_evaluated_regions',
'number_of_regions',
'number_of_ready_regions',
'number_of_green_regions',
]
GenePanelSnapshot = apps.get_model('panels', 'GenePanelSnapshot')
for panel in GenePanelSnapshot.objects.filter(pk__in=Subquery(
GenePanelSnapshot.objects.exclude(panel__status=GenePanel.STATUS.deleted)
.distinct('panel_id')
.values('pk').order_by('panel_id', '-major_version', '-minor_version')
)).annotate(child_panels_count=Count('child_panels')) \
.annotate(superpanels_count=Count('genepanelsnapshot')) \
.annotate(
is_super_panel=Case(
When(child_panels_count__gt=0, then=Value(True)),
default=Value(False),
output_field=models.BooleanField()
),
is_child_panel=Case(
When(superpanels_count__gt=0, then=Value(True)),
default=Value(False),
output_field=models.BooleanField()
),
):
pks = [panel.pk, ]
if panel.is_super_panel:
pks = panel.child_panels.values_list('pk', flat=True)
# another way to refactor below info: when copying data, just count the numbers...
info = GenePanelSnapshot.objects.filter(pk__in=pks) \
.aggregate(
gene_reviewers=ArrayAgg('genepanelentrysnapshot__evaluation__user', distinct=True),
number_of_evaluated_genes=Count(Case(
# Count unique genes if that gene has more than 1 evaluation
When(
genepanelentrysnapshot__evaluation__isnull=False,
then=models.F('genepanelentrysnapshot')
)
), distinct=True),
number_of_genes=Count('genepanelentrysnapshot', distinct=True),
number_of_ready_genes=Count(
Case(
When(
genepanelentrysnapshot__ready=True,
then=models.F('genepanelentrysnapshot')
)
),
distinct=True
),
number_of_green_genes=Count(
Case(
When(
genepanelentrysnapshot__saved_gel_status__gte=3,
then=models.F('genepanelentrysnapshot')
)
),
distinct=True
),
str_reviewers=ArrayAgg('str__evaluation__user', distinct=True),
number_of_evaluated_strs=Count(Case(
# Count unique genes if that gene has more than 1 evaluation
When(
str__evaluation__isnull=False,
then=models.F('str')
)
), distinct=True),
number_of_strs=Count('str', distinct=True),
number_of_ready_strs=Count(
Case(
When(
str__ready=True,
then=models.F('str')
)
),
distinct=True
),
number_of_green_strs=Count(
Case(
When(
str__saved_gel_status__gte=3,
then=models.F('str')
)
),
distinct=True
),
region_reviewers=ArrayAgg('region__evaluation__user', distinct=True),
number_of_evaluated_regions=Count(Case(
# Count unique genes if that gene has more than 1 evaluation
When(
region__evaluation__isnull=False,
then=models.F('region')
)
), distinct=True),
number_of_regions=Count('region', distinct=True),
number_of_ready_regions=Count(
Case(
When(
region__ready=True,
then=models.F('region')
)
),
distinct=True
),
number_of_green_region=Count(
Case(
When(
region__saved_gel_status__gte=3,
then=models.F('region')
)
),
distinct=True
),
)
out = {
'gene_reviewers': [],
'str_reviewers': [],
'region_reviewers': []
}
for key in keys:
out[key] = out.get(key, 0) + info.get(key, 0)
out['gene_reviewers'] = list(set([r for r in out['gene_reviewers'] if r])) # remove None
out['str_reviewers'] = list(set([r for r in out['str_reviewers'] if r])) # remove None
out['region_reviewers'] = list(set([r for r in out['region_reviewers'] if r])) # remove None
out['entity_reviewers'] = list(set(out['gene_reviewers'] + out['str_reviewers'] + out['region_reviewers']))
out['number_of_reviewers'] = len(out['entity_reviewers'])
out['number_of_evaluated_entities'] = out['number_of_evaluated_genes'] + out['number_of_evaluated_strs'] + \
out['number_of_evaluated_regions']
out['number_of_entities'] = out['number_of_genes'] + out['number_of_strs'] + out['number_of_regions']
out['number_of_ready_entities'] = out['number_of_ready_genes'] + out['number_of_ready_strs'] + \
out['number_of_ready_regions']
out['number_of_green_entities'] = out['number_of_green_genes'] + out['number_of_green_strs'] + \
out['number_of_green_regions']
panel.stats = out
panel.save(update_fields=['stats', ])
def detail_asset_group_view(request, assetgroup_id):
asset_group = get_object_or_404(AssetGroup.objects.for_user(request.user),
id=assetgroup_id)
assets_list = asset_group.assets.all().order_by("-risk_level__grade",
"criticity", "type")
findings = Finding.objects.severity_ordering().filter(
asset__in=asset_group.assets.all()).annotate(
scope_list=ArrayAgg('scopes__name')).order_by(
'-severity_order', 'asset', 'type',
'updated_at').only("severity", "status", "engine_type",
"risk_info", "vuln_refs", "title", "id",
"solution", "updated_at", "type",
"asset_id", "asset_name")
asset_scopes = {}
for scope in EnginePolicyScope.objects.all():
asset_scopes.update({
scope.name: {
'priority': scope.priority,
'id': scope.id,
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0
}
})
findings_stats = {
'total': 0,
'critical': 0,
'high': 0,
'medium': 0,
'low': 0,
'info': 0,
'new': 0,
'ack': 0
}
engines_stats = {}
for finding in findings:
findings_stats['total'] = findings_stats.get('total', 0) + 1
findings_stats[finding.severity] = findings_stats.get(
finding.severity, 0) + 1
if finding.status == 'new':
findings_stats['new'] = findings_stats.get('new', 0) + 1
if finding.status == 'ack':
findings_stats['ack'] = findings_stats.get('ack', 0) + 1
for fs in finding.scope_list:
if fs is not None:
c = asset_scopes[fs]
asset_scopes[fs].update({
'total':
c['total'] + 1,
finding.severity:
c[finding.severity] + 1
})
if finding.engine_type not in engines_stats.keys():
engines_stats.update({finding.engine_type: 0})
engines_stats[finding.engine_type] = engines_stats.get(
finding.engine_type, 0) + 1
# Scans
scan_defs = ScanDefinition.objects.filter(
Q(assetgroups_list__in=[asset_group])).annotate(
engine_type_name=F('engine_type__name'))
scans = []
for scan_def in scan_defs:
scans = scans + list(scan_def.scan_set.all())
scans_stats = {
'performed': len(scans),
'defined': len(scan_defs),
'periodic': scan_defs.filter(scan_type='periodic').count(),
'ondemand': scan_defs.filter(scan_type='single').count(),
'running': scan_defs.filter(
status='started').count() # bug: a regrouper par assets
}
# calculate automatically risk grade
# asset_group.calc_risk_grade()
asset_group_risk_grade = {
'now': asset_group.get_risk_grade(),
# 'day_ago': asset_group.get_risk_grade(history = 1),
# 'week_ago': asset_group.get_risk_grade(history = 7),
# 'month_ago': asset_group.get_risk_grade(history = 30),
# 'year_ago': asset_group.get_risk_grade(history = 365)
}
# Paginations
# Pagination assets
nb_rows = int(request.GET.get('n_assets', 25))
assets_paginator = Paginator(assets_list, nb_rows)
page = request.GET.get('p_assets')
try:
assets = assets_paginator.page(page)
except PageNotAnInteger:
assets = assets_paginator.page(1)
except EmptyPage:
assets = assets_paginator.page(assets_paginator.num_pages)
# Pagination findings
nb_rows = int(request.GET.get('n_findings', 50))
findings_paginator = Paginator(findings, nb_rows)
page = request.GET.get('p_findings')
try:
ag_findings = findings_paginator.page(page)
except PageNotAnInteger:
ag_findings = findings_paginator.page(1)
except EmptyPage:
ag_findings = findings_paginator.page(findings_paginator.num_pages)
return render(
request, 'details-asset-group.html', {
'asset_group': asset_group,
'asset_group_risk_grade': asset_group_risk_grade,
'assets': assets,
'findings': ag_findings,
'findings_stats': findings_stats,
'scans_stats': scans_stats,
'scans': scans,
'scan_defs': scan_defs,
'engines_stats': engines_stats,
'asset_scopes': list(asset_scopes.items())
})
Subquery,
)
from maasserver.config import RegionConfiguration
from maasserver.forms import ControllerForm
from maasserver.models import Config, Controller, Event, RackController, VLAN
from maasserver.models.controllerinfo import get_target_version
from maasserver.permissions import NodePermission
from maasserver.websockets.base import HandlerError, HandlerPermissionError
from maasserver.websockets.handlers.machine import MachineHandler
from maasserver.websockets.handlers.node import node_prefetch
# return the list of VLAN ids connected to a controller
_vlan_ids_aggr = ArrayAgg(
"interface__ip_addresses__subnet__vlan__id",
distinct=True,
filter=Q(interface__ip_addresses__subnet__vlan__isnull=False),
)
class ControllerHandler(MachineHandler):
class Meta(MachineHandler.Meta):
abstract = False
queryset = node_prefetch(
Controller.controllers.all().prefetch_related("service_set"),
"controllerinfo",
).annotate(vlan_ids=_vlan_ids_aggr)
list_queryset = (
Controller.controllers.all()
.select_related("controllerinfo", "domain", "bmc")
.prefetch_related("service_set")
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [{
"provider":
Provider.OCP_ALL,
"alias":
"account_alias__account_alias",
"annotations": {
"cluster": "cluster_id",
"project": "namespace",
"account": "usage_account_id",
"service": "product_code",
"az": "availability_zone",
},
"end_date":
"usage_end",
"filters": {
"project": {
"field": "namespace",
"operation": "icontains"
},
"cluster": [
{
"field": "cluster_alias",
"operation": "icontains",
"composition_key": "cluster_filter"
},
{
"field": "cluster_id",
"operation": "icontains",
"composition_key": "cluster_filter"
},
],
"node": {
"field": "node",
"operation": "icontains"
},
"account": [
{
"field": "account_alias__account_alias",
"operation": "icontains",
"composition_key": "account_filter",
},
{
"field": "usage_account_id",
"operation": "icontains",
"composition_key": "account_filter"
},
],
"service": {
"field": "product_code",
"operation": "icontains"
},
"product_family": {
"field": "product_family",
"operation": "icontains"
},
"az": {
"field": "availability_zone",
"operation": "icontains"
},
"region": {
"field": "region",
"operation": "icontains"
},
"source_type": {
"field": "source_type",
"operation": "icontains"
},
},
"group_by_options": [
"account", "service", "region", "cluster", "project", "node",
"product_family"
],
"tag_column":
"tags",
"report_type": {
"costs": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Value(0, output_field=DecimalField()),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Value(0, output_field=DecimalField()),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
None,
"delta_key": {
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField())))
},
"filter": [{}],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"sum_columns": [
"cost_total",
"sup_total",
"infra_total",
"infra_raw",
"infra_usage",
"infra_markup",
],
"default_ordering": {
"cost_total": "desc"
},
},
"costs_by_project": {
"tables": {
"query": OCPAllCostLineItemProjectDailySummary,
"total": OCPAllCostLineItemProjectDailySummary,
},
"tag_column":
"pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
None,
"delta_key": {
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField())))
},
"filter": [{}],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"sum_columns": [
"infra_raw",
"infra_markup",
"infra_usage",
"cost_total",
"sup_total",
"infra_total",
],
"default_ordering": {
"cost_total": "desc"
},
},
"storage": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
None,
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{}],
"or_filter": [
{
"field": "product_family",
"operation": "icontains",
"parameter": "Storage"
},
{
"field": "product_code",
"operation": "icontains",
"parameter": "Storage"
},
],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"GB-Mo",
"sum_columns": [
"usage",
"infra_raw",
"infra_markup",
"infra_usage",
"cost_total",
"sup_total",
"infra_total",
],
"default_ordering": {
"usage": "desc"
},
},
"storage_by_project": {
"tables": {
"query": OCPAllCostLineItemProjectDailySummary,
"total": OCPAllCostLineItemProjectDailySummary,
},
"tag_column":
"pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
None,
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{}],
"or_filter": [
{
"field": "product_family",
"operation": "icontains",
"parameter": "Storage"
},
{
"field": "product_code",
"operation": "icontains",
"parameter": "Storage"
},
],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"GB-Mo",
"sum_columns": [
"usage",
"cost_total",
"sup_total",
"infra_total",
"infra_raw",
"infra_usage",
"infra_markup",
],
"default_ordering": {
"usage": "desc"
},
},
"instance_type": {
"aggregates": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"aggregate_key":
"usage_amount",
"annotations": {
"infra_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum(F("unblended_cost")),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("unblended_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum(F("unblended_cost")),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"count_units":
Value("instances", output_field=CharField()),
"usage":
Sum(F("usage_amount")),
"usage_units":
Coalesce(Max("unit"), Value("Hrs")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
"resource_id",
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "instance_type",
"operation": "isnull",
"parameter": False
}],
"group_by": ["instance_type"],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"Hrs",
"count_units_fallback":
"instances",
"sum_columns": [
"usage",
"cost_total",
"sup_total",
"infra_total",
"infra_raw",
"infra_markup",
"infra_usage",
"count",
],
"default_ordering": {
"usage": "desc"
},
},
"instance_type_by_project": {
"tables": {
"query": OCPAllCostLineItemProjectDailySummary,
"total": OCPAllCostLineItemProjectDailySummary,
},
"tag_column":
"pod_labels",
"aggregates": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Sum(Value(0, output_field=DecimalField())),
"sup_usage":
Sum(Value(0, output_field=DecimalField())),
"sup_markup":
Sum(Value(0, output_field=DecimalField())),
"sup_total":
Sum(Value(0, output_field=DecimalField())),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("GB-Mo")),
},
"aggregate_key":
"usage_amount",
"annotations": {
"infra_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"infra_raw":
Sum("pod_cost"),
"infra_usage":
Sum(Value(0, output_field=DecimalField())),
"infra_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"sup_raw":
Value(0, output_field=DecimalField()),
"sup_usage":
Value(0, output_field=DecimalField()),
"sup_markup":
Value(0, output_field=DecimalField()),
"sup_total":
Value(0, output_field=DecimalField()),
"cost_total":
Sum(
Coalesce(F("pod_cost"),
Value(0, output_field=DecimalField())) +
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_raw":
Sum("pod_cost"),
"cost_usage":
Sum(Value(0, output_field=DecimalField())),
"cost_markup":
Sum(
Coalesce(F("project_markup_cost"),
Value(0, output_field=DecimalField()))),
"cost_units":
Coalesce(Max("currency_code"), Value("USD")),
"count":
Count("resource_id", distinct=True),
"count_units":
Value("instances", output_field=CharField()),
"usage":
Sum("usage_amount"),
"usage_units":
Coalesce(Max("unit"), Value("Hrs")),
"clusters":
ArrayAgg(Coalesce("cluster_alias", "cluster_id"),
distinct=True),
},
"count":
"resource_id",
"delta_key": {
"usage": Sum("usage_amount")
},
"filter": [{
"field": "instance_type",
"operation": "isnull",
"parameter": False
}],
"group_by": ["instance_type"],
"cost_units_key":
"currency_code",
"cost_units_fallback":
"USD",
"usage_units_key":
"unit",
"usage_units_fallback":
"Hrs",
"count_units_fallback":
"instances",
"sum_columns": [
"usage",
"cost_total",
"sup_total",
"infra_total",
"infra_raw",
"infra_markup",
"infra_usage",
"count",
],
"default_ordering": {
"usage": "desc"
},
},
"tags": {
"default_ordering": {
"cost_total": "desc"
}
},
},
"start_date":
"usage_start",
"tables": {
"query": OCPAllCostLineItemDailySummary,
"total": OCPAllCostLineItemDailySummary
},
}]
self.views = {
"costs": {
"default": OCPAllCostSummary,
"account": OCPAllCostSummaryByAccount,
"region": OCPAllCostSummaryByRegion,
"service": OCPAllCostSummaryByService,
"product_family": OCPAllCostSummaryByService,
},
"instance_type": {
"default": OCPAllComputeSummary,
"instance_type": OCPAllComputeSummary
},
"storage": {
"default": OCPAllStorageSummary,
"product_family": OCPAllStorageSummary
},
"database": {
"default": OCPAllDatabaseSummary,
"service": OCPAllDatabaseSummary
},
"network": {
"default": OCPAllNetworkSummary,
"service": OCPAllNetworkSummary
},
}
super().__init__(provider, report_type)
def render_tree_v3(request):
# Verify the user selection from session and if not present redirect
simple_selection = request.session.get('selection', False)
if simple_selection == False or not simple_selection.targets:
return redirect("/phylogenetic_trees/targetselection")
Tree_class = Treeclass()
phylogeny_input, branches, ttype, total, legend, box, Additional_info, buttons, proteins = Tree_class.Prepare_file(
request)
if phylogeny_input == 'too big':
return render(request, 'phylogenetic_trees/too_big.html')
if phylogeny_input == 'More_prots':
return render(request, 'phylogenetic_trees/warning.html')
request.session['Tree'] = Tree_class
# output dictionary
data = {}
data['tree'] = Tree_class.phylip.replace('\n', '')
# context['protein_data'] = protein_data
protein_entries = []
for pc in proteins:
protein_entries.append(pc.protein.entry_name)
# load all
cluster_method = 0
# Collect structure annotations
protein_annotations = {}
# Grab all annotations and all the ligand role when present in aggregates
# NOTE: we can probably remove the parent step and go directly via family in the query
annotations = Protein.objects.filter(entry_name__in=protein_entries) \
.values_list('entry_name', 'name', 'family__parent__name', 'family__parent__parent__name', 'family__parent__parent__parent__name', 'family__slug', 'name')
protein_slugs = set()
for an in annotations:
protein_annotations[an[0]] = list(an[1:])
# add slug to lists
slug = protein_annotations[an[0]][4]
protein_slugs.add(slug)
data['annotations'] = protein_annotations
# Grab G-protein coupling profile for all receptors covered by the selection
# TODO: make general cache(s) for these kinds of data
selectivitydata = {}
coupling = ProteinGProteinPair.objects.filter(
protein__family__slug__in=protein_slugs,
source="GuideToPharma").values_list(
'protein__family__slug',
'transduction').annotate(arr=ArrayAgg('g_protein__name'))
for pairing in coupling:
if pairing[0] not in selectivitydata:
selectivitydata[pairing[0]] = {}
selectivitydata[pairing[0]][pairing[1]] = pairing[2]
data['Gprot_coupling'] = selectivitydata
context = {}
context["data"] = json.dumps(data)
return render(request, 'phylogenetic_trees/phylo_tree.html', context)