lacemi/data/upgrade/v2.0.0/v2.0.0.sql

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `documentary_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' AFTER `documentary_id`;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `documentary_number_pid` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '' AFTER `documentary_number`;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `documentary_product_id` int(11) NULL DEFAULT NULL AFTER `documentary_number_pid`;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `documentary_object` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' AFTER `documentary_product_id`;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '' AFTER `documentary_state`;

ALTER TABLE `t_documentary` ADD COLUMN IF NOT EXISTS `companyId` int(11) NULL DEFAULT NULL AFTER `data_id`;

ALTER TABLE `t_documentary` MODIFY COLUMN IF EXISTS `documentary_id` int(11) NOT NULL FIRST;

ALTER TABLE `t_documentary` DROP PRIMARY KEY;

ALTER TABLE `t_documentary` ADD PRIMARY KEY (`documentary_id`, `documentary_number`) USING BTREE;

ALTER TABLE `t_documentary` ADD UNIQUE INDEX IF NOT EXISTS `documentary_number`(`documentary_number`) USING BTREE;

ALTER TABLE `t_documentary` MODIFY COLUMN IF EXISTS `documentary_id` int(11) NOT NULL AUTO_INCREMENT FIRST;

CREATE TABLE IF NOT EXISTS `t_documentary_statistics`  (
  `statistics_id` int(11) NOT NULL AUTO_INCREMENT,
  `statistics_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `statistics_number` int(11) NOT NULL DEFAULT 0,
  `permission` tinyint(1) NULL DEFAULT 0,
  `user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `companyId` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`statistics_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

