Visual Basic code example for EDI AS2 communication.
Imports nsoftware.IPWorksEDIImports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Partial Class reciever
Inherits System.Web.UI.Page
Protected TextMessageCON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Protected TextMessageDA As SqlDataAdapter = _
New SqlDataAdapter("SELECT TOP 1 MSG_ID, DATE_RECEIVED, INVOICENUM, PURPOSE_CODE, MESSAGE FROM TEXTMESSAGE", TextMessageCON)
Protected TextMessageDS As DataSet = New DataSet
Protected AcknowledgmentCON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Protected AcknowledgmentDA As SqlDataAdapter = _
New SqlDataAdapter("SELECT TOP 1 MSG_ID, DATE_RECEIVED, INVOICENUM, CODE, MESSAGE FROM ACKNOWLEDGMENT", AcknowledgmentCON)
Protected AcknowledgmentDS As DataSet = New DataSet
Protected AddressListCON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=******; Password=********")
Protected AddressListDA As SqlDataAdapter = _
New SqlDataAdapter("SELECT GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE FROM ADDRESSLIST", AddressListCON)
Protected AddressListDS As DataSet = New DataSet
Protected PO_CON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Protected PO_DA As SqlDataAdapter = _
New SqlDataAdapter("SELECT TOP 1 MSG_ID, DATE_RECEIVED, GS_CONTROLNUM, ST_CONTROLNUM, PONUM, PURPOSE_CODE, CURRENCY, ORDER_DATE, VENDORNUM, DIVISION, PO_TYPE, " + _
"VENDOR_ORDERNUM, BUYER, BUYER_PHONE, REVISED_BY, REVISED_PHONE, FACILITY_CONTACT, FACILITY_PHONE, FOB, " + _
"DISCOUNT, DISCOUNT_DAYS, TERMS, ARRIVAL_DATE, NOTES_LABEL, NOTES, BILL_TO_NAME, BILL_TO_GLN, SHIP_TO_NAME, " + _
"SHIP_TO_GLN, VENDOR_NAME, SHIP_TO_ADDRESS1, SHIP_TO_ADDRESS2, SHIP_TO_CITY, SHIP_TO_STATE, SHIP_TO_ZIP, " + _
"SHIP_TO_COUNTRY, FINAL_DELIVERY_LOCATION, AMOUNT, ACKNOWLEDGED, QUANTUS FROM PO", PO_CON)
Protected PO_DS As DataSet = New DataSet
Protected PO_LINE_CON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Protected PO_LINE_DA As SqlDataAdapter = _
New SqlDataAdapter("SELECT TOP 1 LINE_ID, MSG_ID, LINE_NUM, QTY, UNITS, UNIT_PRICE, CUSTOMER_ITEM_NUM, VENDOR_ITEM_NUM, DESCRIPTION, " + _
"AMOUNT, SDQ FROM PO_LINE", PO_LINE_CON)
Protected PO_LINE_DS As DataSet = New DataSet
Protected SDQ_CON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Protected SDQ_DA As SqlDataAdapter = _
New SqlDataAdapter("SELECT TOP 1 LINE_ID, GLN, QTY FROM SDQ", SDQ_CON)
Protected SDQ_DS As DataSet = New DataSet
Protected lstEdiParsed As List(Of String) = New List(Of String)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim as2 As New nsoftware.IPWorksEDI.As2receiver
Dim receiptMessage As String = ""
as2.RuntimeLicense = "*******"
If Not Me.Request.HttpMethod.Equals("POST") Then
Return
End If
Try
' You can generate detailed logs, but make sure first that you have the appropriate
' write permissions.
as2.LogDirectory = Path.Combine(Request.PhysicalApplicationPath, "Logs")
as2.Config("RequireSign=true")
' The first order of business is to parse the incoming headers. This will
' make sure it is an AS2 message, and will also determine the values of
' AS2From and AS2To.
as2.ReadRequest()
Dim myAS2Identifier As String = "*******"
'Check to make sure that this request is intended for you
If (Not as2.AS2To.Equals(myAS2Identifier)) Then
Throw New Exception("The EDI message is meant for " + as2.AS2To + " not for us. [" + myAS2Identifier + "]")
End If
' In a real AS2 application you would now check the values of
' AS2To and look up your trading partner's certificate in a database.
' This demo application uses a simple select statement to load a
' partner certificate. Note that this certificate corresponds to the
' as2sender.pfx used in the client demo.
'Select Case (as2.AS2From)
' Case "*******"
' as2.SignerCert = New Certificate(Path.Combine(Request.PhysicalApplicationPath, "*******.cer"))
' Case "AS2 Test Sending Organization"
' as2.SignerCert = New Certificate(Path.Combine(Request.PhysicalApplicationPath, "*******.cer"))
' Case Else
' 'Alternatively, close the request
' Throw New Exception("Partner unknown: [" + as2.AS2From + "]")
'End Select
If as2.AS2From = "AS2 Test Sending Organization" Then
as2.SignerCert = New Certificate(Path.Combine(Request.PhysicalApplicationPath, "*******.cer"))
Else
as2.SignerCert = New Certificate(Path.Combine(Request.PhysicalApplicationPath, "*******.cer"))
End If
' To sign receipts and/or decrypt incoming transmissions, you will need to
' set your certificate. (Note that by default, you do not need to explicitly
' tell the component to sign or decrypt; per AS2 standards message security is
' at the option of the client.
' For yourself, you will need a certificate with a private key. You can use a PFX
' file for this, or you can use a certificate directly from a Windows system store.
' Note that if you don't know the subject for your certificate, you can use the
' CertMgr component to determine it.
' Note: this is the certificate that corresponds to the as2receiver.cer
' key that is included with the client.
as2.Certificate = New Certificate(CertStoreTypes.cstPFXFile, Path.Combine(Request.PhysicalApplicationPath, _
"*******.pfx"), "*******", "CN=*******")
as2.ParseRequest()
Catch ex As Exception
' Set the unexpected processing error status in the MDN
as2.Config("ProcessingError=true")
receiptMessage = ex.Message
Finally
Try
' If no eerror was encountered, the component will generate a default message
as2.CreateMDNReceipt("", "", receiptMessage)
' close the connection if dealing with an asynch MDN
as2.AckRequest()
If (as2.ReceiptDeliveryOption <> "") Then
Response.Close()
End If
' Send the MDN receipt, synchronously or asynchronously, as requested
as2.SendResponse()
If String.IsNullOrEmpty(as2.EDIData.Data.ToString) = False Then
lstEdiParsed = as2.EDIData.Data.ToString.Split("~").ToList
handlemessage()
End If
Catch ex As Exception
' An error here means that the component was not even able to send an MDN.
Response.StatusCode = 400
Response.Write("ERROR: " + ex.Message)
Response.Flush()
Response.Close()
email("AS2 failed!" + vbCrLf + ex.ToString + vbCrLf + "Chad Stacktrace:" + vbCrLf)
End Try
End Try
End Sub
Protected Sub handlemessage()
Try
Select Case lstEdiParsed.ElementAt(2).ToString.Substring(0, 7)
Case "ST*864*"
TextMessage()
Case "ST*997*"
Acknowledgment()
Case "ST*816*"
AddressList()
Case "ST*850*"
PurchaseOrder()
'Case "ST*820*"
'RemintenceAdvice()
End Select
Catch ex As Exception
email("handlemessage failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub TextMessage()
Dim dr As DataRow
Dim lstParsedBMG As List(Of String) = New List(Of String)
Dim lstParsedREFIV As List(Of String) = New List(Of String)
Dim message As String = ""
Try
MessageDASetup()
lstParsedBMG = lstEdiParsed.ElementAt(3).ToString.Split("*").ToList()
For i As Integer = 0 To lstEdiParsed.Count - 1
message = message + lstEdiParsed.ElementAt(i).ToString + vbCrLf
If lstEdiParsed.ElementAt(i).ToString.Length > 6 Then
If lstEdiParsed.ElementAt(i).ToString.Substring(0, 7) = "REF*IV*" Then
lstParsedREFIV = lstEdiParsed.ElementAt(i).ToString.Split("*").ToList()
End If
End If
Next
dr = TextMessageDS.Tables("TextMessage").NewRow
dr.Item("DATE_RECEIVED") = DateTime.Now
dr.Item("PURPOSE_CODE") = lstParsedBMG.ElementAt(1).ToString
dr.Item("INVOICENUM") = lstParsedREFIV.ElementAt(2).ToString.TrimStart("0")
dr.Item("MESSAGE") = message
TextMessageDS.Tables("TextMessage").Rows.Add(dr)
TextMessageDA.Update(TextMessageDS, "TextMessage")
Catch ex As Exception
email("TextMessage failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub Acknowledgment()
Dim INVS_CON As SqlConnection = New SqlConnection("Data Source=*******; Initial Catalog=*******; User Id=*******; Password=*******")
Dim INVS_DA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM SENT_INVS WHERE MSG_ID = @MSG_ID", INVS_CON)
Dim INVS_DS As DataSet = New DataSet
INVS_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
Dim dr As DataRow
Dim lstParsedAK1 As List(Of String) = New List(Of String)
Dim lstParsedAK5 As List(Of String) = New List(Of String)
Dim lstParsedSentInvsEDI_DATA1 As List(Of String) = New List(Of String)
Dim lstParsedSentInvsEDI_DATA2 As List(Of String) = New List(Of String)
Dim InvoiceSentMSG_ID As Integer
Dim message As String = ""
Try
AcknowledgmentDASetup()
lstParsedAK1 = lstEdiParsed.ElementAt(3).ToString.Split("*").ToList()
InvoiceSentMSG_ID = lstParsedAK1.ElementAt(2) - 100000000
INVS_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = InvoiceSentMSG_ID
INVS_DA.Fill(INVS_DS, "SENT_INVS")
lstParsedSentInvsEDI_DATA1 = INVS_DS.Tables("SENT_INVS").Rows(0).Item("EDI_DATA").ToString.Split("~").ToList()
lstParsedSentInvsEDI_DATA2 = lstParsedSentInvsEDI_DATA1.ElementAt(3).ToString.Split("*").ToList()
For i As Integer = 0 To lstEdiParsed.Count - 1
message = message + lstEdiParsed.ElementAt(i).ToString + vbCrLf
If lstEdiParsed.ElementAt(i).ToString.Length > 3 Then
If lstEdiParsed.ElementAt(i).ToString.Substring(0, 4) = "AK5*" Then
lstParsedAK5 = lstEdiParsed.ElementAt(i).ToString.Split("*").ToList()
End If
End If
Next
dr = AcknowledgmentDS.Tables("Acknowledgment").NewRow
dr.Item("DATE_RECEIVED") = DateTime.Now
dr.Item("INVOICENUM") = lstParsedSentInvsEDI_DATA2.ElementAt(2).ToString
dr.Item("CODE") = lstParsedAK5.ElementAt(1).ToString
dr.Item("MESSAGE") = message
AcknowledgmentDS.Tables("Acknowledgment").Rows.Add(dr)
AcknowledgmentDA.Update(AcknowledgmentDS, "Acknowledgment")
Catch ex As Exception
email("Acknowledgment failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub AddressList()
Dim dr As DataRow
Dim count As Integer
Dim row_count As Integer
Dim lstParsedBHT As List(Of String) = New List(Of String)
Dim lstParsed As List(Of String) = New List(Of String)
Try
lstParsedBHT = lstEdiParsed.ElementAt(3).ToString.Split("*").ToList()
If lstParsedBHT(2).ToString = "04" Then
'email()
'Throw New Exception("Received a Weekly Update Address List from *******")
AddressListDASetup(True)
AddressListUpdate()
Exit Sub
End If
AddressListDASetup(False)
Dim activedate = ""
activedate = lstParsedBHT.ElementAt(4).ToString().Insert(4, "/")
activedate = activedate.Insert(7, "/")
count = 5
row_count = 0
While True
dr = AddressListDS.Tables("AddressList").NewRow
row_count = row_count + 1
dr.Item("GLN_ID") = row_count
dr.Item("Date") = activedate
While True
count = count + 1
lstParsed = lstEdiParsed.ElementAt(count).ToString.Split("*").ToList()
Select Case lstParsed.ElementAt(0).ToString
Case "N1"
If lstParsed.Count > 4 Then
dr.Item("GLN") = lstParsed.ElementAt(4).ToString
End If
dr.Item("NAME") = lstParsed.ElementAt(2).ToString
Case "N3"
dr.Item("ADDRESS1") = lstParsed.ElementAt(1).ToString
If lstParsed.Count > 3 Then
dr.Item("ADDRESS2") = lstParsed.ElementAt(2).ToString
End If
Case "N4"
If lstParsed.Count > 1 Then
dr.Item("CITY") = lstParsed.ElementAt(1).ToString
End If
If lstParsed.Count > 2 Then
dr.Item("STATE") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 3 Then
dr.Item("ZIP") = lstParsed.ElementAt(3).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("COUNTRY") = lstParsed.ElementAt(4).ToString
End If
Case "PER"
dr.Item("CONTACT") = lstParsed.ElementAt(2).ToString
If lstParsed.Count > 4 Then
dr.Item("PHONE") = lstParsed.ElementAt(4).ToString
End If
Case "REF"
Select Case lstParsed.ElementAt(1).ToString
Case "DNS"
dr.Item("DUNS") = lstParsed.ElementAt(2).ToString
Case "ST"
dr.Item("STORENUM") = lstParsed.ElementAt(2).ToString
Case "AEM"
dr.Item("DISTRONUM") = lstParsed.ElementAt(2).ToString
End Select
Case "HL", "SE"
Exit While
End Select
End While
AddressListDS.Tables("AddressList").Rows.Add(dr)
If lstParsed.ElementAt(0).ToString = "SE" Then
Exit While
End If
End While
AddressListDA.Update(AddressListDS, "AddressList")
Catch ex As Exception
email("AddressList failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub AddressListUpdate()
Dim dr As DataRow
Dim count As Integer
Dim row_count As Integer
Dim PrimaryKeys(1) As DataColumn
Dim lstParsedBHT As List(Of String) = New List(Of String)
Dim lstParsed As List(Of String) = New List(Of String)
Try
AddressListDA.SelectCommand.CommandText = "SELECT MAX(GLN_ID) FROM ADDRESSLIST"
AddressListDA.Fill(AddressListDS, "AddressList")
row_count = AddressListDS.Tables("AddressList").Rows(0).Item(0)
AddressListDS.Tables("AddressList").Reset()
'AddressListDS.AcceptChanges()
AddressListDA.SelectCommand.CommandText = "SELECT TOP 1 GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE " + _
"FROM ADDRESSLIST"
AddressListDA.Fill(AddressListDS, "AddressList")
PrimaryKeys(0) = AddressListDS.Tables("AddressList").Columns("GLN_ID")
AddressListDS.Tables("AddressList").PrimaryKey = PrimaryKeys
AddressListDS.Tables("AddressList").Rows(0).Delete()
AddressListDS.AcceptChanges()
lstParsedBHT = lstEdiParsed.ElementAt(3).ToString.Split("*").ToList()
Dim activedate = ""
activedate = lstParsedBHT.ElementAt(4).ToString().Insert(4, "/")
activedate = activedate.Insert(7, "/")
count = 5
While True
dr = AddressListDS.Tables("AddressList").NewRow
dr.Item("DATE") = activedate
While True
count = count + 1
lstParsed = lstEdiParsed.ElementAt(count).ToString.Split("*").ToList()
Select Case lstParsed.ElementAt(0).ToString
Case "N1"
If lstParsed.Count > 4 Then
dr.Item("GLN") = lstParsed.ElementAt(4).ToString
End If
dr.Item("NAME") = lstParsed.ElementAt(2).ToString
Case "N3"
dr.Item("ADDRESS1") = lstParsed.ElementAt(1).ToString
If lstParsed.Count > 3 Then
dr.Item("ADDRESS2") = lstParsed.ElementAt(2).ToString
End If
Case "N4"
If lstParsed.Count > 1 Then
dr.Item("CITY") = lstParsed.ElementAt(1).ToString
End If
If lstParsed.Count > 2 Then
dr.Item("STATE") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 3 Then
dr.Item("ZIP") = lstParsed.ElementAt(3).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("COUNTRY") = lstParsed.ElementAt(4).ToString
End If
Case "PER"
dr.Item("CONTACT") = lstParsed.ElementAt(2).ToString
If lstParsed.Count > 4 Then
dr.Item("PHONE") = lstParsed.ElementAt(4).ToString
End If
Case "REF"
Select Case lstParsed.ElementAt(1).ToString
Case "DNS"
dr.Item("DUNS") = lstParsed.ElementAt(2).ToString
Case "ST"
dr.Item("STORENUM") = lstParsed.ElementAt(2).ToString
Case "AEM"
dr.Item("DISTRONUM") = lstParsed.ElementAt(2).ToString
End Select
Case "ASI"
Select Case lstParsed.ElementAt(2).ToString
Case "001"
AddressListDA.SelectCommand.CommandText = "SELECT GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE " + _
"FROM ADDRESSLIST WHERE GLN = '" + dr.Item("GLN").ToString + "' AND NAME = '" + dr.Item("NAME").ToString + "' AND COUNTRY = '" + dr.Item("COUNTRY").ToString + "'"
If AddressListDA.Fill(AddressListDS, "AddressList") > 0 Then
AddressListDS.Tables("AddressList").Rows(0).Item("GLN") = dr.Item("GLN")
AddressListDS.Tables("AddressList").Rows(0).Item("NAME") = dr.Item("NAME")
AddressListDS.Tables("AddressList").Rows(0).Item("ADDRESS1") = dr.Item("ADDRESS1")
AddressListDS.Tables("AddressList").Rows(0).Item("ADDRESS2") = dr.Item("ADDRESS2")
AddressListDS.Tables("AddressList").Rows(0).Item("CITY") = dr.Item("CITY")
AddressListDS.Tables("AddressList").Rows(0).Item("STATE") = dr.Item("STATE")
AddressListDS.Tables("AddressList").Rows(0).Item("ZIP") = dr.Item("ZIP")
AddressListDS.Tables("AddressList").Rows(0).Item("COUNTRY") = dr.Item("COUNTRY")
AddressListDS.Tables("AddressList").Rows(0).Item("CONTACT") = dr.Item("CONTACT")
AddressListDS.Tables("AddressList").Rows(0).Item("PHONE") = dr.Item("PHONE")
AddressListDS.Tables("AddressList").Rows(0).Item("DUNS") = dr.Item("DUNS")
AddressListDS.Tables("AddressList").Rows(0).Item("STORENUM") = dr.Item("STORENUM")
AddressListDS.Tables("AddressList").Rows(0).Item("DISTRONUM") = dr.Item("DISTRONUM")
AddressListDS.Tables("AddressList").Rows(0).Item("DATE") = dr.Item("DATE")
Else
row_count = row_count + 1
dr.Item("GLN_ID") = row_count
AddressListDS.Tables("AddressList").Rows.Add(dr)
End If
AddressListDA.Update(AddressListDS, "AddressList")
AddressListDS.Tables("AddressList").Rows(0).Delete()
AddressListDS.AcceptChanges()
Case "002"
AddressListDA.SelectCommand.CommandText = "SELECT GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE " + _
"FROM ADDRESSLIST WHERE GLN = '" + dr.Item("GLN").ToString + "' AND NAME = '" + dr.Item("NAME").ToString + "' AND COUNTRY = '" + dr.Item("COUNTRY").ToString + "'"
If AddressListDA.Fill(AddressListDS, "AddressList") > 0 Then
AddressListDS.Tables("AddressList").Rows(0).Delete()
AddressListDA.Update(AddressListDS, "AddressList")
AddressListDS.AcceptChanges()
End If
Case "021"
row_count = row_count + 1
dr.Item("GLN_ID") = row_count
AddressListDS.Tables("AddressList").Rows.Add(dr)
AddressListDA.Update(AddressListDS, "AddressList")
AddressListDS.Tables("AddressList").Rows(0).Delete()
AddressListDS.AcceptChanges()
End Select
Case "HL", "SE"
Exit While
End Select
End While
lstParsed = lstEdiParsed.ElementAt(count + 1).ToString.Split("*").ToList()
If lstParsed.ElementAt(0).ToString = "GE" Then
Exit While
End If
End While
Catch ex As Exception
email("AddressListUpdate failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub PurchaseOrder()
Dim dr As DataRow
Dim dr_line As DataRow
Dim dr_sdq_line As DataRow
Dim count As Integer
Dim SDQ_count As Integer
Dim strMessage As String
Dim lstParsed As List(Of String) = New List(Of String)
Dim gs_controlnum As String
Try
PO_DASetup()
PO_LINE_DASetup()
SDQ_DASetup()
count = 0
While True
dr = PO_DS.Tables("PO").NewRow
dr.Item("DATE_RECEIVED") = DateTime.Now
strMessage = ""
While True
count = count + 1
lstParsed = lstEdiParsed.ElementAt(count).ToString.Split("*").ToList()
Select Case lstParsed.ElementAt(0).ToString
Case "GS"
gs_controlnum = lstParsed.ElementAt(6).ToString
Case "ST"
dr.Item("GS_CONTROLNUM") = gs_controlnum
dr.Item("ST_CONTROLNUM") = lstParsed.ElementAt(2).ToString
Case "BEG"
dr.Item("PONUM") = lstParsed.ElementAt(3).ToString
'sendAcknowledgment(lstParsed.ElementAt(3).ToString)
dr.Item("PURPOSE_CODE") = lstParsed.ElementAt(1).ToString
Dim orderdate = ""
orderdate = lstParsed.ElementAt(5).ToString.Insert(4, "/")
orderdate = orderdate.Insert(7, "/")
dr.Item("ORDER_DATE") = orderdate
Case "CUR"
dr.Item("CURRENCY") = lstParsed.ElementAt(2).ToString
PO_DS.Tables("PO").Rows.Add(dr)
PO_DA.Update(PO_DS, "PO")
Case "REF"
Select Case lstParsed.ElementAt(1).ToString
Case "IA"
dr.Item("VENDORNUM") = lstParsed.ElementAt(2).ToString
Case "19"
dr.Item("DIVISION") = lstParsed.ElementAt(2).ToString
Case "MR"
dr.Item("PO_TYPE") = lstParsed.ElementAt(2).ToString
Case "VN"
dr.Item("VENDOR_ORDERNUM") = lstParsed.ElementAt(2).ToString
Case "AFN"
If dr.Item("PO_TYPE") = "ZUS2" And (lstParsed.ElementAt(2).ToString = "10" Or _
lstParsed.ElementAt(2).ToString = "11" Or _
lstParsed.ElementAt(2).ToString = "12") Then
dr.Item("PO_TYPE") = "ZUS2_S"
End If
End Select
Case "PER"
Select Case lstParsed.ElementAt(1).ToString
Case "BD"
If lstParsed.Count > 2 Then
dr.Item("BUYER") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("BUYER_PHONE") = lstParsed.ElementAt(4).ToString
End If
Case "CB"
If lstParsed.Count > 2 Then
dr.Item("REVISED_BY") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("REVISED_PHONE") = lstParsed.ElementAt(4).ToString
End If
Case "AJ"
If lstParsed.Count > 2 Then
dr.Item("FACILITY_CONTACT") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("FACILITY_PHONE") = lstParsed.ElementAt(4).ToString
End If
End Select
Case "FOB"
dr.Item("FOB") = lstParsed.ElementAt(1).ToString
Case "ITD"
If lstParsed.Count > 3 Then
dr.Item("DISCOUNT") = lstParsed.ElementAt(3).ToString
End If
If lstParsed.Count > 5 Then
dr.Item("DISCOUNT_DAYS") = lstParsed.ElementAt(5).ToString
End If
If lstParsed.Count > 12 Then
dr.Item("TERMS") = lstParsed.ElementAt(12).ToString
End If
Case "DTM"
Dim orderdate = ""
orderdate = lstParsed.ElementAt(2).ToString.Insert(4, "/")
orderdate = orderdate.Insert(7, "/")
dr.Item("ARRIVAL_DATE") = orderdate
Case "N9"
dr.Item("NOTES_LABEL") = lstParsed.ElementAt(2).ToString
Case "MTX"
strMessage = strMessage + lstParsed.ElementAt(2).ToString + vbCrLf
dr.Item("NOTES") = strMessage
Case "N1"
Select Case lstParsed.ElementAt(1).ToString
Case "BT"
dr.Item("BILL_TO_NAME") = lstParsed.ElementAt(2).ToString
dr.Item("BILL_TO_GLN") = lstParsed.ElementAt(4).ToString
Case "ST"
dr.Item("SHIP_TO_NAME") = lstParsed.ElementAt(2).ToString
If lstParsed.Count > 4 Then
dr.Item("SHIP_TO_GLN") = lstParsed.ElementAt(4).ToString
End If
Case "SU"
dr.Item("VENDOR_NAME") = lstParsed.ElementAt(2).ToString
Case "MQ"
dr.Item("FINAL_DELIVERY_LOCATION") = lstParsed.ElementAt(2).ToString
End Select
Case "N3"
If lstParsed.Count > 1 Then
dr.Item("SHIP_TO_ADDRESS1") = lstParsed.ElementAt(1).ToString
End If
If lstParsed.Count > 2 Then
dr.Item("SHIP_TO_ADDRESS2") = lstParsed.ElementAt(2).ToString
End If
Case "N4"
If lstParsed.Count > 1 Then
dr.Item("SHIP_TO_CITY") = lstParsed.ElementAt(1).ToString
End If
If lstParsed.Count > 2 Then
dr.Item("SHIP_TO_STATE") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 3 Then
dr.Item("SHIP_TO_ZIP") = lstParsed.ElementAt(3).ToString
End If
If lstParsed.Count > 4 Then
dr.Item("SHIP_TO_COUNTRY") = lstParsed.ElementAt(4).ToString
End If
Case "AMT"
Select Case lstParsed.ElementAt(1).ToString
Case "TT"
dr.Item("AMOUNT") = lstParsed.ElementAt(2).ToString
Case "1"
If Not dr_line.IsNull("LINE_ID") Then
dr_line.Item("AMOUNT") = lstParsed.ElementAt(2).ToString
PO_LINE_DA.Update(PO_LINE_DS, "PO_LINE")
End If
End Select
Case "PO1"
dr_line = PO_LINE_DS.Tables("PO_LINE").NewRow
dr_line.Item("MSG_ID") = dr.Item("MSG_ID").ToString
PO_LINE_DS.Tables("PO_LINE").Rows.Add(dr_line)
PO_LINE_DA.Update(PO_LINE_DS, "PO_LINE")
If lstParsed.Count > 1 Then
dr_line.Item("LINE_NUM") = lstParsed.ElementAt(1).ToString
End If
If lstParsed.Count > 2 Then
dr_line.Item("QTY") = lstParsed.ElementAt(2).ToString
End If
If lstParsed.Count > 3 Then
dr_line.Item("UNITS") = lstParsed.ElementAt(3).ToString
End If
If lstParsed.Count > 4 Then
dr_line.Item("UNIT_PRICE") = lstParsed.ElementAt(4).ToString
End If
If lstParsed.Count > 7 Then
dr_line.Item("CUSTOMER_ITEM_NUM") = lstParsed.ElementAt(7).ToString
End If
If lstParsed.Count > 9 Then
dr_line.Item("VENDOR_ITEM_NUM") = lstParsed.ElementAt(9).ToString
End If
Case "PID"
If Not dr_line.IsNull("LINE_ID") Then
dr_line.Item("DESCRIPTION") = lstParsed.ElementAt(5).ToString
PO_LINE_DA.Update(PO_LINE_DS, "PO_LINE")
End If
Case "SDQ"
If Not dr_line.IsNull("LINE_ID") Then
dr_line.Item("SDQ") = 1
SDQ_count = 3
While True
dr_sdq_line = SDQ_DS.Tables("SDQ").NewRow
dr_sdq_line.Item("LINE_ID") = dr_line.Item("LINE_ID").ToString
dr_sdq_line.Item("GLN") = lstParsed.ElementAt(SDQ_count).ToString
SDQ_count = SDQ_count + 1
dr_sdq_line.Item("QTY") = lstParsed.ElementAt(SDQ_count).ToString
SDQ_DS.Tables("SDQ").Rows.Add(dr_sdq_line)
SDQ_DA.Update(SDQ_DS, "SDQ")
If lstParsed.Count = SDQ_count + 1 Then
Exit While
Else
SDQ_count = SDQ_count + 1
End If
End While
PO_LINE_DA.Update(PO_LINE_DS, "PO_LINE")
End If
Case "SE"
Exit While
End Select
End While
lstParsed = lstEdiParsed.ElementAt(count + 1).ToString.Split("*").ToList()
If lstParsed.ElementAt(0).ToString = "GE" Then
Exit While
End If
End While
PO_LINE_DA.Update(PO_LINE_DS, "PO_LINE")
PO_DA.Update(PO_DS, "PO")
Console.WriteLine("")
Catch ex As Exception
email("PurchaseOrder failed!" + vbCrLf + ex.ToString)
End Try
End Sub
'Protected Sub RemintenceAdvice()
' Return
'End Sub
Protected Sub MessageDASetup()
'Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Try
'Insert command
DAInsertCmd = New SqlCommand("INSERT INTO TEXTMESSAGE VALUES (@DATE_RECEIVED, @INVOICENUM, @PURPOSE_CODE, @MESSAGE)", TextMessageDA.SelectCommand.Connection)
DAInsertCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
DAInsertCmd.Parameters.Add("@DATE_RECEIVED", SqlDbType.DateTime, 8, "DATE_RECEIVED")
DAInsertCmd.Parameters.Add("@PURPOSE_CODE", SqlDbType.VarChar, 2, "PURPOSE_CODE")
DAInsertCmd.Parameters.Add("@MESSAGE", SqlDbType.Text, -1, "MESSAGE")
TextMessageDA.InsertCommand = DAInsertCmd
TextMessageDA.Fill(TextMessageDS, "TextMessage")
'PrimaryKeys(0) = TextMessageDS.Tables("TextMessage").Columns("MSG_ID")
'TextMessageDS.Tables("TextMessage").PrimaryKey = PrimaryKeys
TextMessageDS.Tables("TextMessage").Rows(0).Delete()
TextMessageDS.AcceptChanges()
Catch ex As Exception
email("MessageDASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub AcknowledgmentDASetup()
'Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Try
'Insert command
DAInsertCmd = New SqlCommand("INSERT INTO ACKNOWLEDGMENT VALUES (@DATE_RECEIVED, @INVOICENUM, @CODE, @MESSAGE)", AcknowledgmentDA.SelectCommand.Connection)
DAInsertCmd.Parameters.Add("@DATE_RECEIVED", SqlDbType.DateTime, 8, "DATE_RECEIVED")
DAInsertCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
DAInsertCmd.Parameters.Add("@CODE", SqlDbType.Char, 1, "CODE")
DAInsertCmd.Parameters.Add("@MESSAGE", SqlDbType.Text, -1, "MESSAGE")
AcknowledgmentDA.InsertCommand = DAInsertCmd
AcknowledgmentDA.Fill(AcknowledgmentDS, "Acknowledgment")
'PrimaryKeys(0) = AcknowledgmentDS.Tables("Acknowledgment").Columns("MSG_ID")
'AcknowledgmentDS.Tables("Acknowledgment").PrimaryKey = PrimaryKeys
AcknowledgmentDS.Tables("Acknowledgment").Rows(0).Delete()
AcknowledgmentDS.AcceptChanges()
Catch ex As Exception
email("AcknowledgmentDASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub AddressListDASetup(ByVal update As Boolean)
Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Dim DADeleteCmd As SqlCommand
Try
'Insert command
DAInsertCmd = New SqlCommand("INSERT INTO ADDRESSLIST VALUES (@GLN_ID, @GLN, @NAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE, " + _
"@ZIP, @COUNTRY, @CONTACT, @PHONE, @DUNS, @STORENUM, @DISTRONUM, @DATE)", AddressListDA.SelectCommand.Connection)
DAInsertCmd.Parameters.Add("@GLN_ID", SqlDbType.Int, 4, "GLN_ID")
DAInsertCmd.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
DAInsertCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 60, "NAME")
DAInsertCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 55, "ADDRESS1")
DAInsertCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 55, "ADDRESS2")
DAInsertCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
DAInsertCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 2, "STATE")
DAInsertCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 15, "ZIP")
DAInsertCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 3, "COUNTRY")
DAInsertCmd.Parameters.Add("@CONTACT", SqlDbType.VarChar, 60, "CONTACT")
DAInsertCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 256, "PHONE")
DAInsertCmd.Parameters.Add("@DUNS", SqlDbType.VarChar, 50, "DUNS")
DAInsertCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 50, "STORENUM")
DAInsertCmd.Parameters.Add("@DISTRONUM", SqlDbType.VarChar, 50, "DISTRONUM")
DAInsertCmd.Parameters.Add("@DATE", SqlDbType.Date, 3, "DATE")
AddressListDA.InsertCommand = DAInsertCmd
'Update command
DAUpdateCmd = New SqlCommand("UPDATE ADDRESSLIST SET NAME = @NAME, ADDRESS1 = @ADDRESS1, ADDRESS2 = @ADDRESS2, " + _
"CITY = @CITY, STATE = @STATE, ZIP = @ZIP, COUNTRY = @COUNTRY, CONTACT = @CONTACT, PHONE = @PHONE, DUNS = @DUNS, " + _
"STORENUM = @STORENUM, DISTRONUM = @DISTRONUM, DATE = @DATE WHERE GLN_ID = @GLN_ID AND GLN = @GLN and NAME = @NAME", AddressListDA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@GLN_ID", SqlDbType.Int, 4, "GLN_ID")
DAUpdateCmd.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
DAUpdateCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 60, "NAME")
DAUpdateCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 55, "ADDRESS1")
DAUpdateCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 55, "ADDRESS2")
DAUpdateCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
DAUpdateCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 2, "STATE")
DAUpdateCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 15, "ZIP")
DAUpdateCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 3, "COUNTRY")
DAUpdateCmd.Parameters.Add("@CONTACT", SqlDbType.VarChar, 60, "CONTACT")
DAUpdateCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 256, "PHONE")
DAUpdateCmd.Parameters.Add("@DUNS", SqlDbType.VarChar, 50, "DUNS")
DAUpdateCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 50, "STORENUM")
DAUpdateCmd.Parameters.Add("@DISTRONUM", SqlDbType.VarChar, 50, "DISTRONUM")
DAUpdateCmd.Parameters.Add("@DATE", SqlDbType.Date, 3, "DATE")
AddressListDA.UpdateCommand = DAUpdateCmd
'Delete command
DADeleteCmd = New SqlCommand("DELETE FROM ADDRESSLIST WHERE GLN_ID = @GLN_ID AND GLN = @GLN AND NAME = @NAME", AddressListDA.SelectCommand.Connection)
DADeleteCmd.Parameters.Add("@GLN_ID", SqlDbType.Int, 4, "GLN_ID")
DADeleteCmd.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
DADeleteCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 60, "NAME")
AddressListDA.DeleteCommand = DADeleteCmd
If Not update Then
AddressListDA.Fill(AddressListDS, "AddressList")
'AddressListDA.ContinueUpdateOnError = True
PrimaryKeys(0) = AddressListDS.Tables("AddressList").Columns("GLN_ID")
AddressListDS.Tables("AddressList").PrimaryKey = PrimaryKeys
For i As Integer = 0 To AddressListDS.Tables("AddressList").Rows.Count - 1
AddressListDS.Tables("AddressList").Rows(i).Delete()
Next
AddressListDA.Update(AddressListDS, "AddressList")
AddressListDS.AcceptChanges()
End If
Catch ex As Exception
email("AddressListDASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub PO_DASetup()
Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
'Insert command
'DAInsertCmd = New SqlCommand("INSERT INTO PO VALUES (@CONTROLNUM, @PONUM, @PURPOSE_CODE, @CURRENCY, @ORDER_DATE, @VENDORNUM, " + _
' "@DIVISION, @PO_TYPE, @VENDOR_ORDERNUM, @BUYER, @BUYER_PHONE, @REVISED_BY, @REVISED_PHONE, @FACILITY_CONTACT, " + _
' "@FACILITY_PHONE, @FOB, @DISCOUNT, @DISCOUNT_DAYS, @TERMS, @ARRIVAL_DATE, @NOTES_LABEL, @NOTES, @BILL_TO_NAME, " + _
' "@BILL_TO_GLN, @SHIP_TO_NAME, @SHIP_TO_GLN, @VENDOR_NAME, @SHIP_TO_ADDRESS1, @SHIP_TO_ADDRESS2, @SHIP_TO_CITY, " + _
' "@SHIP_TO_STATE, @SHIP_TO_ZIP, @SHIP_TO_COUNTRY, @FINAL_DELIVERY_LOCATION, @AMOUNT)", PO_DA.SelectCommand.Connection)
DAInsertCmd = New SqlCommand("InsertMSG_ID", PO_DA.SelectCommand.Connection)
DAInsertCmd.CommandType = CommandType.StoredProcedure
DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAInsertCmd.Parameters("@MSG_ID").Direction = ParameterDirection.Output
DAInsertCmd.Parameters.Add("@DATE_RECEIVED", SqlDbType.DateTime, 8, "DATE_RECEIVED")
DAInsertCmd.Parameters.Add("@GS_CONTROLNUM", SqlDbType.VarChar, 9, "GS_CONTROLNUM")
DAInsertCmd.Parameters.Add("@ST_CONTROLNUM", SqlDbType.VarChar, 9, "ST_CONTROLNUM")
DAInsertCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
DAInsertCmd.Parameters.Add("@PURPOSE_CODE", SqlDbType.VarChar, 2, "PURPOSE_CODE")
DAInsertCmd.Parameters.Add("@CURRENCY", SqlDbType.VarChar, 3, "CURRENCY")
DAInsertCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
DAInsertCmd.Parameters.Add("@VENDORNUM", SqlDbType.VarChar, 50, "VENDORNUM")
DAInsertCmd.Parameters.Add("@DIVISION", SqlDbType.VarChar, 50, "DIVISION")
DAInsertCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
DAInsertCmd.Parameters.Add("@VENDOR_ORDERNUM", SqlDbType.VarChar, 50, "VENDOR_ORDERNUM")
DAInsertCmd.Parameters.Add("@BUYER", SqlDbType.VarChar, 60, "BUYER")
DAInsertCmd.Parameters.Add("@BUYER_PHONE", SqlDbType.VarChar, 256, "BUYER_PHONE")
DAInsertCmd.Parameters.Add("@REVISED_BY", SqlDbType.VarChar, 60, "REVISED_BY")
DAInsertCmd.Parameters.Add("@REVISED_PHONE", SqlDbType.VarChar, 256, "REVISED_PHONE")
DAInsertCmd.Parameters.Add("@FACILITY_CONTACT", SqlDbType.VarChar, 60, "FACILITY_CONTACT")
DAInsertCmd.Parameters.Add("@FACILITY_PHONE", SqlDbType.VarChar, 256, "FACILITY_PHONE")
DAInsertCmd.Parameters.Add("@FOB", SqlDbType.VarChar, 2, "FOB")
DAInsertCmd.Parameters.Add("@DISCOUNT", SqlDbType.VarChar, 6, "DISCOUNT")
DAInsertCmd.Parameters.Add("@DISCOUNT_DAYS", SqlDbType.VarChar, 3, "DISCOUNT_DAYS")
DAInsertCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
DAInsertCmd.Parameters.Add("@ARRIVAL_DATE", SqlDbType.Date, 3, "ARRIVAL_DATE")
DAInsertCmd.Parameters.Add("@NOTES_LABEL", SqlDbType.VarChar, 50, "NOTES_LABEL")
DAInsertCmd.Parameters.Add("@NOTES", SqlDbType.Text, -1, "NOTES")
DAInsertCmd.Parameters.Add("@BILL_TO_NAME", SqlDbType.VarChar, 60, "BILL_TO_NAME")
DAInsertCmd.Parameters.Add("@BILL_TO_GLN", SqlDbType.VarChar, 80, "BILL_TO_GLN")
DAInsertCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_NAME")
DAInsertCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
DAInsertCmd.Parameters.Add("@VENDOR_NAME", SqlDbType.VarChar, 60, "VENDOR_NAME")
DAInsertCmd.Parameters.Add("@SHIP_TO_ADDRESS1", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS1")
DAInsertCmd.Parameters.Add("@SHIP_TO_ADDRESS2", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS2")
DAInsertCmd.Parameters.Add("@SHIP_TO_CITY", SqlDbType.VarChar, 30, "SHIP_TO_CITY")
DAInsertCmd.Parameters.Add("@SHIP_TO_STATE", SqlDbType.VarChar, 2, "SHIP_TO_STATE")
DAInsertCmd.Parameters.Add("@SHIP_TO_ZIP", SqlDbType.VarChar, 15, "SHIP_TO_ZIP")
DAInsertCmd.Parameters.Add("@SHIP_TO_COUNTRY", SqlDbType.VarChar, 3, "SHIP_TO_COUNTRY")
DAInsertCmd.Parameters.Add("@FINAL_DELIVERY_LOCATION", SqlDbType.VarChar, 60, "FINAL_DELIVERY_LOCATION")
DAInsertCmd.Parameters.Add("@AMOUNT", SqlDbType.VarChar, 18, "AMOUNT")
DAInsertCmd.Parameters.Add("@ACKNOWLEDGED", SqlDbType.Bit, 1, "ACKNOWLEDGED")
DAInsertCmd.Parameters.Add("@QUANTUS", SqlDbType.Bit, 1, "QUANTUS")
PO_DA.InsertCommand = DAInsertCmd
'Update command
DAUpdateCmd = New SqlCommand("UPDATE PO SET DATE_RECEIVED = @DATE_RECEIVED, GS_CONTROLNUM = @GS_CONTROLNUM, ST_CONTROLNUM = @ST_CONTROLNUM, PONUM = @PONUM, PURPOSE_CODE = @PURPOSE_CODE, CURRENCY = @CURRENCY, ORDER_DATE = @ORDER_DATE, VENDORNUM = @VENDORNUM, " + _
"DIVISION = @DIVISION, PO_TYPE = @PO_TYPE, VENDOR_ORDERNUM = @VENDOR_ORDERNUM, BUYER = @BUYER, BUYER_PHONE = @BUYER_PHONE, REVISED_BY = @REVISED_BY, REVISED_PHONE = @REVISED_PHONE, FACILITY_CONTACT = @FACILITY_CONTACT, " + _
"FACILITY_PHONE = @FACILITY_PHONE, FOB = @FOB, DISCOUNT = @DISCOUNT, DISCOUNT_DAYS = @DISCOUNT_DAYS, TERMS = @TERMS, ARRIVAL_DATE = @ARRIVAL_DATE, NOTES_LABEL = @NOTES_LABEL, NOTES = @NOTES, BILL_TO_NAME = @BILL_TO_NAME, " + _
"BILL_TO_GLN = @BILL_TO_GLN, SHIP_TO_NAME = @SHIP_TO_NAME, SHIP_TO_GLN = @SHIP_TO_GLN, VENDOR_NAME = @VENDOR_NAME, SHIP_TO_ADDRESS1 = @SHIP_TO_ADDRESS1, SHIP_TO_ADDRESS2 = @SHIP_TO_ADDRESS2, SHIP_TO_CITY = @SHIP_TO_CITY, " + _
"SHIP_TO_STATE = @SHIP_TO_STATE, SHIP_TO_ZIP = @SHIP_TO_ZIP, SHIP_TO_COUNTRY = @SHIP_TO_COUNTRY, FINAL_DELIVERY_LOCATION = @FINAL_DELIVERY_LOCATION, AMOUNT = @AMOUNT, ACKNOWLEDGED = @ACKNOWLEDGED, QUANTUS = @QUANTUS WHERE MSG_ID = @MSG_ID", AddressListDA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAUpdateCmd.Parameters.Add("@DATE_RECEIVED", SqlDbType.DateTime, 8, "DATE_RECEIVED")
DAUpdateCmd.Parameters.Add("@GS_CONTROLNUM", SqlDbType.VarChar, 9, "GS_CONTROLNUM")
DAUpdateCmd.Parameters.Add("@ST_CONTROLNUM", SqlDbType.VarChar, 9, "ST_CONTROLNUM")
DAUpdateCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
DAUpdateCmd.Parameters.Add("@PURPOSE_CODE", SqlDbType.VarChar, 2, "PURPOSE_CODE")
DAUpdateCmd.Parameters.Add("@CURRENCY", SqlDbType.VarChar, 3, "CURRENCY")
DAUpdateCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
DAUpdateCmd.Parameters.Add("@VENDORNUM", SqlDbType.VarChar, 50, "VENDORNUM")
DAUpdateCmd.Parameters.Add("@DIVISION", SqlDbType.VarChar, 50, "DIVISION")
DAUpdateCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
DAUpdateCmd.Parameters.Add("@VENDOR_ORDERNUM", SqlDbType.VarChar, 50, "VENDOR_ORDERNUM")
DAUpdateCmd.Parameters.Add("@BUYER", SqlDbType.VarChar, 60, "BUYER")
DAUpdateCmd.Parameters.Add("@BUYER_PHONE", SqlDbType.VarChar, 256, "BUYER_PHONE")
DAUpdateCmd.Parameters.Add("@REVISED_BY", SqlDbType.VarChar, 60, "REVISED_BY")
DAUpdateCmd.Parameters.Add("@REVISED_PHONE", SqlDbType.VarChar, 256, "REVISED_PHONE")
DAUpdateCmd.Parameters.Add("@FACILITY_CONTACT", SqlDbType.VarChar, 60, "FACILITY_CONTACT")
DAUpdateCmd.Parameters.Add("@FACILITY_PHONE", SqlDbType.VarChar, 256, "FACILITY_PHONE")
DAUpdateCmd.Parameters.Add("@FOB", SqlDbType.VarChar, 2, "FOB")
DAUpdateCmd.Parameters.Add("@DISCOUNT", SqlDbType.VarChar, 6, "DISCOUNT")
DAUpdateCmd.Parameters.Add("@DISCOUNT_DAYS", SqlDbType.VarChar, 3, "DISCOUNT_DAYS")
DAUpdateCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
DAUpdateCmd.Parameters.Add("@ARRIVAL_DATE", SqlDbType.Date, 3, "ARRIVAL_DATE")
DAUpdateCmd.Parameters.Add("@NOTES_LABEL", SqlDbType.VarChar, 50, "NOTES_LABEL")
DAUpdateCmd.Parameters.Add("@NOTES", SqlDbType.Text, -1, "NOTES")
DAUpdateCmd.Parameters.Add("@BILL_TO_NAME", SqlDbType.VarChar, 60, "BILL_TO_NAME")
DAUpdateCmd.Parameters.Add("@BILL_TO_GLN", SqlDbType.VarChar, 80, "BILL_TO_GLN")
DAUpdateCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_NAME")
DAUpdateCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
DAUpdateCmd.Parameters.Add("@VENDOR_NAME", SqlDbType.VarChar, 60, "VENDOR_NAME")
DAUpdateCmd.Parameters.Add("@SHIP_TO_ADDRESS1", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS1")
DAUpdateCmd.Parameters.Add("@SHIP_TO_ADDRESS2", SqlDbType.VarChar, 55, "SHIP_TO_ADDRESS2")
DAUpdateCmd.Parameters.Add("@SHIP_TO_CITY", SqlDbType.VarChar, 30, "SHIP_TO_CITY")
DAUpdateCmd.Parameters.Add("@SHIP_TO_STATE", SqlDbType.VarChar, 2, "SHIP_TO_STATE")
DAUpdateCmd.Parameters.Add("@SHIP_TO_ZIP", SqlDbType.VarChar, 15, "SHIP_TO_ZIP")
DAUpdateCmd.Parameters.Add("@SHIP_TO_COUNTRY", SqlDbType.VarChar, 3, "SHIP_TO_COUNTRY")
DAUpdateCmd.Parameters.Add("@FINAL_DELIVERY_LOCATION", SqlDbType.VarChar, 60, "FINAL_DELIVERY_LOCATION")
DAUpdateCmd.Parameters.Add("@AMOUNT", SqlDbType.VarChar, 18, "AMOUNT")
DAUpdateCmd.Parameters.Add("@ACKNOWLEDGED", SqlDbType.Bit, 1, "ACKNOWLEDGED")
DAUpdateCmd.Parameters.Add("@QUANTUS", SqlDbType.Bit, 1, "QUANTUS")
PO_DA.UpdateCommand = DAUpdateCmd
PO_DA.Fill(PO_DS, "PO")
'PrimaryKeys(0) = PO_DS.Tables("PO").Columns("MSG_ID")
'PO_DS.Tables("PO").PrimaryKey = PrimaryKeys
PO_DS.Tables("PO").Rows(0).Delete()
PO_DS.AcceptChanges()
Catch ex As Exception
email("PO_DASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub PO_LINE_DASetup()
Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
'Insert command
'DAInsertCmd = New SqlCommand("INSERT INTO PO_LINE VALUES (@MSG_ID, @LINE_NUM, @QTY, @UNITS, @UNIT_PRICE, @CUSTOMER_ITEM_NUM, " + _
' "@VENDOR_ITEM_NUM, @DESCRIPTION, @AMOUNT, @SDQ)", PO_LINE_DA.SelectCommand.Connection)
DAInsertCmd = New SqlCommand("InsertLINE_ID", PO_LINE_DA.SelectCommand.Connection)
DAInsertCmd.CommandType = CommandType.StoredProcedure
DAInsertCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
DAInsertCmd.Parameters("@LINE_ID").Direction = ParameterDirection.Output
DAInsertCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAInsertCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
DAInsertCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
DAInsertCmd.Parameters.Add("@UNITS", SqlDbType.VarChar, 2, "UNITS")
DAInsertCmd.Parameters.Add("@UNIT_PRICE", SqlDbType.VarChar, 20, "UNIT_PRICE")
DAInsertCmd.Parameters.Add("@CUSTOMER_ITEM_NUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEM_NUM")
DAInsertCmd.Parameters.Add("@VENDOR_ITEM_NUM", SqlDbType.VarChar, 48, "VENDOR_ITEM_NUM")
DAInsertCmd.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar, 80, "DESCRIPTION")
DAInsertCmd.Parameters.Add("@AMOUNT", SqlDbType.VarChar, 18, "AMOUNT")
DAInsertCmd.Parameters.Add("@SDQ", SqlDbType.Bit, 1, "SDQ")
PO_LINE_DA.InsertCommand = DAInsertCmd
'Update command
DAUpdateCmd = New SqlCommand("UPDATE PO_LINE SET MSG_ID = @MSG_ID, LINE_NUM = @LINE_NUM, QTY = @QTY, UNITS = @UNITS, UNIT_PRICE = @UNIT_PRICE, " + _
"CUSTOMER_ITEM_NUM = @CUSTOMER_ITEM_NUM, VENDOR_ITEM_NUM = @VENDOR_ITEM_NUM, DESCRIPTION = @DESCRIPTION, AMOUNT = @AMOUNT, SDQ = @SDQ " + _
"WHERE LINE_ID = @LINE_ID", PO_LINE_DA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAUpdateCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
DAUpdateCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
DAUpdateCmd.Parameters.Add("@UNITS", SqlDbType.VarChar, 2, "UNITS")
DAUpdateCmd.Parameters.Add("@UNIT_PRICE", SqlDbType.VarChar, 20, "UNIT_PRICE")
DAUpdateCmd.Parameters.Add("@CUSTOMER_ITEM_NUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEM_NUM")
DAUpdateCmd.Parameters.Add("@VENDOR_ITEM_NUM", SqlDbType.VarChar, 48, "VENDOR_ITEM_NUM")
DAUpdateCmd.Parameters.Add("@DESCRIPTION", SqlDbType.VarChar, 80, "DESCRIPTION")
DAUpdateCmd.Parameters.Add("@AMOUNT", SqlDbType.VarChar, 18, "AMOUNT")
DAUpdateCmd.Parameters.Add("@SDQ", SqlDbType.Bit, 1, "SDQ")
PO_LINE_DA.UpdateCommand = DAUpdateCmd
PO_LINE_DA.Fill(PO_LINE_DS, "PO_LINE")
'PrimaryKeys(0) = PO_LINE_DS.Tables("PO_LINE").Columns("LINE_ID")
'PO_LINE_DS.Tables("PO_LINE").PrimaryKey = PrimaryKeys
PO_LINE_DS.Tables("PO_LINE").Rows(0).Delete()
PO_LINE_DS.AcceptChanges()
Catch ex As Exception
email("PO_LINE_DASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Protected Sub SDQ_DASetup()
'Dim PrimaryKeys(1) As DataColumn
Dim DAInsertCmd As SqlCommand
Try
'Insert command
DAInsertCmd = New SqlCommand("INSERT INTO SDQ VALUES (@LINE_ID, @GLN, @QTY)", SDQ_DA.SelectCommand.Connection)
DAInsertCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
DAInsertCmd.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
DAInsertCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
SDQ_DA.InsertCommand = DAInsertCmd
SDQ_DA.Fill(SDQ_DS, "SDQ")
'PrimaryKeys(0) = AcknowledgmentDS.Tables("Acknowledgment").Columns("MSG_ID")
'AcknowledgmentDS.Tables("Acknowledgment").PrimaryKey = PrimaryKeys
SDQ_DS.Tables("SDQ").Rows(0).Delete()
SDQ_DS.AcceptChanges()
Catch ex As Exception
email("SDQ_DASetup failed!" + vbCrLf + ex.ToString)
End Try
End Sub
Sub email(ByVal EmailBody As String)
Dim MailObj As New System.Net.Mail.SmtpClient
Dim msgTo As String = "*******@*******.com"
Dim msgFrom As String = "aspnet@*******.*******.com"
Dim msgSubject As String = "Message from ******* EDI!"
Dim msgBody As String = EmailBody
MailObj.Host = "chad1"
MailObj.Send(msgFrom, msgTo, msgSubject, msgBody)
End Sub
'Sub sendAcknowledgment(ByVal PONUM As String)
'End Sub
End Class