Custom Drupal 6 Module (Hack) to filter TableWizard Data by Dates in Views 2

I use Drupal to do some reporting on non-Drupal data using Views and TableWizard. It works pretty well but there are some serious limitations with relationships and filters.
I use a number of work-arounds for these limitations including referencing Views within Views, custom PHP and jQuery hacks via Views Customfield php fields and now a custom module that allows me to filter on dates.

My recent goal was to filter some external data by a date range. I can filter by my external datetime columns (as text) but Views doesn’t recognize them as valid date fields so Views date filters are unavailable.
So rather than rebuild the entire report in custom php I choose to manipulate the views query and filters. The result is a module/hack that converts the external datetime data to the Drupal friendly date format and then updates the query to filter by those dates.

I’ve been using the module for about a month and it works great. In its current state, the module uses some static code that specifies a view and display to act on and some where clauses to replace.

To work correctly, it requires that the Views display include two date filters (start and end date) both exposed and set to ‘contains’.
The date variable is formatted and then the matching ‘datetime contains’ where clauses (ex:

(datetime.dt_end_date) LIKE ('%%%s%%')

) are replaced with ‘date greater / less than’ clauses (ex:

(datetime.dt_end_date) <= ('%s')

).
I need to check and validate the format of date data several times in the module. So to keep the code clean and non-redundant I added couple of functions: strIsDate($string), dateValidates($dateVal), which you'll see below.
Finally the existing filter fields are changed to make them date_popup fields.

Here's a quick look at what the form looks like with the altered fields:
Here's a look at the start and end date fields
Nice, clean and just what you'd expect when filtering on dates.

I prefer to keep all my views additions (PHP, jQuery, CSS) inside the view (in the header, footer or views_customfield), but that's not possible in this case. So I added this text to the views header (input format: php):

<?php /*
The query for this view and display (page_4) is being overridden by the tw_session_w_user_count_report_override.module.
This depends on two filters: datetime dt_start_date and dt_end_date being exposed and set to 'contains'.
*/ ?>

Dissecting the module

Thirst things first, I declare the views api version.

<?php
/**
 * Implementation of hook_views_api().
 */
function tw_session_w_user_count_report_override_views_api() {
  return array(
  'api' => '2',
);
}//end function tw_session_w_user_count_report_override_views_api

Then I hook views_query_alter

<?
/**
 * Replace the date 'contains' filters with a date range filter
 */
