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)); } } }