feat(scripts): 添加MPS生产计划与销售订单关联匹配脚本

云

18 小时以前 9dc4057ee86abf7a69e7d7a8720a41efa8dabdcc

feat(scripts): 添加MPS生产计划与销售订单关联匹配脚本

- 新增 mps-link-data-audit.py 脚本用于审计MPS数据
- 新增 mps-link-data-match.py 脚本实现生产计划与销售订单严格匹配
- 添加匹配结果文件 confirmed-candidates.json 和统计摘要
- 生成备份文件 updated-records-backup.json 确保数据安全
- 输出CSV格式匹配结果便于人工审核验证
- 实现自动关联功能支持批量处理MPS销售订单匹配

 scripts/mps-link-data-20261014/audit-snapshot.json

¶Ô±ÈÐÂÎļþ
ÎļþÌ«´ó

 scripts/mps-link-data-20261014/confirmed-candidates.json

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,37 @@
[
  {
    "mps_id": 3456,
    "mps_code": "MPS20260910-CL-0023",
    "order_id": 1031,
    "order_no": "26000458",
    "item_id": 4145
  },
  {
    "mps_id": 3459,
    "mps_code": "MPS20260911-CL-0026",
    "order_id": 1028,
    "order_no": "26000442",
    "item_id": 4141
  },
  {
    "mps_id": 3462,
    "mps_code": "MPS20260911-CL-0029",
    "order_id": 1026,
    "order_no": "26000111",
    "item_id": 4131
  },
  {
    "mps_id": 3476,
    "mps_code": "MPS20260917-CL-0043",
    "order_id": 1027,
    "order_no": "26000427",
    "item_id": 4135
  },
  {
    "mps_id": 3482,
    "mps_code": "MPS20260918-CL-0049",
    "order_id": 1031,
    "order_no": "26000458",
    "item_id": 4146
  }
]

 scripts/mps-link-data-20261014/strict-audit-before.json

