How to get the sum of spcific fields in odoo 12

Question

I am trying to get the sum of loan_amount fields for all the requests of the employee and then subtract it from the limitation, I get the sum and employee_id using this query:

self._cr.execute("SELECT sum(h.loan_amount) AS amount ,h.employee_id   From hr_loan h GROUP BY employee_id")

then store it in dictionary:

            for i, row in enumerate(self._cr.dictfetchall()):
            result = {}
            result['employee_id'] = row['employee_id']
            result['amount'] = row['amount']
            i + 1

When calling the function it return only the last element of the dictionary

here is the function:

@api.onchange('loan_amount', 'employee_id', 'limit')
def _check_contract_limitation(self):
    for rec in self:
        
        self._cr.execute("SELECT sum(h.loan_amount) AS amount ,h.employee_id   From hr_loan h GROUP BY employee_id")
    
        for i, row in enumerate(self._cr.dictfetchall()):
            result = {}
            result['employee_id'] = row['employee_id']
            result['amount'] = row['amount']
            if rec.employee_id:
                summation = result['amount']
                print(type(summation))

                i + 1
          
        hr_contract = self.env['hr.contract'].search([('employee_id.name', '=', self.employee_id.name)], limit=1)
        if not hr_contract:
            raise ValidationError("you dont have contract record.")
        elif hr_contract:
            rec.limit = hr_contract.wage
            if hr_contract.date_start:
                fmt = '%Y-%m-%d'
                d1 = hr_contract.date_start
                d2 = datetime.datetime.now().date()
                r = relativedelta.relativedelta(d2, d1)
                contract_time = r.years
                rec.a = contract_time
                if contract_time == None:
                    raise ValidationError("check contract duration.")
                else:
                    if contract_time >= 5 and datetime.datetime.now().date().year and rec.employee_id:
                        if rec.employee_id.loan_count == 0:
                            rec.limit = hr_contract.wage * 5
                        else:
                            rec.limit = hr_contract.wage * 5 - summation
                    elif contract_time >= 0 and contract_time < 5 and datetime.datetime.now().date().year and rec.employee_id:
                        if rec.employee_id.loan_count == 0:
                            rec.limit = hr_contract.wage * contract_time
                        else:
                            rec.limit = hr_contract.wage * contract_time - summation

The problem is that each time it calculates the result based on the last employee, how can I map the employee_id with his loan_amount

You reinitialize the `result` in each iteration, only the last element will be kept, you need to move the `result` variable declaration outside the for loop — Kenly, Oct 11 '22 at 11:27

score 0 · Answer 1 · answered Oct 06 '22 at 10:41

As Kenly said, you do re-initialize your result variable at each loop.

Plus, you do set the two same keys each time so you will anyway override those two keys with the last value of your iteration.

You may want something like one of these two possibilities:

1.

result = {}
for i, row in enumerate(self._cr.dictfetchall()):
    result[i] = {}
    result[i]['employee_id'] = row['employee_id']
    result[i]['amount'] = row['amount']
# Here you will end with a result like
#{
#   0: {
#      'employee_id': 4,
#      'amount': 123
#   },
#   1: {...}
#}

Else, go for a list of dicts (2.) :

result = []
for row in self._cr.dictfetchall():
    result.append({
        'employee_id': row['employee_id'],
        'amount': row['amount']
    })
# Here you will end with a result like
#[
#   {
#      'employee_id': 4,
#      'amount': 123
#   },
#   {...}
#]

How to get the sum of spcific fields in odoo 12

1 Answers1