其他 #440
Updated by Junyi Zhang 3 months ago
*Background*
# New requirement for v4.0 OTS
See: OneDrive\赤蓝商贸（上海）有限公司\Shanghai Group - IT\开发相关\OTS V4\开发需求
*Task Details*
* *Rule (v4.0)*
# %{color:red}DONE%: ENGINE = MyISAM -> InnoDB
## 修改date/datetime的值 0000-0000-000 -> null (遇到在修改)
<pre>
sample:
1. 查询所有默认值为0000-00-00 / 0000-00-00 00:00:00的表
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM information_schema.columns
WHERE column_default = '0000-00-00'
AND table_schema = 'redblue_ots4.0';
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM information_schema.columns
WHERE column_default = '0000-00-00 00:00:00'
AND table_schema = 'redblue_ots4.0';
2. 修改列：允许为null，默认值为null
ALTER TABLE `ots_product_discount`
CHANGE COLUMN `date_start` `date_start` DATE NULL DEFAULT NULL AFTER `price`,
CHANGE COLUMN `date_end` `date_end` DATE NULL DEFAULT NULL AFTER `date_start`;
Note: 已修改的表
ots_zone_to_geo_zone (如果数据是 0000-00-00 00:00:00的话，需要修改成有效的 datetime，然后执行以上SQL)
ots_return
ots_product
ots_product_special
ots_product_discount
ots_coupon
</pre>
## 修改表（一个一个执行）的ENGINE = MyISAM -> InnoDB
<pre>
sample:
1. 先查需要修改的表
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = 'redblue_ots4.0'
AND engine = 'MyISAM';
2. MyISAM -> InnoDB
ALTER TABLE `ots_order_status` ENGINE=InnoDB;
</pre>
# %{color:red}DONE%: Don’t need to specify the length or precision for your *numeric* data. (遇到在修改)
<pre>
SELECT table_name, column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'redblue_ots4.0'
AND data_type IN ('tinyint','int', 'bigint', 'decimal', 'numeric', 'float', 'double')
AND (numeric_precision IS NOT NULL OR numeric_scale IS NOT NULL);
</pre>
# %{color:red}DONE%: Set the default text type obviously -> utf8mb4_general_ci
<pre>
1. 查询表COLLATE为'utf8mb3_general_ci'的表
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'redblue_ots4.0'
AND table_collation = 'utf8mb3_general_ci';
2. 修改：utf8mb3_general_ci -> UTF8MB4_GENERAL_CI
ALTER TABLE `ots_unit_class`
CONVERT TO CHARACTER SET utf8mb4
COLLATE UTF8MB4_GENERAL_CI;
</pre>
# %{color:red}TO-DO%: 需要维护v3数据库
* *Record all SQL changes (v4)*: ENGINE = MyISAM -> InnoDB
|. Issue |. SQL |
| #441 | <pre>
ALTER TABLE `ots_order_status`
ADD COLUMN `code` INT NOT NULL AFTER `name`,
ADD COLUMN `hex` VARCHAR(32) NOT NULL DEFAULT '' AFTER `code`,
ALTER TABLE ots_order_status
ADD COLUMN status TINYINT NOT NULL DEFAULT '1' AFTER hex;
ALTER TABLE ots_order_status
ADD COLUMN shipped TINYINT NOT NULL DEFAULT '0' AFTER hex,
ADD COLUMN sort_order INT NOT NULL DEFAULT (0) AFTER shipped;
</pre> |
| #454 |
<pre>
CREATE TABLE `ots_unit_class` (
`unit_class_id` INT NOT NULL AUTO_INCREMENT,
`status` TINYINT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`unit_class_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4_general_ci;
CREATE TABLE `ots_unit_class_description` (
`unit_class_id` INT NOT NULL AUTO_INCREMENT,
`language_id` INT NOT NULL,
`title` VARCHAR(32) NOT NULL,
`unit` VARCHAR(4) NOT NULL,
PRIMARY KEY (`unit_class_id`, `language_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4_general_ci;
---------------------------------------------------------
ALTER TABLE `ots_manufacturer`
ADD COLUMN `status` TINYINT NOT NULL DEFAULT (0) AFTER `manufacturer_id`,
ADD COLUMN `country_id` INT NOT NULL AFTER `name`,
ADD COLUMN `zone_id` INT NOT NULL AFTER `country_id`,
ADD COLUMN `address_1` VARCHAR(128) NOT NULL DEFAULT '' AFTER `zone_id`,
ADD COLUMN `address_2` VARCHAR(128) NOT NULL DEFAULT '' AFTER `address_1`,
DROP COLUMN `image`;
ALTER TABLE `ots_manufacturer`
ADD COLUMN `city` VARCHAR(128) NOT NULL DEFAULT '' AFTER `zone_id`;
DROP TABLE `ots_manufacturer_to_store`;
ALTER TABLE `ots_category`
DROP COLUMN `image`,
DROP COLUMN `top`,
DROP COLUMN `column`;
ALTER TABLE `ots_category_description`
DROP COLUMN `meta_title`,
DROP COLUMN `meta_description`,
DROP COLUMN `meta_keyword`;
DROP TABLE `ots_category_filter`;
DROP TABLE `ots_category_to_layout`;
DROP TABLE `ots_category_to_store`;
---------------------------------------------------------
DROP TABLE `ots_option`;
CREATE TABLE `ots_option` (
`option_id` INT NOT NULL AUTO_INCREMENT,
`et_option_id` INT NOT NULL DEFAULT '0',
`type` VARCHAR(32) NOT NULL,
`option_delivery_min` INT NOT NULL DEFAULT '0',
`option_delivery_max` INT NOT NULL DEFAULT '0',
`delivery` TINYINT NOT NULL DEFAULT '0',
`extra` TINYINT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL,
PRIMARY KEY (`option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
DROP TABLE `ots_option_description`;
CREATE TABLE `ots_option_description` (
`option_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(128) NOT NULL,
`short_text` TEXT NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`option_id`, `language_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
DROP TABLE `ots_option_value`;
CREATE TABLE `ots_option_value` (
`option_value_id` INT NOT NULL AUTO_INCREMENT,
`et_option_value_id` INT NOT NULL DEFAULT '0',
`option_id` INT NOT NULL,
`image` VARCHAR(255) NOT NULL,
`class` VARCHAR(45) NOT NULL DEFAULT '',
`option_value_price` VARCHAR(255) NULL DEFAULT NULL,
`color_id` INT NULL DEFAULT NULL,
`option_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`option_value_startcost_price` VARCHAR(255) NULL DEFAULT NULL,
`option_value_startcost_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`option_value_startcost_quantity` INT NOT NULL,
`option_value_startcost_quantity_display` TINYINT NOT NULL DEFAULT '1',
`option_value_delivery_min` INT NOT NULL DEFAULT '0',
`option_value_delivery_max` INT NOT NULL DEFAULT '0',
`branding` TINYINT NOT NULL DEFAULT '0',
`branding_group_id` INT NOT NULL DEFAULT '0',
`design_type` ENUM('upload','proof','online') NULL DEFAULT NULL,
`sort_order` INT NOT NULL,
PRIMARY KEY (`option_value_id`),
INDEX `option_id` (`option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
DROP TABLE `ots_option_value_description`;
CREATE TABLE `ots_option_value_description` (
`option_value_id` INT NOT NULL,
`language_id` INT NOT NULL,
`option_id` INT NOT NULL,
`delivery_service` VARCHAR(128) NOT NULL,
`name` VARCHAR(128) NOT NULL,
`video_id` VARCHAR(64) NOT NULL,
`addition` VARCHAR(50) NOT NULL,
`startcost` VARCHAR(255) NOT NULL,
`text` TEXT NOT NULL,
PRIMARY KEY (`option_value_id`, `language_id`),
INDEX `option_id` (`option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
CREATE TABLE `ots_branding_group` (
`branding_group_id` INT NOT NULL AUTO_INCREMENT,
`image` VARCHAR(255) NOT NULL DEFAULT '',
`display` TINYINT NOT NULL DEFAULT '0',
`status` TINYINT NOT NULL DEFAULT '1',
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`branding_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
CREATE TABLE `ots_branding_group_description` (
`branding_group_id` INT NOT NULL,
`language_id` INT NOT NULL,
`title` VARCHAR(64) NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`branding_group_id`, `language_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
ALTER TABLE ots_option AUTO_INCREMENT = 10000;
ALTER TABLE ots_option_value AUTO_INCREMENT = 1000000;
ALTER TABLE ots_branding_group AUTO_INCREMENT = 10000;
---------------------------------------------------------
DROP TABLE `ots_product_attribute`;
DROP TABLE `ots_product_filter`;
DROP TABLE `ots_product_recurring`;
DROP TABLE `ots_product_related`;
DROP TABLE `ots_product_reward`;
DROP TABLE `ots_product_special`;
DROP TABLE `ots_product_to_download`;
DROP TABLE `ots_product_to_layout`;
DROP TABLE `ots_product_to_store`;
DROP TABLE `ots_product`;
CREATE TABLE `ots_product` (
`product_id` INT NOT NULL AUTO_INCREMENT,
`et_product_id` INT NOT NULL,
`model` VARCHAR(64) NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`product_group_id` INT NOT NULL DEFAULT '0',
`tax_class_id` INT NOT NULL,
`date_available` DATE NULL DEFAULT NULL,
`date_unavailable` DATE NULL DEFAULT NULL,
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` INT NOT NULL DEFAULT '0',
`length` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`width` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`height` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`length_class_id` INT NOT NULL DEFAULT '0',
`unit_class_id` INT NOT NULL DEFAULT '0',
`unit_price` TINYINT NOT NULL DEFAULT '1',
`price_label` ENUM('min','max') NOT NULL DEFAULT 'max',
`delivery_min` INT NOT NULL DEFAULT '0',
`delivery_max` INT NOT NULL DEFAULT '0',
`declaration_cn_hs_code` VARCHAR(64) NOT NULL,
`declaration_se_hs_code` VARCHAR(64) NOT NULL,
`declaration_g_w_g` VARCHAR(64) NOT NULL,
`declaration_w_v` VARCHAR(64) NOT NULL,
`sort_order` INT NOT NULL DEFAULT '0',
`status` TINYINT NOT NULL DEFAULT '0',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`product_id`),
UNIQUE INDEX `product_id` (`product_id`),
INDEX `model` (`model`),
INDEX `sort_order` (`sort_order`),
INDEX `status` (`status`),
INDEX `date_available` (`date_available`),
INDEX `date_unavailable` (`date_unavailable`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=90000
;
DROP TABLE `ots_product_description`;
CREATE TABLE `ots_product_description` (
`product_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(255) NOT NULL,
`short_name` VARCHAR(100) NOT NULL,
`declaration_name` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`product_id`, `language_id`),
INDEX `name` (`name`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_product_to_manufacturer` (
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`declaration_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`minimum` INT NOT NULL DEFAULT '1',
`quantity_start` INT NOT NULL DEFAULT '0',
`price_calculation` TINYINT NOT NULL DEFAULT '0',
`price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`price_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`campaign_price` VARCHAR(255) NOT NULL,
`campaign_type` ENUM('fixed','percent') NULL DEFAULT 'percent',
`campaign_end` DATE NULL DEFAULT NULL,
`campaign_base` TINYINT NOT NULL DEFAULT '0',
`price_min` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`price_min_branding` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`formula` VARCHAR(255) NOT NULL DEFAULT '',
`depth_enable` TINYINT NOT NULL DEFAULT '0',
`depth_max` INT NOT NULL DEFAULT '0',
`depth_min` INT NOT NULL DEFAULT '0',
`height_enable` TINYINT NOT NULL DEFAULT '0',
`height_max` INT NOT NULL DEFAULT '0',
`width_enable` TINYINT NOT NULL DEFAULT '0',
`height_min` INT NOT NULL DEFAULT '0',
`width_max` INT NOT NULL DEFAULT '0',
`width_min` INT NOT NULL DEFAULT '0',
`length_enable` TINYINT NOT NULL DEFAULT '0',
`length_min` INT NOT NULL DEFAULT '0',
`length_max` INT NOT NULL DEFAULT '0',
`startcost_enabled` TINYINT NOT NULL DEFAULT '0',
`startcost_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`startcost_type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`startcost_quantity` INT NOT NULL DEFAULT '0',
`startcost_dependant_option_enabled` TINYINT NOT NULL DEFAULT '0',
`startcost_dependant_option_id` INT NOT NULL DEFAULT '0',
`startcost_dependant_option_value_id` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`, `manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_product_to_manufacturer_description` (
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`language_id` INT NOT NULL,
`startcost` VARCHAR(255) NOT NULL,
PRIMARY KEY (`product_id`, `manufacturer_id`, `language_id`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_product_group` (
`product_group_id` INT NOT NULL AUTO_INCREMENT,
`prefix` VARCHAR(20) NOT NULL,
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=1000
;
CREATE TABLE `ots_product_group_description` (
`product_group_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`product_group_id`, `language_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE `ots_product_option`;
CREATE TABLE `ots_product_option` (
`product_option_id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`option_id` INT NOT NULL,
`value` TEXT NOT NULL,
`required` TINYINT NOT NULL,
`status` TINYINT NOT NULL DEFAULT '1',
`hidden` TINYINT NOT NULL DEFAULT '0',
`display` TINYINT NOT NULL DEFAULT '1',
`connect` TINYINT NOT NULL DEFAULT '0',
`color_type` TINYINT NOT NULL DEFAULT '0',
`color_display` TINYINT NOT NULL DEFAULT '0',
`color_preview` TINYINT NOT NULL DEFAULT '1',
`surface_id` TINYINT NOT NULL DEFAULT '0',
`product_image_id` INT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_option_id`),
INDEX `option_id` (`option_id`),
INDEX `product_id` (`product_id`),
INDEX `manufacturer_id` (`manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_product_option_description` (
`product_option_description_id` INT NOT NULL AUTO_INCREMENT,
`product_option_id` INT NOT NULL,
`product_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL DEFAULT '',
`description` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`product_option_description_id`),
INDEX `product_id` (`product_id`),
INDEX `language_id` (`language_id`),
INDEX `product_option_id` (`product_option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=10000000
;
DROP TABLE `ots_product_option_value`;
CREATE TABLE `ots_product_option_value` (
`product_option_value_id` INT NOT NULL AUTO_INCREMENT,
`product_option_id` INT NOT NULL,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`option_id` INT NOT NULL,
`option_value_id` INT NOT NULL,
`quantity` INT NOT NULL,
`subtract` TINYINT NOT NULL,
`price` VARCHAR(255) NOT NULL DEFAULT '0.0000',
`type` ENUM('fixed','percent','formula') NOT NULL,
`price_prefix` VARCHAR(1) NOT NULL,
`points` INT NOT NULL,
`points_prefix` VARCHAR(1) NOT NULL,
`minimum` INT NOT NULL DEFAULT '1',
`weight` DECIMAL(15,8) NOT NULL,
`weight_prefix` VARCHAR(1) NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`extra_option_enabled` TINYINT NOT NULL,
`dependant_option_enabled` TINYINT NOT NULL DEFAULT '0',
`dependant_option_id` INT NOT NULL DEFAULT '0',
`dependant_option_value_id` INT NOT NULL DEFAULT '0',
`product_price_enabled` TINYINT NOT NULL,
`delivery_enabled` TINYINT NOT NULL,
`delivery_max` INT NOT NULL,
`startcost_enabled` TINYINT NOT NULL,
`startcost_price` VARCHAR(255) NOT NULL DEFAULT '0.0000',
`startcost_type` ENUM('fixed','percent','formula') NOT NULL,
`startcost_quantity` INT NOT NULL,
`startcost_dependant_option_enabled` TINYINT NOT NULL DEFAULT '0',
`startcost_dependant_option_id` INT NOT NULL DEFAULT '0',
`startcost_dependant_option_value_id` INT NOT NULL DEFAULT '0',
`startcost_product_price_enabled` TINYINT NOT NULL DEFAULT '0',
`info_enabled` TINYINT NOT NULL,
`image_layer` VARCHAR(255) NOT NULL DEFAULT '',
`image_layer_color_id` INT NOT NULL DEFAULT '0',
`image_z_index` TINYINT NOT NULL DEFAULT '0',
`image_hover` VARCHAR(255) NOT NULL DEFAULT '',
`image_hover_description` VARCHAR(255) NOT NULL DEFAULT '',
`product_effect_id` INT NULL DEFAULT NULL,
`logoarea_enabled` TINYINT NOT NULL,
`surface_id` TINYINT NOT NULL DEFAULT '0',
`branding_id` TINYINT NOT NULL DEFAULT '0',
`logotype_id` INT NOT NULL DEFAULT '0',
`status` TINYINT NOT NULL DEFAULT '1',
`default_pantone_color_id` INT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_option_value_id`),
INDEX `product_option_id` (`product_option_id`),
INDEX `product_id` (`product_id`),
INDEX `manufacturer_id` (`manufacturer_id`),
INDEX `option_id` (`option_id`),
INDEX `option_value_id` (`option_value_id`),
INDEX `sort_order` (`sort_order`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_product_option_value_description` (
`product_option_value_description_id` INT NOT NULL AUTO_INCREMENT,
`product_option_value_id` INT NOT NULL,
`product_id` INT NOT NULL,
`language_id` INT NOT NULL,
`startcost` VARCHAR(255) NOT NULL DEFAULT '',
`custom_option_title` VARCHAR(128) NULL DEFAULT '',
PRIMARY KEY (`product_option_value_description_id`),
INDEX `product_id` (`product_id`),
INDEX `language_id` (`language_id`),
INDEX `product_option_value_id` (`product_option_value_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=10000000
;
DROP TABLE `ots_product_discount`;
CREATE TABLE `ots_product_discount` (
`product_discount_id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`quantity_type` ENUM('normal','extra') NOT NULL DEFAULT 'normal',
`priority` INT NOT NULL DEFAULT '1',
`type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`position` INT NOT NULL DEFAULT '0',
`price` VARCHAR(255) NOT NULL DEFAULT '0.0000',
`price_base` TINYINT NOT NULL DEFAULT '0',
`date_start` DATE NULL DEFAULT NULL,
`date_end` DATE NULL DEFAULT NULL,
`discount_price` VARCHAR(255) NOT NULL,
`discount_type` ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
PRIMARY KEY (`product_discount_id`),
INDEX `product_id` (`product_id`),
INDEX `manufacturer_id` (`manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=10000000
;
CREATE TABLE `ots_product_option_value_discount` (
`product_option_value_discount_id` INT NOT NULL AUTO_INCREMENT,
`product_option_value_id` INT NOT NULL,
`quantity_type` ENUM('normal','extra') NOT NULL DEFAULT 'normal',
`price` VARCHAR(255) NOT NULL DEFAULT '0.0000',
`price_base` TINYINT NOT NULL DEFAULT '0',
`type` ENUM('fixed','percent','formula') NOT NULL,
`position` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_option_value_discount_id`),
INDEX `product_option_value_id` (`product_option_value_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=100000000
;
CREATE TABLE `ots_product_extra_option` (
`product_extra_option_id` INT NOT NULL AUTO_INCREMENT,
`product_option_value_id` INT NOT NULL,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`option_id` INT NOT NULL,
`required` TINYINT NOT NULL DEFAULT '0',
`color_type` TINYINT NOT NULL DEFAULT '0',
`color_display` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_extra_option_id`),
INDEX `product_option_value_id` (`product_option_value_id`),
INDEX `option_id` (`option_id`),
INDEX `product_id` (`product_id`),
INDEX `manufacturer_id` (`manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_product_extra_option_value` (
`product_extra_option_value_id` INT NOT NULL AUTO_INCREMENT,
`product_extra_option_id` INT NOT NULL,
`option_value_id` INT NOT NULL,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`option_id` INT NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`price` VARCHAR(255) NOT NULL DEFAULT '0.00',
`type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`default_pantone_color_id` INT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_extra_option_value_id`),
INDEX `product_extra_option_id` (`product_extra_option_id`),
INDEX `option_value_id` (`option_value_id`),
INDEX `sort_order` (`sort_order`),
INDEX `manufacturer_id` (`manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
AUTO_INCREMENT=10000000
;
DROP TABLE `ots_product_to_category`;
CREATE TABLE `ots_product_to_category` (
`product_id` INT NOT NULL,
`category_id` INT NOT NULL,
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`, `category_id`),
INDEX `category_id` (`category_id`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE `ots_product_image`;
CREATE TABLE `ots_product_image` (
`product_image_id` INT NOT NULL AUTO_INCREMENT,
`default` TINYINT NOT NULL DEFAULT '0',
`default_category` TINYINT NOT NULL DEFAULT '0',
`default_category_hover` TINYINT NOT NULL DEFAULT '0',
`product_id` INT NOT NULL,
`image` VARCHAR(255) NULL DEFAULT NULL,
`zoom` FLOAT NOT NULL DEFAULT '1.00',
`layer_image` VARCHAR(255) NULL DEFAULT NULL,
`layer_z_index` TINYINT NOT NULL DEFAULT '0',
`displacement_image` VARCHAR(255) NULL DEFAULT NULL,
`transform` TEXT NULL DEFAULT NULL,
`color_id` INT NULL DEFAULT NULL,
`type` TINYINT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_image_id`),
INDEX `product_id` (`product_id`),
INDEX `color_id` (`color_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_product_image_description` (
`image_description_id` INT NOT NULL AUTO_INCREMENT,
`product_image_id` INT NOT NULL,
`product_id` INT NOT NULL,
`language_id` INT NOT NULL,
`title_text` VARCHAR(255) NULL DEFAULT NULL,
`alt_text` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`image_description_id`),
INDEX `product_id` (`product_id`),
INDEX `product_image_id` (`product_image_id`),
INDEX `language_id` (`language_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10000000
;
CREATE TABLE `ots_product_option_value_hover_description` (
`product_option_value_hover_description_id` INT NOT NULL AUTO_INCREMENT,
`product_option_value_id` INT NOT NULL,
`product_id` INT NOT NULL,
`language_id` INT NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`product_option_value_hover_description_id`),
INDEX `product_id` (`product_id`),
INDEX `language_id` (`language_id`),
INDEX `product_option_value_id` (`product_option_value_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_product_option_value_layer_image` (
`product_option_value_layer_image_id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`product_option_value_id` INT NOT NULL,
`product_image_id` INT NOT NULL DEFAULT '0',
`image_layer` VARCHAR(255) NOT NULL DEFAULT '',
`image_layer_color_id` INT NOT NULL DEFAULT '0',
`image_z_index` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_option_value_layer_image_id`),
INDEX `product_option_value_id` (`product_option_value_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10000000
;
CREATE TABLE `ots_product_square_meter_discount` (
`product_square_meter_discount_id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`manufacturer_id` INT NOT NULL,
`quantity` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`type` ENUM('fixed','percent','formula') NOT NULL DEFAULT 'fixed',
`price` VARCHAR(255) NOT NULL DEFAULT '0.0000',
`position` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`product_square_meter_discount_id`),
INDEX `product_id` (`product_id`),
INDEX `manufacturer_id` (`manufacturer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1000000
;
CREATE TABLE `ots_currency_round` (
`currency_round_id` INT NOT NULL AUTO_INCREMENT,
`currency_id` INT NOT NULL,
`value` FLOAT NOT NULL,
`precision` TINYINT NOT NULL,
PRIMARY KEY (`currency_round_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
--------------------------------------
ALTER TABLE `ots_product`
DROP COLUMN `price_label`;
ALTER TABLE `ots_manufacturer`
ADD COLUMN `origin` VARCHAR(64) NOT NULL AFTER `name`;
ALTER TABLE `ots_product`
ADD COLUMN `declaration_base_info` VARCHAR(255) NOT NULL AFTER `declaration_w_v`, -- 申报要素的基本信息
ADD COLUMN `declaration_option` TINYINT NOT NULL DEFAULT (0) AFTER `declaration_base_info`, -- 申报要素中是否含有尺寸/容量的option
ADD COLUMN `declaration_brand` TINYINT NOT NULL DEFAULT (0) AFTER `declaration_option`; -- 申报要素中是否包含品牌
-----------------------------
CREATE TABLE `ots_color` (
`color_id` INT NOT NULL AUTO_INCREMENT,
`hex` VARCHAR(7) NOT NULL,
`pantone` VARCHAR(64) NOT NULL,
`status` TINYINT NOT NULL DEFAULT '0',
`color_group_id` INT NULL DEFAULT NULL,
`sort_order` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`color_id`),
INDEX `color_group_id` (`color_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10000
;
CREATE TABLE `ots_color_group` (
`color_group_id` INT NOT NULL AUTO_INCREMENT,
`hex` VARCHAR(7) NOT NULL,
`status` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`color_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1000
;
CREATE TABLE `ots_color_group_description` (
`color_group_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`color_group_id`, `language_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
ALTER TABLE `ots_option`
ADD COLUMN `color_change` TINYINT NOT NULL DEFAULT '0' AFTER `type`;
-----------------------------------------------
ALTER TABLE `ots_product`
CHANGE COLUMN `declaration_brand` `declaration_brand` TINYINT NOT NULL DEFAULT '1' AFTER `declaration_option`;
ALTER TABLE `ots_product`
CHANGE COLUMN `declaration_base_info` `declaration_base_info` TEXT NOT NULL AFTER `declaration_w_v`;
UPDATE ots_product SET declaration_brand = 1;
------------------------------------------------
ALTER TABLE `ots_product`
ADD COLUMN `declaration_product_unit` VARCHAR(32) NOT NULL AFTER `declaration_w_v`,
ADD COLUMN `declaration_required` TINYINT NOT NULL DEFAULT '1' AFTER `declaration_brand`;
</pre> |
| #464 | <pre>
-- Step 1
DROP TABLE IF EXISTS `ots_customer`;
CREATE TABLE `ots_customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`customer_company_id` INT NOT NULL,
`firstname` VARCHAR(32) NOT NULL,
`lastname` VARCHAR(32) NOT NULL,
`abbreviation_name` VARCHAR(10) NOT NULL,
`email` VARCHAR(96) NOT NULL,
`telephone` VARCHAR(32) NOT NULL,
`username` VARCHAR(32) NOT NULL,
`password` VARCHAR(60) NOT NULL,
`status` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`customer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company`;
CREATE TABLE `ots_customer_company` (
`customer_company_id` INT NOT NULL AUTO_INCREMENT,
`create_customer_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`abbreviation_name` VARCHAR(10) NOT NULL,
`manufacturer_origin` VARCHAR(64) NOT NULL,
`manufacturer_rank` VARCHAR(64) NOT NULL,
`manager_firstname` VARCHAR(32) NOT NULL,
`manager_lastname` VARCHAR(32) NOT NULL,
`manager_email` VARCHAR(96) NOT NULL,
`telephone` VARCHAR(32) NOT NULL,
`vat` VARCHAR(20) NOT NULL,
`bank_name` VARCHAR(128) NOT NULL,
`bank_account` VARCHAR(32) NOT NULL,
`se_info` TINYINT NOT NULL DEFAULT '0',
`moq_check` TINYINT NOT NULL DEFAULT '0',
`status` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`customer_company_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_product_to_customer`;
CREATE TABLE `ots_product_to_customer` (
`customer_id` INT NOT NULL,
`product_id` INT NOT NULL,
PRIMARY KEY (`customer_id`, `product_id`),
INDEX `customer_id` (`customer_id`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_address`;
DROP TABLE IF EXISTS `ots_customer_company_address`;
CREATE TABLE `ots_customer_company_address` (
`customer_company_address_id` INT NOT NULL AUTO_INCREMENT,
`customer_company_id` INT NOT NULL,
`recipient_name` VARCHAR(32) NOT NULL,
`recipient_email` VARCHAR(96) NOT NULL,
`recipient_telephone` VARCHAR(32) NOT NULL,
`country_id` INT NOT NULL DEFAULT '0',
`zone_id` INT NOT NULL DEFAULT '0',
`city` VARCHAR(128) NOT NULL,
`postcode` VARCHAR(10) NOT NULL,
`address_1` VARCHAR(128) NOT NULL,
`address_2` VARCHAR(128) NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`shipping_address` TINYINT NOT NULL DEFAULT '0',
`invoice_address` TINYINT NOT NULL DEFAULT '0',
PRIMARY KEY (`customer_company_address_id`),
INDEX `customer_company_id` (`customer_company_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company_to_role_group`;
CREATE TABLE `ots_customer_company_to_role_group` (
`customer_company_id` INT NOT NULL,
`customer_company_role_group_id` INT NOT NULL,
PRIMARY KEY (`customer_company_id`, `customer_company_role_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company_role`;
CREATE TABLE `ots_customer_company_role` (
`customer_id` INT NOT NULL,
`customer_company_id` INT NOT NULL,
`customer_role_id` INT NOT NULL,
PRIMARY KEY (`customer_id`, `customer_company_id`, `customer_role_id`),
INDEX `customer_company_id` (`customer_company_id`),
INDEX `customer_role_id` (`customer_role_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_company_role_group`;
CREATE TABLE `ots_customer_company_role_group` (
`customer_company_role_group_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`customer_company_role_group_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
TRUNCATE TABLE `ots_customer_company_role_group`;
INSERT INTO `ots_customer_company_role_group` (`customer_company_role_group_id`, `name`) VALUES
(1, 'Administrators'),
(2, 'Retailers'),
(3, 'Distributors'),
(4, 'Manufacturers');
DROP TABLE IF EXISTS `ots_customer_role`;
CREATE TABLE `ots_customer_role` (
`customer_role_id` INT NOT NULL AUTO_INCREMENT,
`customer_company_role_group_id` INT NOT NULL,
`name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`customer_role_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
TRUNCATE TABLE `ots_customer_role`;
INSERT INTO `ots_customer_role` (`customer_role_id`, `customer_company_role_group_id`, `name`) VALUES
(1, 1, 'Admin'),
(2, 2, 'Manager'),
(3, 2, 'Sales'),
(4, 2, 'Logistics'),
(5, 2, 'Guest'),
(6, 3, 'Manager'),
(7, 3, 'Operator'),
(8, 3, 'Logistics'),
(9, 4, 'Manager'),
(10, 4, 'Producer');
DROP TABLE IF EXISTS `ots_customer_favorite`;
CREATE TABLE `ots_customer_favorite` (
`customer_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`customer_id`, `order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_search`;
DROP TABLE IF EXISTS `ots_customer_filter`;
CREATE TABLE `ots_customer_filter` (
`customer_id` INT NOT NULL,
`filter_name` VARCHAR(100) NOT NULL,
`filter_data` TEXT NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`customer_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 2
TRUNCATE TABLE `ots_customer`;
INSERT INTO `ots_customer` (`customer_id`, `customer_company_id`, `firstname`, `lastname`, `abbreviation_name`, `email`, `telephone`, `username`, `password`, `status`, `is_delete`, `date_added`, `date_modified`) VALUES
(1, 1, 'admin', 'admin', 'admin', 'info@redbluetrading.com', '', 'admin', '$2y$10$zm8clFn3EqbzKUt4lnHbLuWiL48WtBO.Q/SJCRWATiSVr2G39OQn.', 1, 0, '2025-03-19 16:21:02', '2025-03-26 12:24:52');
TRUNCATE TABLE `ots_customer_company`;
INSERT INTO `ots_customer_company` (`customer_company_id`, `create_customer_id`, `name`, `abbreviation_name`, `manufacturer_origin`, `manufacturer_rank`, `manager_firstname`, `manager_lastname`, `manager_email`, `telephone`, `vat`, `bank_name`, `bank_account`, `se_info`, `moq_check`, `status`, `is_delete`, `date_added`, `date_modified`) VALUES
(1, 1, 'Redblue', 'RBSH', '', '', 'Daping', 'Huang', 'daping@redbluetrading.com', '', '', '', '', 1, 0, 1, 0, '2025-03-25 14:58:09', '2025-03-26 12:26:45');
TRUNCATE TABLE `ots_customer_company_address`;
INSERT INTO `ots_customer_company_address` (`customer_company_address_id`, `customer_company_id`, `recipient_name`, `recipient_email`, `recipient_telephone`, `country_id`, `zone_id`, `city`, `postcode`, `address_1`, `address_2`, `default`, `shipping_address`, `invoice_address`) VALUES
(1, 1, 'Daping', 'daping@redbluetrading.com', 'Huang', 44, 708, '上海', '201615', '4th Floor, Building F', 'No.858 Jiujing Road', 1, 0, 1),
(2, 1, 'Daping', 'daping@redbluetrading.com', 'Huang', 44, 708, '上海', '201615', '4th Floor, Building F', 'No.858 Jiujing Road', 1, 1, 0);
TRUNCATE TABLE `ots_customer_company_role`;
INSERT INTO `ots_customer_company_role` (`customer_id`, `customer_company_id`, `customer_role_id`) VALUES
(1, 1, 1);
TRUNCATE TABLE `ots_customer_company_to_role_group`;
INSERT INTO `ots_customer_company_to_role_group` (`customer_company_id`, `customer_company_role_group_id`) VALUES
(1, 1),
(1, 2),
(1, 3);
TRUNCATE TABLE `ots_product_to_customer`;
-- Step 3:
DROP TABLE `ots_customer_affiliate`;
DROP TABLE `ots_customer_approval`;
DROP TABLE `ots_customer_ip`;
DROP TABLE `ots_customer_online`;
DROP TABLE `ots_customer_reward`;
DROP TABLE `ots_customer_transaction`;
DROP TABLE `ots_customer_wishlist`;
DROP TABLE `ots_customer_activity`;
DROP TABLE `ots_custom_field`;
DROP TABLE `ots_custom_field_customer_group`;
DROP TABLE `ots_custom_field_description`;
DROP TABLE `ots_custom_field_value`;
DROP TABLE `ots_custom_field_value_description`;
-- Step 4:
ALTER TABLE `ots_customer_history`
ADD COLUMN `action` VARCHAR(100) NOT NULL AFTER `customer_id`;
-- Step 5:
ALTER TABLE `ots_customer_company`
ADD COLUMN `tax_class_id` INT NOT NULL AFTER `vat`;
-- Step 6:
ALTER TABLE `ots_customer_company_address`
ADD COLUMN `title` VARCHAR(32) NOT NULL AFTER `customer_company_id`;
-- Step 7:
ALTER TABLE `ots_customer_company`
ADD COLUMN `display_price` INT NOT NULL DEFAULT '0' AFTER `moq_check`;
-- 更新redblue的display price = 1
UPDATE ots_customer_company SET display_price = 1 WHERE customer_company_id = 1
-- Step 8:
ALTER TABLE `ots_customer_company`
ADD COLUMN `display_shipping` INT NOT NULL DEFAULT '0' AFTER `display_price`;
-- 更新redblue和EASY的display shipping = 1
UPDATE ots_customer_company SET display_shipping = 1 WHERE customer_company_id = 1 OR customer_company_id = 2
</pre> |
| #463 | <pre>
-- Step 1
-- Whether the product option value needs to extra manufacturer and whether it needs to be manually reviewed
ALTER TABLE `ots_product_option_value`
ADD COLUMN `extra_manufacturer_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `logotype_id`,
ADD COLUMN `extra_manufacturer_id` INT NOT NULL DEFAULT '0' AFTER `extra_manufacturer_enabled`,
ADD COLUMN `manual_review` TINYINT NOT NULL DEFAULT '0' AFTER `extra_manufacturer_id`;
-- Step 2
-- Remove manufacturer
DROP TABLE `ots_manufacturer`;
-- Step 3
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `manual_review` TINYINT NOT NULL DEFAULT '0' AFTER `startcost_dependant_option_value_id`;
-- Step 4
ALTER TABLE `ots_product`
ADD COLUMN `is_stock` TINYINT NOT NULL DEFAULT '0' AFTER `status`;
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `stock_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `manual_review`,
ADD COLUMN `stock_product_id` INT NOT NULL DEFAULT '0' AFTER `stock_enabled`;
ALTER TABLE `ots_product_option_value`
ADD COLUMN `stock_enabled` TINYINT NOT NULL DEFAULT '0' AFTER `manual_review`,
ADD COLUMN `stock_product_id` INT NOT NULL DEFAULT '0' AFTER `stock_enabled`;
-- Step 5
ALTER TABLE `ots_product_to_manufacturer`
ADD COLUMN `stock_multiplier` INT NOT NULL DEFAULT '1' AFTER `stock_product_id`;
ALTER TABLE `ots_product_option_value`
ADD COLUMN `stock_multiplier` INT NOT NULL DEFAULT '1' AFTER `stock_product_id`;
</pre> |
| #455 | <pre>
-- Step 1
DROP TABLE IF EXISTS `ots_order`;
CREATE TABLE `ots_order` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`order_name` VARCHAR(255) NOT NULL,
`urgency` TINYINT NOT NULL DEFAULT '0',
`remake` TINYINT NOT NULL DEFAULT '0',
`retailer_company_id` INT NOT NULL DEFAULT '0',
`retailer_abbreviation_name` VARCHAR(10) NOT NULL,
`retailer_shipping_time` DATE NULL DEFAULT NULL,
`retailer_shipping_address_id` INT NOT NULL,
`retailer_shipping_country_id` INT NOT NULL,
`retailer_shipping_zone_id` INT NOT NULL,
`retailer_shipping_city` VARCHAR(128) NOT NULL,
`retailer_shipping_postcode` VARCHAR(10) NOT NULL,
`retailer_shipping_address_1` VARCHAR(128) NOT NULL,
`retailer_shipping_address_2` VARCHAR(128) NOT NULL,
`retailer_shipping_recipient_name` VARCHAR(32) NOT NULL,
`retailer_shipping_recipient_email` VARCHAR(96) NOT NULL,
`retailer_shipping_recipient_telephone` VARCHAR(32) NOT NULL,
`retailer_shipping_recipient_comment` TEXT NOT NULL,
`customer_shipping_time` DATE NULL DEFAULT NULL,
`customer_shipping_service` VARCHAR(32) NOT NULL,
`customer_shipping_country_id` INT NOT NULL,
`customer_shipping_zone_id` INT NOT NULL,
`customer_shipping_city` VARCHAR(128) NOT NULL,
`customer_shipping_postcode` VARCHAR(10) NOT NULL,
`customer_shipping_address_1` VARCHAR(128) NOT NULL,
`customer_shipping_address_2` VARCHAR(128) NOT NULL,
`customer_shipping_recipient_name` VARCHAR(32) NOT NULL,
`customer_shipping_recipient_email` VARCHAR(96) NOT NULL,
`customer_shipping_recipient_telephone` VARCHAR(32) NOT NULL,
`customer_shipping_recipient_comment` TEXT NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_order_product`;
CREATE TABLE `ots_order_product` (
`order_product_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`product_id` INT NOT NULL,
`model` VARCHAR(64) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`manufacturer_id` INT NOT NULL,
`manufacturer_name` VARCHAR(255) NOT NULL,
`manufacturer_origin` VARCHAR(255) NOT NULL,
`extra_info` TEXT NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 产品单价（不含option，税前价格）
`total` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 单价X数量（税前价格）
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` INT NOT NULL DEFAULT '0',
`tax_class_id` INT NOT NULL,
`status` INT NOT NULL DEFAULT '0',
`expected_finishing_time` DATE NULL DEFAULT NULL,
`retailer_shipper` VARCHAR(20) NOT NULL,
`retailer_shipper_code` VARCHAR(200) NULL DEFAULT NULL,
`customer_shipper` VARCHAR(20) NOT NULL,
`customer_shipper_code` VARCHAR(200) NULL DEFAULT NULL,
`brand` VARCHAR(64) NOT NULL, -- if declaration_brand = 1
`declaration_unit_price` VARCHAR(200) NULL DEFAULT NULL,
`declaration_g_w_g` VARCHAR(64) NULL DEFAULT NULL,
`declaration_w_v` VARCHAR(64) NULL DEFAULT NULL,
`declaration_product_unit` VARCHAR(32) NOT NULL,
`declaration_cn_product_name` VARCHAR(255) NOT NULL,
`declaration_en_product_name` VARCHAR(255) NOT NULL,
`declaration_cn_hs_code` VARCHAR(64) NOT NULL,
`declaration_se_hs_code` VARCHAR(64) NOT NULL,
`declaration_base_info` TEXT NOT NULL,
`manual_reviewed` TINYINT NOT NULL DEFAULT '0', -- 是否人工干预过价格
`key` TEXT NULL DEFAULT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_id`),
INDEX `order_id` (`order_id`),
INDEX `product_id` (`product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_order_option`;
CREATE TABLE `ots_order_product_option` (
`order_product_option_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`option_id` INT NOT NULL,
`option_value_id` INT NOT NULL,
`product_option_id` INT NOT NULL,
`product_option_name` VARCHAR(100) NOT NULL,
`product_option_value_id` INT NOT NULL DEFAULT '0',
`extra_manufacturer_id` INT NOT NULL,
`extra_manufacturer_name` VARCHAR(255) NOT NULL,
`model` VARCHAR(64) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`quantity` INT NOT NULL DEFAULT '0',
`unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- option单价（不含option，税前价格）
`total` DECIMAL(15,4) NOT NULL DEFAULT '0.0000', -- 单价X数量（税前价格）
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`manual_reviewed` TINYINT NOT NULL DEFAULT '0', -- 是否人工干预过价格
PRIMARY KEY (`order_product_option_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_order_product_files` (
`order_product_file_id` INT NOT NULL AUTO_INCREMENT,
`order_product_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_file_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_order_product_proof_images` (
`order_product_proof_image_id` INT NOT NULL AUTO_INCREMENT,
`order_product_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_proof_image_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- 由于v4不需要turnover功能，所以order_history只用于记录log
DROP TABLE IF EXISTS `ots_order_history`;
CREATE TABLE `ots_order_history` (
`order_history_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`order_id` INT NOT NULL,
`action` VARCHAR(100) NOT NULL,
`original` TEXT NOT NULL,
`altered` TEXT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`order_history_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 2 ots_customer_favorite -> ots_order_favorite，ots_customer_filter -> ots_order_filter
DROP TABLE IF EXISTS `ots_customer_favorite`;
CREATE TABLE `ots_order_favorite` (
`customer_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`customer_id`, `order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE IF EXISTS `ots_customer_filter`;
CREATE TABLE `ots_order_filter` (
`order_filter_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`filter_name` VARCHAR(100) NOT NULL,
`filter_data` TEXT NOT NULL,
`default` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_filter_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 3 标记order product是否是转移的产品
ALTER TABLE `ots_order_product`
ADD COLUMN `transferred` TINYINT NOT NULL DEFAULT '0' AFTER `manual_reviewed`;
-- Step 4
ALTER TABLE `ots_order_product_option`
ADD COLUMN `type` VARCHAR(32) NOT NULL AFTER `quantity`;
-- Step 5
DROP TABLE `ots_order_recurring`;
DROP TABLE `ots_order_recurring_transaction`;
DROP TABLE `ots_order_shipment`;
DROP TABLE `ots_order_total`;
DROP TABLE `ots_order_voucher`;
-- Step 6
-- 清理数据库中的0000-00-00， 代码的问题导致保存的是0000-00-00
UPDATE ots_order SET customer_shipping_time = NULL WHERE customer_shipping_time = 0000-00-00;
UPDATE ots_order SET retailer_shipping_time = NULL WHERE retailer_shipping_time = 0000-00-00;
UPDATE ots_order_product SET expected_finishing_time = NULL WHERE expected_finishing_time = 0000-00-00;
-- 修改''为0.0，且在order detail中将0.0视为没有填写declaration_unit_price
UPDATE ots_order_product SET declaration_unit_price = 0.0 WHERE declaration_unit_price = '';
-- varchar -> DECIMAL
ALTER TABLE `ots_order_product`
CHANGE COLUMN `declaration_unit_price` `declaration_unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `brand`;
-- Step 7
ALTER TABLE `ots_product`
CHANGE COLUMN `declaration_g_w_g` `declaration_g_w_g` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_general_ci' AFTER `declaration_se_hs_code`,
ADD COLUMN `declaration_g_w_g_type` ENUM('fixed','formula') NOT NULL DEFAULT 'fixed' AFTER `declaration_g_w_g`;
ALTER TABLE `ots_product_option_value`
CHANGE COLUMN `weight` `weight` VARCHAR(255) NOT NULL AFTER `minimum`,
ADD COLUMN `weight_type` ENUM('fixed','formula') NOT NULL DEFAULT 'fixed' AFTER `weight`;
-- Step 8
ALTER TABLE `ots_order_product_option`
DROP COLUMN `manual_reviewed`;
-- 记录当前order product是否需要人工干预，需要区分manual_reviewed(已人工干预过)
ALTER TABLE `ots_order_product`
ADD COLUMN `manual_review` TINYINT NOT NULL DEFAULT '0' AFTER `declaration_base_info`;
</pre> |
| #471 | <pre>
-- Order product files + Order product proof images
DROP TABLE `ots_order_product_files`;
CREATE TABLE `ots_order_product_file` (
`order_product_file_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`original_name` VARCHAR(255) NOT NULL,
`save_path` VARCHAR(255) NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`order_product_file_id`),
INDEX `order_id` (`order_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
DROP TABLE `ots_order_product_proof_images`;
CREATE TABLE `ots_order_product_proof_image` (
`order_product_proof_image_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`original_name` VARCHAR(255) NOT NULL,
`save_path` VARCHAR(255) NOT NULL,
`create_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`order_product_proof_image_id`),
INDEX `order_id` (`order_id`),
INDEX `order_product_id` (`order_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
</pre> |
| #476 | <pre>
CREATE TABLE `ots_shipping_carton` (
`shipping_carton_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`length` INT NOT NULL DEFAULT '0',
`width` INT NOT NULL DEFAULT '0',
`height` INT NOT NULL DEFAULT '0',
`weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_carton_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_shipping_history` (
`shipping_history_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`action` VARCHAR(100) NOT NULL,
`original` TEXT NOT NULL,
`altered` TEXT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`shipping_history_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
</pre> |
| #477 | <pre>
-- Step 1
CREATE TABLE `ots_shipping_supplier` (
`shipping_supplier_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`location_country_id` INT NOT NULL,
`currency_code` INT NOT NULL,
`type` ENUM('express','air','train','boat','others') NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_supplier_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_shipping_cost` (
`shipping_cost_id` INT NOT NULL AUTO_INCREMENT,
`shipping_supplier_id` INT NOT NULL,
`price_hot_season` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`fuel_surcharge_percentage` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`price_length_over_size` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`price_whole_over_size` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`price_over_weight` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`price_remote_shipping_area` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_cost_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_shipping_cost_to_destination` (
`shipping_cost_id` INT NOT NULL AUTO_INCREMENT,
`destination_code` CHAR(2) NOT NULL DEFAULT '',
`price_unit_below_21` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`days_delivery_below_21` INT NOT NULL DEFAULT '0',
`price_unit_above_21` DECIMAL(15,2) NOT NULL DEFAULT '0.00',
`days_delivery_above_21` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`shipping_cost_id`, `destination_code`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 2
ALTER TABLE `ots_shipping_supplier`
CHANGE COLUMN `currency_code` `currency_code` VARCHAR(3) NOT NULL AFTER `location_country_id`;
-- Clear the old data since we change the type of currency_code.
TRUNCATE ots_shipping_cost;
TRUNCATE ots_shipping_supplier;
TRUNCATE ots_shipping_cost_to_destination;
-- Step 3
ALTER TABLE `ots_shipping_cost`
ADD COLUMN `threshold_length_over_size` INT NOT NULL DEFAULT '0' AFTER `price_length_over_size`,
ADD COLUMN `threshold_whole_over_size` INT NOT NULL DEFAULT '0' AFTER `price_whole_over_size`,
ADD COLUMN `threshold_over_weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000' AFTER `price_over_weight`;
</pre> |
| #478 | <pre>
-- Step 1
CREATE TABLE `ots_shipping_carton_location` (
`shipping_carton_location_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`type` ENUM('small','mix') NOT NULL,
`is_delete` TINYINT NOT NULL DEFAULT '0',
`sort_order` INT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_carton_location_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
ALTER TABLE `ots_customer_company`
CHANGE COLUMN `display_shipping` `display_shipping_cost` INT NOT NULL DEFAULT '0' AFTER `display_price`,
ADD COLUMN `display_shipping_logistics` INT NOT NULL DEFAULT '0' AFTER `display_shipping_cost`;
UPDATE ots_customer_company SET display_shipping_logistics= 1 WHERE customer_company_id = 1
-- Step 2
CREATE TABLE `ots_shipping_packing_rule` (
`shipping_packing_rule_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`shipping_time_interval` INT NOT NULL, -- Delivery Date 时间相近的（比如：+- 2天内）
`status` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_rule_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_shipping_packing_rule_to_carton` (
`shipping_packing_rule_carton_id` INT NOT NULL AUTO_INCREMENT,
`shipping_packing_rule_id` INT NOT NULL,
`shipping_carton_id` INT NOT NULL,
`grouping_carton_quantity` INT NOT NULL, -- 数字： 代表多少数量小箱（比如 6）可以Rollup Group化（装大箱 Big = 6xSmall）
`grouping_shipping_carton_id` INT NOT NULL, -- 数字： 可以转为哪个大箱（比如：Big）
PRIMARY KEY (`shipping_packing_rule_carton_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `ots_shipping_packing_rule_to_carton_product` (
`shipping_packing_rule_carton_id` INT NOT NULL,
`product_id` INT NOT NULL, -- 支持设置product，表示单一产品使用此规则
`option_id` INT NOT NULL, -- 不支持单独设置option，只支持设置option value并把它对应的option id存入数据库
`option_value_id` INT NOT NULL, -- 支持设置option value，表示单一option value使用此规则 （使用此规则，必须和 option_id一起使用）
PRIMARY KEY (`shipping_packing_rule_carton_id`, `product_id`, `option_id`, `option_value_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 3
-- 结合数据库锁，保证生成的箱号唯一，且每天刷新
CREATE TABLE `ots_shipping_packing_carton_number` (
`shipping_packing_carton_number_id` INT NOT NULL AUTO_INCREMENT,
`date` DATE NOT NULL, -- 组成箱号的日期
`company_code` VARCHAR(32) NOT NULL, -- 组成箱号的公司code
`daily_carton_number` INT NOT NULL, -- 组成箱号的number
`status` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_carton_number_id`),
UNIQUE KEY unique_number (`date`, daily_carton_number)
);
-- 记录外箱的数据
CREATE TABLE `ots_shipping_packing_carton` (
`shipping_packing_carton_id` INT NOT NULL AUTO_INCREMENT,
`shipping_packing_carton_number_id` INT NOT NULL,
`shipping_carton_id` INT NOT NULL,
`extra_info` VARCHAR(255) NOT NULL, -- 参考v3的extra info，应该没有需要设置格式的场景，所以使用普通的input
`status` INT NOT NULL, -- 满箱，无箱，拼箱，label
`carton_length` INT NOT NULL DEFAULT '0',
`carton_width` INT NOT NULL DEFAULT '0',
`carton_height` INT NOT NULL DEFAULT '0',
`carton_v_w` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`carton_g_w` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`shipping_carton_location_id` INT NOT NULL, -- location, 结合status即可判断是否属于公共区域（无箱/拼箱）还是temp
`pallet` TINYINT NOT NULL DEFAULT '0', -- 是否放入托盘中
`pallet_label` TINYINT NOT NULL DEFAULT '0', -- 是否打印了托盘的唛头（A4纸）
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_carton_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- 记录外箱中的产品的数据和内箱的数据（如果有内箱）
CREATE TABLE `ots_shipping_packing_carton_product` (
`shipping_packing_carton_product_id` INT NOT NULL AUTO_INCREMENT,
`shipping_packing_carton_id` INT NOT NULL,
`order_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`sample` TINYINT NOT NULL DEFAULT '0',
`quantity` VARCHAR(32) NOT NULL,
`extra_info` VARCHAR(255) NOT NULL, -- 参考v3的extra info，应该没有需要设置格式的场景，所以使用普通的input
`carton_label` TINYINT NOT NULL DEFAULT '0', -- 是否打了内箱唛头
`status` INT NOT NULL, -- 满箱，无箱，拼箱，label
`shipping_carton_id` INT NOT NULL,
`grouping_shipping_carton_id` INT NOT NULL,
`carton_length` INT NOT NULL DEFAULT '0',
`carton_width` INT NOT NULL DEFAULT '0',
`carton_height` INT NOT NULL DEFAULT '0',
`carton_v_w` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`carton_g_w` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`unit_cost` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`original_customer_id` INT NOT NULL, -- 记录order product的原始跟单
`primary` TINYINT(1) NOT NULL DEFAULT '0', -- 拼箱时，记录箱子中的第一个产品
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_carton_product_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
ALTER TABLE `ots_order`
ADD COLUMN `retailer_company_name` VARCHAR(100) NOT NULL AFTER `retailer_abbreviation_name`;
ALTER TABLE `ots_customer_company_address`
ADD COLUMN `company_name` VARCHAR(100) NOT NULL AFTER `customer_company_id`,
ADD COLUMN `is_customer` TINYINT NOT NULL DEFAULT '0' AFTER `invoice_address`,
ADD COLUMN `same_company_address_id` INT NOT NULL DEFAULT '0' AFTER `is_customer`;
ALTER TABLE `ots_shipping_packing_rule_to_carton`
ADD COLUMN `unit_product_quantity` INT NOT NULL AFTER `shipping_carton_id`;
-- step 4
-- 添加shipping_carton_location_id，记录order product的原始location
ALTER TABLE `ots_shipping_packing_carton_product`
ADD COLUMN `shipping_carton_location_id` INT NOT NULL AFTER `original_customer_id`;
-- Step 5
-- 记录内唛上的标记信息：date_added取今天日期，code记录标记（比如：A,B,C ...）
CREATE TABLE `ots_shipping_packing_carton_label` (
`shipping_packing_carton_label_id` INT NOT NULL AUTO_INCREMENT,
`date` DATE NOT NULL,
`code` VARCHAR(10) NOT NULL,
`create_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_carton_label_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
-- Step 6
-- 报关文件表
CREATE TABLE `ots_shipping_packing_carton_custom_declaration` (
`shipping_packing_carton_custom_declaration_id` INT NOT NULL AUTO_INCREMENT,
`total_weight` DECIMAL(15,8) NOT NULL DEFAULT '0.00000000',
`total_cost` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`shipping_cost` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`declaration_type` TINYINT NOT NULL DEFAULT '0',
`is_delete` TINYINT NOT NULL DEFAULT '0',
`create_customer_id` INT NOT NULL,
`update_customer_id` INT NOT NULL,
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`shipping_packing_carton_custom_declaration_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
ALTER TABLE `ots_shipping_packing_carton`
ADD COLUMN `shipping_supplier_id` INT NOT NULL DEFAULT '0' AFTER `pallet_label`, -- shipping supplier
ADD COLUMN `retailer_shipper_code` VARCHAR(200) NULL DEFAULT NULL AFTER `shipping_supplier_id`, -- tracking number
ADD COLUMN `shipping_cost` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `retailer_shipper_code`, -- 运费，以箱子为单位记录运费
ADD COLUMN `labelled_pending` TINYINT NOT NULL DEFAULT '0' AFTER `shipping_cost`, -- 在logisctic模块中repack之后,回到packing模块，在labelled区域红底
ADD COLUMN `shipping_packing_carton_custom_declaration_id` INT NOT NULL DEFAULT '0' AFTER `labelled_pending`;
ALTER TABLE `ots_shipping_packing_carton_product`
ADD COLUMN `declaration_display` TINYINT NOT NULL DEFAULT '0' AFTER `primary`; -- 是否在报关文件中显示
-- 删除order product表中tracking info的相关字段
ALTER TABLE `ots_order_product`
DROP COLUMN `retailer_shipper`,
DROP COLUMN `retailer_shipper_code`;
-- Step 7
ALTER TABLE `ots_order_product`
ADD COLUMN `declaration_required` TINYINT NOT NULL DEFAULT '0' AFTER `brand`, -- 是否报关
ADD COLUMN `sales_info_incoterm` INT NOT NULL DEFAULT '0' AFTER `declaration_base_info`, -- incoterm:FOB,C&F,DDU,DDP
ADD COLUMN `sales_info_currency` INT NOT NULL DEFAULT '0' AFTER `sales_info_incoterm`, -- 货币
ADD COLUMN `sales_info_real_unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `sales_info_currency`, -- 实际单价
ADD COLUMN `sales_info_declare_unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `sales_info_real_unit_price`; -- 报关单价
ALTER TABLE `ots_shipping_packing_carton_product`
ADD COLUMN `incoterm` INT NOT NULL DEFAULT '0' AFTER `primary`, -- incoterm:FOB,C&F,DDU,DDP
ADD COLUMN `declare_unit_price_currency` INT NOT NULL DEFAULT '0' AFTER `incoterm`, -- 货币
ADD COLUMN `declare_unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `declare_unit_price_currency`; -- 报关单价
ALTER TABLE `ots_shipping_packing_carton_custom_declaration`
ADD COLUMN `shipping_supplier_id` INT NOT NULL DEFAULT '0' AFTER `total_cost`, -- 物流商
ADD COLUMN `shipping_pickup_country_id` INT NOT NULL DEFAULT '0' AFTER `shipping_cost`, -- 取货地国家
ADD COLUMN `shipping_pickup_zone_id` INT NOT NULL DEFAULT '0' AFTER `shipping_pickup_country_id`, -- 取货地区域
ADD COLUMN `declaration_report_percent` INT NOT NULL DEFAULT '0' AFTER `shipping_pickup_zone_id`; -- 申报比例
-- 默认报关，默认incoterm=C&F, 默认currency = RMB
UPDATE ots_order_product SET declaration_required = 1, sales_info_incoterm = 20, sales_info_currency = 4;
--Step 8
CREATE TABLE `ots_country_description` (
`country_id` INT NOT NULL,
`language_id` INT NOT NULL,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`country_id`, `language_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
INSERT INTO ots_country_description (country_id, language_id, `name`)
SELECT c.country_id, l.language_id, c.`name`
FROM ots_country c
CROSS JOIN (
SELECT 1 AS language_id
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) l;
ALTER TABLE `ots_country`
ADD COLUMN `is_eu` TINYINT NOT NULL DEFAULT '0' AFTER `postcode_required`,
ADD COLUMN `is_au` TINYINT NOT NULL DEFAULT '0' AFTER `is_eu`;
ALTER TABLE `ots_country`
DROP COLUMN `name`;
UPDATE ots_country SET `status` = 0 WHERE country_id != 44
-- Step 9
ALTER TABLE `ots_country`
ADD COLUMN `is_se` TINYINT NOT NULL DEFAULT '0' AFTER `postcode_required`,
ADD COLUMN `is_us` TINYINT NOT NULL DEFAULT '0' AFTER `is_eu`;
-- Step 10
ALTER TABLE `ots_customer_company`
ADD COLUMN `declaration_report_percent` INT NOT NULL DEFAULT '0' AFTER `display_shipping_logistics`;
UPDATE ots_customer_company SET declaration_report_percent = 110 WHERE customer_company_id = 2;
-- Step 11
ALTER TABLE `ots_shipping_packing_carton_custom_declaration`
CHANGE COLUMN `shipping_pickup_zone_id` `shipping_pickup_city` VARCHAR(128) NOT NULL DEFAULT '' AFTER `shipping_pickup_country_id`,
DROP COLUMN `total_cost`;
-- Step 12
ALTER TABLE `ots_shipping_packing_carton_custom_declaration`
ADD COLUMN `path` VARCHAR(255) NOT NULL DEFAULT '' AFTER `declaration_type`;
-- Step 13 关联内箱唛头标记和内箱
ALTER TABLE `ots_shipping_packing_carton_label`
ADD COLUMN `shipping_packing_carton_product_id` INT NOT NULL DEFAULT '0' AFTER `shipping_packing_carton_label_id`;
-- Step 14
ALTER TABLE `ots_product_option_value`
ADD COLUMN `declaration_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `weight_prefix`;
-- Step 15
CREATE TABLE `ots_currency_exchange_rate` (
`currency_exchange_rate_id` INT NOT NULL AUTO_INCREMENT,
`from_currency_code` VARCHAR(10) NOT NULL,
`to_currency_code` VARCHAR(10) NOT NULL,
`exchange_rate` DECIMAL(15,4) NOT NULL DEFAULT '0.0000',
`date_added` DATETIME NOT NULL,
`date_modified` DATETIME NOT NULL,
PRIMARY KEY (`currency_exchange_rate_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
ALTER TABLE `ots_shipping_packing_carton_product`
ADD COLUMN `declare_cn_unit_price` DECIMAL(15,4) NOT NULL DEFAULT '0.0000' AFTER `declare_unit_price`;
</pre> |
* *Record all SQL changes (v3)*
|. Issue |. SQL |
| #441 | <pre>CREATE TABLE `t_user_order_product` (
`id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`order_product_id` INT NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `order_product_id` (`order_product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;</pre> |
| #441 | <pre>ALTER TABLE `t_user_search`
ADD COLUMN `is_default` TINYINT NOT NULL DEFAULT '0' AFTER `search_data`;</pre> |
*v4 coding standard:*
# Git提交
#xxx(redmine number): xxx(redmine title) - xxx(任意：补充内容)
# 代码规范
2.1: 结构：Public -> Protected -> Private
2.2: 其他代码规范同SBX
# MVCL的v3/v4物理分割
3.1: v3/v4的代码分为两个文件，比如： order.php, order_v3.php
3.2: system也分为两个文件，暂时只有一个user_v3
- Note: front的$this->user在v4中会替换为$this->customer, 即v4中需要清除$this->user
3.3: 代码中，区分v3/v4的调用，比如：new User_V3
# 前台代码
twig, js, css (js, css尽量写在单独的css文件中)
# 后台代码
MVCL (L：en，cn)
# 注释
## 类名：
<pre>
/**
* xxx
*
* @copyright RedBlue-OTS 2024
* @version v4
*
*/
</pre>
## 函数名：参数类型 + 返回值类型
<pre>
/**
* xxx
*
* @param xxx $xxx
* @param xxx $xxx
* @return xxx
*/
</pre>
*Output*
* Report and Solution
*Others*