shophere.sql_full.sql

NIIT project

create proc prc_insert_PurchaseOrders
@pid char(4), @sid char(6), @eid char(6), @odate datetime, @sdate datetime, @shid char(6), @fcharge money
as
begin
	if @odate is null
		begin
			insert into dbo.PurchaseOrders (PurchaseOrderID,SupplierID,EmployeeID,ShipDate, ShippingMethodID, FreightCharge)
				values (@pid, @sid, @eid, @sdate, @shid, @fcharge)
		end
	else
		begin
			insert into dbo.PurchaseOrders (PurchaseOrderID,SupplierID,EmployeeID,OrderDate,ShipDate, ShippingMethodID, FreightCharge)
				values (@pid, @sid, @eid, @odate, @sdate, @shid, @fcharge)
		end
end
go


alter table dbo.PurchaseOrders
add constraint df_orderdate default getdate() for OrderDate
go


create trigger trg_check_PurchaseOrders
on PurchaseOrders
for insert, update
as
begin
	declare @fcharge money, @odate datetime, @sdate datetime
	select @fcharge = FreightCharge, @odate = OrderDate, @sdate = ShipDate
	from inserted
	
	if @fcharge <= 0
		begin
			print'Chu y: FreightCharge > 0'
			rollback tran
		end
	else
		begin
			if @odate > getdate() and @odate > @sdate
				begin
					print' OrderDate <= Current date & ShipDate'
					rollback tran
				end
	end
end
go
-----------------------------------------


create table InventoryTransactions
(
	TransactionID char(6) not null,
	TransactionDate datetime,
	ItemID char(6) not null,
	PurchaseOrderID char(4) not null,
	TransactionDescription varchar(30) not null,
	QuantityOrdered int,
	QuantityReceived int,
	TotalAmount money
)
go


alter table dbo.InventoryTransactions
add constraint pk_transactions primary key (TransactionID)
go


create proc prc_genTransactions
@tid char(6) output
as
if not exists(select * from dbo.InventoryTransactions) select @tid='000000'
else
	select @tid=Max(RIGHT(TransactionID,5)) from dbo.InventoryTransactions
	select @tid=
	case
		when @tid >=0 and @tid <9 Then 'T0000'+CONVERT(char,@tid+1)
		when @tid >=9 and @tid <99 Then 'T000'+CONVERT(char,@tid+1)
		when @tid >=99 and @tid <999 Then 'T00'+CONVERT(char,@tid+1)
		when @tid >=999 and @tid <9999 Then 'T0'+CONVERT(char,@tid+1)
		when @tid >=9999 and @tid <=99998 Then 'T'+CONVERT(char,@tid+1)
		
	end
go


create proc prc_insert_Transactions
@tdate datetime, @iid char(6), @pid char(4), @tdes varchar(30), @qordered int, @qreceived int
as
begin
	declare @tid char(6)
	exec dbo.prc_genTransactions @tid output

	if @tdate is null
		begin
			insert into dbo.InventoryTransactions (TransactionID, ItemID, PurchaseOrderID, TransactionDescription, QuantityOrdered, QuantityReceived)
				values (@tid, @iid, @pid, @tdes, @qordered, @qreceived)
		end
	else
		begin
			insert into dbo.InventoryTransactions (TransactionID, TransactionDate, ItemID, PurchaseOrderID, TransactionDescription, QuantityOrdered, QuantityReceived)
				values (@tid, @tdate, @iid, @pid, @tdes, @qordered, @qreceived)
		end
end
go


alter table dbo.InventoryTransactions
add constraint df_transactiondate default getdate() for TransactionDate
go


create trigger trg_check_iTransactions_1
on dbo.InventoryTransactions
for insert, update
as
begin
	declare @qordered int, @qreceived int
	select @qordered = QuantityOrdered, @qreceived = QuantityReceived
	from inserted
	
	if @qordered <= 0 or @qreceived <= 0
		begin
			print'Chu y: QuantityOrdered,QuantityReceived > 0'
			rollback tran
		end
end
go


create trigger trg_check_iTransactions_2
on dbo.InventoryTransactions
for insert, update
as
begin
	declare @qordered int, @qreceived int, @tdate datetime, @odate datetime
	select	@qordered = inserted.QuantityOrdered, @qreceived = inserted.QuantityReceived,
		@tdate = inserted.TransactionDate,
		@odate = OrderDate
	from	inserted join PurchaseOrders	on inserted.PurchaseOrderID = PurchaseOrders.PurchaseOrderID

	if @qreceived > @qordered
		begin
			print'Chu y: QuantityReceived <= QuantityOrdered'
			rollback tran
		end
	else
		begin
			if @tdate > getdate() and @tdate <= @odate
				begin
					print'Chu y: OrderDate of PurchaseOrder < TransactionDate <= Current date'
					rollback tran
				end
		end
end
go

-------------
--TotalAmount = ((QuantityReceived * UnitPrice of Item) + FreightCharge)
create trigger trg_chk_totalamount on dbo.InventoryTransactions
for insert, update
as
begin
	declare @qreceived int, @fcharge money, @price money, @total money, @tid char(6), @iid char(6), @pid char(6)
	select	@qreceived = inserted.QuantityReceived,  @total = inserted.TotalAmount,
		@tid = inserted.TransactionID, @iid = inserted.ItemID, @pid = inserted.PurchaseOrderID,
		@price = UnitPrice, @fcharge = FreightCharge
	from  inserted join Items		on inserted.ItemID = Items.ItemID
			 join PurchaseOrders	on inserted.PurchaseOrderID = PurchaseOrders.PurchaseOrderID
	
	update dbo.InventoryTransactions
	set TotalAmount = ((@fcharge * @price) + @fcharge)
	where TransactionID = @tid
