Exemple #1
0
        internal static string GetSAPJson(int StockType, string erpVoucherNo, Sync_Model Type, ref SyncTime_Model syncTime)
        {
            syncTime = SyncTimeManager.GetInstance().GetLastSyncTime(Type.WmsType);
            if (syncTime == null)
            {
                syncTime = new SyncTime_Model();
                syncTime.SyncServerTime = DateTime.Now.AddDays(Convert.ToInt32(ConfigurationManager.ConnectionStrings["SyncAddDate"].ConnectionString)).ToString("yyyyMMdd,HHmmss");
                syncTime.WmsType        = Type.WmsType;
                syncTime.ID             = 0;
            }

            BILBasic.Interface.T_Interface_Func TIF = new BILBasic.Interface.T_Interface_Func();
            string json = "{\"code\":\"" + erpVoucherNo + "\",\"VoucherType\":\"" + Type.WmsType.ToString() + "\",\"sync_time\":\"";

            json += syncTime.SyncServerTime;
            json += "\",\"erp_vourcher_type\":\"" + Type.ErpType + "\"}";
            LogNet.SyncInfo(json);
            return //"{\"result\":1,\"resultValue\":\"\",\"data\":[{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431000180890\",\"cinvname\":\"LED消防应急标志灯线下款06  双面安全出口\",\"cinvstd\":\"NEP-XBZ06-021\",\"cbarcode\":\"2222220002565\",\"cinvdefine6\":\"883675\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431000180906\",\"cinvname\":\"LED消防应急标志灯线下款08   嵌入式左向\",\"cinvstd\":\"NEP-XBZ08-012\",\"cbarcode\":\"2222220002581\",\"cinvdefine6\":\"883691\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431001120306\",\"cinvname\":\"吸顶吊灯24 铜本色 玻璃灯罩 标配E27螺纹灯头,负载单头最大功率40W,整灯240W,出货不配光源\",\"cinvstd\":\"NEP-XDD24240X4\",\"cbarcode\":\"6941461102961\",\"cinvdefine6\":\"209479\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431001120322\",\"cinvname\":\"吸顶灯218 108W  双色 白色 方形\",\"cinvstd\":\"NEP-XD21810881-F\",\"cbarcode\":\"6941461103272\",\"cinvdefine6\":\"209510\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431001730064\",\"cinvname\":\"吸顶灯124款 32W 双色温  粉色面罩带厚亚克力\",\"cinvstd\":\"NEP-XD12403284-Y\",\"cbarcode\":\"6941461106815\",\"cinvdefine6\":\"209864\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431002170341\",\"cinvname\":\"线下新型筒灯22 3.5W 银色(铝面环) 6000K\",\"cinvstd\":\"NEP-TD2200363A\",\"cbarcode\":\"6941461103265\",\"cinvdefine6\":\"209509\",\"cumName\":\"只\"}},{\"head\":{\"cwhcode\":null,\"cwhname\":null,\"iquantity\":null,\"cinvcode\":\"710431000260017\",\"cinvname\":\"LED筒灯27 18W 白色 3000K\",\"cinvstd\":\"NEP-TD2701831G\",\"cbarcode\":\"2222220002644\",\"cinvdefine6\":\"883754\",\"cumName\":\"只\"}}]}";
                   //"{\"result\":1,\"resultValue\":\"\",\"data\":[{\"head\":{\"body\":[{\"cwhcode1\":\"013\",\"cbmemo\":\"\",\"boxQty\":10,\"rQty\":0,\"packQty\":1,\"cwhname\":\"\",\"cwhcode\":\"013\",\"cinvcode\":\"710431001120181\",\"cinvname\":\"吸顶灯188款,白色灯罩,12头组合,192W,3000K+5700K\",\"cinvstd\":\"NEP-XD18819281\",\"iquantity\":10.00,\"foutquantity\":10.0000000000,\"Qty\":0.0000000000,\"Qty1\":0.0000000000,\"itaxunitprice\":0.0,\"itaxrate\":0.0,\"invbatch\":null,\"irowno\":1,\"iunitprice\":0.0,\"itax\":0.0,\"cdlcode\":\"FH1811230008\",\"ccuscode\":\"10097701\",\"ccusname\":null,\"idlsid\":1002675166},{\"cwhcode1\":\"013\",\"cbmemo\":\"\",\"boxQty\":10,\"rQty\":0,\"packQty\":1,\"cwhname\":\"\",\"cwhcode\":\"013\",\"cinvcode\":\"710431001120181\",\"cinvname\":\"吸顶灯188款,白色灯罩,12头组合,192W,3000K+5700K\",\"cinvstd\":\"NEP-XD18819281\",\"iquantity\":10.00,\"foutquantity\":10.0000000000,\"Qty\":0.0000000000,\"Qty1\":0.0000000000,\"itaxunitprice\":0.0,\"itaxrate\":0.0,\"invbatch\":null,\"irowno\":2,\"iunitprice\":0.0,\"itax\":0.0,\"cdlcode\":\"FH1811230008\",\"ccuscode\":\"10097701\",\"ccusname\":null,\"idlsid\":1002675167}],\"ccusphone\":\"0551-64369575\",\"ccusperson\":\"王世珍\",\"DLID\":1000385412,\"cDLCode\":\"FH1811230008\",\"dDate\":\"2018-11-23T00:00:00\",\"cRdCode\":null,\"cDepCode\":\"030901\",\"cDepName\":\"营业\",\"cPersonCode\":null,\"cSOCode\":null,\"cCusCode\":\"10097701\",\"cCusAddress\":null,\"cShipAddress\":\"0551-64369575 15605699888 安徽省合肥市瑶海区瑶海工业园灵石路中段安徽百事兴电气有限公司 正泰仓库\",\"cexch_name\":\"\",\"iExchRate\":0.0,\"iTaxRate\":0.0,\"cMemo\":\"照明\",\"cDefine1\":null,\"cDefine2\":\"\",\"cDefine3\":\"\",\"cDefine4\":null,\"cDefine5\":null,\"cDefine6\":null,\"cDefine7\":null,\"cDefine8\":null,\"cDefine9\":\"\",\"cDefine10\":\"\",\"cDefine11\":\"\",\"cDefine12\":null,\"cDefine13\":\"\",\"cDefine14\":null,\"cDefine15\":null,\"cDefine16\":null,\"cMaker\":\"demo\",\"cCusName\":\"王世珍-安徽百事兴电气有限公司\",\"cBusType\":\"普通销售\"}}]}";
                   (TIF.GetModelListByInterface(json));
        }
Exemple #2
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);
        }