Script - Editable Paging Tables

{run:id=page|autoRun=true|hideColumnHeadings=true|replace=
 g1::Edit:group,
 limit:10:Size:select::5::10::25::50::75::100::200::300::400::500,
 direction:refresh:Select:select::forward::backward::begin::end::refresh,
 offset:0::hidden
 |titleRun=Page}

{groovy:output=wiki|glimit=$limit|goffset=$offset|gdirection=$direction|goffsetoveride=$overideoffset}

import com.atlassian.renderer.v2.RenderMode

def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
SESSION_OFFSET_ATTRIBUTE='hga_pageoffsetvalue'

value=session.getAttribute(SESSION_OFFSET_ATTRIBUTE)

//***UPDATE SQL: nested in between quotes
def countMacro = "{sql:id=countsql|datasource=test|table=false}select count(*) from confluence_editable_table;{sql}"

def count = subRenderer.render(countMacro, context, renderMode)

glimit=glimit.toInteger()

  if (value==null) {
    value=goffset
  } else {
    c=count.toInteger()
    value=value.toInteger()

    if (gdirection=='forward') {
      if (c>(value+glimit))
        value=value+glimit
    } else if (gdirection=='backward') {
      if ((value-glimit)<0)
        value=0
      else
        value=value-glimit
    } else if (gdirection=='begin') {
        value=0
    } else if (gdirection=='end') {
        if (glimit<c)
          value=c-glimit
        else
          value=0
    }
  }

session.setAttribute(SESSION_OFFSET_ATTRIBUTE, value)

//***UPDATE SQL: nested in between quotes
def selectResultsMacro = """
{sql-query:dataSource=test|output=wiki}

 select id, name, value,

 '{table:border=0|cellpadding=0|cellspacing=0}{table-row}{table-cell}'
 || '{run1:id=e' || id || '|buttonPadding=0'
 || '|keepRequestParameters=true|anchor=edit|titleRun=Edit}'
 || '{extract-link:redirect=true}'
 || '$page_url'
 || '?run_selection=run&run_edit_name={url-encode}' || name || '{url-encode}'
 || '&run_edit_value={url-encode}' || value || '{url-encode}'
 || '&run_edit_id=' || id
 || '&run_selection_hideEdit=false'
 || '#\$page_anchor_e' || id
 || '{extract-link}'
 || '{run1}'
 || '{table-cell} {table-cell}'
 || '{run1:id=d' || id
 || '|buttonPadding=0'
 || '|titleRun=Delete}'
 || '{sql:dataSource=test}'
 || 'delete from confluence_editable_table where id = ' || id
 || '{sql}'
 || '{extract-link:redirect=true}'
 || '$page_url?run_page_direction=refresh'
 || '{extract-link}'
 || '{run1}'
 || '{table-cell}{table-row}{table}' as "Actions"

 from confluence_editable_table order by id limit $limit offset $value

{sql-query}
"""
println selectResultsMacro
println "Page command = '$gdirection', limit=$glimit, offset=$value, rows=$count"

{groovy}

{run}

h3. Insert
{run:id=recinsert|titleRun=Insert|autoRun=false|hideColumnHeadings=true|keepRequestIds=false|replace=
 g1::Insert:group,
 id::id,
 name::name,
 value::value}
 {groovy:output=wiki|gid=$id|gname=$name|gvalue=$value}
    //***UPDATE SQL: nested in between quotes
    def insertMacro = """
        {sql:id=insertsql|datasource=test|p1=$gid|p2=$gname|p3=$gvalue}
         insert into confluence_editable_table(id, name, value)
         values(cast(? as integer), ?, ?)
        {sql}
    """

  println insertMacro
 {groovy}
 {extract-link:redirect=true} $page_url?run_page_direction=refresh{extract-link}
{run}

{anchor:edit}
h3. Edit
{run:id=edit|hideColumnHeadings=true|replace=
 g1::Edit:group,
 id::id:hidden,
 name::name,
 value::value
 |titleRun=Update}
{groovy:output=wiki|gid=$id|gname=$name|gvalue=$value}
 //***UPDATE SQL: nested in between quotes
 def updateMacro = """
   {sql:dataSource=test|p1=$gname|p2=$gvalue|p3=$gid}
     update confluence_editable_table
     set name = ?, value = ?
     where id = cast(? as integer)
   {sql}
 """
 println updateMacro
{groovy}
{extract-link:redirect=true} $page_url?run_page_direction=refresh{extract-link}
{run}