This question has been flagged
2 Replies
3659 Views
balanceaccountreportopenerp7move_lines
Avatar
Jose Vasquez

I'm working on OpenERP7 in a customized report of balance.

print "registers ", len(account_move_line_search)
            print "start ", time.strftime("%Y-%m-%d %H:%M:%S")
            account_account_search = account_account_pool.search(cr, uid, [('level','=',5)])
            for account_account in account_account_pool.browse(cr,uid,account_account_search):
                    account_move_line_search_inicial = account_move_line_pool.search(cr,uid,[('move_id.date','<=',self_browse.end_date),
                                                                                             ('account_id','=',account_account.id)])
                    sum_cuenta_debit = 0.00
                    sum_cuenta_credit = 0.00
                    for account_move_line in account_move_line_pool.browse(cr,uid,account_move_line_search_inicial):
                        sum_cuenta_debit += account_move_line.debit
                        sum_cuenta_credit += account_move_line.credit
                        total_asientos += 1
                    mis_cuentas.append([account_account.id,
                                        account_account.parent_id.id,
                                        account_account.parent_id.parent_id.id,
                                        account_account.parent_id.parent_id.parent_id.id,
                                        account_account.parent_id.parent_id.parent_id.parent_id.id,
                                        sum_cuenta_debit,
                                        sum_cuenta_credit])
            print "end ", time.strftime("%Y-%m-%d %H:%M:%S")

The report takes seats, in Date Range (do not take the date of account_move_line).

My work code it's to get the necessary values (debit,credit,account_id,account_id_level_4,account_id_level_3,account_id_level_2,account_id_level_1). 

With a sample of 14580 records of 10 days, movements of my jornuas, it takes 47 seconds (too in my opinion).

When I place my search more filters, takes longer.

Is there any way to simplify this time ?.

or, my work code its bad?, I should do?

It welcomes comments.

Avatar
Discard
Avatar
Ludo - 21South
Best Answer

Well, for each account in your for-loop, you will execute another for loop searching for move_lines, effectively searching the 14580 records aroudn 300 times ( based on the Dutch accounting schema). Since the data does not change in your for loop, perhaps you could move the search outside of your loop. That would surely already make a little difference.

Furthermore, maybe you can use the methods for gathering move lines that the existing reports use, as they tend to be quite fast? 

Avatar
Discard
Avatar
Jose Vasquez
Author Best Answer

Hi Ludo, and thanks for responding.

The date filter is for table "account_move" but the debit and credit are in "account_move_line", but I have to build it so:

http://bit.ly/1Bahtba

Given that the sum of level 5, adds to level 4, and level 4 adds to level 3, so on until the level 0 or level 1.

There is another way you suggest? or that is my fault in work code??

Thanks.

Avatar
Discard
Jose Vasquez
Author

I solved. It was more effective by a query to get the values. cr.execute(''' SELECT SUM (aml.debit) ,SUM (aml.credit) ,ac.id ,ac.code FROM account_move_line AS aml JOIN account_move AS am ON am.id = aml.move_id JOIN account_account AS ac ON ac.id = aml.account_id WHERE am.date BETWEEN %s AND %s AND (aml.debit > 0 OR aml.credit > 0) AND am.state IN ('posted') GROUP BY ac.id, ac.code ORDER BY code ASC ''',(self_browse.start_date,self_browse.end_date))