<?php
/*
Plugin Name: Related Record Lookup Functions
Plugin URI:
Description: (BETA!) Makes two functions available for your Viewers: beta_lookupRelatedFields() and beta_lookupReferringRecords()
Author: chris
Version: 0.3
Author URI:
*/


/*******************************************************************************
 * FUNCTION:    beta_lookupRelatedFields
 * DESCRIPTION: load related records from other sections which are referenced by the listed fields, and inject them into our already loaded records
 * EXAMPLE:     beta_lookupRelatedFields(array(
 *                'table'      => 'product',
 *                'recordList' => &$productRecords,
 *                'fieldList'  => array(
 *                  'primary_category', 'other_categories'
 *                )
 *              ));
 *              foreach ( $productRecords as $product ) {
 *                echo($product['title']);
 *                echo($product['primary_category']['title']);
 *                foreach ( $product['other_categories'] as $category ) {
 *                  echo($category['title']);
 *                }
 *              }
 */
# e.g. lookupRelatedFields('inventory', $inventoryRecords, array('model', 'manufacturer'))
#      > this will replace the nums in $inventoryRecords[*]['model'] and $inventoryRecords[*]['manufacturer'] with the appropriate records from the appropriate sections
function beta_lookupRelatedFields($options) {
  $tableName     = $options['table']       or die("lookupReferringRecords: 'table' option missing");
  $recordList    =& $options['recordList'] or die("lookupReferringRecords: 'recordList' option missing");
  $fieldNameList = $options['fieldList']   or die("lookupReferringRecords: 'fieldList' option missing");
  
  $schema = loadSchema($tableName);
  
  // check supplied fields and get referenced table names if necessary (and possible)
  $refFieldNameList = array();
  foreach ( $fieldNameList as $key => $value ) {
    if (!is_numeric($key)) {
      $refFieldNameList[ $key ] = $value;
    }
    else {
      $fieldName = $value; // ignore key
      $field = $schema[$fieldName];
      if ( !$field ) { die("lookupRelatedFields: field '$fieldName' not found"); }
      if ( @$field['optionsType'] != 'table' ) { die("lookupRelatedFields: field '$fieldName' is not set to 'Get options from database (advanced)'"); }
      if ( !@$field['optionsTablename'] ) { die("lookupRelatedFields: field '$fieldName' does not reference another table"); }
      if ( @$field['optionsValueField'] != 'num' ) { die("lookupRelatedFields: field '$fieldName' is not set to use 'num' for option values"); }
      $refFieldNameList[ $fieldName ] = $field['optionsTablename'];
    }
  }
  
  // look through supplied fields and records, compiling a list of tables and nums to lookup
  $lookupsToDo = array();
  foreach ( $refFieldNameList as $fieldName => $refTableName ) {
    
    // add an array for this table if we don't have one already
    if (!@$lookupsToDo[$refTableName]) { $lookupsToDo[$refTableName] = array(); }
    
    // add nums to be looked up in the referenced table
    foreach ( $recordList as $record ) {
      if (@$record[$fieldName]) {
        $refNums = explode("\t", trim($record[$fieldName]));
        foreach ( $refNums as $refNum ) {
          $lookupsToDo[$refTableName][ $refNum ] = true;
        }
      }
    }
  }
  
  // get referenced records
  $referencedRecords = array();
  foreach ( $lookupsToDo as $refTable => $refNums ) {
    if (!empty($refNums)) {
      $refNumsCommaSep = join(', ', array_keys($refNums));
      list($refRecords, $refMeta) = getRecords(array(
        'tableName'   => $refTable,
        'where'       => "num in ($refNumsCommaSep)",
        'allowSearch' => false,
      ));
      $refRecordsByNum = array_combine(beta_array_collect($refRecords, 'num'), $refRecords);
      $referencedRecords[ $refTable ] = $refRecordsByNum;
    }
  }

  // inject referenced records into original records' fields
  foreach ( array_keys($recordList) as $recordKey ) {
    $record =& $recordList[$recordKey];
    foreach ( $refFieldNameList as $fieldName => $refTableName ) {
      $field = $schema[$fieldName];
      
      if ($field['listType'] == 'checkboxes' || $field['listType'] == 'pulldownMulti') {
        $newValues = array();
        if (@$record[$fieldName]) {
          $refNums = explode("\t", trim($record[$fieldName]));
          foreach ( $refNums as $refNum ) {
            $newValues[] =& $referencedRecords[ $refTableName ][ $refNum ];
          }
        }
        $record[$fieldName] = $newValues;
      }
      else {
        $record[$fieldName] =& $referencedRecords[ $refTableName ][ $record[$fieldName] ];
      }
    }
  }
}

