community.egroupware.org: Community wiki

  
Community wiki
The 5th day
the day before | Up | the day after


As we started our fifth day, we tried to find out about our needs.
  • we needed to be able to retrieve the data stored in the database
  • we wanted to display thoose data
  • we wanted to be able to edit the data
  • and we wanted to be able to get rid of already stored data.
The typical CRUD functionality.

Just to retrieve data, without being able to display them, does not make sense.
There are two major ways in eGroupware to do the trick.
  • grids, with the autorepeat functionality of the grid-widget triggered
  • the next-match widget, which - in fact - uses a grid, which you have to provide.

building our grid

We decided for a split design for our application.
  • test.index
  • test.index.rows

This means nesting eTemplates. To do that. go to the last cell of test.index.
In our test-application it is the debug - checkbox.
Add a widget behind that one, choose template as style. In options provide a namespace
(we did choose nm), so the data of that new template could be easily told apart
in the content array.
With the namespace we constructed a data-structure like this:
$content=array(
	'nm'=>array(
		)
	)
In name provide the name of the new template rows (yet to be created).
Save the changes.
Select the newly created widget, and watch that we get the message:
Error: Template not found !!!
Also note that the name is provided as test.index.rows. If you save or apply now
you get Error: Template not found !!!Template saved as message.
Now we provide the header of our database table, with
  • test_id -> ID
  • test_name -> name
  • test_firstname -> firstname
  • test_property -> property
  • test_dts -> date+time
For this we create 5 columns in the grid and label them accordingly: ID, name, firstname, property, date+time.

Since this is the table-header (of our grid of the new template rows) tell eTemplate so, by entering th as class of the cells you create.
Now add a row below our newly created table header.
Select the new row, and assign the row class as class of the first field (by entering row as class),
to make that area the table-body.
Now we have to set the names of the fields of the table.
''Consider, that we design a table with a single row as template for the table body
to hold an unknown number of rows (records) as output from our database table.''
Knowing this, we want to know how the data are provided.

Data are typically provided as arrays similar to this:
array(
		0=>array(
			'test_id'=>13,
			'test_name'=>'klaus',
			'test_firstname'=>'Leithoff',
			'test_property'=>'con',
			'test_dts'=>1344456,
			),
		1=>array(
			'test_id'=>14,
			...
			)
	)
This is, if you query our new table, of course.
So we have a named array, with an enumeration index for the rows retrieved, and
a named set of keys, for the columns retrieved per row.
To be able to put a datum into a cell we must know both the index of our row and
the key for our tablecolumn.
A name to adress the cells within our grid must need the needs.
  • ${row}[test_id]
  • ${row}[test_name]
  • ${row}[test_firstname]
  • ${row}[test_property] -> select selectbox as widget-type and make the cell readonly
  • ${row}[test_dts] -> remember to select date+time as widget-type and make the cell readonly
They will do the trick. You may ask: why???
A name like that will become 1[test_id] for the first row, 2[test_id] for the second row and so on to [lastrow][test_id].
Check with the eTemplate documentation class.botemplate.inc.php ->expand_name, here it is explained in detail.

This line, equipped like that, will repeat itself, if there are data to display. This is checked with the autorepeat functionality in eTemplate.
The function autorepeat_idx checks with the first (and second column, if the first contains no data) of a row, if there are data to display. If there are data the row is build
and populated.
See class.botemplate.inc.php -> autorepeat_idx for further details.
The XML-dump (ExportXML?) of our sub-template looks like that:

The whole thing presents itself as:

Now we need to populate the grid. So we need data. To retrieve the data, we will search for them.
The interesting parameters for the search function are
  • criteria (empty),
  • only_keys (false) and
  • order_by (ordered by test_dts in descending order)
	/**
	 * search function
	 *
	 */
	function search_all()
	{
		//array will be returned 0 indexed
		$result=$this->search('',false,'test_dts DESC');
		// the searchresult must be adapted to the requirements of eTemplate
		// insert a first row, to shift the data a notch up the index ladder, since data
		// for eTemplate begin with index 1
		array_unshift($result,'nothing');
		return $result;
	}
