| 142 | | |
| 143 | | |
| 144 | | === Stored procedure |
| 145 | | |
| 146 | | {{{ |
| 147 | | CREATE OR REPLACE FUNCTION get_analytics_stats( |
| 148 | | p_start_date DATE, |
| 149 | | p_end_date DATE |
| 150 | | ) |
| 151 | | RETURNS JSON AS $$ |
| 152 | | DECLARE |
| 153 | | result JSON; |
| 154 | | BEGIN |
| 155 | | SELECT json_build_object( |
| 156 | | 'dailyRevenue', ( |
| 157 | | SELECT json_agg(r) |
| 158 | | FROM ( |
| 159 | | SELECT DATE(timestamp) AS payment_date, SUM(amount) AS total_revenue |
| 160 | | FROM payments |
| 161 | | WHERE DATE(timestamp) BETWEEN p_start_date AND p_end_date |
| 162 | | GROUP BY DATE(timestamp) |
| 163 | | ORDER BY payment_date |
| 164 | | ) r |
| 165 | | ), |
| 166 | | 'dailyOrders', ( |
| 167 | | SELECT json_agg(r) |
| 168 | | FROM ( |
| 169 | | SELECT DATE(o.datetime) AS order_date, COUNT(o.id) AS total_orders, AVG(p.amount) AS avg_order_value |
| 170 | | FROM orders o |
| 171 | | JOIN payments p ON o.id = p.order_id |
| 172 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 173 | | GROUP BY DATE(o.datetime) |
| 174 | | ORDER BY order_date |
| 175 | | ) r |
| 176 | | ), |
| 177 | | 'orderDistribution', ( |
| 178 | | SELECT json_agg(r) |
| 179 | | FROM ( |
| 180 | | SELECT c.name AS category, COUNT(oi.id) AS total_items_ordered |
| 181 | | FROM order_items oi |
| 182 | | JOIN products p ON oi.product_id = p.id |
| 183 | | JOIN categories c ON p.category_id = c.id |
| 184 | | JOIN orders o ON o.id = oi.order_id -- filter by order date |
| 185 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 186 | | GROUP BY c.name |
| 187 | | ORDER BY total_items_ordered DESC |
| 188 | | ) r |
| 189 | | ), |
| 190 | | 'topProducts', ( |
| 191 | | SELECT json_agg(r) |
| 192 | | FROM ( |
| 193 | | SELECT p.name AS product, SUM(oi.quantity) AS total_quantity_sold |
| 194 | | FROM order_items oi |
| 195 | | JOIN products p ON oi.product_id = p.id |
| 196 | | JOIN orders o ON o.id = oi.order_id -- apply date filter |
| 197 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 198 | | GROUP BY p.name |
| 199 | | ORDER BY total_quantity_sold DESC |
| 200 | | LIMIT 10 |
| 201 | | ) r |
| 202 | | ), |
| 203 | | 'onlineVsTabOrders', ( |
| 204 | | SELECT json_agg(r) |
| 205 | | FROM ( |
| 206 | | SELECT 'Online Orders' AS order_type, COUNT(*) AS total_orders |
| 207 | | FROM online_orders oo |
| 208 | | JOIN orders o ON oo.order_id = o.id |
| 209 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 210 | | UNION ALL |
| 211 | | SELECT 'Tab Orders' AS order_type, COUNT(*) AS total_orders |
| 212 | | FROM tab_orders tord |
| 213 | | JOIN orders o ON tord.order_id = o.id |
| 214 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 215 | | ) r |
| 216 | | ), |
| 217 | | 'revenueSplit', ( |
| 218 | | SELECT json_agg(r) |
| 219 | | FROM ( |
| 220 | | SELECT 'Online Orders' AS order_type, SUM(p.amount) AS total_revenue |
| 221 | | FROM orders o |
| 222 | | JOIN payments p ON o.id = p.order_id |
| 223 | | JOIN online_orders oo ON o.id = oo.order_id |
| 224 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 225 | | UNION ALL |
| 226 | | SELECT 'Tab Orders' AS order_type, SUM(p.amount) AS total_revenue |
| 227 | | FROM orders o |
| 228 | | JOIN payments p ON o.id = p.order_id |
| 229 | | JOIN tab_orders tord ON o.id = tord.order_id |
| 230 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 231 | | ) r |
| 232 | | ), |
| 233 | | 'avgShiftDuration', ( |
| 234 | | SELECT json_agg(r) |
| 235 | | FROM ( |
| 236 | | SELECT manager_id, |
| 237 | | AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS avg_shift_duration_hours |
| 238 | | FROM shifts |
| 239 | | WHERE DATE(start_time) BETWEEN p_start_date AND p_end_date |
| 240 | | GROUP BY manager_id |
| 241 | | ) r |
| 242 | | ), |
| 243 | | 'ordersByFrontStaff', ( |
| 244 | | SELECT json_agg(r) |
| 245 | | FROM ( |
| 246 | | SELECT fs.employee_id, COUNT(tord.order_id) AS total_orders_managed |
| 247 | | FROM tab_orders tord |
| 248 | | JOIN front_staff fs ON tord.front_staff_id = fs.employee_id |
| 249 | | JOIN orders o ON tord.order_id = o.id |
| 250 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 251 | | GROUP BY fs.employee_id |
| 252 | | ORDER BY total_orders_managed DESC |
| 253 | | ) r |
| 254 | | ), |
| 255 | | 'avgTipPercentage', ( |
| 256 | | SELECT json_agg(r) |
| 257 | | FROM ( |
| 258 | | SELECT sr.name AS staff_role, AVG(fs.tip_percent) AS avg_tip_percent |
| 259 | | FROM front_staff fs |
| 260 | | JOIN staff_roles sr ON fs.staff_role_id = sr.id |
| 261 | | GROUP BY sr.name |
| 262 | | ) r |
| 263 | | ), |
| 264 | | 'inventoryTurnover', ( |
| 265 | | SELECT json_agg(r) |
| 266 | | FROM ( |
| 267 | | SELECT p.name AS product, inv.quantity AS current_inventory, |
| 268 | | SUM(oi.quantity) AS total_quantity_ordered, |
| 269 | | CASE WHEN inv.quantity = 0 THEN NULL ELSE SUM(oi.quantity)::decimal / inv.quantity END AS turnover_ratio |
| 270 | | FROM products p |
| 271 | | JOIN inventories inv ON p.id = inv.product_id |
| 272 | | JOIN order_items oi ON p.id = oi.product_id |
| 273 | | JOIN orders o ON o.id = oi.order_id |
| 274 | | WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date |
| 275 | | GROUP BY p.name, inv.quantity |
| 276 | | ORDER BY turnover_ratio DESC |
| 277 | | ) r |
| 278 | | ), |
| 279 | | 'mostPopularTable', ( |
| 280 | | SELECT json_build_object( |
| 281 | | 'table_number', table_number, |
| 282 | | 'reservation_count', reservation_count |
| 283 | | ) |
| 284 | | FROM ( |
| 285 | | SELECT table_number, COUNT(*) AS reservation_count |
| 286 | | FROM frontstaff_managed_reservations |
| 287 | | WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date |
| 288 | | GROUP BY table_number |
| 289 | | ORDER BY reservation_count DESC |
| 290 | | LIMIT 1 |
| 291 | | ) sub |
| 292 | | ), |
| 293 | | 'reservationsByDayOfWeek', ( |
| 294 | | SELECT json_agg(r) |
| 295 | | FROM ( |
| 296 | | SELECT EXTRACT(DOW FROM datetime) AS day_of_week, COUNT(*) AS total_reservations |
| 297 | | FROM reservations |
| 298 | | WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date |
| 299 | | GROUP BY day_of_week |
| 300 | | ORDER BY total_reservations DESC |
| 301 | | ) r |
| 302 | | ) |
| 303 | | ) INTO result; |
| 304 | | |
| 305 | | RETURN result; |
| 306 | | END; |
| 307 | | $$ LANGUAGE plpgsql; |
| 308 | | }}} |
| 309 | | {{{ |
| 310 | | SELECT get_analytics_stats('2025-01-01', '2025-01-31'); |
| 311 | | }}} |
