== 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.


{{{#!sql
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.

{{{#!sql
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);

}}}

{{{#!sql
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}}}.

{{{#!sql
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:

{{{#!sql
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}}}:

{{{#!sql
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:

{{{#!sql
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. 

{{{#!sql
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.

{{{#!sql
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';
}}}