tripal/base/tripal_bulk_loader/tripal_bulk_loader.module

<?php

include('tripal_bulk_loader.loader.inc');

/**
 * Implements hook_init
 * Used to add stylesheets and javascript files to the header
 */
function tripal_bulk_loader_init(){
	// Add javascript and style sheet
	drupal_add_css(drupal_get_path('theme', 'tripal').'/css/tripal_bulk_loader.css');
	drupal_add_js(drupal_get_path('theme', 'tripal').'/js/tripal_bulk_loader.js');
}

/**
 * Implements hook_menu
 */
function tripal_bulk_loader_menu() {
  $items = array();
   // Show all loaders
   $items['tripal_bulk_loaders'] = array(
     'title' => 'Tripal Bulk Loaders',
     'description' => 'Tripal bulk loaders for loading tab-delimited file into chado database',
     'page callback' => 'tripal_bulk_loader_list',
     'access arguments' => array('access tripal_bulk_loader'),
     'type' => MENU_NORMAL_ITEM,
   );
   // Admin page to create the template
   $items['admin/tripal/tripal_bulk_loader_template'] = array(
      'title' => 'Bulk Loader Template',
      'description' => 'Templates for loading tab-delimited data',
    	'page callback' => 'tripal_bulk_loader_admin_template',
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		  'file' => 'tripal_bulk_loader.admin.inc',
	);
	// Create/Edit Template -------
  $items['admin/tripal/tripal_bulk_loader_template/create'] = array(
      'title' => 'Create Bulk Loader Template',
      'description' => 'Create loader template for loading tab-delimited data',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_modify_template_base_form', 'create'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		'file' => 'tripal_bulk_loader.admin.inc',
  );
  $items['admin/tripal/tripal_bulk_loader_template/edit'] = array(
      'title' => 'Edit Bulk Loader Template',
      'description' => 'Edit loader template for loading tab-delimited data',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_modify_template_base_form', 'edit'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		  'file' => 'tripal_bulk_loader.admin.inc',  
  );
	$items['admin/tripal/tripal_bulk_loader_template/edit_record'] = array(
      'title' => 'Edit Template Record',
      'description' => 'Edit a record in an existing tripal bulk loader template.',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_edit_template_record_form'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_CALLBACK,   
		'file' => 'tripal_bulk_loader.admin.inc',
	);
	$items['admin/tripal/tripal_bulk_loader_template/add_field'] = array(
      'title' => 'Add Template Field',
      'description' => 'Add a template field to an existing tripal bulk loader template.',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_add_template_field_form'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_CALLBACK,   
		'file' => 'tripal_bulk_loader.admin.inc',
	);
	$items['admin/tripal/tripal_bulk_loader_template/edit_field'] = array(
      'title' => 'Edit Template Field',
      'description' => 'Edit an existing field from a tripal bulk loader template.',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_edit_template_field_form'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_CALLBACK,   
		'file' => 'tripal_bulk_loader.admin.inc',
	);
	// Delete Template -----
	$items['admin/tripal/tripal_bulk_loader_template/delete'] = array(
      'title' => 'Delete Bulk Loader Template',
      'description' => 'Delete bulk loader template',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_delete_template_base_form'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		'file' => 'tripal_bulk_loader.admin.inc',
	);
	// Import/Export ---------
  $items['admin/tripal/tripal_bulk_loader_template/import'] = array(
      'title' => 'Import Bulk Loader Template',
      'description' => 'Import Loaders',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_import_export_template_form', 'import'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		  'file' => 'tripal_bulk_loader.admin.inc',  
  );	
  $items['admin/tripal/tripal_bulk_loader_template/export'] = array(
      'title' => 'Export Bulk Loader Template',
      'description' => 'Export Loaders',
      'page callback' => 'drupal_get_form',
      'page arguments' => array('tripal_bulk_loader_import_export_template_form', 'export'),
      'access arguments' => array('administer site configuration'),
      'type' => MENU_NORMAL_ITEM,   
		  'file' => 'tripal_bulk_loader.admin.inc',  
  );	
	// AHAH ---------
	$items['node/add/tripal-bulk-loader/ahah'] = array(
      'page callback' => 'tripal_bulk_loader_create_node_ahah',
      'access arguments' => array('create tripal_bulk_loader'),
      'type' => MENU_CALLBACK,   
	);
	$items['admin/tripal/tripal_bulk_loader_template/add_field_ahah'] = array(
      'page callback' => 'tripal_bulk_loader_add_field_ahah',
      'access arguments' => array('administer site configuration'),
      'type' => MENU_CALLBACK,   
		  'file' => 'tripal_bulk_loader.admin.inc',	
	);
	$items['admin/tripal/tripal_bulk_loader_template/edit_field_ahah'] = array(
      'page callback' => 'tripal_bulk_loader_edit_field_ahah',
      'access arguments' => array('administer site configuration'),
      'type' => MENU_CALLBACK,   
		  'file' => 'tripal_bulk_loader.admin.inc',	
	);
	
  return $items;
}

