public Invoice GetInvoiceByName(Guid gCURRENCY_ID, string sNAME)
{
Invoice item = new Invoice();
//try
{
if (Security.USER_ID == Guid.Empty)
{
throw(new Exception("Authentication required"));
}
SplendidCRM.DbProviderFactory dbf = SplendidCRM.DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
// 04/13/2007 Paul. Use LIKE clause so that the user can abbreviate Names.
sSQL = "select * " + ControlChars.CrLf
+ " from vwINVOICES" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "Invoices", "list");
Sql.AppendParameter(cmd, sNAME, Sql.SqlFilterMode.StartsWith, "NAME");
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
item.ID = Sql.ToGuid(rdr["ID"]);
item.NAME = Sql.ToString(rdr["NAME"]);
item.AMOUNT_DUE = Sql.ToDecimal(rdr["AMOUNT_DUE"]);
item.AMOUNT_DUE_USDOLLAR = Sql.ToDecimal(rdr["AMOUNT_DUE_USDOLLAR"]);
// 03/31/2007 Paul. The price of the product may not be in the same currency as the order form.
// Make sure to convert to the specified currency.
if (gCURRENCY_ID != Sql.ToGuid(rdr["CURRENCY_ID"]))
{
Currency C10n = Currency.CreateCurrency(gCURRENCY_ID);
item.AMOUNT_DUE = C10n.ToCurrency(item.AMOUNT_DUE_USDOLLAR);
}
}
}
}
}
if (Sql.IsEmptyGuid(item.ID))
{
throw(new Exception("Item not found"));
}
}
//catch
{
// 02/04/2007 Paul. Don't catch the exception.
// It is a web service, so the exception will be handled properly by the AJAX framework.
}
return(item);
}
public string[] ItemNumberList(string prefixText, int count)
{
string[] arrItems = new string[0];
try
{
if (Security.USER_ID == Guid.Empty)
{
throw(new Exception("Authentication required"));
}
SplendidCRM.DbProviderFactory dbf = SplendidCRM.DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
// 03/29/2007 Paul. Use LIKE clause so that the user can abbreviate unique part numbers.
sSQL = "select MFT_PART_NUM " + ControlChars.CrLf
+ " from vwPRODUCT_CATALOG" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "ProductTemplates", "list");
Sql.AppendParameter(cmd, prefixText, Sql.SqlFilterMode.StartsWith, "MFT_PART_NUM");
sSQL += " order by MFT_PART_NUM" + ControlChars.CrLf;
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(0, count, dt);
arrItems = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
arrItems[i] = Sql.ToString(dt.Rows[i]["MFT_PART_NUM"]);
}
}
}
}
}
}
catch
{
}
return(arrItems);
}
public string[] InvoiceNameList(string prefixText, int count)
{
string[] arrItems = new string[0];
try
{
if (Security.USER_ID == Guid.Empty)
{
throw(new Exception("Authentication required"));
}
SplendidCRM.DbProviderFactory dbf = SplendidCRM.DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select NAME " + ControlChars.CrLf
+ " from vwINVOICES" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "Invoices", "list");
Sql.AppendParameter(cmd, prefixText, Sql.SqlFilterMode.StartsWith, "NAME");
sSQL += " order by NAME" + ControlChars.CrLf;
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(0, count, dt);
arrItems = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
arrItems[i] = Sql.ToString(dt.Rows[i]["NAME"]);
}
}
}
}
}
}
catch
{
}
return(arrItems);
}
public LineItem GetItemDetailsByNumber(Guid gCURRENCY_ID, string sMFT_PART_NUM)
{
LineItem item = new LineItem();
//try
{
if (Security.USER_ID == Guid.Empty)
{
throw(new Exception("Authentication required"));
}
SplendidCRM.DbProviderFactory dbf = SplendidCRM.DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
// 02/04/2007 Paul. Use LIKE clause so that the user can abbreviate unique part numbers.
sSQL = "select * " + ControlChars.CrLf
+ " from vwPRODUCT_CATALOG" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Security.Filter(cmd, "ProductTemplates", "list");
Sql.AppendParameter(cmd, sMFT_PART_NUM, Sql.SqlFilterMode.StartsWith, "MFT_PART_NUM");
// 07/02/2007 Paul. Sort is important so that the first match is selected.
sSQL += " order by MFT_PART_NUM" + ControlChars.CrLf;
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
item.ID = Sql.ToGuid(rdr["ID"]);
item.NAME = Sql.ToString(rdr["NAME"]);
item.MFT_PART_NUM = Sql.ToString(rdr["MFT_PART_NUM"]);
item.VENDOR_PART_NUM = Sql.ToString(rdr["VENDOR_PART_NUM"]);
item.TAX_CLASS = Sql.ToString(rdr["TAX_CLASS"]);
item.COST_PRICE = Sql.ToDecimal(rdr["COST_PRICE"]);
item.COST_USDOLLAR = Sql.ToDecimal(rdr["COST_USDOLLAR"]);
item.LIST_PRICE = Sql.ToDecimal(rdr["LIST_PRICE"]);
item.LIST_USDOLLAR = Sql.ToDecimal(rdr["LIST_USDOLLAR"]);
item.UNIT_PRICE = Sql.ToDecimal(rdr["UNIT_PRICE"]);
item.UNIT_USDOLLAR = Sql.ToDecimal(rdr["UNIT_USDOLLAR"]);
// 03/31/2007 Paul. The price of the product may not be in the same currency as the order form.
// Make sure to convert to the specified currency.
if (gCURRENCY_ID != Sql.ToGuid(rdr["CURRENCY_ID"]))
{
Currency C10n = Currency.CreateCurrency(gCURRENCY_ID);
item.COST_PRICE = C10n.ToCurrency(item.COST_USDOLLAR);
item.LIST_PRICE = C10n.ToCurrency(item.LIST_USDOLLAR);
item.UNIT_PRICE = C10n.ToCurrency(item.UNIT_USDOLLAR);
}
}
}
}
}
if (Sql.IsEmptyGuid(item.ID))
{
throw(new Exception("Item not found"));
}
}
//catch
{
// 02/04/2007 Paul. Don't catch the exception.
// It is a web service, so the exception will be handled properly by the AJAX framework.
}
return(item);
}
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);
}
}