How can I get Total Deposit of Customers-CodePudding

I am working on a Django project with 2 Models; Customer and Deposit and I want to display a list of Customers with their names, deposited dated, account number, and Total Deposited within the year so how do I do it the right way. See what I have tried but Couldn't get the Customers' name in my Django Templates.

Models:

class Customer(models.Model):
    surname = models.CharField(max_length=10, null=True)
    othernames = models.CharField(max_length=20, null=True)
    account_number = models.CharField(max_length=10, null=True)
    address = models.CharField(max_length=50, null=True)
    phone = models.CharField(max_length=11, null=True)
    date = models.DateTimeField(auto_now_add=True, null=True) 

    #Get the url path of the view
    def get_absolute_url(self):
        return reverse('customer_create', args=[self.id])

    #Making Sure Django Display the name of our Models as it is without Pluralizing
    class Meta:
        verbose_name_plural = 'Customer'

#
    def __str__(self):
        return f'{self.surname} {self.othernames} - {self.account_number}' 

class Deposit(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, null=True)
    acct = models.CharField(max_length=6, null=True)
    staff = models.ForeignKey(User, on_delete=models.CASCADE, null=True)
    deposit_amount = models.PositiveIntegerField(null=True) 
    date = models.DateTimeField(auto_now_add=True)

    def get_absolute_url(self):
        return reverse('create_account', args=[self.id])

    def __str__(self):
        return f'{self.customer} Deposited {self.deposit_amount} by {self.staff.username}'

here is my view code:

def customer_list(request):
    #Get Current Date
    current_date = datetime.now().date()
    #Get Current Month Name from Calendar
    current_month_name = calendar.month_name[date.today().month]
    group_deposits = Deposit.objects.filter(date__year=current_date.year).order_by('acct')
    grouped_customer_deposit = group_deposits.values('acct').annotate(total `=Sum('deposit_amount')).order_by()`
context = { 'customers':grouped_customer_deposit,}

Here is how I tried to display the result in Django Template:

{% for deposit in customers %}
              <tr>
                <td>{{ forloop.counter }}</td> 
                <td>{{ deposit.acct }}</td>
                <td>{{ deposit.customer.surname }}</td>
                <td>{{ deposit.total }}</td>
                <td>{{ customer.deposit.date }}</td>
                
                <th scope="row"><a  href=" ">Deposit</a></th>
              </tr>
              {% endfor %}

Someone should graciously help with the most efficient way of getting the Total Deposit for each customer with their names, account number, and date deposited. Thank in anticipation for your kind answers.

CodePudding user response：

As you are using the values method:

grouped_customer_deposit = group_deposits.values('acct').annotate(total `=Sum('deposit_amount')).order_by()`

Django will return a queryset that returns dictionaries rather than model instances. See Django Queryset API reference for more details.

This means that you cannot reference your model relationships as you are trying to do here:

<td>{{ deposit.customer.surname }}</td>

as that information will not be in the dictionary passed to the template (you can do a print of grouped_customer_deposit in your view, or display it in your template -- {{ customers }} -- to see what I mean).

Ergo, if you remove your call to values() then your template will receive model instances which should solve your problem, and make your template work as-is.

grouped_customer_deposit = group_deposits.annotate(total `=Sum('deposit_amount')).order_by()`

(Although your syntax for your annotate is not correct, so I'm assuming that was a mistake when you pasted your code here.)