Livetable Results Macros

#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 (")

57

#foreach($tag in $request.getParameterValues('tag'))

58

#if($foreach.count > 1) #set($whereSql = "${whereSql} and ") #end

59

## Tags are case insensitive but they are stored unchanged which means we have to normalize them when performing

60

## a query. Unfortunately there's no simple way to match multiple tags (AND operator). If we join the list of

61

## tags in the FROM clause then we match at least one of the tags (OR operator). The only remaining option is to

62

## check that the list of tags contains each of the desired tags. HQL doesn't help us to lower-case the entire

63

## list of tags so we use an inner select for this.

64

#if ($whereParams.entrySet())

65

#set($whereSql = "${whereSql} lower(:wikitag${foreach.count}) in (select lower(tag) from tagprop.list tag)")

66

#set($discard = $whereParams.put("wikitag$foreach.count", "${tag}"))

67

#else

68

#set($whereSql = "${whereSql} lower(?) in (select lower(tag) from tagprop.list tag)")

69

#set($discard = $whereParams.add("${tag}"))

70

#end

71

#end

72

#set($whereSql = "${whereSql})")

#end

##

##

## Order

##

## if the object for the classname of the order column is not already in the from sql, put it

79

#macro(addObjectClause $objectAlias)

80

#if($fromSql.indexOf($objectAlias) < 0)

81

#set($fromSql = "${fromSql}, BaseObject $objectAlias")

82

#if ($whereParams.entrySet())

83

#set($whereSql = "${whereSql} and ${objectAlias}.name = doc.fullName and ${objectAlias}.className = :${objectAlias}_className")

84

#set($discard = $whereParams.put("${objectAlias}_className", $propClassName))

85

#else

86

#set($whereSql = "${whereSql} and ${objectAlias}.name = doc.fullName and ${objectAlias}.className = ?")

87

#set($discard = $whereParams.add($propClassName))

#end

#end

#end

## Set the order clause for a field. We first ignore the case using the lower function (so that e.g. 'aaa' equals 'AAA')

92

## but then consider it only for equal values (so that e.g. 'AAA' comes before 'aaa').

93

#macro(setOrderClause $fieldName $direction $useRawValue)

94

#if ($useRawValue)

95

#set($orderSql = " order by ${fieldName} ${direction}")

96

#else

97

#set($orderSql = " order by lower(${fieldName}) ${direction}, ${fieldName} ${direction}")

98

#end

99

#end

100

#set($order = "$!request.sort")

101

#if ($order == 'doc.location')

102

#set ($order = 'doc.fullName')

103

#elseif ($order == 'email' && $services.mail.general.shouldObfuscate())

104

#set ($order = '')

105

#end

106

#set ($orderSql = '')

107

#if($order != '')

108

#set($orderDirection = "$!{request.get('dir').toLowerCase()}")

109

#if("$!orderDirection" != '' && "$!orderDirection" != 'asc')

110

#set($orderDirection = 'desc')

111

#end

112

#livetable_getTableAlias($order)

113

#if($order.startsWith('doc.'))

114

## The column is a document field.

115

##

116

## These document fields need to be ordered as raw values and not as strings.

117

#set($rawDocumentFields = ['translation', 'date', 'contentUpdateDate', 'creationDate', 'elements', 'minorEdit1', 'hidden'])

118

#set($documentField = $stringtool.removeStart($order, 'doc.'))

119

#setOrderClause(${safe_tableAlias.replace('_','.')}, ${orderDirection}, $rawDocumentFields.contains($documentField))

120

#else

121

## The column is an object property.

122

##

123

## Resolve the property.

124

#livetable_getPropertyClassAndType($order)

125

#set ($multiselect = "$!{propClass.get($order).getProperty('multiSelect').getValue()}")

126

## We can only handle single values, not multiselect ones.

127

#if ($multiselect != '1')

128

## Some property types do not need lowercasing since they have unique values by design, so we use the raw values to order.

129

#set($rawPropertyTypes = ['NumberClass', 'BooleanClass', 'DateClass', 'LevelsClass'])

130

## If the order column is also a filer column, this means that it was already added to the query and all we need to do is to add it to the order clause.

131

#if(!$tablelist.contains($order))

132

## The order column is not also a filter column, so not yet defined in the query.

133

## We need to first define it (to the from and where clauses) before we can add it to the order clause.

134

##

135

## Resolve the table name of the property to be used in the from clause below.

136

#livetable_getTableName($order)

137

## If the sort column has a _class specified, join that object in

138

#set($orderObjectAlias = 'obj')

139

#if($propClassName != '' && "$!propClass" != '')

140

## prepare the alias of the BaseObject table that corresponds to the class of this column

141

#set($orderObjectAlias = "$!{propClassName.replaceAll('[^a-zA-Z0-9_]', '')}_obj")

142

#addObjectClause($orderObjectAlias)

143

#end

144

#set($fromSql = "${fromSql}, ${tableName} ${safe_tableAlias}")

145

## FIXME: Check if this is indeed a property of the class. Hint: $propType can be used.

146

## Conditions are put on the object coresponding to the column of the order ($orderObjectAlias), which depends on which is the class of the $order

147

#if ($whereParams.entrySet())

148

#set($whereSql = "${whereSql} and ${orderObjectAlias}.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.name = :${safe_tableAlias}_name")

149

#set($discard = $whereParams.put("${safe_tableAlias}_name", "${order}"))

150

#else

151

#set($whereSql = "${whereSql} and ${orderObjectAlias}.id=${safe_tableAlias}.id.id and ${safe_tableAlias}.name = ?")

152

#set($discard = $whereParams.add("${order}"))

153

#end

154

#end

155

## Add the column to the order clause.

156

#setOrderClause("${safe_tableAlias}.value", ${orderDirection}, $rawPropertyTypes.contains($propType))

#end

#end

#end

##

##

## Compute the final queries

163

##

164

#if ($filterParams.entrySet())

165

#set($sqlParams = {})

166

#set($tagsMatchingParams = {})

167

#set($allMatchingParams = {})

168

#else

169

#set($sqlParams = [])

170

#set($tagsMatchingParams = [])

171

#set($allMatchingParams = [])

172

#end

173

#if("$!className" != '')

174

## Class query

175

#if ($sqlParams.entrySet())

176

