Showing posts with label CakePHP. Show all posts
Showing posts with label CakePHP. Show all posts

Tuesday, 7 August 2018

How to use the latest PHPOffice/Spreadsheet with CakePHP 2.x

Preface

Using the latest PHPOfficePhpSpreadsheet in a CakePHP3 application is relatively easy, A simple composer command like composer require phpoffice/phpspreadsheet does the trick in a few seconds and then you ready to go.

All of as Cake uses end up with a PHPExcelComponent that we either borrow or devise ourselves, so we can make our users ... happy by giving them the beloved excel files.

The problem with using PHPOffice/PhpSpreadsheet inside a CakePHP2 application is that CakePHP2 does not support namespaces and so the App::import() is not able to load all the required classes correctly.

This simple howto lists all the steps that I followed in order to make things work, with the help of this question at stackoverflow and this blog post from Mark.

The Steps

  1. Download the latest version from here.
  2. Extract the zip file and rename the src folder (inside Phpspreadsheet-develop) to PhpOffice
  3. Go to your Cake2 project and move/copy this new PhpOffice folder inside your APP/Vendors folder
  4. While you are there, create another folder named Psr (inside your APP/Vendors) and an other folder named SimpleCache inside Psr. (You will know why in a minute)
  5. PhpSpreadsheet refferences the Fig-Simple cache library that we also have to install manually. Go to the php-fig/simple-cache page and download the required zip file (name is simple-cache-master.zip).
  6. Unzip that in a temprary directory and move all three files from the src folder to APP/Vendors/Psr/SimpleCache.
  7. Open your APP/Config/bootstrap.php file and place the following code at the end.
        /**
         * Configure the autoloader
         */
        spl_autoload_register( function($class) {
            foreach(App::path('Vendor') as $base) {
                $path = $base . str_replace('\\', DS, $class) . '.php';
                if (file_exists($path)) 
                    return include $path;            
            }
        }, true);
    
  8. That's it. If you want to use the Spreadsheet class, all you have to do is place a use PhpOffice\PhpSpreadsheet\Spreadsheet; statement at the top of your file and then any reference like _xls = new Spreadsheet(); will work just fine.

A few afterwords

What we actually accomplished here is that we instructed the php class loader to search for any class it does not know about inside a speciffic sub folder of our APP/Vendors. This subfolder structure, must confirm to the exact package name of the class. Hence we created the PhpOffice/PhpSpreadsheet and Psr/SimpleCache subfolders. Using this technique we may add any additional library into our CakePHP2 application provided that we keep this naming standard. My setup was a CentOS 6.10 web server with PHP 5.6.37 (from Remi).

Once again, CakePHP3 and composer can make this job extremely easier, so unless you really have to, do not go into all this fass ;)

Monday, 14 July 2014

CakePHP Find out the link that brough us to the current page

Most web applications today have some kind of menu build with anchor tags. The problem that the following script -- courtesy of my friend Anestis from alfasoftware.gr -- solves, is how to determine the actual link that was used to bring the user to the currently displayed page.

To use it, place it inside a script tag towards the end of your app/Views/Layout/default.ctp file (just before the closing body tag, will do just fine) and a little bit of CSS code in your appropriate style file (again app/webroot/css/cake.generic.css is a good candidate).

The CSS code is pretty easy :

a.active {
    background-color: yellow; // add anything you like
}

and the javascript :


        <script type="text/javascript">
            // add the "active" class to the navigation link that brought us to this page
            jQuery( function() {
                // retrieve the relative url of the current page
                var curUrl = "<?php echo (Router::url( NULL, FALSE)); ?>";
                // in a cake application a relative URL is usually like
                // /application/controller/action/param1/param2 ...
                // so what we really need is the first three pieses of the URL
                var tokens = curUrl.split("/").slice(0,4);
                curUrl = tokens.join("/");
                // if there is any pagination information then the word "index" also appears in the URL
                // in this case we need to remove it, so it can match generated URL
                var indexIndex = curUrl.indexOf('index');
                if ( indexIndex > -1)
                    curUrl = curUrl.substring( 0, indexIndex - 1);

                // for each page anchor tag
                $('a').each( function() {
                    // retrieve the arnchor's target
                    var ref = $(this).attr("href");
                    // so if that anchor points to the current page
                    if(ref === curUrl){
                        $(this).addClass("active");
                    }

                });
            });
        </script>

Enjoy!

Thursday, 20 March 2014

CakePHP-2 and AJAX The dependent list boxes problem (remake)

Forward

Back in 2010, I had written a post regarding the case of dependent list boxes in a CakePHP view. Back at those days CakePHP was at version 1.2 and support for Javascript and AJAX was very limited. Today I shall revise this using JQuery and JSON encoding which will make things simpler and easier to understand, implement and maintain.

The long story short

Suppose you have a page with two list boxes. One contains a standard set of values while the second one's list of values must be dynamically updated depending on the actual selected value of the first.

In our (sort of) real life example we have a list of commissions (aka production orders) that produce a series of products of varying lengths. The list of lengths that each commission is allowed to produce is available via a detail table and our goal is write an addProduct action and view that allows the user to specify the commission based on which the actual product was produced and the actual length of the product that should be one of the assigned commission lengths. The Product model has a commission_id and an actual_length fields. So each time the commission combo box changes the actual length field input options should also change in order to contain the commissions list of allowed lengths.

Getting Started

To begin with our tutorial make sure that your standard layout references the jQuery library. The easiest was to do this would be to open APP/View/Layout/default.ctp and make sure that a line like

<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>

can be found somewhere in your html's head section.

The next thing that needs to be done is to add the RequestHandler component in our AppController. Add or modify your existing source so it looks more or less like this.

class AppController extends Controller {
    public $components = array(
        'RequestHandler', 
        ...
    );

    ,,,
}

Next we need to inform our routing system that it should also parse json URLs. So the line

Router::parseExtensions('json');

should be added to the APP/Config/routes.php file right below the other Router::xxx() commands.

Create the AJAX method and view to return the JSON encoded list of lengths

The next thing to do is create the action that will return the list of lengths to be sent back to the view given commission id. The best place to put it will be the commissions controller. The code for the function looks a lot like the one baked by view. We just need to make sure that the data returned "contain" the correct detail information

    public function getRequestedLengths($id)
    {
        if (!$this->Commission->exists($id)) 
            throw new NotFoundException(__('Invalid record'));
        
        $options = array(
            'conditions' => array(
                'Commission.' . $this->Commission->primaryKey => $id
            ),
            'contain' => array(
                'CommissionProduct',
            )
        );
        $this->set('commission', $this->Commission->find('first', $options));
    }

The view for the method should be placed in: APP/View/Commissions/json/get_requested_lengths.ctp.

