/// <summary> /// convert server side encrypted value to client side encrypted value /// - first decrypt value by server key , and then encrypt by client's key /// </summary> /// <param name="value">encrypted value(server)</param> /// <param name="key">client key</param> /// <returns>encrypted value(client)</returns> public static string EncryptFromSeverToClient(string value, string key) { if (string.IsNullOrEmpty(value)) { return(null); } string val = value; if (SecureEngine.IsEncrypted(value)) { val = SecureEngine.Decrypt(value); } return(SecureEngineH5.Encrypt(val, key, key)); }
/// <summary> /// return is credential provide by user is right or not /// </summary> /// <param name="model">login model class</param> /// <param name="roles">out roles , has role list of user</param> /// <param name="ctx" ></param> /// <returns>true if athenicated</returns> public static bool Login(LoginModel model, out List <KeyNamePair> roles) { roles = null; // loginModel = null; //bool isMatch = false; SecureEngine.Encrypt("t"); //Initialize // Cannot use encrypted password if (model.Login1Model.Password != null && SecureEngine.IsEncrypted(model.Login1Model.Password)) { //log.warning("Cannot use Encrypted Password"); return(false); } // Authentification bool authenticated = false; bool isLDAP = false; MSystem system = MSystem.Get(new Ctx()); if (system != null && system.IsLDAP()) { authenticated = system.IsLDAP(model.Login1Model.UserName, model.Login1Model.Password); if (authenticated) { model.Login1Model.Password = null; } isLDAP = true; // if not authenticated, use AD_User as backup } StringBuilder sql = new StringBuilder("SELECT u.AD_User_ID, r.AD_Role_ID,r.Name,") .Append(" u.ConnectionProfile, u.Password ") // 4,5 .Append("FROM AD_User u") .Append(" INNER JOIN AD_User_Roles ur ON (u.AD_User_ID=ur.AD_User_ID AND ur.IsActive='Y')") .Append(" INNER JOIN AD_Role r ON (ur.AD_Role_ID=r.AD_Role_ID AND r.IsActive='Y') "); //.Append("WHERE COALESCE(u.LDAPUser,u.Name)=@username") // #1 if (isLDAP && authenticated) { sql.Append(" WHERE (COALESCE(u.LDAPUser,u.Value)=@username)"); } else if (isLDAP && !authenticated && model.Login1Model.Password == null)// If user not authenicated using LDAP, then if LDAP user is available { sql.Append(" WHERE (u.LDAPUser=@username OR u.Name=@username OR u.Value=@username)"); } else { sql.Append(" WHERE (u.Name=@username OR u.Value=@username)"); } sql.Append(" AND u.IsActive='Y' ") .Append(" AND u.IsLoginUser='******' ") .Append(" AND EXISTS (SELECT * FROM AD_Client c WHERE u.AD_Client_ID=c.AD_Client_ID AND c.IsActive='Y')") .Append(" AND EXISTS (SELECT * FROM AD_Client c WHERE r.AD_Client_ID=c.AD_Client_ID AND c.IsActive='Y')"); string sqlEnc = "select isencrypted from ad_column where ad_table_id=(select ad_table_id from ad_table where tablename='AD_User') and columnname='Password'"; char isEncrypted = Convert.ToChar(DB.ExecuteScalar(sqlEnc)); if (model.Login1Model.Password != null) { if (isEncrypted == 'Y') { sql.Append(" AND (u.Password='******')"); // #2/3 } else { sql.Append(" AND (u.Password='******')"); // #2/3 } } sql.Append(" ORDER BY r.Name"); IDataReader dr = null; //try //{ SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@username", model.Login1Model.UserName); // execute a query dr = DB.ExecuteReader(sql.ToString(), param); if (!dr.Read()) // no record found { dr.Close(); return(false); } int AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); //User Id roles = new List <KeyNamePair>(); //roles List <int> usersRoles = new List <int>(); do // read all roles { AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); int AD_Role_ID = Util.GetValueOfInt(dr[1].ToString()); String Name = dr[2].ToString(); KeyNamePair p = new KeyNamePair(AD_Role_ID, Name); roles.Add(p); usersRoles.Add(AD_Role_ID); }while (dr.Read()); dr.Close(); model.Login1Model.AD_User_ID = AD_User_ID; IDataReader drLogin = null; if (model.Login2Model == null) { try { //* Change sub query into ineer join */ drLogin = DB.ExecuteReader(" SELECT l.AD_Role_ID," + " (SELECT r.Name FROM AD_ROLE r WHERE r.AD_Role_ID=l.AD_ROLE_ID) as RoleName," + " l.AD_Org_ID," + " (SELECT o.Name FROM AD_Org o WHERE o.AD_Org_ID=l.AD_Org_ID) as OrgName," + " l.AD_Client_ID," + " (SELECT c.Name FROM AD_Client c WHERE c.AD_Client_ID=l.AD_Client_ID) as ClientName," + " l.M_Warehouse_ID," + " (SELECT m.Name FROM M_Warehouse m WHERE m.M_Warehouse_Id = l.M_Warehouse_ID) as WarehouseName" + " FROM AD_LoginSetting l WHERE l.IsActive = 'Y' AND l.AD_User_ID=" + AD_User_ID); if (drLogin.Read()) { bool deleteRecord = false; //1 firt check - Check role exist //if (usersRoles.Contains(Util.GetValueOfInt(drLogin[0]))) //{ // //check for Org Access Setting // bool isUseUserOrgAccess = Util.GetValueOfString(DB.ExecuteScalar("SELECT IsUseUserOrgAccess FROM AD_ROLE WHERE AD_ROLE_ID = " + drLogin[0].ToString())) == "Y"; // if (isUseUserOrgAccess) //User User Org // { // if (Convert.ToInt32(DB.ExecuteScalar("SELECT Count(1) FROM AD_User_OrgAccess WHERE AD_User_ID = " + AD_User_ID + " AND AD_ORG_ID= " + drLogin[2].ToString() + " AND IsActive='Y'")) < 1) // { // deleteRecord = true; // } // } // else //User Role Org Access // { // if (Convert.ToInt32(DB.ExecuteScalar("SELECT Count(1) FROM AD_Role_OrgAccess WHERE AD_Role_ID = " + drLogin[0] + " AND AD_ORG_ID= " + drLogin[2].ToString() + " AND IsActive='Y'")) < 1) // { // deleteRecord = true; // } // } //} //else //{ // deleteRecord = true; //} //Delete Login Setting if (deleteRecord) { DB.ExecuteQuery("DELETE FROM AD_LoginSetting WHERE AD_User_ID = " + AD_User_ID); } else { model.Login2Model = new Login2Model(); model.Login2Model.Role = drLogin[0].ToString(); model.Login2Model.RoleName = drLogin[1].ToString(); model.Login2Model.Org = drLogin[2].ToString(); model.Login2Model.OrgName = drLogin[3].ToString(); model.Login2Model.Client = drLogin[4].ToString(); model.Login2Model.ClientName = drLogin[5].ToString(); model.Login2Model.Warehouse = drLogin[6].ToString(); model.Login2Model.WarehouseName = drLogin[7].ToString(); model.Login2Model.Date = System.DateTime.Now.Date; } } drLogin.Close(); } catch { if (drLogin != null) { drLogin.Close(); } } } return(true); }
/// <summary> /// Get Roles for the user with email in client with the web store. /// If the user does not have roles and the web store has a default role, it will return that. /// </summary> /// <param name="eMail">email add</param> /// <param name="password">password</param> /// <param name="W_Store_ID">web store</param> /// <returns></returns> private KeyNamePair[] GetRolesByEmail(String eMail, String password, int W_Store_ID) { long start = CommonFunctions.CurrentTimeMillis(); if (eMail == null || eMail.Length == 0 || password == null || password.Length == 0 || W_Store_ID == 0) { return(null); } // Cannot use encrypted password if (SecureEngine.IsEncrypted(password)) { return(null); } KeyNamePair[] retValue = null; List <KeyNamePair> list = new List <KeyNamePair>(); // String sql = "SELECT u.AD_User_ID, r.AD_Role_ID, u.Name " + "FROM AD_User u" + " INNER JOIN W_Store ws ON (u.AD_Client_ID=ws.AD_Client_ID) " + " INNER JOIN AD_Role r ON (ws.AD_Role_ID=r.AD_Role_ID) " + "WHERE u.EMail='" + eMail + "'" + " AND (u.Password='******' OR u.Password='******')" + " AND ws.W_Store_ID='" + W_Store_ID + "'" + " AND (r.IsActive='Y' OR r.IsActive IS NULL)" + " AND u.isActive='Y' AND ws.IsActive='Y'" + " AND u.AD_Client_ID=ws.AD_Client_ID " + "ORDER BY r.Name"; m_roles.Clear(); m_users.Clear(); IDataReader dr = null; try { // execute a query dr = DataBase.DB.ExecuteReader(sql); if (!dr.Read()) { dr.Close(); return(null); } int AD_User_ID = Utility.Util.GetValueOfInt(dr[0].ToString()); m_ctx.SetAD_User_ID(AD_User_ID); m_user = new KeyNamePair(AD_User_ID, eMail); m_users.Add(AD_User_ID); // for role // int AD_Role_ID = Utility.Util.GetValueOfInt(dr[1].ToString()); m_ctx.SetAD_Role_ID(AD_Role_ID); String Name = dr[2].ToString(); m_ctx.SetContext("##AD_User_Name", Name); if (AD_Role_ID == 0) // User is a Sys Admin { m_ctx.SetContext("#SysAdmin", "Y"); } KeyNamePair p = new KeyNamePair(AD_Role_ID, Name); m_roles.Add(p); list.Add(p); dr.Close(); // retValue = new KeyNamePair[list.Count]; retValue = list.ToArray(); } catch { if (dr != null) { dr.Close(); } retValue = null; m_ctx.SetContext("##AD_User_Name", eMail); } return(retValue); }
} // prepare /** * Process * @return info * @throws Exception */ protected override String DoIt()// throws Exception { log.Info("AD_Column_ID=" + p_AD_Column_ID + ", IsEncrypted=" + p_IsEncrypted + ", ChangeSetting=" + p_ChangeSetting + ", MaxLength=" + p_MaxLength); MColumn column = new MColumn(GetCtx(), p_AD_Column_ID, Get_Trx()); if (column.Get_ID() == 0 || column.Get_ID() != p_AD_Column_ID) { throw new Exception("@NotFound@ @AD_Column_ID@ - " + p_AD_Column_ID); } // String columnName = column.GetColumnName(); int dt = column.GetAD_Reference_ID(); // Can it be enabled? if (column.IsKey() || column.IsParent() || column.IsStandardColumn() || column.IsVirtualColumn() || column.IsIdentifier() || column.IsTranslated() || DisplayType.IsLookup(dt) || DisplayType.IsLOB(dt) || "DocumentNo".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) || "Value".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase) || "Name".Equals(column.GetColumnName(), StringComparison.OrdinalIgnoreCase)) { if (column.IsEncrypted()) { column.SetIsEncrypted(false); column.Save(Get_Trx()); } return(columnName + ": cannot be encrypted"); } // Start AddLog(0, null, null, "Encryption Class = " + SecureEngine.GetClassName()); bool error = false; // Test Value if (p_TestValue != null && p_TestValue.Length > 0) { String encString = SecureEngine.Encrypt(p_TestValue); AddLog(0, null, null, "Encrypted Test Value=" + encString); String clearString = SecureEngine.Decrypt(encString); if (p_TestValue.Equals(clearString)) { AddLog(0, null, null, "Decrypted=" + clearString + " (same as test value)"); } else { AddLog(0, null, null, "Decrypted=" + clearString + " (NOT the same as test value - check algorithm)"); error = true; } int encLength = encString.Length; AddLog(0, null, null, "Test Length=" + p_TestValue.Length + " -> " + encLength); if (encLength <= column.GetFieldLength()) { AddLog(0, null, null, "Encrypted Length (" + encLength + ") fits into field (" + column.GetFieldLength() + ")"); } else { AddLog(0, null, null, "Encrypted Length (" + encLength + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field"); error = true; } } // Length Test if (p_MaxLength != 0) { String testClear = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; while (testClear.Length < p_MaxLength) { testClear += testClear; } testClear = testClear.Substring(0, p_MaxLength); log.Config("Test=" + testClear + " (" + p_MaxLength + ")"); // String encString = SecureEngine.Encrypt(testClear); int encLength = encString.Length; AddLog(0, null, null, "Test Max Length=" + testClear.Length + " -> " + encLength); if (encLength <= column.GetFieldLength()) { AddLog(0, null, null, "Encrypted Max Length (" + encLength + ") fits into field (" + column.GetFieldLength() + ")"); } else { AddLog(0, null, null, "Encrypted Max Length (" + encLength + ") does NOT fit into field (" + column.GetFieldLength() + ") - resize field"); error = true; } } if (p_IsEncrypted != column.IsEncrypted()) { if (error || !p_ChangeSetting) { AddLog(0, null, null, "Encryption NOT changed - Encryption=" + column.IsEncrypted()); } else { column.SetIsEncrypted(p_IsEncrypted); if (column.Save(Get_Trx())) { AddLog(0, null, null, "Encryption CHANGED - Encryption=" + column.IsEncrypted()); } else { AddLog(0, null, null, "Save Error"); } } } if (p_IsEncrypted == column.IsEncrypted() && !error) // Done By Karan on 10-nov-2016, to encrypt/decrypt passwords according to settings. { //object colID = DB.ExecuteScalar("SELECT AD_Column_ID FROM AD_Column WHERE AD_Table_ID =(SELECT AD_Table_ID From AD_Table WHERE TableName='AD_User') AND ColumnName='Password'", null, Get_Trx()); // if (colID != null && colID != DBNull.Value && Convert.ToInt32(colID) == column.GetAD_Column_ID()) //{ string tableName = MTable.GetTableName(GetCtx(), column.GetAD_Table_ID()); DataSet ds = DB.ExecuteDataset("SELECT " + column.GetColumnName() + "," + tableName + "_ID FROM " + tableName, null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { if (p_IsEncrypted) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value && !SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())) { //MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID()) + "_ID"]), Get_Trx()); //user.SetPassword(SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())); int encLength = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()).Length; if (encLength <= column.GetFieldLength()) { //PO tab = MTable.GetPO(GetCtx(), tableName, // Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx()); //tab.Set_Value(column.GetColumnName(), (SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))); //if (!tab.Save(Get_Trx())) //{ // Rollback(); // return "Encryption=" + false; //} string p_NewPassword = SecureEngine.Encrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()); String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID(); if (!string.IsNullOrEmpty(p_NewPassword)) { sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword); } sql += " WHERE " + tableName + "_ID=" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]); int iRes = DB.ExecuteQuery(sql, null, Get_Trx()); if (iRes <= 0) { Rollback(); return("Encryption=" + false); } } else { Rollback(); return("After Encryption some values may exceed the value of column length. Please exceed column Length."); } } } } else { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i][column.GetColumnName()] != null && ds.Tables[0].Rows[i][column.GetColumnName()] != DBNull.Value && SecureEngine.IsEncrypted(ds.Tables[0].Rows[i][column.GetColumnName()].ToString())) { // MUser user = new MUser(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i][MTable.GetTableName(GetCtx(), column.GetAD_Table_ID())+"_ID"]), Get_Trx()); //PO tab = MTable.GetPO(GetCtx(), tableName, // Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]), Get_Trx()); //tab.Set_Value(column.GetColumnName(), (SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()))); //if (!tab.Save(Get_Trx())) //{ // Rollback(); // return "Encryption=" + false; //} string p_NewPassword = SecureEngine.Decrypt(ds.Tables[0].Rows[i][column.GetColumnName()].ToString()); String sql = "UPDATE " + tableName + " SET Updated=SYSDATE, UpdatedBy=" + GetAD_User_ID(); if (!string.IsNullOrEmpty(p_NewPassword)) { sql += ", " + column.GetColumnName() + "=" + GlobalVariable.TO_STRING(p_NewPassword); } sql += " WHERE " + tableName + "_ID =" + Util.GetValueOfInt(ds.Tables[0].Rows[i][tableName + "_ID"]); int iRes = DB.ExecuteQuery(sql, null, Get_Trx()); if (iRes <= 0) { Rollback(); return("Encryption=" + false); } } } } } //} } return("Encryption=" + column.IsEncrypted()); }
/// <summary> /// return is credential provide by user is right or not /// </summary> /// <param name="model">login model class</param> /// <param name="roles">out roles , has role list of user</param> /// <param name="ctx" ></param> /// <returns>true if athenicated</returns> public static bool Login(LoginModel model, out List <KeyNamePair> roles) { // loginModel = null; //bool isMatch = false; roles = null; SecureEngine.Encrypt("t"); //Initialize // Cannot use encrypted password //if ()) //{ // //log.warning("Cannot use Encrypted Password"); // return false; //} // Authentification bool authenticated = false; bool isLDAP = false; MSystem system = MSystem.Get(new Ctx()); string output = ""; if (system != null && system.IsLDAP()) { authenticated = system.IsLDAP(model.Login1Model.UserValue, model.Login1Model.Password, out output); isLDAP = true; } //Save Failed Login Count and Password validty in cache GetSysConfigForlogin(); int fCount = Util.GetValueOfInt(cache[Common.Failed_Login_Count_Key]); int passwordValidUpto = Util.GetValueOfInt(cache[Common.Password_Valid_Upto_Key]); SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@username", model.Login1Model.UserValue); DataSet dsUserInfo = DB.ExecuteDataset("SELECT AD_User_ID, Value, Password,IsLoginUser,FailedLoginCount, IsOnlyLDAP FROM AD_User WHERE Value=@username", param); if (dsUserInfo != null && dsUserInfo.Tables[0].Rows.Count > 0) { // skipped Login user check for SuperUser (100) if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue) && !dsUserInfo.Tables[0].Rows[0]["IsLoginUser"].ToString().Equals("Y")) { throw new Exception("NotLoginUser"); } // output length will be greater than 0 if there is any error while ldap auth. //output check is applied to becuase after first login, when user redriect to home page, this functioexecutes again and password is null on that time. // so ldap reject auth , but user is actually authenticated. so to avoid error, this check is used. if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue) && dsUserInfo.Tables[0].Rows[0]["IsOnlyLDAP"].ToString().Equals("Y") && isLDAP && !authenticated) { throw new Exception(output); } } else { throw new Exception("UserNotFound"); } //if authenticated by LDAP or password is null(Means request from home page) if (!authenticated && model.Login1Model.Password != null) { string sqlEnc = "SELECT isencrypted FROM ad_column WHERE ad_table_id=(SELECT ad_table_id FROM ad_table WHERE tablename='AD_User') AND columnname='Password'"; char isEncrypted = Convert.ToChar(DB.ExecuteScalar(sqlEnc)); string originalpwd = model.Login1Model.Password; if (isEncrypted == 'Y' && model.Login1Model.Password != null) { model.Login1Model.Password = SecureEngine.Encrypt(model.Login1Model.Password); } // DataSet dsUserInfo = DB.ExecuteDataset("SELECT AD_User_ID, Value, Password,IsLoginUser,FailedLoginCount FROM AD_User WHERE Value=@username", param); if (dsUserInfo != null && dsUserInfo.Tables[0].Rows.Count > 0) { //if username or password is not matching if ((!dsUserInfo.Tables[0].Rows[0]["Value"].Equals(model.Login1Model.UserValue) || !dsUserInfo.Tables[0].Rows[0]["Password"].Equals(model.Login1Model.Password)) || (originalpwd != null && SecureEngine.IsEncrypted(originalpwd))) { //if current user is Not superuser, then increase failed login count if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue)) { param[0] = new SqlParameter("@username", model.Login1Model.UserValue); int count = DB.ExecuteQuery("UPDATE AD_User Set FAILEDLOGINCOUNT=FAILEDLOGINCOUNT+1 WHERE Value=@username ", param); if (fCount > 0 && fCount <= Util.GetValueOfInt(dsUserInfo.Tables[0].Rows[0]["FailedLoginCount"]) + 1) { throw new Exception("MaxFailedLoginAttempts"); } } throw new Exception("UserPwdError"); } else// if username and password matched, then check if account is locked or not { if (fCount > 0 && fCount <= Util.GetValueOfInt(dsUserInfo.Tables[0].Rows[0]["FailedLoginCount"])) { throw new Exception("MaxFailedLoginAttempts"); } } } } IDataReader dr = GetRoles(model.Login1Model.UserValue, authenticated, isLDAP); if (!dr.Read()) // no record found, then return msaage that role not found. { dr.Close(); throw new Exception("RoleNotDefined"); } // if user logged in successfully, then set failed login count to 0 DB.ExecuteQuery("UPDATE AD_User SET FailedLoginCount=0 WHERE Value=@username", param); int AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); //User Id if (!cache["SuperUserVal"].Equals(model.Login1Model.UserValue)) { String Token2FAKey = Util.GetValueOfString(dr["TokenKey2FA"]); bool enable2FA = Util.GetValueOfString(dr["Is2FAEnabled"]) == "Y"; if (enable2FA) { model.Login1Model.QRFirstTime = false; TwoFactorAuthenticator tfa = new TwoFactorAuthenticator(); SetupCode setupInfo = null; string userSKey = Util.GetValueOfString(dr["Value"]); int ADUserID = Util.GetValueOfInt(dr["AD_User_ID"]); // if token key don't exist for user, then create new if (Token2FAKey.Trim() == "") { model.Login1Model.QRFirstTime = true; Token2FAKey = userSKey; // get Random Number model.Login1Model.TokenKey2FA = GetRndNum(); // create Token key based on Value, UserID and Random Number Token2FAKey = userSKey + ADUserID.ToString() + model.Login1Model.TokenKey2FA; } else { // Decrypt token key saved in database string decKey = SecureEngine.Decrypt(Token2FAKey); Token2FAKey = userSKey + ADUserID.ToString() + decKey; } string url = Util.GetValueOfString(HttpContext.Current.Request.Url.AbsoluteUri).Replace("VIS/Account/JsonLogin", "").Replace("https://", "").Replace("http://", ""); setupInfo = tfa.GenerateSetupCode("VA ", url + " " + userSKey, Token2FAKey, 150, 150); model.Login1Model.QRCodeURL = setupInfo.QrCodeSetupImageUrl; } model.Login1Model.Is2FAEnabled = enable2FA; } if (!authenticated) { DateTime?pwdExpireDate = Util.GetValueOfDateTime(dr["PasswordExpireOn"]); if (pwdExpireDate == null || (passwordValidUpto > 0 && (DateTime.Compare(DateTime.Now, Convert.ToDateTime(pwdExpireDate)) > 0))) { model.Login1Model.ResetPwd = true; //if (SecureEngine.IsEncrypted(model.Login1Model.Password)) // model.Login1Model.Password = SecureEngine.Decrypt(model.Login1Model.Password); } } roles = new List <KeyNamePair>(); //roles List <int> usersRoles = new List <int>(); string username = ""; do // read all roles { AD_User_ID = Util.GetValueOfInt(dr[0].ToString()); int AD_Role_ID = Util.GetValueOfInt(dr[1].ToString()); String Name = dr[2].ToString(); KeyNamePair p = new KeyNamePair(AD_Role_ID, Name); username = Util.GetValueOfString(dr["username"].ToString()); roles.Add(p); usersRoles.Add(AD_Role_ID); }while (dr.Read()); dr.Close(); model.Login1Model.AD_User_ID = AD_User_ID; model.Login1Model.DisplayName = username; IDataReader drLogin = null; if (model.Login2Model == null) { try { //* Change sub query into ineer join */ drLogin = DB.ExecuteReader(" SELECT l.AD_Role_ID," + " (SELECT r.Name FROM AD_ROLE r WHERE r.AD_Role_ID=l.AD_ROLE_ID) as RoleName," + " l.AD_Org_ID," + " (SELECT o.Name FROM AD_Org o WHERE o.AD_Org_ID=l.AD_Org_ID) as OrgName," + " l.AD_Client_ID," + " (SELECT c.Name FROM AD_Client c WHERE c.AD_Client_ID=l.AD_Client_ID) as ClientName," + " l.M_Warehouse_ID," + " (SELECT m.Name FROM M_Warehouse m WHERE m.M_Warehouse_Id = l.M_Warehouse_ID) as WarehouseName" + " FROM AD_LoginSetting l WHERE l.IsActive = 'Y' AND l.AD_User_ID=" + AD_User_ID); if (drLogin.Read()) { bool deleteRecord = false; //Delete Login Setting if (deleteRecord) { DB.ExecuteQuery("DELETE FROM AD_LoginSetting WHERE AD_User_ID = " + AD_User_ID); } else { model.Login2Model = new Login2Model(); model.Login2Model.Role = drLogin[0].ToString(); model.Login2Model.RoleName = drLogin[1].ToString(); model.Login2Model.Org = drLogin[2].ToString(); model.Login2Model.OrgName = drLogin[3].ToString(); model.Login2Model.Client = drLogin[4].ToString(); model.Login2Model.ClientName = drLogin[5].ToString(); model.Login2Model.Warehouse = drLogin[6].ToString(); model.Login2Model.WarehouseName = drLogin[7].ToString(); model.Login2Model.Date = System.DateTime.Now.Date; } } drLogin.Close(); } catch { if (drLogin != null) { drLogin.Close(); } } } return(true); }