public bool ValidateMRRate(MRRate mRRate)
{
bool returnVal = false;
string branchType = "";
string relevantLimitRange = "";
double maximumAllowedRate = 0.00;
double sumInsured = 0.00;
OracleConnection con = new OracleConnection(ConnectionString);
OracleDataReader dr;
con.Open();
String sql = "";
if (mRRate.BranchCode != null && mRRate.BranchCode != "")
{
branchType = getBranchType(mRRate.BranchCode);
}
if (branchType == "")
{
branchType = mRRate.BranchCode;
}
double MNBQMotorCarRatePoint = Properties.Settings.Default.MNBQMotorCarRatePoint;
sumInsured = mRRate.SumInsured;
if (mRRate.RiskTypeId == Properties.Settings.Default.MNBQMotorCarRiskTypeId)
{
if (sumInsured > MNBQMotorCarRatePoint)
{
sql = "SELECT MML.MAXIMUM_RATE_2 FROM MNBQ_T_MR_LIMIT MML " +
" WHERE MML.BRANCH_TYPE=:V_BRANCH_TYPE " +
" AND MML.RISK_TYPE_ID=:V_RISK_TYPE_ID " +
" AND MML.USAGE_ID=:V_USAGE_ID " +
" AND TO_DATE(SYSDATE,'DD/MM/RRRR') >= TO_DATE(MML.START_DATE,'DD/MM/RRRR') AND TO_DATE(SYSDATE,'DD/MM/RRRR') <=TO_DATE(MML.END_DATE,'DD/MM/RRRR') ";
}
else
{
sql = "SELECT MML.MAXIMUM_RATE FROM MNBQ_T_MR_LIMIT MML " +
" WHERE MML.BRANCH_TYPE=:V_BRANCH_TYPE " +
" AND MML.RISK_TYPE_ID=:V_RISK_TYPE_ID " +
" AND MML.USAGE_ID=:V_USAGE_ID " +
" AND TO_DATE(SYSDATE,'DD/MM/RRRR') >= TO_DATE(MML.START_DATE,'DD/MM/RRRR') AND TO_DATE(SYSDATE,'DD/MM/RRRR') <=TO_DATE(MML.END_DATE,'DD/MM/RRRR') ";
}
}
else
{
sql = "SELECT MML.MAXIMUM_RATE FROM MNBQ_T_MR_LIMIT MML " +
" WHERE MML.BRANCH_TYPE=:V_BRANCH_TYPE " +
" AND MML.RISK_TYPE_ID=:V_RISK_TYPE_ID " +
" AND MML.USAGE_ID=:V_USAGE_ID " +
" AND TO_DATE(SYSDATE,'DD/MM/RRRR') >= TO_DATE(MML.START_DATE,'DD/MM/RRRR') AND TO_DATE(SYSDATE,'DD/MM/RRRR') <=TO_DATE(MML.END_DATE,'DD/MM/RRRR') ";
}
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add(new OracleParameter("V_BRANCH_TYPE", branchType));
cmd.Parameters.Add(new OracleParameter("V_RISK_TYPE_ID", mRRate.RiskTypeId));
cmd.Parameters.Add(new OracleParameter("V_USAGE_ID", mRRate.VehicleClassId));
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
maximumAllowedRate = Convert.ToDouble(dr[0].ToString());
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
if (mRRate.RequestedMR <= maximumAllowedRate)
{
returnVal = true;
}
return(returnVal);
}
public bool ValidateMRRate(MRRate mRRate)
{
bool returnVal = false;
string branchType = "";
string relevantLimitRange = "";
double maximumAllowedRate = 0.00;
double sumInsured = 0.00;
OracleConnection con = new OracleConnection(ConnectionString);
OracleDataReader dr;
con.Open();
String sql = "";
if (mRRate.BranchCode != null && mRRate.BranchCode != "")
{
branchType = getBranchType(mRRate.BranchCode);
}
if (branchType == "")
{
branchType = mRRate.BranchCode;
}
string AgentTypeMNBQHNB = Properties.Settings.Default.MNBQHNB;
sumInsured = mRRate.SumInsured;
relevantLimitRange = getRelevantLimitRange(sumInsured);
if (relevantLimitRange == "")
{
relevantLimitRange = "RATE_FOR_LIMIT_5";
}
if (mRRate.AgentType == AgentTypeMNBQHNB)
{
sql = "SELECT " + relevantLimitRange + " FROM MNBQ_MR_LIMIT_GUIDE " +
" WHERE BRANCH_TYPE=:V_BRANCH_TYPE " +
" AND RISK_TYPE_ID=:V_RISK_TYPE_ID " +
" AND USAGE_ID=:V_USAGE_ID " +
" AND FUEL_TYPE_CODE=:V_FUEL_TYPE_CODE " +
" AND AGENT_TYPE_CODE=4" +
" AND MAKE_MODEL=:V_MAKE_MODEL" +
" AND YEAR_OF_MANUFACTURE=:V_YEAR_OF_MANU " +
" AND TO_DATE(SYSDATE,'DD/MM/RRRR') >= TO_DATE(START_DATE,'DD/MM/RRRR') AND TO_DATE(SYSDATE,'DD/MM/RRRR') <=TO_DATE(END_DATE,'DD/MM/RRRR') ";
}
else
{
sql = "SELECT " + relevantLimitRange + " FROM MNBQ_MR_LIMIT_GUIDE " +
" WHERE BRANCH_TYPE=:V_BRANCH_TYPE " +
" AND RISK_TYPE_ID=:V_RISK_TYPE_ID " +
" AND USAGE_ID=:V_USAGE_ID " +
" AND FUEL_TYPE_CODE=:V_FUEL_TYPE_CODE " +
" AND AGENT_TYPE_CODE=0" +
" AND MAKE_MODEL=:V_MAKE_MODEL" +
" AND YEAR_OF_MANUFACTURE=:V_YEAR_OF_MANU " +
" AND TO_DATE(SYSDATE,'DD/MM/RRRR') >= TO_DATE(START_DATE,'DD/MM/RRRR') AND TO_DATE(SYSDATE,'DD/MM/RRRR') <=TO_DATE(END_DATE,'DD/MM/RRRR') ";
}
OracleCommand cmd = new OracleCommand(sql, con);
cmd.Parameters.Add(new OracleParameter("V_BRANCH_TYPE", branchType));
cmd.Parameters.Add(new OracleParameter("V_RISK_TYPE_ID", mRRate.RiskTypeId));
cmd.Parameters.Add(new OracleParameter("V_USAGE_ID", mRRate.VehicleClassId));
cmd.Parameters.Add(new OracleParameter("V_FUEL_TYPE_CODE", mRRate.FuelTypeCode));
cmd.Parameters.Add(new OracleParameter("V_MAKE_MODEL", mRRate.MakeModelCode));
cmd.Parameters.Add(new OracleParameter("V_YEAR_OF_MANU", mRRate.YearOfManufactureValidationId));
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
maximumAllowedRate = Convert.ToDouble(dr[0].ToString());
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
if (mRRate.RequestedMR <= maximumAllowedRate)
{
returnVal = true;
}
return(returnVal);
}