Thoose we use, since we wanted to see the entrys, with the most current ones at the top.
To have the information on our fingertips we must call our search routine from within our main function
index. Since we want to see all the data, even thoose, which we have just inserted, we call
it, just before we call (read) and execute (exec) our eTemplate.
Note that we populate only the namespace nm -> $content['nm']=$this->search_all();
    function index($content=null)
    {
        if ($content['clear'])
        {
            //$content=array();
            $content['debug']=0;
            $content['who']='';
            $content['test_name']='';
            $content['test_firstname']='';
        }
        $debug=$content['debug'];
        $separator=$content['debug'];
        $sel_options = array(
                'test_property' => $this->types
        );

        if (is_array($content) && !$content['clear']){
            // after submit
            $content['debug']=$debug;
            $content['message']=print_r($sel_options,true).print_r($content,true);
            $content['datetime']=time();
            if (trim($content['test_firstname'].$content['test_name'])!='')
            {
                $this->data['test_name']=$content['test_name'];
                $this->data['test_firstname']=$content['test_firstname'];
                $this->data['test_dts']=$this->now;

                $this->save();
                $content['who']=$content['test_firstname']." ".$content['test_name'] ;
                $separator=1;
            }

            $content['test_name']='';
            $content['test_firstname']='';
        } else {
            // first call
            /*
            $content=array(
                'who'=>', please type a name ...',
            );
            */
        }
        // call and populate of template test.index.rows
        $content['nm']=$this->search_all();
        $content['message'].='After the search: '.print_r($content,true);
        //$tmpl=new etemplate('test.index'); //this is discarded since we do that while constructing the class
        $this->tmpl->read('test.index');
        $this->tmpl->set_cell_attribute('debuginfo','disabled',!$debug);
        $this->tmpl->set_cell_attribute('myhrule','disabled',!$separator);
        $this->tmpl->exec('test.test_ui.index',$content,$sel_options);
        // the debug info will be displayed at the very end of the page
        //_debug_array($content);
    }
We can see now, that there is a problem with our data:

The property is empty in all cases. This is, since we populate the data sub-array, which is used by the save method, ourself.
    $this->data['test_name']=$content['test_name'];
    $this->data['test_firstname']=$content['test_firstname'];
    $this->data['test_dts']=$this->now;

    $this->save();
Two possible solutions to that problem are available:
    $this->data['test_name']=$content['test_name'];
    $this->data['test_firstname']=$content['test_firstname'];
    $this->data['test_property']=$content['test_property'];
    $this->data['test_dts']=$this->now;

    $this->save();
or:
    $this->data['test_dts']=$this->now;
    $this->save($content);
    // resetting of the data-array
    $this->init();
With that solution, we leave the selection of the data to be inserted into the database table to the save function.
Having done that, we still notice, that instead of an empty field, we get Select one .. displayed as entry in the field.

Remember (?!), we defined the property of our entrys in the class.test_bo.inc.php.
     var $types = array(
         ''      => 'Select one ...',
         'con'    => 'Contact',
         'bus'   => 'Business',
     );
and assigned the types array to the sel_options - array
        $sel_options = array(
                'test_property' => $this->types
        );
Since the sel_options - array is applied everywhere, where it possiply matches for select boxes, we get the values
rather than the keys (which are stored with the database).
We supplied an empty string (as key) and a display value (as keyvalue). If we cut out the no-value-key
     var $types = array(
         'con'    => 'Contact',
         'bus'   => 'Business',
     );
we take care of the display problem, within our table-grid. But now we are missing an empty entry for our select box.
This can be solved by selecting the cell with the test_property - selectbox within our original test.index template and applying the text
Select one ... to the options field.
So we learn to walk the eTemplate step by step.
We can see our data now, but we can not alter or delete them. We want to be able to do that as well.
  • So we added another column to our grid in test.index.rows,
  • labeled the label in the table-header section actions.
  • went to the table-body, and transformed the label to an hbox
  • saved the changes
  • selected the new cell within our hbox (you can see what is selected by looking at the name section).
  • choosed submitbutton as type
  • applied edit as option
    • which means that the picture/icon edit will be applied as button texture/image (if found)
  • labeled the button edit, in case the picture is not found, or we wanted a tool-tip info for that button
  • and named it edit[$row_cont[test_id]]
  • applied the changes
  • added another widget behind
  • selected the new cell within our hbox (you can see what is selected by looking at the name section).
  • choosed submitbutton as type
  • applied delete as option
    • which means that the picture/icon delete will be applied as button texture/image (if found)
  • labeled the button delete, in case the picture is not found, or we wanted a tool-tip info for that button
  • and named it delete[$row_cont[test_id]]

Having the buttons in place, we must handle their request for action within our code. Buttons are only submitted
if they are pressed, so handling their request is watching for their notification and act upon it.
We added
    # handle any button pressed within the grid-area
    if ($content['nm']['edit'])
    {
        list($id)=each($content['nm']['edit']);
        if ($this->read($id))
        {
            $content=$this->data;
        }
    }
    // delete if set
    if ($content['nm']['delete'])
    {
        list($del)=each($content['nm']['delete']);
        //echo "<br>del $del<br>";
        $this->delete(array('test_id'=>$del));
    }
at the very begining of our function. Read does, what the name suggests. It reads data from the database, and supplies them to the data-array.
Location: C:\Xampp\xampp\htdocs\egroupware\etemplate\inc\class.so_sql.inc.php
class so_sql
function read(
	array $keys,
	string/array $extra_cols,
	string $join)
