SQL Kodları

-- Veritabanı oluşturulması
-- Bu veritabanı, bir gaz dağıtım sistemindeki valflerin, hatların, müşterilerin, çalışanların, bakım işlemlerinin, sensörlerin, olay kayıtlarının ve faturalandırmanın yönetimini sağlar.
IF DB_ID('GazValfiYonetim') IS NOT NULL
    DROP DATABASE GazValfiYonetim;
GO
CREATE DATABASE GazValfiYonetim;
GO

-- Veritabanı kullanımı
USE GazValfiYonetim;
GO

-- Gaz Valfi tablosu
IF OBJECT_ID('dbo.GazValfi', 'U') IS NOT NULL
    DROP TABLE dbo.GazValfi;
GO
CREATE TABLE GazValfi (
    ValfID INT PRIMARY KEY IDENTITY(1,1),
    HatID INT NOT NULL,
    Durum NVARCHAR(50) NOT NULL,
    SizintiDurumu BIT DEFAULT 0,
    SonBakimTarihi DATE NOT NULL
);

-- Dağıtım Hattı tablosu
IF OBJECT_ID('dbo.DagitimHatti', 'U') IS NOT NULL
    DROP TABLE dbo.DagitimHatti;
GO
CREATE TABLE DagitimHatti (
    HatID INT PRIMARY KEY IDENTITY(1,1),
    HatAdi NVARCHAR(100) NOT NULL,
    ToplamGazAkisi FLOAT DEFAULT 0 CHECK (ToplamGazAkisi>=0),
    Durum NVARCHAR(50) NOT NULL
);

-- Müşteri tablosu
IF OBJECT_ID('dbo.Musteri', 'U') IS NOT NULL
    DROP TABLE dbo.Musteri;
GO
CREATE TABLE Musteri (
    MusteriID INT PRIMARY KEY IDENTITY(1,1),
    AdSoyad NVARCHAR(100) NOT NULL,
    Adres NVARCHAR(255) NOT NULL,
    KullanilanGaz FLOAT DEFAULT 0 CHECK (KullanilanGaz>=0),
    HatID INT NOT NULL,
    FOREIGN KEY (HatID) REFERENCES DagitimHatti(HatID)
);

-- Çalışanlar tablosu
IF OBJECT_ID('dbo.Calisanlar', 'U') IS NOT NULL
    DROP TABLE dbo.Calisanlar;
GO
CREATE TABLE Calisanlar (
    CalisanID INT PRIMARY KEY IDENTITY(1,1),
    AdSoyad NVARCHAR(100) NOT NULL,
    Gorev NVARCHAR(50) NOT NULL,
    Telefon NVARCHAR(15) NOT NULL UNIQUE,
    Adres NVARCHAR(100) NOT NULL
);

-- Bakım tablosu
IF OBJECT_ID('dbo.Bakim', 'U') IS NOT NULL
    DROP TABLE dbo.Bakim;
GO
CREATE TABLE Bakim (
    BakimID INT PRIMARY KEY IDENTITY(1,1),
    ValfID INT NOT NULL,
    CalisanID INT NOT NULL,
    BakimTarihi DATE NOT NULL,
    Aciklama NVARCHAR(255) NOT NULL,
    FOREIGN KEY (ValfID) REFERENCES GazValfi(ValfID),
    FOREIGN KEY (CalisanID) REFERENCES Calisanlar(CalisanID)
);

-- Sensör tablosu
IF OBJECT_ID('dbo.Sensor', 'U') IS NOT NULL
    DROP TABLE dbo.Sensor;
GO
CREATE TABLE Sensor (
    SensorID INT PRIMARY KEY IDENTITY(1,1),
    ValfID INT NOT NULL,
    SensorTipi NVARCHAR(50) NOT NULL,
    Deger FLOAT DEFAULT 0 CHECK (Deger>=0),
    FOREIGN KEY (ValfID) REFERENCES GazValfi(ValfID)
);

-- Olay Kaydı tablosu
IF OBJECT_ID('dbo.OlayKaydi', 'U') IS NOT NULL
    DROP TABLE dbo.OlayKaydi;
