/***
 *** Description：托盘物料信息视图
 *** Author:shaocx
 *** Date:2021-02-23
 ***/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='ContainerVItemView') 
   DROP VIEW ContainerVItemView 
GO 
CREATE VIEW ContainerVItemView
AS

SELECT  
   ivc.ID,
   ivc.CONTAINERID,
   ivc.ITEMID,
   ivc.UPDATETIME,
   c.CONTAINERNO,
   c.CONTAINERNAME,
   item.ITEMNO,
   item.TYPE,
   item.CREATEPERSONID,
   item.PartId,
   item.StoreId,
   item.partName,
   item.mouldId
FROM      dbo.[ITEM_VS_CONTAINER] AS ivc 
INNER JOIN
                   dbo.[ITEM] AS item ON ivc.ITEMID = item.ID INNER JOIN
                   dbo.[CONTAINER] AS c ON ivc.CONTAINERID = c.id


GO


/***
 *** Description：修改出入库视图
 *** Author:shaocx
 *** Date:2021-02-23
 ***/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='TASK_ITEM_VIEW') 
   DROP VIEW TASK_ITEM_VIEW 
GO 
CREATE VIEW TASK_ITEM_VIEW
AS

 SELECT   dbo.CONTAINER.CONTAINERNO,dbo.TASK.LastModifier,dbo.TASK.DODEVICEID, dbo.TASK.Weight, dbo.TASK.OperatorUser, dbo.TASK.ID, dbo.TASK.TASKTYPE, 
                dbo.ITEM.ITEMNO, dbo.ITEM.TYPE, dbo.TASK.CreateTime, dbo.TASK.UPDATETIME, dbo.TASK.FINISHTIME, 
                PLACE_1.PLACENO AS SOURCEPLACENO, dbo.PLACE.PLACENO AS TOPLACENO, dbo.TASK.StoreNo, 
                PLACE_1.STORENO AS Expr1, dbo.TASK.RELEASESTATUS, 
                CASE dbo.TASK.RELEASESTATUS WHEN 1 THEN '已下发' ELSE '未下发' END AS RELEASESTATUS_Text, 
                dbo.TASK.HADFINISHED, 
                CASE dbo.TASK.HADFINISHED WHEN 1 THEN '已完成' ELSE '未完成' END AS HADFINISHED_Text,
				dbo.TASK.DelFlag,
				CASE dbo.TASK.DelFlag WHEN '1' THEN '已删除' ELSE '' END AS DelFlag_Text
FROM      dbo.TASK 
               LEFT JOIN dbo.CONTAINER ON dbo.CONTAINER.ID = dbo.TASK.CONTAINERID 
               INNER JOIN  dbo.ITEM_VS_CONTAINER INNER JOIN
                dbo.ITEM ON dbo.ITEM_VS_CONTAINER.ITEMID = dbo.ITEM.ID ON 
                dbo.CONTAINER.ID = dbo.ITEM_VS_CONTAINER.CONTAINERID INNER JOIN
                dbo.PLACE ON dbo.TASK.TOPLACEID = dbo.PLACE.ID INNER JOIN
                dbo.PLACE AS PLACE_1 ON dbo.TASK.SOURCEPLACEID = PLACE_1.ID
WHERE   (dbo.TASK.DODEVICEID IN (1, 2)) -- 只显示堆垛机任务
				
GO



/***
 *** Description：修改库存视图
 *** Author:shaocx
 *** Date:2021-02-23
 ***/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='STORE') 
   DROP VIEW STORE 
GO 
CREATE VIEW STORE
AS
SELECT DISTINCT dbo.CONTAINER_VS_PLACE.Void,
                dbo.ITEM_VS_CONTAINER.Weight, dbo.PLACE.PLACENO, dbo.PLACE.STORENO, dbo.PLACE.ISFULL, 
                dbo.PLACE.ISLOCK, dbo.ITEM.ITEMNO, dbo.CONTAINER.CONTAINERNO, dbo.PLACE.ID AS PLACEID, 
                dbo.CONTAINER.ID AS CONTAINERID, dbo.ITEM.ID AS ITEMID, dbo.ITEM.TYPE, dbo.ITEM.PartId, dbo.ITEM.mouldId, 
                dbo.ITEM.partName
FROM      dbo.ITEM INNER JOIN
                dbo.ITEM_VS_CONTAINER ON dbo.ITEM.ID = dbo.ITEM_VS_CONTAINER.ITEMID RIGHT OUTER JOIN
                dbo.CONTAINER INNER JOIN
                dbo.CONTAINER_VS_PLACE ON dbo.CONTAINER.ID = dbo.CONTAINER_VS_PLACE.CONTAINERID ON 
                dbo.ITEM_VS_CONTAINER.CONTAINERID = dbo.CONTAINER.ID RIGHT OUTER JOIN
                dbo.PLACE ON dbo.CONTAINER_VS_PLACE.PLACEID = dbo.PLACE.ID
GO

/***
 *** Description：修改权限 视图
 *** Author:shaocx
 *** Date:2021-02-23
 ***/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='Role_VS_Permission_View') 
   DROP VIEW Role_VS_Permission_View
GO 
CREATE VIEW Role_VS_Permission_View
AS
SELECT lang.ENGLISH as PERMISSIONNAME_English,
ROLE_VS_PERMISSION.ID, dbo.PERMISSION.PERMISSIONNAME, dbo.ROLE.ROLENAME, 
                dbo.PERMISSION.ID AS PERMISSIONID, dbo.ROLE.ID AS ROLEID, dbo.ROLE.ROLENAMEENG, 
                dbo.PERMISSION.PERMISSIONNAMEENG
FROM      dbo.ROLE_VS_PERMISSION INNER JOIN
                dbo.ROLE ON dbo.ROLE_VS_PERMISSION.ROLEID = dbo.ROLE.ID INNER JOIN
                dbo.PERMISSION ON dbo.ROLE_VS_PERMISSION.PERMISSIONID = dbo.PERMISSION.ID
				LEFT JOIN dbo.[LANGUAGE] as lang ON lang.NAME=dbo.PERMISSION.PERMISSIONNAME
GO