FUNCTION GetPageResult(nPageSize, lFirst, lLast, nOffset, cOffSet, cWhere, cOrderBy) LOCAL cOrderBy2, nLower, nUpper * nPageSize = Size of the pages, in our cases 50 * lFirst, Show first page * lLast, Show last page * nOffSet, Show from record nOffset onwards * cOffset, drilldown to an alphabetic character selected (could be one or more, so A,B,C or AA, AB, AC etc) * cWhere: It is assumed that the selection criteria: cWhere is constructed by an upper layer. So for example * WHERE State = 'State of California' AND status IN(2,3) * WHERE Address LIKE '%Lane%' AND State is 'NY' AND status = 1 * cOrderBy Indicates the fields on which the resultsets need to be ordered. This could be a comma delimited list * in this case the selection of offset would only operate on the first column in the list * It assumed that the current page location is handled by the application layer (stateless) and the page number display is assumed * to be handled by the upper layer. This routine is only retrieving the requested pages from the backend. cOrderBy2 = cOrderBy cWhere = cWhere + ' AND '+GETWORDNUM(cOrderBY,1,",") + ' LIKE '+ALLTRIM(cOffSet)+'%' nLower = 1 nUpper = nPageSize DO CASE CASE lLast cOrderBy2 = cOrderBy+' DESC' CASE nOffSet > 0 nLower = nOffSet nUpper = nOffSet + nPageSize -1 ENDCASE ** SQL Statement will return at most (nPageSize records) with the total rowcount of the selection in the Totalrows column TEXT TO cSQL TEXTMERGE NOSHOW SELECT * FROM (SELECT * FROM Customers WHERE <<cWhere>> ORDER BY <<cOrderBy2>> LIMIT <<nLower>>, <<nUpper>>) as A INNER JOIN (SELECT COUNT(*) as totalrows FROM Customers WHERE <<cWhere>>) as C ON 1 = 1 ORDER BY <<cOrderby>> ENDTEXT =SQLEXEC(Applic.Sqlhandle, cSql, 'QueryResult') RETURN
