custom_view_reports.module

<?php

/**
 * @file
 * Custom reports for myboosters
 *
 */ 

/**
 * Implementation of hook_menu()
 * 
 * Add a settings page, and the general award callback  
 * @return
 *   Returns an array definining services, their call backs, and options/info.
 */
function custom_view_reports_menu() {

  $items['reporting/custom'] = array(
    'title'            => 'Awards',
    'page callback'    => 'custom_view_reports_load',
    'access arguments' => array('access content'),
    'description'      => 'Customized reports for your needs',
    'type' => MENU_CALLBACK,
  ); 

  $items['admin/settings/myboosters-reporting'] = array(
    'title' => t('Myboosters Report Settings'),
    'description' => t('Adjust your reporting settings'),
    'page callback' => 'drupal_get_form',
    'page arguments' => array('custom_view_reports_admin'),
    'access arguments' => array('access administration pages'),
    'type' => MENU_NORMAL_ITEM,
   );
  return $items;
}

/**
 * Implementation of hook_admin()
 * 
 * Create the admin settings form  for database name & pager length
 * @return
 *   Returns an array of form items
 */
function custom_view_reports_admin() {
  $form = array();

  $form['custom_view_reports_db_name'] = array(
    '#type' => 'textfield',
    '#title' => t('Civicrm(or External) Database Admin'),
    '#default_value' => variable_get('custom_view_reports_db_name', "customDB"),
    '#size' => 35,
    '#maxlength' => 35,
    '#description' => t("The DB you will be querying"),
    '#required' => TRUE,
  );
  $form['custom_view_reports_pager'] = array(
    '#type' => 'textfield',
    '#title' => t('Set pager size'),
    '#default_value' => variable_get('custom_view_reports_pager', "10"),
    '#size' => 3,
    '#maxlength' => 3,
    '#description' => t("Default is 10"),
    '#required' => TRUE,
  );
  $form['custom_view_reports_debug'] = array(
      '#type' => 'checkbox',
      '#title' => t('Debug?'),
      '#default_value' => variable_get('custom_view_reports_debug', FALSE),
      '#description' => t("Log the report query to watchdog & other useful information"),
      '#required' => TRUE,
  );


  return system_settings_form($form);
}

/**
 * return the query for filtering based on years
 * 
 * either all awards in a given year, using like(inefficient??) 
 * @return
 *   $suffix_query: the query to append to our where clause when filtering based on years
 *
 * TODOS: is LIKE the best route if we know the specific year?
 */
function custom_view_reports_year_range_sql($yrs) {
  if (strpos($yrs, '-'))  {
    $start=substr($yrs, 0, 4) . '-01-01';
    $end=substr($yrs, 5, 4) . '-12-31';
    $suffix_query="AND civicrm_grant.decision_date >= '$start' AND civicrm_grant.decision_date <= '$end'";
  }
  else  {
    $suffix_query="AND civicrm_grant.decision_date LIKE '%$yrs%'"; 
  }
  return ($suffix_query);
}

//not needed yet
function custom_view_reports_download()  {
print "lets do some downloading";
}


/**
 * Generate the report page
 * 
 * Add a settings page, and the general award callback  
 * @return
 *   Returns the reporting/custom page
 * TODOS: make the queries safe using placeholders
 */
