For consistency's sake we should update the Developer list page to look nice too. The following steps demonstrate how you can mix code and templates to keep your code in your classes where it belongs while leaving most if not all of the HTML rendering external in a template page. We'll also discuss using Sessions to do multi-page operations and I'll throw in some discussion of security and logins to keep people from editing and deleting data.

I'm going to add a new method to our server called default which is going to display our developer list similar to ShowDevelopers, but it will use a template and provide some query capabilities. This request will be a bit more complex than the ones we've been coding because we'll deal with two sets of data we need to manage: The query result as well as the query parameters.

Let's start in FrontPage by designing the page:

You can see the fields of the query form filled by a poQuery object. This is a tempoary object that is used to track the form variables and echo them back to the form when read.

When the user clicks the Search for developers button the query is displayed between the two horizontal lines you see on the bottom of the form with a simple:

<hr>
<%= pcDeveloperList %>
<hr>

pcDeveloperList is actually updated in the Fox code and contains the output from wwShowCursor() which will be the same list we used before.

Now searching becomes a little more difficult in this scenario, because we have various dynamic parameters that the user provides. You have a choice here of how you deal with this in terms of the business object. You can either decide that since this is indeed a fairly dynamic query that involves most of the fields as possible query parameters in the query that it's easier to simply build the Query and its SQL statement directly into the form. Or you can choose to go ahead and build a business object method that takes the various result values as parameters.We'll do the latter to keep the query creation code out of the Web application.

So let's add a method to the cDeveloper class called DeveloperListQuery:

LPARAMETERS lcCompany, lcName, lcCity, lcState, ;
            lcZip, lcZip2, lcCountry, ;
            lnDev, lnSupport, lnTraining, lcOrder,;
            lcFields, lnMode

IF EMPTY(lcCountry)
   lcCountry = "United States"
ENDIF
IF EMPTY(lcFields)
   lcFields = "company,name,state,City,Country,pk"
ENDIF


lcWhere = "Approved=1"
IF !EMPTY(lcName)
    lcWhere = lcWhere + " AND LOWER(Name) like '" + LOWER(lcName) + "%'"
ENDIF
IF !EMPTY(lcCompany)
    lcWhere = lcWhere + " AND LOWER(Company) like '" + LOWER(lcCompany) + "%'"
ENDIF
IF !EMPTY(lcCity)
    lcWhere = lcWhere + " AND LOWER(City) LIKE '" + LOWER(lcCity) + "%'"
ENDIF
IF !EMPTY(lcState)
    lcWhere = lcWhere + " AND LOWER(state) LIKE '" + LOWER(lcState) + "%'"
ENDIF
IF !EMPTY(lcZip) AND EMPTY(lcZip2)
    lcWhere = lcWhere + " AND Zip like '" + lcZip + "%'"
ENDIF
IF !EMPTY(lcZip) AND !EMPTY(lcZip2)
    lcWhere = lcWhere + " AND Zip>='" + lcZip + "' and Zip<='" + lcZip2 +"'"
ENDIF
IF !EMPTY(lcCountry)
    lcWhere = lcWhere + " AND LOWER(Country) like '" + LOWER(lcCountry) + "%'"
ENDIF

IF NOT (lnDev=0 AND lnTraining=0 AND lnSupport=0)
    IF lnDev = 1
        lcWhere = lcWhere + " AND dev=" + TRANSFORM(lnDev)
    ENDIF
    IF lnSupport = 1
        lcWhere = lcWhere + " AND support=" + TRANSFORM(lnSupport)
    ENDIF
    IF lnTraining = 1
        lcWhere = lcWhere + " AND training=" + TRANSFORM(lnTraining)
    ENDIF
ENDIF

IF EMPTY(lcOrder)
   lcOrder = "Company"
ENDIF

lcOrder = "ORDER BY " +lcOrder

THIS.cSQLcursor = "TDevelopers"
THIS.cSQL = "select " + lcFields + " from " + THIS.cFileName + ;
             " WHERE " + lcWhere + " " + lcOrder

lnResult = THIS.QUERY(THIS.cSQL)

THIS.cSQLCursor = "TQuery"

RETURN lnResult

