پروژه‌ی پایانی (۲): گزارش‌های مدیریتی، View و دسترسی گزارش‌گیر

از داده به تصمیم

مدیر کارخانه هر صبح سه سؤال دارد: کدام سفارش‌ها عقب افتاده‌اند، کدام دستگاه‌ها بهره‌وری کم یا ضایعات زیاد دارند، و فروش هر نقشه در ماه‌های اخیر چطور بوده است. هر کدام را با ابزارهایی که در فصل‌های قبل دیدیم پاسخ می‌دهیم.

۱. سفارش‌های عقب‌افتاده

WITH produced AS (
  SELECT order_item_id, SUM(produced_qty) AS made
  FROM production_jobs
  GROUP BY order_item_id
)
SELECT o.id AS order_id, c.full_name, c.city, o.due_date,
       SUM(GREATEST(CAST(oi.qty AS SIGNED) - COALESCE(pr.made, 0), 0)) AS remaining,
       DATEDIFF(CURDATE(), o.due_date) AS days_late
FROM orders o
JOIN customers c   ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN produced pr ON pr.order_item_id = oi.id
WHERE o.status IN ('confirmed', 'in_production')
  AND o.due_date < CURDATE()
GROUP BY o.id, c.full_name, c.city, o.due_date
HAVING remaining > 0
ORDER BY days_late DESC;

۲. بهره‌وری و ضایعات دستگاه‌ها در ۳۰ روز اخیر

SELECT l.code, l.hall,
       SUM(j.produced_qty) AS produced,
       SUM(j.defect_qty)   AS defects,
       ROUND(100 * SUM(j.defect_qty)
             / NULLIF(SUM(j.produced_qty + j.defect_qty), 0), 2) AS defect_pct,
       RANK() OVER (PARTITION BY l.hall ORDER BY SUM(j.produced_qty) DESC) AS rank_in_hall
FROM looms l
JOIN production_jobs j ON j.loom_id = l.id
WHERE j.finished_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY l.id, l.code, l.hall
ORDER BY l.hall, rank_in_hall;

Window Function روی نتیجه‌ی GROUP BY اجرا می‌شود؛ برای همین می‌توان SUM() را داخل ORDER BY پنجره نوشت.

۳. فروش ماهانه‌ی هر نقشه با جمع ماه

SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
       IF(GROUPING(d.name), 'TOTAL', d.name) AS design,
       SUM(oi.qty * p.area_m2)          AS sold_m2,
       SUM(oi.qty * oi.unit_price_toman) AS revenue_toman
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN designs d      ON d.id = p.design_id
WHERE o.status <> 'cancelled'
  AND o.ordered_at >= '2026-03-21'          -- ابتدای ۱۴۰۵
GROUP BY DATE_FORMAT(o.ordered_at, '%Y-%m'), d.name WITH ROLLUP;

برای ماه‌های شمسی، به‌جای DATE_FORMAT یک جدول تقویم (dim_date با ستون‌های سال و ماه شمسی) بسازید و با DATE(o.ordered_at) به آن JOIN کنید؛ همان روشی که در فصل دوم برای تاریخ شمسی دیدیم.

۴. View و دسترسی فقط‌خواندنی

CREATE VIEW v_loom_last30 AS
SELECT l.code, l.hall, SUM(j.produced_qty) AS produced, SUM(j.defect_qty) AS defects
FROM looms l JOIN production_jobs j ON j.loom_id = l.id
WHERE j.finished_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY l.id, l.code, l.hall;

GRANT SELECT ON carpet_factory.v_loom_last30 TO 'report_ro';

گزارش‌گیر فقط View را می‌بیند و به جدول‌های خام دسترسی ندارد. اگر replica دارید، ابزار گزارش را به آن وصل کنید و پیش از هر گزارش جدید، EXPLAIN ANALYZE بگیرید.

نکته‌هایی که کمتر کسی می‌داند

• تفریق دو ستون UNSIGNED که نتیجه‌اش منفی شود خطای BIGINT UNSIGNED value is out of range (ERROR 1690) می‌دهد؛ برای همین در گزارش اول CAST(... AS SIGNED) آمده است.
• تابع GROUPING() ردیف‌های جمع ROLLUP را از ردیف‌هایی که واقعاً NULL دارند جدا می‌کند؛ IFNULL این دو را با هم قاطی می‌کند.
• View در MySQL نتیجه را ذخیره نمی‌کند؛ برای داشبوردی که هر دقیقه رفرش می‌شود، جدول خلاصه‌ای بسازید که یک Event هر ساعت پرش کند.
• شرطی مثل DATE(finished_at) = CURDATE() ایندکس ix_jobs_finished را بی‌اثر می‌کند؛ بازه‌ای بنویسید: finished_at >= CURDATE() AND finished_at < CURDATE() + INTERVAL 1 DAY.