using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using Weee.DAL;
using Weee.Models.Paramemter;
using Weee.Models;
using System.Data.Entity.Core.Objects;
using Qcarbon.ViewModels.DTO;
using Weee.Controllers;

namespace Weee.Service
{
    public class WeeeParameterDataService
    {
        public WeeeDataContext _db;

        public WeeeParameterDataService(WeeeDataContext db)
        {
            _db = db;
        }
        public WeeeDataContext GetDataContext()
        {
            return _db;
        }
        public IEnumerable<object> GetSimaproCategories()
        {
            var resulst = _db.SimaproCategories
                .ToList()
                .Select(x => new { ID = x.ID, Name = x.DisplayName });
            return resulst;
        }

        public IEnumerable<PCR> GetPublicPCRs()
        {
            var pcrs = _db.PCRs.ToList();
            return pcrs;
        }

        public PCR GetPublicPCR(int id)
        {
            var pcr = _db.PCRs.Where(x => x.ID == id).First();
            return pcr;
        }

        public List<string> GetSimaproUnits()
        {
            return _db.SimaproParameters.Select(x => x.Unit).Distinct().ToList();
        }
        public decimal getARnGWPvalue(int LCAID, int ARnGWPid)
        {
            decimal ret = 0;
            var qry = (from a in _db.AssessmentReportGWP
                       where a.ID==ARnGWPid
                       select a).FirstOrDefault();
            if (qry == null)
                return ret;
            string ARversion = getARnVersion(LCAID);
            switch (ARversion)
            {
                case "AR6":
                    ret = qry.AR6GWP100;
                    break;
                case "AR5":
                    ret = qry.AR5GWP100;
                    break;
                case "AR4":
                    ret = qry.AR4GWP100;
                    break;
            }
            return ret;
        }
        protected string getARnVersion(int LCAID)
        {
            string ret = "";
            var qry = (from a in _db.LCAs
                       where a.ID == LCAID
                       select a).FirstOrDefault();
            if (qry != null)
            {
                ret = qry.ARversion;
                if (string.IsNullOrWhiteSpace(ret))
                {
                    ret = "AR6";
                    qry.ARversion = ret;
                    _db.SaveChanges();
                }
            }
            return ret;
        }
        public Dictionary<int, string> GetARnGWPoptions(GWPparameterType GWPtype, int LCAID)
        {
            Dictionary<int, string> ret = new Dictionary<int, string>();
            var qry = (from a in _db.AssessmentReportGWP
                       join b in _db.LCAs on a.GWPtype equals GWPtype
                       where b.ID == LCAID
                       orderby a.ingredientName
                       select a).AsQueryable();
                       //select new
                       //{
                       //    ID = a.ID,
                       //    DisplayName = a.ingredientName
                       //}).AsQueryable();
            if (qry==null || !qry.Any())
                return ret;
            foreach(var rec in qry.ToList())
            {
                if (string.IsNullOrWhiteSpace(rec.Alias) || string.Compare(rec.ingredientName, rec.Alias)==0)
                    ret.Add(rec.ID, rec.ingredientName);
                else
                    ret.Add(rec.ID, $"{rec.Alias}({rec.ingredientName})");
            }
            //var ret = qry.ToDictionary(x => x.ID, x => x.DisplayName);
            //var ret = _db.NonYearlyParameters.Include(x => x.Type.Category)
            //                              .Where(x => x.Type.Category.Category == cate)
            //                              .Where(x => x.IsHistory == false)
            //                              .ToList()
            //                              .Select(x => new { ID = x.ID, DisplayName = x.Type.DisplayName })
            //                              .OrderBy(x => x.DisplayName);
            //var qry = ret
            //                              .ToDictionary(x => x.ID, x => x.DisplayName);
            return ret;
        }

