University of Oregon

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.

Aegir: Adding external databases / injecting data in the settings.php file

I’ve been using Aegir a lot lately and it’s nice to find built-in work-arounds when you need one.
I needed to add an external database to my drupal site. As it turns out you can create a local.settings.php file in your site folder, and extend your settings.php file. See Injecting into settings.php for a full description.

Aegir manages the settings.php file. So any changes you make there will be overwritten. But we have control over the local.settings.php file.
As an example, I needed to include an external database that I incorporate into my site via TableWizard. To achieve that I created this local.settings.php file:

[code:php] ‘mysqli’,
‘database’ => ‘hr_ee_forms’,
‘username’ => ‘hr_reader’,
‘password’ => ‘******’,
‘host’ => ‘hrpub’,
‘port’ => ‘3306’,
$db_url[‘uo_ee_forms’] = $databases[‘uo_ee_forms’][‘default’][‘driver’] . ‘://’ . $databases[‘uo_ee_forms’][‘default’][‘username’] . ‘:’ . $databases[‘uo_ee_forms’][‘default’][‘password’] . ‘@’ . $databases[‘uo_ee_forms’][‘default’][‘host’] . ‘:’ . $databases[‘uo_ee_forms’][‘default’][‘port’] . ‘/’. $databases[‘uo_ee_forms’][‘default’][‘database’];

$db_prefix = array(
‘default’ => ”,
‘org_level’ => ”,/*org_chart*/
‘uo_oa’ => ”/*uo_ee_forms*/

The above code adds an external DB that TableWizard can read, providing access to the data via views.

Note that I had to ‘hard code’ the values. We can’t take advantage of the stored $_server vars that Aegir keeps in the vhost file. For ex:

$databases[‘default’][‘default’] = array(
‘driver’ => $_SERVER[‘db_type’],
‘database’ => $_SERVER[‘db_name’],
‘username’ => $_SERVER[‘db_user’],
‘password’ => $_SERVER[‘db_passwd’],
‘host’ => $_SERVER[‘db_host’],
‘port’ => $_SERVER[‘db_port’],

Those are available in the settings.php file but unset before the our local.settings.php file is run.

There’s also an option for a file that can apply settings to all your sites.