private void OnSelectedParaItemChanged()
{
if (SelectedParaItemValue == null || SelectedParaItemValue == string.Empty)
{
return;
}
var info = ParaItemsSrc.AsEnumerable().Where(x => x["CHART_PARA_INDEX"].ToString() == SelectedParaItemValue)
.Select(x => new
{
ItemList = x.Field <string>("ITEM_LIST"),
ApplyDate = x.Field <DateTime>("APPLY_DATE")
}).First();
ParaSetInfo paraSet = GetParaDetail(info.ItemList);
MeanVector = paraSet.Mean;
CovarianceMatrix = paraSet.Cov;
SampleSize = paraSet.SampleSize;
ApplyDate = info.ApplyDate;
Step = 2;
}
private void EditParaInDatabase(object obj)
{
ParaSetInfo paraSet = new ParaSetInfo()
{
Mean = MeanVector,
Cov = CovarianceMatrix,
SampleSize = SampleSize,
SubgroupSize = 1
};
ParaValidation(paraSet);
//判斷時間是否重複，並且不可比現有的應用時間(被編輯項除外)還要早
string itemList = ParaItemsSrc.AsEnumerable().Where(x => x["CHART_PARA_INDEX"].ToString() == SelectedParaItemValue)
.Select(x => x.Field <string>("ITEM_LIST")).First();
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT DISTINCT CHART_PARA_INDEX, APPLY_DATE FROM vw_chartparameter");
query.AppendFormat("WHERE ITEM_LIST ='{0}'\r\n", itemList);
DataTable dateRecord = Database.DBQueryTool.GetData(query.ToString(), Database.DBQueryTool.GetConnString());
if (dateRecord.Rows.Count > 1)//如果等於1，表示只有一筆參數組
{
int compare = DateTime.Compare(dateRecord.AsEnumerable()
.Where(x => x["CHART_PARA_INDEX"].ToString() != SelectedParaItemValue)
.Select(x => x.Field <DateTime>("APPLY_DATE"))
.OrderByDescending(x => x).First(), ApplyDate);
if (compare >= 0)
{
throw new Exception("套用時間不可等於或早於該多變量管制圖的其他參數組套用時間");
}
}
//建立上傳資料表
DataTable uploadTable = new DataTable();
uploadTable.Columns.Add("ITEM_LIST", typeof(string));
uploadTable.Columns.Add("CHART_FLAG", typeof(string));
uploadTable.Columns.Add("PARA_FLAG", typeof(string));
uploadTable.Columns.Add("ROWNO", typeof(int));
uploadTable.Columns.Add("COLNO", typeof(int));
uploadTable.Columns.Add("VALUE", typeof(double));
uploadTable.Columns.Add("APPLY_DATE", typeof(DateTime));
DataRow dr;
int r = CovarianceMatrix.Columns.Count;
int c = CovarianceMatrix.Rows.Count;
for (int cCnt = 0; cCnt < c; cCnt++)
{
dr = uploadTable.NewRow();
dr["ITEM_LIST"] = itemList; dr["CHART_FLAG"] = "T2"; dr["PARA_FLAG"] = "MEAN"; dr["APPLY_DATE"] = ApplyDate;
dr["ROWNO"] = 1;
dr["COLNO"] = cCnt + 1;
dr["VALUE"] = double.Parse(MeanVector.Rows[0][cCnt].ToString());
uploadTable.Rows.Add(dr);
}
for (int rCnt = 0; rCnt < r; rCnt++)
{
for (int cCnt = 0; cCnt < c; cCnt++)
{
dr = uploadTable.NewRow();
dr["ITEM_LIST"] = itemList; dr["CHART_FLAG"] = "T2"; dr["PARA_FLAG"] = "COV"; dr["APPLY_DATE"] = ApplyDate;
dr["ROWNO"] = rCnt + 1;
dr["COLNO"] = cCnt + 1;
dr["VALUE"] = double.Parse(CovarianceMatrix.Rows[rCnt][cCnt].ToString());
uploadTable.Rows.Add(dr);
}
}
dr = uploadTable.NewRow();
dr["ITEM_LIST"] = itemList; dr["CHART_FLAG"] = "T2"; dr["PARA_FLAG"] = "N"; dr["APPLY_DATE"] = ApplyDate;
dr["ROWNO"] = 1; dr["COLNO"] = 1;
dr["VALUE"] = SampleSize;
uploadTable.Rows.Add(dr);
dr = uploadTable.NewRow();
dr["ITEM_LIST"] = itemList; dr["CHART_FLAG"] = "T2"; dr["PARA_FLAG"] = "SUBGP"; dr["APPLY_DATE"] = ApplyDate;
dr["ROWNO"] = 1; dr["COLNO"] = 1;
dr["VALUE"] = 1;
uploadTable.Rows.Add(dr);
using (SqlConnection conn = new SqlConnection(Database.DBQueryTool.GetConnString()))
{
try
{
conn.Open();
using (SqlCommand sqlCmnd = new SqlCommand("TRUNCATE TABLE UPLOAD_PARAMETER", conn))
{
//先清除暫存表上的資料
sqlCmnd.ExecuteNonQuery();
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
sqlBC.BatchSize = 1000;
sqlBC.BulkCopyTimeout = 6000;
//設定要寫入的資料庫
sqlBC.DestinationTableName = "UPLOAD_PARAMETER";
//對應資料行
sqlBC.ColumnMappings.Add("ITEM_LIST", "ITEM_LIST");
sqlBC.ColumnMappings.Add("CHART_FLAG", "CHART_FLAG");
sqlBC.ColumnMappings.Add("PARA_FLAG", "PARA_FLAG");
sqlBC.ColumnMappings.Add("APPLY_DATE", "APPLY_DATE");
sqlBC.ColumnMappings.Add("ROWNO", "ROWNO");
sqlBC.ColumnMappings.Add("COLNO", "COLNO");
sqlBC.ColumnMappings.Add("VALUE", "VALUE");
//開始寫入
sqlBC.WriteToServer(uploadTable);
}
using (SqlCommand sqlCmnd = new SqlCommand())
{
query.Clear();
//更新 PARAMETER_DETAIL
query.AppendLine("UPDATE PARAMETER_DETAIL");
query.AppendLine("SET PARAMETER_DETAIL.VALUE = a.VALUE FROM(");
query.AppendFormat("SELECT CHART_PARA_INDEX='{0}',* FROM UPLOAD_PARAMETER", SelectedParaItemValue);
query.AppendLine(") a");
query.AppendLine("WHERE PARAMETER_DETAIL.CHART_PARA_INDEX = a.CHART_PARA_INDEX AND ");
query.AppendLine("PARAMETER_DETAIL.FLAG = a.PARA_FLAG AND PARAMETER_DETAIL.ROWNO = a.ROWNO AND PARAMETER_DETAIL.COLNO = a.COLNO");
//更新 CHART_PARAMETER
query.AppendLine("UPDATE CHART_PARAMETER");
query.AppendLine("SET APPLY_DATE = a.APPLY_DATE, RPT_DATE = GETDATE() FROM(");
query.AppendFormat("SELECT CHART_PARA_INDEX='{0}',* FROM UPLOAD_PARAMETER", SelectedParaItemValue);
query.AppendLine(") a");
query.AppendLine("WHERE CHART_PARAMETER.CHART_PARA_INDEX = a.CHART_PARA_INDEX");
sqlCmnd.Connection = conn;
sqlCmnd.CommandText = query.ToString();
sqlCmnd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(string.Format("更新時發生錯誤:\r\n{0}", ex.Message));
}
}
}