public DataTable returnEM_Affected_Cover(String subscriberName, String subscriberPassword, String subscriberCode, String productCode, String baseRisk, String riskModifier, String premiumValue, String quoteDate, String customerID, String quotationID, String EMloading)
{
// EM_Affected_Cover = Input premium / [(Premium rate) + (Base premium rate) *max( (EM loading – 25),0) / 100]
DataTable dt = new DataTable("Result");
dt.Columns.Add("loginResult", typeof(string));
dt.Columns.Add("SubcriberID", typeof(string));
DataRow dr = dt.NewRow();
try
{
Subscriber SubscriberX = new Subscriber();
SubscriberX.SubscriberName = subscriberName;
SubscriberX.SubscriberPassword = subscriberPassword;
SubscriberX.SubscriberCode = subscriberCode;
Subscriber Subscriber_sys = Subscriber_Auth(SubscriberX);
if (Subscriber_sys.ResultMessage == "Subscriber Authentication failed: Subscriber password is incorrect")
{
Subscriber_sys.ResultMessage = "Error: Subscriber Authentication failed: Subscriber password is incorrect";
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
else
{
//Check if the Subscriber hass access to the method
if (Subscriber_sys.RetrunCover == true)
{
#region "Check if the Subscriber has access to the product"
bool blnProductAllowed = true;
DateTime DTToDate = DateTime.Now;
DateTime DTFromDate = DateTime.Now;
sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
sqlConnectionX.Open();
sqlCommandX = new SqlCommand();
sqlCommandX.Connection = sqlConnectionX;
sqlCommandX.CommandType = CommandType.StoredProcedure;
sqlCommandX.CommandText = "spx_Pricing_SubscriberProductAccess";
sqlParam = new SqlParameter("SubscriberCode", subscriberCode);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("ProductCode", productCode);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("quoteDate", quoteDate);
sqlCommandX.Parameters.Add(sqlParam);
sqlDR = sqlCommandX.ExecuteReader();
while (sqlDR.Read())
{
if (sqlDR.GetValue(0).ToString() == "T")
{
blnProductAllowed = true;
}
else
{
blnProductAllowed = false;
}
DTFromDate = Convert.ToDateTime(sqlDR.GetValue(1));
DTToDate = Convert.ToDateTime(sqlDR.GetValue(2));
}
sqlDR.Close();
sqlDR.Dispose();
#endregion
if (blnProductAllowed == false)
{
Subscriber_sys.ResultMessage = "Error: Subscriber does not has access to the product";
}
else
{
//check product request is within period parameters
if (!(Convert.ToDateTime(quoteDate) > DTFromDate && Convert.ToDateTime(quoteDate) < DTToDate))
{
Subscriber_sys.ResultMessage = "Error: Product request is outside of period parameters";
}
else
{
//Get Premium (and log audit entry)
sqlCommandX = new SqlCommand();
sqlCommandX.Connection = sqlConnectionX;
sqlCommandX.CommandType = CommandType.StoredProcedure;
sqlCommandX.CommandText = "spx_PricingReturnCoverEM_Affected";
sqlParam = new SqlParameter("SubscriberID", Subscriber_sys.SubscriberID);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("ProductCode", productCode);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("BaseRisk", baseRisk);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("RiskModifierCode", riskModifier);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("PremiumValue", premiumValue);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("CustomerID", customerID);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("riskQuotationID", quotationID);
sqlCommandX.Parameters.Add(sqlParam);
sqlParam = new SqlParameter("EMloading", EMloading);
sqlCommandX.Parameters.Add(sqlParam);
sqlDR = sqlCommandX.ExecuteReader();
DataTable dtResult = new DataTable("Result");
dtResult.Load(sqlDR);
dt = dtResult;
}
}
}
else
{
if (Subscriber_sys.ResultMessage != "Successful")
{
Subscriber_sys.ResultMessage += ", subscriber does not have access to method";
}
else
{
Subscriber_sys.ResultMessage = "Subscriber Authentication failed: Subscriber does not have access to method";
}
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
}
sqlConnectionX.Close();
return dt;
}
catch (Exception ex)
{
//throw;
dr["loginResult"] = ex.Message;
dr["SubcriberID"] = DBNull.Value;
dt.Rows.Add(dr);
return dt;
}
finally
{
sqlConnectionX.Close();
}
}
public DataTable QualifyLife(String subscriberName, String subscriberPassword, String subscriberCode, String AgeNextBirthday, String TobaccoUse, String HbA1cPercent, String BMI, String PantSize, String AlcoholUnitsPerDay, String Occupation)
{
DataTable dt = new DataTable("Result");
dt.Columns.Add("loginResult", typeof(string));
dt.Columns.Add("SubcriberID", typeof(string));
DataRow dr = dt.NewRow();
bool blnLifeAvailable = true;
try
{
Subscriber SubscriberX = new Subscriber();
SubscriberX.SubscriberName = subscriberName;
SubscriberX.SubscriberPassword = subscriberPassword;
SubscriberX.SubscriberCode = subscriberCode;
Subscriber Subscriber_sys = Subscriber_Auth(SubscriberX);
if (Subscriber_sys.ResultMessage == "Subscriber Authentication failed: Subscriber password is incorrect")
{
Subscriber_sys.ResultMessage = "Error: Subscriber Authentication failed: Subscriber password is incorrect";
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
else
{
//Check if the Subscriber hass access to the method
if (Subscriber_sys.RetrunCover == true)
{
//reset the message
Subscriber_sys.ResultMessage = "";
if (Convert.ToInt16(AgeNextBirthday) < 18)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Age next birthday is below 18";
else
Subscriber_sys.ResultMessage += ", Age next birthday is below 18";
}
if (Convert.ToInt16(AgeNextBirthday) > 75)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage += "Age next birthday is above 75";
else
Subscriber_sys.ResultMessage += ", Age next birthday is above 75";
}
if ((Convert.ToBoolean(TobaccoUse) == true) && (Convert.ToInt16(HbA1cPercent) >= 12))
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "HbA1c is too high (smoker)";
else
Subscriber_sys.ResultMessage += ", HbA1c is too high (smoker)";
}
if (Convert.ToInt16(HbA1cPercent) > 14)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "HbA1c is too high";
else
Subscriber_sys.ResultMessage += ", HbA1c is too high";
}
if (BMI != "")
{
if (Convert.ToDecimal(BMI) > 44)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "BMI is too high";
else
Subscriber_sys.ResultMessage += ", BMI is too high";
}
}
if (PantSize != "")
{
if (Convert.ToInt16(PantSize) > 44)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Pant Size is too high";
else
Subscriber_sys.ResultMessage += ", Pant Size is too high";
}
}
if (Convert.ToInt16(AlcoholUnitsPerDay) > 5)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Alcohol consumption is too high";
else
Subscriber_sys.ResultMessage += ", Alcohol consumption is too high";
}
sqlCommandX = new SqlCommand();
sqlCommandX.Connection = sqlConnectionX;
sqlCommandX.CommandType = CommandType.StoredProcedure;
sqlCommandX.CommandText = "spx_Select_OccupationLimitsByOccupation";
sqlParam = new SqlParameter("Occupation", Occupation);
sqlCommandX.Parameters.Add(sqlParam);
sqlDR = sqlCommandX.ExecuteReader();
while (sqlDR.Read())
{
if (sqlDR.GetBoolean(0) == false) //sql column 0 = Life
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Occupation does not allow life cover";
else
Subscriber_sys.ResultMessage += ", Occupation does not allow life cover";
}
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage += "Successful";
DataTable dt2 = new DataTable("Result");
dt2.Columns.Add("Result", typeof(string));
DataRow dr2 = dt2.NewRow();
dr2["Result"] = Subscriber_sys.ResultMessage;
dt2.Rows.Add(dr2);
dt = dt2;
#region "Old code"
#region "Check if the Subscriber has access to the product"
//bool blnProductAllowed = true;
//DateTime DTToDate = DateTime.Now;
//DateTime DTFromDate = DateTime.Now;
//sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
//sqlConnectionX.Open();
//sqlCommandX = new SqlCommand();
//sqlCommandX.Connection = sqlConnectionX;
//sqlCommandX.CommandType = CommandType.StoredProcedure;
//sqlCommandX.CommandText = "spx_Pricing_SubscriberProductAccess";
//sqlParam = new SqlParameter("SubscriberCode", subscriberCode);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlParam = new SqlParameter("ProductCode", productCode);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlParam = new SqlParameter("quoteDate", quoteDate);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlDR = sqlCommandX.ExecuteReader();
//while (sqlDR.Read())
//{
// if (sqlDR.GetValue(0).ToString() == "T")
// {
// blnProductAllowed = true;
// }
// else
// {
// blnProductAllowed = false;
// }
// DTFromDate = Convert.ToDateTime(sqlDR.GetValue(1));
// DTToDate = Convert.ToDateTime(sqlDR.GetValue(2));
//}
//sqlDR.Close();
//sqlDR.Dispose();
#endregion
////if (blnProductAllowed == false)
////{
//// Subscriber_sys.ResultMessage = "Error: Subscriber does not has access to the product";
////}
////else
////{
// //Get Premium (and log audit entry)
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_PricingReturnCover";
// sqlParam = new SqlParameter("SubscriberID", Subscriber_sys.SubscriberID);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("ProductCode", productCode);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("BaseRisk", baseRisk);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("RiskModifierCode", riskModifier);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("PremiumValue", premiumValue);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("CustomerID", customerID);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("riskQuotationID", quotationID);
// sqlCommandX.Parameters.Add(sqlParam);
// //sqlParam = new SqlParameter("quoteDate", quoteDate);
// //sqlCommandX.Parameters.Add(sqlParam);
// sqlDR = sqlCommandX.ExecuteReader();
// DataTable dtResult = new DataTable("Result");
// dtResult.Load(sqlDR);
// dt = dtResult;
////}
#endregion
}
else
{
if (Subscriber_sys.ResultMessage != "Successful")
{
Subscriber_sys.ResultMessage += ", subscriber does not have access to method";
}
else
{
//Subscriber_sys.ResultMessage = "Subscriber Authentication failed: Subscriber does not have access to method";
}
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
}
sqlConnectionX.Close();
return dt;
}
catch (Exception ex)
{
//throw;
dr["loginResult"] = ex.Message;
dr["SubcriberID"] = DBNull.Value;
dt.Rows.Add(dr);
return dt;
}
finally
{
sqlConnectionX.Close();
}
}
public DataTable QualifyDisability(String subscriberName, String subscriberPassword, String subscriberCode, String AgeNextBirthday, String TobaccoUse, String HbA1cPercent, String BMI, String PantSize, String AlcoholUnitsPerDay, String Employment, String Qualification, String Income, String SpouseIncome, String RiskBand)
{
DataTable dt = new DataTable("Result");
dt.Columns.Add("loginResult", typeof(string));
dt.Columns.Add("SubcriberID", typeof(string));
DataRow dr = dt.NewRow();
bool blnLifeAvailable = true;
try
{
Subscriber SubscriberX = new Subscriber();
SubscriberX.SubscriberName = subscriberName;
SubscriberX.SubscriberPassword = subscriberPassword;
SubscriberX.SubscriberCode = subscriberCode;
Subscriber Subscriber_sys = Subscriber_Auth(SubscriberX);
if (Subscriber_sys.ResultMessage == "Subscriber Authentication failed: Subscriber password is incorrect")
{
Subscriber_sys.ResultMessage = "Error: Subscriber Authentication failed: Subscriber password is incorrect";
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
else
{
//Check if the Subscriber hass access to the method
if (Subscriber_sys.RetrunCover == true)
{
//reset the message
Subscriber_sys.ResultMessage = "";
if (Convert.ToInt16(AgeNextBirthday) < 18)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Age next birthday is below 18";
else
Subscriber_sys.ResultMessage += ", Age next birthday is below 18";
}
if (Convert.ToInt16(AgeNextBirthday) > 60)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage += "Age next birthday is above 60";
else
Subscriber_sys.ResultMessage += ", Age next birthday is above 60";
}
if ((Convert.ToBoolean(TobaccoUse) == true) && (Convert.ToInt16(HbA1cPercent) >= 12))
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "HbA1c is too high (smoker)";
else
Subscriber_sys.ResultMessage += ", HbA1c is too high (smoker)";
}
if (Convert.ToInt16(HbA1cPercent) > 14)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "HbA1c is too high";
else
Subscriber_sys.ResultMessage += ", HbA1c is too high";
}
if (BMI != "")
{
if (Convert.ToDecimal(BMI) > 40)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "BMI is too high";
else
Subscriber_sys.ResultMessage += ", BMI is too high";
}
}
if (PantSize != "")
{
if (Convert.ToInt16(PantSize) > 40)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Pant Size is too high";
else
Subscriber_sys.ResultMessage += ", Pant Size is too high";
}
}
if (Convert.ToInt16(AlcoholUnitsPerDay) > 5)
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Alcohol consumption is too high";
else
Subscriber_sys.ResultMessage += ", Alcohol consumption is too high";
}
if ((Employment.Contains("Unemployed") && SpouseIncome == "0"))
{
blnLifeAvailable = false;
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Unemployed";
else
Subscriber_sys.ResultMessage += ", Unemployed";
}
//sqlCommandX = new SqlCommand();
//sqlCommandX.Connection = sqlConnectionX;
//sqlCommandX.CommandType = CommandType.StoredProcedure;
//sqlCommandX.CommandText = "spx_Select_OccupationLimitsByOccupation";
//sqlParam = new SqlParameter("Occupation", Employment);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlDR = sqlCommandX.ExecuteReader();
//while (sqlDR.Read())
//{
// if (sqlDR.GetValue(0).ToString() == "0") //sql column 0 = Life
// if (Subscriber_sys.ResultMessage.Length == 0)
// Subscriber_sys.ResultMessage = "Occupation does not allow life cover";
// else
// Subscriber_sys.ResultMessage += ", Occupation does not allow life cover";
//}
//if (RiskBand == "Unemployed")
//{
// blnLifeAvailable = false;
// if (Subscriber_sys.ResultMessage.Length == 0)
// Subscriber_sys.ResultMessage = "Unemployed";
// else
// Subscriber_sys.ResultMessage += ", Unemployed";
//}
if (RiskBand == "Silver")
{
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Risk band is Silver";
else
Subscriber_sys.ResultMessage += ", Risk band is Silver";
}
if (RiskBand == "Bronze")
{
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage = "Risk band is Bronze";
else
Subscriber_sys.ResultMessage += ", Risk band is Bronze";
}
decimal decIncome = Convert.ToDecimal(Income);
int intClass = 0;
if ((decIncome >= 0) && (decIncome<10499))
{
switch (Qualification)
{
case "No matric":
intClass = 4;
break;
case "Matric":
intClass = 4;
break;
case "3 or 4 yr. Diploma/3 yr. Degree":
intClass = 3;
break;
case "4 yr. Degree/professional qualification":
intClass = 1;
break;
}
}
if ((decIncome >= 10500) && (decIncome < 15749))
{
switch (Qualification)
{
case "No matric":
intClass = 4;
break;
case "Matric":
intClass = 3;
break;
case "3 or 4 yr. Diploma/3 yr. Degree":
intClass = 2;
break;
case "4 yr. Degree/professional qualification":
intClass = 1;
break;
}
}
if ((decIncome >= 15750) && (decIncome < 26249))
{
switch (Qualification)
{
case "No matric":
intClass = 3;
break;
case "Matric":
intClass = 2;
break;
case "3 or 4 yr. Diploma/3 yr. Degree":
intClass = 1;
break;
case "4 yr. Degree/professional qualification":
intClass = 1;
break;
}
}
if ((decIncome >= 26250) && (decIncome < 41999))
{
switch (Qualification)
{
case "No matric":
intClass = 2;
break;
case "Matric":
intClass = 2;
break;
case "3 or 4 yr. Diploma/3 yr. Degree":
intClass = 1;
break;
case "4 yr. Degree/professional qualification":
intClass = 1;
break;
}
}
if (decIncome >= 42000)
{
switch (Qualification)
{
case "No matric":
intClass = 2;
break;
case "Matric":
intClass = 1;
break;
case "3 or 4 yr. Diploma/3 yr. Degree":
intClass = 1;
break;
case "4 yr. Degree/professional qualification":
intClass = 1;
break;
}
}
if (Subscriber_sys.ResultMessage.Length == 0)
Subscriber_sys.ResultMessage += "Successful";
DataTable dt2 = new DataTable("Result");
dt2.Columns.Add("Result", typeof(string));
dt2.Columns.Add("Class", typeof(string));
DataRow dr2 = dt2.NewRow();
dr2["Result"] = Subscriber_sys.ResultMessage;
dr2["Class"] = intClass;
dt2.Rows.Add(dr2);
dt = dt2;
#region "Old code"
#region "Check if the Subscriber has access to the product"
//bool blnProductAllowed = true;
//DateTime DTToDate = DateTime.Now;
//DateTime DTFromDate = DateTime.Now;
//sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
//sqlConnectionX.Open();
//sqlCommandX = new SqlCommand();
//sqlCommandX.Connection = sqlConnectionX;
//sqlCommandX.CommandType = CommandType.StoredProcedure;
//sqlCommandX.CommandText = "spx_Pricing_SubscriberProductAccess";
//sqlParam = new SqlParameter("SubscriberCode", subscriberCode);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlParam = new SqlParameter("ProductCode", productCode);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlParam = new SqlParameter("quoteDate", quoteDate);
//sqlCommandX.Parameters.Add(sqlParam);
//sqlDR = sqlCommandX.ExecuteReader();
//while (sqlDR.Read())
//{
// if (sqlDR.GetValue(0).ToString() == "T")
// {
// blnProductAllowed = true;
// }
// else
// {
// blnProductAllowed = false;
// }
// DTFromDate = Convert.ToDateTime(sqlDR.GetValue(1));
// DTToDate = Convert.ToDateTime(sqlDR.GetValue(2));
//}
//sqlDR.Close();
//sqlDR.Dispose();
#endregion
////if (blnProductAllowed == false)
////{
//// Subscriber_sys.ResultMessage = "Error: Subscriber does not has access to the product";
////}
////else
////{
// //Get Premium (and log audit entry)
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_PricingReturnCover";
// sqlParam = new SqlParameter("SubscriberID", Subscriber_sys.SubscriberID);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("ProductCode", productCode);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("BaseRisk", baseRisk);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("RiskModifierCode", riskModifier);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("PremiumValue", premiumValue);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("CustomerID", customerID);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlParam = new SqlParameter("riskQuotationID", quotationID);
// sqlCommandX.Parameters.Add(sqlParam);
// //sqlParam = new SqlParameter("quoteDate", quoteDate);
// //sqlCommandX.Parameters.Add(sqlParam);
// sqlDR = sqlCommandX.ExecuteReader();
// DataTable dtResult = new DataTable("Result");
// dtResult.Load(sqlDR);
// dt = dtResult;
////}
#endregion
}
else
{
if (Subscriber_sys.ResultMessage != "Successful")
{
Subscriber_sys.ResultMessage += ", subscriber does not have access to method";
}
else
{
//Subscriber_sys.ResultMessage = "Subscriber Authentication failed: Subscriber does not have access to method";
}
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage != "Successful")
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
}
}
sqlConnectionX.Close();
return dt;
}
catch (Exception ex)
{
//throw;
dr["loginResult"] = ex.Message;
dr["SubcriberID"] = DBNull.Value;
dt.Rows.Add(dr);
return dt;
}
finally
{
sqlConnectionX.Close();
}
}
/*
*
public string MyTest(String strValue, int intValue)
{
//sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
//sqlConnectionX.Open();
string test = ComputeHash(strValue, "SHA512", null);
return test;
//return strValue + intValue.ToString();
}
public string MyTestDecrypt(String strValue, String strValue2)
{
string test = string.Empty;
bool flag = VerifyHash(strValue, "SHA512", strValue2);
if (flag == true)
{
test = "the decrypted value is: " + strValue;
}
else
{
test = "The password is incorrect";
}
return test;
}
public DataTable loginSubscriber(String subscriberName, String subscriberPassword, String subscriberCode)
{
string s = string.Empty;
DataTable dt = new DataTable("Result");
dt.Columns.Add("loginResult", typeof(string));
dt.Columns.Add("SubcriberID", typeof(string));
DataRow dr = dt.NewRow();
try
{
Subscriber SubscriberX = new Subscriber();
SubscriberX.SubscriberName = subscriberName;
SubscriberX.SubscriberPassword = subscriberPassword;
SubscriberX.SubscriberCode = subscriberCode;
Subscriber Subscriber_sys = User_Auth(SubscriberX);
dr["loginResult"] = Subscriber_sys.ResultMessage;
if (Subscriber_sys.ResultMessage == "Successful")
{
dr["SubcriberID"] = Subscriber_sys.SubscriberID.ToString();
}
else
{
dr["SubcriberID"] = DBNull.Value;
}
dt.Rows.Add(dr);
//s = Subscriber_sys.SubscriberID.ToString();
}
catch (Exception ex)
{
//throw;
dr["loginResult"] = ex.Message;
dr["SubcriberID"] = DBNull.Value;
dt.Rows.Add(dr);
}
return dt;
//return s;
}
*/
//public PricingUser Userlogin(PricingUser User_)
//{
// PricingUser DBUser = new PricingUser();
// bool blnAreThereErrors = false;
// try
// {
// sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
// sqlConnectionX.Open();
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_Pricing_UserAuth";
// sqlParam = new SqlParameter("UserName", User_.Username);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlDR = sqlCommandX.ExecuteReader();
// while (sqlDR.Read())
// {
// DBUser.UserID = sqlDR.GetInt32(0);
// DBUser.Username = sqlDR.GetString(1);
// DBUser.Password = sqlDR.GetString(2);
// }
// sqlDR.Close();
// sqlCommandX.Cancel();
// sqlCommandX.Dispose();
// //Check the password is correct
// bool flag = VerifyHash(User_.Password, "SHA512", DBUser.Password);
// if (flag != true)
// {
// blnAreThereErrors = true;
// if (DBUser.Result != null)
// {
// DBUser.Result += ", User password is incorrect";
// }
// else
// {
// DBUser.Result = "User password is incorrect";
// }
// }
// else
// {
// DBUser.Result = "Success";
// DBUser.Password = "";
// }
// }
// catch (Exception)
// {
// //mySubscriber.ResultMessage = ex.Message;
// }
// finally
// {
// sqlDR.Close();
// sqlDR.Dispose();
// sqlConnectionX.Close();
// }
// return DBUser;
//}
//public DataSet Get_UserMenu(int UserID)
//{
// try
// {
// sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
// sqlConnectionX.Open();
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_SELECT_UserMenu";
// sqlParam = new SqlParameter("UserID", UserID);
// sqlCommandX.Parameters.Add(sqlParam);
// SqlDataAdapter daX = new SqlDataAdapter(sqlCommandX);
// DataSet dsX = new DataSet();
// daX.Fill(dsX);
// return dsX;
// }
// finally
// {
// sqlConnectionX.Close();
// }
//}
//public DataTable Get_Users()
//public DataSet Get_Users()
//{
// try
// {
// sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
// sqlConnectionX.Open();
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_Select_Users";
// //SqlDataReader dr = sqlCommandX.ExecuteReader();
// //DataTable dt = new DataTable("Users");
// //dt.Load(dr);
// //return dt;
// SqlDataAdapter daX = new SqlDataAdapter(sqlCommandX);
// DataSet dsX = new DataSet();
// daX.Fill(dsX);
// return dsX;
// }
// catch (Exception ex)
// {
// DataTable dt = new DataTable("Result");
// dt.Columns.Add("loginResult", typeof(string));
// dt.Columns.Add("SubcriberID", typeof(string));
// DataRow dr = dt.NewRow();
// dr["loginResult"] = ex.Message;
// dr["SubcriberID"] = DBNull.Value;
// dt.Rows.Add(dr);
// DataSet dsEr = new DataSet();
// dsEr.Tables.Add(dt);
// return dsEr;
// }
// finally
// {
// sqlConnectionX.Close();
// }
//}
//private Subscriber UserAuth(String User_)
//{
// Subscriber mySubscriber = new Subscriber();
// bool blnAreThereErrors = false;
// try
// {
// sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
// sqlConnectionX.Open();
// sqlCommandX = new SqlCommand();
// sqlCommandX.Connection = sqlConnectionX;
// sqlCommandX.CommandType = CommandType.StoredProcedure;
// sqlCommandX.CommandText = "spx_Pricing_SubscriberAuth";
// sqlParam = new SqlParameter("SubscriberName", SubscriberX.SubscriberName);
// sqlCommandX.Parameters.Add(sqlParam);
// sqlDR = sqlCommandX.ExecuteReader();
// while (sqlDR.Read())
// {
// mySubscriber.SubscriberID = sqlDR.GetInt32(0);
// mySubscriber.SubscriberName = sqlDR.GetString(1);
// mySubscriber.SubscriberPassword = sqlDR.GetString(2);
// mySubscriber.SubscriberCode = sqlDR.GetValue(3).ToString();
// mySubscriber.SubscriberStatus = sqlDR.GetValue(4).ToString();
// if (sqlDR.GetValue(5).ToString() == "F")
// {
// mySubscriber.RetrunRisk = false;
// }
// else
// {
// mySubscriber.RetrunRisk = true;
// }
// if (sqlDR.GetValue(6).ToString() == "F")
// {
// mySubscriber.RetrunPremium = false;
// }
// else
// {
// mySubscriber.RetrunPremium = true;
// }
// if (sqlDR.GetValue(7).ToString() == "F")
// {
// mySubscriber.RetrunCover = false;
// }
// else
// {
// mySubscriber.RetrunCover = true;
// }
// }
// sqlDR.Close();
// sqlCommandX.Cancel();
// sqlCommandX.Dispose();
// //if (mySubscriber.SubscriberID != 0)
// //{
// // //Check the Subscriber code is correct
// // if (mySubscriber.SubscriberCode != SubscriberX.SubscriberCode)
// // {
// // blnAreThereErrors = true;
// // mySubscriber.ResultMessage = "Subscriber code incorrect";
// // }
// // //Check the password is correct
// // bool flag = VerifyHash(SubscriberX.SubscriberPassword, "SHA512", mySubscriber.SubscriberPassword);
// // if (flag != true)
// // {
// // blnAreThereErrors = true;
// // if (mySubscriber.ResultMessage != null)
// // {
// // mySubscriber.ResultMessage += ", Subscriber password is incorrect";
// // }
// // else
// // {
// // mySubscriber.ResultMessage = "Subscriber password is incorrect";
// // }
// // }
// // //Check if the user is enabled
// // if (mySubscriber.SubscriberStatus == "0")
// // {
// // blnAreThereErrors = true;
// // //if (mySubscriber.ResultMessage != null)
// // //{
// // // mySubscriber.ResultMessage += ", The subscriber is disabled";
// // //}
// // //else
// // //{
// // // mySubscriber.ResultMessage = "The subscriber is disabled";
// // //}
// // mySubscriber.ResultMessage = "The subscriber is disabled";
// // }
// //}
// //else
// //{
// // blnAreThereErrors = true;
// // mySubscriber.ResultMessage = "The subscriber name does not exist";
// //}
// //if (blnAreThereErrors == true)
// //{
// // mySubscriber.ResultMessage = "Subscriber Authentication failed: " + mySubscriber.ResultMessage;
// //}
// //else
// //{
// // mySubscriber.ResultMessage = "Successful";
// //}
// }
// catch (Exception ex)
// {
// mySubscriber.ResultMessage = ex.Message;
// }
// finally
// {
// sqlDR.Close();
// sqlDR.Dispose();
// sqlConnectionX.Close();
// }
// return mySubscriber;
//}
private Subscriber Subscriber_Auth(Subscriber SubscriberX)
{
Subscriber mySubscriber = new Subscriber();
bool blnAreThereErrors = false;
try
{
sqlConnectionX = new SqlConnection(ConfigurationManager.AppSettings["WSSQLConnection"]);
sqlConnectionX.Open();
sqlCommandX = new SqlCommand();
sqlCommandX.Connection = sqlConnectionX;
sqlCommandX.CommandType = CommandType.StoredProcedure;
sqlCommandX.CommandText = "spx_Pricing_SubscriberAuth";
sqlParam = new SqlParameter("SubscriberName", SubscriberX.SubscriberName);
sqlCommandX.Parameters.Add(sqlParam);
sqlDR = sqlCommandX.ExecuteReader();
while (sqlDR.Read())
{
mySubscriber.SubscriberID = sqlDR.GetInt32(0);
mySubscriber.SubscriberName = sqlDR.GetString(1);
mySubscriber.SubscriberPassword = sqlDR.GetString(2);
mySubscriber.SubscriberCode = sqlDR.GetValue(3).ToString();
mySubscriber.SubscriberStatus = sqlDR.GetValue(4).ToString();
if (sqlDR.GetValue(5).ToString() == "F")
{
mySubscriber.RetrunRisk = false;
}
else
{
mySubscriber.RetrunRisk = true;
}
if (sqlDR.GetValue(6).ToString() == "F")
{
mySubscriber.RetrunPremium = false;
}
else
{
mySubscriber.RetrunPremium = true;
}
if (sqlDR.GetValue(7).ToString() == "F")
{
mySubscriber.RetrunCover = false;
}
else
{
mySubscriber.RetrunCover = true;
}
}
sqlDR.Close();
sqlCommandX.Cancel();
sqlCommandX.Dispose();
if (mySubscriber.SubscriberID != 0)
{
//Check the Subscriber code is correct
if (mySubscriber.SubscriberCode != SubscriberX.SubscriberCode)
{
blnAreThereErrors = true;
mySubscriber.ResultMessage = "Subscriber code incorrect";
}
//Check the password is correct
bool flag = VerifyHash(SubscriberX.SubscriberPassword, "SHA512", mySubscriber.SubscriberPassword);
if (flag != true)
{
blnAreThereErrors = true;
if (mySubscriber.ResultMessage != null)
{
mySubscriber.ResultMessage += ", Subscriber password is incorrect";
}
else
{
mySubscriber.ResultMessage = "Subscriber password is incorrect";
}
}
//Check if the user is enabled
if (mySubscriber.SubscriberStatus == "0")
{
blnAreThereErrors = true;
//if (mySubscriber.ResultMessage != null)
//{
// mySubscriber.ResultMessage += ", The subscriber is disabled";
//}
//else
//{
// mySubscriber.ResultMessage = "The subscriber is disabled";
//}
mySubscriber.ResultMessage = "The subscriber is disabled";
}
}
else
{
blnAreThereErrors = true;
mySubscriber.ResultMessage = "The subscriber name does not exist";
}
if (blnAreThereErrors == true)
{
mySubscriber.ResultMessage = "Subscriber Authentication failed: " + mySubscriber.ResultMessage;
}
else
{
mySubscriber.ResultMessage = "Successful";
}
}
catch (Exception ex)
{
mySubscriber.ResultMessage = ex.Message;
}
//finally
//{
// sqlDR.Close();
// sqlDR.Dispose();
// sqlConnectionX.Close();
//}
return mySubscriber;
}