        public Dictionary<int, string> GetNonYearlyParameterOptions(Categories cate)
        {
            var ret = _db.NonYearlyParameters.Include(x => x.Type.Category)
                                          .Where(x => x.Type.Category.Category == cate)
                                          .Where(x => x.IsHistory == false)
                                          .ToList()
                                          .Select(x => new { ID = x.ID, DisplayName = x.Type.DisplayName })
                                          .OrderBy(x => x.DisplayName);
            var qry = ret
                                          .ToDictionary(x => x.ID, x => x.DisplayName);
            return qry;// ret;
        }

        public Dictionary<int, string> GetYearlyParameterOptions(Categories cate)
        {   
            if (cate.Equals(Categories.Kitchen))
            {
                var ret = _db.YearlyParameters
                .Include(x => x.Type)
                .Include(x => x.Area)
                .Where(x => x.Area.Category.Category == cate && x.IsHistory == false
                    && x.Type.DisplayNameTW != CScommon.ProgramConstants.POWER_C4_COEFFNAME)
                .OrderBy(x => x.AreaID)
                .ThenBy(x => x.Year)
                .ToDictionary(x => x.ID, x => x.Area.DisplayName + "," + x.Type.DisplayName + "," + x.Year + "," + x.Type.oriHeatValue);
                return ret;
            }
            else
            {
                var ret = _db.YearlyParameters
                .Include(x => x.Type)
                .Include(x => x.Area)
                .Where(x => x.Area.Category.Category == cate && x.IsHistory == false
                    && x.Type.DisplayNameTW != CScommon.ProgramConstants.POWER_C4_COEFFNAME)
                .OrderBy(x => x.AreaID)
                .ThenBy(x => x.Year)
                .ToDictionary(x => x.ID, x => x.Area.DisplayName + "," + x.Type.DisplayName + "," + x.Year);
                return ret;
            }                
        }
        public Dictionary<int, WarmGasType?> GetYearlyParameterWarmGasTypes(Categories cate)
        {

            var ret = _db.YearlyParameters
                .Include(x => x.Type)
                .Include(x => x.Area)
                .Where(x => x.Area.Category.Category == cate && x.IsHistory == false
                    && x.Type.DisplayNameTW != CScommon.ProgramConstants.POWER_C4_COEFFNAME)
                .OrderBy(x => x.AreaID)
                .ThenBy(x => x.Year)
                .ToDictionary(x => x.ID, x => x.Type.warmGasType);
            return ret;
        }

