DELIMITER $$
CREATE PROCEDURE `get_dashboard`(IN `in_query_type` VARCHAR(50), IN `in_date_from` VARCHAR(50), IN `in_date_to` VARCHAR(50), IN `in_facilityid` VARCHAR(50), IN `in_range_type` VARCHAR(50), IN `in_unit` VARCHAR(50))
BEGIN
	IF in_query_type = 'revenue_trend' THEN
    	IF in_range_type = 'weekly' THEN
            	SELECT sum(amount) as amount,  
                	date_format(transaction_date, '%W') as data_key 
                FROM `pending_txn` 
            	WHERE facilityId=in_facilityid AND date(transaction_date) BETWEEN in_date_from AND in_date_to
 				group by date_format(transaction_date, '%W');
        	
        ELSEIF in_range_type = 'monthly' THEN
            	SELECT sum(amount) as amount, 
                	date_format(transaction_date, '%D') as data_key FROM `pending_txn` 
            	WHERE facilityId=in_facilityid AND date(transaction_date) BETWEEN in_date_from AND in_date_to
 				group by date_format(transaction_date, '%D');

        ELSEIF in_range_type = 'quarterly' THEN
                SELECT 
                    SUM(amount) AS amount, 
                    CASE 
                        WHEN QUARTER(transaction_date) = 1 THEN 'First Quarter'
                        WHEN QUARTER(transaction_date) = 2 THEN 'Second Quarter'
                        WHEN QUARTER(transaction_date) = 3 THEN 'Third Quarter'
                        WHEN QUARTER(transaction_date) = 4 THEN 'Fourth Quarter'
                    END AS data_key
                FROM pending_txn 
                WHERE facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to
                GROUP BY QUARTER(transaction_date);
        	
        ELSEIF in_range_type = 'yearly' THEN
                SELECT 
                    SUM(amount) AS amount, 
                    DATE_FORMAT(transaction_date, '%M') AS data_key 
                FROM pending_txn 
                WHERE facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to 
                GROUP BY DATE_FORMAT(transaction_date, '%M');
        END IF;
    ELSEIF in_query_type = 'by_hospital' THEN
        	SELECT a.facilityId, sum(amount) as amount,  b.name as data_key,
            (SUM(amount) / (SELECT SUM(amount) FROM pending_txn a JOIN hospitals b on a.facilityId=b.id  WHERE a.facilityId=in_facilityid AND date(transaction_date) BETWEEN in_date_from AND in_date_to)) * 100 AS amount_perc
            FROM `pending_txn` a JOIN hospitals b
            on a.facilityId=b.id
            WHERE a.facilityId=in_facilityid AND date(transaction_date) BETWEEN in_date_from AND in_date_to
 			group by b.name, a.facilityId;
   ELSEIF in_query_type = 'by_unit' THEN
            SELECT 
            	 facilityId,
            service_type as name,
                SUM(amount) AS amount,
                service_type AS data_key,
                (SUM(amount) / (SELECT SUM(amount) FROM pending_txn WHERE a.facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to)) * 100 AS amount_perc
            FROM 
                pending_txn
            WHERE 
                facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to
            GROUP BY 
                service_type, facilityId;
ELSEIF in_query_type = 'by_service' THEN
        SELECT 
            SUM(amount) AS amount,
            b.description AS data_key,
           (SUM(amount) / (SELECT SUM(amount) FROM pending_txn a JOIN account b ON a.head = b.head WHERE a.facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to)) * 100 AS amount_perc
        FROM `pending_txn` a
        JOIN account b ON a.head = b.head
        WHERE a.facilityId=in_facilityid AND date(transaction_date) BETWEEN in_date_from AND in_date_to
        GROUP BY b.description  ORDER BY amount DESC LIMIT 7;

    ELSEIF in_query_type = 'hospitals' THEN
        	SELECT facilityId, b.name, SUM(amount) as total FROM `pending_txn` a JOIN hospitals b ON a.facilityId=b.id
            WHERE a.facilityId=in_facilityid AND DATE(transaction_date) BETWEEN in_date_from AND in_date_to
group by facilityId, b.name;
	ELSEIF in_query_type = 'units' THEN
        	SELECT distinct service_type, facilityId FROM pending_txn WHERE facilityId=in_facilityid;
	ELSEIF in_query_type = 'hospital_breakdown' THEN
    	SELECT created_at, description, amount FROM `pending_txn`
WHERE facilityId=in_facilityid;
	ELSEIF in_query_type ='department_breakdown' THEN
    	SELECT facilityId, service_type as unit_id, service_type as name, sum(amount) as amount FROM `pending_txn`
            WHERE facilityId=in_facilityid AND
            DATE(transaction_date) BETWEEN in_date_from AND in_date_to
            GROUP BY service_typee,facilityId;
	ELSEIF in_query_type = 'by_date' THEN
    	SELECT month(created_at) as report_month, year(created_at) as report_year, sum(amount) as amount FROM `pending_txn` GROUP BY month(created_at), year(created_at);
    
    ELSEIF in_query_type = 'service_by_unit' THEN
        SELECT 
            SUM(amount) AS amount,
            description
        FROM 
            pending_txn 
        WHERE 
            service_type = in_unit AND 
            facilityId = in_facilityid AND
            DATE(transaction_date) BETWEEN in_date_from AND in_date_to
        GROUP BY description;
	END IF;
END$$
DELIMITER ;