set_context to @table_query from Report

[Josias Pérez]

Hi,

I have a custom module with the follow definition:

DepartmentLine
Department (many2one to Deparment)
Product
Quantity

Department
Customer
Date
Lines (one2many to DepartmentLine)


I have a custom report that call info from a table_query

class DepartmentReport(Report):

'Report'
__name__ = 'disc.report'

@classmethod
def _get_records(cls, ids, model, data):
Report = Pool().get('disc.report.department.table')

clause = ''
clause = clause[:]
date = data['date']

department = data['department']

if date:
clause = [clause,
('date','>=',date)
]
if department:
clause = [clause,
('department','=',department)
]
query = Report.search(clause,
order=[('total', 'DESC')])
return query

class ReportDepartmentTable(ModelSQL, ModelView):
'Report Department'
__name__ = 'disc.report.department.table'

group = fields.Many2One('disc.group',
'Group')
department = fields.Many2One('disc.department',
'Department')
total = fields.Numeric('Total')
date = fields.Date('Date')

@staticmethod
def table_query():
pool = Pool()
Report = pool.get('disc.report')
report = Report.__table__()
ReportLine = pool.get('disc.report.line')
report_line = ReportLine.__table__()

query = (report
.join(report_line,
condition=report_line.report == report.id)
.select(
Max(report_line.id * 1000).as_('id'),
Max(report.create_uid).as_('create_uid'),
Max(report.create_date).as_('create_date'),
Max(report.write_uid).as_('write_uid'),
Max(report.write_date).as_('write_date'),
Max(report.department).as_('department'),
report.date
report_line.group,
Sum(report_line.quantity).as_('total'),
where=where,
group_by=
(report_line.group,
report.date,
)
)
)
return query

The report works fine, with some detail, in different dates, I receive the following data

Group 1 10 #10/01
Group 1 15 #15/01
Group 1 20 #20/01

So, I want to group these result to show only

Group 1 45 (which is the expected behavior), dates are not required in the report.

I was thinking in add the method set_context to the report and send it to the table_query. In a table_query, without report, using an aditional class
'deparment.context' it works fine and give the expected results.

Any help will be appreciated.

[Sergi Almacellas Abellana]

El 31/01/18 a les 13:04, Josias Pérez ha escrit:

> So, I want to group these result to show only
>
> Group 1 45 (which is the expected behavior), dates are not required in the report.
>
> I was thinking in add the method set_context to the report and send it to the table_query. In a table_query, without report, using an aditional class
> 'deparment.context' it works fine and give the expected results.
>
> Any help will be appreciated.

I think the better option is to create two diferents table_queries with
different group bys. And then on your report just use the one you want.

In order to simplify the code, you can use a Mixin as done in the
oportunity module:

http://hg.tryton.org/modules/sale_opportunity/file/e65684f7ecbd/opportunity.py#l520

Hope it helps.

--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Josias Pérez]

I was thinking in something like the code below:

In Report

with Transaction().set_context(_check_access=False):
date = data['date']
department = data['deparment']

In table_query
if Transaction().context.get('date'):
where &= report.date >= Transaction().context['date']
if Transaction().context.get('department'):
where &= report_line.department == Transaction().context['deparment']

But I dont know if it is possible or a good practice.

[Sergi Almacellas Abellana]

El 31/01/18 a les 15:33, Josias Pérez ha escrit:

> I was thinking in something like the code below:
>
> In Report
>
> with Transaction().set_context(_check_access=False):
> date = data['date']
> department = data['deparment']

Then you should have to surround the search with the set_context call:

with Transaction().set_context(
date=data['date'], department=data['department']):
records = Model.search(domain)

>
> In table_query
> if Transaction().context.get('date'):
> where &= report.date >= Transaction().context['date']
> if Transaction().context.get('department'):
> where &= report_line.department == Transaction().context['deparment']
>
> But I dont know if it is possible or a good practice.

Indeed the table query can be directly searched so you be able to
perform the following search on your get_records method:

records = Model.search([
('date', '>=', data['date']),
('department', '=', data['department']),
])

For me this is the cleaner solution.

[Josias Pérez]

This works well for me. Thanks!