mescloud/db/mysql/scale_spot_welding_current.sql

-- 历史点焊电流 * 10，与当前入库规则对齐
-- 只处理 weldingType=spot；电流绝对值已经 >= 3 的视为已放大，会跳过
-- 可重复执行。执行前建议先备份 mes_product_arc_welding

-- 1. 先看有多少点焊 / 弧焊（确认只动点焊）
SELECT
  SUM(content LIKE '%"weldingType":"spot"%') AS spot_cnt,
  SUM(content LIKE '%"weldingType":"arc"%') AS arc_cnt
FROM mes_product_arc_welding;

-- 2. 看几条点焊当前电流（放大前一般是 0.81，放大后是 8.1）
SELECT
  id,
  oprno,
  create_date,
  JSON_UNQUOTE(JSON_EXTRACT(content, '$.samples[0].robot1Current')) AS i1,
  JSON_UNQUOTE(JSON_EXTRACT(content, '$.samples[0].robot2Current')) AS i2
FROM mes_product_arc_welding
WHERE content LIKE '%"weldingType":"spot"%'
ORDER BY create_date DESC
LIMIT 10;

-- 3. 执行放大（Navicat 里把分隔符改成 $$ 再跑下面这段）
DROP PROCEDURE IF EXISTS scale_spot_welding_current;

DELIMITER $$
CREATE PROCEDURE scale_spot_welding_current()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE rid VARCHAR(64);
  DECLARE rdate DATETIME;
  DECLARE rcontent LONGTEXT;
  DECLARE i INT;
  DECLARE n INT;
  DECLARE c1 DECIMAL(20,6);
  DECLARE c2 DECIMAL(20,6);
  DECLARE maxc DECIMAL(20,6);
  DECLARE updated_cnt INT DEFAULT 0;
  DECLARE skipped_cnt INT DEFAULT 0;

  DECLARE cur CURSOR FOR
    SELECT id, create_date, content
    FROM mes_product_arc_welding
    WHERE content LIKE '%"weldingType":"spot"%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO rid, rdate, rcontent;
    IF done = 1 THEN
      LEAVE read_loop;
    END IF;

    IF rcontent IS NULL OR JSON_VALID(rcontent) = 0 THEN
      SET skipped_cnt = skipped_cnt + 1;
      ITERATE read_loop;
    END IF;
    IF JSON_UNQUOTE(JSON_EXTRACT(rcontent, '$.weldingType')) <> 'spot' THEN
      SET skipped_cnt = skipped_cnt + 1;
      ITERATE read_loop;
    END IF;

    SET n = IFNULL(JSON_LENGTH(rcontent, '$.samples'), 0);
    IF n <= 0 THEN
      SET skipped_cnt = skipped_cnt + 1;
      ITERATE read_loop;
    END IF;

    SET maxc = 0;
    SET i = 0;
    WHILE i < n DO
      SET c1 = CAST(JSON_UNQUOTE(JSON_EXTRACT(rcontent, CONCAT('$.samples[', i, '].robot1Current'))) AS DECIMAL(20,6));
      SET c2 = CAST(JSON_UNQUOTE(JSON_EXTRACT(rcontent, CONCAT('$.samples[', i, '].robot2Current'))) AS DECIMAL(20,6));
      IF c1 IS NOT NULL AND ABS(c1) > maxc THEN
        SET maxc = ABS(c1);
      END IF;
      IF c2 IS NOT NULL AND ABS(c2) > maxc THEN
        SET maxc = ABS(c2);
      END IF;
      SET i = i + 1;
    END WHILE;

    -- 没有电流，或已经放大过（>=3），跳过
    IF maxc <= 0 OR maxc >= 3 THEN
      SET skipped_cnt = skipped_cnt + 1;
      ITERATE read_loop;
    END IF;

    SET i = 0;
    WHILE i < n DO
      SET c1 = CAST(JSON_UNQUOTE(JSON_EXTRACT(rcontent, CONCAT('$.samples[', i, '].robot1Current'))) AS DECIMAL(20,6));
      SET c2 = CAST(JSON_UNQUOTE(JSON_EXTRACT(rcontent, CONCAT('$.samples[', i, '].robot2Current'))) AS DECIMAL(20,6));
      IF c1 IS NOT NULL THEN
        SET rcontent = JSON_SET(rcontent, CONCAT('$.samples[', i, '].robot1Current'), c1 * 10);
      END IF;
      IF c2 IS NOT NULL THEN
        SET rcontent = JSON_SET(rcontent, CONCAT('$.samples[', i, '].robot2Current'), c2 * 10);
      END IF;
      SET i = i + 1;
    END WHILE;

    UPDATE mes_product_arc_welding
    SET content = rcontent
    WHERE id = rid AND create_date = rdate;

    SET updated_cnt = updated_cnt + 1;
  END LOOP;
  CLOSE cur;

  SELECT updated_cnt AS updated_cnt, skipped_cnt AS skipped_cnt;
END$$
DELIMITER ;

CALL scale_spot_welding_current();
DROP PROCEDURE IF EXISTS scale_spot_welding_current;

-- 4. 执行后再抽查，点焊应为 8.1 左右，弧焊仍是原来的电流/电压
SELECT
  id,
  oprno,
  create_date,
  JSON_UNQUOTE(JSON_EXTRACT(content, '$.weldingType')) AS welding_type,
  JSON_UNQUOTE(JSON_EXTRACT(content, '$.samples[0].robot1Current')) AS i1,
  JSON_UNQUOTE(JSON_EXTRACT(content, '$.samples[0].robot2Current')) AS i2
FROM mes_product_arc_welding
WHERE content LIKE '%"weldingType":"spot"%'
ORDER BY create_date DESC
LIMIT 10;