modules/legislature/legislature.module

<?php
// $Id: legislature.module,v 1.70 2009/10/21 03:09:35 drumm Exp $

/**
 * @file
 *
 * To build a jurisdiction's import:
 * - Fill out the @link /api/group/metadata metadata @endlink functions.
 * - For each data source or type, create a PHP file,
 *   imports/{jusrisdiction}/{name}.inc. Define all functions as
 *   legislature_{jurisdiction}_{...}().
 * - Import functions which should be visible in the UI for importing need an
 *   entry in legislature_imports().
 * - Long imports can be queued to run later with job_queue_add().
 * - Call @link /api/group/import import @endlink functions for saving data.
 * - Call @link /api/group/info info @endlink functions for getting information about the
 *   database.
 */

define('LEGISLATURE_SPONSOR', 1);
define('LEGISLATURE_COSPONSOR', 2);

define('LEGISLATURE_LAST_FIRST', 1);
define('LEGISLATURE_FIRST_LAST', 2);

function legislature_perm() {
  return array('run legislature imports', 'configure legislatures');
}

function legislature_menu() {
  return array(
    'admin/settings/legislature' => array(
      'title' => 'Legislatures',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('legislature_configure_form'),
      'access arguments' => array('configure legislatures'),
    ),
    'legislature-ids.csv' => array(
      'title' => 'Legislature IDs',
      'page callback' => 'legislature_ids_csv',
      'access arguments' => array('access content'), 
      'type' => MENU_CALLBACK,
    ),
  );
}

/**
 * @defgroup import Importing data
 * Import functions will either update existing records or save new data.
 *
 * Most data structures should be saved via the API instead of directly
 * accessing the database. Execptions which have not been implemented in the
 * API yet include the following tables:
 * - {legislature_action_politician}, individual politician votes on a action.
 * - {legislature_bill_committee}, committees which have worked on a bill.
 * - {legislature_bill_sponsor}, politicians which have sponsored or
 *   co-sponsored a bill.
 * - {legislature_bill_title}, alternate titles for bills.
 * - {legislature_committee_membership}, politician membership to committees.
 *
 * The full database structure is described in legislature_install().
 */

/**
 * @defgroup info Information retrieval
 * Retrieve information from the legislature database, such as ID numbers.
 */

/**
 * @defgroup metadata Jurisdiction metadata
 *
 * Information about each jurisdiction.
 *
 * @{
 */

/**
 * A list of supported sessions.
 */
function legislature_sessions() {
  return array(
    'us' => array( // Top-level key is jusisdiction.
      '108' => t('108th U.S. Congress'), // Number => Title
      '109' => t('109th U.S. Congress'),
      '110' => t('110th U.S. Congress'),
      '111' => t('111th U.S. Congress'),
    ),
    'ca' => array(
      '2003' => t('2003-2004 California State Legislature'),
      '2009' => t('2009-2010 California State Legislature'),
    ),
    'ia' => array(
      '82' => t('82nd Iowa General Assembly'),
    ),
    'lax' => array(
      '2009' => t('2009 City of Los Angeles'),
    ),
  );
}

/**
 * A list of start and end years for each session.
 */
function legislature_session_years() {
  return array(
    'us' => array(
      '108' => array('start' => 2003, 'end' => 2004),
      '109' => array('start' => 2005, 'end' => 2006),
      '110' => array('start' => 2007, 'end' => 2008),
      '111' => array('start' => 2009, 'end' => 2010),
    ),
    'ca' => array(
      '2003' => array('start' => 2003, 'end' => 2004),
      '2009' => array('start' => 2009, 'end' => 2010),
    ),
    'ia' => array(
      '82' => array('start' => 2007, 'end' => 2008),
    ),
    'lax' => array(
      '2009' => array('start' => 2009, 'end' => 2009),
    ),
  );
}

/**
 * A list of offices.
 */
function legislature_offices() {
  return array(
    'us' => array(
      t('Senate') => array( // Office title
        'length' => 6, // Years
        'count_limit' => 0, // Sessions, 0 is unlimited
      ),
      t('House') => array(
        'length' => 2,
        'count_limit' => 0,
      ),
    ),
    'ca' => array(
      t('Assembly') => array(
        'length' => 2,
        'count_limit' => 3,
      ),
      t('Senate') => array(
        'length' => 4,
        'count_limit' => 2,
      ),
      t('Governor') => array(
        'length' => 4,
        'count_limit' => 2,
      ),
    ),
    'ia' => array(
      t('Senate') => array(
        'length' => 4,
        'count_limit' => 0,
      ),
      t('House') => array(
        'length' => 2,
        'count_limit' => 0,
      ),
    ),
    'lax' => array(
      t('Mayor') => array(
        'length' => 4,
        'count_limit' => 2,
      ),
      t('Controller') => array(
        'length' => 4,
        'count_limit' => 0,
      ),
      t('Attorney') => array(
        'length' => 4,
        'count_limit' => 0,
      ),
      t('Council') => array(
        'length' => 4,
        'count_limit' => 0,
      ),
    ),
  );
}

