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.

Local hosting of custom drupal modules

I have a development site where I’ve started hosting my custom drupal modules. This way I can share install them across my sites and post updates to a central location.
The site re-creates the module project pages on drupal so I can use my bookmarklet for updates and installs.

So far it seems to be working.
The site is an openAtrium install and I created a book called: projects in my admin user group. Modules are just child books of that project node. Then I created a custom content type ‘uploads’ to house the attachments. The magic happens in a view that checks for any children book nodes that are content type: uploads posts them in a table.