using System;
using System.Collections.Generic;
using System.Linq;
using Weee.DAL;
using Weee.Models;
using Qcarbon.ViewModels.DTO;
using CScommon;
using Resources;
using System.Globalization;
using Weee.Models.Survey_form_metadata;
using Qcarbon.Database.Lca.Org36;
using NLog;
using System.Data.Entity;
using LinqToExcel.Extensions;
using DocumentFormat.OpenXml.Office2010.Excel;

namespace Weee.Service
{
    public class WeeeMaterialTransportService
    {
        private WeeeDataContext _db;
        private Logger log;

        public WeeeMaterialTransportService(WeeeDataContext db)
        {
            _db = db;
        }

        public List<RiskMaterialC3EmissionViewModel> GetByLCAID(int LCAID)
        {
            var re = new List<RiskMaterialC3EmissionViewModel>();
            var dbList = _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Where(x => x.LCAID == LCAID).ToList();
            if (dbList != null && dbList.Count() > 0)
                re = JsonUtl.jsonCopy<List<LCARiskAssmtSurveyForm_MaterialC3Emission>, List<RiskMaterialC3EmissionViewModel>>(dbList);
            foreach (var item in re)
            {
                var transports = _db.LCARiskAssmtSurveyForm_MaterialTransports.Where(x => x.RiskAssmtMaterialID == item.ID);
                item.EmissionLand = transports.Where(x => x.Description == MaterialTransportType.MaterialTransportTypeLand.ToString()).Select(x => x.KgCO2e).DefaultIfEmpty(0).Sum();
                item.EmissionSea = transports.Where(x => x.Description == MaterialTransportType.MaterialTransportTypeSea.ToString()).Select(x => x.KgCO2e).DefaultIfEmpty(0).Sum();
                item.EmissionAir = transports.Where(x => x.Description == MaterialTransportType.MaterialTransportTypeAir.ToString()).Select(x => x.KgCO2e).DefaultIfEmpty(0).Sum();
                item.EmissionTotal = item.EmissionLand + item.EmissionSea + item.EmissionAir + item.Emission;
            }
            return re;
        }
        public List<RiskMaterialTransportViewModel> GetByC3EmissionId(int C3EmissionId)
        {
            var re = new List<RiskMaterialTransportViewModel>();
            var dbList = _db.LCARiskAssmtSurveyForm_MaterialTransports.Where(x => x.RiskAssmtMaterialID == C3EmissionId).ToList();
            if (dbList != null && dbList.Count() > 0)
                re = JsonUtl.jsonCopy<List<LCARiskAssmtSurveyForm_MaterialTransport>, List<RiskMaterialTransportViewModel>>(dbList);
            return re;
        }
        
