using IWareDataAccess.EF;
|
using IWareDataAccess.Entity.Base;
|
using IWareDataAccess.Helper;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace IWareDataAccess.Base.ITEM
|
{
|
public static class ItemSqlFunc
|
{
|
/// <summary>
|
/// 搜索表
|
/// </summary>
|
/// <param name="keyValue"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
public static List<BASE_ITEM> Search(ItemWebEntity webEntity, int page, int onePageNum)
|
{
|
using (Model edm = new Model())
|
{
|
var f = PredicateBuilder.True<BASE_ITEM>();
|
//便利所有属性
|
Type type = webEntity.GetType();
|
foreach (var i in type.GetProperties())
|
{
|
object v = Helper.Helper.GetFieldValueByName(webEntity, i.Name);
|
if (i.PropertyType == typeof(String))
|
{
|
string value;
|
if (v != null)
|
{
|
value = v.ToString();
|
f = f.And(x => Helper.Helper.GetFieldValueByName(x, i.Name) != null ? Helper.Helper.GetFieldValueByName(x, i.Name).ToString().Contains(value) : false);
|
}
|
}
|
else if (i.PropertyType == typeof(DateTime?))
|
{
|
if (v != null)
|
{
|
DateTime value = DateTime.Parse(v.ToString());
|
if (i.Name == "updateTimeStart")
|
{
|
f = f.And(x => x.UPDATETIME > value);
|
}
|
if (i.Name == "updateTimeEnd")
|
{
|
f = f.And(x => x.UPDATETIME < value);
|
}
|
}
|
}
|
else
|
{
|
if (v != null)
|
{
|
f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
}
|
}
|
}
|
List<BASE_ITEM> list = edm.BASE_ITEM.Where(f.Compile()).Skip((page - 1) * onePageNum).Take(onePageNum).ToList();
|
|
return list;
|
}
|
}
|
|
/// <summary>
|
/// 搜索视图
|
/// </summary>
|
/// <param name="keyValue"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
public static List<View_BASE_ITEM> SearchView(ItemWebEntity webEntity, int page, int onePageNum)
|
{
|
using (Model edm = new Model())
|
{
|
var f = PredicateBuilder.True<View_BASE_ITEM>();
|
//便利所有属性
|
Type type = webEntity.GetType();
|
foreach (var i in type.GetProperties())
|
{
|
object v = Helper.Helper.GetFieldValueByName(webEntity, i.Name);
|
if (i.PropertyType == typeof(String))
|
{
|
string value;
|
if (v != null)
|
{
|
value = v.ToString();
|
f = f.And(x => Helper.Helper.GetFieldValueByName(x, i.Name) != null ? Helper.Helper.GetFieldValueByName(x, i.Name).ToString().Contains(value) : false);
|
}
|
}
|
else if (i.PropertyType == typeof(DateTime?))
|
{
|
if (v != null)
|
{
|
DateTime value = DateTime.Parse(v.ToString());
|
if (i.Name == "updateTimeStart")
|
{
|
f = f.And(x => x.updateTime > value);
|
}
|
if (i.Name == "updateTimeEnd")
|
{
|
f = f.And(x => x.updateTime < value);
|
}
|
}
|
}
|
else
|
{
|
if (v != null)
|
{
|
f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
}
|
}
|
}
|
|
List<View_BASE_ITEM> list = edm.View_BASE_ITEM.OrderByDescending(x => x.id).Where(f.Compile()).Skip((page - 1) * onePageNum).Take(onePageNum).ToList();
|
|
return list;
|
}
|
}
|
public static List<View_BASE_ITEM> SearchAllView(ItemWebEntity webEntity)
|
{
|
using (Model edm = new Model())
|
{
|
var f = PredicateBuilder.True<View_BASE_ITEM>();
|
//便利所有属性
|
Type type = webEntity.GetType();
|
foreach (var i in type.GetProperties())
|
{
|
object v = Helper.Helper.GetFieldValueByName(webEntity, i.Name);
|
if (i.PropertyType == typeof(String))
|
{
|
string value;
|
if (v != null)
|
{
|
value = v.ToString();
|
f = f.And(x => Helper.Helper.GetFieldValueByName(x, i.Name) != null ? Helper.Helper.GetFieldValueByName(x, i.Name).ToString().Contains(value) : false);
|
}
|
}
|
else if (i.PropertyType == typeof(DateTime?))
|
{
|
if (v != null)
|
{
|
DateTime value = DateTime.Parse(v.ToString());
|
if (i.Name == "updateTimeStart")
|
{
|
f = f.And(x => x.updateTime > value);
|
}
|
if (i.Name == "updateTimeEnd")
|
{
|
f = f.And(x => x.updateTime < value);
|
}
|
}
|
}
|
else
|
{
|
if (v != null)
|
{
|
f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
}
|
}
|
}
|
|
List<View_BASE_ITEM> list = edm.View_BASE_ITEM.OrderByDescending(x => x.id).Where(f.Compile()).ToList();
|
|
return list;
|
}
|
}
|
|
/// <summary>
|
/// 搜索视图数量
|
/// </summary>
|
/// <param name="keyValue"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
public static int SearchViewNum(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
var f = PredicateBuilder.True<View_BASE_ITEM>();
|
|
//便利所有属性
|
Type type = webEntity.GetType();
|
foreach (var i in type.GetProperties())
|
{
|
object v = Helper.Helper.GetFieldValueByName(webEntity, i.Name);
|
if (i.PropertyType == typeof(String))
|
{
|
string value;
|
if (v != null)
|
{
|
value = v.ToString();
|
f = f.And(x => Helper.Helper.GetFieldValueByName(x, i.Name) != null ? Helper.Helper.GetFieldValueByName(x, i.Name).ToString().Contains(value) : false);
|
}
|
}
|
else if (i.PropertyType == typeof(DateTime?))
|
{
|
if (v != null)
|
{
|
DateTime value = DateTime.Parse(v.ToString());
|
if (i.Name == "updateTimeStart")
|
{
|
f = f.And(x => x.updateTime > value);
|
}
|
if (i.Name == "updateTimeEnd")
|
{
|
f = f.And(x => x.updateTime < value);
|
}
|
}
|
}
|
else
|
{
|
if (v != null)
|
{
|
f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
}
|
}
|
}
|
|
int num = edm.View_BASE_ITEM.Where(f.Compile()).Count();
|
|
return num;
|
}
|
}
|
|
|
/// <summary>
|
/// 添加修改
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool AddOrUpdate(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//记录当前是否开启超限
|
int storageEnable = SearchStorageEnable(webEntity, out msg) ? 1 : 0;
|
|
//查询是否已存在此托盘
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME == webEntity.itemName);
|
if (item == null)
|
{
|
//无此库位，属于新建
|
item = new BASE_ITEM();
|
item.MAXSTORAGE = 0;
|
item.MINSTORAGE = 0;
|
item.OVERDUETIME = 0;
|
}
|
|
item.ITEMNAME = webEntity.itemName ?? item.ITEMNAME;
|
item.ITEMDES = webEntity.itemDes ?? item.ITEMDES;
|
item.ITEMTYPE = webEntity.itemType ?? item.ITEMTYPE;
|
item.WEIGHT = webEntity.weight ?? item.WEIGHT;
|
item.WEIGHTDIFFERENCE = webEntity.weightDifference ?? item.WEIGHTDIFFERENCE;
|
item.IMAGE = webEntity.image ?? item.IMAGE;
|
item.MAXNUM = webEntity.maxNum ?? item.MAXNUM;
|
item.ENABLE = webEntity.enable ?? item.ENABLE;
|
item.CREATELINE = webEntity.createLine ?? item.CREATELINE;
|
item.PRICE = webEntity.price ?? item.PRICE;
|
item.UNIT = webEntity.unit ?? item.UNIT;
|
item.USECONTAINERTYPE = webEntity.useContainerType ?? item.USECONTAINERTYPE;
|
item.MAXSTORAGE = webEntity.maxStorage ?? item.MAXSTORAGE;
|
item.MINSTORAGE = webEntity.minStorage ?? item.MINSTORAGE;
|
item.OVERDUETIME = webEntity.overDueTime ?? item.OVERDUETIME;
|
item.ISLOCK = webEntity.isLock ?? item.ISLOCK;
|
//item.STORAGEENABLE = webEntity.storageEnable ?? item.STORAGEENABLE;
|
item.STORAGEENABLE = storageEnable;
|
|
edm.BASE_ITEM.AddOrUpdateExtension(item);
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="webDevice"></param>
|
/// <returns></returns>
|
public static bool Delete(ItemWebEntity webDevice, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//查询是否已存在
|
if (!string.IsNullOrEmpty(webDevice.itemName))
|
{
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME == webDevice.itemName);
|
|
if (item == null)
|
{
|
//无此零件
|
msg = "无此零件";
|
return false;
|
}
|
else
|
{
|
return DeleteContainerRelevance(item);
|
}
|
}
|
else
|
{
|
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME ==null|| x.ITEMNAME.Contains(""));
|
|
if (item == null)
|
{
|
//无此零件
|
msg = "无此零件";
|
return false;
|
}
|
else
|
{
|
return DeleteContainerRelevance(item);
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 关联删除
|
/// </summary>
|
/// <param name="user"></param>
|
/// <returns></returns>
|
public static bool DeleteContainerRelevance(BASE_ITEM container)
|
{
|
using (Model edm = new Model())
|
{
|
//因已释放连接，故重新指向数据库对象进行索引
|
BASE_ITEM containerDelete = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME == container.ITEMNAME);
|
containerDelete.ENABLE = 0;
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
}
|
/// <summary>
|
///单价以及最大最小库存导入
|
/// </summary>
|
/// <param name="model"></param>
|
/// <param name="userName"></param>
|
/// <param name="msg"></param>
|
/// <returns></returns>
|
public static bool ImportItemInfo(List<ItemWebEntity> model, string userName, out string msg)
|
{
|
|
using (Model dbModel = new Model())
|
{
|
try
|
{
|
//记录当前是否开启超限
|
int storageEnable = SearchStorageEnable(model.FirstOrDefault(), out msg) ? 1 : 0;
|
|
msg = "";
|
List<BASE_ITEM> upitemlst = new List<BASE_ITEM>();
|
List<string> itemnamelst = model.Select(x => x.itemName).ToList();
|
List<BASE_ITEM> itemlst = dbModel.BASE_ITEM.Where(x => itemnamelst.Contains(x.ITEMNAME)).ToList();
|
model.ForEach(x =>
|
{
|
if (!string.IsNullOrEmpty(x.itemName))
|
{
|
var itemn = itemlst.FirstOrDefault(y => y.ITEMNAME == x.itemName);
|
if (itemn != null)
|
{
|
itemn.PRICE = x.price ?? itemn.PRICE;
|
itemn.OVERDUETIME = x.overDueTime ?? itemn.OVERDUETIME;
|
itemn.MAXSTORAGE = x.maxStorage ?? itemn.MAXSTORAGE;
|
itemn.MINSTORAGE = x.minStorage ?? itemn.MINSTORAGE;
|
itemn.STORAGEENABLE = storageEnable;
|
upitemlst.Add(itemn);
|
}
|
else
|
{
|
BASE_ITEM item = new BASE_ITEM();
|
item.ITEMNAME = x.itemName ?? item.ITEMNAME;
|
item.ITEMDES = x.itemDes ?? item.ITEMDES;
|
item.ITEMTYPE = x.itemType ?? item.ITEMTYPE;
|
item.WEIGHT = x.weight ?? 0;
|
item.WEIGHTDIFFERENCE = x.weightDifference ?? item.WEIGHTDIFFERENCE;
|
item.MAXNUM = x.maxNum ?? 0;
|
item.ENABLE = 1;
|
item.CREATELINE = x.createLine;
|
item.PRICE = x.price ?? 0;
|
item.UNIT = x.unit;
|
item.USECONTAINERTYPE = x.useContainerType;
|
item.MAXSTORAGE = x.maxStorage ?? 0;
|
item.MINSTORAGE = x.minStorage ?? 0;
|
item.OVERDUETIME = x.overDueTime ?? 0;
|
item.STORAGEENABLE = storageEnable;
|
dbModel.BASE_ITEM.Add(item);
|
}
|
}
|
|
});
|
dbModel.SaveChanges();
|
return string.IsNullOrEmpty(msg);
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
|
return false;
|
}
|
}
|
|
|
}
|
|
/// <summary>
|
/// 根据器具类型找对应所需可以装入的零件
|
/// </summary>
|
/// <param name="itemName"></param>
|
/// <param name="containerType"></param>
|
/// <param name="msg"></param>
|
/// <returns></returns>
|
public static List<View_BASE_ITEM> GetItemByContainerType(string itemName, string containerType, out string msg)
|
{
|
using (Model dbModel = new Model())
|
{
|
try
|
{
|
msg = "";
|
List<View_BASE_ITEM> upitemlst = new List<View_BASE_ITEM>();
|
if (!string.IsNullOrEmpty(containerType))
|
{
|
upitemlst = dbModel.View_BASE_ITEM.Where(x => x.useContainerType.Contains(containerType)).ToList();
|
}else
|
{
|
if (!string.IsNullOrEmpty(itemName))
|
{
|
upitemlst = dbModel.View_BASE_ITEM.OrderByDescending(x => x.id).Where(x => x.itemName.Contains(itemName)).Skip(0).Take(20).ToList();
|
}
|
else
|
{
|
upitemlst = dbModel.View_BASE_ITEM.OrderByDescending(x => x.id).Skip(0).Take(20).ToList();
|
}
|
|
}
|
|
return upitemlst;
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
|
return null;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 修改上下限功能是否开启
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool UpdateStorageEnable(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//查询是否已存在此托盘
|
List<BASE_ITEM> itemList = edm.BASE_ITEM.ToList();
|
foreach (var i in itemList)
|
{
|
i.STORAGEENABLE = webEntity.storageEnable;
|
}
|
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 搜索上下限功能是否开启
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool SearchStorageEnable(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//查询是否已存在此托盘
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.STORAGEENABLE == 1);
|
|
if (item!=null)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 批量修改上限
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool UpdateMaxStorage(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//查询是否已存在此托盘
|
List<BASE_ITEM> itemList = edm.BASE_ITEM.ToList();
|
foreach (var i in itemList)
|
{
|
i.MAXSTORAGE = webEntity.maxStorage;
|
}
|
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 批量修改下限
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool UpdateMinStorage(ItemWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//查询是否已存在此托盘
|
List<BASE_ITEM> itemList = edm.BASE_ITEM.ToList();
|
foreach (var i in itemList)
|
{
|
i.MINSTORAGE = webEntity.minStorage;
|
}
|
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
}
|
}
|