#set($sql = ", BaseObject as obj $!fromSql $!filterfrom where obj.name=doc.fullName and obj.className = :className and doc.fullName not in (:classTemplate1, :classTemplate2) $!whereSql $!filterwhere")

177

#set($discard = $sqlParams.put('className', "${className}"))

178

#set($discard = $sqlParams.put('classTemplate1', "${className}Template"))

179

#set($discard = $sqlParams.put('classTemplate2', ${className.replaceAll('Class$', 'Template')}))

180

#set($discard = $sqlParams.putAll($whereParams))

181

#else

182

#set($sql = ", BaseObject as obj $!fromSql $!filterfrom where obj.name=doc.fullName and obj.className = ? and doc.fullName not in (?, ?) $!whereSql $!filterwhere")

183

#set($discard = $sqlParams.addAll(["${className}", "${className}Template", ${className.replaceAll('Class$', 'Template')}]))

184

#set($discard = $sqlParams.addAll($whereParams))

185

#end

186

##

187

#set($tagsMatchingFiltersFrom = ", BaseObject as obj $!fromSql $!filterfrom")

188

#if ($tagsMatchingParams.entrySet())

189

#set($tagsMatchingFiltersWhere = "obj.name=doc.fullName and obj.className = :className and doc.fullName not in (:classTemplate1, :classTemplate2) $!whereSql $!filterwhere")

190

#set($discard = $tagsMatchingParams.put('className', "${className}"))

191

#set($discard = $tagsMatchingParams.put('classTemplate1', "${className}Template"))

192

#set($discard = $tagsMatchingParams.put('classTemplate2', ${className.replaceAll('Class$', 'Template')}))

193

#set($discard = $tagsMatchingParams.putAll($whereParams))

194

#else

195

#set($tagsMatchingFiltersWhere = "obj.name=doc.fullName and obj.className = ? and doc.fullName not in (?, ?) $!whereSql $!filterwhere")

196

#set($discard = $tagsMatchingParams.addAll(["${className}", "${className}Template", ${className.replaceAll('Class$', 'Template')}]))

197

#set($discard = $tagsMatchingParams.addAll($whereParams))

198

#end

199

##

200

#set($allMatchingTagsFrom = ", BaseObject as obj $!filterfrom")

201

#if ($allMatchingParams.entrySet())

202

#set($allMatchingTagsWhere = "obj.name=doc.fullName and obj.className = :className and doc.fullName not in (:classTemplate1, :classTemplate2) $!filterwhere")

203

#set($discard = $allMatchingParams.put('className', "${className}"))

204

#set($discard = $allMatchingParams.put('classTemplate1', "${className}Template"))

205

#set($discard = $allMatchingParams.put('classTemplate2', ${className.replaceAll('Class$', 'Template')}))

206

#else

207

#set($allMatchingTagsWhere = "obj.name=doc.fullName and obj.className = ? and doc.fullName not in (?, ?) $!filterwhere")

208

#set($discard = $allMatchingParams.addAll(["${className}", "${className}Template", ${className.replaceAll('Class$', 'Template')}]))

#end

##

#if($filterParams)

#if ($filterParams.entrySet())

213

#set($discard = $sqlParams.putAll($filterParams))

214

#set($discard = $tagsMatchingParams.putAll($filterParams))

215

#set($discard = $allMatchingParams.putAll($filterParams))

216

#else

217

#set($discard = $sqlParams.addAll($filterParams))

218

#set($discard = $tagsMatchingParams.addAll($filterParams))

219

#set($discard = $allMatchingParams.addAll($filterParams))

#end

#end

#else

## Document query

#set($sql = "$!fromSql $!filterfrom where 1=1 $!whereSql $!filterwhere")

225

#if ($whereParams.entrySet())

226

#set($discard = $sqlParams.putAll($whereParams))

227

#else

228

#set($discard = $sqlParams.addAll($whereParams))

229

#end

230

##

231

#set($tagsMatchingFiltersFrom = "$!fromSql $!filterfrom")

232

#set($tagsMatchingFiltersWhere = "1=1 $!whereSql $!filterwhere")

233

#if ($whereParams.entrySet())

234

#set($discard = $tagsMatchingParams.putAll($whereParams))

235

#else

236

#set($discard = $tagsMatchingParams.addAll($whereParams))

237

#end

238

##

239

#set($allMatchingTagsFrom = "$!filterfrom")

240

#set($allMatchingTagsWhere = "1=1 $!filterwhere")

241

##

242

#if($filterParams)

243

#if ($filterParams.entrySet())

244

#set($discard = $sqlParams.putAll($filterParams))

245

#set($discard = $tagsMatchingParams.putAll($filterParams))

246

#set($discard = $allMatchingParams.putAll($filterParams))

247

#else

248

#set($discard = $sqlParams.addAll($filterParams))

249

#set($discard = $tagsMatchingParams.addAll($filterParams))

250

#set($discard = $allMatchingParams.addAll($filterParams))

#end

#end

#end

#if($orderSql != '')

#set($sql = "$sql $!{orderSql}")

#end

#end

#**

* Adds TagCloud information to the JSON returned by a live table data call.

260

* NOTE: This macro uses Velocity variables defined by gridresultwithfilter_buildQuery.

261

*

262

* @param $map stores the JSON in memory so that it can be adjusted before serialization

263

*#

264

#macro(gridresult_buildTagCloudJSON $map)

265

##

266

## TagCloud matching the current filters

267

##

268

#set($tagsMatchingFilters = $xwiki.tag.getTagCountForQuery($tagsMatchingFiltersFrom, $tagsMatchingFiltersWhere, $tagsMatchingParams))

269

## FIXME: We use a map just because the client expects an object, but all we really need is a list..

270

#set($matchingTags = {})

271

#foreach($tag in $tagsMatchingFilters.keySet())

272

## NOTE: The value doesn't have a special meaning. I've used 1 just because it takes less space when serialized.

273

#set($discard = $matchingTags.put($tag, 1))

274

#end

275

#set($discard = $map.put('matchingtags', $matchingTags))

276

##

277

## TagCloud matching all the documents used by the live table

278

##

279

## If all the query parameters are the same as for $tagsMatchingFilters, no need to run the query again.

280

## This optimization allows to divide the time to compute the tagcloud by 2 when the table has no filters applied.

281

#if ($allMatchingTagsFrom.trim() != $tagsMatchingFiltersFrom.trim() || $allMatchingTagsWhere.trim() != $tagsMatchingFiltersWhere.trim() || $tagsMatchingParams != $allMatchingParams)