reads row matched by key and puts all cols in the data array

delete deletes a row (or rows) represented by the key/value pairs supplied:
Location: C:\Xampp\xampp\htdocs\egroupware\etemplate\inc\class.so_sql.inc.php
class so_sql
function delete(array $keys)
deletes row representing keys in internal data or the supplied $keys if != null
Parameters:
      array keys if given array with col => value pairs to characterise the rows to delete

Deletion worked pretty nice from the start.

Now we have to take into account that if we have a read action, we supply a name and a firstname, which triggers our
old functionality to greet us, then save the data, and empty the edit fields for new data to come. We do not want that, if we
read the data to edit them. So we alter
if (is_array($content) && !$content['clear']){
to
if (!$id && is_array($content) && !$content['clear']){

So we alter
$this->tmpl->exec('test.test_ui.index',$content,$sel_options);
to
$this->tmpl->exec('test.test_ui.index',$content,$sel_options,'',array('test_id'=>$this->data['test_id']));
this activates the preserv option of the exec method with vars which should be transported to the method-call (eg. an id): array('id' => id) sets _POST['id'] for the method-call.

Now the edit works as well, although we notice that we loose the debuginfo-settings, if we submit from the table-grid. This is, because we reset our
content-array as we read the data. As we handled that little problem by merging the data-array into the content-array:
    if ($content['nm']['edit'])
    {
        list($id)=each($content['nm']['edit']);
        if ($this->read($id))
        {
            //$content=$this->data;
            // merge the data array into the content, preserving non-db settings
            foreach($this->data as $db_col => $col)
            {
                    $content[$db_col]=$col;
            }
        }
    }
Even the editing of our existing data worked properly, and the original functionality with the debug-option and the clearing of our edit-fields was restored.

Since a table can contain a lot of data, you may want to add a scrollbar, if the height of the table is exeeding a given height.
You can do that by
If you want to reset that setting you have to
  • replace the given height (e.g. 100) with auto (, since an empty Entry will not be saved) and
  • set the value for overflow to visible.

If you wanted to sort the data, by some other column then the test_dts, you would have to think of a checkbox within the table-header
handling the checked boxes, and then apply the sort-criteria to the search.
If you had a lot of data, and wanted to filter the data, you had to add the functionality on your own.
If you had a lot of data, you would go crasy, for the solution, we have now, tries to retrieve all the data at once, and builds the
grid threafter. You would have to build some sort of paging on your own.

These are some of the reasons for the introduction of the next-match widget.

introducing the next-match widget

The next-match widget provides a lot of the above mentioned functionality, without a lot of programming.
It is of course documented with the eTemplate documentation.

First thing is, we restructure our template test.index. By now it holds a sub-template.

  • select nextmatch as type
  • provide rows in the options field (it is the name of the template used by the nextmatch widget)
  • enter nm as name (that is/becomes the namespace used by the nextmatch-widget)

If we call our template now, we get nextmatch_widget::pre_process(nm): '' is no valid method !!!

The most important thing to note, when working with the nextmatch widget is:
it MUST be initialized properly.
    // initializing of thwe nextmatch widget, through reading of stored sessiondata
    $content['nm']= $GLOBALS['egw']->session->appsession(@$this->called_by.'session_data','test');
    // if empty, or not an  array, then you have to do the initializing on your own.
    if (!is_array($content['nm']))
    {
        $content['nm'] = array(                           // I = value set by the app, 0 = value on return / output
            'get_rows'       => 'test.test_ui.get_rows',   // I  method/callback to request the data for the rows eg. 'notes.bo.get_rows'
            'filter_label'   => '',                       // I  label for filter    (optional)
            'filter_help'    => '',                       // I  help-msg for filter (optional)
            'no_filter'      => True,                     // I  disable the 1. filter
            'no_filter2'     => True,                     // I  disable the 2. filter (params are the same as for filter)
            'no_cat'         => True,                     // I  disable the cat-selectbox
            //'template'       =>   ,                     // I  template to use for the rows, if not set via options
            //'header_left'    =>   ,                     // I  template to show left of the range-value, left-aligned (optional)
            //'header_right'   =>   ,                     // I  template to show right of the range-value, right-aligned (optional)
            //'bottom_too'     => True,                   // I  show the nextmatch-line (arrows, filters, search, ...) again after the rows
            'never_hide'     => True,                     // I  never hide the nextmatch-line if less then maxmatch entrie
            'lettersearch'   => False,                    // I  show a lettersearch
            'searchletter'   => '',                       // I0 active letter of the lettersearch or false for [all]
            'start'          => 0,                        // IO position in list
            //'num_rows'       =>   ,                     // IO number of rows to show, defaults to maxmatches from the general prefs
            //'cat_id'         =>   ,                     // IO category, if not 'no_cat' => True
            //'search'         =>   ,                     // IO search pattern
            'order'          => 'test_dts',               // IO name of the column to sort after (optional for the sortheaders)
            'sort'           => 'DESC',                   // IO direction of the sort: 'ASC' or 'DESC'
            'col_filter'     => array(),                  // IO array of column-name value pairs (optional for the filterheaders)
            //'filter'         =>   ,                     // IO filter, if not 'no_filter' => True
            //'filter_no_lang' => True,                   // I  set no_lang for filter (=dont translate the options)
            //'filter_onchange'=> 'this.form.submit();',  // I onChange action for filter, default: this.form.submit();
            //'filter2'        =>   ,                     // IO filter2, if not 'no_filter2' => True
            //'filter2_no_lang'=> True,                   // I  set no_lang for filter2 (=dont translate the options)
            //'filter2_onchange'=> 'this.form.submit();', // I onChange action for filter, default: this.form.submit();
            //'rows'           =>   ,                     //  O content set by callback
            //'total'          =>   ,                     //  O the total number of entries
            //'sel_options'    =>   ,                     //  O additional or changed sel_options set by the callback and merged into $tmpl->sel_options
        );
    } else {

    }

Note: You can see, that there is a method, you have to provide, to enable the nextmatch widget to populate the
rows of the bound sub-template.

    /**
     * query rows for the nextmatch widget
     *
     * @param array $query with keys 'start', 'search', 'order', 'sort', 'col_filter'
     *  For other keys like 'filter', 'cat_id' you have to reimplement this method in a derived class.
     * @param array &$rows returned rows/competitions
     * @param array &$readonlys eg. to disable buttons based on acl, not use here, maybe in a derived class
     * @param string $join='' sql to do a join, added as is after the table-name, eg. ", table2 WHERE x=y" or
     *  "LEFT JOIN table2 ON (x=y)", Note: there's no quoting done on $join!
     * @param boolean $need_full_no_count=false If true an unlimited query is run to determine the total number of rows, default false
     * @return int total number of rows
     * optional not used here: $join='',$need_full_no_count=false
     */
    function get_rows($query,&$rows,&$readonlys)
    {
        // save the nextmatch entrys/settings with the sessiondata
        $GLOBALS['egw']->session->appsession(@$this->called_by.'session_data','test',$query);
        return parent::get_rows($query,$rows,$readonlys);
    }
Since the nextmatch widget needs some data, to remember its own state, it is a good idea to store the sessiondata, before the action starts.
After having stored them with the session, you can retrieve them from there for the initialization process of the nextmatch widget.

Since we implemented a new method to fill our data-grid we should disable the old one by commenting it out. If we dont, the population method of the
search_all function, destroys our initialized nm-array, since we do not merge the search_all result into the nm-array but crudely
set the array.
    // call and populate of template test.index.rows
    // after using the nextmatch widget the search_all function is not used
    // for the population of the data-grid anymore
    //$content['nm']=$this->search_all();

Done that, we tried again to get our baby on the road.
  • displaying of the data -> worked fine
  • editing -> no go
  • deleting -> no go
Why? Because (after some debugging):
On function entry: Array
(
    [test_id] =>
    [test_firstname] =>
    [test_name] =>
    [test_property] =>
    [debug] => 1
    [nm] => Array
        (
            [search] =>
            [num_rows] => 15
            [rows] => Array
                (
                    [edit] => Array
                        (
                            [3] => pressed
                        )
                )
            [start] => 0
        )
)
There is no content of ['nm']['edit'] anymore. It is $content['nm']['rows']['edit'] now.
		# handle any button pressed within the grid-area
		if ($content['nm']['rows']['edit'])
		{
			list($id)=each($content['nm']['rows']['edit']);
			if ($this->read($id))
			{
				//$content=$this->data;
				// merge the data array into the content, preserving non-db settings
				foreach($this->data as $db_col => $col)
				{
						$content[$db_col]=$col;
				}
			}
		}
		//_debug_array($content);
		// delete if set
		if ($content['nm']['rows']['delete'])
		{
			list($del)=each($content['nm']['rows']['delete']);
			//echo "<br>del $del<br>";
			$this->delete(array('test_id'=>$del));
		}
Having fixed that, our baby was on the road. To go for a spin around the block, we added some shine.
For the table to use the entire width of the window.

a sort header, to sort by ID.

a sort header, to sort by name.

a sort header, to sort by firstname.

a filter header, to filter by property.

a sort header, to sort by date.

Here we go:


The outcome of Day 5 as ZiP File


the day before | Up | the day after

You are here