using System; using System.Collections.Generic; using System.Linq; using System.Web; using AIAHTML5.API.Properties; using AIAHTML5.API.Constants; using log4net; using System.Data; using System.Data.SqlClient; using Newtonsoft.Json; using System.Collections; namespace AIAHTML5.API.Models { public class DBModel { static string dbConnectionString = System.Configuration.ConfigurationManager.AppSettings["AIADatabaseV5Context"]; private static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); public DBModel() { } protected static DataSet GetDataFromStoredProcedure(string commandText) { logger.Debug(" Inside GetSQLData for command text = " + commandText); DataSet ds = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(commandText, conn); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; ds = new DataSet(); da.Fill(ds); return ds; } internal ArrayList GetAllModules() { logger.Debug(" Inside GetAllModules"); ArrayList arrUserModules = new ArrayList(); Hashtable userModuleHash = new Hashtable(); string sp = DBConstants.GET_ALL_MODULES; DataSet ds = DBModel.GetDataFromStoredProcedure(sp); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; foreach (DataRow drModule in dt.Rows) { userModuleHash = new Hashtable(); userModuleHash.Add(AIAConstants.KEY_ID, drModule["Id"]); userModuleHash.Add(AIAConstants.KEY_NAME, drModule["Name"]); userModuleHash.Add(AIAConstants.KEY_SLUG, drModule["Slug"]); arrUserModules.Add(userModuleHash); } } return arrUserModules; } internal static User GetUserDetailsByLoginId(string loginId) { logger.Debug(" Inside GetUserDetailsByLoginId for LoginId = " + loginId); User objUser = null; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_USER_DELAILS_BY_LOGIN_ID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@sLoginId", loginId); param.Direction = ParameterDirection.Input; param.DbType = DbType.String; cmd.Parameters.Add(param); da.SelectCommand = cmd; DataTable dt = new DataTable(); da.Fill(dt); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { int tempVal; DateTime date; objUser = new User(); objUser.Id = Convert.ToInt32(dr["Id"]); objUser.FirstName = dr["FirstName"].ToString(); objUser.LastName = dr["LastName"].ToString(); objUser.EmailId = dr["EmailId"].ToString(); objUser.LoginId = dr["LoginId"].ToString(); objUser.Password = dr["Password"].ToString(); objUser.SecurityQuestionId = Int32.TryParse(dr["SecurityQuestionId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.SecurityAnswer = dr["SecurityAnswer"].ToString(); ; objUser.CreatorId = Int32.TryParse(dr["CreatorId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.CreationDate = Convert.ToDateTime(dr["CreationDate"]); objUser.DeactivationDate = DateTime.TryParse(dr["DeactivationDate"].ToString(), out date) ? date : (DateTime?)null; objUser.ModifierId = Int32.TryParse(dr["ModifierId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null; objUser.UserTypeId = Convert.ToInt32(dr["UserTypeId"]); objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr["UserTypeId"])); objUser.IsActive = Convert.ToBoolean(dr["IsActive"]); } } return objUser; } internal Hashtable GetLicenseDetailByUserId(int userId) { logger.Debug(" Inside GetUserLicenseDetailByUserId for UserId = " + userId); Hashtable hash = new Hashtable(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LICENSE_DETAILS_BY_USER_ID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@iUserId", userId); param.Direction = ParameterDirection.Input; param.DbType = DbType.Int32; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { hash.Add(AIAConstants.LICENSE_KEY_ID, ds.Tables[0].Rows[0][0]); hash.Add(AIAConstants.EDITION_KEY_ID, ds.Tables[0].Rows[0][1]); // LicenseEditionId use to update modesty in LicenseToEdition table hash.Add("LicenseEditionId", ds.Tables[0].Rows[0][2]); } } return hash; } internal ArrayList GetUserModulesByLicenseId(int licenseId) { logger.Debug(" Inside GetUserModulesByLicenseId for LicenseId = " + licenseId); ArrayList userModulelist = new ArrayList(); Hashtable modulesHash; DataSet ds = new DataSet(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; cmd.Connection = conn; cmd.CommandText = DBConstants.GET_USER_MODULES_BY_LICENSE_ID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@iLicenseId", licenseId); param.Direction = ParameterDirection.Input; param.DbType = DbType.Int32; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { modulesHash = new Hashtable(); modulesHash.Add(AIAConstants.KEY_ID, dr["Id"]); modulesHash.Add(AIAConstants.KEY_NAME, dr["Title"]); modulesHash.Add(AIAConstants.KEY_SLUG, dr["Slug"]); userModulelist.Add(modulesHash); } } } return userModulelist; } protected string GetUserTypeStringById(int userTypeId) { string userType = string.Empty; switch (userTypeId) { case (int)UserType.SUPER_ADMIN: userType = User.SUPER_ADMIN; break; case (int)UserType.GENERAL_ADMIN: userType = User.GENERAL_ADMIN; break; case (int)UserType.DISTRICT_ADMIN: userType = User.DISTRICT_ADMIN; break; case (int)UserType.CLIENT_ADMIN: userType = User.CLIENT_ADMIN; break; case (int)UserType.SINGLE_USER: userType = User.SINGLE_USER; break; case (int)UserType.CONCURRENT_USER: userType = User.CONCURRENT_USER; break; case (int)UserType.RESELLER: userType = User.RESELLER; break; case (int)UserType.TEST_ACCOUNT: userType = User.TEST_ACCOUNT; break; case (int)UserType.SITE_USER: userType = User.SITE_USER; break; } return userType; } internal static User GetUserDetailsByEmailId(string emailId) { logger.Debug(" Inside GetUserDetailsByEmailId for emailId = " + emailId); User objUser = null; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_USER_DETAILS_BY_EMAILID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@sEmailId", emailId); param.Direction = ParameterDirection.Input; param.DbType = DbType.String; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { int tempVal; DateTime date; objUser = new User(); objUser.Id = Convert.ToInt32(dr["Id"]); objUser.FirstName = dr["FirstName"].ToString(); objUser.LastName = dr["LastName"].ToString(); objUser.EmailId = dr["EmailId"].ToString(); objUser.LoginId = dr["LoginId"].ToString(); objUser.Password = dr["Password"].ToString(); objUser.SecurityQuestionId = Int32.TryParse(dr["SecurityQuestionId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.SecurityAnswer = dr["SecurityAnswer"].ToString(); ; objUser.CreatorId = Int32.TryParse(dr["CreatorId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.CreationDate = Convert.ToDateTime(dr["CreationDate"]); objUser.DeactivationDate = DateTime.TryParse(dr["DeactivationDate"].ToString(), out date) ? date : (DateTime?)null; objUser.ModifierId = Int32.TryParse(dr["ModifierId"].ToString(), out tempVal) ? tempVal : (int?)null; objUser.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null; objUser.UserTypeId = Convert.ToInt32(dr["UserTypeId"]); objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr["UserTypeId"])); objUser.IsActive = Convert.ToBoolean(dr["IsActive"]); logger.Debug("objUser.Id= " + objUser.Id + ",objUser.FirstName= " + objUser.FirstName + ",objUser.LoginId= " + objUser.LoginId + ",objUser.Password= " + objUser.Password + ",objUser.SecurityQuestionId= " + objUser.SecurityQuestionId); } } } return objUser; } internal static BypassLogin ByPassLoginDetail(string loginId, string accountNumber) { BypassLogin objUser = null; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_USER_DETAIL_BYLOGIN_AND_ACCOUNT; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@LoginId", loginId); cmd.Parameters.AddWithValue("@AccountNumber", accountNumber); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { objUser = new BypassLogin(); objUser.LoginId = dr["LoginId"].ToString(); objUser.Password = dr["Password"].ToString(); } } } return objUser; } internal static string GetUserLoginStatus(int userId,string tagName, long SessionId, bool isSiteUser,bool isAdmin) { string status=string.Empty; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_USER_LOGIN_STATUS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.AddWithValue("@tag", tagName); cmd.Parameters.AddWithValue("@sessionId", SessionId); cmd.Parameters.AddWithValue("@isSiteUser", isSiteUser); cmd.Parameters.AddWithValue("@isAdmin", isAdmin); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { status = dr["loginStatus"].ToString(); } } } return status; } internal static int ValidateAodAthenticationStatus(long SessionId, string aiaConfigKey, string aodpasskey, string CourseId) { int status = 0; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_AOD_AUTHENTICATION_STATUS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sessionId", SessionId); cmd.Parameters.AddWithValue("@aiaConfigKey", aiaConfigKey); cmd.Parameters.AddWithValue("@aodpasskey", aodpasskey); cmd.Parameters.AddWithValue("@CourseId", CourseId); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { status =Convert.ToInt32( dr["requestStatus"]); } } } return status; } internal List GetSelectedCourseList(int licenseId) { AodCourse course = null; List courselist = new List(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_AOD_COURSE_ITEMS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@licenseId", licenseId); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { course = new AodCourse(); course.CourseId= dr["CourseId"].ToString(); course.CourseName = dr["CourseName"].ToString(); course.BodySystem = dr["BodySystem"].ToString(); courselist.Add(course); } } } return courselist; } internal User GetSelectedSettings(int userId,bool isSiteUser) { logger.Debug(" Inside GetSelectedSettings for userId = " + userId); UserLexicon objlexicon = null; User objUser = null; DBModel objModel = new DBModel(); SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_SETTINGS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); cmd.Parameters.AddWithValue("@isSiteUser", isSiteUser); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { objlexicon = new UserLexicon(); objUser = new User(); objUser.Id = Convert.ToInt32(dr["UserId"]); objUser.userSelectedSkintone = dr["Skintone"].ToString(); objUser.userselectedModesty = dr["Modesty"].ToString(); objlexicon.primaryid= dr["PrimaryLexicon"].ToString(); objlexicon.secondryids = dr["SecondryLexicon"].ToString(); objUser.userLexicon = objlexicon; // logger.Debug("objUser.Id= " + objUser.Id + ",objUser.userselectedModesty= " + objUser.userselectedModesty + ",objUser.userSelectedSkintone= " + objUser.userSelectedSkintone + ",objUser.Password= " + objUser.Password + ",objUser.SecurityQuestionId= " + objUser.SecurityQuestionId); } } } return objUser; } internal static int UpdateUserPassword(dynamic userInfo, string loginId, string emailId) { logger.Debug(" Inside UpdateUserPassword for LoginId: " + loginId + ", EmailId: " + emailId); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.UPDATE_USER_PASSWORD; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sLoginId", loginId); cmd.Parameters.AddWithValue("@sEmailId", emailId); cmd.Parameters.AddWithValue("@sNewPassword", userInfo["newPassword"].ToString()); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { conn.Close(); logger.Fatal("Exception in UpdateUserPassword for LoginId: " + loginId + ", EmailId: " + emailId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal LicenseSubscriptionDetails GetLicenseSubscriptionDetailsByLicenseId(int licenseId) { logger.Debug(" Inside GetLicenseSubscriptionDetailsByLicenseId for LicenseId = " + licenseId); LicenseSubscriptionDetails licenseSubscriptionDetails = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_SUBSCRIPTION_DETAILS_BY_LICENSE_ID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@iLicenseId", licenseId); param.Direction = ParameterDirection.Input; param.DbType = DbType.Int32; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { licenseSubscriptionDetails = new LicenseSubscriptionDetails(); DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { int tempVal; DateTime date; licenseSubscriptionDetails.Id = Convert.ToInt32(dr["Id"]); licenseSubscriptionDetails.LicenseId = Convert.ToInt32(dr["LicenseId"]); licenseSubscriptionDetails.SubscriptionPlanId = Int32.TryParse(dr["SubscriptionPlanId"].ToString(), out tempVal) ? tempVal : (int?)null; licenseSubscriptionDetails.SubscriptionValidFrom = DateTime.TryParse(dr["SubscriptionValidFrom"].ToString(), out date) ? date : (DateTime?)null; licenseSubscriptionDetails.SubscriptionValidThrough = DateTime.TryParse(dr["SubscriptionValidThrough"].ToString(), out date) ? date : (DateTime?)null; licenseSubscriptionDetails.RenewalDate = DateTime.TryParse(dr["RenewalDate"].ToString(), out date) ? date : (DateTime?)null; licenseSubscriptionDetails.PaymentMode = dr["PaymentMode"].ToString(); licenseSubscriptionDetails.TotalAmount = Convert.ToDouble(dr["TotalAmount"]); licenseSubscriptionDetails.AmountPaid = Convert.ToDouble(dr["AmountPaid"]); licenseSubscriptionDetails.AmountPending = Convert.ToDouble(dr["AmountPending"]); licenseSubscriptionDetails.NoOfImages = Convert.ToInt32(dr["NoofImages"]); } } } return licenseSubscriptionDetails; } internal License GetLicenseDetailsByLicenseId(int licenseId) { logger.Debug(" inside GetLicenseDetailsByLicenseId for LicenseId = " + licenseId); License license = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LICENSE_DETAILS_BY_ID; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@Id", licenseId); param.Direction = ParameterDirection.Input; param.DbType = DbType.Int32; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { license = new License(); foreach (DataRow dr in dt.Rows) { DateTime date; license.Id = Convert.ToInt32(dr["Id"]); license.AccountNumber = dr["AccountNumber"].ToString(); license.LicenseeFirstName = dr["LicenseeFirstName"].ToString(); license.LicenseeLastName = dr["LicenseeLastName"].ToString(); license.LicenseTypeId = Convert.ToInt32(dr["LicenseTypeId"]); license.InstitutionName = dr["InstitutionName"].ToString(); license.Address1 = dr["Address1"].ToString(); license.Address2 = dr["Address2"].ToString(); license.CountryId = Convert.ToInt32(dr["CountryId"]); license.StateId = Convert.ToInt32(dr["StateId"]); license.City = dr["City"].ToString(); license.Zip = dr["Zip"].ToString(); license.Phone = dr["Phone"].ToString(); license.EmailId = dr["EmailId"].ToString(); license.TotalLogins = Convert.ToInt32(dr["TotalLogins"]); license.AccountTypeId = Convert.ToInt32(dr["AccountTypeId"]); license.IsActive = Convert.ToBoolean(dr["IsActive"]); license.IsDistrictSiteLicense = Convert.ToBoolean(dr["IsDistrictSiteLicense"]); license.CreationDate = Convert.ToDateTime(dr["CreationDate"]); license.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null; license.NoOfRenewals = Convert.ToInt32(dr["NoOfRenewals"]); license.IsTermAccepted = Convert.ToBoolean(dr["IsTermsAccepted"]); license.ProductId = dr["ProductId"].ToString(); } } } return license; } internal LicenseUserExportedImageDetail GetLicenseUserExportImageDetail(int licenseId) { logger.Debug(" inside GetLicenseUserExportImageDetail for LicenseId = " + licenseId); LicenseUserExportedImageDetail exportImageDetails = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_COUNT_EXPORTED_IMAGE; cmd.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@LicenseId", licenseId); param.Direction = ParameterDirection.Input; param.DbType = DbType.Int32; cmd.Parameters.Add(param); adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); if (ds != null && ds.Tables.Count > 0) { exportImageDetails = new LicenseUserExportedImageDetail(); DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { exportImageDetails.isExportImage = Convert.ToBoolean(dr["isExportImage"]); exportImageDetails.CountExportedImage = Convert.ToInt32(dr["CountExportedImage"]); exportImageDetails.ExptImageLimit = Convert.ToInt32(dr["ExptImageLimit"]); } } } return exportImageDetails; } internal int LicenseUserInsertExportedImageDetail(LicenseUserInsertImageDetail imageDetail) { logger.Debug(" inside LicenseUserInsertExportedImageDetail for Image= " + imageDetail.ImageName); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.INSERT_EXPORTED_IMAGE; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserId", imageDetail.UserId); cmd.Parameters.AddWithValue("@LicenseId", imageDetail.LicenseId); cmd.Parameters.AddWithValue("@ImageName", imageDetail.ImageName); cmd.Parameters.AddWithValue("@OriginalFileName", imageDetail.OriginalFileName); cmd.Parameters.AddWithValue("@Title", imageDetail.Title); cmd.Parameters.AddWithValue("@ModuleName", imageDetail.ModuleName); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in LicenseUserInsertExportedImageDetail for Image= " + imageDetail.ImageName + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal static int UpdateLicenseTermStatus(string accountNumber) { logger.Debug(" inside UpdateLicenseTermStatus for AccountNumber = " + accountNumber); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.UPDATE_LICENSE_TERM_STATUS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sAccountNumber", accountNumber); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in UpdateLicenseTermStatus for AccountNumber =" + accountNumber + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal static ArrayList GetTermsAndConditions() { logger.Debug(" inside GetTermsAndConditions"); ArrayList arrTermsAndConditions = new ArrayList(); Hashtable contentHash = null; string str = string.Empty; string spName = DBConstants.GET_TERMS_AND_CONDITIONS; DataSet ds = DBModel.GetDataFromStoredProcedure(spName); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { contentHash = new Hashtable(); contentHash.Add(AIAConstants.KEY_TITLE, dr["Title"]); contentHash.Add(AIAConstants.KEY_CONTENT, dr["Content"]); arrTermsAndConditions.Add(contentHash); } } } return arrTermsAndConditions; } internal int InsertLoginDetails(int userId) { logger.Debug(" inside InsertLoginDetails for UserId= " + userId); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.INSERT_LOGIN_DETAIL; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertLoginDetails for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal int SaveSettings(User settings) { logger.Debug(" inside InsertSettings for UserId= " + settings.Id); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.SAVE_SETTINGS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", settings.Id); cmd.Parameters.AddWithValue("@iModesty", settings.userselectedModesty); cmd.Parameters.AddWithValue("@iSkintone",settings. userSelectedSkintone); cmd.Parameters.AddWithValue("@ifont","" ); cmd.Parameters.AddWithValue("@pLexicon", settings.userLexicon.primaryid); cmd.Parameters.AddWithValue("@sLexicon", settings.userLexicon.secondryids); cmd.Parameters.AddWithValue("@isSiteUser", settings.isSiteUser); cmd.Parameters.AddWithValue("@LicenseEditionId", settings.LicenseEditionId); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertLoginDetails for UserId= " + settings.Id + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal int InsertIncorrectLoginAttempts(int userId) { logger.Debug(" inside InsertIncorrectLoginAttempts for UserId= " + userId); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.INSERT_INCORRECT_LOGIN_ATTEMPTS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal int GetIncorrectLoginAttempts(int userId) { logger.Debug(" inside GetIncorrectLoginAttempts for UserId = " + userId); int count = 0; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_INCORRECT_LOGIN_ATTEMPTS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { foreach (DataColumn dc in dt.Columns) { count = Convert.ToInt32(dr[dc]); } } } } return count; } internal int UpdateIncorrectLoginAttempts(int userId) { logger.Debug(" inside UpdateIncorrectLoginAttempts for UserId= " + userId); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.UPDATE_INCORRECT_LOGIN_ATTEMPTS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in UpdateIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } public int DeleteIncorrectLoginAttempts(int userId) { logger.Debug(" inside DeleteIncorrectLoginAttempts for UserId= " + userId); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.DELETE_INCORRECT_LOGIN_ATTEMPTS; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserId", userId); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in DeleteIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } protected ArrayList GetLoginFailureCauses() { logger.Debug(" Inside GetLoginFailureCauses"); ArrayList failureCauseList = new ArrayList(); Hashtable fcHash = null; string sp = DBConstants.GET_ALL_LOGIN_FAILURE_CAUSES; DataSet ds = DBModel.GetDataFromStoredProcedure(sp); if (ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow drFailureCause in dt.Rows) { fcHash = new Hashtable(); fcHash.Add(AIAConstants.KEY_ID, drFailureCause["Id"]); fcHash.Add(AIAConstants.KEY_DESCRIPTION, drFailureCause["Description"]); failureCauseList.Add(fcHash); } } } return failureCauseList; } internal int InsertUserLoginLog(string accountNumber, Int16 failureId, string referalUrl, string edition, string httpReferer) { logger.Debug(" inside InsertUserLoginLog for AccountNumber= " + accountNumber); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.INSERT_LOGIN_ERROR_LOG; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@nvAccountNumber", accountNumber); cmd.Parameters.AddWithValue("@dtLogDate", DateTime.Now); cmd.Parameters.AddWithValue("@tiFailureId", failureId); cmd.Parameters.AddWithValue("@nvReferalUrl", referalUrl); cmd.Parameters.AddWithValue("@nvEdition", edition); cmd.Parameters.AddWithValue("@nvHttpReferer", httpReferer); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertUserLoginLog for AccountNumber= " + accountNumber + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal BlockedUser GetUserBlockedStatusByUserId(int userId) { logger.Debug(" inside GetUserBlockedStatusByUserId for UserId= " + userId); BlockedUser blockedUser = null; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_BLOCKED_USER_BY_USER_ID; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userId", userId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); if (dt != null && dt.Rows.Count > 0) { blockedUser = new BlockedUser(); foreach (DataRow dr in dt.Rows) { blockedUser.Id = Convert.ToInt32(dr["Id"]); blockedUser.FirstName = dr["FirstName"].ToString(); blockedUser.LastName = dr["LastName"].ToString(); blockedUser.EmailId = dr["EmailId"].ToString(); blockedUser.LoginId = dr["LoginId"].ToString(); blockedUser.Password = dr["Password"].ToString(); blockedUser.AccountNumber = dr["AccountNumber"].ToString(); blockedUser.LoginTime = Convert.ToDateTime(dr["LoginTime"]); } } return blockedUser; } protected ArrayList GetBlockedUsersByUserType(int userTypeId) { logger.Debug(" inside GetBlockedUsersByUserType for UserTypeId= " + userTypeId); ArrayList blockedUsersList = new ArrayList(); BlockedUser blockedUser = null; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_BLOCKED_USERS_BY_USER_TYPE; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iUserTypeId", userTypeId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { blockedUser = new BlockedUser(); blockedUser.Id = Convert.ToInt32(dr["Id"]); blockedUser.FirstName = dr["FirstName"].ToString(); blockedUser.LastName = dr["LastName"].ToString(); blockedUser.EmailId = dr["EmailId"].ToString(); blockedUser.LoginId = dr["LoginId"].ToString(); blockedUser.Password = dr["Password"].ToString(); blockedUser.AccountNumber = dr["AccountNumber"].ToString(); blockedUser.LoginTime = Convert.ToDateTime(dr["LoginTime"]); blockedUsersList.Add(blockedUser); } } return blockedUsersList; } internal static int UnblockUser(int userId) { int result = 0; DBModel objModel = new DBModel(); result = objModel.DeleteIncorrectLoginAttempts(userId); return result; } internal static bool ValidateUserAuthenticity(string username, string password, User user) { logger.Debug(" Inside ValidateUserAuthenticity for Username = " + username + ", Password: " + password); bool result = false; try { // if ((string.Equals(username.ToUpper(), user.LoginId.ToUpper())) && (string.Equals(password, user.Password))) if (username.ToUpper().Trim().Equals(user.LoginId.ToUpper().Trim()) && (password.Trim().Equals(user.Password.Trim()))) { result = true; } else { result = false; } } catch (SqlException ex) { logger.Fatal("Exception in ValidateUserAuthenticity for Username = " + username + ", Password: " + password + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace); throw; } return result; } internal int SaveLabExerciseAttempt(LabExercise le, DataTable labExeciseQuizAttemptedData) { logger.Debug(" inside InsertUserLoginLog for userId= " + le.userId + ", labExerciseIdentifier= " + le.labExerciseIdentifier + ",lastQusetion= " + le.lastQuestion + ",totalQuestions=" + le.totalQuestions); int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.SAVE_LAB_EXERCISE_ATTEMPT; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserId", le.userId); cmd.Parameters.AddWithValue("@LabExerciseIdentifier", le.labExerciseIdentifier); cmd.Parameters.AddWithValue("@LastQuestion", le.lastQuestion); cmd.Parameters.AddWithValue("@TotalQuestions", le.totalQuestions); cmd.Parameters.AddWithValue("@LabExerciseTempTbl", labExeciseQuizAttemptedData); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertUserLoginLog for userId= " + le.userId + ", labExerciseIdentifier= " + le.labExerciseIdentifier + ",lastQusetion= " + le.lastQuestion + ",totalQuestions=" + le.totalQuestions); throw; } finally { conn.Dispose(); } return result; } internal LabExercise GetLabExercise(int userId, string labIdentifier) { logger.Debug(" Inside GetLabExercise for userId = " + userId + "and labIdentifier= " + labIdentifier); SqlConnection conn = null; try { LabExercise le = new LabExercise(); le.labExercise = new List(); DBModel objModel = new DBModel(); conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); SqlDataAdapter adapter; SqlParameter param; DataSet ds = new DataSet(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LAB_EXERCISE; cmd.CommandType = CommandType.StoredProcedure; //cmd.Parameters.AddWithValue("@UserId", userId); //cmd.Parameters.AddWithValue("@Identifier", labIdentifier); cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId; cmd.Parameters.Add("@Identifier", SqlDbType.NVarChar).Value = labIdentifier; adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); logger.Debug("dataset is filled"); if (ds != null && ds.Tables.Count > 0) { logger.Debug("ds.Tables.Count= " + ds.Tables.Count); DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { logger.Debug("dt.Rows.Count= " + dt.Rows.Count); foreach (DataRow dr in dt.Rows) { le.userId = Convert.ToInt32(dr["UserId"]); le.labExerciseIdentifier = dr["labExerciseIdentifier"].ToString(); le.lastQuestion = Convert.ToInt32(dr["LastQuestion"]); le.totalQuestions = Convert.ToInt32(dr["TotalQuestions"]); LabEcerciseDetails led = new LabEcerciseDetails(); // led.StateObject = dr["StateObject"].ToString(); led.UserAnswers = dr["UserAnswers"].ToString(); led.Score = Convert.ToInt32(dr["Score"]); led.MaxScore = Convert.ToInt32(dr["MaxScore"]); led.QuestionNo = Convert.ToInt32(dr["QuestionNo"]); led.CorrectAnswers = dr["CorrectAnswers"].ToString(); led.DragItems = dr["DragItems"].ToString(); le.labExercise.Add(led); } } } return le; } catch (SqlException ex) { logger.Fatal("Exception in GetLabExercise for userId= " + userId + ", labExerciseIdentifier= " + labIdentifier); throw; } finally { conn.Dispose(); } } //internal int InsertLabExerciseDetails(int userId, string labExerciseIdentifier, int lastQusetion, int totalQuestions, List<> labExDetails ) //{ // return 1; //} internal DataTable GetLicenseInfoBySiteUrl(string licenceAccount, int editionId) { logger.Debug(" inside GetLicenseIdBySiteUrl for UserId= " + editionId + ",licenceAccount = " + licenceAccount); // SiteUrl siteUrl = null; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LICENSEINFO_BY_SITE_URL; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sLicenseAccount", licenceAccount); cmd.Parameters.AddWithValue("@iEditionId", editionId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); return dt; } internal DataTable GetLicenseBySiteId(int siteId, int editionId) { logger.Debug(" inside GetLicenseBySiteId for siteId= " + siteId); // SiteUrl siteUrl = null; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LICENSE_BY_SITE_ID; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sSiteId", siteId); cmd.Parameters.AddWithValue("@sEditionId", editionId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); return dt; } internal int InsertSiteLoginDetails(String strAcccountNumber, String strUrlReferer, string strEdition) { int result = 0; SqlConnection conn = null; try { conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = DBConstants.INSERT_SITE_LOGIN_LOG; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@nvAccountNumber", strAcccountNumber); cmd.Parameters.AddWithValue("@nvReferalUrl", strUrlReferer); cmd.Parameters.AddWithValue("@nvEdition", strEdition); cmd.Parameters.AddWithValue("@nvHttpReferer", null); result = cmd.ExecuteNonQuery(); } catch (SqlException ex) { logger.Fatal("Exception in InsertSiteLoginDetails for strAcccountNumber= " + strAcccountNumber + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace); throw; } finally { conn.Dispose(); } return result; } internal DataTable GetEditionsForModesty(int licenceId, int buildingLevelAcc) { logger.Debug(" inside GetEditionsForModesty for licenceId= " + licenceId); // SiteUrl siteUrl = null; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_LICENSE_EDITIONS_FOR_MODESTY; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@iLicenseId", licenceId); cmd.Parameters.AddWithValue("@iBuildingLevelId", buildingLevelAcc); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); //if (dt != null && dt.Rows.Count > 0) //{ // siteUrl = new SiteUrl(); // foreach (DataRow dr in dt.Rows) // { // siteUrl.Id = Convert.ToInt32(dr["Id"]); // siteUrl.SiteIp = dr["SiteIp"].ToString(); // siteUrl.SiteIpTo = dr["SiteIpTo"].ToString(); // siteUrl.SiteMasterIpTo = dr["SiteMasterIpTo"].ToString(); // siteUrl.IsMaster = Convert.ToInt32(dr["IsMaster"]); // } //} //return siteUrl; return dt; } internal Hashtable GetEditionFeatures(byte editionId) { Hashtable objFeatures = new Hashtable(); DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_PRODUCT_FEATURES; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EditionId", editionId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { if (Convert.ToBoolean(dr["IsActive"])) { objFeatures.Add(dr["Id"], dr["Title"]); } } } return objFeatures; } internal Boolean GetModestyInfo(int LicenseId, Int16 editionId) { logger.Debug(" inside GetModestyInfo for licenseId= " + LicenseId); Boolean isModestyOn = false; DataTable dt = null; SqlConnection conn = new SqlConnection(dbConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = DBConstants.GET_MODESTY_FOR_THIS_LICENSE; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@licenseId", LicenseId); cmd.Parameters.AddWithValue("@editionId", editionId); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); if (dt != null && dt.Rows.Count > 0) { string IsModesty = dt.Rows[0]["IsModesty"].ToString(); if (IsModesty == "True") { isModestyOn = true; } else { isModestyOn = false; } } return isModestyOn; } } }