private String SapLogon(String userName, String password, out String statusCode, out String statusMessage)
{
String returnValue = String.Empty;
statusCode = StatusCode.OK.ToString();
statusMessage = String.Empty;
// 写入SAP
try
{
String connectString = ConfigHelper.GetValue("SAPConnectionString").ToString().Replace("{Username}", userName).Replace("{Password}", password);
SAPLogonCtrl.SAPLogonControlClass SapLogon = new SAPLogonCtrl.SAPLogonControlClass();
if (!ParseSapConnection(SapLogon, connectString))
{
statusCode = "-2";
statusMessage = "SAP连接串格式错误。";
return(returnValue);
}
// 以下建立与R3的通信机制
SAPLogonCtrl.Connection EnterSap = (SAPLogonCtrl.Connection)SapLogon.NewConnection();//建立连接
if (EnterSap.Logon(0, true) == false)
{
statusCode = "-1";
statusMessage = "连接SAP失败。";
return(returnValue);
}
returnValue = userName;
}
// 写入SAP失败
catch (Exception exception)
{
this.ProcessException(exception);
}
return(userName);
}
public void Main()
{
SAPLogonCtrl.SAPLogonControlClass login = new SAPLogonCtrl.SAPLogonControlClass();
login.ApplicationServer = ConfigurationManager.AppSettings["lg_ApplicationServer"];
login.Client = ConfigurationManager.AppSettings["lg_Client"];
login.Language = ConfigurationManager.AppSettings["lg_Language"];
login.User = ConfigurationManager.AppSettings["lg_User"];
login.Password = ConfigurationManager.AppSettings["lg_Password"];
login.SystemNumber = Int32.Parse(ConfigurationManager.AppSettings["lg_SystemNumber"]);
SAPLogonCtrl.Connection conn = (SAPLogonCtrl.Connection)login.NewConnection();
try
{
if (conn.Logon(0, true))
{
//lblLogonStatus.Text = "登入SAP成功";
SAPFunctionsOCX.SAPFunctionsClass func = new SAPFunctionsOCX.SAPFunctionsClass();
func.Connection = conn;
SAPFunctionsOCX.IFunction ifunc = (SAPFunctionsOCX.IFunction)func.Add("RFC_READ_TABLE");
SAPFunctionsOCX.IParameter iQUERY_TABLE = (SAPFunctionsOCX.IParameter)ifunc.get_Exports("QUERY_TABLE");
iQUERY_TABLE.Value = iQueryTable;
SAPFunctionsOCX.IParameter iDELIMITER = (SAPFunctionsOCX.IParameter)ifunc.get_Exports("DELIMITER");
iDELIMITER.Value = "|";
SAPTableFactoryCtrl.Tables tables = (SAPTableFactoryCtrl.Tables)ifunc.Tables;
SAPTableFactoryCtrl.Table tOPTIONS = (SAPTableFactoryCtrl.Table)tables.get_Item("OPTIONS");
tOPTIONS.AppendGridData(1, 1, 1, "");
SAPTableFactoryCtrl.Table tFIELDS = (SAPTableFactoryCtrl.Table)tables.get_Item("FIELDS");
if (iQueryField != null)
{
int StringStart = 0;
int iColumnIndex = 1;
int isDoExit = 0;
while (isDoExit == 0)
{
int StringEnd = iQueryField.ToString().IndexOf(",", StringStart);
if (StringEnd == -1)
{
StringEnd = iQueryField.ToString().Length;
isDoExit = 1;
}
int StringLength = StringEnd - StringStart;
tFIELDS.AppendGridData(1, iColumnIndex, 1, iQueryField.ToString().Substring(StringStart, StringLength));
StringStart = StringEnd + 1;
iColumnIndex = iColumnIndex + 1;
}
}
ifunc.Call();
oException = ifunc.Exception;
SAPTableFactoryCtrl.Table tDATA = (SAPTableFactoryCtrl.Table)tables.get_Item("DATA");
for (int m = 1; m <= tDATA.RowCount; m++)
{
int StringStart = 0;
Dictionary <string, object> listObject = new Dictionary <string, object>();
for (int n = 1; n <= tFIELDS.RowCount; n++)
{
int StringEnd = tDATA.get_Cell(m, 1).ToString().IndexOf("|", StringStart);
if (StringEnd == -1)
{
StringEnd = tDATA.get_Cell(m, 1).ToString().Length;
}
int StringLength = StringEnd - StringStart;
listObject.Add(tFIELDS.get_Cell(n, 1).ToString(), tDATA.get_Cell(m, 1).ToString().Substring(StringStart, StringLength));
StringStart = StringEnd + 1;
}
listResult.Add(listObject);
}
conn.Logoff();
}
else
{
throw new Exception("Logon Fail");
}
Result = listResult;
}
catch (Exception ex)
{
throw ex;
}
}
public void Main()
{
SAPLogonCtrl.SAPLogonControlClass login = new SAPLogonCtrl.SAPLogonControlClass();
login.ApplicationServer = ConfigurationManager.AppSettings["lg_ApplicationServer"];
login.Client = ConfigurationManager.AppSettings["lg_Client"];
login.Language = ConfigurationManager.AppSettings["lg_Language"];
login.User = ConfigurationManager.AppSettings["lg_User"];
login.Password = ConfigurationManager.AppSettings["lg_Password"];
login.SystemNumber = Int32.Parse(ConfigurationManager.AppSettings["lg_SystemNumber"]);
string str_json = string.Empty;
SAPLogonCtrl.Connection conn = (SAPLogonCtrl.Connection)login.NewConnection();
//conn.SAPRouter
try
{
if (conn.Logon(0, true))
{
//lblLogonStatus.Text = "登入SAP成功";
SAPFunctionsOCX.SAPFunctionsClass func = new SAPFunctionsOCX.SAPFunctionsClass();
func.Connection = conn;
SAPFunctionsOCX.IFunction ifunc = (SAPFunctionsOCX.IFunction)func.Add("RFC_READ_TABLE");
SAPFunctionsOCX.IParameter iQUERY_TABLE = (SAPFunctionsOCX.IParameter)ifunc.get_Exports("QUERY_TABLE");
iQUERY_TABLE.Value = iQueryTable;
SAPFunctionsOCX.IParameter iDELIMITER = (SAPFunctionsOCX.IParameter)ifunc.get_Exports("DELIMITER");
iDELIMITER.Value = "|";
SAPTableFactoryCtrl.Tables tables = (SAPTableFactoryCtrl.Tables)ifunc.Tables;
SAPTableFactoryCtrl.Table tOPTIONS = (SAPTableFactoryCtrl.Table)tables.get_Item("OPTIONS");
tOPTIONS.AppendGridData(1, 1, 1, "");
SAPTableFactoryCtrl.Table tFIELDS = (SAPTableFactoryCtrl.Table)tables.get_Item("FIELDS");
//tFIELDS.AppendGridData(1, 1, 1, "BNAME");
//tFIELDS.AppendGridData(1, 2, 1, "TERMINAL");
ifunc.Call();
SAPTableFactoryCtrl.Table tDATA = (SAPTableFactoryCtrl.Table)tables.get_Item("DATA");
//得到一個DataTable物件
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
for (int i = 1; i <= tFIELDS.RowCount; i++)
{
dt.Columns.Add(tFIELDS.get_Cell(i, 1).ToString());
}
for (int m = 1; m <= tDATA.RowCount; m++)
{
DataRow dr = dt.NewRow();
int StringStart = 0;
for (int n = 1; n <= tFIELDS.RowCount; n++)
{
int StringEnd = tDATA.get_Cell(m, 1).ToString().IndexOf("|", StringStart);
if (StringEnd == -1)
{
StringEnd = tDATA.get_Cell(m, 1).ToString().Length;
}
int StringLength = StringEnd - StringStart;
dr[tFIELDS.get_Cell(n, 1).ToString()] = tDATA.get_Cell(m, 1).ToString().Substring(StringStart, StringLength);
StringStart = StringEnd + 1;
}
dt.Rows.Add(dr);
}
//將DataTable轉成JSON字串
str_json = JsonConvert.SerializeObject(dt, Formatting.Indented);
dt2 = JsonConvert.DeserializeObject <DataTable>(str_json);
conn.Logoff();
}
else
{
throw new Exception("Logon Fail");
}
Result = str_json;
}
catch (Exception ex)
{
throw ex;
}
}
private void connSAP()
{
Sapcon oLogon = new Sapcon(clientNum);
SAPLogonCtrl.Connection oSAPConn = CType <SAPLogonCtrl.Connection>(oLogon.NewConnection());
try
{
if (oSAPConn.Logon(0, true))
{
sDate1 = txtDate1.Text.Trim();
sDate2 = txtDate2.Text.Trim();
sPO = txtPO.Text.Trim();
if (ddlMvt.SelectedIndex > 0)
{
sMvt = ddlMvt.SelectedValue.ToString();
}
sMtlDoc = txtMtlDoc.Text.Trim();
sRefDoc = txtRefDoc.Text.Trim();
sVendorName = txtVendorName.Text.Trim();
sMaterial = txtMaterial.Text.Trim();
sZflag = convertFlag(cbZflag.Checked);
SAPFunctionsClass func = new SAPFunctionsClass();
func.Connection = oSAPConn;
if (string.IsNullOrEmpty(sDate1))
{
sDate1 = DateTime.Today.ToString("yyyyMMdd");
}
if (string.IsNullOrEmpty(sDate2))
{
sDate2 = sDate1;
}
//功能模組名稱
IFunction ifunc = (IFunction)func.Add(rfcName);
//查詢參數：起始日期
IParameter pDate1 = (IParameter)ifunc.get_Exports("DATE1");
pDate1.Value = sDate1;
//查詢參數：結束日期
IParameter pDate2 = (IParameter)ifunc.get_Exports("DATE2");
if (txtDate2.Text == "")
{
pDate2.Value = sDate1;
}
else
{
pDate2.Value = sDate2;
}
//查詢參數：未產生105
IParameter pFlag = (IParameter)ifunc.get_Exports("ZFLAG");
pFlag.Value = sZflag;
//查詢參數：採購單
IParameter pPO = (IParameter)ifunc.get_Exports("PO");
pPO.Value = sPO;
//查詢參數：物料號碼
IParameter pMaterial = (IParameter)ifunc.get_Exports("MATERIAL");
pMaterial.Value = sMaterial;
//查詢參數：廠商名稱
IParameter pVendorName = (IParameter)ifunc.get_Exports("VENDORNM");
pVendorName.Value = sVendorName;
//查詢參數：異動類型
IParameter pMvt = (IParameter)ifunc.get_Exports("MVT");
pMvt.Value = sMvt;
//查詢參數：物料文件
IParameter pMtlDoc = (IParameter)ifunc.get_Exports("MTLDOC");
pMtlDoc.Value = sMtlDoc;
//查詢參數：參考文件
IParameter pRefDoc = (IParameter)ifunc.get_Exports("REFDOC");
pRefDoc.Value = sRefDoc;
ifunc.Call();
Tables tables = (Tables)ifunc.Tables;
SAPTableFactoryCtrl.Table ITAB = (SAPTableFactoryCtrl.Table)tables.get_Item("ITAB");
int itabRowCount = ITAB.RowCount;
if (itabRowCount > 0)
{
for (int i = 1; i <= itabRowCount; i++)
{
DataRow dr = sapDataDt.NewRow();
if (i == 1)
{
// dt.Columns.Add("No");
sapDataDt.Columns.Add("輸入日期");
sapDataDt.Columns.Add("輸入時間");
sapDataDt.Columns.Add("採購文件");
sapDataDt.Columns.Add("文件項次");
sapDataDt.Columns.Add("物料文件");
sapDataDt.Columns.Add("異動類型");
sapDataDt.Columns.Add("參考文件");
sapDataDt.Columns.Add("數量");
sapDataDt.Columns.Add("單價");
sapDataDt.Columns.Add("幣別");
sapDataDt.Columns.Add("料號");
sapDataDt.Columns.Add("工單");
sapDataDt.Columns.Add("群組說明");
sapDataDt.Columns.Add("品名");
sapDataDt.Columns.Add("供應商");
sapDataDt.Columns.Add("備註");
}
//dr["No"] = i.ToString();
dr["輸入日期"] = Convert.ToDateTime(ITAB.get_Cell(i, "CPUDT")).ToString("yyyy-MM-dd");
dr["輸入時間"] = Convert.ToDateTime(ITAB.get_Cell(i, "CPUTM")).ToString("HH:mm:ss");
dr["採購文件"] = ITAB.get_Cell(i, "EBELN").ToString();
dr["文件項次"] = ITAB.get_Cell(i, "EBELP").ToString().TrimStart('0');
dr["物料文件"] = ITAB.get_Cell(i, "BELNR").ToString();
dr["異動類型"] = ITAB.get_Cell(i, "BWART").ToString();
dr["參考文件"] = ITAB.get_Cell(i, "LFBNR").ToString();
dr["數量"] = ITAB.get_Cell(i, "MENGE").ToString().TrimEnd('0').TrimEnd('.');
dr["單價"] = ITAB.get_Cell(i, "NETPR").ToString();
dr["幣別"] = ITAB.get_Cell(i, "WAERS").ToString();
dr["料號"] = ITAB.get_Cell(i, "MATNR").ToString().TrimStart('0');
dr["工單"] = ITAB.get_Cell(i, "AUFNR").ToString().TrimStart('0');
dr["群組說明"] = ITAB.get_Cell(i, "TEXT20").ToString();
dr["品名"] = ITAB.get_Cell(i, "TXZ01").ToString();
var vendorName = checkStringLen(ITAB.get_Cell(i, "NAME1").ToString(), 4);
dr["供應商"] = vendorName;
dr["備註"] = ITAB.get_Cell(i, "MD_MEMO").ToString().TrimEnd(':');
sapDataDt.Rows.Add(dr);
}
formatedDT = arrangeExcelLayout(sapDataDt);
gvData.DataSource = formatedDT.DefaultView;
gvData.DataBind();
btnConvert.Visible = true;
}
else
{
lblMsg.Text = "無符合條件的資料";
gvData.Visible = false;
}
}
else
{
lblMsg.Text = "無法連接 sap";
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}