public static problem getProblem(int problemID)
{
problem prob = new problem();
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM problem WHERE problem_id=@problem_id";
MySqlCommand command = new MySqlCommand(query, db_obj.connection);
command.Parameters.AddWithValue("@problem_id", problemID);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
prob.id = int.Parse(reader["problem_id"].ToString());
prob.venue = reader["venue"].ToString();
prob.pc = reader["pc"].ToString();
prob.description = reader["description"].ToString();
prob.location_id = int.Parse(reader["location_id"].ToString());
prob.shift = reader["shift"].ToString();
prob.date = reader["date"].ToString();
prob.status = char.Parse(reader["status"].ToString());
prob.type = char.Parse(reader["type"].ToString());
prob.added_by = int.Parse(reader["added_by"].ToString());
}
db_obj.close();
return(prob);
}
//Function to get function & position IDs based on grid view row and update the joint table access value
public void updateFunctionAccess(string functionTitle, string positionTitle, int accessValue)
{
db_connection db_obj = new db_connection();
db_obj.open();
//get function ID from db
string getFunctionIDquery = " SELECT function_id FROM function WHERE title = @function_title;";
MySqlCommand cmd1 = new MySqlCommand(getFunctionIDquery, db_obj.connection);
cmd1.Parameters.AddWithValue("@function_title", functionTitle);
int functionID = (int)cmd1.ExecuteScalar();
//get position ID from db
string getPositionIDquery = " SELECT position_id FROM position WHERE title = @position_title;";
MySqlCommand cmd2 = new MySqlCommand(getPositionIDquery, db_obj.connection);
cmd2.Parameters.AddWithValue("@position_title", positionTitle);
int positionID = (int)cmd2.ExecuteScalar();
//update function_position table
string giveAccessToFunctionQuery = "UPDATE function_position SET given=@access WHERE function_id =@FID AND position_id=@PID;";
MySqlCommand cmd3 = new MySqlCommand(giveAccessToFunctionQuery, db_obj.connection);
cmd3.Parameters.AddWithValue("@FID", functionID);
cmd3.Parameters.AddWithValue("@PID", positionID);
cmd3.Parameters.AddWithValue("@access", accessValue);
cmd3.ExecuteNonQuery();
db_obj.close();
}
protected void displayAllUser()
{
db_connection db_obj = new db_connection();
db_obj.open();
//**********************
userReport.ProcessingMode = ProcessingMode.Local;
userReport.LocalReport.ReportPath = Server.MapPath("~/userReport.rdlc");
userInfoDataSet userDataset = new userInfoDataSet();
String s = "SELECT [user_id],[user_name],[user_code],[private_email],[searching_email],[password],[dob],[gender],[nationality],[address],[achievements],[member_date],[contact_number],[active],[position_id],[credits] FROM [_user] ;";
SqlDataAdapter da = new SqlDataAdapter(s, db_obj.connection);
DataTable dt = new DataTable();
da.Fill(userDataset, userDataset.Tables[0].TableName);
dt = userDataset.Tables[0];
// //YouTube. 2016. crystal report,how to create crystal report in C# net using Dataset,visual studio - YouTube. [ONLINE] Available at: https://www.youtube.com/watch?v=uRICO1sZ3Hc. [Accessed 10 Nov 2017].
ReportDataSource ReportDataSource = new ReportDataSource("_user", userDataset.Tables[0]);
this.userReport.LocalReport.DataSources.Clear();
this.userReport.LocalReport.DataSources.Add(ReportDataSource);
this.userReport.LocalReport.Refresh();
Generatereport(dt);
db_obj.close();
}
protected void populateLocationsList()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM location";
SqlCommand cmd1 = new SqlCommand(query, db_obj.connection);
SqlDataReader reader1 = cmd1.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].*/
txt_location.DataSource = reader1;
txt_location.DataTextField = "name";
txt_location.DataValueField = "location_id";
txt_location.DataBind();
txt_location.Items.Insert(0, new ListItem("Select a location", "Null"));
reader1.Close();
SqlDataReader reader2 = cmd1.ExecuteReader();
txt_newLocation.DataSource = reader2;
txt_newLocation.DataTextField = "name";
txt_newLocation.DataValueField = "location_id";
txt_newLocation.DataBind();
txt_newLocation.Items.Insert(0, new ListItem("Select a location", "Null"));
reader2.Close();
db_obj.close();
}
protected void displayItemByQuery(String s)
{
db_connection db_obj = new db_connection();
db_obj.open();
//**********************
itemReport.ProcessingMode = ProcessingMode.Local;
itemReport.LocalReport.ReportPath = Server.MapPath("~/itemReport.rdlc");
itemDataSet item_Dataset = new itemDataSet();
SqlDataAdapter da = new SqlDataAdapter(s, db_obj.connection);
DataTable dt = new DataTable();
da.Fill(item_Dataset, item_Dataset.Tables[0].TableName);
dt = item_Dataset.Tables[0];
// //YouTube. 2016. crystal report,how to create crystal report in C# net using Dataset,visual studio - YouTube. [ONLINE] Available at: https://www.youtube.com/watch?v=uRICO1sZ3Hc. [Accessed 10 Nov 2017].
ReportDataSource ReportDataSource = new ReportDataSource("item_services", item_Dataset.Tables[0]);
this.itemReport.LocalReport.DataSources.Clear();
this.itemReport.LocalReport.DataSources.Add(ReportDataSource);
this.itemReport.LocalReport.Refresh();
Generatereport(dt);
//***
// this.userReport.ReportRefresh();
//************************
db_obj.close();
}
/*
* C# function parameter Array vs List - Stack Overflow. 2016.
* [ONLINE] Available at: http://stackoverflow.com/questions/29896045/c-sharp-function-parameter-array-vs-list. [Accessed 23 September 2017].
*/
public void editProfile(int id, object[] newInfo)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "UPDATE _user SET private_email = @newPrivateEmail, address=@newAddress, contact_number=@newNumber WHERE user_id = @userID;";
/*
* MSDN.
* SqlParameterCollection.AddWithValue Method (String, Object) (System.Data.SqlClient). 2016.
* [ONLINE] Available at: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx. [Accessed 05 September 2017].
*/
SqlCommand cmd = new SqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@newPrivateEmail", newInfo[0]);
cmd.Parameters.AddWithValue("@newAddress", newInfo[1]);
cmd.Parameters.AddWithValue("@newNumber", newInfo[2]);
cmd.Parameters.AddWithValue("@userID", id);
cmd.ExecuteNonQuery();
db_obj.close();
}
public static string[] GetPC(string prefix)
{
List <string> PCS = new List <string>();
db_connection db_obj1 = new db_connection();
using (db_obj1.connection)
{
string sql = "select fypdb.problem.pc from fypdb.problem where fypdb.problem.pc like '" + prefix + "%'";
// conn.ConnectionString = ConfigurationManager.ConnectionStrings[db_obj1.getConn()].ConnectionString;
using (MySqlCommand cmd = new MySqlCommand())
{
sql = "select fypdb.problem.pc from fypdb.problem where fypdb.problem.pc like '" + prefix + "%'";
cmd.CommandText = sql;
cmd.Connection = db_obj1.connection;
db_obj1.open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
PCS.Add(string.Format("{0}-{1}", sdr["pc"]));
}
}
db_obj1.close();
}
}
return(PCS.ToArray());
}
protected void edit_duty_Click(object sender, EventArgs e)
{
int selected_duty_id = Int32.Parse(duties.SelectedValue);
try
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM duty WHERE duty_id =@id ;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", selected_duty_id);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
duty_title.Text = reader["title"].ToString();
duty_desc.Text = reader["description"].ToString();
reader.Close();
db_obj.close();
save_duty_btn.Text = "Save Changes";
cancel_duty_btn.Visible = true;
}
catch (Exception ex)
{
duty_msg.Text = "Something went wrong, error: " + ex.Message;
}
}
//a method to test if a shift is full.
public static bool isFull(int shift_id, int location_id, string week, string day)
{
bool result = false;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT COUNT(user_id) FROM duty_request WHERE shift_id=@shift AND location_id=@location AND week_start_date=@week AND requestForDay =@day AND request_status IN (1,2);";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@location", location_id);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@week", week);
cmd.Parameters.AddWithValue("@shift", shift_id);
int takenSlots_count = int.Parse(cmd.ExecuteScalar().ToString());
int requiredNumber = commonMethods.getRosterDutiesCells(day, location_id, shift_id).Count;
if (takenSlots_count < requiredNumber)
{
result = false;
}
else
{
result = true;
}
return(result);
}
//retrieve values of the selected item to edit and populate the form
protected void edit_btn_Click(object sender, EventArgs e)
{
int selected_position_id = Int32.Parse(position_list.SelectedValue);
try
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM position WHERE position_id =@id ;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", selected_position_id);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
title.Text = reader["title"].ToString();
pay_rate.Text = reader["pay_rate"].ToString();
quota.Text = reader["monthly_quota_hours"].ToString();
reader.Close();
db_obj.close();
save_btn.Text = "Save Changes";
cancel_btn.Visible = true;
}
catch (Exception ex)
{
msg.Text = "Something went wrong, error: " + ex.Message;
}
}
/*** Edit button action for all the lists : retrieve values of the required item to be edited ***/
protected void edit_location_Click(object sender, EventArgs e)
{
int selected_location_id = Int32.Parse(locations.SelectedValue);
try
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM location WHERE location_id =@id ;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", selected_location_id);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
location_name.Text = reader["name"].ToString();
location_address.Text = reader["address"].ToString();
reader.Close();
db_obj.close();
save_location_btn.Text = "Save Changes";
cancel_location_btn.Visible = true;
}
catch (Exception ex)
{
location_msg.Text = "Something went wrong, error: " + ex.Message;
}
}
protected void decativateCurrentShiftBtn_Click(object sender, EventArgs e)
{
try
{
int locationID = int.Parse(currentLocationsList.SelectedValue);
int shiftID = int.Parse(currentShiftsDaysList.SelectedValue);
string day = currentDaysList.SelectedValue;
deleteShiftRelatedDuties(locationID, day, shiftID);
db_connection db_obj = new db_connection();
db_obj.open();
string query = "DELETE FROM 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();
msg1.Text = "DONE!";
Response.Redirect(Request.RawUrl);
}
catch (Exception ex)
{
msg1.Text = "There was something wrong!" + ex.Message;
}
}
protected bool isThereTimeInterference(int locationID, string day, string timeToCheck)
{
bool result = false;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT end_time FROM shift_location WHERE location_id=@location AND day =@day";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@location", locationID);
cmd.Parameters.AddWithValue("@day", day);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (TimeSpan.Parse(timeToCheck) < TimeSpan.Parse(reader["end_time"].ToString()))
{
return(result = true);
}
}
reader.Close();
db_obj.close();
return(result);
}
protected void populateCurrentLocationsList()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT * FROM location";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
location_list.DataSource = reader;
location_list.DataTextField = "name";
location_list.DataValueField = "location_id";
location_list.DataBind();
location_list.Items.Insert(0, new ListItem("SELECT LOCATION", "N/A"));
reader.Close();
MySqlCommand cmd1 = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader1 = cmd1.ExecuteReader();
newProblemLocation.DataSource = reader1;
newProblemLocation.DataTextField = "name";
newProblemLocation.DataValueField = "location_id";
newProblemLocation.DataBind();
newProblemLocation.Items.Insert(0, new ListItem("SELECT LOCATION", "N/A"));
reader1.Close();
db_obj.close();
}
public object[] viewProfile(int TA_id)
{
object[] TA_profile_info = new object[12];
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_name, user_tp, user_ta_code, private_email, ta_email, contact_number, dob, intake_code, gpa, address, achievements, warning_count FROM user WHERE user_id = @userID; ";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@userID", TA_id);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TA_profile_info[0] = reader["user_name"];
TA_profile_info[1] = reader["user_tp"];
TA_profile_info[2] = reader["user_ta_code"];
TA_profile_info[3] = reader["private_email"];
TA_profile_info[4] = reader["contact_number"];
TA_profile_info[5] = reader["ta_email"];
TA_profile_info[6] = reader["dob"];
TA_profile_info[7] = reader["intake_code"];
TA_profile_info[8] = reader["gpa"];
TA_profile_info[9] = reader["address"];
TA_profile_info[10] = reader["achievements"];
TA_profile_info[11] = reader["warning_count"];
}
db_obj.close();
return(TA_profile_info);
}
public static bool isScheduler(int positionID)
{
string result;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT given FROM function_position WHERE function_id=8 AND position_id=@positionID";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@positionID", positionID);
result = cmd.ExecuteScalar().ToString();
db_obj.close();
if (int.Parse(result) == 1)
{
return(true);
}
else
{
return(false);
}
}
public void requestDuty(int id, object[] requestDetails)
{
db_connection db_obj = new db_connection();
db_obj.open();
string weekDate = requestDetails[0].ToString();
string query = "INSERT INTO duty_request (user_id, week_start_date, shift_id, location_id, requestForDay, request_time, request_date, request_for_date, request_status, request_type, request_remark, request_order) VALUES (@id, @week, @shift, @location, @forDay, CURTIME(), @date, @forDate, @status, @type, @remark, @order)";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@week", weekDate);
cmd.Parameters.AddWithValue("@shift", requestDetails[1]);
cmd.Parameters.AddWithValue("@location", requestDetails[2]);
cmd.Parameters.AddWithValue("@forDay", requestDetails[3]);
//cmd.Parameters.AddWithValue("@time", requestDetails[4]);
cmd.Parameters.AddWithValue("@date", requestDetails[5]);
cmd.Parameters.AddWithValue("@forDate", requestDetails[6]);
cmd.Parameters.AddWithValue("@status", requestDetails[7]);
cmd.Parameters.AddWithValue("@type", requestDetails[8]);
cmd.Parameters.AddWithValue("@remark", requestDetails[9]);
cmd.Parameters.AddWithValue("@order", requestDetails[10]);
cmd.ExecuteNonQuery();
db_obj.close();
}
//to return shift Timing and name at the location on a specific day instead of its ID in the roster
protected Tuple <string, string> getShiftDetails(int shift_ID, string day, int locationID)
{
string name = "", timing = "";
//Dictionary<string, string> shiftDetails = new Dictionary<string, string>();
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT shift.name, CONCAT(TIME_FORMAT(start_time, '%H:%i'),'-', TIME_FORMAT(end_time, '%H:%i')) AS TIMING FROM shift_location INNER JOIN shift ON shift_location.shift_id = shift.shift_id WHERE shift_location.shift_id=@sid AND day=@day AND location_id=@lid";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@sid", shift_ID);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@lid", locationID);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
name = reader["name"].ToString();
timing = reader["TIMING"].ToString();
}
db_obj.close();
return(Tuple.Create(name, timing));
}
public Boolean login(string tp, string password)
{
bool i = false;
db_connection db = new db_connection();
db.open();
string query = "SELECT user_id, user_name, user_tp, password, active, position_id FROM user";
MySqlCommand cmd = new MySqlCommand(query, db.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader["user_tp"].ToString() == tp && reader["password"].ToString() == password)
{
if (int.Parse(reader["active"].ToString()) == 1)
{
System.Web.HttpContext.Current.Session["id"] = reader["user_id"];
System.Web.HttpContext.Current.Session["username"] = reader["user_name"];
System.Web.HttpContext.Current.Session["tp"] = reader["user_tp"];
System.Web.HttpContext.Current.Session["position"] = reader["position_id"];
i = true;
db.close();
break;
}
else
{
return(i);
}
}
}
return(i);
}
//to disable the shifts which have already been requested by the user
protected List <string> disableRequestedShift(int userID, string weekDate)
{
List <string> requestedShifts = new List <string>();
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT shift_id, location_id, requestForDay FROM duty_request WHERE user_id=@id AND week_start_date=@weekDate";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", userID);
cmd.Parameters.AddWithValue("@weekDate", weekDate);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string requested_shift = reader["shift_id"].ToString() + "," + reader["location_id"].ToString() + "," + reader["requestForDay"].ToString();
requestedShifts.Add(requested_shift);
}
db_obj.close();
return(requestedShifts);
}
//populate table from DB
protected void populateReportTable(int locationID, string month, string year)
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user.user_name AS 'Technical Assistant', concat(login_date, ', ', attendance.login_time) AS 'Check In', " +
"concat(attendance.logout_date, ', ', attendance.logout_time) AS 'Check Out', " +
" FORMAT(TIME_TO_SEC(TIMEDIFF(attendance.logout_time, attendance.login_time)) / 3600, 2) AS 'Workig Hours'," +
"position.pay_rate AS 'Rate', FORMAT(position.pay_rate * TIME_TO_SEC(TIMEDIFF(attendance.logout_time, attendance.login_time)) / 3600, 2) AS 'Total'," +
" location.name AS 'Location', attendance.system_remark FROM attendance JOIN user ON attendance.user_id = user.user_id JOIN location ON attendance.location_id = " +
" location.location_id JOIN position ON position.position_id = user.position_id WHERE attendance.location_id=@location AND DATE_FORMAT(attendance.week_start_date, '%M-%Y') = " +
" '" + month + "-" + year + "' UNION ALL SELECT 'TOTAL AMOUNT', null, null, null , null, CAST(SUM(FORMAT(position.pay_rate * TIME_TO_SEC(TIMEDIFF(attendance.logout_time, " +
" attendance.login_time))/3600, 2)) AS DECIMAL(5,2)), null, null FROM attendance JOIN user ON attendance.user_id = user.user_id JOIN position ON " +
" user.position_id = position.position_id WHERE attendance.location_id=@location AND DATE_FORMAT(attendance.week_start_date, '%M-%Y') = '" + month + "-" + year + "';";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@location", locationID);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
reportGrid.DataSource = dt;
reportGrid.CssClass = "table";
reportGrid.DataBind();
}
protected List <int> getFinal(int shift, int location, string day, string weekDate, int requiredNumber)
{
List <int> TAs = new List <int>();
string query = "SELECT user_id FROM duty_request WHERE shift_id=@shift AND location_id=@location AND requestForDay=@day AND week_start_date=@week AND request_status in (2,3) ORDER BY request_order LIMIT " + requiredNumber + ";";
db_connection db_object = new db_connection();
db_object.open();
MySqlCommand cmd = new MySqlCommand(query, db_object.connection);
cmd.Parameters.AddWithValue("@shift", shift);
cmd.Parameters.AddWithValue("@location", location);
cmd.Parameters.AddWithValue("@day", day);
cmd.Parameters.AddWithValue("@week", weekDate);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int userID = Int32.Parse(reader["user_id"].ToString());
TAs.Add(userID);
}
db_object.close();
return(TAs);
}
protected void displayAllItem()
{
db_connection db_obj = new db_connection();
db_obj.open();
//**********************
itemReport.ProcessingMode = ProcessingMode.Local;
itemReport.LocalReport.ReportPath = Server.MapPath("~/itemReport.rdlc");
itemDataSet item_Dataset = new itemDataSet();
string s = "SELECT [item_id],[venue],[item_name],[description],[location_id],[available_time],[date],[status],[type],[added_by],[unit_price],[img] FROM [item_services];";
SqlDataAdapter da = new SqlDataAdapter(s, db_obj.connection);
DataTable dt = new DataTable();
da.Fill(item_Dataset, item_Dataset.Tables[0].TableName);
dt = item_Dataset.Tables[0];
// //YouTube. 2016. crystal report,how to create crystal report in C# net using Dataset,visual studio - YouTube. [ONLINE] Available at: https://www.youtube.com/watch?v=uRICO1sZ3Hc. [Accessed 10 Nov 2017].
ReportDataSource ReportDataSource = new ReportDataSource("item_services", item_Dataset.Tables[0]);
this.itemReport.LocalReport.DataSources.Clear();
this.itemReport.LocalReport.DataSources.Add(ReportDataSource);
this.itemReport.LocalReport.Refresh();
Generatereport(dt);
//***
// this.userReport.ReportRefresh();
//************************
db_obj.close();
}
protected void populateTAsList()
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_id, user_name FROM user";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
MySqlDataReader reader = cmd.ExecuteReader();
taNamesList.DataSource = reader;
taNamesList.DataTextField = "user_name";
taNamesList.DataValueField = "user_id";
taNamesList.DataBind();
taNamesList.Items.RemoveAt(0);
db_obj.close();
int userPositionID = int.Parse(Session["position"].ToString());
if (commonMethods.isScheduler(userPositionID))
{
}
else
{
taNamesList.SelectedValue = Session["id"].ToString();
taNamesList.Enabled = false;
}
}
//a method to check if TA is logged in to shift or not from attendance table
protected bool checkLogginStatus(int userID, int locationID, string week, string day)
{
bool result = false;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT IF((SELECT DISTINCT shift_id FROM attendance WHERE user_id=@user AND week_start_date=@week AND location_id=@location AND day=@day AND logIn_status=1 LIMIT 1), 1, 0)";
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);
string queryResult = (cmd.ExecuteScalar()).ToString();
if (queryResult == "1")
{
result = true;
}
else
{
result = false;
}
db_obj.close();
return(result);
}
protected void insertIntoAttendance()
{
int user = int.Parse(taNamesList.SelectedValue.ToString());
string week = nextMondayDate.Text;
int shiftID = int.Parse(shiftsList.SelectedValue.ToString());
int locationID = int.Parse(locationsList.SelectedValue.ToString());
string day = daysList.SelectedValue;
db_connection db_obj = new db_connection();
db_obj.open();
string query = "INSERT INTO attendance (user_id, week_start_date, shift_id, location_id, day, status, logIn_status) VALUES (@user, @week, @shift, @location, @day, 0, 0);";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@user", user);
cmd.Parameters.AddWithValue("@week", week);
cmd.Parameters.AddWithValue("@shift", shiftID);
cmd.Parameters.AddWithValue("@location", locationID);
cmd.Parameters.AddWithValue("@day", day);
cmd.ExecuteNonQuery();
db_obj.close();
}
//load user details once the username is selected from the dropdown menu
protected void userDropList_SelectedIndexChanged(object sender, EventArgs e)
{
if (userDropList.SelectedIndex != 0)
{
int user_id = int.Parse(userDropList.SelectedValue);
Session["selectedUser"] = user_id;
try
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_name, position_id, warning_count, password, resigning_date, achievements FROM user WHERE user_id=@id ;";
MySqlCommand cmd = new MySqlCommand(query, db_obj.connection);
cmd.Parameters.AddWithValue("@id", user_id);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
usernameText.Text = reader["user_name"].ToString();
userPositionDropList.SelectedValue = reader["position_id"].ToString();
warningLettterCount.Text = reader["warning_count"].ToString();
userPassword.Text = reader["password"].ToString();
achievements.Text = reader["achievements"].ToString();
resignDate.Text = reader["resigning_date"].ToString();
reader.Close();
db_obj.close();
delete.Enabled = true;
save.Enabled = true;
}
catch (Exception ex)
{
msg.Text = "There was something wrong while retrieveing the user details. Error: " + ex.Message;
}
}
}
protected void cancelBtn_Click(object sender, EventArgs e)
{
Button cancelBtn = sender as Button;
//Response.Redirect("Request Duty.aspx");
string[] dutyDetails = cancelBtn.ID.Split(',');
db_connection db_object = new db_connection();
db_object.open();
string query = "UPDATE duty_request SET request_type=1, cancel_remark=@remark WHERE user_id=@userID AND location_id=@location AND week_start_date=@week AND shift_id=@shift AND requestForDay=@day;";
MySqlCommand cmd = new MySqlCommand(query, db_object.connection);
cmd.Parameters.AddWithValue("@userID", Session["id"].ToString());
cmd.Parameters.AddWithValue("@location", dutyDetails[0]);
cmd.Parameters.AddWithValue("@shift", dutyDetails[1]);
cmd.Parameters.AddWithValue("@week", nextMondayDate.Text);
cmd.Parameters.AddWithValue("@day", dutyDetails[2]);
cmd.Parameters.AddWithValue("@remark", cancelRemark.Text);
cmd.ExecuteNonQuery();
Response.Redirect(Request.RawUrl);
db_object.close();
msg.Text = "Your cancellation will be considered soon.";
}
protected void populateCustomer_namesList()
{
try
{
db_connection db_obj = new db_connection();
db_obj.open();
string query = "SELECT user_name AS user_name, user_code FROM _user;";
SqlCommand cmd = new SqlCommand(query, db_obj.connection);
SqlDataReader reader = cmd.ExecuteReader();
namesDropList.DataSource = reader;
namesDropList.DataTextField = "user_name";
namesDropList.DataValueField = "user_code";
namesDropList.DataBind();
/*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].*/
namesDropList.Items.Insert(0, new ListItem("Choose a name to report....", "Null"));
reader.Close();
db_obj.close();
}
catch (Exception ex)
{
msg.Text = "Name list are not available now... Error : " + ex.Message;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
int newTopup;
if (txt_card_num.Text == "" || txt_3_digit.Text == "" || txt_acct_name.Text == "" || txt_acct_num.Text == "" || txt_bank_name.Text == "" || txt_dob.Text == "" || txt_nationality.Text == "" || txt_expire.Text == "" || txt_topup.Text == "")
{
msg.Text = "Please fill in all the information!";
System.Web.UI.ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AlertBox", "alert('Please fill in all the information!');", true);
}
else
{
msg.Text = "Your Top-Up is progressing...";
System.Threading.Thread.Sleep(5000);
string id = Session["user_code"].ToString();
newTopup = Convert.ToInt32(Session["credits"]);
newTopup = newTopup + Convert.ToInt32(txt_topup.Text);
db_connection db = new db_connection();
db.open();
System.Web.HttpContext.Current.Session["credits"] = newTopup;
string query = "UPDATE _user SET credits=@newCredits WHERE user_code=@userID;";
SqlCommand cmd = new SqlCommand(query, db.connection);
cmd.Parameters.AddWithValue("@newCredits", newTopup);
cmd.Parameters.AddWithValue("@userID", id);
cmd.ExecuteNonQuery();
Session["credits"] = newTopup.ToString();
System.Web.UI.ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AlertBox", "alert('Congrats! You have Top-Uped" + txt_topup.Text + ", now Your New Balance is :RM" + newTopup.ToString() + " ');", true);
msg.Text = "Congrats! You have Top-Uped" + txt_topup.Text + ", now Your New Balance is :RM" + newTopup.ToString() + " ";
db.close();
}
}
