Örnek DI uygulaması 1-Veritabanı oluşturulması

Tüm bu örnekleri çalıştırabilmek için öncelikle örnek veritabanını oluşturmamız gerekiyor.

Bu nedenle gerekli 5 adet tabloyu çok kısa şekilde SQL yaratma cümleciklerini paylaşacağım. Bunları MS Sql Server Management Studio'da çalıştırırsanız veritabanınız oluşturulacaktır.

create db_sample;
use db_sample;

create table Customer(
  CustomerId integer not null identity(1,1) primary key,
  CustomerName nvarchar(100) null,
  AddressLine1 nvarchar(50) null,
  AddressLine2 nvarchar(50) null,
  AddressLine3 nvarchar(50) null,
  City nvarchar(50) null,
  Country nvarchar(50) null,
  PostCode nvarchar(20) null,
  ContactPerson nvarchar(50) null,
  Phone varchar(15) null,
  Fax varchar(15) null,
  ContactEMail nvarchar(100) null,
  CurrencyTypeId integer,
  TotalDebit float default 0,
  TotalCredit float default 0,
  Balance float default 0,
  PaymentDueDate integer default 0,
  InBlackList bit not null default 0,
  IsDeleted bit not null default 0,
  LastUpdatedDT datetime default getutcdate(),
  LastModifyingUser integer
);


insert into [dbSample].dbo.Customer 
(CustomerName, AddressLine1, AddressLine2, AddressLine3, City, Country, ContactPerson, Phone, Fax, ContactEMail) 
values
('Kart Dekorasyon Malzemeleri ve Müt.Hiz. A.Ş.', 'Bedesten Sok. Katmerli Han No:4/13 Kat:3', 'Karaköy', '', 'İstanbul', 'Türkiye', 'Ahmet Alagöz', '0532717717', '02127127272', 'a-alagoz@gmail.com'),
('Damak Şekercilik ve Pastacılık Ltd.', 'Limon Sok. Hacıoğlu Ap. No:12/1', 'Göztepe', '', 'İstanbul', 'Türkiye', 'Murat Yapan', '05327177818', '02127127273', 'muratyapan@damak.com.tr');

create table Supplier(
  SupplierId integer not null identity(1,1) primary key,
  SupplierName nvarchar(100) null,
  AddressLine1 nvarchar(50) null,
  AddressLine2 nvarchar(50) null,
  AddressLine3 nvarchar(50) null,
  City nvarchar(50) null,
  Country nvarchar(50) null,
  PostCode nvarchar(20) null,
  ContactPerson nvarchar(50) null,
  Phone varchar(15) null,
  Fax varchar(15) null,
  ContactEMail nvarchar(100) null,
  CurrencyTypeId integer,
  TotalDebit float default 0,
  TotalCredit float default 0,
  Balance float default 0,
  PaymentDueDate integer default 0,
  InBlackList bit not null default 0,
  IsDeleted bit not null default 0,
  LastUpdatedDT datetime default getutcdate(),
  LastModifyingUser integer
);

insert into Supplier 
(SupplierName, AddressLine1, AddressLine2, AddressLine3, City, Country, ContactPerson, Phone, Fax, ContactEMail) 
values
('Kartel Yemek ve İkram Üretim ve Satış A.Ş.', '', 'Ümraniye', '', 'İstanbul', 'Türkiye', 'Mehmet Yurdan', '0532717719', '02127127274', 'mehmetyurdan@gmail.com'),
('Alaylı Otomotiv San. ve Tic. A.Ş.', 'Yalı Cad. Nur Han No:15/44 Kat 4', 'Karaköy', '', 'İstanbul', 'Türkiye', 'Yelda Saran', '05327177819', '02127127275', 'yeldasaran@alayliotomotiv.com.tr');

