/***
|
*** 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
|
