DROP TABLE IF EXISTS "blade_ng_reason_info";
|
CREATE TABLE "blade_ng_reason_info"
|
(
|
"id" BIGINT NOT NULL,
|
"tenant_id" VARCHAR(12) DEFAULT '000000',
|
"type_id" BIGINT,
|
"reason_code" VARCHAR(24),
|
"reason_name" VARCHAR(64),
|
"create_user" BIGINT,
|
"create_dept" BIGINT,
|
"create_time" TIMESTAMP(0),
|
"update_user" BIGINT,
|
"update_time" TIMESTAMP(0),
|
"remark" VARCHAR(255),
|
"is_deleted" INT,
|
"status" INT,
|
NOT CLUSTER PRIMARY KEY("id"),
|
UNIQUE ("reason_code", "is_deleted")
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_ng_reason_info" IS 'NG原因维护信息表';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."create_dept" IS '创建单位';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."create_time" IS '创建时间';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."create_user" IS '创建人';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."id" IS 'id';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."reason_code" IS '原因编号';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."reason_name" IS '原因名称';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."remark" IS '备注';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."status" IS '业务状态';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."tenant_id" IS '租户ID';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."type_id" IS '父类型id(暂没用,现使用的是公共组表做关联)';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."update_time" IS '更新时间';
|
COMMENT
|
ON COLUMN "blade_ng_reason_info"."update_user" IS '更新人';
|
|
DROP TABLE IF EXISTS "blade_trace_flow_record";
|
CREATE TABLE "blade_trace_flow_record"
|
(
|
"id" BIGINT NOT NULL,
|
"remark" VARCHAR(255),
|
"create_user" BIGINT,
|
"create_dept" BIGINT,
|
"create_time" TIMESTAMP(0),
|
"update_user" BIGINT,
|
"update_time" TIMESTAMP(0),
|
"status" INT,
|
"is_deleted" INT,
|
"tenant_id" VARCHAR(10) DEFAULT '000000',
|
"part_no" VARCHAR(50) NOT NULL,
|
"flow_code" VARCHAR(50) NOT NULL,
|
"flow_display_name" VARCHAR(50) NOT NULL,
|
"flow_state" INT,
|
"flow_tag" INT,
|
"trace_flow_setting_id" BIGINT,
|
"workstation_id" BIGINT,
|
"workstation_code" VARCHAR(50),
|
"workstation_name" VARCHAR(50),
|
"workbench_id" BIGINT,
|
"workbench_name" VARCHAR(50),
|
"entry_time" TIMESTAMP(0),
|
"left_time" TIMESTAMP(0),
|
"extension_data" TEXT,
|
"archive_table" VARCHAR(50),
|
"position" VARCHAR(50),
|
NOT CLUSTER PRIMARY KEY("id")
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_trace_flow_record" IS '工件流程记录';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."entry_time" IS '进入时间';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."extension_data" IS '数据';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."flow_code" IS '流程编号';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."flow_display_name" IS '流程名称';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."flow_state" IS '流程状态:1 完成 2 进行中';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."flow_tag" IS '流程标识:1 质检合格 2 质检不合格';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."left_time" IS '离开时间';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."part_no" IS '工件编号';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."remark" IS '备注';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."trace_flow_setting_id" IS '流程设定ID';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."workbench_id" IS '工作台id';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."workbench_name" IS '工作台名称';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."workstation_code" IS '工位编号';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."POSITION" IS '位置';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."workstation_id" IS '工位id';
|
COMMENT
|
ON COLUMN "blade_trace_flow_record"."workstation_name" IS '工位名称';
|
|
|
DROP TABLE IF EXISTS "blade_trace_catalog";
|
CREATE TABLE "blade_trace_catalog"
|
(
|
"id" BIGINT NOT NULL,
|
"remark" VARCHAR(255),
|
"create_user" BIGINT,
|
"create_dept" BIGINT,
|
"create_time" TIMESTAMP(0),
|
"update_user" BIGINT,
|
"update_time" TIMESTAMP(0),
|
"status" INT,
|
"is_deleted" INT,
|
"tenant_id" VARCHAR(10) DEFAULT '000000',
|
"part_no" VARCHAR(50) NOT NULL,
|
"batch_number" VARCHAR(50),
|
"online_time" TIMESTAMP(0),
|
"offline_time" TIMESTAMP(0),
|
"qualified" TINYINT,
|
"is_rework_part" TINYINT,
|
"shift_index" INT,
|
"shift_index_name" VARCHAR(50),
|
"plan_id" INT,
|
"trace_status" INT,
|
"workstation_group_id" BIGINT,
|
NOT CLUSTER PRIMARY KEY("id")
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_trace_catalog" IS '工件上线记录';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."batch_number" IS '批次号';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."is_rework_part" IS '是否返工：0 否 1 是';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."offline_time" IS '下线时间';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."online_time" IS '上线时间';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."part_no" IS '工件编号';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."plan_id" IS '计划id,无计划填0';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."qualified" IS '是否合格：0 否 1 是';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."remark" IS '备注';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."shift_index" IS '班次下标';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."shift_index_name" IS '班次下标别名';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."trace_status" IS '工件转态：1 已下线 2 未下线 3 未知';
|
COMMENT
|
ON COLUMN "blade_trace_catalog"."workstation_group_id" IS '工位组id';
|
|
-- 流程维护
|
DROP TABLE IF EXISTS "blade_trace_flow_settings";
|
CREATE TABLE "blade_trace_flow_settings"
|
(
|
"id" BIGINT NOT NULL,
|
"tenant_id" VARCHAR(12),
|
"code" VARCHAR(40),
|
"name" VARCHAR(100),
|
"flow_seq" INT,
|
"station_type" INT,
|
"workstation_group_id" BIGINT,
|
"pre_flow_id" BIGINT,
|
"next_flow_id" BIGINT,
|
"flow_type" BIGINT,
|
"trigger_end_flow_style" INT,
|
"quality_result_offline" VARCHAR(32),
|
"quality_maker_flow_id" BIGINT,
|
"is_deleted" INT,
|
"create_time" TIMESTAMP(0),
|
"create_user" BIGINT,
|
"update_time" TIMESTAMP(0),
|
"update_user" BIGINT,
|
"create_dept" BIGINT,
|
"status" INT,
|
"extension_data" TEXT,
|
not CLUSTER PRIMARY KEY ("id"),
|
UNIQUE ("code", "is_deleted"),
|
CHECK ("flow_type" >= 0)
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_trace_flow_settings" IS '流程维护';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."code" IS '流程编号';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."create_dept" IS '创建部门';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."create_time" IS '创建时间';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."create_user" IS '创建人';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."extension_data" IS '数据';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."flow_seq" IS '流程顺序';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."flow_type" IS '流程类别 1-上线流程 2-途径流程 3-下线流程 4-可选流程';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."name" IS '流程名称';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."next_flow_id" IS '下一流程id';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."pre_flow_id" IS '上一流程id';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."quality_maker_flow_id" IS '质量责任归属到特定流程';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."quality_result_offline" IS '按质量结果下线 1-OK下线 2-NG下线';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."station_type" IS '工位类型 0-其他 1-打标 2-测量 3-清洗 4-装卸站 5-加工 6-搬运 7-三坐标';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."status" IS '业务状态';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."tenant_id" IS '所属租户';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."trigger_end_flow_style" IS '结束流程方式 1-仅本流程 2-仅上个流程 3-本流程和上个流程';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."update_time" IS '更新时间';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."update_user" IS '更新人';
|
COMMENT
|
ON COLUMN "blade_trace_flow_settings"."workstation_group_id" IS '工位组id';
|
|
-- 追溯关联工位
|
DROP TABLE IF EXISTS "blade_trace_related_workstation";
|
CREATE TABLE "blade_trace_related_workstation"
|
(
|
"id" BIGINT NOT NULL,
|
"workstation_id" BIGINT NOT NULL,
|
"workbench_id" BIGINT NOT NULL,
|
"trace_flow_setting_id" BIGINT NOT NULL,
|
"machining_position" VARCHAR(255),
|
NOT CLUSTER PRIMARY KEY("id")
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_trace_related_workstation" IS '追溯关联工位';
|
COMMENT
|
ON COLUMN "blade_trace_related_workstation"."machining_position" IS '加工位置';
|
COMMENT
|
ON COLUMN "blade_trace_related_workstation"."trace_flow_setting_id" IS '流程设定ID';
|
COMMENT
|
ON COLUMN "blade_trace_related_workstation"."workbench_id" IS '工作台id';
|
COMMENT
|
ON COLUMN "blade_trace_related_workstation"."workstation_id" IS '工位id';
|
|
-- 工件追溯NG表
|
DROP TABLE IF EXISTS "blade_trace_record_ng_reason";
|
CREATE TABLE "blade_trace_record_ng_reason"
|
(
|
"id" BIGINT NOT NULL,
|
"record_id" BIGINT,
|
"bad_part_id" BIGINT,
|
"bad_part" VARCHAR(32),
|
"reason_id" BIGINT,
|
"reason_code" VARCHAR(24),
|
"reason_name" VARCHAR(64),
|
"create_user_name" VARCHAR(255),
|
"tenant_id" VARCHAR(12) DEFAULT '000000',
|
"status" TINYINT DEFAULT 1,
|
"is_deleted" DECIMAL(20, 0),
|
"create_user" BIGINT,
|
"create_dept" BIGINT,
|
"create_time" TIMESTAMP(0),
|
"update_user" BIGINT,
|
"update_time" TIMESTAMP(0),
|
NOT CLUSTER PRIMARY KEY("id"),
|
CHECK ("is_deleted" >= 0)
|
) STORAGE(ON "MAIN", CLUSTERBTR);
|
|
COMMENT
|
ON TABLE "blade_trace_record_ng_reason" IS '工件流程NG原因表';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."bad_part" is '不良部位';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."bad_part_id" IS '不良部位id';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."create_dept" IS '创建部门';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."create_time" IS '创建时间';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."create_user" IS '创建人';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."create_user_name" IS '创建用户名称';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."is_deleted" IS '删除标记：0-未删除，1-已删除';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."reason_code" IS '原因编号';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."reason_id" IS '原因id';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."reason_name" IS '原因名称';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."record_id" IS '工件流程记录id';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."status" IS '业务状态 1激活 0 冻结';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."tenant_id" IS '租户ID';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."update_time" IS '更新时间';
|
COMMENT
|
ON COLUMN "blade_trace_record_ng_reason"."update_user" IS '更新人';
|
|
|
|
INSERT INTO blade_menu(id, parent_id, code, name, alias, path, source, sort,
|
category, action, is_open, remark, is_deleted, belong_application, keep_alive)
|
VALUES (1518863601934606338, 0, 'trace', '工件追溯', 'trace', '/trace', 'iconfont saber-zhushuju', 8, 0, 0, 1, '', 0,
|
'CPS',
|
1),
|
(1518864188126339074, 1518863601934606338, 'trace_query', '追溯查询', 'trace_query', '/trace/query/index',
|
'iconfont iconicon_search', 1, 1, 0, 1, '', 0, 'CPS', 1),
|
(1519183072176939010, 1518863601934606338, 'processMaintenance', '流程维护', 'processMaintenance',
|
'/trace/maintain/index', 'iconfont saber-caijifenxi', 3, 1, 0, 1, '', 0, 'CPS', 1),
|
(1522377449602215938, 1518863601934606338, 'ng', 'NG原因维护', 'ng', '/trace/ng-info/index',
|
'iconfont iconicon_setting', 4, 1, 0, 1, '', 0, 'CPS', 1),
|
(1523465621521539073, 1518863601934606338, 'ng_trace', 'NG工件追溯', 'ng_trace', '/trace/ng-trace/index',
|
'iconfont iconicon_ding', 99, 1, 0, 1, '', 0, 'CPS', 1);
|
|
|
CREATE UNIQUE INDEX default_index ON blade_trace_catalog (part_no, batch_number, workstation_group_id, online_time,
|
offline_time);
|
|
CREATE INDEX time_index ON blade_trace_catalog (online_time, offline_time, part_no, batch_number);
|
|
CREATE INDEX record_default_index ON blade_trace_flow_record (part_no, workstation_id, entry_time);
|
|
CREATE INDEX record_flag_index ON blade_trace_flow_record (flow_tag, part_no, workstation_id);
|
|
CREATE INDEX ng_reason_default_index ON blade_trace_record_ng_reason (record_id);
|
