1

hutongqing

2025-01-02 8c6fd742db249ad4cc819cf041eb98d880a3ef73

 WIDESEAWCS_Server/WIDESEAWCS_Core/BaseServices/ServiceBase.cs

@@ -2,7 +2,9 @@
using Magicodes.ExporterAndImporter.Core.Models;
using Magicodes.ExporterAndImporter.Excel;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using SqlSugar;
using System.Drawing.Drawing2D;
@@ -10,6 +12,7 @@
using System.Linq.Expressions;
using System.Reflection;
using WIDESEAWCS_Core.BaseRepository;
using WIDESEAWCS_Core.Const;
using WIDESEAWCS_Core.Enums;
using WIDESEAWCS_Core.Helper;
using WIDESEAWCS_Core.Utilities;
@@ -46,49 +49,23 @@

        public virtual PageGridData<TEntity> GetPageData(PageDataOptions options)
        {
            string wheres = ValidatePageOptions(options);
            ISugarQueryable<TEntity> sugarQueryable = Db.Queryable<TEntity>();

            string wheres = ValidatePageOptions(options, ref sugarQueryable);
            //获取排序字段
            Dictionary<string, OrderByType> orderbyDic = GetPageDataSort(options, TProperties);

            int total = 0;
            PageGridData<TEntity> pageGridData = new PageGridData<TEntity>();
            //if (QueryRelativeExpression != null)
            //{
            //    queryable = QueryRelativeExpression.Invoke(queryable);
            //}
            //if (options.Export)
            //{
            //    queryable = queryable.GetIQueryableOrderBy(orderbyDic);
            //    if (Limit > 0)
            //    {
            //        queryable = queryable.Take(Limit);
            //    }
            //    pageGridData.rows = queryable.ToList();
            //}
            //else
            //{
            //    pageGridData.rows = repository.IQueryablePage(queryable,
            //                        options.Page,
            //                        options.Rows,
            //                        out int rowCount,
            //                        orderbyDic).ToList();
            //    pageGridData.total = rowCount;
            //    //查询界面统计求等字段
            //    if (SummaryExpress != null)
            //    {
            //        pageGridData.summary = SummaryExpress.Invoke(queryable);
            //        //Func<T, T> groupExpress = x =>x;
            //        //pageGridData.summary = queryable.GroupBy(groupExpress).Select(SummaryExpress).FirstOrDefault();
            //    }
            //}
            //GetPageDataOnExecuted?.Invoke(pageGridData);

            pageGridData = BaseDal.QueryPage(wheres, options.Page, options.Rows, orderbyDic);
            pageGridData.Rows = sugarQueryable.ToPageList(options.Page, options.Rows, ref total);
            pageGridData.Total = total;

            //pageGridData = BaseDal.QueryPage(wheres, options.Page, options.Rows, orderbyDic);

            return pageGridData;
        }


        protected string ValidatePageOptions(PageDataOptions options)
        protected string ValidatePageOptions(PageDataOptions options, ref ISugarQueryable<TEntity> sugarQueryable)
        {
            options = options ?? new PageDataOptions();
            string where = "";
@@ -115,33 +92,135 @@
                    continue;
                }

                PropertyInfo property = TProperties.Where(c => c.Name.ToUpper() == searchParametersList[i].Name.ToUpper()).FirstOrDefault();
                PropertyInfo? property = TProperties.Where(c => c.Name.ToUpper() == searchParametersList[i].Name.ToUpper()).FirstOrDefault();

                if (property == null) continue;

                (bool, string, object) result = property.ValidationVal(searchParametersList[i].Value.Split(','));
                if (!result.Item1)
                List<(bool, string, object)> results = property.ValidationValueForDbType(searchParametersList[i].Value.Split(',')).ToList();
                if (results == null || results.Count() == 0)
                {
                    continue;
                }
                for (int j = 0; j < results.Count(); j++)
                {
                    LinqExpressionType expressionType = searchParametersList[i].DisplayType.GetLinqCondition();
                    Expression<Func<TEntity, bool>> expression = GetWhereExpression(property.Name, results[j].Item3, null, expressionType);
                    sugarQueryable = sugarQueryable.Where(expression);

                LinqExpressionType expressionType = searchParametersList[i].DisplayType.GetLinqCondition();
                if (expressionType == LinqExpressionType.Equal)
                {
                    if (string.IsNullOrEmpty(where))
                        where += $"{searchParametersList[i].Name} = '{searchParametersList[i].Value}'";
                    else
                        where += $" and {searchParametersList[i].Name} = '{searchParametersList[i].Value}'";
                    //if (j == 0)
                    //{
                    //    where += "(";
                    //}
                    //LinqExpressionType expressionType = searchParametersList[i].DisplayType.GetLinqCondition();
                    //if (expressionType == LinqExpressionType.Equal)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.Equal} '{results[j].Item3}'";
                    //}
                    //else if (expressionType == LinqExpressionType.ThanOrEqual)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.ThanOrEqual} '{searchParametersList[i].Value}'";
                    //}
                    //else if (expressionType == LinqExpressionType.LessThanOrEqual)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.LessOrEqual} '{searchParametersList[i].Value}'";
                    //}
                    //else if (expressionType == LinqExpressionType.GreaterThan)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.GT} '{searchParametersList[i].Value}'";
                    //}
                    //else if (expressionType == LinqExpressionType.LessThan)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.LT} '{searchParametersList[i].Value}'";
                    //}
                    //else if (expressionType == LinqExpressionType.Contains)
                    //{
                    //    where += $"{searchParametersList[i].Name} {HtmlElementType.like} '%{searchParametersList[i].Value}%'";
                    //}
                    //else
                    //{
                    //    where += $"{searchParametersList[i].Name} {searchParametersList[i].DisplayType} '{results[j].Item3}'";
                    //}

                    //if (j == results.Count() - 1)
                    //{
                    //    where += ")";
                    //}
                    //else
                    //{
                    //    where += " or ";
                    //}
                }
                else
                {
                    if (string.IsNullOrEmpty(where))
                        where += $"{searchParametersList[i].Name} {searchParametersList[i].DisplayType} '{searchParametersList[i].Value}'";
                    else
                        where += $" and {searchParametersList[i].Name} {searchParametersList[i].DisplayType} '{searchParametersList[i].Value}'";
                }
                //if (i < searchParametersList.Count - 1)
                //    where += " and ";
            }
            return where;
        }

        private Expression<Func<TEntity, bool>> GetWhereExpression(string propertyName, object propertyValue, ParameterExpression parameter, LinqExpressionType expressionType)
        {
            Type? proType = typeof(TEntity).GetProperty(propertyName)?.PropertyType;
            if (proType == null) return p => true;
            if (propertyValue == null) return p => true;
            string? value = propertyValue?.ToString();
            if (value == null) return p => true;
            ConstantExpression constant = proType.ToString() == "System.String"
               ? Expression.Constant(propertyValue) : Expression.Constant(value.ChangeType(proType));

            // DateTime只选择了日期的时候自动在结束日期加一天，修复DateTime类型使用日期区间查询无法查询到结束日期的问题
            if ((proType == typeof(DateTime) || proType == typeof(DateTime?)) && expressionType == LinqExpressionType.LessThanOrEqual && value.Length == 10)
            {
                constant = Expression.Constant(Convert.ToDateTime(value).AddDays(1));
            }
            parameter = parameter ?? Expression.Parameter(typeof(TEntity), "x");
            //创建节点的属性p=>p.name 属性name
            MemberExpression memberProperty = Expression.PropertyOrField(parameter, propertyName);
            UnaryExpression member = Expression.Convert(memberProperty, constant.Type);
            Expression<Func<TEntity, bool>> expression = p => false;
            switch (expressionType)
            {
                case LinqExpressionType.Equal:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.Equal(member, constant), parameter);
                    break;
                case LinqExpressionType.NotEqual:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.NotEqual(member, constant), parameter);
                    break;
                case LinqExpressionType.GreaterThan:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.GreaterThan(member, constant), parameter);
                    break;
                case LinqExpressionType.LessThan:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.LessThan(member, constant), parameter);
                    break;
                case LinqExpressionType.ThanOrEqual:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.GreaterThanOrEqual(member, constant), parameter);
                    break;
                case LinqExpressionType.LessThanOrEqual:
                    expression = Expression.Lambda<Func<TEntity, bool>>(Expression.LessThanOrEqual(member, constant), parameter);
                    break;
                case LinqExpressionType.Contains:
                case LinqExpressionType.NotContains:
                    MethodInfo? method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                    if (method != null)
                    {
                        constant = Expression.Constant(propertyValue, typeof(string));
                        if (expressionType == LinqExpressionType.Contains)
                        {
                            expression = Expression.Lambda<Func<TEntity, bool>>(Expression.Call(member, method, constant), parameter);
                        }
                        else
                        {
                            expression = Expression.Lambda<Func<TEntity, bool>>(Expression.Not(Expression.Call(member, method, constant)), parameter);
                        }
                    }
                    else
                    {
                        expression = p => true;
                    }
                    break;
                default:
                    expression = p => false;
                    break;
            }
            return expression;
        }

        /// <summary>
