0830_DB_Modify

一、添加字段

1. 表MachineBreak，字段[OperatorId] [int] NOT NULL
2. 表Machine，字段[OperatorId] [int] NOT NULL
3. 表EquipmentFaultRepair，字段[OperatorId] [int] NOT NULL
   注意：VS对应添加

二、 创建视图

视图名：View_MachineOperationRecords
语句：

create view View_MachineOperationRecords as
(
select dbo.MachineBreak.MachineId as MachineId,'停机(故障)' as Operation, 
dbo.MachineBreak.StartDate as OperateDatetime ,dbo.MachineBreak.[OperatorId] as [OperatorId],dbo.MachineBreak.Comments as Comments
from dbo.MachineBreak
union
select dbo.MachineBreak.MachineId as MachineId,'启用' as Operation, 
dbo.MachineBreak.EndDate as OperateDatetime ,dbo.MachineBreak.[OperatorId] as [OperatorId],'' as Comments
from dbo.MachineBreak where dbo.MachineBreak.EndDate is not null
union
select dbo.[EquipmentFaultReport].EquipmentId as MachineId,'报修' as Operation, 
dbo.[EquipmentFaultReport].ReportDateTime as OperateDatetime,dbo.[EquipmentFaultReport].UserId as [OperatorId],dbo.[EquipmentFaultReport].FaultDescription as Comments
from dbo.[EquipmentFaultReport]
union
select dbo.[EquipmentFaultReport].EquipmentId as MachineId,'维修' as Operation, 
dbo.[EquipmentFaultReport].HandleDateTime as OperateDatetime,dbo.[EquipmentFaultReport].ReportHandlerId as [OperatorId],'' as Comments
from dbo.[EquipmentFaultReport] where dbo.[EquipmentFaultReport].HandleDateTime is not null
union
select dbo.MachineSterilization.MachineId as MachineId,'消毒' as Operation, 
dbo.MachineSterilization.OperateDatetime as OperateDatetime,dbo.MachineSterilization.[OperatorId] as [OperatorId],dbo.MachineSterilization.Comments as Comments
from dbo.MachineSterilization
union
select dbo.Machine.Id as MachineId,'报废' as Operation, 
dbo.Machine.ObsoleteDate as OperateDatetime,dbo.Machine.[OperatorId] as [OperatorId],'' as Comments
from dbo.Machine where dbo.Machine.ObsoleteDate is not null
union
select dbo.[EquipmentFaultRepair].EquipmentId as MachineId,'提交维修结果' as Operation, 
dbo.[EquipmentFaultRepair].RepairDateTime as OperateDatetime,dbo.[EquipmentFaultRepair].[OperatorId] as [OperatorId],dbo.[EquipmentFaultRepair].MaintenanceItem as Comments
from dbo.[EquipmentFaultRepair]
)