everlight.eap/backend/DllEapDal/MacModelProgramDal.cs

using Cksoft.Data;
using DllEapEntity;
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Extensions.Configuration;
using Cksoft.Unity;
using System.Linq;
using System.IO;
using DllEapEntity.Dtos;
using Cksoft.Data.Repository;
using DllEapEntity.Rms;
using Cksoft.Unity.Log4NetConfig;

namespace DllEapDal
{
    public class MacModelProgramDal
    {
        private string programBaseDir;
        private IDatabase CurrDb;
        private string commonFilter = null;
        public MacModelProgramDal(IDatabase db)
        {
            CurrDb = db;
            this.programBaseDir = AppConfigurtaionServices.Configuration.GetValue<string>("MacModelProgramDir");
        }

        public MacModelProgramDal(IDatabase db, string userCode)
        {
            CurrDb = db;
            this.programBaseDir = AppConfigurtaionServices.Configuration.GetValue<string>("MacModelProgramDir");
            //var smDal = new StaffMachineDal(CurrDb);
            //string errorinfo = string.Empty;
            // var idFilter = smDal.GetFilter(userCode, ref errorinfo);
            // commonFilter = idFilter.Replace("a.id", "b.id");
        }

        #region MacModelProgram
        public IEnumerable<MacModelProgram> Get()
        {
            var models = CurrDb.FindList<MacModelProgram>();
            return models;
        }

        public IEnumerable<MacModelProgram> Get(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            var versions = CurrDb.FindListForCondition<BusinessFileRelation>($"{filter} order by {sort} {order} limit {start - 1},{length} ", ref errorinfo);
            return versions.Select(c => new MacModelProgram
            {
                ID = c.ID,
                FileOrgName = c.FileOrgName,
                IsDefault = c.IsDefault,
                ModCode = c.ModCode,
                MacModelID = c.OrgID,
                Version = c.Version,
                ModTime = c.ModTime,
                RecCode = c.RecCode,
                ProgramName = c.ProgramName,
                RecTime = c.RecTime,
                Remark = c.remark,
                FileInfoRemark = c.FileInfoRemark,
                UploadMacCode = c.UploadMacCode
            });
        }

        public int GetVersionCount(string filter)
        {
            string errorinfo = string.Empty;
            var versions = CurrDb.FindListForCondition<BusinessFileRelation>($"{filter}", ref errorinfo);
            return versions.Count();
        }

        /// <summary>
        /// 获取机型程序列表
        /// </summary>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <param name="order"></param>
        /// <param name="sort"></param>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<MacProgramDto> GetGrouped(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            var sql = $"select a.Id,a.FName as ProgramName,b.FCode MacModelCode,b.FName MacModelName,b.Id MacModelId " +
                    $"from businessfile a " +
                    $"inner join MacModel b on a.OrgId=b.Id and a.OrgTypeID=2 " +
                    $"where 1=1 {filter} and a.OrgId in (select MModeId from machine where 1=1 {commonFilter} ) " +
                    $"order by {sort} {order} limit {start - 1},{length} ";
            var datas = CurrDb.FindList<MacProgramDto>(sql);
            return datas;
        }

