1Imports System.IO
2Imports EnterpriseDT.Net.Ftp
3Imports Microsoft.VisualBasic
4
5Private Structure FTPInfo
6 Public UserName As String
7 Public Password As String
8 Public Host As String
9 End Structure
10
11Private Sub ExportXML()
12 'Dim objDataSet As New System.Data.DataSet("AmtechToASW")
13 'Dim objXmlDocument As New System.Xml.XmlDocument
14 'Dim objCmd As System.Data.OleDb.OleDbDataAdapter
15 'Dim str As String
16 'Dim openedconn As Boolean = False
17
18 'Try
19
20
21 'If oledbObjConn.State = ConnectionState.Closed Then
22 ' oledbObjConn.Open()
23 ' openedconn = True
24 'End If
25 'str = "SELECT * FROM tblRentalASW"
26 'objCmd = New System.Data.OleDb.OleDbDataAdapter(str, oledbObjConn)
27 'objCmd.Fill(objDataSet, "tblRentalASW")
28
29 'str = "SELECT * FROM tblRentalASW"
30 'objCmd = New System.Data.OleDb.OleDbDataAdapter(str, oledbObjConn)
31 'objCmd.Fill(objDataSet, "tblRentalASW")
32
33 'oledbObjConn.Close()
34
35 'objXmlDocument.LoadXml(objDataSet.GetXml())
36
37 'If File.Exists(xmlFile) Then
38 ' File.Delete(xmlFile)
39 'End If
40 ' objXmlDocument.Save(xmlFile)
41 'Catch ex As Exception
42 ' MsgBox("There was an error: " & ex.Message & ex.StackTrace)
43 'Finally
44 ' If openedconn Then oledbObjConn.Close()
45 'End Try
46
47 'Dim poNum As String
48 'Dim jobNum As String
49 'Dim filename As String
50
51
52 Dim str As String
53 Dim f As System.IO.File
54 Dim fso As System.IO.StreamWriter
55 'need to get the customer information for the header
56
57 If File.Exists(xmlFile) Then
58 File.Delete(xmlFile)
59 End If
60
61 fso = f.CreateText(xmlFile)
62
63 fso.WriteLine("<?xml version=""1.0"" encoding=""UTF-8""?>")
64 fso.WriteLine("<ASWNotifier>")
65 fso.WriteLine(" <Invoices>")
66 GetInvoices(fso)
67 fso.WriteLine(" </Invoices>")
68 fso.WriteLine("</ASWNotifier>")
69 fso.Close()
70
71
72 End Sub
73
74 Private Sub GetInvoices(ByRef fso As StreamWriter)
75 Dim str As String = "SELECT tblInvoicePerm.*, tblAddress.AddressCode, tblAddress.Address, tblAddress.City, tblAddress.State, tblAddress.Zip, tblAddress.AddressCode, tblAddress.Contact, tblAddress.Phone, tblCustomer.Customer, tblCustomer.RepCode " _
76 & " FROM (tblInvoicePerm LEFT JOIN tblAddress ON tblInvoicePerm.AddressCode = tblAddress.AddressCode) " _
77 & " LEFT JOIN tblCustomer ON tblInvoicePerm.CustNum = tblCustomer.CustNum;"
78 Dim cmd As OleDb.OleDbCommand
79 Dim rs As OleDb.OleDbDataReader
80 Dim invoice As InvoiceInfo
81 Dim invoiceArr As New ArrayList
82 Dim openedconn As Boolean = False
83
84 Try
85 If oledbObjConn.State = ConnectionState.Closed Then
86 oledbObjConn.Open()
87 openedconn = True
88 End If
89 cmd = New OleDb.OleDbCommand(str, oledbObjConn)
90 rs = cmd.ExecuteReader()
91
92 While rs.Read
93 invoice = New InvoiceInfo
94 'Add in all the invoice information for the ship to address
95 invoice.invoiceNum = rs("InvoiceNum")
96 invoice.orderNum = rs("OrderNum")
97 invoice.customer = rs("Customer")
98 invoice.invoiceDate = rs("InvoiceDate")
99 invoice.custNum = rs("CustNum")
100 invoice.address = rs("address")
101 invoice.city = rs("city")
102 invoice.state = rs("state")
103 invoice.zip = rs("zip")
104 invoice.phone = rs("phone")
105 invoice.total = rs("total")
106 'invoice.contact = rs("contact")
107 invoiceArr.Add(invoice)
108 End While
109 rs.Close()
110
111 Catch ex As Exception
112 MsgBox("Error: " & ex.Message & " " & ex.StackTrace)
113 Finally
114 If openedconn Then oledbObjConn.Close()
115 End Try
116
117 For Each invoice In invoiceArr
118 fso.WriteLine(" <invoice>")
119 fso.WriteLine(" <InvoiceNum>" & invoice.invoiceNum & "</InvoiceNum>")
120 fso.WriteLine(" <OrderNum>" & invoice.orderNum & "</OrderNum>")
121 fso.WriteLine(" <Customer>" & invoice.customer & "</Customer>")
122 fso.WriteLine(" <InvoiceDate>" & invoice.invoiceDate & "</InvoiceDate>")
123 fso.WriteLine(" <CustNum>" & invoice.custNum & "</CustNum>")
124 fso.WriteLine(" <total>" & invoice.total & "</total>")
125 fso.WriteLine(" <addresscode>" & invoice.addressCode & "</addresscode>")
126 fso.WriteLine(" <address>" & invoice.address & "</address>")
127 fso.WriteLine(" <city>" & invoice.city & "</city>")
128 fso.WriteLine(" <state>" & invoice.state & "</state>")
129 fso.WriteLine(" <zip>" & invoice.zip & "</zip>")
130 'fso.WriteLine(" <contact>" & invoice.contact & "</contact>")
131 fso.WriteLine(" <phone>" & invoice.phone & "</phone>")
132 fso.WriteLine(" <repairAmount>" & invoice.repairAmount & "</repairAmount>")
133 fso.WriteLine(" <lostAmount>" & invoice.lostAmount & "</lostAmount>")
134 fso.WriteLine(" <lines>")
135 GetLines(fso, invoice)
136 fso.WriteLine(" </lines>")
137 fso.WriteLine(" </invoice>")
138 Next
139 End Sub
140
141 Private Sub GetLines(ByRef fso As StreamWriter, ByVal invoice As InvoiceInfo)
142 Dim str As String = "SELECT * FROM tblRentalPERM WHERE (InvoiceNum = '" & invoice.invoiceNum & "')"
143 Dim cmd As OleDb.OleDbCommand
144 Dim rs As OleDb.OleDbDataReader
145 Dim openedconn As Boolean = False
146
147 Try
148 If oledbObjConn.State = ConnectionState.Closed Then
149 oledbObjConn.Open()
150 openedconn = True
151 End If
152 cmd = New OleDb.OleDbCommand(str, oledbObjConn)
153 rs = cmd.ExecuteReader()
154
155
156 While rs.Read
157 fso.WriteLine(" <line>")
158 fso.WriteLine(" <BarCode>" & rs("BarCode") & "</BarCode>")
159 fso.WriteLine(" <timeOut>" & rs("timeOut") & "</timeOut>")
160 fso.WriteLine(" <timeIn>" & rs("timeIn") & "</timeIn>")
161 fso.WriteLine(" <NumDays>" & rs("NumDays") & "</NumDays>")
162 fso.WriteLine(" <amount>" & rs("amount") & "</amount>")
163 fso.WriteLine(" <damaged>" & rs("damaged") & "</damaged>")
164 fso.WriteLine(" </line>")
165 End While
166 rs.Close()
167
168 Catch ex As Exception
169 MsgBox("Error: " & ex.Message & " " & ex.StackTrace)
170 Finally
171 If openedconn Then oledbObjConn.Close()
172 End Try
173 End Sub
174
175 Private Sub SendXMLtoFTP()
176 Dim ftp As FTPClient
177 Dim info As FTPInfo = GetFTPInfo()
178
179 Try
180 ftp = New FTPClient(info.Host)
181 ftp.Login(info.UserName, info.Password)
182 ftp.ConnectMode = FTPConnectMode.PASV
183 ftp.TransferType = FTPTransferType.ASCII
184 ftp.ChDir("/victoryftp/SKRental")
185 ftp.Put(xmlFile, "RentalASWnotifier.xml")
186 ftp.Quit()
187 Catch ex As Exception
188 MsgBox("There was an error: " & ex.Message)
189 End Try
190 End Sub
191
192 Private Function GetFTPInfo() As FTPInfo
193 Dim info As New FTPInfo
194 Dim openedconn As Boolean
195 Try
196 If oledbObjConn.State = ConnectionState.Closed Then
197 oledbObjConn.Open()
198 openedconn = True
199 End If
200 Dim str As String = "SELECT * FROM tblFTPInfo"
201 Dim cmd As New OleDb.OleDbCommand(str, oledbObjConn)
202 Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
203
204 If reader.Read() Then
205 info.Host = reader("IPAddress")
206 info.UserName = reader("UserName")
207 info.Password = reader("Password")
208 Else
209 Throw New Exception("No data found")
210 End If
211 Catch ex As Exception
212 MsgBox("Error retriving FTP info: " & ex.Message & vbCrLf & ex.StackTrace)
213 Finally
214 If openedconn = True Then oledbObjConn.Close()
215 End Try
216
217 Return info
218 End Function