1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
.. _tutorial-module-table-query:
Define aggregated model
=======================
Aggregated data are useful to analyze business.
Tryton can provide such data using :class:`~trytond.model.ModelSQL` class which
are not based on an existing table in the database but using a SQL query.
This is done by defining a :meth:`~trytond.model.ModelSQL.table_query` method
which returns a SQL ``FromItem``.
Let's create a :class:`~trytond.model.ModelSQL` which aggregate the number of
opportunity converted or lost per month.
First we create a :class:`~trytond.model.ModelSQL` class which defines a
:meth:`~trytond.model.ModelSQL.table_query` in :file:`opportunity.py`:
.. code-block:: python
from sql import Literal
from sql.aggregate import Count, Min
from sql.functions import CurrentTimestamp, DateTrunc
...
class OpportunityMonthly(ModelSQL, ModelView):
"Opportunity Monthly"
__name__ = 'training.opportunity.monthly'
month = fields.Date("Month")
converted = fields.Integer("Converted")
lost = fields.Integer("Lost")
@classmethod
def table_query(cls):
pool = Pool()
Opportunity = pool.get('training.opportunity')
opportunity = Opportunity.__table__()
month = cls.month.sql_cast(
DateTrunc('month', opportunity.end_date))
query = opportunity.select(
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Min(opportunity.id).as_('id'),
month.as_('month'),
Count(
Literal('*'),
filter_=opportunity.state == 'converted').as_('converted'),
Count(
Literal('*'),
filter_=opportunity.state == 'lost').as_('lost'),
where=opportunity.state.in_(['converted', 'lost']),
group_by=[month])
return query
.. note::
The table query must return a value for all the fields of the model but also
a unique ``id`` and a value for the create and write fields.
.. note::
We get the SQL table from the :meth:`~trytond.model.ModelSQL.__table__`
method.
.. note::
We use :meth:`~trytond.model.fields.Field.sql_cast` to convert the timestamp
returned by ``date_trunc`` into a :py:class:`~datetime.date`.
Then as usual we register the :class:`~trytond.model.ModelSQL` class in the in
the :class:`~trytond.pool.Pool` as type ``model`` in :file:`__init__.py`:
.. code-block:: python
def register():
...
Pool.register(
...,
opportunity.OpportunityMonthly,
module='opportunity', type_='model')
And to display we create a list view and the menu entry in
:file:`opportunity.xml`:
.. code-block:: xml
<tryton>
<data>
...
<record model="ir.ui.view" id="opportunity_monthly_view_list">
<field name="model">training.opportunity.monthly</field>
<field name="type">tree</field>
<field name="name">opportunity_monthly_list</field>
</record>
<record model="ir.action.act_window" id="act_opportunity_monthly_form">
<field name="name">Monthly Opportunities</field>
<field name="res_model">training.opportunity.monthly</field>
</record>
<record model="ir.action.act_window.view" id="act_opportunity_monthly_form_view">
<field name="sequence" eval="10"/>
<field name="view" ref="opportunity_monthly_view_list"/>
<field name="act_window" ref="act_opportunity_monthly_form"/>
</record>
<menuitem
parent="menu_opportunity"
action="act_opportunity_monthly_form"
sequence="50"
id="menu_opportunity_monthly_form"/>
</data>
</tryton>
And now the view in :file:`view/opportunity_monthly_list.xml`:
.. code-block:: xml
<tree>
<field name="month"/>
<field name="converted"/>
<field name="lost"/>
</tree>
Update database
---------------
As we have registered new model and XML records, we need to update the database
with:
.. code-block:: console
$ trytond-admin -d test --all
And restart the server and reconnect with the client to test computing
aggregate:
.. code-block:: console
$ trytond
.. note::
As you can see the model behaves like the other models, except that you can
not create, delete nor write on them.
This is all for your first module.
If you want to learn more about Tryton, you can continue on :ref:`specific
topics <topics-index>`.
|
