everlight.eap/backend/DllEapDal/ProgramMstDal.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Cksoft.Data;
using DllEapEntity.Dtos;
using DllEapEntity.Rms;
using DllEapEntity;
using MacTProcess = DllEapEntity.Rms.MacTProcess;

namespace DllEapDal
{
    public class ProgramMstDal
    {
        private IDatabase CurrDb;
        public ProgramMstDal(IDatabase db)
        {
            this.CurrDb = db;
        }

        public IEnumerable<ProgramMst> Get(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            var pros = CurrDb.FindListForCondition<ProgramMst>($" {filter} order by {sort} {order} limit {start - 1},{length}", ref errorinfo).ToList();
            // 加入参数信息
            if (pros != null && pros.Count() > 0)
            {
                for (var i = 0; i < pros.Count(); i++)
                {
                    var detail = CurrDb.FindListForCondition<ProgramParamsDetail>($" and a.PreID='{pros[i].ID}'", ref errorinfo);
                    if (detail == null)
                    {
                        detail = new List<ProgramParamsDetail>();
                    }
                    pros[i].ParamsDetail = detail;
                }
            }
            return pros;
        }

        public int GetCount(string filter)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            string errorinfo = string.Empty;
            var entities = CurrDb.FindListForCondition<ProgramMst>(filter, ref errorinfo);
            if (entities != null)
            {
                return entities.Count();
            }
            return 0;
        }

        public ProgramMst Get(int id)
        {
            string errorinfo = string.Empty;
            var pro = CurrDb.FindEntityFor<ProgramMst>(id);
            if (pro != null)
            {
                var detail = CurrDb.FindListForCondition<ProgramParamsDetail>($" and a.PreID='{pro.ID}'", ref errorinfo);
                pro.ParamsDetail = detail;
            }
            return pro;
        }

        /// <summary>
        /// 添加角色并返回角色Id
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public int Add(ProgramMst pro, string userCode, ref string errorinfo)
        {
            pro.RecCode = pro.ModCode = userCode;
            pro.RecTime = pro.ModTime = DateTime.Now;
            var entity = CurrDb.FindListForCondition<ProgramMst>($" and a.FName ='{pro.FName}' and ModelID='{pro.ModelID}' " +
                $"and ProcessCode='{pro.ProcessCode}' ", ref errorinfo).FirstOrDefault();
            if (entity != null)
            {
                errorinfo = "程序名已存在，请修改后重新添加";
                return -1;
            }
            string sql = $"insert into ProgramMst(FName,ModelID,ProcessCode,Remark,RecCode,RecTime,ModCode,ModTime) values('{pro.FName}','{pro.ModelID}'," +
                 $"'{pro.ProcessCode}','{pro.Remark}','{pro.RecCode}','{pro.RecTime.ToString("yyyy-MM-dd HH:mm:ss")}','{pro.ModCode}','{pro.ModTime.ToString("yyyy-MM-dd HH:mm:ss")}');";
            sql += "select @@identity;";
            var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
            if (pro.ParamsDetail != null && pro.ParamsDetail.Count() > 0)
            {
                var detailDal = new ProgramMstDetailDal(CurrDb);
                int index = 1;
                foreach (var item in pro.ParamsDetail)
                {
                    item.PreID = id;
                    item.FNum = index++;
                    if (detailDal.Add(item, userCode) < 0)
                    {
                        errorinfo = "新增程序参数失败,请联系管理员";
                        return -1;
                    }
                }
            }
            errorinfo = "";
            return id;
        }

