Пример #1
0
        public void ExecuteUserSync(UserSynch usersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
        {
            int i;
            ArrayList debugList = new ArrayList();
            StopWatch time = new StopWatch();

            string baseOU = usersyn.BaseUserOU;
            string DC = baseOU.Substring(baseOU.IndexOf("DC"));
            string sqlForCustomFields = "";

            // Table string place holders
            string sqlUsersTable = "#FHC_USERS_SQLusersTable";
            string adUsersTable = "#FHC_USERS_ADusersTable";

            SqlDataReader add;
            SqlDataReader delete;
            SqlDataReader update;

            SearchScope scope = SearchScope.OneLevel;
            ArrayList completeSqlKeys = new ArrayList();
            ArrayList completeADKeys = new ArrayList();
            ArrayList adUpdateKeys = new ArrayList();
            ArrayList sqlUpdateKeys = new ArrayList();
            ArrayList extraFieldsToReturn = new ArrayList();
            ArrayList fields = new ArrayList();
            Dictionary<string, string> userObject = new Dictionary<string, string>();
            SqlConnection sqlConn = new SqlConnection("Data Source=" + usersyn.DataServer + ";Initial Catalog=" + usersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360");

            if (settingsConfig.TempTables == true)
            {
                sqlUsersTable = "#FHC_USERS_SQLusersTable";
                adUsersTable = "#FHC_USERS_ADusersTable";
            }
            else
            {
                sqlUsersTable = "FHC_USERS_SQLusersTable";
                adUsersTable = "FHC_USERS_ADusersTable";

            }

            //SqlDataReader sqlusers;
            SqlCommand sqlComm;
            SqlCommand sqlComm2;
            string recordCount = "";
            DataTable adUsers = new DataTable();

            sqlConn.Open();
            //housecleaning
            log.addTrn("Cleaning out tables", "Info");
            if (settingsConfig.TempTables == false)
            {
                tools.DropTable(sqlUsersTable, sqlConn, log);
                tools.DropTable(adUsersTable, sqlConn, log);
            }

            //if were only updating it doesnt matter where we want ot put new users
            if (usersyn.UpdateOnly == false)
            {
                log.addTrn("Initial setup of OUs and Groups", "Info");
                // create initial ou's; will log a warning out if they already exist
                tools.CreateOURecursive(usersyn.BaseUserOU, log);
                tools.CreateOURecursive(usersyn.UserHoldingTank, log);

                // setup extentions for the user accounts to go in to the right ou's
                userObject.Add("sAMAccountName", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
                userObject.Add("CN", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
                userObject.Add("description", "Universal Group For Users");
                // creates the group if it does not exist
                tools.CreateGroup(usersyn.UniversalGroup.Remove(0, usersyn.UniversalGroup.IndexOf(",") + 1), userObject, log);
            }

            // need to add this field first to use as a primary key when checking for existance in AD
            completeSqlKeys.Add("sAMAccountName");
            completeSqlKeys.Add("CN");
            completeSqlKeys.Add("sn");
            completeSqlKeys.Add("givenName");
            completeSqlKeys.Add("homePhone");
            completeSqlKeys.Add("st");
            completeSqlKeys.Add("streetAddress");
            completeSqlKeys.Add("l");
            completeSqlKeys.Add("postalCode");
            // ?????? MIGHT NOT BE USED

            // Lets make the SQL fields to check for update
            sqlUpdateKeys.Add("sn");
            sqlUpdateKeys.Add("givenName");
            sqlUpdateKeys.Add("homePhone");
            sqlUpdateKeys.Add("st");
            sqlUpdateKeys.Add("streetAddress");
            sqlUpdateKeys.Add("l");
            sqlUpdateKeys.Add("postalCode");

            // Lets make the Active Directory Keys as well
            completeADKeys.Add("sAMAccountName");
            completeADKeys.Add("CN");
            completeADKeys.Add("sn");
            completeADKeys.Add("givenName");
            completeADKeys.Add("homePhone");
            completeADKeys.Add("st");
            completeADKeys.Add("streetAddress");
            completeADKeys.Add("l");
            completeADKeys.Add("postalCode");
            completeADKeys.Add("distinguishedName");

            // Lets make the Active Directory fields to check for update
            adUpdateKeys.Add("sn");
            adUpdateKeys.Add("givenName");
            adUpdateKeys.Add("homePhone");
            adUpdateKeys.Add("st");
            adUpdateKeys.Add("streetAddress");
            adUpdateKeys.Add("l");
            adUpdateKeys.Add("postalCode");

            //build custom keys
            for (i = 0; i < usersyn.UserCustoms.Rows.Count; i++)
            {
                // build keys to pull back from SQL
                // as well keys to check if these fields need updating
                completeSqlKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
                sqlUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());

                // build keys to pull back from AD
                // as well keys to check if these fields need updating
                completeADKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
                adUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());

                // build fields to pull back from SQL
                //create props from rows in usercustoms datatable our column names match the appropriate fields in AD and SQL
                if (usersyn.UserCustoms.Rows[i][1].ToString() != "Static Value")
                {
                    sqlForCustomFields += ", RTRIM(" + usersyn.UserCustoms.Rows[i][1].ToString() + ") AS " + usersyn.UserCustoms.Rows[i][0].ToString();
                }
                // static fields get static values for the table to get updated
                else
                {
                    sqlForCustomFields += ", '" + usersyn.UserCustoms.Rows[i][2].ToString() + "' AS " + usersyn.UserCustoms.Rows[i][0].ToString();
                }
            }

            // grab users data from sql
            log.addTrn("Get users from SQL tables", "Info");
            if (usersyn.User_where == "")
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
                    ", RTRIM(" + usersyn.User_CN + ") AS CN" +
                    ", RTRIM(" + usersyn.User_Lname + ") AS sn" +
                    ", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
                    ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
                    ", RTRIM(" + usersyn.User_State + ") AS st" +
                    ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
                    //", RTRIM(" + usersyn.User_mail + ") AS mail" +
                    ", RTRIM(" + usersyn.User_city + ") AS l" +
                    ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
                    ", RTRIM(" + usersyn.User_password + ") AS password" +
                    sqlForCustomFields +
                    " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable, sqlConn);
            }
            else
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
                    ", RTRIM(" + usersyn.User_CN + ") AS CN" +
                    ", RTRIM(" + usersyn.User_Lname + ") AS sn" +
                    ", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
                    ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
                    ", RTRIM(" + usersyn.User_State + ") AS st" +
                    ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
                    //", RTRIM(" + usersyn.User_mail + ") AS mail" +
                    ", RTRIM(" + usersyn.User_city + ") AS l" +
                    ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
                    ", RTRIM(" + usersyn.User_password + ") AS password" +
                    sqlForCustomFields +
                    " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable +
                    " WHERE " + usersyn.User_where, sqlConn);
            }
            try
            {
                sqlComm.CommandTimeout = 360;
                sqlComm.ExecuteNonQuery();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");

            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                throw;
            }
            if (usersyn.SearchScope == "Subtree")
            {
                scope = SearchScope.Subtree;
            }

            // go grab all the users from AD
            log.addTrn("Get users from active directory", "Info");
            adUsers = tools.EnumerateUsersInOUDataTable(usersyn.BaseUserOU, completeADKeys, adUsersTable, scope, log);
            if (adUsers.Rows.Count > 0)
            {
                // make the temp table for ou comparisons

                tools.Create_Table(adUsers, adUsersTable, sqlConn, log);

                // Quick check to stop adding if the update only box is checked
                if (usersyn.UpdateOnly == false)
                {
                    // compare query for the add/remove
                    log.addTrn("Query to find users to add", "Info");
                    add = tools.QueryNotExistsByPkey(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);

                    // actual add stuff
                    log.addTrn("Adding users", "Info");
                    tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
                    add.Close();

                    sqlComm2 = new SqlCommand("select count(sAMAccountName) FROM " + sqlUsersTable, sqlConn);
                    sqlComm2.CommandTimeout = 360;
                    recordCount = sqlComm2.ExecuteScalar().ToString();
                    sqlComm2.Dispose();

                    if (recordCount != "0")
                    {
                        // compare query to find records which need deletion
                        log.addTrn("Query to find users to delete", "Info");
                        delete = tools.QueryNotExistsByPkey(adUsersTable, sqlUsersTable, sqlConn, usersyn.User_sAMAccount, completeADKeys[0].ToString(), log);

                        // delete users in AD
                        log.addTrn("Deleting users", "Info");
                        try
                        {
                            while (delete.Read())
                            {

                                tools.DeleteUserAccount((string)delete["distinguishedname"], log);
                                // log.addTrn("User removed ;" + (string)delete[adUpdateKeys[1].ToString()].ToString().Trim());
                            }
                        }
                        catch (Exception ex)
                        {
                            log.addTrn("Issue deleting AD users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                        }
                        delete.Close();
                    }
                }

                // add the extra fields in form ".field ,"
                extraFieldsToReturn.Add(adUsersTable + ".distinguishedname ,");

                log.addTrn("Query to find users to update", "Info");
                update = tools.CheckUpdate(sqlUsersTable, adUsersTable, "sAMAccountName", "sAMAccountName", sqlUpdateKeys, adUpdateKeys, extraFieldsToReturn, 1, sqlConn, log);

                // update users in ad
                // last record which matches the primary key is the one which gets inserted into the database
                log.addTrn("Updating users", "Info");
                tools.UpdateUsers(update, DC, usersyn, log);

                update.Close();
            }
            // did not find any records in AD we are only adding users
            else
            {
                // and we are not updating users
                if (usersyn.UpdateOnly == false)
                {
                    // add the users without doing additional checks
                    tools.Create_Table(adUsers, adUsersTable, sqlConn, log);
                    log.addTrn("Query to find users to add", "Info");
                    add = tools.QueryNotExistsAllFields(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);
                    log.addTrn("Add all users", "Info");
                    tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
                    add.Close();
                }
            }
            sqlConn.Close();
        }