Demystifying Stored Procedures in MongoDB

1
DELIMITER $$
2
3
CREATE PROCEDURE UpdateTotalRevenueForToday()
4
BEGIN
5
    DECLARE today DATE;
6
    DECLARE total_revenue DECIMAL(10, 2);
7
8
    -- Get today's date
9
    SET today = CURDATE();
10
11
    -- Calculate total revenue for today
12
    SELECT SUM(total_price) INTO total_revenue
13
    FROM orders
14
    WHERE DATE(order_date) = today;
15
16
    -- Update total revenue for today in a separate table or perform any other necessary action
17
    -- Here, I'm assuming you have a separate table named 'daily_revenue' to store daily revenue
18
    -- If not, you can perform any other desired action with the calculated total revenue
19
20
    -- Update or insert the total revenue for today into the 'daily_revenue' table
21
    INSERT INTO daily_revenue (date, revenue)
22
    VALUES (today, total_revenue)
23
    ON DUPLICATE KEY UPDATE revenue = total_revenue;
24
END$$
25
26
DELIMITER ;

1
CREATE TRIGGER AfterInsertOrder
2
AFTER INSERT ON orders
3
FOR EACH ROW
4
BEGIN
5
    CALL UpdateTotalRevenueForToday();
6
END;

1
exports = async function (changeEvent) {
2
    //Get the date from the newly inserted document
3
    const date = changeEvent.fullDocument.order_date;
4
    const ordersCollection = context.services.get("triggers-demo").db("business").collection("orders");
5
    const dailyRevenueCollection = context.services.get("triggers-demo").db("business").collection("daily_revenue");
6
7
    const pipeline = [
8
        {
9
            $group: {
10
                _id: date,
11
                total_revenue: { $sum: "$total_amount" }
12
            }
13
        }
14
    ];
15
16
    const result = await ordersCollection.aggregate(pipeline).toArray();
17
    const totalRevenue = result.length > 0 ? result[0].total_revenue : 0;
18
19
    await dailyRevenueCollection.updateOne(
20
        { date: date },
21
        { $set: { revenue: totalRevenue } },
22
        { upsert: true }
23
    );
24
};

1
{
2
    "_id": { "$oid": "65c357c89b2a791ae372bce3" },
3
    "order_date": "07-02-2024",
4
    "total_amount": 1499
5
}