createview.sql

用VC编写的立体仓库的管理软件源程序和设计说明书。

USE WuHan
GO


--------------------------
	--商品库存视图--
--------------------------
IF EXISTS (SELECT TABLE_NAME 
   	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'V_GoodsStock')
DROP VIEW V_GoodsStock
GO

CREATE VIEW V_GoodsStock
AS 
SELECT	T_Stock.F_Type		AS F_Kind,
	T_Stock.F_Code  	AS F_Index,
	T_Stock.F_Name		AS F_Name,
	(SELECT F_Spec   FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Spec,
	(SELECT F_Color  FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Color,
	(SELECT F_Price  FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Price,
	(SELECT F_Weight FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Weight,
	(SELECT F_Unit	 FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Unit,
	(SELECT F_Supplier FROM T_Products WHERE T_Stock.F_Type = T_Products.F_Type)	AS F_Supplier,
	T_Stock.F_MaxNum,
	T_Stock.F_MinNum
FROM 	T_Stock

GO

--设备故障视图
IF EXISTS (SELECT TABLE_NAME 
   	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'V_DeviceFault')
DROP VIEW V_DeviceFault
GO

CREATE VIEW V_DeviceFault
AS 
SELECT	T_Device_Fault_Show.F_Remark	AS F_Remark,
	T_Device_Fault.F_Time	AS F_Time
	
FROM 	T_Device_Fault_Show,T_Device_Fault
WHERE   T_Device_Fault.F_Index = T_Device_Fault_Show.F_Index
GO

--货位信息视图
IF EXISTS (SELECT TABLE_NAME 
   	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'V_GoodsSiteInfo')
DROP VIEW V_GoodsSiteInfo
GO

CREATE VIEW V_GoodsSiteInfo
AS 
SELECT 	F_SiteIndex  AS F_Site,
       	F_TrayIndex  AS F_Tray,
	F_Index1,
	F_Index2,
	F_Index3,
	F_Index4,
	F_Index5,
	F_Index6,
	F_Index7,
	F_Index8,
	CASE (F_GoodsKind)
		WHEN 100 THEN '杂色商品'
		WHEN 101 THEN '空货箱'
		WHEN 102 THEN '单空托盘'
		WHEN 103 THEN '托盘垛'
		ELSE(SELECT F_Name FROM T_Products WHERE F_Type = F_GoodsKind)
	END 	as F_Name,
	F_GoodsCount,
	F_CustomerName,
	(SELECT F_Name FROM T_Site_State WHERE F_Index = F_SiteState) as F_State
FROM    T_Goods_Site_Manage

GO


--入库记录信息视图
IF EXISTS (SELECT TABLE_NAME 
   	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'V_InLibRecInfo')
DROP VIEW V_InLibRecInfo
GO

CREATE VIEW V_InLibRecInfo
AS 
SELECT 	F_EndPoint	AS F_SiteIndex,
       	F_TrayCode	AS F_TrayIndex,
	(SELECT F_Name FROM T_Products WHERE F_Type = F_GoodsKind)	as F_Name,
	F_Count,
	F_EndTime
FROM    T_InOutLibRec
WHERE   F_TaskKind = 1

GO


--管理任务视图
IF EXISTS (SELECT TABLE_NAME 
   	   FROM   INFORMATION_SCHEMA.VIEWS 
	   WHERE  TABLE_NAME = N'V_ManageTask')
DROP VIEW V_ManageTask
GO

CREATE VIEW V_ManageTask
AS 
SELECT 	F_ManagerIndex		AS F_Index,
	F_OrderIndex		AS F_Order,
	F_GroupIndex		AS F_Group,
	F_StartPoint		AS F_StartPoint,
	F_EndPoint		AS F_EndPoint,
	(SELECT F_Name FROM T_Task_Kind WHERE T_Task_Kind.F_Index = T_Manager_Task.F_TaskKind)	  AS F_TaskKind,
	(SELECT F_Name FROM T_Task_Level WHERE T_Task_Level.F_Index = T_Manager_Task.F_TaskLevel) AS F_TaskLevel,
	(SELECT F_Name FROM T_Task_State WHERE T_Task_State.F_Index = T_Manager_Task.F_TaskState) AS F_TaskState,
	(SELECT F_Name FROM T_Products WHERE T_Products.F_Type = T_Manager_Task.F_GoodsKind)	  AS F_GoodsKind,
	F_FinishCount		AS F_FinishCount,
	F_SortCount		AS F_Count,
	F_CustomerName		AS F_Customer,
	F_DeliverDate		AS F_DeliverDate,
	F_SendTime		AS F_SendTime,
	F_StartTime		AS F_StartTime,
	F_EndTime		AS F_EndTime
FROM 	T_Manager_Task

GO