/**
 * Implements hook_theme
 */
function tripal_bulk_loader_theme() {
  return array(
    'tripal_bulk_loader_template' => array(
    	'arguments'=> array('template_id' => NULL),
    	'template' => 'tripal_bulk_loader_template'
    ),
    'tripal_bulk_loader_modify_template_base_form' => array(
    	'arguments' => array('form' => NULL),
    	'template' => 'tripal_bulk_loader_modify_template_base_form',
    ),
    'tripal_bulk_loader_edit_template_field_form' => array(
      'arguments' => array('form' => NULL),
      'template' => 'tripal_bulk_loader_edit_template_field_form',
    ),
    'tripal_bulk_loader_add_template_field_form' => array(
      'arguments' => array('form' => NULL),
      'template' => 'tripal_bulk_loader_add_template_field_form',
    ),
  );
}

/**
 *  Implements hook_access
 */
function tripal_bulk_loader_access($op, $node, $account){
	if ($op == 'create') {
		if(!user_access('create tripal_bulk_loader', $account)){
         return FALSE;
      }
	}
	if ($op == 'update') {
		if (!user_access('edit tripal_bulk_loader', $account)) {
			return FALSE;
		}
	}
	if ($op == 'delete') {
		if (!user_access('delete tripal_bulk_loader', $account)) {
			return FALSE;
		}
	}
	if ($op == 'view') {
		if (!user_access('access tripal_bulk_loader', $account)) {
			return FALSE;
		}
	}
	return NULL;
}

/**
 * Implements hook_perm
 */
function tripal_bulk_loader_perm(){
	return array(
      'access tripal_bulk_loader',
      'create tripal_bulk_loader',
      'delete tripal_bulk_loader',
      'edit tripal_bulk_loader',
	);
}

/**
 * Creates a listing page for all bulk loading jobs
 */
function tripal_bulk_loader_list () {
  $num_results_per_page = 50;
  $output = '';
  
  $header = array('','Status','Loader','File');
  $rows = array();
  
  $query = 'SELECT * FROM {tripal_bulk_loader} l '
    .'LEFT JOIN {node} n ON n.nid = l.nid '
    .'LEFT JOIN {tripal_bulk_loader_template} t ON t.template_id = cast(l.template_id as integer)';
  $resource = pager_query($query, $num_results_per_page, 0, NULL);
  while ($r = db_fetch_object($resource)) {
    $row = array(
      l($r->title, 'node/'.$r->nid),
      $r->job_status,
      $r->name,
      $r->file
    );
    $rows[] = $row;
  }
  
  $output .= theme('table', $header, $rows);
  return $output;
  
}

//////////////////////////////////////////////////////////////////////////////////////////////
// Node Functions
//////////////////////////////////////////////////////////////////////////////////////////////

/**
 * Implements hook_node_info
 */
function tripal_bulk_loader_node_info() {
	$nodes = array();
	$nodes['tripal_bulk_loader'] = array(
      'name' => t('Bulk Loading Job'),
      'module' => 'tripal_bulk_loader',
      'description' => t('A bulk loader for inserting tab-delimited data into chado database'),
      'has_title' => TRUE,
      'has_body' => FALSE,
      'locked' => TRUE
	);
	return $nodes;
}

