lacemi/data/sql/structure.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_achievements
-- ----------------------------
DROP TABLE IF EXISTS `t_achievements`;
CREATE TABLE `t_achievements`  (
  `achievements_id` int(11) NOT NULL AUTO_INCREMENT,
  `total_goal` double(22, 0) NOT NULL,
  `no_customer_tag` double(5, 2) NULL DEFAULT 0.00,
  `customer_tag` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`achievements_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_achievements_group
-- ----------------------------
DROP TABLE IF EXISTS `t_achievements_group`;
CREATE TABLE `t_achievements_group`  (
  `achievements_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_total_goal` double(22, 0) NOT NULL,
  `achievements_group_title` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `achievements_group_users` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`achievements_group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair
-- ----------------------------
DROP TABLE IF EXISTS `t_affair`;
CREATE TABLE `t_affair`  (
  `affair_id` int(11) NOT NULL AUTO_INCREMENT,
  `affair_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `affair_icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `affair_tablename` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `affair_is_buildin` tinyint(255) NULL DEFAULT NULL,
  `affair_group_id` int(11) NULL DEFAULT NULL,
  `version` int(11) NULL DEFAULT NULL,
  `lock_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`affair_id`) USING BTREE,
  INDEX `affair_group_id`(`affair_group_id`) USING BTREE,
  CONSTRAINT `t_affair_ibfk_1` FOREIGN KEY (`affair_group_id`) REFERENCES `t_affair_group` (`affair_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_bar
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_bar`;
CREATE TABLE `t_affair_bar`  (
  `bar_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `affairIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `affair_category` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`bar_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_affair_bar_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

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

-- ----------------------------
-- Table structure for t_affair_frequently
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_frequently`;
CREATE TABLE `t_affair_frequently`  (
  `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `affair_id` int(11) NOT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`frequently_id`) USING BTREE,
  INDEX `affair_id`(`affair_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_affair_frequently_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_affair_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_affair_group
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_group`;
CREATE TABLE `t_affair_group`  (
  `affair_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `affair_group_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `affair_group_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(11) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`affair_group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_modify_record
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_modify_record`;
CREATE TABLE `t_affair_modify_record`  (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `affair_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `record_data` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `type` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`record_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_affair_modify_record_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_affair_rule
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_rule`;
CREATE TABLE `t_affair_rule`  (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `affair_id` int(11) NULL DEFAULT NULL,
  `sheet_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `rule_allow_use` tinyint(1) NULL DEFAULT 0,
  `rule_allow_view` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`rule_id`) USING BTREE,
  INDEX `sheet_id`(`sheet_id`) USING BTREE,
  CONSTRAINT `t_affair_rule_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_sheet
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_sheet`;
CREATE TABLE `t_affair_sheet`  (
  `sheet_id` int(11) NOT NULL AUTO_INCREMENT,
  `sheet_prev_id` int(11) NULL DEFAULT NULL,
  `sheet_next_id` int(11) NULL DEFAULT NULL,
  `affair_id` int(11) NOT NULL,
  `sheet_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `sheet_tablename` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `sheet_rule_use_option` tinyint(1) NULL DEFAULT NULL,
  `sheet_rule_view_option` tinyint(1) NULL DEFAULT NULL,
  `sheet_join_order` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`sheet_id`) USING BTREE,
  INDEX `affair_id`(`affair_id`) USING BTREE,
  CONSTRAINT `t_affair_sheet_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_sheet_column
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_sheet_column`;
CREATE TABLE `t_affair_sheet_column`  (
  `sheet_column_id` int(11) NOT NULL AUTO_INCREMENT,
  `sheet_column_pid` int(11) NULL DEFAULT 0,
  `column_id` int(11) NULL DEFAULT NULL,
  `sheet_id` int(11) NULL DEFAULT NULL,
  `sheet_group_id` int(11) NULL DEFAULT NULL,
  `column_is_require` tinyint(1) NULL DEFAULT 0,
  `column_is_summary` tinyint(1) NULL DEFAULT 0,
  `column_order` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`sheet_column_id`) USING BTREE,
  INDEX `sheet_id`(`sheet_id`) USING BTREE,
  INDEX `system_column_id`(`column_id`) USING BTREE,
  CONSTRAINT `t_affair_sheet_column_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_affair_sheet_column_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `t_affair_system_column` (`column_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_sheet_column_group
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_sheet_column_group`;
CREATE TABLE `t_affair_sheet_column_group`  (
  `sheet_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `sheet_group_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `sheet_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sheet_group_id`) USING BTREE,
  INDEX `sheet_id`(`sheet_id`) USING BTREE,
  CONSTRAINT `t_affair_sheet_column_group_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_affair_system_column
-- ----------------------------
DROP TABLE IF EXISTS `t_affair_system_column`;
CREATE TABLE `t_affair_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 NULL,
  `column_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `column_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '[]',
  `column_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `column_control` tinyint(1) NULL DEFAULT 0,
  `column_rule` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `column_is_virtual` tinyint(1) NULL DEFAULT 0,
  `column_unit_symbiosis` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `column_detachable` tinyint(1) NULL DEFAULT 0,
  `status` tinyint(255) NULL DEFAULT NULL,
  PRIMARY KEY (`column_id`) USING BTREE,
  INDEX `group_id`(`group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

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

-- ----------------------------
-- Table structure for t_bill
-- ----------------------------
DROP TABLE IF EXISTS `t_bill`;
CREATE TABLE `t_bill`  (
  `bill_id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_count` int(11) NULL DEFAULT 0,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `permission` int(1) NULL DEFAULT 1,
  `order_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_affair_Ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_type` tinyint(1) NULL DEFAULT 0,
  `bill_template` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_merge_condition` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `bill_set_explain` varchar(255) NULL DEFAULT '0',
  PRIMARY KEY (`bill_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_bill_order
-- ----------------------------
DROP TABLE IF EXISTS `t_bill_order`;
CREATE TABLE `t_bill_order`  (
  `bill_order_id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_id` int(11) NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_order_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_order_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_affair_data_Ids` varchar(550) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `ptime` datetime(0) NULL DEFAULT NULL,
  `bill_data` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `printer_status` int(1) NULL DEFAULT 0,
  `bill_order_supplement` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_explain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`bill_order_id`) USING BTREE,
  INDEX `bill_id`(`bill_id`) USING BTREE,
  CONSTRAINT `t_bill_order_ibfk_1` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_bill_permission
-- ----------------------------
DROP TABLE IF EXISTS `t_bill_permission`;
CREATE TABLE `t_bill_permission`  (
  `bill_per_id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`bill_per_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `bill_per_id`(`bill_per_id`) USING BTREE,
  INDEX `bill_id`(`bill_id`) USING BTREE,
  CONSTRAINT `t_bill_permission_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_bill_permission_ibfk_3` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_bill_subtitle
-- ----------------------------
DROP TABLE IF EXISTS `t_bill_subtitle`;
CREATE TABLE `t_bill_subtitle`  (
  `bill_subtitle_id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_id` int(11) NOT NULL,
  `user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `bill_subtitle_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`bill_subtitle_id`) USING BTREE,
  INDEX `bill_id`(`bill_id`) USING BTREE,
  CONSTRAINT `t_bill_subtitle_ibfk_2` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_cas_st
-- ----------------------------
DROP TABLE IF EXISTS `t_cas_st`;
CREATE TABLE `t_cas_st`  (
  `st_id` varchar(23) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `tgt_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `st_service` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `st_device` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `deviceId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `deviceToken` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `st_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `st_last_used` datetime(0) NULL DEFAULT NULL,
  `st_use_times` int(11) NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `dtime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `status` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`st_id`) USING BTREE,
  INDEX `tgt_id`(`tgt_id`) USING BTREE,
  CONSTRAINT `t_cas_st_ibfk_1` FOREIGN KEY (`tgt_id`) REFERENCES `t_cas_tgt` (`tgt_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_cas_tgt
-- ----------------------------
DROP TABLE IF EXISTS `t_cas_tgt`;
CREATE TABLE `t_cas_tgt`  (
  `tgt_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `tgt_last_used` datetime(0) NULL DEFAULT NULL,
  `tgt_use_times` int(11) NULL DEFAULT NULL,
  `tgt_platform` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tgt_kick_step` int(255) NULL DEFAULT NULL,
  `ctime` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tgt_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_collection
-- ----------------------------
DROP TABLE IF EXISTS `t_collection`;
CREATE TABLE `t_collection`  (
  `collection_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `company_id` int(11) NOT NULL,
  `image_id` int(11) NULL DEFAULT NULL,
  `product_id` int(11) NULL DEFAULT NULL,
  `collection_image_id` int(11) NULL DEFAULT NULL,
  `collection_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `collection_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `collection_type` tinyint(2) NOT NULL DEFAULT 0,
  `collection_state` tinyint(2) NULL DEFAULT 0,
  `collection_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_inventory` decimal(13, 3) NULL DEFAULT NULL,
  `collection_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_width` decimal(13, 2) NULL DEFAULT NULL,
  `collection_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_height` decimal(13, 2) NULL DEFAULT NULL,
  `collection_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  `collection_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `collection_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `collection_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `collection_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `collection_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `collection_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `collection_gram_weight` decimal(18, 0) NULL DEFAULT NULL,
  `collection_gram_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT 'g/m2',
  `collection_tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_cloth_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `product_product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `product_cloth_price` decimal(13, 2) NULL DEFAULT NULL,
  `product_product_price` decimal(13, 2) NULL DEFAULT NULL,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  `status` tinyint(2) NOT NULL DEFAULT 99,
  PRIMARY KEY (`collection_id`) USING BTREE,
  INDEX `image_id`(`image_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `company_id`(`company_id`) USING BTREE,
  FULLTEXT INDEX `collection_pname`(`collection_pname`),
  FULLTEXT INDEX `collection_cname`(`collection_cname`),
  CONSTRAINT `t_collection_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `t_collection_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_company
-- ----------------------------
DROP TABLE IF EXISTS `t_company`;
CREATE TABLE `t_company`  (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `location_id` int(11) NULL DEFAULT NULL,
  `company_type` tinyint(1) NULL DEFAULT 0,
  `company_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `company_shortname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_major` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_urlname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_cert` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_fax` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `company_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_ctime` date NULL DEFAULT NULL,
  `company_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_corporation` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_copy` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `company_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `viewtimes` int(11) NULL DEFAULT 0,
  `company_follows` int(255) NULL DEFAULT 0,
  `is_vip` tinyint(1) NULL DEFAULT 0,
  `status` int(255) NULL DEFAULT 0,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  `tagIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `customerGroupId` int(11) NULL DEFAULT 0,
  `company_program` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `version` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`company_id`) USING BTREE,
  INDEX `FK_Reference_29`(`location_id`) USING BTREE,
  INDEX `customerGroupId`(`customerGroupId`) USING BTREE,
  INDEX `company_name`(`company_name`) USING BTREE,
  INDEX `company_shortname`(`company_shortname`) USING BTREE,
  INDEX `company_urlname`(`company_urlname`) USING BTREE,
  CONSTRAINT `FK_Reference_29` FOREIGN KEY (`location_id`) REFERENCES `t_location` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `t_company_ibfk_1` FOREIGN KEY (`customerGroupId`) REFERENCES `t_customer_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_customer
-- ----------------------------
DROP TABLE IF EXISTS `t_customer`;
CREATE TABLE `t_customer`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyId` int(11) NOT NULL DEFAULT 0,
  `addUserId` int(11) NOT NULL,
  `customerCompanyId` int(11) NULL DEFAULT NULL,
  `avatarImageId` int(11) NULL DEFAULT NULL,
  `avatarImage` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tagIds` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `visitUserIds` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `state` int(11) NULL DEFAULT 0,
  `status` int(11) NULL DEFAULT 99,
  `createAt` datetime(0) NULL DEFAULT NULL,
  `updateAt` datetime(0) NULL DEFAULT NULL,
  `deleteAt` datetime(0) NULL DEFAULT NULL,
  `groupId` int(11) NULL DEFAULT NULL,
  `card1ImageId` int(11) NULL DEFAULT NULL,
  `card2ImageId` int(11) NULL DEFAULT NULL,
  `card3ImageId` int(11) NULL DEFAULT NULL,
  `representative` tinyint(1) NULL DEFAULT 0,
  `customer_add` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `customer_phone2` varchar(255) NULL DEFAULT '',
  `customer_phone3` varchar(255) NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `companyId`(`companyId`) USING BTREE,
  INDEX `t_customer_ibfk_2`(`customerCompanyId`) USING BTREE,
  INDEX `id`(`id`, `customerCompanyId`) USING BTREE,
  INDEX `groupId`(`groupId`) USING BTREE,
  CONSTRAINT `t_customer_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_customer_ibfk_2` FOREIGN KEY (`customerCompanyId`) REFERENCES `t_company` (`company_id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `t_customer_ibfk_3` FOREIGN KEY (`groupId`) REFERENCES `t_customer_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_customer_group
-- ----------------------------
DROP TABLE IF EXISTS `t_customer_group`;
CREATE TABLE `t_customer_group`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `usedCount` int(11) NULL DEFAULT 0,
  `status` int(11) NULL DEFAULT 99,
  `createAt` datetime(0) NULL DEFAULT NULL,
  `updateAt` datetime(0) NULL DEFAULT NULL,
  `deleteAt` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `companyId`(`companyId`) USING BTREE,
  INDEX `userId`(`userId`) USING BTREE,
  CONSTRAINT `t_customer_group_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_customer_group_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_department
-- ----------------------------
DROP TABLE IF EXISTS `t_department`;
CREATE TABLE `t_department`  (
  `department_id` int(255) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `department_pid` int(255) NULL DEFAULT 0,
  PRIMARY KEY (`department_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_documentary
-- ----------------------------
DROP TABLE IF EXISTS `t_documentary`;
CREATE TABLE `t_documentary`  (
  `documentary_id` int(11) NOT NULL,
  `documentary_summary` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `documentary_state` tinyint(1) NULL DEFAULT 0,
  `user_id` int(11) NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `status` tinyint(2) NULL DEFAULT 99,
  PRIMARY KEY (`documentary_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_documentary_report
-- ----------------------------
DROP TABLE IF EXISTS `t_documentary_report`;
CREATE TABLE `t_documentary_report`  (
  `report_id` int(11) NOT NULL AUTO_INCREMENT,
  `documentary_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `reply_user_id` int(11) NULL DEFAULT NULL,
  `reply_txt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_voice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`report_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_file_list
-- ----------------------------
DROP TABLE IF EXISTS `t_file_list`;
CREATE TABLE `t_file_list`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `md5` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `crop` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `category` int(255) NULL DEFAULT NULL,
  `props` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `mime` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `extension` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `size` int(11) NULL DEFAULT NULL,
  `type` int(255) NULL DEFAULT NULL,
  `icat` int(255) NULL DEFAULT NULL,
  `icatmp` int(255) NULL DEFAULT NULL,
  `visitimes` int(11) NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  `status` int(255) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `md5`(`md5`, `type`, `category`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_group_position
-- ----------------------------
DROP TABLE IF EXISTS `t_group_position`;
CREATE TABLE `t_group_position`  (
  `position_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `groupIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `position_type` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`position_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_group_position_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_inventory
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory`;
CREATE TABLE `t_inventory`  (
  `inventory_id` int(11) NOT NULL AUTO_INCREMENT,
  `warehouse_product_id` int(11) NULL DEFAULT NULL,
  `warehouse_product_color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `warehouse_product_color_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `warehouse_product_height` double(22, 2) NULL DEFAULT NULL,
  `warehouse_product_width` double(22, 2) NULL DEFAULT NULL,
  `warehouse_material_composition` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `warehouse_machine_id` int(11) NULL DEFAULT NULL,
  `warehouse_storage_area` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `inventory_quantity_unit` int(1) NULL DEFAULT 0,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`inventory_id`) USING BTREE,
  INDEX `warehouse_product_id`(`warehouse_product_id`) USING BTREE,
  INDEX `warehouse_machine_id`(`warehouse_machine_id`) USING BTREE,
  CONSTRAINT `t_inventory_ibfk_1` FOREIGN KEY (`warehouse_product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_inventory_ibfk_2` FOREIGN KEY (`warehouse_machine_id`) REFERENCES `t_machine` (`machine_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_calculation
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_calculation`;
CREATE TABLE `t_inventory_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `affair_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_key` int(11) NOT NULL,
  `calculation_time` datetime(0) NOT NULL,
  `calculation_value` decimal(22, 6) NOT NULL,
  `calculation_value2` decimal(22, 6) NOT NULL,
  `calculation_condition` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `call_time` datetime(0) NOT NULL,
  `from_affair_id` int(11) NOT NULL,
  PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX `calculation_value`(`calculation_key`, `calculation_time`, `calculation_value`, `calculation_value2`, `calculation_condition`, `tagIds`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_inventory_record
-- ----------------------------
DROP TABLE IF EXISTS `t_inventory_record`;
CREATE TABLE `t_inventory_record`  (
  `inventory_record_id` int(11) NOT NULL AUTO_INCREMENT,
  `inventory_id` int(11) NOT NULL,
  `record_time` datetime(0) NOT NULL,
  `inventory_quantity` double(22, 2) NOT NULL,
  `inventory_quantity_reserve` double(22, 2) NULL DEFAULT 0.00,
  `notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`inventory_record_id`) USING BTREE,
  INDEX `inventory_id`(`inventory_id`) USING BTREE,
  CONSTRAINT `t_inventory_record_ibfk_1` FOREIGN KEY (`inventory_id`) REFERENCES `t_inventory` (`inventory_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_keyword_log
-- ----------------------------
DROP TABLE IF EXISTS `t_keyword_log`;
CREATE TABLE `t_keyword_log`  (
  `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `company_id` int(11) NULL DEFAULT NULL,
  `keyword_category` int(11) NULL DEFAULT NULL,
  `keyword_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `keyword_values` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(11) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`keyword_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `company_id`(`company_id`) USING BTREE,
  CONSTRAINT `t_keyword_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_keyword_log_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_location
-- ----------------------------
DROP TABLE IF EXISTS `t_location`;
CREATE TABLE `t_location`  (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `location_pid` int(11) NULL DEFAULT NULL,
  `location_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `location_postnum` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `location_level` tinyint(1) NULL DEFAULT NULL,
  `location_phonenum` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`location_id`) USING BTREE,
  INDEX `FK_Reference_28`(`location_pid`) USING BTREE,
  CONSTRAINT `FK_Reference_28` FOREIGN KEY (`location_pid`) REFERENCES `t_location` (`location_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_log_edit
-- ----------------------------
DROP TABLE IF EXISTS `t_log_edit`;
CREATE TABLE `t_log_edit`  (
  `log_edit_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_company_id` int(11) NULL DEFAULT NULL,
  `log_user_id` int(11) NULL DEFAULT NULL,
  `log_edit_clazz` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `log_edit_oldvalue` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  `log_edit_newvalue` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  `log_action` tinyint(1) NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`log_edit_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_machine
-- ----------------------------
DROP TABLE IF EXISTS `t_machine`;
CREATE TABLE `t_machine`  (
  `machine_id` int(11) NOT NULL AUTO_INCREMENT,
  `machine_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `machine_horse` int(11) NULL DEFAULT 0,
  `machine_des` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `machine_state` int(2) NOT NULL DEFAULT 0,
  `affair_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `model_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `status` int(2) NOT NULL DEFAULT 99,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`machine_id`) USING BTREE,
  INDEX `machine_id`(`machine_id`) USING BTREE,
  INDEX `model_name`(`model_name`) USING BTREE,
  CONSTRAINT `t_machine_ibfk_1` FOREIGN KEY (`model_name`) REFERENCES `t_machine_model` (`model_name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_machine_model
-- ----------------------------
DROP TABLE IF EXISTS `t_machine_model`;
CREATE TABLE `t_machine_model`  (
  `model_id` int(11) NOT NULL AUTO_INCREMENT,
  `model_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `model_des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(11) NULL DEFAULT 99,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`model_id`) USING BTREE,
  INDEX `model_name`(`model_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_machine_record
-- ----------------------------
DROP TABLE IF EXISTS `t_machine_record`;
CREATE TABLE `t_machine_record`  (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `machine_id` int(11) NOT NULL,
  `record_des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `affair_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `change_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `production_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `stop_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `utime` datetime(0) NOT NULL,
  `ctime` datetime(0) NOT NULL,
  PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_machine_settings
-- ----------------------------
DROP TABLE IF EXISTS `t_machine_settings`;
CREATE TABLE `t_machine_settings`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `produced_affair_id` int(11) NULL DEFAULT NULL,
  `produced_sheet_id` int(11) NULL DEFAULT NULL,
  `produced_affair_tag_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `produced_user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `produced_affair_summary` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `produced_affair_summary_permission` tinyint(1) NULL DEFAULT 1,
  `utime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `produced_sheet`(`produced_sheet_id`) USING BTREE,
  INDEX `produced_affair_id`(`produced_affair_id`) USING BTREE,
  CONSTRAINT `t_machine_settings_ibfk_3` FOREIGN KEY (`produced_sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_machine_settings_ibfk_4` FOREIGN KEY (`produced_affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_pattribute
-- ----------------------------
DROP TABLE IF EXISTS `t_pattribute`;
CREATE TABLE `t_pattribute`  (
  `pattribute_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `company_id` int(11) NULL DEFAULT NULL,
  `product_id` int(11) NOT NULL,
  `image_id` int(11) NULL DEFAULT NULL,
  `pattribute_image_id` int(11) NULL DEFAULT NULL,
  `pattribute_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pattribute_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pattribute_type` int(255) NULL DEFAULT NULL,
  `pattribute_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_inventory` decimal(13, 3) NULL DEFAULT NULL,
  `pattribute_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_width` decimal(13, 2) NULL DEFAULT NULL,
  `pattribute_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_height` decimal(13, 2) NULL DEFAULT NULL,
  `pattribute_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  `pattribute_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `pattribute_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `pattribute_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `pattribute_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `pattribute_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pattribute_from` tinyint(1) NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`pattribute_id`) USING BTREE,
  UNIQUE INDEX `pattribute_id`(`pattribute_id`) USING BTREE,
  INDEX `product_id`(`product_id`) USING BTREE,
  INDEX `image_id`(`image_id`) USING BTREE,
  CONSTRAINT `t_pattribute_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_pattribute_linkman
-- ----------------------------
DROP TABLE IF EXISTS `t_pattribute_linkman`;
CREATE TABLE `t_pattribute_linkman`  (
  `linkman_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `pattribute_id` int(11) NULL DEFAULT NULL,
  `linkman_realname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `linkman_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `linkman_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `linkman_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `linkman_rolename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`linkman_id`) USING BTREE,
  UNIQUE INDEX `linkman_id`(`linkman_id`) USING BTREE,
  INDEX `pattribute_id`(`pattribute_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_pattribute_linkman_ibfk_1` FOREIGN KEY (`pattribute_id`) REFERENCES `t_pattribute` (`pattribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_pattribute_linkman_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_printer
-- ----------------------------
DROP TABLE IF EXISTS `t_printer`;
CREATE TABLE `t_printer`  (
  `printer_id` int(11) NOT NULL AUTO_INCREMENT,
  `need_user_id` int(11) NULL DEFAULT NULL,
  `printer_user_id` int(11) NULL DEFAULT NULL,
  `printer_type` int(11) NULL DEFAULT NULL,
  `printer_from_id` int(11) NULL DEFAULT NULL,
  `printer_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `printer_state` int(11) NULL DEFAULT 0,
  `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `ptime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `ttime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  PRIMARY KEY (`printer_id`) USING BTREE,
  UNIQUE INDEX `printer_id`(`printer_id`) USING BTREE,
  INDEX `need_user_id`(`need_user_id`) USING BTREE,
  INDEX `printer_user_id`(`printer_user_id`) USING BTREE,
  CONSTRAINT `t_printer_ibfk_1` FOREIGN KEY (`need_user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_printer_ibfk_2` FOREIGN KEY (`printer_user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_printer_frequently
-- ----------------------------
DROP TABLE IF EXISTS `t_printer_frequently`;
CREATE TABLE `t_printer_frequently`  (
  `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `printer_user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `utime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  UNIQUE INDEX `frequently_id`(`frequently_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_printer_frequently_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_product
-- ----------------------------
DROP TABLE IF EXISTS `t_product`;
CREATE TABLE `t_product`  (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `company_id` int(11) NOT NULL,
  `company_type` int(11) NULL DEFAULT NULL,
  `image_id` int(11) NULL DEFAULT NULL,
  `product_image_id` int(11) NULL DEFAULT NULL,
  `product_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_type` tinyint(2) NULL DEFAULT NULL,
  `product_state` tinyint(2) NULL DEFAULT 0,
  `product_price` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_cloth_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_cloth_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_product_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_inventory` decimal(13, 3) NULL DEFAULT NULL,
  `product_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_width` decimal(13, 2) NULL DEFAULT NULL,
  `product_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_height` decimal(13, 2) NULL DEFAULT NULL,
  `product_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  `product_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_uptime` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_uptime_density` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_uptime_density_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_chart` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `chart_image_id` int(11) NULL DEFAULT NULL,
  `chart_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `product_hits` int(255) NULL DEFAULT 0,
  `product_tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  `status` tinyint(2) NOT NULL DEFAULT 99,
  `product_exhibition_id` int(11) NULL DEFAULT NULL,
  `product_partially_id` int(11) NULL DEFAULT NULL,
  `product_beginning_id` int(11) NULL DEFAULT NULL,
  `product_gram_weight` decimal(18, 0) NULL DEFAULT NULL,
  `product_gram_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT 'g/m2',
  PRIMARY KEY (`product_id`) USING BTREE,
  UNIQUE INDEX `product_id`(`product_id`) USING BTREE,
  INDEX `FK_Relationship_12`(`user_id`) USING BTREE,
  INDEX `ctime`(`ctime`) USING BTREE,
  INDEX `status`(`status`) USING BTREE,
  INDEX `company_id`(`company_id`) USING BTREE,
  INDEX `image_id`(`image_id`) USING BTREE,
  INDEX `product_pname`(`product_pname`, `product_cname`, `product_type`, `status`) USING BTREE,
  CONSTRAINT `t_product_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_product_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_r_product_collection
-- ----------------------------
DROP TABLE IF EXISTS `t_r_product_collection`;
CREATE TABLE `t_r_product_collection`  (
  `collection_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`collection_id`, `product_id`) USING BTREE,
  INDEX `product_id`(`product_id`) USING BTREE,
  INDEX `collection_id_2`(`collection_id`) USING BTREE,
  CONSTRAINT `t_r_product_collection_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `t_collection` (`collection_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_r_product_collection_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_report_forms
-- ----------------------------
DROP TABLE IF EXISTS `t_report_forms`;
CREATE TABLE `t_report_forms`  (
  `report_forms_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_forms_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_count` int(11) NULL DEFAULT 0,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `forms_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `forms_affair_Ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tag_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `set_begin` tinyint(1) NULL DEFAULT 2,
  `set_inventory` tinyint(1) NULL DEFAULT 2,
  `report_forms_pid` int(11) NULL DEFAULT NULL,
  `report_forms_prompt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `print_forms_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `report_forms_group_id` int(11) NULL DEFAULT NULL,
  `set_seal_account` tinyint(1) NULL DEFAULT 2,
  `set_achievements` tinyint(1) NULL DEFAULT 2,
  PRIMARY KEY (`report_forms_id`) USING BTREE,
  INDEX `report_forms_group_id`(`report_forms_group_id`) USING BTREE,
  CONSTRAINT `t_report_forms_ibfk_1` FOREIGN KEY (`report_forms_group_id`) REFERENCES `t_report_forms_group` (`report_forms_group_id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_report_forms_begin
-- ----------------------------
DROP TABLE IF EXISTS `t_report_forms_begin`;
CREATE TABLE `t_report_forms_begin`  (
  `begin_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `report_forms_id` int(11) NULL DEFAULT NULL,
  `begin_customer` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `begin_cumulative` double(16, 2) NULL DEFAULT NULL,
  `admission` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`begin_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `report_forms_id`(`report_forms_id`) USING BTREE,
  CONSTRAINT `t_report_forms_begin_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_report_forms_begin_ibfk_2` FOREIGN KEY (`report_forms_id`) REFERENCES `t_report_forms` (`report_forms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_report_forms_group
-- ----------------------------
DROP TABLE IF EXISTS `t_report_forms_group`;
CREATE TABLE `t_report_forms_group`  (
  `report_forms_group_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_forms_group_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `report_forms_group_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` int(11) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`report_forms_group_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_report_forms_permission
-- ----------------------------
DROP TABLE IF EXISTS `t_report_forms_permission`;
CREATE TABLE `t_report_forms_permission`  (
  `report_forms_per_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_forms_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`report_forms_per_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `bill_per_id`(`report_forms_per_id`) USING BTREE,
  INDEX `bill_id`(`report_forms_id`) USING BTREE,
  CONSTRAINT `t_report_forms_permission_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_report_forms_permission_ibfk_2` FOREIGN KEY (`report_forms_id`) REFERENCES `t_report_forms` (`report_forms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_role
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role`  (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `role_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_rule_new
-- ----------------------------
DROP TABLE IF EXISTS `t_rule_new`;
CREATE TABLE `t_rule_new`  (
  `rule_new_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `product_modular` tinyint(1) NULL DEFAULT 0,
  `product_modular_delete` tinyint(1) NULL DEFAULT 0,
  `product_modular_add` tinyint(1) NULL DEFAULT 0,
  `product_modular_public` tinyint(1) NULL DEFAULT 0,
  `product_modular_private` tinyint(1) NULL DEFAULT 0,
  `product_modular_supplier` tinyint(1) NULL DEFAULT 0,
  `material_modular` tinyint(1) NULL DEFAULT 0,
  `material_modular_edit` tinyint(1) NULL DEFAULT 0,
  `material_modular_delete` tinyint(1) NULL DEFAULT 0,
  `customer_modular` tinyint(1) NULL DEFAULT 0,
  `customer_modular_delete` tinyint(1) NULL DEFAULT 0,
  `customer_modular_customer_edit` tinyint(1) NULL DEFAULT 0,
  `customer_modular_company_edit` tinyint(1) NULL DEFAULT 0,
  `customer_modular_add` tinyint(1) NULL DEFAULT 0,
  `customer_modular_transfer` tinyint(1) NULL DEFAULT 0,
  `customer_modular_set_visible` tinyint(1) NULL DEFAULT 0,
  `machine_modular` tinyint(1) NULL DEFAULT 0,
  `machine_modular_add` tinyint(1) NULL DEFAULT 0,
  `machine_modular_edit` tinyint(1) NULL DEFAULT 0,
  `machine_modular_record` tinyint(1) NULL DEFAULT 0,
  `machine_modular_wait` tinyint(1) NULL DEFAULT 0,
  `shop_modular` tinyint(1) NULL DEFAULT 0,
  `shop_modular_update` tinyint(1) NULL DEFAULT 0,
  `bill_modular` tinyint(1) NULL DEFAULT 0,
  `bill_modular_waste` tinyint(1) NULL DEFAULT 0,
  `bill_modular_set` tinyint(1) NULL DEFAULT 0,
  `affair_modular` tinyint(1) NULL DEFAULT 0,
  `employee_modular` tinyint(1) NULL DEFAULT 0,
  `tag_modular` tinyint(1) NULL DEFAULT 0,
  `report_modular` tinyint(1) NULL DEFAULT 0,
  `report_modular_download` tinyint(1) NULL DEFAULT 0,
  `company_modular` tinyint(1) NULL DEFAULT 0,
  `company_modular_authentication` tinyint(1) NULL DEFAULT 0,
  `affair_recycle_bin` tinyint(1) NULL DEFAULT 0,
  `documentary_modular` tinyint(1) NULL DEFAULT 0,
  `documentary_modular_control` tinyint(1) NULL DEFAULT 0,
  `documentary_modular_report` tinyint(1) NULL DEFAULT 0,
  `print_modular` tinyint(1) NULL DEFAULT 0,
  `documentary_modular_adjustment` tinyint(1) NULL DEFAULT 0,
  `product_modular_public_see` tinyint(1) NULL DEFAULT 0,
  `product_modular_private_see` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`rule_new_id`) USING BTREE,
  UNIQUE INDEX `rule_new_id`(`rule_new_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_rule_new_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_seal_account
-- ----------------------------
DROP TABLE IF EXISTS `t_seal_account`;
CREATE TABLE `t_seal_account`  (
  `seal_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `content_id` int(11) NULL DEFAULT NULL,
  `content_type` tinyint(1) NULL DEFAULT 0,
  `content_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`seal_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_seal_account_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_shop
-- ----------------------------
DROP TABLE IF EXISTS `t_shop`;
CREATE TABLE `t_shop`  (
  `shop_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NULL DEFAULT NULL,
  `shop_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `shop_urlname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `shop_intro` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `shop_allow_subdomain` smallint(6) NULL DEFAULT 0,
  `shop_open_time` datetime(0) NULL DEFAULT NULL,
  `shop_hits` int(255) NULL DEFAULT 0,
  `shop_wechat_hits` int(255) NULL DEFAULT 0,
  PRIMARY KEY (`shop_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_shop_product
-- ----------------------------
DROP TABLE IF EXISTS `t_shop_product`;
CREATE TABLE `t_shop_product`  (
  `product_id` int(11) NOT NULL,
  `company_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `shop_id` int(11) NULL DEFAULT NULL,
  `product_shop_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_height` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_width` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_price_origin` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_price_origin_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_price_on_sale` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_price_on_sale_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_inventory` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_shop_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_intro` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_is_promotion` int(11) NULL DEFAULT NULL,
  `product_is_order` int(255) NULL DEFAULT NULL,
  `product_on_time` datetime(0) NULL DEFAULT NULL,
  `product_tag_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_order` int(255) NULL DEFAULT 0,
  `product_promotion` tinyint(1) NULL DEFAULT NULL,
  `product_promotion_start` datetime(0) NULL DEFAULT NULL,
  `product_promotion_end` datetime(0) NULL DEFAULT NULL,
  `product_shop_hits` int(255) UNSIGNED NULL DEFAULT 0,
  `product_shop_hits_weight` int(11) NULL DEFAULT 0,
  PRIMARY KEY (`product_id`) USING BTREE,
  UNIQUE INDEX `product_id`(`product_id`) USING BTREE,
  CONSTRAINT `t_shop_product_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_statement_calculation
-- ----------------------------
DROP TABLE IF EXISTS `t_statement_calculation`;
CREATE TABLE `t_statement_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `affair_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `bill_order_id` int(11) NOT NULL,
  `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `calculation_time` datetime(0) NOT NULL,
  `calculation_value` decimal(22, 6) NOT NULL DEFAULT 0.000000,
  `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_supplement` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `call_time` datetime(0) NOT NULL,
  `calculation_senior_screen` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_account_category` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_product_id` int(11) NOT NULL,
  `calculation_collection_id` int(11) NOT NULL,
  `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  INDEX `bill_order_id`(`bill_order_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX ` calculation_value`(`calculation_key`, `affair_id`, `calculation_time`, `calculation_value`, `calculation_condition`, `tagIds`) USING BTREE,
  INDEX `calculation_time`(`calculation_time`, `affair_id`, `calculation_condition`, `tagIds`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_tag_frequently
-- ----------------------------
DROP TABLE IF EXISTS `t_tag_frequently`;
CREATE TABLE `t_tag_frequently`  (
  `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`frequently_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `tag_id`(`tag_id`) USING BTREE,
  CONSTRAINT `t_tag_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_tag_frequently_ibfk_3` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_tag_list
-- ----------------------------
DROP TABLE IF EXISTS `t_tag_list`;
CREATE TABLE `t_tag_list`  (
  `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `tag_category` int(11) NULL DEFAULT 0,
  `tag_image_id` int(11) NULL DEFAULT NULL,
  `tag_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tag_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tag_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `used_count` int(11) NULL DEFAULT 0,
  `status` int(2) NULL DEFAULT 99,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `permission` int(1) NULL DEFAULT 1,
  `necessary` int(1) NULL DEFAULT 0,
  `data_protection` int(1) NULL DEFAULT 0,
  `position`  int(11) NULL DEFAULT 0,
  PRIMARY KEY (`tag_id`) USING BTREE,
  INDEX `t_tag_list_ibfk_1`(`tag_image_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_tag_list_rel
-- ----------------------------
DROP TABLE IF EXISTS `t_tag_list_rel`;
CREATE TABLE `t_tag_list_rel`  (
  `rel_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `tag_category` int(11) NULL DEFAULT NULL,
  `content_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `content_type` int(11) NULL DEFAULT 0,
  `tag_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`rel_id`) USING BTREE,
  INDEX `tag_id`(`tag_id`) USING BTREE,
  CONSTRAINT `t_tag_list_rel_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_tag_permission
-- ----------------------------
DROP TABLE IF EXISTS `t_tag_permission`;
CREATE TABLE `t_tag_permission`  (
  `per_id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`per_id`) USING BTREE,
  INDEX `per_id`(`per_id`) USING BTREE,
  INDEX `tag_id`(`tag_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_tag_permission_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_tag_permission_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NULL DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `location_id` int(11) NULL DEFAULT NULL,
  `department_id` int(11) NULL DEFAULT NULL,
  `user_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_company_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `user_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `user_pwdhash` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_realname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `user_gender` tinyint(1) NULL DEFAULT 1,
  `user_avatar` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_phonenum` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_state` tinyint(1) NULL DEFAULT 0,
  `user_qq` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_uid` varchar(18) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_region` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `visible` tinyint(255) NULL DEFAULT NULL,
  `ctime` datetime(0) NOT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `dtime` datetime(0) NULL DEFAULT NULL,
  `status` decimal(2, 0) NOT NULL DEFAULT 99,
  `user_logtimes` int(11) NULL DEFAULT NULL,
  `user_lastip` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_lastlogintime` datetime(0) NULL DEFAULT NULL,
  `user_lastackqstring` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_lastackurl` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `avos_session_token` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_lastacktime` datetime(0) NULL DEFAULT NULL,
  `user_lastackdevice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `user_signup_user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `auth_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `access_token` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `tgt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `st` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `sid` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `avos_token` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `referral_code` int(6) NULL DEFAULT NULL,
  `user_lastaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `necessary` tinyint(1) NULL DEFAULT 0,
  `work_time` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `attendance_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE,
  INDEX `FK_Relationship_2`(`role_id`) USING BTREE,
  INDEX `company_id`(`company_id`) USING BTREE,
  INDEX `department_id`(`department_id`) USING BTREE,
  INDEX `user_name`(`user_name`) USING BTREE,
  INDEX `referral_code`(`referral_code`) USING BTREE,
  INDEX `referral_code_2`(`referral_code`) USING BTREE,
  INDEX `user_phonenum`(`user_phonenum`) USING BTREE,
  CONSTRAINT `FK_Relationship_2` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_user_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_user_ibfk_3` FOREIGN KEY (`department_id`) REFERENCES `t_department` (`department_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_user_attendance
-- ----------------------------
DROP TABLE IF EXISTS `t_user_attendance`;
CREATE TABLE `t_user_attendance`  (
  `attendance_id` int(11) NOT NULL AUTO_INCREMENT,
  `attendance_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `attendance_time` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `monday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `tuesday` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  `wednesday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `thursday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `friday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `saturday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `sunday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  PRIMARY KEY (`attendance_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_work
-- ----------------------------
DROP TABLE IF EXISTS `t_work`;
CREATE TABLE `t_work`  (
  `work_id` int(11) NOT NULL AUTO_INCREMENT,
  `work_pid` int(11) NULL DEFAULT NULL,
  `work_children_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `affair_id` int(11) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `work_type` int(11) NULL DEFAULT NULL,
  `reminder_id` int(11) NULL DEFAULT NULL,
  `image_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `voice_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `content_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `work_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `work_object_type` tinyint(1) NULL DEFAULT 1,
  `work_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `work_theme` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `affair_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`work_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_work_frequently
-- ----------------------------
DROP TABLE IF EXISTS `t_work_frequently`;
CREATE TABLE `t_work_frequently`  (
  `work_frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `affair_id` int(11) NOT NULL,
  `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `work_type_notice` tinyint(1) NULL DEFAULT 0,
  `work_type_instructions` tinyint(1) NULL DEFAULT 0,
  `work_type_Assign` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`work_frequently_id`) USING BTREE,
  INDEX `affair_id`(`affair_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `t_work_frequently_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `t_work_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

-- ----------------------------
-- Table structure for t_work_reminder
-- ----------------------------
DROP TABLE IF EXISTS `t_work_reminder`;
CREATE TABLE `t_work_reminder`  (
  `work_id` int(11) NOT NULL,
  `reply_id` int(11) NULL DEFAULT NULL,
  `set_object_type` tinyint(1) NULL DEFAULT NULL,
  `set_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `set_frequency` int(11) NULL DEFAULT 0,
  `set_interval` int(11) NULL DEFAULT NULL,
  `set_interval_unit` tinyint(1) NULL DEFAULT NULL,
  `reminder_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reminder_frequency` int(11) NULL DEFAULT 0,
  `reminder_time` datetime(0) NULL DEFAULT NULL,
  `is_reminder` tinyint(1) NULL DEFAULT 0,
  PRIMARY KEY (`work_id`) USING BTREE,
  CONSTRAINT `t_work_reminder_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `t_work` (`work_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_work_reply
-- ----------------------------
DROP TABLE IF EXISTS `t_work_reply`;
CREATE TABLE `t_work_reply`  (
  `reply_id` int(11) NOT NULL AUTO_INCREMENT,
  `reply_pid` int(11) NULL DEFAULT NULL,
  `work_id` int(11) NULL DEFAULT NULL,
  `reply_user_id` int(11) NULL DEFAULT NULL,
  `reply_txt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_affair_old_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_affair_new_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_time` datetime(0) NULL DEFAULT NULL,
  `reply_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_voice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `reply_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`reply_id`) USING BTREE,
  INDEX `work_id`(`work_id`) USING BTREE,
  CONSTRAINT `t_work_reply_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `t_work` (`work_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_process_in_calculation
-- ----------------------------
DROP TABLE IF EXISTS `lacew_mi_prod`.`t_process_in_calculation`;
CREATE TABLE `lacew_mi_prod`.`t_process_in_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `affair_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_key` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_time` datetime(0) NOT NULL,
  `product_id` int(11) NOT NULL,
  `collection_id` int(11) NOT NULL,
  `product_color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_color_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sale_object` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_width` decimal(14, 2) NOT NULL,
  `product_height` decimal(14, 2) NOT NULL,
  `machining_price` decimal(15, 3) NOT NULL,
  `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `actual_in_storehouse_quantity` decimal(22, 2) NOT NULL,
  `call_time` datetime(0) NOT NULL,
  PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX `data_id`(`data_id`) USING BTREE,
  INDEX `affair_id`(`affair_id`, `calculation_time`, `calculation_condition`, `tagIds`, `calculation_key`, `product_id`, `product_color`, `sale_object`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_process_out_calculation
-- ----------------------------
DROP TABLE IF EXISTS `lacew_mi_prod`.`t_process_out_calculation`;
CREATE TABLE `lacew_mi_prod`.`t_process_out_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `affair_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `calculation_time` datetime(0) NOT NULL,
  `machining_price` decimal(15, 3) NOT NULL,
  `machining_quantity` decimal(15, 2) NOT NULL,
  `machining_total_amount` decimal(15, 3) NOT NULL,
  `out_storehouse_quantity` decimal(22, 2) NOT NULL,
  `from_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `is_complete` int(1) NOT NULL DEFAULT 0,
  `call_time` datetime(0) NOT NULL,
  `clearing_storehouse` int(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`calculation_id`, `data_id`, `from_data_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX `affair_id`(`affair_id`, `calculation_time`, `calculation_condition`, `tagIds`, `from_data_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_machine_calculation
-- ----------------------------
DROP TABLE IF EXISTS `t_machine_calculation`;
CREATE TABLE `lacew_mi_prod`.`t_machine_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `affair_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `calculation_condition` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `call_time` datetime(0) NOT NULL,
  `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `calculation_time` datetime(0) NOT NULL ,
  `calculation_time2` datetime(0) NOT NULL ,
  `product_id` int(11) NOT NULL DEFAULT 0,
  `single_weight` decimal(22, 2) NULL DEFAULT 0.00 ,
  `pipe_weight` decimal(22, 2) NULL DEFAULT 0.00 ,
  `machine_horse` bigint(22) NULL DEFAULT NULL,
  `pan_head_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `public_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX ` calculation_value`(`affair_id`, `calculation_key`, `calculation_condition`, `calculation_time`, `product_id`, `tagIds`, `calculation_time2`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_ticket_calculation
-- ----------------------------
DROP TABLE IF EXISTS `t_ticket_calculation`;
CREATE TABLE `lacew_mi_prod`.`t_ticket_calculation`  (
  `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `affair_id` int(11) NOT NULL,
  `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `calculation_condition` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `calculation_time` datetime(0) NOT NULL ,
  `calculation_value` decimal(22, 6) NOT NULL DEFAULT 0.000000 ,
  `call_time` datetime(0) NOT NULL,
  `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ticket_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `ticket_time` datetime(0) NOT NULL ,
  PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  INDEX `call_time`(`call_time`) USING BTREE,
  INDEX ` calculation_value`(`affair_id`, `calculation_key`, `calculation_condition`, `calculation_time`, `tagIds`, `ticket_key`, `ticket_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_customer_given
-- ----------------------------
DROP TABLE IF EXISTS `t_customer_given`;
CREATE TABLE `t_customer_given` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `givenKey` varchar(50) NOT NULL,
  `companyId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_customer_visible
-- ----------------------------
DROP TABLE IF EXISTS `t_customer_visible`;
CREATE TABLE `t_customer_visible` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `objectKey` varchar(50) NOT NULL,
  `companyId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`,`objectKey`) USING BTREE,
  CONSTRAINT `t_customer_visible_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Function structure for Fun_GetPY
-- ----------------------------
SET GLOBAL log_bin_trust_function_creators = 1;
delimiter ;;
CREATE FUNCTION IF NOT EXISTS `Fun_GetPY`(in_string VARCHAR(255) CHARSET utf8)
 RETURNS varchar(255) CHARSET utf8
BEGIN
#截取字符串，每次做截取后的字符串存放在该变量中，初始为函数参数in_string值
DECLARE tmp_str VARCHAR(255) CHARSET gbk DEFAULT '' ; 
#tmp_str的长度
DECLARE tmp_len SMALLINT DEFAULT 0;
#tmp_str的长度
DECLARE tmp_loc SMALLINT DEFAULT 0;
#截取字符，每次 left(tmp_str,1) 返回值存放在该变量中
DECLARE tmp_char VARCHAR(2) CHARSET gbk DEFAULT '';
#结果字符串
DECLARE tmp_rs VARCHAR(255)CHARSET gbk DEFAULT '';
#拼音字符，存放单个汉字对应的拼音首字符
DECLARE tmp_cc VARCHAR(2) CHARSET gbk DEFAULT '';
#初始化，将in_string赋给tmp_str
SET tmp_str = in_string;
#初始化长度
SET tmp_len = LENGTH(tmp_str);
#如果被计算的tmp_str长度大于0则进入该while
WHILE tmp_len > 0 DO 
#获取tmp_str最左端的首个字符，注意这里是获取首个字符，该字符可能是汉字，也可能不是。
SET tmp_char = LEFT(tmp_str,1);
#左端首个字符赋值给拼音字符
SET tmp_cc = tmp_char;
#获取字符的编码范围的位置，为了确认汉字拼音首字母是那一个
SET tmp_loc=INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC
,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1);
#判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。如果是多字节字符但是不在对应的编码范围之内，即对应的不是大写字母则也不做处理，这样数字或者特殊字符就保持原样了
IF (LENGTH(tmp_char)>1 AND tmp_loc>0 AND tmp_loc<24) THEN
#获得汉字拼音首字符
SELECT ELT(tmp_loc,'a','b','c','d','e','f','g','h','j','k','l','m','n','o','p','q','r','s','t','w','x','y','z') INTO tmp_cc; 
END IF;
IF ASCII(tmp_cc)>96 AND ASCII(tmp_cc) <123
THEN
#将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
SET tmp_rs = CONCAT(tmp_rs,tmp_cc);
ELSE
SET tmp_rs = CONCAT(tmp_rs,tmp_char);
END IF;
#将tmp_str左端首字符去除
SET tmp_str = SUBSTRING(tmp_str,2);
#计算当前字符串长度
SET tmp_len = LENGTH(tmp_str);
END WHILE;
#返回结果字符串
RETURN tmp_rs;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;