Changes between Version 2 and Version 3 of PrototypeApplication

Timestamp:

01/22/25 00:11:33 (9 days ago)

Author:

212054

Comment:

--

PrototypeApplication

@@ -1 @@
-= **Напредни извештаи од базата (SQL и складирани процедури)**
-
-=== ** Преглед и одобрување на набавка **
-
-{{{
-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;
-}}}
-
-=== ** Интерфејс за создавање на едноставно квери **
-
-{{{
- 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;
-}}}
-
-=== ** Генерална состојба на објект **
-
-{{{
-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 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 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
-}}}