/**
 * Implements node_form
 * Used to gather the extra details stored with a Bulk Loading Job Node
 */
function tripal_bulk_loader_form ($node, $form_state){
  $form = array();
  
  if (isset($form_state['values'])) {
    $node = $form_state['values'] + (array)$node;
    $node = (object) $node;
  }
  
  $sql = "SELECT * FROM {tripal_bulk_loader_template}";
  $results = db_query($sql);
  $templates = array ();
  while ($template = db_fetch_object ($results)) {
    $templates [$template->template_id] = $template->name;
  }
  
  if (!$templates) {
    $form['label'] = array(
    '#type' => 'item',
      '#description' => t("Loader template needs to be created before any bulk loader can be added. Go to 'Tripal Management > Bulk Loader Template' to create the template."),
      '#weight'        => -10,
    );
    
    return $form;
  }
  
  $form['loader'] = array(
    '#type' => 'fieldset',
    '#title' => t('Basic Details'),
  );
  
  $form['loader']['loader_name'] = array(
    '#type'          => 'textfield',
    '#title'         => t('Loading Job Name'),
    '#weight'        => -10,
    '#required'      => TRUE,
    '#default_value' => $node->loader_name
  );
  
  $form['loader']['template_id'] = array(
    '#type' => 'select',
    '#title' => t('Template'),
    '#description'   => t('Please specify a template for this loader'),
    '#options'       => $templates,
    '#weight'        => -9,
    '#required'      => TRUE,
    '#default_value' => $node->template_id,
    '#ahah' => array(
      'path' => 'node/add/tripal-bulk-loader/ahah',
      'wrapper' => 'set-constants',
      'event' => 'change',
      'method' => 'replace',
      'effect' => 'fade',
    ),
  );
  
  $form['loader']['file']= array(
    '#type'          => 'textfield',
    '#title'         => t('Data File'),
    '#description'   => t('Please specify the data file to be loaded.'),
    '#weight'        => -8,
    '#default_value' => $node->file
  );
  
  $form['loader']['has_header'] = array(
    '#type' => 'radios',
    '#title' => t('File has a Header'),
    '#options' => array( 1 => 'Yes', 2 => 'No'),
    '#weight' => -7,
    '#default_value' => $node->file_has_header,
  );
  
  return $form;
}

/**
 * AHAH Function: Replace $form['constants'] in tripal_bulk_loader_form
 *
 * @return
 *  JSON Data printed to the screen
 */
function tripal_bulk_loader_create_node_ahah () {

  // The form is generated in an include file which we need to include manually.
  include_once 'modules/node/node.pages.inc';
  
  $form_state = array('storage' => NULL, 'submitted' => FALSE);
  $form_build_id = $_POST['form_build_id'];
  $form = form_get_cache($form_build_id, $form_state);
  $args = $form['#parameters'];
  $form_id = array_shift($args);
  $form_state['post'] = $form['#post'] = $_POST;
  
  // Enable the submit/validate handlers to determine whether AHAH-submittted.
  $form_state['ahah_submission'] = TRUE;
  
  $form['#programmed'] = $form['#redirect'] = FALSE;
  drupal_process_form($form_id, $form, $form_state);
  $form = drupal_rebuild_form($form_id, $form_state, $args, $form_build_id);
  
  $form_element = $form['exposed_fields'];
  // Remove the wrapper so we don't double it up.
  unset($form_element['#prefix'], $form_element['#suffix']);

  $output = theme('status_messages');
  $output .= drupal_render($form_element);

  // Final rendering callback.
  print drupal_json(array('status' => TRUE, 'data' => $output));
  exit();
  
}

/**
 * Implements node_load
 */
