JLH/JLHHJSvr/Helper/MattressHelper.cs

using CSUST.Data.Expr;
using DirectService.Tools;
using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using JLHHJSvr.LJFramework.Tools;
using LJLib.DAL.SQL;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace JLHHJSvr.Helper
{
    class MattressHelper : HelperBase
    {
        private Dictionary<string, object>  replacements = new Dictionary<string, object>();
        private Dictionary<string, string>  formula_replacements = new Dictionary<string, string>();

        public void CalCulateFormula(u_mattress mattress, List<u_mattress_mx_mtrl> mxlist)
        {
            InitMattress(mattress);

            InitMattressMx(mattress,mxlist);

            // 重算床网价格
            CalCulateBedNet(mxlist);

            InitReplaceMents(mattress);

            var diancengList = mxlist.Where(t => t.formulatype == 1).ToList();

            var mattersstype = new u_mattress_type() { mattresstypeid = mattress.mattresstypeid };
            DbSqlHelper.SelectOne(cmd, mattersstype, "formula_big_side,formula_small_side,formula_v_side");

            foreach (var mx in mxlist)
            {
                if (mx.mtrlid == 0 && mx.formulakind != 31) continue;

                if (string.IsNullOrEmpty(mx.formula) && string.IsNullOrEmpty(mx.useformula)) continue;

                InitMxReplaceMents(mx);

                string expression = ConvertToEnglishSymbols(mx.formula), use_expression = ConvertToEnglishSymbols(mx.useformula);

                expression = InitMxSideReplaceMents(mx, diancengList, mattersstype, expression);
                use_expression = InitMxSideReplaceMents(mx, diancengList, mattersstype,use_expression);

                #region 替换布料幅宽
                //大侧
                if (new List<int>() { 2,42,52,62,72,82 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothWidthReplaceMents(mx, mxlist,2);
                }
                //小侧
                if (new List<int>() { 3, 43, 53, 63, 73, 83 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothWidthReplaceMents(mx, mxlist,3);
                }
                //大侧
                if (new List<int>() { 4, 44, 54, 64, 74, 84 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothWidthReplaceMents(mx, mxlist,4);
                }
                //面裥绵
                if (new List<int>() { 0, 40, 50, 60, 70, 80, 203 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothWidthReplaceMents(mx, mxlist,0);
                }
                //底裥绵
                if (new List<int>() { 1, 41, 51, 61, 71, 81 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothWidthReplaceMents(mx, mxlist,1);
                }
                #endregion

                #region 替换布套高
                //内布套
                if (new List<int>() { 101,102,103,104,105,106 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothHeightReplaceMents(mxlist, 101);
                }
                //外布套
                if (new List<int>() { 111, 112, 113, 114, 115, 116 }.Contains(mx.formulakind.Value))
                {
                    InitMxClothHeightReplaceMents(mxlist, 111);
                }
                #endregion

                #region 裥绵收缩率
                InitMxShrinkageReplaceMents(mx,mxlist);
                #endregion

                foreach (var replacement in replacements)
                {
                    expression = expression.Replace(replacement.Key, Convert.ToString(replacement.Value));
                    use_expression = use_expression.Replace(replacement.Key, Convert.ToString(replacement.Value));
                }

                #region 计算成本金额
                // 判断expression前两个字符是否为@@
                bool sqlCal = !string.IsNullOrEmpty(expression) && expression.Substring(0, 2) == "@@";
                expression = expression.Replace("@@", "");
                mx.replace_formula = expression;

                if (sqlCal)
                {
                    mx.costamt = Convert.ToDecimal(SqlCalculate(expression, "成本金额"));
                }
                else
                {
                    mx.costamt = Calculate(expression, "成本金额").DecimalValue;
                }
                #endregion

                #region 计算清单用量
                sqlCal = !string.IsNullOrEmpty(use_expression) && use_expression.Substring(0, 2) == "@@";
                use_expression = use_expression.Replace("@@", "");
                mx.replace_useformula = use_expression;

                if (sqlCal)
                {
                    mx.useqty = Convert.ToDecimal(SqlCalculate(use_expression, "清单用量"));
                }
                else
                {
                    mx.useqty = Calculate(use_expression, "清单用量").DecimalValue;
                }
                #endregion
            }

            InitMattressMxListReplaceMents(mattress, mxlist);
        }
        private void CalCulateBedNet(List<u_mattress_mx_mtrl> mxlist)
        {
            var bedNetHelper = HelperBase.GetHelper<BedNetHelper>(cmd);

            var bednetList = mxlist.Where(t => t.formulatype == 99);

            foreach(var mx in bednetList)
            {
                if (mx.mtrlid <= 0) continue;
                var bednet = new u_bednet() { bednetid = mx.mtrlid };
                DbSqlHelper.SelectOne(cmd, bednet, "flag");

                if (bednet.flag == 1) continue;

                bedNetHelper.GetBedNet(bednet);

                var bednetMxlist = bedNetHelper.GetBedNetMxList(bednet.bednetid.Value);

                var bednetSpringList = bedNetHelper.GetBedNetSpringList(bednet.bednetid.Value);

                bedNetHelper.CalCulateFormula(bednet, bednetMxlist, bednetSpringList);

                bedNetHelper.SaveBedNet(bednet, bednetMxlist, bednetSpringList);

                mx.price = bednet.nottax_factory_cost;
            }
        }
        private void InitMattress(u_mattress mattress)
        {
            var dept = new u_dept() { deptid = mattress.deptid.Value };
            DbSqlHelper.SelectOne(cmd, dept, "pricelistid,profitrate,moneyrate,discount");
            var profirate = new u_factory_profitrate() { deptid = mattress.deptid, bednettypeid_mattresstypeid = mattress.mattresstypeid, bednet_or_mattress = 1 };
            DbSqlHelper.SelectOne(cmd, profirate, "profitrate");

            mattress.pricelistid = dept.pricelistid;

            mattress.dept_profitrate = mattress.profitrate;
            mattress.profitrate = profirate.profitrate;

            mattress.moneyrate = mattress.if_moneyrate == 1 ? dept.moneyrate ?? 1 : 1;
            mattress.discount = dept.discount ?? 1 ;
            mattress.commission = mattress.commission ?? 1;
            mattress.taxrate = mattress.taxrate ?? 1;
            mattress.other_rate = mattress.other_rate ?? 1;
            mattress.dept_profitrate = mattress.dept_profitrate ?? 0;

            // 检查佣金是否小于1
            if (mattress.commission < 1)
            {
                throw new LJCommonException("佣金点数不能小于1!");
            }

            // 检查税率是否小于1
            if (mattress.taxrate < 1)
            {
                throw new LJCommonException("税率不能小于1!");
            }

            // 检查额外点数是否小于1
            if (mattress.other_rate < 1)
            {
                throw new LJCommonException("税率不能小于1!");
            }

            // 检查部门利润率是否为0
            if (mattress.dept_profitrate == 0)
            {
                throw new LJCommonException("部门利润率不能为0!");
            }
        }
        private void InitMattressMx(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist)
        {
            foreach(var mx in mxlist)
            {
                var formula = new u_mattress_formula() { formulaid = mx.formulaid, };
                DbSqlHelper.SelectOne(cmd, formula, "formulakind,formulatype,sortcode");

                var mtrldef = new u_mtrl_price() { mtrlid = mx.mtrlid };
                DbSqlHelper.SelectOne(cmd, mtrldef, "name,price,gram_weight,cloth_width,if_inputqty,priceunit,shrinkage,if_areaprice");

                mx.formulakind = formula.formulakind;
                mx.formulatype = formula.formulatype;
                mx.sortcode = formula.sortcode.Trim();
                mx.mtrlname = mtrldef.name ?? "";

                var formula2 = new u_mattress_formula();
                DbSqlHelper.SelectOne(cmd, "u_mattress_formula","formulakind = @formulakind",
                    new Dictionary<string, object>() { { "@formulakind",mx.formulakind } },formula2, "formula,useformula,gydscrp");

                mx.formula = formula2.formula;
                mx.useformula = formula2.useformula;
                mx.gydscrp = formula2.gydscrp;

                cmd.CommandText = @"SELECT isnull(u_mtrl_price_pricelist.price, 0) AS pricelistprice
	                                    ,u_mtrl_price.gram_weight
	                                    ,u_mtrl_price.cloth_width
	                                    ,u_mtrl_price.if_inputqty
	                                    ,u_mtrl_price.priceunit
	                                    ,u_mtrl_price.shrinkage
	                                    ,isnull(u_mtrl_price_pricelist.price_formula, 0) AS price_formula
	                                    ,isnull(u_mtrl_price_pricelist.qty_formula, 0) AS qty_formula
	                                    ,u_mtrl_price.if_areaprice
	                                    ,u_mtrl_price.thickness
                                    FROM u_mtrl_price
                                    INNER JOIN u_mtrl_price_pricelist ON u_mtrl_price.mtrlid = u_mtrl_price_pricelist.mtrlid
                                    WHERE (u_mtrl_price.mtrlid = @mtrlid)
	                                    AND (u_mtrl_price_pricelist.pricelistid = @pricelistid)";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@mtrlid", mx.mtrlid);
                cmd.Parameters.AddWithValue("@pricelistid", mattress.pricelistid);
                using(var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        mx.price = Convert.ToDecimal(reader["pricelistprice"]);
                        mx.gram_weight = Convert.ToDecimal(reader["gram_weight"]);
                        mx.cloth_width = Convert.ToDecimal(reader["cloth_width"]);
                        mx.if_inputqty = Convert.ToInt32(reader["if_inputqty"]);
                        mx.priceunit = reader["priceunit"].ToString();
                        mx.shrinkage = Convert.ToDecimal(reader["shrinkage"]);
                        mx.if_areaprice = Convert.ToByte(reader["if_areaprice"]);

                        if(mx.if_inputqty == 1) mx.thickness = Convert.ToDecimal(reader["thickness"]);
                        string price_formula = Convert.ToString(reader["price_formula"]);
                        string qty_formula = Convert.ToString(reader["qty_formula"]);
                        mx.formula = string.IsNullOrEmpty(price_formula) ? "" : price_formula;
                        mx.useformula = string.IsNullOrEmpty(qty_formula) ? "" : qty_formula;
                    }
                }
            }
        }
        private void InitReplaceMents(u_mattress mattress)
        {
            replacements.Clear();

            AddKeyValue("规格宽", mattress.mattress_width);
            AddKeyValue("规格长", mattress.mattress_length);
            AddKeyValue("规格高", mattress.mattress_height);
            AddKeyValue("压包数量", mattress.packqty);
            AddKeyValue("卷包直径", mattress.diameter);
            AddKeyValue("顶布裥棉外布套做法", mattress.if_db_wbutao_way);
            AddKeyValue("面料外布套做法", mattress.if_m_wbutao_way);
            AddKeyValue("内布套上覆", mattress.s_cover_qty);
            AddKeyValue("内布套侧覆", mattress.z_cover_qty);
            AddKeyValue("内布套下覆", mattress.x_cover_qty);
            AddKeyValue("面料上覆", mattress.s_m_cover_qty);
            AddKeyValue("面料侧覆", mattress.z_m_cover_qty);
            AddKeyValue("面料下覆", mattress.x_m_cover_qty);
            AddKeyValue("面拆", mattress.if_m_chai);
            AddKeyValue("中拆", mattress.if_z_chai);
            AddKeyValue("底拆", mattress.if_d_chai);
            AddKeyValue("工厂利润率", mattress.profitrate);
            AddKeyValue("部门利润率", mattress.dept_profitrate);
            AddKeyValue("部门让利点数", mattress.dept_profitrate_rangli);
            AddKeyValue("佣金点数", mattress.commission);
            AddKeyValue("额外点数", mattress.other_rate);
            AddKeyValue("额外费用", mattress.extras_cost);
            AddKeyValue("汇率", mattress.moneyrate);
            AddKeyValue("税率", mattress.taxrate);
            AddKeyValue("折扣率", mattress.discount);
            // 地区FOB费用,如果存在变量表，会替代
            AddKeyValue("大柜-普通-地区FOB费用", 6500);
            AddKeyValue("大柜-特定-地区FOB费用", 8500);
            AddKeyValue("小柜-普通-地区FOB费用", 6000);
            AddKeyValue("小柜-特定-地区FOB费用", 7000);
            // 柜型立方数
            AddKeyValue("大柜-柜型立方数", 64);
            AddKeyValue("小柜-柜型立方数", 28);
            // 柜型米数
            AddKeyValue("大柜-柜型米数", 12);
            AddKeyValue("小柜-柜型米数", 5.9);
            // 公式
            AddFormulaKeyValue("【总成本】", "【总材料成本】*【大小单】+【款式费用】+【边带费用】+【额外费用】+【制造费用】");
            AddFormulaKeyValue("【不含税出厂价】", "【总成本】*(【工厂利润率】+【工艺点数】+【内布套点数】+【拆装点数】+【海绵扣点】)*【管理费点】");
            AddFormulaKeyValue("【部门不含税价】", "【不含税出厂价】/(【部门利润率】+【部门让利点数】/100)/(1-(【佣金点数】-1))*【额外点数】+【FOB】");
            AddFormulaKeyValue("【税金】", "【部门不含税价】*(【税率】-1)+【佣金】 * 0.05");
            AddFormulaKeyValue("【部门含税价】", "(【部门不含税价】+【佣金】 * 0.06)*【税率】*【折扣率】");
            AddFormulaKeyValue("【外币价】", "【部门含税价】/【汇率】");
            AddFormulaKeyValue("【底价】", "【底价】/（1-(【佣金点数】-1)-【底价】");
            AddFormulaKeyValue("【佣金】", "if((【不含税出厂价】+【部门利润】/100)<> 0,【不含税出厂价】/(【部门利润】+【部门让利点数】/ 100),0)");

            if (mattress.woodpallettype == 0)
            {
                AddKeyValue("木托方式", "'普通木托'");
            }else if (mattress.woodpallettype == 1)
            {
                AddKeyValue("木托方式", " '夹板木托'");
            }else
            {
                AddKeyValue("木托方式", "'铁管木托' ");
            }

            var dept = new u_dept() { deptid = mattress.deptid.Value };
            DbSqlHelper.SelectOne(cmd, dept, "pricelistid");

            var bednetVarList = new List<u_bednet_var>();
            var selectStr = @"SELECT u_bednet_varmx.varid AS varid
	                            ,varkind
	                            ,varcode
	                            ,varname
	                            ,varclass
	                            ,vartype
	                            ,varunit
	                            ,u_bednet_varmx.varvalue AS varvalue
                                ,formula
                            FROM u_bednet_var
                            INNER JOIN u_bednet_varmx ON u_bednet_varmx.varid = u_bednet_var.varid
                            INNER JOIN u_dept ON u_dept.pricelistid = u_bednet_varmx.pricelistid";
            var whereList = new List<string>();
            whereList.Add("u_bednet_var.varclass < 2");
            whereList.Add("u_bednet_var.varkind = @varkind");
            whereList.Add("u_dept.deptid = @deptid");
            var param = new Dictionary<string, object>();
            param.Add("@varkind", 1);
            param.Add("@deptid", mattress.deptid);
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "varid", "varid,varkind,varcode,varname,varclass,vartype,varunit,varvalue", 0, 0, bednetVarList);

            foreach (var netvar in bednetVarList)
            {
                AddKeyValue(netvar.varname, netvar.varvalue);
            }

            selectStr = @"SELECT varid
	                            ,varkind
	                            ,varcode
	                            ,varname
	                            ,varclass
	                            ,vartype
	                            ,varunit
                                ,formula
                            FROM u_bednet_var";
            bednetVarList = new List<u_bednet_var>();
            whereList = new List<string>();
            whereList.Add("u_bednet_var.varkind = @varkind");
            whereList.Add("u_bednet_var.varclass = @varclass");
            param = new Dictionary<string, object>();
            param.Add("@varkind", 1);
            param.Add("@varclass", 2);
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "varid", "varid,varkind,varcode,varname,varclass,vartype,varunit,formula", 0, 0, bednetVarList);

            // 添加替换公式变量
            foreach (var netvar in bednetVarList)
            {
                AddFormulaKeyValue(netvar.varname, netvar.formula);
            }
        }
        private void InitMxReplaceMents(u_mattress_mx_mtrl mx)
        {
            mx.mtrlname = !string.IsNullOrEmpty(mx.mtrlname) ? mx.mtrlname.Trim() : "null";

            mx.shrinkage = mx.shrinkage ?? 1;

            //取mtrldef.name的前两位
            AddKeyValue("名称前2位", mx.mtrlname.Substring(0, 2));
            AddKeyValue("物料单价", mx.price);
            AddKeyValue("物料克重", mx.gram_weight);
            AddKeyValue("幅宽", mx.cloth_width);
            AddKeyValue("数量", mx.qty);
            AddKeyValue("物料厚度", mx.if_inputqty);
            AddKeyValue("固定厚度", mx.if_inputqty);
            AddKeyValue("厚度", mx.thickness);
            AddKeyValue("按面积单价", mx.if_areaprice);
            AddKeyValue("物料名称", mx.mtrlname);
            AddKeyValue("物料收缩率", mx.shrinkage);
        }
        /// <summary>
        /// 添加大侧，小侧，V侧高度公式替换
        /// </summary>
        /// <param name="mx"></param>
        /// <param name="diancengList"></param>
        /// <param name="mattersstype"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        private string InitMxSideReplaceMents(u_mattress_mx_mtrl mx,List<u_mattress_mx_mtrl> diancengList, u_mattress_type mattersstype, string expression)
        {
            // 获取垫层的数据
            decimal bigSum = 0, smallSum = 0, vSum = 0;

            string bigChaStr = mx.chastr ?? "大侧";
            string smallChaStr = mx.chastr ?? "小侧1";
            string vChaStr = mx.chastr ?? "V侧1";

            foreach (var dianceng in diancengList)
            {
                if (dianceng.chastr.IndexOf(bigChaStr) > -1) bigSum += dianceng.qty.Value * dianceng.thickness.Value;
                if (dianceng.chastr.IndexOf(smallChaStr) > -1) smallSum += dianceng.qty.Value * dianceng.thickness.Value;
                if (dianceng.chastr.IndexOf(vChaStr) > -1) vSum += dianceng.qty.Value * dianceng.thickness.Value;
            }

            if (bigSum > 0)
            {
                AddKeyValue("大侧高度", bigSum);
            }

            if (smallSum > 0)
            {
                AddKeyValue("小侧高度", smallSum);
            }

            if (vSum > 0)
            {
                AddKeyValue("V侧高度", vSum);
            }

            #region 替换大侧高度
            if (expression.IndexOf("【大侧高度】") > -1)
            {
                if (!string.IsNullOrEmpty(mattersstype.formula_big_side)) expression = expression.Replace("【大侧高度】", $"({mattersstype.formula_big_side})");
                expression = expression.Replace("【位置】", $"'{bigChaStr}'");
            }
            #endregion

            #region 替换小侧高度
            if (expression.IndexOf("【小侧高度】") > -1)
            {
                if (!string.IsNullOrEmpty(mattersstype.formula_small_side)) expression = expression.Replace("【小侧高度】", $"({mattersstype.formula_small_side})");
                expression = expression.Replace("【位置】", $"'{smallChaStr}'");
            }
            #endregion

            #region 替换V侧高度
            if (expression.IndexOf("【V侧高度】") > -1)
            {
                if(!string.IsNullOrEmpty(mattersstype.formula_v_side)) expression = expression.Replace("【V侧高度】", $"({mattersstype.formula_v_side})");
                expression = expression.Replace("【位置】", $"'{vChaStr}'");
            }
            #endregion

            return expression;
        }
        /// <summary>
        /// 添加布料幅宽公式替换
        /// </summary>
        /// <param name="mx"></param>
        /// <param name="mxlist"></param>
        /// <param name="formulakind"></param>
        private void InitMxClothWidthReplaceMents(u_mattress_mx_mtrl mx, List<u_mattress_mx_mtrl> mxlist,int formulakind)
        {
            decimal cloth_width = 0;
            if (string.IsNullOrEmpty(mx.chastr))
            {
                cloth_width = mxlist.First(t => t.formulakind.Value == formulakind).cloth_width.Value;
            }
            else
            {
                cloth_width = mxlist.First(t => t.formulakind.Value == formulakind && t.chastr.Equals(mx.chastr)).cloth_width.Value;
            }

            AddKeyValue("布料幅宽", cloth_width);
        }
        /// <summary>
        /// 添加布套高公式替换
        /// </summary>
        /// <param name="mxlist"></param>
        /// <param name="formulakind"></param>
        private void InitMxClothHeightReplaceMents(List<u_mattress_mx_mtrl> mxlist, int formulakind)
        {
            decimal cloth_height = 0;
            cloth_height = mxlist.First(t => t.formulakind.Value == formulakind).thickness.Value;

            AddKeyValue("布套高", cloth_height);
        }
        /// <summary>
        /// 添加裥绵收缩率公式替换
        /// </summary>
        /// <param name="mx"></param>
        /// <param name="mxlist"></param>
        private void InitMxShrinkageReplaceMents(u_mattress_mx_mtrl mx, List<u_mattress_mx_mtrl> mxlist)
        {
            decimal thickness = 0, shrinkage = 0;
            if(new List<int>() { 0,40,50,60,70,80,1,41, 51,61,71,81, 2,42,52,62,72,82,3,43, 53,63,73,83,4,44, 54,64,74,84 }.Contains(mx.formulakind.Value))
            {
                foreach (var row in mxlist) // mxList.Rows等价于arg_dw.RowCount()
                {
                    if (new List<int>() { 50, 51, 52, 53, 54 }.Contains(row.formulakind.Value))
                    {
                        // 如果当前行的公式种类匹配
                        thickness += row.thickness.Value * row.qty.Value; // 简化计算
                    }
                }
            }
            

            if(thickness > 0)
            {
                cmd.CommandText = @"SELECT TOP 1 shrinkage
                                    FROM u_shrinkage
                                    WHERE u_shrinkage.minvalue <= @thickness
	                                    AND u_shrinkage.maxvalue >= @thickness";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@thickness", thickness);
                using(var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        shrinkage = Convert.ToDecimal(reader["shrinkage"]);
                    }
                }
            }

            if(shrinkage > 0)
            {
                AddKeyValue("收缩率", shrinkage);
            }
        }
        /// <summary>
        /// 添加主表与明细表相关公式替换
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        private void InitMattressMxListReplaceMents(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist)
        {
            string fob_expression = string.Empty,cubage_expression = string.Empty;

            cubage_expression = "（【规格长】*【规格宽】* 【规格高】）/100000";
            #region 判断装柜方式  是否进箱
            if (mattress.packtype == 0)
            {
                mattress.loading_type = "平压";
                fob_expression = "【地区FOB费用】/（【柜型米数】 /（【规格宽】/ 100 +0.1）*（【压包数量】*2））";

                if (mattress.packqty == 0) throw new LJCommonException("请填写压包数量!");
            }
            else if (mattress.packtype == 1)
            {
                mattress.loading_type = "卷包(非进箱)";
                fob_expression = "【地区FOB费用】 /（【柜型立方数】 /( 3.14*（ 【卷包直径】    /100/2+0.03）*（  【卷包直径】  /100/2+0.03）*（【规格宽】/ 100 +0.05）））";
                cubage_expression = "( 3.14*（ 【卷包直径】    /100/2+0.03）*（  【卷包直径】  /100/2+0.03）*（【规格宽】/ 100 +0.05））";

                if (mattress.packqty == 0) throw new LJCommonException("请填写卷包直径!");

                var _bzList = mxlist.Where(t => t.formulatype == 3 && t.formulakind == 19).ToList();

                foreach(var bz in _bzList)
                {
                    if (bz.mtrlname.IndexOf("箱") > -1) { 
                        mattress.loading_type = "卷包(进箱)";
                        fob_expression = "【地区FOB费用】/（【柜型立方数】 / (（ 【纸箱宽】  /100+0.03）*（【纸箱宽】/100+0.03）*（【规格宽】/ 100 +0.1）））";
                        cubage_expression = "（ 【纸箱宽】  /100+0.03）*（【纸箱宽】/100+0.03）*（【规格宽】/ 100 +0.1）";
                        AddKeyValue("【纸箱宽】", bz.mtrlname.Substring(0, 2));
                        break; 
                    }
                }
            }
            else if (mattress.packtype == 2)
            {
                mattress.loading_type = "国内";
                fob_expression = "【地区FOB费用】/（【柜型立方数】/（（【规格宽】/100+0.03）*（【规格长】/100+0.03）*（【规格高】/100+0.03）））";
            }
            #endregion

            #region 计算FOB
            if (string.IsNullOrEmpty(mattress.area) || string.IsNullOrEmpty(mattress.cabinet_type) || string.IsNullOrEmpty(mattress.loading_type)) return;

            //地区FOB费用
            if (new List<string>() { "大柜", "小柜" }.Contains(mattress.cabinet_type) && new List<string>() { "普通地区", "特定地区" }.Contains(mattress.area))
            {
                fob_expression = fob_expression.Replace("【地区FOB费用】", $"【{mattress.cabinet_type.Trim()}-{mattress.area.Trim()}-地区FOB费用】");
            }

            //柜型立方数
            if (new List<string>() { "大柜", "小柜" }.Contains(mattress.cabinet_type))
            {
                fob_expression = fob_expression.Replace("【柜型立方数】", $"【{mattress.cabinet_type.Trim()}-柜型立方数】");
            }

            //纸箱宽 见上面

            //柜型米数
            if (new List<string>() { "大柜", "小柜" }.Contains(mattress.cabinet_type))
            {
                fob_expression = fob_expression.Replace("【柜型米数】", $"【{mattress.cabinet_type.Trim()}-柜型米数】");
            }

            mattress.fob_replace_formula = fob_expression;
            mattress.fob = Calculate(fob_expression, "FOB").DecimalValue;
            //半成品:如果PE袋物料是含 半成品专用  的编码，FOB需要除以数量
            var bzList = mxlist.Where(t => t.formulatype == 3).ToList();

            foreach (var bz in bzList)
            {
                if (bz.qty.Value > 0 && bz.mtrlname.IndexOf("半成品专用") > -1)
                {
                    mattress.fob /= bz.qty.Value;

                    mattress.fob_replace_formula = $"{mattress.fob_replace_formula} / {bz.qty.Value}";
                }
            }

            AddKeyValue("FOB", mattress.fob);
            #endregion

            #region 计算体积
            if (mattress.fob > 0)
            {
                mattress.cubage = Calculate(cubage_expression, "体积").DecimalValue;
            }
            #endregion

            #region 计算人工费用
            var mattersstype = new u_mattress_type() { mattresstypeid = mattress.mattresstypeid };
            DbSqlHelper.SelectOne(cmd, mattersstype, "hrcost_formula,biandaiqty,zhizao_amt,guanli_rate");

            if(mattress.biandai_qty == 0 && mattress.if_m_chai == 0 && mattress.if_z_chai == 0 && mattress.if_d_chai == 0)
            {
                mattress.biandai_qty = mattersstype.biandaiqty;
            }

            mattress.hrcost_replace_formula = mattersstype.hrcost_formula;
            mattress.zhizao_amt = mattersstype.zhizao_amt;
            mattress.guanli_rate = mattersstype.guanli_rate;

            var diancengList = mxlist.Where(t => t.formulatype == 1).ToList();
            int diancengCount = 0;
            foreach(var dianceng in diancengList)
            {
                if (dianceng.formulatype == 32) diancengCount++;
            }

            AddKeyValue("垫层数量", diancengCount);
            AddKeyValue("制造费用", mattress.zhizao_amt);
            AddKeyValue("管理费点", mattress.guanli_rate);

            mattress.hrcost = Calculate(mattress.hrcost_replace_formula, "款式费用").DecimalValue;
            AddKeyValue("款式费用", mattress.hrcost);
            #endregion

            #region 计算边带费用
            mattress.biandaicost_replace_formula = "【边带条数】*【边带单价】*（（【规格宽】+【规格长】）*2+30）/100";

            AddKeyValue("边带条数", mattress.biandai_qty);

            mattress.biandaicost = Calculate(mattress.biandaicost_replace_formula, "边带费用").DecimalValue;
            AddKeyValue("边带费用", mattress.biandaicost);
            #endregion

            #region 辅料 有打钮纽扣 加0.13 工厂利润率
            var fuliaoList = mxlist.Where(t => t.formulatype == 2 && t.mtrlname.IndexOf("打钮") > -1).ToList();

            if (fuliaoList.Any()) mattress.profitrate += 0.13M;
            #endregion

            #region 拼侧 最大金额的侧  和   工厂利润率加点
            CalCulatePince(mattress, mxlist);
            #endregion

            #region 内布套特殊加点
            if(mattress.if_n_butao == 1) CalCulateNetBuTao(mattress, mxlist);
            #endregion

            #region 拆装特殊加点
            CalCulateChaiZhuang(mattress);
            #endregion

            #region 海绵款特殊扣点
            if (mattress.if_haimian_type == 1) CalCulateHaiMian(mattress);
            #endregion

            #region 计算主表 总材料成本
            mattress.total_material_cost = mxlist.Sum(t => t.costamt);
            AddKeyValue("总材料成本", mattress.total_material_cost);
            #endregion

            CalCulateCostNew(mattress, "标准");
            CalCulateCostNew(mattress, "大单");
            CalCulateCostNew(mattress, "散单");
        }
        /// <summary>
        /// 计算拼侧最大金额的侧和工厂利润率加点
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        private void CalCulatePince(u_mattress mattress, List<u_mattress_mx_mtrl> mxlist)
        {
            var mianliaoList = mxlist.Where(t => t.formulatype == 0).ToList();

            var costAmounts = new Dictionary<string, decimal>
            {
                { "普通大侧", 0m },
                { "上下拼侧1", 0m },
                { "上下拼侧2", 0m },
                { "上下拼侧3", 0m },
                { "左右拼侧1", 0m },
                { "左右拼侧2", 0m },
                { "左右拼侧3", 0m },
                { "左右拼侧4", 0m }
            };

            var costAmounts2 = new Dictionary<string, decimal>
            {
                { "小侧1-上下拼侧1", 0m },
                { "小侧1-上下拼侧2", 0m },
                { "小侧1-上下拼侧3", 0m }
            };

            foreach (var mianliao in mianliaoList)
            {
                if (costAmounts.ContainsKey(mianliao.sortcode))
                {
                    costAmounts[mianliao.sortcode] += mianliao.costamt.Value;
                }else if (costAmounts2.ContainsKey(mianliao.sortcode))
                {
                    costAmounts2[mianliao.sortcode] += mianliao.costamt.Value;
                }
            }

            // 找出最大拼侧金额及对应的拼侧名称
            string lsTempMaxStr = costAmounts.OrderByDescending(x => x.Value).First().Key;
            decimal ldeTempMax = costAmounts[lsTempMaxStr];

            // 清空不是最大金额的拼侧
            foreach (var mianliao in mianliaoList)
            {
                if (mianliao.sortcode != lsTempMaxStr && new List<int>() { 2, 82, 42, 52, 62, 72 }.Contains(mianliao.formulakind.Value))
                {
                    mianliao.costamt = 0;
                }
            }

            // 找出最大小侧拼侧金额及对应的拼侧名称
            string lsTempMaxStr2 = costAmounts2.OrderByDescending(x => x.Value).First().Key;
            decimal ldeTempMax2 = costAmounts2[lsTempMaxStr2];

            // 清空不是最大金额的拼侧
            foreach (var mianliao in mianliaoList)
            {
                if (mianliao.sortcode != lsTempMaxStr2 && new List<int>() { 3, 83, 43, 53, 63, 73 }.Contains(mianliao.formulakind.Value))
                {
                    mianliao.costamt = 0;
                }
            }

            // 计算拼侧数量
            int pinceCnt = costAmounts.Values.Count(value => value > 0);

            // 判断是否有大侧
            bool hasBigSide = mianliaoList.Any(row => new[] { 7, 8, 9, 10 }.Contains(row.formulakind.Value) && row.mtrlid.Value > 0);

            string field = pinceCnt > 3 || (hasBigSide && (pinceCnt == 2 || pinceCnt == 3)) ? "num_3" : hasBigSide ? "num_1" : pinceCnt == 2 || pinceCnt == 3 ? "num_2" : string.Empty;

            // 查找点数
            var workmanship = new u_workmanship_add() { deptid = mattress.deptid, mattresstypeid = mattress.mattresstypeid, workmanshipname = field };
            mattress.profitrate_point = workmanship.rate;
            AddKeyValue("工艺点数", mattress.profitrate_point);
        }
        /// <summary>
        /// 计算内布套特殊加点
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        private void CalCulateNetBuTao(u_mattress mattress, List<u_mattress_mx_mtrl> mxlist)
        {
            var butaoList = mxlist.Where(t => t.formulatype == 101 || t.formulatype == 102);
            bool butaoFz = false, butaoJd = false;
            // 检查内布套是否为复杂款
            foreach (var butao in butaoList) {
                if (butao.formulakind == 70 && butao.mtrlid > 0) { butaoFz = true; break; }
            }

            if (butaoFz)
            {
                foreach (var butao in butaoList)
                {
                    if (butao.formulakind == 203 && butao.mtrlid > 0) { butaoFz = true; break; }
                }
            }
            else
            {
                butaoJd = true; // 没有找到复杂款即为简单款
            }

            // 根据款式类型判断查询的字段
            string field = butaoJd ? "num_4" : (butaoFz ? "num_5" : "");

            // 查找点数
            var workmanship = new u_workmanship_add() { deptid = mattress.deptid, mattresstypeid = mattress.mattresstypeid, workmanshipname = field };
            mattress.butao_point = workmanship.rate;
            AddKeyValue("内布套点数", mattress.butao_point);
        }
        /// <summary>
        /// 拆装特殊加点
        /// </summary>
        /// <param name="mattress"></param>
        private void CalCulateChaiZhuang(u_mattress mattress)
        {
            int cnt = 0;

            // 判断 if_m_chai, if_z_chai, if_d_chai 是否为 1
            if (Convert.ToInt32(mattress.if_m_chai) == 1) cnt++;
            if (Convert.ToInt32(mattress.if_z_chai) == 1) cnt++;
            if (Convert.ToInt32(mattress.if_d_chai) == 1) cnt++;

            // 根据款式类型判断查询的字段
            string field = cnt == 1 ? "num_6" : (cnt > 1 ? "num_7" : "");

            // 查找点数
            var workmanship = new u_workmanship_add() { deptid = mattress.deptid, mattresstypeid = mattress.mattresstypeid, workmanshipname = field };
            mattress.chaizhuang_point = workmanship.rate;
            AddKeyValue("拆装点数", mattress.chaizhuang_point);
        }
        /// <summary>
        /// 计算海绵款特殊扣点
        /// </summary>
        /// <param name="mattress"></param>
        private void CalCulateHaiMian(u_mattress mattress)
        {
            // 根据款式类型判断查询的字段
            string field = "num_8";

            // 查找点数
            var workmanship = new u_workmanship_add() { deptid = mattress.deptid, mattresstypeid = mattress.mattresstypeid, workmanshipname = field };
            mattress.haimian_point = workmanship.rate;
            AddKeyValue("海绵扣点", mattress.haimian_point);
        }
        /// <summary>
        /// 计算大小单加点
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typename"></param>
        private void CalCulateDanNum(u_mattress mattress,string typename)
        {
            // 根据款式类型判断查询的字段
            string field = "散单".Equals(typename) ? "num_9" : "标准".Equals(typename) ? "num_10" : "大单".Equals(typename) ? "num_11" : "";

            // 查找点数
            var workmanship = new u_workmanship_add() { deptid = mattress.deptid, mattresstypeid = mattress.mattresstypeid, workmanshipname = field };
            mattress.dannum_rate = workmanship.rate;
            AddKeyValue("大小单", mattress.dannum_rate);
        }
        /// <summary>
        /// 计算成本
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typename"></param>
        private void CalCulateCostNew(u_mattress mattress, string typename)
        {
            #region 总成本
            CalCulateDanNum(mattress, typename);
            mattress.total_cost = CalculateVarFormula("总成本").DecimalValue;
            if (mattress.if_zhedie_type == 1) mattress.total_cost *= 2;
            AddKeyValue("总成本", mattress.total_cost);
            #endregion

            #region 不含税出厂价
            mattress.nottax_factory_cost = CalculateVarFormula("不含税出厂价").DecimalValue;
            AddKeyValue("不含税出厂价", mattress.nottax_factory_cost);
            #endregion

            #region 部门不含税价
            mattress.nottax_dept_cost = CalculateVarFormula("部门不含税价").DecimalValue;
            AddKeyValue("部门不含税价", mattress.nottax_dept_cost);
            #endregion

            #region 税金
            mattress.taxes = CalculateVarFormula("税金").DecimalValue;
            AddKeyValue("税金", mattress.taxes);
            #endregion

            #region 部门含税价=（部门不含税价+佣金*0.06）*税率*折扣率（fob>0)
            if (mattress.fob == 0) { 
                mattress.discount = 1;
                AddKeyValue("折扣率", mattress.discount);
            }

            mattress.taxes = CalculateVarFormula("部门含税价").DecimalValue;
            AddKeyValue("部门含税价", mattress.dept_cost);
            #endregion

            #region 外币价= 部门含税价/汇率
            if (mattress.moneyrate > 0)
            {
                if ("标准".Equals(typename)) mattress.foreign_cost_bz = mattress.dept_cost / mattress.moneyrate;
                else if ("大单".Equals(typename)) mattress.foreign_cost_dd = mattress.dept_cost / mattress.moneyrate;
                else if ("散单".Equals(typename)) mattress.foreign_cost = mattress.dept_cost / mattress.moneyrate;
            }
            #endregion
        }
        /// <summary>
        /// 计算成本
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typename"></param>
        private void CalCulateCost(u_mattress mattress, string typename)
        {
            #region 计算主表 总成本
            CalCulateDanNum(mattress, typename);
            mattress.total_cost = mattress.total_material_cost * mattress.dannum_rate + mattress.hrcost + mattress.biandaicost + mattress.extras_cost + mattress.zhizao_amt;

            if (mattress.if_zhedie_type == 1) mattress.total_cost *= 2;
            #endregion

            #region 不含税出厂价=总成本*工厂利润率
            mattress.nottax_factory_cost = mattress.total_cost * (mattress.profitrate + mattress.profitrate_point + mattress.butao_point + mattress.chaizhuang_point + mattress.haimian_point) * mattress.guanli_rate;
            #endregion

            #region 部门不含税价=不含税出厂价*部门利润率*佣金点数 * 额外点数  +FOB
            mattress.nottax_dept_cost = mattress.nottax_factory_cost /
                                      (mattress.dept_profitrate
                                      + mattress.dept_profitrate_rangli / 100)
                                      / (1 - (mattress.commission - 1))
                                      * mattress.other_rate
                                      + mattress.fob;
            #endregion

            #region 税金 = 部门不含税价 * 税率
            mattress.taxes = mattress.nottax_dept_cost * (mattress.taxrate - 1);
            //问题1,税金增加需加上佣金*0.05 #TODO
            decimal lde_rj = 0;
            mattress.taxes += lde_rj * 0.05M;
            #endregion

            #region 部门含税价=（部门不含税价+佣金*0.06）*税率*折扣率（fob>0)
            if (mattress.fob == 0) mattress.discount = 1;
            mattress.dept_cost = (mattress.nottax_dept_cost + lde_rj * 0.06m)
                               * mattress.taxrate
                               * mattress.discount;
            #endregion

            #region 外币价= 部门含税价/汇率
            if(mattress.moneyrate > 0)
            {
                if ("标准".Equals(typename)) mattress.foreign_cost_bz = mattress.dept_cost / mattress.moneyrate;
                else if ("大单".Equals(typename)) mattress.foreign_cost_dd = mattress.dept_cost / mattress.moneyrate;
                else if ("散单".Equals(typename)) mattress.foreign_cost = mattress.dept_cost / mattress.moneyrate;
            }
            #endregion
        }
        private void AddKeyValue(string key, object value, bool isReplace = true)
        {
            key = $"【{key}】";
            if (!replacements.ContainsKey(key))
            {
                replacements.Add(key, value);
            }
            else if (isReplace)
            {
                replacements[key] = value;
            }
        }
        private void AddFormulaKeyValue(string key, string value, bool isReplace = true)
        {
            if (!formula_replacements.ContainsKey(key))
            {
                formula_replacements.Add(key, value);
            }
            else if (isReplace)
            {
                formula_replacements[key] = value;
            }
        }
        private string ConvertToEnglishSymbols(string input)
        {
            input = input.Replace("（", "(")
                 .Replace("）", ")")
                 .Replace("，", ",")
                 .Replace("。", ".")
                 .Replace("：", ":")
                 .Replace("；", ";")
                 .Replace("“", "\"")
                 .Replace("”", "\"")
                 .Replace("‘", "'")
                 .Replace("’", "'");

            return input;
        }
        private TData CalculateVarFormula(string varname)
        {
            varname = $"【{varname}】";
            if (!formula_replacements.ContainsKey(varname)) return null;
            return Calculate(varname,varname);
        }
        private TData Calculate(string expression,string name="")
        {
            ConvertToEnglishSymbols(expression);

            // 定义正则表达式模式，匹配包含【】的内容
            string pattern = @"【(.*?)】";

            // 创建正则表达式对象
            Regex regex = new Regex(pattern);

            foreach (var replacement in formula_replacements)
            {
                expression = expression.Replace(replacement.Key, replacement.Value);
            }

            // 使用正则表达式匹配输入字符串
            MatchCollection matches = regex.Matches(expression);

            var new_replacements = new Dictionary<string, object>();

            foreach (Match match in matches)
            {
                if (replacements.TryGetValue(match.Value, out object value2))
                {
                    new_replacements[match.Value] = value2;
                }
            }

            foreach (var replacement in new_replacements)
            {
                expression = expression.Replace(replacement.Key, Convert.ToString(replacement.Value));
            }

            try
            {
                return LJExprParser.Parse(expression).Result;
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"计算{name}公式错误!expression: {expression},exception: {ex.Message}");
            }
        }

        private object SqlCalculate(string expression, string name = "")
        {
            ConvertToEnglishSymbols(expression);

            expression = expression.Replace("@@", "");

            // 定义正则表达式模式，匹配包含【】的内容
            string pattern = @"【(.*?)】";

            // 创建正则表达式对象
            Regex regex = new Regex(pattern);

            // 使用正则表达式匹配输入字符串
            MatchCollection matches = regex.Matches(expression);

            var new_formula_replacements = new Dictionary<string, string>();
            var new_replacements = new Dictionary<string, object>();

            foreach (Match match in matches)
            {
                // 检查原始字典中是否存在该键，并添加到新字典中
                if (formula_replacements.TryGetValue(match.Value, out string value))
                {
                    new_formula_replacements[match.Value] = value;
                }

                if (replacements.TryGetValue(match.Value, out object value2))
                {
                    new_replacements[match.Value] = value2;
                }
            }

            foreach (var replacement in new_formula_replacements)
            {
                expression = expression.Replace(replacement.Key, replacement.Value);
            }

            foreach (var replacement in new_replacements)
            {
                expression = expression.Replace(replacement.Key, Convert.ToString(replacement.Value));
            }

            try
            {
                cmd.CommandText = $@"{expression}";
                cmd.Parameters.Clear();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"计算{name}公式错误!expression: {expression},exception: {ex.Message}");
            }
        }

        public void SaveMattress(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist)
        {
            var fields = "mattressname, deptid, mattresscode, mattresstypeid, mattress_width, mattress_length, mattress_height, packtype, packqty, woodpallettype, total_hr_cost, total_material_cost, fees_dscrp, total_fees_cost, total_cost, taxrate, taxes, commissionrate, commission, fob, profitrate, dept_profitrate, moneyrate, nottax_factory_cost, nottax_dept_cost, foreign_cost, diameter, area, cabinet_type, hrcost, biandaicost, createtime, createby, mattressrelcode, other_rate, flag, dept_profitrate_rangli, profitrate_point, if_moneyrate, discount, if_m_chai, if_z_chai, if_d_chai, if_n_butao, if_w_butao, if_m_wbutao_way, s_cover_qty, z_cover_qty, x_cover_qty, biandai_qty, s_m_cover_qty, z_m_cover_qty, x_m_cover_qty, chaizhuang_point, haimian_point, if_zhedie_type, qr_auditingrep, qr_auditingdate, if_bcp_type, zhizao_amt, foreign_cost_bz, cubage";
            var fieldsMx = "mattressmxid,mattressid,formulaid,formula,replace_formula,if_success,priceunit,shrinkage,mtrlid,price,gram_weight,cloth_width,if_inputqty,qty ,costamt,if_areaprice,thickness,chastr,xu,useqty,useformula,replace_useformula,gydscrp";

            if (mattress.mattressid <= 0)
            {
                //新建
                mattress.createtime = context.opdate;
                mattress.createby = context.tokendata.username;

                mattress.mattressid = BllHelper.GetID(cmd, "u_mattress");
                fields = "mattressid," + fields;


                var mattresstype = new u_mattress_type() { mattresstypeid = mattress.mattresstypeid };
                DbSqlHelper.SelectOne(cmd, mattresstype, "typecode");


                mattress.mattresscode = $"{mattresstype.typecode}-{context.opdate.ToString("yyyyMMdd")}{(mattress.mattressid % 10000).ToString("D4")}";

                DbSqlHelper.Insert(cmd, "u_mattress", null, mattress, fields);
            }
            else
            {
                // 删除所有明细
                cmd.CommandText = @"DELETE u_mattress_mx_mtrl WHERE mattressid = @mattressid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@mattressid", mattress.mattressid);
                cmd.ExecuteNonQuery();

                DbSqlHelper.Update(cmd, "u_mattress", null, mattress, "mattressid", fields);
            }
            
            if (mxlist != null && mxlist.Any())
            {
                foreach (var item in mxlist)
                {
                    item.mattressid = mattress.mattressid;
                    if (item.mattressmxid == null || item.mattressmxid <= 0)
                    {
                        item.mattressmxid = BllHelper.GetID(cmd, "u_mattress_mx_mtrl");
                    }

                    DbSqlHelper.Insert(cmd, "u_mattress_mx_mtrl", null, item, fieldsMx);
                }

            }

            // 更新计价历史
            var hisprice = new u_his_price
            {
                bednetid_mattressid = mattress.mattressid,
                typeid = 1,
                cmpdate = context.opdate,
                cmpemp = context.tokendata.username,
                nottax_dept_cost = mattress.nottax_dept_cost,
                dept_cost = mattress.dept_cost,
                foreign_cost = mattress.foreign_cost
            };
            var fieldsHs = "bednetid_mattressid, typeid, cmpdate, cmpemp, nottax_dept_cost, dept_cost, foreign_cost";
            DbSqlHelper.Insert(cmd, "u_his_price", null, hisprice, fieldsHs);
        }
    }
}