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 JLHHJSvr.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>();
        /// <summary>
        /// 床网参数替换
        /// </summary>
        public List<replacement_bednet> BendetReplace = new List<replacement_bednet>();
        public List<replacement> BendetFormulasReplace = new List<replacement>();

        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 });
                }

                string[] keys = { "车间成本", "总床垫车间成本", "不含税出厂价", "部门不含税价", "税金", "部门含税价", "外币价", "底价", "佣金" };
                res = res.OrderBy(r => Array.IndexOf(keys, r.label.Replace("【", "").Replace("】", ""))).ToList();
                foreach (var item in formula_replacements_hide)
                {
                    res.Add(new replacement { label = item.Key, value = item.Value, type = 1 });
                }
                return res;
            }
        }

        /// <summary>
        /// 公式计算
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        /// <param name="isCalBed">是否实时计算</param>
        /// <param name="isNewFormula">是否获取新的公式计算</param>
        /// <param name="extraProcesses">特殊工艺</param>
        /// <param name="extraCosts">额外费用</param>
        public void CalCulateFormula(u_mattress mattress, List<u_mattress_mx_mtrl> mxlist, Boolean isCalBed = true, Boolean isNewFormula = true, List<u_mattress_mx_extra> extraProcesses = null, List<u_mattress_mx_extra> extraCosts = null)
        {

            BendetReplace = new List<replacement_bednet>();
            BendetFormulasReplace = new List<replacement>();

            InitMattress(mattress);

            InitReplaceMents(mattress, isNewFormula, extraProcesses, extraCosts);

            if (isCalBed || isNewFormula) CalCulateBedNet(mxlist, isCalBed, isNewFormula, mattress.deptid.Value);
            // 实时计算价格
            if (isCalBed)
            {
                InitMattressMx(mattress, mxlist);

                //CalCulateBedNet(mxlist);

                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");

                var thicknessDict = InitMxThicknessReplaceMents(mxlist);

                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);

                    // 顶替分组关键值：內布套、顶布裥棉
                    if (thicknessDict.ContainsKey(mx.chastr))
                    {
                        foreach(var rp in thicknessDict[mx.chastr])
                        {
                            AddKeyValue(rp.Key, rp.Value);
                        }
                    }

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

                    expression = InitMxSideReplaceMents(mattress, mx, diancengList, mattersstype, expression);
                    use_expression = InitMxSideReplaceMents(mattress, 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))
                    {
                        expression = InitMxClothHeightReplaceMents(mattress, mx, expression, mxlist, 101);
                        use_expression = InitMxClothHeightReplaceMents(mattress, mx, use_expression, mxlist, 101);
                    }
                    //外布套
                    if (new List<int>() { 111, 112, 113, 114, 115, 116 }.Contains(mx.formulakind.Value))
                    {
                        expression = InitMxClothHeightReplaceMents(mattress, mx, expression, mxlist, 111);
                        use_expression = InitMxClothHeightReplaceMents(mattress, mx, use_expression, 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;

                    try
                    {
                        if (sqlCal)
                        {
                            mx.costamt = Convert.ToDecimal(SqlCalculate(expression, "成本金额"));
                        }
                        else
                        {
                            mx.costamt = Calculate(expression, "成本金额").DecimalValue;
                        }
                    }
                    catch (Exception e)
                    {
                        mx.if_success = 1;
                        mx.message = e.Message;
                    }
                    #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;

                    try
                    {
                        if (sqlCal)
                        {
                            mx.useqty = Convert.ToDecimal(SqlCalculate(use_expression, "清单用量"));
                        }
                        else
                        {
                            mx.useqty = Calculate(use_expression, "清单用量").DecimalValue;
                        }
                    }
                    catch (Exception e)
                    {
                        mx.if_success = 1;
                        mx.message = e.Message;
                    }
                    #endregion
                }
            }

            InitMattressMxListReplaceMents(mattress, mxlist, isNewFormula);

            string[] keys = { "车间成本", "不含税出厂价", "部门不含税价", "税金", "部门含税价", "外币价", "底价", "佣金" };

            if (formula_replacements_hide.Count > 0)
            {
                foreach (var replacement in formula_replacements_hide)
                {
                    var _key = replacement.Key.Replace("【", "").Replace("】", "");
                    var value = CalculateVarFormula(_key).DecimalValue;
                    AddKeyValue(_key, value);
                }
                foreach (var replacement in formula_replacements)
                {
                    var _key = replacement.Key.Replace("【", "").Replace("】", "");
                    if (!keys.Contains(_key) )
                    {
                        var value = CalculateVarFormula(_key).DecimalValue;
                        AddKeyValue(_key, value);
                    }
                }
            }
        }
        /// <summary>
        /// 记录明细按分组读取厚度变量
        /// </summary>
        /// <param name="mxlist"></param>
        private Dictionary<string, Dictionary<string, object>> InitMxThicknessReplaceMents(List<u_mattress_mx_mtrl> mxlist)
        {
            // 明细按分组读取厚度变量
            var thicknessDict = new Dictionary<string, Dictionary<string, object>> () { };

            foreach (var mx in mxlist)
            {
                // 只处理 内布套（101,102）、顶布裥棉（103）
                if (!new List<int>() { 101, 102, 103 }.Contains(mx.formulatype.Value)) continue;

                if (mx.chastr.Contains("顶布裥棉") || mx.chastr.Contains("内布套"))
                {
                    if(!thicknessDict.ContainsKey(mx.chastr))
                    {
                        thicknessDict.Add(mx.chastr, new Dictionary<string, object>() {});
                    }
                    var mxDict = thicknessDict[mx.chastr];
                    setMxThicknessValue(mx, ref mxDict);
                    thicknessDict[mx.chastr] = mxDict;
                }
            }

            return thicknessDict;
        }
        private void setMxThicknessValue(u_mattress_mx_mtrl mx, ref Dictionary<string, object> mxDict)
        {
            var keyStr = "";
            if (new List<int>() { 101, 102 }.Contains(mx.formulatype.Value))
            {
                switch (mx.formulakind.Value)
                {
                    case 0: // 面裥绵-布料
                        keyStr = "内布套上覆";
                        break;
                    case 1: // 底裥绵-布料
                        keyStr = "内布套下覆";
                        break;
                    case 2: // 大侧裥绵-布料
                        keyStr = "内布套侧覆";
                        break;
                }
                if (!string.IsNullOrEmpty(keyStr))
                {
                    mxDict.Add(keyStr, mx.thickness);
                }
            } 
            else if (new List<int>() { 103 }.Contains(mx.formulatype.Value))
            {
                switch (mx.formulakind.Value)
                {
                    case 50: // 面裥绵-裥绵海绵
                        keyStr = "物料厚度";
                        break;
                }
                if (!string.IsNullOrEmpty(keyStr))
                {
                    mxDict.Add(keyStr, mx.thickness.Value);
                }
            }
        }

        private void CalCulateBedNet(List<u_mattress_mx_mtrl> mxlist, Boolean isCalBed = true, Boolean isNewFormula = true, int deptid = 0)
        {
            var bedNetHelper = HelperBase.GetHelper<BedNetHelper>(cmd, new HelperBase.Context() { tokendata = context.tokendata });

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

            decimal dannumRate = 0;
            int dannumType = 0;
            //if (replacements.TryGetValue("【大小单系数】", out object value2))
            //{
            //    dannumRate = Convert.ToDecimal(value2);
            //}
            if (replacements.TryGetValue("【大小单类型】", out object value1))
            {
                dannumType = Convert.ToInt32(value1);
            }

            List<replacement_bednet> rpBednet = new List<replacement_bednet>();
            var bednetSumList = new List<decimal?>();

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

                var bednet = bedNetHelper.GetBedNet(mx.mtrlid.Value);
                if (bednet.flag == 1) continue;
                if (deptid > 0)
                {
                    bednet.deptid = deptid;
                }

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

                var bednetSpringList = bedNetHelper.GetBedNetSpringList(bednet.bednetid.Value);
                //bednet.dannum_rate = dannumRate;
                bednet.dannum_type = dannumType;

                bedNetHelper.CalCulateFormula(bednet, bednetMxlist, bednetSpringList, true, true);

                var rpBednetItem = new replacement_bednet()
                {
                    label = mx.mtrlname,
                    replace = bedNetHelper.Replacements
                };
                BendetFormulasReplace = bedNetHelper.FormulaReplacements.Where(itm => new List<int>{ 0, 1 }.Contains(itm.type.Value)).ToList();
                rpBednet.Add(rpBednetItem);

                //if (isCalBed)
                //{
                //    bedNetHelper.SaveBedNet(bednet, bednetMxlist, bednetSpringList);
                //}

                if (isNewFormula)
                {
                    mx.price = bednet.total_cost;
                    AddKeyValue("床网车间成本" + (index + 1), bednet.total_cost);
                    bednetSumList.Add(bednet.total_cost);
                }
                else
                {
                    mx.price = bednet.nottax_factory_cost;
                }
            }
            BendetReplace = rpBednet;


            #region 总床网车间成本
            AddKeyValue("总床网车间成本", bednetSumList.Sum());
            #endregion
        }
        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)
            {
                if (!new List<int>() { 0, 1, 2, 3, 104 }.Contains(mx.formulatype.Value)) continue;

                // 垫层 wf_check_dianceng_ifright
                if (mx.formulatype.Value == 1)
                {
                    cmd.CommandText = @"SELECT TOP 1 formulaid From u_mattress_formula where formulakind = @ll_formulakind";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@ll_formulakind", mx.formulakind);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            mx.formulaid = Convert.ToInt32(reader["formulaid"]);
                        }
                        else
                        {
                            mx.formulaid = 0;
                        }
                    }
                }

                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,gram_weight,cloth_width,if_inputqty,priceunit,shrinkage,if_areaprice");

                mx.costamt = 0;
                mx.useqty = 0;
                mx.if_success = 0;
                mx.replace_formula = "";
                mx.replace_useformula = "";
                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;
                    }
                }
            }
        }
        /// <summary>
        /// 其他额外费用tab / 特殊工艺计算
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typeid"></param> 1: 特殊工艺。2：其他额外费用
        /// <returns></returns>
        private decimal GetExtraAmt(u_mattress mattress, int typeid)
        {
            decimal amt = 0;
            cmd.CommandText = @"
		        SELECT ISNULL(SUM(u_mattress_mx_extra.price * u_mattress_mx_extra.qty), 0) AS amt
		        From u_mattress_mx_extra
		        INNER JOIN u_mattress_extra_type On u_mattress_extra_type.extraid = u_mattress_mx_extra.extraid
		        WHERE u_mattress_extra_type.typeid = @typeid 
                    AND u_mattress_mx_extra.mattressid = @mattressid";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@typeid", typeid);
            cmd.Parameters.AddWithValue("@mattressid", mattress.mattressid);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    amt = Convert.ToDecimal(reader["amt"]);
                }
            }

            return amt;
        }
        private void InitReplaceMents(u_mattress mattress, Boolean isNewFormula = true, List<u_mattress_mx_extra> extraProcesses = null, List<u_mattress_mx_extra> extraCosts = null)
        {
            replacements.Clear();

            decimal cost = 0;
            if (extraCosts != null && extraCosts.Count > 0)
            {
                foreach(var itemCost in extraCosts)
                {
                    cost += itemCost.price.Value * itemCost.qty.Value;
                }
            }
            else
            {
                cost = GetExtraAmt(mattress, 2);
            }
            AddKeyValue("材料额外费用", cost);

            decimal cost1 = 0;
            if (extraProcesses != null && extraProcesses.Count > 0)
            {
                foreach (var itemPro in extraProcesses)
                {
                    cost1 += itemPro.price.Value * itemPro.qty.Value;
                }
            }
            else
            {
                cost1 = GetExtraAmt(mattress, 1);
            }
            AddKeyValue("特殊工艺费用", cost1);


            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);
            //AddFormulaHideKeyValue("【总材料成本】", "【弹簧材料总成本】+【蛇线材料成本】+【四周口袋弹簧成本】+【四周口袋无纺布成本】+【四周加硬材料成本】+【胶水材料成本】+【入袋无纺布材料成本】+【底面无纺布材料成本】+【边铁材料成本】+【C钉/夹码材料成本】+【海绵包边材料成本】+【填充海绵成本】+【封边材料成本】+【弹叉材料成本】+【胶条/包角材料成本】+【上垫层物料成本】+【下垫层物料成本】+【包装总成本】");


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

            // 公式
            AddFormulaKeyValue("车间成本", "【材料成本】*【大小单】+【人工费用】+【边带费用】+【额外费用】+【制造费用】");
            AddFormulaKeyValue("不含税出厂价", "【车间成本】*(【工厂利润率】+【工艺点数】+【内布套点数】+【拆装点数】+【海绵扣点】)*【管理费点】");
            AddFormulaKeyValue("部门不含税价", "【不含税出厂价】/(【部门利润率】+【部门让利点数】/100)/(1-(【佣金点数】-1))*【额外点数】+【FOB】");
            AddFormulaKeyValue("税金", "【部门不含税价】*(【税率】-1)+【佣金】 * 0.05");
            AddFormulaKeyValue("部门含税价", "(【部门不含税价】+【佣金】 * 0.06)*【税率】*【折扣率】");
            AddFormulaKeyValue("外币价", "【部门含税价】/【汇率】");
            AddFormulaKeyValue("底价", "if((【部门利润率】+【部门让利点数】/100)<> 0,【不含税出厂价】/(【部门利润率】+【部门让利点数】/ 100),0)");
            AddFormulaKeyValue("佣金", "【底价】/（1-(【佣金点数】-1))-【底价】");

            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);
            }

            if (isNewFormula)
            {
                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);

                string[] keys = { "车间成本", "不含税出厂价", "部门不含税价", "税金", "部门含税价", "外币价", "底价", "佣金", "总床垫车间成本" };
                // 添加替换公式变量
                foreach (var netvar in bednetVarList)
                {
                    if (keys.Contains(netvar.varname))
                    {
                        AddFormulaKeyValue(netvar.varname, netvar.formula);
                    }
                    else
                    {
                        AddFormulaHideKeyValue(netvar.varname, netvar.formula);
                    }
                }
            }

            if (isNewFormula) GetDannumRate(mattress);
        }
        private void InitMxReplaceMents(u_mattress_mx_mtrl mx)
        {
            mx.mtrlname = !string.IsNullOrEmpty(mx.mtrlname) ? mx.mtrlname.Trim() : "null";

            decimal _shrinkage = mx.shrinkage.Value == 0 ? (decimal)1 : mx.shrinkage.Value;

            //取mtrldef.name的前两位
            AddKeyValue("名称前2位", mx.mtrlname.Length > 2 ? mx.mtrlname.Substring(0, 2) : mx.mtrlname);
            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);
            if (_shrinkage > 0) AddKeyValue("物料收缩率", _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 mattress, 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

            #region 若填写则顶替床垫尺寸
            if (mx.mattress_width != null && mx.mattress_width > 0 && mx.mattress_width <= mattress.mattress_width) expression = expression.Replace("【规格宽】", $"{mx.mattress_width}");
            if (mx.mattress_length != null && mx.mattress_length > 0 && mx.mattress_length <= mattress.mattress_length) expression = expression.Replace("【规格长】", $"{mx.mattress_length}");
            #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 string InitMxClothHeightReplaceMents(u_mattress mattress, u_mattress_mx_mtrl mx, string expression, 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);

            if (mx.mattress_width != null && mx.mattress_width > 0 && mx.mattress_width <= mattress.mattress_width) expression = expression.Replace("【规格宽】", $"{mx.mattress_width}");
            if (mx.mattress_length != null && mx.mattress_length > 0 && mx.mattress_length <= mattress.mattress_length) expression = expression.Replace("【规格长】", $"{mx.mattress_length}");

            return expression;
        }
        /// <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))
                {
                    var _mxList = mxlist.Where(o => o.formulatype == mx.formulatype).ToList(); //在同一个tab中计算
                    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>
        /// <param name="isNewFormula">是否获取新的公式计算</param>
        private void InitMattressMxListReplaceMents(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist, Boolean isNewFormula = true)
        {
            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.Length > 2 ? bz.mtrlname.Substring(0, 2) : bz.mtrlname);
                        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,typerate,dannum1_rate,dannum2_rate,dannum3_rate,dannum4_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.formulakind == 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 计算边带费用
            if (!isNewFormula || !formula_replacements.ContainsKey("【边带费用】"))
            {
                mattress.biandaicost_replace_formula = "【边带条数】*【边带单价】*（（【规格宽】+【规格长】）*2+30）/100";
            }
            else
            {
                mattress.biandaicost_replace_formula = formula_replacements["【边带费用】"];
            }

            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, isNewFormula);
            #endregion

            #region 内布套特殊加点
            CalCulateNetBuTao(mattress, mxlist);
            #endregion

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

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

            #region 计算主表 材料成本
            var aa = new List<decimal>();
            mxlist.ForEach(t => {
                var _costamt = t.costamt.Value;
                if (isNewFormula)
                {
                    decimal _dannum_rate = 0;
                    #region 计算损耗率
                    switch (mattress.dannum_type.Value)
                    {
                        case 1:
                            if (t.dannum1_rate != null) _dannum_rate = t.dannum1_rate.Value;
                            break;
                        case 2:
                            if (t.dannum2_rate != null) _dannum_rate = t.dannum2_rate.Value;
                            break;
                        case 3:
                            if (t.dannum3_rate != null) _dannum_rate = t.dannum3_rate.Value;
                            break;
                        case 4:
                            if (t.dannum4_rate != null) _dannum_rate = t.dannum4_rate.Value;
                            break;
                    }
                    _costamt = _costamt * (1 + _dannum_rate);
                    #endregion

                    // 新公式排除床网计算
                    if (t.formulatype != 99) aa.Add(_costamt);
                }
                else
                {
                    aa.Add(_costamt);
                }
            });
            mattress.total_material_cost = aa.Sum();
            AddKeyValue("材料成本", mattress.total_material_cost);
            #endregion


            //#region 标准金额
            //if (!isNewFormula)
            //{
            //    // 旧公式
            //    CalCulateCostNew(mattress, "标准");
            //    // 查找点数
            //    decimal _rate = GetWorkmanshipRate(mattress, "num_10", "标准");
            //    _rate = _rate == 0 ? 1 : _rate;

            //    mattress.foreign_cost_bz = mattress.dept_cost / _rate;
            //}
            //else
            //{
            //    CalCulateCostNew(mattress, "标准", mattersstype);
            //    mattress.foreign_cost_bz = mattress.dept_cost;
            //}
            //AddKeyValue("标准金额", mattress.foreign_cost_bz);
            //#endregion

            //#region 大单金额
            //if (!isNewFormula)
            //{
            //    // 旧公式
            //    CalCulateCostNew(mattress, "大单");
            //    // 查找点数
            //    decimal _rate2 = GetWorkmanshipRate(mattress, "num_11", "大单");
            //    _rate2 = _rate2 == 0 ? 1 : _rate2;

            //    mattress.foreign_cost_dd = mattress.dept_cost / _rate2;
            //}
            //else
            //{
            //    CalCulateCostNew(mattress, "大单", mattersstype);
            //    mattress.foreign_cost_dd = mattress.dept_cost;
            //}
            //AddKeyValue("大单金额", mattress.foreign_cost_dd);
            //#endregion

            if (!isNewFormula)
            {
                // 旧公式
                CalCulateCostNew(mattress, "散单");
            }
            else
            {
                CalCulateCostNew(mattress, "散单", isNewFormula);
            }
            AddKeyValue("散单金额", mattress.foreign_cost);
        }
        /// <summary>
        /// 计算拼侧最大金额的侧和工厂利润率加点
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        private void CalCulatePince(u_mattress mattress, List<u_mattress_mx_mtrl> mxlist, Boolean isNewFormula = true)
        {
            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 = null;
            decimal ldeTempMax = decimal.MinValue;
            foreach (var item in costAmounts)
            {
                if (item.Value >= ldeTempMax)
                {
                    ldeTempMax = item.Value;
                    lsTempMaxStr = item.Key;
                }
            }


            // 清空不是最大金额的拼侧
            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 = null;
            decimal ldeTempMax2 = decimal.MinValue;
            foreach (var item in costAmounts)
            {
                if (item.Value >= ldeTempMax)
                {
                    ldeTempMax = item.Value;
                    lsTempMaxStr = item.Key;
                }
            }

            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);
            AddKeyValue("拼侧数量", pinceCnt);

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

            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, "工艺点数", isNewFormula);
            AddKeyValue("工艺点数", mattress.profitrate_point);

            decimal extraProcesses = GetExtraAmt(mattress, 1);
            AddKeyValue("特殊工艺费用", extraProcesses);
        }
        /// <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" : "");
                AddKeyValue("内布套简单款", Convert.ToInt32(butaoJd));
                AddKeyValue("内布套复杂款", Convert.ToInt32(butaoFz));

                // 查找点数
                //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" : "");
            AddKeyValue("拆装数量", cnt);

            // 查找点数
            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="label"></param>
        /// <returns></returns>
        public List<u_workmanship_add> GetWorkmanshipAdd(string label)
        {
            var list = new List<u_workmanship_add>();
            var outputFields = @"deptid, mattresstypeid, formula, rate";
            var selectStr = @"SELECT deptid, mattresstypeid, formula, rate FROM u_workmanship_add ";
            var whereList = new List<string>();
            whereList.Add("u_workmanship_add.inuse = 1");
            whereList.Add("workmanshipname = @workmanshipname");
            var param = new Dictionary<string, object>();
            param.Add("@workmanshipname", label);

            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "mattressid", outputFields, 0, 0, list);

            return list;
        }
        /// <summary>
        /// 获取工厂利润率
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="field">旧表字段名</paramD>
        /// <param name="label">新表变量名</paramD>
        /// <returns></returns>
        public decimal GetWorkmanshipRate(u_mattress mattress, string field, string label, Boolean isNewFormula = true)
        {
            decimal rate = 0;
            if (field == "") return 0;

            if (isNewFormula)
            {
                var addList = GetWorkmanshipAdd(label);
                var tgList = addList.Where(o => o.deptid == mattress.deptid && o.mattresstypeid == mattress.mattresstypeid).ToList();
                if (tgList.Count <= 0) tgList = addList.Where(o => o.deptid == mattress.deptid).ToList();
                if (tgList.Count <= 0) tgList = addList.Where(o => o.mattresstypeid == mattress.mattresstypeid).ToList();
                if (tgList.Count <= 0) tgList = addList;
                if (tgList.Count > 0)
                {
                    var item = tgList[0];
                    rate = item.rate == null ? 0 : Convert.ToDecimal(item.rate);

                    var _formula = Convert.ToString(item.formula);
                    if (!string.IsNullOrEmpty(_formula))
                    {
                        AddKeyValue(label, rate);
                        rate = Calculate(_formula, label).DecimalValue;
                    }
                }
            }
            else
            {
                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);
                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)
        {
            var _typename = typename;
            if (mattress.dannum_type.Value != 1)
            {
                switch(mattress.dannum_type.Value)
                {
                    case 1:
                        _typename = "散单";
                        break;
                    case 2:
                        _typename = "标准";
                        break;
                    case 3:
                        _typename = "大单";
                        break;
                    case 4: // "小单":
                        _typename = "";
                        break;
                }
            }
            // 根据款式类型判断查询的字段
            string field = "散单".Equals(_typename) ? "num_9" : "标准".Equals(_typename) ? "num_10" : "大单".Equals(_typename) ? "num_11" : "";

            // 查找点数
            decimal _rate = GetWorkmanshipRate(mattress, field, typename);
            mattress.dannum_rate = _rate == 0 ? 1 : _rate;
            AddKeyValue("大小单", mattress.dannum_rate);
        }
        private decimal CalCulateDanNumRate(u_mattress mattress, string typename)
        {
            // 根据款式类型判断查询的字段
            string field = "散单".Equals(typename) ? "num_9" : "标准".Equals(typename) ? "num_10" : "大单".Equals(typename) ? "num_11" : "";

            // 查找点数
            decimal _rate = GetWorkmanshipRate(mattress, field, typename);
            return _rate;
        }
        /// <summary>
        /// 
        /// </summary>
        private void GetDannumRate(u_mattress mattress)
        {
            var mattressType = new u_mattress_type() { mattresstypeid = mattress.mattresstypeid };
            DbSqlHelper.SelectOne(cmd, mattressType, "hrcost_formula,biandaiqty,zhizao_amt,guanli_rate,typerate,dannum1_rate,dannum2_rate,dannum3_rate,dannum4_rate");

            var dept = new u_dept() { deptid = mattress.deptid.Value };
            DbSqlHelper.SelectOne(cmd, dept, "pricelistid,managerate,com_profitrate,dannum1_rate,dannum2_rate,dannum3_rate,dannum4_rate");
            decimal? com_profitrate_dannum = 0;

            decimal _rate = 1;
            if (mattress.dannum_type == null)
            {
                mattress.dannum_type = 2;
            }

            switch (mattress.dannum_type.Value)
            {
                case 1: // "散单":
                    _rate = mattressType.dannum1_rate.Value;
                    com_profitrate_dannum = dept.dannum1_rate;
                    break;
                case 2: // "标准":
                    _rate = mattressType.dannum2_rate.Value;
                    com_profitrate_dannum = dept.dannum2_rate;
                    break;
                case 3: // "大单":
                    _rate = mattressType.dannum3_rate.Value;
                    com_profitrate_dannum = dept.dannum3_rate;
                    break;
                case 4: // "小单":
                    _rate = mattressType.dannum4_rate.Value;
                    com_profitrate_dannum = dept.dannum4_rate;
                    break;
            }

            AddKeyValue("大小单类型", mattress.dannum_type.Value);
            mattress.dannum_rate = _rate;
            AddKeyValue("大小单系数", _rate);
            AddKeyValue("款式系数", mattressType.typerate.Value);

            #region 公式利润点
            AddKeyValue("管理费用点", dept.managerate);
            AddKeyValue("公司利润点", dept.com_profitrate + com_profitrate_dannum);
            #endregion
        }
        /// <summary>
        /// 计算成本
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typename"></param>
        private void CalCulateCostNew(u_mattress mattress, string typename, Boolean isNewFormula = false)
        {

            #region 判断大小单
            if (isNewFormula)
            {
                GetDannumRate(mattress);
            }
            #endregion

            #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 = 0;
            if (mattress.moneyrate > 0)
            {
                _waibi = CalculateVarFormula("外币价").DecimalValue;
            } else
            {
                _waibi = mattress.dept_cost.Value;
            }
            mattress.foreign_cost = _waibi;
            AddKeyValue("外币价", _waibi);
            #endregion

        }
        /// <summary>
        /// 保存床垫报价
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="mxlist"></param>
        /// <param name="subspecs">副规格列表</param>
        public void SaveMattress(u_mattress mattress,List<u_mattress_mx_mtrl> mxlist, List<u_mattress_mx_extra> extraProcesses = null, List<u_mattress_mx_extra> extraCosts = null, bool iferpmtrl = false)
        {
            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, extras_cost, extras_cost_dscrp, parentid, flag, xd_flag, dannum_type";
            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,mattress_width,mattress_length";
            var fieldsExtra = "mattressmxid,mattressid, extraid, extramxid, extraname, price, qty, dscrp";

            if (iferpmtrl)
            {
                fields += ",erp_mtrlcode,erp_mtrlid";
            }
                
            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();

                // 删除所有额外费用明细
                cmd.CommandText = @"DELETE u_mattress_mx_extra 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);
                }

            }

            if (extraProcesses != null && extraProcesses.Any())
            {
                foreach (var item in extraProcesses)
                {
                    item.mattressid = mattress.mattressid;
                    item.mattressmxid = BllHelper.GetID(cmd, "u_mattress_mx_extra");

                    DbSqlHelper.Insert(cmd, "u_mattress_mx_extra", null, item, fieldsExtra);
                }
            }

            if (extraCosts != null && extraCosts.Any())
            {
                foreach (var item in extraCosts)
                {
                    item.mattressid = mattress.mattressid;
                    item.mattressmxid = BllHelper.GetID(cmd, "u_mattress_mx_extra");

                    DbSqlHelper.Insert(cmd, "u_mattress_mx_extra", null, item, fieldsExtra);
                }
            }

            // 更新计价历史
            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,erp_mtrlcode,erp_mtrlid,dannum_type";
            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, formulakind, formula, formulaid, mtrlid, mtrlname, price, gram_weight, cloth_width, if_inputqty, qty , costamt, if_areaprice, formulatype, sortcode, if_mtrl, if_success, replace_formula, priceunit, shrinkage, name, dannum1_rate, dannum2_rate, dannum3_rate, dannum4_rate, thickness, chastr, xu, useqty, useformula, replace_useformula , gydscrp, mattress_width, mattress_length";
            var selectStr = @"
		                        SELECT
			                        u_mattress_mx_mtrl.mattressmxid,
			                        u_mattress_mx_mtrl.mattressid,
			                        u_mattress_formula.formulakind as formulakind ,
			                        u_mattress_mx_mtrl.formula as  formula,
			                        u_mattress_mx_mtrl.formulaid,
			                        u_mattress_mx_mtrl.mtrlid,
			                        CASE WHEN u_mattress_formula.formulatype = 99 THEN vv_bednet.bednetname ELSE vv_mtrl_price.mtrlname END AS mtrlname,
			                        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.costamt,
			                        u_mattress_mx_mtrl.if_areaprice,
			                        u_mattress_formula.formulatype,
			                        case when u_mattress_mx_mtrl.chastr <> '' then  u_mattress_mx_mtrl.chastr  else u_mattress_formula.sortcode end  as  sortcode,
			                        u_mattress_formula.if_mtrl,
			                        u_mattress_mx_mtrl.if_success,
			                        u_mattress_mx_mtrl.replace_formula,
			                        u_mattress_mx_mtrl.priceunit,
			                        u_mattress_mx_mtrl.shrinkage,
			                        CASE when u_mattress_formula.if_mtrl > 1 THEN '+' ELSE '' END as addmx,
			                        CASE when u_mattress_formula.if_mtrl > 1 THEN '-' ELSE '' END as delmx,
			                        u_mtrl_price.name,
			                        ISNULL(u_mtrltype.dannum1_rate, 0) AS dannum1_rate,
			                        ISNULL(u_mtrltype.dannum2_rate, 0) AS dannum2_rate,
			                        ISNULL(u_mtrltype.dannum3_rate, 0) AS dannum3_rate,
			                        ISNULL(u_mtrltype.dannum4_rate, 0) AS dannum4_rate,
			                        u_mattress_mx_mtrl.thickness,
			                        u_mattress_mx_mtrl.chastr,
			                        u_mattress_mx_mtrl.xu,
			                        u_mattress_mx_mtrl.useqty as useqty,
			                        u_mattress_mx_mtrl.useformula,
			                        u_mattress_mx_mtrl.replace_useformula ,
			                        u_mattress_mx_mtrl.gydscrp,
			                        u_mattress_mx_mtrl.mattress_width,
			                        u_mattress_mx_mtrl.mattress_length
		                        From u_mattress_mx_mtrl
		                        Left 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
		                        left outer join u_mtrltype on u_mtrltype.mtrltypeid = u_mtrl_price.mtrltype
		                        Left OUTER JOIN (
			                        select
				                        mtrlid,
				                        name as mtrlname
			                        from u_mtrl_price
			                        union all
				                        select top 1
				                        0 as mtrlid,
				                        ' ' as mtrlname
				                        from u_user_jlhprice
		                        ) AS vv_mtrl_price on vv_mtrl_price.mtrlid = u_mattress_mx_mtrl.mtrlid
		                        Left OUTER JOIN (
			                        SELECT u_bednet.bednetid,
			                        rtrim(u_bednet_type.typename)+ ' ' +  rtrim(u_bednet.bednetcode)+ ' '+cast(u_bednet.mattress_width as char(3))+ '*'
			                        +cast(u_bednet.mattress_length as char(3))+ '*'+cast(u_bednet.mattress_height as char(3)) as bednetname

			                        FROM u_bednet
			                        inner join u_bednet_type ON u_bednet_type.bednettypeid = u_bednet.bednettypeid
		                        ) as vv_bednet ON vv_bednet.bednetid = u_mattress_mx_mtrl.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>
        /// 其他额外费用tab / 特殊工艺列表
        /// </summary>
        /// <param name="mattress"></param>
        /// <param name="typeid"></param> 1: 特殊工艺。2：其他额外费用
        /// <returns></returns>
        public List<u_mattress_mx_extra> GetMattressMxExtra(int mattressid, int typeid)
        {
            var mxList = new List<u_mattress_mx_extra>();
            var outputFields = @"mattressmxid,mattressid,extraid,extramxid,extratypename,extraname,price,dscrp,qty";
            var selectStr = @"
		        SELECT
			        u_mattress_mx_extra.mattressid,
			        u_mattress_mx_extra.mattressmxid,
			        u_mattress_mx_extra.extraid,
			        u_mattress_mx_extra.extramxid,
			        u_mattress_mx_extra.extraname,
			        u_mattress_mx_extra.price,
			        u_mattress_mx_extra.qty,
			        u_mattress_mx_extra.dscrp
		        From u_mattress_mx_extra
		        Left OUTER JOIN u_mattress_extra_type On u_mattress_extra_type.extraid = u_mattress_mx_extra.extraid
		        Left OUTER JOIN u_mattress_extra On u_mattress_extra.extramxid = u_mattress_mx_extra.extramxid
            ";

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

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

            return mxList;
        }
        /// <summary>
        /// 获取副规格列表
        /// </summary>
        /// <param name="mattressid"></param>
        /// <returns></returns>
        public List<u_mattress> GetMattressSubspecs(int mattressid)
        {
            var list = new List<u_mattress>();
            var outputFields = @"mattressid,mattresscode,mattress_width,mattress_length,mattress_height,parentid";
            var selectStr = @"SELECT 
		                            mattressid,
		                            mattresscode,
		                            mattress_width,
		                            mattress_length,
		                            mattress_height,
		                            parentid
                                FROM u_mattress
                                ";
            var whereList = new List<string>();
            whereList.Add(@"u_mattress.parentid = @mattressid OR  parentid = (
                SELECT parentid FROM u_mattress WHERE mattressid = @mattressid and parentid > 0
            )");

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

            return list;
        }
        /// <summary>
        /// 获取副规格的床网弹簧排列数
        /// </summary>
        /// <param name="mattressid"></param>
        /// <returns></returns>
        public List<u_mattress_mx_mtrl> GetMattressSubspecsBednet(int mattressid)
        {
            var list = new List<u_mattress_mx_mtrl>();
            var outputFields = @"mattressid,mtrlid,spring_qty_width,spring_qty_length";
            var selectStr = @"SELECT
                                u_mattress_mx_mtrl.mattressid,
                                u_mattress_mx_mtrl.mattressmxid,
				                u_mattress_mx_mtrl.mtrlid,
				                ISNULL(u_bednetmx.spring_qty_width, 0) AS spring_qty_width,
				                ISNULL(u_bednetmx.spring_qty_length, 0) AS spring_qty_length
			                FROM u_mattress_mx_mtrl
			                LEFT JOIN u_mattress_formula On u_mattress_formula.formulaid = u_mattress_mx_mtrl.formulaid
			                LEFT OUTER JOIN u_bednetmx on u_bednetmx.bednetid = u_mattress_mx_mtrl.mtrlid
                                ";
            var whereList = new List<string>();
            whereList.Add("u_mattress_formula.formulatype = 99");
            whereList.Add("u_mattress_mx_mtrl.mattressid = @mattressid");

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

            return list;
        }
        /// <summary>
        /// 获取床垫明细中，垫层出现的主副规格调整物料
        /// </summary>
        /// <param name="mattressid"></param>
        /// <returns></returns>
        public List<u_mattress_mx_mtrl> GetMattressMxSubspecsMtrl(int mattressid)
        {
            var list = new List<u_mattress_mx_mtrl>();
            var outputFields = @"mattressid,mtrlid,chastr,formulakind,mtrlname";
            var selectStr = @"SELECT
                                u_mattress_mx_mtrl.mattressid,
                                u_mattress_mx_mtrl.mattressmxid,
				                u_mattress_mx_mtrl.mtrlid,
				                u_mattress_mx_mtrl.chastr,
				                u_mattress_formula.formulakind,
                                vv_mtrl_price.mtrlname
			                FROM u_mattress_mx_mtrl
			                LEFT JOIN u_mattress_formula On u_mattress_formula.formulaid = u_mattress_mx_mtrl.formulaid
			                LEFT OUTER JOIN u_mtrl_price on u_mtrl_price.mtrlid = u_mattress_mx_mtrl.mtrlid
		                    Left OUTER JOIN (
			                    select
				                    mtrlid,
				                    name as mtrlname
			                    from u_mtrl_price
			                    union all
				                    select top 1
				                    0 as mtrlid,
				                    ' ' as mtrlname
				                    from u_user_jlhprice
		                    ) AS vv_mtrl_price on vv_mtrl_price.mtrlid = u_mattress_mx_mtrl.mtrlid
                                ";
            var whereList = new List<string>();
            whereList.Add("u_mattress_formula.formulatype = 1");
            whereList.Add("u_mtrl_price.if_subspecs = 1");
            whereList.Add("u_mattress_mx_mtrl.mattressid = @mattressid");

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

            return list;
        }
        /// <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
                                    ,formulaid
	                                )
                                SELECT @mattressid
	                                ,printid
	                                ,itemname
	                                ,bj_pzname
	                                ,bj_pzname_mx
	                                ,mtrlid
	                                ,erp_mtrlid
	                                ,wrkgrpid
	                                ,useqty
	                                ,dscrp
	                                ,actual_useqty
	                                ,qd_actual_size
	                                ,qd_pfgroupqty
                                    ,formulaid
                                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>();
                mattress_copy.deptid = mattress.deptid;
                
                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;
                }

                var mattressMxExtra1List = GetMattressMxExtra(mattress.mattressid, 1);

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

                var mattressMxExtra2List = GetMattressMxExtra(mattress.mattressid, 2);

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

                try
                {
                    CalCulateFormula(mattress_copy, mattressMxMtrlList, true, true, mattressMxExtra1List, mattressMxExtra2List);
                }
                catch (Exception e)
                {
                    throw new LJCommonException(e.Message);
                }

                SaveMattress(mattress_copy, mattressMxMtrlList, mattressMxExtra1List, mattressMxExtra2List, true);

                var mattressSubspecs = GetMattressSubspecs(mattress.mattressid);

                foreach (var sub in mattressSubspecs)
                {
                    sub.parentid = mattress_copy.mattressid;
                    sub.mattressid = 0;
                    sub.mattresscode = "";

                    var mattressMxList = GetMattressMxMtrl(sub.mattressid);

                    foreach (var submx in mattressMxList)
                    {
                        submx.mattressid = 0;
                        submx.mattressmxid = 0;
                    }

                    var mattressMxExtra1 = GetMattressMxExtra(sub.mattressid, 1);

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

                    var mattressMxExtra2 = GetMattressMxExtra(sub.mattressid, 2);

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

                    SaveMattress(sub, mattressMxList, mattressMxExtra1, mattressMxExtra2);
                }

                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,wrkgrpid2,useqty,dscrp,actual_useqty,qd_actual_size,qd_pfgroupqty,ss_rate,ls_rate,sh_rate,formulaid,wrkgrpcode1,wrkgrpcode2");
            }


            AutoUpdateBcp(mattressid);

        }

        #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>
        public void AutoUpdateBcp(int mattressid)
        {

            var qdList = new List<u_mattress_interface_qd>();

            var selectStr = @"
                            SELECT u_mattress_interface_qd.mattressid
	                            ,u_mattress_interface_qd.printid
	                            ,u_mattress_interface_qd.itemname
	                            ,u_mattress_interface_qd.bj_pzname
	                            ,u_mattress_interface_qd.bj_pzname_mx
	                            ,u_mattress_interface_qd.bj_pzname_mx_mx
	                            ,u_mattress_interface_qd.mtrlid
	                            ,u_mattress_interface_qd.erp_mtrlid
	                            ,u_mattress_interface_qd.useqty
	                            ,u_mattress_interface_qd.dscrp
	                            ,u_mattress_interface_qd.actual_useqty
	                            ,u_mattress_interface_qd.qd_actual_size
	                            ,u_mattress_interface_qd.qd_pfgroupqty
	                            ,u_mattress_interface_qd.wrkgrpid
	                            ,u_mattress_interface_qd.ss_rate
	                            ,u_mattress_interface_qd.ls_rate
	                            ,u_mattress_interface_qd.sh_rate
	                            ,u_mattress_interface_qd.formulaid
                            FROM u_mattress_interface_qd
                                    ";
            var whereList = new List<string>();
            whereList.Add("mattressid = @mattressid");
            var param = new Dictionary<string, object>();
            param.Add("@mattressid", mattressid);
            var outFields = "mattressid, printid, itemname, bj_pzname, bj_pzname_mx, bj_pzname_mx_mx, mtrlid, erp_mtrlid, useqty, dscrp, actual_useqty, qd_actual_size, qd_pfgroupqty, wrkgrpid, ss_rate, ls_rate, sh_rate, formulaid";
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "printid", outFields, 0, 0, qdList);



            var chastrArr = new List<string>() { "裥面", "裥底", "顶布裥棉", "大侧", "小侧", "V侧" };
            var chastrType = new Dictionary<string, int>();
            Dictionary<string, List<u_mattress_interface_qd>> bcpQdlist = new Dictionary<string, List<u_mattress_interface_qd>>();

            foreach (var mx in qdList)
            {
                if (chastrArr.Contains(mx.itemname) && !string.IsNullOrEmpty(mx.bj_pzname_mx_mx))
                {
                    if (!chastrType.ContainsKey(mx.bj_pzname_mx_mx))
                    {
                        switch (mx.itemname)
                        {
                            case "裥面":
                                chastrType.Add(mx.bj_pzname_mx_mx, 901);
                                mx.billtype = 901;
                                break;
                            case "裥底":
                                chastrType.Add(mx.bj_pzname_mx_mx, 902);
                                mx.billtype = 902;
                                break;
                            case "大侧":
                                chastrType.Add(mx.bj_pzname_mx_mx, 903);
                                mx.billtype = 903;
                                break;
                            case "小侧":
                                chastrType.Add(mx.bj_pzname_mx_mx, 904);
                                mx.billtype = 904;
                                break;
                            case "V侧":
                                chastrType.Add(mx.bj_pzname_mx_mx, 905);
                                mx.billtype = 905;
                                break;
                        }
                    }
                    if (!bcpQdlist.ContainsKey(mx.bj_pzname_mx_mx))
                    {
                        List<u_mattress_interface_qd> list = new List<u_mattress_interface_qd>();
                        list.Add(mx);
                        bcpQdlist.Add(mx.bj_pzname_mx_mx, list);
                    }
                    else
                    {
                        bcpQdlist[mx.bj_pzname_mx_mx].Add(mx);
                    }
                }

            }

            foreach (var mx in bcpQdlist)
            {
                var billid = 0;
                cmd.CommandText = @"SELECT TOP 1 billid
                                    FROM u_semi_finished_product
                                    WHERE semi_finished_name = @semi_finished_name";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@semi_finished_name", mx.Key);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        billid = Convert.ToInt32(reader["billid"]);
                    }
                }
                if (billid == 0)
                {
                    //创建
                    var bcpMattress = new u_semi_finished_product() { billid = 0 };

                    AutoInit.AutoInitS(cmd, bcpMattress);

                    bcpMattress.billid = 0;
                    bcpMattress.billdate = DateTime.Now;
                    bcpMattress.dscrp = "自动生成，来自mattressid:" + mattressid;
                    //bcpMattress.production_size = DateTime.Now;
                    //bcpMattress.hemming_process = DateTime.Now;
                    //bcpMattress.hemmed_size = DateTime.Now;
                    //bcpMattress.shrinkage_rate = DateTime.Now;
                    //bcpMattress.elongation_rate = DateTime.Now;
                    bcpMattress.opemp = context.tokendata.username;
                    bcpMattress.opdate = DateTime.Now;
                    bcpMattress.semi_finished_type = chastrType[mx.Key];
                    bcpMattress.semi_finished_code = mx.Key;
                    bcpMattress.semi_finished_name = mx.Key;

                    var bcpMxList = new List<u_semi_finished_product_mx>();
                    foreach (var qdmx in mx.Value)
                    {
                        var bcpmx = new u_semi_finished_product_mx()
                        {
                            mtrlid = qdmx.mtrlid.Value,
                            erp_mtrlid = qdmx.erp_mtrlid,
                            formulaid = qdmx.formulaid
                        };
                        bcpMxList.Add(bcpmx);
                    }

                    bcpMattress.mxlist = bcpMxList;

                    SaveMattressBcp(bcpMattress);
                }
                else
                {
                    // 判断不一样，就覆盖
                    selectStr = @"
                                    SELECT u_semi_finished_product_mx.billid
	                                    ,u_semi_finished_product_mx.printid
	                                    ,u_semi_finished_product_mx.mtrlid
	                                    ,u_semi_finished_product_mx.formulaid
	                                    ,u_semi_finished_product_mx.chastr
	                                    ,u_semi_finished_product_mx.thickness
	                                    ,u_semi_finished_product_mx.qty
	                                    ,u_semi_finished_product_mx.erp_mtrlid
	                                    ,u_semi_finished_product_mx.usenum
	                                    ,u_semi_finished_product_mx.usedenom
                                    FROM u_semi_finished_product_mx
                                    ";
                    var bcpMxList = new List<u_semi_finished_product_mx>();
                    whereList = new List<string>();
                    whereList.Add("u_semi_finished_product_mx.billid = @billid");
                    param.Clear();
                    param.Add("@billid", billid);
                    outFields = "billid, printid, mtrlid, formulaid, chastr, thickness, qty, erp_mtrlid, usenum, usedenom, mtrlname, if_mtrl, formula, formulatype, formulakind";
                    DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "formulatype, sortcode", outFields, 0, 0, bcpMxList);

                    var ErpMtrlids = "";
                    var BcpErpMtrlids = "";

                    foreach (var qdmx in mx.Value)
                    {
                        ErpMtrlids += qdmx.erp_mtrlid + "+";
                    }

                    foreach (var qdmx in bcpMxList)
                    {
                        BcpErpMtrlids += qdmx.erp_mtrlid + "+";
                    }

                    if (ErpMtrlids != BcpErpMtrlids)
                    {
                        var bcpMattress = new u_semi_finished_product() { billid = billid };
                        DbSqlHelper.SelectOne(cmd, bcpMattress, "billid, billdate, flag, semi_finished_type, semi_finished_code, semi_finished_name, dscrp, production_size, hemming_process, hemmed_size, shrinkage_rate, elongation_rate, opemp, opdate, auditemp, auditdate");

                        bcpMattress.billid = billid;
                        bcpMattress.auditemp = context.tokendata.username;
                        bcpMattress.auditdate = DateTime.Now;

                        foreach (var qdmx in bcpMxList)
                        {
                            var mtrlid = 0;
                            var arr = mx.Value.Where((itm) => itm.mtrlid == qdmx.mtrlid).ToList();
                            if (arr.Count > 0)
                            {
                                mtrlid = arr[0].erp_mtrlid.Value;
                            }
                            qdmx.erp_mtrlid = mtrlid;
                            qdmx.formulaid = arr[0].formulaid;
                        }

                        bcpMattress.mxlist = bcpMxList;

                        SaveMattressBcp(bcpMattress);
                    }
                }
            }

        }

        /// <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)
        {
            key = $"【{key}】";
            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)
        {
            key = $"【{key}】";
            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 Calculate(formula_replacements[varname], varname);
            if (formula_replacements_hide.ContainsKey(varname)) return Calculate(formula_replacements_hide[varname], varname);
            return null;
        }
        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;
                }
            }
            foreach (var replacement in formula_replacements_hide)
            {
                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

        #region 导入包装明细
        /// <summary>
        /// 导入 2-按包装方式  的公式
        /// </summary>
        /// <param name="packtype">包装方式</param>
        /// <param name="dw_2">结果导出</param>
        public void wf_import_bz(int packtype, ref List<u_mattress_mx_mtrl> dw_2)
        {
             var selectStr = @"
                            SELECT formulaid
	                            ,formulakind
	                            ,formulatype
	                            ,sortcode
	                            ,if_mtrl
	                            ,formula
	                            ,usetype
	                            ,if_packtype0
	                            ,if_packtype1
	                            ,if_packtype2
	                            ,default_mtrlid
	                            ,createtime
	                            ,createby
	                            ,useformula
	                            ,gydscrp
	                            ,mtrltype
                            FROM u_mattress_formula";
             var outFields = "formulaid, formulakind, formulatype, sortcode, if_mtrl, formula, usetype, if_packtype0, if_packtype1, if_packtype2, default_mtrlid, createtime, createby, useformula, gydscrp, mtrltype";
             var whereList = new List<string>();
             whereList.Add("u_mattress_formula.formulatype IN (0, 1, 2, 3, 99, 104)");
             whereList.Add("u_mattress_formula.usetype = @usetype");
             var param = new Dictionary<string, object>();

            //   导入 2-按包装方式  的公式
            var mattressFormulaList = new List<u_mattress_formula>();
            param.Clear();
            param.Add("@usetype", 2);
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "formulatype, sortcode", outFields, 0, 0, mattressFormulaList);

            foreach (var mx in mattressFormulaList)
            {
                if (packtype == 0 && mx.if_packtype0 != 1) continue;
                if (packtype == 1 && mx.if_packtype1 != 1) continue;
                if (packtype == 2 && mx.if_packtype2 != 1) continue;

                var newDw2 = new u_mattress_mx_mtrl();
                AutoInit.AutoInitS(newDw2);
                if (mx.if_mtrl > 1)
                {
                    newDw2.addmx = "+";
                    newDw2.delmx = "-";
                }
                newDw2.formulatype = mx.formulatype;
                newDw2.if_mtrl = mx.if_mtrl;
                newDw2.formulaid = mx.formulaid;
                newDw2.sortcode = mx.sortcode;
                newDw2.formulakind = mx.formulakind;
                newDw2.formula = mx.formula;
                newDw2.useformula = mx.useformula;
                newDw2.gydscrp = mx.gydscrp;

                dw_2.Add(newDw2);
            }

        }
        #endregion

        #region 明细排序
        /// <summary>
        /// 明细排序
        /// </summary>
        /// <param name="dw_2"></param>
        public void wf_sort_mx(ref List<u_mattress_mx_mtrl> dw_2)
        {
            // u_mattress_formula_formulatype A xu A u_mattress_formula_sortcode A u_mattress_mx_mtrl_formulaid A
            dw_2.Sort((a, b) =>
            {
                if (a.formulatype != b.formulatype)
                {
                    return a.formulatype.Value.CompareTo(b.formulatype.Value);
                }
                if (a.xu != b.xu)
                {
                    return a.xu.Value.CompareTo(b.xu.Value);
                }
                if (a.sortcode != b.sortcode)
                {
                    return a.sortcode.CompareTo(b.sortcode);
                }

                int result = GetFormulakindOrder(a.formulakind).CompareTo(GetFormulakindOrder(b.formulakind));
                if (result != 0)
                {
                    return result;
                }

                return a.formulaid.Value.CompareTo(b.formulaid.Value);
            });
        }
        #endregion

        // 普通大侧特殊排序
        private int GetFormulakindOrder(int? formulakind)
        {
            switch (formulakind)
            {
                case 2: return 1;
                case 82: return 2;
                case 42: return 3;
                case 52: return 4;
                case 62: return 5;
                case 72: return 6;
                default: return int.MaxValue;
            }
        }

        #region 获取物料信息
        public void wf_mtrl_fine(List<int> arg_mtrlid, int arg_pricelistid, ref List<u_mtrl_price> mtrlList)
        {
            var selectStr = @"
                            SELECT
                                u_mtrl_price.mtrlid,
                                u_mtrl_price.name,
                                isnull(u_mtrl_price_pricelist.price,0) as price,
                                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";
            var outFields = "mtrlid, name, price, gram_weight, cloth_width, if_inputqty, priceunit, shrinkage, price_formula, qty_formula, if_areaprice, thickness";
            var whereList = new List<string>();
            whereList.Add("u_mtrl_price.mtrlid IN (" + string.Join(",", arg_mtrlid) + ")");
            whereList.Add("u_mtrl_price_pricelist.pricelistid = @arg_pricelistid");
            var param = new Dictionary<string, object>();

            param.Clear();
            param.Add("@arg_pricelistid", arg_pricelistid);
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), param, "", outFields, 0, 0, mtrlList);
        }
        #endregion

        #region 获取包装默认物料
        /// <summary>
        /// 获取包装默认物料
        /// </summary>
        /// <param name="dw_2"></param>
        public void wf_default_mtrlid(int pricelistid, ref List<u_mattress_mx_mtrl> dw_2)
        {
            //   导入 2-按包装方式  的公式
            var mtrlPriceList = new List<u_mtrl_price>();
            wf_mtrl_fine(new List<int> { 46804, 46809, 46811, 46817, 46872 }, pricelistid, ref mtrlPriceList);

            foreach (var mx in dw_2)
            {
                var _mtrlid = 0;
                if (mx.formulakind == 16)
                {
                    //默认 pe    kind = 16  默认  10C PE袋  mtrlid  46804
                    _mtrlid = 46804;
                }
                else if (mx.formulakind == 17)
                {
                    //默认 外层pe    kind = 17  默认  20C  mtrlid  46809
                    _mtrlid = 46809;
                }
                else if (mx.formulakind == 18)
                {
                    //默认 pvc    kind = 18  默认  15C   mtrlid  46811
                    _mtrlid = 46811;
                }
                else if (mx.formulakind == 19)
                {
                    //默认 卷包外包装    kind = 19  默认  彩箱   32*32   46817
                    _mtrlid = 46817;
                }
                else if (mx.formulakind == 27)
                {
                    //默认 拉手脚轮    kind = 27  默认     46872
                    _mtrlid = 46872;
                }

                var mtrlItm = mtrlPriceList.Where((itm) => itm.mtrlid == _mtrlid).ToList();

                if (mtrlItm.Count > 0)
                {
                    mx.mtrlid = mtrlItm[0].mtrlid;
                    mx.mtrlname = mtrlItm[0].name;
                    mx.price = mtrlItm[0].price;
                    mx.gram_weight = mtrlItm[0].gram_weight;
                    mx.cloth_width = mtrlItm[0].cloth_width;
                    mx.if_inputqty = mtrlItm[0].if_inputqty;
                    mx.priceunit = mtrlItm[0].priceunit;
                    mx.shrinkage = mtrlItm[0].shrinkage;
                    mx.qty = 1;
                    if (!string.IsNullOrEmpty(mtrlItm[0].price_formula)) mx.formula = mtrlItm[0].price_formula;
                }
            }
        }
        #endregion

        #region 获取非包装默认物料
        /// <summary>
        /// 获取非包装默认物料
        /// </summary>
        /// <param name="dw_2"></param>
        public void wf_default_mtrlid_notbz(int pricelistid, ref List<u_mattress_mx_mtrl> dw_2)
        {
            foreach (var mx in dw_2)
            {
                var matterssformula = new u_mattress_formula() { formulaid = mx.formulaid };
                if (DbSqlHelper.SelectOne(cmd, matterssformula, "default_mtrlid") != 1 || matterssformula.default_mtrlid.Value == 0) continue;

                //   导入 2-按包装方式  的公式
                var mtrlItm = new List<u_mtrl_price>();
                wf_mtrl_fine(new List<int> { matterssformula.default_mtrlid.Value }, pricelistid, ref mtrlItm);

                if (mtrlItm.Count > 0)
                {
                    mx.mtrlid = mtrlItm[0].mtrlid;
                    mx.mtrlname = mtrlItm[0].name;
                    mx.price = mtrlItm[0].price;
                    mx.gram_weight = mtrlItm[0].gram_weight;
                    mx.cloth_width = mtrlItm[0].cloth_width;
                    mx.if_inputqty = mtrlItm[0].if_inputqty;
                    mx.priceunit = mtrlItm[0].priceunit;
                    mx.shrinkage = mtrlItm[0].shrinkage;
                    mx.qty = 1;
                    if (!string.IsNullOrEmpty(mtrlItm[0].price_formula)) mx.formula = mtrlItm[0].price_formula;
                }
            }
        }
        #endregion

        #region 保存半成品部件
        public void SaveMattressBcp(u_semi_finished_product mattress)
        {
            var dtNow = DateTime.Now;

            var fields = @"billdate,semi_finished_type,semi_finished_code,semi_finished_name,dscrp,production_size,hemming_process,
                                        hemmed_size,shrinkage_rate,elongation_rate,opemp,opdate,auditemp,auditdate";
            var fieldsMx = "billid,printid,mtrlid,formulaid,chastr,thickness,qty,erp_mtrlid,usenum,usedenom";

            if (mattress.billid <= 0)
            {
                //新建
                fields += ",billid,billcode";

                mattress.opdate = dtNow;
                mattress.opemp = context.tokendata.username;

                mattress.billid = BllHelper.GetID(cmd, "u_semi_finished_product");

                switch (mattress.semi_finished_type)
                {
                    case 901:
                        mattress.billcode = "JM";
                        break;
                    case 902:
                        mattress.billcode = "JD";
                        break;
                    case 903:
                        mattress.billcode = "BC";
                        break;
                    case 904:
                        mattress.billcode = "XC";
                        break;
                    case 905:
                        mattress.billcode = "VC";
                        break;
                }

                // 编号-年月日+流水
                mattress.billcode = $"{mattress.billcode}-{dtNow.ToString("yyyyMMdd")}{(mattress.billid.Value % 10000).ToString("D4")}";

                DbSqlHelper.Insert(cmd, "u_semi_finished_product", null, mattress, fields);
            }
            else
            {
                //修改
                cmd.CommandText = @"DELETE FROM u_semi_finished_product_mx WHERE billid = @billid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@billid", mattress.billid);
                cmd.ExecuteNonQuery();

                DbSqlHelper.Update(cmd, "u_semi_finished_product", null, mattress, "billid", fields);
            }

            var cnt = 0;
            foreach (var mx in mattress.mxlist)
            {
                AutoInit.AutoInitS(cmd, mx);

                mx.billid = mattress.billid;
                mx.printid = ++cnt;

                DbSqlHelper.Insert(cmd, "u_semi_finished_product_mx", null, mx, fieldsMx);
            }

            var hisprice = new u_his_price
            {
                bednetid_mattressid = mattress.billid,
                typeid = 2,
                cmpdate = dtNow,
                cmpemp = context.tokendata.username,
                nottax_dept_cost = 0,
                dept_cost = 0,
                foreign_cost = 0
            };
            var fieldsHs = "bednetid_mattressid, typeid, cmpdate, cmpemp, nottax_dept_cost, dept_cost, foreign_cost";
            DbSqlHelper.Insert(cmd, "u_his_price", null, hisprice, fieldsHs);

        }
        #endregion
    }
}