//Converts String attendeesIDList, e.g. "3,4,5" TO ArrayList [userID, firstName, lastName] public ArrayList convertExistingAttendeesIDsStringToArray(String attendeesIDList) { ArrayList attendeesListArray = new ArrayList(); //Return This //Split String to List<int> List <int> userIDList = new List <int>(); userIDList = attendeesIDList.Split(',').Where(x => int.TryParse(x, out _)).Select(int.Parse).ToList(); //For Each Item int lastIndex = userIDList.Count - 1; for (int i = 0; i <= lastIndex; i++) { Console.WriteLine("LIST ID = " + userIDList[i]); //prepare an SQL query to retrieve all the users in DB //*EXCLUDING userID=1 AND userID=2 because that is SYSTEM & ADMIN DataTable myTable = new DataTable(); MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["_db"].ConnectionString); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT userID, firstName, lastName, email FROM z_corona_user WHERE userID<>1 AND userID<>2 AND userID=@userID;"; MySqlCommand cmd = new MySqlCommand(sql, conn); MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd); cmd.Parameters.AddWithValue("@userID", userIDList[i]); myAdapter.Fill(myTable); Console.WriteLine("Table is ready."); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); //convert the retrieved data to events and save them to the list foreach (DataRow row in myTable.Rows) { ManagerEvent user = new ManagerEvent(); user.userID = (int)row["userID"]; //Retrieve userID user.firstName = row["firstName"].ToString(); user.lastName = row["lastName"].ToString(); user.email = row["email"].ToString(); attendeesListArray.Add(user); } } //TestPrint [WORKS!] /* * for (int i = 0; i <= lastIndex; i++) * { * Console.WriteLine("USER: "******" " + ((ManagerEvent)attendeesListArray[i]).getFirstName() +" " + ((ManagerEvent)attendeesListArray[i]).getLastName()); * }*/ return(attendeesListArray); }
//Function to return an eventList of ALL SELECTED ATTENDEES ON THE SELECTED DATE public static ArrayList getAttendeeEventList(string dateStringStart, int userID) { //Console.WriteLine("dateStart=" + dateStringStart); //Console.WriteLine("dateEnd=" + dateStringEnd); ArrayList attendeeEventList = new ArrayList(); //a list to save the events //prepare an SQL query to retrieve all the events on the same, specified date DataTable myTable = new DataTable(); MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["_db"].ConnectionString); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT * FROM z_corona_event WHERE (date=@myDateStart AND userID=@userID) OR (date=@myDateStart AND isManagerEvent=1) ORDER BY date ASC, startTime ASC"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@myDateStart", dateStringStart); //cmd.Parameters.AddWithValue("@myDateEnd", dateStringEnd); cmd.Parameters.AddWithValue("@userID", userID); //cmd.Parameters.AddWithValue("@isManagerEvent", 1); MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd); myAdapter.Fill(myTable); Console.WriteLine("Table is ready."); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); //convert the retrieved data to events and save them to the list foreach (DataRow row in myTable.Rows) { ManagerEvent newEvent = new ManagerEvent(); newEvent.eventID = (int)row["eventID"]; //Retrieve eventID to use for editing events... DB side is auto-increment... newEvent.userID = (int)row["userID"]; //Retrieve userID = for group project //newEvent.title = row["title"].ToString(); //newEvent.date = (DateTime)row["date"]; //works newEvent.startTime = (TimeSpan)row["startTime"]; newEvent.endTime = (TimeSpan)row["endTime"]; //newEvent.location = row["location"].ToString(); //newEvent.description = row["description"].ToString(); //newEvent.reminderTime = (TimeSpan)row["reminderTime"]; newEvent.isManagerEvent = (int)row["isManagerEvent"]; //newEvent.attendees = row["attendees"].ToString(); attendeeEventList.Add(newEvent); } return(attendeeEventList); }
//Function gets userID, fistName, lastName of ALL USERS (EXPT 1 AND 2 | SYSTEM & ADMIN) | [userID, firstName, lastName] public ArrayList getUsersList() { ArrayList usersList = new ArrayList(); //prepare an SQL query to retrieve all the users in DB //*EXCLUDING userID=1 AND userID=2 because that is SYSTEM & ADMIN DataTable myTable = new DataTable(); MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["_db"].ConnectionString); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT userID, firstName, lastName, email FROM z_corona_user WHERE userID<>1 AND userID<>2 ORDER BY lastName ASC, firstName ASC, userID ASC;"; MySqlCommand cmd = new MySqlCommand(sql, conn); MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd); myAdapter.Fill(myTable); Console.WriteLine("Table is ready."); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); //convert the retrieved data to events and save them to the list foreach (DataRow row in myTable.Rows) { ManagerEvent user = new ManagerEvent(); user.userID = (int)row["userID"]; //Retrieve userID = for group project user.firstName = row["firstName"].ToString(); user.lastName = row["lastName"].ToString(); user.email = row["email"].ToString(); usersList.Add(user); } return(usersList); //return the event list }