/// <summary>
/// Adds missing status records per milestone, project, deliverable and activity
/// </summary>
/// <param name="releaseId"></param>
/// <param name="milestoneId"></param>
/// <param name="deliverable"></param>
/// <param name="projectId"></param>
private void AddDeliverableStatusRecords(int releaseId, int milestoneId, Deliverable deliverable, int projectId)
{
var input = new StatusInputModel
{
ProjectId = projectId,
DeliverableId = deliverable.Id,
MilestoneId = milestoneId,
ReleaseId = releaseId,
ActivityStatuses = new List<DeliverableActivityStatusInputModel>()
};
foreach (var act in deliverable.ConfiguredActivities)
{
input.ActivityStatuses.Add(new DeliverableActivityStatusInputModel { ActivityId = act.Id, HoursRemaining = 0 });
}
this.CreateDeliverableStatusRecords(input);
}
/// <summary>
/// MilestoneStatus and RemainingWorkHistory tables are updated by first deleting records with releaseId, MilestoneId, DelId, ProjId and ActId and then adding them again
/// with updated numbers
/// </summary>
/// <param name="obj"></param>
public void CreateDeliverableStatusRecords(StatusInputModel obj)
{
var conn = new SqlConnection("Data Source=localhost\\SQLENTERPRISE;Initial Catalog=Planner;Integrated Security=SSPI;MultipleActiveResultSets=true");
try
{
using (conn)
{
conn.Open();
var cmd = new SqlCommand("sp_insert_milestonestatus", conn);
cmd.Parameters.Add("@ReleaseId", System.Data.SqlDbType.Int).Value = obj.ReleaseId;
cmd.Parameters.Add("@MilestoneId", System.Data.SqlDbType.Int).Value = obj.MilestoneId;
cmd.Parameters.Add("@DeliverableId", System.Data.SqlDbType.Int).Value = obj.DeliverableId;
cmd.Parameters.Add("@ProjectId", System.Data.SqlDbType.Int).Value = obj.ProjectId;
cmd.Parameters.Add("@ActivityId", System.Data.SqlDbType.Int).Value = 0;
cmd.Parameters.Add("@HoursRemaining", System.Data.SqlDbType.Int).Value = 0;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// add to history table for reporting purposes
var cmd2 = new SqlCommand("sp_insert_remainingwork_history_table", conn);
cmd2.Parameters.Add("@ReleaseId", System.Data.SqlDbType.Int).Value = obj.ReleaseId;
cmd2.Parameters.Add("@MilestoneId", System.Data.SqlDbType.Int).Value = obj.MilestoneId;
cmd2.Parameters.Add("@DeliverableId", System.Data.SqlDbType.Int).Value = obj.DeliverableId;
cmd2.Parameters.Add("@ProjectId", System.Data.SqlDbType.Int).Value = obj.ProjectId;
cmd2.Parameters.Add("@ActivityId", System.Data.SqlDbType.Int).Value = 0;
cmd2.Parameters.Add("@HoursRemaining", System.Data.SqlDbType.Int).Value = 0;
cmd2.CommandType = System.Data.CommandType.StoredProcedure;
// completely renew the ProjectActivityStatuses for the Milestone Deliverables as set in the client app
var cmdDelCross = new SqlCommand(string.Format("DELETE FROM MilestoneStatus WHERE ReleaseId = {0} AND MilestoneId = {1} AND DeliverableId = {2} AND ProjectId = {3}", obj.ReleaseId, obj.MilestoneId, obj.DeliverableId, obj.ProjectId), conn);
cmdDelCross.ExecuteNonQuery();
foreach (var itm in obj.ActivityStatuses)
{
//cmd.Parameters["@ProjectId"].Value = itm.ProjectId;
cmd.Parameters["@ActivityId"].Value = itm.ActivityId;
cmd.Parameters["@HoursRemaining"].Value = itm.HoursRemaining;
cmd.ExecuteNonQuery();
cmd2.Parameters["@ActivityId"].Value = itm.ActivityId;
cmd2.Parameters["@HoursRemaining"].Value = itm.HoursRemaining;
cmd2.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw;
}
}
public void SaveDeliverableStatus(DeliverableStatusInputModel obj)
{
var input = new StatusInputModel { DeliverableId = obj.DeliverableId, MilestoneId = obj.MilestoneId, ReleaseId = obj.ReleaseId, ActivityStatuses = new List<DeliverableActivityStatusInputModel>() };
foreach (var proj in obj.Scope)
{
input.ProjectId = proj.Id;
foreach (var act in proj.Workload)
{
input.ActivityStatuses.Add(new DeliverableActivityStatusInputModel { ActivityId = act.Activity.Id, HoursRemaining = act.HoursRemaining });
}
this.CreateDeliverableStatusRecords(input);
input.ActivityStatuses.Clear();
}
}