專案

一般

配置概況

下載 (389 KB)

Feature #2643 » frm_DbSetting.xaml.cs

莊 施嶔, 03/06/2025 08:53

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using System.Windows.Media;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Windows.Threading;
using NLog;
using System.Reflection;
using System.Xml.Linq;
using System.Diagnostics;
using IOPath = System.IO.Path; // 添加別名以避免命名空間衝突
using IOFile = System.IO.File; // 添加別名以避免可能的命名空間衝突
using com.mirle.ibg3k0.sc.App;
using com.mirle.ibg3k0.sc.Common;

namespace BCWPF
{
/// <summary>
/// 用於顯示可還原資料庫資訊的類
/// </summary>
public class RestorableDatabaseInfo
{
/// <summary>
/// 資料庫名稱
/// </summary>
public string DatabaseName { get; set; }

/// <summary>
/// 資料庫版本
/// </summary>
public string Version { get; set; }

/// <summary>
/// 資料庫建立時間
/// </summary>
public DateTime CreationTime { get; set; }

/// <summary>
/// 顯示在清單中的文字
/// </summary>
public override string ToString()
{
return $"{DatabaseName} (版本: {Version}, 建立時間: {CreationTime:yyyy-MM-dd HH:mm:ss})";
}
}

public partial class frm_DbSetting : Window
{
/// <summary>
/// 檢查資料庫連線狀態並等待直到可以連線
/// </summary>
/// <param name="connectionString">資料庫連線字串</param>
/// <param name="databaseName">要檢查的資料庫名稱</param>
/// <param name="maxRetries">最大重試次數</param>
/// <param name="retryDelayMs">每次重試間隔（毫秒）</param>
/// <param name="timeoutMs">總超時時間（毫秒），0表示無限制</param>
/// <returns>成功返回true，失敗返回false</returns>
private async Task<bool> WaitForDatabaseConnection(
string connectionString,
string databaseName,
int maxRetries = 20,
int retryDelayMs = 1000,
int timeoutMs = 0)
{
var startTime = DateTime.Now;
int retryCount = 0;

while (retryCount < maxRetries)
{
try
{
using (var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();

// 如果指定了特定資料庫，檢查該資料庫是否可用
if (!string.IsNullOrEmpty(databaseName))
{
using (var command = new SqlCommand(
"SELECT COUNT(*) FROM sys.databases WHERE name = @dbName",
connection))
{
command.Parameters.AddWithValue("@dbName", databaseName);
int count = Convert.ToInt32(await command.ExecuteScalarAsync());
if (count == 0)
{
throw new Exception($"資料庫 {databaseName} 不存在");
}
}

// 嘗試切換到指定的資料庫
using (var command = new SqlCommand($"USE [{databaseName}]", connection))
{
await command.ExecuteNonQueryAsync();
}
}

LogMessage($"成功連線到資料庫 {(string.IsNullOrEmpty(databaseName) ? "(master)" : databaseName)}");
return true;
}
}
catch (Exception ex)
{
retryCount++;

// 檢查是否超時
if (timeoutMs > 0 && (DateTime.Now - startTime).TotalMilliseconds > timeoutMs)
{
LogMessage($"等待資料庫連線超時: {ex.Message}", true);
return false;
}

if (retryCount < maxRetries)
{
LogMessage($"等待資料庫連線中... (重試 {retryCount}/{maxRetries}): {ex.Message}", true);
await Task.Delay(retryDelayMs);
}
else
{
LogMessage($"資料庫連線重試次數已達上限: {ex.Message}", true);
return false;
}
}
}

return false;
}

private string _connectionString = string.Empty; // 預設值移至SetDefaultValues方法中
private string _masterConnectionString = string.Empty; // 預設值移至SetDefaultValues方法中
private string _configPath;
private static readonly Logger _logger = LogManager.GetLogger("DbSettings");

// 新增一個標記來追蹤是否有操作正在執行
private bool _isOperationInProgress = false;

// 用於追蹤資料庫遷移過程中是否因錯誤而失敗
// 當遷移過程中發生錯誤時設為 true，用於後續判斷是否需要重試或回滾
private bool _migrationFailedDueToError = false;
// 追蹤當前執行的操作名稱，便於日誌記錄
private string _currentOperation = string.Empty;
// 新增一個標記，表示是否已從配置檔案載入成功
private bool _isLoadedFromConfig = false;
// 新增一個標記，表示界面是否有未保存的變更
private bool _hasUnsavedChanges = false;

/// <summary>
/// 檢查主視窗是否存在且可見
/// </summary>
private bool IsMainWindowActive()
{
return Application.Current.Windows.OfType<MainWindow>().Any(w => w.IsVisible);
}

private void PauseTraceDataReportTimer()
{
try
{
if (IsMainWindowActive())
{
// 只有在主視窗存在時才控制資料庫存取
SCUtility.AllowDBAccess = false;
LogMessage("已暫停資料庫存取和 Timer");
}
}
catch (Exception ex)
{
LogMessage($"暫停資料庫存取和 Timer 時發生錯誤: {ex.Message}", true);
}
}

private void ResumeTraceDataReportTimer()
{
try
{
if (IsMainWindowActive())
{
// 只有在主視窗存在時才控制資料庫存取
SCUtility.AllowDBAccess = true;
LogMessage("已恢復資料庫存取和 Timer");
}
}
catch (Exception ex)
{
LogMessage($"恢復資料庫存取和 Timer 時發生錯誤: {ex.Message}", true);
}
}

public frm_DbSetting()
{
InitializeComponent();

// 添加鍵盤焦點處理事件
this.KeyDown += Frm_DbSetting_KeyDown;
this.PreviewMouseDown += Frm_DbSetting_PreviewMouseDown;
this.MouseDown += Frm_DbSetting_MouseDown;

// 添加窗體點擊事件來處理焦點
this.MouseDown += Window_MouseDown;



// 使用低優先級加載初始設定，避免阻塞UI
Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 設定配置檔案路徑
string baseDir = AppDomain.CurrentDomain.BaseDirectory;
string configDir = IOPath.Combine(baseDir, "Config");

// 如果是 Debug 模式，使用專案目錄下的 Config
if (System.Diagnostics.Debugger.IsAttached)
{
// 取得專案根目錄（向上三層到專案根目錄）
string projectRoot = IOPath.GetFullPath(IOPath.Combine(baseDir, @"..\..\..\"));
configDir = IOPath.Combine(projectRoot, "BCWPF", "Config");
LogMessage($"Debug 模式：使用專案目錄 {configDir}");
}

_configPath = IOPath.Combine(configDir, "SQLServer.cfg.xml");
LogMessage($"設定檔路徑：{_configPath}");

// 初始化日誌區域
ClearLog();
LogMessage("系統設定視窗初始化...");

// 設定預設連線字串 - 這只是備用
SetDefaultValues();

// 載入資料庫設定
LoadDatabaseSettings();

LogMessage("系統設定視窗初始化完成");
}
catch (Exception ex)
{
_logger.Error($"視窗初始化錯誤: {ex.Message}");
MessageBox.Show($"視窗初始化發生錯誤: {ex.Message}", "錯誤", MessageBoxButton.OK, MessageBoxImage.Error);
}
}), DispatcherPriority.Background);

// 立即註冊事件，避免等待初始化完成
RegisterEvents();
}

// 點擊窗體任何區域時嘗試將焦點移到窗體上，以便讓文本框失去焦點
private void Frm_DbSetting_MouseDown(object sender, MouseButtonEventArgs e)
{
Keyboard.ClearFocus(); // 清除鍵盤焦點
this.Focus(); // 使窗體獲得焦點
}

// 預覽鼠標點擊事件
private void Frm_DbSetting_PreviewMouseDown(object sender, MouseButtonEventArgs e)
{
// 如果點擊的是窗體背景（非控件區域）
if (e.OriginalSource is Border || e.OriginalSource is Grid || e.OriginalSource is Window)
{
Keyboard.ClearFocus(); // 清除當前控件的焦點
FocusManager.SetFocusedElement(this, this); // 將焦點設置到窗體
}
}

// 處理按Esc鍵清除焦點
private void Frm_DbSetting_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Escape)
{
// 清除焦點
Keyboard.ClearFocus();
FocusManager.SetFocusedElement(this, this);
e.Handled = true;
}
}

// 分離事件註冊邏輯，提高UI回應速度
private void RegisterEvents()
{
try
{
// 設定事件處理
txtDbPort.PreviewTextInput += TxtDbPort_PreviewTextInput;
txtDbPort.LostFocus += TxtDbPort_LostFocus;

// 為IP地址文字框添加事件處理
txtDbIP.LostFocus += TxtDbIP_LostFocus;

// 新增文本變更事件處理以追蹤未保存的變更
txtDbIP.TextChanged += MarkAsChanged;
txtDbPort.TextChanged += MarkAsChanged;
txtDbName.TextChanged += MarkAsChanged;
txtDbID.TextChanged += MarkAsChanged;
txtDbPassword.TextChanged += MarkAsChanged;
txtMinPoolSize.TextChanged += MarkAsChanged;
txtMaxPoolSize.TextChanged += MarkAsChanged;

// 預先設置文本框的Tag屬性，避免第一次失焦驗證時找不到Tag
txtDbIP.Tag = "localhost";
txtDbPort.Tag = "1433";
txtMinPoolSize.Tag = "50";
txtMaxPoolSize.Tag = "1000";

// 移除窗體點擊事件，它過於嚴格導致文本框無法獲得焦點
// this.PreviewMouseDown += Window_PreviewMouseDown;
}
catch (Exception ex)
{
_logger.Error($"註冊事件時發生錯誤: {ex.Message}");
}
}



// 恢復簡單的GotFocus處理方法，但不添加特殊處理邏輯
private void TextBox_GotFocus(object sender, RoutedEventArgs e)
{
// 只做一件事：保存當前值到Tag
if (sender is TextBox textBox && !textBox.IsReadOnly)
{
textBox.Tag = textBox.Text;
}
}

private void TxtDbIP_GotFocus(object sender, RoutedEventArgs e)
{
// 標記為已獲取焦點，避免重複處理
txtDbIP.Tag = txtDbIP.Text;
}

// IP文字框失去焦點時的驗證處理
private void TxtDbIP_LostFocus(object sender, RoutedEventArgs e)
{
// 使用Dispatcher.BeginInvoke確保UI響應
Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 保存原始值
string originalValue = txtDbIP.Tag as string ?? "localhost";
string ipAddress = txtDbIP.Text.Trim();

// 允許使用 localhost
if (ipAddress.Equals("localhost", StringComparison.OrdinalIgnoreCase))
{
// 更新保存的有效值
txtDbIP.Tag = ipAddress;
return;
}

// 驗證IPv4格式
if (!IsValidIPv4(ipAddress))
{
MessageBox.Show("請輸入有效的IPv4地址（如: 192.168.1.1）或 localhost\n\nPlease enter a valid IPv4 address (e.g. 192.168.1.1) or localhost", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
// 恢復為最後一個有效值
txtDbIP.Text = originalValue;
txtDbIP.Focus();
txtDbIP.SelectAll();
}
else
{
// 更新保存的有效值
txtDbIP.Tag = ipAddress;
}
}
catch (Exception ex)
{
// 確保即使驗證出現問題也不會阻塞UI
_logger.Error($"IP驗證時發生錯誤: {ex.Message}");
}
}), DispatcherPriority.Background);
}