        public int SaveMaterialC3Emissions(RiskMaterialC3EmissionViewModel sour)
        {

            if (string.IsNullOrWhiteSpace(sour.MaterialNo))
                throw new Exception($"{Resource.MaterialNo} {Resource.HeaderMessageRequire}");
            if (string.IsNullOrWhiteSpace(sour.MaterialName))
                throw new Exception($"{Resource.RAMC3EMaterialName} {Resource.HeaderMessageRequire}");
            if (string.IsNullOrWhiteSpace(sour.AnnualPurchaseAmountUnit))
                throw new Exception($"{Resource.RAMC3EMaterialUnit} {Resource.HeaderMessageRequire}");
            if (string.IsNullOrWhiteSpace(sour.Unit))
                throw new Exception($"{Resource.RAMC3EMaterialActivityUnit} {Resource.HeaderMessageRequire}");

            sour.ActivityIntensity = sour.MaterialSpec * sour.AnnualPurchaseAmount;
            sour.Emission = sour.ActivityIntensity * sour.ParameterValue;//.KgCO2e;
            int re = 0;
            //string logicCk = CkSaveLogic(sour);
            //if(!string.IsNullOrWhiteSpace(logicCk))
            //    throw new Exception(logicCk);
            //try
            {
                if (sour.ID == 0)
                {
                    var dbIt = new LCARiskAssmtSurveyForm_MaterialC3Emission()
                    {
                        LCAID = sour.LCAID,
                        MaterialNo = sour.MaterialNo,
                        MaterialName = sour.MaterialName,
                        MaterialSpec = sour.MaterialSpec,
                        AnnualPurchaseAmount = sour.AnnualPurchaseAmount,
                        AnnualPurchaseAmountUnit = sour.AnnualPurchaseAmountUnit,
                        KgCO2e = sour.KgCO2e,
                        ActivityIntensity = sour.ActivityIntensity,
                        Emission = sour.Emission,
                        Unit = sour.Unit,
                        Comment = sour.Comment,
                        CreatedDate = DateTime.Now,
                        CreatedBy = sour.ModifiedBy,
                        ModifiedDate = DateTime.Now,
                        ModifiedBy = sour.ModifiedBy,
                        ParameterValue= sour.ParameterValue,
                        ParameterDescription= sour.ParameterDescription,
                    };
                    _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Add(dbIt);
                    _db.SaveChanges();
                    re = dbIt.ID;
                }
                else
                {
                    var list = _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Where(x => x.ID == sour.ID).ToList();
                    if (list != null && list.Count() > 0)
                    {
                        re = sour.ID;
                        var dbIt = list.FirstOrDefault();

                        dbIt.MaterialNo = sour.MaterialNo;
                        dbIt.MaterialName = sour.MaterialName;
                        dbIt.MaterialSpec = sour.MaterialSpec;
                        dbIt.AnnualPurchaseAmount = sour.AnnualPurchaseAmount;
                        dbIt.AnnualPurchaseAmountUnit = sour.AnnualPurchaseAmountUnit;
                        dbIt.KgCO2e = sour.KgCO2e;
                        dbIt.ActivityIntensity = sour.ActivityIntensity;
                        dbIt.Emission = sour.Emission;
                        dbIt.Unit = sour.Unit;
                        dbIt.Comment = sour.Comment;
                        dbIt.ModifiedDate = DateTime.Now;
                        dbIt.ModifiedBy = sour.ModifiedBy;
                        dbIt.ParameterDescription= sour.ParameterDescription;
                        dbIt.ParameterValue= sour.ParameterValue;
                        _db.SaveChanges();
                    }
                    else
                        throw new Exception("查無Id: " + sour.ID.ToString() + "資料");
                }
                Org36RecalculateService o36rs = new Org36RecalculateService(_db);
                o36rs.lifeCycleSyncMaterial(sour.LCAID);
            }
            //catch(Exception ex)
            //{
            //    log.Error(ex);
            //    log.Error(CScommon.Exceptions.inner(ex));
            //    log.Error(ex.StackTrace);
            //    throw;
            //}
            return re;
        }