282

#set($allMatchingTags = $xwiki.tag.getTagCountForQuery($allMatchingTagsFrom, $allMatchingTagsWhere, $allMatchingParams))

283

#else

284

#set($allMatchingTags = $tagsMatchingFilters)

285

#end

286

## FIXME: We use a list of maps just because the client expects an array, but we should simply return $allMatchingTags..

287

#set($tags = [])

288

#foreach($tag in $allMatchingTags.keySet())

289

#set($discard = $tags.add({'tag': $tag, 'count': $allMatchingTags.get($tag)}))

290

#end

291

#set($discard = $map.put('tags', $tags))

#end

#**

* Adds information about each live table row to the JSON returned by a live table data call.

297

* NOTE: This macro uses Velocity variables defined by gridresultwithfilter_buildQuery.

298

*

299

* @param $map stores the JSON in memory so that it can be adjusted before serialization

300

*#

301

#macro(gridresult_buildRowsJSON $map)

302

#set($offset = $numbertool.toNumber($request.get('offset')).intValue())

303

## Offset starts from 0 in velocity and 1 in javascript

304

#set($offset = $offset - 1)

305

#if(!$offset || $offset < 0)

306

#set($offset = 0)

307

#end

308

#set($limit = $numbertool.toNumber($request.get('limit')).intValue())

#if(!$limit)

#set ($limit = 15)

#end

#set($query = $services.query.hql($sql))

313

## Apply query filters if defined. Otherwise use default.

314

#foreach ($queryFilter in $stringtool.split($!request.queryFilters, ', '))

315

#set ($query = $query.addFilter($queryFilter))

316

#end

317

#set ($query = $query.setLimit($limit).setOffset($offset).bindValues($sqlParams))

318

#set($items = $query.execute())

319

#set($discard = $map.put('totalrows', $query.count()))

320

#if ($limit > 0)

321

#set($discard = $map.put('returnedrows', $mathtool.min($items.size(), $limit)))

322

#else

323

## When the limit is 0, it's equivalent to no limit at all and the actual number of returned results can be used.

324

#set($discard = $map.put('returnedrows', $items.size()))

325

#end

326

#set($discard = $map.put('offset', $mathtool.add($offset, 1)))

327

#set($rows = [])

328

#foreach($item in $items)

329

#gridresult_buildRowJSON($item $rows)

330

#end

331

#set ($discard = $map.put('rows', $rows))

332

#livetable_filterObfuscated($map)

#end

#**

* Adds information about the specified live table row to the JSON returned by a live table data call.

338

* NOTE: This macro uses Velocity variables available in gridresult_buildRowsJSON.

339

*

340

* @param $item the name of the document that feeds this live table row

341

* @param $rows stores the JSON in memory so that it can be adjusted before serialization

342

*#

343

#macro(gridresult_buildRowJSON $item $rows)

344

## Handle both the case where the "language" filter is used and thus languages are returned too and the case where

345

## only the document name is returned. When more than the document name is returned the $item variable is a list.

346

#if($item.size())

347

## Extract doc name and doc language from $item

348

#set($docName = $item[0])

349

#set($docLanguage = $item[1])

350

#else

351

#set($docName = $item)

352

#set($docLanguage = '')

353

#end

354

#set ($docReference = $services.model.resolveDocument($docName))

355

#set ($isViewable = $services.security.authorization.hasAccess('view', $docReference))

356

#if ($isViewable)

357

#set ($row = {

358

'doc_viewable': $isViewable,

359

'doc_fullName': $services.model.serialize($docReference, 'local'),

360

'doc_space': $services.model.serialize($docReference.parent, 'local'),

361

'doc_location': "#hierarchy($docReference, {'limit': 5, 'plain': false, 'local': true, 'displayTitle': false})",

362

'doc_url': $xwiki.getURL($docReference),

363

'doc_space_url': $xwiki.getURL($docReference.parent),

364

'doc_wiki': $docReference.wikiReference.name,

365

'doc_wiki_url': $xwiki.getURL($docReference.wikiReference),

366

'doc_hasadmin': $xwiki.hasAdminRights(),

367

'doc_hasedit': $services.security.authorization.hasAccess('edit', $docReference),

368

'doc_hasdelete': $services.security.authorization.hasAccess('delete', $docReference),

369

'doc_edit_url': $xwiki.getURL($docReference, 'edit'),

370

'doc_copy_url': $xwiki.getURL($docReference, 'view', 'xpage=copy'),

371

'doc_delete_url': $xwiki.getURL($docReference, 'delete'),

372

'doc_rename_url': $xwiki.getURL($docReference, 'view', 'xpage=rename&step=1')

373

})

374

#set ($isTranslation = "$!docLanguage" != '' && $xwiki.getLanguagePreference() != $docLanguage)

375

## Display the language after the document name so that not all translated documents have the same name displayed.

376

#set ($row.doc_name = "$docReference.name#if ($isTranslation) ($docLanguage)#end")

377

#set ($row.doc_hascopy = $row.doc_viewable)

378

#set ($row.doc_hasrename = $row.doc_hasdelete)

379

#set ($row.doc_hasrights = $row.doc_hasedit && $isAdvancedUser)

380

#if ($docReference.name == 'WebHome')

381

382

## For nested pages, use the page administration.

383

#set ($webPreferencesReference = $services.model.createDocumentReference(

384

'WebPreferences', $docReference.lastSpaceReference))

385

#set ($row.doc_rights_url = $xwiki.getURL($webPreferencesReference, 'admin',

386

'editor=spaceadmin&section=PageRights'))

387

#else

388

## For terminal pages, use the old rights editor.

389

## TODO: We should create a page administration for terminal pages too.

390

#set ($row.doc_rights_url = $xwiki.getURL($docReference, 'edit', 'editor=rights'))

391

#end

392

#if ($row.doc_viewable)

393

#set ($itemDoc = $xwiki.getDocument($docReference))

394

## Handle translations. We need to make sure we display the data associated to the correct document if the returned

395

## result is a translation.

396

#if ($isTranslation)

397

#set ($translatedDoc = $itemDoc.getTranslatedDocument($docLanguage))

398

#else

399

#set ($translatedDoc = $itemDoc.translatedDocument)

400

#end

401

