Unanswered: [Newbie] Postgresql for Odoo/Python: Concatenate rows value until condition = true?
I do apologize in advance if this forum is not intended for newbie, but is just some days I'm starting to use postgreSQL and Python massively and I really need time to learn them well. Even if I have a background as maintainer in linux and SAP (ABAP) I'm facing various difficulties start learning again. After all I'm an over 40 so please don't blame me for that...
I'm creating a new view for a customized Point of Sale module in Odoo (formerly OpenERP) for my restaurant. It's a specific view for the kitchen, already filtered to show just kitchen's related items of a PoS order.
Now I would like to show a particular series of product with the name starting with "#" in a field populated by a function side by side with the "standard" products. In fact I'm speaking of a set of instructions coded as products to be available on PoS and to suit my needs I decided to create kitchen instructions as a product (service). In my PoS there are 3 categories that were created like products but are in fact instructions (ADD, SUBTRACT, ADDITIONAL NOTE).
Problem is Odoo uses massively XML, Python and Qweb other than be based on PostgreSQL. I worked from time to time with SQL and with reports engine (Crystal Reports and others) but I really need help to archive the result here because I'm editing the DB not just viewing it.
I'm not asking to write code from me, or at least is not the primary scope, but since I would like to understand what I'm doing some explained examples will be really appreciated.
A typical, filtered, order on DB will show as:
id (integer); order id (integer); name (char); x_notes(char)
1923;716;"# + Alface";""
1924;716;"# - Rucola";""
1925;716;"# ---- servir depois ----";""
1927;716;"# + Creme de ricota";""
Please note the field x_notes is empty. What I would like to archive is a view like:
Please, let me explain: I need to select just the strings starting with the "#" (my script-wake character) between two normal code and this is the hard thing to do. I don't need to delete the values I "move" because I need the stock view when I print the receipt o the thermal printers. I know this will let grow the db with redundant data but we are talking about a small db.
The flow I would like to see with the needed, real, fields should be more less the follow:
SELECT pos_order_line.id, pos_order_line.order_id, product_template.name, pos_order_line.x_notes
FROM public.pos_order_line, public.product_template
WHERE pos_order_line.product_id = product_template.id AND pos_order_line.order_id = 716 # This must be a variable generated from Odoo (Python)
ORDER BY pos_order_line.id DESC
WHILE pos_order_line.order_id IS THE SAME:
DO a LOOP:
IF product_template.name ILIKE '#%'
THEN ADD without deleting the VALUE of product_template.name TO product_template.name OF the previous row number (pos_order_line.id = pos_order_line.id - 1) and RESTART
ELSE (pos_order_line.id = pos_order_line.id - 1) and RESTART
I will call this function using python (and psycopg2 on postgresql 9.4) with on_change or extending the workflow when an order is marked as "invoiced".
I'm already familiar on how call a python procedure on odoo then this is not the scope of this thread.
That's complicated for a newbie, I know, but I'm ready to be criticized, insulted and so on... Have mercy if you can!