/// <summary>
/// VEX框架的主入口
/// </summary>
/// <returns></returns>
public Boolean Execute()
{
try
{
int nExecuteRowCount = 200;
if (m_request != null && m_request.Tables.Count > 0 && m_request.Tables[0].Rows.Count == 1)
{
if (m_request.Tables[0].Columns.Contains("executeRowCount"))
{
try
{
nExecuteRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["executeRowCount"]);
}
catch (Exception ex)
{
}
}
}
m_conn.TimeOut = 60;
//事务开始
m_conn.BeginTransaction();
string strSQL = @"
SELECT top " + nExecuteRowCount.ToString() + @" [NewID]
,[CreateTime]
,[Timestamps]
,[WMSFlag]
,[WCSFlag]
,[SKU]
,[Descr_C]
,[SKU_GROUP4]
,[SKU_GROUP1]
,[Price]
,[Alternate_SKU1]
,[ReservedField01]
,[ReservedField02]
,[ReservedField03]
,[ReservedField04]
,[ReservedField05]
,[NOTES]
INTO #tmp
FROM [dbo].[Put_SKU]
WHERE isnull(WMSFlag,0)=0
--AND NOT EXISTS(
-- SELECT 1 FROM FLUX_WMS.dbo.BAS_SKU WITH(NOLOCK)
-- WHERE [Put_SKU].SKU=bas_sku.sku
-- )
ORDER BY CreateTime ASC,Timestamps ASC
UPDATE [dbo].[Put_SKU]
SET WMSFlag=1
WHERE EXISTS(SELECT 1 FROM #tmp WHERE #tmp.NewID = Put_SKU.newid)
SELECT * FROM #tmp
DROP TABLE #tmp
";
DataTable dtData = m_conn.GetDataTable(strSQL);
if (dtData.Rows.Count > 0)
{
WMSWS.CERPWSService2Service ws = new WMSWS.CERPWSService2Service();
WMSWS.wmsSecurityInfo wmsSecurityInfo = new WMSWS.wmsSecurityInfo();
WMSWS.wmsParamInfo wmsParamInfo = new WMSWS.wmsParamInfo();
WMSWS.wmsSkuInfo wmsSkuInfo = new WMSWS.wmsSkuInfo();
wmsParamInfo.customerid = "PAND";
wmsParamInfo.messageid = "102";
wmsParamInfo.stdno = "SKU";
wmsParamInfo.warehouseid = "WH01";
wmsSecurityInfo.password = "******";
wmsSecurityInfo.username = "******";
wmsSkuInfo.wmsSkuHeaders = new WMSWS.wmsSkuHeader[dtData.Rows.Count];
for (int i = 0; i < dtData.Rows.Count; i++)
{
WMSWS.wmsSkuHeader skuHeader = new WMSWS.wmsSkuHeader();
skuHeader.SKU = dtData.Rows[i]["SKU"].ToString();
skuHeader.DESCR_C = dtData.Rows[i]["DESCR_C"].ToString();
skuHeader.SKU_GROUP1 = dtData.Rows[i]["SKU_GROUP1"].ToString();
skuHeader.SKU_GROUP4 = dtData.Rows[i]["SKU_GROUP4"].ToString();
skuHeader.CUSTOMERID = "EP";
skuHeader.PRICE = dtData.Rows[i]["PRICE"].ToString();
skuHeader.ALTERNATE_SKU1 = dtData.Rows[i]["ALTERNATE_SKU1"].ToString();
skuHeader.RESERVEDFIELD01 = dtData.Rows[i]["RESERVEDFIELD01"].ToString();
skuHeader.RESERVEDFIELD02 = dtData.Rows[i]["RESERVEDFIELD02"].ToString();
skuHeader.RESERVEDFIELD03 = dtData.Rows[i]["RESERVEDFIELD03"].ToString();
skuHeader.RESERVEDFIELD04 = dtData.Rows[i]["RESERVEDFIELD04"].ToString();
skuHeader.RESERVEDFIELD05 = dtData.Rows[i]["RESERVEDFIELD05"].ToString();
wmsSkuInfo.wmsSkuHeaders[i] = skuHeader;
}
ws.Timeout = 3600 * 1000;
WMSWS.wmsResultInfo resultInfo = ws.putSKUData(wmsSecurityInfo, wmsSkuInfo, wmsParamInfo);
//执行失败
if (resultInfo.returnDesc != "成功")
{
m_conn.RollbackTransaction();
return false;
}
}
//事务提交 --注意：不能移动到每个方法里面，这里面的方法会有互相调用的情况
m_conn.CommitTransaction();
}
catch
{
m_conn.RollbackTransaction();
throw;
}
return true;
}
/// <summary>
/// VEX框架的主入口
/// </summary>
/// <returns></returns>
public Boolean Execute()
{
try
{
int nExecuteRowCount = 200;//定义执行条数，如果没有配置，就默认200
if (m_request != null && m_request.Tables.Count > 0 && m_request.Tables[0].Rows.Count == 1)
{
if (m_request.Tables[0].Columns.Contains("executeRowCount"))
{
try
{
nExecuteRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["executeRowCount"]);
}
catch (Exception ex)
{
}
}
}
m_conn.TimeOut = 60;
//事务开始
m_conn.BeginTransaction();
//获取主表信息
string strSQL = @"SELECT * into #tmp FROM (
SELECT top " + nExecuteRowCount.ToString() + @"
ROW_NUMBER() OVER ( PARTITION BY [ASNREFERENCE1] ORDER BY requestid DESC ) AS a ,
[NewID]
,[CreateTime]
,[Timestamps]
,[requestid]
,[ASNREFERENCE1]
,[CustomerID]
,[ASNType]
,[SupplierId]
,[Supplier_Name]
,[H_EDI_01]
,[H_EDI_02]
,[Notes]
,[ASNCreationTime]
,[LotAtt05]
,[LotAtt06]
,[WMSFLAG]
FROM [dbo].[PUT_DRPd_Msell]
WHERE isnull(WMSFlag,0)=0
AND ASNType='TS'
) f
WHERE a = 1
ORDER BY [requestid] DESC
SELECT* FROM #tmp ";
DataTable dtData = m_conn.GetDataTable(strSQL);
if (dtData.Rows.Count > 0)
{
WMSWS.CERPWSService2Service ws = new WMSWS.CERPWSService2Service();
WMSWS.wmsSecurityInfo wmsSecurityInfo = new WMSWS.wmsSecurityInfo();// 系统级参数
WMSWS.wmsParamInfo wmsParamInfo = new WMSWS.wmsParamInfo();// 系统级参数
wmsParamInfo.customerid = "PAND";
wmsParamInfo.messageid = "1042";
wmsParamInfo.stdno = "ASN";
wmsParamInfo.warehouseid = "WH01";
wmsSecurityInfo.password = "******";
wmsSecurityInfo.username = "******";
WMSWS.wmsASNInfo wmsASNInfo = new WMSWS.wmsASNInfo();
wmsASNInfo.wmsASNHeaders = new WMSWS.wmsASNHeader[dtData.Rows.Count];//定义主表行数
for (int i = 0; i < dtData.Rows.Count; i++)
{
WMSWS.wmsASNHeader ASNHeader = new WMSWS.wmsASNHeader();
//表头主表信息
ASNHeader.ASNREFERENCE1 = dtData.Rows[i]["ASNREFERENCE1"].ToString();
ASNHeader.CUSTOMERID = dtData.Rows[i]["CUSTOMERID"].ToString();
ASNHeader.ASNTYPE = dtData.Rows[i]["ASNType"].ToString();
ASNHeader.SUPPLIERID = dtData.Rows[i]["SupplierId"].ToString();
ASNHeader.SUPPLIER_NAME = dtData.Rows[i]["Supplier_Name"].ToString();
ASNHeader.NOTES = dtData.Rows[i]["Notes"].ToString();
ASNHeader.h_EDI_01 = dtData.Rows[i]["H_EDI_01"].ToString();
ASNHeader.h_EDI_02 = dtData.Rows[i]["H_EDI_02"].ToString();
ASNHeader.ASNCREATIONTIME = Convert.ToDateTime(dtData.Rows[i]["ASNCreationTime"]).ToString("yyyy-MM-dd HH:mm:ss");
//按照主表订单号和状态值获取子表信息
string strRequestid = dtData.Rows[i]["requestid"].ToString();
string strSQLSub = @"SELECT b.NewID ,b.Timestamps ,b.requestid ,b.ASNREFERENCE1 ,b.SKU ,b.ExpectedQty ,b.D_EDI_01 ,b.D_EDI_02
,b.D_EDI_03 ,b.D_EDI_04,a.LotAtt05,a.LotAtt06
FROM [PUT_DRPd_Msell] a LEFT JOIN [PUT_DRPd_Msellsub] b ON b.ASNREFERENCE1 = a.ASNREFERENCE1 AND b.requestid = a.requestid
WHERE ISNULL(b.ExpectedQty, '') != '' and a.ASNREFERENCE1='" + dtData.Rows[i]["ASNREFERENCE1"].ToString() + "' AND a.requestid='" + strRequestid + "'";
DataTable dtDataSub = m_conn.GetDataTable(strSQLSub);
ASNHeader.detailsItem = new WMSWS.wmsASNDetails[dtDataSub.Rows.Count];//定义子表行数
for (int j = 0; j < dtDataSub.Rows.Count; j++)
{
WMSWS.wmsASNDetails ASNDetail = new WMSWS.wmsASNDetails();
ASNDetail.SKU = dtDataSub.Rows[j]["SKU"].ToString();
ASNDetail.EXPECTEDQTY = dtDataSub.Rows[j]["ExpectedQty"].ToString();
ASNDetail.LOTATT05 = dtDataSub.Rows[j]["LotAtt05"].ToString();
ASNDetail.LOTATT06 = dtDataSub.Rows[j]["LotAtt06"].ToString();
ASNDetail.d_EDI_01 = dtDataSub.Rows[j]["D_EDI_01"].ToString();
ASNDetail.d_EDI_02 = dtDataSub.Rows[j]["D_EDI_02"].ToString();
ASNDetail.d_EDI_03 = dtDataSub.Rows[j]["D_EDI_03"].ToString();
ASNDetail.d_EDI_04 = dtDataSub.Rows[j]["D_EDI_04"].ToString();
ASNHeader.detailsItem[j] = ASNDetail;//把子表放入主表容器中
}
wmsASNInfo.wmsASNHeaders[i] = ASNHeader;//把一个订单放入头文件中
}
ws.Timeout = 3600 * 1000;
WMSWS.wmsResultInfo resultInfo = ws.putASNData(wmsSecurityInfo, wmsASNInfo, wmsParamInfo);//传入接口
//执行失败
if (resultInfo.returnDesc != "成功")
{
m_conn.RollbackTransaction();
return false;
}
else
{
string strSQLflag = @"
UPDATE [dbo].[PUT_DRPd_Msell]
SET WMSFlag=1
WHERE ASNType='TS'
--AND EXISTS(SELECT 1 FROM #tmp WHERE #tmp.NewID = PUT_DRPd_Msell.newid)
And requestid <='" + dtData.Rows[0]["requestid"].ToString() + @"'
DROP TABLE #tmp ";
m_conn.ExcuteQuerryByTran(strSQLflag, new object[] { });
}
HZY.COM.Common.Log.WirteLogWS(resultInfo.returnDesc, null);
}
//事务提交 --注意：不能移动到每个方法里面，这里面的方法会有互相调用的情况
m_conn.CommitTransaction();
}
catch (Exception ex)
{
m_conn.RollbackTransaction();
HZY.COM.Common.Log.WirteLogWS(ex, null);
throw ex;
}
return true;
}
//string strMaxRequestid;
#endregion
#region GetData
/// <summary>
/// VEX框架的主入口
/// </summary>
/// <returns></returns>
public Boolean Execute()
{
try
{
int nExecuteRowCount = 200;//定义执行条数，如果没有配置，就默认200
if (m_request != null && m_request.Tables.Count > 0 && m_request.Tables[0].Rows.Count == 1)
{
if (m_request.Tables[0].Columns.Contains("executeRowCount"))
{
try
{
nExecuteRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["executeRowCount"]);
}
catch (Exception ex)
{
}
}
}
m_conn.TimeOut = 60;
//事务开始
m_conn.BeginTransaction();
//获取主表信息
string strSQL = @" SELECT * into #tmp FROM ( SELECT top " + nExecuteRowCount.ToString() + @"
ROW_NUMBER() OVER ( PARTITION BY [SOReference1] ORDER BY requestid DESC ) AS a ,
[NewID]
,[CreateTime]
,[Timestamps]
,[requestid]
,[SOReference1]
,[SOReference2]
,[CustomerID]
,[OrderType]
,[ConsigneeID]
,[ConsigneeName]
,[C_Address1]
,[OrderTime]
,[H_EDI_01]
,[H_EDI_02]
,[H_EDI_03]
,[H_EDI_04]
,[LotAtt05]
,[LotAtt06]
,[WMSFLAG]
,[NOTES]
FROM PUT_DRPd_Sell
WHERE ISNULL(WMSFlag, 0) = 0
) f
WHERE a = 1
ORDER BY [requestid] DESC
SELECT* FROM #tmp ";
DataTable dtData = m_conn.GetDataTable(strSQL);
//string strMaxRequestid = dtData.Rows[0]["requestid"].ToString();
if (dtData.Rows.Count > 0)
{
WMSWS.CERPWSService2Service ws = new WMSWS.CERPWSService2Service();
WMSWS.wmsSecurityInfo wmsSecurityInfo = new WMSWS.wmsSecurityInfo();// 系统级参数
WMSWS.wmsParamInfo wmsParamInfo = new WMSWS.wmsParamInfo();// 系统级参数
wmsParamInfo.customerid = "PAND";
wmsParamInfo.messageid = "1061";
wmsParamInfo.stdno = "SO";
wmsParamInfo.warehouseid = "WH01";
wmsSecurityInfo.password = "******";
wmsSecurityInfo.username = "******";
WMSWS.wmsSOInfo wmsSOInfo = new WMSWS.wmsSOInfo();
wmsSOInfo.wmsSOHeaders = new WMSWS.wmsSOHeader[dtData.Rows.Count];//定义主表行数
for (int i = 0; i < dtData.Rows.Count; i++)
{
WMSWS.wmsSOHeader SOHeader = new WMSWS.wmsSOHeader();
//表头主表信息
SOHeader.SOREFERENCE1 = dtData.Rows[i]["SOReference1"].ToString();
SOHeader.SOREFERENCE2 = dtData.Rows[i]["SOReference2"].ToString();
SOHeader.CUSTOMERID = dtData.Rows[i]["CustomerID"].ToString();
SOHeader.ORDERTYPE = dtData.Rows[i]["OrderType"].ToString();
SOHeader.CONSIGNEEID = dtData.Rows[i]["ConsigneeID"].ToString();
SOHeader.CONSIGNEENAME = dtData.Rows[i]["ConsigneeName"].ToString();
SOHeader.c_ADDRESS1 = dtData.Rows[i]["C_Address1"].ToString();
SOHeader.ORDERTIME = Convert.ToDateTime(dtData.Rows[i]["OrderTime"]).ToString("yyyy-MM-dd HH:mm:ss");
SOHeader.h_EDI_01 = dtData.Rows[i]["H_EDI_01"].ToString();
SOHeader.h_EDI_02 = dtData.Rows[i]["H_EDI_02"].ToString();
SOHeader.h_EDI_03 = dtData.Rows[i]["H_EDI_03"].ToString();
SOHeader.h_EDI_04 = dtData.Rows[i]["H_EDI_04"].ToString();
//按照主表订单号和状态值获取子表信息
string strRequestid = dtData.Rows[i]["requestid"].ToString();
string strSQLSub = @"SELECT b.NewID ,b.Timestamps ,b.requestid ,b.SOReference1 ,b.SKU ,b.QtyOrdered ,b.D_EDI_01 ,b.D_EDI_02 ,a.LotAtt05 ,a.LotAtt06
FROM PUT_DRPd_Sell a LEFT JOIN PUT_DRPd_Sellsub b ON b.SOReference1 = a.SOReference1 AND b.requestid = a.requestid
WHERE ISNULL(b.QtyOrdered, '') != '' and a.SOReference1='" + dtData.Rows[i]["SOReference1"].ToString() + "' AND a.requestid='" + strRequestid + "'";
DataTable dtDataSub = m_conn.GetDataTable(strSQLSub);
SOHeader.detailsItem = new WMSWS.wmsSODetails[dtDataSub.Rows.Count];//定义子表行数
for (int j = 0; j < dtDataSub.Rows.Count; j++)
{
WMSWS.wmsSODetails SODetails = new WMSWS.wmsSODetails();
SODetails.SKU = dtDataSub.Rows[j]["SKU"].ToString();
SODetails.QTYORDERED = dtDataSub.Rows[j]["QtyOrdered"].ToString();
SODetails.LOTATT05 = dtDataSub.Rows[j]["LotAtt05"].ToString();
SODetails.LOTATT06 = dtDataSub.Rows[j]["LotAtt06"].ToString();
SODetails.d_EDI_01 = dtDataSub.Rows[j]["D_EDI_01"].ToString();
SODetails.d_EDI_02 = dtDataSub.Rows[j]["D_EDI_02"].ToString();
SOHeader.detailsItem[j] = SODetails;//把子表放入主表容器中
}
wmsSOInfo.wmsSOHeaders[i] = SOHeader;//把一个订单放入头文件中
}
ws.Timeout = 3600 * 1000;
WMSWS.wmsResultInfo resultInfo = ws.putSOData(wmsSecurityInfo, wmsSOInfo, wmsParamInfo);//传入接口
HZY.COM.Common.Log.WirteLogWS(resultInfo.returnDesc, null);
//执行失败
if (resultInfo.returnDesc != "成功")
{
m_conn.RollbackTransaction();
return false;
}
else
{
string strSQLflag = @"
UPDATE [dbo].[PUT_DRPd_Sell]
SET WMSFlag=1
--WHERE EXISTS(SELECT 1 FROM #tmp WHERE #tmp.NewID = PUT_DRPd_Sell.newid)
Where requestid <='" + dtData.Rows[0]["requestid"].ToString() + @"'
DROP TABLE #tmp ";
m_conn.ExcuteQuerryByTran(strSQLflag, new object[] { });
}
// HZY.COM.Common.Log.WirteLogWS(resultInfo.returnDesc, null);
}
//事务提交 --注意：不能移动到每个方法里面，这里面的方法会有互相调用的情况
m_conn.CommitTransaction();
}
catch (Exception ex)
{
m_conn.RollbackTransaction();
HZY.COM.Common.Log.WirteLogWS(ex, null);
throw ex;
}
return true;
}