Add vendor id by sql in purchasing job view

2 files changed, 28 insertions, 25 deletions

diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py
index 1c74f0e9..ac9ea272 100644
--- a/indoteknik_custom/models/purchasing_job.py
+++ b/indoteknik_custom/models/purchasing_job.py
@@ -10,9 +10,8 @@ class PurchasingJob(models.Model):
     _auto = False
     _rec_name = 'product_id'
 
-    id = fields.Integer()
     product_id = fields.Many2one('product.product', string="Product")
-    vendor_id = fields.Many2one('res.partner', string="Vendor", compute='compute_vendor_id')
+    vendor_id = fields.Many2one('res.partner', string="Vendor")
     brand = fields.Char(string='Brand')
     item_code = fields.Char(string='Item Code')
     product = fields.Char(string='Product Name')
@@ -25,38 +24,41 @@ class PurchasingJob(models.Model):
         ('apo', 'APO')
     ], string='APO?')
 
-    def compute_vendor_id(self):
-        for sale in self:
-            domain = [
-                ('product_id', '=', sale.product_id.id)
-            ]
-            sales = self.env['v.sales.outstanding'].search(domain)
-
-            vendor_id = False
-            
-            if sales:
-                vendor_id = sales[0].sale_line_id.vendor_id.id
-
-            sale.vendor_id = vendor_id
-
     def init(self):
         tools.drop_view_if_exists(self.env.cr, self._table)
         self.env.cr.execute("""
             CREATE OR REPLACE VIEW %s AS (
                 SELECT
-                    pmp.product_id as id, 
-                    pmp.product_id, 
-                    pmp.brand, 
-                    pmp.item_code, 
-                    pmp.product, 
-                    pmp.onhand, 
-                    pmp.incoming, 
-                    pmp.outgoing, 
+                    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,
-                    pjs.status_apo AS status_apo
+                    MAX(pjs.status_apo) AS status_apo
                 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,
+                        rp.id AS vendor_id
+                    FROM v_sales_outstanding vso
+                    LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id
+                    LEFT JOIN res_partner rp ON rp.id = sol.vendor_id
+                ) AS sub ON sub.product_id = pmp.product_id
                 WHERE pmp.action = 'kurang'
+                AND sub.vendor_id <> 1
+                GROUP BY
+                    pmp.product_id,
+                    pmp.brand,
+                    pmp.item_code,
+                    pmp.product,
+                    pmp.action,
+                    sub.vendor_id
             )
         """ % self._table)
 
diff --git a/indoteknik_custom/views/purchasing_job.xml b/indoteknik_custom/views/purchasing_job.xml
index c439dc8f..c307ce11 100644
--- a/indoteknik_custom/views/purchasing_job.xml
+++ b/indoteknik_custom/views/purchasing_job.xml
@@ -53,6 +53,7 @@
                 <field name="product_id"/>
                 <field name="item_code"/>
                 <field name="brand"/>
+                <field name="vendor_id"/>
            </search>
         </field>
     </record>