数据库设计

Mongodb

Collections设计

purchase_specification

{
  '_id': 'P001',  ## 主键
  'id': 'xxxx' ## 规格ID
  'name': 'xxxx' ## 采购规格名字，如果没有这个字段就 spu + 规格
  'version': 1 ## 版本号(默认从1开始)
  'category_a': 'xxxx',
  'category_b': 'xxxx',
  'pinlei': 'xxxx',
  'spu_id': 'c001'， ## spuid
  'price': 'xxx', ##采购单价
  'unit_name': 'xxxx', #采购单位
  'ratio': xxx, ## FLOAT 转化率。
  'station_id': 'T100',## 绑定的站点id
  'status': 1 代表激活，2代表删除
  'update_time': ISODate('xxxx'),
  'create_time': ISODate('xxxxx'),
}

station表逻辑变动

{
    "_id" : "T1088",
    "role" : 6,
删除"receive_begin_time" : "",
    "address" : "-",
    "code" : "T1088",
    "card_no" : "---",
删除"salemenu" : {},
删除"main_cate" : [
        "A817"
    ],
删除"receiver_phone" : "",
删除"receiver_name" : "",
    "contacter" : "崔姐",
删除"visible_salemenu" : [],
    "state" : 0,
    "suppliers" : [
        "T1069"
    ],
新增"merchandise": ['B100', 'B1001', ....]
新增"search_text":['蔬菜自采','GYS001',13627878989, 'SCZC']
## 通过采购sku，找到对应的二级分类
    "district_name" : "调兵山市",
删除"receive_end_time" : "",
    "account" : "----",
    "bank" : "---",
    "district_code" : "211281",
    "company_name" : "-",
    "phone" : "-",
    "pay_method" : 1,
    "contacter_phone" : "---",
    "name" : "崔姐干调",
    "business_licence" : "----",
删除 "enable_customized" : 0.0,
删除"key_url" : "xnn",
    "distribute_cities" : [
        "211281"
    ],
删除"pay_info_id" : "PAY0028",
删除"enable_pay_info" : 0
}

sku逻辑变动

去除掉采购sku
sku表变动

{
    "_id" : "D05026",
新增'supplier_id': 'D100'， ##新增supplier_id
新增'purchase_specification_id': 'P002', ##采购规格id
新增'purchase_create_version': 1,
    "is_weigh" : false,
    "s_type" : 0,
    "modify_time" : ISODate("2015-11-15T00:38:35.867Z"),
    "sale_num_least" : 1,
    "down_sku_id" : "D01745",
    "state" : 1,
    "material" : [
        {
            "std_sale_price" : 2700,
            "sale_ratio" : 1.0,
            "std_unit_name" : "斤",
            "default_supply" : "D02981",
            "upstream_flag" : 1,
            "supply_skus" : {
                "D02981" : {
                    "std_unit_cost" : 3200.0,
                    "unit_name" : "条",
                    "sku_id" : "D02981",
                    "ratio" : 1.0,
                    "station_id" : "T002"
                }
            },
            "spu_id" : "C00350"
        }
    ],
    "version" : 15,
    "stocks" : -99999,
    "split_flag" : 1,
    "sync_state" : 1,
    "sale_unit_name" : "条",
    "category_id_2" : "B012",
    "sale_price" : 2700,
    "name" : "鲜牛心顶|条",
    "station_id" : "T013",
    "desc" : "约1斤/条，牛的心脏顶部连接血脉和心脏的组织，韧性较高，可爆炒",
    "create_time" : ISODate("2015-06-08T12:03:49.692Z"),
    "salemenu_id" : "S0015",
    "imgs" : [
        "969d4df6a874d476.png"
    ],
    "category_id_1" : "A002",
    "stock_type" : 1
}

进销存相关表

采购记录purchase_sku_log 增加规格信息从采购任务生成的采购记录从采购任务保存的规格id+版本号获取规格信息手动生成的采购记录取生成时刻最新的规格信息