CREATE TABLE IF NOT EXISTS `t_documentary_step`  (
  `step_id` int(11) NOT NULL AUTO_INCREMENT,
  `step_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `step_number` int(11) NOT NULL,
  `affairIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `companyId` int(11) NULL DEFAULT 0,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`step_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

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

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

CREATE TABLE IF NOT EXISTS  `t_report_account`  (
  `account_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `account_bank_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `account_bank_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `account_cumulative` double(13, 2) NULL DEFAULT 0,
  `ctime` datetime(0) NULL DEFAULT NULL,
  `utime` datetime(0) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `permission` tinyint(1) NULL DEFAULT 1,
  `see_user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`account_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

ALTER TABLE `t_report_forms` ADD COLUMN IF NOT EXISTS `set_company_account` tinyint(1) NULL DEFAULT 2 AFTER `set_achievements`;

ALTER TABLE `t_statement_calculation` ADD COLUMN IF NOT EXISTS `calculation_account_category_out` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `calculation_product_color`;
ALTER TABLE `t_statement_calculation` ADD COLUMN IF NOT EXISTS `calculation_receivables_payment_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `calculation_account_category_out`;
ALTER TABLE `t_statement_calculation` ADD COLUMN IF NOT EXISTS `receivables_mode`  tinyint(1) NOT NULL AFTER `calculation_receivables_payment_category`;
ALTER TABLE `t_statement_calculation` ADD COLUMN IF NOT EXISTS `bill_order_number`  varchar(255) NULL  AFTER `receivables_mode`;

ALTER TABLE `t_affair` ADD COLUMN IF NOT EXISTS `lock_user` int(11) NULL DEFAULT NULL AFTER `lock_time`;

ALTER TABLE `t_bill` ADD COLUMN IF NOT EXISTS `bill_number_user_defined` varchar(255) NULL AFTER `bill_name`;

ALTER TABLE `t_bill_order` ADD COLUMN IF NOT EXISTS `bill_amount` decimal(18, 3) NULL DEFAULT NULL AFTER `bill_object`;

UPDATE `t_affair_system_column` SET `column_control` = 35 WHERE `column_id` = 247;

ALTER TABLE `t_bill_order`
ADD COLUMN IF NOT EXISTS `bill_pnames` text NULL AFTER `bill_amount`;

CREATE TABLE IF NOT EXISTS `t_inventory_frequently`  (
  `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `inventory_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
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

CREATE TABLE IF NOT EXISTS `t_inventory_notice`  (
  `notice_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `data_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `data_count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`notice_id`) USING BTREE,
  UNIQUE INDEX `frequently_id`(`notice_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

CREATE TABLE IF NOT EXISTS `t_inventory_notice_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notice_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `notice_state` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `id`(`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

CREATE TABLE IF NOT EXISTS `t_screen`  (
  `screen_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `column_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `utime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `screen_type` tinyint(1) NULL DEFAULT 1,
  UNIQUE INDEX `frequently_id`(`screen_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

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

UPDATE `t_report_forms` SET `report_forms_title`='账户金额核算表' WHERE (`report_forms_title`='帐户金额核算表') LIMIT 1;

ALTER TABLE `t_statement_calculation` MODIFY COLUMN IF EXISTS `calculation_ctime` datetime(0) NULL DEFAULT NULL AFTER `day`;

ALTER TABLE `t_documentary_step`
ADD COLUMN IF NOT EXISTS `tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL AFTER `utime`;

ALTER TABLE `t_report_account`
MODIFY COLUMN IF EXISTS  `account_cumulative` double(13, 2) NULL DEFAULT 0 AFTER `account_bank_number`;

ALTER TABLE `t_user`
ADD COLUMN IF NOT EXISTS `report_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NULL AFTER `report_header`;

CREATE TABLE IF NOT EXISTS `t_user_reminder` (
  `user_id` int(11) NOT NULL,
  `sale_price_reminder` tinyint(1) DEFAULT 0,
  `purchase_price_reminder` tinyint(1) DEFAULT 0,
  `documentary_add_reminder` tinyint(1) DEFAULT 0,
  `documentary_add_son_reminder` tinyint(1) DEFAULT 0,
  `documentary_join_reminder` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;

ALTER TABLE `t_user`
ADD COLUMN IF NOT EXISTS `report_password_type` tinyint(1) NULL DEFAULT 0 AFTER `report_password`;

ALTER TABLE `t_user_reminder`
ADD COLUMN IF NOT EXISTS `documentary_new_affair_join_reminder` tinyint(1) NULL DEFAULT 0 AFTER `documentary_join_reminder`;

ALTER TABLE `t_user_reminder`
ADD COLUMN IF NOT EXISTS `product_image_shear`  tinyint(1) NULL DEFAULT 0 AFTER `documentary_new_affair_join_reminder`;

ALTER TABLE `t_user_reminder`
ADD COLUMN IF NOT EXISTS `documentary_copy_order`  tinyint(1) NULL DEFAULT 0 AFTER `product_image_shear`;

ALTER TABLE `t_statement_calculation`
ADD COLUMN IF NOT EXISTS `tag_numbers` tinyint(1) NULL DEFAULT 0 AFTER `bill_order_number`;

ALTER TABLE `t_ticket_calculation`
ADD COLUMN IF NOT EXISTS `tag_numbers` tinyint(1) NULL DEFAULT 0 AFTER `calculation_note`;

ALTER TABLE `t_process_in_calculation`
ADD COLUMN IF NOT EXISTS `tag_numbers` tinyint(1) NULL DEFAULT 0 AFTER `call_time`;

ALTER TABLE `t_process_out_calculation`
ADD COLUMN IF NOT EXISTS `tag_numbers` tinyint(1) NULL DEFAULT 0 AFTER `clearing_storehouse`;

ALTER TABLE `t_inventory_calculation`
ADD COLUMN IF NOT EXISTS `tag_numbers` tinyint(1) NULL DEFAULT 0 AFTER `self_built_source`;

ALTER TABLE `t_inventory_calculation`
ADD COLUMN IF NOT EXISTS `from_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `tag_numbers`;

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

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

CREATE TABLE IF NOT EXISTS `t_product_share`  (
  `share_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `product_ids` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `company_id` int(11) NULL DEFAULT NULL,
  `title` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `share_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `imgUrl` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `share_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `share_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`share_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

ALTER TABLE `t_file_list`
MODIFY COLUMN IF EXISTS `template_number` varchar(50) NULL DEFAULT '' AFTER `user_id`;

CREATE TABLE IF NOT EXISTS `t_product_template` (
  `template_md5` varchar(50) COLLATE utf8_bin NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `template_imgUrl` varchar(255) COLLATE utf8_bin DEFAULT '',
  `template_number` varchar(25) COLLATE utf8_bin DEFAULT '',
  `template_color` varchar(50) COLLATE utf8_bin DEFAULT '',
  PRIMARY KEY (`template_md5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;

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

REPLACE INTO `t_affair_system_column_group` (`group_id`, `group_name`) VALUES ('10', '成本');

ALTER TABLE `t_product_share`
ADD COLUMN IF NOT EXISTS `hits` int(11) NULL DEFAULT 0 AFTER `share_password`;

ALTER TABLE `t_product_share`
MODIFY COLUMN IF EXISTS `ctime`  datetime NULL AFTER `title`;

ALTER TABLE `t_inventory`
ADD COLUMN IF NOT EXISTS `warehouse_other_description`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `inventory_quantity_unit`;

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 (332, 0, 8, 31, '暂停时间', 'suspended_time', '[]', '{\"key\":\"date_time\",\"value\":\"1\"}', 6, '', 0, '', 0, 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 (333, 0, 8, 31, '启动时间', 'activate_time', '[]', '{\"key\":\"date_time\",\"value\":\"1\"}', 6, '', 0, '', 0, 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 (334, 221, 6, 999910, '其他描述', 'warehouse_other_description', '[{\"key\":\"all\",\"value\":\"\"}]', '{\"key\":\"string\",\"value\":\"255\"}', 1, '{\"regex\":\"\",\"tips\":\"设置其他描述\"}', 0, '', 0, 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 (17, 15, 2, 9999, '手机号码', 'sale_customer_phone', '[]', '{\"key\":\"integer\",\"value\":\"\"}', 0, '{\"regex\":\"/^1\\d{10}$/\",\"tips\":\"填入11位手机号码\"}', 0, '', 0, 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 (235, 221, 6, 999912, '库存数量', 'warehouse_inventory_quantity', '[]', '{\"key\":\"string\",\"value\":\"255\"}', -1, '{\"regex\":\"\",\"tips\":\"\"}', 0, '', 0, 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 (247, 0, 7, 2, '账户', 'account_category', '[{\"key\":\"oneself\",\"value\":\"\"}]', '{\"key\":\"string\",\"value\":\"50\"}', 35, '{\"regex\":\"\",\"tips\":\"请选择账户\"}', 0, '', 0, 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 (331, 0, 5, 12, '停机事由', 'stop_reason_name', '[{\"key\":\"stop_reason_id\"}]', '{\"key\":\"stop_reason_id\",\"value\":\"stop_reason_name\"}', 39, '{\"regex\":\"\",\"tips\":\"选择暂停事由\"}', 0, '', 0, NULL);

ALTER TABLE `t_inventory_record`
ADD COLUMN IF NOT EXISTS `user_id`  int(11) NULL AFTER `utime`;

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 (335, 0, 9, 15, '核对数量', 'check_amount', '[{\"key\":\"kg\",\"value\":\"公斤\"},{\"key\":\"yard\",\"value\":\"码\"},{\"key\":\"one\",\"value\":\"条\"},{\"key\":\"m\",\"value\":\"米\"}]', '{\"key\":\"double\",\"value\":\"2\"}', 1, '{\"regex\":\"/^[0-9]+(.[0-9]{0,2})?$/ \",\"tips\":\"只能为数字和2位小数点\"}', 0, '', 0, NULL);


SET FOREIGN_KEY_CHECKS=1;