/// <summary>
/// 處理滑鼠左鍵連點在可選/已選項
/// </summary>
public void AddClickedItem(object obj)
{
System.Windows.Controls.TextBlock itemTextBlock = obj as System.Windows.Controls.TextBlock;
if (itemTextBlock == null)
{
return;
}
foreach (var item in FurnItemsSrc)
{
if (item.Description == itemTextBlock.Text)
{
if (SelectedFurnItems.Count() > 0)
{
var a = SelectedFurnItems.Select(x => x.ItemList == item.ItemList);
a = a as IEnumerable <bool>;
if (!a.Contains(true))
{
SelectedFurnItems.Add(item);
return;
}
}
else
{
SelectedFurnItems.Add(item);
return;
}
}
}
}
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));
}
}
}