{
    "_id" : ObjectId("58008bec61617b0902608afe"),
    "purchase_amount" : 3.0,
    "purchase_operator" : "szqlnfcpfwz",
    "sheet_no" : "T338-CGD-2016-10-14-00001",
    "status" : 1,
    "purchase_price" : 100,
    "station_id" : "T338",
    "create_time" : ISODate("2016-10-14T15:40:28.267Z"),
    "sku_id" : "D649407",
    "arrived_price" : 100,
    "arrived_amount" : 3.0,
    "remark" : "",
新增 "purchase_name" : "河粉",
新增 "category_id_2" : "B020",
新增 "purchase_ratio" : 1.0,
新增 "spu_id" : "C68113",
新增 "pinlei_id" : "",
新增 "purchase_unit_name" : "斤",
新增"category_id_1" : "A003"

}

入库记录in_stock_log 增加规格信息从采购记录中获取规格信息从商户退货生成的入库记录从销售sku中获取对应版本的规格信息手动生成的入库记录取生成时刻最新的规格信息

{
    "_id" : ObjectId("56283d2161617b117a8515a0"),
    "station_id" : "T134",
    "in_stock_amount" : 2.3,
    "type" : 1,
    "price" : 500.0,
    "operator" : "dglhxinxi",
    "create_time" : ISODate("2015-10-22T09:34:25.859Z"),
    "purchase_sku_id" : "D103841",
    "sheet_no" : "JHD-2015-10-22-00004",
    "spu_id" : "C00878",
    新增 "purchase_name" : "河粉",
    新增 "category_id_2" : "B020",
    新增 "purchase_ratio" : 1.0,
    新增 "spu_id" : "C68113",
    新增 "pinlei_id" : "",
    新增 "purchase_unit_name" : "斤",
    新增"category_id_1" : "A003"
}

退货记录return_to_supply_log 增加规格信息手动生成的退货记录取生成时刻最新的规格信息

{
    "_id" : ObjectId("563704b861617b3fb90ae1d3"),
    "sheet_no" : "JHTHD-2015-11-02-00028",
    "station_id" : "T134",
    "return_amount" : 20.0,
    "price" : 500.0,
    "purchase_sku_id" : "D165521",
    "operator" : "dglhxinxi",
    "create_time" : ISODate("2015-11-02T14:37:44.084Z"),
    "spu_id" : "C01980",
    新增 "purchase_name" : "河粉",
    新增 "category_id_2" : "B020",
    新增 "purchase_ratio" : 1.0,
    新增 "spu_id" : "C68113",
    新增 "pinlei_id" : "",
    新增 "purchase_unit_name" : "斤",
    新增"category_id_1" : "A003"
}

Mysql表修改

tbl_purchase_task


+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| order_id         | varchar(20)  | YES  | MUL |         |                |
| sale_sku_id      | varchar(20)  | YES  |     | NULL    |                |
| status           | int(11)      | NO   |     | NULL    |                |
| task_id          | int(11)      | NO   | MUL | NULL    |                |
| plan_amount      | float        | NO   |     | NULL    |                |
| purchase_amount  | float        | NO   |     | 0       |                |
| create_time      | datetime     | NO   |     | NULL    |                |
| modify_time      | datetime     | NO   |     | NULL    |                |
| extra_1          | int(11)      | YES  |     | NULL    |                |
| extra_2          | varchar(128) | YES  |     | NULL    |                |
| station_id       | varchar(16)  | NO   |     |         |                |
| time_config_id   | varchar(16)  | NO   |     |         |                |
| cycle_start_time | datetime     | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
新增字段
'version': 1, ## 规格版本
'purchase_spec_id': 'xxxx', ## 采购规格id

需要刷的数据库

将采购sku刷到采购规格里面。
将销售sku里面的supplier_sku的信息，刷到采购规格purchase_spec_id。
需要将采购sku的字段，刷到退货记录return_to_supply_log，入库记录in_stock_log，采购记录purchase_sku_log里面。