Generating Store Reports

Actors: Registered Boss

1. One of the bosses of the store request data for monthly profit for the past month from orders and refund.

2. The system gets information from orders and refunds for the month and calculates the overall profit for the month.

SELECT SUM(order.total) AS sales FROM order
WHERE order.placement_date >= DATE_TRUNC('month', CURRENT_DATE) AND
order.placement_date < (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month') AND
order.store_ID = '001';

SELECT SUM(refund.amount) AS damages FROM refund
WHERE refund.approval_date >= DATE_TRUNC('month', CURRENT_DATE) AND
order.approval_date < (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month') AND
order.store_ID = '001';

3. One of the boss of the store sends data for the store through exchanges_data and worked for the current month in the last days of the month.

INSERT INTO exchanges_data(report_date, store_ID, monthly_profit, date, sales, damages) VALUES
('2025-12-31 23:59:59', '001', 60000.00 , '2025-12-30 15:28:46', 62000.00, 2000.00);

INSERT INTO worked (personal_ID, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
('0010001', '2025-12-31 23:59:59', '001', 15000, 'monthly', 52, '2025-12-29 - 2025-12-31'),
('0010002', '2025-12-31 23:59:59', '001', 10000, 'monthly', 34, '2025-12-29 - 2025-12-31');

4. The system automatically generates a monthly report for the store on the last day of each month. The data is noted in report.

INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
('2025-12-31 23:59:59', '001', 0.00, '', '', ''),

5. The system collects hours worked, wages, and pay_type from the worked table:

SELECT personal_ID, SUM(total_hours) AS total_hours, wage, pay_method FROM worked
WHERE store_ID='001'
AND week.first_date >= DATE_TRUNC('month', CURRENT_DATE)
AND week.last_date < (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month')
GROUP BY personal_ID;

6. The system collects the monthly_profit, sales and damages from exchanges_data:

SELECT monthly_profit AS total_monthly_profit, sales AS total_sales, damages AS total_damages
FROM exchanges_data
WHERE store_ID='001'
AND date >= DATE_TRUNC('month', CURRENT_DATE)
AND date < (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month');

7. The report is in the report table is updated with the data:

UPDATE report
SET overall_profit='35000', 
sales_trend='Growing', 
markething_growth='Stable'
WHERE report.date >= DATE_TRUNC('month', CURRENT_DATE) AND
report.date < (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month') AND
report.store_ID = '001';

8. The system sends the report to all bosses of the store for approval.

SELECT b.boss_ID, p.first_name, p.last_name, p.email
FROM boss b
JOIN personal p ON b.boss_ID = p.id
JOIN works_in_store w ON p.id = w.personal_ID
WHERE w.store_ID='001';

9. Each boss reviews and signs the report. Once at least one boss signs, the report is considered approved.

UPDATE report
SET owner_signature = boss.signature
WHERE store_ID='001' AND date = '2025-12-30 00:00:00' AND boss.boss_ID = works_in.personal_ID and works_id.store_ID = '001';