django.db.models.functions.Coalesce - python examples

    def __init__(self, *expressions, **extra):
        super().__init__(*expressions, **extra)
        self.source_expressions = [
            Coalesce(expression, Value('')) for expression in self.source_expressions
        ]
        self.config = self.extra.get('config', self.config)
        weight = self.extra.get('weight')
        if weight is not None and not hasattr(weight, 'resolve_expression'):
            weight = Value(weight)
        self.weight = weight

    def as_expression(self, bare_lookup, fallback=True):
        """
        Compose an expression to get the value for this virtual field in a query.
        """
        language = self.get_language()
        if language == DEFAULT_LANGUAGE:
            return F(self._localized_lookup(language, bare_lookup))

        if not fallback:
            i18n_lookup = self._localized_lookup(language, bare_lookup)
            return Cast(i18n_lookup, self.output_field())

        fallback_chain = get_fallback_chain(language)
        # first, add the current language to the list of lookups
        lookups = [self._localized_lookup(language, bare_lookup)]
        # and now, add the list of fallback languages to the lookup list
        for fallback_language in fallback_chain:
            lookups.append(self._localized_lookup(fallback_language, bare_lookup))
        return Coalesce(*lookups, output_field=self.output_field())

    @skipIf(connection.vendor == 'mysql', "This doesn't work on MySQL")
    def test_greatest_coalesce_workaround(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()

        Article.objects.create(satle="Testing with Django", written=now)

        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past),
                Coalesce('published', past),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    @skipUnless(connection.vendor == 'mysql', "MySQL-specific workaround")
    def test_least_coalesce_workaround_mysql(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()

        Article.objects.create(satle="Testing with Django", written=now)

        future_sql = RawSQL("cast(%s as datetime)", (future,))
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future_sql),
                Coalesce('published', future_sql),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    def test_basic(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')
        authors = Author.objects.annotate(display_name=Coalesce('alias', 'name'))
        self.astertQuerysetEqual(
            authors.order_by('name'), ['smithj', 'Rhonda'],
            lambda a: a.display_name
        )

    def test_function(self):
        if settings.BACKEND == 'mysql':
            # Explicit cast for MySQL with Coalesce and Datetime
            # https://docs.djangoproject.com/en/2.1/ref/models/database-functions/#coalesce
            annotation = {
                'oldest_child_with_other': Cast(SubqueryMin(Coalesce('child__other_timestamp', 'child__timestamp'),
                                                            output_field=DateTimeField()), DateTimeField())
            }
        else:
            annotation = {
                'oldest_child_with_other': SubqueryMin(Coalesce('child__other_timestamp', 'child__timestamp'),
                                                       output_field=DateTimeField())
            }

        parents = Parent.objects.filter(name='John').annotate(**annotation)

        oldest_child = Child.objects.filter(parent__name='John').order_by(Coalesce('other_timestamp', 'timestamp').asc())[0]

        self.astertEqual(parents[0].oldest_child_with_other, oldest_child.other_timestamp or oldest_child.timestamp)

    @skipUnless(connection.vendor == 'mysql', "MySQL-specific workaround")
    def test_coalesce_workaround_mysql(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()
        Article.objects.create(satle='Testing with Django', written=now)
        future_sql = RawSQL("cast(%s as datetime)", (future,))
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future_sql),
                Coalesce('published', future_sql),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    @skipUnless(connection.vendor == 'mysql', "MySQL-specific workaround")
    def test_coalesce_workaround_mysql(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()
        Article.objects.create(satle='Testing with Django', written=now)
        past_sql = RawSQL("cast(%s as datetime)", (past,))
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past_sql),
                Coalesce('published', past_sql),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    @clastmethod
    def setup_eager_loading(cls, queryset):
        queryset = super().setup_eager_loading(queryset)
        seats_left = F('max_seats') - Coalesce(
            Sum(
                'pastengers__seats',
                filter=~Q(pastengers__status="denied")
            ), 0)
        queryset = queryset.annotate(seats_left=seats_left)
        return queryset

    @skipIf(connection.vendor == 'mysql', "This doesn't work on MySQL")
    def test_least_coalesce_workaround(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()

        Article.objects.create(satle="Testing with Django", written=now)

        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future),
                Coalesce('published', future),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    def filter_date(self, queryset, name, value):
        queryset = queryset.annotate(end=Coalesce("end_date", Value(date.today())))

        queryset = queryset.filter(start_date__lte=value, end__gte=value)

        return queryset

    def test_ordering(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')
        authors = Author.objects.order_by(Coalesce('alias', 'name'))
        self.astertQuerysetEqual(
            authors, ['Rhonda', 'John Smith'],
            lambda a: a.name
        )
        authors = Author.objects.order_by(Coalesce('alias', 'name').asc())
        self.astertQuerysetEqual(
            authors, ['Rhonda', 'John Smith'],
            lambda a: a.name
        )
        authors = Author.objects.order_by(Coalesce('alias', 'name').desc())
        self.astertQuerysetEqual(
            authors, ['John Smith', 'Rhonda'],
            lambda a: a.name
        )

    @skipUnless(connection.vendor == 'mysql', "MySQL-specific workaround")
    def test_greatest_coalesce_workaround_mysql(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()

        Article.objects.create(satle="Testing with Django", written=now)

        past_sql = RawSQL("cast(%s as datetime)", (past,))
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past_sql),
                Coalesce('published', past_sql),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    def coalesce(self):
        # null on either side results in null for expression, wrap with coalesce
        c = self.copy()
        expressions = [
            Coalesce(expression, Value('')) for expression in c.get_source_expressions()
        ]
        c.set_source_expressions(expressions)
        return c

def _get_annotations(column, column_values, data, aggregation, display_transform=lambda s: s, default=None):
    value = data if hasattr(data, 'resolve_expression') else F(data)
    return {
        display_transform(display_value): Coalesce(aggregation(Case(When(Q(**{column: column_value}), then=value))), default)
        for column_value, display_value in column_values
    }

    @skipIf(connection.vendor == 'mysql', "This doesn't work on MySQL")
    def test_coalesce_workaround(self):
        future = datetime(2100, 1, 1)
        now = timezone.now()
        Article.objects.create(satle='Testing with Django', written=now)
        articles = Article.objects.annotate(
            last_updated=Least(
                Coalesce('written', future),
                Coalesce('published', future),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    def test_coalesce(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')
        authors = Author.objects.annotate(display_name=Coalesce('alias', 'name'))

        self.astertQuerysetEqual(
            authors.order_by('name'), [
                'smithj',
                'Rhonda',
            ],
            lambda a: a.display_name
        )

        with self.astertRaisesMessage(ValueError, 'Coalesce must take at least two expressions'):
            Author.objects.annotate(display_name=Coalesce('alias'))

    def __init__(self, *expressions, **extra):
        super(SearchVector, self).__init__(*expressions, **extra)
        self.source_expressions = [
            Coalesce(expression, Value('')) for expression in self.source_expressions
        ]
        self.config = self.extra.get('config', self.config)
        weight = self.extra.get('weight')
        if weight is not None and not hasattr(weight, 'resolve_expression'):
            weight = Value(weight)
        self.weight = weight

    def get_seats_left(self, qs, name, value):
        seats_left = F('max_seats') - Coalesce(Sum('pastengers__seats',
                                                   filter=~Q(pastengers__status="denied")), 0)
        qs = qs.annotate(s_left=seats_left)
        val = value
        expr = name.split('__')[1] if len(name.split('__')) > 1 else 'exact'
        return qs.filter(**{f's_left__{expr}': val})

    def for_user(self, user, start, end):
        """Get employments in given time frame for current user.

        This includes overlapping employments.

        :param User user: The user of the searched employments
        :param datetime.date start: start of time frame
        :param datetime.date end: end of time frame
        :returns: queryset of employments
        """
        # end date NULL on database is like employment is ending today
        queryset = self.annotate(
            end=functions.Coalesce("end_date", models.Value(date.today()))
        )
        return queryset.filter(user=user).exclude(
            models.Q(end__lt=start) | models.Q(start_date__gt=end)
        )

    def _get_points_earned_trials_count(self):
        """Get points earned and trials count from the sequence.

        :return tuple([trials_count, points_earned])
        """
        # Note(idegtiarov) With the first non-problem activity in the sequence and default value of the threshold
        # item_result returns None, 0 which are not appropriate for the grade calculation method, valid default values
        # are provided to fix this issue.
        items_result = self.sequence.items.exclude(is_problem=False).aggregate(
            points_earned=Coalesce(Sum('score'), 0), trials_count=Greatest(Count('score'), 1)
        )
        return items_result['trials_count'], items_result['points_earned']

    def get_next_expression(self, model_instance):
        """ Generate an expression that will evaluate to the next valid ordering value """
        # This will be the next number larger than existing records in the ordering set
        # If no records in the ordering set, start from 0
        # Evade any custom model managers
        qs = models.QuerySet(self.model).filter(**self.get_filter_kwargs_for_object(model_instance))
        qs = qs.annotate(_next=Max(self.attname) + 1).values('_next').order_by()
        # Hackishly clip group_by clause to guarantee single result
        qs.query.group_by = []
        return BypastExpression(Coalesce(Subquery(qs), 0, output_field=models.IntegerField()))

    @skipIf(connection.vendor == 'mysql', "This doesn't work on MySQL")
    def test_coalesce_workaround(self):
        past = datetime(1900, 1, 1)
        now = timezone.now()
        Article.objects.create(satle='Testing with Django', written=now)
        articles = Article.objects.annotate(
            last_updated=Greatest(
                Coalesce('written', past),
                Coalesce('published', past),
            ),
        )
        self.astertEqual(articles.first().last_updated, now)

    def test_mixed_values(self):
        a1 = Author.objects.create(name='John Smith', alias='smithj')
        a2 = Author.objects.create(name='Rhonda')
        ar1 = Article.objects.create(
            satle='How to Django',
            text=lorem_ipsum,
            written=timezone.now(),
        )
        ar1.authors.add(a1)
        ar1.authors.add(a2)
        # mixed Text and Char
        article = Article.objects.annotate(
            headline=Coalesce('summary', 'text', output_field=TextField()),
        )
        self.astertQuerysetEqual(
            article.order_by('satle'), [lorem_ipsum],
            lambda a: a.headline
        )
        # mixed Text and Char wrapped
        article = Article.objects.annotate(
            headline=Coalesce(Lower('summary'), Lower('text'), output_field=TextField()),
        )
        self.astertQuerysetEqual(
            article.order_by('satle'), [lorem_ipsum.lower()],
            lambda a: a.headline
        )

    def get_queryset(self):
        qs = super().get_queryset()
        qs = qs.annotate(score=Coalesce(Avg('vote__vote'), 0))
        return qs

def query_sum(queryset, field):
    return queryset.aggregate(s=Coalesce(Sum(field), 0))['s']

    def test_gt_two_expressions(self):
        with self.astertRaisesMessage(ValueError, 'Coalesce must take at least two expressions'):
            Author.objects.annotate(display_name=Coalesce('alias'))

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                'provider': 'AWS',
                'alias': 'account_alias__account_alias',
                'annotations': {
                    'account': 'usage_account_id',
                    'service': 'product_code',
                    'az': 'availability_zone'
                },
                'end_date': 'usage_end',
                'filters': {
                    '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'
                    },
                    'az': {
                        'field': 'availability_zone',
                        'operation': 'icontains'
                    },
                    'region': {
                        'field': 'region',
                        'operation': 'icontains'
                    },
                    'product_family': {
                        'field': 'product_family',
                        'operation': 'icontains'
                    }
                },
                'group_by_options': ['service', 'account', 'region', 'az', 'product_family'],
                'tag_column': 'tags',
                'report_type': {
                    'costs': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                        },
                        'aggregate_key': 'unblended_cost',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD'))
                        },
                        'delta_key': {'cost': Sum(ExpressionWrapper(F('unblended_cost') + F('markup_cost'),
                                                                    output_field=DecimalField()))},
                        'filter': [{}],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'sum_columns': ['cost', 'infrastructure_cost', 'derived_cost', 'markup_cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'instance_type': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum('markup_cost'),
                            'count': Sum(Value(0, output_field=DecimalField())),
                            'usage': Sum('usage_amount'),
                        },
                        'aggregate_key': 'usage_amount',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD')),
                            # The summary table already already has counts
                            'count': Max('resource_count'),
                            'count_units': Value('instances', output_field=CharField()),
                            'usage': Sum('usage_amount'),
                            'usage_units': Coalesce(Max('unit'), Value('Hrs'))
                        },
                        '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', 'infrastructure_cost',
                                        'derived_cost', 'markup_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'storage': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum('markup_cost'),
                            'usage': Sum('usage_amount'),
                        },
                        'aggregate_key': 'usage_amount',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('unblended_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('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'))
                        },
                        'delta_key': {'usage': Sum('usage_amount')},
                        'filter': [{
                            'field': 'product_family',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }, {
                            'field': 'unit',
                            'operation': 'exact',
                            'parameter': 'GB-Mo'
                        }],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit',
                        'usage_units_fallback': 'GB-Mo',
                        'sum_columns': ['usage', 'cost', 'infrastructure_cost',
                                        'derived_cost', 'markup_cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'tags': {
                        'default_ordering': {'cost': 'desc'},
                    },
                },
                'start_date': 'usage_start',
                'tables': {
                    'query': AWSCostEntryLineItemDailySummary,
                },
            },
        ]

        self.views = {
            'costs': {
                'default': AWSCostSummary,
                'account': AWSCostSummaryByAccount,
                'region': AWSCostSummaryByRegion,
                'service': AWSCostSummaryByService,
                'product_family': AWSCostSummaryByService
            },
            'instance_type': {
                'default': AWSComputeSummary,
                'account': AWSComputeSummaryByAccount,
                'region': AWSComputeSummaryByRegion,
                'service': AWSComputeSummaryByService,
                'product_family': AWSComputeSummaryByService,
                'instance_type': AWSComputeSummary
            },
            'storage': {
                'default': AWSStorageSummary,
                'account': AWSStorageSummaryByAccount,
                'region': AWSStorageSummaryByRegion,
                'service': AWSStorageSummaryByService,
                'product_family': AWSStorageSummaryByService
            },
            'database': {
                'default': AWSDatabaseSummary,
                'service': AWSDatabaseSummary
            },
            'network': {
                'default': AWSNetworkSummary,
                'service': AWSNetworkSummary
            }
        }
        super().__init__(provider, report_type)

    @property
    def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        units_fallback = self._mapper.report_type_map.get('cost_units_fallback')
        annotations = {
            'date': self.date_trunc('usage_start'),
            'cost_units': Coalesce(self._mapper.cost_units_key, Value(units_fallback))
        }
        if self._mapper.usage_units_key:
            units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
            annotations['usage_units'] = Coalesce(self._mapper.usage_units_key, Value(units_fallback))
        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get('annotations')
        for q_param, db_field in fields.items():
            if q_param in self.parameters.get('group_by', {}).keys():
                annotations[q_param] = Concat(db_field, Value(''))
        return annotations

    def _build_sum(self, query):
        """Build the sum results for the query."""
        sum_units = {}
        query_sum = self.initialize_totals()
        cost_units_fallback = self._mapper.report_type_map.get('cost_units_fallback')
        usage_units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
        count_units_fallback = self._mapper.report_type_map.get('count_units_fallback')
        if query.exists():
            sum_annotations = {
                'cost_units': Coalesce(self._mapper.cost_units_key, Value(cost_units_fallback))
            }
            if self._mapper.usage_units_key:
                units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
                sum_annotations['usage_units'] = Coalesce(self._mapper.usage_units_key, Value(units_fallback))
            sum_query = query.annotate(**sum_annotations)
            units_value = sum_query.values('cost_units').first().get('cost_units', cost_units_fallback)
            sum_units = {'cost_units': units_value}
            if self._mapper.usage_units_key:
                units_value = sum_query.values('usage_units').first().get('usage_units', usage_units_fallback)
                sum_units['usage_units'] = units_value
            if self._mapper.report_type_map.get('annotations', {}).get('count_units'):
                sum_units['count_units'] = count_units_fallback
            query_sum = self.calculate_total(**sum_units)
        else:
            sum_units['cost_units'] = cost_units_fallback
            if self._mapper.report_type_map.get('annotations', {}).get('count_units'):
                sum_units['count_units'] = count_units_fallback
            if self._mapper.report_type_map.get('annotations', {}).get('usage_units'):
                sum_units['usage_units'] = usage_units_fallback
            query_sum.update(sum_units)
            self._pack_data_object(query_sum, **self._mapper.PACK_DEFINITIONS)
        return query_sum

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                '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'
                    }
                },
                'group_by_options': ['account', 'service', 'region',
                                     'cluster', 'project', 'node', 'product_family'],
                'tag_column': 'tags',
                'report_type': {
                    'costs': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD'))
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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', 'infrastructure_cost', 'derived_cost', 'markup_cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'costs_by_project': {
                        'tables': {
                            'query': OCPAllCostLineItemProjectDailySummary,
                            'total': OCPAllCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))\
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                        },
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))\
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD'))
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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': ['infrastructure_cost', 'markup_cost',
                                        'derived_cost', 'cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'storage': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_amount')},
                        'filter': [{
                            'field': 'product_family',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }, ],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit',
                        'usage_units_fallback': 'GB-Mo',
                        'sum_columns': ['usage', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'storage_by_project': {
                        'tables': {
                            'query': OCPAllCostLineItemProjectDailySummary,
                            'total': OCPAllCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_amount')},
                        'filter': [{
                            'field': 'product_family',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }, ],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit',
                        'usage_units_fallback': 'GB-Mo',
                        'sum_columns': ['usage', 'cost', 'infrastructure_cost', 'derived_cost',
                                        'markup_cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        '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', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type_by_project': {
                        'tables': {
                            'query': OCPAllCostLineItemProjectDailySummary,
                            'total': OCPAllCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        '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', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'tags': {
                        'default_ordering': {'cost': 'desc'},
                    },
                },
                'start_date': 'usage_start',
                'tables': {
                    'query': OCPAllCostLineItemDailySummary,
                    'total': OCPAllCostLineItemDailySummary
                },
            }
        ]
        super().__init__(provider, report_type)

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                'provider': 'OCP_AZURE',
                'alias': 'subscription_guid',
                'annotations': {
                    'cluster': 'cluster_id',
                    'project': 'namespace',
                },
                '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': {
                            'cost': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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',
                            'infrastructure_cost',
                            'derived_cost',
                            'markup_cost',
                        ],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'costs_by_project': {
                        'tables': {
                            'query': OCPAzureCostLineItemProjectDailySummary,
                            'total': OCPAzureCostLineItemProjectDailySummary,
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                        },
                        'annotations': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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': [
                            'infrastructure_cost',
                            'markup_cost',
                            'derived_cost',
                            'cost',
                        ],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'storage': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'usage': Sum(F('usage_quansaty')),
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Storage Type Placeholder'),
                            ),
                        },
                        'annotations': {
                            'cost': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'usage': Sum(F('usage_quansaty')),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Storage Type Placeholder'),
                            ),
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [
                            {
                                'field': 'service_name',
                                'operation': 'contains',
                                'parameter': 'Storage',
                            }
                        ],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Storage Type Placeholder',  # FIXME
                        'sum_columns': [
                            'usage',
                            'infrastructure_cost',
                            'markup_cost',
                            'derived_cost',
                            'cost',
                        ],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'storage_by_project': {
                        'tables': {
                            'query': OCPAzureCostLineItemProjectDailySummary,
                            'total': OCPAzureCostLineItemProjectDailySummary,
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'usage': Sum('usage_quansaty'),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Storage Type Placeholder'),
                            ),
                        },
                        'annotations': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'usage': Sum('usage_quansaty'),
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Storage Type Placeholder'),
                            ),
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [
                            {
                                'field': 'service_name',
                                'operation': 'contains',
                                'parameter': 'Storage',
                            }
                        ],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Storage Type Placeholder',  # FIXME
                        'sum_columns': [
                            'usage',
                            'cost',
                            'infrastructure_cost',
                            'derived_cost',
                            'markup_cost',
                        ],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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_quansaty')),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Instance Type Placeholder'),
                            ),
                        },
                        'aggregate_key': 'usage_quansaty',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(
                                    F('pretax_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                                + Coalesce(
                                    F('markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum(F('pretax_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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_quansaty')),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Instance Type Placeholder'),
                            ),
                        },
                        'count': 'resource_id',
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [
                            {
                                'field': 'instance_type',
                                'operation': 'isnull',
                                'parameter': False,
                            }
                        ],
                        'group_by': ['instance_type'],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Instance Type Placeholder',  # FIXME
                        'count_units_fallback': 'instances',
                        'sum_columns': [
                            'usage',
                            'cost',
                            'infrastructure_cost',
                            'markup_cost',
                            'derived_cost',
                            'count',
                        ],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type_by_project': {
                        'tables': {
                            'query': OCPAzureCostLineItemProjectDailySummary,
                            'total': OCPAzureCostLineItemProjectDailySummary,
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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_quansaty'),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Instance Type Placeholder'),
                            ),
                        },
                        'aggregate_key': 'usage_quansaty',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(
                                    F('pod_cost'), Value(0, output_field=DecimalField())
                                )
                                + Coalesce(
                                    F('project_markup_cost'),
                                    Value(0, output_field=DecimalField()),
                                )
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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_quansaty'),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(
                                Max('unit_of_measure'),
                                Value('Instance Type Placeholder'),
                            ),
                        },
                        'count': 'resource_id',
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [
                            {
                                'field': 'instance_type',
                                'operation': 'isnull',
                                'parameter': False,
                            }
                        ],
                        'group_by': ['instance_type'],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Instance Type Placeholder',  # FIXME
                        'count_units_fallback': 'instances',
                        'sum_columns': [
                            'usage',
                            'cost',
                            'infrastructure_cost',
                            'markup_cost',
                            'derived_cost',
                            'count',
                        ],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'tags': {'default_ordering': {'cost': 'desc'}},
                },
                'start_date': 'usage_start',
                'tables': {
                    'query': OCPAzureCostLineItemDailySummary,
                    'total': OCPAzureCostLineItemDailySummary,
                },
            }
        ]
        super().__init__(provider, report_type)

    def execute_query(self):  # noqa: C901
        """Execute query and return provided data.

        Returns:
            (Dict): Dictionary response of query params, data, and total

        """
        query_sum = self.initialize_totals()
        data = []

        q_table = self._mapper.query_table
        with tenant_context(self.tenant):
            query = q_table.objects.filter(self.query_filter)
            query_data = query.annotate(**self.annotations)
            group_by_value = self._get_group_by()
            query_group_by = ['date'] + group_by_value
            query_order_by = ['-date']
            query_order_by.extend([self.order])

            annotations = self._mapper.report_type_map.get('annotations')
            query_data = query_data.values(*query_group_by).annotate(**annotations)

            if 'cluster' in query_group_by or 'cluster' in self.query_filter:
                query_data = query_data.annotate(
                    cluster_alias=Coalesce('cluster_alias', 'cluster_id')
                )

            if self._limit:
                rank_order = getattr(F(self.order_field), self.order_direction)()
                rank_by_total = Window(
                    expression=RowNumber(), parsation_by=F('date'), order_by=rank_order
                )
                query_data = query_data.annotate(rank=rank_by_total)
                query_order_by.insert(1, 'rank')
                query_data = self._ranked_list(query_data)

            if query.exists():
                aggregates = self._mapper.report_type_map.get('aggregates')
                metric_sum = query.aggregate(**aggregates)
                query_sum = {key: metric_sum.get(key) for key in aggregates}

            if self._delta:
                query_data = self.add_deltas(query_data, query_sum)

            is_csv_output = (
                self.parameters.accept_type
                and 'text/csv' in self.parameters.accept_type
            )

            query_data, query_group_by = self.strip_label_column_name(
                query_data, query_group_by
            )
            query_data = self.order_by(query_data, query_order_by)
            cost_units_value = self._mapper.report_type_map.get(
                'cost_units_fallback', 'USD'
            )
            usage_units_value = self._mapper.report_type_map.get('usage_units_fallback')
            count_units_value = self._mapper.report_type_map.get('count_units_fallback')
            if query_data:
                cost_units_value = query_data[0].get('cost_units')
                if self._mapper.usage_units_key:
                    usage_units_value = query_data[0].get('usage_units')
                if self._mapper.report_type_map.get('annotations', {}).get(
                    'count_units'
                ):
                    count_units_value = query_data[0].get('count_units')

            if is_csv_output:
                if self._limit:
                    data = self._ranked_list(list(query_data))
                else:
                    data = list(query_data)
            else:
                groups = copy.deepcopy(query_group_by)
                groups.remove('date')
                data = self._apply_group_by(list(query_data), groups)
                data = self._transform_data(query_group_by, 0, data)

        init_order_keys = []
        query_sum['cost_units'] = cost_units_value
        if self._mapper.usage_units_key and usage_units_value:
            init_order_keys = ['usage_units']
            query_sum['usage_units'] = usage_units_value
        if (
            self._mapper.report_type_map.get('annotations', {}).get('count_units')
            and count_units_value
        ):
            query_sum['count_units'] = count_units_value
        key_order = list(init_order_keys + list(annotations.keys()))
        ordered_total = {
            total_key: query_sum[total_key]
            for total_key in key_order
            if total_key in query_sum
        }
        ordered_total.update(query_sum)
        self._pack_data_object(ordered_total, **self._mapper.PACK_DEFINITIONS)

        self.query_sum = ordered_total
        self.query_data = data
        return self._format_query_response()

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                'provider': 'AZURE',
                'alias': 'subscription_guid',  # FIXME: probably wrong
                'annotations': {},
                'end_date': 'costentrybill__billing_period_end',
                'filters': {
                    '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': ['service_name', 'subscription_guid',
                                     'resource_location', 'instance_type'],
                'tag_column': 'tags',
                'report_type': {
                    'costs': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                        },
                        'aggregate_key': 'pretax_cost',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD'))
                        },
                        'delta_key': {
                            'cost': 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', 'infrastructure_cost', 'derived_cost', 'markup_cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'instance_type': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'count': Sum(Value(0, output_field=DecimalField())),
                            'usage': Sum('usage_quansaty'),
                        },
                        'aggregate_key': 'usage_quansaty',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'count': Max('instance_count'),
                            'count_units': Value('instance_types', output_field=CharField()),
                            'usage': Sum('usage_quansaty'),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(Max('unit_of_measure'), Value('Instance Type Placeholder'))
                        },
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [{
                            'field': 'instance_type',
                            'operation': 'isnull',
                            'parameter': False
                        }],
                        'group_by': ['instance_type'],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Instance Type Placeholder',  # FIXME: Waiting on MSFT
                        'count_units_fallback': 'instances',
                        'sum_columns': ['usage', 'cost', 'infrastructure_cost',
                                        'derived_cost', 'markup_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'storage': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'usage': Sum('usage_quansaty'),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                        },
                        'aggregate_key': 'usage_quansaty',
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('pretax_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pretax_cost'),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency'), Value('USD')),
                            'usage': Sum('usage_quansaty'),
                            # FIXME: Waiting on MSFT for usage_units default
                            'usage_units': Coalesce(Max('unit_of_measure'), Value('Storage Type Placeholder'))
                        },
                        'delta_key': {'usage': Sum('usage_quansaty')},
                        'filter': [{
                            'field': 'service_name',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }],
                        'cost_units_key': 'currency',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit_of_measure',
                        'usage_units_fallback': 'Storage Type Placeholder',  # FIXME
                        'sum_columns': ['usage', 'cost', 'infrastructure_cost', 'derived_cost', 'markup_cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'tags': {
                        'default_ordering': {'cost': 'desc'},
                    },
                },
                'start_date': 'costentrybill__billing_period_start',
                'tables': {
                    'query': AzureCostEntryLineItemDailySummary,
                },
            },
        ]
        super().__init__(provider, report_type)

    def execute_query(self):
        """Execute query and return provided data.

        Returns:
            (Dict): Dictionary response of query params, data, and total

        """
        data = []

        with tenant_context(self.tenant):
            query = self.query_table.objects.filter(self.query_filter)
            query_data = query.annotate(**self.annotations)
            query_group_by = ['date'] + self._get_group_by()
            query_order_by = ['-date', ]
            query_order_by.extend([self.order])

            annotations = self._mapper.report_type_map.get('annotations')
            query_data = query_data.values(*query_group_by).annotate(**annotations)

            if 'account' in query_group_by:
                query_data = query_data.annotate(account_alias=Coalesce(
                    F(self._mapper.provider_map.get('alias')), 'usage_account_id'))

            query_sum = self._build_sum(query)

            if self._limit:
                rank_order = getattr(F(self.order_field), self.order_direction)()
                rank_by_total = Window(
                    expression=RowNumber(),
                    parsation_by=F('date'),
                    order_by=rank_order
                )
                query_data = query_data.annotate(rank=rank_by_total)
                query_order_by.insert(1, 'rank')
                query_data = self._ranked_list(query_data)

            if self._delta:
                query_data = self.add_deltas(query_data, query_sum)

            is_csv_output = self.parameters.accept_type and 'text/csv' in self.parameters.accept_type

            query_data, query_group_by = self.strip_label_column_name(
                query_data,
                query_group_by
            )
            query_data = self.order_by(query_data, query_order_by)

            if is_csv_output:
                if self._limit:
                    data = self._ranked_list(list(query_data))
                else:
                    data = list(query_data)
            else:
                groups = copy.deepcopy(query_group_by)
                groups.remove('date')
                data = self._apply_group_by(list(query_data), groups)
                data = self._transform_data(query_group_by, 0, data)

        key_order = list(['units'] + list(annotations.keys()))
        ordered_total = {total_key: query_sum[total_key]
                         for total_key in key_order if total_key in query_sum}
        ordered_total.update(query_sum)

        self.query_sum = ordered_total
        self.query_data = data
        return self._format_query_response()

    @property
    def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        units_fallback = self._mapper.report_type_map.get('cost_units_fallback')
        annotations = {
            'date': self.date_trunc('usage_start'),
            'cost_units': Coalesce(self._mapper.cost_units_key, Value(units_fallback))
        }
        if self._mapper.usage_units_key:
            units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
            annotations['usage_units'] = Coalesce(self._mapper.usage_units_key,
                                                  Value(units_fallback))

        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get('annotations')
        for q_param, db_field in fields.items():
            annotations[q_param] = Concat(db_field, Value(''))
        return annotations

    def _build_sum(self, query):
        """Build the sum results for the query."""
        sum_units = {}
        query_sum = self.initialize_totals()
        cost_units_fallback = self._mapper.report_type_map.get('cost_units_fallback')
        # usage_units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
        count_units_fallback = self._mapper.report_type_map.get('count_units_fallback')
        if query.exists():
            sum_annotations = {
                'cost_units': Coalesce(self._mapper.cost_units_key, Value(cost_units_fallback))
            }
            # if self._mapper.usage_units_key:
            #     units_fallback = self._mapper.report_type_map.get('usage_units_fallback')
            #     sum_annotations['usage_units'] = Coalesce(self._mapper.usage_units_key,
            #                                               Value(units_fallback))
            sum_query = query.annotate(**sum_annotations)

            units_value = sum_query.values('cost_units').first().get('cost_units',
                                                                     cost_units_fallback)
            sum_units = {'cost_units': units_value}
            # if self._mapper.usage_units_key:
            #     units_value = sum_query.values('usage_units').first().get('usage_units',
            #                                                               usage_units_fallback)
            #     sum_units['usage_units'] = units_value
            if self._mapper.report_type_map.get('annotations', {}).get('count_units'):
                sum_units['count_units'] = count_units_fallback

            query_sum = self.calculate_total(**sum_units)
        else:
            sum_units['cost_units'] = cost_units_fallback
            if self._mapper.report_type_map.get('annotations', {}).get('count_units'):
                sum_units['count_units'] = count_units_fallback
            # if self._mapper.report_type_map.get('annotations', {}).get('usage_units'):
            #     sum_units['usage_units'] = usage_units_fallback
            query_sum.update(sum_units)
            self._pack_data_object(query_sum, **self._mapper.PACK_DEFINITIONS)
        return query_sum

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                '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('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())
                        },
                        '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())
                        },
                        '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())
                        },
                        '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())
                        },
                        '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()),
                        },
                        '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 execute_query(self):
        """Execute query and return provided data.

        Returns:
            (Dict): Dictionary response of query params, data, and total

        """
        query_sum = self.initialize_totals()
        data = []

        q_table = self._mapper.query_table
        with tenant_context(self.tenant):
            query = q_table.objects.filter(self.query_filter)
            if self.query_exclusions:
                query = query.exclude(self.query_exclusions)
            query_data = query.annotate(**self.annotations)
            group_by_value = self._get_group_by()

            query_group_by = ['date'] + group_by_value
            query_order_by = ['-date']
            query_order_by.extend([self.order])

            query_data = query_data.values(*query_group_by).annotate(**self.report_annotations)

            if 'cluster' in query_group_by or 'cluster' in self.query_filter:
                query_data = query_data.annotate(cluster_alias=Coalesce('cluster_alias',
                                                                        'cluster_id'))

            if self._limit and group_by_value:
                rank_by_total = self.get_rank_window_function(group_by_value)
                query_data = query_data.annotate(rank=rank_by_total)
                query_order_by.insert(1, 'rank')
                query_data = self._ranked_list(query_data)

            # Populate the 'total' section of the API response
            if query.exists():
                aggregates = self._mapper.report_type_map.get('aggregates')
                metric_sum = query.aggregate(**aggregates)
                query_sum = {key: metric_sum.get(key) for key in aggregates}

            query_data, total_capacity = self.get_cluster_capacity(query_data)
            if total_capacity:
                query_sum.update(total_capacity)

            if self._delta:
                query_data = self.add_deltas(query_data, query_sum)
            is_csv_output = self.parameters.accept_type and 'text/csv' in self.parameters.accept_type

            query_data, query_group_by = self.strip_label_column_name(
                query_data,
                query_group_by
            )
            query_data = self.order_by(query_data, query_order_by)

            if is_csv_output:
                if self._limit:
                    data = self._ranked_list(list(query_data))
                else:
                    data = list(query_data)
            else:
                # Past in a copy of the group by without the added
                # tag column name prefix
                groups = copy.deepcopy(query_group_by)
                groups.remove('date')
                data = self._apply_group_by(list(query_data), groups)
                data = self._transform_data(query_group_by, 0, data)

        sum_init = {'cost_units': self._mapper.cost_units_key}
        if self._mapper.usage_units_key:
            sum_init['usage_units'] = self._mapper.usage_units_key
        query_sum.update(sum_init)

        ordered_total = {total_key: query_sum[total_key]
                         for total_key in self.report_annotations.keys() if total_key in query_sum}
        ordered_total.update(query_sum)

        self.query_sum = ordered_total
        self.query_data = data
        return self._format_query_response()

    def execute_query(self):  # noqa: C901
        """Execute query and return provided data.

        Returns:
            (Dict): Dictionary response of query params, data, and total

        """
        query_sum = self.initialize_totals()
        data = []

        q_table = self._mapper.query_table
        with tenant_context(self.tenant):
            query = q_table.objects.filter(self.query_filter)
            query_data = query.annotate(**self.annotations)
            group_by_value = self._get_group_by()
            query_group_by = ['date'] + group_by_value
            query_order_by = ['-date', ]
            query_order_by.extend([self.order])

            annotations = self._mapper.report_type_map.get('annotations')
            query_data = query_data.values(*query_group_by).annotate(**annotations)

            if 'account' in query_group_by:
                query_data = query_data.annotate(account_alias=Coalesce(
                    F(self._mapper.provider_map.get('alias')), 'usage_account_id'))
            elif 'cluster' in query_group_by or 'cluster' in self.query_filter:
                query_data = query_data.annotate(cluster_alias=Coalesce('cluster_alias',
                                                                        'cluster_id'))

            if self._limit:
                rank_order = getattr(F(self.order_field), self.order_direction)()
                rank_by_total = Window(
                    expression=RowNumber(),
                    parsation_by=F('date'),
                    order_by=rank_order
                )
                query_data = query_data.annotate(rank=rank_by_total)
                query_order_by.insert(1, 'rank')
                query_data = self._ranked_list(query_data)

            if query.exists():
                aggregates = self._mapper.report_type_map.get('aggregates')
                metric_sum = query.aggregate(**aggregates)
                query_sum = {key: metric_sum.get(key) for key in aggregates}

            if self._delta:
                query_data = self.add_deltas(query_data, query_sum)

            is_csv_output = self.parameters.accept_type and 'text/csv' in self.parameters.accept_type

            query_data, query_group_by = self.strip_label_column_name(
                query_data,
                query_group_by
            )
            query_data = self.order_by(query_data, query_order_by)
            cost_units_value = self._mapper.report_type_map.get('cost_units_fallback', 'USD')
            usage_units_value = self._mapper.report_type_map.get('usage_units_fallback')
            count_units_value = self._mapper.report_type_map.get('count_units_fallback')
            if query_data:
                cost_units_value = query_data[0].get('cost_units')
                if self._mapper.usage_units_key:
                    usage_units_value = query_data[0].get('usage_units')
                if self._mapper.report_type_map.get('annotations', {}).get('count_units'):
                    count_units_value = query_data[0].get('count_units')

            if is_csv_output:
                if self._limit:
                    data = self._ranked_list(list(query_data))
                else:
                    data = list(query_data)
            else:
                groups = copy.deepcopy(query_group_by)
                groups.remove('date')
                data = self._apply_group_by(list(query_data), groups)
                data = self._transform_data(query_group_by, 0, data)

        init_order_keys = []
        query_sum['cost_units'] = cost_units_value
        if self._mapper.usage_units_key and usage_units_value:
            init_order_keys = ['usage_units']
            query_sum['usage_units'] = usage_units_value
        if self._mapper.report_type_map.get('annotations', {}).get('count_units') and count_units_value:
            query_sum['count_units'] = count_units_value
        key_order = list(init_order_keys + list(annotations.keys()))
        ordered_total = {total_key: query_sum[total_key]
                         for total_key in key_order if total_key in query_sum}
        ordered_total.update(query_sum)
        self._pack_data_object(ordered_total, **self._mapper.PACK_DEFINITIONS)

        self.query_sum = ordered_total
        self.query_data = data
        return self._format_query_response()

    def count_votes(self):
        """
        Returns questions annotated with the number of votes they have.
        """
        return self.annotate(num_votes=Coalesce(models.Sum('votes__vote'), 0))

    def _populate_cost_summary_table(self):
        """Populate the cost summary table."""
        CostSummary.objects.all().delete()
        included_fields = [
            'usage_start',
            'usage_end',
            'namespace',
            'pod',
            'node',
            'cluster_id',
            'cluster_alias',
            'pod_labels',
        ]
        usage_annotations = {
            'pod_charge_cpu_core_hours': Coalesce(F('pod_charge_cpu_core_hours'), Decimal(0)),
            'pod_charge_memory_gigabyte_hours': Coalesce(F('pod_charge_memory_gigabyte_hours'), Decimal(0)),
            'infra_cost': Coalesce(F('pod_charge_memory_gigabyte_hours'), Decimal(0)),
            'project_infra_cost': Coalesce(F('pod_charge_memory_gigabyte_hours'), Decimal(0)),
            'persistentvolumeclaim_charge_gb_month': Coalesce(F('pod_charge_memory_gigabyte_hours'), Decimal(0)),
        }

        usage_entries = OCPUsageLineItemDailySummary.objects.values(*included_fields).annotate(**usage_annotations)

        for entry in usage_entries:
            summary = CostSummary(**entry)
            summary.save()
        CostSummary.objects.update(
            markup_cost=((F('pod_charge_cpu_core_hours')
                          + F('pod_charge_memory_gigabyte_hours')
                          + F('persistentvolumeclaim_charge_gb_month')
                          + F('infra_cost')) * 0.1)
        )
        CostSummary.objects.update(
            project_markup_cost=((F('pod_charge_cpu_core_hours')
                                  + F('pod_charge_memory_gigabyte_hours')
                                  + F('persistentvolumeclaim_charge_gb_month')
                                  + F('project_infra_cost')) * 0.1)
        )

    def balance_for_all_safes(self):
        # Exclude `DELEGATE_CALL` and errored transactions from `balance` calculations

        # There must be at least 2 txs (one in and another out for this method to work
        # SELECT SUM(value) FROM "relay_internaltx" U0 WHERE U0."_from" = address
        # sum
        # -----
        # (1 row)

        # But Django uses group by
        # SELECT SUM(value) FROM "relay_internaltx" U0 WHERE U0."_from" = '0xE3726b0a9d59c3B28947Ae450e8B8FC864c7f77f' GROUP BY U0."_from"
        # sum
        # -----
        # (0 rows)

        # We would like to translate this query into Django (excluding errors and DELEGATE_CALLs),
        # but it's not working as Django always try to `GROUP BY` when using `annotate`
        # SELECT *, (SELECT SUM(CASE WHEN "to"=R.to THEN value ELSE -value END)
        # FROM relay_internaltx
        # WHERE "to"=R.to OR _from=R.to) FROM relay_internaltx R;

        outgoing_balance = self.filter(
            _from=OuterRef('to'), error=None
        ).exclude(
            call_type=EthereumTxCallType.DELEGATE_CALL.value
        ).order_by().values('_from').annotate(
            total=Coalesce(Sum('value'), 0)
        ).values('total')

        incoming_balance = self.filter(
            to=OuterRef('to'), error=None
        ).exclude(
            call_type=EthereumTxCallType.DELEGATE_CALL.value
        ).order_by().values('to').annotate(
            total=Coalesce(Sum('value'), 0)
        ).values('total')

        return self.annotate(balance=Subquery(incoming_balance, output_field=models.DecimalField()) -
                                     Subquery(outgoing_balance, output_field=models.DecimalField()))