/**
 * A list of bill prefixes.
 *
 * @return an array of system prefix => display prefix.
 */
function legislature_prefixes($jurisdiction) {
  switch ($jurisdiction) {
    case 'us':
      // Put short prefixes last.
      return array(
        'HR' => 'H.Res.',
        'HJ' => 'H.J.Res.',
        'HC' => 'H.Con.Res.',
        'H' => 'H.R.',
        'SR' => 'S.Res.',
        'SJ' => 'S.J.Res.',
        'SC' => 'S.Con.Res.',
        'S' => 'S.',
      );

    case 'ca':
      return array(
        'AB' => 'AB',
        'SB' => 'SB',
      );
  }
}

/**
 * A list of full chamber names corresponding to the first letter of the bill
 * prefix.
 *
 * @return An array of prefix{0} => house name.
 */
function legislature_prefix_chamber($jurisdiction) {
  switch ($jurisdiction) {
    case 'us':
      return array(
        'H' => t('House'),
        'S' => t('Senate'),
      );

    case 'ca':
      return array(
        'A' => t('Assembly'),
        'S' => t('Senate'),
      );
  }
}

/**
 * @}
 */

/**
 * Parses measures into valid prefixes and numbers. If the prefix is not valid,
 * the returned prefix is NULL.
 */
function legislature_parse_measure($measure, $jurisdiction) {
  $measure = str_replace(' ', '', str_replace('.', '', drupal_strtoupper($measure)));

  foreach (legislature_prefixes($jurisdiction) as $system => $prefix) {
    $prefix = str_replace('.', '', drupal_strtoupper($prefix));
    if (strpos($measure, $prefix) === 0) {
      return array($system, intval(trim(drupal_substr($measure, strlen($prefix)))));
    }
  }
  return array(NULL, intval($measure));
}

/**
 * Define import functions which will be visible in the UI.
 */