#set($discard = $itemDoc.use($className))

402

#set($discard = $row.put('doc_objectCount', $itemDoc.getObjectNumbers($className)))

403

#set($discard = $row.put('doc_edit_url', $itemDoc.getURL($itemDoc.defaultEditMode)))

404

#set($discard = $row.put('doc_date', $xwiki.formatDate($translatedDoc.date)))

405

#set($discard = $row.put('doc_title', $translatedDoc.plainTitle))

406

#set($rawTitle = $translatedDoc.title)

407

#if($rawTitle != $row['doc_title'])

408

#set($discard = $row.put('doc_title_raw', $rawTitle))

409

#end

410

#set ($metadataAuthor = $translatedDoc.authors.originalMetadataAuthor)

411

#if ($metadataAuthor == $services.user.getGuestUserReference())

412

## Special handling for guest so that it displays unknown user.

413

#set($discard = $row.put('doc_author', $xwiki.getPlainUserName($NULL)))

414

#else

415

#set($discard = $row.put('doc_author', $xwiki.getPlainUserName($metadataAuthor)))

416

#end

417

418

#set($discard = $row.put('doc_author_url', $xwiki.getURL($metadataAuthor)))

419

#set($discard = $row.put('doc_creationDate', $xwiki.formatDate($translatedDoc.creationDate)))

420

#set($discard = $row.put('doc_creator', $xwiki.getPlainUserName($translatedDoc.creatorReference)))

421

#set($discard = $row.put('doc_hidden', $translatedDoc.isHidden()))

422

#foreach($colname in $collist)

423

#gridresult_buildColumnJSON($colname $row)

#end

#end

#else

#set ($row = {

'doc_viewable': $isViewable,

429

'doc_fullName': 'obfuscated'

430

})

431

#end

432

#set($discard = $rows.add($row))

#end

#**

* Adds information about the given column to the JSON returned by a live table data call.

438

* NOTE: This macro uses Velocity variables available in gridresult_buildRowJSON.

439

*

440

* @param $colname the name of the live table column for which to retrieve information

441

* @param $row stores the JSON in memory so that it can be adjusted before serialization

442

*#

443

#macro(gridresult_buildColumnJSON $colname $row)

444

#if($colname.startsWith('doc.'))

445

#elseif($colname == '_action')

446

#set($discard = $row.put($colname, $services.localization.render("${request.transprefix}actiontext")))

447

#elseif($colname == '_attachments')

448

#livetable_getAttachmentsList($translatedDoc)

449

#set($discard = $row.put($colname, $attachlist))

450

#elseif($colname == '_avatar')

451

#livetable_getAvatar($itemDoc)

452

#set($discard = $row.put($colname, $avatar))

453

#elseif($colname == '_images')

454

#livetable_getImagesList($itemDoc)

455

#set($discard = $row.put($colname, $imagesList))

456

## Output likes if they are available.

457

#elseif($colname == '_likes' && "$!services.like" != "")

458

#set($likes = $services.like.getLikes($docReference))

459

#if ($likes.isPresent())

460

#set($discard = $row.put('_likes', $likes.get()))

461

#end

462

#else

463

#livetable_getPropertyClassAndType($colname)

464

#if(!$propClass.equals($class))

465

#set($discard = $itemDoc.use($propClassName))

466

#end

467

#set($fieldObject = $itemDoc.getFirstObject($colname))

468

#set($fieldProperty = $fieldObject.getProperty($colname))

469

#if ($fieldProperty.getPropertyClass().classType == 'Password')

470

#set($fieldValue = '********')

471

#elseif ($fieldProperty.getPropertyClass().classType == 'Email' && $services.mail.general.shouldObfuscate())

472

#set ($fieldValue = $services.mail.general.obfuscate("$!fieldProperty.getValue()"))

473

#else

474

#set($fieldValue = "$!fieldProperty.getValue()")

475

#end

476

#set($fieldDisplayValue = "#unwrapXPropertyDisplay($itemDoc.display($colname, 'view'))")

477

#if($fieldDisplayValue == '')

478

#set($fieldDisplayValue = $services.localization.render("${request.transprefix}emptyvalue"))

479

#end

480

#set($fieldUrl = '')

481

## Only retrieve an URL for a DBListClass item

482

#if(($propType == 'DBListClass' || $propType == 'PageClass') && $propClass.get($colname).getProperty('multiSelect').value != 1)

483

#set($fieldUrl = $xwiki.getURL($fieldValue))

484

#if($fieldUrl == $xwiki.getURL($services.model.resolveDocument('', 'default', $doc.documentReference.extractReference('WIKI'))))

#set($fieldUrl = '')

#end

#end

#set($discard = $row.put($colname, $fieldDisplayValue))

489

#set($discard = $row.put("${colname}_value", $fieldValue))

490

#set($discard = $row.put("${colname}_url", $fieldUrl))

491

## Reset to the default class

492

#set($discard = $itemDoc.use($className))

#end

#end

#**

* Builds the JSON response to a live table data call.

499

*

500

* @param $map stores the JSON in memory so that it can be adjusted before serialization

501

*#

502

#macro(gridresultwithfilter_buildJSON $className $collist $filterfrom $filterwhere $filterParams $map)

503

#gridresultwithfilter_buildQuery($className $collist $filterfrom $filterwhere $filterParams)

504

#if("$!request.sql" == '1')

505

#set($discard = $map.put('sql', $sql))

506

#set($discard = $map.put('params', $sqlParams))

507

#end

508

#set($discard = $map.put('reqNo', $numbertool.toNumber($request.reqNo).intValue()))

509

#gridresult_buildTagCloudJSON($map)

510

#gridresult_buildRowsJSON($map)

#end

#**

* Builds the JSON response to a live table data call.

516

*

517

* @param $map stores the JSON in memory so that it can be adjusted before serialization

518

*#

519

#macro(gridresult_buildJSON $className $collist $map)

520

#gridresultwithfilter_buildJSON($className $collist '' '' {} $map)

#end

#**

* Macro to get the results of a livetable data call.

526

* This page is called from live grids via Ajax with the argument xpage=plain. It returns a

527

* set of results serialized in JSON.

528

*#

529

#macro(gridresultwithfilter $className $collist $filterfrom $filterwhere $filterParams)

530

#if($xcontext.action == 'get' && "$!{request.outputSyntax}" == 'plain')

531