<?php
/*
 * Create and echo a json encoded list of the allowed lengths
 ^/
$output = array();
if (!empty($commission['CommissionProduct']))
    foreach ($commission['CommissionProduct'] as $commissionProduct)
        $output[] = $commissionProduct['actual_length'];


echo json_encode($output);

So now if we point our browser towards http://yourserver/projectPath/Commissions/getRequestedLengths/7.json, we will receive a response with a JSON array containing all allowed lengths for commission id 7. The actual response will be something like ["45900","23400"].

Building the addProduct view

So far we have the mechanism to retrieve the required data. The final step will be to use it in the actual add product view, the PHP part of which should look more or less like this:

<div class="products form">
    <?php echo $this->Form->create('Product'); ?>
        <fieldset>
            <legend>Add Product</legend>
            <?php echo $this->Form->input('commission_id', array('empty' => __('Please select a commission'))); ?>
            <?php echo $this->Form->input('operator_id'); ?>
            <?php echo $this->Form->input('shift'); ?>
            <?php
                echo $this->Form->input(
                'status',
                array(
                    'type' => 'select',
                    'options' => $statusList
                )
                );
            ?>
            <?php echo $this->Form->input('actual_length', array('type' => 'select')); ?>
            <?php echo $this->Form->input('gross_weight'); ?>
        </fieldset>
    <?php echo $this->Form->end('Save'); ?>
</div>

The final part will be the adding of Javascript code to make our form responsive.

<script type="text/javascript">
    var commissionsCombo;
    var allowedLengthsCombo;

    jQuery(function() {
        commissionsCombo = jQuery('#ProductCommissionId');
        allowedLengthsCombo = jQuery('#ProductActualLength');

        commissionsCombo.change( function() {
            var selectedCommission = this.value;  // or $(this).val()
            // build the url that contained the selected commission code
            var ajaxUrl =
                    '<?php echo Router::url(array('controller' => 'Commissions', 'action' => 'getRequestedLengths', 'admin' => FALSE), TRUE)?>'
                    + '/'
                    + selectedCommission
                    + '.json';

            // do a "synchronous" AJAX call
            jQuery.ajax({
                    type:'GET',
                    async: false,
                    cache: false,
                    url: ajaxUrl,
                    success: function(response) {
                        // remove all options from the allowed metres per bobbin
                        allowedLengthsCombo.find('option').remove();

                        // add all returned values
                        for (var i = 0; i < response.length; i++) {
                            var currentKey = response[i];
                            var currentKeyDescr = response[i] + ' metres';
                            var optionText = '<option value="'
                                    + currentKey
                                    + '">'
                                    + currentKeyDescr
                                    + '</option>';
                            allowedLengthsCombo.append(optionText);
                        }
                    }
            });
        });
    });
</script>

As a last statement The code for manipulating the select input options comes from the very consise post from StackOverflow.com

Friday, 31 January 2014

CakePHP 2.x: Saving paging data in the session

Quite some time ago I wrote a blog post about saving CakePHP 1.x paging data in the session so that they can be available at next page visit. The basic idea was that you could store the page, sort and direction named parameters in the session and restore them back when no paging parameters were available.

When I tried applying the same technique in CakePHP 2.x, I run into a very serious obstacle and that was the fact that the Paginator::numbers() function does not anymore include the page:1 named parameter when creating the link for the first (or previous) page. This created a phenomenon that when someone visited a page without any paging parameters, it was impossible to know whether they were there because of a link to the first page or as a result of a redirect from somewhere else, in which case the session had to be checked and paging data to be restored.

The thing had been puzzling me for some days now. I tried different remedies without any luck or results until I cried for help at stackoverflow.com. It was there that Ilie Pandia shook things a bit and then I managed, thanks to his advice, to create the following component that mimics the original Cake 1.x PaginationRecallComponent by mattc found in the Bakery, from which I borrowed the name and structure.

This version is new (April-2015) and hopefully includes the bug-fixes pointed out by all comments. The tests where made using CakePHP version 2.6.3.

<?php
App::uses('Component', 'Controller');

/**
 * Pagination Recall CakePHP Component
 * CakePHP 2.x version. Thanassis Bakalidis abakalidis.blogspot.com=
 *
 * @author  Thanassis Bakalidis
 * @version  2.2
 * @license  MIT
 * @property SessionComponent $Sesion Session handler to save paging data into
 */
class PaginationRecallComponent extends Component {
    const PREV_DATA_KEY = 'Paginaion-PrevData';

    public $components = ['Session'];
    private $_controller = NULL;
    private $_action = NULL;
    private $_previousUrl;

    public function initialize(\Controller $controller)
    {
        $this->_controller = $controller;
        $this->_action = $controller->params['action'];
    }

    public function startup(Controller $controller)
    {
        if ($this->_controller->name === 'CakeError')
            return;

        $this->_restorePagingParams();

        // save the current controller and action for the next time
        $this->Session->write(
            self::PREV_DATA_KEY,
            [
                'controller' => $this->_controller->name,
                'action' => $this->_action
            ]
        );
    }

    private function _restorePagingParams()
    {
        $sessionKey = "Pagination.{$this->_controller->name}.{$this->_action}";

        // extract paging data from the request parameters
        $pagingParams = $this->_extractPagingParams();

        // if paging data exist write them in the session
        if (!empty($pagingParams)) {
            $this->Session->write( $sessionKey, $pagingParams);
            return;
        }

        // no paging data.
        // construct the previous URL
        $this->_previousUrl = $this->Session->check(self::PREV_DATA_KEY)
            ? $this->Session->read(self::PREV_DATA_KEY)
            : [
                'controller' => '',
                'action' => ''
            ];

        // and check if the current page is the same as the previous
        if ($this->_previousUrl['controller'] === $this->_controller->name &&
            $this->_previousUrl['action'] === $this->_action) {
            // in this case we have a link from our own paging::numbers() function
            // to move to page 1 pf the current page, delete any paging data
            $this->Session->delete($sessionKey);
            return;
        }

        // we are comming from a different page so if we have any session data
        if ($this->Session->check($sessionKey))
            // then restore and use them
            $this->_controller->request->params['named'] = array_merge(
                $this->_controller->request->params['named'],
                $this->Session->read($sessionKey)
            );
    }

    private function _extractPagingParams()
    {
        $pagingParams = $this->_controller->request->params['named'];
        $vars = ['page', 'sort', 'direction'];
        $keys = array_keys($pagingParams);
        $count = count($keys);

        for ($i = 0; $i < $count; $i++)
            if (!in_array($keys[$i], $vars))
                unset($pagingParams[$keys[$i]]);

        return $pagingParams;
    }
}

