Get Tryton Stock from Database

[TPH]

Hello,

I just wrote this question in the German group, but I think here are much more people. Ill will keep both articles updated.

What is the best way to get product stock directly from database?

For a webshop / ebay sync I need the stock for every product of the warehouse. I have tried this with proteus, but this is not fast enough.

So I am searching for an way, to get the actually stock of product on the warehouse.

I think I have to use  "stock_location" and "stock_move". Maybe someone have already a snipped or can give me some hints.

Thank you very much.

[Raimon Esteve]

https://bitbucket.org/tryton/stock/src/c9a913139d8da1cac5835362b829d16c0d28602f/product.py?fileviewer=file-view-default#product.py-135

It's a method very hard and spend some time depends about
product/stock moves in your database.

Atention with the context because return some values or others.

Raimon

[Sergi Almacellas Abellana]

El 22/03/16 a les 13:46, Raimon Esteve ha escrit:

Or you can simply browse the list of products with the correct keys in
the context:

location_ids = (list of ids of the locations you want to take in account)
product_ids = (list of products id you want no know the stock)

with Transaction().set_context(locations=location_ids):
for product in Product.browse(product_ids):
print product.quantity, product.forecast_quantity

>
> Atention with the context because return some values or others.

This is explained on the docstring of compute_quantities [1]

And it affects both ways.

HIH

[1]
https://bitbucket.org/tryton/stock/src/c9a913139d8da1cac5835362b829d16c0d28602f/move.py?fileviewer=file-view-default#move.py-878
--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Cédric Krier]

On 2016-03-22 04:51, TPH wrote:
> For a webshop / ebay sync I need the stock for every product of the
> warehouse. I have tried this with proteus, but this is not fast enough.

proteus is not yet optimized for performance because it doesn't read by
bunch of records.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[TPH]

Thank you for response. I know function. I am searching for a solution to get the stock directly from the Database

Maybe this is an way:

Get all locations in you warehouse
Get a Product
Filter Stock Moves to get all Moves for this Product from or into that locations and warehouse

[TPH]

Hello

I am using Tryton 3.2 and work with proteus. I am not sure but I think there is no Transaction in this proteus version.
So I try to get the stock directly from the SQL Database.

[Sergi Almacellas Abellana]

El 22/03/16 a les 14:50, TPH ha escrit:

> Am Dienstag, 22. März 2016 14:15:17 UTC+1 schrieb Sergi Almacellas Abellana:
>
> El 22/03/16 a les 13:46, Raimon Esteve ha escrit:

> > 2016-03-22 12:51 GMT+01:00 TPH<truckpart...@gmx.de <javascript:>>:

> www.koolpi.com <http://www.koolpi.com>

> Twitter: @pokoli_srk
>
>
> Hello
>
> I am using Tryton 3.2 and work with proteus. I am not sure but I think
> there is no Transaction in this proteus version.
> So I try to get the stock directly from the SQL Database.

Sorry the proposed code has to be executed directly from the server
(which is directly conected to the database).

Although it's possible to build a raw sql query to get the stock, I will
recomend you to add a method on the server that computes the required
stock (with the code proposed earlier).

[TPH]

Because of this I try now to optimize my scripts, reading bigger date directly from database and use Proteus almost only for writing. I use the databases directly already for sale statistics.

[TPH]

This is now my first draft to get the stock directly from database, first only for an specific product. Optimization tips are welcome. I don't have sub-locations so I don't have to search for other locations then parent = warehouse.

product_id = 7
warehouse_id = 22
stock = 0

# get all locations with parent warehouse_id
cursor.execute("SELECT * FROM stock_location WHERE parent = '%s'", [warehouse_id])
records = cursor.fetchall()
location_ids = [r["id"] for r in records]

# get all moves from or to warehouse
cursor.execute("SELECT * FROM stock_move WHERE product = %s AND state != 'draft' AND (from_location = ANY(%s) OR to_location = ANY(%s))", (
            product_id,
            location_ids,
            location_ids
            ))
records = cursor.fetchall()

# calculate stock
for r in records:
    if r["to_location"] in location_ids:
        stock + r["internal_quantity"]
    if r["from_location"] in location_ids:
        stock - r["internal_quantity"]
print "stock:", stock


regards

[Cédric Krier]

On 2016-03-22 09:57, TPH wrote:
>
>
> Am Dienstag, 22. März 2016 13:05:04 UTC+1 schrieb TPH:
> >
> > Hello,
> >
> > I just wrote this question in the German group, but I think here are much
> > more people. Ill will keep both articles updated.
> >
> > What is the best way to get product stock directly from database?
> >
> > For a webshop / ebay sync I need the stock for every product of the
> > warehouse. I have tried this with proteus, but this is not fast enough.
> >
> > So I am searching for an way, to get the actually stock of product on the
> > warehouse.
> >
> > I think I have to use "stock_location" and "stock_move". Maybe someone
> > have already a snipped or can give me some hints.
> >
> > Thank you very much.
> >
> >
> This is now my first draft to get the stock directly from database, first
> only for an specific product. Optimization tips are welcome. I don't have
> sub-locations so I don't have to search for other locations then parent =
> warehouse.

I still don't understand why you don't use the quantity on Product.
The Tryton code to compute the quantity is quite complex because it has
a lot of improvements that you will probably miss by reimplementing it
like: using SQL SUM (right order), reuse stock period cache, take care
of assignation, take care of the effective date etc.

If proteus is not fast enough when computing product per product, you
can make yourself the RPC call for the list of products.

[TPH]

The reason is very simple. I never have used RPC and don't know how to start.

Do I have to install local client package e.g. with pip so I can make "import tryton.rpc as rpc"?
We do talk about JSON-RPC, doesn't we? Or XML-RPC?

May you have links?
I found only old ones:
https://code.google.com/archive/p/tryton/wikis/RemoteCalls.wiki#XML-RPC_in_Python

Here are also not many informations:
http://doc.tryton.org/3.2/trytond/doc/ref/rpc.html

I have interests in doing it a better way.

thanks and regards

[Cédric Krier]

On 2016-03-23 01:28, TPH wrote:
> The reason is very simple. I never have used RPC and don't know how to
> start.
>
> Do I have to install local client package e.g. with pip so I can make
> "import tryton.rpc as rpc"?
> We do talk about JSON-RPC, doesn't we? Or XML-RPC?
>
> May you have links?
> I found only old ones:
> https://code.google.com/archive/p/tryton/wikis/RemoteCalls.wiki#XML-RPC_in_Python
>
> Here are also not many informations:
> http://doc.tryton.org/3.2/trytond/doc/ref/rpc.html
>
> I have interests in doing it a better way.

Indeed when I said RPC call, I mean to skip the ActiveRecord pattern of
proteus and call the method directly from proteus like the method _read
does:

product_proxy = config.get_proxy('product.product')
product_proxy.read(ids, fields, context)

with proper ids, fields and context

PS: Please don't top-post on this mailing list, see
http://groups.tryton.org/netiquette

[TPH]

Thank you for the help. This is working fast enought !
Is there any disadvantage in this method?

locations = Location.find([('type', '=', 'storage'), ('parent.name', '=' ,'Main Storage Name')])
location_ids = [l.id for l in locations]
config._context['locations'] = location_ids

products = Product.find([('type', '=', 'goods')])
product_ids = [p.id for p in products]

product_proxy = config.get_proxy('product.product')

product_informations = product_proxy.read(product_ids, ["code", "quantity"], config.context)