Queennie L
asked on
Import using VS 2010.net to CSV delimited file
This is my current code in VS 2010.net. This will import to CSV delimited file that would populate each line of ITEMS amount. (See attached .del txt file - APImport1111_This is the result of the my current code.txt)
What I want the result would be is to populate to CSV delimited file the TOTAL Amount of all the lines of ITEMS. (See attached .del txt file - APImport1111_This is the result I want.txt)
I appreciate the help.
APImport1111-This-is-the-result-of-the-m
APImport1111-This-is-the-result-I-want.t
Private Sub CreateImport()
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 acn 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 lsTransComment 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 vMcLaneInvoice As Boolean
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
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 = "Track"
UseTaxString = "000 NOT TAXABLE"
defaultifnull = "1"
trantype = "401"
detailtype = "D"
headertype = "V"
s = ";"
lscurpath = GetFilePath("VoucherImport")
vLastItemGL = ""
lsZeroItemFullGLNo = ""
detailDesc = ""
lsTransComment = "23842"
vHeaderFilter = ""
If Not GetFilter(vHeaderFilter, "Header") Then
Exit Sub
End If
acn.ConnectionString = aappConnString
acn.Open()
pbgoodpostingdate = False
lbgoodglnumbers = True
pdpostingdate = Today
Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
sqlstr = "Select glvalid from invoiceheader " & vHeaderFilter & " and glvalid = 0"
rs = New ADODB.Recordset
rs.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
If Not rs.EOF Then
retval = MsgBox("At least one of the invoices in the selected batch has an invalid G/L number on one of its detail lines. Do you want to continue posting anyway?", MsgBoxStyle.YesNo, "Invalid G/L Number-- Continue Posing?")
If retval = MsgBoxResult.No Then
Exit Sub
End If
End If
retval = MsgBox("All G/L numbers have been validated. Are you sure you want to post the selected invoices to table with a posting date of:" & pdpostingdate & "?(The posting date CAN be modified in table)", MsgBoxStyle.YesNo, "Post to table?")
If retval = MsgBoxResult.No Then
Exit Sub
End If
Dim noDups As Boolean
sqlstr = "Select * From Item Where Item.ItemNo IN (SELECT ItemNo FROM Item GROUP BY ItemNo HAVING Count( GLMainSegment) > 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( GLMainSegment) > 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
'This section will auto add plus 1 increment number:
Me.ToolStripStatusLabel1.Text = "Incrementing file number...."
Application.DoEvents()
sqlstr = "select * from invoicefileincrement"
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 = "APImport" & CStr(lncurfilenamecounter) & ".del"
fName = lscurpath & "\" & fName
'open file for output
FileOpen(1, fName, OpenMode.Output)
sqlstr = "Select * from invoiceheader " & vHeaderFilter & " and postedac = 0 order by invoiceno,houseno,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
'check Trantype before writing header rec
If rsheader.Fields("invoicetotal").Value < 0 Then
trantype = "402" 'Debit Memo
Else
trantype = "401"
End If
ldcurinvoicedate = rsheader.Fields("invoicedate").Value
lscurinvoicedate = Trim(CStr(ldcurinvoicedate))
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 & lsTransComment & s & lscurinvoicedate & s & lsCurInvoiceNo & s & trantype & s & s & defaultifnull & s & lsuserid & s & s & s & s & s & s & 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 invoicedetail.*," & " (Select GLMainSegment From Item Where Item.ItemNo =" & " InvoiceDetail.ItemNo) as ItemGL" & " From invoicedetail where invoiceno = '" & lsCurInvoiceNo & "'" & " and VendorID = '" & lsVendorID & "'" & " and postedtoac = 0" & " Order By ItemGL"
rsDetail.Open(sqlstr, pcn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
If Not rsDetail.EOF Then
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 trantype = "401" Then
lnZeroItemTotalPrice = System.Math.Round(System.Math.Abs(lnZeroItemTotalPrice), 2)
lnZeroItemTotalQty = System.Math.Round(System.Math.Abs(lnZeroItemTotalQty), 2)
End If
lsZeroItemTotalPrice = System.Math.Round(System.Math.Abs(lnZeroItemTotalPrice), 2)
lsZeroItemTotalQty = System.Math.Round(System.Math.Abs(lnZeroItemTotalQty), 2)
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 = lnZeroItemTotalPrice + System.Math.Round(rsDetail.Fields("extendedprice").Value, 2)
lnZeroItemTotalQty = lnZeroItemTotalQty + System.Math.Round(rsDetail.Fields("qtyordered").Value, 2)
If Trim(Nz(rsDetail.Fields("ProdDesc").Value, "")) <> "" Then
detailDesc = Trim(Nz(rsDetail.Fields("houseno").Value, "")) & "-" & Trim(Nz(rsDetail.Fields("ProdDesc").Value, ""))
Else
detailDesc = ""
End If
rsDetail.MoveNext()
Loop
If trantype = "401" Then
lsZeroItemTotalPrice = CDbl(System.Math.Round(lnZeroItemTotalPrice, 2))
lsZeroItemTotalQty = CStr(System.Math.Round(lnZeroItemTotalQty, 2))
End If
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 &
rsDetail.Close()
rsheader.MoveNext()
Loop
Me.ToolStripStatusLabel1.Text = "Posting Complete"
Application.DoEvents()
FileClose(1)
'rsacct.Close()
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 Try
End Sub
What I want the result would be is to populate to CSV delimited file the TOTAL Amount of all the lines of ITEMS. (See attached .del txt file - APImport1111_This is the result I want.txt)
I appreciate the help.
APImport1111-This-is-the-result-of-the-m
APImport1111-This-is-the-result-I-want.t
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it took me awhile to accept this.
Thanks.
Thanks.
ASKER
I want to populate the sum total of invoice not per line item.
Thank you.