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 = self::getMax($daoName, $fieldValues, $weightField) ?: 1;
 130
 131     if ($newWeight > $maxWeight) {
 132       // calculate new weight, CRM-4133
 133       $calNewWeight = CRM_Utils_Weight::getNewWeight($daoName, $fieldValues, $weightField);
 134
 135       // no need to update weight for other fields.
 136       if ($calNewWeight > $maxWeight) {
 137         return $calNewWeight;
 138       }
 139       $newWeight = $maxWeight;
 140
 141       if (!$oldWeight) {
 142         return $newWeight + 1;
 143       }
 144     }
 145     elseif ($newWeight < 1) {
 146       $newWeight = 1;
 147     }
 148
 149     // if they're the same, nothing to do
 150     if ($oldWeight == $newWeight) {
 151       return $newWeight;
 152     }
 153
 154     // Check for an existing record with this weight
 155     $existing = self::query('SELECT', $daoName, $fieldValues, "id", "$weightField = $newWeight");
 156     // Nothing to do if no existing record has this weight
 157     if (empty($existing->N)) {
 158       return $newWeight;
 159     }
 160
 161     // if oldWeight not present, indicates new weight is to be added. So create a gap for a new row to be inserted.
 162     if (!$oldWeight) {
 163       $additionalWhere = "$weightField >= $newWeight";
 164       $update = "$weightField = ($weightField + 1)";
 165       CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
 166     }
 167     else {
 168       if ($newWeight > $oldWeight) {
 169         $additionalWhere = "$weightField > $oldWeight AND $weightField <= $newWeight";
 170         $update = "$weightField = ($weightField - 1)";
 171       }
 172       elseif ($newWeight < $oldWeight) {
 173         $additionalWhere = "$weightField >= $newWeight AND $weightField < $oldWeight";
 174         $update = "$weightField = ($weightField + 1)";
 175       }
 176       CRM_Utils_Weight::query('UPDATE', $daoName, $fieldValues, $update, $additionalWhere);
 177     }
 178     return $newWeight;
 179   }
 180
 181   /**
 182    * Returns the new calculated weight.
 183    *
 184    * @param string $daoName
 185    *   Full name of the DAO.
 186    * @param array $fieldValues
 187    *   Field => value to be used in the WHERE.
 188    * @param string $weightField
 189    *   Field which used to get the wt, default to 'weight'.
 190    *
 191    * @return int
 192    */
 193   public static function getNewWeight($daoName, $fieldValues = NULL, $weightField = 'weight') {
 194     $selectField = "id AS fieldID, $weightField AS weight";
 195     $field = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField);
 196     $sameWeightCount = 0;
 197     $weights = [];
 198     while ($field->fetch()) {
 199       if (in_array($field->weight, $weights)) {
 200         $sameWeightCount++;
 201       }
 202       $weights[$field->fieldID] = $field->weight;
 203     }
 204
 205     $newWeight = 1;
 206     if ($sameWeightCount) {
 207       $newWeight = max($weights) + 1;
 208
 209       // check for max wt, should not greater than cal max wt.
 210       $calMaxWt = min($weights) + count($weights) - 1;
 211       if ($newWeight > $calMaxWt) {
 212         $newWeight = $calMaxWt;
 213       }
 214     }
 215     elseif (!empty($weights)) {
 216       $newWeight = max($weights);
 217     }
 218
 219     return $newWeight;
 220   }
 221
 222   /**
 223    * Returns the highest weight.
 224    *
 225    * @param string $daoName
 226    *   Full name of the DAO.
 227    * @param array $fieldValues
 228    *   Field => value to be used in the WHERE.
 229    * @param string $weightField
 230    *   Field which contains the weight value.
 231    *   defaults to 'weight'
 232    *
 233    * @return int
 234    */
 235   public static function getMax($daoName, $fieldValues = NULL, $weightField = 'weight') {
 236     $selectField = "MAX(ROUND($weightField)) AS max_weight";
 237     $weightDAO = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField);
 238     $weightDAO->fetch();
 239     if ($weightDAO->max_weight) {
 240       return $weightDAO->max_weight;
 241     }
 242     return 0;
 243   }
 244
 245   /**
 246    * Returns the default weight ( highest weight + 1 ) to be used.
 247    *
 248    * @param string $daoName
 249    *   Full name of the DAO.
 250    * @param array $fieldValues
 251    *   Field => value to be used in the WHERE.
 252    * @param string $weightField
 253    *   Field which contains the weight value.
 254    *   defaults to 'weight'
 255    *
 256    * @return int
 257    */
 258   public static function getDefaultWeight($daoName, $fieldValues = NULL, $weightField = 'weight') {
 259     $maxWeight = CRM_Utils_Weight::getMax($daoName, $fieldValues, $weightField);
 260     return $maxWeight + 1;
 261   }
 262
 263   /**
 264    * Execute a weight-related query
 265    *
 266    * @param string $queryType
 267    *   SELECT, UPDATE, DELETE.
 268    * @param CRM_Core_DAO|string $daoName
 269    *   Full name of the DAO.
 270    * @param array $fieldValues
 271    *   Field => value to be used in the WHERE.
 272    * @param string $queryData
 273    *   Data to be used, dependent on the query type.
 274    * @param string|null $additionalWhere
 275    *   Optional WHERE field.
 276    * @param string|null $orderBy
 277    *   Optional ORDER BY field.
 278    * @param string|null $groupBy
 279    *   Optional GROU{} BY field.
 280    *
 281    * @return CRM_Core_DAO
 282    *   objet that holds the results of the query
 283    */
 284   public static function &query(
 285     $queryType,
 286     $daoName,
 287     $fieldValues,
 288     $queryData,
 289     $additionalWhere = NULL,
 290     $orderBy = NULL,
 291     $groupBy = NULL
 292   ) {
 293     $table = $daoName::getTablename();
 294     $fields = $daoName::getSupportedFields();
 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           throw new CRM_Core_Exception("Invalid field '$fieldName' for $daoName");
 308         }
 309         if (CRM_Utils_System::isNull($value)) {
 310           $whereConditions[] = "$fieldName IS NULL";
 311         }
 312         else {
 313           $fieldNum++;
 314           $whereConditions[] = "$fieldName = %$fieldNum";
 315           $fieldType = $fields[$fieldName]['type'];
 316           $params[$fieldNum] = [$value, CRM_Utils_Type::typeToString($fieldType)];
 317         }
 318       }
 319     }
 320     $where = implode(' AND ', $whereConditions);
 321
 322     switch ($queryType) {
 323       case 'SELECT':
 324         $query = "SELECT $queryData FROM $table";
 325         if ($where) {
 326           $query .= " WHERE $where";
 327         }
 328         if ($groupBy) {
 329           $query .= " GROUP BY $groupBy";
 330         }
 331         if ($orderBy) {
 332           $query .= " ORDER BY $orderBy";
 333         }
 334         break;
 335
 336       case 'UPDATE':
 337         $query = "UPDATE $table SET $queryData";
 338         if ($where) {
 339           $query .= " WHERE $where";
 340         }
 341         break;
 342
 343       case 'DELETE':
 344         $query = "DELETE FROM $table WHERE $where AND $queryData";
 345         break;
 346
 347       default:
 348         throw new CRM_Core_Exception("Invalid query operation for $daoName");
 349     }
 350
 351     $resultDAO = CRM_Core_DAO::executeQuery($query, $params);
 352     return $resultDAO;
 353   }
 354
 355   /**
 356    * @param array $rows
 357    * @param string $daoName
 358    * @param string $idName
 359    * @param string $returnURL
 360    * @param string|null $filter
 361    */
 362   public static function addOrder(&$rows, $daoName, $idName, $returnURL, $filter = NULL) {
 363     if (empty($rows)) {
 364       return;
 365     }
 366
 367     $ids = array_keys($rows);
 368     $numIDs = count($ids);
 369     array_unshift($ids, 0);
 370     $ids[] = 0;
 371     $firstID = $ids[1];
 372     $lastID = $ids[$numIDs];
 373     if ($firstID == $lastID) {
 374       $rows[$firstID]['order'] = NULL;
 375       return;
 376     }
 377     $config = CRM_Core_Config::singleton();
 378     $imageURL = $config->userFrameworkResourceURL . 'i/arrow';
 379
 380     $queryParams = [
 381       'reset' => 1,
 382       'dao' => $daoName,
 383       'idName' => $idName,
 384       'url' => $returnURL,
 385       'filter' => $filter,
 386     ];
 387
 388     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 389     $queryParams['_sgn'] = $signer->sign($queryParams);
 390     $baseURL = CRM_Utils_System::url('civicrm/admin/weight', $queryParams);
 391
 392     for ($i = 1; $i <= $numIDs; $i++) {
 393       $id = $ids[$i];
 394       $prevID = $ids[$i - 1];
 395       $nextID = $ids[$i + 1];
 396
 397       $links = [];
 398       $url = "{$baseURL}&amp;src=$id";
 399
 400       if ($prevID != 0) {
 401         $alt = ts('Move to top');
 402         $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>";
 403
 404         $alt = ts('Move up one row');
 405         $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>";
 406       }
 407       else {
 408         $links[] = "<span class=\"order-icon\"></span>";
 409         $links[] = "<span class=\"order-icon\"></span>";
 410       }
 411
 412       if ($nextID != 0) {
 413         $alt = ts('Move down one row');
 414         $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>";
 415
 416         $alt = ts('Move to bottom');
 417         $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>";
 418       }
 419       else {
 420         $links[] = "<span class=\"order-icon\"></span>";
 421         $links[] = "<span class=\"order-icon\"></span>";
 422       }
 423       $rows[$id]['weight'] = implode('&nbsp;', $links);
 424     }
 425   }
 426
 427   /**
 428    *
 429    * @throws CRM_Core_Exception
 430    */
 431   public static function fixOrder() {
 432     $signature = CRM_Utils_Request::retrieve('_sgn', 'String');
 433     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 434
 435     // Validate $_GET values b/c subsequent code reads $_GET (via CRM_Utils_Request::retrieve)
 436     if (!$signer->validate($signature, $_GET)) {
 437       throw new CRM_Core_Exception('Request signature is invalid');
 438     }
 439
 440     // Note: Ensure this list matches self::$SIGNABLE_FIELDS
 441     $daoName = CRM_Utils_Request::retrieve('dao', 'String');
 442     $id = CRM_Utils_Request::retrieve('id', 'Integer');
 443     $idName = CRM_Utils_Request::retrieve('idName', 'String');
 444     $url = CRM_Utils_Request::retrieve('url', 'String');
 445     $filter = CRM_Utils_Request::retrieve('filter', 'String');
 446     $src = CRM_Utils_Request::retrieve('src', 'Integer');
 447     $dst = CRM_Utils_Request::retrieve('dst', 'Integer');
 448     $dir = CRM_Utils_Request::retrieve('dir', 'String');
 449     $object = new $daoName();
 450     $srcWeight = CRM_Core_DAO::getFieldValue($daoName, $src, 'weight', $idName);
 451     $dstWeight = CRM_Core_DAO::getFieldValue($daoName, $dst, 'weight', $idName);
 452     if ($srcWeight == $dstWeight) {
 453       self::fixOrderOutput($url);
 454     }
 455
 456     $tableName = $object->tableName();
 457
 458     $query = "UPDATE $tableName SET weight = %1 WHERE $idName = %2";
 459     $params = [
 460       1 => [$dstWeight, 'Integer'],
 461       2 => [$src, 'Integer'],
 462     ];
 463     CRM_Core_DAO::executeQuery($query, $params);
 464
 465     if ($dir == 'swap') {
 466       $params = [
 467         1 => [$srcWeight, 'Integer'],
 468         2 => [$dst, 'Integer'],
 469       ];
 470       CRM_Core_DAO::executeQuery($query, $params);
 471     }
 472     elseif ($dir == 'first') {
 473       // increment the rest by one
 474       $query = "UPDATE $tableName SET weight = weight + 1 WHERE $idName != %1 AND weight < %2";
 475       if ($filter) {
 476         $query .= " AND $filter";
 477       }
 478       $params = [
 479         1 => [$src, 'Integer'],
 480         2 => [$srcWeight, 'Integer'],
 481       ];
 482       CRM_Core_DAO::executeQuery($query, $params);
 483     }
 484     elseif ($dir == 'last') {
 485       // increment the rest by one
 486       $query = "UPDATE $tableName SET weight = weight - 1 WHERE $idName != %1 AND weight > %2";
 487       if ($filter) {
 488         $query .= " AND $filter";
 489       }
 490       $params = [
 491         1 => [$src, 'Integer'],
 492         2 => [$srcWeight, 'Integer'],
 493       ];
 494       CRM_Core_DAO::executeQuery($query, $params);
 495     }
 496
 497     self::fixOrderOutput($url);
 498   }
 499
 500   /**
 501    * @param string $url
 502    */
 503   public static function fixOrderOutput($url) {
 504     if (empty($_GET['snippet']) || $_GET['snippet'] !== 'json') {
 505       CRM_Utils_System::redirect($url);
 506     }
 507
 508     CRM_Core_Page_AJAX::returnJsonResponse([
 509       'userContext' => $url,
 510     ]);
 511   }
 512
 513 }