Editable reports

Commands

dbr.editable - Prepares a column so it is in-place editable
dbr.upload - Upload a file to the server's filesystem
dbr.upload.options - Set an upload option
dbr.selectable - Convert an integer ID column to a selectable row to be used with a report linked button

Syntax

select 'dbr.editable', ColumnReference, procedure_name, [,parameter_name=paramref] [,type=input_type] [,select=query] [,options={'jeditable_options'}]
select 'dbr.upload', path_to_directory, upload_sp [, parameters]
select 'dbr.upload.options', option, value
select 'dbr.selectable', ColumnReference

Where:

ColumnReference
The column which will be made editable
procedure_name
Editing report's procedure. Permissions to this report determine if the user is allowed to edit the column
parameter_name
Refers to a editing stored procedure's / URL's parameter name to which we want to pass value to
ColumnReference/ParameterReference
A column value or a parameter value to be passed on to the editing report as a value for parameter_name.
type
Allows you to choose from special input types: 'textarea', 'autosize' (autosizing textarea), 'select', 'select_find', 'radiobutton', 'checkbox', 'datepicker', 'datetimepicker', 'timepicker', 'colorpicker', 'richtext', 'file_rename' and code editing specific types 'html', 'php', 'javascript', 'css' and 'sql'. If no input type is specified, a normal input-field is displayed. Type 'select_find' is a selectlist with search functionality. For code editing specific types, enable the 'Code editing' report extension so the editor component will be included into the report.
select
Defines the selection query used when the type is select or radiobutton. The query should return the ID and the user-visible value. The query can be a direct select query or a stored procedure call. The stored procedure call can use automatic parameters and its parameters may refer to the result set columns and report parameters. If you use 'type=select' or 'type=select_find', you can use opgroups by specifying the group as thrid column in the select query.
checkbox_id
When using 'checkbox' type, defines the ColumnReference for the checkbox item IDs.
options
myDBR uses Jeditable to make cells editable and its parameters can be passed on with options
additional options
For 'type=radiobutton' you can use 'autosubmit':1 (does not display OK and Cancel buttons) and 'display':'inline' which will place items side-by-side
path_to_directory
Full path to server directory where the uploaded files will be stored
upload_sp
The procedure which will be called once the upload is done
upload option
To configure the upload functionality
  • titleTitle shown in upload
  • noreplacePrevent overwriting files (value = 1)
  • accepted_filesA comma-separated list of accepted file extensions
  • maxfilesizemaximum size of the uploaded file in megabytes

Explanation

A report column can be marked as editable (in-place editing) with the dbr.editable-command. The command takes column number and parameter references as parameters. When the user clicks on the editable column, an in-place editor is shown and the edited value with specified parameters are sent to the procedure that was given as a parameter. The procedure can perform the update or reject the update and return the original value. If the editing procedure returns the value (single row, single column), myDBR will format the cell using the defined cell formatting.

Preventing editing certain cells

Sometimes not all rows in a column or columns in crosstab should be edited. You can prevent editing on selected cells by adding show_link-parameter to the dbr.editable command. You can also use 'no_edit'-class to the cell by using the dbr.cellclass-command.

(If you wish to edit more than one column at a time see Creating editable reports for more info.)

Using options

