everlight.eap/backend/DllEapDal/MacModelDal.cs

using AutoMapper;
using Cksoft.Data;
using Cksoft.Unity;
using DllEapEntity;
using DllEapEntity.Dtos;
using DllEapEntity.ExportDtos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace DllEapDal
{
    public class MacModelDal
    {
        private IDatabase CurrDb = null;
        private IDatabase SecDb = null;
        private IMapper _mapper;
        public MacModelDal(IDatabase db)
        {
            CurrDb = db;
        }

        public MacModelDal(IDatabase db, IMapper mapper)
        {
            CurrDb = db;
            _mapper = mapper;
        }

        public MacModelDal(IDatabase db, IDatabase secDb)
        {
            CurrDb = db;
            SecDb = secDb;
        }
        private MacModel IUMacModel(MacModel mst, string usercode, ref string errorinfo)
        {
            try
            {
                int result = 0;
                int id = mst.ID;
                if (mst.EntityStatusID == 1)
                {
                    //mst.RecCode = usercode;
                    //mst.RecTime = DateTime.Now;
                    //mst.ModCode = usercode;
                    //mst.ModTime = DateTime.Now;
                    //mst.ID = Guid.NewGuid().ToString();
                    result = CurrDb.InsertFor(mst, usercode);
                    if (result < 0)
                    {
                        return null;
                    }
                    object objid = CurrDb.FindObject("select @@IDENTITY");
                    if (objid.ToString() == "")
                    {
                        return null;
                    }
                    id = int.Parse(objid.ToString());
                }
                else
                {
                    //mst.ModCode = usercode;
                    //mst.ModTime = DateTime.Now;
                    result = CurrDb.UpdateFor(mst, usercode);
                    if (result < 0)
                    {
                        return null;
                    }
                }
                mst = CurrDb.FindEntityFor<MacModel>(id);
                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }
        public MacModel IUMacModel(MacModel mst, List<MMSecDetail> details, string usercode, ref string errorinfo)
        {
            try
            {
                mst = IUMacModel(mst, usercode, ref errorinfo);
                if (mst == null)
                    return null;
                int result = IUMMSecDetail(mst, details, usercode, ref errorinfo);
                if (result < 0)
                    return null;
                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }
        private int IUMMSecDetail(MacModel mst, List<MMSecDetail> details, string usercode, ref string errorinfo)
        {
            try
            {
                List<MMSecDetail> templists = details.Where(t => t.EntityStatusID < 0).ToList();
                int result = CurrDb.DeleteForEntity<MMSecDetail>(templists);
                if (result < 0)
                    return -1;
                templists = details.Where(t => t.EntityStatusID == 1).ToList();
                foreach (var item in templists)
                    item.PreID = mst.ID;
                result = CurrDb.InsertFor<MMSecDetail>(templists, usercode);
                if (result < 0)
                    return -1;
                templists = details.Where(t => t.EntityStatusID == 2).ToList();
                result = CurrDb.UpdateFor<MMSecDetail>(templists, usercode);
                if (result < 0)
                    return -1;
                return 1;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return -1;
            }

        }
        public IEnumerable<SecMacModel> Get()
        {
            return CurrDb.FindList<SecMacModel>("select * from MacModel");
        }

        public IEnumerable<SecMacModel> GetSecMacModels()
        {
            string sql = $"select a.ID,a.Fname,a.FCode,c.PCode as TPCode from MacModel a "
                + "left join Machine b on a.id=b.mmodeid "
                + "left join MacTProcess c on b.id=c.MacId";
            return CurrDb.FindList<SecMacModel>(sql);
        }

        public IEnumerable<MacModel> GetEapMacModels()
        {
            string sql = @"select a.Fname,a.FCode,c.PCode as TPCode,a.id 
                         from MacModel a
                         left
                         join Machine b on a.id = b.MModeID
                         left
                         join MacTProcess c on b.id = c.MacId";
            return CurrDb.FindList<MacModel>(sql);
        }

        #region Web
        public IEnumerable<MacModel> Get(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            var pros = CurrDb.FindListForCondition<MacModel>($" {filter} order by {sort} {order} limit {start - 1},{length}", ref errorinfo);
            return pros;
        }

        public int GetCount(string filter)
        {
            string errorinfo = string.Empty;
            //string sql = $"select count(1) from ProgramRule a where 1=1 {filter}";
            var entities = CurrDb.FindListForCondition<MacModel>(filter, ref errorinfo);
            if (entities != null)
            {
                return entities.Count();
            }
            return 0;
        }

        public MacModel Get(int id)
        {
            var pro = CurrDb.FindEntityFor<MacModel>(id);
            return pro;
        }

        public IEnumerable<MacModel> GetAllMacmodels(string filter, ref string errorinfo)
        {
            return CurrDb.FindListForCondition<MacModel>(filter, ref errorinfo);
        }

        public async Task<IEnumerable<MacModelExportDto>> GetExportData(string filter)
        {
            string erorinfo = string.Empty;
            var datas = await CurrDb.FindListForConditionAsync<MacModel>(filter, ref erorinfo);
            var models = _mapper.Map<IEnumerable<MacModelExportDto>>(datas).ToList();
            return models;
        }

        /// <summary>
        /// 添加角色并返回角色Id
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public int Add(MacModel pro, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<MacModel>($" and a.FCode='{pro.FCode}' ", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "机型已存在，请确认";
                return -1;
            }
            pro.RecCode = pro.ModCode = userCode;
            pro.RecTime = pro.ModTime = DateTime.Now;
            string sql = $"insert into MacModel(FCode,FName,Remark,RecCode,RecTime," +
                $"ModCode,ModTime,SoftVersion,SupplierID) values('{pro.FCode}','{pro.FName}'," +
                $"'{pro.Remark}','{pro.RecCode}','{pro.RecTime.ToString("yyyy-MM-dd")}'" +
                $",'{pro.ModCode}','{pro.ModTime.ToString("yyyy-MM-dd")}','{pro.SoftVersion}','{pro.SupplierID}');";
            sql += "select @@identity;";
            var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
            if (pro.MMSecDetails != null && pro.MMSecDetails.Count() > 0)
            {
                foreach (var item in pro.MMSecDetails)
                {
                    item.PreID = id;
                    if (CurrDb.InsertFor<MMSecDetail>(item, userCode) < 0)
                    {
                        return -1;
                    }
                }
            }
            return id;
        }

        public int Update(MacModel role, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<MacModel>($" and a.FCode='{role.FCode}' " +
                $"and a.ID<>{role.ID}", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同的机型，请确认";
                return -1;
            }
            if (role.MMSecDetails != null && role.MMSecDetails.Count() > 0)
            {
                var exists = CurrDb.FindListForCondition<MMSecDetail>($" and a.PreID={role.ID}", ref errorinfo);
                var updates = role.MMSecDetails.Where(c => exists.Any(f => f.SecFCode == c.SecFCode));
                var adds = role.MMSecDetails.Where(c => exists.All(f => f.SecFCode != c.SecFCode));
                var deletes = exists.Where(c => role.MMSecDetails.All(f => f.SecFCode != c.SecFCode));
                if (adds != null && adds.Count() > 0)
                {
                    foreach (var i in adds)
                    {
                        if (CurrDb.InsertFor(i, userCode) < 0)
                        {
                            errorinfo = "新增参数失败";
                            return -1;
                        }
                    }
                }
                if (updates != null && updates.Count() > 0)
                {
                    foreach (var i in updates)
                    {
                        if (CurrDb.UpdateFor(i, userCode) < 0)
                        {
                            errorinfo = "更新参数失败";
                            return -1;
                        }
                    }
                }
                if (deletes != null && deletes.Count() > 0)
                {
                    foreach (var item in deletes)
                    {
                        if (CurrDb.DeleteFor<MMSecDetail>(item.ID) < 0)
                        {
                            errorinfo = "删除参数失败";
                            return -1;
                        }
                    }
                }
            }
            else
            {
                string sql = string.Format($"delete from MMSecDetail where PreID='{role.ID}'");
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    errorinfo = "当前参数正在被使用，不能删除";
                    return -1;
                }
            }
            if (CurrDb.UpdateFor(role, userCode) < 0)
            {
                return -1;
            }
            return role.ID;
        }

        public int Delete(int id, ref string msg)
        {
            string sql = $"delete from modelsubdetail where preid in (select id from MMSecDetail where preid={id})";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除机型子参数失败";
                return -1;
            }
            sql = $"delete from eventreportdetail where preid in (select id from orderevent " +
                $"where MMSecDetailID in(select id from MMSecDetail where preid={id}))";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除事件报告参数失败";
                return -1;
            }
            sql = $"delete from orderevent where MMSecDetailID in(select id from MMSecDetail where preid={id})";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除该该机型下指令事件失败";
                return -1;
            }
            sql = $"delete from MMSecDetail where PreID='{id}'";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除参数信息时失败";
                return -1;
            }
            sql = $"delete from fileparams where ModelID={id}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除文件参数时失败";
                return -1;
            }
            if (CurrDb.DeleteFor<MacModel>(id) < 0)
            {
                msg = "删除失败";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        public MacModel AddOrGet(MacModel macModel, string userCode, ref string errorinfo)
        {
            var exist = CurrDb.FindListForCondition<MacModel>($" and a.FCode='{macModel.FCode.Trim()}'",
                ref errorinfo).FirstOrDefault();
            if (exist != null)
                return exist;
            if (CurrDb.InsertFor(macModel, userCode) < 0)
            {
                errorinfo = "插入数据库失败";
                return null;
            }
            exist = CurrDb.FindListForCondition<MacModel>($" and a.FCode='{macModel.FCode.Trim()}'",
                ref errorinfo).FirstOrDefault();
            return exist;
        }

        #endregion

        #region  同步Sec数据库
        public int AddToSec(MacModel model)
        {
            var secMacModel = new SecMacModel
            {
                ID = Guid.NewGuid().ToString(),
                FCode = model.FCode,
                FName = model.FName,
                TPCode = model.TPCode,
                reccode = model.RecCode,
                rectime = model.RecTime,
                modcode = model.ModCode,
                modtime = model.ModTime,
                Remark = model.Remark
            };
            return SecDb.Insert<SecMacModel>(secMacModel);
        }

        //public int UpdateToSec(MacModel model)
        //{
        //    var secMacmodel = SecDb.FindList<SecMacModel>($"select * from MacModel where FCode='{model.FCode}'").FirstOrDefault();
        //    if (secMacmodel == null)
        //    {
        //        return AddToSec(model);
        //    }
        //    else
        //    {
        //        secMacmodel.FCode = model.FCode;
        //        secMacmodel.FName = model.FName;
        //    }
        //}
        #endregion
    }
}