function tripal_bulk_loader_load($node){
	$sql = "SELECT * FROM {tripal_bulk_loader} WHERE nid = %d";
	$node = db_fetch_object(db_query($sql, $node->nid));

	$node->title = 'Bulk Loading Job: '.$node->loader_name;
	
	// Add the loader template
	$sql = "SELECT * FROM {tripal_bulk_loader_template} WHERE template_id=%d";
	$results = db_fetch_object(db_query($sql, $node->template_id));
	$template = unserialize($results->template_array);
	$node->template = $results;
	$node->template->template_array = $template;

  // Add inserted records
  $sql = 'SELECT * FROM {tripal_bulk_loader_inserted} WHERE nid=%d';
  $resource = db_query($sql,$node->nid);
  while ($r = db_fetch_object($resource)) {
    $r->num_inserted = sizeof(preg_split('/,/',$r->ids_inserted)); 
    $node->inserted_records->{$r->table_inserted_into} = $r;
  }
  
  // Add constants
  $sql = 'SELECT * FROM {tripal_bulk_loader_constants} WHERE nid=%d ORDER BY record_id, field_id';
  $resource = db_query($sql,$node->nid);
  while ($r = db_fetch_object($resource)) {
    $node->constants[$r->record_id][$r->field_id] = array(
      'chado_table'=>$r->chado_table, 
      'chado_field'=>$r->chado_field, 
      'record_id'=>$r->record_id, 
      'field_id'=>$r->field_id, 
      'value'=>$r->value
    );
  }
  
	return $node;
}

/**
 * Implements node_insert
 * Insert the data from the node form on Create content
 */
function tripal_bulk_loader_insert ($node) {
  
  // Insert into tripal_bulk_loader
  $sql = "INSERT INTO {tripal_bulk_loader} (nid, loader_name, template_id, file, file_has_header, job_status) VALUES (%d, '%s', %d, '%s', %d, '%s')";
  db_query($sql, $node->nid, $node->loader_name, $node->template_id, $node->file, $node->has_header, 'Initialized');
	
	// Update title
	$node->title =$node->loader_name;
	drupal_write_record('node',$node,'nid');
	drupal_write_record('node_revision',$node,'nid');	
  
  drupal_set_message('After reviewing the details, please Submit this Job (by clicking the "Submit Job" button below). No data will be loaded until the submitted job is reached in the queue.');
  
}

/**
 * Implements node_delete
 * Deletes the data when the delete button on the node form is clicked
 */
function tripal_bulk_loader_delete ($node) {
	$sql = "DELETE FROM {tripal_bulk_loader} WHERE nid = %d";
	db_query($sql, $node->nid);
}

/**
 * Implements node_update
 * Updates the data submitted by the node form on edit
 */
function tripal_bulk_loader_update ($node) {
  
  // Update tripal_bulk_loader
	$sql = "UPDATE {tripal_bulk_loader} SET nid = %d, loader_name = '%s', template_id = %d, file = '%s', file_has_header = '%s' WHERE nid = %d";
	db_query($sql, $node->nid, $node->loader_name, $node->template_id, $node->file, $node->has_header, $node->nid);
	
	// Add a job if the user want to load the data
	global $user;
	if($node->job) {
		$job_args[0] =$node->loader_name;
		$job_args[1] = $node->template_id;
		$job_args[2] = $node->file;
		if (is_readable($node->file)) {
			$fname = preg_replace("/.*\/(.*)/", "$1", $node->file);
			tripal_add_job("Bulk Load: $fname",'tripal_bulk_loader', 'tripal_bulk_loader_load_data', $job_args, $user->uid);
		} else {
			drupal_set_message("Can not open $node->file. Job not scheduled.");
		}
	}
  
}

///////////////////////////////////////////////////////////
// Set Constants Form
///////////////////////////////////////////////////////////

/**
 * Set constants (exposed fields in template)
 *
 * @param $form_state 
 *   The current state of the form
 * @param $node
 *   The node to set constants for
 *
 * @return
 *   A form array to be rendered by drupal_get_form()
 */