Note: It turns out that the components shutdown method is not called when the owner controller's action returns a redirect(). Hence, this last update was about getting rid of any shutdown() functionality and performing everything in the components startup() moethod code. This hopefully, fixes the bug of loosing paging data after a call to the delete() method which returns an immediate redirect..

Thursday, 7 November 2013

CakePHP New application database config (never forget the enconding)

Today I will put down the way to begin a new web application project using CakePHP.

The first part is database creation. Database charset must be utf-8 and collation: utf8_unicode_ci. The difference between general and unicode collations is explained in an excellent manner in this post on stackoverlow.com

Next, when you start a new CakePHP project the database config class looks like this


public $default = array(
  'datasource' => 'Database/Mysql',
  'persistent' => false,
  'host' => 'localhost',
  'login' => 'user',
  'password' => 'password',
  'database' => 'test_database_name',
  'prefix' => '',
  // 'encoding' => 'utf8',
);

My lesson today is: Before changing anything else, uncomment the last line!.

... in case you do not then Unicode (Greek in my case) text will still appear correctly in the web application, but phpMyAdmin and sqldump will display garbage. In addition searches with non-latin text will always fail. To make matters worse when I discovered the case of the problem, I realised that I would have to re-enter all my test data.

Monday, 7 October 2013

CakePHP locking tables

Here are my two cents on the issue.

The code below is a function from a behaviour that tries to create an additional unique key on a field named code, by counting the number of records created this year. The important part in the locking procedure is that we must specify the AS clause in the LOCK TABLES statement or otherwise the $model->find() function will not work complaining that the table is locked.

    public function getNextCode(&$model)
    {
        $thisYear = date('Y');
        $dbo = $model->getDataSource();
        $dbo->execute(
            sprintf('LOCK TABLES %s AS %s WRITE;',
                $model->table,
                $model->alias
            )
        );
        $recordsThisYear = $model->find(
            'count',
            array(
                'recursive' => -1,
                'conditions' => array(
                    $model->alias .'.code LIKE' => $thisYear.'%'
                )
            )
        );
        $dbo->execute('UNLOCK TABLES');
        return sprintf('%d-%06d', $thisYear, $recordsThisYear + 1);
    }

The original idea for the post and function cake from a doWeb posting available through here.

Thursday, 23 May 2013

CakePHP and AJAX submitting a form with jQuery

A couple of years back I wrote an article about how to handle the dependent drop down lists problem using CakePHP's Ajax facilities. Today i will put down a trivial example of how to submit a CakePHP created form using jQuery as a small reference that can be easily pasted.

Let me remind you of the CakePHP ajax way. You start by creating a controller method that will "return" the ajax content. For our trivial example, the following controller will be more than enough.

class AjaxController extends AppController {
   var $uses = NULL;

   public function helloAjax()
   {
       $this->layout='ajax';
       // result can be anything coming from $this->data
       $result =  'Hello Dolly!';
       $this->set("result", $result);
   }
}

The corresponding view file view/ajax/hello_ajax.ctp should contain just the following:

<?php echo $result; ?>

Setting up our Ajax call is now as easy as, creating a link or a button that will invoke the asynchronous call and then setting the id of the field that will receive the result. A typical setup would be that the link looks something like this :

<a href="#" id="performAjaxLink">Do Ajax </a>

And then the target field can be created using:

<?php echo $this->Form->input('your_field', array('id' => 'resultField')); ?>

Finally a little script at the end of the file ...

<script>
    jQuery("#performAjaxLink").click(
            function()
            {                
                jQuery.ajax({
                    type:'POST',
                    async: true,
                    cache: false,
                    url: '<?= Router::Url(['controller' => 'ajax','admin' => FALSE, 'action' => 'helloAjax'], TRUE); ?>',
                    success: function(response) {
                        jQuery('#resultField').val(response);
                    },
                    data:jQuery('form').serialize()
                });
                return false;
            }
    );
</script>

The jQuery Ajax API is available here.

Friday, 17 February 2012

CakePHP: Loosing translated texts

This one drove me crazy for the last couple of hours, so I thought I better share it right away.

I have a bilingual CakePHP application that displays content messages translated from English to Greek. To achieve this I wrap all my English texts inside cake's __() function and then run the cake i18n extract script in order to assemble a .pot file. Finally, I translate my original messages to Greek using the POEdit program to create and manage the necessary translations. Everything seemed to work well: Each time I added new strings, I would execute the cake i18n extract script, then open POEdit, update my .po catalogue from the generated .pot file and translate only the new texts.

Except for today. I was asked to asked to add a few more messages, so I followed the standard procedure. but after I updated both my .po and .mo files, the messages on the web page remained in English despite my ... sincere efforts and honest desire to see them in Greek.

It took me a while to figure this out: The solution was as simple as to delete all files from the APP/tmp/cache/persistent directory.

Saturday, 5 November 2011

CakePHP: Storing multi-dimentional arrays in cookies

II didn't know that cookies are basically plain text data. This makes it impossible to store complex data structures directly inside a cookie. More information can be found in the archives of the CakePHP google group following this link.

The bottom line is that complex data need to be serialized before being saved in a cookie and unserialized after they are read from one. The serialize() and unserialize() PHP functions are here to do the job and last but not least the third parameter of the Cookie::write call -- the one that instructs cake to encrypt the cookie data -- should be set to true.

So to save a controller's form data you need to write something like this:

    $dataToSave = serialize($this->data);
    $this->Cookie->write( self::SEARCH_DATA_KEY, $dataToSave, true, '1 year');

and to read them back ....

        if (empty($this->data)) {
            // try to see if we have a stored cookie
            $cookieData = $this->Cookie->read(self::SEARCH_DATA_KEY);                        
            if (empty($cookieData)) {
               // provide default values here 
               ...
            } else 
                $this->data = unserialize($cookieData);            
        }

Tuesday, 1 November 2011

CakePHP: An edit form with a cancel button (1.3 and 2.x)

When developing database CRUD applications with CakePHP, sooner or later you end up writing view code looking more or less like this
<div class="my model form">
<?php echo $this->Form->create('MyModel');?>
 <fieldset>
  <legend></legend>
 <?php
  echo $this->Form->input('id');
  echo $this->Form->input('name');
 ?>
 </fieldset>
<?php echo $this->Form->end(__('Submit', true));?>
</div>
This creates a nice form with a submit button at the end that every self respecting user can press to create a new record or modify the data of an existing one. It is also logical that you place a link to the index page somewhere near the form, so your users know where to go in case they change their mind about altering the database data.
With my users this time is was different. The form had to contain a cancel button. So how does one do it? If you are using the cake bake script and wish to have two nice round green buttons right at the bottom of your form then replace the last two lines of the previous code fragment with the following:
     ...
     <div class="submit">
         <?php echo $this->Form->submit(__('Submit', true), array('name' => 'ok', 'div' => false)); ?>
         <?php echo $this->Form->submit(__('Cancel', true), array('name' => 'cancel','div' => false)); ?>
     </div>
     </fieldset>                   
 <?php echo $this->Form->end();?>
