using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using NPOI.HSSF.Record.Chart;
using Siger.Middlelayer.Common;
using Siger.Middlelayer.Common.Extensions;
using Siger.Middlelayer.Common.Helpers;
using Siger.Middlelayer.Common.ModuleEnum;
using Siger.Middlelayer.Repository.Data;
using Siger.Middlelayer.Repository.Entities;
using Siger.Middlelayer.Repository.Extensions;
using Siger.Middlelayer.Repository.Paged;
using Siger.Middlelayer.Repository.Repositories.Interface;
using Siger.Middlelayer.Repository.Response;
using Siger.Middlelayer.Utility.ExcelImport;
using Siger.Middlelayer.Utility.ImportEntities;

namespace Siger.Middlelayer.Repository.Repositories
{
    internal class SigerProjectMachineRepository : ApiConfigRepositoryBase<siger_project_machine>, ISigerProjectMachineRepository
    {
        private readonly ApiConfigDbContext _context;
        public SigerProjectMachineRepository(ApiConfigDbContext context) : base(context)
        {
            _context = context;
        }

        public IPagedCollectionResult<Machine> GetPagedMachines(int childTypeId, int parentMachinetype, string code, string factory,
            string made_date, int projectId, string attribution, IList<int> typeIds, int page, int pagesize)
        {
            var queryList = from q in _context.siger_project_machine
                            join t in _context.siger_project_machine_type on q.typeid equals t.id
                            where q.projectid == projectId && q.status == (int)RowState.Valid orderby q.sorting,q.id
                            select new Machine
                            {
                                id = q.id,
                                projectid = projectId,
                                title = q.title,
                                description = q.description,
                                brand = q.brand,
                                code = q.code,
                                current_status = q.current_status,
                                factory = q.factory,
                                factory_code = q.factory_code,
                                image = q.image,
                                ip = q.ip,
                                mid = q.mid,
                                model = q.model,
                                savedaycomment = q.savedaycomment,
                                savedays = q.savedays,
                                sorting = q.sorting,
                                time = q.time,
                                typeid = q.typeid,
                                ptypeid = t.parentid,
                                made_date = q.made_date,
                                tool_count = q.tool_count,
                                category = q.category,
                                attribution = q.attribution,
                                type_name = t.title,
                                remarks = q.remarks,
                                asset_code = q.asset_code
                            };

            Expression<Func<Machine, bool>> typeidExpression = q => true;
            if (childTypeId != 0)
            {
                typeidExpression = q => q.typeid == childTypeId;
            }
            Expression<Func<Machine, bool>> typesExpression = q => true;
            if (typeIds.Any())
            {
                typesExpression = q => typeIds.Contains(q.typeid);
            }
            Expression<Func<Machine, bool>> codeExpression = q => true;
            if (!string.IsNullOrEmpty(code))
            {
                codeExpression = q => q.code.Contains(code);
            }
            Expression<Func<Machine, bool>> factoryExpression = q => true;
            if (!string.IsNullOrEmpty(factory))
            {
                factoryExpression = q => q.factory.Contains(factory);
            }
            Expression<Func<Machine, bool>> madedateExpression = q => true;
            if (!string.IsNullOrEmpty(made_date))
            {
                madedateExpression = q => q.made_date.Contains(made_date);
            }
            Expression<Func<Machine, bool>> attributionExpression = q => true;
            if (!string.IsNullOrEmpty(attribution))
            {
                attributionExpression = q => q.attribution == attribution.ToInt();
            }

            var predicate = typeidExpression.And(codeExpression).And(factoryExpression).And(madedateExpression)
                .And(attributionExpression).And(typesExpression);

            var totalCount = queryList.Count(predicate);
            var entities = queryList.Where(predicate).Skip((page - 1) * pagesize).Take(pagesize).AsNoTracking().ToList();
            foreach (var item in entities)
            {
                var attribute = _context.siger_project_machine_attribution.FirstOrDefault(f => f.machine.Equals(item.id) && f.status == (int)RowState.Valid && f.projectid.Equals(projectId));
                if (attribute != null)
                {
                    item.section_id = attribute.station;
                }
            }
            return new PagedCollectionResult<Machine>(entities, totalCount);
        }