Notice those 10 (count 'em) parameters. Ugggh. An alternative would be to create a Query object that allows you to set properties on an object and pass that in, but that's actually even more code. This code just takes the parameters and converts them into SQL filters in the WHERE clause plus the order by that determines how records are ordered.

With this method in place we can now easily query the data, which will reduce the amount of code in the Web process method. I'll call this one default (Default.dp) because this will be our home page and if you'll remember the HTML table data will generate into pcDeveloperList variable (at the bottom of this code), which embeds and displays the list on the template. Here's the code:

FUNCTION Default
LOCAL loSc as wwShowCursor, loDev as wwDevRegistry
PRIVATE pcDeveloperList as String

*** Object that'll hold the search vars as properties
*** PRIVATE so it's visible to the external template
PRIVATE poQuery
poQuery = CREATEOBJECT("Relation")
poQuery.AddProperty("DevName","")
poQuery.AddProperty("Company","")
poQuery.AddProperty("City","")
poQuery.AddProperty("State","")
poQuery.AddProperty("Zip","")
poQuery.AddProperty("Zip2","")
poQuery.AddProperty("Country","United States")
poQuery.AddProperty("Sortby",1)
poQuery.AddProperty("Dev",0)
poQuery.AddProperty("Training",0)
poQuery.AddProperty("Support",0)
 
*** Collect all the form vars into this object
Request.FormVarsToObject(poQuery,"txt")

IF poQuery.SortBy = 1
   lcOrder = "Company"
ELSE
   lcOrder = "Country,State,City"
ENDIF

loDev = CREATEOBJECT("cDeveloper")
loDev.DeveloperListQuery(poQuery.Company,poQuery.DevName,;
                                  poQuery.City,poQuery.State,;
                                  poQuery.Zip,poQuery.Zip2,;
                                  poQuery.Country,;
                                  poQuery.Dev,poQuery.Support,poQuery.Training,;
                                  lcOrder)

*** Post process for links
SELECT [<a href="Showdeveloper.dp?Id=] + TRANSFORM(pk) + [">] + Company  + [</a>] as Company, ;
       Name as Contact, State, City, Country ;
       FROM TDevelopers ;
   INTO CURSOR TQuery

loSC = CREATEOBJECT("wwShowCursor")
loSC.lAlternateRows = .T.
loSC.cExtraTableTags = " style='font:normal normal 8pt tahoma'"

loSC.ShowCursor()
pcDeveloperList = loSC.GetOutput()

IF RECCOUNT() = 0
  pcDeveloperList = pcDeveloperList + ;
    "<p><center><b style='color:darkred'>No entries matched your search</b></center></p>"
ENDIF

Response.ExpandTemplate( Request.GetPhysicalPath() )
ENDFUNC

Notice the use of Query object to hold the form variables from the query form. Why do I do this? Well, on the Web page I'd like to echo back the values of the input form and using the object is an easy way to do this. If you look back on the FrontPage image you'll see that the query form has <%= poQuery.Company %> for the company name value - using the object makes it easy to display this value with out calling Request.Form("txtCompany") for each field. In addition, the parameters we pass to the DeveloperListQuery() method also requires those same values and here again we can simply use the object value instead of calling Request.Form() for each of the fields.

Notice the call to Request.FormVarsToObject() which pulls all the form variables that match variable names into the object. The field names have a txt prefix (txtCompany, txtDevName etc) and that prefix is adjusted for. Using the poQuery object simplifies the job of passing this data around.

If we run this form we now see the developer list as expected and it looks like this:

Handling Query State between requests

Cool. Play around with this and notice that the query is working correctly. But also notice that I cheated a little bit here! When we built the original developer list it was a paged list that only displayed a few records at a time - the list now is displaying all developers. To add that functionality back we'll have to do a little bit more work because we now have a dynamic query we're running with input provided by the users. Think about it: The user entered some data and if we see the link to go to page 2 the application has now lost the form variables that were entered to create the query. poQuery is going to have all blank values which means the paging mechanism without state keeping won't work.

What we have to do is persist the query information between requests. To do this we need to use a Session object. We'll need a couple of thing to get this to work. First we need to add Session support. I'll add the call to InitSession into this method only since this is the only method that'll require it. If your app relies on session data in more than a few methods you probably should put the call to InitSession into the Process() method. We'll add this at the top of the method.

THIS.InitSession()
Session = THIS.oSession

But what do we save there? All the search parameters? That's an option - but there's an easier way. We can simply take the poQuery object, persist into XML and store that in the session using wwXML::ObjectToXML(). Add this code:

loXML = CREATEOBJECT("wwXML")

IF EMPTY(Request.Form("btnSubmit"))
   *** Not a form submission - we're paging or first time access
   lcXML = Session.GetSessionVar("poQuery")

   IF !EMPTY(lcXML)  && Empty - first time access: Do nothing
      *** Load poQuery from the stored data of the last query
      loXML.XMLToObject(lcXML,poQuery)  && restore the persisted data
   ENDIF
ELSE
   Session.SetSessionVar("poQuery",loXML.ObjectToXML(poQuery))
ENDIF

Finally we can add the paging support to ShowCursor by adding these two lines after loSC has been created:

loSC.nPage_itemsPerpage = 8
loSc.cPage_PageUrl = "Default.dp?"

Ok, now let's test this again. The list should now page to 8 pages a piece. To test make sure you pick a query that returns more than 8 items such as Zip between 000 and 500 maybe. Notice now as you page through the list that your query stays intact and that the query search box remains filled out on each hit. This is because even though the user didn't fill out these values on subsequent hits these values come from the poQuery object that was persisted in the Session object.

Pretty slick, huh? Using XML in this fashion allows you to very easily save complex data in a session and pull it back out. Objects are a great way to make this work because ObjectToXML() can with a single line of code persist an object to the session.