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 Dictionary<string,FormulaItem> _formula; // 公式

        public CalculateFormula() 
        {
            _formula = new Dictionary<string, FormulaItem>();
        }

        public void AddFormulaItem(FormulaItem item) 
        {
            if(_formula.ContainsKey(item.formula_name))
            {
                _formula.Remove(item.formula_name);
            }
            _formula.Add(item.formula_name, item);
        }

        public void AddFormulaItem(string name,decimal value)
        {
            AddFormulaItem(new FormulaItem() { formula_name = name, value = value,isConst = true });
        }

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

        public FormulaItem GetFormulaItem(string name,bool isConst = false)
        {
            return _formula[name];
        }

        /// <summary>
        /// 主计算入口
        /// </summary>
        public void CalculateAll()
        {
            var graph = BuildDependencyGraph(_formula);
            var sorted = TopologicalSort(graph);
            foreach (var name in sorted)
            {
                var item = _formula[name];
                // 常量直接赋值
                if (item.isConst)
                {
                    //item.formula_transform = item.formula;
                    //item.value = Convert.ToDecimal(item.formula);
                    continue;
                }
                // 变量公式
                item.formula = ConvertToEnglishSymbols(item.formula);
                string expanded = ExpandFormula(item.formula, _formula);
                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 || kv.Value.isConst) 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)
            {
                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_transform).Result.DecimalValue;
                formula.isConst = true;
            }
            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}");
            }
        }
    }
}