fix duplikat data purchasing job

1 files changed, 76 insertions, 31 deletions

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)