Напредни извештаи од базата (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