|
|
|
|
ALTER TABLE EquipmentBaseInfo ADD CuttingFluidChangeTime Datetime2 NUll --增加设备切削液更换时间【Editby baotian,2023-3-9】
|
ALTER TABLE EquipmentBaseInfo ADD CuttingFluidChangeAlertThreshold int NUll --增加设备切削液预警阈值【Editby baotian,2023-3-9】
|
|
|
ALTER TABLE EquipmentBaseInfo ADD EquipmentCurrentMonitor bit NUll --增加是否关闭设备告警【Editby baotian,2023-3-10】
|
|
|
ALTER TABLE WorkPieceInfo ADD WorkPieceLastOfflineTime datetime NUll --增加工件完工时间【Editby baotian,2023-3-10】
|
|
|
|
--20231024
|
ALTER TABLE WorkPieceProcess ADD OperationType nvarchar(50);
|
ALTER TABLE WorkPieceProcess ADD Remarks nvarchar(255);
|
|
ALTER TABLE QualityDataInfo ADD OP10QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP20QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP30QualityStateCH3 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP30QualityStateCH4 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP30QualityStateCH5 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP30QualityStateCH6 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP30QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP40QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP60QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP80QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP10QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfo ADD OP20QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfo ADD OP30QualityReceiveTimeCH3 DateTime;
|
ALTER TABLE QualityDataInfo ADD OP30QualityReceiveTimeCH4 DateTime;
|
ALTER TABLE QualityDataInfo ADD OP30QualityReceiveTimeCH5 DateTime;
|
ALTER TABLE QualityDataInfo ADD OP30QualityReceiveTimeCH6 DateTime;
|
ALTER TABLE QualityDataInfo ADD OP30QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfo ADD OP40QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfo ADD OP60QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfo ADD OP80QualityReceiveTime DateTime;
|
|
|
ALTER TABLE QualityDataInfoLog ADD OP10QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP20QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityStateCH3 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityStateCH4 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityStateCH5 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityStateCH6 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP40QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP60QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP80QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP10QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP20QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityReceiveTimeCH3 DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityReceiveTimeCH4 DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityReceiveTimeCH5 DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityReceiveTimeCH6 DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP40QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP60QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP80QualityReceiveTime DateTime;
|
|
ALTER TABLE QualityDataInfo ADD OP35QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD OP35QualityReceiveTime DateTime;
|
ALTER TABLE QualityDataInfoLog ADD OP35QualityState nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD OP35QualityReceiveTime DateTime;
|
--20231029
|
|
ALTER TABLE EquipmentCurrentMonitor ADD WarnType nvarchar(32) NUll --增加告警代码【Editby baotian,2023-10-29】
|
|
ALTER TABLE WorkPieceProcess ALTER COLUMN QualityDataInfoID bigint;
|
ALTER TABLE WorkPieceInfoLog ADD LogAddTime DateTime;
|
ALTER TABLE WorkPieceInfoLog ADD LogAddRemark nvarchar(255) NUll
|
ALTER TABLE WorkPieceInfoLog ADD CompleteTime DateTime
|
|
|
--20231101
|
|
ALTER TABLE EquipmentCurrentMonitor ADD WarnTime datetime2;--新增告警时间 message时间
|
ALTER TABLE EquipmentCurrentMonitor ADD Warnmsg nvarchar(max);--新增告警内容 message
|
ALTER TABLE EquipmentWorkingLog ADD WarnType nvarchar(max);--新增告警类型 message时间
|
ALTER TABLE EquipmentWorkingLog ADD Warnmsg nvarchar(max);--新增告警内容 message
|
ALTER TABLE EquipmentWorkingLog ADD UpdateDesc nvarchar(255);--新增变更描述
|
|
ALTER TABLE EquipmentWorkingLog ADD WarnStartTime datetime2;--新增告警时间 message时间
|
ALTER TABLE EquipmentWorkingLog ADD WarnEndTime datetime2;--新增告警时间 message时间
|
|
ALTER TABLE EquipmentWorkingLog ADD EquipmentState nvarchar(32);
|
ALTER TABLE EquipmentWorkingLog ADD EquipmentStateStartTime datetime2;--新增状态开始时间 message时间
|
ALTER TABLE EquipmentWorkingLog ADD EquipmentStateEndTime datetime2;--新增状态结束时间 message时间
|
|
ALTER TABLE WorkPieceInfo ADD WorkingProcedureEndTime datetime2;--新增g工序结束时间
|
ALTER TABLE WorkPieceInfoLog ADD WorkingProcedureEndTime datetime2;--新增工序结束时间
|
--WorkPieceInfoLog表相关字段全部可空
|
|
|
|
USE [iWare.SCADA]
|
GO
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EquipmentFailureCode]') AND type in (N'U')) DROP TABLE [dbo].[EquipmentFailureCode];
|
CREATE TABLE [dbo].[EquipmentFailureCode](
|
[Id] [bigint] NOT NULL,
|
EquipmentID VARCHAR(255),
|
WorkingProcedure VARCHAR(255),
|
Code VARCHAR(255),
|
CodeType VARCHAR(255),
|
CodeLevel VARCHAR(255),
|
CodeDesc VARCHAR(255),
|
[Remarks] [nvarchar](255) NULL,
|
[CreatedTime] [datetimeoffset](7) NULL,
|
[UpdatedTime] [datetimeoffset](7) NULL,
|
[CreatedUserId] [bigint] NULL,
|
[CreatedUserName] [nvarchar](50) NULL,
|
[UpdatedUserId] [bigint] NULL,
|
[UpdatedUserName] [nvarchar](50) NULL,
|
[IsDeleted] [bit] NOT NULL
|
);
|
|
EXEC sp_addextendedproperty 'MS_Description', '告警故障代码表;', 'SCHEMA', dbo, 'table', EquipmentFailureCode, null, null;
|
EXEC sp_addextendedproperty 'MS_Description', '设备代号', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', EquipmentID;
|
EXEC sp_addextendedproperty 'MS_Description', '设备所属工序', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', WorkingProcedure;
|
EXEC sp_addextendedproperty 'MS_Description', '代号', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', Code;
|
EXEC sp_addextendedproperty 'MS_Description', '代号类型（1：故障 2：告警）', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', CodeType;
|
EXEC sp_addextendedproperty 'MS_Description', '代号级别（1,2,3级）', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', CodeLevel;
|
EXEC sp_addextendedproperty 'MS_Description', '代号描述', 'SCHEMA', dbo, 'table', EquipmentFailureCode, 'column', CodeDesc;
|
|
|
|
--20231109
|
alter table WorkPieceInfo ADD OP80NewCode nvarchar(255) --OP80重新打印二维码
|
alter table WorkPieceInfoLog ADD OP80NewCode nvarchar(255) --OP80重新打印二维码
|
|
--20231114
|
ALTER TABLE EquipmentCurrentMonitor ALTER COLUMN Alertmsg nvarchar(max);
|
ALTER TABLE EquipmentWorkingLog ALTER COLUMN FailureMsg nvarchar(max);
|
|
--20231121
|
ALTER TABLE QualityDataInfo ADD OP30QualityFilePathCH3 nvarchar(900);
|
ALTER TABLE QualityDataInfo ADD OP30QualityFilePathCH4 nvarchar(900);
|
ALTER TABLE QualityDataInfo ADD OP30QualityFilePathCH5 nvarchar(900);
|
ALTER TABLE QualityDataInfo ADD OP30QualityFilePathCH6 nvarchar(900);
|
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityFilePathCH3 nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityFilePathCH4 nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityFilePathCH5 nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP30QualityFilePathCH6 nvarchar(900);
|
|
--20231123
|
ALTER TABLE KnifeToolBaseInfo ADD KnifeToolChangeAlertThreshold int; --刀具预警阈值
|
|
|
--20231205
|
|
ALTER TABLE v_get_equipment_alert ADD DetergentDesc nvarchar(900);
|
ALTER TABLE v_get_equipment_alert ADD KnifeToolDesc nvarchar(900);
|
ALTER TABLE v_get_equipment_alert ADD CuttingFluidDesc nvarchar(900);
|
ALTER TABLE v_get_equipment_alert ADD AlertDesc nvarchar(max);
|
|
--20231206
|
ALTER TABLE QualityDataInfo ADD QualityOP40To1 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD QualityOP40To1 nvarchar(32);
|
--20231207
|
ALTER TABLE EquipmentBaseInfo ADD TactTime int; --生产节拍
|
|
--20231216
|
|
ALTER TABLE QualityDataInfo ADD QualityOP80To10 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD QualityOP80To10 nvarchar(32);
|
|
|
ALTER TABLE QualityDataInfo ADD QualityOP30To8 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD QualityOP30To9 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD QualityOP30To10 nvarchar(32);
|
ALTER TABLE QualityDataInfo ADD QualityOP30To11 nvarchar(32);
|
|
ALTER TABLE QualityDataInfoLog ADD QualityOP30To8 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD QualityOP30To9 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD QualityOP30To10 nvarchar(32);
|
ALTER TABLE QualityDataInfoLog ADD QualityOP30To11 nvarchar(32);
|
|
|
ALTER TABLE KnifeToolEquipmentUpdateLog ADD OperateType nvarchar(32);
|
|
|
ALTER TABLE QualityDataInfo ADD OP80QualityFilePath nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP80QualityFilePath nvarchar(900);
|
ALTER TABLE QualityDataInfo ADD OP60QualityFilePath nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP60QualityFilePath nvarchar(900);
|
ALTER TABLE QualityDataInfo ADD OP20QualityFilePath nvarchar(900);
|
ALTER TABLE QualityDataInfoLog ADD OP20QualityFilePath nvarchar(900);
|
|
|
|
ALTER TABLE EquipmentBaseInfo Add DetergentAddtime datetime, -- 清洗液添加时间 【Editby shaocx,2024-06-11】
|
CuttingFluidAddTime datetime -- 切削液添加时间 【Editby shaocx,2024-06-11】
|
|
|
|
--新增OP80的一些质量数据 【Editby shaocx,2024-06-13】
|
ALTER TABLE QualityDataInfo Add QualityOP80_Houdu nvarchar(15), -- OP80 质量数据-厚度
|
QualityOP80_ZXJ nvarchar(15),-- OP80 质量数据-中心距
|
QualityOP80_DTKYZD nvarchar(15),-- OP80 质量数据-大头孔圆柱度
|
QualityOP80_XTSMYD nvarchar(15),-- OP80 质量数据-小头上面圆度
|
QualityOP80_XTXMYD nvarchar(15),-- OP80 质量数据-小头下面圆度
|
|
QualityOP80_D_TKZJ nvarchar(15),-- OP80 质量数据-大头孔直径,大头孔直径=（大头上面X方向直径+大头上面Y方向直径+大头下面X方向直径+大头下面Y方向直径）/4
|
QualityOP80_D_S_X nvarchar(15),-- OP80 质量数据-大头上面X方向直径
|
QualityOP80_D_S_Y nvarchar(15),-- OP80 质量数据-大头上面Y方向直径
|
QualityOP80_D_X_X nvarchar(15),-- OP80 质量数据-大头下面X方向直径
|
QualityOP80_D_X_Y nvarchar(15),-- OP80 质量数据-大头下面Y方向直径
|
|
|
QualityOP80_X_TKZJ nvarchar(15),-- OP80 质量数据-小头孔直径,小头孔直径=（小头上面X方向直径+小头上面Y方向直径+小头下面X方向直径+小头下面Y方向直径）/4
|
QualityOP80_X_S_X nvarchar(15),-- OP80 质量数据-小头上面X方向直径
|
QualityOP80_X_S_Y nvarchar(15),-- OP80 质量数据-小头上面Y方向直径
|
QualityOP80_X_X_X nvarchar(15),-- OP80 质量数据-小头下面X方向直径
|
QualityOP80_X_X_Y nvarchar(15) -- OP80 质量数据-小头下面Y方向直径
|
|
ALTER TABLE QualityDataInfoLog Add QualityOP80_Houdu nvarchar(15), -- OP80 质量数据-厚度
|
QualityOP80_ZXJ nvarchar(15),-- OP80 质量数据-中心距
|
QualityOP80_DTKYZD nvarchar(15),-- OP80 质量数据-大头孔圆柱度
|
QualityOP80_XTSMYD nvarchar(15),-- OP80 质量数据-小头上面圆度
|
QualityOP80_XTXMYD nvarchar(15),-- OP80 质量数据-小头下面圆度
|
|
QualityOP80_D_TKZJ nvarchar(15),-- OP80 质量数据-大头孔直径,大头孔直径=（大头上面X方向直径+大头上面Y方向直径+大头下面X方向直径+大头下面Y方向直径）/4
|
QualityOP80_D_S_X nvarchar(15),-- OP80 质量数据-大头上面X方向直径
|
QualityOP80_D_S_Y nvarchar(15),-- OP80 质量数据-大头上面Y方向直径
|
QualityOP80_D_X_X nvarchar(15),-- OP80 质量数据-大头下面X方向直径
|
QualityOP80_D_X_Y nvarchar(15),-- OP80 质量数据-大头下面Y方向直径
|
|
|
QualityOP80_X_TKZJ nvarchar(15),-- OP80 质量数据-小头孔直径,小头孔直径=（小头上面X方向直径+小头上面Y方向直径+小头下面X方向直径+小头下面Y方向直径）/4
|
QualityOP80_X_S_X nvarchar(15),-- OP80 质量数据-小头上面X方向直径
|
QualityOP80_X_S_Y nvarchar(15),-- OP80 质量数据-小头上面Y方向直径
|
QualityOP80_X_X_X nvarchar(15),-- OP80 质量数据-小头下面X方向直径
|
QualityOP80_X_X_Y nvarchar(15) -- OP80 质量数据-小头下面Y方向直径
|
|
|
|
--新增获取QC数据标记 【Editby shaocx,2024-06-13】
|
ALTER TABLE WorkPieceProcess Add GetQcDataFlag int, -- 获取QC数据标记,0表示待获取，1表示获取成功，2表示获取失败 【Editby shaocx,2024-06-15】
|
GetQcDataCount int,-- 获取QC数据标记次数 【Editby shaocx,2024-06-15】
|
GetQcDataFlag_Remark nvarchar(100) -- 获取QC数据标记描述 【Editby shaocx,2024-06-15】
|
|
|
|
|
alter table WorkPieceInfo ADD OutTime datetime, -- 出库时间
|
OutPerson nvarchar(255), -- 出库人
|
OutRemark nvarchar(255), -- 出库备注
|
IsOut bit -- 是否出库
|
|
|
|
|
alter table WorkPieceInfo ADD QualityNoOk int, -- 质量不合格原因类型
|
QualityNoOkReason nvarchar(255), -- 质量不合格原因
|
|
alter table WorkPieceInfoLog ADD QualityNoOk int, -- 质量不合格原因类型
|
QualityNoOkReason nvarchar(255), -- 质量不合格原因
|
|
alter table WorkPieceLog ADD QualityNoOk int, -- 质量不合格原因类型
|
QualityNoOkReason nvarchar(255), -- 质量不合格原因
|
|
alter table WorkPieceProcess ADD QualityNoOk int, -- 质量不合格原因类型
|
QualityNoOkReason nvarchar(255), -- 质量不合格原因
|
|
|
|
-- QualityDataInfo
|
|
alter table QualityDataInfo Add QualityNoOk_OP10 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP10 nvarchar(255)
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP20 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP20 nvarchar(255)
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP30 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP30 nvarchar(255)
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP35 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP35 nvarchar(255)
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP40 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP40 nvarchar(255)
|
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP60 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP60 nvarchar(255)
|
|
|
alter table QualityDataInfo Add QualityNoOk_OP80 int
|
|
|
alter table QualityDataInfo Add QualityNoOkReason_OP80 nvarchar(255)
|
|
|
-- QualityDataInfoLog
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP10 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP10 nvarchar(255)
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP20 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP20 nvarchar(255)
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP30 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP30 nvarchar(255)
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP35 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP35 nvarchar(255)
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP40 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP40 nvarchar(255)
|
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP60 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP60 nvarchar(255)
|
|
|
alter table QualityDataInfoLog Add QualityNoOk_OP80 int
|
|
|
alter table QualityDataInfoLog Add QualityNoOkReason_OP80 nvarchar(255)
|
|
|
|
|
|
|
alter table WorkPieceOutbound Add CarNo nvarchar(255) -- 小车码
|
alter table WorkPieceOutbound Add UnLineStage int
|
alter table WorkPieceOutbound Add UnLineStageName nvarchar(255)
|
alter table WorkPieceOutbound Add WorkPieceUnLineTime datetime
|
alter table WorkPieceOutbound Add WorkPieceUnLineUserId bigint
|
alter table WorkPieceOutbound Add WorkPieceUnLineUserName nvarchar(255)
|
|
|
-- 添加唯一索引 [Editby liuwq,2024-07-20]
|
IF EXISTS(select 1 from sysindexes where id=object_id('WorkPieceOutbound ') and name='idx_WorkPieceOutbound')
|
DROP INDEX idx_WorkPieceOutbound ON WorkPieceOutbound
|
CREATE UNIQUE INDEX
|
idx_WorkPieceOutbound ON WorkPieceOutbound (OP80NewCode)
|
GO
|
|
|
|
alter table WorkPieceInfo Add UnLineStage int
|
alter table WorkPieceInfo Add UnLineStageName nvarchar(255)
|
alter table WorkPieceInfo Add WorkPieceUnLineTime datetime
|
alter table WorkPieceInfo Add WorkPieceUnLineUserId bigint
|
alter table WorkPieceInfo Add WorkPieceUnLineUserName nvarchar(255)
|
|
|
-- 增加 对创建人、修改人的解释 [Editby shaocx,2024-08-31]
|
alter table WorkPieceLog Add DataCapturePointCname nvarchar(255)
|
alter table WorkPieceLog Add UpdateDataCapturePointCname nvarchar(255)
|
|
|
alter table WorkPieceInfo Add DataCapturePointCname nvarchar(255)
|
alter table WorkPieceInfo Add UpdateDataCapturePointCname nvarchar(255)
|
|
|
alter table WorkPieceProcess Add DataCapturePointCname nvarchar(255)
|
alter table WorkPieceProcess Add UpdateDataCapturePointCname nvarchar(255)
|
|
alter table WorkPieceProcess Add MyRemarks nvarchar(255) -- 自我描述的备注
|
|
|
alter table AccessInterfaceLog Add WorkPieceID nvarchar(255)
|
alter table AccessInterfaceLog Add WorkingProcedureCurrent nvarchar(255)
|
|
|
|
alter table WorkPieceLog Add MyRemarks nvarchar(255) -- 自我描述的备注
|
|
alter table WorkPieceLog Add PieceLogType int
|
alter table WorkPieceLog Add PieceLogTypeName nvarchar(255)
|
|
|
alter table AccessInterfaceLog Add QualityState int
|
alter table AccessInterfaceLog Add QualityStateName nvarchar(255)
|
