<?php
// $Id: tw_pages.inc,v 1.1.2.13 2009-03-31 00:17:32 mikeryan Exp $
/**
 * @file
 * Table Wizard menu callbacks
 */

define('TW_COLS_ALL', 1);
define('TW_COLS_EMPTY', 2);
define('TW_COLS_NONEMPTY', 3);

/**
 * Enter description here...
 *
 * @return unknown
 */
function tw_front() {
  $content = t('
          
    ');
  module_load_include('inc', 'system', 'system.admin');
  return system_admin_menu_block_page().$content;
}

/**
 * Enter description here...
 *
 * @return unknown
 */
function tw_sources() {
  return drupal_get_form('_tw_sources_form');
}

/**
 * Enter description here...
 *
 * @param unknown_type $form_state
 * @return unknown
 */
function _tw_sources_form($form_state) {
  $form['description'] = array(
    '#prefix' => '<div>',
    '#value' => t('Tables managed by the Table Wizard module are listed here, each with the name of the database
      table used to store the data, and statistics on the amount of data. Click the <b>Analyze</b>
      link to view and edit information on the fields available in the table. Click on the table
      name to view the data in the table and mark specific rows for exclusion.'),
    '#suffix' => '</div>',
  );

  $tableheader = array('data' => t('Table name'), 'field' => 'twt.tablename', 'sort' => 'asc');
  $form['header'] = array(
    '#type' => 'value',
    '#value' => array(
      array('data' => t('Delete')),
      array('data' => ''),
      $tableheader,
    ),
  );

  $sql = "SELECT * FROM {tw_tables} twt";
  $tablesort = tablesort_sql(array($tableheader));
  $result = db_query($sql . $tablesort);
  $checks = array();
  while ($row = db_fetch_object($result)) {
    $checks[$row->twtid] = '';
    $form['twtid'][$row->twtid] = array('#value' => $row->twtid);
    $form['analyze'][$row->twtid] = array('#value' => 
      l(t('Analyze'), 'admin/content/tw/analyze/'. $row->tablename, array('html' => TRUE)));
    $form['tablename'][$row->twtid] = array('#value' => 
      l($row->tablename, 'admin/content/tw/view/'. $row->tablename, array('html' => TRUE)));
  }

  $form['checks'] = array('#type' => 'checkboxes', '#options' => $checks);
  $form['#theme'] = 'tw_sources';
  $form['delete'] = array(
    '#type' => 'submit',
    '#value' => t('Remove selected tables'),
  );

  // Keep each type's fields apart
  $form['#tree'] = TRUE;
  
  // Note that fieldset names ('existing', 'delimited', ...) are the source type names
  $sourcefieldsets = module_invoke_all('tw_form');
  $form = array_merge($form, $sourcefieldsets);
  // Support file uploads
  $form['#attributes'] = array('enctype' => 'multipart/form-data');

  return $form;
}

function theme_tw_sources($form) {
  $output = drupal_render($form['description']);

  if (isset($form['twtid']) && is_array($form['twtid'])) {
    foreach (element_children($form['twtid']) as $twtid) {
      $row = array();
      // Don't show the table id
      $null = drupal_render($form['twtid']);
      $row[] = drupal_render($form['checks'][$twtid]);
      $row[] = drupal_render($form['analyze'][$twtid]);
      $row[] = drupal_render($form['tablename'][$twtid]);
      $rows[] = $row;
    }
  }
  $header = $form['header']['#value'];
  if (!isset($rows)) {
    $rows[] = array(array('data' => t('No tables have been added..'),
                          'colspan' => count($header)));
  }
  $output .= theme('table', $header, $rows);
  $output .= drupal_render($form['delete']);
  $output .= drupal_render($form);
  return $output;
}

/**
 * Enter description here...
 *
 * @param unknown_type $form
 * @param unknown_type $form_state
 */
function _tw_sources_form_submit($form, &$form_state) {
  $type = $form_state['clicked_button']['#parents'][0];
  if ($type == 'delete') {
    foreach ($form_state['values']['checks'] as $twtid => $value) {
      if ($value) {
        $sql = "SELECT tablename FROM {tw_tables} WHERE twtid=%d";
        $tablename = db_result(db_query($sql, $twtid));
        tw_remove_tables($tablename);
        drupal_set_message(t('Removed table %tablename', array('%tablename' => $tablename)));
      }
    }
  } else {
    // Submit hooks return arrays of tablenames they're bringing in
    $values = $form_state['values'][$type];
    $tableset = module_invoke_all("tw_form_submit_$type", $values);
    tw_add_tables($tableset);
  }
}

/**
 * Add a fieldset into the import form, for identifying where to obtain source data.
 *
 * @return unknown
 */
function tw_tw_form() {
  ////////////////////////////////////////////////////////////
  // Add existing tables
  
  // Build list of tables we already manage
  $excludes = array();
  $sql = "SELECT * FROM {tw_tables}";
  $tblresult = db_query($sql);
  while ($tblrow = db_fetch_object($tblresult)) {
    $tblname = $tblrow->tablename;
    // Exclude the table itself...
    $excludes[$tblname] = $tblname;
  }

  $sql = 'SHOW TABLES';
  $result = db_query($sql);
  $options = array();
  while ($row = db_fetch_array($result)) {
    foreach ($row as $tablename) {
      if (!$excludes[$tablename]) {
        // Remove the prefix if any - views knows tables by their logical name, not the DB name
        $rawtablename = schema_unprefix_table($tablename);
        $prefixtablename = db_prefix_tables('{'.$rawtablename.'}');
        
        // See if this table already has a views definition - skip it if it does
        $views_data = views_fetch_data($rawtablename);
        if (!$views_data && ($tablename == $prefixtablename)) {
          $options[$tablename] = $tablename;
        }
      }
    }
  }
  
  // Add tables from any other available databases
  // TODO: Allow selecting a DB and updating the options list dynamically
  global $db_url;
  if (is_array($db_url)) {
    foreach ($db_url as $dbname => $url) {
      if ($dbname != 'default') {
        db_set_active($dbname);
        $sql = 'SHOW TABLES';
        $result = db_query($sql);
        while ($row = db_fetch_array($result)) {
          foreach ($row as $tablename) {
            $tablename = $dbname . '.' . $tablename;
            $options[$tablename] = $tablename;
          }
        }
      }
    }
    db_set_active('default');
  }
  
  $fieldsets['existing'] = array(
    '#type' => 'fieldset',
    '#title' => t('Add existing tables'),
    '#collapsible' => TRUE,
    '#collapsed' => TRUE,
    'uploadhelp' => array(
      '#prefix' => '<div>',
      '#value' => t('Add tables to the Table Wizard.'),
      '#suffix' => '</div>',
    ),
    'tablelist' => array(
      '#type' => 'select',
      '#title' => t('Available tables'),
      '#options' => $options,
      '#description' => t('Tables which are not currently managed
        by the Table Wizard or otherwise already made available to Views'),
    ),
    'existingsubmit' => array(
      '#type' => 'submit',
      '#value' => t('Add tables'),
     ),
  );
  return $fieldsets;
}

/**
 * Enter description here...
 *
 * @param unknown_type $values
 * @return unknown
 */
function tw_tw_form_submit_existing($values) {
  $tableset = array();
  $tablename = $values['tablelist'];
  return array($tablename);
}


/**
 * Menu callback function
 * TODO: Flag any available FKs that are not indexed
 *
 * @param unknown_type $form_state
 * @param unknown_type $table
 * @param unknown_type $flag
 * @return unknown
 */
function tw_analysis($form_state, $table, $flag=TW_COLS_NONEMPTY) {
  $row = db_fetch_object(db_query("SELECT twtid,tabledb FROM {tw_tables}
                                   WHERE tablename='%s'",
                                  $table));
  $twtid = $row->twtid;
  $tabledb = $row->tabledb;

  if (isset($_GET['reanalyze'])) {
    tw_perform_analysis($table, $tabledb);
    drupal_set_message(t('!tablename analyzed', array('!tablename' => $table)));
  }

  $form['twtid'] = array('#type' => 'value', '#value' => $twtid);
  $form['table'] = array('#type' => 'value', '#value' => $table);

  switch ($flag) {
    case TW_COLS_ALL: 
      $flaglabel = t('All fields');
      break;
    case TW_COLS_EMPTY:
      $flaglabel = t('Empty fields only');
      break;
    case TW_COLS_NONEMPTY:
      $flaglabel = t('Non-empty fields only');
      break;
  }
  
  $form['help'] = array(
    '#type' => 'fieldset',
    '#title' => t('Help'),
    '#collapsible' => TRUE,
    '#collapsed' => TRUE,
  );
  
  $form['help']['help1'] = array(
    '#prefix' => '<div>',
    '#value' => t('Fields in the table are listed below, with some information
      on the range and type of values each field contains. Comments may be entered to
      document each field individually, and any fields deemed unnecessary can be marked
      <b>Ignore</b> to omit them from views based on this table.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['help2'] = array(
    '#prefix' => '<div>',
    '#value' => t('The first time this page is visited for a file that has been
      uploaded (including the first time after a new copy has been imported), the entire
      contents are read and analyzed - this can take quite a while. The results of the analysis
      are saved so later visits to the analysis page are quicker. Sometimes, if changes have
      been made to the table behind the scenes, it\'s useful to force the full analysis
      by clicking <b>Reanalyze</b>.'),
    '#suffix' => '</div>',
  );

  $form['help']['help3'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>View contents</b> goes to the Table View page for this table.
      You may find it helpful to open this link in a new window, to browse the data in context
      while figuring out its meaning for the comments here.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['columns'] = array(
    '#prefix' => '<div>',
    '#value' => t('These are the columns in the table below:'),
    '#suffix' => '</div>',
  );
  $form['help']['fieldname'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Field name</b> - The name of the field as imported into the database table.
      This is not necessarily the same as the name in the original file - any non-alphanumeric
      characters are replaced by underscores (_), and duplicate column names have sequence
      numbers appended.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['ignore'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Ignore</b> - Checking this box for a field will omit that field from the 
      table view.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['pk'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Potential PK/PK</b> - Any field which could be used as a primary key for 
      the table (that is, each value is unique and there are no NULL values) is flagged with a 1. 
      The PK checkbox is used to define a field as the primary key for the table.'),
    '#suffix' => '</div>',
  );
    
  $form['help']['fk'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Available FK</b> - Check this box to make the field available for use
      in table relationships.'),
    '#suffix' => '</div>',
  );
    
  $form['help']['strlength'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>String length</b> - The ranges of string lengths in this field, which helps
      determine whether the data will fit into the corresponding Drupal field when migrated.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['numrange'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Numeric value range</b> - For potential numeric fields, the range of numeric 
      values they contain (determined numerically - e.g., 10 is greater than 9).'),
    '#suffix' => '</div>',
  );
  
  $form['help']['strrange'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>String value range</b> - For all fields, the range of string values they 
      contained (determined by character order - e.g., 9 is greater than 10). It\'s important to 
      note that at this time all fields except the PK are being stored as strings, so sorting on 
      "numeric" fields in the Table View won\'t necessarily have the expected effect.'),
    '#suffix' => '</div>',
  );
  
  $form['help']['comments'] = array(
    '#prefix' => '<div>',
    '#value' => t('<b>Comments</b> - Description of the field - what it means, and what we will do 
      with it in the migration (ignore it, map it to a given user field or node field, etc.).'),
    '#suffix' => '</div>',
  );
  
  
  $form['title'] = array('#value' => "Analysis of $table");
  $form['header'] = array(
    '#type' => 'value',
    '#value' => array(
      array('data' => t('Field name')),
      array('data' => t('Ignore')),
      array('data' => t('PK')),
      array('data' => t('Available FK')),
      array('data' => t('Current type')),
      array('data' => t('String length')),
      array('data' => t('Range')),
      array('data' => t('Comments')),
    ),
  );

  $colcount = 0;
  $emptycount = 0;
  $result = db_query("SELECT *  
                      FROM {tw_columns} 
                      WHERE twtid=%d
                      ORDER BY weight",
                      $twtid);

  $availablefks = array();
  $availablefkenable = array();
  $ignoredcols = array();
  $ignoreenable = array();
  $cols = array();
  $pks = array();
  while ($row = db_fetch_object($result)) {
    $colname = $row->colname;
    $colcount++;
    if ($row->isempty) {
      $emptycount++;
    }
    if (($flag == TW_COLS_ALL) ||
       (($flag == TW_COLS_EMPTY) && $row->isempty) ||
       (($flag == TW_COLS_NONEMPTY) && !$row->isempty)) {
      $cols[] = $colname;
      $form['colname'][$colname] = array('#value' => $colname);
      $ignoredcols[$colname] = '';
      if ($row->ignorecol) {
        $ignoreenable[] = $colname;
      }
      $form['primarykey'][$colname] = array('#value' => $row->primarykey);
      if ($row->primarykey) {
        $pks[] = $colname;
      }
      $availablefks[$colname] = '';
      if ($row->availablefk) {
        $availablefkenable[] = $colname;
      }
        
      $form['currtype'][$colname] = array('#value' => $row->coltype);

      if (tw_column_type($row->coltype) == 'text') {
        $form['lengths'][$colname] = array('#value' => 
          $row->maxlength ? $row->minlength.'-'.$row->maxlength : ''
        );
        
        $form['values'][$colname] = array(
          '#attributes' => array('style' => 'white-space: normal'),
          '#prefix' => '<div style="white-space: normal">',
          '#value' => 
            $row->maxlength ? substr(check_plain($row->minstring),0,40).'<hr />'.
              substr(check_plain($row->maxstring),0,40) : '',
          '#suffix' => '</div>',
        );
      } else {
        $form['lengths'][$colname] = array('#value' => '');
        $form['values'][$colname] = array('#value' => 
          $row->maxvalue ? $row->minvalue.'-'.$row->maxvalue : ''
        );
      }
      $form['comments'][$colname] = array(
        '#type' => 'textarea',
        '#default_value' => $row->comments,
        '#cols' => 45,
        '#rows' => 3,
        '#resizable' => FALSE,
      );
    }
  }
  
  $form['availablefk'] = array(
    '#type' => 'checkboxes', 
    '#options' => $availablefks,
    '#default_value' => $availablefkenable,
  );
  
  $form['ignorecol'] = array(
    '#type' => 'checkboxes', 
    '#options' => $ignoredcols,
    '#default_value' => $ignoreenable,
  );
  
  $form['cols'] = array(
    '#type' => 'value',
    '#value' => $cols,
  );

  $form['colcounts'] = array('#value' => "$emptycount of $colcount fields have no data for any row.");
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Submit changes'),
  );
  
  if (count($pks) == 0) {
    drupal_set_message(t('!tablename has no primary key defined. A single-column primary
      key is necessary to use this table as the base table for a view.', 
      array('!tablename' => $table)));
  } elseif (count($pks) > 1) {
    drupal_set_message(t('!tablename has a multiple-column primary key. A single-column primary
      key is necessary to use this table as the base table for a view.', 
      array('!tablename' => $table)));
  }
  
  return $form;
}

/**
 * Enter description here...
 *
 * @param unknown_type $form
 * @return unknown
 */
function theme_tw_analysis($form) {
  $title = drupal_render($form['title']);
  $table = $form['table']['#value'];
  drupal_set_title($title);
  $output = '<div class="tw_analyze_links">';
  $output .= '<b>Operations on this table: </b>';
  $output .= l(t('Reanalyze'), 
               "admin/content/tw/analyze/$table/", 
               array('html' => TRUE, 'query' => 'reanalyze=1')).' | ';
  $output .= l(t('View table contents'), 
               "admin/content/tw/view/$table/", 
               array('html' => TRUE));
  $output .= '</div>';
  $output .= '<div class="tw_analyze_links">';
  $output .= '<b>Filter: </b>';
  $output .= l(t('Show all fields'), 
               "admin/content/tw/analyze/$table/".TW_COLS_ALL, 
               array('html' => TRUE)).' | ';
  $output .= l(t('Show only fields that are empty for all rows'), 
               "admin/content/tw/analyze/$table/".TW_COLS_EMPTY, 
               array('html' => TRUE)).' | ';
  $output .= l(t('Show only fields which have data for at least one row'), 
               "admin/content/tw/analyze/$table/".TW_COLS_NONEMPTY, 
               array('html' => TRUE));
  $output .= '</div>';
  $output .= '<div>'.drupal_render($form['colcounts']).'</div>';

  $output .= drupal_render($form['help']);

  if (isset($form['colname']) && is_array($form['colname'])) {
    foreach(element_children($form['colname']) as $colname) {
      $row = array();
      $row[] = drupal_render($form['colname'][$colname]);
      $row[] = drupal_render($form['ignorecol'][$colname]);
      $row[] = drupal_render($form['primarykey'][$colname]);
      $row[] = drupal_render($form['availablefk'][$colname]);
      $row[] = drupal_render($form['currtype'][$colname]);
      $row[] = drupal_render($form['lengths'][$colname]);
      $row[] = drupal_render($form['values'][$colname]);
      $row[] = drupal_render($form['comments'][$colname]);
      $rows[] = $row;
    }
  }
  
  $header = $form['header']['#value'];
  if (!isset($rows)) {
    $rows[] = array(array('data' => t('There are no non-empty columns in the table.'), 
                          'colspan' => count($header)));
  }
  
  $output .= theme('table', $header, $rows);
  $output .= drupal_render($form);

  return $output;
}

/**
 * Implementation of hook_submit()
 *
 * @param unknown_type $form
 * @param unknown_type $form_state
 */
function tw_analysis_submit($form, &$form_state) {
  $twtid = $form_state['values']['twtid'];
  foreach($form_state['values']['cols'] as $key => $colname) {
    $newignore = $form_state['values']['ignorecol'][$colname] ? 1 : 0;
    $newavailablefk = $form_state['values']['availablefk'][$colname] ? 1 : 0;
    $newcomment = $form_state['values'][$colname];
    $newfk = $form_state['values'][$colname.'_fk'];
    db_query("UPDATE {tw_columns}
              SET ignorecol=%d, availablefk=%d, comments='%s'
              WHERE twtid=%d AND colname='%s'",
             $newignore, $newavailablefk, $newcomment, $twtid, $colname);
  }
  drupal_flush_all_caches();
  drupal_set_message(t('Your changes have been saved'));
}

/**
 * Enter description here...
 *
 * @return unknown
 */
function tw_relationships() {
  return(drupal_get_form('_tw_relationships_form'));
}

/**
 * Enter description here...
 *
 * @param unknown_type $form_state
 * @return unknown
 */
function _tw_relationships_form($form_state) {
  $form['description'] = array(
    '#prefix' => '<div>',
    '#value' => t('Define relationships between tables, enabling the creation
      of views joining those tables.'),
    '#suffix' => '</div>',
  );
  $form['header'] = array(
    '#type' => 'value',
    '#value' => array(
      array('data' => t('Delete')),
      array('data' => t('Left column')),
      array('data' => t('Right column')),
    ),
  );

  $sql = "SELECT twr.twrid,CONCAT(twtleft.tablename, '.', twcleft.colname) leftcol,
                 CONCAT(twtright.tablename, '.', twcright.colname) rightcol,
                 twtleft.tabledb leftdb, twtright.tabledb rightdb
          FROM {tw_relationships} twr
          INNER JOIN {tw_columns} twcleft ON twr.leftcol=twcleft.twcid
          INNER JOIN {tw_tables} twtleft ON twcleft.twtid=twtleft.twtid
          INNER JOIN {tw_columns} twcright ON twr.rightcol=twcright.twcid
          INNER JOIN {tw_tables} twtright ON twcright.twtid=twtright.twtid
          ORDER BY leftdb, leftcol, rightcol";
  $result = db_query($sql);
  $checks = array();
  while ($row = db_fetch_object($result)) {
    $checks[$row->twrid] = '';
    $form['twrid'][$row->twrid] = array('#value' => $row->twrid);
    if ($row->leftdb != 'default') {
      $row->leftcol = $row->leftdb . '.' . $row->leftcol;
    }
    $form['leftcol'][$row->twrid] = array('#value' => $row->leftcol);
    if ($row->rightdb != 'default') {
      $row->rightcol = $row->rightdb . '.' . $row->rightcol;
    }
    $form['rightcol'][$row->twrid] = array('#value' => $row->rightcol);
  }

  $form['checks'] = array('#type' => 'checkboxes', '#options' => $checks);
  $form['#theme'] = 'tw_relationships';
  $form['delete'] = array(
    '#type' => 'submit',
    '#value' => t('Delete selected relationships'),
  );
  $form['addrel'] = array(
    '#type' => 'fieldset',
    '#title' => t('Add a relationship'),
    '#collapsible' => TRUE,
  );
  $sql = "SELECT twc.twcid, twc.colname, twt.tablename, twt.tabledb
          FROM {tw_columns} twc
          INNER JOIN {tw_tables} twt ON twc.twtid=twt.twtid
          WHERE twc.availablefk=1
          ORDER BY tabledb, tablename, colname";
  $result = db_query($sql);
  while ($row = db_fetch_array($result)) {
    if ($row['tabledb'] == 'default') {
      $options[$row['twcid']] = $row['tablename'] . '.' . $row['colname'];
    } else {
      $options[$row['twcid']] = $row['tabledb'] . '.' . $row['tablename'] . '.' . $row['colname'];
    }
  }
  $form['addrel']['newleftcol'] = array(
    '#type' => 'select',
    '#title' => t('Column to appear on the left side of the join'),
    '#options' => $options,
  );
  $form['addrel']['newrightcol'] = array(
    '#type' => 'select',
    '#title' => t('Column to appear on the right side of the join'),
    '#options' => $options,
  );
  $form['addrel']['add'] = array(
    '#type' => 'submit',
    '#value' => t('Add'),
  );
  return $form;
}

/**
 * Enter description here...
 *
 * @param unknown_type $form
 */
function theme_tw_relationships($form) {
  $output = drupal_render($form['description']);

  if (isset($form['twrid']) && is_array($form['twrid'])) {
    foreach (element_children($form['twrid']) as $twrid) {
      $row = array();
      // Don't show the row ID
      $null = drupal_render($form['twrid']);
      $row[] = drupal_render($form['checks'][$twrid]);
      $row[] = drupal_render($form['leftcol'][$twrid]);
      $row[] = drupal_render($form['rightcol'][$twrid]);
      $rows[] = $row;
    }
  }
  $header = $form['header']['#value'];
  if (!isset($rows)) {
    $rows[] = array(array('data' => t('No relationships have been defined.'),
                          'colspan' => count($header)));
  }
  $output .= theme('table', $header, $rows);
  $output .= drupal_render($form['delete']);
  $output .= drupal_render($form['addrel']);
  $output .= drupal_render($form);
  
  return $output;
}
/**
 * Enter description here...
 *
 * @param unknown_type $form
 * @param unknown_type $form_state
 */
function _tw_relationships_form_submit($form, &$form_state) {
  if ($form_state['clicked_button']['#parents'][0] == 'delete') {
    foreach ($form_state['values']['checks'] as $twrid => $value) {
      if ($value) {
        tw_delete_relationship($twrid);
        $count++;
      }
    }
    if ($count) {
      drupal_set_message(format_plural($count, '1 relationship deleted', '@count relationships deleted'));
    }
  } else {
    $leftcol = $form_state['values']['newleftcol'];
    $rightcol = $form_state['values']['newrightcol'];
    tw_add_relationship($leftcol, $rightcol);
  }
  return;
}