$ErrorActionPreference = 'Stop'

$server = '192.168.1.27'
$db = 'ERPTOOL_JY_20250826Back'
$user = 'sa'
$pwd = '126.com'
$connStr = "Server=$server;Database=$db;User ID=$user;Password=$pwd;TrustServerCertificate=True;"

$conn = New-Object System.Data.SqlClient.SqlConnection $connStr
$conn.Open()

try {
  $cmd = $conn.CreateCommand()
  $cmd.CommandText = "SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.PDM_组件名称_确认更改新'))"
  $sql = [string]$cmd.ExecuteScalar()

  if ([string]::IsNullOrEmpty($sql)) {
    throw '未找到存储过程 PDM_组件名称_确认更改新'
  }

  if ($sql.Contains('修改人:Ld 修改时间:2026-07-23 允许合同修改时删除未领料机床')) {
    Write-Output '测试库过程已包含本次删除机床加固逻辑，无需重复修改'
    exit 0
  }

  $anchor = 'delete from PDM_机床名称_修改记录_临时表 where 项目流水号 = @项目流水号'
  $alter = [regex]::Replace($sql, '^CREATE\s+PROCEDURE', 'ALTER PROCEDURE')
  $pos = $alter.IndexOf($anchor, [System.StringComparison]::Ordinal)
  if ($pos -lt 0) {
    throw '未找到预期插入位置，未执行修改'
  }

  $new = @'
     -- 修改人:Ld 修改时间:2026-07-23 允许合同修改时删除未领料机床，已领料机床保留，避免误删已领料数据
     IF EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 WHERE 项目流水号 = @项目流水号)
     BEGIN
         DELETE r FROM 车间装配管理_装配任务 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE r FROM 车间装配管理_装配执行_临时领料明细 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE r FROM 车间装配管理_领料单明细 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE r FROM 车间装配管理_领料单 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE r FROM PDM_零件明细表_基本件 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE r FROM PDM_零件明细表_标准件和外购件 r
         INNER JOIN PDM_组件名称 c ON c.组件流水号 = r.组件流水号
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE c FROM PDM_组件名称 c
         INNER JOIN PDM_机床名称 m ON m.机床流水号 = c.机床流水号
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c2 WHERE c2.机床流水号 = m.机床流水号 AND ISNULL(c2.是否领料, 0) <> 0)

         DELETE m FROM PDM_机床名称 m
         WHERE m.项目流水号 = @项目流水号
           AND NOT EXISTS (SELECT 1 FROM PDM_机床名称_修改记录_临时表 t WHERE t.项目流水号 = @项目流水号 AND t.机床流水号 = m.机床流水号)
           AND NOT EXISTS (SELECT 1 FROM PDM_组件名称 c WHERE c.机床流水号 = m.机床流水号 AND ISNULL(c.是否领料, 0) <> 0)
     END

'@

  $alter = $alter.Insert($pos, $new)
  $cmd = $conn.CreateCommand()
  $cmd.CommandTimeout = 120
  $cmd.CommandText = $alter
  [void]$cmd.ExecuteNonQuery()
  Write-Output '测试库 PDM_组件名称_确认更改新 修改完成'
}
finally {
  $conn.Close()
}