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));
}
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);
}