using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using Weee.Supports;
using Weee.DAL;
using Weee.Models;
using Weee.Models.ExtensionMethods;
using Weee.Models.Paramemter;
using Weee.DataTransferObject;
using NPOI.SS.UserModel;
// 使用 openxml 匯出 word
using DocumentFormat.OpenXml.Packaging;
using log4net;
using Qcarbon.ViewModels.ExcelImportExport.LcaOrganization.excelExportOrgLCAdata;
using Qcarbon.ViewModels.ExcelImportExport.LcaOrganization.excelExportOrgLCArisk;
using ClosedXML.Excel;
using Qcarbon.Interfaces;
using Qcarbon.ExcelImportExport.LcaOrganization;
using Qcarbon.Interfaces.Database.Lca.Org12.Reports;
using Qcarbon.Database.Lca.Org12.Reports;
using Qcarbon.ViewModels.ExcelImportExport.LcaOrganization.excelExportOrgList;
using NLog;
using Microsoft.AspNet.Identity;
using static Weee.DataTransferObject.OrganizationLCAReportDTO;
using System.Threading;
using Qcarbon.Database.Lca.Org12.Reports.excelExportOrgList;
using Qcarbon.Interfaces.adminCheck;
using Qcarbon.Database.adminCheck;
using Qcarbon.ViewModels.admin;
using Qcarbon.ViewModels.DTO;
using Qcarbon.Database.Lca.Org12.Reports.excelExportOrgList.merged;
using Qcarbon.Interfaces.Database.Lca.Prod;
using Qcarbon.Interfaces.ExcelImportExport.LcaProduct;
using Qcarbon.Database.Lca.Prod;
using Qcarbon.ExcelImportExport.LcaProduct;
using Qcarbon.ViewModels.ExcelImportExport.LcaProduct.excelExportProdLCAdata;
using Qcarbon.ViewModels.ExcelImportExport.LcaProduct.excelExportProdList;
using Qcarbon.ViewModels.ExcelImportExport.LcaProduct.excelExportProdSensitivity;
using Qcarbon.Database.Lca.Org12;
using Qcarbon.Database.Lca.Org36;
using Qcarbon.Database.Lca;
using Qcarbon.ViewModels.ExcelImportExport.LcaOrganization;
using Resources;
using Qcarbon.ExcelImportExport.LcaOrganization.excelImportLCAdata;
using System.EnterpriseServices;
using AutoMapper;

namespace Weee.Service
{
    public class WeeeLCADataService : WeeeDataAuthorizeService
    {
        //public delegate void UpdateStatusEventHandler(object sender, EventArgs e);
        //public event UpdateStatusEventHandler UpdateStatus;
        protected IadminCheckService _adminCheckService;
        protected IcompanyAdminCheckService _companyAdminCheckService;
        protected LCAcommonService _LCAcommonService;
        protected Logger log;

        private readonly EmailService.Service service = DependencyResolver.Current.GetService<EmailService.Service>();

