everlight.eap/backend/DllEapDal/OFILM/MacRecipeDal.cs

using Cksoft.Data;
using Cksoft.Unity;
using DllEapCommon.Regex;
using DllEapEntity;
using DllEapEntity.OFILM;
using DllEapEntity.Rms;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DllEapDal.OFILM
{
    public class MacRecipeDal
    {
        private IDatabase CurrDb = null;
        public MacRecipeDal(IDatabase db)
        {
            CurrDb = db;
        }

        public IEnumerable<MacRecipeDto> GetMacRecipeDtos(string filter, int start, int length, string sort, string order, out int total, ref string errorinfo)
        {
            var countSql = $@"select count(1) from machine a
                             left join macstatus01 b on a.FCode = b.maccode
                             left join mactprocess c on a.id = c.macid
                             left join standardstatus d on b.statusid = d.StatusVal
                             left join factoryregion f on a.factoryId = f.id 
                             where 1=1 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            var sql = $@"select a.ID MacId,a.FCode maccode,a.RegionId regionId,c.pcode,b.remark programName,a.realip,a.macaddress,
                         ifnull(d.FName,'离线') statusName,ifnull(d.FCode,'DISCONN') statusCode,f.FName factory,a.ipaddress,a.fVersion,
                         ifnull(d.red,178) red ,ifnull(d.green,178) green,ifnull(d.blue,178) blue,g.fname floor,b.StatusID,b.STime ,l.FName PlantName,h.FName Line
                         from machine a 
                         left join macstatus01 b on a.FCode=b.maccode
                         left join mactprocess c on a.id=c.macid
                         left join standardstatus d on b.statusid=d.StatusVal
                         left join factoryregion f on a.factoryId = f.id
                         left join factoryregion h on a.regionId=h.id 
                         left join factoryregion g on h.parentid=g.id
                         left outer join factoryregion l on g.parentid =l.id
                         where 1=1 {filter} 
                         order by {sort} {order} limit {start - 1},{length}";
            var datas = CurrDb.FindList<MacRecipeDto>(sql);
            if (datas != null && datas.Count() > 0)
            {
                sql = "select * from machine where realip in (select realip from machine group by realip having count(1)>1)";
                var wrongs = CurrDb.FindList<Machine>(sql);
                foreach (var item in datas)
                {
                    if (wrongs.Any(c => c.ID == item.MacId))
                    {
                        item.RealIpState = -1;
                    }
                    else
                    {
                        item.RealIpState = 1;
                    }
                }

            }
            return datas;

        }

        public IEnumerable<MacRecipeDto> GetMacStatusDtos(string filter, int start, int length, string sort, string order, out int total, ref string errorinfo)
        {
            var countSql = $@"select count(1) from machine a
                             left join macstatus b on a.FCode = b.maccode
                             left join mactprocess c on a.id = c.macid
                             left join standardstatus d on b.statusid = d.StatusVal
                             left join factoryregion f on a.factoryId = f.id 
                             where 1=1 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            var sql = $@"select a.ID MacId,a.FCode maccode,a.RegionId regionId,c.pcode,b.remark programName,a.realip,a.macaddress,
                         ifnull(d.FName,'离线') statusName,ifnull(d.FCode,'DISCONN') statusCode,f.FName factory,a.ipaddress,
                         ifnull(d.red,178) red ,ifnull(d.green,178) green,ifnull(d.blue,178) blue,g.fname floor,b.StatusID,b.STime,b.ETime 
                         from machine a 
                         left join macstatus b on a.FCode=b.maccode
                         left join mactprocess c on a.id=c.macid
                         left join standardstatus d on b.statusid=d.StatusVal
                         left join factoryregion f on a.factoryId = f.id
                         left join factoryregion h on a.regionId=h.id 
                         left join factoryregion g on h.parentid=g.id
                         where 1=1 {filter} 
                         order by {sort} {order} limit {start - 1},{length}";
            var datas = CurrDb.FindList<MacRecipeDto>(sql);
            return datas;

        }

        public IEnumerable<MachineInfo> GetMachineInfoes(string filter, int start, int length, string sort, string order, out int total, ref string errorinfo)
        {
            var recipeStr = OfilmRecipeProvider.GenRecipeString("b");
            var countSql = $@"select count(1) from machine a
                             left join macstatus01 b on a.FCode = b.maccode
                             left join macmodel l on a.mmodeid=l.id 
                             left join mactprocess c on a.id = c.macid
                             left join standardstatus d on b.statusid = d.StatusVal
                             left join factoryregion f on a.factoryId = f.id 
                             LEFT JOIN machinenumber  ON a.ID = machinenumber.MacId 
                             where 1=1 {filter}";
            total = Convert.ToInt32(CurrDb.FindList<string>(countSql).FirstOrDefault() ?? "0");
            var sql = $@"select a.ID MacId,a.FCode maccode,machinenumber.MacNumber macnumber,a.fName macName,a.RegionId regionId,c.pcode,b.remark programName,a.realip,a.macaddress,
                         m.id SupplierId,m.FCode SupplierCode,l.id MacModelId,a.LocDir LocDir,a.DeviceID DeviceID,a.FPort FPort,
                         a.CallPMode CallPMode,a.Remark Remark,
                         ifnull(d.FName,'离线') statusName,ifnull(d.FCode,'DISCONN') statusCode,f.FName factory,a.ipaddress,
                         ifnull(d.red,178) red ,ifnull(d.green,178) green,ifnull(d.blue,178) blue,g.fname floor,h.fname line,i.fname plant,b.StatusID,b.STime,b.ETime,
                         ifnull(j.project,'暂无') project,k.fName modName,a.ModTime ModTime,
                         {recipeStr} recipe,
                         l.fCode MacModelCode,p.FCode AppServerCode,p.FName AppServerName,
                         p.FIp AppServerIp ,CASE when a.AppletS=1 THEN 1 ELSE 2 end as AppletS,a.AppletV
                         from machine a 
                         left join macstatus01 b on a.FCode=b.maccode
                         left join macmodel l on a.mmodeid=l.id 
                         left join supplier m on l.supplierid=m.id 
                         left join mactprocess c on a.id=c.macid
                         left join standardstatus d on b.statusid=d.StatusVal
                         left join factoryregion f on a.factoryId = f.id
                         left join factoryregion h on a.regionId=h.id 
                         left join factoryregion g on h.parentid=g.id 
                         left join factoryregion i on g.parentid=i.id 
                         left join projectandprogram j on {recipeStr}=j.recipe 
                         left join staff k on a.modCode = k.fcode 
                         left join eapappservermac o on a.id=o.macid 
                         left join eapappserver p on p.id=o.eapappserverid 
                         LEFT JOIN machinenumber  ON a.ID = machinenumber.MacId 
                         where 1=1 {filter} 
                         order by {sort} {order} limit {start - 1},{length}";
            var datas = CurrDb.FindList<MachineInfo>(sql);
            if (datas != null && datas.Count() > 0)
            {
                sql = "select * from machine where realip in (select realip from machine group by realip having count(1)>1)";
                var wrongs = CurrDb.FindList<Machine>(sql);
                foreach (var item in datas)
                {
                    if (wrongs.Any(c => c.ID == item.MacId))
                    {
                        item.RealIpState = -1;
                    }
                    else
                    {
                        item.RealIpState = 1;
                    }
                    var changeLogDal = new MachineInfoChangeLogDal(CurrDb);
                    var log = changeLogDal.GetLastModifyRecord(item.MacCode).Result;
                    if (log != null && log.RecTime > item.ModTime)
                    {
                        item.ModName = log.RecName;
                        item.ModTime = log.RecTime;
                    }
                    if (item.AppletS == 1)
                    {
                        item.AppletSName = "已开启";
                    }
                }

            }

            return datas;
        }

        public int ImportMachines(MachineInfo[] machines, string userCode, IList<string> existMacCodes, ref string errorinfo)
        {
            foreach (var item in machines)
            {
                var ipRegex = new Regex(RegexExpression.IpAddressExp);
                if (!ipRegex.IsMatch(item.IpAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的IP地址[{item.IpAddress}]格式不正确";
                    return -1;
                }
                var macRegex = new Regex(RegexExpression.MacAddressExp);
                if (!macRegex.IsMatch(item.MacAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的MAC地址[{item.MacAddress}]格式不正确";
                    return -1;
                }
            }
            foreach (var item in machines)
            {
                var exist = Convert.ToInt32(CurrDb.FindList<string>($"select count(1) from machine where fCode='{item.MacCode}'").FirstOrDefault() ?? "-1");
                if (exist > 0)
                {
                    existMacCodes.Add(item.MacCode);
                    continue;
                }
                #region 获取所在园区及线体
                if (string.IsNullOrEmpty(item.Factory) || string.IsNullOrEmpty(item.Plant) || string.IsNullOrEmpty(item.Floor) || string.IsNullOrEmpty(item.Line))
                {
                    errorinfo = $"机台[{item.MacCode}]的位置信息不完整,不能导入";
                    return -1;
                }
                var lineId = 0; int? facId = 0;
                lineId = GetRegionId(item, out facId, ref errorinfo);
                if (lineId < 0)
                {
                    return -1;
                }

                #endregion
                #region 获取机型
                var supplierDal = new SupplierDal(CurrDb);
                var supplier = supplierDal.AddOrGet(new Supplier { FCode = item.SupplierCode, FName = item.SupplierCode }, userCode, ref errorinfo);
                if (supplier == null)
                {
                    errorinfo = $"机台厂家[{item.SupplierCode}]新增失败";
                    return -1;
                }
                var macmodelDal = new MacModelDal(CurrDb);
                var macModel = new MacModel { FCode = item.MacModelCode, FName = item.MacModelCode, SupplierID = supplier.ID };
                macModel = macmodelDal.AddOrGet(macModel, userCode, ref errorinfo);
                if (macModel == null)
                {
                    errorinfo = $"机型[{item.MacModelCode}]新增失败";
                    return -1;
                }
                #endregion
                #region 判断机台IP地址和MAC地址是否重复
                if (IsDuplicate("IpAddress", item.IpAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的IP地址[{item.IpAddress}]在数据库中已存在,请确认";
                    return -1;
                }
                if (IsDuplicate("MacAddress", item.MacAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的MAC地址[{item.MacAddress}]在数据库中已存在,请确认";
                    return -1;
                }
                #endregion
                #region 插入机台表
                var machineDal = new MachineDal(CurrDb);
                var machine = new Machine
                {
                    FCode = item.MacCode,
                    FName = item.MacCode,
                    IPAddress = item.IpAddress,
                    MacAddress = item.MacAddress,
                    DeviceID = 0,
                    FPort = item.FPort == 0 ? 5000 : item.FPort,
                    FactoryId = facId.Value,
                    RegionId = lineId,
                    LocDir = string.Empty,
                    MModeID = macModel.ID,
                    IsConn = 1,
                    IsControl = -1,
                    IsAnalysis = 1,
                    IsRemoveProgram = -1,
                    Source = "EAP"
                };
                if (CurrDb.InsertFor(machine, userCode) < 0)
                {
                    errorinfo = $"机台[{machine.FCode}]新增失败";
                    return -1;
                }
                var sql = "select @@identity;";
                var macid = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault());
                #endregion
                #region 插入制程关系
                if (!string.IsNullOrEmpty(item.PCode))
                {
                    var pCodeDal = new TProcessDal(CurrDb);
                    var pcode = pCodeDal.AddOrGet(new TProcess
                    {
                        FCode = item.PCode,
                        FName = item.PCode,
                        IsChange = 1,
                        UseImage = item.PCode
                    }, userCode, ref errorinfo);
                    if (pcode == null)
                    {
                        errorinfo = $"制程[{item.PCode}]新增失败";
                        return -1;
                    }
                    var mactProcess = new MacTProcess { MacID = macid.ToString(), PCode = item.PCode };
                    if (CurrDb.InsertFor(mactProcess, userCode) < 0)
                    {
                        errorinfo = $"机台[{item.MacCode}]制程关系新增失败";
                        return -1;
                    }
                }
                #endregion
                #region 插入机台编号
                if (!string.IsNullOrEmpty(item.MacNumber))
                {
                    MachineNumber num = new MachineNumber();
                    num.MacId = macid;
                    num.MacNumber = item.MacNumber;
                    if (CurrDb.InsertFor<MachineNumber>(num, userCode) < 0)
                    {
                        errorinfo = $"机台[{item.MacCode}]机台编号新增失败";
                        return -1;
                    }
                }
                #endregion
            }
            return 1;
        }

        public int UpdateMachines(MachineInfo[] machines, string userCode, ref string errorinfo)
        {
            foreach (var item in machines)
            {
                var ipRegex = new Regex(RegexExpression.IpAddressExp);
                if (!ipRegex.IsMatch(item.IpAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的IP地址[{item.IpAddress}]格式不正确";
                    return -1;
                }
                var macRegex = new Regex(RegexExpression.MacAddressExp);
                if (!macRegex.IsMatch(item.MacAddress))
                {
                    errorinfo = $"机台[{item.MacCode}]的MAC地址[{item.MacAddress}]格式不正确";
                    return -1;
                }
            }
            foreach (var item in machines)
            {
                #region 获取所在园区及线体
                if (string.IsNullOrEmpty(item.Factory) || string.IsNullOrEmpty(item.Plant) || string.IsNullOrEmpty(item.Floor) || string.IsNullOrEmpty(item.Line))
                {
                    errorinfo = $"机台[{item.MacCode}]的位置信息不完整,不能导入";
                    return -1;
                }
                var lineId = 0; int? facId = 0;
                lineId = GetRegionId(item, out facId, ref errorinfo);
                if (lineId < 0)
                {
                    return -1;
                }
                #endregion
                #region 获取机型
                var supplierDal = new SupplierDal(CurrDb);
                var supplier = supplierDal.AddOrGet(new Supplier { FCode = item.SupplierCode, FName = item.SupplierCode }, userCode, ref errorinfo);
                if (supplier == null)
                {
                    errorinfo = $"机台厂家[{item.SupplierCode}]新增失败";
                    return -1;
                }
                var macmodelDal = new MacModelDal(CurrDb);
                var macModel = new MacModel { FCode = item.MacModelCode, FName = item.MacModelCode, SupplierID = supplier.ID };
                macModel = macmodelDal.AddOrGet(macModel, userCode, ref errorinfo);
                if (macModel == null)
                {
                    errorinfo = $"机型[{item.MacModelCode}]新增失败";
                    return -1;
                }
                #endregion
                #region 更新机台表
                var machine = CurrDb.FindListForCondition<Machine>($" and a.fcode='{item.MacCode}'",
                  ref errorinfo).FirstOrDefault();

                #region 判断机台IP地址和MAC地址是否重复
                if (IsDuplicate("IpAddress", item.IpAddress, machine.ID))
                {
                    errorinfo = $"机台[{item.MacCode}]的IP地址[{item.IpAddress}]在数据库中已存在,请确认";
                    return -1;
                }
                if (IsDuplicate("MacAddress", item.MacAddress, machine.ID))
                {
                    errorinfo = $"机台[{item.MacCode}]的MAC地址[{item.MacAddress}]在数据库中已存在,请确认";
                    return -1;
                }
                #endregion

                var machineDal = new MachineDal(CurrDb);
                machine.IPAddress = item.IpAddress;
                machine.MacAddress = item.MacAddress;
                machine.FactoryId = facId.Value;
                machine.RegionId = lineId;
                machine.MModeID = macModel.ID;
                if (CurrDb.UpdateFor(machine, userCode) < 0)
                {
                    errorinfo = $"机台[{machine.FCode}]更新失败";
                    return -1;
                }

                #endregion
                #region 插入制程关系
                if (!string.IsNullOrEmpty(item.PCode))
                {
                    var pCodeDal = new TProcessDal(CurrDb);
                    var pcode = pCodeDal.AddOrGet(new TProcess
                    {
                        FCode = item.PCode,
                        FName = item.PCode,
                        IsChange = 1,
                        UseImage = item.PCode
                    }, userCode, ref errorinfo);
                    if (pcode == null)
                    {
                        errorinfo = $"制程[{item.PCode}]新增失败";
                        return -1;
                    }
                    var sql = $"DELETE from MActprocess where macid={machine.ID}";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        errorinfo = "删除原制程关系失败";
                        return -1;
                    }
                    var mactProcess = new MacTProcess { MacID = machine.ID.ToString(), PCode = item.PCode };
                    if (CurrDb.InsertFor(mactProcess, userCode) < 0)
                    {
                        errorinfo = $"机台[{item.MacCode}]制程关系新增失败";
                        return -1;
                    }
                }
                #endregion
                #region
                if (!string.IsNullOrEmpty(item.MacNumber))
                {
                    MachineNumber m = new MachineNumber();
                    List<MachineNumber> list = (List<MachineNumber>)CurrDb.FindList<MachineNumber>($"select * from machinenumber where macid={machine.ID}");
                    if (list.Count < 1)
                    {
                        m.MacId = machine.ID;
                        m.MacNumber = item.MacNumber;
                        if (CurrDb.InsertFor<MachineNumber>(m, userCode) < 0)
                        {
                            errorinfo = $"机台[{item.MacCode}]机台编号新增失败";
                            return -1;
                        }
                    }
                    else
                    {
                        m = list[0];
                        m.MacNumber = item.MacNumber;
                        m.ModCode = userCode;
                        if (CurrDb.UpdateFor<MachineNumber>(m, userCode) < 0)
                        {
                            errorinfo = $"机台[{item.MacCode}]机台编号修改失败";
                            return -1;
                        }
                    }

                }
                #endregion
            }
            return 1;
        }

        private int GetRegionId(MachineInfo item, out int? factoryId, ref string errorinfo)
        {
            var facDal = new FactoryRegionDal(CurrDb);
            var facId = facDal.Get(1, 1, "ID", "asc", $" and a.FName='{item.Factory.Trim()}'", errorinfo)
                .FirstOrDefault()?.Id;
            if (facId == null)
            {
                errorinfo = $"机台[{item.MacCode}]所在区域[{item.Factory}]无效";
                factoryId = null;
                return -1;
            }
            var plantId = facDal.Get(1, 1, "ID", "asc", $" and a.FName='{item.Plant.Trim()}' and " +
                $"a.parentId={facId}", errorinfo)
                .FirstOrDefault()?.Id;
            if (plantId == null)
            {
                errorinfo = $"机台[{item.MacCode}]所在区域[{item.Plant}]无效";
                factoryId = null;
                return -1;
            }
            var floorId = facDal.Get(1, 1, "ID", "asc", $" and a.FName='{item.Floor.Trim()}' and " +
               $"a.parentId={plantId}", errorinfo)
               .FirstOrDefault()?.Id;
            if (floorId == null)
            {
                errorinfo = $"机台[{item.MacCode}]所在区域[{item.Floor}]无效";
                factoryId = null;
                return -1;
            }
            var lineId = facDal.Get(1, 1, "ID", "asc", $" and a.FName='{item.Line.Trim()}' and " +
             $"a.parentId={floorId}", errorinfo)
             .FirstOrDefault()?.Id;
            if (lineId == null)
            {
                errorinfo = $"机台[{item.MacCode}]所在区域[{item.Line}]无效";
                factoryId = null;
                return -1;
            }
            factoryId = facId;
            return lineId.Value;
        }

        private bool IsDuplicate(string type, string address, int? macid = null)
        {
            string errorinfo = string.Empty;
            var filter = $" and a.{type}='{address}'";
            if (macid != null)
            {
                filter += $" and a.id<>{macid}";
            }
            Machine exist = CurrDb.FindListForCondition<Machine>(filter,
                ref errorinfo).FirstOrDefault();
            return exist != null;
        }
    }
}