rawpheno.install

<?php

/**
 * @file
 * Contains all schema api and hook_install()/hook_uninstall()
 * implementations required by this module.
 */

/**
 * Implements hook_schema().
 */
function rawpheno_schema() {
  // PHENO PLANT
  // Since the plant can be described in many different ways,
  // we are using a prop-like table to describe the plant.
  // This allows for the flexibility needed to describe both
  // greenhouse and field pots.
  $schema['pheno_plant'] = array(
    'description' => t('Table for phenotype plant.'),
    'fields' => array(
      'plant_id' => array(
        'description' => t('A unique ID for each row'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'stock_id' => array(
        'description' => t('A unique ID for a stock name.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,),
      ),
  );

  // PHENO PLANT PROP
  // Type should include entry, plot, rep and location.
  $schema['pheno_plantprop'] = array(
    'description' => t('Table for phenotype plantprop.'),
    'fields' => array(
      'plantprop_id' => array(
        'description' => t('Primary key: A unique ID for each plantprop record.'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE
      ),
      'plant_id' => array(
        'description' => t('Holds plant ID number.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' =>  TRUE,
      ),
      'type_id' => array(
        'description' => t('Term ID number of trait inserted.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE
      ),
      'value' => array(
        'description' => t('The value of the trait measured.'),
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE
      ,)
    ,),
    'primary key' => array('plantprop_id'),
    'foreign keys' => array(
      'pheno_plant' => array(
        'table' => 'pheno_plant',
        'columns' => array('plant_id' => 'plant_id')),),
  );

  // PHENO SCALE MEMBER
  // The scale code is what is written in the spreadsheet,
  // whereas the value is what it actually represents.
  $schema['pheno_scale_member'] = array(
    'description' => t('Table for pheno scale member.'),
    'fields' => array(
      'member_id' => array(
        'description' => t('Primary key: A unique ID for each scale value.'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'scale_id' => array(
        'description' => t('Scale ID number of a scale value.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'code' => array(
        'description' => t('Corresponding code for each scale ID.'),
        'type' => 'varchar',
        'length' => 255,
      ),
      'value' => array(
        'description' => t('Description of a scale value.'),
        'type' => 'text',
        'not null' => TRUE
      ,)
    ,),
    'primary key' => array('member_id'),
  );

  // PHENO MEASUREMENTS
  // Modified is the timestamp that it was last modified.
  // This allows us to determine when values were updated.
  $schema['pheno_measurements'] = array(
    'description' => t('Table for phenotype measurements'),
    'fields' => array(
      'measurement_id' => array(
        'description' => t('Primary key: A unique ID for each trait measured.'),
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'plant_id' => array(
        'description' => t('Holds plant ID number.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'type_id' => array(
        'description' => t('Term ID number of trait inserted.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'unit_id' => array(
        'description' => t('Unit ID number of trait inserted.'),
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      'value' => array(
        'description' => t('The value of the trait measured.'),
        'type' => 'varchar',
        'length' => 255,
        'not null' => FALSE,
      ),
      'cvalue_id' => array(
        'description' => t('Scale member code number when unit is scale.'),
        'type' => 'varchar',
        'length' => 10,
        'not null' => FALSE,
      ),
      'modified' => array(
        'description' => t('timestamp of the last modification.'),
        'type' => 'varchar',
        'length' => 30,
        'not null' => TRUE,
      )
    ,),
    'primary key' => array('measurement_id'),
    'foreign keys' => array(
      'pheno_plant' => array(
        'table' => 'pheno_plant',
        'columns' => array('plant_id' => 'plant_id')),),
  );
  //

  // PROJECT-CVTERM
  // Group a set of traits/cvterm to a project.
  $schema['pheno_project_cvterm'] = array(
    'description' => t('Table for grouping set of traits to a project.'),
    'fields' => array(
      'project_cvterm_id' => array(
        'description' => t('Primary key: A unique ID number.)'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of a project from chado.project table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'cvterm_id' => array(
        'description' => t('cvterm id number of a trait'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      // This option will allow for grouping of traits. In AGILE Project, types include:
      // Essential Traits, Optional Traits, Subset Trait and Plant Property.
      // Use this field to indicate as such.
      // A function in rawpheno.function.measurements lists possible types that can be made available
      // to admin when adding traits in the admin control panel.
      'type' => array(
        'description' => t('Indicate a column header is essential, not essential (optional), a plant property and other'),
        'type' => 'varchar',
        'length' => 20,
      ),
    ),
    'primary key' => array('project_cvterm_id'),
  );

  // PROJECT-PLANT/STOCK
  $schema['pheno_plant_project'] = array(
    'description' => t('Table for mapping stock to a project'),
    'fields' => array(
      'plant_project_id' => array(
        'description' => t('Primary key: A nunique ID number.'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of a project from chado.project table.'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'plant_id' => array(
        'description' => t('ID number of stock from pheno_plant table.'),
        'type' => 'int',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('plant_project_id'),
  );

  // PROJECT-USER
  $schema['pheno_project_user'] = array(
    'description' => t('This table will hold information about user and project'),
    'fields' => array(
      'project_user_id' => array(
        'description' => t('Primary Key: A unique ID number'),
        'type' => 'serial',
        'not noll' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of project from chado.project table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'uid' => array(
        'description' => t('ID number of logged in user from user table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('project_user_id'),
  );

  // PROJECT-BACKUP-FILE
  $schema['pheno_backup_file'] = array(
    'description' => t('This table will hold information about files uploaded by user to a project'),
    'fields' => array(
      'file_id' => array(
        'description' => t('Primary Key: A unique ID number'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_user_id' => array(
        'description' => t('The project ID number from table pheno_project_user'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'fid' => array(
        'description' => t('File ID number from file_managed table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'version' => array(
        'description' => t('The sequence number of the file in relation to a project'),
        'type' => 'int',
      ),
      'validation_result' => array(
        'description' => t('Stores the result of file validation performed'),
        'type' => 'text',
      ),
      'notes' => array(
        'description' => t('Notes, description and comments made to a file'),
        'type' => 'text',
      ),
      'archive' => array(
        'description' => t('Indicates whether a file is temporarily removed from a project.'),
        'type' => 'varchar',
        'length' => 2,
        'default' => 'n',
      ),
    ),
    'primary key' => array('file_id'),
  );

  return $schema;
}

// Include function to manage column headers, cv terms and variable names.
module_load_include('inc', 'rawpheno', 'includes/rawpheno.function.measurements');

/**
 * Function to manage terms used by this module.
 *
 * @return
 *   An array of string representing scale measurements, variable names,
 *   controlled vocabulary and measurement units.
 */
function rawpheno_function_terms($type) {
  switch($type) {
    case 'project':
      return 'AGILE: Application of Genomic Innovation in the Lentil Economy';
      break;

    case 'defaults':
      // Default colour scheme and heading/title.
      return array('colour' => '#304356',
                   'rawdata' => 'Phenotypic data available',
                   'download' => 'Select locations and traits that you want to download',
                   'instructions' => 'Standard Phenotyping Procedure',
                   'upload' => 'Drag and Drop phenotypic data collection spreadsheet',
                   'backup' => 'My files',
                   'r-words' => 'of,to,have,on,at',
                   'r-chars' => '(,),/,-,:,;,%',
                   'r-replace' => '# = num,/ = div,? = unsure,- = to',);
      break;

    case 'scales':
      // Scale equivalent for scale units.
      return  array('Lodging (Scale: 1-5) upright - lodged' => array(
                1 => 'Vertical/upright',
                2 => 'Leaning',
                3 => 'Most plants at 45 degrees angle',
                4 => 'All plants 10-45 degrees from ground',
                5 => 'Most plants flat/prostrate',));
      break;

    case 'variables':
      // Configuration variable names.
      return array('rawpheno_colour_scheme',
                   'rawpheno_rawdata_title',
                   'rawpheno_download_title',
                   'rawpheno_instructions_title',
                   'rawpheno_upload_title',
                   'rawpheno_backup_title',
                   'rawpheno_rtransform_words',
                   'rawpheno_rtransform_characters',
                   'rawpheno_rtransform_replace',);
      break;

    case 'vocabularies':
      // Controlled vocabularies.
      return array('cv_prop' => 'phenotype_plant_property_types',
                   'cv_unit' => 'phenotype_measurement_units',
                   'cv_type' => 'phenotype_measurement_types',
                   'cv_rver' => 'phenotype_r_compatible_version',
                   'cv_desc' => 'phenotype_collection_method',);
      break;

    case 'units':
      // Units available in the spreadsheet.
      return rawpheno_function_default_unit('def');

      break;
  }
}


/**
 * Implements hook_install().
 */
function rawpheno_install() {
  // Get variable names and default values.
  $vars = rawpheno_function_terms('variables');
  $config = rawpheno_function_terms('defaults');

  // # 1. INSERT CV.
  // Insert all cvs required.
  $cv = rawpheno_function_terms('vocabularies');

  // Array to hold cv_id and cv_name.
  // This will be used when creating cvterm relationships.
  $arr_cvid_cvname = array();

  $chado_inscv_exists = function_exists('chado_insert_cv');

  foreach($cv as $cv_i => $cv_name) {
   $def = str_replace('_', ' ', ucfirst($cv_name));
   $tmp = ($chado_inscv_exists)
     ? chado_insert_cv($cv_name, $def)
     : tripal_insert_cv($cv_name, $def);

    if ($tmp) {
      $arr_cvid_cvname[$cv_name] = $tmp->cv_id;
    }
    else {
      tripal_report_error('Raw Phenotypes', TRIPAL_CRITICAL, 'Chado/Tripal failed to insert cv', array(), array('print' => TRUE));
    }
  }

  // # 2. INSERT UNITS.
  // Insert units and scale member used by scale.
  // NOTE: Scale memeber is link to the column header Lodging (scale 1-5) and not to scale unit.
  //       When the column header Lodging is inserted, only then we can insert the scale members.

  // Array to hold scale members.
  $arr_unit = rawpheno_function_terms('units');
  // Array to hold cvterm_id of each inserted unit.
  // This will be used when creating a cvterm relationship linking a cvterm to a unit.
  $arr_cvtermid_unit = array();

  $chado_insterm_exists = function_exists('chado_insert_cvterm');

  foreach($arr_unit as $unit => $def) {
    $ins_unit = array(
      'id' => 'rawpheno_tripal:' . $unit,
      'name' => $unit,
      'definition' => $def,
      'cv_name' => $cv['cv_unit']
    );

    $tmp = ($chado_insterm_exists)
      ? chado_insert_cvterm($ins_unit)
      : tripal_insert_cvterm($ins_unit);

    if ($tmp) {
      $arr_cvtermid_unit[$unit] = $tmp->cvterm_id;
    }
    else {
      tripal_report_error('Raw Phenotypes', TRIPAL_CRITICAL, 'Chado/Tripal failed to insert cvterm (trait unit)', array(), array('print' => TRUE));
    }
  }

  // # 3. INSERT COLUMN HEADERS.
  // Insert column headers.
  // When inserting column headers, generate the R Version, add collection method
  // and map it to a AGILE (only when this project is available).

  // Default Project.
  // This is the default project, only when such project exists, otherwise, insert the column headers
  // and make them available for use in admin control panel.
  // @note project.name must be unique so no need to limit 1.
  $default_project = rawpheno_function_terms('project');
  $sql = "SELECT project_id FROM {project} WHERE name = :default_project";
  $args = array(':default_project' => $default_project);
  $prj_id = chado_query($sql, $args);

  if ($prj_id->rowCount()) {
    $project_ID = $prj_id->fetchField();

    // When AGILE is present in this site, add a user to this project.
    db_insert('pheno_project_user')
      ->fields(array('project_id' => $project_ID,
                     'uid' => $GLOBALS['user']->uid))
      ->execute();

    // If the user is not the superadmin, add superadmin as well.
    if ($GLOBALS['user']->uid != 1) {
      db_insert('pheno_project_user')
        ->fields(array('project_id' => $project_ID,
                       'uid' => 1))
        ->execute();
      }
  }

  // Initialize R Transformation rules required in generating R version of header.
  // The default rules are the rules required in generating R version of headers default to this module.
  variable_set($vars[6], $config['r-words']);
  variable_set($vars[7], $config['r-chars']);
  variable_set($vars[8], $config['r-replace']);

  // Array to hold header types.
  $trait_type = rawpheno_function_trait_types();

  // Array to hold all column headers, plant property headers and essential headers.
  $arr_allheaders = rawpheno_function_headers('expected');
  $arr_subsetheader = rawpheno_function_headers('subset');
  $arr_plantpropheader = rawpheno_function_headers('plantprop');
  $arr_essentialheader = rawpheno_function_headers('essential');

  foreach($arr_allheaders as $header) {
    // Skip when it is Name.
    if ($header == 'Name') {
      continue;
    }

    // Get the definition and collection method.
    $cvterm_info = rawpheno_function_get_definition($header);
    // When no information is retured, default the definition to the term itself.
    $cvterm_definition = (!empty($cvterm_info['define'])) ? $cvterm_info['define'] : $header;
    // Set the type of header.
    $cvterm_type = (in_array($header, $arr_plantpropheader)) ? $cv['cv_prop'] : $cv['cv_type'];

    $ins_term = array(
      'id'         => 'rawpheno_tripal:' . $header,
      'name'       => $header,
      'definition' => $cvterm_definition,
      'cv_name'    => $cvterm_type
    );

    // Insert the cvterm.
    $cvterm = ($chado_insterm_exists)
      ? chado_insert_cvterm($ins_term)
      : tripal_insert_cvterm($ins_term);

    // Insert the cvterm data collection method.
    if (!empty($cvterm_info['method']) && $cvterm) {
      // Data collection method.
      chado_insert_record('cvtermprop',
        array(
          'cvterm_id' => $cvterm->cvterm_id,
          'type_id'   => $arr_cvid_cvname[ $cv['cv_desc'] ],
          'value'     => $cvterm_info['method'],
          'rank'      => 0
        )
      );
    }
    else {
      tripal_report_error('Raw Phenotypes', TRIPAL_CRITICAL, 'Chado/Tripal failed to insert cvterm (traits)', array(), array('print' => TRUE));
    }

    // Relate cvterm and unit.
    // No relationship when header is Plant Property Type.
    if (!in_array($header, $arr_plantpropheader) && $cvterm) {
      // Extract the unit part from the column header. Usually, the information enclosed in a parenthesis.
      $header_unit = rawpheno_function_header_unit($header);
      $header_unit = (empty($header_unit)) ? 'text' : $header_unit;

      chado_insert_record('cvterm_relationship',
        array(
          'subject_id' => $arr_cvtermid_unit[$header_unit],
          'type_id'    => $arr_cvid_cvname[ $cv['cv_unit'] ],
          'object_id'  => $cvterm->cvterm_id
        )
      );
    }

    // R version. To be implemented to all column headers.
    $r_version = rawpheno_function_make_r_compatible($header);

    if ($cvterm) {
      chado_insert_record('cvtermprop',
        array(
          'cvterm_id' => $cvterm->cvterm_id,
          'type_id'   => $arr_cvid_cvname[ $cv['cv_rver'] ],
          'value'     => $r_version,
          'rank'      => 0
        )
      );
    }

    // Finally, map this particular header to project, again, only when default project is present.
    if (isset($project_ID) AND $project_ID > 0) {
      // Indicate the type of header.
      if (in_array($header, $arr_plantpropheader)) {
        // Type is Plant Property.
        $type = $trait_type['type4'];
      }
      elseif (in_array($header, $arr_essentialheader)) {
        // Trait is essential (must be in the spreadsheet).
        $type = $trait_type['type1'];
      }
      elseif (in_array($header, $arr_subsetheader)) {
        // Subset Trait
        $type = $trait_type['type3'];
      }
      else {
        // Trait is optional
        $type = $trait_type['type2'];
      }

      db_insert('pheno_project_cvterm')
        ->fields(array(
            'project_id' => $project_ID,
            'cvterm_id' => $cvterm->cvterm_id,
            'type' => $type
          )
        )
        ->execute();
    }
  }

  // Lodging (scale 1-5) has been added, so the scale member table can now be generated.
  $lodging = rawpheno_function_terms('scales');

  $cl = array('name' => array_keys($lodging)[0], 'cv_id' => array('name' => $cv['cv_type']));

  if (function_exists('chado_get_cvterm')) {
    $cvterm_lodging = chado_get_cvterm($cl);
  }
  else {
    $cvterm_lodging = tripal_get_cvterm($cl);
  }

  // Insert scale values 1 - 5
  foreach (array_values($lodging)[0] as $code => $val) {
    db_insert('pheno_scale_member')
      ->fields(array(
        'scale_id' => $cvterm_lodging->cvterm_id,
        'code' => $code,
        'value' => $val
      ))
      ->execute();
  }


  // # 4. MATERIALIZED VIEW.
  // Create materialized view used in phenotypes/rawdata.
  // Using Tripal: tripal_add_mview().
  $mv_name = 'rawpheno_rawdata_summary';
  $mv_module = 'rawpheno';
  $mv_comment = 'Materialized view used by rawpheno module to generate summary of traits per location, rep and year.';

  // Create a summary of data in the following format:
  // Plant id, Location, Rep, Planting Date and Total traits count.
  // NOTE: trait count includes planting date.
  // NOTE: As a materialized view, this get's executed by chado query.
  $mv_sql = "SELECT CAST(t1.plant_id AS numeric) AS plant_id,
      t2.value AS location,
      t3.value AS rep,
      t5.value AS planting_year,
      COUNT(DISTINCT t4.type_id) AS total_count,
      ARRAY_TO_STRING(ARRAY_AGG(DISTINCT t4.type_id), ',') AS all_traits
    FROM pheno_plant AS t1
      INNER JOIN [pheno_plantprop] AS t2 USING(plant_id)
      INNER JOIN [pheno_plantprop] AS t3 USING(plant_id)
      INNER JOIN [pheno_measurements] AS t4 USING(plant_id)
      INNER JOIN [pheno_measurements] AS t5 USING(plant_id)
    WHERE
      t2.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} cv ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Location' AND cv.name = 'phenotype_plant_property_types') AND
      t3.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Rep' AND cv.name = 'phenotype_plant_property_types') AND
      t5.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Planting Date (date)' AND cv.name = 'phenotype_measurement_types')
    GROUP BY t1.plant_id, t4.plant_id, t2.value, t3.value, t5.value";

  // Schema array.
  $mv_schema = array (
    'table' => 'rawpheno_rawdata_mview',
    'fields' => array (
      'plant_id' => array (
        'type' => 'int'),
      'location' => array (
        'type' => 'varchar',
        'length' => 255),
      'rep' => array (
        'type' => 'varchar',
        'length' => 255),
      'planting_date' => array (
        'type' => 'varchar',
        'length' => 255),
      'total_count' => array (
        'type' => 'int'),
      'all_traits' => array(
        'type' => 'text'),
    ),
    'indexes' => array(
      'plant_id' => array('plant_id'),
      'location' => array('location'),
    )
  );

  // Create materialized view.
  if (function_exists('chado_add_mview')) {
    chado_add_mview($mv_name, $mv_module, $mv_schema, $mv_sql, $mv_comment, FALSE);
  }
  else {
    tripal_add_mview($mv_name, $mv_module, $mv_schema, $mv_sql, $mv_comment, FALSE);
  }

  // #5. DEFAULT SITE SETTINGS.
  // Set default colour scheme and page titles.
  variable_set($vars[0], $config['colour']);
  variable_set($vars[1], $config['rawdata']);
  variable_set($vars[2], $config['download']);
  variable_set($vars[3], $config['instructions']);
  variable_set($vars[4], $config['upload']);
  variable_set($vars[5], $config['backup']);

  // Add data collection spreadsheet to public:// directory,
  // and link this file for download in instructions page.
  $destination = file_default_scheme() . '://';
  if (file_prepare_directory($destination, FILE_MODIFY_PERMISSIONS)) {
    // Add this file if directory is writable.
    // File is in zip format as it is more friendly for download than xlsx.
    $file = 'AGILE-PhenotypeDataCollection-v5.xlsx.zip';
    $source = drupal_get_path('module', 'rawpheno') . '/theme/' . $file;
    // Copy file without creating an entry in drupal database.
    // If file is present, replace it with the same file.
    file_unmanaged_copy($source, $destination, FILE_EXISTS_REPLACE);
  }
}


/**
 * Implements hook_uninstall().
 */
function rawpheno_uninstall() {
  // # 1. UNREGISTER PERSISTENT VARIABLES.
  $vars = rawpheno_function_terms('variables');
  foreach($vars as $var) {
    if (variable_get($var)) {
      variable_del($var);
    }
  }

  // # 2. DROP MATERIALIZED VIEW TABLE.
  // Using tripal: tripal_delete_mview().
  // Get mview id of materilaized view created by hook_install().
  // @note tripal_mviews.mv_table must be unique (constraint).
  $sql = "SELECT mview_id FROM {tripal_mviews} WHERE mv_table = :mv_table";
  $mview_id = db_query($sql, array(':mv_table' => 'rawpheno_rawdata_mview'))
    ->fetchField();

  if (isset($mview_id) AND $mview_id > 0) {
    // Delete materialized view.
    tripal_delete_mview($mview_id);
  }

  // # 3. DELETE CV AND CVTERM.
  $cv = rawpheno_function_terms('vocabularies');

  foreach($cv as $cv_i => $cv_name) {
    if (function_exists('chado_get_cv')) {
      $cv = chado_get_cv(array('name' => $cv_name));
    }
    else {
      $cv = tripal_get_cv(array('name' => $cv_name));
    }

    $args = array(':cv_id' => $cv->cv_id);

    // Delete relationships.
    if ($cv_i == 'cv_unit') {
      $del = "DELETE FROM {cvterm_relationship} WHERE type_id = :cv_id";
      chado_query($del, $args);
    }

    // Delete R version.
    if ($cv_i == 'cv_rver') {
      $del = "DELETE FROM {cvtermprop} WHERE type_id = :cv_id";
      chado_query($del, $args);
    }

    // Delete cvterms.
    if ($cv_i == 'cv_type') {
      $del = "DELETE FROM {cvterm} WHERE cv_id = :cv_id";
      chado_query($del, $args);
    }

    // Finally, delete the cv
    $del = "DELETE FROM {cv} WHERE cv_id = :cv_id";
    chado_query($del, $args);
  }
}


/**
 * Implements hook_update_N().
 * Map cvterm in tripal database to rawpheno_tripal.
 *
 * N
 * 7  - Drupal core compatibility.
 * 2  - Module's major release version.
 * 01 - Sequence count.
 */
function rawpheno_update_7201() {
  // Rawphenotypes database name.
  $rawpheno_dbname = 'rawpheno_tripal';
  // db_id
  $rawpheno_dbid = '';

  // In db table, check to see if rawpheno_tripal exists.
  // @note db.name must be unique (constraint).
  $sql = "SELECT db_id FROM {db} WHERE name = :rawpheno_dbname";
  $args = array(':rawpheno_dbname' => $rawpheno_dbname);

  $db_id = chado_query($sql, $args);

  if ($db_id->rowCount()) {
    // Already created. Save the db_id
    $rawpheno_dbid = $db_id
      ->fetchField();
  }
  else {
    // Not created. Insert a row and get the id.
    $db_id = chado_insert_record('db', array('name' => $rawpheno_dbname));
    $rawpheno_dbid = $db_id['db_id'];
  }

  // Get the db id of Tripal db. Use it to futher filter the rows for update
  // since previous version use tripal as default db.
  // @note db.name must be unique (constraint).
  $sql = "SELECT db_id FROM {db} WHERE name = 'tripal'";
  $tripal = chado_query($sql);

  if ($tripal->rowCount()) {
    $tripal_dbid = $tripal
      ->fetchField();

    // With the db_id, update all rawphenotypes cvterms to use this db_id in dbxref.
    $t = rawpheno_function_terms('vocabularies');
    unset($t['cv_rver'], $t['cv_desc']);
    $terms = array_values($t);

    // Update db_id in dbxerf table. Map all rawphenotypes cvterms to rapheno_tripal db.
    $sql = "
      UPDATE {dbxref} SET db_id = :rawpheno_dbid
      WHERE
        dbxref_id IN (SELECT t2.dbxref_id FROM {cv} AS t1 INNER JOIN {cvterm} AS t2 USING (cv_id) WHERE t1.name IN (:terms))
        AND db_id = :tripal_dbid";

    $args = array(':rawpheno_dbid' => $rawpheno_dbid, // Map to rawpheno_tripal instead.
                  ':terms' => $terms,                 // Only for terms phenotype_plant_property, phenotype_measurement_units and phenotype_measurement_type
                  ':tripal_dbid' => $tripal_dbid);    // Where previously set to TRIPAL.

    chado_query($sql, $args);
  }
}


/**
 * Implements hook_update_N().
 * Update custom table with tables relating a project to set of cvterms
 * and project to stock/name.
 *
 * N
 * 7  - Drupal core compatibility.
 * 0  - Module's major release version.
 * 02 - Sequence count.
 */
function rawpheno_update_7002() {
  // Table definition as per definition in hook_schema() above.
  // PROJECT-CVTERM
  // Group a set of traits/cvterm to a project.
  $schema['pheno_project_cvterm'] = array(
    'description' => t('Table for grouping set of traits to a project.'),
    'fields' => array(
      'project_cvterm_id' => array(
        'description' => t('Primary key: A unique ID number.)'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of a project from chado.project table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'cvterm_id' => array(
        'description' => t('cvterm id number of a trait'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      // This option will allow for grouping of traits. In for AGILE Project, types include:
      // Essential Traits, Optional Traits, Subset Trait and Plant Property.
      // Use this field to indicate as such.
      // A function in rawpheno.function.measurements lists possible types that can be made available
      // to admin when adding traits in the admin control panel.
      'type' => array(
        'description' => t('Indicate a column header is essential, not essential (optional), a plant property and other'),
        'type' => 'varchar',
        'length' => 20,
      ),
    ),
    'primary key' => array('project_cvterm_id'),
  );

  db_create_table('pheno_project_cvterm', $schema['pheno_project_cvterm']);

  // PROJECT-PLANT/STOCK
  $schema['pheno_plant_project'] = array(
    'description' => t('Table for mapping stock to a project'),
    'fields' => array(
      'plant_project_id' => array(
        'description' => t('Primary key: A nunique ID number.'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of a project from chado.project table.'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'plant_id' => array(
        'description' => t('ID number of stock from pheno_plant table.'),
        'type' => 'int',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('plant_project_id'),
  );

  db_create_table('pheno_plant_project', $schema['pheno_plant_project']);

  // PROJECT-USER
  $schema['pheno_project_user'] = array(
    'description' => t('This table will hold information about user and project that'),
    'fields' => array(
      'project_user_id' => array(
        'description' => t('Primary Key: A unique ID number'),
        'type' => 'serial',
        'not noll' => TRUE,
      ),
      'project_id' => array(
        'description' => t('ID number of project from chado.project table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'uid' => array(
        'description' => t('ID number of logged in user from user table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('project_user_id'),
  );

  db_create_table('pheno_project_user', $schema['pheno_project_user']);

  // PROJECT-BACKUP-FILE
  $schema['pheno_backup_file'] = array(
    'description' => t('This table will hold information about files uploaded by user to a project'),
    'fields' => array(
      'file_id' => array(
        'description' => t('Primary Key: A unique ID number'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_user_id' => array(
        'description' => t('The project ID number from table pheno_project_user'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'fid' => array(
        'description' => t('File ID number from file_managed table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'version' => array(
        'description' => t('The sequence number of the file in relation to a project'),
        'type' => 'int',
      ),
      'validation_result' => array(
        'description' => t('Stores the result of file validation performed'),
        'type' => 'text',
      ),
      'notes' => array(
        'description' => t('Notes, description and comments made to a file'),
        'type' => 'text',
      ),
      'archive' => array(
        'description' => t('Indicates whether a file is temporarily removed from a project.'),
        'type' => 'varchar',
        'length' => 2,
        'default' => 'n',
      ),
    ),
    'primary key' => array('file_id'),
  );

  db_create_table('pheno_backup_file', $schema['pheno_backup_file']);


  // UPDATE First Release to Project Based.
  // When updating the first release to project based, first update page configuration
  // to add page title to back up page at the same time initialize R transformation rules
  // with default rules (rules used to generate R compatible verstion of all AGILE-specific
  // traits). Next, update cvterms definition, create a relationship entry relating a unit to cvterm,
  // add method of collection method description and transform a R compatible version
  // of the cvterm. Finally, tell if AGILE project is present in the host site and if not, create the project
  // and reference the project id number. When the module has data, map everything to this
  // project and appoint the user running the update and super admin.

  // Update - Add backup page title.
  $vars = rawpheno_function_terms('variables');
  $config = rawpheno_function_terms('defaults');
  variable_set($vars[5], $config['backup']);

  // Update - Add default R transformation rules.
  variable_set($vars[6], $config['r-words']);
  variable_set($vars[7], $config['r-chars']);
  variable_set($vars[8], $config['r-replace']);

  // AGILE Project.
  $default_project = rawpheno_function_terms('project');

  // @note project.name must be unique (constraint).
  $sql = "SELECT project_id FROM {project} WHERE name = :default_project";
  $args = array(':default_project' => $default_project);
  $prj_id = chado_query($sql, $args);

  if ($prj_id->rowCount()) {
    $project_ID = $prj_id->fetchField();
  }
  else {
    // Create AGILE Project when not present in this site.
    $tmp = chado_insert_record('project',
      array('name' => $default_project)
    );

    $project_ID = $tmp->project_id;
  }

  // Trait types array.
  $trait_type = rawpheno_function_trait_types();
  // Subset trait types array.
  $arr_subsetheader = rawpheno_function_headers('subset');
  // Plant Property trait types array.
  $arr_plantpropheader = rawpheno_function_headers('plantprop');
  // Essential trait types array.
  $arr_essentialheader = rawpheno_function_headers('essential');

  // Measurements type and Plant Property type.
  $voc = rawpheno_function_terms('vocabularies');
  // CV R version.
  $cv_rver = tripal_insert_cv($voc['cv_rver'], str_replace('_', ' ', ucfirst($voc['cv_rver'])));
  // CV Method of collection.
  $cv_desc = tripal_insert_cv($voc['cv_desc'], str_replace('_', ' ', ucfirst($voc['cv_desc'])));
  // CV unit.
  if (function_exists('chado_get_cv')) {
    $cv_unit = chado_get_cv(array('name' => $voc['cv_unit']));
  }
  else {
    $cv_unit = tripal_get_cv(array('name' => $voc['cv_unit']));
  }

  // Query traits.
  $sql = "SELECT t2.cvterm_id, t2.name, t1.cv_id, t2.name, t1.name AS cv_name
          FROM {cv} AS t1 INNER JOIN {cvterm} AS t2 USING(cv_id)
          WHERE t1.name IN (:cvs)";

  $args = array(':cvs' => array($voc['cv_prop'], $voc['cv_type']));
  $result = chado_query($sql, $args);

  // Update each cvterm.
  foreach($result as $cvterm) {
    // Get definition and method of collection information.
    $cvterm_info = rawpheno_function_get_definition($cvterm->name);
    // In the first release, term name is also the term definition.
    $cvterm_definition = (!empty($cvterm_info['define'])) ? $cvterm_info['define'] : $cvterm->name;

    // DEFINIION:
    // Update the term with this information.
    chado_query('UPDATE {cvterm} SET definition = :def WHERE cvterm_id = :id',
      array(':def' => $cvterm_definition, ':id' => $cvterm->cvterm_id));

    // METHOD OF COLLECTION:
    // Add the method of collection description to cvterm prop.
    if (!empty($cvterm_info['method'])) {
      chado_insert_record('cvtermprop',
        array(
          'cvterm_id' => $cvterm->cvterm_id,
          'type_id' => $cv_desc->cv_id,
          'value' => $cvterm_info['method'],
          'rank' => 0
        )
      );
    }

    // R COMPATIBLE VERSION:
    // Transform trait to R compatible version.
    $r_version = rawpheno_function_make_r_compatible($cvterm->name);
    chado_insert_record('cvtermprop',
      array(
        'cvterm_id' => $cvterm->cvterm_id,
        'type_id'   => $cv_rver->cv_id,
        'value'     => $r_version,
        'rank'      => 0
      )
    );

    // CVTERM AND UNIT RELATIONSHIP:
    if ($cvterm->cv_name == $voc['cv_type']) {
      // Extract the unit part from the column header. Usually, the information enclosed in a parenthesis.
      $header_unit = rawpheno_function_header_unit($cvterm->name);
      // eg. Comment
      $header_unit = (empty($header_unit)) ? 'text' : $header_unit;

      if (function_exists('chado_get_cvterm')) {
        $unit_cvterm_id = chado_get_cvterm(array('name' => $header_unit, 'cv_id' => $cv_unit->cv_id));
      }
      else {
        $unit_cvterm_id = tripal_get_cvterm(array('name' => $header_unit, 'cv_id' => $cv_unit->cv_id));
      }

      chado_insert_record('cvterm_relationship',
        array(
          'subject_id' => $unit_cvterm_id->cvterm_id,
          'type_id'    => $cv_unit->cv_id,
          'object_id'  => $cvterm->cvterm_id
        )
      );
    }

    // PROJECT TO CVTERM:
    // Tell the type of the trait.
    if (in_array($cvterm->name, $arr_plantpropheader)) {
      // Type is Plant Property.
      $type = $trait_type['type4'];
    }
    elseif (in_array($cvterm->name, $arr_essentialheader)) {
      // Trait is essential (must be in the spreadsheet).
      $type = $trait_type['type1'];
    }
    elseif (in_array($cvterm->name, $arr_subsetheader)) {
      // Subset Trait
      $type = $trait_type['type3'];
    }
    else {
      // Trait is optional
      $type = $trait_type['type2'];
    }

    db_insert('pheno_project_cvterm')
    ->fields(array(
        'project_id' => $project_ID,
        'cvterm_id' => $cvterm->cvterm_id,
        'type' => $type
      )
    )
    ->execute();
  }

  // WHEN WITH DATA: STOCK TO PROJECT:
  // When pheno_measurements has data.
  $sql = "SELECT plant_id FROM {pheno_plant} ORDER BY plant_id ASC";
  $plant = db_query($sql);

  if ($plant->rowCount() > 0) {
    // STOCK:
    foreach($plant as $p) {
      db_insert('pheno_plant_project')
        ->fields(array('project_id' => $project_ID,
                       'plant_id' => $p->plant_id))
        ->execute();
    }

    // USER:
    //  Default to user updating this module as the user who uploaded the data.
    db_insert('pheno_project_user')
      ->fields(array('project_id' => $project_ID,
                     'uid' => $GLOBALS['user']->uid))
      ->execute();

    // If the user is not the superadmin, add superadmin as well.
    if ($GLOBALS['user']->uid != 1) {
      db_insert('pheno_project_user')
        ->fields(array('project_id' => $project_ID,
                       'uid' => 1))
        ->execute();
    }
  }

  // MATERIALIZED VIEW:
  // Replace materialized view.
  // Using tripal: tripal_delete_mview().
  // Get mview id of materilaized view created by hook_install().
  // @note tripal_mviews.mv_table must be unique (constraint).
  $sql = "SELECT mview_id FROM {tripal_mviews} WHERE mv_table = :mv_table";
  $mview_id = db_query($sql, array(':mv_table' => 'rawpheno_rawdata_mview'))
    ->fetchField();

  if (isset($mview_id) AND $mview_id > 0) {
    // Delete materialized view.
    tripal_delete_mview($mview_id);
  }

  // Create materialized view used in phenotypes/rawdata.
  // Using Tripal: tripal_add_mview().
  $mv_name = 'rawpheno_rawdata_summary';
  $mv_module = 'rawpheno';
  $mv_comment = 'Materialized view used by rawpheno module to generate summary of traits per location, rep and year.';

  // Create a summary of data in the following format:
  // Plant id, Location, Rep, Planting Date and Total traits count.
  // NOTE: trait count includes planting date.
  // NOTE: As a materialized view, this get's executed by chado query.
  $mv_sql = "SELECT CAST(t1.plant_id AS numeric) AS plant_id,
      t2.value AS location,
      t3.value AS rep,
      t5.value AS planting_year,
      COUNT(DISTINCT t4.type_id) AS total_count,
      ARRAY_TO_STRING(ARRAY_AGG(DISTINCT t4.type_id), ',') AS all_traits
    FROM pheno_plant AS t1
      INNER JOIN [pheno_plantprop] AS t2 USING(plant_id)
      INNER JOIN [pheno_plantprop] AS t3 USING(plant_id)
      INNER JOIN [pheno_measurements] AS t4 USING(plant_id)
      INNER JOIN [pheno_measurements] AS t5 USING(plant_id)
    WHERE
      t2.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} cv ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Location' AND cv.name = 'phenotype_plant_property_types') AND
      t3.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Rep' AND cv.name = 'phenotype_plant_property_types') AND
      t5.type_id = (SELECT cvterm_id FROM {cvterm} cvt LEFT JOIN {cv} ON cv.cv_id=cvt.cv_id WHERE cvt.name = 'Planting Date (date)' AND cv.name = 'phenotype_measurement_types')
    GROUP BY t1.plant_id, t4.plant_id, t2.value, t3.value, t5.value";

  // Schema array.
  $mv_schema = array (
    'table' => 'rawpheno_rawdata_mview',
    'fields' => array (
      'plant_id' => array (
        'type' => 'int'),
      'location' => array (
        'type' => 'varchar',
        'length' => 255),
      'rep' => array (
        'type' => 'varchar',
        'length' => 255),
      'planting_date' => array (
        'type' => 'varchar',
        'length' => 255),
      'total_count' => array (
        'type' => 'int'),
      'all_traits' => array(
        'type' => 'text'),
    ),
    'indexes' => array(
      'plant_id' => array('plant_id'),
      'location' => array('location'),
    )
  );

  // Create materialized view.
  tripal_add_mview($mv_name, $mv_module, $mv_schema, $mv_sql, $mv_comment);

  // Populate view.
  $mview_id = tripal_get_mview_id('rawpheno_rawdata_mview');
  tripal_populate_mview($mview_id);
}

/**
 * Implements hook_update_N().
 * Add custom table for environment data.
 *
 * N
 * 7  - Drupal core compatibility.
 * 0  - Module's major release version.
 * 03 - Sequence count.
 */
function rawpheno_update_7003() {
  // PHENO-ENVRONMENT-DATA
  $schema['pheno_environment_data'] = array(
    'description' => t('This table will hold information about environment data.'),
    'fields' => array(
      'environment_data_id' => array(
        'description' => t('Primary Key: A unique ID number'),
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'project_id' => array(
        'description' => t('The project ID number from table chado.project'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'fid' => array(
        'description' => t('File ID number from file_managed table'),
        'type' => 'int',
        'not null' => TRUE,
      ),
      'sequence_no' => array(
        'description' => t('The sequence number of the file.'),
        'type' => 'int',
      ),
      'location' => array(
        'description' => t('Location information of the file.'),
        'type' => 'text',
      ),
      'year' => array(
        'description' => t('Year information of the file.'),
        'type' => 'text',
      ),
    ),
    'primary key' => array('environment_data_id'),
  );

  db_create_table('pheno_environment_data', $schema['pheno_environment_data']);
}

/**
 * Implements hook_requirements().
 */
function rawpheno_requirements($phase) {
  $requirements = array();

  // On install of this module, check the following libraries.
  if (($phase == 'install' OR $phase == 'update') AND function_exists('libraries_get_path')) {
    $t = get_t();

    $libraries = 'i.e., sites/all/libraries/';

    $arr_req_libraries = array(
      // Libraries.
      'excel_writer' =>
        array(
          'is_in'  => libraries_get_path('PHP_XLSXWriter_plus'),
          'source' => 'https://github.com/SystemDevil/PHP_XLSXWriter_plus/archive/master.zip',
          'path'   => $libraries . 'PHP_XLSXWriter_plus'
        ),

      'excel_reader' =>
        array(
          'is_in'  => libraries_get_path('spreadsheet-reader'),
          'source' => 'https://github.com/nuovo/spreadsheet-reader/archive/master.zip',
          'path'   => $libraries . 'spreadsheet-reader'
        ),

      'd3' =>
        array(
          'is_in'  => libraries_get_path('d3'),
          'source' => 'https://github.com/d3/d3/releases/download/v3.5.14/d3.zip',
          'path'   => $libraries . 'd3'
        )
    );

    foreach($arr_req_libraries as $i => $info) {
      if (!$info['is_in']) {
        $path = $info['path'];
        $source = $info['source'];

        $requirements[$i] = array(
          'severity' => REQUIREMENT_ERROR,
          'description' =>
            $t('This module uses !library, which is missing.
            Please download and extract the entire contents of the archive into Libraries Directory %path directory on your server.',
            array('!library' => l(strtoupper(str_replace('_',' ',$i)),
                                  $source,
                                  array('attributes' => array('target' => '_blank'))), '%path' => $path)
          ),
        );
      }

      // Check version of D3 and esure patch has been implemented.
      if ($i == 'd3' && $info['is_in']) {
        $ver = rawpheno_function_d3_version();
        $v = explode('.', $ver);

        if (trim($v[0]) != '3' OR (int)$v[1] < 4) {
          $requirements['d3_version'] = array(
            'severity' => REQUIREMENT_ERROR,
            'description' => $t('The D3 (ver %ver) library installed is not supported by this module. Please review the module requirements (!rawpheno_module) or download D3 version 3.5.14 (!download).',
              array('!rawpheno_module' => l('Rawphenotypes Module', 'https://github.com/UofS-Pulse-Binfo/rawphenotypes'), '%ver' => $ver, '!download' => l('D3 3.5.14', 'https://github.com/d3/d3/releases/download/v3.5.14/d3.zip'))
            ),
          );
        }
      }
      elseif ($i == 'excel_reader' && $info['is_in']) {
        $is_patched = rawpheno_function_library_patch();

        if (!$is_patched) {
          $requirements['patch'] = array(
            'severity' => REQUIREMENT_ERROR,
            'description' => $t('The Spreadsheet Reader library patch was not implemented. Please review the module requirements (!rawpheno_module).',
              array('!rawpheno_module' => l('Rawphenotypes Module', 'https://github.com/UofS-Pulse-Binfo/rawphenotypes'))
            ),
          );
        }
      }
      //
    }
  }

  return $requirements;
}