        public ExtendMachineInfo GetExtendMachineInfo(int id)
        {
            var query = from q in _context.siger_project_machine
                        join s in _context.siger_project_machine_attribution on q.id equals s.machine into se
                        from s in se.DefaultIfEmpty()
                        join sec in _context.siger_project_level_section on s.station equals sec.id into section
                        from sec in section.DefaultIfEmpty()
                        join m in _context.siger_project_machine_extend on q.id equals m.machine_id into mm
                        from m in mm.DefaultIfEmpty()
                        where q.id == id && q.status == (int)RowState.Valid && s.status == (int)RowState.Valid
                        select new ExtendMachineInfo
                        {
                            id = q.id,
                            title = q.title,
                            description = q.description,
                            brand = q.brand,
                            code = q.code,
                            current_status = q.current_status,
                            factory = q.factory,
                            factory_code = q.factory_code,
                            image = q.image,
                            ip = q.ip,
                            mid = q.mid,
                            model = q.model,
                            savedaycomment = q.savedaycomment,
                            savedays = q.savedays,
                            sorting = q.sorting,
                            section = sec != null ? sec.title : "",
                            section_id = sec != null ? sec.id : 0,
                            time = q.time,
                            typeid = q.typeid,
                            made_date = q.made_date,
                            attribute = q.attribute,//DNC新增字段
                            tool_count = q.tool_count,
                            machinelevel = q.machinelevel,
                            category = q.category,
                            machine_name = m.machine_name ?? "",
                            typical_application = m.typical_application ?? "",
                            inside_diameter = m.inside_diameter,
                            outside_diameter = m.outside_diameter ?? "",
                            radial_load = m.radial_load ?? "",
                            axial_load = m.axial_load ?? "",
                            pressure = m.pressure ?? "",
                            speed = m.speed ?? "",
                            temperature = m.temperature ?? "",
                            rotation = m.rotation ?? "",
                            sample_lubrication = m.sample_lubrication ?? "",
                            oil_hydraulic = m.oil_hydraulic ?? "",
                            oil_lubrication = m.oil_lubrication ?? "",
                            signal_monitor = m.signal_monitor ?? "",
                            position = m.position ?? "",
                            machine_size = m.machine_size ?? "",
                            machine_shortname = m.machine_shortname ?? "",
                            responsor = m.responsor ?? "",
                            attribution = q.attribution
                        };
            return query.FirstOrDefault();
        }

        public MachineInfo GetMachineInfo(int id)
        {
            var query = from q in _context.siger_project_machine
                        join t in _context.siger_project_machine_type on q.typeid equals t.id
                        where q.id == id && q.status == (int)RowState.Valid
                        select new MachineInfo
                        {
                            id = q.id,
                            title = q.title,
                            description = q.description,
                            brand = q.brand,
                            code = q.code,
                            current_status = q.current_status,
                            factory = q.factory,
                            factory_code = q.factory_code,
                            image = q.image,
                            ip = q.ip,
                            mid = q.mid,
                            model = q.model,
                            savedaycomment = q.savedaycomment,
                            savedays = q.savedays,
                            sorting = q.sorting,
                            time = q.time,
                            typeid = q.typeid,
                            ptypeid = t.parentid,
                            made_date = q.made_date,
                            attribute = q.attribute,//DNC新增字段
                            tool_count = q.tool_count,
                            machinelevel = q.machinelevel,
                            category = q.category,
                            attribution = q.attribution
                        };
            MachineInfo data = query.FirstOrDefault();
            if (data != null)
            {
                var attribute = _context.siger_project_machine_attribution.FirstOrDefault(f => f.machine.Equals(data.id) && f.status == (int)RowState.Valid);
                data.section_id = attribute?.station ?? 0;
                data.station = GetLevelSection(data.section_id, data.projectid)?.title ?? "";
            }
            return data;
        }

        public ResponseGetResponseExtendMachines GetExtendMachines(int id, int projectId)
        {
            var query = from q in _context.siger_project_machine
                        join ty in _context.siger_project_machine_type on q.typeid equals ty.id into type
                        from ty in type.DefaultIfEmpty()
                        join s in _context.siger_project_machine_attribution on q.id equals s.machine into se
                        from s in se.DefaultIfEmpty()
                        join sec in _context.siger_project_level_section on s.station equals sec.id into section
                        from sec in section.DefaultIfEmpty()
                        join m in _context.siger_project_machine_extend on q.id equals m.machine_id into mm
                        from m in mm.DefaultIfEmpty()
                        where q.id == id && q.status == (int)RowState.Valid && s.status == (int)RowState.Valid
                        select new ResponseGetResponseExtendMachines
                        {
                            id = q.id,
                            title = q.title,
                            description = q.description,
                            brand = q.brand,
                            code = q.code,
                            current_status = q.current_status,
                            factory = q.factory,
                            factory_code = q.factory_code,
                            image = q.image,
                            ip = q.ip,
                            model = q.model,
                            savedaycomment = q.savedaycomment,
                            savedays = q.savedays,
                            sorting = q.sorting,
                            lmachine = sec != null ? sec.title : "",
                            time = q.time,
                            typeid = q.typeid,
                            made_date = q.made_date,
                            typetitle = ty != null ? ty.title : "",
                            section_id = s.station,
                            machine_name = m.machine_name ?? "",
                            typical_application = m.typical_application ?? "",
                            inside_diameter = m.inside_diameter,
                            outside_diameter = m.outside_diameter ?? "",
                            radial_load = m.radial_load ?? "",
                            axial_load = m.axial_load ?? "",
                            pressure = m.pressure ?? "",
                            speed = m.speed ?? "",
                            temperature = m.temperature ?? "",
                            rotation = m.rotation ?? "",
                            sample_lubrication = m.sample_lubrication ?? "",
                            oil_hydraulic = m.oil_hydraulic ?? "",
                            oil_lubrication = m.oil_lubrication ?? "",
                            signal_monitor = m.signal_monitor ?? "",
                            position = m.position ?? "",
                            machine_size = m.machine_size ?? "",
                            machine_shortname = m.machine_shortname ?? "",
                            responsor = m.responsor ?? ""
                        };
            return query.FirstOrDefault();
        }

