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();
}
