public static bool GetFodmapStatus(string ingredient)
{
using (FFDBContext db = new FFDBContext())
{
return(db.FODMAPIngredients.Where(f => ingredient.Contains(f.Name.ToLower())).Count() != 0 ? true: false);
}
}
public static string GetLabel(string ingredient)
{
string userID = System.Web.HttpContext.Current.User.Identity.GetUserId();
using (FFDBContext db = new FFDBContext())
{
return(db.UserIngredients.Where(u => u.userID == userID && u.LabelledIngredient.Name == ingredient).Select(u => u.Label).FirstOrDefault());
}
}
public List <HighRiskLabelledIngredient> RetrieveRiskyIngredientsEthnicity4()
{
using (FFDBContext db = new FFDBContext())
{
List <HighRiskLabelledIngredient> highRiskLabelledIngredientList = new List <HighRiskLabelledIngredient>();
List <int> highRiskList = db.UserIngredients.Where(u => u.Label == "High-Risk").Select(u => u.LabelledIngredientID).Distinct().ToList();
foreach (int ingredient in highRiskList)
{
LabelledIngredient labelIng = db.LabelledIngredients.Where(l => l.ID == ingredient).FirstOrDefault();
int count = db.UserIngredients.Where(u => u.LabelledIngredientID == ingredient && u.Label == "High-Risk" && u.AspNetUser.UserInformation.ethnicity == "Native Hawaiian or Other Pacific Islander" && u.AspNetUser.UserProfile.optIn).Count();
HighRiskLabelledIngredient newIngredient = new HighRiskLabelledIngredient(labelIng, count);
highRiskLabelledIngredientList.Add(newIngredient);
}
highRiskLabelledIngredientList = highRiskLabelledIngredientList.OrderByDescending(o => o.countOfLabelOccurences).ToList();
return(highRiskLabelledIngredientList);
}
}
public List <HighRiskLabelledIngredient> RetrieveRiskyIngredientsAge4()
{
using (FFDBContext db = new FFDBContext())
{
List <HighRiskLabelledIngredient> highRiskLabelledIngredientList = new List <HighRiskLabelledIngredient>();
List <int> highRiskList = db.UserIngredients.Where(u => u.Label == "High-Risk").Select(u => u.LabelledIngredientID).Distinct().ToList();
foreach (int ingredient in highRiskList)
{
LabelledIngredient labelIng = db.LabelledIngredients.Where(l => l.ID == ingredient).FirstOrDefault();
int count = db.UserIngredients.Where(u => u.LabelledIngredientID == ingredient && u.Label == "High-Risk" && (DateTime.Now.Year - u.AspNetUser.UserInformation.birthdate.Year) > 55 && (DateTime.Now.Year - u.AspNetUser.UserInformation.birthdate.Year) < 76 && u.AspNetUser.UserProfile.optIn).Count();
HighRiskLabelledIngredient newIngredient = new HighRiskLabelledIngredient(labelIng, count);
highRiskLabelledIngredientList.Add(newIngredient);
}
highRiskLabelledIngredientList = highRiskLabelledIngredientList.OrderByDescending(o => o.countOfLabelOccurences).ToList();
return(highRiskLabelledIngredientList);
}
}
public List <HighRiskLabelledIngredient> RetrieveRiskyIngredients()
{
using (FFDBContext db = new FFDBContext())
{
List <HighRiskLabelledIngredient> highRiskLabelledIngredientList = new List <HighRiskLabelledIngredient>();
List <int> highRiskList = db.UserIngredients.Where(u => u.Label == "High-Risk").Select(u => u.LabelledIngredientID).Distinct().ToList();
foreach (int ingredient in highRiskList)
{
LabelledIngredient labelIng = db.LabelledIngredients.Where(l => l.ID == ingredient).FirstOrDefault();
int count = db.UserIngredients.Where(u => u.LabelledIngredientID == ingredient && u.Label == "High-Risk").Count();
HighRiskLabelledIngredient newIngredient = new HighRiskLabelledIngredient(labelIng, count);
highRiskLabelledIngredientList.Add(newIngredient);
}
highRiskLabelledIngredientList = highRiskLabelledIngredientList.OrderByDescending(o => o.countOfLabelOccurences).ToList();
return(highRiskLabelledIngredientList);
}
}
public IHttpActionResult Create([FromBody] SavedFoodRequest request)
{
using (FFDBContext db = new FFDBContext())
{
if (User.Identity.GetUserId() != null)
{
SavedFood savedFood = new SavedFood(request.UsdaFoodID, request.ListID, request.BrandOwner, request.Upc, request.Description);
try
{
db.SavedFoods.Add(savedFood);
db.SaveChanges();
var jsonData = new JsonResponse
{
success = true,
message = "Food has been saved.",
redirect = false
};
return(Ok(jsonData));
}
catch
{
var jsonData2 = new JsonResponse
{
success = false,
message = "Food has already been saved.",
redirect = false
};
return(Ok(jsonData2));
}
}
var jsonData3 = new JsonResponse
{
success = false,
message = "User not logged in.",
redirect = true
};
return(Ok(jsonData3));
}
}
public bool AddCompany(string companyName, string companyCode)
{
using (FFDBContext context = new FFDBContext())
{
try
{
context.Companies.Add(new Company()
{
Code = companyCode,
Name = companyName
});
context.SaveChanges();
return true;
}
catch (Exception ex)
{
Logger.Error("添加货代公司失败", ex);
return false;
}
}
}
public IHttpActionResult getLists()
{
using (var db = new FFDBContext())
{
string userID = User.Identity.GetUserId();
if (userID != null)
{
List <UserList> userLists = db.UserLists.Where(x => x.userID == userID).Include(u => u.SavedFoods).ToList();
if (!userLists.Any())
{
var jsonNoLists = new UserListJsonResponse
{
success = false,
redirect = false,
};
return(Ok(jsonNoLists));
}
var jsonLoggedIn = new UserListJsonResponse
{
success = true,
redirect = false,
lists = userLists
};
return(Ok(jsonLoggedIn));
}
var jsonNotLoggedIn = new UserListJsonResponse
{
success = false,
redirect = true
};
return(Ok(jsonNotLoggedIn));
}
}
public bool AddRegCode(string machineCode, string regCode, int companyId)
{
using (FFDBContext context = new FFDBContext())
{
try
{
context.RegisterCodes.Add(new RegisterCode()
{
MachineCode = machineCode,
RegCode = regCode,
CompanyId = companyId,
CreatedDate = DateTime.Now,
State = (int)RegCodeStates.Actived
});
context.SaveChanges();
return true;
}
catch (Exception ex)
{
Logger.Error("添加注册码失败", ex);
return false;
}
}
}
public IHttpActionResult Create([FromBody] LabelIngredientRequest req)
{
var ingredientName = req.IngredientName;
var assignLabel = req.AssignLabel;
using (FFDBContext db = new FFDBContext())
{
string userID = User.Identity.GetUserId();
if (userID != null)
{
int ingredientID = db.LabelledIngredients.Where(s => s.Name == ingredientName).Select(s => s.ID).FirstOrDefault();
if (ingredientID == 0)
{
LabelledIngredient labelIngredient = new LabelledIngredient {
Name = ingredientName
};
try
{
db.LabelledIngredients.Add(labelIngredient);
db.SaveChanges();
ingredientID = labelIngredient.ID;
}
catch
{
var jsonData_failed_ingredient_add = new JsonResponse
{
success = false,
message = "Ingredient not added to database",
redirect = false
};
return(Ok(jsonData_failed_ingredient_add));
}
}
if (db.UserIngredients.Where(s => s.LabelledIngredientID == ingredientID && s.userID == userID).Count() > 0)
{
var existingRecord = db.UserIngredients.FirstOrDefault(s => s.LabelledIngredientID == ingredientID && s.userID == userID);
existingRecord.Label = assignLabel;
db.SaveChanges();
var jsonData_edit_record = new JsonResponse
{
success = true,
message = "Label has been changed in the record",
redirect = false
};
return(Ok(jsonData_edit_record));
}
else
{
UserIngredient userIng = new UserIngredient(userID, assignLabel, ingredientID);
try
{
db.UserIngredients.Add(userIng);
db.SaveChanges();
var jsonData_success = new JsonResponse
{
success = true,
message = "Label has been saved.",
redirect = false
};
return(Ok(jsonData_success));
}
catch (Exception e)
{
var jsonData_fail = new JsonResponse
{
success = false,
message = $"Something went wrong: {e.InnerException.InnerException.Message}",
redirect = false
};
return(Ok(jsonData_fail));
}
}
}
var jsonData3 = new JsonResponse
{
success = false,
message = "User not logged in.",
redirect = true
};
return(Ok(jsonData3));
}
}
public IEnumerable<string> CheckUpdate(string version)
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.UpgradePackages
.Where(p => p.FileVersion.CompareTo(version) > 0)
.Select(p => p.FileVersion)
.ToList();
}
catch (Exception ex)
{
throw ex;
}
}
}
public bool DeletePackage(int pid)
{
using (FFDBContext context = new FFDBContext())
{
try
{
var package = context.UpgradePackages.SingleOrDefault(p=>p.Id == pid);
if (package != null) {
context.UpgradePackages.Remove(package);
context.SaveChanges();
return true;
}
return false;
}
catch (Exception ex)
{
throw ex;
}
}
}
public bool AddRouteInformationItems(IEnumerable<RouteInformationItem> rlist)
{
using (FFDBContext context = new FFDBContext())
{
try
{
foreach (RouteInformationItem item in rlist)
{
context.RouteItems.Add(item);
}
context.SaveChanges();
return true;
}
catch (Exception ex)
{
Logger.Error("向数据库中添加新纪录失败", ex);
return false;
}
}
}
public IEnumerable<Company> GetAllCompanies()
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.Companies.ToList();
}
catch (Exception ex)
{
Logger.Error("获取所有货代公司失败", ex);
return null;
}
}
}
public RegisterCode SoftwareIsRegistered(string machineCode)
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.RegisterCodes
.Include(ri => ri.Company) //预先加载
.Where(rc => rc.MachineCode.Trim().Equals(machineCode)).SingleOrDefault();
}
catch (Exception ex)
{
Logger.Error("判断机器码是否在数据库中已经注册失败", ex);
return null;
}
}
}
public bool ImportRouteInformationItems(IEnumerable<RouteInformationItem> importlist)
{
// insert列表
IList<RouteInformationItem> insertlist = importlist.Where(ri => ri.Id == 0).ToList();
// update列表
IList<RouteInformationItem> updatelist = importlist.Where(ri => ri.Id != 0).ToList();
using (FFDBContext context = new FFDBContext())
{
//设置的时间是300秒，即五分钟
context.Database.CommandTimeout = 300;
DbTransaction tran = null;
try
{
using (DbConnection conn = ((IObjectContextAdapter)context).ObjectContext.Connection)
{
conn.Open();
using (tran = conn.BeginTransaction())
{
// insert
foreach (RouteInformationItem item in insertlist)
{
item.CreateDate = DateTime.Now;
item.UpdateDate = DateTime.Now;
item.IsDeleted = false;
context.RouteItems.Add(item);
}
// 要更新的所有记录ID
int[] idInFiles = importlist.Where(ri => ri.Id != 0).Select(ri => ri.Id).ToArray();
// 数据库中要更新的数据
IEnumerable<RouteInformationItem> db_updatelist = context.RouteItems.Where(ri => idInFiles.Contains(ri.Id)).ToList();
foreach (RouteInformationItem item in updatelist)
{
RouteInformationItem dbitem = db_updatelist.Single(ri => ri.Id == item.Id);
dbitem.ShipName = item.ShipName;
dbitem.StartPort = item.StartPort;
dbitem.DestinationPort = item.DestinationPort;
dbitem.StartDay = item.StartDay;
dbitem.Price_20GP = item.Price_20GP;
dbitem.Price_40GP = item.Price_40GP;
dbitem.Price_40HQ = item.Price_40HQ;
dbitem.Nonstop = item.Nonstop;
dbitem.SailDayLength = item.SailDayLength;
dbitem.ValidDate = item.ValidDate;
dbitem.Remarks = item.Remarks;
dbitem.IsSingleContainer = item.IsSingleContainer;
dbitem.UpdateDate = DateTime.Now;
}
// 参与重新导入的所有企业ID
int[] companyIDs = importlist.Select(ri => ri.CompanyId).Distinct().ToArray<int>();
// 数据库中现存的所有要参与重新导入企业的旧的信息
int[] oldDestinationIDs = context.RouteItems.Where(ri => companyIDs.Contains<int>(ri.CompanyId)).Select(ri => ri.Id).ToArray<int>();
// 要删除的 数据库ID
int[] deleteIDs = oldDestinationIDs.Except(idInFiles).ToArray();
IEnumerable<RouteInformationItem> deletelist = context.RouteItems.Where(ri => deleteIDs.Contains(ri.Id)).ToList();
// delete
foreach (RouteInformationItem item in deletelist)
{
// context.RouteItems.Remove(item);
item.IsDeleted = true;
item.DeleteDate = DateTime.Now;
}
context.SaveChanges();
// 提交事务
tran.Commit();
}
}
return true;
}
catch (Exception ex)
{
// 回滚事务
tran.Rollback();
Logger.Error("批量更新数据失败", ex);
return false;
}
}
}
public bool AddUpgradePackage(UpgradePackage entity)
{
using (FFDBContext context = new FFDBContext())
{
try
{
context.UpgradePackages.Add(entity);
context.SaveChanges();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
}
public int GetUpgradePackageCount()
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.UpgradePackages.Count();
}
catch (Exception ex)
{
throw ex;
}
}
}
public UpgradePackage GetUpdate(string version)
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.UpgradePackages.Where(p => p.FileVersion.Equals(version)).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
}
public IList<RouteInformationItem> GetRouteInformationItems(string shipName, string startPort, string destinationPort, bool? isSingleContainer)
{
using (FFDBContext context = new FFDBContext())
{
try
{
var rlist = context.RouteItems
.Include(ri => ri.Company) //预先加载
.Where(ri => !ri.IsDeleted);
// 船名
if (!string.IsNullOrEmpty(shipName))
{
//importlist = importlist.WhereOrLike(ri => ri.ShipName, new string[] { shipName });
rlist = rlist.Where(ri => ri.ShipName.Contains(shipName));
}
// 起始港
if (!string.IsNullOrEmpty(startPort))
{
//importlist = importlist.WhereOrLike(ri => ri.StartPort, new string[] { startPort });
rlist = rlist.Where(ri => ri.StartPort.Contains(startPort));
}
// 目的港
if (!string.IsNullOrEmpty(destinationPort))
{
//importlist = importlist.WhereOrLike(ri => ri.DestinationPort, new string[] { destinationPort });
rlist = rlist.Where(ri => ri.DestinationPort.Contains(destinationPort));
}
// 整柜/拼箱
if (isSingleContainer.HasValue && isSingleContainer.Value)
{
rlist = rlist.Where(ri => ri.IsSingleContainer == (int)IsSingleContainerValues.Yes);
}
else if (isSingleContainer.HasValue && !isSingleContainer.Value)
{
rlist = rlist.Where(ri => ri.IsSingleContainer == (int)IsSingleContainerValues.No);
}
return rlist
.ToList();
}
catch (Exception ex)
{
Logger.Error("模糊查询失败", ex);
return null;
}
}
}
public IList<RouteInformationItem> GetRouteInformationItems(int? companyId)
{
using (FFDBContext context = new FFDBContext())
{
try
{
var query = context.RouteItems
.Include(ri => ri.Company) //预先加载
.Where(ri => !ri.IsDeleted);
if (companyId.HasValue)
{
query = query.Where(ri => ri.CompanyId == companyId.Value);
}
return query.ToList();
}
catch (Exception ex)
{
Logger.Error("根据货代公司ID检索记录失败", ex);
return null;
}
}
}
public bool AssociatMachineAndRegCode(string machineCode, string regcode, int companyId)
{
using (FFDBContext context = new FFDBContext())
{
try
{
RegisterCode entity = context.RegisterCodes.Where(rc => rc.MachineCode.ToLower().Equals(machineCode.ToLower())).FirstOrDefault();
if (entity != null)
{
entity.RegCode = regcode;
entity.CompanyId = companyId;
entity.State = (int)RegCodeStates.Actived;
context.SaveChanges();
return true;
}
return false;
}
catch (Exception ex)
{
Logger.Error("服务端关联公司ID和机器码失败", ex);
return false;
}
}
}
public IEnumerable<UpgradePackage> GetUpgradePackages()
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.UpgradePackages.ToList();
}
catch (Exception ex)
{
throw ex;
}
}
}
public bool ExistedEntity(string machineCode)
{
using (FFDBContext context = new FFDBContext())
{
try
{
return context.RegisterCodes.Where(rc => rc.MachineCode.Equals(machineCode)).Count() > 0 ? true : false;
}
catch (Exception ex)
{
Logger.Error("根据机器码判断是否已存在失败", ex);
return true;
}
}
}