The next thing to know from inside the controller code, is which button was pressed before the data were posted and that is available inside the 'form' array of the Controller::params property.
So a simple modification like the one on the following code :
        public function edit($id = null)
        {
            if (!$id && empty($this->data)) {
               $this->Session->setFlash(__('Invalid property', true));
               $this->redirect(array('action' => 'index'));
            }
            if (!empty($this->data)) {
                // abort if cancel button was pressed  
                if (isset( $this->params['form']['cancel'])) {
                    $this->Session->setFlash(__('Changes were not saved. User cancelled.', true));
                    $this->redirect( array( 'action' => 'index' ));
                }

                // proceed to save changes as usual
        }
... and everyone is happy.

Edit: Alternatively, if working with CakePHP version 2.4 then the sane info is inside the data array of the controller's request property.
So the previous code gets rewritten like this
        public function edit($id = null)
        {
            if (!$this->MyModel->exists($id)) {
                throw new NotFoundException(__('Invalid record'));
            }
            if ($this->request->is(array( 'post','put'))) {
                if (isset($this->request->data['cancel'])) {
                    $this->Session->setFlash(__('Changes were not saved. User cancelled.'));
                    return $this->redirect( array( 'action' => 'index' ));
                }

                // proceed to save changes as usual
        }
There is one last thing though... the cancel button in your form should also indicate that no form validation should be performed at the browser level. This can be accomplished by setting the 'formnovalidate' key of the Form::input options parameter to TRUE. So the whole cancel button creation tag should now look like this:
        <div class="submit">
            <?php echo $this->Form->submit(__('Create Account'), array('name' => 'ok', 'div' => FALSE)); ?>
            <?php echo $this->Form->submit(__('Cancel'), array('name' => 'cancel', 'formnovalidate' => TRUE, 'div' => FALSE)); ?>
        </div>
    Form->end(); ?>

Wednesday, 19 October 2011

A new version of the CakePHP QBE component

I have just developed and started testing of a new version of my QBE component.

Major changes are that the component now accepts the model name as an initialization parameter, it provides a new ~ X Y operator to implement the between clause and that support for a different search and results page is now more clear.

The code for the component, along with usage details can be found in my GitHub repository available from this link:.

Monday, 22 August 2011

SELinux and CakePHP on Fedora and CentOS

The first time I installed CakePHP, on a machine with SELinux enabled, I run into two big problems:

  1. Cake was unable to write to the application's tmp directory
  2. Cake was unable to connect tot the database, hosted on an other machine

The first thing that comes to mind, is to disable SELinux completely, and I did more than once :^). This time however, I said to myself that if so many people say SELinux is good, why not give it a try and see if we can both live peacefully on the same machine.

The first thing we need to deal with is allow access to the $APP/tmp directory. This can be accomplished by issuing :

# cd $APP
# chcon -Rv --type=httpd_user_content_rw_t tmp

Next will be to allow httpd to connect to a database hosted on a different machine than the one running the web server in case your setup use different machines for Database and web servers. This is allowed by issuing the following command again as the root user.

setsebool -P httpd_can_network_connect_db 1

For the moment my CakePHP server seems to be running fine. If any problems arise, I will update this post accordingly.

Finally a couple of links on SELinux

Friday, 5 August 2011

CakePHP The Query by Example Component

After the previous post, regarding how to create and use a input QBE form, here is my simple QBE component.

To use it, paste the following code in a file named qbe.php into your APP/controllers/components directory.

<?php
/**
 * @class QbeComponent
 * Convert posted data entered in a pseudo Query by Example fashion
 * from a CakePHP Form into Model::find() acceptable conditions.
 *
 * @author: Thanassis Bakalidis
 * @version: 1.0
 */
class QbeComponent extends Object {
    // sesion keys for saving and retrieving controller data
    const CONDITIONS_SESSION_KEY = 'SRCH_COND';
    const FORM_DATA_SESSION_KEY = 'SRCH_DATA';

    // supported SQL operators
    private $SQL_OPERATORS = array(
        'IN', '<>', '>=', '<=',
        '>', '<'
    );

    var $owner;     // the controller using the component

    /**
     * @name initialize
     * The initialize method is called before the controller's
     * beforeFilter method.
     */
    function initialize(&$controller, $settings=array())
    {
        $this->owner =& $controller;
    }

    /**
     * @name: getSearchConditions()
     * Return an array to be used as search conditions in a find
     * based on the controller's current data
     * @param : string $modelName name of the model to search controller data
     * @version: 1.3
     */
    function getSearchConditions($modelName = null)
    {
        if ($modelName == null)
            return null;

        // create speciffic keys for the model andcontroller
        $sessionConditionsKey = sprintf("%s-%s-%s",
                                self::CONDITIONS_SESSION_KEY,
                                $this->owner->name,
                                $modelName
                            );
        $sessionDataKey = sprintf("%s-%s-%s",
                                self::FORM_DATA_SESSION_KEY,
                                $this->owner->name,
                                $modelName
                            );

        if (empty($this->owner->data)) {
            // attempt to read conditions from sesion
            $conditions = $this->owner->Session->check($sessionConditionsKey)
                ? $this->owner->Session->read($sessionConditionsKey)
                : array();
            $this->owner->data = $this->owner->Session->check($sessionDataKey)
                ? $this->owner->Session->read($sessionDataKey)
                : array();
        } else {
            // we have posted data. Atempt to rebuild conditons
            // array
            $conditions = array();
            foreach( $this->owner->data[$modelName] as $key => $value) {
                if (empty($value))
                    continue;

                $operator = $this->extractOperator($value);

                if (is_array($value)) {
                    // this can only be a date field

                    $month = $value['month'];
                    $day = $value['day'];
                    $year = $value['year'];

                    // We want all three variables to be numeric so we 'll check their
                    // concatenation. After all PHP numbers as just strings with digits
                    if (is_numeric($month.$day.$year) && checkdate( $month, $day, $year)) {
                        $conditionsKey ="$modelName.$key";
                        $conditionsValue = "$year-$month-$day";
                    } else
                        continue;
                } else {
                    // we have normal input, remove any leading and trailing blanks
                    $value = trim($value);                          
                    // and check the operator given
                    if ($operator === '' && !is_numeric($value)) {
                        // turn '='' to 'LIKE' for non numeric data
                        // numeric data will be treated as if they
                        // have an wquals operator
                        $operator = 'LIKE';
                        $value = str_replace('*', '%',  $value);
                        $value = str_replace('?', '.',  $value);
                    } else if ($operator === 'IN') {
                        // we need to convert the input string to an aray
                        // of the designated values
                        $operator = '';
                        $value = array_filter(explode( ' ', $value));
                    }

                    $conditionsValue = $value;
                    $conditionsKey = "$modelName.$key $operator";
                }

                // add the new condition entry
                $conditions[trim($conditionsKey)] = $conditionsValue;
            }

            // if we have some criteria, add them in the sesion
            $this->owner->Session->write($sessionConditionsKey, $conditions);
            $this->owner->Session->write($sessionDataKey, $this->owner->data);
        }

        return $conditions;
    }

    private function extractOperator(&$input)
    {
        if (is_array($input))
            return '';

        $operator = strtoupper(strtok($input, ' '));

        if (in_array($operator, $this->SQL_OPERATORS)) {
            $opLength = strlen($operator);
            $inputLength = strlen($input);
            $input = trim(substr( $input, $opLength, $inputLength - $opLength));
        } else {
            $operator = '';
        }

        return $operator;
    }
}

Next modify your AppController to use the component by adding 'Qbe' to the $components array. An example would be:

    var $components = array(
        'RequestHandler',
        'Auth',
        'Session',
        'Qbe'
    );

Now crate a query form before the results table into your index view. Copying the form from the corresponding model's create or edit pages is usually enough to get you started.

Finally, modify the corresponding controller method to use the posted data in order to create search conditions. A typical example for a model named Product would be something like that:

    function index()
    {        
        $this->Product->recursive = 0;
        $conditions = $this->Qbe->getSearchConditions($this->Product->name);
        $products = $this->paginate('Product', $conditions);

        $this->set( 'products', $products);
        $this->prepareCombos();
    }

The prepareCombos() method is a simple private function I created by copying all the find)'list', ...) commands that the cake bake script created after the add() and edit() controller methods, so that all foreign key fields have correct value ranges, when the input form gets displayed. For more information and example code, see the previous post which presents the same functionality, by adding code to the AppController.

