追加功能 #499: v4.0: Migration plan (AWS China → AWS Singapore) - IT: OTS (China) - Redmine

追加功能 #499

*Background* 
 # 国内的AWS迁移到海外（新加坡），主要分3个Web资源。 
 ## OTS3.0 
 ## Easydingzhi 
 ## Redmine 
 # 国内其他云服务（阿里云： 域名， DNS， SSL等）不在迁移计划内。 

 *Task Details（概要）* 
 * 1. OTS3.0 
 ** 1.1 数据库数据迁移（主要是订单数据，产品+文件等除外）到OTS4.0 （ *1/26 凌晨完成* ） 
        问题1：OTS3.0老订单数字5位，比如 EASY00001 - EASY99999，OTS4.0新订单数字6位，比如 EASY100001 
        问题2：OTS4.0新订单数字6位，既存数据需要保留嘛？如果保留可能需要统一【id + 100000】，否者删除即可。另外UI6位数字是否换行等，需要排查 
 ** 1.2 SSL域名迁移 （ *1/26 凌晨完成* ） 
        OTS3.0 = order3.easydingzhi.com (现域名：order.easydingzhi.com) 
        OTS4.0 = order.easydingzhi.com 
 ** 1.3 OTS3.0备份 所有DB， Uploaded files数据全部迁移（ *2月内完成 van可以处理，这里先关闭* ） files数据全部迁移（1月内完成） 
        Uploaded files数据半年前的数据备份onedrive。 

 * 2. Easydingzhi 
      所有DB， 图片，Uploaded files数据全部迁移（ *2月内完成 van可以处理，这里先关闭* ） files数据全部迁移（1月内完成） 

 * 3. Redmine (IT团队使用) 
      所有DB， Uploaded files数据全部迁移（ *2月内完成 van+bruce可以处理，这里先关闭* ） files数据全部迁移（1月内完成） 

 * 4. 其他：等1-3全部完成，将全部关闭AWS China，并注销【网站备注】。（ *2月内完成 van可以处理，这里先关闭* ） China，并注销【网站备注】。 

 *Task Details（OTS3.0 -> OTS4.0 数据迁移的详细步骤）* 

 * 0. *%{color:red}事先准备（重要）%* 
      - 备份v4数据库（OneDrive）  
      - 备份文件（OneDrive） 
      - 导出v3数据库（程序处理 csv导出） 

 * 1. 数据保留【v4 11月-1月】：Van 
      - 1.1 查找和v3的不同，需要order全匹配并组装【order id mapping表】 

 * 2. 数据清除【v4】：程序处理（DB+File物理文件） 
      - 2.1 删除11月前（ o.date_added < '2025-11-01'）的所有相关数据（排除packing相关数据） 
      - 2.2 删除 o.is_delete = 1的所有相关数据（排除packing相关数据） 
      - 2.3 删除脏数据（排除packing相关数据）： order_id = 2081, 2082，还有其他指定order id（参考1.1） 

 * 3. 数据导入【v3全部】：程序处理 
      - 3.1 v4 11月-1月的order id + 90000（临时：给v3导入腾出id空间），注意DB+File都要处理 
      - 3.2 导入v3数据，重复的order id = 70000+, 且order status = completed(80) 
      - 3.3 根据1.1的mapping表，恢复2.1为真实的order id（v3老的id） 
      --3.3.1 先根据mapping表中的v3.order_id(ca_index) 将导入的v3数据删除一遍（关联的表都需要删除）。 
      --3.3.2 再根据mapping表，更新v4数据为v3数据（如果v4存在多个相同的v3.order_id,执行合并操作） 

 * 4. Order id采用新的开始v4 id：程序处理 
      - 4.1 order id = 100001 

 * 5. *%{color:red}手动修改order_id%* 
      - 5.1 只有v3 -> v4才需处理以下:(删除老的v3的order_id) 
 <pre> 
 START TRANSACTION; 

 DELETE FROM ots_order_product_file 
 WHERE order_id = 1; 

 DELETE FROM ots_order_product 
 WHERE order_id = 1; 

 DELETE FROM ots_order 
 WHERE order_id = 1; 

 COMMIT; 
 </pre> 
      - 5.2 修改order_id  
 <pre> 
 START TRANSACTION; 

 UPDATE ots_order 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_order_product 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_order_product_option 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_order_history 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_order_product_file 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_order_product_proof_image 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 UPDATE ots_shipping_packing_carton_product 
 SET order_id = new_order_id 
 WHERE order_id = old_order_id; 

 COMMIT; 
 </pre> 

 * 6. *%{color:red}手动修改指定order/order_product部分字段%* 
 ** 6.1 order 的retailer_shipping_time，date_added，date_modified 
 <pre><code class="sql"> 
 -- 已执行：原Excel *100001 ~ 100019* 的记录 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-09', date_added = '2026-01-24 00:04:48', date_modified = '2026-01-24 00:06:06' WHERE order_id = 64251; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-13', date_added = '2026-01-23 16:56:27', date_modified = '2026-01-23 16:56:49' WHERE order_id = 64241; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-03', date_added = '2026-01-23 23:10:31', date_modified = '2026-01-23 23:10:31' WHERE order_id = 64250; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-05', date_added = '2026-01-23 23:00:41', date_modified = '2026-01-23 23:00:54' WHERE order_id = 64249; 
 UPDATE ots_order SET retailer_shipping_time = '2026-01-29', date_added = '2026-01-23 22:06:57', date_modified = '2026-01-24 09:13:08' WHERE order_id = 64248; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-09', date_added = '2026-01-23 22:06:31', date_modified = '2026-01-23 22:07:40' WHERE order_id = 64247; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-18', date_added = '2026-01-23 20:09:09', date_modified = '2026-01-26 10:44:06' WHERE order_id = 64246; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-11', date_added = '2026-01-23 19:54:01', date_modified = '2026-01-23 20:54:39' WHERE order_id = 64245; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-13', date_added = '2026-01-23 19:35:38', date_modified = '2026-01-23 19:36:55' WHERE order_id = 64244; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-03', date_added = '2026-01-23 19:32:02', date_modified = '2026-01-23 19:32:02' WHERE order_id = 64243; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-13', date_added = '2026-01-23 18:16:53', date_modified = '2026-01-23 18:16:53' WHERE order_id = 64242; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-11', date_added = '2026-01-23 16:48:59', date_modified = '2026-01-23 16:48:59' WHERE order_id = 64240; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-26', date_added = '2026-01-23 16:41:25', date_modified = '2026-01-25 02:19:04' WHERE order_id = 1298; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-05', date_added = '2026-01-23 16:36:44', date_modified = '2026-01-25 02:20:57' WHERE order_id = 6865; 
 UPDATE ots_order SET retailer_shipping_time = '2026-01-27', date_added = '2026-01-23 16:35:10', date_modified = '2026-01-24 09:13:36' WHERE order_id = 64239; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-05', date_added = '2026-01-23 16:32:00', date_modified = '2026-01-25 02:18:43' WHERE order_id = 6864; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-03', date_added = '2026-01-23 15:52:13', date_modified = '2026-01-25 02:25:51' WHERE order_id = 6863; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-03', date_added = '2026-01-23 15:46:04', date_modified = '2026-01-25 02:25:45' WHERE order_id = 6862; 
 UPDATE ots_order SET retailer_shipping_time = '2026-02-26', date_added = '2026-01-23 15:10:33', date_modified = '2026-01-25 02:26:02' WHERE order_id = 6861; 

 -- 已执行：原Excel 标记 *IT修改日期* 的记录 
 UPDATE ots_order SET retailer_shipping_time = '2026-01-30', date_added = '2026-01-20 21:21:14', date_modified = '2026-01-25 02:34:48' WHERE order_id = 64192; 
 UPDATE ots_order SET retailer_shipping_time = '2026-01-05', date_added = '2025-12-15 16:31:39', date_modified = '2025-12-23 18:08:09' WHERE order_id = 63843; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-30', date_added = '2025-12-10 18:47:40', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63799; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-23', date_added = '2025-12-10 16:38:30', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63796; 
 UPDATE ots_order SET retailer_shipping_time = '2026-01-07', date_added = '2025-12-09 11:55:18', date_modified = '2025-12-15 18:41:02' WHERE order_id = 63782; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-22', date_added = '2025-12-08 21:24:07', date_modified = '2025-12-23 18:55:28' WHERE order_id = 63780; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-17', date_added = '2025-12-05 19:49:46', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63759; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-16', date_added = '2025-12-04 21:20:40', date_modified = '2025-12-23 18:54:45' WHERE order_id = 63739; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-18', date_added = '2025-12-04 19:14:35', date_modified = '2025-12-09 18:42:37' WHERE order_id = 63734; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-17', date_added = '2025-12-02 19:55:01', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63695; 
 UPDATE ots_order SET retailer_shipping_time = '2025-11-13', date_added = '2025-11-06 20:03:07', date_modified = '2025-11-11 18:30:09' WHERE order_id = 63306; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-19', date_added = '2025-12-15 15:07:16', date_modified = '2026-01-25 03:25:05' WHERE order_id = 1295; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-29', date_added = '2025-12-09 22:47:06', date_modified = '2025-12-15 18:41:02' WHERE order_id = 63791; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-05', date_added = '2025-12-01 21:37:38', date_modified = '2026-01-25 03:12:11' WHERE order_id = 63679; 

 -- 待执行：待v4 数据录入后，v4 order_id 改为 v3 order_id 后执行 
 UPDATE ots_order SET retailer_shipping_time = '2025-11-28', date_added = '2025-11-18 01:46:36', date_modified = '2026-01-25 03:12:07' WHERE order_id = 63476; 
 UPDATE ots_order SET retailer_shipping_time = '2025-11-17', date_added = '2025-11-06 16:58:22', date_modified = '2025-11-10 12:23:30' WHERE order_id = 63296; 
 UPDATE ots_order SET retailer_shipping_time = '2025-11-21', date_added = '2025-11-04 20:52:52', date_modified = '2025-11-10 12:23:30' WHERE order_id = 63253; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-17', date_added = '2025-12-17 12:48:23', date_modified = '2026-01-07 12:09:59' WHERE order_id = 5865; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-24', date_added = '2025-12-12 15:05:38', date_modified = '2025-12-31 15:46:07' WHERE order_id = 6824; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-22', date_added = '2025-12-16 15:29:14', date_modified = '2025-12-31 15:45:44' WHERE order_id = 6827; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-19', date_added = '2025-12-05 22:02:41', date_modified = '2026-01-23 18:51:02' WHERE order_id = 1294; 
 UPDATE ots_order SET retailer_shipping_time = '2025-12-01', date_added = '2025-11-25 20:40:32', date_modified = '2025-12-09 22:30:14' WHERE order_id = 1293; 
 </code></pre> 
 ** 6.2 order_product 的date_added，date_modified 
 <pre><code class="sql"> 
 -- 已执行：原Excel *100001 ~ 100019* 的记录 
 UPDATE ots_order_product SET date_added = '2026-01-24 00:04:48', date_modified = '2026-01-24 00:06:06' WHERE order_id = 64251; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:56:27', date_modified = '2026-01-23 16:56:49' WHERE order_id = 64241; 
 UPDATE ots_order_product SET date_added = '2026-01-23 23:10:31', date_modified = '2026-01-23 23:10:31' WHERE order_id = 64250; 
 UPDATE ots_order_product SET date_added = '2026-01-23 23:00:41', date_modified = '2026-01-23 23:00:41' WHERE order_id = 64249; 
 UPDATE ots_order_product SET date_added = '2026-01-23 22:06:57', date_modified = '2026-01-24 09:13:08' WHERE order_id = 64248; 
 UPDATE ots_order_product SET date_added = '2026-01-23 22:06:31', date_modified = '2026-01-23 22:06:31' WHERE order_id = 64247; 
 UPDATE ots_order_product SET date_added = '2026-01-23 20:09:09', date_modified = '2026-01-23 20:09:09' WHERE order_id = 64246; 
 UPDATE ots_order_product SET date_added = '2026-01-23 19:54:01', date_modified = '2026-01-23 20:54:39' WHERE order_id = 64245; 
 UPDATE ots_order_product SET date_added = '2026-01-23 19:35:38', date_modified = '2026-01-23 19:36:47' WHERE order_id = 64244; 
 UPDATE ots_order_product SET date_added = '2026-01-23 19:32:02', date_modified = '2026-01-23 19:32:02' WHERE order_id = 64243; 
 UPDATE ots_order_product SET date_added = '2026-01-23 18:16:53', date_modified = '2026-01-23 18:16:53' WHERE order_id = 64242; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:48:59', date_modified = '2026-01-23 16:48:59' WHERE order_id = 64240; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:49:21', date_modified = '2026-01-25 02:19:04' WHERE order_id = 1298; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:36:44', date_modified = '2026-01-25 02:20:57' WHERE order_id = 6865; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:35:10', date_modified = '2026-01-24 09:13:36' WHERE order_id = 64239; 
 UPDATE ots_order_product SET date_added = '2026-01-23 16:32:00', date_modified = '2026-01-25 02:18:43' WHERE order_id = 6864; 
 UPDATE ots_order_product SET date_added = '2026-01-23 15:52:13', date_modified = '2026-01-25 02:25:51' WHERE order_id = 6863; 
 UPDATE ots_order_product SET date_added = '2026-01-23 15:46:04', date_modified = '2026-01-25 02:25:45' WHERE order_id = 6862; 
 UPDATE ots_order_product SET date_added = '2026-01-23 15:10:33', date_modified = '2026-01-25 02:26:02' WHERE order_id = 6861; 

 -- 已执行：原Excel 标记 *IT修改日期* 的记录 
 UPDATE ots_order_product SET date_added = '2026-01-20 21:21:14', date_modified = '2026-01-25 02:34:48' WHERE order_id = 64192; 
 UPDATE ots_order_product SET date_added = '2025-12-15 16:31:39', date_modified = '2025-12-23 18:08:09' WHERE order_id = 63843; 
 UPDATE ots_order_product SET date_added = '2025-12-10 18:47:40', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63799; 
 UPDATE ots_order_product SET date_added = '2025-12-10 16:38:30', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63796; 
 UPDATE ots_order_product SET date_added = '2025-12-09 11:55:18', date_modified = '2025-12-15 18:41:02' WHERE order_id = 63782; 
 UPDATE ots_order_product SET date_added = '2025-12-08 21:24:07', date_modified = '2025-12-15 18:41:02' WHERE order_id = 63780; 
 UPDATE ots_order_product SET date_added = '2025-12-05 19:49:46', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63759; 
 UPDATE ots_order_product SET date_added = '2025-12-04 21:20:40', date_modified = '2025-12-23 12:29:19' WHERE order_id = 63739; 
 UPDATE ots_order_product SET date_added = '2025-12-04 19:14:35', date_modified = '2025-12-09 18:42:37' WHERE order_id = 63734; 
 UPDATE ots_order_product SET date_added = '2025-12-02 19:55:01', date_modified = '2025-12-16 18:42:21' WHERE order_id = 63695; 
 UPDATE ots_order_product SET date_added = '2025-11-06 20:03:07', date_modified = '2025-11-11 18:30:09' WHERE order_id = 63306; 
 UPDATE ots_order_product SET date_added = '2025-12-15 15:07:16', date_modified = '2026-01-25 03:25:05' WHERE order_id = 1295; 
 UPDATE ots_order_product SET date_added = '2025-12-09 22:47:06', date_modified = '2025-12-15 18:41:02' WHERE order_id = 63791; 
 UPDATE ots_order_product SET date_added = '2025-12-01 21:37:38', date_modified = '2026-01-25 03:12:11' WHERE order_id = 63679; 

 -- 待执行：待v4 数据录入后，v4 order_id 改为 v3 order_id 后执行 
 UPDATE ots_order_product SET date_added = '2025-11-18 01:46:36', date_modified = '2026-01-25 03:12:07' WHERE order_id = 63476; 
 UPDATE ots_order_product SET date_added = '2025-11-06 16:58:22', date_modified = '2025-11-10 12:23:30' WHERE order_id = 63296; 
 UPDATE ots_order_product SET date_added = '2025-11-04 20:52:52', date_modified = '2025-11-10 12:23:30' WHERE order_id = 63253; 
 UPDATE ots_order_product SET date_added = '2025-12-17 12:48:23', date_modified = '2026-01-07 12:09:59' WHERE order_id = 5865; 
 UPDATE ots_order_product SET date_added = '2025-12-12 15:05:38', date_modified = '2025-12-31 15:46:07' WHERE order_id = 6824; 
 UPDATE ots_order_product SET date_added = '2025-12-16 15:29:14', date_modified = '2025-12-31 15:45:44' WHERE order_id = 6827; 
 UPDATE ots_order_product SET date_added = '2025-12-05 22:02:41', date_modified = '2026-01-15 00:14:40' WHERE order_id = 1294; 
 UPDATE ots_order_product SET date_added = '2025-11-25 20:40:32', date_modified = '2025-12-09 22:30:14' WHERE order_id = 1293; 
 </code></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*

Back

Project

General

Profile

IT: OTS (China)

追加功能 #499