Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

01/22/25 00:10:45 (9 days ago)

Author:

212054

Comment:

--

AdvancedReports

@@ -1 @@
-Во изведба
+= **Напредни извештаи од базата (SQL и складирани процедури)**
+
+=== **  Преглед и одобрување на набавка  **
+
+{{{
+CREATE PROCEDURE GetDobavuvacDetails(IN input_Dobavuvac_Ime VARCHAR(50))
+ BEGIN
+    SELECT d.Dobavuvac_Ime, d.Klasa AS Class, d.Tip AS Type, d.Poteklo AS Origin
+    FROM Dobavuvac d
+    WHERE d.Dobavuvac_Ime = input_Dobavuvac_Ime;
+
+    SELECT o.Objekt_ID, o.Tip AS Objekt_Type, o.Adresa AS Objekt_Address, n.Predmet AS Item_Supplied, n.Cena AS Item_Cost, n.Kolicina AS Quantity_Supplied
+    FROM Ispolnuva i
+    JOIN Nabavka n ON i.Nabavka_ID = n.Nabavka_ID
+    JOIN Objekt o ON n.Objekt_ID = o.Objekt_ID
+    WHERE i.Dobavuvac_Ime = input_Dobavuvac_Ime;
+
+    SELECT SUM(n.Cena * n.Kolicina) AS Total_Spent
+    FROM Ispolnuva i
+    JOIN Nabavka n ON i.Nabavka_ID = n.Nabavka_ID
+    WHERE i.Dobavuvac_Ime = input_Dobavuvac_Ime;
+ END
+}}}
+
+=== **  Информации за соработка со подизведувачи  **
+
+{{{
+CREATE PROCEDURE GetPodizveduvacDetails(IN input_Podizveduvac_Ime VARCHAR(50))
+BEGIN
+    SELECT 
+        p.Podizveduvac_Ime,
+        p.Struka AS Expertise,
+        p.Br_vraboteni AS Total_Employees,
+        p.Saatnica AS Hourly_Rate
+    FROM Podizveduvac p
+    WHERE p.Podizveduvac_Ime = input_Podizveduvac_Ime;
+
+    SELECT 
+        r.Maticen_br AS Employee_ID,
+        r.Ime AS Employee_Name,
+        r.Seniornost AS Seniority
+    FROM Raboti_Za rz
+    JOIN Rabotnik r ON rz.Maticen_br = r.Maticen_br
+    WHERE rz.Podizveduvac_Ime = input_Podizveduvac_Ime;
+
+    SELECT 
+        o.Objekt_ID,
+        o.Tip AS Objekt_Type,
+        o.Adresa AS Objekt_Address,
+        TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(rn.Od_vreme), FROM_UNIXTIME(rn.Do_vreme)) AS Total_Hours_Worked,
+        TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(rn.Od_vreme), FROM_UNIXTIME(rn.Do_vreme)) * p.Saatnica AS Total_Payments
+    FROM Raboti_Na rn
+    JOIN Objekt o ON rn.Objekt_ID = o.Objekt_ID
+    JOIN Podizveduvac p ON rn.Podizveduvac_Ime = p.Podizveduvac_Ime
+    WHERE rn.Podizveduvac_Ime = input_Podizveduvac_Ime;
+
+    SELECT 
+        n.Nabavka_ID,
+        n.Datum AS Request_Date,
+        n.Cena AS Cost,
+        n.Kolicina AS Quantity,
+        n.Predmet AS Item
+    FROM Pobaruva pb
+    JOIN Nabavka n ON pb.Nabavka_ID = n.Nabavka_ID
+    WHERE pb.Podizveduvac_Ime = input_Podizveduvac_Ime;
+END
+}}}
+
+=== **  Генерална состојба на објект  **
+
+{{{
+CREATE PROCEDURE GetObjektDetails(IN input_Objekt_ID INT)
+BEGIN
+    -- Opsti informacii za objektot
+    SELECT 
+        o.Objekt_ID,
+        o.Tip AS Objekt_Type,
+        o.Adresa AS Objekt_Address,
+        vi.Budzet AS Objekt_Budget,
+        n.Nadzor_Ime
+    FROM Objekt o
+    LEFT JOIN Vo_Izgradba vi ON o.Objekt_ID = vi.Objekt_ID
+    LEFT JOIN Nadgleduva nd ON o.Objekt_ID = nd.Objekt_ID
+    LEFT JOIN Nadzor n ON nd.Nadzor_Ime = n.Nadzor_Ime
+    WHERE o.Objekt_ID = input_Objekt_ID
+    GROUP BY o.Objekt_ID;
+
+    -- Info za podizveduvacite koi rabotele na objektot
+    SELECT 
+        pn.Podizveduvac_Ime,
+        TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(rn.Od_vreme), FROM_UNIXTIME(rn.Do_vreme)) AS Total_Hours_Worked,
+        TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(rn.Od_vreme), FROM_UNIXTIME(rn.Do_vreme)) * pn.Saatnica AS Total_Cost
+    FROM Raboti_Na rn
+    JOIN Podizveduvac pn ON rn.Podizveduvac_Ime = pn.Podizveduvac_Ime
+    WHERE rn.Objekt_ID = input_Objekt_ID;
+
+    -- Info za dobavuvachi
+    SELECT 
+        d.Dobavuvac_Ime,
+        SUM(n.Cena * n.Kolicina) AS Total_Spent
+    FROM Nabavka n
+    JOIN Nameneta_Za nz ON n.Nabavka_ID = nz.Nabavka_ID
+    JOIN Ispolnuva i ON n.Nabavka_ID = i.Nabavka_ID
+    JOIN Dobavuvac d ON i.Dobavuvac_Ime = d.Dobavuvac_Ime
+    WHERE nz.Objekt_ID = input_Objekt_ID
+    GROUP BY d.Dobavuvac_Ime;
+END
+}}}
+
+=== **  Преглед и одобрување на набавка  **
+
+{{{
+CREATE PROCEDURE `Display_Nabavki_For_Nadzor` (IN N_Ime VARCHAR(50))
+BEGIN
+    WITH select_objekt_nabavki AS (
+        SELECT 
+            o.Objekt_ID AS son_OID, 
+            nb.Nabavka_ID AS son_NID, 
+            nb.Datum AS son_D, 
+            nb.Predmet AS son_P, 
+            nb.Kolicina AS son_K, 
+            nb.Cena AS son_C 
+        FROM Objekt o
+        JOIN Nadgleduva n ON o.Objekt_ID = n.Objekt_ID
+        JOIN Nameneta_Za nz ON nz.Objekt_ID = o.Objekt_ID
+        JOIN Nabavka nb ON nb.Nabavka_ID = nz.Nabavka_ID
+        WHERE n.Nadzor_Ime = N_Ime
+    )
+
+    SELECT son_OID, son_NID, son_D, son_P, son_K, son_C
+    FROM select_objekt_nabavki son
+    LEFT JOIN Odobruva o ON son.son_NID = o.Nabavka_ID
+    WHERE o.Nabavka_ID IS NULL;
+    
+    SELECT son_OID, son_NID, son_D, son_P, son_K, son_C
+    FROM select_objekt_nabavki son
+    LEFT JOIN Odobruva o ON son.son_NID = o.Nabavka_ID
+    WHERE o.Nabavka_ID IS NOT NULL;
+END;
+}}}