509 lines
22 KiB
C#
509 lines
22 KiB
C#
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;
|
|
|
|
}
|
|
}
|
|
} |