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