CRM/Utils/Weight.php

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