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;
}
/// <summary>
/// 判斷individaul T2管制圖的參數是否合法? 如果合法不回發生任何事，若不合法則回傳例外。
/// </summary>
/// <param name="paraSet"></param>
/// <returns></returns>
private void ParaValidation(ParaSetInfo paraSet)
{
DataTable mean = paraSet.Mean;
DataTable cov = paraSet.Cov;
int? ssiz = paraSet.SampleSize;
if (mean == null || cov == null)
{
throw new ArgumentNullException("尚未輸入平均數向量或共變異數矩陣");
}
if (SampleSize == null || SampleSize <= 0)
{
throw new Exception("樣本數內容不正確");
}
//確認輸入的數據是否正確(數量、共變異矩陣對稱、對角項大於0、正定等特性)
int r = cov.Columns.Count;
int c = cov.Rows.Count;
double result;
if (mean.Rows.Cast <DataRow>().Any(x => x.ItemArray.Any(y => !Double.TryParse(y.ToString(), out result))))
{
throw new Exception("平均數欄位內容必須為數字");
}
if (cov.Rows.Cast <DataRow>().Any(x => x.ItemArray.Any(y => !Double.TryParse(y.ToString(), out result))))
{
throw new Exception("共變異數內容必須為數字");
}
//將共變異數矩陣轉到Minitab中判斷是否合法
double[] covArray = new double[r * c];
for (int cCnt = 0; cCnt < c; cCnt++)
{
for (int rCnt = cCnt; rCnt < r; rCnt++)
{
if (rCnt == cCnt)
{
double value = double.Parse(cov.Rows[rCnt][cCnt].ToString());
if (value <= 0)
{
throw new Exception("共變異數矩陣對角項小於等於0");
}
covArray[cCnt * r + rCnt] = value;
}
else
{
if (cov.Rows[rCnt][cCnt].ToString() != cov.Rows[cCnt][rCnt].ToString())
{
throw new Exception("共變異數矩陣不對稱");
}
double value = double.Parse(CovarianceMatrix.Rows[rCnt][cCnt].ToString());
covArray[cCnt * r + rCnt] = value;
covArray[rCnt * r + cCnt] = value;
}
}
}
Mtb.Worksheet ws = Project.Worksheets.Add(1);
Mtb.Matrix mat = ws.Matrices.Add(Quantity: 1);
Mtb.Column col = ws.Columns.Add(Quantity: 1);
mat.SetData(covArray, 1, 1);
Project.ExecuteCommand(string.Format("Eigen {0} {1}.\r\n", mat.SynthesizedName, col.SynthesizedName), ws);
double[] eigvalue = col.GetData();
if (eigvalue.Any(x => x <= 0))
{
throw new Exception("共變異數矩陣不為正定");
}
ws.Delete();
Project.Commands.Delete();
}
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));
}
}
}
private void AddParaToDatabase(object obj)
{
ParaSetInfo paraSet = new ParaSetInfo()
{
Mean = MeanVector,
Cov = CovarianceMatrix,
SampleSize = SampleSize,
SubgroupSize = 1
};
ParaValidation(paraSet);
//確認時間是否有重複
string itemList = string.Join(",", SelectedFurnItems.Select(x => x.IntItemList));
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT DISTINCT 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.AsEnumerable().Select(x => x.Field <DateTime>("APPLY_DATE")).ToArray().Contains(ApplyDate))
{
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();
//上傳主要參數資訊
query.AppendLine("INSERT INTO CHART_PARAMETER(SITE_ID, ITEM_LIST, FLAG, APPLY_DATE, RPT_DATE)");
query.AppendLine("SELECT * FROM (");
query.AppendFormat("SELECT DISTINCT SITE_ID ='{0}', ITEM_LIST, CHART_FLAG, APPLY_DATE, RPT_DATE= GETDATE() FROM UPLOAD_PARAMETER) AS T\r\n", Site);
//建立虛擬表格
query.AppendLine("IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL");
query.AppendLine("BEGIN");
query.AppendLine("DROP TABLE #TMP");
query.AppendLine("END");
query.AppendLine("CREATE TABLE #TMP(ID VARCHAR(32))");
//取得新增的流水號
query.AppendLine("INSERT INTO #TMP (ID)");
query.AppendLine("SELECT TOP(1) CHART_PARA_INDEX FROM CHART_PARAMETER");
query.AppendFormat("WHERE ITEM_LIST='{0}' AND APPLY_DATE='{1:yyyy-M-dd HH:mm}'\r\n", itemList, ApplyDate);
query.AppendLine("DECLARE @ID VARCHAR");
query.AppendLine("SET @ID = (SELECT TOP(1) ID FROM #TMP)");
//將參數內容上傳至表格內
query.AppendLine("INSERT INTO PARAMETER_DETAIL(CHART_PARA_INDEX,FLAG, ROWNO,COLNO,VALUE)");
query.AppendLine("SELECT * FROM (SELECT ID=@ID, FLAG= PARA_FLAG, ROWNO, COLNO, VALUE FROM UPLOAD_PARAMETER) AS T");
sqlCmnd.Connection = conn;
sqlCmnd.CommandText = query.ToString();
sqlCmnd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(string.Format("上傳時發生錯誤:\r\n{0}", ex.Message));
}
}
}
/// <summary>
/// 取得指定熔爐組合的參數值
/// </summary>
/// <param name="itemList">熔爐 Index 組合</param>
/// <returns></returns>
private ParaSetInfo GetParaDetail(string itemList)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT CHART_PARA_INDEX,SITE_ID,ITEM_LIST,APPLY_DATE,P_FLAG,ROWNO,COLNO,VALUE FROM vw_chartparameter");
query.AppendFormat("WHERE ITEM_LIST ='{0}' AND APPLY_DATE=LAPPLYDATE\r\n", itemList);
query.AppendLine("ORDER BY APPLY_DATE DESC, RPT_DATE DESC,P_FLAG,ROWNO,COLNO");
DataTable paraData = Database.DBQueryTool.GetData(query.ToString(), Database.DBQueryTool.GetConnString());
//建立平均數向量和共變異數矩陣
DataTable mean = new DataTable();
DataTable cov = new DataTable();
string[] itemNames = Database.DBQueryTool.GetFurnNameByItemList(itemList);
foreach (var item in itemNames)
{
//建構 Mean vector 欄位
DataColumn dc = new DataColumn(item, typeof(string));
dc.AllowDBNull = true;
mean.Columns.Add(dc);
//建構 Covariance matrix 欄位
dc = new DataColumn(item, typeof(string));
dc.AllowDBNull = true;
cov.Columns.Add(dc);
}
DataTable dt;
ParaSetInfo paraSet = new ParaSetInfo();
if (paraData != null && paraData.Rows.Count > 0)
{
#region 複製最新一筆參數值
dt = paraData.Select("P_FLAG = 'MEAN'").CopyToDataTable();
if (dt != null && dt.Rows.Count > 0)
{
int r = dt.AsEnumerable().Select(x => x.Field <Byte>("ROWNO")).Max();
int c = dt.AsEnumerable().Select(x => x.Field <Byte>("COLNO")).Max();
var data = dt.AsEnumerable();
for (int rCnt = 0; rCnt < r; rCnt++)
{
DataRow dr = mean.NewRow();
for (int cCnt = 0; cCnt < c; cCnt++)
{
dr[cCnt] = data.Where(x => x.Field <Byte>("ROWNO") == rCnt + 1 && x.Field <Byte>("COLNO") == cCnt + 1)
.Select(x => x.Field <decimal>("VALUE")).First();
}
mean.Rows.Add(dr);
}
paraSet.Mean = mean;
}
dt = paraData.Select("P_FLAG='COV'").CopyToDataTable();
if (dt != null && dt.Rows.Count > 0)
{
int r = dt.AsEnumerable().Select(x => x.Field <Byte>("ROWNO")).Max();
int c = dt.AsEnumerable().Select(x => x.Field <Byte>("COLNO")).Max();
var data = dt.AsEnumerable();
for (int rCnt = 0; rCnt < r; rCnt++)
{
DataRow dr = cov.NewRow();
for (int cCnt = 0; cCnt < c; cCnt++)
{
dr[cCnt] = data.Where(x => x.Field <Byte>("ROWNO") == rCnt + 1 && x.Field <Byte>("COLNO") == cCnt + 1)
.Select(x => x.Field <decimal>("VALUE")).First();
}
cov.Rows.Add(dr);
}
paraSet.Cov = cov;
dt = paraData.Select("P_FLAG='N'").CopyToDataTable();
paraSet.SampleSize = Convert.ToInt32(dt.AsEnumerable().Select(x => x.Field <decimal>("VALUE")).FirstOrDefault());
}
#endregion
}
else
{
mean.Rows.Add(mean.NewRow());
paraSet.Mean = mean;
for (int i = 0; i < SelectedFurnItems.Count; i++)
{
cov.Rows.Add(cov.NewRow());
}
paraSet.Cov = cov;
paraSet.SampleSize = null;
}
paraSet.SubgroupSize = 1;
return(paraSet);
}