        public ResponseGetResponseMachines GetMachines(int id, int projectId)
        {
            var query = from q in _context.siger_project_machine
                        join ty in _context.siger_project_machine_type on q.typeid equals ty.id into type
                        from ty in type.DefaultIfEmpty()
                        where q.id == id && q.status == (int)RowState.Valid
                        select new ResponseGetResponseMachines
                        {
                            id = q.id,
                            title = q.title,
                            description = q.description,
                            brand = q.brand,
                            code = q.code,
                            current_status = q.current_status,
                            factory = q.factory,
                            factory_code = q.factory_code,
                            image = q.image,
                            ip = q.ip,
                            model = q.model,
                            savedaycomment = q.savedaycomment,
                            savedays = q.savedays,
                            sorting = q.sorting,
                            time = q.time,
                            typeid = q.typeid,
                            made_date = q.made_date,
                            typetitle = ty != null ? ty.title : "",
                            attribution = q.attribution
                        };
            ResponseGetResponseMachines data = query.FirstOrDefault();
            if (data != null)
            {
                var attribute = _context.siger_project_machine_attribution.FirstOrDefault(f => f.machine.Equals(data.id) && f.status == (int)RowState.Valid && f.projectid.Equals(projectId));
                data.section_id = attribute?.station ?? 0;
                data.station = _context.siger_project_level_section.FirstOrDefault(f => f.id.Equals(data.section_id) && f.status == (int)RowState.Valid)?.title ?? "";
                data.lmachine = data.station;
            }
            return data;
        }

        public CommonImportResult ImportMachines(IEnumerable<MachineList> machines, int mid, int projectid)
        {
            var errors = new List<string>();
            var rowIndex = 1;
            var typeIds = new List<int>();
            var newEntitys = new List<siger_project_machine>();
            var index = 0;
            foreach (var machineEntity in machines)
            {
                rowIndex++;

                //检查id
                var machine = _context.siger_project_machine.FirstOrDefault(q => q.id == machineEntity.ID.ToInt());
                if (machine != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.IdHasExist}");
                }

                //check machine attribute
                var attribute = machineEntity.MachineAttribute.ToInt();
                if (attribute <= 0 || attribute > 3)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.MachineAttributionInValid}");
                }

                //check parent machine type
                var typeid = 0;
                var parentType = _context.siger_project_machine_type.FirstOrDefault(q => q.title == machineEntity.ParentType
                    && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (parentType == null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.MachineTypeNotFound}");
                }
                else
                {
                    typeid = parentType.id;
                }

