queries

DELIMITER $$
CREATE PROCEDURE `sp_get_dashboard_sales_by_region`(
    IN duration VARCHAR(15),
    IN input_date DATE
)
BEGIN
    DECLARE current_month_start DATE;
    IF duration = 'current' THEN
        SET current_month_start = DATE_FORMAT(input_date, '%Y-%m-01');
        SELECT
            COALESCE(h.sale_area,'uncategorized') as area,
            COALESCE(sum(d.net_amount),0) as 'value'
        FROM
            sale_headers h
            join sale_details d on h.id = d.header_id
        WHERE
            h.sale_date between current_month_start and input_date
        GROUP BY
            h.sale_area;
     ELSE
        SELECT
            COALESCE(h.sale_area,'uncategorized') as area,
            COALESCE(sum(d.net_amount),0) as 'value'
        FROM
            sale_headers h
            join sale_details d on h.id = d.header_id
        WHERE
            h.sale_date <= input_date
        GROUP BY
            h.sale_area;
     END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `sp_get_dashboard_sales_by_product`(
    IN duration VARCHAR(15),
    IN input_date DATE
)
BEGIN
    DECLARE current_month_start DATE DEFAULT NULL;

    IF duration = 'current' THEN
        SET current_month_start = DATE_FORMAT(input_date, '%Y-%m-01');

        SELECT
            p.name AS product,
            COALESCE(SUM(s.qty * -1), 0) AS `value`
        FROM
            stock_movements s
            JOIN products p ON s.product_id = p.id
        WHERE
            s.transaction_date BETWEEN current_month_start AND input_date
            AND s.transaction_type IN ('sale', 'invoice')
        GROUP BY
            p.name;

    ELSE
        SELECT
            p.name AS product,
            COALESCE(SUM(s.qty * -1), 0) AS `value`
        FROM
            stock_movements s
            JOIN products p ON s.product_id = p.id
        WHERE
            s.transaction_date <= input_date
            AND s.transaction_type IN ('sale', 'invoice')
        GROUP BY
            p.name;
    END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `sp_get_daily_revenue_expense`(
    IN duration VARCHAR(15),
    IN input_date DATE
)
BEGIN
    DECLARE start_date DATE;
    DECLARE end_date DATE;

    IF duration = 'current' THEN
        SET start_date = DATE_FORMAT(input_date, '%Y-%m-01');
    ELSE
        SET start_date = input_date - INTERVAL 90 DAY;
    END IF;

    SET end_date = input_date;

    SELECT
        dates.date AS `date`,
        COALESCE(SUM(CASE WHEN l.account_type_id = 1 THEN round(l.credit/100,2) ELSE 0 END), 0) AS `revenue`,
        COALESCE(SUM(CASE WHEN l.account_type_id = 2 THEN round(l.debit/100,2) ELSE 0 END), 0) AS `expense`
    FROM
        (
            SELECT start_date + INTERVAL seq DAY AS `date`
            FROM
                (SELECT @row := @row + 1 AS seq FROM
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
                    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                     UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                     UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
                    (SELECT @row := -1) r
                ) seq_generator
            WHERE start_date + INTERVAL seq DAY <= end_date
        ) AS dates
    LEFT JOIN ledgers l
        ON l.date = dates.date
    WHERE
        l.deleted_at IS NULL
    GROUP BY dates.date
    ORDER BY dates.date;
END$$
DELIMITER ;