function custom_view_reports_load($args=NULL, $filter=NULL, $param='')  {
 $default_message = "Dear Friends and Family,\n\nI am part of a fundraiser on myBoosters.com to help raise money for my cause. I'm currently doing my best in school and working hard in the organization, and I really want to do my best in this fundraiser. We are all trying our best to raise money very quickly.\n\n All help is appreciated! And if you really want to help, please also sign up as a supporter and send your page to your friends and family.";
  $select_query = "SELECT contactname, city, profile_state, message, boosteruser.pageurl as pageurl, FROM_UNIXTIME(users.created) AS created
                   FROM boosteruser
                   JOIN booster 
                   ON booster.nid = boosteruser.coach_id 
                   JOIN users 
                   ON users.uid = boosteruser.uid
                   WHERE bid=87";

  $count_query = "SELECT COUNT(*) 
                   FROM `boosteruser` 
                   JOIN booster 
                   ON booster.nid = boosteruser.coach_id 
                   WHERE bid=87";
   
  $headers = array(
    array('data' => t('Contact Name'), 'field' => 'contactname'),
    array('data' => t('City'), 'field' => 'city'),
    array('data' => t('State'), 'field' => 'profile_state'),
    array('data' => t('Message'), 'field' => 'message'),
    array('data' => t('Page Url'), 'field' => 'pageurl'),
    array('data' => t('Created On'), 'field' => 'created', 'sort'=>'asc'),
    array('data' => t('Arbitrary'), 'text' => 'la-di-da')
    );
  $limit=variable_get('custom_view_reports_pager', '30');
  $tablesort = tablesort_sql($headers);
  //dpm($select_query); dpm($tablesort); 
  //print $select_query . $tablesort . '<br>' . $count_query;
  $results = pager_query($select_query . $tablesort, $limit, 0, $count_query);
  if ($debug)  {
    watchdog('mybooster_reports' , 'query is=>' . $query);
    watchdog('mybooster_reports' , 'table sort query is=>' . $tablesort);
    watchdog('mybooster_reports' , 'pager_query=>' . $query . $tablesort);
    watchdog('mybooster_reports' , 'count query =>' . $count_query);
  }
   
  $rows=array();
  $count=0;
  while ($result = db_fetch_object($results)) { 
    $count++;
    if (!$dl)  {
      $page_url = "<a href='/". $result->pageurl . "'>See Page</a>";
      //print "IS <br> $default_message <br>EQUAL to <br> $result->message "; die();
      $message = strcmp($default_message,$result->message) ? 'Same' : $page_url;
      array_push($rows, array($result->contactname, $result->city, $result->profile_state, $message, $result->pageurl, $result->created, 'magicsauce'));
    }
    if (($dl)&&($type=='doc'))  {
      array_push($rows, array($result->firstname, $result->lastname, $result->email, $result->reporting_chapter, $result->org, number_format($result->amount), substr($result->date, 0, 4)));
    }
      else  {
        $csv .= "$result->firstname, $result->lastname, $result->email, $result->reporting_chapter, $result->org, $result->amount, " . substr($result->date, 0, 4) . "\n";
      }
  }

  if (($dl)&&($type=='csv'))  {
    return mybooster_reports_export_file($headers, $csv, $type);
  }
  elseif (($dl)&&($type=='doc'))  {
    $html .= theme('table', $headers, $rows);
    return mybooster_reports_export_file($headers, $html, $type);
  }
  else {
    global $base_url;
    $html = drupal_get_form('custom_view_reports_form');
    $html .= theme('table', $headers, $rows);
    $html .= theme('pager');
    $path=$base_url . '/' .  '/download';
    $path='http://' . $_SERVER['SERVER_NAME'] . '/' . $path;
    $html .= '<div="mybooster_reports_dl_cs">'.l('CSV', $path . '/csv/') . '</div>';
    $html .= '<div="mybooster_reports_dl_cs">'.l('DOC', $path . '/doc/') . '</div>';
    
    return $html;
  }

}


function mybooster_reports_export_file($headers, $rows, $type) { 
  $filename = 'custom_report-' . date('m-d-Y-g-i-s') . '.' . $type;
  $header = NULL;
  // Create  within the files folder.
  $csvpath = file_create_path('mybooster_reports_export');
  file_check_directory($csvpath, FILE_CREATE_DIRECTORY);
  $file = $csvpath .'/'. $filename;
  // Build CSV file.  "a" means we are opening to append, creating if needed.
  if (!$fp = fopen($file, 'ab')) {
    drupal_set_message(t('The file could not be created.'), 'error');
    return FALSE;
  }
    if ($headers) {
      fwrite($fp, $header);
    }

    fwrite($fp, $rows);
    fclose($fp);
    drupal_goto($file);
  return TRUE;
}


/**
 * Implementation of hook_form()
 * 
 * Create the filters, in views speak these are considered the exposed filters 
 * @return
 *   $form: an array of form elements
 * TODOS: add validation hook for year range, year1 must be less than year2
 */
function custom_view_reports_form() {
  $form = array(
    //'#action' => url($_GET['q'], array('query' => drupal_get_destination())),
    '#id' => 'custom_view_reports_form',
    '#submit' => array('custom_view_reports_submit')
  );


  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Filter'),
  );
  

  return $form;
}
/**
 * Implementation of hook_submit()
 * 
 * There has to be a better way to do this, probably to properly set the #action attribute with the parameters.  Currently it redirects to 
 * self with params in the query.  Not proper.
 * 
 */
function custom_view_reports_submit($form, &$form_state) {

  drupal_set_message(t('These are your filtered results.'));
  if ($form_state['values']['civicrm_school']!='')  {
    drupal_goto('reporting/custom/school/' . $form_state['values']['civicrm_school']);
  }
  elseif ($form_state['values']['civicrm_chapter']!='')  {
    drupal_goto('reporting/custom/chapter/' . $form_state['values']['civicrm_chapter']);
  }
  elseif ($form_state['values']['civicrm_first_name']!='')  {
    drupal_goto('reporting/custom/first_name/' . $form_state['values']['civicrm_first_name']);
  }
  elseif ($form_state['values']['civicrm_last_name']!='')  {
    drupal_goto('reporting/custom/last_name/' . $form_state['values']['civicrm_last_name']);
  }
  elseif ($form_state['values']['awards_year']!='')  {
    $year=(strlen($form_state['values']['awards_year_range'])>0)?$form_state['values']['awards_year'] . '-' . $form_state['values']['awards_year_range']:$form_state['values']['awards_year'];
    drupal_goto('reporting/custom/year/' . $year);
  }
}