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>();
        /// <summary>
        /// 默认展示公式
        /// </summary>
        private Dictionary<string, string> formula_replacements = new Dictionary<string, string>();
        /// <summary>
        /// 隐藏公式
        /// </summary>
        private Dictionary<string, string> formula_replacements_hide = new Dictionary<string, string>();

        public List<replacement> Replacements
        {
            get
            {
                var res = new List<replacement>();
                foreach (var item in replacements)
                {
                    res.Add(new replacement { label = item.Key, value = item.Value, type = 0 });
                }
                return res;
            }
        }
        public List<replacement> FormulaReplacements
        {
            get
            {
                var res = new List<replacement>();
                foreach (var item in formula_replacements)
                {
                    res.Add(new replacement { label = item.Key, value = item.Value, type = 0 });
                }
                foreach (var item in formula_replacements_hide)
                {
                    res.Add(new replacement { label = item.Key, value = item.Value, type = 1 });
                }
                return res;
            }
        }

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

            InitMattressMx(mattress,mxlist);

            // 重算床网价格
            if(isCalBed) 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.Length > 2 && 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.Length > 2 &&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, new HelperBase.Context() { tokendata = context.tokendata });

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

            foreach(var mx in bednetList)
            {
                if (mx.mtrlid <= 0) continue;

                var bednet = bedNetHelper.GetBedNet(mx.mtrlid.Value);
                if (bednet.flag == 1) continue;

                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");
            decimal profitrate = 0;
            cmd.CommandText = @"SELECT profitrate FROM u_factory_profitrate 
                            WHERE deptid = @deptid 
                                AND  bednettypeid_mattresstypeid = @bednettypeid_mattresstypeid 
                                AND  bednet_or_mattress = 1";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@deptid", mattress.deptid);
            cmd.Parameters.AddWithValue("@bednettypeid_mattresstypeid", mattress.mattresstypeid);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    profitrate = Convert.ToDecimal(reader["profitrate"]);
                }
            }

            mattress.pricelistid = dept.pricelistid;

            mattress.dept_profitrate = dept.profitrate;
            mattress.profitrate = 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) ? mx.formula : price_formula;
                        mx.useformula = string.IsNullOrEmpty(qty_formula) ? mx.useformula : 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("【底价】", "if((【部门利润率】+【部门让利点数】/100)<> 0,【不含税出厂价】/(【部门利润率】+【部门让利点数】/ 100),0)");
            AddFormulaKeyValue("【佣金】", "【底价】/（1-(【佣金点数】-1))-【底价】");
            //AddFormulaHideKeyValue("【总材料成本】", "【弹簧材料总成本】+【蛇线材料成本】+【四周口袋弹簧成本】+【四周口袋无纺布成本】+【四周加硬材料成本】+【胶水材料成本】+【入袋无纺布材料成本】+【底面无纺布材料成本】+【边铁材料成本】+【C钉/夹码材料成本】+【海绵包边材料成本】+【填充海绵成本】+【封边材料成本】+【弹叉材料成本】+【胶条/包角材料成本】+【上垫层物料成本】+【下垫层物料成本】+【包装总成本】");


            if (mattress.woodpallettype == 0)
            {
                AddKeyValue("木托方式", "'普通木托'");
            }else if (mattress.woodpallettype == 1)
            {
                AddKeyValue("木托方式", " '夹板木托'");
            }else if (mattress.woodpallettype == 2)
            {
                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 == 0 ? 1 : mx.shrinkage;

            //取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 = "大侧";
            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("垫层", vSum);
            }

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

            #region 替换小侧高度
            if (expression.IndexOf("【小侧高度】") > -1)
            {
                if (!string.IsNullOrEmpty(mattersstype.formula_small_side)) expression = expression.Replace("【小侧高度】", $"({mattersstype.formula_small_side})");
                expression = expression.Replace("【位置】", $"'{smallChaStr}'");
                expression = expression.Replace("【小侧高度】", $"{smallSum}");
            }
            #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}'");
                expression = expression.Replace("【V侧高度】", $"18");
            }
            #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;

            int[][] formulaKinds = new int[][]
            {
                new int [] {50, 0, 40, 60, 70, 80},
                new int [] {51, 1, 41, 61, 71, 81},
                new int [] {52, 2, 42, 62, 72, 82},
                new int [] {53, 3, 43, 63, 73, 83},
                new int [] {54, 4, 44, 64, 74, 84},
            };
            foreach (int[] kindGroup in formulaKinds)
            {
                if (kindGroup.Contains(mx.formulakind.Value))
                {
                    foreach (var row in mxlist) // mxList.Rows等价于arg_dw.RowCount()
                    {
                        if (row.formulakind.Value == kindGroup[0])
                        {
                            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.diameter == 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))
            {
                //地区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 bzList2 = mxlist.Where(t => t.formulatype == 3).ToList();

                foreach (var bz in bzList2)
                {
                    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 内布套特殊加点
            CalCulateNetBuTao(mattress, mxlist);
            #endregion

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

            #region 海绵款特殊扣点
            CalCulateHaiMian(mattress);
            #endregion

            #region 计算主表 总材料成本
            var aa = new List<decimal>();
            mxlist.ForEach(t => {
                aa.Add(t.costamt.Value);
            });
            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];

            if(ldeTempMax2 > 0)
            {
                // 清空不是最大金额的拼侧
                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;

            // 查找点数
            mattress.profitrate_point = GetWorkmanshipRate(mattress, field);
            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)
        {
            if(mattress.if_n_butao == 1)
            {
                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 = GetWorkmanshipRate(mattress, field);
            }
            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" : "");

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

                // 查找点数
                mattress.haimian_point = GetWorkmanshipRate(mattress, field);
            }
            AddKeyValue("海绵扣点", mattress.haimian_point);
        }
        /// <summary>
        /// 获取工厂利润率
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public decimal GetWorkmanshipRate(u_mattress mattress, string field)
        {
            decimal rate = 0;
            if (field == "") return 0;
            //cmd.CommandText = @"SELECT rate FROM u_workmanship_add WHERE deptid = @deptid AND  mattresstypeid = @mattresstypeid AND  workmanshipname = @workmanshipname";
            cmd.CommandText = @"SELECT " + field + " FROM u_workmanship_dianshu WHERE deptid = @deptid AND  mattresstypeid = @mattresstypeid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@deptid", mattress.deptid);
            cmd.Parameters.AddWithValue("@mattresstypeid", mattress.mattresstypeid);
            //cmd.Parameters.AddWithValue("@workmanshipname", field);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    rate = reader[field] == null ? 0 : Convert.ToDecimal(reader[field]);
                }
            }
            return rate;
        }
        /// <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" : "";

            // 查找点数
            decimal _rate = GetWorkmanshipRate(mattress, field);
            mattress.dannum_rate = _rate == 0 ? 1 : _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 底价
            decimal _dijia = CalculateVarFormula("底价").DecimalValue;
            AddKeyValue("底价", _dijia);
            #endregion

            #region 佣金
            decimal _yongjin = CalculateVarFormula("佣金").DecimalValue;
            AddKeyValue("佣金", _yongjin);
            #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.dept_cost = 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;
            }
            decimal _waibi = CalculateVarFormula("外币价").DecimalValue;
            AddKeyValue("外币价", _waibi);
            #endregion

        }
        /// <summary>
        /// 保存床垫报价
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        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;

                if (string.IsNullOrEmpty(mattress.mattresscode))
                {
                    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);
        }
        public u_mattress GetMattress(int mattressid)
        {
            if(mattressid <= 0)
            {
                throw new LJCommonException("查找床垫报价失败,ID错误!");
            }

            var mattress = new u_mattress() { mattressid = mattressid };
            var fields = @"mattressid,mattressname,deptid,mattresstypeid,mattresscode,mattress_width,mattress_length,mattress_height,packtype,packqty,woodpallettype,
                        total_hr_cost,total_material_cost,fees_dscrp,total_fees_cost,taxrate,commissionrate,commission,fob,profitrate,profitrate_point,butao_point,chaizhuang_point,
                        haimian_point,dannum_rate,hrcost,biandaicost,zhizao_amt,guanli_rate,discount,extras_cost,dept_profitrate,dept_profitrate_rangli,moneyrate,mattressrelcode,
                        flag,auditingrep,auditingdate,createtime,createby,total_cost,nottax_factory_cost,nottax_dept_cost,taxes,dept_cost,foreign_cost,foreign_cost_bz,
                        nottax_factory_cost/(dept_profitratedept_profitrate_rangli/100)asdijia_cost,xd_flag,xd_auditingrep,xd_auditingdate,qr_flag,qr_auditingrep,
                        qr_auditingdate,js1_flag,erp_mtrlcode,erp_cb_updatetime,if_bcp_type,if_zhedie_type,if_w_butao,biandai_qty,other_rate,if_moneyrate,
                        if_m_chai,if_z_chai,if_d_chai,if_n_butao,if_m_wbutao_way,s_cover_qty,z_cover_qty,x_cover_qty,s_m_cover_qty,z_m_cover_qty,x_m_cover_qty,cabinet_type,area,diameter,cubage";
            DbSqlHelper.SelectOne(cmd, mattress, fields);
            return mattress;
        }
        public List<u_mattress_mx_mtrl> GetMattressMxMtrl(int mattressid,List<string> extraWhere = null)
        {
            var mxList = new List<u_mattress_mx_mtrl>();
            var outputFields = @"mattressmxid,mattressid,formulaid,formula,mtrlid,price,gram_weight,cloth_width,if_inputqty,qty,costamt,if_15strip,if_success,shrinkage,replace_formula,priceunit,if_areaprice,thickness,chastr,dv_dscrp,xu,useqty,useformula,replace_useformula,gydscrp,cw_erpmtrlcode,erp_mtrlid,formulakind,formulatype,mtrlname";
            var selectStr = @"SELECT u_mattress_mx_mtrl.mattressmxid
	                                ,u_mattress_mx_mtrl.mattressid
	                                ,u_mattress_mx_mtrl.formulaid
	                                ,u_mattress_mx_mtrl.formula
	                                ,u_mattress_mx_mtrl.mtrlid
	                                ,u_mattress_mx_mtrl.price
	                                ,u_mattress_mx_mtrl.gram_weight
	                                ,u_mattress_mx_mtrl.cloth_width
	                                ,u_mattress_mx_mtrl.if_inputqty
	                                ,u_mattress_mx_mtrl.qty
	                                ,u_mattress_mx_mtrl.if_15strip
	                                ,u_mattress_mx_mtrl.costamt
	                                ,u_mattress_mx_mtrl.if_success
	                                ,u_mattress_mx_mtrl.shrinkage
	                                ,u_mattress_mx_mtrl.replace_formula
	                                ,u_mattress_mx_mtrl.if_areaprice
	                                ,u_mattress_mx_mtrl.priceunit
	                                ,u_mattress_mx_mtrl.thickness
	                                ,u_mattress_mx_mtrl.chastr
	                                ,u_mattress_mx_mtrl.xu
	                                ,u_mattress_mx_mtrl.dv_dscrp
	                                ,u_mattress_mx_mtrl.useqty
	                                ,u_mattress_mx_mtrl.useformula
	                                ,u_mattress_mx_mtrl.replace_useformula
	                                ,u_mattress_mx_mtrl.gydscrp
	                                ,u_mattress_mx_mtrl.cw_erpmtrlcode
	                                ,u_mattress_mx_mtrl.erp_mtrlid
                                    ,u_mattress_formula.formulakind
                                    ,u_mattress_formula.formulatype
                                    ,u_mtrl_price.name AS mtrlname
                                FROM u_mattress_mx_mtrl
                                LEFT OUTER JOIN u_mattress_formula ON u_mattress_formula.formulaid = u_mattress_mx_mtrl.formulaid
                                LEFT OUTER JOIN u_mtrl_price ON u_mattress_mx_mtrl.mtrlid = u_mtrl_price.mtrlid";

            var whereList = new List<string>();
            whereList.Add("u_mattress_mx_mtrl.mattressid = @mattressid");

            if(extraWhere != null && extraWhere.Any())
            {
                whereList = whereList.Concat(extraWhere).ToList();
            }

            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), new Dictionary<string, object>() { { "@mattressid", mattressid } }, "mattressmxid", outputFields, 0, 0, mxList);

            return mxList;
        }
        /// <summary>
        /// 保存床垫复制清单
        /// </summary>
        /// <param name="mattressid"></param>
        public void CopyMattressInterface(int cpoyid,int mattressid)
        {
            cmd.CommandText = @"DELETE u_mattress_interface WHERE mattressid = @mattressid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.ExecuteNonQuery();

            cmd.CommandText = @"DELETE u_mattress_interface_qd WHERE mattressid = @mattressid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.ExecuteNonQuery();

            cmd.CommandText = @"INSERT INTO u_mattress_interface (
	                                mattressid
	                                ,printid
	                                ,itemname
	                                ,bj_pzname
	                                ,bj_namemx
	                                ,bj_inputtype
	                                ,actual_size
	                                ,sb_craft
	                                ,actual_size_sb
	                                ,erp_pzid
	                                ,ss_rate
	                                ,ls_rate
	                                )
                                SELECT @mattressid
	                                ,printid
	                                ,itemname
	                                ,bj_pzname
	                                ,bj_namemx
	                                ,bj_inputtype
	                                ,actual_size
	                                ,sb_craft
	                                ,actual_size_sb
	                                ,erp_pzid
	                                ,ss_rate
	                                ,ls_rate
                                FROM u_mattress_interface
                                WHERE mattressid = @copy_id";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.Parameters.AddWithValue("@copy_id", cpoyid);
            cmd.ExecuteNonQuery();

            cmd.CommandText = @"INSERT INTO u_mattress_interface_qd (
	                                mattressid
	                                ,printid
	                                ,itemname
	                                ,bj_pzname
	                                ,bj_pzname_mx
	                                ,mtrlid
	                                ,erp_mtrlid
	                                ,wrkgrpid
	                                ,useqty
	                                ,dscrp
	                                ,actual_useqty
	                                ,qd_actual_size
	                                ,qd_pfgroupqty
	                                )
                                SELECT @mattressid
	                                ,printid
	                                ,itemname
	                                ,bj_pzname
	                                ,bj_pzname_mx
	                                ,mtrlid
	                                ,erp_mtrlid
	                                ,wrkgrpid
	                                ,useqty
	                                ,dscrp
	                                ,actual_useqty
	                                ,qd_actual_size
	                                ,qd_pfgroupqty
                                FROM u_mattress_interface_qd
                                WHERE mattressid = @copy_id";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.Parameters.AddWithValue("@copy_id", cpoyid);
            cmd.ExecuteNonQuery();
        }

        public void CopyMattress(List<u_mattress> mattressList,int gwidth,int glength)
        {
            var bedNetHelpe = HelperBase.GetHelper<BedNetHelper>(cmd);
            foreach(var mattress in mattressList)
            {
                var mattress_copy = GetMattress(mattress.mattressid);
                var mxlist = new List<u_mattress_mx_mtrl>();
                
                if (mattress.mattress_length > 0 && mattress.mattress_width > 0)
                {
                    cmd.CommandText = @"SELECT u_mattress_mx_mtrl.mtrlid
                                        FROM u_mattress_mx_mtrl
                                        INNER JOIN u_mattress_formula ON u_mattress_formula.formulaid = u_mattress_mx_mtrl.formulaid
                                        WHERE u_mattress_formula.formulakind = 99
	                                        AND u_mattress_mx_mtrl.mattressid = @mattressid
	                                        AND u_mattress_mx_mtrl.mtrlid > 0";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@mattressid", mattress.mattressid);
                    using(var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            mxlist.Add(new u_mattress_mx_mtrl() { copty_mtrlid = Convert.ToInt32(reader["mtrlid"]) });
                        }
                    }

                    foreach(var mx in mxlist)
                    {
                        var bednet = new u_bednet() { bednetid = mx.copty_mtrlid, mattress_width = mattress.mattress_width ,mattress_length = mattress.mattress_length };
                        var bednetMx = new u_bednetmx() { spring_qty_width = gwidth, spring_qty_length = glength };
                        bedNetHelpe.CopyBedNet(bednet, bednetMx, out int newMtrlid);

                        mx.mtrlid = newMtrlid;
                    }
                }

                // 判断copy_bedNet.bednetcode是否存在@@字符串，如果存在，则删除@@后面的字符串，包括@@
                if (mattress_copy.mattresscode.IndexOf("@@") > -1)
                {
                    mattress_copy.mattresscode = mattress_copy.mattresscode.Substring(mattress_copy.mattresscode.IndexOf("@@"));
                }

                mattress_copy.mattresscode += " @@";
                mattress_copy.mattresscode += DateTime.Now.ToString("yyyMMdd_mmhhss");

                if (mattress_copy.mattressrelcode.IndexOf("@@") > -1)
                {
                    mattress_copy.mattressrelcode = mattress_copy.mattressrelcode.Substring(mattress_copy.mattressrelcode.IndexOf("@@"));
                }

                mattress_copy.mattressrelcode += " @@";
                mattress_copy.mattressrelcode += DateTime.Now.ToString("yyyMMdd_mmhhss");

                mattress_copy.mattressid = 0;

                mattress_copy.mattress_width = mattress.mattress_width > 0 ? mattress.mattress_width : mattress_copy.mattress_width;
                mattress_copy.mattress_length = mattress.mattress_length > 0 ? mattress.mattress_length : mattress_copy.mattress_length;

                if (!"维持原状".Equals(mattress.area)) mattress_copy.area = mattress.area;
                if (!"维持原状".Equals(mattress.cabinet_type)) mattress_copy.cabinet_type = mattress.cabinet_type;

                mattress_copy.js1_flag = 0;
                mattress_copy.js1_auditingrep = string.Empty;
                mattress_copy.js1_auditingdate = null;

                mattress_copy.xd_flag = 0;

                var mattressMxMtrlList = GetMattressMxMtrl(mattress.mattressid);

                foreach(var mx in mattressMxMtrlList)
                {
                    mx.mattressid = 0;
                    mx.mattressmxid = 0;
                }

                SaveMattress(mattress_copy, mattressMxMtrlList);

                CopyMattressInterface(mattress_copy.mattressid, mattress.mattressid);

                foreach (var mx in mxlist)
                {
                    cmd.CommandText = @"UPDATE u_mattress_mx_mtrl
                                            SET mtrlid = @new_mtrlid
                                            FROM u_mattress_mx_mtrl
                                            INNER JOIN u_mattress_formula ON u_mattress_formula.formulaid = u_mattress_mx_mtrl.formulaid
                                            WHERE u_mattress_formula.formulakind = 99
	                                            AND u_mattress_mx_mtrl.mattressid = @new_mattressid
	                                            AND u_mattress_mx_mtrl.mtrlid = @old_mtrld";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@new_mtrlid", mx.mtrlid);
                    cmd.Parameters.AddWithValue("@new_mattressid", mattress_copy.mattressid);
                    cmd.Parameters.AddWithValue("@old_mtrld", mx.copty_mtrlid);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void SaveMattressInterface(int mattressid, List<u_mattress_interface> mxlist,List<u_mattress_interface_qd> qdlist)
        {
            cmd.CommandText = @"DELETE u_mattress_interface WHERE mattressid = @mattressid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.ExecuteNonQuery();

            cmd.CommandText = @"DELETE u_mattress_interface_qd WHERE mattressid = @mattressid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@mattressid", mattressid);
            cmd.ExecuteNonQuery();

            foreach (var mx in mxlist) {
                mx.mattressid = mattressid;
                DbSqlHelper.Insert(cmd, mx, "mattressid,printid,itemname,bj_pzname,bj_namemx,actual_size,sb_craft,actual_size_sb,erp_pzid,bj_inputtype,ss_rate,ls_rate"); 
            }

            foreach (var mx in qdlist)
            {
                mx.mattressid = mattressid;
                DbSqlHelper.Insert(cmd, mx, "mattressid,printid,itemname,bj_pzname,bj_pzname_mx,bj_pzname_mx_mx,mtrlid,erp_mtrlid,wrkgrpid,useqty,dscrp,actual_useqty,qd_actual_size,qd_pfgroupqty,ss_rate,ls_rate,sh_rate");
            }
        }

        #region 床垫接口相关方法
        /// <summary>
        /// 业务审核
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressYWAudit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "yw_flag");

            if(mattress.yw_flag == 1)
            {
                throw new LJCommonException("床垫已业务审核，不能业务审核!");
            }

            mattress.yw_flag = 1;
            mattress.yw_auditingdate = context.opdate;
            mattress.yw_auditingrep = context.tokendata.username;

            DbSqlHelper.Update(cmd, mattress, "yw_flag,yw_auditingdate,yw_auditingrep");
        }

        /// <summary>
        /// 业务撤审
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressYWCancelAudit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "yw_flag,js1_flag");

            if (mattress.js1_flag == 1)
            {
                throw new LJCommonException("床垫已技术1审核，不能业务撤审!");
            }

            if (mattress.yw_flag == 0)
            {
                throw new LJCommonException("床垫未业务审核，不能业务撤审!");
            }

            mattress.yw_flag = 0;
            mattress.yw_auditingdate = null;
            mattress.yw_auditingrep = string.Empty;

            DbSqlHelper.Update(cmd, mattress, "yw_flag,yw_auditingdate,yw_auditingrep");
        }

        /// <summary>
        /// 产品补充审核
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressJSAudit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "yw_flag,js1_flag");

            if (mattress.js1_flag == 1)
            {
                throw new LJCommonException("床垫已技术1审核，不能技术1审核!");
            }

            if (mattress.yw_flag == 0)
            {
                throw new LJCommonException("床垫未业务审核，不能技术1审核!");
            }

            mattress.js1_flag = 1;
            mattress.js1_auditingdate = context.opdate;
            mattress.js1_auditingrep = context.tokendata.username;

            DbSqlHelper.Update(cmd, mattress, "js1_flag,js1_auditingdate,js1_auditingrep");
        }

        /// <summary>
        /// 产品补充撤审
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressJSCancelAudit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "yw_flag,js1_flag,creatmtrl_flag");

            if (mattress.creatmtrl_flag == 1)
            {
                throw new LJCommonException("床垫已生成产品，不能技术1撤审!");
            }

            if (mattress.js1_flag == 0)
            {
                throw new LJCommonException("床垫未技术1撤审，不能技术1撤审!");
            }

            mattress.js1_flag = 0;
            mattress.js1_auditingdate = null;
            mattress.js1_auditingrep = string.Empty;

            DbSqlHelper.Update(cmd, mattress, "js1_flag,js1_auditingdate,js1_auditingrep");
        }
        /// <summary>
        /// 清单补充审核
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressJS2Audit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "js2_flag");

            if (mattress.js2_flag == 1)
            {
                throw new LJCommonException("床垫已技术2审核，不能技术2审核!");
            }

            mattress.js2_flag = 1;
            mattress.js2_auditingdate = context.opdate;
            mattress.js2_auditingrep = context.tokendata.username;

            DbSqlHelper.Update(cmd, mattress, "js2_flag,js2_auditingdate,js2_auditingrep");
        }

        /// <summary>
        /// 清单补充撤审
        /// </summary>
        /// <param name="mattressid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MattressJS2CancelAudit(int mattressid)
        {
            var mattress = new u_mattress() { mattressid = mattressid };
            DbSqlHelper.SelectOne(cmd, mattress, "js2_flag");

            if (mattress.js2_flag == 0)
            {
                throw new LJCommonException("床垫未技术2审核，不能技术2撤审!");
            }

            mattress.js2_flag = 0;
            mattress.js2_auditingdate = null;
            mattress.js2_auditingrep = string.Empty;

            DbSqlHelper.Update(cmd, mattress, "js2_flag,js2_auditingdate,js2_auditingrep");
        }

        /// <summary>
        /// 配对ERP成品
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        /// <exception cref="LJCommonException"></exception>
        public void MatchERPMtrldef(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist)
        {
            if(mattress.mattressid <= 0)
            {
                throw new LJCommonException("床垫id有误,请检查!");
            }

            DbSqlHelper.Update(cmd, mattress, "erp_mtrlid,erp_mtrlcode");

            foreach(var mx in mxlist)
            {
                DbSqlHelper.Update(cmd, mx, "erp_mtrlid");
            }
        }
        #endregion

        #region 公式计算通用方法
        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;
            }
        }
        /// <summary>
        /// 记录隐藏公式
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <param name="isReplace"></param>
        private void AddFormulaHideKeyValue(string key, string value, bool isReplace = true)
        {
            if (!formula_replacements_hide.ContainsKey(key))
            {
                formula_replacements_hide.Add(key, value);
            }
            else if (isReplace)
            {
                formula_replacements_hide[key] = value;
            }
        }
        private string ConvertToEnglishSymbols(string input)
        {
            input = input.Replace("（", "(")
                 .Replace("）", ")")
                 .Replace("，", ",")
                 .Replace("。", ".")
                 .Replace("：", ":")
                 .Replace("；", ";")
                 .Replace("“", "\"")
                 .Replace("”", "\"")
                 .Replace("‘", "'")
                 .Replace("’", "'");

            return input;
        }
        /// <summary>
        /// 变量公式计算
        /// </summary>
        /// <param name="varname"></param>
        /// <returns></returns>
        private TData CalculateVarFormula(string varname)
        {
            varname = $"【{varname}】";
            if (!formula_replacements.ContainsKey(varname)) return null;
            return Calculate(formula_replacements[varname], varname);
        }
        private void replaceFormulas(ref string expression)
        {
            bool hasReplace = false;

            foreach (var replacement in formula_replacements)
            {
                if (expression.IndexOf(replacement.Key) > -1)
                {
                    expression = expression.Replace(replacement.Key, "(" + replacement.Value + ")");
                    hasReplace = true;
                }
            }
            if (hasReplace)
            {
                replaceFormulas(ref expression);
            }
        }
        private void FormatExpression(ref string expression)
        {
            // 替换相互嵌套的公式
            replaceFormulas(ref expression);

            expression = ConvertToEnglishSymbols(expression);

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

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

            // 使用正则表达式匹配输入字符串
            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));
            }
        }
        /// <summary>
        /// 公式计算
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private TData Calculate(string expression, string name = "")
        {
            FormatExpression(ref expression);

            try
            {
                return LJExprParser.Parse(expression).Result;
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"计算{name}公式错误: {ex.Message}");
            }
        }
        /// <summary>
        /// Sql公式计算
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        private object SqlCalculate(string expression, string name = "")
        {
            FormatExpression(ref expression);

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