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