        public IEnumerable<SimaproVersion> GetSimaproVersionCategory(SimaproOption simOption)
        {
            IEnumerable<SimaproVersion> ret;
            // GetSimaproVersionCategory
            if (simOption == SimaproOption.Waste)
            {
                var qry = (from a in _db.SimaproVersions
                            join b in _db.SimaproCategories on a.ID equals b.VersionID
                            where a.IsActive && (b.DisplayNameTW == "廢棄物回收處理服務"
                                || b.DisplayNameTW == "廢棄物處理服務" || b.DisplayNameTW == "WASTE TREATMENT")
                            orderby a.ID descending
                            select a).Distinct().ToList();
                foreach(var rec in qry)
                {
                    int catnum=rec.Categories.Count();
                    var qry2 = _db.SimaproCategories.Where(b => b.VersionID==rec.ID &&
                        (b.DisplayNameTW == "廢棄物回收處理服務"
                                || b.DisplayNameTW == "廢棄物處理服務" || b.DisplayNameTW == "WASTE TREATMENT")).ToList();
                    rec.Categories = qry2;
                }
                ret = qry;
            }
            else if (simOption == SimaproOption.WasteWater)
            {
                var qry = (from a in _db.SimaproVersions
                           join b in _db.SimaproCategories on a.ID equals b.VersionID
                           where a.IsActive && (b.DisplayNameTW == "廢水處理服務"
                               || b.DisplayNameTW == "WASTE TREATMENT")
                           orderby a.ID descending
                           select a).Distinct().ToList();
                foreach (var rec in qry)
                {
                    int catnum = rec.Categories.Count();
                    var qry2 = _db.SimaproCategories.Where(b => b.VersionID == rec.ID &&
                        (b.DisplayNameTW == "廢水處理服務"
                                || b.DisplayNameTW == "WASTE TREATMENT")).ToList();
                    rec.Categories = qry2;
                }
                ret = qry;
            }
            else if (simOption == SimaproOption.GoodsTransportation
                || simOption == SimaproOption.PeopleTransportation)
            {
                var qry = (from a in _db.SimaproVersions
                           join b in _db.SimaproCategories on a.ID equals b.VersionID
                           where a.IsActive && (b.DisplayNameTW == "運輸服務"
                               || b.DisplayNameTW == "TRANSPORTATION")
                           orderby a.ID descending
                           select a).Distinct().ToList();
                foreach (var rec in qry)
                {
                    int catnum = rec.Categories.Count();
                    var qry2 = _db.SimaproCategories.Where(b => b.VersionID == rec.ID &&
                        (b.DisplayNameTW == "運輸服務"
                                || b.DisplayNameTW == "TRANSPORTATION")).ToList();
                    rec.Categories = qry2;
                }
                ret = qry;
            }
            else if (simOption == SimaproOption.Energy)
            {
                var qry = (from a in _db.SimaproVersions
                           join b in _db.SimaproCategories on a.ID equals b.VersionID
                           where a.IsActive && (b.DisplayNameTW == "能資源"
                               || b.DisplayNameTW == "ENERGY")
                           orderby a.ID descending
                           select a).Distinct().ToList();
                foreach (var rec in qry)
                {
                    int catnum = rec.Categories.Count();
                    var qry2 = _db.SimaproCategories.Where(b => b.VersionID == rec.ID &&
                        (b.DisplayNameTW == "能資源"
                                || b.DisplayNameTW == "ENERGY")).ToList();
                    rec.Categories = qry2;
                }
                ret = qry;
            }
            else if (simOption == SimaproOption.Material)
            {
                var qry = (from a in _db.SimaproVersions
                           join b in _db.SimaproCategories on a.ID equals b.VersionID
                           where a.IsActive && (b.DisplayNameTW != "廢棄物回收處理服務"
                               && b.DisplayNameTW != "廢棄物處理服務"
                               && b.DisplayNameTW != "廢水處理服務"
                               && b.DisplayNameTW != "WASTE TREATMENT"
                               && b.DisplayNameTW != "運輸服務"
                               && b.DisplayNameTW != "TRANSPORTATION"
                               && b.DisplayNameTW != "乳品及其加工品"
                               && b.DisplayNameTW != "肉品及其加工品"
                               && b.DisplayNameTW != "蛋品及其加工品"
                               && b.DisplayNameTW != "水產品及其加工品"
                               && b.DisplayNameTW != "穀豆類及其加工品"
                               && b.DisplayNameTW != "蔬果類及其加工品"
                               && b.DisplayNameTW != "醬油及調味品"
                               && b.DisplayNameTW != "食用油脂"
                               && b.DisplayNameTW != "飲品"
                               && b.DisplayNameTW != "民生相關"
                               && b.DisplayNameTW != "能資源"
                               && b.DisplayNameTW != "ENERGY"
                               && b.DisplayNameTW != "WATER")
                           orderby a.ID descending
                           select a).Distinct().ToList();
                foreach (var rec in qry)
                {
                    int catnum = rec.Categories.Count();
                    var qry2 = _db.SimaproCategories.Where(b => b.VersionID == rec.ID &&
                        (b.DisplayNameTW != "廢棄物回收處理服務"
                            && b.DisplayNameTW != "廢棄物處理服務"
                            && b.DisplayNameTW != "廢水處理服務"
                            && b.DisplayNameTW != "WASTE TREATMENT"
                            && b.DisplayNameTW != "運輸服務"
                            && b.DisplayNameTW != "TRANSPORTATION"
                            && b.DisplayNameTW != "乳品及其加工品"
                            && b.DisplayNameTW != "肉品及其加工品"
                            && b.DisplayNameTW != "蛋品及其加工品"
                            && b.DisplayNameTW != "水產品及其加工品"
                            && b.DisplayNameTW != "穀豆類及其加工品"
                            && b.DisplayNameTW != "蔬果類及其加工品"
                            && b.DisplayNameTW != "醬油及調味品"
                            && b.DisplayNameTW != "食用油脂"
                            && b.DisplayNameTW != "飲品"
                            && b.DisplayNameTW != "民生相關"
                            && b.DisplayNameTW != "能資源"
                            && b.DisplayNameTW != "ENERGY"
                            && b.DisplayNameTW != "WATER")).ToList();
                    rec.Categories = qry2;
                }
                ret = qry;
            }
            else
                ret = _db.SimaproVersions.Include(x => x.Categories)
                    .Where(x => x.IsActive)
                    .OrderByDescending(x => x.ID)
                    .ToList();
            return ret;
        }
        public IEnumerable<object> GetSimaproTypeWithoutParameter(int CategoryID, SimaproOption simOption)
        {
            IEnumerable<object> ret;
            // GetSimaproTypeWithoutParameter
            if (simOption==SimaproOption.Waste)
                ret = _db.SimaproTypes.Where(x => x.CategoryID == CategoryID && 
                        (!x.DisplayNameTW.Contains("Waste water") && !x.DisplayNameTW.Contains("Wastewater")))
                    .ToList()
                    .Select(x => new { ID = x.ID, CategoryID = x.CategoryID, DisplayName = x.DisplayName });
            else if (simOption == SimaproOption.WasteWater)
                ret = _db.SimaproTypes.Where(x => x.CategoryID == CategoryID &&
                        (x.DisplayNameTW.Contains("Waste water") || x.DisplayNameTW.Contains("Wastewater")
                         || x.DisplayNameTW.Contains("廢水") || x.DisplayNameTW.Contains("廢(污)水")))
                    .ToList()
                    .Select(x => new { ID = x.ID, CategoryID = x.CategoryID, DisplayName = x.DisplayName });
            else
                ret = _db.SimaproTypes.Where(x => x.CategoryID == CategoryID)
                    .ToList()
                    .Select(x => new { ID=x.ID, CategoryID=x.CategoryID , DisplayName = x.DisplayName});
            return ret;
        }