With options you can:

  • Change the type of the editable field to something other than a basic input field. Currently textareas are supported.
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit_comment', 'inID=is', 'inQuarter=Q', 'type:textarea', "options={'rows':6,'cols':20}";
  • Define the size of the editable field
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20}";
  • Provide a callback javascript method to be called after the editing is done. myDBR provides two ready-made callback methods: autosum_int (integers) and autosum_float (decimal numbers) which will refresh the summary column for the edited column.

    Usage:
    select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20, 'callback':autosum_int}";

    myDBR also offers callback helper functions which allow you to make updates to other columns based on the user editable cell. Following list of javascript-functions are available:
    • col_value_get( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
      get a value for cell. With no decimals defined deals with integers. The column can be the column index or a class.
    • col_value_set( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
      set a value for cell. With no decimals defined deals with integers. The column can be the column index or a class.
    • ct_total( obj[, NULL [, decimals [, value [, prefix [, suffix]]]]] )
      Calculate horizontal aggregate from first parameter's cell. The first parameter should point to first data column.
    • col_value_text(obj [, column[, value]])
      get/set textual column value. The column can be the column index or a class.
    • col_sum( obj [, column [, decimals[, prefix[, suffix]]]] )
      Returns the sum of the column. column can be column index or a class. The column can be the column index or a class.
    • autosum_int( obj, [colindex [, prefix[, suffix]]] )
      Updates the sum of the column. If no column is defined the user editable column is updated. The column can be the column index or a class.
    • autosum_float( obj, [colindex [, decimals [, prefix[ , suffix]]]] )
      Updates the sum of the column as a decimal number. If no column is defined the user editable column is updated. The column can be the column index or a class.
    obj is reference to the edited cell (javascript 'this').

Basic example

In the example, we'll define the in-place editing to be available in a following cross-table report. We'll use the dbr.editable command to mark column 4 (Budget) to be updateable with the procedure sp_DBR_update_crosstable.

Parameters for the command in the example are:

  • id
    The column to be edited
  • sp_DBR_update_crosstable
    The myDBR report the edited value is passed to
  • inCategory=cat
    sp_DBR_update_crosstable's inCategory-parameter is passed the value for selected 'cat' column value (Category)
  • inWeek=Week
    sp_DBR_update_crosstable's inWeek-parameter is passed the value for selected 'Week' column value (payment_week)

CREATE PROCEDURE `sp_DBR_InPlaceEditing
BEGIN

select 'dbr.editable', 'Budget', 'sp_DBR_update_crosstable', 'inCategory=cat', 'inWeek=week';

select 'dbr.crosstab', 'Week';
select 'dbr.count', 'cat';
select 'dbr.sum', 'Total';
select 'dbr.hsum', 'Total';
select 'dbr.hidecolumns', 1;

select  category as 'Film category[cat]', 
  payment_week 'Week', 
  amount as 'Total', 
  budget as 'Budget', 
  category_id
from film_budget;

END

The report that is doing the actual updating can either reject the update and return the original value or simply update the new value. The report gets all parameters specified in the dbr.editable-call and an additional parameter for the value.

The last parameter for the editing procedure is automatically populated by the value of the edited field. You can name the parameter as you like (inValue in the example).

CREATE PROCEDURE `sp_DBR_update_crosstable`(
inCategory int,
inWeek int,
inValue float
)
BEGIN

/* Check the data */
if (inValue>0) then
  /* Accept the update, no need to return a value */
  update film_budget
  set budget = inValue
  where category_id=inCategory and payment_week=inWeek;
else 
  /* Reject the update, return the original value */
  select budget
  from film_budget
  where category_id=inCategory and payment_week=inWeek;
end if;

END

In the example, we'll define the in-place editing to be available in a following cross-table report (Budget column under weeks).

Examples of input types

We'll use different input types for the following data: a plain input, a textarea (autosizing), a select list, radio buttons, a datepicker, and a colorpicker. Dates are shown as is from the database.

For the radio button and selectlist the textual representation shown to the user will be fetched. myDBR will pass the ID to the editing report.

CREATE PROCEDURE `sp_DBR_update_inputtype`()
BEGIN
select 'dbr.pageview';

select 'dbr.javascript', '
function color_callback(settings, cell)
{
/* 
  Chosen (format #626262) value is in 'this'
  colorpicker.convert(this) will convert the color value into visible color
*/
colorpicker.convert(this);
}';

select 'dbr.editable', 'Basic', 'sp_DBR_input_edit', 'inId=id';
select 'dbr.editable', 'autosize', 'sp_DBR_text_edit', 'inId=id', 'type=autosize';
select 'dbr.editable', 'select', 'sp_DBR_varchar_edit', 'inId=id', 'type=select', "select=select id, value from char_values";
select 'dbr.editable', 'radio', 'sp_DBR_integer_edit', 'inId=id', 'type=radiobutton', "select=sp_radiovalues";
select 'dbr.editable', 'Date', 'sp_DBR_date_edit', 'inId=id', 'type=datepicker';
select 'dbr.editable', 'color', 'sp_DBR_color_edit', 'inId=id', 'type=colorpicker', "options={'callback':color_callback}";

select 'dbr.hidecolumn' 'id';

select  e.plain_column as 'Basic Input[Basic]',
        e.text_column as 'Textarea[autosize]',
        c.value as 'Select list[select]',
        i.value as 'Radio Button[radio]',
        e.date_column as 'Date', 
        e.color as 'Color[color]',  /* color format: #626262 */
        id
from mydb.editable e
  join int_values i on i.id = e.integer_column
  join char_values c on c.id = e.char_column;

END

Table int_values and its associated sp_radiovalues looks like this:

select * from int_values;

id   name
-----------
 1   'Asia'
 2   'Europe'
 3   'North America'
 4   'Africa'
 5   'Oceania'
 6   'South America'
 7   'Antartica'

CREATE PROCEDURE sp_radiovalues()
BEGIN

select id, name
from int_values;

END

Updating procedures get two parameters. First, the supplied 'id' parameter from first column of the result set and the user-supplied value with the specific datatype. The update procedures can include checks for the input, update the data and optionally return a value which will replace the user input.

For the date column we'll do a direct update

CREATE PROCEDURE sp_DBR_date_edit (
inId int,
inUservalue date
)
BEGIN

update mydb.editable
set date_column = inUservalue
where id=inId;

END

For the datetime we, for example, can check if the date belongs to the current year and accept/reject the update based on that.

CREATE PROCEDURE sp_DBR_datetime_edit (
inId int,
inUservalue datetime
)
BEGIN

/* Does the user date belong to current year? */
if ( year(inUservalue) = year(now()) ) then
  update mydb.editable
  set datetime_column = inUservalue
  where id=inId;
else
  /* Do not update and return the original data back to report */
  select datetime_column
  from mydb.editable
  where id=inId;
end if;

END

myDBR uses the IDs of the user selectable radio buttons. If this update would be rejected and ID converted, myDBR will automatically convert the ID back into user-visible value.

CREATE PROCEDURE sp_DBR_integer_edit (
inId int,
inUservalue int
)
BEGIN

update mydb.editable
set integer_column = inUservalue
where id=inId;

END

Select works just like the radio button automating the conversion between ids and user-visible values.

CREATE PROCEDURE sp_DBR_varchar_edit (
inId int,
inUservalue varchar(5)
)
BEGIN

update mydb.editable
set varchar_column = inUservalue
where id=inId;

END

When using checkbox-type, one places the ID's (numeric) and the user-visible values in separate columns. The ID column is pinpointed with 'checkbox_id'-option. myDBR will return the selected values wrapped in DIV with class editable_cb_item and separated by DIV with class editable_cb_item_comma. This allows the formatting of the visible values either in the same line separated with a comma or in separate lines.

CREATE PROCEDURE sp_DBR_editable_checkbox()
BEGIN

select 'dbr.editable', '[Categories]', 'sp_DBR_edit_film_category', 'in_film_id=film_id','checkbox_id=ids', 'type=checkbox', 'select=select category_id, name from category';


select fi.film_id, fi.title, ca.cat_ids as 'ids', ca.cat_names as 'Categories'
from film_samples fi
join (
  select f.film_id, group_concat(c.category_id) as cat_ids, group_concat(c.name) as cat_names
  from film_samples f
    left join film_category fc on fc.film_id=f.film_id
    left join category c on c.category_id=fc.category_id
  group by f.film_id
) ca on ca.film_id = fi.film_id;


select 'dbr.css', '.inlines .editable_cb_item {display:block} .inlines .editable_cb_item_comma {display:none}';

select 'dbr.resultclass', 'inlines';
select 'dbr.editable', '[Categories]', 'sp_DBR_edit_film_category', 'in_film_id=film_id','checkbox_id=ids', 'type=checkbox', 'select=select category_id, name from category';


select fi.film_id, fi.title, ca.cat_ids as 'ids', ca.cat_names as 'Categories'
from film_samples fi
join (
  select f.film_id, group_concat(c.category_id) as cat_ids, concat('dbr.html:',group_concat(concat('<div class="editable_cb_item">',c.name,'</div>') SEPARATOR '<div class="editable_cb_item_comma">,</div>')) as cat_names
  from film_samples f
    left join film_category fc on fc.film_id=f.film_id
    left join category c on c.category_id=fc.category_id
  group by f.film_id
) ca on ca.film_id = fi.film_id;

END

Passing parameters to a parameter query

When stored procedures are used in dbr.editable select parameters, you can pass parameters to it using normal column references.

CREATE PROCEDURE `sp_DBR_update_inputtype_parameter`()
BEGIN
select 'dbr.pageview';

select 'dbr.editable', 'Select', 'sp_DBR_integer_edit', 'inId=id', 'type=select', "select=sp_select_with_params [id]";

select  id, 
        date_column as 'Date', 
        datetime_column as 'Datetime', 
        i.value as 'Radio Button[radio]',
        c.value as 'Select'
from mydb.editable e, int_values i, char_values c
where e.integer_column = i.id and e.char_column = c.id;

END

Example of using file_rename-type dbr.editable

File renaming editable type is meant for situations where there is a table in the database containing the info which files user can edit. The routine changes the actual file name and the reference in the database.

The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename.

CREATE PROCEDURE `sp_DBR_editable_rename`()
begin

select 'dbr.editable', 'file', 'sp_DBR_editable_rename_do', 'type=file_rename', 'in_id=id';

select id, substring_index(file, '/', -1) as 'file'
from editable_rename
where id=1;

end;

The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename. The editable procedure for file rename has two automatically filled parameters (the last two). The penultimate parameter gets values of 'get' and 'set' and the last one the user edited file name. The routine is called first with the parameter 'get' to get the full filename based on the ID. At this stage, the routine should return the full filename. After a successful rename, the routine is called second time so that the file reference can be updated. The file will not be renamed if a) file does not exist in the filesystem b) there exists already a file with the new name or c) there are not enough filesystem permissions to the rename action.

create procedure sp_DBR_editable_rename_do(
in_id int,
in_action varchar(10),
in_file varchar(255)
)
begin

if (in_action='get') then
  select file
  from editable_rename
  where id=in_id;
else
  update editable_rename
  set file = in_file
  where id=in_id;
end if;

end

Example of basic callback usage

Auto-updating the summary columns with the supplied autosum_int / autosum_float requires you to just to define the predefined callback function. After the editing is done, a new summary value is calculated.

Setting the onblur option to empty makes the 'OK'- and 'Cancel'-buttons to appear below the enterable field.

select 'dbr.editable', 'value', 'sp_DBR_budget_edit', 'inAccount=account', "options={'onblur':'', 'callback':autosum_int}";
select 'dbr.sum', 2;

select account, value
from budget;

Example of own callback function and the onsubmit

In the example, we have an editable column and a column that holds the calculated value of other columns. When a user updates a column, we need to calculate new calculated value and refresh the summary columns. In the example, we'll use myDBR built-in functions col_value_get and col_value_set which will get and set table cell value for a given cell.

The example also checks if the entered value is bigger than 100 and asks confirmation from the user. This is done by setting own handler for the onsubmit event.

The 'col_value_get'-function has the following format: col_value_get( obj, col ), where obj is the reference to the cell and col is the column class in the row if another column than the object's own column is referenced.

The 'col_value_set'-function has the following format: col_value_set( obj, col, decimals, val, prefix, suffix ), where 'obj' is the reference to the cell and col is the column class in the row if other column than the object's column is referenced. 'val' is the value to insert to set and prefix and suffix are additional text to be attached to the cell value.

The autosum-functions have the following formats:

autosum_int( obj, column, prefix, suffix )

autosum_float( obj, column, decimals, prefix, suffix )

When the first row's B column is updated following callback will calculate the cells marked in red.


select 'dbr.javascript', 'function mycallback()
{

// Value of A in edited row
var A = col_value_get( this, "Acol" );

// Value of B in edited cell
var B = col_value_get( this );

// Set the value of 4th A+B column
col_value_set( this, "ABcol", 0, A+B);

// Calculate sum of edited column
autosum_int(this);

// Calculate sum of A+B column
autosum_int(this,"ABcol");

}
function mysubmit(settings, cell)
{
  var edited_value;

  edited_value = Cell_data.get_value_raw( $(cell).find("input").val(), "int" );

  if ( (edited_value>100) && !confirm("Do you to set the value this high ("+$(cell).find("input").val()+")?")) {
    cell.reset();
    return false;
  }
  return true;
}

';

select 'dbr.sum', 'A', 'B', 'AB';
select 'dbr.editable', 'B', 'sp_DBR_edit_B', 'inKey=[ID]', "options={'callback':mycallback,'onsubmit':mysubmit}";

select 'dbr.colclass', 'A', 'Acol';
select 'dbr.colclass', 'A+B', 'ABcol';

select ID, A, B, A+B as 'A+B'
from mytable;

Example of using dbr.selectable

dbr.selectable converts ID columns of a result set to checkboxes, which can be used to mark the rows to be processed. The selected row ID's can be used in a subsequent linked report (usually with a combination of dbr.button). The result set for the selectable will be marked with the dbr.resultclass. The selected row ID's can later be referenced in the linked report using 'selectable.cssclass'-notation where the cssclass is class added with dbr.resultclass. Note that the selected ID's are user given values, so you should not trust the values and should validate the values in the linked report if needed.

Convert an integer ID column to checkboxes and mark the result with dbr.resultclass. The list of ID's is passed as a parameter to the report attached to a button via the selectable.resultclass syntax:

/* Convert the ID column to checkboxes */
select 'dbr.selectable', 'ID';
/* Give the result set a class which will be used later as parameter reference to the linked report */
select 'dbr.resultclass', 'films';

select  
  f.film_id as 'ID',
  f.title as 'Title',
  c.name as 'Category'
from films f 
  join category c on c.category_id = f.category_id;
  
select 'dbr.button', 'Set category';
select 'dbr.report', 'sp_DBR_film_category_set', 'dynamic_div[]', 'in_ids=selectable.films';

select 'dummy';

The ID's are passed in as a comma-separated string ("1,2,3,4"). At the end, we'll refresh the main report with dbr.refresh

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

select 'dbr.refresh';

end

To refresh the list with selected values, one has multiple choices:

  1. Use dbr.refresh to reload the whole report
  2. Embed the element with dbr.report and use a hidden button to trigger the element refresh
  3. Use direct JavaScript to refresh the values (can be used when the values are not used in another dbr.report/dbr.url)

To use direct JavaScript to change the visible values

create procedure sp_DBR_film_category_set(
in_ids text,
in_category_id int
)
begin
declare v_category_name varchar(50);

update films 
set category_id = in_category_id
where find_in_set(films.film_id, in_ids);

-- Get the name
select name into v_category_name
from category
where category_id = in_category_id;

-- Escape the v_category_name if it can contain backslashes or '-characters, if not, you can skip this
set v_category_name = replace(v_category_name, "\\", "\\\\");
set v_category_name = replace(v_category_name, "'", "\\'");

select 'dbr.javascript', concat("selected.set_col_value('films', '",in_ids,"', 'category', '",v_category_name,"')");

end

The selected.set_col_value is a helper function which has a format of

selected.set_col_value(table_class, comma_separated_id_string, column_class, visible_value);.

It changes the value for column_class-columm for all rows whose ID matches with the supplied ID's and the table is identified by the table_class.

Rich text editing

By adding 'Rich text'-extension to the report from report parameters, one can use 'richtext' editing type which will bring up full-fledged editor. The editor can be invoked from direct HTML code or from HTML code shown with dbr.html:.

myDBR uses TinyMCE for the Rich Text editor. TinyMCE options can be passed in as the 'richtext'-option.

To show the HTML code as rendered HTML, we'll use 'dbr.html:' in the query. To enable rich text editing, we'll use 'type=richtext' and use option "'html':1" to indicate that we'll be converting the HTML into an editable format.

select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'html':1,'richtext':{'width': 600, 'height': 350}}";
select 'dbr.hidecolumn', 'id';
select 'dbr.colstyle', 'rich_text', '[white-space: pre;]';

select id, concat('dbr.html:', rich_text) as rich_text
from rich_text;

One can also use direct HTML code to invoke the editor. In this case, the code is shown as is in the query and no html-option is passed.

select 'dbr.colstyle', 'rich_text', '[white-space:pre]';
select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'rich_text',  'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'richtext':{'width': 600, 'height': 350}}";

select id, rich_text
from rich_text;

Code editing

By adding 'Code editor'-extension to the report from report parameters, one can use code editing with 'html', 'php', 'javascript', 'css' and 'sql' types.

select 'dbr.hidecolumn', 'id';
select 'dbr.editable', 'code', 'sp_DBR_edit_code', 'inA=id', 'type=php';
select 'dbr.colstyle', 'code', '[white-space:pre;font-family:Courier]';

select id, code
from code_editing;

Example of using dbr.upload

With dbr.upload, one can offer file upload capability to the server.


select 'dbr.upload.options', 'noreplace', 1;
select 'dbr.upload.options', 'accepted_files', '.pdf,.xlsx';

select 'dbr.upload', '/var/www/upload', 'sp_DBR_upload_file', v_client_id;

select 'dummy';

The upload procedure takes three fixed parameters path, filename and file size. One can also pass additional parameters to the proceudre. The procedure will return the text that is shown to the user after successful upload.

create procedure sp_DBR_upload_file(
in_path varchar(255),
in_file  varchar(255),
in_size int,
in_id int,
inLogin varchar(30)
)
begin


insert into uploads 
values (inLogin, in_path, in_file, in_size, in_id, now());

select concat('File ', in_file, ' uploaded (',format_bytes(in_size),')');

end