ASP.NET MVC (Fatura Kayıtları) - Veritabanı

Bu içerik oluşturacağımız Fatura Kayıtları uygulamasının girişi niteliğindedir. Uygulama web tabanlı ASP.NET MVC ile geliştirilecektir. İçeriğinden kısaca bahsedersek, fatura genel bilgileri ve satırlarını MS-SQL veritabanına kaydeden, sorgulayan bir uygulama olacaktır.

Aynı zamanda kullanıcı girişli, girilen kullanıcı ile güvenlik anahtarı oluşturulan ve bu anahtarla işlem yapan bir güvenlik yapısına sahip olacaktır.

Veritabanı Yapısı

MS-SQL 2014 sürümü kullanılacaktır. Veritabanı tablolarına ait bir şemayı baştan oluşturmakta fayda var.

Öncelikle müşterilerin tutulduğu bir tablo, bu tablo ile ilişkili müşteri tiplerinin tutulduğu bir tablo olmalıdır. Faturaların genel bilgileri (adı, numarası, müşterisi, ödeme tarihi, ödeme türü) içerisinde tutulan bir faturalar tablosu olmalıdır. Faturaların içerisindeki kalem bilgilerini satır satır tutan ve faturalar tablosu ile ilişkili başka bir tablo da olmalıdır.

Bütün bunların dışında kullanıcıların ve kullanıcılara ait güvenlik anahtarlarının tutulduğu tablolar da oluşturulmalı.

Oluşturulan bütün tablolar ve kodlamada kullanılan bütün değişkenler İngilizce olarak tanımlanacaktır. Oluşturacağımız tabloları aşağıdaki gibi listeleyebiliriz.

Primary Key yani birincil anahtar alanları PK, Foreign Key alanları ise FK ile belirtilmiştir.

InvoicesDB isimli bir veritabanı oluşturarak bütün tabloları elle oluşturabilirsiniz veya buradan veritabanı script dosyasını indirebilirsiniz. Veritabanı diagramı aşağıdaki gibi olacaktır.

Veritabanı içerisinde colTitle veya colName gibi alanlar nvarchar olarak tanımlanmalıdır. colEmail gibi alanların ise varchar olarak tanımlanması yeterlidir. Eğer içerisinde sadece İngilizce karakterler barındırabilen veya rakam barındırması gereken metin alanlarının varchar olarak tanımlanması daha uygundur. nvarchar türü alanlar her dildeki karakterleri alabilmekte fakat her karakter 2 byte yer kaplamaktadır. varchar türü alanlarda ise İngilizce dışındaki karakterlerin alınabilmesi için alanın collation özelliğinin ilgili dilde belirtilmesi gerekir ve her karakter 1 byte yer kaplar. Veritabanındaki bütün ID birincil anahtarları int türündedir. Kullanıcı şifreleri ise kripte edileceği için varbinary türünde tanımlanmıştır.

Veritabanı Standart Değerleri

Dikkat ettiyseniz fatura türü, müşteri türü ve durumu gibi birçok tablo oluşturuldu ve bu tablolar ana tablolarla ilişkilendirilmiş halde. Bu küçük tabloların içerisindeki sabit değerleri büyük tablolardaki ilişkili alanlarda kullanacağız. Sebebi ise hem veri doğruluğunun daha stabil olması hem de daha az alan kullanmaktır. Örneğin, fatura durumları için ödendi, bekleniyor, iptal edildi seçeneklerini metin olarak her faturanın ilgili alanına elle girdiğinizi düşünürseniz, hem hatalı metin girme riski daha fazla olacaktır, hem de 4 byte büyüklüğünde int yerine en az 16 byte uzunluğundaki nvarchar değerleri daha fazla yer kaplayacaktır. Hatalı metin girilmesi durumunda sorgular doğru veri getirmeyecektir.

Aşağıdaki insert komutları ile veritabanı tablolarının ilk değerleri oluşturulacaktır.

insert into tblCustomerStatuses (colCustomerStatus)
values (N'Aktif')
insert into tblCustomerStatuses (colCustomerStatus)
values (N'Görüşülüyor')
insert into tblCustomerStatuses (colCustomerStatus)
values (N'Pasif')

insert into tblCustomerTypes (colCustomerType)
values (N'Şahıs')
insert into tblCustomerTypes (colCustomerType)
values (N'Şirket')

insert into tblUserRoles (colRoleName)
values (N'Engelli') --Oturum açamayan kullanıcı
insert into tblUserRoles (colRoleName)
values (N'Yönetici') --Kullanıcı oluşturabilen tam yetkili
insert into tblUserRoles (colRoleName)
values (N'Alt Yönetici') --Okuma ve yazma yetkili kullanıcı
insert into tblUserRoles (colRoleName)
values (N'Kullanıcı') --Sadece okuma yetkisi olan kullanıcı

