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;
                }
            }
        }
Beispiel #2
0
        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()));
            }
        }
Beispiel #3
0
        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);
            }
        }