oze4

-- Tworzenie tabeli urządzeń OZE

CREATE TABLE Renewable_Energy_Devices (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6)
);

-- Wstawianie danych do tabeli urządzeń OZE
INSERT INTO Renewable_Energy_Devices (device_id, device_status)
VALUES
('12345678901234567890123456',	0),
('22345678901234567890123456',	1),
('32345678901234567890123456',	2),
('42345678901234567890123456',	3),
('52345678901234567890123456',	4),
('62345678901234567890123456',	0),
('72345678901234567890123456',	1),
('82345678901234567890123456',	2),
('92345678901234567890123456',	3),
('10234567890123456789012345',	4),
('11234567890123456789012345',	0),
('12234567890123456789013456',	1),
('13234567890123456789012456',	2),
('14234567890123456789012345',	3),
('15234567890123456789012345',	4),
('16234567890123456789012345',	0),
('17234567890123456789012356',	1),
('18234567890123456789012345',	2),
('19234567890123456789012345',	3),
('20234567890123456789012345',	4),
('21234567890123456789012345',	0),
('22234567890123456789012345',	1),
('23234567890123456789012345',	2),
('24234567890123456789012345',	3),
('25234567890123456789012345',	4),
('26234567890123456789012345',	0),
('27234567890123456789012345',	1),
('28234567890123456789012345',	2),
('29234567890123456789012345',	3),
('30234567890123456789012345',	4),
('31234567890123456789012345',	0),
('32234567890123456789012345',	1),
('33234567890123456789012345',	2),
('34234567890123456789012345',	3),
('35234567890123456789012345',	4),
('36234567890123456789012345',	0),
('37234567890123456789012345',	1),
('38234567890123456789012345',	2),
('39234567890123456789012345',	3),
('40234567890123456789012345',	4),
('41234567890123456789012345',	0),
('42234567890123456789012345',	1),
('43234567890123456789012345',	2),
('44234567890123456789012345',	3),
('45234567890123456789012345',	4);

	   --('12345678901234567890123456', 0),
       --('22345678901234567890123456', 1),
       --('32345678901234567890123456', 2),
       --('42345678901234567890123456', 3),
       --('52345678901234567890123456', 4);


-- Tworzenie tabeli zgłoszeń awarii

CREATE TABLE Device_Failures (
    failure_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    shift_number INT,
    failure_start DATETIME,
    failure_end DATETIME,
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

-- Wstawianie danych do tabeli zgłoszeń awarii

DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO Device_Failures (device_id, shift_number, failure_start, failure_end)
    VALUES (CONCAT(@i, '2345678901234567890123456'), 1, DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()));
    SET @i = @i + 1
END

-- Tworzenie tabeli klientów

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address VARCHAR(255)
);

-- Wstawianie danych do tabeli klientów
INSERT INTO Customers (customer_id, name, email, phone, address)
VALUES
(1, 'John Doe', '[email protected]', '123456789', '123 Main St'),
(2, 'Jane Smith', '[email protected]', '987654321', '456 Elm St'),
(3, 'Michael Johnson', '[email protected]', '555123456', '789 Oak St'),
(4, 'Emily Brown', '[email protected]', '444987654', '321 Pine St'),
(5, 'William Wilson', '[email protected]', '789456123', '654 Birch St'),
(6, 'Olivia Taylor', '[email protected]', '987123456', '987 Maple St'),
(7, 'James Martinez', '[email protected]', '321654987', '159 Cedar St'),
(8, 'Emma Anderson', '[email protected]', '666333999', '753 Walnut St'),
(9, 'Noah Thomas', '[email protected]', '222333444', '852 Spruce St'),
(10, 'Ava Hernandez', '[email protected]', '111222333', '369 Cherry St'),
(11, 'Sophia Lopez', '[email protected]', '777888999', '741 Elm St'),
(12, 'Matthew Gonzalez', '[email protected]', '222111333', '258 Oak St'),
(13, 'Isabella Perez', '[email protected]', '666777888', '951 Pine St'),
(14, 'Jacob Robinson', '[email protected]', '999888777', '753 Maple St'),
(15, 'Ethan Lee', '[email protected]', '111222333', '852 Cedar St'),
(16, 'Oliver Walker', '[email protected]', '444555666', '963 Walnut St'),
(17, 'Daniel Hall', '[email protected]', '888777666', '357 Birch St'),
(18, 'Liam Young', '[email protected]', '999888777', '159 Spruce St'),
(19, 'Charlotte Hernandez', '[email protected]', '333222111', '456 Cherry St'),
(20, 'Amelia Moore', '[email protected]', '222333444', '789 Elm St'),
(21, 'Benjamin Nelson', '[email protected]', '444555666', '357 Pine St'),
(22, 'Lucas Hill', '[email protected]', '111222333', '852 Maple St'),
(23, 'Mia King', '[email protected]', '555666777', '963 Cedar St'),
(24, 'Harper Baker', '[email protected]', '777888999', '357 Walnut St'),
(25, 'Evelyn Adams', '[email protected]', '999888777', '456 Birch St');