        public int Update(ProgramMst role, string userCode, ref string error)
        {
            var entity = CurrDb.FindListForCondition<ProgramMst>($" and a.FName ='{role.FName}' and ModelID='{role.ModelID}' " +
                $"and ProcessCode='{role.ProcessCode}'  and a.id <> '{role.ID}'", ref error).FirstOrDefault();
            if (entity != null)
            {
                error = "程序名已存在，请确认";
                return -1;
            }
            var t = CurrDb.FindEntity<ProgramMst>(role.ID);
            role.ServerPath = t.ServerPath;
            var detail = role.ParamsDetail;
            if (detail != null && detail.Count() > 0)
            {
                string strsql = $"select * from programparamsdetail where PreId={role.ID}";
                var exists = CurrDb.FindList<ProgramParamsDetail>(strsql);
                var existsIds = exists?.Select(c => c.ID);
                var upIds = role.ParamsDetail.Select(c => c.ID);
                var deleteIds = existsIds.Where(c => !upIds.Contains(c));
                var sql = string.Empty;
                if (deleteIds != null && deleteIds.Count() > 0)
                {
                    sql = $"delete from PPSubDetail where preid in ({string.Join(",", deleteIds.Select(c => $"{c}"))})";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        error = "删除子参数失败,请联系管理员";
                        return -1;
                    }
                    sql = $" delete from programparamsdetail  where id in({string.Join(",", deleteIds.Select(c => $"{c}"))})";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        error = "删除原参数失败,请联系管理员";
                        return -1;
                    }
                }
                var updates = role.ParamsDetail.Where(c => existsIds.Contains(c.ID));
                if (updates != null && updates.Count() > 0)
                {
                    foreach (var item in updates)
                    {
                        if (CurrDb.UpdateFor<ProgramParamsDetail>(item, userCode) < 0)
                        {
                            error = "更新原有参数失败，请联系管理员";
                            return -1;
                        }
                    }
                }
                var adds = role.ParamsDetail.Where(c => !existsIds.Contains(c.ID));
                if (adds != null && adds.Count() > 0)
                {
                    var detailDal = new ProgramMstDetailDal(CurrDb);
                    var maxFnum = exists.Max(c => c.FNum) == null ? 0 : exists.Max(c => c.FNum);
                    foreach (var item in detail)
                    {
                        item.PreID = role.ID;
                        item.FNum = ++maxFnum;
                        if (detailDal.Add(item, userCode) < 0)
                        {
                            error = "新增程序参数失败，请联系管理员";
                            return -1;
                        }
                    }
                }
            }
            else
            {
                var str = $"select * from programparamsdetail where preid={role.ID}";
                var details = CurrDb.FindList<ProgramParamsDetail>(str);
                if (details != null && details.Count() > 0)
                {
                    var deleteFilter = string.Join(",", details.Select(c => c.ID));
                    str = $"delete from PPSubDetail where  preid in ({deleteFilter})";
                    CurrDb.ExecuteBySql(str);
                    str = $"delete from programparamsdetail where PreId={role.ID}";
                    CurrDb.ExecuteBySql(str);
                }
            }
            if (CurrDb.UpdateFor(role, userCode) > 0)
            {
                return role.ID;
            }
            return -1;
        }
        public IEnumerable<PPSubDetail> getppsubdetail(int id)
        {
            string sql = $"select * from ppsubdetail where preid in (select id from ProgramParamsDetail where preid={id})";
            return CurrDb.FindList<PPSubDetail>(sql);
        }
        public IEnumerable<ProgramParamsDetail> getProgramParamsDetail(int id)
        {
            string sql = $"select * from ProgramParamsDetail where PreId='{id}'";
            return CurrDb.FindList<ProgramParamsDetail>(sql);
        }
        public int Delete(int id, ref string msg)
        {
            // 判断是否有产品正在使用该程序
            string sql = $"select * from ProgramRule where ProgramMstID='{id}'";
            var rule = CurrDb.FindList<ProgramRule>(sql).FirstOrDefault();
            if (rule != null)
            {
                msg = "该程序已经设置规则，如需删除，请先删除对应的规则";
                return -1;
            }
            sql = $"delete from ppsubdetail where preid in (select id from ProgramParamsDetail where preid={id})";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除程序二级参数时失败，请联系管理员";
                return -1;
            }
            sql = $"delete from ProgramParamsDetail where PreId='{id}'";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除程序参数时出错，请联系管理员";
                return -1;
            }
            if (CurrDb.DeleteFor<ProgramMst>(id) < 0)
            {
                msg = "删除程序主表时出错，请联系管理员";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        public int UpdateFileParams(ProgramMst mst, string userCode, ref string error)
        {
            var details = mst.FileParamsDetail;
            var sql = $"delete from programfileparams where preid={mst.ID}";
            CurrDb.ExecuteBySql(sql);
            if (details != null && details.Count() > 0)
            {
                if (CurrDb.Insert(details) < 0)
                {
                    error = "插入文件参数失败";
                    return -1;
                }
            }
            return 1;
        }

        public IEnumerable<CascaderDto> GetCasMaModels()
        {
            string sql = "select * from tprocess";
            var processes = CurrDb.FindList<ProcessInfo>(sql);
            var macmodelDal = new MacModelDal(CurrDb);
            var macmodels = macmodelDal.GetEapMacModels();
            var list = new List<CascaderDto>();
            for (var i = 0; i < processes.Count(); i++)
            {
                var casDto = new CascaderDto()
                {

                    Label = processes.ElementAt(i).FCode,
                    Value = processes.ElementAt(i).FCode,
                    IsLeaf = false
                };
                var children = macmodels.Where(c => c.TPCode == casDto.Value);
                var cList = new List<CascaderDto>();
                if (children != null && children.Count() > 0)
                {
                    foreach (var item in children)
                    {
                        if (cList.Count == 0 || cList.FirstOrDefault(c => c.Value == item.FCode) == null)
                        {
                            cList.Add(new CascaderDto
                            {
                                Id = item.ID,
                                Label = item.FCode,
                                Value = item.FCode,
                                Children = null,
                                IsLeaf = true
                            });
                        }
                    }
                    casDto.Children = cList;
                }
                else
                {
                    casDto.IsLeaf = true;
                }
                list.Add(casDto);
            }
            return list;
        }

        public int IsExists(string name, ref string errorinfo)
        {
            var entity = CurrDb.FindListForCondition<ProgramMst>($" and a.FName ='{name}'", ref errorinfo).FirstOrDefault();
            if (entity != null)
                return 1;
            else
                return -1;
        }

        public IEnumerable<SelectDto<int>> GetParamsList(string macmodelCode)
        {
            string sql = $"select a.id as value,b.FName as label,b.FCode as Code from MMSecDetail a " +
                $"inner join Sec b on a.SecID=b.id " +
                $"inner join MacModel c on a.PreID=c.id " +
                $"where 1=1 and c.FCode='{macmodelCode}' and b.FType<>3";
            return CurrDb.FindList<SelectDto<int>>(sql);
        }

        public IEnumerable<SelectDto<int>> GetPrograms()
        {
            string sql = @"select a.id as value, concat(a.fname,' | ',a.ProcessCode,' | ',b.FCode) as label from ProgramMst a 
                          left join MacModel b on a.ModelId=b.id";
            return CurrDb.FindList<SelectDto<int>>(sql);
        }

        public int IUProgramMst(Machine mac, string programname, ref string errorinfo)
        {
            try
            {
                string tpcode = "";
                string condition = $" and a.MacID={mac.ID}";
                List<MacTProcess> mactps = CurrDb.FindListForCondition<MacTProcess>(condition, ref errorinfo).ToList();
                if (mactps.Count > 0)
                {
                    tpcode = mactps[0].PCode;
                }

                condition = $" and a.MacID={mac.ID}";
                List<MacTProcess> mactp = CurrDb.FindListForCondition<MacTProcess>(condition, ref errorinfo).ToList();
                if (mactp.Count > 0)
                {
                    tpcode = mactp[0].PCode;
                }
                condition = $" and a.ModelID={mac.MModeID} and a.FName='{programname}' and a.ProcessCode='{tpcode}'";
                List<ProgramMst> pmsts = CurrDb.FindListForCondition<ProgramMst>(condition, ref errorinfo).ToList();
                if (pmsts.Count > 0)
                    return pmsts[0].ID;
                ProgramMst entity = new ProgramMst();
                entity.ModelID = mac.MModeID;
                entity.ProcessCode = tpcode;
                entity.FName = programname;
                CurrDb.InsertFor<ProgramMst>(entity, "");
                return 1;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return -1;
            }
        }
    }
}