hrodriguez761
asked on
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,
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,
Carlos Villegas
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.
ASKER
yv989c,
thanks for your quick response here is a portion of the code for the printing process.. hopes this gives you a better picture
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
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.
ASKER
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()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()
Wow! I will tell you when I have something ok.
ASKER
Thanks for your help- I pasted the code twice.
ASKER
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?
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?
Forget the tables definitions, I will give you an example explaining how handle this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
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..
ASKER
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,
Again thanks so much for all your help on this,
my best regards,
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.
ASKER
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
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:
Then based in my example you can alter SpTestMembersData with two more parameters:
Now, you can call SpSplitMembersId and fill a data table with that, from here you can use System.Threading.ThreadPoo
The pseudo-code can be something like this:
That is it! the System.Threading.ThreadPoo
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
@tCustomerIdSetThen 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....Now, you can call SpSplitMembersId and fill a data table with that, from here you can use System.Threading.ThreadPoo
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) })
NextThat is it! the System.Threading.ThreadPoo
ASKER
yv989c, you are a genius, again your solution is just brilliant, easy to understand and implement - i'm still reading about the System.Threading.ThreadPoo
https://www.experts-exchange.com/questions/27033402/System-Threading-ThreadPool.html
Thanks
https://www.experts-exchange.com/questions/27033402/System-Threading-ThreadPool.html
Thanks
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.