@@ -150,7 +229,7 @@
        /// <param name="pageData"></param>
        /// <param name="propertyInfo"></param>
        /// <returns></returns>
        private Dictionary<string, OrderByType> GetPageDataSort(PageDataOptions pageData, PropertyInfo[] propertyInfo)
        protected Dictionary<string, OrderByType> GetPageDataSort(PageDataOptions pageData, PropertyInfo[] propertyInfo)
        {
            if (!string.IsNullOrEmpty(pageData.Sort))
            {
@@ -164,11 +243,11 @@
                    }
                    return sortDic;
                }
                else if (propertyInfo.Any(x => x.Name == pageData.Sort))
                else if (propertyInfo.Any(x => x.Name == pageData.Sort.FirstLetterToLower() || x.Name == pageData.Sort.FirstLetterToUpper()))
                {
                    return new Dictionary<string, OrderByType> {
                        {
                            pageData.Sort,pageData.Order?.ToLower() == OrderByType.Asc.ToString() ? OrderByType.Asc : OrderByType.Desc
                            pageData.Sort,pageData.Order?.ToLower() == OrderByType.Asc.ToString().ToLower() ? OrderByType.Asc : OrderByType.Desc
                        } };
                }
            }
@@ -267,17 +346,64 @@
                        saveModel.MainData.Remove(keyPro.Name.FirstLetterToLower());
                    }
                }

                TEntity entity = saveModel.MainData.DicToModel<TEntity>();
                if (saveModel.DetailData == null || saveModel.DetailData.Count == 0)
                {
                    BaseDal.AddData(entity);
                    return WebResponseContent.Instance.OK();
                }

                BaseDal.AddData(entity);
                if (typeof(TEntity).GetNavigatePro() == null)
                {
                    return WebResponseContent.Instance.Error("未配置导航属性");
                }

                return WebResponseContent.Instance.OK();
                Type detailType = typeof(TEntity).GetDetailType();
                MethodInfo? methodInfo = GetType().GetMethod(nameof(AddDataIncludesDetail));
                methodInfo = methodInfo?.MakeGenericMethod(new Type[] { detailType });
                object? obj = methodInfo?.Invoke(this, new object[] { entity, detailType, saveModel.DetailData });
                return obj as WebResponseContent;
            }
            catch (Exception ex)
            {
                return WebResponseContent.Instance.Error(ex.Message);
            }
        }

        public WebResponseContent AddDataIncludesDetail<TDetail>(TEntity entity, Type detailType, List<Dictionary<string, object>> detailDics) where TDetail : class, new()
        {
            WebResponseContent content = new WebResponseContent();
            try
            {
                string name = typeof(TEntity).GetMainIdByDetail();
                string reslut = detailType.ValidateDicInEntity(detailDics, true, new string[] { name });
                if (reslut != string.Empty)
                    return WebResponseContent.Instance.Error(reslut);

                List<TDetail> list = detailDics.DicToIEnumerable<TDetail>();

                ((SqlSugarClient)BaseDal.Db).BeginTran();

                int id = BaseDal.Db.Insertable(entity).ExecuteReturnIdentity();

                for (int i = 0; i < list.Count; i++)
                {
                    TDetail detail = list[i];
                    typeof(TDetail).SetDetailId(detail, id, name);
                }

                BaseDal.Db.Insertable(list).ExecuteCommand();

                ((SqlSugarClient)BaseDal.Db).CommitTran();

                content = WebResponseContent.Instance.OK();
            }
            catch (Exception ex)
            {
                ((SqlSugarClient)BaseDal.Db).RollbackTran();
                content = WebResponseContent.Instance.Error(ex.Message);
            }
            return content;
        }

        /// <summary>