## Build the JSON in memory (using basic Java data types) so that it can be adjusted before serialization.

532

#set($map = {})

533

#gridresultwithfilter_buildJSON($className $collist $filterfrom $filterwhere $filterParams $map)

#jsonResponse($map)

#end

#end

#**

* Get the name of the Property that should be used for a given livetable column.

541

* NOTE the resulting $tableName is safe to use inside SQL queries

542

*#

543

#macro(livetable_getTableName $colname)

544

#livetable_getPropertyClassAndType($colname)

545

#if($propType == 'NumberClass')

546

#set($numberType = $propClass.get($colname).getProperty('numberType').getValue())

547

#if($numberType == 'integer')

548

#set($tableName = 'IntegerProperty')

549

#elseif($numberType == 'float')

550

#set($tableName = 'FloatProperty')

551

#elseif($numberType == 'double')

552

#set($tableName = 'DoubleProperty')

553

#else

554

#set($tableName = 'LongProperty')

555

#end

556

#elseif($propType == 'BooleanClass')

557

#set($tableName = 'IntegerProperty')

558

#elseif($propType == 'DateClass')

559

#set($tableName = 'DateProperty')

560

#elseif($propType == 'TextAreaClass' || $propType == 'UsersClass' || $propType == 'GroupsClass')

561

#set($tableName = 'LargeStringProperty')

562

#elseif($propType == 'StaticListClass' || $propType == 'DBListClass' || $propType == 'DBTreeListClass' || $propType == 'PageClass')

563

#set($multiSelect = $propClass.get($colname).getProperty('multiSelect').getValue())

564

#set($relationalStorage = $propClass.get($colname).getProperty('relationalStorage').getValue())

565

#if($multiSelect == 1)

566

#if($relationalStorage == 1)

567

#set($tableName = 'DBStringListProperty')

568

#else

569

#set($tableName = 'StringListProperty')

570

#end

571

#else

572

#set($tableName = 'StringProperty')

573

#end

574

#else

575

#set($tableName = 'StringProperty')

#end

#end

#**

* Get the property class and type for a given livetable column.

581

*#

582

#macro(livetable_getPropertyClassAndType $colname)

583

#set($propClassName = "$!request.get(${colname.concat('_class')})")

584

#if($propClassName != '')

585

#set($propClass = $xwiki.getDocument($propClassName).getxWikiClass())

586

#else

587

#set($propClass = $class)

588

#end

589

#set($propType = '')

590

#if($propClass.getPropertyNames().contains($colname))

591

#set($propType = "$!{propClass.get($colname).type}")

#end

#end

#**

* Old alias of the #livetable_getTableName macro.

597

* @deprecated since 2.2.3, use {@link #livetable_getTableName}

598

*#

599

#macro(grid_gettablename $colname)

600

#livetable_getTableName($colname)

#end

#**

* List attachments for a document, putting the result as HTML markup in the $attachlist variable.

607

*#

608

#macro(livetable_getAttachmentsList $itemDoc)

609

#set($attachlist = '')

610

#foreach($attachment in $itemDoc.attachmentList)

611

#set($attachmentUrl = $itemDoc.getAttachmentURL($attachment.filename))

612

#set($attachlist = "${attachlist}<a href='${attachmentUrl}'>$attachment.filename</a><br/>")

#end

#end

#**

* Old alias of the #livetable_getAttachmentsList macro.

618

* @deprecated since 2.2.3, use {@link #livetable_getAttachmentsList}

619

*#

620

#macro(grid_attachlist $itemDoc)

621

#livetable_getAttachmentsList($itemDoc)

#end

#**

* List image attachments for a document, putting the result as HTML markup in the $imagesList variable.

628

*#

629

#macro(livetable_getImagesList $itemDoc)

630

#set($imagesList = '')

631

#foreach ($attachment in $itemDoc.attachmentList)

632

#if($attachment.isImage())

633

## Create a thumbnail by resizing the image on the server side, if needed, to fit inside a 50x50 pixel square.

634

#set($thumbnailURL = $itemDoc.getAttachmentURL($attachment.filename, 'download', "width=50&height=50&keepAspectRatio=true"))

635

#set($imageURL = $itemDoc.getAttachmentURL($attachment.filename))

636

#set($imagesList = "${imagesList}<a href=""$imageURL""><img src=""$thumbnailURL"" alt=""$attachment.filename"" title=""$attachment.filename"" /></a>")

#end

#end

#end

#**

* Old alias of the #livetable_getImagesList macro.

643

* @deprecated since 2.2.3, use {@link #livetable_getImagesList}

644

*#

645

#macro(grid_photolist $itemDoc)

646

#livetable_getImagesList($itemDoc)

#end

#**

* Generate the HTML code for a user avatar.

652

*#

653

#macro(livetable_getAvatar $itemDoc)

654

#set ($avatar = "#mediumUserAvatar($itemDoc.fullName)")

655

#set ($avatar = $avatar.trim())

#end

#**

* Old alias of the #livetable_getAvatar macro.

660

* @deprecated since 2.2.3, use {@link #livetable_getAvatar}

661

*#

662

#macro(grid_avatar $itemDoc)

663

#livetable_getAvatar($itemDoc)

#end

#**

* Macro to extend the query to select the properties for the livetable columns.

670

* NOTE $colName is filtered (all characters but [a-zA-Z0-9_.] are removed) before use

671

*#

672

#macro (livetable_addColumnToQuery $colName)

673

## Safe because / is not allowed in property names

674

## The $joinModeMarker is used in #livetable_filterDBStringListProperty.

675

#set ($joinModeMarker = "/join_mode")

676

#if (!$colName.endsWith($joinModeMarker))

677

#set ($filterValue = "$!request.getParameter($colName)")

678

#if ("$!filterValue" != '')

679

#set ($discard = $tablelist.add($colName))

680

## Some columns may support filtering with multiple constraints (multiple filter values).

681

#set ($filterValues = $request.getParameterValues($colName))

682

#if ($colName.startsWith('doc.'))

683

#if ($colName == 'doc.location')

684

#set ($safeColName = 'doc.fullName')

685

## Use filterLocation since addLivetableLocationFilter is buggy when called several times (it'll add the

686

## same HQL binding name every time it's called! See https://jira.xwiki.org/browse/XWIKI-17463).

687

## Also note that we don't call addLocationFilter since we use a Map for $params.

688

#filterLocation($whereSql, $whereParams, $filterValue, 'locationFilterValue2', true)

689

#elseif ($colName == 'doc.date' || $colName == 'doc.creationDate' || $colName == 'doc.contentUpdateDate')

690

#livetable_getTableAlias($colName)

691

#livetable_filterDateProperty()

692

#else

693

#set ($safeColName = $colName.replaceAll('[^a-zA-Z0-9_.]', '').replace('_', '.'))

694

#if ($whereParams.entrySet())

695

#set ($whereSql = "${whereSql} and upper(str($safeColName)) like upper(:${safeColName.replace('.', '_')}_filter)")

696

#set ($discard = $whereParams.put("${safeColName.replace('.', '_')}_filter", "%$filterValue%"))

697

#else

698

#set ($whereSql = "${whereSql} and upper(str($safeColName)) like upper(?)")

699

#set ($discard = $whereParams.add("%$filterValue%"))

#end

#end

#else

#livetable_filterProperty($colName)

#end

#end

#end

#end

#**

* Determine how the filter values should be matched against the stored values. This macro sets two variables:

712

* <ul>

713

* <li>$matchType: use this when the specified column supports only a single filter value</li>

714

* <li>$matchTypes: use this when the specified column supports multiple filter values.</li>

715

* </ul>

716

*

717

* @param column the column name; each column can have a different match type

718

* @param filterValueCount the number of filter values for which to determine the match type; each filter value can have

719

* a different match type

720

* @param defaultMatchType the default match type to use for the given column when the request doesn't specify one

721

*#

722

#macro (livetable_getMatchTypes $column $filterValueCount $defaultMatchType)

723

#set ($macro.matchTypes = $request.getParameterValues("${column}_match"))

724

#if (!$macro.matchTypes || $macro.matchTypes.isEmpty())

725

## No match type specified for this column.

726

#set ($matchType = $defaultMatchType)

727

#set ($matchTypes = $stringtool.repeat($matchType, ',', $filterValueCount).split(','))

728

#else

729

## At least one match type specified for this column.

730

#set ($matchType = $macro.matchTypes.get(0))

731

#set ($matchTypes = [])

732

#set ($discard = $matchTypes.addAll($macro.matchTypes.subList(0, $mathtool.min($macro.matchTypes.size(),

733

$filterValueCount))))

