Context: I have generated a customer consumption report in Odoo which obtains a table grouped by partner_id and invoice_date to get an average of amount_untaxed per client and other statistical data, and then grouped only by partner_id.
def init(self, cr):
tools.sql.drop_view_if_exists(cr, 'customer_consumption_report') rs = cr.execute(""" CREATE OR REPLACE VIEW customer_consumption_report AS ( SELECT l.id, l.display_name, l.email, l.phone, l.total_invoices, l.avg_amount, l.total_amount, l.date_first_invoice, l.date_last_invoice, l.days_between_purchases, l.range_of_days, (current_date - l.date_last_invoice) AS days_since_last_invoice, l.last_contact, (CASE WHEN (l.days_between_purchases + l.range_of_days - (current_date - l.date_last_invoice)) + 30 >= 0 THEN 'activo' ELSE 'inactivo' END) AS customer_status, (CASE WHEN l.sum_invoices_last_year >= 4 AND l.avg_amount_last_year > 200 THEN 'oro' WHEN l.sum_invoices_last_year >= 4 AND l.avg_amount_last_year <= 200 THEN 'plata' WHEN l.sum_invoices_last_year < 4 AND l.avg_amount_last_year > 200 THEN 'bronce' ELSE 'turista' END) AS customer_level FROM ( SELECT rp.id, rp.display_name, rp.phone, rp.email, SUM(i.invoices_in_date) AS total_invoices, round(AVG(i.total_untaxed_per_day),2) AS avg_amount, SUM(i.total_untaxed_per_day) AS total_amount, MIN(i.date_invoice) AS date_first_invoice, MAX(i.date_invoice) AS date_last_invoice, extract( day from date_trunc('day', ( CASE WHEN COUNT(*) <= 1 THEN 0 ELSE SUM(time_since_last_invoice)/(COUNT(*)-1) END ) * '1 day'::interval) ) AS days_between_purchases, extract( day from date_trunc('day', ( CASE WHEN COUNT(*) <= 2 THEN 0 ELSE STDDEV(time_since_last_invoice) END ) * '1 day'::interval) ) AS range_of_days, SUM(i.invoices_in_date) FILTER(WHERE i.date_invoice > NOW() - INTERVAL '1 year') AS sum_invoices_last_year, AVG(i.total_untaxed_per_day) FILTER(WHERE i.date_invoice > NOW() - INTERVAL '1 year') AS avg_amount_last_year, AVG(i.total_avg_amount_last_year), MAX(pc.date::DATE) AS last_contact FROM ( SELECT ai.partner_id, ai.date_invoice, SUM(ai.amount_untaxed) AS total_untaxed_per_day, COUNT(ai.partner_id) AS invoices_in_date, COALESCE( ai.date_invoice - lag(ai.date_invoice) OVER ( PARTITION BY ai.partner_id ORDER BY ai.partner_id, ai.date_invoice ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ), 0 ) AS time_since_last_invoice, AVG(ai.amount_untaxed) FILTER(WHERE ai.date_invoice > NOW() - INTERVAL '1 year') OVER () total_avg_amount_last_year FROM account_invoice AS ai WHERE type = 'out_invoice' AND state IN ('open','paid') AND amount_untaxed > 0 AND amount_total > 0 GROUP BY ai.partner_id, ai.date_invoice, ai.amount_untaxed ORDER BY ai.partner_id, ai.date_invoice ) AS i JOIN res_partner AS rp ON rp.id = i.partner_id LEFT JOIN crm_phonecall pc ON pc.partner_id = i.partner_id WHERE rp.customer = 't' GROUP BY rp.id ) AS l ); SELECT * FROM customer_consumption_report;
""")
Now, the users want to search in specific range of dates but, this is not possible working over results table because it has the following structure:
id | total_invoices | avg_amount | total_amount | date_first_invoice | date_last_invoice | days_between_purchases | range_of_days | days_since_last_invoice | customer_status | customer_level
------+----------------+------------+--------------+--------------------+-------------------+------------------------+---------------+-------------------------+-----------------+----------------
6 | 4 | 64.66 | 258.63 | 2016-03-01 | 2016-06-29 | 40 | 46 | 168 | inactivo | plata
8 | 3 | 128.74 | 386.23 | 2016-05-24 | 2016-12-09 | 99 | 114 | 5 | activo | turista
18 | 2 | 324.80 | 649.59 | 2016-07-07 | 2016-09-28 | 83 | 0 | 77 | activo | bronce
28 | 1 | 128.36 | 128.36 | 2016-01-22 | 2016-01-22 | 0 | 0 | 327 | inactivo | turista
44 | 7 | 191.28 | 1338.94 | 2015-10-21 | 2016-05-31 | 37 | 26 | 197 | inactivo | plata
48 | 22 | 174.51 | 3839.15 | 2015-09-09 | 2016-11-03 | 20 | 17 | 41 | activo | plata
51 | 164 | 235.45 | 38613.36 | 2015-07-21 | 2016-11-28 | 3 | 4 | 16 | activo | oro
Reason: They want to be able to evaluate customer consumption in a range of dates.
Question: How can I get an user parameter from search box and insert it in a SQL query?
Could you format the code of your init function so it is more readable?
Additionally, I want to clarify your question. Are you working with an Odoo report and you want to be able to filter by start/end dates? Or something else?
I tried twice but I have some issues with formatting. Anyway that is only the SQL query I run to get this table.
Now, I need to know how to get some values (i.e.: start_date and end_date) to insert those values in my query. My boss wants to specify from when to when process invoices data in the table. This is not possible directly on the table result because there are not all invoice_date because final data is grouped by client.