Tuesday, 28 June 2011

CakePHP: Turn an input form into a query form (Version 2)

CakePHP makes constructing input forms easy. The Form helper object's input() methods provides us with the necessary intelligence, to display and get input data from our users, then store it into the controller's $this->data property using the format required by the framework's Model->save() method, so they can eventually be written to the database, with no need for any elaborate code. Furthermore, given the fact that cake's cake script will bake all that standard code in a flash. it becomes a matter of minutes to create a full featured CRUD application, starting from just a good database design.

One thing I find missing from the baked code, is the ability to create input forms whose data will be used as search criteria -- in a QBE fashion -- in an index or display page. My solution requires us to convert the submitted data into a form that is compatible with the Model->find()'s $conditions parameter and then feed this to the Model->find() or the Controller->paginate() methods in order to limit the number of returned items.

As Richard pointed out during the first version of this post, cake offers the postConditions() method, which does this out of the box and the truth of the matter is that I had completely missed that when I started coding. After looking at the CakePHP code and comparing it with my approach, I can only say that with the code below, you don't have to worry about operators and providing complex parameters. The latest version will allow users to type their desired operator before the value in a QBE like fashion. The standard Cake approach gives more accurate control over the entire process, but I am not sure how to create a UI that will allow users to change operators dynamically .

So, to get started with my alternative version, the typical index() method, for a model named Product, using this approach will have to look somehow like this:

    function index()
    {        
        $this->Product->recursive = 0;
        $conditions = $this->getSearchConditions($this->Product->name);
        $products = $this->paginate('Product', $conditions);

        $this->set( 'products', $products);
        $this->prepareCombos();
    }

The important part here s obviously the $this->getSearchConditions() function that will get the controllers $data property and transform it into the $conditions array. The perfect place to put the code for that would be our application's controller.

class AppController extends Controller {
    const CONDITIONS_SESSION_KEY = 'SRCH_COND';
    const FORM_DATA_SESSION_KEY = 'SRCH_DATA';

    private $SQL_OPERATORS = array(
        'IN', '<>', '>=', '<=',
        '>', '<'        
    );

    /**
     * @name: getSearchConditions()
     * @access: protected
     * @author: Thanassis Bakalidis
     * @param : string $modelName name of the model to search controller data
     * Return an array to be used as search conditions in a Model's find 
     * method based on the controller's current data
     * @version: 1.4
     */
    protected function getSearchConditions($modelName = null)
    {
        if ($modelName == null)
            return null;

        // create speciffic keys for the model andcontroller
        $sessionConditionsKey = sprintf("%s-%s-%s",
                                self::CONDITIONS_SESSION_KEY,
                                $this->name,
                                $modelName
                            );
        $sessionDataKey = sprintf("%s-%s-%s",
                                self::FORM_DATA_SESSION_KEY,
                                $this->name,
                                $modelName
                            );

        if (empty($this->data)) {
            // attempt to read conditions from sesion
            $conditions = $this->Session->check($sessionConditionsKey)
                ? $this->Session->read($sessionConditionsKey)
                : array();
            $this->data = $this->Session->check($sessionDataKey)
                ? $this->Session->read($sessionDataKey)
                : array();
        } else {
            // we have posted data. Atempt to rebuild conditons
            // array
            $conditions = array();
            foreach( $this->data[$modelName] as $key => $value) {
                if (empty($value))
                    continue;

                $operator = $this->extractOperator($value);
                
                if (is_array($value)) {
                    // this can only be a date field

                    $month = $value['month'];
                    $day = $value['day'];
                    $year = $value['year'];

                    // We want all three variables to be numeric so we 'll check their
                    // concatenation. After all PHP numbers as just strings with digits
                    if (is_numeric($month.$day.$year) && checkdate( $month, $day, $year)) {
                        $conditionsKey ="$modelName.$key";
                        $conditionsValue = "$year-$month-$day";
                    } else
                        continue;                        
                } else {
                    // we have normal input check the operator given                    
                    if ($operator == '' && !is_numeric($value)) {
                        // turn '=' to 'LIKE' for non numeric data
                        // numeric data will be treated as if they
                        // have an wquals operator
                        $operator = 'LIKE';
                        $value = str_replace('*', '%',  $value);
                        $value = str_replace('?', '.',  $value);                        
                    } else if ($operator === 'IN') {
                        // we need to convert the input string to an aray
                        // of the designated values
                        $operator = '';
                        $value = array_filter(explode( ' ', $value));
                    } 
                    
                    $conditionsValue = $value;
                    $conditionsKey = "$modelName.$key $operator";                    
                }

                // add the new condition entry
                $conditions[trim($conditionsKey)] = $conditionsValue;
            }

            // if we have some criteria, add them in the sesion
            $this->Session->write($sessionConditionsKey, $conditions);
            $this->Session->write($sessionDataKey, $this->data);
        }

        return $conditions;
    }