insert into tblPaymentTypes (colPaymentType)
values (N'Havale')
insert into tblPaymentTypes (colPaymentType)
values (N'EFT')
insert into tblPaymentTypes (colPaymentType)
values (N'Nakit')
insert into tblPaymentTypes (colPaymentType)
values (N'Kredi Kartı')
insert into tblPaymentTypes (colPaymentType)
values (N'Sanal Kart')

insert into tblInvoiceTypes (colInvoiceType)
values (N'Satış')
insert into tblInvoiceTypes (colInvoiceType)
values (N'İrsaliye')

insert into tblInvoiceStatuses (colInvoiceStatus)
values (N'Ödendi')
insert into tblInvoiceStatuses (colInvoiceStatus)
values (N'Bekleniyor')
insert into tblInvoiceStatuses (colInvoiceStatus)
values (N'Askıda')
insert into tblInvoiceStatuses (colInvoiceStatus)
values (N'Gecikti')
insert into tblInvoiceStatuses (colInvoiceStatus)
values (N'İptal Edildi')

Veritabanı Kullanıcı Prosedürleri

Veritabanı içerisinde birçok prosedür ve fonksiyon tanımlayabilirsiniz. Kullanıcı oluşturma, oturum doğrulama, güvenlik anahtarı oluşturma gibi işlemler için prosedür kullanılabilir. Bu prosedürler C# .NET tarafında SqlCommand nesneleri ile çalıştırılabilir. Ya da Entity Framework teknolojisi ile bütün veritabanı C# tarafında veri modellerine dönüştürülüp bütün sorgular Linq ile çalıştırılabilir.

Kullanıcı oluşturmak için aşağıdaki gibi bir prosedür yazılabilir.

-- Kullanıcı oluşturma prosedürü
create proc dbo.procCreateUser @_userName varchar(32), @_password varchar(32), 
	@_fullName nvarchar(64), @_email varchar(64), @_roleID int
as
begin
	if ((select count(*) from tblUsers where colUserName = @_userName) > 0)
		select N'Kullanıcı adı zaten mevcut' as colResult, 0 as colSuccess
	else if ((select count(*) from tblUsers where colEmail = @_email) > 0)
		select N'Email adresi zaten mevcut' as colResult, 0 as colSuccess
	else if ((select count(*) from tblUserRoles where colRoleID = @_roleID) = 0)
		select N'Kullanıcı rolü bulunamadı' as colResult, 0 as colSuccess
	else if (@_userName is null or len(@_userName) < 4)
		select N'Geçersiz bir kullanıcı adı girdiniz' as colResult, 0 as colSuccess
	else if (len(@_password) < 8)
		select N'Şifre en az 8 karakter olmalı' as colResult, 0 as colSuccess
	else if (@_fullName is null or len(@_fullName) = 0)
		select N'Kullanıcının tam adını girmelisiniz' as colResult, 0 as colSuccess
	else
	begin
		insert into tblUsers (colUserName, colPassword, colFullName, colEmail, 
			colRoleID, colRegister, colLastLogin)
		values (@_userName, encryptbypassphrase(@_password, 'sifreleme-anahtari-123321'),
			@_fullName, @_email, @_roleID, getdate(), getdate())
		select N'Kullanıcı oluşturuldu' as colResult, 1 as colSuccess
	end
end

Prosedürde veritabanına kullanıcı adı, şifre, tam isim, e-posta ve kullanıcının hangi rolde oluşturulacağının id bilgisi parametre olarak tanımlıdır. Gönderilen parametreler bazı koşullardan geçirilerek prosedür çalışmaktadır. Aynı kullanıcı adı veya aynı e-posta adresi zaten mevcut ise, gönderilen rol id numarası tblUserRoles tablosunda yoksa, kullanıcı adı null veya 4 karakterden küçükse, şifre 8 karakterden küçükse, tam isim null veya boşsa her biri için colResult alanlı ayrı birer mesaj ve 0 değerinde colSuccess alanı döndürülecek.

Prosedür bu engellerin hiçbirine takılmazsa insert ile kullanıcıların tutulduğu tabloya yeni kayıt girilecek ve colSuccess 1 değerinde dönecektir.

-- Kullanıcı düzenleme prosedürü
create proc dbo.procUpdateUser @_userID int, @_fullName nvarchar(64), 
	@_email varchar(64), @_roleID int
