public static void OnTimer(Object sender)
{
// 12/22/2007 Paul. In case the timer takes a long time, only allow one timer event to be processed.
if (!bInsideTimer)
{
bInsideTimer = true;
HttpApplication global = sender as HttpApplication;
try
{
// 12/30/2007 Paul. Workflow events always get processed.
WorkflowUtils.Process(global.Application);
DbProviderFactory dbf = DbProviderFactories.GetFactory(global.Application);
using (DataTable dt = new DataTable())
{
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwSCHEDULERS_Run" + ControlChars.CrLf
+ " order by NEXT_RUN " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 01/01/2008 Paul. The scheduler query should always be very fast.
// In the off chance that there is a problem, abort after 15 seconds.
cmd.CommandTimeout = 15;
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
da.Fill(dt);
}
}
}
// 01/13/2008 Paul. Loop outside the connection so that only one connection will be used.
foreach (DataRow row in dt.Rows)
{
Guid gID = Sql.ToGuid(row["ID"]);
string sJOB = Sql.ToString(row["JOB"]);
DateTime dtNEXT_RUN = Sql.ToDateTime(row["NEXT_RUN"]);
try
{
switch (sJOB)
{
case "function::BackupDatabase":
{
// 01/28/2008 Paul. Cannot perform a backup or restore operation within a transaction. BACKUP DATABASE is terminating abnormally.
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
try
{
string sFILENAME = String.Empty;
string sTYPE = "FULL";
//SqlProcs.spSqlBackupDatabase(ref sNAME, "FULL", trn);
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spSqlBackupDatabase";
IDbDataParameter parFILENAME = Sql.AddParameter(cmd, "@FILENAME", sFILENAME, 255);
IDbDataParameter parTYPE = Sql.AddParameter(cmd, "@TYPE", sTYPE, 20);
parFILENAME.Direction = ParameterDirection.InputOutput;
cmd.ExecuteNonQuery();
sFILENAME = Sql.ToString(parFILENAME.Value);
}
SplendidError.SystemMessage(global.Application, "Information", new StackTrace(true).GetFrame(0), "Database backup complete " + sFILENAME);
}
catch (Exception ex)
{
SplendidError.SystemMessage(global.Application, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
}
}
break;
}
case "function::BackupTransactionLog":
{
// 01/28/2008 Paul. Cannot perform a backup or restore operation within a transaction. BACKUP DATABASE is terminating abnormally.
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
try
{
string sFILENAME = String.Empty;
string sTYPE = "LOG";
//SqlProcs.spSqlBackupDatabase(ref sNAME, "LOG", trn);
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spSqlBackupDatabase";
IDbDataParameter parFILENAME = Sql.AddParameter(cmd, "@FILENAME", sFILENAME, 255);
IDbDataParameter parTYPE = Sql.AddParameter(cmd, "@TYPE", sTYPE, 20);
parFILENAME.Direction = ParameterDirection.InputOutput;
cmd.ExecuteNonQuery();
sFILENAME = Sql.ToString(parFILENAME.Value);
}
SplendidError.SystemMessage(global.Application, "Information", new StackTrace(true).GetFrame(0), "Transaction Log backup complete " + sFILENAME);
}
catch (Exception ex)
{
SplendidError.SystemMessage(global.Application, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
}
}
break;
}
case "function::runMassEmailCampaign":
{
// 12/30/2007 Paul. Update the last run date before running so that the date marks the start of the run.
EmailUtils.SendQueued(global.Application, Guid.Empty, Guid.Empty, false);
break;
}
case "function::pruneDatabase":
{
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
SqlProcs.spSqlPruneDatabase(trn);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemMessage(global.Application, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
}
}
}
break;
}
case "function::pollMonitoredInboxes":
{
EmailUtils.CheckMonitored(global.Application, Guid.Empty);
break;
}
case "function::pollMonitoredInboxesForBouncedCampaignEmails":
{
EmailUtils.CheckBounced(global.Application, Guid.Empty);
break;
}
case "function::CheckVersion":
{
DataTable dtVersions = Utils.CheckVersion(global.Application);
DataView vwVersions = dtVersions.DefaultView;
vwVersions.RowFilter = "New = '1'";
if (vwVersions.Count > 0)
{
global.Application["available_version"] = Sql.ToString(vwVersions[0]["Build"]);
global.Application["available_version_description"] = Sql.ToString(vwVersions[0]["Description"]);
}
break;
}
}
}
finally
{
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
// 01/12/2008 Paul. Make sure the Last Run value is updated after the operation.
SqlProcs.spSCHEDULERS_UpdateLastRun(gID, dtNEXT_RUN, trn);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemMessage(global.Application, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
}
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemMessage(global.Application, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
}
finally
{
bInsideTimer = false;
}
}
}
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()));
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Assembly asm = Assembly.GetExecutingAssembly();
sBuildNumber = asm.GetName().Version.ToString();
// 01/20/2006 Paul. Expire immediately.
Response.ExpiresAbsolute = new DateTime(1980, 1, 1, 0, 0, 0, 0);
try
{
// 11/20/2005 Paul. ASP.NET 2.0 has a namespace conflict, so we need the full name for the SplendidCRM factory.
SplendidCRM.DbProviderFactory dbf = SplendidCRM.DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
}
}
catch (Exception ex)
{
//SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
// 06/21/2007 Paul. Display inner exception if exists.
if (ex.InnerException != null)
{
Response.Write(ex.InnerException.Message + "<br>");
}
Response.Write(ex.Message + "<br>");
}
try
{
// 08/17/2006 Paul. A customer reported a problem with a view missing columns.
// Provide a way to recompile the views.
if (Request.QueryString["Recompile"] == "1" || Request.QueryString["Reload"] == "1" || Sql.IsEmptyString(Application["imageURL"]))
{
// 12/20/2005 Paul. Require admin rights to reload.
if (SplendidCRM.Security.IS_ADMIN)
{
if (Request.QueryString["Recompile"] == "1")
{
Utils.RefreshAllViews();
}
SplendidInit.InitApp();
// 11/17/2007 Paul. New function to determine if user is authenticated.
if (Security.IsAuthenticated())
{
SplendidInit.LoadUserPreferences(Security.USER_ID, Sql.ToString(Session["USER_SETTINGS/THEME"]), Sql.ToString(Session["USER_SETTINGS/CULTURE"]));
}
}
else
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), "You must be an administrator to reload the application.");
Response.Write("You must be an administrator to reload the application." + "<br>");
}
}
}
catch (Exception ex)
{
//SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
Response.Write(ex.Message + "<br>");
}
Page.DataBind();
}
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 XmlDocument InitUserPreferences(string sUSER_PREFERENCES)
{
XmlDocument xml = null;
try
{
xml = new XmlDocument();
if (!sUSER_PREFERENCES.StartsWith("<?xml "))
{
sUSER_PREFERENCES = XmlUtil.ConvertFromPHP(sUSER_PREFERENCES);
}
xml.LoadXml(sUSER_PREFERENCES);
HttpApplicationState Application = HttpContext.Current.Application;
string sCulture = L10N.NormalizeCulture(XmlUtil.SelectSingleNode(xml, "culture"));
string sTheme = XmlUtil.SelectSingleNode(xml, "theme");
string sDateFormat = XmlUtil.SelectSingleNode(xml, "dateformat");
string sTimeFormat = XmlUtil.SelectSingleNode(xml, "timeformat");
string sTimeZone = XmlUtil.SelectSingleNode(xml, "timezone");
string sCurrencyID = XmlUtil.SelectSingleNode(xml, "currency_id");
if (Sql.IsEmptyString(sCulture))
{
XmlUtil.SetSingleNode(xml, "culture", SplendidDefaults.Culture());
}
if (Sql.IsEmptyString(sTheme))
{
XmlUtil.SetSingleNode(xml, "theme", SplendidDefaults.Theme());
}
if (Sql.IsEmptyString(sDateFormat))
{
XmlUtil.SetSingleNode(xml, "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))
{
XmlUtil.SetSingleNode(xml, "dateformat", SplendidDefaults.DateFormat(sDateFormat));
}
if (Sql.IsEmptyString(sTimeFormat))
{
XmlUtil.SetSingleNode(xml, "timeformat", SplendidDefaults.TimeFormat());
}
if (Sql.IsEmptyString(sCurrencyID))
{
XmlUtil.SetSingleNode(xml, "currency_id", SplendidDefaults.CurrencyID());
}
// 09/01/2006 Paul. Only use timez if provided. Otherwise we will default to GMT.
if (Sql.IsEmptyString(sTimeZone) && !Sql.IsEmptyString(XmlUtil.SelectSingleNode(xml, "timez")))
{
int nTimez = Sql.ToInteger(XmlUtil.SelectSingleNode(xml, "timez"));
sTimeZone = SplendidDefaults.TimeZone(nTimez);
XmlUtil.SetSingleNode(xml, "timezone", sTimeZone);
}
// 09/01/2006 Paul. Default TimeZone was not getting set properly.
if (Sql.IsEmptyString(sTimeZone))
{
sTimeZone = SplendidDefaults.TimeZone();
XmlUtil.SetSingleNode(xml, "timezone", sTimeZone);
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
}
return(xml);
}
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);
}
}