INSERT INTO Customers (customer_id, name, email, phone, address)
VALUES (26, 'John Doe', '[email protected]', '123456789', '556 Main St'),
       (27, 'Jane Smith', '[email protected]', '987654321', '431 Elm St');




-- Tworzenie tabeli firm serwisowych

CREATE TABLE Servicing_Companies (
    company_id INT PRIMARY KEY,
    name VARCHAR(255),
    working_hours VARCHAR(255) -- Można to dostosować w zależności od potrzeb
);

-- Wstawianie danych do tabeli firm serwisowych

INSERT INTO Servicing_Companies (company_id, name, working_hours)
VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');




-- Tworzenie tabeli zmian serwisowych

CREATE TABLE Servicing_Shifts (
    shift_number INT PRIMARY KEY IDENTITY,
    shift_date DATE,
    shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
    start_time TIME,
    end_time TIME
);

-- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
INSERT INTO Servicing_Shifts (shift_date, shift_type, start_time, end_time) VALUES
('2024-04-01', 1, '06:00:00', '14:00:00'),
('2024-04-01', 2, '14:00:00', '22:00:00'),
('2024-04-01', 3, '22:00:00', '06:00:00'),
('2024-04-02', 1, '06:00:00', '14:00:00'),
('2024-04-02', 2, '14:00:00', '22:00:00'),
('2024-04-02', 3, '22:00:00', '06:00:00'),
('2024-04-03', 1, '06:00:00', '14:00:00'),
('2024-04-03', 2, '14:00:00', '22:00:00'),
('2024-04-03', 3, '22:00:00', '06:00:00'),
('2024-04-04', 1, '06:00:00', '14:00:00'),
('2024-04-04', 2, '14:00:00', '22:00:00'),
('2024-04-04', 3, '22:00:00', '06:00:00'),
('2024-04-05', 1, '06:00:00', '14:00:00'),
('2024-04-05', 2, '14:00:00', '22:00:00'),
('2024-04-05', 3, '22:00:00', '06:00:00'),
('2024-04-06', 1, '06:00:00', '14:00:00'),
('2024-04-06', 2, '14:00:00', '22:00:00'),
('2024-04-06', 3, '22:00:00', '06:00:00');



-- Tworzenie tabeli napraw

 CREATE TABLE Repairs (
    repair_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    repair_start DATETIME,
    repair_end DATETIME,
    repair_description NVARCHAR(255),
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

-- Wstawianie danych do tabeli napraw

DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
    VALUES (CONCAT(@i, '2345678901234567890123456'), DATEADD(DAY, -@i, GETDATE()), DATEADD(DAY, -@i+1, GETDATE()), 'Naprawa urządzenia');
    SET @i = @i + 1
END




--RAPORTY ####################################################3

--Na jakiej zmianie dane urządzenie OZE uległo awarii:

SELECT df.device_id, df.shift_number
FROM Device_Failures df
ORDER BY df.failure_start;


--Na jakiej zmianie urządzenie OZE zostało naprawione:

SELECT r.device_id, df.shift_number
FROM Repairs r
JOIN Device_Failures df ON r.device_id = df.device_id
WHERE r.repair_start BETWEEN df.failure_start AND df.failure_end
ORDER BY r.repair_start;


--Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu:

SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
GROUP BY df.device_id;


--Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu nie wliczając w to czasu kiedy firma serwisująca nie pracowała (weekendy):

SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
GROUP BY df.device_id;


--Jaki był sumaryczny czas przestoju wszystkich urządzeń OZE w ciągu zadanego okresu czasu nie wliczając w to czasu kiedy firma serwisująca nie pracowała (weekendy):

SELECT SUM(downtime_hours) AS total_downtime_hours
FROM (
    SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
    FROM Device_Failures df
    JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
    WHERE DATENAME(WEEKDAY, df.failure_start) NOT IN ('Saturday', 'Sunday')
    GROUP BY df.device_id
) AS downtime_per_device;



--Jaki jest procentowy udział urządzeń OZE w poszczególnych fazach awarii w stosunku do ilości wszystkich dostępnych urządzeń OZE:

WITH Device_Failure_Count AS (
    SELECT device_id, COUNT(*) AS failure_count
    FROM Device_Failures
    GROUP BY device_id
)
SELECT df.device_status, COUNT(*) AS device_count, DFC.failure_count, CAST(COUNT(*) AS DECIMAL) / DFC.failure_count * 100 AS percentage
FROM Renewable_Energy_Devices df
JOIN Device_Failure_Count DFC ON df.device_id = DFC.device_id
GROUP BY df.device_status, DFC.failure_count;


--Na ilu zmianach nie pracowało dane urządzenie OZE (wliczając to zmianę na której zgłoszono awarię i na której uruchomiono ja znów produkcyjnie):

SELECT df.device_id, COUNT(DISTINCT df.shift_number) AS downtime_shifts
FROM Device_Failures df
JOIN Servicing_Shifts ss ON df.shift_number = ss.shift_number
GROUP BY df.device_id;