AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.420.verify.sql

-- ===========================================================================
-- 演示 / AI 文案清理 VERIFY —— 全部 SELECT 真值标量断言
-- 核心断言：UATTEST_CHL（客户演示唯一使用的租户）零残留
-- ===========================================================================

-- ---------- UATTEST_CHL 零残留 ----------
SELECT COUNT(*) = 0 FROM PersonSkill
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (Name LIKE '%[DEMO]%' OR Remark LIKE '%[DEMO]%' OR Remark LIKE '%演示%');

SELECT COUNT(*) = 0 FROM LocationShelfMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (descr LIKE '%DEMO%' OR create_user LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM S0MaterialPlanCycle
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (remarks LIKE '%演示%' OR create_user LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM S0OrderReviewCycle
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (remarks LIKE '%演示%' OR create_user LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM S0OrderScheduleCycle
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (remarks LIKE '%演示%' OR create_user LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM S0CategoryLeadTime
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (remarks LIKE '%演示%' OR create_user LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM S0ProductDesignCycle
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND create_user LIKE '%DEMO%';

SELECT COUNT(*) = 0 FROM NbrTypeMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND CreateUser LIKE '%DEMO%';

SELECT COUNT(*) = 0 FROM CostCtrMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND CreateUser LIKE '%DEMO%';

SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND CreateUser LIKE '%DEMO%';

SELECT COUNT(*) = 0 FROM EmployeeMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND (CreateUser LIKE '%DEMO%' OR UpdateUser LIKE '%DEMO%');

SELECT COUNT(*) = 0 FROM WorkCtrMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND UpdateUser LIKE '%DEMO%';

SELECT COUNT(*) = 0 FROM qms_quality_base_type
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND remark LIKE '%演示%';

SELECT COUNT(*) = 0 FROM qms_inspectioncrit
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND FCOMMENT LIKE '%演示%';

SELECT COUNT(*) = 0 FROM qms_inspectionfreq
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND FREMARK LIKE '%演示%';

SELECT COUNT(*) = 0 FROM qms_inspectionstd
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1)
    AND FCOMMENT LIKE '%演示%';

-- ---------- AIDOP 租户的 S0 文案同样清空 ----------
SELECT COUNT(*) = 0 FROM PersonSkill
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOP' AND t.Status=1 LIMIT 1)
    AND (Name LIKE '%[DEMO]%' OR Remark LIKE '%演示%');

SELECT COUNT(*) = 0 FROM wms_rwzp
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOP' AND t.Status=1 LIMIT 1)
    AND (bz LIKE '%[DEMO]%' OR rwlx LIKE '%[DEMO]%' OR zt LIKE '%[DEMO]%' OR rqpc LIKE 'DEMO-%');

SELECT COUNT(*) = 0 FROM DevMonitor
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOP' AND t.Status=1 LIMIT 1)
    AND (CodeDescr LIKE '%[DEMO]%' OR DeviceDescr LIKE '%[DEMO]%' OR DeviceNumber LIKE 'DEMO-%' OR MessageCode LIKE 'DEMO-%');

-- ---------- 质量域 [AI测试] 全清（不限租户）----------
SELECT COUNT(*) = 0 FROM qms_inspection_method WHERE FNAME LIKE '%[AI测试]%' OR FCTRLSTRATEGY LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_inspectionitems   WHERE FNAME LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_inspectioninstru  WHERE FNAME LIKE '%[AI测试]%' OR FMODEL LIKE '%[AI测试]%' OR FMANUFACTURER LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_inspectionfreq    WHERE FNAME LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_inspectioncrit    WHERE FNAME LIKE '%[AI测试]%' OR FCTRLSTRATEGY LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_inspeccrit_entry  WHERE FWORDNAME LIKE '%[AI测试]%' OR FJ LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_fqcjygf   WHERE fj LIKE '%[AI测试]%' OR fj2 LIKE '%[AI测试]%' OR wlbm LIKE '%演示%' OR syxh LIKE '%演示%';
SELECT COUNT(*) = 0 FROM qms_fqcjygfzb WHERE gxdh LIKE '%[AI测试]%' OR gxmc LIKE '%[AI测试]%' OR jyxm LIKE '%[AI测试]%' OR jyff LIKE '%[AI测试]%' OR jygg LIKE '%[AI测试]%' OR jypc LIKE '%[AI测试]%' OR jsbz LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_oqcjygf   WHERE fj LIKE '%[AI测试]%' OR fj2 LIKE '%[AI测试]%' OR wlbm LIKE '%演示%' OR syxh LIKE '%演示%';
SELECT COUNT(*) = 0 FROM qms_oqcjygfzb WHERE gxdh LIKE '%[AI测试]%' OR gxmc LIKE '%[AI测试]%' OR jyxm LIKE '%[AI测试]%' OR jyff LIKE '%[AI测试]%' OR jygg LIKE '%[AI测试]%' OR jypc LIKE '%[AI测试]%' OR jsbz LIKE '%[AI测试]%';
SELECT COUNT(*) = 0 FROM qms_gcjygf    WHERE fj LIKE '%[AI测试]%' OR fj2 LIKE '%[AI测试]%' OR wlbm LIKE '%演示%' OR syxh LIKE '%演示%';
SELECT COUNT(*) = 0 FROM qms_gcjygfzb  WHERE gxdh LIKE '%[AI测试]%' OR jypc LIKE '%[AI测试]%';

-- ---------- 只改文案，不增删数据 ----------
SELECT COUNT(*) = 5  FROM PersonSkill
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1);
SELECT COUNT(*) = 20 FROM LocationShelfMaster
  WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='UATTEST_CHL' AND t.Status=1 LIMIT 1);
SELECT COUNT(*) = 808 FROM ProductStructureOp;