everlight.eap/backend/DllEapDal/OFILM/CmkDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapEntity;
using DllEapEntity.Dtos;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DllEapDal.OFILM
{
    public class CmkDal
    {
        private IDatabase CurrDb = null;
        public CmkDal(IDatabase db)
        {
            CurrDb = db;
        }
        public IEnumerable<Ofilmcmk> Get(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            try
            {
                var pros = CurrDb.FindListForCondition<Ofilmcmk>($" {filter} order by {sort} {order} limit {start - 1},{length}", ref errorinfo);
                return pros;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString() + ex.StackTrace);
                return null;
            }

        }

        public int GetCount(string filter)
        {
            string errorinfo = string.Empty;
            var entities = CurrDb.FindListForCondition<Ofilmcmk>(filter, ref errorinfo);
            if (entities != null)
            {
                return entities.Count();
            }
            return 0;
        }
        public Ofilmcmk Get(int id)
        {
            var pro = CurrDb.FindEntityFor<Ofilmcmk>(id);
            return pro;
        }

        /// <summary>
        /// 添加WorkingProcedure并返回Id
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public int Add(Ofilmcmk pro, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<Ofilmcmk>($"  and a.macID='{pro.macID}'  ", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同设备ID,请确认!";
                return -1;
            }
            CurrDb.InsertFor(pro, userCode);

            var sql = "select @@identity;";
            var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
            return id;
        }
        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="pros"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        //public int Adds(IEnumerable<Ofilmcmk> pros, string userCode, ref string errorinfo)
        //{
        //    try
        //    {
        //        var gpors = pros.GroupBy(l => new { l.Park, l.Floor, l.MachineType }).Select(p => new { p.Key.Park, p.Key.Floor, p.Key.MachineType });
        //        foreach (var item in gpors)
        //        {
        //            string sqldel = $"delete from WorkingProcedure where Park='{item.Park}' and Floor='{item.Floor}' and MachineType='{item.MachineType}'";
        //            CurrDb.ExecuteBySql(sqldel);
        //        }
        //        CurrDb.InsertFor<Ofilmcmk>(pros, userCode);

        //        var sql = "select @@identity;";
        //        var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
        //        return id;
        //    }
        //    catch (Exception e)
        //    {
        //        errorinfo = e.ToString();
        //        return -1;
        //    }

        //}
        /// <summary>
        /// 查询是否有重复数据
        /// </summary>
        /// <param name="pros"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int CheckSame(IEnumerable<Ofilmcmk> pros, ref Object errorinfo)
        {
            List<Ofilmcmk> workingProcedures = new List<Ofilmcmk>();
            var result = from r in pros
                         group r by new { r.macID } into g
                         where g.Count() > 1
                         select g;
            //遍历分组结果集
            foreach (var item in result)
            {
                foreach (Ofilmcmk u in item)
                {
                    workingProcedures.Add(u);
                }
            }

            if (workingProcedures.Count() >= 1)
            {
                var gpors = workingProcedures.GroupBy(l => new { l.macID }).Select(p => new { p.Key.macID });
                errorinfo = gpors;
                return -2;
            }

            return 1;
        }

        /// <summary>
        /// 待优化  存在删除后不能
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int Update(Ofilmcmk role, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<Ofilmcmk>($" and a.macID='{role.macID}'  " +
                $"and a.ID<>{role.ID}", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同MacID，请确认";
                return -1;
            }
            if (CurrDb.UpdateFor(role, userCode) < 0)
            {
                return -1;
            }
            return role.ID;
        }
        public IEnumerable<Ofilmcmk> getWorkingProcedure(int id)
        {
            var sql = $"select * from WorkingProcedure where ID={id}";
            return CurrDb.FindList<Ofilmcmk>(sql);
        }
        public int Delete(int id, ref string msg)
        {
            if (CurrDb.DeleteFor<Ofilmcmk>(id) < 0)
            {
                msg = "删除失败";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        public int DeleteAll(string filter, ref string msg)
        {
            string sql = $@" delete from Ofilmcmk where 1=1 {filter}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除失败";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        public IEnumerable<Ofilmcmk> GetWorkingProcedureExprort(string filter, string sortField, string sortOrder, int take)
        {
            var sql = $"select * from Ofilmcmk  limit {take}";
            IEnumerable<Ofilmcmk> dtos = CurrDb.FindList<Ofilmcmk>(sql).Take(take);
            if (dtos == null || dtos.Count() <= 0)
                return null;
            return dtos;
        }

        public IEnumerable<SelectDto<string>> GetMultipleClassSelects(string filter)
        {
            var sql = $@"select distinct classes value,classes label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleFloorSelects(string filter)
        {
            var sql = $@"select distinct floor value,floor label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleCustomerSelects(string filter)
        {
            var sql = $@"select distinct customer value,customer label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleMacIDSelects(string filter)
        {
            var sql = $@"select distinct macID value,macID label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultiplePositionSelects(string filter)
        {
            var sql = $@"select distinct position value,position label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
        public IEnumerable<SelectDto<string>> GetMultipleRecCodeSelects(string filter)
        {
            var sql = $@"select distinct RecCode value,RecCode label
                            FROM  ofilmcmk  ";
            var entities = CurrDb.FindList<SelectDto<string>>(sql);
            return entities;
        }
    }
}