geboshi_V1/LeatherProject/Service/RecordsService.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 RecordsService : Repository<Models.Records>
    {
        //通用查询 ，所有表
        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.IsNullOrEmpty(domain))
                sql = sql.Where(base.Context.Utilities.JsonToConditionalModels(domain));
            if (!string.IsNullOrEmpty(fields) && fields!="*")
                sql = sql.Select(fields);
            if (!string.IsNullOrEmpty(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 Insert(string tablename, string jsonStr)
        {
            //使用JsonConvert.DeserializeObject方法将json字符串转换为Dictionary
            Dictionary<string, object> dic = JsonConvert.DeserializeObject<Dictionary<string, object>>(jsonStr); 
            return base.AsSugarClient().Insertable(dic).AS(tablename).ExecuteCommand();
        }
        public int DeleteIdList(string tablename,int[] ids)
        {
            return base.AsSugarClient().Deleteable<object>().AS(tablename).Where("Id in (@id) ", new { id = ids }).ExecuteCommand();//批量
        }
        public Records GetModelNav(int id)
        {
            return base.AsSugarClient().Queryable<Records>()
                //.Includes(m => m.ProductInfo.ToList(x => new Product() { Code = x.Code, Name = x.Name }))//,n=>n.ClassesInfo)
                //.Includes(m => m.ProductInfo)
                .Includes(m => m.DefectInfoList) 
                .First(m => m.Id == id);
        }
        public List<Records> GetListNav(int pageNum, int pageSize, ref int totalCount, Expression<Func<Records, bool>> exp)
        {
            return base.AsSugarClient().Queryable<Records>()
                //.Includes(m => m.ProductInfo.ToList(x => new Product() {Code=x.Code, Name = x.Name }))//,n=>n.ClassesInfo)
                .WhereIF(exp!=null,exp)
                .OrderByDescending(m=>m.CreateTime)
                .ToPageList(pageNum, pageSize, ref totalCount); 
        }
        public bool InsertNav(Models.Records model)
        {
            return base.AsSugarClient().InsertNav(model)
                .Include(m => m.DefectInfoList) 
                .ExecuteCommand(); 
        }
        public bool DelNav(Models.Records model)
        {
            return base.AsSugarClient().DeleteNav(model) 
                .Include(a => a.DefectInfoList)//.ThenInclude(z1 => z1.RoomList) //插入2层 Root->ShoolA->RoomList 
                .ExecuteCommand();
        }

        //获取产品
        public List<Product> GetProductList()
        { 
            var db = base.Change<Product>();//切换仓储（新功能） 
            return db.AsSugarClient().Queryable<Product>()
                .OrderBy(x => x.Code)
                .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>
        /// 按日期统计合格率(日期段<=30,因只显示天)
        /// </summary> 
        /// <returns></returns>
        public string GetReport_Qualified_Date(Expression<Func<Records, bool>> exp)
        {
            var sql = base.AsSugarClient().Queryable<Records>()
           .WhereIF(exp != null, exp);
            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);
             
            return sql2.ToJson(); 
        }
        /// <summary>
        /// 合格率[[当日总量，当日合格量],[当月总量，当月合格量],[当季总量，当季合格量],[当年总量，当年合格量]]
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public List<List<int>> GetReport_Qualified_Total(string barCode)
        {
            List<List<int>> result = new List<List<int>>();
            int liQualifiedCount, liTotal;
            DateTime now = DateTime.Now.AddDays(-1);
            //DateTime now = DateTime.Parse("2023-6-13"); 
            //当天
            //bool qualifiedGrade = 5;
            var sql = base.AsSugarClient().Queryable<Records>();//只能执行一次，不能复用
            liQualifiedCount = liTotal = 0;
            var list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
                .WhereIF(string.IsNullOrWhiteSpace(barCode), m => m.BarCode == barCode)
                .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<Records>();
            now = DateTime.Now.AddDays(-30);
            liQualifiedCount = liTotal = 0;
            list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
                .WhereIF(string.IsNullOrWhiteSpace(barCode), m => m.BarCode == barCode)
                .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<Records>();
            now = DateTime.Now.AddDays(-90);
            liQualifiedCount = liTotal = 0;
            list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
                .WhereIF(string.IsNullOrWhiteSpace(barCode), m => m.BarCode == barCode)
                .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<Records>();
            now = DateTime.Now.AddDays(-365);
            liQualifiedCount = liTotal = 0;
            list = sql.Where(m => m.CreateTime >= now.Date && !SqlFunc.EqualsNull(m.Qualified, null))
                .WhereIF(string.IsNullOrWhiteSpace(barCode), m => m.BarCode == barCode)
                .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> 
        /// <returns></returns>
        public string GetReport_Defects_Date(Expression<Func<Records, bool>> exp)
        {
            var sql = base.AsSugarClient().Queryable<Records>()
           .WhereIF(exp != null, exp);  
            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),
                });
             
             return sql2.ToJson(); 
        }
        /// <summary>
        /// 缺陷总数
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public string GetReport_Defects_Total(Expression<Func<Records, bool>> exp)
        {
            var sql = base.AsSugarClient().Queryable<Records>()
           .WhereIF(exp != null, exp);
            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();
        }
    }
}