Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
groovity.sqlTable.grvt Maven / Gradle / Ivy
/*******************************************************************************
* © 2018 Disney | ABC Television Group
*
* Licensed under the Apache License, Version 2.0 (the "Apache License")
* with the following modification; you may not use this file except in
* compliance with the Apache License and the following modification to it:
* Section 6. Trademarks. is deleted and replaced with:
*
* 6. Trademarks. This License does not grant permission to use the trade
* names, trademarks, service marks, or product names of the Licensor
* and its affiliates, except as required to comply with Section 4(c) of
* the License and to reproduce the content of the NOTICE file.
*
* You may obtain a copy of the Apache License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the Apache License with the above modification is
* distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the Apache License for the specific
* language governing permissions and limitations under the Apache License.
*******************************************************************************/
import groovy.sql.Sql
@Tag(
info = "Create a paginated HTML table from a SQL table or view",
attrs = [
@Attr(
name = "sql",
info = "The sql instance or name to use to connect with the database",
required = true
),
@Attr(
name = "table",
info = "The name of the database table or view to visualize",
required = true
),
@Attr(
name = "id",
info = "A unique identifier for this table to isolate interactions",
required = true
),
@Attr(
name = "cols",
info = "The map of database column names to labels to visualize",
required = true
),
@Attr(
name = "title",
info = "The title to apply to the table",
required = true
),
@Attr(
name = "limit",
info = "The number of rows to display at one time, defaults to 10 or binding variable 'limit'",
required = false
),
@Attr(
name = "page",
info = "The 1-based page number to return, defaults to 1 or binding variable 'page'",
required = false
),
@Attr(
name = "sortCol",
info = "The name of the column to sort on, defaults to id or binding variable 'sortCol'",
required = false
),
@Attr(
name = "sortOrder",
info = "The direction to sort, ASC or DESC, defaults to ASC or binding variable 'sortOrder'",
required = false
),
@Attr(
name = "filter",
info = "A simple pattern to search for in the data, defaults to none or binding variable 'filter'",
required = false
),
@Attr(
name = "selection",
info = "A closure to be used to identify whether a row is selected",
required = false
),
@Attr(
name = "cellFormat",
info = "A closure that can be passed in to completely control the formatting of cell contents, takes two arguments 'row' and 'col'",
required = false
),
@Attr(
name = "cellLabel",
info = "A closure that can be passed in to generate value labels for the default cellFormat",
required = false
),
@Attr(
name = "where",
info = "A map of column names to values used to restrict the dataset for this table",
required = false
),
@Attr(
name = "deleteTable",
info = "a table name to allow interactive deletes on the data",
required = false
)
]
)
class SqlTable{
def tag(Map atts, Closure body){
def sql = resolve(atts,'sql');
if(sql==null){
throw new RuntimeException("sqlTable requires a 'sql' attribute");
}
if(!(sql instanceof Sql)){
sql = load('/groovity/sql').sql(sql.toString())
}
def table = resolve(atts,'table');
if(table==null){
throw new RuntimeException("sqlTable requires a 'table' attribute");
}
def id = resolve(atts,'id');
if(id==null){
throw new RuntimeException("sqlTable requires an 'id' attribute");
}
def queryString = request.queryString ?: '';
def cols = resolve(atts,'cols');
def title = resolve(atts,'title');
def p = {
name ->
def val = request.getParameter(name);
if(val){
return val;
}
return body.binding.variables[name];
}
def limit = 10;
if(p("${id}_limit")){
limit = p("${id}_limit") as Integer;
}
else if(atts.limit!=null){
limit = resolve(atts,'limit',Integer.class)
}
def page = 1;
if(p("${id}_page")){
page = p("${id}_page") as Integer;
}
else if(atts.page!=null){
page = resolve(atts,'page',Integer.class)
}
def sortCol = null;
if(p("${id}_sortCol")){
sortCol = p("${id}_sortCol");
}
if(sortCol==null){
sortCol = resolve(atts,'sortCol');
}
def sortOrder = null;
if(p("${id}_sortOrder")){
sortOrder = p("${id}_sortOrder");
}
if(sortOrder==null){
sortOrder = resolve(atts,'sortOrder');
}
if(sortOrder==null || sortOrder!='DESC'){
sortOrder = "ASC";
}
def deleteTable = resolve(atts,'deleteTable');
def selection = atts.selection;
if(!selection){
selection = {p("${id}_id") && it.id==p("${id}_id") as Long};
}
def urlBase = "${request.requestURI}?${queryString}#${id}_sqlPager";
def cellLabel = resolve(atts,'cellLabel');
if(!cellLabel){
cellLabel = { it }
}
def cellFormat = resolve(atts,'cellFormat');
if(!cellFormat){
cellFormat = { row, col ->
def rowId=''
if(row.containsKey('id') && row.id){
rowId = row.id
}
def href = uri(base:urlBase,{ param(name:"${id}_id",value:rowId,replace:true) });
<~ ${cellLabel(row[col])} ~>
}
}
def filter = null;
if(p("${id}_filter")){
filter = p("${id}_filter");
}
if(filter==null){
filter = resolve(atts,'filter');
}
def where = resolve(atts,'where');
def filterBuilder = new StringBuilder();
def sqlArgs = [];
if(filter!=null && cols!=null){
def lf = "%${filter}%".toString();
filterBuilder << " WHERE ("
cols.eachWithIndex{ col, idx ->
if(idx>0){
filterBuilder << " OR "
}
filterBuilder << " ( ${table}.${col.key} LIKE ? ) "
sqlArgs << lf
}
filterBuilder << " )"
}
if(where){
if(filterBuilder.length() == 0){
filterBuilder << " WHERE "
}
else{
filterBuilder << " AND "
}
where.eachWithIndex{ entry, idx ->
if(idx > 0){
filterBuilder << " AND "
}
filterBuilder << " ( ${table}.${entry.key} = ? ) "
sqlArgs << entry.value;
}
}
if(deleteTable && p("${id}_delete")){
//to protect against back doors we should validate that the data to be deleted
//is actually a part of the results for this query
def myArgs = [p("${id}_delete") as Long];
def testArgs = []
testArgs.addAll(myArgs)
testArgs.addAll(sqlArgs);
def isAllowed = null
if(deleteTable==table){
isAllowed = sql.firstRow("SELECT 0 FROM ${deleteTable} WHERE ${deleteTable}.id = ? ${filterBuilder.toString().replace('WHERE','AND')}",testArgs);
}
else{
isAllowed = sql.firstRow("SELECT 0 FROM ${deleteTable} WHERE ${deleteTable}.id = ? AND EXISTS (SELECT 0 FROM ${table} ${filterBuilder})",testArgs);
}
if(isAllowed){
log(info:"DELETING ${myArgs} from ${deleteTable}")
sql.executeUpdate("DELETE FROM ${deleteTable} WHERE ${deleteTable}.id = ?",myArgs);
}
else{
log(warn:"WILL NOT DELETE ${myArgs} from ${deleteTable}")
}
}
def totalRows = sql.firstRow("SELECT COUNT(*) AS cnt FROM ${table} ${filterBuilder}".toString(),sqlArgs)?.cnt;
def maxPage = (int) Math.ceil(totalRows/(float)limit);
def select = cols!=null?cols.keySet().join(", "):"*";
def order = sortCol!=null?"ORDER BY ${sortCol} ${sortOrder}":"";
int startRow = ((page-1)*limit)+1;
def sqlStmt = "SELECT ${select} FROM ${table} ${filterBuilder} ${order} ";
def myRows = sql.rows(sqlStmt.toString(),sqlArgs,startRow,limit);
def backUrl= page > 1 ? uri(base:urlBase,{param(name:"${id}_page",value:(page-1) as String, replace:true)}) : null;
def nextUrl= page < maxPage ? uri(base:urlBase,{param(name:"${id}_page",value:(page+1) as String, replace:true)}) : null;
def filterUrl = uri(base:urlBase,{
param(name:"${id}_page",value:"1",replace:true)
param(name:"${id}_id",value:"",replace:true)
});
def deleteUrl = uri(base:urlBase,{
param(name:"${id}_delete",value:"DELETE_VALUE",replace:true)
});
def numCols = cols.collect{ it.value }.size();
if(deleteTable){
numCols++;
}
write{<~
${title}
~>}
}
}
