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
供应链管理系统源码.rar （52个子文件） 
供应链管理系统源码 XML Retailer Include 
ADOdata.asp 431B test.htm 2KB Product.asp 2KB pdetail.htm 3KB Login.asp 1KB Menu.asp 1KB show.htm 530B Main.asp 2KB AppFiles basket.xsl 1KB Invoices.xsl 1KB menu.xsl 1020B OrderSchema.xml 2KB CatalogSchema.xml 1KB Products.xsl 1KB productdetails.xsl 2KB AddItem.asp 2KB Register.htm 928B Payment.asp 619B CatalogImport CatalogImport.vbs 2KB Catalog.xml 2KB CatalogSchema.xml 1KB Login.htm 407B Header.htm 152B Register.asp 1KB Browse.asp 1KB Logout.asp 127B Checkout.asp 3KB Basket.asp 2KB index.htm 3KB Images 
LCD_PC.JPG 3KB hplaserjet1200.jpg 3KB Thumbs.db 16KB Notebook.JPG 4KB Default.htm 686B RemoveItem.asp 845B SupplierIntranet header.htm 534B Dispatch.xsl 2KB Templates Orders.xml 277B Orders.xsl 1KB OrderDetails.xsl 2KB OrderDetails.xml 521B Untitled-3.htm 2KB Dispatch.asp 2KB Default.htm 875B ShippingCo templates deliveries.xml 260B deliveries.xsl 977B Delivery.xsl 712B SQLXMLDemo.sql 10KB SupplierExtranet Schemas CatalogSchema.xml 1KB PlaceOrder.asp 559B sqlxml3.0.msi 2.57MB
安装说明.doc 159KB
- 1
- 2
- 3
前往页