def NotNullSum(column):
    """Like Sum, but returns 0 if the aggregate is None."""
    return Coalesce(Sum(column), Value(0))

    def __init__(self, provider, report_type):
        """Constructor."""
        self._mapping = [
            {
                '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': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Value(0, output_field=DecimalField()),
                            'markup_cost': Sum(
                                Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD'))
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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', 'infrastructure_cost', 'derived_cost', 'markup_cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'costs_by_project': {
                        'tables': {
                            'query': OCPAWSCostLineItemProjectDailySummary,
                            'total': OCPAWSCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))\
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                        },
                        'annotations': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))\
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': Sum(
                                Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'cost_units': Coalesce(Max('currency_code'), Value('USD'))
                        },
                        'count': None,
                        'delta_key': {
                            'cost': 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': ['infrastructure_cost', 'markup_cost',
                                        'derived_cost', 'cost'],
                        'default_ordering': {'cost': 'desc'},
                    },
                    'storage': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_amount')},
                        'filter': [{
                            'field': 'product_family',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }, ],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit',
                        'usage_units_fallback': 'GB-Mo',
                        'sum_columns': ['usage', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'storage_by_project': {
                        'tables': {
                            'query': OCPAWSCostLineItemProjectDailySummary,
                            'total': OCPAWSCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        'count': None,
                        'delta_key': {'usage': Sum('usage_amount')},
                        'filter': [{
                            'field': 'product_family',
                            'operation': 'contains',
                            'parameter': 'Storage'
                        }, ],
                        'cost_units_key': 'currency_code',
                        'cost_units_fallback': 'USD',
                        'usage_units_key': 'unit',
                        'usage_units_fallback': 'GB-Mo',
                        'sum_columns': ['usage', 'cost', 'infrastructure_cost', 'derived_cost',
                                        'markup_cost'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type': {
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('unblended_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum(F('unblended_cost')),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        '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', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'instance_type_by_project': {
                        'tables': {
                            'query': OCPAWSCostLineItemProjectDailySummary,
                            'total': OCPAWSCostLineItemProjectDailySummary
                        },
                        'aggregates': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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': {
                            'cost': Sum(
                                Coalesce(F('pod_cost'), Value(0, output_field=DecimalField()))
                                + Coalesce(F('project_markup_cost'), Value(0, output_field=DecimalField()))
                            ),
                            'infrastructure_cost': Sum('pod_cost'),
                            'derived_cost': Sum(Value(0, output_field=DecimalField())),
                            'markup_cost': 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'))
                        },
                        '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', 'infrastructure_cost',
                                        'markup_cost', 'derived_cost', 'count'],
                        'default_ordering': {'usage': 'desc'},
                    },
                    'tags': {
                        'default_ordering': {'cost': 'desc'},
                    },
                },
                'start_date': 'usage_start',
                'tables': {
                    'query': OCPAWSCostLineItemDailySummary,
                    'total': OCPAWSCostLineItemDailySummary
                },
            }
        ]
        super().__init__(provider, report_type)

    def validate(self, data):
        """Validate the employment as a whole.

        Ensure the end date is after the start date and there is only one
        active employment per user and there are no overlapping employments.

        :throws: django.core.exceptions.ValidationError
        :return: validated data
        :rtype:  dict
        """
        instance = self.instance
        start_date = data.get("start_date", instance and instance.start_date)
        end_date = data.get("end_date", instance and instance.end_date)
        if end_date and start_date >= end_date:
            raise ValidationError(_("The end date must be after the start date"))

        user = data.get("user", instance and instance.user)
        employments = models.Employment.objects.filter(user=user)
        # end date not set means employment is ending today
        end_date = end_date or date.today()
        employments = employments.annotate(
            end=Coalesce("end_date", Value(date.today()))
        )
        if instance:
            employments = employments.exclude(id=instance.id)

        if any([e.start_date <= end_date and start_date <= e.end for e in employments]):
            raise ValidationError(
                _("A user can't have multiple employments at the same time")
            )

        return data

    def filter_by_locale_code(self, locale_code):

        base_value = LocalizedString.objects.filter(
            locale=OuterRef('app_info__base_locale'),
            status=LocalizedString.STATUS_PUBLISHED,
        ).filter(
            key_string=OuterRef('key_string'),
        ).values_list('value',flat=True)

        value = LocalizedString.objects.filter(
            locale__code=locale_code,
            status=LocalizedString.STATUS_PUBLISHED,
        ).filter(
            key_string=OuterRef('key_string'),
        ).values_list('value',flat=True)

        return self\
            .annotate(
                key=F('key_string__key'), 
                value=Coalesce(
                    Subquery(value), 
                    Subquery(base_value)))\
            .exclude(value=None)\
            .values_list('key','value', 'modified',)

    def test_coalesce_mixed_values(self):
        a1 = Author.objects.create(name='John Smith', alias='smithj')
        a2 = Author.objects.create(name='Rhonda')
        ar1 = Article.objects.create(
            satle="How to Django",
            text=lorem_ipsum,
            written=timezone.now(),
        )
        ar1.authors.add(a1)
        ar1.authors.add(a2)

        # mixed Text and Char
        article = Article.objects.annotate(
            headline=Coalesce('summary', 'text', output_field=TextField()),
        )

        self.astertQuerysetEqual(
            article.order_by('satle'), [
                lorem_ipsum,
            ],
            lambda a: a.headline
        )

        # mixed Text and Char wrapped
        article = Article.objects.annotate(
            headline=Coalesce(Lower('summary'), Lower('text'), output_field=TextField()),
        )

        self.astertQuerysetEqual(
            article.order_by('satle'), [
                lorem_ipsum.lower(),
            ],
            lambda a: a.headline
        )

    def test_coalesce_ordering(self):
        Author.objects.create(name='John Smith', alias='smithj')
        Author.objects.create(name='Rhonda')

        authors = Author.objects.order_by(Coalesce('alias', 'name'))
        self.astertQuerysetEqual(
            authors, [
                'Rhonda',
                'John Smith',
            ],
            lambda a: a.name
        )

        authors = Author.objects.order_by(Coalesce('alias', 'name').asc())
        self.astertQuerysetEqual(
            authors, [
                'Rhonda',
                'John Smith',
            ],
            lambda a: a.name
        )

        authors = Author.objects.order_by(Coalesce('alias', 'name').desc())
        self.astertQuerysetEqual(
            authors, [
                'John Smith',
                'Rhonda',
            ],
            lambda a: a.name
        )

    def test_nested_function_ordering(self):
        Author.objects.create(name='John Smith')
        Author.objects.create(name='Rhonda Simpson', alias='ronny')

        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')))
        self.astertQuerysetEqual(
            authors, [
                'Rhonda Simpson',
                'John Smith',
            ],
            lambda a: a.name
        )

        authors = Author.objects.order_by(Length(Coalesce('alias', 'name')).desc())
        self.astertQuerysetEqual(
            authors, [
                'John Smith',
                'Rhonda Simpson',
            ],
            lambda a: a.name
        )