JLH/JLHHJSvr/Helper/BasicInfoHelper.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.Generic;
using System.Linq;
using System.Text;
using static JLHHJSvr.Helper.CacheHelper;

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 SaveConfigureCode(u_configure_code configure)
        {
            if (configure.pzid <= 0)
            {
                // 新增
                configure.pzid = BllHelper.GetID(cmd, "u_configure_code"); 
                var fields = @"typeid,pzid,pzcode,inputtype,name,configtype,ifcross,ifcheck,ifuse,rulestr,ifnum,decnum,maxnum,minnum,pricestr,priceratestr,ifpack,zj_special";
                DbSqlHelper.Insert(cmd, "u_configure_code", null, configure, fields);
            }
            else
            {
                //修改
                var fields = @"pzcode,inputtype,name,configtype,ifcross,ifcheck,ifuse,rulestr,ifnum,decnum,maxnum,minnum,pricestr,priceratestr,ifpack,zj_special";
                DbSqlHelper.Update(cmd, "u_configure_code", null, configure, "typeid,pzid", 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="configure"></param>
        public void SaveConfigureCodeMxBomList(List<u_configure_codemxbom> bomList)
        {
            var sb = new StringBuilder();
            var saveList = new List<u_configure_codemxbom>();
            foreach (var bom in bomList)
            {
                AutoInit.AutoInitS(cmd, bom);

                if (bom.pzid <= 0)
                {
                    sb.AppendLine($"物料:{bom.mtrlname}配置项ID错误!\r\n");
                }

                if (bom.printid <= 0)
                {
                    sb.AppendLine($"物料:{bom.mtrlname}配置值ID错误!\r\n");
                }

                if (sb.Length > 0)
                {
                    continue;
                }
                saveList.Add(bom);
            }

            if (sb.Length > 0) throw new LJCommonException(sb.ToString());

            var fields = @"mtrlid,sonscale,sonscale_formula,mng_cost_rate,profit_rate,realqty,sonloss,sonloss_formula,sondecloss,sondecloss_formula,default_length,default_width,default_qty";
            foreach (var item in saveList)
            {
                if (item.pid <= 0)
                {
                    // 新增
                    fields += ",pzid,printid,pid";
                    int _pid = 0;
                    cmd.CommandText = @"SELECT ISNULL(MAX(pid),0) AS pid FROM u_configure_codemxbom WHERE pzid = @pzid AND printid = @printid";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("@pzid", item.pzid);
                    cmd.Parameters.AddWithValue("@printid", item.printid);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            _pid = Convert.ToInt32(reader["pid"]);
                        }
                    }
                    item.pid = _pid + 1;
                    DbSqlHelper.Insert(cmd, "u_configure_codemxbom", null, item, fields);
                }
                else
                {
                    // 修改
                    DbSqlHelper.Update(cmd, "u_configure_codemxbom", null, item, "pzid,printid,pid", fields);
                }
            }
        }
        /// <summary>
        /// 核价软工公式定义-删除
        /// </summary>
        /// <param name="formula"></param>
        /// <exception cref="LJCommonException"></exception>
        public void DeleteSoftBedFormula(u_softbed_formula formula)
        {
            if (formula == null || formula.formulaid <= 0)
            {
                throw new LJCommonException("未提交软床公式定义信息");
            }
            DbSqlHelper.Delete(cmd, formula);
        }
        /// <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;
        }

        public void SaveSysPostMessage(u_sys_post message)
        {
            if (message == null)
            {
                throw new LJCommonException("未提交公告栏信息!");
            }

            if (string.IsNullOrEmpty(message.dscrp))
            {
                throw new LJCommonException($"公告内容不能为空,请检查!");
            }

            if(message.type == 1 && message.empid == 0)
            {
                throw new LJCommonException("不允许向超级管理员发布提醒!");
            }

            AutoInit.AutoInitS(message);
            //
            message.sdate = message.sdate.Value.Date;
            message.edate = message.edate.Value.Date.AddDays(1).AddSeconds(-1);
            // 新建
            message.postid = BllHelper.GetID(cmd, "u_sys_post");
            message.opdate = context.opdate;
            message.opemp = context.tokendata.username;
            //if(message.empidList != null && message.empidList.Count > 0) message.empids = string.Join(",",message.empidList);

            DbSqlHelper.Insert(cmd, "u_sys_post", null, message, "postid,scid,deptid,empid,sdate,edate,dscrp,opemp,opdate,level,type");
        }
        /// <summary>
        /// 保存核价物料资料
        /// </summary>
        /// <param name="mtrldef"></param>
        public void SaveMtrlDef(u_mtrl_price mtrldef)
        {
            if (mtrldef.mtrlid <= 0)
            {
                mtrldef.mtrlid = BllHelper.GetID(cmd, "u_mtrl_price");

                mtrldef.createtime = context.opdate;
                mtrldef.createby = context.tokendata.username;

                var fields = @"mtrlid,mtrltype,name,priceunit,shrinkage,gram_weight,cloth_width,if_inputqty,if_areaprice,createtime,createby,thickness,dscrp,erp_mtrlid,fjcnt,isuse,handtype,lastdate,erp_mtrlengname,if_subspecs,extra_cost,is_singleqty";
                DbSqlHelper.Insert(cmd, "u_mtrl_price", null, mtrldef, fields);

                var fields2 = @"mtrlid,pricelistid,price,pricetype,price_formula,qty_formula";

                if (mtrldef.mxlist != null && mtrldef.mxlist.Any())
                {
                    foreach (var mx in mtrldef.mxlist)
                    {
                        AutoInit.AutoInitS(cmd, mx);
                        mx.mtrlid = mtrldef.mtrlid;

                        DbSqlHelper.Insert(cmd, "u_mtrl_price_pricelist", null, mx, fields2);
                    }
                }
            }
            else
            {
                //修改
                var fields = @"mtrltype,name,priceunit,shrinkage,gram_weight,cloth_width,if_inputqty,if_areaprice,thickness,dscrp,erp_mtrlid,fjcnt,isuse,handtype,lastdate,erp_mtrlengname,if_subspecs,extra_cost,is_singleqty";
                DbSqlHelper.Update(cmd, "u_mtrl_price", null, mtrldef, "mtrlid", fields);

                var fields2 = @"price,pricetype,price_formula,qty_formula";

                if (mtrldef.mxlist != null && mtrldef.mxlist.Any())
                {
                    foreach (var mx in mtrldef.mxlist)
                    {
                        AutoInit.AutoInitS(cmd, mx);
                        DbSqlHelper.Update(cmd, "u_mtrl_price_pricelist", null, mx, "mtrlid,pricelistid", fields2);
                    }
                }
            }

            Cache.RemoveData<MtrlMapping, u_mtrl_price>(mtrldef.mtrlid);
        }
        /// <summary>
        /// 保存部门资料
        /// </summary>
        /// <param name="dept"></param>
        public void SaveDept(u_dept dept)
        {
            if (dept.deptid <= 0)
            {
                cmd.CommandText = @"SELECT COUNT(deptname) AS cnt FROM u_dept WHERE deptname = @deptname";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@deptname", dept.deptname);
                var cnt = Convert.ToInt32(cmd.ExecuteScalar());
                if (cnt > 0)
                {
                    throw new LJCommonException("存在重复部门名称，请检查!");
                }

                //新建
                dept.createtime = context.opdate;
                dept.deptid = BllHelper.GetID(cmd, "u_dept");

                var fields = "deptid,deptname,profitrate,pricelistid,springtypeid,createtime,moneyrate,discount,flag,if_rate_auto,manage_amt,mtrltype,managerate,com_profitrate,dannum1_rate,dannum2_rate,dannum3_rate,dannum4_rate,taxes_rate";
                DbSqlHelper.Insert(cmd, "u_dept", null, dept, fields);
            }
            else
            {
                //修改
                dept.moddate = context.opdate;
                dept.modemp = context.tokendata.username;

                var fields = "deptname,profitrate,pricelistid,springtypeid,moneyrate,discount,flag,if_rate_auto,manage_amt,mtrltype,moddate,modemp,managerate,com_profitrate,dannum1_rate,dannum2_rate,dannum3_rate,dannum4_rate,taxes_rate";
                DbSqlHelper.Update(cmd, "u_dept", null, dept, "deptid", fields);
            }

            Cache.RemoveData<DeptMapping, u_dept>(dept.deptid);
        }

        public void DeleteSysPostMessage(int postid)
        {
            if (postid == 0)
            {
                throw new LJCommonException("删除公告id有误,请检查!");
            }

            var message = new u_sys_post() { postid = postid };
            DbSqlHelper.Delete(cmd, message);
        }
    }
}