/// <summary>
/// save A81.A82.A83
/// </summary>
/// <param name="type">(A81 or A82 or A83)</param>
/// <param name="dataModel">Exhibit10Model</param>
/// <returns></returns>
public MSGReturnModel SaveA8(string type, List <Exhibit10Model> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
try
{
bool _flag = true;
string _message = string.Empty;
List <string> A8Type = new List <string>()
{
Table_Type.A81.ToString(),
Table_Type.A82.ToString(),
Table_Type.A83.ToString()
};
if (!A8Type.Contains(type))
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription();
return(result);
}
//using (IFRS9DBEntities db = new IFRS9DBEntities())
//{
#region save Moody_Monthly_PD_Info(A81)
if (Table_Type.A81.ToString().Equals(type))
{
int id = 1;
var A81s = new List <Moody_Monthly_PD_Info>();
foreach (var item in dataModel)
{
DateTime?dt = TypeTransfer.stringToDateTimeN(item.Trailing);
A81s.Add(
new Moody_Monthly_PD_Info()
{
Id = id,
Trailing_12m_Ending = dt,
Actual_Allcorp =
TypeTransfer.stringToDoubleN(item.Actual_Allcorp),
Baseline_forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Baseline_forecast_Allcorp),
Pessimistic_Forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_Allcorp),
Actual_SG = TypeTransfer.stringToDoubleN(item.Actual_SG),
Baseline_forecast_SG = TypeTransfer.stringToDoubleN(item.Baseline_forecast_SG),
Pessimistic_Forecast_SG =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_SG),
Data_Year = (dt == null) ? string.Empty : ((DateTime)dt).Year.ToString()
});
id += 1;
}
if (A81s.Any())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
{
IFRS9DBEntities db = null;
try
{
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
if (db.Moody_Monthly_PD_Info.Any())
{
db.Moody_Monthly_PD_Info.RemoveRange(
db.Moody_Monthly_PD_Info); //資料全刪除
}
db.SaveChanges();
db.Dispose();
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var A81 in A81s)
{
++count;
db = Common.AddToContext(db, A81, count, 100, true);
}
db.SaveChanges();
}
catch (Exception ex)
{
_flag = false;
_message = ex.exceptionMessage();
}
finally
{
if (_flag)
{
scope.Complete();
}
db.Dispose();
}
}
}
}
#endregion save Moody_Monthly_PD_Info(A81)
#region save Moody_Quartly_PD_Info(A82)
if (Table_Type.A82.ToString().Equals(type))
{
int id = 1;
List <Moody_Quartly_PD_Info> A82s = new List <Moody_Quartly_PD_Info>();
List <Moody_Quartly_PD_Info> allData = new List <Moody_Quartly_PD_Info>();
List <int> months = new List <int>()
{
3, 6, 9, 12
}; //只搜尋3.6.9.12 月份
foreach (var item in dataModel
.Where(x => !string.IsNullOrWhiteSpace(x.Actual_Allcorp) && //要有Actual_Allcorp (排除今年)
months.Contains(DateTime.Parse(x.Trailing).Month)) //只搜尋3.6.9.12 月份
.OrderByDescending(x => x.Trailing)) //排序=>日期大到小
{
DateTime dt = DateTime.Parse(item.Trailing);
string quartly = dt.Year.ToString();
switch (dt.Month) //判斷季別
{
case 3:
quartly += "Q1";
break;
case 6:
quartly += "Q2";
break;
case 9:
quartly += "Q3";
break;
case 12:
quartly += "Q4";
break;
}
double?actualAllcorp = null;
if (!string.IsNullOrWhiteSpace(item.Actual_Allcorp))
{
actualAllcorp = double.Parse(item.Actual_Allcorp);
}
A82s.Add(new Moody_Quartly_PD_Info()
{
Id = id,
Data_Year = dt.Year.ToString(),
Year_Quartly = quartly,
PD = actualAllcorp
});
id += 1;
}
if (A82s.Any())
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 10, 0)))
{
IFRS9DBEntities db = null;
try
{
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
if (db.Moody_Quartly_PD_Info.Any())
{
db.Moody_Quartly_PD_Info.RemoveRange(
db.Moody_Quartly_PD_Info);
}
db.SaveChanges();
db.Dispose();
db = new IFRS9DBEntities();
db.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var A82 in A82s)
{
++count;
db = Common.AddToContext(db, A82, count, 100, true);
}
db.SaveChanges();
using (IFRS9DBEntities db2 = new IFRS9DBEntities())
{
string sql = string.Empty;
var C04s = db2.Econ_F_YYYYMMDD.ToList();
int _count = 0;
List <SqlParameter> sps = new List <SqlParameter>();
foreach (var A82 in A82s)
{
++_count;
var C04 = C04s.FirstOrDefault(x => x.Year_Quartly == A82.Year_Quartly);
if (C04 != null)
{
sql += $@"
update Econ_F_YYYYMMDD
set PD_Quartly = @PD_Quartly{_count},
LastUpdate_User = '******',
LastUpdate_Date = @LastUpdate_Date,
LastUpdate_Time = @LastUpdate_Time
where Year_Quartly = @Year_Quartly{_count};
";
sps.Add(new SqlParameter($"PD_Quartly{_count}", A82.PD));
sps.Add(new SqlParameter($"Year_Quartly{_count}", A82.Year_Quartly));
}
}
sps.Add(new SqlParameter("LastUpdate_Date", _UserInfo._date));
sps.Add(new SqlParameter("LastUpdate_Time", _UserInfo._time));
if (sql.Length > 0)
{
db.Database.ExecuteSqlCommand(sql, sps.ToArray());
}
}
}
catch (Exception ex)
{
_flag = false;
_message = ex.exceptionMessage();
}
finally
{
if (_flag)
{
scope.Complete();
}
db.Dispose();
}
}
}
}
#endregion save Moody_Quartly_PD_Info(A82)
#region save Moody_Predit_PD_Info(A83)
if (Table_Type.A83.ToString().Equals(type))
{
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (db.Moody_Predit_PD_Info.Any())
{
db.Moody_Predit_PD_Info.RemoveRange(db.Moody_Predit_PD_Info);
}
List <Exhibit10Model> models = (from q in dataModel
where !string.IsNullOrWhiteSpace(q.Actual_Allcorp) && //排除掉今年
12.Equals(DateTime.Parse(q.Trailing).Month) //只取12月
select q).ToList();
string maxYear = models.Max(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最大年
string minYear = models.Min(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最小年
double?PD = null;
double PDValue = models.Sum(x => double.Parse(x.Actual_Allcorp)) / models.Count; //計算 PD
if (PDValue > 0)
{
PD = PDValue;
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 1,
Data_Year = maxYear,
Period = minYear + "-" + maxYear,
PD_TYPE = PD_Type.Past_Year_AVG.ToString(),
PD = PD
});
var dtn = DateTime.Now.Year;
Exhibit10Model model =
dataModel.Where(x => dtn.Equals(DateTime.Parse(x.Trailing).Year) &&
12.Equals(DateTime.Parse(x.Trailing).Month)).FirstOrDefault(); //抓今年又是12月的資料
string baselineForecastAllcorp = string.Empty;
if (model != null)
{
baselineForecastAllcorp = model.Baseline_forecast_Allcorp;
}
PD = null;
if (!string.IsNullOrWhiteSpace(baselineForecastAllcorp))
{
PD = double.Parse(baselineForecastAllcorp);
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 2,
Data_Year = maxYear,
Period = dtn.ToString(),
PD_TYPE = PD_Type.Forcast.ToString(),
PD = PD
});
db.SaveChanges();
}
}
#endregion save Moody_Predit_PD_Info(A83)
if (_flag)
{
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription(type);
}
else
{
result.RETURN_FLAG = false;
result.DESCRIPTION = _message;
}
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(type,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
return(result);
}
/// <summary>
/// save A81.A82.A83
/// </summary>
/// <param name="type">(A81 or A82 or A83)</param>
/// <param name="dataModel">Exhibit10Model</param>
/// <returns></returns>
public MSGReturnModel SaveA8(string type, List <Exhibit10Model> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
try
{
List <string> A8Type = new List <string>()
{
Table_Type.A81.ToString(),
Table_Type.A82.ToString(),
Table_Type.A83.ToString()
};
if (!A8Type.Contains(type))
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription();
return(result);
}
#region save Moody_Monthly_PD_Info(A81)
if (Table_Type.A81.ToString().Equals(type))
{
if (db.Moody_Monthly_PD_Info.Any())
{
db.Moody_Monthly_PD_Info.RemoveRange(
db.Moody_Monthly_PD_Info); //資料全刪除
}
int id = 1;
foreach (var item in dataModel)
{
DateTime?dt = TypeTransfer.stringToDateTimeN(item.Trailing);
db.Moody_Monthly_PD_Info.Add(
new Moody_Monthly_PD_Info()
{
Id = id,
Trailing_12m_Ending = dt,
Actual_Allcorp =
TypeTransfer.stringToDoubleN(item.Actual_Allcorp),
Baseline_forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Baseline_forecast_Allcorp),
Pessimistic_Forecast_Allcorp =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_Allcorp),
Actual_SG = TypeTransfer.stringToDoubleN(item.Actual_SG),
Baseline_forecast_SG = TypeTransfer.stringToDoubleN(item.Baseline_forecast_SG),
Pessimistic_Forecast_SG =
TypeTransfer.stringToDoubleN(item.Pessimistic_Forecast_SG),
Data_Year = (dt == null) ? string.Empty : ((DateTime)dt).Year.ToString()
});
id += 1;
}
}
#endregion save Moody_Monthly_PD_Info(A81)
#region save Moody_Quartly_PD_Info(A82)
if (Table_Type.A82.ToString().Equals(type))
{
if (db.Moody_Quartly_PD_Info.Any())
{
db.Moody_Quartly_PD_Info.RemoveRange(
db.Moody_Quartly_PD_Info);
}
int id = 1;
List <Moody_Quartly_PD_Info> allData = new List <Moody_Quartly_PD_Info>();
List <int> months = new List <int>()
{
3, 6, 9, 12
}; //只搜尋3.6.9.12 月份
foreach (var item in dataModel
.Where(x => !string.IsNullOrWhiteSpace(x.Actual_Allcorp) && //要有Actual_Allcorp (排除今年)
months.Contains(DateTime.Parse(x.Trailing).Month)) //只搜尋3.6.9.12 月份
.OrderByDescending(x => x.Trailing)) //排序=>日期大到小
{
DateTime dt = DateTime.Parse(item.Trailing);
string quartly = dt.Year.ToString();
switch (dt.Month) //判斷季別
{
case 3:
quartly += "Q1";
break;
case 6:
quartly += "Q2";
break;
case 9:
quartly += "Q3";
break;
case 12:
quartly += "Q4";
break;
}
double?actualAllcorp = null;
if (!string.IsNullOrWhiteSpace(item.Actual_Allcorp))
{
actualAllcorp = double.Parse(item.Actual_Allcorp);
}
db.Moody_Quartly_PD_Info.Add(new Moody_Quartly_PD_Info()
{
Id = id,
Data_Year = dt.Year.ToString(),
Year_Quartly = quartly,
PD = actualAllcorp
});
id += 1;
}
}
#endregion save Moody_Quartly_PD_Info(A82)
#region save Moody_Predit_PD_Info(A83)
if (Table_Type.A83.ToString().Equals(type))
{
if (db.Moody_Predit_PD_Info.Any())
{
db.Moody_Predit_PD_Info.RemoveRange(db.Moody_Predit_PD_Info);
}
List <Exhibit10Model> models = (from q in dataModel
where !string.IsNullOrWhiteSpace(q.Actual_Allcorp) && //排除掉今年
12.Equals(DateTime.Parse(q.Trailing).Month) //只取12月
select q).ToList();
string maxYear = models.Max(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最大年
string minYear = models.Min(x => DateTime.Parse(x.Trailing)).Year.ToString(); //抓取最小年
double?PD = null;
double PDValue = models.Sum(x => double.Parse(x.Actual_Allcorp)) / models.Count; //計算 PD
if (PDValue > 0)
{
PD = PDValue;
}
//int id = 1;
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 1,
Data_Year = maxYear,
Period = minYear + "-" + maxYear,
PD_TYPE = PD_Type.Past_Year_AVG.ToString(),
PD = PD
});
var dtn = DateTime.Now.Year;
Exhibit10Model model =
dataModel.Where(x => dtn.Equals(DateTime.Parse(x.Trailing).Year) &&
12.Equals(DateTime.Parse(x.Trailing).Month)).FirstOrDefault(); //抓今年又是12月的資料
string baselineForecastAllcorp = string.Empty;
if (model != null)
{
baselineForecastAllcorp = model.Baseline_forecast_Allcorp;
}
PD = null;
if (!string.IsNullOrWhiteSpace(baselineForecastAllcorp))
{
PD = double.Parse(baselineForecastAllcorp);
}
db.Moody_Predit_PD_Info.Add(new Moody_Predit_PD_Info()
{
Id = 2,
Data_Year = maxYear,
Period = dtn.ToString(),
PD_TYPE = PD_Type.Forcast.ToString(),
PD = PD
});
}
#endregion save Moody_Predit_PD_Info(A83)
SaveChange();
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription(type);
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(type,
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
return(result);
}