as
begin
	if (@_fullName is null or len(@_fullName) = 0)
		select N'Kullanıcının tam adını girmelisiniz' as colResult, 0 as colSuccess
	else if ((select count(*) from tblUserRoles where colRoleID = @_roleID) = 0)
		select N'Kullanıcı rolü bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblUsers where colEmail = @_email and colUserID <> @_userID) > 0)
		select N'Email adresi zaten mevcut' as colResult, 0 as colSuccess
	else
	begin
		update tblUsers set colFullName = @_fullName, colEmail = @_email
		where colUserID = @_userID
		select N'Kullanıcı düzenlendi' as colResult, 1 as colSuccess
	end
end

Güncelleme işleminde sadece tam isim, e-posta ve kullanıcı rolü değiştirilebilecek şekilde bir prosedür yazılabilir. Şifrenin ayrıca değiştirilmesi ve kullanıcı adının değiştirilememesi daha güvenli olacaktır.

-- Kullanıcı silme prosedürü
create proc dbo.procDeleteUser @_userID int
as
begin
	if ((select colRoleID from tblUsers where colUserID = @_userID) <> 2)
	begin
		delete from tblUsers where colUserID = @_userID
		select N'Kullanıcı silindi' as colResult, 1 as colSuccess
	end
	else
		select N'Bu yetkiye sahip kullanıcı silinemez' as colResult, 0 as colSuccess
end

Prosedürde yönetici rolüne sahip kullanıcılar silinemez olarak belirlenmiştir.

Kullanıcı oturum açarken kullanıcı adı/email ve şifre bilgisi ile talep gönderecektir. Karşılığında oturum açma başarılı olursa veritabanında oluşturulan bir güvenlik anahtarı ile bütün işlemler yapılabilecektir. Oturum açma prosedürü aşağıdaki gibi tanımlanmıştır.

-- Kullanıcı girişi (email veya kullanıcı adı ile)
create proc dbo.procUserLogin @_user varchar(64), @_password varchar(32)
as
begin
	if ((select count(*) from tblUsers where colUserName = @_user or colEmail = @_user) = 0)
	begin
		select N'Kullanıcı bulunamadı' as colResult, 0 as colLogged, '' as colKey
	end
	else if ((select decryptbypassphrase(colPassword, 'sifreleme-anahtari-123321') from tblUsers) = @_password)
	begin
		declare @_allChars varchar(36) = 'abcdefghijklmnopqrstuvwxyz0123456789'
		declare @_len int, @_i int, @_key varchar(64), @_userID int
		set @_i = 0;
		set @_len = 60;
		set @_key = '';
		while @_i < @_len
		begin
			set @_key = @_key + substring(@_allChars, convert(int, rand()*36), 1)
			set @_i = @_i + 1
		end
		select colUserID = @_userID from tblUsers 
		where colUserName = @_user or colEmail = @_user
		insert into tblSecurityKeys (colKey, colUserID, colExpire)
		values (@_key, @_userID, dateadd(day, 30, getdate()))
		select N'Giriş başarılı' as colResult, 1 as colLogged, @_key as colKey
	end
	else
	begin
		select N'Yanlış şifre girdiniz' as colResult, 0 as colLogged, '' as colKey
	end
end

Oturum kapatma prosedüründe ise mevcut güvenlik anahtarı veritabanından silinecektir.

-- Kullanıcı oturumu kapatınca
create proc dbo.procUserLogout @_key varchar(64)
as
begin
	delete from tblSecurityKeys where colKey = @_key
end

Oturum açtıktan sonra yapılacak her işlemde öncelikle güvenlik anahtarı veritabanına gönderilip doğrulanacaktır. Doğrulama sonucu kullanıcı rolü id bilgisi okunacaktır. Bu id yapılacak işlem için uygunsa işlem gerçekleştirilecektir.

-- Güvenlik anahtarı doğrulama
create proc dbo.procCheckKey @_key varchar(64)
as
begin
	declare @_authority int
	select @_authority = u.colRoleID from tblSecurityKeys sk
		left join tblUsers u on u.colUserID = sk.colUserID
		left join tblUserRoles ur on ur.colRoleID = u.colUserID
	where sk.colKey = @_key
	if (@_authority is null)
		select -1 as colAuthority
	else
		select @_authority as colAuthority
end

Veritabanı Müşteri Prosedürleri

Müşteri oluşturmak için müşteri ismi, türü, durumu, e-posta, telefon, vergi numarası ve adres bilgileri isteyen bir prosedür aşağıdaki şekilde oluşturulacaktır.

create proc dbo.procCreateCustomer @_name nvarchar(128), @_customerTypeID int, 
	@_customerStatusID int, @_email varchar(64), @_phone varchar(32),
	@_taxNumber varchar(32), @_address nvarchar(256)
