Page Comparison

一、数据的导入与导出

1.库位数据导出与导入
（*根据需要导出，之前已经导入过一些数据:19站点的ZONE 1,ZONE 8,ZONE 9）

• 申通导入模板文件
  

...

• 鸟系统库区信息导出
  (1)连接线上数据库
  (2)执行sql

  Code Block
  language sql
  SELECT zone.NAME AS "编码", zone.NAME AS "名称", 500 AS "面积", 3 AS "行数", 30 AS "列数", 3 AS "层数" FROM zone LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id WHERE warehouse.company_id = {company_id} AND zone.STATUS = "ACTIVE";

  
  其中{company_id}表示具体的站点ID
  (3)示例数据如下，将生成的结果复制到库区模板，并根据需要修改编码及名称，这个编码在后续库位导入会需要用到
  

  Image RemovedImage Added

• 鸟系统库区信息导入申通系统
  联系对方技术或者英仓同事登录申通系统导入即可

...

• 鸟系统库位信息导出
  (1)连接线上数据库
  (2)执行sql，按照一个库区导一份文件的方式来导，因为多个库区的库位信息太多

  Code Block
  language sql
  SELECT container.id AS "库位编码", "" AS "库位名称", "{code}" AS "库区编码", 20 AS "长", 10 AS "宽", 15 AS "高", "拣货" AS "类型", "手工" AS "操作方式", "否" AS "机器人库位", "否" AS "租用", "小" AS "大小类型" FROM container LEFT JOIN aisle ON container.aisle_id = aisle.id LEFT JOIN zone ON aisle.zone_id = zone.id LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id WHERE warehouse.company_id = {company_id} AND zone.NAME = "{zone_name}" AND container.STATUS = "ACTIVE";

其中,{code}表示当前要导出的库区的编码(也就是上一步导出的库区编码),{company_id}表示具体的站点ID,{zone_name} 表示的是需要导出的库区对应的鸟系统的仓库区域名称
(3)示例数据如下，将生成的结果复制到库位模板，库区编码以及鸟系统仓库区域名称需要对应好

...

• 鸟系统产品信息导出
  (1)连接线上数据库
  (2)执行sql
  

  Code Block
  language sql
  SELECT id AS barcode, id, NAME, IF(length( name_customs )!= char_length( name_customs ), "other", name_customs), "非自带包装", "公制单位", IF(weight = 0, 0.1, weight / 1000 ) AS weight_kg, (IF(length = 0, 0.1, length ) / 10) AS length_cm, (IF(width = 0, 0.1, width ) / 10 ) AS width_cm, (IF(depth = 0, 0.1, depth ) / 10 ) AS height_cm, "", "", "", "", "", "", "否" FROM product WHERE client_id IN ({client_id}) AND STATUS = "ACTIVE";

其中{client_id}表示具体的客户的ID
(3)示例数据如下，将生成的结果复制到产品模板

...

• 导入申通系统
  登录申通客户端系统，点货品档案，导入按钮，上传文件，等待上传完毕，查看右边日志信息，根据报错信息修改

...

3.渠道数据导出与导入
（*根据需要导出，之前已经导入过一些数据:19站点的本地类型并且是正常状态的外部邮递服务，需确认有无新增邮递服务）

• 申通导入模板文件
  

• 鸟系统邮递服务信息导出
  (1)连接线上数据库
  (2)执行sql
  

  Code Block
  language sql
  SELECT delivery_service.id, concat("FN_",delivery_service.NAME) as delivery_service_name, consignment_delivery_agent.NAME as delivery_service_agent_name FROM delivery_service LEFT JOIN consignment_delivery_agent ON consignment_delivery_agent.id = delivery_service.consignment_delivery_agent_id WHERE delivery_service.company_id = {company_id} AND STATUS = "ACTIVE" AND consignment_type = "LOCAL" AND is_internal = 0;

其中{company_id}表示具体的站点ID
(3)示例数据如下

...

• 鸟系统渠道信息导入申通系统
  联系对方技术导入

4.库存数据导出与导入
（*操作之前需保证相应的用户的库存信息不再变动）

• 申通导入模板文件
  

...

• 鸟系统库存信息导出
  (1)连接线上数据库
  (2)执行sql
  

  Code Block
  language sql
  SELECT product.id, container.reference, "合格", container_product.quantity, DATE(NOW()) as date_time FROM container_product LEFT JOIN container ON container.id = container_product.container_id LEFT JOIN aisle ON aisle.id = container.aisle_id LEFT JOIN zone ON zone.id = aisle.zone_id LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id LEFT JOIN product ON product.id = container_product.product_id WHERE warehouse.company_id = {company_id} AND product.client_id IN ( {client_id} );

其中{client_id}表示具体的客户的ID,{company_id}表示具体的站点ID
(3)示例数据如下，将生成的结果复制到库存模板

...

• 鸟系统库存信息导入申通系统
  联系对方技术导入

• 鸟系统库存信息容器号修改
  (因为在鸟系统上需要有产品库存记录，但都是在一个固定的虚拟容器号里，所以需要把现有的库存数据都转到固定的容器号里，这个固定容器号在配置里由英仓确定使用的一个容器号，现在使用的是666666)
  1.预先保留原始库存数据
  (1)连接线上数据库
  (2)执行sql
  

  Code Block
  language sql
  SELECT container.reference, product.id, container_product.quantity AS quantity FROM container_product LEFT JOIN container ON container.id = container_product.container_id LEFT JOIN aisle ON aisle.id = container.aisle_id LEFT JOIN zone ON zone.id = aisle.zone_id LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id LEFT JOIN product ON product.id = container_product.product_id WHERE warehouse.company_id = {company_id} AND product.client_id IN ( {client_id} );

  
  其中{client_id}表示具体的客户的ID,{company_id}表示具体的站点ID
  2.先扣减库存，再增加库存
  (1)连接线上数据库
  (2)执行sql,用于转移到固定容器的库存数据
  

  Code Block

  SELECT {container_reference} as container_reference, product.id, container_product.quantity AS quantity FROM container_product LEFT JOIN container ON container.id = container_product.container_id LEFT JOIN aisle ON aisle.id = container.aisle_id LEFT JOIN zone ON zone.id = aisle.zone_id LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id LEFT JOIN product ON product.id = container_product.product_id WHERE warehouse.company_id = {company_id} AND product.client_id IN ( {client_id} );

  
  其中{container_reference}为固定的虚拟容器号{client_id}表示具体的客户的ID,{company_id}表示具体的站点ID
  (3)执行sql，用于扣减库存
  

  Code Block
  language sql
  SELECT container.reference, product.id,( container_product.quantity * - 1 ) AS quantity FROM container_product LEFT JOIN container ON container.id = container_product.container_id LEFT JOIN aisle ON aisle.id = container.aisle_id LEFT JOIN zone ON zone.id = aisle.zone_id LEFT JOIN warehouse ON warehouse.id = zone.warehouse_id LEFT JOIN product ON product.id = container_product.product_id WHERE warehouse.company_id = {company_id} AND product.client_id IN ( {client_id} );

其中{client_id}表示具体的客户的ID,{company_id}表示具体的站点ID

(4)将扣减库存，将(3)的结果在鸟系统库存记录上传的结果复制到模板文件（在弹出框下载）中在鸟系统库存记录上传

...

(5) 再将添加库存，再将(2)的结果也在鸟系统库存记录上传

...

的结果也复制到模板文件(重新下载模板文件)中在鸟系统库存记录上传

...