everlight.eap/backend/DllEapDal/FactoryRegionDal.cs

using Cksoft.Data;
using DllEapEntity;
using DllEapEntity.Dtos;
using log4net.Util;
using SharpCifs.Util.Sharpen;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;

namespace DllEapDal
{
    public class FactoryRegionDal
    {
        private IDatabase CurrDb = null;
        public FactoryRegionDal(IDatabase db)
        {
            CurrDb = db;
        }

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

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

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

        /// <summary>
        /// 添加角色并返回角色Id
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public int Add(FactoryRegion pro, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<FactoryRegion>($" and " +
                $"a.FName='{pro.FName}' and a.ParentId='{pro.ParentId}'", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同名称的区域，请确认";
                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="role"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int Update(FactoryRegion role, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<FactoryRegion>($" and a.FName='{role.FName}' " +
                $"and a.ID<>{role.Id} and a.ParentId={role.ParentId}", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同的名称的区域，请确认";
                return -1;
            }
            if (CurrDb.UpdateFor(role, userCode) < 0)
            {
                return -1;
            }
            return role.Id;
        }
        public IEnumerable<FactoryRegion> getFactoryRegion(int id)
        {
            var sql = $"select * from FactoryRegion where parentId={id}";
            return CurrDb.FindList<FactoryRegion>(sql);
        }
        public int Delete(int id, ref string msg)
        {
            var children = CurrDb.FindListForCondition<FactoryRegion>($" and a.ParentId={id}",
                ref msg);
            if (children != null && children.Count() > 0)
            {
                var sql = $"delete from FactoryRegion where parentId={id}";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    msg = "删除该节点下的子节点失败";
                    return -1;
                }
            }
            if (CurrDb.DeleteFor<FactoryRegion>(id) < 0)
            {
                msg = "删除失败";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        /// <summary>
        /// 获取区域树
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<FactoryRegionTreeDto> GetRegionTree(string filter, ref string errorinfo)
        {
            var datas = this.Get(1, 10000, "id", "asc", filter, errorinfo);
            var trees = new List<FactoryRegionTreeDto>();
            int level = 1;
            if (datas != null && datas.Count() > 0)
            {
                var minParentId = datas.Min(c => c.ParentId);
                var list = datas.Where(c => c.ParentId == minParentId);    // 根节点
                if (list != null && list.Count() > 0)
                {
                    trees = list.Select(c => new FactoryRegionTreeDto
                    {
                        Id = c.Id,
                        FCode = c.FCode,
                        FName = c.FName,
                        Key = c.Id.ToString(),
                        Name = c.FName,
                        IsUse = c.IsUse,
                        Level = level,
                        Rank = c.Rank,
                        Remark = c.Remark,
                        RecCode = c.RecCode,
                        RecTime = c.RecTime,
                        ModCode = c.ModCode,
                        ModTime = c.ModTime,
                        RecName = c.RecName,
                        ModName = c.ModName
                    }).ToList();
                    for (int i = 0; i < trees.Count; i++)
                    {
                        this.GetChildren(trees[i], datas, level);
                    }
                }
            }
            return trees;
        }

        /// <summary>
        /// 根据ParentID获取子节点
        /// </summary>
        /// <param name="parentId"></param>
        /// <param name="datas"></param>
        /// <returns></returns>
        private void GetChildren(FactoryRegionTreeDto entity,
            IEnumerable<FactoryRegion> datas, int level)
        {
            var list = datas.Where(c => c.ParentId == entity.Id);

            List<FactoryRegionTreeDto> children = null;
            if (list != null && list.Count() > 0)
            {
                children = list.Select(c => new FactoryRegionTreeDto
                {
                    Id = c.Id,
                    FCode = c.FCode,
                    FName = c.FName,
                    IsUse = c.IsUse,
                    Rank = c.Rank,
                    Level = ++level,
                    Key = c.Id.ToString(),
                    Name = c.FName,
                    Remark = c.Remark,
                    RecCode = c.RecCode,
                    RecTime = c.RecTime,
                    ModCode = c.ModCode,
                    ModTime = c.ModTime,
                    RecName = c.RecName,
                    ModName = c.ModName
                }).ToList();

                for (int i = 0; i < children.Count; i++)
                {
                    this.GetChildren(children[i], datas, level);
                }
                entity.Children = children;
            }
            else
            {
                entity.Children = null;
            }
        }

        public IEnumerable<TreeSelectDto> GetRegionTreeSelect(int id, ref string errorinfo)
        {
            string filter = " and a.IsUse=1 ";
            if (id > 0)
            {
                filter += $" and a.Id<>{id}";
            }
            var datas = this.Get(1, 10000, "id", "asc", filter, errorinfo);
            var trees = new List<TreeSelectDto>();
            if (datas != null && datas.Count() > 0)
            {
                var list = datas.Where(c => c.ParentId == 0);    // 根节点
                if (list != null && list.Count() > 0)
                {
                    trees = list.Select(c => new TreeSelectDto
                    {
                        Title = c.FName,
                        Key = c.Id,
                        Label = c.FName,
                        Value = c.Id
                    }).ToList();
                    for (int i = 0; i < trees.Count; i++)
                    {
                        this.GetSelectChildren(trees[i], datas);
                    }
                }
            }
            return trees;
        }

        private void GetSelectChildren(TreeSelectDto entity,
           IEnumerable<FactoryRegion> datas)
        {
            var list = datas.Where(c => c.ParentId == Convert.ToInt32(entity.Key));
            if (list == null || list.Count() <= 0)
            {
                entity.Children = null;
                entity.IsLeaf = true;
                return;
            }
            var children = list.Select(c => new TreeSelectDto
            {
                Title = c.FName,
                Key = c.Id,
                Label = c.FName,
                Value = c.Id,
                ParentId =Convert.ToInt32(entity.Key)
            }).ToList();

            entity.Children = children;
            entity.IsLeaf = false;
            for (var i = 0; i < children.Count; i++)
            {
                this.GetSelectChildren(children[i], datas);
            }
        }

        /// <summary>
        /// 根据区域Id获取区域的完整名称
        /// </summary>
        /// <param name="regionId"></param>
        /// <returns></returns>
        public string GetFullRegionName(int regionId, IEnumerable<FactoryRegion> allRegions)
        {
            string fullName = "";
            var region = allRegions.FirstOrDefault(f => f.Id == regionId);
            if (region == null)
            {
                return string.Empty;
            }
            fullName = $"/{region.FName}";
            return this.GetFName(region, fullName, allRegions).Substring(1);
        }

        private string GetFName(FactoryRegion region, string fullName, IEnumerable<FactoryRegion> allRegions)
        {
            var parent = allRegions.FirstOrDefault(c => c.Id == region.ParentId);
            if (parent != null)
            {
                fullName = "/" + parent.FName + fullName;
                return this.GetFName(parent, fullName, allRegions);
            }
            return fullName;
        }


        public FactoryRegion GetRoot(int regionId)
        {
            FactoryRegion region = CurrDb.FindEntityFor<FactoryRegion>(regionId);
            if (region.ParentId > 0)
            {
                return this.GetParent(region);
            }
            return region;
        }

        private FactoryRegion GetParent(FactoryRegion region)
        {
            var parentId = region.ParentId;
            var parent = CurrDb.FindEntityFor<FactoryRegion>(parentId);
            if (parent == null)
                return region;
            return this.GetParent(parent);
        }

        public IEnumerable<SelectDto<int?>> GetFactorySelect(ref string errorinfo)
        {
            var datas = CurrDb.FindListForCondition<FactoryRegion>($" and a.ParentId=0" +
                $" and a.IsUse=1", ref errorinfo);
            var selects = new List<SelectDto<int?>>();
            if (datas != null && datas.Count() > 0)
            {
                selects = datas.Select(c => new SelectDto<int?>
                {
                    Code = c.FCode,
                    Label = c.FName,
                    Value = c.Id
                }).ToList();
                selects.Insert(0, new SelectDto<int?>
                {
                    Label = "全部",
                    Value = null
                });
            }
            return selects;
        }
        public IEnumerable<SelectDto<int?>> GetFactorySelectMulti(ref string errorinfo)
        {
            var datas = CurrDb.FindListForCondition<FactoryRegion>($" and a.ParentId=0" +
                $" and a.IsUse=1", ref errorinfo);
            var selects = new List<SelectDto<int?>>();
            if (datas != null && datas.Count() > 0)
            {
                selects = datas.Select(c => new SelectDto<int?>
                {
                    Code = c.FCode,
                    Label = c.FName,
                    Value = c.Id
                }).ToList();
            }
            return selects;
        }

        public IEnumerable<SelectDto<int?>> GetChildrenSelect(int parentId, ref string errorinfo)
        {
            var sql = $@"select a.id as value,a.fname as label 
                         from factoryregion a
                         where a.parentID={parentId}";
            return CurrDb.FindList<SelectDto<int?>>(sql);
        }

        /// <summary>
        /// 根据选中的楼层车间获取该车间下所有的线体
        /// </summary>
        /// <param name="factoryId"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<SelectDto<int?>> GetLinesSelect(int[] floorIds)
        {
            var sql = $@"select a.id as value,CONCAT(b.fname,'/',a.FName) as label 
                         from factoryregion a
                         left join factoryregion b on a.parentid = b.id
                         where b.id in ({string.Join(",", floorIds)})";
            return CurrDb.FindList<SelectDto<int?>>(sql);
        }

        /// <summary>
        /// 获取机台位置下拉框数据源
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="type">1 园区  2厂房  3楼层 4线体</param>
        /// <returns></returns>
        public IEnumerable<SelectDto<int?>> GetRegionSelects(string filter, int type = 1)
        {
            string sql = string.Empty;
            switch (type)
            {
                case 1:
                    sql = $@"select a.id as value,a.fname as label from factoryregion 
                             where 1=1 {filter}";
                    break;
                case 2:
                    sql = $@"select CONCAT(b.fname,'/',a.FName) as label,a.id as value 
                          from factoryregion a 
                          left join factoryregion b on a.parentid = b.id 
                          where 1=1 {filter}";
                    break;
                case 3:
                    sql = $@"select CONCAT(c.fname,'/',b.fname,'/',a.FName) as label,a.id as value 
                          from factoryregion a 
                          left join factoryregion b on a.parentid = b.id 
                          left join factoryregion c on b.parentid = c.id 
                          where 1=1 {filter}";
                    break;
                default:
                    sql = $@"select a.id as value,a.fname as label from factoryregion 
                             where 1=1 {filter}";
                    break;
            }
            return CurrDb.FindList<SelectDto<int?>>(sql).OrderBy(c => c.Label);
        }

        //public FactoryRegion AddOrUpdate(string region, ref string errorinfo, params string[] parents)
        //{
        //    if (parents == null || parents.Count() == 0)
        //    {
        //        var factory = CurrDb.FindListForCondition<FactoryRegion>($" and a.fName='{region}'",
        //            ref errorinfo).FirstOrDefault();
        //        if (factory != null)
        //            return factory;
        //        var enity = new FactoryRegion { FCode= }
        //    }
        //}
    }
}