function tw_session_w_user_count_report_override_views_query_alter(&$view, &$query) {
if($view->name == 'session_w_user_count'){
  if($view->current_display == "page_4"){

You can see I've hard-coded the view name and display here.

Next comes the date validation functions

<?
if(!function_exists('strIsDate')){
  function strIsDate($string){
    $t = strtotime($string);
    $m = date('m',$t);
    $d = date('d',$t);
    $y = date('Y',$t);
    return checkdate ($m, $d, $y);
  }//end function strIsDate($string)
}//end if(!function_exists('strIsDate'))

if(!function_exists('dateValidates')){
  function dateValidates($dateVal){
    $t = $dateVal;
    $m = date('m',$t);
    $d = date('d',$t);
    $y = date('Y',$t);
    return checkdate ($m, $d, $y);
  }//end function dateValidates($string)
}//end if(!function_exists('dateValidates'))
&#91;/code&#93;</div>

<h3>Looking at the existing query</h3>
Now I dig into the where clauses in the query and fiddle around a bit.
During testing I output the query to the screen:
<div class="tallcode"><?
drupal_set_message('<h2>Query</h2><pre>' . print_r($query,true) . '</pre>');
### Example output ###
[where] => Array (
  [0] => Array
    (
      [clauses] => Array
        (
          [0] => (registration.reg_status) != ('%s')
          [1] => (session.sess_status) != ('%s')
          [2] => (datetime.dt_start_date) LIKE ('%%%s%%')  # I put in start_date and end_date contains filters to replace with the combined datetime (> <) filter
          &#91;3&#93; => (datetime.dt_end_date) LIKE ('%%%s%%')
        )

      [args] => Array
        (
          [0] => removed
          [1] => cancelled
          [2] => 1/1/2012
          [3] => 7/26/2012
        )

That gives me what I need to replace the contains (LIKE) query with the greater or less than query as well as formatting the datetime values appropriately.

Manipulating the query array

Now I search for the first where clause I want to act on (start_date) and capture it's array key. Then I replace that where clause with my own. I re-use the array key to act on the correlating value in the args array.
The arg value is then validated and formatted.
You'll see in the code block below that I've left in all my testing feedback (drupal_set_message). Those are all commented out in the live code.

That whole process is repeated for the end_date.

Here's the code:

<?
### Notes ###
/*
Check the where clauses for datetime.dt_end_date using that array &#91;id&#93; ($endDateId) repace the $query->where[0]['clauses'][$endDateId] with the datetime lookup. Convert the $query->where[0]['args'][$endDateId] to datetime as well.
//DATE_ADD(end_date)
*/
### /Notes ###
//start date:
$startKey=false;
$startKey = array_search("(datetime.dt_start_date) LIKE ('%%%s%%')", $query->where[0]['clauses']);
drupal_set_message('find: $startKey: '.$startKey);// . ' in ' . print_r($query->where[0]['clauses']) );
drupal_set_message('<pre>' . print_r($query->where[0]['clauses'],true) . '</pre>');
drupal_set_message('<pre>' . print_r($query->where[0]['args'],true) . '</pre>');
drupal_set_message($query->where[0]['clauses'][$startKey]);
if($startKey !== false){
  //if there are args present
  if($query->where[0]['args'][$startKey]){
    $query->where[0]['clauses'][$startKey] = "(datetime.dt_start_date) >= ('%s')";
    drupal_set_message('StartKey Val: '.$query->where[0]['clauses'][$startKey]);
    //update arg to the correct format
    $startTime = $query->where[0]['args'][$startKey];
    drupal_set_message('strIsDate?: '.strIsDate( $startTime ));
    if( !strIsDate( $query->where[0]['args'][$startKey] )){ //not a full date? then add time
      $startTime = date( 'Y-m-d', strtotime($query->where[0]['args'][$startKey] . ' 00:00:00' ));
    } else {
      $startTime = date('Y-m-d', strtotime($query->where[0]['args'][$startKey] ));
    }//end if( !strIsDate(...) )
    drupal_set_message('dateValidates: '.dateValidates($startTime));
    drupal_set_message('$startTime: '.$startTime);
    $query->where[0]['args'][$startKey] = $startTime;
    drupal_set_message('$query->where[0]['args'][$startKey] = ' .$startTime);//date( 'Y-m-d', $startTime ));
  }//end if($query->where[0]['args'][$startKey])
}//end if($startKey !== false)

And finally here's the full code that is currently in use:

<?php
/**
 * Implementation of hook_views_api().
 */
function tw_session_w_user_count_report_override_views_api() {
  return array(
    'api' => '2',
  );
}//end function tw_session_w_user_count_report_override_views_api

/**
 * Replace the date 'contains' filters with a date range filter
 */
function tw_session_w_user_count_report_override_views_query_alter(&$view, &$query) {
  if($view->name == 'session_w_user_count'){
    if($view->current_display == "page_4"){

      if(!function_exists('strIsDate')){
        function strIsDate($string){
          $t = strtotime($string);
          $m = date('m',$t);
          $d = date('d',$t);
          $y = date('Y',$t);
          return checkdate ($m, $d, $y);
        }//end function strIsDate($string)
      }//end if(!function_exists('strIsDate'))

      if(!function_exists('dateValidates')){
        function dateValidates($dateVal){
          $t = $dateVal;
          $m = date('m',$t);
          $d = date('d',$t);
          $y = date('Y',$t);
          return checkdate ($m, $d, $y);
        }//end function dateValidates($string)
      }//end if(!function_exists('dateValidates'))

      ### Testing ###
      //drupal_set_message('<h2>View</h2><pre>' . print_r($view,true) . '</pre>');
      //drupal_set_message('<h2>Query</h2><pre>' . print_r($query,true) . '</pre>');
      /*### Example output ###
      [where] => Array (
        [0] => Array
          (
            [clauses] => Array
              (
                [0] => (registration.reg_status) != ('%s')
                [1] => (session.sess_status) != ('%s')
                [2] => (datetime.dt_start_date) LIKE ('%%%s%%')  # I put in start_date and end_date contains filters to replace with the combined datetime (> <) filter
                &#91;3&#93; => (datetime.dt_end_date) LIKE ('%%%s%%')
              )

            [args] => Array
              (
                [0] => removed
                [1] => cancelled
                [2] => 1/1/2012
                [3] => 7/26/2012
              )
      */
      ### /Testing ###
      ### Notes ###
      /*
      Check the where clauses for datetime.dt_end_date using that array [id] ($endDateId) repace the $query->where[0]['clauses'][$endDateId] with the datetime lookup. Convert the $query->where[0]['args'][$endDateId] to datetime as well.
      //DATE_ADD(end_date)
      */
      ### /Notes ###
      //start date:
      $startKey=false;
      $startKey = array_search("(datetime.dt_start_date) LIKE ('%%%s%%')", $query->where[0]['clauses']);
      //drupal_set_message('find: $startKey: '.$startKey);// . ' in ' . print_r($query->where[0]['clauses']) );
      //drupal_set_message('<pre>' . print_r($query->where[0]['clauses'],true) . '</pre>');
      //drupal_set_message('<pre>' . print_r($query->where[0]['args'],true) . '</pre>');
      //drupal_set_message($query->where[0]['clauses'][$startKey]);
      if($startKey !== false){
        //if there are args present
        if($query->where[0]['args'][$startKey]){
          $query->where[0]['clauses'][$startKey] = "(datetime.dt_start_date) >= ('%s')";
          //drupal_set_message('StartKey Val: '.$query->where[0]['clauses'][$startKey]);
          //update arg to the correct format
          $startTime = $query->where[0]['args'][$startKey];
          //drupal_set_message('strIsDate?: '.strIsDate( $startTime ));
          if( !strIsDate( $query->where[0]['args'][$startKey] )){ //not a full date? then add time
            $startTime = date( 'Y-m-d', strtotime($query->where[0]['args'][$startKey] . ' 00:00:00' ));
          } else {
            $startTime = date('Y-m-d', strtotime($query->where[0]['args'][$startKey] ));
          }//end if( !strIsDate(...) )
          //drupal_set_message('dateValidates: '.dateValidates($startTime));
          /*
          if(!dateValidates($startTime)){

          }
          */
          //drupal_set_message('$startTime: '.$startTime);
          $query->where[0]['args'][$startKey] = $startTime;
          //drupal_set_message('$query->where[0]['args'][$startKey] = ' .$startTime);//date( 'Y-m-d', $startTime ));
        }//end if($query->where[0]['args'][$startKey])
      }//end if($startKey !== false)
      //End date:
      $endKey=false;
      $endKey = array_search("(datetime.dt_end_date) LIKE ('%%%s%%')", $query->where[0]['clauses']);
      //drupal_set_message('find: $endKey: '.$endKey);// . ' in ' . print_r($query->where[0]['clauses']) );
      //drupal_set_message('<pre>' . print_r($query->where[0]['clauses'],true) . '</pre>');
      //drupal_set_message($query->where[0]['clauses'][$endKey]);
      if($endKey !== false){
        //if there are args present
        if($query->where[0]['args'][$endKey]){
          $query->where[0]['clauses'][$endKey] = "(datetime.dt_end_date) <= ('%s')";
          //drupal_set_message('endKey Val: '.$query->where[0]['clauses'][$endKey]);
          //update arg to the correct format
          $endTime = $query->where[0]['args'][$endKey];
          //drupal_set_message('strIsDate?: '.strIsDate( $endTime ));
          if( !strIsDate( $query->where[0]['args'][$endKey] )){ //not a full date? then add time
            $endTime = date( 'Y-m-d', strtotime($query->where[0]['args'][$endKey] . ' 00:00:00' ));
          } else {
            $endTime = date('Y-m-d', strtotime($query->where[0]['args'][$endKey] ));
          }//end if( !strIsDate(...) )
          //drupal_set_message('dateValidates: '.dateValidates($endTime));
          /*
          if(!dateValidates($endTime)){

          }
          */
          //drupal_set_message('$endTime: '.$endTime);
          $query->where[0]['args'][$endKey] = $endTime; //date( 'y-m-d', $endTime );
          //drupal_set_message('$query->where[0]['args'][$endKey] = ' .$endTime);//date( 'Y-m-d', $endTime ));
        }//end if($query->where[0]['args'][$endKey])
      }//end if($endKey !== false)
      //drupal_set_message('Query: <pre>' . print_r($query,true) . '</pre>');
    }//end if($view->current_display == "page_4")
  }//end if($view->name == 'tw_session_w_user_count')
}//end function tw_session_w_user_count_report_override_views_query_alter


/**
 * Update the form to make the fields date picker enabled
 */
/*ex: //from http://www.konordo.com/blog/how-use-datepicker-pop-form-field-custom-module#comment-415989658
$form['dt_start_date'] = array(
  '#title' => t('select the date booking to'),
  '#type' => 'date_popup',
  '#description' => t('Please select the date to which you want to book the resource type'),
  '#required' => TRUE,
  '#date_format' => 'Y-m-d h:i A',
  '#date_year_range' => '-0:+1',
  '#default_value' => empty($date)? '' : date('Y-m-d H:i:s', $date),
);
*/
//thanks to http://drupal.org/node/502824#comment-4342322

function tw_session_w_user_count_report_override_form_alter(&$form, &$form_state, $form_id){
  //drupal_set_message($form_id);
  if($form_id == 'views_exposed_form'){
    //drupal_set_message($form['#id']);
    //session_w_user_count
    if($form['#id'] == 'views-exposed-form-session-w-user-count-page-4'){
      //drupal_set_message('<pre>' . print_r($form,true) . '</pre>');
      $form['dt_start_date']['#type'] = 'date_popup';
      $form['dt_start_date']['#date_format'] = 'm/d/Y';
      $form['dt_start_date']['#date_year_range'] = '-3:+1';
      //$form['dt_start_date']['default_value'] = empty($date)? '' : date('Y-m-d', $date);

      $form['dt_end_date']['#type'] = 'date_popup';
      $form['dt_end_date']['#date_format'] = 'm/d/Y';
      $form['dt_end_date']['#date_year_range'] = '-3:+1';
      //$form['dt_end_date']['default_value'] = empty($date)? '' : date('Y-m-d', $date);

    }//end if($form['#id'] == 'views-exposed-form-session-w-user-count-page-4')
  }//end if($form_id == 'views_exposed_form')
}//end function tw_session_w_user_count_report_override_form_alter
Leave a Comment