Queennie L
asked on
Sum of numbers in CSV text import
This is about to import output to CSV text file using VB.net from SQL Server table.
My problem is when output result to CSV text file does not calculate based on a column fields ("fullglacctno") and "BatchNo".
I don't know what I am missing in my code.
Below is my vb.net code:
Please help.
Thank you.
SQL-table-and-OUTPUT-Result.xlsx
Import09242015.txt
Import09242015_This-output-what-I-ne.txt
My problem is when output result to CSV text file does not calculate based on a column fields ("fullglacctno") and "BatchNo".
I don't know what I am missing in my code.
Below is my vb.net code:
Dim lscurpath As String
Dim fName As String
Dim rsinvincrem As New ADODB.Recordset
Dim lbgoodglnumbers As Boolean
Dim retval As MsgBoxResult
Dim lsCurInvoiceNo As String
Dim rs As ADODB.Recordset
Dim rsDetail As New ADODB.Recordset
Dim rsheader As New ADODB.Recordset
Dim rsacct As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim acuitycn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim s As String
Dim headertype As String
Dim detailtype As String
Dim trantype As String
Dim defaultifnull As String
Dim lscurcompanyid As String
Dim lsVendorID As String
Dim lsCurComment As String
Dim lnZeroItemTotalPrice As Single
Dim lsZeroItemTotalPrice As String
Dim lnZeroItemTotalQty As Single
Dim lsZeroItemTotalQty As String
Dim lsZeroItemFullGLNo As String
Dim lsCommentVal As String
Dim lsuserid As String
Dim lscurinvoicedate As String
Dim ldcurinvoicedate As Date
Dim vLastItemGL As String
Dim UseTaxString As String
Dim pbgoodpostingdate As Boolean
Dim lncurfilenamecounter As Integer
Dim fItems As New frmItems
Dim vHeaderFilter As String
Dim sqlstr As String
Dim detailDesc As String
Dim lsCurCommentsMain As String
Dim lsCurCommentsCover As String
Dim lsCurCommentsAccountNo As String
Dim lsInvoiceDueDate As Date
Dim lnInvoiceDueDate As Date
Try
If Trim(Me.cboVendor.Text) = "" And Trim(Me.cboVendorClass.Text) = "" Then
retval = MsgBox("You must first enter a Vendor.", MsgBoxStyle.OkOnly, "Error")
Exit Sub
End If
lsuserid = "admin"
lsCommentVal = "0"
lscurcompanyid = "HOME"
UseTaxString = "000 NOT TAXABLE"
defaultifnull = "1"
trantype = "101"
detailtype = "D"
headertype = "V"
s = ";"
lscurpath = GetFilePath("Import")
vLastItemGL = ""
lsZeroItemFullGLNo = ""
detailDesc = ""
vHeaderFilter = ""
If Not GetFilter(vHeaderFilter, "Header") Then
Exit Sub
End If
cn.ConnectionString = ConnString
cn.Open()
pbgoodpostingdate = False
lbgoodglnumbers = True
pdpostingdate = Today
Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
sqlstr = "Select glvalid from tblheader " & vHeaderFilter & " and glvalid = 0"
rs = New ADODB.Recordset
rs.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
Dim noDups As Boolean
sqlstr = "Select * From Item Where Item.ItemNo IN (SELECT ItemNo FROM Item GROUP BY ItemNo HAVING Count( Segment) > 1)"
rsDetail.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
noDups = rsDetail.EOF
rsDetail.Close()
While retval = MsgBoxResult.Yes And noDups = False
MsgBox("Duplicate item numbers have been detected in the items table. Please correct before continuing.", MsgBoxStyle.ApplicationModal)
Me.Hide()
fItems.RefreshDGVData(" Where Item.ItemNo IN (SELECT ItemNo FROM Item GROUP BY ItemNo HAVING Count( Segment) > 1)")
fItems.ShowDialog()
fItems.Dispose()
Me.Show()
retval = MsgBox("Continue import file creation?", MsgBoxStyle.YesNo)
If retval = MsgBoxResult.No Then
Exit Sub
End If
rsDetail.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
noDups = rsDetail.EOF
rsDetail.Close()
End While
'let the posting begin
Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
Me.ProgressBar1.Value = 1
Me.ToolStripStatusLabel1.Text = "Incrementing file number...."
Application.DoEvents()
sqlstr = "select * from invoiceincrement"
rsinvincrem.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
rsinvincrem.MoveLast()
lncurfilenamecounter = rsinvincrem.Fields("filenamecounter").Value + 1
rsinvincrem.Fields("filenamecounter").Value = lncurfilenamecounter
rsinvincrem.Update()
rsinvincrem.Close()
fName = "Import" & CStr(lncurfilenamecounter) & ".txt"
fName = lscurpath & "\" & fName
'open file for output
FileOpen(1, fName, OpenMode.Output)
sqlstr = "Select [invoiceno]" & _
",[invoicedate]" & _
",[shipdate]" & _
",SUM([invpretaxtotal]) AS InvPretaxTotal" & _
",[invoicetax]" & _
",SUM([invoicetotal]) AS InvoiceTotal" & _
",[recorddate]" & _
",[manualadjustment]" & _
",[postedacuity]" & _
",[postingdate]" & _
",[glvalidated]" & _
",[glvalid]" & _
",[AccountNo]" & _
",[VendorID]" & _
"FROM tblheader " & vHeaderFilter & " and postedacuity = 0" & _
"GROUP BY invoiceno, invoicedate, shipdate, invoicetax, recorddate,manualadjustment, postedacuity, postingdate, glvalidated, glvalid, AccountNo, VendorID " & _
"ORDER by invoiceno,invoicedate"
rsheader.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly)
Do Until rsheader.EOF
Me.ToolStripStatusLabel1.Text = "Creating import records for invoice: " & rsheader.Fields("invoiceno").Value
Application.DoEvents()
If Me.ProgressBar1.Value < 95 Then
Me.ProgressBar1.Value = Me.ProgressBar1.Value + 5
Else
Me.ProgressBar1.Value = 1
End If
lsCurInvoiceNo = rsheader.Fields("invoiceno").Value
lsVendorID = rsheader.Fields("VendorID").Value
lsCurCommentsAccountNo = rsheader.Fields("AccountNo").Value
lsCurCommentsCover = "DAL 1111"
lsCurCommentsMain = lsCurCommentsCover
'check Trantype before writing header rec
If rsheader.Fields("InvoiceTotal").Value < 0 Then
trantype = "102" 'Debit Memo
Else
trantype = "101"
End If
ldcurinvoicedate = rsheader.Fields("invoicedate").Value
lscurinvoicedate = Trim(CStr(ldcurinvoicedate))
If lsInvoiceDueDate.DayOfWeek <> DayOfWeek.Monday Then
While lsInvoiceDueDate.DayOfWeek <> DayOfWeek.Monday
lsInvoiceDueDate = lsInvoiceDueDate.AddDays(1)
End While
End If
lnInvoiceDueDate = Trim(CStr(lsInvoiceDueDate.AddDays(7)))
PrintLine(1, headertype & s & s & s & lsVendorID & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & lsVendorID & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & lsCurCommentsMain & s & lscurinvoicedate & s & lsCurInvoiceNo & s & trantype & s & s & defaultifnull & s & lsuserid & s & s & s & s & s & s & lnInvoiceDueDate & s & s & s & s & s & s & s & s)
Me.ToolStripStatusLabel1.Text = "Writing to import file....."
Application.DoEvents()
lsCurComment = "Import - Invoice No:" & Trim(lsCurInvoiceNo)
sqlstr = "select tbldetail.*," & " (Select Segment From Item Where Item.ItemNo =" & " tblDetail.ItemNo) as ItemGL" & " From tbldetail where invoiceno = '" & lsCurInvoiceNo & "'" & " and VendorID = '" & lsVendorID & "'" & " and postedtoacuity = 0" & " Order By ItemGL"
rsDetail.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
If Not rsDetail.EOF Then
lsZeroItemFullGLNo = rsDetail.Fields("fullglacctno").Value
vLastItemGL = lsZeroItemFullGLNo
End If
lnZeroItemTotalPrice = 0
lnZeroItemTotalQty = 0
Do While Not rsDetail.EOF
If Me.ProgressBar1.Value < 95 Then
Me.ProgressBar1.Value = Me.ProgressBar1.Value + 5
Else
Me.ProgressBar1.Value = 1
End If
If vLastItemGL <> lsZeroItemFullGLNo Then
If lnZeroItemTotalPrice > 0 Or lnZeroItemTotalQty > 0 Then
If trantype = "101" Then
lnZeroItemTotalPrice = CDbl(System.Math.Round(System.Math.Abs(lnZeroItemTotalPrice), 2))
End If
lsZeroItemTotalPrice = CDbl(CStr(System.Math.Round(System.Math.Abs(lnZeroItemTotalPrice), 2)))
lsZeroItemTotalQty = 1
PrintLine(1, detailtype & s & s & lsCommentVal & s & s & lsZeroItemTotalPrice & s & s & s & lsCurComment & s & s & s & lsZeroItemTotalQty & s & s & vLastItemGL & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & UseTaxString & s & s & s & s & s & s & s & s & s & s)
End If
lnZeroItemTotalPrice = 0
lnZeroItemTotalQty = 0
vLastItemGL = lsZeroItemFullGLNo
End If
lnZeroItemTotalPrice = CDbl(lnZeroItemTotalPrice + System.Math.Round(rsDetail.Fields("extendedprice").Value, 2))
lnZeroItemTotalQty = 1
rsDetail.MoveNext()
Loop
If lnZeroItemTotalPrice <> 0 Or lnZeroItemTotalQty <> 0 Then
If trantype = "101" Then
lnZeroItemTotalPrice = CDbl(System.Math.Round(System.Math.Abs(lnZeroItemTotalPrice), 2))
lnZeroItemTotalQty = 1
End If
lsZeroItemTotalPrice = CDbl(System.Math.Round(lnZeroItemTotalPrice, 2))
lsZeroItemTotalQty = 1
If CDbl(lsZeroItemTotalPrice) <> 0 Or CDbl(lsZeroItemTotalQty) <> 0 Then
PrintLine(1, detailtype & s & s & lsCommentVal & s & s & lnZeroItemTotalPrice & s & s & s & lsCurComment & s & s & s & lnZeroItemTotalQty & s & s & lsZeroItemFullGLNo & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & s & UseTaxString & s & s & s & s & s & s & s & s & s & s)
End If
End If
rsDetail.Close()
rsheader.MoveNext()
Loop
Me.ToolStripStatusLabel1.Text = "Posting Complete"
Application.DoEvents()
FileClose(1)
Catch ex As Exception
MessageBox.Show(ex.ToString, "Error")
Finally
Me.ProgressBar1.Value = 1
Me.ProgressBar1.Visible = False
Me.Cursor = System.Windows.Forms.Cursors.Default
End TryPlease help.
Thank you.
SQL-table-and-OUTPUT-Result.xlsx
Import09242015.txt
Import09242015_This-output-what-I-ne.txt
ste5an
Pretty overkill for an simple CSV export. Refactor your code to separate data loading from the export code...
ASKER
How to refactor the code to separate data loading from the export code?
Can you redirect me?
Thanks.
Can you redirect me?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it working.
I believed my code is overkill. I tried to accomplish something but it turned out to be a confusing.
Thank you for your input. I rewrote to a simpler code.
I really appreciate your time and help.
I believed my code is overkill. I tried to accomplish something but it turned out to be a confusing.
Thank you for your input. I rewrote to a simpler code.
I really appreciate your time and help.
ASKER
Thank you again.