        public IEnumerable<SimaproParameter> GetSimaproParameterByTypeID(int TypeID, SimaproOption simOption)
        {
            IEnumerable<SimaproParameter> ret;
            if (simOption==SimaproOption.GoodsTransportation)
                ret = _db.SimaproParameters.Where(x => (x.TypeID == TypeID &&
                        (x.Unit != "延人公里(pkm)" && x.Unit != "每人次")))
                    .OrderBy(x => x.Encoding)
                    .ToList();
            else if (simOption == SimaproOption.GoodsTransportation)
                ret = _db.SimaproParameters.Where(x => x.TypeID == TypeID &&
                        x.Unit != "延噸公里(tkm)")
                    .OrderBy(x => x.Encoding)
                    .ToList();
            else
                ret = _db.SimaproParameters.Where(x => x.TypeID == TypeID)
                    .OrderBy(x=>x.Encoding)
                    .ToList();
            return ret;
        }

        public IEnumerable<SimaproParameterType> GetSimaproTypeParameter(int CategoryID)
        {
            return _db.SimaproTypes.Where(x => x.CategoryID == CategoryID).Include(x => x.Parameters).ToList();
        }

        public Dictionary<int, string> GetSimaproEncodingsAndValues(List<int> ids)
        {
            return _db.SimaproParameters
                .Where(x => ids.Contains(x.ID))
                .Select(x => new { ID = x.ID, Result = x.Encoding + "," + x.Value.ToString() })
                .ToDictionary(x => x.ID, x => x.Result);
        }

        public Dictionary<int, string> GetSimaproTypeNames(List<int> ids)
        {
            return _db.SimaproTypes
                .Where(x => ids.Contains(x.ID))
                .ToList()
                .Select(x => new { ID = x.ID, DisplayName = x.DisplayName })
                .ToDictionary(x => x.ID, x => x.DisplayName);
        }

