6098/Cowain.Bake.BLL/MesDataService.cs


using Cowain.Bake.Common.Enums;
using Cowain.Bake.Model;
using Cowain.Bake.Model.Entity;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
using System.Linq;
using Unity;

namespace Cowain.Bake.BLL
{
    public class MesDataService : ServiceBase
    {
        List<sbyte> flags = new List<sbyte>();
        public MesDataService(IUnityContainer unityContainer) : base(unityContainer)
        {
            flags.Add((sbyte)EMesUpLoadStatus.Wait);
            flags.Add((sbyte)EMesUpLoadStatus.Fail);
        }
        public int ModifySendFlag(long id, sbyte sendFlag)
        {
            using (var Context = new BakingEntities())
            {
                var model = Context.Set<TMesData>().Where(x => x.Id == id).FirstOrDefault();

                if (null == model)
                {
                    return 0;
                }

                model.SendFlag = sendFlag;
                Context.Entry(model).State = System.Data.Entity.EntityState.Modified;
                return Context.SaveChanges();
            }
        }

        /*
         * 条件	            执行时间（有索引）	扫描行数
         * col = 3	        0.03 ms	            1 次随机 I/O
         * col NOT IN (1,2)	120 ms	            980 万次逻辑读
         */
        public List<TMesData> GetUpLoadData()
        {
            using (var Context = new BakingEntities())
            {
                return Context.Set<TMesData>().Where(x => flags.Contains(x.SendFlag)).OrderByDescending(x => x.Id).Take(5).ToList();
                //return Context.Set<TMesData>().Where(x => x.SendFlag != (sbyte)EMesUpLoadStatus.Success).OrderBy(x => x.Id).Take(5).ToList();
            }
        }

        public int Insert(string commandType, string content, EMesLogClass mesType)
        {
            TMesData model = new TMesData()
            {
                CommandType = commandType,
                Content = content,
                SendFlag = 0,
                MsgType = (sbyte)mesType
            };

            return Insert<TMesData>(model);
            
        }
        public int Update(TMesData data)
        {
            using (var Context = new BakingEntities())
            {
                Context.Entry(data).State = EntityState.Modified;
                return Context.SaveChanges();
            }
        }

        public TMesData Query(int msgId)
        {
            string first = @"SELECT * FROM TMesData td WHERE JSON_CONTAINS(td.Content, '{""MsgNO"":";   //其中特殊字符（如转义字符）不会被解释
            string sql = first + msgId + @"}');";
            using (var Context = new BakingEntities())
            {
                return Context.Database.SqlQuery<TMesData>(sql).FirstOrDefault();
            }
        }

        public List<MesDataEntity> GetMesDataCellState(string batteryCode, DateTime startDateTime, DateTime EndDateTime)
        {
            string sql = $"SELECT *,Content->>'$.Info.Cells' BatteryCode FROM TMesData WHERE CreateTime > '{startDateTime}' and  CreateTime < '{EndDateTime}' and JSON_EXTRACT(Content, '$.Info.CellNo') = '{batteryCode}'";
            using (var Context = new BakingEntities())
            {
                return Context.Database.SqlQuery<MesDataEntity>(sql).ToList();
            }
        }

        public List<MesDataEntity> GetMesDataBakingOutput(string batteryCode, DateTime startDateTime, DateTime EndDateTime)
        {
            string sql = $"SELECT *,Content->>'$.Info.Cells' BatteryCode FROM TMesData WHERE CreateTime > '{startDateTime}' and  CreateTime < '{EndDateTime}'" + "and JSON_CONTAINS(Content,'\\{\"CellNo\":\"" + batteryCode + "\"\\}','$.Info.Cells')";
            using (var Context = new BakingEntities())
            {
                return Context.Database.SqlQuery<MesDataEntity>(sql).ToList();
            }
        }

        public List<MesDataEntity> GetMesDataList(EMesLogClass msgType, DateTime dateTime1, DateTime dateTime2)
        {
            string sql = $@"SELECT *,Content->>'$.Info.Cells' BatteryCode FROM TMesData td  WHERE ('{dateTime1}'< td.CreateTime and td.CreateTime < '{dateTime2}') AND  MsgType = {(sbyte)msgType}";
            using (var Context = new BakingEntities())
            {
                var MesData = Context.Database.SqlQuery<MesDataEntity>(sql).ToList();
                return MesData;
            }
        }
    }

}