using EnumType;
|
using IWareDataAccess.EF;
|
using IWareDataAccess.Entity.Base;
|
using IWareDataAccess.Entity.Order;
|
using IWareDataAccess.Helper;
|
using Models.Input;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace IWareDataAccess.Order.INORDER
|
{
|
public static class InOrderSqlFunc
|
{
|
/// <summary>
|
/// 搜索表
|
/// </summary>
|
/// <param name="keyValue"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
public static List<ORDER_INORDER> Search(InOrderWebEntity webEntity, int page, int onePageNum, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
var f = PredicateBuilder.True<ORDER_INORDER>();
|
//便利所有属性
|
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);
|
}
|
//执行时间
|
if (i.Name == "doTimeStart")
|
{
|
f = f.And(x => x.DOTIME > value);
|
}
|
if (i.Name == "doTimeEnd")
|
{
|
f = f.And(x => x.DOTIME < value);
|
}
|
}
|
}
|
else
|
{
|
if (v != null)
|
{
|
f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
}
|
}
|
}
|
List<ORDER_INORDER> list = edm.ORDER_INORDER.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_ORDER_INORDER> SearchView(Expression<Func<View_ORDER_INORDER, bool>> f, int page, int onePageNum, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
//var f = PredicateBuilder.True<View_ORDER_INORDER>();
|
////便利所有属性
|
//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);
|
// }
|
// //执行时间
|
// if (i.Name == "doTimeStart")
|
// {
|
// f = f.And(x => x.doTime > value);
|
// }
|
// if (i.Name == "doTimeEnd")
|
// {
|
// f = f.And(x => x.doTime < value);
|
// }
|
// }
|
// }
|
// else
|
// {
|
// if (v != null)
|
// {
|
// f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
// }
|
// }
|
//}
|
|
|
|
List<View_ORDER_INORDER> list = edm.View_ORDER_INORDER.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 int SearchViewNum(Expression<Func<View_ORDER_INORDER, bool>> f)
|
{
|
using (Model edm = new Model())
|
{
|
//var f = PredicateBuilder.True<View_ORDER_INORDER>();
|
////便利所有属性
|
//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);
|
// }
|
// //执行时间
|
// if (i.Name == "doTimeStart")
|
// {
|
// f = f.And(x => x.doTime > value);
|
// }
|
// if (i.Name == "doTimeEnd")
|
// {
|
// f = f.And(x => x.doTime < value);
|
// }
|
// }
|
// }
|
// else
|
// {
|
// if (v != null)
|
// {
|
// f = f.And(x => Object.Equals(Helper.Helper.GetFieldValueByName(x, i.Name), v));
|
// }
|
// }
|
//}
|
int num = edm.View_ORDER_INORDER.Where(f.Compile()).Count();
|
|
return num;
|
}
|
}
|
|
/// <summary>
|
/// 添加修改入库计划
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool AddOrUpdate(InOrderWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
|
ORDER_INORDER order = new ORDER_INORDER();
|
order = edm.ORDER_INORDER.FirstOrDefault(x => x.INORDERCODE == webEntity.inOrderCode);
|
if (order == null)
|
{
|
order = new ORDER_INORDER();
|
order.CreateTime = DateTime.Now;
|
//order.ORDERSTATUS ="未完成";
|
}
|
|
order.INORDERCODE = webEntity.inOrderCode;
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME == webEntity.itemName);
|
if (item != null)
|
{
|
//msg = "无此零件";
|
//return false;
|
order.ITEMID = item.ID;
|
}
|
//order.BASE_ITEM = item;
|
|
order.UPDATETIME = DateTime.Now;
|
order.ORDERTYPE = webEntity.orderType ?? order.ORDERTYPE;
|
order.TOTALNUM = webEntity.totalNum ?? order.TOTALNUM;
|
//校验已绑任务记录与当前入库单是否匹配
|
var taskRecordList = edm.TASK_RECORD.Where(x => x.INORDERID == order.ID && x.ITEMID != order.ITEMID);
|
foreach (var i in taskRecordList)
|
{
|
i.INORDERID = null;//不匹配的自动去除
|
}
|
//查询任务统计数量
|
//order.DONENUM = webEntity.doneNum ?? order.DONENUM;
|
var taskRecord = edm.TASK_RECORD.Where(x => x.INORDERID == order.ID && x.ITEMID == order.ITEMID).ToList();
|
int doneNum = taskRecord.Sum(x => x.ITEMCOUNT);
|
order.DONENUM = doneNum;
|
|
order.UNLINENUM = webEntity.unLineNum ?? order.UNLINENUM;
|
|
order.ORDERSTATUS = webEntity.orderStatus ?? order.ORDERSTATUS;
|
|
order.AllFinishedNum = (order.DONENUM ?? 0) + (order.UNLINENUM ?? 0);//总完工数
|
|
order.IsSatisfyAudit = false;
|
//通过数量变化状态
|
if (order.TOTALNUM <= order.AllFinishedNum)
|
{
|
//order.ORDERSTATUS = "完工";
|
order.IsSatisfyAudit = true;
|
}
|
else if (0 < order.AllFinishedNum)
|
{
|
//order.ORDERSTATUS = "执行中";
|
}
|
else
|
{
|
var changeStatus = InOrderStatusEnum.新建;
|
order.OrderStatusId = (int)changeStatus;
|
order.ORDERSTATUS = changeStatus.ToString();
|
}
|
|
SYS_USER user = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == webEntity.userName);
|
if (user != null)
|
{
|
order.CREATORID = user.ID;
|
}
|
|
|
order.ISFINISH = webEntity.isFinsish ?? order.ISFINISH;
|
order.DOTIME = webEntity.doTime ?? order.DOTIME;
|
order.ENABLE = webEntity.enable ?? order.ENABLE;
|
if (item != null)
|
{
|
order.TOTALWEIGHT = item.WEIGHT * order.TOTALNUM ?? order.TOTALWEIGHT;
|
}
|
order.CLASSNO = webEntity.classNo ?? order.CLASSNO;
|
//order.STATUS = webEntity.status ?? order.STATUS;
|
SYS_USER sender = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == webEntity.senderName);
|
if (sender != null)
|
{
|
order.SENDERID = sender.ID;
|
}
|
SYS_USER checker = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == webEntity.checkerName);
|
if (checker != null)
|
{
|
order.CHECKERID = checker.ID;
|
}
|
|
edm.ORDER_INORDER.AddOrUpdateExtension(order);
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 修改入库计划 状态
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool UpdateStatus(InOrderAuditInput input, string type, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
|
ORDER_INORDER order = new ORDER_INORDER();
|
order = edm.ORDER_INORDER.FirstOrDefault(x => x.INORDERCODE == input.InOrderCode);
|
InOrderStatusEnum changeStatus = default(InOrderStatusEnum);
|
InOrderStatusEnum orderStatus = (InOrderStatusEnum)Enum.Parse(typeof(InOrderStatusEnum), order.ORDERSTATUS);
|
switch (type)
|
{
|
case "提交审核":
|
if (orderStatus != InOrderStatusEnum.新建 && orderStatus != InOrderStatusEnum.驳回)
|
{
|
msg = "状态必须是'新建'或'驳回'才能提交审核！";
|
return false;
|
}
|
if (order.AllFinishedNum == null || Convert.ToInt32(order.AllFinishedNum) <= 0)
|
{
|
msg = "总完工数必须大于0才能提交审核！";
|
return false;
|
}
|
|
//修改
|
changeStatus = InOrderStatusEnum.焊装审核中;
|
order.OrderStatusId = (int)changeStatus;
|
order.ORDERSTATUS = changeStatus.ToString();
|
|
order.CHECKERID = input.AuditUserId;
|
order.CheckResult = "待审";
|
|
order.SENDERID = input.SendUserId;
|
order.SendTime = DateTime.Now;
|
|
break;
|
case "审核通过":
|
if (orderStatus != InOrderStatusEnum.焊装审核中)
|
{
|
msg = "状态必须是'焊装审核中'才能提交审核！";
|
return false;
|
}
|
//修改
|
changeStatus = InOrderStatusEnum.完工;
|
order.OrderStatusId = (int)changeStatus;
|
order.ORDERSTATUS = changeStatus.ToString();
|
order.CheckTime = DateTime.Now;
|
order.CheckResult = "通过";
|
break;
|
case "驳回":
|
if (orderStatus != InOrderStatusEnum.焊装审核中)
|
{
|
msg = "状态必须是'焊装审核中'才能提交审核！";
|
return false;
|
}
|
changeStatus = InOrderStatusEnum.驳回;
|
order.OrderStatusId = (int)changeStatus;
|
order.ORDERSTATUS = changeStatus.ToString();
|
order.CheckRemark = input.Remark;
|
order.CheckTime = DateTime.Now;
|
order.CheckResult = "驳回";
|
break;
|
default:
|
throw new Exception("不支持的类型");
|
}
|
|
|
order.UPDATETIME = DateTime.Now;
|
|
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
|
|
/// <summary>
|
/// 修改 线下交付数量
|
/// </summary>
|
/// <param name="role"></param>
|
/// <returns></returns>
|
public static bool UpdateUnlineNum(InOrderUnlineNumInput input, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
|
ORDER_INORDER order = new ORDER_INORDER();
|
order = edm.ORDER_INORDER.FirstOrDefault(x => x.INORDERCODE == input.InOrderCode);
|
|
//重新计算订单表的数量
|
order.UNLINENUM = input.Qty;
|
order.AllFinishedNum = (order.DONENUM ?? 0) + (order.UNLINENUM ?? 0);
|
|
order.UPDATETIME = DateTime.Now;
|
|
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="webDevice"></param>
|
/// <returns></returns>
|
public static bool Delete(InOrderWebEntity webEntity, out string msg)
|
{
|
msg = "";
|
using (Model edm = new Model())
|
{
|
ORDER_INORDER inOrder = edm.ORDER_INORDER.FirstOrDefault(x => x.INORDERCODE == webEntity.inOrderCode);
|
if (inOrder == null)
|
{
|
//无此角色
|
msg = "无此订单";
|
return false;
|
}
|
else
|
{
|
//edm.BASE_CONTAINER_VS_ITEM.Remove(cvi);
|
inOrder.ENABLE = 0;
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "保存失败";
|
return false;
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取编号
|
/// </summary>
|
/// <returns></returns>
|
public static string GetCode()
|
{
|
string month = DateTime.Now.Month.ToString("00");
|
string day = DateTime.Now.Day.ToString("00");
|
string hour = DateTime.Now.Hour.ToString("00");
|
string minute = DateTime.Now.Minute.ToString("00");
|
string second = DateTime.Now.Second.ToString("00");
|
string millSecond = DateTime.Now.Millisecond.ToString("0000");
|
string time = DateTime.Now.Year.ToString() + month + day + hour + minute + second + millSecond;
|
return "IO" + time;
|
}
|
|
/// <summary>
|
/// 导入入库单数据
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public static bool ImportInOrder(List<InOrderWebEntity> model, string userName, out string msg)
|
{
|
try
|
{
|
msg = "";
|
|
using (Model edm = new Model())
|
{
|
if (model != null && model.Count > 0)
|
{
|
int userid = 1;
|
SYS_USER user = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == userName);
|
if (user != null)
|
{
|
userid = user.ID;
|
}
|
List<ORDER_INORDER> oinlst = new List<ORDER_INORDER>();
|
foreach (InOrderWebEntity iow in model)
|
//foreach (InOrderExcelEntity iow in model)
|
{
|
ORDER_INORDER order = new ORDER_INORDER();
|
order = edm.ORDER_INORDER.FirstOrDefault(x => x.INORDERCODE == iow.inOrderCode);
|
if (order == null)
|
{
|
order = new ORDER_INORDER();
|
//order.ORDERSTATUS ="未完成";
|
order.CreateTime = DateTime.Now;
|
}
|
order.INORDERCODE = iow.inOrderCode;
|
BASE_ITEM item = edm.BASE_ITEM.FirstOrDefault(x => x.ITEMNAME == iow.itemName);
|
if (item == null)
|
{
|
msg = "无此零件";
|
return false;
|
}
|
order.BASE_ITEM = item;
|
order.UPDATETIME = DateTime.Now;
|
order.ORDERTYPE = iow.orderType ?? order.ORDERTYPE;
|
order.TOTALNUM = iow.totalNum ?? order.TOTALNUM;
|
//校验已绑任务记录与当前入库单是否匹配
|
var taskRecordList = edm.TASK_RECORD.Where(x => x.INORDERID == order.ID && x.ITEMID != order.ITEMID);
|
foreach (var i in taskRecordList)
|
{
|
i.INORDERID = null;//不匹配的自动去除
|
}
|
//查询任务统计数量
|
//order.DONENUM = webEntity.doneNum ?? order.DONENUM;
|
int doneNum = edm.TASK_RECORD.Where(x => x.INORDERID == order.ID && x.ITEMID == order.ITEMID).Sum(x => x.ITEMCOUNT);
|
order.DONENUM = doneNum;
|
|
order.UNLINENUM = iow.unLineNum ?? order.UNLINENUM;
|
|
order.ORDERSTATUS = iow.orderStatus ?? order.ORDERSTATUS;
|
|
//else
|
//{
|
// //通过数量变化状态
|
// if (order.TOTALNUM <= order.DONENUM + order.UNLINENUM)
|
// {
|
// order.ORDERSTATUS = "完工";
|
// }
|
// else if (0 < order.DONENUM + order.UNLINENUM)
|
// {
|
// order.ORDERSTATUS = "执行中";
|
// }
|
// else
|
// {
|
// order.ORDERSTATUS = "新建";
|
// }
|
//}
|
|
if (user != null)
|
{
|
order.CREATORID = user.ID;
|
}
|
order.ISFINISH = iow.isFinsish ?? order.ISFINISH;
|
order.DOTIME = iow.doTime ?? order.DOTIME;
|
order.ENABLE = iow.enable ?? order.ENABLE;
|
order.TOTALWEIGHT = item.WEIGHT * order.TOTALNUM ?? order.TOTALWEIGHT;
|
order.CLASSNO = iow.classNo ?? order.CLASSNO;
|
//order.STATUS = webEntity.status ?? order.STATUS;
|
SYS_USER sender = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == iow.senderName);
|
if (sender != null)
|
{
|
order.SENDERID = sender.ID;
|
}
|
SYS_USER checker = edm.SYS_USER.FirstOrDefault(x => x.USERNAME == iow.checkerName);
|
if (checker != null)
|
{
|
order.CHECKERID = checker.ID;
|
}
|
oinlst.Add(order);
|
}
|
if (string.IsNullOrEmpty(msg))
|
{
|
edm.ORDER_INORDER.AddRange(oinlst);
|
if (edm.SaveChanges() > 0)
|
{
|
return true;
|
}
|
else
|
{
|
msg = "导入失败";
|
return false;
|
}
|
}
|
else
|
{
|
return false;
|
}
|
|
}
|
else
|
{
|
msg = "导入失败";
|
return false;
|
}
|
|
|
}
|
}
|
catch (Exception ex)
|
{
|
msg = "导入失败";
|
return false;
|
}
|
}
|
|
/// <summary>
|
/// 导出入库单数据
|
/// </summary>
|
/// <returns></returns>
|
public static List<InOrderExcelEntity> ExportInOrder(Expression<Func<View_ORDER_INORDER, bool>> f, int page, int onePageNum, out string msg)
|
{
|
try
|
{
|
msg = "";
|
List<InOrderExcelEntity> iowlst = new List<InOrderExcelEntity>();
|
using (Model edm = new Model())
|
{
|
|
var list = SearchView(f, page, onePageNum, out msg);
|
|
if (list != null && list.Count > 0)
|
{
|
|
foreach (var inorder in list)
|
{
|
iowlst.Add(new InOrderExcelEntity()
|
{
|
itemCode = inorder.itemName,
|
inType = inorder.orderType,
|
itemNum = (int)inorder.totalNum,
|
createTime = (DateTime)inorder.updateTime
|
});
|
}
|
}
|
else
|
{
|
return null;
|
}
|
}
|
return iowlst;
|
}
|
|
catch (Exception ex)
|
{
|
msg = "导出失败！";
|
return null;
|
}
|
}
|
|
public static bool ImportExcel(string path, InOrderExcelEntity inord, out string msg)
|
{
|
using (Model dbModel = new Model())
|
{
|
try
|
{
|
msg = "";
|
var ds = ExcelHelper.FromExcelByEPPlus(path, "冲压计划导入模板", inord, out msg);
|
if (!string.IsNullOrEmpty(msg))
|
{
|
throw new Exception(msg);
|
}
|
|
var data = ds;
|
//数据保存
|
foreach (var i in data)
|
{
|
View_ORDER_INORDER newInorder = (View_ORDER_INORDER)i;
|
|
}
|
return string.IsNullOrEmpty(msg);
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
|
return false;
|
}
|
}
|
}
|
|
|
|
}
|
}
|
