/// <summary>
/// 將具有指定規範的 DataTable (Username、Password, FirstName, LastName, Site_id, AccRole)上傳至指定的資料表中。
/// 上傳至 Furnacemonitor 的 User_info 和 User_acc_info
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static int UploadUsers(DataTable dt)
{
string connStr = DBTool.GetConnString();
int r = 0;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand sqlCmnd = new SqlCommand("TRUNCATE TABLE UPLOAD_USER_ACC", conn))
{
//先清除暫存表上的資料
sqlCmnd.ExecuteNonQuery();
}
//上傳 dt 內容至 upload_user_acc
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 6000;
//設定要寫入的資料庫
sqlBC.DestinationTableName = "UPLOAD_USER_ACC";
//對應資料行
sqlBC.ColumnMappings.Add("USERNAME", "USERNAME");
sqlBC.ColumnMappings.Add("PASSWORD", "PASSWORD");
sqlBC.ColumnMappings.Add("FIRSTNAME", "FIRSTNAME");
sqlBC.ColumnMappings.Add("LASTNAME", "LASTNAME");
sqlBC.ColumnMappings.Add("SITE_ID", "SITE_ID");
sqlBC.ColumnMappings.Add("ACC_ROLE", "ACC_ROLE");
//開始寫入
sqlBC.WriteToServer(dt);
}
//執行 sql
using (SqlCommand sqlCmnd = new SqlCommand())
{
StringBuilder query = new StringBuilder();
query.AppendLine("");
//建立暫存表
query.AppendLine("IF OBJECT_ID('tempdb..#NEWUSERS') IS NOT NULL DROP TABLE #NEWUSERS");
query.AppendLine("CREATE TABLE #NEWUSERS(");
query.AppendLine("USERNAME VARCHAR(32),");
query.AppendLine("PASSWORD VARCHAR(32),");
query.AppendLine("FIRSTNAME VARCHAR(32),");
query.AppendLine("LASTNAME VARCHAR(32),");
query.AppendLine("SITE_ID VARCHAR(10))");
query.AppendLine("IF OBJECT_ID('tempdb..#INSERED_ACCROLE') IS NOT NULL DROP TABLE #INSERED_ACCROLE");
query.AppendLine("CREATE TABLE #INSERED_ACCROLE(");
query.AppendLine("USERNAME VARCHAR(32),SITE_ID VARCHAR(10),ACC_ROLE VARCHAR(10))");
//把新增的項目加到暫存表
query.AppendLine("insert into #NEWUSERS(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, SITE_ID)");
query.AppendLine("select * from (");
query.AppendLine("select a.USERNAME, a.PASSWORD, a.FIRSTNAME, a.LASTNAME, a.SITE_ID from UPLOAD_USER_ACC a, ");
query.AppendLine("(select USERNAME, SITE_ID from UPLOAD_USER_ACC");
query.AppendLine("except");
query.AppendLine("select USERNAME, SITE_ID from USERS) b");
query.AppendLine("where a.USERNAME = b.USERNAME and a.SITE_ID = b.SITE_ID) a");
//把要新增的項目與角色加入暫存表
query.AppendLine("INSERT INTO #INSERED_ACCROLE(USERNAME,SITE_ID, ACC_ROLE)");
query.AppendLine("SELECT * FROM ( ");
query.AppendLine("SELECT a.USERNAME,a.SITE_ID, a.ACC_ROLE FROM UPLOAD_USER_ACC a, #NEWUSERS b");
query.AppendLine("WHERE a.USERNAME = b.USERNAME AND a.SITE_ID = b.SITE_ID ) a");
//新增 User
query.AppendLine("INSERT INTO USERS(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, SITE_ID)");
query.AppendLine("SELECT USERNAME, PASSWORD, FIRSTNAME, LASTNAME, SITE_ID FROM #NEWUSERS");
//建立關係
query.AppendLine("INSERT INTO USER_ACC_INFO (USER_INDEX, ACC_INDEX)");
query.AppendLine("SELECT * FROM (");
query.AppendLine("SELECT a.USER_INDEX, c.ACC_INDEX FROM USERS a, #INSERED_ACCROLE b, ACCOUNTS c ");
query.AppendLine("WHERE a.USERNAME = b.USERNAME AND a.SITE_ID = b.SITE_ID AND b.ACC_ROLE = c.NAME) a");
//刪除暫存表
query.AppendLine("DROP TABLE #NEWUSERS");
query.AppendLine("DROP TABLE #INSERED_ACCROLE");
//建立暫存表
query.AppendLine("IF OBJECT_ID('tempdb..#DELINDEX') IS NOT NULL DROP TABLE #DELINDEX");
query.AppendLine("CREATE TABLE #DELINDEX (DEL_INDEX VARCHAR(32))");
//取得要刪除的User資訊
query.AppendLine("INSERT INTO #DELINDEX (DEL_INDEX)");
query.AppendLine("SELECT USER_INDEX FROM USERS a, ");
query.AppendLine("(SELECT USERNAME, SITE_ID FROM USERS");
query.AppendLine("EXCEPT");
query.AppendLine("SELECT USERNAME, SITE_ID FROM UPLOAD_USER_ACC) b");
query.AppendLine("WHERE a.USERNAME = b.USERNAME and a.SITE_ID = b.SITE_ID");
//刪除不存在的帳號與關聯
query.AppendLine("DELETE FROM USERS WHERE USER_INDEX in (SELECT DEL_INDEX FROM #DELINDEX)");
query.AppendLine("DELETE FROM USER_ACC_INFO WHERE USER_INDEX in (SELECT DEL_INDEX FROM #DELINDEX)");
//刪除暫存表
query.AppendLine("DROP TABLE #DELINDEX");
//更新帳號基本資訊(First name/Last name)
query.AppendLine("update USERS ");
query.AppendLine("set USERS.FIRSTNAME = b.FIRSTNAME, USERS.LASTNAME = b.LASTNAME");
query.AppendLine("from USERS a ");
query.AppendLine("inner join UPLOAD_USER_ACC b on ");
query.AppendLine("a.USERNAME = b.USERNAME and a.SITE_ID = b.SITE_ID and (a.FIRSTNAME!=b.FIRSTNAME OR a.LASTNAME!= b.LASTNAME)");
//更新關聯
query.AppendLine("update USER_ACC_INFO");
query.AppendLine("set USER_ACC_INFO.ACC_INDEX = b.ACC_INDEX");
query.AppendLine("from USER_ACC_INFO a ");
query.AppendLine("join (");
query.AppendLine("select a.USERNAME, a.SITE_ID, c.USER_INDEX, b.ACC_INDEX from UPLOAD_USER_ACC a ");
query.AppendLine("inner join ACCOUNTS b on a.ACC_ROLE = b.NAME");
query.AppendLine("inner join USERS c on a.USERNAME = c.USERNAME and a.SITE_ID = c.SITE_ID ");
query.AppendLine(") b on a.USER_INDEX = b.USER_INDEX");
query.AppendLine("where a.ACC_INDEX!=b.ACC_INDEX");
sqlCmnd.Connection = conn;
sqlCmnd.CommandText = query.ToString();
r = sqlCmnd.ExecuteNonQuery();
}
}
return(r);
}
