CREATE DEFINER=CURRENT_USER PROCEDURE `pharmacy_sales_rpt`(IN `start_date` DATE, IN `end_date` DATE, IN `user_filter` INT)
BEGIN
	
SELECT 
    Date,`Pos number`,Product,Quantity,`Unit price`,Total,Paid,Discount
FROM
    (SELECT 
       DATE_FORMAT(pos_date, '%d %b, %Y') as Date,CONCAT('POS_', phar_pos.pos_id) AS `Pos number`,
            scm_products.name AS Product,
            Quantity,
            CONCAT('$ ', format(price,2)) AS `Unit price`,
            CONCAT('$ ', format(price * quantity,2)) AS Total,
            0 Paid,
            0 Discount,
             0 AS isTotal
    FROM
        phar_pos
    JOIN phar_pos_products ON phar_pos_products.pos_id = phar_pos.id
    JOIN scm_products ON scm_products.id = phar_pos_products.product_id
    JOIN phar_pos_payments ON phar_pos_payments.pos_id = phar_pos.id
    JOIN acc_currencies ON acc_currencies.id = phar_pos_payments.currency 
    where phar_pos.pos_date between start_date and end_date and IFNULL(phar_pos.is_deleted, 0) = 0 and 
    (user_filter = 0 or phar_pos.created_by = user_filter)
    UNION SELECT 
       '' as Date,CONCAT('POS_', phar_pos.pos_id) AS `Pos number`,
            '<span style="font-weight:bold">Total</span>' AS Product,
            concat('<span style="font-weight:bold">',SUM(quantity),'</span>') as Quantity,
             concat('<span style="font-weight:bold">',format(SUM(CASE
                WHEN phar_pos_payments.currency = 2 THEN price
                ELSE price
            END),2),'</span>') `Unit price`,
             concat('<span style="font-weight:bold">',format(SUM(price * quantity),2),'</span>') Total,
            CONCAT("<span style='font-weight:bold'>", '', format(CASE
                WHEN phar_pos_payments.currency = 2 THEN phar_pos_payments.amount / phar_pos_payments.exchange_rate
                ELSE phar_pos_payments.amount
            END,2),"</span>") AS Paid,
            CONCAT("<span style='font-weight:bold'>", '', format(CASE
                WHEN phar_pos_payments.currency = 2 THEN phar_pos_payments.discount / phar_pos_payments.exchange_rate
                ELSE phar_pos_payments.discount
            END,2),"</span>") AS Discount,
             1 AS isTotal
    FROM
        phar_pos
    JOIN phar_pos_payments ON phar_pos_payments.pos_id = phar_pos.id
    JOIN phar_pos_products ON phar_pos_products.pos_id = phar_pos.id
    JOIN acc_currencies ON acc_currencies.id = phar_pos_payments.currency
    where phar_pos.pos_date between start_date and end_date and IFNULL(phar_pos.is_deleted, 0) = 0
    and (user_filter = 0 or phar_pos.created_by = user_filter)
    GROUP BY pos_date,`Pos number`, Product , Paid , Discount
    
    union 
    SELECT 
       '' as Date, "<span style='color:black; font-weight:bold; font-size:18px;'>Total</span>" AS `Pos number`,
            '' AS Product,
            '' as Quantity,
            '' AS `Unit price`,
            '' AS Total,
            concat("<span style='color:black; font-weight:bold; font-size:18px;'>$ ",format(sum(CASE
                WHEN phar_pos_payments.currency = 2 THEN phar_pos_payments.amount / phar_pos_payments.exchange_rate
                ELSE phar_pos_payments.amount
            END),2),"</span>") Paid,
		    concat("<span style='color:black; font-weight:bold; font-size:18px;'>$ ", format(sum(CASE
                WHEN phar_pos_payments.currency = 2 THEN phar_pos_payments.discount / phar_pos_payments.exchange_rate
                ELSE phar_pos_payments.discount
            END),2),"</span>") Discount,
             5 AS isTotal
    FROM
        phar_pos
    JOIN phar_pos_payments ON phar_pos_payments.pos_id = phar_pos.id
    where phar_pos.pos_date between start_date and end_date and IFNULL(phar_pos.is_deleted, 0) = 0 and 
    (user_filter = 0 or phar_pos.created_by = user_filter)
    ) z
ORDER BY `Pos number` desc, isTotal;
END
