public ActionResult ShowVacationSummary() { String currentUserId = User.Identity.GetUserId(); DBQuery dbq = new DBQuery(); int remaining = dbq.SelectRemainingVacationDays(currentUserId); int total = dbq.SelectVacationDaysTotal(currentUserId); int prevYear = dbq.SelectVacationDaysPreviousYear(currentUserId); GUIManagement.VacationEntitlementViewItem summary = new GUIManagement.VacationEntitlementViewItem(currentUserId,remaining,total,prevYear); return View(summary); }
public Boolean UpdateEntitlementOfEmployee(VacationEntitlementViewItem VacationEntitlement, String EmployeeId) { int VacationDaysPreviousYear_tmp = SelectVacationDaysPreviousYear(EmployeeId); int VacationDaysTotal_tmp = SelectVacationDaysTotal(EmployeeId); int VacationDayRemaining_tmp = SelectRemainingVacationDays(EmployeeId); int VacationDaysUsed_tmp = VacationDaysPreviousYear_tmp + VacationDaysTotal_tmp - VacationDayRemaining_tmp; int VacationDayRemaining_new = VacationEntitlement.VacationDaysTotal + VacationEntitlement.VacationDaysPreviousYear - VacationDaysUsed_tmp; bool Result = false; using (SqlConnection connection = new SqlConnection(sqlConnectionString)) { string sqlStatement = " UPDATE dbo.VacationEntitlement SET VacationDaysPreviousYear = @VacationDaysPreviousYear, VacationDaysTotal = @VacationDaysTotal, VacationDayRemaining = @VacationDaysRemaining WHERE EmployeeID = @EmployeeId"; using (SqlCommand cmd = new SqlCommand(sqlStatement, connection)) { cmd.Parameters.AddWithValue("VacationDaysPreviousYear", VacationEntitlement.VacationDaysPreviousYear); cmd.Parameters.AddWithValue("VacationDaysTotal", VacationEntitlement.VacationDaysTotal); cmd.Parameters.AddWithValue("VacationDaysRemaining", VacationDayRemaining_new); cmd.Parameters.AddWithValue("EmployeeId", EmployeeId); try { connection.Open(); cmd.ExecuteNonQuery();//insert/delete/update is nonQuery Result = true; } catch (SqlException e) //if Failure in Database then Result = false { { Result = false; } } } } return Result; }
public VacationEntitlementViewItem SelectEntitlementsOfEmployee(String EmployeeId) { String EmployeeID_tmp; String EmployeeFullName_tmp; int RemainingVacationDays_tmp; int VacationDaysTotal_tmp; int VacationDaysPreviousYear_tmp; using (SqlConnection connection = new SqlConnection(sqlConnectionString)) { string sqlStatement = "SELECT * FROM dbo.VacationEntitlement WHERE EmployeeID = @EmployeeId"; using (SqlCommand cmd = new SqlCommand(sqlStatement, connection)) { cmd.Parameters.AddWithValue("EmployeeId", EmployeeId); connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { // Check if the reader has any rows at all before starting to read. if (reader.HasRows) { // Read advances to the next row. while (reader.Read()) { // Save DB-Return into variables // wihout Role and StaffID EmployeeID_tmp = reader.GetString(reader.GetOrdinal("EmployeeID")); RemainingVacationDays_tmp = reader.GetInt32(reader.GetOrdinal("VacationDayRemaining")); VacationDaysTotal_tmp = reader.GetInt32(reader.GetOrdinal("VacationDaysTotal")); VacationDaysPreviousYear_tmp = reader.GetInt32(reader.GetOrdinal("VacationDaysPreviousYear")); EmployeeFullName_tmp = SelectEmployeeFullName(EmployeeID_tmp); VacationEntitlementViewItem VacationEntitlement = new VacationEntitlementViewItem(EmployeeID_tmp, RemainingVacationDays_tmp, VacationDaysTotal_tmp, VacationDaysPreviousYear_tmp, EmployeeFullName_tmp); return VacationEntitlement; } } } return null; } } }