CRM/Utils/Weight.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.4                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2013                                |
   7   +--------------------------------------------------------------------+
   8   | This file is a part of CiviCRM.                                    |
   9   |                                                                    |
  10   | CiviCRM is free software; you can copy, modify, and distribute it  |
  11   | under the terms of the GNU Affero General Public License           |
  12   | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13   |                                                                    |
  14   | CiviCRM is distributed in the hope that it will be useful, but     |
  15   | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16   | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17   | See the GNU Affero General Public License for more details.        |
  18   |                                                                    |
  19   | You should have received a copy of the GNU Affero General Public   |
  20   | License and the CiviCRM Licensing Exception along                  |
  21   | with this program; if not, contact CiviCRM LLC                     |
  22   | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23   | GNU Affero General Public License or the licensing of CiviCRM,     |
  24   | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25   +--------------------------------------------------------------------+
  26 */
  27 class CRM_Utils_Weight {
  28   /**
  29    * @var array, list of GET fields which must be validated
  30    *
  31    * To reduce the size of this patch, we only sign the exploitable fields
  32    * which make up "$baseURL" in addOrder() (eg 'filter' or 'dao').
  33    * Less-exploitable fields (eg 'dir') are left unsigned.
  34    */
  35   static $SIGNABLE_FIELDS = array('reset', 'dao', 'idName', 'url', 'filter'); // 'id','src','dst','dir'
  36
  37   /**
  38    * Function to correct duplicate weight entries by putting them (duplicate weights) in sequence.
  39    *
  40    * @param string  $daoName full name of the DAO
  41    * @param array   $fieldValues field => value to be used in the WHERE
  42    * @param string  $weightField field which contains the weight value,
  43    * defaults to 'weight'
  44    *
  45    * @return bool
  46    */
  47   static function correctDuplicateWeights($daoName, $fieldValues = NULL, $weightField = 'weight') {
  48     $selectField = "MIN(id) AS dupeId, count(id) as dupeCount, $weightField as dupeWeight";
  49     $groupBy = "$weightField having dupeCount>1";
  50
  51     $minDupeID = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField, NULL, NULL, $groupBy);
  52
  53     // return early if query returned empty
  54     // CRM-8043
  55     if (!$minDupeID->fetch()) {
  56       return TRUE;
  57     }
  58
  59     if ($minDupeID->dupeId) {
  60       $additionalWhere = "id !=" . $minDupeID->dupeId . " AND $weightField >= " . $minDupeID->dupeWeight;
  61       $update          = "$weightField = $weightField + 1";
  62       $status          = CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
  63     }
  64
  65     if ($minDupeID->dupeId && $status) {
  66       //recursive call to correct all duplicate weight entries.
  67       return CRM_Utils_Weight::correctDuplicateWeights($daoName, $fieldValues, $weightField);
  68     }
  69     elseif (!$minDupeID->dupeId) {
  70       // case when no duplicate records are found.
  71       return TRUE;
  72     }
  73     elseif (!$status) {
  74       // case when duplicate records are found but update status is false.
  75       return FALSE;
  76     }
  77   }
  78
  79   /**
  80    * Remove a row from the specified weight, and shift all rows below it up
  81    *
  82    * @param string $daoName full name of the DAO
  83    * $param integer $weight the weight to be removed
  84    * @param array $fieldValues field => value to be used in the WHERE
  85    * @param string $weightField field which contains the weight value,
  86    * defaults to 'weight'
  87    *
  88    * @return bool
  89    */
  90   static function delWeight($daoName, $fieldID, $fieldValues = NULL, $weightField = 'weight') {
  91     $object = new $daoName();
  92     $object->id = $fieldID;
  93     if (!$object->find(TRUE)) {
  94       return FALSE;
  95     }
  96
  97     $weight = (int)$object->weight;
  98     if ($weight < 1) {
  99       return FALSE;
 100     }
 101
 102     // fill the gap
 103     $additionalWhere = "$weightField > $weight";
 104     $update          = "$weightField = $weightField - 1";
 105     $status          = CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
 106
 107     return $status;
 108   }
 109
 110   /**
 111    * Updates the weight fields of other rows according to the new and old weight passed in.
 112    * And returns the new weight be used. If old-weight not present, Creates a gap for a new row to be inserted
 113    * at the specified new weight
 114    *
 115    * @param string $daoName full name of the DAO
 116    * @param integer $oldWeight
 117    * @param integer $newWeight
 118    * @param array $fieldValues field => value to be used in the WHERE
 119    * @param string $weightField field which contains the weight value,
 120    * defaults to 'weight'
 121    *
 122    * @return int
 123    */
 124   static function updateOtherWeights($daoName, $oldWeight, $newWeight, $fieldValues = NULL, $weightField = 'weight') {
 125     $oldWeight = (int ) $oldWeight;
 126     $newWeight = (int ) $newWeight;
 127
 128     // max weight is the highest current weight
 129     $maxWeight = CRM_Utils_Weight::getMax($daoName, $fieldValues, $weightField);
 130     if (!$maxWeight) {
 131       $maxWeight = 1;
 132     }
 133
 134     if ($newWeight > $maxWeight) {
 135       //calculate new weight, CRM-4133
 136       $calNewWeight = CRM_Utils_Weight::getNewWeight($daoName, $fieldValues, $weightField);
 137
 138       //no need to update weight for other fields.
 139       if ($calNewWeight > $maxWeight) {
 140         return $calNewWeight;
 141       }
 142       $newWeight = $maxWeight;
 143
 144       if (!$oldWeight) {
 145         return $newWeight + 1;
 146       }
 147     }
 148     elseif ($newWeight < 1) {
 149       $newWeight = 1;
 150     }
 151
 152     // if they're the same, nothing to do
 153     if ($oldWeight == $newWeight) {
 154       return $newWeight;
 155     }
 156
 157     // if oldWeight not present, indicates new weight is to be added. So create a gap for a new row to be inserted.
 158     if (!$oldWeight) {
 159       $additionalWhere = "$weightField >= $newWeight";
 160       $update = "$weightField = ($weightField + 1)";
 161       CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
 162       return $newWeight;
 163     }
 164     else {
 165       if ($newWeight > $oldWeight) {
 166         $additionalWhere = "$weightField > $oldWeight AND $weightField <= $newWeight";
 167         $update = "$weightField = ($weightField - 1)";
 168       }
 169       elseif ($newWeight < $oldWeight) {
 170         $additionalWhere = "$weightField >= $newWeight AND $weightField < $oldWeight";
 171         $update = "$weightField = ($weightField + 1)";
 172       }
 173       CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
 174       return $newWeight;
 175     }
 176   }
 177
 178   /**
 179    * returns the new calculated weight.
 180    *
 181    * @param string  $daoName     full name of the DAO
 182    * @param array   $fieldValues field => value to be used in the WHERE
 183    * @param string  $weightField field which used to get the wt, default to 'weight'.
 184    *
 185    * @return integer
 186    */
 187   static function getNewWeight($daoName, $fieldValues = NULL, $weightField = 'weight') {
 188     $selectField     = "id AS fieldID, $weightField AS weight";
 189     $field           = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField);
 190     $sameWeightCount = 0;
 191     $weights         = array();
 192     while ($field->fetch()) {
 193       if (in_array($field->weight, $weights)) {
 194         $sameWeightCount++;
 195       }
 196       $weights[$field->fieldID] = $field->weight;
 197     }
 198
 199     $newWeight = 1;
 200     if ($sameWeightCount) {
 201       $newWeight = max($weights) + 1;
 202
 203       //check for max wt should not greater than cal max wt.
 204       $calMaxWt = min($weights) + count($weights) - 1;
 205       if ($newWeight > $calMaxWt) {
 206         $newWeight = $calMaxWt;
 207       }
 208     }
 209     elseif (!empty($weights)) {
 210       $newWeight = max($weights);
 211     }
 212
 213     return $newWeight;
 214   }
 215
 216   /**
 217    * returns the highest weight.
 218    *
 219    * @param string $daoName full name of the DAO
 220    * @param array  $fieldValues field => value to be used in the WHERE
 221    * @param string $weightField field which contains the weight value,
 222    * defaults to 'weight'
 223    *
 224    * @return integer
 225    */
 226   static function getMax($daoName, $fieldValues = NULL, $weightField = 'weight') {
 227     $selectField = "MAX(ROUND($weightField)) AS max_weight";
 228     $weightDAO = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField);
 229     $weightDAO->fetch();
 230     if ($weightDAO->max_weight) {
 231       return $weightDAO->max_weight;
 232     }
 233     return 0;
 234   }
 235
 236   /**
 237    * returns the default weight ( highest weight + 1 ) to be used.
 238    *
 239    * @param string $daoName full name of the DAO
 240    * @param array  $fieldValues field => value to be used in the WHERE
 241    * @param string $weightField field which contains the weight value,
 242    * defaults to 'weight'
 243    *
 244    * @return integer
 245    */
 246   static function getDefaultWeight($daoName, $fieldValues = NULL, $weightField = 'weight') {
 247     $maxWeight = CRM_Utils_Weight::getMax($daoName, $fieldValues, $weightField);
 248     return $maxWeight + 1;
 249   }
 250
 251   /**
 252    * Execute a weight-related query
 253    *
 254    * @param string $queryType SELECT, UPDATE, DELETE
 255    * @param string $daoName full name of the DAO
 256    * @param array $fieldValues field => value to be used in the WHERE
 257    * @param string $queryData data to be used, dependent on the query type
 258    * @param string $orderBy optional ORDER BY field
 259    *
 260    * @return Object CRM_Core_DAO objet that holds the results of the query
 261    */
 262   static function &query($queryType,
 263       $daoName,
 264       $fieldValues = NULL,
 265       $queryData,
 266       $additionalWhere = NULL,
 267       $orderBy         = NULL,
 268       $groupBy         = NULL
 269     ) {
 270
 271     require_once (str_replace('_', DIRECTORY_SEPARATOR, $daoName) . ".php");
 272
 273     $dao       = new $daoName;
 274     $table     = $dao->getTablename();
 275     $fields    = &$dao->fields();
 276     $fieldlist = array_keys($fields);
 277
 278     $whereConditions = array();
 279     if ($additionalWhere) {
 280       $whereConditions[] = $additionalWhere;
 281     }
 282     $params = array();
 283     $fieldNum = 0;
 284     if (is_array($fieldValues)) {
 285       foreach ($fieldValues as $fieldName => $value) {
 286         if (!in_array($fieldName, $fieldlist)) {
 287           // invalid field specified.  abort.
 288           return FALSE;
 289         }
 290         $fieldNum++;
 291         $whereConditions[] = "$fieldName = %$fieldNum";
 292         $fieldType         = $fields[$fieldName]['type'];
 293         $params[$fieldNum] = array($value, CRM_Utils_Type::typeToString($fieldType));
 294       }
 295     }
 296     $where = implode(' AND ', $whereConditions);
 297
 298     switch ($queryType) {
 299       case 'SELECT':
 300         $query = "SELECT $queryData FROM $table";
 301         if ($where) {
 302           $query .= " WHERE $where";
 303         }
 304         if ($groupBy) {
 305           $query .= " GROUP BY $groupBy";
 306         }
 307         if ($orderBy) {
 308           $query .= " ORDER BY $orderBy";
 309         }
 310         break;
 311
 312       case 'UPDATE':
 313         $query = "UPDATE $table SET $queryData";
 314         if ($where) {
 315           $query .= " WHERE $where";
 316         }
 317         break;
 318
 319       case 'DELETE':
 320         $query = "DELETE FROM $table WHERE $where AND $queryData";
 321         break;
 322
 323       default:
 324         return FALSE;
 325     }
 326
 327     $resultDAO = CRM_Core_DAO::executeQuery($query, $params);
 328     return $resultDAO;
 329   }
 330
 331   static function addOrder(&$rows, $daoName, $idName, $returnURL, $filter = NULL) {
 332     if (empty($rows)) {
 333       return;
 334     }
 335
 336     $ids = array_keys($rows);
 337     $numIDs = count($ids);
 338     array_unshift($ids, 0);
 339     $ids[]   = 0;
 340     $firstID = $ids[1];
 341     $lastID  = $ids[$numIDs];
 342     if ($firstID == $lastID) {
 343       $rows[$firstID]['order'] = NULL;
 344       return;
 345     }
 346     $config    = CRM_Core_Config::singleton();
 347     $imageURL  = $config->userFrameworkResourceURL . 'i/arrow';
 348
 349     $queryParams = array(
 350       'reset' => 1,
 351       'dao' => $daoName,
 352       'idName' => $idName,
 353       'url' => $returnURL,
 354       'filter' => $filter,
 355     );
 356
 357     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 358     $queryParams['_sgn'] = $signer->sign($queryParams);
 359     $baseURL = CRM_Utils_System::url('civicrm/admin/weight', $queryParams);
 360
 361     for ($i = 1; $i <= $numIDs; $i++) {
 362       $id     = $ids[$i];
 363       $prevID = $ids[$i - 1];
 364       $nextID = $ids[$i + 1];
 365
 366       $links = array();
 367       $url = "{$baseURL}&src=$id";
 368
 369       if ($prevID != 0) {
 370         $alt = ts('Move to top');
 371         $links[] = "<a href=\"{$url}&dst={$firstID}&dir=first\"><img src=\"{$imageURL}/first.gif\" title=\"$alt\" alt=\"$alt\" class=\"order-icon\"></a>";
 372
 373         $alt = ts('Move up one row');
 374         $links[] = "<a href=\"{$url}&dst={$prevID}&dir=swap\"><img src=\"{$imageURL}/up.gif\" title=\"$alt\" alt=\"$alt\" class=\"order-icon\"></a>";
 375       }
 376       else {
 377         $links[] = "<img src=\"{$imageURL}/spacer.gif\" class=\"order-icon\">";
 378         $links[] = "<img src=\"{$imageURL}/spacer.gif\" class=\"order-icon\">";
 379       }
 380
 381       if ($nextID != 0) {
 382         $alt = ts('Move down one row');
 383         $links[] = "<a href=\"{$url}&dst={$nextID}&dir=swap\"><img src=\"{$imageURL}/down.gif\" title=\"$alt\" alt=\"$alt\" class=\"order-icon\"></a>";
 384
 385         $alt = ts('Move to bottom');
 386         $links[] = "<a href=\"{$url}&dst={$lastID}&dir=last\"><img src=\"{$imageURL}/last.gif\" title=\"$alt\" alt=\"$alt\" class=\"order-icon\"></a>";
 387       }
 388       else {
 389         $links[] = "<img src=\"{$imageURL}/spacer.gif\" class=\"order-icon\">";
 390         $links[] = "<img src=\"{$imageURL}/spacer.gif\" class=\"order-icon\">";
 391       }
 392       $rows[$id]['weight'] = implode('&nbsp;', $links);
 393     }
 394   }
 395
 396   static function fixOrder() {
 397     $signature = CRM_Utils_Request::retrieve( '_sgn', 'String', CRM_Core_DAO::$_nullObject);
 398     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 399
 400     // Validate $_GET values b/c subsequent code reads $_GET (via CRM_Utils_Request::retrieve)
 401     if (! $signer->validate($signature, $_GET)) {
 402       CRM_Core_Error::fatal('Request signature is invalid');
 403     }
 404
 405     // Note: Ensure this list matches self::$SIGNABLE_FIELDS
 406     $daoName = CRM_Utils_Request::retrieve('dao', 'String', CRM_Core_DAO::$_nullObject);
 407     $id      = CRM_Utils_Request::retrieve('id', 'Integer', CRM_Core_DAO::$_nullObject);
 408     $idName  = CRM_Utils_Request::retrieve('idName', 'String', CRM_Core_DAO::$_nullObject);
 409     $url     = CRM_Utils_Request::retrieve('url', 'String', CRM_Core_DAO::$_nullObject);
 410     $filter  = CRM_Utils_Request::retrieve('filter', 'String', CRM_Core_DAO::$_nullObject);
 411     $src     = CRM_Utils_Request::retrieve('src', 'Integer', CRM_Core_DAO::$_nullObject);
 412     $dst     = CRM_Utils_Request::retrieve('dst', 'Integer', CRM_Core_DAO::$_nullObject);
 413     $dir     = CRM_Utils_Request::retrieve('dir', 'String', CRM_Core_DAO::$_nullObject);
 414     $object   = new $daoName();
 415     $srcWeight = CRM_Core_DAO::getFieldValue($daoName, $src, 'weight', $idName);
 416     $dstWeight = CRM_Core_DAO::getFieldValue($daoName, $dst, 'weight', $idName);
 417     if ($srcWeight == $dstWeight) {
 418       CRM_Utils_System::redirect($url);
 419     }
 420
 421     $tableName = $object->tableName();
 422
 423     $query = "UPDATE $tableName SET weight = %1 WHERE $idName = %2";
 424     $params = array(1 => array($dstWeight, 'Integer'),
 425               2 => array($src, 'Integer'),
 426     );
 427     CRM_Core_DAO::executeQuery($query, $params);
 428
 429     if ($dir == 'swap') {
 430       $params = array(1 => array($srcWeight, 'Integer'),
 431                 2 => array($dst, 'Integer'),
 432       );
 433       CRM_Core_DAO::executeQuery($query, $params);
 434     }
 435     elseif ($dir == 'first') {
 436       // increment the rest by one
 437       $query = "UPDATE $tableName SET weight = weight + 1 WHERE $idName != %1 AND weight < %2";
 438       if ($filter) {
 439         $query .= " AND $filter";
 440       }
 441       $params = array(1 => array($src, 'Integer'),
 442                 2 => array($srcWeight, 'Integer'),
 443       );
 444       CRM_Core_DAO::executeQuery($query, $params);
 445     }
 446     elseif ($dir == 'last') {
 447       // increment the rest by one
 448       $query = "UPDATE $tableName SET weight = weight - 1 WHERE $idName != %1 AND weight > %2";
 449       if ($filter) {
 450         $query .= " AND $filter";
 451       }
 452       $params = array(1 => array($src, 'Integer'),
 453                 2 => array($srcWeight, 'Integer'),
 454       );
 455       CRM_Core_DAO::executeQuery($query, $params);
 456     }
 457
 458     CRM_Utils_System::redirect($url);
 459   }
 460 }
 461