¶Ô±ÈÐÂÎļþ
ÎļþÌ«´ó

 scripts/mps-link-data-20261014/strict-match-summary.json

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,10 @@
{
  "total_plans": 56,
  "unique_strict_matches": 5,
  "applied": 5,
  "results": {
    "产品无对应销售明细": 42,
    "已关联": 5,
    "数量不一致": 9
  }
}

 scripts/mps-link-data-20261014/updated-records-backup.json

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,247 @@
[
  {
    "plan": {
      "id": 3456,
      "code": "MPS20260910-CL-0023",
      "status": 2,
      "sale_order_id": null,
      "sale_order_no": null,
      "sale_order_item_id": null,
      "product_id": 1508,
      "quantity": "4000.000000",
      "quantity_produced": "4000.000000",
      "unit_measure_id": 9,
      "client_id": null,
      "request_date": "2026-09-11 08:00:00",
      "issue_date": "2026-09-10 09:48:24",
      "finish_date": "2026-09-12 03:13:00",
      "work_order_id": 52,
      "work_order_code": "WO_20260911001",
      "merge_flag": 0,
      "merge_batch_no": null,
      "remark": "2026å¹´9月产量计划（枸杞原浆-900ml（青源堂））",
      "creator": "1",
      "create_time": "2026-09-10 09:45:33",
      "updater": "1",
      "update_time": "2026-09-10 09:48:30",
      "deleted": "b'\\x00'",
      "tenant_id": 0
    },
    "item": {
      "id": 4145,
      "order_id": 1031,
      "product_id": 1508,
      "product_unit_id": 9,
      "product_price": "23.920400",
      "count": "4000.000000",
      "total_price": "95681.600000",
      "tax_percent": "13.000000",
      "tax_price": "12438.610000",
      "remark": "台账行54；原单26000458；原项目号Q2100082；原净额95681.6，税12438.608，含税108120.208",
      "creator": "291",
      "create_time": "2026-09-20 00:00:00",
      "updater": "291",
      "update_time": "2026-10-13 14:13:07",
      "deleted": "b'\\x00'",
      "out_count": "4000.000000",
      "need_production": 0,
      "mps_id": null
    }
  },
  {
    "plan": {
      "id": 3459,
      "code": "MPS20260911-CL-0026",
      "status": 2,
      "sale_order_id": null,
      "sale_order_no": null,
      "sale_order_item_id": null,
      "product_id": 1544,
      "quantity": "5037.000000",
      "quantity_produced": "5037.000000",
      "unit_measure_id": 31,
      "client_id": null,
      "request_date": "2026-09-12 08:00:00",
      "issue_date": "2026-09-11 12:27:51",
      "finish_date": "2026-09-12 21:31:00",
      "work_order_id": 55,
      "work_order_code": "WO_20260912002",
      "merge_flag": 0,
      "merge_batch_no": null,
      "remark": "2026å¹´9月产量计划（黑果枸杞-150克）",
      "creator": "1",
      "create_time": "2026-09-11 12:24:57",
      "updater": "1",
      "update_time": "2026-09-11 12:28:00",
      "deleted": "b'\\x00'",
      "tenant_id": 0
    },
    "item": {
      "id": 4141,
      "order_id": 1028,
      "product_id": 1544,
      "product_unit_id": 31,
      "product_price": "15.477099",
      "count": "5037.000000",
      "total_price": "77958.150000",
      "tax_percent": "9.000000",
      "tax_price": "7016.230000",
      "remark": "台账行50；原单26000442；原项目号U2100794；原净额77958.15，税7016.2335，含税84974.3835",
      "creator": "291",
      "create_time": "2026-09-20 00:00:00",
      "updater": "291",
      "update_time": "2026-10-13 14:13:07",
      "deleted": "b'\\x00'",
      "out_count": "5037.000000",
      "need_production": 0,
      "mps_id": null
    }
  },
  {
    "plan": {
      "id": 3462,
      "code": "MPS20260911-CL-0029",
      "status": 2,
      "sale_order_id": null,
      "sale_order_no": null,
      "sale_order_item_id": null,
      "product_id": 991,
      "quantity": "22.000000",
      "quantity_produced": "22.000000",
      "unit_measure_id": 9,
      "client_id": null,
      "request_date": "2026-09-14 08:00:00",
      "issue_date": "2026-09-11 15:06:18",
      "finish_date": "2026-09-14 16:12:00",
      "work_order_id": 58,
      "work_order_code": "WO_20260914001",
      "merge_flag": 0,
      "merge_batch_no": null,
      "remark": "2026å¹´9月产量计划（鲜冬虫夏草（电视购物））",
      "creator": "1",
      "create_time": "2026-09-11 15:03:21",
      "updater": "1",
      "update_time": "2026-09-11 15:06:30",
      "deleted": "b'\\x00'",
      "tenant_id": 0
    },
    "item": {
      "id": 4131,
      "order_id": 1026,
      "product_id": 991,
      "product_unit_id": 9,
      "product_price": "596.700000",
      "count": "22.000000",
      "total_price": "13127.400000",
      "tax_percent": "0.000000",
      "tax_price": "0.000000",
      "remark": "台账行40；原单26000111；原项目号12101918；原净额13127.4，税0，含税13127.4",
      "creator": "291",
      "create_time": "2026-09-22 00:00:00",
      "updater": "291",
      "update_time": "2026-10-13 14:13:07",
      "deleted": "b'\\x00'",
      "out_count": "22.000000",
      "need_production": 0,
      "mps_id": null
    }
  },
  {
    "plan": {
      "id": 3476,
      "code": "MPS20260917-CL-0043",
      "status": 2,
      "sale_order_id": null,
      "sale_order_no": null,
      "sale_order_item_id": null,
      "product_id": 1521,
      "quantity": "2.000000",
      "quantity_produced": "2.000000",
      "unit_measure_id": 8,
      "client_id": null,
      "request_date": "2026-09-20 08:00:00",
      "issue_date": "2026-09-17 14:08:04",
      "finish_date": "2026-09-20 16:35:00",
      "work_order_id": 72,
      "work_order_code": "WO_20260920002",
      "merge_flag": 0,
      "merge_batch_no": null,
      "remark": "2026å¹´9月产量计划（鲜冬虫夏草（抖音直播））",
      "creator": "1",
      "create_time": "2026-09-17 14:04:53",
      "updater": "1",
      "update_time": "2026-09-17 14:08:30",
      "deleted": "b'\\x00'",
      "tenant_id": 0
    },
    "item": {
      "id": 4135,
      "order_id": 1027,
      "product_id": 1521,
      "product_unit_id": 8,
      "product_price": "248.450000",
      "count": "2.000000",
      "total_price": "496.900000",
      "tax_percent": "0.000000",
      "tax_price": "0.000000",
      "remark": "台账行44；原单26000427；原项目号QZ000516；原净额496.9，税0，含税496.9",
      "creator": "291",
      "create_time": "2026-09-03 00:00:00",
      "updater": "291",
      "update_time": "2026-10-13 14:13:07",
      "deleted": "b'\\x00'",
      "out_count": "2.000000",
      "need_production": 0,
      "mps_id": null
    }
  },
  {
    "plan": {
      "id": 3482,
      "code": "MPS20260918-CL-0049",
      "status": 2,
      "sale_order_id": null,
      "sale_order_no": null,
      "sale_order_item_id": null,
      "product_id": 1550,
      "quantity": "2018.000000",
      "quantity_produced": "2018.000000",
      "unit_measure_id": 9,
      "client_id": null,
      "request_date": "2026-09-23 08:00:00",
      "issue_date": "2026-09-18 11:26:58",
      "finish_date": "2026-09-23 17:40:00",
      "work_order_id": 78,
      "work_order_code": "WO_20260923002",
      "merge_flag": 0,
      "merge_batch_no": null,
      "remark": "2026å¹´9月产量计划（锁鲜冬虫夏草-4克）",
      "creator": "1",
      "create_time": "2026-09-18 11:23:41",
      "updater": "1",
      "update_time": "2026-09-18 11:27:30",
      "deleted": "b'\\x00'",
      "tenant_id": 0
    },
    "item": {
      "id": 4146,
      "order_id": 1031,
      "product_id": 1550,
      "product_unit_id": 9,
      "product_price": "190.950000",
      "count": "2018.000000",
      "total_price": "385337.100000",
      "tax_percent": "0.000000",
      "tax_price": "0.000000",
      "remark": "台账行55；原单26000458；原项目号UZ000042；原净额385337.1，税0，含税385337.1",
      "creator": "291",
      "create_time": "2026-09-20 00:00:00",
      "updater": "291",
      "update_time": "2026-10-13 14:13:07",
      "deleted": "b'\\x00'",
      "out_count": "2018.000000",
      "need_production": 0,
      "mps_id": null
    }
  }
]

 scripts/mps-link-data-20261014/Éú²úÖ÷¼Æ»®ÏúÊÛ¶©µ¥ÑϸñÆ¥Åä½á¹û.csv

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,57 @@
主计划ID,主计划编码,产品编码,产品名称,计划数量,计划单位,需求日期,同产品候选数,同数量候选数,同单位候选数,同月份候选数,候选销售单号,结果
3434,MPS20260828-CL-0001,12100335,冻干枸杞,80.000000,盒,2026-09-01 08:00:00,0,0,0,0,,产品无对应销售明细
3435,MPS20260831-CL-0002,12101052,有机黑果枸杞,72.000000,盒,2026-09-01 08:00:00,0,0,0,0,,产品无对应销售明细
3436,MPS20260828-CL-0003,12101053,有机黑果枸杞,108.000000,盒,2026-09-01 08:00:00,0,0,0,0,,产品无对应销售明细
3437,MPS20260828-CL-0004,12101054,有机黑果枸杞,72.000000,盒,2026-09-02 08:00:00,0,0,0,0,,产品无对应销售明细
3438,MPS20260901-CL-0005,12100934,黑果枸杞,195.000000,盒,2026-09-02 08:00:00,0,0,0,0,,产品无对应销售明细
3439,MPS20260901-CL-0006,12100329,黑果枸杞袋泡茶,120.000000,盒,2026-09-03 08:00:00,0,0,0,0,,产品无对应销售明细
3440,MPS20260831-CL-0007,12101008,黑果枸杞原浆罐头,1023.000000,盒,2026-09-03 08:00:00,0,0,0,0,,产品无对应销售明细
3441,MPS20260903-CL-0008,12101005,枸杞原浆罐头,1078.000000,盒,2026-09-04 08:00:00,0,0,0,0,,产品无对应销售明细
3442,MPS20260902-CL-0009,12101011,沙棘原浆罐头,108.000000,盒,2026-09-04 08:00:00,0,0,0,0,,产品无对应销售明细
3443,MPS20260902-CL-0010,12101010,沙棘原浆罐头,120.000000,盒,2026-09-05 08:00:00,0,0,0,0,,产品无对应销售明细
3444,MPS20260904-CL-0011,12100441,枸杞原浆罐头,40.000000,盒,2026-09-05 08:00:00,0,0,0,0,,产品无对应销售明细
3445,MPS20260904-CL-0012,28200023,有机黑果枸杞,200.000000,罐,2026-09-06 08:00:00,0,0,0,0,,产品无对应销售明细
3446,MPS20260903-CL-0013,18200098,冻干枸杞,180.000000,袋,2026-09-06 08:00:00,0,0,0,0,,产品无对应销售明细
3447,MPS20260904-CL-0014,28002153,即食小罐洞燕窝,3040.000000,盒,2026-09-07 08:00:00,0,0,0,0,,产品无对应销售明细
3448,MPS20260904-CL-0015,22102130,即食小罐洞燕窝,900.000000,盒,2026-09-07 08:00:00,0,0,0,0,,产品无对应销售明细
3449,MPS20260904-CL-0016,22102072,即食小罐洞燕窝,400.000000,盒,2026-09-08 08:00:00,0,0,0,0,,产品无对应销售明细
3450,MPS20260907-CL-0017,22102093,即食小罐洞燕窝（尊享款）,60.000000,盒,2026-09-08 08:00:00,0,0,0,0,,产品无对应销售明细
3451,MPS20260907-CL-0018,28002225,即食小罐洞燕窝(尊享款),320.000000,罐,2026-09-09 08:00:00,0,0,0,0,,产品无对应销售明细
3452,MPS20260904-CL-0019,22102128,即食小罐洞燕窝（尊享款）,60.000000,盒,2026-09-09 08:00:00,0,0,0,0,,产品无对应销售明细
3453,MPS20260908-CL-0020,12100543,洞燕窝即食碗装燕窝,80.000000,盒,2026-09-09 08:00:00,0,0,0,0,,产品无对应销售明细
3454,MPS20260908-CL-0021,22102095,洞燕窝即食碗装燕窝,160.000000,盒,2026-09-10 08:00:00,0,0,0,0,,产品无对应销售明细
3455,MPS20260907-CL-0022,22102143,即食碗装洞燕窝（怡碗燕）,196.000000,盒,2026-09-10 08:00:00,0,0,0,0,,产品无对应销售明细
3456,MPS20260910-CL-0023,Q2100082,枸杞原浆罐头,4000.000000,盒,2026-09-11 08:00:00,1,1,1,1,26000458,已关联
3457,MPS20260909-CL-0024,U2100336,血红素铁红枣饮,4048.000000,盒,2026-09-11 08:00:00,0,0,0,0,,产品无对应销售明细
3458,MPS20260909-CL-0025,U2100335,血蛋白营养素饮,10140.000000,盒,2026-09-12 08:00:00,0,0,0,0,,产品无对应销售明细
3459,MPS20260911-CL-0026,U2100794,黑果枸杞,5037.000000,罐,2026-09-12 08:00:00,1,1,1,1,26000442,已关联
3460,MPS20260911-CL-0027,U2100832,液体钙铁锌镁果蔬饮,8055.000000,盒,2026-09-13 08:00:00,0,0,0,0,,产品无对应销售明细
3461,MPS20260910-CL-0028,74000650,鲜冬虫夏草,36.000000,盒,2026-09-13 08:00:00,1,0,0,0,,数量不一致
3462,MPS20260911-CL-0029,12101918,鲜冬虫夏草,22.000000,盒,2026-09-14 08:00:00,1,1,1,1,26000111,已关联
3463,MPS20260911-CL-0030,74000628,鲜冬虫夏草,4.000000,盒,2026-09-14 08:00:00,0,0,0,0,,产品无对应销售明细
3464,MPS20260911-CL-0031,12100728,鲜冬虫夏草,28.000000,盒,2026-09-15 08:00:00,2,0,0,0,,数量不一致
3465,MPS20260914-CL-0032,12101002,鲜冬虫夏草,3.000000,æ ¹,2026-09-15 08:00:00,1,0,0,0,,数量不一致
3466,MPS20260914-CL-0033,QZ100077,鲜冬虫夏草,4.000000,盒,2026-09-16 08:00:00,0,0,0,0,,产品无对应销售明细
3467,MPS20260911-CL-0034,QZ100078,鲜冬虫夏草,4.000000,盒,2026-09-16 08:00:00,0,0,0,0,,产品无对应销售明细
3468,MPS20260916-CL-0035,QZ000499,鲜冬虫夏草,1.000000,盒,2026-09-17 08:00:00,0,0,0,0,,产品无对应销售明细
3469,MPS20260915-CL-0036,QZ000502,鲜冬虫夏草,2.000000,盒,2026-09-17 08:00:00,0,0,0,0,,产品无对应销售明细
3470,MPS20260915-CL-0037,QZ000504,鲜冬虫夏草,1.000000,盒,2026-09-18 08:00:00,0,0,0,0,,产品无对应销售明细
3471,MPS20260917-CL-0038,QZ000505,鲜冬虫夏草,1.000000,盒,2026-09-18 08:00:00,0,0,0,0,,产品无对应销售明细
3472,MPS20260916-CL-0039,QZ000507,鲜冬虫夏草,1.000000,盒,2026-09-18 08:00:00,0,0,0,0,,产品无对应销售明细
3473,MPS20260916-CL-0040,22102189,鲜冬虫夏草,34.000000,罐,2026-09-19 08:00:00,1,0,0,0,,数量不一致
3474,MPS20260918-CL-0041,12100991,鲜冬虫夏草,1.000000,æ ¹,2026-09-19 08:00:00,0,0,0,0,,产品无对应销售明细
3475,MPS20260918-CL-0042,QZ000523,鲜冬虫夏草,1.000000,瓶,2026-09-20 08:00:00,0,0,0,0,,产品无对应销售明细
3476,MPS20260917-CL-0043,QZ000516,鲜冬虫夏草,2.000000,瓶,2026-09-20 08:00:00,1,1,1,1,26000427,已关联
3477,MPS20260918-CL-0044,QZ000518,鲜冬虫夏草,2.000000,瓶,2026-09-21 08:00:00,0,0,0,0,,产品无对应销售明细
3478,MPS20260918-CL-0045,QZ000519,鲜冬虫夏草,13.000000,瓶,2026-09-21 08:00:00,1,0,0,0,,数量不一致
3479,MPS20260918-CL-0046,QZ000520,鲜冬虫夏草,357.000000,瓶,2026-09-22 08:00:00,1,0,0,0,,数量不一致
3480,MPS20260921-CL-0047,QZ000525,鲜冬虫夏草,268.000000,瓶,2026-09-22 08:00:00,0,0,0,0,,产品无对应销售明细
3481,MPS20260921-CL-0048,QZ000522,鲜冬虫夏草,254.000000,瓶,2026-09-23 08:00:00,1,0,0,0,,数量不一致
3482,MPS20260918-CL-0049,UZ000042,锁鲜冬虫夏草,2018.000000,盒,2026-09-23 08:00:00,2,1,1,1,26000458,已关联
3483,MPS20260923-CL-0050,QZ000529,锁鲜冬虫夏草,500.000000,板,2026-09-24 08:00:00,0,0,0,0,,产品无对应销售明细
3484,MPS20260922-CL-0051,QZ000528,锁鲜冬虫夏草,500.000000,板,2026-09-24 08:00:00,0,0,0,0,,产品无对应销售明细
3485,MPS20260922-CL-0052,QZ000531,锁鲜冬虫夏草,500.000000,瓶,2026-09-25 08:00:00,0,0,0,0,,产品无对应销售明细
3486,MPS20260924-CL-0053,QZ000532,锁鲜冬虫夏草,30000.000000,盒,2026-09-25 08:00:00,2,0,0,0,,数量不一致
3487,MPS20260924-CL-0054,74000489,冻干枸杞（金标）（内购专用）,100.000000,盒,2026-09-26 08:00:00,0,0,0,0,,产品无对应销售明细
3488,MPS20260923-CL-0055,74000486,黎麦大礼包（内购专用）,100.000000,盒,2026-09-26 08:00:00,0,0,0,0,,产品无对应销售明细
3489,MPS20260925-CL-0056,74000485,西北精致礼盒（内购专用）,100.000000,盒,2026-09-27 08:00:00,1,0,0,0,,数量不一致

 scripts/mps-link-data-20261014/Éú²úÖ÷¼Æ»®ÏúÊÛ¶©µ¥¹ØÁªºòÑ¡.csv

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,57 @@
主计划ID,主计划编码,产品编码,产品名称,计划数量,需求日期,同产品订单明细数,同产品同数量明细数,候选销售单号,同产品同数量候选,确认销售单号,说明
3434,MPS20260828-CL-0001,12100335,冻干枸杞,80.000000,2026-09-01 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3435,MPS20260831-CL-0002,12101052,有机黑果枸杞,72.000000,2026-09-01 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3436,MPS20260828-CL-0003,12101053,有机黑果枸杞,108.000000,2026-09-01 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3437,MPS20260828-CL-0004,12101054,有机黑果枸杞,72.000000,2026-09-02 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3438,MPS20260901-CL-0005,12100934,黑果枸杞,195.000000,2026-09-02 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3439,MPS20260901-CL-0006,12100329,黑果枸杞袋泡茶,120.000000,2026-09-03 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3440,MPS20260831-CL-0007,12101008,黑果枸杞原浆罐头,1023.000000,2026-09-03 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3441,MPS20260903-CL-0008,12101005,枸杞原浆罐头,1078.000000,2026-09-04 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3442,MPS20260902-CL-0009,12101011,沙棘原浆罐头,108.000000,2026-09-04 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3443,MPS20260902-CL-0010,12101010,沙棘原浆罐头,120.000000,2026-09-05 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3444,MPS20260904-CL-0011,12100441,枸杞原浆罐头,40.000000,2026-09-05 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3445,MPS20260904-CL-0012,28200023,有机黑果枸杞,200.000000,2026-09-06 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3446,MPS20260903-CL-0013,18200098,冻干枸杞,180.000000,2026-09-06 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3447,MPS20260904-CL-0014,28002153,即食小罐洞燕窝,3040.000000,2026-09-07 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3448,MPS20260904-CL-0015,22102130,即食小罐洞燕窝,900.000000,2026-09-07 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3449,MPS20260904-CL-0016,22102072,即食小罐洞燕窝,400.000000,2026-09-08 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3450,MPS20260907-CL-0017,22102093,即食小罐洞燕窝（尊享款）,60.000000,2026-09-08 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3451,MPS20260907-CL-0018,28002225,即食小罐洞燕窝(尊享款),320.000000,2026-09-09 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3452,MPS20260904-CL-0019,22102128,即食小罐洞燕窝（尊享款）,60.000000,2026-09-09 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3453,MPS20260908-CL-0020,12100543,洞燕窝即食碗装燕窝,80.000000,2026-09-09 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3454,MPS20260908-CL-0021,22102095,洞燕窝即食碗装燕窝,160.000000,2026-09-10 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3455,MPS20260907-CL-0022,22102143,即食碗装洞燕窝（怡碗燕）,196.000000,2026-09-10 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3456,MPS20260910-CL-0023,Q2100082,枸杞原浆罐头,4000.000000,2026-09-11 08:00:00,1,1,26000458,26000458（明细4145）,,待核对单位、月份及实际业务对应关系
3457,MPS20260909-CL-0024,U2100336,血红素铁红枣饮,4048.000000,2026-09-11 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3458,MPS20260909-CL-0025,U2100335,血蛋白营养素饮,10140.000000,2026-09-12 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3459,MPS20260911-CL-0026,U2100794,黑果枸杞,5037.000000,2026-09-12 08:00:00,1,1,26000442,26000442（明细4141）,,待核对单位、月份及实际业务对应关系
3460,MPS20260911-CL-0027,U2100832,液体钙铁锌镁果蔬饮,8055.000000,2026-09-13 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3461,MPS20260910-CL-0028,74000650,鲜冬虫夏草,36.000000,2026-09-13 08:00:00,1,0,26000111,,,待核对单位、月份及实际业务对应关系
3462,MPS20260911-CL-0029,12101918,鲜冬虫夏草,22.000000,2026-09-14 08:00:00,1,1,26000111,26000111（明细4131）,,待核对单位、月份及实际业务对应关系
3463,MPS20260911-CL-0030,74000628,鲜冬虫夏草,4.000000,2026-09-14 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3464,MPS20260911-CL-0031,12100728,鲜冬虫夏草,28.000000,2026-09-15 08:00:00,2,0,26000111,,,待核对单位、月份及实际业务对应关系
3465,MPS20260914-CL-0032,12101002,鲜冬虫夏草,3.000000,2026-09-15 08:00:00,1,0,26000111,,,待核对单位、月份及实际业务对应关系
3466,MPS20260914-CL-0033,QZ100077,鲜冬虫夏草,4.000000,2026-09-16 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3467,MPS20260911-CL-0034,QZ100078,鲜冬虫夏草,4.000000,2026-09-16 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3468,MPS20260916-CL-0035,QZ000499,鲜冬虫夏草,1.000000,2026-09-17 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3469,MPS20260915-CL-0036,QZ000502,鲜冬虫夏草,2.000000,2026-09-17 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3470,MPS20260915-CL-0037,QZ000504,鲜冬虫夏草,1.000000,2026-09-18 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3471,MPS20260917-CL-0038,QZ000505,鲜冬虫夏草,1.000000,2026-09-18 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3472,MPS20260916-CL-0039,QZ000507,鲜冬虫夏草,1.000000,2026-09-18 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3473,MPS20260916-CL-0040,22102189,鲜冬虫夏草,34.000000,2026-09-19 08:00:00,1,0,26000106,,,待核对单位、月份及实际业务对应关系
3474,MPS20260918-CL-0041,12100991,鲜冬虫夏草,1.000000,2026-09-19 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3475,MPS20260918-CL-0042,QZ000523,鲜冬虫夏草,1.000000,2026-09-20 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3476,MPS20260917-CL-0043,QZ000516,鲜冬虫夏草,2.000000,2026-09-20 08:00:00,1,1,26000427,26000427（明细4135）,,待核对单位、月份及实际业务对应关系
3477,MPS20260918-CL-0044,QZ000518,鲜冬虫夏草,2.000000,2026-09-21 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3478,MPS20260918-CL-0045,QZ000519,鲜冬虫夏草,13.000000,2026-09-21 08:00:00,1,0,26000427,,,待核对单位、月份及实际业务对应关系
3479,MPS20260918-CL-0046,QZ000520,鲜冬虫夏草,357.000000,2026-09-22 08:00:00,1,0,26000427,,,待核对单位、月份及实际业务对应关系
3480,MPS20260921-CL-0047,QZ000525,鲜冬虫夏草,268.000000,2026-09-22 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3481,MPS20260921-CL-0048,QZ000522,鲜冬虫夏草,254.000000,2026-09-23 08:00:00,1,0,26000427,,,待核对单位、月份及实际业务对应关系
3482,MPS20260918-CL-0049,UZ000042,锁鲜冬虫夏草,2018.000000,2026-09-23 08:00:00,2,1,26000403；26000458,26000458（明细4146）,,待核对单位、月份及实际业务对应关系
3483,MPS20260923-CL-0050,QZ000529,锁鲜冬虫夏草,500.000000,2026-09-24 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3484,MPS20260922-CL-0051,QZ000528,锁鲜冬虫夏草,500.000000,2026-09-24 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3485,MPS20260922-CL-0052,QZ000531,锁鲜冬虫夏草,500.000000,2026-09-25 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3486,MPS20260924-CL-0053,QZ000532,锁鲜冬虫夏草,30000.000000,2026-09-25 08:00:00,2,0,26000452,,,待核对单位、月份及实际业务对应关系
3487,MPS20260924-CL-0054,74000489,冻干枸杞（金标）（内购专用）,100.000000,2026-09-26 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3488,MPS20260923-CL-0055,74000486,黎麦大礼包（内购专用）,100.000000,2026-09-26 08:00:00,0,0,,,,未找到同产品订单候选（按现有MDM映射）
3489,MPS20260925-CL-0056,74000485,西北精致礼盒（内购专用）,100.000000,2026-09-27 08:00:00,1,0,26000115,,,待核对单位、月份及实际业务对应关系

 scripts/mps-link-data-audit.py

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,21 @@
import sys,json,csv
from pathlib import Path
sys.path.insert(0,str(Path('scripts/iqc-import-20261012/deps').resolve()))
import pymysql
conn=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor,connect_timeout=15,read_timeout=60)
with conn.cursor() as q:
    q.execute('SELECT id,code,sale_order_id,sale_order_no,sale_order_item_id,product_id,quantity,client_id,request_date,status,remark FROM mes_pro_mps WHERE deleted=0 ORDER BY id')
    plans=q.fetchall()
    q.execute('SELECT i.id,i.order_id,i.product_id,i.count,i.mps_id,o.no,o.customer_id FROM erp_sale_order_items i JOIN erp_sale_order o ON o.id=i.order_id AND o.deleted=0 WHERE i.deleted=0')
    items=q.fetchall()
    q.execute('SELECT id,code,mdm_item_id,name FROM mes_md_item WHERE deleted=0')
    products=q.fetchall()
