Files
GX.O3.WMS/WebApi/docs/migrate_delivery.ps1

49 lines
1.8 KiB
PowerShell
Executable File

Add-Type -AssemblyName System.Data
$conn = New-Object System.Data.SqlClient.SqlConnection('server=10.10.10.101;database=GXWMS;uid=sa;pwd=Gx@20240111')
$conn.Open()
$cmd = $conn.CreateCommand()
# 1) 加 ProductTypes 字段
$cmd.CommandText = @"
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('Material_Delivery') AND name='ProductTypes')
BEGIN
ALTER TABLE Material_Delivery ADD ProductTypes NVARCHAR(500) NOT NULL DEFAULT N''
END
"@
$cmd.ExecuteNonQuery() | Out-Null
# 2) 合并历史重复:同物料多条记录 -> 保留最小Id,合并ProductTypes,迁移到货计划后删除多余记录
$cmd.CommandText = @"
;WITH D AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ChildMaterialId ORDER BY Id) AS RN
FROM Material_Delivery
)
UPDATE D SET ProductTypes = STUFF((
SELECT DISTINCT N',' + CAST(S.ProductCode AS NVARCHAR(100))
FROM Material_Delivery S WHERE S.ChildMaterialId = D.ChildMaterialId
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,N'')
WHERE RN = 1
"@
$cmd.ExecuteNonQuery() | Out-Null
# 迁移到货计划到保留记录
$cmd.CommandText = @"
UPDATE A SET A.DeliveryId = K.KeepId
FROM Arrival_Plan A
INNER JOIN (
SELECT AP.Id, (SELECT MIN(Id) FROM Material_Delivery M WHERE M.ChildMaterialId=D.ChildMaterialId) AS KeepId
FROM Arrival_Plan AP INNER JOIN Material_Delivery D ON AP.DeliveryId=D.Id
WHERE D.Id NOT IN (SELECT MIN(Id) FROM Material_Delivery GROUP BY ChildMaterialId)
) K ON A.Id = K.Id
"@
$cmd.ExecuteNonQuery() | Out-Null
# 删除重复交付记录(保留每组最小Id)
$cmd.CommandText = @"
DELETE FROM Material_Delivery
WHERE Id NOT IN (SELECT MIN(Id) FROM Material_Delivery GROUP BY ChildMaterialId)
"@
$rows = $cmd.ExecuteNonQuery()
$conn.Close()
Write-Output "OK, merged rows deleted: $rows"