function tripal_bulk_loader_set_constants_form ($form_state, $node) {
  $form = array();
  
  $form['nid'] = array(
    '#type' => 'hidden',
    '#value' => $node->nid
  );
  
  $form['exposed_fields'] = array(
    '#type' => 'fieldset',
    '#title' => t('Constant Values'),
    '#collapsible' => TRUE,
    '#collapsed' => ($node->template_id) ? FALSE : TRUE,
    '#prefix' => '<div id="set-constants">',
    '#suffix' => '</div>',
  );
  
  $form['exposed_fields']['explanation'] = array(
    '#type' => 'item',
    '#value' => t('The following fields are constants in the selected template that you need to set values for.')
  );
  
  // Add textifelds for exposed fields of the current template
  $exposed_fields = FALSE;
  $indexes = array();
  if ($node->template_id) {
    if (isset($node->template)) {
      foreach ($node->template->template_array as $record_id => $record) {
        foreach ($record['fields'] as $field_id => $field) {
          if ($field['exposed']) {
            $exposed_fields = TRUE;
            $indexes[$record_id][] = $field_id;
            
            switch($field['type']) {
              case 'table field':
                $form['exposed_fields'][$record_id.'-'.$field_id] = array(
                  '#type' => 'textfield',
                  '#title' => t($field['title']),
                  '#description' => t($field['exposed_description']),
                  '#default_value' => (isset($node->constants[$record_id][$field_id]['value'])) ? $node->constants[$record_id][$field_id]['value'] : $field['constant value'],
                );
              break;
              case 'constant':
                $form['exposed_fields'][$record_id.'-'.$field_id] = array(
                  '#type' => 'textfield',
                  '#title' => t($field['title']),
                  '#description' => t('Enter the case-sensitive value of this constant for your spreadsheet'),
                  '#default_value' => (isset($node->constants[$record_id][$field_id]['value'])) ? $node->constants[$record_id][$field_id]['value'] : $field['constant value'],
                );
              break;
            }
            
            $form['exposed_fields'][$record_id.'-'.$field_id.'-table'] = array(
              '#type' => 'hidden',
              '#value' => $record['table'],
            );
            $form['exposed_fields'][$record_id.'-'.$field_id.'-field'] = array(
              '#type' => 'hidden',
              '#value' => $field['field'],
            );
            $form['exposed_fields'][$record_id.'-'.$field_id.'-type'] = array(
              '#type' => 'hidden',
              '#value' => $field['type'],
            );
          }
        }
      }
    }
  }
  $form['template'] = array(
    '#type' => 'hidden',
    '#value' => serialize($node->template->template_array)
  );
  
  $form['exposed_fields']['indexes'] = array(
    '#type' => 'hidden',
    '#value' => serialize($indexes),
  );
  
  if (!$exposed_fields) {
    $form['exposed_fields']['explanation'] = array(
      '#type' => 'item',
      '#value' => t('There are no exposed fields for this template.')
    );    
  }
  
  $form['exposed_fields']['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Save Changes')
  );
  
  return $form;
}

/**
 * Validate that the values entered exist in the database
 * if indicated in hte template array
 */
function tripal_bulk_loader_set_constants_form_validate ($form_state, $form) {

  $template = unserialize($form_state['template']['#value']);
  $indexes = unserialize($form_state['exposed_fields']['indexes']['#value']);
  
  foreach ($indexes as $record_id => $array) {
    foreach($array as $field_id) {
      if ($template[$record_id]['fields'][$field_id]['exposed_validate']) {
        $result = db_fetch_object(db_query(
          "SELECT 1 as valid FROM %s WHERE %s='%s'",
          $template[$record_id]['table'],
          $template[$record_id]['fields'][$field_id]['field'],
          $form_state['exposed_fields'][$record_id.'-'.$field_id]['#value']
        ));

        if (!$result->valid) {
          $msg = 'A '.$form_state['exposed_fields'][$record_id.'-'.$field_id]['#title'].' of "'.$form_state['exposed_fields'][$record_id.'-'.$field_id]['#value'].'" must already exist!';
          form_set_error($record_id.'-'.$field_id, $msg);
        } else {
          drupal_set_message('Confirmed a '.$form_state['exposed_fields'][$record_id.'-'.$field_id]['#title'].' of "'.$form_state['exposed_fields'][$record_id.'-'.$field_id]['#value'].'" already exists.');
        }
      }
    }
  }
  
}

/**
 * Insert/update the constants associated with this node
 */
function tripal_bulk_loader_set_constants_form_submit ($form, $form_state) {

	// Insert/Update constants
  $template = unserialize($form_state['values']['template']);
  $indexes = unserialize($form_state['values']['indexes']);
  
  foreach ($indexes as $record_id => $array) {
    foreach($array as $field_id) {	
      tripal_bulk_loader_update_constant(
        $form_state['values'][nid], 
        $form_state['values'][$record_id.'-'.$field_id.'-table'], 
        $form_state['values'][$record_id.'-'.$field_id.'-field'],
        $record_id,
        $field_id,
        $form_state['values'][$record_id.'-'.$field_id]
        );
    }
  }
  
}

/**
 * Inserts/Updates a tripal bulk loading job constant
 *
 * @param $nid
 *   The node ID of the the tripal bulk loading job the constant is associated with
 * @param $table
 *   The chado table the constant is associated with
 * @param $field
 *   The chado field the constant is associated with
 * @param $record_id
 *   The index in the template array for this record
 * @param $field_id
 *   The index in the template array for this field
 *
 * NOTE: $template_array[$record_id]['table'] = $table and $template_array[$record_id]['fields'][$field_id]['field'] = $field
 *   both are included as a means of double-checking the constant still is still in thesame place in the template array.
 *   For example, that the template was not edited and the records moved around after the job was submitted but before it was run.
 *
 * @return
 *   On success it returns the object (with primary key if inserted);
 *   on failure it returns FALSE
 */
function tripal_bulk_loader_update_constant ($nid, $table, $field, $record_id, $field_id, $value) {
  
  $record = array(
    'nid'=>$nid, 
    'chado_table'=>$table, 
    'chado_field'=>$field, 
    'record_id'=>$record_id, 
    'field_id'=>$field_id, 
    'value'=>$value
  );
  
  // Check to see if already exists
  $exists = db_fetch_object(db_query(
    "SELECT constant_id FROM {tripal_bulk_loader_constants} WHERE nid=%d AND record_id=%d AND field_id=%d",
    $record['nid'],
    $record['record_id'],
    $record['field_id']
  ));
  if ($exists->constant_id) {
    $record['constant_id'] = $exists->constant_id;
    $status = drupal_write_record('tripal_bulk_loader_constants',$record,'constant_id');
    if ($status) {
      return $record;
    } else {
      return FALSE;
    }
  } else {
    $status = drupal_write_record('tripal_bulk_loader_constants',$record);
    if ($status) {
      return $record;
    } else {
      return FALSE;
    }
  }
}

///////////////////////////////////////////////////////////

/**
 * Preprocessor function for the tripal_bulk_loader template
 */
function tripal_bulk_loader_preprocess_tripal_bulk_loader_template (&$variables) {

	$sql = "SELECT * FROM {tripal_bulk_loader_template} WHERE template_id=%d";
	$template = db_fetch_object(db_query($sql, $variables['template_id']));
	$template->template_array = unserialize($template->template_array);	
	$variables['template'] = $template;
	
}

/**
 * Implements hook_job_describe_args()
 * Specifically to make viewing past tripal jobs more readable for jobs registered by this module
 *
 * @params $callback
 *   The callback passed into tripal_add_job()
 * @param $args
 *   The arguements passed into tripal_add_job()
 * @return 
 *   An array where keys are the human readable headers describing each arguement 
 *   and the value is the aguement passed in after formatting
 */
function tripal_bulk_loader_job_describe_args($callback,$args){

  $new_args = array();
  if($callback == 'tripal_bulk_loader_load_data'){
    //1st arg is the nid for a bulk loader node
    $node = node_load($args[0]);
    $new_args['Bulk Loading Job'] = l($node->title, 'node/'.$args[0]);
    return $new_args;
  }
  
}