from odoo import fields, models, api, tools


class SaleMonitoring(models.Model):
    _name = 'sale.monitoring'
    _auto = False
    _rec_name = 'sale_order_id'

    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")
    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 
              *, 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)