//a method to retreive a list of all the TA duties for a specific day at a specific location
protected List <string> get_today_duties(int userID, int locationID, string week, string day)
{
bool enableBtn = false;
List <string> shifts_list = new List <string>();
db_connection db_obj = new db_connection();
db_obj.open();
//string query = "SELECT attendance.shift_id, shift_location.start_time, shift_location.end_time FROM attendance INNER JOIN shift_location ON attendance.shift_id = shift_location.shift_id AND shift_location.day=@day AND shift_location.location_id=@location WHERE user_id=@user AND week_start_date=@week AND day=@day;";
string query = "SELECT attendance.shift_id, shift_location.start_time, shift_location.end_time FROM attendance INNER JOIN shift_location ON attendance.shift_id = shift_location.shift_id AND shift_location.day = @day AND shift_location.location_id = @location WHERE user_id =@user AND week_start_date =@week AND attendance.day =@day";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@user", userID);
cmd.Parameters.AddWithValue("@week", week);
cmd.Parameters.AddWithValue("@location", locationID);
cmd.Parameters.AddWithValue("@day", day);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string shift_name = commonMethods.getShiftName(int.Parse(reader["shift_id"].ToString()));
string sTime = reader["start_time"].ToString();
TimeSpan timeDifference = TimeSpan.Parse(sTime).Subtract(TimeSpan.Parse(now));
int timeDifferenceInMinutes = timeDifference.Hours * 60 + timeDifference.Minutes;
if (timeDifferenceInMinutes > 0)
{
string shift = shift_name + " Start Time: " + reader["start_time"].ToString() + " End Time: " + reader["end_time"] + " (" + timeDifferenceInMinutes + " minutes left)";
shifts_list.Add(shift);
if (timeDifferenceInMinutes < getSignInWindow())
{
enableBtn = true;
}
}
else
{
string shift = shift_name + " Start Time: " + reader["start_time"].ToString() + " End Time: " + reader["end_time"];
shifts_list.Add(shift);
}
}
if (enableBtn == true)
{
//dutySignInBtn.Enabled = true;
}
else
{
// dutySignInBtn.Enabled = false;
// msg.Text = "You can only sign in " + getSignInWindow().ToString() + " minutes before your duty, for more info please contact the scheduler.";
}
db_obj.close();
return(shifts_list);
}
protected void populateCurrentLocationsList()
{
string query = "SELECT * FROM location";
SqlDataReader reader1 = performQuery(query).ExecuteReader();
/*Mudassar Ahmed Khan. 2016. Bind (Populate) ASP.Net DropDownList using DataTable (DataSet) in C# and VB.Net.
* [ONLINE] Available at: http://www.aspsnippets.com/Articles/Bind-Populate-ASPNet-DropDownList-using-DataTable-DataSet-in-C-and-VBNet.aspx.
* [Accessed 09 September 2017].*/
newitemLocation.DataSource = reader1;
newitemLocation.DataTextField = "name";
newitemLocation.DataValueField = "location_id";
newitemLocation.DataBind();
newitemLocation.Items.Insert(0, new ListItem("SELECT LOCATION", "N/A"));
reader1.Close();
dbRef.close();
}
public List <item> getitems()
{
List <item> itemsList = new List <item>();
//loop through list and populate data
/*
* c# - How can I loop through a List and grab each item? - Stack Overflow. 2016. c# - How can I loop through a List and grab each item? - Stack Overflow.
* [ONLINE] Available at: http://stackoverflow.com/questions/18863187/how-can-i-loop-through-a-listt-and-grab-each-item. [Accessed 08 September 2017].
*/
if (Session["id"] != null)
{
string current_user_id = Session["id"].ToString();
int int_user_id = Int16.Parse(current_user_id);
string query = "SELECT item_id,venue,item_name,description,location_id,available_time,date,status,type,_user.user_name as posted_by,unit_price,img FROM item_services inner join _user on _user.user_id=item_services.added_by WHERE item_services.added_by=" + int_user_id + "";
SqlDataReader reader = performQuery(query).ExecuteReader();
while (reader.Read())
{
item item = new item();
item.id = int.Parse(reader["item_id"].ToString()); item.venue = reader["venue"].ToString();
item.ITEM = reader["item_name"].ToString(); item.description = reader["description"].ToString();
item.location_id = int.Parse(reader["location_id"].ToString()); item.available = reader["available_time"].ToString();
item.date = reader["date"].ToString(); item.status = char.Parse(reader["status"].ToString());
item.type = char.Parse(reader["type"].ToString()); item.posted_by = reader["posted_by"].ToString();
item.img = reader["img"].ToString(); item.unit_price = float.Parse(reader["unit_price"].ToString());
/*
* c# - Implementation of OOP for retrieving list of objects from database - Code Review Stack Exchange.
* . c# - Implementation of OOP for retrieving list of objects from database - Code Review Stack Exchange. [ONLINE] Available at: http://codereview.stackexchange.com/questions/33810/implementation-of-oop-for-retrieving-list-of-objects-from-database.
* [Accessed 08 September 2017].
*/
itemsList.Add(item);
}
dbRef.close();
}
return(itemsList);
}
public void addNewUser(_user _user)
{
string time_now = DateTime.Now.ToString("yyyy-MM-dd");
string dob = (_user.dob).ToString("yyyy-MM-dd");
string query = "INSERT INTO _user (user_name,user_code,private_email,searching_email,password,dob,gender,nationality,address,member_date,contact_number,active,position_id,credits) VALUES ('" + _user.user_name + "', '" + _user.user_code + "', '" + _user.private_email + "', '" + _user.searching_email + "', '" + _user.password + "', '" + dob + "', '" + _user.gender + "', '" + _user.nationality + "', '" + _user.address + "', '" + time_now + "','" + _user.contact_number + "',1," + _user.position_id + ",0)";
performQuery(query).ExecuteNonQuery();
dbRef.close();
System.Web.UI.ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AlertBox", "alert('User has been Added');", true);
}
//delete duty fucntion
public void deleteDuty(int dutyID)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "UPDATE duty_shift_location set active='0' WHERE duty_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@id", dutyID);
cmd.ExecuteNonQuery();
dbObj.close();
}
//add new shift function
public void addShift(string shiftName)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "INSERT INTO shift (name) VALUES (@name);";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@name", shiftName);
cmd.ExecuteNonQuery();
db_obj.close();
}
//delete shift fucntion
public void deleteShift(int shiftID)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "DELETE FROM shift WHERE shift_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@id", shiftID);
cmd.ExecuteNonQuery();
dbObj.close();
}
//delete location fucntion
public void deleteLocation(int locationID)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "DELETE FROM location WHERE location_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@id", locationID);
cmd.ExecuteNonQuery();
dbObj.close();
}
//add new duty function
public void addDuty(string dutyTitle, string dutyDescription)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "INSERT INTO duty (title, description) VALUES (@title, @description);";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@title", dutyTitle);
cmd.Parameters.AddWithValue("@description", dutyDescription);
cmd.ExecuteNonQuery();
db_obj.close();
}
//add new location fucntion
public void addLocation(string locationName, string locationAddress)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "INSERT INTO location (name, address) VALUES (@name, @address);";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@name", locationName);
cmd.Parameters.AddWithValue("@address", locationAddress);
cmd.ExecuteNonQuery();
db_obj.close();
}
public void deleteUser(int id)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "UPDATE user SET active=0 WHERE user_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
dbObj.close();
}
public static void solveProblem(int problemID)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "UPDATE problem SET status='s' WHERE problem_id=@problemID";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@problemID", problemID);
cmd.ExecuteNonQuery();
db_obj.close();
}
//method to help in getting the latest order in order to continue ordering when duties are added in draft and final.
protected int getTheLatestOrder(string week)
{
int orderValue;
string query = "SELECT DISTINCT request_order FROM duty_request WHERE week_start_date=@weekDate ORDER BY request_order DESC LIMIT 1;";
db_connection db_object = new db_connection();
db_object.open();
MySqlCommand cmd = new MySqlCommand(query, db_object.connection);
cmd.Parameters.AddWithValue("@weekDate", week);
orderValue = int.Parse(cmd.ExecuteScalar().ToString());
db_object.close();
return(orderValue);
}
//this method to get the number of minutes window for TAs to sign out
protected int getSignOutWindow()
{
int minutes_num = 0;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT duty_signOutWindow FROM duty_request_rule WHERE rule_id=1;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
minutes_num = int.Parse(cmd.ExecuteScalar().ToString());
db_obj.close();
return(minutes_num);
}
//edit shift function
public void modifyShift(int shiftID, string shiftName)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "UPDATE shfit set name=@newName WHERE shift_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@newName", shiftName);
cmd.Parameters.AddWithValue("@id", shiftID);
cmd.ExecuteNonQuery();
dbObj.close();
}
protected void updateCurrentShiftBtn_Click(object sender, EventArgs e)
{
try
{
if ((currentLocationsList.SelectedIndex != 0 && currentShiftsDaysList.SelectedIndex != 0) && currentDaysList.SelectedIndex != 0)
{
if (isStartTimeBeforeEndTime(currentStartTime.Text, currentEndTime.Text) == true)
{
if (isThereTimeInterference(int.Parse(currentLocationsList.SelectedValue), currentDaysList.SelectedValue, currentStartTime.Text) != true)
{
int locationID = int.Parse(currentLocationsList.SelectedValue);
int shiftID = int.Parse(currentShiftsDaysList.SelectedValue);
string day = currentDaysList.SelectedValue;
string newStart = currentStartTime.Text;
string newEnd = currentEndTime.Text;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "UPDATE shift_location SET start_time=@newStart, end_time=@newEnd WHERE location_id=@location AND day =@day AND shift_id=@shift";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@location", locationID);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@shift", shiftID);
cmd.Parameters.AddWithValue("@newStart", newStart);
cmd.Parameters.AddWithValue("@newEnd", newEnd);
cmd.ExecuteNonQuery();
db_obj.close();
}
else
{
msg1.Text = "Shift cannot be updated, there is time intereference with another shift";
}
}
else
{
msg1.Text = "Start time must be before end time";
}
}
else
{
msg1.Text = "Please specify all the required details";
}
}catch (Exception ex)
{
msg1.Text = "Shift could not be updated, time input is not valid";
}
}
//edit location function
public void modifyLocation(int locationID, string locationName, string locationAddress)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "UPDATE location set name=@newName, address=@newAddress WHERE location_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@newName", locationName);
cmd.Parameters.AddWithValue("@newAddress", locationAddress);
cmd.Parameters.AddWithValue("@id", locationID);
cmd.ExecuteNonQuery();
dbObj.close();
}
public void changePassword(int id, string newPassword)
{
db_connection db = new db_connection();
db.open();
string query = "UPDATE user SET password=@newPassword WHERE user_id=@userID;";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
cmd.Parameters.AddWithValue("@newPassword", newPassword);
cmd.Parameters.AddWithValue("@userID", id);
cmd.ExecuteNonQuery();
db.close();
}
protected void deleteShiftRelatedDuties(int locationID, string day, int shiftID)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "DELETE FROM duty_shift_location WHERE location_id=@location AND day=@day AND shift_id=@shift";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@location", locationID);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@shift", shiftID);
cmd.ExecuteNonQuery();
db_obj.close();
}
//edit duty function
public void modifyDuty(int dutyID, string dutyTitle, string dutyDescription)
{
db_connection dbObj = new db_connection();
dbObj.open();
string query = "UPDATE duty set title=@newTitle, description=@newDescription WHERE duty_id=@id;";
MySqlCommand cmd = new MySqlCommand(query, dbObj.connection);
cmd.Parameters.AddWithValue("@newTitle", dutyTitle);
cmd.Parameters.AddWithValue("@newDescription", dutyDescription);
cmd.Parameters.AddWithValue("@id", dutyID);
cmd.ExecuteNonQuery();
dbObj.close();
}
//to return location name instead of its ID in the roster
protected string getLocationName(int locationID)
{
string locationName;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT name FROM location WHERE location_id=@lid";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@lid", locationID);
locationName = cmd.ExecuteScalar().ToString();
db_obj.close();
return(locationName);
}
protected string getUserName(int userID)
{
string name;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_name FROM user WHERE user_id=@lid";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@lid", userID);
name = cmd.ExecuteScalar().ToString();
db_obj.close();
return(name);
}
protected string getShiftName(int shiftID)
{
string shiftName;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT name FROM shift WHERE shift_id=@lid";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@lid", shiftID);
shiftName = cmd.ExecuteScalar().ToString();
db_obj.close();
return(shiftName);
}
//populate the months drop down
protected void populateMonthsList(string year)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT DISTINCT MONTHNAME(week_start_date) AS 'Month' FROM attendance WHERE DATE_FORMAT(attendance.week_start_date, '%Y') = @year";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@year", year);
MySqlDataReader reader = cmd.ExecuteReader();
MonthsList.DataSource = reader;
MonthsList.DataTextField = "Month";
MonthsList.DataValueField = "Month";
MonthsList.DataBind();
db_obj.close();
}
//populate the years drop down
protected void populateYearsList()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT DISTINCT YEAR(week_start_date) AS 'Year' FROM attendance;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
yearList.DataSource = reader;
yearList.DataTextField = "Year";
yearList.DataValueField = "Year";
yearList.DataBind();
yearList.Items.Insert(0, new System.Web.UI.WebControls.ListItem("SELECT", "N/A"));
db_obj.close();
}
protected void populateLocation()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT location_id, name FROM location";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
locationsList.DataSource = reader;
locationsList.DataTextField = "name";
locationsList.DataValueField = "location_id";
locationsList.DataBind();
locationsList.Items.Insert(0, new System.Web.UI.WebControls.ListItem("SELECT LOCATION", "0"));
db_obj.close();
}
//to return duty name instead of its ID in the roster
public static string getDutyName(int dutyID)
{
string dutyName;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT title FROM duty WHERE duty_id=@id";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", dutyID);
dutyName = cmd.ExecuteScalar().ToString();
db_obj.close();
return(dutyName);
}
public static string getTAName(int id)
{
string TA_name;
string query = "SELECT user_name FROM user WHERE user_id=@id";
db_connection db_object = new db_connection();
db_object.open();
MySqlCommand cmd = new MySqlCommand(query, db_object.connection);
cmd.Parameters.AddWithValue("@id", id);
TA_name = cmd.ExecuteScalar().ToString();
db_object.close();
return(TA_name);
}
//populate positions list box with values from DB
protected void populateListBox()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM position";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
userPositionDropList.DataSource = reader;
userPositionDropList.DataTextField = "title";
userPositionDropList.DataValueField = "position_id";
userPositionDropList.DataBind();
reader.Close();
db_obj.close();
}
//populate the drop down menu with usernames
protected void populateUserList()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_id, user_name FROM user WHERE active=1";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
userDropList.DataSource = reader;
userDropList.DataTextField = "user_name";
userDropList.DataValueField = "user_id";
userDropList.DataBind();
userDropList.Items.RemoveAt(0);
userDropList.Items.Insert(0, new ListItem("SELECT", "N/A"));
db_obj.close();
}