/// <summary>
/// 取得最新投資風險評估結果
/// </summary>
/// <param name="userId">用戶ID</param>
/// <returns>投資風險評估結果</returns>
public RiskEvaluationDO GetLatestRiskEvaluation(string userId)
{
RiskEvaluationDO riskEvaDO = null;
if (String.IsNullOrEmpty(userId))
{
throw new ArgumentNullException("userId");
}
string query = @"
SELECT TOP 1
[Uid],[RiskEvaId],[QuestAnswerId],[CliId],[RiskResult],
[RiskScore],[RiskAttribute],[EvaluationDate],[BusinessDate],
[IsUsed],[CreateUserId],[CreateTime],[ModifyUserId],[ModifyTime]
FROM [RiskEvaluation]
WHERE [CliId] = @CliId
ORDER BY EvaluationDate DESC;";
using (SqlConnection connection = DbConnection)
{
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add(new SqlParameter("@CliId", SqlDbType.VarChar)
{
Value = userId,
});
connection.Open();
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
if (dt.Rows.Count == 1)
{
riskEvaDO = ConvertRiskEvaluationDO(dt.Rows[0]);
}
adapter = null;
dt = null;
command = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(riskEvaDO);
}
/// <summary>
/// 判斷可否重做風險評估問卷
/// </summary>
/// <param name="riskEvaluationDO">風險評估紀錄</param>
/// <returns></returns>
public bool CheckCanEvaluteRisk(RiskEvaluationDO riskEvaluationDO)
{
bool canEvaluteRisk = true;
if (riskEvaluationDO != null)
{
bool inCuttimeRange = CheckInCuttimeRange(riskEvaluationDO);
if (inCuttimeRange && (riskEvaluationDO.IsUsed == "Y"))
{
canEvaluteRisk = false;
}
}
return(canEvaluteRisk);
}
public void CheckRiskEvaCondition_When_RiskEvaluationDO_Is_Null_Then_Success()
{
//Arrange
var riskService = new RiskEvaluationService(new CuttimeRangeInfoEntity()
{
StartTime = new DateTime(2018, 10, 08, 10, 30, 00),
EndTime = new DateTime(2018, 10, 08, 18, 30, 00),
});
RiskEvaluationDO riskEvaluationDO = null;
bool expected = true;
//Actual
bool actual = riskService.CheckCanEvaluteRisk(riskEvaluationDO);
//Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// 檢查風險紀錄評估時間是否在目前作業週期內
/// </summary>
/// <param name="riskEvaluationDO">投資風險評估結果</param>
/// <returns>檢查結果</returns>
public bool CheckInCuttimeRange(RiskEvaluationDO riskEvaluationDO)
{
bool inCuttimeRange = false;
CuttimeRangeInfoEntity cuttimeRange = GetCurrentCuttimeRange();
if (cuttimeRange == null)
{
throw new InvalidOperationException("cuttimeRange not found");
}
if ((riskEvaluationDO.EvaluationDate < cuttimeRange.EndTime) &&
(riskEvaluationDO.EvaluationDate >= cuttimeRange.StartTime))
{
inCuttimeRange = true;
}
return(inCuttimeRange);
}
public void CheckInCuttimeRangeTest_When_EvaluationDate_NotInCuttimeRange_Then_Fail()
{
//Arrage
var riskService = new RiskEvaluationService(new CuttimeRangeInfoEntity()
{
StartTime = new DateTime(2018, 10, 08, 10, 30, 00),
EndTime = new DateTime(2018, 10, 08, 18, 30, 00),
});
var riskEvaluationDO = new RiskEvaluationDO()
{
EvaluationDate = riskService.GetCurrentCuttimeRange().StartTime.AddSeconds(-1),
};
var expected = false;
//Actual
var actual = riskService.CheckInCuttimeRange(riskEvaluationDO);
//Assert
Assert.AreEqual(expected, actual);
}
public void CheckRiskEvaCondition_When_EvaluationDate_NotInCuttimeRange_And_IsUsed_Is_Y_Then_Success()
{
//Arrange
var riskService = new RiskEvaluationService(new CuttimeRangeInfoEntity()
{
StartTime = new DateTime(2018, 10, 08, 10, 30, 00),
EndTime = new DateTime(2018, 10, 08, 18, 30, 00),
});
RiskEvaluationDO riskEvaluationDO = new RiskEvaluationDO()
{
EvaluationDate = riskService.GetCurrentCuttimeRange().StartTime.AddSeconds(-1),
IsUsed = "Y",
};
bool expected = true;
//Actual
bool actual = riskService.CheckCanEvaluteRisk(riskEvaluationDO);
//Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// 儲存評估投資風險評估結果資料
/// </summary>
/// <param name="riskResultId">風險評估結果識別代號</param>
public void SaveRiskRank(string riskResultId)
{
if (String.IsNullOrEmpty(riskResultId))
{
throw new ArgumentNullException("riskResultId");
}
RiskEvaResultDTO riskEvaResultDTO = GetRiskResult(riskResultId);
if (riskEvaResultDTO == null)
{
var ex = new InvalidOperationException("riskEvaResultDTO not found");
ex.Data["riskResultId"] = riskResultId;
throw ex;
}
else if (riskEvaResultDTO.QuestionnaireResultEntity == null)
{
var ex = new InvalidOperationException("QuestionnaireResultEntity not found");
ex.Data["riskResultId"] = riskResultId;
throw ex;
}
QuestionnaireResultEntity questResultEntity = riskEvaResultDTO.QuestionnaireResultEntity;
RiskEvaluationDO riskEvaluationDO = new RiskEvaluationDAO().GetLatestRiskEvaluation(
questResultEntity.TesteeId);
bool canEvaluteRisk = CheckCanEvaluteRisk(riskEvaluationDO);
if (!canEvaluteRisk)
{
var ex = new InvalidOperationException("Not can used risk evaluation");
ex.Data["canEvaluteRisk"] = canEvaluteRisk;
throw ex;
}
DateTime currenTime = DateTime.Now;
if (riskEvaluationDO == null)
{
riskEvaluationDO = new RiskEvaluationDO()
{
Uid = Guid.NewGuid(),
RiskEvaId = _riskEvaIdFundInvestment,
QuestAnswerId = questResultEntity.QuestAnswerId,
CliId = questResultEntity.TesteeId,
RiskResult = string.Join(";", questResultEntity.RiskResult.Select(x => $"[{x.Key}:{x.Value}]")),
RiskScore = questResultEntity.ActualScore,
RiskAttribute = questResultEntity.RiskRankKind,
EvaluationDate = currenTime,
BusinessDate = ConvertBusinessDate(currenTime),
IsUsed = "N",
CreateUserId = questResultEntity.TesteeId,
CreateTime = currenTime,
ModifyUserId = null,
ModifyTime = null,
};
new RiskEvaluationDAO().Insert(riskEvaluationDO);
}
else
{
bool inCuttimeRange = CheckInCuttimeRange(riskEvaluationDO);
if (inCuttimeRange)
{
riskEvaluationDO.QuestAnswerId = questResultEntity.QuestAnswerId;
riskEvaluationDO.RiskResult = String.Join(";", questResultEntity.RiskResult.Select(x => $"[{x.Key}:{x.Value}]"));
riskEvaluationDO.RiskScore = questResultEntity.ActualScore;
riskEvaluationDO.RiskAttribute = questResultEntity.RiskRankKind;
riskEvaluationDO.EvaluationDate = currenTime;
riskEvaluationDO.BusinessDate = ConvertBusinessDate(currenTime);
riskEvaluationDO.IsUsed = "N";
riskEvaluationDO.ModifyUserId = questResultEntity.TesteeId;
riskEvaluationDO.ModifyTime = currenTime;
new RiskEvaluationDAO().Update(riskEvaluationDO);
}
else
{
riskEvaluationDO = new RiskEvaluationDO()
{
Uid = Guid.NewGuid(),
RiskEvaId = _riskEvaIdFundInvestment,
QuestAnswerId = questResultEntity.QuestAnswerId,
CliId = questResultEntity.TesteeId,
RiskResult = String.Join(";", questResultEntity.RiskResult.Select(x => $"[{x.Key}:{x.Value}]")),
RiskScore = questResultEntity.ActualScore,
RiskAttribute = questResultEntity.RiskRankKind,
EvaluationDate = currenTime,
BusinessDate = ConvertBusinessDate(currenTime),
IsUsed = "N",
CreateUserId = questResultEntity.TesteeId,
CreateTime = currenTime,
ModifyUserId = null,
ModifyTime = null,
};
new RiskEvaluationDAO().Insert(riskEvaluationDO);
}
}
}
/// <summary>
/// 更新投資風險評估結果
/// </summary>
/// <param name="riskEvaluation">投資風險評估結果資料</param>
public void Update(RiskEvaluationDO riskEvaluation)
{
if (riskEvaluation == null)
{
throw new ArgumentNullException("riskEvaluation");
}
string query = @"
UPDATE [RiskEvaluation]
SET [QuestAnswerId] = @QuestAnswerId,
[RiskResult] = @RiskResult,
[RiskScore] = @RiskScore,
[RiskAttribute] = @RiskAttribute,
[EvaluationDate] = @EvaluationDate,
[BusinessDate] = @BusinessDate,
[IsUsed] = @IsUsed,
[ModifyUserId] = @ModifyUserId,
[ModifyTime] = @ModifyTime
WHERE [Uid] = @Uid;";
using (SqlConnection connection = DbConnection)
{
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add(new SqlParameter("@Uid", SqlDbType.VarChar)
{
Value = riskEvaluation.Uid.ToString()
});
command.Parameters.Add(new SqlParameter("@QuestAnswerId", SqlDbType.VarChar)
{
Value = riskEvaluation.QuestAnswerId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@RiskResult", SqlDbType.VarChar)
{
Value = riskEvaluation.RiskResult
});
command.Parameters.Add(new SqlParameter("@RiskScore", SqlDbType.Int)
{
Value = riskEvaluation.RiskScore ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@RiskAttribute", SqlDbType.VarChar)
{
Value = riskEvaluation.RiskAttribute ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@EvaluationDate", SqlDbType.DateTime)
{
Value = riskEvaluation.EvaluationDate ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@BusinessDate", SqlDbType.DateTime)
{
Value = riskEvaluation.BusinessDate ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@IsUsed", SqlDbType.VarChar)
{
Value = riskEvaluation.IsUsed ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@ModifyUserId", SqlDbType.VarChar)
{
Value = riskEvaluation.ModifyUserId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@ModifyTime", SqlDbType.DateTime)
{
Value = riskEvaluation.ModifyTime ?? Convert.DBNull
});
connection.Open();
command.ExecuteNonQuery();
command = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
/// <summary>
/// 新增投資風險評估結果
/// </summary>
/// <param name="riskEvaluation">投資風險評估結果資料</param>
public void Insert(RiskEvaluationDO riskEvaluation)
{
if (riskEvaluation == null)
{
throw new ArgumentNullException("riskEvaluation");
}
string query = @"
INSERT INTO RiskEvaluation
([Uid],[RiskEvaId],[QuestAnswerId],[CliId],[RiskResult],[RiskScore],[RiskAttribute],
[EvaluationDate],[BusinessDate],[IsUsed],[CreateUserId],[CreateTime],
[ModifyUserId],[ModifyTime])
VALUES
(@Uid, @RiskEvaId, @QuestAnswerId, @CliId, @RiskResult, @RiskScore, @RiskAttribute,
@EvaluationDate, @BusinessDate, @IsUsed, @CreateUserId, @CreateTime,
@ModifyUserId, @ModifyTime);";
using (SqlConnection connection = DbConnection)
{
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add(new SqlParameter("@Uid", SqlDbType.VarChar)
{
Value = riskEvaluation.Uid.ToString()
});
command.Parameters.Add(new SqlParameter("@RiskEvaId", SqlDbType.VarChar)
{
Value = riskEvaluation.RiskEvaId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@QuestAnswerId", SqlDbType.VarChar)
{
Value = riskEvaluation.QuestAnswerId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@CliId", SqlDbType.VarChar)
{
Value = riskEvaluation.CliId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@RiskResult", SqlDbType.VarChar)
{
Value = riskEvaluation.RiskResult
});
command.Parameters.Add(new SqlParameter("@RiskScore", SqlDbType.Int)
{
Value = riskEvaluation.RiskScore ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@RiskAttribute", SqlDbType.VarChar)
{
Value = riskEvaluation.RiskAttribute ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@EvaluationDate", SqlDbType.DateTime)
{
Value = riskEvaluation.EvaluationDate ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@BusinessDate", SqlDbType.DateTime)
{
Value = riskEvaluation.BusinessDate ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@IsUsed", SqlDbType.VarChar)
{
Value = riskEvaluation.IsUsed ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@CreateUserId", SqlDbType.VarChar)
{
Value = riskEvaluation.CreateUserId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@CreateTime", SqlDbType.DateTime)
{
Value = riskEvaluation.CreateTime ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@ModifyUserId", SqlDbType.VarChar)
{
Value = riskEvaluation.ModifyUserId ?? Convert.DBNull
});
command.Parameters.Add(new SqlParameter("@ModifyTime", SqlDbType.DateTime)
{
Value = riskEvaluation.ModifyTime ?? Convert.DBNull
});
connection.Open();
command.ExecuteNonQuery();
command = null;
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
