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

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`

SET FOREIGN_KEY_CHECKS=1;