Hi!
I need to add a custom search in v6.1 for a customer (though I believe the same thing will happen in later versions).
I am trying to add a 'groupby' filter by Country, State, Zip and City in Account > Account > Statistic Reports > Invoice analysis (that is, in the account/report/account_invoice_report.py and account/report/account_invoice_report_view.xml)
I add the buttons correctly in the view, but when I try to filter 'groupby' with them, two differnt errors raise.
- When trying to group_by country_id or state_id, I get:
2014-05-27 11:26:14,469 6141 ERROR ? openerp.osv.osv: Uncaught exception
Traceback (most recent call last):
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 129, in wrapper
return f(self, dbname, *args, **kwargs)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 195, in execute
res = self.execute_cr(cr, uid, obj, method, *args, **kw)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 183, in execute_cr
return getattr(object, method)(cr, uid, *args, **kw)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 2572, in read_group
data_ids = self.search(cr, uid, [('id', 'in', alldata.keys())], order=order, context=context)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 2251, in search
return self._search(cr, user, args, offset=offset, limit=limit, order=order, context=context, count=count)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 4594, in _search
order_by = self._generate_order_by(order, query)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 4551, in _generate_order_by
inner_clause = self._generate_m2o_order_by(order_field, query)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 4509, in _generate_m2o_order_by
m2o_order = dest_model._order
AttributeError: 'NoneType' object has no attribute '_order'
2014-05-27 11:26:14,469 6141 ERROR ? openerp.netsvc: 'NoneType' object has no attribute '_order'
- When trying to group by Zip or City, it raises:
Traceback (most recent call last):
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 129, in wrapper
return f(self, dbname, *args, **kwargs)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 195, in execute
res = self.execute_cr(cr, uid, obj, method, *args, **kw)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/osv.py", line 183, in execute_cr
return getattr(object, method)(cr, uid, *args, **kw)
File "/mnt/workZone/openerp/openerp_61_ocb_devel/ocb-server/openerp/osv/orm.py", line 2520, in read_group
assert groupby_def and groupby_def._classic_write, "Fields in 'groupby' must be regular database-persisted fields (no function or related fields), or function fields with store=True"
AssertionError: Fields in 'groupby' must be regular database-persisted fields (no function or related fields), or function fields with store=True
2014-05-27 11:26:07,990 6141 ERROR ? openerp.netsvc: Fields in 'groupby' must be regular database-persisted fields (no function or related fields), or function fields with store=True
So... no related fields? Must I add fields (country_id, state_id, zip, city) to invoice object in order to be able to group by them? I don't really like it because it menas duplicate data, create an onchange for the invoice_address and override the create function, if I do not miss anything.
I paste the current code below:
class account_invoice_report(orm.Model):
_inherit = "account.invoice.report"
_columns = {
'country_id': fields.many2one('res.partner.address.country_id', 'Country', readonly=True),
'state_id': fields.many2one('res.partner.address.state_id', 'State', readonly=True),
'zip': fields.many2one('res.partner.address.zip', 'Zip', readonly=True),
'city': fields.many2one('res.partner.address.city', 'City', readonly=True),
# 'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
# 'address_contact_id': fields.many2one('res.partner.address', 'Contact Address Name', readonly=True),
# 'address_invoice_id': fields.many2one('res.partner.address', 'Invoice Address Name', readonly=True),
# 'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
}
_order = 'date desc'
def init(self, cr):
super(account_invoice_report, self).init(cr)tools.drop_view_if_exists(cr, 'account_invoice_report')
cr.execute("""
create or replace view account_invoice_report as (
select min(ail.id) as id,
ai.date_invoice as date,
to_char(ai.date_invoice, 'YYYY') as year,
to_char(ai.date_invoice, 'MM') as month,
to_char(ai.date_invoice, 'YYYY-MM-DD') as day,
ail.product_id,
ai.partner_id as partner_id,
ai.payment_term as payment_term,
ai.period_id as period_id,
(case when u.uom_type not in ('reference') then
(select name from product_uom where uom_type='reference' and active and category_id=u.category_id LIMIT 1)
else
u.name
end) as uom_name,
ai.currency_id as currency_id,
ai.journal_id as journal_id,
ai.fiscal_position as fiscal_position,
ai.user_id as user_id,
ai.company_id as company_id,
count(ail.*) as nbr,
ai.type as type,
ai.state,
pt.categ_id,
ai.date_due as date_due,
ai.address_contact_id as address_contact_id,
ai.address_invoice_id as address_invoice_id,
(select country_id from res_partner_address where id=address_invoice_id) as country_id,
(select state_id from res_partner_address where id=address_invoice_id) as state_id,
(select zip from res_partner_address where id=address_invoice_id) as zip,
(select city from res_partner_address where id=address_invoice_id) as city,
ai.account_id as account_id,
ail.account_id as account_line_id,
ai.partner_bank_id as partner_bank_id,
sum(case when ai.type in ('out_refund','in_invoice') then
-ail.quantity / u.factor
else
ail.quantity / u.factor
end) as product_qty,sum(case when ai.type in ('out_refund','in_invoice') then
-ail.price_subtotal
else
ail.price_subtotal
end) / cr.rate as price_total,(case when ai.type in ('out_refund','in_invoice') then
sum(-ail.price_subtotal)
else
sum(ail.price_subtotal)
end) / (CASE WHEN sum(ail.quantity/u.factor) <> 0
THEN
(case when ai.type in ('out_refund','in_invoice')
then sum(-ail.quantity/u.factor)
else sum(ail.quantity/u.factor) end)
ELSE 1
END)
/ cr.rate as price_average,cr.rate as currency_rate,
sum((select extract(epoch from avg(date_trunc('day',aml.date_created)-date_trunc('day',ail.create_date)))/(24*60*60)::decimal(16,2)
from account_move_line as aml
WHERE ai.move_id=aml.move_id AND ail.product_id=aml.product_id AND ai.partner_id=aml.partner_id
)) as delay_to_pay,
(select extract(epoch from avg(date_trunc('day',ai.date_due)-date_trunc('day',ai.date_invoice)))/(24*60*60)::decimal(16,2)) as due_delay,
(case when ai.type in ('out_refund','in_invoice') then
-ai.residual
else
ai.residual
end)/ (CASE WHEN
(select count(l.id) from account_invoice_line as l
left join account_invoice as a ON (a.id=l.invoice_id)
where a.id=ai.id) <> 0
THEN
(select count(l.id) from account_invoice_line as l
left join account_invoice as a ON (a.id=l.invoice_id)
where a.id=ai.id)
ELSE 1
END) / cr.rate as residual
from account_invoice_line as ail
left join account_invoice as ai ON (ai.id=ail.invoice_id)
left join product_product pr on (pr.id=ail.product_id)
left join product_template pt on (pt.id=pr.product_tmpl_id)
left join product_uom u on (u.id=ail.uos_id),
res_currency_rate cr
where cr.id in (select id from res_currency_rate cr2 where (cr2.currency_id = ai.currency_id)
and ((ai.date_invoice is not null and cr.name <= ai.date_invoice) or (ai.date_invoice is null and cr.name <= NOW())) limit 1)
group by ail.product_id,
ai.date_invoice,
ai.id,
cr.rate,
to_char(ai.date_invoice, 'YYYY'),
to_char(ai.date_invoice, 'MM'),
to_char(ai.date_invoice, 'YYYY-MM-DD'),
ai.partner_id,
ai.payment_term,
ai.period_id,
u.name,
ai.currency_id,
ai.journal_id,
ai.fiscal_position,
ai.user_id,
ai.company_id,
ai.type,
ai.state,
pt.categ_id,
ai.date_due,
ai.address_contact_id,
ai.address_invoice_id,
country_id,
state_id,
zip,
city,
ai.account_id,
ail.account_id,
ai.partner_bank_id,
ai.residual,
ai.amount_total,
u.uom_type,
u.category_id
)
""")account_invoice_report()
