Sale monitoring

2 files changed, 51 insertions, 0 deletions

diff --git a/indoteknik_custom/models/__init__.py b/indoteknik_custom/models/__init__.py
index 30914b52..03b01fc4 100644
--- a/indoteknik_custom/models/__init__.py
+++ b/indoteknik_custom/models/__init__.py
@@ -14,3 +14,4 @@ from . import user_activity_log
 from . import purchase_order
 from . import purchase_pricelist
 from . import purchase_order_line
+from . import sale_monitoring
diff --git a/indoteknik_custom/models/sale_monitoring.py b/indoteknik_custom/models/sale_monitoring.py
new file mode 100644
index 00000000..6a558669
--- /dev/null
+++ b/indoteknik_custom/models/sale_monitoring.py
@@ -0,0 +1,50 @@
+from odoo import fields, models, api, tools
+
+
+class SaleMonitoring(models.Model):
+    _name = 'sale.monitoring'
+    _auto = False
+
+    id = fields.Integer()
+    name = fields.Char(string="Name")
+    product_name = fields.Char(string="Product Name")
+    default_code = fields.Char(string="Default Code")
+    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")
+
+    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.name AS name, 
+                p.default_code AS default_code,
+                pt.name as product_name,
+                sol.product_uom_qty AS qty_so, 
+                sol.qty_delivered AS qty_so_delivered, 
+                sol.qty_invoiced AS qty_so_invoiced,
+                (
+                    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'
+        )
+        """ % self._table)