566 lines
37 KiB
C#
566 lines
37 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.IO;
|
|||
|
|
using System.Web;
|
|||
|
|
using System.Web.UI.DataVisualization.Charting;
|
|||
|
|
using System.Drawing;
|
|||
|
|
using NPOI.SS.UserModel;
|
|||
|
|
using NPOI.SS.Util;
|
|||
|
|
using NPOI.XSSF.UserModel;
|
|||
|
|
using Resources;
|
|||
|
|
using Weee.Models;
|
|||
|
|
using Weee.Models.ExtensionMethods;
|
|||
|
|
|
|||
|
|
namespace Weee.Supports
|
|||
|
|
{
|
|||
|
|
public class ProductLCAInventoryExcelExporter : ExcelExporter
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
public void InsertCO2Data(IWorkbook workbook, ISheet sheet, ProductLCA productLca , out MemoryStream imageStream)
|
|||
|
|
{
|
|||
|
|
var grayColor = new XSSFColor(Color.LightGray);
|
|||
|
|
var orangeColor = new XSSFColor(Color.Orange);
|
|||
|
|
var greenColor = new XSSFColor(Color.Green);
|
|||
|
|
var orangeHeaderStyle = CreateCommonStyleWithBorder(workbook, orangeColor);
|
|||
|
|
var greenHeaderStyle = CreateCommonStyleWithBorder(workbook, greenColor);
|
|||
|
|
var grayHeaderStyle = CreateCommonStyleWithBorder(workbook, grayColor);
|
|||
|
|
var borderStyle = CreateCommonStyleWithBorder(workbook);
|
|||
|
|
var noneBorderStyle = CreateCommonStyleWithoutBorder(workbook);
|
|||
|
|
var currentRow = 0;
|
|||
|
|
|
|||
|
|
InsertCO2InventoryCaption(sheet, productLca);
|
|||
|
|
SetCellStyle(sheet, noneBorderStyle, currentRow, currentRow, 0, 16);
|
|||
|
|
SetCellStyle(sheet, orangeHeaderStyle, currentRow, currentRow, 9, 12);
|
|||
|
|
SetCellStyle(sheet, greenHeaderStyle, currentRow, currentRow, 13, 16);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
string[] inventoryHeader = new string[] { Resource.Phase, Resource.Items, Resource.DataType, Resource.PartNumber,
|
|||
|
|
Resource.SupplierName, Resource.DescriptionAndExplanation, Resource.BomWeight,
|
|||
|
|
Resource.UnitFunctionProducts, Resource.DuringInterrogation, Resource.ScopeOnePrimaryData,
|
|||
|
|
Resource.ScopeTwoPrimaryData, Resource.ScopeThreePrimaryData, Resource.SumOfPrimaryPartsCarbonFootprint,
|
|||
|
|
Resource.SecondaryDataOfUnitPartProduction, Resource.LCAResultCO2, Resource.TransportainGToDCarbonEmission,
|
|||
|
|
Resource.SumOfSecondaryPartsCarbonFootprint, Resource.BomQuantity, Resource.PartsCarbonFootprint,
|
|||
|
|
Resource.UnitPartsWeightProportion, Resource.UnitPartsCarbonFootprintProportion };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, inventoryHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, inventoryHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
var result1 = InsertCO2Inventory(sheet, productLca.Materials.ToList(), currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow + productLca.Materials.Count(), 0, inventoryHeader.Length - 1);
|
|||
|
|
currentRow += productLca.Materials.Count() + 3;
|
|||
|
|
|
|||
|
|
string[] productCO2DataInManufacturingHeader = new string[] { Resource.ProductInManufacturingStage, Resource.Items, Resource.DataType, Resource.AssemblyFactoryName,
|
|||
|
|
Resource.MaterialPartNumber, Resource.ProductName, Resource.UnitFunctionProducts, Resource.DuringInterrogation,
|
|||
|
|
Resource.ScopeOnePrimaryData, Resource.ScopeTwoPrimaryData, Resource.ScopeThreePrimaryData,
|
|||
|
|
Resource.SumOfPrimaryManufacturingCarbonFootprint, Resource.SecondaryDataOfManufacturing,
|
|||
|
|
Resource.LCAResultCO2, Resource.SumOfSecondaryManufacturingCarbonFootprint, Resource.ManufacturingCarbonFootprint,
|
|||
|
|
Resource.RemarkOfDistributeDescription };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, productCO2DataInManufacturingHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, productCO2DataInManufacturingHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
var result2 = InsertProductCO2DataInManufacturing(sheet, productLca, currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 0, productCO2DataInManufacturingHeader.Length - 1);
|
|||
|
|
currentRow += 3;
|
|||
|
|
|
|||
|
|
string[] productCO2DataInDeliveryHeader = new string[] { Resource.ProductDeliveryStage, Resource.Items, Resource.DataType, Resource.AssemblyFactoryName,
|
|||
|
|
Resource.MaterialPartNumber, Resource.ProductName, Resource.UnitFunctionProducts,
|
|||
|
|
Resource.TransportainGToDCarbonEmission, Resource.ProductDeliveryCarbonFootprint };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, productCO2DataInDeliveryHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, productCO2DataInDeliveryHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
var result3 = InsertProductCO2DataInDelivery(sheet, productLca, currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 0, productCO2DataInDeliveryHeader.Length - 1);
|
|||
|
|
currentRow += 3;
|
|||
|
|
|
|||
|
|
string[] productCO2DataInUsageHeader = new string[] { Resource.ProductUsageStage, Resource.Items, Resource.PowerCoefficient, Resource.DuringInterrogation,
|
|||
|
|
Resource.ProductCarbonFootprint, Resource.PowerCoefficientSourceDescription };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, productCO2DataInUsageHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, productCO2DataInUsageHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
var result4 = InsertProductCO2DataInUsage(sheet, productLca, currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 0, productCO2DataInUsageHeader.Length - 1);
|
|||
|
|
currentRow += 3;
|
|||
|
|
|
|||
|
|
string[] greenhouseGasEmissionHeader = new string[] { Resource.Phase, Resource.ProductionAndTransportRawMaterial,
|
|||
|
|
Resource.ProductInManufacturingStage, Resource.ProductDeliveryStage,
|
|||
|
|
Resource.ProductUsageStage, Resource.AbandonedStage, Resource.ProductCarbonFootprint };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, greenhouseGasEmissionHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, greenhouseGasEmissionHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
InsertGreenhouseGasEmission(productLca,
|
|||
|
|
result1,
|
|||
|
|
result2,
|
|||
|
|
result3,
|
|||
|
|
result4,
|
|||
|
|
sheet, currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 0, greenhouseGasEmissionHeader.Length - 1);
|
|||
|
|
currentRow += 3;
|
|||
|
|
|
|||
|
|
string[] emissionPercentagePerPhaseHeader = new string[] { Resource.Phase, Resource.ProductionAndTransportRawMaterial,
|
|||
|
|
Resource.ProductInManufacturingStage, Resource.ProductDeliveryStage,
|
|||
|
|
Resource.ProductUsageStage, Resource.AbandonedStage, Resource.ProductCarbonFootprint };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 0, emissionPercentagePerPhaseHeader);
|
|||
|
|
SetCellStyle(sheet, grayHeaderStyle, currentRow, currentRow, 0, emissionPercentagePerPhaseHeader.Length - 1);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
InsertEmissionPercentagePerPhase(productLca,
|
|||
|
|
result1,
|
|||
|
|
result2,
|
|||
|
|
result3,
|
|||
|
|
result4,
|
|||
|
|
sheet, currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 0, emissionPercentagePerPhaseHeader.Length - 1);
|
|||
|
|
currentRow += 3;
|
|||
|
|
MemoryStream aa;
|
|||
|
|
InsertPercentageOfCarbonEmissionChart(workbook, sheet, productLca,
|
|||
|
|
result1,
|
|||
|
|
result2,
|
|||
|
|
result3,
|
|||
|
|
result4,
|
|||
|
|
currentRow,
|
|||
|
|
out aa);
|
|||
|
|
imageStream = new MemoryStream();
|
|||
|
|
aa.Seek(0, SeekOrigin.Begin);
|
|||
|
|
aa.CopyTo(imageStream);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertCO2InventoryCaption(ISheet sheet, ProductLCA productLca)
|
|||
|
|
{
|
|||
|
|
var productName = productLca.TargetProduct != null ? productLca.TargetProduct.Name : "";
|
|||
|
|
|
|||
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
|
|||
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 4));
|
|||
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8));
|
|||
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 9, 12));
|
|||
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 13, 16));
|
|||
|
|
|
|||
|
|
sheet.GetRow(0).GetCell(0).SetCellValue(Resource.MachineTypesOrSeriesOfProducts + " : " + productName);
|
|||
|
|
sheet.GetRow(0).GetCell(2).SetCellValue(Resource.DatabaseName + " : Simapro");
|
|||
|
|
sheet.GetRow(0).GetCell(9).SetCellValue("Primary Data");
|
|||
|
|
sheet.GetRow(0).GetCell(13).SetCellValue("Secondary Data");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private decimal InsertCO2Inventory(ISheet sheet, List<Material> materials, int startRow)
|
|||
|
|
{
|
|||
|
|
decimal sumOfWeight = materials.Sum(x => x.Scalar);
|
|||
|
|
decimal sumOfScopeOnePrimaryFabKgCO2e = 0;
|
|||
|
|
decimal sumOfScopeTwoPrimaryFabKgCO2e = 0;
|
|||
|
|
decimal sumOfScopeThreePrimaryFabKgCO2e = 0;
|
|||
|
|
decimal sumOfPrimaryFabKgCO2e = 0;
|
|||
|
|
decimal sumOfSecondaryPartsProductionKgCO2e = 0;
|
|||
|
|
decimal sumOfLcaResultKgCO2e = 0;
|
|||
|
|
decimal sumOfTransportKgCO2e = 0;
|
|||
|
|
decimal sumOfSecondaryPartsKgCO2e = 0;
|
|||
|
|
int sumOfBomQuantity = 0;
|
|||
|
|
decimal sumOfTotalKgCO2e = materials.Sum(x => x.KgCO2e);
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
sumOfLcaResultKgCO2e = sumOfPrimaryFabKgCO2e + sumOfSecondaryPartsProductionKgCO2e;
|
|||
|
|
sumOfSecondaryPartsKgCO2e = sumOfSecondaryPartsProductionKgCO2e + sumOfTransportKgCO2e;
|
|||
|
|
|
|||
|
|
int row = startRow;
|
|||
|
|
int serialNumber = 1;
|
|||
|
|
foreach (var material in materials)
|
|||
|
|
{
|
|||
|
|
//land mine ! this is related to CarbonEmissionCalculator.GetSecondDataTypeOfPartsKgCO2e
|
|||
|
|
var isPrimary = material.RequestSent != null &&
|
|||
|
|
material.RequestSent.AcceptReplyDate != null &&
|
|||
|
|
material.RequestSent.RepliedByWhichLCA != null &&
|
|||
|
|
material.RequestSent.RepliedByWhichLCA.SnapShotSurveyResult != null;
|
|||
|
|
var repliedLca = isPrimary ? material.RequestSent.RepliedByWhichLCA : null;
|
|||
|
|
var snapShotSurveyResult = isPrimary ? material.RequestSent.RepliedByWhichLCA.SnapShotSurveyResult : null;
|
|||
|
|
var constFactor = 1m;//secondary initial
|
|||
|
|
constFactor = isPrimary && (material.RequestSent.DivideBy == ProductLCAReplyRequest.DivideByOptions.byWorkHour) ? //by workhour
|
|||
|
|
snapShotSurveyResult.constantFactorInWorkHour.Value :
|
|||
|
|
constFactor;
|
|||
|
|
|
|||
|
|
constFactor = isPrimary && (material.RequestSent.DivideBy == ProductLCAReplyRequest.DivideByOptions.byArea) ?//by area
|
|||
|
|
snapShotSurveyResult.constantFactorInArea.Value :
|
|||
|
|
constFactor;
|
|||
|
|
|
|||
|
|
constFactor = isPrimary && (material.RequestSent.DivideBy == ProductLCAReplyRequest.DivideByOptions.byWeight) ?//by weight
|
|||
|
|
snapShotSurveyResult.constantFactorInWeight.Value :
|
|||
|
|
constFactor;
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(1).SetCellValue(serialNumber);
|
|||
|
|
|
|||
|
|
string dataType = isPrimary ? "Primary" : "Secondary";
|
|||
|
|
sheet.GetRow(row).GetCell(2).SetCellValue(dataType);
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(3).SetCellValue(material.PartNumber);
|
|||
|
|
|
|||
|
|
string supplierName = material.SupplierCompanyName;
|
|||
|
|
sheet.GetRow(row).GetCell(4).SetCellValue(supplierName);
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(5).SetCellValue(material.Description);
|
|||
|
|
sheet.GetRow(row).GetCell(6).SetCellValue((double)material.Scalar);
|
|||
|
|
|
|||
|
|
string unitFunctionalProduct = isPrimary ? material.RequestSent.DivideBy.DisplayString() : "PCS";
|
|||
|
|
sheet.GetRow(row).GetCell(7).SetCellValue(unitFunctionalProduct);
|
|||
|
|
|
|||
|
|
string duringInventory = isPrimary ? repliedLca.StartDate.ToShortDateString() + " ~ " + repliedLca.EndDate.ToShortDateString() : "";
|
|||
|
|
sheet.GetRow(row).GetCell(8).SetCellValue(duringInventory);
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(9).SetCellValue(isPrimary ? (snapShotSurveyResult.Scope1FabResult * constFactor).ToString() : "");
|
|||
|
|
sheet.GetRow(row).GetCell(10).SetCellValue(isPrimary ? (snapShotSurveyResult.Scope2FabResult * constFactor).ToString() : "");
|
|||
|
|
sheet.GetRow(row).GetCell(11).SetCellValue(isPrimary ? (snapShotSurveyResult.Scope3FabResult * constFactor).ToString() : "");
|
|||
|
|
|
|||
|
|
decimal tempm = 0;
|
|||
|
|
if (isPrimary)
|
|||
|
|
{
|
|||
|
|
tempm = (snapShotSurveyResult.Scope1FabResult
|
|||
|
|
+ snapShotSurveyResult.Scope2FabResult
|
|||
|
|
+ snapShotSurveyResult.Scope3FabResult) * constFactor;
|
|||
|
|
}
|
|||
|
|
//M
|
|||
|
|
sheet.GetRow(row).GetCell(12).SetCellValue(isPrimary ? tempm.ToString() : "");
|
|||
|
|
|
|||
|
|
decimal tempn = 0;
|
|||
|
|
if (isPrimary)
|
|||
|
|
{
|
|||
|
|
tempn = (snapShotSurveyResult.SecondaryWasteResult + snapShotSurveyResult.SecondaryWasteTransportResult)
|
|||
|
|
* constFactor
|
|||
|
|
+ snapShotSurveyResult.SecondaryDirectMaterialResult
|
|||
|
|
+ snapShotSurveyResult.SecondaryIndirectMaterialResult
|
|||
|
|
+ snapShotSurveyResult.SecondaryWrapMaterialResult;
|
|||
|
|
}
|
|||
|
|
//N
|
|||
|
|
sheet.GetRow(row).GetCell(13).SetCellValue(isPrimary ? tempn.ToString() : "");
|
|||
|
|
//O
|
|||
|
|
sheet.GetRow(row).GetCell(14).SetCellValue(isPrimary ? (tempm + tempn).ToString() : "");
|
|||
|
|
//P
|
|||
|
|
var tempp = isPrimary?snapShotSurveyResult.SecondaryTransportResult:0m;
|
|||
|
|
sheet.GetRow(row).GetCell(15).SetCellValue(isPrimary ? tempp.ToString() : "");
|
|||
|
|
|
|||
|
|
var tempq = isPrimary?(tempn+tempp):material.KgCO2e;
|
|||
|
|
//Q
|
|||
|
|
sheet.GetRow(row).GetCell(16).SetCellValue(tempq.ToString());
|
|||
|
|
//R
|
|||
|
|
sheet.GetRow(row).GetCell(17).SetCellValue(material.Quantity.ToString());
|
|||
|
|
var temps = material.KgCO2e * material.Quantity;
|
|||
|
|
//S
|
|||
|
|
sheet.GetRow(row).GetCell(18).SetCellValue(temps.ToString());
|
|||
|
|
//T
|
|||
|
|
decimal partsWeightProportion = sumOfWeight != 0 ? (material.Scalar / sumOfWeight) * 100 : 0;
|
|||
|
|
sheet.GetRow(row).GetCell(19).SetCellValue(Math.Round(partsWeightProportion, 8).ToString());
|
|||
|
|
//U
|
|||
|
|
decimal partsTotalKgCO2eProportion = sumOfTotalKgCO2e != 0 ? (temps / sumOfTotalKgCO2e) * 100 : 0;
|
|||
|
|
sheet.GetRow(row).GetCell(20).SetCellValue(Math.Round(partsTotalKgCO2eProportion, 8).ToString());
|
|||
|
|
|
|||
|
|
row++;
|
|||
|
|
serialNumber++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(0).SetCellValue("Total");
|
|||
|
|
sheet.GetRow(row).GetCell(6).SetCellValue(Math.Round((double)sumOfWeight, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(9).SetCellValue(Math.Round((double)sumOfScopeOnePrimaryFabKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(10).SetCellValue(Math.Round((double)sumOfScopeTwoPrimaryFabKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(11).SetCellValue(Math.Round((double)sumOfScopeThreePrimaryFabKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(12).SetCellValue(Math.Round((double)sumOfPrimaryFabKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(13).SetCellValue(Math.Round((double)sumOfSecondaryPartsProductionKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(14).SetCellValue(Math.Round((double)sumOfLcaResultKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(15).SetCellValue(Math.Round((double)sumOfTransportKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(16).SetCellValue(Math.Round((double)sumOfSecondaryPartsKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(17).SetCellValue(sumOfBomQuantity);
|
|||
|
|
sheet.GetRow(row).GetCell(18).SetCellValue(Math.Round((double)sumOfTotalKgCO2e, 8));
|
|||
|
|
sheet.GetRow(row).GetCell(19).SetCellValue("100%");
|
|||
|
|
sheet.GetRow(row).GetCell(20).SetCellValue("100%");
|
|||
|
|
|
|||
|
|
return sumOfTotalKgCO2e;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private decimal InsertProductCO2DataInManufacturing(ISheet sheet, ProductLCA productLca, int startRow)
|
|||
|
|
{
|
|||
|
|
var snapshot = productLca.SnapShotSurveyResult;
|
|||
|
|
var dataType = "Primary";
|
|||
|
|
var fabName = productLca.TargetFab.Name;
|
|||
|
|
var productType = productLca.TargetProduct.SerialNumber;
|
|||
|
|
var productName = productLca.TargetProduct.Name;
|
|||
|
|
var unitFunctionProduct = "PCS";
|
|||
|
|
var duringInventory = productLca.StartDate.ToShortDateString() + "~" + productLca.EndDate.ToShortDateString();
|
|||
|
|
var scopeOneData = snapshot.Scope1FabResult * snapshot.constantFactorInWorkHour;
|
|||
|
|
var scopeTwoData = snapshot.Scope2FabResult * snapshot.constantFactorInWorkHour;
|
|||
|
|
var scopeThreeData = snapshot.Scope3FabResult * snapshot.constantFactorInWorkHour;
|
|||
|
|
var primaryData = scopeOneData + scopeThreeData + scopeTwoData;
|
|||
|
|
var secondaryDataOfManufacturing = (snapshot.SecondaryWasteResult + snapshot.SecondaryWasteTransportResult) * snapshot.constantFactorInWorkHour;
|
|||
|
|
var lcaResultCO2 = secondaryDataOfManufacturing + primaryData;
|
|||
|
|
var remarkOfDescription = "工時";
|
|||
|
|
|
|||
|
|
sheet.GetRow(startRow).GetCell(1).SetCellValue(1);
|
|||
|
|
sheet.GetRow(startRow).GetCell(2).SetCellValue(dataType);
|
|||
|
|
sheet.GetRow(startRow).GetCell(3).SetCellValue(fabName);
|
|||
|
|
sheet.GetRow(startRow).GetCell(4).SetCellValue(productType);
|
|||
|
|
sheet.GetRow(startRow).GetCell(5).SetCellValue(productName);
|
|||
|
|
sheet.GetRow(startRow).GetCell(6).SetCellValue(unitFunctionProduct);
|
|||
|
|
sheet.GetRow(startRow).GetCell(7).SetCellValue(duringInventory);
|
|||
|
|
sheet.GetRow(startRow).GetCell(8).SetCellValue(scopeOneData.HasValue?scopeOneData.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(9).SetCellValue(scopeTwoData.HasValue ? scopeTwoData.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(10).SetCellValue(scopeThreeData.HasValue ? scopeThreeData.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(11).SetCellValue(primaryData.HasValue ? primaryData.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(12).SetCellValue(secondaryDataOfManufacturing.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(13).SetCellValue(lcaResultCO2.HasValue ? lcaResultCO2.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(14).SetCellValue(secondaryDataOfManufacturing.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(15).SetCellValue(lcaResultCO2.HasValue ? lcaResultCO2.Value.ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(16).SetCellValue(remarkOfDescription);
|
|||
|
|
return lcaResultCO2.HasValue?lcaResultCO2.Value:0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private decimal InsertProductCO2DataInDelivery(ISheet sheet, ProductLCA productLca, int startRow)
|
|||
|
|
{
|
|||
|
|
var snapshot = productLca.SnapShotSurveyResult;
|
|||
|
|
var dataType = "Secondary";
|
|||
|
|
var fabName = productLca.TargetFab.Name;
|
|||
|
|
var productType = productLca.TargetProduct.SerialNumber;
|
|||
|
|
var productName = productLca.TargetProduct.Name;
|
|||
|
|
var unitFunctionProduct = "PCS";
|
|||
|
|
var transportationDToGCarbonEmissions = "[ph]";
|
|||
|
|
var productDeliveryCarbonFootprint = snapshot.SecondaryTransportResult;
|
|||
|
|
|
|||
|
|
sheet.GetRow(startRow).GetCell(1).SetCellValue(1);
|
|||
|
|
sheet.GetRow(startRow).GetCell(2).SetCellValue(dataType);
|
|||
|
|
sheet.GetRow(startRow).GetCell(3).SetCellValue(fabName);
|
|||
|
|
sheet.GetRow(startRow).GetCell(4).SetCellValue(productType);
|
|||
|
|
sheet.GetRow(startRow).GetCell(5).SetCellValue(productName);
|
|||
|
|
sheet.GetRow(startRow).GetCell(6).SetCellValue(unitFunctionProduct);
|
|||
|
|
sheet.GetRow(startRow).GetCell(7).SetCellValue(transportationDToGCarbonEmissions);
|
|||
|
|
sheet.GetRow(startRow).GetCell(8).SetCellValue(productDeliveryCarbonFootprint.ToString());
|
|||
|
|
return productDeliveryCarbonFootprint;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private decimal InsertProductCO2DataInUsage(ISheet sheet, ProductLCA productLca, int startRow)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
var inventoryStageData = productLca.TargetInventoryStageData;
|
|||
|
|
var result = inventoryStageData.GetUsageStageCarbonFootprint();
|
|||
|
|
sheet.GetRow(startRow).GetCell(0).SetCellValue(Resource.NonContinuousUsagePatterns);
|
|||
|
|
sheet.GetRow(startRow).GetCell(1).SetCellValue(1);
|
|||
|
|
sheet.GetRow(startRow).GetCell(2).SetCellValue(inventoryStageData != null ? Math.Round(inventoryStageData.ProductUsageStagePowerFactor, 2).ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(3).SetCellValue(inventoryStageData != null ? Math.Round(inventoryStageData.GetUsageStageDuringInventory(), 2).ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(4).SetCellValue(inventoryStageData != null ? Math.Round(result, 2).ToString() : "");
|
|||
|
|
sheet.GetRow(startRow).GetCell(5).SetCellValue(inventoryStageData != null ? inventoryStageData.ProductUsageStageDescription : "");
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertGreenhouseGasEmission(ProductLCA productLca,
|
|||
|
|
decimal materialProductionAndTransport,
|
|||
|
|
decimal productInManufacturingStage,
|
|||
|
|
decimal productInDeliveryStage,
|
|||
|
|
decimal productInUsageStage,
|
|||
|
|
ISheet sheet, int startRow)
|
|||
|
|
{
|
|||
|
|
var materials = productLca.Materials.ToList();
|
|||
|
|
var inventoryStageData = productLca.TargetInventoryStageData;
|
|||
|
|
var productInAbandonedStage = inventoryStageData != null ? Math.Round(inventoryStageData.ProductAbandonedStageCarbonFootprint, 4) : 0;
|
|||
|
|
var productCarbonFootprint = materialProductionAndTransport +
|
|||
|
|
productInManufacturingStage +
|
|||
|
|
productInDeliveryStage +
|
|||
|
|
productInUsageStage +
|
|||
|
|
productInAbandonedStage;
|
|||
|
|
|
|||
|
|
sheet.GetRow(startRow).GetCell(0).SetCellValue(Resource.GreenhouseGasEmissions);
|
|||
|
|
sheet.GetRow(startRow).GetCell(1).SetCellValue(materialProductionAndTransport.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(2).SetCellValue(productInManufacturingStage.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(3).SetCellValue(productInDeliveryStage.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(4).SetCellValue(productInUsageStage.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(5).SetCellValue(productInAbandonedStage.ToString());
|
|||
|
|
sheet.GetRow(startRow).GetCell(6).SetCellValue(productCarbonFootprint.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertEmissionPercentagePerPhase(ProductLCA productLca,
|
|||
|
|
decimal materialProductionAndTransport,
|
|||
|
|
decimal productInManufacturingStage,
|
|||
|
|
decimal productInDeliveryStage,
|
|||
|
|
decimal productInUsageStage,
|
|||
|
|
ISheet sheet, int startRow)
|
|||
|
|
{
|
|||
|
|
var materials = productLca.Materials.ToList();
|
|||
|
|
var inventoryStageData = productLca.TargetInventoryStageData;
|
|||
|
|
var productInAbandonedStage = inventoryStageData != null ? Math.Round(inventoryStageData.ProductAbandonedStageCarbonFootprint, 4) : 0;
|
|||
|
|
var productCarbonFootprint = materialProductionAndTransport +
|
|||
|
|
productInManufacturingStage +
|
|||
|
|
productInDeliveryStage +
|
|||
|
|
productInUsageStage +
|
|||
|
|
productInAbandonedStage;
|
|||
|
|
|
|||
|
|
var materialProductionAndTransportPercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((materialProductionAndTransport / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInManufacturingStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInManufacturingStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInDeliveryStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInDeliveryStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInUsageStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInUsageStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInAbandonedStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInAbandonedStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
|
|||
|
|
sheet.GetRow(startRow).GetCell(0).SetCellValue(Resource.Percentage);
|
|||
|
|
sheet.GetRow(startRow).GetCell(1).SetCellValue(materialProductionAndTransportPercentage.ToString() + "%");
|
|||
|
|
sheet.GetRow(startRow).GetCell(2).SetCellValue(productInManufacturingStagePercentage.ToString() + "%");
|
|||
|
|
sheet.GetRow(startRow).GetCell(3).SetCellValue(productInDeliveryStagePercentage.ToString() + "%");
|
|||
|
|
sheet.GetRow(startRow).GetCell(4).SetCellValue(productInUsageStagePercentage.ToString() + "%");
|
|||
|
|
sheet.GetRow(startRow).GetCell(5).SetCellValue(productInAbandonedStagePercentage.ToString() + "%");
|
|||
|
|
sheet.GetRow(startRow).GetCell(6).SetCellValue("100%");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public void InsertLCAData(IWorkbook workbook, ISheet sheet, ProductLCA productLca)
|
|||
|
|
{
|
|||
|
|
var borderStyle = CreateCommonStyleWithBorder(workbook);
|
|||
|
|
var currentRow = 1;
|
|||
|
|
|
|||
|
|
string[] lcaHeader = new string[] { Resource.SupplierName, Resource.DistributionPrinciple, Resource.IndividualPartsByWeight, Resource.PartNumber,
|
|||
|
|
Resource.ScopeOnePrimaryData, Resource.ScopeTwoPrimaryData, Resource.ScopeThreePrimaryData, Resource.SumOfPrimaryData,
|
|||
|
|
Resource.MaterialDirectMaterial + "(kg co2)", Resource.MaterialIndirectMaterial + "(kg co2)", Resource.MaterialWrapMaterial + "(kg co2)",
|
|||
|
|
"LCA", Resource.TransportationCarbonEmission, Resource.WasteAndWasteTransportOfPartsManufacturing };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 1, lcaHeader);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 1, lcaHeader.Length);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
InsertLCAContent(sheet, productLca.Materials.ToList(), currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow + productLca.Materials.Count() - 1, 1, lcaHeader.Length);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertLCAContent(ISheet sheet, List<Material> materials, int startRow)
|
|||
|
|
{
|
|||
|
|
var row = startRow;
|
|||
|
|
|
|||
|
|
foreach (var material in materials)
|
|||
|
|
{
|
|||
|
|
var repliedLca = material.RequestSent != null ? material.RequestSent.RepliedByWhichLCA : new ProductLCA();
|
|||
|
|
var supplierName = material.RequestSent != null ? material.RequestSent.SenderCompanyName : "";
|
|||
|
|
var distributePriciple = repliedLca != null && repliedLca.ProductProductionPcs.HasValue ? (decimal)repliedLca.ProductProductionPcs.Value : 0;
|
|||
|
|
var distributeKgCO2e = repliedLca != null && repliedLca.DistributeKgCO2eInWeight.HasValue ? (decimal)repliedLca.DistributeKgCO2eInWeight.Value : 0;
|
|||
|
|
var transportationKgCO2e = repliedLca != null ? repliedLca.TransportSheet.ToList().GetTotalKgCO2e() : 0;
|
|||
|
|
var wasteAndWasteTransportationKgCO2e = repliedLca != null ? repliedLca.WasteSheet.ToList().GetTotalKgCO2e() + repliedLca.WasteTransportSheet.ToList().GetTotalKgCO2e() : 0;
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(1).SetCellValue(supplierName);
|
|||
|
|
sheet.GetRow(row).GetCell(2).SetCellValue(Math.Round(distributePriciple, 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(3).SetCellValue(Math.Round(distributeKgCO2e, 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(4).SetCellValue(material.PartNumber);
|
|||
|
|
sheet.GetRow(row).GetCell(5).SetCellValue(Math.Round(repliedLca.GetScopeOnePrimaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(6).SetCellValue(Math.Round(repliedLca.GetScopeTwoPrimaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(7).SetCellValue(Math.Round(repliedLca.GetScopeThreePrimaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(8).SetCellValue(Math.Round(repliedLca.GetPrimaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(9).SetCellValue(Math.Round(repliedLca.Materials.Where(x => x.MaterialType == MaterialType.DirectMaterial).ToList().GetTotalKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(10).SetCellValue(Math.Round(repliedLca.Materials.Where(x => x.MaterialType == MaterialType.IndirectMaterial).ToList().GetTotalKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(11).SetCellValue(Math.Round(repliedLca.Materials.Where(x => x.MaterialType == MaterialType.WrapMaterial).ToList().GetTotalKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(12).SetCellValue(Math.Round(repliedLca.Materials.GetTotalKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(13).SetCellValue(Math.Round(transportationKgCO2e, 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(14).SetCellValue(Math.Round(wasteAndWasteTransportationKgCO2e, 2).ToString());
|
|||
|
|
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public void InsertOnePointFiveData(IWorkbook workbook, ISheet sheet, ProductLCA productLca)
|
|||
|
|
{
|
|||
|
|
var borderStyle = CreateCommonStyleWithBorder(workbook);
|
|||
|
|
var currentRow = 1;
|
|||
|
|
|
|||
|
|
string[] onePointFiveDataHeader = new string[] { Resource.ProjectName, Resource.Manufacturer, Resource.CommodityName, Resource.PartNumber,
|
|||
|
|
Resource.DuringInterrogation, Resource.LevelOneData, Resource.LevelTwoData, Resource.CarbonFootPrintResult,
|
|||
|
|
Resource.DistributionPrinciple, Resource.StaticLabelCertificationCompany };
|
|||
|
|
InsertHorizontalHeader(sheet, currentRow, currentRow, 1, onePointFiveDataHeader);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow, 1, onePointFiveDataHeader.Length);
|
|||
|
|
currentRow++;
|
|||
|
|
|
|||
|
|
InsertOnePointFiveContent(sheet, productLca.Materials.ToList(), currentRow);
|
|||
|
|
SetCellStyle(sheet, borderStyle, currentRow, currentRow + productLca.Materials.Count() - 1, 1, onePointFiveDataHeader.Length);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertOnePointFiveContent(ISheet sheet, List<Material> materials, int startRow)
|
|||
|
|
{
|
|||
|
|
var row = startRow;
|
|||
|
|
|
|||
|
|
foreach (var material in materials)
|
|||
|
|
{
|
|||
|
|
var projectName = 1;
|
|||
|
|
var repliedLca = material.RequestSent != null ? material.RequestSent.RepliedByWhichLCA : null;
|
|||
|
|
var supplierName = material.RequestSent != null ? material.RequestSent.SenderCompanyName : "";
|
|||
|
|
var duringInventory = repliedLca != null ? repliedLca.StartDate + "~" + repliedLca.EndDate : "";
|
|||
|
|
var productionPcs = repliedLca != null && repliedLca.ProductProductionPcs.HasValue ? (decimal)repliedLca.ProductProductionPcs.Value : 0;
|
|||
|
|
var verifyBy = repliedLca != null && repliedLca.VerifyBy != null ? repliedLca.VerifyBy.Name : "";
|
|||
|
|
|
|||
|
|
sheet.GetRow(row).GetCell(1).SetCellValue(projectName);
|
|||
|
|
sheet.GetRow(row).GetCell(2).SetCellValue(supplierName);
|
|||
|
|
sheet.GetRow(row).GetCell(3).SetCellValue(material.Name);
|
|||
|
|
sheet.GetRow(row).GetCell(4).SetCellValue(material.PartNumber);
|
|||
|
|
sheet.GetRow(row).GetCell(5).SetCellValue(duringInventory);
|
|||
|
|
sheet.GetRow(row).GetCell(6).SetCellValue(Math.Round(repliedLca.GetPrimaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(7).SetCellValue(Math.Round(repliedLca.GetSecondaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(8).SetCellValue(Math.Round(repliedLca.GetPrimaryFabKgCO2e() + repliedLca.GetSecondaryFabKgCO2e(), 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(9).SetCellValue(Math.Round(productionPcs, 2).ToString());
|
|||
|
|
sheet.GetRow(row).GetCell(10).SetCellValue(verifyBy);
|
|||
|
|
|
|||
|
|
projectName++;
|
|||
|
|
row++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private void InsertPercentageOfCarbonEmissionChart(IWorkbook workbook, ISheet sheet, ProductLCA productLca,
|
|||
|
|
decimal materialProductionAndTransport,
|
|||
|
|
decimal productInManufacturingStage,
|
|||
|
|
decimal productInDeliveryStage,
|
|||
|
|
decimal productInUsageStage,
|
|||
|
|
int currentRow,
|
|||
|
|
out MemoryStream imageMemoryStream)
|
|||
|
|
{
|
|||
|
|
var materials = productLca.Materials.ToList();
|
|||
|
|
var inventoryStageData = productLca.TargetInventoryStageData;
|
|||
|
|
var productInAbandonedStage = inventoryStageData != null ? Math.Round(inventoryStageData.ProductAbandonedStageCarbonFootprint, 4) : 0;
|
|||
|
|
var productCarbonFootprint = materialProductionAndTransport +
|
|||
|
|
productInManufacturingStage +
|
|||
|
|
productInDeliveryStage +
|
|||
|
|
productInUsageStage +
|
|||
|
|
productInAbandonedStage;
|
|||
|
|
|
|||
|
|
var materialProductionAndTransportPercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((materialProductionAndTransport / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInManufacturingStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInManufacturingStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInDeliveryStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInDeliveryStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInUsageStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInUsageStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
var productInAbandonedStagePercentage = productCarbonFootprint == 0 ?
|
|||
|
|
0 :
|
|||
|
|
Math.Round((productInAbandonedStage / productCarbonFootprint) * 100, 2);
|
|||
|
|
|
|||
|
|
var chart = new Chart();
|
|||
|
|
chart.Height = 720;
|
|||
|
|
chart.Width = 1024;
|
|||
|
|
|
|||
|
|
var chartArea = chart.ChartAreas.Add("PercentageOfCarbonEmission");
|
|||
|
|
|
|||
|
|
var series = chart.Series.Add("PercentageOfCarbonEmission");
|
|||
|
|
series.ChartType = SeriesChartType.Pie;
|
|||
|
|
series.IsValueShownAsLabel = true;
|
|||
|
|
series.Points.Add((double)materialProductionAndTransportPercentage).Label = Resource.ProductionAndTransportRawMaterial + " " + materialProductionAndTransportPercentage + "%";
|
|||
|
|
series.Points.Add((double)productInManufacturingStagePercentage).Label = Resource.ProductInManufacturingStage + " " + productInManufacturingStagePercentage + "%";
|
|||
|
|
series.Points.Add((double)productInDeliveryStagePercentage).Label = Resource.ProductDeliveryStage + " " + productInDeliveryStagePercentage + "%";
|
|||
|
|
series.Points.Add((double)productInUsageStagePercentage).Label = Resource.ProductUsageStage + " " + productInUsageStagePercentage + "%";
|
|||
|
|
series.Points.Add((double)productInAbandonedStagePercentage).Label = Resource.AbandonedStage + " " + productInAbandonedStagePercentage + "%";
|
|||
|
|
|
|||
|
|
imageMemoryStream = new MemoryStream();
|
|||
|
|
chart.SaveImage(imageMemoryStream, ChartImageFormat.Png);
|
|||
|
|
var patriarch = sheet.CreateDrawingPatriarch();
|
|||
|
|
var anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, currentRow + 1, 5, currentRow + 28);
|
|||
|
|
var pictureIndex = workbook.AddPicture(imageMemoryStream.ToArray(), PictureType.PNG);
|
|||
|
|
var signaturePicture = patriarch.CreatePicture(anchor, pictureIndex);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|