as
begin
	if (@_name is null or len(@_name) = 0)
		select N'Müşteri ismi gerekiyor' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomerTypes 
		where colCustomerTypeID = @_customerTypeID) = 0)
		select N'Müşteri türü bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomerStatuses 
		where colCustomerStatusID = @_customerStatusID) = 0)
		select N'Müşteri durumu bulunamadı' as colResult, 0 as colSuccess
	else
	begin
		insert into tblCustomers (colName, colCustomerTypeID, colCustomerStatusID,
			colEmail, colPhone, colTaxNumber, colAddress, colRegister)
		values (@_name, @_customerTypeID, @_customerStatusID, @_email, @_phone,
			@_taxNumber, @_address, getdate())
		select N'Müşteri oluşturuldu' as colResult, 1 as colSuccess
	end
end

Müşteri isminin boş olması, müşteri durum veya türünün ilgili tablolarda bulunamaması durumunda işlem başarısız olacaktır.

Mevcut müşteriyi güncellemek için aşağıdaki prosedür kullanılacaktır. Müşteri oluşturma prosedüründeki bütün denetimler aynen burada da mevcuttur.

create proc dbo.procUpdateCustomer @_name nvarchar(128), @_customerTypeID int, 
	@_customerStatusID int, @_email varchar(64), @_phone varchar(32),
	@_taxNumber varchar(32), @_address nvarchar(256), @_customerID int
as
begin
	if (@_name is null or len(@_name) = 0)
		select N'Müşteri ismi gerekiyor' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomerTypes 
		where colCustomerTypeID = @_customerTypeID) = 0)
		select N'Müşteri türü bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomerStatuses 
		where colCustomerStatusID = @_customerStatusID) = 0)
		select N'Müşteri durumu bulunamadı' as colResult, 0 as colSuccess
	else
	begin
		update tblCustomers set colName = @_name, colCustomerTypeID = @_customerTypeID,
			colCustomerStatusID = @_customerStatusID, colEmail = @_email, colPhone = @_phone,
			colTaxNumber = @_taxNumber, colAddress = @_address
		where colCustomerID = @_customerID
		select N'Müşteri güncellendi' as colResult, 1 as colSuccess
	end
end

Müşteri silme işlemi için aşağıdaki prosedürü uygulayabilirsiniz. Bu prosedürdeki denetimler müşterinin mevcut olup olmaması ve müşterinin en az bir fatura ile ilişkili olup olmamasıdır. Yukarıdaki veritabanı diagramına geri dönerseniz tblCustomers tablosunun colCustomerID adlı birincil anahtar alanı, tblInvoices tablosunun colCustomerID alanı ile bağlantılı olduğunu göreceksiniz. Yani bu alanlardan müşteri tablosunda olmayan bir colCustomerID değeri faturalar tablosunda olamaz. Dolayısı ile faturalar tablosunda olan colCustomerID değeri varsa o değeri silmeden veya değiştirmeden müşteriler tablosundan silinemez.

create proc dbo.procDeleteCustomer @_customerID int
as
begin
	if ((select count(*) from tblCustomers where colCustomerID = @_customerID) = 0)
		select N'Müşteri bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblInvoices where colCustomerID = @_customerID) > 0)
		select N'Müşteri ile ilişkili fatura kayıtları bulunmaktadır' as colResult, 0 as colSuccess
	else
	begin
		delete from tblCustomers where colCustomerID = @_customerID
		select N'Müşteri silindi' as colResult, 1 as colSuccess
	end
end

Veritabanı Fatura Prosedürleri

Fatura oluşturmak, güncellemek ve silmek için aşağıdaki prosedürler kullanılacaktır.

create proc dbo.procCreateInvoice @_invoiceNumber varchar(16), @_title nvarchar(64),
	@_customerID int, @_address nvarchar(256), @_invoiceStatusID int, @_invoiceTypeID int,
	@_paymentTypeID int, @_userID int, @_paymentDate date
