using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.DataVisualization.Charting;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Net;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using Weee.Models;
using Weee.Models.ExtensionMethods;
using Resources;

namespace Weee.Supports
{
    public class ProductLCAExcelExporter : ExcelExporter
    {
        public void InsertLcaData(IWorkbook workbook, ISheet sheet, ProductLCA productLca)
        {
            var grayColor = new XSSFColor(Color.LightGray);
            var headerStyle = CreateCommonStyleWithBorder(workbook, grayColor);
            var borderStyle = CreateCommonStyleWithBorder(workbook);
            var noneBorderStyle = CreateCommonStyleWithoutBorder(workbook);
            if (productLca.Owner != null)
                InsertLogo(workbook, sheet, productLca.Owner.LogoUrl);
            if (productLca.SnapShotSurveyResult == null)
                throw new Exception("SnapShotSurveyResult can not be null");

            InsertTitle(sheet);
            SetCellStyle(sheet, noneBorderStyle, 0, 0, 0, 1);

            InsertFabSubTitle(sheet);
            SetCellStyle(sheet, noneBorderStyle, 4, 4, 0, 0);

            InsertPrimaryFabDataScopeTitle(sheet);
            SetCellStyle(sheet, headerStyle, 5, 5, 0, 8);

            InsertPrimaryFabDataScopeOne(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 6, 6, 0, 8);
            SetCellStyle(sheet, borderStyle, 7, 7, 0, 8);

            InsertPrimaryFabDataScopeTwo(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 9, 9, 0, 3);
            SetCellStyle(sheet, borderStyle, 10, 10, 0, 3);

            InsertPrimaryFabDataScopeThree(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 12, 12, 0, 4);
            SetCellStyle(sheet, borderStyle, 13, 13, 0, 4);

            InsertPrimaryFabData(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 15, 17, 0, 1);
            SetCellStyle(sheet, headerStyle, 15, 16, 2, 2);
            SetCellStyle(sheet, borderStyle, 17, 17, 2, 2);

            InsertSecondaryFabData(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 15, 16, 4, 5);
            SetCellStyle(sheet, borderStyle, 17, 17, 4, 5);

            InsertTotalFabData(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 15, 16, 7, 8);
            SetCellStyle(sheet, borderStyle, 17, 17, 7, 8);

            InsertProductSubTitle(sheet);
            SetCellStyle(sheet, noneBorderStyle, 19, 19, 0, 0);

            InsertProductInfo(sheet, productLca);
            SetCellStyle(sheet, noneBorderStyle, 20, 21, 0, 1);

            InsertProductData(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 22, 23, 0, 2);
            SetCellStyle(sheet, borderStyle, 24, 26, 0, 2);

            InsertPrimaryAndSecondaryFabData(sheet, productLca);
            SetCellStyle(sheet, headerStyle, 22, 23, 4, 8);
            SetCellStyle(sheet, borderStyle, 24, 26, 4, 8);

            InsertSecondaryProductData(sheet, productLca.SnapShotSurveyResult);
            SetCellStyle(sheet, headerStyle, 28, 29, 4, 7);
            SetCellStyle(sheet, borderStyle, 30, 30, 4, 7);

            var totalKgCO2e = productLca.SnapShotSurveyResult.Scope1FabResult
                            + productLca.SnapShotSurveyResult.Scope2FabResult
                            + productLca.SnapShotSurveyResult.Scope3FabResult
                            + productLca.SnapShotSurveyResult.SecondaryWasteResult
                            + productLca.SnapShotSurveyResult.SecondaryWasteTransportResult;

            InsertChartWithFabCarbonEmissionDistributeByWeight(workbook, sheet, productLca, totalKgCO2e);
            InsertChartWithFabCarbonEmissionDistributeByArea(workbook, sheet, productLca, totalKgCO2e);
            InsertChartWithFabCarbonEmissionDistributeByHour(workbook, sheet, productLca, totalKgCO2e);
        }

