using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using Weee.DAL;
using Weee.Models;
using Weee.Models.Paramemter;
using Weee.ViewModels.DataTransferObject;

namespace Weee.Service
{
    public class LifecycleAssmtDataService : WeeeLCADataService
    {
        public LifecycleAssmtDataService(WeeeDataContext db)
        : base(db)
        {
        }

        /// <summary>
        /// 將用電量同步到風險評估的C2,C4生命週期項目
        /// </summary>
        /// <param name="Scalar"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public string power2lifeCycle(string userID, int LCAID, decimal Scalar, string curArea
            , int curYear, Weee.Models.Paramemter.Parameter para)
        {
            string ret = "";
            //同步到風險評估的C2電力項目
            var qry2 = (from a in _db.LCARiskAssmtSurveyForm_LifecycleAssmts
                        where a.LCAID == LCAID && a.LifeCycleStage == 3
                            && a.category == "2" && a.GHGinventoryItemNo == "2.1"
                        select a);
            if (qry2.Any())//有資料則修改
            {
                foreach (var rec in qry2.ToList())
                {
                    rec.ActivityIntensity = Scalar;
                    rec.KgCO2e = para.CO2Value > 0 ? para.CO2Value : para.Value;
                    rec.EmissionKg = rec.ActivityIntensity * rec.KgCO2e;
                    rec.EmissionT = rec.EmissionKg / 1000;
                }
            }
            else//無資料則新增
            {
                LCARiskAssmtSurveyForm_LifecycleAssmt rec = new LCARiskAssmtSurveyForm_LifecycleAssmt();
                rec.LCAID = LCAID;
                rec.Unit = "用電度";//from para
                rec.CreatedBy = userID;
                rec.CreatedDate = DateTime.Now;
                rec.LifeCycleStage = 3;
                //rec.Instruction = ""; from para
                rec.GHGEvaluateItem = "輸入電力";
                rec.category = "2";
                rec.categorySubItem = "輸入電力";
                rec.GHGinventoryItemNo = "2.1";
                rec.ActivityIntensity = Scalar;
                rec.KgCO2e = para.CO2Value > 0 ? para.CO2Value : para.Value;
                rec.EmissionKg = rec.ActivityIntensity * rec.KgCO2e;
                rec.EmissionT = rec.EmissionKg / 1000;
                _db.LCARiskAssmtSurveyForm_LifecycleAssmts.Add(rec);
            }
            //同步到風險評估的C4電力上游採購
            Weee.Models.Paramemter.Parameter paraC4 = getC4coeff(curArea, curYear);
            var qry4 = (from a in _db.LCARiskAssmtSurveyForm_LifecycleAssmts
                        where a.LCAID == LCAID && a.LifeCycleStage == 3
                            && a.category == "4" && a.GHGinventoryItemNo == "4.1b"
                        select a);
            if (qry4.Any())//有資料則修改
            {
                foreach (var rec in qry4.ToList())
                {
                    rec.ActivityIntensity = Scalar;
                    rec.KgCO2e = paraC4==null ? 0 : paraC4.CO2Value;
                    rec.EmissionKg = rec.ActivityIntensity * rec.KgCO2e;
                    rec.EmissionT = rec.EmissionKg / 1000;
                }
            }
            else//無資料則新增
            {
                LCARiskAssmtSurveyForm_LifecycleAssmt rec = new LCARiskAssmtSurveyForm_LifecycleAssmt();
                rec.LCAID = LCAID;
                rec.Unit = "用電度";//from paraC4
                rec.CreatedBy = userID;
                rec.CreatedDate= DateTime.Now;
                rec.LifeCycleStage = 3;
                //rec.Instruction = ""; from paraC4
                rec.GHGEvaluateItem = "輸入電力上游";
                rec.category = "4";
                rec.categorySubItem = "輸入電力上游";
                rec.GHGinventoryItemNo = "4.1b";
                rec.ActivityIntensity = Scalar;
                rec.KgCO2e = paraC4==null ? 0 : paraC4.CO2Value;
                rec.EmissionKg = rec.ActivityIntensity * rec.KgCO2e;
                rec.EmissionT = rec.EmissionKg / 1000;
                _db.LCARiskAssmtSurveyForm_LifecycleAssmts.Add(rec);
            }
            _db.SaveChanges();
            //最後還要更新風險評估資料的逐項百分比
            UpdatePercentage(LCAID);
            _db.SaveChanges();
            return ret;
        }
        private Weee.Models.Paramemter.Parameter getC4coeff(string curArea
            , int curYear)
        {
            Weee.Models.Paramemter.Parameter ret = null;
            var qry = (from p in _db.Parameters
                       join yp in _db.YearlyParameters on p.ID equals yp.ID
                       join t in _db.YearlyParameterTypes on yp.TypeID equals t.ID
                       join c in _db.YearlyParameterCategories on t.CategoryID equals c.ID
                       where yp.Year==curYear && yp.IsHistory==false &&
                        c.Category==Categories.Electric && t.DisplayNameTW=="用電量上游排放"
                       select p).FirstOrDefault();
            if (qry!=null)
                ret = qry;
            return ret;
        }
        public IQueryable<LCARiskAssmtSurveyForm_LifecycleAssmt> GetList(int LCAID)
        {
            if (!AuthorizedLCAs.Contains(LCAID)) throw new Exception("not authorized");
            return _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.LCAID == LCAID)
                .OrderByDescending(x => x.ID);
        }

        public IEnumerable<LifecycleAssmtItemDto> GetItems()
        {
            return _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmtItem>()
                .ToList()
                .Select(x => new LifecycleAssmtItemDto() 
                { 
                    ID = x.ID,
                    LifeCycleStage = x.LifeCycleStage,
                    DisplayName = x.DisplayName
                });
        }

        public IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> Sync(int LCAID, UpdateLIfecycleAssmtDTO input)
        {
            // 生命週期階段的大update
            var oldLifecycleAssmtDataData = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.LCAID == LCAID).ToList();
            var newLifecycleAssmtData = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();

            if (input.Material)
                newLifecycleAssmtData.AddRange(
                    UpdateMaterial(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 1)
                        , LCAID, input.MaterialDelete));
            if (input.UpstreamDelivery)
                newLifecycleAssmtData.AddRange(
                    UpdateUpstreamDelivery(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 2)
                        , LCAID, input.UpstreamDeliveryDelete));
            if (input.Service)
                newLifecycleAssmtData.AddRange(
                    UpdateService(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 3)
                        , LCAID, input.ServiceDelete));
            if (input.DownstreamDelivery)
                newLifecycleAssmtData.AddRange(
                    UpdateDownstreamDelivery(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 4)
                        , LCAID, input.DownstreamDeliveryDelete));
            if (input.Product)
                newLifecycleAssmtData.AddRange(
                    UpdateProduct(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 5)
                        , LCAID, input.ProductDelete));
            if (input.Waste)
                newLifecycleAssmtData.AddRange(
                    UpdateWaste(oldLifecycleAssmtDataData.Where(x => x.LifeCycleStage == 6)
                        , LCAID, input.WasteDelete));

            UpdatePercentage(newLifecycleAssmtData);

            _db.SaveChanges();

            return newLifecycleAssmtData;
        }

        private Dictionary<int, string> GetYearlyParameterOptions(Categories cate)
        {

            var ret = _db.YearlyParameters
                .Include(x => x.Type)
                .Include(x => x.Area)
                .Where(x => x.Area.Category.Category == cate && x.IsHistory == false)
                .OrderBy(x => x.AreaID)
                .ThenBy(x => x.Year)
                .ToDictionary(x => x.ID, x => x.Area.DisplayName + "," + x.Type.DisplayName + "," + x.Year);

            return ret;
        }

        private void UpdatePercentage(IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> lifecycleAssmtData)
        {
            var sumEmissionT = lifecycleAssmtData.Sum(x => x.EmissionT);

            foreach (var data in lifecycleAssmtData)
            {
                data.Percentage = sumEmissionT==0 ? 0 : Math.Round((data.EmissionT / sumEmissionT) * 100, 4);
            }
        }
        private void UpdatePercentage(int LCAID)
        {
            var qry = (from a in _db.LCARiskAssmtSurveyForm_LifecycleAssmts
                       where a.LCAID == LCAID
                       select a);
            if (qry.Any())
                UpdatePercentage(qry.ToList());
        }
        /// <summary>
        /// 更新原料取得资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateMaterial(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> materialData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in materialData)
                {
                    if (data.LCAID==LCAID)
                        _db.Entry(data).State = EntityState.Deleted;
                }
            }
            else
            {
                result.AddRange(materialData);
            }
            //if (update)
            {
                var material = _db.Set<LCARiskAssmtSurveyForm_MaterialC3Emission>().Where(x => x.LCAID == LCAID).ToList();
                foreach (var item in material)
                {
                    if (delete || !materialData.Any(x => x.GHGEvaluateItem == item.MaterialName))
                    {
                        var materialAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            MaterialNo = item.MaterialNo,
                            LifeCycleStage = 1,
                            GHGEvaluateItem = item.MaterialName,
                            ActivityIntensity = item.ActivityIntensity,
                            KgCO2e = item.KgCO2e,
                            Unit = item.Unit,
                            EmissionKg = item.ActivityIntensity * item.KgCO2e,
                            EmissionT = item.ActivityIntensity * item.KgCO2e / 1000,
                            Instruction = item.Comment
                        };
                        result.Add(materialAssmt);
                        _db.Entry(materialAssmt).State = EntityState.Added;
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 更新上游运输资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateUpstreamDelivery(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> deliveryData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in deliveryData)
                {
                    _db.Entry(data).State = EntityState.Deleted;
                }
            }
            else
            {
                result.AddRange(deliveryData);
            }
            //if (update)
            {
                var purchase = _db.Set<LCARiskAssmtSurveyForm_ItemPurchase>().Where(x => x.LCAID == LCAID).ToList(); // 採購及運輸地址
                var travel = _db.Set<LCARiskAssmtSurveyForm_IntlTravel>().Where(x => x.LCAID == LCAID).ToList(); // 商務旅行(國際)
                foreach (var item in purchase)
                {
                    if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == item.ElementName))
                    {
                        var materialAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 2,
                            MaterialNo=item.MaterialNo,
                            GHGEvaluateItem = item.ElementName,
                            ActivityIntensity = item.TKM,
                            KgCO2e = 0.235m,
                            Unit = "tkm",
                            EmissionKg = item.TKM * 0.235m,
                            EmissionT = item.TKM * 0.235m / 1000,
                            Instruction= @"產品碳足跡計算服務平台營業用大貨車https://cfp-calculate.tw/cfpc/WebPage/WebSites/CoefficientDB.aspx"
                        };
                        result.Add(materialAssmt);
                        _db.Entry(materialAssmt).State = EntityState.Added;
                    }
                }
                //// 商務旅行(國際)
                //var travelItems = new List<IntlTravel>();
                //foreach (var item in travel)
                //{
                //    if (string.IsNullOrEmpty(item.AirportFrom) || string.IsNullOrEmpty(item.AirportTo))
                //    {
                //        continue;
                //    }
                //    var airportFrom = TryAddTravelItem(item.AirportFrom, item.TransferStation1);
                //    airportFrom = TryAddTravelItem(airportFrom, item.TransferStation2);
                //    airportFrom = TryAddTravelItem(airportFrom, item.AirportTo);
                //}
                //foreach (var item in travelItems)
                //{
                //    var ghgEvaluateItem = $"商務旅行({item.From}-{item.To})";
                //    if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == ghgEvaluateItem))
                //    {
                //        var materialAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                //        {
                //            LCAID = LCAID,
                //            LifeCycleStage = 2,
                //            GHGEvaluateItem = ghgEvaluateItem,
                //            ActivityIntensity = item.Count,
                //            KgCO2e = 0m,
                //            Unit = "kgco2e",
                //            EmissionKg = 0m,
                //            EmissionT = 0m,
                //        };
                //        result.Add(materialAssmt);
                //        _db.Entry(materialAssmt).State = EntityState.Added;
                //    }
                //    else
                //    {
                //        var updateItem = deliveryData.FirstOrDefault(x => x.GHGEvaluateItem == ghgEvaluateItem && x.ActivityIntensity != item.Count);
                //        if (updateItem != null)
                //        {
                //            updateItem.ActivityIntensity = item.Count;
                //            updateItem.EmissionKg = item.Count * updateItem.KgCO2e;
                //            updateItem.EmissionT = item.Count * updateItem.KgCO2e / 1000;
                //        }
                //    }
                //}
                //if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "員工通勤"))
                //{
                //    var employeeAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                //    {
                //        LCAID = LCAID,
                //        LifeCycleStage = 2,
                //        GHGEvaluateItem = "員工通勤",
                //        ActivityIntensity = 0,
                //        KgCO2e = 0.0951m,
                //        Unit = "延人公里(PKM)",
                //        EmissionKg = 0m,
                //        EmissionT = 0m,
                //    };
                //    result.Add(employeeAssmt);
                //    _db.Entry(employeeAssmt).State = EntityState.Added;
                //}
                //if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "客戶和訪客運輸造成之排放"))
                //{ 
                //    var customerAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                //    {
                //        LCAID = LCAID,
                //        LifeCycleStage = 2,
                //        GHGEvaluateItem = "客戶和訪客運輸造成之排放",
                //        ActivityIntensity = 0,
                //        KgCO2e = 0.115m,
                //        Unit = "延人公里(PKM)",
                //        EmissionKg = 0m,
                //        EmissionT = 0m,
                //    };
                //    result.Add(customerAssmt);
                //    _db.Entry(customerAssmt).State = EntityState.Added;
                //}
                //string TryAddTravelItem(string airportFrom, string airportTo)
                //{
                //    if (!string.IsNullOrEmpty(airportTo))
                //    { 
                //        var travelItem = travelItems.FirstOrDefault(x => x.From == airportFrom && x.To == airportTo);
                //        if (travelItem != null)
                //        {
                //            travelItem.Count += 1;
                //        }
                //        else
                //        {
                //            travelItems.Add(new IntlTravel() { From = airportFrom, To = airportTo, Count = 1 });
                //        }
                //        return airportTo;
                //    }
                //    return airportFrom;
                //}
            }
            return result;
        }

        /// <summary>
        /// 更新运作支援及服务资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateService(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> serviceData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in serviceData)
                {
                    _db.Entry(data).State = EntityState.Deleted;
                }
            }
            //else
            //{
            //    result.AddRange(serviceData);
            //}
            //if (update)
            {
                var vehicles = _db.Set<LCACommonSurveyForm_Vehicles>().Where(x => x.LCAID == LCAID).ToList();
                var powerUsages = _db.Set<LCACommonSurveyForm_PowerUsages>().Where(x => x.LCAID == LCAID).ToList();
                var waterUsages = _db.Set<LCARiskAssmtSurveyForm_WaterUsages>().Where(x => x.LCAID == LCAID).ToList();
                var options = GetYearlyParameterOptions(Categories.Vehicle);
                var vehicleItems = new List<Vehicle>();
                foreach (var item in vehicles)
                {
                    if (!item.ParameterID.HasValue)
                    {
                        continue;
                    }
                    if (options.TryGetValue(item.ParameterID.Value, out string parameter))
                    {
                        var fuelType = GetFuelType(parameter);
                        var vehicleItem = vehicleItems.FirstOrDefault(x => x.FuelType == fuelType);

                        if (vehicleItem != null)
                        {
                            vehicleItem.Scalar += item.Scalar;
                        }
                        else
                        {
                            vehicleItems.Add(new Vehicle() { FuelType = fuelType, Scalar = item.Scalar });
                        }
                    }
                }
                foreach (var item in vehicleItems)
                {
                    var ghgEvaluteItem = $"車輛用油上游能源採購({item.FuelType})";

                    if (delete || !serviceData.Any(x => x.GHGEvaluateItem == ghgEvaluteItem))
                    {
                        var vehicleAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 3,
                            GHGEvaluateItem = ghgEvaluteItem,
                            ActivityIntensity = item.Scalar,
                            KgCO2e = 0m,
                            Unit = "L",
                            EmissionKg = 0m,
                            EmissionT = 0m,
                        };
                        result.Add(vehicleAssmt);
                        _db.Entry(vehicleAssmt).State = EntityState.Added;
                    }
                }
                var sumPowerNormal = powerUsages.Where(x => x.Type == LCACommonSurveyForm_PowerUsages.PowerType.Normal).Sum(x => x.Scalar);
                var sumPowerGreen = powerUsages.Where(x => x.Type == LCACommonSurveyForm_PowerUsages.PowerType.GreenNoCarbon).Sum(x => x.Scalar);
                if (sumPowerNormal > 0)
                {
                    var powerNormalGHGEvaluateItem1 = "電力上游能源採購(一般用電)";
                    var powerNormalGHGEvaluateItem2 = "C2外購電力(一般用電)";
                    if (delete || !serviceData.Any(x => x.GHGEvaluateItem == powerNormalGHGEvaluateItem1))
                    {
                        var powerAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 3,
                            GHGEvaluateItem = powerNormalGHGEvaluateItem1,
                            ActivityIntensity = sumPowerNormal,
                            KgCO2e = 0.0923m,
                            Unit = "每度電（Kw/h）",
                            EmissionKg = sumPowerNormal * 0.0923m,
                            EmissionT = sumPowerNormal * 0.0923m / 1000
                        };
                        result.Add(powerAssmt);
                        _db.Entry(powerAssmt).State = EntityState.Added;
                    }
                    else
                    {
                        var updateItem = serviceData.FirstOrDefault(x => x.GHGEvaluateItem == powerNormalGHGEvaluateItem1 && x.ActivityIntensity != sumPowerNormal);
                        if (updateItem != null)
                        {
                            updateItem.ActivityIntensity = sumPowerNormal;
                            updateItem.EmissionKg = sumPowerNormal * 0.0923m;
                            updateItem.EmissionT = sumPowerNormal * 0.0923m / 1000;
                        }
                    }
                    if (delete || !serviceData.Any(x => x.GHGEvaluateItem == powerNormalGHGEvaluateItem2))
                    {
                        var powerAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 3,
                            GHGEvaluateItem = powerNormalGHGEvaluateItem2,
                            ActivityIntensity = sumPowerNormal,
                            KgCO2e = 0.8042m,
                            Unit = "每度電（Kw/h）",
                            EmissionKg = sumPowerNormal * 0.8042m,
                            EmissionT = sumPowerNormal * 0.8042m / 1000
                        };
                        result.Add(powerAssmt);
                        _db.Entry(powerAssmt).State = EntityState.Added;
                    }
                    else
                    {
                        var updateItem = serviceData.FirstOrDefault(x => x.GHGEvaluateItem == powerNormalGHGEvaluateItem2 && x.ActivityIntensity != sumPowerNormal);

                        if (updateItem != null)
                        {
                            updateItem.ActivityIntensity = sumPowerNormal;
                            updateItem.EmissionKg = sumPowerNormal * 0.8042m;
                            updateItem.EmissionT = sumPowerNormal * 0.8042m / 1000;
                        }
                    }
                }
                //if (sumPowerGreen > 0)
                //{
                //}
                var sumWaterUsage = waterUsages.Sum(x => x.Scalar);
                if (sumWaterUsage > 0)
                {
                    var waterUsageGHGEvaluateItem = "用水";
                    if (delete || !serviceData.Any(x => x.GHGEvaluateItem == waterUsageGHGEvaluateItem))
                    {
                        var powerAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 3,
                            GHGEvaluateItem = waterUsageGHGEvaluateItem,
                            ActivityIntensity = sumWaterUsage,
                            KgCO2e = 0.299m,
                            Unit = "m3",
                            EmissionKg = sumWaterUsage * 0.299m,
                            EmissionT = sumWaterUsage * 0.299m / 1000
                        };
                        result.Add(powerAssmt);
                        _db.Entry(powerAssmt).State = EntityState.Added;
                    }
                    else
                    {
                        var updateItem = serviceData.FirstOrDefault(x => x.GHGEvaluateItem == waterUsageGHGEvaluateItem && x.ActivityIntensity != sumWaterUsage);
                        if (updateItem != null)
                        {
                            updateItem.ActivityIntensity = sumWaterUsage;
                            updateItem.EmissionKg = sumWaterUsage * 0.299m;
                            updateItem.EmissionT = sumWaterUsage * 0.299m / 1000;
                        }
                    }
                }
                if (delete || !serviceData.Any(x => x.GHGEvaluateItem == "購買顧問服務"))
                {
                    var buyAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 3,
                        GHGEvaluateItem = "購買顧問服務",
                        ActivityIntensity = 0,
                        KgCO2e = 0.66m,
                        Unit = "度",
                        EmissionKg = 0m,
                        EmissionT = 0m,
                    };
                    result.Add(buyAssmt);
                    _db.Entry(buyAssmt).State = EntityState.Added;
                }
                if (delete || !serviceData.Any(x => x.GHGEvaluateItem == "資本貨物"))
                {
                    var goodsAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 3,
                        GHGEvaluateItem = "資本貨物",
                        ActivityIntensity = 0,
                        KgCO2e = 0.007m,
                        Unit = "kg CO2e/台幣",
                        EmissionKg = 0m,
                        EmissionT = 0m,
                    };
                    result.Add(goodsAssmt);
                    _db.Entry(goodsAssmt).State = EntityState.Added;
                }
            }
            string GetFuelType(string value)
            {
                var index = value.IndexOf(',');
                var lastIndex = value.LastIndexOf(',');

                return value.Substring(value.IndexOf(",") + 1, lastIndex - index - 1);
            }
            return result;
        }

        /// <summary>
        /// 更新下游运输资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateDownstreamDelivery(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> deliveryData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in deliveryData)
                {
                    _db.Entry(data).State = EntityState.Deleted;
                }
            }
            //else
            //{
            //    result.AddRange(deliveryData);
            //}
            //if (update)
            {
                var waste = _db.Set<LCARiskAssmtSurveyForm_WasteMaterial>().Where(x => x.LCAID == LCAID).ToList();
                var deliveries = _db.Set<LCARiskAssmtSurveyForm_ItemDelivery>().Where(x => x.LCAID == LCAID).ToList();
                var lca = base.GetLCA(LCAID);
                if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "廢棄物運輸"))
                {
                    var wasteAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 4,
                        GHGEvaluateItem = "廢棄物運輸",
                        ActivityIntensity = lca.WasteMaterialTotalOutputKg,
                        KgCO2e = 0.235m,
                        Unit = "tkm",
                        EmissionKg = lca.WasteMaterialTotalOutputKg * 0.235m,
                        EmissionT = lca.WasteMaterialTotalOutputKg * 0.235m / 1000,
                    };
                    result.Add(wasteAssmt);
                    _db.Entry(wasteAssmt).State = EntityState.Added;
                }
                else
                {
                    var updateItem = deliveryData.FirstOrDefault(x => x.GHGEvaluateItem == "廢棄物運輸" && x.ActivityIntensity != lca.WasteMaterialTotalOutputKg);
                    if (updateItem != null)
                    {
                        updateItem.ActivityIntensity = lca.WasteMaterialTotalOutputKg;
                        updateItem.EmissionKg = lca.WasteMaterialTotalOutputKg * 0.235m;
                        updateItem.EmissionT = lca.WasteMaterialTotalOutputKg * 0.235m / 1000;
                    }
                }
                var sumWasteQuantity = waste.Sum(x => x.TotalQuantity);
                if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "回收物運輸"))
                {
                    var wasteAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 4,
                        GHGEvaluateItem = "回收物運輸",
                        ActivityIntensity = sumWasteQuantity,
                        KgCO2e = 0.235m,
                        Unit = "tkm",
                        EmissionKg = sumWasteQuantity * 0.235m,
                        EmissionT = sumWasteQuantity * 0.235m / 1000,
                    };
                    result.Add(wasteAssmt);
                    _db.Entry(wasteAssmt).State = EntityState.Added;
                }
                else
                {
                    var updateItem = deliveryData.FirstOrDefault(x => x.GHGEvaluateItem == "回收物運輸" && x.ActivityIntensity != sumWasteQuantity);
                    if (updateItem != null)
                    {
                        updateItem.ActivityIntensity = sumWasteQuantity;
                        updateItem.EmissionKg = sumWasteQuantity * 0.235m;
                        updateItem.EmissionT = sumWasteQuantity * 0.235m / 1000;
                    }
                }
                var sumTkmland = deliveries.Sum(x => x.TotalTkmland);
                var sumTkmsea = deliveries.Sum(x => x.TransportTkmsea);
                if (sumTkmland > 0)
                {
                    if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "貨物運輸(陸運)"))
                    {
                        var landAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 4,
                            GHGEvaluateItem = "貨物運輸(陸運)",
                            ActivityIntensity = sumTkmland,
                            KgCO2e = 0.235m,
                            Unit = "tkm",
                            EmissionKg = sumTkmland * 0.235m,
                            EmissionT = sumTkmland * 0.235m / 1000
                        };
                        result.Add(landAssmt);
                        _db.Entry(landAssmt).State = EntityState.Added;
                    }
                    else
                    {
                        var updateItem = deliveryData.FirstOrDefault(x => x.GHGEvaluateItem == "貨物運輸(陸運)" && x.ActivityIntensity != sumTkmland);
                        if (updateItem != null)
                        {
                            updateItem.ActivityIntensity = sumTkmland;
                            updateItem.EmissionKg = sumTkmland * 0.235m;
                            updateItem.EmissionT = sumTkmland * 0.235m / 1000;
                        }
                    }
                }
                if (sumTkmsea > 0)
                {
                    if (delete || !deliveryData.Any(x => x.GHGEvaluateItem == "貨物運輸(海運)"))
                    {
                        var seaAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 4,
                            GHGEvaluateItem = "貨物運輸(海運)",
                            ActivityIntensity = sumTkmsea,
                            KgCO2e = 0.0198m,
                            Unit = "tkm",
                            EmissionKg = sumTkmsea * 0.0198m,
                            EmissionT = sumTkmsea * 0.0198m / 1000
                        };
                        result.Add(seaAssmt);
                        _db.Entry(seaAssmt).State = EntityState.Added;
                    }
                    else
                    {
                        var updateItem = deliveryData.FirstOrDefault(x => x.GHGEvaluateItem == "貨物運輸(海運)" && x.ActivityIntensity != sumTkmsea);
                        if (updateItem != null)
                        {
                            updateItem.ActivityIntensity = sumTkmsea;
                            updateItem.EmissionKg = sumTkmsea * 0.0198m;
                            updateItem.EmissionT = sumTkmsea * 0.0198m / 1000;
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 更新产品使用及废弃资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateProduct(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> productData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in productData)
                {
                    _db.Entry(data).State = EntityState.Deleted;
                }
            }
            //else
            //{
            //    result.AddRange(productData);
            //}
            //if (update)
            {
                var deliveries = _db.Set<LCARiskAssmtSurveyForm_ItemDelivery>().Where(x => x.LCAID == LCAID).ToList();
                var power = _db.Set<LCARiskAssmtSurveyForm_PowerConsumption>().Where(x => x.LCAID == LCAID).ToList();
                foreach (var group in power.GroupBy(x => x.ModelType))
                {
                    var modelType = group.Key;
                    var ghgEvaluateItem = $"產品使用造成溫室氣體排放({modelType})";
                    var delivery = deliveries.FirstOrDefault(x => x.ModelName == modelType);
                    if (delivery != null)
                    {
                        var activityIntensity = delivery.DeliveryAmount * group.Sum(x => x.PowerLossPerYear);
                        if (delete || !productData.Any(x => x.GHGEvaluateItem == ghgEvaluateItem))
                        {
                            var seaAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                            {
                                LCAID = LCAID,
                                LifeCycleStage = 5,
                                GHGEvaluateItem = ghgEvaluateItem,
                                ActivityIntensity = activityIntensity,
                                KgCO2e = 0.509m,
                                Unit = "kgCO2e",
                                EmissionKg = activityIntensity * 0.509m,
                                EmissionT = activityIntensity * 0.509m / 1000
                            };
                            result.Add(seaAssmt);
                            _db.Entry(seaAssmt).State = EntityState.Added;
                        }
                        else
                        {
                            var updateItem = productData.FirstOrDefault(x => x.GHGEvaluateItem == ghgEvaluateItem && x.ActivityIntensity != activityIntensity);
                            if (updateItem != null)
                            {
                                updateItem.ActivityIntensity = activityIntensity;
                                updateItem.EmissionKg = activityIntensity * 0.509m;
                                updateItem.EmissionT = activityIntensity * 0.509m / 1000;
                            }
                        }
                    }
                }
                if (delete || !productData.Any(x => x.GHGEvaluateItem == "產品廢棄階段溫室氣體排放(回收)"))
                {
                    var productAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 5,
                        GHGEvaluateItem = "產品廢棄階段溫室氣體排放(回收)",
                        ActivityIntensity = 0,
                        KgCO2e = 0.12m,
                        Unit = "kg",
                        EmissionKg = 0m,
                        EmissionT = 0m,
                    };
                    result.Add(productAssmt);
                    _db.Entry(productAssmt).State = EntityState.Added;
                }
                if (delete || !productData.Any(x => x.GHGEvaluateItem == "產品廢棄階段溫室氣體排放(焚化)"))
                {
                    var productAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                    {
                        LCAID = LCAID,
                        LifeCycleStage = 5,
                        GHGEvaluateItem = "產品廢棄階段溫室氣體排放(焚化)",
                        ActivityIntensity = 0,
                        KgCO2e = 360m,
                        Unit = "噸",
                        EmissionKg = 0m,
                        EmissionT = 0m,
                    };
                    result.Add(productAssmt);
                    _db.Entry(productAssmt).State = EntityState.Added;
                }
            }
            return result;
        }

        /// <summary>
        /// 更新废弃物处理资料
        /// </summary>
        /// <param name="LCAID"></param>
        /// <param name="delete"></param>
        private IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> UpdateWaste(
            IEnumerable<LCARiskAssmtSurveyForm_LifecycleAssmt> wasteData
            , int LCAID, /*bool update,*/ bool delete)
        {
            var result = new List<LCARiskAssmtSurveyForm_LifecycleAssmt>();
            if (delete)
            {
                foreach (var data in wasteData)
                {
                    _db.Entry(data).State = EntityState.Deleted;
                }
            }
            //else
            //{
            //    result.AddRange(wasteData);
            //}
            //if (update)
            {
                var waste = _db.Set<LCARiskAssmtSurveyForm_WasteMaterial>().Where(x => x.LCAID == LCAID).ToList();
                foreach (var item in waste)
                {
                    if (delete || !wasteData.Any(x => x.GHGEvaluateItem == item.MaterialName))
                    {
                        var wasteAssmt = new LCARiskAssmtSurveyForm_LifecycleAssmt()
                        {
                            LCAID = LCAID,
                            LifeCycleStage = 6,
                            GHGEvaluateItem = item.MaterialName,
                            ActivityIntensity = item.TotalQuantity,
                            KgCO2e = 0m,
                            Unit = "kg",
                            EmissionKg = 0m,
                            EmissionT = 0m,
                        };
                        result.Add(wasteAssmt);
                        _db.Entry(wasteAssmt).State = EntityState.Added;
                    }
                }
            }
            return result;
        }
        public LCARiskAssmtSurveyForm_LifecycleAssmt Save(LCARiskAssmtSurveyForm_LifecycleAssmt toBeSave)
        {
            var user = GetUserContext();

            if (!AuthorizedLCAs.Contains(toBeSave.LCAID)) 
                throw new Exception("not authorized");

            ThrowExceptionIfDuplicate(toBeSave);

            var entry = _db.Entry(toBeSave);
            var other = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>()
                .Where(x => x.LCAID == toBeSave.LCAID && x.ID != toBeSave.ID).ToList();
            var sumEmissionT = other.Sum(x => x.EmissionT);

            toBeSave.ActivityIntensity = Math.Round(toBeSave.ActivityIntensity, 4);
            toBeSave.KgCO2e = Math.Round(toBeSave.KgCO2e, 10);
            if (toBeSave.ID == 0)
            {
                toBeSave.CreatedBy = user.Id;
                toBeSave.CreatedDate = DateTime.Now;
                entry.State = EntityState.Added;
            }
            else
            {
                toBeSave.ModifiedBy = user.Id;
                toBeSave.ModifiedDate = DateTime.Now;
                entry.State = EntityState.Modified;
                entry.Property(x => x.LCAID).IsModified = false;
            }

            sumEmissionT += toBeSave.EmissionT;

            foreach (var data in other)
            {
                data.Percentage = Math.Round((data.EmissionT / sumEmissionT) * 100, 4);
            }

            _db.SaveChanges();

            return toBeSave;
        }

        private void ThrowExceptionIfDuplicate(LCARiskAssmtSurveyForm_LifecycleAssmt toBeSave)
        {
            var qry = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.LCAID == toBeSave.LCAID
                    && x.ID != toBeSave.ID && x.LifeCycleStage == toBeSave.LifeCycleStage
                    && x.GHGEvaluateItem == toBeSave.GHGEvaluateItem);
            if (qry!=null && qry.ToList().Count>0)
            {
                throw new Exception($"同一生命周期，評估項目 {toBeSave.GHGEvaluateItem} 不能重複");
            }
        }

        public void Delete(int ID)
        {
            var entry = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.ID == ID).Single();
            var left = _db.Set<LCARiskAssmtSurveyForm_LifecycleAssmt>().Where(x => x.LCAID == entry.LCAID && x.ID != ID).ToList();
            var LCA = GetLCA(entry.LCAID);
            if (LCA.Status != LCAStatus.New && LCA.Status != LCAStatus.Processing) throw new Exception("Business logic error , should not delete data in this status: " + LCA.Status.ToString());

            var ToBeDelete = _db.Entry(entry);
            ToBeDelete.State = EntityState.Deleted;

            if (left.Count > 0)
            {
                UpdatePercentage(left);
            }

            _db.SaveChanges();
        }

        public class IntlTravel
        {
            /// <summary>
            /// 起點(機場)
            /// </summary>
            public string From { get; set; }

            /// <summary>
            /// 中轉站/迄點(機場)
            /// </summary>
            public string To { get; set; }

            /// <summary>
            /// 组数
            /// </summary>
            public int Count { get; set; }
        }

        public class Vehicle
        {
            public string FuelType { get; set; }

            public decimal Scalar { get; set; }
        }
    }
}