//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
}
