I'm recreating a precious stone inventory status report in Django which was originally built in Microsoft Access. The report is organized as follows:
- Deal ("ABC" - indicates house owned, consigned, partnership, etc.)
- Inventory Status (Inventory, Sold, Cost Only, Historical FYI, etc.)
- Detail lines (StoneID, Carats, Purchase Cost, etc.)
- Subtotals (Total Cost, Total Carats - being able to insert this in the correct place is where I'm stuck...)
- Detail lines (StoneID, Carats, Purchase Cost, etc.)
- Inventory Status (Inventory, Sold, Cost Only, Historical FYI, etc.)
Here are the relevant parts of the models:
class Deal(models.Model):
deal_name = models.TextField()
class Stone(models.Model):
stoneid = models.TextField(verbose_name='StoneID', unique=True)
dealid = models.ForeignKey(Deal, on_delete=models.PROTECT)
ct_in = models.DecimalField(verbose_name='Carats', max_digits=7, decimal_places=3)
cost_purchase = models.DecimalField(verbose_name='Purchase Cost', max_digits=14, decimal_places=2, null=True, blank=True)
I get the data via two queries - one for the detail lines and the other for the subtotals. Here are the queries:
def dump_stone(request):
query = Stone.objects.filter(Q(dealid_id__deal_name='ABC') | \
Q(dealid_id__deal_name='DEF') | \
Q(dealid_id__deal_name='GHI')).select_related().order_by('dealid_id__deal_name', 'inventory_status', 'stoneid')
totals = Stone.objects.values('dealid', 'inventory_status').annotate(sum_by_deal=Sum('cost_purchase'), sum_ct_in_by_deal=Sum('ct_in'))
The template to print out the inventory detail tables by status, by deal is:
{% block content %}
REPORT:
</br>
{% regroup context by dealid as deal_list %}
{% for dealid in deal_list %}
{{dealid.grouper}}
{% regroup dealid.list by inventory_status as stone_list%}
{% for inventory_status in stone_list %}
{{inventory_status.grouper}}
<table>
<thead>
<tr>
<th>StoneID</th>
<th>Ct</th>
<th>Cost</th>
</tr>
</thead>
<tbody>
{% for stone in inventory_status.list %}
<tr>
<td>{{ stone.stoneid }}</td>
<td>{{ stone.ct_in|floatformat:2 }}</td>
<td>{{ stone.cost_purchase|prepend_dollars }}</td>
</tr>
{% endfor %}
{% endfor %}
</tbody>
</table>
{% endfor %}
{% endblock content %}
The totals query produces the following output:
{'dealid': 1, 'inventory_status': 'HistoricFYI', 'sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000')}
{'dealid': 1, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('209138.7100000'), 'sum_ct_in_by_deal': Decimal('327.810000000000')}
{'dealid': 2, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000')}
{'dealid': 3, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('296754.5900000'), 'sum_ct_in_by_deal': Decimal('294.970000000000')}
{'dealid': 3, 'inventory_status': 'Memo In', 'sum_by_deal': Decimal('192948.340000000'), 'sum_ct_in_by_deal': Decimal('9.47000000000000')}
{'dealid': 3, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('154384.57000000'), 'sum_ct_in_by_deal': Decimal('88.1200000000000')}
{'dealid': 5, 'inventory_status': 'Inventory', 'sum_by_deal': Decimal('187000'), 'sum_ct_in_by_deal': Decimal('26.75')}
{'dealid': 5, 'inventory_status': 'Sold', 'sum_by_deal': Decimal('20000'), 'sum_ct_in_by_deal': Decimal('2')}
{'dealid': 5, 'inventory_status': 'Test', 'sum_by_deal': Decimal('13700'), 'sum_ct_in_by_deal': Decimal('19')}
What I'm trying to do is to convert the totals query into a dictionary of dictionaries so that I can access individual subtotals by deal, by status, and insert them into the correct place in the template with a tag (won't be hard-coded as shown, but I'll work on that next):
{{deal_dict.1.Sold.sum_by_deal}}
I'm trying to produce a dictionary that looks like this:
{ 1:
{
‘HistoricFYI’:{’sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000’)},
'Sold:{'sum_by_deal': Decimal('209138.7100000'), 'sum_ct_in_by_deal': Decimal('327.810000000000’)}
},
2:
{
’Sold’:{‘sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000’)},
},
3:
{
'Inventory’:{‘sum_by_deal': Decimal('296754.5900000'), 'sum_ct_in_by_deal': Decimal('294.970000000000’)},
'Memo In’:{‘sum_by_deal': Decimal('192948.340000000'), 'sum_ct_in_by_deal': Decimal('9.47000000000000’)},
'Sold’: {‘sum_by_deal': Decimal('154384.57000000'), 'sum_ct_in_by_deal': Decimal('88.1200000000000')}
},
5: {
'Inventory’:{‘sum_by_deal': Decimal('187000'), 'sum_ct_in_by_deal': Decimal('26.75’)},
'Sold’: {‘sum_by_deal': Decimal('20000'), 'sum_ct_in_by_deal': Decimal(‘2’)},
'Test’: {‘sum_by_deal': Decimal('13700'), 'sum_ct_in_by_deal': Decimal('19')}
}
}
I've tried a few things to take the totals queryset and make it into a nested dictionary:
deal_dict = {}
status_dict = {}
numbers_dict = {}
for things in totals:
print(things)
numbers_dict['sum_by_deal']=things['sum_by_deal']
numbers_dict['sum_ct_in_by_deal']=things['sum_ct_in_by_deal']
status_dict[things['inventory_status']]=dict(numbers_dict)
deal_dict[things['dealid']]=dict(status_dict)
The problem with the above code is that the nested dictionary for each deal includes the statuses from previous deals, unless the deal itself has its own data for that status that overwrites the previous data. In other words, for deal 2 for example I get
{ 2:
{
‘HistoricFYI’:{’sum_by_deal': Decimal('1287750'), 'sum_ct_in_by_deal': Decimal('15.1500000000000’)},
'Sold:{'sum_by_deal': Decimal('338726.99000000'), 'sum_ct_in_by_deal': Decimal('56.2000000000000’)}
},
even though it doesn't have any "HistoricFYI" data of its own, because the dictionary still includes the deal 1 data.
I also tried clearing the dictionary like so
status_dict.clear()
at the end of each loop, but I wound up with dictionary with only the last status in alphabetical order of each deal (Sale or Test).
I also tried
deal_dict = {}
for things in totals:
deal_dict.update({things['dealid']:{things['inventory_status']:{'sum_by_deal': things['sum_by_deal'], 'sum_ct_in_by_deal': things['sum_ct_in_by_deal']}}})
but that left just the last status for each deal in the dictionary like when I tried the clear() method.
I couldn't figure out how to adapt this --> Totals/Subtotals in Django template or this --> Django: how to process flat queryset to nested dictionary?
How can I produce this dictionary of dictionaries so that I can insert subtotals into the template, or somehow get the subtotals into the right place some other way? I'd greatly appreciate any help!
