Changes between Version 3 and Version 4 of Design

Timestamp:

02/07/25 17:28:59 (4 months ago)

Author:

163080

Comment:

--

Design

@@ -19 +19 @@
 === Најважни случаи на употреба
 1. **Регистрација на нов вработен од страна на администратор**
+
+{{{
+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
+);
+}}}
+
 2. **Приказ на сите клиенти и деталите за одреден клиент**
+All
+
+{{{
+SELECT 
+    client_id, 
+    name, 
+    contact_person, 
+    email, 
+    phone, 
+    city, 
+    country 
+FROM 
+    Client
+ORDER BY 
+    name;
+
+{{{
+
+Specific Client
+
+{{{
+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 = 5  -- Replace 5 with the specific client ID
+GROUP BY 
+    cl.client_id;
+
+}}}
+
+
 3. **Креирање и уредување на фактури**
+All
+{{{
+SELECT 
+    i.invoice_id, 
+    c.name AS 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;
+
+}}}
+
+
+New Invoice
+{{{
+INSERT INTO Invoice (client_id, issue_date, due_date, total_amount)
+VALUES 
+(3, CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days', 1250.00);
+
+}}}
+
+Modify Existing Invoice
+
+{{{
+UPDATE Invoice 
+SET 
+    total_amount = 1350.00,
+    due_date = '2025-03-01',
+    status = 'Paid'
+WHERE 
+    invoice_id = 2; -- Replace 2 with the actual invoice ID
+
+}}}
+
+