private int FillWorkloadData(List <WorkloadEntity> workloads, int startrow, bool isTester)
{
int firstrow = startrow;
int standardWorkingDays = TFS.Utility.GetStandardWorkingDays(this.project.Name, TFS.Utility.GetBestIteration(this.project.Name));
var orderedLoads = GetOrderedWorkloads(workloads);
var allbugs = Bug.GetAddedBugsByIteration(this.project.Name, API.TFS.Utility.GetBestIteration(this.project.Name));
object[,] arr = new object[orderedLoads.Count(), 35];
int line = 0;
var capacities = TFS.Agile.Capacity.GetIterationCapacitiesForTeamMember(this.project.Name, TFS.Utility.GetBestIteration(this.project.Name).Id);
foreach (var load in orderedLoads)
{
string person = Utility.GetPersonName(load.Key);
int bugCount = 0;
if (isTester)
{
bugCount = allbugs.Where(bug => bug.DiscoveryUser == load.Key).Count();
}
else
{
bugCount = allbugs.Where(bug => bug.AssignedTo == load.Key).Count();
}
double leavetime = load.Where(wl => wl.Type == "请假").Sum(wl => wl.SumHours);
var dev = load.Where(wl => wl.SupperType == "研发");
double devtime = dev.Sum(wl => wl.SumHours);
double devtime1 = dev.Where(wl => wl.Type == "开发").Sum(wl => wl.SumHours);
double devtime2 = dev.Where(wl => wl.Type == "需求").Sum(wl => wl.SumHours);
double devtime3 = dev.Where(wl => wl.Type == "设计").Sum(wl => wl.SumHours);
double devtime4 = dev.Where(wl => wl.Type == "测试设计").Sum(wl => wl.SumHours);
double devtime5 = dev.Where(wl => wl.Type == "测试执行").Sum(wl => wl.SumHours);
double devtime6 = dev.Where(wl => wl.Type == "技术支持").Sum(wl => wl.SumHours);
double devtime7 = devtime - (devtime1 + devtime2 + devtime3 + devtime4 + devtime5 + devtime6);
double mgrtime = load.Where(wl => wl.SupperType == "管理").Sum(wl => wl.SumHours);
double oprtime = load.Where(wl => wl.SupperType == "运维").Sum(wl => wl.SumHours);
double doctime = load.Where(wl => wl.SupperType == "文档").Sum(wl => wl.SumHours);
double studytime = load.Where(wl => wl.SupperType == "学习交流").Sum(wl => wl.SumHours);
double presalestime = load.Where(wl => wl.SupperType == "售前/推广").Sum(wl => wl.SumHours);
double othertime = load.Where(wl => wl.SupperType == "其他").Sum(wl => wl.SumHours) - leavetime;
arr[line, 0] = person;
//if (capacities.ContainsKey(load.Key))
//{
// arr[line, 1] = capacities[load.Key] * standardWorkingDays;
//}
//else
//{
// arr[line, 1] = 9999;// "未在迭代settings中定义【"+Utility.GetPersonName(load.Key)+"】的容量";
//}
arr[line, 1] = standardWorkingDays * 8;//capacities[load.Key] * standardWorkingDays;// standardWorkingDays * 8;
arr[line, 2] = load.Where(wl => wl.Type != "请假").Sum(wl => wl.SumHours);
arr[line, 3] = leavetime;
arr[line, 4] = String.Format("=D{0}/C{0}", startrow);
arr[line, 5] = bugCount;
arr[line, 6] = String.Format("=G{0}/(D{0}/8)", startrow);
arr[line, 7] = devtime / (load.Sum(wl => wl.SumHours) - leavetime);
arr[line, 8] = devtime1;
arr[line, 9] = String.Format("=IF(J{0}<>0,J{0}/D{0}, \"\"", startrow);
arr[line, 10] = devtime2;
arr[line, 11] = String.Format("=IF(L{0}<>0,L{0}/D{0}, \"\"", startrow);
arr[line, 12] = devtime3;
arr[line, 13] = String.Format("=IF(N{0}<>0,N{0}/D{0}, \"\"", startrow);
arr[line, 14] = devtime4;
arr[line, 15] = String.Format("=IF(P{0}<>0,P{0}/D{0}, \"\"", startrow);
arr[line, 16] = devtime5;
arr[line, 17] = String.Format("=IF(R{0}<>0,R{0}/D{0}, \"\"", startrow);
arr[line, 18] = devtime6;
arr[line, 19] = String.Format("=IF(T{0}<>0,T{0}/D{0}, \"\"", startrow);
arr[line, 20] = devtime7;
arr[line, 21] = String.Format("=IF(V{0}<>0,V{0}/D{0}, \"\"", startrow);
arr[line, 22] = mgrtime;
arr[line, 23] = String.Format("=IF(X{0}<>0,X{0}/D{0}, \"\"", startrow);
arr[line, 24] = oprtime;
arr[line, 25] = String.Format("=IF(Z{0}<>0,Z{0}/D{0}, \"\"", startrow);
arr[line, 26] = doctime;
arr[line, 27] = String.Format("=IF(AB{0}<>0,AB{0}/D{0}, \"\"", startrow);
arr[line, 28] = studytime;
arr[line, 29] = String.Format("=IF(AD{0}<>0,AD{0}/D{0}, \"\"", startrow);
arr[line, 30] = presalestime;
arr[line, 31] = String.Format("=IF(AF{0}<>0,AF{0}/D{0}, \"\"", startrow);
arr[line, 32] = othertime;
arr[line, 33] = String.Format("=IF(AH{0}<>0,AH{0}/D{0}, \"\"", startrow);
startrow++;
line++;
}
ExcelInterop.Range devRange = sheet.Range[sheet.Cells[firstrow, "B"], sheet.Cells[startrow - 1, "AI"]];
Utility.AddNativieResource(devRange);
devRange.Value2 = arr;
devRange.HorizontalAlignment = ExcelInterop.XlHAlign.xlHAlignRight;
devRange.WrapText = true;
devRange.RowHeight = 20;
devRange.ColumnWidth = 7;
Utility.SetCellBorder(devRange);
Utility.SetCellAlignAndWrap(sheet.Range[sheet.Cells[firstrow, "B"], sheet.Cells[firstrow + orderedLoads.Count() - 1, "B"]]);
Utility.SetCellPercentFormat(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "D"], sheet.Cells[startrow - 1, "D"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "H"], sheet.Cells[startrow - 1, "H"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "I"], sheet.Cells[startrow - 1, "I"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "K"], sheet.Cells[startrow - 1, "K"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "M"], sheet.Cells[startrow - 1, "M"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "O"], sheet.Cells[startrow - 1, "O"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "Q"], sheet.Cells[startrow - 1, "Q"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "S"], sheet.Cells[startrow - 1, "S"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "U"], sheet.Cells[startrow - 1, "U"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "W"], sheet.Cells[startrow - 1, "W"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "Y"], sheet.Cells[startrow - 1, "Y"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AA"], sheet.Cells[startrow - 1, "AA"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AC"], sheet.Cells[startrow - 1, "AC"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AE"], sheet.Cells[startrow - 1, "AE"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AG"], sheet.Cells[startrow - 1, "AG"]]);
Utility.SetCellGreenColor(sheet.Range[sheet.Cells[firstrow, "AI"], sheet.Cells[startrow - 1, "AI"]]);
ExcelInterop.Range bugrange = sheet.Range[sheet.Cells[firstrow, "H"], sheet.Cells[startrow - 1, "H"]];
Utility.AddNativieResource(bugrange);
bugrange.NumberFormat = "#0.00";
for (int col = 9; col <= 35; col += 2)
{
Utility.SetCellPercentFormat(sheet, firstrow, col, startrow - 1, col);
}
Utility.SetFormatBigger(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]], 1.20d);
Utility.SetFormatSmaller(sheet.Range[sheet.Cells[firstrow, "F"], sheet.Cells[startrow - 1, "F"]], 1.00d);
Utility.SetFormatSmaller(sheet.Range[sheet.Cells[firstrow, "I"], sheet.Cells[startrow - 1, "I"]], 0.60d);
return(startrow);
}