JLH/JLHHJSvr/Helper/ERPHelper.cs

using JLHHJSvr.BLL;
using JLHHJSvr.Com.Model;
using JLHHJSvr.LJException;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using LJLib.DAL.SQL;
using DirectService.Tools;
using System.Data.SqlClient;

namespace JLHHJSvr.Helper
{
    internal class ERPHelper : HelperBase
    {
        private JObject BuildLoginRequest()
        {
            return new JObject
            {
                { "token", GlobalVar.ERP_TOKEN },
                { "account", GlobalVar.ERP_ACCOUNT_NAME },
                { "userid", GlobalVar.ERP_ACCOUNT_USERNAME },
                { "password", GlobalVar.ERP_ACCOUNT_PASSWORD },
                { "clientType", 30 }
            };
        }
        private static readonly Dictionary<string, string> _apiResultMap = new Dictionary<string, string>()
        {
            {"GetL1Mtrldef", "mtrldefList"},
            {"GetSCWorkgroupList", "scworkgroupList"},
            {"GetL1Mtrltype", "mtrltypeList"},
            {"GetL1ConfigureCode", "resultList"},
            {"CommonDynamicSelect", "datatable"},
        };
        protected void Login()
        {
            var request = BuildLoginRequest();

            try
            {
                var result = DoExecute("Login", request);
                var token = result.GetValue("token").ToObject<string>();

                if (!string.IsNullOrEmpty(token))
                {
                    GlobalVar.ERP_TOKEN = token;
                }
            }
            catch (Exception ex)
            {
                throw new LJCommonException($"ERP登录失败: {ex.Message}");
            }
        }
        public void CheckLogin()
        {
            if(string.IsNullOrEmpty(GlobalVar.ERP_TOKEN))
            {
                Login();
            }
        }
        public List<T> GetERPList<T>(string apiMethod, JObject parameters = null, int tryCnt = 0)
        {
            CheckLogin();

            try
            {
                var request = BuildRequest(parameters);
                var result = DoExecute(apiMethod, request);

                if (!_apiResultMap.TryGetValue(apiMethod, out var listKey)) throw new ArgumentException($"Unsupported API Method: {apiMethod}");

                return result[listKey]?.ToObject<List<T>>() ?? new List<T>();
            }
            catch (Exception ex) when (IsTokenExpired(ex))
            {
                if (tryCnt >= 3) throw new LJCommonException("超过最大重连次数，请检查连接！");

                Login();
                return GetERPList<T>(apiMethod, parameters, tryCnt + 1);
            }
            catch
            {
                throw; 
            }
        }
        public List<T> GetERPList<T>(string apiMethod, ref int total, JObject parameters = null, int tryCnt = 0)
        {
            CheckLogin();

            try
            {
                var request = BuildRequest(parameters);
                var result = DoExecute(apiMethod, request);

                if (!_apiResultMap.TryGetValue(apiMethod, out var listKey)) throw new ArgumentException($"Unsupported API Method: {apiMethod}");
                total = ((int)result["totalcnt"]);

                return result[listKey]?.ToObject<List<T>>() ?? new List<T>();
            }
            catch (Exception ex) when (IsTokenExpired(ex))
            {
                if (tryCnt >= 3) throw new LJCommonException("超过最大重连次数，请检查连接！");

                Login();
                return GetERPList<T>(apiMethod, ref total, parameters, tryCnt + 1);
            }
            catch
            {
                throw;
            }
        }
        public List<L1Mtrldef> GetERPMtrldef(SqlCommand cmd, ref int total, string keyword, L1Mtrldef mtrldef, int pagesize = 0, int pageindex = 0, int simpleField = 1, List<int> mtrlids = null)
        {
            var mtrldefList = new List<L1Mtrldef>();
            var selectFields =
                "row_number() over (order by mtrlcode) as rowNum,u_mtrldef.mtrlid,mtrlcode,mtrlname,mtrlorigin,mtrltype,mtrlengname," +
                "unit,mtrlmode,mtrlsectype,zxmtrlmode,u_mtrldef.fjcnt,status_config,woodcode_config,pcode_config,statustype,woodcodetype,pcodetype,u_mtrldef.scid";
            var outputFields =
                "rowNum,mtrlid,mtrlcode,mtrlname,mtrlorigin,mtrltype,unit,mtrlmode,mtrlsectype,zxmtrlmode,mtrlengname," +
                "fjcnt,status_config,woodcode_config,pcode_config,statustype,woodcodetype,pcodetype,scid";

            if (simpleField != 1)
            {
                selectFields +=
                    ",handtype,barcode,u_mtrldef.dscrp,issuliao,isuse,iflimitprice,net_weight,gross_weight,cubage,prdpackcode,opdate,opemp";
                outputFields +=
                    ",handtype,barcode,dscrp,issuliao,isuse,iflimitprice,net_weight,gross_weight,cubage,prdpackcode,opdate,opemp";
            }
            var selectStr = string.Format(@"
                    SELECT {0}
                    FROM   u_mtrldef
                    ",
                selectFields);

            var whereList = new List<string>();
            var parms = new Dictionary<string, object>();

            ////查看有权限类别的物料
            //var mtrltypeids = UserHelper.ParsePowerStr(cmd, tokenData.empid, "mtrltypestr");

            //if (!string.IsNullOrEmpty(mtrltypeids))
            //{
            //    whereList.Add($"u_mtrldef.mtrltypeid IN {mtrltypeids}");
            //}

            if (mtrldef.mtrlid > 0)
            {
                whereList.Add("u_mtrldef.mtrlid = @mtrlid");
                parms.Add("@mtrlid", mtrldef.mtrlid);
            }
            else if (mtrlids != null && mtrlids.Count > 0)
            {
                whereList.Add($"u_mtrldef.mtrlid IN {ListEx.getString(mtrlids)}");
            }
            else
            {
                if (mtrldef.mtrlprp != null && mtrldef.mtrlprp != -1)
                {
                    whereList.Add("u_mtrldef.mtrlprp = @mtrlprp");
                    parms.Add("@mtrlprp", mtrldef.mtrlprp);
                }
                if (mtrldef.mtrltypeid != null && mtrldef.mtrltypeid != -1)
                {
                    whereList.Add("u_mtrldef.mtrltypeid = @mtrltypeid");
                    parms.Add("@mtrltypeid", mtrldef.mtrltypeid);
                }
                if (mtrldef.storageid != null && mtrldef.storageid != -1)
                {
                    whereList.Add(
                        "EXISTS (SELECT mtrlwareid FROM u_mtrlware WHERE mtrlid = u_mtrldef.mtrlid AND storageid = @storageid)");
                    parms.Add("@storageid", mtrldef.storageid);
                }
                if (mtrldef.mtrlorigin != null && mtrldef.mtrlorigin != -1)
                {
                    whereList.Add("u_mtrldef.mtrlorigin = @mtrlorigin");
                    parms.Add("@mtrlorigin", mtrldef.mtrlorigin);
                }
                if (mtrldef.isuse != null && mtrldef.isuse != -1)
                {
                    whereList.Add("u_mtrldef.isuse = @isuse");
                    parms.Add("@isuse", mtrldef.isuse);
                }
                if (!string.IsNullOrEmpty(keyword))
                {
                    whereList.Add(@"(u_mtrldef.mtrlcode LIKE @keyword) 
                                            OR (u_mtrldef.mtrlname LIKE @keyword) 
                                            OR (u_mtrldef.mtrlmode LIKE @keyword) 
                                            OR (u_mtrldef.mtrlsectype LIKE @keyword) 
                                            OR (u_mtrldef.zxmtrlmode LIKE @keyword)
                                            OR (u_mtrldef.usermtrlmode LIKE @keyword)
                                            OR (u_mtrldef.plancode LIKE @keyword)");
                    parms.Add("@keyword", "%" + keyword + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.mtrlcode))
                {
                    whereList.Add("u_mtrldef.mtrlcode LIKE @mtrlcode");
                    parms.Add("@mtrlcode", "%" + mtrldef.mtrlcode + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.mtrlname))
                {
                    whereList.Add("u_mtrldef.mtrlname LIKE @mtrlname");
                    parms.Add("@mtrlname", "%" + mtrldef.mtrlname + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.mtrlmode))
                {
                    whereList.Add("u_mtrldef.mtrlmode LIKE @mtrlmode");
                    parms.Add("@mtrlmode", "%" + mtrldef.mtrlmode + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.mtrlsectype))
                {
                    whereList.Add("u_mtrldef.mtrlsectype LIKE @mtrlsectype");
                    parms.Add("@mtrlsectype", "%" + mtrldef.mtrlsectype + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.zxmtrlmode))
                {
                    whereList.Add("u_mtrldef.zxmtrlmode LIKE @zxmtrlmode");
                    parms.Add("@zxmtrlmode", "%" + mtrldef.zxmtrlmode + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.usermtrlmode))
                {
                    whereList.Add("u_mtrldef.usermtrlmode LIKE @usermtrlmode");
                    parms.Add("@usermtrlmode", "%" + mtrldef.usermtrlmode + "%");
                }

                if (!string.IsNullOrEmpty(mtrldef.plancode))
                {
                    whereList.Add("u_mtrldef.plancode LIKE @plancode");
                    parms.Add("@plancode", "%" + mtrldef.plancode + "%");
                }
                if (!string.IsNullOrEmpty(mtrldef.mtrlengname))
                {
                    whereList.Add("u_mtrldef.mtrlengname LIKE @mtrlengname");
                    parms.Add("@mtrlengname", "%" + mtrldef.mtrlengname + "%");
                }
            }
            if (mtrldef.flag != null && mtrldef.flag != -1)
            {
                whereList.Add("u_mtrldef.flag = @flag");
                parms.Add("@flag", mtrldef.flag);
            }
            var whereStr = ListEx.GetWhereStr(whereList);
            var orderStr = "mtrlcode";
            DbSqlHelper.SelectJoin(cmd, selectStr, whereStr, parms, orderStr, outputFields, pageindex,
               pagesize, mtrldefList, ref total);
            return mtrldefList;
        }
        public List<u_sc_workgroup_erp> GetERPScWorkgroup(SqlCommand cmd)
        {
            var selectStr = @" SELECT wrkGrpid,
                                wrkgrpcode,
                                wrkGrpName,
                                ifjj,
                                ifprint,
                                handtype,
                                sonflag,
                                parentid,
                                inuse,
                                storageid,
                                opdate,
                                opemp,
                                moddate,
                                modemp,
                                ifwkpmtrlware,
                                dscrp,
                                empnum,
                                avggz,
                                jdtype,
                                jdnum,
                                tasktype,
                                sys_changetime,
                                userate,
                                deptid,
                                OTHour,
                                shortrate,
                                iflastfinish,
                                barcode_check,
                                ifauto_mode,
                                ifbj,
                                jjamt,
                                finflowop
                            FROM u_sc_workgroup_erp";
            var whereList = new List<string>();
            var parms = new Dictionary<string, object>();

            //if (request.inuse != null && request.inuse.Value != -1)
            //{
            //    whereList.Add("u_sc_workgroup.inuse = @inuse ");
            //    parms.Add("@inuse", request.inuse.Value);
            //}

            var scworkgroupList = new List<u_sc_workgroup_erp>();
            var outputFields =
                "wrkGrpid,wrkgrpcode,wrkGrpName,ifjj,ifprint,handtype,sonflag,parentid,inuse,storageid,opdate,opemp,moddate,modemp,ifwkpmtrlware,dscrp,empnum,avggz,jdtype,jdnum,tasktype,sys_changetime,userate,deptid,OTHour,shortrate,iflastfinish,barcode_check,ifauto_mode,ifbj,jjamt,finflowop";
            DbSqlHelper.SelectJoin(cmd, selectStr, ListEx.GetWhereStr(whereList), parms, "wrkgrpcode", outputFields, 0, 0, scworkgroupList);

            return scworkgroupList;
        }
        public List<u_mtrltype> GetERPMtrltype(SqlCommand cmd)
        {
            var selectStr = @"
                    SELECT
                        mtrltypeid,
                        mtrltype,
                        handtype,
                        sonflag,
                        parentid,
                        mtrlprp,
                        typecode,
                        typecodestr
                    FROM u_mtrltype_erp";
            var outputFields = "mtrltypeid,mtrltype,handtype,sonflag,parentid,mtrlprp,typecode,typecodestr";
            var mtrltypeList = new List<u_mtrltype>();
            DbSqlHelper.SelectJoin(cmd, selectStr, null, null, null, outputFields, 0, 0, mtrltypeList);

            return mtrltypeList;
        }
        // 提取请求构建逻辑
        private JObject BuildRequest(JObject parameters)
        {
            var request = new JObject { ["token"] = GlobalVar.ERP_TOKEN };
            parameters = parameters ?? new JObject();

            foreach (var param in parameters)
            {
                request.Add(param.Key, param.Value);
            }
            return request;
        }
        private bool IsTokenExpired(Exception ex)
        {
            const string expiredFlag = "已与服务器失联";
            return ex.Message.Contains(expiredFlag);
        }
        /// <summary>
        /// 保存物料资料,成功后保存erp_mtrlid
        /// </summary>
        /// <param name="mtrl">核价物料资料</param>
        /// <returns>错误信息</returns>
        public string SaveMtrldef(u_mattress mtrl, List<u_mattress_interface> interfaceList = null, string fields = "mtrlname,mtrlmode,mtrltypeid,mtrltype,unit,mtrlengname,configcode,configname,model,configcodetype")
        {
            var errMsg = string.Empty;

            var configure2 = (JArray)null;
            if (interfaceList != null)
            {
                configure2 = new JArray { };
                foreach (var item in interfaceList)
                {
                    configure2.Add(new JObject()
                    {
                        ["pzid"] = item.erp_pzid,
                        ["pzcode"] = item.pzcode,
                        ["pzname"] = item.bj_pzname,
                        ["pznamemx"] = item.bj_namemx
                    });
                }
            }

            var l1Req = new JObject()
            {
                ["token"] = GlobalVar.ERP_TOKEN,
                ["mtrls"] = new JArray
                {
                    new JObject()
                    {
                        ["mtrlid"] = mtrl.erp_mtrlid,
                        ["mtrlcode"] = mtrl.erp_mtrlcode,
                        ["mtrlname"] = mtrl.erp_mtrlname,
                        ["mtrlmode"] = mtrl.erp_mtrlmode,
                        ["mtrltypeid"] = mtrl.erp_mtrltypeid,
                        ["mtrltype"] = mtrl.erp_mtrltype,
                        ["unit"] = mtrl.erp_mtrlunit,
                        ["mtrlengname"] = mtrl.erp_mtrlengname,
                        ["dijia_cost"] = mtrl.dijia_cost, // 底价1
                        ["dijia_cost2"] = mtrl.dijia_cost, // 底价2
                        ["dijia_cost3"] = mtrl.dijia_cost, // 底价3
                        ["fob"] = mtrl.fob, // fob费用
                        ["taxes"] = mtrl.taxes, // 税金
                        ["extras_cost"] = mtrl.extras_cost, // 额外费用
                        ["planprice"] = mtrl.nottax_factory_cost, // 计划价
                        ["bj_mxdscrp"] = mtrl.bj_mxdscrp,
                        ["configcode"] = mtrl.configcode,
                        ["configname"] = mtrl.configname,
                        ["model"] = mtrl.model,
                        ["configcodetype"] = mtrl.erp_configcodetype,
                        ["mtrl_config2"] = configure2
                    }
                },
                ["updateFields"] = fields
            };

            var l1Rslt = DoExecute("SaveMtrldef", JObject.FromObject(l1Req));
            errMsg = $"{l1Rslt.GetValue("ErrMsg")}";
            if (string.IsNullOrEmpty(errMsg))
            {
                mtrl.erp_mtrlid = (l1Rslt.GetValue("mtrlids") as JArray)[0].Value<int>();
                mtrl.erp_mtrlcode = (l1Rslt.GetValue("mtrlcodes") as JArray)[0].Value<string>();
                Trace.Write($"Update erp_mtrlid={mtrl.erp_mtrlid} WHERE mattressid={mtrl.mattressid} AND erp_mtrlcode={mtrl.erp_mtrlcode}");
                DbSqlHelper.Update(cmd, mtrl, "erp_mtrlid");
            }
            return errMsg;
        }

        /// <summary>
        /// 更新床垫相关选项
        /// </summary>
        /// <param name="mattress"></param>
        public u_mattress GetMattressMsg(u_mattress mattress, List<u_mattress_interface> interfaceList)
        {
            var errMsg = string.Empty;

            var mattresstype = new u_mattress_type();
            if (DbSqlHelper.SelectOne(cmd, "u_mattress_type", "mattresstypeid = @mattresstypeid", new Dictionary<string, object>() { { "mattresstypeid", mattress.mattresstypeid } }, mattresstype, "typename") == 1)
            {
                mattress.model = mattresstype.typename;
            }

            var lde_dijia_cost = mattress.nottax_factory_cost / (mattress.dept_profitrate + mattress.dept_profitrate_rangli / 100);
            var lde_commission = lde_dijia_cost / (1 - (mattress.commission - 1)) - lde_dijia_cost;
            var ls_bj_mxdscrp = "底价：" + lde_dijia_cost.Value.ToString("###0.00") + ",  佣金：" + lde_commission.Value.ToString("###0.00") + ",  税金：" + mattress.taxes.Value.ToString("###0.00") + ",  fob：" + mattress.fob.Value.ToString("###0.00") + ",  最后更新日期：" + DateTime.Today.ToString("yyyy-MM-dd") + "," + mattress.nottax_factory_cost.Value.ToString("###0.00");
            mattress.bj_mxdscrp = ls_bj_mxdscrp;
            mattress.dijia_cost = lde_dijia_cost;

            string ls_configcode = "", ls_configname = "";
            foreach (var item in interfaceList)
            {
                string ls_temp_pzcode = item.pzcode;
                string ls_temp_str, ls_temp_pznamemx, ls_temp_pzname = item.pzname;

                ls_temp_str = item.actual_size + item.sb_craft + item.actual_size_sb;
                ls_temp_pznamemx = string.IsNullOrEmpty(ls_temp_str) ? item.bj_namemx : item.bj_namemx + "@@" + ls_temp_str + "@@" + item.ss_rate.Value.ToString("###0.00") + "@@" + item.ls_rate.Value.ToString("###0.00");

                item.bj_namemx = ls_temp_pznamemx;

                ls_configcode = ls_configcode + ls_temp_pznamemx + "|";
                ls_configname = ls_configname + ls_temp_pzname + ":" + ls_temp_pznamemx + "|";
            }

            mattress.configcode = ls_configcode;
            mattress.configname = ls_configname;

            return mattress;
        }

        /// <summary>
        /// 更新L1计划价 wf_updateL1_planprice
        /// </summary>
        /// <param name="mattress"></param>
        public string UpdateL1Planprice(int erp_mtrlid, decimal value)
        {
            var errMsg = string.Empty;

            var l1Req = new JObject()
            {
                ["token"] = GlobalVar.ERP_TOKEN,
                ["mtrlid"] = erp_mtrlid,
                ["value"] = value,
            };

            var l1Rslt = DoExecute("UpdateMtrldefPlanPrice", JObject.FromObject(l1Req));
            errMsg = $"{l1Rslt.GetValue("ErrMsg")}";
            return errMsg;
        }
    }
}