@@ -335,39 +461,131 @@
                    return WebResponseContent.Instance.Error(validResult);
                }

                //PropertyInfo keyPro = typeof(TEntity).GetKeyProperty();
                //if (keyPro == null)
                //{
                //    return WebResponseContent.Instance.Error("请先设置主键");
                //}
                PropertyInfo keyPro = typeof(TEntity).GetKeyProperty();
                if (keyPro == null)
                {
                    return WebResponseContent.Instance.Error("请先设置主键");
                }

                TEntity entity = saveModel.MainData.DicToModel<TEntity>();

                List<string> listCol = new List<string>();
                foreach (var item in saveModel.MainData)
                //List<string> listCol = new List<string>();
                //foreach (var item in saveModel.MainData)
                //{
                //    PropertyInfo propertyInfo = typeof(TEntity).GetProperty(item.Key);
                //    if (propertyInfo == null)
                //    {
                //        propertyInfo = typeof(TEntity).GetProperty(item.Key.FirstLetterToLower());
                //        if (propertyInfo == null)
                //        {
                //            propertyInfo = typeof(TEntity).GetProperty(item.Key.FirstLetterToUpper());
                //        }
                //    }

                //    listCol.Add(propertyInfo?.Name);
                //}

                if (saveModel.DetailData == null || saveModel.DetailData.Count == 0)
                {
                    PropertyInfo propertyInfo = typeof(TEntity).GetProperty(item.Key);
                    if (propertyInfo == null)
                    {
                        propertyInfo = typeof(TEntity).GetProperty(item.Key.FirstLetterToLower());
                        if (propertyInfo == null)
                        {
                            propertyInfo = typeof(TEntity).GetProperty(item.Key.FirstLetterToUpper());
                        }
                    }

                    listCol.Add(propertyInfo?.Name);
                    //if (list != null)
                    //    listCol = listCol.Where(x => !list.Contains(x)).ToList();
                    bool result = BaseDal.UpdateData(entity);
                    return WebResponseContent.Instance.OK();
                }
                if (list != null)
                    listCol = listCol.Where(x => !list.Contains(x)).ToList();
                bool result = BaseDal.UpdateData(entity, listCol, list);

                return WebResponseContent.Instance.OK();
                if (typeof(TEntity).GetNavigatePro() == null)
                {
                    return WebResponseContent.Instance.Error("未配置导航属性");
                }

                Type detailType = typeof(TEntity).GetDetailType();
                MethodInfo? methodInfo = GetType().GetMethod(nameof(UpdateDataInculdesDetail));
                methodInfo = methodInfo?.MakeGenericMethod(new Type[] { detailType });
                object? obj = methodInfo?.Invoke(this, new object[] { entity, detailType, saveModel.DetailData, saveModel.DelKeys });
                return obj as WebResponseContent;
            }
            catch (Exception ex)
            {
                return WebResponseContent.Instance.Error(ex.Message);
            }
        }

        public WebResponseContent UpdateDataInculdesDetail<TDetail>(TEntity entity, Type detailType, List<Dictionary<string, object>> detailDics, List<object> delKeys) where TDetail : class, new()
        {
            WebResponseContent content = new WebResponseContent();
            try
            {
                string name = typeof(TEntity).GetMainIdByDetail();
                string reslut = detailType.ValidateDicInEntity(detailDics, true, new string[] { name });
                if (reslut != string.Empty)
                    return WebResponseContent.Instance.Error(reslut);

                List<TDetail> list = detailDics.DicToIEnumerable<TDetail>();

                List<object> dynamicDelKeys = new List<object>();
                if (delKeys != null)
                {
                    for (int i = 0; i < delKeys.Count; i++)
                    {
                        dynamicDelKeys.Add(delKeys[i]);
                    }
                }

                List<TDetail> updateRows = new List<TDetail>();
                List<TDetail> addRows = new List<TDetail>();

                for (int i = 0; i < list.Count; i++)
                {
                    object detailId = typeof(TDetail).GetPropertyValue(list[i], typeof(TDetail).GetKeyName());
                    if (detailId != null)
                    {
                        if (detailId.ToString() != "0")
                        {
                            updateRows.Add(list[i]);
                        }
                        else
                        {
                            addRows.Add(list[i]);
                        }
                    }
                }

                object mainId = typeof(TEntity).GetPropertyValue(entity, typeof(TEntity).GetKeyName());
                if (mainId != null)
                {
                    ((SqlSugarClient)BaseDal.Db).BeginTran();

                    if (dynamicDelKeys.Count > 0)
                        BaseDal.Db.Deleteable<object>().AS(detailType.Name).Where($"{detailType.GetKeyName()} in (@id)", new { id = dynamicDelKeys.ToArray() }).ExecuteCommandHasChange();

                    BaseDal.Db.Updateable(entity).ExecuteCommandHasChange();

                    BaseDal.Db.Updateable(updateRows).ExecuteCommand();

                    for (int i = 0; i < addRows.Count; i++)
                    {
                        TDetail detail = addRows[i];
                        typeof(TDetail).SetDetailId(detail, mainId, name);
                    }

                    BaseDal.Db.Insertable(addRows).ExecuteCommand();

                    ((SqlSugarClient)BaseDal.Db).CommitTran();

                    content = WebResponseContent.Instance.OK();
                }
                else
                {
                    content = WebResponseContent.Instance.Error("未找到主表主键值");
                }

            }
            catch (Exception ex)
            {
                ((SqlSugarClient)BaseDal.Db).RollbackTran();
                content = WebResponseContent.Instance.Error(ex.Message);
            }
            return content;
        }

        /// <summary>
