Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Datatable & Report Printing

Hello Experts,

Im new to VB and need your advice on my current project, Im working an app that generates invocies for our customers, and saves them as pdf. we have a SQL backend holding all the data. my problem is that when I send to print 15K invoices it takes too long to generate the pdf.  Here is where im looking for your advise..

Here is what im doing now and my table structure

custtable  servicetypetbl  detailsofservctbl  dynamicmsgtbl

each cust will have 1 or many services and each service will have multiple details.

what im doing now is, I query the first 2000 id's from the customer table into a datatable, then i do a for each whit the resiults  and query each of the tables where cust id match and put those resutls into datables.
something like this:

select top 2000 ID from custable
 c as datatable from above select
     for each in mem in c  
             
                   here i create another dataadapter and fill a dataset with a select where id = c for
                   each of the following tables servicetypetbl  detailsofservctbl  dynamicmsgtbl

This works fine and generates my pdf's. my problem is that when i try to print 30K invoices takes for ever in excess of 4 hours.

 I'm sure there is  a way to avoid soo many tris back and forth to the database - any suggestions are greatly appretitated.

Regards,







0
hrodriguez761
Asked:
hrodriguez761
  • 9
  • 8
1 Solution
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi, can you show your code? I need to know how you generate the pdf files, may be you can generate its by using threads, Im sure that can reduce the time required to generate your files.
0
 
hrodriguez761Author Commented:
yv989c,

