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);
}
/// <summary>
/// Calculates the needed vacation days for the desired holiday period. Weekends and holidays are deducted.
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Requester</param>
/// <returns>number of needed vacation days</returns>
public static int DetermineWorkingDaysInVacationRequest(VacationRequest VacationRequest)
{
int workingDays = 0;
int holiDays = 0;
int netWorkingDays = 0;
//count weekdays between vacation start day and vacation end day
int n = 0;
while (VacationRequest.getVacationStartDate().AddDays(n) <= VacationRequest.getVacationEndDate()) // vacation start day == vacation start day is possible
{
if (VacationRequest.getVacationStartDate().AddDays(n).DayOfWeek != DayOfWeek.Saturday && VacationRequest.getVacationStartDate().AddDays(n).DayOfWeek != DayOfWeek.Sunday) //== no weekday
{
workingDays++;
}
n++;
}
// check if holidays days are in the vacation period
DBQuery dbq = new DBQuery();
holiDays = dbq.SelectHolidays(VacationRequest);
// correct required vacation days
netWorkingDays = workingDays - holiDays;
return netWorkingDays;
}
/// <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;
}
/// <summary>
/// Select VacationRequests of the Requester without status canceled which overlap with the new VacationRequest
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Requester</param>
/// <param name="EmployeeID">EmployeeID of the Requester</param>
/// <returns>List two DateTimes: Startdate and Enddate of the selected VacationRequests</returns>
public List<Tuple<ulong, DateTime, DateTime>> SelectUncanceledEmployeeVacationRequestInTimePeriod(VacationRequest VacationRequest, string EmployeeID)
{
var timelist = new List<Tuple<ulong, DateTime, DateTime>>();
ulong VacationRequestIDtmp = 0UL;
DateTime StartTmp = new DateTime();
DateTime EndTmp = new DateTime();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT VacationRequestID, VacationStartDate, VacationEndDate FROM VacationRequest " +
"WHERE EmployeeID = @EmployeeID AND (VacationProcessingState != @VacationProcessingState) AND " +
"((@VacationStartDate BETWEEN VacationStartDate AND VacationEndDate) OR " +
"(@VacationEndDate BETWEEN VacationStartDate AND VacationEndDate) OR " +
"(VacationStartDate BETWEEN @VacationStartDate AND @VacationEndDate) OR " +
"(VacationEndDate BETWEEN @VacationStartDate AND @VacationEndDate)) " +
"order by VacationStartDate";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID", EmployeeID);
cmd.Parameters.AddWithValue("VacationStartDate", VacationRequest.getVacationStartDate());
cmd.Parameters.AddWithValue("VacationEndDate", VacationRequest.getVacationEndDate());
cmd.Parameters.AddWithValue("VacationProcessingState", CANCELED);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
VacationRequestIDtmp = (ulong)reader.GetInt64(reader.GetOrdinal("VacationRequestID"));
StartTmp = reader.GetDateTime(reader.GetOrdinal("VacationStartDate"));
EndTmp = reader.GetDateTime(reader.GetOrdinal("VacationEndDate"));
timelist.Add(Tuple.Create(VacationRequestIDtmp,StartTmp, EndTmp));
}
}
}
}
}
return timelist;
}
/// <summary>
/// selects all LockPeriods with StartDate and EndDate within the VacationPeriod of VacationRequest
/// sortiert nach Startdatum
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Employee</param>
/// <returns>List that contains (0/one/many) Tuples, every Tuple contains 2 DateTime (Start, End of LockPeriod)</returns>
public List<Tuple<DateTime, DateTime>> SelectLockPeriods(VacationRequest VacationRequest)
{
var locklist = new List<Tuple<DateTime, DateTime>>();
DateTime StartlockTmp = new DateTime();
DateTime EndlockTmp = new DateTime();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT LockPeriodStartDate, LockPeriodEndDate FROM dbo.VacationLockPeriod WHERE (@VacationStartDate BETWEEN LockPeriodStartDate AND LockPeriodEndDate) OR (@VacationEndDate BETWEEN LockPeriodStartDate AND LockPeriodEndDate) order by LockPeriodStartDate";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("VacationStartDate", VacationRequest.getVacationStartDate());
cmd.Parameters.AddWithValue("VacationEndDate", VacationRequest.getVacationEndDate());
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
StartlockTmp = reader.GetDateTime(reader.GetOrdinal("LockPeriodStartDate"));
EndlockTmp = reader.GetDateTime(reader.GetOrdinal("LockPeriodEndDate"));
locklist.Add(Tuple.Create(StartlockTmp, EndlockTmp));
}
}
}
}
}
return locklist;
}
/// <summary>
/// selects a number of all Holidays between the VacationRequest' StartDate and EndDate that are workdays and not a Saturday/Sunday
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Employee in the VacationRequest</param>
/// <returns>int number of selected Holidays</returns>
public int SelectHolidays(VacationRequest VacationRequest)
{
int intdays = 0; // return value
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement = "SELECT Count(*) AS 'DayCount' FROM dbo.Calendar WHERE (Date BETWEEN @VacationStartDate AND @VacationEndDate) AND DayType IN ('Feiertag', 'Betriebsruhetag') AND Weekday NOT IN ('Samstag','Sonntag')";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("VacationStartDate", VacationRequest.getVacationStartDate());
cmd.Parameters.AddWithValue("VacationEndDate", VacationRequest.getVacationEndDate());
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
// To avoid unexpected bugs access columns by name.
intdays = reader.GetInt32(reader.GetOrdinal("DayCount"));
}
}
}
}
}
return intdays;
}
/// <summary>
/// selects all VacationTimePeriods of defined employeeID (VacationStartDates and VacationEndDates),
/// e.g. shift partner's vacation time periods that are in the same time period like the requested VacationTimePeriod
/// sortiert nach Startdatum
/// </summary>
/// <param name="VacationRequest">VacationRequest of the Employee</param>
/// <param name="EmployeeID">EmployeeID of the specific Employee </param>
/// <returns>List with (empty/ one Tuple/ many Tuples) and VacationStartDate, VacationEndDate inside every Tuple</returns>
public List<Tuple<ulong, DateTime, DateTime>> SelectEmployeeVacationRequestInTimePeriod(VacationRequest VacationRequest, string EmployeeID)
{
var timelist = new List<Tuple<ulong, DateTime, DateTime>>();
ulong VacationRequestIDtmp = 0UL;
DateTime StartTmp = new DateTime();
DateTime EndTmp = new DateTime();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT VacationRequestID, VacationStartDate, VacationEndDate FROM VacationRequest WHERE EmployeeID = @EmployeeID AND ((@VacationStartDate BETWEEN VacationStartDate AND VacationEndDate) OR (@VacationEndDate BETWEEN VacationStartDate AND VacationEndDate)) order by VacationStartDate";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID", EmployeeID);
cmd.Parameters.AddWithValue("VacationStartDate", VacationRequest.getVacationStartDate());
cmd.Parameters.AddWithValue("VacationEndDate", VacationRequest.getVacationEndDate());
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"));
StartTmp = reader.GetDateTime(reader.GetOrdinal("VacationStartDate"));
EndTmp = reader.GetDateTime(reader.GetOrdinal("VacationEndDate"));
timelist.Add(Tuple.Create(VacationRequestIDtmp,StartTmp, EndTmp));
}
}
}
}
}
return timelist;
}