        private void InsertLogo(IWorkbook workbook, ISheet sheet, string PhotoUrl)
        {
            if (PhotoUrl != null)
            {
                //Dl-53 begin
                string actPath = AppDomain.CurrentDomain.BaseDirectory;
                string uriPath = System.Web.HttpContext.Current.Request.Url.Authority;
                PhotoUrl = actPath + (PhotoUrl.Substring(1)).Replace("/", "\\");
                var uri = new Uri(PhotoUrl);
                var imageRequest = WebRequest.Create(uri);
                //var imageRequest = (HttpWebRequest)WebRequest.Create(PhotoUrl);
                //end
                var imageResponse = imageRequest.GetResponse();
                var image = Image.FromStream(imageResponse.GetResponseStream());
                var imageMemoryStream = new MemoryStream();
                image.Save(imageMemoryStream, ImageFormat.Png);

                var patriarch = sheet.CreateDrawingPatriarch();
                var anchor = new XSSFClientAnchor(0, 0, 2, 3, 0, 0, 1, 3);
                var pictureIndex = workbook.AddPicture(imageMemoryStream.GetBuffer(), PictureType.PNG);
                patriarch.CreatePicture(anchor, pictureIndex);
                imageMemoryStream.Close();
            }
        }

        private void InsertTitle(ISheet sheet)
        {
            sheet.AddMergedRegion(new CellRangeAddress(0, 2, 1, 8));
            sheet.GetRow(0).GetCell(1).SetCellValue(Resource.ProductLcaInterrogationTitle);
        }

        private void InsertFabSubTitle(ISheet sheet)
        {
            sheet.GetRow(4).GetCell(0).SetCellValue(Resource.FabCarbonEmissionsA);
        }