GO
CREATE TABLE OlayKaydi (
    OlayID INT PRIMARY KEY IDENTITY(1,1),
    ValfID INT NOT NULL,
    OlayTarihi DATETIME DEFAULT GETDATE(),
    OlayTipi NVARCHAR(100) NOT NULL,
    Aciklama NVARCHAR(255),
    FOREIGN KEY (ValfID) REFERENCES GazValfi(ValfID)
);

-- Fatura tablosu
IF OBJECT_ID('dbo.Fatura', 'U') IS NOT NULL
    DROP TABLE dbo.Fatura;
GO
CREATE TABLE Fatura (
    FaturaID INT PRIMARY KEY IDENTITY(1,1),
    MusteriID INT NOT NULL,
    FaturaTutari FLOAT DEFAULT 0 CHECK (FaturaTutari>=0),
    FaturaTarihi DATE NOT NULL,
    Aciklama NVARCHAR(255) NOT NULL,
    FOREIGN KEY (MusteriID) REFERENCES Musteri(MusteriID)
);

-- Örnek veri ekleme
-- Dağıtım Hatları
INSERT INTO DagitimHatti (HatAdi, ToplamGazAkisi, Durum)
VALUES ('Hat A', 500.5, 'Aktif'),
       ('Hat B', 300.2, 'Aktif'),
       ('Hat C', 0, 'Bakımda');

-- Gaz Valfleri
INSERT INTO GazValfi (HatID, Durum, SonBakimTarihi)
VALUES (1, 'Aktif', '2024-01-01'),
       (2, 'Aktif', '2023-12-01'),
       (3, 'Kapalı', '2023-11-15');

-- Müşteriler
INSERT INTO Musteri (AdSoyad, Adres, KullanilanGaz, HatID)
VALUES ('Ahmet Yılmaz', 'Ankara, Türkiye', 150.75, 1),
       ('Fatma Kaya', 'İstanbul, Türkiye', 200.5, 2),
       ('Mehmet Demir', 'İzmir, Türkiye', 100.3, 1);

-- Çalışanlar
INSERT INTO Calisanlar (AdSoyad, Gorev, Telefon, Adres)
VALUES ('Ali Vural', 'Teknisyen', '05551234567', 'Işıl Cd. 101 Sk. No: 3 D: 2 Beylikdüzü / İstanbul'),
       ('Emine Korkmaz', 'Bakım Uzmanı', '05559876543', 'Vatan Cd. 1923 Sk. No: 12 D: 83 Küçükçekmece / İstanbul');

-- Bakım
INSERT INTO Bakim (ValfID, CalisanID, BakimTarihi, Aciklama)
VALUES (1, 1, '2024-02-15', 'Valf genel bakımı yapıldı.'),
       (2, 2, '2023-11-20', 'Sensör değişimi yapıldı.');

-- Sensörler
INSERT INTO Sensor (ValfID, SensorTipi, Deger)
VALUES (1, 'Basınç', 1.5),
       (1, 'Sıcaklık', 45.2),
       (2, 'Basınç', 2.3);

-- Faturalar
INSERT INTO Fatura (MusteriID, FaturaTutari, FaturaTarihi, Aciklama)
VALUES (1, 300.50, '2024-03-01', 'Valf Bakım Ücreti'),
       (2, 400.75, '2024-03-02' 'Hat Alma Ücreti'),
       (3, 200.25, '2024-03-03' 'Valf Kayıt Ücreti');

-- Olay Kayıtları
INSERT INTO OlayKaydi (ValfID, OlayTarihi, OlayTipi, Aciklama)
VALUES (1, '2024-02-20', 'Bakım Yapıldı', 'Valf üzerindeki düzenli bakım tamamlandı.'),
       (2, '2024-02-25', 'Gaz Kaçağı Algılandı', 'Valf üzerinde gaz kaçağı tespit edildi.');

-- Trigger: Sızıntı Tespiti
-- Bu trigger, GazValfi tablosunda bir güncelleme olduğunda çalışır.
-- Eğer bir valf için sızıntı durumu '1' olarak ayarlanmışsa, OlayKaydi tablosuna bir kayıt ekler.
IF OBJECT_ID('dbo.Trigger_SizintiTespit', 'TR') IS NOT NULL
    DROP TRIGGER dbo.Trigger_SizintiTespit;
