Private Sub Transactions1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim thisConnection As New iDB2Connection("Datasource=sub; Naming=System; LibraryList=QS36F")
Dim thisCommand As New iDB2Command("SELECT DATE(CAST(INT(MOD(DECIMAL(SUBSTR(HEX(SUBSTR(F00001,30,4)),1,7),7,0),10000)/100) || '/' ||" &
"MOD(DECIMAL(SUBSTR(HEX(SUBSTR(F00001,30,4)),1,7),7,0),100) || '/' || INT((19000000 + " &
"DECIMAL(SUBSTR(HEX(SUBSTR(F00001,30,4)),1,7),7,0))/10000) AS CHAR(10) CCSID 37)) AS Date," &
"DECIMAL(CAST(SUBSTR(HEX(SUBSTR(F00001,14,3)),1,5) AS CHAR(5) CCSID 37),5,0) AS Receipt," &
"DECIMAL(CAST(SUBSTR(HEX(SUBSTR(F00001,17,2)),1,3) AS CHAR(3) CCSID 37),3,0) AS Batch," &
"CAST(SUBSTR(F00001,2,2) AS CHAR(2) CCSID 37) AS TransCode," &
"CAST(SUBSTR(F00001,34,1) AS CHAR(1) CCSID 37) AS ATDC, " &
"DECIMAL(DECIMAL(CAST(SUBSTR(HEX(SUBSTR(F00001,23,5)),1,9) AS CHAR(9) CCSID 37),9,0)/100,9,2) " &
"* CASE WHEN CAST(SUBSTR(HEX(SUBSTR(F00001,27,1)),2,1) AS CHAR(1) CCSID 37) ='F' " &
"THEN 1 ELSE -1 END AS CREDIT, " &
"CASE WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='}' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '0'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='J' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '1'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='K' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '2'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='L' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '3'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='M' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '4'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='N' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '5'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='O' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '6'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='P' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '7'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='Q' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '8'),9,0)/100,9,2)*-1 " &
"WHEN CAST(SUBSTR(F00001,49,1) AS CHAR(1) CCSID 37) ='R' THEN " &
"DECIMAL(DECIMAL((CAST(SUBSTR(F00001,41,8) AS CHAR(8) CCSID 37) || '9'),9,0)/100,9,2)*-1 " &
"ELSE DECIMAL(DECIMAL(CAST(SUBSTR(F00001,41,9) AS CHAR(9) CCSID 37),9,0)/100,9,2) END AS BALANCE " &
"FROM ""UBA.MTD"" WHERE " &
"CAST(SUBSTR(F00001,5,9) AS CHAR(9) CCSID 37)=@uacct " &
"AND CAST(SUBSTR(F00001,2,2) AS CHAR(2) CCSID 37)<>'99' ORDER BY Date DESC, Receipt DESC, TransCode DESC", thisConnection)
Dim thisCommand1 As New iDB2Command("SELECT TTC,TDESC FROM ""UBA.TC""", thisConnection)
Dim thisReader As iDB2DataReader
Dim thisReader2 As iDB2DataReader
Try
thisConnection.Open()
thisCommand.DeriveParameters()
thisCommand.Parameters("@uacct").Value = CustInfo.Label68.Text
thisReader = thisCommand.ExecuteReader()
Dim dt As New DataTable()
dt.Load(thisReader)
thisReader.Close()
dt.Columns.Add("DEBIT", Type.GetType("System.Decimal"))
dt.Columns.Add("DESC", Type.GetType("System.String"))
dt.Columns(7).SetOrdinal(4)
dt.Columns(8).SetOrdinal(4)
thisReader2 = thisCommand1.ExecuteReader()
Dim dt2 As New DataTable()
dt2.Load(thisReader2)
thisReader2.Close()
Dim foundrow() As Data.DataRow
Dim transcode As String = ""
Dim i As Integer
For i = 0 To dt.Rows.Count - 1
transcode = CStr(dt.Rows(i).Item("TRANSCODE"))
foundrow = dt2.Select("TTC = '" & transcode & "'")
dt.Rows(i).Item("DESC") = foundrow(0).Item("TDESC")
Next
For x = 0 To dt.Rows.Count - 1
If CStr(dt.Rows(x).Item("ATDC")) = "D" Then
dt.Rows(x).Item("DEBIT") = dt.Rows(x).Item("CREDIT")
dt.Rows(x).Item("CREDIT") = 0
End If
Next
dgv1.DataSource = dt
thisCommand.Dispose()
thisCommand1.Dispose()
dgv1.Columns("ATDC").Visible = False
Label3.Text = CStr(Today)
Label4.Text = CStr(TimeOfDay)
Label6.Text = CustInfo.Label68.Text
Label8.Text = CustInfo.Label71.Text
Dim filterManger As New DgvFilterPopup.DgvFilterManager(dgv1)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
thisConnection.Close()
End Try
End Sub
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.