Visual Studio form code example for EDI AS2 communication.
Imports System.IOImports System.Diagnostics
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Imports Excel = Microsoft.Office.Interop.Excel
Imports nsoftware.IPWorksEDI
Imports IBM.Data.DB2
Public Class Form1
Dim ACKS_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim ACKS_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 MSG_ID, EDI_DATA, STATUS FROM SENT_ACKS", ACKS_CON)
Dim ACKS_DS As DataSet = New DataSet
Dim INVS_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim INVS_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 MSG_ID, EDI_DATA, STATUS FROM SENT_ACKS", INVS_CON)
Dim INVS_DS As DataSet = New DataSet
Dim INVOICES_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim INVOICES_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM INVOICES", INVOICES_CON)
Dim INVOICES_DS As DataSet = New DataSet
Dim INVOICE_LINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim INVOICE_LINE_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM INVOICE_LINE", INVOICE_LINE_CON)
Dim INVOICE_LINE_DS As DataSet = New DataSet
Dim WPOT_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim WPOT_DA As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM WPOT", WPOT_CON)
Dim WPOT_DS As DataSet = New DataSet
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'TODO: This line of code loads data into the 'WediDataSet7.WPOT' table. You can move, or remove it, as needed.
'Me.WPOTTableAdapter.Fill(Me.WediDataSet7.WPOT)
'TODO: This line of code loads data into the 'WediDataSet6.SENT_INVS' table. You can move, or remove it, as needed.
'Me.SENT_INVSTableAdapter.Fill(Me.WediDataSet6.SENT_INVS)
'TODO: This line of code loads data into the 'WediDataSet5.INVOICES' table. You can move, or remove it, as needed.
'Me.INVOICESTableAdapter.Fill(Me.WediDataSet5.INVOICES)
'TODO: This line of code loads data into the 'WediDataSet4.SENT_ACKS' table. You can move, or remove it, as needed.
'Me.SENT_ACKSTableAdapter.Fill(Me.WediDataSet4.SENT_ACKS)
'TODO: This line of code loads data into the 'WediDataSet2.TEXTMESSAGE' table. You can move, or remove it, as needed.
'Me.TEXTMESSAGETableAdapter.Fill(Me.WediDataSet2.TEXTMESSAGE)
'TODO: This line of code loads data into the 'WediDataSet1.ACKNOWLEDGMENT' table. You can move, or remove it, as needed.
'Me.ACKNOWLEDGMENTTableAdapter.Fill(Me.WediDataSet1.ACKNOWLEDGMENT)
'TODO: This line of code loads data into the 'WediDataSet.PO' table. You can move, or remove it, as needed.
PO_DataGridView.AllowUserToDeleteRows = False
SentInvoicesDataGridView.AllowUserToDeleteRows = False
AcknowledgmentsDataGridView.AllowUserToDeleteRows = False
TextMessagesDataGridView.AllowUserToDeleteRows = False
SentAcksDataGridView.AllowUserToDeleteRows = False
SentInvsDataGridView.AllowUserToDeleteRows = False
WPOT_DataGridView.AllowUserToDeleteRows = False
POTableAdapter.Fill(Me.WediDataSet.PO)
PO_DataGridView.AutoResizeColumns()
SentInvoicesDataGridView.AutoResizeColumns()
AcknowledgmentsDataGridView.AutoResizeColumns()
TextMessagesDataGridView.AutoResizeColumns()
WPOT_DataGridView.AutoResizeColumns()
as2.RuntimeLicense = "********"
Dim Certificate1 As nsoftware.IPWorksEDI.Certificate = New nsoftware.IPWorksEDI.Certificate(nsoftware.IPWorksEDI.CertStoreTypes.cstPublicKeyFile, Path.Combine(System.Windows.Forms.Application.StartupPath, "********.cer"), "", "C=********, S=********, L=********, O=********, OU=********, CN=********")
Dim Certificate2 As nsoftware.IPWorksEDI.Certificate = New nsoftware.IPWorksEDI.Certificate(nsoftware.IPWorksEDI.CertStoreTypes.cstPFXFile, Path.Combine(System.Windows.Forms.Application.StartupPath, "********.pfx"), "********", "CN=********")
as2.ReceiptSignerCert = Certificate1
as2.SigningCert = Certificate2
PO_DataGridView.Sort(PO_DataGridView.Columns("DATERECEIVED"), System.ComponentModel.ListSortDirection.Descending)
SentInvoicesDataGridView.Sort(SentInvoicesDataGridView.Columns("DATEADDED"), System.ComponentModel.ListSortDirection.Descending)
AcknowledgmentsDataGridView.Sort(AcknowledgmentsDataGridView.Columns("DATERECEIVEDDataGridViewTextBoxColumn1"), System.ComponentModel.ListSortDirection.Descending)
TextMessagesDataGridView.Sort(TextMessagesDataGridView.Columns("DATE_RECEIVED"), System.ComponentModel.ListSortDirection.Descending)
setupAcks()
setupInvs()
setupInvoices()
setupInvoice_line()
setupWpot()
myLog.WriteEntry("User " + System.Security.Principal.WindowsIdentity.GetCurrent.Name + " Started ********", System.Diagnostics.EventLogEntryType.Information)
End Sub
Private Sub MainTabs_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MainTabs.Click
Select Case MainTabs.SelectedTab.Name
Case "PurchaseOrderTab"
POTableAdapter.Fill(WediDataSet.PO)
PO_DataGridView.Refresh()
Case "AcknowledgementsTab"
ACKNOWLEDGMENTTableAdapter.Fill(WediDataSet1.ACKNOWLEDGMENT)
AcknowledgmentsDataGridView.Refresh()
Case "TextMessagesTab"
TEXTMESSAGETableAdapter.Fill(WediDataSet2.TEXTMESSAGE)
TextMessagesDataGridView.Refresh()
Case "SentAcksTab"
SENT_ACKSTableAdapter.Fill(WediDataSet4.SENT_ACKS)
SentAcksDataGridView.Refresh()
Case "SentInvoicesTab"
INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
SentInvoicesDataGridView.Refresh()
Case "SentInvsTab"
SENT_INVSTableAdapter.Fill(WediDataSet6.SENT_INVS)
SentInvsDataGridView.Refresh()
Case "WPOT_tab"
WPOTTableAdapter.Fill(WediDataSet7.WPOT)
WPOT_DataGridView.Refresh()
End Select
End Sub
Private Sub PO_DataGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles PO_DataGridView.CellContentClick
If e.RowIndex > -1 Then
If PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "ACKNOWLEDGED_CHECKBOX" Then
transmission.invoice = False
If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
transmission.row = e.RowIndex
If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
End If
ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
Else
transmission.row = e.RowIndex
If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
End If
End If
ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "QUANTUS_CHECKBOX" Then
If CheckPOTracked(PO_DataGridView.Rows(e.RowIndex).Cells("POTYPE").Value.ToString) Then
If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
WPOT_Acks.row = e.RowIndex
If WPOT_Acks.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
End If
ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
'WPOT_DelSO(e.RowIndex)
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
Else
WPOT_Acks.row = e.RowIndex
If WPOT_Acks.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
End If
End If
Else
If IsDBNull(PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
ElseIf PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
Else
PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
End If
End If
End If
POBindingSource.EndEdit()
POTableAdapter.Update(WediDataSet.PO)
End If
End Sub
Private Sub PO_DataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles PO_DataGridView.CellDoubleClick
If e.RowIndex > -1 Then
If Not PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "ACKNOWLEDGED_CHECKBOX" And Not PO_DataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "QUANTUS_CHECKBOX" Then
Dim tmpApp As New Excel.Application
Dim tmpWorkbook As Excel.Workbook
Dim tmpWorksheet As Excel.Worksheet
tmpWorkbook = tmpApp.Workbooks.Add
tmpWorksheet = tmpWorkbook.Sheets.Add
Dim AddressListCON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim AddressListDA As SqlDataAdapter = _
New SqlDataAdapter("SELECT GLN_ID, GLN, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, CONTACT, PHONE, DUNS, STORENUM, DISTRONUM, DATE FROM ADDRESSLIST WHERE GLN = @GLN", AddressListCON)
Dim AddressListDS As DataSet = New DataSet
Dim PO_LINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim PO_LINE_DA As SqlDataAdapter = _
New SqlDataAdapter("SELECT LINE_ID, MSG_ID, LINE_NUM, QTY, UNITS, UNIT_PRICE, CUSTOMER_ITEM_NUM, VENDOR_ITEM_NUM, DESCRIPTION, " + _
"AMOUNT, SDQ FROM PO_LINE WHERE MSG_ID = @MSG_ID", PO_LINE_CON)
Dim PO_LINE_DS As DataSet = New DataSet
Dim SDQ_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim SDQ_DA As SqlDataAdapter = _
New SqlDataAdapter("SELECT LINE_ID, GLN, QTY FROM SDQ WHERE LINE_ID = @LINE_ID", SDQ_CON)
Dim SDQ_DS As DataSet = New DataSet
Dim storenum As String
Dim distronum As String
Dim division As String
Dim items_SDQ As List(Of Line_items_SDQ) = New List(Of Line_items_SDQ)
AddressListDA.SelectCommand.Parameters.Add("@GLN", SqlDbType.VarChar, 80, "GLN")
PO_LINE_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
SDQ_DA.SelectCommand.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
With tmpWorksheet
.Range("a:i").Font.Size = 8
.Range("a:i").WrapText = False
.Rows.RowHeight = 14
.Range("1:2").RowHeight = 26
.Range("1:2").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("1:2").VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("1:2").Font.Bold = True
.Range("g3").Font.Bold = True
.Range("h3").Font.Bold = True
.Range("a16").Font.Bold = True
.Range("a17").Font.Bold = True
.Range("20:20").Font.Bold = True
.Range("20:20").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Name = "WM PO#" + PO_DataGridView.Rows(e.RowIndex).Cells("PONUM").Value.ToString
.Range("a1:i1").Merge()
.Range("a1").Value = "******** PURCHASE ORDER"
.Range("a2:f2").Merge()
.Range("g2:i2").Merge()
.Range("a2").Value = "VENDOR INFORMATION"
.Range("a2").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("g2").Value = "ORDER DETAILS"
.Range("g1").HorizontalAlignment = XlHAlign.xlHAlignCenter
For counter As Integer = 3 To 15
.Range("a" & counter & ":c" & counter).Merge()
.Range("d" & counter & ":f" & counter).Merge()
.Range("h" & counter & ":i" & counter).Merge()
.Range("h" & counter).HorizontalAlignment = XlHAlign.xlHAlignLeft
Next
.Range("a3").Value = PO_DataGridView.Rows(e.RowIndex).Cells("VENDORNAME").Value.ToString
.Range("d3").Value = "HOST Vendor # " + PO_DataGridView.Rows(e.RowIndex).Cells("VENDORNUM").Value.ToString
.Range("g3").Value = "PO Number: "
.Range("h3").Value = PO_DataGridView.Rows(e.RowIndex).Cells("PONUM").Value.ToString
.Range("g4").Value = "Order Type: "
.Range("h4").Value = PO_DataGridView.Rows(e.RowIndex).Cells("POTYPE").Value.ToString
.Range("g5").Value = "Order Date: "
.Range("h5").Value = PO_DataGridView.Rows(e.RowIndex).Cells("ORDERDATE").Value
.Range("g6").Value = "Arrival Date: "
.Range("h6").Value = PO_DataGridView.Rows(e.RowIndex).Cells("ARRIVALDATE").Value
.Range("a7").Value = "SHIP MERCHANDISE TO"
.Range("d7").Value = "SEND INVOICES TO"
.Range("g7").Value = "Ordered By: "
.Range("h7").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BUYER").Value.ToString
If String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOGLN").Value.ToString) Then
.Range("a8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTONAME").Value.ToString
.Range("a9").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOADDRESS1").Value.ToString
.Range("a10").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOADDRESS2").Value.ToString
.Range("a11").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOCITY").Value.ToString + " " + _
PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOSTATE").Value.ToString + " " + _
PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOZIP").Value.ToString
.Range("a12").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOCOUNTRY").Value.ToString
storenum = Nothing
distronum = Nothing
Else
AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = PO_DataGridView.Rows(e.RowIndex).Cells("SHIPTOGLN").Value.ToString
AddressListDA.Fill(AddressListDS, "ADDRESS")
.Range("a8").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("NAME").ToString
.Range("a9").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS1").ToString
.Range("a10").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS2").ToString
.Range("a11").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("CITY").ToString + " " + _
AddressListDS.Tables("ADDRESS").Rows(0).Item("STATE").ToString + " " + _
AddressListDS.Tables("ADDRESS").Rows(0).Item("ZIP").ToString
.Range("a12").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("COUNTRY").ToString
storenum = AddressListDS.Tables("ADDRESS").Rows(0).Item("STORENUM").ToString
distronum = AddressListDS.Tables("ADDRESS").Rows(0).Item("DISTRONUM").ToString
AddressListDS.Clear()
End If
If String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("BILLTOGLN").Value.ToString) Then
.Range("d8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BILLTONAME").Value.ToString
Else
AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BILLTOGLN").Value.ToString
AddressListDA.Fill(AddressListDS, "ADDRESS")
.Range("d8").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("NAME").ToString
.Range("d9").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS1").ToString
.Range("d10").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("ADDRESS2").ToString
.Range("d11").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("CITY").ToString + " " + _
AddressListDS.Tables("ADDRESS").Rows(0).Item("STATE").ToString + " " + _
AddressListDS.Tables("ADDRESS").Rows(0).Item("ZIP").ToString
.Range("d12").Value = AddressListDS.Tables("ADDRESS").Rows(0).Item("COUNTRY").ToString
AddressListDS.Clear()
End If
If Not String.IsNullOrEmpty(PO_DataGridView.Rows(e.RowIndex).Cells("FINALDELIVERYLOCATION").Value.ToString) Then
.Range("a14").Value = "Final Destination:"
.Range("a15").Value = PO_DataGridView.Rows(e.RowIndex).Cells("FINALDELIVERYLOCATION").Value.ToString
End If
.Range("g8").Value = "Phone #: "
.Range("h8").Value = PO_DataGridView.Rows(e.RowIndex).Cells("BUYERPHONE").Value.ToString
.Range("g9").Value = "Revised By: "
.Range("h9").Value = PO_DataGridView.Rows(e.RowIndex).Cells("REVISEDBY").Value.ToString
.Range("g10").Value = "Phone #: "
.Range("h10").Value = PO_DataGridView.Rows(e.RowIndex).Cells("REVISEDPHONE").Value.ToString
.Range("g11").Value = "Terms:"
.Range("h11").Value = PO_DataGridView.Rows(e.RowIndex).Cells("TERMS").Value.ToString
.Range("g12").Value = "Currency:"
.Range("h12").Value = PO_DataGridView.Rows(e.RowIndex).Cells("CURRENCY").Value.ToString
.Range("a16:i16").Merge()
.Range("a16").RowHeight = 26
.Range("a16").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a16").VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("a16").Value = "FACILITY INFORMATION"
.Range("a17:c17").Merge()
.Range("d17:f17").Merge()
.Range("g17:i17").Merge()
.Range("a17").Value = "Facility #: "
If Not String.IsNullOrEmpty(storenum) Then
.Range("a17").Value = .Range("a17").Value + storenum.TrimStart("0")
End If
If Not String.IsNullOrEmpty(distronum) Then
.Range("a17").Value = .Range("a17").Value + distronum.TrimStart("0")
End If
.Range("d17").Value = "Manager: " + PO_DataGridView.Rows(e.RowIndex).Cells("FACILITYCONTACT").Value.ToString
.Range("g17").Value = "Phone #: " + PO_DataGridView.Rows(e.RowIndex).Cells("FACILITYPHONE").Value.ToString
.Range("a18:i18").Merge()
.Range("a18").RowHeight = 26
.Range("a18").VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("a18").Value = PO_DataGridView.Rows(e.RowIndex).Cells("NOTESLABEL").Value.ToString
Dim count As Integer = 19
.Range("a" & count & ":i" & count).Merge()
.Range("a" & count).Value = PO_DataGridView.Rows(e.RowIndex).Cells("NOTES").Value.ToString.Replace(vbCrLf, vbLf)
Dim tmplist As List(Of String) = PO_DataGridView.Rows(e.RowIndex).Cells("NOTES").Value.ToString.Split(vbLf).ToList
.Range("a" & count).RowHeight = 51 '12.75 * tmplist.Count
count = count + 1
.Range("d" & count & ":f" & count).Merge()
.Range("a" & count).Value = "DIV"
.Range("b" & count).Value = "LINE ID"
.Range("c" & count).Value = "PART NUMBER"
.Range("d" & count).Value = "DESCRIPTION"
.Range("g" & count).Value = "QUANTITY"
.Range("h" & count).Value = "UNIT PRICE"
.Range("i" & count).Value = "EXTENDED PRICE"
division = PO_DataGridView.Rows(e.RowIndex).Cells("DIVISION").Value.ToString
PO_LINE_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = PO_DataGridView.Rows(e.RowIndex).Cells("MSGID").Value.ToString
PO_LINE_DA.Fill(PO_LINE_DS, "PO_LINE")
For Each dr As DataRow In PO_LINE_DS.Tables("PO_LINE").Rows
count = count + 2
.Range("a" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a" & count).Value = division
.Range("b" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("b" & count).Value = dr.Item("LINE_NUM").ToString
.Range("c" & count).Value = dr.Item("CUSTOMER_ITEM_NUM").ToString
.Range("g" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
.Range("g" & count).Value = dr.Item("QTY").ToString + " " + dr.Item("UNITS").ToString
.Range("h" & count).NumberFormat = "$#,##0.00"
.Range("h" & count).Value = dr.Item("UNIT_PRICE").ToString
.Range("i" & count).NumberFormat = "$#,##0.00"
.Range("i" & count).Value = dr.Item("AMOUNT").ToString
count = count + 1
.Range("d" & count & ":h" & count).Merge()
.Range("d" & count).Value = dr.Item("DESCRIPTION").ToString
count = count + 1
.Range("c" & count & ":f" & count).Merge()
.Range("c" & count).Value = "Supplier Item Number: " + dr.Item("VENDOR_ITEM_NUM").ToString
If Not String.IsNullOrEmpty(dr.Item("SDQ").ToString) Then
Dim tmpLine_items_SDQ As Line_items_SDQ = New Line_items_SDQ
tmpLine_items_SDQ.partnum = dr.Item("CUSTOMER_ITEM_NUM").ToString
SDQ_DA.SelectCommand.Parameters.Item("@LINE_ID").Value = dr.Item("LINE_ID").ToString
SDQ_DA.Fill(SDQ_DS, "SDQ")
For Each tmpdr As DataRow In SDQ_DS.Tables("SDQ").Rows
Dim tmpSDQ As SDQ = New SDQ
storenum = Nothing
distronum = Nothing
AddressListDA.SelectCommand.Parameters.Item("@GLN").Value = tmpdr.Item("GLN").ToString
AddressListDA.Fill(AddressListDS, "ADDRESS")
storenum = AddressListDS.Tables("ADDRESS").Rows(0).Item("STORENUM").ToString
distronum = AddressListDS.Tables("ADDRESS").Rows(0).Item("DISTRONUM").ToString
If String.IsNullOrEmpty(storenum) Then
tmpSDQ.storenum = distronum
Else
tmpSDQ.storenum = storenum
End If
tmpSDQ.quantity = tmpdr.Item("QTY").ToString
tmpLine_items_SDQ.part_sdq.Add(tmpSDQ)
AddressListDS.Clear()
Next
items_SDQ.Add(tmpLine_items_SDQ)
SDQ_DS.Clear()
End If
Next
count = count + 1
.Range("c" & count & ":d" & count).Merge()
.Range("e" & count & ":f" & count).Merge()
.Range("h" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
.Range("h" & count).Value = "TOTAL"
.Range("i" & count).NumberFormat = "$#,##0.00"
.Range("i" & count).Value = PO_DataGridView.Rows(e.RowIndex).Cells("AMOUNT").Value.ToString
count = count + 1
If items_SDQ.Count > 0 Then
.Range("a" & count & ":i" & count).Merge()
.Range("a" & count).RowHeight = 26
.Range("a" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("a" & count).Font.Bold = True
.Range("a" & count).Value = "SDQ List"
For Each tmpItemListing As Line_items_SDQ In items_SDQ
count = count + 1
.Range("a" & count).RowHeight = 26
.Range(count & ":" & count).Font.Bold = True
.Range(count & ":" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("a" & count & ":c" & count).Merge()
.Range("d" & count & ":f" & count).Merge()
.Range("a" & count).Value = "Item Number"
.Range("d" & count).HorizontalAlignment = XlHAlign.xlHAlignLeft
.Range("d" & count).Value = tmpItemListing.partnum
count = count + 1
.Range("a" & count).RowHeight = 26
.Range(count & ":" & count).Font.Bold = True
.Range(count & ":" & count).VerticalAlignment = XlVAlign.xlVAlignCenter
.Range(count & ":" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a" & count & ":c" & count).Merge()
.Range("d" & count & ":f" & count).Merge()
.Range("a" & count).Value = "Store No."
.Range("d" & count).Value = "Quantity."
For Each tmpItemListingSDQ As SDQ In tmpItemListing.part_sdq
count = count + 1
.Range(count & ":" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a" & count & ":c" & count).Merge()
.Range("d" & count & ":f" & count).Merge()
.Range("a" & count).Value = tmpItemListingSDQ.storenum
.Range("d" & count).Value = tmpItemListingSDQ.quantity
Next
Next
End If
.Range("A2:I2").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("A3:C15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("D3:F15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G3:I6").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G7:I10").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G11:I15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("A16:I16").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
.Range("A17:I17").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
.Range("A18:I19").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("A20:I20").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("A7:F7").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("a1").Select()
.Range("a:a").ColumnWidth = 4.71
.Range("b:b").ColumnWidth = 7
.Range("c:c").ColumnWidth = 20.16
.Range("d:f").ColumnWidth = 11.29
.Range("g:g").ColumnWidth = 9.71
.Range("h:h").ColumnWidth = 9.86
.Range("i:i").ColumnWidth = 14.14
.PageSetup.LeftMargin = 9.0
.PageSetup.RightMargin = 9.0
.PageSetup.TopMargin = 9.0
.PageSetup.BottomMargin = 9.0
.PageSetup.HeaderMargin = 9.0
.PageSetup.FooterMargin = 9.0
End With
tmpApp.Visible = True
End If
End If
End Sub
Private Sub PO_DataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles PO_DataGridView.UserDeletedRow
POTableAdapter.Update(WediDataSet)
End Sub
Private Sub AcknowledgmentsDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles AcknowledgmentsDataGridView.CellDoubleClick
If e.RowIndex > -1 Then
Acknowledgement.TextBox1.Text = AcknowledgmentsDataGridView.Rows(e.RowIndex).Cells("DataGridViewTextBoxColumn2").Value.ToString()
Acknowledgement.Show()
End If
End Sub
Private Sub AcknowledgmentsDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles AcknowledgmentsDataGridView.UserDeletedRow
ACKNOWLEDGMENTTableAdapter.Update(WediDataSet1)
End Sub
Private Sub TextMessagesDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TextMessagesDataGridView.CellDoubleClick
If e.RowIndex > -1 Then
TextMessage.TextBox1.Text = TextMessagesDataGridView.Rows(e.RowIndex).Cells("MESSAGE").Value.ToString()
TextMessage.Show()
End If
End Sub
Private Sub TextMessagesDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles TextMessagesDataGridView.UserDeletedRow
TEXTMESSAGETableAdapter.Update(WediDataSet2)
End Sub
Public Function acknowledge(ByVal row As Integer) As Boolean
Dim transmitted As Boolean = False
Dim dr As DataRow
Dim isa As String
Dim gs As String
Dim st As String
Dim ak1 As String
Dim ak2 As String
Dim ak5 As String
Dim ak9 As String
Dim se As String
Dim ge As String
Dim iea As String
Dim isa_controlnum As String
Dim gs_controlnum As String
Dim transString As String
dr = ACKS_DS.Tables("SENT_ACKS").NewRow
dr.Item("EDI_DATA") = "Initialized"
dr.Item("STATUS") = "Failed"
ACKS_DS.Tables("SENT_ACKS").Rows.Add(dr)
ACKS_DA.Update(ACKS_DS, "SENT_ACKS")
isa_controlnum = (dr.Item("MSG_ID") + 997000000).ToString
gs_controlnum = (dr.Item("MSG_ID") + 850000000).ToString
isa = "ISA*00* *00* *ZZ********* *08********* *" + _
Today.ToString("yyMMdd") + "*" + Date.Now.ToString("HHmm") + "*:**********" + isa_controlnum + "*0*P*>~"
gs = "GS*FA*******************" + Today.ToString("yyyyMMdd") + "*" + Date.Now.ToString("HHmm") + _
"*" + gs_controlnum + "*X*********~"
st = "ST*997*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
ak1 = "AK1*PO*" + PO_DataGridView.Rows(row).Cells("GS_CONTROLNUM").Value.ToString + "~"
ak2 = "AK2*850*" + PO_DataGridView.Rows(row).Cells("ST_CONTROLNUM").Value.ToString + "~"
ak5 = "AK5*A~"
ak9 = "AK9*A*1*1*1~"
se = "SE*6*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
ge = "GE*1*" + gs_controlnum + "~"
iea = "IEA*1*" + isa_controlnum + "~"
transString = isa + gs + st + ak1 + ak2 + ak5 + ak9 + se + ge + iea
dr.Item("EDI_DATA") = isa + vbCrLf + gs + vbCrLf + st + vbCrLf + ak1 + vbCrLf + ak2 + vbCrLf + ak5 + vbCrLf + ak9 + vbCrLf + se + vbCrLf + ge + vbCrLf + iea
as2.EDIData = New EDIData()
as2.EDIData.EDIType = "application/edi-x12"
as2.EDIData.Data = transString
Try
as2.Post()
' If the call to post returns without throwing an exception, then the
' component was able to post the data and verify the response. In particular,
' if you requested a synchronous MDN, it will automatically be validated,
' and an exception will be thrown if there are any problems.
' If you requested an asynchronous MDN, you will need to save the values
' of MessageId, OriginalContentMIC, and MDNOptions, so they can be looked
' up based on the MessageId. Then, when you process the asynchronous MDN,
' you will need to load these values into the component to verify the
' MDN. See the Async MDN demo or the AS2 Connector application for more
' information on how to do this.
dr.Item("STATUS") = "Success"
transmitted = True
Catch ipwee As nsoftware.IPWorksEDI.IPWorksEDIException
myLog.WriteEntry("Transmission was unsuccessful: " + ipwee.Message)
Finally
If Not as2.MDNReceipt Is Nothing Then
TextBox5.Text = transString
TextBox1.Text = as2.MDNReceipt.Headers
TextBox2.Text = as2.MDNReceipt.Content
TextBox3.Text = as2.MDNReceipt.Message
TextBox4.Text = as2.MDNReceipt.MDN
End If
End Try
ACKS_DA.Update(ACKS_DS, "SENT_ACKS")
ACKS_DS.Tables("SENT_ACKS").Clear()
ACKS_DS.AcceptChanges()
Return transmitted
End Function
Public Function invoice(ByVal row As Integer) As Boolean
Dim INVOICELINE_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim INVOICELINE_DA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INVOICE_LINE WHERE MSG_ID = @MSG_ID", INVOICELINE_CON)
Dim INVOICELINE_DS As DataSet = New DataSet
INVOICELINE_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
Dim transmitted As Boolean = False
Dim dr As DataRow
Dim isa As String
Dim gs As String
Dim st As String
Dim big As String
Dim ref_mr As String
Dim n1_st As String
Dim n3 As String
Dim n4 As String
Dim n1_su As String
Dim ref_ia As String
Dim itd As String
Dim dtm As String
Dim it1 As String = ""
Dim tds As String
Dim txi As String
Dim cad As String
Dim ctt As String
Dim se As String
Dim ge As String
Dim iea As String
Dim isa_controlnum As String
Dim gs_controlnum As String
Dim transString As String
Dim dtInvoiced As Date
Dim dtOrdered As Date
Dim dtShipped As Date
Dim line_total As Double
Dim total As Double = 0
Dim line_count As Integer = 0
dr = INVS_DS.Tables("SENT_INVS").NewRow
dr.Item("EDI_DATA") = "Initialized"
dr.Item("STATUS") = "Failed"
INVS_DS.Tables("SENT_INVS").Rows.Add(dr)
INVS_DA.Update(INVS_DS, "SENT_INVS")
isa_controlnum = (dr.Item("MSG_ID") + 810000000).ToString
gs_controlnum = (dr.Item("MSG_ID") + 100000000).ToString
isa = "ISA*00* *00* *ZZ********* *08********* *" + _
Today.ToString("yyMMdd") + "*" + Date.Now.ToString("HHmm") + "*:*00501*" + isa_controlnum + "*0*P*>~"
gs = "GS*IN*******************" + Today.ToString("yyyyMMdd") + "*" + Date.Now.ToString("HHmm") + _
"*" + gs_controlnum + "*X*********~"
st = "ST*810*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
dtInvoiced = SentInvoicesDataGridView.Rows(row).Cells("INVOICEDATE").Value
dtOrdered = SentInvoicesDataGridView.Rows(row).Cells("ORDER_DATE").Value
dtShipped = SentInvoicesDataGridView.Rows(row).Cells("SHIPPED_DATE").Value
big = "BIG*" + dtInvoiced.ToString("yyyyMMdd") + "*" + SentInvoicesDataGridView.Rows(row).Cells("INVOICE_NUM").Value.ToString + _
"*" + dtOrdered.ToString("yyyyMMdd") + "*" + SentInvoicesDataGridView.Rows(row).Cells("PO_NUM").Value.ToString + "~"
If SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.Contains("_S") Then
Dim tmpStr As String = SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.Substring(0, SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString.IndexOf("_S"))
ref_mr = "REF*MR*" + tmpStr + "~"
Else
ref_mr = "REF*MR*" + SentInvoicesDataGridView.Rows(row).Cells("PO_TYPE").Value.ToString + "~"
End If
n1_st = "N1*ST*" + SentInvoicesDataGridView.Rows(row).Cells("ST_NAME").Value.ToString
If String.IsNullOrEmpty(SentInvoicesDataGridView.Rows(row).Cells("SHIP_TO_GLN").Value.ToString) Then
n1_st = n1_st + "**~"
Else
n1_st = n1_st + "*UL*" + SentInvoicesDataGridView.Rows(row).Cells("SHIP_TO_GLN").Value.ToString + "~"
End If
n3 = "N3*" + SentInvoicesDataGridView.Rows(row).Cells("ADDRESS1").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("ADDRESS2").Value.ToString + "~"
n4 = "N4*" + SentInvoicesDataGridView.Rows(row).Cells("CITY").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("STATE").Value.ToString + _
"*" + SentInvoicesDataGridView.Rows(row).Cells("ZIP").Value.ToString + "*" + SentInvoicesDataGridView.Rows(row).Cells("COUNTRY").Value.ToString + "~"
n1_su = "N1*SU*********~"
ref_ia = "REF*IA*********~"
If SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value > 0 Then
itd = "ITD*08*3*" + (SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value * 100).ToString + "**" + SentInvoicesDataGridView.Rows(row).Cells("DISCDAYS").Value.ToString + _
"**" + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "*****" + (SentInvoicesDataGridView.Rows(row).Cells("DISCRATE").Value * 100).ToString + "% " + SentInvoicesDataGridView.Rows(row).Cells("DISCDAYS").Value.ToString + "NET " + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "~"
Else
itd = "ITD*05******" + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "*****" + "NET " + SentInvoicesDataGridView.Rows(row).Cells("NETDAYS").Value.ToString + "~"
End If
dtm = "DTM*011*" + dtShipped.ToString("yyyyMMdd") + "~"
INVOICELINE_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = SentInvoicesDataGridView.Rows(row).Cells("MSG_ID").Value.ToString()
INVOICELINE_DA.Fill(INVOICELINE_DS, "INVOICE_LINE")
For Each tmpdr As DataRow In INVOICELINE_DS.Tables("INVOICE_LINE").Rows
line_total = 0
it1 = it1 + "IT1*" + tmpdr.Item("LINE_NUM").ToString() + "*" + tmpdr.Item("QTY").ToString() + "*" + tmpdr.Item("UNITS").ToString() + "*" + _
tmpdr.Item("UNIT_PRICE").ToString() + "*LE*IN*" + tmpdr.Item("CUSTOMER_ITEMNUM").ToString() + "*VN*" + _
tmpdr.Item("VENDOR_ITEMNUM").ToString() + "~"
line_total = tmpdr.Item("UNIT_PRICE") * tmpdr.Item("QTY")
total = total + line_total
line_count = line_count + 1
Next
total = total + SentInvoicesDataGridView.Rows(row).Cells("TAX").Value
tds = "TDS*" + total.ToString("F").Replace(".", "") + "~"
txi = "TXI*TX*" + SentInvoicesDataGridView.Rows(row).Cells("TAX").Value.ToString + "~"
cad = "CAD*" + SentInvoicesDataGridView.Rows(row).Cells("TRANS_TYPE").Value.ToString() + "***" + _
SentInvoicesDataGridView.Rows(row).Cells("CARRIER").Value.ToString() + "*" + _
SentInvoicesDataGridView.Rows(row).Cells("CARRIER_DESCRIPTION").Value.ToString + "**" + _
SentInvoicesDataGridView.Rows(row).Cells("REF_CODE").Value.ToString() + "*" + _
SentInvoicesDataGridView.Rows(row).Cells("REFNUM").Value.ToString() + "~"
ctt = "CTT*" + line_count.ToString + "~"
se = "SE*" + (line_count + 15).ToString + "*" + String.Format("{0:D4}", dr.Item("MSG_ID")) + "~"
ge = "GE*1*" + gs_controlnum + "~"
iea = "IEA*1*" + isa_controlnum + "~"
transString = isa + gs + st + big + ref_mr + n1_st + n3 + n4 + n1_su + ref_ia + itd + dtm + it1 + tds + txi + cad + ctt + se + ge + iea
dr.Item("EDI_DATA") = isa + vbCrLf + gs + vbCrLf + st + vbCrLf + big + vbCrLf + ref_mr + vbCrLf + n1_st + vbCrLf + n3 + vbCrLf + _
n4 + vbCrLf + n1_su + vbCrLf + ref_ia + vbCrLf + itd + vbCrLf + dtm + vbCrLf + it1 + vbCrLf + tds + vbCrLf + _
txi + vbCrLf + cad + vbCrLf + ctt + vbCrLf + se + vbCrLf + ge + vbCrLf + iea
as2.EDIData = New EDIData()
as2.EDIData.EDIType = "application/edi-x12"
as2.EDIData.Data = transString
Try
as2.Post()
' If the call to post returns without throwing an exception, then the
' component was able to post the data and verify the response. In particular,
' if you requested a synchronous MDN, it will automatically be validated,
' and an exception will be thrown if there are any problems.
' If you requested an asynchronous MDN, you will need to save the values
' of MessageId, OriginalContentMIC, and MDNOptions, so they can be looked
' up based on the MessageId. Then, when you process the asynchronous MDN,
' you will need to load these values into the component to verify the
' MDN. See the Async MDN demo or the AS2 Connector application for more
' information on how to do this.
dr.Item("STATUS") = "Success"
transmitted = True
Catch ipwee As nsoftware.IPWorksEDI.IPWorksEDIException
myLog.WriteEntry("Transmission was unsuccessful: " + ipwee.Message)
Finally
If Not as2.MDNReceipt Is Nothing Then
TextBox5.Text = transString
TextBox1.Text = as2.MDNReceipt.Headers
TextBox2.Text = as2.MDNReceipt.Content
TextBox3.Text = as2.MDNReceipt.Message
TextBox4.Text = as2.MDNReceipt.MDN
End If
End Try
INVS_DA.Update(INVS_DS, "SENT_INVS")
INVS_DS.Tables("SENT_INVS").Clear()
INVS_DS.AcceptChanges()
Return transmitted
End Function
Private Sub setupAcks()
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
DAInsertCmd = New SqlCommand("InsertAcksMSG_ID", ACKS_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("@EDI_DATA", SqlDbType.VarChar, 1000, "EDI_DATA")
DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
ACKS_DA.InsertCommand = DAInsertCmd
DAUpdateCmd = New SqlCommand("UPDATE SENT_ACKS SET EDI_DATA = @EDI_DATA, STATUS = @STATUS WHERE MSG_ID = @MSG_ID", ACKS_DA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAUpdateCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 1000, "EDI_DATA")
DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
ACKS_DA.UpdateCommand = DAUpdateCmd
ACKS_DA.Fill(ACKS_DS, "SENT_ACKS")
ACKS_DS.Tables("SENT_ACKS").Clear()
ACKS_DS.AcceptChanges()
Catch ex As Exception
myLog.WriteEntry("setupAcks failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Private Sub setupInvs()
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
DAInsertCmd = New SqlCommand("InsertInvsMSG_ID", INVS_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("@EDI_DATA", SqlDbType.VarChar, 4096, "EDI_DATA")
DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
INVS_DA.InsertCommand = DAInsertCmd
DAUpdateCmd = New SqlCommand("UPDATE SENT_INVS SET EDI_DATA = @EDI_DATA, STATUS = @STATUS WHERE MSG_ID = @MSG_ID", INVS_DA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAUpdateCmd.Parameters.Add("@EDI_DATA", SqlDbType.VarChar, 4096, "EDI_DATA")
DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.VarChar, 50, "STATUS")
INVS_DA.UpdateCommand = DAUpdateCmd
INVS_DA.Fill(INVS_DS, "SENT_INVS")
INVS_DS.Tables("SENT_INVS").Clear()
INVS_DS.AcceptChanges()
Catch ex As Exception
myLog.WriteEntry("setupInvs failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Private Sub SentAcksDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentAcksDataGridView.CellDoubleClick
If e.RowIndex > -1 Then
sent_acks.TextBox1.Text = SentAcksDataGridView.Rows(e.RowIndex).Cells("EDIDATADataGridViewTextBoxColumn").Value.ToString()
sent_acks.Show()
End If
End Sub
Private Sub SentAcksDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentAcksDataGridView.UserDeletedRow
SENT_ACKSTableAdapter.Update(WediDataSet4)
End Sub
Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
POTableAdapter.Fill(WediDataSet.PO)
PO_DataGridView.Refresh()
End Sub
Private Sub CheckBox1_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckStateChanged
If CheckBox1.Checked Then
Timer1.Start()
Else
Timer1.Stop()
End If
End Sub
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Select Case MainTabs.SelectedTab.Name
Case "PurchaseOrderTab"
POTableAdapter.Fill(WediDataSet.PO)
PO_DataGridView.Refresh()
Case "AcknowledgementsTab"
ACKNOWLEDGMENTTableAdapter.Fill(WediDataSet1.ACKNOWLEDGMENT)
AcknowledgmentsDataGridView.Refresh()
Case "TextMessagesTab"
TEXTMESSAGETableAdapter.Fill(WediDataSet2.TEXTMESSAGE)
TextMessagesDataGridView.Refresh()
Case "SentAcksTab"
SENT_ACKSTableAdapter.Fill(WediDataSet4.SENT_ACKS)
SentAcksDataGridView.Refresh()
Case "SentInvoicesTab"
INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
SentInvoicesDataGridView.Refresh()
Case "SentInvsTab"
SENT_INVSTableAdapter.Fill(WediDataSet6.SENT_INVS)
SentInvsDataGridView.Refresh()
Case "WPOT_tab"
WPOTTableAdapter.Fill(WediDataSet7.WPOT)
WPOT_DataGridView.Refresh()
End Select
End Sub
Private Sub PurchaseOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurchaseOrdersToolStripMenuItem.Click
edi_purchase_orders.Show()
End Sub
Private Sub PurposeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurposeToolStripMenuItem.Click
purposecodes.Show()
End Sub
Private Sub PurchaseOrderTypesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PurchaseOrderTypesToolStripMenuItem.Click
purchaseOrderTypes.Show()
End Sub
Private Sub ********EDIToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ********EDIToolStripMenuItem.Click
VEDIHelp.Show()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim QDB_Con As DB2Connection = New DB2Connection("Database=********;User ID=********;Password=********;Server=********.********.com:50000;Persist Security Info=True")
Dim mySelectText As String = "SELECT * FROM QUANTUS.EMPLOYEE"
Dim QDB_DA As DB2DataAdapter = New DB2DataAdapter(mySelectText, QDB_Con)
Dim QDB_DS As DataSet = New DataSet()
Dim SelectShippedBLText As String
Dim dr As DataRow
Dim msg_id As Integer
Dim total_tax As Double = 0
SelectShippedBLText = "SELECT QUANTUS.ORDER_LINE.DTINVOICED, RTRIM(QUANTUS.ORDER_LINE.REFERNUM1) AS INVOICENUM, QUANTUS.ORDER_LINE.LINESEQNO AS LINENUM, QUANTUS.SALES_ORDER.DTORDER, QUANTUS.ORDER_LINE.DTSHIPPED, " + _
"RTRIM(QUANTUS.SALES_ORDER.CUSTPONUM) AS PONUM, RTRIM(QUANTUS.SALES_ORDER.MISCINFO) AS PO_TYPE, RTRIM(QUANTUS.SALES_ORDER.CARRIER) AS CARRIER, RTRIM(QUANTUS.TAILOR_CODES.DESCRIPTION) AS CARRIER_DESCRIPTION, " + _
"RTRIM(QUANTUS.CUSTOMER.NAME1) AS NAME, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS1) AS ADDRESS1, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS2) AS ADDRESS2, RTRIM(QUANTUS.CUSTOMER.MAILCITY) AS CITY, " + _
"RTRIM(QUANTUS.CUSTOMER.MAILPROV) AS STATE, RTRIM(QUANTUS.CUSTOMER.MAILPOSTAL) AS ZIP, RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) AS COUNTRY, " + _
"QUANTUS.SALES_ORDER.DTREQUESTED, QUANTUS.SALES_ORDER.DTPROMISED, " + _
"QUANTUS.SALES_ORDER.DISCTYPE, QUANTUS.SALES_ORDER.DISCDAYS, QUANTUS.SALES_ORDER.DISCRATE, QUANTUS.SALES_ORDER.DUETYPE, QUANTUS.SALES_ORDER.DUEDAYS, QUANTUS.ORDER_LINE.TAXAMT1, QUANTUS.ORDER_LINE.TAXAMT2, QUANTUS.ORDER_LINE.TAXAMT3, QUANTUS.ORDER_LINE.TAXAMT4, " + _
"QUANTUS.ORDER_LINE.INVOICEQTY, RTRIM(QUANTUS.ORDER_LINE.ORDERUOFM) AS UNITS, QUANTUS.ORDER_LINE.UNITPRICE, LTRIM(QUANTUS.ORDER_LINE.ALTITEMNUM) AS CUSTITEMNUM, " + _
"LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) AS ITEMNUM, LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) AS ORDERNUM, LTRIM(QUANTUS.SALES_ORDER.RELEASE) AS RELEASE " + _
"FROM QUANTUS.SALES_ORDER LEFT OUTER JOIN QUANTUS.TAILOR_CODES ON QUANTUS.SALES_ORDER.CARRIER = QUANTUS.TAILOR_CODES.CODE AND QUANTUS.TAILOR_CODES.TYPE = 'C', QUANTUS.ORDER_LINE, QUANTUS.CUSTOMER " + _
"WHERE QUANTUS.SALES_ORDER.BRANCH = QUANTUS.ORDER_LINE.BRANCH " + _
"AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM " + _
"AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.ORDER_LINE.RELEASE " + _
"AND QUANTUS.SALES_ORDER.SHIPBRANCH = QUANTUS.CUSTOMER.BRANCH " + _
"AND QUANTUS.SALES_ORDER.SHIPACCT = QUANTUS.CUSTOMER.CUSTOMERACCT " + _
"AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) = '02' " + _
"AND LTRIM(QUANTUS.CUSTOMER.BILLINGACCT) = '********' " + _
"AND QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 " + _
"AND LTRIM(QUANTUS.ORDER_LINE.REFERNUM1) = '" + tbInvoicenum.Text.ToUpper + "' " + _
"ORDER BY QUANTUS.ORDER_LINE.LINESEQNO ASC"
QDB_DA.SelectCommand.CommandText = SelectShippedBLText
If QDB_DA.Fill(QDB_DS, "InvoiceLines") > 0 Then
If CheckPOTracked(QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString) Then
WPOT_DA.SelectCommand.CommandText = "SELECT * FROM WPOT WHERE ORDERNUM = '" + QDB_DS.Tables("InvoiceLines").Rows(0).Item("ORDERNUM").ToString + _
" " + QDB_DS.Tables("InvoiceLines").Rows(0).Item("RELEASE").ToString + "'"
If WPOT_DA.Fill(WPOT_DS, "WPOT") > 0 Then
If ShippingInfo.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
For Each tmp2dr As DataRow In WPOT_DS.Tables("WPOT").Rows
For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
If tmp2dr.Item("LINE_NUM").ToString = QDB_DS.Tables("InvoiceLines").Rows(i).Item("LINENUM").ToString Then
If ShippingInfo.ComboBox2.Text = "2I" Then
tmp2dr.Item("PRONUM") = ShippingInfo.TextBox1.Text
tmp2dr.Item("WAYBILLNUM") = ""
ElseIf ShippingInfo.ComboBox2.Text = "BM" Then
tmp2dr.Item("WAYBILLNUM") = ShippingInfo.TextBox1.Text
tmp2dr.Item("PRONUM") = ""
ElseIf ShippingInfo.ComboBox2.Text = "WT" Then
tmp2dr.Item("PRONUM") = ShippingInfo.TextBox1.Text
tmp2dr.Item("WAYBILLNUM") = ""
End If
tmp2dr.Item("ALTITEMNUM") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("CUSTITEMNUM").ToString
tmp2dr.Item("ITEMNUM") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("ITEMNUM").ToString
tmp2dr.Item("DTSHIPPED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTSHIPPED").ToString
tmp2dr.Item("DTREQUESTED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTREQUESTED").ToString
tmp2dr.Item("DTPROMISED") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("DTPROMISED").ToString
If QDB_DS.Tables("InvoiceLines").Rows(i).Item("CARRIER").ToString = "CALL" Then
tmp2dr.Item("CARRIER") = "CALL1"
Else
tmp2dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("CARRIER").ToString
End If
tmp2dr.Item("SHIPQTY") = QDB_DS.Tables("InvoiceLines").Rows(i).Item("INVOICEQTY").ToString
tmp2dr.Item("STATUS") = 1
tmp2dr.Item("SENT") = 0
tmp2dr.Item("ETA") = ShippingInfo.TextBox3.Text
Exit For
End If
Next
Next
WPOT_DA.Update(WPOT_DS, "WPOT")
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
dr = INVOICES_DS.Tables("INVOICES").NewRow
dr.Item("DATE_ADDED") = Date.Now.ToString
dr.Item("INVOICE_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTINVOICED")
dr.Item("INVOICENUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("INVOICENUM").ToString
dr.Item("ORDER_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTORDER")
dr.Item("SHIPPED_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTSHIPPED")
dr.Item("PONUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PONUM").ToString
dr.Item("PO_TYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString
dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER").ToString
dr.Item("CARRIER_DESCRIPTION") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER_DESCRIPTION").ToString
dr.Item("SHIP_TO_NAME") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("NAME").ToString
dr.Item("SHIP_TO_ADDRESS1") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS1").ToString
dr.Item("SHIP_TO_ADDRESS2") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS2").ToString
dr.Item("SHIP_TO_CITY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CITY").ToString
dr.Item("SHIP_TO_STATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("STATE").ToString
dr.Item("SHIP_TO_ZIP") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ZIP").ToString
dr.Item("SHIP_TO_COUNTRY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("COUNTRY").ToString
dr.Item("DISCTYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCTYPE").ToString
dr.Item("DISCDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCDAYS").ToString
dr.Item("DISCRATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCRATE").ToString
dr.Item("NETDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUEDAYS").ToString
dr.Item("TERMS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUETYPE").ToString
dr.Item("TRANS_TYPE") = ShippingInfo.ComboBox1.Text
If ShippingInfo.ComboBox2.Text = "WT" Then
dr.Item("REF_CODE") = "BM"
Else
dr.Item("REF_CODE") = ShippingInfo.ComboBox2.Text
End If
dr.Item("REFNUM") = ShippingInfo.TextBox1.Text
dr.Item("SHIP_TO_GLN") = ShippingInfo.TextBox2.Text
For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
total_tax = total_tax + (QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT1") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT2") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT3") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT4"))
Next
dr.Item("TAX") = total_tax.ToString
INVOICES_DS.Tables("INVOICES").Rows.Add(dr)
INVOICES_DA.Update(INVOICES_DS, "INVOICES")
msg_id = dr.Item("MSG_ID")
For Each tmpdr As DataRow In QDB_DS.Tables("InvoiceLines").Rows
dr = INVOICE_LINE_DS.Tables("INVOICE_LINE").NewRow
dr.Item("MSG_ID") = msg_id
dr.Item("LINE_NUM") = tmpdr.Item("LINENUM")
dr.Item("QTY") = tmpdr.Item("INVOICEQTY")
dr.Item("UNITS") = "EA" 'tmpdr.Item("UNITS")
dr.Item("UNIT_PRICE") = tmpdr.Item("UNITPRICE")
dr.Item("CUSTOMER_ITEMNUM") = tmpdr.Item("CUSTITEMNUM")
dr.Item("VENDOR_ITEMNUM") = tmpdr.Item("ITEMNUM")
INVOICE_LINE_DS.Tables("INVOICE_LINE").Rows.Add(dr)
Next
INVOICE_LINE_DA.Update(INVOICE_LINE_DS, "INVOICE_LINE")
INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
INVOICE_LINE_DS.AcceptChanges()
INVOICES_DS.Tables("INVOICES").Clear()
INVOICES_DS.AcceptChanges()
INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
SentInvoicesDataGridView.Refresh()
End If
Else
MsgBox("******** PO is tracked but not found in PO tracking table." + vbCrLf + vbCrLf + _
"Contact your ******** EDI Administrator", MsgBoxStyle.Critical, "******** PO Tracking")
End If
Else
If ShippingInfo.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
dr = INVOICES_DS.Tables("INVOICES").NewRow
dr.Item("DATE_ADDED") = Date.Now.ToString
dr.Item("INVOICE_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTINVOICED")
dr.Item("INVOICENUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("INVOICENUM").ToString
dr.Item("ORDER_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTORDER")
dr.Item("SHIPPED_DATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DTSHIPPED")
dr.Item("PONUM") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PONUM").ToString
dr.Item("PO_TYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("PO_TYPE").ToString
dr.Item("CARRIER") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER").ToString
dr.Item("CARRIER_DESCRIPTION") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CARRIER_DESCRIPTION").ToString
dr.Item("SHIP_TO_NAME") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("NAME").ToString
dr.Item("SHIP_TO_ADDRESS1") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS1").ToString
dr.Item("SHIP_TO_ADDRESS2") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ADDRESS2").ToString
dr.Item("SHIP_TO_CITY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("CITY").ToString
dr.Item("SHIP_TO_STATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("STATE").ToString
dr.Item("SHIP_TO_ZIP") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("ZIP").ToString
dr.Item("SHIP_TO_COUNTRY") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("COUNTRY").ToString
dr.Item("DISCTYPE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCTYPE").ToString
dr.Item("DISCDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCDAYS").ToString
dr.Item("DISCRATE") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DISCRATE").ToString
dr.Item("NETDAYS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUEDAYS").ToString
dr.Item("TERMS") = QDB_DS.Tables("InvoiceLines").Rows(0).Item("DUETYPE").ToString
dr.Item("TRANS_TYPE") = ShippingInfo.ComboBox1.Text
If ShippingInfo.ComboBox2.Text = "WT" Then
dr.Item("REF_CODE") = "BM"
Else
dr.Item("REF_CODE") = ShippingInfo.ComboBox2.Text
End If
dr.Item("REFNUM") = ShippingInfo.TextBox1.Text
dr.Item("SHIP_TO_GLN") = ShippingInfo.TextBox2.Text
For i As Integer = 0 To QDB_DS.Tables("InvoiceLines").Rows.Count - 1
total_tax = total_tax + (QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT1") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT2") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT3") + _
QDB_DS.Tables("InvoiceLines").Rows(i).Item("TAXAMT4"))
Next
dr.Item("TAX") = total_tax.ToString
INVOICES_DS.Tables("INVOICES").Rows.Add(dr)
INVOICES_DA.Update(INVOICES_DS, "INVOICES")
msg_id = dr.Item("MSG_ID")
For Each tmpdr As DataRow In QDB_DS.Tables("InvoiceLines").Rows
dr = INVOICE_LINE_DS.Tables("INVOICE_LINE").NewRow
dr.Item("MSG_ID") = msg_id
dr.Item("LINE_NUM") = tmpdr.Item("LINENUM")
dr.Item("QTY") = tmpdr.Item("INVOICEQTY")
dr.Item("UNITS") = "EA" 'tmpdr.Item("UNITS")
dr.Item("UNIT_PRICE") = tmpdr.Item("UNITPRICE")
dr.Item("CUSTOMER_ITEMNUM") = tmpdr.Item("CUSTITEMNUM")
dr.Item("VENDOR_ITEMNUM") = tmpdr.Item("ITEMNUM")
INVOICE_LINE_DS.Tables("INVOICE_LINE").Rows.Add(dr)
Next
INVOICE_LINE_DA.Update(INVOICE_LINE_DS, "INVOICE_LINE")
INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
INVOICE_LINE_DS.AcceptChanges()
INVOICES_DS.Tables("INVOICES").Clear()
INVOICES_DS.AcceptChanges()
INVOICESTableAdapter.Fill(WediDataSet5.INVOICES)
SentInvoicesDataGridView.Refresh()
End If
End If
Else
MsgBox("Invoice Error", MsgBoxStyle.Exclamation, "******** EDI")
End If
End Sub
Public Sub setupInvoices()
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
DAInsertCmd = New SqlCommand("InsertInvoicesMSG_ID", INVOICES_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_ADDED", SqlDbType.DateTime, 8, "DATE_ADDED")
DAInsertCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
DAInsertCmd.Parameters.Add("@INVOICE_DATE", SqlDbType.Date, 3, "INVOICE_DATE")
DAInsertCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
DAInsertCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
DAInsertCmd.Parameters.Add("@SHIPPED_DATE", SqlDbType.Date, 3, "SHIPPED_DATE")
DAInsertCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
DAInsertCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
DAInsertCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 4, "CARRIER")
DAInsertCmd.Parameters.Add("@CARRIER_DESCRIPTION", SqlDbType.VarChar, 35, "CARRIER_DESCRIPTION")
DAInsertCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_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("@DISCTYPE", SqlDbType.VarChar, 2, "DISCTYPE")
DAInsertCmd.Parameters.Add("@DISCDAYS", SqlDbType.VarChar, 3, "DISCDAYS")
DAInsertCmd.Parameters.Add("@DISCRATE", SqlDbType.VarChar, 6, "DISCRATE")
DAInsertCmd.Parameters.Add("@NETDAYS", SqlDbType.VarChar, 3, "NETDAYS")
DAInsertCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
DAInsertCmd.Parameters.Add("@TAX", SqlDbType.VarChar, 18, "TAX")
DAInsertCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
DAInsertCmd.Parameters.Add("@TRANS_TYPE", SqlDbType.VarChar, 2, "TRANS_TYPE")
DAInsertCmd.Parameters.Add("@REF_CODE", SqlDbType.VarChar, 3, "REF_CODE")
DAInsertCmd.Parameters.Add("@REFNUM", SqlDbType.VarChar, 50, "REFNUM")
DAInsertCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
INVOICES_DA.InsertCommand = DAInsertCmd
DAUpdateCmd = New SqlCommand("UPDATE INVOICES SET DATE_ADDED = @DATE_ADDED, DATE_SENT = @DATE_SENT, INVOICE_DATE = @INVOICE_DATE, INVOICENUM = @INVOICENUM, ORDER_DATE = @ORDER_DATE, SHIPPED_DATE = @SHIPPED_DATE, PONUM = @PONUM, PO_TYPE = @PO_TYPE, CARRIER = @CARRIER, " + _
"CARRIER_DESCRIPTION = @CARRIER_DESCRIPTION, SHIP_TO_NAME = @SHIP_TO_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, " + _
"DISCTYPE = @DISCTYPE, DISCDAYS = @DISCDAYS, DISCRATE = @DISCRATE, NETDAYS = @NETDAYS, TERMS = @TERMS, TAX = @TAX, SENT = @SENT, TRANS_TYPE = @TRANS_TYPE, REF_CODE = @REF_CODE, " + _
"REFNUM = @REFNUM, SHIP_TO_GLN = @SHIP_TO_GLN WHERE MSG_ID = @MSG_ID", INVOICES_DA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
DAUpdateCmd.Parameters.Add("@DATE_ADDED", SqlDbType.DateTime, 8, "DATE_ADDED")
DAUpdateCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
DAUpdateCmd.Parameters.Add("@INVOICE_DATE", SqlDbType.Date, 3, "INVOICE_DATE")
DAUpdateCmd.Parameters.Add("@INVOICENUM", SqlDbType.VarChar, 22, "INVOICENUM")
DAUpdateCmd.Parameters.Add("@ORDER_DATE", SqlDbType.Date, 3, "ORDER_DATE")
DAUpdateCmd.Parameters.Add("@SHIPPED_DATE", SqlDbType.Date, 3, "SHIPPED_DATE")
DAUpdateCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 22, "PONUM")
DAUpdateCmd.Parameters.Add("@PO_TYPE", SqlDbType.VarChar, 50, "PO_TYPE")
DAUpdateCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 4, "CARRIER")
DAUpdateCmd.Parameters.Add("@CARRIER_DESCRIPTION", SqlDbType.VarChar, 35, "CARRIER_DESCRIPTION")
DAUpdateCmd.Parameters.Add("@SHIP_TO_NAME", SqlDbType.VarChar, 60, "SHIP_TO_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("@DISCTYPE", SqlDbType.VarChar, 2, "DISCTYPE")
DAUpdateCmd.Parameters.Add("@DISCDAYS", SqlDbType.VarChar, 3, "DISCDAYS")
DAUpdateCmd.Parameters.Add("@DISCRATE", SqlDbType.VarChar, 6, "DISCRATE")
DAUpdateCmd.Parameters.Add("@NETDAYS", SqlDbType.VarChar, 3, "NETDAYS")
DAUpdateCmd.Parameters.Add("@TERMS", SqlDbType.VarChar, 80, "TERMS")
DAUpdateCmd.Parameters.Add("@TAX", SqlDbType.VarChar, 18, "TAX")
DAUpdateCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
DAUpdateCmd.Parameters.Add("@TRANS_TYPE", SqlDbType.VarChar, 2, "TRANS_TYPE")
DAUpdateCmd.Parameters.Add("@REF_CODE", SqlDbType.VarChar, 3, "REF_CODE")
DAUpdateCmd.Parameters.Add("@REFNUM", SqlDbType.VarChar, 50, "REFNUM")
DAUpdateCmd.Parameters.Add("@SHIP_TO_GLN", SqlDbType.VarChar, 80, "SHIP_TO_GLN")
INVOICES_DA.UpdateCommand = DAUpdateCmd
INVOICES_DA.Fill(INVOICES_DS, "INVOICES")
INVOICES_DS.Tables("INVOICES").Clear()
INVOICES_DS.AcceptChanges()
Catch ex As Exception
myLog.WriteEntry("setupInvoices failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Public Sub setupInvoice_line()
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Try
DAInsertCmd = New SqlCommand("InsertInvoice_lineLINE_ID", INVOICE_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, 17, "UNIT_PRICE")
DAInsertCmd.Parameters.Add("@CUSTOMER_ITEMNUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEMNUM")
DAInsertCmd.Parameters.Add("@VENDOR_ITEMNUM", SqlDbType.VarChar, 48, "VENDOR_ITEMNUM")
INVOICE_LINE_DA.InsertCommand = DAInsertCmd
DAUpdateCmd = New SqlCommand("UPDATE INVOICE_LINE SET MSG_ID = @MSG_ID, LINE_NUM = @LINE_NUM, QTY = @QTY, UNITS = @UNITS, UNIT_PRICE = @UNIT_PRICE, CUSTOMER_ITEMNUM = @CUSTOMER_ITEMNUM, VENDOR_ITEMNUM = @VENDOR_ITEMNUM " + _
"WHERE LINE_ID = @LINE_ID", INVOICE_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, 17, "UNIT_PRICE")
DAUpdateCmd.Parameters.Add("@CUSTOMER_ITEMNUM", SqlDbType.VarChar, 48, "CUSTOMER_ITEMNUM")
DAUpdateCmd.Parameters.Add("@VENDOR_ITEMNUM", SqlDbType.VarChar, 48, "VENDOR_ITEMNUM")
INVOICE_LINE_DA.UpdateCommand = DAUpdateCmd
INVOICE_LINE_DA.Fill(INVOICE_LINE_DS, "INVOICE_LINE")
INVOICE_LINE_DS.Tables("INVOICE_LINE").Clear()
INVOICE_LINE_DS.AcceptChanges()
Catch ex As Exception
myLog.WriteEntry("setupInvoice_line failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Public Sub setupWpot()
Dim DAInsertCmd As SqlCommand
Dim DAUpdateCmd As SqlCommand
Dim DADeleteCmd As SqlCommand
Try
DAInsertCmd = New SqlCommand("InsertWPOT_LINE_ID", WPOT_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("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
DAInsertCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 30, "PONUM")
DAInsertCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 10, "STORENUM")
DAInsertCmd.Parameters.Add("@ORDERNUM", SqlDbType.VarChar, 20, "ORDERNUM")
DAInsertCmd.Parameters.Add("@DTREQUESTED", SqlDbType.DateTime, 8, "DTREQUESTED")
DAInsertCmd.Parameters.Add("@DTSHIPPED", SqlDbType.DateTime, 8, "DTSHIPPED")
DAInsertCmd.Parameters.Add("@DTPROMISED", SqlDbType.DateTime, 8, "DTPROMISED")
DAInsertCmd.Parameters.Add("@SHIP_TO_CODE", SqlDbType.VarChar, 3, "SHIP_TO_CODE")
DAInsertCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 45, "ADDRESS1")
DAInsertCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 45, "ADDRESS2")
DAInsertCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
DAInsertCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 30, "STATE")
DAInsertCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 5, "CARRIER")
DAInsertCmd.Parameters.Add("@PRONUM", SqlDbType.VarChar, 30, "PRONUM")
DAInsertCmd.Parameters.Add("@WAYBILLNUM", SqlDbType.VarChar, 30, "WAYBILLNUM")
DAInsertCmd.Parameters.Add("@ALTITEMNUM", SqlDbType.VarChar, 30, "ALTITEMNUM")
DAInsertCmd.Parameters.Add("@ITEMNUM", SqlDbType.VarChar, 30, "ITEMNUM")
DAInsertCmd.Parameters.Add("@ALTITEMDESC", SqlDbType.VarChar, 61, "ALTITEMDESC")
DAInsertCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
DAInsertCmd.Parameters.Add("@SHIPQTY", SqlDbType.VarChar, 15, "SHIPQTY")
DAInsertCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
DAInsertCmd.Parameters.Add("@PALLETID", SqlDbType.VarChar, 30, "PALLETID")
DAInsertCmd.Parameters.Add("@STATUS", SqlDbType.Bit, 1, "STATUS")
DAInsertCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
DAInsertCmd.Parameters.Add("@LABELS", SqlDbType.Bit, 1, "LABELS")
DAInsertCmd.Parameters.Add("@BATCHID", SqlDbType.VarChar, 65, "BATCHID")
DAInsertCmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 45, "COMPANY")
DAInsertCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 4, "COUNTRY")
DAInsertCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 12, "ZIP")
DAInsertCmd.Parameters.Add("@ETA", SqlDbType.VarChar, 4, "ETA")
WPOT_DA.InsertCommand = DAInsertCmd
DAUpdateCmd = New SqlCommand("UPDATE WPOT SET DATE_SENT = @DATE_SENT, PONUM = @PONUM, STORENUM = @STORENUM, ORDERNUM = @ORDERNUM, DTREQUESTED = @DTREQUESTED, DTSHIPPED = @DTSHIPPED, DTPROMISED = @DTPROMISED, SHIP_TO_CODE = @SHIP_TO_CODE, ADDRESS1 = @ADDRESS1, " + _
"ADDRESS2 = @ADDRESS2, CITY = @CITY, STATE = @STATE, CARRIER = @CARRIER, PRONUM = @PRONUM, WAYBILLNUM = @WAYBILLNUM, ALTITEMNUM = @ALTITEMNUM, ITEMNUM = @ITEMNUM, ALTITEMDESC = @ALTITEMDESC, " + _
"QTY = @QTY, SHIPQTY = @SHIPQTY, LINE_NUM = @LINE_NUM, PALLETID = @PALLETID, STATUS = @STATUS, SENT = @SENT, LABELS = @LABELS, BATCHID = @BATCHID, COMPANY = @COMPANY, COUNTRY = @COUNTRY, ZIP = @ZIP, ETA = @ETA WHERE LINE_ID = @LINE_ID", WPOT_DA.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add("@LINE_ID", SqlDbType.Int, 4, "LINE_ID")
DAUpdateCmd.Parameters("@LINE_ID").SourceVersion = DataRowVersion.Original
DAUpdateCmd.Parameters.Add("@DATE_SENT", SqlDbType.DateTime, 8, "DATE_SENT")
DAUpdateCmd.Parameters.Add("@PONUM", SqlDbType.VarChar, 30, "PONUM")
DAUpdateCmd.Parameters.Add("@STORENUM", SqlDbType.VarChar, 10, "STORENUM")
DAUpdateCmd.Parameters.Add("@ORDERNUM", SqlDbType.VarChar, 20, "ORDERNUM")
DAUpdateCmd.Parameters.Add("@DTREQUESTED", SqlDbType.DateTime, 8, "DTREQUESTED")
DAUpdateCmd.Parameters.Add("@DTSHIPPED", SqlDbType.DateTime, 8, "DTSHIPPED")
DAUpdateCmd.Parameters.Add("@DTPROMISED", SqlDbType.DateTime, 8, "DTPROMISED")
DAUpdateCmd.Parameters.Add("@SHIP_TO_CODE", SqlDbType.VarChar, 3, "SHIP_TO_CODE")
DAUpdateCmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 45, "ADDRESS1")
DAUpdateCmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 45, "ADDRESS2")
DAUpdateCmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30, "CITY")
DAUpdateCmd.Parameters.Add("@STATE", SqlDbType.VarChar, 30, "STATE")
DAUpdateCmd.Parameters.Add("@CARRIER", SqlDbType.VarChar, 5, "CARRIER")
DAUpdateCmd.Parameters.Add("@PRONUM", SqlDbType.VarChar, 30, "PRONUM")
DAUpdateCmd.Parameters.Add("@WAYBILLNUM", SqlDbType.VarChar, 30, "WAYBILLNUM")
DAUpdateCmd.Parameters.Add("@ALTITEMNUM", SqlDbType.VarChar, 30, "ALTITEMNUM")
DAUpdateCmd.Parameters.Add("@ITEMNUM", SqlDbType.VarChar, 30, "ITEMNUM")
DAUpdateCmd.Parameters.Add("@ALTITEMDESC", SqlDbType.VarChar, 61, "ALTITEMDESC")
DAUpdateCmd.Parameters.Add("@QTY", SqlDbType.VarChar, 15, "QTY")
DAUpdateCmd.Parameters.Add("@SHIPQTY", SqlDbType.VarChar, 15, "SHIPQTY")
DAUpdateCmd.Parameters.Add("@LINE_NUM", SqlDbType.VarChar, 20, "LINE_NUM")
DAUpdateCmd.Parameters.Add("@PALLETID", SqlDbType.VarChar, 30, "PALLETID")
DAUpdateCmd.Parameters.Add("@STATUS", SqlDbType.Bit, 1, "STATUS")
DAUpdateCmd.Parameters.Add("@SENT", SqlDbType.Bit, 1, "SENT")
DAUpdateCmd.Parameters.Add("@LABELS", SqlDbType.Bit, 1, "LABELS")
DAUpdateCmd.Parameters.Add("@BATCHID", SqlDbType.VarChar, 30, "BATCHID")
DAUpdateCmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 45, "COMPANY")
DAUpdateCmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 4, "COUNTRY")
DAUpdateCmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 12, "ZIP")
DAUpdateCmd.Parameters.Add("@ETA", SqlDbType.VarChar, 4, "ETA")
WPOT_DA.UpdateCommand = DAUpdateCmd
DADeleteCmd = New SqlCommand("DELETE FROM WPOT WHERE LINE_ID = @LINE_ID", WPOT_DA.SelectCommand.Connection)
DADeleteCmd.Parameters.Add("@LINE_ID", SqlDbType.VarChar, 20, "LINE_ID")
WPOT_DA.DeleteCommand = DADeleteCmd
WPOT_DA.Fill(WPOT_DS, "WPOT")
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
Catch ex As Exception
myLog.WriteEntry("setupWpot failed!" + vbCrLf + ex.ToString, System.Diagnostics.EventLogEntryType.Error)
End Try
End Sub
Private Sub SentInvoicesDataGridView_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvoicesDataGridView.CellContentClick
If e.RowIndex > -1 Then
If SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "SENTINVOICE_CHECKBOX" Then
transmission.invoice = True
If IsDBNull(SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value) Then
transmission.row = e.RowIndex
If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DATESENT").Value = Date.Now
End If
ElseIf SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value Then
SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = False
Else
transmission.row = e.RowIndex
If transmission.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = True
SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DATESENT").Value = Date.Now
End If
End If
INVOICESBindingSource.EndEdit()
INVOICESTableAdapter.Update(WediDataSet5.INVOICES)
End If
End If
End Sub
Private Sub SentInvoicesDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvoicesDataGridView.CellDoubleClick
If e.RowIndex > -1 Then
If Not SentInvoicesDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).OwningColumn.Name = "SENTINVOICE_CHECKBOX" Then
Dim tmpApp As New Excel.Application
Dim tmpWorkbook As Excel.Workbook
Dim tmpWorksheet As Excel.Worksheet
tmpWorkbook = tmpApp.Workbooks.Add
tmpWorksheet = tmpWorkbook.Sheets.Add
Dim INVOICELINE2_CON As SqlConnection = New SqlConnection("Data Source=********; Initial Catalog=********; User Id=********; Password=********")
Dim INVOICELINE2_DA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INVOICE_LINE WHERE MSG_ID=@MSG_ID", INVOICELINE2_CON)
Dim INVOICELINE2_DS As DataSet = New DataSet
Dim line_total As Double
Dim total As Double = 0
INVOICELINE2_DA.SelectCommand.Parameters.Add("@MSG_ID", SqlDbType.Int, 4, "MSG_ID")
With tmpWorksheet
.Range("a:i").Font.Size = 8
.Range("a:i").WrapText = False
.Rows.RowHeight = 14
.Range("1:2").RowHeight = 26
.Range("1:2").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("1:2").VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("1:2").Font.Bold = True
.Range("g3").Font.Bold = True
.Range("h3").Font.Bold = True
.Range("g7").Font.Bold = True
.Range("h7").Font.Bold = True
.Range("a8").Font.Bold = True
.Range("a16").Font.Bold = True
.Range("a17").Font.Bold = True
.Range("d17").Font.Bold = True
.Range("d19").Font.Bold = True
.Range("20:20").Font.Bold = True
.Range("20:20").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Name = "VI INVOICE#" + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICE_NUM").Value.ToString
.Range("a1:i1").Merge()
.Range("a1").Value = "******** EDI INVOICE"
.Range("a2:f2").Merge()
.Range("g2:i2").Merge()
.Range("a2").Value = "VENDOR INFORMATION"
.Range("a2").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("g2").Value = "INVOICE DETAILS"
.Range("g1").HorizontalAlignment = XlHAlign.xlHAlignCenter
For counter As Integer = 3 To 15
.Range("a" & counter & ":c" & counter).Merge()
.Range("d" & counter & ":f" & counter).Merge()
.Range("h" & counter & ":i" & counter).Merge()
.Range("h" & counter).HorizontalAlignment = XlHAlign.xlHAlignLeft
Next
.Range("a3").Value = "********"
.Range("d3").Value = "HOST Vendor # ********"
.Range("g3").Value = "PO Number: "
.Range("h3").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("PO_NUM").Value.ToString
.Range("g4").Value = "PO Type: "
.Range("h4").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("PO_TYPE").Value.ToString
.Range("a7").Value = "SOLD TO"
.Range("a8").Value = "********"
.Range("d7").Value = "SHIP TO"
.Range("g7").Value = "Invoice #: "
.Range("h7").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICE_NUM").Value.ToString
.Range("d8").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ST_NAME").Value.ToString
.Range("d9").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ADDRESS1").Value.ToString
.Range("d10").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ADDRESS2").Value.ToString
.Range("d11").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CITY").Value.ToString + " " + _
SentInvoicesDataGridView.Rows(e.RowIndex).Cells("STATE").Value.ToString + " " + _
SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ZIP").Value.ToString
.Range("d12").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("COUNTRY").Value.ToString
.Range("g8").Value = "Invoice Date: "
.Range("h8").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("INVOICEDATE").Value.ToString)
.Range("g9").Value = "Order Date: "
.Range("h9").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("ORDER_DATE").Value.ToString)
.Range("g10").Value = "Shipped Date: "
.Range("h10").Value = Date.Parse(SentInvoicesDataGridView.Rows(e.RowIndex).Cells("SHIPPED_DATE").Value.ToString)
.Range("g11").Value = "Terms:"
If SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCRATE").Value > 0 Then
.Range("h11").Value = (SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCRATE").Value * 100).ToString + "% " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("DISCDAYS").Value.ToString + ", NET " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("NETDAYS").Value.ToString
End If
.Range("a16:i16").Merge()
.Range("a16").RowHeight = 26
.Range("a16").HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("a16").VerticalAlignment = XlVAlign.xlVAlignCenter
.Range("a16").Value = "CARRIER INFORMATION"
.Range("a17:c17").Merge()
.Range("d17:f17").Merge()
.Range("g17:i17").Merge()
.Range("a17").Value = "SCAC: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CARRIER").Value.ToString
.Range("d17").Value = "DESCRIPTION: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("CARRIER_DESCRIPTION").Value.ToString
.Range("a18:c18").Merge()
.Range("d18:f18").Merge()
.Range("g18:i18").Merge()
.Range("a19:c19").Merge()
.Range("d19:f19").Merge()
.Range("g19:i19").Merge()
.Range("a18").Value = "Transportation Method: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TRANS_TYPE").Value.ToString()
.Range("a19").Value = "Tracking Type: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("REF_CODE").Value.ToString()
.Range("d19").Value = "Tracking Number: " + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("REFNUM").Value.ToString()
Dim count As Integer = 21
.Range("d" & count & ":f" & count).Merge()
.Range("b" & count).Value = "LINE ID"
.Range("c" & count).Value = "PART NUMBER"
.Range("d" & count).Value = "CUSTOMER PART NUMBER"
.Range("g" & count).Value = "QUANTITY"
.Range("h" & count).Value = "UNIT PRICE"
.Range("i" & count).Value = "EXTENDED PRICE"
INVOICELINE2_DA.SelectCommand.Parameters.Item("@MSG_ID").Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("MSG_ID").Value.ToString()
INVOICELINE2_DA.Fill(INVOICELINE2_DS, "INVOICE_LINE")
For Each dr As DataRow In INVOICELINE2_DS.Tables("INVOICE_LINE").Rows
line_total = 0
count = count + 2
.Range("d" & count & ":f" & count).Merge()
.Range("b" & count).HorizontalAlignment = XlHAlign.xlHAlignCenter
.Range("b" & count).Value = dr.Item("LINE_NUM").ToString
.Range("c" & count).NumberFormat = "@"
.Range("c" & count).Value = dr.Item("VENDOR_ITEMNUM").ToString
.Range("d" & count).NumberFormat = "@"
.Range("d" & count).Value = dr.Item("CUSTOMER_ITEMNUM").ToString
.Range("g" & count).HorizontalAlignment = XlHAlign.xlHAlignRight
.Range("g" & count).Value = dr.Item("QTY").ToString + " " + dr.Item("UNITS").ToString
.Range("h" & count).NumberFormat = "$#,##0.00"
.Range("h" & count).Value = dr.Item("UNIT_PRICE").ToString
.Range("i" & count).NumberFormat = "$#,##0.00"
line_total = dr.Item("UNIT_PRICE") * dr.Item("QTY")
.Range("i" & count).Value = line_total
total = total + line_total
Next
count = count + 2
.Range("h" & count).Value = "SUB-TOTAL"
.Range("i" & count).NumberFormat = "$#,##0.00"
.Range("i" & count).Font.Underline = True
.Range("i" & count).Value = total
count = count + 1
.Range("h" & count).Value = "TAX"
.Range("i" & count).NumberFormat = "$#,##0.00"
.Range("i" & count).Font.Underline = True
.Range("i" & count).Value = SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TAX").Value.ToString()
total = total + SentInvoicesDataGridView.Rows(e.RowIndex).Cells("TAX").Value
count = count + 2
.Range("h" & count).Value = "TOTAL"
.Range("i" & count).NumberFormat = "$#,##0.00"
.Range("i" & count).Value = total
.Range("A2:I2").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("A3:C15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("D3:F15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G3:I6").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G7:I10").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("G11:I15").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.Constants.xlNone
.Range("A16:I16").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
.Range("A17:i19").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.Constants.xlNone
.Range("A21:I21").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("A7:F7").Select()
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.Constants.xlNone
tmpApp.Selection.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.Constants.xlNone
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
With tmpApp.Selection.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = Excel.Constants.xlAutomatic
End With
.Range("a1").Select()
.Range("a:a").ColumnWidth = 4.71
.Range("b:b").ColumnWidth = 7
.Range("c:c").ColumnWidth = 20.16
.Range("d:f").ColumnWidth = 11.29
.Range("g:g").ColumnWidth = 9.71
.Range("h:h").ColumnWidth = 9.86
.Range("i:i").ColumnWidth = 14.14
.PageSetup.LeftMargin = 9.0
.PageSetup.RightMargin = 9.0
.PageSetup.TopMargin = 9.0
.PageSetup.BottomMargin = 9.0
.PageSetup.HeaderMargin = 9.0
.PageSetup.FooterMargin = 9.0
End With
tmpApp.Visible = True
End If
End If
End Sub
Private Sub SentInvoicesDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentInvoicesDataGridView.UserDeletedRow
INVOICESTableAdapter.Update(WediDataSet5)
End Sub
Private Sub SentInvsDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles SentInvsDataGridView.CellDoubleClick
If e.RowIndex > -1 Then
sent_invs.TextBox1.Text = SentInvsDataGridView.Rows(e.RowIndex).Cells("EDIDATADataGridViewTextBoxColumn1").Value.ToString()
sent_invs.Show()
End If
End Sub
Private Sub SentInvsDataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles SentInvsDataGridView.UserDeletedRow
SENT_INVSTableAdapter.Update(WediDataSet6)
End Sub
Private Sub ShippingMethodsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShippingMethodsToolStripMenuItem.Click
shippingcodes.Show()
End Sub
Private Sub InvoicingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoicingToolStripMenuItem.Click
edi_invoicing.Show()
End Sub
Private Sub AcknowledgeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AcknowledgeToolStripMenuItem.Click
ackcodes.Show()
End Sub
Public Function WPOT_AddSO(ByVal SalesOrder As String, ByVal Releasenum As String, ByVal row As Integer) As Boolean
Dim success As Boolean = False
Dim QDB_Con As DB2Connection = New DB2Connection("Database=********;User ID=********;Password=********;Server=********.********.com:50000;Persist Security Info=True")
Dim mySelectText As String = "SELECT * FROM QUANTUS.EMPLOYEE"
Dim QDB_DA As DB2DataAdapter = New DB2DataAdapter(mySelectText, QDB_Con)
Dim QDB_DS As DataSet = New DataSet()
Dim SelectShippedBLText As String
Dim dr As DataRow
Dim total_tax As Double = 0
WPOT_DA.SelectCommand.CommandText = "SELECT * FROM WPOT WHERE ORDERNUM = '" + SalesOrder.ToUpper + " " + Releasenum + "'"
If WPOT_DA.Fill(WPOT_DS, "WPOT") > 0 Then
If MsgBox("Sales Order has already been added to ******** PO Tracking table." + _
vbCrLf + "Labels will have to be reprinted." + _
vbCrLf + vbCrLf + "Delete old Sales Order from table and add the new one?", _
MsgBoxStyle.YesNo, "******** PO Tracking") = MsgBoxResult.Yes Then
For Each tmpdr2 As DataRow In WPOT_DS.Tables("WPOT").Rows
tmpdr2.Delete()
Next
WPOT_DA.Update(WPOT_DS, "WPOT")
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
Else
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
Return False
End If
End If
SelectShippedBLText = "SELECT RTRIM(QUANTUS.SALES_ORDER.CUSTPONUM) AS PONUM, RTRIM(QUANTUS.CUSTOMER.NAME1) AS NAME, " + _
"LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) AS ORDERNUM, LTRIM(QUANTUS.SALES_ORDER.RELEASE) AS RELEASE, " + _
"QUANTUS.SALES_ORDER.DTREQUESTED, QUANTUS.SALES_ORDER.DTPROMISED, " + _
"RTRIM(QUANTUS.CUSTOMER.MAILADDRESS1) AS ADDRESS1, RTRIM(QUANTUS.CUSTOMER.MAILADDRESS2) AS ADDRESS2, RTRIM(QUANTUS.CUSTOMER.MAILCITY) AS CITY, " + _
"RTRIM(QUANTUS.CUSTOMER.MAILPROV) AS STATE, RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) AS COUNTRY, RTRIM(QUANTUS.CUSTOMER.MAILPOSTAL) AS ZIP, " + _
"RTRIM(QUANTUS.SALES_ORDER.CARRIER) AS CARRIER, " + _
"LTRIM(QUANTUS.ORDER_LINE.ALTITEMNUM) AS ALTITEMNUM, LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) AS ITEMNUM, REPLACE(RTRIM(QUANTUS.ORDER_LINE.ALTITEMDESC), ',', ' ') AS ALTITEMDESC, " + _
"QUANTUS.ORDER_LINE.ORDERQTY AS QTY, QUANTUS.ORDER_LINE.SHIPQTY, QUANTUS.ORDER_LINE.LINESEQNO AS LINE_NUM, RTRIM(QUANTUS.SALES_ORDER.MISCINFO) AS PO_TYPE " + _
"FROM QUANTUS.SALES_ORDER, QUANTUS.ORDER_LINE, QUANTUS.CUSTOMER " + _
"WHERE QUANTUS.SALES_ORDER.BRANCH = QUANTUS.ORDER_LINE.BRANCH " + _
"AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM " + _
"AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.ORDER_LINE.RELEASE " + _
"AND QUANTUS.SALES_ORDER.SHIPBRANCH = QUANTUS.CUSTOMER.BRANCH " + _
"AND QUANTUS.SALES_ORDER.SHIPACCT = QUANTUS.CUSTOMER.CUSTOMERACCT " + _
"AND QUANTUS.ORDER_LINE.SOURCE = 'S' " + _
"AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) = '02' " + _
"AND LTRIM(QUANTUS.CUSTOMER.BILLINGACCT) = '********' " + _
"AND QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0 " + _
"AND LTRIM(QUANTUS.SALES_ORDER.ORDERNUM) = '" + SalesOrder.ToUpper + "' " + _
"AND LTRIM(QUANTUS.SALES_ORDER.RELEASE) = '" + Releasenum + "' " + _
"ORDER BY QUANTUS.ORDER_LINE.LINESEQNO ASC"
QDB_DA.SelectCommand.CommandText = SelectShippedBLText
If QDB_DA.Fill(QDB_DS, "OrderLines") > 0 Then
If PO_DataGridView.Rows(row).Cells("PONUM").Value.ToString.TrimStart("0") = QDB_DS.Tables("OrderLines").Rows(0).Item("PONUM").ToString Then
If PO_DataGridView.Rows(row).Cells("POTYPE").Value.ToString = QDB_DS.Tables("OrderLines").Rows(0).Item("PO_TYPE").ToString Then
For Each tmpdr As DataRow In QDB_DS.Tables("OrderLines").Rows
dr = WPOT_DS.Tables("WPOT").NewRow
dr.Item("PONUM") = tmpdr.Item("PONUM").ToString
dr.Item("STORENUM") = tmpdr.Item("NAME").ToString.Substring(tmpdr.Item("NAME").ToString.Length - 4, 4).TrimStart("0", "#")
dr.Item("ORDERNUM") = tmpdr.Item("ORDERNUM").ToString + " " + tmpdr.Item("RELEASE").ToString
dr.Item("DTREQUESTED") = tmpdr.Item("DTREQUESTED").ToString
If tmpdr.Item("DTPROMISED").ToString = "1/1/9999 12:00:00 AM" Then
MsgBox("Date Promised can't be blank.", MsgBoxStyle.Critical, "******** PO Tracking")
success = False
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
Return success
End If
dr.Item("DTPROMISED") = tmpdr.Item("DTPROMISED").ToString
dr.Item("SHIP_TO_CODE") = WPOT_Acks.ComboBox1.Text
dr.Item("ADDRESS1") = tmpdr.Item("ADDRESS1").ToString
dr.Item("ADDRESS2") = tmpdr.Item("ADDRESS2").ToString
dr.Item("CITY") = tmpdr.Item("CITY").ToString
dr.Item("STATE") = tmpdr.Item("STATE").ToString
If String.IsNullOrEmpty(tmpdr.Item("ALTITEMNUM").ToString) Then
MsgBox("Customer Item Number can't be blank.", MsgBoxStyle.Critical, "******** PO Tracking")
success = False
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
Return success
End If
dr.Item("ALTITEMNUM") = tmpdr.Item("ALTITEMNUM").ToString
dr.Item("ITEMNUM") = tmpdr.Item("ITEMNUM").ToString
dr.Item("ALTITEMDESC") = tmpdr.Item("ALTITEMDESC").ToString
dr.Item("QTY") = tmpdr.Item("QTY").ToString
dr.Item("SHIPQTY") = "0"
dr.Item("LINE_NUM") = tmpdr.Item("LINE_NUM").ToString
dr.Item("STATUS") = 0
dr.Item("SENT") = 0
dr.Item("LABELS") = 0
dr.Item("COMPANY") = tmpdr.Item("NAME").ToString
dr.Item("COUNTRY") = tmpdr.Item("COUNTRY").ToString
dr.Item("ZIP") = tmpdr.Item("ZIP").ToString
WPOT_DS.Tables("WPOT").Rows.Add(dr)
Next
WPOT_DA.Update(WPOT_DS, "WPOT")
WPOT_DS.Tables("WPOT").Clear()
WPOT_DS.AcceptChanges()
success = True
Else
MsgBox("Sales Order PO Type does not match PO", MsgBoxStyle.Critical, "******** PO Tracking")
End If
Else
MsgBox("Sales Order is for a different Purchase Order", MsgBoxStyle.Critical, "******** PO Tracking")
End If
Else
MsgBox("Can't find Sales Order", MsgBoxStyle.Critical, "******** PO Tracking")
End If
Return success
End Function
Public Function CheckPOTracked(ByVal PO_Type As String) As Boolean
Dim tracked As Boolean = False
If PO_Type = "ZGL0" Or _
PO_Type = "ZUS1" Or _
PO_Type = "ZUS2" Or _
PO_Type = "ZUS4" Or _
PO_Type = "ZUS5" Or _
PO_Type = "ZUS6" Or _
PO_Type = "ZUS8" Then
tracked = True
End If
Return tracked
End Function
Private Sub WPOT_DataGridView_UserDeletedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles WPOT_DataGridView.UserDeletedRow
WPOTTableAdapter.Update(WediDataSet7)
End Sub
Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
AboutBox1.ShowDialog()
End Sub
End Class
Public Class Line_items_SDQ
Public partnum As String
Public part_sdq As List(Of SDQ) = New List(Of SDQ)
End Class
Public Class SDQ
Public storenum As String
Public quantity As String
End Class