So we're having a rather heated debate at work as to what would be the best practice going forward while refactoring our reporting system. Most of us have some strong opinions one way or the other, but we are all also mostly failing at articulating what we feel would be the best approach. I'm mostly looking for some input to see what approach would result in the best solution overall for us.
Our current system is relatively straightforward, but it's comprised of quite a bit of spaghetti-code. We have roughly 10-15 dashboards/reports with about 20 metrics on them each. Some of these metrics are shared across reports, but a lot are unique to each report. As such, there are some report filters that are common on most/all reports, and other filters that are specific to a given report.
In our controllers, we have different objects that accept the report parameters, based on what filters are present. So, for example, we have an object called QueryParams
, this object contains filters for StartDate
, EndDate
, Company
, and Division
. On another dashboard, we have a different parameter object that accepts all of the aforementioned parameters, but also includes Provider
and Gender
. And on another dashboard, we may have almost all of the same parameters as before, but instead they don't need the start/end date parameters.
This has lead to our parameter objects not really being used passed our WebUI project. Once we get to the logic and repository layers, our method signatures are simply looking for all the filters. So rather than GetVisitCounts(QueryParams)
we have `GetVisitCounts(StartDate, EndDate, Company, Division). This isn't too bad, except for the fact that these filters get updated at a non-trivial frequency. When it comes time to add some new filters to a report, developers find themselves going through each layer of the project and each method to update the signature and underlying logic.
Once you get to the repository layer, most of the parameters are handled the same way. Some SQL is written and where clauses are generated based on the parameters passed in to the method. This SQL is pretty much entirely handwritten in every single method. In some cases the where clauses can be dynamically generated as the same tables are being used with the same aliases, but it's rough to make this call for all methods as there are a lot of one-offs.
Now we find ourselves needing to add 3-4 filters to all of our dashboards, and this requires us to go through and update everything. There are some proposed solutions for simplifying everything, but none of them seem super ideal. One such idea is to create a master QueryParams
object that houses every possible filter across every possible report. This object would just be a super class that acts like a data store. It'd simplify the method signatures, but actual implementation of the repository methods would still require everything to be generated by hand.
A second approach was to use QueryParams
as a base class, and built subclasses that add additional parameters as we go along. Using this approach we could build some sort of adapter that attempts to serialize the parameter object into a SQL where clause. However, this is not ideal either as there are many one-off cases where the tables are following non-standard joins, or we're only using a couple of the parameters for some reason and we can't include the parameters in the where clause.
I'm open to any potential solutions that could simplify our current approach, even if a near full rewrite is necessary.
where
clause (or an additional AND condition to the existing where clauses) to the SQLs?AND
condition.