thanks for your quick response here is a portion of the code for the printing process.. hopes this gives you a better picture
Dim intro As String  'get id from db after record is inserted.
        Dim intropd As SqlDataAdapter
        Dim introset As New DataSet("getmember")
        'intro = "select id from member where id = '188'"
        intro = "select top 100 id from member"
        intropd = New SqlDataAdapter(intro, con)
        intropd.Fill(introset, "getmember")
        Dim introtable As DataTable
        introtable = introset.Tables("getmember")
        Dim membrid As Integer
        Dim membrid2 As String
        Dim page1 As New PDFCreationOptions
        page1.LeftMargin = 1
        page1.TopMargin = 1
        page1.RightMargin = 1
        page1.BottomMargin = 1
        Dim doc As New PDFDocument("c:\test\invs_long2.pdf", page1)
        For Each membrow In introtable.Rows
            membrid2 = introtable.Rows.IndexOf(membrow)
            membrid = introtable.Rows(membrid2).Item(0).ToString
              
            Dim sql2 As String
            Dim mydatapd As SqlDataAdapter
            Dim mydataset As New DataSet("accdetail")
            sql2 = "select * from accdetail where custid = '" & membrid & "'"
            mydatapd = New SqlDataAdapter(sql2, con)
            mydatapd.Fill(mydataset, "accdetail")
            Dim accdet As DataTable
            accdet = mydataset.Tables("accdetail")

            Dim sql3 As String  'get id from db after record is inserted.
            Dim mydatapd2 As SqlDataAdapter
            Dim mydataset2 As New DataSet("customer")
            sql3 = "select * from member where id = '" & membrid & "'"
            mydatapd2 = New SqlDataAdapter(sql3, con)
            mydatapd2.Fill(mydataset2, "member")
            Dim custable As DataTable
            custable = mydataset2.Tables("member")
            
            'accounts table
            Dim sql4 As String  'get id from db after record is inserted.
            Dim mydatapd4 As SqlDataAdapter
            Dim mydataset4 As New DataSet("account")
            sql4 = "select * from accounts where custid = '" & membrid & "'"
            mydatapd4 = New SqlDataAdapter(sql4, con)
            mydatapd4.Fill(mydataset4, "account")
            Dim accstable As DataTable
            accstable = mydataset4.Tables("account")
            
            'servicetype1 table
            Dim sql5 As String  'get id from db after record is inserted.
            Dim mydatapd5 As SqlDataAdapter
            Dim mydataset5 As New DataSet("servT1")
            sql5 = "select * from services1 where custid = '" & membrid & "'"
            mydatapd5 = New SqlDataAdapter(sql5, con)
            mydatapd5.Fill(mydataset5, "servT1")
            Dim servT1 As DataTable
            servT1 = mydataset5.Tables("servT1")
            'loand details
            
            ' servT1det - details for servicesT1
            Dim sql6 As String  'get id from db after record is inserted.
            Dim mydatapd6 As SqlDataAdapter
            Dim mydataset6 As New DataSet("servT1det")
            sql6 = "select * from servT1det where custid = '" & membrid & "'"
            mydatapd6 = New SqlDataAdapter(sql6, con)
            mydatapd6.Fill(mydataset6, "servT1det")
            Dim servT1dettable As DataTable
            servT1dettable = mydataset6.Tables("servT1det")
            
            'image table
            Dim sql7 As String  'get id from db after record is inserted.
            Dim mydatapd7 As SqlDataAdapter
            Dim mydataset7 As New DataSet("image")
            sql7 = "select * from image"
            mydatapd7 = New SqlDataAdapter(sql7, con)
            mydatapd7.Fill(mydataset7, "image")
            Dim reconimg As DataTable
            reconimg = mydataset7.Tables("image")

            Dim sql8 As String  'get id from db after record is inserted.
            Dim mydatapd8 As SqlDataAdapter
            Dim mydataset8 As New DataSet("mbrdets")
            sql8 = "select * from custdetails where custid = '" & membrid & "'"
            mydatapd8 = New SqlDataAdapter(sql8, con)
            mydatapd8.Fill(mydataset8, "mbrdets")
            Dim memdets As DataTable
            memdets = mydataset8.Tables("mbrdets")

            Dim myrow As DataSet
            Dim rowcount As Integer
            Dim currentrow As Integer
            Dim date1 As String
            Dim xpage
            Dim xtr As Integer
            Dim ytr As Integer
            Dim docname As String
            docname = custable.Rows(0).Item(4).ToString.Trim()
            Dim headers As Integer
           
            'Dim doc As New PDFDocument("c:\test\stm_" & docname & ".pdf", page1)
            doc.CurrentPage.Body.AddTextArea(New RectangleF(20, 20, 20, 20), "test", True)
            Dim blackb As New PDFBrush(Color.Black)
            Dim whiteb As New PDFBrush(Color.White)
            Dim blackp As PDFPen
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=390, top:=25, width:=180)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.TextOut(420, 25, 0, "INVOICE PERIOD")
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=575, top:=25, width:=95)
            doc.CurrentPage.Body.TextOut(605, 25, 0, "PAGE")
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=675, top:=25, width:=120)
            doc.CurrentPage.Body.TextOut(678, 25, 0, "ACCOUNT NUMBER")
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
            'doc.CurrentPage.Body.TextOut(580, 45, 0,) "1", HorJust.Left, VertJust.Center)
            'doc.CurrentPage.Body.TextOut(625, 45, 0, "Of", HorJust.Left, VertJust.Center)
            doc.CurrentPage.Body.TextOut(390, 46, 0, Format(custable.Rows(0).Item(2), "MMM  dd  yyyy"), HorJust.Left, VertJust.Center)
            doc.CurrentPage.Body.TextOut(572, 46, 0, Format(custable.Rows(0).Item(3), "MMM  dd  yyyy"), HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(730, 46, 0, custable.Rows(0).Item(4).ToString.Trim(), HorJust.Left, VertJust.Center)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=515, top:=73, width:=280)
            doc.CurrentPage.Body.TextOut(630, 73, 0, "MESSAGE")
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
            Dim xaddr As Integer
            xaddr = 240
            If IsDBNull(custable.Rows(0).Item(5)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable.Rows(0).Item(5).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If
            If IsDBNull(custable.Rows(0).Item(6)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable.Rows(0).Item(6).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If
            If IsDBNull(custable.Rows(0).Item(7)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable.Rows(0).Item(7).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If
            If IsDBNull(custable.Rows(0).Item(8)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable.Rows(0).Item(8).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hola, I see where your problem is, you are doing too many round trips to your DB server, but it can be redesigned to get all the data required by using just a single call to your DB server, after that you will see a big time reduction to get the job done. I will create an SP for you and instructions of how to implement it by using just one dataset and datarelations objects. Please provide the rest of your code to be able to give you a better assist.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
hrodriguez761Author Commented:
Thanks so much for the advise - here is the rest of the code - I hope it doesn't give you a headache
If IsDBNull(custable(9)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable(9).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If
            If IsDBNull(custable(10)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable(10).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If

            Dim dataMatrix = New PDFTech.Barcodes.Barcode2D.QRCode(custable(4).ToString.Trim(), UnitOfMeasure.Pixel)
            dataMatrix.SymbolSize = PDFTech.Barcodes.QRCodeSymbolSize.Size01
            If dataMatrix.IsCodeValid() Then
                doc.CurrentPage.Body.Add2DBarcode(dataMatrix, 350, 245)
            End If

            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=315, width:=725)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.TextOut(360, 315, 0, "INVOICE SUMMARY")
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 8, Charset.ANSI_CHARSET, Color.Black)
            doc.CurrentPage.Body.TextOut(70, 337, 0, "Account Type", HorJust.Left, VertJust.Center)
            doc.CurrentPage.Body.TextOut(390, 337, 0, "Balance", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(525, 337, 0, "Advances", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(670, 337, 0, "Payments", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(795, 337, 0, "Ending Balance", HorJust.Right, VertJust.Center)
            ytr = 350

            For Each accRow In accstable
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                If IsDBNull(accRow.item("description")) = False Then
                    doc.CurrentPage.Body.TextOut(70, ytr, 0, accRow.item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                End If
                If IsDBNull(accRow.item("begbal")) = False Then
                    doc.CurrentPage.Body.TextOut(390, ytr, 0, Format(accRow.item("begbal"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("advances")) = False Then
                    doc.CurrentPage.Body.TextOut(525, ytr, 0, Format(accRow.item("advances"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("payments")) = False Then
                    doc.CurrentPage.Body.TextOut(670, ytr, 0, Format(accRow.item("payments"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("endbal")) = False Then
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(accRow.item("endbal"), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 12
            Next (accRow)
            For Each servRow In servT1
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                If IsDBNull(servRow.Item("description")) = False Then
                    doc.CurrentPage.Body.TextOut(70, ytr, 0, servRow.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("plb5")) = False Then
                    doc.CurrentPage.Body.TextOut(390, ytr, 0, Format(servRow.Item("plb5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("des5")) = False Then
                    doc.CurrentPage.Body.TextOut(525, ytr, 0, Format(servRow.Item("des5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("ptpandc5")) = False Then
                    doc.CurrentPage.Body.TextOut(670, ytr, 0, Format(servRow.Item("ptpandc5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("nlb5")) = False Then
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(servRow.Item("nlb5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 12
            Next (servRow)
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 10, width:=725)
            For Each row2 In accstable
                Dim curracc As Integer
                Dim begbal As Integer
                curracc = row2.Item(0)
                begbal = row2.Item(5)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr + 10, width:=350)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 10, width:=725)
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                doc.CurrentPage.Body.TextOut(75, ytr + 10, 0, row2.Item("number"))
                doc.CurrentPage.Body.TextOut(180, ytr + 10, 0, row2.Item("description"))
                headers = ytr
                ytr = ytr + 3
                'xtr = 90
                ytr = ytr + 40
                xpage = 1
                If IsDBNull(row2.Item(10)) Then
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    Dim aprind As Boolean
                    aprind = False
                    If IsDBNull(row2.Item(18)) = False Then
                        aprind = True
                    End If
                    If IsDBNull(row2.Item(23)) = False Then
                        aprind = True
                    End If
                    If IsDBNull(row2.Item(17)) = False Then
                        doc.CurrentPage.Body.TextOut(458, ytr, 0, row2.Item(17), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    'ytr = ytr + 15
                    If aprind = True Then
                        doc.CurrentPage.Body.TextOut(100, ytr, 0, "TRUTH INFORMATION", HorJust.Left, VertJust.Center)
                        ytr = ytr + 30
                    End If
                    If Not IsDBNull(row2.Item(31)) = True Then 
                        If aprind = True Then
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "JOINT:", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(300, ytr - 15, 0, row2.Item(31), HorJust.Left, VertJust.Center)
                            If Not IsDBNull(row2.Item(32)) = True Then
                                doc.CurrentPage.Body.TextOut(480, ytr - 15, 0, row2.Item(32), HorJust.Left, VertJust.Center)
                            End If
                            ytr = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr, 0, "JOINT:", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(300, ytr, 0, row2.Item(31), HorJust.Left, VertJust.Center)
                            If Not IsDBNull(row2.Item(32)) = True Then
                                doc.CurrentPage.Body.TextOut(480, ytr, 0, row2.Item(32), HorJust.Left, VertJust.Center)
                            End If
                            ytr = ytr + 15
                        End If
                    End If
                    If Not IsDBNull(row2.Item("hold")) Then
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "HOLDING:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, row2.Item("hold"), HorJust.Right, VertJust.Center)
                    End If
                    If IsDBNull(row2.Item(35)) = True Then
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, "0", HorJust.Right, VertJust.Center)

                        ytr = ytr + 15
                    Else
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, row2.Item(35), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    If aprind = True Then
                        If IsDBNull(row2.Item(18)) = True Then
                            'doc.CurrentPage.Body.TextOut(100, ytr + 22, 0, "RATE", HorJust.Left, VertJust.Center)
                            'doc.CurrentPage.Body.TextOut(458, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                            ' = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "RATE", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(458, ytr - 15, 0, row2.Item(18), HorJust.Right, VertJust.Center)
                            ytr = ytr + 15
                        End If
                        If IsDBNull(row2.Item(23)) = True Then
                            'doc.CurrentPage.Body.TextOut(100, ytr + 22, 0, "PERCENTAGE", HorJust.Left, VertJust.Center)
                            'doc.CurrentPage.Body.TextOut(458, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                            'ytr = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "PERCENTAGE", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(458, ytr - 15, 0, row2.Item(23), HorJust.Right, VertJust.Center)
                            ytr = ytr + 15
                        End If
                    End If
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 7, Charset.ANSI_CHARSET, Color.Black)
                    ytr = ytr + 20
                    Dim feeindc2 As Boolean
                    feeindc2 = False
                    If IsDBNull(row2.Item(37)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(38)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(39)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(40)) = False Then
                        feeindc2 = True
                    End If
                    If feeindc2 = True Then
                        'doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=60, left:=200, top:=ytr + 5, width:=500)
                        Dim feetable As PDFTech.Table
                        feetable = New PDFTech.Table(3, 3)
                        feetable.style.borderColor = Color.Black
                        feetable.rowHeight = 15
                        'feetable.style.verticalAlign = VerticalAlignment.middle
                        feetable.style.borderWidth = 1
                        feetable.column(0).width = 167
                        feetable.column(1).width = 145
                        feetable.column(2).width = 126
                        doc.CurrentPage.Body.DrawTable(feetable, 353, ytr)
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(658, ytr + 7, 0, "TOTAL", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(787, ytr + 7, 0, "DATE", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 22, 0, "FEES", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 38, 0, "RETURNED", HorJust.Left, VertJust.Center)
                        If IsDBNull(row2.Item(37)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, row2.Item(37), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(38)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, row2.Item(38), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(39)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, row2.Item(39), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(40)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, row2.Item(40), HorJust.Right, VertJust.Center)
                        End If
                        feeindc2 = False
                        aprind = False
                        ytr = ytr + 80
                    End If
                    
                End If
                If Not IsDBNull(row2.Item(10)) Then
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    ytr = ytr + 15
                    For Each mdets In memdets
                        Dim alignright As Integer
                        If mdets.Item("section") = "10" Then
                            alignright = alignright + 1
                            If alignright >= 2 Then
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.TextOut(500, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                                ytr = ytr + 15
                            Else
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.TextOut(150, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                                ytr = ytr + 15
                            End If
                        End If
                    Next
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "BEGINING", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(5), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(11)) Then
                        doc.CurrentPage.Body.TextOut(400, ytr, 0, "DRAFT", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(11), "standard"), HorJust.Right, VertJust.Center)
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "SITS", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(8), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(13)) Then
                        'doc.CurrentPage.Body.TextOut(400, ytr, 0, "AVG", HorJust.Left, VertJust.Center)
                        If Not IsDBNull(row2.Item(11)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "BAL", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(13), "standard"), HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "AVG", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(13), "standard"), HorJust.Right, VertJust.Center)
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "DRAF", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(10), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(15)) Then
                        If Not IsDBNull(row2.Item(13)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "LOW", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(15), "standard"), HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "MONTH", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(15), "standard"), HorJust.Right, VertJust.Center)
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "MISC", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(12), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(31)) Then
                        If Not IsDBNull(row2.Item(13)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                            If IsDBNull(row2.Item(35)) Then
                                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format("0", "standard"), HorJust.Right, VertJust.Center)
                            Else
                                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(35), "standard"), HorJust.Right, VertJust.Center)
                            End If
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                            If IsDBNull(row2.Item(35)) Then
                                doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format("0", "standard"), HorJust.Right, VertJust.Center)
                            Else
                                doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(35), "standard"), HorJust.Right, VertJust.Center)
                            End If
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "MAINT", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(14), "standard"), HorJust.Right, VertJust.Center)
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "ENDING", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(9), "standard"), HorJust.Right, VertJust.Center)
                    ytr = ytr + 20
                    'ytr = ytr + 20
                    Dim feeindc2 As Boolean
                    feeindc2 = False
                    If IsDBNull(row2.Item(37)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(38)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(39)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(40)) = False Then
                        feeindc2 = True
                    End If
                    If feeindc2 = True Then
                        'doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=60, left:=200, top:=ytr + 5, width:=500)
                        Dim feetable As PDFTech.Table
                        feetable = New PDFTech.Table(3, 3)
                        feetable.style.borderColor = Color.Black
                        feetable.rowHeight = 15
                        'feetable.style.verticalAlign = VerticalAlignment.middle
                        feetable.style.borderWidth = 1
                        feetable.column(0).width = 167
                        feetable.column(1).width = 145
                        feetable.column(2).width = 126
                        doc.CurrentPage.Body.DrawTable(feetable, 353, ytr)
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(658, ytr + 7, 0, "*******", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(787, ytr + 7, 0, "-----------", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 22, 0, "@@@@@@@@@@@", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 38, 0, "############", HorJust.Left, VertJust.Center)
                        If IsDBNull(row2.Item(37)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, row2.Item(37), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(38)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, row2.Item(38), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(39)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, row2.Item(39), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(40)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, row2.Item(40), HorJust.Right, VertJust.Center)
                        End If
                        feeindc2 = False
                        ytr = ytr + 60
                    End If
                    ytr = ytr + 25
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, "Balance", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(250, ytr, 0, "Description", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(235, ytr, 0, "Amount", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(155, ytr, 0, "Eff", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 9, width:=725)
                    ytr = ytr + 15
                End If
                'Dim sqlr As String
                'Dim mydatarpd As SqlDataAdapter
                'Dim mydatarset As New DataSet("accdetail2")
                'sqlr = "select * from accdetail where memberid = '" & row2.item("id") & "'"
                'mydatarpd = New SqlDataAdapter(sqlr, con)
                'mydatarpd.Fill(mydatarset, "accdetail2")
                'Dim accrdet As DataTable
                'accrdet = mydatarset.Tables("accdetail2")
                doc.CurrentPage.Body.TextOut(250, ytr, 0, "Begining Balance", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(795, ytr, 0, Format((begbal), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15

                For Each row4 In accdet
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    If row4.Item(1) = curracc Then
                        If IsDBNull(row4.Item("date2")) = False Then
                            doc.CurrentPage.Body.TextOut(155, ytr, 0, Format(row4.Item("date2"), "dd MMM"), HorJust.Right, VertJust.Center)
                        End If
                        doc.CurrentPage.Body.TextOut(250, ytr, 0, row4.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                        If IsDBNull(row4.Item("tracer")) = False Then
                            doc.CurrentPage.Body.TextOut(650, ytr, 0, row4.Item("tracer").ToString.Trim(), HorJust.Center, VertJust.Center)
                        End If
                        If IsDBNull(row4.Item("amount1")) = False Then
                            doc.CurrentPage.Body.TextOut(235, ytr, 0, Format(row4.Item("amt1"), "standard").ToString.Trim(), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row4.Item("amount2")) = False Then
                            doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(row4.Item("amt2"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        ytr = ytr + 15
                        If ytr >= 950 Then
                            xpage = xpage + 1
                            doc.NewPage()
                            ytr = 75
                        End If
                        If doc.CurrentPage.Index >= 1 Then
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=390, top:=25, width:=180)
                            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                            doc.CurrentPage.Body.TextOut(420, 25, 0, "PERIOD")
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=575, top:=25, width:=95)
                            doc.CurrentPage.Body.TextOut(605, 25, 0, "PAGE")
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=675, top:=25, width:=120)
                            doc.CurrentPage.Body.TextOut(678, 25, 0, "ACCOUNT NUMBER")
                            If ytr = 75 Then
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Blue)
                                doc.CurrentPage.Body.TextOut(390, 46, 0, Format(custable(2), "MMM  dd  yyyy"), HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(572, 46, 0, Format(custable(3), "MMM  dd  yyyy"), HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(750, 46, 0, custable(4).ToString.Trim(), HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=68, width:=725)
                                doc.CurrentPage.Body.TextOut(795, 58, 0, "Balance", HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(250, 58, 0, "Description", HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(235, 58, 0, "Amount", HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(155, 58, 0, "Eff Date", HorJust.Right, VertJust.Center)
                            End If
                        End If
                    End If
                Next (row4)
                For Each mdets In memdets
                    If mdets.Item("section") = "15" Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(210, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                        ytr = ytr + 15
                    End If
                Next
                If ytr >= 800 Then
                    xpage = xpage + 1
                    doc.NewPage()
                    ytr = 75
                End If
            Next (row2)
            For Each row5 In servT1
                Dim currserv As Integer
                currloan = row5.Item(0)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr + 10, width:=350)
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                doc.CurrentPage.Body.TextOut(75, ytr + 10, 0, row5.Item("number"))
                doc.CurrentPage.Body.TextOut(180, ytr + 10, 0, row5.Item("description"))
                ytr = ytr + 50
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "BALANCE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(8), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "****", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(6), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "NEW", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(12), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "RATE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(7), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "DUE:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, row5.Item(16), HorJust.Right, VertJust.Center)
                If IsDBNull(row5.Item(9)) = False Then
                    doc.CurrentPage.Body.TextOut(400, ytr, 0, "AVAILABLE", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(10), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CURRENT:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(13), "standard"), HorJust.Right, VertJust.Center)
                If IsDBNull(row5.Item(8)) = False Then
                    doc.CurrentPage.Body.TextOut(400, ytr, 0, "LIMIT:", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(9), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "TOTAL:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(15), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "CHARGE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(11), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                'need to add past due to table strucutre and parser
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "DUE:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(14), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "DEBITS:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(18)) Then
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(18), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CHARGE*:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(19)) Then
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(19), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CREDITS:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(17)) Then
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(17), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 25
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.Black)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 9, width:=725)
                doc.CurrentPage.Body.TextOut(155, ytr, 0, "Eff", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(175, ytr, 0, "Description", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(500, ytr, 0, "Late", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(580, ytr, 0, "Chng", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(660, ytr, 0, "Principal", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(720, ytr, 0, "Rev", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(795, ytr, 0, "Bal", HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                For Each servT1 In servT1dettable
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    If servT1dettable.Item(1) = servt1 Then
                        If IsDBNull(servT1dettable.Item("date2")) = False Then
                            doc.CurrentPage.Body.TextOut(155, ytr, 0, Format(servT1dettable.Item("date2"), "dd MMM"), HorJust.Right, VertJust.Center)
                        End If
                        doc.CurrentPage.Body.TextOut(175, ytr, 0, servT1dettable.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                        If IsDBNull(servT1dettable.Item("cec")) = False Then
                            doc.CurrentPage.Body.TextOut(580, ytr, 0, servT1dettable.Item("cec").ToString.Trim(), HorJust.Center, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("ipal")) = False Then
                            doc.CurrentPage.Body.TextOut(660, ytr, 0, Format(servT1dettable.Item("ipal"), "standard").ToString.Trim(), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("tott")) = False Then
                            doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(servT1dettable.Item("tott"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("ge5")) = False Then
                            doc.CurrentPage.Body.TextOut(760, ytr, 0, Format(servT1dettable.Item("ge5"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("pal5")) = False Then
                            doc.CurrentPage.Body.TextOut(660, ytr, 0, Format(servT1dettable.Item("pal5"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        ytr = ytr + 15
                        If ytr >= 950 Then
                            xpage = xpage + 1
                            doc.NewPage()
                            ytr = 75
                        End If                 
                        
                    End If
                Next (servT1dettable)

            Next (row5)
            'Dim totalp As Integer
            'totalp = doc.PageCount
            If ytr >= 800 Then
                xpage = xpage + 1
                doc.NewPage()
                ytr = 75
            End If
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr, width:=350)
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr, width:=725)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.TextOut(75, ytr, 0, "SUMM")
            ytr = ytr + 45
            For Each mdets In memdets
                If mdets.Item("section") = 30 Then
                    Dim sumcount As Integer
                    sumcount = sumcount + 1
                    If sumcount = 8 Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(650, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    If sumcount = 10 Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(650, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    doc.CurrentPage.Body.TextOut(160, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                    ytr = ytr + 15
                End If
            Next
            doc.NewPage()
        Next (membrow)
        'Dim _ImageData(-1) As Byte
        '_ImageData = CType(reconimg.Rows(0).Item(3), Byte())
        'Dim _MemoryStream As New System.IO.MemoryStream(_ImageData)
        'Dim _Image As System.Drawing.Image
        '_Image = System.Drawing.Image.FromStream(_MemoryStream)
        '_Image.Save("c:\test\pic.jpg", System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim imgreco As New PDFImage("c:\test\pic.jpg")
        'doc.CurrentPage.Body.AddImage(imgreco, 293, ytr)
        doc.Save()

Open in new window

If IsDBNull(custable(9)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable(9).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If
            If IsDBNull(custable(10)) = False Then
                doc.CurrentPage.Body.TextOut(120, xaddr, 0, custable(10).ToString.Trim(), HorJust.Left, VertJust.Center)
                xaddr = xaddr + 13
            End If

            Dim dataMatrix = New PDFTech.Barcodes.Barcode2D.QRCode(custable(4).ToString.Trim(), UnitOfMeasure.Pixel)
            dataMatrix.SymbolSize = PDFTech.Barcodes.QRCodeSymbolSize.Size01
            If dataMatrix.IsCodeValid() Then
                doc.CurrentPage.Body.Add2DBarcode(dataMatrix, 350, 245)
            End If

            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=315, width:=725)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.TextOut(360, 315, 0, "INVOICE SUMMARY")
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 8, Charset.ANSI_CHARSET, Color.Black)
            doc.CurrentPage.Body.TextOut(70, 337, 0, "Account Type", HorJust.Left, VertJust.Center)
            doc.CurrentPage.Body.TextOut(390, 337, 0, "Balance", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(525, 337, 0, "Advances", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(670, 337, 0, "Payments", HorJust.Right, VertJust.Center)
            doc.CurrentPage.Body.TextOut(795, 337, 0, "Ending Balance", HorJust.Right, VertJust.Center)
            ytr = 350

            For Each accRow In accstable
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                If IsDBNull(accRow.item("description")) = False Then
                    doc.CurrentPage.Body.TextOut(70, ytr, 0, accRow.item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                End If
                If IsDBNull(accRow.item("begbal")) = False Then
                    doc.CurrentPage.Body.TextOut(390, ytr, 0, Format(accRow.item("begbal"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("advances")) = False Then
                    doc.CurrentPage.Body.TextOut(525, ytr, 0, Format(accRow.item("advances"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("payments")) = False Then
                    doc.CurrentPage.Body.TextOut(670, ytr, 0, Format(accRow.item("pays"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(accRow.item("endbal")) = False Then
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(accRow.item("endbal"), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 12
            Next (accRow)
            For Each servRow In servT1
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                If IsDBNull(servRow.Item("description")) = False Then
                    doc.CurrentPage.Body.TextOut(70, ytr, 0, servRow.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("plb5")) = False Then
                    doc.CurrentPage.Body.TextOut(390, ytr, 0, Format(servRow.Item("plb5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("des5")) = False Then
                    doc.CurrentPage.Body.TextOut(525, ytr, 0, Format(servRow.Item("des5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("ptpandc5")) = False Then
                    doc.CurrentPage.Body.TextOut(670, ytr, 0, Format(servRow.Item("ptpandc5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                If IsDBNull(servRow.Item("nlb5")) = False Then
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(servRow.Item("nlb5"), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 12
            Next (servRow)
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 10, width:=725)
            For Each row2 In accstable
                Dim curracc As Integer
                Dim begbal As Integer
                curracc = row2.Item(0)
                begbal = row2.Item(5)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr + 10, width:=350)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 10, width:=725)
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                doc.CurrentPage.Body.TextOut(75, ytr + 10, 0, row2.Item("number"))
                doc.CurrentPage.Body.TextOut(180, ytr + 10, 0, row2.Item("description"))
                headers = ytr
                ytr = ytr + 3
                'xtr = 90
                ytr = ytr + 40
                xpage = 1
                If IsDBNull(row2.Item(10)) Then
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    Dim aprind As Boolean
                    aprind = False
                    If IsDBNull(row2.Item(18)) = False Then
                        aprind = True
                    End If
                    If IsDBNull(row2.Item(23)) = False Then
                        aprind = True
                    End If
                    If IsDBNull(row2.Item(17)) = False Then
                        doc.CurrentPage.Body.TextOut(458, ytr, 0, row2.Item(17), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    'ytr = ytr + 15
                    If aprind = True Then
                        doc.CurrentPage.Body.TextOut(100, ytr, 0, "TRUTH INFORMATION", HorJust.Left, VertJust.Center)
                        ytr = ytr + 30
                    End If
                    If Not IsDBNull(row2.Item(31)) = True Then 
                        If aprind = True Then
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "JOINT:", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(300, ytr - 15, 0, row2.Item(31), HorJust.Left, VertJust.Center)
                            If Not IsDBNull(row2.Item(32)) = True Then
                                doc.CurrentPage.Body.TextOut(480, ytr - 15, 0, row2.Item(32), HorJust.Left, VertJust.Center)
                            End If
                            ytr = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr, 0, "JOINT:", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(300, ytr, 0, row2.Item(31), HorJust.Left, VertJust.Center)
                            If Not IsDBNull(row2.Item(32)) = True Then
                                doc.CurrentPage.Body.TextOut(480, ytr, 0, row2.Item(32), HorJust.Left, VertJust.Center)
                            End If
                            ytr = ytr + 15
                        End If
                    End If
                    If Not IsDBNull(row2.Item("hold")) Then
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "HOLDING:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, row2.Item("hold"), HorJust.Right, VertJust.Center)
                    End If
                    If IsDBNull(row2.Item(35)) = True Then
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, "0", HorJust.Right, VertJust.Center)

                        ytr = ytr + 15
                    Else
                        doc.CurrentPage.Body.TextOut(500, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(658, ytr, 0, row2.Item(35), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    If aprind = True Then
                        If IsDBNull(row2.Item(18)) = True Then
                            'doc.CurrentPage.Body.TextOut(100, ytr + 22, 0, "RATE", HorJust.Left, VertJust.Center)
                            'doc.CurrentPage.Body.TextOut(458, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                            ' = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "RATE", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(458, ytr - 15, 0, row2.Item(18), HorJust.Right, VertJust.Center)
                            ytr = ytr + 15
                        End If
                        If IsDBNull(row2.Item(23)) = True Then
                            'doc.CurrentPage.Body.TextOut(100, ytr + 22, 0, "PERCENTAGE", HorJust.Left, VertJust.Center)
                            'doc.CurrentPage.Body.TextOut(458, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                            'ytr = ytr + 15
                        Else
                            doc.CurrentPage.Body.TextOut(100, ytr - 15, 0, "PERCENTAGE", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(458, ytr - 15, 0, row2.Item(23), HorJust.Right, VertJust.Center)
                            ytr = ytr + 15
                        End If
                    End If
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 7, Charset.ANSI_CHARSET, Color.Black)
                    ytr = ytr + 20
                    Dim feeindc2 As Boolean
                    feeindc2 = False
                    If IsDBNull(row2.Item(37)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(38)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(39)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(40)) = False Then
                        feeindc2 = True
                    End If
                    If feeindc2 = True Then
                        'doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=60, left:=200, top:=ytr + 5, width:=500)
                        Dim feetable As PDFTech.Table
                        feetable = New PDFTech.Table(3, 3)
                        feetable.style.borderColor = Color.Black
                        feetable.rowHeight = 15
                        'feetable.style.verticalAlign = VerticalAlignment.middle
                        feetable.style.borderWidth = 1
                        feetable.column(0).width = 167
                        feetable.column(1).width = 145
                        feetable.column(2).width = 126
                        doc.CurrentPage.Body.DrawTable(feetable, 353, ytr)
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(658, ytr + 7, 0, "TOTAL", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(787, ytr + 7, 0, "DATE", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 22, 0, "FEES", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 38, 0, "RETURNED", HorJust.Left, VertJust.Center)
                        If IsDBNull(row2.Item(37)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, row2.Item(37), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(38)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, row2.Item(38), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(39)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, row2.Item(39), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(40)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, "0.00", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, row2.Item(40), HorJust.Right, VertJust.Center)
                        End If
                        feeindc2 = False
                        aprind = False
                        ytr = ytr + 80
                    End If
                    
                End If
                If Not IsDBNull(row2.Item(10)) Then
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    ytr = ytr + 15
                    For Each mdets In memdets
                        Dim alignright As Integer
                        If mdets.Item("section") = "10" Then
                            alignright = alignright + 1
                            If alignright >= 2 Then
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.TextOut(500, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                                ytr = ytr + 15
                            Else
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.TextOut(150, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                                ytr = ytr + 15
                            End If
                        End If
                    Next
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "BEGINING", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(5), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(11)) Then
                        doc.CurrentPage.Body.TextOut(400, ytr, 0, "DRAFT", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(11), "standard"), HorJust.Right, VertJust.Center)
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "SITS", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(8), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(13)) Then
                        'doc.CurrentPage.Body.TextOut(400, ytr, 0, "AVG", HorJust.Left, VertJust.Center)
                        If Not IsDBNull(row2.Item(11)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "BAL", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(13), "standard"), HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "AVG", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(13), "standard"), HorJust.Right, VertJust.Center)
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "DRAF", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(10), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(15)) Then
                        If Not IsDBNull(row2.Item(13)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "LOW", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(15), "standard"), HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "MONTH", HorJust.Left, VertJust.Center)
                            doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(15), "standard"), HorJust.Right, VertJust.Center)
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "MISC", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(12), "standard"), HorJust.Right, VertJust.Center)
                    If Not IsDBNull(row2.Item(31)) Then
                        If Not IsDBNull(row2.Item(13)) Then
                            doc.CurrentPage.Body.TextOut(400, ytr, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                            If IsDBNull(row2.Item(35)) Then
                                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format("0", "standard"), HorJust.Right, VertJust.Center)
                            Else
                                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row2.Item(35), "standard"), HorJust.Right, VertJust.Center)
                            End If
                        Else
                            doc.CurrentPage.Body.TextOut(400, ytr - 15, 0, "Y-T-D:", HorJust.Left, VertJust.Center)
                            If IsDBNull(row2.Item(35)) Then
                                doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format("0", "standard"), HorJust.Right, VertJust.Center)
                            Else
                                doc.CurrentPage.Body.TextOut(750, ytr - 15, 0, Format(row2.Item(35), "standard"), HorJust.Right, VertJust.Center)
                            End If
                        End If
                    End If
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "MAINT", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(14), "standard"), HorJust.Right, VertJust.Center)
                    ytr = ytr + 15
                    doc.CurrentPage.Body.TextOut(100, ytr, 0, "ENDING", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row2.Item(9), "standard"), HorJust.Right, VertJust.Center)
                    ytr = ytr + 20
                    'ytr = ytr + 20
                    Dim feeindc2 As Boolean
                    feeindc2 = False
                    If IsDBNull(row2.Item(37)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(38)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(39)) = False Then
                        feeindc2 = True
                    End If
                    If IsDBNull(row2.Item(40)) = False Then
                        feeindc2 = True
                    End If
                    If feeindc2 = True Then
                        'doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=60, left:=200, top:=ytr + 5, width:=500)
                        Dim feetable As PDFTech.Table
                        feetable = New PDFTech.Table(3, 3)
                        feetable.style.borderColor = Color.Black
                        feetable.rowHeight = 15
                        'feetable.style.verticalAlign = VerticalAlignment.middle
                        feetable.style.borderWidth = 1
                        feetable.column(0).width = 167
                        feetable.column(1).width = 145
                        feetable.column(2).width = 126
                        doc.CurrentPage.Body.DrawTable(feetable, 353, ytr)
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 8, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(658, ytr + 7, 0, "*******", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(787, ytr + 7, 0, "-----------", HorJust.Right, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 22, 0, "@@@@@@@@@@@", HorJust.Left, VertJust.Center)
                        doc.CurrentPage.Body.TextOut(358, ytr + 38, 0, "############", HorJust.Left, VertJust.Center)
                        If IsDBNull(row2.Item(37)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 22, 0, row2.Item(37), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(38)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 22, 0, row2.Item(38), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(39)) = True Then
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(658, ytr + 38, 0, row2.Item(39), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row2.Item(40)) = True Then
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, "0", HorJust.Right, VertJust.Center)
                        Else
                            doc.CurrentPage.Body.TextOut(787, ytr + 38, 0, row2.Item(40), HorJust.Right, VertJust.Center)
                        End If
                        feeindc2 = False
                        ytr = ytr + 60
                    End If
                    ytr = ytr + 25
                    doc.CurrentPage.Body.TextOut(795, ytr, 0, "Balance", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(250, ytr, 0, "Description", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(235, ytr, 0, "Amount", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(155, ytr, 0, "Eff", HorJust.Right, VertJust.Center)
                    doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 9, width:=725)
                    ytr = ytr + 15
                End If
                'Dim sqlr As String
                'Dim mydatarpd As SqlDataAdapter
                'Dim mydatarset As New DataSet("accdetail2")
                'sqlr = "select * from accdetail where memberid = '" & row2.item("id") & "'"
                'mydatarpd = New SqlDataAdapter(sqlr, con)
                'mydatarpd.Fill(mydatarset, "accdetail2")
                'Dim accrdet As DataTable
                'accrdet = mydatarset.Tables("accdetail2")
                doc.CurrentPage.Body.TextOut(250, ytr, 0, "Begining Balance", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(795, ytr, 0, Format((begbal), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15

                For Each row4 In accdet
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    If row4.Item(1) = curracc Then
                        If IsDBNull(row4.Item("date2")) = False Then
                            doc.CurrentPage.Body.TextOut(155, ytr, 0, Format(row4.Item("date2"), "dd MMM"), HorJust.Right, VertJust.Center)
                        End If
                        doc.CurrentPage.Body.TextOut(250, ytr, 0, row4.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                        If IsDBNull(row4.Item("tracer")) = False Then
                            doc.CurrentPage.Body.TextOut(650, ytr, 0, row4.Item("tracer").ToString.Trim(), HorJust.Center, VertJust.Center)
                        End If
                        If IsDBNull(row4.Item("amount1")) = False Then
                            doc.CurrentPage.Body.TextOut(235, ytr, 0, Format(row4.Item("amt1"), "standard").ToString.Trim(), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(row4.Item("amount2")) = False Then
                            doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(row4.Item("amt2"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        ytr = ytr + 15
                        If ytr >= 950 Then
                            xpage = xpage + 1
                            doc.NewPage()
                            ytr = 75
                        End If
                        If doc.CurrentPage.Index >= 1 Then
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=390, top:=25, width:=180)
                            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                            doc.CurrentPage.Body.TextOut(420, 25, 0, "PERIOD")
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=575, top:=25, width:=95)
                            doc.CurrentPage.Body.TextOut(605, 25, 0, "PAGE")
                            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=675, top:=25, width:=120)
                            doc.CurrentPage.Body.TextOut(678, 25, 0, "ACCOUNT NUMBER")
                            If ytr = 75 Then
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Blue)
                                doc.CurrentPage.Body.TextOut(390, 46, 0, Format(custable(2), "MMM  dd  yyyy"), HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(572, 46, 0, Format(custable(3), "MMM  dd  yyyy"), HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(750, 46, 0, custable(4).ToString.Trim(), HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.Black)
                                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=68, width:=725)
                                doc.CurrentPage.Body.TextOut(795, 58, 0, "Balance", HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(250, 58, 0, "Description", HorJust.Left, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(235, 58, 0, "Amount", HorJust.Right, VertJust.Center)
                                doc.CurrentPage.Body.TextOut(155, 58, 0, "Eff Date", HorJust.Right, VertJust.Center)
                            End If
                        End If
                    End If
                Next (row4)
                For Each mdets In memdets
                    If mdets.Item("section") = "15" Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(210, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                        ytr = ytr + 15
                    End If
                Next
                If ytr >= 800 Then
                    xpage = xpage + 1
                    doc.NewPage()
                    ytr = 75
                End If
            Next (row2)
            For Each row5 In servT1
                Dim currserv As Integer
                currloan = row5.Item(0)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr + 10, width:=350)
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
                doc.CurrentPage.Body.TextOut(75, ytr + 10, 0, row5.Item("number"))
                doc.CurrentPage.Body.TextOut(180, ytr + 10, 0, row5.Item("description"))
                ytr = ytr + 50
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "BALANCE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(8), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "****", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(6), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "NEW", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(12), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "RATE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(7), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "DUE:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, row5.Item(16), HorJust.Right, VertJust.Center)
                If IsDBNull(row5.Item(9)) = False Then
                    doc.CurrentPage.Body.TextOut(400, ytr, 0, "AVAILABLE", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(10), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CURRENT:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(13), "standard"), HorJust.Right, VertJust.Center)
                If IsDBNull(row5.Item(8)) = False Then
                    doc.CurrentPage.Body.TextOut(400, ytr, 0, "LIMIT:", HorJust.Left, VertJust.Center)
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(9), "standard"), HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "TOTAL:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(15), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "CHARGE", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(11), "standard"), HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                'need to add past due to table strucutre and parser
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "DUE:", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(14), "standard"), HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(400, ytr, 0, "DEBITS:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(18)) Then
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, Format(row5.Item(18), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(750, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CHARGE*:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(19)) Then
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(19), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 15
                doc.CurrentPage.Body.TextOut(100, ytr, 0, "CREDITS:", HorJust.Left, VertJust.Center)
                If Not IsDBNull(row5.Item(17)) Then
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, Format(row5.Item(17), "standard"), HorJust.Right, VertJust.Center)
                Else
                    doc.CurrentPage.Body.TextOut(380, ytr, 0, "0", HorJust.Right, VertJust.Center)
                End If
                ytr = ytr + 25
                doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.Black)
                doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr + 9, width:=725)
                doc.CurrentPage.Body.TextOut(155, ytr, 0, "Eff", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(175, ytr, 0, "Description", HorJust.Left, VertJust.Center)
                doc.CurrentPage.Body.TextOut(500, ytr, 0, "Late", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(580, ytr, 0, "Chng", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(660, ytr, 0, "Principal", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(720, ytr, 0, "Rev", HorJust.Right, VertJust.Center)
                doc.CurrentPage.Body.TextOut(795, ytr, 0, "Bal", HorJust.Right, VertJust.Center)
                ytr = ytr + 15
                For Each servT1 In servT1dettable
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    If servT1dettable.Item(1) = servt1 Then
                        If IsDBNull(servT1dettable.Item("date2")) = False Then
                            doc.CurrentPage.Body.TextOut(155, ytr, 0, Format(servT1dettable.Item("date2"), "dd MMM"), HorJust.Right, VertJust.Center)
                        End If
                        doc.CurrentPage.Body.TextOut(175, ytr, 0, servT1dettable.Item("description").ToString.Trim(), HorJust.Left, VertJust.Center)
                        If IsDBNull(servT1dettable.Item("cec")) = False Then
                            doc.CurrentPage.Body.TextOut(580, ytr, 0, servT1dettable.Item("cec").ToString.Trim(), HorJust.Center, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("ipal")) = False Then
                            doc.CurrentPage.Body.TextOut(660, ytr, 0, Format(servT1dettable.Item("ipal"), "standard").ToString.Trim(), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("tott")) = False Then
                            doc.CurrentPage.Body.TextOut(795, ytr, 0, Format(servT1dettable.Item("tott"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("ge5")) = False Then
                            doc.CurrentPage.Body.TextOut(760, ytr, 0, Format(servT1dettable.Item("ge5"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        If IsDBNull(servT1dettable.Item("pal5")) = False Then
                            doc.CurrentPage.Body.TextOut(660, ytr, 0, Format(servT1dettable.Item("pal5"), "standard"), HorJust.Right, VertJust.Center)
                        End If
                        ytr = ytr + 15
                        If ytr >= 950 Then
                            xpage = xpage + 1
                            doc.NewPage()
                            ytr = 75
                        End If                 
                        
                    End If
                Next (servT1dettable)

            Next (row5)
            'Dim totalp As Integer
            'totalp = doc.PageCount
            If ytr >= 800 Then
                xpage = xpage + 1
                doc.NewPage()
                ytr = 75
            End If
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=15, left:=70, top:=ytr, width:=350)
            doc.CurrentPage.Body.Shapes.DrawRectangle(blackb, blackp, height:=1, left:=70, top:=ytr, width:=725)
            doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Bold, 9, Charset.ANSI_CHARSET, Color.White)
            doc.CurrentPage.Body.TextOut(75, ytr, 0, "SUMM")
            ytr = ytr + 45
            For Each mdets In memdets
                If mdets.Item("section") = 30 Then
                    Dim sumcount As Integer
                    sumcount = sumcount + 1
                    If sumcount = 8 Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(650, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    If sumcount = 10 Then
                        doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                        doc.CurrentPage.Body.TextOut(650, ytr, 0, mdets.Item(3), HorJust.Right, VertJust.Center)
                        ytr = ytr + 15
                    End If
                    doc.CurrentPage.Body.SetActiveFont("Arial", PDFFontStyles.Regular, 9, Charset.ANSI_CHARSET, Color.Black)
                    doc.CurrentPage.Body.TextOut(160, ytr, 0, mdets.Item(3), HorJust.Left, VertJust.Center)
                    ytr = ytr + 15
                End If
            Next
            doc.NewPage()
        Next (membrow)
        'Dim _ImageData(-1) As Byte
        '_ImageData = CType(reconimg.Rows(0).Item(3), Byte())
        'Dim _MemoryStream As New System.IO.MemoryStream(_ImageData)
        'Dim _Image As System.Drawing.Image
        '_Image = System.Drawing.Image.FromStream(_MemoryStream)
        '_Image.Save("c:\test\pic.jpg", System.Drawing.Imaging.ImageFormat.Jpeg)
        Dim imgreco As New PDFImage("c:\test\pic.jpg")
        'doc.CurrentPage.Body.AddImage(imgreco, 293, ytr)
        doc.Save()

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Wow! I will tell you when I have something ok.
0
 
hrodriguez761Author Commented:
Thanks  for your help- I pasted the code twice.
0
 
hrodriguez761Author Commented:
Ok this is what I changed, I created a SP that execute all the select statements I need for the current account. I loading the resulting tables into a single dataset with all the tables. Processing time still the same. I'm thinking maybe loading the entire db into memory and filter from there. What do you think?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi buddy, that is a good start, but the problem is this, you need to avoid query account by account, and yes, the key is download all the data required (just the data required) into the dataset, then use datarelations to filter the data, casually Im working on your example right now, give me some minutes to post that. can you post yours tables definitions? do you know how to do that by using sql server management studio?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Forget the tables definitions, I will give you an example explaining how handle this.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ok, here the example:

I made this SP, it must return all the data required for your process:
CREATE PROC
	dbo.SpTestMembersData
AS

-- This is goin to be our helper table
DECLARE @tMemberIds TABLE
(
	MemberId INT
)
INSERT
	@tMemberIds
SELECT
	MemberId
FROM
	dbo.member
-- Use a WHERE to define the customers to be procesed


-- GET THE member DATA
SELECT
	m.MemberId,
	m2.ColA,
	m2.ColB,
	m2.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.member AS m2
	ON
		m2.MemberId = m.MemberId


-- GET THE accdetail DATA
SELECT
	m.MemberId,
	ad.ColA,
	ad.ColB,
	ad.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.accdetail AS ad
	ON
		ad.MemberId = m.MemberId


-- GET THE accounts DATA
SELECT
	m.MemberId,
	ac.ColA,
	ac.ColB,
	ac.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.accounts AS ac
	ON
		ac.MemberId = m.MemberId


-- GET THE services1 DATA
SELECT
	m.MemberId,
	s1.ColA,
	s1.ColB,
	s1.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.services1 AS s1
	ON
		s1.MemberId = m.MemberId


-- GET THE servT1det DATA
SELECT
	m.MemberId,
	s1d.ColA,
	s1d.ColB,
	s1d.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.servT1det AS s1d
	ON
		s1d.MemberId = m.MemberId


-- GET THE custdetails DATA
SELECT
	m.MemberId,
	cd.ColA,
	cd.ColB,
	cd.ColC
FROM
	@tMemberIds AS m
INNER JOIN
	dbo.custdetails AS cd
	ON
		cd.MemberId = m.MemberId

Open in new window



Now this is the VB code, that will make use of the previous SP:
Sub ProcessCustomers()
	Dim dts As New DataSet

	Using cn As New SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")

	    Dim cm As New SqlCommand("dbo.SpTestMembersData", cn)
	    cm.CommandType = CommandType.StoredProcedure

	    Using dta As New SqlDataAdapter(cm)

		' Set a name to each result set
		dta.TableMappings.Add("Table", "member")
		dta.TableMappings.Add("Table1", "accdetail")
		dta.TableMappings.Add("Table2", "accounts")
		dta.TableMappings.Add("Table3", "services1")
		dta.TableMappings.Add("Table4", "servT1det")
		dta.TableMappings.Add("Table5", "custdetails")

		' Fill the dataset
		dta.Fill(dts)
	    End Using

	End Using

	Dim dttMember As DataTable = dts.Tables("member")

	Dim dttAccdetail As DataTable = dts.Tables("accdetail")
	Dim dttAccounts As DataTable = dts.Tables("accounts")
	Dim dttServices1 As DataTable = dts.Tables("services1")
	Dim dttServT1det As DataTable = dts.Tables("servT1det")
	Dim dttCustdetails As DataTable = dts.Tables("custdetails")

	Dim relAccdetail As DataRelation = dts.Relations.Add(dttMember.Columns("MemberId"), dttAccdetail.Columns("MemberId"))
	Dim relAccounts As DataRelation = dts.Relations.Add(dttMember.Columns("MemberId"), dttAccounts.Columns("MemberId"))
	Dim relServices1 As DataRelation = dts.Relations.Add(dttMember.Columns("MemberId"), dttServices1.Columns("MemberId"))
	Dim relServT1det As DataRelation = dts.Relations.Add(dttMember.Columns("MemberId"), dttServT1det.Columns("MemberId"))
	Dim relCustdetails As DataRelation = dts.Relations.Add(dttMember.Columns("MemberId"), dttCustdetails.Columns("MemberId"))

	' Process each customer
	For Each rowMember As DataRow In dttMember.Rows

	    ' Get Accdetail data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relAccdetail)
		Console.WriteLine(row(0))
	    Next

	    ' Get Accounts data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relAccounts)
		Console.WriteLine(row(0))
	    Next

	    ' Get Services1 data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relServices1)
		Console.WriteLine(row(0))
	    Next

	    ' Get Accdetail data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relAccdetail)
		Console.WriteLine(row(0))
	    Next

	    ' Get ServT1det data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relServT1det)
		Console.WriteLine(row(0))
	    Next

	    ' Get Custdetails data for the current member
	    For Each row As DataRow In rowMember.GetChildRows(relCustdetails)
		Console.WriteLine(row(0))
	    Next
	Next

End Sub

Open in new window



I think this example can give you a good start, please in the SP avoid use SELECT * FROM... at least that you need all the columns in that table, if not, just select the columns that you will use in your code, this reduces the data transfered from the sql server instance to your application.

Also measure from sql management studio the time required to run the SP, and see if this take to much time to return the data, be sure to have the corrects indexes in yours tables.

The SP and code is a demo, you need to adapt this to your scenario.

I hope this help.
0
 
hrodriguez761Author Commented:
Hey  Buddy,

 Thanks so much for your help and time on this problem - i'll get to work on this now  and will post back the results.  or more questions lol..
0
 
hrodriguez761Author Commented:
your code works pretty well by bringing all the data in one trip and im able to sort the records with the "getchildrows" method. much nicer than the mess i had before lol.  however i discovered another bottle neck in my app at the time to generate the pdf. still work on that.

 Again thanks so much for all your help on this,  

my best regards,
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi buddy, glad to know that, an advice, short your data on the SP, avoid do that on the client side, you will note the difference. For your PDF bottleneck problem I think that I can help you with that too,but only if you generate a PDF file per customer and not just one for all's.
0
 
hrodriguez761Author Commented:
Hey sorry for posting back now things got a bit crazy on my side. I was able to break the PDF into individual files and it's generating about 200 PDFs per minute - overall took about 4.5hrs to complete
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi, I think that we can improve that to 600 PDFs per minute or more, thats depends on your server resources and processor. First, you need to add a parameter to your SP so you can split yours customers data, for example, customers from ID 0 to 1000, then from 1001 to 2000 and so on, can you do that? an TSQL algorithm to get the valid customers range can be, for example:
CREATE PROC
	dbo.SpSplitMembersId
AS

DECLARE @vMinCustomerId INT, @vMaxCustomerId INT
SELECT
	@vMinCustomerId = MIN(MemberId),
	@vMaxCustomerId = MAX(MemberId)
FROM
	dbo.member


DECLARE @tCustomerIdSet TABLE
(
	FromId INT,
	ToId INT
)

DECLARE @vCurrentId INT, @vLastId INT
SELECT
	@vCurrentId = @vMinCustomerId,
	@vLastId = @vMinCustomerId


WHILE (@vCurrentId < @vMaxCustomerId)
BEGIN
	-- Step
	SET @vCurrentId += 1000;

	INSERT
		@tCustomerIdSet
		(FromId, ToId)
	VALUES
		(@vLastId, @vCurrentId)

	SET @vLastId = @vCurrentId + 1;
END

SELECT
	FromId,
	ToId
FROM
	@tCustomerIdSet

Open in new window


Then based in my example you can alter SpTestMembersData with two more parameters:
ALTER PROC
	[dbo].[SpTestMembersData]
(
	@pFromId INT,
	@pToId INT
)
AS

DECLARE @tMemberIds TABLE
(
	MemberId INT
)

INSERT
	@tMemberIds
SELECT
	MemberId
FROM
	dbo.member
WHERE
	MemberId BETWEEN @pFromId AND @pToId

...............ETC ETC ETC....

Open in new window


Now, you can call SpSplitMembersId and fill a data table with that, from here you can use System.Threading.ThreadPool.QueueUserWorkItem method to call a method that receive two parameters (fromId and toId), that method must generate the pdfs for that customers range.

The pseudo-code can be something like this:
For Each FromToRow In CustomerSetRows
      System.Threading.ThreadPool.QueueUserWorkItem(AddressOf GenerateCustomersPdf, New Object() {FromToRow(0), FromToRow(1) })
Next

Open in new window


That is it! the System.Threading.ThreadPool class will take care of the threads to be running in parallel, if is right for you please open a new question for this, so we can handle this from that question.
0
 
hrodriguez761Author Commented:
yv989c, you are a genius, again your solution is just brilliant, easy to understand and implement - i'm still reading about the System.Threading.ThreadPool so that i can undestand it better. i opened a new question here:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_27033402.html

Thanks
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Thanks buddy, those words are too much for me, am not a genius, I just like my job and I've been through these problems, I'm glad to help. I published my suggestion in the question that you opened.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now