CRM/Utils/Weight.php

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