Notes

use vtPlakaTanima
go
--Create procedure sp_husnu (@degisken varchar(50), @uyeno int) as begin
--insert into tblArac values(@degisken, @uyeno); end

--exec sp_husnu '34 Vt 5161',1

/**************************/



/* Üye CRUD */
create procedure sp_UyeEkle(@adi varchar(50),@soyadi varchar(50),@tc char(11),@tel char(11),@abone bit,@seviye tinyint )
as begin
insert into tblUye values(@adi,@soyadi,@tc,@tel,@abone,@seviye);

end
go
exec sp_UyeEkle 'Husnu21','Nazlım','12345678901','12345678901','0','2'
go



/****************/
create procedure sp_UyeGuncelle1 (@no int, @adi varchar(50),@soyadi varchar(50),@tc char(11),@tel char(11),@abone bit,@seviye tinyint )
as begin
update tblUye set uyeAdi=@adi,uyeSoyadi=@soyadi,uyeTc=@tc,uyeTel=@tel,abone=@abone,seviye=@seviye where uyeNo=@no

end
go
exec sp_UyeGuncelle1 3,'sercan','s','12354698701','12354698701',0,1
go

create procedure sp_UyeSil(@no int) as begin

delete from tblUye where uyeNo=@no

end
exec sp_UyeSil 5
go
/************************/
create procedure sp_UyeListele as begin

select * from tblUye
end
go
exec sp_UyeListele
/***************/
create procedure sp_UyeParametreliListe(@no int) as begin

select * from tblUye where uyeNo=@no

end
go
exec sp_UyeParametreliListe 1
/*************************/

/* ARAC CRUD */

create procedure sp_AracEkle(@aracplaka varchar(50),@uyeno int) as begin

insert into tblArac values (@aracplaka,@uyeno)

end
go
exec sp_AracEkle '34 sc 6231', '1'
go

create procedure sp_AracGuncelle(@uyeno int,@aracplaka varchar(50)) as begin

update tblArac set uyeNo=@uyeno where aracPlaka=@aracplaka

end
go
exec sp_AracGuncelle 2,'34 sc 6231'
go

create procedure sp_AracSil(@aracPlk varchar(50)) as begin

delete from tblArac where aracPlaka=@aracPlk

end
go
exec sp_AracSil '34 sc 6231'

create procedure sp_AracListe as begin

select * from tblArac

end
go
exec sp_AracListe

go

create procedure sp_AracListe1(@uye int ) as begin

select * from tblArac where uyeNo=@uye

end
go
exec sp_AracListe1 1

/***********************/

/* Abone */

create procedure sp_AboneEkle(@uyeno int, @tarihh datetime, @ucret money) as begin

insert into tblAbone values(@uyeno,@tarihh,@ucret)

end
go
exec sp_AboneEkle 1,'2019-12-13',5

create procedure sp_AboneGuncelle(@aboneNo int,@uyeno int,@tarih datetime,@ucret money) as begin

update tblAbone set uyeNo=@uyeno,tarih=@tarih,ucret=@ucret where aboneNo=@aboneNo

end
go
exec sp_AboneGuncelle 1,2,'2012-12-15',50
go
create procedure sp_AboneListele as begin

select * from tblAbone

end
go
exec sp_AboneListele
go
create procedure sp_AboneListelepara(@aboneno int) as begin

select * from tblAbone where aboneNo=@aboneno
end
go
exec sp_AboneListelepara 1
go


create procedure sp_AboneSil(@abone int) as begin

delete from tblAbone where aboneNo=@abone

end
go
exec sp_AboneSil 1







