Hello everyone
Today we will be looking at further enhancements to our Desktop App by building a custom Report Viewer.
Looks interesting right. Well to see it in action watch this short video then follow along to see how it is done.
I hope you enjoyed the video. Lets get started.
As I mentioned in the video our lesson on xBasic and SuperControls states thatyou can pass object properties to the embedded xDialog Report viewer allowing you to view any report in your application from just the one object. I cannot make this work. So instead I created my own report viewer using xBasic and a HTML memo field. In the video you see that the reports are generated by passing HTML code to our HTML memo field on our form. Here is the code in it’s entirety.
'Date Created: 25-Jun-2015 04:19:12 PM
'Last Updated: 01-Jul-2015 08:31:13 AM
'Created By : Natn
'Updated By : Natn
parentform.commit()
ui_freeze(.t.)
if ListState = "CDB" then
if vSRList = "Activities" then
dim html_text as C
dim html_body as C
dim rtftext as C
dim tbl as P
dim t as P
dim c_ID as C
dim cMemo as C
dim fMemo as C
c_ID = ""
c_ID = word(vStartDir.value,2," ",1)
t = table.open("act_memos")
t.fetch_first()
while .not. t.fetch_eof()
if t.act_id = c_ID then
rtftext = *bin_to_rtf(t.cont_memo)
cMemo = *rtf_to_html(rtftext)
fMemo = t.fu_memo
end if
t.fetch_next()
end while
t.close()
html_body = "</font></p><font size=2 face=Arial><strong>" + "Origianl Note: "+ "<br>" +"____________________" + Alltrim(cMemo) + "<br>" + "</font></p><font size=2 face=Arial><strong>" + "Follow Up Note" + "<br>" +"____________________" + "<br>"+ Alltrim(fMemo) +"</strong></font></font></font> </body></html>"
html_text = file.to_string(a5.Get_Path()+"\Documents\QuickNote1.htm")+html_body
tbl = table.open("filemanager")
tbl.fetch_first()
tbl.change_begin(.t.)
html_text = html_text+"</font></p></body>"
tbl.hbody = Left(Alltrim(html_text),-1)
tbl.change_end(.t.)
tbl.close()
xbasic_wait_for_idle()
HBody.refresh()
ui_freeze(.f.)
xbasic_wait_for_idle()
sys_send_keys("{F5}")
else if vSRList = "Business" then
dim html_body as C
dim co_Name as C
co_Name = ""
co_Name = vStartDir.value
cot = table.open("company")
cot.fetch_first()
while .not. cot.fetch_eof()
if cot.company_Na = Var->co_Name then
html_body = "<h4align=justify><strong><font color=#003366 size=3><u>Billing Information</u></font></strong> </h4><br><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Company Name: "+ cot.company_na+"<br>DBA Name: " + UPPER(cot.dba_name) + "</b></font></strong> </p>" +\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Billing Address: " + cot.bill_addre + " " + cot.bill_addr0 + "<br>Billing City: " + Alltrim(cot.bill_city) +", " + UPPER(cot.State) + " " + cot.zip + "</b></font></strong> </p>"
html_body = Alltrim(html_body) + "<h4align=justify><strong><font color=#003366 size=3><u><br>Shipping Information</u></font></strong> </h4><br>"
if cot.shipsameas = .t. then
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b><i>Shipping Address Same as Billing</i></b></font></strong> </p>"
else
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Shipping Address: " + cot.ship_addre + cot.ship_addr0 + "<br>Shipping City: " + Alltrim(cot.ship_city) +", " + UPPER(cot.ship_state) + " " + cot.ship_zip+ "</b></font></strong> </p>"
end if
html_body = html_body + "<h4align=justify><strong><font color=#003366 size=3><u><br>Contact Information</u></font></strong> </h4><br>"+\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Main Phone Nbr: " + cot.phone_nbr + "<br>Company EMail: " + cot.co_email + "<br>Web Site: " + Alltrim(cot.web_site)+ "</b></font></strong> </p>"
end if
cot.fetch_next()
end while
cot.close()
html_text = file.to_string(a5.Get_Path()+"\Documents\CoCard1.htm")+html_body
tbl = table.open("filemanager")
tbl.fetch_first()
tbl.change_begin(.t.)
' html_text = html_text
tbl.hbody = Alltrim(html_text)
tbl.change_end(.t.)
tbl.close()
xbasic_wait_for_idle()
HBody.refresh()
ui_freeze(.f.)
xbasic_wait_for_idle()
sys_send_keys("{F5}")
else if vSRList = "People" then
dim html_body as C
dim tdef as C
dim Contact_Name as C
tdef = ""
Contact_Name = ""
Contact_Name = vStartDir.value
Cnt = table.open("People")
Cnt.fetch_first()
while .not. Cnt.fetch_eof()
if Cnt.Contact = Var->Contact_Name then
html_body = "<h4align=justify><strong><font color=#003366 size=3><u>Personal Information</u></font></strong> </h4><br><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Contact Name: "+ Cnt.Contact+"</b></font></strong> </p>" +\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Contact Address: " + Cnt.Home_Addre + " " + Cnt.Home_Addr0 + "<br>Home City: " + Alltrim(Cnt.Home_City) +", " + UPPER(Cnt.Home_State) + " " + Cnt.Home_Zip + "</b></font></strong> </p>"
html_body = Alltrim(html_body) + "<h4align=justify><strong><font color=#003366 size=3><u><br>Work Information</u></font></strong> </h4><br>"
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Business Address: " + Cnt.Address_1 + Cnt.Address_2 + "<br>Business City: " + Alltrim(Cnt.City) +", " + UPPER(Cnt.home_state) + " " + Cnt.zip+ "</b></font></strong> </p>"
html_body = html_body + "<h4align=justify><strong><font color=#003366 size=3><u><br>Contact Information</u></font></strong> </h4><br>"
dim contactID as C
dim rf as N
rf = 0
contactID = Cnt.contact_id
rf = tablecount("communication","Contact_Id = '" + Var->contactID + "' ")
if rf > 0 then
comtbl = table.open("communication")
comtbl.fetch_first()
while .not. comtbl.fetch_eof()
if comtbl.contact_id = Var->contactID then
tdef = tdef + "<tr>"+\
"<td> "+comtbl.com_type+"</td>"+\
"<td>"+comtbl.com_number+" ex: "+Alltrim(comtbl.com_ext)+"</td></tr>"
'
' html_body = html_body + "<tr><td> "+comtbl.com_type+"</td>"
' html_body = html_body + "<td>"+comtbl.com_number+" "+Alltrim(comtbl.com_ext)+"</td></tr><br>"
end if
' html_body = html_body + "</td></tr></tbody></table>"
comtbl.fetch_next()
end while
comtbl.close()
end if
end if
cnt.fetch_next()
end while
cnt.close()
html_body = ALLTRIM(html_body) + "<table border=1 cellSpacing=0 borderColor=#003366 cellPadding=4 width=0% bgColor=#ebffff align=left><tbody>"
tdef = tdef + "</td></tr></tbody></table>"
html_text = file.to_string(a5.Get_Path()+"\Documents\PeopleCard1.htm")+html_body+tdef
tbl = table.open("filemanager")
tbl.fetch_first()
tbl.change_begin(.t.)
' html_text = html_text
tbl.hbody = Alltrim(html_text)
tbl.change_end(.t.)
tbl.close()
xbasic_wait_for_idle()
HBody.refresh()
ui_freeze(.f.)
xbasic_wait_for_idle()
sys_send_keys("{F5}")
end if
end if
This code is on the OnChange event of our vStartDir list object. When the user selects a value it determines which table is being viewed, finds the proper record then does the magic. In the video I show three different report views so lets look at the code for each more closely.
Company Report Preview – Simply passes variable field values to the html document.
dim html_body as C
dim co_Name as C
co_Name = ""
co_Name = vStartDir.value
cot = table.open("company")
cot.fetch_first()
while .not. cot.fetch_eof()
if cot.company_Na = Var->co_Name then
html_body = "<h4align=justify><strong><font color=#003366 size=3><u>Billing Information</u></font></strong> </h4><br><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Company Name: "+ cot.company_na+"<br>DBA Name: " + UPPER(cot.dba_name) + "</b></font></strong> </p>" +\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Billing Address: " + cot.bill_addre + " " + cot.bill_addr0 + "<br>Billing City: " + Alltrim(cot.bill_city) +", " + UPPER(cot.State) + " " + cot.zip + "</b></font></strong> </p>"
html_body = Alltrim(html_body) + "<h4align=justify><strong><font color=#003366 size=3><u><br>Shipping Information</u></font></strong> </h4><br>"
if cot.shipsameas = .t. then
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b><i>Shipping Address Same as Billing</i></b></font></strong> </p>"
else
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Shipping Address: " + cot.ship_addre + cot.ship_addr0 + "<br>Shipping City: " + Alltrim(cot.ship_city) +", " + UPPER(cot.ship_state) + " " + cot.ship_zip+ "</b></font></strong> </p>"
end if
html_body = html_body + "<h4align=justify><strong><font color=#003366 size=3><u><br>Contact Information</u></font></strong> </h4><br>"+\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Main Phone Nbr: " + cot.phone_nbr + "<br>Company EMail: " + cot.co_email + "<br>Web Site: " + Alltrim(cot.web_site)+ "</b></font></strong> </p>"
end if
cot.fetch_next()
end while
cot.close()
html_text = file.to_string(a5.Get_Path()+"\Documents\CoCard1.htm")+html_body
tbl = table.open("filemanager")
tbl.fetch_first()
tbl.change_begin(.t.)
' html_text = html_text
tbl.hbody = Alltrim(html_text)
tbl.change_end(.t.)
tbl.close()
xbasic_wait_for_idle()
HBody.refresh()
ui_freeze(.f.)
xbasic_wait_for_idle()
sys_send_keys("{F5}")
The first thing we do is declare two variables, html_body and co_name. We then assign the value of the selected line item in our list to co_name which is then used to find the correct record in our external company.tbl. Remember that basic html is written as text and encoded to work as HTML. This makes our job easier. Using xBasic we set our html_body variable to a text string and insert the field values (ie: ” + UPPER(cot.State) + “) from the matching record into our string. We also add additional formatting to our lines under body to further enhance our report. For example <u> underlines any following text <b> emboldens text and <br> creates a paragraph line break. Please note each formatting control you add must be terminated by repeating the control with a ‘/’.
(example: </u></b>) <br> does not require a termination.)
Once we have completed our html_body variable we assign our header file and our variable to another variable HTML_text
html_text = file.to_string(a5.Get_Path()+"\Documents\CoCard1.htm")+html_body
Finally we open our filemanager table and put it into edit mode then assign HTML_text to our html memo field hbody. Save our change and refresh our object on the screen.
Activities – displays both Rich Text and plain memo text in our Report Viewer
In this example I formatted the html slightly differently to allow you to see different ways of doing the same thing; also, because the values we are adding to our HTML memo field are rich text and plain text we need to do character conversions in order for the html to read the data correctly.
t = table.open("act_memos")
t.fetch_first()
while .not. t.fetch_eof()
if t.act_id = c_ID then
rtftext = *bin_to_rtf(t.cont_memo)
cMemo = *rtf_to_html(rtftext)
fMemo = t.fu_memo
end if
t.fetch_next()
end while
t.close()
Once we find the proper record in our external table we use
*bin_to_rtf() and
*rtf_to_html()
to convert the character data to html. Our follow up memo field is just a plain text memo field so no conversion is needed.
Finally we will look at the People Report
People: – Uses field values from two separate tables and incorporates an html table for enhanced aesthetics.
If you followed along on our Contact Management App session you saw us create a separate table for contact phone numbers. This gave us the ability to have as many phone number values for a contact as we wanted. Now in a standard report, you would create a set with a one to many link from contact to communication on contact_ID then build the report. Here, our tables are both separate and external to our filemanager table. Here is what we did.
dim html_body as C
dim tdef as C
dim Contact_Name as C
tdef = ""
Contact_Name = ""
Contact_Name = vStartDir.value
Cnt = table.open("People")
Cnt.fetch_first()
while .not. Cnt.fetch_eof()
if Cnt.Contact = Var->Contact_Name then
html_body = "<h4align=justify><strong><font color=#003366 size=3><u>Personal Information</u></font></strong> </h4><br><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Contact Name: "+ Cnt.Contact+"</b></font></strong> </p>" +\
"<p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Contact Address: " + Cnt.Home_Addre + " " + Cnt.Home_Addr0 + "<br>Home City: " + Alltrim(Cnt.Home_City) +", " + UPPER(Cnt.Home_State) + " " + Cnt.Home_Zip + "</b></font></strong> </p>"
html_body = Alltrim(html_body) + "<h4align=justify><strong><font color=#003366 size=3><u><br>Work Information</u></font></strong> </h4><br>"
html_body = html_body + "<p align=justify><strong><font color=black size=2 face=\"Arial\"><b><p align=justify><strong><font color=black size=2 face=\"Arial\"><b>Business Address: " + Cnt.Address_1 + Cnt.Address_2 + "<br>Business City: " + Alltrim(Cnt.City) +", " + UPPER(Cnt.home_state) + " " + Cnt.zip+ "</b></font></strong> </p>"
html_body = html_body + "<h4align=justify><strong><font color=#003366 size=3><u><br>Contact Information</u></font></strong> </h4><br>"
dim contactID as C
dim rf as N
rf = 0
contactID = Cnt.contact_id
rf = tablecount("communication","Contact_Id = '" + Var->contactID + "' ")
if rf > 0 then
comtbl = table.open("communication")
comtbl.fetch_first()
while .not. comtbl.fetch_eof()
if comtbl.contact_id = Var->contactID then
tdef = tdef + "<tr>"+\
"<td> "+comtbl.com_type+"</td>"+\
"<td>"+comtbl.com_number+" ex: "+Alltrim(comtbl.com_ext)+"</td></tr>"
end if
comtbl.fetch_next()
end while
comtbl.close()
end if
end if
cnt.fetch_next()
end while
cnt.close()
if rf > 0 then
html_body = ALLTRIM(html_body) + "<table border=1 cellSpacing=0 borderColor=#003366 cellPadding=4 width=0% bgColor=#ebffff align=left><tbody>"
tdef = tdef + "</td></tr></tbody></table>"
html_text = file.to_string(a5.Get_Path()+"\Documents\PeopleCard1.htm")+html_body+tdef
else
html_text = file.to_string(a5.Get_Path()+"\Documents\PeopleCard1.htm")+html_body
end if
tbl = table.open("filemanager")
tbl.fetch_first()
tbl.change_begin(.t.)
tbl.hbody = Alltrim(html_text)
tbl.change_end(.t.)
tbl.close()
xbasic_wait_for_idle()
HBody.refresh()
ui_freeze(.f.)
xbasic_wait_for_idle()
sys_send_keys("{F5}")
First we build our html string from the people table and we find the contact id value and pass it to a variable. We then use that variable to open and fetch the proper records in our communication table. Now, we do not want an empty table in our report if no communication records are found so we test using tablecount to return the number of records found in communications. If the number is greated than zero we build our table fields and assign them to our tdef variable. Once the table fields are created we again test rf and if greater than zero we add tdef to our html_text variable and the rest is as above.
Well that’s it for today. I hope you find this lesson helpful.
In our next session we will continue to look for ways to enhance our desktop in antisapation of the arrival of Win 10©. Until then, I would like to say thanks again for stopping by and remember, if you need help with an Alpha Software application or wish to inquire about a custom application for your business go to our website
and inquire or contact
NLawson@cdc-TakeCharge.com
Have a great day.
Custome Database Design with Alpha Software 
Leave a comment