return code pj

1 files changed, 20 insertions, 65 deletions

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):