JLH/JLHHJSvr/Helper/SoftBedHelper.cs

using DirectService.Tools;
using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using JLHHJSvr.Tools;
using LJLib.DAL.SQL;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using static JLHHJSvr.Helper.CacheHelper;

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 = null)
        {
            fields = fields ?? @"softbed_id,softbed_code,softbed_relcode,softbed_name,deptid,create_date,create_emp,mtrlmode,mtrltype,has_headboard,has_nightstand,has_bedframe,is_template,
                                template_id,template_code,template_name,commission,taxes,taxrate,other_rate,extras_cost,money_type,moneyrate,dscrp,costamt,nottax_factory_cost,nottax_dept_cost,
                                dept_cost,foreign_cost";
            var bill = new u_softbed() { softbed_id = billid };
			if (DbSqlHelper.SelectOne(cmd, bill, fields) == 0) return null;
			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 List<u_softbed_mx> GetSoftBedMxList(int billid, string fields = null)
        {
            GetSoftBedMxList(billid, fields, out var mxlist, out _);
			return mxlist;
        }
        /// <summary>
        /// 根据部件配置项值获取对应BOM明细
        /// </summary>
        /// <param name="mxlist"></param>
        /// <param name="codeList"></param>
        public List<u_softbed_mx> GetSoftBedMxBomList(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,default_length,default_width,default_qty,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.default_length
										,u_configure_codemxbom.default_width
										,u_configure_codemxbom.default_qty
										,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",
					new Dictionary<string, object>() { { "@pzid", code.pzid }, { "@printid",code.printid} }, 
					"u_configure_codemxbom.pid", outputFields, 0,0,resultList);

				foreach(var item in resultList)
				{
					mxlist.Add(InitSoftBedMxFromBom(item));
				}
			}
			return mxlist;
        }
		/// <summary>
		/// 初始化配置项值带出的BOM清单列表
		/// </summary>
		/// <param name="bomItem"></param>
		/// <returns></returns>
		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 = bomItem.has_type, // 1-床头、2-床头柜、4-床架
				cutting_length = bomItem.default_length,
				cutting_width = bomItem.default_width,
				cutting_qty = bomItem.default_qty,
				useqty = bomItem.sonscale,
				use_formula = "",
				use_formula_str = bomItem.sonscale_formula,
				actual_useqty = 0,
				loss_rate = bomItem.sonloss,
				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;
        }

		/// <summary>
		/// 新建/修改 软床报价
		/// </summary>
		/// <param name="softbed"></param>
		/// <exception cref="LJCommonException"></exception>
		public void SaveSoftBed(u_softbed softbed)
		{
			if (softbed == null) throw new LJCommonException("软床报价保存失败,数据异常!");
			if (softbed.is_template == 0 && (softbed.mxList == null || softbed.mxList.Count <= 0)) throw new LJCommonException("软床报价保存失败，明细内容为空!");

			// 初始化属性
			AutoInit.AutoInitS(softbed);

			foreach(var mx in softbed.mxList)
			{
				AutoInit.AutoInitS(mx);
			}

			// TODO:计算价格
			CalCulateFormula(softbed);
            //
            var dtNow = context.opdate;
			var fields = @"softbed_relcode,softbed_name,deptid,mtrlmode,mtrltype,has_headboard,has_nightstand,has_bedframe,is_template,
                            template_id,template_code,template_name,commission,taxes,taxrate,other_rate,extras_cost,money_type,moneyrate,dscrp,costamt,nottax_factory_cost,nottax_dept_cost,
                            dept_cost,foreign_cost";

			var mx_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";

			if(softbed.softbed_id == 0)
			{
				// 新建
				fields += ",softbed_id,softbed_code,create_date,create_emp";

                softbed.softbed_id = BllHelper.GetID(cmd, "u_softbed");
                softbed.create_date = dtNow;
                softbed.create_emp = context.tokendata.username;

                if (string.IsNullOrEmpty(softbed.softbed_code))
                {
                    // 编号-年月日+流水
                    softbed.softbed_code = $"RC-{dtNow.ToString("yyyyMMdd")}{(softbed.softbed_id % 10000).ToString("D4")}";
                }

                DbSqlHelper.Insert(cmd, "u_softbed", null, softbed, fields);
            } else
			{
				// 修改
				softbed.update_date = dtNow;
				softbed.update_emp = context.tokendata.username;
                fields += ",update_date,update_emp";

                cmd.CommandText = @"DELETE FROM u_softbed_mx WHERE softbed_id = @softbed_id";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@softbed_id", softbed.softbed_id);
                cmd.ExecuteNonQuery();

                DbSqlHelper.Update(cmd, "u_softbed", null, softbed, "softbed_id", fields);
            }

            for (int i = 0; i < softbed.mxList.Count; i++)
            {
                var mx = softbed.mxList[i];

                mx.softbed_id = softbed.softbed_id;
				mx.printid = i + 1;

				DbSqlHelper.Insert(cmd, "u_softbed_mx", null, mx, mx_fields);
            }

			SaveSoftBedTemplate(softbed);
        }
		/// <summary>
		/// 新建软床报价模板
		/// </summary>
		/// <param name="softbed"></param>
		private void SaveSoftBedTemplate(u_softbed softbed)
		{
			if (softbed.is_template == 0) return;

			if(string.IsNullOrEmpty(softbed.softbed_code))
			{
				throw new LJCommonException("报价未生成唯一码,无法生成模板配置");
			}

			string prefix = $"{softbed.softbed_code}|";
			var configureList = new List<u_configure_type>();
			if(softbed.has_headboard == 1)
			{
                configureList.Add(new u_configure_type()
				{
					contfigtypeid = 0,
                    contfigtypename = $"{prefix}床头",
                    contfigtype = 0,
                    usechflag = 1,
					flag = 0
                });
			}

			if(softbed.has_bedframe == 1)
			{
                configureList.Add(new u_configure_type()
                {
                    contfigtypeid = 0,
                    contfigtypename = $"{prefix}床架",
                    contfigtype = 0,
                    usechflag = 1,
                    flag = 0
                });
            }

            if (softbed.has_nightstand == 1)
            {
                configureList.Add(new u_configure_type()
                {
                    contfigtypeid = 0,
                    contfigtypename = $"{prefix}床头柜",
                    contfigtype = 0,
                    usechflag = 1,
                    flag = 0
                });
            }


			var baseInfoHelper = GetHelper<BasicInfoHelper>(cmd, context);
			foreach(var configure in configureList)
            {
                // 判断是否已存在
                cmd.CommandText = @"SELECT COUNT(*) FROM u_configure_type WHERE LTRIM(RTRIM(contfigtypename)) = @contfigtypename";
                cmd.Parameters.Clear();
				cmd.Parameters.AddWithValue("@contfigtypename", configure.contfigtypename);
				var cnt = Convert.ToInt32(cmd.ExecuteScalar());

				if (cnt > 0) continue;
                baseInfoHelper.SaveConfigureType(configure);
            }
        }
		public List<u_configure_code> GetSoftBedConfigureCodeList(u_softbed softbed)
		{
			var codeList = new List<u_configure_code>();

			var selectStr = @"SELECT LTRIM(RTRIM(u_configure_type.configurename)) AS configurename
								,u_configure_code.typeid
								,u_configure_code.pzid
								,u_configure_code.name
								,u_configure_code.pzcode
								,u_configure_code.inputtype
							FROM u_configure_code
							INNER JOIN u_configure_type ON u_configure_code.typeid = u_configure_type.configureid
							WHERE LTRIM(RTRIM(u_configure_type.configurename)) IN ()";

			var whereList = new List<string>();
			var nameList = new List<string>();
            if (softbed.softbed_id > 0)
            {
                // 模版配置获取
                var _softbed = GetSoftBed(softbed.softbed_id, "softbed_id,softbed_code,has_headboard,has_nightstand,has_bedframe");

                string prefix = $"{_softbed.softbed_code}|";

				if(_softbed.has_headboard == 1)
				{
					nameList.Add($"{prefix}床头");
                }

                if (_softbed.has_nightstand == 1)
                {
                    nameList.Add($"{prefix}床头柜");
                }

                if (_softbed.has_bedframe == 1)
                {
                    nameList.Add($"{prefix}床架");
                }
            }

			if(nameList.Count > 0)
			{
				whereList.Add($"LTRIM(RTRIM(u_configure_type.configurename)) IN {ListEx.getString(nameList)}");
			}
        }
		/// <summary>
		/// 复制软床报价
		/// </summary>
		/// <param name="softbed"></param>
		/// <param name="newId"></param>
        public void CopySoftBed(u_softbed softbed, out int newId)
        {
			var fields = @"mtrlmode,mtrltype,has_headboard,has_nightstand,has_bedframe,is_template,
                            template_id,template_code,template_name,commission,taxes,taxrate,other_rate,extras_cost,moneyrate";
            var softbed_copy = GetSoftBed(softbed.softbed_id, fields);


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

            softbed_copy.softbed_code += " @@";
            softbed_copy.softbed_code += DateTime.Now.ToString("yyyMMdd_mmhhss");
            softbed_copy.softbed_id = 0;

            SaveSoftBed(softbed_copy);

            newId = softbed_copy.softbed_id;
        }

        /// <summary>
        /// 删除软床报价
        /// </summary>
        /// <param name="billid"></param>
        /// <exception cref="LJCommonException"></exception>
        public void DeleteSoftBed(int billid)
		{
			var softbed = GetSoftBed(billid, "softbed_code,flag");

			if (softbed == null) throw new LJCommonException($"单据不存在,请检查! billid:{billid}");

			if (softbed.flag == 1) throw new LJCommonException($"单据:{softbed.softbed_code}已审核,无法删除!");

			DbSqlHelper.Delete(cmd, softbed);
		}

		/// <summary>
		/// 审核软床报价
		/// </summary>
		/// <param name="billid"></param>
		/// <exception cref="LJCommonException"></exception>
		public void AuditSoftBed(int billid)
        {
            var softbed = GetSoftBed(billid, "softbed_code,flag");

            if (softbed == null) throw new LJCommonException($"单据不存在,请检查! billid:{billid}");

            if (softbed.flag == 1) throw new LJCommonException($"单据:{softbed.softbed_code}未撤审,无法审核!");

			softbed.flag = 1;
			softbed.audit_date = context.opdate;
			softbed.audit_emp = context.tokendata.username;
            DbSqlHelper.Update(cmd, "u_softbed",null,softbed, "softbed_id", "flag,audit_date,audit_emp");
        }

		/// <summary>
		/// 撤审软床报价
		/// </summary>
		/// <param name="billid"></param>
		/// <exception cref="LJCommonException"></exception>
        public void CAuditSoftBed(int billid)
        {
            var softbed = GetSoftBed(billid, "softbed_code,flag");

            if (softbed == null) throw new LJCommonException($"单据不存在,请检查! billid:{billid}");

            if (softbed.flag == 0) throw new LJCommonException($"单据:{softbed.softbed_code}未审核,无法撤审!");

			softbed.flag = 0;
            softbed.audit_date = null;
            softbed.audit_emp = string.Empty;
            DbSqlHelper.Update(cmd, "u_softbed", null, softbed, "softbed_id", "flag,audit_date,audit_emp");
        }

		public void CalCulateFormula(u_softbed softbed)
		{
			InitSoftBed(softbed);

			InitReplaceMents(softbed);

            foreach (var mx in softbed.mxList)
			{
				InitMxReplaceMents(softbed, mx);
			}
        }

		private void InitSoftBed(u_softbed softbed)
		{
            var dept = Cache.GetData<int, u_dept, DeptMapping>(softbed.deptid);

            softbed.moneyrate = softbed.moneyrate <= 0 ? 1 : softbed.moneyrate;
            softbed.commission = softbed.commission <= 0 ? 1 : softbed.commission;
            softbed.taxrate = softbed.taxrate <= 0 ? 1 : softbed.taxrate;
            softbed.other_rate = softbed.other_rate <= 0 ? 1 : softbed.other_rate;
            softbed.dept_profitrate = softbed.dept_profitrate <= 0 ? 1 : softbed.dept_profitrate;

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

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

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

            // 检查部门利润率是否为0
            if (softbed.dept_profitrate == 0)
            {
                throw new LJCommonException("部门利润率不能为0!");
            }

			if(softbed.is_template == 1)
			{
				// 使用模板报价
				softbed.template_id = 0;
				softbed.template_code = string.Empty;
				softbed.template_name = string.Empty;
			}
        }

		private void InitReplaceMents(u_softbed softbed)
		{
            //AddKeyValue("不含税出厂价", "【车间成本】*(【工厂利润率】+【外销加点】)",0);
            //AddKeyValue("部门不含税价", "【不含税出厂价】/【部门利润率】/( 1 - (【佣金点数】- 1))*【额外点数】", 0);
            //AddKeyValue("税金", "【部门不含税价】* (【税率】-1)", 0);
            //AddKeyValue("部门含税价", "【部门不含税价】*【税率】", 0);
            //AddKeyValue("外币价", "【部门含税价】/【汇率】", 0);

            //// 初始变量
            //AddKeyValue("部门利润率", string.Empty,softbed.dept_profitrate);
            //AddKeyValue("佣金点数", string.Empty, softbed.commission);
            //AddKeyValue("额外点数", string.Empty, softbed.other_rate);
            //AddKeyValue("额外费用", string.Empty, softbed.extras_cost);
            ////AddKeyValue("FOB", softbed.fob);
            //AddKeyValue("汇率", string.Empty, softbed.moneyrate);
            //AddKeyValue("税率", string.Empty, softbed.taxrate);
        }

        private void InitMxReplaceMents(u_softbed softbed,u_softbed_mx mx)
        {
            //AddKeyValue("下料长", string.Empty,mx.cutting_length);
            //AddKeyValue("下料宽", string.Empty, mx.cutting_width);
            //AddKeyValue("下料数量", string.Empty, mx.cutting_qty);
            //AddKeyValue("用料量", string.Empty, mx.useqty);
            //AddKeyValue("损耗率", string.Empty, mx.loss_rate);
            //AddKeyValue("材料单价", string.Empty, mx.price);
            //AddKeyValue("成本单价", string.Empty, mx.cost_price);
        }

        #region 通用公式
        private CalculateFormula formula = new CalculateFormula();
        #endregion
    }
}