General Actions:
Log-in
Wiki:
L3D's Wiki
▼
:
Document Index
»
Space:
XWiki
▼
:
Document Index
»
Page:
LiveTableResultsMacros
Search
Page Actions:
Export
▼
:
Export as PDF
Export as RTF
Export as HTML
More actions
▼
:
Print preview
View Source
Welcome to your wiki
»
XWiki Space
»
Livetable Results Macros
Wiki source code of
Livetable Results Macros
Last modified by
Administrator
on 2011/03/02 17:34
Content
·
Comments
(0)
·
Attachments
(0)
·
History
·
Information
Show line numbers
{{velocity output="false"}} #** * Macro to get the results of a livetable data call. * This page is called from live grids via Ajax with the argument xpage=plain. It returns a * set of results serialized in JSON. *# #macro(gridresult $className $collist) #gridresultwithfilter($className $collist '' '' []) #end #** * Macro to get the results of a livetable data call. * This page is called from live grids via Ajax with the argument xpage=plain. It returns a * set of results serialized in JSON. *# #macro(gridresultwithfilter $className $collist $filterfrom $filterwhere $filterParams) #if("$!{request.xpage}" == 'plain') $response.setContentType('application/json') #end #set($offset = $util.parseInt($request.get('offset'))) ## offset starts from 0 in velocity and 1 in javascript #set($offset = $offset - 1) #if($offset < 0) #set($offset = 0) #end #set($limit = $util.parseInt($request.get('limit'))) #set($tablelist = $util.arrayList) ## ## Compute the query to use for retrieving data ## ## ## additional columns, should *not* contain parameters, all added column names must be filtered #set($fromSql = '') ## parametrized filter part of the query #set($whereSql = '') ## list of parameters to use with $whereSql #set($whereParams = []) #set($class = $xwiki.getDocument($className).getxWikiClass()) ## Exclude Blacklisted spaces ($blacklistedSpaces is set in xwikivars.vm) ## #if($blacklistedSpaces.size() > 0) #set($whereSql = "${whereSql} and doc.space not in (") #foreach ($blacklistedSpace in $blacklistedSpaces) #if($velocityCount > 1)#set ($whereSql = "${whereSql},")#end #set($whereSql = "${whereSql}?") #set($discard = $whereParams.add("$!{blacklistedSpace}")) #end #set($whereSql = "${whereSql})") #end ## Add the columns needed for the actual data ## #foreach($colname in $collist) #livetable_addColumnToQuery($colname) #end ## Tag filtering ## #if($request.tag) #set($fromSql = "${fromSql} , BaseObject as tobject, DBStringListProperty as tagprop") #set($whereSql = "${whereSql} and tobject.className='XWiki.TagClass' and tobject.name=doc.fullName and tobject.id=tagprop.id.id and tagprop.id.name='tags' and (") #foreach($tag in $request.getParameterValues('tag')) #set($tag = $util.decodeURI($tag)) #if($velocityCount > 1) #set($whereSql = "${whereSql} and ") #end #set($whereSql = "${whereSql} ? in elements(tagprop.list)") #set($discard = $whereParams.add("${tag}")) #end #set($whereSql = "${whereSql})") #end ## ## Order ## #set($order = "$!request.sort") #if($order != '') #set($orderDirection = "$!{request.get('dir').toLowerCase()}") #if("$!orderDirection" != '' && "$!orderDirection" != 'asc') #set($orderDirection = 'desc') #end #if($order == 'description') #set($tableAlias = 'dtable') #else #set($tableAlias = $order) #end ## only safe version should be used in SQL queries #set($safe_tableAlias = "$!{tableAlias.replaceAll('[^a-zA-Z0-9_.]','')}") ## #if($order.startsWith('doc.')) #set($orderSql = " order by ${safe_tableAlias.replace('_','.')} ${orderDirection}") #elseif($tablelist.contains($tableAlias)) #set($orderSql = " order by ${safe_tableAlias}.value ${orderDirection}") #elseif($order == 'averagevote') #livetable_getTableName($order) #set($fromSql = "${fromSql}, BaseObject as objratings, ${tableName} ${safe_tableAlias}") #set($whereSql = "${whereSql} and doc.fullName=objratings.name and objratings.className='XWiki.AverageRatingsClass' and objratings.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.name = ?") #set($discard = $whereParams.add("${order}")) #set($orderSql = " order by ${safe_tableAlias}.value ${orderDirection}") #else #set($multiselect = $class.get($order).getProperty('multiSelect').getValue()) #if("$multiselect" != '1') #livetable_getTableName($order) #set($fromSql = "${fromSql}, ${tableName} ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.name = ?") #set($discard = $whereParams.add("${order}")) #set($orderSql = " order by ${safe_tableAlias}.value ${orderDirection}") #end #end #end ## ## Compute the final queries ## #set($sqlParams = []) #set($tagsMatchingParams = []) #set($allMatchingParams = []) #if("$!className" != '') ## Class query #set($countSql = ", BaseObject as obj $!fromSql $!filterfrom where obj.name=doc.fullName and obj.className = ? and doc.fullName <> ? $!whereSql $!filterwhere") #set($discard = $sqlParams.addAll(["${className}", "${className}Template"])) #set($discard = $sqlParams.addAll($whereParams)) ## #set($tagsMatchingFiltersFrom = ", BaseObject as obj $!fromSql $!filterfrom") #set($tagsMatchingFiltersWhere = "obj.name=doc.fullName and obj.className = ? and doc.fullName <> ? $!whereSql $!filterwhere") #set($discard = $tagsMatchingParams.addAll(["${className}", "${className}Template"])) #set($discard = $tagsMatchingParams.addAll($whereParams)) ## #set($allMatchingTagsFrom = ", BaseObject as obj $!filterfrom") #set($allMatchingTagsWhere = "obj.name=doc.fullName and obj.className = ? and doc.fullName <> ? $!filterwhere") #set($discard = $allMatchingParams.addAll(["${className}", "${className}Template"])) ## #if($filterParams) #set($discard = $sqlParams.addAll($filterParams)) #set($discard = $tagsMatchingParams.addAll($filterParams)) #set($discard = $allMatchingParams.addAll($filterParams)) #end #else ## Document query #set($countSql = "$!fromSql $!filterfrom where 1=1 $!whereSql $!filterwhere") #set($discard = $sqlParams.addAll($whereParams)) ## #set($tagsMatchingFiltersFrom = "$!fromSql $!filterfrom") #set($tagsMatchingFiltersWhere = "1=1 $!whereSql $!filterwhere") #set($discard = $tagsMatchingParams.addAll($whereParams)) ## #set($allMatchingTagsFrom = "$!filterfrom") #set($allMatchingTagsWhere = "1=1 $!filterwhere") ## #if($filterParams) #set($discard = $sqlParams.addAll($filterParams)) #set($discard = $tagsMatchingParams.addAll($filterParams)) #set($discard = $allMatchingParams.addAll($filterParams)) #end #end ## TagCould matching the current filters #set($tagsMatchingFilters = $xwiki.tag.getTagCountForQuery($tagsMatchingFiltersFrom, $tagsMatchingFiltersWhere, $tagsMatchingParams)) ## TagCloud matching all the documents used by the livetable #set($allMatchingTags = $xwiki.tag.getTagCountForQuery($allMatchingTagsFrom, $allMatchingTagsWhere, $allMatchingParams)) #set($sql = "$countSql $!{orderSql}") #set($items = $xwiki.searchDocuments($sql, $limit, $offset, $sqlParams)) #set($totalItems = $xwiki.countDocuments($countSql, $sqlParams)) #set($returnedItems = $items.size()) ## ============================================== ## json starts ## ============================================== { #if("$!request.sql" == '1') "sql": "$escapetool.javascript($sql)", "countsql": "$escapetool.javascript($countSql)", "params": "$escapetool.javascript($sqlParams)" #end "totalrows": $totalItems, "matchingtags": { #foreach($tag in $tagsMatchingFilters.keySet()) #if($velocityCount > 1) , #end "$escapetool.javascript($tag)": {} #end }, "tags" : [ #foreach($tag in $allMatchingTags.keySet()) #if($velocityCount > 1) , #end {"tag": "$escapetool.javascript($tag)", "count": $allMatchingTags.get($tag)} #end ], "returnedrows": #if($returnedItems < $limit) $returnedItems #else $limit #end, "offset": $mathtool.add($offset, 1), "reqNo": $util.parseInt($request.reqNo), "rows": [ #foreach($item in $items) #set($viewable = $xwiki.hasAccessLevel('view', $context.user, "${context.database}:${item}")) #if($velocityCount > 1) , #end { "doc_viewable" : $viewable, #if(!$viewable) "doc_fullname" : "$escapetool.javascript(${context.database}):$escapetool.javascript(${item})" #else #set($itemDoc = $xwiki.getDocument($item)) #set($fullname = "${itemDoc.wiki}:${itemDoc.space}.${itemDoc.name}") #set($discard = $itemDoc.use($className)) #set($itemUrl = $xwiki.getURL($item)) #set($spaceUrl = $xwiki.getURL("${itemDoc.wiki}:${itemDoc.space}.WebHome")) #if($xwiki.isVirtualMode()) #set($wiki = $itemDoc.wiki) #set($wikiUrl = $xwiki.getURL("${itemDoc.wiki}:Main.WebHome")) #end #set($editUrl = $itemDoc.getURL($itemDoc.defaultEditMode)) #set($copyUrl = $xwiki.getURL('XWiki.CopyDocument', 'view', "sourcedoc=${escapetool.url($fullname)}")) #set($deleteUrl = $itemDoc.getURL('delete')) #set($renameUrl = $itemDoc.getURL('view', 'xpage=rename&step=1')) #set($rightsUrl = $itemDoc.getURL('edit', 'editor=rights')) "doc_name" : "$escapetool.javascript($itemDoc.name)", "doc_fullname" : "$escapetool.javascript($fullname)", "doc_space" : "$escapetool.javascript(${itemDoc.space})", "doc_url" : "$escapetool.javascript(${itemUrl})", "doc_space_url" : "$escapetool.javascript(${spaceUrl})", #if($xwiki.isVirtualMode()) "doc_wiki" : "$escapetool.javascript($wiki)", "doc_wiki_url" : "$escapetool.javascript($wikiUrl)", #end "doc_hasadmin" : $xwiki.hasAdminRights(), "doc_edit_url" : "$escapetool.javascript($editUrl)", "doc_copy_url" : "$escapetool.javascript($copyUrl)", "doc_delete_url" : "$escapetool.javascript($deleteUrl)", "doc_rename_url" : "$escapetool.javascript($renameUrl)", "doc_rights_url" : "$escapetool.javascript($rightsUrl)", "doc_author_url" : "$escapetool.javascript($xwiki.getURL($itemDoc.author))", "doc_date" : "$escapetool.javascript(${xwiki.formatDate($itemDoc.date)})", "doc_title" : "$escapetool.javascript($xwiki.getXMLEncoded(${itemDoc.getDisplayTitle()}))", "doc_author" : "$escapetool.javascript(${xwiki.getLocalUserName($itemDoc.author, false)})", "doc_creationDate" : "$escapetool.javascript(${xwiki.formatDate($itemDoc.creationDate)})", "doc_creator" : "$escapetool.javascript(${xwiki.getLocalUserName($itemDoc.creator, false)})" #foreach($colname in $collist) #if($colname.startsWith('doc.')) #elseif($colname == '_action') , #set($text = $msg.get("${request.transprefix}actiontext")) "$escapetool.javascript(${colname})" : "$escapetool.javascript(${text})" #elseif($colname == '_attachments') , #livetable_getAttachmentsList($itemDoc) "$escapetool.javascript(${colname})" : "$escapetool.javascript(${attachlist})" #elseif($colname == '_avatar') , #livetable_getAvatar($itemDoc) "$escapetool.javascript(${colname})" : "$escapetool.javascript(${avatar})" #elseif($colname == '_images') , #livetable_getImagesList($itemDoc) "$escapetool.javascript(${colname})" : "$escapetool.javascript(${imagesList})" #else , #set($propClassName = "$!{request.get(${colname.concat('_class')})}") #if($propClassName != '') #set($propClass = $xwiki.getDocument($propClassName).getxWikiClass()) #set($discard = $itemDoc.use($propClassName)) #else #set($propClass = $class) #end ## Retrieve the property type as it might be usefull #set($propType = '') #if($listtool.contains($propClass.getPropertyNames(), $colname)) #set($propType = "$!{propClass.get($colname).type}") #end #set($fieldValue = "$!itemDoc.getValue($colname)") #set($fieldDisplayValue = "$!itemDoc.display($colname, 'view')") #if($fieldDisplayValue == '') #set($fieldDisplayValue = $msg.get("${request.transprefix}emptyvalue")) #end #set($fieldUrl = '') ## only retrieve an URL for a DBListClass item #if($propType == 'DBListClass') #set($fieldUrl = $xwiki.getDocument($fieldValue).getURL()) #if($fieldUrl == $xwiki.getURL('Main.WebHome')) #set($fieldUrl = '') #end #end "$escapetool.javascript(${colname})" : "$escapetool.javascript($fieldDisplayValue.replaceFirst($regextool.quote('{{html clean="false" wiki="false"}}'), '').replaceAll("$regextool.quote('{{/html}}')$", ''))", "$escapetool.javascript(${colname})_value" : "$escapetool.javascript(${fieldValue})", "$escapetool.javascript(${colname})_url" : "$escapetool.javascript(${fieldUrl})" ## reset to the default class #set($discard = $itemDoc.use($className)) #end #end #end } #end ]} ## ============================================== ## json ended ## ============================================== #end #** * Get the name of the Property that should be used for a given livetable column. * NOTE the resulting $tableName is safe to use inside SQL queries *# #macro(livetable_getTableName $colname) #set($propClassName = "$!request.get(${colname.concat('_class')})") #if($propClassName != '') #set($propClass = $xwiki.getDocument($propClassName).getxWikiClass()) #else #set($propClass = $class) #end #set($propType = '') #if($listtool.contains($propClass.getPropertyNames(), $colname)) #set($propType = "$!{propClass.get($colname).type}") #end #if($propType == 'NumberClass') #set($numberType = $propClass.get($colname).getProperty('numberType').getValue()) #if($numberType == 'integer') #set($tableName = 'IntegerProperty') #elseif($numberType == 'float') #set($tableName = 'FloatProperty') #elseif($numberType == 'double') #set($tableName = 'DoubleProperty') #else #set($tableName = 'LongProperty') #end #elseif($propType == 'BooleanClass') #set($tableName = 'IntegerProperty') #elseif($propType == 'DateClass') #set($tableName = 'DateProperty') #elseif($propType == 'TextAreaClass' || $propType == 'UsersClass' || $propType == 'GroupsClass') #set($tableName = 'LargeStringProperty') #elseif($propType == 'StaticListClass' || $propType == 'DBListClass' || $propType == 'DBTreeListClass') #set($multiSelect = $propClass.get($colname).getProperty('multiSelect').getValue()) #set($relationalStorage = $propClass.get($colname).getProperty('relationalStorage').getValue()) #if($multiSelect == 1) #if($relationalStorage == 1) #set($tableName = 'DBStringListProperty') #else #set($tableName = 'StringListProperty') #end #else #set($tableName = 'StringProperty') #end #else #set($tableName = 'StringProperty') #end #end #** * Old alias of the #livetable_getTableName macro. * @deprecated since 2.2.3, use {@link #livetable_getTableName} *# #macro(grid_gettablename $colname) #livetable_getTableName($colname) #end #** * List attachments for a document, putting the result as HTML markup in the $attachlist variable. *# #macro(livetable_getAttachmentsList $itemDoc) #set($attachlist = '') #foreach($attachment in $itemDoc.attachmentList) #set($attachmentUrl = $itemDoc.getAttachmentURL($attachment.filename)) #set($attachlist = "${attachlist}<a href='${attachmentUrl}'>$attachment.filename</a><br/>") #end #end #** * Old alias of the #livetable_getAttachmentsList macro. * @deprecated since 2.2.3, use {@link #livetable_getAttachmentsList} *# #macro(grid_attachlist $itemDoc) #livetable_getAttachmentsList($itemDoc) #end #** * List image attachments for a document, putting the result as HTML markup in the $imagesList variable. *# #macro(livetable_getImagesList $itemDoc) #set($imagesList = '') #foreach ($attachment in $itemDoc.attachmentList) #if($attachment.isImage()) #set($attachmentUrl = $itemDoc.getAttachmentURL($attachment.filename)) #set($imagesList = "${imagesList}<img src='${attachmentUrl}' alt='${attachment.filename}' width='50'/><br/>") #end #end #end #** * Old alias of the #livetable_getImagesList macro. * @deprecated since 2.2.3, use {@link #livetable_getImagesList} *# #macro(grid_photolist $itemDoc) #livetable_getImagesList($itemDoc) #end #** * Generate the HTML code for a user avatar, with a fixed 50px width. *# #macro(livetable_getAvatar $itemDoc) #set ($avatar = "#mediumUserAvatar($itemDoc.fullName)") #set ($avatar = $avatar.trim()) #end #** * Old alias of the #livetable_getAvatar macro. * @deprecated since 2.2.3, use {@link #livetable_getAvatar} *# #macro(grid_avatar $itemDoc) #livetable_getAvatar($itemDoc) #end #** * Macro to extend the query to select the properties for the livetable columns. * NOTE $colname is filtered (all characters but [a-zA-Z0-9_.] are removed) before use *# #macro(livetable_addColumnToQuery $colname) #set($filterValue = "$!{request.get($colname)}") #if($colname == 'description') #set($tableAlias = 'dtable') #else #set($tableAlias = $colname) #end ## only safe versions should be used in SQL queries #set($safe_colname = "$!{colname.replaceAll('[^a-zA-Z0-9_.]','')}") #set($safe_tableAlias = "$!{tableAlias.replaceAll('[^a-zA-Z0-9_.]','')}") ## #set($propType = '') #set($propClassName = "$!request.get(${colname.concat('_class')})") #if($propClassName != '') #set($propClass = $xwiki.getDocument($propClassName).getxWikiClass()) #else #set($propClass = $class) #end #if($propClass && $listtool.contains($propClass.getPropertyNames(), $colname)) #set($propType = "$!{propClass.get($colname).type}") #end ## ## Filter values #if("$!filterValue" != '') #set($discard = $tablelist.add($tableAlias)) #if($colname.startsWith('doc.')) #set($whereSql = "${whereSql} and upper(str(${safe_colname.replace('_','.')})) like upper(?)") #set($discard = $whereParams.add("%${filterValue}%")) #elseif($propType == 'NumberClass' || $propType == 'BooleanClass') #livetable_getTableName($colname) #set($fromSql = "${fromSql}, ${tableName} ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and ${safe_tableAlias}.value = ?") #set($discard = $whereParams.addAll(["${colname}", $util.parseInt(${filterValue})])) #elseif($propType == 'DateClass') #set($fromSql = "${fromSql}, DateProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and upper(str(${safe_tableAlias}.value)) like upper(?)") #set($discard = $whereParams.addAll(["${colname}", "%${filterValue}%"])) #elseif($propType == 'TextAreaClass' || $propType == 'UsersClass' || $propType == 'GroupsClass') #set($fromSql = "${fromSql}, LargeStringProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and upper(${safe_tableAlias}.value) like upper(?)") #set($discard = $whereParams.addAll(["${colname}", "%${filterValue}%"])) #elseif($propType == 'StaticListClass' || $propType == 'DBListClass' || $propType == 'DBTreeListClass') #set($multiSelect = $propClass.get($colname).getProperty('multiSelect').getValue()) #set($relationalStorage = $propClass.get($colname).getProperty('relationalStorage').getValue()) #if($multiSelect == 1) #if($relationalStorage == 1) #set($fromSql = "${fromSql}, DBStringListProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and ? in elements(${safe_tableAlias}.list)") #set($discard = $whereParams.addAll(["${colname}", "${filterValue}"])) #else #set($fromSql = "${fromSql}, StringListProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and upper(${safe_tableAlias}.textValue) like upper(?)") #set($discard = $whereParams.addAll(["${colname}", "%${filterValue}%"])) #end #else #set($fromSql = "${fromSql}, StringProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and ${safe_tableAlias}.value = ?") #set($discard = $whereParams.addAll(["${colname}", "${filterValue}"])) #end #else #set($fromSql = "${fromSql}, StringProperty ${safe_tableAlias}") #set($whereSql = "${whereSql} and obj.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ? and upper(${safe_tableAlias}.value) like upper(?)") #set($discard = $whereParams.addAll(["${colname}", "%${filterValue}%"])) #end #end #end #** * Old alias of the #livetable_addColumnToQuery macro. * @deprecated since 2.2.3, use {@link #livetable_addColumnToQuery} *# #macro(grid_addcolumn $colname) #livetable_addColumnToQuery($colname) #end {{/velocity}}
Quick Links
L3D Calendar
Wiki Dashboard
Document Index
Blog
Sandbox