end

go

---------------------------
--ShipDate tu dong cap nhat them 2 ngay, sau khi TransactionDate hoan tat
create trigger trg_update_shipdate on dbo.InventoryTransactions
for update
as
begin
	declare @tdate datetime, @sdate datetime, @pid char(4)
	select 	@tdate = inserted.TransactionDate, @pid = inserted.PurchaseOrderID,
		@sdate = ShipDate
	from inserted join PurchaseOrders	on inserted.PurchaseOrderID = PurchaseOrders.PurchaseOrderID

	update dbo.PurchaseOrders
	set ShipDate = dateadd(dd,2,@sdate)
	where PurchaseOrderID = @pid
end

go

---------------------------
-- QuantityReceived duoc nhap vao, QOH tu dong cap nhat thong tin
create trigger trg_updateQOH on dbo.InventoryTransactions
for update
as
begin
	declare @qreceived int, @qoh int, @iid char(6)
	select 	@qreceived = inserted.QuantityReceived, @iid = inserted.ItemID,
		@qoh = QOH
	from inserted join Items	on inserted.ItemID = Items.ItemID
	
	update dbo.Items
	set QOH = @qoh + @qreceived
	where ItemID = @iid
end

go

-------------------------------

alter table dbo.Items
add constraint fk_items foreign key (CategoryID) references dbo.Categories

go
alter table dbo.PurchaseOrders
add constraint fk_purchaseorders1 foreign key (SupplierID) references dbo.Suppliers(SupplierID)
go

alter table dbo.PurchaseOrders 
add constraint fk_purchaseorders2 foreign key (EmployeeID) references dbo.Employees

go

alter table dbo.PurchaseOrders 
add constraint fk_purchaseorders3 foreign key (ShippingMethodID) references dbo.ShippingMethods

go

alter table dbo.InventoryTransactions 
add constraint fk_transactions1 foreign key (ItemID) references dbo.Items

go

alter table dbo.InventoryTransactions 
add constraint fk_transactions2 foreign key (PurchaseOrderID) references dbo.PurchaseOrders
go
------------------------------

alter proc prc_delete_SupplierID
@sid char(6)
as
begin
	if not exists (select * from Suppliers where SupplieriD = @sid)
		print'Khong tim thay '+@sid+'tuong ung de xoa'
	else
		begin
			delete InventoryTransactions
			where	TransactionID in (Select TransactionID 
				 		  from InventoryTransactions
		        			  where PurchaseOrderID in (Select PurchaseOrderID
									    from PurchaseOrders
									    where SupplierID = @sid))
			print'Da xoa du lieu trong bang InventoryTransactions'
			--------------------------------------------------------
			delete PurchaseOrders
			where SupplierID = @sid
			print'Da xoa du lieu trong bang PurchaseOrders'
			--------------------------------------------------------
			delete Suppliers
			where SupplierID = @sid
			print'Da xoa thanh cong '+@sid+'trong bang Suppliers'
		end 
end
go

--exec prc_delete_SupplierID 'U00001'
----------------------------
-------------- cau 7 ---------------------------------------------
--cau a
select *
from dbo.PurchaseOrders
where convert (char(10),month(getdate())) = convert(char(10), month(OrderDate))

--cau b
select *
from dbo.PurchaseOrders a join dbo.Employees b
on a.EmployeeID = b.EmployeeID

---cau c
select *
from dbo.PurchaseOrders
where convert(char(10),year(getdate()))-2 = convert(char(10),year(ShipDate))

---cau d
select *
from dbo.Employees

---chi muc
go
create nonclustered index idx_PurchaseOrders1
on dbo.PurchaseOrders (OrderDate)

go
create nonclustered index idx_PurchaseOrders2
on dbo.PurchaseOrders (EmployeeID)

go
create nonclustered index idx_PurchaseOrders3
on dbo.PurchaseOrders (ShipDate)

go
create nonclustered index idx_Employees
on dbo.Employees (Title)


sp_helpindex PurchaseOrders
sp_helpindex Employees
---------------------------------------------------

exec dbo.prc_insert_Cats 'hang de vo'
exec dbo.prc_insert_Cats 'hang chat luong'

exec dbo.prc_insert_Emps 'vo', 'thong', 'Senior Excutive', '2314-123-232'
exec dbo.prc_insert_Emps 'ho', 'vi', 'Trainee', '4567-456-444'

exec dbo.prc_insert_Items 'ly su', 'ly su dung uong nuoc', 'C00001', 2, 3, 2
exec dbo.prc_insert_Items 'keo cao su', 'cao su nguy阯 chat', 'C00002', 4, 1, 5

exec dbo.prc_insert_ShippingMethods 'duong bo'
exec dbo.prc_insert_ShippingMethods 'duong chim bay'

exec dbo.prc_insert_Suppliers 'cao', 'cao', '123qwe', 'hcm', '084', 'q11', 'trung quoc', '4321-332-321'
exec dbo.prc_insert_Suppliers 'lun', 'lun', '890asd', 'hcm', '123', 'q11', 'viet name', '9874-456-987'

exec dbo.prc_insert_PurchaseOrders 'P001', 'U00001', 'E00002',null , '2009-03-25', 'S00002', 2
exec dbo.prc_insert_PurchaseOrders 'p002', 'U00002', 'E00002','2009-03-1' , '2009-03-25', 'S00002', 3



exec dbo.prc_insert_Transactions '2009-03-01', 'I00002', 'p002', 'xong xuoi het roi', 5, 2
exec dbo.prc_insert_Transactions null, 'I00001', 'p001', 'xong xuoi het roi', 4, 2