        public Dictionary<int, string> GetParameterValues(IEnumerable<int> IDs)
        {
            var parameters = _db.Parameters
                .Where(x => IDs.Contains(x.ID))
                .ToList();

            var result = new Dictionary<int, string>();

            foreach (var parameter in parameters)
            {
                if (ObjectContext.GetObjectType(parameter.GetType()) == typeof(SimaproParameter))
                {
                    result.Add(parameter.ID, ((SimaproParameter)parameter).Encoding + "," + parameter.Value);
                }
                else
                {
                    result.Add(parameter.ID, parameter.Value.ToString());
                }
            }
            return result;
        }

        public Dictionary<int, string> GetParameterCO2Values(IEnumerable<int> IDs)
        {
            var parameters = _db.Parameters
                .Where(x => IDs.Contains(x.ID))
                .ToList();

            var result = new Dictionary<int, string>();

            foreach (var parameter in parameters)
            {
                if (ObjectContext.GetObjectType(parameter.GetType()) == typeof(SimaproParameter))
                {
                    result.Add(parameter.ID, ((SimaproParameter)parameter).Encoding + "," + parameter.CO2Value);
                }
                else
                {
                    result.Add(parameter.ID, parameter.CO2Value.ToString());
                }
            }
            return result;
        }

        public SimaproParameter GetSimaproParameter(int simaproParameterId)
        {
            var simaproParameter = _db.SimaproParameters.Find(simaproParameterId);
            return simaproParameter;
        }

        public IQueryable<SimaproParameter> GetSimaproParameters()
        {
            return _db.SimaproParameters;
        }

        public NonYearlyParameter GetNonYearlyParameter(int nonYearlyParameterId)
        {
            var nonYearlyParameter = _db.NonYearlyParameters.Find(nonYearlyParameterId);
            return nonYearlyParameter;
        }

        public YearlyParameter GetYearlyParameter(int yearlyParameterId)
        {
            var yearlyParameter = _db.YearlyParameters.Find(yearlyParameterId);
            return yearlyParameter;
        }

        public void DisableProxyAndLazyLoad()
        {
            _db.Configuration.ProxyCreationEnabled = false;
            _db.Configuration.LazyLoadingEnabled = false;
        }
        public object ValueByID(int id)
        {
            object ret = null;
            var qry = (from a in _db.Parameters
                       where a.ID == id
                       select a.Value).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;
        }
        public decimal? LCAID2steamPara(int LCAID)
        {
            decimal? ret = null;
            var qry = (from a in _db.LCACommonSurveyForm_SteamUsage
                       join b in _db.Parameters on a.ParameterID equals b.ID
                       //join c in _db.YearlyParameters on b.ID equals c.ID
                       where a.LCAID == LCAID //&& c.IsHistory==false
                       select b.Value).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;
            //select p.Value
            //from LCACommonSurveyForm_SteamUsages s
            //join Parameters p on s.ParameterID = p.ID
            //where LCAID = 468
        }
        public decimal? LCAID2powerPara(int LCAID)
        {
            decimal? ret = null;
            var qry = (from a in _db.LCACommonSurveyForm_PowerUsage
                       join b in _db.Parameters on a.ParameterID equals b.ID
                       join c in _db.YearlyParameters on b.ID equals c.ID
                       where a.LCAID == LCAID && c.IsHistory == false
                       select b.Value).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;

            //select p.Value
            //from LCACommonSurveyForm_PowerUsages s
            //join Parameters p on s.ParameterID = p.ID
            //where LCAID = 468
        }
        public decimal? LCAID2waterPara(int LCAID)
        {
            decimal? ret = null;
            var qry = (from a in _db.ProductLCAFabSurveyForm_WaterUsage
                       join b in _db.Parameters on a.ParameterID equals b.ID
                       join c in _db.YearlyParameters on b.ID equals c.ID
                       where a.LCAID == LCAID //&& c.IsHistory == false
                       select b.Value).FirstOrDefault();
            if (qry != null)
                ret = qry;
            return ret;

        }
    }
}