conn.close()
out=Path('scripts/mps-link-data-20261014');out.mkdir(exist_ok=True)
(out/'audit-snapshot.json').write_text(json.dumps({'plans':plans,'items':items,'products':products},ensure_ascii=False,indent=2,default=str),encoding='utf8')
by_mps={}
for item in items:
    if item['mps_id']:by_mps.setdefault(item['mps_id'],[]).append(item)
missing=[p for p in plans if not p['sale_order_id'] or not p['sale_order_no']]
print(json.dumps({'database':'remote mom-hm','total_plans':len(plans),'missing_link':len(missing),'missing_with_unique_backlink':sum(len(by_mps.get(p['id'],[]))==1 for p in missing),'missing_with_multiple_backlinks':sum(len(by_mps.get(p['id'],[]))>1 for p in missing),'missing_without_backlink':sum(not by_mps.get(p['id']) for p in missing)},ensure_ascii=False))
print(json.dumps(missing[:8],ensure_ascii=False,default=str))

 scripts/mps-link-data-match.py

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,73 @@
import sys,json,csv,collections
from decimal import Decimal
from pathlib import Path
sys.path.insert(0,str(Path('scripts/iqc-import-20261012/deps').resolve()))
import pymysql
apply='--apply' in sys.argv
conn=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor,connect_timeout=15,read_timeout=60,autocommit=False)
out=Path('scripts/mps-link-data-20261014');out.mkdir(exist_ok=True)
def save(name,data): (out/name).write_text(json.dumps(data,ensure_ascii=False,indent=2,default=str),encoding='utf8')
with conn.cursor() as q:
    q.execute('SELECT p.*,u.name AS plan_unit_name FROM mes_pro_mps p LEFT JOIN mes_md_unit_measure u ON u.id=p.unit_measure_id AND u.deleted=0 WHERE p.deleted=0 ORDER BY p.id')
    plans=q.fetchall()
    q.execute('SELECT i.*,o.no,o.customer_id,o.order_time,u.name AS sale_unit_name FROM erp_sale_order_items i JOIN erp_sale_order o ON o.id=i.order_id AND o.deleted=0 LEFT JOIN mdm_unit_measure u ON u.id=i.product_unit_id AND u.deleted=0 WHERE i.deleted=0')
    items=q.fetchall()
    q.execute('SELECT id,code,mdm_item_id,name FROM mes_md_item WHERE deleted=0')
    products={x['id']:x for x in q.fetchall()}
    q.execute('SELECT id,code FROM mdm_item WHERE deleted=0')
    mdm_by_code=collections.defaultdict(list)
    for x in q.fetchall():mdm_by_code[x['code']].append(x['id'])
