供应链管理系统源码

USE master GO --新建数据库------------------------------------------------------ DROP DATABASE Retailer DROP DATABASE Supplier DROP DATABASE ShippingCo --新建retailer数据库 CREATE DATABASE Retailer GO --新建Supplier数据库 CREATE DATABASE Supplier GO --新建shippingco数据库 CREATE DATABASE ShippingCo --------------------------------------------------------------- -- 设置Supplier数据库 --------------------------------------------------------------- USE Supplier GO CREATE TABLE Products ( ProductID int Not Null, ProductName nvarchar(40) not null, CategoryID int null, UnitPrice money null, Tax money null, ) GO CREATE TABLE Categories ( CategoryID int not null, CategoryName nvarchar(20) not null, ) GO --Add products INSERT Categories VALUES (1, '电脑系列') INSERT Categories VALUES (2, '显示器') INSERT Categories VALUES (3, '打印机') INSERT Categories VALUES (4, '周边设备等.') GO INSERT Products VALUES (1, '英特尔赛扬1.7G系统', 1, 38000, 0) INSERT Products VALUES (2, '英特尔Pentium 4/2000系统', 1, 40000, 0) INSERT Products VALUES (3, 'AMD Athlon XP 1900+系统', 1, 35000, 0) INSERT Products VALUES (4, 'AMD Duron 850系统', 1, 25000, 0) INSERT Products VALUES (5, '15寸LCD显示器', 2, 10000, 0) INSERT Products VALUES (6, '17寸LCD显示器', 2, 18000, 0) GO INSERT Products VALUES (7, '平面直角显示器', 2, 1000, 0) INSERT Products VALUES (9, '彩色喷墨打印机', 3, 6300, 0) INSERT Products VALUES (10, '单色激光打印机', 3, 700, 0) INSERT Products VALUES (11, '彩色激光打印机', 3, 10000, 0) INSERT Products VALUES (12, 'UTP电缆线(100 mtrs)', 4, 100, 0) INSERT Products VALUES (13, '小型集线器', 4, 2000, 0) INSERT Products VALUES (14, 'ISDN路由器', 4, 6000, 0) INSERT Products VALUES (15, '赛扬笔记型电脑', 1, 40000, 0) INSERT Products VALUES (16, '英特尔Pentium 4/1000笔记型电脑', 1, 50000, 0) INSERT Products VALUES (17, '英特尔Pentium 4/1000系统', 1, 36000, 0) INSERT Products VALUES (18, '无线鼠标', 4, 2000, 0) INSERT Products VALUES (19, 'Athlon笔记型电脑', 1, 45000, 0) GO -- 指定税金 UPDATE Products SET Tax = (UnitPrice * 0.05) GO --新建Order与OrderDetail数据表 CREATE TABLE Orders (OrderNo integer not null, OrderDate DateTime, RetailerID varchar(30) not null, SupplierID varchar(30) not null, Customer varchar(40), DeliveryAddress varchar(255), OrderStatus varchar(40) null ) GO CREATE TABLE OrderDetails (OrderNo integer not null, ProductID int not null, Quantity int not null, UnitPrice money null, Tax money null, ) GO -- 插入订单初始资料 INSERT Orders VALUES (1000, getdate(), 'retailer','supplier', newid(), '台北市仁爱路春风街12号', 'placed') INSERT OrderDetails VALUES (1000, 1, 1, 1000.00, 250) INSERT OrderDetails VALUES (1000, 9, 1, 300.00, 75) GO --新建ImportOrder预存程序 CREATE PROC ImportOrder @xml ntext AS DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @xml INSERT Orders SELECT * FROM OpenXML(@idoc, 'PurchaseOrder/Order', 3) WITH (OrderNo integer, OrderDate datetime, RetailerID varchar(30), SupplierID varchar(30), Customer varchar(40), DeliveryAddress varchar(255), OrderStatus varchar(40) ) INSERT OrderDetails SELECT * FROM OpenXML(@idoc, 'PurchaseOrder/Order/OrderDetail', 2) WITH (OrderNo integer '../@OrderNo', ProductID int, Quantity int, UnitPrice money, Tax money ) EXEC sp_xml_removedocument @idoc GO -- 数据库安全设置 declare @s varchar(50) SELECT @s = @@servername + '\IUSR_' + @@servername EXEC sp_grantlogin @s Exec sp_grantdbaccess @s, 'webcustomer' GO GRANT EXECUTE ON ImportOrder TO webcustomer EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'webcustomer' EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'webcustomer' GO ----------------------------------------------------------------- -- 设置Retailer数据库 ----------------------------------------------------------------- USE retailer GO CREATE TABLE Products ( ProductID int Not Null, ProductName nvarchar(40) not null, CategoryID int null, UnitPrice money null, Tax money, ) GO CREATE TABLE Categories ( CategoryID int not null, CategoryName nvarchar(20) not null, ) GO --由supplier数据库汇入资料 INSERT Categories SELECT CategoryID, CategoryName FROM supplier.dbo.categories GO INSERT Products SELECT ProductID, ProductName, CategoryID, UnitPrice, Tax FROM supplier.dbo.products WHERE ProductID < 13 GO --新建Order与Order Details数据表 CREATE TABLE Orders (OrderNo integer not null, OrderDate DateTime, RetailerID varchar(30) not null, SupplierID varchar(30), Customer varchar(40), DeliveryAddress varchar(255), OrderStatus varchar(40) ) GO CREATE TABLE OrderDetails (OrderNo integer not null, ProductID int not null, Quantity int not null, UnitPrice money null, Tax money ) GO -- 插入订单初始资料 INSERT Orders VALUES (1000, getdate(), 'retailer','supplier', newid(), 'xx市xx路xx街xx号', 'placed') INSERT OrderDetails VALUES (1000, 1, 1, 1000.00, 250) INSERT OrderDetails VALUES (1000, 9, 1, 300.00, 75) GO --新建Invoice数据表 CREATE TABLE Invoices (InvoiceNo integer not null, OrderDate datetime, RetailerID varchar(30), SupplierID varchar(30), CustomerRef varchar(40), Total money ) GO CREATE TABLE LineItems (InvoiceNo integer not null, ProductName varchar(40), Quantity integer, Price money ) GO --购物袋管理 CREATE TABLE Basket (Customer varchar(40) not null, ProductID int not null, Quantity int not null, UnitPrice money null, Tax money ) GO --加入项目AddItem预存程序 CREATE PROC AddItem @Customer varchar(40), @ProductID int, @Quantity int, @UnitPrice money, @Tax money AS INSERT Basket VALUES (@Customer, @ProductID, @Quantity, @UnitPrice, @Tax) GO --删除项目RemoveItem预存程序 CREATE PROC RemoveItem @Customer varchar(40), @ProductID int AS DELETE Basket WHERE Customer = @Customer AND ProductID = @ProductID GO --结帐CheckOut预存程序 CREATE PROC CheckOut @Customer varchar(40), @Address varchar(255), @OrderNo integer OUTPUT AS BEGIN --取得下一个订单编号 SELECT @OrderNo = MAX(OrderNo) + 1 FROM Orders --插入订单表头 INSERT Orders VALUES (@OrderNo,GetDate(), 'retailer','supplier', @Customer, @Address, 'placed') --插入订单明细与订单编号到暂存数据表#OrderDetails SELECT @OrderNo OrderNo, ProductID, Quantity, UnitPrice, Tax INTO #OrderDetails FROM Basket --插入暂存数据表内容到订单明细数据表 INSERT OrderDetails SELECT OrderNo, ProductID, Quantity, UnitPrice, Tax FROM #OrderDetails --删除在basket中的项目 DELETE Basket WHERE Customer = @Customer --删除暂存数据表 DROP TABLE #OrderDetails END GO CREATE VIEW BasketView AS SELECT P.ProductID, P.ProductName, B.UnitPrice, B.Tax, B.Quantity, B.Customer FROM Products P JOIN Basket B ON P.ProductID = B.ProductID GO --会员管理 CREATE TABLE users ( userid uniqueidentifier not null primary key, username varchar(20) not null, userpassword varchar(20) not null ) GO CREATE UNIQUE INDEX userIndex ON users(username) GO INSERT users VALUES (newid(),'Graeme', 'password') GO CREATE PROC getuserid @Username varchar(30), @Password varchar(20), @UserId varchar(36) OUTPUT AS SELECT @UserID = UserID FROM users WHERE username = @UserName AND userpassword = @Password GO CREATE PROC addUser @UserName varchar(30), @Password varchar(20), @UserID varchar(36) OUTPUT AS SET @UserID = NewID() INSERT Users VALUES (@UserID, @UserName, @Password) GO --匿名存取安全设置 declare @s varchar(50) SELECT @s = @@servername + '\IUSR_' + @@servername Exec sp_grantdbaccess @s, 'webcustomer' GO EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'webcustomer' EXEC sp_addrole