        public int SaveMaterialTransport(RiskMaterialTransportViewModel sour)
        {
            var materialList = _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Where(x => x.ID == sour.RiskAssmtMaterialID).ToList();
            var materialIt = materialList.FirstOrDefault();
            //sour.TransportQuantity = materialIt.ActivityIntensity * sour.TransportDistance;
            //sour.KgCO2e = sour.TransportQuantity * sour.Scalar;
            int re = 0;
            try { 
                if (sour.ID == 0)
                {
                    var dbIt = new LCARiskAssmtSurveyForm_MaterialTransport()
                    {
                        LCAID = sour.LCAID,
                        RiskAssmtMaterialID = sour.RiskAssmtMaterialID,
                        TransportDate = DateTime.Now,
                        JourneyNO = sour.JourneyNO,
                        StartLocation = sour.StartLocation,
                        EndLocation = sour.EndLocation,
                        KgCO2e = sour.KgCO2e,
                        TransportDistance = sour.TransportDistance,
                        TransportQuantity = sour.TransportQuantity,
                        Scalar = sour.Scalar,
                        ScalarUnit = sour.ScalarUnit,
                        ScalarComment = sour.ScalarComment,
                        Description = sour.Description.ToString(),
                        ModifiedDate = DateTime.Now,
                        ModifiedBy = sour.ModifiedBy,
                        TransportWeight= sour.TransportWeight,
                        ParameterValue= sour.ParameterValue,
                    };
                    _db.LCARiskAssmtSurveyForm_MaterialTransports.Add(dbIt);
                    _db.SaveChanges();
                    re = dbIt.ID;
                }
                else
                {
                    var list = _db.LCARiskAssmtSurveyForm_MaterialTransports.Where(x => x.ID == sour.ID).ToList();
                    if (list != null && list.Count() > 0)
                    {
                        re = sour.ID;
                        var dbIt = list.FirstOrDefault();

                        dbIt.TransportDate = DateTime.Now;
                        dbIt.JourneyNO = sour.JourneyNO;
                        dbIt.StartLocation = sour.StartLocation;
                        dbIt.EndLocation = sour.EndLocation;
                        dbIt.KgCO2e = sour.KgCO2e;
                        dbIt.TransportDistance = sour.TransportDistance;
                        dbIt.TransportQuantity = sour.TransportQuantity;
                        dbIt.Scalar = sour.Scalar;
                        dbIt.ScalarUnit = sour.ScalarUnit;
                        dbIt.ScalarComment = sour.ScalarComment;
                        dbIt.Description = sour.Description.ToString();
                        dbIt.ModifiedDate = DateTime.Now;
                        dbIt.ModifiedBy = sour.ModifiedBy;
                        dbIt.TransportWeight= sour.TransportWeight;
                        dbIt.ParameterValue= sour.ParameterValue;
                        _db.SaveChanges();
                    }
                    else
                        throw new Exception("查無Id: " + sour.ID.ToString() + "資料");
                }
                Org36RecalculateService o36rs = new Org36RecalculateService(_db);
                o36rs.lifeCycleSyncMaterialTransport(sour.LCAID);
            }
            catch (Exception ex)
            {
                log.Error(ex);
                log.Error(CScommon.Exceptions.inner(ex));
                log.Error(ex.StackTrace);
                throw;
            }
            return re;
        }


