CREATE DEFINER=CURRENT_USER PROCEDURE `purchase_order_rpt`(IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `supplier_id_filter` INT, IN `product_id_filter` INT)
BEGIN
    SELECT  
        ROW_NUMBER() OVER (ORDER BY date) AS Sqn, 
        scm_purchase_orders.name AS Po_no,
        date,
        scm_suppliers.name AS Supplier, 
        scm_products.name AS Product,
        CONCAT(scm_purchase_order_details.quantity,' ',scm_product_measurements.name) AS Quantity,
        format(scm_purchase_order_details.unit_price,2) AS `Unit price`, 
        FORMAT(scm_purchase_order_details.quantity * scm_purchase_order_details.unit_price, 2) AS Total,
        scm_purchase_orders.status,
        scm_purchase_orders.payment_status AS `Payment status` 
    FROM scm_purchase_orders
    JOIN scm_suppliers ON scm_suppliers.id = scm_purchase_orders.supplier
    JOIN scm_purchase_order_details ON scm_purchase_order_details.po_id = scm_purchase_orders.id
    JOIN scm_products ON scm_products.id = scm_purchase_order_details.product
    JOIN scm_product_measurements ON scm_product_measurements.id = scm_purchase_order_details.uom
    WHERE date BETWEEN start_date_filter AND end_date_filter
        AND (supplier_id_filter = 0 OR scm_purchase_orders.supplier = supplier_id_filter)
        AND (product_id_filter = 0 OR scm_purchase_order_details.product = product_id_filter)
        
        union 
        
         SELECT  
        '' AS Sqn, 
        '' Po_no,
        ''date,
        '' Supplier, 
        '' Product,
        concat('<span style="font-weight:bold;">',format(sum(scm_purchase_order_details.quantity),2),'</span>') AS Quantity,
         concat('<span style="font-weight:bold;">',format(sum(scm_purchase_order_details.unit_price),2),'</span>') AS `Unit price`, 
         concat('<span style="font-weight:bold;">',FORMAT(sum(scm_purchase_order_details.quantity * scm_purchase_order_details.unit_price), 2),'</span>') AS Total,
        '' as status,
        '' as `Payment status` 
    FROM scm_purchase_orders
    JOIN scm_suppliers ON scm_suppliers.id = scm_purchase_orders.supplier
    JOIN scm_purchase_order_details ON scm_purchase_order_details.po_id = scm_purchase_orders.id
    JOIN scm_products ON scm_products.id = scm_purchase_order_details.product
    JOIN scm_product_measurements ON scm_product_measurements.id = scm_purchase_order_details.uom
    WHERE date BETWEEN start_date_filter AND end_date_filter
        AND (supplier_id_filter = 0 OR scm_purchase_orders.supplier = supplier_id_filter)
        AND (product_id_filter = 0 OR scm_purchase_order_details.product = product_id_filter);


   
END
