zkxw/docs/sql/migrations/2026-10-21-sf-express-monthly-settlement.sql

-- 顺丰月结：运费可空，增加单号与结算字段；历史顺丰补未结算，不改原金额。
ALTER TABLE biz_sample_freight
  MODIFY COLUMN freight_amount DECIMAL(18,2) NULL DEFAULT NULL COMMENT '运费（元），顺丰月结未填时为空',
  ADD COLUMN waybill_no VARCHAR(64) NULL DEFAULT NULL COMMENT '顺丰单号' AFTER freight_amount,
  ADD COLUMN settle_status VARCHAR(8) NULL DEFAULT NULL COMMENT '结算状态：wjs 未结算，yjs 已结算' AFTER waybill_no,
  ADD COLUMN settle_time DATETIME NULL DEFAULT NULL COMMENT '结算时间' AFTER settle_status;

UPDATE biz_sample_freight
SET settle_status = 'wjs'
WHERE del_flag = 0
  AND transport_mode = 'sfkd'
  AND (settle_status IS NULL OR settle_status = '');

-- 菜单插在缴费财务确认之前。id 尚未占用。
UPDATE sys_menu
SET order_num = IFNULL(order_num, 0) + 1,
    update_time = NOW()
WHERE id = '50000000000000000000000000000004'
  AND del_flag = 0
  AND NOT EXISTS (
    SELECT 1 FROM (
      SELECT id FROM sys_menu
      WHERE id = '50000000000000000000000000000023' AND del_flag = 0
    ) existing_menu
  );

INSERT INTO sys_menu (
  id, menu_name, parent_id, menu_type, path, component, perms,
  is_frame, visible, status, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  '50000000000000000000000000000023',
  '顺丰月结',
  'CEL00000000000000000000000000001',
  'C',
  'sf-express-settlement',
  '/cell-storage/sf-express-settlement/index',
  'cell-storage:sf-express-settlement:list',
  1,
  '1',
  '1',
  'zkxw',
  (
    SELECT order_num - 1 FROM (
      SELECT order_num FROM sys_menu
      WHERE id = '50000000000000000000000000000004' AND del_flag = 0
      LIMIT 1
    ) pay_menu
  ),
  '细胞存储：顺丰月结',
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM sys_menu m
  WHERE m.del_flag = 0
    AND (
      m.id = '50000000000000000000000000000023'
      OR m.perms = 'cell-storage:sf-express-settlement:list'
    )
);

INSERT INTO sys_role_menu (
  id, role_id, menu_id, tenant_no, order_num, remark,
  create_by, create_dept_id, create_time, update_by, update_time, del_flag
)
SELECT
  MD5(CONCAT(r.id, '|50000000000000000000000000000023')),
  r.id,
  '50000000000000000000000000000023',
  IFNULL(NULLIF(r.tenant_no, ''), 'zkxw'),
  0,
  'grant sf-express settlement to payment-confirm roles',
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_role r
INNER JOIN sys_role_menu rm
  ON rm.role_id = r.id
 AND rm.menu_id = '50000000000000000000000000000004'
 AND rm.del_flag = 0
WHERE r.del_flag = 0
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu x
    WHERE x.role_id = r.id
      AND x.menu_id = '50000000000000000000000000000023'
      AND x.del_flag = 0
  );