public void UpdateGmailUser(AppsService service, GmailUsers gusersyn, SqlDataReader usersToUpdate, LogFile log)
{
string userNickName = "";
string middlename = "";
try
{
while (usersToUpdate.Read())
{
try
{
UserEntry gmailUser = service.RetrieveUser((string)usersToUpdate[gusersyn.User_StuID]);
//special gmail username replace string only allows -_. special character thru
gmailUser.Login.UserName = System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%", "_");
gmailUser.Name.FamilyName = usersToUpdate[gusersyn.User_Lname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
gmailUser.Name.GivenName = usersToUpdate[gusersyn.User_Fname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
middlename = usersToUpdate[gusersyn.User_Mname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
service.UpdateUser(gmailUser);
log.addTrn("Updated " + System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A") + " because of name change. New Name is " + gmailUser.Name.FamilyName.ToString() + ", " + gmailUser.Name.GivenName.ToString(), "Transaction");
// if (gusersyn.Levenshtein == true)
// {
userNickName = GetNewUserNickname(service, gmailUser.Login.UserName, gmailUser.Name.GivenName, middlename, gmailUser.Name.FamilyName, 0, false);
log.addTrn("Added New Alias for " + gmailUser.Login.UserName + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain, "Transaction");
// }
}
catch (Exception ex)
{
log.addTrn("Failed update gmail account " + System.Web.HttpUtility.UrlEncode(usersToUpdate[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%", "_") + " exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
}
catch (Exception ex)
{
log.addTrn("Issue updating gmail users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
static void Main(string[] args)
{
Arguments CommandLine = new Arguments(args);
string operation = "";
string file = "";
if (CommandLine["F"] != null)
{
file = CommandLine["F"];
}
else Console.WriteLine("File not defined full path needed -F=c:\\xxx\\yyy\\zzz\\file.ldap warning there is no sanity check on the path");
operation = CommandLine["O"];
if (CommandLine["O"] != null)
{
if (CommandLine["O"] != "users" || CommandLine["O"] != "groups" || CommandLine["O"] != "OUmap" || CommandLine["O"] != "gmail")
{
Console.WriteLine("Operation not defined -O=users -O=groups -O=OUmap -O=gmail");
}
else
{
operation = CommandLine["O"];
}
}
// Check for tests first
// If tests are desired, skip normal opretaions
if( CommandLine["T"] != null ){
// Collection of Class objects
// Each class needs to have a runTests() method
GroupSynch groupconfig = new GroupSynch();
UserSynch userconfig = new UserSynch();
GmailUsers guserconfig = new GmailUsers();
executionOrder execution = new executionOrder();
UserStateChange usermapping = new UserStateChange();
ConfigSettings settingsconfig = new ConfigSettings();
utils.ToolSet tools = new ToolSet();
LogFile log = new LogFile();
ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
StopWatch timer = new StopWatch();
log.initiateTrn();
// Sift through are different tests
switch(CommandLine["T"])
{
// Run tests specific to users sync
case "users":
// userconfig.runTests();
break;
// Run tests specific to group sync
case "groups":
// groupconfig.runTests();
break;
// Run tests specific to OUmap sync
case "OUmap":
// Not sure how this operation is used
break;
// Run tests specifc to gmail sync
case "gmail":
// guserconfig.runTests();
break;
// Run all tests
default:
case "all":
// userconfig.runTests();
// groupconfig.runTests();
// guserconfig.runTests();
break;
}
}
// MessageBox.Show("operation is " + operation + " file is " + file);
else if (file != "" && operation != "")
{
// woot halleluijah we have input from the user time to execute
//duplicate the gui fucntionality in cmd line
// we won't check this input cause its from a really smart system administrator
// just in case file expects a full path
// c:\blah\blah\blah.ext
// valid oprations are
// users groups OUmap gmail
// create objects to hold save data
GroupSynch groupconfig = new GroupSynch();
UserSynch userconfig = new UserSynch();
GmailUsers guserconfig = new GmailUsers();
executionOrder execution = new executionOrder();
UserStateChange usermapping = new UserStateChange();
ConfigSettings settingsconfig = new ConfigSettings();
utils.ToolSet tools = new ToolSet();
LogFile log = new LogFile();
ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
StopWatch timer = new StopWatch();
log.initiateTrn();
// perform operations based on the data input from the user fro groups users, OU's and gmail
if (operation == "group")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
// Load values into text boxes
// reload properties each time as they are overwritten with the combo object trigger events
groupconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start Groups Syncs", "Info");
timer.Start();
groupSyncr.ExecuteGroupSync(groupconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Groups " + groupconfig.Group_Append + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
log.errors.Add("Failed to load save file");
}
//// save log to disk
//SaveFileDialog saveFileDialog1 = new SaveFileDialog();
//saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
//saveFileDialog1.FilterIndex = 2;
//saveFileDialog1.RestoreDirectory = true;
//if (saveFileDialog1.ShowDialog() == DialogResult.OK)
//{
// // create a file stream, where "c:\\testing.txt" is the file path
// System.IO.FileStream fs = new System.IO.FileStream(saveFileDialog1.FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite);
// // create a stream writer
// System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);
// // write to file (buffer), where textbox1 is your text box
// sw.WriteLine("{0}", result2);
// sw.WriteLine("{0}", result);
// // flush buffer (so the text really goes into the file)
// sw.Flush();
// // close stream writer and file
// sw.Close();
// fs.Close();
//}
}
if (operation == "users")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
DataTable customs = new DataTable();
BindingSource bs = new BindingSource();
//OpenFileDialog openFileDialog1 = new OpenFileDialog();
//openFileDialog1.InitialDirectory = "c:\\";
//openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
//openFileDialog1.FilterIndex = 2;
//openFileDialog1.RestoreDirectory = true;
//if (openFileDialog1.ShowDialog() == DialogResult.OK)
//{
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
userconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start User Synch", "Info");
timer.Start();
groupSyncr.ExecuteUserSync(userconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Users " + userconfig.BaseUserOU + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
Console.Write("Failed to load save file");
}
}
if (operation == "gmail")
{
Dictionary<string, string> properties = new Dictionary<string, string>();
BindingSource bs = new BindingSource();
try
{
StreamReader re = File.OpenText(file);
string input = null;
while ((input = re.ReadLine()) != null && input != "<config>")
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
guserconfig.Load(properties);
//load config settings
properties.Clear();
while ((input = re.ReadLine()) != null)
{
string[] parts = input.Split('|');
properties.Add(parts[0].Trim(), parts[1].Trim());
}
re.Close();
settingsconfig.Load(properties);
log.addTrn("Start Gmail Synch", "Info");
timer.Start();
gmailSyncr.EmailUsersSync(guserconfig, settingsconfig, tools, log);
timer.Stop();
log.addTrn("Gmail " + guserconfig.Admin_domain + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
tools.savelog(log, settingsconfig);
}
catch
{
Console.Write("Failed to load save file");
}
}
}
else
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
public void Mass_Email_Shift(GmailUsers gusersyn, string sourceTable, string targetTable, string sourcePimaryKey, string targetPrimaryKey, ArrayList sourceColumnsAndTable, ArrayList targetColumnsAndTable, string whereClause, SqlConnection sqlConn, LogFile log)
{
// sourceColumnsAndTable and targetColumnsAndTable must have the same number of columns (its a one to one transfer)
// updates the targetColumnsAndTable with the data from the sourceColumnsAndTable
// does not overwrite secondary fields which contain the email domain from the gusersyn
//
// sourceColumnsAndTable expects "table.column"
// targetColumnsAndTable expects "table.column"
// massUpdateSameTable truely a useless flag to overload the function
//
//UPDATE table1
// SET table1.col = table2.col1
//FROM table1 INNER JOIN table2 ON table1.Pkey = table2.Pkey
//WHERE variable clause
//UPDATE a1
//SET a1.e_mail2 = a1.e_mail
//FROM address as a1 INNER JOIN (select * from address as a2 where rtrim(a2.gmail) <> '') as a2 ON a1.soc_sec = a2.soc_sec
string columnValues = "";
int sourceCount = sourceColumnsAndTable.Count;
int i = 0;
for (i = 0; i < sourceCount; i++)
{
columnValues += targetColumnsAndTable[i].ToString() + " = " + sourceColumnsAndTable[i].ToString() + ", ";
}
columnValues = columnValues.Remove(columnValues.Length - 2);
SqlCommand sqlComm;
if (whereClause.Length == 0)
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey + " WHERE " + sourceColumnsAndTable[0] + " not like '%" + gusersyn.Admin_domain + "%' AND " + sourceColumnsAndTable[0] + " <> '' AND " + sourceColumnsAndTable[0] + " <> '?' AND " + sourceColumnsAndTable[0] + " IS NOT NULL ", sqlConn);
}
else
{
sqlComm = new SqlCommand("UPDATE " + targetTable + " SET " + columnValues + " FROM " + targetTable + " INNER JOIN " + sourceTable + " ON " + targetTable + "." + targetPrimaryKey + " = " + sourceTable + "." + sourcePimaryKey + " WHERE " + sourceColumnsAndTable[0] + " not like '%" + gusersyn.Admin_domain + "%' AND " + sourceColumnsAndTable[0] + " <> '' AND " + sourceColumnsAndTable[0] + " <> '?' AND " + sourceColumnsAndTable[0] + " IS NOT NULL AND " + whereClause, 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");
}
}
public void Mass_update_email_field(SqlDataReader users, SqlConnection sqlConn, GmailUsers gusersyn, LogFile log)
{
// string concatenation replaced with stringbuilder due to rumored performance increases
//
/*
UPDATE address
SET e_mail2 = e_mail,
e_mail = 'actual address'
WHERE primary_key = '####' AND where clause
* customized to work with GmailUsers object pulling the email field and primary keys for the transition form there
*/
StringBuilder sqlstring = new StringBuilder();
SqlCommand sqlComm;
try
{
while (users.Read())
{
// now add the data
sqlstring.Remove(0, sqlstring.Length);
sqlstring.Append("UPDATE " + gusersyn.Writeback_table + " ");
if (gusersyn.Writeback_transfer_email_checkbox == true)
{
sqlstring.Append("SET " + gusersyn.Writeback_secondary_email_field + " = " + gusersyn.Writeback_email_field + ",");
sqlstring.Append(gusersyn.Writeback_email_field + " = '" + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + "' ");
}
else
{
sqlstring.Append("SET " + gusersyn.Writeback_email_field + " = '" + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + "' ");
}
sqlstring.Append("WHERE " + gusersyn.Writeback_primary_key + " = '" + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + "' ");
if (gusersyn.Writeback_where_clause.ToString().Trim() != "")
{
sqlstring.Append("AND " + gusersyn.Writeback_where_clause);
}
sqlComm = new SqlCommand(sqlstring.ToString(), sqlConn);
// MessageBox.Show(sqlstring.Length.ToString());
try
{
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
log.addTrn("DB email writeback, user " + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + ", email " + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''"), "Transaction");
}
catch (Exception ex)
{
log.addTrn("DB email writeback failure, user " + users[gusersyn.Writeback_primary_key].ToString().Replace("'", "''") + ", email " + users[gusersyn.Writeback_email_field].ToString().Replace("'", "''") + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
sqlComm.Dispose();
}
}
catch (Exception ex)
{
log.addTrn("Issue in DB writeback datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
// Gmail data pull
public DataTable Get_Gmail_Users(AppsService service, GmailUsers gusersyn, string table, LogFile log)
{
// nicknames will have to dealt with seperately
DataTable returnvalue = new DataTable();
DataRow row;
returnvalue.TableName = table;
int i = 0;
int count = 0;
returnvalue.Columns.Add(gusersyn.User_StuID);
returnvalue.Columns.Add(gusersyn.User_Fname);
returnvalue.Columns.Add(gusersyn.User_Lname);
try
{
UserFeed usersList = service.RetrieveAllUsers();
count = usersList.Entries.Count;
//result.AppendText("domain " + service.Domain + "\n");
//result.AppendText("app name " + service.ApplicationName + "\n");
//result.AppendText("users " + count + "\n");
row = returnvalue.NewRow();
for (i = 0; i < count; i++)
{
UserEntry userEntry = usersList.Entries[i] as UserEntry;
// special handling for userID due to % being an illegal character using _ as an escape character
row[0] = (System.Web.HttpUtility.UrlDecode(userEntry.Login.UserName.ToString().Replace("_", "%")));
// decode names due to encoding to remove <>= characters
row[1] = (System.Web.HttpUtility.UrlDecode(userEntry.Name.GivenName.ToString()));
row[2] = (System.Web.HttpUtility.UrlDecode(userEntry.Name.FamilyName.ToString()));
//userList.Add(userEntry.Login.UserName.ToString());
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
}
catch (Exception ex)
{
log.addTrn("failed to pull gmail user list exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public DataTable Get_Gmail_Nicknames(AppsService service, GmailUsers gusersyn, string table, LogFile log)
{
// nicknames retrieval
DataTable returnvalue = new DataTable();
DataRow row;
returnvalue.TableName = table;
int i = 0;
int count = 0;
string nickname = "";
returnvalue.Columns.Add(gusersyn.Writeback_primary_key);
returnvalue.Columns.Add("nickname");
returnvalue.Columns.Add("Email");
try
{
NicknameFeed usersNicknameList = service.RetrieveAllNicknames();
count = usersNicknameList.Entries.Count;
//result.AppendText("domain " + service.Domain + "\n");
//result.AppendText("app name " + service.ApplicationName + "\n");
//result.AppendText("users " + count + "\n");
row = returnvalue.NewRow();
for (i = 0; i < count; i++)
{
NicknameEntry userNicknameEntry = usersNicknameList.Entries[i] as NicknameEntry;
// special handling for userID due to % being an illegal character using _ as an escape character
row[0] = (System.Web.HttpUtility.UrlDecode(userNicknameEntry.Login.UserName.ToString().Replace("_", "%")));
// decode names due to encoding to remove <>= characters
nickname = (System.Web.HttpUtility.UrlDecode(userNicknameEntry.Nickname.Name.ToString()));
row[1] = nickname;
row[2] = nickname + "@" + gusersyn.Admin_domain.ToString();
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
}
}
catch (Exception ex)
{
log.addTrn("failed to pull gmail nickname list exception " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public DataTable Create_Gmail_Users(AppsService service, GmailUsers gusersyn, SqlDataReader users, LogFile log)
{
// user alising not created yet
// Takes the SQLDataReader and creates all users in the reader
// create the table for holding the users final gmail account infomation for email writeback
DataTable returnvalue = new DataTable();
DataRow row;
returnvalue.TableName = "users";
returnvalue.Columns.Add(gusersyn.Writeback_primary_key);
returnvalue.Columns.Add(gusersyn.Writeback_email_field);
row = returnvalue.NewRow();
string studentID = "";
string first_name = "";
string last_name = "";
string middle_name = "";
string password = "";
string userNickName = "Aliasing off";
try
{
while (users.Read())
{
try
{
// using _ as escape character allows illegal characters in username
studentID = System.Web.HttpUtility.UrlEncode(users[gusersyn.User_StuID].ToString()).Replace("+", " ").Replace("*", "%2A").Replace("!", "%21").Replace("(", "%28").Replace(")", "%29").Replace("'", "%27").Replace("_", "%5f").Replace(" ", "%20").Replace("%", "_");
// names are less restricitve the only illegal characters are < > =
first_name = users[gusersyn.User_Fname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
middle_name = users[gusersyn.User_Mname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
last_name = users[gusersyn.User_Lname].ToString().Replace("<", "%3c").Replace(">", "%3e").Replace("=", "%3d").Replace("%", "%25");
if (gusersyn.User_password_generate_checkbox == false)
{
// password needs to bea able to handle special characters
password = users[gusersyn.User_password].ToString();
}
else
{
password = GetPassword(14);
}
if (gusersyn.User_password_short_fix_checkbox == true && password.Length < 8)
{
password = GetPassword(14);
}
//Create a new user.
UserEntry insertedEntry = service.CreateUser(studentID, first_name, last_name, password);
//if (gusersyn.Levenshtein == true)
//{
// create user ailas here
userNickName = GetNewUserNickname(service, studentID, first_name, middle_name, last_name, 0, false );
row[0] = studentID;
if (userNickName != "failure")
{
row[1] = userNickName + "@" + gusersyn.Admin_domain;
}
else
{
row[1] = studentID + "@" + gusersyn.Admin_domain;
}
returnvalue.Rows.Add(row);
row = returnvalue.NewRow();
log.addTrn("Added Gmail user " + studentID + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain, "Transaction");
// }
}
catch (AppsException e)
{
log.addTrn("Failed adding Gmail user " + studentID + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain + " failed " + e.Message.ToString() + " reason " + e.Reason.ToString(), "Error");
}
catch (Exception ex)
{
log.addTrn("Failed adding Gmail user " + studentID + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain + " failed " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
}
catch (Exception ex)
{
log.addTrn("Issue adding gmail users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
return returnvalue;
}
public void CreateSendAs(GmailUsers gusersyn, SqlDataReader userNicknames, string sendASFieldName, string replyToFieldName, LogFile log)
{
//AppsService service = new AppsService(gusersyn.Admin_domain, gusersyn.Admin_user + "@" + gusersyn.Admin_domain, gusersyn.Admin_password);
GoogleMailSettingsService gmailSettings = new GoogleMailSettingsService(gusersyn.Admin_domain, gusersyn.Admin_domain);
gmailSettings.setUserCredentials(gusersyn.Admin_user + "@" + gusersyn.Admin_domain, gusersyn.Admin_password);
try
{
while (userNicknames.Read())
{
try
{
gmailSettings.CreateSendAs((string)userNicknames[gusersyn.User_StuID], (string)userNicknames[gusersyn.User_Fname] + " " + (string)userNicknames[gusersyn.User_Lname], (string)userNicknames[sendASFieldName], (string)userNicknames[replyToFieldName], "true");
log.addTrn("Created send as alias " + (string)userNicknames[sendASFieldName] + " for userlogin " + (string)userNicknames[gusersyn.User_StuID], "Transaction");
}
catch (Exception ex)
{
log.addTrn("Failed user send as creation " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
}
catch (Exception ex)
{
log.addTrn("Issue creating send as datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
}
public void EmailUsersSync(GmailUsers gusersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
{
// MessageBox.Show("gmail " + gusersyn.Admin_password + " " + gusersyn.Admin_domain + " " + gusersyn.Admin_user + " " + gusersyn.DataServer + " " + gusersyn.DBCatalog + " " + gusersyn.User_ad_OU + " " + gusersyn.User_Datasource + " " + gusersyn.User_dbTable + " " + gusersyn.User_Fname + " " + gusersyn.User_Lname + " " + gusersyn.User_Mname + " " + gusersyn.User_password + " " + gusersyn.User_password_short_fix_checkbox.ToString() + " " + gusersyn.User_password_generate_checkbox.ToString() + " " + gusersyn.User_StuID + " " + gusersyn.User_table_view + " " + gusersyn.User_where + " " + gusersyn.Writeback_AD_checkbox.ToString() + " " + gusersyn.Writeback_ad_OU + " " + gusersyn.Writeback_DB_checkbox.ToString() + " " + gusersyn.Writeback_email_field + " " + gusersyn.Writeback_primary_key + " " + gusersyn.Writeback_secondary_email_field + " " + gusersyn.Writeback_table + " " + gusersyn.Writeback_transfer_email_checkbox.ToString() + " " + gusersyn.Writeback_where_clause);
// Email addresses are static so only the names can be updated. passwords will be ignored
// appservice variables will come from a config designed ot hold its data (sql and Gmail login)
string userDN = "";
AppsService service = new AppsService(gusersyn.Admin_domain, gusersyn.Admin_user + "@" + gusersyn.Admin_domain, gusersyn.Admin_password);
ArrayList completeSqlKeys = new ArrayList();
ArrayList completeGmailKeys = new ArrayList();
ArrayList gmailUpdateKeys = new ArrayList();
ArrayList sqlUpdateKeys = new ArrayList();
ArrayList adUpdateKeys = new ArrayList();
ArrayList additionalKeys = new ArrayList();
// Table place holders
string sqlUsersTable = "#FHC_LDAP_sqlusersTable";
string gmailUsersTable = "#FHC_LDAP_gmailusersTable";
string nicknamesFromGmailTable = "#FHC_LDAP_gmailNicknamesTable";
string loginWithoutNicknamesTable = "#FHC_LDAP_loginsWONicknamesTable";
string adNicknamesTable = "#FHC_LDAP_adNicknamesTable";
string sqlNicknamesTable = "#FHC_LDAP_sqlNicknamesTable";
string nicknamesToUpdateDBTable = "#FHC_LDAP_nicknamesToUpdateDB";
string nicknamesFilteredForDuplicatesTable = "#FHC_LDAP_nicknamesFilteredDuplicates";
string nicknamesFromGmailTable2 = "#FHC_LDAP_gmailNicknamesTable2";
string gmailUsersTableWB = "#FHC_LDAP_gmailusersTableWB";
SqlDataReader add;
//SqlDataReader delete;
SqlDataReader update;
SqlConnection sqlConn = new SqlConnection("Data Source=" + gusersyn.DataServer + ";Initial Catalog=" + gusersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360;");
if (settingsConfig.TempTables == true)
{
sqlUsersTable = "#FHC_LDAP_sqlusersTable";
gmailUsersTable = "#FHC_LDAP_gmailusersTable";
}
else
{
sqlUsersTable = "FHC_LDAP_sqlusersTable";
gmailUsersTable = "FHC_LDAP_gmailusersTable";
}
//SqlDataReader sqlusers;
SqlCommand sqlComm;
DataTable gmailUsers = new DataTable();
DataTable adUsers = new DataTable();
DataTable writeback = new DataTable();
// set up fields to pull back from SQL or AD if the flag is checked both must contain the same data
completeSqlKeys.Add(gusersyn.User_StuID);
completeSqlKeys.Add(gusersyn.User_Fname);
completeSqlKeys.Add(gusersyn.User_Lname);
completeSqlKeys.Add(gusersyn.User_Mname);
completeSqlKeys.Add(gusersyn.User_password);
// Lets make the SQL fields to check for update
sqlUpdateKeys.Add(gusersyn.User_Fname);
sqlUpdateKeys.Add(gusersyn.User_Lname);
// Lets make the gmail Keys as well
completeGmailKeys.Add(gusersyn.User_StuID);
completeGmailKeys.Add(gusersyn.User_Fname);
completeGmailKeys.Add(gusersyn.User_Mname);
completeGmailKeys.Add(gusersyn.User_Lname);
completeGmailKeys.Add(gusersyn.User_password);
// Lets make the gmail fields to check for update
gmailUpdateKeys.Add(gusersyn.User_Fname);
gmailUpdateKeys.Add(gusersyn.User_Lname);
// List of extra fields to pull when dealing with accounts for update since we only compare first and last name we need to repull ID and middle name
additionalKeys.Add(sqlUsersTable + "." + gusersyn.User_StuID + ", ");
additionalKeys.Add(sqlUsersTable + "." + gusersyn.User_Mname + ", ");
sqlConn.Open();
//housecleaning
log.addTrn("Clear out tables for use", "Info");
if (settingsConfig.TempTables == false)
{
tools.DropTable(sqlUsersTable, sqlConn, log);
tools.DropTable(gmailUsersTable, sqlConn, log);
}
// this statement picks the datasource SQL vs AD and sets up the temp table
if (gusersyn.User_Datasource == "database")
{
// grab users data from sql
log.addTrn("Get users from SQL", "Info");
if (gusersyn.User_where == "")
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.User_StuID + ")" +
", RTRIM(" + gusersyn.User_Fname + ") AS " + gusersyn.User_Fname +
", RTRIM(" + gusersyn.User_Lname + ") AS " + gusersyn.User_Lname +
", RTRIM(" + gusersyn.User_Mname + ") AS " + gusersyn.User_Mname +
", RTRIM(" + gusersyn.User_password + ") AS " + gusersyn.User_password +
" INTO " + sqlUsersTable + " FROM " + gusersyn.User_dbTable, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.User_StuID +
", RTRIM(" + gusersyn.User_Fname + ")" + gusersyn.User_Fname +
", RTRIM(" + gusersyn.User_Lname + ")" + gusersyn.User_Lname +
", RTRIM(" + gusersyn.User_Mname + ")" + gusersyn.User_Mname +
", RTRIM(" + gusersyn.User_password + ")" + gusersyn.User_password +
" INTO " + sqlUsersTable + " FROM " + gusersyn.User_dbTable +
" WHERE " + gusersyn.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");
// If we add deletion we need to fail out if this is blank
//throw;
}
}
else
{
log.addTrn("Get users from AD", "Info");
adUsers = tools.EnumerateUsersInOUDataTable(gusersyn.User_ad_OU, completeSqlKeys, sqlUsersTable, SearchScope.OneLevel, log);
// build the database temp table from the users retrieved into adUsers
tools.Create_Table(adUsers, sqlUsersTable, sqlConn, log);
}
// go grab all the users from Gmail from the database
log.addTrn("Get users from Gmail for comparison", "Info");
gmailUsers = tools.Get_Gmail_Users(service, gusersyn, gmailUsersTable, log);
// make the temp table for ou comparisons
tools.Create_Table(gmailUsers, gmailUsersTable, sqlConn, log);
// compare and add/remove
log.addTrn("Query to find the users who need to be created", "Info");
add = tools.QueryNotExistsByPkey(sqlUsersTable, gmailUsersTable, sqlConn, gusersyn.User_StuID, gmailUsers.Columns[0].ColumnName, log);
log.addTrn("Adding users", "Info");
tools.Create_Gmail_Users(service, gusersyn, add, log);
add.Close();
//delete = tools.QueryNotExistsAllFields(sqlUsersTable, gmailUsersTable, sqlConn, gusersyn.User_StuID, completeGmailKeys[0].ToString());
// delete groups in AD
//while (delete.Read())
//{
//tools.DeleteGmailUserAccount((string)delete[0], (string)delete[1], log);
//
//}
//delete.Close();
log.addTrn("Query to find users to update", "Info");
update = tools.CheckUpdate(sqlUsersTable, gmailUsersTable, gusersyn.User_StuID, gmailUsers.Columns[0].ColumnName, sqlUpdateKeys, gmailUpdateKeys, additionalKeys, 0, sqlConn, log);
log.addTrn("Updating users", "Info");
tools.UpdateGmailUser(service, gusersyn, update, log);
update.Close();
// ***********************************
// ** Start writeback features
// ***********************************
if (settingsConfig.TempTables == true)
{
nicknamesFromGmailTable = "#FHC_LDAP_gmailNicknamesTable";
loginWithoutNicknamesTable = "#FHC_LDAP_loginsWONicknamesTable";
adNicknamesTable = "#FHC_LDAP_adNicknamesTable";
sqlNicknamesTable = "#FHC_LDAP_sqlNicknamesTable";
nicknamesToUpdateDBTable = "#FHC_LDAP_nicknamesToUpdateDB";
nicknamesFilteredForDuplicatesTable = "#FHC_LDAP_nicknamesFilteredDuplicates";
nicknamesFromGmailTable2 = "#FHC_LDAP_gmailNicknamesTable2";
gmailUsersTableWB = "#FHC_LDAP_gmailusersTableWB";
}
else
{
nicknamesFromGmailTable = "FHC_LDAP_gmailNicknamesTable";
loginWithoutNicknamesTable = "FHC_LDAP_loginsWONicknamesTable";
adNicknamesTable = "FHC_LDAP_adNicknamesTable";
sqlNicknamesTable = "FHC_LDAP_sqlNicknamesTable";
nicknamesToUpdateDBTable = "FHC_LDAP_nicknamesToUpdateDB";
nicknamesFilteredForDuplicatesTable = "FHC_LDAP_nicknamesFilteredDuplicates";
nicknamesFromGmailTable2 = "FHC_LDAP_gmailNicknamesTable2";
gmailUsersTableWB = "FHC_LDAP_gmailusersTableWB";
}
string dc = gusersyn.Writeback_ad_OU.Substring(gusersyn.Writeback_ad_OU.IndexOf("DC"));
string userNickName = "";
SqlDataReader nicknamesToAddToAD;
SqlDataReader lostNicknames;
ArrayList nicknameKeys = new ArrayList();
ArrayList sqlkeys = new ArrayList();
ArrayList adPullKeys = new ArrayList();
ArrayList adMailUpdateKeys = new ArrayList();
ArrayList nicknameKeysAndTable = new ArrayList();
ArrayList sqlkeysAndTable = new ArrayList();
ArrayList keywordFields = new ArrayList(); //fields for checking against nickname to see how close it is to the real data
ArrayList selectFields = new ArrayList(); //fields from nicknamesFromGmailTable to bring back
DataTable nicknames = new DataTable();
SqlDataReader sendAsAliases;
// housecleaning
log.addTrn("Clear out tables for use", "Info");
// if (settingsConfig.TempTables == false)
// {
tools.DropTable(nicknamesFromGmailTable, sqlConn, log);
tools.DropTable(loginWithoutNicknamesTable, sqlConn, log);
tools.DropTable(adNicknamesTable, sqlConn, log);
tools.DropTable(sqlNicknamesTable, sqlConn, log);
tools.DropTable(nicknamesToUpdateDBTable, sqlConn, log);
tools.DropTable(nicknamesFilteredForDuplicatesTable, sqlConn, log);
tools.DropTable(nicknamesFromGmailTable2, sqlConn, log);
tools.DropTable(gmailUsersTableWB, sqlConn, log);
// }
// install levenstein if bulid nicknames checked
log.addTrn("Add levenshtein", "Info");
if (gusersyn.Levenshtein == true)
{
string sqlcmd = "CREATE function LEVENSHTEIN( @s varchar(50), @t varchar(50) ) \n --Returns the Levenshtein Distance between strings s1 and s2. \n " +
"--Original developer: Michael Gilleland http://www.merriampark.com/ld.htm \n --Translated to TSQL by Joseph Gama \n returns varchar(50) \n " +
"as \n BEGIN \n DECLARE @d varchar(2500), @LD int, @m int, @n int, @i int, @j int, \n @s_i char(1), @t_j char(1),@cost int \n --Step 1 \n SET @n=LEN(@s) \n" +
" SET @m=LEN(@t) \n SET @d=replicate(CHAR(0),2500) \n If @n = 0 \n BEGIN \n SET @LD = @m \n GOTO done \n END \n If @m = 0 \n BEGIN \n SET @LD = @n \n" +
" GOTO done \n END \n --Step 2 \n SET @i=0 \n WHILE @i<=@n \n BEGIN \n SET @d=STUFF(@d,@i+1,1,CHAR(@i))--d(i, 0) = i \n SET @i=@i+1 \n END \n" +
" SET @i=0 \n WHILE @i<=@m \n BEGIN \n SET @d=STUFF(@d,@i*(@n+1)+1,1,CHAR(@i))--d(0, j) = j \n SET @i=@i+1 \n END \n --goto done \n --Step 3 \n" +
" SET @i=1 \n WHILE @i<=@n \n BEGIN \n SET @s_i=(substring(@s,@i,1)) \n --Step 4 \n SET @j=1 \n WHILE @j<=@m \n BEGIN \n SET @t_j=(substring(@t,@j,1)) \n" +
" --Step 5 \n If @s_i = @t_j \n SET @cost=0 \n ELSE \n SET @cost=1 \n --Step 6 \n SET @d=STUFF(@d,@j*(@n+1)+@i+1,1,CHAR(dbo.MIN3( \n" +
" ASCII(substring(@d,@j*(@n+1)+@i-1+1,1))+1, \n ASCII(substring(@d,(@j-1)*(@n+1)+@i+1,1))+1, \n ASCII(substring(@d,(@j-1)*(@n+1)+@i-1+1,1))+@cost) \n )) \n" +
" SET @j=@j+1 \n END \n SET @i=@i+1 \n END \n --Step 7 \n SET @LD = ASCII(substring(@d,@n*(@m+1)+@m+1,1)) \n done: \n --RETURN @LD \n" +
" --I kept this code that can be used to display the matrix with all calculated values \n --From Query Analyser it provides a nice way to check the algorithm in action \n" +
" -- \n RETURN @LD \n --declare @z varchar(8000) \n --set @z='' \n --SET @i=0 \n --WHILE @i<=@n \n -- BEGIN \n -- SET @j=0 \n -- WHILE @j<=@m \n -- BEGIN \n" +
" -- set @z=@z+CONVERT(char(3),ASCII(substring(@d,@i*(@m+1 )+@j+1 ,1))) \n -- SET @j=@j+1 \n -- END \n -- SET @i=@i+1 \n -- END \n --print dbo.wrap(@z,3*(@n+1)) \n END \n";
sqlComm = new SqlCommand(sqlcmd, sqlConn);
try
{
sqlComm.CommandTimeout = 360;
// sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed to install levenstein SQL command or levenstein already installed" + sqlComm.CommandText.ToString() + " error " + ex + "\n" + ex.StackTrace.ToString(), "Error");
}
// install min3
sqlcmd = "CREATE function MIN3(@a int,@b int,@c int ) \n --Returns the smallest of 3 numbers. \n" +
"returns int \n as \n BEGIN \n declare @temp int \n if (@a < @b) AND (@a < @c) \n select @temp=@a \n else \n if (@b < @a) AND (@b < @c) \n select @temp=@b \n else \n" +
"select @temp=@c \n return @temp \n END";
sqlComm = new SqlCommand(sqlcmd, sqlConn);
try
{
//sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
sqlComm.CommandTimeout = 360;
sqlComm.ExecuteNonQuery();
log.addTrn(sqlComm.CommandText.ToString(), "Query");
}
catch (Exception ex)
{
log.addTrn("Failed to install min3 SQL command or min3 already installed" + sqlComm.CommandText.ToString() + " error " + ex + "\n" + ex.StackTrace.ToString(), "Error");
}
}
if (gusersyn.Writeback_AD_checkbox == true || gusersyn.Writeback_DB_checkbox == true)
{
// DATABASE writeback
// Make preperations to pull all data into seperate tables
// build sql to run to get gmail user nicknames
// execute data pull for SQL nicknames used in writeback to SQL database
log.addTrn("Pull SQL data fro writeback of emails", "Info");
if (gusersyn.Writeback_DB_checkbox == true)
{
if (gusersyn.Writeback_where_clause == "")
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.Writeback_primary_key + ") AS " + gusersyn.Writeback_primary_key +
", RTRIM(" + gusersyn.Writeback_email_field + ") AS " + gusersyn.Writeback_email_field +
" INTO " + sqlNicknamesTable + " FROM " + gusersyn.Writeback_table, sqlConn);
}
else
{
sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.Writeback_primary_key + ") AS " + gusersyn.Writeback_primary_key +
", RTRIM(" + gusersyn.Writeback_email_field + ") AS " + gusersyn.Writeback_email_field +
" INTO " + sqlNicknamesTable + " FROM " + gusersyn.Writeback_table +
" WHERE " + gusersyn.Writeback_where_clause, 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 + "\n" + ex.StackTrace.ToString(), "Error");
}
}
// TABLE pulled by command from gmail
// sqlNicknamesTable:: #sqlNicknamesTable
//---------------------------------------------
// gusersyn.writebackPrimaryKey:: soc_sec xx111111
// gusersyn.writebackEmailField:: gmail first.last@domain
// TABLE pulled by command from AD
// adNicknamesTable:: #adNicknamesTable
//---------------------------------------------
// sAMAccountName xx111111
// givenName first
// middleName middle
// sn last
// mail first.last@domain
// TABLE pulled by command from gamil
// nicknamesFromGmailTable:: #nicknamesFromGmailTable
//---------------------------------------------
// gusersyn.Writeback_primary_key:: soc_sec xx111111
// nicknames.column[1]:: nickname first.last
// nicknames.column[2]:: email first.last@domain
// TABLE pulled by command from gmail
// gmailUsersTable:: #gmailUsersTable
//---------------------------------------------
// gusersyn.User_StuID:: sAMAccountName xx111111
// gusersyn.Fname:: givenname first
// gusersyn.Lname:: sn last
// TABLE generated by sql query from #nicknamesFromGmailTable and #gmailUsersTable
// loginWithoutNicknamesTable:: #loginWithoutNicknamesTable
//---------------------------------------------
// gusersyn.User_StuID:: sAMAccountName xx111111
// gusersyn.Fname:: givenname first
// gusersyn.Lname:: sn last
// TABLE created by query to database
// sqlUsersTable:: #sqlUsersTable
//---------------------------------------------
// gusersyn.User_StuID:: SQL or AD xx111111
// gusersyn.User_Fname:: SQL or AD first
// gusersyn.User_Lname:: SQL or AD middle
// gusersyn.User_Mname:: SQL or AD last
// gusersyn.User_password:: SQL or AD *******
// TABLE created by query to database
// nicknamesToUpdateDBTable:: #nicknamesToUpdateDBTable
//---------------------------------------------
//nicknames.Columns[0].ColumnName:: gusersyn.Writeback_primary_key :: soc_sec xx111111
//nicknames.Columns[2].ColumnName:: email first.last@domain
// were adding the mail field to the pull fields not so we need to repull
// keys to pull from database pull are most likely wrong need to hard code appropriate keys
adPullKeys.Add("sAMAccountName");
adPullKeys.Add("givenName");
adPullKeys.Add("middleName");
adPullKeys.Add("sn");
adPullKeys.Add("mail");
// clear our previous data
log.addTrn("Get AD user data for nickname comparison", "Info");
adUsers.Clear();
adUsers = tools.EnumerateUsersInOUDataTable(gusersyn.User_ad_OU, adPullKeys, adNicknamesTable, SearchScope.OneLevel, log);
tools.Create_Table(adUsers, adNicknamesTable, sqlConn, log);
// get list of users from gmail this may have changed when we ran the update to test to see if anyone is missing nicknames
log.addTrn("Get Gmail user data for user comparison", "Info");
gmailUsers.Clear();
gmailUsers = tools.Get_Gmail_Users(service, gusersyn, gmailUsersTableWB, log);
tools.Create_Table(gmailUsers, gmailUsersTableWB, sqlConn, log);
if (gmailUsers.Rows.Count > 0)
{
// get list of nicknames from gmail
log.addTrn("Get Gmail nickname data for nickname comparison", "Info");
nicknames.Clear();
nicknames = tools.Get_Gmail_Nicknames(service, gusersyn, nicknamesFromGmailTable, log);
tools.Create_Table(nicknames, nicknamesFromGmailTable, sqlConn, log);
// if we did not get any nicknames there will be problems
if (nicknames.Rows.Count > 0)
{
// check which user do not have a an associated nickname with them
// cross reference for null userID's in nickname service.RetrieveAllNicknames table with list of all userlogin userID's from gmail service.RetrieveAllUsers
log.addTrn("Query to find users which do not have a nickname", "Info");
tools.QueryNotExistsIntoNewTable(gmailUsersTableWB, nicknamesFromGmailTable, loginWithoutNicknamesTable, sqlConn, gusersyn.User_StuID, nicknames.Columns[0].ColumnName, log);
// use retrieved list of users without nicknames and check for updates against list of users in main datasource
// we dont want ot add nicknames to people wo aren't in the primary datasource
// use the datatable from the view/table as the primary data source
// this table is generated above during the user account addition and update section
// sqlUsersTable will have AD or SQL no matter which we checked, there was only on variable used
// Pulls back the account infromation from the add/update section for users without a nickname
// loginWithoutNicknamesTable inherits key from from #gmailuserstable during QueryNotExistsIntoNewTable due to only pulling data from the gmailuserstable
log.addTrn("Query to cross reference the primary datasource to find the users who need a nickname", "Info");
lostNicknames = tools.QueryInnerJoin(sqlUsersTable, loginWithoutNicknamesTable, gusersyn.User_StuID, gusersyn.User_StuID, sqlConn, log);
// iterate lostnicknames and create nicknames
try
{
log.addTrn("Creating new nicknames", "Info");
while (lostNicknames.Read())
{
userNickName = tools.GetNewUserNickname(service, lostNicknames[gusersyn.User_StuID.ToString()].ToString(), lostNicknames[gusersyn.User_Fname.ToString()].ToString(), lostNicknames[gusersyn.User_Mname.ToString()].ToString(), lostNicknames[gusersyn.User_Lname.ToString()].ToString(), 0, false);
log.addTrn("Added Gmail user " + lostNicknames[gusersyn.User_StuID.ToString()].ToString() + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain, "Transaction");
}
}
catch (Exception ex)
{
log.addTrn("Issue creating new nicknames datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
lostNicknames.Close();
}
// get list of nicknames from gmail it may have changed in the last update.
log.addTrn("Get nicknames to compare for who need new nicknames", "Info");
nicknames = tools.Get_Gmail_Nicknames(service, gusersyn, nicknamesFromGmailTable2, log);
tools.Create_Table(nicknames, nicknamesFromGmailTable2, sqlConn, log);
// check if we got nicknames or else it will be a problem
if (nicknames.Rows.Count > 0)
{
// create array lists of fields which match for updating
// ID field
nicknameKeys.Add(nicknames.Columns[0].ColumnName);
nicknameKeys.Add(nicknames.Columns[2].ColumnName);
sqlkeys.Add(gusersyn.Writeback_primary_key);
sqlkeys.Add(gusersyn.Writeback_email_field);
// check against list of nicknames in database
if (gusersyn.Writeback_DB_checkbox == true)
{
// check the table to see if the nicknames match the ones we have there can be more than one nickname per user
//select FHC_test2_gmailNicknamesTable.soc_sec, FHC_test2_gmailNicknamesTable.Email
//FROM FHC_test2_gmailNicknamesTable INNER JOIN FHC_test2_sqlNicknamesTable
//ON FHC_test2_gmailNicknamesTable.soc_sec = FHC_test2_sqlNicknamesTable.soc_sec
//where email not in (select email from FHC_test2_gmailnicknamestable)
//Filter out the good nicknames
// gusersyn.User_Fname:: SQL or AD first
// gusersyn.User_Lname:: SQL or AD middle
// gusersyn.User_Mname:: SQL or AD last
keywordFields.Add(gusersyn.User_Lname);
keywordFields.Add(gusersyn.User_Fname);
keywordFields.Add(gusersyn.User_Mname);
selectFields.Add(nicknames.Columns[2].ColumnName);
/* if (gusersyn.Levenshtein == true)
{
tools.SelectNicknamesClosestToActualNameIntoNewTable(nicknamesFromGmailTable, sqlUsersTable, nicknames.Columns[0].ColumnName, gusersyn.User_StuID, nicknamesFilteredForDuplicatesTable, selectFields, nicknames.Columns[1].ColumnName, keywordFields, sqlConn, log);
if (gusersyn.Levenshtein == true)
{
//correct nicknames in Gmail
// tools.setnames(nicknamesFromGmailTable, sqlConn, log);
}
}
else
{
// Filter nicknames for duplicates into new table */
log.addTrn("Query to find most likely nickname", "Info");
tools.SelectNicknamesClosestToActualNameIntoNewTable(nicknamesFromGmailTable2, sqlUsersTable, nicknames.Columns[0].ColumnName, gusersyn.User_StuID, nicknamesFilteredForDuplicatesTable, selectFields, nicknames.Columns[1].ColumnName, keywordFields, sqlConn, log);
// }
// Check filtered nicknames against the sql data to see which emails need updating and put into a table for the next operation
log.addTrn("Query to see if most likely nickname matches the primary datasource", "Info");
tools.CheckEmailUpdateIntoNewTable(nicknames.Columns[2].ColumnName, gusersyn.Writeback_email_field, nicknamesFilteredForDuplicatesTable, sqlNicknamesTable, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknamesToUpdateDBTable, nicknameKeys, sqlkeys, sqlConn, log);
// reset arraylists with just the data fields we don't to be updating the primary key
nicknameKeys.Clear();
sqlkeys.Clear();
nicknameKeys.Add(nicknames.Columns[2].ColumnName);
sqlkeys.Add(gusersyn.Writeback_email_field);
adMailUpdateKeys.Add("mail");
if (gusersyn.Writeback_transfer_email_checkbox == true)
{
//UPDATE a1 SET a1.e_mail = a2.gmail FROM address as a1 INNER JOIN address as a2 ON a1.soc_sec = a2.soc_sec where a2.gmail <> ' '
nicknameKeysAndTable.Add(gusersyn.Writeback_table + "." + gusersyn.Writeback_email_field); // source table and column
sqlkeysAndTable.Add(gusersyn.Writeback_table + "." + gusersyn.Writeback_secondary_email_field); // target table and column
//UNTESTED WILL MOST LIKELY FAIL
log.addTrn("Query to mass move the primary email field to secondary email field in primary datasource", "Info");
tools.Mass_Email_Shift(gusersyn, nicknamesToUpdateDBTable, gusersyn.Writeback_table, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknameKeysAndTable, sqlkeysAndTable, gusersyn.Writeback_where_clause, sqlConn, log);
}
log.addTrn("Query to mass populate the email field with new aliases", "Info");
tools.Mass_Table_update(nicknamesToUpdateDBTable, gusersyn.Writeback_table, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknameKeys, sqlkeys, gusersyn.Writeback_where_clause, sqlConn, log);
}
// reset arraylists for new update check
nicknameKeys.Clear();
nicknameKeys.Add(nicknames.Columns[0].ColumnName);
nicknameKeys.Add(nicknames.Columns[2].ColumnName);
// AD fields hard code due to not giving options in interface
adMailUpdateKeys.Clear();
adMailUpdateKeys.Add("sAMAccountName");
adMailUpdateKeys.Add("mail");
if (gusersyn.Writeback_AD_checkbox == true)
{
// find nicknames missing in AD
log.addTrn("Query to find AD users that need their nickname updated", "Info");
nicknamesToAddToAD = tools.CheckUpdate(nicknamesFilteredForDuplicatesTable, adNicknamesTable, nicknames.Columns[0].ColumnName, adPullKeys[0].ToString(), nicknameKeys, adMailUpdateKeys, sqlConn, log);
//nicknamesToAddToAD = tools.QueryNotExistsAllFields(nicknamesFromGmailTable, adNicknamesTable, sqlConn, nicknames.Columns[0].ColumnName, adPullKeys[0].ToString(), log);
// interate and update mail fields
log.addTrn("Updating nicknames in AD", "Info");
try
{
while (nicknamesToAddToAD.Read())
{
userDN = tools.GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, nicknamesToAddToAD[nicknames.Columns[0].ColumnName].ToString(), dc, log);
tools.SetAttributeValuesSingleString("mail", nicknamesToAddToAD[nicknames.Columns[2].ColumnName.ToString()].ToString(), userDN, log);
}
}
catch (Exception ex)
{
log.addTrn("Issue writing nickname to AD datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
}
nicknamesToAddToAD.Close();
}
// create send as aliases need to get data for users
additionalKeys.Clear();
additionalKeys.Add(nicknamesFilteredForDuplicatesTable + "." + nicknames.Columns[2].ColumnName + ", ");
log.addTrn("Query to find new send as aliases", "Info");
sendAsAliases = tools.QueryInnerJoin(sqlUsersTable, nicknamesFilteredForDuplicatesTable, gusersyn.User_StuID, nicknames.Columns[0].ColumnName, additionalKeys, sqlConn, log);
tools.CreateSendAs(gusersyn, sendAsAliases, nicknames.Columns[2].ColumnName, nicknames.Columns[2].ColumnName, log);
}
}
}
sqlConn.Close();
}