Напредни извештаи од базата (SQL и складирани процедури)

Преглед на одобрени/неодобрени набавки за надзор

CREATE PROCEDURE `Display_Nabavki_For_Nadzor` (IN UID_n INT)
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.UID_n = UID_n
    )

    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 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, 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
    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 UID_p INT)
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.UID_p = UID_p;

    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.UID_p = UID_p;

    SELECT 
        o.Objekt_ID,
        o.Tip AS Objekt_Type,
        o.Adresa AS Objekt_Address,
        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.UID_p = p.UID
    WHERE rn.UID_p = UID_p;

    SELECT 
        n.Nabavka_ID,
        DATE_FORMAT(n.Datum, '%Y-%m-%d') 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.UID_p = UID_p;
END;

Информации за соработка со добавувачи

CREATE PROCEDURE GetDobavuvacDetails(IN input_Dobavuvac_Ime VARCHAR(50))
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;

    -- 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 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;

    -- 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;