/// <summary>
/// datarow 組成 Exhibit29Model
/// </summary>
/// <param name="item">DataRow</param>
/// <returns>Exhibit29Model</returns>
private Exhibit29Model getExhibit29Model(DataRow item)
{
return(new Exhibit29Model()
{
From_To = TypeTransfer.objToString(item[0]),
Aaa = TypeTransfer.objToString(item[1]),
Aa1 = TypeTransfer.objToString(item[2]),
Aa2 = TypeTransfer.objToString(item[3]),
Aa3 = TypeTransfer.objToString(item[4]),
A1 = TypeTransfer.objToString(item[5]),
A2 = TypeTransfer.objToString(item[6]),
A3 = TypeTransfer.objToString(item[7]),
Baa1 = TypeTransfer.objToString(item[8]),
Baa2 = TypeTransfer.objToString(item[9]),
Baa3 = TypeTransfer.objToString(item[10]),
Ba1 = TypeTransfer.objToString(item[11]),
Ba2 = TypeTransfer.objToString(item[12]),
Ba3 = TypeTransfer.objToString(item[13]),
B1 = TypeTransfer.objToString(item[14]),
B2 = TypeTransfer.objToString(item[15]),
B3 = TypeTransfer.objToString(item[16]),
Caa1 = TypeTransfer.objToString(item[17]),
Caa2 = TypeTransfer.objToString(item[18]),
Caa3 = TypeTransfer.objToString(item[19]),
Ca_C = TypeTransfer.objToString(item[20]),
WR = TypeTransfer.objToString(item[21]),
Default = TypeTransfer.objToString(item[22]),
});
}
private A63ViewModel getA63Models(DataRow item, string Data_Year)
{
return(new A63ViewModel()
{
Data_Year = Data_Year,
Lien_Position = TypeTransfer.objToString(item[0]),
Recovery_Rate = TypeTransfer.objToString(item[3]),
LGD = (1 - TypeTransfer.objToDouble(item[3])).ToString()
});
}
/// <summary>
/// datarow 組成 Exhibit7Model
/// </summary>
/// <param name="item">DataRow</param>
/// <returns>Exhibit7Model</returns>
private Exhibit7Model getExhibit7Models(DataRow item, string Data_Year)
{
return(new Exhibit7Model()
{
Data_Year = Data_Year,
Lien_Position = TypeTransfer.objToString(item[0]),
Recovery_Rate = TypeTransfer.objToString(item[3]),
LGD = (1 - TypeTransfer.objToDouble(item[3])).ToString()
//Recovery_Rate = string.Format("{0}%", (Recovery_Rate * 100).ToString()),
//LGD = string.Format("{0}%", (LGD * 100).ToString())
});
}
/// <summary>
/// dataRow to ViewModel
/// </summary>
/// <param name="item"></param>
/// <param name="titles"></param>
/// <param name="type"></param>
/// <returns></returns>
public List <IViewModel> getViewModel(IEnumerable <DataRow> items, List <string> titles, Table_Type type)
{
List <IViewModel> datas = new List <IViewModel>();
var obj = FactoryRegistry.GetInstance(type);
if (obj == null)
{
return(datas);
}
var pros = obj.GetType().GetProperties();
if (!items.Any() || items.First().ItemArray.Count() != titles.Count)
{
return(datas);
}
items.ToList().ForEach(item =>
{
obj = FactoryRegistry.GetInstance(type);
for (int i = 0; i < titles.Count; i++) //每一行所有資料
{
string data = null;
if (item[i].GetType().Name.Equals("DateTime"))
{
data = TypeTransfer.objDateToString(item[i]);
}
else
{
data = TypeTransfer.objToString(item[i]);
}
if (!data.IsNullOrWhiteSpace()) //資料有值
{
var PInfo = pros.Where(x => x.Name.Trim().ToLower() == titles[i].Trim().ToLower())
.FirstOrDefault();
if (PInfo != null)
{
PInfo.SetValue(obj, data);
}
}
}
datas.Add(obj);
});
return(datas);
}
/// <summary>
/// Excel 資料轉成 A95_1ViewModel
/// </summary>
/// <param name="pathType"></param>
/// <param name="stream"></param>
/// <returns></returns>
public Tuple <string, List <A95_1ViewModel> > getA95_1Excel(string pathType, Stream stream)
{
List <A95_1ViewModel> dataModel = new List <A95_1ViewModel>();
string message = string.Empty;
DataSet resultData = new DataSet();
try
{
IWorkbook wb = null;
stream.Position = 0;
switch (pathType) //判斷型別
{
case "xls":
wb = new HSSFWorkbook(stream);
break;
case "xlsx":
wb = new XSSFWorkbook(stream);
break;
}
ISheet sheet = wb.GetSheetAt(0);
DataTable dt = sheet.ISheetToDataTable(true);
if (dt.Rows.Count > 0) //判斷有無資料
{
dataModel = dt.AsEnumerable()
.Select((x, y) => new A95_1ViewModel()
{
Bond_Number = TypeTransfer.objToString(x[0]),
Security_Des = TypeTransfer.objToString(x[1]),
Bloomberg_Ticker = TypeTransfer.objToString(x[2])
}).ToList();
}
}
catch (Exception ex)
{
message = ex.exceptionMessage();
}
if (!dataModel.Any())
{
message = Message_Type.not_Find_Any.GetDescription();
}
return(new Tuple <string, List <A95_1ViewModel> >(message, dataModel));
}
/// <summary>
/// datarow 組成 Exhibit10Model
/// </summary>
/// <param name="item">DataRow</param>
/// <returns>Exhibit10Model</returns>
private Exhibit10Model getExhibit10Models(DataRow item)
{
DateTime minDate = DateTime.MinValue;
if (item[0] != null)
{
DateTime.TryParse(item[0].ToString(), out minDate);
}
return(new Exhibit10Model()
{
Trailing = (item[0] != null) && (minDate != DateTime.MinValue) ?
minDate.ToString("yyyy/MM/dd") : string.Empty,
Actual_Allcorp = TypeTransfer.objToString(item[1]),
Baseline_forecast_Allcorp = TypeTransfer.objToString(item[2]),
Pessimistic_Forecast_Allcorp = TypeTransfer.objToString(item[3]),
Actual_SG = TypeTransfer.objToString(item[4]),
Baseline_forecast_SG = TypeTransfer.objToString(item[5]),
Pessimistic_Forecast_SG = TypeTransfer.objToString(item[6])
});
}
private A59ViewModel getA59ViewModelInExcel(DataRow item)
{
return(new A59ViewModel()
{
Reference_Nbr = TypeTransfer.objToString(item[0]), //帳戶編號
Report_Date = TypeTransfer.objToString(item[1]), //報導日
Version = TypeTransfer.objToString(item[2]), //資料版本
Bond_Number = TypeTransfer.objToString(item[3]), //債券編號
Lots = TypeTransfer.objToString(item[4]), //Lots
Origination_Date = TypeTransfer.objToString(item[5]), //債券購入(認列)日期
Portfolio_Name = TypeTransfer.objToString(item[6]), //Portfolio英文
SMF = TypeTransfer.objToString(item[7]), //債券產品別(揭露使用)
Rating_Type = TypeTransfer.objToString(item[8]), //Rating_Type
Issuer = TypeTransfer.objToString(item[9]), //Issuer
Security_Ticker = TypeTransfer.objToString(item[10]), //Security_Ticker
RATING_AS_OF_DATE_OVERRIDE = TypeTransfer.objToString(item[11]), //RATING_AS_OF_DATE_OVERRIDE
ISSUER_TICKER = TypeTransfer.objToString(item[12]), //ISSUER_TICKER
GUARANTOR_NAME = TypeTransfer.objToString(item[13]), //GUARANTOR_NAME
GUARANTOR_EQY_TICKER = TypeTransfer.objToString(item[14]), //GUARANTOR_EQY_TICKER
RTG_SP = TypeTransfer.objToString(item[15]), //債項_標普評等 (債項\ sp\國外)
SP_EFF_DT = TypeTransfer.objToString(item[16]), //債項_標普評等日期
RTG_TRC = TypeTransfer.objToString(item[17]), //債項_TRC 評等 (債項\ CW\國內)
TRC_EFF_DT = TypeTransfer.objToString(item[18]), //債項_TRC 評等日期
RTG_MOODY = TypeTransfer.objToString(item[19]), //債項_穆迪評等 (債項\ moody\國外)
MOODY_EFF_DT = TypeTransfer.objToString(item[20]), //債項_穆迪評等日期
RTG_FITCH = TypeTransfer.objToString(item[21]), //債項_惠譽評等 (債項\ Fitch\國外)
FITCH_EFF_DT = TypeTransfer.objToString(item[22]), //債項_惠譽評等日期
RTG_FITCH_NATIONAL = TypeTransfer.objToString(item[23]), //債項_惠譽國內評等 (債項\ Fitch(twn)\國內)
RTG_FITCH_NATIONAL_DT = TypeTransfer.objToString(item[24]), //債項_惠譽國內評等日期
RTG_SP_LT_FC_ISSUER_CREDIT = TypeTransfer.objToString(item[25]), //標普長期外幣發行人信用評等 (發行人\ sp\國外)
RTG_SP_LT_FC_ISS_CRED_RTG_DT = TypeTransfer.objToString(item[26]), //標普長期外幣發行人信用評等日期
RTG_SP_LT_LC_ISSUER_CREDIT = TypeTransfer.objToString(item[27]), //標普本國貨幣長期發行人信用評等 (發行人\ sp\國外)
RTG_SP_LT_LC_ISS_CRED_RTG_DT = TypeTransfer.objToString(item[28]), //標普本國貨幣長期發行人信用評等日期
RTG_MDY_ISSUER = TypeTransfer.objToString(item[29]), //穆迪發行人評等 (發行人\ moody\國外)
RTG_MDY_ISSUER_RTG_DT = TypeTransfer.objToString(item[30]), //穆迪發行人評等日期
RTG_MOODY_LONG_TERM = TypeTransfer.objToString(item[31]), //發行人_穆迪長期評等 (發行人\ moody\國外)
RTG_MOODY_LONG_TERM_DATE = TypeTransfer.objToString(item[32]), //發行人_穆迪長期評等日期
RTG_MDY_SEN_UNSECURED_DEBT = TypeTransfer.objToString(item[33]), //發行人_穆迪優先無擔保債務評等 (發行人\ moody\國外)
RTG_MDY_SEN_UNSEC_RTG_DT = TypeTransfer.objToString(item[34]), //發行人_穆迪優先無擔保債務評等_日期
RTG_MDY_FC_CURR_ISSUER_RATING = TypeTransfer.objToString(item[35]), //穆迪外幣發行人評等 (發行人\ moody\國外)
RTG_MDY_FC_CURR_ISSUER_RTG_DT = TypeTransfer.objToString(item[36]), //穆迪外幣發行人評等日期
RTG_MDY_LOCAL_LT_BANK_DEPOSITS = TypeTransfer.objToString(item[37]), //發行人_穆迪長期本國銀行存款評等 (發行人\ moody\國內)
RTG_MDY_LT_LC_BANK_DEP_RTG_DT = TypeTransfer.objToString(item[38]), //發行人_穆迪長期本國銀行存款評等日期
RTG_FITCH_LT_ISSUER_DEFAULT = TypeTransfer.objToString(item[39]), //惠譽長期發行人違約評等 (發行人\ Fitch\國外)
RTG_FITCH_LT_ISSUER_DFLT_RTG_DT = TypeTransfer.objToString(item[40]), //惠譽長期發行人違約評等日期
RTG_FITCH_SEN_UNSECURED = TypeTransfer.objToString(item[41]), //發行人_惠譽優先無擔保債務評等 (發行人\ Fitch\國外)
RTG_FITCH_SEN_UNSEC_RTG_DT = TypeTransfer.objToString(item[42]), //發行人_惠譽優先無擔保債務評等日期
RTG_FITCH_LT_FC_ISSUER_DEFAULT = TypeTransfer.objToString(item[43]), //惠譽長期外幣發行人違約評等 (發行人\ Fitch\國外)
RTG_FITCH_LT_FC_ISS_DFLT_RTG_DT = TypeTransfer.objToString(item[44]), //惠譽長期外幣發行人違約評等日期
RTG_FITCH_LT_LC_ISSUER_DEFAULT = TypeTransfer.objToString(item[45]), //惠譽長期本國貨幣發行人違約評等 (發行人\ Fitch\國外)
RTG_FITCH_LT_LC_ISS_DFLT_RTG_DT = TypeTransfer.objToString(item[46]), //惠譽長期本國貨幣發行人違約評等日期
RTG_FITCH_NATIONAL_LT = TypeTransfer.objToString(item[47]), //發行人_惠譽國內長期評等 (發行人\ Fitch(twn)\國內)
RTG_FITCH_NATIONAL_LT_DT = TypeTransfer.objToString(item[48]), //發行人_惠譽國內長期評等日期
RTG_TRC_LONG_TERM = TypeTransfer.objToString(item[49]), //發行人_TRC 長期評等 (發行人\ CW\國內)
RTG_TRC_LONG_TERM_RTG_DT = TypeTransfer.objToString(item[50]), //發行人_TRC 長期評等日期
G_RTG_SP_LT_FC_ISSUER_CREDIT = TypeTransfer.objToString(item[51]), //標普長期外幣保證人信用評等 (保證人\ sp\國外)
G_RTG_SP_LT_FC_ISS_CRED_RTG_DT = TypeTransfer.objToString(item[52]), //標普長期外幣保證人信用評等日期
G_RTG_SP_LT_LC_ISSUER_CREDIT = TypeTransfer.objToString(item[53]), //標普本國貨幣長期保證人信用評等 (保證人\ sp\國外)
G_RTG_SP_LT_LC_ISS_CRED_RTG_DT = TypeTransfer.objToString(item[54]), //標普本國貨幣長期保證人信用評等日期
G_RTG_MDY_ISSUER = TypeTransfer.objToString(item[55]), //穆迪保證人評等 (保證人\ moody\國外)
G_RTG_MDY_ISSUER_RTG_DT = TypeTransfer.objToString(item[56]), //穆迪保證人評等日期
G_RTG_MOODY_LONG_TERM = TypeTransfer.objToString(item[57]), //保證人_穆迪長期評等 (保證人\ moody\國外)
G_RTG_MOODY_LONG_TERM_DATE = TypeTransfer.objToString(item[58]), //保證人_穆迪長期評等日期
G_RTG_MDY_SEN_UNSECURED_DEBT = TypeTransfer.objToString(item[59]), //保證人_穆迪優先無擔保債務評等 (保證人\ moody\國外)
G_RTG_MDY_SEN_UNSEC_RTG_DT = TypeTransfer.objToString(item[60]), //保證人_穆迪優先無擔保債務評等_日期
G_RTG_MDY_FC_CURR_ISSUER_RATING = TypeTransfer.objToString(item[61]), //穆迪外幣保證人評等 (保證人\ moody\國外)
G_RTG_MDY_FC_CURR_ISSUER_RTG_DT = TypeTransfer.objToString(item[62]), //穆迪外幣保證人評等日期
G_RTG_MDY_LOCAL_LT_BANK_DEPOSITS = TypeTransfer.objToString(item[63]), //保證人_穆迪長期本國銀行存款評等 (保證人\ moody\國內)
G_RTG_MDY_LT_LC_BANK_DEP_RTG_DT = TypeTransfer.objToString(item[64]), //保證人_穆迪長期本國銀行存款評等日期
G_RTG_FITCH_LT_ISSUER_DEFAULT = TypeTransfer.objToString(item[65]), //惠譽長期保證人違約評等 (保證人\ Fitch\國外)
G_RTG_FITCH_LT_ISSUER_DFLT_RTG_DT = TypeTransfer.objToString(item[66]), //惠譽長期保證人違約評等日期
G_RTG_FITCH_SEN_UNSECURED = TypeTransfer.objToString(item[67]), //保證人_惠譽優先無擔保債務評等 (保證人\ Fitch\國外)
G_RTG_FITCH_SEN_UNSEC_RTG_DT = TypeTransfer.objToString(item[68]), //保證人_惠譽優先無擔保債務評等日期
G_RTG_FITCH_LT_FC_ISSUER_DEFAULT = TypeTransfer.objToString(item[69]), //惠譽長期外幣保證人違約評等 (保證人\ Fitch\國外)
G_RTG_FITCH_LT_FC_ISS_DFLT_RTG_DT = TypeTransfer.objToString(item[70]), //惠譽長期外幣保證人違約評等日期
G_RTG_FITCH_LT_LC_ISSUER_DEFAULT = TypeTransfer.objToString(item[71]), //惠譽長期本國貨幣保證人違約評等 (保證人\ Fitch\國外)
G_RTG_FITCH_LT_LC_ISS_DFLT_RTG_DT = TypeTransfer.objToString(item[72]), //惠譽長期本國貨幣保證人違約評等日期
G_RTG_FITCH_NATIONAL_LT = TypeTransfer.objToString(item[73]), //保證人_惠譽國內長期評等 (保證人\ Fitch(twn)\國內)
G_RTG_FITCH_NATIONAL_LT_DT = TypeTransfer.objToString(item[74]), //保證人_惠譽國內長期評等日期
G_RTG_TRC_LONG_TERM = TypeTransfer.objToString(item[75]), //保證人_TRC 長期評等 (保證人\ CW\國內)
G_RTG_TRC_LONG_TERM_RTG_DT = TypeTransfer.objToString(item[76]), //保證人_TRC 長期評等日期
//Processing_Date =
});
}