lacemi/data/upgrade/v4.0.0/v4.0.0.sql

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `t_affair_detail`;
CREATE TABLE `t_affair_detail`  (
`detail_id` int(11) NOT NULL AUTO_INCREMENT,
`detail_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`affair_id` int(11) NOT NULL,
`column_id` int(11) NOT NULL,
`is_manual` int(1) NULL DEFAULT 0,
`is_system` int(1) NULL DEFAULT 0,
PRIMARY KEY (`detail_id`) USING BTREE,
INDEX `affair_id`(`affair_id`) USING BTREE,
CONSTRAINT `t_affair_detail_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 33 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_detail_calculation
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_detail_calculation`;
CREATE TABLE `t_affair_detail_calculation`  (
`calculation_id` int(11) NOT NULL AUTO_INCREMENT,
`detail_sub_id` int(11) NOT NULL,
`calculation_formula` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`calculation_formula_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`calculation_result` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`calculation_result_title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`calculation_result_accuracy` tinyint(1) NULL DEFAULT 0,
`all_algebras` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`calculation_steps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
PRIMARY KEY (`calculation_id`) USING BTREE,
INDEX `detail_sub_id`(`detail_sub_id`) USING BTREE,
CONSTRAINT `t_affair_detail_calculation_ibfk_2` FOREIGN KEY (`detail_sub_id`) REFERENCES `t_affair_detail_sub` (`detail_sub_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 163 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_detail_sub
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_detail_sub`;
CREATE TABLE `t_affair_detail_sub`  (
`detail_sub_id` int(11) NOT NULL AUTO_INCREMENT,
`detail_sub_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`detail_id` int(11) NOT NULL,
`detail_sub_column_names` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`result_column_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`result_column_unit` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`detail_sub_order` int(11) NULL DEFAULT 0,
`is_horizontal` tinyint(1) NULL DEFAULT 0,
`subscript_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`detail_sub_id`) USING BTREE,
INDEX `detail_id`(`detail_id`) USING BTREE,
CONSTRAINT `t_affair_detail_sub_ibfk_1` FOREIGN KEY (`detail_id`) REFERENCES `t_affair_detail` (`detail_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 44 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_detail_system_column
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_detail_system_column`;
CREATE TABLE `t_affair_detail_system_column`  (
`column_id` int(11) NOT NULL AUTO_INCREMENT,
`column_pid` int(11) NULL DEFAULT 0,
`group_id` int(11) NULL DEFAULT NULL,
`column_order` int(11) NULL DEFAULT NULL,
`column_title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`column_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`column_unit` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '[]',
`column_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`column_control` tinyint(1) NULL DEFAULT 0,
`column_rule` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`column_is_virtual` tinyint(1) NULL DEFAULT 0,
`column_unit_symbiosis` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`column_detachable` tinyint(1) NULL DEFAULT 0,
`column_branch_calculation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`status` tinyint(2) NULL DEFAULT NULL,
PRIMARY KEY (`column_id`, `column_name`) USING BTREE,
UNIQUE INDEX `column_name`(`column_name`) USING BTREE,
INDEX `group_id`(`group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 618 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_detail_system_column_group
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_detail_system_column_group`;
CREATE TABLE `t_affair_detail_system_column_group`  (
`group_id` int(11) NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
PRIMARY KEY (`group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci  ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `t_inventory_detail_calculation`;
CREATE TABLE `t_inventory_detail_calculation`  (
`detail_calculation_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`data_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`warehouse_type` int(11) NOT NULL,
`common_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`detail_calculation_key` int(11) NOT NULL,
`detail_calculation_time` datetime(0) NOT NULL,
`detail_calculation_value` decimal(22, 6) NULL DEFAULT NULL,
`detail_calculation_note` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`user_id` int(11) NULL DEFAULT NULL,
`status` int(2) NULL DEFAULT 99,
`ctime` datetime(0) NULL DEFAULT NULL,
`call_time` datetime(0) NOT NULL,
PRIMARY KEY (`detail_calculation_id`) USING BTREE,
INDEX `inventory`(`detail_calculation_key`, `common_id`, `detail_calculation_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_detail_record
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_detail_record`;
CREATE TABLE `t_inventory_detail_record`  (
`detail_record_id` int(11) NOT NULL AUTO_INCREMENT,
`inventory_id` int(11) NOT NULL,
`detail_data_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`detail_record_time` datetime(0) NOT NULL,
`detail_record_value` decimal(22, 6) NOT NULL,
`detail_record_type` tinyint(1) NOT NULL DEFAULT 0,
`detail_diff_value` decimal(22, 6) NULL DEFAULT NULL,
`transfer_in_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`user_id` int(11) NULL DEFAULT NULL,
`status` int(2) NULL DEFAULT 99,
`ctime` datetime(0) NULL DEFAULT NULL,
`utime` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`detail_record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 113 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_detail_sub_record
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_detail_sub_record`;
CREATE TABLE `t_inventory_detail_sub_record`  (
`detail_sub_record_id` int(11) NOT NULL AUTO_INCREMENT,
`detail_data_id` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`detail_sub_qrcode_id` int(11) NOT NULL,
`detail_sub_record_time` datetime(0) NOT NULL,
`detail_sub_record_value` decimal(22, 6) NOT NULL,
`detail_sub_record_type` tinyint(1) NOT NULL DEFAULT 0,
`detail_sub_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
`notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`user_id` int(11) NULL DEFAULT NULL,
`status` int(2) NULL DEFAULT 99,
`ctime` datetime(0) NULL DEFAULT NULL,
`utime` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`detail_sub_record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_fast
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_fast`;
CREATE TABLE `t_inventory_fast`  (
`inventory_fast_id` int(11) NOT NULL AUTO_INCREMENT,
`affair_id` int(11) NOT NULL,
`inventory_fast_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`inventory_fast_data_ids` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`inventory_fast_content` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`notice_affair_id` int(11) NULL DEFAULT NULL,
`from_affair_id` int(11) NULL DEFAULT NULL,
`from_affair_data_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`requirement_quantity` decimal(22, 6) NULL DEFAULT NULL,
`requirement_quantity_unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
`requirement_note` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`requirement_voice` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`synchronous_quantity` decimal(22, 6) NULL DEFAULT NULL,
`synchronous_quantity_unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
`ctime` datetime(0) NULL DEFAULT NULL,
`utime` datetime(0) NULL DEFAULT NULL,
`company_id` int(11) NULL DEFAULT NULL,
`user_id` int(11) NULL DEFAULT NULL,
`status` int(2) NULL DEFAULT 99,
PRIMARY KEY (`inventory_fast_id`) USING BTREE,
INDEX `status`(`status`, `affair_id`, `user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 111 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_notice_affair
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_notice_affair`;
CREATE TABLE `t_inventory_notice_affair`  (
`notice_affair_id` int(11) NOT NULL AUTO_INCREMENT,
`affair_id` int(11) NOT NULL,
`from_affair_id` int(11) NOT NULL,
`from_affair_data_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`notice_state` tinyint(1) NULL DEFAULT 0,
`requirement_quantity` decimal(22, 6) NULL DEFAULT NULL,
`requirement_quantity_unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`requirement_note` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`requirement_voice` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
`allow_user_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`ignore_user_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`user_id` int(11) NULL DEFAULT NULL,
`ctime` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
PRIMARY KEY (`notice_affair_id`) USING BTREE,
UNIQUE INDEX `id`(`notice_affair_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1567 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;


ALTER TABLE `t_affair_sheet_column`
ADD COLUMN IF NOT EXISTS `detail_id` int(11) NULL DEFAULT NULL AFTER `column_order`;

ALTER TABLE `t_machine_calculation`
MODIFY COLUMN IF EXISTS `calculation_condition` varchar (80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `tagIds`,
ADD COLUMN IF NOT EXISTS `total_weight` decimal (22,2) NULL DEFAULT NULL AFTER `pipe_weight`;


ALTER TABLE `t_rule_new`
ADD COLUMN IF NOT EXISTS `set_begin` tinyint(1) NULL DEFAULT 0 AFTER `image_search`,
ADD COLUMN IF NOT EXISTS `set_account`  tinyint(1) NULL DEFAULT 0 AFTER `set_begin`,
ADD COLUMN IF NOT EXISTS `qr_code_modular`  tinyint(1) NULL DEFAULT 0 AFTER `set_account`,
ADD COLUMN IF NOT EXISTS `qr_code_modular_set`  tinyint(1) NULL DEFAULT 0 AFTER `qr_code_modular`,
ADD COLUMN IF NOT EXISTS `qr_code_modular_delete`  tinyint(1) NULL DEFAULT 0 AFTER `qr_code_modular_set`,
ADD COLUMN IF NOT EXISTS `inventory_modular`  tinyint(1) NULL DEFAULT 0 AFTER `qr_code_modular_delete`,
ADD COLUMN IF NOT EXISTS `inventory_modular_set`  tinyint(1) NULL DEFAULT 0 AFTER `inventory_modular`,
ADD COLUMN IF NOT EXISTS `set_inventory`  tinyint(1) NULL DEFAULT 0 AFTER `inventory_modular_set`,
ADD COLUMN IF NOT EXISTS `documentary_modify_record_see`  tinyint(1) NULL AFTER `set_inventory`;

ALTER TABLE `t_user_reminder`
ADD COLUMN IF NOT EXISTS `inventory_notice_reminder` tinyint(1) NULL DEFAULT 0 AFTER `customer_tag_reminder`,
ADD COLUMN IF NOT EXISTS `documentary_reply_reminder`  tinyint(1) NULL DEFAULT 0 AFTER `inventory_notice_reminder`;

ALTER TABLE `t_printer`
MODIFY COLUMN IF EXISTS `printer_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL AFTER `printer_from_id`;

ALTER TABLE `t_affair_frequently`
ADD COLUMN IF NOT EXISTS `affair_category` tinyint(1) NULL DEFAULT 0 AFTER `affair_id`;

ALTER TABLE `t_affair_bar`
ADD COLUMN IF NOT EXISTS `affair_selected` int(11) NULL AFTER `affair_category`;

ALTER TABLE `t_affair_modify_record`
DROP INDEX IF EXISTS `type`,
ADD INDEX `type`(`type`);

ALTER TABLE `t_documentary`
ADD COLUMN IF NOT EXISTS `documentary_type` tinyint(1) NULL DEFAULT 1 AFTER `documentary_state`,
ADD COLUMN IF NOT EXISTS `receive_user_ids`  text CHARACTER SET utf8 COLLATE utf8_bin NULL AFTER `documentary_type`;


ALTER TABLE `t_documentary_step`
ADD COLUMN IF NOT EXISTS `step_is_system` tinyint(1) NULL DEFAULT 0 AFTER `step_number`,
ADD COLUMN IF NOT EXISTS `documentary_type`  tinyint(1) NULL DEFAULT 1 AFTER `step_is_system`;

ALTER TABLE `t_documentary_statistics`
ADD COLUMN IF NOT EXISTS `documentary_type` tinyint(1) NULL DEFAULT 1 AFTER `statistics_number`;

SET FOREIGN_KEY_CHECKS=1;