        public WeeeLCADataService(WeeeDataContext d)
            : base(d)
        {
            _LCAcommonService = new LCAcommonService(d);
            log = NLog.LogManager.GetCurrentClassLogger();
        }
        /// <summary>
        /// 產品型盤查狀態變化時 如有完整性 , 一致性之業務邏輯檢查 應於此實作
        /// </summary>
        #region Business logic for Product LCA
        public void ProductLCANewToProcessing(int LCAID, string userName, string baseUrl)//DL-41
        {
            if (!AuthorizedLCAs.Contains(LCAID))
                throw new Exception("not authorzied");
            var LCA = _db.ProductLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.New)
                .Include(x => x.Materials).Single();
            var trans = _db.Database.BeginTransaction();
            try
            {
                foreach (var material in LCA.Materials.Where(x => x.IncludedInInterrogation.HasValue && x.IncludedInInterrogation.Value))
                {
                    // create child LCA for each material to send to supplier company
                    var request = new ProductLCAReplyRequest()
                    {
                        SenderCompanyName = CurrentCompany.Name,
                        //Distribute = material.Unit, 無用的欄位，錯誤不匹配的assignment
                        ReceiverCompanyID = material.SupplierCompanyID
                    };//DL-49
                    if (material.SupplierCompanyEmail != null && material.SupplierCompanyEmail != "")
                    {
                        material.RequestSent = request;
                        service.SendRequestMessaage(material.SupplierCompanyEmail, CurrentCompany.Name, request.RequestLink
                            , request.AnonymousReplyLink, material.PartNumber, material.Name, userName, request.Uid
                            , baseUrl);//DL-41
                                                                                                                     // to do: send an email link for user to apply
                    }
                }
                LCA.Status = LCAStatus.Processing;
                LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Processing, GetUserContext().Name));
                _db.SaveChanges();
                trans.Commit();
            }
            catch(Exception ex)
            {
                trans.Rollback();
                log.Error(ex);
                log.Error(CScommon.Exceptions.inner(ex));
                log.Error(ex.StackTrace);
                throw;
            }
            Thread.Sleep(0);
        }

        public string ProductLCAProcessingToWait(string sourceDir, string targetFile
            , int LCAID, string userName,string baseUrl, bool changeStatus = true
            , bool wordReportOnly = true, bool skipAuthorization = false)
        {
            if (!skipAuthorization && !AuthorizedLCAs.Contains(LCAID))
                throw new Exception("not authorzied");
            //var qry = _db.ProductLCAs
            //    .Include(x => x.Comments)
            //    .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
            //    ).FirstOrDefault();
            var lca = _db.ProductLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                ).Single();
            if (lca.SnapShotSurveyResult == null)
                lca.SnapShotSurveyResult = new ProductLCAFabSurveyResult(lca, lca.TargetProduct);

            lca.SnapShotSurveyResult.Scope1FabResult = lca.GetScopeOnePrimaryFabKgCO2e();
            lca.SnapShotSurveyResult.Scope2FabResult = lca.GetScopeTwoPrimaryFabKgCO2e();
            lca.SnapShotSurveyResult.Scope3FabResult = lca.GetScopeThreePrimaryFabKgCO2e();
            lca.SnapShotSurveyResult.SecondaryWasteResult = lca.WasteSheet.Sum(x => x.KgCO2e);
            lca.SnapShotSurveyResult.SecondaryWasteTransportResult = lca.WasteTransportSheet.Sum(x => x.KgCO2e);
            lca.SnapShotSurveyResult.SecondaryDirectMaterialResult = lca.Materials.Where(x => x.MaterialType == MaterialType.DirectMaterial).Sum(x => x.KgCO2e);
            lca.SnapShotSurveyResult.SecondaryIndirectMaterialResult = lca.Materials.Where(x => x.MaterialType == MaterialType.IndirectMaterial).Sum(x => x.KgCO2e);
            lca.SnapShotSurveyResult.SecondaryWrapMaterialResult = lca.Materials.Where(x => x.MaterialType == MaterialType.WrapMaterial).Sum(x => x.KgCO2e);
            var totalTransportPCS = lca.TransportSheet.Sum(x => x.TransportQuantity);
            totalTransportPCS = totalTransportPCS == 0m ? 1m : totalTransportPCS;
            lca.SnapShotSurveyResult.SecondaryTransportResult = lca.TransportSheet.Sum(x => x.KgCO2e) / totalTransportPCS;

            var totalFabResult = lca.SnapShotSurveyResult.Scope1FabResult
                                + lca.SnapShotSurveyResult.Scope2FabResult
                                + lca.SnapShotSurveyResult.Scope3FabResult
                                + lca.SnapShotSurveyResult.SecondaryWasteResult
                                + lca.SnapShotSurveyResult.SecondaryWasteTransportResult;
            var totalProductResult = lca.SnapShotSurveyResult.SecondaryDirectMaterialResult
                                   + lca.SnapShotSurveyResult.SecondaryIndirectMaterialResult
                                   + lca.SnapShotSurveyResult.SecondaryWrapMaterialResult
                                   + lca.SnapShotSurveyResult.SecondaryTransportResult;

            var areaFactor = lca.SnapShotSurveyResult.constantFactorInArea;
            var weightFactor = lca.SnapShotSurveyResult.constantFactorInWeight;
            var workHourFactor = lca.SnapShotSurveyResult.constantFactorInWorkHour;

            lca.DistributeKgCO2eInWeight = null;
            lca.DistributeKgCO2eInArea = null;
            lca.DistributeKgCO2eInWorkHour = null;
            lca.DistributeKgCO2e = lca.GetDistributedKgCO2e();

            if (areaFactor != null)
                lca.DistributeKgCO2eInArea = totalFabResult * areaFactor.Value + totalProductResult;
            if (weightFactor != null)
                lca.DistributeKgCO2eInWeight = totalFabResult * weightFactor.Value + totalProductResult;
            if (workHourFactor != null)
                lca.DistributeKgCO2eInWorkHour = totalFabResult * workHourFactor.Value + totalProductResult;

            // start of save carbon footprint result
            if (!wordReportOnly)
            {
                var productLcaExcelExporter = new ProductLCAExcelExporter();
                var productLcaWorkbook = productLcaExcelExporter.CreateWorkbook();
                var productLcaSheet = productLcaExcelExporter.CreateSheet(productLcaWorkbook, "碳足跡結果");
                productLcaExcelExporter.CreateInsertableArea(productLcaSheet, 0, 34, 0, 8);
                productLcaExcelExporter.InsertLcaData(productLcaWorkbook, productLcaSheet, lca);
                // end of save carbon footprint result

                SaveExcel(productLcaWorkbook, lca, "CarbonResult", userName, baseUrl);

                // start of save carbon footprint inventory
                var inventoryExcelExporter = new ProductLCAInventoryExcelExporter();
                var inventoryWorkbook = inventoryExcelExporter.CreateWorkbook();
                var carbonDataSheet = inventoryExcelExporter.CreateSheet(inventoryWorkbook, "CO2DATA");

                inventoryExcelExporter.CreateInsertableArea(carbonDataSheet, 0, lca.Materials.Count() + 100, 0, 20);
                MemoryStream chartImage;

                inventoryExcelExporter.InsertCO2Data(inventoryWorkbook, carbonDataSheet, lca, out chartImage);
                SaveExcel(inventoryWorkbook, lca, "Inventory", userName, baseUrl);
            }
            // end of save carbon footprint inventory

            // start of generate word report
            //ExportWordReport(lca, chartImage, userName);
            string ret = ExportWordReport(lca, sourceDir, targetFile, LCAID, userName, baseUrl);
            // end
            if (!changeStatus) // 如果僅產生報表不變更狀態
                return ret;

            lca.Comments.ToList().ForEach(x => x.IsHistory = true);
            lca.Status = LCAStatus.Waiting;
            lca.StatusHistory.Add(new LCAStatusLog(LCAStatus.Waiting, GetUserContext().Name));
            _db.SaveChanges();
            return ret;
        }

        public void ProductLCAWaitToRejected(int LCAID)
        {
            if (CurrentCompany.CompanyType != typeof(CertificationCompany)) throw new Exception("this action is only avaliable for CertificationCompany");
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.ProductLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.Waiting).Include(x => x.Comments).Single();


            LCA.Status = LCAStatus.Rejected;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Rejected, GetUserContext().Name));
            // LCA.Comments.ToList().ForEach(x => x.IsHistory = true);
            _db.SaveChanges();
        }

        public void ProductLCARejectedToProcessing(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.ProductLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.Rejected).Include(x => x.Comments).Single();

            LCA.Status = LCAStatus.Processing;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Processing, GetUserContext().Name));
            _db.SaveChanges();
        }

        // create snapShot and generate excel
        public void ProductLCAWaitToConfirmed(int LCAID)
        {
            if (CurrentCompany.CompanyType != typeof(CertificationCompany)) throw new Exception("this action is only avaliable for CertificationCompany");
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var lca = _db.ProductLCAs.Include(x => x.SnapShotSurveyResult)
                .Where(x => x.ID == LCAID && x.Status == LCAStatus.Waiting).Single();

            lca.Status = LCAStatus.Confirmed;
            lca.StatusHistory.Add(new LCAStatusLog(LCAStatus.Confirmed, GetUserContext().Name));
            lca.Comments.ToList().ForEach(x => x.IsHistory = true);
            _db.SaveChanges();

        }

        public void ProductLCAConfirmedToCompleted(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var lca = _db.ProductLCAs.Include(x => x.RepliedRequests).Where(x => x.ID == LCAID && x.Status == LCAStatus.Confirmed).Single();

            lca.Status = LCAStatus.Completed;

            lca.StatusHistory.Add(new LCAStatusLog(LCAStatus.Completed, GetUserContext().Name));
            _db.SaveChanges();
        }
        #endregion

        /// <summary>
        /// 組織型盤查狀態變化時 如有完整性 , 一致性之業務邏輯檢查 應於此實作
        /// </summary>
        #region Business logic for Organization LCA
        private string makeupDTOfields(OrganizationLCAReportDTO data
            , string userID, int LCAID, bool isMergedRpt)
        {
            string ret = "";
            try
            {
                var qry = (from a in _db.LCAs
                           where a.ID == LCAID
                           select a).FirstOrDefault();
                data.ReportDate = DateTime.Today.ToString("yyyy/MM/dd");
                if (qry == null)
                {
                    ret = $"LCAID {LCAID} 查無資料";
                    data.RiskAssmtThreshold = 100;
                }
                else
                {
                    data.LCAstartYear = qry.StartDate.Year.ToString();
                    data.LCAstart = qry.StartDate.ToString("yyyy/MM/dd");
                    data.LCAend = qry.EndDate.ToString("yyyy/MM/dd");
                    data.LCAstarter = qry.LCAStarter;
                    data.LCAstarterDepartment = qry.LCAStarterDepartment;
                    data.LCAstartPhone = qry.LCAStarterPhone;
                    data.LCAstartEmail = qry.LCAStarterEmail;
                    data.RiskAssmtThreshold = qry.GradingScore == 0 ? 100 : qry.GradingScore;
                    data.ListDefaultPurpose = string.IsNullOrWhiteSpace(qry.ListDefaultPurpose)
                        ? CScommon.ProgramConstants.LIST_DEFAULT_PURPOSE : qry.ListDefaultPurpose;
                    var qry2 = (from a in _db.Companies
                                where a.ID == qry.OwnerID
                                select a).FirstOrDefault();
                    if (qry2 != null)
                        data.companyAddress = qry2.Address;
                }
                var qry3 = (from a1 in _db.LCAs // 基準年資料
                            from a2 in _db.LCAs // 若當下的資料並非基準年，則先用LCAID取出該資料對應的BaseYearLCAID，已取得對應的基準年資料a1
                            join b in _db.OrganizationLCAs on a1.ID equals b.ID
                            where (a1.isBaseYear == true && a1.ID == LCAID) || (a1.isBaseYear == true && a1.UserId == userID && a2.ID == LCAID && a1.ID == a2.BaseYearLCAID)
                            select a1)
                    .OrderByDescending(x => x.StartDate).Take(1).FirstOrDefault();
                //var qry3 = _db.LCAs.Where(x => x.isBaseYear == true)
                //    .OrderByDescending(x => x.ID).Take(1).FirstOrDefault();
                if (qry3 != null)
                {
                    data.BaseYear = qry3.StartDate.Year;
                    decimal baseYearKgCO2e = 0;
                    var qry4a = _db.LCACommonSurveyForm_WorkHour
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4a != null)
                    {
                        foreach (var rec in qry4a)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4b = _db.LCACommonSurveyForm_Vehicle
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4b != null)
                    {
                        foreach (var rec in qry4b)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4c = _db.LCACommonSurveyForm_GasolineEquipment
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4c != null)
                    {
                        foreach (var rec in qry4c)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4d = _db.LCACommonSurveyForm_Kitchen
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4d != null)
                    {
                        foreach (var rec in qry4d)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4e = _db.LCACommonSurveyForm_FireEquipment
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4e != null)
                    {
                        foreach (var rec in qry4e)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4f = _db.LCACommonSurveyForm_Refrigerant
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4f != null)
                    {
                        foreach (var rec in qry4f)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4g = _db.LCACommonSurveyForm_SteamUsage
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4g != null)
                    {
                        foreach (var rec in qry4g)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    var qry4h = _db.LCACommonSurveyForm_PowerUsage
                        .Where(x => x.LCAID == qry3.ID);
                    if (qry4h != null)
                    {
                        foreach (var rec in qry4h)
                            baseYearKgCO2e += rec.KgCO2e;
                    }
                    data.BaseYearTotalValue = baseYearKgCO2e / 1000;
                }
                IEnumerable<RiskAssmtFactorScoreVM> qry5 = null;
                if (!isMergedRpt)
                {
                    var qry5a = (from a in _db.LCARiskAssmtSurveyForm_AssmtFactors
                                 join b in _db.LCARiskAssmtSurveyForm_AssmtFactorScores on a.ID equals b.FactorId
                                 where a.LCAID == LCAID
                                 select new RiskAssmtFactorScoreVM
                                 {
                                     FactorName = a.FactorName,
                                     OptionDescription = b.OptionDescription,
                                     OptionScore = b.OptionScore
                                 });
                    if (qry5a.Any())
                        qry5 = qry5a.ToList();
                }
                else
                {
                    var qry5a = (from a in _db.LCARiskAssmtSurveyForm_AssmtFactors
                                 join b in _db.LCARiskAssmtSurveyForm_AssmtFactorScores on a.ID equals b.FactorId
                                 join e in _db.LCAs on a.LCAID equals e.ID
                                 where e.parentLCAID == LCAID
                                 select new RiskAssmtFactorScoreVM
                                 {
                                     FactorName = a.FactorName,
                                     OptionDescription = b.OptionDescription,
                                     OptionScore = b.OptionScore
                                 });
                    if (qry5a.Any())
                        qry5 = qry5a.ToList();
                }
                data.RiskAssmtFactorText = "評估因子:";
                if (qry5 != null)
                {
                    string curFactor = "";
                    string oneFactor = "";
                    foreach (var rec in qry5)
                    {
                        if (rec.FactorName != curFactor)
                        {
                            if (curFactor != "")
                                data.RiskAssmtFactorText += $"{curFactor}:({oneFactor})";
                            curFactor = rec.FactorName;
                            oneFactor = $"{rec.OptionDescription},{rec.OptionScore}分;";
                        }
                        else
                            oneFactor += $"{rec.OptionDescription},{rec.OptionScore}分;";
                    }
                    data.RiskAssmtFactorText += $"{curFactor}:({oneFactor})";
                }
            }
            catch (Exception ex)
            {
                Logger log = NLog.LogManager.GetCurrentClassLogger();
                log.Error(ex.StackTrace);
                ex = CScommon.Exceptions.inner(ex);
                ret = ex.Message;
                throw;
            }
            return ret;
        }
        public void OrganizationLCAProcessingToWait(int LCAID, string userID, string userName, string baseUrl)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID && x.Status == LCAStatus.Processing).Single();
            //generate word , excel
            //CFT-89
            //string sourceFileName = "OrganizationLCADocTemplate.docx";
            //string sourceFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data", sourceFileName);
            //再發送給查證單位之前，客戶未產生報告書和清冊
            //所以在發送給查證單位時一同爲他產生
            //if(LCA.CarbonFootprintDocxUrl == null && LCA.InterrogationResultUrl ==null)
            {
                string templatePath;
                try
                {
                    templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
                }
                catch
                {
                    templatePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory
                        , "Browser_Local\\WebFilesRoot");
                }
                //Logger log = NLog.LogManager.GetCurrentClassLogger();
                //log.Info($"OrganizationLCAProcessingToWait templatePath={templatePath}");
                templatePath = Path.Combine(templatePath, userName);
                string sourceFilePath = "";
                //get user's template
                var fileFullName = (from u in _db.UserLCADocExcelTemplates
                                    join t in _db.DocExcelTemplates on u.TemplateID equals t.ID
                                    where u.UserID == userID && t.IsDocDefaultTemplate == true
                                    select t.FileName).FirstOrDefault();
                //if (fileFullName != null && fileFullName.Trim() != "")
                //{
                //    fileFullName = fileFullName.Substring(1);
                //    sourceFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fileFullName);
                //}
                //else//用戶未上傳自己的報告書範本，則使用系統默認範本
                {
                    string sourceFileName = CScommon.ProgramConstants.OrganizationWordReport;// "OrganizationLCADocTemplate.docx";
                    sourceFilePath = Path.Combine(templatePath, sourceFileName);
                }
                using (var file = System.IO.File.Open(sourceFilePath, FileMode.Open))
                using (var cache = new MemoryStream())
                using (var excelCache = new MemoryStream())
                {
                    var KgToTon = 0.001m;
                    var dto = _db.OrganizationLCAs
                        .Where(x => x.ID == LCAID && x.Status == LCAStatus.Processing)
                        .Select(x => new OrganizationLCAReportDTO()
                        {
                            CompanyName = x.Owner == null ? "" : x.Owner.Name,
                            FabName = x.TargetFab == null ? "" : x.TargetFab.Name,
                            FabAddress = x.TargetFab == null ? "" : x.TargetFab.Address,
                            WorkhourSum = (x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            PowerSum = (x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            CarSum = (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            GasolineSum = (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            KitchenSum = (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            FireEQSum = (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            RefSum = (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            SteamSum = (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                            OthersSum = (x.OtherCompound.Count == 0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) * KgToTon,
                            ScopeOneTotalValue = ((x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) +
                                                    (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) +
                                                    (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                    (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) +
                                                    (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                    (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) +
                                                    (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e)) +
                                                    (x.OtherCompound.Count == 0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) ) * KgToTon,
                            ScopeTwoTotalValue = x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e) * KgToTon,
                            Table1Data = x.RefrigerantSheet.Select(y => new OrganizationLCAReportDTO.table1Row()
                            {
                                EquipmentName = y.Factor.Type == null ? "" : y.Factor.Type.DisplayNameTW,
                                RefName = y.Parameter.Type == null ? "" : y.Parameter.Type.DisplayNameTW,
                                Value = y.Factor == null ? 0 : y.Factor.Value
                            }).Distinct().ToList(),
                            CertificationCompanyName = x.VerifyBy == null ? "" : x.VerifyBy.Name
                        }).Single();//DL-75
                    makeupDTOfields(dto, userID, LCAID, false);
                    var duringDate = _db.OrganizationLCAs
                        .Where(x => x.ID == LCAID && x.Status == LCAStatus.Processing)
                        .Select(x => new { start = x.StartDate, end = x.EndDate }).Single();
                    dto.DuringInventory = duringDate.start.ToString("yyyy/MM/dd") + " ~ " + duringDate.end.ToString("yyyy/MM/dd");

                    var GWPValue = GetGWPValueByLCAID(LCAID);

                    var qry = (from a in _db.UserAccountType
                               where a.UserId == userID
                               select a.tCO2digits).FirstOrDefault();
                    int digits = 4;
                    if (qry != null)
                        digits = (int)qry;
                    //generate docx
                    file.CopyTo(cache);
                    WordprocessingDocument wordDocument = WordprocessingDocument.Open(cache, true);
                    var parser = new OrganizationLCADocParser(wordDocument);
                    Dictionary<int, decimal> sixCat = get6categoryKgCO2e(LCAID, false);
                    decimal baseYearTonCO2e = baseYearLCATonCO2e(LCAID, false);
                    parser.ReplaceAllTextTagWithData(dto, digits, sixCat, baseYearTonCO2e, GWPValue);
                    wordDocument.MainDocumentPart.Document.Save();
                    cache.Seek(0, SeekOrigin.Begin);
                    //generate 清冊
                    var excelExporter = new OrganizationLCAExcelExport();
                    var workbook = excelExporter.CreateWorkbook();
                    var lcaSheet = excelExporter.CreateSheet(workbook, "清冊");
                    excelExporter.CreateInsertableArea(lcaSheet, 0, 20, 0, 10);
                    excelExporter.InsertOrganizationLcaData(workbook, lcaSheet, dto);
                    workbook.Write(excelCache);
                    //end  
                    using (var temp = new MemoryStream(excelCache.ToArray()))
                    {
                        var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(Storage.AzureStorage)) as Storage.AzureStorage;
                        //Start CFT-6
                        //LCA.CarbonFootprintDocxUrl = storage.SaveToAzure(cache, "CarbonFootprint.docx").ToString();
                        //LCA.InterrogationResultUrl = storage.SaveToAzure(temp, "Lca_Result.xlsx").ToString();
                        var CarbonFootprintDocxUrl = storage.SaveToAzure(cache, "CarbonFootprint.docx", baseUrl).ToString();
                        var InterrogationResultUrl = storage.SaveToAzure(temp, "OrgLca_Result.xlsx", baseUrl).ToString();
                        //Logger log = NLog.LogManager.GetCurrentClassLogger();
                        //log.Info($"CarbonFootprintDocxUrl={CarbonFootprintDocxUrl}");
                        //log.Info($"InterrogationResultUrl={InterrogationResultUrl}");
                        LCA.CarbonFootprintDocxUrl = CarbonFootprintDocxUrl.Substring(CarbonFootprintDocxUrl.IndexOf("Browser_Local") - 1);
                        LCA.InterrogationResultUrl = InterrogationResultUrl.Substring(InterrogationResultUrl.IndexOf("Browser_Local") - 1);
                        //End CFT-6
                    }
                }
            }

            //update status
            LCA.Comments.ToList().ForEach(x => x.IsHistory = true);
            LCA.Status = LCAStatus.Waiting;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Waiting, GetUserContext().Name));

            _db.SaveChanges();

            //CFT-17
            //send eamil to certification company
            string CCemail = "";
            int companyID = LCA.VerifierCompanyID > 0 ? (int)LCA.VerifierCompanyID : 0;
            var userInfo = _db.Users.FirstOrDefault(u => u.CompanyID == companyID);
            if (userInfo != null && userInfo.Email != null && userInfo.Email.Trim() != "")
            {
                CCemail = userInfo.Email.Trim();
                service.SendMessageToCertificationCompany(CCemail);
            }
            //End CFT-17
        }
        protected Dictionary<int, decimal> get6categoryKgCO2e(int LCAID, bool isRptMerged)
        {
            Dictionary<int, decimal> ret = new Dictionary<int, decimal>();
            orgList6GHGinventoryVM GHGrec;
            if (!isRptMerged)
            {
                orgList6GHGinventoryService _orgList6GHGinventoryService =
                    new orgList6GHGinventoryService(_db);
                GHGrec = _orgList6GHGinventoryService.generateReportData(LCAID);
            }
            else
            {
                orgList6GHGinventoryMergeService _orgList6GHGinventoryService =
                    new orgList6GHGinventoryMergeService(_db);
                GHGrec = _orgList6GHGinventoryService.generateReportData(LCAID);
            }
            ret.Add(1, GHGrec.row10.TOTAL);
            ret.Add(2, GHGrec.row20a.TOTAL + GHGrec.row20b.TOTAL);
            ret.Add(3, GHGrec.row30.Stotal + GHGrec.row31.Stotal + GHGrec.row32.Stotal
                + GHGrec.row33.Stotal + GHGrec.row34.Stotal + GHGrec.row35.Stotal);
            ret.Add(4, GHGrec.row40.Stotal + GHGrec.row41a.Stotal + GHGrec.row41b.Stotal
                + GHGrec.row42.Stotal + GHGrec.row43.Stotal + GHGrec.row44.Stotal
                + GHGrec.row45.Stotal);
            ret.Add(5, GHGrec.row50.Stotal + GHGrec.row51.Stotal + GHGrec.row52.Stotal
                + GHGrec.row53.Stotal + GHGrec.row54.Stotal);
            ret.Add(6, GHGrec.row6.Stotal);
            return ret;
        }
        protected int getBaseYearLCAID(int LCAID)
        {
            int ret = 0;
            string sql = $@"
select id 
from (
select a.ID, a.ID oriID
from LCAs a 
where a.isBaseYear=1
union
select b.id, a.ID oriID
from LCAs a 
join LCAs b on a.BaseYearLCAID=b.ID
where b.isBaseYear=1
) a
where oriID={LCAID}
";
            var qry = _db.Database.SqlQuery<int>(sql).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;
        }
        /// <summary>
        /// 取得某盤查單盤查表總排碳量
        /// </summary>
        /// <param name="LCAID">目前盤查單號</param>
        /// <returns></returns>
        protected decimal baseYearLCATonCO2e(int LCAID, bool rptMerged)
        {
            orgLCAdata1resultVM ret;
            excelExportOrgLCAdataVM dataObj;
            IexcelExportLCAdataService realData;
            int baseYearLCAID = 0;
            if (!rptMerged)
            {
                realData = new excelExportLCAdataService(_db);
                baseYearLCAID = getBaseYearLCAID(LCAID);
                if (baseYearLCAID == 0)
                    return 0;
                dataObj =
                    realData.generateReportData(baseYearLCAID);
            }
            else
            {
                realData = new excelExportLCAmergeDataService(_db);
                baseYearLCAID = getBaseYearLCAID(LCAID);
                if (baseYearLCAID == 0)
                    return 0;
                dataObj =
                    realData.generateReportData(baseYearLCAID);
            }
            ret = dataObj._orgLCAdata1resultVM;
            return ret.totalKgCO2e / 1000;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="userID"></param>
        /// <param name="userName"></param>
        /// <param name="option">0: all, 1: 盤查表, 2: 清冊, 3: 報告書, 4: 風險評估表</param>
        /// <returns></returns>
        public string OrganizationLCAGenerateReport(WeeeDataContext _db
            , string sourceDir, string targetFile, int LCAID
            , string userID, string userName, string baseUrl, int option = 0)
        {
            UserAccountType accountType = _db.UserAccountType.Where(x => x.UserId == userID).FirstOrDefault();
            int tCO2digits = (int)(accountType.tCO2digits != null ? accountType.tCO2digits : 4);
            bool inWeb = false;
            if (string.IsNullOrWhiteSpace(sourceDir) && string.IsNullOrWhiteSpace(targetFile))
            {
                inWeb = true;
                sourceDir = AppDomain.CurrentDomain.BaseDirectory;
            }
            bool rptLCAdata = true;//盤查表
            //bool rptLCAdata = false;//盤查表
            bool rptLCAlist = true;//清冊
            bool rptLCAreport = true;//報告書
            //bool rptLCArisk = false;//風險評估表
            bool rptLCArisk = true;//風險評估表
            bool rptMerged = false;
            switch (option)
            {
                case 0://all
                    break;
                case 1://盤查表
                    rptLCAlist = false;
                    rptLCAreport = false;
                    rptLCArisk = false;
                    break;
                case 12://合併清冊
                    rptMerged = true;
                    rptLCAdata = false;
                    rptLCAreport = false;
                    rptLCArisk = false;
                    break;
                case 2://清冊
                    rptLCAdata = false;
                    rptLCAreport = false;
                    rptLCArisk = false;
                    break;
                case 13://合併報告書
                    rptMerged = true;
                    rptLCAdata = false;
                    rptLCAlist = false;
                    rptLCArisk = false;
                    break;
                case 3://報告書
                    rptLCAdata = false;
                    rptLCAlist = false;
                    rptLCArisk = false;
                    break;
                case 4://風險評估表
                    rptLCAdata = false;
                    rptLCAlist = false;
                    rptLCAreport = false;
                    break;
                default:
                    break;
            }
            if (accountType.AccountType == ACCOUNT_TYPE.ORGANICATION12)
                rptLCArisk = false;
            string templatePath;
            try
            {
                templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
            }
            catch
            {
                templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            //Logger log = NLog.LogManager.GetCurrentClassLogger();
            //log.Info($"OrganizationLCAGenerateReport templatePath={templatePath}");
            templatePath = Path.Combine(templatePath, userName);
            if (!inWeb && option == 3)
                templatePath = sourceDir;
            //if (!AuthorizedLCAs.Contains(LCAID)) 
            //    throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                ).Single();
            var fileFullName = Path.Combine(templatePath
                , CScommon.ProgramConstants.OrganizationWordReport);// qry.FirstOrDefault();
            var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(
                typeof(Storage.AzureStorage)) as Storage.AzureStorage;

            var KgToTon = 0.001m;
            OrganizationLCAReportDTO dto;
            if (!rptMerged)
            {
                dto = _db.OrganizationLCAs
                    .Where(x => x.ID == LCAID
                    //&& x.Status == LCAStatus.Processing
                    )
                    .Select(x => new OrganizationLCAReportDTO()
                    {
                        CompanyName = x.Owner == null ? "" : x.Owner.Name,
                        FabName = x.TargetFab == null ? "" : x.TargetFab.Name,
                        FabAddress = x.TargetFab == null ? "" : x.TargetFab.Address,
                        WorkhourSum = (x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        PowerSum = (x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        CarSum = (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        GasolineSum = (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        KitchenSum = (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        FireEQSum = (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        RefSum = (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        SteamSum = (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        OthersSum = (x.OtherCompound.Count == 0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) * KgToTon,
                        ScopeOneTotalValue = ((x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) +
                                                (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) +
                                                        (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                        (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) +
                                                        (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) +
                                                (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e))+
                                                (x.OtherCompound.Count==0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) ) * KgToTon,
                        ScopeTwoTotalValue = x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e) * KgToTon,
                        Table1Data = x.RefrigerantSheet.Select(y => new OrganizationLCAReportDTO.table1Row()
                        {
                            EquipmentName = y.Factor.Type == null ? "" : y.Factor.Type.DisplayNameTW,
                            RefName = y.Parameter.Type == null ? "" : y.Parameter.Type.DisplayNameTW,
                            Value = y.Factor == null ? 0 : y.Factor.Value
                        }).Distinct().ToList(),
                        CertificationCompanyName = x.VerifyBy == null ? "" : x.VerifyBy.Name
                    }).Single();//DL-75
                makeupDTOfields(dto, userID, LCAID, rptMerged);
                //組織邊界設定範圍一覽表
                var qryFab = (from a in _db.OrganizationLCAs
                            join b in _db.Fabs on a.FabID equals b.ID
                            where a.ID == LCAID
                            select b).AsQueryable();
                if (qryFab.Any())
                    dto.fabs = qryFab.ToList();
                else
                    dto.fabs = new List<Fab>();
                // CFT-263 排放係數管理表
                dto.EmitParaMngTableData = new List<OrganizationLCAReportDTO.EmitParaMngTableRow>();
                var rowComparer = new OrganizationLCAReportDTO.EmitParaMngTableRowComparer();
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_PowerUsage
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     where a.LCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(1, "外購電力", 
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/千度",
                        "能源局公告109年電力係數", 2, "外購電力")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_FireEquipment
                     //join b in _db.Parameters on a.ParameterID equals b.ID
                     //join c in _db.NonYearlyParameters on a.ParameterID equals c.ID
                     //join d in _db.NonYearlyParameterTypes on c.TypeID equals d.ID
                     join b in _db.AssessmentReportGWP on a.ARnGWPid equals b.ID
                     join d in _db.LCAs on a.LCAID equals d.ID
                     where a.LCAID == LCAID
                     select new { a, b, d }
                    ).ToList().Select(r => new EmitParaMngTableRow(2, r.a.Name,
                     r.b.CO2Value(r.d.ARversion), r.b.CH4Value(r.d.ARversion), r.b.N2OValue(r.d.ARversion), 
                     r.b.HFCsValue(r.d.ARversion), r.b.PFCsValue(r.d.ARversion),
                     r.b.SF6Value(r.d.ARversion), r.b.NF3Value(r.d.ARversion),
                     r.b.ingredientName, r.b.paraSource,
                    "公噸{gasName}/公噸",
                        CScommon.ProgramConstants.ipccAR6, 1, "逸散 (F)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_GasolineEquipment
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     where a.LCAID == LCAID && d.warmGasType == WarmGasType.固定源
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(3, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        CScommon.ProgramConstants.ipccAR6, 1, "固定 (E)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Kitchen
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     where a.LCAID == LCAID && d.warmGasType == WarmGasType.製程
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(4, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        CScommon.ProgramConstants.ipccAR6, 1, "製程 (P)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Kitchen
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     where a.LCAID == LCAID && d.warmGasType == WarmGasType.固定源
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(4, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        CScommon.ProgramConstants.ipccAR6, 1, "固定 (E)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Refrigerant
                         //join b in _db.Parameters on a.ParameterID equals b.ID
                         //join c in _db.NonYearlyParameters on a.ParameterID equals c.ID
                         //join d in _db.NonYearlyParameterTypes on c.TypeID equals d.ID
                     join b in _db.AssessmentReportGWP on a.ARnGWPid equals b.ID
                     join d in _db.LCAs on a.LCAID equals d.ID
                     where a.LCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(5, r.a.Name,
                     r.b.CO2Value(r.d.ARversion), r.b.CH4Value(r.d.ARversion), r.b.N2OValue(r.d.ARversion),
                     r.b.HFCsValue(r.d.ARversion), r.b.PFCsValue(r.d.ARversion),
                     r.b.SF6Value(r.d.ARversion), r.b.NF3Value(r.d.ARversion),
                     r.b.ingredientName, r.b.paraSource,
                     "公噸{gasName}/公噸",
                        CScommon.ProgramConstants.ipccAR6, 1, "逸散 (F)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Vehicle
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     where a.LCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(6, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        CScommon.ProgramConstants.ipccAR6, 1, "移動 (T)")).Distinct(rowComparer)
                );
            }
            else//合併報表
            {
                var qry = _db.OrganizationLCAs
                    .Where(x => x.parentLCAID == LCAID
                    //&& x.Status == LCAStatus.Processing
                    )
                    .Select(x => new OrganizationLCAReportDTO()
                    {
                        CompanyName = x.Owner == null ? "" : x.Owner.Name,
                        FabName = x.TargetFab == null ? "" : x.TargetFab.Name,
                        FabAddress = x.TargetFab == null ? "" : x.TargetFab.Address,
                        WorkhourSum = (x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        PowerSum = (x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        CarSum = (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        GasolineSum = (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        KitchenSum = (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        FireEQSum = (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        RefSum = (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        SteamSum = (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e)) * KgToTon,
                        OthersSum = (x.OtherCompound.Count == 0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) * KgToTon,
                        ScopeOneTotalValue = ((x.WorkHourSheet.Count == 0 ? 0 : x.WorkHourSheet.Sum(y => y.KgCO2e)) +
                                                (x.VehicleSheet.Count == 0 ? 0 : x.VehicleSheet.Sum(y => y.KgCO2e)) +
                                                        (x.GasolineEquipmentSheet.Count == 0 ? 0 : x.GasolineEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                        (x.KitchenSheet.Count == 0 ? 0 : x.KitchenSheet.Sum(y => y.KgCO2e)) +
                                                        (x.FireEquipmentSheet.Count == 0 ? 0 : x.FireEquipmentSheet.Sum(y => y.KgCO2e)) +
                                                (x.RefrigerantSheet.Count == 0 ? 0 : x.RefrigerantSheet.Sum(y => y.KgCO2e)) +
                                                (x.SteamUsageSheet.Count == 0 ? 0 : x.SteamUsageSheet.Sum(y => y.KgCO2e)) +
                                                (x.OtherCompound.Count == 0 ? 0 : x.OtherCompound.Sum(y => y.KgCO2e)) ) * KgToTon,
                        ScopeTwoTotalValue = x.PowerUsageSheet.Count == 0 ? 0 : x.PowerUsageSheet.Sum(y => y.KgCO2e) * KgToTon,
                        Table1Data = x.RefrigerantSheet.Select(y => new OrganizationLCAReportDTO.table1Row()
                        {
                            EquipmentName = y.Factor.Type == null ? "" : y.Factor.Type.DisplayNameTW,
                            RefName = y.Parameter.Type == null ? "" : y.Parameter.Type.DisplayNameTW,
                            Value = y.Factor == null ? 0 : y.Factor.Value
                        }).Distinct().ToList(),
                        CertificationCompanyName = x.VerifyBy == null ? "" : x.VerifyBy.Name
                    }).AsQueryable();//DL-75
                var qry2 = qry.ToList();
                dto = new OrganizationLCAReportDTO();
                foreach (var rec in qry2)
                {
                    dto.CompanyName = string.IsNullOrWhiteSpace(rec.CompanyName) ? "" : rec.CompanyName;
                    dto.FabName = string.IsNullOrWhiteSpace(rec.FabName) ? "" : rec.FabName;
                    dto.FabAddress = string.IsNullOrWhiteSpace(rec.FabAddress) ? "" : rec.FabAddress;

                    dto.WorkhourSum += rec.WorkhourSum;
                    dto.PowerSum += rec.PowerSum;
                    dto.CarSum += rec.CarSum;
                    dto.GasolineSum += rec.GasolineSum;
                    dto.KitchenSum += rec.KitchenSum;
                    dto.FireEQSum += rec.FireEQSum;
                    dto.RefSum += rec.RefSum;
                    dto.SteamSum += rec.SteamSum;
                    dto.OthersSum+= rec.OthersSum;
                    dto.ScopeOneTotalValue += rec.ScopeOneTotalValue;
                    dto.ScopeTwoTotalValue += rec.ScopeTwoTotalValue;

                    dto.CertificationCompanyName = string.IsNullOrWhiteSpace(rec.CertificationCompanyName) ? "" : rec.CertificationCompanyName;
                    if (dto.Table1Data == null) dto.Table1Data = new List<table1Row>();
                    foreach (var rec2 in rec.Table1Data)
                        dto.Table1Data.Add(rec2);
                }
                makeupDTOfields(dto, userID, LCAID, rptMerged);
                //組織邊界設定範圍一覽表
                var qryFab = (from a in _db.LCAs
                              join b in _db.OrganizationLCAs on a.ID equals b.ID
                              join c in _db.Fabs on b.FabID equals c.ID
                              where a.parentLCAID == LCAID
                              select c).AsQueryable();
                if (qryFab.Any())
                    dto.fabs = qryFab.ToList();
                else
                    dto.fabs = new List<Fab>();
                // CFT-263 排放係數管理表
                dto.EmitParaMngTableData = new List<OrganizationLCAReportDTO.EmitParaMngTableRow>();
                var rowComparer = new OrganizationLCAReportDTO.EmitParaMngTableRowComparer();
                //const string ipccAR6 = @"IPCC AR6係數";
                const string ipccAR6 = @"溫室氣體排放係數管理表6.0.4版";
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_PowerUsage
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     join e in _db.LCAs on a.LCAID equals e.ID
                     where e.parentLCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(1, "外購電力", r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/千度",
                        "能源局公告109年電力係數", 2, "外購電力")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_FireEquipment
                         //join b in _db.Parameters on a.ParameterID equals b.ID
                         //join c in _db.NonYearlyParameters on a.ParameterID equals c.ID
                         //join d in _db.NonYearlyParameterTypes on c.TypeID equals d.ID
                     join b in _db.AssessmentReportGWP on a.ARnGWPid equals b.ID
                     join d in _db.LCAs on a.LCAID equals d.ID
                     where d.parentLCAID == LCAID
                     select new { a, b, d }
                    ).ToList().Select(r => new EmitParaMngTableRow(2, r.a.Name,
                     r.b.CO2Value(r.d.ARversion), r.b.CH4Value(r.d.ARversion), r.b.N2OValue(r.d.ARversion),
                     r.b.HFCsValue(r.d.ARversion), r.b.PFCsValue(r.d.ARversion),
                     r.b.SF6Value(r.d.ARversion), r.b.NF3Value(r.d.ARversion),
                     r.b.ingredientName, r.b.paraSource,
                    "公噸{gasName}/公噸",
                        ipccAR6, 1, "逸散 (F)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_GasolineEquipment
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     join e in _db.LCAs on a.LCAID equals e.ID
                     where e.parentLCAID == LCAID && d.warmGasType == WarmGasType.固定源
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(3, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        ipccAR6, 1, "固定 (E)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Kitchen
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     join e in _db.LCAs on a.LCAID equals e.ID
                     where e.parentLCAID == LCAID && d.warmGasType == WarmGasType.製程
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(4, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        ipccAR6, 1, "製程 (P)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Kitchen
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     join e in _db.LCAs on a.LCAID equals e.ID
                     where e.parentLCAID == LCAID && d.warmGasType == WarmGasType.固定源
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(4, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        ipccAR6, 1, "固定 (E)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Refrigerant
                         //join b in _db.Parameters on a.ParameterID equals b.ID
                         //join c in _db.NonYearlyParameters on a.ParameterID equals c.ID
                         //join d in _db.NonYearlyParameterTypes on c.TypeID equals d.ID
                     join b in _db.AssessmentReportGWP on a.ARnGWPid equals b.ID
                     join d in _db.LCAs on a.LCAID equals d.ID
                     where d.parentLCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(5, r.a.Name,
                     r.b.CO2Value(r.d.ARversion), r.b.CH4Value(r.d.ARversion), r.b.N2OValue(r.d.ARversion),
                     r.b.HFCsValue(r.d.ARversion), r.b.PFCsValue(r.d.ARversion),
                     r.b.SF6Value(r.d.ARversion), r.b.NF3Value(r.d.ARversion),
                     r.b.ingredientName, r.b.paraSource,
                     "公噸{gasName}/公噸",
                        ipccAR6, 1, "逸散 (F)")).Distinct(rowComparer)
                );
                dto.EmitParaMngTableData.AddRange(
                    (from a in _db.LCACommonSurveyForm_Vehicle
                     join b in _db.Parameters on a.ParameterID equals b.ID
                     join c in _db.YearlyParameters on a.ParameterID equals c.ID
                     join d in _db.YearlyParameterTypes on c.TypeID equals d.ID
                     join e in _db.LCAs on a.LCAID equals e.ID
                     where e.parentLCAID == LCAID
                     select new { a, b, d }
                     ).ToList().Select(r => new EmitParaMngTableRow(6, r.a.Name,
                     r.b.CO2Value, r.b.CH4Value, r.b.N2OValue, r.b.HFCsValue, r.b.PFCsValue,
                     r.b.SF6Value, r.b.NF3Value,
                     r.d.DisplayNameTW, r.d.paraSource,
                     "公噸{gasName}/公秉",
                        ipccAR6, 1, "移動 (T)")).Distinct(rowComparer)
                );
            }
            // (外購能源上游生產) 外購電力，CO2 公噸 / 千度，此項例外，資料庫沒有，固定是0.0923，就如範例中固定加入到最後一項
            dto.EmitParaMngTableData.Add(new EmitParaMngTableRow()
            {
                Order = 7,
                EquipmentName = "外購能源 上游生產 (電)",
                ParameterTypeName = "外購電力",
                CO2Value = 0.0923000000M,
                Unit = "公噸{gasName}/千度",
                InfoSource = "碳足跡計算服務平台",
                EmitSourceCategory = 4,
                EmitSourceType = "外購電力 上游"
            });

            dto.EmitParaMngTableData = dto.EmitParaMngTableData.Distinct().ToList();

            var duringDate = _db.OrganizationLCAs
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                )
                .Select(x => new { start = x.StartDate, end = x.EndDate }).Single();
            //報告書                 
            string companyName = CompanyNameByLCAID(LCAID);
            if (rptLCAreport && fileFullName != null && fileFullName.Trim() != "")
            {
                //fileFullName = fileFullName.Substring(1);
                string sourceFilePath = Path.Combine(sourceDir, fileFullName);

                var qry = (from a in _db.UserAccountType
                           where a.UserId == userID
                           select a.tCO2digits).FirstOrDefault();
                int digits = 4;
                if (qry != null)
                    digits = (int)qry;
                //Logger log = NLog.LogManager.GetCurrentClassLogger();
                //log.Error("App_Error", context.Exception);
                //log.Info($"OrganizationLCAGenerateReport sourceFilePath={sourceFilePath}");
                using (var file = System.IO.File.Open(sourceFilePath, FileMode.Open))
                using (var wordMemoryStream = new MemoryStream())
                {
                    dto.DuringInventory = duringDate.start.ToString("yyyy/MM/dd") + " ~ " + duringDate.end.ToString("yyyy/MM/dd");
                    {
                        file.CopyTo(wordMemoryStream);
                        WordprocessingDocument wordDocument = WordprocessingDocument.Open(wordMemoryStream, true);
                        var parser = new OrganizationLCADocParser(wordDocument);

                        //實際變更報告書內容處
                        //rptMerged 合併報告書
                        Dictionary<int, decimal> sixCat = get6categoryKgCO2e(LCAID, rptMerged);
                        decimal baseYearTonCO2e = baseYearLCATonCO2e(LCAID, rptMerged);
                        var GWPValue = GetGWPValueByLCAID(LCAID);
                        parser.ReplaceAllTextTagWithData(dto, digits, sixCat, baseYearTonCO2e,GWPValue);

                        wordDocument.MainDocumentPart.Document.Save();
                        wordMemoryStream.Seek(0, SeekOrigin.Begin);
                        //IexcelExportLCAdataService realData;
                        //realData = new excelExportLCAdataService(_db);
                        //excelExportOrgLCAdataVM dataObj =
                        //    realData.generateReportData(LCAID);       
                        string filename = $"{companyName}_報告書{LCAID}.docx";
                        if (inWeb)
                        {
                            var CarbonFootprintDocxUrl = storage.SaveToAzure(wordMemoryStream, filename, baseUrl, userName).ToString();
                            //Logger log = NLog.LogManager.GetCurrentClassLogger();
                            //log.Info($"CarbonFootprintDocxUrl 2={CarbonFootprintDocxUrl}");
                            LCA.CarbonFootprintDocxUrl = CarbonFootprintDocxUrl.Substring(CarbonFootprintDocxUrl.IndexOf("Browser_Local") - 1);
                        }
                        else
                        {
                            FileStream fs = new FileStream(targetFile, FileMode.Create, FileAccess.Write);
                            wordMemoryStream.WriteTo(fs);
                            fs.Close();
                            LCA.CarbonFootprintDocxUrl = "";
                        }
                    }
                }
            }
            //產生清冊
            if (rptLCAlist)
            {
                _adminCheckService = new adminCheckService(_db, userID);
                _companyAdminCheckService = new companyAdminCheckService(_db, userID);
                if (!_adminCheckService.adminCheckPass())//若未能規避權限控管檢查
                {
                    try
                    {
                        AdminAccess aa = _adminCheckService.getAdminAccess();
                        if (aa == null)
                            throw new Exception("伺服器尚未匯入金鑰，請洽系統管理員處理");
                        int listReportQuantity = _adminCheckService.getListReportQuantity();
                        if (listReportQuantity >= aa.listReportLimit && aa.listReportLimit != -1)
                            throw new Exception(
                                $"盤查清冊數({listReportQuantity})已達或超過全站限制" +
                                $"({aa.listReportLimit})，請系統管理員更新金鑰限制重新匯入");
                        companyAdminAccessVM uaaVM = _companyAdminCheckService
                            .getCompanyAdminAccessVM(
                                _companyAdminCheckService.userId2companyID(userID));
                        int listReportQuantityByUserId =
                            _companyAdminCheckService.getListReportQuantityByUserId(userID);
                        if (listReportQuantityByUserId >= uaaVM.listReportLimit
                                && uaaVM.listReportLimit != -1)
                            throw new Exception($"盤查清冊數({listReportQuantityByUserId})已達或超過公司限制" +
                                $"({uaaVM.listReportLimit})，請公司管理員更新限制重新嘗試");
                    }
                    catch (Exception ex)
                    {
                        log.Error(ex);
                        log.Error(CScommon.Exceptions.inner(ex));
                        log.Error(ex.StackTrace);
                        throw;
                    }
                }
                // 產生盤查表 Thomas Mao
                string reportTemplateRootPath;
                reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.OrganizationExcelList);
                if (!File.Exists(reportTemplateRootPath))
                    throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelList}不存在！");

                XLWorkbook workBook;
                if (!rptMerged)
                {
                    IexcelExportOrgListWrapper reportWrapper
                        = new excelExportOrgListWrapper(templatePath);
                    IexcelExportOrgListService realData;
                    realData = new excelExportOrgListService(_db);
                    excelExportOrgListVM dataObj;
                    dataObj = realData.generateReportData(LCAID);
                    dataObj._orgList2CalculationVM.tCO2digits = tCO2digits;
                    dataObj._orgList4UncertaintyVMdomestic.tCO2digits = tCO2digits;
                    dataObj._orgList4UncertaintyVMforeign.tCO2digits = tCO2digits;
                    dataObj._orgList5DataQualityVM.tCO2digits = tCO2digits;
                    dataObj._orgList6GHGinventoryVM.tCO2digits = tCO2digits;
                    workBook = reportWrapper.generateReport(dataObj);
                }
                else
                {
                    IexcelExportOrgMergeListService realData;
                    IexcelExportOrgMergeListWrapper reportWrapper
                        = new excelExportOrgMergeListWrapper(templatePath);
                    realData = new excelExportOrgMergeListService(_db);
                    excelExportOrgMergeListVM dataObj;
                    dataObj = realData.generateReportData(LCAID);
                    if (dataObj._orgList2CalculationLstVM != null)
                    {
                        foreach (var rec in dataObj._orgList2CalculationLstVM)
                            rec.tCO2digits = tCO2digits;
                    }
                    dataObj._orgList4UncertaintyVMdomestic.tCO2digits = tCO2digits;
                    dataObj._orgList4UncertaintyVMforeign.tCO2digits = tCO2digits;
                    dataObj._orgList5DataQualityVM.tCO2digits = tCO2digits;
                    dataObj._orgList6GHGinventoryVM.tCO2digits = tCO2digits;
                    workBook = reportWrapper.generateReport(dataObj);
                }

                using (var LCAdataMemoryStream = new MemoryStream())
                {
                    workBook.SaveAs(LCAdataMemoryStream);
                    // 更新清冊url Thomas Mao
                    using (var LCAdataRptMS = new MemoryStream(LCAdataMemoryStream.ToArray()))
                    {
                        var InterrogationResultUrl = storage.SaveToAzure(LCAdataRptMS
                            , $"{companyName}_清冊{LCAID}.xlsx", baseUrl, userName).ToString();
                        //Logger log = NLog.LogManager.GetCurrentClassLogger();
                        //log.Info($"InterrogationResultUrl={InterrogationResultUrl}");
                        LCA.InterrogationResultUrl = InterrogationResultUrl.Substring(
                            InterrogationResultUrl.IndexOf("Browser_Local") - 1);
                        LCA.listReportGeneratedTime = DateTime.Now;
                    }
                }
            }
            //產生盤查表
            if (rptLCAdata)
            {
                // 產生盤查表 Thomas Mao
                string reportTemplateRootPath;
                reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.OrganizationExcelLCAdata);
                if (!File.Exists(reportTemplateRootPath))
                    throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelLCAdata}不存在！");
                IexcelExportLCAdataWrapper reportWrapper
                    = new excelExportLCAdataWrapper(templatePath);
                IexcelExportLCAdataService realData;
                realData = new excelExportLCAdataService(_db);
                excelExportOrgLCAdataVM dataObj =
                    realData.generateReportData(LCAID);
                dataObj._orgLCAdata1resultVM.tCO2digits = tCO2digits;
                XLWorkbook workBook = reportWrapper.generateReport(dataObj);
                using (var LCAdataMemoryStream = new MemoryStream())
                {
                    workBook.SaveAs(LCAdataMemoryStream);
                    // 更新盤查表url Thomas Mao
                    using (var LCAdataRptMS = new MemoryStream(LCAdataMemoryStream.ToArray()))
                    {
                        var LCAdataRptUrl = storage.SaveToAzure(LCAdataRptMS, $"{companyName}_盤查表{LCAID}.xlsx", baseUrl, userName).ToString();
                        LCA.LCAdataRptUrl = LCAdataRptUrl.Substring(LCAdataRptUrl.IndexOf("Browser_Local") - 1);
                    }
                }
            }
            //產生風險評估報表
            if (rptLCArisk)
            {
                string reportTemplateRootPath;
                reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.OrganizationExcelLCArisk); //風險評估表
                if (!File.Exists(reportTemplateRootPath))
                    throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelLCArisk}不存在！");
                IexcelExportLCAriskWrapper reportWrapper
                    = new excelExportLCAriskWrapper(templatePath);
                IexcelExportLCAriskService realData;
                realData = new excelExportLCAriskService(_db);
                excelExportOrgLCAriskVM dataObj =
                    realData.generateReportData(LCAID);
                dataObj._orgLCArisk1lifeCycleVM.tCO2digits = tCO2digits;
                dataObj._orgLCArisk4listContentVM.tCO2digits = tCO2digits;
                XLWorkbook workBook = reportWrapper.generateReport(dataObj);
                workBook.Worksheet("生命週期流程圖及評估").Cell($"B2").Value = $"{companyName}產品生命週期流程圖";
                using (var LCAriskMemoryStream = new MemoryStream())
                {
                    workBook.SaveAs(LCAriskMemoryStream);
                    using (var LCAriskRptMS = new MemoryStream(LCAriskMemoryStream.ToArray()))
                    {
                        var LCAriskRptUrl = storage.SaveToAzure(LCAriskRptMS, $"{companyName}_風險評估表{LCAID}.xlsx", baseUrl, userName).ToString();
                        LCA.LCAriskRptUrl = LCAriskRptUrl.Substring(LCAriskRptUrl.IndexOf("Browser_Local") - 1);
                    }
                }
            }
            //End CFT-6
            _db.SaveChanges();
            if (!inWeb && option == 3)
                return "";
            string url = "";
            string actPath = System.Web.HttpContext.Current.Request.Url.Authority;
            //string fullPath = HttpContext.Current.Request.Url.ToString();
            string protocolName;// = fullPath.Substring(0, fullPath.IndexOf("//") + 2);
            if (HttpContext.Current.Request.IsSecureConnection)
                protocolName = "https://";
            else
                protocolName = "http://";
            actPath = protocolName + actPath;
            //ILog log = LogManager.GetLogger(typeof(MvcApplication));
            //log.Info($"WeeeLCADataService OrganizationLCAGenerateReport actPath={actPath}");
            //log.Info($"IsSecureConnection={HttpContext.Current.Request.IsSecureConnection}");
            log.Info($"org report baseUrl={baseUrl} actPath={actPath}");
            if (LCA.InterrogationResultUrl != null)
            {
                if (rptLCAlist)
                    url = actPath + baseUrl + LCA.InterrogationResultUrl.Trim();
                if (rptLCAreport && LCA.CarbonFootprintDocxUrl != null)
                {
                    if (!string.IsNullOrWhiteSpace(url))
                        url += ",";
                    url += actPath + baseUrl + LCA.CarbonFootprintDocxUrl;
                }
            }
            else
            {
                if (rptLCAreport && LCA.CarbonFootprintDocxUrl != null)
                {
                    url = actPath + baseUrl + LCA.CarbonFootprintDocxUrl.Trim();
                }
            }
            if (rptLCAdata)
            {
                if (!string.IsNullOrWhiteSpace(url))
                    url += ",";
                url += actPath + baseUrl + LCA.LCAdataRptUrl;
            }

            if (rptLCArisk)
            {
                if (!string.IsNullOrWhiteSpace(url))
                    url += ",";
                url += actPath + baseUrl + LCA.LCAriskRptUrl;
            }
            return url;
        }

        //產品型盤查表
        public string ProductLCAdataGenerateReport(WeeeDataContext _db
            , string sourceDir, string targetFile, int LCAID
            , string userID, string userName, string baseUrl)
        {
            string url = "";

            string companyName = CompanyNameByLCAID(LCAID);
            UserAccountType accountType = _db.UserAccountType.Where(x => x.UserId == userID).FirstOrDefault();
            int tCO2digits = (int)(accountType.tCO2digits != null ? accountType.tCO2digits : 4);
            bool inWeb = false;
            if (string.IsNullOrWhiteSpace(sourceDir) && string.IsNullOrWhiteSpace(targetFile))
            {
                inWeb = true;
                sourceDir = AppDomain.CurrentDomain.BaseDirectory;
            }
            string templatePath;
            try
            {
                templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
            }
            catch
            {
                templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            //Logger log = NLog.LogManager.GetCurrentClassLogger();
            //log.Info($"OrganizationLCAGenerateReport templatePath={templatePath}");
            templatePath = Path.Combine(templatePath, userName);
            if (!inWeb )//&& option == 3)
                templatePath = sourceDir;

            var LCA = _db.ProductLCAs//.OrganizationLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                ).Single();
            var fileFullName = Path.Combine(templatePath
                , CScommon.ProgramConstants.OrganizationWordReport);// qry.FirstOrDefault();
            var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(
                typeof(Storage.AzureStorage)) as Storage.AzureStorage;
            // 產生盤查表 Thomas Mao
            string reportTemplateRootPath;
            reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.ProductExcelLCAdata);
            if (!File.Exists(reportTemplateRootPath))
                throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelLCAdata}不存在！");

            IexcelExportProdLCAdataService realData;
            IexcelExportProdLCAdataWrapper reportWrapper;
            realData = new excelExportProdLCAdataService(_db);
            reportWrapper = new excelExportProdLCAdataWrapper(templatePath);
            excelExportProdLCAdataVM dataObj =
                realData.generateReportData(LCAID);
            //dataObj._orgLCAdata1resultVM.tCO2digits = tCO2digits;
            XLWorkbook workBook = reportWrapper.generateReport(dataObj);

            using (var LCAdataMemoryStream = new MemoryStream())
            {
                workBook.SaveAs(LCAdataMemoryStream);
                // 更新盤查表url Thomas Mao
                using (var LCAdataRptMS = new MemoryStream(LCAdataMemoryStream.ToArray()))
                {
                    var LCAdataRptUrl = storage.SaveToAzure(LCAdataRptMS, $"{companyName}_產品盤查表{LCAID}.xlsx", baseUrl, userName).ToString();
                    LCA.LCAdataRptUrl = LCAdataRptUrl.Substring(LCAdataRptUrl.IndexOf("Browser_Local") - 1);
                }
            }
            var protocolName = "";
            if (HttpContext.Current.Request.IsSecureConnection)
                protocolName = "https://";
            else
                protocolName = "http://";
            string actPath = System.Web.HttpContext.Current.Request.Url.Authority;
            actPath = protocolName + actPath;
            url = actPath + baseUrl + LCA.LCAdataRptUrl;
            return url;
        }

        //產品型清冊
        public string ProductListGenerateReport(WeeeDataContext _db
            , string sourceDir, string targetFile, int LCAID
            , string userID, string userName, string baseUrl)
        {
            string url = "";

            string companyName = CompanyNameByLCAID(LCAID);
            UserAccountType accountType = _db.UserAccountType.Where(x => x.UserId == userID).FirstOrDefault();
            int tCO2digits = (int)(accountType.tCO2digits != null ? accountType.tCO2digits : 4);
            bool inWeb = false;
            if (string.IsNullOrWhiteSpace(sourceDir) && string.IsNullOrWhiteSpace(targetFile))
            {
                inWeb = true;
                sourceDir = AppDomain.CurrentDomain.BaseDirectory;
            }
            string templatePath;
            try
            {
                templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
            }
            catch
            {
                templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            //Logger log = NLog.LogManager.GetCurrentClassLogger();
            //log.Info($"OrganizationLCAGenerateReport templatePath={templatePath}");
            templatePath = Path.Combine(templatePath, userName);
            if (!inWeb)//&& option == 3)
                templatePath = sourceDir;

            var LCA = _db.ProductLCAs//.OrganizationLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                ).Single();
            var fileFullName = Path.Combine(templatePath
                , CScommon.ProgramConstants.OrganizationWordReport);// qry.FirstOrDefault();
            var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(
                typeof(Storage.AzureStorage)) as Storage.AzureStorage;
            // 產生盤查表 Thomas Mao
            string reportTemplateRootPath;
            reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.ProductExcelList);
            if (!File.Exists(reportTemplateRootPath))
                throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelLCAdata}不存在！");

            IexcelExportProdListService realData;
            IexcelExportProdListWrapper reportWrapper;
            realData = new excelExportProdListService(_db);
            reportWrapper = new excelExportProdListWrapper(templatePath);
            excelExportProdListVM dataObj =
                realData.generateReportData(LCAID);
            //dataObj._orgLCAdata1resultVM.tCO2digits = tCO2digits;
            XLWorkbook workBook = reportWrapper.generateReport(dataObj);

            using (var LCAdataMemoryStream = new MemoryStream())
            {
                workBook.SaveAs(LCAdataMemoryStream);
                // 更新盤查表url Thomas Mao
                using (var LCAdataRptMS = new MemoryStream(LCAdataMemoryStream.ToArray()))
                {
                    var LCAdataRptUrl = storage.SaveToAzure(LCAdataRptMS, $"{companyName}_產品清冊{LCAID}.xlsx", baseUrl, userName).ToString();
                    LCA.LCAdataRptUrl = LCAdataRptUrl.Substring(LCAdataRptUrl.IndexOf("Browser_Local") - 1);
                }
            }
            var protocolName = "";
            if (HttpContext.Current.Request.IsSecureConnection)
                protocolName = "https://";
            else
                protocolName = "http://";
            string actPath = System.Web.HttpContext.Current.Request.Url.Authority;
            actPath = protocolName + actPath;
            url = actPath + baseUrl+ LCA.LCAdataRptUrl;
            return url;
        }

        //敏感度分析
        public string ProductSensitivityGenerateReport(WeeeDataContext _db
            , string sourceDir, string targetFile, int LCAID
            , string userID, string userName, string baseUrl)
        {
            string url = "";

            string companyName = CompanyNameByLCAID(LCAID);
            UserAccountType accountType = _db.UserAccountType.Where(x => x.UserId == userID).FirstOrDefault();
            int tCO2digits = (int)(accountType.tCO2digits != null ? accountType.tCO2digits : 4);
            bool inWeb = false;
            if (string.IsNullOrWhiteSpace(sourceDir) && string.IsNullOrWhiteSpace(targetFile))
            {
                inWeb = true;
                sourceDir = AppDomain.CurrentDomain.BaseDirectory;
            }
            string templatePath;
            try
            {
                templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
            }
            catch
            {
                templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            //Logger log = NLog.LogManager.GetCurrentClassLogger();
            //log.Info($"OrganizationLCAGenerateReport templatePath={templatePath}");
            templatePath = Path.Combine(templatePath, userName);
            if (!inWeb)//&& option == 3)
                templatePath = sourceDir;

            var LCA = _db.ProductLCAs//.OrganizationLCAs
                .Include(x => x.Comments)
                .Where(x => x.ID == LCAID //&& x.Status == LCAStatus.Processing
                ).Single();
            var fileFullName = Path.Combine(templatePath
                , CScommon.ProgramConstants.OrganizationWordReport);// qry.FirstOrDefault();
            var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(
                typeof(Storage.AzureStorage)) as Storage.AzureStorage;

            string reportTemplateRootPath;
            reportTemplateRootPath = Path.Combine(templatePath, CScommon.ProgramConstants.ProductSensitivity);
            if (!File.Exists(reportTemplateRootPath))
                throw new Exception($"{userName} {CScommon.ProgramConstants.OrganizationExcelLCAdata}不存在！");

            IExcelExportProdSensitivityService realData;
            IexcelExportProdSensitivityWrapper reportWrapper;
            realData = new ExcelExportProdSensitivityService(_db);
            reportWrapper = new excelExportProdSensitivityWrapper(templatePath);
            excelExportProdSensitivityVM dataObj =
                realData.generateReportData(LCAID);
            //dataObj._orgLCAdata1resultVM.tCO2digits = tCO2digits;
            XLWorkbook workBook = reportWrapper.generateReport(dataObj);

            using (var LCAdataMemoryStream = new MemoryStream())
            {
                workBook.SaveAs(LCAdataMemoryStream);
                // 更新url 
                using (var LCAdataRptMS = new MemoryStream(LCAdataMemoryStream.ToArray()))
                {
                    var LCAdataRptUrl = storage.SaveToAzure(LCAdataRptMS, $"{companyName}_敏感性分析{LCAID}.xlsx", baseUrl, userName).ToString();
                    LCA.LCAdataRptUrl = LCAdataRptUrl.Substring(LCAdataRptUrl.IndexOf("Browser_Local") - 1);
                }
            }
            var protocolName = "";
            if (HttpContext.Current.Request.IsSecureConnection)
                protocolName = "https://";
            else
                protocolName = "http://";
            string actPath = System.Web.HttpContext.Current.Request.Url.Authority;
            actPath = protocolName + actPath;
            url = actPath + baseUrl + LCA.LCAdataRptUrl;
            return url;
        }

        internal void SaveCertificationZipFile(int LCAID, string filePath)
        {
            var LCA = _db.LCAs.Where(x => x.ID == LCAID).Single();
            if(LCA != null)
            {
                LCA.CertificationFeedbackZipUrl = filePath;
                _db.SaveChanges();
            }
        }

        public void OrganizationLCAWaitToRejected(int LCAID)
        {
            if (CurrentCompany.CompanyType != typeof(CertificationCompany)) throw new Exception("this action is only avaliable for CertificationCompany");
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs.Where(x => x.ID == LCAID && (x.Status == LCAStatus.Waiting || x.Status==LCAStatus.Completed)).Include(x => x.Comments).Single();

            LCA.Status = LCAStatus.Rejected;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Rejected, GetUserContext().Name));
            //           LCA.Comments.ToList().ForEach(x => x.IsHistory = true);
            _db.SaveChanges();
        }

        public void OrganizationLCARejectedToProcessing(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.Rejected).Include(x => x.Comments).Single();

            LCA.Status = LCAStatus.Processing;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Processing, GetUserContext().Name));
            _db.SaveChanges();
        }

        public void OrganizationLCAWaitToConfirmed(int LCAID)
        {
            if (CurrentCompany.CompanyType != typeof(CertificationCompany)) throw new Exception("this action is only avaliable for CertificationCompany");
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.Waiting).Single();

            LCA.Status = LCAStatus.Confirmed;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Confirmed, GetUserContext().Name));
            LCA.Comments.ToList().ForEach(x => x.IsHistory = true);

            _db.SaveChanges();

        }

        public void OrganizationLCAConfirmedToCompleted(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.Confirmed).Single();

            LCA.Status = LCAStatus.Completed;

            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Completed, GetUserContext().Name));

            _db.SaveChanges();
        }

        public void OrganizationLCANewToCompleted(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.OrganizationLCAs.Where(x => x.ID == LCAID && x.Status == LCAStatus.New).Single();

            LCA.Status = LCAStatus.Completed;
            LCA.StatusHistory.Add(new LCAStatusLog(LCAStatus.Completed, GetUserContext().Name));
            _db.SaveChanges();
        }

        /// <summary>
        /// 盤查單位拒絕擔任該盤查單之盤查單位
        /// </summary>
        /// <param name="LCAID"></param>
        public void LCARefuse(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.LCAs.Where(x => x.ID == LCAID).Single();
            LCA.VerifierCompanyID = null;
            _db.SaveChanges();

        }
        public void LcaChangeScheduledVerificationDate(int LCAID, DateTime? scheduledVerificationDate = null)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorzied");
            var LCA = _db.LCAs.Where(x => x.ID == LCAID).Single();
            LCA.ScheduledVerificationDate = scheduledVerificationDate;
            _db.SaveChanges();

        }

        public bool CheckOrganizationBaseYearIsCorrect(OrganizationLCA lca, string userID)
        {
            // 是否為基準年
            if (!lca.isBaseYear)
            {
                // 非基準年的話，確認基準年盤查單的建立者是否為本人，且必須勾選為基準年
                if (!_db.LCAs.Where(x => x.UserId == userID && lca.BaseYearLCAID == x.ID && x.isBaseYear).Any())
                    return false;
            }
            return true;
        }
        #endregion

        #region Get list
        public IEnumerable<ProductLCAReplyRequest> GetReceivedRequests(string userName)//DL-50
        {
            var replyRequests = _db.ProductLCAReplyRequests
                .Include(x => x.RepliedByWhichLCA)
                .Include(x => x.SentByWhichMaterial)
                .Where(x => x.ReceiverCompanyID == CurrentCompany.ID)
                ;
                //.ToList();
            var qry = replyRequests
                .Where(x => x.SentByWhichMaterial.SupplierCompanyName.ToLower().Trim() == userName.ToLower().Trim())
                .AsQueryable();
            var result = qry
                .OrderByDescending(x => x.CreatedDate)
                //.ThenBy(x => x.SentByWhichMaterial.PartNumber)
                .ToList();
            return result;
            //end
        }

        public IEnumerable<LCA> GetLCAs(string UserId="")
        {
            IEnumerable<LCA> ret;
            if (UserId=="")
                ret = _db.LCAs
                    .Where(x => x.OwnerID == CurrentCompany.ID)
                    .OrderByDescending(x => x.ID)
                    .ToList();
            else
                ret = _db.LCAs
                    .Where(x => x.UserId == UserId)
                    .OrderByDescending(x => x.ID)
                    .ToList();
            //Start CFT-54
            int userType = UserAccountType != null ? (int)UserAccountType.AccountType : 0;
            //if(userType != 2)
            //{
            //    ret = (from r in ret
            //           where !(from pl in _db.ProductLCAs select pl.ID).Contains(r.ID)
            //           select r).ToList();
            //}
            //End CFT-54
            return ret;
        }
        //DL-51
        public IEnumerable<LCA> GetLCAsForUser(string UserName)
        {
            //根据log获取当前用户创建的盘查
            var logs = (from log in _db.LCAStatusLogs where log.WhoDidThis.Trim().ToLower() == UserName.Trim().ToLower()
                        group log by log.LCA into rg select
                            (from l in rg orderby l.ID ascending select l).FirstOrDefault());//.ToList();

            var result = (from lca in _db.LCAs.Local
                          join l in logs on lca.ID equals l.LCAID
                          select lca).ToList();
            //if (!string.IsNullOrWhiteSpace(userID))
            //{
            //    result = result.Where(x => x.UserId == userID);
            //    return result.ToList();
            //}
            //Start CFT-54
            int userType = UserAccountType != null ? (int) UserAccountType.AccountType : 0;
            //if (userType != 2)
            //{
            //    result  = (from r in result
            //           where !(from pl in _db.ProductLCAs select pl.ID).Contains(r.ID)
            //           select r).ToList();
            //}
            //End CFT-54
            return result;
        }

        //DL-51
        public IEnumerable<ProductLCAReplyRequest> GetRequestForUser(string UserName, string Name, IEnumerable<ProductLCAReplyRequest> sendRequests)
        {

            //根据log获取当前用户发送的盘查指令
            var logs = (from log in _db.LCAStatusLogs
                        where (log.WhoDidThis.Trim().ToLower() == UserName.Trim().ToLower() || log.WhoDidThis.Trim().ToLower() == Name.Trim().ToLower())
                        group log by log.LCA into rg
                        select (from l in rg orderby l.ID ascending select l).FirstOrDefault()).ToList();
            var result = (from r in sendRequests
                          join m in _db.ProductLCAProductSurveyForm_Material.Local on r.ID equals m.ID
                          join l in logs on m.LCAID equals l.LCAID
                          select r).ToList();
            return result;
        }
        #endregion

        #region Get and Save detail
        public Type GetLCAType(int id)
        {
            return _db.LCAs.Find(id).LCAType;
        }

        public LCA GetLCA(int id)
        {
            if (AuthorizedLCAs.Contains(id))
            {
                LCA ret = _db.LCAs.Find(id);
                if (string.IsNullOrWhiteSpace(ret.ListDefaultPurpose))
                    ret.ListDefaultPurpose = CScommon.ProgramConstants.LIST_DEFAULT_PURPOSE;
                return ret;
            }
            throw new Exception("not authorized");
        }

        public ProductLCA GetProductLCA(int id)
        {
            if (AuthorizedLCAs.Contains(id))
            {
                return _db.ProductLCAs.Find(id);
            }
            throw new Exception("not authorized");
        }

        public ProductLCA GetProductLCAWithInclude(int id)
        {
            if (AuthorizedLCAs.Contains(id))
            {
                return _db.ProductLCAs.Include(x => x.Materials)
                                      .Include(x => x.Materials.Select(y => y.AssignedParameter))
                                      .Include(x => x.Materials.Select(y => y.AssignedParameterType))
                                      .Include(x => x.Materials.Select(y => y.RequestSent.ReceiveBy))
                                      .Include(x => x.WorkHourSheet)
                                      .Include(x => x.PowerUsageSheet)
                                      .Include(x => x.WaterUsageSheet)
                                      .Include(x => x.WasteSheet)
                                      .Include(x => x.WasteSheet.Select(y => y.Parameter))
                                      .Include(x => x.WasteSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.TransportSheet)
                                      .Include(x => x.TransportSheet.Select(y => y.NonYearlyParameter))
                                      .Include(x => x.TransportSheet.Select(y => y.NonYearlyParameter.Type))
                                      .Include(x => x.WasteTransportSheet)
                                      //.Include(x => x.WasteTransportSheet.Select(y => y.Parameter))
                                      //.Include(x => x.WasteTransportSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.VehicleSheet)
                                      .Include(x => x.VehicleSheet.Select(y => y.Parameter))
                                      .Include(x => x.VehicleSheet.Select(y => y.Parameter.Area))
                                      .Include(x => x.VehicleSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.GasolineEquipmentSheet)
                                      .Include(x => x.GasolineEquipmentSheet.Select(y => y.Parameter))
                                      .Include(x => x.GasolineEquipmentSheet.Select(y => y.Parameter.Area))
                                      .Include(x => x.GasolineEquipmentSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.KitchenSheet)
                                      .Include(x => x.KitchenSheet.Select(y => y.Parameter))
                                      .Include(x => x.KitchenSheet.Select(y => y.Parameter.Area))
                                      .Include(x => x.KitchenSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.RefrigerantSheet)
                                      .Include(x => x.RefrigerantSheet.Select(y => y.Parameter))
                                      .Include(x => x.RefrigerantSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.RefrigerantSheet.Select(y => y.Factor))
                                      .Include(x => x.RefrigerantSheet.Select(y => y.Factor.Type))
                                      .Include(x => x.FireEquipmentSheet)
                                      .Include(x => x.FireEquipmentSheet.Select(y => y.Parameter))
                                      .Include(x => x.FireEquipmentSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.SteamUsageSheet)
                                      .Include(x => x.SteamUsageSheet.Select(y => y.Parameter))
                                      .Include(x => x.SteamUsageSheet.Select(y => y.Parameter.Type))
                                      .Include(x => x.OtherCompoundSheet)
                                      .Include(x => x.OtherCompoundSheet.Select(y => y.Parameter))
                                      .Include(x => x.OtherCompoundSheet.Select(y => y.Parameter.Type))
                                      .Where(x => x.ID == id).Single();
            }

            throw new Exception("not authroized");
        }

        public OrganizationLCA GetOrganizationLCA(int id)
        {
            if (AuthorizedLCAs.Contains(id))
            {
                return _db.OrganizationLCAs.Find(id);
            }
            throw new Exception("not authorized");
        }

        public OrganizationLCA GetOrganizationLCAWithInclude(int id)
        {
            if (AuthorizedLCAs.Contains(id))
            {
                return _db.OrganizationLCAs
                       .Include(x => x.PowerUsageSheet)
                       .Include(x => x.VehicleSheet)
                       .Include(x => x.VehicleSheet.Select(y => y.Parameter))
                       .Include(x => x.VehicleSheet.Select(y => y.Parameter.Area))
                       .Include(x => x.VehicleSheet.Select(y => y.Parameter.Type))
                       .Include(x => x.RefrigerantSheet)
                       .Include(x => x.RefrigerantSheet.Select(y => y.Parameter))
                       .Include(x => x.RefrigerantSheet.Select(y => y.Parameter.Type))
                       .Include(x => x.RefrigerantSheet.Select(y => y.Factor))
                       .Include(x => x.RefrigerantSheet.Select(y => y.Factor.Type))
                       .Include(x => x.SteamUsageSheet)
                       .Include(x => x.SteamUsageSheet.Select(y => y.Parameter))
                       .Include(x => x.SteamUsageSheet.Select(y => y.Parameter.Type))
                       .Where(x => x.ID == id).Single();
            }

            throw new Exception("not authroized");
        }
        public bool isOrgLCA(int LCAID)
        {
            bool ret = false;
            var qry = (from a in _db.LCAs
                       join b in _db.OrganizationLCAs on a.ID equals b.ID
                       where a.ID == LCAID
                       select a.ID).FirstOrDefault();
            if (qry != null && qry>0)
                ret = true;
            return ret;
        }
        public bool isProdLCA(int LCAID)
        {
            bool ret = false;
            var qry = (from a in _db.LCAs
                       join b in _db.ProductLCAs on a.ID equals b.ID
                       where a.ID == LCAID
                       select a.ID).FirstOrDefault();
            if (qry != null && qry>0)
                ret = true;
            return ret;
        }
        public decimal[] GetRptLCAdata(int LCAID)
        {
            IexcelExportLCAdataService realData;
            realData = new excelExportLCAdataService(_db);
            excelExportOrgLCAdataVM dataObj = realData.generateReportData(LCAID);
            var ret = new decimal[] {0, 0, 0, 0, 0};
            if (!isOrgLCA(LCAID))
                return ret;
            //固定式燃燒之直接排放
            ret[0] = dataObj._orgLCAdata1resultVM.fuels固定KgCO2e;
            //移動式燃燒之直接排放
            ret[1] = dataObj._orgLCAdata1resultVM.fuels移動KgCO2e;
            //工業製程之直接排放量及移除量
            ret[2] = dataObj._orgLCAdata1resultVM.fuels製程KgCO2e;
            //人為系統中釋放之溫室氣體的直接逸散性排放
            ret[3] = dataObj._orgLCAdata1resultVM.workHrKgCO2e+ dataObj._orgLCAdata1resultVM.rfgKgCO2e + dataObj._orgLCAdata1resultVM.fireKgCO2e + dataObj._orgLCAdata1resultVM.othersKgCO2e;
            //土地使用、土地使用變更和林業的直接排放和移除
            ret[4] = 0; 
            return ret;
        }
        public decimal[] GetRptList(int LCAID)
        {
            IexcelExportOrgListService realData;
            realData = new excelExportOrgListService(_db);
            excelExportOrgListVM dataObj = realData.generateReportData(LCAID);
            //var data = (from l in _db.LCARiskAssmtSurveyForm_LifecycleAssmts
            //           where l.category != null && l.GHGinventoryItemNo != null && l.LCAID == LCAID
            //           group l by l.category into g
            //           select new { category = g.Key, sum = g.Sum(x=>x.EmissionT)}).ToList();

            //                       select sum(EmissionKg), category
            //from LCARiskAssmtSurveyForm_LifecycleAssmt
            //where category is not null and GHGinventoryItemNo is not null
            //    and LCAID = 494
            //group by category
            //order by category
            var ret = new decimal[] { 0, 0, 0, 0, 0, 0};
            if (!isOrgLCA(LCAID))
                return ret;
            //第一類排放
            ret[0] = dataObj._orgList6GHGinventoryVM.row10.TOTAL;
            //第二類排放
            ret[1] = //dataObj._orgList6GHGinventoryVM.row21.CO2 + dataObj._orgList6GHGinventoryVM.row22.CO2;
                dataObj._orgList6GHGinventoryVM.row21.TOTAL+ dataObj._orgList6GHGinventoryVM.row22.TOTAL;
            //第三類排放
            ret[2] = //data.Where(x => x.category == "3").Any() ? data.Where(x => x.category == "3").First().sum : (decimal)0.00;
                dataObj._orgList6GHGinventoryVM.row31.Stotal + dataObj._orgList6GHGinventoryVM.row32.Stotal
                + dataObj._orgList6GHGinventoryVM.row33.Stotal + dataObj._orgList6GHGinventoryVM.row34.Stotal 
                + dataObj._orgList6GHGinventoryVM.row35.Stotal;
            //第四類排放
            ret[3] = //data.Where(x => x.category == "4").Any() ? data.Where(x => x.category == "4").First().sum : (decimal)0.00;
                dataObj._orgList6GHGinventoryVM.row41a.Stotal + dataObj._orgList6GHGinventoryVM.row41b.Stotal
                + dataObj._orgList6GHGinventoryVM.row42.Stotal + dataObj._orgList6GHGinventoryVM.row43.Stotal
                + dataObj._orgList6GHGinventoryVM.row44.Stotal + dataObj._orgList6GHGinventoryVM.row45.Stotal;
            //第五類排放
            ret[4] = //data.Where(x => x.category == "5").Any() ? data.Where(x => x.category == "5").First().sum : (decimal)0.00;
                dataObj._orgList6GHGinventoryVM.row51.Stotal + dataObj._orgList6GHGinventoryVM.row52.Stotal
                + dataObj._orgList6GHGinventoryVM.row53.Stotal + dataObj._orgList6GHGinventoryVM.row54.Stotal;
            //第六類排放
            ret[5] = //data.Where(x => x.category == "6").Any() ? data.Where(x => x.category == "6").First().sum : (decimal)0.00;
                dataObj._orgList6GHGinventoryVM.row6.Stotal;
            return ret;
        }

        public string RecalculateProductLCA(int LCAID, bool dueToARchange )
        {
            string err = "";
            // 如果AR4-6版本變更，那麼
            // 若ARversion為null，那麼parameterID來自於Nonyearlyparameter
            // 反之，則parameterID來自AssessmentReportGWP，然後由ARversionGuid，去AssessmentReportVersion抓出新的parameterID
            // 然後再重算

            // RecalculateProductLCA
            Org12RecalculateService o12rs = new Org12RecalculateService(_db);
            err = o12rs.proceedRecalculate(LCAID);
            if (!string.IsNullOrWhiteSpace(err))
                return err;
            ProdRecalculateService prs= new ProdRecalculateService(_db);
            err = prs.proceedRecalculate(LCAID);
            return err;
        }
        public ProductLCA SaveProductLCA(ProductLCA ToBeSave)
        {
            ToBeSave.OwnerID = CurrentCompany.ID;
            if (ToBeSave.ARversion == null)
                ToBeSave.ARversion = "AR6";
            var entry = _db.Entry(ToBeSave);
            bool recalculate = false;
            if (ToBeSave.ID == 0)
            {
                //business logic , if pcrid is null means that this lca do not need to send Request to others
                //therefore we assign status to processing , rather then using the status update api to do this task
                //if (ToBeSave.PCRID == null) ToBeSave.Status = LCAStatus.Processing;
                if (ToBeSave.PCRID == null) ToBeSave.Status = LCAStatus.New;
                entry.State = EntityState.Added;
            }
            else if (AuthorizedLCAs.Contains(ToBeSave.ID))
            {
                var lcastatus = _db.ProductLCAs
                    .Where(x => x.ID == ToBeSave.ID)
                    .Select(x => x.Status)
                    .FirstOrDefault();
                var ARversion = _db.ProductLCAs
                    .Where(x => x.ID == ToBeSave.ID)
                    .Select(x => x.ARversion)
                    .FirstOrDefault();
                if (ARversion!=ToBeSave.ARversion) 
                    recalculate = true;
                if (lcastatus == LCAStatus.New || lcastatus == LCAStatus.Processing)
                {
                    entry.State = EntityState.Modified;
                    entry.Property(x => x.LCAStarter).IsModified = true;
                    entry.Property(x => x.LCAStarterDepartment).IsModified = true;
                    entry.Property(x => x.LCAStarterJob).IsModified = true;
                    entry.Property(x => x.LCAStarterPhone).IsModified = true;
                    entry.Property(x => x.LCAStarterEmail).IsModified = true;
                    entry.Property(x => x.FabProductionArea).IsModified = true;
                    entry.Property(x => x.FabProductionHour).IsModified = true;
                    entry.Property(x => x.ProductProductionPcs).IsModified = true;
                    entry.Property(x => x.ProductProductionHour).IsModified = true;
                    entry.Property(x => x.FabProductionWeight).IsModified = true;
                    entry.Property(x => x.Description).IsModified = true;
                    entry.Property(x => x.VerifierCompanyID).IsModified = true;
                    entry.Property(x => x.parentLCAID).IsModified = true;
                    entry.Property(x => x.ProductAllocationFireEquipment).IsModified = true;
                    entry.Property(x => x.ProductProductionEconomic).IsModified = true;
                    entry.Property(x => x.ProductAllocationWorkHour).IsModified = true;
                    entry.Property(x => x.ProductAllocationPowerUsage).IsModified = true;
                    entry.Property(x => x.ProductAllocationWaterUsage).IsModified = true;
                    entry.Property(x => x.ProductAllocationWaste).IsModified = true;
                    entry.Property(x => x.ProductAllocationWasteWater).IsModified = true;
                    entry.Property(x => x.ProductAllocationWasteTransport).IsModified = true;
                    entry.Property(x => x.ProductAllocationVehicle).IsModified = true;
                    entry.Property(x => x.ProductAllocationGasolineEquipment).IsModified = true;
                    entry.Property(x => x.ProductAllocationKitchen).IsModified = true;
                    entry.Property(x => x.ProductAllocationFireEquipment).IsModified = true;
                    entry.Property(x => x.ProductAllocationRefrigerant).IsModified = true;
                    entry.Property(x => x.ProductAllocationSteamUsage).IsModified = true;
                    entry.Property(x => x.ProductAllocationOtherCompound).IsModified = true;
                    entry.Property(x => x.ProductAllocationTransportUpstream).IsModified = true;
                    entry.Property(x => x.ProductAllocationTransportDownstream).IsModified = true;
                    entry.Property(x=>x.ARversion).IsModified = true;
                }
            }
            _db.SaveChanges();
            string err = "";
            if (recalculate)
            {
                err = RecalculateProductLCA(ToBeSave.ID, true);
                if (!string.IsNullOrWhiteSpace(err))
                    throw new Exception(err);
            }
            return ToBeSave;
        }
        public string RecalculateOrganizationLCA(int LCAID, bool dueToARchange)
        {
            string err = "";
            // 如果AR4-6版本變更，那麼
            // 若ARversion為null，那麼parameterID來自於Nonyearlyparameter
            // 反之，則parameterID來自AssessmentReportGWP，然後由ARversionGuid，去AssessmentReportVersion抓出新的parameterID
            // 然後再重算

            // RecalculateOrganizationLCA
            Org12RecalculateService o12rs = new Org12RecalculateService(_db);
            err = o12rs.proceedRecalculate(LCAID);
            if (!string.IsNullOrWhiteSpace(err))
                return err;
            Org36RecalculateService o36rs = new Org36RecalculateService(_db);
            err = o36rs.proceedRecalculate(LCAID);
            return err;
        }
        public OrganizationLCA SaveOrganizationLCA(OrganizationLCA ToBeSave)
        {
            ToBeSave.OwnerID = CurrentCompany.ID;
            //ToBeSave.LCAStarterUserName = UserName;//CFT-25
            var entry = _db.Entry(ToBeSave);
            if (ToBeSave.ARversion == null)
                ToBeSave.ARversion = "AR6";
            bool recalculate = false;
            bool isAdd = false;
            if (ToBeSave.ID == 0)
            {
                //Company.HasLCAs.Add(ToBeCreated);
                ToBeSave.Status = LCAStatus.Processing;
                entry.State = EntityState.Added;
                isAdd = true;
            }
            else if (AuthorizedLCAs.Contains(ToBeSave.ID))
            {
                var lcastatus = _db.OrganizationLCAs
                    .Where(x => x.ID == ToBeSave.ID)
                    .Select(x => x.Status)
                    .FirstOrDefault();
                var ARversion = _db.OrganizationLCAs
                    .Where(x => x.ID == ToBeSave.ID)
                    .Select(x => x.ARversion)
                    .FirstOrDefault();
                if (ARversion != ToBeSave.ARversion)
                    recalculate = true;
                if (lcastatus == LCAStatus.New || lcastatus == LCAStatus.Processing)
                {
                    entry.State = EntityState.Modified;
                    entry.Property(x => x.Status).IsModified = false;
                    entry.Property(x => x.FabID).IsModified = false;
                    //entry.Property(x => x.VerifierCompanyID).IsModified = false; // CFT-833
                    entry.Property(x => x.StartDate).IsModified = false;
                    entry.Property(x => x.EndDate).IsModified = false;
                    entry.Property(x => x.CreatedDate).IsModified = false;
                    entry.Property(x => x.parentLCAID).IsModified = true;
                    entry.Property(x=>x.ARversion).IsModified = true;
                }
            }
            _db.SaveChanges();
            if (isAdd)
            {
                var defaultFactors = GetDefaultAssmtFactor(ToBeSave.ID);
                _db.LCARiskAssmtSurveyForm_AssmtFactors.AddRange(defaultFactors);
                _db.SaveChanges();
            }
            string err = "";
            if (recalculate)
            {
                err = RecalculateOrganizationLCA(ToBeSave.ID, true);
                if (!string.IsNullOrWhiteSpace(err))
                    throw new Exception(err);
            }
            return ToBeSave;
        }
        public List<LCARiskAssmtSurveyForm_AssmtFactor> GetDefaultAssmtFactor(int LCAID)
        {
            var ret = new List<LCARiskAssmtSurveyForm_AssmtFactor>();
            ret.Add(new LCARiskAssmtSurveyForm_AssmtFactor() { LCAID = LCAID, FactorName = Resource.DefaultAssmtFactor1 });
            ret[0].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore1_1, OptionScore = 10 });
            ret[0].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore1_2, OptionScore = 5 });
            ret[0].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore1_3, OptionScore = 1 });

            ret.Add(new LCARiskAssmtSurveyForm_AssmtFactor() { LCAID = LCAID, FactorName = Resource.DefaultAssmtFactor2 });
            ret[1].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore2_1, OptionScore = 10 });
            ret[1].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore2_2, OptionScore = 5 });
            ret[1].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore2_3, OptionScore = 1 });

            ret.Add(new LCARiskAssmtSurveyForm_AssmtFactor() { LCAID = LCAID, FactorName = Resource.DefaultAssmtFactor3 });
            ret[2].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore3_1, OptionScore = 10 });
            ret[2].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore3_2, OptionScore = 5 });
            ret[2].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore3_3, OptionScore = 1 });

            ret.Add(new LCARiskAssmtSurveyForm_AssmtFactor() { LCAID = LCAID, FactorName = Resource.DefaultAssmtFactor4 });
            ret[3].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore4_1, OptionScore = 5 });
            ret[3].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore4_2, OptionScore = 3 });
            ret[3].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore4_3, OptionScore = 1 });
            
            ret.Add(new LCARiskAssmtSurveyForm_AssmtFactor() { LCAID = LCAID, FactorName = Resource.DefaultAssmtFactor5 });
            ret[4].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore5_1, OptionScore = 5 });
            ret[4].FactorScores.Add(new LCARiskAssmtSurveyForm_AssmtFactorScore() { OptionDescription = Resource.DefaultAssmtFactorScore5_2, OptionScore = 1 });
            return ret;
        }

        public IEnumerable<ProductLCAProductSurveyForm_Materials> GetMaterials(string userName)//DL-49 DL-50
        {
            //var supplier = _db.Suppliers.FirstOrDefault(x => x.Name.ToLower().Trim() == userName.ToLower().Trim());
            var materials = _db.ProductLCAProductSurveyForm_Material.Where(x => x.SupplierCompanyName.ToLower().Trim() == userName.ToLower().Trim()).ToList();
            return materials;
        }
        #endregion

        #region For reply request
        public ProductLCAReplyRequest ApplyRepliedRequest(Guid requestID)
        {
            var request = _db.ProductLCAReplyRequests
                .Include(x => x.SentByWhichMaterial.OwnerLCA)
                .Where(x => x.Uid == requestID)
                .Single();
            var material = request.SentByWhichMaterial;
            var lca = material.OwnerLCA;
            if (!AuthorizedLCAs.Contains(request.SendLCAID)) throw new Exception("not auhtorized");
            if (request.CanBeApply && lca.Status == LCAStatus.Processing)
            {
                material.oriKgCO2e = material.KgCO2e;
                //material.oriHighLevelAnalyzeResult = material.HighLevelAnalyzeResult;
                material.modifyTime = DateTime.Now;

                material.ParameterValue = request.RepliedValue.Value;
                material.KgCO2e = material.ActivityIntensity * material.ParameterValue;
                material.KgCO2e_BOM = material.ActivityIntensity_BOM * material.ParameterValue;
                material.ParameterSource = 1;
                // 如果回覆盤查的帳號是自己或是自己的子帳號，就會顯示”特定廠址數據”。
                if (lca.OwnerID == request.ReceiverCompanyID )
                    material.ParameterSource = 3;
                //material.HighLevelAnalyzeResult = request.RepliedValue.Value* material.Quantity;
                request.AcceptReplyDate = DateTime.Now;
                _db.SaveChanges();
            }
            return request;
        }

        public ProductLCAReplyRequest ReplyRequest(Guid RequestID, int LCAID, ProductLCAReplyRequest.DivideByOptions divideby)
        //public ProductLCAReplyRequest ReplyRequest(Guid RequestID, int LCAID)
        {
            if (AuthorizedLCAs.Contains(LCAID))
            {
                var request = _db.ProductLCAReplyRequests
                    .Where(x => x.Uid == RequestID && x.ReceiverCompanyID == CurrentCompany.ID)
                    .Single();
                var lca = _db.ProductLCAs.Find(LCAID);
                if (request.CanBeReplied && lca.CanReplyOtherRequest)
                {
                    // 用來計算單位個數的排碳量
                    decimal ratio = lca.ProductProductionPcs > 0 ? (decimal)lca.ProductProductionPcs : 1;
                    //if (divideby == ProductLCAReplyRequest.DivideByOptions.byWeight)
                    //{
                    //    request.RepliedValue = lca.DistributeKgCO2eInWeight / ratio;
                    //}
                    //else if (divideby == ProductLCAReplyRequest.DivideByOptions.byArea)
                    //{
                    //    request.RepliedValue = lca.DistributeKgCO2eInArea / ratio;
                    //}
                    //else if (divideby == ProductLCAReplyRequest.DivideByOptions.byWorkHour)
                    //{
                    //    request.RepliedValue = lca.DistributeKgCO2eInWorkHour / ratio;
                    //}

                    // 不需要再額外選統一的分配方式 而是依照各功能各自的分配方式做計算 
                    request.RepliedValue = lca.DistributeKgCO2e;// Thomas 1/20, 註解掉是因為lca.DistributeKgCO2e會是null,若不是要確定一下怎麼算出來的
                    request.DivideBy = divideby;
                    request.ReplyDate = DateTime.Now;
                    request.RepliedLCAID = LCAID;
                }
                else if (!lca.CanReplyOtherRequest)
                {
                    request.RepliedValue = null;
                    request.RepliedLCAID = LCAID;
                }
                _db.SaveChanges();
                return request;
            }
            return null;
        }

        public void DeleteRequest(Guid Uid)
        {
            var request = _db.ProductLCAReplyRequests.Where(x => x.Uid == Uid).Single();
            if (!AuthorizedLCAs.Contains(request.SendLCAID)) throw new Exception("not authorized");
            if (request.CanBeDelete)
            {
                _db.ProductLCAReplyRequests.Remove(request);
                _db.SaveChanges();
            }
        }

        //public void ResendRequest(Guid Uid)
        //{
        //    var request = _db.ProductLCAReplyRequests
        //        .Include(x => x.SentByWhichMaterial)
        //        .Where(x => x.Uid == Uid)
        //        .Single();
        //    if (!AuthorizedLCAs.Contains(request.SendLCAID)) throw new Exception("not authorized");
        //    request.LastResentDate = DateTime.Now;
        //    service.SendRequestMessaage(request.MaterialSupplierEmail, CurrentCompany.Name
        //        , request.RequestLink, request.AnonymousReplyLink,request.MaterialPartNumber
        //        , request.MaterialName,"");//DL-41
        //    _db.SaveChanges();
        //}

        public ProductLCAReplyRequest ResetRequest(int id)
        {
            ProductLCAReplyRequest request = _db.ProductLCAReplyRequests.Where(x => x.ID == id).First();
            request.CreatedDate = DateTime.Now;
            if (request.AcceptDate != null)
            {
                request.AcceptDate = DateTime.Now;
            }
            else
            {
                request.AcceptDate = null;
            }
            request.ReplyDate = null;
            request.AcceptReplyDate = null;

            _db.SaveChanges();

            return request;
        }
        #endregion

        #region Excel Export
        private void SaveExcel(IWorkbook workbook, LCA lca, string type , string userName, string baseUrl)
        {
            // generate excel report 清冊產生處 ?
            if (type == "" ||
                type != "Inventory" &&
                type != "CarbonResult")
            {
                throw new Exception("Can not save type not exist excel");
            }

            using (var memoryStream = new MemoryStream())
            {
                workbook.Write(memoryStream);
                using (var memoryStream2 = new MemoryStream(memoryStream.ToArray()))
                {
                    var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(Storage.AzureStorage)) as Storage.AzureStorage;
                    if (type == "Inventory")
                    {
                        //DL-38
                        var url = storage.SaveToAzure(memoryStream2, "Inventory.xlsx", baseUrl, userName).ToString();
                        lca.ProductInventoryResultUrl = url.Substring(url.IndexOf("Browser_Local") - 1);
                        //lca.ProductInventoryResultUrl = storage.SaveToAzure(memoryStream2, "Inventory.xlsx").ToString();
                    }
                    else if (type == "CarbonResult")
                    {
                        //DL-38
                        var url = storage.SaveToAzure(memoryStream2, "ProdLca_Result.xlsx", baseUrl, userName).ToString();
                        lca.InterrogationResultUrl = url.Substring (url.IndexOf("Browser_Local") - 1);
                        //lca.InterrogationResultUrl = storage.SaveToAzure(memoryStream2, "Lca_Result.xlsx").ToString();
                    }
                }
            }
        }
        #endregion

        #region Word Export
        private string ExportWordReport(ProductLCA productLca, string sourceDir
            , string targetFile, int LCAID, string userName, string baseUrl)
        {
            // get tamplate file path
            string sourceFileName = CScommon.ProgramConstants.ProductWordReport;
            //string sourceDir = "";
            bool inWeb = false;
            if (string.IsNullOrWhiteSpace(sourceDir) && string.IsNullOrWhiteSpace(targetFile))
            {
                inWeb = true;
                sourceDir = AppDomain.CurrentDomain.BaseDirectory;
            }

            string templatePath;
            try
            {
                if (inWeb)
                    templatePath = HttpContext.Current.Server.MapPath("~/Browser_Local/WebFilesRoot");
                else
                    templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            catch
            {
                templatePath = Path.Combine(sourceDir, "Browser_Local\\WebFilesRoot");
            }
            //Logger log = NLog.LogManager.GetCurrentClassLogger();
            //log.Info($"OrganizationLCAGenerateReport templatePath={templatePath}");
            templatePath = Path.Combine(templatePath, userName);
            if (!inWeb)
                templatePath = sourceDir;
            var fileFullName = Path.Combine(templatePath, sourceFileName);

            //string sourceFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory
            //    , "App_Data", sourceFileName);
            // 從 sourceFilePath 到 targetFile
            using (var file = System.IO.File.Open(fileFullName, FileMode.Open))
            using (var cache = new MemoryStream())
            //using (var aa = System.IO.File.Create("../../../CompanyCarbonFootprintReport.docx"))
            {
                file.CopyTo(cache);
                // get new file
                WordprocessingDocument wordDocument = WordprocessingDocument.Open(cache, true);

                ProductLCADocParser docParser = new ProductLCADocParser(wordDocument
                    , _db);

                // replace text on file
                BeReplacedTextDTO beReplacedText = PrepareWordData(productLca);
                //try
                {
                    docParser.ReplaceAllTextTagWithData(beReplacedText);
                }
                //catch(Exception ex)
                //{
                //    log.Error(ex.Message);
                //    log.Error(CScommon.Exceptions.inner(ex));
                //    log.Error(ex.StackTrace);
                //    throw;
                //}
                //docParser.InsertChart(imageStream);
                docParser.SaveDocument();

                cache.Seek(0, SeekOrigin.Begin);
                var storage = GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(Storage.AzureStorage)) as Storage.AzureStorage;
                //DL-38
                string productName = ProductNameByLCAID(LCAID);
                string filename = $"{productName}_產品型報告書{LCAID}.docx";
                if (inWeb)
                {
                    var url = storage.SaveToAzure(cache, filename, baseUrl, userName).ToString();
                    productLca.CarbonFootprintDocxUrl = url.Substring(url.IndexOf("Browser_Local") - 1);
                    //productLca.CarbonFootprintDocxUrl = storage.SaveToAzure(cache, "CarbonFootprint.docx").ToString();
                }
                else
                {
                    FileStream fs = new FileStream(targetFile, FileMode.Create, FileAccess.Write);
                    cache.WriteTo(fs);
                    fs.Close();
                    productLca.CarbonFootprintDocxUrl = "";
                }
            }
            string protocolName;
            if (inWeb && HttpContext.Current.Request.IsSecureConnection)
                protocolName = "https://";
            else
                protocolName = "http://";
            string ret;
            if (inWeb)
                ret= protocolName + System.Web.HttpContext.Current.Request.Url.Authority + baseUrl
                    + productLca.CarbonFootprintDocxUrl;
            else
                ret = productLca.CarbonFootprintDocxUrl;
            return ret;
        }

        private BeReplacedTextDTO PrepareWordData(ProductLCA productLca)
        {
            string decimalFormat = "G5";
            string percentFormat = "P4";
            var surveyResult = productLca.SnapShotSurveyResult;
            BeReplacedTextDTO beReplacedText = new BeReplacedTextDTO();

            //產品型報告書填入欄位
            beReplacedText.LCAID = productLca.ID;

            var product = productLca.TargetProduct;
            beReplacedText.ProductName = product == null ? "" : product.Name;
            beReplacedText.PartNumber = product == null ? "" : product.SerialNumber; ;
            beReplacedText.ProductSpec = product == null ? "" : product.SpecDescription; ;

            var owner = productLca.Owner;
            beReplacedText.CompanyName = owner == null ? "" : owner.Name;

            var fab = productLca.TargetFab;
            beReplacedText.FabName = fab == null ? "" : fab.Name;
            beReplacedText.FabAddress = fab == null ? "" : fab.Address;

            beReplacedText.DuringInventory = productLca.StartDate.ToShortDateString() + " ~ " + productLca.EndDate.ToShortDateString();

            var inventoryStageData = productLca.TargetInventoryStageData;

            beReplacedText.TurnOnHourPerDayInWorkDay = inventoryStageData == null ? "" :
                inventoryStageData.TurnOnHourPerDayInWorkDay.ToString();
            beReplacedText.TurnOnHourPerDayInOffDay = inventoryStageData == null ? "" :
                inventoryStageData.TurnOnHourPerDayInOffDay.ToString();
            beReplacedText.WorkDayPerYear = inventoryStageData == null ? "" :
                inventoryStageData.WorkDayPerYear.ToString();
            beReplacedText.WattInOperation = inventoryStageData == null ? "" :
                inventoryStageData.WattInOperation.ToString(decimalFormat);
            beReplacedText.WattInLowOperation = inventoryStageData == null ? "" :
                inventoryStageData.WattInLowOperation.ToString(decimalFormat);
            beReplacedText.EnergyConsumption = inventoryStageData == null ? "" :
                EnergyConsumption(inventoryStageData).ToString(decimalFormat);
            beReplacedText.UsageStageDescription = inventoryStageData == null ? "" :
                inventoryStageData.ProductUsageStageDescription;
            beReplacedText.PowerFactor = inventoryStageData == null ? "" :
                inventoryStageData.ProductUsageStagePowerFactor.ToString(decimalFormat);
            beReplacedText.AbandonedStageDescription = inventoryStageData == null ? "" :
                inventoryStageData.ProductAbandonedStage;

            var materialProductionAndTransportPhase = CarbonEmissionCalculator.GetMaterialProductionAndTransportPhase(productLca.Materials);
            beReplacedText.MaterialProductionAndTransportPhase = materialProductionAndTransportPhase.ToString(decimalFormat);

            var productionPhase = CarbonEmissionCalculator.GetProductionPhase(productLca);
            beReplacedText.ProductionPhase = productionPhase.ToString(decimalFormat);

            var devliveryPhase = CarbonEmissionCalculator.GetDeliveryPhase(productLca);
            beReplacedText.DeliveryPhase = devliveryPhase.ToString(decimalFormat);

            var usagePhase = inventoryStageData.GetUsageStageCarbonFootprint();
            beReplacedText.UsagePhase = usagePhase.ToString(decimalFormat);

            var abandonedPhase = CarbonEmissionCalculator.GetAbandonedPhase(inventoryStageData);
            beReplacedText.AbandonedPhase = abandonedPhase.ToString(decimalFormat);

            beReplacedText.PowerYear = productLca.StartDate != null ? productLca.StartDate.Year.ToString() : "";

            var allPhaseCarbonEmission = materialProductionAndTransportPhase
                                        + productionPhase
                                        + devliveryPhase
                                        + usagePhase
                                        + abandonedPhase;
            beReplacedText.AllPhaseCarbonEmission = allPhaseCarbonEmission != 0 ? allPhaseCarbonEmission.ToString(decimalFormat) : "0";
            beReplacedText.MaterialProductionAndTransportPhasePercent = allPhaseCarbonEmission != 0 ? (materialProductionAndTransportPhase / allPhaseCarbonEmission).ToString(percentFormat) : "0";
            beReplacedText.ProductionPhasePercent = allPhaseCarbonEmission != 0 ? (productionPhase / allPhaseCarbonEmission).ToString(percentFormat) : "0";
            beReplacedText.DeliveryPhasePercent = allPhaseCarbonEmission != 0 ? (devliveryPhase / allPhaseCarbonEmission).ToString(percentFormat) : "0";
            beReplacedText.UsagePhasePercent = allPhaseCarbonEmission != 0 ? (usagePhase / allPhaseCarbonEmission).ToString(percentFormat) : "0";
            beReplacedText.AbandonedPhasePercent = allPhaseCarbonEmission != 0 ? (abandonedPhase / allPhaseCarbonEmission).ToString(percentFormat) : "0";

            List<decimal> allPhaseList = new List<decimal>();
            var maxIndex = 0;
            var secondMaxIndex = 0;
            var maxValue = 0m;
            var secondMaxValue = 0m;
            allPhaseList.Add(materialProductionAndTransportPhase);
            allPhaseList.Add(productionPhase);
            allPhaseList.Add(devliveryPhase);
            allPhaseList.Add(usagePhase);
            allPhaseList.Add(abandonedPhase);
            for (int i = 0; i < allPhaseList.Count; i++)
            {
                if (maxValue < allPhaseList[i])
                {
                    maxValue = allPhaseList[i];
                    maxIndex = i;
                }

                if (secondMaxValue < allPhaseList[i] && maxValue > allPhaseList[i])
                {
                    secondMaxValue = allPhaseList[i];
                    secondMaxIndex = i;
                }
            }
            List<string> descriptions = new List<string>()
            {
                "原物料製造及運輸" , "產品生產製造階段","產品配送階段","產品使用階段","廢棄階段"
            };

            beReplacedText.BiggestCarbonEmissionPhase = descriptions[maxIndex];
            beReplacedText.BiggestCarbonEmissionPercent = allPhaseCarbonEmission != 0 ? (allPhaseList[maxIndex] / allPhaseCarbonEmission).ToString(percentFormat) : "0";
            beReplacedText.SecondCarbonEmissionPhase = descriptions[secondMaxIndex];
            beReplacedText.SecondCarbonEmissionPercent = allPhaseCarbonEmission != 0 ? (allPhaseList[secondMaxIndex] / allPhaseCarbonEmission).ToString(percentFormat) : "0";

            var secondDataTypeOfPartsKgCO2e = CarbonEmissionCalculator.GetSecondDataTypeOfPartsKgCO2e(productLca.Materials);
            beReplacedText.SecondDataTypeOfPartsKgCO2e = secondDataTypeOfPartsKgCO2e.ToString(decimalFormat);
            beReplacedText.SecondDataTypeOfPartsPercent = allPhaseCarbonEmission != 0 ? (secondDataTypeOfPartsKgCO2e / allPhaseCarbonEmission).ToString(percentFormat) : "0";

            var primaryDataTypeOfPartsKgCO2e = CarbonEmissionCalculator.GetPrimaryDataTypeOfPartsKgCO2e(productLca.Materials);
            beReplacedText.PrimaryDataTypeOfPartsKgCO2e = primaryDataTypeOfPartsKgCO2e.ToString(decimalFormat);
            beReplacedText.PrimaryDataTypeOfPartsPercent = allPhaseCarbonEmission != 0 ? (primaryDataTypeOfPartsKgCO2e / allPhaseCarbonEmission).ToString(percentFormat) : "0";

            var materialScopeOneDistributeKgCO2e = CarbonEmissionCalculator.GetMaterialScopeOneDistributeKgCO2e(productLca.Materials);
            beReplacedText.MaterialScopeOneDistributeKgCO2e = materialScopeOneDistributeKgCO2e.ToString(decimalFormat);

            var materialScopeTwoDistributeKgCO2e = CarbonEmissionCalculator.GetMaterialScopeTwoDistributeKgCO2e(productLca.Materials);
            beReplacedText.MaterialScopeTwoDistributeKgCO2e = materialScopeTwoDistributeKgCO2e.ToString(decimalFormat);

            var materialScopeThreeDistributeKgCO2e = CarbonEmissionCalculator.GetMaterialScopeThreeDistributeKgCO2e(productLca.Materials);
            beReplacedText.MaterialScopeThreeDistributeKgCO2e = materialScopeThreeDistributeKgCO2e.ToString(decimalFormat);

            var snapShotSurveryResult = productLca.SnapShotSurveyResult;
            var scopeOneFabResult = "";
            var scopeTwoFabResult = "";
            var scopeThreeFabResult = "";

            if (snapShotSurveryResult != null)
            {
                scopeOneFabResult = snapShotSurveryResult.Scope1FabResult.ToString(decimalFormat);
                scopeTwoFabResult = snapShotSurveryResult.Scope2FabResult.ToString(decimalFormat);
                scopeThreeFabResult = snapShotSurveryResult.Scope3FabResult.ToString(decimalFormat);
            }

            beReplacedText.ProductLcaScopeOne = scopeOneFabResult;
            beReplacedText.ProductLcaScopeTwo = scopeTwoFabResult;
            beReplacedText.ProductLcaScopeThree = scopeThreeFabResult;

            var wasteTransportDistributeByHour = CarbonEmissionCalculator.GetWasteTransportDistributeByHour(productLca);
            beReplacedText.ProductLcaWasteTransportDistributeByHour = wasteTransportDistributeByHour.ToString(decimalFormat);

            // prepare yearly paramter
            var vehicleParameters = productLca.VehicleSheet.Select(x => x.Parameter).Distinct();
            var vehicleTable = PrepareTable1Data(vehicleParameters);
            beReplacedText.Table1Data.AddRange(vehicleTable);

            var gasolineParameters = productLca.GasolineEquipmentSheet.Select(x => x.Parameter).Distinct();
            var gasolineTable = PrepareTable1Data(gasolineParameters);
            beReplacedText.Table1Data.AddRange(gasolineTable);

            var kitchenParameters = productLca.KitchenSheet.Select(x => x.Parameter).Distinct();
            var kitchenTable = PrepareTable1Data(kitchenParameters);
            beReplacedText.Table1Data.AddRange(kitchenTable);

            var powerParameters = productLca.PowerUsageSheet.Select(x => x.Parameter).Distinct();
            var powerTable = PrepareTable1Data(powerParameters);
            beReplacedText.Table1Data.AddRange(powerTable);

            var steamParameters = productLca.SteamUsageSheet.Select(x => x.Parameter).Distinct();
            var steamTable = PrepareTable1Data(steamParameters);
            beReplacedText.Table1Data.AddRange(steamTable);

            var waterParameters = productLca.WaterUsageSheet.Select(x => x.Parameter).Distinct();
            var waterTable = PrepareTable1Data(waterParameters);
            beReplacedText.Table1Data.AddRange(waterTable);

            var refrigerantParameterIds = productLca.RefrigerantSheet.Select(x => x.ParameterID2).Distinct();
            var refrigerantParameters = _db.NonYearlyParameters.Where(x => refrigerantParameterIds.Contains(x.ID));
            var refrigerantTable = refrigerantParameters.Where(x => x != null)
                                                        .ToList()
                                                        .Select(x => new Weee.DataTransferObject.BeReplacedTextDTO.TableParameterRow()
                                                        {
                                                            Display = x.Type.DisplayName,
                                                            Value = x.Value,
                                                            Description = x.Description
                                                        });
            beReplacedText.Table2Data.AddRange(refrigerantTable);

            var repliedMaterials = productLca.Materials.Where(x => x.RequestSent != null).Select(x => x);
            var distributeTable = repliedMaterials.Select(x => new Weee.DataTransferObject.BeReplacedTextDTO.TableDistributeRow()
                                  {
                                      MaterialName = x.Name,
                                      SupplierName = x.SupplierCompanyName,
                                      DistributionDescription = x.RequestSent.DivideBy.DisplayString()
                                  });

            beReplacedText.Table3And5Data.AddRange(distributeTable);

            return beReplacedText;
        }
        private decimal EnergyConsumption(InventoryStageData inventoryStageData)
        {
            decimal workDayHightOperate = inventoryStageData.WattInOperation * inventoryStageData.TurnOnHourPerDayInWorkDay / 24;
            decimal workDayLowOperate = inventoryStageData.WattInLowOperation * (24 - inventoryStageData.TurnOnHourPerDayInWorkDay) / 24;
            decimal offDayHightOperate = inventoryStageData.WattInOperation * inventoryStageData.TurnOnHourPerDayInOffDay / 24;
            decimal offDayLowOperatie = inventoryStageData.WattInLowOperation * (24 - inventoryStageData.TurnOnHourPerDayInOffDay) / 24;
            decimal hourOfWorkDayPerYear = inventoryStageData.WorkDayPerYear * 24;
            decimal hourOfOffDayPerYear = (365 - inventoryStageData.WorkDayPerYear) * 24;

            decimal energyConsumption = ((workDayHightOperate + workDayLowOperate) * hourOfWorkDayPerYear +
                                         (offDayHightOperate + offDayLowOperatie) * hourOfOffDayPerYear) /
                                        1000;

            return energyConsumption;
        }
        private IEnumerable<Weee.DataTransferObject.BeReplacedTextDTO.TableParameterRow> PrepareTable1Data(IEnumerable<YearlyParameter> parameters, string display = "")
        {
            return parameters.Where(x => x != null)
                             .Select(x => new Weee.DataTransferObject.BeReplacedTextDTO.TableParameterRow()
                             {
                                 Display = display != "" ? display : x.Type.DisplayName,
                                 Value = x.Value,
                                 Description = x.Description
                             });
        }
        #endregion

        #region Excel Import Check & Save
        public List<string> ExcelImportCheckAndSave(int LCAID, excelExportOrgLCAdataVM excelVMobj, out ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            var lca = GetLCAs().Where(x => x.ID == LCAID).FirstOrDefault();
            importLCA = new ImportOrganizationLCA();
            // 燃料
            err.AddRange(ExcelImportCheck_Fuel(LCAID, excelVMobj._orgLCAdata2fuels, lca, ref importLCA));
            // 總工時
            err.AddRange(ExcelImportCheck_WorkHr(LCAID, excelVMobj._orgLCAdata6workHr, lca, ref importLCA));
            // 冷媒設備
            err.AddRange(ExcelImportCheck_Refrigerant(LCAID, excelVMobj._orgLCAdata7rfg, lca, ref importLCA));
            // 消防設備
            err.AddRange(ExcelImportCheck_FireEquipment(LCAID, excelVMobj._orgLCAdata8fire, lca, ref importLCA));
            // 其他類關注物質
            err.AddRange(ExcelImportCheck_OtherCompound(LCAID, excelVMobj._orgLCAdata9others, lca, ref importLCA));
            // 用電量
            err.AddRange(ExcelImportCheck_Power(LCAID, excelVMobj._orgLCAdata10elec, lca, ref importLCA));
            // 用蒸氣量
            err.AddRange(ExcelImportCheck_Steam(LCAID, excelVMobj._orgLCAdata11steamVM, lca, ref importLCA));
            return err;
        }

        public List<string> ExcelImportCheck_Fuel(int LCAID, orgLCAdata2fuelsVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 消防設備
            if (modelVM.fuels_rows.Count > 0)
            {
                var vehicle = new List<LCACommonSurveyForm_Vehicles>();
                var kitchen = new List<LCACommonSurveyForm_Kitchens>();
                var gasolineEquipment = new List<LCACommonSurveyForm_GasolineEquipments>();
                var config1 = new MapperConfiguration(cfg => {
                    cfg.CreateMap<LCACommonSurveyForm_Vehicles, LCACommonSurveyForm_Kitchens>();
                });
                IMapper mapper1 = config1.CreateMapper(); 
                var config2 = new MapperConfiguration(cfg => {
                    cfg.CreateMap<LCACommonSurveyForm_Vehicles, LCACommonSurveyForm_GasolineEquipments>();
                });
                IMapper mapper2 = config2.CreateMapper();
                if (string.IsNullOrWhiteSpace(modelVM.commonFields.LCAyear.ToString()))
                {
                    err.Add("'油、氣燃料'年份Year不得為空");
                    return err;
                }
                if (string.IsNullOrWhiteSpace(modelVM.area))
                {
                    err.Add("'油、氣燃料'區域Area不得為空");
                    return err;
                }
                var fuelParameter = (from a in _db.YearlyParameters
                                      join b in _db.YearlyParameterAreas on a.AreaID equals b.ID
                                      join c in _db.YearlyParameterCategories on b.CategoryID equals c.ID
                                      join d in _db.YearlyParameterTypes on a.TypeID equals d.ID
                                      where (c.Category == Categories.GasolineEquipment || c.Category == Categories.Kitchen || c.Category == Categories.Vehicle) 
                                            && a.Year.ToString() == modelVM.commonFields.LCAyear.ToString()
                                            && modelVM.area.Contains(b.DisplayNameTW) 
                                      select new { 
                                        Parameter = a,
                                        c.Category,
                                        d.DisplayNameTW,
                                        d.warmGasType
                                      }).AsNoTracking().ToList();
                if (fuelParameter == null)
                {
                    err.Add("'油、氣燃料'查無任何 AR version(s) and GWP 係數");
                    return err;
                }
                for (var i = 0; i < modelVM.fuels_rows.Count; i++)
                {
                    var category = new Categories();
                    var add = new LCACommonSurveyForm_Vehicles();

                    add.LCAID = LCAID;
                    add.ProcessName = modelVM.fuels_rows[i].ProcessName;
                    add.ResponsibleUnit = modelVM.fuels_rows[i].ResponsibleUnit;
                    add.Name = modelVM.fuels_rows[i].Name;
                    add.Scalar = modelVM.fuels_rows[i].Scalar;
                    add.Description = modelVM.fuels_rows[i].Description;
                    add.Evidence = modelVM.fuels_rows[i].Evidence;
                    var errStr = "";
                    if (string.IsNullOrWhiteSpace(add.Name))
                        errStr += "設備名稱不得為空。";
                    if (add.Scalar < 0)
                        errStr += "盤查期間使用量需大於等於0。";
                    add.KgCO2e = add.Scalar;
                    if (!fuelParameter.Any(x => x.DisplayNameTW.Contains(modelVM.fuels_rows[i].paraName) && x.warmGasType == modelVM.fuels_rows[i].warmGasType ))
                        errStr += "該燃料不存在。";
                    else
                    {
                        var parameters = fuelParameter.Where(x => x.DisplayNameTW.Contains(modelVM.fuels_rows[i].paraName)).ToList();
                        if(parameters.Count > 1)
                        {
                            // 移動源預設給車輛設備
                            if (modelVM.fuels_rows[i].warmGasType == WarmGasType.移動源)
                                parameters = parameters.OrderBy(x => x.Category != Categories.Vehicle).ToList();
                            // 非移動源柴油預設給柴油設備
                            else if (modelVM.fuels_rows[i].paraName.Contains("柴油"))
                                parameters = parameters.OrderBy(x => x.Category != Categories.GasolineEquipment).ToList();
                            // 其餘皆預設給油氣燃料
                            else
                                parameters = parameters.OrderBy(x => x.Category != Categories.Kitchen).ToList();
                        }
                        add.ParameterID = parameters[0].Parameter.ID;
                        add.KgCO2e *= parameters[0].Parameter.Value;
                        category = parameters[0].Category;
                    }
                    
                    if (!string.IsNullOrWhiteSpace(errStr))
                        err.Add("'油、氣燃料'當中第" + (i + 1) + "筆資料有誤：" + errStr);
                    if (string.IsNullOrWhiteSpace(errStr))
                    {
                        if (category == Categories.Vehicle)
                            vehicle.Add(add);
                        else if (category == Categories.Kitchen)
                            kitchen.Add(mapper1.Map<LCACommonSurveyForm_Vehicles, LCACommonSurveyForm_Kitchens>(add));
                        else if (category == Categories.GasolineEquipment)
                            gasolineEquipment.Add(mapper2.Map<LCACommonSurveyForm_Vehicles, LCACommonSurveyForm_GasolineEquipments>(add));
                    }
                }
                importLCA.VehicleSheet = vehicle;
                importLCA.KitchenSheet = kitchen;
                importLCA.GasolineEquipmentSheet = gasolineEquipment;
            }
            return err;
        }

        public List<string> ExcelImportCheck_WorkHr(int LCAID, orgLCAdata6workHrVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            if (!modelVM.workHourTypeDirect.Any(x => x.Scalar > 0) && !modelVM.workHourTypeIndirect.Any(x => x.Scalar > 0))
                return err;
            if (string.IsNullOrWhiteSpace(modelVM.commonFields.LCAyear.ToString()))
            {
                err.Add("'糞肥管理-邊界範圍總工時'年份Year不得為空");
                return err;
            }
            var spvalue = _db.NonYearlyParameters
                   .Where(y => y.Type.Category.Category == Weee.Models.Paramemter.Categories.Septic)
                   .Where(y => !y.IsHistory)
                   .Where(y => y.Type.DisplayNameTW != "GWP")
                   .AsNoTracking()
                   .Select(y => y.Value)
                   .AsEnumerable()
                   .Multiply();
            var GWP_CH4 = (from a in _db.AssessmentReportGWP
                           where a.chemicalFormula == "CH4"
                           select a).FirstOrDefault();
            if (GWP_CH4 != null && lca.ARversion != null)
            {
                if (lca.ARversion == "AR4")
                    spvalue *= GWP_CH4.AR4GWP100;
                else if (lca.ARversion == "AR5")
                    spvalue *= GWP_CH4.AR5GWP100;
                else // if (lca.ARversion == "AR6") // 預設AR6
                    spvalue *= GWP_CH4.AR6GWP100;
            }
            var finalWorkHrList = new List<LCACommonSurveyForm_WorkHours>();
            var workHr = modelVM.workHourList;
            for (int j = 0; j < workHr.Count; j++)
            {
                var oneWorkHrList = workHr[j];
                for (var i = 0; i < 12; i++)
                {
                    finalWorkHrList.Add(new LCACommonSurveyForm_WorkHours());
                    finalWorkHrList[i + j * 12].Index = i + 1;
                    finalWorkHrList[i + j * 12].LCAID = LCAID;
                    finalWorkHrList[i + j * 12].WorkerNumber = oneWorkHrList[i].WorkerNumber;
                    finalWorkHrList[i + j * 12].AverageHourPerDay = oneWorkHrList[i].AverageHourPerDay;
                    finalWorkHrList[i + j * 12].WorkDay = oneWorkHrList[i].WorkDay;
                    finalWorkHrList[i + j * 12].Type = oneWorkHrList[i].Type;
                    finalWorkHrList[i + j * 12].WorkHourPeopleType = oneWorkHrList[i].WorkHourPeopleType;
                    finalWorkHrList[i + j * 12].Scalar = finalWorkHrList[i].WorkerNumber * finalWorkHrList[i].AverageHourPerDay * finalWorkHrList[i].WorkDay;
                    finalWorkHrList[i + j * 12].KgCO2e = finalWorkHrList[i].Scalar * spvalue;
                }

            }
            importLCA.WorkHourSheet = finalWorkHrList;
            /*
            // 總工時 - 直接人員
            var workHrDirect = new List<LCACommonSurveyForm_WorkHours>();
            if (modelVM.workHourTypeDirect.Count == 12)
            {
                if (workHrDirect.Count < 12)
                    for (var i = workHrDirect.Count; i < 12; i++)
                        workHrDirect.Add(new LCACommonSurveyForm_WorkHours());
                for (var i = 0; i < 12; i++)
                {
                    workHrDirect[i].Index = i + 1;
                    workHrDirect[i].LCAID = LCAID;
                    workHrDirect[i].WorkerNumber = modelVM.workHourTypeDirect[i].WorkerNumber;
                    workHrDirect[i].AverageHourPerDay = modelVM.workHourTypeDirect[i].AverageHourPerDay;
                    workHrDirect[i].WorkDay = modelVM.workHourTypeDirect[i].WorkDay;
                    workHrDirect[i].Type = LCACommonSurveyForm_WorkHours.WorkHourType.Direct;
                    workHrDirect[i].Scalar = modelVM.workHourTypeDirect[i].Scalar;
                    workHrDirect[i].KgCO2e = workHrDirect[i].Scalar * spvalue;
                }
            }

            // 總工時 - 間接人員
            var workHrIndirect = new List<LCACommonSurveyForm_WorkHours>();
            if (modelVM.workHourTypeIndirect.Count == 12)
            {
                if (workHrIndirect.Count < 12)
                    for (var i = workHrIndirect.Count; i < 12; i++)
                        workHrIndirect.Add(new LCACommonSurveyForm_WorkHours());
                for (var i = 0; i < 12; i++)
                {
                    workHrIndirect[i].Index = i + 1;
                    workHrIndirect[i].LCAID = LCAID;
                    workHrIndirect[i].WorkerNumber = modelVM.workHourTypeIndirect[i].WorkerNumber;
                    workHrIndirect[i].AverageHourPerDay = modelVM.workHourTypeIndirect[i].AverageHourPerDay;
                    workHrIndirect[i].WorkDay = modelVM.workHourTypeIndirect[i].WorkDay;
                    workHrIndirect[i].Type = LCACommonSurveyForm_WorkHours.WorkHourType.Indirect;
                    workHrIndirect[i].Scalar = modelVM.workHourTypeIndirect[i].Scalar;
                    workHrIndirect[i].KgCO2e = workHrIndirect[i].Scalar * spvalue;
                }
            }

            workHrDirect.AddRange(workHrIndirect);
            importLCA.WorkHourSheet = workHrDirect;*/
            return err;
        }

        public List<string> ExcelImportCheck_Refrigerant(int LCAID, orgLCAdata7rfgVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 冷媒設備
            if (modelVM.rfg_rows.Count > 0)
            {
                var refrigerantUsage = new List<LCACommonSurveyForm_Refrigerants>();
                var refrigerantParameter1 = (from a in _db.AssessmentReportGWP
                                             where a.GWPtype == GWPparameterType.Refrigerant
                                             select a).AsNoTracking().ToList();
                var refrigerantParameter2 = (from a in _db.NonYearlyParameters
                                            join b in _db.NonYearlyParameterTypes on a.TypeID equals b.ID
                                            join c in _db.NonYearlyParameterCategories on b.CategoryID equals c.ID
                                            where c.Category == Categories.Refrigerant
                                            select new{ 
                                                Parameter = a,
                                                typeName = b.DisplayNameTW
                                            }).AsNoTracking().ToList();

                if (refrigerantParameter1 == null)
                {
                    err.Add("'含冷媒/製冷劑之設備'查無任何 AR version(s) and GWP 係數");
                    return err;
                }
                if (refrigerantParameter2 == null)
                {
                    err.Add("'含冷媒/製冷劑之設備'查無任何係數");
                    return err;
                }
                for (var i = 0; i < modelVM.rfg_rows.Count; i++) 
                {
                    var add = new LCACommonSurveyForm_Refrigerants();
                    add.LCAID = LCAID;
                    add.ProcessName = modelVM.rfg_rows[i].ProcessName;
                    add.ResponsibleUnit = modelVM.rfg_rows[i].ResponsibleUnit;
                    add.Name = modelVM.rfg_rows[i].Name;
                    add.ModelNumber = modelVM.rfg_rows[i].ModelNumber;
                    add.TotalNumber = modelVM.rfg_rows[i].TotalNumber;
                    add.Scalar = modelVM.rfg_rows[i].Scalar;
                    add.UsedMonth = modelVM.rfg_rows[i].UsedMonth;
                    add.Description = modelVM.rfg_rows[i].Description;
                    var errStr = "";
                    if (add.TotalNumber < 0)
                        errStr += "全廠台數需大於等於0。";
                    if (add.Scalar < 0)
                        errStr += "單台冷煤原始填充量需大於等於0。";
                    if (add.UsedMonth > 12 || add.UsedMonth < 0)
                        errStr += "使用月份須0~12個月。";
                    add.KgCO2e = add.Scalar * add.TotalNumber * add.UsedMonth / 12;
                    if (!refrigerantParameter1.Any(x => x.ingredientName == modelVM.rfg_rows[i].paraName_refrigerant 
                            || (!string.IsNullOrWhiteSpace(x.Alias) && x.Alias == modelVM.rfg_rows[i].paraName_refrigerant)))
                        errStr += $"該使用冷煤種類{modelVM.rfg_rows[i].paraName_refrigerant}不存在。";
                    else
                    {
                        var parameter1 = refrigerantParameter1.Find(x => x.ingredientName == modelVM.rfg_rows[i].paraName_refrigerant || (!string.IsNullOrWhiteSpace(x.Alias) && x.Alias == modelVM.rfg_rows[i].paraName_refrigerant));
                        add.ARnGWPid = parameter1.ID;
                        add.KgCO2e *= getGWPValueByARVersion(parameter1, lca.ARversion);
                    }
                    if (!refrigerantParameter2.Any(x => modelVM.rfg_rows[i].para2Name_equipment.Contains(x.typeName)))
                        errStr += $"該設備類型(排放因子){modelVM.rfg_rows[i].para2Name_equipment}不存在。";
                    else
                    {
                        var parameter2 = refrigerantParameter2.Find(x => modelVM.rfg_rows[i].para2Name_equipment.Contains(x.typeName));
                        add.ParameterID2 = parameter2.Parameter.ID;
                        add.KgCO2e *= parameter2.Parameter.Value;
                    }

                    if (!string.IsNullOrWhiteSpace(errStr))
                        err.Add("'含冷媒/製冷劑之設備'當中第" + (i + 1) + "筆資料有誤：" + errStr);
                    if (string.IsNullOrWhiteSpace(errStr))
                        refrigerantUsage.Add(add);
                }
                importLCA.RefrigerantSheet = refrigerantUsage;
            }
            return err;
        }

        public List<string> ExcelImportCheck_FireEquipment(int LCAID, orgLCAdata8fireVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 消防設備
            if (modelVM.fire_rows.Count > 0)
            {
                var fireEquipment = new List<LCACommonSurveyForm_FireEquipments>();
                var fireEquipmentParameter = (from a in _db.AssessmentReportGWP
                                             where a.GWPtype == GWPparameterType.FireEquipment
                                             select a).AsNoTracking().ToList();
                if (fireEquipmentParameter == null)
                {
                    err.Add("'消防設備'查無任何 AR version(s) and GWP 係數");
                    return err;
                }
                for (var i = 0; i < modelVM.fire_rows.Count; i++)
                {
                    var add = new LCACommonSurveyForm_FireEquipments();
                    add.LCAID = LCAID;
                    add.ProcessName = modelVM.fire_rows[i].ProcessName;
                    add.ResponsibleUnit = modelVM.fire_rows[i].ResponsibleUnit;
                    add.Name = modelVM.fire_rows[i].Name;
                    add.Quantity = modelVM.fire_rows[i].Quantity;
                    add.Scalar = modelVM.fire_rows[i].Scalar;
                    add.Description = modelVM.fire_rows[i].Description;
                    var errStr = "";
                    if (add.Quantity < 0)
                        errStr += "數量需大於等於0。";
                    if (add.Scalar < 0)
                        errStr += "使用/充填量(kg)需大於等於0。";
                    add.KgCO2e = add.Scalar * add.Quantity;
                    if (!fireEquipmentParameter.Any(x => x.ingredientName == modelVM.fire_rows[i].paraName))
                        errStr += "該排放源不存在。";
                    else
                    {
                        var parameter = fireEquipmentParameter.Find(x => x.ingredientName == modelVM.fire_rows[i].paraName);
                        add.ARnGWPid = parameter.ID;
                        add.KgCO2e *= getGWPValueByARVersion(parameter, lca.ARversion);
                    }

                    if (!string.IsNullOrWhiteSpace(errStr))
                        err.Add("'消防設備'當中第" + (i + 1) + "筆資料有誤：" + errStr);
                    if (string.IsNullOrWhiteSpace(errStr))
                        fireEquipment.Add(add);
                }
                importLCA.FireEquipmentSheet = fireEquipment;
            }
            return err;
        }

        public List<string> ExcelImportCheck_OtherCompound(int LCAID, orgLCAdata9othersVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 其他類關注物質
            if (modelVM.others_rows.Count > 0)
            {
                var otherCompound = new List<OrganizationLCAFabSurveyForm_OtherCompounds>();
                for (var i = 0; i < modelVM.others_rows.Count; i++)
                {
                    var add = new OrganizationLCAFabSurveyForm_OtherCompounds();
                    add.LCAID = LCAID;
                    add.ProcessName = modelVM.others_rows[i].ProcessName;
                    add.ResponsibleUnit = modelVM.others_rows[i].ResponsibleUnit;
                    add.Name = modelVM.others_rows[i].Name;
                    add.Scalar = modelVM.others_rows[i].Scalar;
                    add.Description = modelVM.others_rows[i].Description;
                    add.ingredientName = modelVM.others_rows[i].paraName;
                    add.CO2Factor = modelVM.others_rows[i].CO2Factor;
                    add.CO2Value = modelVM.others_rows[i].CO2Value;
                    add.KgCO2e = add.Scalar * (decimal)add.CO2Factor;
                    var errStr = "";
                    if (string.IsNullOrWhiteSpace(add.ingredientName))
                        errStr += "排放源名稱不得為空。";
                    if (add.Scalar < 0)
                        errStr += "數量需大於等於0。";
                    if (!string.IsNullOrWhiteSpace(errStr))
                        err.Add("'其他類關注物質'當中第" + (i + 1) + "筆資料有誤：" + errStr);
                    if (string.IsNullOrWhiteSpace(errStr))
                        otherCompound.Add(add);
                }
                importLCA.OtherCompound = otherCompound;
            }
            return err;
        }

        public List<string> ExcelImportCheck_Power(int LCAID, orgLCAdata10elecVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 用電量
            if (modelVM.elecVMcolumns.Count == 12)
            {
                if (!modelVM.elecVMcolumns.Any(x => x.Scalar > 0))
                    return err;
                if (string.IsNullOrWhiteSpace(modelVM.commonFields.LCAyear.ToString()))
                {
                    err.Add("'用電量'年份Year不得為空");
                    return err;
                }
                if (string.IsNullOrWhiteSpace(modelVM.area))
                {
                    err.Add("'用電量'區域Area不得為空");
                    return err;
                }
                var powerUsage = new List<LCACommonSurveyForm_PowerUsages>();
                if (powerUsage.Count < 12)
                    for (var i = powerUsage.Count; i < 12; i++)
                        powerUsage.Add(new LCACommonSurveyForm_PowerUsages());
                var powerParameter = (from a in _db.YearlyParameters
                                      join b in _db.YearlyParameterAreas on a.AreaID equals b.ID
                                      join c in _db.YearlyParameterCategories on b.CategoryID equals c.ID
                                      join d in _db.YearlyParameterTypes on a.TypeID equals d.ID
                                      where c.Category == Categories.Electric && a.Year.ToString() == modelVM.commonFields.LCAyear.ToString()
                                            && modelVM.area.Contains(b.DisplayNameTW) && d.DisplayNameTW != CScommon.ProgramConstants.POWER_C4_COEFFNAME
                                      select a).AsNoTracking().FirstOrDefault();
                if (powerParameter == null)
                {
                    err.Add("用電量查無'" + modelVM.area + "-" + modelVM.commonFields.LCAyear.ToString() + "'該係數");
                    return err;
                }
                for (var i = 0; i < 12; i++)
                {
                    powerUsage[i].Index = i + 1;
                    powerUsage[i].LCAID = LCAID;
                    powerUsage[i].Area = modelVM.area;
                    powerUsage[i].Year = modelVM.commonFields.LCAyear.ToString();
                    powerUsage[i].Peak = modelVM.elecVMcolumns[i].Peak;
                    powerUsage[i].HalfPeak = modelVM.elecVMcolumns[i].HalfPeak;
                    powerUsage[i].OffPeak = modelVM.elecVMcolumns[i].OffPeak;
                    powerUsage[i].SaturdayHalfPeak = modelVM.elecVMcolumns[i].SaturdayHalfPeak;
                    powerUsage[i].Scalar = modelVM.elecVMcolumns[i].Scalar;
                    powerUsage[i].ParameterID = powerParameter.ID;
                    powerUsage[i].KgCO2e = powerUsage[i].Scalar * powerParameter.Value;
                }
                importLCA.PowerUsageSheet = powerUsage;
            }
            return err;
        }

        public List<string> ExcelImportCheck_Steam(int LCAID, orgLCAdata11steamVM modelVM, LCA lca, ref ImportOrganizationLCA importLCA)
        {
            var err = new List<string>();
            // 用蒸氣量
            if (modelVM.steamVMcolumns.Count == 12)
            {
                if (!modelVM.steamVMcolumns.Any(x => x.Scalar > 0))
                    return err;
                if (string.IsNullOrWhiteSpace(modelVM.commonFields.LCAyear.ToString()))
                {
                    err.Add("'用蒸氣量'年份Year不得為空");
                    return err;
                }
                if (string.IsNullOrWhiteSpace(modelVM.area))
                {
                    err.Add("'用蒸氣量'區域Area不得為空");
                    return err;
                }
                var steamUsage = new List<LCACommonSurveyForm_SteamUsages>();
                if (steamUsage.Count < 12)
                    for (var i = steamUsage.Count; i < 12; i++)
                        steamUsage.Add(new LCACommonSurveyForm_SteamUsages());
                var steamParameter = (from a in _db.YearlyParameters
                                      join b in _db.YearlyParameterAreas on a.AreaID equals b.ID
                                      join c in _db.YearlyParameterCategories on b.CategoryID equals c.ID
                                      join d in _db.YearlyParameterTypes on a.TypeID equals d.ID
                                      where c.Category == Categories.Steam && a.Year.ToString() == modelVM.commonFields.LCAyear.ToString()
                                            && modelVM.area.Contains(b.DisplayNameTW)
                                      select a).AsNoTracking().FirstOrDefault();
                if (steamParameter == null)
                {
                    err.Add("'用蒸氣量'查無'" + modelVM.area + "-" + modelVM.commonFields.LCAyear.ToString() + "'該係數");
                    return err;
                }
                for (var i = 0; i < 12; i++)
                {
                    steamUsage[i].Index = i + 1;
                    steamUsage[i].LCAID = LCAID;
                    steamUsage[i].Area = modelVM.area;
                    steamUsage[i].Year = modelVM.commonFields.LCAyear.ToString();
                    steamUsage[i].Scalar = modelVM.steamVMcolumns[i].Scalar;
                    steamUsage[i].ParameterID = steamParameter.ID;
                    steamUsage[i].KgCO2e = steamUsage[i].Scalar * steamParameter.Value;
                }
                importLCA.SteamUsageSheet = steamUsage;
            }
            return err;
        }
        #endregion

        private decimal getGWPValueByARVersion(AssessmentReportGWP gwp, string version)
        {
            if (version == "AR4")
                return gwp.AR4GWP100;
            else if (version == "AR5")
                return gwp.AR5GWP100;
            else
                return gwp.AR6GWP100;
        }

        public string CompanyNameByLCAID(int LCAID)
        {
            string ret = "";
            var qry = (from a in _db.Companies
                       join b in _db.LCAs on a.ID equals b.OwnerID
                       where b.ID == LCAID
                       select a.Name).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;
        }
        public string ProductNameByLCAID(int LCAID)
        {
            string ret = "";
            var qry = (from a in _db.Products
                       join b in _db.ProductLCAs on a.ID equals b.ProductID
                       where b.ID == LCAID
                       select a.Name).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;
        }

        public Dictionary<string,decimal> GetGWPValueByLCAID(int LCAID)
        {
            // 取得CH4 N2O GWP在不同AR version的數值
            LCAcommon lcaCommonRec = _LCAcommonService.generateReportData(LCAID);
            var GWPValue = new Dictionary<string, decimal>();
            var qry4 = (from a in _db.AssessmentReportGWP
                        where a.chemicalFormula == "CH4" || a.chemicalFormula == "N2O"
                        select a);
            var GWP_CH4 = qry4.FirstOrDefault(x => x.chemicalFormula == "CH4");
            var GWP_N2O = qry4.FirstOrDefault(x => x.chemicalFormula == "N2O");
            GWPValue.Add("CO2", CScommon.ProgramConstants.GWPco2);
            if (lcaCommonRec.ARversion == "AR4")
            {
                GWPValue.Add("CH4", GWP_CH4 == null ? CScommon.ProgramConstants.GWPch4 : GWP_CH4.AR4GWP100);
                GWPValue.Add("N2O", GWP_CH4 == null ? CScommon.ProgramConstants.GWPn2o : GWP_N2O.AR4GWP100);
            }
            else if (lcaCommonRec.ARversion == "AR5")
            {
                GWPValue.Add("CH4", GWP_CH4 == null ? CScommon.ProgramConstants.GWPch4 : GWP_CH4.AR5GWP100);
                GWPValue.Add("N2O", GWP_CH4 == null ? CScommon.ProgramConstants.GWPn2o : GWP_N2O.AR5GWP100);
            }
            else if (lcaCommonRec.ARversion == "AR6")
            {
                GWPValue.Add("CH4", GWP_CH4 == null ? CScommon.ProgramConstants.GWPch4 : GWP_CH4.AR6GWP100);
                GWPValue.Add("N2O", GWP_CH4 == null ? CScommon.ProgramConstants.GWPn2o : GWP_N2O.AR6GWP100);
            }
            else
            {
                GWPValue.Add("CH4", CScommon.ProgramConstants.GWPch4);
                GWPValue.Add("N2O", CScommon.ProgramConstants.GWPn2o);
            }
            return GWPValue;
        }
    }
}