/// <summary>
/// 抓DB的資料
/// </summary>
/// <param name="db">Moody_Tm_YYYY</param>
/// <param name="cname">哪一欄位</param>
/// <returns></returns>
private double getDbValueINColume(Moody_Tm_YYYY db, string cname)
{
if (cname.Equals(A7_Type.Aaa.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Aaa));
}
if (cname.Equals(A7_Type.Aa1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Aa1));
}
if (cname.Equals(A7_Type.Aa2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Aa2));
}
if (cname.Equals(A7_Type.Aa3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Aa3));
}
if (cname.Equals(A7_Type.A1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.A1));
}
if (cname.Equals(A7_Type.A2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.A2));
}
if (cname.Equals(A7_Type.A3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.A3));
}
if (cname.Equals(A7_Type.Baa1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Baa1));
}
if (cname.Equals(A7_Type.Baa2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Baa2));
}
if (cname.Equals(A7_Type.Baa3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Baa3));
}
if (cname.Equals(A7_Type.Ba1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Ba1));
}
if (cname.Equals(A7_Type.Ba2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Ba2));
}
if (cname.Equals(A7_Type.Ba3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Ba3));
}
if (cname.Equals(A7_Type.B1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.B1));
}
if (cname.Equals(A7_Type.B2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.B2));
}
if (cname.Equals(A7_Type.B3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.B3));
}
if (cname.Equals(A7_Type.Caa1.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Caa1));
}
if (cname.Equals(A7_Type.Caa2.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Caa2));
}
if (cname.Equals(A7_Type.Caa3.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Caa3));
}
if (cname.Equals(A7_Type.Ca_C.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Ca_C));
}
if (cname.Equals(A7_Type.WR.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.WR));
}
if (cname.Equals(A7_Type.Default.ToString()))
{
return(TypeTransfer.doubleNToDouble(db.Default_Value));
}
return(0d);
}
/// <summary>
/// DB Moody_Tm_YYYY 組成 DataTable
/// </summary>
/// <param name="dbDatas"></param>
/// <returns></returns>
private Tuple <DataTable, Dictionary <string, List <string> > > getExhibit29ModelFromDb(List <Moody_Tm_YYYY> dbDatas)
{
DataTable dt = new DataTable();
//超過的筆對紀錄 => string 開始的參數,List<string>要相加的參數
Dictionary <string, List <string> > overData =
new Dictionary <string, List <string> >();
try
{
#region 找出錯誤的參數
string errorKey = string.Empty; //錯誤起始欄位
string last_FromTo = string.Empty; //上一個 From_To
double last_value = 0d; //上一個default的參數(#)
foreach (Moody_Tm_YYYY item in dbDatas) //第一次迴圈先抓出不符合的項目
{
double now_default_value = //目前的default 參數
TypeTransfer.doubleNToDouble(item.Default_Value);
if (now_default_value >= last_value) //下一筆比上一筆大(正常情況)
{
if (!errorKey.IsNullOrWhiteSpace()) //假如上一筆是超過的參數
{
errorKey = string.Empty; //把錯誤Flag 取消掉(到上一筆為止)
}
#region 把現在的參數寄到最後一個裡面
last_FromTo = item.From_To;
last_value = now_default_value;
#endregion 把現在的參數寄到最後一個裡面
}
else //現在的參數比上一個還要小
{
if (!errorKey.IsNullOrWhiteSpace()) //上一個是錯誤的,修改錯誤記錄資料
{
var hestory = overData[errorKey];
hestory.Add(item.From_To);
overData[errorKey] = hestory;
}
else //上一個是對的(這次錯誤需新增錯誤資料)
{
overData.Add(last_FromTo,
new List <string>()
{
last_FromTo, item.From_To
}); //加入一筆歷史錯誤
errorKey = last_FromTo; //紀錄上一筆的FromTo為超過起始欄位
}
last_value = (last_value + now_default_value) / 2; //default 相加除以2
}
}
#endregion 找出錯誤的參數
#region 組出DataTable 的欄位
dt.Columns.Add("TM", typeof(object)); //第一欄固定為TM
List <string> errorData = new List <string>(); //錯誤資料
List <string> rowData = new List <string>(); //左邊行數欄位
foreach (Moody_Tm_YYYY item in dbDatas) //第二次迴圈組 DataTable 欄位
{
if (overData.ContainsKey(item.From_To)) //為起始錯誤
{
errorData = overData[item.From_To]; //把錯誤資料找出來
}
else if (errorData.Contains(item.From_To)) //為中間錯誤
{
//不做任何動作
}
else //無錯誤 (columns 加入原本 參數)
{
if (errorData.Any()) //上一筆是錯誤情形
{
string key =
string.Format("{0}_{1}",
errorData.First(),
errorData.Last()
);
dt.Columns.Add(key, typeof(object));
errorData = new List <string>();
rowData.Add(key);
}
dt.Columns.Add(item.From_To, typeof(object));
rowData.Add(item.From_To);
}
}
if (errorData.Any()) //此為最後一筆為錯誤時觸發
{
string key =
string.Format("{0}_{1}",
errorData.First(),
errorData.Last()
);
dt.Columns.Add(key, typeof(double));
rowData.Add(key);
}
//最後兩欄固定為 WR & Default
dt.Columns.Add("WR", typeof(string));
dt.Columns.Add("Default", typeof(string));
#endregion 組出DataTable 的欄位
#region 組出資料
List <string> columnsName = (from q in rowData select q).ToList();
columnsName.AddRange(new List <string>()
{
"WR", "Default"
});
foreach (var item in rowData) //by 每一行
{
if (item.IndexOf('_') > -1) //合併行需特別處理
{
List <string> err = overData[item.Split('_')[0]];
List <Moody_Tm_YYYY> dbs = dbDatas.Where(x => err.Contains(x.From_To)).ToList();
List <double> datas = new List <double>();
foreach (string cname in columnsName) //by 每一欄
{
if (cname.IndexOf('_') > -1) //合併欄
{
List <string> err2 = overData[cname.Split('_')[0]];
datas.Add((from y in dbs
select(
(from z in err2
select getDbValueINColume(y, z))
.Sum())).Sum() / (err.Count));
}
else
{
datas.Add((from x in dbs
select getDbValueINColume(x, cname)).Sum()
/ err.Count);
}
}
List <object> o = new List <object>();
o.Add(item);
o.AddRange((from q in datas select q as object).ToList());
var row = dt.NewRow();
row.ItemArray = (o.ToArray());
dt.Rows.Add(row);
}
else //其他無合併行的只要單獨處理某特例欄位(合併)
{
string from_to = item;
List <double> datas = new List <double>();
Moody_Tm_YYYY db = dbDatas.Where(x => x.From_To == item).First();
foreach (string cname in columnsName) //by 每一欄
{
if (cname.IndexOf('_') > -1) //合併欄
{
List <string> err = overData[cname.Split('_')[0]];
double avg = err.Select(x => getDbValueINColume(db, x)).Sum() / err.Count;
datas.Add(avg);
}
else //正常的
{
datas.Add(getDbValueINColume(db, cname));
}
}
List <object> o = new List <object>();
o.Add(item);
o.AddRange((from q in datas select q as object).ToList());
var row = dt.NewRow();
row.ItemArray = (o.ToArray());
dt.Rows.Add(row);
}
}
//加入 WT & Default 行
List <string> WTArray = new List <string>()
{
"Baa1", "Baa2", "Baa3", "Ba1", "Ba2", "Ba3"
};
List <string> WTRow = new List <string>(); //WT要尋找的行的From/To
foreach (var item in overData) //合併的資料紀錄
{
if (WTArray.Intersect(item.Value).Any()) //找合併裡面符合的
{
WTRow.Add(item.Key);
}
}
WTRow.AddRange(rowData.Where(x => WTArray.Contains(x)));
List <object> WTData = new List <object>();
List <object> DefaultData = new List <object>();
WTData.Add("WT");
DefaultData.Add("Default");
for (var i = 1; i < dt.Rows[0].ItemArray.Count(); i++) //i從1開始 from/to 那欄不用看
{
double d = 0d;
for (var j = 0; j < dt.Rows.Count; j++)
{
if (WTRow.Contains(dt.Rows[j].ItemArray[0].ToString())) //符合排
{
d += Convert.ToDouble(dt.Rows[j][i]);
}
}
WTData.Add(d * (dt.Columns[i].ToString().IndexOf("_") > -1 ?
overData[dt.Columns[i].ToString().Split('_')[0]].Count : 1)
/ WTRow.Count); //多筆需*合併數
if (i == (dt.Rows[0].ItemArray.Count() - 1))
{
DefaultData.Add(100d);
}
else
{
DefaultData.Add(0d);
}
}
var nrow = dt.NewRow();
nrow.ItemArray = (WTData.ToArray());
dt.Rows.Add(nrow);
nrow = dt.NewRow();
nrow.ItemArray = (DefaultData.ToArray());
dt.Rows.Add(nrow);
#endregion 組出資料
}
catch
{
}
return(new Tuple <DataTable, Dictionary <string, List <string> > >(dt, overData));
}