        private void InsertPrimaryFabDataScopeTitle(ISheet sheet)
        {
            sheet.AddMergedRegion(new CellRangeAddress(5, 5, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(5, 5, 2, 8));

            sheet.GetRow(5).GetCell(0).SetCellValue("Scope");
            sheet.GetRow(5).GetCell(2).SetCellValue(Resource.TheInterrogationOfCarbonEmissions);
        }

        private void InsertPrimaryFabDataScopeOne(ISheet sheet, ProductLCA productLca)
        {
            sheet.AddMergedRegion(new CellRangeAddress(6, 7, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(6, 7, 1, 1));

            sheet.GetRow(6).GetCell(0).SetCellValue("Ⅰ");
            sheet.GetRow(6).GetCell(1).SetCellValue(Resource.ScopeOneTitle);
            sheet.GetRow(6).GetCell(2).SetCellValue(Resource.SepticOfTotalWorkHour);
            sheet.GetRow(6).GetCell(3).SetCellValue(Resource.CompanyVehicle);
            sheet.GetRow(6).GetCell(4).SetCellValue(Resource.DieselEquipment);
            sheet.GetRow(6).GetCell(5).SetCellValue(Resource.CompanyKitchenAndOtherFuelsA);
            sheet.GetRow(6).GetCell(6).SetCellValue(Resource.RefrigerantEquipment);
            sheet.GetRow(6).GetCell(7).SetCellValue(Resource.FireEquipment);
            sheet.GetRow(6).GetCell(8).SetCellValue(Resource.OtherCompound);

            sheet.GetRow(7).GetCell(2).SetCellValue(Math.Round(productLca.WorkHourSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(3).SetCellValue(Math.Round(productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.owned).ToList().GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(4).SetCellValue(Math.Round(productLca.GasolineEquipmentSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(5).SetCellValue(Math.Round(productLca.KitchenSheet.Where(x => x.Type != Kitchen.KitchenType.outsourcing).ToList().GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(6).SetCellValue(Math.Round(productLca.RefrigerantSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(7).SetCellValue(Math.Round(productLca.FireEquipmentSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(7).GetCell(8).SetCellValue(Math.Round(productLca.OtherCompoundSheet.GetTotalKgCO2e(), 2).ToString());
        }

        private void InsertPrimaryFabDataScopeTwo(ISheet sheet, ProductLCA productLca)
        {
            sheet.AddMergedRegion(new CellRangeAddress(9, 10, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(9, 10, 1, 1));

            sheet.GetRow(9).GetCell(0).SetCellValue("Ⅱ");
            sheet.GetRow(9).GetCell(1).SetCellValue(Resource.ScopeTwoTitle);
            sheet.GetRow(9).GetCell(2).SetCellValue(Resource.PowerUsage);
            sheet.GetRow(9).GetCell(3).SetCellValue(Resource.SteamUsage);

            sheet.GetRow(10).GetCell(2).SetCellValue(Math.Round(productLca.PowerUsageSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(10).GetCell(3).SetCellValue(Math.Round(productLca.SteamUsageSheet.GetTotalKgCO2e(), 2).ToString());
        }

        private void InsertPrimaryFabDataScopeThree(ISheet sheet, ProductLCA productLca)
        {
            sheet.AddMergedRegion(new CellRangeAddress(12, 13, 0, 0));
            sheet.AddMergedRegion(new CellRangeAddress(12, 13, 1, 1));

            sheet.GetRow(12).GetCell(0).SetCellValue("Ⅲ");
            sheet.GetRow(12).GetCell(1).SetCellValue(Resource.ScopeThreeTitle);
            sheet.GetRow(12).GetCell(2).SetCellValue(Resource.WaterUsage);
            sheet.GetRow(12).GetCell(3).SetCellValue(Resource.RentVehicle);
            sheet.GetRow(12).GetCell(4).SetCellValue(Resource.OutSourcingKitchenFuel);

            sheet.GetRow(13).GetCell(2).SetCellValue(productLca.WaterUsageSheet.GetTotalKgCO2e().ToString());
            sheet.GetRow(13).GetCell(3).SetCellValue(productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.rental).ToList().GetTotalKgCO2e().ToString());
            sheet.GetRow(13).GetCell(4).SetCellValue(productLca.KitchenSheet.Where(x => x.Type == Kitchen.KitchenType.outsourcing).ToList().GetTotalKgCO2e().ToString());
        }

        private void InsertPrimaryFabData(ISheet sheet, ProductLCA productLca)
        {
            sheet.AddMergedRegion(new CellRangeAddress(15, 17, 0, 1));

            sheet.GetRow(15).GetCell(0).SetCellValue(Resource.FabCarbonEmissionsB);
            sheet.GetRow(15).GetCell(2).SetCellValue(Resource.PrimaryData);
            sheet.GetRow(16).GetCell(2).SetCellValue("Scope Ⅰ, Ⅱ, Ⅲ");
            sheet.GetRow(17).GetCell(2).SetCellValue(Math.Round(productLca.GetPrimaryFabKgCO2e(), 2).ToString());
        }

        private void InsertSecondaryFabData(ISheet sheet, ProductLCA productLca)
        {
            sheet.GetRow(16).GetCell(3).SetCellValue("+");

            sheet.AddMergedRegion(new CellRangeAddress(15, 15, 4, 5));

            sheet.GetRow(15).GetCell(4).SetCellValue(Resource.SecondaryData);
            sheet.GetRow(16).GetCell(4).SetCellValue(Resource.Waste);
            sheet.GetRow(16).GetCell(5).SetCellValue(Resource.WasteTransportation);

            sheet.GetRow(17).GetCell(4).SetCellValue(Math.Round(productLca.WasteSheet.GetTotalKgCO2e(), 2).ToString());
            sheet.GetRow(17).GetCell(5).SetCellValue(Math.Round(productLca.WasteTransportSheet.GetTotalKgCO2e(), 2).ToString());
        }

        private void InsertTotalFabData(ISheet sheet, ProductLCA productLca)
        {
            sheet.GetRow(16).GetCell(6).SetCellValue("=");

            sheet.AddMergedRegion(new CellRangeAddress(15, 15, 7, 8));
            sheet.AddMergedRegion(new CellRangeAddress(16, 16, 7, 8));
            sheet.AddMergedRegion(new CellRangeAddress(17, 17, 7, 8));

            sheet.GetRow(15).GetCell(7).SetCellValue(Resource.TotalEmissions);
            sheet.GetRow(16).GetCell(7).SetCellValue("KgCO2e");

            sheet.GetRow(17).GetCell(7).SetCellValue(productLca.GetTotalFabKgCO2e().ToString());
        }

        private void InsertProductSubTitle(ISheet sheet)
        {
            sheet.GetRow(19).GetCell(0).SetCellValue(Resource.ProductCarbonEmission);
        }

        private void InsertProductInfo(ISheet sheet, ProductLCA productLca)
        {
            sheet.GetRow(20).GetCell(0).SetCellValue(Resource.ProductName + ":");
            sheet.GetRow(20).GetCell(1).SetCellValue(productLca.LCATypeDisplayName);
            sheet.GetRow(21).GetCell(1).SetCellValue(productLca.TargetProduct.SpecDescription);
        }

        private void InsertProductData(ISheet sheet, ProductLCA productLca)
        {
            sheet.AddMergedRegion(new CellRangeAddress(22, 23, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(22, 23, 2, 2));
            sheet.AddMergedRegion(new CellRangeAddress(24, 24, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(25, 25, 0, 1));
            sheet.AddMergedRegion(new CellRangeAddress(26, 26, 0, 1));

            sheet.GetRow(22).GetCell(0).SetCellValue(Resource.VariousAspectsOfTheProductDistribution);
            sheet.GetRow(22).GetCell(2).SetCellValue(Resource.ProductCarbonEmission + " (KgCO2e)");

            sheet.GetRow(24).GetCell(0).SetCellValue(Resource.Weight);
            sheet.GetRow(24).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInWeight.HasValue ? productLca.DistributeKgCO2eInWeight.ToString() : "");

            sheet.GetRow(25).GetCell(0).SetCellValue(Resource.AreaB);
            sheet.GetRow(25).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInArea.HasValue ? productLca.DistributeKgCO2eInArea.ToString() : "");

            sheet.GetRow(26).GetCell(0).SetCellValue(Resource.WorkHour);
            sheet.GetRow(26).GetCell(2).SetCellValue(productLca.DistributeKgCO2eInWorkHour.HasValue ? productLca.DistributeKgCO2eInWorkHour.ToString() : "");
        }

        private void InsertPrimaryAndSecondaryFabData(ISheet sheet, ProductLCA productLca)
        {
            var snapShot = productLca.SnapShotSurveyResult;
            var weightFactor = snapShot.constantFactorInWeight;
            var areaFactor = snapShot.constantFactorInArea;
            var workhourFactor = snapShot.constantFactorInWorkHour;
            var wasteTotalKgCO2e = productLca.WasteSheet.Sum(x => x.KgCO2e);
            var wastetransportTotalKgCO2e = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
            sheet.AddMergedRegion(new CellRangeAddress(22, 22, 4, 6));
            sheet.AddMergedRegion(new CellRangeAddress(22, 22, 7, 8));

            sheet.GetRow(22).GetCell(4).SetCellValue(Resource.PrimaryData + " / Scope");
            sheet.GetRow(22).GetCell(7).SetCellValue("Secondary Data");

            sheet.GetRow(23).GetCell(4).SetCellValue("Ⅰ");
            sheet.GetRow(23).GetCell(5).SetCellValue("Ⅱ");
            sheet.GetRow(23).GetCell(6).SetCellValue("Ⅲ");
            sheet.GetRow(23).GetCell(7).SetCellValue(Resource.Waste);
            sheet.GetRow(23).GetCell(8).SetCellValue(Resource.WasteTransportation);
            //Math.Round(productLca.GetScopeOneDistributeByWeightFabKgCO2e(), 2).ToString()
            sheet.GetRow(24).GetCell(4).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * weightFactor.Value).ToString());
            sheet.GetRow(24).GetCell(5).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * weightFactor.Value).ToString());
            sheet.GetRow(24).GetCell(6).SetCellValue(weightFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * weightFactor.Value).ToString());
            sheet.GetRow(24).GetCell(7).SetCellValue(weightFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * weightFactor.Value).ToString());
            sheet.GetRow(24).GetCell(8).SetCellValue(weightFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * weightFactor.Value).ToString());

            sheet.GetRow(25).GetCell(4).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * areaFactor.Value).ToString());
            sheet.GetRow(25).GetCell(5).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * areaFactor.Value).ToString());
            sheet.GetRow(25).GetCell(6).SetCellValue(areaFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * areaFactor.Value).ToString());
            sheet.GetRow(25).GetCell(7).SetCellValue(areaFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * areaFactor.Value).ToString());
            sheet.GetRow(25).GetCell(8).SetCellValue(areaFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * areaFactor.Value).ToString());

            sheet.GetRow(26).GetCell(4).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope1FabResult * workhourFactor.Value).ToString());
            sheet.GetRow(26).GetCell(5).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope2FabResult * workhourFactor.Value).ToString());
            sheet.GetRow(26).GetCell(6).SetCellValue(workhourFactor == null ? "#DIV/0" : (snapShot.Scope3FabResult * workhourFactor.Value).ToString());
            sheet.GetRow(26).GetCell(7).SetCellValue(workhourFactor == null ? "#DIV/0" : (wasteTotalKgCO2e * workhourFactor.Value).ToString());
            sheet.GetRow(26).GetCell(8).SetCellValue(workhourFactor == null ? "#DIV/0" : (wastetransportTotalKgCO2e * workhourFactor.Value).ToString());
        }

        private void InsertSecondaryProductData(ISheet sheet, SnapShotSurveyResult snapShot)
        {

            sheet.AddMergedRegion(new CellRangeAddress(28, 28, 4, 7));

            sheet.GetRow(28).GetCell(4).SetCellValue(Resource.SecondaryData);

            sheet.GetRow(29).GetCell(4).SetCellValue(Resource.StaticLabelGlobal_LCIDirectMaterial);
            sheet.GetRow(29).GetCell(5).SetCellValue(Resource.StaticLabelGlobal_LCIIndirectMaterial);
            sheet.GetRow(29).GetCell(6).SetCellValue(Resource.StaticLabelGlobal_LCIWrapMaterial);
            sheet.GetRow(29).GetCell(7).SetCellValue(Resource.Transport);

            sheet.GetRow(30).GetCell(4).SetCellValue(snapShot.SecondaryDirectMaterialResult.ToString());
            sheet.GetRow(30).GetCell(5).SetCellValue(snapShot.SecondaryIndirectMaterialResult.ToString());
            sheet.GetRow(30).GetCell(6).SetCellValue(snapShot.SecondaryWrapMaterialResult.ToString());
            sheet.GetRow(30).GetCell(7).SetCellValue(snapShot.SecondaryTransportResult.ToString());
        }

        private void InsertChartWithFabCarbonEmissionDistributeByWeight(IWorkbook workbook, ISheet sheet, ProductLCA productLca,decimal totalKgCO2e)
        {
            var isNotValid = productLca.SnapShotSurveyResult.constantFactorInWeight == null || totalKgCO2e == 0m;
            var chart = new Chart();
            chart.Titles.Add(Resource.DistributeByWeight);
            chart.Width = 1280;
            chart.Height = 720;

            var chartArea = chart.ChartAreas.Add("distributeByWeight");
            chartArea.AxisY.Title = Resource.Percentage;
            chartArea.AxisX.MajorGrid.LineWidth = 0;
            chartArea.AxisX2.Enabled = AxisEnabled.False;
            chartArea.AxisX.Interval = 1;

            var series = chart.Series.Add("distributeByWeight");
            series.ChartType = SeriesChartType.Column;
            series.ChartArea = "distributeByWeight";
            series.IsValueShownAsLabel = true;

            var workHour = productLca.WorkHourSheet;
            var workHourTotalKGCO2e = workHour.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHourTotalKGCO2e / totalKgCO2e));

            var ownVehicle = productLca.VehicleSheet.Where(x => x.Type == Vehicle.VehicleType.owned).ToList().Sum(x=>x.KgCO2e);
            series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownVehicle / totalKgCO2e));

            var gaslineEquipment = productLca.GasolineEquipmentSheet.Sum(x=>x.KgCO2e);
            series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gaslineEquipment / totalKgCO2e));

            var ownedAndOtherKitchen = productLca.KitchenSheet
                .Where(x => x.Type != Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x=>x.KgCO2e);
            series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));

            var refrigerant = productLca.RefrigerantSheet.Sum(x=>x.KgCO2e);
            series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));

            var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));

            var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));

            var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));

            var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));

            var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));

            var rentalVehicle = productLca.VehicleSheet
                .Where(x => x.Type == Vehicle.VehicleType.rental)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));

            var outSourcingKitchen = productLca.KitchenSheet
                .Where(x => x.Type == Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));

            var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));

            var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));

            CreateImage(workbook, sheet, chart, 0, 37, 9, 62);
        }

        private void InsertChartWithFabCarbonEmissionDistributeByArea(IWorkbook workbook, ISheet sheet, ProductLCA productLca, decimal totalKgCO2e)
        {
            var isNotValid = productLca.SnapShotSurveyResult.constantFactorInArea == null || totalKgCO2e == 0m;
            var chart = new Chart();
            chart.Titles.Add(Resource.DistributeByArea);
            chart.Width = 1280;
            chart.Height = 720;

            var chartArea = chart.ChartAreas.Add("distributeByArea");
            chartArea.AxisY.Title = Resource.Percentage;
            chartArea.AxisX.MajorGrid.LineWidth = 0;
            chartArea.AxisX2.Enabled = AxisEnabled.False;
            chartArea.AxisX.Interval = 1;

            var series = chart.Series.Add("distributeByArea");
            series.ChartType = SeriesChartType.Column; // Chart type
            series.ChartArea = "distributeByArea";
            series.IsValueShownAsLabel = true;

            var workHour = productLca.WorkHourSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHour / totalKgCO2e));

            var ownedVehicle = productLca.VehicleSheet
                .Where(x => x.Type == Vehicle.VehicleType.owned)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownedVehicle / totalKgCO2e));

            var gasolineEquipment = productLca.GasolineEquipmentSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gasolineEquipment / totalKgCO2e));

            var ownedAndOtherKitchen = productLca.KitchenSheet
                .Where(x => x.Type != Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));

            var refrigerant = productLca.RefrigerantSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));

            var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));

            var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));

            var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));

            var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));

            var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));

            var rentalVehicle = productLca.VehicleSheet
                .Where(x => x.Type == Vehicle.VehicleType.rental)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));

            var outSourcingKitchen = productLca.KitchenSheet
                .Where(x => x.Type == Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));

            var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));

            var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));

            CreateImage(workbook, sheet, chart, 0, 64, 9, 89);
        }

        private void InsertChartWithFabCarbonEmissionDistributeByHour(IWorkbook workbook, ISheet sheet, ProductLCA productLca, decimal totalKgCO2e)
        {
            var isNotValid = productLca.SnapShotSurveyResult.constantFactorInWorkHour == null||totalKgCO2e==0m;
            var chart = new Chart();
            chart.Titles.Add(Resource.DistributeByHour);
            chart.Width = 1280;
            chart.Height = 720;

            var chartArea = chart.ChartAreas.Add("distributeByHour");
            chartArea.AxisY.Title = Resource.Percentage;
            chartArea.AxisX.MajorGrid.LineWidth = 0;
            chartArea.AxisX2.Enabled = AxisEnabled.False;
            chartArea.AxisX.Interval = 1;

            var series = chart.Series.Add("distributeByHour");
            series.ChartType = SeriesChartType.Column; // Chart type
            series.ChartArea = "distributeByHour";
            series.IsValueShownAsLabel = true;

            var workHour = productLca.WorkHourSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Septic, isNotValid ? 0 : (workHour / totalKgCO2e));

            var ownedVehicle = productLca.VehicleSheet
                .Where(x => x.Type == Vehicle.VehicleType.owned)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.CompanyVehicle, isNotValid ? 0 : (ownedVehicle / totalKgCO2e));

            var gasolineEquipment = productLca.GasolineEquipmentSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.DieselEquipment, isNotValid ? 0 : (gasolineEquipment / totalKgCO2e));

            var ownedAndOtherKitchen = productLca.KitchenSheet
                .Where(x => x.Type != Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.CompanyKitchenAndOtherFuelsB, isNotValid ? 0 : (ownedAndOtherKitchen / totalKgCO2e));

            var refrigerant = productLca.RefrigerantSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.EquipmentContainingRefrigerant, isNotValid ? 0 : (refrigerant / totalKgCO2e));

            var fireEquipment = productLca.FireEquipmentSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.FireEquipment, isNotValid ? 0 : (fireEquipment / totalKgCO2e));

            var otherCompound = productLca.OtherCompoundSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OtherCompound, isNotValid ? 0 : (otherCompound / totalKgCO2e));

            var powerUsage = productLca.PowerUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Power, isNotValid ? 0 : (powerUsage / totalKgCO2e));

            var steamUsage = productLca.SteamUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Steam, isNotValid ? 0 : (steamUsage / totalKgCO2e));

            var waterUsage = productLca.WaterUsageSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Water, isNotValid ? 0 : (waterUsage / totalKgCO2e));

            var rentalVehicle = productLca.VehicleSheet
                .Where(x => x.Type == Vehicle.VehicleType.rental)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.RentVehicle, isNotValid ? 0 : (rentalVehicle / totalKgCO2e));

            var outSourcingKitchen = productLca.KitchenSheet
                .Where(x => x.Type == Kitchen.KitchenType.outsourcing)
                .ToList()
                .Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.OutSourcingKitchenAndOtherFuels, isNotValid ? 0 : (outSourcingKitchen / totalKgCO2e));

            var waste = productLca.WasteSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.Waste, isNotValid ? 0 : (waste / totalKgCO2e));

            var wasteTransport = productLca.WasteTransportSheet.Sum(x => x.KgCO2e);
            series.Points.AddXY(Resource.WasteTransportation, isNotValid ? 0 : (wasteTransport / totalKgCO2e));

            CreateImage(workbook, sheet, chart, 0, 91, 9, 116);
        }

        private void CreateImage(IWorkbook workbook, ISheet sheet, Chart chart, int col1, int row1, int col2, int row2)
        {
            var imageMemoryStream = new MemoryStream();
            chart.SaveImage(imageMemoryStream, ChartImageFormat.Png);

            var patriarch = sheet.CreateDrawingPatriarch();
            var anchor = new XSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
            var pictureIndex = workbook.AddPicture(imageMemoryStream.ToArray(), PictureType.PNG);
            var signaturePicture = patriarch.CreatePicture(anchor, pictureIndex);

            imageMemoryStream.Close();
        }

    }
}