324 lines
14 KiB
C#
324 lines
14 KiB
C#
using Models;
|
|
using Newtonsoft.Json;
|
|
using SqlSugar;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Dynamic;
|
|
using System.Linq;
|
|
using System.Linq.Expressions;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace Service
|
|
{
|
|
public class OrderService : Repository<Models.Order>
|
|
{
|
|
public bool InsertNav(Models.Order model)
|
|
{
|
|
return base.AsSugarClient().InsertNav(model)
|
|
.Include(m => m.OrderHistoryList)
|
|
.Include(a => a.DefectInfoList)
|
|
.ExecuteCommand();
|
|
}
|
|
public bool DelNav(Models.Order model)
|
|
{
|
|
return base.AsSugarClient().DeleteNav(model)
|
|
.Include(a => a.OrderHistoryList)
|
|
.Include(a => a.DefectInfoList)//.ThenInclude(z1 => z1.RoomList) //插入2层 Root->ShoolA->RoomList
|
|
.ExecuteCommand();
|
|
}
|
|
//-----------------------
|
|
public List<ExpandoObject> GetList(string tablename, string fields, string domain, string orderby)
|
|
{
|
|
int totalCount=0;
|
|
return GetList(tablename,fields, domain, orderby, 0, 0, ref totalCount);
|
|
}
|
|
public List<ExpandoObject> GetList(string tablename,string fields, string domain, string orderby, int pageNum, int pageSize, ref int totalCount)
|
|
{
|
|
var sql = base.AsSugarClient().Queryable(tablename,"");
|
|
if (!string.IsNullOrWhiteSpace(domain))
|
|
sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
|
|
if (!string.IsNullOrWhiteSpace(fields) && fields!="*")
|
|
sql = sql.Select(fields);
|
|
if (!string.IsNullOrWhiteSpace(orderby))
|
|
sql = sql.OrderBy(orderby);
|
|
else
|
|
sql = sql.OrderBy("id desc");
|
|
|
|
if(pageNum==0 && pageSize == 0)
|
|
return sql.ToList();
|
|
else
|
|
return sql.ToPageList(pageNum, pageSize, ref totalCount);
|
|
}
|
|
public int DeleteList(string tablename, string domain)
|
|
{
|
|
var where = base.Context.Utilities.JsonToConditionalModels(domain);
|
|
return base.AsSugarClient().Deleteable<object>().AS(tablename).Where(where).ExecuteCommand();//批量
|
|
}
|
|
//public int DeleteIdList(string tablename, int[] ids)
|
|
//{
|
|
// return base.AsSugarClient().Deleteable<object>().AS(tablename).Where("Id in (@id) ", new { id = ids }).ExecuteCommand();//批量
|
|
//}
|
|
/// <summary>
|
|
/// 批量更新+新增
|
|
/// </summary>
|
|
/// <param name="tablename"></param>
|
|
/// <param name="lstDatas"></param>
|
|
/// <returns></returns>
|
|
public int UpdateableList(string tablename, string lstDatas,string[] updateColumns=null)
|
|
{
|
|
int result = 0;
|
|
var list = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(lstDatas);
|
|
base.AsSugarClient().Ado.BeginTran();
|
|
foreach (Dictionary<string, object> item in list)
|
|
{
|
|
if (!item.ContainsKey("Id") || Convert.ToInt32(item["Id"]) < 1)
|
|
result += base.AsSugarClient().Insertable(item).AS(tablename).ExecuteCommand();
|
|
else
|
|
{
|
|
if(updateColumns==null || updateColumns.Length==0)
|
|
result += base.AsSugarClient().Updateable(item).WhereColumns("Id").AS(tablename).ExecuteCommand();
|
|
else
|
|
result += base.AsSugarClient().Updateable(item).UpdateColumns(updateColumns).WhereColumns("Id").AS(tablename).ExecuteCommand();
|
|
}
|
|
}
|
|
base.AsSugarClient().Ado.CommitTran();
|
|
return result;
|
|
}
|
|
public List<Order> GetListNav(int pageNum, int pageSize, ref int totalCount, Expression<Func<Order, bool>> exp)
|
|
{
|
|
return base.AsSugarClient().Queryable<Order>()
|
|
.Includes(m => m.ProductInfo.ToList(x => new Product() { Name = x.Name, Spec = x.Spec }))
|
|
.Includes(m => m.StepInfo.ToList(x => new Step() { Name = x.Name }))
|
|
.Where(exp)
|
|
.ToPageList(pageNum, pageSize, ref totalCount);
|
|
}
|
|
public Order GetModelNav(string sn)
|
|
{
|
|
return base.AsSugarClient().Queryable<Order>()
|
|
.Includes(m => m.ProductInfo,x=>x.AssistStepInfo, info => info.ProcessList.OrderBy(x => x.Order).ToList()) //多级
|
|
.Includes(m => m.DefectInfoList)
|
|
.First(m => m.SN == sn);
|
|
}
|
|
//获取产品
|
|
public List<Product> GetProductList()
|
|
{
|
|
var db = base.Change<Product>();//切换仓储（新功能）
|
|
return db.AsSugarClient().Queryable<Product>()
|
|
.OrderBy(x => x.Name)
|
|
.ToList();
|
|
}
|
|
//获取流程工序
|
|
public List<Step> GetStepList()
|
|
{
|
|
var db = base.Change<Step>();//切换仓储（新功能）
|
|
return db.AsSugarClient().Queryable<Step>()
|
|
.OrderBy(x => x.Name)
|
|
.ToList();
|
|
}
|
|
|
|
//分页
|
|
//public List<Models.User> GetOrderPage(Expression<Func<Models.User, bool>> where, int pagesize, int pageindex)
|
|
//{
|
|
// return base.GetPageList(where, new SqlSugar.PageModel() { PageIndex = pageindex, PageSize = pagesize }); //使用自已的仓储方法
|
|
//}
|
|
|
|
//调用仓储扩展方法
|
|
//public List<Models.User> GetOrderByJson(string Json)
|
|
//{
|
|
// return base.CommQuery(Json);
|
|
//}
|
|
|
|
/// <summary>
|
|
/// 按产品和批次 统计 合格率
|
|
/// </summary>
|
|
/// <param name="domain"></param>
|
|
/// <param name="orderby"></param>
|
|
/// <param name="pageNum"></param>
|
|
/// <param name="pageSize"></param>
|
|
/// <param name="totalCount"></param>
|
|
/// <returns></returns>
|
|
public string GetReport_Qualified( string domain, string orderby,int pageNum, int pageSize, ref int totalCount)
|
|
{
|
|
var sql = base.AsSugarClient().Queryable<Order>()
|
|
.LeftJoin<Product>((a, b) => a.ProductId == b.Id);
|
|
if (!string.IsNullOrWhiteSpace(domain))
|
|
sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
|
|
var sql2 = sql.GroupBy((a,b) => new { a.ProductId,b.Name,b.Code, a.BatchId })
|
|
.Select((a,b) => new { a.ProductId,b.Name,b.Code, a.BatchId, Qualified = SqlFunc.AggregateSum(a.Qualified ? 1 : 0), Total = SqlFunc.AggregateSum(1) });
|
|
|
|
if (!string.IsNullOrWhiteSpace(orderby))
|
|
sql2 = sql2.OrderBy(orderby);
|
|
|
|
if (pageNum == 0 && pageSize == 0)
|
|
return sql2.ToJson();
|
|
else
|
|
return sql2.ToJsonPage(pageNum, pageSize, ref totalCount);
|
|
}
|
|
/// <summary>
|
|
/// 按日期统计合格率(日期段<=30,因只显示天)
|
|
/// </summary>
|
|
/// <param name="domain"></param>
|
|
/// <param name="orderby"></param>
|
|
/// <param name="pageNum"></param>
|
|
/// <param name="pageSize"></param>
|
|
/// <param name="totalCount"></param>
|
|
/// <returns></returns>
|
|
public string GetReport_Qualified_Date(string domain, string orderby, int pageNum, int pageSize, ref int totalCount)
|
|
{
|
|
var sql = base.AsSugarClient().Queryable<Order>();
|
|
if (!string.IsNullOrWhiteSpace(domain))
|
|
sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
|
|
var sql2 = sql.GroupBy((a) => new { CreateTime = a.CreateTime.Date }) //DATE_FORMAT(NOW(), '%Y-%m-%d')
|
|
.Select((a) => new { a.CreateTime.Date, Qualified = SqlFunc.AggregateSum(a.Qualified ? 1 : 0), Total = SqlFunc.AggregateSum(1) });
|
|
|
|
if (!string.IsNullOrWhiteSpace(orderby))
|
|
sql2 = sql2.OrderBy(orderby);
|
|
|
|
if (pageNum == 0 && pageSize == 0)
|
|
return sql2.ToJson();
|
|
else
|
|
return sql2.ToJsonPage(pageNum, pageSize, ref totalCount);
|
|
}
|
|
/// <summary>
|
|
/// 合格率[0,100,50,50]
|
|
/// </summary>
|
|
/// <param name="domain"></param>
|
|
/// <returns></returns>
|
|
public List<List<int>> GetReport_Qualified_Total()
|
|
{
|
|
List<List<int>> result =new List<List<int>>();
|
|
int liQualifiedCount, liTotal;
|
|
DateTime now = DateTime.Now.AddDays(-1);
|
|
//DateTime now = DateTime.Parse("2023-6-13");
|
|
//当天
|
|
var sql = base.AsSugarClient().Queryable<Order>();//只能执行一次，不能复用
|
|
liQualifiedCount = liTotal = 0;
|
|
var list=sql.Where(m=>m.CreateTime>=now.Date && !SqlFunc.EqualsNull(m.Qualified,null))
|
|
.GroupBy(m=>m.Qualified)
|
|
.Select((a) => new { a.Qualified, Count= SqlFunc.AggregateCount(a.Id) })
|
|
.ToList();
|
|
foreach(var item in list)
|
|
{
|
|
if (item.Qualified)
|
|
liQualifiedCount = item.Count;
|
|
liTotal += item.Count;
|
|
}
|
|
//result.Add(liTotal == 0 ? 0 : (liQualifiedCount * 100 / liTotal));
|
|
result.Add(new List<int>{ liTotal, liQualifiedCount });
|
|
//最近一月
|
|
sql = base.AsSugarClient().Queryable<Order>();
|
|
now = DateTime.Now.AddDays(-30);
|
|
liQualifiedCount = liTotal = 0;
|
|
list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
|
|
.GroupBy(m => m.Qualified)
|
|
.Select((a) => new { a.Qualified, Count = SqlFunc.AggregateCount(a.Id) })
|
|
.ToList();
|
|
foreach (var item in list)
|
|
{
|
|
if (item.Qualified)
|
|
liQualifiedCount = item.Count;
|
|
liTotal += item.Count;
|
|
}
|
|
//result.Add(liTotal == 0 ? 0 : (liQualifiedCount * 100 / liTotal));
|
|
result.Add(new List<int> { liTotal, liQualifiedCount });
|
|
//最近一季
|
|
sql = base.AsSugarClient().Queryable<Order>();
|
|
now = DateTime.Now.AddDays(-90);
|
|
liQualifiedCount = liTotal = 0;
|
|
list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
|
|
.GroupBy(m => m.Qualified)
|
|
.Select((a) => new { a.Qualified, Count = SqlFunc.AggregateCount(a.Id) })
|
|
.ToList();
|
|
foreach (var item in list)
|
|
{
|
|
if (item.Qualified)
|
|
liQualifiedCount = item.Count;
|
|
liTotal += item.Count;
|
|
}
|
|
//result.Add(liTotal == 0 ? 0 : (liQualifiedCount * 100 / liTotal));
|
|
result.Add(new List<int> { liTotal, liQualifiedCount });
|
|
//最近一年
|
|
sql = base.AsSugarClient().Queryable<Order>();
|
|
now = DateTime.Now.AddDays(-365);
|
|
liQualifiedCount = liTotal = 0;
|
|
list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
|
|
.GroupBy(m => m.Qualified)
|
|
.Select((a) => new { a.Qualified, Count = SqlFunc.AggregateCount(a.Id) })
|
|
.ToList();
|
|
foreach (var item in list)
|
|
{
|
|
if (item.Qualified)
|
|
liQualifiedCount = item.Count;
|
|
liTotal += item.Count;
|
|
}
|
|
//result.Add(liTotal == 0 ? 0 : (liQualifiedCount * 100 / liTotal));
|
|
result.Add(new List<int> { liTotal, liQualifiedCount });
|
|
|
|
return result;
|
|
}
|
|
/// <summary>
|
|
/// 按日期统计各缺陷数
|
|
/// </summary>
|
|
/// <param name="domain"></param>
|
|
/// <param name="orderby"></param>
|
|
/// <param name="pageNum"></param>
|
|
/// <param name="pageSize"></param>
|
|
/// <param name="totalCount"></param>
|
|
/// <returns></returns>
|
|
public string GetReport_Defects_Date(string domain, string orderby, int pageNum, int pageSize, ref int totalCount)
|
|
{
|
|
var sql = base.AsSugarClient().Queryable<Order>();
|
|
if (!string.IsNullOrWhiteSpace(domain))
|
|
sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
|
|
var sql2 = sql.GroupBy((a) => new { CreateTime = a.CreateTime.Date })
|
|
.Select((a) => new { a.CreateTime.Date,
|
|
Total = SqlFunc.AggregateSum(1),
|
|
堵孔 = SqlFunc.AggregateSum(a.DKCount),
|
|
脏污 = SqlFunc.AggregateSum(a.ZWCount),
|
|
钢丝异常 = SqlFunc.AggregateSum(a.GSYCCount),
|
|
纤维丝 = SqlFunc.AggregateSum(a.XWSCount),
|
|
缺口 = SqlFunc.AggregateSum(a.QKCount),
|
|
针孔 = SqlFunc.AggregateSum(a.ZKCount),
|
|
泡泡 = SqlFunc.AggregateSum(a.PPCount),
|
|
划伤 = SqlFunc.AggregateSum(a.HSCount),
|
|
压线 = SqlFunc.AggregateSum(a.YXCount),
|
|
斜边 = SqlFunc.AggregateSum(a.XBCount),
|
|
栅线 = SqlFunc.AggregateSum(a.SXCount),
|
|
});
|
|
|
|
if (!string.IsNullOrWhiteSpace(orderby))
|
|
sql2 = sql2.OrderBy(orderby);
|
|
|
|
if (pageNum == 0 && pageSize == 0)
|
|
return sql2.ToJson();
|
|
else
|
|
return sql2.ToJsonPage(pageNum, pageSize, ref totalCount);
|
|
}
|
|
/// <summary>
|
|
/// 缺陷总数
|
|
/// </summary>
|
|
/// <param name="domain"></param>
|
|
/// <returns></returns>
|
|
public string GetReport_Defects_Total(string domain)
|
|
{
|
|
var sql = base.AsSugarClient().Queryable<Order>();
|
|
if (!string.IsNullOrWhiteSpace(domain))
|
|
sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
|
|
return sql.Select((a) => new {
|
|
堵孔 = SqlFunc.AggregateSum(a.DKCount),
|
|
脏污 = SqlFunc.AggregateSum(a.ZWCount),
|
|
钢丝异常 = SqlFunc.AggregateSum(a.GSYCCount),
|
|
纤维丝 = SqlFunc.AggregateSum(a.XWSCount),
|
|
缺口 = SqlFunc.AggregateSum(a.QKCount),
|
|
针孔 = SqlFunc.AggregateSum(a.ZKCount),
|
|
泡泡 = SqlFunc.AggregateSum(a.PPCount),
|
|
划伤 = SqlFunc.AggregateSum(a.HSCount),
|
|
压线 = SqlFunc.AggregateSum(a.YXCount),
|
|
斜边 = SqlFunc.AggregateSum(a.XBCount),
|
|
栅线 = SqlFunc.AggregateSum(a.SXCount),
|
|
}).ToJson();
|
|
}
|
|
}
|
|
}
|