public void AddApprovalAudit(TravelAndTrainingRequest assignedTo, Approval approval) { using (SqlConnection cn = new SqlConnection(SqlHelper.TTRConnectionString)) { using (SqlCommand cm = cn.CreateCommand()) { cm.CommandText = "dbo.swn_sp_Approval_Audit_Insert"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.AddWithValue("@Request_Id", assignedTo.Id); cm.Parameters.AddWithValue("@Reviewed_By", approval.ReviewedBy); cm.Parameters.AddWithValue("@Reviewed_On", approval.ReviewedOn); cm.Parameters.AddWithValue("@Reviewal_Status_Id", SqlHelper.ConvertToApprovalStatus(approval.Status)); cm.Parameters.AddWithValue("@Comments", approval.Comments); cn.Open(); cm.ExecuteNonQuery(); } } }
/// <summary> /// Sends the request for review. /// </summary> /// <param name="request">The request.</param> /// <param name="recipient">The recipient.</param> public void SendForReview(TravelAndTrainingRequest request, System.Net.Mail.MailAddress recipient) { using (MailMessage notification = new MailMessage()) { notification.Priority = MailPriority.High; notification.Subject = Resources.RequestReviewalEmailSubject; notification.IsBodyHtml = true; notification.To.Add(recipient); notification.Body = string.Format(Resources.ReviewRequestEmail, ConfigurationManager.AppSettings["PendingRequestsLink"], request.EventName, request.EventStart.ToString("d"), request.EventEnd.ToString("d"), provider.GetEmployeeByNetworkID(request.Requestor).FullName, request.TotalCost.ToString("$0")); Send(notification); } }
/// <summary> /// Sends the request back to the requestor once it has been final reviewed. /// </summary> /// <param name="request">The request.</param> /// <param name="comments">The comments.</param> public void SendRequestFinalReviewed(TravelAndTrainingRequest request, string comments) { using (MailMessage notification = new MailMessage()) { notification.Priority = MailPriority.High; notification.Subject = string.Format(Resources.RequestActedOnSubject, request.Status); notification.IsBodyHtml = true; Employee requestor = provider.GetEmployeeByNetworkID(request.Requestor); notification.To.Add(requestor.EmailAddress); notification.Body = string.Format(Resources.RequestActedOnEmail, ConfigurationManager.AppSettings["SubmittedRequestsLink"], request.Status, request.EventName, request.EventStart.ToString("d"), request.EventEnd.ToString("d"), provider.GetEmployeeByNetworkID(request.FinalReviewedBy).FullName, comments); Send(notification); } }
public TravelAndTrainingRequest GetRequestById(int id) { using (SqlConnection cn = new SqlConnection(SqlHelper.TTRConnectionString)) { using (SqlCommand cm = cn.CreateCommand()) { cm.CommandText = "dbo.swn_sp_Travel_Training_Request_Retrieve_By_Id"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.AddWithValue("@Id", id); cm.Connection.Open(); using (SqlDataReader rdr = cm.ExecuteReader(CommandBehavior.CloseConnection)) { if (rdr.Read()) { TravelAndTrainingRequest result = new TravelAndTrainingRequest(); result.Id = System.Convert.ToInt32(rdr["Id"]); result.Requestor = rdr["Requested_By"].ToString(); result.RequestedOn = System.Convert.ToDateTime(rdr["Requested_DateTime"]); result.EventStart = System.Convert.ToDateTime(rdr["Event_Start_Date"]); result.EventEnd = System.Convert.ToDateTime(rdr["Event_End_Date"]); result.Status = SqlHelper.ConvertToRequestStatus(rdr["Status"].ToString()); result.HasTraining = System.Convert.ToBoolean(rdr["Has_Training"]); result.HasTravel = System.Convert.ToBoolean(rdr["Has_Travel"]); result.EventName = rdr["Event_Name"].ToString(); result.EventCity = rdr["Event_Location_City"].ToString(); result.EventState = rdr["Event_Location_State"].ToString(); result.BusinessPurpose = rdr["Event_Business_Purpose"].ToString(); result.ProjectName = rdr["Event_Associated_Project"].ToString(); result.PendingReviewBy = rdr["Pending_Review_By"].ToString(); result.FinalReviewedBy = rdr["Final_Reviewed_By"].ToString(); result.FinalReviewedOn = System.Convert.ToDateTime(rdr["Final_Reviewed_DateTime"]); result.Training = new SWN.TTR.Estimates.TrainingEstimate(); result.Training.TrainingName = rdr["Training_Name"].ToString(); result.Training.StartingDate = System.Convert.ToDateTime(rdr["Training_Start_Date"]); result.Training.EndingDate = System.Convert.ToDateTime(rdr["Training_End_Date"]); result.Training.TotalCost = System.Convert.ToDecimal(rdr["Training_Total_Cost"]); result.Travel = new SWN.TTR.Estimates.TravelEstimate(); result.Travel.DestinationState = rdr["Travel_Destination_State"].ToString(); result.Travel.DestinationCity = rdr["Travel_Destination_City"].ToString(); result.Travel.HasFlightArrangements = System.Convert.ToBoolean(rdr["Travel_Has_Flight"]); result.Travel.HasGroundArrangements = System.Convert.ToBoolean(rdr["Travel_Has_Ground"]); result.Travel.HasLodgingArrangements = System.Convert.ToBoolean(rdr["Travel_Has_Lodging"]); result.MiscealleanousCostsComments = rdr["Travel_Comments"].ToString(); result.Travel.FlightArrangements.DestinationMethod = rdr["Travel_Flight_Destination_Id"].ToString(); result.Travel.FlightArrangements.ReturnMethod = rdr["Travel_Flight_Return_Id"].ToString(); result.Travel.FlightArrangements.TotalCost = System.Convert.ToDecimal(rdr["Travel_Flight_Total_Cost"]); result.Travel.GroundArrangements.TotalCost = System.Convert.ToDecimal(rdr["Travel_Ground_Total_Cost"]); result.Travel.LodgingArrangements.NumberOfNights = System.Convert.ToInt32(rdr["Travel_Lodging_Number_Of_Nights"]); result.Travel.LodgingArrangements.TotalCost = System.Convert.ToDecimal(rdr["Travel_Lodging_Total_Cost"]); result.MisceallaneousCosts = System.Convert.ToDecimal(rdr["Misc_Costs"]); rdr.NextResult(); while (rdr.Read()) { result.Travel.GroundArrangements.Modes.Add(rdr["Ground_Transportation_Id"].ToString()); } return result; } else return null; } } } }
private static void RemoveGroundTransportation(TravelAndTrainingRequest request, SqlTransaction trans) { using (SqlCommand cm = trans.Connection.CreateCommand()) { cm.Transaction = trans; cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "dbo.swn_sp_Travel_Training_Request_Ground_Transportation_Delete_By_Request"; cm.Parameters.AddWithValue("@Request_Id", request.Id); cm.ExecuteNonQuery(); } }
private static void AddTrainingRequestInsertParameters(TravelAndTrainingRequest request, SqlCommand cm) { cm.Parameters.AddWithValue("@Requested_By", request.Requestor); cm.Parameters.AddWithValue("@Requested_DateTime", request.RequestedOn); cm.Parameters.AddWithValue("@Event_Start_Date", request.EventStart); cm.Parameters.AddWithValue("@Event_End_Date", request.EventEnd); cm.Parameters.AddWithValue("@Status", SqlHelper.ConvertToRequestStatus(request.Status)); cm.Parameters.AddWithValue("@Has_Training", request.HasTraining); cm.Parameters.AddWithValue("@Has_Travel", request.HasTravel); cm.Parameters.AddWithValue("@Event_Name", request.EventName); cm.Parameters.AddWithValue("@Event_Location_City", request.EventCity); cm.Parameters.AddWithValue("@Event_Location_State", request.EventState); cm.Parameters.AddWithValue("@Event_Business_Purpose", request.BusinessPurpose); cm.Parameters.AddWithValue("@Event_Associated_Project", request.ProjectName); cm.Parameters.AddWithValue("@Pending_Review_By", request.PendingReviewBy); cm.Parameters.AddWithValue("@Final_Reviewed_By", request.FinalReviewedBy); cm.Parameters.AddWithValue("@Final_Reviewed_DateTime", request.FinalReviewedOn); cm.Parameters.AddWithValue("@Training_Name", request.Training.TrainingName); cm.Parameters.AddWithValue("@Training_Start_Date", request.Training.StartingDate); cm.Parameters.AddWithValue("@Training_End_Date", request.Training.EndingDate); cm.Parameters.AddWithValue("@Training_Total_Cost", request.Training.TotalCost); cm.Parameters.AddWithValue("@Travel_Destination_State", request.Travel.DestinationState); cm.Parameters.AddWithValue("@Travel_Destination_City", request.Travel.DestinationCity); cm.Parameters.AddWithValue("@Travel_Has_Flight", request.Travel.HasFlightArrangements); cm.Parameters.AddWithValue("@Travel_Has_Ground", request.Travel.HasGroundArrangements); cm.Parameters.AddWithValue("@Travel_Has_Lodging", request.Travel.HasLodgingArrangements); cm.Parameters.AddWithValue("@Travel_Comments", request.MiscealleanousCostsComments); cm.Parameters.AddWithValue("@Travel_Flight_Destination_Id", request.Travel.FlightArrangements.DestinationMethod); cm.Parameters.AddWithValue("@Travel_Flight_Return_Id", request.Travel.FlightArrangements.ReturnMethod); cm.Parameters.AddWithValue("@Travel_Flight_Total_Cost", request.Travel.FlightArrangements.TotalCost); cm.Parameters.AddWithValue("@Travel_Flight_Is_Interoffice", request.Travel.FlightArrangements.IsInterofficeOnly); cm.Parameters.AddWithValue("@Travel_Ground_Total_Cost", request.Travel.GroundArrangements.TotalCost); cm.Parameters.AddWithValue("@Travel_Lodging_Number_Of_Nights", request.Travel.LodgingArrangements.NumberOfNights); cm.Parameters.AddWithValue("@Travel_Lodging_Total_Cost", request.Travel.LodgingArrangements.TotalCost); cm.Parameters.AddWithValue("@Total_Cost", request.TotalCost); cm.Parameters.AddWithValue("@Misc_Costs", request.MisceallaneousCosts); }
private static void AddGroundTransportation(TravelAndTrainingRequest request, SqlTransaction trans) { foreach (string groundMode in request.Travel.GroundArrangements.Modes) { using (SqlCommand cm = trans.Connection.CreateCommand()) { cm.Transaction = trans; cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "dbo.swn_sp_Travel_Training_Request_Ground_Transportation_Insert"; cm.Parameters.AddWithValue("@Request_Id", request.Id); cm.Parameters.AddWithValue("@Ground_Transportation_Id", groundMode); cm.ExecuteNonQuery(); } } }
public void Save(TravelAndTrainingRequest request) { using (SqlConnection cn = new SqlConnection(SqlHelper.TTRConnectionString)) { using (SqlCommand cm = cn.CreateCommand()) { AddTrainingRequestInsertParameters(request, cm); cm.CommandType = CommandType.StoredProcedure; cm.Connection.Open(); using (SqlTransaction trans = cn.BeginTransaction()) { cm.Transaction = trans; try { if (request.Id == 0) { cm.CommandText = "dbo.swn_sp_Travel_Training_Request_Insert"; // do an insert on the ground transportation request.Id = System.Convert.ToInt32(cm.ExecuteScalar()); AddGroundTransportation(request, trans); } else { cm.Parameters.AddWithValue("@Id", request.Id); cm.CommandText = "dbo.swn_sp_Travel_Training_Request_Update"; int rowsAffected = System.Convert.ToInt32(cm.ExecuteScalar()); RemoveGroundTransportation(request, trans); AddGroundTransportation(request, trans); } trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } } } }
private void UnpackRequest(TravelAndTrainingRequest request) { this.txtEvent.Text = request.EventName; if (!request.EventStart.IsDefault()) this.txtEventStartDate.Text = request.EventStart.ToShortDateString(); if (!request.EventEnd.IsDefault()) this.txtEventEndDate.Text = request.EventEnd.ToShortDateString(); this.txtEventCity.Text = request.EventCity; this.ddlEventStates.SelectedValue = request.EventState; this.txtEventPurpose.Text = request.BusinessPurpose; this.txtEventProjects.Text = request.ProjectName; this.ckbTraining.Checked = request.HasTraining; this.ckbTravel.Checked = request.HasTravel; this.txtTrainName.Text = request.Training.TrainingName; if (!request.Training.StartingDate.IsDefault()) this.txtTrainStart.Text = request.Training.StartingDate.ToShortDateString(); if (!request.Training.EndingDate.IsDefault()) this.txtTrainEnd.Text = request.Training.EndingDate.ToShortDateString(); if(request.Training.TotalCost != BizObjectValidator.DEFAULT_COST) this.txtTrainCost.Text = request.Training.TotalCost.ToString("0"); this.txtTravelCity.Text = request.Travel.DestinationCity; this.ddlTravelStates.SelectedValue = request.Travel.DestinationState; this.ckbTravelAir.Checked = request.Travel.HasFlightArrangements; this.ckbTravelGround.Checked = request.Travel.HasGroundArrangements; this.ckbTravelLodging.Checked = request.Travel.HasLodgingArrangements; this.ckbAirInteroffice.Checked = request.Travel.FlightArrangements.IsInterofficeOnly; this.ddlAirDestinationMode.SelectedValue = request.Travel.FlightArrangements.DestinationMethod; this.ddlAirReturnMode.SelectedValue = request.Travel.FlightArrangements.ReturnMethod; if(request.Travel.FlightArrangements.TotalCost != BizObjectValidator.DEFAULT_COST) this.txtAirCost.Text = request.Travel.FlightArrangements.TotalCost.ToString("0"); if(request.Travel.LodgingArrangements.NumberOfNights > 0) this.txtLodgingNights.Text = request.Travel.LodgingArrangements.NumberOfNights.ToString(); if(request.Travel.LodgingArrangements.TotalCost != BizObjectValidator.DEFAULT_COST) this.txtLodgingCost.Text = request.Travel.LodgingArrangements.TotalCost.ToString("0"); foreach (string mode in request.Travel.GroundArrangements.Modes) this.ckblstGroundModes.Items.FindByValue(mode).Selected = true; if(request.Travel.GroundArrangements.TotalCost != BizObjectValidator.DEFAULT_COST) this.txtGroundCost.Text = request.Travel.GroundArrangements.TotalCost.ToString("0"); this.txtMiscComments.Text = request.MiscealleanousCostsComments; if(request.MisceallaneousCosts != BizObjectValidator.DEFAULT_COST) this.txtMiscCost.Text = request.MisceallaneousCosts.ToString("0"); }
private TravelAndTrainingRequest PackRequest() { TravelAndTrainingRequest request = null; if (this.RequestId == 0) request = new TravelAndTrainingRequest(this.User); else { TravelAndTrainingRequestFinder requestFinder = new TravelAndTrainingRequestFinder(this.User); request = requestFinder.GetRequestById(this.RequestId); } request.EventName = this.txtEvent.Text; if (this.txtEventStartDate.Text.Length > 0) { try { request.EventStart = DateTime.Parse(this.txtEventStartDate.Text); } catch (FormatException) { request.EventStart = BizObjectValidator.DEFAULT_DATETIME; this.packingErrors.Add("Event start date is not valid."); } } else request.EventStart = BizObjectValidator.DEFAULT_DATETIME; if (this.txtEventEndDate.Text.Length > 0) { try { request.EventEnd = DateTime.Parse(this.txtEventEndDate.Text); } catch (FormatException) { request.EventEnd = BizObjectValidator.DEFAULT_DATETIME; this.packingErrors.Add("Event end date is not valid."); } } else request.EventEnd = BizObjectValidator.DEFAULT_DATETIME; request.EventCity = this.txtEventCity.Text; request.EventState = this.ddlEventStates.SelectedValue; request.BusinessPurpose = this.txtEventPurpose.Text; request.ProjectName = this.txtEventProjects.Text; if (this.ckbTraining.Checked) request.HasTraining = true; else request.HasTraining = false; request.Training.TrainingName = this.txtTrainName.Text; if (this.txtTrainStart.Text.Length > 0) { try { request.Training.StartingDate = DateTime.Parse(this.txtTrainStart.Text); } catch (FormatException) { request.Training.StartingDate = BizObjectValidator.DEFAULT_DATETIME; this.packingErrors.Add("Start date is not valid."); } } else request.Training.StartingDate = BizObjectValidator.DEFAULT_DATETIME; if (this.txtTrainEnd.Text.Length > 0) { try { request.Training.EndingDate = DateTime.Parse(this.txtTrainEnd.Text); } catch (FormatException) { request.Training.EndingDate = BizObjectValidator.DEFAULT_DATETIME; this.packingErrors.Add("End date is not valid."); } } else request.Training.EndingDate = BizObjectValidator.DEFAULT_DATETIME; if (this.txtTrainCost.Text.Length > 0) { try { request.Training.TotalCost = System.Convert.ToDecimal(this.txtTrainCost.Text); } catch (FormatException) { request.Training.TotalCost = BizObjectValidator.DEFAULT_COST; this.packingErrors.Add("Training estimated cost is not valid."); } } else request.Training.TotalCost = BizObjectValidator.DEFAULT_COST; if (this.ckbTravel.Checked) request.HasTravel = true; else request.HasTravel = false; request.Travel.DestinationCity = this.txtTravelCity.Text; request.Travel.DestinationState = this.ddlTravelStates.SelectedValue; if (this.ckbTravelAir.Checked) request.Travel.HasFlightArrangements = true; else request.Travel.HasFlightArrangements = false; request.Travel.FlightArrangements.DestinationMethod = this.ddlAirDestinationMode.SelectedValue; request.Travel.FlightArrangements.ReturnMethod = this.ddlAirReturnMode.SelectedValue; if (this.txtAirCost.Text.Length > 0) { try { request.Travel.FlightArrangements.TotalCost = System.Convert.ToDecimal(this.txtAirCost.Text); } catch (FormatException) { request.Travel.FlightArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; this.packingErrors.Add("Flight estimated cost is not valid."); } } else request.Travel.FlightArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; if (this.ckbTravelGround.Checked) request.Travel.HasGroundArrangements = true; else request.Travel.HasGroundArrangements = false; request.Travel.GroundArrangements.Modes.Clear(); foreach (ListItem groundMode in this.ckblstGroundModes.Items) if(groundMode.Selected) request.Travel.GroundArrangements.Modes.Add(groundMode.Value); if (this.txtGroundCost.Text.Length > 0) { try { request.Travel.GroundArrangements.TotalCost = System.Convert.ToDecimal(this.txtGroundCost.Text); } catch (FormatException) { request.Travel.GroundArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; this.packingErrors.Add("Ground estimated cost is not valid."); } } else request.Travel.GroundArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; if (this.ckbTravelLodging.Checked) request.Travel.HasLodgingArrangements = true; else request.Travel.HasLodgingArrangements = false; if (this.txtLodgingNights.Text.Length > 0) { try { request.Travel.LodgingArrangements.NumberOfNights = System.Convert.ToInt32(this.txtLodgingNights.Text); } catch (FormatException) { request.Travel.LodgingArrangements.NumberOfNights = 0; this.packingErrors.Add("Lodging # of nights is not valid."); } } else request.Travel.LodgingArrangements.NumberOfNights = 0; if (this.txtLodgingCost.Text.Length > 0) { try { request.Travel.LodgingArrangements.TotalCost = System.Convert.ToDecimal(this.txtLodgingCost.Text); } catch (FormatException) { request.Travel.LodgingArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; this.packingErrors.Add("Lodging estimated cost is not valid."); } } else request.Travel.LodgingArrangements.TotalCost = BizObjectValidator.DEFAULT_COST; request.MiscealleanousCostsComments = this.txtMiscComments.Text; if (this.txtMiscCost.Text.Length > 0) { try { request.MisceallaneousCosts = System.Convert.ToDecimal(this.txtMiscCost.Text); } catch (FormatException) { request.MisceallaneousCosts = BizObjectValidator.DEFAULT_COST; this.packingErrors.Add("Additional estimated costs is not valid."); } } else request.MisceallaneousCosts = BizObjectValidator.DEFAULT_COST; return request; }
private void LoadSummary(TravelAndTrainingRequest request) { this.litcEventName.Text = request.EventName; if (!request.EventStart.IsDefault()) this.litcEventStart.Text = request.EventStart.ToShortDateString(); if (!request.EventEnd.IsDefault()) this.litcEventEnd.Text = request.EventEnd.ToShortDateString(); this.litcEventCity.Text = request.EventCity; if (string.CompareOrdinal(request.EventState, "-") != 0) this.litcEventState.Text = request.EventState; this.litcEventPurpose.Text = request.BusinessPurpose; this.litcEventProjects.Text = request.ProjectName; if (request.HasTraining) { this.pnlcnfTraining.Visible = true; this.litcTrainName.Text = request.Training.TrainingName; if (!request.Training.StartingDate.IsDefault()) this.litcTrainStart.Text = request.Training.StartingDate.ToShortDateString(); if (!request.Training.EndingDate.IsDefault()) this.litcTrainEnd.Text = request.Training.EndingDate.ToShortDateString(); this.litcTrainCost.Text = request.Training.TotalCost.ToString("0"); } else this.pnlcnfTraining.Visible = false; if (request.HasTravel) { this.pnlcnfTravel.Visible = true; this.litcTravelCity.Text = request.Travel.DestinationCity; if (string.CompareOrdinal(request.Travel.DestinationState, "-") != 0) this.litcTravelState.Text = request.Travel.DestinationState; if (request.Travel.HasFlightArrangements) { this.pnlcnfAir.Visible = true; if (this.ddlAirDestinationMode.SelectedIndex > 0) this.litcAirDest.Text = this.ddlAirDestinationMode.Items.FindByValue(request.Travel.FlightArrangements.DestinationMethod).Text; if (this.ddlAirReturnMode.SelectedIndex > 0) this.litcAirReturn.Text = this.ddlAirReturnMode.Items.FindByValue(request.Travel.FlightArrangements.ReturnMethod).Text; this.litcAirCost.Text = request.Travel.FlightArrangements.TotalCost.ToString("0"); } else this.pnlcnfAir.Visible = false; if (request.Travel.HasGroundArrangements) { this.pnlcnfGround.Visible = true; StringBuilder sb = new StringBuilder(); foreach (string groundMode in request.Travel.GroundArrangements.Modes) sb.Append(this.ckblstGroundModes.Items.FindByValue(groundMode).Text + ","); if (sb.Length > 0) sb.Remove(sb.Length - 1, 1); this.litcGroundModes.Text = sb.ToString(); this.litcGroundCost.Text = request.Travel.GroundArrangements.TotalCost.ToString("0"); } else this.pnlcnfGround.Visible = false; if (request.Travel.HasLodgingArrangements) { this.pnlcnfLodging.Visible = true; this.litcLodgingNights.Text = request.Travel.LodgingArrangements.NumberOfNights.ToString(); this.litcLodgingCost.Text = request.Travel.LodgingArrangements.TotalCost.ToString("0"); } else this.pnlcnfLodging.Visible = false; } else this.pnlcnfTravel.Visible = false; this.litcGenComments.Text = request.MiscealleanousCostsComments; this.litcMiscCost.Text = request.MisceallaneousCosts.ToString("0"); this.litcTotalCost.Text = request.TotalCost.ToString("0"); }
protected void LoadApprovers(object sender, EventArgs e) { TravelAndTrainingRequest request = new TravelAndTrainingRequest(this.User); this.ddlApprovers.DataSource = request.GetFinalApprovers(); this.ddlApprovers.DataBind(); }