JLH/JLHHJSvr/Helper/BasicInfoHelper.cs

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

namespace JLHHJSvr.Helper
{
    internal class BasicInfoHelper: HelperBase
    {
        /// <summary>
        /// 核价选配类型-保存
        /// </summary>
        /// <param name="configure"></param>
        public void SaveConfigureType(u_configure_type configure)
        {
            if (configure.contfigtypeid <= 0)
            {
                configure.contfigtypeid = BllHelper.GetID(cmd, "u_configure_type");

                var fields = @"contfigtypeid,contfigtype,contfigtypename,usechflag,flag";
                DbSqlHelper.Insert(cmd, "u_configure_type", null, configure, fields);
            }
            else
            {
                configure.moddate = context.opdate;
                configure.modemp = context.tokendata.username;

                //修改
                var fields = @"contfigtype,contfigtypename,usechflag,modemp,moddate";
                DbSqlHelper.Update(cmd, "u_configure_type", null, configure, "contfigtypeid", fields);
            }
        }
        /// <summary>
        /// 核价选配项值-保存
        /// </summary>
        /// <param name="configure"></param>
        public void SaveConfigureCodeMx(u_configure_codemx codeMx)
        {
            if (codeMx.printid <= 0)
            {
                int _printid = 0;
                cmd.CommandText = @"SELECT ISNULL(MAX(printid),0) AS printid FROM u_configure_codemx WHERE pzid = @pzid";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@pzid", codeMx.pzid);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        _printid = Convert.ToInt32(reader["printid"]);
                    }
                }
                codeMx.printid = _printid + 1;

                var fields = @"pzid,printid,pzcodemx,namemx,gradestr,mtrlcode,price,ifdft,MCostRate,ProfitRate,dscrp,ifuse,ifnoch,pricerate,packqty,packvol,price_pz,grade";
                DbSqlHelper.Insert(cmd, "u_configure_codemx", null, codeMx, fields);
            }
            else
            {
                //修改
                var fields = @"pzcodemx,namemx,gradestr,mtrlcode,price,ifdft,MCostRate,ProfitRate,dscrp,ifuse,ifnoch,pricerate,packqty,packvol,price_pz,grade";
                DbSqlHelper.Update(cmd, "u_configure_codemx", null, codeMx, "pzid,printid", fields);
            }
        }
        /// <summary>
        /// 核价软床公式定义-保存
        /// </summary>
        /// <param name="formula"></param>
        public void SaveSoftBedFormula(u_softbed_formula formula)
        {
            if (formula == null)
            {
                throw new LJCommonException("未提交软床公式定义信息");
            }

            if (string.IsNullOrEmpty(formula.formulaname))
            {
                throw new LJCommonException($"公式名称不能为空,请检查!");
            }

            var fields = "formulaname,price_formula,use_formula";

            if (formula.formulaid <= 0)
            {
                // 新建
                fields += ",formulaid,create_date,create_emp";
                formula.formulaid = BllHelper.GetID(cmd, "u_softbed_formula");
                formula.create_date = context.opdate;
                formula.create_emp = context.tokendata.username;

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

                DbSqlHelper.Update(cmd, "u_softbed_formula", null, formula, "formulaid", fields);
            }
        }
        /// <summary>
        /// 保存ERP物料资料定义
        /// </summary>
        /// <param name="mtrl"></param>
        /// <exception cref="LJCommonException"></exception>
        public void SaveErpMtrlPrice(u_erpmtrl_price mtrl)
        {
            var fields = "";

            if(mtrl.mtrlid <= 0)
            {
                throw new LJCommonException("物料ID有误,请检查!");
            }

            mtrl.update_date = context.opdate;
            mtrl.update_emp = context.tokendata.username;
            if(DbSqlHelper.Update(cmd, "u_erpmtrl_price", null, mtrl, "mtrlid", "price,update_date,update_emp") == 0)
            {
                mtrl.create_date = context.opdate;
                mtrl.create_emp = context.tokendata.username;

                DbSqlHelper.Insert(cmd, "u_erpmtrl_price", null, mtrl, "mtrlid,price,create_date,create_emp");
            }
        }
        /// <summary>
        /// 获取ERP物料资料定义
        /// </summary>
        /// <param name="whereList"></param>
        /// <param name="param"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalcnt"></param>
        /// <returns></returns>
        public List<L1Mtrldef> GetErpMtrlPriceList(List<string> whereList, Dictionary<string, object> param,int pageindex,int pagesize,out int totalcnt)
        {
            var mtrldefList = new List<L1Mtrldef>();

            var selectFields = @"row_number() over (order by u_mtrldef.mtrlcode) as rowNum,u_mtrldef.scid,u_mtrldef.mtrlid,u_mtrldef.mtrlcode,u_mtrldef.mtrlname,u_mtrldef.mtrlorigin,u_mtrldef.mtrltype,u_mtrldef.mtrlengname,
                                u_mtrldef.unit,u_mtrldef.mtrlmode,u_mtrldef.mtrlsectype,u_mtrldef.zxmtrlmode,u_mtrldef.status_config,u_mtrldef.woodcode_config,
                                u_mtrldef.pcode_config,u_mtrldef.statustype,u_mtrldef.woodcodetype,u_mtrldef.pcodetype,ISNULL(u_erpmtrl_price.price,0) AS price,
                                u_erpmtrl_price.create_date,u_erpmtrl_price.create_emp,u_erpmtrl_price.update_date,u_erpmtrl_price.update_emp";
            var outputFileds = @"rowNum,scid,mtrlid,mtrlcode,mtrlname,mtrlorigin,mtrltype,mtrlengname,unit,mtrlmode,mtrlsectype,zxmtrlmode,status_config,woodcode_config,
                                pcode_config,statustype,woodcodetype,pcodetype,price,create_date,create_emp,update_date,update_emp";
            var selectStr = $@"SELECT {selectFields}
                            FROM u_mtrldef
                            LEFT JOIN u_erpmtrl_price ON u_mtrldef.mtrlid = u_erpmtrl_price.mtrlid";

            var whereStr = string.Empty;
            if (whereList != null && whereList.Count > 0)
            {
                whereStr = ListEx.GetWhereStr(whereList);
            }

            var orderStr = "mtrlcode";
            totalcnt = 0;
            DbSqlHelper.SelectJoin(cmd, selectStr, whereStr, param, orderStr, outputFileds, pageindex,
               pagesize, mtrldefList, ref totalcnt);

            return mtrldefList;
        }
    }
}