Changes between Version 9 and Version 10 of Design

Timestamp:

08/13/25 16:27:49 (6 days ago)

Author:

163080

Comment:

--

Design

@@ -18 +18 @@
 
 === Најважни случаи на употреба
-1. **Регистрација на нов вработен од страна на администратор**
+1. **Регистрација на нов вработен (само Админ)**
+**Чекори:**
+1. Администраторот пополнува форма со податоци за нов вработен.
+2. Системот ги запишува податоците во `Employee`.
 
 {{{
-CREATE TABLE Employee (
-    employee_id BIGSERIAL PRIMARY KEY,
-    first_name VARCHAR(255) NOT NULL,
-    last_name VARCHAR(255) NOT NULL,
-    email VARCHAR(255) UNIQUE NOT NULL,
-    password_hash VARCHAR(255) NOT NULL,
-    role VARCHAR(50) CHECK (role IN ('Admin', 'Manager', 'Accountant')) NOT NULL,
-    date_of_employment DATE NOT NULL DEFAULT CURRENT_DATE
-);
+INSERT INTO Employee (first_name, last_name, email, status, iban, cv, photo, notes, company_id, created_at, updated_at)
+VALUES ('Alice', 'Johnson', 'alice.johnson@agencyos.com', 'Active', 'MK121212121212', '/cvs/alice_johnson.pdf', '/photos/alice.jpg', 'Great with clients', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
 }}}
 
-2. **Приказ на сите клиенти и деталите за одреден клиент**
-All
+2. **Преглед на сите вработени во компанија**
+**Чекори:**
+1. HR или Админ избира компанија.
+2. Системот враќа листа од сите вработени за таа компанија.
 
 {{{
-SELECT 
-    client_id, 
-    client_name, 
-    contact_person, 
-    client_email, 
-    phone, 
-    city, 
-    country 
-FROM 
-    Client
-ORDER BY 
-    name;
-
+SELECT employee_id, first_name, last_name, email, status
+FROM Employee
+WHERE company_id = 1;
 }}}
 
-Specific Client
+3. **Преглед на сите клиенти**
 
 {{{
-SELECT 
-    cl.*, 
-    COUNT(i.invoice_id) AS total_invoices, 
-    SUM(i.total_amount) AS total_amount_due
-FROM 
-    Client cl
-LEFT JOIN 
-    Invoice i ON cl.client_id = i.client_id AND i.status = 'Unpaid'
-WHERE 
-    cl.client_id = X
-GROUP BY 
-    cl.client_id;
-
+SELECT client_id, name, email, phone, city, country
+FROM Client
+ORDER BY name;
 }}}
 
-
-3. **Креирање и уредување на фактури**
-All
-
+4. **Преглед на деталите за конкретен клиент**
 {{{
-SELECT 
-    i.invoice_id, 
-    c.client_name, 
-    i.issue_date, 
-    i.due_date, 
-    i.total_amount, 
-    i.status
-FROM 
-    Invoice i
-JOIN 
-    Client c ON i.client_id = c.client_id
-ORDER BY 
-    i.issue_date DESC;
-
+SELECT cl.*, COUNT(i.invoice_id) AS total_invoices, SUM(li.total) AS total_billed
+FROM Client cl
+LEFT JOIN Invoice i ON i.client_id = cl.client_id
+LEFT JOIN LineItem li ON li.invoice_id = i.invoice_id
+WHERE cl.client_id = 1
+GROUP BY cl.client_id;
 }}}
 
-
-New Invoice
+5. **Креирање на нова фактура**
 {{{
-INSERT INTO Invoice (client_id, issue_date, due_date, total_amount)
-VALUES 
-(3, CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', 1250.00);
-
+INSERT INTO Invoice ("from", "to", issue_date, due_date, notes, status, company_id, client_id, created_at, updated_at)
+VALUES ('AgencyOS Inc.', 'Global Enterprises', CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', 'Project Alpha delivery', 'Pending', 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
 }}}
 
-UPDATE Invoice 
+6. **Уредување на постоечка фактура**
 {{{
-SET 
-    total_amount = 1350.00,
-    due_date = '2025-03-01',
-    status = 'Paid'
-WHERE 
-    invoice_id = X;
+UPDATE Invoice
+SET status = 'Paid', updated_at = CURRENT_TIMESTAMP
+WHERE invoice_id = 1;
+}}}
 
+7. **Преглед на фактури за клиент**
+{{{
+SELECT i.invoice_id, i.issue_date, i.due_date, i.status, SUM(li.total) AS total
+FROM Invoice i
+LEFT JOIN LineItem li ON li.invoice_id = i.invoice_id
+WHERE i.client_id = 1
+GROUP BY i.invoice_id;
 }}}
+
+8. **Преглед на детали за фактура**
+{{{
+SELECT i.*, li.description, li.quantity, li.unit_price, li.total
+FROM Invoice i
+JOIN LineItem li ON li.invoice_id = i.invoice_id
+WHERE i.invoice_id = 1;
+}}}