Changes between Initial Version and Version 1 of GenerateReports

Timestamp:

12/30/25 17:39:41 (14 hours ago)

Author:

235018

Comment:

--

GenerateReports

@@ +1 @@
+== 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';
+}}}