function legislature_imports() {
  $imports = array();

  if (count(legislature_get_enabled_sessions('us')) > 0) { // Only show imports if the jurisdiction is enabled
    $imports[t('GovTrack')] = array( // Section
      'legislature_govtrack_politicians' => array( // Function name
        'title' => t('Politicians'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_committees' => array(
        'title' => t('Committees'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_bills' => array(
        'title' => t('Bills'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_update_bill' => array(
        'title' => t('Specific bill'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_roll' => array(
        'title' => t('Specific roll'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_recent_votes' => array(
        'title' => t('Recent votes'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_govtrack_undervotes' => array(
        'title' => t('Schedule updates for under or over-voted bills'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/govtrack.inc',
        'access' => user_access('run legislature imports'),
      ),
    );

    $imports[t('Sunlight')] = array(
      'legislature_sunlight_politicians' => array(
        'title' => t('Politician contact information'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/sunlight.inc',
        'access' => user_access('run legislature imports') && (variable_get('legislature_sunlight_api_key', '') !== ''),
      ),
    );

    $imports[t('Thomas')] = array(
      'legislature_us_thomas_committees' => array(
        'title' => t('Committees'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/us/thomas.inc',
        'access' => user_access('run legislature imports'),
      ),
    );
  }

  if (count(legislature_get_enabled_sessions('ia')) > 0) {
    $imports[t('Iowa')] = array(
      'legislature_ia_politicians' => array(
        'title' => t('Politicians'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ia/legis_politicians.inc',
        'access' => user_access('run legislature imports') && legislature_html_parser_installed(),
      ),
      'legislature_ia_bills' => array(
        'title' => t('Bills'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ia/legis_bills.inc',
        'access' => user_access('run legislature imports') && legislature_html_parser_installed(),
      ),
    );
  }

  if (count(legislature_get_enabled_sessions('ca')) > 0) {
    $imports[t('California')] = array(
      'legislature_ca_load_state_data' => array(
        'title' => t('Load State Data (load files and move records)'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_load_files' => array(
        'title' => t('Load State Files'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_move_legislators' => array(
        'title' => t('Move State Legislators'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_move_committees' => array(
        'title' => t('Move State Committees'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_move_bills' => array(
        'title' => t('Move State Bills'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_move_actions' => array(
        'title' => t('Move State Actions'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/leginfo.inc',
        'access' => user_access('run legislature imports'),
      ),
      'legislature_ca_move_nimsp' => array(
        'title' => t('Move NIMSP Data'),
        'file path' => drupal_get_path('module', 'legislature') .'/imports/ca/nimsp.inc',
        'access' => user_access('run legislature imports'),
      ),
    );
  }
  
  $imports[t('CSV')] = array(
    'legislature_import_politicians' => array(
      'title' => t('Politicians'),
      'file path' => drupal_get_path('module', 'legislature') .'/imports/politicians.inc',
      'access' => user_access('run legislature imports'),
    ),
    'legislature_import_districts' => array(
      'title' => t('Districts'),
      'file path' => drupal_get_path('module', 'legislature') .'/imports/districts.inc',
      'access' => user_access('run legislature imports'),
    ),
    'legislature_import_jobs' => array(
      'title' => t('Jobs'),
      'file path' => drupal_get_path('module', 'legislature') .'/imports/jobs.inc',
      'access' => user_access('run legislature imports'),
    ),
  );

  return $imports;
}

function legislature_help($page = NULL) {
  switch ($page) {
    case 'admin/content/import_manager/legislature/GovTrack':
    case 'admin/content/import_manager/legislature/Sunlight':
      return '<p>'. t('These can take awhile. Let the browser load and check the <a href="!logs">logs</a>. Some actions add <a href="!queue">queued jobs</a> to be executed later.', array('!logs' => url('admin/logs/watchdog'), '!queue' => url('admin/logs/job_queue'))) .'</p>';
  }
}

function legislature_form_alter(&$form, &$form_state, $form_id) {
  switch ($form_id) {
    case 'legislature_govtrack_bills':
      foreach (legislature_get_enabled_sessions('us') as $session) {
        $form['data']['clear'][$session] = array(
          '#type' => 'checkbox',
          '#title' => t('Reload all %session bills', array('%session' => $session)),
        );
      }
      break;

    case 'legislature_govtrack_update_bill':
      $form['data']['bill_id'] = array(
        '#type' => 'textfield',
        '#title' => t('Bill ID'),
      );
      break;

    case 'legislature_govtrack_roll':
      $form['data']['action_id'] = array(
        '#type' => 'textfield',
        '#title' => t('Action ID'),
      );
      break;

    case 'legislature_ia_politicians':
      if (!legislature_html_parser_installed()) {
        $form['data'][] = array(
          '#value' => '<p>'. t('Requires <a href="@url">htmlparser.inc</a>', array('@url' => 'http://php-html.cvs.sourceforge.net/php-html/phphtmlparser/src/htmlparser.inc?view=log')) .'</p>',
        );
      }
      break;

    case 'legislature_import_politicians':
      $form['#attributes']['enctype'] = 'multipart/form-data';
      $form['data']['file'] = array(
        '#type' => 'file',
        '#value' => t('CSV file'),
        '#description' => t('With columns politician_id, last_name, full_name, roster_name, phone, fax, email, party, gender, middle_name, nick_name, first_name, name_modifier.'),
      );
      break;

    case 'legislature_import_districts':
      $form['#attributes']['enctype'] = 'multipart/form-data';
      $form['data']['file'] = array(
        '#type' => 'file',
        '#value' => t('CSV file'),
        '#description' => t('With columns jurisdiction, office, number, state, name_display.'),
      );
      break;

    case 'legislature_import_jobs':
      $form['#attributes']['enctype'] = 'multipart/form-data';
      $form['data']['file'] = array(
        '#type' => 'file',
        '#value' => t('CSV file'),
        '#description' => t('With columns politician_id, jurisdiction, state, office, number, start, end, is_candidate.'),
      );
      break;
      
    case 'legislature_ca_load_state_data':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      // Day menu
      $form['data']['day'] = array(
        '#type' => 'select',
        '#title' => t('Day'),
        '#options' => array(
          'Sun' => 'Sun',
          'Mon' => 'Mon',
          'Tue' => 'Tue',
          'Wed' => 'Wed',
          'Thu' => 'Thu',
          'Fri' => 'Fri',
          'Sat' => 'Sat',
        ),
      ); 
      break;
      
      case 'legislature_ca_load_files':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      // Day menu
      $form['data']['day'] = array(
        '#type' => 'select',
        '#title' => t('Day'),
        '#options' => array(
          'Sun' => 'Sun',
          'Mon' => 'Mon',
          'Tue' => 'Tue',
          'Wed' => 'Wed',
          'Thu' => 'Thu',
          'Fri' => 'Fri',
          'Sat' => 'Sat',
        ),
      ); 
      // Table menu
      $tables['all'] = 'All';
      include_once './' . drupal_get_path('module', 'legislature') .'/imports/ca/leginfo_mapping.inc';
      foreach(legislature_ca_table_file_mapping() as $key => $value) {
        $tables[$key] = $key;
      }
      $form['data']['table'] = array(
        '#type' => 'select',
        '#title' => t('Table(s)'),
        '#options' => $tables,
      ); 
      break;
      
    case 'legislature_ca_move_legislators':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      break;
      
    case 'legislature_ca_move_committees':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      break;      
      
    case 'legislature_ca_move_bills':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      break;  
    case 'legislature_ca_move_actions':
      $sessions = legislature_sessions();
      // Session menu
      $form['data']['session'] = array(
        '#type' => 'select',
        '#title' => t('Session'),
        '#options' => $sessions['ca'],
      ); 
      break;  
      
  }
}

function legislature_configure_form() {
  $session_options = array();
  $session_value = array();
  foreach (legislature_sessions() as $jurisdiction => $sessions) {
    foreach ($sessions as $session => $name) {
      $session_options[$jurisdiction .'-'. $session] = $name;
      $session = legislature_get_session($jurisdiction, $session);
      if (!is_null($session) && $session->enabled) {
        $session_value[] = $jurisdiction .'-'. $session->name;
      }
    }
  }
  $form = array();

  $form['legislature_sessions'] = array(
    '#title' => t('Enabled sessions'),
    '#type' => 'checkboxes',
    '#options' => $session_options,
    '#default_value' => $session_value,
  );

  $form['legislature_ca_data_directory'] = array(
    '#type' => 'textfield',
    '#title' => t('California Legislature data directory'),
    '#default_value' => variable_get('legislature_ca_data_directory', ''),
  );
  $form['legislature_ca_antiword'] = array(
    '#type' => 'textfield',
    '#title' => t('Location of Antiword application (converts Word documents to plain text)'),
    '#default_value' => variable_get('legislature_ca_antiword', ''),
  );
  
  $form['legislature_sunlight_api_key'] = array(
    '#type' => 'textfield',
    '#title' => t('Sunlight API key'),
    '#default_value' => variable_get('legislature_sunlight_api_key', ''),
    '#description' => l(t('Register for a key'), 'http://services.sunlightlabs.com/api/register/'),
  );

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

  return $form;
}

function legislature_configure_form_submit($form, &$form_state) {
  foreach ($form_state['values']['legislature_sessions'] as $key => $value) {
    list($jurisdiction, $name) = explode('-', $key);
    $session = legislature_get_session($jurisdiction, $name);
    if (is_null($session)) {
      db_query("INSERT INTO {legislature_session} (session_jurisdiction, session_name, enabled) VALUES ('%s', '%s', %d)", $jurisdiction, $name, ($key === $value));

      // If this is the first of a jurisdiction, we need to add offices.
      if (db_result(db_query("SELECT count(*) FROM {legislature_office} WHERE jurisdiction = '%s'", drupal_strtoupper($jurisdiction))) == 0) {
        $offices = legislature_offices();
        foreach ($offices[$jurisdiction] as $title => $office) {
          db_query("INSERT INTO {legislature_office} (jurisdiction, title, length, count_limit) VALUES ('%s', '%s', %d, %d)", drupal_strtoupper($jurisdiction), $title, $office['length'], $office['count_limit']);
        }
      }
    }
    else {
      db_query('UPDATE {legislature_session} SET enabled = %d WHERE session_id = %d', ($key === $value), $session->session_id);
    }
  }

  variable_set('legislature_ca_data_directory', $form_state['values']['legislature_ca_data_directory']);
  variable_set('legislature_ca_antiword', $form_state['values']['legislature_ca_antiword']);
  
  variable_set('legislature_sunlight_api_key', $form_state['values']['legislature_sunlight_api_key']);

  drupal_set_message(t('Saved configuration.'));
}

function legislature_ids_csv() {
  $output = "politician_id,govtrack_id,crp_id,legis_id,fec_id,name\n";
  $result = db_query('SELECT politician_id, govtrack_id, crp_id, legis_id, fec_id, full_name FROM {legislature_politician}');
  while ($row = db_fetch_array($result)) {
    $row['name'] = '"'. $row['name'] .'"';
    $output .= implode(',', $row) ."\n";
  }
  drupal_set_header('Content-type: text/csv');
  print $output;
}

/**
 * Get the session object for a given jurisdiction and name.
 *
 * @ingroup info
 *
 * @param $jurisdiction
 *   A jurisdiction name
 * @param $name
 *   A session name
 * @return
 *   A session object. If the session does not exist, NULL.
 */
function legislature_get_session($jurisdiction, $name) {
  static $sessions;

  if (is_null($sessions)) {
    $sessions = array();
    $result = db_query('SELECT session_id, session_jurisdiction, session_name, enabled FROM {legislature_session}');
    while ($session = db_fetch_object($result)) {
      $sessions[$session->session_jurisdiction][$session->session_name] = (object) array(
        'session_id' => $session->session_id,
        'jurisdiction' => $session->session_jurisdiction,
        'name' => $session->session_name,
        'enabled' => $session->enabled,
      );
    }
  }

  if (isset($sessions[$jurisdiction][$name])) {
    return $sessions[$jurisdiction][$name];
  }
  return NULL;
}

/**
 * Find enabled sessions within a jurisdiction.
 *
 * @ingroup info
 *
 * @param $jurisdiction
 *   A jurisdiction string.
 * @return
 *   An array of enabled session name strings.
 */
function legislature_get_enabled_sessions($jurisdiction) {
  $enabled_sessions = array();

  $sessions = legislature_sessions();
  foreach (array_keys($sessions[$jurisdiction]) as $name) {
    $session = legislature_get_session($jurisdiction, $name);
    if (!is_null($session) && $session->enabled) {
      $enabled_sessions[] = $name;
    }
  }

  return $enabled_sessions;
}

function legislature_html_parser_installed() {
  return is_readable(drupal_get_path('module', 'legislature') .'/htmlparser.inc');
}

/**
 * Import a politician.
 *
 * @ingroup import
 *
 * @param $politician
 *   An associative array of the politician's data. A key matching
 *   $id_key .'_id' must exist. Not all possible keys need to exist.
 * @param $id_key
 *   The key to de-dupe on. Do not include '_id' suffix.
 *
 * @return
 *   The $politician array with the 'politician_id' filled in.
 */
function legislature_politician($politician, $id_key = 'politician') {
  if ($id_key != 'politician') {
    $politician['politician_id'] = legislature_get_politician_id($id_key, $politician[$id_key .'_id']);
  }

  if ($politician['politician_id'] == 0) {
    drupal_write_record('legislature_politician', $politician);
    $politician['politician_id'] = db_last_insert_id('legislature_politician', 'politician_id');
  }
  else {
    drupal_write_record('legislature_politician', $politician, 'politician_id');
  }

  return $politician['politician_id'];
}

/**
 * Import a district and retrieve the district_id.
 *
 * @ingroup import
 *
 * @param $jurisdiction
 *   The jusrisdiction string, such as 'ca' or 'us'.
 * @param $office
 *   The office string, such as 'Senate' or 'House'.
 * @param $state
 *   Two-letter state abbreviation, such as 'CA' or 'IA'.
 * @param $number
 *   District number, or 0 if the district is not numbered.
 * @param $name_display
 *   The human-readable name for the district.
 *
 * @return
 *   The district_id for the district.
 */
function legislature_district($jurisdiction, $office, $state, $number, $name_display) {
  $name = drupal_strtoupper($jurisdiction) .'-'. $office .'-'. $state .'-'. $number;
  $return = db_result(db_query("SELECT district_id FROM {legislature_district} WHERE name = '%s'", $name));
  if ($return !== FALSE) {
    return $return;
  }
  else {
    db_query("INSERT INTO {legislature_district} (jurisdiction, name, office, number, city, state, name_display) VALUES ('%s', '%s', '%s', %d, '', '%s', '%s')", $jurisdiction, $name, $office, $number, $state, $name_display);
    return db_last_insert_id('legislature_district', 'district_id');;
  }
}

/**
 * Get a district's district_id.
 *
 * @ingroup info
 *
 * @param $jurisdiction
 *   The jusrisdiction string, such as 'ca' or 'us'.
 * @param $office
 *   The office string, such as 'Senate' or 'House'.
 * @param $state
 *   Two-letter state abbreviation, such as 'CA' or 'IA'.
 * @param $number
 *   District number, or 0 if the district is not numbered.
 *
 * @return
 *   The district_id for the district.
 */
function legislature_get_district_id($jurisdiction, $state, $office, $number) {
  static $districts = array();

  $name = drupal_strtoupper($jurisdiction) .'-'. $office .'-'. drupal_strtoupper($state) .'-'. $number;

  if (!isset($districts[$name])) {
    $districts[$name] = db_result(db_query("SELECT district_id FROM {legislature_district} WHERE name = '%s'", $name));
  }

  return $districts[$name];
}

/**
 * Returns all districts in a jurisdiction.
 */
function legislature_get_districts($jurisdiction) {
  $districts = array();
  $result = db_query("SELECT district_id, name, office, number, city, state, name_display FROM {legislature_district} WHERE jurisdiction = '%s'", $jurisdiction);
  while ($district = db_fetch_object($result)) {
    $districts[$district->district_id] = $district;
  }
  return $districts;
}

/**
 * Get an office's office_id.
 *
 * @ingroup info
 *
 * @param $jurisdiction
 *   The jusrisdiction string, such as 'ca' or 'us'.
 * @param $title
 *   The office's title.
 * @return
 *   The office_id for the office.
 */
function legislature_get_office_id($jurisdiction, $title) {
  return db_result(db_query("SELECT office_id FROM {legislature_office} WHERE jurisdiction = '%s' AND title = '%s'", $jurisdiction, $title));
}

/**
 * Save elected jobs for a politician. All the elected jobs for a politician
 * need to be saved in one call to this function. Include elected_jobs from all
 * sessions.
 *
 * Keys in a elected_job associative array are:
 * - office_id
 * - district_id
 * - start, a UNIX timestamp for the start of the politician's term.
 * - end, a UNIX timestamp for the end of the politician's term.
 * - is_candidate, TRUE if the job is a candidacy.
 *
 * @ingroup import
 *
 * @param $jurisdiction
 *   The jusrisdiction string, such as 'ca' or 'us'.
 * @param $politician_id
 *   The politician_id for the elected job.
 * @param $elected_jobs
 *   An array of elected_job associative arrays.
 */
function legislature_save_elected_jobs($jurisdiction, $politician_id, $elected_jobs, $session = NULL) {
  //todo this thrashes the elected job id, but we don't really use that anywhere.
  // Clear old jobs
  if (isset($session)) {
    $result = db_query("SELECT ej.elected_job_id FROM {legislature_elected_job} ej INNER JOIN {legislature_elected_job_session} ejs ON ejs.elected_job_id = ej.elected_job_id INNER JOIN {legislature_session} s ON s.session_id = ejs.session_id WHERE ej.politician_id = %d AND s.session_name = '%s' AND s.session_jurisdiction = '%s'", $politician_id, $session, $jurisdiction);
  }
  else {
    $result = db_query('SELECT elected_job_id FROM {legislature_elected_job} WHERE politician_id = %d', $politician_id);
  }
  $old_elected_job_ids = array();
  while ($row = db_fetch_object($result)) {
    $old_elected_job_ids[] = $row->elected_job_id;
  }
  db_query("DELETE FROM {legislature_elected_job_session} WHERE elected_job_id IN (" . db_placeholders($old_elected_job_ids) . ")", $old_elected_job_ids);
  db_query("DELETE FROM {legislature_elected_job} WHERE elected_job_id IN (" . db_placeholders($old_elected_job_ids) . ")", $old_elected_job_ids);

  // Save the jobs
  foreach ($elected_jobs as $job) {
    db_query('INSERT INTO {legislature_elected_job} (politician_id, office_id, district_id, start, end, is_candidate) VALUES (%d, %d, %d, %d, %d, %d)', $politician_id, $job['office_id'], $job['district_id'], $job['start'], $job['end'], $job['is_candidate']);
    $job['elected_job_id'] = db_last_insert_id('legislature_elected_job', 'elected_job_id');
    foreach (legislature_get_sessions_by_date($jurisdiction, $job['start'], $job['end']) as $session) {
      db_query('INSERT INTO {legislature_elected_job_session} (elected_job_id, session_id) VALUES (%d, %d)', $job['elected_job_id'], $session->session_id);
    }
  }

  // Mark is_most_recent for all elected job rows as 0
  db_query("UPDATE {legislature_elected_job} SET is_most_recent = 0 WHERE politician_id = %d", $politician_id);
  // Mark record with most recent start value as 1
  $max_start = db_result(db_query("SELECT MAX(start) FROM {legislature_elected_job} WHERE politician_id = %d", $politician_id));
  db_query("UPDATE {legislature_elected_job} SET is_most_recent = 1 WHERE politician_id = %d AND start = %d", $politician_id, $max_start);
}

/**
 * Get an array of sessions intersecting with a time period.
 *
 * @ingroup info
 *
 * @param $jurisdiction
 *   The jusrisdiction string, such as 'ca' or 'us'.
 * @param $start
 *   A UNIX timestamp for the time period start.
 * @param $end
 *   A UNIX timestamp for the time period end.
 *
 * @return
 *   An array of session objects.
 */
function legislature_get_sessions_by_date($jurisdiction, $start, $end) {
  $session_years = legislature_session_years();
  $years = range(gmdate('Y', $start), gmdate('Y', $end));

  $sessions = array();
  foreach (legislature_get_enabled_sessions($jurisdiction) as $session) {
    if (array_intersect(range($session_years[$jurisdiction][$session]['start'], $session_years[$jurisdiction][$session]['end']), $years)) {
      $sessions[] = legislature_get_session($jurisdiction, $session);
    }
  }
  return $sessions;
}

/**
 * Import a bill's metadata.
 *
 * Keys for bill data include:
 * - session_id
 * - measure, {machine-readable prefix} .' '. {bill number}
 * - last_version, any string which changes when a full bill update is needed.
 * - status, a short machine-readble string describing the bill's status.
 * - topic, the bill's main title.
 * - description, a string descriibng the bill.
 * all are required.
 *
 * @ingroup import
 *
 * @param $data
 *   An object of bill data.
 *
 * @return
 *   TRUE if a full bill update needs to be run, otherwise FALSE. The bill_id
 *   is filled into $data, which is passed by-reference.
 */
function legislature_save_bill(&$data) {
  $update = FALSE;

  $bill = db_fetch_object(db_query("SELECT bill_id, last_version FROM {legislature_bill} WHERE session_id = %d AND measure = '%s'", $data['session_id'], $data['measure']));
  if ($bill !== FALSE) {
    $data['bill_id'] = $bill->bill_id;
    if ($data['last_version'] != $bill->last_version) {
      $update = TRUE;
      $data['queued'] = TRUE;
    }
    db_query("UPDATE {legislature_bill} SET status = '%s', last_version = '%s', topic = '%s', description = '%s' WHERE bill_id = %d", $data['status'], $data['last_version'], $data['topic'], $data['description'], $data['bill_id']);
    module_invoke_all('legislature', 'bill', 'update', $data);
  }
  else {
    db_query("INSERT INTO {legislature_bill} (measure, prefix, session_id, topic, description, status, last_version) VALUES ('%s', '%s', %d, '%s', '%s', '%s', '%s')", $data['measure'], $data['prefix'], $data['session_id'], $data['topic'], $data['description'], $data['status'], $data['last_version']);
    $data['bill_id'] = db_last_insert_id('legislature_bill', 'bill_id');
    $update = TRUE;
    module_invoke_all('legislature', 'bill', 'insert', $data);
  }

  return $update;
}

/**
 * Get the standardized politician ID using a 3rd party ID.
 *
 * @ingroup info
 *
 * @param $type
 *   Either 'govtrack', 'crp', or 'legis' for US, CA, and IA.
 * @param $id
 *   An ID number of $type.
 *
 * @return
 *   The corresponding standardized politician ID.
 */
function legislature_get_politician_id($type, $id) {
  static $politician_ids = array(
    'govtrack' => array(),
    'crp' => array(),
    'legis' => array(),
    'fec' => array(),
  );

  if (!in_array($type, array_keys($politician_ids))) {
    return NULL; // We don't support this $type.
  }

  if (!isset($politician_ids[$type][$id])) {
    $politician_ids[$type][$id] = db_result(db_query('SELECT politician_id FROM {legislature_politician} WHERE '. $type ."_id = '%s'", $id));
  }

  return $politician_ids[$type][$id];
}

/**
 *
 */
function legislature_search_politician_id($conditions = array()) {
  $where = array();
  $args = array(); 

  foreach ($conditions as $key => $value) {
    switch ($key) {
      case 'session id':
        $where[] = 'ejs.session_id = %d';
        $args[] = $value;
        break;

      case 'office id':
        $where[] = 'ej.office_id = %d';
        $args[] = $value;
        break;

      case 'district id':
        $where[] = 'ej.district_id = %d';
        $args[] = $value;
        break;

      case 'name':
        $politician_ids = legislature_normalize_politician_name($value);
        if (count($politician_ids) == 0) {
          return NULL;
        }
        $where[] = 'p.politician_id IN ('. implode(',', array_fill(0, count($politician_ids), '%d')) .') ';
        $args = array_merge($args, $politician_ids);
        break;

      case 'first last name':
        $politician_ids = legislature_normalize_politician_name($value, LEGISLATURE_FIRST_LAST);
        if (count($politician_ids) == 0) {
          return NULL;
        }
        $where[] = 'p.politician_id IN ('. implode(',', array_fill(0, count($politician_ids), '%d')) .') ';
        $args = array_merge($args, $politician_ids);
        break;
    }
  }

  $return = db_result(db_query_range('SELECT DISTINCT p.politician_id FROM {legislature_politician} p INNER JOIN {legislature_elected_job} ej ON ej.politician_id = p.politician_id INNER JOIN {legislature_elected_job_session} ejs ON ejs.elected_job_id = ej.elected_job_id WHERE '. implode(' AND ', $where), $args, 0, 1));
  if ($return === FALSE) {
    return NULL;
  }
  else {
    return $return;
  }
}

/**
 * Normalize a poltician's name and look for matches.
 *
 * Based on work by GovTrack.us. http://www.govtrack.us/source.xpd
 *
 * @param $name
 *   The politician's name. Either as 'Last, First' or 'First Last'.
 * @param $format
 *   Either LEGISLATURE_LAST_FIRST or LEGISLATURE_FIRST_LAST.
 * @return
 *   An array of possible politician_ids.
 */
function legislature_normalize_politician_name($name, $format = LEGISLATURE_LAST_FIRST) {
  // Split last name, first name
  $name = str_replace(array('``', "''"), array('"', '"'), $name); // ``Hank''
  $name = str_replace(array('é', 'ú'), array('e', 'u'), $name); // MySQL has little respect for proper accents
  $name = str_replace(',"', '",', $name); // Henry C. "Hank," Jr.
  $name = preg_replace('/(\.)(\S)/', '$1 $2', $name); // 'C.A.' => 'C. A.'

  if ($format == LEGISLATURE_FIRST_LAST) {
    list($name, $namemod) = legislature_parse_suffix($name, $namemod);

    $fnames = preg_split('/\s+/', $name);
    $lastname = array_pop($fnames);
    if ($namemod != '') {
      array_push($fnames, $namemod);
    }

    if (preg_match('/^(du|de|La|Van)$/i', $fnames[count($fnames) - 1])) {
      $lastname = array_pop($fnames) .' '. $lastname;
    }
  }
  else { // Either just last name, or 'last, first' format.
    $lastname = $name;
    $fnames = array();
    if (preg_match('/^([^,]+),\s+([\w\W]+)/', $name, $matches)) {
      $lastname = $matches[1];
      $fnames = preg_split('/[\s,]+/', $matches[2]);

      // Test $namemod
      $m = $fnames[count($fnames) - 1];
      if (preg_match('/^(Jr\.?|Sr\.?|I|II|III|IV)$/', $m)) {
        $namemod = $m;
        array_pop($fnames);
      }
    }

    if ($lastname == drupal_strtoupper($lastname)) {
      $lastname = drupal_strtolower($lastname);
      $lastname = drupal_ucfirst($lastname);
    }

    list($lastname, $namemod) = legislature_parse_suffix($lastname, $namemod);
    if ($namemod != '') {
      array_push($fnames, $namemod);
    }
  }

  // Single letter first name entries need a period.
  foreach ($fnames as $key => $f) {
    if (strlen($f) == 1) {
      $fnames[$key] .= '.';
    }
  }

  // Get list of matching records by last name.

  $lastsplit = explode(' ', $lastname);

  // For women we better search the middle name field for maiden names, but
  // then if it matches the last name we have to undef the middle name field
  // because then it's not to be understood as a middle name.
  $lastname = strtr($lastname, ' ', '-');
  $lastname2 = strtr($lastname, '-', ' ');
  $where = array();
  $args = array();
  $where[] = "last_name = '%s'";
  $args[] = $lastname;
  $where[] = "last_name = '%s'";
  $args[] = $lastname2;
  $where[] = "middle_name = '%s'";
  $args[] = $lastname2;
  if (count($lastsplit) == 2) {
    $where[] = "(middle_name = '%s' AND last_name = '%s')";
    $args[] = $lastsplit[0];
    $args[] = $lastsplit[1];
  }
  $where[] = "last_name LIKE '%s'";
  $args[] = $lastname .'-';
  $where[] = "last_name LIKE '%s'";
  $args[] = $lastname .' ';
  if (count($fnames) >= 2) {
    $where[] = "last_name = '%s'";
    $args[] = $fnames[count($fnames) - 1] .' '. $lastname;
    $where[] = "last_name = '%s'";
    $args[] = $fnames[count($fnames) - 1] .'-'. $lastname;
  }
  $count = db_result(db_query('SELECT count(*) FROM {legislature_politician} WHERE ('. implode(' OR ', $where) .') AND govtrack_id <> 0', $args));
  if ($count == 1) {
    return array(db_result(db_query('SELECT politician_id FROM {legislature_politician} WHERE ('. implode(' OR ', $where) .') AND govtrack_id <> 0', $args)));
  }
  $result = db_query('SELECT politician_id, middle_name, nick_name, last_name, name_modifier, first_name FROM {legislature_politician} WHERE ('. implode(' OR ', $where) .') AND govtrack_id <> 0', $args);
  while ($match = db_fetch_object($result)) {
    if ($lastname != $match->lastname && $lastname == $match->middle_name) {
      $match->middle_name = '';
    }

    foreach (array('first_name', 'nick_name', 'name_modifier', 'middle_name') as $n) {
      $match->$n = str_replace(array('é', 'ú'), array('e', 'u'), $match->$n); // MySQL has little respect for proper accents
    }

    // Make list of possible first name strings
    $fntests = array();
    $fntests[] = array($match->first_name, $match->nick_name, $match->name_modifier);
    if ($match->nick_name != '') {
      $fntests[] = array($match->first_name, $match->middle_name, $match->name_modifier);
      $fntests[] = array($match->first_name, '('. $match->nick_name .')', $match->name_modifier);
      $fntests[] = array($match->first_name, '"'. $match->nick_name .'"', $match->name_modifier);
      $fntests[] = array($match->first_name, $match->middle_name, '('. $match->nick_name .')', $match->name_modifier);
      $fntests[] =