everlight.eap/backend/DllEapDal/MachineDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using DllEapEntity.Dtos;
using System.ComponentModel.DataAnnotations;
using Cksoft.Unity.Log4NetConfig;
using DllEapCommon;
using DllEapDal.OFILM;
using Cksoft.Data.Repository;
using System.Threading.Tasks;
using DllEapCommon.NPOI;
using DllEapEntity.Dtos.Export;
using AutoMapper;
using NPOI.SS.UserModel;

namespace DllEapDal
{
    public class MachineDal
    {
        private IDatabase CurrDb = null;
        private string commonFilter = null;
        private IMapper _mapper;
        public MachineDal(IDatabase db)
        {
            CurrDb = db;
        }
        public MachineDal(IDatabase db, string userCode)
        {
            CurrDb = db;
        }
        public MachineDal(IDatabase db, IMapper mapper)
        {
            CurrDb = db;
            _mapper = mapper;
        }
        public Machine IUMachine(Machine 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;
                    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<Machine>(id);
                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }

        public object DelMachine(List<Machine> msts, ref string errorinfo)
        {
            try
            {
                int result = CurrDb.DeleteForEntity<Machine>(msts);
                if (result < 0)
                    return null;
                return result;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }

        public IEnumerable<CascaderDto> GetCascaders()
        {
            var processDal = new TProcessDal(CurrDb);
            var processes = processDal.Get();
            var macmodelDal = new MacModelDal(CurrDb);
            var macmodels = macmodelDal.GetSecMacModels();
            var list = new List<CascaderDto>();
            for (var i = 0; i < processes.Count(); i++)
            {
                var casDto = new CascaderDto()
                {
                    Key = processes.ElementAt(i).ID,
                    Title = processes.ElementAt(i).FCode,
                    Label = processes.ElementAt(i).FCode,
                    Value = processes.ElementAt(i).FCode,
                    Group = true,
                    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
                            {
                                Key = item.ID,
                                Title = item.FCode,
                                Label = item.FCode,
                                Value = item.FCode,
                                Children = null,
                                IsLeaf = true,

                            });
                        }
                    }
                    casDto.Children = cList;
                }
                else
                {
                    casDto.IsLeaf = true;
                }
                list.Add(casDto);
            }
            return list;
        }

        #region Web展示机台
        public IEnumerable<Machine> Get(int start, int length, string order, string sort, string filter, ref string errorinfo)
        {
            var pros = CurrDb.FindListForCondition<Machine>($" {commonFilter + filter} order by {sort} {order} limit {start - 1},{length}", ref errorinfo);
            if (pros != null && pros.Count() > 0)
            {
                var regionDal = new FactoryRegionDal(CurrDb);
                var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
                foreach (var item in pros)
                {
                    item.RegionName = regionDal.GetFullRegionName(item.RegionId, allRegions);
                }
            }
            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<Machine>(commonFilter + filter, ref errorinfo);
            if (entities != null)
            {
                return entities.GroupBy(c => c.FCode).Count();
            }
            return 0;
        }

        public Machine Get(int id)
        {
            var pro = CurrDb.FindEntityFor<Machine>(id);
            return pro;
        }
        public Machine GetMac(string filter)
        {
            try
            {
                string sql = $@"SELECT A.FCode,A.IPAddress,A.ID,B.ProRootPath,A.MacAddress,A.RegionId,g.FCode PCode,a.MModeID
                                from machine as A
                                LEFT JOIN macmodel as B on A.MModeID = B.id
                                left outer join mactprocess f on a.id =f.macid
                                left outer join tprocess g on f.PCode =g.FCode
                                WHERE a.MacAddress = '{filter}'";
                var entities = CurrDb.FindList<Machine>(sql).FirstOrDefault();
                return entities;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public string GetMacType(string macCode)
        {
            try
            {
                string sql = $@"select Remark  from MacStatus01 where MacCode='{macCode}'";
                var entities = CurrDb.FindList<MacStatus01>(sql).FirstOrDefault();
                return entities?.Remark;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 添加机台并返回机台Id
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <returns></returns>
        public int Add(Machine pro, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<Machine>($" and a.FCode='{pro.FCode}' ", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "机台已存在，请确认";
                return -1;
            }
            pro.FName = string.IsNullOrEmpty(pro.FName) ? pro.FCode : pro.FName;
            // AOI机台排除与当前机台成对的另一个机台
            if (pro.FCode.StartsWith("LXJC"))
            {
                entities = CurrDb.FindListForCondition<Machine>($"and a.IpAddress='{pro.IPAddress}' " +
                    $"and a.FCode<>'{pro.FCode.Substring(0, pro.FCode.Length - 1) + "L"}' " +
                    $"and a.FCode<>'{pro.FCode.Substring(0, pro.FCode.Length - 1) + "R"}'", ref errorinfo);
            }
            else
            {
                entities = CurrDb.FindListForCondition<Machine>($" and a.IpAddress='{pro.IPAddress}'", ref errorinfo);
            }
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = $"IP地址与机台[{entities.First().FCode}]重复，请确认";
                return -1;
            }
            if (pro.FCode.StartsWith("LXJC"))
            {
                entities = CurrDb.FindListForCondition<Machine>($"and a.MacAddress='{pro.MacAddress}' " +
                    $"and a.FCode<>'{pro.FCode.Substring(0, pro.FCode.Length - 1) + "L"}' " +
                    $"and a.FCode<>'{pro.FCode.Substring(0, pro.FCode.Length - 1) + "R"}'", ref errorinfo);
            }
            else
            {
                entities = CurrDb.FindListForCondition<Machine>($" and a.MacAddress='{pro.MacAddress}'", ref errorinfo);
            }
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = $"MAC地址与机台[{entities.First().FCode}]重复，请确认";
                return -1;
            }
            pro.RecCode = userCode;
            pro.ModCode = userCode;
            pro.RecTime = DateTime.Now;
            pro.ModTime = DateTime.Now;
            pro.FPort = pro.FPort ?? 5001;
            pro.DeviceID = pro.DeviceID ?? 0;
            pro.IsConn = pro.IsConn ?? 1;
            pro.IsControl = pro.IsControl ?? -1;
            pro.IsAnalysis = pro.IsAnalysis ?? 1;
            pro.CallPMode = pro.CallPMode ?? 1;
            pro.IsRemoveProgram = pro.IsRemoveProgram ?? -1;
            if (pro.RegionId != 0)
            {
                var regionDal = new FactoryRegionDal(CurrDb);
                var factoryId = regionDal.GetRoot(pro.RegionId).Id;
                pro.FactoryId = factoryId;
            }
            string sql = $"insert into Machine(MModeID,FCode,FName,IPAddress,FPort,SupplierID,DeviceID,T3,T5,T6,T7,T8,Remark,RecCode,RecTime," +
                $"ModCode,ModTime,ScanIp,CallPMode,LocDir," +
                $"MacAddress,RegionId,FactoryId) values('{pro.MModeID}','{pro.FCode}','{pro.FName}','{pro.IPAddress}','{pro.FPort}','{pro.SupplierID}'," +
                $"'{pro.DeviceID}','{pro.T3}','{pro.T5}','{pro.T6}','{pro.T7}','{pro.T8}','{pro.Remark}','{pro.RecCode}'," +
                $"'{pro.RecTime.ToString("yyyy-MM-dd HH:mm:ss")}','{pro.ModCode}','{pro.ModTime.ToString("yyyy-MM-dd HH:mm:ss")}','{pro.ScanIp}','{pro.CallPMode}','{pro.LocDir}'," +
                $"'{pro.MacAddress}','{pro.RegionId}','{pro.FactoryId}');";
            sql += "select @@identity;";
            var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
            if (id < 0)
            {
                errorinfo = "机台信息新增失败";
                return -1;
            }
            // 插入制程关系
            if (!string.IsNullOrEmpty(pro.PCode))
            {
                sql = "insert into mactprocess(MacID,PCode,reccode,rectime,modcode,modtime) " +
                    $"values('{id}','{pro.PCode}','{userCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'," +
                    $"'{userCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    errorinfo = "插入制程信息失败";
                    return -1;
                }
            }
            if (!string.IsNullOrEmpty(pro.macnumber))
            {
                MachineNumber m = new MachineNumber();
                m.MacNumber = pro.macnumber;
                m.RecCode = userCode;
                m.MacId = id;
                if (CurrDb.InsertFor<MachineNumber>(m, pro.RecCode) < 0)
                {
                    errorinfo = "机台编号添加失败";
                    return -1;
                }
            }
            return id;
        }

        /// <summary>
        /// 修改机台信息
        /// </summary>
        /// <param name="role"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int Update(Machine role, string userCode, ref string errorinfo)
        {
            var entities = CurrDb.FindListForCondition<Machine>($" and a.FCode='{role.FCode}' " +
                $"and a.ID<>{role.ID}", ref errorinfo);
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = "已存在相同的机台，请确认";
                return -1;
            }
            if (role.FCode.StartsWith("LXJC"))
            {
                entities = CurrDb.FindListForCondition<Machine>($"and a.ID<>{role.ID} and a.IpAddress='{role.IPAddress}' " +
                    $"and a.FCode<>'{role.FCode.Substring(0, role.FCode.Length - 1) + "L"}' " +
                    $"and a.FCode<>'{role.FCode.Substring(0, role.FCode.Length - 1) + "R"}'", ref errorinfo);
            }
            else
            {
                entities = CurrDb.FindListForCondition<Machine>($" and a.ID<>{role.ID} and a.IpAddress='{role.IPAddress}'", ref errorinfo);
            }
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = $"IP地址与机台[{entities.First().FCode}]重复，请确认";
                return -1;
            }
            if (role.FCode.StartsWith("LXJC"))
            {
                entities = CurrDb.FindListForCondition<Machine>($"and a.ID<>{role.ID} and a.MacAddress='{role.MacAddress}' " +
                    $"and a.FCode<>'{role.FCode.Substring(0, role.FCode.Length - 1) + "L"}' " +
                    $"and a.FCode<>'{role.FCode.Substring(0, role.FCode.Length - 1) + "R"}'", ref errorinfo);
            }
            else
            {
                entities = CurrDb.FindListForCondition<Machine>($" and a.ID<>{role.ID} and a.MacAddress='{role.MacAddress}'", ref errorinfo);
            }
            if (entities != null && entities.Count() > 0)
            {
                errorinfo = $"MAC地址与机台[{entities.First().FCode}]重复，请确认";
                return -1;
            }
            var entity = CurrDb.FindEntityFor<Machine>(role.ID);
            entity.FCode = role.FCode;
            entity.FName = role.FName ?? entity.FName;
            entity.CallPMode = role.CallPMode ?? entity.CallPMode;
            entity.DeviceID = role.DeviceID ?? entity.DeviceID;
            entity.FPort = role.FPort ?? entity.FPort;
            entity.FVersion = role.FVersion ?? entity.FVersion;
            entity.IPAddress = role.IPAddress;
            entity.LocDir = role.LocDir ?? entity.LocDir;
            entity.MacAddress = role.MacAddress;
            entity.MModeID = role.MModeID;
            entity.IsConn = role.IsConn ?? entity.IsConn;
            entity.IsAnalysis = role.IsAnalysis ?? entity.IsAnalysis;
            entity.IsControl = role.IsControl ?? entity.IsControl;
            entity.IsRemoveProgram = role.IsRemoveProgram ?? entity.IsRemoveProgram;
            entity.ProRootPath = role.ProRootPath ?? entity.ProRootPath;
            entity.Remark = role.Remark ?? entity.Remark;
            entity.ModCode = userCode;
            entity.ModTime = DateTime.Now;
            entity.macnumber = role.macnumber ?? entity.macnumber;
            // var autoReconnct = false;
            if (role.RegionId != 0)
            {
                var regionDal = new FactoryRegionDal(CurrDb);
                var factoryId = regionDal.GetRoot(role.RegionId).Id;
                // autoReconnct = entity.FactoryId != factoryId ? true : false;
                //if (entity.IsConn == -1)
                //{
                //    autoReconnct = false;
                //}
                entity.FactoryId = factoryId;
                entity.RegionId = role.RegionId;
            }
            if (CurrDb.UpdateFor(entity, userCode) < 0)
            {
                return -1;
            }
            // 更新制程关系
            if (!string.IsNullOrEmpty(role.PCode))
            {
                var sql = $"delete from mactprocess where macid='{role.ID}'";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    errorinfo = "更新制程信息失败";
                    return -1;
                }
                sql = "insert into mactprocess(MacID,PCode,reccode,rectime,modcode,modtime) " +
                   $"values('{role.ID}','{role.PCode}','{userCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'," +
                   $"'{userCode}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    errorinfo = "更新制程信息失败";
                    return -1;
                }
            }
            if (true)
            {
                MachineNumber m = new MachineNumber();
                List<MachineNumber> list = CurrDb.FindList<MachineNumber>($"select * from machinenumber where macid={role.ID}")
                    .ToList();
                if (list.Count > 0)
                {
                    m = list[0];
                    if (m.MacNumber != entity.macnumber)
                    {
                        m.MacNumber = entity.macnumber;
                        m.ModCode = userCode;
                        if (CurrDb.UpdateFor<MachineNumber>(m, userCode) < 0)
                        {
                            errorinfo = "机台编号修改失败";
                            return -1;
                        }
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(entity.macnumber))
                    {
                        m.MacId = role.ID;
                        m.MacNumber = role.macnumber;
                        if (CurrDb.InsertFor<MachineNumber>(m, userCode) < 0)
                        {
                            errorinfo = "机台编号修改失败";
                            return -1;
                        }
                    }
                }

            }
            return role.ID;
        }

        public int DoAutoReconnect(Machine preMac, int macId, ref string errorinfo)
        {
            if (preMac.ID == 0)
            {
                return this.AutoReconnet(macId, ref errorinfo, preMac.FCode);
            }
            var shouldReconnct = false;
            var regionDal = new FactoryRegionDal(CurrDb);
            var preFactoryId = regionDal.GetRoot(preMac.RegionId).Id;
            var currMac = CurrDb.FindEntityFor<Machine>(macId);
            var currFactoryId = regionDal.GetRoot(currMac.RegionId).Id;
            if (preFactoryId != currFactoryId || preMac.FCode.ToUpper() != currMac.FCode.ToUpper()
                || preMac.IPAddress != currMac.IPAddress || preMac.MModeID != currMac.MModeID)
            {
                shouldReconnct = true;
            }
            if (shouldReconnct)
            {
                return this.AutoReconnet(macId, ref errorinfo, preMac.FCode);
            }
            return 1;
        }

        /// <summary>
        /// 自动重连
        /// </summary>
        /// <returns></returns>
        public int AutoReconnet(int macId, ref string errorinfo, string preMacCode = null,
            bool moveMacServer = true)
        {
            using (IDatabase db = DbFactory.Base("eap"))
            {
                db.BeginTrans();
                var mac = db.FindEntityFor<Machine>(macId);
                // 当机台设置成不连接时直接返回
                //if (mac.IsConn == -1)
                //    return 1;
                var macOrderDal = new MacOrderDal(db);
                // 检查指令是否正确
                if (macOrderDal.BindOrders(mac, mac.ModCode, ref errorinfo) < 0)
                {
                    db.Rollback();
                    return -1;
                }
                // 关闭现有连接
                AppserverHelper appServerHelper = new AppserverHelper();
                if (appServerHelper.StopMachine(db, new Machine() { ID = macId, FCode = preMacCode }, ref errorinfo) < 0)
                {
                    db.Commit();
                    return -2;
                }
                if (moveMacServer)
                {
                    // 将机台移动到对应园区的AP服务器上
                    var serverDal = new EapAppServerDal(db);
                    var server = serverDal.GetMacLeastServer(mac);
                    if (server == null)
                    {
                        errorinfo = "未找到当前园区对应的AP服务器";
                        db.Commit();
                        return -1;
                    }
                    var serverMacDal = new EapAppServerMacDal(db);
                    if (serverMacDal.Update(mac.ID, server.ID, mac.ModCode, ref errorinfo) < 0)
                    {
                        db.Rollback();
                        return -1;
                    }
                    db.Commit();
                    // 刷新RMS服务
                    if (appServerHelper.RmsRefreshOrders(db, mac.FCode, ref errorinfo) < 0)
                        return -2;
                }
                // 重新打开连接
                if (appServerHelper.StartMachine(db, mac.FCode, ref errorinfo) < 0)
                    return -2;
            }
            return 1;
        }

        /// <summary>
        /// 修改真实ip
        /// </summary>
        /// <param name="role"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int UpdateRealIP(Machine role, ref string errorinfo)
        {
            string filter = $" and a.RealIP='{role.RealIP}' and AppletV='{role.AppletV}' and a.id={role.ID}";
            var mdoels = CurrDb.FindListForCondition<Machine>(filter, ref errorinfo);
            if (mdoels != null && mdoels.Count() > 0)
            {
                // LogHelper<Machine>.LogFatal("Machine真实IP，版本号与采集到的一致-->filter:" + filter, "小程序操作", "none");
            }
            else
            {
                string sql = $" update machine set RealIP='{role.RealIP}',AppletV='{role.AppletV}' where id={role.ID}";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    return -1;
                }
                //LogHelper<Machine>.LogFatal("修改Machine真实IP,版本号-->sql:" + sql, "小程序操作", "none");
            }
            string insert_sql = $@"INSERT INTO ofappletlog (  macCode, AppletV, AppletS )VALUES( '{role.FCode}', '{role.AppletV}', {1});";
            CurrDb.ExecuteBySql(insert_sql);
            return role.ID;
        }

        public int UpdateRealIPForMac(Machine ma, ref string errorinfo)
        {
            try
            {
                var entities = CurrDb.FindListForCondition<Machine>($" and locate('{ma.MacAddress}',MacAddress)>0 ", ref errorinfo);
                if (entities != null && entities.Count() > 0)//存在则更新RealIP
                {
                    string filter = $" and a.RealIP='{ma.RealIP}' and a.id={ma.ID}";
                    var mdoels = CurrDb.FindListForCondition<Machine>(filter, ref errorinfo);
                    if (mdoels != null && mdoels.Count() > 0)
                    {
                        //LogHelper<Machine>.LogFatal("Machine真实IP与采集到的一致-->filter:" + filter, "小程序操作", "none");
                    }
                    else
                    {
                        string sql = $" update machine set RealIP='{ma.RealIP}' where locate('{ma.MacAddress}',MacAddress)>0";
                        if (CurrDb.ExecuteBySql(sql) < 0)
                        {
                            errorinfo = $"mac地址为:{ma.MacAddress}机台的RealIP{ma.RealIP}更新失败！";
                            return -1;
                        }
                    }

                }
                else//否则新增一条此MAC\RealIP的机台
                {
                    string NewID = "";
                    String Unsql = $" SELECT max(right(FCode,5)) from machine where substr(FCode,1,6)='Unknow'  ";
                    object Unid = CurrDb.FindObject(Unsql);
                    if (Unid.ToString() == "")
                    {
                        NewID = "Unknow00001";
                    }
                    else
                    {
                        NewID = (int.Parse(Unid.ToString()) + 1).ToString("00000");
                    }
                    string sql = $"insert into Machine(FCode,RecTime,ModTime,MacAddress,RealIP) values('Unknow{NewID}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{ma.MacAddress}','{ma.RealIP}') ";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        errorinfo = $"机台号为:{NewID},mac地址为:{ma.MacAddress}机台的RealIP{ma.RealIP}新增失败！";
                        return -1;
                    }
                }
                return 1;
            }
            catch (Exception ex)
            {
                errorinfo = "UpdateRealIPForMac报错：" + ex.Message;
                return -1;
            }
        }
        public int Delete(int id, ref string msg)
        {
            var sql = $"delete from staffmachine where macid={id}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "删除已绑定该机台的相关员工数据时发生错误";
                return -1;
            }
            sql = $"delete from eapappservermac where macid={id}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "将机台从绑定的AP服务器中移除失败";
                return -1;
            }
            sql = $"delete from mactprocess where macid={id}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "移除机台制程关系失败";
                return -1;
            }
            sql = $"delete from macorder where macid={id}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                msg = "移除机台指令失败";
                return -1;
            }
            if (CurrDb.DeleteFor<Machine>(id) < 0)
            {
                msg = "删除失败";
                return -1;
            }
            msg = string.Empty;
            return 1;
        }

        public int ChangeIsControl(string field, int id, int value)
        {
            string errorinfo = string.Empty;
            string sql = string.Format($"update machine set {field}={value} where id={id}");
            int count = CurrDb.ExecuteBySql(sql);
            if (count > 0)
            {
                LogHelper<Machine>.LogFatal("修改Machine-->sql:" + sql, "用户操作", "none");
                if (field.ToLower() == "isconn" && value == 1)
                {
                    //var mac = CurrDb.FindEntityFor<Machine>(id);
                    //this.AutoReconnet(mac, ref errorinfo);
                }
            }
            return count;
        }

        public IEnumerable<Machine> Get(string filter)
        {
            string error = string.Empty;
            var entities = CurrDb.FindListForCondition<Machine>(filter, ref error);
            var regionDal = new FactoryRegionDal(CurrDb);
            var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, error);
            var machineNm = CurrDb.FindListForCondition<MachineNumber>("", ref error);
            foreach (var item in entities)
            {
                item.RegionName = regionDal.GetFullRegionName(item.RegionId, allRegions);
                item.macnumber = machineNm.Where(c => c.MacId == item.ID).Select(c => c.MacNumber).FirstOrDefault();
            }
            return entities;
        }

        /// <summary>
        /// 获取机台多选框
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<SelectDto<int>> GetMultipleSelects(string filter)
        {
            var sql = "select a.id value,a.FCode label from machine a " +
                "left join factoryregion b on a.regionid=b.id " +
                "left join factoryregion c on b.parentid=c.id " +
                "left join factoryregion d on c.parentid=d.id " +
                $" where 1=1 {filter}";
            var entities = CurrDb.FindList<SelectDto<int>>(sql);
            return entities;
        }

        public int BindMachine(string pCode, IEnumerable<string> macIds, string userCode, ref string errorinfo)
        {
            string sql = string.Format($"delete from mactprocess where pcode='{pCode}'");
            CurrDb.ExecuteBySql(sql);
            if (macIds != null && macIds.Count() > 0)
            {
                foreach (var item in macIds)
                {
                    var mactprocess = new DllEapEntity.Rms.MacTProcess
                    {
                        MacID = item,
                        modcode = userCode,
                        PCode = pCode,
                        reccode = userCode,
                        modtime = DateTime.Now,
                        rectime = DateTime.Now
                    };
                    if (CurrDb.InsertFor<DllEapEntity.Rms.MacTProcess>(mactprocess, userCode) < 0)
                    {
                        errorinfo = "修改绑定机台失败";
                        return -1;
                    }
                }
            }
            return 1;
        }

        public int BindMachineToPM(int pmId, IEnumerable<string> macIds, string userCode, ref string errorinfo)
        {
            string sql = string.Format($"delete from pmmachine where pmid='{pmId}'");
            CurrDb.ExecuteBySql(sql);
            if (macIds != null && macIds.Count() > 0)
            {
                foreach (var item in macIds)
                {
                    var mactprocess = new DllEapEntity.PM.PMMachine
                    {
                        MacID = Convert.ToInt32(item),
                        ModCode = userCode,
                        PMID = pmId,
                        StartTime = DateTime.Now,
                        RecCode = userCode,
                        ModTime = DateTime.Now,
                        RecTime = DateTime.Now
                    };
                    if (CurrDb.InsertFor<DllEapEntity.PM.PMMachine>(mactprocess, userCode) < 0)
                    {
                        errorinfo = "修改绑定机台失败";
                        return -1;
                    }
                }
            }
            return 1;
        }

        public IEnumerable<CascaderDto> GetGroupedMachines(int? facotryId)
        {
            string errorinfo = string.Empty;
            var macmodelDal = new MacModelDal(CurrDb);
            var macmodels = macmodelDal.GetSecMacModels();
            var filter = string.Empty;
            if (facotryId != null)
            {
                filter += $" and a.factoryId={facotryId}";
            }
            var sql = $@"select a.*,b.FCode as FModel from Machine a 
                        left join Macmodel b on a.MModeID=b.ID 
                        where 1=1 {filter}";
            var machines = CurrDb.FindList<SecMachine>(sql);
            var list = new List<CascaderDto>();
            for (var i = 0; i < macmodels.Count(); i++)
            {
                if (list.FirstOrDefault(c => c.Value == macmodels.ElementAt(i).FCode) != null)
                    continue;
                var casDto = new CascaderDto()
                {
                    Key = macmodels.ElementAt(i).FCode,
                    Title = macmodels.ElementAt(i).FCode,
                    Label = macmodels.ElementAt(i).FCode,
                    Value = macmodels.ElementAt(i).FCode,
                    Group = true,
                    IsLeaf = false
                };
                var children = machines.Where(c => c.FModel == 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
                            {
                                Key = item.FCode,
                                Title = item.FCode,
                                Label = item.FCode,
                                Value = item.FCode,
                                Children = null,
                                IsLeaf = true,

                            });
                        }
                    }
                    casDto.Children = cList;
                }
                else
                {
                    casDto.IsLeaf = true;
                }
                list.Add(casDto);
            }
            return list;
        }


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

        /// <summary>
        /// 根据员工工号获取已授权的机台
        /// </summary>
        /// <param name="staffCode"></param>
        /// <returns></returns>
        public IEnumerable<string> GetAuthedMacs(string staffCode, ref string errorinfo)
        {
            return CurrDb.FindListForCondition<StaffMachine>($" and a.StaffCode='{staffCode}'", ref errorinfo)
                .Select(c => c.MacCode);
        }

        /// <summary>
        /// 授权机台
        /// </summary>
        /// <param name="staffCode">员工工号</param>
        /// <param name="macIds">机台编号</param>
        /// <param name="userCode">操作人</param>
        /// <param name="errorinfo">错误信息</param>
        /// <returns></returns>
        public int SetMacs(string staffCode, IEnumerable<int> macIds, string userCode, ref string errorinfo)
        {
            string sql = $"delete from StaffMachine where StaffCode='{staffCode}'";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                errorinfo = "删除原数据失败";
                return -1;
            }
            if (macIds != null && macIds.Count() > 0)
            {
                foreach (var item in macIds)
                {
                    sql = $@"insert into StaffMachine(StaffCode,MacId,RecCode,RecTime,ModCode,ModTime) 
                           values('{staffCode}','{item}','{userCode}',
                           '{DateTime.Now.ToString("yyyy-MM-dd")}','{userCode}',
                           '{DateTime.Now.ToString("yyyy-MM-dd")}')";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        errorinfo = "插入新纪录失败";
                        return -1;
                    }
                }
            }
            return 1;
        }

        /// <summary>
        /// 批量设置是否管控等参数
        /// </summary>
        public int SetControlByTrans(IEnumerable<int> ids, string fileld, string value, ref string errorinfo)
        {
            var idFilter = $" and id in ({string.Join(",", ids.Select(c => c))})";
            var sql = $"Update machine set {fileld}={value} where 1=1 {idFilter}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                errorinfo = "更新数据库失败";
                return -1;
            }
            return 1;
        }

        public IEnumerable<Machine> GetMachineList()
        {
            string sql = $@"select a.*,b.FCode MModeCode,b.FName MModeName,c.FName FactoryName,d.FName RegionName,
                            e.FName Floor,g.FCode PCode,
                            f.FName as plantName,f.id as plantId,e.id as FloorId,d.FName as LineName 
                            from machine a
                            left join macmodel b on a.MModeID=b.Id
                            left outer join mactprocess h on a.id =h.macid
                            left outer join tprocess g on h.PCode =g.FCode
                            left join factoryregion c on a.factoryId = c.id
                            left join factoryregion d on a.regionId=d.id 
                            left join factoryregion e on d.parentid=e.id
                            left join factoryregion f on e.parentid=f.id 
                            where g.FCode is not null
                            order by a.FCode ";
            return CurrDb.FindList<Machine>(sql);
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public async Task<IWorkbook> Export(string filter)
        {
            string errorinfo = string.Empty;
            var pros = CurrDb.FindListForCondition<Machine>($" {commonFilter + filter} order by a.fcode", ref errorinfo);
            if (pros != null && pros.Count() > 0)
            {
                var regionDal = new FactoryRegionDal(CurrDb);
                var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
                foreach (var item in pros)
                {
                    item.RegionName = regionDal.GetFullRegionName(item.RegionId, allRegions);
                }
            }
            var list = _mapper.Map<IEnumerable<MachineExportDto>>(pros);
            var book = DataExportHelper.EntityToExcel(list.ToList());
            await Task.CompletedTask;
            return book;
        }

        #endregion
    }
}