lacemi/data/upgrade/v3.0.0/v3.0.0.sql

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `t_customized` (
  `customized_id` int(11) NOT NULL AUTO_INCREMENT,
  `customized_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `customized_shortname` varchar(255) COLLATE utf8_bin DEFAULT '',
  `customized_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `customized_class` varchar(255) COLLATE utf8_bin DEFAULT '',
  `customized_content` varchar(255) COLLATE utf8_bin DEFAULT '',
  `customized_type` tinyint(1) DEFAULT NULL,
  `permission` tinyint(1) DEFAULT 0,
  `user_ids` text COLLATE utf8_bin DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `customized_other` text COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`customized_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for t_fast_input
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_fast_input` (
  `fast_input_id` int(11) NOT NULL AUTO_INCREMENT,
  `customized_id` int(11) NOT NULL,
  `fast_input_number` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
  `fast_input_state` tinyint(1) DEFAULT 0,
  `fast_input_data_ids` text COLLATE utf8_bin NOT NULL,
  `fast_input_content` text COLLATE utf8_bin DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`fast_input_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for t_inventory_price_record
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_inventory_price_record` (
  `inventory_price_record_id` int(11) NOT NULL AUTO_INCREMENT,
  `inventory_id` int(11) NOT NULL,
  `record_time` datetime NOT NULL,
  `inventory_price` double(22,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `status` int(2) DEFAULT 99,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`inventory_price_record_id`),
  KEY `inventory_id` (`inventory_id`),
  CONSTRAINT `t_inventory_price_record_ibfk_1` FOREIGN KEY (`inventory_id`) REFERENCES `t_inventory` (`inventory_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_machine_pause
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_machine_pause` (
  `machine_id` int(11) NOT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `permission` tinyint(1) DEFAULT 1,
  `pause_user_ids` text DEFAULT NULL,
  PRIMARY KEY (`machine_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for t_machine_stop_reason
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_machine_stop_reason` (
  `stop_reason_id` int(11) NOT NULL AUTO_INCREMENT,
  `stop_reason_name` varchar(255) NOT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `permission` tinyint(1) DEFAULT 1,
  `stop_reason_user_ids` text DEFAULT NULL,
  PRIMARY KEY (`stop_reason_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for t_offer_cost_float
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_offer_cost_float` (
  `offer_cost_float_id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_cost_float_number` double(13,2) DEFAULT 0.00,
  `product_ids` text DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`offer_cost_float_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for t_offer_price
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_offer_price` (
  `offer_price_id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_price_des` varchar(255) NOT NULL,
  `offer_price_number` double(13,2) DEFAULT 0.00,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `permission` tinyint(1) DEFAULT 1,
  `see_user_ids` text DEFAULT NULL,
  PRIMARY KEY (`offer_price_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Table structure for t_user_report_password
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_user_report_password` (
  `user_id` int(11) NOT NULL,
  `report_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `report_password_type` tinyint(1) NOT NULL DEFAULT 0,
  `report_password_interval` int(11) DEFAULT 0,
  `use_time` datetime DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE,
  CONSTRAINT `t_user_report_password_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

ALTER TABLE `t_machine_calculation`
ADD COLUMN IF NOT EXISTS `stop_reason_id`  int(11) NULL AFTER `public_note`;

ALTER TABLE `t_machine`
ADD COLUMN IF NOT EXISTS `pause_affair_data_id`  varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `affair_data_id`;

ALTER TABLE `t_rule_new`
ADD COLUMN IF NOT EXISTS `machine_modular_pause`  tinyint(1) NULL DEFAULT 0 AFTER `machine_modular_wait`;


ALTER TABLE `t_rule_new`
ADD COLUMN IF NOT EXISTS `product_modular_internal_price`  tinyint(1) NULL DEFAULT 0 AFTER `documentary_modular_remove`,
ADD COLUMN IF NOT EXISTS `product_cost_accounting`  tinyint(1) NULL DEFAULT 0 AFTER `product_modular_internal_price`,
ADD COLUMN IF NOT EXISTS `product_cost_view`  tinyint(1) NULL DEFAULT 0 AFTER `product_cost_accounting`;


ALTER TABLE `t_inventory_calculation`
ADD COLUMN IF NOT EXISTS `offer_price`  decimal(22,3) NULL DEFAULT -1 AFTER `from_data_id`,
ADD COLUMN IF NOT EXISTS `offer_product_total_cost`  decimal(22,6) NULL AFTER `offer_price`,
ADD COLUMN IF NOT EXISTS `calculation_ctime`  datetime NULL DEFAULT NULL AFTER `offer_product_total_cost`;


ALTER TABLE `t_inventory`
ADD COLUMN IF NOT EXISTS `inventory_price_unit`  tinyint(1) NULL AFTER `utime`;

ALTER TABLE `t_statement_calculation`
ADD COLUMN IF NOT EXISTS `bill_ids` text NULL AFTER `tag_numbers`;

ALTER TABLE `t_tag_list_rel`
ADD COLUMN IF NOT EXISTS `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `tag_id`;

REPLACE INTO `t_affair_system_column`(`column_id`, `column_pid`, `group_id`, `column_order`, `column_title`, `column_name`, `column_unit`, `column_type`, `column_control`, `column_rule`, `column_is_virtual`, `column_unit_symbiosis`, `column_detachable`, `status`) VALUES (238, 0, 9, 9, '胚布价格', 'public_embryo_price', '[{\"key\":\"yuan/kg\",\"value\":\"元/公斤\"},{\"key\":\"yuan/yard\",\"value\":\"元/码\"},{\"key\":\"yuan/one\",\"value\":\"元/条\"},{\"key\":\"yuan/m\",\"value\":\"元/米\"},{\"key\":\"yuan/slice\",\"value\":\"元/片\"}]', '{\"key\":\"double\",\"value\":\"3\"}', 0, '{\"regex\":\"/^[0-9]+(.[0-9]{0,3})?$/\",\"tips\":\"只能为数字和3位小数点\"}', 0, '', 2, NULL);

REPLACE INTO `t_affair_system_column`(`column_id`, `column_pid`, `group_id`, `column_order`, `column_title`, `column_name`, `column_unit`, `column_type`, `column_control`, `column_rule`, `column_is_virtual`, `column_unit_symbiosis`, `column_detachable`, `status`) VALUES (338, 0, 6, 10, '同步数量', 'synchronous_quantity', '[{\"key\":\"kg\",\"value\":\"公斤\"},{\"key\":\"yard\",\"value\":\"码\"},{\"key\":\"kg/yard\",\"value\":\"公斤/码\"},{\"key\":\"one\",\"value\":\"条\"},{\"key\":\"m\",\"value\":\"米\"},{\"key\":\"slice\",\"value\":\"片\"}]', '', 13, '{\"regex\":\"\",\"tips\":\"[公斤/码]时应如5.11/6.22\"}', 0, '', 1, NULL);

ALTER TABLE `t_rule_new`
ADD COLUMN IF NOT EXISTS `image_search`  tinyint(1) NULL DEFAULT 0 AFTER `inventory_modular_edit`;

ALTER TABLE `t_product`
ADD COLUMN IF NOT EXISTS `search_id` int(11) NULL AFTER `product_public_notes`,
ADD COLUMN IF NOT EXISTS `search_status` int(11) NULL AFTER `search_id`;

ALTER TABLE `t_collection`
ADD COLUMN IF NOT EXISTS `search_id` int(11) NULL AFTER `recv_result`,
ADD COLUMN IF NOT EXISTS `search_status` int(11) NULL AFTER `search_id`;

ALTER TABLE `t_customer_group`
ADD COLUMN IF NOT EXISTS `permission`  tinyint(1) NULL DEFAULT 1 AFTER `deleteAt`,
ADD COLUMN IF NOT EXISTS `user_ids`  text CHARACTER SET utf8 COLLATE utf8_bin NULL AFTER `permission`

ALTER TABLE `t_rule_new`
ADD COLUMN IF NOT EXISTS `product_confidential_set`  tinyint(1) NULL DEFAULT 0 AFTER `product_cost_view`,
ADD COLUMN IF NOT EXISTS `product_confidential_use`  tinyint(1) NULL DEFAULT 0 AFTER `product_confidential_set`

ALTER TABLE `t_product`
ADD COLUMN IF NOT EXISTS  `product_cabinet_id`  int(11) NULL AFTER `search_status`

ALTER TABLE `t_product` ADD FOREIGN KEY (`product_cabinet_id`) REFERENCES `t_confidential_cabinet` (`cabinet_id`) ON DELETE SET NULL ON UPDATE CASCADE

-- ----------------------------
-- Table structure for t_confidential_cabinet
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_confidential_cabinet` (
  `cabinet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '公司单号ID',
  `cabinet_name` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  `permission` tinyint(1) DEFAULT 0,
  `user_ids` text COLLATE utf8_bin DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  PRIMARY KEY (`cabinet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for t_confidential_lock
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_confidential_lock` (
  `lock_id` int(11) NOT NULL AUTO_INCREMENT,
  `lock_name` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lock_password` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lock_time` varchar(25) COLLATE utf8_bin DEFAULT '',
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `utime` datetime DEFAULT NULL,
  PRIMARY KEY (`lock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for t_confidential_relation
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_confidential_relation` (
  `relation_id` int(11) NOT NULL AUTO_INCREMENT,
  `lock_id` int(11) NOT NULL,
  `cabinet_id` int(11) NOT NULL,
  PRIMARY KEY (`relation_id`),
  KEY `lock_id` (`lock_id`),
  KEY `cabinet_id` (`cabinet_id`),
  CONSTRAINT `t_confidential_relation_ibfk_1` FOREIGN KEY (`lock_id`) REFERENCES `t_confidential_lock` (`lock_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_confidential_relation_ibfk_2` FOREIGN KEY (`cabinet_id`) REFERENCES `t_confidential_cabinet` (`cabinet_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for t_product_share_error
-- ----------------------------
CREATE TABLE IF NOT EXISTS `t_product_share_error` (
  `error_id` int(11) NOT NULL AUTO_INCREMENT,
  `error_ip` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',
  `error_number` tinyint(1) DEFAULT NULL,
  `error_time` datetime DEFAULT NULL,
  PRIMARY KEY (`error_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

SET FOREIGN_KEY_CHECKS=1;