    private function extractOperator(&$input)
    {     
        $operator = strtoupper((strtok($input, ' '));
        
        if (in_array($operator, $this->SQL_OPERATORS)) {
            $opLength = strlen($operator);
            $inputLength = strlen($input);
            $input = trim(substr( $input, $opLength, $inputLength - $opLength));                        
        } else {            
            $operator = '';
        }
        
        return $operator;
    }

This was the hard part. The next ones have to do with only copy and paste. So get the form that cake created from your add or edit views and copy it before the table in the index view page. You may possibly wish to remove some unwanted input fields and change the value of the $this->Form->end() function's parameter from "Submit" to "Search".

So we are almost there. One last stroke and we are ready. Remember the $this->prepareCombos(); last comand on the index action? Well, that is a simple private method I created by copying all the find)'list', ...) commands that the cake bake script created after the add() and edit() methods, so that all foreign key fields have correct value ranges, when the input form gets displayed. For my product's controller for example the function looks like this:

    private function prepareCombos()
    {
        $productTypes = $this->Product->ProductType->find('list');
        $productCategories = $this->Product->ProductCategory->find('list');
        $suppliers = $this->Product->Supplier->find(
                                                'list',
                                                array(
                                                    'order' => array(
                                                        'id' => 'asc'
                                                    )
                                                )
                                            );
        $qualities = $this->Product->Quality->find('list');        
        $this->set(compact('productTypes', 'productCategories', 'suppliers', 'qualities'));
    }

Notes

  • In case your model contains validation rules like range then the default input element that the form helper creates , will contain an appropriate maxlength attribute, that will not allow your users to type more than the maximum allowed characters for the field. Say, for example that you have a rule for a field named width, that looks like this
           'width' => array (
                'rule' => array('range', 1, 999),
                'message' => 'Width must be between 1 and 999 mm',
                'last' => true
           )
          
    ... then the actual HTML input element will have a maxlength attribute of 3. This is not going to make your query UI very usable, so make sure that the search form's echo $this->Form->input( ... statement provides an appropriate hardcoded value for maxlength.
  • The next thing to consider, is what will happen when our users provide wrong inputs, say they type =< 13 instead of >= 13. In that case the code -- as it is now -- will create an SQL statement of the form select ... where Model.Field LIKE '=< 13' , that will provide an empty result set, but is still valid SQL. Any workaround for this type of situation would make our code even more complicated than it is now, so I am not going to spend any more thought on this.
  • After going this far, I believe that the next reasonable thing would be to move the functionality into a new component, that will take away all the complexity from the AppController class, but this is probably going to be the subject of a forthcoming post.

Tuesday, 7 June 2011

CakePHP: Authenticating against Microsoft Active Directory

My work environment uses Windows. Everyone logs on to an Active Directory based domain and this is how security is managed. When you have web applications running on Linux and you need some sort of authentication service, then you can either implement your own user data store or you can use an exiting. The good news is that MS AD is actually an LDAPv3 server, so getting data to and from LDAP is not that difficult in the world of both PHP and Cake.

The following "howto" will list the steps I followed in order to perform authentication from the company's Windows 2003 servers. I will also explain one approach for deciding how to grant users admin privileges on a CakePHP application based on membership of a Windows user group. Many might argue that I am oversimplifying, but I believe that on most occasions, this will be more than adequate.

In order to get started, remember that building an authentication system on a CakePHP application starts with creating a User model. Only difference will be here that the model in question will not get its data from an ordinary database table, but rather from an LDAP data source.

Get the LDAP datasource component

So this brings as to the first issue, which is: Get a datasource to read LDAP data. Fortunately the bakery has just what we need. The actual article that contains the code I am still using is this.

Analogrithems, however, the author of the datasource code, is providing new updates on github accessible via this link. The truth is that I have yet to test the new version, so if you are to follow the tutorial better stick with the one in the bakery.

No matter the version you choose you will end up with a file named ldap_source.php, that you will need to place in your APP/models/datasources/ directory.

Set up a connection using the new datasource

The next thing that needs to be done is to create a new connection that will use the new datasource. Open APP/config/database.php and add a new entry looking more or less like this

    var $ldap = array (
        'datasource' => 'ldap',
         // list of active directory hosts
        'host' => array(
                    'ldap1.example.com',
                    'ldap2.example.com',
                    'ldap3.example.com'
                ),
        'port' => 389,
         // location (root) in LDAP tree to start searching 
        'basedn' => 'DC=example,DC=com',
         // user to authenticate on AD server with
        'login' => 'cn=LDAPBindUser,cn=Users,dc=example,dc=com',
        'password' => 'abABa@332',
        'database' => '',
        'tls'      => false,
        'version'  => 3
    );

Verify with your network administrator that LDAPBindUser in created on the correct OU and that the base DN is correct, according to where you placed the user account. (Needless to say that the user must have next to no privileges at all)

Set up the User model class

The next step is our user model. This should be like any model, you have except that it will be based on the $ldap database config, its primary key will be the LDAP dn attribute and that it will need no table.

class User extends AppModel {
    var $name = 'User';
    var $useDbConfig = 'ldap';

    var $primaryKey = 'dn';
    var $useTable = '';

    /**
     * return true if the userName is a member of the groupName
     * Active Directory group
     */
    function isMemberOf($userName, $groupName)
    {
        // trivial check for valid names
        if (empty($userName) || empty($groupName))
            return false;

        // locate the user record
        $userData = $this->find('first',
                                array(
                                    'conditions' => array(
                                        'samaccountname' => $userName
                                    )
                                )
                            );
        // no user by that name exists
        if (empty($userData))
            return false;

        // check if the userin question belongs to any groups
        if (!isset($userData['User']['memberof']))
            return false;

        // search all groups that our user if a meber
        $groups = $userData['User']['memberof'];
        foreach( $groups as $index => $group)
            if (strpos( $group, $groupName) != false)
                return true;

        return false;
    }
}

We have also added a member function which allows us to determine if a user-name belongs to an Active directory group that will be very handy when checking for application privileges later on.

Set up the LdapAuth Component

And finally the "auth" part. The standard CakePHP Auth component will not work for us so we need a specialized version that thanks to analogthemes again is accessible from his web site through here. Just place ldap_auth.php to your APP/controllers/components/ directory and change your AppController so that it uses LdapAuth.

