Totalise the data of an insurance branch

I've inherited a piece of legacy code that's pretty huge, and I've no idea how to go about breaking it into smaller pieces, and how to make the DB queries more efficient.

The project is built on a vanilla Python back-end with no ORM, hence raw SQL queries, the purpose of the function is to totalize the data of an insurance branch. An insurance branch has a branch_id which can be cross referenced against an Insurance Company ref_no to return a data set. The data is then broken into an overall total category as well as several time periods.

I've added a multiple choice option on the front end, and want to adjust the back-end accordingly to totalize the selection of multiple branch_ids, but I think it's probably better to try and break this down first.

I'm feeling pretty overwhelmed at the moment, I've spent the afternoon printing and trying to make sense of what each part of the function is doing, but I've no idea how to take this on without breaking so many things.

Help me out with a strategy please.

```
def company_summary_totals(self, ref_no, branch_id):

if branch_id == "*":
branch_id = "%"

# If the universal group has been chosen, change the ref_no to be the same as the field name.
# This means that the query will have this inside it: 'Insurance_No = Insurance_No' which will get everything
if int(ref_no) == 999:
ref_no = '%'

reports = {
"total": "SELECT count(*) as count, ceil(datediff(date(now()), date(in_yard)) / 30) as date_range FROM vhistory WHERE INSURANCE_No LIKE %(ref_no)s AND INSURANCE_No != 998 AND RefSfx LIKE %(ref_sfx)s AND Live='y' AND STATUS = 'IN YARD NOT CLEARED' group by date_range",
"sfos": "SELECT IFNULL(SUM(Storage_Fee_OS),0) as total, count(*) as count, ceil(datediff(date(now()), date(in_yard)) / 30) as date_range FROM vhistory WHERE INSURANCE_No LIKE %(ref_no)s AND INSURANCE_No != 998 AND RefSfx LIKE %(ref_sfx)s AND Live='y'AND Storage_Fee_OS > 0 group by date_range"

Overall, I see this as an SQL question and not Python where the latter simply runs the query. While this does not align with your end output, namely nested Python dictionaries, consider a tabular output with an aggregate SQL query where everything is combined together using conditional aggregates for category counts and totals. Basically, the individual WHERE clauses are moved to CASE WHEN statements.

SELECT ceil(datediff(date(now()), date(in_yard)) / 30) as date_range ,

SUM(CASE WHEN Storage_Fee_OS > 0 THEN 1 ELSE 0 END) as sfos_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) as sfos_total,

SUM(CASE WHEN Cheque_sent IS NULL THEN 1 ELSE 0 END) as tc_count,
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) as tc_total,

SUM(CASE WHEN AIInvoiced IS NOT NULL THEN 1 ELSE 0 END) as aois_count,
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as aois_total

SUM(CASE WHEN STATUS = 'IN YARD NOT CLEARED' THEN 1 ELSE 0 END) as total_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) +
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) +
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) total_sum

FROM vhistory
WHERE INSURANCE_No = %(ref_sfx)s AND INSURANCE_No != 998
GROUP BY ceil(datediff(date(now()), date(in_yard)) / 30)


And pass the ref_sfx as a parameter to filter individual branches:

strSQL = '''...
WHERE INSURANCE_No = ? AND INSURANCE_No != 998
...'''

cur.exec(strSQL, branch_id)


And to add date ranges, use a union query filtered on the 30-day calculated column multiple where each SELECT block adds a WHERE condition on date_range (30-day multiples). While this may seem a long query, an individual branch can at most return four records each.

`-- 30 DAYS
SELECT '0 - 30 days' as title,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN 1 ELSE 0 END) as sfos_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) as sfos_total,

SUM(CASE WHEN Cheque_sent IS NULL THEN 1 ELSE 0 END) as tc_count,
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) as tc_total,

SUM(CASE WHEN AIInvoiced IS NOT NULL THEN 1 ELSE 0 END) as aois_count,
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as aois_total,

SUM(CASE WHEN STATUS = 'IN YARD NOT CLEARED' THEN 1 ELSE 0 END) as total_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) +
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) +
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as total_sum

FROM vhistory
WHERE INSURANCE_No = %(ref_sfx)s AND INSURANCE_No != 998
AND ceil(datediff(date(now()), date(in_yard)) / 30) = 1

UNION ALL

-- 60 DAYS
SELECT '31 - 60 days' as title,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN 1 ELSE 0 END) as sfos_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) as sfos_total,

SUM(CASE WHEN Cheque_sent IS NULL THEN 1 ELSE 0 END) as tc_count,
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) as tc_total,

SUM(CASE WHEN AIInvoiced IS NOT NULL THEN 1 ELSE 0 END) as aois_count,
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as aois_total,

SUM(CASE WHEN STATUS = 'IN YARD NOT CLEARED' THEN 1 ELSE 0 END) as total_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) +
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) +
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as total_sum

FROM vhistory
WHERE INSURANCE_No = %(ref_sfx)s AND INSURANCE_No != 998
AND ceil(datediff(date(now()), date(in_yard)) / 30) = 2

UNION ALL

-- 90 DAYS
SELECT '61 - 90 days' as title,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN 1 ELSE 0 END) as sfos_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) as sfos_total,

SUM(CASE WHEN Cheque_sent IS NULL THEN 1 ELSE 0 END) as tc_count,
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) as tc_total,

SUM(CASE WHEN AIInvoiced IS NOT NULL THEN 1 ELSE 0 END) as aois_count,
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as aois_total,

SUM(CASE WHEN STATUS = 'IN YARD NOT CLEARED' THEN 1 ELSE 0 END) as total_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) +
SUM(CASE WHEN Cheque_sent IS NULL THEN totalcost ELSE 0 END) +
SUM(CASE WHEN AIInvoiced IS NOT NULL THEN AI_OS ELSE 0 END) as total_sum

FROM vhistory
WHERE INSURANCE_No = %(ref_sfx)s AND INSURANCE_No != 998
AND ceil(datediff(date(now()), date(in_yard)) / 30) = 3

UNION ALL

-- OVER 90 DAYS
SELECT 'Over 90 days' as title,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN 1 ELSE 0 END) as sfos_count,
SUM(CASE WHEN Storage_Fee_OS > 0 THEN Storage_Fee_OS ELSE 0 END) as sfos_t

StackExchange Code Review Q#140651, answer score: 3