JLH/JLHHJSvr/Tools/CalculateFormula.cs

using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using JLHHJSvr.LJFramework.Tools;
using NPOI.SS.Formula;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;

namespace JLHHJSvr.Tools
{
    public class CalculateFormula
    {
        private HashSet<FormulaItem> _formula; // 公式
        private HashSet<FormulaItem> _const; // 常量

        public CalculateFormula() 
        {
            _formula = new HashSet<FormulaItem>();
            _const = new HashSet<FormulaItem>();
        }

        public void AddFormulaItem(FormulaItem item) 
        {
            if(!_formula.Add(item))
            {
                _formula.Remove(item);
                _formula.Add(item);
            }
        }
        public void AddConstFormulaItem(FormulaItem item)
        {
            if (!_const.Add(item))
            {
                _const.Remove(item);
                _const.Add(item);
            }
        }

        public void AddFormulaItem(string name,object value)
        {
            AddConstFormulaItem(new FormulaItem() { formula_name = name, value = value });
        }

        public void AddFormulaItem(string name, string formula)
        {
            var formulaItem = new FormulaItem() { formula_name = name, formula = formula };
            AddFormulaItem(formulaItem);
        }

        /// <summary>
        /// 主计算入口
        /// </summary>
        public void CalculateAll()
        {
            var dict = _formula.ToDictionary(f => f.formula_name, f => f);
            foreach (var c in _const)
            {
                dict[c.formula_name] = c;
            }

            var graph = BuildDependencyGraph(dict);
            var sorted = TopologicalSort(graph);
            foreach (var name in sorted)
            {
                var item = dict[name];
                item.formula = ConvertToEnglishSymbols(item.formula);
                // 展开
                string expanded = ExpandFormula(item.formula, dict);
                item.formula_transform = expanded;
                // 计算
                if (item.isSql) SqlCalculate(item);
                else Calculate(item);
            }
        }

        /// <summary>
        /// 解析依赖
        /// </summary>
        private Dictionary<string, List<string>> BuildDependencyGraph(Dictionary<string, FormulaItem> dict)
        {
            var graph = new Dictionary<string, List<string>>();

            foreach (var kv in dict)
            {
                var deps = new List<string>();

                foreach (var name in dict.Keys)
                {
                    if (name == kv.Key) continue;
                    if (Regex.IsMatch(kv.Value.formula, name))
                    {
                        deps.Add(name);
                    }
                }

                graph[kv.Key] = deps;
            }

            return graph;
        }

        /// <summary>
        /// 拓扑排序
        /// </summary>
        private List<string> TopologicalSort(Dictionary<string, List<string>> graph)
        {
            var result = new List<string>();
            var visited = new Dictionary<string, int>(); // 0=未访问,1=正在访问,2=已完成

            foreach (var node in graph.Keys)
            {
                if (!visited.ContainsKey(node))
                {
                    Dfs(node, graph, visited, result);
                }
            }

            return result;
        }

        private void Dfs(string node, Dictionary<string, List<string>> graph,Dictionary<string, int> visited, List<string> result)
        {
            if (visited.ContainsKey(node) && visited[node] == 1) throw new InvalidOperationException($"检测到循环依赖: {node}");

            if (visited.ContainsKey(node) && visited[node] == 2) return;

            visited[node] = 1;
            foreach (var dep in graph[node])
            {
                Dfs(dep, graph, visited, result);
            }

            visited[node] = 2;
            result.Add(node);
        }

        /// <summary>
        /// 展开公式，把变量替换为已计算的值
        /// </summary>
        private string ExpandFormula(string formula, Dictionary<string, FormulaItem> dict)
        {
            foreach (var kv in dict)
            {
                if (kv.Value.value != null) // 已经算过
                {
                    formula = Regex.Replace(formula, $"【{kv.Key}】", kv.Value.value.ToString());
                }
            }
            return formula;
        }

        private string ConvertToEnglishSymbols(string input)
        {
            input = input.Replace("（", "(")
                 .Replace("）", ")")
                 .Replace("，", ",")
                 .Replace("。", ".")
                 .Replace("：", ":")
                 .Replace("；", ";")
                 .Replace("“", "\"")
                 .Replace("”", "\"")
                 .Replace("‘", "'")
                 .Replace("’", "'");

            return input;
        }
        /// <summary>
        /// 公式计算
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private void Calculate(FormulaItem formula)
        {
            try
            {
                formula.value = LJExprParser.Parse(formula.formula).Result;
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"计算{formula.formula_name}公式错误: {ex.Message}");
            }
        }
        /// <summary>
        /// Sql公式计算
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private void SqlCalculate(FormulaItem formula)
        {
            try
            {
                //cmd.CommandText = $@"{formula.formula}";
                //cmd.Parameters.Clear();
                //return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"计算{formula.formula_name}公式错误: {ex.Message}");
            }
        }
    }
}