public static void Import(XmlDocument xml, ArrayList arrTables, bool bTruncate) { HttpResponse Response = HttpContext.Current.Response; // 12/16/2005 Paul. First create a hash table to convert tab name to a uppercase table name. Hashtable hashTables = new Hashtable(); XmlNodeList nlTables = xml.DocumentElement.ChildNodes; foreach (XmlNode node in nlTables) { if (!hashTables.ContainsKey(node.Name.ToUpper())) { hashTables.Add(node.Name.ToUpper(), node.Name); } } ArrayList lstReservedTables = new ArrayList(); lstReservedTables.Add("CONFIG"); lstReservedTables.Add("DETAILVIEWS"); lstReservedTables.Add("DETAILVIEWS_FIELDS"); lstReservedTables.Add("DETAILVIEWS_RELATIONSHIPS"); lstReservedTables.Add("EDITVIEWS"); lstReservedTables.Add("EDITVIEWS_FIELDS"); lstReservedTables.Add("GRIDVIEWS"); lstReservedTables.Add("GRIDVIEWS_COLUMNS"); lstReservedTables.Add("LANGUAGES"); lstReservedTables.Add("MODULES"); lstReservedTables.Add("SHORTCUTS"); lstReservedTables.Add("TERMINOLOGY"); lstReservedTables.Add("TIMEZONES"); StringBuilder sbErrors = new StringBuilder(); DbProviderFactory dbf = DbProviderFactories.GetFactory(); if (arrTables == null) { arrTables = new ArrayList(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "select * from vwSqlTableDependencies order by 2, 1"; using (DbDataAdapter da = dbf.CreateDataAdapter()) { ((IDbDataAdapter)da).SelectCommand = cmd; using (DataTable dt = new DataTable()) { da.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = dt.Rows[i]; arrTables.Add(row["name"].ToString()); } } } if (bTruncate) { cmd.CommandText = "select * from vwSqlTableDependencies order by 2 desc, 1 desc"; using (DbDataAdapter da = dbf.CreateDataAdapter()) { ((IDbDataAdapter)da).SelectCommand = cmd; using (DataTable dt = new DataTable()) { da.Fill(dt); for (int i = 0; i < dt.Rows.Count && Response.IsClientConnected; i++) { DataRow row = dt.Rows[i]; string sTABLE_NAME = row["name"].ToString().ToUpper(); // 12/18/2005 Paul. Some tables are reserved and should not be truncated or imported. if (lstReservedTables.Contains(sTABLE_NAME)) { continue; } // 12/18/2005 Paul. Only truncate tables that are being imported. if (hashTables.ContainsKey(sTABLE_NAME)) { try { if (sTABLE_NAME == "USERS") { // 12/17/2005 Paul. Don't delete the existing user, otherwise it will cause a login problem in the future. cmd.CommandText = "delete from USERS where ID != @ID"; Sql.AddParameter(cmd, "@ID", Security.USER_ID); } else { cmd.CommandText = "delete from " + sTABLE_NAME; } cmd.ExecuteNonQuery(); Response.Write(" "); // Write a singe byte to keep the connection open. } catch (Exception ex) { LogError(ref sbErrors, Sql.ExpandParameters(cmd), ex.Message); } } } } } } } } } for (int i = 0; i < arrTables.Count && Response.IsClientConnected; i++) { string sTABLE_NAME = arrTables[i].ToString().ToUpper(); // 12/18/2005 Paul. Some tables are reserved and should not be truncated or imported. if (lstReservedTables.Contains(sTABLE_NAME)) { continue; } if (hashTables.ContainsKey(sTABLE_NAME)) { string sXML_TABLE_NAME = hashTables[sTABLE_NAME].ToString(); XmlNodeList nlRows = xml.DocumentElement.SelectNodes(sXML_TABLE_NAME); if (nlRows.Count > 0) { SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Import Database Table: " + sTABLE_NAME); // 12/17/2005 Paul. Use a new connection for each table import so that connection state will be reset. // My main concern is that the identity_insert gets reset. using (IDbConnection con = dbf.CreateConnection()) { con.Open(); try { if (Sql.IsSQLServer(con)) { // 12/17/2005 Paul. In SQL Server, turn on identity_insert. string sIDENTITY_NAME = String.Empty; switch (sIDENTITY_NAME) { case "BUGS": sIDENTITY_NAME = "BUGS"; break; case "CASES": sIDENTITY_NAME = "CASES"; break; case "CAMPAIGNS": sIDENTITY_NAME = "CAMPAIGNS"; break; case "PROSPECTS": sIDENTITY_NAME = "PROSPECTS"; break; } if (!Sql.IsEmptyString(sIDENTITY_NAME)) { IDbCommand cmdIdentity = con.CreateCommand(); cmdIdentity.CommandText = "set identity_insert " + sIDENTITY_NAME + " on"; cmdIdentity.ExecuteNonQuery(); } } else if (Sql.IsOracle(con)) { // 12/17/2005 Paul. In Oracle, disable sequence triggers. string sTRIGGER_NAME = String.Empty; switch (sTABLE_NAME) { case "BUGS": sTRIGGER_NAME = "TR_S_BUGS_BUG_NUMBER"; break; case "CASES": sTRIGGER_NAME = "TR_S_CASES_CASE_NUMBER"; break; case "CAMPAIGNS": sTRIGGER_NAME = "TR_S_CAMPAIGNS_TRACKER_KEY"; break; case "PROSPECTS": sTRIGGER_NAME = "TR_S_PROSPECTS_TRACKER_KEY"; break; } if (!Sql.IsEmptyString(sTRIGGER_NAME)) { IDbCommand cmdTrigger = con.CreateCommand(); cmdTrigger.CommandText = "alter trigger " + sTRIGGER_NAME + " disable"; cmdTrigger.ExecuteNonQuery(); } } int nTableErrors = 0; IDbCommand cmdImport = Sql.CreateInsertParameters(con, sTABLE_NAME); foreach (XmlNode node in nlRows) { if (!Response.IsClientConnected) { break; } foreach (IDataParameter par in cmdImport.Parameters) { par.Value = DBNull.Value; } for (int j = 0; j < node.ChildNodes.Count; j++) { // 12/18/2005 Paul. A short-sighted programmer at SugarCRM created GUIDs with invalid characters. // We need to convert them to valid GUIDs. string sText = node.ChildNodes[j].InnerText; // 08/20/2006 Paul. Dynamically attempt to fix invalid GUIDs. It really only works for the ones defined below. string sName = node.ChildNodes[j].Name.ToUpper(); if (sName == "ID" || sName.EndsWith("_ID")) { if (sText.Length < 36) { sText = "00000000-0000-0000-0000-000000000000".Substring(0, 36 - sText.Length) + sText; } } switch (sText) { case "00000000-0000-0000-0000-000000jim_id": sText = "00000000-0000-0000-0001-000000000000"; break; case "00000000-0000-0000-0000-000000max_id": sText = "00000000-0000-0000-0002-000000000000"; break; case "00000000-0000-0000-0000-00000will_id": sText = "00000000-0000-0000-0003-000000000000"; break; case "00000000-0000-0000-0000-0000chris_id": sText = "00000000-0000-0000-0004-000000000000"; break; case "00000000-0000-0000-0000-0000sally_id": sText = "00000000-0000-0000-0005-000000000000"; break; case "00000000-0000-0000-0000-0000sarah_id": sText = "00000000-0000-0000-0006-000000000000"; break; } Sql.SetParameter(cmdImport, node.ChildNodes[j].Name, sText); } // 12/18/2005 Paul. ID can never be NULL. SugarCRM does not use an ID in the CONFIG or USERS_FEEDS table. IDbDataParameter parID = Sql.FindParameter(cmdImport, "@ID"); if (parID != null) { // 12/18/2005 Paul. GUIDs from SugarCRM may not be 36 characters. string sID = Sql.ToString(parID.Value); if (parID.Value != DBNull.Value) { if (sID.Length < 36) { // 07/31/2006 Paul. Stop using VisualBasic library to increase compatibility with Mono. parID.Value = "00000000-0000-0000-0000-000000000000".Substring(0, 36 - sID.Length) + sID; } } if (Sql.IsEmptyGuid(parID.Value)) { if (parID.DbType == DbType.Guid) { parID.Value = Guid.NewGuid(); } else { parID.Value = Guid.NewGuid().ToString(); } } } // 12/18/2005 Paul. DATE_ENTERED can never be NULL. SugarCRM does not use DATE_ENTERED in a number of tables. IDbDataParameter parDATE_ENTERED = Sql.FindParameter(cmdImport, "@DATE_ENTERED"); if (parDATE_ENTERED != null) { if (parDATE_ENTERED.Value == DBNull.Value) { parDATE_ENTERED.Value = DateTime.Now; } } // 12/18/2005 Paul. DATE_MODIFIED can never be NULL. SugarCRM does not use DATE_MODIFIED in a number of tables. IDbDataParameter parDATE_MODIFIED = Sql.FindParameter(cmdImport, "@DATE_MODIFIED"); if (parDATE_MODIFIED != null) { if (parDATE_MODIFIED.Value == DBNull.Value) { parDATE_MODIFIED.Value = DateTime.Now; } } try { cmdImport.ExecuteNonQuery(); Response.Write(" "); } catch (Exception ex) { LogError(ref sbErrors, Sql.ExpandParameters(cmdImport), ex.Message); // 12/17/2005 Paul. If there is an error, stop importing from this table. // 12/18/2005 Paul. I'd like to see the first 100 errors. nTableErrors++; if (nTableErrors > 100) { break; } } } } catch (Exception ex) { LogError(ref sbErrors, sTABLE_NAME, ex.Message); } finally { try { if (Sql.IsSQLServer(con)) { // 12/17/2005 Paul. In SQL Server, turn off identity_insert. string sIDENTITY_NAME = String.Empty; switch (sIDENTITY_NAME) { case "BUGS": sIDENTITY_NAME = "BUGS"; break; case "CASES": sIDENTITY_NAME = "CASES"; break; case "CAMPAIGNS": sIDENTITY_NAME = "CAMPAIGNS"; break; case "PROSPECTS": sIDENTITY_NAME = "PROSPECTS"; break; } if (!Sql.IsEmptyString(sIDENTITY_NAME)) { IDbCommand cmdIdentity = con.CreateCommand(); cmdIdentity.CommandText = "set identity_insert " + sIDENTITY_NAME + " off"; cmdIdentity.ExecuteNonQuery(); } } else if (Sql.IsOracle(con)) { // 12/17/2005 Paul. In Oracle, enable sequence triggers. string sTRIGGER_NAME = String.Empty; switch (sTABLE_NAME) { case "BUGS": sTRIGGER_NAME = "TR_S_BUGS_BUG_NUMBER"; break; case "CASES": sTRIGGER_NAME = "TR_S_CASES_CASE_NUMBER"; break; case "CAMPAIGNS": sTRIGGER_NAME = "TR_S_CAMPAIGNS_TRACKER_KEY"; break; case "PROSPECTS": sTRIGGER_NAME = "TR_S_PROSPECTS_TRACKER_KEY"; break; } if (!Sql.IsEmptyString(sTRIGGER_NAME)) { IDbCommand cmdTrigger = con.CreateCommand(); cmdTrigger.CommandText = "alter trigger " + sTRIGGER_NAME + " enable"; cmdTrigger.ExecuteNonQuery(); } } } catch (Exception ex) { LogError(ref sbErrors, sTABLE_NAME, ex.Message); } } } Response.Write(" "); // Write a singe byte to keep the connection open. } } } // 12/18/2005 Paul. Reserved tables will still be imported, but we use the associated spXXX_Update procedure. for (int i = 0; i < arrTables.Count && Response.IsClientConnected; i++) { string sTABLE_NAME = arrTables[i].ToString().ToUpper(); if (hashTables.ContainsKey(sTABLE_NAME) && lstReservedTables.Contains(sTABLE_NAME)) { string sXML_TABLE_NAME = hashTables[sTABLE_NAME].ToString(); XmlNodeList nlRows = xml.DocumentElement.SelectNodes(sXML_TABLE_NAME); if (nlRows.Count > 0) { SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Import Database Table: " + sTABLE_NAME); // 12/17/2005 Paul. Use a new connection for each table import so that connection state will be reset. // My main concern is that the identity_insert gets reset. using (IDbConnection con = dbf.CreateConnection()) { con.Open(); try { int nTableErrors = 0; IDbCommand cmdImport = SqlProcs.Factory(con, "sp" + sTABLE_NAME + "_Update"); foreach (XmlNode node in nlRows) { if (!Response.IsClientConnected) { break; } foreach (IDataParameter par in cmdImport.Parameters) { par.Value = DBNull.Value; } for (int j = 0; j < node.ChildNodes.Count; j++) { string sText = node.ChildNodes[j].InnerText; Sql.SetParameter(cmdImport, node.ChildNodes[j].Name, sText); } // 12/18/2005 Paul. ID can never be NULL. SugarCRM does not use an ID in the CONFIG or USERS_FEEDS table. IDbDataParameter parID = Sql.FindParameter(cmdImport, "@ID"); if (parID != null) { // 12/18/2005 Paul. GUIDs from SugarCRM may not be 36 characters. string sID = Sql.ToString(parID.Value); if (parID.Value != DBNull.Value) { if (sID.Length < 36) { // 07/31/2006 Paul. Stop using VisualBasic library to increase compatibility with Mono. parID.Value = "00000000-0000-0000-0000-000000000000".Substring(0, 36 - sID.Length) + sID; } } } try { cmdImport.ExecuteNonQuery(); Response.Write(" "); } catch (Exception ex) { LogError(ref sbErrors, Sql.ExpandParameters(cmdImport), ex.Message); // 12/17/2005 Paul. If there is an error, stop importing from this table. // 12/18/2005 Paul. I'd like to see the first 100 errors. nTableErrors++; if (nTableErrors > 100) { break; } } } } catch (Exception ex) { LogError(ref sbErrors, sTABLE_NAME, ex.Message); } } Response.Write(" "); // Write a singe byte to keep the connection open. } } } if (sbErrors.Length > 0) { throw(new Exception(sbErrors.ToString())); } }
public string BuildQuery(string sCondition, string sField, bool bTextField) { StringBuilder sbSqlQuery = new StringBuilder(); int nNotFlag = 0; bool bOrFlag = false; int nQueryLine = 0; int nLessFlag = 0; bool bEqualFlag = false; sbSqlQuery.Append(sCondition); sbSqlQuery.Append("("); bool bIsOracle = false; bool bIsDB2 = false; bool bIsMySQL = false; DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { using (IDbCommand cmd = con.CreateCommand()) { bIsOracle = Sql.IsOracle(cmd); bIsDB2 = Sql.IsDB2(cmd); bIsMySQL = Sql.IsMySQL(cmd); } } // 07/16/2006 Paul. Now that we know the database platform, only use the upper clause if Oracle or DB2. if (bIsOracle || bIsDB2) { //Can't do an upper() on NTEXT. SQL Server complains. // 01/16/2008 Paul. We don't use the bTextField anymore, and I could not find any places where we searched an NTEXT field anyway. //if ( !bTextField ) sField = "upper(" + sField + ")"; } foreach (string sThisToken in m_arrTokens) { if (m_bFeatureMIMUS && sThisToken == "-") { nNotFlag = 1; } else if (m_bFeaturePLUS && sThisToken == "+") { nNotFlag = 2; } else if (m_bFeatureOR && (sThisToken == m_sTermOR || sThisToken == "OR")) { bOrFlag = true; } else if (m_bFeatureAND && (sThisToken == m_sTermAND || sThisToken == "AND")) { bOrFlag = false; } else if (m_bFeatureNOT && (sThisToken == m_sTermNOT || sThisToken == "NOT")) { nNotFlag = 1; } // 12/17/2007 Paul. Add support for <, <=, > and >=. else if (m_bFeatureLESS && sThisToken == "<") { nLessFlag = 1; } else if (m_bFeatureLESS && sThisToken == ">") { nLessFlag = -1; } // 12/17/2007 Paul. Add support for ! and !=. They are both the same. else if (m_bFeatureEXCL && sThisToken == "!") { nNotFlag = 1; } else if (m_bFeatureEQUAL && sThisToken == "=") { bEqualFlag = true; } else { if (sThisToken.Length > 0) { //Google ignores single digit and single letters unless + or - is used. if (!m_bFeatureSINGLE || nNotFlag > 0 || sThisToken.Length > 1 || !Char.IsLetterOrDigit(sThisToken[0])) { //Ignore quoted strings that contain nothing after the quote. if (sThisToken[0] != '\"' || sThisToken.Length > 1) { //Add spaces to the line to align the fields. if (nQueryLine > 0) { sbSqlQuery.Append(Strings.Space(sCondition.Length)); if (bOrFlag) { sbSqlQuery.Append(" or "); } else { sbSqlQuery.Append(" and "); } } else { sbSqlQuery.Append(" "); } if (nNotFlag == 1) { sbSqlQuery.Append("not "); } else { sbSqlQuery.Append(" "); } //Remove the double quote flag from a quoted string. string sToken = sThisToken; if (sToken[0] == '\"') { sToken = sThisToken.Substring(1); } // 12/17/2007 Paul. Wildcards will have a higher priority over greater than or less than. if (m_bFeatureSTAR && sToken.IndexOf('*') >= 0) { //Escape to prevent use of % as a wild-card. sToken = EscapeLike(sToken); // 07/16/2006 Paul. SQL Server, Oracle and DB2 all support the ESCAPE '\' clause. if (bIsMySQL) { sToken = sToken.Replace("\\", "\\\\"); } sToken = sToken.Replace("*", "%"); sbSqlQuery.Append(sField + " like '" + sToken + "'"); // 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause. if (bIsMySQL) { sbSqlQuery.Append(" escape '\\\\'" + ControlChars.CrLf); } else { sbSqlQuery.Append(" escape '\\'" + ControlChars.CrLf); } } else { if (nLessFlag == 0) { if (bEqualFlag) { // 12/17/2007 Paul. Single quotes need to be manually escaped. sToken = EscapeSql(sToken); sbSqlQuery.Append(sField + " = '" + sToken + "'"); } else { //Escape to prevent use of % as a wild-card. sToken = EscapeLike(sToken); // 07/16/2006 Paul. SQL Server, Oracle and DB2 all support the ESCAPE '\' clause. if (bIsMySQL) { sToken = sToken.Replace("\\", "\\\\"); } sbSqlQuery.Append(sField + " like '%" + sToken + "%'"); // 07/16/2006 Paul. MySQL requires that slashes be escaped, even in the escape clause. if (bIsMySQL) { sbSqlQuery.Append(" escape '\\\\'" + ControlChars.CrLf); } else { sbSqlQuery.Append(" escape '\\'" + ControlChars.CrLf); } } } else { // 12/17/2007 Paul. Single quotes need to be manually escaped. sToken = EscapeSql(sToken); if (nLessFlag == 1) { if (bEqualFlag) { sbSqlQuery.Append(sField + " <= '" + sToken + "'"); } else { sbSqlQuery.Append(sField + " < '" + sToken + "'"); } } else if (nLessFlag == -1) { if (bEqualFlag) { sbSqlQuery.Append(sField + " >= '" + sToken + "'"); } else { sbSqlQuery.Append(sField + " > '" + sToken + "'"); } } } } nQueryLine += 1; } } } nNotFlag = 0; bOrFlag = false; bEqualFlag = false; nLessFlag = 0; } } if (nQueryLine > 0) { sbSqlQuery.Append(Strings.Space(sCondition.Length) + ")" + ControlChars.CrLf); return(sbSqlQuery.ToString()); } return(String.Empty); }
public static bool LoginUser(string sUSER_NAME, string sPASSWORD, string sTHEME, string sLANGUAGE, string sUSER_DOMAIN, bool bIS_ADMIN) { HttpApplicationState Application = HttpContext.Current.Application; HttpSessionState Session = HttpContext.Current.Session; bool bValidUser = false; DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); string sSQL; // 03/22/2006 Paul. The user name should be case-insignificant. The password is case-significant. // 03/22/2006 Paul. DB2 does not like lower(USER_NAME) = lower(@USER_NAME). It returns the following error. // ERROR [42610] [IBM][DB2/NT] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610 // 05/23/2006 Paul. Use vwUSERS_Login so that USER_HASH can be removed from vwUSERS to prevent its use in reports. sSQL = "select ID " + ControlChars.CrLf + " , USER_NAME " + ControlChars.CrLf + " , FULL_NAME " + ControlChars.CrLf + " , IS_ADMIN " + ControlChars.CrLf + " , STATUS " + ControlChars.CrLf + " , PORTAL_ONLY " + ControlChars.CrLf + " from vwUSERS_Login " + ControlChars.CrLf + " where lower(USER_NAME) = @USER_NAME" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; /* #if DEBUG * if ( sUSER_NAME == "paulrony" && Sql.ToString(Application["SplendidProvider"]) == "MySql.Data" ) * sUSER_NAME = "admin"; #endif */ // 03/22/2006 Paul. Convert the name to lowercase here. Sql.AddParameter(cmd, "@USER_NAME", sUSER_NAME.ToLower()); // 11/19/2005 Paul. sUSER_DOMAIN is used to determine if NTLM is enabled. if (Sql.IsEmptyString(sUSER_DOMAIN)) { if (!Sql.IsEmptyString(sPASSWORD)) { string sUSER_HASH = Security.HashPassword(sPASSWORD); cmd.CommandText += " and USER_HASH = @USER_HASH" + ControlChars.CrLf; Sql.AddParameter(cmd, "@USER_HASH", sUSER_HASH); } else { // 11/19/2005 Paul. Handle the special case of the password stored as NULL or empty string. cmd.CommandText += " and (USER_HASH = '' or USER_HASH is null)" + ControlChars.CrLf; } } using (IDataReader rdr = cmd.ExecuteReader()) { string sApplicationPath = Sql.ToString(Application["rootURL"]); if (rdr.Read()) { // 11/19/2005 Paul. Clear all session values. Session.Clear(); Security.USER_ID = Sql.ToGuid(rdr["ID"]); Security.USER_NAME = Sql.ToString(rdr["USER_NAME"]); Security.FULL_NAME = Sql.ToString(rdr["FULL_NAME"]); Security.IS_ADMIN = Sql.ToBoolean(rdr["IS_ADMIN"]); Security.PORTAL_ONLY = Sql.ToBoolean(rdr["PORTAL_ONLY"]); Guid gUSER_ID = Sql.ToGuid(rdr["ID"]); // 08/08/2006 Paul. Don't supply the Language as it prevents the user value from being used. // This bug is a hold-over from the time we removed the Lauguage combo from the login screen. LoadUserPreferences(gUSER_ID, sTHEME, String.Empty); LoadUserACL(gUSER_ID); bValidUser = true; SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "User login."); } else if (Security.IsWindowsAuthentication()) { // 11/04/2005. If user does not exist, then create it, but only if NTLM is used. Guid gUSER_ID = Guid.Empty; SqlProcs.spUSERS_InsertNTLM(ref gUSER_ID, sUSER_DOMAIN, sUSER_NAME, bIS_ADMIN); // 11/19/2005 Paul. Clear all session values. Session.Clear(); Security.USER_ID = gUSER_ID; Security.USER_NAME = sUSER_NAME; Security.IS_ADMIN = bIS_ADMIN; Security.PORTAL_ONLY = false; // 11/21/2005 Paul. Load the preferences to initialize cuture, date, time and currency preferences. LoadUserPreferences(gUSER_ID, String.Empty, String.Empty); LoadUserACL(gUSER_ID); bValidUser = true; SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "User login."); } else { // 11/22/2005 Paul. Initialize preferences even if login fails so that the theme gets set to the default value. LoadUserPreferences(Guid.Empty, String.Empty, String.Empty); } } } } return(bValidUser); // throw(new Exception("Users.ERR_INVALID_PASSWORD")); }
public static void InitTerminology() { try { HttpApplicationState Application = HttpContext.Current.Application; DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); string sSQL; sSQL = "select NAME " + ControlChars.CrLf + " , LANG " + ControlChars.CrLf + " , MODULE_NAME " + ControlChars.CrLf + " , DISPLAY_NAME " + ControlChars.CrLf + " from vwTERMINOLOGY " + ControlChars.CrLf + " where LIST_NAME is null" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { // 01/20/2006 Paul. Enable all languages when debugging. //#if DEBUG // sSQL += " and LANG = 'en-us'" + ControlChars.CrLf; //#endif cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { //Application[Sql.ToString(rdr["LANG"]) + "." + Sql.ToString(rdr["MODULE_NAME"]) + "." + Sql.ToString(rdr["NAME"])] = Sql.ToString(rdr["DISPLAY_NAME"]); string sLANG = Sql.ToString(rdr["LANG"]); string sMODULE_NAME = Sql.ToString(rdr["MODULE_NAME"]); string sNAME = Sql.ToString(rdr["NAME"]); string sDISPLAY_NAME = Sql.ToString(rdr["DISPLAY_NAME"]); L10N.SetTerm(sLANG, sMODULE_NAME, sNAME, sDISPLAY_NAME); } } } sSQL = "select NAME " + ControlChars.CrLf + " , LANG " + ControlChars.CrLf + " , MODULE_NAME " + ControlChars.CrLf + " , LIST_NAME " + ControlChars.CrLf + " , DISPLAY_NAME " + ControlChars.CrLf + " from vwTERMINOLOGY " + ControlChars.CrLf + " where LIST_NAME is not null" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { // 01/20/2006 Paul. Enable all languages when debugging. //#if DEBUG // sSQL += " and LANG = 'en-us'" + ControlChars.CrLf; //#endif cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { // 01/13/2006 Paul. Don't include MODULE_NAME when used with a list. // DropDownLists are populated without the module name in the list name. // 01/13/2006 Paul. We can remove the module, but not the dot. // Otherwise it breaks all other code that references a list term. //Application[Sql.ToString(rdr["LANG"]) + "." + sMODULE_NAME + "." + Sql.ToString(rdr["LIST_NAME"]) + "." + Sql.ToString(rdr["NAME"])] = Sql.ToString(rdr["DISPLAY_NAME"]); string sLANG = Sql.ToString(rdr["LANG"]); string sMODULE_NAME = Sql.ToString(rdr["MODULE_NAME"]); string sNAME = Sql.ToString(rdr["NAME"]); string sLIST_NAME = Sql.ToString(rdr["LIST_NAME"]); string sDISPLAY_NAME = Sql.ToString(rdr["DISPLAY_NAME"]); L10N.SetTerm(sLANG, sMODULE_NAME, sLIST_NAME, sNAME, sDISPLAY_NAME); } } } sSQL = "select ALIAS_NAME " + ControlChars.CrLf + " , ALIAS_MODULE_NAME " + ControlChars.CrLf + " , ALIAS_LIST_NAME " + ControlChars.CrLf + " , NAME " + ControlChars.CrLf + " , MODULE_NAME " + ControlChars.CrLf + " , LIST_NAME " + ControlChars.CrLf + " from vwTERMINOLOGY_ALIASES" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { string sALIAS_NAME = Sql.ToString(rdr["ALIAS_NAME"]); string sALIAS_MODULE_NAME = Sql.ToString(rdr["ALIAS_MODULE_NAME"]); string sALIAS_LIST_NAME = Sql.ToString(rdr["ALIAS_LIST_NAME"]); string sNAME = Sql.ToString(rdr["NAME"]); string sMODULE_NAME = Sql.ToString(rdr["MODULE_NAME"]); string sLIST_NAME = Sql.ToString(rdr["LIST_NAME"]); L10N.SetAlias(sALIAS_MODULE_NAME, sALIAS_LIST_NAME, sALIAS_NAME, sMODULE_NAME, sLIST_NAME, sNAME); } } } // 07/13/2006 Paul. The reporting module needs a quick way to translate a module name to a table name. sSQL = "select MODULE_NAME" + ControlChars.CrLf + " , TABLE_NAME " + ControlChars.CrLf + " from vwMODULES " + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { string sMODULE_NAME = Sql.ToString(rdr["MODULE_NAME"]); string sTABLE_NAME = Sql.ToString(rdr["TABLE_NAME"]); Application["Modules." + sMODULE_NAME + ".TableName"] = sTABLE_NAME; } } } } } catch (Exception ex) { SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message); //HttpContext.Current.Response.Write(ex.Message); } }
public static void LoadUserPreferences(Guid gID, string sTheme, string sCulture) { HttpApplicationState Application = HttpContext.Current.Application; HttpSessionState Session = HttpContext.Current.Session; string sApplicationPath = Sql.ToString(Application["rootURL"]); DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { string sSQL; sSQL = "select * " + ControlChars.CrLf + " from vwUSERS_Edit" + ControlChars.CrLf + " where ID = @ID " + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; Sql.AddParameter(cmd, "@ID", gID); con.Open(); using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow)) { if (rdr.Read()) { string sUSER_PREFERENCES = Sql.ToString(rdr["USER_PREFERENCES"]); if (!Sql.IsEmptyString(sUSER_PREFERENCES)) { XmlDocument xml = InitUserPreferences(sUSER_PREFERENCES); Session["USER_PREFERENCES"] = xml.OuterXml; // 11/19/2005 Paul. Not sure why the login screen has the language, but it would seem to allow overriding the default. if (Sql.IsEmptyString(sCulture)) { sCulture = XmlUtil.SelectSingleNode(xml, "culture").Replace("_", "-"); } // 11/22/2005 Paul. The theme can be overridden as well. if (Sql.IsEmptyString(sTheme)) { sTheme = XmlUtil.SelectSingleNode(xml, "theme").Replace("_", "-"); } Session["USER_SETTINGS/CULTURE"] = sCulture; Session["USER_SETTINGS/THEME"] = sTheme; Session["themeURL"] = sApplicationPath + "Themes/" + sTheme + "/"; Session["USER_SETTINGS/DATEFORMAT"] = XmlUtil.SelectSingleNode(xml, "dateformat"); Session["USER_SETTINGS/TIMEFORMAT"] = XmlUtil.SelectSingleNode(xml, "timeformat"); // 01/21/2006 Paul. It is useful to have quick access to email address. Session["USER_SETTINGS/MAIL_FROMNAME"] = XmlUtil.SelectSingleNode(xml, "mail_fromname"); Session["USER_SETTINGS/MAIL_FROMADDRESS"] = XmlUtil.SelectSingleNode(xml, "mail_fromaddress"); // 05/09/2006 Paul. Initialize the numeric separators. Session["USER_SETTINGS/GROUP_SEPARATOR"] = XmlUtil.SelectSingleNode(xml, "num_grp_sep"); Session["USER_SETTINGS/DECIMAL_SEPARATOR"] = XmlUtil.SelectSingleNode(xml, "dec_sep"); try { Session["USER_SETTINGS/TIMEZONE"] = Sql.ToGuid(XmlUtil.SelectSingleNode(xml, "timezone")).ToString(); } catch { SplendidError.SystemError(new StackTrace(true).GetFrame(0), "Invalid USER_SETTINGS/TIMEZONE: " + XmlUtil.SelectSingleNode(xml, "timezone")); } try { Session["USER_SETTINGS/CURRENCY"] = XmlUtil.SelectSingleNode(xml, "currency_id"); } catch { SplendidError.SystemError(new StackTrace(true).GetFrame(0), "Invalid USER_SETTINGS/CURRENCY: " + XmlUtil.SelectSingleNode(xml, "currency_id")); } DataView vwCurrencies = new DataView(SplendidCache.Currencies()); vwCurrencies.RowFilter = "ID = '" + XmlUtil.SelectSingleNode(xml, "currency_id") + "'"; if (vwCurrencies.Count > 0) { Session["USER_SETTINGS/CURRENCY_SYMBOL"] = Sql.ToString(vwCurrencies[0]["SYMBOL"]); } } } } } // 11/21/2005 Paul. New users may not have any settings, so we need to initialize the defaults. // It is best to do it here rather than wrap the variables in a function that would return the default if null. sCulture = Sql.ToString(Session["USER_SETTINGS/CULTURE"]); sTheme = Sql.ToString(Session["USER_SETTINGS/THEME"]); string sDateFormat = Sql.ToString(Session["USER_SETTINGS/DATEFORMAT"]); string sTimeFormat = Sql.ToString(Session["USER_SETTINGS/TIMEFORMAT"]); string sTimeZone = Sql.ToString(Session["USER_SETTINGS/TIMEZONE"]); string sCurrencyID = Sql.ToString(Session["USER_SETTINGS/CURRENCY"]); if (Sql.IsEmptyString(sCulture)) { Session["USER_SETTINGS/CULTURE"] = SplendidDefaults.Culture(); } if (Sql.IsEmptyString(sTheme)) { sTheme = SplendidDefaults.Theme(); Session["USER_SETTINGS/THEME"] = sTheme; Session["themeURL"] = sApplicationPath + "Themes/" + sTheme + "/"; } if (Sql.IsEmptyString(sDateFormat)) { Session["USER_SETTINGS/DATEFORMAT"] = SplendidDefaults.DateFormat(); } // 11/12/2005 Paul. "m" is not valid for .NET month formatting. Must use MM. // 11/12/2005 Paul. Require 4 digit year. Otherwise default date in Pipeline of 12/31/2100 would get converted to 12/31/00. if (SplendidDefaults.IsValidDateFormat(sDateFormat)) { Session["USER_SETTINGS/DATEFORMAT"] = SplendidDefaults.DateFormat(sDateFormat); } if (Sql.IsEmptyString(sTimeFormat)) { Session["USER_SETTINGS/TIMEFORMAT"] = SplendidDefaults.TimeFormat(); } if (Sql.IsEmptyString(sCurrencyID)) { Session["USER_SETTINGS/CURRENCY"] = SplendidDefaults.CurrencyID(); } if (Sql.IsEmptyString(sTimeZone)) { Session["USER_SETTINGS/TIMEZONE"] = SplendidDefaults.TimeZone(); } // 05/09/2006 Paul. Use defaults when necessary. string sGROUP_SEPARATOR = Sql.ToString(Session["USER_SETTINGS/GROUP_SEPARATOR"]); string sDECIMAL_SEPARATOR = Sql.ToString(Session["USER_SETTINGS/DECIMAL_SEPARATOR"]); if (Sql.IsEmptyString(sGROUP_SEPARATOR)) { Session["USER_SETTINGS/GROUP_SEPARATOR"] = SplendidDefaults.GroupSeparator(); } if (Sql.IsEmptyString(sDECIMAL_SEPARATOR)) { Session["USER_SETTINGS/DECIMAL_SEPARATOR"] = SplendidDefaults.DecimalSeparator(); } } }
public static void InitApp() { try { HttpApplicationState Application = HttpContext.Current.Application; if (Application.Count == 0) { SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Application start."); } else { SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), "Application restart."); } // 11/14/2005 Paul. Force the reload of the provider and connection strings. // Application.Remove("SplendidProvider"); // 11/28/2005 Paul. Use Clear() to clear all application variables. DataTable dtSystemErrors = Application["SystemErrors"] as DataTable; Application.Clear(); // 11/28/2005 Paul. Save and restore the system errors table. Application["SystemErrors"] = dtSystemErrors; InitAppURLs(); // 11/28/2005 Paul. Clear all cache variables as well. foreach (DictionaryEntry oKey in HttpContext.Current.Cache) { string sKey = oKey.Key.ToString(); HttpContext.Current.Cache.Remove(sKey); } // 06/03/2006 Paul. Clear the cached data that is stored in the Session object. if (HttpContext.Current.Session != null) { Hashtable hashSessionKeys = new Hashtable(); foreach (string sKey in HttpContext.Current.Session.Keys) { hashSessionKeys.Add(sKey, null); } // 06/03/2006 Paul. We can't remove a key when it is used in the enumerator. foreach (string sKey in hashSessionKeys.Keys) { if (sKey.StartsWith("vwSHORTCUTS_Menu_ByUser") || sKey.StartsWith("vwMODULES_TabMenu_ByUser")) { HttpContext.Current.Session.Remove(sKey); } } } DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { // 07/28/2006 Paul. Test the database connection and allow an early exit if failed. con.Open(); } // 01/12/2006 Paul. Separate out the terminology so that it can be called when importing a language pack. InitTerminology(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); string sSQL; sSQL = "select NAME " + ControlChars.CrLf + " , VALUE " + ControlChars.CrLf + " from vwCONFIG" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Application["CONFIG." + Sql.ToString(rdr["NAME"])] = Sql.ToString(rdr["VALUE"]); } } } sSQL = "select * " + ControlChars.CrLf + " from vwTIMEZONES" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { TimeZone oTimeZone = new TimeZone (Sql.ToGuid(rdr["ID"]) , Sql.ToString(rdr["NAME"]) , Sql.ToString(rdr["STANDARD_NAME"]) , Sql.ToString(rdr["STANDARD_ABBREVIATION"]) , Sql.ToString(rdr["DAYLIGHT_NAME"]) , Sql.ToString(rdr["DAYLIGHT_ABBREVIATION"]) , Sql.ToInteger(rdr["BIAS"]) , Sql.ToInteger(rdr["STANDARD_BIAS"]) , Sql.ToInteger(rdr["DAYLIGHT_BIAS"]) , Sql.ToInteger(rdr["STANDARD_YEAR"]) , Sql.ToInteger(rdr["STANDARD_MONTH"]) , Sql.ToInteger(rdr["STANDARD_WEEK"]) , Sql.ToInteger(rdr["STANDARD_DAYOFWEEK"]) , Sql.ToInteger(rdr["STANDARD_HOUR"]) , Sql.ToInteger(rdr["STANDARD_MINUTE"]) , Sql.ToInteger(rdr["DAYLIGHT_YEAR"]) , Sql.ToInteger(rdr["DAYLIGHT_MONTH"]) , Sql.ToInteger(rdr["DAYLIGHT_WEEK"]) , Sql.ToInteger(rdr["DAYLIGHT_DAYOFWEEK"]) , Sql.ToInteger(rdr["DAYLIGHT_HOUR"]) , Sql.ToInteger(rdr["DAYLIGHT_MINUTE"]) , Sql.ToBoolean(Application["CONFIG.GMT_Storage"]) ); Application["TIMEZONE." + oTimeZone.ID.ToString()] = oTimeZone; } } } sSQL = "select * " + ControlChars.CrLf + " from vwCURRENCIES" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Currency C10n = new Currency (Sql.ToGuid(rdr["ID"]) , Sql.ToString(rdr["NAME"]) , Sql.ToString(rdr["SYMBOL"]) , Sql.ToFloat(rdr["CONVERSION_RATE"]) ); Application["CURRENCY." + C10n.ID.ToString()] = C10n; } } } } } catch (Exception ex) { SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message); //HttpContext.Current.Response.Write(ex.Message); } }