@@ -396,10 +614,40 @@
        {
            try
            {
                return BaseDal.DeleteDataByIds(keys) ? WebResponseContent.Instance.OK() : WebResponseContent.Instance.Error();
                if (typeof(TEntity).GetNavigatePro() == null)
                    return BaseDal.DeleteDataByIds(keys) ? WebResponseContent.Instance.OK() : WebResponseContent.Instance.Error();
                else
                {
                    if (keys != null)
                    {
                        Type detailType = typeof(TEntity).GetDetailType();
                        string name = typeof(TEntity).GetMainIdByDetail();
                        List<object> dynamicDelKeys = new List<object>();

                        for (int i = 0; i < keys.Length; i++)
                        {
                            dynamicDelKeys.Add(keys[i]);
                        }
                        ((SqlSugarClient)BaseDal.Db).BeginTran();

                        if (dynamicDelKeys.Count > 0)
                            BaseDal.Db.Deleteable<object>().AS(detailType.Name).Where($"{name} in (@id)", new { id = dynamicDelKeys.ToArray() }).ExecuteCommandHasChange();

                        BaseDal.DeleteDataByIds(keys);

                        ((SqlSugarClient)BaseDal.Db).CommitTran();

                        return WebResponseContent.Instance.OK();
                    }
                    else
                    {
                        return WebResponseContent.Instance.Error("参数错误");
                    }
                }
            }
            catch (Exception ex)
            {
                ((SqlSugarClient)BaseDal.Db).RollbackTran();
                return WebResponseContent.Instance.Error(ex.Message);
            }
        }
