103 lines
6.0 KiB
PowerShell
103 lines
6.0 KiB
PowerShell
$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()
|
|
}
|