Exemplo n.º 1
0
        /// <summary>
        /// 获取SQL
        /// </summary>
        private static string  GetSql(JToken JToken, ParamaterFiled_DB db, List <ParamaterField_Model> pmListbyType,
                                      string[] keys, string[] MatrtialKeys, string TableName, bool isHead, ref int headID,
                                      ref string WmsHeadVourcherNo, ref List <int> BeforeIDs, ref List <int> AfterIDs)
        {
            string sql         = String.Empty;
            string whereString = GetwhereString(isHead, keys, pmListbyType, JToken);


            int TableID = db.checkRecode(whereString, pmListbyType, JToken, TableName);

            if (TableID == 0) //新增
            {
                sql = GetInsertSql(JToken, db, pmListbyType, MatrtialKeys, TableName, isHead, ref headID, ref WmsHeadVourcherNo, whereString);
            }
            else //修改
            {
                if (isHead)//获取修改前的表体ID集合
                {
                    headID            = TableID;
                    WmsHeadVourcherNo = db.GetWmsWoucherNo(pmListbyType[0].WMSTableNameH, TableID);
                    if (!String.IsNullOrEmpty(pmListbyType[0].WMSTableNameD))
                    {
                        BeforeIDs = db.getDetailIDs(pmListbyType[0].WMSTableNameD, TableID);
                    }
                }
                else //获取修改表体ID
                {
                    AfterIDs.Add(TableID);
                }
                sql = GetUpdateSql(JToken, db, pmListbyType, MatrtialKeys, TableName, isHead, whereString);
            }

            return(sql);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 获取updateSql
        /// </summary>
        /// <returns></returns>
        private static string GetUpdateSql(JToken JToken, ParamaterFiled_DB db, List <ParamaterField_Model> pmListbyType,
                                           string[] MatrtialKeys, string TableName, bool isHead, string whereString)
        {
            string        sql;
            StringBuilder valueList = new StringBuilder();

            foreach (JProperty jp in JToken)
            {
                int index = pmListbyType.FindIndex(p => p.ERPField.ToUpper() == jp.Name.ToUpper() && p.FieldHD.ToUpper() == (isHead ? "H" : "D"));
                if (index != -1)
                {
                    string value = FilteSQLStr(jp.Value.ToString());
                    value = pmListbyType[index].DefaultType == 1 ? "CONVERT(varchar(100),'" + value + "', 20)," : "'" + value + "',";
                    valueList.Append(pmListbyType[index].WMSField + "=" + value);
                }
            }
            var defaultValueList = pmListbyType.FindAll(p => !String.IsNullOrEmpty(p.DefaultValue) && (p.DefaultType == 2 || p.DefaultType == 3 || p.DefaultType == 4 || p.DefaultType == 5) &&
                                                        p.FieldHD.ToUpper() == (isHead ? "H" : "D") &&
                                                        String.IsNullOrEmpty(p.ERPField)).DistinctBy(s => new { s.WMSField, s.DefaultValue });

            foreach (var pmodel in defaultValueList)
            {
                if (pmodel.DefaultType == 2)
                {
                    string materialSubSql = String.Format(SqlModel.GetmaterialIdsql, GetwhereString(isHead, MatrtialKeys, pmListbyType, JToken));
                    materialSubSql = "(" + materialSubSql + ")";
                    valueList.Append(pmodel.WMSField + "=" + materialSubSql + ",");
                }
                //出库单自动分配客户对应发货仓库 只有发货单需要处理
                if (pmodel.DefaultType == 3 || pmodel.DefaultType == 4)
                {
                    int index = pmListbyType.FindIndex(p => p.WMSField.ToUpper() == customKey && p.FieldHD.ToUpper() == "D");
                    if (index != -1)
                    {
                        string customerno        = ((JObject)JToken).GetValue(pmListbyType[index].ERPField.ToString()).ToString();
                        string warehouseIDSubSql = String.Format(pmodel.DefaultType == 3?SqlModel.GetWhareHouseID: SqlModel.GetWhareHouseNo, customerno);
                        valueList.Append(pmodel.WMSField + "=(" + warehouseIDSubSql + "),");
                    }
                }
                //调拨单获取warehouseid
                if (pmodel.DefaultType == 5)
                {
                    int index = pmListbyType.FindIndex(p => p.WMSField.ToUpper() == warehouseNoKey && p.FieldHD.ToUpper() == "D");
                    if (index != -1)
                    {
                        string warehouseNo       = ((JObject)JToken).GetValue(pmListbyType[index].ERPField.ToString()).ToString();
                        string warehouseIDSubSql = String.Format(SqlModel.GetWhareHouseIDByNo, warehouseNo);
                        valueList.Append(pmodel.WMSField + "=(" + warehouseIDSubSql + "),");
                    }
                }
            }
            // "update {0} set {1} where 1=1 and {2}";
            sql = String.Format(SqlModel.UpdateSql, TableName, (isHead ? "LASTSYNCTIME=GETDATE()," : "") + valueList.Remove(valueList.Length - 1, 1), whereString);
            return(sql);
        }
Exemplo n.º 3
0
        /// <summary>
        /// 获取insertsql
        /// </summary>
        /// <returns></returns>
        private static string GetInsertSql(JToken JToken, ParamaterFiled_DB db, List <ParamaterField_Model> pmListbyType,
                                           string[] MatrtialKeys, string TableName, bool isHead, ref int headID, ref string WmsHeadVourcherNo, string whereString)
        {
            string        sql;
            string        WmsVourcherNo = isHead ? db.GetWmsWoucherNo(pmListbyType[0].VOUCHERNO, pmListbyType[0].WMSTableNameH, WmsHeadVourcherNo) : "";
            int           NewTableID    = isHead ? db.GetHeadID(pmListbyType[0].HEADID) : 0;
            StringBuilder contextList   = new StringBuilder();
            StringBuilder valueList     = new StringBuilder();

            foreach (JProperty jp in JToken)
            {
                int index = pmListbyType.FindIndex(p => p.ERPField.ToUpper() == jp.Name.ToUpper() && p.FieldHD.ToUpper() == (isHead ? "H" : "D"));
                if (index != -1)
                {
                    string value = FilteSQLStr(jp.Value.ToString());
                    value = pmListbyType[index].DefaultType == 1 ? "CONVERT(varchar(100),'" + value + "', 20)," : "'" + value + "',";

                    contextList.Append(pmListbyType[index].WMSField + ",");
                    valueList.Append(value.Trim());
                }
            }

            var defaultValueList = pmListbyType.FindAll(p => !String.IsNullOrEmpty(p.DefaultValue) && p.FieldHD.ToUpper() == (isHead ? "H" : "D") &&
                                                        String.IsNullOrEmpty(p.ERPField)).DistinctBy(s => new { s.WMSField, s.DefaultValue });

            foreach (var pmodel in defaultValueList)
            {
                string value = pmodel.DefaultValue;
                if (pmodel.DefaultType == 2)
                {
                    string materialSubSql = String.Format(SqlModel.GetmaterialIdsql, GetwhereString(isHead, MatrtialKeys, pmListbyType, JToken));
                    value = "(" + materialSubSql + ")";
                }
                //出库单自动分配客户对应发货仓库 只有发货单需要处理
                if (pmodel.DefaultType == 3 || pmodel.DefaultType == 4)
                {
                    int index = pmListbyType.FindIndex(p => p.WMSField.ToUpper() == customKey && p.FieldHD.ToUpper() == "D");
                    if (index != -1)
                    {
                        string customerno        = ((JObject)JToken).GetValue(pmListbyType[index].ERPField.ToString()).ToString();
                        string warehouseIDSubSql = String.Format(pmodel.DefaultType == 3 ? SqlModel.GetWhareHouseID : SqlModel.GetWhareHouseNo, customerno);
                        value = "(" + warehouseIDSubSql + ")";
                    }
                }
                //调拨单获取warehouseid
                if (pmodel.DefaultType == 5)
                {
                    int index = pmListbyType.FindIndex(p => p.WMSField.ToUpper() == warehouseNoKey && p.FieldHD.ToUpper() == "D");
                    if (index != -1)
                    {
                        string warehouseNo       = ((JObject)JToken).GetValue(pmListbyType[index].ERPField.ToString()).ToString();
                        string warehouseIDSubSql = String.Format(SqlModel.GetWhareHouseIDByNo, warehouseNo);
                        value = "(" + warehouseIDSubSql + ")";
                    }
                }
                contextList.Append(pmodel.WMSField + ",");
                valueList.Append(value + ",");
            }
            //"IF NOT EXISTS (SELECT id FROM {0} WHERE {1}) INSERT INTO {0} ({2},ID,VOUCHERNO,VOUCHERTYPE) SELECT {3},{4},'{5}','{6}'"
            if (isHead)
            {
                headID            = NewTableID;
                WmsHeadVourcherNo = WmsVourcherNo;
                sql =
                    String.Format(SqlModel.InsertTitleSql, TableName, whereString,
                                  contextList.Remove(contextList.Length - 1, 1), valueList.Remove(valueList.Length - 1, 1),
                                  NewTableID, WmsVourcherNo, pmListbyType[0].ErpVourcherType, CheckVoucherType(pmListbyType[0].VoucherType));
            }
            else
            {
                //IF NOT EXISTS (SELECT id FROM {0} WHERE {1} 1=1) INSERT INTO {0} ({2},HEADERID,VOUCHERNO) SELECT {3},'{4}','{5}'
                sql = String.Format(SqlModel.InsertDetailSql, TableName, whereString,
                                    contextList.Remove(contextList.Length - 1, 1), valueList.Remove(valueList.Length - 1, 1), headID, WmsHeadVourcherNo);
            }
            return(sql);
        }
Exemplo n.º 4
0
        /// <summary>
        /// 按照ERP数据生产对应SQL
        /// </summary>
        /// <param name="pmListbyType">同步字段</param>
        /// <param name="dataJson">ERP数据</param>
        /// <param name="wmsVourcherType">WMS单据类型</param>
        /// <param name="errMsg">错误消息</param>
        /// <param name="syncType">同步类型 10:入库 20:出库  99:基础资料</param>
        /// <returns></returns>
        private static List <string> CreateSqlList(ParamaterFiled_DB db, List <ParamaterField_Model> pmListbyType,
                                                   string dataJson, int wmsVourcherType, ref string errMsg)
        {
            List <string> SQLIST = new List <string>();

            string[] Headkeys        = pmListbyType[0].HEADKEYS.ToString().Split(',');   //表头主键
            string[] Detailkeys      = pmListbyType[0].DETAILKEYS.ToString().Split(','); //表体主键
            string[] MatrtialKeys    = pmListbyType[0].MATERIALNOKEYS.ToString().Split(',');
            string   headTableName   = pmListbyType[0].WMSTableNameH.ToString();         //表头名称
            string   detailTableName = pmListbyType[0].WMSTableNameD.ToString();         //表体名称

            JArray headJarray = JArray.Parse(dataJson);

            for (int i = 0; i < headJarray.Count; i++)
            {
                ////------------测试用
                //if (i == 2)
                //    break;
                ////--------------
                JToken jToken = JObject.Parse(headJarray[i].ToString());

                foreach (JProperty jp in jToken)
                {
                    if (jp.Name.ToUpper().Equals(headContent))
                    {
                        string WmsVourcherNo = String.Empty;
                        JToken headJToken    = JObject.Parse(jp.Value.ToString());
                        //表头SQL
                        int        headID    = 0;
                        List <int> BeforeIDs = new List <int>(); //同步前表体ID集合
                        List <int> AfterIDs  = new List <int>(); //同步后表体ID集合
                        string     headsql   = GetSql(headJToken, db, pmListbyType, Headkeys, MatrtialKeys, headTableName, true, ref headID, ref WmsVourcherNo, ref BeforeIDs, ref AfterIDs);
                        SQLIST.Add(headsql);
                        foreach (JProperty hjp in headJToken)
                        {
                            if (hjp.Name.ToUpper().Equals(detailContent))
                            {
                                if (!String.IsNullOrEmpty(hjp.Value.ToString()))
                                {
                                    //表体SQL
                                    JArray detailJarray = JArray.Parse(hjp.Value.ToString());
                                    for (int j = 0; j < detailJarray.Count; j++)
                                    {
                                        JToken detailJToken = JObject.Parse(detailJarray[j].ToString());
                                        string detailsql    = GetSql(detailJToken, db, pmListbyType, Detailkeys, MatrtialKeys, detailTableName, false, ref headID, ref WmsVourcherNo, ref BeforeIDs, ref AfterIDs);
                                        SQLIST.Add(detailsql);
                                    }
                                }
                            }
                        }
                        //删除sql
                        //List<int> diffID = BeforeIDs.Where(a => !AfterIDs.Exists(t => a == t)).ToList();
                        //if (diffID.Count != 0)
                        //{
                        //    string ids = String.Join(",", diffID.ToArray());
                        //    string sql = String.Format(SqlModel.DeleteSql, detailTableName, ids);
                        //    //零时修改,等老余修复问题之后,需要恢复2018-08-06
                        //    SQLIST.Add(sql);
                        //}
                    }
                }
            }
            return(SQLIST);
        }
Exemplo n.º 5
0
        /// <summary>
        /// 同步ERP单据
        /// </summary>
        /// <param name="StockType">类型 10:入库 20:出库  99:基础资料</param>
        /// <param name="LastSyncTime">最后同步时间</param>
        /// <param name="ErpVoucherNo">ERP单号</param>
        /// <param name="wmsVourcherType">wms单据类型</param>
        /// <param name="ErrMsg">返回错误信息</param>
        /// <param name="syncType">同步数据来源 ERP或者 EXCEL</param>
        /// <returns></returns>
        public static bool SyncJsonFromErp(int StockType, string LastSyncTime, string ErpVoucherNo, int wmsVourcherType, ref string ErrMsg)
        {
            bool result = false;

            try
            {
                //根据stockType查询出入库类型(T_PARAMETER表-出入库类型)
                ParamaterFiled_DB db = new ParamaterFiled_DB();
                int type             = db.GetRealStockType(StockType);
                //根据单据类型和出库入类型获取同步字段
                List <ParamaterField_Model> pmList = db.GetPmList(type, wmsVourcherType, ref ErrMsg); //同步字段
                if (pmList == null || pmList.Count == 0)
                {
                    ErrMsg += "单据类型未配置!";
                    return(false);
                }

                var TypeList = pmList.Where(p => p.ErpVourcherType != null).DistinctBy(s => new { s.ErpVourcherType, s.VoucherType }); //获取同步单据类型
                //按照单据类型循环
                foreach (var Type in TypeList)
                {
                    //获取单据同步数据Json
                    string dataJson   = String.Empty;
                    string ErpvouType = Type.ErpVourcherType.ToString();
                    string WmsvouType = Type.VoucherType.ToString();

                    //   string time = "";
                    //   System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
                    //    stopwatch.Start();

                    string Json =                                                              //"{\"result\":\"1\",\"resultValue\":\"\",\"data\":[{\"Head\":{\"darvdate\":\"2018-01-02\",\"cvenname\":\"供应商名称1\",\"cvencode\":\"供应商编码1\",\"carvcode\":\"\",\"cbustype\":\"业务类型1\",\"ipurarriveid\":\"到货单ID2\",\"itaxrate\":\"121\",\"iexchrate\":\"131\",\"cexch_name\":\"币种\",\"body\":[{\"iarrsid\":\"到货单子表ID\",\"POID\":\"到货单ID2\",\"cinvcode\":\"存货编码1\",\"cinvname\":\"存货名称1\",\"cinvstd\":\"规格型号1\",\"cvencode\":\"供应商编码1\",\"cbatch\":\"\",\"iNQuantity\":\"2500\"},{\"iarrsid\":\"到货单子表ID3\",\"POID\":\"到货单ID2\",\"cinvcode\":\"存货编码3\",\"cinvname\":\"存货名称3\",\"cinvstd\":\"规格型号3\",\"cvencode\":\"供应商编码3\",\"cbatch\":\"\",\"iNQuantity\":\"900\"}]}},{\"Head\":{\"darvdate\":\"2018-01-03\",\"cvenname\":\"供应商名称2\",\"cvencode\":\"供应商编码2\",\"carvcode\":\"\",\"cbustype\":\"业务类型2\",\"ipurarriveid\":\"到货单ID3\",\"itaxrate\":\"132\",\"iexchrate\":\"142\",\"cexch_name\":\"币种\",\"body\":[{\"iarrsid\":\"到货单子表ID\",\"POID\":\"到货单ID3\",\"cinvcode\":\"存货编码2\",\"cinvname\":\"存货名称2\",\"cinvstd\":\"规格型号2\",\"cvencode\":\"供应商编码\",\"cbatch\":\"\",\"iNQuantity\":\"2500\"}]}}]}";
                                  GetErpJson(StockType, LastSyncTime, ErpVoucherNo, Type, db); //获取JSON

                    //    time += "获取json:" + stopwatch.Elapsed.TotalSeconds;
                    //  stopwatch.Reset(); stopwatch.Start();

                    //解析JSON格式
                    result = GetDataJson(Json, ref dataJson, WmsvouType, ref ErrMsg);

                    if (result)
                    {
                        ErrMsg += " \r\nerp类型:" + ErpvouType + "\r\n" + dataJson + "\r\n";
                        if (pmList.Count > 0 && !String.IsNullOrEmpty(dataJson.TrimStart('[').TrimEnd(']')))
                        {
                            //单据类型对应字段
                            List <ParamaterField_Model> pmListbyType = pmList.FindAll(p => p.VoucherType.ToString() == WmsvouType && p.ErpVourcherType == ErpvouType);
                            //生成SQL语句
                            List <string> SQList = CreateSqlList(db, pmListbyType, dataJson, wmsVourcherType, ref ErrMsg);
                            //     time += "生成SQL:" + stopwatch.Elapsed.TotalSeconds;
                            //     stopwatch.Reset(); stopwatch.Start();
                            result = db.SaveSqlList(SQList, ref ErrMsg);
                            //    time += "插入SQL:" + stopwatch.Elapsed.TotalSeconds;
                            //   stopwatch.Reset(); stopwatch.Start();
                            //if (!result)
                            //    break;
                        }
                    }

                    //  time += "解析SQL:" + stopwatch.Elapsed.TotalSeconds;
                    //  stopwatch.Stop();
                }
            }
            catch (Exception ex)
            {
                result  = false;
                ErrMsg += ex.Message + "|";
            }
            return(result);
        }
Exemplo n.º 6
0
        /// <summary>
        /// 获取单据同步数据Json
        /// </summary>
        /// <param name="StockType">类型 10:入库 20:出库  99:基础资料</param>
        /// <param name="LastSyncTime">最后同步时间</param>
        /// <param name="ErpVoucherNo">ERP单号</param>
        /// <param name="wmsVourcherType">wms单据类型</param>
        /// <param name="db"></param>
        /// <returns></returns>
        private static string GetErpJson(int StockType, string LastSyncTime, string ErpVoucherNo, ParamaterField_Model Type, ParamaterFiled_DB db)
        {
            //取最后同步时间或者最大ERP单号单号
            //ERP单据号不为空的情况下,查询该单据的最后同步时间
            string lastSyncErpVoucherNo = String.Empty;

            if (!String.IsNullOrEmpty(ErpVoucherNo))
            {
                LastSyncTime = db.GetLastSyncTime(StockType, ErpVoucherNo);
            }
            else
            {
                if (StockType != 99) //99:基础资料同步
                {
                    //获取最大单号特殊处理
                    lastSyncErpVoucherNo = db.getLastSyncErpVoucherNo(StockType, Type.VoucherType.ToString(), Type.ErpVouType, Type.ErpVourcherType);
                }
            }
            BILBasic.Interface.T_Interface_Func TIF = new BILBasic.Interface.T_Interface_Func();
            string json = "{\"code\":\"" + ErpVoucherNo + "\",\"VoucherType\":\"" + Type.VoucherType.ToString() + "\",\"max_code\":\"" + lastSyncErpVoucherNo + "\",\"sync_time\":\"";

            json += !String.IsNullOrEmpty(LastSyncTime)?DateTime.Parse(LastSyncTime).ToString("yyyy-MM-dd"):"";
            json += "\",\"erp_vourcher_type\":\"" + Type.ErpVourcherType + "\"}";
            return(TIF.GetModelListByInterface(json));
        }
Exemplo n.º 7
0
        internal static bool SyncSAPJsonFromErp(int stockType, string lastSyncTime, string erpVoucherNo, int wmsVourcherType, string InJsonData, ref string errMsg)
        {
            //     InJsonData = "{\"result\":\"1\",\"resultValue\":\"\",\"data\":[{\"head\":{\"cDLCode\":\"MD20181116142149000181\",\"caddcode\":\"01\",\"cDefine14\":\"成品仓\",\"cShipAddress\":\"内蒙古自治区 / 锡林郭勒盟 / 西乌珠穆XXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"cCusCode\":\"10000201\",\"cCusName\":\"嘉善三雷贸易有点公司\",\"ccusperson\":\"19999999999\",\"dDate\":1542349309000,\"cMaker\":\"正泰民用管理员\",\"body\":[{\"cOrderCode\":\"1\",\"cSoCode\":\"262\",\"iQuantity\":\"100\",\"cInvCode\":\"120100020177\"}]}}]}";
            bool result = false;

            try
            {
                int autoSync = String.IsNullOrEmpty(InJsonData) ? 1 : 0;
                //根据单据类型和出库入类型获取同步字段
                List <Sync_Model> syncModelList = SyncManager.GetInstance().GetSyncModelList(stockType, wmsVourcherType, autoSync); //同步字段
                if (syncModelList == null || syncModelList.Count == 0)
                {
                    errMsg += wmsVourcherType + "|单据类型未配置!\r\n";
                    LogNet.SyncInfo(errMsg);
                    return(false);
                }
                var ErpTypeList = syncModelList.Where(p => p.ErpType != null).DistinctBy(s => new { s.ErpType }); //获取同步单据类型

                foreach (var erpType in ErpTypeList)                                                              //ERP单据类型
                {
                    //获取单据同步数据Json
                    string dataJson      = String.Empty;
                    string SAPServerTime = "";
                    if (String.IsNullOrEmpty(erpVoucherNo) && String.IsNullOrEmpty(InJsonData))
                    {
                        SAPServerTime = Sync_Erp_Func.GetSAPServerTime(ConfigurationManager.ConnectionStrings["ServerTimeType"].ConnectionString); //获取SAP服务器时间
                        JsonModel jsmodel = JsonConvert.DeserializeObject <JsonModel>(SAPServerTime);
                        if (!jsmodel.result.Equals("1"))
                        {
                            errMsg += erpType.WmsName + "|" + jsmodel.resultValue;
                            continue;
                        }
                        SAPServerTime = JArray.Parse(jsmodel.data.ToString())[0]["head"]["ZDATUM"].ToString();
                    }
                    SyncTime_Model syncTime = new SyncTime_Model();

                    string Json = String.IsNullOrEmpty(InJsonData) ? Sync_Erp_Func.GetSAPJson(stockType, erpVoucherNo, erpType, ref syncTime) : InJsonData; //获取JSON
                    if (String.IsNullOrEmpty(Json))
                    {
                        errMsg += erpType.WmsName + "|" + erpVoucherNo + "|接口没有返回数据\r\n";
                        continue;
                    }
                    result = GetDataJson(Json, ref dataJson, erpType.WmsName, ref errMsg);
                    if (!result)
                    {
                        continue;
                    }
                    LogNet.SyncInfo(erpType.WmsName + ":\r\n" + dataJson);
                    string ErpvouType = erpType.ErpType.ToString();
                    //根据ERP类型查询WMS类型
                    var WmsTypeList = syncModelList.Where(p => p.ErpType != null && p.ErpType == ErpvouType).DistinctBy(s => new { s.WmsType, s.MainSubject });

                    List <string> sqlList = new List <string>();

                    List <string> headInsertKeys   = new List <string>();
                    List <string> detailInsertKeys = new List <string>();
                    List <string> headUpdateKeys   = new List <string>();
                    List <string> detailUpdateKeys = new List <string>();

                    List <Sync_Model> pmListbyType = syncModelList.FindAll(p => p.WmsType.ToString() == WmsTypeList.ToList()[0].WmsType && p.ErpType == ErpvouType && p.MainSubject == WmsTypeList.ToList()[0].MainSubject);

                    foreach (Sync_Model sync in pmListbyType)
                    {
                        if (sync.FieldHD.Equals("H"))
                        {
                            headInsertKeys.Add(sync.WmsField);
                            if (!(sync.FUNCTIONTYPE == 2 || sync.FUNCTIONTYPE == 1))
                            {
                                headUpdateKeys.Add(sync.WmsField);
                            }
                        }

                        else
                        {
                            detailInsertKeys.Add(sync.WmsField);
                            if (!(sync.FUNCTIONTYPE == 2 || sync.FUNCTIONTYPE == 1))
                            {
                                detailUpdateKeys.Add(sync.WmsField);
                            }
                        }
                    }

                    List <string> detailWhereStringList;
                    string[]      headwherekeys   = pmListbyType[0].WmsHeadKeys.Split(',');
                    string[]      detailwherekeys = null;
                    if (pmListbyType[0].WmsDetailKeys != null)
                    {
                        detailwherekeys = pmListbyType[0].WmsDetailKeys.Split(',');
                    }
                    JArray                jarray       = JArray.Parse(dataJson);
                    ParamaterFiled_DB     db           = new ParamaterFiled_DB();
                    List <string>         headValues   = new List <string>();
                    List <List <string> > detailValues = new List <List <string> >();
                    JToken                Head;
                    JArray                Detail;

                    foreach (var wmsType in WmsTypeList)
                    {
                        //生成SQL语句(insert\update\delete)
                        string insertHeadSQL   = String.Empty;
                        string insertDetailSQL = String.Empty;
                        string updateHeadSQL   = String.Empty;
                        string updateDetailSQL = String.Empty;

                        if (syncModelList.Count > 0 && !String.IsNullOrEmpty(dataJson.TrimStart('[').TrimEnd(']')))
                        {
                            string WmsvouType  = wmsType.WmsType.ToString();
                            string MainProject = wmsType.MainSubject.ToString();
                            //单据类型对应字段
                            pmListbyType = syncModelList.FindAll(p => p.WmsType.ToString() == WmsvouType && p.ErpType == ErpvouType && p.MainSubject == MainProject);
                            string headTableName   = pmListbyType[0].WmsTableH;
                            string detailTableName = pmListbyType[0].WmsTableD;

                            for (int i = 0; i < jarray.Count; i++) //记录数
                            {
                                headValues   = new List <string>();
                                detailValues = new List <List <string> >();
                                Head         = JObject.Parse(jarray[i]["head"].ToString());
                                string headWhereString = SyncManager.GetInstance().GetSAPwhereString(true, headwherekeys, pmListbyType, Head);
                                int    ID = SyncManager.GetInstance().CheckVoucherNoExit(stockType, headTableName, headWhereString);
                                if (ID == -99)
                                {
                                    continue;            //不需要执行update
                                }
                                //同一种单据类型中,没有生成过SQL的,第一次需要生成SQL语句
                                if (ID == 0) //insert
                                {
                                    if (String.IsNullOrEmpty(insertHeadSQL))
                                    {
                                        insertHeadSQL   = SqlModel.InsertSAPTitleSql;
                                        insertDetailSQL = SqlModel.InsertSAPDetailSql;
                                        string WmsVourcherNo = db.GetWmsWoucherNo(pmListbyType[0].WmsVoucherNoRual, headTableName, null);

                                        insertHeadSQL   = String.Format(insertHeadSQL, headTableName, "{2}", "{1}", "{0}", "{3}", WmsVourcherNo, ErpvouType, WmsvouType);
                                        insertDetailSQL = String.Format(insertDetailSQL, detailTableName, "{0}", "{2}", "{1}", "{3}", WmsVourcherNo);
                                    }
                                }
                                else
                                {
                                    if (String.IsNullOrEmpty(updateHeadSQL))
                                    {
                                        updateHeadSQL   = String.Format(SqlModel.UpdateSAPSql, headTableName, "{0}", "{1}");
                                        updateDetailSQL = String.Format(SqlModel.UpdateSAPSql, detailTableName, "{0}", "{1}");
                                    }
                                }


                                Detail = null;
                                detailWhereStringList = new List <string>();
                                if (!String.IsNullOrEmpty(Head.ToString()) && !String.IsNullOrEmpty(detailTableName))
                                {
                                    Detail = JArray.Parse(Head["body"].ToString());
                                    for (int j = 0; j < Detail.Count; j++)
                                    {
                                        string detailWhereString = SyncManager.GetInstance().GetSAPwhereString(false, detailwherekeys, pmListbyType, JObject.Parse(Detail[j].ToString()));
                                        detailWhereStringList.Add(detailWhereString);
                                    }
                                }


                                foreach (Sync_Model sync in pmListbyType)
                                {
                                    if (ID != 0 && (sync.FUNCTIONTYPE == 2 || sync.FUNCTIONTYPE == 1))
                                    {
                                        continue;                                                                //如果是update,FUNCTIONTYPE=2不需要赋值
                                    }
                                    if (sync.FieldHD.Equals("H"))
                                    {
                                        string value = sync.FUNCTIONTYPE == 1 ? sync.DefaultValue : Head[sync.ErpField].ToString();
                                        if (sync.DefaultType == 0)
                                        {
                                            value = sync.ErpField.Trim().Equals("") ? value : "'" + value + "'";
                                        }
                                        else
                                        if (sync.DefaultType == 1)
                                        {
                                            value = "CONVERT(varchar(100),'" + value + "', 20)";
                                        }
                                        else
                                        if (sync.DefaultType == 2)
                                        {
                                            string materialNo     = Head[sync.ErpField].ToString();
                                            string materialSubSql = String.Format(SqlModel.GetsAPmaterialIdsql, materialNo);
                                            value = "(" + materialSubSql + ")";
                                        }
                                        //else
                                        //{
                                        //   value = sync.DefaultType == 1 ? "CONVERT(varchar(100),'" + value + "', 20)" : ;
                                        //}
                                        headValues.Add(value.Trim());
                                    }
                                    else
                                    {
                                        if (Detail != null)
                                        {
                                            for (int j = 0; j < Detail.Count; j++)
                                            {
                                                JToken detailJToken = JObject.Parse(Detail[j].ToString());
                                                string value        = sync.FUNCTIONTYPE == 1 ? sync.DefaultValue : detailJToken[sync.ErpField].ToString();
                                                // value = sync.DefaultType == 1 ? "CONVERT(varchar(100),'" + value + "', 20)" : (sync.ErpField.Trim().Equals("") ? value : "'" + value + "'");

                                                if (sync.DefaultType == 0)
                                                {
                                                    value = sync.ErpField.Trim().Equals("") ? value : "'" + value + "'";
                                                }
                                                else
                                                if (sync.DefaultType == 1)
                                                {
                                                    value = "CONVERT(varchar(100),'" + value + "', 20)";
                                                }
                                                else
                                                if (sync.DefaultType == 2)
                                                {
                                                    string materialNo     = detailJToken[sync.ErpField].ToString();
                                                    string materialSubSql = String.Format(SqlModel.GetsAPmaterialIdsql, materialNo);
                                                    value = "(" + materialSubSql + ")";
                                                }
                                                else              //出库单自动分配客户对应发货仓库 只有发货单需要处理
                                                if (sync.DefaultType == 3 || sync.DefaultType == 4)
                                                {
                                                    string customerno        = detailJToken[sync.ErpField].ToString();
                                                    string warehouseIDSubSql = String.Format(sync.DefaultType == 3 ? SqlModel.GetWhareHouseID : SqlModel.GetWhareHouseNo, customerno);
                                                    value = "(" + warehouseIDSubSql + ")";
                                                }
                                                else
                                                //调拨单获取warehouseid
                                                if (sync.DefaultType == 5)
                                                {
                                                    string warehouseNo       = detailJToken[sync.ErpField].ToString();
                                                    string warehouseIDSubSql = String.Format(SqlModel.GetWhareHouseIDByNo, warehouseNo);
                                                    value = "(" + warehouseIDSubSql + ")";
                                                }
                                                if (detailValues.Count < (j + 1))
                                                {
                                                    List <string> valueD = new List <string>();
                                                    valueD.Add(value.Trim());
                                                    detailValues.Add(valueD);
                                                }
                                                else
                                                {
                                                    detailValues[j].Add(value.Trim());
                                                }
                                            }
                                        }
                                    }
                                }
                                if (ID == 0)
                                {
                                    int    NewTableID = db.GetHeadID(pmListbyType[0].WmsHeadID);
                                    string insertHead = String.Format(insertHeadSQL, String.Join(",", headValues.ToArray()), String.Join(",", headInsertKeys.ToArray()).ToString(), headWhereString, NewTableID);
                                    sqlList.Add(insertHead);
                                    if (detailValues.Count != 0)
                                    {
                                        for (int k = 0; k < detailWhereStringList.Count; k++)
                                        {
                                            string insertDetail = String.Format(insertDetailSQL, detailWhereStringList[k], String.Join(",", detailValues[k].ToArray()), String.Join(",", detailInsertKeys.ToArray()).ToString(), NewTableID);
                                            sqlList.Add(insertDetail);
                                        }
                                    }
                                }
                                else
                                {
                                    List <string> updateHeadValues = new List <string>();

                                    for (int k = 0; k < headUpdateKeys.Count; k++)
                                    {
                                        updateHeadValues.Add(headUpdateKeys[k] + "=" + headValues[k]);
                                    }
                                    string updateHSQL = String.Format(updateHeadSQL, String.Join(",", updateHeadValues.ToArray()), headWhereString);
                                    sqlList.Add(updateHSQL);
                                    for (int l = 0; l < detailWhereStringList.Count; l++)
                                    {
                                        List <string> updateDetailValues = new List <string>();
                                        for (int m = 0; m < detailUpdateKeys.Count; m++)
                                        {
                                            updateDetailValues.Add(detailUpdateKeys[m] + "=" + detailValues[l][m]);
                                        }
                                        string updateDSQL = String.Format(updateDetailSQL, String.Join(",", updateDetailValues.ToArray()), detailWhereStringList[l]);
                                        sqlList.Add(updateDSQL);
                                    }
                                }
                                if (sqlList.Count >= 100)
                                {
                                    result  = SyncManager.GetInstance().Executrans(sqlList, ref errMsg);
                                    sqlList = new List <string>();
                                    GC.Collect();
                                }
                            }
                        }
                    }
                    if (sqlList.Count != 0)
                    {
                        result = SyncManager.GetInstance().Executrans(sqlList, ref errMsg);
                    }
                    if (result)
                    {
                        if (String.IsNullOrEmpty(erpVoucherNo) && String.IsNullOrEmpty(InJsonData))
                        {
                            syncTime.SyncServerTime = SAPServerTime;
                            //同步成功,更新同步时间
                            SyncTimeManager.GetInstance().InsertOrUpdateSyncTime(syncTime);
                        }
                    }

                    GC.Collect();
                }
                LogNet.SyncInfo(errMsg);
            }
            catch (Exception ex)
            {
                errMsg += wmsVourcherType + "|" + ex.Message;
                LogNet.SyncInfo(errMsg);
            }
            return(result);
        }