How to calculate statistical data

[Vincenzo (Vincenzo Ferraro)]

hello everyone,
how do calculate the total (of a year) of the quantities of a product sent to a location (> 10000 moves)? Is there a specific method to have this type of data (statistics)?
Other question: calculate the quantities of product sent to a location divided by month and by type of document (customer shipment, internal shipment, customer return shipment)
Thanks

[ced (Cédric Krier)]

how do calculate the total (of a year) of the quantities of a product sent to a location (> 10000 moves)?

You must write a SQL query like:

SELECT SUM(internal_quantity) FROM stock_move WHERE to_location = ? AND product = ? AND state = 'done';

Other question: calculate the quantities of product sent to a location divided by month and by type of document (customer shipment, internal shipment, customer return shipment)

You can add a GROUP BY clause.

[ced (Cédric Krier)]

More over, you can use this query in ModelSQL.table_query to display the result in the client.

[Vincenzo (Vincenzo Ferraro)]

Can it be used via RPC?
If YES, with which model?

[ced (Cédric Krier)]

A ModelSQL with a table_query behaves exactly like any other ModelSQL (except for create, write and delete which are forbidden).

[Vincenzo (Vincenzo Ferraro)]

I never used “ModelSQL”. I’ve always used “ModelStorage”.

Usually through RPC, with “ModelStorage”, I use the syntax:

model.stock.move.search ([('product', '=', 8), ('to_location', '=', 259), ('state', '=', 'done')], 0, 1000, None, <CONTEXT>)

If I wanted to use “ModelSql” what is the syntax to use?

I have tried with:

model.table_query (['SELECT SUM (internal_quantity) FROM stock_move WHERE to_location = 259 AND product = 8 AND state = "done"'], <CONTEXT>)

But I get nothing …

[ced (Cédric Krier)]

ModelSQL inherits from ModelStorage. Indeed it is an implementation of the ModelStorage for the SQL backend. So you just need to use the same API.

By the way, stock.move is a ModelSQL subclass.

[Vincenzo (Vincenzo Ferraro)]

I’m sorry, but I can’t get results …
I can’t find examples of using table_query.

I tried to use:

model.stock.move.table_query (['SELECT SUM (internal_quantity) FROM stock_move WHERE to_location = 259 AND product = 8 AND state = "done"'], <CONTEXT>)

but I don’t get results.

What is the correct command syntax?
Could you give me an example that I can test?

Thanks

[ced (Cédric Krier)]

You must define a new ModelSQL that you will query.
Here is an example: https://hg.tryton.org/modules/timesheet/file/ee4b51fe0754/line.py#l230