734

#if ($matchTypes.size() < $filterValueCount)

735

## Add missing match types.

736

#set ($discard = $matchTypes.addAll($stringtool.repeat($matchType, ',', $mathtool.sub($filterValueCount,

737

$matchTypes.size())).split(',')))

#end

#end

#end

#macro (livetable_filterProperty $colname)

744

#livetable_getTableAlias($colname)

745

#livetable_getTableName($colname)

746

#set ($fromSql = "$fromSql, $tableName as $safe_tableAlias")

747

##

748

## If the column is not from $class, we need to make sure we join with the proper table.

749

#set ($filterObjectAlias = 'obj')

750

#set ($propClass = $class)

751

#set ($propClassName = $request.getParameter("${colname}_class"))

752

#if ("$!propClassName" != '')

753

#set ($propClass = $xwiki.getDocument($propClassName).getxWikiClass())

754

#if ("$!propClass" != '')

755

## Prepare the alias of the BaseObject table that corresponds to the class of this column

756

## Property table is to be joined with its object, determined depending on $propClassName.

757

#set ($filterObjectAlias = "$!{propClassName.replaceAll('[^a-zA-Z0-9_]', '')}_obj")

758

#addObjectClause($filterObjectAlias)

759

#end

760

#end

761

#if ($whereParams.entrySet())

762

#set ($joinObjectTable = "${filterObjectAlias}.id = ${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = :${safe_tableAlias}_id_name")

763

#set ($discard = $whereParams.put("${safe_tableAlias}_id_name", $colname))

764

#else

765

#set ($joinObjectTable = "${filterObjectAlias}.id = ${safe_tableAlias}.id.id and ${safe_tableAlias}.id.name = ?")

766

#set ($discard = $whereParams.add($colname))

767

#end

768

#set ($whereSql = "$whereSql and $joinObjectTable")

769

##

770

## We determine the default match type (when not specified) based on the property meta class (e.g. DateClass).

771

#set ($propMetaClass = $NULL)

772

#if ($propClass && $propClass.getPropertyNames().contains($colname))

773

#set ($propMetaClass = $propClass.get($colname).type)

774

#end

775

##

776

#set ($numberProperties = ['IntegerProperty', 'LongProperty', 'FloatProperty', 'DoubleProperty'])

777

#if ($numberProperties.contains($tableName))

778

#livetable_filterNumberProperty()

779

#elseif ($tableName == 'DateProperty')

780

#livetable_filterDateProperty()

781

#elseif ($tableName == 'DBStringListProperty')

782

#livetable_filterDBStringListProperty()

783

#elseif ($tableName == 'StringListProperty')

784

#livetable_filterStringListProperty()

785

#else

786

## StringProperty or LargeStringProperty

787

#livetable_filterStringProperty()

#end

#end

#**

* NOTE: This macro uses variables defined in livetable_filterProperty . It was not meant to be used alone.

794

*#

795

#macro (livetable_filterNumberProperty)

796

#set($numberValue = $numbertool.toNumber($filterValue))

797

#if($tableName == 'IntegerProperty' || $tableName == 'LongProperty')

798

#if($tableName == 'LongProperty')

799

#set($numberValue = $numberValue.longValue())

800

#else

801

## IntegerProperty

802

#set($numberValue = $numberValue.intValue())

803

#end

804

#if ($whereParams.entrySet())

805

#set($whereSql = "${whereSql} and ${safe_tableAlias}.value = :${safe_tableAlias}_value")

806

#set($discard = $whereParams.put("${safe_tableAlias}_value", $numberValue))

807

#else

808

#set($whereSql = "${whereSql} and ${safe_tableAlias}.value = ?")

809

#set($discard = $whereParams.add($numberValue))

810

#end

811

#else

812

#if($tableName == 'FloatProperty')

813

#set($numberValue = $numberValue.floatValue())

814

#else

815

## DoubleProperty

816

#set($numberValue = $numberValue.doubleValue())

817

#end

818

#set($precision = 0.000001)

819

#if ($whereParams.entrySet())

820