as
begin
	if ((select count(*) from tblInvoices where colInvoiceNumber = @_invoiceNumber) > 0)
		select N'Fatura numarası zaten mevcut' as colResult, 0 as colSuccess
	else if (@_title is null or len(@_title) = 0)
		select N'Faturaya bir başlık girmelisiniz' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomers where colCustomerID = @_customerID) = 0)
		select N'Müşteri kaydı bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblInvoiceStatuses where colInvoiceStatusID = @_invoiceStatusID) = 0)
		select N'Fatura durumu bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblInvoiceTypes where colInvoiceTypeID = @_invoiceTypeID) = 0)
		select N'Fatura türü bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblPaymentTypes where colPaymentTypeID = @_paymentTypeID) = 0)
		select N'Ödeme türü bulunamadı' as colResult, 0 as colSuccess
	else
	begin
		insert into tblInvoices (colInvoiceNumber, colTitle, colCustomerID, colAddress,
			colInvoiceStatusID, colInvoiceTypeID, colPaymentTypeID, colUserID, 
			colPaymentDate, colCreateDateTime)
		values (@_invoiceNumber, @_title, @_customerID, @_address, @_invoiceStatusID,
			@_invoiceTypeID, @_paymentTypeID, @_userID, @_paymentDate, getdate())
		select N'Fatura oluşturuldu' as colResult, 1 as colSuccess
	end
end

create proc dbo.procUpdateInvoice @_invoiceNumber varchar(16), @_title nvarchar(64),
	@_customerID int, @_address nvarchar(256), @_invoiceStatusID int, @_invoiceTypeID int,
	@_paymentTypeID int, @_paymentDate date, @_invoiceID int
as
begin
	if ((select count(*) from tblInvoices 
		where colInvoiceNumber = @_invoiceNumber and colInvoiceID <> @_invoiceID) > 0)
		select N'Fatura numarası zaten mevcut' as colResult, 0 as colSuccess
	else if (@_title is null or len(@_title) = 0)
		select N'Faturaya bir başlık girmelisiniz' as colResult, 0 as colSuccess
	else if ((select count(*) from tblCustomers where colCustomerID = @_customerID) = 0)
		select N'Müşteri kaydı bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblInvoiceStatuses where colInvoiceStatusID = @_invoiceStatusID) = 0)
		select N'Fatura durumu bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblInvoiceTypes where colInvoiceTypeID = @_invoiceTypeID) = 0)
		select N'Fatura türü bulunamadı' as colResult, 0 as colSuccess
	else if ((select count(*) from tblPaymentTypes where colPaymentTypeID = @_paymentTypeID) = 0)
		select N'Ödeme türü bulunamadı' as colResult, 0 as colSuccess
	else
	begin
		update tblInvoices set colInvoiceNumber = @_invoiceNumber, colTitle = @_title,
			colCustomerID = @_customerID, colAddress = @_address, colInvoiceStatusID = @_invoiceStatusID,
			colInvoiceTypeID = @_invoiceTypeID, colPaymentTypeID = @_paymentTypeID, colPaymentDate = @_paymentDate
		where colInvoiceID = @_invoiceID
		select N'Fatura oluşturuldu' as colResult, 1 as colSuccess
	end
end

create proc dbo.procDeleteInvoice @_invoiceID int
as
begin
	-- öncelikle ilişkili satırlar silinecek
	delete from tblInvoiceLines where colInvoiceID = @_invoiceID
	-- ardından faturanın kendi kaydı silinecek
	delete from tblInvoices where colInvoiceID = @_invoiceID
	select N'Fatura silindi' as colResult, 1 as colSuccess
end

Faturaların satırları ile ilgili oluşturma, güncelleme ve silme işlemleri için ise aşağıdaki prosedürler kullanılır.

create proc dbo.procCreateInvoiceLine @_invoiceID int, @_description nvarchar(128), @_unit nvarchar(16),
	@_price decimal(17, 4), @_vatRatio decimal(17, 4)
as
begin
	if ((select count (*) from tblInvoices where colInvoiceID = @_invoiceID) = 0)
		select N'Fatura bulunamadı' as colResult, 0 as colSuccess
	else
	begin
		insert into tblInvoiceLines (colDescription, colUnit, colPrice, colVatRatio, colInvoiceID)
		values (@_description, @_unit, @_price, @_vatRatio, @_invoiceID)
		select N'Fatura satırı oluşturuldu' as colResult, 0 as colSuccess
	end
end

create proc dbo.procUpdateInvoiceLine @_invoiceLineID int, @_description nvarchar(128), @_unit nvarchar(16),
	@_price decimal(17, 4), @_vatRatio decimal(17, 4)
as
begin
	update tblInvoiceLines set colDescription = @_description, colUnit = @_unit,
		colPrice = @_price, colVatRatio = @_vatRatio
	where colInvoiceLineID = @_invoiceLineID
	select N'Fatura satırı güncellendi' as colResult, 0 as colSuccess
end

create proc dbo.procDeleteInvoiceLine @_invoiceLineID int
as
begin
	delete from tblInvoiceLines where colInvoiceLineID = @_invoiceLineID
	select N'Fatura satırı silindi' as colResult, 0 as colSuccess
end