1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
| PROCEDURE GetPage(cTablename,cCursorname,nPageSize, lFirst, lLast, nOffset, cOffSet, cWhere, cOrderBy)
LOCAL cOrderBy2, cOrderBy3, cPageSize, cTop
* nPageSize = Size of the pages, in your example 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.
* Getting the 50 located records.
cOrderBy2 = cOrderBy
cOrderBy3 = cOrderBy
cPageSize = STR(nPageSize)
cTop = cPageSize
** Handling the naviation to a particular starting char
cWhere = cWhere + IIF(EMPTY(cOffSet),'',' AND '+GETWORDNUM(cOrderBY,1) + " LIKE '"+ALLTRIM(cOffSet)+"%'")
DO CASE
CASE lLast
cOrderBy2 = cOrderBy+' DESC'
CASE nOffSet > 0
cTop = STR(nOffset+nPageSize)
cOrderBy3 = cOrderBy3+' DESC'
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 TOP <<cPageSize>> * FROM (
SELECT TOP <<cPageSize>> * FROM (
SELECT TOP <<cTop>> *
FROM <<cTablename>>
WHERE <<cWhere>>
ORDER BY <<cOrderBy2>> ) as A
ORDER BY <<cOrderBy3>>) as B
INNER JOIN (SELECT COUNT(*) as totalrows FROM <<cTablename>> WHERE <<cWhere>>) as C ON 1 = 1
ORDER BY <<cOrderby>>
ENDTEXT
RETURN |
