/// <summary>
/// Db save
/// </summary>
protected override void DataToDb()
{
IFRS9Entities db = new IFRS9Entities();
List <Rating_Info> sampleData = new List <Rating_Info>();
List <Rating_Info> commpanyData = new List <Rating_Info>();
List <sampleInfo> sampleInfos = new List <sampleInfo>();
A53Sample a53Sample = new A53Sample();
A53Commpany a53Commpany = new A53Commpany();
#region sample Data
using (StreamReader sr = new StreamReader(Path.Combine(
setFile.getSampleFilePath(), setFile.getSampleFileName())))
{
bool flag = false; //判斷是否為要讀取的資料行數
string line = string.Empty;
while ((line = sr.ReadLine()) != null)
{
if ("END-OF-DATA".Equals(line))
{
flag = false;
}
if (flag) //找到的資料
{
var arr = line.Split('|');
//arr[0] ex: US00206RDH21 (債券編號)
//arr[1] ex: 0
//arr[2] ex: 20
//arr[3] ISSUER_EQUITY_TICKER (發行人)
//arr[4] ISSUE_DT (債券評等日期)
//arr[5] ISSUER (債券名稱)
//arr[6] GUARANTOR_EQY_TICKER (擔保人)
//arr[7] GUARANTOR_NAME (擔保人名稱)
//--標普(S&P)
//arr[8] (國外)RTG_SP (SP國外評等)
//arr[9] (國外)SP_EFF_DT (SP國外評等日期)
//--穆迪(Moody's)
//arr[10] (國外)RTG_MOODY (Moody's國外評等)
//arr[11] (國外)MOODY_EFF_DT (Moody's國外評等日期)
//--惠譽台灣(Fitch(twn))
//arr[12] (國內)RTG_FITCH_NATIONAL (惠譽國內評等)
//arr[13] (國內)RTG_FITCH_NATIONAL_DT (惠譽國內評等日期)
//--惠譽(Fitch)
//arr[14] (國外)RTG_FITCH (惠譽評等)
//arr[15] (國外)FITCH_EFF_DT (惠譽評等日期)
//--TRC(中華信評)
//arr[16] (國內)RTG_TRC (TRC 評等)
//arr[17] (國內)TRC_EFF_DT (TRC 評等日期)
if (arr.Length >= 18)
{
//S&P國外評等
validateSample(
arr[8],
arr[9],
arr[0],
A53SampleBloombergField.RTG_SP.ToString(),
RatingOrg.SP,
sampleData);
//Moody's國外評等
validateSample(
arr[10],
arr[11],
arr[0],
A53SampleBloombergField.RTG_MOODY.ToString(),
RatingOrg.Moody,
sampleData);
//惠譽台灣
validateSample(
arr[12],
arr[13],
arr[0],
A53SampleBloombergField.RTG_FITCH_NATIONAL.ToString(),
RatingOrg.FitchTwn,
sampleData);
//惠譽
validateSample(
arr[14],
arr[15],
arr[0],
A53SampleBloombergField.RTG_FITCH.ToString(),
RatingOrg.Fitch,
sampleData);
//TRC(中華信評)
validateSample(
arr[16],
arr[17],
arr[0],
A53SampleBloombergField.RTG_TRC.ToString(),
RatingOrg.CW,
sampleData);
sampleInfos.Add(new sampleInfo()
{
Bond_Number = arr[0],
ISSUER_TICKER = arr[3],
GUARANTOR_EQY_TICKER = arr[6],
GUARANTOR_NAME = arr[7]
});
}
}
if ("START-OF-DATA".Equals(line))
{
flag = true;
}
}
}
#endregion sample Data
#region commpany Data
using (StreamReader sr = new StreamReader(Path.Combine(
setFile.getCommpanyFilePath(), setFile.getCommpanyFileName())))
{
bool flag = false; //判斷是否為要讀取的資料行數
string line = string.Empty;
while ((line = sr.ReadLine()) != null)
{
if ("END-OF-DATA".Equals(line))
{
flag = false;
}
if (flag) //找到的資料
{
var arr = line.Split('|');
//arr[0] ex: T US Equity (發行人or擔保人)
//arr[1] ex: 0
//arr[2] ex: 38
//arr[3] ID_BB_COMPANY 公司ID
//arr[4] LONG_COMP_NAME 公司名稱
//arr[5] COUNTRY_ISO 城市(國家)
//arr[6] INDUSTRY_GROUP
//arr[7] INDUSTRY_SECTOR
//--標普(S&P)
//arr[8] (國外)RTG_SP_LT_LC_ISSUER_CREDIT (標普本國貨幣長期發行人信用評等)
//arr[9] (國外)RTG_SP_LT_LC_ISS_CRED_RTG_DT (標普本國貨幣長期發行人信用評等日期)
//arr[10] (國外)RTG_SP_LT_FC_ISSUER_CREDIT (標普長期外幣發行人信用評等)
//arr[11] (國外)RTG_SP_LT_FC_ISS_CRED_RTG_DT (標普長期外幣發行人信用評等日期)
//--穆迪(Moody's)
//arr[12] (國內)RTG_MDY_LOCAL_LT_BANK_DEPOSITS (穆迪長期本國銀行存款評等)
//arr[13] (國內)RTG_MDY_LT_LC_BANK_DEP_RTG_DT (穆迪長期本國銀行存款評等日期)
//arr[14] (國外)RTG_MDY_FC_CURR_ISSUER_RATING (穆迪外幣發行人評等)
//arr[15] (國外)RTG_MDY_FC_CURR_ISSUER_RTG_DT (穆迪外幣發行人評等日期)
//arr[16] (國外)RTG_MDY_ISSUER (穆迪發行人評等)
//arr[17] (國外)RTG_MDY_ISSUER_RTG_DT (穆迪發行人評等日期)
//arr[18] (國外)RTG_MOODY_LONG_TERM (穆迪長期評等)
//arr[19] (國外)RTG_MOODY_LONG_TERM_DATE (穆迪長期評等日期)
//arr[20] (國外)RTG_MDY_SEN_UNSECURED_DEBT (穆迪優先無擔保債務評等)
//arr[21] (國外)RTG_MDY_SEN_UNSEC_RTG_DT (穆迪優先無擔保債務評等日期)
//--惠譽(Fitch)
//arr[22] (國外)RTG_FITCH_LT_ISSUER_DEFAULT (惠譽長期發行人違約評等)
//arr[23] (國外)RTG_FITCH_LT_ISSUER_DFLT_RTG_DT (惠譽長期發行人違約評等日期)
//arr[24] (國外)RTG_FITCH_LT_FC_ISSUER_DEFAULT (惠譽長期外幣發行人違約評等)
//arr[25] (國外)RTG_FITCH_LT_FC_ISS_DFLT_RTG_DT (惠譽長期外幣發行人違約評等日期)
//arr[26] (國外)RTG_FITCH_LT_LC_ISSUER_DEFAULT (惠譽長期本國貨幣發行人違約評等)
//arr[27] (國外)RTG_FITCH_LT_LC_ISS_DFLT_RTG_DT (惠譽長期本國貨幣發行人違約評等日期)
//arr[28] (國外)RTG_FITCH_SEN_UNSECURED (惠譽優先無擔保債務評等)
//arr[29] (國外)RTG_FITCH_SEN_UNSEC_RTG_DT (惠譽優先無擔保債務評等日期)
//--惠譽台灣(Fitch(twn))
//arr[30] (國內)RTG_FITCH_NATIONAL_LT (惠譽國內長期評等)
//arr[31] (國內)RTG_FITCH_NATIONAL_LT_DT (惠譽國內長期評等日期)
//--TRC(中華信評)
//arr[32] (國內)RTG_TRC_LONG_TERM (TRC 長期評等)
//arr[33] (國內)RTG_TRC_LONG_TERM_RTG_DT (TRC 長期評等日期)
if (arr.Length >= 34)
{
//RTG_SP_LT_LC_ISSUER_CREDIT 標普本國貨幣長期發行人信用評等
validateCommpany(
arr[8],
arr[9],
arr[0],
A53CommpanyBloombergField.RTG_SP_LT_LC_ISSUER_CREDIT.ToString(),
RatingOrg.SP,
commpanyData);
//RTG_SP_LT_FC_ISSUER_CREDIT 標普長期外幣發行人信用評等
validateCommpany(
arr[10],
arr[11],
arr[0],
A53CommpanyBloombergField.RTG_SP_LT_FC_ISSUER_CREDIT.ToString(),
RatingOrg.SP,
commpanyData);
//RTG_MDY_LOCAL_LT_BANK_DEPOSITS 穆迪長期本國銀行存款評等
validateCommpany(
arr[12],
arr[13],
arr[0],
A53CommpanyBloombergField.RTG_MDY_LOCAL_LT_BANK_DEPOSITS.ToString(),
RatingOrg.Moody,
commpanyData);
//RTG_MDY_FC_CURR_ISSUER_RATING 穆迪外幣發行人評等
validateCommpany(
arr[14],
arr[15],
arr[0],
A53CommpanyBloombergField.RTG_MDY_FC_CURR_ISSUER_RATING.ToString(),
RatingOrg.Moody,
commpanyData
);
//RTG_MDY_ISSUER 穆迪發行人評等
validateCommpany(
arr[16],
arr[17],
arr[0],
A53CommpanyBloombergField.RTG_MDY_ISSUER.ToString(),
RatingOrg.Moody,
commpanyData);
//RTG_MOODY_LONG_TERM 穆迪長期評等
validateCommpany(
arr[18],
arr[19],
arr[0],
A53CommpanyBloombergField.RTG_MOODY_LONG_TERM.ToString(),
RatingOrg.Moody,
commpanyData);
//RTG_MDY_SEN_UNSECURED_DEBT 穆迪優先無擔保債務評等
validateCommpany(
arr[20],
arr[21],
arr[0],
A53CommpanyBloombergField.RTG_MDY_SEN_UNSECURED_DEBT.ToString(),
RatingOrg.Moody,
commpanyData);
//RTG_FITCH_LT_ISSUER_DEFAULT 惠譽長期發行人違約評等
validateCommpany(
arr[22],
arr[23],
arr[0],
A53CommpanyBloombergField.RTG_FITCH_LT_ISSUER_DEFAULT.ToString(),
RatingOrg.Fitch,
commpanyData);
//RTG_FITCH_LT_FC_ISSUER_DEFAULT 惠譽長期外幣發行人違約評等
validateCommpany(
arr[24],
arr[25],
arr[0],
A53CommpanyBloombergField.RTG_FITCH_LT_FC_ISSUER_DEFAULT.ToString(),
RatingOrg.Fitch,
commpanyData);
//RTG_FITCH_LT_LC_ISSUER_DEFAULT 惠譽長期本國貨幣發行人違約評等
validateCommpany(
arr[26],
arr[27],
arr[0],
A53CommpanyBloombergField.RTG_FITCH_LT_LC_ISSUER_DEFAULT.ToString(),
RatingOrg.Fitch,
commpanyData);
//RTG_FITCH_SEN_UNSECURED 惠譽優先無擔保債務評等
validateCommpany(
arr[28],
arr[29],
arr[0],
A53CommpanyBloombergField.RTG_FITCH_SEN_UNSECURED.ToString(),
RatingOrg.Fitch,
commpanyData);
//RTG_FITCH_NATIONAL_LT 惠譽國內長期評等
validateCommpany(
arr[30],
arr[31],
arr[0],
A53CommpanyBloombergField.RTG_FITCH_NATIONAL_LT.ToString(),
RatingOrg.FitchTwn,
commpanyData);
//RTG_TRC_LONG_TERM (國內)TRC 長期評等
validateCommpany(
arr[32],
arr[33],
arr[0],
A53CommpanyBloombergField.RTG_TRC_LONG_TERM.ToString(),
RatingOrg.CW,
commpanyData);
}
}
if ("START-OF-DATA".Equals(line))
{
flag = true;
}
}
}
#endregion commpany Data
#region saveDb
db.Rating_Info.RemoveRange(
db.Rating_Info.Where(x => x.Report_Date == reportDateDt));
db.Rating_Info.AddRange(sampleData);
db.Rating_Info.AddRange(commpanyData);
db.Rating_Info_SampleInfo.RemoveRange(
db.Rating_Info_SampleInfo.Where(x => x.Report_Date == reportDateDt));
db.Rating_Info_SampleInfo.AddRange(
sampleInfos.Select(x => new Rating_Info_SampleInfo()
{
Bond_Number = x.Bond_Number,
GUARANTOR_EQY_TICKER = x.GUARANTOR_EQY_TICKER,
GUARANTOR_NAME = x.GUARANTOR_NAME,
ISSUER_TICKER = x.ISSUER_TICKER,
Report_Date = reportDateDt
}));
try
{
db.SaveChanges();
db.Dispose();
log.saveTransferCheck(
type,
true,
reportDateDt,
1,
startTime,
DateTime.Now);
log.txtLog(
type,
true,
startTime,
logPath,
MessageType.Success.GetDescription());
sampleData.AddRange(commpanyData);
new CompleteEvent().saveDb(reportDateDt, verInt);
}
catch (DbUpdateException ex)
{
log.saveTransferCheck(
type,
false,
reportDateDt,
1,
startTime,
DateTime.Now);
log.txtLog(
type,
false,
startTime,
logPath,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
#endregion saveDb
}
public bool create(TableType type, string dateTime, List <string> datas)
{
bool flag = false;
try
{
List <string> data = new List <string>();
SetFile f = new SetFile(type, dateTime);
//ex: commpanyA53_20170803
string getFileName = f.getCommpanyFileName();
#region File
data.Add("START-OF-FILE");
#region Title
data.Add($"REPLYFILENAME={getFileName}");
data.Add("PROGRAMNAME=getcompany");
data.Add("PROGRAMFLAG=" + f.getPROGRAMFLAG());
data.Add("FIRMNAME=" + f.getFIRMNAME()); //確認是否提出來?
data.Add("CREDITRISK=yes");
data.Add("SECID=TICKER");
#endregion Title
//空一行
data.Add(string.Empty);
#region START-OF-FIELDS
data.Add("START-OF-FIELDS");
object obj = null;
bool findFlag = false;
if (TableType.A53.ToString().Equals(type.ToString()))
{
obj = new A53Commpany();
findFlag = true;
}
if (findFlag)
{
obj.GetType()
.GetProperties()
//.OrderBy(x => x.Name)
.ToList()
.ForEach(x => data.Add(x.Name));
}
data.Add("END-OF-FIELDS");
#endregion START-OF-FIELDS
//空一行
data.Add(string.Empty);
#region START-OF-DATA
data.Add("START-OF-DATA");
datas.ForEach(x => data.Add(string.Format("{0} Equity|TICKER", x)));
data.Add("END-OF-DATA");
#endregion START-OF-DATA
data.Add("END-OF-FILE");
#endregion File
//ex: ../commpanyPut 資料夾
//f.putCommpanyFilePath();
//ex: commpanyA53_20170803
//f.putCommpanyFileName();
flag = new CreatePutFile().create(
f.putCommpanyFilePath(),
f.putCommpanyFileName(),
data);
}
catch
{
flag = false;
}
return(flag);
}