#set($whereSql = "${whereSql} and abs(:${safe_tableAlias}_value - ${safe_tableAlias}.value) <= ${precision}")

821

#set($discard = $whereParams.put("${safe_tableAlias}_value", $numberValue))

822

#else

823

#set($whereSql = "${whereSql} and abs(? - ${safe_tableAlias}.value) <= ${precision}")

824

#set($discard = $whereParams.add($numberValue))

#end

#end

#end

#**

* NOTE: This macro uses variables defined in livetable_filterProperty . It was not meant to be used alone.

832

*#

833

#macro (livetable_filterDateProperty)

834

#if ($safe_tableAlias.startsWith('doc.'))

835

#set ($dateProperty = $safe_tableAlias)

836

#else

837

#set ($dateProperty = "${safe_tableAlias}.value")

838

#end

839

#set ($safeDateProperty = $dateProperty.replace('.', '_'))

840

#set ($dateRange = {})

841

## Perform partial string matching by default if no match type is specified.

842

## Note that for the moment we support only one filter value (e.g. one date range) and thus only the first match type

843

## is taken into account.

844

#livetable_getMatchTypes($colname $filterValues.size() 'partial')

845

#parseDateRange($matchType $filterValue $dateRange)

846

#if ($dateRange.start || $dateRange.end)

847

## Date range.

848

#if ($dateRange.start)

849

#if ($whereParams.entrySet())

850

#set ($whereSql = "${whereSql} and $dateProperty >= :${safeDateProperty}1")

851

#set ($discard = $whereParams.put("${safeDateProperty}1", $dateRange.start))

852

#else

853

#set ($whereSql = "${whereSql} and $dateProperty >= ?")

854

#set ($discard = $whereParams.add($dateRange.start))

#end

#end

#if ($dateRange.end)

#if ($whereParams.entrySet())

859

#set ($whereSql = "${whereSql} and $dateProperty <= :${safeDateProperty}2")

860

#set ($discard = $whereParams.put("${safeDateProperty}2", $dateRange.end))

861

#else

862

#set ($whereSql = "${whereSql} and $dateProperty <= ?")

863

#set ($discard = $whereParams.add($dateRange.end))

#end

#end

#else

## String matching (contains).

868

#if ($whereParams.entrySet())

869

#set ($whereSql = "${whereSql} and upper(str($dateProperty)) like upper(:$safeDateProperty)")

870

#set ($discard = $whereParams.put($safeDateProperty, "%$filterValue%"))

871

#else

872

#set ($whereSql = "${whereSql} and upper(str($dateProperty)) like upper(?)")

873

#set ($discard = $whereParams.add("%$filterValue%"))

#end

#end

#end

#**

* NOTE: This macro uses variables defined in livetable_filterProperty . It was not meant to be used alone.

881

*#

882

#macro (livetable_filterDBStringListProperty)

883

## Perform exact matching by default if no match type is specified.

884

## Note that for DBStringList properties we take into account only the first match type, even if multiple filter

885

## values are specified. Basically the first match type is used for all filter values.

886

#livetable_getMatchTypes($colname $filterValues.size() 'exact')

887

#if ($matchType == 'partial' || $matchType == 'prefix')

888

## We need to join with the list of values in order to be able to use the LIKE operator.

889

#set ($matchTarget = "${safe_tableAlias}_item")

890

#if ($whereParams.entrySet())

891

#set ($paramPrefix = "${safe_tableAlias}_item_")

892

#else

893

#set ($paramPrefix = $NULL)

894

#end

895

#set ($joinPos = $mathtool.add($fromSql.lastIndexOf(" $safe_tableAlias"), $mathtool.add($safe_tableAlias.length(), 1)))

896

#set ($fromSql = "$fromSql.substring(0, $joinPos) join ${safe_tableAlias}.list as $matchTarget $fromSql.substring($joinPos)")

897

#else

898

## Fall-back on exact matching even if the match type is specified, when its value is not supported.

899

#set ($matchType = 'exact')

900

#set ($matchTarget = "${safe_tableAlias}.list")

901

#if ($whereParams.entrySet())

902

#set ($paramPrefix = "${safe_tableAlias}_list_")

903

#else

904

#set ($paramPrefix = $NULL)

905

#end

906

#end

907

#set ($filterQuery = "#livetable_getFilterQuery($matchTarget $matchType true $filterValues.size() $paramPrefix $NULL)")

908

#set ($whereSql = "$whereSql and ($filterQuery.trim())")

909

#foreach ($filterValue in $filterValues)

910

#livetable_addFilterParam($filterValue $matchType $whereParams "${paramPrefix}${foreach.count}")

#end

#end

#**

* NOTE: This macro uses variables defined in livetable_filterProperty . It was not meant to be used alone.

917

*#

918

#macro (livetable_filterStringListProperty)

919

## From the user point of view we support only exact matching for StringList properties, due to the way the values of

920

## these properties are stored (concatenated). But when building the actual query, the match type is in fact partial

921

## because we have to use the like operator in order to match the concatenated list of values.

922

#livetable_getMatchTypes($colname $filterValues.size() 'exact')

923

#set ($matchTarget = "concat('|', concat(${safe_tableAlias}.textValue, '|'))")

924

#if ($whereParams.entrySet())

925

#set ($paramPrefix = "${safe_tableAlias}_textValue_")

926

#else

927

#set ($paramPrefix = $NULL)

928

#end

929

## As noted above, we have to use the like operator because the list of values is saved concatenated, so from the

930

## point of view of the query the match type is always partial.

931

#set ($filterQuery = "#livetable_getFilterQuery($matchTarget 'partial' false $filterValues.size() $paramPrefix $NULL)")

932

#set ($whereSql = "${whereSql} and ($filterQuery.trim())")

933

#foreach ($filterValue in $filterValues)

934

#if ($matchTypes.get($foreach.index) == 'empty')

935

## The client side cannot pass an empty filter value so it specifies that the value is empty using the match type.

936

#set ($filterValue = '')

937

#end

938

## As noted above, we can only perform exact matching due to the way the values are stored (concatenated).

939

#livetable_addFilterParam("%|$filterValue|%" 'exact' $whereParams "${paramPrefix}${foreach.count}")

#end

#end

#**

* NOTE: This macro uses variables defined in livetable_filterProperty . It was not meant to be used alone.

946

*#

947

#macro (livetable_filterStringProperty)

948