        public int GetGroupedCount(string filter)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            string sql = $"select count(1) " +
                      $"from businessfile a " +
                      $"inner join MacModel b on a.OrgId=b.Id and a.OrgTypeID=2 " +
                      $"where 1=1 {filter} and a.OrgId in (select MModeId from machine where 1=1 {commonFilter} ) ";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        /// <summary>
        /// 设置默认版次
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isDefault"></param>
        /// <param name="macId"></param>
        /// <param name="programName"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int UpdateField(int id, int isDefault, int macId, string programName, ref string errorinfo)
        {
            if (isDefault != -1)
            {
                var filter = $" and a.IsDefault=1 and b.OrgId={macId} and b.OrgTypeID=2 and b.FName='{programName}'";
                var entity = CurrDb.FindListForCondition<BusinessFileRelation>(filter, ref errorinfo).FirstOrDefault();
                if (entity != null)
                {
                    if (entity.ID != id)
                    {
                        errorinfo = "当前机型已经存在默认程序，如需重新设置请先取消原来的默认程序";
                    }
                    else
                    {
                        errorinfo = "当前程序版次已经是默认版次，无需重复设置";
                    }
                    return -1;
                }
            }
            string sql = $"update BusinessFileRelation set isDefault={isDefault} where id={id}";
            return CurrDb.ExecuteBySql(sql);
        }
        #endregion
        public BusinessFileRelation getBusinessFileRelation(int id)
        {
            return CurrDb.FindEntityFor<BusinessFileRelation>(id);
        }
        public IEnumerable<BusinessFile> getBusinessFile(BusinessFileRelation macProgram)
        {
            string sql = $"select * from BusinessFile where OrgId='{macProgram.OrgID}' and Fname='{macProgram.ProgramName}' and OrgTypeID =2 ";
            return CurrDb.FindList<BusinessFile>(sql);
        }
        /// <summary>
        /// 删除单个版次
        /// </summary>
        /// <param name="id"></param>
        /// <param name="macModelCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int DeleteSingVersion(int id, string macModelCode, ref string errorinfo)
        {
            var macmodel = CurrDb.FindListForCondition<MacModel>($" and a.FCode='{macModelCode}'", ref errorinfo);
            var macProgram = CurrDb.FindEntityFor<BusinessFileRelation>(id);
            if (macProgram != null)
            {
                var sql = $"delete from BusinessFileRelation where id={id}";
                if (CurrDb.ExecuteBySql(sql) < 0)
                {
                    errorinfo = "删除数据库中程序版次失败";
                    return -1;
                }
                var macPros = CurrDb.FindListForCondition<BusinessFileRelation>($" and b.OrgId='{macProgram.OrgID}' and b.OrgTypeID=2 " +
                  $"and b.FName='{macProgram.ProgramName}'", ref errorinfo);
                if (macPros == null || macPros.Count() <= 0)
                {
                    sql = $"delete from BusinessFile where OrgId='{macProgram.OrgID}' and Fname='{macProgram.ProgramName}' and OrgTypeID =2 ";
                    if (CurrDb.ExecuteBySql(sql) < 0)
                    {
                        errorinfo = "删除程序版次失败";
                        return -1;
                    }
                }
            }
            return 1;
        }
        public int getBusinessFilebusinessId(string proName, int macModelId, ref string errorinfo)
        {
            var businessFile = CurrDb.FindListForCondition<BusinessFile>($" and a.OrgId={macModelId} and OrgTypeID=2 " +
                $" and a.FName='{proName}'", ref errorinfo)
                .FirstOrDefault();
            return businessFile.ID;
        }
        public IEnumerable<BusinessFileRelation> getBusinessFileRelationbusinessId(int businessId)
        {
            string sql = "select * from businessfilerelation where BusinessFileId = '{businessId}'";
            return CurrDb.FindList<BusinessFileRelation>(sql);
        }
        public BusinessFile getBusinessFilebusinessId(int businessId)
        {
            return CurrDb.FindEntityFor<BusinessFile>(businessId);
        }
        /// <summary>
        /// 删除整个程序
        /// </summary>
        /// <param name="proName"></param>
        /// <param name="macId"></param>
        /// <param name="macModelId"></param>
        /// <param name="macModelCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int DeleteWholeProgram(string proName, int macId, int macModelId, string macModelCode, ref string errorinfo)
        {
            var businessFile = CurrDb.FindListForCondition<BusinessFile>($" and a.OrgId={macModelId} and OrgTypeID=2 " +
                $" and a.FName='{proName}'", ref errorinfo)
                .FirstOrDefault();
            var businessId = businessFile.ID;
            var sql = $"delete from businessfilerelation where BusinessFileId='{businessId}'";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                errorinfo = "删除版次失败";
                return -1;
            }
            sql = $"delete from businessfile where id={businessId}";
            if (CurrDb.ExecuteBySql(sql) < 0)
            {
                errorinfo = "删除机型程序表失败";
                return -1;
            }
            return 1;
        }

        /// <summary>
        /// 机台复制程序产生新程序
        /// </summary>
        /// <param name="proId"></param>
        /// <param name="newProName"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int CopyProgramWithNewName(string proName, string newProName, int macModelId, string userCode, ref string errorinfo)
        {
            using (IDatabase db = DbFactory.Base("eap"))
            {
                db.BeginTrans();
                var businessFile = CurrDb.FindListForCondition<BusinessFile>($" and a.OrgId={macModelId} and a.OrgTypeID=2 " +
                    $" and a.FName='{proName}'",
                    ref errorinfo).FirstOrDefault();
                if (businessFile == null)
                {
                    errorinfo = "该程序不存在或已被删除";
                    return -1;
                }
                var temp = db.FindListForCondition<BusinessFile>($" and a.OrgId={macModelId} and a.OrgTypeID=2 and a.FName='{newProName}'",
                    ref errorinfo);
                if (temp != null && temp.Count() > 0)
                {
                    errorinfo = "程序名已存在";
                    return -1;
                }
                businessFile.FName = newProName;
                if (CurrDb.InsertFor(businessFile, userCode) < 0)
                {
                    errorinfo = "复制失败";
                    return -1;
                }
                var sql = $"select @@identity;";
                var id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
                var mac = CurrDb.FindListForCondition<Machine>($" and a.MModeID={macModelId}",
                    ref errorinfo).FirstOrDefault();

                var versions = CurrDb.FindListForCondition<BusinessFileRelation>($" and a.businessFileId={businessFile.ID}", ref errorinfo);
                if (versions != null && versions.Count() > 0)
                {
                    foreach (var item in versions)
                    {
                        var newPro = new BusinessFileRelation()
                        {
                            BusinessFileID = id,
                            FileInfoID = item.FileInfoID,
                            Version = item.Version,
                            IsDefault = item.IsDefault,
                            RecTime = DateTime.Now,
                            ModTime = DateTime.Now
                        };
                        if (CurrDb.InsertFor(newPro, userCode) < 0)
                        {
                            errorinfo = "程序信息保存数据库失败";
                            db.Rollback();
                            return -1;
                        }
                    }
                }
                var program = CurrDb.FindListForCondition<ProgramMst>($" and a.FName='{newProName}' " +
                  $"and a.ModelID='{macModelId}' and a.ProcessCode='{mac.PCode}' ",
                  ref errorinfo).FirstOrDefault();
                if (program == null)
                {
                    var proMst = new ProgramMst
                    {
                        FName = newProName,
                        ModelID = mac.MModeID,
                        ProcessCode = mac.PCode,
                        RecCode = userCode,
                        RecTime = DateTime.Now,
                        ModCode = userCode,
                        ModTime = DateTime.Now,
                        Remark = $"从机型【{mac.MModeCode}】上复制而来"
                    };
                    if (CurrDb.InsertFor(proMst, userCode) < 0)
                    {
                        errorinfo = "程序表新增数据失败";
                        return -1;
                    }
                }
                db.Commit();
                return 1;
            }
        }

        /// <summary>
        /// 将机台程序设置为机型通用程序
        /// </summary>
        /// <param name="macCode"></param>
        /// <param name="userCode"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public int CopyMacProgrmToModel(int macId, int macModelId, string userCode, string fName, ref string errorinfo)
        {
            using (IDatabase db = DbFactory.Base("eap"))
            {
                db.BeginTrans();
                var macPrograms = CurrDb.FindListForCondition<BusinessFile>($" and a.OrgTypeID=1 and a.OrgID={macId} and FName='{fName}'", ref errorinfo);
                if (macPrograms == null || macPrograms.Count() <= 0)
                {
                    errorinfo = "该机台下没有程序";
                    return -1;
                }
                foreach (var item in macPrograms)
                {
                    var temp = CurrDb.FindListForCondition<BusinessFile>($" and a.FName='{item.FName}' " +
                        $"and a.OrgTypeID=2 and a.OrgId={macModelId}", ref errorinfo).FirstOrDefault();
                    var id = 0;
                    if (temp != null)
                    {
                        id = temp.ID;
                    }
                    else
                    {
                        var newPro = new BusinessFile()
                        {
                            FName = item.FName,
                            ModTime = DateTime.Now,
                            OrgID = macModelId,
                            OrgTypeID = 2,
                            RecTime = DateTime.Now,
                        };
                        if (CurrDb.InsertFor(newPro, userCode) < 0)
                        {
                            errorinfo = "程序信息保存数据库失败";
                            db.Rollback();
                            return -1;
                        }
                        var sql = $"select @@identity;";
                        id = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "-1");
                    }
                    var versions = CurrDb.FindListForCondition<BusinessFileRelation>($" and a.businessFileId={item.ID}", ref errorinfo);
                    if (versions != null && versions.Count() > 0)
                    {
                        //var exists = CurrDb.FindListForCondition<BusinessFileRelation>($" and b.FName='{item.FName}' " +
                        //        $"and b.OrgTypeID=2 and b.OrgId={macModelId}", ref errorinfo);

                        var exists = CurrDb.FindListForCondition<BusinessFileRelation>($"  and a.businessFileId={id}", ref errorinfo);

                        if (exists != null && exists.Count() > 0)
                        {
                            var sql = $"delete from BusinessFileRelation where id in ({string.Join(",", exists.Select(c => c.ID))})";
                            if (CurrDb.ExecuteBySql(sql) < 0)
                            {
                                errorinfo = "删除原程序关系失败";
                                return -1;
                            }
                        }
                        foreach (var v in versions)
                        {
                            var obj = new BusinessFileRelation
                            {
                                FileInfoID = v.FileInfoID,
                                IsDefault = v.IsDefault,
                                Version = v.Version,
                                BusinessFileID = id,
                                RecTime = DateTime.Now,
                                ModTime = DateTime.Now
                            };
                            if (CurrDb.InsertFor(obj, userCode) < 0)
                            {
                                db.Rollback();
                                errorinfo = "新增失败";
                                return -1;
                            }
                        }
                    }
                    var machine = CurrDb.FindEntityFor<Machine>(macId);
                    var program = CurrDb.FindListForCondition<ProgramMst>($" and a.FName='{item.FName}' " +
                 $"and a.ModelID='{macModelId}' and a.ProcessCode='{machine.PCode}' ",
                 ref errorinfo).FirstOrDefault();
                    if (program == null)
                    {
                        var proMst = new ProgramMst
                        {
                            FName = item.FName,
                            ModelID = machine.MModeID,
                            ProcessCode = machine.PCode,
                            RecCode = userCode,
                            RecTime = DateTime.Now,
                            ModCode = userCode,
                            ModTime = DateTime.Now,
                            Remark = $"从机型【{machine.MModeCode}】上复制而来"
                        };
                        if (CurrDb.InsertFor(proMst, userCode) < 0)
                        {
                            errorinfo = "程序表新增数据失败";
                            return -1;
                        }
                    }
                }
                db.Commit();
                return 1;
            }
        }

        public IEnumerable<MacModelProgram> GetMacModelProgramVersionList(int start, int length, string order, string sort, string filter, string errorinfo)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            string sql = $@"select a.ID,a.BusinessFileID,a.FileInfoID,a.Version,a.IsDefault,a.remark,a.RecTime,
                            b.OrgID,b.OrgTypeID,b.FName ProgramName,c.UploadMacID,c.FName FileOrgName,c.remark FileInfoRemark,
                            d.FCode UploadMacCode,e.FCode MacModelCode,e.FName MacModelName,e.Id MacModelId
                            from businessfilerelation a
                            left join businessfile b on a.BusinessFileId =b.id
                            left join tfileinfo c on a.FileInfoId =c.id
                            left join machine d on c.UploadMacID =d.id
                            left join macmodel e on b.OrgId=e.Id
                            where b.OrgTypeID=2 {filter}
                            order by {sort} {order} limit {start - 1},{length} ";
            return CurrDb.FindList<MacModelProgram>(sql);
        }

        public int GetMacModelProgramVersionListCount(string filter)
        {
            if (!string.IsNullOrEmpty(filter))
                filter = filter.Replace("%2B", "+");
            string sql = $@"select count(1) from businessfilerelation a
                            left join businessfile b on a.BusinessFileId =b.id
                            where b.OrgTypeID=2 {filter}";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }
    }
}