AOI_V1/MaiMuAOI/Service/OrderService.cs

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 List<Order> GetListEx(string fields, string domain, string orderby, int pageNum, int pageSize, ref int totalCount)
        {
            var sql = base.AsSugarClient().Queryable<Order>()
                 .Includes(m => m.StepInfo)
                 .Includes(m => m.ProductInfo)
                 .Includes(m => m.OrderHistoryList);
            //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 bool UpdateableListEx(Order order)
        {
            return base.AsSugarClient().UpdateNav(order)
                .Include(m => m.OrderHistoryList)
                .ExecuteCommand();
        }

        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();
        }
    }
}