save('strict-audit-before.json',{'plans':plans,'items':items,'products':products})
rows=[];matches=[]
for plan in plans:
    product=products.get(plan['product_id'],{})
    mdm_id=product.get('mdm_item_id')
    if not mdm_id and len(mdm_by_code[product.get('code')])==1:mdm_id=mdm_by_code[product['code']][0]
    choices=[x for x in items if mdm_id and x['product_id']==mdm_id]
    qty=[x for x in choices if Decimal(str(x['count']))==Decimal(str(plan['quantity']))]
    unit=[x for x in qty if plan['plan_unit_name'] and x['sale_unit_name'] and plan['plan_unit_name'].strip()==x['sale_unit_name'].strip()]
    month=[x for x in unit if plan['request_date'] and x['order_time'] and plan['request_date'].strftime('%Y-%m')==x['order_time'].strftime('%Y-%m')]
    available=[x for x in month if not x['mps_id'] or x['mps_id']==plan['id']]
    reason=('产品无对应销售明细' if not choices else '数量不一致' if not qty else '单位不一致或缺失' if not unit else '月份不一致或缺失' if not month else '销售明细已关联其他主计划' if not available else '多条符合条件的销售明细' if len(available)>1 else '唯一候选')
    if plan['sale_order_id'] or plan['sale_order_no'] or plan['sale_order_item_id']:reason='已有来源关联，保持原样'
    row={'主计划ID':plan['id'],'主计划编码':plan['code'],'产品编码':product.get('code',''),'产品名称':product.get('name',''),'计划数量':plan['quantity'],'计划单位':plan['plan_unit_name'],'需求日期':plan['request_date'],'同产品候选数':len(choices),'同数量候选数':len(qty),'同单位候选数':len(unit),'同月份候选数':len(month),'候选销售单号':'；'.join(sorted(set(x['no'] for x in available))),'结果':reason}
    rows.append(row)
    if reason=='唯一候选':matches.append((plan,available[0],row))
