/// <summary>
/// Log資料存到Sql(IFRS9_Log)
/// </summary>
/// <param name="tableType">table簡寫</param>
/// <param name="tableName">table名</param>
/// <param name="fileName">檔案名</param>
/// <param name="programName">專案名</param>
/// <param name="falg">成功失敗</param>
/// <param name="deptType">B:債券 M:房貸 (共用同一table時做區分)</param>
/// <param name="start">開始時間</param>
/// <param name="end">結束時間</param>
/// <returns>回傳成功或失敗</returns>
public bool saveLog(
Table_Type table,
string fileName,
string programName,
bool falg,
string deptType,
DateTime start,
DateTime end)
{
bool flag = true;
try
{
var tableName = table.GetDescription();
db.IFRS9_Log.Add(new IFRS9_Log() //寫入DB
{
Table_type = table.ToString(),
Table_name = tableName.Substring(0, (tableName.Length > 40 ? 40 : tableName.Length)),
File_name = fileName,
Program_name = programName,
Create_date = start.ToString("yyyyMMdd"),
Create_time = start.ToString("HH:mm:ss"),
End_date = end.ToString("yyyyMMdd"),
End_time = end.ToString("HH:mm:ss"),
TYPE = falg ? "Y" : "N",
Debt_Type = deptType
});
db.SaveChanges(); //DB SAVE
}
catch
{
flag = false;
}
return(flag);
}
/// <summary>
/// 轉檔紀錄存到Sql(Transfer_CheckTable)
/// </summary>
/// <param name="fileName">檔案名稱 A41,A42...</param>
/// <param name="flag">成功失敗</param>
/// <param name="reportDate">基準日</param>
/// <param name="version">版本</param>
/// <param name="start">轉檔開始時間</param>
/// <param name="end">轉檔結束時間</param>
/// <returns></returns>
public bool saveTransferCheck(
string fileName,
bool flag,
DateTime reportDate,
int version,
DateTime start,
DateTime end)
{
IFRS9Entities db = new IFRS9Entities();
if (flag && db.Transfer_CheckTable.Any(x =>
fileName != TableType.A53.ToString() &&
x.ReportDate == reportDate &&
x.Version == version &&
x.File_Name == fileName &&
x.TransferType == "Y"))
{
return(false);
}
if (EnumUtil.GetValues <TableType>()
.Select(x => x.ToString()).ToList().Contains(fileName))
{
if (fileName.Equals("A53"))
{
var A53 = db.Transfer_CheckTable
.Where(x => x.File_Name == "A53" &&
x.ReportDate == reportDate).FirstOrDefault();
if (A53 != null)
{
db.Transfer_CheckTable.Remove(A53);
}
}
db.Transfer_CheckTable.Add(new Transfer_CheckTable()
{
File_Name = fileName,
ReportDate = reportDate,
Version = version,
TransferType = flag ? "Y" : "N",
Create_date = start.ToString("yyyyMMdd"),
Create_time = start.ToString("HH:mm:ss"),
End_date = end.ToString("yyyyMMdd"),
End_time = end.ToString("HH:mm:ss"),
});
try
{
db.SaveChanges();
return(true);
}
catch
{
return(false);
}
finally
{
db.Dispose();
}
}
return(false);
}
/// <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
}
/// <summary>
/// C03 Mortgage save
/// </summary>
/// <param name="yearQuartly"></param>
public void saveC03Mortgage(string yearQuartly)
{
string logPath = log.txtLocation(TableType.C03Mortgage.ToString());
DateTime startTime = DateTime.Now;
try
{
List <Loan_default_Info> A06Data = db.Loan_default_Info
.Where(x => x.Year_Quartly == yearQuartly).ToList();
List <Econ_Domestic> A07Data = db.Econ_Domestic
.Where(x => x.Year_Quartly == yearQuartly).ToList();
if (!A06Data.Any())
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Loan_default_Info 無 " + yearQuartly + " 的資料");
}
else if (!A07Data.Any())
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Econ_Domestic 無 " + yearQuartly + " 的資料");
}
else
{
string productCode = GroupProductCode.M.GetDescription();
Group_Product_Code_Mapping gpcm = db.Group_Product_Code_Mapping.Where(x => x.Group_Product_Code.StartsWith(productCode)).FirstOrDefault();
if (gpcm == null)
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Group_Product_Code_Mapping 無房貸的 Product_Code");
}
else
{
productCode = gpcm.Product_Code;
var query = db.Econ_D_YYYYMMDD
.Where(x => x.Year_Quartly == yearQuartly);
db.Econ_D_YYYYMMDD.RemoveRange(query);
db.Econ_D_YYYYMMDD.AddRange(
A07Data.Select(x => new Econ_D_YYYYMMDD()
{
Processing_Date = DateTime.Now.ToString("yyyy/MM/dd"),
Product_Code = productCode,
Data_ID = "",
Year_Quartly = x.Year_Quartly,
PD_Quartly = A06Data[0].PD_Quartly,
var1 = Extension.doubleNToDouble(x.TWSE_Index),
var2 = Extension.doubleNToDouble(x.TWRGSARP_Index),
var3 = Extension.doubleNToDouble(x.TWGDPCON_Index),
var4 = Extension.doubleNToDouble(x.TWLFADJ_Index),
var5 = Extension.doubleNToDouble(x.TWCPI_Index),
var6 = Extension.doubleNToDouble(x.TWMSA1A_Index),
var7 = Extension.doubleNToDouble(x.TWMSA1B_Index),
var8 = Extension.doubleNToDouble(x.TWMSAM2_Index),
var9 = Extension.doubleNToDouble(x.GVTW10YR_Index),
var10 = Extension.doubleNToDouble(x.TWTRBAL_Index),
var11 = Extension.doubleNToDouble(x.TWTREXP_Index),
var12 = Extension.doubleNToDouble(x.TWTRIMP_Index),
var13 = Extension.doubleNToDouble(x.TAREDSCD_Index),
var14 = Extension.doubleNToDouble(x.TWCILI_Index),
var15 = Extension.doubleNToDouble(x.TWBOPCUR_Index),
var16 = Extension.doubleNToDouble(x.EHCATW_Index),
var17 = Extension.doubleNToDouble(x.TWINDPI_Index),
var18 = Extension.doubleNToDouble(x.TWWPI_Index),
var19 = Extension.doubleNToDouble(x.TARSYOY_Index),
var20 = Extension.doubleNToDouble(x.TWEOTTL_Index),
var21 = Extension.doubleNToDouble(x.SLDETIGT_Index),
var22 = Extension.doubleNToDouble(x.TWIRFE_Index),
var23 = Extension.doubleNToDouble(x.SINYI_HOUSE_PRICE_index),
var24 = Extension.doubleNToDouble(x.CATHAY_ESTATE_index),
var25 = Extension.doubleNToDouble(x.Real_GDP2011),
var26 = Extension.doubleNToDouble(x.MCCCTW_Index),
var27 = Extension.doubleNToDouble(x.TRDR1T_Index)
})
);
db.SaveChanges();
log.txtLog(
TableType.C03Mortgage.ToString(),
true,
startTime,
logPath,
MessageType.Success.GetDescription());
}
}
}
catch (Exception ex)
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
ex.Message);
}
}
/// <summary>
/// start Transfer
/// </summary>
/// <param name="year"></param>
public void startTransfer(string year)
{
logPath = log.txtLocation(type);
IFRS9Entities db = new IFRS9Entities();
DateTime dt = DateTime.Now;
List <string> notParm = new List <string>()
{
"Year_Quartly", "Date"
};
if (!year.IsNullOrWhiteSpace())
{
var A84datas = db.Econ_Foreign.AsNoTracking()
.Where(x => x.Year_Quartly.StartsWith(year));
if (A84datas.Any())
{
List <Econ_F_YYYYMMDD> C04s = new List <Econ_F_YYYYMMDD>();
List <string> yearQuartlys = A84datas.Select(x => x.Year_Quartly).ToList();
var A82Datas = db.Moody_Quartly_PD_Info.AsNoTracking().Where(x => yearQuartlys.Contains(x.Year_Quartly)).ToList();
A84datas.ToList().ForEach(x =>
{
Econ_F_YYYYMMDD C04Data = new Econ_F_YYYYMMDD();
C04Data = db.Econ_F_YYYYMMDD.FirstOrDefault(i => i.Year_Quartly == x.Year_Quartly);
if (C04Data != null)
{
C04Data.Processing_Date = dt.ToString("yyyyMMdd");
var A82Data = A82Datas.FirstOrDefault(z => z.Year_Quartly == x.Year_Quartly);
if (A82Data != null)
{
C04Data.PD_Quartly = A82Data.PD;
}
x.GetType().GetProperties().Where(z => !notParm.Contains(z.Name)).ToList().ForEach(
y =>
{
var p = C04Data.GetType().GetProperties().FirstOrDefault(i => i.Name == y.Name);
if (p != null)
{
p.SetValue(C04Data, y.GetValue(x));
}
});
}
else
{
C04Data = new Econ_F_YYYYMMDD();
C04Data.Processing_Date = dt.ToString("yyyyMMdd");
C04Data.Year_Quartly = x.Year_Quartly;
var A82Data = A82Datas.FirstOrDefault(z => z.Year_Quartly == x.Year_Quartly);
if (A82Data != null)
{
C04Data.PD_Quartly = A82Data.PD;
}
x.GetType().GetProperties().Where(z => !notParm.Contains(z.Name)).ToList().ForEach(
y =>
{
var p = C04Data.GetType().GetProperties().FirstOrDefault(i => i.Name == y.Name);
if (p != null)
{
p.SetValue(C04Data, y.GetValue(x));
}
});
C04s.Add(C04Data);
}
});
db.Econ_F_YYYYMMDD.AddRange(C04s);
try
{
db.SaveChanges();
db.Dispose();
log.txtLog(
type,
true,
dt,
logPath,
MessageType.Success.GetDescription());
}
catch (DbUpdateException ex)
{
log.txtLog(
type,
false,
dt,
logPath,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
}
else
{
log.txtLog(
type,
false,
dt,
logPath,
"找不到A84符合的資料(Econ_Foreign)");
}
}
}
/// <summary>
/// Db save
/// </summary>
protected void DataToDb()
{
IFRS9Entities db = new IFRS9Entities();
List <Econ_Domestic> A07Datas = new List <Econ_Domestic>();
string date = startTime.ToString("yyyyMMdd");
#region A07 Data
using (StreamReader sr = new StreamReader(Path.Combine(
setFile.getA07FilePath(), setFile.getFileName())))
{
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: TWSE Index
//arr[1] ex: 03/31/2016
//arr[2] ex: 8744.83
if (arr.Length >= 3 && !arr[0].IsNullOrWhiteSpace() &&
!arr[0].StartsWith("START") && !arr[0].StartsWith("END"))
{
Econ_Domestic ef = new Econ_Domestic();
DateTime dt = DateTime.MinValue;
double d = 0d;
string index = arr[0].Trim();
if (arr[2] != null && double.TryParse(arr[2], out d) &&
DateTime.TryParseExact(arr[1], "MM/dd/yyyy", null,
System.Globalization.DateTimeStyles.AllowWhiteSpaces,
out dt) && !index.IsNullOrWhiteSpace())
{
var YQ = dt.Year.ToString() + dt.Month.IntToYearQuartly();
var A07 = db.Econ_Domestic.Where(x => x.Year_Quartly == YQ).FirstOrDefault();
var A07Data = A07Datas.FirstOrDefault(x => x.Year_Quartly == YQ);
if (A07 != null)
{
var A07pro = A07.GetType().GetProperties()
.Where(x => x.Name.Replace("_", " ") == index.Replace("_", " ")).FirstOrDefault();
if (A07pro != null)
{
A07pro.SetValue(A07, d);
A07.Date = date;
}
}
else if (A07Data != null)
{
var A07pro = A07Data.GetType().GetProperties()
.Where(x => x.Name.Replace("_", " ") == index.Replace("_", " ")).FirstOrDefault();
if (A07pro != null)
{
A07pro.SetValue(A07Data, d);
A07Data.Date = date;
}
}
else
{
Econ_Domestic newData = new Econ_Domestic();
newData.Year_Quartly = YQ;
var A07pro = newData.GetType().GetProperties()
.Where(x => x.Name.Replace("_", " ") == index.Replace("_", " ")).FirstOrDefault();
if (A07pro != null)
{
A07pro.SetValue(newData, d);
newData.Date = date;
}
A07Datas.Add(newData);
}
}
}
}
if ("START-OF-DATA".Equals(line))
{
flag = true;
}
}
}
#endregion A07 Data
#region saveDb
try
{
db.Econ_Domestic.AddRange(A07Datas);
db.SaveChanges();
db.Dispose();
log.txtLog(
type,
true,
startTime,
logPath,
MessageType.Success.GetDescription());
}
catch (DbUpdateException ex)
{
log.txtLog(
type,
false,
startTime,
logPath,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
#endregion saveDb
}
/// <summary>
/// C03 Mortgage save
/// </summary>
/// <param name="todayDate"></param>
public void saveC03Mortgage(string todayDate)
{
string logPath = log.txtLocation(TableType.C03Mortgage.ToString());
DateTime startTime = DateTime.Now;
try
{
string thisYear = todayDate.Substring(0, 4);
string lastYear = (int.Parse(thisYear) - 1).ToString();
string startYearQuartly = lastYear + "Q1";
string endYearQuartly = thisYear + "Q4";
IEnumerable <Loan_default_Info> A06Data = db.Loan_default_Info
.Where(x => x.Year_Quartly.CompareTo(startYearQuartly) >= 0 &&
x.Year_Quartly.CompareTo(endYearQuartly) <= 0
).AsEnumerable();
IEnumerable <Econ_Domestic> A07Data = db.Econ_Domestic
.Where(x => x.Year_Quartly.CompareTo(startYearQuartly) >= 0 &&
x.Year_Quartly.CompareTo(endYearQuartly) <= 0
).AsEnumerable();
if (!A06Data.Any())
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Loan_default_Info 無 " + startYearQuartly + " ~ " + endYearQuartly + " 的資料");
}
else if (!A07Data.Any())
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Econ_Domestic 無 " + startYearQuartly + " ~ " + endYearQuartly + " 的資料");
}
else
{
string productCode = GroupProductCode.M.GetDescription();
Group_Product_Code_Mapping gpcm = db.Group_Product_Code_Mapping.Where(x => x.Group_Product_Code.StartsWith(productCode)).FirstOrDefault();
if (gpcm == null)
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
"Group_Product_Code_Mapping 無房貸的 Product_Code");
}
else
{
productCode = gpcm.Product_Code;
var query = db.Econ_D_YYYYMMDD
.Where(x => x.Year_Quartly.CompareTo(startYearQuartly) >= 0 &&
x.Year_Quartly.CompareTo(endYearQuartly) <= 0
).ToList();
db.Econ_D_YYYYMMDD.RemoveRange(query);
string nowDate = DateTime.Now.ToString("yyyy/MM/dd");
var addData = (
from a in A06Data
join b in A07Data
on new { a.Year_Quartly }
equals new { b.Year_Quartly }
select new Econ_D_YYYYMMDD()
{
Processing_Date = nowDate,
Product_Code = productCode,
Data_ID = "",
Year_Quartly = b.Year_Quartly,
PD_Quartly = a.PD_Quartly,
TWSE_Index = Extension.doubleNToDouble(b.TWSE_Index),
TWRGSARP_Index = Extension.doubleNToDouble(b.TWRGSARP_Index),
TWGDPCON_Index = Extension.doubleNToDouble(b.TWGDPCON_Index),
TWLFADJ_Index = Extension.doubleNToDouble(b.TWLFADJ_Index),
TWCPI_Index = Extension.doubleNToDouble(b.TWCPI_Index),
TWMSA1A_Index = Extension.doubleNToDouble(b.TWMSA1A_Index),
TWMSA1B_Index = Extension.doubleNToDouble(b.TWMSA1B_Index),
TWMSAM2_Index = Extension.doubleNToDouble(b.TWMSAM2_Index),
GVTW10YR_Index = Extension.doubleNToDouble(b.GVTW10YR_Index),
TWTRBAL_Index = Extension.doubleNToDouble(b.TWTRBAL_Index),
TWTREXP_Index = Extension.doubleNToDouble(b.TWTREXP_Index),
TWTRIMP_Index = Extension.doubleNToDouble(b.TWTRIMP_Index),
TAREDSCD_Index = Extension.doubleNToDouble(b.TAREDSCD_Index),
TWCILI_Index = Extension.doubleNToDouble(b.TWCILI_Index),
TWBOPCUR_Index = Extension.doubleNToDouble(b.TWBOPCUR_Index),
EHCATW_Index = Extension.doubleNToDouble(b.EHCATW_Index),
TWINDPI_Index = Extension.doubleNToDouble(b.TWINDPI_Index),
TWWPI_Index = Extension.doubleNToDouble(b.TWWPI_Index),
TARSYOY_Index = Extension.doubleNToDouble(b.TARSYOY_Index),
TWEOTTL_Index = Extension.doubleNToDouble(b.TWEOTTL_Index),
SLDETIGT_Index = Extension.doubleNToDouble(b.SLDETIGT_Index),
TWIRFE_Index = Extension.doubleNToDouble(b.TWIRFE_Index),
SINYI_HOUSE_PRICE_index = Extension.doubleNToDouble(b.SINYI_HOUSE_PRICE_index),
CATHAY_ESTATE_index = Extension.doubleNToDouble(b.CATHAY_ESTATE_index),
Real_GDP2011 = Extension.doubleNToDouble(b.Real_GDP2011),
MCCCTW_Index = Extension.doubleNToDouble(b.MCCCTW_Index),
TRDR1T_Index = Extension.doubleNToDouble(b.TRDR1T_Index)
}
).AsEnumerable();
db.Econ_D_YYYYMMDD.AddRange(addData);
db.SaveChanges();
log.txtLog(
TableType.C03Mortgage.ToString(),
true,
startTime,
logPath,
MessageType.Success.GetDescription());
}
}
}
catch (Exception ex)
{
log.txtLog(
TableType.C03Mortgage.ToString(),
false,
startTime,
logPath,
ex.Message);
}
}