fix purchasing job

1 files changed, 25 insertions, 62 deletions

diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py
index 6e4f239d..0c34fc3c 100644
--- a/indoteknik_custom/models/purchasing_job.py
+++ b/indoteknik_custom/models/purchasing_job.py
@@ -43,73 +43,36 @@ 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 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
-                            WHEN vendor_id = 29712 THEN 397
-                            ELSE (CASE WHEN random() < 0.5 THEN 397 ELSE 1036 END)
-                        END AS user_id,
-                        vendor_id
-                    FROM (
-                        SELECT
-                            sol.vendor_id
-                        FROM v_sales_outstanding vso
-                        LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id
-                    ) AS sub
-                    WHERE sub.vendor_id IS NOT NULL
-                )
-                SELECT
-                    pmp.product_id AS id,
-                    pmp.product_id,
-                    sub.vendor_id,
-                    pmp.brand,
-                    pmp.item_code,
-                    pmp.product,
-                    MAX(pmp.onhand) AS onhand,
-                    MAX(pmp.incoming) AS incoming,
-                    MAX(pmp.outgoing) AS outgoing,
-                    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
+            CREATE OR REPLACE VIEW %s
+            AS SELECT pmp.product_id AS id,
+                pmp.product_id,
+                sub.vendor_id,
+                pmp.brand,
+                pmp.item_code,
+                pmp.product,
+                max(pmp.onhand) AS onhand,
+                max(pmp.incoming) AS incoming,
+                max(pmp.outgoing) AS outgoing,
+                pmp.action,
+                max(pjs.status_apo::text) AS status_apo,
+                max(pjs.note::text) AS note,
+                    CASE
+                        WHEN sub.vendor_id = 5571 THEN 27
+                        WHEN sub.vendor_id = 9688 THEN 397
+                        WHEN sub.vendor_id = 35475 THEN 397
+                        WHEN sub.vendor_id = 29712 THEN 397
+                        ELSE 1036
+                    END 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,
+                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
-                GROUP BY
-                    pmp.product_id,
-                    pmp.brand,
-                    pmp.item_code,
-                    pmp.product,
-                    pmp.action,
-                    sub.vendor_id,
-                    ru.user_id
-                )
+                        LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id) sub ON sub.product_id = pmp.product_id
+            WHERE pmp.action = 'kurang'::text AND sub.vendor_id IS NOT NULL
+            GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, pmp.action, sub.vendor_id;
         """ % self._table)
 
-
     def open_form_multi_generate_request_po(self):
         action = self.env['ir.actions.act_window']._for_xml_id('indoteknik_custom.action_purchasing_job_multi_update')
         action['context'] = {