Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

02/11/25 01:06:24 (2 months ago)

Author:

212054

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = **Напредни извештаи од базата (SQL и складирани процедури)**
 
-=== ** Преглед и одобрување на набавка **
+=== ** Преглед на одобрени/неодобрени набавки за надзор**
 
 {{{
-CREATE PROCEDURE `Display_Nabavki_For_Nadzor` (IN N_Ime VARCHAR(50))
+CREATE PROCEDURE `Display_Nabavki_For_Nadzor` (IN UID_n INT)
 BEGIN
     WITH select_objekt_nabavki AS (
@@ -18 +18 @@
         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
+        WHERE n.UID_n = UID_n
     )
 
@@ -31 +31 @@
     WHERE o.Nabavka_ID IS NOT NULL;
 END;
-}}}
-
-=== ** Интерфејс за создавање на едноставно квери **
-
-{{{
- CREATE PROCEDURE `simple_select_with_filter` (
-    IN od VARCHAR(50),      
-    IN shto VARCHAR(50),    
-    IN filter_uslov TEXT  
- )
- BEGIN
-    SET @query = CONCAT('SELECT ', shto, ' FROM ', od);
-    
-    IF filter_uslov IS NOT NULL AND filter_uslov != '' THEN
-        SET @query = CONCAT(@query, ' WHERE ', filter_uslov);
-    END IF;
-    
-    PREPARE stmt FROM @query;
-    EXECUTE stmt;
-    DEALLOCATE PREPARE stmt;
- END;
 }}}
 
@@ -76 +55 @@
     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
+        TIMESTAMPDIFF(HOUR, rn.Od_vreme, rn.Do_vreme) AS Total_Hours_Worked,
+        TIMESTAMPDIFF(HOUR, rn.Od_vreme, rn.Do_vreme) * pn.Saatnica AS Total_Cost
     FROM Raboti_Na rn
     JOIN Podizveduvac pn ON rn.Podizveduvac_Ime = pn.Podizveduvac_Ime
@@ -92 +71 @@
     WHERE nz.Objekt_ID = input_Objekt_ID
     GROUP BY d.Dobavuvac_Ime;
-END
+END;
 }}}
 
@@ -98 +77 @@
 
 {{{
-CREATE PROCEDURE GetPodizveduvacDetails(IN input_Podizveduvac_Ime VARCHAR(50))
+CREATE PROCEDURE GetPodizveduvacDetails(IN UID_p INT)
 BEGIN
     SELECT 
@@ -106 +85 @@
         p.Saatnica AS Hourly_Rate
     FROM Podizveduvac p
-    WHERE p.Podizveduvac_Ime = input_Podizveduvac_Ime;
+    WHERE p.UID_p = UID_p;
 
     SELECT 
@@ -114 +93 @@
     FROM Raboti_Za rz
     JOIN Rabotnik r ON rz.Maticen_br = r.Maticen_br
-    WHERE rz.Podizveduvac_Ime = input_Podizveduvac_Ime;
+    WHERE rz.UID_p = UID_p;
 
     SELECT 
@@ -120 +99 @@
         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
+        TIMESTAMPDIFF(HOUR, DATE_FORMAT(rn.Od_vreme, '%Y-%m-%d'), DATE_FORMAT(rn.Do_vreme, '%Y-%m-%d')) AS Total_Hours_Worked,
+        TIMESTAMPDIFF(HOUR, DATE_FORMAT(rn.Od_vreme, '%Y-%m-%d'), DATE_FORMAT(rn.Do_vreme, '%Y-%m-%d')) * 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;
+    JOIN Podizveduvac p ON rn.UID_p = p.UID
+    WHERE rn.UID_p = UID_p;
 
     SELECT 
         n.Nabavka_ID,
-        n.Datum AS Request_Date,
+        DATE_FORMAT(n.Datum, '%Y-%m-%d') AS Request_Date,
         n.Cena AS Cost,
         n.Kolicina AS Quantity,
@@ -135 +114 @@
     FROM Pobaruva pb
     JOIN Nabavka n ON pb.Nabavka_ID = n.Nabavka_ID
-    WHERE pb.Podizveduvac_Ime = input_Podizveduvac_Ime;
-END
+    WHERE pb.UID_p = UID_p;
+END;
 }}}
 
@@ -143 +122 @@
 {{{
 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
+BEGIN
+    -- Basic info
+    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
+    -- Object supply info
+    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
+    JOIN Nameneta_Za nz ON n.Nabavka_ID = nz.Nabavka_ID
+    JOIN Objekt o ON nz.Objekt_ID = o.Objekt_ID
     WHERE i.Dobavuvac_Ime = input_Dobavuvac_Ime;
 
-    SELECT SUM(n.Cena * n.Kolicina) AS Total_Spent
+    -- Total spent
+    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
+END;
 }}}