// 驗證IPv4地址格式
private bool IsValidIPv4(string ipAddress)
{
// IP地址格式檢查
if (string.IsNullOrWhiteSpace(ipAddress))
return false;

// 嘗試解析IP地址
if (System.Net.IPAddress.TryParse(ipAddress, out System.Net.IPAddress address))
{
// 確保是IPv4地址
if (address.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
{
string[] parts = ipAddress.Split('.');
// 確保有四個部分且每個部分都是0-255的數字
if (parts.Length == 4)
{
foreach (string part in parts)
{
if (!int.TryParse(part, out int value) || value < 0 || value > 255)
return false;
}
return true;
}
}
}

return false;
}

private void TxtDbPort_PreviewTextInput(object sender, TextCompositionEventArgs e)
{
// 只允許輸入數字
if (!char.IsDigit(e.Text, 0))
{
e.Handled = true;
return;
}

// 檢查輸入後的值是否超過範圍
string newText = txtDbPort.Text.Insert(txtDbPort.CaretIndex, e.Text);
if (int.TryParse(newText, out int port))
{
if (port > 65535)
{
e.Handled = true;
MessageBox.Show("Port 值不可大於 65535\n\nPort value cannot be greater than 65535", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
}

private void TxtDbPort_LostFocus(object sender, RoutedEventArgs e)
{
// 使用Dispatcher.BeginInvoke確保UI響應
Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 檢查 Port 值是否在有效範圍內
if (string.IsNullOrEmpty(txtDbPort.Text))
{
txtDbPort.Text = "1433";
return;
}

if (!int.TryParse(txtDbPort.Text, out int port))
{
txtDbPort.Text = "1433";
MessageBox.Show("Port 必須為數字\n\nPort must be a number", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}

if (port < 100)
{
txtDbPort.Text = "1433";
MessageBox.Show("Port 值不可小於 100\n\nPort value cannot be less than 100", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else if (port > 32765)
{
txtDbPort.Text = "1433";
MessageBox.Show("Port 值不可大於 32765\n\nPort value cannot be greater than 32765", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
catch (Exception ex)
{
// 確保即使驗證出現問題也不會阻塞UI
_logger.Error($"Port驗證時發生錯誤: {ex.Message}");
}
}), DispatcherPriority.Background);
}

private void TxtMinPoolSize_PreviewTextInput(object sender, TextCompositionEventArgs e)
{
// 只允許輸入數字
if (!char.IsDigit(e.Text, 0))
{
e.Handled = true;
return;
}

// 檢查輸入後的值是否超過範圍
string newText = txtMinPoolSize.Text.Insert(txtMinPoolSize.CaretIndex, e.Text);
if (int.TryParse(newText, out int value))
{
if (value > 32767)
{
e.Handled = true;
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
}

private void TxtMinPoolSize_LostFocus(object sender, RoutedEventArgs e)
{
// 使用Dispatcher.BeginInvoke確保UI響應
Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 檢查 MinPoolSize 值是否在有效範圍內
if (string.IsNullOrEmpty(txtMinPoolSize.Text))
{
txtMinPoolSize.Text = "50";
return;
}

if (!int.TryParse(txtMinPoolSize.Text, out int value))
{
txtMinPoolSize.Text = "50";
MessageBox.Show("Connection Pool大小必須為數字\n\nConnection Pool size must be a number", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}

if (value < 1)
{
txtMinPoolSize.Text = "50";
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else if (value > 32767)
{
txtMinPoolSize.Text = "50";
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
catch (Exception ex)
{
// 確保即使驗證出現問題也不會阻塞UI
_logger.Error($"MinPoolSize驗證時發生錯誤: {ex.Message}");
}
}), DispatcherPriority.Background);
}

private void TxtMaxPoolSize_PreviewTextInput(object sender, TextCompositionEventArgs e)
{
// 只允許輸入數字
if (!char.IsDigit(e.Text, 0))
{
e.Handled = true;
return;
}

// 檢查輸入後的值是否超過範圍
string newText = txtMaxPoolSize.Text.Insert(txtMaxPoolSize.CaretIndex, e.Text);
if (int.TryParse(newText, out int value))
{
if (value > 32767)
{
e.Handled = true;
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
}

private void TxtMaxPoolSize_LostFocus(object sender, RoutedEventArgs e)
{
// 使用Dispatcher.BeginInvoke確保UI響應
Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 檢查 MaxPoolSize 值是否在有效範圍內
if (string.IsNullOrEmpty(txtMaxPoolSize.Text))
{
txtMaxPoolSize.Text = "1000";
return;
}

if (!int.TryParse(txtMaxPoolSize.Text, out int value))
{
txtMaxPoolSize.Text = "1000";
MessageBox.Show("Connection Pool大小必須為數字\n\nConnection Pool size must be a number", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}

if (value < 1)
{
txtMaxPoolSize.Text = "1000";
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else if (value > 32767)
{
txtMaxPoolSize.Text = "1000";
MessageBox.Show("Connection Pool大小必須在 1-32767 之間\n\nConnection Pool size must be between 1-32767", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
catch (Exception ex)
{
// 確保即使驗證出現問題也不會阻塞UI
_logger.Error($"MaxPoolSize驗證時發生錯誤: {ex.Message}");
}
}), DispatcherPriority.Background);
}

private void LoadDatabaseSettings()
{
try
{
LogMessage($"開始讀取資料庫設定檔：{_configPath}");

if (!IOFile.Exists(_configPath))
{
LogMessage("設定檔不存在，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

LogMessage("讀取設定檔...");
XDocument doc = XDocument.Load(_configPath);
LogMessage("設定檔讀取成功");

// 取得命名空間
XNamespace ns = doc.Root.GetDefaultNamespace();
LogMessage($"XML 命名空間：{ns}");

// 尋找 session-factory 元素
var sessionFactory = doc.Root.Element(ns + "session-factory");
if (sessionFactory == null)
{
LogMessage("設定檔中找不到 session-factory 元素，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 尋找 connection.connection_string 屬性
var propertyElement = sessionFactory.Elements(ns + "property")
.FirstOrDefault(p => p.Attribute("name")?.Value == "connection.connection_string");

if (propertyElement == null)
{
LogMessage("設定檔中找不到 connection.connection_string 屬性，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

string connString = propertyElement.Value.Trim();
if (string.IsNullOrEmpty(connString))
{
LogMessage("連線字串為空，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

LogMessage("解析連線字串...");

try
{
var builder = new SqlConnectionStringBuilder(connString);

// 解析 DataSource 以取得 IP 和 PORT
string dataSource = builder.DataSource;
if (string.IsNullOrEmpty(dataSource))
{
LogMessage("資料來源為空，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

string[] dataSourceParts = dataSource.Split(',');

string ip = dataSourceParts[0].Trim();
string port = "1433"; // 預設值

// 如果有指定 PORT，則使用指定的值
if (dataSourceParts.Length > 1)
{
port = dataSourceParts[1].Trim();
}

// 檢查IP是否有效
if (!ip.Equals("localhost", StringComparison.OrdinalIgnoreCase) && !IsValidIPv4(ip))
{
LogMessage($"設定檔中的IP格式無效：{ip}，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 檢查連接埠是否有效
if (!int.TryParse(port, out int portNum) || portNum < 1 || portNum > 65535)
{
LogMessage($"設定檔中的連接埠無效：{port}，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 檢查資料庫名稱是否有效
if (string.IsNullOrEmpty(builder.InitialCatalog))
{
LogMessage("設定檔中的資料庫名稱為空，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 檢查使用者ID是否有效
if (string.IsNullOrEmpty(builder.UserID))
{
LogMessage("設定檔中的使用者ID為空，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 檢查密碼是否有效
if (string.IsNullOrEmpty(builder.Password))
{
LogMessage("設定檔中的密碼為空，將使用預設值", true);
_isLoadedFromConfig = false;
return;
}

// 檢查連線池設定
if (builder.MinPoolSize < 1 || builder.MinPoolSize > 32767)
{
LogMessage($"設定檔中的最小連線池大小無效：{builder.MinPoolSize}，將使用預設值", true);
builder.MinPoolSize = 50;
}

if (builder.MaxPoolSize < builder.MinPoolSize || builder.MaxPoolSize > 32767)
{
LogMessage($"設定檔中的最大連線池大小無效：{builder.MaxPoolSize}，將使用預設值", true);
builder.MaxPoolSize = 1000;
}

// 更新 UI 控制項
txtDbIP.Text = ip;
txtDbIP.Tag = ip; // 保存有效的IP到Tag屬性
txtDbPort.Text = port;
txtDbName.Text = builder.InitialCatalog;
txtDbID.Text = builder.UserID;
txtDbPassword.Text = builder.Password;
txtMinPoolSize.Text = builder.MinPoolSize.ToString();
txtMaxPoolSize.Text = builder.MaxPoolSize.ToString();

LogMessage("連線字串解析完成：");
LogMessage($"- 資料庫伺服器：{txtDbIP.Text}:{txtDbPort.Text}");
LogMessage($"- 資料庫名稱：{txtDbName.Text}");
LogMessage($"- 使用者帳號：{txtDbID.Text}");
LogMessage($"- Connection Pool設定：Min={txtMinPoolSize.Text}, Max={txtMaxPoolSize.Text}");

// 更新記憶體中的連線字串
_connectionString = builder.ConnectionString;

// 更新 master 連線字串
var masterBuilder = new SqlConnectionStringBuilder(builder.ConnectionString);
masterBuilder.InitialCatalog = "master";
_masterConnectionString = masterBuilder.ConnectionString;

LogMessage("記憶體中的連線字串已更新");
_isLoadedFromConfig = true;

LogMessage("資料庫設定載入完成");
_hasUnsavedChanges = false; // 重置未保存變更標記
}
catch (Exception ex)
{
LogMessage($"解析連線字串時發生錯誤：{ex.Message}", true);
LogMessage($"錯誤詳細資訊：{ex.StackTrace}", true);
LogMessage("將使用預設值");
_isLoadedFromConfig = false;
}
}
catch (Exception ex)
{
LogMessage($"載入資料庫設定時發生錯誤：{ex.Message}", true);
LogMessage($"錯誤詳細資訊：{ex.StackTrace}", true);
LogMessage("將使用預設值");
_isLoadedFromConfig = false;
MessageBox.Show($"載入資料庫設定時發生錯誤。\n請參閱操作記錄了解詳細錯誤資訊。\n\nAn error occurred while loading the database settings.\nPlease refer to the operation log for detailed error information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

private void SetDefaultValues()
{
// 設定預設UI值
txtDbIP.Text = "localhost";
txtDbIP.Tag = "localhost"; // 保存有效的IP到Tag屬性
txtDbPort.Text = "1433";
txtDbName.Text = "BC_Dev";
txtDbID.Text = "sa";
txtDbPassword.Text = "p@ssw0rd";
txtMinPoolSize.Text = "50";
txtMaxPoolSize.Text = "1000";

// 設定預設連線字串
var builder = new SqlConnectionStringBuilder
{
DataSource = "localhost,1433",
InitialCatalog = "BC_Dev",
UserID = "sa",
Password = "p@ssw0rd",
MinPoolSize = 50,
MaxPoolSize = 1000
};

_connectionString = builder.ConnectionString;

// 設定預設 master 連線字串
var masterBuilder = new SqlConnectionStringBuilder(builder.ConnectionString);
masterBuilder.InitialCatalog = "master";
_masterConnectionString = masterBuilder.ConnectionString;

LogMessage("已設定預設連線字串值");
}

private void UpdateConnectionStrings()
{
try
{
// 如果不在 UI 執行緒上，則使用 Dispatcher 來執行
if (!Dispatcher.CheckAccess())
{
Dispatcher.Invoke(() => UpdateConnectionStrings());
return;
}

// 確保IP和PORT都是有效值
string ip = txtDbIP.Text.Trim();
string port = txtDbPort.Text.Trim();

if (string.IsNullOrEmpty(ip))
{
// 使用上一個有效值或預設值
ip = txtDbIP.Tag as string ?? "localhost";
txtDbIP.Text = ip;
LogMessage("警告：IP地址為空，恢復到上一個有效值", true);
}
else if (!ip.Equals("localhost", StringComparison.OrdinalIgnoreCase) && !IsValidIPv4(ip))
{
// 如果IP格式無效，恢復到上一個有效值
ip = txtDbIP.Tag as string ?? "localhost";
LogMessage("警告：IP地址格式無效，恢復到上一個有效值", true);
txtDbIP.Text = ip;
}
else
{
// 更新有效值標記
txtDbIP.Tag = ip;
}

if (string.IsNullOrEmpty(port))
{
port = "1433";
txtDbPort.Text = port;
LogMessage("警告：設定了預設的Port 1433");
}

// 確保最小池大小有效
if (!int.TryParse(txtMinPoolSize.Text, out int minPoolSize) || minPoolSize < 1 || minPoolSize > 32767)
{
txtMinPoolSize.Text = "50";
minPoolSize = 50;
LogMessage("警告：最小池大小無效，設定為預設值 50", true);
}

// 確保最大池大小有效
if (!int.TryParse(txtMaxPoolSize.Text, out int maxPoolSize) || maxPoolSize < minPoolSize || maxPoolSize > 32767)
{
txtMaxPoolSize.Text = "1000";
maxPoolSize = 1000;
LogMessage("警告：最大池大小無效，設定為預設值 1000", true);
}

// 建立資料來源字串
string dataSource = $"{ip},{port}";

// 建立完整的連線字串
var builder = new SqlConnectionStringBuilder
{
DataSource = dataSource,
InitialCatalog = txtDbName.Text.Trim(),
UserID = txtDbID.Text.Trim(),
Password = txtDbPassword.Text,
MinPoolSize = minPoolSize,
MaxPoolSize = maxPoolSize,
ApplicationName = "OHCS_BC_Controller_Admin" // 為資料庫設定頁添加特定的應用程式名稱
};

_connectionString = builder.ConnectionString;

// 更新 master 連線字串
var masterBuilder = new SqlConnectionStringBuilder(builder.ConnectionString);
masterBuilder.InitialCatalog = "master";
_masterConnectionString = masterBuilder.ConnectionString;

LogMessage($"已更新連線字串，資料來源: {dataSource}");
}
catch (Exception ex)
{
LogMessage($"更新連線字串時發生錯誤：{ex.Message}", true);
LogMessage($"錯誤詳細資訊：{ex.StackTrace}", true);

// 發生異常時，如果之前有從配置檔案載入成功，則使用之前的連線字串
// 否則使用預設值
if (!_isLoadedFromConfig)
{
SetDefaultValues();
}
}
}

private bool ValidateDatabaseSettings()
{
try
{
// 檢查必要欄位
if (string.IsNullOrWhiteSpace(txtDbIP.Text))
{
LogMessage("錯誤：資料庫伺服器 IP 不能為空", true);
MessageBox.Show("資料庫伺服器 IP 不能為空\n\nDatabase server IP cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

// 驗證IP地址格式
string ip = txtDbIP.Text.Trim();
if (!ip.Equals("localhost", StringComparison.OrdinalIgnoreCase) && !IsValidIPv4(ip))
{
LogMessage("錯誤：資料庫伺服器 IP 格式無效", true);
// 恢復到上一個有效值
string validIP = txtDbIP.Tag as string ?? "localhost";
txtDbIP.Text = validIP;
ip = validIP;
MessageBox.Show("請輸入有效的IPv4地址（如: 192.168.1.1）或 localhost\n\nPlease enter a valid IPv4 address (e.g. 192.168.1.1) or localhost", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
txtDbIP.Focus();
txtDbIP.SelectAll();
return false;
}

if (string.IsNullOrWhiteSpace(txtDbPort.Text))
{
LogMessage("錯誤：資料庫伺服器 Port 不能為空", true);
MessageBox.Show("資料庫伺服器 Port 不能為空\n\nDatabase server Port cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

if (string.IsNullOrWhiteSpace(txtDbID.Text))
{
LogMessage("錯誤：資料庫使用者 ID 不能為空", true);
MessageBox.Show("資料庫使用者 ID 不能為空\n\nDatabase user ID cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

if (string.IsNullOrWhiteSpace(txtDbPassword.Text))
{
LogMessage("錯誤：資料庫密碼不能為空", true);
MessageBox.Show("資料庫密碼不能為空\n\nDatabase password cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

// 檢查 Min Pool 和 Max Pool
if (!int.TryParse(txtMinPoolSize.Text, out int minPool) ||
!int.TryParse(txtMaxPoolSize.Text, out int maxPool))
{
LogMessage("錯誤：連線池大小必須為數字", true);
MessageBox.Show("連線池大小必須為數字\n\nConnection Pool size must be a number", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

if (minPool > maxPool)
{
LogMessage("錯誤：最小連線池大小不能大於最大連線池大小", true);
MessageBox.Show("最小連線池大小不能大於最大連線池大小\n\nMinimum Connection Pool size cannot be greater than Maximum Connection Pool size", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

return true;
}
catch (Exception ex)
{
LogMessage($"驗證資料庫設定時發生錯誤：{ex.Message}", true);
MessageBox.Show("驗證資料庫設定時發生錯誤。\n請參閱操作記錄了解詳細錯誤資訊。\n\nAn error occurred while validating the database settings.\nPlease refer to the operation log for detailed error information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}
}

private void LogMessage(string message, bool isError = false)
{
string timestamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string logMessage = $"[{timestamp}] {(isError ? "ERROR" : "INFO")}: {message}";

// 使用 NLog 輸出到檔案
if (isError)
{
_logger.Error(message);
}
else
{
_logger.Info(message);
}

// 使用 Dispatcher 確保在 UI 執行緒上更新
// 優先使用 Invoke 強制立即更新，而不是 BeginInvoke (異步更新)
if (Dispatcher.CheckAccess())
{
try
{
// 如果已經在UI線程上，直接更新
// 添加新日誌
txtLog.AppendText(logMessage + Environment.NewLine);

// 確保最新的日誌訊息可見
txtLog.CaretIndex = txtLog.Text.Length;
txtLog.ScrollToEnd();

// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(logMessage);
// 防止日誌顯示錯誤導致整個程序崩潰
_logger.Error(ex, "添加日誌到 UI 時發生錯誤");
}
}
else
{
try
{
// 異步時使用 Invoke 而非 BeginInvoke，確保UI更新不會延遲
Dispatcher.Invoke(new Action(() =>
{
try
{
// 添加新日誌
txtLog.AppendText(logMessage + Environment.NewLine);

// 確保最新的日誌訊息可見
txtLog.CaretIndex = txtLog.Text.Length;
txtLog.ScrollToEnd();

// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(logMessage);
// 防止日誌顯示錯誤導致整個程序崩潰
_logger.Error(ex, "添加日誌到 UI 時發生錯誤");
}
}), DispatcherPriority.Send);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(logMessage);
// 如果 Invoke 失敗，嘗試使用 BeginInvoke
_logger.Error(ex, "使用 Invoke 更新日誌失敗，嘗試 BeginInvoke");

Dispatcher.BeginInvoke(new Action(() =>
{
try
{
// 添加新日誌
txtLog.AppendText(logMessage + Environment.NewLine);

// 確保最新的日誌訊息可見
txtLog.CaretIndex = txtLog.Text.Length;
txtLog.ScrollToEnd();

// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception innerEx)
{
System.Diagnostics.Debug.WriteLine(logMessage);
// 防止日誌顯示錯誤導致整個程序崩潰
_logger.Error(innerEx, "添加日誌到 UI 時發生錯誤");
}
}), DispatcherPriority.Send);
}
}

}

private void ClearLog()
{
if (Dispatcher.CheckAccess())
{
try
{
txtLog.Clear();
// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception ex)
{
// 防止清除日誌時發生錯誤導致整個程序崩潰
_logger.Error(ex, "清除日誌時發生錯誤");
}
}
else
{
try
{
// 使用 Invoke 而非 BeginInvoke 確保立即更新 UI
Dispatcher.Invoke(new Action(() =>
{
try
{
txtLog.Clear();
// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception ex)
{
// 防止清除日誌時發生錯誤導致整個程序崩潰
_logger.Error(ex, "清除日誌時發生錯誤");
}
}), DispatcherPriority.Send);
}
catch (Exception ex)
{
// 如果 Invoke 失敗，嘗試使用 BeginInvoke
_logger.Error(ex, "使用 Invoke 清除日誌失敗，嘗試 BeginInvoke");

Dispatcher.BeginInvoke(new Action(() =>
{
try
{
txtLog.Clear();
// 強制更新 UI
txtLog.UpdateLayout();
}
catch (Exception innerEx)
{
// 防止清除日誌時發生錯誤導致整個程序崩潰
_logger.Error(innerEx, "清除日誌時發生錯誤");
}
}), DispatcherPriority.Send);
}
}
}

/// <summary>
/// 處理UI事件，防止UI凍結
/// </summary>
private void DoEvents()
{
try
{
// 使用 DispatcherFrame 創建一個新的消息循環
DispatcherFrame frame = new DispatcherFrame();

// 建立一個在消息循環中被執行的動作
Dispatcher.CurrentDispatcher.BeginInvoke(DispatcherPriority.Background,
new DispatcherOperationCallback(delegate (object parameter)
{
((DispatcherFrame)parameter).Continue = false;
return null;
}), frame);

// 啟動新的消息循環，處理所有挂起的UI事件
Dispatcher.PushFrame(frame);
}
catch (Exception ex)
{
_logger.Error($"DoEvents失敗: {ex.Message}");
}
}

private void btn_OK_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

Close();
}

private void btn_Cancel_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}
Close();
}

/// <summary>
/// 設置所有操作按鈕的可用性
/// </summary>
/// <param name="isEnabled">是否啟用按鈕</param>
/// <param name="operationName">當前正在執行的操作名稱（當禁用按鈕時）</param>
private void SetOperationButtonsState(bool isEnabled, string operationName = "")
{
Action updateAction = () =>
{
try
{
// 更新操作狀態標記和名稱
_isOperationInProgress = !isEnabled;
_currentOperation = isEnabled ? string.Empty : operationName;

// 更新所有操作按鈕的啟用狀態
btn_CreateDatabase.IsEnabled = isEnabled;
btn_UpdateDatabase.IsEnabled = isEnabled;
btn_BackupDatabase.IsEnabled = isEnabled;
btn_RestoreDatabase.IsEnabled = isEnabled;
btn_ClearBackup.IsEnabled = isEnabled;
btn_SaveSettings.IsEnabled = isEnabled;
btn_Close.IsEnabled = isEnabled;
btn_OpenConfigFile.IsEnabled = isEnabled;
btn_CheckFieldConsistency.IsEnabled = isEnabled; // 確保檢查按鈕也被禁用

// 如果禁用按鈕，顯示操作名稱
if (!isEnabled && !string.IsNullOrEmpty(operationName))
{
LogMessage($"正在執行{operationName}操作...");
}
}
catch (Exception ex)
{
_logger.Error($"設置按鈕狀態時發生錯誤: {ex.Message}");
}
};

// 使用Dispatcher確保在UI線程上執行
if (Dispatcher.CheckAccess())
{
// 當前線程是UI線程，直接執行
updateAction();
}
else
{
try
{
// 使用 Invoke 而非 BeginInvoke 確保立即更新 UI
Dispatcher.Invoke(updateAction, DispatcherPriority.Send);
}
catch (Exception ex)
{
// 如果 Invoke 失敗，嘗試使用 BeginInvoke
_logger.Error($"使用 Invoke 設置按鈕狀態失敗: {ex.Message}");
Dispatcher.BeginInvoke(updateAction, DispatcherPriority.Send);
}
}
}

/// <summary>
/// 開啟資料庫設定檔案
/// </summary>
private void btn_OpenConfigFile_Click(object sender, RoutedEventArgs e)
{
try
{
// 檢查檔案是否存在
if (!IOFile.Exists(_configPath))
{
MessageBox.Show($"找不到資料庫設定檔案：\n{_configPath}\n\nDatabase configuration file not found:\n{_configPath}", "檔案不存在 / File Not Found", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}

LogMessage($"嘗試開啟資料庫設定檔案：{_configPath}");

// 使用系統默認程式開啟檔案
Process.Start(new ProcessStartInfo
{
FileName = _configPath,
UseShellExecute = true
});

LogMessage("已開啟資料庫設定檔案");
}
catch (Exception ex)
{
LogMessage($"開啟資料庫設定檔案時發生錯誤：{ex.Message}", true);

// 嘗試使用記事本開啟
try
{
LogMessage("嘗試使用記事本開啟檔案...");
Process.Start(new ProcessStartInfo
{
FileName = "notepad.exe",
Arguments = _configPath,
UseShellExecute = true
});
LogMessage("已使用記事本開啟檔案");
}
catch (Exception notepadEx)
{
LogMessage($"使用記事本開啟檔案失敗：{notepadEx.Message}", true);
MessageBox.Show($"無法開啟資料庫設定檔案。\n\n路徑：{_configPath}\n\n錯誤：{ex.Message}\n\nUnable to open the database configuration file.\n\nPath: {_configPath}\n\nError: {ex.Message}",
"開啟檔案失敗 / Failed to Open File", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}

private async void btn_CreateDatabase_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

// 禁用所有按鈕
SetOperationButtonsState(false, "建立資料庫");

try
{
// 暫停 TraceDataReport Timer
PauseTraceDataReportTimer();

if (!ValidateDatabaseSettings())
{
return;
}

UpdateConnectionStrings();
ClearLog();
LogMessage("開始建立資料庫程序");

// 先確認是否要執行建立資料庫
LogMessage("詢問用戶是否確定要建立資料庫");
var confirmResult = MessageBox.Show("確定要建立資料庫嗎？\n\nAre you sure you want to create the database?", "確認操作 / Confirm Operation",
MessageBoxButton.YesNo, MessageBoxImage.Question);

if (confirmResult != MessageBoxResult.Yes)
{
LogMessage("使用者選擇取消建立資料庫操作");
return;
}

LogMessage("使用者確認要建立資料庫");

// 檢查ACJID和APJID表是否有資料(簡單判斷是否未清線狀況下作資料庫操作)
if (!CheckAccountTables())
{
LogMessage("使用者因資料表風險取消操作");
return;
}

// 檢查資料庫是否已存在
using (var connection = new SqlConnection(_masterConnectionString))
{
connection.Open();
using (var command = new SqlCommand("SELECT database_id FROM sys.databases WHERE Name = 'BC_Dev'", connection))
{
var dbExists = command.ExecuteScalar();
if (dbExists != null)
{
// 資料庫已存在，詢問是否要備份後重建
LogMessage("發現BC_Dev資料庫已存在，詢問用戶是否要備份後重建");
var backupResult = MessageBox.Show("資料庫已存在，是否要備份後重新建立？\n\nDatabase already exists. Do you want to backup and recreate it?", "確認操作 / Confirm Operation",
MessageBoxButton.YesNo, MessageBoxImage.Warning);

if (backupResult == MessageBoxResult.Yes)
{
LogMessage("使用者選擇備份後重建資料庫");
// 執行備份
var result = await Task.Run(() => BackupDatabase());
if (result.ErrorMessage != null)
{
// 備份失敗，詢問是否繼續
LogMessage($"備份失敗：{result.ErrorMessage}，詢問用戶是否直接刪除並重建", true);
var continueResult = MessageBox.Show($"備份失敗：{result.ErrorMessage}\n是否要直接刪除資料庫並重新建立？\n\nBackup failed: {result.ErrorMessage}\nDo you want to delete the database and recreate it directly?", "備份失敗 / Backup Failed",
MessageBoxButton.YesNo, MessageBoxImage.Warning);

if (continueResult != MessageBoxResult.Yes)
{
LogMessage("使用者取消重建資料庫操作");
return;
}

LogMessage("使用者選擇直接刪除並重建資料庫");
}
}
else
{
LogMessage("使用者選擇不備份，詢問是否直接刪除並重建");
// 詢問是否直接刪除資料庫
var deleteResult = MessageBox.Show("是否要直接刪除現有資料庫後重新建立？\n\nDo you want to delete the existing database and recreate it?", "確認操作 / Confirm Operation",
MessageBoxButton.YesNo, MessageBoxImage.Warning);

if (deleteResult != MessageBoxResult.Yes)
{
LogMessage("使用者取消刪除並重建資料庫操作");
return;
}

LogMessage("使用者確認直接刪除並重建資料庫");
}
}
}
}

// 顯示等待消息
var progressIndicator = new Progress<string>(message =>
{
LogMessage(message);
// 在長時間運行的操作中使用 DoEvents 讓 UI 有機會更新
DoEvents();
});

// 使用 CancellationTokenSource 來支持任務取消
var cts = new CancellationTokenSource();

try
{
// 新建資料庫時需要寫入基本值，並支持進度回報和取消
await Task.Run(async () => await CreateDatabaseWithProgress(true, progressIndicator, cts.Token), cts.Token);
}
catch (OperationCanceledException)
{
LogMessage("建立資料庫操作已取消");
return;
}

// 執行資料欄位一致性檢查
LogMessage("開始進行資料欄位一致性檢查...");
bool hasInconsistentFields = false;
try
{
hasInconsistentFields = await Task.Run(() => CheckFieldConsistency(false));

if (hasInconsistentFields)
{
MessageBox.Show("資料庫建立成功，但發現欄位定義不一致的情況，詳情請查看日誌報告。\n\nDatabase created successfully, but field definition inconsistencies were found. Please check the log report for details.",
"建立完成但有警告",
MessageBoxButton.OK,
MessageBoxImage.Warning,
MessageBoxResult.OK,
MessageBoxOptions.DefaultDesktopOnly);
}
else
{
MessageBox.Show("資料庫建立成功！\n\nDatabase created successfully!",
"成功 / Success",
MessageBoxButton.OK,
MessageBoxImage.Information,
MessageBoxResult.OK,
MessageBoxOptions.DefaultDesktopOnly);
}
}
catch (Exception ex)
{
LogMessage($"檢查欄位定義失敗: {ex.Message}", true);
MessageBox.Show("資料庫建立成功！\n\nDatabase created successfully!",
"成功 / Success",
MessageBoxButton.OK,
MessageBoxImage.Information,
MessageBoxResult.OK,
MessageBoxOptions.DefaultDesktopOnly);
}

// 通知使用者程式即將關閉
MessageBox.Show("資料庫建立完成，程式即將關閉。\n請手動重新啟動程式才能生效。\n\nDatabase creation completed. The program will close.\nPlease restart the program manually for the changes to take effect.",
"系統提示 / System Message",
MessageBoxButton.OK,
MessageBoxImage.Information,
MessageBoxResult.OK,
MessageBoxOptions.DefaultDesktopOnly);

ShutdownApplication();
}
catch (Exception ex)
{
LogMessage($"建立資料庫失敗: {ex.Message}", true);
MessageBox.Show($"建立資料庫失敗。\n請參閱操作記錄了解詳細錯誤資訊。\n\nFailed to create database.\nPlease refer to the operation log for detailed error information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
// 無論執行結果如何，確保按鈕狀態都會被恢復
SetOperationButtonsState(true);
// 恢復 TraceDataReport Timer
ResumeTraceDataReportTimer();
}
}

/// <summary>
/// 建立資料庫基本方法
/// </summary>
private async Task CreateDatabase(bool insertInitialData = true)
{
LogMessage("開始建立資料庫");

// 連接到master資料庫
using (SqlConnection conn = new SqlConnection(_masterConnectionString))
{
conn.Open();
LogMessage("已連接到master資料庫");

// 檢查資料庫是否存在於 SQL Server
bool dbExistsInSQLServer = false;
using (var command = new SqlCommand("SELECT database_id FROM sys.databases WHERE Name = 'BC_Dev'", conn))
{
var dbExists = command.ExecuteScalar();
dbExistsInSQLServer = (dbExists != null);
}

// 如果資料庫存在於 SQL Server，則進行正規的刪除
if (dbExistsInSQLServer)
{
LogMessage("資料庫 BC_Dev 存在於 SQL Server，進行正規刪除");
try
{
using (var command = new SqlCommand(@"
ALTER DATABASE BC_Dev SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BC_Dev;", conn))
{
command.ExecuteNonQuery();
}
LogMessage("資料庫 BC_Dev 已成功刪除");
}
catch (Exception ex)
{
LogMessage($"刪除資料庫失敗: {ex.Message}", true);
throw new Exception($"無法刪除現有的資料庫。請檢查資料庫是否被使用中，或是否有足夠的權限。", ex);
}
}

// 檢查資料庫檔案是否存在，存在則刪除 (處理實體檔案存在但資料庫不存在於 SQL Server 的情況)
string defaultDataPath = string.Empty;
string defaultLogPath = string.Empty;

// 取得SQL Server的默認資料目錄
using (var command = new SqlCommand(@"
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath", conn))
{
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
defaultDataPath = reader["DefaultDataPath"] as string ?? string.Empty;
defaultLogPath = reader["DefaultLogPath"] as string ?? string.Empty;
}
}
}

// 若取得失敗，嘗試從系統目錄獲取
if (string.IsNullOrEmpty(defaultDataPath) || string.IsNullOrEmpty(defaultLogPath))
{
using (var command = new SqlCommand(@"
SELECT
physical_name,
type_desc
FROM sys.master_files
WHERE database_id = 1 AND type IN (0, 1)", conn))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string filePath = reader["physical_name"].ToString();
string typeDesc = reader["type_desc"].ToString();

if (typeDesc == "ROWS" && string.IsNullOrEmpty(defaultDataPath))
{
defaultDataPath = IOPath.GetDirectoryName(filePath);
}
else if (typeDesc == "LOG" && string.IsNullOrEmpty(defaultLogPath))
{
defaultLogPath = IOPath.GetDirectoryName(filePath);
}
}
}
}
}

// 如果路徑仍然為空，則使用默認的SQL Server安裝路徑
if (string.IsNullOrEmpty(defaultDataPath))
{
// 嘗試使用SQL Server Express的默認路徑
defaultDataPath = @"C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\";
LogMessage("無法取得SQL Server資料目錄，使用默認路徑: " + defaultDataPath);
}

if (string.IsNullOrEmpty(defaultLogPath))
{
defaultLogPath = defaultDataPath; // 如果日誌路徑未定義，使用相同的資料路徑
}

// 構造MDF和LDF檔案路徑
string mdfPath = IOPath.Combine(defaultDataPath, "BC_Dev.mdf");
string ldfPath = IOPath.Combine(defaultLogPath, "BC_Dev_log.ldf");

// 檢查 MDF 檔案和 LDF 檔案是否存在
bool mdfExists = IOFile.Exists(mdfPath);
bool ldfExists = IOFile.Exists(ldfPath);

if (mdfExists || ldfExists)
{
if (!dbExistsInSQLServer)
{
LogMessage("發現殘留的資料庫實體檔案，但資料庫不存在於 SQL Server，準備清理殘留檔案");
}
else
{
LogMessage("檢查資料庫檔案");
}
}

// 刪除 MDF 檔案（如果存在）
if (mdfExists)
{
try
{
LogMessage($"發現資料庫MDF檔案，準備刪除: {mdfPath}");
IOFile.Delete(mdfPath);
LogMessage("MDF檔案刪除成功");
}
catch (Exception ex)
{
LogMessage($"無法刪除MDF檔案: {ex.Message}", true);
throw new Exception($"無法刪除現有的資料庫檔案({mdfPath})。請檢查檔案是否被使用中，或是否有足夠的權限。", ex);
}
}

// 刪除 LDF 檔案（如果存在）
if (ldfExists)
{
try
{
LogMessage($"發現資料庫LDF檔案，準備刪除: {ldfPath}");
IOFile.Delete(ldfPath);
LogMessage("LDF檔案刪除成功");
}
catch (Exception ex)
{
LogMessage($"無法刪除LDF檔案: {ex.Message}", true);
throw new Exception($"無法刪除現有的資料庫日誌檔案({ldfPath})。請檢查檔案是否被使用中，或是否有足夠的權限。", ex);
}
}

// 建立資料庫 (在交易外執行)
LogMessage("正在建立BC_Dev資料庫...");
try
{
using (var command = new SqlCommand("CREATE DATABASE BC_Dev COLLATE Chinese_Taiwan_Stroke_CI_AS", conn))
{
command.ExecuteNonQuery();
}
LogMessage("BC_Dev資料庫建立完成");

// 檢查資料庫連線狀態
if (!await WaitForDatabaseConnection(_connectionString, "BC_Dev", 30, 1000, 10000))
{
LogMessage("無法連接到新建立的資料庫，建立資料庫失敗", true);
throw new Exception("無法連接到新建立的資料庫，請檢查SQL Server狀態或權限設定");
}
}
catch (Exception ex)
{
LogMessage($"建立資料庫失敗: {ex.Message}", true);
throw;
}

// 切換到BC_Dev資料庫
conn.ChangeDatabase("BC_Dev");
LogMessage("已切換到BC_Dev資料庫");

// 開始交易來建立表格和插入資料
using (var transaction = conn.BeginTransaction())
{
try
{
// 建立表格
CreateTables(conn, transaction);

// 只有在需要時才插入基本資料
if (insertInitialData)
{
InsertInitialData(conn, transaction);
}

// 提交交易
transaction.Commit();
LogMessage("資料庫建立完成");

// 計算建立的表格和視圖數量
int tableCount = 0;
int viewCount = 0;

using (var command = new SqlCommand(@"
SELECT
(SELECT COUNT(*) FROM sys.tables WHERE type = 'U') as TableCount,
(SELECT COUNT(*) FROM sys.views) as ViewCount", conn))
{
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
tableCount = reader.GetInt32(0);
viewCount = reader.GetInt32(1);
}
}
}

LogMessage($"資料庫建立完成，共建立 {tableCount} 張Table和 {viewCount} 個View");
}
catch (Exception ex)
{
try
{
LogMessage($"發生錯誤，開始回滾交易: {ex.Message}", true);
transaction.Rollback();
LogMessage("交易回滾完成");
throw;
}
catch (Exception rollbackEx)
{
LogMessage($"回滾過程中發生錯誤: {rollbackEx.Message}", true);
throw new Exception($"建立資料庫時發生錯誤，且無法完全回滾，請參閱操作日誌", rollbackEx);
}
}
}
}
}

/// <summary>
/// 帶有進度回報功能的資料庫建立方法
/// </summary>
/// <param name="insertInitialData">是否插入初始資料</param>
/// <param name="progress">進度回報介面</param>
/// <param name="progress">進度回報介面</param>
/// <param name="cancellationToken">取消令牌</param>
private async Task CreateDatabaseWithProgress(bool insertInitialData = true, IProgress<string> progress = null, CancellationToken cancellationToken = default)
{
// 在長時間操作中檢查取消狀態和報告進度
cancellationToken.ThrowIfCancellationRequested();

// 直接調用原有方法
await CreateDatabase(insertInitialData);
}

//資料庫結構變動時，一定要來更新這裡的SQL語法
private void CreateTables(SqlConnection conn, SqlTransaction transaction)
{
//此段註解請勿移除
//SQL語法可以先在SSMS等Client工具匯出語句，對於比較不好閱讀的可以貼給AI去轉成一般SQL
//不要直接貼SSMS含GO的SQL
//SQL有變動，目前千萬要記得：
//1.HBM檔同步調整
//2.App.xaml.cs中的最小相容版本號(_minimumRequiredVersion)一定要更新為此次的版本

LogMessage("開始建立表格...");
string currentSql = string.Empty;

try
{
#region ABUFFER Table
// 建立ABUFFER表格
LogMessage("正在建立ABUFFER表格...");
currentSql = $@"
CREATE TABLE [dbo].[ABUFFER](
[BUFF_ID] [char](15) NOT NULL,
[UNIT_NUM] [int] NOT NULL,
[EQPT_ID] [char](15) NOT NULL,
[CAPACITY] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[BUFF_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ABUFFER表格建立完成");
#endregion

#region ACASSETTE Table
// 建立ACASSETTE表格
LogMessage("正在建立ACASSETTE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ACASSETTE](
[CST_ID] [char](20) NOT NULL,
[CST_STAT] [char](1) NOT NULL,
[SHT_CNT] [int] NOT NULL,
[PORT_ID] [char](20) NOT NULL,
[PORT_NUM] [int] NOT NULL,
[LOT_ID] [char](22) NULL,
[SLOT_MAP] [char](36) NULL,
[SLOT_SEL] [char](36) NULL,
[END_STAT] [char](1) NULL,
[RPTED_EAP_CSTID] [char](1) NOT NULL,
[EAP_CHECK_SLOTMAP] [char](1) NOT NULL,
[EAP_USER_RELOAD] [char](1) NOT NULL,
[CSTLOGON_TIME] [datetime] NULL,
[CSTLOGOff_TIME] [datetime] NULL,
[ABNORMAL_CODE] [char](3) NULL,
[CSTID_READ_TYPE] [char](1) NULL,
[CDA_PURGET_STAT] [char](2) NULL,
[ISLOTSTART] [char](1) NOT NULL,
[CSTPROCSTART_TIME] [datetime] NULL,
[CSTPROCEND_TIME] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[CST_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ACASSETTE表格建立完成");
#endregion

#region ACELL Table
// 建立ACELL表格
LogMessage("正在建立ACELL表格...");
currentSql = $@"
CREATE TABLE [dbo].[ACELL](
[SHT_ID] [char](26) NOT NULL,
[CELL_ID] [char](20) NULL,
[CELL_JUDGE] [char](20) NULL,
PRIMARY KEY CLUSTERED
(
[SHT_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ACELL表格建立完成");
#endregion

#region ACJID Table
// 建立ACJID表格
LogMessage("正在建立ACJID表格...");
currentSql = $@"
CREATE TABLE [dbo].[ACJID](
[CJID] [char](100) NOT NULL,
[CJ_STATUS] [int] NOT NULL,
[CJ_AUTOSTART] [char](1) NULL,
[CJCREATE_TIME] [datetime] NULL,
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[PROCESS_END_CNT] [int] NOT NULL,
[SCRAP_CNT] [int] NOT NULL,
[TOTAL_COUNT] [int] NOT NULL,
[END_RESULT] [char](2) NOT NULL,
[PJID_01] [char](50) NULL,
[PJID_02] [char](50) NULL,
[PJID_03] [char](50) NULL,
[PJID_04] [char](50) NULL,
[PJID_05] [char](50) NULL,
[PJID_06] [char](50) NULL,
[PJID_07] [char](50) NULL,
[PJID_08] [char](50) NULL,
[PJID_09] [char](50) NULL,
[PJID_10] [char](50) NULL,
[PJID_11] [char](50) NULL,
[PJID_12] [char](50) NULL,
[LDPROCTIME] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[CJID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ACJID表格建立完成");
#endregion

#region ACJPANELIDLST Table
// 建立ACJPANELIDLST表格、Index
LogMessage("正在建立ACJPANELIDLST表格...");
currentSql = $@"
CREATE TABLE [dbo].[ACJPANELIDLST](
[CJID] [char](100) NOT NULL,
[PANEL_ID] [char](26) NOT NULL,
[WORK_NO] [char](5) NOT NULL,
PRIMARY KEY CLUSTERED
(
[CJID] ASC,
[PANEL_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ACJPANELIDLST表格建立完成");
#endregion

#region ACRATE Table
// 建立ACRATE表格、Index
LogMessage("正在建立ACRATE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ACRATE](
[CRATE_ID] [char](20) NOT NULL,
[MAKER] [char](20) NULL,
PRIMARY KEY CLUSTERED
(
[CRATE_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ACRATE表格建立完成");
#endregion

#region AECDATAMAP Table
// 建立AECDATAMAP表格' Index
LogMessage("正在建立AECDATAMAP表格...");
currentSql = $@"
CREATE TABLE [dbo].[AECDATAMAP](
[ECID] [char](4) NOT NULL,
[EQPT_REAL_ID] [char](15) NULL,
[ECNAME] [char](40) NULL,
[ECMIN] [char](20) NULL,
[ECMAX] [char](20) NULL,
[ECV] [char](20) NULL,
PRIMARY KEY CLUSTERED
(
[ECID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AECDATAMAP表格建立完成");
#endregion

#region AEQPT Table
// 建立AEQPT表格、Index
LogMessage("正在建立AEQPT表格...");
currentSql = $@"
CREATE TABLE [dbo].[AEQPT](
[EQPT_ID] [char](15) NOT NULL,
[NODE_ID] [char](15) NOT NULL,
[CIM_MODE] [char](1) NOT NULL,
[OPER_MODE] [char](1) NOT NULL,
[INLINE_MODE] [char](1) NOT NULL,
[EQPT_STAT] [char](1) NOT NULL,
[EQPT_PROC_STAT] [char](1) NOT NULL,
[CR_RECIPE] [char](33) NULL,
[MAX_SHT_CNT] [int] NULL,
[MIN_SHT_CNT] [int] NULL,
[ALARM_STAT] [char](1) NULL,
[WARN_STAT] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
[EQPT_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AEQPT表格建立完成");
#endregion

#region AEVENTRPTCOND Table
// 建立AEVENTRPTCOND表格、Index
LogMessage("正在建立AEVENTRPTCOND表格...");
currentSql = $@"
CREATE TABLE [dbo].[AEVENTRPTCOND](
[CEID] [char](3) NOT NULL,
[ENABLE_FLG] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[CEID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AEVENTRPTCOND表格建立完成");
#endregion

#region AFLOW_REL Table
// 建立AFLOW_REL表格、Index
LogMessage("正在建立AFLOW_REL表格...");
currentSql = $@"
CREATE TABLE [dbo].[AFLOW_REL](
[UPSTREAM_ID] [char](10) NOT NULL,
[DOWNSTREAM_ID] [char](10) NOT NULL,
[FR_ID] [char](10) NOT NULL,
[REL_TYPE] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UPSTREAM_ID] ASC,
[DOWNSTREAM_ID] ASC
),
UNIQUE NONCLUSTERED
(
[FR_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AFLOW_REL表格建立完成");
#endregion



#region AHLOT Table
// 建立AHLOT表格、Index
LogMessage("正在建立AHLOT表格...");
currentSql = $@"
CREATE TABLE [dbo].[AHLOT](
[SEQ_NO] [nvarchar](255) NOT NULL,
[LOT_ID] [char](22) NULL,
[T_STAMP] [char](19) NULL,
[CST_OPER_MODE] [char](1) NOT NULL,
[DUMMY_TYPE] [char](1) NOT NULL,
[LOT_JUDGE] [char](1) NULL,
[WORK_ORDER] [char](30) NULL,
[PROC_STAT] [char](1) NULL,
[LOT_START_PROC_TIME] [char](16) NULL,
[LOT_END_PROC_TIME] [char](16) NULL,
[PROC_SHT_CNT] [int] NULL,
[ESFLAG] [char](1) NULL,
[LOTSTATUS] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AHLOT表格建立完成");
#endregion

#region ALARM Table
// 建立ALARM表格
LogMessage("正在建立ALARM表格...");
currentSql = $@"
CREATE TABLE [dbo].[ALARM](
[EQPT_ID] [char](15) NOT NULL,
[UNIT_NUM] [int] NOT NULL,
[RPT_DATE_TIME] [char](19) NOT NULL,
[ALAM_CODE] [char](10) NOT NULL,
[ALAM_AFFECT_COUNT] [char](3) NOT NULL,
[ALAM_LVL] [char](1) NOT NULL,
[ALAM_STAT] [char](1) NOT NULL,
[ALAM_DESC] [char](80) NULL,
[CLEAR_DATE_TIME] [char](19) NULL,
PRIMARY KEY CLUSTERED
(
[EQPT_ID] ASC,
[UNIT_NUM] ASC,
[RPT_DATE_TIME] ASC,
[ALAM_CODE] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ALARM表格建立完成");
#endregion

#region ALARMAFFECTGI Table
// 建立ALARMAFFECTGI表格
LogMessage("正在建立ALARMAFFECTGI表格...");
currentSql = $@"
CREATE TABLE [dbo].[ALARMAFFECTGI](
[EQPT_ID] [char](15) NOT NULL,
[RPT_DATE_TIME] [char](19) NOT NULL,
[GLASS_ID] [char](26) NOT NULL,
PRIMARY KEY CLUSTERED
(
[EQPT_ID] ASC,
[RPT_DATE_TIME] ASC,
[GLASS_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ALARMAFFECTGI表格建立完成");
#endregion

#region ALARMRPTCOND Table
// 建立ALARMRPTCOND表格
LogMessage("正在建立ALARMRPTCOND表格...");
currentSql = $@"
CREATE TABLE [dbo].[ALARMRPTCOND](
[EQPT_ID] [char](15) NOT NULL,
[ALAM_CODE] [char](10) NOT NULL,
[ENABLE_FLG] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[EQPT_ID] ASC,
[ALAM_CODE] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ALARMRPTCOND表格建立完成");
#endregion

#region ALINE Table
// 建立ALINE表格
LogMessage("正在建立ALINE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ALINE](
[LINE_ID] [char](25) NOT NULL,
[HOST_MODE] [char](1) NOT NULL,
[LINE_STAT] [char](1) NOT NULL,
[RUN_MODE] [char](2) NOT NULL,
[CUR_PPID] [char](40) NULL,
[CUR_CJID] [char](100) NULL,
[CUR_PJID] [char](20) NULL,
PRIMARY KEY CLUSTERED
(
[LINE_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ALINE表格建立完成");
#endregion

#region ALOT Table
// 建立ALOT表格
LogMessage("正在建立ALOT表格...");
currentSql = $@"
CREATE TABLE [dbo].[ALOT](
[LOT_ID] [char](22) NOT NULL,
[CST_OPER_MODE] [char](1) NOT NULL,
[DUMMY_TYPE] [char](1) NOT NULL,
[LOT_JUDGE] [char](1) NULL,
[WORK_ORDER] [char](30) NULL,
[PROC_STAT] [char](1) NULL,
[LOT_START_PROC_TIME] [char](16) NULL,
[LOT_END_PROC_TIME] [char](16) NULL,
[PROC_SHT_CNT] [int] NULL,
[ESFLAG] [char](1) NULL,
[LOTSTATUS] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[LOT_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ALOT表格建立完成");
#endregion

#region ANODE Table
// 建立ANODE表格
LogMessage("正在建立ANODE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ANODE](
[NODE_ID] [char](15) NOT NULL,
[ZONE_ID] [char](30) NOT NULL,
PRIMARY KEY CLUSTERED
(
[NODE_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ANODE表格建立完成");
#endregion

#region APJID Table
LogMessage("正在建立APJID表格...");
currentSql = $@"
CREATE TABLE [dbo].[APJID](
[PJID] [char](50) NOT NULL,
[CSTID] [char](20) NOT NULL,
[PJ_STATUS] [int] NOT NULL,
[PJ_PRESTATUS] [int] NULL,
[AUTOSTART] [char](1) NULL,
[CJID] [char](100) NULL,
[RECIPEID] [char](4) NOT NULL,
[SEQ_NO] [int] NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[PJIDSTART_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[SLOT_SELECT] [char](36) NULL,
[WORK_QTY] [char](3) NULL,
[SLOT_SELECT_NUM] [char](98) NULL,
[EQ_USAGE] [char](1) NULL,
[CS_SIDE_PROC] [char](1) NULL,
[RUN_MODE] [char](2) NULL,
[LOTX_VALUE] [char](12) NULL,
[LOTY_VALUE] [char](12) NULL,
[MF] [char](2) NULL,
[END_RESULT] [char](2) NOT NULL,
[IS_NORMAL_END] [char](1) NULL,
[WORKNO_01] [int] NULL,
[WORKNO_02] [int] NULL,
[WORKNO_03] [int] NULL,
[WORKNO_04] [int] NULL,
[WORKNO_05] [int] NULL,
[WORKNO_06] [int] NULL,
[WORKNO_07] [int] NULL,
[WORKNO_08] [int] NULL,
[WORKNO_09] [int] NULL,
[WORKNO_10] [int] NULL,
[WORKNO_11] [int] NULL,
[WORKNO_12] [int] NULL,
[WORKNO_13] [int] NULL,
[WORKNO_14] [int] NULL,
[WORKNO_15] [int] NULL,
[WORKNO_16] [int] NULL,
[WORKNO_17] [int] NULL,
[WORKNO_18] [int] NULL,
[WORKNO_19] [int] NULL,
[WORKNO_20] [int] NULL,
[WORKNO_21] [int] NULL,
[WORKNO_22] [int] NULL,
[WORKNO_23] [int] NULL,
[WORKNO_24] [int] NULL,
[WORKNO_25] [int] NULL,
[WORKNO_26] [int] NULL,
[WORKNO_27] [int] NULL,
[WORKNO_28] [int] NULL,
[WORKNO_29] [int] NULL,
[WORKNO_30] [int] NULL,
[WORKNO_31] [int] NULL,
[WORKNO_32] [int] NULL,
[WORKNO_33] [int] NULL,
[WORKNO_34] [int] NULL,
[WORKNO_35] [int] NULL,
[WORKNO_36] [int] NULL,
[SheetID_01] [char](26) NULL,
[SheetID_02] [char](26) NULL,
[SheetID_03] [char](26) NULL,
[SheetID_04] [char](26) NULL,
[SheetID_05] [char](26) NULL,
[SheetID_06] [char](26) NULL,
[SheetID_07] [char](26) NULL,
[SheetID_08] [char](26) NULL,
[SheetID_09] [char](26) NULL,
[SheetID_10] [char](26) NULL,
[SheetID_11] [char](26) NULL,
[SheetID_12] [char](26) NULL,
[SheetID_13] [char](26) NULL,
[SheetID_14] [char](26) NULL,
[SheetID_15] [char](26) NULL,
[SheetID_16] [char](26) NULL,
[SheetID_17] [char](26) NULL,
[SheetID_18] [char](26) NULL,
[SheetID_19] [char](26) NULL,
[SheetID_20] [char](26) NULL,
[SheetID_21] [char](26) NULL,
[SheetID_22] [char](26) NULL,
[SheetID_23] [char](26) NULL,
[SheetID_24] [char](26) NULL,
[SheetID_25] [char](26) NULL,
[SheetID_26] [char](26) NULL,
[SheetID_27] [char](26) NULL,
[SheetID_28] [char](26) NULL,
[SheetID_29] [char](26) NULL,
[SheetID_30] [char](26) NULL,
[SheetID_31] [char](26) NULL,
[SheetID_32] [char](26) NULL,
[SheetID_33] [char](26) NULL,
[SheetID_34] [char](26) NULL,
[SheetID_35] [char](26) NULL,
[SheetID_36] [char](26) NULL,
PRIMARY KEY CLUSTERED
(
[PJID] ASC,
[CSTID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("APJID表格建立完成");
#endregion

#region APJPANELIDLST Table
// 建立APJPANELIDLST表格
LogMessage("正在建立APJPANELIDLST表格...");
currentSql = $@"
CREATE TABLE [dbo].[APJPANELIDLST](
[PJID] [char](50) NOT NULL,
[PANEL_ID] [char](26) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PJID] ASC,
[PANEL_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("APJPANELIDLST表格建立完成");
#endregion

#region APORT Table
// 建立APORT表格
LogMessage("正在建立APORT表格...");
currentSql = $@"
CREATE TABLE [dbo].[APORT](
[PORT_ID] [char](20) NOT NULL,
[EQPT_ID] [char](15) NOT NULL,
[UNIT_NUM] [int] NOT NULL,
[PORT_NUM] [int] NOT NULL,
[PORT_TYPE] [char](1) NOT NULL,
[PORT_USE_TYPE] [char](2) NULL,
[PORT_REAL_TYPE] [char](2) NULL,
[CAPACITY] [int] NOT NULL,
[PORT_STAT] [char](1) NOT NULL,
[PORT_ENABLE] [char](1) NOT NULL,
[PORT_STATUS] [char](1) NOT NULL,
[ACCESSMODE] [char](1) NULL,
[PORT_ACTIONSTATE] [int] NOT NULL,
[PORT_CMDSTAT] [int] NOT NULL,
[SERVICE_MODE] [char](1) NULL,
[CJID] [char](100) NULL,
[PJID] [char](50) NULL,
[CJ_STATUS] [int] NULL,
[PJ_STATUS] [int] NULL,
[RECIPEID] [char](4) NOT NULL,
PRIMARY KEY CLUSTERED
(
[PORT_ID] ASC,
[EQPT_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("APORT表格建立完成");
#endregion

#region ASEQUENCE Table
// 建立ASEQUENCE表格
LogMessage("正在建立ASEQUENCE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ASEQUENCE](
[SEQ_NAME] [char](12) NOT NULL,
[NXT_VAL] [bigint] NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NAME] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASEQUENCE表格建立完成");
#endregion

#region ASHEET Table
// 建立ASHEET表格
LogMessage("正在建立ASHEET表格...");
currentSql = $@"
CREATE TABLE [dbo].[ASHEET](
[SHT_ID] [char](26) NOT NULL,
[WORK_NO] [char](5) NOT NULL,
[FINAL_ID] [char](26) NULL,
[LOT_ID] [char](22) NULL,
[JOB_NO] [char](5) NOT NULL,
[PROD_ID] [char](20) NOT NULL,
[SHT_STAT] [int] NULL,
[SOURCE_CST_ID] [char](20) NULL,
[SOURCE_SLOT_NO] [int] NOT NULL,
[SOURCE_PORT_NO] [int] NOT NULL,
[TARGET_CST_ID] [char](20) NULL,
[TARGET_SLOT_NO] [int] NULL,
[TARGET_PORT_NO] [int] NULL,
[RECIPE_ID] [char](4) NULL,
[TAKE_OUT_STAT] [char](1) NULL,
[NODE_ID] [char](15) NULL,
[CST_ID] [char](20) NULL,
[SLOT_NO] [int] NOT NULL,
[TAKE_OUT_TIME] [char](16) NULL,
[SCRAP_CODE] [char](5) NULL,
[REASON_CODE] [char](5) NULL,
[LOGON_TIME] [datetime] NULL,
[LOGOff_TIME] [datetime] NULL,
[SELECTFLAG] [char](1) NULL,
[BCR_SHT_ID] [char](26) NULL,
[BCR_RESULT] [char](17) NULL,
[BCR_MISDISPO] [char](16) NULL,
[IPQC_SAMPLENO] [int] NULL,
[IPQC_RESULT] [char](16) NULL,
[USER_ID] [nvarchar](20) NULL,
[PROCESS_START_TIME] [char](16) NULL,
[PJID] [char](50) NULL,
[CJID] [char](100) NULL,
[DUMMY_CASE] [int] NULL,
[DUMMY_FLAG] [int] NULL,
PRIMARY KEY CLUSTERED
(
[SHT_ID] ASC,
[WORK_NO] ASC
),
UNIQUE NONCLUSTERED
(
[WORK_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASHEET表格建立完成");
#endregion

#region ASHTDCS Table
// 建立ASHTDCS表格
LogMessage("正在建立ASHTDCS表格...");
currentSql = $@"
CREATE TABLE [dbo].[ASHTDCS](
[SHT_ID] [char](26) NOT NULL,
[WORK_NO] [char](5) NOT NULL,
[ST1] [char](15) NULL,
[ST1_UNIT] [char](15) NULL,
[ST1_RECIPE] [char](4) NULL,
[ST1_ONTIME] [datetime] NULL,
[ST1_OFFTIME] [datetime] NULL,
[ST1_TIMEOUT] [char](3) NULL,
[ST2] [char](15) NULL,
[ST2_UNIT] [char](15) NULL,
[ST2_RECIPE] [char](4) NULL,
[ST2_ONTIME] [datetime] NULL,
[ST2_OFFTIME] [datetime] NULL,
[ST2_TIMEOUT] [char](3) NULL,
[ST3] [char](15) NULL,
[ST3_UNIT] [char](15) NULL,
[ST3_RECIPE] [char](4) NULL,
[ST3_ONTIME] [datetime] NULL,
[ST3_OFFTIME] [datetime] NULL,
[ST3_TIMEOUT] [char](3) NULL,
[ST4] [char](15) NULL,
[ST4_UNIT] [char](15) NULL,
[ST4_RECIPE] [char](4) NULL,
[ST4_ONTIME] [datetime] NULL,
[ST4_OFFTIME] [datetime] NULL,
[ST4_TIMEOUT] [char](3) NULL,
[ST5] [char](15) NULL,
[ST5_UNIT] [char](15) NULL,
[ST5_RECIPE] [char](4) NULL,
[ST5_ONTIME] [datetime] NULL,
[ST5_OFFTIME] [datetime] NULL,
[ST5_TIMEOUT] [char](3) NULL,
[ST6] [char](15) NULL,
[ST6_UNIT] [char](15) NULL,
[ST6_RECIPE] [char](4) NULL,
[ST6_ONTIME] [datetime] NULL,
[ST6_OFFTIME] [datetime] NULL,
[ST6_TIMEOUT] [char](3) NULL,
[START_WAIT_TIME] [datetime] NULL,
[NEXTST] [char](1) NULL,
[TOTLAST] [char](1) NULL,
[NEXTEPQT] [char](15) NULL,
[NEXTUNIT] [char](15) NULL,
[CURRENTEQ] [char](15) NULL,
[CURRENTUNIT] [char](15) NULL,
[CURRENTSTAGE] [char](2) NULL,
[CURRENTSLOT] [char](2) NULL,
[ROBOTNO] [char](1) NULL,
[ONROBOT] [char](1) NULL,
[TURN_FLAG] [char](1) NULL,
[SCRAP_FLAG] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
[SHT_ID] ASC,
[WORK_NO] ASC
),
UNIQUE NONCLUSTERED
(
[WORK_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASHTDCS表格建立完成");
#endregion

#region ASHTDESC Table
LogMessage("正在建立ASHTDESC表格...");
currentSql = $@"
CREATE TABLE ASHTDESC (
SHT_ID CHAR(26) NOT NULL,
WORK_NO CHAR(5) NOT NULL,
ARRAY_REPAIR_TYPE CHAR(800) NULL,
LCVD_REPAIR_TYPE CHAR(800) NULL,
PANEL_JUDGE CHAR(800) NULL,
LOT_TOP CHAR(1) NULL,
LOT_BUTTOM CHAR(1) NULL,
SHT_THICKNESS FLOAT NULL,
SHT_SIZE CHAR(1) NULL,
MASK_ID CHAR(30) NULL,
MAKER_ID CHAR(20) NULL,
TURN_GLASS CHAR(1) NULL,
PROBER_ID CHAR(20) NULL,
EXP_UNIT_ID CHAR(20) NULL,
EXP_RECIPE_ID CHAR(30) NULL,
SHT_ID_TYPE CHAR(1) NULL,
SMPL_FLAG CHAR(1) NULL,
REWORK_CNT INT NULL DEFAULT 0,
DUMMY_USED_CNT INT NULL DEFAULT 0,
JOB_TYPE INT NULL DEFAULT 0,
LOT_STARTEND INT NULL,
LOT_JUDGE CHAR(1) NULL,
RECIPE_01 CHAR(2) NULL,
RECIPE_02 CHAR(2) NULL,
RECIPE_03 CHAR(2) NULL,
RECIPE_04 CHAR(2) NULL,
RECIPE_05 CHAR(2) NULL,
RECIPE_06 CHAR(2) NULL,
RECIPE_07 CHAR(2) NULL,
RECIPE_08 CHAR(2) NULL,
SHT_TYPE CHAR(1) NULL,
PRIMARY KEY CLUSTERED (SHT_ID, WORK_NO),
UNIQUE NONCLUSTERED (WORK_NO)
) ;";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASHTDESC表格建立完成");
#endregion

#region ASHTPROCDATA Table
LogMessage("正在建立ASHTPROCDATA表格...");
currentSql = $@"
CREATE TABLE [dbo].[ASHTPROCDATA](
[SHT_ID] [char](26) NOT NULL,
[WORK_NO] [char](5) NOT NULL,
[EQPT_ID] [char](15) NOT NULL,
[PROCDATA_NAME] [char](30) NOT NULL,
[PROCDATA_VALUE] [char](30) NULL,
[UNIT] [char](15) NULL,
[NO] [char](4) NULL,
[EQ_Recipe_ID] [char](2) NULL,
PRIMARY KEY CLUSTERED
(
[SHT_ID] ASC,
[WORK_NO] ASC,
[EQPT_ID] ASC,
[PROCDATA_NAME] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASHTPROCDATA表格建立完成");
#endregion

#region ASUBFRAME Table
// 建立ASUBFRAME表格
LogMessage("正在建立ASUBFRAME表格...");
currentSql = $@"
CREATE TABLE [dbo].[ASUBFRAME](
[SUB_NO] [int] NOT NULL,
[SUBFRAME_ID] [char](26) NOT NULL,
[Port_No] [int] NOT NULL,
[SHT_ID] [char](26) NULL,
[FINAL_ID] [char](26) NULL,
[LOT_ID] [char](22) NULL,
[CST_ID] [char](20) NULL,
[SOURCE_CST_ID] [char](20) NOT NULL,
[TARGET_CST_ID] [char](20) NULL,
[SLOT_NO] [int] NOT NULL,
[BCR_SUBFRAME_ID] [char](26) NULL,
[BCR_RESULT] [char](17) NULL,
[IS_USED_STAT] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[SUB_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ASUBFRAME表格建立完成");
#endregion

#region ATRACEITEM Table
LogMessage("正在建立ATRACEITEM表格...");
currentSql = $@"
CREATE TABLE [dbo].[ATRACEITEM](
[TRACE_ID] [char](5) NOT NULL,
[SVID] [char](6) NOT NULL,
[SORT_NO] [int] NULL,
PRIMARY KEY CLUSTERED
(
[TRACE_ID] ASC,
[SVID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ATRACEITEM表格建立完成");
#endregion

#region ATRACEITEMVALUE Table
LogMessage("正在建立ATRACEITEMVALUE表格...");
currentSql = $@"
CREATE TABLE [dbo].[ATRACEITEMVALUE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TRACE_ID] [char](5) NULL,
[SVID] [char](6) NULL,
[SMP_SEQUENCE] [int] NULL,
[ITEM_SEQUENCE] [int] NULL,
[SV] [nvarchar](max) NULL,
[SMP_TIME] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ATRACEITEMVALUE表格建立完成");
#endregion

#region ATRACESET Table
LogMessage("正在建立ATRACESET表格...");
currentSql = $@"
CREATE TABLE [dbo].[ATRACESET](
[TRACE_ID] [char](5) NOT NULL,
[SMP_PERIOD] [char](6) NULL,
[SMP_PERIOD_SEC] [bigint] NULL,
[TOTAL_SMP_CNT] [int] NULL,
[SMP_GRP_CNT] [int] NULL,
[SMP_CNT] [int] NULL,
[NX_SMP_TIME] [datetime] NULL,
[SMP_TIME] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[TRACE_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("ATRACESET表格建立完成");
#endregion

#region AUNIT Table
// 建立AUNIT表格
LogMessage("正在建立AUNIT表格...");
currentSql = $@"
CREATE TABLE [dbo].[AUNIT](
[UNIT_ID] [char](15) NOT NULL,
[UNIT_NUM] [int] NOT NULL,
[EQPT_ID] [char](15) NOT NULL,
[UNIT_CATE] [char](1) NOT NULL,
[EQPT_TYPE] [char](1) NOT NULL,
[CAPACITY] [int] NOT NULL,
[UNIT_STAT] [char](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UNIT_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AUNIT表格建立完成");
#endregion

#region AZONE Table
// 建立AZONE表格
LogMessage("正在建立AZONE表格...");
currentSql = $@"
CREATE TABLE [dbo].[AZONE](
[ZONE_ID] [char](30) NOT NULL,
[LINE_ID] [char](25) NULL,
[LOT_ID] [char](22) NULL,
PRIMARY KEY CLUSTERED
(
[ZONE_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("AZONE表格建立完成");
#endregion

#region BCSTAT Table
// 建立BCSTAT表格
LogMessage("正在建立BCSTAT表格...");
currentSql = $@"
CREATE TABLE [dbo].[BCSTAT](
[BC_ID] [char](6) NOT NULL,
[CLOSE_MODE] [char](1) NOT NULL,
[RUN_TIMESTAMP] [char](16) NULL,
PRIMARY KEY CLUSTERED
(
[BC_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("BCSTAT表格建立完成");
#endregion

#region EFEMPPID Table
// 建立EFEMPPID表格
LogMessage("正在建立EFEMPPID表格...");
currentSql = $@"
CREATE TABLE [dbo].[EFEMPPID](
[PPID] [char](80) NOT NULL,
[ST1_EQ] [char](15) NULL,
[ST1_RECIPE] [char](4) NULL,
[ST1_PREGET] [char](1) NULL,
[ST1_TIMEOUT] [char](3) NULL,
[ST2_EQ] [char](15) NULL,
[ST2_RECIPE] [char](4) NULL,
[ST2_PREGET] [char](1) NULL,
[ST2_TIMEOUT] [char](3) NULL,
[ST3_EQ] [char](15) NULL,
[ST3_RECIPE] [char](4) NULL,
[ST3_PREGET] [char](1) NULL,
[ST3_TIMEOUT] [char](3) NULL,
[ST4_EQ] [char](15) NULL,
[ST4_RECIPE] [char](4) NULL,
[ST4_PREGET] [char](1) NULL,
[ST4_TIMEOUT] [char](3) NULL,
[ST5_EQ] [char](15) NULL,
[ST5_RECIPE] [char](4) NULL,
[ST5_PREGET] [char](1) NULL,
[ST5_TIMEOUT] [char](3) NULL,
[ST6_EQ] [char](15) NULL,
[ST6_RECIPE] [char](4) NULL,
[ST6_PREGET] [char](1) NULL,
[ST6_TIMEOUT] [char](3) NULL,
[RECIPE_VERSION] [char](10) NULL,
[LCTIME] [char](20) NULL,
[LCUSER] [char](20) NULL,
[Q_TIME] [char](5) NULL,
[REMARK] [char](40) NULL,
PRIMARY KEY CLUSTERED
(
[PPID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("EFEMPPID表格建立完成");
#endregion

#region HACASSETTE Table
// 建立HACASSETTE表格
LogMessage("正在建立HACASSETTE表格...");
currentSql = $@"
CREATE TABLE [dbo].[HACASSETTE](
[T_STAMP] [char](19) NOT NULL,
[CST_ID] [char](20) NULL,
[SHT_CNT] [char](5) NOT NULL,
[CSTLOGON_TIME] [datetime] NULL,
[CSTLOGOFF_TIME] [datetime] NULL,
[CSTPROCSTART_TIME] [datetime] NULL,
[CSTPROCEND_TIME] [datetime] NULL,
[END_STAT] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
[T_STAMP] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HACASSETTE表格建立完成");
#endregion

#region HACJID Table
// 建立HACJID表格
LogMessage("正在建立HACJID表格...");
currentSql = $@"
CREATE TABLE [dbo].[HACJID](
[T_STAMP] [char](19) NOT NULL,
[CJID] [char](100) NULL,
[CJ_STATUS] [int] NOT NULL,
[AUTO_START] [char](1) NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[PROCESS_END_CNT] [int] NOT NULL,
[SCRAP_CNT] [int] NOT NULL,
[TOTAL_COUNT] [int] NOT NULL,
[END_RESULT] [char](2) NOT NULL,
[PJID_01] [char](50) NULL,
[PJID_02] [char](50) NULL,
[PJID_03] [char](50) NULL,
[PJID_04] [char](50) NULL,
[PJID_05] [char](50) NULL,
[PJID_06] [char](50) NULL,
[PJID_07] [char](50) NULL,
[PJID_08] [char](50) NULL,
[PJID_09] [char](50) NULL,
[PJID_10] [char](50) NULL,
[PJID_11] [char](50) NULL,
[PJID_12] [char](50) NULL,
[LDPROCTIME] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[T_STAMP] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HACJID表格建立完成");
#endregion

#region HAPJID Table
// 建立HAPJID表格
LogMessage("正在建立HAPJID表格...");
currentSql = $@"
CREATE TABLE [dbo].[HAPJID](
[T_STAMP] [char](19) NOT NULL,
[PJID] [char](50) NULL,
[PJ_STATUS] [int] NOT NULL,
[PJ_PRESTATUS] [int] NULL,
[CSTID] [char](20) NULL,
[AUTOSTART] [char](1) NULL,
[CJID] [char](100) NULL,
[RECIPEID] [char](4) NOT NULL,
[SEQ_NO] [int] NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[START_TIME] [datetime] NULL,
[END_TIME] [datetime] NULL,
[SLOT_SELECT] [char](36) NULL,
[WORK_QTY] [char](3) NULL,
[SLOT_SELECT_NUM] [char](98) NULL,
[EQ_USAGE] [char](1) NULL,
[CS_SIDE_PROC] [char](1) NULL,
[RUN_MODE] [char](2) NULL,
[LOTX_VALUE] [char](12) NULL,
[LOTY_VALUE] [char](12) NULL,
[MF] [char](2) NULL,
[END_RESULT] [char](2) NOT NULL,
[IS_NORMAL_END] [char](1) NULL,
[WORKNO_01] [int] NULL,
[WORKNO_02] [int] NULL,
[WORKNO_03] [int] NULL,
[WORKNO_04] [int] NULL,
[WORKNO_05] [int] NULL,
[WORKNO_06] [int] NULL,
[WORKNO_07] [int] NULL,
[WORKNO_08] [int] NULL,
[WORKNO_09] [int] NULL,
[WORKNO_10] [int] NULL,
[WORKNO_11] [int] NULL,
[WORKNO_12] [int] NULL,
[WORKNO_13] [int] NULL,
[WORKNO_14] [int] NULL,
[WORKNO_15] [int] NULL,
[WORKNO_16] [int] NULL,
[WORKNO_17] [int] NULL,
[WORKNO_18] [int] NULL,
[WORKNO_19] [int] NULL,
[WORKNO_20] [int] NULL,
[WORKNO_21] [int] NULL,
[WORKNO_22] [int] NULL,
[WORKNO_23] [int] NULL,
[WORKNO_24] [int] NULL,
[WORKNO_25] [int] NULL,
[WORKNO_26] [int] NULL,
[WORKNO_27] [int] NULL,
[WORKNO_28] [int] NULL,
[WORKNO_29] [int] NULL,
[WORKNO_30] [int] NULL,
[WORKNO_31] [int] NULL,
[WORKNO_32] [int] NULL,
[WORKNO_33] [int] NULL,
[WORKNO_34] [int] NULL,
[WORKNO_35] [int] NULL,
[WORKNO_36] [int] NULL,
[SheetID_01] [char](26) NULL,
[SheetID_02] [char](26) NULL,
[SheetID_03] [char](26) NULL,
[SheetID_04] [char](26) NULL,
[SheetID_05] [char](26) NULL,
[SheetID_06] [char](26) NULL,
[SheetID_07] [char](26) NULL,
[SheetID_08] [char](26) NULL,
[SheetID_09] [char](26) NULL,
[SheetID_10] [char](26) NULL,
[SheetID_11] [char](26) NULL,
[SheetID_12] [char](26) NULL,
[SheetID_13] [char](26) NULL,
[SheetID_14] [char](26) NULL,
[SheetID_15] [char](26) NULL,
[SheetID_16] [char](26) NULL,
[SheetID_17] [char](26) NULL,
[SheetID_18] [char](26) NULL,
[SheetID_19] [char](26) NULL,
[SheetID_20] [char](26) NULL,
[SheetID_21] [char](26) NULL,
[SheetID_22] [char](26) NULL,
[SheetID_23] [char](26) NULL,
[SheetID_24] [char](26) NULL,
[SheetID_25] [char](26) NULL,
[SheetID_26] [char](26) NULL,
[SheetID_27] [char](26) NULL,
[SheetID_28] [char](26) NULL,
[SheetID_29] [char](26) NULL,
[SheetID_30] [char](26) NULL,
[SheetID_31] [char](26) NULL,
[SheetID_32] [char](26) NULL,
[SheetID_33] [char](26) NULL,
[SheetID_34] [char](26) NULL,
[SheetID_35] [char](26) NULL,
[SheetID_36] [char](26) NULL,
PRIMARY KEY CLUSTERED
(
[T_STAMP] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HAPJID表格建立完成");
#endregion

#region HASHEET Table
LogMessage("正在建立HASHEET表格...");
currentSql = $@"
CREATE TABLE [dbo].[HASHEET](
[SEQ_NO] [nvarchar](255) NOT NULL,
[T_STAMP] [char](19) NULL,
[SHT_ID] [char](26) NULL,
[FINAL_ID] [char](26) NULL,
[BCR_SHT_ID] [char](26) NULL,
[WORK_NO] [char](5) NOT NULL,
[LOT_ID] [char](22) NULL,
[JOB_NO] [char](5) NOT NULL,
[PROD_ID] [char](20) NOT NULL,
[SHT_STAT] [int] NULL,
[SOURCE_CST_ID] [char](20) NULL,
[SOURCE_SLOT_NO] [int] NOT NULL,
[SOURCE_PORT_NO] [int] NOT NULL,
[TARGET_CST_ID] [char](20) NULL,
[TARGET_SLOT_NO] [int] NULL,
[TARGET_PORT_NO] [int] NULL,
[RECIPE_ID] [char](4) NULL,
[TAKE_OUT_STAT] [char](1) NULL,
[NODE_ID] [char](15) NULL,
[CST_ID] [char](20) NULL,
[SLOT_NO] [int] NOT NULL,
[TAKE_OUT_TIME] [char](16) NULL,
[SCRAP_CODE] [char](5) NULL,
[REASON_CODE] [char](5) NULL,
[LOGON_TIME] [datetime] NULL,
[LOGOff_TIME] [datetime] NULL,
[SELECTFLAG] [char](1) NULL,
[BCR_RESULT] [char](17) NULL,
[BCR_MISDISPO] [char](16) NULL,
[IPQC_RESULT] [char](16) NULL,
[USER_ID] [nvarchar](20) NULL,
[PJID] [char](50) NULL,
[CJID] [char](100) NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NO] ASC
),
UNIQUE NONCLUSTERED
(
[WORK_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HASHEET表格建立完成");
#endregion

#region HASHTDCS Table
LogMessage("正在建立HASHTDCS表格...");
currentSql = $@"
CREATE TABLE [dbo].[HASHTDCS](
[SEQ_NO] [nvarchar](255) NOT NULL,
[T_STAMP] [char](19) NULL,
[SHT_ID] [char](26) NULL,
[WORK_NO] [char](5) NULL,
[ST1] [char](15) NULL,
[ST1_UNIT] [char](15) NULL,
[ST1_RECIPE] [char](4) NULL,
[ST1_ONTIME] [datetime] NULL,
[ST1_OFFTIME] [datetime] NULL,
[ST1_TIMEOUT] [char](3) NULL,
[ST2] [char](15) NULL,
[ST2_UNIT] [char](15) NULL,
[ST2_RECIPE] [char](4) NULL,
[ST2_ONTIME] [datetime] NULL,
[ST2_OFFTIME] [datetime] NULL,
[ST2_TIMEOUT] [char](3) NULL,
[ST3] [char](15) NULL,
[ST3_UNIT] [char](15) NULL,
[ST3_RECIPE] [char](4) NULL,
[ST3_ONTIME] [datetime] NULL,
[ST3_OFFTIME] [datetime] NULL,
[ST3_TIMEOUT] [char](3) NULL,
[ST4] [char](15) NULL,
[ST4_UNIT] [char](15) NULL,
[ST4_RECIPE] [char](4) NULL,
[ST4_ONTIME] [datetime] NULL,
[ST4_OFFTIME] [datetime] NULL,
[ST4_TIMEOUT] [char](3) NULL,
[ST5] [char](15) NULL,
[ST5_UNIT] [char](15) NULL,
[ST5_RECIPE] [char](4) NULL,
[ST5_ONTIME] [datetime] NULL,
[ST5_OFFTIME] [datetime] NULL,
[ST5_TIMEOUT] [char](3) NULL,
[ST6] [char](15) NULL,
[ST6_UNIT] [char](15) NULL,
[ST6_RECIPE] [char](4) NULL,
[ST6_ONTIME] [datetime] NULL,
[ST6_OFFTIME] [datetime] NULL,
[ST6_TIMEOUT] [char](3) NULL,
[START_WAIT_TIME] [datetime] NULL,
[NEXTST] [char](1) NULL,
[TOTLAST] [char](1) NULL,
[NEXTEPQT] [char](15) NULL,
[NEXTUNIT] [char](15) NULL,
[CURRENTEQ] [char](15) NULL,
[CURRENTUNIT] [char](15) NULL,
[CURRENTSTAGE] [char](2) NULL,
[CURRENTSLOT] [char](2) NULL,
[ROBOTNO] [char](1) NULL,
[ONROBOT] [char](1) NULL,
[TURN_FLAG] [char](1) NULL,
[SCRAP_FLAG] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HASHTDCS表格建立完成");
#endregion

#region HASHTDESC Table
LogMessage("正在建立HASHTDESC表格...");
currentSql = $@"
CREATE TABLE HASHTDESC (
SEQ_NO NVARCHAR(255) NOT NULL,
T_STAMP CHAR(19) NULL,
SHT_ID CHAR(26) NULL,
WORK_NO CHAR(5) NULL,
ARRAY_REPAIR_TYPE CHAR(800) NULL,
LCVD_REPAIR_TYPE CHAR(800) NULL,
PANEL_JUDGE CHAR(800) NULL,
LOT_TOP CHAR(1) NULL,
LOT_BUTTOM CHAR(1) NULL,
SHT_THICKNESS FLOAT NULL,
SHT_SIZE CHAR(1) NULL,
MASK_ID CHAR(30) NULL,
MAKER_ID CHAR(20) NULL,
TURN_GLASS CHAR(1) NULL,
PROBER_ID CHAR(20) NULL,
EXP_UNIT_ID CHAR(20) NULL,
EXP_RECIPE_ID CHAR(30) NULL,
SHT_ID_TYPE CHAR(1) NULL,
SMPL_FLAG CHAR(1) NULL,
REWORK_CNT INT NULL DEFAULT 0,
DUMMY_USED_CNT INT NULL DEFAULT 0,
JOB_TYPE INT NULL DEFAULT 0,
LOT_STARTEND CHAR(1) NULL,
LOT_JUDGE CHAR(1) NULL,
RECIPE_01 CHAR(2) NULL,
RECIPE_02 CHAR(2) NULL,
RECIPE_03 CHAR(2) NULL,
RECIPE_04 CHAR(2) NULL,
RECIPE_05 CHAR(2) NULL,
RECIPE_06 CHAR(2) NULL,
RECIPE_07 CHAR(2) NULL,
RECIPE_08 CHAR(2) NULL,
SHT_TYPE CHAR(1) NULL,
PRIMARY KEY CLUSTERED (SEQ_NO)
)";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HASHTDESC表格建立完成");
#endregion

#region HASUBFRAME Table
LogMessage("正在建立HASUBFRAME表格...");
currentSql = $@"
CREATE TABLE [dbo].[HASUBFRAME](
[SEQ_NO] [nvarchar](255) NOT NULL,
[T_STAMP] [char](19) NULL,
[SUB_NO] [int] NOT NULL,
[SUBFRAME_ID] [char](26) NOT NULL,
[Port_No] [int] NOT NULL,
[SHT_ID] [char](26) NULL,
[FINAL_ID] [char](26) NULL,
[LOT_ID] [char](22) NULL,
[CST_ID] [char](20) NULL,
[SOURCE_CST_ID] [char](20) NOT NULL,
[TARGET_CST_ID] [char](20) NULL,
[SLOT_NO] [int] NOT NULL,
[BCR_SUBFRAME_ID] [char](26) NULL,
[BCR_RESULT] [char](17) NULL,
[IS_USED_STAT] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HASUBFRAME表格建立完成");
#endregion

#region HOPERATION Table
// 建立HOPERATION表格
LogMessage("正在建立HOPERATION表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE [dbo].[HOPERATION](
[SEQ_NO] [nvarchar](255) NOT NULL,
[T_STAMP] [char](19) NULL,
[USER_ID] [nvarchar](20) NULL,
[FORM_NAME] [nvarchar](30) NULL,
[ACTION] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[SEQ_NO] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("HOPERATION表格建立完成");
#endregion

#region UASFNC Table
// 建立UASFNC表格
LogMessage("正在建立UASFNC表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE [dbo].[UASFNC](
[FUNC_CODE] [nvarchar](60) NOT NULL,
[FUNC_NAME] [nvarchar](80) NULL,
PRIMARY KEY CLUSTERED
(
[FUNC_CODE] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASFNC表格建立完成");
#endregion

#region UASUFNC Table
// 建立UASUFNC表格
LogMessage("正在建立UASUFNC表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE [dbo].[UASUFNC](
[USER_GRP] [nvarchar](20) NOT NULL,
[FUNC_CODE] [nvarchar](60) NOT NULL,
PRIMARY KEY CLUSTERED
(
[USER_GRP] ASC,
[FUNC_CODE] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUFNC表格建立完成");
#endregion

#region UASUSR Table
// 建立UASUSR表格
LogMessage("正在建立UASUSR表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE [dbo].[UASUSR](
[USER_ID] [nvarchar](20) NOT NULL,
[PASSWD] [nvarchar](max) NULL,
[BADGE_NUMBER] [nvarchar](80) NULL,
[USER_NAME] [nvarchar](30) NULL,
[DISABLE_FLG] [char](1) NULL,
[POWER_USER_FLG] [char](1) NULL,
[ADMIN_FLG] [char](1) NULL,
[USER_GRP] [nvarchar](20) NULL,
[DEPARTMENT] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[USER_ID] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUSR表格建立完成");
#endregion

#region UASUSRGRP Table
// 建立UASUSRGRP表格
LogMessage("正在建立UASUSRGRP表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE [dbo].[UASUSRGRP](
[USER_GRP] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[USER_GRP] ASC
)
) ON [PRIMARY]";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUSRGRP表格建立完成");
#endregion

#region UNITPOSITION Table
// 建立UNITPOSITION表格
LogMessage("正在建立UNITPOSITION表格...");
// 使用currentSql變數儲存SQL語句，方便例外時記錄
currentSql = $@"
CREATE TABLE UNITPOSITION (
EQPT_REAL_ID CHAR(15) NOT NULL,
POSITION CHAR(2) NOT NULL,
UPDATETIME CHAR(19) NULL,
UNIT_ID CHAR(15) NULL,
UNIT_NO CHAR(2) NULL,
SLOT_NO CHAR(4) NULL,
TYPE CHAR(1) NULL,
UNIT_STATE CHAR(15) NULL,
GLASS_ID CHAR(26) NULL,
UNIT_RESULT CHAR(15) NULL,
DESCRIPTION CHAR(40) NULL,
PRIMARY KEY CLUSTERED (EQPT_REAL_ID, POSITION)
);";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UNITPOSITION表格建立完成");
#endregion

#region VERSION Table
// 建立VERSION表格
LogMessage("正在建立VERSION表格...");
currentSql = $@"
CREATE TABLE VERSION (
VERSION NVARCHAR(25) NULL
)";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("VERSION表格建立完成");

// 寫入當前版本號
currentSql = $@"
INSERT INTO VERSION (VERSION) VALUES (@version)";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.Parameters.AddWithValue("@version", System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString());
command.ExecuteNonQuery();
}
LogMessage("版本號寫入完成");
#endregion

#region VSHEET View
// 建立VSHEET View
LogMessage("正在建立VSHEET View...");
currentSql = $@"
IF EXISTS (SELECT * FROM sys.views WHERE name = 'VSHEET')
BEGIN
DROP VIEW VSHEET
END";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("已刪除舊的VSHEET View");

currentSql = $@"
CREATE VIEW VSHEET AS
Select s.SHT_ID, s.WORK_NO, s.JOB_NO, s.PROD_ID, s.SHT_STAT, s.SOURCE_CST_ID, s.SOURCE_SLOT_NO, s.SOURCE_PORT_NO,
s.TARGET_CST_ID, s.TARGET_SLOT_NO, s.TARGET_PORT_NO, s.RECIPE_ID, s.BCR_Result, s.IPQC_SampleNo, s.IPQC_Result,
s.TAKE_OUT_STAT,
s.CST_ID, s.SLOT_NO, s.TAKE_OUT_TIME, s.CJID, s.PJID,
c.CURRENTEQ, c.CURRENTUNIT, c.CURRENTSTAGE, c.CURRENTSLOT, c.ROBOTNO, c.ONROBOT,
e.CSTLOGON_TIME, e.CSTLOGOFF_TIME, e.CST_STAT, e.CSTPROCSTART_TIME, e.CSTPROCEND_TIME,
f.PJIDSTART_TIME,
g.CJCREATE_TIME, g.CJ_AUTOSTART
FROM ASHEET AS s
LEFT JOIN ASHTDCS AS c ON s.SHT_ID = c.SHT_ID
LEFT JOIN ACASSETTE AS e ON s.SOURCE_CST_ID = e.CST_ID
LEFT JOIN APJID AS f ON s.PJID = f.PJID
LEFT JOIN ACJID AS g ON s.CJID = g.CJID";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("VSHEET View建立完成");
#endregion
}
catch (Exception ex)
{
LogMessage($"建立表格時發生錯誤: {ex.Message}", true);
LogMessage($"錯誤詳細資訊: {ex.StackTrace}", true);
LogMessage($"發生錯誤的SQL語句: {currentSql}", true);

throw; // 重新拋出例外，讓上層處理交易回滾
}
}

private void InsertInitialData(SqlConnection conn, SqlTransaction transaction)
{
string currentSql = string.Empty;
try
{
LogMessage("開始插入基本資料...");

#region UASFNC Table
// 插入UASFNC基本資料
LogMessage("正在插入UASFNC基本資料...");
currentSql = @"
INSERT INTO UASFNC (FUNC_CODE, FUNC_NAME) VALUES
(N'FUNC_ALARM_INFO_QUERY', N'Alarm Information'),
(N'FUNC_ALARM_MAINTENANCE', N'Alarm Maintenance'),
(N'FUNC_CJPJ_MAINTENANCE', N'CJ/PJ Maintenance'),
(N'FUNC_CJPJINFORMATION', N'CJ/PJ Information'),
(N'FUNC_CLOSE_BC', N'System Close'),
(N'FUNC_COMMAND_INFO_QUERY', N'Command Information'),
(N'FUNC_CONNECTION_CONTROL', N'Connection Control'),
(N'FUNC_CST_DATA_OPERATION', N'CST Data Operation'),
(N'FUNC_CST_INFO_QUERY', N'CST Information'),
(N'FUNC_DATABASE_SETTINGS', N'Database Settings'),
(N'FUNC_DEBUG_CYCLE_RUN', N'Cycle Run'),
(N'FUNC_DEBUG_EAP_REPORT', N'EAP Report'),
(N'FUNC_DEBUG_ENGINEERING_MODE', N'Engineering Mode'),
(N'FUNC_DEBUG_PLC_REPORT', N'PLC Report'),
(N'FUNC_EQ_CONSTANT', N'Equipment Constant'),
(N'FUNC_EQ_MESSAGE_QUERY', N'EQ Message'),
(N'FUNC_EQ_RECIPE_LIST', N'EQ Recipe'),
(N'FUNC_HOST_MESSAGE_QUERY', N'Host Message'),
(N'FUNC_LOGOUT', N'User LogOut'),
(N'FUNC_OPERATION_PERFORMANCE', N'Operation Performance'),
(N'FUNC_PASSWORD_CHANGE', N'Password Change'),
(N'FUNC_PORT_MAINTENANCE', N'Port Maintenance'),
(N'FUNC_RUN_MODE_CHANGE', N'Run Mode Change'),
(N'FUNC_TERMINAL_REQUEST', N'Terminal Request'),
(N'FUNC_TIP_MESSAGE_QUERY', N'Tip Message'),
(N'FUNC_USER_MANAGEMENT', N'User Account Management'),
(N'FUNC_USER_OPERATION_RECORD', N'User Operation Records'),
(N'FUNC_WIP_INFO_QUERY', N'WIP Information')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASFNC基本資料插入完成");
#endregion

#region UASUSRGRP Table
// 插入UASUSRGRP基本資料
LogMessage("正在插入UASUSRGRP基本資料...");
currentSql = @"
INSERT INTO UASUSRGRP (USER_GRP) VALUES
(N'ADMIN'),
(N'ENG'),
(N'OPER')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUSRGRP基本資料插入完成");
#endregion

#region UASUSR Table
// 插入UASUSR基本資料 (預設管理員帳號)
LogMessage("正在插入UASUSR基本資料...");
currentSql = @"
INSERT INTO UASUSR (USER_ID, PASSWD, BADGE_NUMBER, USER_NAME, DISABLE_FLG, POWER_USER_FLG, ADMIN_FLG, USER_GRP, DEPARTMENT) VALUES
(N'ADMIN ', N'ml22099478', NULL, N'Admin ', N'N', N'N', N'N', N'ADMIN ', N'FA '),
(N'ENG ', N'umtckf', NULL, N'Eng ', N'N', N'N', N'N', N'ENG ', N' '),
(N'OP ', N'123', NULL, N' ', N'N', NULL, N'N', N'OPER ', N' ')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUSR基本資料插入完成");
#endregion

#region UASUFNC Table
// 插入UASUFNC基本資料 (管理員權限)
LogMessage("正在插入UASUFNC基本資料...");
currentSql = @"
INSERT INTO UASUFNC (USER_GRP, FUNC_CODE) VALUES
(N'ADMIN', N'FUNC_ALARM_INFO_QUERY'),
(N'ADMIN', N'FUNC_ALARM_MAINTENANCE'),
(N'ADMIN', N'FUNC_CJPJ_MAINTENANCE'),
(N'ADMIN', N'FUNC_CJPJINFORMATION'),
(N'ADMIN', N'FUNC_CLOSE_BC'),
(N'ADMIN', N'FUNC_CONNECTION_CONTROL'),
(N'ADMIN', N'FUNC_CST_DATA_OPERATION'),
(N'ADMIN', N'FUNC_DATABASE_SETTINGS'),
(N'ADMIN', N'FUNC_DEBUG_CYCLE_RUN'),
(N'ADMIN', N'FUNC_DEBUG_EAP_REPORT'),
(N'ADMIN', N'FUNC_DEBUG_ENGINEERING_MODE'),
(N'ADMIN', N'FUNC_DEBUG_PLC_REPORT'),
(N'ADMIN', N'FUNC_EQ_CONSTANT'),
(N'ADMIN', N'FUNC_EQ_MESSAGE_QUERY'),
(N'ADMIN', N'FUNC_EQ_RECIPE_LIST'),
(N'ADMIN', N'FUNC_HOST_MESSAGE_QUERY'),
(N'ADMIN', N'FUNC_LOGOUT'),
(N'ADMIN', N'FUNC_OPERATION_PERFORMANCE'),
(N'ADMIN', N'FUNC_PASSWORD_CHANGE'),
(N'ADMIN', N'FUNC_PORT_MAINTENANCE'),
(N'ADMIN', N'FUNC_PPID_MAINTENANCE'),
(N'ADMIN', N'FUNC_RUN_MODE_CHANGE'),
(N'ADMIN', N'FUNC_TERMINAL_REQUEST'),
(N'ADMIN', N'FUNC_TIP_MESSAGE_QUERY'),
(N'ADMIN', N'FUNC_USER_MANAGEMENT'),
(N'ADMIN', N'FUNC_USER_OPERATION_RECORD'),
(N'ADMIN', N'FUNC_WIP_INFO_QUERY')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}

// 插入ENG權限
currentSql = @"
INSERT INTO UASUFNC (USER_GRP, FUNC_CODE) VALUES
(N'ENG', N'FUNC_ALARM_INFO_QUERY'),
(N'ENG', N'FUNC_ALARM_MAINTENANCE'),
(N'ENG', N'FUNC_CJPJ_MAINTENANCE'),
(N'ENG', N'FUNC_CJPJINFORMATION'),
(N'ENG', N'FUNC_CLOSE_BC'),
(N'ENG', N'FUNC_CONNECTION_CONTROL'),
(N'ENG', N'FUNC_CST_DATA_OPERATION'),
(N'ENG', N'FUNC_DATABASE_SETTINGS'),
(N'ENG', N'FUNC_DEBUG_CYCLE_RUN'),
(N'ENG', N'FUNC_DEBUG_EAP_REPORT'),
(N'ENG', N'FUNC_DEBUG_ENGINEERING_MODE'),
(N'ENG', N'FUNC_DEBUG_PLC_REPORT'),
(N'ENG', N'FUNC_EQ_CONSTANT'),
(N'ENG', N'FUNC_EQ_MESSAGE_QUERY'),
(N'ENG', N'FUNC_EQ_RECIPE_LIST'),
(N'ENG', N'FUNC_HOST_MESSAGE_QUERY'),
(N'ENG', N'FUNC_LOGOUT'),
(N'ENG', N'FUNC_OPERATION_PERFORMANCE'),
(N'ENG', N'FUNC_PASSWORD_CHANGE'),
(N'ENG', N'FUNC_PORT_MAINTENANCE'),
(N'ENG', N'FUNC_PPID_MAINTENANCE'),
(N'ENG', N'FUNC_RUN_MODE_CHANGE'),
(N'ENG', N'FUNC_TERMINAL_REQUEST'),
(N'ENG', N'FUNC_TIP_MESSAGE_QUERY'),
(N'ENG', N'FUNC_USER_OPERATION_RECORD'),
(N'ENG', N'FUNC_WIP_INFO_QUERY')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}

// 插入OPER權限
currentSql = @"
INSERT INTO UASUFNC (USER_GRP, FUNC_CODE) VALUES
(N'OPER', N'FUNC_ALARM_INFO_QUERY'),
(N'OPER', N'FUNC_CJPJ_MAINTENANCE'),
(N'OPER', N'FUNC_CJPJINFORMATION'),
(N'OPER', N'FUNC_CONNECTION_CONTROL'),
(N'OPER', N'FUNC_CST_DATA_OPERATION'),
(N'OPER', N'FUNC_EQ_MESSAGE_QUERY'),
(N'OPER', N'FUNC_EQ_RECIPE_LIST'),
(N'OPER', N'FUNC_HOST_MESSAGE_QUERY'),
(N'OPER', N'FUNC_LOGOUT'),
(N'OPER', N'FUNC_OPERATION_PERFORMANCE'),
(N'OPER', N'FUNC_TIP_MESSAGE_QUERY'),
(N'OPER', N'FUNC_USER_OPERATION_RECORD'),
(N'OPER', N'FUNC_WIP_INFO_QUERY')";
using (var command = new SqlCommand(currentSql, conn, transaction))
{
command.ExecuteNonQuery();
}
LogMessage("UASUFNC基本資料插入完成");
#endregion

LogMessage("所有基本資料插入完成");
}
catch (Exception ex)
{
LogMessage($"插入基本資料時發生錯誤: {ex.Message}", true);
LogMessage($"錯誤詳細資訊: {ex.StackTrace}", true);
LogMessage($"發生錯誤的SQL語句: {currentSql}", true);
throw; // 重新拋出例外，讓上層處理交易回滾
}
}

private async void btn_UpdateDatabase_Click(object sender, RoutedEventArgs e)
{
if (!CheckUnsavedChanges()) return;
SetOperationButtonsState(false, "更新資料庫");

string backupDbName = null;
bool overallUpdateSuccess = false; // Renamed for clarity
var cts = new CancellationTokenSource(); // For cancellation

try
{
PauseTraceDataReportTimer();
var confirmResult = MessageBox.Show("確定要更新資料庫嗎？\n\n註：更新失敗時，資料庫會自動盡可能的還原至更新前的狀態。\n\nAre you sure you want to update the database?\n\nNote: If the update fails, the database will attempt to restore to the state before the update as much as possible.",
"確認操作 / Confirm Operation", MessageBoxButton.YesNo, MessageBoxImage.Question);
if (confirmResult != MessageBoxResult.Yes)
{
LogMessage("使用者取消操作");
return;
}

LogMessage("資料更新作業開始");
if (!ValidateDatabaseSettings()) return;
UpdateConnectionStrings();
if (!CheckAccountTables())
{
LogMessage("使用者因資料表風險取消操作");
return;
}

// Check if BC_Dev exists
using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync(cts.Token);
using (var command = new SqlCommand("SELECT COUNT(*) FROM sys.databases WHERE name = 'BC_Dev'", connection))
{
if ((int)await command.ExecuteScalarAsync(cts.Token) == 0)
{
LogMessage("找不到 BC_Dev 資料庫，無法執行更新");
MessageBox.Show("找不到 BC_Dev 資料庫，無法執行更新。\n\nDatabase 'BC_Dev' not found. Update cannot be performed.", "提示 / Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
}
}

// Backup Database
var backupTaskResult = await Task.Run(async () =>
{
System.Threading.Thread.CurrentThread.Name = "UpdateDatabase_BackupTask";
_currentOperation = "更新資料庫 - 備份";
return await BackupDatabase(); // BackupDatabase itself should handle cancellation token if made async and passed cts.Token
}, cts.Token);


if (backupTaskResult == null || backupTaskResult.DatabaseName == null)
{
LogMessage($"資料庫備份失敗: {backupTaskResult?.ErrorMessage ?? "未知錯誤"}", true);
MessageBox.Show("資料庫備份失敗，無法繼續執行更新操作。\n請參閱操作記錄了解詳細資訊。", "提示 / Information", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
backupDbName = backupTaskResult.DatabaseName;
LogMessage($"資料庫成功備份為: {backupDbName}");


// Delete original BC_Dev (MDF and LDF files as well)
try
{
LogMessage("開始刪除原始BC_Dev資料庫...");
// ... (code to delete database and files, ensure it uses cts.Token if async)
using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync(cts.Token);
string mdfPath = string.Empty, ldfPath = string.Empty;
bool bcDevExists = false;
using (var cmd = new SqlCommand("SELECT COUNT(*) FROM sys.databases WHERE name = 'BC_Dev'", connection))
bcDevExists = (int)await cmd.ExecuteScalarAsync(cts.Token) > 0;

if (bcDevExists)
{
using (var cmd = new SqlCommand("SELECT physical_name, type_desc FROM sys.master_files WHERE database_id = DB_ID('BC_Dev')", connection))
using (var reader = await cmd.ExecuteReaderAsync(cts.Token))
{
while (await reader.ReadAsync(cts.Token))
{
if (reader["type_desc"].ToString() == "ROWS") mdfPath = reader["physical_name"].ToString();
else if (reader["type_desc"].ToString() == "LOG") ldfPath = reader["physical_name"].ToString();
}
}
using (var cmd = new SqlCommand("ALTER DATABASE [BC_Dev] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [BC_Dev];", connection))
await cmd.ExecuteNonQueryAsync(cts.Token);
LogMessage("BC_Dev資料庫已從SQL Server移除");

await Task.Delay(1000, cts.Token); // Wait for file release
if (!string.IsNullOrEmpty(mdfPath) && IOFile.Exists(mdfPath)) try { IOFile.Delete(mdfPath); LogMessage($"已刪除MDF檔案: {mdfPath}"); } catch (Exception ex) { LogMessage($"警告：無法刪除MDF檔案: {ex.Message}", true); }
if (!string.IsNullOrEmpty(ldfPath) && IOFile.Exists(ldfPath)) try { IOFile.Delete(ldfPath); LogMessage($"已刪除LDF檔案: {ldfPath}"); } catch (Exception ex) { LogMessage($"警告：無法刪除LDF檔案: {ex.Message}", true); }
}
else LogMessage("找不到BC_Dev資料庫，無需刪除");
}
}
catch (OperationCanceledException) { LogMessage("刪除原始資料庫操作已取消。"); throw; } // Rethrow to main catch
catch (Exception ex) { LogMessage($"刪除原始資料庫時發生錯誤: {ex.Message}", true); /* continue, Create will try again */ }


var progressIndicator = new Progress<string>(message => { DoEvents(); });

// Create new DB (without initial data)
await Task.Run(async () => await CreateDatabaseWithProgress(false, progressIndicator, cts.Token), cts.Token);


// Migrate Data
bool migrationSuccess = false;
// _migrationFailedDueToError will be set by MigrateData if it's an internal error

migrationSuccess = await Task.Run(() => MigrateData(progressIndicator, cts.Token), cts.Token);


if (!migrationSuccess) // This means MigrateData returned false
{
if (_migrationFailedDueToError) // MigrateData itself had an error
{
LogMessage("資料庫更新因資料遷移錯誤而失敗，將還原資料庫...", true);
MessageBox.Show("資料庫更新因資料遷移錯誤而失敗，已還原至原始狀態。\n請檢查日誌以獲取詳細資訊。", "遷移錯誤 / Migration Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
else // User chose to abort during MigrateData (e.g., from a dialog) or some other non-OperationCanceled failure
{
LogMessage("資料遷移未成功 (可能由使用者在提示中選擇中止)，將還原資料庫...", true);
MessageBox.Show("資料庫更新期間，資料遷移未完成，操作已中止並還原至原始狀態。", "操作中止 / Operation Aborted", MessageBoxButton.OK, MessageBoxImage.Warning);
}
await RestoreDatabase(backupDbName); // Restore is critical here
return; // Stop further execution
}

// If migration was successful
LogMessage("資料庫更新作業成功完成");
overallUpdateSuccess = true; // Mark overall success
backupDbName = null; // Prevent restore in finally if we got here

Dispatcher.BeginInvoke(new Action(() =>
{
try
{
LogMessage("更新資料庫成功，開始檢查欄位定義一致性...");
bool hasInconsistentFields = CheckFieldConsistency(false);
if (hasInconsistentFields)
{
MessageBox.Show("資料庫更新成功，但發現欄位定義不一致的情況，詳情請查看日誌報告。", "更新完成但有警告", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
MessageBox.Show("資料庫更新成功！\n需要重新啟動程式才能生效。", "系統提示", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
LogMessage($"更新後檢查欄位一致性時發生錯誤: {ex.Message}", true);
MessageBox.Show("資料庫更新成功！\n需要重新啟動程式才能生效。", "系統提示", MessageBoxButton.OK, MessageBoxImage.Information);
}
MessageBox.Show("資料庫更新完成，程式即將關閉。\n請手動重新啟動程式。", "系統提示", MessageBoxButton.OK, MessageBoxImage.Information);
ShutdownApplication();
}), DispatcherPriority.Normal);
}
catch (OperationCanceledException opEx) // Catches cancellations from any await Task.Run or from MigrateData
{
LogMessage($"資料庫更新操作被取消: {opEx.Message}", true);
if (!string.IsNullOrEmpty(backupDbName))
{
try
{
LogMessage("因操作取消，嘗試還原資料庫...", true);
await RestoreDatabase(backupDbName); // Restore if backup was made
MessageBox.Show("資料庫更新操作已取消，並已還原至原始狀態。", "操作已取消", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception restoreEx)
{
LogMessage($"因操作取消而還原資料庫時發生錯誤: {restoreEx.Message}", true);
MessageBox.Show($"資料庫更新操作已取消，但在還原過程中發生錯誤。\n請聯繫系統管理員。\n\n{restoreEx.Message}", "還原錯誤", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else
{
MessageBox.Show("資料庫更新操作已取消。", "操作已取消", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex) // General errors during the update process
{
LogMessage($"更新資料庫時發生未預期錯誤: {ex.Message}{Environment.NewLine}StackTrace: {ex.StackTrace}", true);
if (!string.IsNullOrEmpty(backupDbName) && !overallUpdateSuccess) // only restore if update wasn't flagged as successful
{
try
{
LogMessage($"因更新錯誤，嘗試還原資料庫 {backupDbName}...", true);
await RestoreDatabase(backupDbName);
MessageBox.Show("資料庫更新失敗，已自動還原至更新前的狀態。\n請參閱操作記錄了解詳細錯誤資訊。", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
catch (Exception restoreEx)
{
LogMessage($"還原資料庫 {backupDbName} 時發生錯誤: {restoreEx.Message}", true);
MessageBox.Show($"資料庫更新失敗，且還原過程中也發生錯誤。\n請聯繫系統管理員進行手動還原。\n\n更新錯誤: {ex.Message}\n還原錯誤: {restoreEx.Message}", "嚴重錯誤 / Severe Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
else if (overallUpdateSuccess)
{
LogMessage($"更新資料庫作業標記為成功，但後續發生錯誤: {ex.Message}。資料庫可能已更新。", true);
}
else
{
MessageBox.Show($"資料庫更新失敗，且無有效備份可供還原。\n錯誤訊息：{ex.Message}\n請參閱操作記錄。", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
finally
{
SetOperationButtonsState(true);
ResumeTraceDataReportTimer();
_migrationFailedDueToError = false; // Reset flag for next operation
}
}

// 定義一個結構來返回備份結果和可能的失敗原因
private class BackupResult
{
public string DatabaseName { get; set; } // 成功時為備份資料庫名稱，失敗時為null
public string ErrorMessage { get; set; } // 失敗原因，成功時為null

public static BackupResult Success(string dbName)
{
return new BackupResult { DatabaseName = dbName };
}

public static BackupResult Failure(string message)
{
return new BackupResult { ErrorMessage = message };
}
}

private async Task<BackupResult> BackupDatabase()
{
string backupDbName = null;
bool singleUserModeEnabled = false;
string backupFilePath = null;

try
{
LogMessage("開始備份資料庫...");

// 生成備份資料庫名稱和備份檔案路徑
backupDbName = "BC_Dev_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");

using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync();

// 檢查資料庫是否存在
using (var command = new SqlCommand(@"
SELECT database_id
FROM sys.databases
WHERE name = 'BC_Dev'", connection))
{
var result = await command.ExecuteScalarAsync();
if (result == null)
{
// 返回具體的失敗原因，不拋出例外
return BackupResult.Failure("找不到 BC_Dev 資料庫，無法執行備份");
}
}

// 取得資料檔案所在目錄，用於存放備份檔案
string backupDir = string.Empty;
using (var command = new SqlCommand(@"
SELECT
LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
FROM sys.master_files
WHERE database_id = DB_ID('BC_Dev')
AND type_desc = 'ROWS'", connection))
{
backupDir = (string)await command.ExecuteScalarAsync();
}

// 確保備份目錄存在
if (!Directory.Exists(backupDir))
{
LogMessage($"備份目錄 {backupDir} 不存在，嘗試使用系統臨時目錄");
backupDir = IOPath.GetTempPath();
}

// 生成備份檔案名稱
backupFilePath = IOPath.Combine(backupDir, $"{backupDbName}.bak");
LogMessage($"備份檔案路徑: {backupFilePath}");

// 執行備份
try
{
LogMessage("執行資料庫備份...");
string backupCommand = $@"
BACKUP DATABASE [BC_Dev]
TO DISK = '{backupFilePath}'
WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10";

using (var command = new SqlCommand(backupCommand, connection))
{
command.CommandTimeout = 300; // 5分鐘超時
await command.ExecuteNonQueryAsync();
}
LogMessage("資料庫備份檔案建立成功");

// 還原為新名稱
LogMessage($"開始還原為新資料庫: {backupDbName}");

// 取得資料庫檔案路徑
string dataDir = string.Empty;
string logDir = string.Empty;
using (var command = new SqlCommand(@"
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath", connection))
{
using (var reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
dataDir = reader["DefaultDataPath"] as string ?? string.Empty;
logDir = reader["DefaultLogPath"] as string ?? string.Empty;
}
}
}

// 如果無法獲取SQL Server默認目錄，嘗試從現有數據文件路徑獲取
if (string.IsNullOrEmpty(dataDir) || string.IsNullOrEmpty(logDir))
{
using (var command = new SqlCommand(@"
SELECT
LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1) AS DirectoryPath,
type_desc
FROM sys.master_files
WHERE database_id = DB_ID('BC_Dev')", connection))
{
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string dirPath = reader["DirectoryPath"].ToString();
string typeDesc = reader["type_desc"].ToString();

if (typeDesc == "ROWS" && string.IsNullOrEmpty(dataDir))
dataDir = dirPath;
else if (typeDesc == "LOG" && string.IsNullOrEmpty(logDir))
logDir = dirPath;
}
}
}
}

// 仍然無法獲取，使用備份目錄
if (string.IsNullOrEmpty(dataDir)) dataDir = backupDir;
if (string.IsNullOrEmpty(logDir)) logDir = dataDir;

string newDataFile = IOPath.Combine(dataDir, $"{backupDbName}.mdf");
string newLogFile = IOPath.Combine(logDir, $"{backupDbName}_log.ldf");

LogMessage($"新資料檔案路徑: {newDataFile}");
LogMessage($"新日誌檔案路徑: {newLogFile}");

// 檢查並刪除可能存在的檔案
if (IOFile.Exists(newDataFile))
{
LogMessage($"發現存在的資料檔案，嘗試刪除: {newDataFile}");
try { IOFile.Delete(newDataFile); }
catch (Exception ex) { LogMessage($"刪除舊資料檔案失敗: {ex.Message}", true); }
}
if (IOFile.Exists(newLogFile))
{
LogMessage($"發現存在的日誌檔案，嘗試刪除: {newLogFile}");
try { IOFile.Delete(newLogFile); }
catch (Exception ex) { LogMessage($"刪除舊日誌檔案失敗: {ex.Message}", true); }
}

// 執行還原
string restoreCommand = $@"
RESTORE DATABASE [{backupDbName}]
FROM DISK = '{backupFilePath}'
WITH
MOVE 'BC_Dev' TO '{newDataFile}',
MOVE 'BC_Dev_log' TO '{newLogFile}',
RECOVERY, REPLACE, STATS = 10";

using (var command = new SqlCommand(restoreCommand, connection))
{
command.CommandTimeout = 300; // 5分鐘超時
await command.ExecuteNonQueryAsync();
}

LogMessage("資料庫還原為新名稱完成");

// 確認資料庫狀態
string checkCommand = $@"
SELECT state_desc FROM sys.databases
WHERE name = '{backupDbName}'";

using (var command = new SqlCommand(checkCommand, connection))
{
string state = (string)await command.ExecuteScalarAsync();
LogMessage($"新資料庫狀態: {state}");

if (state != "ONLINE")
{
using (var setOnlineCmd = new SqlCommand(
$"ALTER DATABASE [{backupDbName}] SET ONLINE", connection))
{
await setOnlineCmd.ExecuteNonQueryAsync();
LogMessage("已將新資料庫設置為線上狀態");
}
}
}

// 清理備份檔案
try
{
IOFile.Delete(backupFilePath);
LogMessage("已刪除臨時備份檔案");
}
catch (Exception ex)
{
LogMessage($"無法刪除臨時備份檔案: {ex.Message}", true);
LogMessage($"請手動刪除檔案: {backupFilePath}", true);
}
}
catch (Exception ex)
{
LogMessage($"執行備份還原操作失敗: {ex.Message}", true);

// 清理資源
try
{
if (IOFile.Exists(backupFilePath))
{
IOFile.Delete(backupFilePath);
LogMessage("已刪除臨時備份檔案");
}
}
catch { }

throw;
}

LogMessage("資料庫備份完成");
return BackupResult.Success(backupDbName);
}
}
catch (Exception ex)
{
LogMessage($"備份資料庫失敗: {ex.Message}", true);

// 清理任何剩餘的資源
if (backupFilePath != null && IOFile.Exists(backupFilePath))
{
try
{
IOFile.Delete(backupFilePath);
LogMessage("已清理備份檔案");
}
catch { }
}

throw;
}
}



private async Task RestoreDatabase(string backupDbName)
{
string backupFilePath = null;

try
{
LogMessage($"開始還原資料庫 {backupDbName} 到 BC_Dev...");

// 在 UI 執行緒上更新連線字串
//await Dispatcher.InvokeAsync(() => UpdateConnectionStrings());
UpdateConnectionStrings();
using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync();

// 1. 檢查來源資料庫是否存在
bool sourceExists = false;
using (var command = new SqlCommand($"SELECT 1 FROM sys.databases WHERE name = '{backupDbName}'", connection))
{
var result = await command.ExecuteScalarAsync();
sourceExists = result != null;
}

if (!sourceExists)
{
throw new Exception($"找不到來源資料庫 {backupDbName}");
}

// 2. 檢查並刪除目標資料庫
bool targetExists = false;
using (var command = new SqlCommand("SELECT 1 FROM sys.databases WHERE name = 'BC_Dev'", connection))
{
var result = await command.ExecuteScalarAsync();
targetExists = result != null;
}

if (targetExists)
{
LogMessage("找到現有的 BC_Dev 資料庫，準備刪除...");
try
{
// 先確保所有連線都斷開
using (var command = new SqlCommand(@"
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'BC_Dev')
BEGIN
ALTER DATABASE [BC_Dev] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [BC_Dev];
END", connection))
{
command.CommandTimeout = 180; // 設定3分鐘超時
await command.ExecuteNonQueryAsync();
LogMessage("已刪除現有的 BC_Dev 資料庫");
}
}
catch (Exception ex)
{
throw new Exception($"無法刪除現有的 BC_Dev 資料庫: {ex.Message}");
}
}

// 3. 獲取資料庫檔案路徑
string dataDir = null;

// 嘗試獲取 SQL Server 資料目錄
using (var command = new SqlCommand("SELECT SERVERPROPERTY('InstanceDefaultDataPath')", connection))
{
var result = await command.ExecuteScalarAsync();
dataDir = result?.ToString();
}

// 如果無法獲取預設路徑，使用備份資料庫的檔案路徑
if (string.IsNullOrEmpty(dataDir))
{
using (var command = new SqlCommand($@"
SELECT TOP 1
LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
FROM sys.master_files
WHERE database_id = DB_ID('{backupDbName}')
AND type_desc = 'ROWS'", connection))
{
var result = await command.ExecuteScalarAsync();
dataDir = result?.ToString();
}
}

// 如果仍然無法獲取，使用臨時目錄
if (string.IsNullOrEmpty(dataDir))
{
dataDir = IOPath.GetTempPath();
LogMessage($"無法獲取資料庫目錄，使用臨時目錄: {dataDir}");
}

// 4. 創建臨時備份檔案路徑
backupFilePath = IOPath.Combine(dataDir, $"BC_Dev_Restore_{DateTime.Now.ToString("yyyyMMddHHmmss")}.bak");
LogMessage($"備份檔案路徑: {backupFilePath}");

// 5. 備份來源資料庫
LogMessage($"備份資料庫 {backupDbName} 到檔案...");
using (var command = new SqlCommand($@"
BACKUP DATABASE [{backupDbName}]
TO DISK = '{backupFilePath}'
WITH FORMAT, INIT, STATS = 10", connection))
{
command.CommandTimeout = 300; // 5分鐘超時
await command.ExecuteNonQueryAsync();
}
LogMessage("資料庫備份完成");

// 6. 從備份檔還原為 BC_Dev
LogMessage("從備份檔還原為 BC_Dev...");

// 獲取備份文件中的邏輯名稱
string dataLogicalName = null;
string logLogicalName = null;
string newMdfPath = IOPath.Combine(dataDir, "BC_Dev.mdf");
string newLdfPath = IOPath.Combine(dataDir, "BC_Dev_log.ldf");

using (var command = new SqlCommand($"RESTORE FILELISTONLY FROM DISK = '{backupFilePath}'", connection))
{
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string logicalName = reader["LogicalName"].ToString();
string type = reader["Type"].ToString();

if (type == "D" || type == "0")
{
dataLogicalName = logicalName;
}
else if (type == "L" || type == "1")
{
logLogicalName = logicalName;
}
}
}
}

if (string.IsNullOrEmpty(dataLogicalName) || string.IsNullOrEmpty(logLogicalName))
{
throw new Exception("無法從備份檔中獲取邏輯檔案名稱");
}

// 檢查並刪除可能存在的檔案
if (IOFile.Exists(newMdfPath))
{
try { IOFile.Delete(newMdfPath); } catch { /* 忽略錯誤 */ }
}

if (IOFile.Exists(newLdfPath))
{
try { IOFile.Delete(newLdfPath); } catch { /* 忽略錯誤 */ }
}

// 還原資料庫，並確保在還原過程中的獨佔存取
string restoreCmd = $@"
-- 如果資料庫存在，先設定為單一使用者模式
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'BC_Dev')
BEGIN
ALTER DATABASE [BC_Dev] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END;

-- 執行還原
RESTORE DATABASE [BC_Dev]
FROM DISK = '{backupFilePath}'
WITH MOVE '{dataLogicalName}' TO '{newMdfPath}',
MOVE '{logLogicalName}' TO '{newLdfPath}',
STATS = 10, REPLACE;

-- 還原完成後設定為多使用者模式
ALTER DATABASE [BC_Dev] SET MULTI_USER;";

using (var command = new SqlCommand(restoreCmd, connection))
{
command.CommandTimeout = 1800; // 30分鐘超時，確保有足夠時間完成還原
await command.ExecuteNonQueryAsync();
}

LogMessage("資料庫還原成功");

// 清理臨時檔案
try
{
if (IOFile.Exists(backupFilePath))
{
IOFile.Delete(backupFilePath);
LogMessage("已刪除臨時備份檔案");
}
}
catch (Exception ex)
{
LogMessage($"警告：無法刪除臨時備份檔案: {ex.Message}", true);
}
}
}
catch (Exception ex)
{
LogMessage($"還原資料庫時發生錯誤: {ex.Message}", true);
LogMessage($"還原資料庫時發生錯誤，詳細資訊: {ex.StackTrace}", true);

// 清理臨時檔案
try
{
if (!string.IsNullOrEmpty(backupFilePath) && IOFile.Exists(backupFilePath))
{
IOFile.Delete(backupFilePath);
LogMessage("已清理臨時備份檔案");
}
}
catch (Exception cleanupEx)
{
LogMessage($"清理臨時檔案時發生錯誤: {cleanupEx.Message}", true);
}

// 重新拋出例外，確保呼叫端知道操作失敗
throw;
}

}
private async Task<bool> MigrateData(IProgress<string> progress = null, CancellationToken cancellationToken = default)
{
_migrationFailedDueToError = false; // Reset the flag at the beginning
List<string> tablesForMigrationLoop = new List<string>(); // Define tables list here for broader scope

try
{
LogMessage("開始資料遷移...");
progress?.Report("Starting data migration...");

if (!await WaitForDatabaseConnection(_connectionString, "BC_Dev", 30, 1000, 10000))
{
LogMessage("無法連接到資料庫，取消資料遷移", true);
progress?.Report("Cannot connect to the database. Cancelling data migration.");
_migrationFailedDueToError = true;
return false;
}

using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync(cancellationToken);

string backupDbName = "";
using (var command = new SqlCommand(@"
SELECT name
FROM sys.databases
WHERE name LIKE 'BC_Dev_%'
AND name != 'BC_Dev'
ORDER BY create_date DESC", connection))
{
var result = await command.ExecuteScalarAsync(cancellationToken);
if (result != null && result != DBNull.Value)
{
backupDbName = result.ToString();
}
}

if (string.IsNullOrEmpty(backupDbName))
{
LogMessage("找不到備份資料庫", true);
progress?.Report("Backup database not found.");
_migrationFailedDueToError = true;
return false;
}

LogMessage($"找到備份資料庫: {backupDbName}");
progress?.Report($"Found backup database: {backupDbName}");

cancellationToken.ThrowIfCancellationRequested();

var columnLengthIssues = new List<(string TableName, string ColumnName, int OldLength, int NewLength, string SampleValue)>();
var incompatibleColumnTypes = new List<(string TableName, string ColumnName, string OldType, string NewType)>();
var notNullIncompatibleColumns = new List<(string TableName, string ColumnName, string ColumnType, string DefaultValue)>();
var textTypeColumns = new List<(string TableName, string ColumnName, string NewType, bool IsCompatible)>();
var tablesToSkipMigration = new HashSet<string>();

var tableRowCounts = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
var tableColumnInfoCache = new Dictionary<string, Dictionary<string, (string DataType, int MaxLength, bool IsNullable, string ColumnDefault)>>(StringComparer.OrdinalIgnoreCase);
var backupTableColumnInfoCache = new Dictionary<string, Dictionary<string, (string DataType, int MaxLength, bool IsNullable, string ColumnDefault)>>(StringComparer.OrdinalIgnoreCase);

LogMessage("檢查資料欄位類型相容性及長度...");
progress?.Report("Checking data column type compatibility and length...");

using (var checkTransaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
// Populate tablesForMigrationLoop here, within the transaction for schema queries
using (var command = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'", connection, checkTransaction))
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
tablesForMigrationLoop.Add(reader.GetString(0));
}
}
}

LogMessage($"發現 {tablesForMigrationLoop.Count} 個資料表進行檢查。");
progress?.Report($"Found {tablesForMigrationLoop.Count} tables to check.");

LogMessage("預先查詢所有表格的資料筆數...");
progress?.Report("Pre-querying row counts for all tables...");
foreach (var table in tablesForMigrationLoop) // Use the scoped list
{
if (table.Equals("VERSION", StringComparison.OrdinalIgnoreCase)) continue;
try
{
using (var command = new SqlCommand($"SELECT COUNT_BIG(*) FROM [{backupDbName}].[dbo].[{table}] WITH (NOLOCK)", connection, checkTransaction))
{
var countResult = await command.ExecuteScalarAsync(cancellationToken);
tableRowCounts[table] = Convert.ToInt32(countResult);
LogMessage($"表格 {table} (來源) 資料筆數: {tableRowCounts[table]}");
}
}
catch (SqlException ex) when (ex.Number == 208)
{
LogMessage($"警告：來源備份資料庫 {backupDbName} 中找不到表格 {table}。將跳過此表格的欄位檢查和資料遷移。", true);
tableRowCounts[table] = 0;
tablesToSkipMigration.Add(table);
}
catch (Exception ex)
{
LogMessage($"查詢表格 {table} 資料筆數時出錯: {ex.Message}", true);
tableRowCounts[table] = 0;
tablesToSkipMigration.Add(table);
}
}

LogMessage("預先查詢所有有資料表格的欄位資訊 (來源與目標)...");
progress?.Report("Pre-querying column info for tables with data (source and target)...");
foreach (var table in tablesForMigrationLoop) // Use the scoped list
{
if (table.Equals("VERSION", StringComparison.OrdinalIgnoreCase) || tablesToSkipMigration.Contains(table)) continue;
if (!tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0)
{
LogMessage($"表格 {table} 在來源中無資料，跳過欄位資訊查詢。");
continue;
}

var currentTableOldColumnInfo = new Dictionary<string, (string DataType, int MaxLength, bool IsNullable, string ColumnDefault)>(StringComparer.OrdinalIgnoreCase);
try
{
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT,
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM [{backupDbName}].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = 'dbo'", connection, checkTransaction))
{
command.Parameters.AddWithValue("@tableName", table);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
string columnName = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
string dataType = reader.GetString(reader.GetOrdinal("DATA_TYPE"));
bool isNullable = reader.GetString(reader.GetOrdinal("IS_NULLABLE")).Equals("YES", StringComparison.OrdinalIgnoreCase);
string columnDefault = reader.IsDBNull(reader.GetOrdinal("COLUMN_DEFAULT")) ? null : reader.GetString(reader.GetOrdinal("COLUMN_DEFAULT"));
int maxLength = reader.IsDBNull(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")) ? 0 : reader.GetInt32(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"));
if ((dataType.Equals("nvarchar", StringComparison.OrdinalIgnoreCase) || dataType.Equals("varchar", StringComparison.OrdinalIgnoreCase) || dataType.Equals("varbinary", StringComparison.OrdinalIgnoreCase)) && maxLength == -1)
maxLength = int.MaxValue / 2 - 10; // Normalize MAX to a large number for comparison
currentTableOldColumnInfo[columnName] = (dataType, maxLength, isNullable, columnDefault);
}
}
}
backupTableColumnInfoCache[table] = currentTableOldColumnInfo;
}
catch (SqlException ex) when (ex.Number == 208)
{
LogMessage($"警告：查詢來源表格 [{backupDbName}].[dbo].[{table}] 的欄位資訊時發生錯誤 (可能不存在)。跳過此表的欄位獲取。", true);
backupTableColumnInfoCache[table] = new Dictionary<string, (string DataType, int MaxLength, bool IsNullable, string ColumnDefault)>(StringComparer.OrdinalIgnoreCase);
tablesToSkipMigration.Add(table);
continue;
}

var currentTableNewColumnInfo = new Dictionary<string, (string DataType, int MaxLength, bool IsNullable, string ColumnDefault)>(StringComparer.OrdinalIgnoreCase);
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT,
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = 'dbo'", connection, checkTransaction))
{
command.Parameters.AddWithValue("@tableName", table);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
string columnName = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
string dataType = reader.GetString(reader.GetOrdinal("DATA_TYPE"));
bool isNullable = reader.GetString(reader.GetOrdinal("IS_NULLABLE")).Equals("YES", StringComparison.OrdinalIgnoreCase);
string columnDefault = reader.IsDBNull(reader.GetOrdinal("COLUMN_DEFAULT")) ? null : reader.GetString(reader.GetOrdinal("COLUMN_DEFAULT"));
int maxLength = reader.IsDBNull(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")) ? 0 : reader.GetInt32(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"));
if ((dataType.Equals("nvarchar", StringComparison.OrdinalIgnoreCase) || dataType.Equals("varchar", StringComparison.OrdinalIgnoreCase) || dataType.Equals("varbinary", StringComparison.OrdinalIgnoreCase)) && maxLength == -1)
maxLength = int.MaxValue / 2 - 10; // Normalize MAX
currentTableNewColumnInfo[columnName] = (dataType, maxLength, isNullable, columnDefault);
}
}
}
tableColumnInfoCache[table] = currentTableNewColumnInfo;
}

foreach (var table in tablesForMigrationLoop) // Use the scoped list
{
if (table.Equals("VERSION", StringComparison.OrdinalIgnoreCase) || tablesToSkipMigration.Contains(table) ||
!tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0 ||
!backupTableColumnInfoCache.ContainsKey(table) || !tableColumnInfoCache.ContainsKey(table) ||
!backupTableColumnInfoCache[table].Any())
{
continue;
}

LogMessage($"檢查表格 {table} 的欄位定義...");
progress?.Report($"Checking column definitions for table {table}...");

var sourceColumnDetails = backupTableColumnInfoCache[table];
var targetColumnDetails = tableColumnInfoCache[table];

foreach (var targetColKvp in targetColumnDetails)
{
var targetColName = targetColKvp.Key;
var targetColInfo = targetColKvp.Value;
if (!targetColInfo.IsNullable && targetColInfo.ColumnDefault == null)
{
if (!sourceColumnDetails.ContainsKey(targetColName))
{
LogMessage($"錯誤：目標表格 {table} 中的欄位 {targetColName} 設定為 NOT NULL 且無預設值，但在來源表格中不存在。這是資料庫設計錯誤，請修正欄位定義。", true);
var result = MessageBox.Show($"發現資料庫設計錯誤：\n目標表格 [{table}] 新增了必填欄位 [{targetColName}]，但未提供預設值。\n即便是新版新增的欄位，這會造成該筆資料無法寫入，\n如果是在設定參數型的資料，會造成系統無法正常執行\n除了資料是可以捨棄的狀況外，請修正欄位定義再重新更新。\n\n是否跳過此表格 [{table}] 的遷移？\n選擇「否」將中止更新並進行還原。",
"必要欄位遺失警告", MessageBoxButton.YesNo, MessageBoxImage.Warning);
if (result == MessageBoxResult.Yes) { tablesToSkipMigration.Add(table); break; }
else { LogMessage("使用者選擇中止更新。", true); _migrationFailedDueToError = true; checkTransaction.Rollback(); return false; }
}
}
}
if (tablesToSkipMigration.Contains(table)) continue;

var commonColumnNames = sourceColumnDetails.Keys.Intersect(targetColumnDetails.Keys, StringComparer.OrdinalIgnoreCase).ToList();
foreach (var columnName in commonColumnNames)
{
var oldInfo = sourceColumnDetails[columnName];
var newInfo = targetColumnDetails[columnName];

bool isOldTextType = oldInfo.DataType.Equals("text", StringComparison.OrdinalIgnoreCase) || oldInfo.DataType.Equals("ntext", StringComparison.OrdinalIgnoreCase);
if (isOldTextType)
{
bool newTypeCanStoreString = IsTypeCompatibleForText(newInfo.DataType);
textTypeColumns.Add((table, columnName, newInfo.DataType, newTypeCanStoreString));
LogMessage(newTypeCanStoreString ? $"TEXT 類型欄位相容: 表格 {table}, 欄位 {columnName}, 舊: {oldInfo.DataType}, 新: {newInfo.DataType}" : $"TEXT 類型欄位不相容: 表格 {table}, 欄位 {columnName}, 舊: {oldInfo.DataType}, 新: {newInfo.DataType}", !newTypeCanStoreString);
}
// Use the updated IsTypeCompatible with 4 arguments
else if (!IsTypeCompatible(oldInfo.DataType, newInfo.DataType, oldInfo.MaxLength, newInfo.MaxLength))
{
incompatibleColumnTypes.Add((table, columnName, oldInfo.DataType, newInfo.DataType));
LogMessage($"欄位類型不相容: 表格 {table}, 欄位 {columnName}, 舊: {oldInfo.DataType}({oldInfo.MaxLength}), 新: {newInfo.DataType}({newInfo.MaxLength})", true);
if (!newInfo.IsNullable && newInfo.ColumnDefault == null)
{
notNullIncompatibleColumns.Add((table, columnName, newInfo.DataType, newInfo.ColumnDefault));
LogMessage($"警告：欄位 {columnName} 在表格 {table} 類型不相容且設為 NOT NULL 無預設值。", true);
}
}

bool oldIsCharOrText = oldInfo.DataType.IndexOf("char", StringComparison.OrdinalIgnoreCase) >= 0 || oldInfo.DataType.IndexOf("text", StringComparison.OrdinalIgnoreCase) >= 0;
bool newIsCharOrText = newInfo.DataType.IndexOf("char", StringComparison.OrdinalIgnoreCase) >= 0 || newInfo.DataType.IndexOf("text", StringComparison.OrdinalIgnoreCase) >= 0;

// Check for string truncation if new length is smaller (and not MAX type)
if (oldIsCharOrText && newIsCharOrText &&
oldInfo.MaxLength < (int.MaxValue / 2 - 20) && newInfo.MaxLength < (int.MaxValue / 2 - 20) && // Exclude MAX types from direct numeric comparison
oldInfo.MaxLength > newInfo.MaxLength)
{
int affectedRows = 0, currentMaxLengthInSource = 0; string sampleValue = "";
using (var checkCmd = new SqlCommand($"SELECT COUNT_BIG(*), MAX(LEN(CAST([{columnName}] AS NVARCHAR(MAX)))) FROM [{backupDbName}].[dbo].[{table}] WITH (NOLOCK) WHERE LEN(CAST([{columnName}] AS NVARCHAR(MAX))) > {newInfo.MaxLength}", connection, checkTransaction))
using (var reader = await checkCmd.ExecuteReaderAsync(cancellationToken))
{
if (await reader.ReadAsync(cancellationToken) && reader[0] != DBNull.Value)
{
affectedRows = Convert.ToInt32(reader[0]);
if (reader[1] != DBNull.Value) currentMaxLengthInSource = Convert.ToInt32(reader[1]);
}
}
if (affectedRows > 0)
{
using (var sampleCmd = new SqlCommand($"SELECT TOP 1 CAST([{columnName}] AS NVARCHAR(MAX)) FROM [{backupDbName}].[dbo].[{table}] WITH (NOLOCK) WHERE LEN(CAST([{columnName}] AS NVARCHAR(MAX))) = {currentMaxLengthInSource}", connection, checkTransaction))
{

object sampleValueObj = await sampleCmd.ExecuteScalarAsync(cancellationToken);
string rawSampleValue = (sampleValueObj as string) ?? "";
sampleValue = rawSampleValue.Length <= 50 ? rawSampleValue : rawSampleValue.Substring(0, 50);
}
columnLengthIssues.Add((table, columnName, oldInfo.MaxLength, newInfo.MaxLength, sampleValue));
LogMessage($"潛在資料截斷: 表格 {table}, 欄位 {columnName}, 舊長度: {oldInfo.MaxLength}, 新長度: {newInfo.MaxLength}, {affectedRows} 筆資料受影響, 最大長度: {currentMaxLengthInSource}, 範例: '{sampleValue}'", true);
}
}
}
}
checkTransaction.Commit();
LogMessage("欄位定義初步檢查完成。");
progress?.Report("Initial column definition check completed.");
}
catch (OperationCanceledException) { LogMessage("欄位定義檢查操作已取消。", true); try { checkTransaction?.Rollback(); } catch (Exception rbEx) { LogMessage($"欄位檢查取消時回滾失敗: {rbEx.Message}", true); } throw; }
catch (Exception ex) { LogMessage($"檢查欄位定義時發生嚴重錯誤: {ex.Message}{Environment.NewLine}StackTrace: {ex.StackTrace}", true); try { checkTransaction?.Rollback(); } catch (Exception rbEx) { LogMessage($"欄位檢查錯誤後回滾失敗: {rbEx.Message}", true); } _migrationFailedDueToError = true; return false; }
}

if (columnLengthIssues.Any() || incompatibleColumnTypes.Any() || notNullIncompatibleColumns.Any())
{
string issueSummary = "資料遷移檢查發現以下潛在問題：\n";
if (columnLengthIssues.Any()) issueSummary += $"\n- {columnLengthIssues.Count} 個欄位可能發生資料截斷。";
if (incompatibleColumnTypes.Any()) issueSummary += $"\n- {incompatibleColumnTypes.Count} 個欄位類型不相容。";
if (notNullIncompatibleColumns.Any()) issueSummary += $"\n- {notNullIncompatibleColumns.Count} 個類型不相容的欄位被設為 NOT NULL 且無預設值。";
issueSummary += "\n\n是否繼續遷移？選擇「是」將嘗試遷移資料（可能會有資料損失或錯誤），選擇「否」將中止更新過程。";
LogMessage(issueSummary.Replace("\n", Environment.NewLine), true);
if (MessageBox.Show(issueSummary.Replace("\\n", Environment.NewLine), "遷移警告", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
{ LogMessage("使用者選擇因潛在問題中止遷移。", true); return false; } // User abort, not an internal error
LogMessage("使用者選擇繼續遷移，忽略警告。");
}

LogMessage("開始遷移資料表...");
progress?.Report("Starting table data migration...");
using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
foreach (var table in tablesForMigrationLoop) // Use the scoped list
{
cancellationToken.ThrowIfCancellationRequested();
if (table.Equals("VERSION", StringComparison.OrdinalIgnoreCase) || tablesToSkipMigration.Contains(table) || !tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0)
{
if (!tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0 && !tablesToSkipMigration.Contains(table) && !table.Equals("VERSION", StringComparison.OrdinalIgnoreCase))
LogMessage($"表格 {table} 來源無資料，跳過遷移。");
else if (tablesToSkipMigration.Contains(table))
LogMessage($"跳過表格 {table} 的遷移（使用者選擇或預先檢查跳過）。");
progress?.Report($"Skipping table {table} (no data or pre-check skip).");
continue;
}
LogMessage($"準備遷移表格: {table}, {tableRowCounts[table]} 筆資料。");
progress?.Report($"Preparing to migrate table: {table}, {tableRowCounts[table]} rows.");

var sourceSchema = backupTableColumnInfoCache[table]; // Already checked for key existence
var currentTargetSchema = tableColumnInfoCache[table]; // Corrected variable name

var columnsToInclude = sourceSchema.Keys.Intersect(currentTargetSchema.Keys, StringComparer.OrdinalIgnoreCase)
.Where(cn => !textTypeColumns.Any(ttc => ttc.TableName.Equals(table, StringComparison.OrdinalIgnoreCase) && ttc.ColumnName.Equals(cn, StringComparison.OrdinalIgnoreCase) && !ttc.IsCompatible))
.ToList();
var columnsToInsertInTarget = new List<string>();
var selectClausesForSource = new List<string>();

foreach (var commonColName in columnsToInclude)
{
var sourceInfo = sourceSchema[commonColName];
var targetInfo = currentTargetSchema[commonColName]; // Use corrected variable

// Use the updated IsTypeCompatible with 4 arguments
if (!IsTypeCompatible(sourceInfo.DataType, targetInfo.DataType, sourceInfo.MaxLength, targetInfo.MaxLength) &&
!textTypeColumns.Any(t => t.TableName.Equals(table, StringComparison.OrdinalIgnoreCase) && t.ColumnName.Equals(commonColName, StringComparison.OrdinalIgnoreCase) && t.IsCompatible))
{
LogMessage($"遷移時再次確認欄位 {table}.{commonColName} 類型不相容 ({sourceInfo.DataType} -> {targetInfo.DataType})，此欄位將不被包含在 INSERT 中。", true);
continue;
}
columnsToInsertInTarget.Add($"[{commonColName}]");
string selectClause = $"S.[{commonColName}]"; // Alias source table as S

bool sourceIsCharOrText = sourceInfo.DataType.IndexOf("char", StringComparison.OrdinalIgnoreCase) >= 0 || sourceInfo.DataType.IndexOf("text", StringComparison.OrdinalIgnoreCase) >= 0;
bool targetIsCharOrText = targetInfo.DataType.IndexOf("char", StringComparison.OrdinalIgnoreCase) >= 0 || targetInfo.DataType.IndexOf("text", StringComparison.OrdinalIgnoreCase) >= 0;

if (sourceIsCharOrText && targetIsCharOrText &&
sourceInfo.MaxLength < (int.MaxValue / 2 - 20) && targetInfo.MaxLength < (int.MaxValue / 2 - 20) &&
sourceInfo.MaxLength > targetInfo.MaxLength)
{
selectClause = $"LEFT(S.[{commonColName}], {targetInfo.MaxLength})";
}
else if ((sourceInfo.DataType.Equals("text", StringComparison.OrdinalIgnoreCase) || sourceInfo.DataType.Equals("ntext", StringComparison.OrdinalIgnoreCase)) &&
textTypeColumns.Any(t => t.TableName.Equals(table, StringComparison.OrdinalIgnoreCase) && t.ColumnName.Equals(commonColName, StringComparison.OrdinalIgnoreCase) && t.IsCompatible))
{
string targetTypeForCast = targetInfo.DataType;
if (targetInfo.DataType.IndexOf("varchar", StringComparison.OrdinalIgnoreCase) >= 0 && (targetInfo.MaxLength >= (int.MaxValue / 2 - 20) || targetInfo.MaxLength == -1))
targetTypeForCast += "(MAX)";
else if (targetInfo.DataType.IndexOf("varchar", StringComparison.OrdinalIgnoreCase) >= 0) // Non-MAX varchar
targetTypeForCast += $"({targetInfo.MaxLength})";
// For text/ntext target, no length needed in CAST
selectClause = $"CAST(S.[{commonColName}] AS {targetTypeForCast})";
}
selectClausesForSource.Add(selectClause + $" AS [{commonColName}]");
}

if (!columnsToInsertInTarget.Any()) { LogMessage($"表格 {table} 沒有可遷移的共同且相容的欄位，跳過。"); progress?.Report($"Table {table} has no common compatible columns, skipping."); continue; }

foreach (var targetColNameToInsert in columnsToInsertInTarget.Select(c => c.Trim('[', ']')))
{
var targetColInfo = currentTargetSchema[targetColNameToInsert]; // Use corrected variable
if (!targetColInfo.IsNullable && targetColInfo.ColumnDefault == null && sourceSchema.ContainsKey(targetColNameToInsert))
{
int nullCountInSource = 0;
using (var cmdNullCheck = new SqlCommand($"SELECT COUNT_BIG(*) FROM [{backupDbName}].[dbo].[{table}] WITH (NOLOCK) WHERE [{targetColNameToInsert}] IS NULL", connection, transaction))
{
var res = await cmdNullCheck.ExecuteScalarAsync(cancellationToken);
if (res != DBNull.Value) nullCountInSource = Convert.ToInt32(res);
}
if (nullCountInSource > 0)
{
LogMessage($"錯誤：資料庫設計錯誤 - 表格 [{table}] 的欄位 [{targetColNameToInsert}] 在新版本被設定為必填(NOT NULL)且無預設值，但舊版本資料中有 {nullCountInSource} 筆包含 NULL 值。", true);
if (MessageBox.Show($"發現資料庫設計錯誤：\n\n表格 [{table}] 的欄位 [{targetColNameToInsert}] 在新版本被設定為必填(NOT NULL)且無預設值，但舊版本資料中有 {nullCountInSource} 筆包含 NULL 值。\n\n此欄位設計不相容於舊版本，請修正欄位定義再重新更新。\n\n警告：即使選擇跳過此表格繼續執行，系統仍可能因資料庫結構不一致而無法正常運作。\n\n是否跳過此表格 [{table}] 的資料遷移？\n選擇「是」以跳過此表格。\n選擇「否」以中止整個資料庫更新過程。",
"資料庫設計錯誤", MessageBoxButton.YesNo, MessageBoxImage.Error) == MessageBoxResult.Yes)
{ tablesToSkipMigration.Add(table); goto NextTableLoop; }
else { LogMessage("使用者選擇因 NULL 值衝突中止更新。", true); _migrationFailedDueToError = true; transaction.Rollback(); return false; }
}
}
}

string insertColumnsSql = string.Join(", ", columnsToInsertInTarget);
string selectColumnsSql = string.Join(", ", selectClausesForSource);
bool hasIdentity = Convert.ToInt32(await new SqlCommand($"SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[{table}]'), 'TableHasIdentity')", connection, transaction).ExecuteScalarAsync(cancellationToken)) == 1;
if (hasIdentity) await new SqlCommand($"SET IDENTITY_INSERT [dbo].[{table}] ON", connection, transaction).ExecuteNonQueryAsync(cancellationToken);

// Alias source table as S in the FROM clause
string migrationSql = $"INSERT INTO [dbo].[{table}] ({insertColumnsSql}) SELECT {selectColumnsSql} FROM [{backupDbName}].[dbo].[{table}] S";
LogMessage($"執行遷移 SQL: {migrationSql}");
using (var migrateCmd = new SqlCommand(migrationSql, connection, transaction))
{
migrateCmd.CommandTimeout = 600;
int rowsAffected = await migrateCmd.ExecuteNonQueryAsync(cancellationToken);
LogMessage($"表格 {table} 資料遷移完成，影響 {rowsAffected} 筆。");
progress?.Report($"Table {table} migrated, {rowsAffected} rows affected.");
}
if (hasIdentity) await new SqlCommand($"SET IDENTITY_INSERT [dbo].[{table}] OFF", connection, transaction).ExecuteNonQueryAsync(cancellationToken);
NextTableLoop:;
}

if (textTypeColumns.Any(t => t.IsCompatible)) // This separate TEXT/NTEXT update loop might be redundant if CAST in main loop handles it
{
LogMessage("開始遷移/更新相容的 TEXT/NTEXT 類型欄位資料 (批次)...");
progress?.Report("Updating compatible TEXT/NTEXT column data (batched)...");
foreach (var textColInfo in textTypeColumns.Where(t => t.IsCompatible))
{
if (tablesToSkipMigration.Contains(textColInfo.TableName) || !tableRowCounts.ContainsKey(textColInfo.TableName) || tableRowCounts[textColInfo.TableName] == 0) continue;
cancellationToken.ThrowIfCancellationRequested();
LogMessage($"處理表格 {textColInfo.TableName} 的 TEXT/NTEXT 欄位 {textColInfo.ColumnName} (遷移到 {textColInfo.NewType})");

string pkColumnName = "";
using (SqlCommand pkCmd = new SqlCommand($"SELECT KU.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND KU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KU.TABLE_NAME = TC.TABLE_NAME WHERE TC.TABLE_NAME = '{textColInfo.TableName}' AND TC.TABLE_SCHEMA = 'dbo' ORDER BY KU.ORDINAL_POSITION", connection, transaction))
pkColumnName = await pkCmd.ExecuteScalarAsync(cancellationToken) as string;

if (string.IsNullOrEmpty(pkColumnName)) { LogMessage($"警告：表格 {textColInfo.TableName} 找不到主鍵，無法批次更新 TEXT 欄位 {textColInfo.ColumnName}。此欄位資料可能未透過主 INSERT 遷移。", true); continue; }

var currentTargetSchemaForText = tableColumnInfoCache[textColInfo.TableName]; // Get target schema for length
var targetColForText = currentTargetSchemaForText[textColInfo.ColumnName];
string targetTypeWithLength = targetColForText.DataType;
if (targetTypeWithLength.IndexOf("varchar", StringComparison.OrdinalIgnoreCase) >= 0)
{
targetTypeWithLength += (targetColForText.MaxLength >= (int.MaxValue / 2 - 20) || targetColForText.MaxLength == -1) ? "(MAX)" : $"({targetColForText.MaxLength})";
}
// For text/ntext itself, no length in cast

int batchSize = 100, offset = 0; bool moreRecords = true;
while (moreRecords)
{
cancellationToken.ThrowIfCancellationRequested();
string updateSql = $@"
UPDATE T SET T.[{textColInfo.ColumnName}] = CAST(S.[{textColInfo.ColumnName}] AS {targetTypeWithLength})
FROM [dbo].[{textColInfo.TableName}] T INNER JOIN (
SELECT [{pkColumnName}], [{textColInfo.ColumnName}] FROM [{backupDbName}].[dbo].[{textColInfo.TableName}]
ORDER BY [{pkColumnName}] OFFSET {offset} ROWS FETCH NEXT {batchSize} ROWS ONLY
) S ON T.[{pkColumnName}] = S.[{pkColumnName}] WHERE S.[{textColInfo.ColumnName}] IS NOT NULL;";
using (var textUpdateCmd = new SqlCommand(updateSql, connection, transaction))
{
textUpdateCmd.CommandTimeout = 300;
int rows = await textUpdateCmd.ExecuteNonQueryAsync(cancellationToken);
LogMessage($"TEXT/NTEXT 欄位 {textColInfo.TableName}.{textColInfo.ColumnName} 更新了 {rows} 筆資料 (批次 {offset / batchSize + 1})。");
if (rows < batchSize) moreRecords = false;
offset += batchSize;
}
progress?.Report($"Table {textColInfo.TableName}.{textColInfo.ColumnName} TEXT data batch {offset / batchSize} migrated.");
}
}
}
transaction.Commit();
LogMessage("所有表格資料遷移完成。");
progress?.Report("All table data migration completed.");
return true;
}
catch (OperationCanceledException) { LogMessage("資料遷移操作已取消。", true); progress?.Report("Data migration operation cancelled."); try { transaction?.Rollback(); } catch (Exception rbEx) { LogMessage($"取消時回滾失敗: {rbEx.Message}", true); } throw; }
catch (Exception ex) { LogMessage($"資料遷移時發生錯誤: {ex.Message}{Environment.NewLine}SQL: {(ex is SqlException sqlEx ? sqlEx.ToString() : string.Empty)}{Environment.NewLine}StackTrace: {ex.StackTrace}", true); progress?.Report($"Error during data migration: {ex.Message}"); try { transaction?.Rollback(); } catch (Exception rbEx) { LogMessage($"錯誤後回滾失敗: {rbEx.Message}", true); } _migrationFailedDueToError = true; return false; }
}
}
}
catch (OperationCanceledException opEx) { LogMessage($"資料遷移任務被使用者或程式邏輯取消: {opEx.Message}", true); progress?.Report("Data migration task was cancelled."); throw; }
catch (Exception ex) { LogMessage($"資料遷移時發生未預期錯誤 (外層): {ex.Message}{Environment.NewLine}StackTrace: {ex.StackTrace}", true); progress?.Report($"Data migration failed due to an unexpected error (outer): {ex.Message}"); _migrationFailedDueToError = true; return false; }
}

// Helper function to determine if a target type can store text from a source TEXT column
private bool IsTypeCompatibleForText(string newType)
{
string nType = newType.ToLower();
return nType.Contains("char") || nType.Contains("text") || nType.Contains("xml") || nType.Contains("json");
}
// Helper function (add this within your class if it doesn't exist)
private bool IsTypeCompatible(string oldType, string newType, int oldLength = 0, int newLength = 0)
{
oldType = oldType.ToLower();
newType = newType.ToLower();

if (oldType == newType)
{
// If types are the same, check length for char types (excluding MAX which is represented by large int or -1)
bool oldIsChar = oldType.Contains("char");
bool oldIsMax = oldLength <= 0 || oldLength >= (int.MaxValue / 2 - 20); // Approximation for MAX
bool newIsMax = newLength <= 0 || newLength >= (int.MaxValue / 2 - 20);

if (oldIsChar && !oldIsMax && !newIsMax && oldLength > newLength)
{
//LogMessage($"類型 {oldType} 相同但長度縮短從 {oldLength} 到 {newLength}，視為不完全相容（可能截斷）。");
return false; // Strict: if length shrinks, consider incompatible for direct migration without truncation
}
return true;
}

// Allow numeric to numeric (int, smallint, bigint, tinyint, decimal, numeric, float, real, money, smallmoney)
List<string> numericTypes = new List<string> { "int", "smallint", "bigint", "tinyint", "decimal", "numeric", "float", "real", "money", "smallmoney" };
if (numericTypes.Contains(oldType) && numericTypes.Contains(newType)) return true;

// Allow char types to char types (char, varchar, nchar, nvarchar)
// TEXT to char types is handled by IsTypeCompatibleForText or specific logic
List<string> charTypes = new List<string> { "char", "varchar", "nchar", "nvarchar" };
if (charTypes.Contains(oldType) && charTypes.Contains(newType))
{
// If converting between char types, and new length is smaller (and not MAX), it's a potential issue handled by truncation logic, but base types are compatible.
// bool oldIsMax = oldLength <= 0 || oldLength >= (int.MaxValue / 2 - 20);
// bool newIsMax = newLength <= 0 || newLength >= (int.MaxValue / 2 - 20);
// if (!oldIsMax && !newIsMax && oldLength > newLength) return false; // Already handled by truncation logic, so here it's type compatible
return true;
}

// Allow TEXT to NTEXT and vice-versa, and TEXT/NTEXT to compatible char(max) types
if ((oldType == "text" || oldType == "ntext") && (newType == "text" || newType == "ntext")) return true;
if ((oldType == "text" || oldType == "ntext") && (newType.Contains("varchar") || newType.Contains("nvarchar")) && (newLength >= (int.MaxValue / 2 - 20) || newLength == -1)) return true; // To varchar(max)/nvarchar(max)


// Allow datetime to datetime (datetime, smalldatetime, date, time, datetime2, datetimeoffset)
List<string> dateTimeTypes = new List<string> { "datetime", "smalldatetime", "date", "time", "datetime2", "datetimeoffset" };
if (dateTimeTypes.Contains(oldType) && dateTimeTypes.Contains(newType)) return true;

if (oldType == "bit" && newType == "bit") return true;
if (oldType == "uniqueidentifier" && newType == "uniqueidentifier") return true;
if (oldType == "timestamp" && newType == "binary" && newLength == 8) return true; // timestamp is rowversion (binary(8))
if (oldType == "rowversion" && newType == "binary" && newLength == 8) return true;


// Add more specific compatibility rules if needed
// For example, int to varchar might be acceptable in some scenarios (but needs explicit cast and is handled by migration logic, not here)
// For now, keep it relatively strict for automatic migration check.

LogMessage($"類型不相容判斷: 舊類型 '{oldType}' (長度 {oldLength}) 到 新類型 '{newType}' (長度 {newLength}) 被認為不相容。", true);
return false;
}

// Helper to check for numeric types for PK batching
private bool IsNumericType(string dataType)
{
string dt = dataType.ToLower();
return dt.Contains("int") || dt.Contains("decimal") || dt.Contains("numeric") || dt.Contains("float") || dt.Contains("real") || dt.Contains("money");
}

private async Task<bool> MigrateData_backup(IProgress<string> progress = null, CancellationToken cancellationToken = default)
{
try
{
LogMessage("開始資料遷移...");

// 檢查資料庫連線狀態
if (!await WaitForDatabaseConnection(_connectionString, "BC_Dev", 30, 1000, 10000))
{
LogMessage("無法連接到資料庫，取消資料遷移", true);
return false;
}

using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();

// 取得備份資料庫名稱
string backupDbName = "";
using (var command = new SqlCommand(@"
SELECT name
FROM sys.databases
WHERE name LIKE 'BC_Dev_%'
AND name != 'BC_Dev'
ORDER BY create_date DESC", connection))
{
backupDbName = (string)command.ExecuteScalar();
}

if (string.IsNullOrEmpty(backupDbName))
{
throw new Exception("找不到備份資料庫");
}

LogMessage($"找到備份資料庫: {backupDbName}");
// 移除重複的進度報告
// progress?.Report($"找到備份資料庫: {backupDbName}");

// 檢查是否請求取消
cancellationToken.ThrowIfCancellationRequested();

// 定義一個結構來存儲欄位長度資訊
var columnLengthIssues = new List<(string TableName, string ColumnName, int OldLength, int NewLength, string SampleValue)>();

// 定義一個結構來存儲欄位類型不相容的資訊
var incompatibleColumnTypes = new List<(string TableName, string ColumnName, string OldType, string NewType)>();

// 定義一個結構來存儲不允許NULL且不相容的欄位資訊
var notNullIncompatibleColumns = new List<(string TableName, string ColumnName, string ColumnType, string DefaultValue)>();

// 定義一個結構來存儲TEXT類型欄位的資訊
var textTypeColumns = new List<(string TableName, string ColumnName, string NewType, bool IsCompatible)>();

// 預先緩存所有表格的資料筆數
var tableRowCounts = new Dictionary<string, int>();

// 預先緩存所有表格的欄位資訊
var tableColumnsCache = new Dictionary<string, List<string>>();
var tableColumnInfoCache = new Dictionary<string, Dictionary<string, (string DataType, int MaxLength)>>();
var backupTableColumnsCache = new Dictionary<string, List<string>>();
var backupTableColumnInfoCache = new Dictionary<string, Dictionary<string, (string DataType, int MaxLength)>>();

// 在遷移之前檢查欄位類型相容性和長度問題
LogMessage("檢查資料欄位類型相容性及長度...");
using (var checkTransaction = connection.BeginTransaction())
{
try
{
// 取得所有表格名稱
var tables = new List<string>();
using (var command = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connection, checkTransaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
}
}

// 取得所有表格的 NOT NULL 欄位資訊
var notNullColumnsInfo = new Dictionary<string, HashSet<string>>();
var columnDefaultValues = new Dictionary<string, Dictionary<string, string>>();

LogMessage("檢查資料表的 NOT NULL 欄位...");
foreach (var table in tables)
{
notNullColumnsInfo[table] = new HashSet<string>();
columnDefaultValues[table] = new Dictionary<string, string>();

using (var command = new SqlCommand($@"
SELECT
c.name AS ColumnName,
t.name AS DataType,
c.is_nullable AS IsNullable,
d.definition AS DefaultValue
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT JOIN sys.default_constraints d ON c.default_object_id = d.object_id
WHERE OBJECT_NAME(c.object_id) = '{table}'
AND c.is_nullable = 0", connection, checkTransaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string columnName = reader["ColumnName"].ToString();
string defaultValue = reader["DefaultValue"] != DBNull.Value ? reader["DefaultValue"].ToString() : null;
notNullColumnsInfo[table].Add(columnName);
columnDefaultValues[table][columnName] = defaultValue;
}
}
}
}

// 預先查詢所有表格的資料筆數
LogMessage("預先查詢所有表格的資料筆數...");
foreach (var table in tables)
{
// 跳過 VERSION 表格
if (table == "VERSION")
continue;

using (var command = new SqlCommand($"SELECT COUNT(*) FROM {backupDbName}.dbo.{table}", connection, checkTransaction))
{
try
{
int count = Convert.ToInt32(command.ExecuteScalar());
tableRowCounts[table] = count;
LogMessage($"表格 {table} 資料筆數: {count}");
}
catch (Exception ex)
{
LogMessage($"查詢表格 {table} 資料筆數時出錯: {ex.Message}", true);
tableRowCounts[table] = 0;
}
}
}

// 預先查詢所有有資料表格的欄位資訊
LogMessage("預先查詢所有有資料表格的欄位資訊...");
foreach (var table in tables)
{
// 跳過 VERSION 表格和無資料表格
if (table == "VERSION" || !tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0)
continue;

// 查詢備份資料庫的欄位資訊
var oldColumnInfo = new Dictionary<string, (string DataType, int MaxLength)>();
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN 0
ELSE CHARACTER_MAXIMUM_LENGTH
END AS CHARACTER_MAXIMUM_LENGTH
FROM {backupDbName}.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'", connection, checkTransaction))
{
using (var reader = command.ExecuteReader())
{
var columns = new List<string>();
while (reader.Read())
{
string columnName = reader.GetString(0);
string dataType = reader.GetString(1);
int maxLength = reader.GetInt32(2);
oldColumnInfo[columnName] = (dataType, maxLength);
columns.Add(columnName);
}
backupTableColumnsCache[table] = columns;
}
}
backupTableColumnInfoCache[table] = oldColumnInfo;

// 查詢新資料庫的欄位資訊
var newColumnInfo = new Dictionary<string, (string DataType, int MaxLength)>();
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN 0
ELSE CHARACTER_MAXIMUM_LENGTH
END AS CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'", connection, checkTransaction))
{
using (var reader = command.ExecuteReader())
{
var columns = new List<string>();
while (reader.Read())
{
string columnName = reader.GetString(0);
string dataType = reader.GetString(1);
int maxLength = reader.GetInt32(2);
newColumnInfo[columnName] = (dataType, maxLength);
columns.Add(columnName);
}
tableColumnsCache[table] = columns;
}
}
tableColumnInfoCache[table] = newColumnInfo;
}

// 檢查每個表格的字符欄位長度
foreach (var table in tables)
{
// 跳過 VERSION 表格
if (table == "VERSION")
{
continue;
}

// 使用預先查詢的資料筆數，若無資料則跳過所有欄位檢查
if (!tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0)
{
LogMessage($"表格 {table} 無資料，跳過欄位檢查");
continue; // 跳過無資料的表格
}

LogMessage($"表格 {table} 有 {tableRowCounts[table]} 筆資料，進行欄位檢查");

// 使用緩存的欄位資訊
if (!backupTableColumnInfoCache.ContainsKey(table) || !tableColumnInfoCache.ContainsKey(table))
{
LogMessage($"表格 {table} 無欄位緩存資訊，跳過欄位檢查");
continue;
}

var oldColumnInfo = backupTableColumnInfoCache[table];
var newColumnInfo = tableColumnInfoCache[table];

// 找出共同存在的欄位
var commonColumns = oldColumnInfo.Keys.Intersect(newColumnInfo.Keys).ToList();

// 檢查每個欄位的類型相容性和長度問題
foreach (var column in commonColumns)
{
var oldInfo = oldColumnInfo[column];
var newInfo = newColumnInfo[column];

// 檢查類型是否相容
bool isIncompatible = false;

// 定義相容的類型組合
bool isCompatible = false;

// 檢查是否為TEXT類型
bool isTextType = oldInfo.DataType.ToLower() == "text";
if (isTextType)
{
// 檢查新類型是否可以存儲字串
bool newTypeCanStoreString = newInfo.DataType.ToLower().Contains("char") ||
newInfo.DataType.ToLower().Contains("text") ||
newInfo.DataType.ToLower().Contains("xml") ||
newInfo.DataType.ToLower().Contains("json");

if (newTypeCanStoreString)
{
// 記錄TEXT類型欄位資訊
textTypeColumns.Add((table, column, newInfo.DataType, true));
isCompatible = true;
LogMessage($"發現TEXT類型欄位可能相容: 表格 {table}, 欄位 {column}, 新類型 {newInfo.DataType}");
}
else
{
// 記錄不相容的TEXT類型欄位
textTypeColumns.Add((table, column, newInfo.DataType, false));
isCompatible = false;
LogMessage($"發現TEXT類型欄位不相容: 表格 {table}, 欄位 {column}, 新類型 {newInfo.DataType}");
}
}
else
{
// 字符類型間相容（如varchar/nvarchar/char/nchar）
bool oldIsCharType = oldInfo.DataType.ToLower().Contains("char");
bool newIsCharType = newInfo.DataType.ToLower().Contains("char");
if (oldIsCharType && newIsCharType)
{
isCompatible = true;
LogMessage($"發現可相容的欄位類型變更: 表格 {table}, 欄位 {column}, 舊類型 {oldInfo.DataType}, 新類型 {newInfo.DataType}");
}

// 數值類型間相容（如int/smallint/tinyint/bigint）
bool oldIsIntType = oldInfo.DataType.ToLower().Contains("int");
bool newIsIntType = newInfo.DataType.ToLower().Contains("int");
if (oldIsIntType && newIsIntType)
{
isCompatible = true;
LogMessage($"發現可相容的欄位類型變更: 表格 {table}, 欄位 {column}, 舊類型 {oldInfo.DataType}, 新類型 {newInfo.DataType}");
}
}

// 如果不是相容的類型組合，且不是TEXT類型，則標記為不相容
if (!isCompatible && !isTextType)
{
isIncompatible = true;
LogMessage($"發現不相容的欄位類型: 表格 {table}, 欄位 {column}, 舊類型 {oldInfo.DataType}, 新類型 {newInfo.DataType}");
}

if (isIncompatible)
{
incompatibleColumnTypes.Add((table, column, oldInfo.DataType, newInfo.DataType));

// 檢查此欄位是否設為NOT NULL且沒有預設值
bool isNotNull = false;
string defaultValue = null;

// 檢查欄位的NULL約束和預設值
using (var nullCheckCommand = new SqlCommand($@"
SELECT
CASE WHEN c.is_nullable = 0 THEN 1 ELSE 0 END AS IsNotNull,
CASE WHEN d.definition IS NULL THEN NULL ELSE d.definition END AS DefaultValue
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN sys.default_constraints d ON c.default_object_id = d.object_id
WHERE t.name = '{table}' AND c.name = '{column}'", connection, checkTransaction))
{
using (var reader = nullCheckCommand.ExecuteReader())
{
if (reader.Read())
{
isNotNull = Convert.ToBoolean(reader["IsNotNull"]);
if (!reader.IsDBNull(reader.GetOrdinal("DefaultValue")))
{
defaultValue = reader["DefaultValue"].ToString();
}
}
}
}

// 如果是NOT NULL且沒有預設值，記錄到特殊列表
if (isNotNull && defaultValue == null)
{
notNullIncompatibleColumns.Add((table, column, newInfo.DataType, defaultValue));
LogMessage($"警告：欄位類型不相容且設為NOT NULL沒有預設值: 表格 {table}, 欄位 {column}", true);
}
}

// 檢查字符欄位長度問題（與原本的邏輯一致）
bool isCharType = oldInfo.DataType.Contains("char") && newInfo.DataType.Contains("char");

if (isCharType && oldInfo.MaxLength > newInfo.MaxLength && oldInfo.MaxLength > 0 && newInfo.MaxLength > 0)
{
// 先輸出簡化的日誌，保持與原本格式一致
LogMessage($"發現欄位長度變更: 表格 {table}, 欄位 {column}, 舊長度 {oldInfo.MaxLength}, 新長度 {newInfo.MaxLength}");

// 檢查是否有實際資料超出新長度限制
string sampleValue = "";
bool hasIssue = false;
int affectedRows = 0;
int maxLength = 0;

// 計算受影響的資料筆數
using (var countCommand = new SqlCommand($@"
SELECT COUNT(*)
FROM {backupDbName}.dbo.{table}
WHERE LEN({column}) > {newInfo.MaxLength}", connection, checkTransaction))
{
affectedRows = Convert.ToInt32(countCommand.ExecuteScalar());
}

// 取得最大長度值
using (var maxCommand = new SqlCommand($@"
SELECT MAX(LEN({column}))
FROM {backupDbName}.dbo.{table}
WHERE LEN({column}) > {newInfo.MaxLength}", connection, checkTransaction))
{
var maxResult = maxCommand.ExecuteScalar();
if (maxResult != null && maxResult != DBNull.Value)
{
maxLength = Convert.ToInt32(maxResult);
}
}

// 取得範例值
using (var command = new SqlCommand($@"
SELECT TOP 1 {column}
FROM {backupDbName}.dbo.{table}
WHERE LEN({column}) > {newInfo.MaxLength}
ORDER BY LEN({column}) DESC", connection, checkTransaction))
{
var result = command.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
sampleValue = result.ToString();
hasIssue = true;
}
}

if (hasIssue)
{
// 先輸出簡化的日誌，保持與原本格式一致
LogMessage($"發現欄位長度問題: 表格 {table}, 欄位 {column}, 舊長度 {oldInfo.MaxLength}, 新長度 {newInfo.MaxLength}");

// 記錄更詳細的日誌
LogMessage($"【欄位長度問題詳情】表格: {table}");
LogMessage($" 欄位名稱: {column}");
LogMessage($" 欄位類型: {oldInfo.DataType}");
LogMessage($" 舊長度上限: {oldInfo.MaxLength}, 新長度上限: {newInfo.MaxLength}");
LogMessage($" 受影響資料筆數: {affectedRows}筆");
LogMessage($" 最大實際長度: {maxLength}字元");
if (sampleValue.Length > 100)
{
LogMessage($" 範例資料: {sampleValue.Substring(0, 97)}...(已截斷)");
}
else
{
LogMessage($" 範例資料: {sampleValue}");
}

columnLengthIssues.Add((table, column, oldInfo.MaxLength, newInfo.MaxLength, sampleValue));
}
}
}
}

checkTransaction.Commit();
LogMessage("欄位類型相容性和長度檢查完成");
}
catch (Exception ex)
{
checkTransaction.Rollback();
throw new Exception($"檢查欄位時發生錯誤: {ex.Message}", ex);
}
}

// 處理特別情況：不允許NULL且不相容的欄位
if (notNullIncompatibleColumns.Any())
{
var sb = new System.Text.StringBuilder();
sb.AppendLine("【重要警告】發現以下欄位同時符合三個條件：");
sb.AppendLine("1. 資料類型不相容，無法自動轉換");
sb.AppendLine("2. 欄位設定為NOT NULL（不接受NULL值）");
sb.AppendLine("3. 欄位沒有設定預設值");
sb.AppendLine();
sb.AppendLine("如果跳過這些欄位的資料遷移，INSERT操作可能會失敗：");
sb.AppendLine();

foreach (var issue in notNullIncompatibleColumns)
{
sb.AppendLine($"【表格:{issue.TableName}】");
sb.AppendLine($"欄位: {issue.ColumnName}");
sb.AppendLine($"類型: {issue.ColumnType}");
sb.AppendLine($"預設值: {(issue.DefaultValue == null ? "無" : issue.DefaultValue)}");
sb.AppendLine("==============================");
}

sb.AppendLine();
sb.AppendLine("請選擇處理方式：");
sb.AppendLine("- 「是」：仍然嘗試遷移資料（可能會失敗）");
sb.AppendLine("- 「否」：取消整個操作並還原資料庫");

// 在UI線程上彈出確認對話框
bool continueWithNotNullIssues = false;
// 記錄警告對話框內容到日誌
LogMessage("【對話框】顯示NOT NULL欄位不相容警告對話框", true);
LogMessage("對話框內容：");
LogMessage(sb.ToString());

Dispatcher.Invoke(() =>
{
var result = MessageBox.Show(
sb.ToString(),
"重要警告：NOT NULL欄位不相容 / Important Warning: NOT NULL Field Incompatibility",
MessageBoxButton.YesNo,
MessageBoxImage.Error);

continueWithNotNullIssues = (result == MessageBoxResult.Yes);

// 記錄用戶選擇
if (continueWithNotNullIssues)
{
LogMessage("【用戶選擇】用戶選擇繼續操作(是)，即使有NOT NULL欄位不相容的風險");
}
else
{
LogMessage("【用戶選擇】用戶選擇取消操作(否)，將放棄資料遷移並還原資料庫");
}
});

if (!continueWithNotNullIssues)
{
LogMessage("使用者選擇取消資料遷移，將還原資料庫");
return false;
}

LogMessage("使用者選擇繼續資料遷移，即使有NOT NULL不相容欄位的風險");
LogMessage("受影響的NOT NULL欄位清單：");
foreach (var issue in notNullIncompatibleColumns)
{
LogMessage($"- 表格 {issue.TableName}, 欄位 {issue.ColumnName}, 類型 {issue.ColumnType}");
}
}

// 處理一般不相容的欄位類型問題
if (incompatibleColumnTypes.Any())
{
var sb = new System.Text.StringBuilder();
sb.AppendLine("發現以下欄位的資料類型不相容，無法自動轉換：");
sb.AppendLine();

// 排除已在NOT NULL警告中顯示的欄位
var regularIncompatibleColumns = incompatibleColumnTypes
.Where(ic => !notNullIncompatibleColumns.Any(nnc =>
nnc.TableName == ic.TableName && nnc.ColumnName == ic.ColumnName))
.ToList();

foreach (var issue in regularIncompatibleColumns)
{
sb.AppendLine($"【表格:{issue.TableName}】");
sb.AppendLine($"欄位: {issue.ColumnName}");
sb.AppendLine($"舊類型: {issue.OldType}, 新類型: {issue.NewType}");
sb.AppendLine("==============================");
}

// 如果所有不相容欄位都是NOT NULL問題且已經顯示過警告，則跳過此對話框
if (regularIncompatibleColumns.Any())
{
sb.AppendLine();
sb.AppendLine("請選擇處理方式：");
sb.AppendLine("- 「是」：跳過這些欄位的資料遷移，這些欄位將保持為預設值或NULL");
sb.AppendLine("- 「否」：取消更新並還原資料庫");

// 在UI線程上彈出確認對話框
bool skipIncompatibleColumns = false;
// 記錄警告對話框內容到日誌
LogMessage("【對話框】顯示欄位類型不相容警告對話框", true);
LogMessage("對話框內容：");
LogMessage(sb.ToString());

Dispatcher.Invoke(() =>
{
var result = MessageBox.Show(
sb.ToString(),
"欄位類型不相容 / Field Type Incompatibility",
MessageBoxButton.YesNo,
MessageBoxImage.Warning);

skipIncompatibleColumns = (result == MessageBoxResult.Yes);

// 記錄用戶選擇
if (skipIncompatibleColumns)
{
LogMessage("【用戶選擇】用戶選擇跳過不相容欄位(是)，將繼續資料遷移");
}
else
{
LogMessage("【用戶選擇】用戶選擇取消操作(否)，將放棄資料遷移並還原資料庫");
}
});

if (!skipIncompatibleColumns)
{
LogMessage("使用者選擇取消資料遷移，將還原資料庫");
return false;
}

LogMessage("使用者選擇跳過不相容的欄位，將繼續資料遷移");
LogMessage("受影響的一般欄位清單：");
foreach (var issue in regularIncompatibleColumns)
{
LogMessage($"- 表格 {issue.TableName}, 欄位 {issue.ColumnName}, 舊類型 {issue.OldType}, 新類型 {issue.NewType}");
}
}
}

// 如果有欄位長度問題，詢問用戶如何處理
if (columnLengthIssues.Any())
{
var sb = new System.Text.StringBuilder();
sb.AppendLine("發現以下欄位中的資料長度超過新資料庫欄位允許的長度：");
sb.AppendLine();

foreach (var issue in columnLengthIssues)
{
sb.AppendLine($"【表格:{issue.TableName}】");
sb.AppendLine($"欄位: {issue.ColumnName}");
sb.AppendLine($"資料長度限制: {issue.OldLength}→{issue.NewLength} (減少 {issue.OldLength - issue.NewLength} 字元)");
sb.AppendLine($"範例值: {(issue.SampleValue.Length > 50 ? issue.SampleValue.Substring(0, 47) + "..." : issue.SampleValue)}");
sb.AppendLine($"此值長度: {issue.SampleValue.Length} (超出 {issue.SampleValue.Length - issue.NewLength} 字元)");
sb.AppendLine("==============================");
}

sb.AppendLine();
sb.AppendLine("是否仍然要繼續？");
sb.AppendLine("- 「是」：將截斷超過長度的資料");
sb.AppendLine("- 「否」：取消更新並還原資料庫");

// 在UI線程上彈出確認對話框
bool continueMigration = false;
// 記錄警告對話框內容到日誌
LogMessage("【對話框】顯示資料長度問題警告對話框", true);
LogMessage("對話框內容：");
LogMessage(sb.ToString());

Dispatcher.Invoke(() =>
{
var result = MessageBox.Show(
sb.ToString(),
"資料長度問題 / Data Length Issue",
MessageBoxButton.YesNo,
MessageBoxImage.Warning);

continueMigration = (result == MessageBoxResult.Yes);

// 記錄用戶選擇
if (continueMigration)
{
LogMessage("【用戶選擇】用戶選擇截斷超過長度的資料(是)，將繼續資料遷移");
}
else
{
LogMessage("【用戶選擇】用戶選擇取消操作(否)，將放棄資料遷移並還原資料庫");
}
});

if (!continueMigration)
{
LogMessage("使用者選擇取消資料遷移，將還原資料庫");
return false;
}

LogMessage("使用者選擇繼續資料遷移，將截斷超過長度的資料");
LogMessage("受影響的欄位清單：");
foreach (var issue in columnLengthIssues)
{
LogMessage($"- 表格 {issue.TableName}, 欄位 {issue.ColumnName}, 舊長度 {issue.OldLength}, 新長度 {issue.NewLength}");
}
}

using (var transaction = connection.BeginTransaction())
{
try
{
// 取得所有表格名稱
var tables = new List<string>();
using (var command = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
}
}

// 對每個表格進行資料遷移
foreach (var table in tables)
{
// 跳過 VERSION 表格的資料遷移
if (table == "VERSION")
{
LogMessage("跳過 VERSION 表格的資料遷移");
continue;
}

LogMessage($"處理表格 {table} 的資料遷移");

// 使用緩存的資料筆數
if (!tableRowCounts.ContainsKey(table) || tableRowCounts[table] == 0)
{
LogMessage($"表格 {table} 無資料或未緩存，跳過遷移");
continue;
}

int rowCount = tableRowCounts[table];
LogMessage($"表格 {table} 有 {rowCount} 筆資料需要遷移");

// 使用緩存的欄位資訊
List<string> oldColumns;
List<string> newColumns;

if (backupTableColumnsCache.ContainsKey(table))
{
oldColumns = backupTableColumnsCache[table];
}
else
{
oldColumns = new List<string>();
// 從備份資料庫取得欄位資訊
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME
FROM {backupDbName}.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
oldColumns.Add(reader.GetString(0));
}
}
}
// 緩存結果，以便後續使用
backupTableColumnsCache[table] = oldColumns;
}

if (tableColumnsCache.ContainsKey(table))
{
newColumns = tableColumnsCache[table];
}
else
{
newColumns = new List<string>();
// 從新資料庫取得欄位資訊
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
newColumns.Add(reader.GetString(0));
}
}
}
// 緩存結果，以便後續使用
tableColumnsCache[table] = newColumns;
}

// 找出共同存在的欄位
var commonColumns = oldColumns.Intersect(newColumns).ToList();
LogMessage($"表格 {table} 找到 {commonColumns.Count} 個共同欄位");

if (commonColumns.Any())
{
// 檢查表格是否有 IDENTITY 欄位
bool hasIdentity = false;
using (var command = new SqlCommand($@"
SELECT COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = '{table}' AND c.is_identity = 1", connection, transaction))
{
hasIdentity = (int)command.ExecuteScalar() > 0;
}

if (hasIdentity)
{
LogMessage($"表格 {table} 有 IDENTITY 欄位，設定 IDENTITY_INSERT ON");
using (var command = new SqlCommand($"SET IDENTITY_INSERT {table} ON", connection, transaction))
{
command.ExecuteNonQuery();
}
}

// 使用緩存的欄位資訊
var columnInfos = new Dictionary<string, (string DataType, int MaxLength, bool IsIncompatible)>();

// 從緩存中獲取欄位類型和長度信息
if (tableColumnInfoCache.ContainsKey(table))
{
var cachedColumnInfo = tableColumnInfoCache[table];

foreach (var kvp in cachedColumnInfo)
{
string columnName = kvp.Key;
string dataType = kvp.Value.DataType;
int maxLength = kvp.Value.MaxLength;

// 檢查此欄位是否在不相容欄位列表中
bool isIncompatible = incompatibleColumnTypes.Any(ic =>
ic.TableName.Equals(table, StringComparison.OrdinalIgnoreCase) &&
ic.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase));

columnInfos[columnName] = (dataType, maxLength, isIncompatible);
}
}
else
{
// 如果沒有緩存，則仍然執行查詢
using (var command = new SqlCommand($@"
SELECT COLUMN_NAME, DATA_TYPE,
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN 0
ELSE CHARACTER_MAXIMUM_LENGTH
END AS CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string columnName = reader.GetString(0);
string dataType = reader.GetString(1);
int maxLength = reader.GetInt32(2);

// 檢查此欄位是否在不相容欄位列表中
bool isIncompatible = incompatibleColumnTypes.Any(ic =>
ic.TableName.Equals(table, StringComparison.OrdinalIgnoreCase) &&
ic.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase));

columnInfos[columnName] = (dataType, maxLength, isIncompatible);
}
}
}
}

// 為每個欄位應用適當的轉換
var columnClauses = new List<string>();
var columnsToInclude = new List<string>();

foreach (var column in commonColumns)
{
// 跳過不在獲取的欄位資訊中的欄位
if (!columnInfos.ContainsKey(column))
continue;

var columnInfo = columnInfos[column];

if (columnInfo.IsIncompatible)
{
// 跳過不相容的欄位
LogMessage($"跳過表格 {table} 中不相容的欄位 {column} 的資料遷移");
continue;
}

// 記錄要包含的欄位
columnsToInclude.Add(column);

// 如果是字符類型，檢查是否需要截斷
if (columnInfo.DataType.Contains("char") && columnInfo.MaxLength > 0)
{
columnClauses.Add($"CASE WHEN LEN([{column}]) > {columnInfo.MaxLength} THEN LEFT([{column}], {columnInfo.MaxLength}) ELSE [{column}] END AS [{column}]");
}
else
{
columnClauses.Add($"[{column}]");
}
}

// 如果沒有相容的欄位，則跳過此表格
if (!columnsToInclude.Any())
{
LogMessage($"表格 {table} 沒有相容的欄位，跳過此表格的資料遷移");
continue;
}

string columns = string.Join(", ", columnsToInclude.Select(c => $"[{c}]"));
string selectColumns = string.Join(", ", columnClauses);

// 檢查是否有資料，避免對空表格執行複雜操作
int recordCount = 0;
using (var countCommand = new SqlCommand($"SELECT COUNT(*) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
recordCount = Convert.ToInt32(countCommand.ExecuteScalar());
}

if (recordCount == 0)
{
LogMessage($"表格 {table} 無資料，跳過遷移");
continue;
}

LogMessage($"表格 {table} 有 {recordCount} 筆資料需要遷移");

// 檢查不允許 NULL 的欄位是否有 NULL 值
var notNullColumns = new List<string>();
using (var schemaCommand = new SqlCommand($@"
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'
AND IS_NULLABLE = 'NO'
AND COLUMN_DEFAULT IS NULL", connection, transaction))
{
using (var reader = schemaCommand.ExecuteReader())
{
while (reader.Read())
{
notNullColumns.Add(reader.GetString(0));
}
}
}

if (notNullColumns.Any())
{
foreach (var column in notNullColumns)
{
// 檢查來源資料是否有 NULL 值
using (var nullCheckCommand = new SqlCommand($@"
SELECT COUNT(*)
FROM {backupDbName}.dbo.{table}
WHERE [{column}] IS NULL", connection, transaction))
{
int nullCount = Convert.ToInt32(nullCheckCommand.ExecuteScalar());
if (nullCount > 0)
{
// 在 UI 線程上顯示確認對話框
bool skipNullRecords = false;
Dispatcher.Invoke(() =>
{
var message = $"表格 {table} 中的欄位 {column} 不允許 NULL 值，但在來源資料中發現 {nullCount} 筆 NULL 值記錄。\n\n" +
"是否要跳過這些記錄？\n" +
"- 「是」：跳過包含 NULL 值的記錄\n" +
"- 「否」：中止整個遷移過程";

LogMessage("【對話框】顯示 NULL 值處理確認對話框");
LogMessage(message);

var result = MessageBox.Show(
message,
"NULL 值處理確認 / NULL Value Handling Confirmation",
MessageBoxButton.YesNo,
MessageBoxImage.Warning);

skipNullRecords = (result == MessageBoxResult.Yes);

if (skipNullRecords)
{
LogMessage($"【用戶選擇】用戶選擇跳過 NULL 值記錄(是)");
}
else
{
LogMessage($"【用戶選擇】用戶選擇中止遷移(否)");
}
});

if (!skipNullRecords)
{
LogMessage("根據用戶選擇，中止資料遷移");
return false;
}

// 修改 SQL 查詢以排除 NULL 值記錄
selectColumns = $"SELECT {selectColumns} FROM {backupDbName}.dbo.{table} WHERE [{column}] IS NOT NULL";
}
}
}
}

// 對大表使用批次處理以提高效率
const int batchSize = 5000; // 每批次處理的記錄數

if (recordCount > batchSize)
{
// 檢查表是否有主鍵或唯一索引可用於分頁
string keyColumn = null;
using (var keyCommand = new SqlCommand($@"
SELECT c.name
FROM {backupDbName}.sys.indexes i
JOIN {backupDbName}.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN {backupDbName}.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN {backupDbName}.sys.tables t ON i.object_id = t.object_id
WHERE i.is_primary_key = 1 OR i.is_unique = 1
AND t.name = '{table}'
AND ic.key_ordinal = 1", connection, transaction))
{
var keyObj = keyCommand.ExecuteScalar();
if (keyObj != null)
{
keyColumn = keyObj.ToString();
}
}

if (keyColumn != null && columnsToInclude.Contains(keyColumn))
{
LogMessage($"使用主鍵 {keyColumn} 進行批次處理");

// 獲取最小和最大值
int minKey = 0, maxKey = 0;
using (var minCommand = new SqlCommand($"SELECT MIN([{keyColumn}]) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
var result = minCommand.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
minKey = Convert.ToInt32(result);
}
}

using (var maxCommand = new SqlCommand($"SELECT MAX([{keyColumn}]) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
var result = maxCommand.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
maxKey = Convert.ToInt32(result);
}
}

int totalBatches = (int)Math.Ceiling((double)(maxKey - minKey + 1) / batchSize);
LogMessage($"將分 {totalBatches} 批處理");

for (int currentBatch = 0; currentBatch < totalBatches; currentBatch++)
{
int startKey = minKey + (currentBatch * batchSize);
int endKey = Math.Min(startKey + batchSize - 1, maxKey);

string batchInsertSql = $@"
INSERT INTO {table} ({columns})
SELECT {selectColumns}
FROM {backupDbName}.dbo.{table}
WHERE [{keyColumn}] BETWEEN {startKey} AND {endKey}";

using (var command = new SqlCommand(batchInsertSql, connection, transaction))
{
command.CommandTimeout = 60; // 每批次1分鐘超時
int affected = command.ExecuteNonQuery();
LogMessage($"批次 {currentBatch + 1}/{totalBatches} 完成，遷移 {affected} 筆資料");
}
}
}
else
{
// 無法使用主鍵分頁，使用一般方式
string insertSql = $@"
INSERT INTO {table} ({columns})
SELECT {selectColumns}
FROM {backupDbName}.dbo.{table}";

using (var command = new SqlCommand(insertSql, connection, transaction))
{
command.CommandTimeout = 300; // 設定5分鐘超時
command.ExecuteNonQuery();
LogMessage($"表格 {table} 的資料遷移完成");
}
}
}
else
{
// 小表直接遷移
string insertSql = $@"
INSERT INTO {table} ({columns})
SELECT {selectColumns}
FROM {backupDbName}.dbo.{table}";

using (var command = new SqlCommand(insertSql, connection, transaction))
{
command.CommandTimeout = 60; // 1分鐘超時足夠小表使用
command.ExecuteNonQuery();
LogMessage($"表格 {table} 的資料遷移完成");
}
}

if (hasIdentity)
{
LogMessage($"表格 {table} 資料遷移完成，設定 IDENTITY_INSERT OFF");
using (var command = new SqlCommand($"SET IDENTITY_INSERT {table} OFF", connection, transaction))
{
command.ExecuteNonQuery();
}
}
}
}

// 處理TEXT類型欄位
foreach (var textColumn in textTypeColumns)
{
string table = textColumn.TableName;
string column = textColumn.ColumnName;
string newDataType = textColumn.NewType;
bool isCompatible = textColumn.IsCompatible;

if (isCompatible)
{
LogMessage($"處理TEXT類型欄位: 表格 {table}, 欄位 {column}, 新類型 {newDataType}");

// 檢查是否有資料，避免對空表格執行複雜操作
int recordCount = 0;
using (var countCommand = new SqlCommand($"SELECT COUNT(*) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
recordCount = Convert.ToInt32(countCommand.ExecuteScalar());
}

if (recordCount == 0)
{
LogMessage($"表格 {table} 無資料，跳過遷移");
continue;
}

LogMessage($"表格 {table} 有 {recordCount} 筆資料需要遷移");

// 對大表使用批次處理以提高效率
const int batchSize = 5000; // 每批次處理的記錄數

if (recordCount > batchSize)
{
// 檢查表是否有主鍵或唯一索引可用於分頁
string keyColumn = null;
using (var keyCommand = new SqlCommand($@"
SELECT c.name
FROM {backupDbName}.sys.indexes i
JOIN {backupDbName}.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN {backupDbName}.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN {backupDbName}.sys.tables t ON i.object_id = t.object_id
WHERE i.is_primary_key = 1 OR i.is_unique = 1
AND t.name = '{table}'
AND ic.key_ordinal = 1", connection, transaction))
{
var keyObj = keyCommand.ExecuteScalar();
if (keyObj != null)
{
keyColumn = keyObj.ToString();
}
}

if (keyColumn != null)
{
LogMessage($"使用主鍵 {keyColumn} 進行批次處理");

// 獲取最小和最大值
int minKey = 0, maxKey = 0;
using (var minCommand = new SqlCommand($"SELECT MIN([{keyColumn}]) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
var result = minCommand.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
minKey = Convert.ToInt32(result);
}
}

using (var maxCommand = new SqlCommand($"SELECT MAX([{keyColumn}]) FROM {backupDbName}.dbo.{table}", connection, transaction))
{
var result = maxCommand.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
maxKey = Convert.ToInt32(result);
}
}

int totalBatches = (int)Math.Ceiling((double)(maxKey - minKey + 1) / batchSize);
LogMessage($"將分 {totalBatches} 批處理");

for (int currentBatch = 0; currentBatch < totalBatches; currentBatch++)
{
int startKey = minKey + (currentBatch * batchSize);
int endKey = Math.Min(startKey + batchSize - 1, maxKey);

string batchUpdateSql = $@"
UPDATE {table}
SET {column} = LEFT({column}, {newDataType.Length})
WHERE [{keyColumn}] BETWEEN {startKey} AND {endKey}";

using (var command = new SqlCommand(batchUpdateSql, connection, transaction))
{
command.CommandTimeout = 60; // 每批次1分鐘超時
int affected = command.ExecuteNonQuery();
LogMessage($"批次 {currentBatch + 1}/{totalBatches} 完成，遷移 {affected} 筆資料");
}
}
}
else
{
// 無法使用主鍵分頁，使用一般方式
string updateSql = $@"
UPDATE {table}
SET {column} = LEFT({column}, {newDataType.Length})";

using (var command = new SqlCommand(updateSql, connection, transaction))
{
command.CommandTimeout = 300; // 設定5分鐘超時
command.ExecuteNonQuery();
LogMessage($"表格 {table} 的資料遷移完成");
}
}
}
else
{
// 小表直接遷移
string updateSql = $@"
UPDATE {table}
SET {column} = LEFT({column}, {newDataType.Length})";

using (var command = new SqlCommand(updateSql, connection, transaction))
{
command.CommandTimeout = 60; // 1分鐘超時足夠小表使用
command.ExecuteNonQuery();
LogMessage($"表格 {table} 的資料遷移完成");
}
}
}
else
{
LogMessage($"跳過不相容的TEXT類型欄位: 表格 {table}, 欄位 {column}, 新類型 {newDataType}");
}
}

transaction.Commit();
LogMessage("所有表格資料遷移完成");
return true;
}
catch (Exception ex)
{
transaction.Rollback();
throw new Exception($"資料遷移時發生錯誤: {ex.Message}", ex);
}
}
}
}
catch (Exception ex)
{
LogMessage($"資料遷移失敗: {ex.Message}", true);
throw;
}
}

private void btn_Close_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

// 關閉視窗
Close();
}

private void ShutdownApplicationOlc()
{
try
{
// 先關閉所有視窗
foreach (Window window in Application.Current.Windows)
{
window.Close();
}

// 確保所有背景執行緒都結束
System.Threading.Thread.Sleep(100); // 給一點時間讓視窗關閉

// 強制終止應用程式
Environment.Exit(0);
}
catch (Exception ex)
{
// 如果正常關閉失敗，使用更強制的方式
Process.GetCurrentProcess().Kill();
}
}
private void ShutdownApplication()
{
try
{
// 檢查主視窗是否存在且可見
MainWindow mainWindow = Application.Current.Windows.OfType<MainWindow>().FirstOrDefault();
if (mainWindow != null && mainWindow.IsVisible)
{
// 如果主視窗存在且可見，則透過主視窗的關閉流程來關閉應用程式
mainWindow.Close();
return; // 讓主視窗的關閉事件處理程序來處理後續的關閉流程
}
else
{
// 如果主視窗不存在或不可見（例如系統啟動時檢查版本），則直接關閉
foreach (Window window in Application.Current.Windows)
{
window.Close();
}

// 確保所有背景執行緒都結束
System.Threading.Thread.Sleep(100);

// 強制終止應用程式
Environment.Exit(0);
}
}
catch (Exception ex)
{
// 如果正常關閉失敗，使用更強制的方式
Process.GetCurrentProcess().Kill();
}
}
private async void btn_ClearBackup_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

// 禁用所有按鈕
SetOperationButtonsState(false, "清除備份");

try
{
if (!ValidateDatabaseSettings())
{
// 在驗證失敗時重新啟用按鈕
SetOperationButtonsState(true);
return;
}

UpdateConnectionStrings();
ClearLog();
LogMessage("開始清除備份資料庫程序");

// 先確認是否要執行清除備份資料庫
var confirmResult = MessageBox.Show("確定要清除所有備份資料庫嗎？\n此操作無法復原。\n\nAre you sure you want to clear all backup databases?\nThis operation cannot be undone.", "確認操作 / Confirm Operation",
MessageBoxButton.YesNo, MessageBoxImage.Warning);

if (confirmResult != MessageBoxResult.Yes)
{
LogMessage("使用者取消清除備份資料庫操作");
// 在用戶取消操作時重新啟用按鈕
SetOperationButtonsState(true);
return;
}

// 取得備份資料庫列表
List<string> backupDbList = new List<string>();

using (var connection = new SqlConnection(_masterConnectionString))
{
connection.Open();
using (var command = new SqlCommand("SELECT name FROM sys.databases WHERE name LIKE 'BC_Dev_%'", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
backupDbList.Add(reader.GetString(0));
}
}
}
}

if (backupDbList.Count == 0)
{
LogMessage("沒有找到任何備份資料庫");
MessageBox.Show("沒有找到任何備份資料庫可以清除\n\nNo backup databases found to clear", "資訊 / Information", MessageBoxButton.OK, MessageBoxImage.Information);
// 在沒有找到備份資料庫時重新啟用按鈕
SetOperationButtonsState(true);
return;
}

LogMessage($"共找到 {backupDbList.Count} 個備份資料庫");

// 在背景執行緒中執行刪除操作
await Task.Run(() =>
{
Dispatcher.BeginInvoke(new Action(() =>
{
LogMessage("開始清除備份資料庫...");
}), DispatcherPriority.Normal);

int successCount = 0;
int failCount = 0;

using (var connection = new SqlConnection(_masterConnectionString))
{
connection.Open();
foreach (var dbName in backupDbList)
{
try
{
Dispatcher.BeginInvoke(new Action(() =>
{
LogMessage($"正在刪除資料庫 {dbName}...");
}), DispatcherPriority.Normal);

// 獲取資料庫檔案路徑
List<string> dbFiles = new List<string>();
using (var command = new SqlCommand($"SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID('{dbName}')", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
dbFiles.Add(reader.GetString(0));
}
}
}

// 刪除資料庫
using (var command = new SqlCommand($"ALTER DATABASE [{dbName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [{dbName}]", connection))
{
command.ExecuteNonQuery();
}

successCount++;
Dispatcher.BeginInvoke(new Action(() =>
{
LogMessage($"成功刪除資料庫 {dbName}");
}), DispatcherPriority.Normal);
}
catch (Exception ex)
{
failCount++;
Dispatcher.BeginInvoke(new Action(() =>
{
LogMessage($"刪除資料庫 {dbName} 時出錯: {ex.Message}", true);
}), DispatcherPriority.Normal);
}
}
}

Dispatcher.BeginInvoke(new Action(() =>
{
LogMessage($"清除完成。成功: {successCount}, 失敗: {failCount}");

if (failCount > 0)
{
MessageBox.Show($"清除備份資料庫完成。\n成功: {successCount}, 失敗: {failCount}\n\n請查看操作記錄了解詳細資訊。\n\nBackup database cleanup completed.\nSuccess: {successCount}, Failed: {failCount}\n\nPlease check operation log for details.",
"部分完成 / Partially Completed", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
MessageBox.Show($"成功清除所有備份資料庫。\n共清除 {successCount} 個資料庫。\n\nAll backup databases have been successfully cleared.\nCleared {successCount} databases.",
"完成 / Completed", MessageBoxButton.OK, MessageBoxImage.Information);
}

// 在操作完成後重新啟用按鈕
SetOperationButtonsState(true);
}), DispatcherPriority.Normal);
});
}
catch (Exception ex)
{
LogMessage($"清除備份資料庫失敗: {ex.Message}", true);
MessageBox.Show($"清除備份資料庫失敗，請參閱操作記錄了解詳細資訊\n\nFailed to clear backup databases. Please refer to the operation log for detailed information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);

// 確保在發生錯誤時也重新啟用按鈕
SetOperationButtonsState(true);
}
}

private void btn_SaveSettings_Click(object sender, RoutedEventArgs e)
{
// 禁用所有按鈕
SetOperationButtonsState(false, "儲存設定");

try
{
LogMessage("開始儲存資料庫設定...");

// 驗證設定
if (!ValidateDatabaseSettings())
{
LogMessage("驗證失敗，取消儲存", true);
// 在驗證失敗時重新啟用按鈕
SetOperationButtonsState(true);
return;
}

// 確保IP和PORT有效
string ip = txtDbIP.Text.Trim();
string port = txtDbPort.Text.Trim();

if (string.IsNullOrEmpty(ip))
{
LogMessage("錯誤：資料庫伺服器 IP 不能為空", true);
MessageBox.Show("資料庫伺服器 IP 不能為空\n\nDatabase server IP cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}

// 驗證IP格式
if (!ip.Equals("localhost", StringComparison.OrdinalIgnoreCase) && !IsValidIPv4(ip))
{
LogMessage("錯誤：資料庫伺服器 IP 格式無效", true);
MessageBox.Show("請輸入有效的IPv4地址（如: 192.168.1.1）或 localhost\n\nPlease enter a valid IPv4 address (e.g. 192.168.1.1) or localhost", "輸入錯誤 / Input Error", MessageBoxButton.OK, MessageBoxImage.Warning);
txtDbIP.Focus();
txtDbIP.SelectAll();
return;
}

if (string.IsNullOrEmpty(port))
{
LogMessage("錯誤：資料庫伺服器 Port 不能為空", true);
MessageBox.Show("資料庫伺服器 Port 不能為空\n\nDatabase server Port cannot be empty", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}

// 建立連線字串
string dataSource = $"{ip},{port}";
var builder = new SqlConnectionStringBuilder
{
DataSource = dataSource,
InitialCatalog = txtDbName.Text, // 使用 InitialCatalog 設定資料庫名稱
UserID = txtDbID.Text,
Password = txtDbPassword.Text,
MinPoolSize = int.Parse(txtMinPoolSize.Text),
MaxPoolSize = int.Parse(txtMaxPoolSize.Text)
};

string connString = builder.ConnectionString;
LogMessage("連線字串已建立");
LogMessage($"- 資料庫伺服器：{ip}:{port}");
LogMessage($"- 資料庫名稱：{txtDbName.Text}");
LogMessage($"- 使用者帳號：{txtDbID.Text}");
LogMessage($"- 連線池設定：Min={txtMinPoolSize.Text}, Max={txtMaxPoolSize.Text}");

// 確保目錄存在
string configDir = IOPath.GetDirectoryName(_configPath);
if (!Directory.Exists(configDir))
{
Directory.CreateDirectory(configDir);
LogMessage($"建立設定檔目錄：{configDir}");
}

// 讀取現有的 XML 檔案
XDocument doc;
if (IOFile.Exists(_configPath))
{
doc = XDocument.Load(_configPath);
LogMessage("讀取現有設定檔");
}
else
{
// 建立新的 XML 文件
doc = new XDocument(
new XElement("hibernate-configuration",
new XElement("session-factory")
)
);
LogMessage("建立新的設定檔");
}

// 取得命名空間
XNamespace ns = doc.Root.GetDefaultNamespace();

// 尋找或建立 session-factory 元素
var sessionFactory = doc.Root.Element(ns + "session-factory");
if (sessionFactory == null)
{
sessionFactory = new XElement(ns + "session-factory");
doc.Root.Add(sessionFactory);
}

// 尋找或建立 connection.connection_string 屬性
var propertyElement = sessionFactory.Elements(ns + "property")
.FirstOrDefault(p => p.Attribute("name")?.Value == "connection.connection_string");

if (propertyElement == null)
{
propertyElement = new XElement(ns + "property",
new XAttribute("name", "connection.connection_string")
);
sessionFactory.Add(propertyElement);
}

// 更新連線字串值
propertyElement.Value = connString;

// 儲存檔案
doc.Save(_configPath);

LogMessage("自動保存了界面設定變更");
_hasUnsavedChanges = false;

MessageBox.Show("資料庫設定已儲存\n\nDatabase settings have been saved", "成功 / Success", MessageBoxButton.OK, MessageBoxImage.Information);

// 在操作完成後重新啟用按鈕
SetOperationButtonsState(true);
}
catch (Exception ex)
{
LogMessage($"儲存資料庫設定時發生錯誤：{ex.Message}", true);
LogMessage($"錯誤詳細資訊：{ex.StackTrace}", true);
MessageBox.Show($"儲存資料庫設定時發生錯誤，請參閱操作記錄了解詳細資訊\n\nError occurred while saving database settings. Please refer to the operation log for detailed information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);

// 確保在發生錯誤時也重新啟用按鈕
SetOperationButtonsState(true);
}
}

private async void btn_BackupDatabase_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

// 禁用所有按鈕
SetOperationButtonsState(false, "備份資料庫");

try
{
// 暫停 TraceDataReport Timer
PauseTraceDataReportTimer();

LogMessage("開始備份資料庫操作");

// 確認是否要執行備份
var result = MessageBox.Show(
"確定要備份資料庫嗎？\n此操作會將目前的BC_Dev資料庫備份為一個新的資料庫。\n\nAre you sure you want to backup the database?\nThis operation will backup the current BC_Dev database as a new database.",
"確認操作 / Confirm Operation",
MessageBoxButton.YesNo,
MessageBoxImage.Question);

if (result != MessageBoxResult.Yes)
{
LogMessage("使用者取消備份操作");
return;
}

// 執行備份操作 - 使用共用的BackupDatabase方法
var backupResult = await Task.Run(() =>
{
// 設置當前執行緒名稱，用於標識是單獨備份操作
System.Threading.Thread.CurrentThread.Name = "StandaloneBackupTask";
_currentOperation = "備份資料庫";
return BackupDatabase();
});

// 檢查備份結果
if (backupResult.DatabaseName == null)
{
// 直接顯示具體的錯誤原因，不再重複記錄
MessageBox.Show(
backupResult.ErrorMessage,
"提示",
MessageBoxButton.OK,
MessageBoxImage.Information);
return;
}

// 獲取成功的備份資料庫名稱
string backupDbName = backupResult.DatabaseName;

// 備份成功
LogMessage($"資料庫備份完成：{backupDbName}");
MessageBox.Show(
$"資料庫備份已完成。\n備份資料庫名稱：{backupDbName}\n\nDatabase backup completed.\nBackup database name: {backupDbName}",
"備份完成 / Backup Completed",
MessageBoxButton.OK,
MessageBoxImage.Information);
}
catch (Exception ex)
{
LogMessage($"備份資料庫時發生錯誤: {ex.Message}", true);
LogMessage($"錯誤詳細資訊：{ex.StackTrace}", true);
MessageBox.Show(
$"備份資料庫時發生錯誤，請參閱操作記錄了解詳細資訊。\n\nError occurred while backing up the database. Please refer to the operation log for detailed information.",
"錯誤 / Error",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
finally
{
// 無論執行結果如何，確保按鈕狀態都會被恢復
SetOperationButtonsState(true);
// 恢復 TraceDataReport Timer
ResumeTraceDataReportTimer();
}
}

private async void btn_RestoreDatabase_Click(object sender, RoutedEventArgs e)
{
// 檢查是否有未保存的變更
if (!CheckUnsavedChanges())
{
return; // 用戶取消操作
}

// 禁用所有按鈕
SetOperationButtonsState(false, "還原資料庫");

try
{
// 暫停 TraceDataReport Timer
PauseTraceDataReportTimer();

LogMessage("準備還原資料庫...");

// 取得當前程式版本
string currentVersion = Assembly.GetExecutingAssembly().GetName().Version.ToString();
LogMessage($"當前程式版本: {currentVersion}");

// 取得最小需求版本號
string minimumRequiredVersion = ((App)Application.Current).GetMinimumRequiredVersion();
LogMessage($"最小需求版本: {minimumRequiredVersion}");

// 使用SQL查詢獲取所有備份資料庫
List<RestorableDatabaseInfo> backupDatabases = new List<RestorableDatabaseInfo>();
using (var connection = new SqlConnection(_masterConnectionString))
{
await connection.OpenAsync();

// 先取得所有備份資料庫名稱
List<string> allBackupDatabases = new List<string>();
// 查詢資料庫名稱和創建時間
using (var command = new SqlCommand(@"
SELECT name, create_date
FROM sys.databases
WHERE name LIKE 'BC_Dev_%'
ORDER BY create_date DESC", connection))
{
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
string dbName = reader.GetString(0);
DateTime createDate = reader.GetDateTime(1);

// 創建包含創建時間的資料庫信息對象
var dbInfo = new RestorableDatabaseInfo
{
DatabaseName = dbName,
CreationTime = createDate,
Version = "未知" // 預設為未知，後續會更新
};

allBackupDatabases.Add(dbName);
}
}
}

// 檢查每個資料庫的版本
foreach (string dbName in allBackupDatabases)
{
try
{
LogMessage($"檢查資料庫 {dbName} 的版本資訊...");

// 檢查資料庫中是否有版本資訊表和欄位
bool hasVersionInfo = false;
using (var tableCommand = new SqlCommand($@"
SELECT COUNT(*)
FROM [{dbName}].INFORMATION_SCHEMA.TABLES t
INNER JOIN [{dbName}].INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_NAME = 'Version'
AND t.TABLE_TYPE = 'BASE TABLE'
AND c.COLUMN_NAME = 'Version'", connection))
{
var count = (int)await tableCommand.ExecuteScalarAsync();
hasVersionInfo = count > 0;
LogMessage($"資料庫 {dbName} 版本資訊檢查結果: {count} 個符合條件的表");
}

if (!hasVersionInfo)
{
// 診斷資訊：列出所有表
using (var tableCommand = new SqlCommand($@"
SELECT TABLE_NAME
FROM [{dbName}].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'", connection))
{
using (var reader = await tableCommand.ExecuteReaderAsync())
{
var tables = new List<string>();
while (await reader.ReadAsync())
{
tables.Add(reader.GetString(0));
}
LogMessage($"資料庫 {dbName} 中的所有表: {string.Join(", ", tables)}");
}
}

// 診斷資訊：檢查 Version 表
using (var tableCommand = new SqlCommand($@"
SELECT COLUMN_NAME
FROM [{dbName}].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Version'", connection))
{
using (var reader = await tableCommand.ExecuteReaderAsync())
{
var columns = new List<string>();
while (await reader.ReadAsync())
{
columns.Add(reader.GetString(0));
}
if (columns.Count > 0)
{
LogMessage($"資料庫 {dbName} 的 Version 表欄位: {string.Join(", ", columns)}");
}
}
}

LogMessage($"備份資料庫 {dbName} 找不到版本資訊表或 Version 欄位");
continue;
}

// 檢查版本資訊
using (var versionCommand = new SqlCommand($@"
USE [{dbName}];
SELECT Version FROM Version", connection))
{
var dbVersion = await versionCommand.ExecuteScalarAsync();

// 修改版本檢查條件：從完全相同改為符合最小需求版本
if (dbVersion != null)
{
try
{
// 比較版本
Version dbVer = new Version(dbVersion.ToString());
Version minRequiredVer = new Version(minimumRequiredVersion);
Version currentVer = new Version(currentVersion);

// 創建資料庫信息對象
var dbInfo = new RestorableDatabaseInfo
{
DatabaseName = dbName,
Version = dbVersion.ToString(),
CreationTime = DateTime.MinValue // 預設值，後續會更新
};

// 獲取資料庫創建時間
using (var createDateCommand = new SqlCommand($@"
SELECT create_date
FROM sys.databases
WHERE name = '{dbName}'", connection))
{
var createDateObj = await createDateCommand.ExecuteScalarAsync();
if (createDateObj != null && createDateObj != DBNull.Value)
{
dbInfo.CreationTime = (DateTime)createDateObj;
}
}

// 如果資料庫版本大於等於最小需求版本且小於等於當前系統版本，則允許還原
if (dbVer >= minRequiredVer && dbVer <= currentVer)
{
backupDatabases.Add(dbInfo);
LogMessage($"資料庫 {dbName} 版本符合要求: {dbVersion} (最小需求: {minimumRequiredVersion}, 當前系統: {currentVersion})");
}
else if (dbVer < minRequiredVer)
{
LogMessage($"備份資料庫 {dbName} 版本低於最小需求版本 (資料庫版本: {dbVersion}, 最小需求版本: {minimumRequiredVersion})");
}
else if (dbVer > currentVer)
{
LogMessage($"備份資料庫 {dbName} 版本高於當前系統版本 (資料庫版本: {dbVersion}, 系統版本: {currentVersion})");
}
}
catch (Exception ex)
{
LogMessage($"解析資料庫 {dbName} 版本時發生錯誤: {ex.Message}", true);
}
}
else
{
LogMessage($"備份資料庫 {dbName} 版本資訊為空");
}
}
}
catch (Exception ex)
{
LogMessage($"檢查資料庫 {dbName} 版本時發生錯誤: {ex.Message}", true);
}
}
}

if (backupDatabases.Count == 0)
{
MessageBox.Show(
"找不到符合版本要求的備份資料庫。\n" +
$"版本要求：大於等於最小需求版本 ({minimumRequiredVersion})\n" +
$"且小於等於當前系統版本 ({currentVersion})\n" +
"請確認是否有正確的備份資料庫。\n\n" +
"No backup database meeting version requirements found.\n" +
$"Version requirement: Greater than or equal to minimum required version ({minimumRequiredVersion}) \n" +
$"and less than or equal to current system version ({currentVersion})\n" +
"Please verify if you have the correct backup database.",
"找不到備份 / No Backup Found",
MessageBoxButton.OK,
MessageBoxImage.Information);
LogMessage("找不到任何符合版本要求的備份資料庫");
return;
}

// 建立選擇視窗
var selectWindow = new Window
{
Title = "選擇要還原的備份資料庫 (顯示版本和建立時間)",
Width = 500,
Height = 400,
WindowStartupLocation = WindowStartupLocation.CenterScreen,
ResizeMode = ResizeMode.NoResize
};

var grid = new Grid();
grid.RowDefinitions.Add(new RowDefinition { Height = new GridLength(1, GridUnitType.Star) });
grid.RowDefinitions.Add(new RowDefinition { Height = new GridLength(70) });

var listBox = new ListBox
{
Margin = new Thickness(10),
ItemsSource = backupDatabases,
FontSize = 12
};
Grid.SetRow(listBox, 0);

// 自定義ListBox項的顯示模板
DataTemplate itemTemplate = new DataTemplate();
var stackPanel = new FrameworkElementFactory(typeof(StackPanel));
stackPanel.SetValue(StackPanel.OrientationProperty, Orientation.Vertical);
stackPanel.SetValue(StackPanel.MarginProperty, new Thickness(5));

var dbNameTextBlock = new FrameworkElementFactory(typeof(TextBlock));
dbNameTextBlock.SetBinding(TextBlock.TextProperty, new Binding("DatabaseName"));
dbNameTextBlock.SetValue(TextBlock.FontWeightProperty, FontWeights.Bold);
dbNameTextBlock.SetValue(TextBlock.FontSizeProperty, 14.0);
stackPanel.AppendChild(dbNameTextBlock);

var versionTextBlock = new FrameworkElementFactory(typeof(TextBlock));
versionTextBlock.SetBinding(TextBlock.TextProperty, new Binding("Version") { StringFormat = "版本: {0}" });
stackPanel.AppendChild(versionTextBlock);

var creationTimeTextBlock = new FrameworkElementFactory(typeof(TextBlock));
creationTimeTextBlock.SetBinding(TextBlock.TextProperty, new Binding("CreationTime") { StringFormat = "建立時間: {0:yyyy-MM-dd HH:mm:ss}" });
stackPanel.AppendChild(creationTimeTextBlock);

itemTemplate.VisualTree = stackPanel;
listBox.ItemTemplate = itemTemplate;

var buttonPanel = new StackPanel
{
Orientation = Orientation.Horizontal,
HorizontalAlignment = HorizontalAlignment.Right,
Margin = new Thickness(10)
};
Grid.SetRow(buttonPanel, 1);

var okButton = new Button
{
Content = "確定",
Width = 80,
Height = 30,
Margin = new Thickness(5),
IsEnabled = false
};

var cancelButton = new Button
{
Content = "取消",
Width = 80,
Height = 30,
Margin = new Thickness(5)
};

buttonPanel.Children.Add(okButton);
buttonPanel.Children.Add(cancelButton);

grid.Children.Add(listBox);
grid.Children.Add(buttonPanel);

selectWindow.Content = grid;

listBox.SelectionChanged += (s, args) =>
{
okButton.IsEnabled = listBox.SelectedItem != null;
};

okButton.Click += (s, args) =>
{
selectWindow.DialogResult = true;
selectWindow.Close();
};

cancelButton.Click += (s, args) =>
{
selectWindow.DialogResult = false;
selectWindow.Close();
};

if (selectWindow.ShowDialog() == true && listBox.SelectedItem != null)
{
var selectedDb = (RestorableDatabaseInfo)listBox.SelectedItem;
string selectedDatabase = selectedDb.DatabaseName;

// 確認是否要覆蓋現有資料庫
var result = MessageBox.Show(
$"還原資料庫將會覆蓋現有的 BC_Dev 資料庫。\n" +
$"選擇的備份資料庫：{selectedDatabase}\n" +
$"版本：{selectedDb.Version}\n" +
$"建立時間：{selectedDb.CreationTime:yyyy-MM-dd HH:mm:ss}\n\n" +
$"註：系統只允許還原符合以下版本要求的資料庫：\n" +
$" 大於等於最小需求版本 ({((App)Application.Current).GetMinimumRequiredVersion()}) 且\n" +
$" 小於等於當前系統版本 ({Assembly.GetExecutingAssembly().GetName().Version})\n\n" +
$"是否確定要繼續？\n\n" +
$"Restoring database will overwrite existing BC_Dev database.\n" +
$"Selected backup database: {selectedDatabase}\n" +
$"Version: {selectedDb.Version}\n" +
$"Creation time: {selectedDb.CreationTime:yyyy-MM-dd HH:mm:ss}\n\n" +
$"Note: System only allows restoring databases that meet the following version requirements:\n" +
$" Greater than or equal to minimum required version ({((App)Application.Current).GetMinimumRequiredVersion()}) and\n" +
$" Less than or equal to current system version ({Assembly.GetExecutingAssembly().GetName().Version})\n\n" +
$"Are you sure to continue?",
"確認 / Confirm",
MessageBoxButton.YesNo,
MessageBoxImage.Warning);

if (result == MessageBoxResult.Yes)
{
LogMessage($"開始還原資料庫 {selectedDatabase}...");

// 檢查ACJID和APJID表是否有資料
if (!CheckAccountTables())
{
LogMessage("使用者因資料表風險取消操作");
return;
}
// 使用進度報告和取消令牌
var progressIndicator = new Progress<string>(message =>
{
LogMessage(message);
// 在長時間運行的操作中使用 DoEvents 讓 UI 有機會更新
DoEvents();
});

// 使用 CancellationTokenSource 支持操作取消
var cts = new CancellationTokenSource();

try
{
// 使用 Task.Run 執行資料庫還原，以防止 UI 凍結
await Task.Run(async () =>
{
// 執行還原操作前報告進度
LogMessage("執行資料庫還原操作...");
await RestoreDatabase(selectedDatabase);
// 還原完成後直接使用 LogMessage，而不是嘗試使用 progressIndicator
LogMessage("資料庫還原完成");
}, cts.Token);

// 等待資料庫就緒
LogMessage("等待資料庫就緒...");
if (!await WaitForDatabaseConnection(_connectionString, "BC_Dev", 30, 1000, 10000))
{
throw new Exception("無法連接到還原後的資料庫，請檢查資料庫狀態");
}

// 還原成功後檢查欄位一致性
bool hasInconsistentFields = false;
bool checkFailed = false;
string errorMessage = string.Empty;

try
{
LogMessage("還原資料庫成功，等待資料庫就緒...");

// 最多重試 5 次，每次等待 1 秒
const int maxRetries = 5;
const int retryDelayMs = 1000;
bool checkSuccess = false;

for (int retry = 0; retry < maxRetries; retry++)
{
try
{
// 先嘗試建立連線測試資料庫是否可用
using (var conn = new SqlConnection(_connectionString))
{
await conn.OpenAsync();
// 如果能成功連線，則執行一致性檢查
LogMessage("資料庫連線成功，開始檢查欄位定義一致性...");
hasInconsistentFields = CheckFieldConsistency(false);
checkSuccess = true;
break;
}
}
catch (Exception retryEx)
{
if (retry < maxRetries - 1)
{
LogMessage($"等待資料庫就緒中... (重試 {retry + 1}/{maxRetries})", true);
await Task.Delay(retryDelayMs);
}
else
{
throw; // 最後一次重試失敗，拋出例外
}
}
}

if (!checkSuccess)
{
throw new Exception("資料庫連線重試次數已達上限，無法完成一致性檢查");
}
}
catch (Exception ex)
{
checkFailed = true;
errorMessage = ex.Message;
LogMessage($"還原後檢查欄位一致性時發生錯誤: {ex.Message}", true);
}

// 根據檢查結果顯示不同的訊息
string notificationMessage;
MessageBoxImage notificationIcon;
string notificationTitle;

if (checkFailed)
{
notificationMessage = $"資料庫還原完成，但欄位一致性檢查失敗：\n{errorMessage}\n\n" +
$"建議您重新啟動程式後，再次執行一致性檢查。\n\n" +
$"系統將重新啟動以套用變更。\n\n" +
$"Database restore completed, but field consistency check failed:\n{errorMessage}\n\n" +
$"It is recommended to restart the program and perform the consistency check again.\n\n" +
$"The system will restart to apply changes.";
notificationIcon = MessageBoxImage.Warning;
notificationTitle = "還原完成但有錯誤 / Restore Completed with Errors";
}
else if (hasInconsistentFields)
{
notificationMessage = $"資料庫還原完成，但發現欄位定義不一致的情況，詳情請查看日誌報告。\n\n" +
$"系統將重新啟動以套用變更。\n\n" +
$"Database restore completed, but field definition inconsistencies were found. Please check the log report for details.\n\n" +
$"The system will restart to apply changes.";
notificationIcon = MessageBoxImage.Warning;
notificationTitle = "還原完成但有警告 / Restore Completed with Warnings";
}
else
{
notificationMessage = $"資料庫還原成功完成！\n\n" +
$"系統將重新啟動以套用變更。\n\n" +
$"Database restore completed successfully!\n\n" +
$"The system will restart to apply changes.";
notificationIcon = MessageBoxImage.Information;
notificationTitle = "還原成功 / Restore Successful";
}

MessageBox.Show(
notificationMessage,
notificationTitle,
MessageBoxButton.OK,
notificationIcon,
MessageBoxResult.OK,
MessageBoxOptions.DefaultDesktopOnly);

ShutdownApplication();
}
catch (Exception ex)
{
// 記錄錯誤
LogMessage($"還原資料庫時發生錯誤: {ex.Message}", true);
LogMessage($"錯誤詳細資訊: {ex.StackTrace}", true);

// 顯示錯誤訊息給使用者
MessageBox.Show(
$"還原資料庫時發生錯誤。\n請查看操作記錄了解詳細資訊。\n\nError occurred while restoring the database. Please refer to the operation log for detailed information.",
"錯誤 / Error",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
else
{
// 用戶取消覆蓋操作
LogMessage("使用者取消還原資料庫操作");
}
}
else
{
// 用戶取消選擇資料庫操作
LogMessage("使用者取消選擇備份資料庫");
}
}
catch (Exception ex)
{
LogMessage($"還原資料庫時發生錯誤: {ex.Message}", true);
MessageBox.Show(
$"還原資料庫時發生錯誤，請參閱操作記錄了解詳細資訊。\n\nError occurred while restoring the database. Please refer to the operation log for detailed information.",
"錯誤 / Error",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
finally
{
// 無論執行結果如何，確保按鈕狀態都會被恢復
SetOperationButtonsState(true);
// 恢復 TraceDataReport Timer
ResumeTraceDataReportTimer();
}
}


/// <summary>
/// 處理清除日誌按鈕點擊事件
/// </summary>
private void MenuItem_ClearLog_Click(object sender, RoutedEventArgs e)
{
// 詢問用戶是否確定要清除日誌
if (MessageBox.Show("確定要清除所有日誌記錄嗎？\n\nAre you sure you want to clear all log records?", "確認清除 / Confirm Clear", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
ClearLog();
LogMessage("日誌已被手動清除");
}
}

/// <summary>
/// 處理複製命令
/// </summary>
private void CommandBinding_Copy_Executed(object sender, ExecutedRoutedEventArgs e)
{
try
{
TextBox textBox = sender as TextBox;
if (textBox != null)
{
// 檢查是否有選取文字
if (textBox.SelectionLength > 0)
{
// 只複製選取的部分
string selectedText = textBox.SelectedText;
Clipboard.SetText(selectedText);
}
else
{
// 如果沒有選取文字，則不執行任何動作
e.Handled = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show("複製時發生錯誤: " + ex.Message, "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

private void CommandBinding_SelectAll_Executed(object sender, ExecutedRoutedEventArgs e)
{
try
{
TextBox textBox = sender as TextBox;
if (textBox != null)
{
// 執行全選前先確認使用者是否真的需要全選
if (MessageBox.Show("是否要全選所有日誌內容？\n\nAre you sure you want to select all log content?", "確認全選 / Confirm Select",
MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
textBox.SelectAll();
}
else
{
e.Handled = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show("全選時發生錯誤: " + ex.Message + "\n\nError occurred while selecting all: " + ex.Message, "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

private void TxtLog_PreviewKeyUp(object sender, KeyEventArgs e)
{
try
{
TextBox textBox = sender as TextBox;
if (textBox != null)
{
// 處理Ctrl+C
if (e.Key == Key.C && (Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control)
{
if (textBox.SelectionLength > 0)
{
string selectedText = textBox.SelectedText;
Clipboard.SetText(selectedText);
}
e.Handled = true;
}
// 處理Ctrl+A
else if (e.Key == Key.A && (Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control)
{
if (MessageBox.Show("是否要全選所有日誌內容？\n\nAre you sure you want to select all log content?", "確認全選 / Confirm Select",
MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
textBox.SelectAll();
}
e.Handled = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show("按鍵處理時發生錯誤: " + ex.Message + "\n\nError occurred while processing key: " + ex.Message, "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

// 新增一個方法，用於更新控件值發生變更時設置標記
private void MarkAsChanged(object sender, EventArgs e)
{
if (!_isOperationInProgress) // 只有在沒有操作進行時才標記為變更
{
_hasUnsavedChanges = true;
// 移除此處的日誌記錄，避免每次輸入字符都產生日誌
// LogMessage("界面設定已變更但尚未保存");
}
}

/// <summary>
/// 檢查是否有未保存的變更並詢問用戶如何處理
/// </summary>
/// <returns>
/// true: 繼續操作 (保存變更或用戶確認使用舊值)
/// false: 取消操作
/// </returns>
private bool CheckUnsavedChanges()
{
if (!_hasUnsavedChanges)
return true; // 沒有未保存的變更，繼續操作

var result = MessageBox.Show(
"您有未保存的數據庫設定變更。\n\n" +
"是否保存這些變更後再繼續？\n" +
"「是」 - 保存變更後繼續\n" +
"「否」 - 使用原設定值繼續\n" +
"「取消」 - 終止操作\n\n" +
"You have unsaved database setting changes.\n\n" +
"Do you want to save these changes before continuing?\n" +
"'Yes' - Save changes and continue\n" +
"'No' - Continue with original settings\n" +
"'Cancel' - Abort operation",
"未保存的變更 / Unsaved Changes",
MessageBoxButton.YesNoCancel,
MessageBoxImage.Question);

switch (result)
{
case MessageBoxResult.Yes:
// 保存變更
try
{
if (!ValidateDatabaseSettings())
return false; // 驗證失敗，不繼續操作

// 更新連線字串
UpdateConnectionStrings();

// 執行保存設定操作
string ip = txtDbIP.Text.Trim();
string port = txtDbPort.Text.Trim();
string dataSource = $"{ip},{port}";
var builder = new SqlConnectionStringBuilder
{
DataSource = dataSource,
InitialCatalog = txtDbName.Text,
UserID = txtDbID.Text,
Password = txtDbPassword.Text,
MinPoolSize = int.Parse(txtMinPoolSize.Text),
MaxPoolSize = int.Parse(txtMaxPoolSize.Text)
};

string connString = builder.ConnectionString;

// 確保目錄存在
string configDir = IOPath.GetDirectoryName(_configPath);
if (!Directory.Exists(configDir))
{
Directory.CreateDirectory(configDir);
}

// 讀取現有的 XML 檔案
XDocument doc;
if (IOFile.Exists(_configPath))
{
doc = XDocument.Load(_configPath);
}
else
{
// 建立新的 XML 文件
doc = new XDocument(
new XElement("hibernate-configuration",
new XElement("session-factory")
)
);
}

// 取得命名空間
XNamespace ns = doc.Root.GetDefaultNamespace();

// 尋找或建立 session-factory 元素
var sessionFactory = doc.Root.Element(ns + "session-factory");
if (sessionFactory == null)
{
sessionFactory = new XElement(ns + "session-factory");
doc.Root.Add(sessionFactory);
}

// 尋找或建立 connection.connection_string 屬性
var propertyElement = sessionFactory.Elements(ns + "property")
.FirstOrDefault(p => p.Attribute("name")?.Value == "connection.connection_string");

if (propertyElement == null)
{
propertyElement = new XElement(ns + "property",
new XAttribute("name", "connection.connection_string")
);
sessionFactory.Add(propertyElement);
}

// 更新連線字串值
propertyElement.Value = connString;

// 儲存檔案
doc.Save(_configPath);

LogMessage("自動保存了界面設定變更");
_hasUnsavedChanges = false;
return true;
}
catch (Exception ex)
{
LogMessage($"自動保存設定時發生錯誤：{ex.Message}", true);
MessageBox.Show($"保存設定時發生錯誤：{ex.Message}\n操作已取消\n\nError occurred while saving settings: {ex.Message}\nOperation cancelled", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}

case MessageBoxResult.No:
// 使用原設定值繼續
LogMessage("用戶選擇使用原設定值繼續操作");
return true;

case MessageBoxResult.Cancel:
default:
// 取消操作
LogMessage("用戶取消了操作");
return false;
}
}

// 在窗體加載完成時優化UI元素
protected override void OnContentRendered(EventArgs e)
{
base.OnContentRendered(e);

// 不做任何特殊處理，讓WPF自己管理焦點
}

// 保留搜索方法，但只用於必要地方
private IEnumerable<T> FindVisualChildren<T>(DependencyObject depObj) where T : DependencyObject
{
if (depObj != null)
{
// 直接使用 LINQ 和 LogicalTreeHelper 替代 VisualTreeHelper
var childrenCount = LogicalTreeHelper.GetChildren(depObj);
foreach (var child in childrenCount)
{
if (child is DependencyObject dependencyObject)
{
if (child is T t)
{
yield return t;
}

// 遞歸處理子元素
foreach (T childOfChild in FindVisualChildren<T>(dependencyObject))
{
yield return childOfChild;
}
}
}
}
}

// 當關閉窗口時檢查是否有未保存的變更
protected override void OnClosing(System.ComponentModel.CancelEventArgs e)
{
// 如果用戶點擊窗口關閉按鈕，則檢查是否有未保存的變更
if (_hasUnsavedChanges)
{
var result = MessageBox.Show(
"您有未保存的數據庫設定變更。\n" +
"是否保存這些變更後再關閉？\n" +
"「是」 - 保存變更後關閉\n" +
"「否」 - 不保存變更直接關閉\n" +
"「取消」 - 不關閉視窗\n\n" +
"You have unsaved database settings changes.\n" +
"Do you want to save these changes before closing?\n" +
"\"Yes\" - Save changes and close\n" +
"\"No\" - Close without saving changes\n" +
"\"Cancel\" - Don't close window",
"未保存的變更 / Unsaved Changes",
MessageBoxButton.YesNoCancel,
MessageBoxImage.Question);

switch (result)
{
case MessageBoxResult.Yes:
// 保存變更
try
{
if (!ValidateDatabaseSettings())
{
e.Cancel = true; // 取消關閉視窗
return;
}

// 更新連線字串
UpdateConnectionStrings();

// 執行保存設定操作
string ip = txtDbIP.Text.Trim();
string port = txtDbPort.Text.Trim();
string dataSource = $"{ip},{port}";
var builder = new SqlConnectionStringBuilder
{
DataSource = dataSource,
InitialCatalog = txtDbName.Text,
UserID = txtDbID.Text,
Password = txtDbPassword.Text,
MinPoolSize = int.Parse(txtMinPoolSize.Text),
MaxPoolSize = int.Parse(txtMaxPoolSize.Text)
};

string connString = builder.ConnectionString;

// 確保目錄存在
string configDir = IOPath.GetDirectoryName(_configPath);
if (!Directory.Exists(configDir))
{
Directory.CreateDirectory(configDir);
}

// 讀取現有的 XML 檔案
XDocument doc;
if (IOFile.Exists(_configPath))
{
doc = XDocument.Load(_configPath);
}
else
{
// 建立新的 XML 文件
doc = new XDocument(
new XElement("hibernate-configuration",
new XElement("session-factory")
)
);
}

// 取得命名空間
XNamespace ns = doc.Root.GetDefaultNamespace();

// 尋找或建立 session-factory 元素
var sessionFactory = doc.Root.Element(ns + "session-factory");
if (sessionFactory == null)
{
sessionFactory = new XElement(ns + "session-factory");
doc.Root.Add(sessionFactory);
}

// 尋找或建立 connection.connection_string 屬性
var propertyElement = sessionFactory.Elements(ns + "property")
.FirstOrDefault(p => p.Attribute("name")?.Value == "connection.connection_string");

if (propertyElement == null)
{
propertyElement = new XElement(ns + "property",
new XAttribute("name", "connection.connection_string")
);
sessionFactory.Add(propertyElement);
}

// 更新連線字串值
propertyElement.Value = connString;

// 儲存檔案
doc.Save(_configPath);

LogMessage("關閉視窗前已自動保存設定變更");
_hasUnsavedChanges = false;
}
catch (Exception ex)
{
LogMessage($"自動保存設定時發生錯誤：{ex.Message}", true);

// 詢問用戶是否仍要關閉視窗
var errorResult = MessageBox.Show($"保存設定時發生錯誤：{ex.Message}\n\n是否仍要關閉視窗？\n\nError occurred while saving settings: {ex.Message}\n\nDo you still want to close the window?",
"錯誤 / Error", MessageBoxButton.YesNo, MessageBoxImage.Error);

if (errorResult != MessageBoxResult.Yes)
{
e.Cancel = true; // 取消關閉視窗
}
}
break;

case MessageBoxResult.No:
// 不保存直接關閉
_hasUnsavedChanges = false;
break;

case MessageBoxResult.Cancel:
default:
// 取消關閉
e.Cancel = true;
break;
}
}

base.OnClosing(e);
}

// 當點擊窗體空白處時，讓當前擁有焦點的文本框失去焦點
private void Window_MouseDown(object sender, MouseButtonEventArgs e)
{
try
{
// 獲取點擊的元素
var element = e.OriginalSource as DependencyObject;

// 檢查是否是有效的點擊目標
if (element != null)
{
// 檢查點擊的是否為文本框或其子元素
bool isTextBox = false;

// 檢查點擊的元素或其父元素是否為TextBox
DependencyObject current = element;
while (current != null && !isTextBox)
{
if (current is TextBox)
{
isTextBox = true;
break;
}
// 使用VisualTreeHelper獲取更準確的父元素
current = VisualTreeHelper.GetParent(current);
}

// 如果點擊的不是文本框
if (!isTextBox)
{
// 記錄當前擁有焦點的元素
var focusedElement = Keyboard.FocusedElement as UIElement;

// 如果當前焦點在文本框上
if (focusedElement is TextBox)
{
// 將焦點轉移到窗體
this.Focus();
Keyboard.ClearFocus();

// 不要標記為已處理，允許事件繼續傳遞
}
}
}
}
catch (Exception ex)
{
// 記錄異常但不顯示給用戶，避免影響用戶體驗
_logger.Error($"處理窗體點擊事件時發生錯誤: {ex.Message}");
}
}

/// <summary>
/// 檢查欄位定義不一致的按鈕點擊事件處理函數
/// </summary>
private async void btn_CheckFieldConsistency_Click(object sender, RoutedEventArgs e)
{
// 禁用所有按鈕
SetOperationButtonsState(false, "檢查欄位定義");

try
{
if (!ValidateDatabaseSettings())
{
return;
}

UpdateConnectionStrings();
ClearLog();
LogMessage("開始檢查資料庫欄位定義一致性");

bool hasInconsistentFields = false;

// 使用 Task.Run 將資料庫操作移到背景執行緒
hasInconsistentFields = await Task.Run(() => CheckFieldConsistency(true));

if (hasInconsistentFields)
{
MessageBox.Show("發現欄位定義不一致的情況，詳情請查看日誌報告。\n\nField definition inconsistencies found. Please check the log report for details.", "檢查結果 / Check Result", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
MessageBox.Show("未發現欄位定義不一致的情況。\n\nNo field definition inconsistencies found.", "檢查結果 / Check Result", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
LogMessage($"檢查欄位定義失敗: {ex.Message}", true);
MessageBox.Show($"檢查欄位定義失敗。\n請參閱操作記錄了解詳細錯誤資訊。\n\nField definition check failed.\nPlease refer to the operation log for detailed error information.", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
// 無論執行結果如何，確保按鈕狀態都會被恢復
SetOperationButtonsState(true);
}
}

/// <summary>
/// 檢查資料庫中欄位名稱相同但類型或長度不同的情況
/// </summary>
/// <param name="showSuccessMessage">是否顯示成功訊息（獨立按鈕檢查時為true，建立資料庫流程中為false）</param>
/// <returns>是否存在不一致的欄位定義</returns>
private bool CheckFieldConsistency(bool showSuccessMessage = false)
{
LogMessage("開始檢查BC_Dev資料庫所有表格中欄位定義的一致性...");

bool hasInconsistentFields = false;
int totalInconsistencies = 0;

try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
LogMessage("已連接到BC_Dev資料庫");

// 獲取所有欄位定義
var fieldDefinitions = new Dictionary<string, List<(string TableName, string DataType, int MaxLength, bool IsNullable, string DefaultValue)>>();

// 查詢所有的表和欄位
string sql = @"
SELECT
t.name AS TableName,
c.name AS ColumnName,
ty.name AS DataType,
c.max_length AS MaxLength,
c.is_nullable AS IsNullable,
dc.definition AS DefaultValue
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
ORDER BY t.name, c.name";

using (SqlCommand cmd = new SqlCommand(sql, conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string tableName = reader["TableName"].ToString();
string columnName = reader["ColumnName"].ToString();
string dataType = reader["DataType"].ToString();
int maxLength = Convert.ToInt32(reader["MaxLength"]);
bool isNullable = Convert.ToBoolean(reader["IsNullable"]);
string defaultValue = reader["DefaultValue"] == DBNull.Value ? null : reader["DefaultValue"].ToString();

// 針對 nvarchar, nchar 等 Unicode 類型，長度需要除以 2
if (dataType.StartsWith("n") && (dataType.Contains("char") || dataType.Contains("varchar")))
{
maxLength /= 2;
}

if (!fieldDefinitions.ContainsKey(columnName))
{
fieldDefinitions[columnName] = new List<(string, string, int, bool, string)>();
}

fieldDefinitions[columnName].Add((tableName, dataType, maxLength, isNullable, defaultValue));
}
}

// 檢查每個欄位是否有不同的定義
StringBuilder inconsistencyReport = new StringBuilder();
inconsistencyReport.AppendLine("========== 欄位定義不一致報告 ==========");
inconsistencyReport.AppendLine("以下是系統中相同名稱但定義不同的欄位列表：");
inconsistencyReport.AppendLine();

foreach (var entry in fieldDefinitions)
{
string columnName = entry.Key;
var definitions = entry.Value;

// 如果同名欄位出現在多個表格
if (definitions.Count > 1)
{
// 檢查是否有不同的定義
bool hasInconsistency = false;

// 收集所有不同的資料類型和長度
var uniqueDefinitions = new Dictionary<(string DataType, int MaxLength), List<string>>();

foreach (var def in definitions)
{
var key = (def.DataType, def.MaxLength);
if (!uniqueDefinitions.ContainsKey(key))
{
uniqueDefinitions[key] = new List<string>();
}
uniqueDefinitions[key].Add(def.TableName);
}

// 如果有多於一種不同的定義，則記錄不一致
if (uniqueDefinitions.Count > 1)
{
hasInconsistency = true;
hasInconsistentFields = true;
totalInconsistencies++;

// 添加到報告
inconsistencyReport.AppendLine($"【欄位】{columnName}");
inconsistencyReport.AppendLine("--------------------------------------------------------");

int defIndex = 1;
foreach (var uniqueDef in uniqueDefinitions)
{
var (dataType, maxLength) = uniqueDef.Key;
var tables = uniqueDef.Value;

inconsistencyReport.AppendLine($"定義 {defIndex}:");
inconsistencyReport.AppendLine($" - 資料類型: {dataType}");
inconsistencyReport.AppendLine($" - 欄位長度: {maxLength}");
inconsistencyReport.AppendLine($" - 出現於表格: {string.Join(", ", tables)}");
inconsistencyReport.AppendLine($" - 表格數量: {tables.Count}");
inconsistencyReport.AppendLine();

defIndex++;
}

inconsistencyReport.AppendLine("--------------------------------------------------------");
inconsistencyReport.AppendLine();
}
}
}

// 輸出報告
if (hasInconsistentFields)
{
inconsistencyReport.AppendLine($"======= 總共發現 {totalInconsistencies} 個欄位定義不一致 =======");
inconsistencyReport.AppendLine("建議：將相同名稱的欄位統一使用相同的類型和長度，以確保資料庫設計的一致性和避免可能的問題。");
LogMessage(inconsistencyReport.ToString());

// 添加重點提示
LogMessage("注意：欄位定義不一致可能導致以下問題：", false);
LogMessage("1. 資料遷移或複製時可能截斷或轉換失敗", false);
LogMessage("2. 聯結查詢時可能導致效能問題或不正確的結果", false);
LogMessage("3. 程式中使用通用模型時可能發生錯誤", false);

// 自動開啟日誌檔案
try
{
string logPath = @"C:\MPC_Log\DbSettings.log";

if (File.Exists(logPath))
{
Process.Start(new ProcessStartInfo
{
FileName = logPath,
UseShellExecute = true
});
}
else
{
LogMessage($"找不到日誌檔案：{logPath}", true);
}
}
catch (Exception ex)
{
LogMessage($"開啟日誌檔案時發生錯誤: {ex.Message}", true);
}
}
else if (showSuccessMessage) // 只在獨立按鈕檢查時顯示成功訊息
{
LogMessage("檢查完成，所有相同名稱的欄位定義都是一致的。");
}
}
}
catch (Exception ex)
{
LogMessage($"檢查欄位定義時發生錯誤: {ex.Message}", true);
throw;
}

return hasInconsistentFields;
}

/// <summary>
/// 自定義標題欄拖動處理
/// </summary>
private void TitleBar_MouseDown(object sender, MouseButtonEventArgs e)
{
if (e.ChangedButton == MouseButton.Left)
{
this.DragMove(); // 允許拖動窗口
}
}

/// <summary>
/// 檢查ACJID和APJID表格是否有資料，若有則警告使用者有操作風險
/// </summary>
/// <returns>使用者選擇繼續操作則返回true，選擇取消則返回false</returns>
private bool CheckAccountTables()
{
try
{
if (!ValidateDatabaseSettings())
{
return false;
}

UpdateConnectionStrings();

bool hasData = false;
string message = string.Empty;

// 檢查資料庫是否存在
using (SqlConnection conn = new SqlConnection(_masterConnectionString))
{
conn.Open();
string checkDbExistsSql = $"SELECT 1 FROM sys.databases WHERE name = 'BC_Dev'";
using (SqlCommand cmd = new SqlCommand(checkDbExistsSql, conn))
{
object result = cmd.ExecuteScalar();
if (result == null || Convert.ToInt32(result) != 1)
{
// 資料庫不存在，無需檢查表格
return true;
}
}
}

// 資料庫存在，檢查表格中是否有資料
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();

// 檢查ACJID表是否存在並有資料
string checkTableSql = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ACJID') " +
"SELECT COUNT(*) FROM ACJID ELSE SELECT 0";
using (SqlCommand cmd = new SqlCommand(checkTableSql, conn))
{
try
{
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
hasData = true;
message += $"• ACJID表格中有{count}筆資料\n";
}
}
catch (Exception ex)
{
_logger.Error($"檢查ACJID表格時發生錯誤: {ex.Message}");
}
}

// 檢查APJID表是否存在並有資料
checkTableSql = "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'APJID') " +
"SELECT COUNT(*) FROM APJID ELSE SELECT 0";
using (SqlCommand cmd = new SqlCommand(checkTableSql, conn))
{
try
{
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
hasData = true;
message += $"• APJID表格中有{count}筆資料\n";
}
}
catch (Exception ex)
{
_logger.Error($"檢查APJID表格時發生錯誤: {ex.Message}");
}
}
}

// 如果任一表格有資料，顯示警告訊息
if (hasData)
{
string warningMessage = "警告：資料庫中有CJ、PJ的帳，操作可能有風險！\n" +
"Warning: There are CJ and PJ accounts in the database, operation may be risky!\n\n" +
message + "\n" +
"這些操作可能導致資料遺失，建議您先清線後再執行。\n" +
"These operations may cause data loss. It is recommended to clear the line first before proceeding.\n\n" +
"您確定要繼續嗎？\n" +
"Are you sure you want to continue?\n\n" +
"這些操作可能導致資料遺失。建議您先清除資料後再執行操作。\n" +
"This operation may cause data loss. It is recommended to clear the data first and then perform the operation.";

// 記錄警告日誌
LogMessage("【警告】發現資料庫中有CJ、PJ的帳號資料：", true);
LogMessage(message.Replace("• ", "").Replace("\n", " "));

MessageBoxResult result = MessageBox.Show(
warningMessage,
"資料遺失風險警告 / Data Loss Risk Warning",
MessageBoxButton.OKCancel,
MessageBoxImage.Warning);

// 記錄用戶的選擇
if (result == MessageBoxResult.OK)
{
LogMessage("使用者選擇繼續操作，即使知道CJ、PJ資料可能遺失的風險");
}
else
{
LogMessage("使用者因CJ、PJ資料遺失風險而取消操作");
}

return result == MessageBoxResult.OK;
}

// 沒有資料，可以安全執行操作
return true;
}
catch (Exception ex)
{
_logger.Error($"檢查帳號表格時發生錯誤: {ex.Message}");
MessageBox.Show($"檢查資料表時發生錯誤: {ex.Message}\n\nError occurred while checking tables: {ex.Message}", "錯誤 / Error", MessageBoxButton.OK, MessageBoxImage.Error);
return false;
}
}
}
}
(30-30/32)