createdbobjects.sql

ASP.NET 建立和发布可自定义 Web 报表的指南

	CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 
	(
		[CustomerTypeID]
	) REFERENCES [dbo].[Reports_CustomerDemographics] (
		[CustomerTypeID]
	),
	CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Reports_Customers] (
		[CustomerID]
	)
GO

ALTER TABLE [dbo].[Reports_Orders] ADD 
	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Reports_Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
	(
		[ShipVia]
	) REFERENCES [dbo].[Reports_Shippers] (
		[ShipperID]
	),
	CONSTRAINT [FK_Reports_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Reports_Customers] (
		[CustomerID]
	)
GO

ALTER TABLE [dbo].[Reports_Products] ADD 
	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 
	(
		[SupplierID]
	) REFERENCES [dbo].[Reports_Suppliers] (
		[SupplierID]
	),
	CONSTRAINT [FK_Reports_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[Reports_Categories] (
		[CategoryID]
	)
GO

ALTER TABLE [dbo].[Reports_Territories] ADD 
	CONSTRAINT [FK_Territories_Region] FOREIGN KEY 
	(
		[RegionID]
	) REFERENCES [dbo].[Reports_Region] (
		[RegionID]
	)
GO

ALTER TABLE [dbo].[Reports_EmployeeTerritories] ADD 
	CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Reports_Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [dbo].[Reports_Territories] (
		[TerritoryID]
	)
GO

ALTER TABLE [dbo].[Reports_Order Details] ADD 
	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[Reports_Orders] (
		[OrderID]
	),
	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[Reports_Products] (
		[ProductID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetAllCustomers

AS

	SELECT * FROM Reports_Customers
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetCustomerContacts
AS

	SELECT CompanyName, ContactName, ContactTitle, Phone, City
	FROM Reports_Customers
	WHERE Phone IS NOT NULL



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetEmployeeByID
(
	@EmployeeID int
)
AS
	SELECT 	e.EmployeeID,
			e.FirstName + ' ' + e.LastName as EmployeeName, 
			e.LastName, 
			e.FirstName, 
			e.Title, 
			e.HireDate, 
			e.Address, 
			e.City, 
			e.Region, 
			e.PostalCode, 
			e.Country, 
			e.HomePhone, 
			e.Extension, 
			e2.FirstName + ' ' + e2.LastName as ReportsTo
	FROM Reports_Employees e
		INNER JOIN Reports_Employees e2 ON
		e.ReportsTo = e2.EmployeeID
	WHERE	e.EmployeeID = @EmployeeID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetEmployees
AS

SELECT  
    e.FirstName + ' ' + e.LastName as EmployeeName,
    e.Notes as EmployeeNotes,
	e.Title as EmployeeTitle,
	e.Extension as EmployeeExt,
	e.Address as EmployeeAddress,
	e.City as EmployeeCity,
	e.Region as EmployeeState,
	e.PostalCode as EmployeeZip
FROM
    Reports_Employees e
WHERE
	e.Notes IS NOT NULL
ORDER BY e.LastName, e.FirstName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


Create Procedure Reports_GetCategories
AS

SELECT  
    Reports_Categories.CategoryID, 
    Reports_Categories.CategoryName, 
    Sum(Reports_Products.UnitsInStock) AS TotalInStock
    
FROM
    Reports_Products 
        INNER JOIN Reports_Categories 
            ON Reports_Products.CategoryID = Reports_Categories.CategoryID

GROUP BY Reports_Categories.CategoryID, Reports_Categories.CategoryName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetOrders
(
	@CustomerID varchar(5)
)
AS

	SELECT * FROM Reports_Orders WHERE CustomerID = @CustomerID	AND ShippedDate <> ''


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetProductsByCategory
(
    @CategoryID int
)
AS

SELECT
    CategoryID, 
    ProductName, 
    QuantityPerUnit, 
    UnitPrice, 
    UnitsInStock
    
FROM
    Reports_Products 
            
WHERE
    CategoryID = @CategoryID
    
ORDER BY 
	ProductName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetCategorySales
    AS
    
    SELECT
        C.CategoryName, 
        Sum(OD.UnitPrice * OD.Quantity * (1-OD.Discount)) Sales
        
    FROM 
        [Reports_Order Details] OD 
            INNER JOIN Reports_Products P 
                ON OD.ProductID = P.ProductID
            INNER JOIN Reports_Categories C 
                ON P.CategoryID = C.CategoryID
                
    GROUP BY C.CategoryName
    
    ORDER BY C.CategoryName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Reports_GetEmployeeSalesByTerritory
(
	@TerritoryName nchar(50),
	@Year int = 1996
)
AS

SELECT    e.EmployeeID,
         e.FirstName + ' ' + e.LastName as EmployeeName,
         SUM(d.UnitPrice * d.Quantity) as 'SalesTotals'
FROM    Reports_Orders o
   INNER JOIN [Reports_Order Details] d 
      ON o.OrderID = d.OrderID
   INNER JOIN Reports_Employees e 
      ON o.EmployeeID = e.EmployeeID
   INNER JOIN Reports_Territories t 
      ON o.TerritoryID = t.TerritoryID
WHERE 
   t.TerritoryDescription = RTRIM(@TerritoryName)
   AND DATEPART(yy, o.OrderDate) = @Year

GROUP BY e.EmployeeID, e.LastName, e.FirstName

ORDER BY  e.LastName, e.FirstName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetOrderDetails
(
	@orderID int
)
AS

	Select 
		od.ProductID, od.UnitPrice, Quantity, ProductName
	FROM 
		[Reports_Order Details] od
	JOIN 
		Reports_Products p
	ON od.ProductID = p.ProductID
	WHERE OrderID = @orderID	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetOrderSummary
(
    @Year int
)
AS

SELECT 
    DATEPART(q, OrderDate) Quarter, 
    COUNT(DISTINCT Reports_Orders.OrderID) OrdersShipped, 
    SUM(UnitPrice * Quantity * (1-Discount)) Sales 
FROM Reports_Orders 
    INNER JOIN [Reports_Order Details] Details 
        ON Reports_Orders.OrderID = Details.OrderID 
WHERE YEAR(OrderDate) =  @Year 
GROUP BY DATEPART(q, OrderDate)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Reports_GetOrdersAndDetails
(
	@Year int,
	@Quarter int
)	
AS

SELECT 
	Reports_Orders.OrderID,
	OrderDate,
	SUM(UnitPrice * Quantity * (1-Discount)) Sales

FROM Reports_Orders 
	INNER JOIN [Reports_Order Details] Details 
		ON Reports_Orders.OrderID = Details.OrderID 

WHERE
	YEAR(OrderDate) = @Year AND
	(@Quarter = 0 OR
	DATEPART(q, OrderDate) = @Quarter)

GROUP BY
	DATEPART(y, OrderDate),
	Reports_Orders.OrderID,
	OrderDate

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE Reports_GetSalesByRegion
(
	@Year int,
    	@Quarter int
)
AS
	Select 
	   *, TotalMonth = Eastern + Western + Northern + Southern
From   
	(
	Select DatePart(month, OrderDate) MonthOfSales, 
	Sum(Case RegionID When 1 Then (UnitPrice * Quantity ) Else 0 End) As Eastern,
	Sum(Case RegionID When 2 Then (UnitPrice * Quantity ) Else 0 End) As Western,
	Sum(Case RegionID When 3 Then (UnitPrice * Quantity ) Else 0 End) As Northern,
	Sum(Case RegionID When 4 Then (UnitPrice * Quantity ) Else 0 End) As Southern
From Reports_Orders O
   
	Inner Join Reports_Territories T On O.TerritoryID = T.TerritoryID 
	Inner Join [Reports_Order Details] OD On O.OrderID = OD.OrderID
	Where Year(OrderDate) = @Year And DatePart(q, OrderDate) = @Quarter
	Group By DatePart(month, OrderDate)

) As T
Order By MonthOfSales
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Reports_GetSalesByTerritory
(
	@Year int = 1996
)
AS

SELECT    t.TerritoryDescription,
         SUM(d.UnitPrice * d.Quantity)  as 'SalesTotals'
FROM    Reports_Orders o
   INNER JOIN [Reports_Order Details] d 
      ON o.OrderID = d.OrderID
   INNER JOIN Reports_Territories t 
      ON o.TerritoryID = t.TerritoryID
WHERE
   DATEPART(yy, o.OrderDate) = @Year

GROUP BY o.TerritoryID, t.TerritoryDescription
ORDER BY  t.TerritoryDescription


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO