tripal_stock.module

Go to the documentation of this file.

<?php

require_once("includes/tripal_stock-administration.inc");
require_once("includes/other_module_api_functions.inc");
require_once("includes/tripal_stock-secondary_tables.inc");
require_once("includes/tripal_stock-properties.inc");
require_once("includes/tripal_stock-relationships.inc");
require_once("includes/tripal_stock-db_references.inc");
require_once("api/tripal_stock.api.inc");
function tripal_stock_menu() {
  $items = array();

  //Administrative settings menu-----------------
  $items['admin/tripal/tripal_stock'] = array(
    'title' => 'Stocks',
    'description' => 'Basic Description of Tripal Stock Module Functionality',
    'page callback' => 'theme',
    'page arguments' => array('tripal_stock_admin'),
    'access arguments' => array('administer tripal stocks'),
    'type' => MENU_NORMAL_ITEM
  );

  $items['admin/tripal/tripal_stock/configuration'] = array(
    'title' => 'Configuration',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('tripal_stock_admin'),
    'access arguments' => array('administer tripal stocks'),
    'type' => MENU_NORMAL_ITEM
  );

  // Adding Secondary Properties-----------------
  $items['node/%cs_node/properties'] = array(
    'title' => 'Add Properties & Synonyms',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_add_ALL_property_page',
    'page arguments' => array(1),
    'access arguments' => array('create chado_stock content'),
    'type' => MENU_CALLBACK
  );

  $items['node/%cs_node/db_references'] = array(
    'title' => 'Add Database References',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_add_ALL_dbreferences_page',
    'page arguments' => array(1),
    'access arguments' => array('create chado_stock content'),
    'type' => MENU_CALLBACK
  );

  $items['node/%cs_node/relationships'] = array(
    'title' => 'Add Relationships',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_add_ALL_relationships_page',
    'page arguments' => array(1),
    'access arguments' => array('create chado_stock content'),
    'type' => MENU_CALLBACK
  );

  //Edit/Deleting Secondary Properties-------------
  $items['node/%cs_node/edit_properties'] = array(
    'title' => 'Edit Properties',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_edit_ALL_properties_page',
    'page arguments' => array(1),
    'access arguments' => array('edit chado_stock content'),
    'type' => MENU_LOCAL_TASK,
    'weight' => 8,
  );

    $items['node/%cs_node/edit_relationships'] = array(
    'title' => 'Edit Relationships',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_edit_ALL_relationships_page',
    'page arguments' => array(1),
    'access arguments' => array('edit chado_stock content'),
    'type' => MENU_LOCAL_TASK,
    'weight' => 9,
  );

  $items['node/%cs_node/edit_db_references'] = array(
    'title' => 'Edit DB References',
    'description' => 'Settings for Chado Stocks',
    'page callback' => 'tripal_stock_edit_ALL_dbreferences_page',
    'page arguments' => array(1),
    'access arguments' => array('edit chado_stock content'),
    'type' => MENU_LOCAL_TASK,
    'weight' => 10,
  );
  return $items;
}

function cs_node_load($nid) {
  if (is_numeric($nid)) {
    $node = node_load($nid);
    if ($node->type == 'chado_stock') {
      return $node;
    }
  }

  return FALSE;
}

function tripal_stock_perm() {
  return array(
    'access chado_stock content',
    'create chado_stock content',
    'edit chado_stock content',
    'delete chado_stock content',
    'administer tripal stocks',
  );
}

function chado_stock_access($op, $node, $account) {
  if ($op == 'create') {
    if (!user_access('create chado_stock content', $account)) {
      return FALSE;
    }
  }
  if ($op == 'update') {
    if (!user_access('edit chado_stock content', $account)) {
      return FALSE;
    }
  }
  if ($op == 'delete') {
    if (!user_access('delete chado_stock content', $account)) {
      return FALSE;
    }
  }
  if ($op == 'view') {
    if (!user_access('access chado_stock content', $account)) {
      return FALSE;
    }
  }
  return NULL;
}

function tripal_stock_views_api() {
  return array(
    'api' => 2.0,
  );
}

function tripal_stock_theme() {
  return array(

    // Property edit forms--------------------------
    'tripal_stock_edit_ALL_properties_form' => array(
      'arguments' => array('form'),
      'function' => 'theme_tripal_stock_edit_ALL_properties_form',
    ),
    'tripal_stock_edit_ALL_db_references_form' => array(
      'arguments' => array('form'),
      'function' => 'theme_tripal_stock_edit_ALL_db_references_form',
    ),
    'tripal_stock_edit_ALL_relationships_form' => array(
      'arguments' => array('form'),
      'function' => 'theme_tripal_stock_edit_ALL_relationships_form',
    ),
    // Block Templates------------------------------
    'tripal_stock_base' => array(
      'arguments' => array('node' => NULL),
      'template' => 'tripal_stock_base',
    ),
    'tripal_stock_properties' => array(
      'arguments' => array('node' => NULL),
      'template' => 'tripal_stock_properties',
    ),
    'tripal_stock_references' => array(
      'arguments' => array('node' => NULL),
      'template' => 'tripal_stock_references',
    ),
    'tripal_stock_relationships' => array(
      'arguments' => array('node' => NULL),
      'template' => 'tripal_stock_relationships',
    ),
    'tripal_stock_synonyms' => array(
      'arguments' => array('node' => NULL),
      'template' => 'tripal_stock_synonyms',
    ),
    'tripal_stock_collections' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_stock_collections',
    ),
    'tripal_stock_collections' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_stock_collections',
    ),
    'tripal_stock_phenotypes' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_stock_phenotypes',
    ),
    'tripal_stock_genotypes' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_stock_genotypes',
    ),
    'tripal_stock_locations' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_stock_locations',
    ),    
    'tripal_organism_stocks' => array(
       'arguments' => array('node' => NULL),
       'template' => 'tripal_organism_stocks',
    ),    
    'tripal_stock_admin' => array(
      'template' => 'tripal_stock_admin',  
      'arguments' =>  array(NULL),  
      'path' => drupal_get_path('module', 'tripal_stock') . '/theme', 
    ),
  );
}

function tripal_stock_node_info() {
  return array(
    'chado_stock' => array(
      'name' => t('Stock'),
      'module' => 'chado_stock',
      'description' => t('A Chado Stock is a collection of material that can be sampled and have experiments performed on it.'),
      'has_title' => TRUE,
      'has_body' => FALSE,
    ),
  );
}

function chado_stock_load($node) {

  // get the stock details from chado
  $stock_id = chado_get_id_for_node('stock', $node);
  
  // build the variable with all the stock details
  $values = array('stock_id' => $stock_id);
  $stock = tripal_core_generate_chado_var('stock', $values);
  
  // add this to the node
  $additions = new stdClass();
  $additions->stock = $stock;
  return $additions;
}


function chado_stock_form($node, $form_state) {

  // Expand all fields needed
  $fields_needed = array('stock.uniquename', 'stock.name', 'stock.stock_id', 'stock.type_id', 'stock.organism_id', 'stock.description', 'stock.dbxref_id', 'dbxref.accession', 'dbxref.description', 'dbxref.db_id', 'db.db_id');
  foreach ($fields_needed as $field_name) {
    // Check to see if it's excluded and expand it if so
    if ($node->expandable_fields) {
      if (in_array($field_name, $node->expandable_fields)) {
        $node = tripal_core_expand_chado_vars($node, 'field', $field_name);
      }
    }
  }

  // This defines the path for the next step in a simulated multipart form
  // NOTE: The %node gets replaced with the nid in insert
  $form['next_step_path'] = array(
    '#type' => 'hidden',
    '#value' => 'node/%node/properties'
  );

  // If you don't want a multipart form set this to false
  // Will then do default redirect (to new node) on submit
  $form['simulate_multipart'] = array(
    '#type' => 'textfield',
    '#attributes' => array('style' => "display:none"),
    '#default_value' => TRUE
  );

  if (!isset($node->stock->uniquename)) {
    $form['progress'] = array(
      '#type' => 'item',
      '#value' => tripal_stock_add_chado_properties_progress('main')
    );
  }

  $form['names'] = array(
    '#type' => 'fieldset',
    '#title' => t('Stock Name')
  );

  $form['names']['title'] = array(
    '#type' => 'textfield',
    '#title' => t('Name'),
    '#default_value' => $node->stock->name,
    '#required'       => TRUE
  );

  $form['names']['uniquename'] = array(
    '#type' => 'textfield',
    '#title' => t('Unique Name'),
    '#default_value' => $node->stock->uniquename,
    '#required'       => TRUE
  );

  $form['names']['stock_id'] = array(
    '#type' => 'hidden',
    '#value' => $node->stock->stock_id
  );

  $form['details'] = array(
    '#type' => 'fieldset',
    '#title' =>  t('Stock Details')
  );

  $type_options = tripal_cv_get_cvterm_options( variable_get('chado_stock_types_cv', 'NULL') );
  $type_options[0] = 'Select a Type';
  if ($node->nid == '') {
  $type_default = 0; }
  else { $type_default = $node->stock->type_id->cvterm_id; }
  $form['details']['type_id'] = array(
    '#type' => 'select',
    '#title' => t('Type of Stock'),
    '#options' => $type_options,
    '#default_value' => $type_default,
    '#required'    => TRUE,
  );

 
  // get the list of organisms
  $sql = "SELECT * FROM {Organism} ORDER BY genus, species";
  $org_rset = chado_query($sql);
  $organisms = array();
  $organisms[''] = '';
  while ($organism = db_fetch_object($org_rset)) {
    $organisms[$organism->organism_id] = "$organism->genus $organism->species ($organism->common_name)";
  }
  $form['details']['organism_id'] = array(
    '#type' => 'select',
    '#title' => t('Source Organism for stock'),
    '#default_value' => $organism_default,
    '#options' => $organisms,
    '#required'    => TRUE
  );


  $form['details']['stock_description'] = array(
    '#type' => 'textarea',
    '#title' => t('Notes'),
    '#default_value' => $node->stock->description,
    '#description' => t('Briefly enter any notes on the above stock. This should not include phenotypes or genotypes.'),
  );

  $form['database_reference'] = array(
    '#type' => 'fieldset',
    '#title' => t('Stock Database Reference')
  );

  $form['database_reference']['accession'] = array(
    '#type' => 'textfield',
    '#title' => t('Accession'),
    '#default_value' => $node->stock->dbxref_id->accession
  );

  $form['database_reference']['db_description'] = array(
    '#type' => 'textarea',
    '#title' => t('Description of Database Reference'),
    '#default_value' => $node->stock->dbxref_id->description,
    '#description' => t('Optionally enter a description about the database accession.')
  );

  $db_options = tripal_db_get_db_options();
  $db_options[0] = 'Select a Database';
  if ($node->nid == '') {
  $db_default = 0; }
  else { $db_default = $node->stock->dbxref_id->db_id->db_id; }
  $form['database_reference']['database'] = array(
    '#type' => 'select',
    '#title' => t('Database'),
    '#options' => $db_options,
    '#default_value' => $db_default
  );

  return $form;
}

function chado_stock_validate($node, &$form) {

  $int_in_chado_sql = "SELECT count(*) as count FROM %s WHERE %s=%d";
  $string_in_chado_sql = "SELECT count(*) as count FROM %s WHERE %s='%s'";

  // Validate Uniquename only if add
  if (empty($node->stock_id)) {
    $chado_row = db_fetch_object(chado_query("SELECT * FROM {stock} WHERE uniquename='" . $node->uniquename . "'"));
    if (!empty($chado_row->stock_id)) {
      $drupal_row = db_fetch_object(db_query("SELECT * FROM {chado_stock} WHERE stock_id=" . $chado_row->stock_id));
      if (!empty($drupal_row->nid)) {
        $link = l('node/' . $drupal_row->nid, $node->uniquename);
        form_set_error('uniquename', "There is already a stock with that uniquename $link. Please enter another uniquename.");
      }
      else {
        form_set_error('uniquename', "There is already a stock with that uniquename (although it's not sync'd with drupal). Please enter another uniquename.");
      }
    }
  }


  // Check Type of Stock is valid cvterm_id in chado ( $form['values']['details']['type_id'] )
  if ( $node->type_id == 0) {
    form_set_error('type_id', 'Please select a type of stock.');
  }
  else {
    $num_rows = db_fetch_object(chado_query($int_in_chado_sql, 'cvterm', 'cvterm_id', $node->type_id));
    if ( $num_rows->count != 1) {
      form_set_error('type_id', "The type you selected is not valid. Please choose another one. (CODE:$num_rows)"); }
  }

  // Check Source Organism is valid organism_id in chado ( $form['values']['details']['organism_id'] )
  if ( $node->organism_id == 0) {
    form_set_error('organism_id', 'Please select a source organism for this stock');
  }
  else {
    $num_rows = db_fetch_object(chado_query($int_in_chado_sql, 'organism', 'organism_id', $node->organism_id));
    if ( $num_rows->count != 1 ) {
      form_set_error('organism_id', "The organism you selected is not valid. Please choose another one. (CODE:$num_rows)"); }
  }

  // Check if Accession also database
  if ($node->accession != '') {
    if ($node->database == 0) {
      // there is an accession but no database selected
      form_set_error('database', 'You need to enter both a database and an accession for that database in order to add a database reference.');
    }
  }
  else {
    if ($node->database > 0) {
      // there is a database selected but no accession
      form_set_error('accession', 'You need to enter both a database and an accession for that database in order to add a database reference.');
    }
  }

  // Check database is valid db_id in chado ( $form['values']['database_reference']['database'] )
  if ( $node->database > 0) {
    $num_rows = db_fetch_object(chado_query($int_in_chado_sql, 'db', 'db_id', $node->database));
    if ($num_rows->count != 1) {
      form_set_error('database', 'The database you selected is not valid. Please choose another one.'); }
  }

}

function chado_stock_insert($node) {

  //If the chado stock exists
  // then don't create but simply link to node
  if ($node->chado_stock_exists) {
    if (!empty($node->stock_id)) {
      db_query(
        "INSERT INTO {chado_stock} (nid, vid, stock_id) "
        ."VALUES (%d, %d, %d)",
        $node->nid,
        $node->vid,
        $node->stock_id
      );
    }
    return $node;
  }

  // create dbxref
  if ( !empty($node->accession) ) {
    if ( !empty($node->database) ) {
      $values = array(
        'db_id' => $node->database,
        'accession' => $node->accession,
      );
      if (!tripal_core_chado_select('dbxref', array(dbxref_id), $values)) {
        $values['description'] = $node->db_description;
        $values['version'] = '1';
        $dbxref_status = tripal_core_chado_insert('dbxref', $values);
        if (!$dbxref_status) {
          drupal_set_message(t('Unable to add database reference to this stock.'), 'warning');
          watchdog('tripal_stock',
            'Insert Stock: Unable to create dbxref where values:%values',
            array('%values' => print_r($values, TRUE)),
            WATCHDOG_WARNING
          );
        }
      }
      else { $dbxref_status = 1; }
    }
  }

  // create stock
  if ($dbxref_status) {
    $values = array(
      'dbxref_id' => array(
        'db_id' => $node->database,
        'accession' => $node->accession
      ),
      'organism_id' => $node->organism_id,
      'name' => $node->title,
      'uniquename' => $node->uniquename,
      'description' => $node->stock_description,
      'type_id' => $node->type_id
    );
    $stock_status = tripal_core_chado_insert('stock', $values);
  }
  else {
    $values = array(
      'organism_id' => $node->organism_id,
      'name' => $node->title,
      'uniquename' => $node->uniquename,
      'description' => $node->stock_description,
      'type_id' => $node->type_id
    );
    $stock_status = tripal_core_chado_insert('stock', $values);
  }

  // create drupal chado_stock entry
  if ($stock_status) {
    $values = array(
      'organism_id' => $node->organism_id,
      'uniquename' => $node->uniquename,
      'type_id' => $node->type_id
    );
    $chado_stock = tripal_core_chado_select('stock', array('stock_id'), $values);
    if (!empty($chado_stock[0]->stock_id)) {
      db_query(
        "INSERT INTO {chado_stock} (nid, vid, stock_id) "
        ."VALUES (%d, %d, %d)",
        $node->nid,
        $node->vid,
        $chado_stock[0]->stock_id
      );

        //Move on to next stage of Stock Creation based on next_stage_path field
      if ($node->simulate_multipart) {
        $next_stage_path = preg_replace('/%node/', $node->nid, $node->next_step_path);
        $_REQUEST['destination'] = $next_stage_path;
      }
    }
    else {
      drupal_set_message(t('Error during stock creation.'), 'error');
      watchdog('tripal_stock',
        'Insert Stock: Unable to find newly created stock where values:%values',
        array('%values' => print_r($values, TRUE)),
        WATCHDOG_ERROR
      );
      return FALSE;
    }
  }
  else {
    drupal_set_message(t('Error during stock creation.'), 'error');
    watchdog('tripal_stock',
      'Insert Stock: Unable to create stock where values:%values',
      array('%values' => print_r($values, TRUE)),
      WATCHDOG_WARNING
    );
    return FALSE;
  }

}

function chado_stock_update($node) {

  if ($node->revision) {
    // there is no way to handle revisions in Chado but leave
    // this here just to make not we've addressed it.
  }
//  if ($node->revision) {
//    chado_stock_insert($node);
//  }
//  else {

  //update dbxref
  if ($node->database) {
    if ($node->accession) {
      $dbxref_mode = '';
      $stock = tripal_core_chado_select(
        'stock',
        array('dbxref_id', 'type_id'),
        array('stock_id' => $node->stock_id)
      );

      if ($stock[0]->dbxref_id) {
        $values = array(
          'db_id' => $node->database,
          'accession' => $node->accession,
          'description' => $node->db_description
        );
        $dbxref_status = tripal_core_chado_update(
          'dbxref',
          array('dbxref_id' => $stock[0]->dbxref_id),
          $values
        );
        $dbxref_mode = 'Update';
      }
      else {
        if ($stock[0]->type_id) {
          //create the dbxref
          //used the type_id as a control to check we have a stock but not a dbxref
          $values = array(
            'db_id' => $node->database,
            'accession' => $node->accession,
            'description' => $node->db_description,
            'version' => '1',
          );
          $dbxref_status = tripal_core_chado_insert(
            'dbxref',
            $values
          );
          $dbxref_mode = 'Create';
        }
        else {
          drupal_set_message(t('Unable to find stock to Update'), 'error');
          watchdog(
            'tripal_stock',
            'Stock Update: Unable to find stock to update using values: %values',
            array('%values', print_r($values, TRUE)),
            WATCHDOG_ERROR
          );
          return FALSE;
        }
      }
    }
  }

  if (!$dbxref_status) {
    watchdog(
      'tripal_stock',
      'Stock Update: Unable to %mode main stock dbxref with values: %values',
      array('%values' => print_r($values, TRUE), '%mode' => $dbxref_mode),
      WATCHDOG_WARNING
    );
  }

  //can't change stock id which is all thats stored in drupal thus only update chado
  $update_values = array(
    'organism_id' => $node->organism_id,
    'name' => $node->title,
    'uniquename' => $node->uniquename,
    'description' => $node->stock_description,
    'type_id' => $node->type_id,
  );
  if ($dbxref_status) {
    $update_values['dbxref_id'] = array(
      'db_id' => $node->database,
      'accession' => $node->accession
    );
  }
  $status = tripal_core_chado_update(
    'stock',
    array('stock_id' => $node->stock_id),
    $update_values
  );

  if (!$status) {
    drupal_set_message(t('Unable to update stock'), 'error');
    watchdog(
      'tripal_stock',
      'Stock Update: Unable to update stock using match values: %mvalues and update values: %uvalues',
      array('%mvalues' => print_r(array('stock_id' => $node->stock_id), TRUE), '%uvalues' => print_r($update_values, TRUE)),
      WATCHDOG_ERROR
    );
  }
}

function chado_stock_delete($node) {

  // Set stock in chado: is_obsolete = TRUE
  chado_query(
    "DELETE FROM {stock} WHERE stock_id=%d",
    $node->stock->stock_id
  );

  //remove drupal node and all revisions
  db_query(
    "DELETE FROM {chado_stock} WHERE nid=%d",
    $node->nid
  );
}

function tripal_stock_block($op = 'list', $delta = 0, $edit=array()) {
  switch ($op) {
    case 'list':
      $blocks['base']['info'] = t('Tripal Stock Details');
      $blocks['base']['cache'] = BLOCK_NO_CACHE;

      $blocks['properties']['info'] = t('Tripal Stock Properties');
      $blocks['properties']['cache'] = BLOCK_NO_CACHE;

      $blocks['references']['info'] = t('Tripal Stock References');
      $blocks['references']['cache'] = BLOCK_NO_CACHE;

      $blocks['relationships_as_object']['info'] = t('Tripal Stock Relationships');
      $blocks['relationships_as_object']['cache'] = BLOCK_NO_CACHE;

      $blocks['synonyms']['info'] = t('Tripal Stock Synonyms');
      $blocks['synonyms']['cache'] = BLOCK_NO_CACHE;
      
      $blocks['collections']['info'] = t('Tripal Stock Collections');
      $blocks['collections']['cache'] = BLOCK_NO_CACHE;
      
      $blocks['phenotypes']['info'] = t('Tripal Stock Phenotypes');
      $blocks['phenotypes']['cache'] = BLOCK_NO_CACHE;
      
      $blocks['genotypes']['info'] = t('Tripal Stock Genotypes');
      $blocks['genotypes']['cache'] = BLOCK_NO_CACHE;
      
      $blocks['locations']['info'] = t('Tripal Stock Locations');
      $blocks['locations']['cache'] = BLOCK_NO_CACHE;
      
      $blocks['orgstocks']['info'] = t('Tripal Organism Stocks');
      $blocks['orgstocks']['cache'] = BLOCK_NO_CACHE;
      
      

      return $blocks;

    case 'view':
      if (user_access('access chado_stock content') and arg(0) == 'node' and is_numeric(arg(1))) {
      $nid = arg(1);
      $node = node_load($nid);

      $block = array();
      switch ($delta) {
        case 'base':
          $block['subject'] = t('Stock Details');
          $block['content'] = theme('tripal_stock_base', $node);
          break;

        case 'properties':
          $block['subject'] = t('Properties');
          $block['content'] = theme('tripal_stock_properties', $node);
          break;

        case 'references':
          $block['subject'] = t('References');
          $block['content'] = theme('tripal_stock_references', $node);
          break;

        case 'relationships':
          $block['subject'] = t('Relationships');
          $block['content'] = theme('tripal_stock_relationships', $node);
          break;

        case 'synonyms':
          $block['subject'] = t('Synonyms');
          $block['content'] = theme('tripal_stock_synonyms', $node);
          break;
          
        case 'collections':
          $block['subject'] = t('Stock Collections');
          $block['content'] = theme('tripal_stock_collections', $node);
          break;
          
        case 'phenotypes':
          $block['subject'] = t('Stock Phenotypes');
          $block['content'] = theme('tripal_stock_phenotypes', $node);
          break;
          
        case 'genotypes':
          $block['subject'] = t('Stock Genotypes');
          $block['content'] = theme('tripal_stock_genotypes', $node);
          break;
          
        case 'locations':
          $block['subject'] = t('Stock Locations');
          $block['content'] = theme('tripal_stock_locations', $node);
          break;
          
        case 'orgstocks':
          $block['subject'] = t('Organism Stocks');
          $block['content'] = theme('tripal_organism_stocks', $node);
          break;

      }
      return $block;
    }
  }
}

function tripal_stock_preprocess_tripal_stock_relationships(&$variables) {
  // we want to provide a new variable that contains the matched stocks.
  $stock = $variables['node']->stock;
   
  // normally we would use tripal_core_expand_chado_vars to expand our
  // organism object and add in the relationships, however whan a large
  // number of relationships are present this significantly slows the
  // query, therefore we will manually perform the query
  $sql = "
    SELECT 
      S.name, S.uniquename, S.stock_id, CS.nid, 
      CVT.name as rel_type, CVTs.name as obj_type,
      SR.value
    FROM stock_relationship SR
      INNER JOIN stock S on SR.object_id = S.stock_id
      INNER JOIN cvterm CVT on SR.type_id = CVT.cvterm_id
      INNER JOIN cvterm CVTs on S.type_id = CVTs.cvterm_id
      LEFT JOIN chado_stock CS on S.stock_id = CS.stock_id
    WHERE SR.subject_id = %d      
  ";
  $as_subject = chado_query($sql, $stock->stock_id);
  $sql = "
    SELECT 
      S.name, S.uniquename,  S.stock_id, CS.nid, 
      CVT.name as rel_type, CVTs.name as sub_type,
      SR.value
    FROM stock_relationship SR
      INNER JOIN stock S on SR.subject_id = S.stock_id
      INNER JOIN cvterm CVT on SR.type_id = CVT.cvterm_id
      INNER JOIN cvterm CVTs on S.type_id = CVTs.cvterm_id
      LEFT JOIN chado_stock CS on S.stock_id = CS.stock_id
    WHERE SR.object_id = %d      
  ";
  $as_object = chado_query($sql, $stock->stock_id);   
  
  // combine both object and subject relationshisp into a single array
  $relationships = array();
  $relationships['object'] = array();
  $relationships['subject'] = array();
  
  // iterate through the object relationships
  while ($relationship = db_fetch_object($as_object)) {
     
     // get the relationship and child types
     $rel_type = t(preg_replace('/_/', " ", $relationship->rel_type));
     $sub_type = t(preg_replace('/_/', " ", $relationship->sub_type));
     
     if (!array_key_exists($rel_type, $relationships['object'])) {
       $relationships['object'][$rel_type] = array();   
     }
     if (!array_key_exists($sub_type, $relationships['object'][$rel_type])) {
       $relationships['object'][$rel_type][$sub_type] = array();   
     }
     $relationships['object'][$rel_type][$sub_type][] = $relationship;     
  }
  
  // now add in the subject relationships
  while ($relationship = db_fetch_object($as_subject)) {
     
     // get the relationship and child types
     $rel_type = t(preg_replace('/_/', " ", $relationship->rel_type));
     $obj_type = t(preg_replace('/_/', " ", $relationship->obj_type));
     
     if (!array_key_exists($rel_type, $relationships['subject'])) {
       $relationships['subject'][$rel_type] = array();   
     }
     if (!array_key_exists($obj_type, $relationships['subject'][$rel_type])) {
       $relationships['subject'][$rel_type][$obj_type] = array();   
     }
     $relationships['subject'][$rel_type][$obj_type][] = $relationship;     
  }
  
  
  $stock->all_relationships = $relationships;

}
function tripal_stock_nodeapi(&$node, $op, $teaser, $page) {

  switch ($op) {
    // Note that this function only adds stock view to an organism/feature
    // node.
    case 'view':
      // add the stock to the organism/feature search indexing
      if ($node->build_mode == NODE_BUILD_SEARCH_INDEX) {

      }
      elseif ($node->build_mode == NODE_BUILD_SEARCH_RESULT) {

      }
      else {
        switch ($node->type) {
          case 'chado_organism':
            // Show stock if the organism/feature is not at teaser view
            $node->content['tripal_organism_stocks'] = array(
              '#value' => theme('tripal_organism_stocks', $node),
            );
            break;          
        }
      }
      break;
  }
}

function tripal_stock_preprocess_tripal_organism_stocks(&$variables) {
  $organism = $variables['node']->organism;

  if (!$organism) {
    return;
  }
  
  // get the stocks for this organism
  $sql  = "SELECT S.name, S.stock_id, S.uniquename, CVT.name as cvname ".
          "FROM {stock} S ".
          "  INNER JOIN {cvterm} CVT on S.type_id = CVT.cvterm_id ".
          "WHERE organism_id = %s".
          "ORDER BY stock_id ASC";

  // the counting SQL
  $csql  = "SELECT count(*) ".
           "FROM {stock} S".
           "  INNER JOIN {cvterm} CVT on S.type_id = CVT.cvterm_id ".
           "WHERE organism_id = %s ".
           "GROUP BY organism_id ";

  $previous_db = tripal_db_set_active('chado');  // use chado database
  $org_stocks = pager_query($sql, 25, 0, $csql, $organism->organism_id);
  tripal_db_set_active($previous_db);  // now use drupal database
  
  // get the pager and reformat so it shows the right block
  $pager = theme('pager');  

  // prepare the query that will lookup node ids
  $sql = "SELECT nid FROM {chado_stock} ".
         "WHERE stock_id = %d";
  $i=0;
  $stocks = array();
  while ($stock = db_fetch_object($org_stocks)) {
    $node = db_fetch_object(db_query($sql, $stock->stock_id));
    $stock->nid = $node->nid;
    $stocks[$i++] = $stock;
  }
  
  $organism->stock_browser =  array('stocks' => $stocks, 'pager' => $pager, 'enabled' => TRUE );
}