private List<OutsideService> ComputeOutsideServiceCost(string connection, string sonum)
{
List<OutsideService> data = new List<OutsideService>();
string bookedQuoteVersion = "0";
string actualQuoteVersion = "0";
GetBookedQuoteVersion(connection, sonum, ref bookedQuoteVersion);
GetActualQuoteVersion(connection, sonum, ref actualQuoteVersion);
if (actualQuoteVersion != "0")
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString()))
{
string sqlScript = GenerateOutsideServiceQueryString(sonum, bookedQuoteVersion, actualQuoteVersion);
SqlCommand cmd = new SqlCommand(sqlScript, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OutsideService oscost = new OutsideService();
oscost.PartNo = reader["QD_Part_num"].ToString();
oscost.PartDescription = reader["QD_Descript"].ToString();
oscost.BookedQuantity = Convert.ToInt32(reader["Qty1"].ToString());
oscost.BookedUnitCost = Convert.ToDouble(reader["Ucost1"].ToString());
oscost.ActualQuantity = Convert.ToInt32(reader["Qty2"].ToString());
oscost.ActualUnitCost = Convert.ToDouble(reader["Ucost2"].ToString());
oscost.Vendor = reader["VE_Name"].ToString();
oscost.BookedSetupCost = Convert.ToDouble(reader["Setup1"].ToString());
oscost.ActualSetupCost = Convert.ToDouble(reader["Setup2"].ToString());
int partype = Convert.ToInt32(reader["SP_PartType"].ToString());
switch (partype)
{
case 0:
oscost.PartType = "Customer Purchased Part";
break;
case 1:
oscost.PartType = "Programming";
break;
case 2:
oscost.PartType = "Standard Purchase Part";
break;
case 3:
oscost.PartType = "Customer Price Adjustment";
break;
case 4:
oscost.PartType = "Labor Offload";
break;
case 5:
oscost.PartType = "Internal Cost Adjustment";
break;
case 6:
oscost.PartType = "Finished Fixture";
break;
default:
oscost.PartType = "Other";
break;
}
data.Add(oscost);
}
}
}
return data;
}
private void GenerateReport(IWorkbook workbook)
{
JobTracker jobtracker = new JobTracker();
List<JobTracker> projectList = jobtracker.GetDistinctProjectListIncludingForApproval(Convert.ToDateTime(txtBoxFrom.Text+ " 00:00:00"), Convert.ToDateTime(txtBoxTo.Text+" 23:59:59"));
int currentrow = 1;
IDataFormat format = workbook.CreateDataFormat();
ICellStyle fontBoldNoBorder = CreateSheetStyle(workbook, false, false, false, false, true, false, false, false,false);
ICellStyle fontBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
ICellStyle fontCurrencyBoldRigthBottom = CreateSheetStyle(workbook, false, false, true, true, true, false, false, false);
fontCurrencyBoldRigthBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontBoldTopBottom = CreateSheetStyle(workbook, false, true, false, true, true, false, false, false);
fontCurrencyBoldRigthBottom.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldRigthBottom.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontCurrencyBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
fontBoldAllBorder.Alignment = HorizontalAlignment.Center;
fontCurrencyBoldAllBorder.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontNormalBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontBoldAllBorder.VerticalAlignment = VerticalAlignment.Center;
ICellStyle fontNormalBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true,true, false, false, false, false);
ICellStyle fontCurrencyBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
fontCurrencyBorderLeftRight.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
ICellStyle fontCurrencyBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true, true, false, false, false, false);
fontCurrencyBorderLeftRightBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
//fontNormalBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontNormalBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontNormalBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
//fontCurrencyBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//fontCurrencyBoldRigthBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
ISheet sheetReport = workbook.GetSheet("Report");
foreach (JobTracker project in projectList)
{
//Running Total Variable
double runningNTApproved = 0;
double runningNTForApproval = 0;
double runningOTApproved = 0;
double runningOTForApproval = 0;
double runningNTApprovedCost = 0;
double runningNTForApprovalCost = 0;
double runningOTApprovedCost = 0;
double runningOTForApprovalCost = 0;
string jobheader = project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : "HW SO: " + project.HWNo;
jobheader += project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : jobheader == "" ? "SW SO: " + project.SWNo : "; SW SO: " + project.SWNo;
jobheader += project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : jobheader == "" ? "EVAL NO: " + project.EvalNo : "; EVAL NO: " + project.EvalNo;
currentrow += 2;
IRow row = sheetReport.CreateRow(currentrow++);
ICell cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue(jobheader);
for (int i = 1; i < 11; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
#region Header Upper Row
row = sheetReport.CreateRow(currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("JobStage");
cell = row.CreateCell(1);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
for (int i = 2; i < 5; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(5);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Labor Cost");
for (int i = 6; i < 9; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = fontBoldAllBorder;
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Total");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow -1, currentrow-1, 0, 10));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,1,4));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,5,8));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,9,10));
#endregion
#region Header Lower Row
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.CellStyle = fontBoldAllBorder;
for (int i = 0; i < 5; i+=4)
{
cell = row.CreateCell(1 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(Approved)");
cell = row.CreateCell(2 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Normal Time(For Approval)");
cell = row.CreateCell(3 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(Approved)");
cell = row.CreateCell(4 + i);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Overtime(For Approved)");
}
cell = row.CreateCell(9);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Time");
cell = row.CreateCell(10);
cell.CellStyle = fontBoldAllBorder;
cell.SetCellValue("Cost");
//Merging
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow - 1, currentrow, 0, 0));
#endregion
#region Data
List<JobTracker> uniqueJobType = jobtracker.GetUniqueComputedJobType(project.HWNo == null ? "" : project.HWNo.Trim(), project.HWNo == null ? "" : project.SWNo.Trim());
#region DETAIL Page
ISheet detailSheet = workbook.CreateSheet(jobheader.Replace(":",""));
int detailRowCount = 0;
IRow detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue(jobheader);
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRowCount++;
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Labor Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
detailRow = detailSheet.CreateRow(detailRowCount++);
string[] detailHeader = { "Department","Name", "Job Task", "Start Time", "End Time","Date", "Remarks","Status", "Normal Time","Overtime", "Normal Time Cost","Overtime Cost" };
for (int i = 0; i < 12; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(detailHeader[i]);
}
for (int i = 0; i < uniqueJobType.Count; i++)
{
row = sheetReport.CreateRow(++currentrow);
cell = row.CreateCell(0);
cell.SetCellValue(uniqueJobType[i].jobtype);
if (i < uniqueJobType.Count - 1)
{
cell.CellStyle = fontNormalBorderLeftRight;
}
else
{
cell.CellStyle = fontNormalBorderLeftRightBottom;
}
List<JobTracker> data = jobtracker.GetJobTrackerListWithJobTypeIdHWSO(Convert.ToInt32(uniqueJobType[i].JobTypeId), project.HWNo == null ? "" : project.HWNo.Trim(), project.SWNo == null ? "" : project.SWNo.Trim(), true);
double ntApproved = 0;
double ntForApproval = 0;
double otApproved = 0;
double otForApproval = 0;
double ntApprovedCost = 0;
double ntForApprovalCost = 0;
double otApprovedCost = 0;
double otForApprovalCost = 0;
for (int x = 0; x < data.Count;x++ )
{
detailRow = detailSheet.CreateRow(detailRowCount++);
if (data[x].Status == "Approved")
{
ntApproved += data[x].normalmins;
otApproved += data[x].otmins;
ntApprovedCost += data[x].normalcost;
otApprovedCost += data[x].otcost;
}
else if (data[x].Status == "For Approval")
{
ntForApproval += data[x].normalmins;
otForApproval += data[x].otmins;
ntForApprovalCost += data[x].normalcost;
otForApprovalCost += data[x].otcost;
}
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(data[x].department);
else if (y == 1)
detailCell.SetCellValue(data[x].fullname);
else if (y == 2)
detailCell.SetCellValue(data[x].jobtype);
else if (y == 3)
detailCell.SetCellValue(Convert.ToDateTime(data[x].StartTime).ToString("hh:mm tt"));
else if (y == 4)
detailCell.SetCellValue(Convert.ToDateTime(data[x].EndTime).ToString("hh:mm tt"));
else if (y == 5)
detailCell.SetCellValue(Convert.ToDateTime(data[x].ScheduleDate).ToString("dd-MMM-yyyy"));
else if (y == 6)
detailCell.SetCellValue(data[x].Remarks);
else if (y == 7)
detailCell.SetCellValue(data[x].Status);
else if (y == 8)
detailCell.SetCellValue(data[x].normalhours.Trim() == "0 min" ? "-" : data[x].normalhours);
else if (y == 9)
detailCell.SetCellValue(data[x].othours.Trim() == "0 min" ? "-" : data[x].othours);
else if (y == 10)
detailCell.SetCellValue(data[x].normalcost);
else if (y == 11)
detailCell.SetCellValue(data[x].otcost);
if (x == data.Count - 1 && i == uniqueJobType.Count -1)
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
else
{
if (y < 10)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
}
}
#endregion
#region GETTING THE TIME
//Running Time
runningNTApproved += ntApproved;
runningNTForApproval += ntForApproval;
runningOTApproved += otApproved;
runningOTForApproval += otForApproval;
runningNTApprovedCost += ntApprovedCost;
runningNTForApprovalCost += ntForApprovalCost;
runningOTApprovedCost += otApprovedCost;
runningOTForApprovalCost += otForApprovalCost;
string NTApproved = GenerateTimeConsumed(ntApproved);
string NTForApproval = GenerateTimeConsumed(ntForApproval);
string OTApproved = GenerateTimeConsumed(otApproved);
string OTForApproval = GenerateTimeConsumed(otForApproval);
string StotalTime = GenerateTimeConsumed(ntApproved + otApproved + ntForApproval + otForApproval);
#endregion
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue(NTApproved.Trim() == "0 min" ? "-" : NTApproved.Trim());
else if (j == 2)
cell.SetCellValue(NTForApproval.Trim() == "0 min" ? "-" : NTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(OTApproved.Trim() == "0 min" ? "-" : OTApproved.Trim());
else if (j == 4)
cell.SetCellValue(OTForApproval.Trim() == "0 min" ? "-" : OTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(ntApprovedCost);
else if (j == 6)
cell.SetCellValue(ntForApprovalCost);
else if (j == 7)
cell.SetCellValue(otApprovedCost);
else if (j == 8)
cell.SetCellValue(otForApprovalCost);
else if (j == 9)
cell.SetCellValue(StotalTime.Trim() == "0 min" ? "-" : StotalTime.Trim());
else if (j == 10)
cell.SetCellValue(ntForApprovalCost + ntApprovedCost + otApprovedCost + otForApprovalCost);
if (i < uniqueJobType.Count - 1)
{
if(j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRight;
else
cell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (j < 5 || j == 9)
cell.CellStyle = fontNormalBorderLeftRightBottom;
else
cell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
}
#endregion
#region TOTAL, MATERIAL COST, OUTSIDE SERVICE
string projNTApproved = GenerateTimeConsumed(runningNTApproved);
string projNTForApproval = GenerateTimeConsumed(runningNTForApproval);
string projOTApproved = GenerateTimeConsumed(runningOTApproved);
string projOTForApproval = GenerateTimeConsumed(runningOTForApproval);
string projTotalTime = GenerateTimeConsumed(runningNTApproved + runningNTForApproval + runningOTApproved + runningOTForApproval);
string projNTTotal = GenerateTimeConsumed(runningNTApproved + runningNTForApproval);
string projOTTotal = GenerateTimeConsumed(runningOTApproved + runningOTForApproval);
#region DETAIL TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 12; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 8)
detailCell.SetCellValue(projNTTotal.Trim() == "0 min" ? "-" : projNTTotal.Trim());
else if (y == 9)
detailCell.SetCellValue(projOTTotal.Trim() == "0 min" ? "-" : projOTTotal.Trim());
else if (y == 10)
detailCell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost);
else if (y == 11)
detailCell.SetCellValue(runningOTApprovedCost + runningOTForApprovalCost);
if (y < 10)
detailCell.CellStyle = fontBoldAllBorder;
else
detailCell.CellStyle = fontCurrencyBoldAllBorder;
}
#endregion
#region MATERIAL COST
MaterialCost materialCost = new MaterialCost();
List<MaterialCost> projMaterialCost = new List<MaterialCost>();
projMaterialCost = materialCost.GetMaterialCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalmaterialBookedCost = 0;
double totalmaterialActualCost = 0;
if (projMaterialCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Material Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] materialHeader = { "Item", "Description", "Part Number", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost","Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 10; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(materialHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projMaterialCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projMaterialCost[i].BookedQuantity * projMaterialCost[i].BookedUnitCost;
double actualcost = projMaterialCost[i].ActualUnitCost * projMaterialCost[i].ActualQuantity;
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projMaterialCost[i].PartDescription);
else if (y == 2)
detailCell.SetCellValue(projMaterialCost[i].PartNo);
else if (y == 3)
detailCell.SetCellValue(projMaterialCost[i].BookedQuantity);
else if (y == 4)
detailCell.SetCellValue(projMaterialCost[i].BookedUnitCost);
else if (y == 5)
detailCell.SetCellValue(bookcost);
else if (y == 6)
detailCell.SetCellValue(projMaterialCost[i].ActualQuantity);
else if (y == 7)
detailCell.SetCellValue(projMaterialCost[i].ActualUnitCost);
else if (y == 8)
detailCell.SetCellValue(actualcost);
else if (y == 9)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 4 || y == 6)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalmaterialBookedCost += bookcost;
totalmaterialActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 10; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 5)
detailCell.SetCellValue(totalmaterialBookedCost);
else if (y == 8)
detailCell.SetCellValue(totalmaterialActualCost);
else if (y == 9)
detailCell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (y == 5 || y == 8 || y == 9)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
#region OUTSIDE SERVICE
OutsideService outsideServiceCost = new OutsideService();
List<OutsideService> projOutServCost = new List<OutsideService>();
projOutServCost = outsideServiceCost.GetOutsideServiceCost(project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : project.HWNo, project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : project.SWNo, project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : project.EvalNo);
double totalOutServBookedCost = 0;
double totalOutServActualCost = 0;
if (projOutServCost.Count > 0)
{
detailRowCount++; //Blank Row
#region HEADER
detailRow = detailSheet.CreateRow(detailRowCount++);
detailRow.CreateCell(0).SetCellValue("Outside Service Cost");
detailRow.GetCell(0).CellStyle = fontBoldNoBorder;
string[] outsideServiceHeader = { "Item","Type", "Description", "Vendor", "Booked Quantity", "Booked Unit Cost", "Booked Extended Cost", "Actual Quantity", "Actual Unit Cost", "Actual Extended Cost", "Cost Overrun (Underrun)" };
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int i = 0; i < 11; i++)
{
ICell detailCell = detailRow.CreateCell(i);
detailCell.CellStyle = fontBoldAllBorder;
detailCell.SetCellValue(outsideServiceHeader[i]);
}
#endregion
#region DETAIL
for (int i = 0; i < projOutServCost.Count; i++)
{
detailRow = detailSheet.CreateRow(detailRowCount++);
double bookcost = projOutServCost[i].BookedQuantity * projOutServCost[i].BookedUnitCost;
double actualcost = projOutServCost[i].ActualUnitCost * projOutServCost[i].ActualQuantity;
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue(i + 1);
else if (y == 1)
detailCell.SetCellValue(projOutServCost[i].PartType);
else if (y == 2)
detailCell.SetCellValue(projOutServCost[i].PartDescription);
else if (y == 3)
detailCell.SetCellValue(projOutServCost[i].Vendor);
else if (y == 4)
detailCell.SetCellValue(projOutServCost[i].BookedQuantity);
else if (y == 5)
detailCell.SetCellValue(projOutServCost[i].BookedUnitCost);
else if (y == 6)
detailCell.SetCellValue(bookcost);
else if (y == 7)
detailCell.SetCellValue(projOutServCost[i].ActualQuantity);
else if (y == 8)
detailCell.SetCellValue(projOutServCost[i].ActualUnitCost);
else if (y == 9)
detailCell.SetCellValue(actualcost);
else if (y == 10)
detailCell.SetCellValue(actualcost - bookcost);
if (i < projMaterialCost.Count - 1)
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRight;
else
detailCell.CellStyle = fontCurrencyBorderLeftRight;
}
else
{
if (y < 5 || y == 7)
detailCell.CellStyle = fontNormalBorderLeftRightBottom;
else
detailCell.CellStyle = fontCurrencyBorderLeftRightBottom;
}
}
totalOutServBookedCost += bookcost;
totalOutServActualCost += actualcost;
}
#endregion
#region TOTAL
detailRow = detailSheet.CreateRow(detailRowCount++);
for (int y = 0; y < 11; y++)
{
ICell detailCell = detailRow.CreateCell(y);
if (y == 0)
detailCell.SetCellValue("TOTAL");
else if (y == 6)
detailCell.SetCellValue(totalOutServBookedCost);
else if (y == 9)
detailCell.SetCellValue(totalOutServActualCost);
else if (y == 10)
detailCell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (y == 6 || y == 9 || y == 10)
detailCell.CellStyle = fontCurrencyBoldAllBorder;
else
detailCell.CellStyle = fontBoldAllBorder;
}
#endregion
}
#endregion
row = sheetReport.CreateRow(++currentrow);
for (int j = 0; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 0)
cell.SetCellValue("Total Labor Cost");
else if (j == 1)
cell.SetCellValue(projNTApproved.Trim() == "0 min" ? "-" : projNTApproved.Trim());
else if (j == 2)
cell.SetCellValue(projNTForApproval.Trim() == "0 min" ? "-" : projNTForApproval.Trim());
else if (j == 3)
cell.SetCellValue(projOTApproved.Trim() == "0 min" ? "-" : projOTApproved.Trim());
else if (j == 4)
cell.SetCellValue(projOTForApproval.Trim() == "0 min" ? "-" : projOTForApproval.Trim());
else if (j == 5)
cell.SetCellValue(runningNTApprovedCost);
else if (j == 6)
cell.SetCellValue(runningNTForApprovalCost);
else if (j == 7)
cell.SetCellValue(runningOTApprovedCost);
else if (j == 8)
cell.SetCellValue(runningOTForApprovalCost);
else if (j == 9)
cell.SetCellValue(projTotalTime.Trim() == "0 min" ? "-" : projTotalTime.Trim());
else if (j == 10)
cell.SetCellValue(runningNTApprovedCost + runningNTForApprovalCost + runningOTApprovedCost + runningOTForApprovalCost);
if (j < 5 || j == 9)
cell.CellStyle = fontBoldAllBorder;
else
cell.CellStyle = fontCurrencyBoldAllBorder;
}
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Material Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalmaterialBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalmaterialActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalmaterialActualCost - totalmaterialBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
row = sheetReport.CreateRow(++currentrow);
row.CreateCell(0).SetCellValue("Outside Service Cost");
row.GetCell(0).CellStyle = fontBoldAllBorder;
for (int j = 1; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 1)
cell.SetCellValue("Booked Cost:");
else if (j == 2)
cell.SetCellValue(totalOutServBookedCost);
else if (j == 4)
cell.SetCellValue("Actual Cost:");
else if (j == 5)
cell.SetCellValue(totalOutServActualCost);
else if (j == 7)
cell.SetCellValue("Cost Overrun (Underrun)");
else if (j == 8)
cell.SetCellValue(totalOutServActualCost - totalOutServBookedCost);
if (j == 1 || j == 4 || j == 7)
cell.CellStyle = fontBoldTopBottom;
else
cell.CellStyle = fontCurrencyBoldRigthBottom;
}
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 2, 3));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 5, 6));
sheetReport.AddMergedRegion(new CellRangeAddress(currentrow, currentrow, 8, 10));
#endregion
}
}