/// <summary>
/// informs the respective persons about the new status
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Requester</param>
/// <param name="message">Content of email</param>
/// <returns></returns>
public void sendMessage(VacationRequest vacationRequest, String message)
{
DBQuery dbq = new DBQuery();
if (message.Equals("SUBMISSION"))
{
Employee deputy = dbq.SelectDeputy(vacationRequest.getEmployeeID());
Employee divisionManager = dbq.SelectDivisionManager(vacationRequest.getEmployeeID());
// TODO: implement Email sending procedure
}
else if (message.Equals("AGREED"))
{
Employee divisionManager = dbq.SelectDivisionManager(vacationRequest.getEmployeeID());
Employee requester = dbq.SelectRequester(vacationRequest.getVacationRequestID());
// TODO: implement Email sending procedure
}
else if (message.Equals("PERMITTED"))
{
}
else if (message.Equals("REJECTED"))
{
Employee requester = dbq.SelectRequester(vacationRequest.getVacationRequestID());
// TODO: implement Email sending procedure
}
}
public VacationReplacementRequestViewItem(VacationRequest vacationRequest, String requesterFullName)
{
this._vacationRequestID = vacationRequest.getVacationRequestID();
this._requesterID = vacationRequest.getEmployeeID();
this._vacationStartDate = vacationRequest.getVacationStartDate();
this._vacationEndDate = vacationRequest.getVacationEndDate();
this._netVacationDays = vacationRequest.getNetVacationDays();
this._requesterFullName = requesterFullName;
this._vacationRequestProcessingState = vacationRequest.getVacationRequestProcessingState();
}
public EmployeeVacationRequestViewItem(VacationRequest vacationRequest, String shiftPartnerFullName)
{
_vacationRequestID = vacationRequest.getVacationRequestID();
_vacationStartDate = vacationRequest.getVacationStartDate();
_vacationEndDate = vacationRequest.getVacationEndDate();
_requesterID = vacationRequest.getEmployeeID();
_netVacationDays = vacationRequest.getNetVacationDays();
_vacationRequestProcessingState = vacationRequest.getVacationRequestProcessingState();
_shiftPartnerFullName = shiftPartnerFullName;
}
public AllVacationRequestViewItem(VacationRequest vacationRequest, String requesterFullName)
{
_vacationRequestID = vacationRequest.getVacationRequestID();
_vacationStartDate = vacationRequest.getVacationStartDate();
_vacationEndDate = vacationRequest.getVacationEndDate();
_requesterID = vacationRequest.getEmployeeID();
_netVacationDays = vacationRequest.getNetVacationDays();
_vacationRequestProcessingState = vacationRequest.getVacationRequestProcessingState();
_requesterFullName = requesterFullName;
_modificationDate = vacationRequest.getModificationDate();
}
public PendingVacationRequestViewItem(VacationRequest vacationRequest, String requesterName, String deputyName, int remainingVacationDays)
{
this._vacationRequestID = vacationRequest.getVacationRequestID();
this._requesterID = vacationRequest.getEmployeeID();
this._vacationStartDate = vacationRequest.getVacationStartDate();
this._vacationEndDate = vacationRequest.getVacationEndDate();
this._netVacationDays = vacationRequest.getNetVacationDays();
this._vacationRequestProcessingState = vacationRequest.getVacationRequestProcessingState();
this._requesterFullName = requesterName;
this._deputyFullName = deputyName;
this._vacationPeriodOverlapNote = vacationRequest.getVacationPeriodOverlapNote();
this._vacationLockPeriodNote = vacationRequest.getVacationLockPeriodNote();
this._remainingVacationDays = remainingVacationDays;
}
/// <summary>
/// (1) checks if remaining vacation days of employee suffice,
/// (2) sets lock period flag if appropriate,
/// (3) sets vacation period overlap flag if appropriate
/// </summary>
/// <param name="VacationRequest"> a vacation request, passed by reference</param>
/// <returns></returns>
public Boolean Validate(VacationRequest VacationRequest)
{
Boolean result = false;
///<remarks> this value must be numeric</remarks>
//checks remaining vacation days
DBQuery dbq = new DBQuery();
int remainingVacationDays = dbq.SelectRemainingVacationDays(VacationRequest.getEmployeeID());
// determine working days in vacation request
int vacationWorkingDays = VacationRequest.DetermineWorkingDaysInVacationRequest(VacationRequest);
// only proceed if time period of vacation request contains working days
if (vacationWorkingDays > 0)
{
VacationRequest.setNetVacationDays(vacationWorkingDays);
// Check VacationRequest overlap with others already existing VacationRequests of Requester without state "canceled"
var VRList = dbq.SelectUncanceledEmployeeVacationRequestInTimePeriod(VacationRequest, VacationRequest.getEmployeeID());
Boolean overlap = false;
Boolean insufficientRemainingVacationDays = false;
if (VRList.Count > 0)
{
overlap = true;
ErrorState.ErrorStateInstance.setError(ErrorState.OVERLAP_WITH_OWN_VACATION_REQUEST);
}
if (remainingVacationDays < vacationWorkingDays)
{
insufficientRemainingVacationDays = true;
ErrorState.ErrorStateInstance.setError(ErrorState.INSUFFICIENT_REMAINING_VACATION_DAYS);
}
// if requester has enough remaining Vacation Days AND no submitted vacation request(s) in this time period, go ahead
if (!insufficientRemainingVacationDays && !overlap)
{
TimePeriodOverlapCheck check = new TimePeriodOverlapCheck(VacationRequest);
//check lock period
if (!check.Validate(TimePeriodOverlapCheck.CHECK_CATEGORY_LOCK_PERIOD))
{
//overlap existing
VacationRequest.setVacationLockPeriodNote(true);
}
//check overlap period with Deputy
if (!check.Validate(TimePeriodOverlapCheck.CHECK_CATEGORY_VACATION_OVERLAP_PERIOD))
{
//overlap existing
VacationRequest.setVacationPeriodOverlapNote(true);
}
if (VacationRequest.getVacationStartDate() < DateTime.Now)
{
//if VacationRequestEndDate is in past the new Processingstatus is: agreed, so the decision of the DivisionManager is the next step
VacationRequest.setVacationRequestProcessingState(VacationRequestProcessingState.agreed);
}
result = true;
}
}
else
{
ErrorState.ErrorStateInstance.setError(ErrorState.ZERO_NET_VACATION_DAYS_IN_VACATION_REQUEST);
}
return result;
}
public void SelectVacationRequestTest_F5032_K_1()
{
ulong VacationRequestIDtmp = 0UL;
DateTime VacationStartDatetmp = new DateTime(2000, 1, 1);
DateTime VacationEndDatetmp = new DateTime(2000, 1, 1);
DateTime SubmissionDatetmp = new DateTime(2000, 1, 1);
string VacationProcessingStatetmp = "";
string VacationTypetmp = "c";
DateTime ModificationDatetmp = new DateTime(2000, 1, 1);
Boolean VacationOverlapNotetmp = false;
Boolean VacationLockPeriodNotetmp = false;
string EmployeeIDtmp = "null";
int NetVacationDays = 0;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.VacationRequest WHERE VacationRequestID = " + 10 + "";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
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-Returns into temp variables and insert them into the list
VacationRequestIDtmp = (ulong)reader.GetInt64(reader.GetOrdinal("VacationRequestID"));
VacationStartDatetmp = reader.GetDateTime(reader.GetOrdinal("VacationStartDate"));
VacationEndDatetmp = reader.GetDateTime(reader.GetOrdinal("VacationEndDate"));
SubmissionDatetmp = reader.GetDateTime(reader.GetOrdinal("SubmissionDate"));
VacationTypetmp = reader.GetString(reader.GetOrdinal("VacationType"));
VacationProcessingStatetmp = reader.GetString(reader.GetOrdinal("VacationProcessingState"));
ModificationDatetmp = reader.GetDateTime(reader.GetOrdinal("ModificationDate"));
VacationOverlapNotetmp = reader.GetBoolean(reader.GetOrdinal("VacationPeriodOverlapNote"));
VacationLockPeriodNotetmp = reader.GetBoolean(reader.GetOrdinal("VacationLockPeriodNote"));
EmployeeIDtmp = reader.GetString(reader.GetOrdinal("EmployeeID"));
NetVacationDays = reader.GetInt32(reader.GetOrdinal("NetVacationDays"));
}
}
}
}
}
VacationRequest Vac = new VacationRequest(VacationRequestIDtmp, EmployeeIDtmp, VacationStartDatetmp,
VacationEndDatetmp, SubmissionDatetmp, VacationTypetmp, VacationRequestProcessingState.submitted,
ModificationDatetmp, VacationOverlapNotetmp, VacationLockPeriodNotetmp, NetVacationDays);
VacationRequest Vacexpect = new VacationRequest(10,"1e04bd54-4fb8-4bb8-806c-8ad7b8c90c43", new DateTime(2015,02,01),new DateTime(2015,02,28),
SubmissionDatetmp, "Erholungsurlaub", VacationRequestProcessingState.submitted, ModificationDatetmp,
false,false,29);
bool Result = false;
if ((Vac.getVacationRequestID() == Vacexpect.getVacationRequestID())
&& (Vac.getEmployeeID() == Vacexpect.getEmployeeID())
&& (Vac.getVacationStartDate() == Vacexpect.getVacationStartDate())
&& (Vac.getVacationEndDate() == Vacexpect.getVacationEndDate())
&& (Vac.getSubmissionDate() == Vacexpect.getSubmissionDate())
&& (Vac.getVacationType() == Vacexpect.getVacationType())
&& (Vac.getVacationRequestProcessingState() == Vacexpect.getVacationRequestProcessingState())
&& (Vac.getModificationDate() == Vacexpect.getModificationDate())
&& (Vac.getVacationPeriodOverlapNote() == Vacexpect.getVacationPeriodOverlapNote())
&& (Vac.getVacationLockPeriodNote() == Vacexpect.getVacationLockPeriodNote())
&& (Vac.getNetVacationDays() == Vacexpect.getNetVacationDays()))
{
Result = true;
}
Assert.AreEqual(true, Result);
}
private Boolean RestoreVacationDaysInEmployeeVacationEntitlement(VacationRequest vacationRequest)
{
Boolean result = false;
DBQuery dbq = new DBQuery();
int netWorkingDays = vacationRequest.getNetVacationDays();
int remainingVacationDays = dbq.SelectRemainingVacationDays(vacationRequest.getEmployeeID());
result = dbq.UpdateRemainingVacationDays(vacationRequest.getEmployeeID(), netWorkingDays + remainingVacationDays);
return result;
}
/// <summary>
/// selects the Shiftpartner of the Employee in the VacationRequest
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Employee</param>
/// <returns>string with ShiftpartnerID</returns>
public string SelectDeputy(VacationRequest VacationRequest)
{
string Id = "null";
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement = "SELECT Id FROM dbo.AspNetUsers WHERE ShiftGroupID = (SELECT ShiftGroupID FROM dbo.AspNetUsers WHERE Id = @EmployeeID) AND Id != @EmployeeID";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID", VacationRequest.getEmployeeID());
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())
{
// To avoid unexpected bugs access columns by name.
Id = reader.GetString(reader.GetOrdinal("Id"));
}
}
}
}
}
return Id;
}
/// <summary>
/// inserts a new VacationRequest into the DB
/// </summary>
/// <param name="VacationRequest">VacationRequest of Employee</param>
/// <returns>true or false (successfull or not)</returns>
public Boolean InsertNewVacationRequest(VacationRequest VacationRequest)
{
bool Result = false;
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"INSERT INTO [dbo].[VacationRequest] VALUES (@VacationRequestID, @VacationStartDate, @VacationEndDate, @SubmissionDate, @VacationType, @VacationProcessingState, @ModificationDate, @VacationPeriodOverlapNote, @VacationLockPeriodNote,@NetVacationDays, @EmployeeID)";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("VacationRequestID", (long)VacationRequest.getVacationRequestID());
cmd.Parameters.AddWithValue("VacationStartDate", VacationRequest.getVacationStartDate());
cmd.Parameters.AddWithValue("VacationEndDate", VacationRequest.getVacationEndDate());
cmd.Parameters.AddWithValue("SubmissionDate", VacationRequest.getSubmissionDate());
cmd.Parameters.AddWithValue("VacationType", VacationRequest.getVacationType());
cmd.Parameters.AddWithValue("VacationProcessingState",
ConvertVacationRequestProcessingStateToString(VacationRequest.getVacationRequestProcessingState()));
cmd.Parameters.AddWithValue("ModificationDate", VacationRequest.getModificationDate());
cmd.Parameters.AddWithValue("VacationPeriodOverlapNote",
VacationRequest.getVacationPeriodOverlapNote());
cmd.Parameters.AddWithValue("VacationLockPeriodNote", VacationRequest.getVacationLockPeriodNote());
cmd.Parameters.AddWithValue("EmployeeID", VacationRequest.getEmployeeID());
cmd.Parameters.AddWithValue("NetVacationDays", VacationRequest.getNetVacationDays());
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;
}