private ProjectOverviewCollection GetOpportunityOverviewCollection()
{
var extraColumns = new Dictionary <string, string>
{
{ "CustomerName", "Customer == null?null:Customer.Name" },
{ "ProjectWeekReports", "ProjectWeekReports" },
{ "ProjectTasks", "ProjectTasks" }
};
var predicate = GetPredicate();
var projects = DynamicDataServiceContext.GetBatchObjects("Project", predicate, extraColumns);
DateTime startDate, endDate;
GetDataRange(DataRangeValue, out startDate, out endDate);
var overviewCollection = new ProjectOverviewCollection();
if (projects == null)
{
return(overviewCollection);
}
foreach (var project in projects)
{
var overview = new ProjectOverview();
var dyanmicProject = project.AsDyanmic();
overview.ProjectId = dyanmicProject.ProjectId;
overview.Name = dyanmicProject.Name;
overview.CustomerName = dyanmicProject.CustomerName;
//
string projectTypeCode = GetLocalizedPickListValue("Project", "ProjectTypeCode", dyanmicProject.ProjectTypeCode);
overview.Type = GetLocalizedCaption("ProjectTypeCode") + (string.IsNullOrEmpty(projectTypeCode) ? "(TBD)" : projectTypeCode);
//
string technologyCode = GetLocalizedPickListValue("Project", "TechnologyCode", dyanmicProject.TechnologyCode);
overview.Technology = GetLocalizedCaption("TechnologyCode") + (string.IsNullOrEmpty(technologyCode) ? "(TBD)" : technologyCode);
//
string probabilityCode = GetLocalizedPickListValue("Project", "ProbabilityCode", dyanmicProject.ProbabilityCode);
overview.Probability = GetLocalizedCaption("ProbabilityCode") + (string.IsNullOrEmpty(probabilityCode) ? "(TBD)" : probabilityCode);
IList projectTasks = dyanmicProject.ProjectTasks;
projectTasks = projectTasks.AsQueryable().Where("IsDeleted = false").ToArrayList();
IList Owners = projectTasks.AsQueryable().Where("OwnerId != null").Select("OwnerId").Cast <Guid>().ToList();
List <object> taskHistory = GetTaskHistory(startDate, endDate, projectTasks.AsQueryable().Select("TaskId").Cast <object>().ToList()) as List <object>;
string owners = GetResponsibilityPerson(Owners);
overview.ResponsibilityPerson = GetLocalizedCaption("ProjectMember") + (string.IsNullOrEmpty(owners) ? "(TBD)" : owners);
overview.ProjectSummaryInfo = overview.Type + " " + overview.Technology + " " + overview.Probability + " " + overview.ResponsibilityPerson;
overview.LatestFeadbackOn = dyanmicProject.LatestFeadbackOn;
overview.EstimatedEndDate = dyanmicProject.EstimatedEndDate;
//total
overview.TotalQuoteWorkHours = projectTasks.AsQueryable().Select("QuoteWorkHours").Cast <double?>().Sum();
overview.TotalActualWorkHours = projectTasks.AsQueryable().Select("ActualWorkHours").Cast <double?>().Sum();
overview.TotalActualInput = projectTasks.AsQueryable().Select("ActualInput").Cast <double?>().Sum();
overview.TotalEffort = projectTasks.AsQueryable().Select("Effort").Cast <double?>().Sum();
overview.TotalRemainderTime = overview.TotalActualWorkHours - overview.TotalEffort;
overview.TotalEvaluateExactlyRate = overview.TotalActualWorkHours == 0 ? 0 : overview.TotalQuoteWorkHours / overview.TotalActualWorkHours;
overview.TotalInputEffortRate = overview.TotalActualInput == 0 ? 0 : overview.TotalEffort / overview.TotalActualInput;
//week
overview.WeekQuoteWorkHours = projectTasks.AsQueryable().Where("StartDate<=@1 && (EndDate==null || EndDate>=@0)", startDate, endDate)
.Select("QuoteWorkHours").Cast <double?>().Sum();
overview.WeekActualWorkHours = projectTasks.AsQueryable().Where("StartDate<=@1 && (EndDate == null || EndDate>=@0)", startDate, endDate)
.Select("ActualWorkHours").Cast <double?>().Sum();
overview.WeekActualInput = taskHistory == null?0:taskHistory.Select(c => c.AsDyanmic().ActualInput).Cast <double?>().Sum();
overview.WeekEffort = taskHistory == null?0:taskHistory.Select(c => c.AsDyanmic().Effort).Cast <double?>().Sum();
overview.WeekRemainderTime = overview.WeekActualWorkHours - overview.WeekEffort;
//rate
overview.EvaluateExactlyRate = overview.WeekActualWorkHours == 0?0:overview.WeekQuoteWorkHours / overview.WeekActualWorkHours;
overview.InputEffortRate = overview.WeekActualInput == 0?0: overview.WeekEffort / overview.WeekActualInput;
overview.ActualStartDate = dyanmicProject.ActualStartDate;
// ProjectStatus and Quality from ProjectWeekReport
IList projectWeekReports = dyanmicProject.ProjectWeekReports;
projectWeekReports = projectWeekReports.AsQueryable().Where("RecordOn>=@0 && RecordOn<=@1", startDate, endDate).ToArrayList();
string projectStatus = "";
string quality = "";
foreach (var weekReport in projectWeekReports)
{
projectStatus += GetHtmlText(weekReport.AsDyanmic().CriteriaProgress);
quality += GetHtmlText(weekReport.AsDyanmic().CriteriaQuality);
}
overview.ProjectStatus = projectStatus;
overview.Quality = quality;
overview.Objective = dyanmicProject.Objective;
overviewCollection.Add(overview);
}
return(overviewCollection);
}
private OpportunityOverviewCollection GetOpportunityOverviewCollection()
{
var overviewCollection = new OpportunityOverviewCollection();
var extraColumns = new Dictionary <string, string>
{
{ "OwnerName", "Owner == null?null:Owner.FullName" },
{ "TechnicianName", "Technician == null?null:Technician.FullName" },
{ "Contracts", "Contracts" },
{ "OriginatingLeadName", "OriginatingLead == null?null:OriginatingLead.Subject" },
{ "CustomerName", "Customer == null?null:Customer.Name" }
};
var predicate = GetPredicate();
var opportunities = DynamicDataServiceContext.GetBatchObjects("Opportunity", predicate, extraColumns);
if (opportunities == null)
{
return(overviewCollection);
}
var notes = GetNotes();
var timetrackings = DynamicDataServiceContext.GetObjects("TimeTracking");
DateTime startDate, endDate;
GetDataRange(DataRangeValue, out startDate, out endDate);
foreach (var opportunity in opportunities)
{
var overview = new OpportunityOverview();
SetPropertyValue(opportunity, "OpportunityId", overview, "OpportunityId");
SetPropertyValue(opportunity, "Name", overview, "Name");
SetPropertyValue(opportunity, "CustomerName", overview, "CustomerName");
var typeCode = (int?)GetPropertyValue(opportunity, "ProjectTypeCode"); //Type
if (typeCode != null)
{
overview.Type = GetLocalizedPickListValue("Opportunity", "ProjectTypeCode", typeCode.Value);
}
var technologyCode = (int?)GetPropertyValue(opportunity, "TechnologyCode"); //Technology
if (technologyCode != null)
{
overview.Technology = GetLocalizedPickListValue("Opportunity", "TechnologyCode",
technologyCode.Value);
}
SetPropertyValue(opportunity, "CloseProbability", overview, "Probability");
SetPropertyValue(opportunity, "LatestFeedbackOn", overview, "LatestFeadbackOn");
SetPropertyValue(opportunity, "ExpectedStartOn", overview, "ExpectedStartDate");
SetPropertyValue(opportunity, "EstimatedWorkAmount", overview, "EstimatedWorkAmount");
SetPropertyValue(opportunity, "ClosedTime", overview, "ClosedDate");
var statusCode = (int?)GetPropertyValue(opportunity, "StatusCode"); //Status
if (statusCode != null)
{
overview.Status = GetLocalizedPickListValue("Opportunity", "StatusCode", statusCode.Value);
}
SetPropertyValue(opportunity, "ClosedTime", overview, "ClosedDate");
var contracts = GetPropertyValue(opportunity, "Contracts") as IList; //ContractTotal
if (contracts != null)
{
var contractTotal = contracts.AsQueryable().Select("TotalPrice").Cast <decimal?>().Sum();
overview.ContractTotal = contractTotal;
}
SetPropertyValue(opportunity, "OwnerName", overview, "SaleName");
var opportunityId = (Guid)GetPropertyValue(opportunity, "OpportunityId");
var timeTrackingRecords = timetrackings.AsQueryable().Where("OpportunityId = @0", opportunityId);
overview.SaleTotalEfforts =
timeTrackingRecords.Where("TypeCode= 1").Select("Effort").Cast <double?>().Sum();
overview.SaleCurrentEfforts =
timeTrackingRecords.Where("TypeCode= 1 && RecordOn >= @0 && RecordOn <= @1", startDate, endDate)
.Select("Effort").Cast <double?>().Sum();
SetPropertyValue(opportunity, "TechnicianName", overview, "TechnicianName");
overview.TechnicianTotalEfforts =
timeTrackingRecords.Where("TypeCode= 2 || TypeCode= 3").Select("Effort").Cast <double?>().Sum();
overview.TechnicianCurrentEfforts =
timeTrackingRecords.Where("(TypeCode= 2 || TypeCode= 3) && RecordOn >= @0 && RecordOn <= @1", startDate, endDate).
Select("Effort").Cast <double?>().Sum();
if (overview.EstimatedWorkAmount > 0)
{
overview.CostRatio = overview.TechnicianTotalEfforts / overview.EstimatedWorkAmount;
}
SetPropertyValue(opportunity, "Description", overview, "Description");
var noteObjects =
notes.AsQueryable().Where("ObjectId = @0", opportunityId).OrderBy("CreatedOn descending").
ToArrayList();
foreach (var noteObject in noteObjects)
{
var note = new Note();
var createdById = (Guid?)GetPropertyValue(noteObject, "CreatedById");
if (createdById != null)
{
var creator = _users.AsQueryable().Where("UserId = @0", createdById)._First();
if (creator != null)
{
note.CreatedBy = (string)GetPropertyValue(creator, "FullName");
}
}
var subject = (string)GetPropertyValue(noteObject, "Subject");
var text = (string)GetPropertyValue(noteObject, "NoteText");
note.NoteText = string.Format("[{0}] {1}", subject, text);
SetPropertyValue(noteObject, "CreatedOn", note, "CreatedOn");
overview.Add(note);
}
overviewCollection.Add(overview);
}
return(overviewCollection);
}