public MSGReturnModel saveB06(string actionType, B06ViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
IFRS9_Foward_Looking_Parm editData = new IFRS9_Foward_Looking_Parm();
if (actionType == "Add")
{
dataModel.Processing_Date = DateTime.Now.ToString("yyyy/MM/dd");
DateTime processingDate = DateTime.Parse(dataModel.Processing_Date);
List <IFRS9_Foward_Looking_Parm> Datas = db.IFRS9_Foward_Looking_Parm.AsNoTracking().ToList();
if (Datas
.Where(x => x.Processing_Date == processingDate &&
x.Product_Code == dataModel.Product_Code &&
x.PRJID == "無" &&
x.FLOWID == "無" &&
x.CompID == "無")
.Any() == true)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = $"資料重複：資料處理日期={dataModel.Processing_Date} 的資料 已存在";
return(result);
}
editData.CPD_Segment_Code = "";
editData.Delta_Q = double.Parse(dataModel.Delta_Q);
editData.Processing_Date = processingDate;
editData.Product_Code = dataModel.Product_Code;
editData.PRJID = "無";
editData.FLOWID = "無";
editData.CompID = "無";
}
else if (actionType == "Modify")
{
}
if (actionType == "Add")
{
db.IFRS9_Foward_Looking_Parm.Add(editData);
}
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel deleteB06(B06ViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
DateTime processingDate = DateTime.Parse(dataModel.Processing_Date);
var query = db.IFRS9_Foward_Looking_Parm
.Where(x => x.Processing_Date == processingDate &&
x.Product_Code == dataModel.Product_Code &&
x.PRJID == dataModel.PRJID &&
x.FLOWID == dataModel.FLOWID &&
x.CompID == dataModel.CompID);
db.IFRS9_Foward_Looking_Parm.RemoveRange(query);
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel deleteBondRating(string bondNumber)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
var query = db.Bond_Rating
.Where(x => x.Bond_Number == bondNumber);
db.Bond_Rating.RemoveRange(query);
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel deleteIssuerTicker(string issuer)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
var query = db.Issuer_Ticker
.Where(x => x.Issuer == issuer);
db.Issuer_Ticker.RemoveRange(query);
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel deleteA94(string country)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
var query = db.Gov_Info_Ticker
.Where(x => x.Country == country);
db.Gov_Info_Ticker.RemoveRange(query);
db.SaveChanges(); //Save
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel A62Audit(List <A62ViewModel> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
if (!dataModel.Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error
.GetDescription(Table_Type.A62.ToString());
return(result);
}
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
if (dataModel[0].Status == "1")
{
List <Moody_LGD_Info> Datas = db.Moody_LGD_Info.Where(x => x.Status == "1").ToList();
for (int i = 0; i < Datas.Count; i++)
{
Datas[i].Status = "3";
Datas[i].Auditor = AccountController.CurrentUserInfo.Name;
Datas[i].Audit_date = DateTime.Now.Date;
}
}
for (int i = 0; i < dataModel.Count; i++)
{
var dataYear = dataModel[i].Data_Year;
var lienPosition = dataModel[i].Lien_Position;
Moody_LGD_Info data = db.Moody_LGD_Info.Where(x => x.Data_Year == dataYear &&
x.Lien_Position == lienPosition)
.FirstOrDefault();
data.Status = dataModel[i].Status;
data.Auditor_Reply = dataModel[i].Auditor_Reply;
data.Auditor = AccountController.CurrentUserInfo.Name;
data.Audit_date = DateTime.Now.Date;
}
db.SaveChanges();
result.RETURN_FLAG = true;
result.DESCRIPTION = "複核完成";
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
/// <summary>
/// save A62
/// </summary>
/// <param name="dataModel">Exhibit7Model</param>
/// <returns></returns>
public MSGReturnModel saveA62(List <Exhibit7Model> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
if (!dataModel.Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error
.GetDescription(Table_Type.A62.ToString());
return(result);
}
string dataYear = dataModel.First().Data_Year;
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (db.Moody_LGD_Info
.Any(x => dataYear.Equals(x.Data_Year)))
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.already_Save
.GetDescription(Table_Type.A62.ToString());
return(result);
}
#region save Moody_LGD_Info(A62)
db.Moody_LGD_Info.AddRange(
dataModel.Select(x => new Moody_LGD_Info()
{
Data_Year = x.Data_Year,
Lien_Position = x.Lien_Position,
Recovery_Rate = double.Parse(x.Recovery_Rate),
LGD = double.Parse(x.LGD)
}));
#endregion save Moody_LGD_Info(A62)
try
{
db.SaveChanges();
result.RETURN_FLAG = true;
result.DESCRIPTION = Message_Type.save_Success.GetDescription();
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type
.save_Fail.GetDescription(Table_Type.A62.ToString(),
$"message: {ex.Message}" +
$", inner message {ex.InnerException?.InnerException?.Message}");
}
}
return(result);
}
public MSGReturnModel saveA63(List <A63ViewModel> dataModel)
{
MSGReturnModel result = new MSGReturnModel();
if (!dataModel.Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = Message_Type.parameter_Error
.GetDescription(Table_Type.A63.ToString());
return(result);
}
string dataYear = dataModel.First().Data_Year;
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (db.Moody_LGD_Info.AsNoTracking().Where(x => x.Data_Year == dataYear && x.Status == "1").Any())
{
result.RETURN_FLAG = false;
result.DESCRIPTION = $"{dataYear} 的資料狀態為 啟用，如要重新轉檔，請複核者先將原本的資料設為 暫不啟用。";
return(result);
}
db.Moody_LGD_Info.RemoveRange(db.Moody_LGD_Info
.Where(x => x.Data_Year == dataYear));
#region save Moody_LGD_Info(A62)
db.Moody_LGD_Info.AddRange(
dataModel.Select(x => new Moody_LGD_Info()
{
Data_Year = x.Data_Year,
Lien_Position = x.Lien_Position,
Recovery_Rate = double.Parse(x.Recovery_Rate),
LGD = double.Parse(x.LGD)
}));
#endregion save Moody_LGD_Info(A62)
try
{
db.SaveChanges();
result.RETURN_FLAG = true;
result.DESCRIPTION = "年度違約損失率已更新，請通知主管複核";
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel saveGuarantorTicker(string actionType, GuarantorTickerViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
if (actionType == "Add")
{
if (db.Guarantor_Ticker.AsNoTracking()
.Where(x => x.Issuer == dataModel.Issuer)
.Count() > 0)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = string.Format("資料重複：{0} 已存在", dataModel.Issuer);
return(result);
}
Guarantor_Ticker addData = new Guarantor_Ticker();
addData.Issuer = dataModel.Issuer;
addData.GUARANTOR_NAME = dataModel.GUARANTOR_NAME;
addData.GUARANTOR_EQY_TICKER = dataModel.GUARANTOR_EQY_TICKER;
addData.Create_User = _UserInfo._user;
addData.Create_Date = _UserInfo._date;
addData.Create_Time = _UserInfo._time;
db.Guarantor_Ticker.Add(addData);
}
else if (actionType == "Modify")
{
Guarantor_Ticker oldData = db.Guarantor_Ticker
.Where(x => x.Issuer == dataModel.Issuer)
.FirstOrDefault();
oldData.GUARANTOR_NAME = dataModel.GUARANTOR_NAME;
oldData.GUARANTOR_EQY_TICKER = dataModel.GUARANTOR_EQY_TICKER;
oldData.LastUpdate_User = _UserInfo._user;
oldData.LastUpdate_Date = _UserInfo._date;
oldData.LastUpdate_Time = _UserInfo._time;
}
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
/// <summary>
/// 新增,刪除,修改 A96 最後交易日
/// </summary>
/// <param name="data"></param>
/// <param name="type"></param>
/// <returns></returns>
public MSGReturnModel saveA96Trade(A96TradeViewModel data, Action_Type type)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
var reportDate = TypeTransfer.stringToDateTime(data.Report_Date);
if (type == Action_Type.Add)
{
db.Bond_Spread_Trade_Info.Add(
new Bond_Spread_Trade_Info()
{
Report_Date = reportDate,
Last_Date = TypeTransfer.stringToDateTime(data.Last_Date),
Create_User = _UserInfo._user,
Create_Date = _UserInfo._date,
Create_Time = _UserInfo._time,
LastUpdate_User = _UserInfo._user,
LastUpdate_Date = _UserInfo._date,
LastUpdate_Time = _UserInfo._time
});
result.DESCRIPTION = Message_Type.save_Success.GetDescription();
}
else if (type == Action_Type.Dele)
{
db.Bond_Spread_Trade_Info.Remove(db.Bond_Spread_Trade_Info.First(x => x.Report_Date == reportDate));
result.DESCRIPTION = Message_Type.delete_Success.GetDescription();
}
else if (type == Action_Type.Edit)
{
var _trade = db.Bond_Spread_Trade_Info.FirstOrDefault(x => x.Report_Date == reportDate);
if (_trade != null)
{
_trade.Last_Date = TypeTransfer.stringToDateTime(data.Last_Date);
_trade.LastUpdate_User = _UserInfo._user;
_trade.LastUpdate_Date = _UserInfo._date;
_trade.LastUpdate_Time = _UserInfo._time;
}
result.DESCRIPTION = Message_Type.save_Success.GetDescription();
}
try
{
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (Exception ex) {
result.DESCRIPTION = ex.exceptionMessage();
}
}
return(result);
}
public MSGReturnModel saveA94(string actionType, A94ViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
Gov_Info_Ticker editData = new Gov_Info_Ticker();
if (actionType == "Add")
{
if (db.Gov_Info_Ticker.AsNoTracking()
.Where(x => x.Country == dataModel.Country)
.FirstOrDefault() != null)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = "資料重複：您輸入的 國家 已存在";
return(result);
}
editData.Country = dataModel.Country;
}
else if (actionType == "Modify")
{
editData = db.Gov_Info_Ticker
.Where(x => x.Country == dataModel.Country)
.FirstOrDefault();
}
editData.IGS_Index_Map = dataModel.IGS_Index_Map;
editData.Short_term_Debt_Map = dataModel.Short_term_Debt_Map;
editData.Foreign_Exchange_Map = dataModel.Foreign_Exchange_Map;
editData.GDP_Yearly_Map = dataModel.GDP_Yearly_Map;
if (actionType == "Add")
{
db.Gov_Info_Ticker.Add(editData);
}
db.SaveChanges(); //Save
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
/// <summary>
/// save A95_1 (產業別對應Ticker補登檔)
/// </summary>
/// <param name="data"></param>
/// <param name="type"></param>
/// <returns></returns>
public MSGReturnModel saveA95_1(A95_1ViewModel data, Action_Type type)
{
MSGReturnModel result = new MSGReturnModel();
result.RETURN_FLAG = false;
var _tableName = Table_Type.A95_1.tableNameGetDescription();
if (data == null || data.Bond_Number.IsNullOrWhiteSpace())
{
result.DESCRIPTION = Message_Type.parameter_Error.GetDescription(_tableName);
return(result);
}
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if ((type & Action_Type.Edit) == Action_Type.Edit)
{
var _edit = db.Assessment_Sub_Kind_Ticker.FirstOrDefault(x => x.Bond_Number == data.Bond_Number);
if (_edit == null)
{
result.DESCRIPTION = Message_Type.already_Change.GetDescription(_tableName);
return(result);
}
_edit.Bloomberg_Ticker = data.Bloomberg_Ticker;
_edit.Security_Des = data.Security_Des;
_edit.LastUpdate_User = _UserInfo._user;
_edit.LastUpdate_Date = _UserInfo._date;
_edit.LastUpdate_Time = _UserInfo._time;
result.DESCRIPTION = Message_Type.update_Success.GetDescription(_tableName);
}
else if ((type & Action_Type.Dele) == Action_Type.Dele)
{
var _dele = db.Assessment_Sub_Kind_Ticker.FirstOrDefault(x => x.Bond_Number == data.Bond_Number);
if (_dele == null)
{
result.DESCRIPTION = Message_Type.already_Change.GetDescription(_tableName);
return(result);
}
db.Assessment_Sub_Kind_Ticker.Remove(_dele);
result.DESCRIPTION = Message_Type.delete_Success.GetDescription(_tableName);
}
try
{
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (DbUpdateException ex)
{
result.DESCRIPTION = ex.exceptionMessage();
}
}
return(result);
}
public void saveD04(Flow_Apply_Status data)
{
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
db.Flow_Apply_Status.Add(data);
try
{
db.SaveChanges();
}
catch (Exception ex) {
}
}
}
/// <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,
string Msg = null)
{
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (flag && db.Transfer_CheckTable.Any(x =>
x.ReportDate == reportDate &&
x.Version == version &&
x.File_Name == fileName &&
x.TransferType == "Y"))
{
return(false);
}
if (EnumUtil.GetValues <Transfer_Table_Type>()
.Select(x => x.ToString()).ToList().Contains(fileName))
{
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"),
Process = "Web",
Error_Msg = Msg
});
try
{
db.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
return(false);
}
}
public static IFRS9DBEntities UpdateToContext <T>(IFRS9DBEntities context,
T entity, int count, int commitCount, bool recreateContext) where T : class
{
if (count % commitCount == 0)
{
context.SaveChanges();
if (recreateContext)
{
context.Dispose();
context = new IFRS9DBEntities();
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
}
}
return(context);
}
//在執行 Action 之前執行
public override void OnActionExecuting(ActionExecutingContext filterContext)
{
_controllerName = (string)filterContext.RouteData.Values["controller"];
_actionName = (string)filterContext.RouteData.Values["action"];
var loginTime = AccountController.CurrentUserInfo.Ticket.UserData;
var dt = DateTime.Now.Date;
db = new IFRS9DBEntities();
var result = db.IFRS9_Browse_Log.AsNoTracking().Where(x =>
x.User_Account == AccountController.CurrentUserInfo.Name &&
System.Data.Entity.DbFunctions.TruncateTime(x.Login_Time).Value == dt)
.AsEnumerable()
.Where(x => x.Login_Time.ToString("yyyy/MM/dd HH:mm:ss") == loginTime)
.OrderByDescending(x => x.Browse_Time)
.FirstOrDefault();
if (result != null)
{
var _Event_Name_Length = 200;
var _Event_Name = formatEventName(filterContext, _eventName);
if (!_Event_Name.IsNullOrWhiteSpace())
{
_Event_Name = _Event_Name.Substring(0, _Event_Name.Length >= _Event_Name_Length ? _Event_Name_Length : _Event_Name.Length);
}
_eventLog = new IFRS9_Event_Log()
{
Menu_Id = result.Menu_Id,
User_Account = result.User_Account,
Login_Time = result.Login_Time,
Browse_Time = result.Browse_Time,
Action_Name = _actionName,
Event_Name = _Event_Name,
Event_Begin = DateTime.Now
};
db.IFRS9_Event_Log.Add(_eventLog);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
ex.exceptionMessage();
}
}
}
/// <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>
/// <param name="userAccount">執行帳號</param>
/// <param name="version">版本</param>
/// <param name="reportDate">基準日</param>
/// <returns>回傳成功或失敗</returns>
public bool saveLog(
Table_Type table,
string fileName,
string programName,
bool falg,
string deptType,
DateTime start,
DateTime end,
string userAccount,
int version = 1,
Nullable <DateTime> date = null)
{
bool flag = true;
try
{
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
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,
Version = version,
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,
User_Account = userAccount,
Report_Date = date
});
db.SaveChanges(); //DB SAVE
}
}
catch
{
flag = false;
}
return(flag);
}
public MSGReturnModel saveC13(string actionType, C13ViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
Econ_D_Var dataEdit = new Econ_D_Var();
if (actionType == "Add")
{
dataEdit.Year_Quartly = dataModel.Year_Quartly;
}
else if (actionType == "Modify")
{
dataEdit = db.Econ_D_Var
.Where(x => x.Year_Quartly == dataModel.Year_Quartly)
.FirstOrDefault();
}
dataEdit.Consumer_Price_Index = double.Parse(dataModel.Consumer_Price_Index);
dataEdit.Consumer_Price_Index_Pre_Ind = dataModel.Consumer_Price_Index_Pre_Ind;
dataEdit.Unemployment_Rate = double.Parse(dataModel.Unemployment_Rate);
dataEdit.Unemployment_Rate_Pre_Ind = dataModel.Unemployment_Rate_Pre_Ind;
if (actionType == "Add")
{
db.Econ_D_Var.Add(dataEdit);
}
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public MSGReturnModel deleteC13(string yearQuartly)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
var query = db.Econ_D_Var
.Where(x => x.Year_Quartly == yearQuartly);
db.Econ_D_Var.RemoveRange(query);
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (Exception ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.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
{
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);
}
protected override bool AuthorizeCore(HttpContextBase httpContext)
{
string controller = (string)((MvcHandler)httpContext.Handler).RequestContext.RouteData.Values["controller"];
string action = (string)((MvcHandler)httpContext.Handler).RequestContext.RouteData.Values["action"];
_href = string.Format("{0},{1}", action, controller);
bool flag = false;
string menu_id = string.Empty;
bool systemFlag = false;
if (controller == "Home" && action == "Index")
{
return(true);
}
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
var sub = db.IFRS9_Menu_Sub.FirstOrDefault(x => x.Href != null &&
x.Href.Replace(System.Environment.NewLine, string.Empty).Trim().Equals(_href));
if (sub != null) //action 為view
{
menu_id = sub.Menu_Id;
systemFlag = sub.Menu == "System";
}
else //action 為event
{
return(true);
}
if (systemFlag)
{
flag = db.IFRS9_User.FirstOrDefault(x =>
x.User_Account == AccountController.CurrentUserInfo.Name &&
x.AdminFlag) != null;
}
else
{
flag = db.IFRS9_Menu_Set.FirstOrDefault(
x => x.User_Account.Equals(AccountController.CurrentUserInfo.Name) &&
x.Menu_Id.Equals(menu_id) && x.Effective == "Y") != null;
}
if (flag)
{
var dt = DateTime.Now.Date;
var lt = TypeTransfer.stringToDateTime(AccountController.CurrentUserInfo.Ticket.UserData);
var userlog = db.IFRS9_User_Log.AsNoTracking()
.Where(x => x.User_Account == AccountController.CurrentUserInfo.Name &&
x.Login_Date == dt)
.AsEnumerable()
.Where(x => x.Login_Time.ToString("yyyy/MM/dd HH:mm:ss") == AccountController.CurrentUserInfo.Ticket.UserData).FirstOrDefault();
if (userlog != null)
{
db.IFRS9_Browse_Log.Add(new IFRS9_Browse_Log()
{
User_Account = AccountController.CurrentUserInfo.Name,
Browse_Time = DateTime.Now,
Menu_Id = menu_id,
Login_Time = userlog.Login_Time
});
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
ex.exceptionMessage();
}
}
}
return(flag);
}
public MSGReturnModel saveBondRating(string actionType, BondRatingViewModel dataModel)
{
MSGReturnModel result = new MSGReturnModel();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
try
{
if (actionType == "Add")
{
if (db.Bond_Rating.AsNoTracking()
.Where(x => x.Bond_Number == dataModel.Bond_Number)
.Count() > 0)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = string.Format("資料重複：{0} 已存在", dataModel.Bond_Number);
return(result);
}
Bond_Rating addData = new Bond_Rating();
addData.Bond_Number = dataModel.Bond_Number;
addData.S_And_P = dataModel.S_And_P;
addData.Moodys = dataModel.Moodys;
addData.Fitch = dataModel.Fitch;
addData.Fitch_TW = dataModel.Fitch_TW;
addData.TRC = dataModel.TRC;
addData.Create_User = _UserInfo._user;
addData.Create_Time = _UserInfo._time;
addData.Create_Date = _UserInfo._date;
db.Bond_Rating.Add(addData);
}
else if (actionType == "Modify")
{
Bond_Rating oldData = db.Bond_Rating
.Where(x => x.Bond_Number == dataModel.Bond_Number)
.FirstOrDefault();
oldData.S_And_P = dataModel.S_And_P;
oldData.Moodys = dataModel.Moodys;
oldData.Fitch = dataModel.Fitch;
oldData.Fitch_TW = dataModel.Fitch_TW;
oldData.TRC = dataModel.TRC;
oldData.LastUpdate_User = _UserInfo._user;
oldData.LastUpdate_Date = _UserInfo._date;
oldData.LastUpdate_Time = _UserInfo._time;
}
db.SaveChanges();
result.RETURN_FLAG = true;
}
catch (DbUpdateException ex)
{
result.RETURN_FLAG = false;
result.DESCRIPTION = ex.Message;
}
}
return(result);
}
public ActionResult Logon(string userId, string pwd)
{
bool flag = false;
var now = DateTime.Now;
if (!ModelState.IsValid)
{
TempData["User"] = userId;
TempData["Login"] = Message_Type.login_Captcha_Fail.GetDescription();
return(RedirectToAction("Login", "Account"));
}
else
{
MvcCaptcha.ResetCaptcha("ExampleCaptcha");
FileRelated.createFile(@"D:\IFRS9Log");
try
{
// set up domain context
//PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
// find the current user
//UserPrincipal aduser = UserPrincipal.Current;
//驗證AD帳號
flag = LdapAuthentication.isAuthenticatrd(userId, pwd);
}
catch
{ }
var user = new IFRS9_User();
using (IFRS9DBEntities db = new IFRS9DBEntities())
{
if (flag) //AD帳號
{
user = db.IFRS9_User.AsNoTracking().FirstOrDefault(x => x.User_Account == userId);
}
else //非AD帳號
{
user = db.IFRS9_User
.Where(x => userId.Equals(x.User_Account))
.AsEnumerable()
.FirstOrDefault(x => pwd.stringToSHA512().Equals(x.User_Password));
}
if (user != null)
{
if (user.Effective)
{
this.LoginProcess(user.User_Account, false, now);
flag = true;
user.LoginFlag = true;
string IP = System.Web.HttpContext.Current.Request
.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (string.IsNullOrEmpty(IP))
{
IP = System.Web.HttpContext.Current.Request
.ServerVariables["REMOTE_ADDR"];
}
db.IFRS9_User_Log.Add(
new IFRS9_User_Log()
{
User_Account = user.User_Account,
Login_Time = now,
Login_IP = IP,
Login_Date = now.Date
});
try
{
db.SaveChanges();
}
catch { }
}
else
{
TempData["Login"] = Message_Type.login_Effective_Fail
.GetDescription();
ModelState.AddModelError("", Message_Type.login_Effective_Fail
.GetDescription());
flag = false;
}
}
else
{
TempData["Login"] = Message_Type.login_Fail.GetDescription();
ModelState.AddModelError("", Message_Type.login_Fail.GetDescription());
flag = false;
}
}
if (flag)
{
TempData["Login"] = string.Empty;
return(RedirectToAction("Index", "Home"));
}
else
{
TempData["User"] = userId;
return(RedirectToAction("Login", "Account"));
}
}
}