This however is not enough. There is one little thing that needs to be changed in the LdapAuth code, so that authenticating in AD works: Locate the login() function in line 153 of ldao_auth.php and change line 156 so that it looks like this:

     ...

     function login($uid, $password) {
        $this->__setDefaults();
        $this->_loggedIn = false;
        // $dn = $this->getDn('uid', $uid);
        $dn = $this->getDn('samaccountname', $uid);
        $loginResult = $this->ldapauth($dn, $password); 
        ...

My simple approach to security says that an application has two types of users. Readers (i.e. everyone) and writers (admins only). We set the administrative users to be the members of a specific group in the active directory server. That way our application controller looks like this :

class AppController extends Controller {
    const ADMIN_KEY  = 'myApp-Admin';
    const AUTH_GROUP = 'MyApp-Admins';

    var $components = array('RequestHandler', 'LdapAuth', 'Session');
    var $helpers = array(
                        'Form', 'Html', 'Locale', 'Session',
                        'Js' => array('Jquery')
                    );
    var $isAdmin;

    function beforeFilter()
    {
        // pr($this->referer());
        $this->isAdmin = false;
        $this->LdapAuth->authorize = 'controller';
        $this->LdapAuth->allowedActions = array(
                                            'index',
                                            'view',
                                            'details'
                                        );

        $this->LdapAuth->loginError = "Invalid credentials";
        $this->LdapAuth->authError  = "Not authorized!";

        $userInfo = $this->LdapAuth->user();

        if (!empty($userInfo)) {
            $user = $userInfo['User'];
            // setup display username and user's full name
            $this->set('loggedInUser', $user['samaccountname']);
            $this->set('loggedInFullName', $user['cn']);

            // if we already have the admin role stored in the session
            if ($this->Session->check(self::ADMIN_KEY))
                $this->isAdmin = $this->Session->read(self::ADMIN_KEY);
            else {
                // determine the admin role from wheither the current 
                // user is a member of the AUTH group
                $this->LoadModel('User');
                $this->isAdmin = $this->User->isMemberOf(
                                                $user['samaccountname'],
                                                self::AUTH_GROUP);
                $this->Session->write(self::ADMIN_KEY, $this->isAdmin);
            }
        } else {
            $this->Session->delete(self::ADMIN_KEY);
        }

        $this->set('admin', $this->isAdmin);
    }

    function isAuthorized()
    {
        // Only administrators have access to the CUD actions
        if ($this->action == 'delete' ||
            $this->action == 'edit' ||
            $this->action == 'add')
          return $this->isAdmin;

        return true;                
    }
}

That way, everyone can access our view, details and index actions of all the application controllers, but will need to enter the user name and password of an ADMIN_GROUP member to gain access to the rest of our controller methods. This tutorial however is has gone long enough. To finish it, please remember to create your user controller and you login user view ...

Tuesday, 17 May 2011

CakePHP and FreeTDS on CentOS 5

We are almost finished with a project using CakePHP 1.3.8 running on a CentOS 5 box that accesses a Microsoft SQL Server 2008 database, using PHP 5.3.6-4 and the freetds driver, all available from Remi's repository.

This is a report of all the little problems we faced and the solutions we came up with :

  1. The first thing that hit us, was he encoding of the saved data. Although we used NVARCHAR columns for all character fields, we ended up seeing gibberish inside the SQL Server Management Studio, whenever we had to enter anything that was not Latin. Both the server and the application are using UTF-8 locales and data entered via Cake would be returned correctly, however there was no way to get the MSSQL Management Studio to display UTF-8 data.
    After some dispute about whether or not to leave things as they are we decided to utilize a behavior that converted data to and from different character sets before storing and retrieving them from the database. The relevant post from back 2009 is accessible via the following link.
    This approach kept almost everyone happy.
  2. Second issue was the length of the Model's visual fields. I have blogged about this a few days ago and it turns out that the maximum number of characters allowed to a virtual field name is 14.
  3. And finally the stored procedure issue. Up until now, we have been unable to properly call a T-SQL stored procedure using mssql_init() and mssql-bind(). The only workarround for doing the job is to use the mssql_query() function and execute an "EXEC ... " query.
    The code we use -- as a Model method -- is more or less like this :
        /**
         * Execute the T-SQL stored procedure named "dbo.storedProcName"
         * and return execution status.
         *
         * NOTE: if the stored proc uses the T-SQL RAISERROR statement
         *       to trigger some sort of error condition, then
         *       mssql_query still returns true but the 
         *       mssql_get_last_message() will contain the actual error
         *       text that will be returned to the caller.
         */
        function callStoredProc($procParam, &$errorMessage = '')
        {
            // get the low level database connection from the model data
            $conn = $this->getDataSource()->connection;

            // execute a query that calls the stored proc
            $sqlResult = mssql_query(
                          " EXEC dbo.storedProcName
                            @procParam = $procParam;",
                          $conn
                        );

            $errorMessage = mssql_get_last_message();
                
            if ($sqlResult == false) {
                // something went wrong 
                $result = false;              
            } else {                    
                // stay on the safe side            
                if (is_resource($sqlResult))
                    mssql_free_result($sqlResult);
                // if the stored procedure error message is empty then all
                // is well
                $result = $errorMessage == '' ? true : false;
            }

            // return success
            return $result;
        }

As always, I try to update the list if anything new comes up.

Tuesday, 3 May 2011

CakePHP: Dynamicaly disabling links

Sometimes we get buried so deeply into ... nuclear science that we forget that after all CakePHP is just PHP, the resulting pages are just HTML and you don't need JQuery to disable a link. Setting it's onlick event to return false, usually does it.

The problem was simple: Given a list of records, (arrivals) selectively enable an action link for each record, (create a purchase order in our case). based on a record attribute (that is whether the purchase order has already been created).

Now, that is now so hard. Well, if you already have something like :

<?php echo $this->Html->link(
                    $this->Html->image('actions/upload.png'),
                    array(
                          'action' => 'createPurchasOorder',
                          $arrival['Arrival']['id']
                    ),
                    array(
                      'escape' => false,
                      'title' => 'Create purchase order for the arrival.'
                    )
                ); ?> 

inside a PHP foreach loop, then it's not that difficult to transform it into :

<?php 
         $canCreatePO = empty($arrival['Arrival']['purchase_order_code']);
         echo $this->Html->link(
                    $this->Html->image('actions/upload.png'),
                    array(
                          'action' => 'createPurchasOorder',
                          $arrival['Arrival']['id']
                    ),
                    array(
                      'escape' => false,
                      'onclick' => $canCreatePO 
                              ? 'return true;'
                              : 'return false',
                      'title' => $canCreatePO 
                              ? 'Create purchase order for the arrival.'
                              : 'Purchase order has already been created.'
                    )
                ); ?> 

So, why the hell did it take me an entire morning to figure this out....

Thursday, 21 April 2011

CakePHP: Length of a virtual field's name

Perhaps the fact that I am accessing an MS SQL 2008 server from Linux using the freetds driver could be the cause of my troubles.

What I did find out today, is that when using the $virualFields property of a model the size of the virtual field's name, cannot exceed 14 characters.

My setup here is: CakePHP 1.3.8, running on CentOS 5.6 x86_64 using PHP 5.3.6-3 from Remi's repository.

And here is my case: My Model looks pretty much like this

class ArrivalPackage extends AppModel {
 var $name = 'ArrivalPackage';
 var $belongsTo = array('Arrival', 'Product', 'PackageType');
 var $hasMany = 'PackageLine';

 var $virtualFields = array(
   'reserved_date_gr' => 'CONVERT(NVARCHAR(10), ArrivalPackage.reserved_date, 105)',  //  not good 
   'loaded_date_gr'   => 'CONVERT(NVARCHAR(10), ArrivalPackage.loaded_date, 105)',
   'sold_date_gr'     => 'CONVERT(NVARCHAR(10), ArrivalPackage.sold_date, 105)'
 );
}

After I baked a view and controller for PackageLines I added a pr($packageLine); on the top of my view.ctp view file. The result was :

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
    
    [0] => Array
        (
            [ArrivalPackage__reserved_date_] => 18-04-2011
        )
    
)