/*İSLEM*/
use vtPlakaTanima
go
/*islem ekle*/
create procedure sp_islemEkle(@islemTarihSaat datetime ,@islemGunSayi tinyint,@arPlaka varchar(50),@resim image)
as begin
insert into tblIslem values(@islemTarihSaat,@islemGunSayi,@arPlaka,@resim);
end
go
exec sp_islemEkle '03.20.2019',1,'34 Vt 5161',null
go
/*Güncelle*/
create procedure sp_islemGuncelle (@islemNo int,@islemTarihSaat datetime ,@islemGunSayi tinyint,@arPlaka varchar(50),@resim image)
as begin
update tblIslem set islemTarihSaat=@islemTarihSaat,islemGunSayi=@islemGunSayi,arPlaka=@arPlaka,resim=@resim where islemNo=@islemNo

end
go
exec sp_islemGuncelle 1, '2020-11-10 12:00:00.000', 2 ,'34 Vt 5160', null
go
/*Silme*/
CREATE PROCEDURE sp_islemSilme (@islemNo int)

AS
begin
DELETE FROM tblIslem where islemNo=@islemNo
end
GO
exec sp_islemSilme 1
go
/*id listeleme*/
CREATE PROCEDURE sp_islemListele(@islemNo int)
AS
begin
SELECT * FROM tblIslem WHERE islemNo=@islemNo
end
go
exec sp_islemListele 2
go
/*çoklu listeleme*/
CREATE PROCEDURE sp_islemCokluListele
AS
begin
SELECT * FROM tblIslem
end
go
exec sp_islemCokluListele
go
/****************/

/*2.Soru park için gelenaraç tblarac tablosunda kayıtlı değilse misafir olarak kaydet*/
use vtPlakaTanima
go
create procedure spAracKontrol(@plaka int)
as
begin
declare @var int
set @var=(select COUNT(aracPlaka) from tblArac where aracPlaka=@plaka)
if @var=0 begin
insert into tblArac values(@plaka,@plaka,1)
end
end

/**/
/*3.Şu anda içeride olan araçların listesini veren procedure*/
create procedure sp_SuanAraclarListesi as begin

select arplaka,count(islemNo),count(islemNo)%2 as 'icerde' from tblIslem where day(islemtarihsaat)=day(getdate())
group by arPlaka

end
go
exec sp_SuanAraclarListesi
/****************/


/*4- Araç plaka numarası verildiginde müşteri bilgileri… */
create procedure sp_AracMusteriListele(@plaka varchar(50)) as begin

select * from tblUye where uyeNo=(select uyeno from tblArac where aracPlaka=@plaka)
end
go
exec sp_AracMusteriListele '34 sc 6231'
/**/


/*5-İsme göre müşteri bilgilerinin getirilmesi*/

create procedure sp_ismegoreliste(@isme varchar(50))
as begin
select * from tblUye where uyeAdi=@isme

end
go
exec sp_ismegoreliste 'Sercan'
/**/
/*6- müşteri numarasının o ay içerisinde ödemesi varmı? */

create procedure sp_ayodemesi(@musteri int) as begin

select MONTH(GETDATE()) from tblAbone where uyeNo=@musteri

end
go
exec sp_ayodemesi 1

/**/

/*7- Ay sonunda bir sonraki ay için ödemesi olmayan üyelerin aboneliginin false yapılması */





/**/


/*View */

create view vw_AktifÜye as select * from tblUye where abone=1
go
select * from vw_AktifÜye
go

create view vw_Toplampara1 as select sum(ucret) as 'TOPLAM' from tblAbone where MONTH(tarih)=MONTH(GETDATE())
go
select * from vw_Toplampara1


/*****/

/*TRİGGER*/

/*Ödeme olduktan sonra uye tablosundaki abone true yapılacak*/
create trigger tr_aboneyapma on tblAbone after insert as begin

update tblUye set abone=1

end

/*Aboneligi aktif üye sayısı 100 e ulaştıysa yeni abone kaydı yapma*/

alter trigger tr_100akfif on tblAbone after insert as begin
declare @sayısı int
set @sayısı= (select count(*) from tblUye where abone=1)

if @sayısı=7
begin
RAISERROR('100Den Fazla Uye Kaydedildi',10,1)
delete from tblAbone where aboneNo=(SELECT max(aboneNo)
FROM tblAbone)

end
else print'KAYDEDİLDİ'


end