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