reverse=collections.Counter(x[1]['id'] for x in matches)
for plan,item,row in matches:
    if reverse[item['id']]>1:row['结果']='同一销售明细匹配多个主计划，暂不关联'
matches=[x for x in matches if reverse[x[1]['id']]==1]
save('confirmed-candidates.json',[{'mps_id':p['id'],'mps_code':p['code'],'order_id':i['order_id'],'order_no':i['no'],'item_id':i['id']} for p,i,r in matches])
if apply and matches:
    try:
        with conn.cursor() as q:
            backups=[]
            for plan,item,row in matches:
                q.execute('SELECT * FROM mes_pro_mps WHERE id=%s AND deleted=0 FOR UPDATE',(plan['id'],));old_plan=q.fetchone()
                q.execute('SELECT * FROM erp_sale_order_items WHERE id=%s AND deleted=0 FOR UPDATE',(item['id'],));old_item=q.fetchone()
                assert old_plan and old_item and not any(old_plan[x] for x in ['sale_order_id','sale_order_no','sale_order_item_id'])
                assert not old_item['mps_id'] or old_item['mps_id']==plan['id']
                backups.append({'plan':old_plan,'item':old_item})
            save('updated-records-backup.json',backups)
            for plan,item,row in matches:
                q.execute('UPDATE mes_pro_mps SET sale_order_id=%s,sale_order_no=%s,sale_order_item_id=%s,update_time=update_time WHERE id=%s AND deleted=0',(item['order_id'],item['no'],item['id'],plan['id']));assert q.rowcount==1
                if not item['mps_id']:
                    q.execute('UPDATE erp_sale_order_items SET mps_id=%s,update_time=update_time WHERE id=%s AND deleted=0 AND (mps_id IS NULL OR mps_id=0)',(plan['id'],item['id']));assert q.rowcount==1
                row['结果']='已关联'
            for backup in backups:
                q.execute('SELECT * FROM mes_pro_mps WHERE id=%s',(backup['plan']['id'],));now_plan=q.fetchone()
                q.execute('SELECT * FROM erp_sale_order_items WHERE id=%s',(backup['item']['id'],));now_item=q.fetchone()
                changed=[k for k,v in backup['plan'].items() if k not in ['sale_order_id','sale_order_no','sale_order_item_id'] and now_plan[k]!=v]
                assert not changed, 'Unexpected plan fields changed: '+','.join(changed)
                assert all(now_item[k]==v for k,v in backup['item'].items() if k!='mps_id')
                assert now_item['mps_id']==now_plan['id'] and now_plan['sale_order_item_id']==now_item['id'] and now_plan['sale_order_id']==now_item['order_id']
        conn.commit()
    except Exception:
        conn.rollback();raise
else:conn.rollback()
with (out/'生产主计划销售订单严格匹配结果.csv').open('w',encoding='utf-8-sig',newline='') as f:
    w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows)
summary={'total_plans':len(plans),'unique_strict_matches':len(matches),'applied':len(matches) if apply else 0,'results':dict(collections.Counter(x['结果'] for x in rows))}
save('strict-match-summary.json',summary)
print(json.dumps(summary,ensure_ascii=False))
conn.close()