exec_test_sql.ps1
2.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
$server = "192.168.1.91"
$database = "wms4_global"
$username = "sa"
$password = "HHrobot123."
Write-Host "============================================================"
Write-Host "Connecting to test DB: $server"
try {
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$server,1433;Database=$database;User Id=$username;Password=$password;"
$conn.Open()
Write-Host "Connection OK!" -ForegroundColor Green
$cmd = $conn.CreateCommand()
# 1. Check if column exists
$cmd.CommandText = "SELECT COUNT(*) FROM sys.columns WHERE Object_ID = Object_ID('receipt_container_detail') AND name = 'status'"
$count = $cmd.ExecuteScalar()
if ($count -eq 0) {
Write-Host "[1/3] Adding status column..."
$cmd.CommandText = "ALTER TABLE receipt_container_detail ADD status INT NULL"
$cmd.ExecuteNonQuery() | Out-Null
Write-Host "OK - status column added" -ForegroundColor Green
} else {
Write-Host "[1/3] status column already exists, skip" -ForegroundColor Yellow
}
# 2. Sync status data
Write-Host "[2/3] Syncing status data..."
$cmd.CommandText = "UPDATE rcd SET rcd.status = rch.status FROM receipt_container_detail rcd INNER JOIN receipt_container_header rch ON rcd.receipt_container_id = rch.id WHERE rcd.status IS NULL"
$rows = $cmd.ExecuteNonQuery()
Write-Host "OK - Synced $rows rows" -ForegroundColor Green
# 3. Query status distribution
Write-Host "[3/3] Status distribution:"
Write-Host "--------------------------------------------------------"
$cmd.CommandText = "SELECT ISNULL(status, -1) as status, COUNT(*) as cnt FROM receipt_container_detail GROUP BY status ORDER BY ISNULL(status, -1)"
$reader = $cmd.ExecuteReader()
while ($reader.Read()) {
$status = $reader[0]
$cnt = $reader[1]
if ($status -eq 0) { $name = "NEW" }
elseif ($status -eq 10) { $name = "GROUPED" }
elseif ($status -eq 20) { $name = "RECEIVED" }
elseif ($status -eq 50) { $name = "RETURNED" }
elseif ($status -eq -1) { $name = "NULL" }
else { $name = "UNKNOWN" }
Write-Host " status=$status ($name): $cnt rows"
}
$reader.Close()
Write-Host "`n============================================================" -ForegroundColor Green
Write-Host "All SQL executed!" -ForegroundColor Green
$conn.Close()
}
catch {
Write-Host "`nError: $_" -ForegroundColor Red
}