                //check child machine type
                if (!string.IsNullOrEmpty(machineEntity.ChildType))
                {
                    var childType = _context.siger_project_machine_type.FirstOrDefault(q => q.title == machineEntity.ChildType
                        && q.projectid == projectid && q.status == (int)RowState.Valid);
                    if (childType == null)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.MachineChildTypeNotFound}");
                    }
                    else
                    {
                        typeid = childType.id;
                    }
                }
                typeIds.Add(typeid);

                //check machine code exist or not
                var entity = _context.siger_project_machine.FirstOrDefault(q => q.code == machineEntity.Code
                    && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (entity != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.CodeHasExist}");
                }
                entity = _context.siger_project_machine.FirstOrDefault(q => q.id == machineEntity.ID.ToInt()
                                                                            && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (entity != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.IdHasExist}");
                }
                if (newEntitys.Exists(f=>f.code==machineEntity.Code))
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.CodeHasExist}");
                }
                if (newEntitys.Exists(f => f.id == machineEntity.ID.ToInt()))
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.IdHasExist}");
                }
                if (errors.Any())
                {
                    return new CommonImportResult(0, string.Join(";", errors));
                }
                newEntitys.Add(new siger_project_machine
                {
                    id = machineEntity.ID.ToInt(),
                    code = machineEntity.Code,
                    factory = machineEntity.Factory,
                    factory_code = machineEntity.FactoryCode,
                    model = machineEntity.Model,
                    made_date = machineEntity.MadeDate,
                    typeid = typeIds[index],
                    ip = "",
                    mid = mid,
                    projectid = projectid,
                    time = UnixTimeHelper.GetNow(),
                    title = machineEntity.Name,
                    status = (int)RowState.Valid,
                    attribute = 1,
                    attribution = machineEntity.MachineAttribute.ToInt(),
                    category = machineEntity.Category.ToInt() > 0 ? machineEntity.Category.ToInt() : (int)MachineCategory.NC,
                    machinelevel = machineEntity.MachineLevel.ToInt()
                });
                index++;
            }
          
            //var index = 0;
            //foreach (var machineEntity in machines)
            //{
            //    var machine = new siger_project_machine
            //    {
            //        id = machineEntity.ID.ToInt(),
            //        code = machineEntity.Code,
            //        factory = machineEntity.Factory,
            //        factory_code = machineEntity.FactoryCode,
            //        model = machineEntity.Model,
            //        made_date = machineEntity.MadeDate,
            //        typeid = typeIds[index],
            //        ip = "",
            //        mid = mid,
            //        projectid = projectid,
            //        time = UnixTimeHelper.GetNow(),
            //        title = machineEntity.Name,
            //        status = (int)RowState.Valid,
            //        attribute = 1,
            //        attribution = machineEntity.MachineAttribute.ToInt(),
            //        category = machineEntity.Category.ToInt() > 0 ? machineEntity.Category.ToInt() : (int)MachineCategory.NC,
            //        machinelevel = machineEntity.MachineLevel.ToInt()
            //    };
            //    _context.siger_project_machine.Add(machine);
            //    _context.SaveChanges();

            //    index++;
            //}

            _context.siger_project_machine.AddRange(newEntitys);
            _context.SaveChanges();

            return new CommonImportResult(1, "1");
        }

        public CommonImportResult ImportMachinesSkf(IEnumerable<MachineEntityList> machines, int mid, string ip, int projectid)
        {
            var errors = new List<string>();
            var rowIndex = 1;
            var sectionIds = new List<int>();
            var typeIds = new List<int>();
            foreach (var machineEntity in machines)
            {
                var sectionId = 0;
                rowIndex++;
                //check levels
                var title = machineEntity.Levels.Last();
                var levels = _context.siger_project_level_section.Where(q =>
                    q.title == title && q.projectid == projectid && q.status == (int)RowState.Valid);

                foreach (var level in levels)
                {
                    var sectionTitles = GetLevelSectionTitles(level.id, projectid);
                    if (sectionTitles.All(machineEntity.Levels.Contains) && sectionTitles.Count() == machineEntity.Levels.Count)
                    {
                        sectionId = level.id;
                        break;
                    }
                }
                if (sectionId == 0)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.LevelNotFound}");
                }
                else
                {
                    sectionIds.Add(sectionId);
                }

                //check parent machine type
                var typeid = 0;
                var parentType = _context.siger_project_machine_type.FirstOrDefault(q => q.title == machineEntity.ParentType
                    && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (parentType == null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.MachineTypeNotFound}");
                }
                else
                {
                    typeid = parentType.id;
                }

                //check child machine type
                if (!string.IsNullOrEmpty(machineEntity.ChildType))
                {
                    var childType = _context.siger_project_machine_type.FirstOrDefault(q => q.title == machineEntity.ChildType
                        && q.projectid == projectid && q.status == (int)RowState.Valid);
                    if (childType == null)
                    {
                        errors.Add($"{rowIndex},{(int)RequestEnum.MachineChildTypeNotFound}");
                    }
                    else
                    {
                        typeid = childType.id;
                    }
                }
                typeIds.Add(typeid);

                //check machine code exist or not
                var entity = _context.siger_project_machine.FirstOrDefault(q => q.code == machineEntity.Code
                    && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (entity != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.CodeHasExist}");
                }
                entity = _context.siger_project_machine.FirstOrDefault(q => q.id == machineEntity.ID.ToInt()
                                                                            && q.projectid == projectid && q.status == (int)RowState.Valid);
                if (entity != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.IdHasExist}");
                }

                //check section has mechine or not
                var sectionMachine = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                    q.station == sectionId
                    && q.status == (int)RowState.Valid);
                if (sectionMachine != null)
                {
                    errors.Add($"{rowIndex},{(int)RequestEnum.SectionMachineHasExist}");
                }

                if (errors.Any())
                {
                    return new CommonImportResult(0, string.Join(";", errors));
                }
            }

            var index = 0;
            foreach (var machineEntity in machines)
            {
                var machine = new siger_project_machine
                {
                    id = machineEntity.ID.ToInt(),
                    code = machineEntity.Code,
                    factory = machineEntity.Factory,
                    factory_code = machineEntity.FactoryCode,
                    model = machineEntity.Model,
                    made_date = machineEntity.MadeDate,
                    typeid = typeIds[index],
                    ip = ip,
                    mid = mid,
                    projectid = projectid,
                    time = UnixTimeHelper.GetNow(),
                    title = machineEntity.Name,
                    status = (int)RowState.Valid,
                    attribute = 1,
                    category = machineEntity.Category.ToInt() > 0 ? machineEntity.Category.ToInt() : (int)MachineCategory.NC,
                    machinelevel = 1
                };
                var extendMachine = new siger_project_machine_extend
                {
                    machine_id = machineEntity.ID.ToInt(),
                    machine_name = machineEntity.machine_name,
                    typical_application = machineEntity.typical_application,
                    inside_diameter = machineEntity.inside_diameter,
                    outside_diameter = machineEntity.outside_diameter,
                    radial_load = machineEntity.radial_load,
                    axial_load = machineEntity.axial_load,
                    pressure = machineEntity.pressure,
                    speed = machineEntity.speed,
                    temperature = machineEntity.temperature,
                    rotation = machineEntity.rotation,
                    sample_lubrication = machineEntity.sample_lubrication,
                    oil_hydraulic = machineEntity.oil_hydraulic,
                    oil_lubrication = machineEntity.oil_lubrication,
                    signal_monitor = machineEntity.signal_monitor,
                    position = machineEntity.position,
                    machine_size = machineEntity.machine_size,
                    machine_shortname = machineEntity.machine_shortname,
                    responsor = machineEntity.responsor
                };
                _context.siger_project_machine.Add(machine);
                _context.siger_project_machine_extend.Add(extendMachine);
                _context.SaveChanges();

                var levelSectionMachine = new siger_project_level_section_machine
                {
                    section_id = sectionIds[index],
                    machine_id = machine.id,
                    status = (int)RowState.Valid
                };
                _context.siger_project_level_section_machine.Add(levelSectionMachine);
                _context.SaveChanges();
                index++;
            }

            return new CommonImportResult(1, "1");
        }


        public IEnumerable<MachineTypes> GetMachineTypes(IEnumerable<int> machineIds, int projectId)
        {
            var query = from m in _context.siger_project_machine
                        join t in _context.siger_project_machine_type on m.typeid equals t.id
                        where m.projectid == projectId && t.projectid == projectId
                                                       && m.status == (int)RowState.Valid && t.status == (int)RowState.Valid
                                                       && machineIds.Contains(m.id)
                        select new MachineTypes
                        {
                            MachineId = m.id,
                            MachineType = t.title
                        };

            return query.AsEnumerable();
        }

        public int GetMahcineIdBySection(int sectionId)
        {
            var query = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.station == sectionId && q.status == (int)RowState.Valid && q.attribution == 1);
            if (query == null)
            {
                return 0;
            }
            return query.machine;
        }

        public IEnumerable<MachinSectionInfo> GetMachinSectionInfos(IEnumerable<int> machineIds, int projectId)
        {
            var query = from sm in _context.siger_project_machine_attribution
                        join ts in _context.siger_project_level_section on sm.station equals ts.id
                        join s in _context.siger_project_level_section on ts.parentid equals s.id
                        where sm.status == (int)RowState.Valid && ts.status == (int)RowState.Valid &&
                              s.status == (int)RowState.Valid && ts.projectid == projectId && sm.attribution == 1
                              && machineIds.Contains(sm.machine)
                        select new MachinSectionInfo
                        {
                            id = s.id,
                            sectionId = ts.id,
                            machineId = sm.machine,
                            section_name = s.title,
                            station_name = ts.title
                        };
            return query.AsEnumerable();
        }

        public IEnumerable<MachinStationInfo> GetMachinStationInfos(int projectId)
        {
            var query = from sm in _context.siger_project_machine_attribution
                        join ts in _context.siger_project_level_section on sm.station equals ts.id
                        join s in _context.siger_project_level_section on ts.parentid equals s.id
                        where sm.status == (int)RowState.Valid && ts.status == (int)RowState.Valid &&
                              s.status == (int)RowState.Valid && sm.attribution == 1
                              && ts.projectid == projectId
                        select new MachinStationInfo
                        {
                            machineId = sm.machine,
                            sectionId = ts.id,
                            section_name = ts.title,
                            station_Id = s.id,
                            station_name = s.title
                        };
            return query.AsEnumerable();
        }

        public IEnumerable<int> GetMachinIdsBySectionId(int sectionId, int typeId, int projectId)
        {
            var list = new List<int>();
            var query = GetSonLevelSections(sectionId, projectId);

            list.Add(sectionId);
            foreach (var section in query.ToList())
            {
                list.Add(section.id);
            }

            var querylist = from q in _context.siger_project_machine_attribution
                            join m in _context.siger_project_machine on q.machine equals m.id into ma
                            from m in ma.DefaultIfEmpty()
                            where list.Contains(q.station) && (typeId == 0 || m.typeid == typeId) && q.status == (int)RowState.Valid &&
                                  m.status == (int)RowState.Valid
                                  && m.projectid == projectId && q.attribution == 1
                            select m.id;

            return querylist.ToList();
        }

        public IEnumerable<ResponseGetUserCode> GetUserCodes(string keyword, int projectId)
        {
            //取出生产部门的人即可
            //取所有类型部门
            var userGroupIds = from s in _context.siger_project_section
                               join g in _context.siger_project_usergroup on s.id equals g.sectionid
                               where g.projectid == projectId && s.status == (int)RowState.Valid && g.status == (int)RowState.Valid
                                     //&& s.type == (int)SectionType.ProduceSection
                               select g.id.ToString();
            if (!userGroupIds.Any())
            {
                return new List<ResponseGetUserCode>();
            }

            var ids = userGroupIds.ToList();
            var query = _context.siger_project_user.Where(q =>
                q.projectid == projectId && q.status == (int)RowState.Valid && ids.Contains(q.usergroupid));
            if (!string.IsNullOrWhiteSpace(keyword))
            {
                query = query.Where(q => q.name.Contains(keyword) || q.work_code.Contains(keyword));
            }

            var response = new List<ResponseGetUserCode>();
            foreach (var sigerProjectUser in query)
            {
                response.Add(new ResponseGetUserCode
                {
                    id = sigerProjectUser.mid,
                    text = sigerProjectUser.name + " " + sigerProjectUser.work_code
                });
            }

            return response;
        }

        public ResponseIdName GetSectionByMachineId(int machineId, int projectId)
        {
            var query = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.machine == machineId && q.status == (int)RowState.Valid && q.attribution == 1);
            if (query == null)
            {
                return null;
            }

            var section = _context.siger_project_level_section.FirstOrDefault(q =>
                 q.projectid == projectId && q.status == (int)RowState.Valid
                                          && q.id == query.station);
            if (section == null)
            {
                return null;
            }

            return new ResponseIdName { id = section.id, name = section.title };
        }

        public ResponseIdName GetMachineBySectionId(int sectionId, int projectId)
        {
            var query = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.station == sectionId && q.status == (int)RowState.Valid && q.attribution == 1);
            if (query == null)
            {
                return null;
            }

            var machine = _context.siger_project_machine.FirstOrDefault(q =>
                q.projectid == projectId && q.status == (int)RowState.Valid
                                         && q.id == query.machine);
            if (machine == null)
            {
                return null;
            }

            return new ResponseIdName { id = machine.id, name = machine.title };
        }

        public IEnumerable<siger_project_level_section> GetLevelSectionsByLevelId(int levelId, int projectId)
        {
            return _context.siger_project_level_section.Where(q =>
                q.levelid == levelId && q.projectid == projectId && q.status == (int)RowState.Valid).ToList();
        }

        public IEnumerable<int> GetSectionIdsByMahcineIds(IEnumerable<int> machineIds, int projectId)
        {
            var query = from l in _context.siger_project_machine_attribution
                        join m in _context.siger_project_machine on l.machine equals m.id
                        where machineIds.Contains(l.machine) && l.status == (int)RowState.Valid &&
                              m.projectid == projectId && m.status == (int)RowState.Valid && l.attribution == 1
                        select l.station;
            return query.AsEnumerable();
        }

        public List<int> GetParentLevelSectionIds(int sectionId, int projectId)
        {
            var list = new List<int> { sectionId };
            var query = GetParentLevelSections(sectionId, projectId);

            foreach (var section in query.ToList().OrderBy(q => q.id))
            {
                list.Add(section.id);
            }

            return list;
        }

        public IEnumerable<MachinSectionInfo> GetNCMachinSectionInfos(IEnumerable<int> machineIds, int projectId)
        {
            var query = from sm in _context.siger_project_machine_attribution
                        join ts in _context.siger_project_level_section on sm.station equals ts.id
                        join s in _context.siger_project_level_section on ts.parentid equals s.id
                        join m in _context.siger_project_machine on sm.machine equals m.id
                        where sm.status == (int)RowState.Valid && ts.status == (int)RowState.Valid &&
                              s.status == (int)RowState.Valid && ts.projectid == projectId
                              && machineIds.Contains(sm.machine) && m.category == (int)MachineCategory.NC && sm.attribution == 1 orderby m.sorting 
                              orderby m.sorting,m.id
                        select new MachinSectionInfo
                        {
                            id = s.id,
                            sectionId = ts.id,
                            machineId = sm.machine,
                            section_name = s.title,
                            station_name = ts.title
                        };
            return query.AsEnumerable();
        }

        public IEnumerable<MachinStationInfo> GetNCMachinStationInfos(int projectId)
        {
            var query = from sm in _context.siger_project_machine_attribution
                        join ts in _context.siger_project_level_section on sm.station equals ts.id
                        join s in _context.siger_project_level_section on ts.parentid equals s.id
                        join m in _context.siger_project_machine on sm.machine equals m.id
                        where sm.status == (int)RowState.Valid && ts.status == (int)RowState.Valid &&
                              s.status == (int)RowState.Valid
                              && ts.projectid == projectId && m.category == (int)MachineCategory.NC && sm.attribution == 1
                        select new MachinStationInfo
                        {
                            machineId = sm.machine,
                            sectionId = ts.id,
                            section_name = ts.title,
                            station_Id = s.id,
                            station_name = s.title
                        };
            return query.AsEnumerable();
        }

        public IEnumerable<int> GetNCMachinIdsBySectionId(int sectionId, int typeId, int projectId, bool dashboardWorkshop=false)
        {
            var list = new List<int>();
            var query = GetSonLevelSections(sectionId, projectId);           
            list.Add(sectionId);
            foreach (var section in query.ToList())
            {
                list.Add(section.id);
            }
            //车间看板按照工位统计
            //if (dashboardWorkshop)
            //{
            //    return list;
            //}

            var querylist = from q in _context.siger_project_machine_attribution
                            join m in _context.siger_project_machine on q.machine equals m.id into ma
                            from m in ma.DefaultIfEmpty()
                            where list.Contains(q.station) && (typeId == 0 || m.typeid == typeId) && q.status == (int)RowState.Valid &&
                                  m.status == (int)RowState.Valid
                                  && m.projectid == projectId && m.category == (int)MachineCategory.NC && m.attribution == 1 orderby m.sorting,m.id
                            select m.id;

            return querylist.ToList();
        }

        public IEnumerable<int> GetNCSectionIdsByMahcineIds(IEnumerable<int> machineIds, int projectId)
        {
            var query = from l in _context.siger_project_machine_attribution
                        join m in _context.siger_project_machine on l.machine equals m.id
                        where machineIds.Contains(l.machine) && l.status == (int)RowState.Valid &&
                              m.projectid == projectId && m.status == (int)RowState.Valid && m.category == (int)MachineCategory.NC
                              && l.attribution == 1
                        select l.station;
            return query.AsEnumerable();
        }

        public ResponseIdName GetNCMachineBySectionId(int sectionId, int projectId)
        {
            var query = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.station == sectionId && q.status == (int)RowState.Valid && q.attribution == 1);
            if (query == null)
            {
                return null;
            }

            var machine = _context.siger_project_machine.FirstOrDefault(q =>
                q.projectid == projectId && q.status == (int)RowState.Valid
                                         && q.id == query.machine && q.category == (int)MachineCategory.NC);
            if (machine == null)
            {
                return null;
            }

            return new ResponseIdName { id = machine.id, name = machine.title };
        }

        public int GetNCMahcineIdBySection(int sectionId)
        {
            var query = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.station == sectionId && q.status == (int)RowState.Valid && q.attribution == 1);
            if (query == null)
            {
                return 0;
            }

            var machine = _context.siger_project_machine.FirstOrDefault(q => q.category == (int)MachineCategory.NC &&
                                                                             q.status == (int)RowState.Valid && q.id == query.machine);
            if (machine == null)
            {
                return 0;
            }

            return machine.id;
        }
        /// <summary>
        /// 获取设备信息
        /// </summary>
        /// <param name="section"></param>
        /// <param name="projectid"></param>
        /// <returns></returns>
        public List<siger_project_machine> GetMachineEntityBySection(int section, int projectid)
        {
            var sections = GetSonLevelSections(section, projectid).Select(s => s.id).ToList();
            sections.Add(section);
            var machines = _context.siger_project_machine_attribution.Where(f => f.projectid == projectid && f.status != 0 && sections.Contains(f.station)).AsNoTracking().ToList().Select(s => s.machine);
            return _context.siger_project_machine.Where(f => f.status != 0 && machines.Contains(f.id) && f.projectid == projectid).AsNoTracking().ToList();
        }

        public string GetLevelSectionTitles(int machineId, int projectid, List<siger_project_level> levels, out int sectionId)
        {
            var list = new List<string>();
            sectionId = 0;
            var lastLvel = levels.Last().id;
            var levelCount = levels.Count;
            var lastSecondLevel = levels.First(t => t.id == levels.Last().parentid).id;

            var machineSection = _context.siger_project_machine_attribution.FirstOrDefault(q =>
                q.machine == machineId && q.status == (int)RowState.Valid);
            if (machineSection != null)
            {
                var query = GetParentLevelSections(machineSection.station, projectid).OrderBy(t => t.id).ToList();
                if (query.Count < levelCount)
                {
                    return string.Empty;
                }

                foreach (var section in query)
                {
                    if (section.levelid == lastLvel)
                    {
                        continue;
                    }

                    if (section.levelid == lastSecondLevel)
                    {
                        sectionId = section.id;
                    }
                    list.Add(section.title);
                }
            }

            return string.Join("-", list);
        }

        public string GetMachineAndSectionName(int machineId,int pid)
        {
            var ret = string.Empty;
            var attributionData = _context.siger_project_machine_attribution.FirstOrDefault(f => f.machine == machineId && f.attribution == 1 && f.projectid == pid && f.status != 0);
            ret = string.Join("-", GetParentLevelSections(attributionData?.station ?? 0, pid).OrderBy(o => o.id).Select(s => s.title));
            ret += "-" + attributionData?.name ?? "";
            return ret;
        }
        public IEnumerable<MachinSectionInfo> GetMachineStationInfos(IEnumerable<int> machineIds, int projectId)
        {
            var query = from sm in _context.siger_project_machine_attribution
                        join ts in _context.siger_project_level_section on sm.station equals ts.id
                        join s in _context.siger_project_level_section on ts.parentid equals s.id
                        where sm.status == (int)RowState.Valid && ts.status == (int)RowState.Valid &&
                              s.status == (int)RowState.Valid && ts.projectid == projectId
                              && machineIds.Contains(sm.machine)
                        select new MachinSectionInfo
                        {
                            id = s.id,
                            machineId = sm.machine,
                            section_name = s.title,
                            station_name = ts.title,
                            machine_name = sm.name,
                            sectionId = ts.id
                        };
            return query.AsEnumerable();
        }

        public List<MachinLocation> GetMachineLocationForDashboard(List<int> machineids, int projectid)
        {
            var query = from ma in _context.siger_project_machine
                        join pc in _context.siger_project_production_configuration on
                        new { machine = ma.id, ma.status, ma.projectid } equals
                        new { machine = pc.machine_id, pc.status, pc.projectid }
                        where ma.status != (int)RowState.Invalid && ma.projectid == projectid
                        select new MachinLocation
                        {
                            machineId = ma.id,
                            machine_name = ma.title,
                            x = pc.x,
                            y = pc.y
                        };

            var ret = query.Where(f => machineids.Contains(f.machineId)).ToList();
            return ret;
        }

        public List<siger_project> GetProjectList()
        {
            var query = _context.siger_project.Where(q => q.status == (int)RowState.Valid).ToList();
            return query;
        }

        public List<siger_project_machine> GetMathineTitle(IEnumerable<int> machineIds,int status,int projectid,int category) 
        {
            var query = from ma in _context.siger_project_machine
                        join pc in _context.siger_project_machine_attribution on ma.id equals pc.machine
                        join oc in _context.siger_project_level_section on pc.station equals oc.id
                        where machineIds.Contains(ma.id) && ma.status == status && ma.projectid==projectid && ma.projectid == projectid &&ma.category==category
                        select new siger_project_machine
                        {
                            id = ma.id,
                            title=oc.title,
                            sorting = ma.sorting,
                        };
            return query.ToList();
        }
        /// <summary>
        /// 通过设备固定资产编码 获取设备产线信息
        /// </summary>
        /// <param name="code"></param>
        /// <param name="sectionId"></param>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public MachinSectionInfo GetSectionMachineByCode(string code, int projectId)
        {
            var query = from m in _context.siger_project_machine
                        join tr in _context.siger_project_machine_attribution on m.id equals tr.machine
                        join l in _context.siger_project_level_section on tr.station equals l.id
                        where m.projectid == projectId && m.code == code && m.status==(int)RowState.Valid
                        select new MachinSectionInfo
                        {
                             machineId=m.id,
                             machine_name=m.title,
                             sectionId=tr.station,
                             section_name=l.title,
                             station_name=l.title
                        };
            return query.FirstOrDefault();
        }
    }
}
