Hello,
I want to display for a product.template all the current product.pricelist.item with active product.pricelist.version and sorted by customer. Of course, If there is a product.pricelist.version that applies on the product.category of my product.template, I also want to display it. These informations can only be get using a complex SQL query with multiple joins.
But I want to display the result as a product.pricelist.item in a one2many field of the product.template. The problem is that I want to compute the result each time the product.template form is displayed , so I made a one2many computed field but I don't want to store records in database (it creates duplicates of existing information) at all. Is it possible to make kind of dummies one2Many records just for displaying purpose ?
My current code looks like this, bu of course that not good because it creates too much records in the database.
pricelist_items = fields.One2many('product.pricelist.item',compute="_compute_pricelist_items",
store=False
)
@api.one
def _compute_pricelist_items(self):
query = """SELECT distinct
pli.price_discount, pli.product_tmpl_id, pli.product_id, pli.base,
pli.price_version_id, pli.min_quantity, pli.categ_id, pa.id
FROM
product_pricelist_item as pli
INNER JOIN product_pricelist_version plv ON pli.price_version_id =
plv.id
INNER JOIN product_pricelist pl ON plv.pricelist_id = pl.id
INNER JOIN ir_property prop ON pl.id = cast(substr(prop.value_reference,
19, 20) as INTEGER)
AND prop.name = 'property_product_pricelist'
INNER JOIN res_partner pa ON cast(substr(prop.res_id, 13, 20) as
INTEGER) = pa.id
WHERE
pli.product_tmpl_id = %(product_template_id)s
UNION
SELECT distinct
pli.price_discount, pli.product_tmpl_id, pli.product_id, pli.base,
pli.price_version_id, pli.min_quantity, pli.categ_id, pa.id
FROM
product_template pt
INNER JOIN product_pricelist_item as pli ON pt.categ_id = pli.categ_id
INNER JOIN product_pricelist_version plv ON pli.price_version_id =
plv.id
INNER JOIN product_pricelist pl ON plv.pricelist_id = pl.id
INNER JOIN ir_property prop ON pl.id = cast(substr(prop.value_reference,
19, 20) as INTEGER)
AND prop.name = 'property_product_pricelist'
INNER JOIN res_partner pa ON cast(substr(prop.res_id, 13, 20) as
INTEGER) = pa.id
WHERE
pt.id = %(product_template_id)s """
#Query result index
PRICE_DISCOUNT = 0
PRODUCT_TMPL_ID = 1
PRODUCT_ID = 2
BASE = 3
PRICE_VERSION_ID = 4
MIN_QUANTITY = 5
CATEG_ID = 6
PARTNER_ID = 7
self._cr.execute(query, {'product_template_id' : self.id})for item in self._cr.fetchall():
pricelist_version = self.env['product.pricelist.version'].browse(item[PRICE_VERSION_ID])
if pricelist_version_date_check(pricelist_version) is True:
vals = {'product_tmpl_id': item[PRODUCT_TMPL_ID],
'product_id': item[PRODUCT_ID],
'categ_id': item[CATEG_ID],
'price_discount': item[PRICE_DISCOUNT],
'min_quantity': item[MIN_QUANTITY],
'base': item[BASE],
'customer': item[PARTNER_ID],
'price_version_id': item[PRICE_VERSION_ID]
}
pricelist_items += self.env['product.pricelist.item'].create(vals)
self.pricelist_items = pricelist_items
