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 null $additionalWhere
 275    * @param string $orderBy
 276    *   Optional ORDER BY field.
 277    *
 278    * @param null $groupBy
 279    *
 280    * @return CRM_Core_DAO
 281    *   objet that holds the results of the query
 282    */
 283   public static function &query(
 284     $queryType,
 285     $daoName,
 286     $fieldValues,
 287     $queryData,
 288     $additionalWhere = NULL,
 289     $orderBy = NULL,
 290     $groupBy = NULL
 291   ) {
 292     $table = $daoName::getTablename();
 293     $fields = $daoName::getSupportedFields();
 294     $fieldlist = array_keys($fields);
 295
 296     $whereConditions = [];
 297     if ($additionalWhere) {
 298       $whereConditions[] = $additionalWhere;
 299     }
 300     $params = [];
 301     $fieldNum = 0;
 302     if (is_array($fieldValues)) {
 303       foreach ($fieldValues as $fieldName => $value) {
 304         if (!in_array($fieldName, $fieldlist)) {
 305           // invalid field specified.  abort.
 306           throw new CRM_Core_Exception("Invalid field '$fieldName' for $daoName");
 307         }
 308         if (CRM_Utils_System::isNull($value)) {
 309           $whereConditions[] = "$fieldName IS NULL";
 310         }
 311         else {
 312           $fieldNum++;
 313           $whereConditions[] = "$fieldName = %$fieldNum";
 314           $fieldType = $fields[$fieldName]['type'];
 315           $params[$fieldNum] = [$value, CRM_Utils_Type::typeToString($fieldType)];
 316         }
 317       }
 318     }
 319     $where = implode(' AND ', $whereConditions);
 320
 321     switch ($queryType) {
 322       case 'SELECT':
 323         $query = "SELECT $queryData FROM $table";
 324         if ($where) {
 325           $query .= " WHERE $where";
 326         }
 327         if ($groupBy) {
 328           $query .= " GROUP BY $groupBy";
 329         }
 330         if ($orderBy) {
 331           $query .= " ORDER BY $orderBy";
 332         }
 333         break;
 334
 335       case 'UPDATE':
 336         $query = "UPDATE $table SET $queryData";
 337         if ($where) {
 338           $query .= " WHERE $where";
 339         }
 340         break;
 341
 342       case 'DELETE':
 343         $query = "DELETE FROM $table WHERE $where AND $queryData";
 344         break;
 345
 346       default:
 347         throw new CRM_Core_Exception("Invalid query operation for $daoName");
 348     }
 349
 350     $resultDAO = CRM_Core_DAO::executeQuery($query, $params);
 351     return $resultDAO;
 352   }
 353
 354   /**
 355    * @param $rows
 356    * @param string $daoName
 357    * @param string $idName
 358    * @param $returnURL
 359    * @param null $filter
 360    */
 361   public static function addOrder(&$rows, $daoName, $idName, $returnURL, $filter = NULL) {
 362     if (empty($rows)) {
 363       return;
 364     }
 365
 366     $ids = array_keys($rows);
 367     $numIDs = count($ids);
 368     array_unshift($ids, 0);
 369     $ids[] = 0;
 370     $firstID = $ids[1];
 371     $lastID = $ids[$numIDs];
 372     if ($firstID == $lastID) {
 373       $rows[$firstID]['order'] = NULL;
 374       return;
 375     }
 376     $config = CRM_Core_Config::singleton();
 377     $imageURL = $config->userFrameworkResourceURL . 'i/arrow';
 378
 379     $queryParams = [
 380       'reset' => 1,
 381       'dao' => $daoName,
 382       'idName' => $idName,
 383       'url' => $returnURL,
 384       'filter' => $filter,
 385     ];
 386
 387     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 388     $queryParams['_sgn'] = $signer->sign($queryParams);
 389     $baseURL = CRM_Utils_System::url('civicrm/admin/weight', $queryParams);
 390
 391     for ($i = 1; $i <= $numIDs; $i++) {
 392       $id = $ids[$i];
 393       $prevID = $ids[$i - 1];
 394       $nextID = $ids[$i + 1];
 395
 396       $links = [];
 397       $url = "{$baseURL}&amp;src=$id";
 398
 399       if ($prevID != 0) {
 400         $alt = ts('Move to top');
 401         $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>";
 402
 403         $alt = ts('Move up one row');
 404         $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>";
 405       }
 406       else {
 407         $links[] = "<span class=\"order-icon\"></span>";
 408         $links[] = "<span class=\"order-icon\"></span>";
 409       }
 410
 411       if ($nextID != 0) {
 412         $alt = ts('Move down one row');
 413         $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>";
 414
 415         $alt = ts('Move to bottom');
 416         $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>";
 417       }
 418       else {
 419         $links[] = "<span class=\"order-icon\"></span>";
 420         $links[] = "<span class=\"order-icon\"></span>";
 421       }
 422       $rows[$id]['weight'] = implode('&nbsp;', $links);
 423     }
 424   }
 425
 426   /**
 427    *
 428    * @throws CRM_Core_Exception
 429    */
 430   public static function fixOrder() {
 431     $signature = CRM_Utils_Request::retrieve('_sgn', 'String');
 432     $signer = new CRM_Utils_Signer(CRM_Core_Key::privateKey(), self::$SIGNABLE_FIELDS);
 433
 434     // Validate $_GET values b/c subsequent code reads $_GET (via CRM_Utils_Request::retrieve)
 435     if (!$signer->validate($signature, $_GET)) {
 436       throw new CRM_Core_Exception('Request signature is invalid');
 437     }
 438
 439     // Note: Ensure this list matches self::$SIGNABLE_FIELDS
 440     $daoName = CRM_Utils_Request::retrieve('dao', 'String');
 441     $id = CRM_Utils_Request::retrieve('id', 'Integer');
 442     $idName = CRM_Utils_Request::retrieve('idName', 'String');
 443     $url = CRM_Utils_Request::retrieve('url', 'String');
 444     $filter = CRM_Utils_Request::retrieve('filter', 'String');
 445     $src = CRM_Utils_Request::retrieve('src', 'Integer');
 446     $dst = CRM_Utils_Request::retrieve('dst', 'Integer');
 447     $dir = CRM_Utils_Request::retrieve('dir', 'String');
 448     $object = new $daoName();
 449     $srcWeight = CRM_Core_DAO::getFieldValue($daoName, $src, 'weight', $idName);
 450     $dstWeight = CRM_Core_DAO::getFieldValue($daoName, $dst, 'weight', $idName);
 451     if ($srcWeight == $dstWeight) {
 452       self::fixOrderOutput($url);
 453     }
 454
 455     $tableName = $object->tableName();
 456
 457     $query = "UPDATE $tableName SET weight = %1 WHERE $idName = %2";
 458     $params = [
 459       1 => [$dstWeight, 'Integer'],
 460       2 => [$src, 'Integer'],
 461     ];
 462     CRM_Core_DAO::executeQuery($query, $params);
 463
 464     if ($dir == 'swap') {
 465       $params = [
 466         1 => [$srcWeight, 'Integer'],
 467         2 => [$dst, 'Integer'],
 468       ];
 469       CRM_Core_DAO::executeQuery($query, $params);
 470     }
 471     elseif ($dir == 'first') {
 472       // increment the rest by one
 473       $query = "UPDATE $tableName SET weight = weight + 1 WHERE $idName != %1 AND weight < %2";
 474       if ($filter) {
 475         $query .= " AND $filter";
 476       }
 477       $params = [
 478         1 => [$src, 'Integer'],
 479         2 => [$srcWeight, 'Integer'],
 480       ];
 481       CRM_Core_DAO::executeQuery($query, $params);
 482     }
 483     elseif ($dir == 'last') {
 484       // increment the rest by one
 485       $query = "UPDATE $tableName SET weight = weight - 1 WHERE $idName != %1 AND weight > %2";
 486       if ($filter) {
 487         $query .= " AND $filter";
 488       }
 489       $params = [
 490         1 => [$src, 'Integer'],
 491         2 => [$srcWeight, 'Integer'],
 492       ];
 493       CRM_Core_DAO::executeQuery($query, $params);
 494     }
 495
 496     self::fixOrderOutput($url);
 497   }
 498
 499   /**
 500    * @param $url
 501    */
 502   public static function fixOrderOutput($url) {
 503     if (empty($_GET['snippet']) || $_GET['snippet'] !== 'json') {
 504       CRM_Utils_System::redirect($url);
 505     }
 506
 507     CRM_Core_Page_AJAX::returnJsonResponse([
 508       'userContext' => $url,
 509     ]);
 510   }
 511
 512 }