        public void DelMaterialC3Emissions(int Id)
        {
            var trans = _db.Database.BeginTransaction();
            try
            {
                var list = _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Where(x => x.ID == Id).ToList();
                if (list != null && list.Count > 0)
                {
                    var listMT = _db.LCARiskAssmtSurveyForm_MaterialTransports.Where(x => x.RiskAssmtMaterialID == Id).ToList();
                    if (listMT != null && listMT.Count > 0)
                    {
                        foreach (var oneMT in listMT)
                        {
                            _db.LCARiskAssmtSurveyForm_MaterialTransports.Remove(oneMT);
                        }
                    }

                    var dbIt = list.FirstOrDefault();
                    int LCAID = dbIt.LCAID;
                    _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.Remove(dbIt);
                    _db.SaveChanges();
                    Org36RecalculateService o36rs = new Org36RecalculateService(_db);
                    o36rs.lifeCycleSyncMaterial(LCAID);
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                log.Error(ex);
                log.Error(CScommon.Exceptions.inner(ex));
                log.Error(ex.StackTrace);
                throw;
            }
        }
        public void DelMaterialTransport(int Id)
        {
            var trans = _db.Database.BeginTransaction();
            try
            {
                var list = _db.LCARiskAssmtSurveyForm_MaterialTransports.Where(x => x.ID == Id).ToList();
                if (list != null && list.Count > 0)
                {
                    var dbIt = list.FirstOrDefault();
                    _db.LCARiskAssmtSurveyForm_MaterialTransports.Remove(dbIt);
                    _db.SaveChanges();
                    int LCAID = dbIt.LCAID;
                    Org36RecalculateService o36rs = new Org36RecalculateService(_db);
                    o36rs.lifeCycleSyncMaterialTransport(LCAID);
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                log.Error(ex);
                log.Error(CScommon.Exceptions.inner(ex));
                log.Error(ex.StackTrace);
                throw;
            }
        }
        
        public List<string> GetExcelColumnsNms()
        {
            var re = new List<string>()
            {
                Resource.Commuting,
                Resource.EmployeesNumber,
                Resource.WorkingDays,
                Resource.AverageMovingDistance,
                Resource.Parameter,
                Resource.PersonDayKm,
                Resource.KgCO2e
            };
            return re;
        }


        public void SaveToDb(List<RiskMaterialC3EmissionViewModel> sour)
        {
            if (sour != null && sour.Count() > 0)
            {
                var tbList = new List<LCARiskAssmtSurveyForm_MaterialC3Emission>();
                foreach (var it in sour)
                {
                    var dbIt = new LCARiskAssmtSurveyForm_MaterialC3Emission()
                    {
                        LCAID = it.LCAID,
                        MaterialNo = it.MaterialNo,
                        MaterialName = it.MaterialName,
                        MaterialSpec = it.MaterialSpec,
                        AnnualPurchaseAmount = it.AnnualPurchaseAmount,
                        KgCO2e = it.KgCO2e,
                        ActivityIntensity = it.ActivityIntensity,
                        Emission = it.Emission,
                        Unit = it.Unit,
                        Comment = it.Comment,
                        CreatedDate = DateTime.Now,
                        CreatedBy = it.ModifiedBy,
                        ModifiedDate = DateTime.Now,
                        ModifiedBy = it.ModifiedBy
                    };
                    tbList.Add(dbIt);
                }
                _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.AddRange(tbList);
                _db.SaveChanges();
            }
        }

        /// <summary>
        /// 引用，除引用材料的資料外，其下的運輸資料也會一併引用
        /// </summary>
        /// <param name="originLCAID"></param>
        /// <param name="quoteLCAID"></param>
        /// <param name="userName"></param>
        public void QuoteAllData(int originLCAID, int quoteLCAID, string userName)
        {
            var trans = _db.Database.BeginTransaction();
            try
            {
                var quoteMaterials = _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.AsNoTracking().Where(x => x.LCAID == quoteLCAID).ToList();
                var quoteTransports = _db.LCARiskAssmtSurveyForm_MaterialTransports.AsNoTracking().Where(x => x.LCAID == quoteLCAID).ToList();
                var materialIDs = quoteMaterials.Select((x, i) => new { x.ID, index = i }).ToDictionary(x => x.ID, x => x.index);
                foreach (var quoteMaterial in quoteMaterials)
                {
                    var tempQuoteTransports = quoteTransports.Where(x => x.RiskAssmtMaterialID == quoteMaterial.ID).ToList();
                    quoteMaterial.ID = 0;
                    quoteMaterial.LCAID = originLCAID;
                    quoteMaterial.ModifiedDate = DateTime.Now;
                    quoteMaterial.ModifiedBy = userName;
                }
                _db.LCARiskAssmtSurveyForm_MaterialC3Emissions.AddRange(quoteMaterials);
                _db.SaveChanges();
                foreach (var quoteTransport in quoteTransports)
                {
                    quoteTransport.ID = 0;
                    var index = materialIDs[(int)quoteTransport.RiskAssmtMaterialID];
                    quoteTransport.RiskAssmtMaterialID = quoteMaterials[index].ID;
                    quoteTransport.LCAID = originLCAID;
                }
                _db.LCARiskAssmtSurveyForm_MaterialTransports.AddRange(quoteTransports);
                _db.SaveChanges();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                log.Error(ex);
                log.Error(CScommon.Exceptions.inner(ex));
                log.Error(ex.StackTrace);
                throw;
            }
        }

        public List<RiskMaterialC3EmissionViewModel> GetExlData(List<RiskMaterialTransportExlImportViewModel> sour, int LCAID, string ModifiedBy)
        {
            var re = new List<RiskMaterialC3EmissionViewModel>();
            if (sour != null && sour.Count > 0)
            {
                foreach (var it in sour)
                {
                    var vIt = new RiskMaterialC3EmissionViewModel()
                    {
                        /*Guid = Guid.NewGuid(),
                        LCAID = LCAID,
                        Commuting = GetCommutingValue(it.StrCommuting),
                        TotalEmployees = Convert.ToInt32(it.StrTotalEmployees),
                        WorkingDays = Convert.ToInt32(it.StrWorkingDays),
                        AverageMovingDistance = Convert.ToDecimal(it.StrAverageMovingDistance),
                        Coefficient = Convert.ToDecimal(it.StrCoefficient),
                        PersonDayKm = Convert.ToDecimal(it.StrPersonDayKm),
                        KgCO2e = Convert.ToDecimal(it.StrKgCO2e),
                        ModifiedBy = ModifiedBy*/
                };
                    re.Add(vIt);
                }
            }
            return re;
        }

        public string ExlDataCk(List<RiskMaterialTransportExlImportViewModel> sour)
        {
            string errMsg = "";
            if (sour == null || sour.Count == 0)
                errMsg = "匯入Excel不可為空";
            else
            {
                var errList = new List<string>();
                for (int i = 0; i < sour.Count(); i++)
                {
                    string rowErrMsg = "Line " + (i + 2).ToString() + " Error";
                    var it = sour[i];
                    bool rowCk = true;
                    /*if (rowCk && string.IsNullOrWhiteSpace(it.StrCommuting))
                        rowCk = false;
                    if (rowCk && string.IsNullOrWhiteSpace(it.StrTotalEmployees))
                        rowCk = false;
                    if (rowCk && string.IsNullOrWhiteSpace(it.StrWorkingDays))
                        rowCk = false;
                    if (rowCk && string.IsNullOrWhiteSpace(it.StrAverageMovingDistance))
                        rowCk = false;
                    if (rowCk && string.IsNullOrWhiteSpace(it.StrCoefficient))
                        rowCk = false;

                    var citems = GetCommutingKeyValue().Where(x => x.itemNm == it.StrCommuting).ToList();
                    if (citems == null || citems.Count == 0)
                    {
                        rowCk = false;
                        rowErrMsg += ", Commuting";
                    }

                    if (!Int32.TryParse(it.StrTotalEmployees, out int TotalEmployees) || TotalEmployees <= 0)
                        rowCk = false;
                    if (!Int32.TryParse(it.StrWorkingDays, out int WorkingDays) || WorkingDays <= 0)
                        rowCk = false;

                    var AverageMovingDistance = GetFromExcel(it.StrAverageMovingDistance);
                    if (AverageMovingDistance <= 0)
                        rowCk = false;
                    if (!CkDecimalLength(AverageMovingDistance))
                    {
                        rowCk = false;
                        rowErrMsg += ", AverageMovingDistance";
                    }

                    var Coefficient = GetFromExcel(it.StrCoefficient);
                    if (Coefficient <= 0)
                        rowCk = false;
                    if (!CkDecimalLength(Coefficient))
                    {
                        rowCk = false;
                        rowErrMsg += ", Coefficient";
                    }

                    decimal _PersonDayKm = GetPersonDayKm(TotalEmployees, WorkingDays, AverageMovingDistance);
                    if (CkDecimalLength(_PersonDayKm))
                        sour[i].StrPersonDayKm = _PersonDayKm.ToString();
                    else
                    {
                        rowErrMsg += ", PersonDayKm:" + _PersonDayKm.ToString();
                        rowCk = false;
                    }

                    decimal _KgCO2e = _PersonDayKm * Coefficient;
                    if (CkDecimalLength(_KgCO2e))
                        sour[i].StrKgCO2e = _KgCO2e.ToString();
                    else
                    {
                        rowErrMsg += ", KgCO2e:" + _KgCO2e.ToString();
                        rowCk = false;
                    }
                    */
                    if (!rowCk)
                        errList.Add(rowErrMsg);
                }
                if (errList.Count() > 0)
                    errMsg = String.Join(Environment.NewLine, errList);
            }

            return errMsg;
        }

        private decimal GetFromExcel(string sour)
        {
            if (string.IsNullOrWhiteSpace(sour))
                return 0;
            if (!Decimal.TryParse(sour, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out decimal Value))
                throw new Exception(sour + " not number");
            return Value;
        }

    }
}