<?php
/*
Plugin Name: List Field Label Cache
Description: Adds a secret, read-only text field to complement list fields to aid in sorting and searching
*/

/*

HOW TO USE
==========

Update the $GLOBALS['LFLC_FIELDS'] array below to point to your list field in the format "tablename.fieldname", for example "products.categories".
List fields must be set to "Get options from database (advanced)" for this plugin to work with your field.
Both multi-value and single-value list fields are supported.
After you've changed the $GLOBALS['LFLC_FIELDS'] array, activate the plugin and click Rebuild Cache Fields on the Admin > Plugins page in the CMS.
The table you've selected will now have an extra field added, called "_categories_cache". This field will not appear on add/edit pages.
Go to Admin > Section Editor, and edit your table. In the General tab, add the _categories_cache field to your ListPage Fields.
You can now sort by the cache field and sorting will be alphabetical with the labels.
Also in the Section Editor, under the Searching tab, you can add the _categories_cache field to your Search Fields, or simply use the default "_all_" Search Field.
You can now search by the cache field, meaning that you can find records with a category label by searching for that label.

NOTES
=====

Records with cache fields are updated any time that record is saved in the CMS.
Whenever a record is saved in the table which is used to supply labels for your list field, all cache fields are rebuilt.
For example, when you edit a category and change its name, all products with that category in their cache field will be updated to reflect that change.

*/

// specify "optionsType=table" list fields to be cached here:
$GLOBALS['LFLC_FIELDS'] = array(
  "foo.category",
  "foo.categories",
);


// DO NOT UPDATE ANYTHING BELOW THIS LINE

$GLOBALS['_LFLC_FIELDS'] = array();
foreach ($GLOBALS['LFLC_FIELDS'] as $str) {
  list($tableName, $fieldname) = explode('.', $str);
  if (!@$GLOBALS['_LFLC_FIELDS'][$tableName]) { $GLOBALS['_LFLC_FIELDS'][$tableName] = array(); }
  $GLOBALS['_LFLC_FIELDS'][$tableName][] = $fieldname;
}

//
pluginAction_addHandlerAndLink("Rebuild Cache Fields", 'lflc_rebuild');
function lflc_rebuild() {
  foreach ($GLOBALS['_LFLC_FIELDS'] as $tableName => $fieldnames) {
    $schema = loadSchema($tableName);
    
    _lflc_ensureCacheFieldsExistForTable($tableName, $schema, $fieldnames);
    $records = mysql_select($tableName);
    _lflc_updateCacheFields($tableName, $schema, $records, $fieldnames);
  }
}


function _lflc_ensureCacheFieldsExistForTable($tableName, $schema, $fieldnames) {
  $schemaUpdated = false;
  foreach ($fieldnames as $fieldname) {
    $cacheFieldname = '_' . $fieldname . '_cache';
    
    // create cache fields if they don't already exist
    if (!@$schema[$cacheFieldname]) {
      $schema[$cacheFieldname] = array(
        '_tableName'        => $tableName,
        'name'              => $cacheFieldname,
        'order'             => time(),
        'label'             => $schema[$fieldname]['label'],
        'type'              => 'textfield',
        'isSystemField'     => '1',
      );
      $schemaUpdated = true;
    }
  }
  
  // update schema if required
  if ($schemaUpdated) {
    saveSchema($tableName, $schema);
    createMissingSchemaTablesAndFields();
  }
}

// 
addFilter('edit_show_field', 'lflc_edit_show_field', null, 3);
function lflc_edit_show_field($retval, $fieldSchema, $record) {
  global $tableName;
  $fieldnames = @$GLOBALS['_LFLC_FIELDS'][$tableName];
  if ($fieldnames && preg_match('/^_(\w+)_cache$/', $fieldSchema['name'], $matches)) {
    list(, $sourceFieldname) = $matches;
    if (in_array($sourceFieldname, $fieldnames)) {
      return false;
    }
  }
  return $retval;
}

// 
addAction('record_presave', 'lflc_record_presave', null, 3);
function lflc_record_presave($tableName, $isNewRecord, $oldRecord) {
  global $schema;
  $fieldnames = @$GLOBALS['_LFLC_FIELDS'][$tableName];
  if (!$fieldnames) { return; }
  
  _lflc_ensureCacheFieldsExistForTable($tableName, $schema, $fieldnames);
  
  // add a temporary bogus request value for the cache field so textfield code doesn't error out
  foreach ($fieldnames as $fieldname) {
    $cacheFieldname = '_' . $fieldname . '_cache';
    $_REQUEST[$cacheFieldname] = ''; // to be set by postsave!
  }
}

// 
addAction('record_postsave', 'lflc_record_postsave', null, 4);
function lflc_record_postsave($tableName, $isNewRecord, $oldRecord, $recordNum) {
  global $schema;
  
  // update cache fields for this record?
  $fieldnames = @$GLOBALS['_LFLC_FIELDS'][$tableName];
  if ($fieldnames) {
    $records = array( mysql_get($tableName, $recordNum) );
    _lflc_updateCacheFields($tableName, $schema, $records, $fieldnames);
  }
  
  // check if this table is a related table for any LFLC_FIELDS
  foreach ($GLOBALS['_LFLC_FIELDS'] as $otherTableName => $fieldnames) {
    $needToRebuildCacheFieldsForOtherTable = false;
    $otherSchema = loadSchema($otherTableName);
    foreach ($fieldnames as $fieldname) {
      if ($tableName === @$otherSchema[$fieldname]['optionsTablename']) {
        $needToRebuildCacheFieldsForOtherTable = true;
      }
    }
    
    if ($needToRebuildCacheFieldsForOtherTable) {
      _lflc_ensureCacheFieldsExistForTable($otherTableName, $otherSchema, $fieldnames);
      $records = mysql_select($otherTableName);
      _lflc_updateCacheFields($otherTableName, $otherSchema, $records, $fieldnames);
    }
  }
}

//
function _lflc_updateCacheFields($tableName, $schema, $records, $fieldnames) {
  
  // load psuedo-fields
  require_once $GLOBALS['PROGRAM_DIR'] . "/lib/viewer_functions.php";
  _getRecords_addPseudoFields($records, array('tableName' => $tableName), $schema);
  
  foreach ($records as $record) {
    // build a list of cache fields to update
    $updates = array();
    foreach ($fieldnames as $fieldname) {
      $cacheFieldname = '_' . $fieldname . '_cache';
      
      $label = coalesce(@$record[$fieldname . ':label'], @$record[$fieldname . ':labels']);
      if (is_array($label)) {
        sort($label); // alphabetic sort of labels in multi-value list
        $label = implode(', ', $label);
      }
      $updates[$cacheFieldname] = $label;
    }
    
    mysql_update($tableName, $record['num'], null, $updates);
  }
}


?>