CODEWITHSUNDEEP
djangodjango-models
erikvm
erikvm

Reputation: 928

Calculate total value from two tables in Django for an invoice application

I'm trying to create an invoice that will generate data from related tables, but I'm not sure how to proceed. In essence, an Invoice instance should capture logged hours found in a Timesheet model and be multiplied by a rate found in another table (ProjectUser) I have attached what the database schema looks like: https://dbdiagram.io/d/5ef0e2bc9ea313663b3ae6e4

How can I populate the amount field on my Invoice ModelForm with data from ProjectUser (user_hour_cost) and Timesheet (day_1 through day_7).

Say user_hour_cost is 500, and the sum of the values for day_1 through day_7 is 50. How can I display on the Invoice form the amount value 25000? (500 * 50).

Upvotes: 1

Views: 417

Answers (2)

Michael Hawkins
Michael Hawkins

Reputation: 2873

If I understand what you are trying to do, would something like this work?:

cost_data = []
grand_total = 0.00
for user_timesheet in Timesheet.objects.filter(week=1).all(): # or whatever date range you want, etc.

    # you can optimize this by joining the Timesheet and ProjectUser queries as an alternative
    project_user = ProjectUser.objects.get(user=user_timesheet.user)
    rate = project_user.user_hour_cost
    user_total = 0.00
    user_data = { 'user': project_user.user }

    # loop over the 7-day range
    for i in range(1,7):
       day_key = f"day_{str(i)}"
       user_data[day_key] = decimal(user_timesheet.get_day_hours(i) * rate)
       user_total += decimal(user_timesheet.get_day_hours(i) * rate)
    user_data['total'] = user_total
    cost_data.append(user_data)
    grand_total += user_data['total']
    user_data = {} # clear it just to be safe

context = { 'cost_data': cost_data, 'grand_total': grand_total }
return render(request, 'template.html', context)

This will put everything into an array of dicts that you can pass to the template and iterate over to display either the 'total' element or daily totals as needed like:

  {% for user_data in cost_data %}
        Username: {{ user_data.user.username }} //accessing the User object
        Person Total: {{ user_data.total }} //the total amount for this person
        Day 1: {{ user_data.day_1 }} //day 1 total
        Day 2: {{ user_data.day_2 }} //day 2 total
        ...
        Day 7: {{ user_data.day_7 }} //day 7 total
  {% endfor %}

  Invoice Total: {{ grand_total }} //total for the whole invoice

Lastly, you will need a method in your Timesheet model for this particular code to work like so:

class Timesheet(models.Model):
...    
...
     def get_day_hours(self, day):
         # there are other ways to do this as well, this was just the easiest for right now
         if day == 1:
             return self.day_1
         elif day == 2:
             return self.day_2
         ...
         elif day == 7:
             return self.day_7

This is essentially pseudocode not knowing the actual code you have, and it is a little rough (there are likely ways to perform this all in the database with less python code). There may be other more efficient ways to accomplish this, but this is what I came up with off the top of my head.

Upvotes: 1

iamkhush
iamkhush

Reputation: 2592

A simple, and easy to read answer would be to make multiple database calls (equal to number of users in a project + 1) to get the overall result.

For this, you can do the following

  • Make a DB Join of Project and ProjectUser table over project_id, so you will get all users in project, all their per_hour cost.
  • Iterate on each user, fetch timesheets from DB and so you can calculate total cost.

You can optimize the second step by doing a group by of user_id having project_id = and doing aggregate of all days.

Upvotes: 0

Related Questions