/// <summary>
/// 編輯資料庫內的界限
/// </summary>
/// <param name="obj"></param>
private int EditLimit(object obj)
{
int effectedRows = 0;
if (FurnItemLimSrc == null || FurnItemLimSrc.Rows.Count == 0)
{
return(0);
}
//檢查每一列都是合法的資料(時間、上下界均為數值)
const double missingvalue = Mtblib.Tools.MtbTools.MISSINGVALUE;
double lsl = missingvalue, usl = missingvalue, lcl = missingvalue, ucl = missingvalue, value = missingvalue;
DateTime applyDate;
foreach (DataRow dr in FurnItemLimSrc.Rows)
{
if (!DateTime.TryParse(dr["APPLY_DATE"].ToString(), out applyDate))
{
throw new Exception("套用時間欄位必須填入時間格式資料");
}
Tool.LimitInformation limits = Tool.LimitStringConverter(
dr["LCL"].ToString(), dr["UCL"].ToString(), dr["LSL"].ToString(), dr["USL"].ToString());
}
//套用時間不可重複
if (FurnItemLimSrc.AsEnumerable().Select(x => x.Field <DateTime>("APPLY_DATE")).Distinct().Count() < FurnItemLimSrc.Rows.Count)
{
throw new Exception("套用時間欄位中有重複的資料");
}
//建立上傳資料
DataTable uploadTable = new DataTable();
uploadTable.Columns.Add("FURN_ITEM_LIM_INDEX", typeof(string));
uploadTable.Columns.Add("LCL", typeof(double));
uploadTable.Columns.Add("UCL", typeof(double));
uploadTable.Columns.Add("LSL", typeof(double));
uploadTable.Columns.Add("USL", typeof(double));
uploadTable.Columns.Add("APPLY_DATE", typeof(DateTime));
foreach (DataRow dr in FurnItemLimSrc.Rows)
{
DataRow newDr = uploadTable.NewRow();
newDr["FURN_ITEM_LIM_INDEX"] = dr["FURN_ITEM_LIM_INDEX"];
newDr["LCL"] = dr["LCL"];
newDr["UCL"] = dr["UCL"];
newDr["LSL"] = dr["LSL"];
newDr["USL"] = dr["USL"];
newDr["APPLY_DATE"] = dr["APPLY_DATE"];
uploadTable.Rows.Add(newDr);
}
//將資料上傳至 UPLOAD_UPDATE_FURN_ITEM_LIMIT_INFO
using (SqlConnection conn = new SqlConnection(Database.DBQueryTool.GetConnString()))
{
conn.Open();
using (SqlCommand sqlCmnd = new SqlCommand("TRUNCATE TABLE UPLOAD_UPDATE_FURN_ITEM_LIMIT_INFO", conn))
{
//先清除暫存表上的資料
sqlCmnd.ExecuteNonQuery();
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 6000;
//設定要寫入的資料庫
sqlBC.DestinationTableName = "UPLOAD_UPDATE_FURN_ITEM_LIMIT_INFO";
//對應資料行
sqlBC.ColumnMappings.Add("FURN_ITEM_LIM_INDEX", "FURN_ITEM_LIM_INDEX");
sqlBC.ColumnMappings.Add("LCL", "LCL");
sqlBC.ColumnMappings.Add("UCL", "UCL");
sqlBC.ColumnMappings.Add("LSL", "LSL");
sqlBC.ColumnMappings.Add("USL", "USL");
sqlBC.ColumnMappings.Add("APPLY_DATE", "APPLY_DATE");
//開始寫入
sqlBC.WriteToServer(uploadTable);
}
using (SqlCommand sqlCmnd = new SqlCommand())
{
StringBuilder query = new StringBuilder();
query.Clear();
query.AppendLine("UPDATE FURN_ITEM_LIMIT_INFO ");
query.AppendLine("SET LCL = a.LCL, UCL= a.UCL, LSL= a.LSL, USL=a.USL, APPLY_DATE = a.APPLY_DATE,RPT_DATE=GETDATE()");
query.AppendLine("FROM (");
query.AppendLine("SELECT * FROM UPLOAD_UPDATE_FURN_ITEM_LIMIT_INFO");
query.AppendLine("EXCEPT");
query.AppendLine("SELECT FURN_ITEM_LIM_INDEX, LCL, UCL, LSL, USL, APPLY_DATE FROM FURN_ITEM_LIMIT_INFO");
query.AppendLine(") a");
query.AppendLine("WHERE a.FURN_ITEM_LIM_INDEX = FURN_ITEM_LIMIT_INFO.FURN_ITEM_LIM_INDEX");
sqlCmnd.Connection = conn;
sqlCmnd.CommandText = query.ToString();
effectedRows = sqlCmnd.ExecuteNonQuery();
}
}
return(effectedRows);
}
/// <summary>
/// 批次新增界限至資料庫
/// </summary>
/// <param name="obj"></param>
private int BatchAddLimit(object obj)
{
int effectedRows = 0;
DataTable dtSrc = Database.DBQueryTool.ReadCSVFile(SelectedFilePath);
if (dtSrc == null || dtSrc.Rows.Count == 0)
{
throw new Exception("檔案無內容");
}
//因為用OLE取得的 DataTable 可能因資料而產生不適合的資料欄位，
//又因 DataTable 填入資料後無法改變資料型別，所以另外複製做為
//上傳程序使用。
DataTable dt = dtSrc.Clone();
dt.Columns["LCL"].DataType = typeof(double); dt.Columns["LCL"].AllowDBNull = true;
dt.Columns["UCL"].DataType = typeof(double); dt.Columns["UCL"].AllowDBNull = true;
dt.Columns["LSL"].DataType = typeof(double); dt.Columns["LSL"].AllowDBNull = true;
dt.Columns["USL"].DataType = typeof(double); dt.Columns["USL"].AllowDBNull = true;
//逐筆檢查資料的合法性
//LCL~USL 須為數值，且大小關係要正確
string itemName;
DateTime appDate;
foreach (DataRow dr in dtSrc.Rows)
{
itemName = dr["TAG_NAME"].ToString();
if (itemName == "")
{
throw new Exception("TAG_NAME 不可為空");
}
//檢查套用時間
if (!DateTime.TryParse(dr["APPLY_DATE"].ToString(), out appDate))
{
throw new Exception(string.Format("[{0}] 的 APPLY_DATE 非日期資料。", itemName));
}
//檢查界限資訊
Tool.LimitInformation limits = Tool.LimitStringConverter(
dr["LCL"].ToString(), dr["UCL"].ToString(), dr["LSL"].ToString(), dr["USL"].ToString());
//將資訊回填至表格中
DataRow newdr = dt.NewRow();
newdr["TAG_NAME"] = itemName;
if (limits.LCL < Mtblib.Tools.MtbTools.MISSINGVALUE)
{
newdr["LCL"] = limits.LCL;
}
if (limits.UCL < Mtblib.Tools.MtbTools.MISSINGVALUE)
{
newdr["UCL"] = limits.UCL;
}
if (limits.LSL < Mtblib.Tools.MtbTools.MISSINGVALUE)
{
newdr["LSL"] = limits.LSL;
}
if (limits.USL < Mtblib.Tools.MtbTools.MISSINGVALUE)
{
newdr["USL"] = limits.USL;
}
newdr["APPLY_DATE"] = appDate;
dt.Rows.Add(newdr);
}
using (SqlConnection conn = new SqlConnection(Database.DBQueryTool.GetConnString()))
{
conn.Open();
using (SqlCommand sqlCmnd = new SqlCommand("TRUNCATE TABLE UPLOAD_FURN_ITEM_LIMIT_INFO", conn))
{
//先清除暫存表上的資料
sqlCmnd.ExecuteNonQuery();
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 6000;
//設定要寫入的資料庫
sqlBC.DestinationTableName = "UPLOAD_FURN_ITEM_LIMIT_INFO";
//對應資料行
sqlBC.ColumnMappings.Add("TAG_NAME", "TAG_NAME");
sqlBC.ColumnMappings.Add("LCL", "LCL");
sqlBC.ColumnMappings.Add("UCL", "UCL");
sqlBC.ColumnMappings.Add("LSL", "LSL");
sqlBC.ColumnMappings.Add("USL", "USL");
sqlBC.ColumnMappings.Add("APPLY_DATE", "APPLY_DATE");
//開始寫入
sqlBC.WriteToServer(dt);
}
using (SqlCommand sqlCmnd = new SqlCommand())
{
StringBuilder query = new StringBuilder();
query.Clear();
query.AppendLine("INSERT INTO FURN_ITEM_LIMIT_INFO(FURN_ITEM_INDEX, LCL, UCL, LSL, USL, APPLY_DATE, RPT_DATE)");
query.AppendLine("SELECT * FROM (SELECT A.FURN_ITEM_INDEX, B.LCL, B.UCL, B.LSL, B.USL, B.APPLY_DATE,RPTDATE=GETDATE() FROM FURN_ITEM_INFO A");
//query.AppendLine("INNER JOIN UPLOAD_FURN_ITEM_LIMIT_INFO B");
query.AppendLine("INNER JOIN (");
query.AppendLine("SELECT * FROM UPLOAD_FURN_ITEM_LIMIT_INFO");
query.AppendLine("EXCEPT");
query.AppendLine("SELECT TAG_NAME, LCL, UCL, LSL, USL, APPLY_DATE FROM vw_furnacelimitrecord");
query.AppendLine("EXCEPT");
query.AppendLine("(SELECT a.TAG_NAME, a.LCL, a.UCL, a.LSL, a.USL, a.APPLY_DATE FROM UPLOAD_FURN_ITEM_LIMIT_INFO a ");
query.AppendLine("INNER JOIN vw_furnacelimitrecord b ON a.TAG_NAME=b.TAG_NAME AND a.APPLY_DATE = b.APPLY_DATE)");
query.AppendLine(") B");
query.AppendLine("ON A.TAG_NAME = B.TAG_NAME) AS T");
sqlCmnd.Connection = conn;
sqlCmnd.CommandText = query.ToString();
effectedRows = sqlCmnd.ExecuteNonQuery();
}
}
return(effectedRows);
}
