add date order and status in sales monitoring

2 files changed, 38 insertions, 36 deletions

diff --git a/indoteknik_custom/models/sale_monitoring.py b/indoteknik_custom/models/sale_monitoring.py
index 27c9007c..e82fe380 100644
--- a/indoteknik_custom/models/sale_monitoring.py
+++ b/indoteknik_custom/models/sale_monitoring.py
@@ -8,51 +8,52 @@ class SaleMonitoring(models.Model):
 
     id = fields.Integer()
     sale_order_id = fields.Many2one("sale.order", string="Sale Order")
-    purchase_order_id = fields.Many2one("purchase.order", string="Purchase Order")
+    status = fields.Char(string="Status")
     product_id = fields.Many2one("product.product", string="Product")
     qty_so = fields.Integer(string="Qty SO")
     qty_so_delivered = fields.Integer(string="Qty SO Delivered")
     qty_so_invoiced = fields.Integer(string="Qty SO Invoiced")
     qty_po = fields.Integer(string="Qty PO")
     qty_po_received = fields.Integer(string="Qty PO Received")
-    sale_create_date = fields.Datetime(string="Sale Create Date")
+    date_order = fields.Datetime(string="Date Order")
 
     def init(self):
         tools.drop_view_if_exists(self.env.cr, self._table)
         self.env.cr.execute("""
         CREATE or REPLACE VIEW %s as (
-            SELECT 
-                p.id AS id,
-                so.id AS sale_order_id, 
-                p.id as product_id,
-                sol.product_uom_qty AS qty_so, 
-                sol.qty_delivered AS qty_so_delivered, 
-                sol.qty_invoiced AS qty_so_invoiced,
-                so.create_date AS sale_create_date,
-                (
-                    SELECT poo.id 
-                    FROM purchase_order_line pol
-                    JOIN purchase_order poo ON poo.id = pol.order_id
-                    WHERE poo.sale_order_id = so.id AND pol.product_id = sol.product_id
-                ) AS purchase_order_id,
-                (
-                    SELECT SUM(product_uom_qty) 
-                    FROM purchase_order_line pol
-                    JOIN purchase_order poo ON poo.id = pol.order_id
-                    WHERE poo.sale_order_id = so.id AND pol.product_id = sol.product_id
-                ) AS qty_po,
-                (
-                    SELECT SUM(qty_received) 
-                    FROM purchase_order_line pol
-                    JOIN purchase_order poo ON poo.id = pol.order_id
-                    WHERE poo.sale_order_id = so.id AND pol.product_id = sol.product_id
-                ) AS qty_po_received
-            FROM sale_order so
-                JOIN sale_order_line sol ON sol.order_id = so.id
-                JOIN product_product p ON p.id = sol.product_id
-                JOIN product_template pt ON pt.id = p.product_tmpl_id
-            WHERE pt.type IN ('consu','product') 
-                AND so.state IN ('sale') 
-                AND so.create_date >= '2022-08-10'
+            select 
+              *, case
+              when qty_po <> qty_so and qty_po <= 0 then 'Belum PO sama sekali'
+              when qty_po <> qty_so then 'Belum PO full'
+              when qty_po_received <> qty_po and qty_po_received <= 0 then 'Belum Received sama sekali'
+              when qty_po_received <> qty_po then 'Belum Received full'
+              when qty_so_delivered <> qty_so and qty_so_delivered <= 0 then 'SIAP KIRIM'
+              when qty_so_delivered <> qty_so then 'KIRIM SISANYA'
+              else 'Belum Invoiced' end as status
+            from
+              (
+                  SELECT 
+                            p.id AS id,
+                            so.id AS sale_order_id, 
+                            p.id as product_id,
+                            sol.product_uom_qty AS qty_so, 
+                            sol.qty_delivered AS qty_so_delivered, 
+                            sol.qty_invoiced AS qty_so_invoiced,
+                            so.date_order AS date_order,
+                            get_qty_po(so.id, sol.product_id) as qty_po, 
+                            get_qty_received(so.id, sol.product_id) AS qty_po_received
+                        FROM sale_order so
+                            JOIN sale_order_line sol ON sol.order_id = so.id
+                            JOIN product_product p ON p.id = sol.product_id
+                            JOIN product_template pt ON pt.id = p.product_tmpl_id
+                        WHERE pt.type IN ('consu','product') 
+                            AND so.state IN ('sale') 
+                            AND so.create_date >= '2022-08-10'
+              ) a 
+            where 
+              a.qty_so <> a.qty_so_delivered 
+              or a.qty_so <> a.qty_so_invoiced 
+              or a.qty_so <> a.qty_po 
+              or a.qty_so <> a.qty_po_received
         )
         """ % self._table)
diff --git a/indoteknik_custom/views/sale_monitoring.xml b/indoteknik_custom/views/sale_monitoring.xml
index 1df23285..50a3aaa5 100644
--- a/indoteknik_custom/views/sale_monitoring.xml
+++ b/indoteknik_custom/views/sale_monitoring.xml
@@ -5,6 +5,7 @@
         <field name="model">sale.monitoring</field>
         <field name="arch" type="xml">
             <tree create="false">
+                <field name="date_order"/>
                 <field name="sale_order_id"/>
                 <field name="product_id"/>
                 <field name="qty_so"/>
@@ -12,6 +13,7 @@
                 <field name="qty_po_received"/>
                 <field name="qty_so_delivered"/>
                 <field name="qty_so_invoiced"/>
+                <field name="status"/>
             </tree>
         </field>
     </record>
@@ -25,7 +27,6 @@
                     <group>
                         <group>
                             <field name="sale_order_id"/>
-                            <field name="purchase_order_id"/>
                             <field name="product_id"/>
                         </group>
                         <group>