create table InventoryItem(
  InventoryItemId integer not null identity(1,1) primary key,
  InventoryGroupId integer not null,
  MainSupplierId integer null,
  ItemCode nvarchar(30) not null,
  ItemName nvarchar(100) not null,
  SpecCode nvarchar(20) null,
  StoragePlace nvarchar(100) null,
  PictureFileName nvarchar(100) null,
  Measurements nvarchar(50) null,
  CurrencyTypeId integer null,
  BuyingPrice float null,
  SellingPrice float null,
  VatPercentage float null,
  TotalIncomingQtty float default 0,
  TotalOutgoingQtty float default 0,
  TotalIncomingAmount float default 0,
  TotalOutgoingAmount float default 0,
  OtvAmount float default 0,
  UnitDesc nvarchar(10) null,
  ReorderPoint float default 0,
  BufferStock float default 0,
  IsDeleted bit not null default 0,
  LastUpdatedDT datetime default getutcdate(),
  LastModifyingUser integer
);

insert into Inventory 
(InventoryGroupId, MainSupplierId, ItemCode, ItemName,                        SpecCode, StoragePlace, Measurements, CurrencyTypeId, BuyingPrice, SellingPrice, VatPercentage, OtvAmount, UnitDesc, ReorderPoint, BufferStock)
  values
(1,                0,              'KAG80-1',	  'Kağıt 80 gram 1. hamur',				 '',       'C18R5',      'A4,80gr',    null, 15,  19,  18, null, 'Paket', 100, 20),
(1, 0,							   'KAG90-1',	  'Kağıt 90 gram 1. hamur',				 '',       'C18R6',      'A4,90gr',    null, 20,  25,  18, null, 'Paket', 40, 5),
(2, 0,                             'DEFHMO160-3', 'Defter Harita Metot 1 orta 3. hamur', '',	   'C18R4',		 'B3,60yaprak',null, 10,  13,  18, null, 'Adet',  90, 10),
(3, 0,                             'RSKLSA3',     'Resim Kalemi Siyah A3',				 'kkk',    'R-B18',      '',           2,    1.2, 1.4, 18, null, 'Adet', 80, 10);


create table PurchaseInvoiceHeader(
  PurchaseInvoiceHeaderId integer not null identity(1, 1) primary key,
  InvoiceDate datetime not null,
  InvoiceNumber nvarchar(20),
  SupplierId integer not null,
  SpecCode nvarchar(20) null,
  InvDescription nvarchar(100) null,
  CurrencyTypeId integer,
  CurrencyRate float,
  TotalAmount float,
  VatPercentage1 float,
  VatAmount1 float,
  VatPercentage2 float,
  VatAmount2 float,
  VatPercentage3 float,
  VatAmount3 float,
  TotalOtvAmount float,
  DueDate integer default 0,
  Paid bit default 0,
  MailAddress1 nvarchar(50) NULL,
  MailAddress2 nvarchar(50) NULL,
  MailAddress3 nvarchar(50) NULL,
  MailCity nvarchar(50) NULL,
  MailCountry nvarchar(50) NULL,
  MailPostCode nvarchar(20) NULL,
  IsDeleted bit not null default 0,
  LastUpdatedDT datetime default getutcdate(),
  LastModifyingUser integer,
  CONSTRAINT FK_PurchaseInvoiceHeader_Supplier FOREIGN KEY (SupplierId)     
    REFERENCES Supplier (SupplierId)
);

create table SalesInvoiceHeader(
  SalesInvoiceHeaderId integer not null identity(1,1) primary key,
  InvoiceDate datetime not null,
  InvoiceNumber nvarchar(20),
  CustomerId integer not null,
  SpecCode varchar(20) null,
  InvDescription nvarchar(100) null,
  CurrencyTypeId integer,
  CurrencyRate float,
  TotalAmount float,
  VatPercentage1 float,
  VatAmount1 float,
  VatPercentage2 float,
  VatAmount2 float,
  VatPercentage3 float,
  VatAmount3 float,
  TotalOtvAmount float,
  DueDate integer default 0,
  Paid bit default 0,
  DeliveryAddress1 nvarchar(50) null,
  DeliveryAddress2 nvarchar(50) null,
  DeliveryAddress3 nvarchar(50) null,
  DeliveryCity nvarchar(50) null,
  DeliveryCountry nvarchar(50) null,
  DeliveryPostCode nvarchar(20) null,
  IsDeleted bit not null default 0,
  LastUpdatedDT datetime default getutcdate(),
  LastModifyingUser integer,
  CONSTRAINT FK_SalesInvoiceHeader_Customer FOREIGN KEY (CustomerId)     
    REFERENCES Customer (CustomerId)
);