/// <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 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 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 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>
/// 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>
/// (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 SelectSubmittedVacationRequestsOfShiftPartnerTest_F5207_K_1()
{
ulong VacationRequestIDtmp = 0UL;
long convertbigint = 0;
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);
DateTime Mod = new DateTime(2000, 1, 1);
DateTime Sub = new DateTime(2000, 1, 1);
Boolean VacationOverlapNotetmp = false;
Boolean VacationLockPeriodNotetmp = false;
string EmployeeIDtmp = "c";
int NetVacationDays = 0;
int i = 0;
var VacList = new List<VacationRequest>();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.VacationRequest WHERE EmployeeID = @EmployeeID AND VacationProcessingState = 'offen' order by VacationStartDate";
using (SqlCommand cmd = new SqlCommand(sqlStatement, connection))
{
cmd.Parameters.AddWithValue("EmployeeID", "1e04bd54-4fb8-4bb8-806c-8ad7b8c90c43");
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"));
if (i == 1)
{
VacationRequest Vac = new VacationRequest(VacationRequestIDtmp, EmployeeIDtmp, VacationStartDatetmp,
VacationEndDatetmp, SubmissionDatetmp, VacationTypetmp,VacationRequestProcessingState.submitted,
ModificationDatetmp, VacationOverlapNotetmp, VacationLockPeriodNotetmp, NetVacationDays);
Mod = ModificationDatetmp;
Sub = SubmissionDatetmp;
VacList.Add(Vac);
}
i++;
}
}
}
}
}
VacationRequest Vac1 = new VacationRequest(8, "1e04bd54-4fb8-4bb8-806c-8ad7b8c90c43", new DateTime(2015, 09, 01), new DateTime(2015, 09, 01),
Sub, "Erholungsurlaub", VacationRequestProcessingState.submitted, Mod, false, false, 26);
var VacListExp = new List<VacationRequest>();
VacListExp.Add(Vac1);
bool result = false;
if ((VacList[0].getVacationRequestID() == VacListExp[0].getVacationRequestID())
&& (VacList[0].getEmployeeID() == VacListExp[0].getEmployeeID())
&& (VacList[0].getVacationStartDate() == VacListExp[0].getVacationStartDate())
&& (VacList[0].getVacationEndDate() == VacListExp[0].getVacationEndDate())
&& (VacList[0].getSubmissionDate() == VacListExp[0].getSubmissionDate())
&& (VacList[0].getVacationType() == VacListExp[0].getVacationType())
&& (VacList[0].getVacationRequestProcessingState() == VacListExp[0].getVacationRequestProcessingState())
&& (VacList[0].getModificationDate() == VacListExp[0].getModificationDate())
&& (VacList[0].getVacationPeriodOverlapNote() == VacListExp[0].getVacationPeriodOverlapNote())
&& (VacList[0].getVacationLockPeriodNote() == VacListExp[0].getVacationLockPeriodNote())
&& (VacList[0].getNetVacationDays() == VacListExp[0].getNetVacationDays()))
{
result = true;
}
Assert.AreEqual(true, 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);
}
public ActionResult Create(VacationManagement.VacationRequest vacationRequest)
{
String currentUserId = User.Identity.GetUserId();
if (ModelState.IsValid)
{
String statusMessage;
bool result = false;
if (vacationRequest.VacationEndDate >= vacationRequest.VacationStartDate)
{
if (DateTime.Now.Year == vacationRequest.getVacationStartDate().Year && DateTime.Now.Year == vacationRequest.getVacationEndDate().Year)
{
//Constructor VacationRequest
VacationManagement.VacationRequest vr = new VacationManagement.VacationRequest(currentUserId, vacationRequest.VacationStartDate, vacationRequest.VacationEndDate);
//start validate then insert into db
result = vr.InsertIntoDB();
if (result)
{
TempData["success"] = "Der Urlaubsantrag wurde erfolgreich angelegt.";
}
}
else
{
ErrorState.ErrorStateInstance.setError(ErrorState.VACATION_REQUEST_NOT_IN_CURRENT_YEAR);
}
}
else
{
ErrorState.ErrorStateInstance.setError(ErrorState.END_DATE_BEFORE_START_DATE);
}
int lastError = ErrorState.ErrorStateInstance.getLastError();
if (lastError != ErrorState.OK )
{
TempData["error"] = "Leider konnte ihr Urlaubsantrag nicht erstellt werden. " + ErrorState.ErrorStateInstance.getErrorMessage(lastError);
}
}
return RedirectToAction("Index", GetVacationRequests());
}
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>
/// Removes the TRUE-flag in all vacation requests of the shift partner, if the given vacation request is cancelled by its requester.
/// Use requester ID of vacation request to identity the shiftpartner and startdate and enddate to retrieve vacation requests of the deputy
/// </summary>
/// <param name="vacationRequest">use requesterID to find deputy, use time period to specify overlap time period </param>
/// <returns>TRUE if successful, else FALSE</returns>
private Boolean RemoveVacationPeriodOverlapNote(VacationRequest vacationRequest)
{
Boolean result = true;
DBQuery dbq = new DBQuery();
String deputyID = dbq.SelectDeputy(vacationRequest);
if (!deputyID.Equals("null") && deputyID != String.Empty)
{
var overlapList = dbq.SelectUncanceledEmployeeVacationRequestInTimePeriod(vacationRequest, deputyID);
if (overlapList.Count > 0)
{
foreach (var i in overlapList)
{
//change OverlapNote of existing VacationRequest of Deputy
result = dbq.UpdateVacationRequestPeriodOverlapNote(i.Item1, false);
if (!result) break;
}
}
}
return 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>
/// selects all VacationRequests of the given EmployeeID
/// sortiert nach Startdatum
/// </summary>
/// <param name="EmployeeID">EmployeeID</param>
/// <returns>List that contain all VacationRequest-Objects</returns>
public List<VacationRequest> SelectAllVacationRequestsOfEmployee(string EmployeeID)
{
ulong VacationRequestIDtmp = 0UL;
DateTime VacationStartDatetmp = new DateTime(2000,1,1);
DateTime VacationEndDatetmp = new DateTime(2000,1,1);
DateTime SubmissionDatetmp = new DateTime(2000,1,1);
VacationRequestProcessingState VacationProcessingStatetmp = 0;
string VacationTypetmp = "c";
DateTime ModificationDatetmp = new DateTime(2000,1,1);
Boolean VacationOverlapNotetmp = false;
Boolean VacationLockPeriodNotetmp = false;
string EmployeeIDtmp = "c";
int NetVacationDays = 0;
var VacList = new List<VacationRequest>();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
string sqlStatement =
"SELECT * FROM dbo.VacationRequest WHERE EmployeeID = @EmployeeID order by VacationStartDate";
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-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 = ConvertStringToVacationRequestProcessingState(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, VacationProcessingStatetmp,
ModificationDatetmp, VacationOverlapNotetmp, VacationLockPeriodNotetmp, NetVacationDays);
VacList.Add(Vac);
}
}
}
}
}
return VacList;
}
public TimePeriodOverlapCheck(VacationRequest VacationRequest)
{
this._vacationRequest = VacationRequest;
}
/// <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;
}