GO
CREATE TRIGGER Trigger_SizintiTespit
ON GazValfi
AFTER UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM (SELECT SizintiDurumu, ValfID FROM GazValfi) AS updatedData WHERE SizintiDurumu = 1
    )
    BEGIN
        -- Yeni bir olay kaydı ekle
        INSERT INTO OlayKaydi (ValfID, OlayTipi, Aciklama)
        SELECT ValfID, 'Sızıntı Tespit Edildi', 'Valf üzerinde gaz sızıntısı algılandı.'
        FROM GazValfi WHERE SizintiDurumu = 1;
    END
END;
GO

-- Fonksiyon: Toplam Kullanım Hesaplama
-- Bu fonksiyon, bir dağıtım hattına bağlı tüm müşterilerin toplam gaz tüketimini hesaplar.
-- Örneğin, bu fonksiyon hattın aşırı yüklenip yüklenmediğini kontrol etmek için kullanılabilir.
IF OBJECT_ID('dbo.ToplamKullanimiHesapla', 'FN') IS NOT NULL
    DROP FUNCTION dbo.ToplamKullanimiHesapla;
GO
CREATE FUNCTION ToplamKullanimiHesapla (@HatID INT)  -- Fonksiyon oluşturuluyor, @HatID parametresi alıyor
RETURNS FLOAT  -- Fonksiyon, FLOAT tipinde bir değer döndürecek
AS
BEGIN  -- Fonksiyon bloğu başlıyor
    DECLARE @ToplamKullanim FLOAT;  -- Toplam kullanım için FLOAT tipi ayarlanıyor
    SELECT @ToplamKullanim = SUM(KullanilanGaz)  -- KullanilanGaz sütununun toplamı SUM ile @ToplamKullanim değişkenine atanıyor
    FROM Musteri  -- Musteri tablosundan veri çekiliyor
    WHERE HatID = @HatID;  -- Veriler, belirtilen HatID'ye göre filtreleniyor
    RETURN ISNULL(@ToplamKullanim, 0);  -- NULL ise 0 döndürülüyor, aksi takdirde toplam değer döndürülüyor
END;  -- Fonksiyon bloğu bitiyor
GO

-- Fonksiyon: Çalışan Başına Bakım Sayısı
-- Bu fonksiyon, belirli bir çalışanın gerçekleştirdiği bakım sayısını hesaplar.
-- Çalışan performansını analiz etmek için kullanılabilir.
IF OBJECT_ID('dbo.CalisanBakimSayisi', 'FN') IS NOT NULL
    DROP FUNCTION dbo.CalisanBakimSayisi;
GO
CREATE FUNCTION CalisanBakimSayisi (@CalisanID INT)  -- Fonksiyon oluşturuluyor, @CalisanID parametresi alıyor
RETURNS INT  -- Fonksiyon, INT tipinde bir değer döndürecek
AS
BEGIN  -- Fonksiyon bloğu başlıyor
    DECLARE @BakimSayisi INT;  -- Bakım sayısı için INT tipi ayarlanıyor
    SELECT @BakimSayisi = COUNT(*)  -- Bakim tablosunda, belirtilen @CalisanID'ye sahip tüm kayıtların sayısı @BakimSayisi değişkenine atanıyor
    FROM Bakim  -- Bakim tablosundan veri çekiliyor
    WHERE CalisanID = @CalisanID;  -- Veriler, belirtilen CalisanID'ye göre filtreleniyor
    RETURN @BakimSayisi;  -- Bakım sayısı döndürülüyor
END;  -- Fonksiyon bloğu bitiyor
GO

-- Trigger: Yeni Müşteri Ekleme Logu
-- Bu trigger, Musteri tablosuna yeni bir kayıt eklendiğinde çalışır.
-- Yeni eklenen müşterinin bilgilerini OlayKaydi tablosuna bir log olarak kaydeder.
IF OBJECT_ID('dbo.Trigger_YeniMusteriEkle', 'TR') IS NOT NULL
    DROP TRIGGER dbo.Trigger_YeniMusteriEkle;