/*******************************************************************************
 * FUNCTION:    beta_lookupReferringRecords
 * DESCRIPTION: load related records from another section which reference already loaded records, and inject arrays of them into our already loaded records
 * EXAMPLE:     beta_lookupReferringRecords(array(
 *                'sourceTable'    => 'category',
 *                'recordList'     => &$categoryRecords,
 *                'injectionField' => 'products'
 *                'foreignTable'   => 'product',
 *                'foreignFields'  => array( 'category' ),
 *              ));
 *              foreach ( $categoryRecords as $category ) {
 *                echo($category['title']);
 *                foreach ( $category['products'] as $product ) {
 *                  echo($product['title']);
 *                }
 *              }
 */
function beta_lookupReferringRecords($options) {
  $sourceTableName    = $options['sourceTable']    or die("lookupReferringRecords: 'sourceTable' option missing");
  $recordList         =& $options['recordList']    or die("lookupReferringRecords: 'recordList' option missing");
  $foreignTableName   = $options['foreignTable']   or die("lookupReferringRecords: 'foreignTable' option missing");
  $foreignFieldNames  = $options['foreignFields']  or die("lookupReferringRecords: 'foreignFields' option missing");
  $injectionFieldName = $options['injectionField'] or die("lookupReferringRecords: 'injectionField' option missing");
  
  $recordListByNum = array();
  foreach ( array_keys($recordList) as $recordKey ) {
    $record =& $recordList[$recordKey];
    $record[ $injectionFieldName ] = array();
    $recordListByNum[ $record['num'] ] =& $record;
  }

  $sourceNums = beta_array_collect($recordList, 'num');
  
  $foreignSchema = loadSchema($foreignTableName);
  $whereClauses = array();
  foreach ( $foreignFieldNames as $foreignFieldName ) {
    $foreignField = @$foreignSchema[$foreignFieldName];
    if (!$foreignField) { die("lookupReferringRecords: field '$foreignFieldName' not found in section '$foreignTableName'"); }
    if (@$foreignField['optionsType'] != 'table') { die("lookupReferringRecords: field '$foreignFieldName' in section '$foreignTableName' is not set to 'Get options from database (advanced)'"); }
    if (@$foreignField['optionsTablename'] != $sourceTableName) { die("lookupReferringRecords: field '$foreignFieldName' in section '$foreignTableName' does not reference source table '$sourceTableName'"); }
    if (@$foreignField['optionsValueField'] != 'num' ) { die("lookupReferringRecords: field '$foreignFieldName' in section '$foreignTableName' is not set to use 'num' for option values"); }
    
    // foreign field references multiple source nums
    if ($foreignField['listType'] == 'checkboxes' || $foreignField['listType'] == 'pulldownMulti') {
      $sourceNumsPipeSep = join('|', $sourceNums);
      array_push($whereClauses, "$foreignFieldName regexp '\t($sourceNumsPipeSep)\t'");
    }
    // foreign field references a single source num
    else {
      $sourceNumsCommaSep = join(',', $sourceNums);
      array_push($whereClauses, "$foreignFieldName in ($sourceNumsCommaSep)");
    }
  }

  list($foreignRecords, $foreignMeta) = getRecords(array(
    'tableName'   => $foreignTableName,
    'where'       => join(' OR ', $whereClauses),
    'allowSearch' => false,
  ));
  
  foreach ( $foreignRecords as $foreignRecord ) {
    // look through foreign fields and keep track of what nums they reference
    $referencedNumsForThisForeignRecord = array();
    foreach ( $foreignFieldNames as $foreignFieldName ) {
      $foreignField = @$foreignSchema[$foreignFieldName];
      
      // foreign field references multiple source nums
      if ($foreignField['listType'] == 'checkboxes' || $foreignField['listType'] == 'pulldownMulti') {
        foreach ( explode("\t", trim($foreignRecord[ $foreignFieldName ])) as $sourceNum ) {
          $referencedNumsForThisForeignRecord[ $sourceNum ] = true;
        }
      }
      // foreign field references a single source num
      else {
        $sourceNum = $foreignRecord[ $foreignFieldName ];
        $referencedNumsForThisForeignRecord[ $sourceNum ] = true;
      }
    }
    // add this foreign record (once) to each source record that was referenced at least once
    foreach ( array_keys($referencedNumsForThisForeignRecord) as $referencedNum ) {
      if ( @$recordListByNum[ $referencedNum ] ) {
        $recordListByNum[ $referencedNum ][ $injectionFieldName ][] = $foreignRecord;
      }
    }
  }
}

// beta_array_collect: utility function which returns specific key/column from an array
function beta_array_collect($array, $key) {
  $result = array();
  foreach ( $array as $element ) {
    if(!in_array($element[$key], $result)) {
      array_push($result, $element[$key]);
    }
  }
  return $result;
}

?>