/*1.新建名為CommodityManage的數據庫*/
create database CommodityManage

/*2.創建各個表*/
create table SupplierDetail
( cSupplierID char(6) primary key,
  vSupplierName varchar(50),
  vSupplierDescription varchar(50),
  cSupplierTele char(12) check(cSupplierTele like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
  vSupplierAddress varchar(50),
  iShipmentDelay int,
  cCredibilityDeg char(5) check(cCredibilityDeg in('so-so','high','best','medium'))
)

create table Commodity
( cCommodityID char(6) primary key,
  vCommodityName varchar(50),
  vCommodityType varchar(20),
  vCommodityDescription varchar(50),
  mCommodityUnitPrice money,
  vCommodityUnit char(10),
)

create table Supply
( cCommodityID char(6),
  cSupplierID char(6),
  primary key(cCommodityID,cSupplierID)
)
  
create table CommodityDetail
( cCommodityID char(6) not null,
  cSupplierID char(6) not null,
  vCommodityName varchar(50),
  vCommodityType varchar(20),
  iCommodityQty int,
  iCommodityThreshold int,
  dCommodityStoreDate datetime,
  cNeedFlag char(1),
  cOrderFlag char(1),
  foreign key (cCommodityID) references Commodity (cCommodityID),
  foreign key (cSupplierID) references SupplierDetail (cSupplierID)
 )


create table OrderDetail
( cOrderID char(3) primary key,
  cCommodityID char(6),
  cSupplierID char(6),
  iOrderQty int,
  dOrderDate datetime,
  dShipmentDate datetime,
  vOrderRemark varchar(50),
  vOrderClerk varchar(50),
  foreign key (cCommodityID) references Commodity (cCommodityID),
  foreign key (cSupplierID) references SupplierDetail (cSupplierID)
)

create table Sold
( cJournalNo char(4) primary key,
  cCommodityID char(6),
  cSupplierID char(6),
  vCommodityName varchar(50),
  iCommodityQty int,
  dSoldDate datetime
  foreign key (cCommodityID) references Commodity (cCommodityID),
  foreign key (cSupplierID) references SupplierDetail (cSupplierID)
)

/*3.在各個表中插入數據*/
/*3.1 在SupplierDetail表中插入數據*/

insert into SupplierDetail values('SE0001','Rdtio Goods','Famous,high quality','0482-2479920','London Bridge No.114',3,'best')
insert into SupplierDetail values('SE0002','Tehns Squre','so-so,but cheap','0477-5489456','Tianhe Road No.231',5,'so-so')
insert into SupplierDetail values('SE0003','Ocean Store','not bad','0479-5461378','Zhongshan Road No.12',4,'high')
insert into SupplierDetail values('SE0004','Mars Commodities','quite good,and very fast','0762-5897146','Renmin Road No.35',2,'high')
insert into SupplierDetail values('SE0005','Simple','good serve and good commodities','0495-8543468','Nanjing Square No.64',3,'best')
insert into SupplierDetail values('SE0006','Just Buy','not so good','0489-5746132','Zhongshan Road No.234',4,'so-so')

/*3.2 在Commodity表中插入數據*/

insert into Commodity values('TM0001','Banana','Fruit','A cheap and delicious fruit','1.7','kilo');/*香蕉*/
insert into Commodity values('MD0002','Apple','Fruit','An apple a day keep the doctor away','2.4','kilo');/*蘋果*/
insert into Commodity values('ZE0004','Cake','Dessert','A sweet food for you to enjoy','10','box');/*蛋糕*/
insert into Commodity values('JJ0007','Chocolate','Dessert','Almost girls enjoy it','15','box');/*巧克力*/
insert into Commodity values('SB0009','Toothpaste','Daily-Necessities','Necessities for tooth','9.9','box');/*牙膏*/
insert into Commodity values('TK0011','Paper','Daily-Necessities','Necessities for clean','4.5','package');/*紙巾*/
insert into Commodity values('EF0014','Rice','Daily-Food','Food that people eat everyday','50','kg');/*大米*/
insert into Commodity values('GE0020','Peanut oil','Daily-Food','Need for cook','45','can');/*花生油*/
insert into Commodity values('FE0027','Badminton Racket','Fun','Need for sport','99','pair');/*羽毛球拍*/
insert into Commodity values('RG0029','Genuine-DVD','Fun','Film,Animation,Concert and so on','111','piece');/*正版DVD*/

/*3.3 在Supply表中插入數據*/

insert into Supply values('TM0001','SE0001')
insert into Supply values('TM0001','SE0004')
insert into Supply values('MD0002','SE0006')
insert into Supply values('EF0014','SE0003')
insert into Supply values('TK0011','SE0002')
insert into Supply values('JJ0007','SE0005')
insert into Supply values('SB0009','SE0005')
insert into Supply values('GE0020','SE0001')
insert into Supply values('FE0027','SE0002')
insert into Supply values('RG0029','SE0003')
insert into Supply values('ZE0004','SE0004')
insert into Supply values('ZE0004','SE0006')
insert into Supply values('TK0011','SE0004')
insert into Supply values('GE0020','SE0003')
insert into Supply values('JJ0007','SE0001')
insert into Supply values('JJ0007','SE0002')
insert into Supply values('FE0027','SE0003')
insert into Supply values('RG0029','SE0006')

/*3.4 在CommodityDetail表中插入數據*/

insert into CommodityDetail values('TM0001','SE0001','Banana','Fruit',56,10,'2008-12-29','n','n')
insert into CommodityDetail values('ZE0004','SE0004','Cake','Dessert',150,25,'2008-11-20','n','n')
insert into CommodityDetail values('TK0011','SE0004','Paper','Daily-Necessities',130,50,'2008-10-5','n','n')
insert into CommodityDetail values('FE0027','SE0003','Badminton Racket','Fun',20,5,'2008-9-15','n','n')
insert into CommodityDetail values('GE0020','SE0001','Peanut oil','Daily-Food',17,5,'2008-11-26','n','n')

/*4.需要用到的觸發器*/

/*4.1 在更新CommodityDetail表時自動更改標志位*/

create trigger trgUpdateCommodityDetail
on CommodityDetail
after update
as
	declare @qty int
	declare @threshold int
	declare @id char(6)
	declare @need char(1)
	select @id=cCommodityID,@qty=iCommodityQty,@threshold=iCommodityThreshold,@need=cNeedFlag from inserted
	if(@qty<@threshold)
	begin
		if(@need='n')
		begin
			update CommodityDetail
			set cNeedFlag='y',cOrderFlag='n'
			where cCommodityID=@id
		end
	end
	else
	begin
		update CommodityDetail
		set cNeedFlag='n',cOrderFlag='n'
		where cCommodityID=@id
	end

/*4.2 在CommodityDetail表中插入新記錄時自動填寫部分列*/

create trigger trgInsertCommodityDetail
on CommodityDetail
after insert
as
	update CommodityDetail
	set dCommodityStoreDate=getdate(),cNeedFlag='n',cOrderFlag='n'
	where cCommodityID=(select cCommodityID from inserted)

/*5.需用到的存儲過程*/

/*5.1 生成售出商品信息*/

create proc prcSold @journalno char(4),@commodityid char(6),@qty int
as
begin
		 begin tran
		 declare @date datetime
	     select @date=getdate()
	     declare @supplierid char(6)
	     declare @commodityname varchar(50)
		 declare @result int
	     select @supplierid=cSupplierID,@commodityname=vCommodityName
	     from CommodityDetail where cCommodityID=@commodityid
	     insert into Sold values(@journalno,@commodityid,@supplierid,@commodityname,@qty,@date)
	     update CommodityDetail set iCommodityQty=iCommodityQty-@qty where cCommodityID=@commodityid
		 set @result=(select iCommodityQty from CommodityDetail where cCommodityID=@commodityid)
		 if(@result<0)
		 begin
			rollback tran
			print '貨物不足！請重新輸入售出的數量。'
		 end
		 commit tran
end

/*5.2 生成一條定單記錄*/

create proc prcOrder @orderid char(3),@commodityid char(6),@supplierid char(6),@orderqty int,@remark varchar(50),@clerk varchar(50)
as
begin
	declare @orderdate datetime
	declare @shipmentdate datetime
	declare @delay int
	select @orderdate=getdate()
	select @delay=iShipmentDelay 
	from SupplierDetail
	where cSupplierID=@supplierid
	select @shipmentdate=dateadd(dd,@delay,getdate())
	insert into OrderDetail 
	values(@orderid,@commodityid,@supplierid,@orderqty,@orderdate,@shipmentdate,@remark,@clerk)
	update CommodityDetail set cOrderFlag='y' where cCommodityID=@commodityid
end