Only after I shortened the name of the reserved_date_gr field to rsvd_date_gr, did I get my expected

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [resvd_date_gr] => 18-04-2011
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
)

Funny thing is that the actual sql statement produced by Cake, no matter what you name the field is valid for SQL Server, still unless the field name becomes smaller than 14 characters things don't work as you would expect them.

Tuesday, 19 April 2011

CakePHP: Storing paging info in the Session

Warning: This post applies to CakePHP 1.3. If you are using CakePHP 2, then visit the updated version available through here.

There is no way you can miss it. Sooner or later you 'll end up having baked your controllers and views only to discover that when you edit a record from the 4th page and press submit on the edit page, much to your annoyance you end up on page one. Needless to say that whatever sort order was there is now gone with the wind.

I have googled a bit and found out the following article in Stack Overflow. The solutions provided over there seemed rather complicated to me, so I decided to craft my own.

Now here is the deal: From what I have seen in cake 1.3, paging is controlled by the value of three parameters: page, sort and direction. All we need to do is provide a way to store them in the session and then have the controller's redirect method inject these back into the URL parameters when they are not present.

<?php
     
class AppController extends Controller {
    const SESSION_KEY = 'PagingInfo.%s.%s';
        
    public $components = array('Session');            

    protected function savePagingInfo()
    {
        // create an array to hold the paging info
        $argsToSave = array();

        // remove paging info from passed args
        foreach( $this->passedArgs as $key => $value)
            if ($key == 'page' || $key == 'sort' || $key == 'direction')
                $argsToSave[$key] = $value;

        // abort if any paging info was found
        $sessionKey = $this->buildSessionKey();
        if (!empty($argsToSave)) {
            $this->Session->write( $sessionKey, $argsToSave);
            return;
        }
        
        // no paging info let's see if we have something in the sesion
        if ($this->Session->check($sessionKey)) {
                $pagingInfo = $this->Session->read($sessionKey);
                $this->passedArgs = array_merge( $this->passedArgs, $pagingInfo);
        }
    }
                        
    private function buildSessionKey($action = NULL)
    {
        if (empty($action))
            $action = $this->action;
            
        return sprintf( self::SESSION_KEY, $this->name, $action);
    }
}
     
?>    

This approach may be simplistic but in my case it worked. All I had to was call $this->savePagingInfo(); from my index() action and then let the rest of the baked code do its work.

Note

And one last comment, there are many cases that you need paging during display of master detail record sets. In cases like these savePagingInfo() will save paging status for the detail records matching the current master and when the master changes, the paging in the session becomes invalid. My simple solution to this is to add a 'page' => 1 entry in the $url params pointing to a master record change. With the design of the application I am currently working on this proves just good enough.

Wednesday, 20 January 2010

CakePHP: Calling Oracle stored procedures and functions

Our testing with Cake is almost complete. We have been able to read and write data correctly in our non UTF-8 Oracle database, managed to communicate with SAP, played enough with AJAX, so our users will not complain when dealing with tree like data and even managed to get authenticated by our MS-Windows 2003 active directory servers. So the last question left was how does one call an Oracle PL/SQL stored procedure or function from CakePHP ?

A little bit of googling and a little bit of digging into the CakePHP code revealed the following: The most common approach to calling stored procedures and functions is to create a member function in your model class and set up the call from there. In the simple case of calling a stored procedure with IN parameters only, the Model's query() method can be used to perform the actual call via the CALL PROC_NAME( ... ) SQL statement. The usual approach is to create a model method like this :

class MyModel extends AppModel {
    var $name = "MyModel";

    ...
    function callStoredProc( $param1, $param2)
    {
        $this->query("CALL my_stored_proc( $param1, $param2");
    }
    ...
}

If however you need to get data in and out of Oracle then you have to get your hands dirty and set up the call using low level oci_* functions. A simple example will clarify everything.

Let us suppose that you library users require that you display the average number of pages of the books stored in your library. A simple PL/SQL function to return this would probably be something like this: /p>

CREATE OR REPLACE FUNCTION average_book_pages RETURN NUMBER
IS
   page_avg NUMBER;   
BEGIN
   SELECT avg( num_pages) 
      INTO page_avg
      FROM books;
   RETURN page_avg;   
END AVERAGE_BOOK_PAGES;

The next thing to do would be to create a getAvergaeBookPages() function in our Book model class :

<?php
class Book extends AppModel {
    var $name = 'Book';
    var $belongsTo = ...
    var $validate = array( ...

    function getAverageBookPages()
    {
        // every model has a datasource
        $dataSource = $this->getDataSource();
        // and every datasource has a  database connection handle
        $connection = $dataSource->connection;
        if (!$connection)
            return -1;

        // from now you need this Oracle specific approach 
        $statement = oci_parse( $connection,
                "begin :ret := AVERAGE_BOOK_PAGES(); end;");
        oci_bind_by_name($statement, ':ret', $r, 100);
        oci_execute($statement);
        return $r;
    }
}
?>

The last parameter of oci_bind_by_name is the number of bytes to allocate for returning the string representation of the bind variable. Just make sure that you allocate enough memory for that. My test data yield an average of 762,666666666666666666666666666666666667 pages per book (Thank you Mr. Minasi) and so oci_execute kept giving me ORA-06502: PL/SQL: numeric or value error: character string buffer too small until I raised the value to 100.

So that does it. Now calling this from you controller code is as easy as :

<?php
class BooksController extends AppController {
    var $name = 'Books';

    ...

    function index()
    {
        $this->Book->recursive = 0;
        $this->set('books', $this->paginate());
        $this->set('averagePages', $this->Book->getAverageBookPages());
    }

    ...
}