From cc9c283c177b47567ef54284af0e18d02ca8b536 Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Mon, 1 Jul 2024 15:41:39 +0700 Subject: fix duplikat data purchasing job --- indoteknik_custom/models/purchasing_job.py | 107 ++++++++++++++++++++--------- 1 file changed, 76 insertions(+), 31 deletions(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index 373e469a..27bb1f4e 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -44,21 +44,21 @@ class PurchasingJob(models.Model): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" CREATE OR REPLACE VIEW %s AS ( - WITH latest_purchase_orders AS ( - SELECT - pol.product_id, - po.user_id, - ROW_NUMBER() OVER (PARTITION BY po.partner_id ORDER BY po.create_date DESC) AS order_rank - FROM purchase_order po - RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id - LEFT JOIN res_partner rp ON rp.id = po.partner_id - ), - random_user_ids AS ( - SELECT DISTINCT - CASE - WHEN vendor_id = 5571 THEN 27 - WHEN vendor_id = 9688 THEN 397 - WHEN vendor_id = 35475 THEN 397 + WITH latest_purchase_orders AS ( + SELECT + pol.product_id, + po.user_id, + ROW_NUMBER() OVER (PARTITION BY pol.product_id ORDER BY po.create_date DESC) AS order_rank + FROM purchase_order po + RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id + LEFT JOIN res_partner rp ON rp.id = po.partner_id + ), + random_user_ids AS ( + SELECT DISTINCT + CASE + WHEN vendor_id = 5571 THEN 27 + WHEN vendor_id = 9688 THEN 397 + WHEN vendor_id = 35475 THEN 397 WHEN vendor_id = 29712 THEN 397 ELSE (CASE WHEN random() < 0.5 THEN 397 ELSE 1036 END) END AS user_id, @@ -70,11 +70,26 @@ class PurchasingJob(models.Model): LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id ) AS sub WHERE sub.vendor_id IS NOT NULL + ), + unique_sub AS ( + SELECT DISTINCT + vso.product_id, + sol.vendor_id + FROM v_sales_outstanding vso + LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id + WHERE sol.vendor_id IS NOT NULL + ), + latest_po_filtered AS ( + SELECT + product_id, + user_id + FROM latest_purchase_orders + WHERE order_rank = 1 ) SELECT pmp.product_id AS id, pmp.product_id, - sub.vendor_id, + MAX(sub.vendor_id) AS vendor_id, pmp.brand, pmp.item_code, pmp.product, @@ -84,29 +99,59 @@ class PurchasingJob(models.Model): pmp.action, MAX(pjs.status_apo) AS status_apo, MAX(pjs.note) AS note, - ru.user_id AS purchase_representative_id - FROM v_procurement_monitoring_by_product pmp - LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id - LEFT JOIN ( + MAX(ru.user_id) AS purchase_representative_id + FROM ( SELECT - vso.product_id, - sol.vendor_id - FROM v_sales_outstanding vso - LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id - ) AS sub ON sub.product_id = pmp.product_id - LEFT JOIN latest_purchase_orders po ON po.product_id = pmp.product_id + a.brand, + a.item_code, + a.product, + a.onhand, + a.incoming, + a.outgoing, + CASE + WHEN (a.incoming + a.onhand) < a.outgoing THEN 'kurang' + ELSE 'cukup' + END AS action, + a.onhand_as, + a.product_id, + a.po_number, + a.so_number + FROM ( + SELECT + xm.x_name AS brand, + COALESCE(pp.default_code, pt.default_code) AS item_code, + pt.name AS product, + get_qty_onhand(pp.id::numeric) AS onhand, + get_qty_incoming(pp.id::numeric) AS incoming, + get_qty_outgoing(pp.id::numeric) AS outgoing, + get_qty_onhand(pp.id::numeric, 75::numeric) AS onhand_as, + pp.id AS product_id, + COALESCE(get_incoming_number(pp.id::numeric), 'kosong') AS po_number, + COALESCE(get_outgoing_number(pp.id::numeric), 'kosong') AS so_number + FROM stock_move sm + JOIN stock_picking sp ON sp.id = sm.picking_id + JOIN product_product pp ON pp.id = sm.product_id + JOIN product_template pt ON pt.id = pp.product_tmpl_id + LEFT JOIN x_manufactures xm ON xm.id = pt.x_manufacture + WHERE sp.state IN ('draft', 'waiting', 'confirmed', 'assigned') + AND sp.name LIKE '%OUT%' + AND sp.location_id IN (57, 83) + GROUP BY pp.id, xm.x_name, pp.default_code, pt.default_code, pt.name + ) a + ) pmp + LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id + LEFT JOIN unique_sub sub ON sub.product_id = pmp.product_id + LEFT JOIN latest_po_filtered po ON po.product_id = pmp.product_id LEFT JOIN random_user_ids ru ON ru.vendor_id = sub.vendor_id OR (ru.vendor_id IS NULL AND sub.vendor_id != 9688) WHERE pmp.action = 'kurang' - AND sub.vendor_id IS NOT NULL + AND sub.vendor_id IS NOT NULL GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, - pmp.action, - sub.vendor_id, - ru.user_id - ) + pmp.action + ) """ % self._table) -- cgit v1.2.3 From fb0b0dfc765cff3377705e1d4904d3a6aa042872 Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Mon, 1 Jul 2024 15:44:26 +0700 Subject: fix error purchasing job --- indoteknik_custom/models/purchasing_job.py | 34 ++++++++++++++---------------- 1 file changed, 16 insertions(+), 18 deletions(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index 27bb1f4e..230c171a 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -41,24 +41,23 @@ class PurchasingJob(models.Model): } def init(self): - tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" CREATE OR REPLACE VIEW %s AS ( - WITH latest_purchase_orders AS ( - SELECT - pol.product_id, - po.user_id, - ROW_NUMBER() OVER (PARTITION BY pol.product_id ORDER BY po.create_date DESC) AS order_rank - FROM purchase_order po - RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id - LEFT JOIN res_partner rp ON rp.id = po.partner_id - ), - random_user_ids AS ( - SELECT DISTINCT - CASE - WHEN vendor_id = 5571 THEN 27 - WHEN vendor_id = 9688 THEN 397 - WHEN vendor_id = 35475 THEN 397 + WITH latest_purchase_orders AS ( + SELECT + pol.product_id, + po.user_id, + ROW_NUMBER() OVER (PARTITION BY pol.product_id ORDER BY po.create_date DESC) AS order_rank + FROM purchase_order po + RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id + LEFT JOIN res_partner rp ON rp.id = po.partner_id + ), + random_user_ids AS ( + SELECT DISTINCT + CASE + WHEN vendor_id = 5571 THEN 27 + WHEN vendor_id = 9688 THEN 397 + WHEN vendor_id = 35475 THEN 397 WHEN vendor_id = 29712 THEN 397 ELSE (CASE WHEN random() < 0.5 THEN 397 ELSE 1036 END) END AS user_id, @@ -151,8 +150,7 @@ class PurchasingJob(models.Model): pmp.item_code, pmp.product, pmp.action - ) - """ % self._table) + )""" % self._table) def open_form_multi_generate_request_po(self): -- cgit v1.2.3 From 773648e3e7d6464a96ef6dac10405926be0d0ad7 Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Mon, 1 Jul 2024 15:49:10 +0700 Subject: fix bug pj --- indoteknik_custom/models/purchasing_job.py | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index 230c171a..4eaf700d 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -41,7 +41,7 @@ class PurchasingJob(models.Model): } def init(self): - self.env.cr.execute(""" + query = """ CREATE OR REPLACE VIEW %s AS ( WITH latest_purchase_orders AS ( SELECT @@ -150,7 +150,9 @@ class PurchasingJob(models.Model): pmp.item_code, pmp.product, pmp.action - )""" % self._table) + )""" % self._table + + self.env.cr.execute(query) def open_form_multi_generate_request_po(self): -- cgit v1.2.3 From e400125695357a2504041a678cccabb32c30f3af Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Mon, 1 Jul 2024 15:50:37 +0700 Subject: return code pj --- indoteknik_custom/models/purchasing_job.py | 85 +++++++----------------------- 1 file changed, 20 insertions(+), 65 deletions(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index 4eaf700d..fb8dec3d 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -41,13 +41,14 @@ class PurchasingJob(models.Model): } def init(self): - query = """ + tools.drop_view_if_exists(self.env.cr, self._table) + self.env.cr.execute(""" CREATE OR REPLACE VIEW %s AS ( WITH latest_purchase_orders AS ( SELECT pol.product_id, po.user_id, - ROW_NUMBER() OVER (PARTITION BY pol.product_id ORDER BY po.create_date DESC) AS order_rank + ROW_NUMBER() OVER (PARTITION BY po.partner_id ORDER BY po.create_date DESC) AS order_rank FROM purchase_order po RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id LEFT JOIN res_partner rp ON rp.id = po.partner_id @@ -69,26 +70,11 @@ class PurchasingJob(models.Model): LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id ) AS sub WHERE sub.vendor_id IS NOT NULL - ), - unique_sub AS ( - SELECT DISTINCT - vso.product_id, - sol.vendor_id - FROM v_sales_outstanding vso - LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id - WHERE sol.vendor_id IS NOT NULL - ), - latest_po_filtered AS ( - SELECT - product_id, - user_id - FROM latest_purchase_orders - WHERE order_rank = 1 ) SELECT pmp.product_id AS id, pmp.product_id, - MAX(sub.vendor_id) AS vendor_id, + sub.vendor_id, pmp.brand, pmp.item_code, pmp.product, @@ -98,61 +84,30 @@ class PurchasingJob(models.Model): pmp.action, MAX(pjs.status_apo) AS status_apo, MAX(pjs.note) AS note, - MAX(ru.user_id) AS purchase_representative_id - FROM ( - SELECT - a.brand, - a.item_code, - a.product, - a.onhand, - a.incoming, - a.outgoing, - CASE - WHEN (a.incoming + a.onhand) < a.outgoing THEN 'kurang' - ELSE 'cukup' - END AS action, - a.onhand_as, - a.product_id, - a.po_number, - a.so_number - FROM ( - SELECT - xm.x_name AS brand, - COALESCE(pp.default_code, pt.default_code) AS item_code, - pt.name AS product, - get_qty_onhand(pp.id::numeric) AS onhand, - get_qty_incoming(pp.id::numeric) AS incoming, - get_qty_outgoing(pp.id::numeric) AS outgoing, - get_qty_onhand(pp.id::numeric, 75::numeric) AS onhand_as, - pp.id AS product_id, - COALESCE(get_incoming_number(pp.id::numeric), 'kosong') AS po_number, - COALESCE(get_outgoing_number(pp.id::numeric), 'kosong') AS so_number - FROM stock_move sm - JOIN stock_picking sp ON sp.id = sm.picking_id - JOIN product_product pp ON pp.id = sm.product_id - JOIN product_template pt ON pt.id = pp.product_tmpl_id - LEFT JOIN x_manufactures xm ON xm.id = pt.x_manufacture - WHERE sp.state IN ('draft', 'waiting', 'confirmed', 'assigned') - AND sp.name LIKE '%OUT%' - AND sp.location_id IN (57, 83) - GROUP BY pp.id, xm.x_name, pp.default_code, pt.default_code, pt.name - ) a - ) pmp + ru.user_id AS purchase_representative_id + FROM v_procurement_monitoring_by_product pmp LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id - LEFT JOIN unique_sub sub ON sub.product_id = pmp.product_id - LEFT JOIN latest_po_filtered po ON po.product_id = pmp.product_id + LEFT JOIN ( + SELECT + vso.product_id, + sol.vendor_id + FROM v_sales_outstanding vso + LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id + ) AS sub ON sub.product_id = pmp.product_id + LEFT JOIN latest_purchase_orders po ON po.product_id = pmp.product_id LEFT JOIN random_user_ids ru ON ru.vendor_id = sub.vendor_id OR (ru.vendor_id IS NULL AND sub.vendor_id != 9688) WHERE pmp.action = 'kurang' - AND sub.vendor_id IS NOT NULL + AND sub.vendor_id IS NOT NULL GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, - pmp.action - )""" % self._table - - self.env.cr.execute(query) + pmp.action, + sub.vendor_id, + ru.user_id + ) + """ % self._table) def open_form_multi_generate_request_po(self): -- cgit v1.2.3 From a8f3194552f980ba6201ee834a0d5582cb584262 Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Mon, 1 Jul 2024 15:52:30 +0700 Subject: fix code --- indoteknik_custom/models/purchasing_job.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index fb8dec3d..373e469a 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -107,7 +107,7 @@ class PurchasingJob(models.Model): sub.vendor_id, ru.user_id ) - """ % self._table) + """ % self._table) def open_form_multi_generate_request_po(self): -- cgit v1.2.3 From f9c5b3dffcd71bfa9dea74c946d7b4277db66bd6 Mon Sep 17 00:00:00 2001 From: Azka Nathan Date: Tue, 2 Jul 2024 09:25:31 +0700 Subject: fix bug on multi create uang muka penjualan and fix bug duplikat data purchasing job --- indoteknik_custom/models/purchasing_job.py | 45 +++++++++++++++++------------- 1 file changed, 26 insertions(+), 19 deletions(-) (limited to 'indoteknik_custom/models/purchasing_job.py') diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py index 373e469a..40061d22 100644 --- a/indoteknik_custom/models/purchasing_job.py +++ b/indoteknik_custom/models/purchasing_job.py @@ -41,14 +41,13 @@ class PurchasingJob(models.Model): } def init(self): - tools.drop_view_if_exists(self.env.cr, self._table) - self.env.cr.execute(""" + query = """ CREATE OR REPLACE VIEW %s AS ( WITH latest_purchase_orders AS ( SELECT pol.product_id, po.user_id, - ROW_NUMBER() OVER (PARTITION BY po.partner_id ORDER BY po.create_date DESC) AS order_rank + ROW_NUMBER() OVER (PARTITION BY pol.product_id ORDER BY po.create_date DESC) AS order_rank FROM purchase_order po RIGHT JOIN purchase_order_line pol ON pol.order_id = po.id LEFT JOIN res_partner rp ON rp.id = po.partner_id @@ -70,11 +69,26 @@ class PurchasingJob(models.Model): LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id ) AS sub WHERE sub.vendor_id IS NOT NULL + ), + unique_sub AS ( + SELECT DISTINCT + vso.product_id, + sol.vendor_id + FROM v_sales_outstanding vso + LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id + WHERE sol.vendor_id IS NOT NULL + ), + latest_po_filtered AS ( + SELECT + product_id, + user_id + FROM latest_purchase_orders + WHERE order_rank = 1 ) SELECT pmp.product_id AS id, pmp.product_id, - sub.vendor_id, + MAX(sub.vendor_id) AS vendor_id, pmp.brand, pmp.item_code, pmp.product, @@ -84,30 +98,23 @@ class PurchasingJob(models.Model): pmp.action, MAX(pjs.status_apo) AS status_apo, MAX(pjs.note) AS note, - ru.user_id AS purchase_representative_id + MAX(ru.user_id) AS purchase_representative_id FROM v_procurement_monitoring_by_product pmp LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id - LEFT JOIN ( - SELECT - vso.product_id, - sol.vendor_id - FROM v_sales_outstanding vso - LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id - ) AS sub ON sub.product_id = pmp.product_id - LEFT JOIN latest_purchase_orders po ON po.product_id = pmp.product_id + LEFT JOIN unique_sub sub ON sub.product_id = pmp.product_id + LEFT JOIN latest_po_filtered po ON po.product_id = pmp.product_id LEFT JOIN random_user_ids ru ON ru.vendor_id = sub.vendor_id OR (ru.vendor_id IS NULL AND sub.vendor_id != 9688) WHERE pmp.action = 'kurang' - AND sub.vendor_id IS NOT NULL + AND sub.vendor_id IS NOT NULL GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, - pmp.action, - sub.vendor_id, - ru.user_id - ) - """ % self._table) + pmp.action + )""" % self._table + + self.env.cr.execute(query) def open_form_multi_generate_request_po(self): -- cgit v1.2.3