A problem with recalling from 2 table, by id, how much customer deposited, in table-CodePudding

I am writing a crm, for my own learning.

And I have a problem because I have 1 table "client" and in the other table I store the deposit of this client, I need on the page in the table to show the clients deposit, with the filter "Status=4", I tried to do it as annoate but then I can not bring up the corresponding id.

Models.py

class Client(models.Model):
    def __str__(self):
        return self.name   " "   self.lastname

    class Meta:
        verbose_name = 'Client'
        verbose_name_plural = 'Client'

    name = models.CharField(max_length=64)
    lastname = models.CharField(max_length=64)
    phone = models.DecimalField(max_digits=20, decimal_places=0)
    email = models.EmailField(max_length=64, null=True)
    salesman = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='salesman')
    retention = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='retention')
    reg_time = models.DateField(blank=True, null=True)

    class Operation(models.Model):
    def __str__(self):
        return self.client.name   ' '   self.client.lastname   '  Cash: '   str(self.cash)

    client = models.ForeignKey(Client, on_delete=models.CASCADE, related_name='client')
    cash = models.DecimalField(max_digits=12, decimal_places=2)
    date = models.DateField(blank=True, null=True)
    bank = models.ForeignKey(Bank, on_delete=models.CASCADE, related_name='bank')
    status = models.ForeignKey(Status, on_delete=models.CASCADE, related_name='stat')
    who = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='ftd_employees')

    class Meta:
        verbose_name = 'Operations'
        verbose_name_plural = 'Operations'

client_list.html

<tbody>
                            {%  for c in clients_list %}
                            <tr>
                                <td>{{ c.id }}</td>
                                <td>{{ c.name }} {{ c.lastname }}</td>
                                <td>{{ c.email }}</td>
                                <td>{{ c.phone }}</td>
                                <td>{{ c.salesman.name }} {{ c.salesman.lastname }}</td>
                                <td>{{ c.retention.name }} {{ c.retention.lastname }}</td>
                                <td>{{ c.phone }}</td>
                                <td>{{ c.reg_time }}</td>
                                <td>{{ c.depo }}</td>
                                {% endfor %}
                            </tr>

                            </tbody>

views.py

@property
def client_ftd(self):
    depo = Client.objects.annotate(Operation=Count("kto__id")).annotate(totals=Sum("kto__cash")).filter(kto__status=4)

    return depo


def client_list(request):
    employee = Employee.objects.all()
    clients_list = Client.objects.all()

    return render(request, 'client_list.html', {'clients_list': clients_list,
                                                'employee': employee, 'client_ftd': client_ftd})

CodePudding user response：

I assume you're looking for this simple query:

clients_list = Client.objects.annotate(depo=Sum('operation__cash', filter=Q(operation__status=4)))

Looking at your code I think you really have to learn some more about:

properties (@property decorator is intended to be used inside a class on a method; why did you put it in the views ??),
aggregate queries (what's "kto", what's the point of annotate(Operation=Count("kto__id")) ??) ??

CodePudding user response：

The problem is your clients_list = Client.objects.all().

For the solution you need to replace .all with .annotate. Subsequently you have to open a parenthesis with your depo, so (depo=Sum('operation__cash', filter=Q(operation__status=4))). As you can see, the "Status = 4" filter is used.

This way you will call back from 2 table, by id, how much customer deposited, in table :)

@property
def client_ftd(self):
    depo = Client.objects.annotate(Operation=Count("kto__id")).annotate(totals=Sum("kto__cash")).filter(kto__status=4)
    return depo

def client_list(request):
    employee = Employee.objects.all()

    clients_list = Client.objects.annotate(depo=Sum('operation__cash', filter=Q(operation__status=4))) #update 

    return render(request, 'client_list.html', {'clients_list': clients_list,
                                                'employee': employee, 'client_ftd': client_ftd})