GO
CREATE TRIGGER Trigger_YeniMusteriEkle
ON Musteri
AFTER INSERT
AS
BEGIN
    -- Yeni müşteri eklendiğinde bir olay kaydı oluştur
    INSERT INTO OlayKaydi (ValfID, OlayTipi, Aciklama)
    SELECT 
        -- Bu sorgu, müşteri eklenen hattın ilk valfini seçecek (hatID'ye göre)
        TOP 1 GazValfi.ValfID, 
        'Yeni Müşteri Eklendi', 
        'Yeni bir müşteri kayıt edildi: ' + Musteri.AdSoyad
    FROM GazValfi
    INNER JOIN Musteri ON GazValfi.HatID = Musteri.HatID
    WHERE Musteri.MusteriID = (SELECT MAX(MusteriID) FROM Musteri) -- Yalnızca yeni eklenen müşteri
    ORDER BY GazValfi.ValfID; -- HatID'ye bağlı valflerden ilkini seç
END;

-- Saklı Yordam: Gaz kullanımı arttırma fonksiyonu
-- Musteri ve DagitimHatti tablolarını güncelleme fonksiyon kodları
IF OBJECT_ID('dbo.MusteriGazArttir', 'P') IS NOT NULL
    DROP PROCEDURE dbo.MusteriGazArttir;
GO
CREATE PROCEDURE MusteriGazArttir
    @MusteriID INT,
    @Miktar FLOAT,
    @Sonuc NVARCHAR(100) OUTPUT -- Çıktıyı almak için OUTPUT parametresi
AS
BEGIN
    BEGIN TRY
        -- Müşteri tablosundaki KullanilanGaz sütununu güncelleme
        UPDATE Musteri
        SET KullanilanGaz = KullanilanGaz + @Miktar
        WHERE MusteriID = @MusteriID;

        -- İlgili müşteri ID'sine ait HatID'yi alma ve toplam gaz akışını güncelleme
        DECLARE @HatID INT;
        SELECT @HatID = HatID FROM Musteri WHERE MusteriID = @MusteriID;

        UPDATE DagitimHatti
        SET ToplamGazAkisi = ToplamGazAkisi + @Miktar
        WHERE HatID = @HatID;

        -- Çıktı mesajını ayarlama
        SET @Sonuc = 'Güncelleme başarıyla tamamlandı.';
    END TRY
    BEGIN CATCH
        -- Hata durumunda çıktıyı ayarlama
        SET @Sonuc = 'Güncelleme sırasında bir hata oluştu: ' + ERROR_MESSAGE();
    END CATCH
END;
GO

-- Saklı Yordam: Valf durumu kontrolü ve güncelleme
IF OBJECT_ID('dbo.ValfDurumuGuncelle', 'P') IS NOT NULL
    DROP PROCEDURE dbo.ValfDurumuGuncelle;
GO
CREATE PROCEDURE ValfDurumuGuncelle
    @ValfID INT,
    @Durum NVARCHAR(50)
AS
BEGIN
    IF @Durum NOT IN ('Aktif', 'Kapalı', 'Bakımda')
        THROW 50000, 'Geçersiz durum değeri. Sadece "Aktif", "Kapalı" veya "Bakımda" kullanılabilir.', 1;

    UPDATE GazValfi
    SET Durum = @Durum
    WHERE ValfID = @ValfID;
END;
GO

-- Trigger: Sızıntı onarımı tespiti
IF OBJECT_ID('dbo.Trigger_SizintiOnarildi', 'TR') IS NOT NULL
    DROP TRIGGER dbo.Trigger_SizintiOnarildi;
GO
CREATE TRIGGER Trigger_SizintiOnarildi
ON GazValfi
AFTER UPDATE
AS
BEGIN
    -- Sızıntı durumu 1'den 0'a düştüğünde kontrol yapar
    IF EXISTS (
        SELECT 1
        FROM GazValfi
        WHERE SizintiDurumu = 0 AND ValfID IN (
            SELECT ValfID FROM GazValfi WHERE SizintiDurumu = 1
        )
    )
    BEGIN
        -- Olay kaydına "Sızıntı Onarıldı" kaydı ekler
        INSERT INTO OlayKaydi (ValfID, OlayTipi, Aciklama)
        SELECT ValfID, 'Sızıntı Onarıldı', 'Valf üzerindeki gaz sızıntısı onarıldı.'
        FROM GazValfi
        WHERE SizintiDurumu = 0;
    END
END;
GO

-- Saklı Yordam: Belirli bir valf için olay kayıtlarını listeleme
IF OBJECT_ID('dbo.GetOlayKayitlariByValfID', 'P') IS NOT NULL
    DROP PROCEDURE dbo.GetOlayKayitlariByValfID;
GO
CREATE PROCEDURE GetOlayKayitlariByValfID
    @ValfID INT
AS
BEGIN
    -- Belirtilen ValfID'ye ait tüm olay kayıtlarını seçer
    SELECT OlayID, OlayTarihi, OlayTipi, Aciklama
    FROM OlayKaydi
    WHERE ValfID = @ValfID;
END;
GO

-- Saklı Yordam: Belirli bir müşteriye fatura ekleme
IF OBJECT_ID('dbo.AddFatura', 'P') IS NOT NULL
    DROP PROCEDURE dbo.AddFatura;
GO
CREATE PROCEDURE AddFatura
    @MusteriID INT,
    @FaturaTutari FLOAT,
    @FaturaTarihi DATE,
    @Aciklama NVARCHAR(255)
AS
BEGIN
    -- Yeni bir fatura ekler ve müşteri bilgisiyle ilişkilendirir
    INSERT INTO Fatura (MusteriID, FaturaTutari, FaturaTarihi, Aciklama)
    VALUES (@MusteriID, @FaturaTutari, @FaturaTarihi, @Aciklama);
END;

/*
Örnek Kullanımlar:

1. Trigger: Trigger_SizintiTespit
   - Valf güncellendiğinde çalışır. Eğer "SizintiDurumu" alanı 1 olarak güncellenmişse, olay kaydı oluşturur.
   Örnek:
   UPDATE GazValfi
   SET SizintiDurumu = 1
   WHERE ValfID = 1;

2. Fonksiyon: ToplamKullanimiHesapla
   - Belirtilen hattaki toplam müşteri gaz tüketimini hesaplar.
   Örnek:
   SELECT dbo.ToplamKullanimiHesapla(1) AS Hat1ToplamGazKullanimi;

3. Fonksiyon: CalisanBakimSayisi
   - Belirtilen çalışanın gerçekleştirdiği toplam bakım sayısını döndürür.
   Örnek:
   SELECT dbo.CalisanBakimSayisi(1) AS BakimSayisi;

4. Trigger: Trigger_YeniMusteriEkle
   - Yeni bir müşteri eklendiğinde çalışır. Eklenen müşteriye ait bilgilerle birlikte olay kaydı oluşturur.
   Örnek:
   INSERT INTO Musteri (AdSoyad, Adres, KullanilanGaz, HatID)
   VALUES ('Cem Yıldırım', 'Bursa, Türkiye', 120.5, 1);

5. Saklı Yordam / Stored Procedure: MusteriGazArttir
   - Belirtilen MusteriID ve miktar ile hem müşterinin kullandığı gazı hem de ilgili hattın toplam gaz akışını arttırır.
   Örnek:
   DECLARE @Sonuc NVARCHAR(100);
   EXEC MusteriGazArttir @MusteriID = 1, @Miktar = 0.75, @Sonuc = @Sonuc OUTPUT;
   PRINT @Sonuc;

6. Saklı Yordam / Stored Procedure: ValfDurumuGuncelle
   - Belirtilen ValfID ve durum değeriyle gaz valfinin durumu güncellenir. Sadece "Açık", "Kapalı" veya "Bakımda" değerleri kabul edilir.
   Örnek:
   EXEC dbo.ValfDurumuGuncelle @ValfID = 2, @Durum = 'Aktif';

7. Tetikleyici / Trigger: Trigger_SizintiOnarildi
   - Valf güncellendiğinde, "SizintiDurumu" alanı 1'den 0'a düşerse olay kaydına "Sızıntı Onarıldı" kaydı ekler.
   Örnek:
   UPDATE GazValfi
   SET SizintiDurumu = 0
   WHERE ValfID = 1;

8. Saklı Yordam / Stored Procedure: GetOlayKayitlariByValfID
   - Belirtilen ValfID'ye ait olay kayıtlarını listeler.
   Örnek:
   EXEC GetOlayKayitlariByValfID @ValfID = 1;

9. Saklı Yordam / Stored Procedure: AddFatura
   - Belirtilen MusteriID, tutar ve tarih ile yeni bir fatura ekler.
   Örnek:
   EXEC AddFatura @MusteriID = 1, @FaturaTutari = 300.75, @FaturaTarihi = '2024-03-15', @Aciklama = 'Valf Ücreti';
*/