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;
        }