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