Half-open intervals

[wherget]

Hi!

I have a model like

class HalfOpen(models.Model):
    class Rating(models.IntegerChoices):
        VERY_BAD = -2, "Very bad"
        BAD = -1, "Bad"
        NEUTRAL = 0, "Neutral"
        GOOD = 1, "Good"
        VERY_GOOD = 2, "Very good"
    start_date = models.DateField(default=date.today, null=True, blank=True)
    end_date = models.DateField(null=True, blank=True)
    amount = models.IntegerField(default=0, verbose_name="A Number")
    rating = models.IntegerField(choices=Rating.choices)

Where end_date__isnull=True would signify that this model is still valid/hasn't yet ended.
How would I go about doing a TimeSeriesReport over these, i.e. amount should appear in any time series column from start_date onwards?

I kind of would have expected the following to work:

class SampleReport(ReportView):
    report_model = HalfOpen
    group_by = "rating"
    start_date_field_name = "start_date"
    end_date_field_name = "end_date"
    columns = ["rating"]
    time_series_pattern = "monthly"
    time_series_columns = [
        ComputationField.create(Sum, "amount", "total_amount", "Total"),
    ]

    chart_settings = [
        Chart(
            "Amount over Time",
            Chart.AREA,
            data_source=["total_amount"],
            title_source=["status"],
        )
    ]

that however gives me a

django.core.exceptions.ImproperlyConfigured: date_field or [start_date_field_name and end_date_field_name] must be set for <SampleReport object at 0x105f3a240>

(I'm thinking the field names get lost in get_report_generator here)

Additionally, since in a normal filter I'd write

HalfOpen.objects.filter(start_date__gte=start_date, Q(end_date__lte=end_date) | Q(end_date__isnull=True))

I'm not clear how I would hook that "OR" into _apply_queryset_options especially since these are AFAICS only for the toplevel group_by query and the Time-Series subqueries build their own in _prepare_report_dependencies.

Can I get around re-implementing most of ReportGenerator somehow?

[RamezIssac]

Hello there , thank you for the nice engaging issue.
As far as i see, there are 2 places to change in order to implement this

1 is ReportGenerator._apply_queryset_options
There is where the "main" date filtering happens, ie for the "big" queryset covering the whole duration of the report

2. django-slick-reporting/slick_reporting/generator.py

   Lines 458 to 461 in a303284

   	if self.start_date_field_name:
   	date_filter[f"{self.start_date_field_name}__gte"] = col_data.get("start_date", self.start_date)
   	if self.end_date_field_name:
   	date_filter[f"{self.end_date_field_name}__lt"] = col_data.get("end_date", self.end_date)

   
   and precisly couple of lines later
   

   django-slick-reporting/slick_reporting/generator.py

   Line 468 in a303284

   report_class.init_preparation(q_filters, date_filter)

   
   This is the filtering for each time series, you can use q_filters (ie Q()|Q() ) as you wish.

Hope this is helpful
Ramez

[wherget]

Hi! Thank you for your reply, that does indeed help.

In the meantime I opted to override get_report_data (and throw out crosstab functionality in the process), which looks to have been a bit late in the pipeline. I was hoping to avoid reimplementing _prepare_report_dependencies, but it looks like doing that breaks the least functionality.

In the meantime I also found another scenario where I would like results to keep occurring after their end_date, as a sort-of emulation of a running sum, so I'll end up in the situation where I exactly don't want date_filter to be populated at all in _prepare_report_dependencies and instead gain control over the entire filter.

I'll be trying to arrive at something along the lines of:

@staticmethod
def limit_results(query, report_start_date, report_end_date):
     return query.filter(
         Q(start_date__lte=report_end_date) | Q(start_date__isnull=True),
         Q(end_date__gte=report_start_date) | Q(end_date__isnull=True),
     )

which would be called from _apply_queryset_options as well as _prepare_report_dependencies. Maybe it'll end up not being static?

I might need this sooner than I can come up with a clean solution, but when the deadline has passed I'll try to come up with a PR. I think a convenient hook into the query system at both top-level and time-series/crosstab might be helpful, what do you think?

Thanks again!

[RamezIssac]

Really cool It would be a very good enhancement.

This new hook can be passed arguments the start_date and the end date, existing q_filters and kwargs_filters, and a context (where we are in the pipeline) because the main qs filter uses gt & lte, while in time series uses gte and lt.
and I don't think there is a benefit of having it as static, would just prevent access to "maybe" needed info for a hook.

I'm looking forward to that PR!