#if ($propMetaClass.endsWith('ListClass'))

949

## Perform exact matching by default for StaticListClass, DBListClass and DBTreeListClass

950

## when they are stored as StringProperty (i.e. single value and no relational storage).

951

#set ($defaultStringMatchType = 'exact')

952

#else

953

## Perform partial matching by default otherwise.

954

#set ($defaultStringMatchType = 'partial')

955

#end

956

#livetable_getMatchTypes($colname $filterValues.size() $defaultStringMatchType)

957

## Group the filter values by match type so that we cann optimize the query.

958

#livetable_groupFilterValuesByMatchType($matchTypes $filterValues)

959

#if ($whereParams.entrySet())

960

#set ($paramPrefix = "${safe_tableAlias}_value_")

961

#else

962

#set ($paramPrefix = $NULL)

963

#end

964

## Note that unlike other property types, the String property supports different match types for different filter

965

## values. This means we have to call livetable_getFilterQuery for each filter value and then join the constraints

966

## ourselves.

967

#set ($constraints = [])

968

#set ($paramOffset = 1)

969

#foreach ($entry in $filterValuesByMatchType.entrySet())

970

#set ($matchType = $entry.key)

971

#set ($filterValues = $entry.value)

972

#set ($constraint = "#livetable_getFilterQuery(""${safe_tableAlias}.value"" $matchType false $filterValues.size() $paramPrefix $paramOffset)")

973

#set ($discard = $constraints.add($constraint.trim()))

974

#foreach ($filterValue in $filterValues)

975

#livetable_addFilterParam($filterValue $matchType $whereParams

976

"${paramPrefix}${mathtool.add($paramOffset, $foreach.index)}")

977

#end

978

#set ($paramOffset = $paramOffset + $filterValues.size())

979

#end

980

#set ($whereSql = "${whereSql} and ($stringtool.join($constraints, "" $joinOperator ""))")

981

#end

982

983

#macro (livetable_groupFilterValuesByMatchType $matchTypes $filterValues)

984

#set ($filterValuesByMatchType = {})

985

#foreach ($matchType in $matchTypes)

986

#set ($discard = $filterValuesByMatchType.putIfAbsent($matchType, []))

987

#set ($discard = $filterValuesByMatchType.get($matchType).add($filterValues.get($foreach.index)))

#end

#end

#macro (livetable_getJoinOperator $colName)

992

#set ($joinOperator = "$!{request.get(""${colName}${joinModeMarker}"").toUpperCase()}")

993

#if ($joinOperator != 'AND' && $joinOperator != 'OR')

994

#set ($joinOperator = 'AND')

#end

#end

#macro (livetable_getFilterQuery $column $matchType $isList $valueCount $paramPrefix $paramOffset)

999

#livetable_getJoinOperator($colname)

1000

#if (!$paramOffset)

1001

#set ($paramOffset = 1)

1002

#end

1003

#if ($matchType == 'partial' || $matchType == 'prefix')

1004

#livetable_repeatParams("upper($column) like upper(?)", " $joinOperator ", $valueCount, $paramPrefix, $paramOffset)

1005

#elseif($matchType == 'empty')

1006

## Check if the value of the column is like the empty parameter (which is often the empty string), or if the value

1007

## of the column is null (to be compliant with Oracle which stores the empty string as a NULL value).

1008

#livetable_repeatParams("($column like ? or $column is null)", " $joinOperator ", $valueCount, $paramPrefix,

1009

$paramOffset)

1010

#elseif ($isList)

1011

#livetable_repeatParams("? in elements($column)", " $joinOperator ", $valueCount, $paramPrefix, $paramOffset)

1012

#elseif ($valueCount > 1 && $joinOperator == 'OR')

1013

$column in (#livetable_repeatParams('?', ', ', $valueCount, $paramPrefix, $paramOffset))

1014

#else

1015

#livetable_repeatParams("$column = ?", ' AND ', $valueCount, $paramPrefix, $paramOffset)

#end

#end

#macro (livetable_repeatParams $str $separator $valueCount $paramPrefix $paramOffset)

1020

#if ($valueCount > 0)

1021

#foreach ($count in [1..$valueCount])

#if ($count > 1)

$separator##

#end

#if ($paramPrefix)

$str.replace('?', ":${paramPrefix}${mathtool.add($paramOffset, $foreach.index)}")##

#else

$str##

#end

#end

#end

#end

#macro (livetable_addFilterParam $filterValue $matchType $params $paramName)

1035

#if ($matchType == 'partial')

1036

#if ($params.entrySet())

1037

#set ($discard = $params.put($paramName, "%$!filterValue%"))

1038

#else

1039

#set ($discard = $params.add("%$!filterValue%"))

1040

#end

1041

#elseif ($matchType == 'prefix')

1042

#if ($params.entrySet())

1043

#set ($discard = $params.put($paramName, "$!filterValue%"))

1044

#else

1045

#set ($discard = $params.add("$!filterValue%"))

1046

#end

1047

#elseif ($matchType == 'empty')

1048

#if ($params.entrySet())

1049

#set ($discard = $params.put($paramName, ''))

1050

#else

1051

#set ($discard = $params.add(''))

1052

#end

1053

#else

1054

#if ($params.entrySet())

1055

#set ($discard = $params.put($paramName, $filterValue))

1056

#else

1057

#set ($discard = $params.add($filterValue))

#end

#end

#end

#**

* Old alias of the #livetable_addColumnToQuery macro.

1065

* @deprecated since 2.2.3, use {@link #livetable_addColumnToQuery}

1066

*#

1067

#macro(grid_addcolumn $colname)

1068

#livetable_addColumnToQuery($colname)

#end

#**

* Generates a valid SQL table alias for the specified live table column.

1073

*#

1074

#macro (livetable_getTableAlias $columnName)

1075

#set ($prefix = 'doc.')

1076

#if ($columnName.startsWith($prefix))

1077

#set ($suffix = $stringtool.removeStart($columnName, $prefix))

1078

#else

1079

## Force a prefix to avoid the cases when the column name is a reserved SQL keyword.

1080

#set ($prefix = 'prop_')

1081

#set ($suffix = $columnName)

1082

#end

1083

## Remove non-word characters.

1084

#set ($safe_tableAlias = "$prefix$suffix.replaceAll('\W', '')")

1085

#end

1086

Wiki source code of Livetable Results Macros