@@ -449,10 +697,10 @@
            try
            {
                Type t = typeof(TEntity);

                ISugarQueryable<TEntity> sugarQueryable = Db.Queryable<TEntity>();
                string savePath = AppDomain.CurrentDomain.BaseDirectory + $"ExcelExport";
                IExporter exporter = new ExcelExporter();
                string wheres = ValidatePageOptions(options);
                string wheres = ValidatePageOptions(options, ref sugarQueryable);
                //获取排序字段
                Dictionary<string, OrderByType> orderbyDic = GetPageDataSort(options, TProperties);

@@ -553,5 +801,24 @@
            content = WebResponseContent.Instance.OK(data: savePath + "\\" + fileName);
            return content;
        }

        public WebResponseContent ExportSeedData()
        {
            WebResponseContent content = new WebResponseContent();
            try
            {
                string seedDataFolder = $"WIDESEAWCS_DB.DBSeed.Json/{typeof(TEntity).Name}.tsv";
                List<TEntity> deviceInfos = BaseDal.QueryData();
                string str = JsonConvert.SerializeObject(deviceInfos, Formatting.Indented);
                List<Dictionary<string, object>> keyValuePairs = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(str);
                FileHelper.WriteFileAndDelOldFile($"{AppDomain.CurrentDomain.BaseDirectory}wwwroot/{seedDataFolder}", str);
                content = WebResponseContent.Instance.OK();
            }
            catch (Exception ex)
            {
                content = WebResponseContent.Instance.Error(ex.Message);
            }
            return content;
        }
    }
}