Views2 date arguments not working

[jalama]

I noticed that the date arguments are not working in views2.  The error is the where part of the SQL statement has no field ie WHERE = '2009-08-01' in lieu of WHERE start_date = '2009-08-01'.  All the date arguments call the views_handler_argument_date which requires more pre-formatting before it can be executed.

I put together a handler, pasted in below, based on the node views date handler.  I would like to clean up the code a bit by minimizing the # of distinct handlers a little before submitting a patch.  Currently, you can expose 6 arguments per field, full date (YYYYMMDD), Year Month (YYYYMM), Year (YYYY), Day (DD), Month (MM), Week (WW).  I think I can combine the first 3, or at least the first 2, into one handler that determines which parameter is being passed into the argument based on the length of the argument's string.

If I'm correct and I didn't miss something.  Do you think it makes sense to have the date argument in all 18 date fields exposed in the views2 module? I don't mind putting together the code for all 18, but think it may be a bit of overkill.  The fields that seem the biggest priority are the event dates (start_date, end date), birth date, membership dates (start & end and maybe renewal dates), activity dates?

current handler code I have in file called drupal/modules/views/civicrm/views_handler_argument_civicrm_dates_various.inc

Code: [Select]

<?php
// $Id: views_handler_argument_dates_various.inc,v 1.4 2009/01/08 00:25:19 merlinofchaos Exp $
/**
 * Argument handler for a full date (CCYYMMDD)
 */
class views_handler_argument_civicrm_fulldate extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->format = 'F j, Y';
    $this->arg_format = 'Ymd';
    $this->formula = views_date_sql_format($this->arg_format, "***table***.$this->real_field", "datetime");
  }

  /**
   * Provide a link to the next level of the view
   */
  function summary_name($data) {
    $created = $data->{$this->name_alias};
    return format_date(strtotime($created), 'custom', $this->format, 0);
  }

  /**
   * Provide a link to the next level of the view
   */
  function title() {
    return format_date(strtotime($this->argument), 'custom', $this->format, 0);
  }
}

/**
 * Argument handler for a year (CCYY)
 */
class views_handler_argument_civicrm_year extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->arg_format = 'Y';
    $this->formula = views_date_sql_extract('YEAR', "***table***.$this->real_field","datetime");
  }
}

/**
 * Argument handler for a year plus month (CCYYMM)
 */
class views_handler_argument_civicrm_year_month extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->format = 'F Y';
    $this->arg_format = 'Ym';
    $this->formula = views_date_sql_format($this->arg_format, "***table***.$this->real_field","datetime");
  }

  /**
   * Provide a link to the next level of the view
   */
  function summary_name($data) {
    $created = $data->{$this->name_alias};
    return format_date(strtotime($created . "15"), 'custom', $this->format, 0);
  }

  /**
   * Provide a link to the next level of the view
   */
  function title() {
    return format_date(strtotime($this->argument . "15"), 'custom', $this->format, 0);
  }
}

/**
 * Argument handler for a month (MM)
 */
class views_handler_argument_civicrm_month extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->formula = views_date_sql_extract('MONTH', "***table***.$this->real_field","datetime");
    $this->format = 'F';
    $this->arg_format = 'm';
  }

  /**
   * Provide a link to the next level of the view
   */
  function summary_name($data) {
    $month = str_pad($data->{$this->name_alias}, 2, '0', STR_PAD_LEFT);
    return format_date(strtotime("2005" . $month . "15"), 'custom', $this->format);
  }

  /**
   * Provide a link to the next level of the view
   */
  function title() {
    $month = str_pad($this->argument, 2, '0', STR_PAD_LEFT);
    return format_date(strtotime("2005" . $month . "15"), 'custom', $this->format, 0);
  }

  function summary_argument($data) {
    // Make sure the argument contains leading zeroes.
    return str_pad($data->{$this->base_alias}, 2, '0', STR_PAD_LEFT);
  }
}

/**
 * Argument handler for a day (DD)
 */
class views_handler_argument_civicrm_day extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->formula = views_date_sql_extract('DAY', "***table***.$this->real_field","datetime");
    $this->format = 'j';
    $this->arg_format = 'd';
  }

  /**
   * Provide a link to the next level of the view
   */
  function summary_name($data) {
    $day = str_pad($data->{$this->name_alias}, 2, '0', STR_PAD_LEFT);
    return format_date(strtotime("2005" . "05" . $day), 'custom', $this->format, 0);
  }

  /**
   * Provide a link to the next level of the view
   */
  function title() {
    $day = str_pad($this->argument, 2, '0', STR_PAD_LEFT);
    return format_date(strtotime("2005" . "05" . $day), 'custom', $this->format, 0);
  }

  function summary_argument($data) {
    // Make sure the argument contains leading zeroes.
    return str_pad($data->{$this->base_alias}, 2, '0', STR_PAD_LEFT);
  }
}

/**
 * Argument handler for a week.
 */
class views_handler_argument_civicrm_week extends views_handler_argument_date {
  /**
   * Constructor implementation
   */
  function construct() {
    parent::construct();
    $this->arg_format = 'w';
    $this->formula = views_date_sql_extract('WEEK', "***table***.$this->real_field","datetime");
  }

  /**
   * Provide a link to the next level of the view
   */
  function summary_name($data) {
    $created = $data->{$this->name_alias};
    return t('Week @week', array('@week' => $created));
  }
}


[Eileen]

Hey - great to see someone sorting this out. In addition to the obvious start and end dates for events the other dates that would be really useful to us are profile dates created as a multi field. I want to use this to create a set of dates against each funding organisation relating to their grant rounds

[jalama]

If I'm not mistaken custom fields are supported by this function, civicrm_views_add_fields, which has no argument handler.  Am I correct here?  I could see adding one if the field type is date as dates are pretty generic.  In other words that make be feasible.

[Donald Lobo]

civicrm_views_custom_data_cache

is the function that adds the custom data fields to the data definition

lobo

[jalama]

Given their unique nature does it make sense to add a function that add the arguments for date fields?  In other words if $currentfield['data_type'] = 'date' then run a function to add the arguments to $data before it gets returned from civicrm_views_custom_data_cache function.  This could be used to populate the date argument fields for custom fields and any other date fields.

[Donald Lobo]

yes, that makes sense and that function should be able to handle different date formats. wanna do it and submit a patch

thanx

lobo

[jalama]

Sure

Though I'm not quite following the format issue.  can you elaborate?

[Donald Lobo]

civicrm dates are configurable by the user and some are implicit in the system. Some are date and time, while others are date only. Would be good to propagate this information to views, so that only the appropriate elements are rendered etc. To begin with we can display all elements and then tweak it as needed

lobo

[jalama]

Sorry I think I'm being a little dense here, but how would that info be useful to a views argument handler?

Either way, determining formats date v datetime should be easy, but how would one tell programaticly which are implicit v user configurable?

[Donald Lobo]

might be easier to have this conversation on IRC

My views knowledge is a bit limited, so i dont know the various date formats that views accepts. I suspect it accepts a few different type of formats, and my main point was we should try to let views know what format we are using, to be more specific:

birth_date is a date field only
start_date is a datetime field

all custom date fields have a date_parts / start_date / end_date which control the date display and format

lobo