5
\$\begingroup\$

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",
            "tc": "SELECT IFNULL(SUM(totalcost),0) as total, count(*) as count, ceil(datediff(date(now()), date(Cleared)) / 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 Cheque_sent IS NULL group by date_range",
            "aios": "SELECT IFNULL(SUM(AI_OS),0) as total, count(*) as count, ceil(datediff(date(now()), date(aiinvoiced)) / 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 AI_OS > 0 AND AIInvoiced is not null group by date_range"
    }
    reports_keys = ['total', 'sfos', 'tc', 'aios']
    titles = ['Total', '0 - 30 days', '31 - 60 days', '61 - 90 days', 'Over 90 days']
    from_to = [(None, None), (None, 30), (31, 60), (61, 90), (91, None)]

    recs = list()
    #Set up the data structure. Need to do this before the query loop as the data goes in columns on the report, not rows
    index = 0
    for t in titles:
        # ITERATES THROUGH THE 5 AVAILABLE TIME PERIODS
        rec_to_add = dict()
        rec_to_add['title'] = t
        #rec_to_add['values'] = list()
        rec_to_add['values'] = dict()
        rec_to_add['from_to'] = from_to[index]
        for report_idx, r in enumerate(reports):
            # print("Report Index:", report_idx)
            # print("R:", r)
            rec_to_add['values'][r] = dict(total=0, count=0)
        index += 1
        recs.append(rec_to_add)
        # print("LOOK AT THE RECS", recs)


    for r in reports_keys:
        # ITERATES THROUGH THE QUERY DATA FOR REPORTS
        dbh = db(dictionary=True)
        query = reports[r]
        cur = dbh.query(query, {'ref_no': ref_no, 'ref_sfx': branch_id})
        if cur is not None:
            print("CUR:", cur)
            running_total_count = 0
            running_total_cost = 0
            print("RUNNING TOTAL COUNT:", running_total_count)
            print("RUNNING TOTAL COST:", running_total_cost)
            for c in cur:
                print("INDIVIDUAL QUERY RESULT:", c)
                if c['date_range'] is not None:
                    date_range = int(c['date_range'])
                    count = int(c.get('count', 0))
                    if 'total' in c:
                        total = float(c.get('total', 0.00))
                    else:
                        total = 0
                    running_total_count += count
                    running_total_cost += total
                    if date_range <= 3:
                        recs[date_range]['values'][r]['total'] = total
                        recs[date_range]['values'][r]['count'] = count
                    else:
                        if recs[4]['values'][r] == {}:
                            recs[4]['values'][r] = {'total': 0, 'count': 0}
                        recs[4]['values'][r]['total'] += total
                        recs[4]['values'][r]['count'] += count
            recs[0]['values'][r] = dict()
            recs[0]['values'][r]['count'] = running_total_count
            recs[0]['values'][r]['total'] = "{:10,.02f}".format(running_total_cost)
            #Format this as well
            for idx in range(1,5):
                if 'total' in recs[idx]['values'][r] and recs[idx]['values'][r]['total'] != 0:
                    recs[idx]['values'][r]['total'] = "{:10,.2f}".format(recs[idx]['values'][r]['total'])
                else:
                    recs[idx]['values'][r]['total'] = "0.00"
    print("FINAL RECS:", recs)

    return recs

Example output:

[{'from_to': (None, None), 'title': 'Total', 'values': 
 {'total': {'total': '      0.00', 'count': 67}, 
     'tc': {'total': ' 15,105.39', 'count': 18}, 
   'sfos': {'total': ' 16,469.54', 'count': 34}, 
   'aios': {'total': '  3,143.40', 'count': 11}}}, 

{'from_to': (None, 30), 'title': '0 - 30 days',
  'values': {'total': {'total': '0.00', 'count': 0}, 
      'tc': {'total': '0.00', 'count': 0}, 
    'sfos': {'total': '0.00', 'count': 0}, 
    'aios': {'total': '0.00', 'count': 0}}}, 

{'from_to': (31, 60), 'title': '31 - 60 days',
  'values': {'total': {'total': '0.00', 'count': 0}, 
      'tc': {'total': '0.00', 'count': 0}, 
    'sfos': {'total': '0.00', 'count': 0}, 
    'aios': {'total': '0.00', 'count': 0}}}, 

{'from_to': (61, 90), 'title': '61 - 90 days', 
  'values': {'total': {'total': '0.00', 'count': 3}, 
      'tc': {'total': '0.00', 'count': 1}, 
    'sfos': {'total': '336.00', 'count': 1},
    'aios': {'total': '    120.00', 'count': 1}}},

{'from_to': (91, None), 'title': 'Over 90 days', 
  'values': {'total': {'total': '0.00', 'count': 64}, 
      'tc': {'total': ' 15,105.39', 'count': 17}, 
    'sfos': {'total': ' 16,133.54', 'count': 33}, 
    'aios': {'total': '  3,023.40', 'count': 10}}}]
\$\endgroup\$
0

1 Answer 1

3
\$\begingroup\$

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_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

Of course, pass parameters for each %(ref_sfx)s reference as shown above. And if needed, consider building nested Python dictionaries from this tabular query output.

\$\endgroup\$
2
  • \$\begingroup\$ Hi Parfait, thanks a bunch for this, I'm currently trying to adjust to get the same results from the DB, couple of questions: Why no END on the "CASE WHEN STATUS = "IN YARD.." lines, is that intentional? It was syntax erroring without. Also, does this solution account for the different date(arguments) being passed in in the original queries, if it does, which bit of logic handles that? Thanks \$\endgroup\$ Sep 7, 2016 at 13:34
  • \$\begingroup\$ Absolutely, right. END should be on close of all Case statements. I edited accordingly. As for different dates, do you mean date_range field (the calculated column for multiple of 30 days: 30 / 60 / 90 ...) included on all queries? The union query accounts for it in WHERE clause. Add that WHERE condition to first query and even pass as parameter like insurance_no. Or even pass start end dates: date BETWEEN ? AND ? \$\endgroup\$
    – Parfait
    Sep 7, 2016 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.