Restructure sale.monitoring and sale.monitoring.detail & Create view

1 files changed, 24 insertions, 39 deletions

diff --git a/indoteknik_custom/models/sale_monitoring.py b/indoteknik_custom/models/sale_monitoring.py
index 6fabb2df..70c51132 100755
--- a/indoteknik_custom/models/sale_monitoring.py
+++ b/indoteknik_custom/models/sale_monitoring.py
@@ -8,52 +8,37 @@ class SaleMonitoring(models.Model):
 
     id = fields.Integer()
     sale_order_id = fields.Many2one("sale.order", string="Sale 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")
+    qty_so_delivered = fields.Integer(string="Qty SO Delivered")
+    qty_so_invoiced = fields.Integer(string="Qty SO Invoiced")
     date_order = fields.Datetime(string="Date Order")
+    status = fields.Char(string="Status")
 
     def init(self):
         tools.drop_view_if_exists(self.env.cr, self._table)
         self.env.cr.execute("""
-        CREATE or REPLACE VIEW %s as (
-            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','done') 
-                            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
+        CREATE OR REPLACE VIEW %s AS (
+            SELECT 
+                smd.sale_order_id AS id,
+                smd.date_order, 
+                smd.sale_order_id, 
+                SUM(smd.qty_so) AS qty_so, 
+                SUM(smd.qty_po) AS qty_po, 
+                SUM(smd.qty_po_received) AS qty_po_received, 
+                SUM(smd.qty_so_delivered) AS qty_so_delivered, 
+                SUM(smd.qty_so_invoiced) AS qty_so_invoiced,
+                CASE
+                    WHEN SUM(qty_po) <> SUM(qty_so) AND SUM(qty_po) <= 0 THEN 'Belum PO sama sekali'
+                    WHEN SUM(qty_po) <> SUM(qty_so) THEN 'Belum PO full'
+                    WHEN SUM(qty_po_received) <> SUM(qty_po) AND SUM(qty_po_received) <= 0 THEN 'Belum Received sama sekali'
+                    WHEN SUM(qty_po_received) <> SUM(qty_po) THEN 'Belum Received full'
+                    WHEN SUM(qty_so_delivered) <> SUM(qty_so) AND SUM(qty_so_delivered) <= 0 THEN 'SIAP KIRIM'
+                    WHEN SUM(qty_so_delivered) <> SUM(qty_so) THEN 'KIRIM SISANYA'
+                    ELSE 'Belum Invoiced' 
+                END AS status
+            FROM sale_monitoring_detail smd
+            GROUP BY smd.date_order, smd.sale_order_id
         )
         """ % self._table)