JLH/JLHHJSvr/Helper/SoftBedHelper.cs

using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using LJLib.DAL.SQL;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace JLHHJSvr.Helper
{
    internal class SoftBedHelper : HelperBase
    {
		/// <summary>
		/// 获取软床报价单据
		/// </summary>
		/// <param name="billid"></param>
		/// <param name="fields"></param>
		/// <param name="bill"></param>
        public u_softbed GetSoftBed(int billid,string fields)
        {
            fields = fields ?? @"softbed_id,softbed_code,softbed_name,deptid,create_time,create_by,mtrlmode,mtrltype,has_headboard,has_nightstand,has_bedframe,is_template,
                                template_id,template_code,template_name,commission,taxes,other_rate,extras_cost,moneyrate,dscrp,costamt,nottax_factory_cost,nottax_dept_cost,
                                dept_cost,foreign_cost";
            var bill = new u_softbed() { softbed_id = billid };
            DbSqlHelper.SelectOne(cmd, bill, fields);

			return bill;
        }

		/// <summary>
		/// 获取软床报价单据明细
		/// </summary>
		/// <param name="billid"></param>
		/// <param name="fields"></param>
		/// <param name="mxlist"></param>
        public void GetSoftBedMxList(int billid,string fields,out List<u_softbed_mx> mxlist,out List<u_configure_codemx> codeList)
        {
            fields = fields ?? @"softbed_id,printid,pzid,pz_printid,mtrlid,mtrlname,mtrlcode,mtrlmode,unit,has_type,allow_edit,cutting_length,cutting_width,cutting_qty,
                                useqty,use_formula,use_formula_str,actual_useqty,loss_rate,price,price_formula,price_formula_str,cost_price,cost_amt,pzname,pzmxname";
            mxlist = new List<u_softbed_mx>();

            var selectStr = @"SELECT u_softbed_mx.softbed_id
								,u_softbed_mx.printid
								,u_softbed_mx.pzid
								,u_softbed_mx.pz_printid
								,u_softbed_mx.mtrlid
								,u_softbed_mx.mtrlname
								,u_softbed_mx.mtrlcode
								,u_softbed_mx.mtrlmode
								,u_softbed_mx.unit
								,u_softbed_mx.has_type
								,u_softbed_mx.allow_edit
								,u_softbed_mx.cutting_length
								,u_softbed_mx.cutting_width
								,u_softbed_mx.cutting_qty
								,u_softbed_mx.useqty
								,u_softbed_mx.use_formula
								,u_softbed_mx.use_formula_str
								,u_softbed_mx.actual_useqty
								,u_softbed_mx.loss_rate
								,u_softbed_mx.price
								,u_softbed_mx.price_formula
								,u_softbed_mx.price_formula_str
								,u_softbed_mx.cost_price
								,u_softbed_mx.cost_amt
								,u_configure_code.name AS pzname
								,u_configure_codemx.namemx AS pzmxname
							FROM u_softbed_mx
								INNER JOIN u_configure_codemx ON u_configure_codemx.pzid = u_softbed_mx.pzid
											AND u_configure_codemx.printid = u_softbed_mx.pz_printid
								INNER JOIN u_configure_code ON u_configure_code.pzid = u_configure_codemx.pzid";

            DbSqlHelper.SelectJoin(cmd, selectStr, "softbed_id = @billid", new Dictionary<string, object>() { { "@billid", billid } }, "printid,pz_printid", fields, 0, 0, mxlist);

            codeList = mxlist.GroupBy(item => new { item.pzid, item.pz_pringid })
                            .Select(group => new u_configure_codemx
                            {
                                pzid = group.Key.pzid,
                                printid = group.Key.pz_pringid,
                                pzname = group.First().pzname,
                                namemx = group.First().pzmxname,
                            })
                            .ToList();
        }

		/// <summary>
		/// 获取软床报价单据明细
		/// </summary>
		/// <param name="billid"></param>
		/// <param name="fields"></param>
		/// <param name="mxlist"></param>
        public void GetSoftBedMxList(int billid, string fields, out List<u_softbed_mx> mxlist)
        {
            GetSoftBedMxList(billid, fields, out mxlist, out _);
        }

        /// <summary>
        /// 根据部件配置项值获取对应BOM明细
        /// </summary>
        /// <param name="mxlist"></param>
        /// <param name="codeList"></param>
        public List<u_softbed_mx> GetSoftBedMxBomList(int deptid, List<u_configure_codemx> codeList)
		{
			var mxlist = new List<u_softbed_mx>();

			foreach(var code in codeList)
			{
				var resultList = new List<u_configure_codemxbom>();
				var outputFields = @"pzid,printid,pid,mtrlid,sonscale,sonscale_formula,mng_cost_rate,profit_rate,realqty,cost,cost_emp,cost_date,sonloss,sonloss_formula,
									sondecloss,sondecloss_formula,deptid_scll,price,price_formula,mtrlcode,mtrlname,mtrlmode,unit,mtrlsectype,zxmtrlmode,usermtrlmode";
				var selectStr = @"SELECT u_configure_codemxbom.pzid
										,u_configure_codemxbom.printid
										,u_configure_codemxbom.pid
										,u_configure_codemxbom.mtrlid
										,u_configure_codemxbom.sonscale
										,u_configure_codemxbom.sonscale_formula
										,u_configure_codemxbom.mng_cost_rate
										,u_configure_codemxbom.profit_rate
										,u_configure_codemxbom.realqty
										,u_configure_codemxbom.cost
										,u_configure_codemxbom.cost_emp
										,u_configure_codemxbom.cost_date
										,u_configure_codemxbom.sonloss
										,u_configure_codemxbom.sonloss_formula
										,u_configure_codemxbom.sondecloss
										,u_configure_codemxbom.sondecloss_formula
										,u_configure_codemxbom.deptid_scll
										,u_configure_codemxbom.price
										,u_configure_codemxbom.price_formula
										,u_mtrldef.mtrlcode
										,u_mtrldef.mtrlname
										,u_mtrldef.mtrlmode
										,u_mtrldef.unit
										,u_mtrldef.mtrlsectype
										,u_mtrldef.zxmtrlmode
										,u_mtrldef.usermtrlmode
									FROM u_configure_codemxbom
									INNER JOIN u_mtrldef ON u_configure_codemxbom.mtrlid = u_mtrldef.mtrlid";
				DbSqlHelper.SelectJoin(cmd,selectStr, 
					"u_configure_codemxbom.pzid = @pzid AND u_configure_codemxbom.printid = @printid AND u_configure_codemxbom.deptid_scll = @deptid",
					new Dictionary<string, object>() { { "@pzid", code.pzid }, { "@printid",code.printid},{ "@deptid",deptid } }, 
					"u_configure_codemxbom.pid", outputFields, 0,0,resultList);

				foreach(var item in resultList)
				{
					mxlist.Add(InitSoftBedMxFromBom(item));

					// 
				}
			}


			return null;
        }

		public u_softbed_mx InitSoftBedMxFromBom(u_configure_codemxbom bomItem)
		{
			var mx = new u_softbed_mx()
			{
				pzid = bomItem.pzid,
				pz_pringid = bomItem.printid,
				mtrlid = bomItem.mtrlid,
				mtrlname = bomItem.mtrlname,
				mtrlcode = bomItem.mtrlcode,
				mtrlmode = bomItem.mtrlmode,
				unit = bomItem.unit,
				allow_edit = 0,
				has_type = 1, // 1-床头、2-床头柜、4-床架
				cutting_length = 0,
				cutting_qty = 0,
				cutting_width = 0,
				useqty = bomItem.sonscale,
				use_formula = "",
				use_formula_str = bomItem.sonscale_formula,
				actual_useqty = 0,
				loss_rate = bomItem.sonloss,
				price = bomItem.price,
				price_formula = "",
				price_formula_str = bomItem.price_formula,
				cost_price = bomItem.cost,
				cost_amt = 0
			};

			// 用量

			// 实际用量: 用量 * 损耗
			mx.actual_useqty = mx.useqty * mx.loss_rate;
			// 成本金额: 实际用量 * 成本单价
			mx.cost_amt = mx.actual_useqty * mx.cost_price;

			return mx;
        }

        #region 通用公式方法
        private string ConvertToEnglishSymbols(string input)
        {
            input = input.Replace("（", "(")
                 .Replace("）", ")")
                 .Replace("，", ",")
                 .Replace("。", ".")
                 .Replace("：", ":")
                 .Replace("；", ";")
                 .Replace("“", "\"")
                 .Replace("”", "\"")
                 .Replace("‘", "'")
                 .Replace("’", "'");

            return input;
        }
        private void FormatExpression(string expression)
		{
            expression = ConvertToEnglishSymbols(expression);


        }
        #endregion
    }
}