[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  namespace phpbb\groupposition;
  15  
  16  /**
  17  * Teampage group position class
  18  *
  19  * Teampage position is an ascending list 1, 2, ..., n for items which are displayed. 1 is the first item, n the last.
  20  */
  21  class teampage implements \phpbb\groupposition\groupposition_interface
  22  {
  23      /**
  24      * Group is not displayed
  25      */
  26      const GROUP_DISABLED = 0;
  27  
  28      /**
  29      * No parent item
  30      */
  31      const NO_PARENT = 0;
  32  
  33      /**
  34      * Database object
  35      * @var \phpbb\db\driver\driver_interface
  36      */
  37      protected $db;
  38  
  39      /**
  40      * User object
  41      * @var \phpbb\user
  42      */
  43      protected $user;
  44  
  45      /**
  46      * Cache object
  47      * @var \phpbb\cache\driver\driver_interface
  48      */
  49      protected $cache;
  50  
  51      /**
  52      * Constructor
  53      *
  54      * @param \phpbb\db\driver\driver_interface                $db        Database object
  55      * @param \phpbb\user                        $user    User object
  56      * @param \phpbb\cache\driver\driver_interface    $cache    Cache object
  57      */
  58  	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\user $user, \phpbb\cache\driver\driver_interface $cache)
  59      {
  60          $this->db = $db;
  61          $this->user = $user;
  62          $this->cache = $cache;
  63      }
  64  
  65      /**
  66      * Returns the teampage position for a given group, if the group exists.
  67      *
  68      * @param    int        $group_id    group_id of the group to be selected
  69      * @return    int            position of the group
  70      * @throws \phpbb\groupposition\exception
  71      */
  72  	public function get_group_value($group_id)
  73      {
  74          // The join is required to ensure that the group itself exists
  75          $sql = 'SELECT g.group_id, t.teampage_position
  76              FROM ' . GROUPS_TABLE . ' g
  77              LEFT JOIN ' . TEAMPAGE_TABLE . ' t
  78                  ON (t.group_id = g.group_id)
  79              WHERE g.group_id = ' . (int) $group_id;
  80          $result = $this->db->sql_query($sql);
  81          $row = $this->db->sql_fetchrow($result);
  82          $this->db->sql_freeresult($result);
  83  
  84          if ($row === false)
  85          {
  86              // Group not found.
  87              throw new \phpbb\groupposition\exception('NO_GROUP');
  88          }
  89  
  90          return (int) $row['teampage_position'];
  91      }
  92  
  93      /**
  94      * Returns the row for a given group, if the group exists.
  95      *
  96      * @param    int        $group_id    group_id of the group to be selected
  97      * @return    array            Data row of the group
  98      * @throws \phpbb\groupposition\exception
  99      */
 100  	public function get_group_values($group_id)
 101      {
 102          // The join is required to ensure that the group itself exists
 103          $sql = 'SELECT *
 104              FROM ' . GROUPS_TABLE . ' g
 105              LEFT JOIN ' . TEAMPAGE_TABLE . ' t
 106                  ON (t.group_id = g.group_id)
 107              WHERE g.group_id = ' . (int) $group_id;
 108          $result = $this->db->sql_query($sql);
 109          $row = $this->db->sql_fetchrow($result);
 110          $this->db->sql_freeresult($result);
 111  
 112          if ($row === false)
 113          {
 114              // Group not found.
 115              throw new \phpbb\groupposition\exception('NO_GROUP');
 116          }
 117  
 118          return $row;
 119      }
 120  
 121      /**
 122      * Returns the teampage position for a given teampage item, if the item exists.
 123      *
 124      * @param    int        $teampage_id    Teampage_id of the selected item
 125      * @return    int            Teampage position of the item
 126      * @throws \phpbb\groupposition\exception
 127      */
 128  	public function get_teampage_value($teampage_id)
 129      {
 130          $sql = 'SELECT teampage_position
 131              FROM ' . TEAMPAGE_TABLE . '
 132              WHERE teampage_id = ' . (int) $teampage_id;
 133          $result = $this->db->sql_query($sql);
 134          $current_value = $this->db->sql_fetchfield('teampage_position');
 135          $this->db->sql_freeresult($result);
 136  
 137          if ($current_value === false)
 138          {
 139              // Group not found.
 140              throw new \phpbb\groupposition\exception('NO_GROUP');
 141          }
 142  
 143          return (int) $current_value;
 144      }
 145  
 146      /**
 147      * Returns the teampage row for a given teampage item, if the item exists.
 148      *
 149      * @param    int        $teampage_id    Teampage_id of the selected item
 150      * @return    array            Teampage row of the item
 151      * @throws \phpbb\groupposition\exception
 152      */
 153  	public function get_teampage_values($teampage_id)
 154      {
 155          $sql = 'SELECT teampage_position, teampage_parent
 156              FROM ' . TEAMPAGE_TABLE . '
 157              WHERE teampage_id = ' . (int) $teampage_id;
 158          $result = $this->db->sql_query($sql);
 159          $row = $this->db->sql_fetchrow($result);
 160          $this->db->sql_freeresult($result);
 161  
 162          if ($row === false)
 163          {
 164              // Group not found.
 165              throw new \phpbb\groupposition\exception('NO_GROUP');
 166          }
 167  
 168          return $row;
 169      }
 170  
 171  
 172      /**
 173      * {@inheritDoc}
 174      */
 175  	public function get_group_count()
 176      {
 177          $sql = 'SELECT teampage_position
 178              FROM ' . TEAMPAGE_TABLE . '
 179              ORDER BY teampage_position DESC';
 180          $result = $this->db->sql_query_limit($sql, 1);
 181          $group_count = (int) $this->db->sql_fetchfield('teampage_position');
 182          $this->db->sql_freeresult($result);
 183  
 184          return $group_count;
 185      }
 186  
 187      /**
 188      * {@inheritDoc}
 189      */
 190  	public function add_group($group_id)
 191      {
 192          return $this->add_group_teampage($group_id, self::NO_PARENT);
 193      }
 194  
 195      /**
 196      * Adds a group by group_id
 197      *
 198      * @param    int        $group_id    group_id of the group to be added
 199      * @param    int        $parent_id    Teampage ID of the parent item
 200      * @return    bool        True if the group was added successfully
 201      */
 202  	public function add_group_teampage($group_id, $parent_id)
 203      {
 204          $current_value = $this->get_group_value($group_id);
 205  
 206          if ($current_value == self::GROUP_DISABLED)
 207          {
 208              if ($parent_id != self::NO_PARENT)
 209              {
 210                  // Check, whether the given parent is a category
 211                  $sql = 'SELECT teampage_id
 212                      FROM ' . TEAMPAGE_TABLE . '
 213                      WHERE group_id = 0
 214                          AND teampage_id = ' . (int) $parent_id;
 215                  $result = $this->db->sql_query_limit($sql, 1);
 216                  $parent_is_category = (bool) $this->db->sql_fetchfield('teampage_id');
 217                  $this->db->sql_freeresult($result);
 218  
 219                  if ($parent_is_category)
 220                  {
 221                      // Get value of last child from this parent and add group there
 222                      $sql = 'SELECT teampage_position
 223                          FROM ' . TEAMPAGE_TABLE . '
 224                          WHERE teampage_parent = ' . (int) $parent_id . '
 225                              OR teampage_id = ' . (int) $parent_id . '
 226                          ORDER BY teampage_position DESC';
 227                      $result = $this->db->sql_query_limit($sql, 1);
 228                      $new_position = (int) $this->db->sql_fetchfield('teampage_position');
 229                      $this->db->sql_freeresult($result);
 230  
 231                      $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 232                          SET teampage_position = teampage_position + 1
 233                          WHERE teampage_position > ' . $new_position;
 234                      $this->db->sql_query($sql);
 235                  }
 236              }
 237              else
 238              {
 239                  // Add group at the end
 240                  $new_position = $this->get_group_count();
 241              }
 242  
 243              $sql_ary = array(
 244                  'group_id'            => $group_id,
 245                  'teampage_position'    => $new_position + 1,
 246                  'teampage_parent'    => $parent_id,
 247              );
 248  
 249              $sql = 'INSERT INTO ' . TEAMPAGE_TABLE . ' ' . $this->db->sql_build_array('INSERT', $sql_ary);
 250              $this->db->sql_query($sql);
 251  
 252              $this->cache->destroy('sql', TEAMPAGE_TABLE);
 253              return true;
 254          }
 255  
 256          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 257          return false;
 258      }
 259  
 260      /**
 261      * Adds a new category
 262      *
 263      * @param    string    $category_name    Name of the category to be added
 264      * @return    bool        True if the category was added successfully
 265      */
 266  	public function add_category_teampage($category_name)
 267      {
 268          if ($category_name === '')
 269          {
 270              return false;
 271          }
 272  
 273          $num_entries = $this->get_group_count();
 274  
 275          $sql_ary = array(
 276              'group_id'            => 0,
 277              'teampage_position'    => $num_entries + 1,
 278              'teampage_parent'    => 0,
 279              'teampage_name'        => truncate_string($category_name, 255, 255),
 280          );
 281  
 282          $sql = 'INSERT INTO ' . TEAMPAGE_TABLE . ' ' . $this->db->sql_build_array('INSERT', $sql_ary);
 283          $this->db->sql_query($sql);
 284  
 285          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 286          return true;
 287      }
 288  
 289      /**
 290      * Deletes a group from the list and closes the gap in the position list.
 291      *
 292      * @param    int        $group_id        group_id of the group to be deleted
 293      * @param    bool    $skip_group        Skip setting the value for this group, to save the query, when you need to update it anyway.
 294      * @return    bool        True if the group was deleted successfully
 295      */
 296  	public function delete_group($group_id, $skip_group = false)
 297      {
 298          $current_value = $this->get_group_value($group_id);
 299  
 300          if ($current_value != self::GROUP_DISABLED)
 301          {
 302              $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 303                  SET teampage_position = teampage_position - 1
 304                  WHERE teampage_position > ' . $current_value;
 305              $this->db->sql_query($sql);
 306  
 307              $sql = 'DELETE FROM ' . TEAMPAGE_TABLE . '
 308                  WHERE group_id = ' . $group_id;
 309              $this->db->sql_query($sql);
 310  
 311              $this->cache->destroy('sql', TEAMPAGE_TABLE);
 312              return true;
 313          }
 314  
 315          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 316          return false;
 317      }
 318  
 319      /**
 320      * Deletes an item from the list and closes the gap in the position list.
 321      *
 322      * @param    int        $teampage_id    teampage_id of the item to be deleted
 323      * @param    bool    $skip_group        Skip setting the group to GROUP_DISABLED, to save the query, when you need to update it anyway.
 324      * @return    bool        True if the item was deleted successfully
 325      */
 326  	public function delete_teampage($teampage_id, $skip_group = false)
 327      {
 328          $current_value = $this->get_teampage_value($teampage_id);
 329  
 330          if ($current_value != self::GROUP_DISABLED)
 331          {
 332              $sql = 'DELETE FROM ' . TEAMPAGE_TABLE . '
 333                  WHERE teampage_id = ' . $teampage_id . '
 334                      OR teampage_parent = ' . $teampage_id;
 335              $this->db->sql_query($sql);
 336  
 337              $delta = (int) $this->db->sql_affectedrows();
 338  
 339              $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 340                  SET teampage_position = teampage_position - ' . $delta . '
 341                  WHERE teampage_position > ' . $current_value;
 342              $this->db->sql_query($sql);
 343  
 344              $this->cache->destroy('sql', TEAMPAGE_TABLE);
 345              return true;
 346          }
 347  
 348          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 349          return false;
 350      }
 351  
 352      /**
 353      * {@inheritDoc}
 354      */
 355  	public function move_up($group_id)
 356      {
 357          return $this->move($group_id, 1);
 358      }
 359  
 360      /**
 361      * Moves an item up by teampage_id
 362      *
 363      * @param    int        $teampage_id    teampage_id of the item to be move
 364      * @return    bool        True if the group was moved successfully
 365      */
 366  	public function move_up_teampage($teampage_id)
 367      {
 368          return $this->move_teampage($teampage_id, 1);
 369      }
 370  
 371      /**
 372      * {@inheritDoc}
 373      */
 374  	public function move_down($group_id)
 375      {
 376          return $this->move($group_id, -1);
 377      }
 378  
 379      /**
 380      * Moves an item down by teampage_id
 381      *
 382      * @param    int        $teampage_id    teampage_id of the item to be moved
 383      * @return    bool        True if the group was moved successfully
 384      */
 385  	public function move_down_teampage($teampage_id)
 386      {
 387          return $this->move_teampage($teampage_id, -1);
 388      }
 389  
 390      /**
 391      * {@inheritDoc}
 392      */
 393  	public function move($group_id, $delta)
 394      {
 395          $delta = (int) $delta;
 396          if (!$delta)
 397          {
 398              return false;
 399          }
 400  
 401          $move_up = ($delta > 0) ? true : false;
 402          $data = $this->get_group_values($group_id);
 403  
 404          $current_value = (int) $data['teampage_position'];
 405          if ($current_value != self::GROUP_DISABLED)
 406          {
 407              $this->db->sql_transaction('begin');
 408  
 409              if (!$move_up && $data['teampage_parent'] == self::NO_PARENT)
 410              {
 411                  // If we move items down, we need to grab the one sibling more,
 412                  // so we do not ignore the children of the previous sibling.
 413                  // We will remove the additional sibling later on.
 414                  $delta = abs($delta) + 1;
 415              }
 416  
 417              $sql = 'SELECT teampage_position
 418                  FROM ' . TEAMPAGE_TABLE . '
 419                  WHERE teampage_parent = ' . (int) $data['teampage_parent'] . '
 420                      AND teampage_position' . (($move_up) ? ' < ' : ' > ') . $current_value . '
 421                  ORDER BY teampage_position' . (($move_up) ? ' DESC' : ' ASC');
 422              $result = $this->db->sql_query_limit($sql, $delta);
 423  
 424              $sibling_count = 0;
 425              $sibling_limit = $delta;
 426  
 427              // Reset the delta, as we recalculate the new real delta
 428              $delta = 0;
 429              while ($row = $this->db->sql_fetchrow($result))
 430              {
 431                  $sibling_count++;
 432                  $delta = $current_value - $row['teampage_position'];
 433  
 434                  if (!$move_up && $data['teampage_parent'] == self::NO_PARENT && $sibling_count == $sibling_limit)
 435                  {
 436                      // Remove the additional sibling we added previously
 437                      $delta++;
 438                  }
 439              }
 440              $this->db->sql_freeresult($result);
 441  
 442              if ($delta)
 443              {
 444                  // First we move all items between our current value and the target value up/down 1,
 445                  // so we have a gap for our item to move.
 446                  $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 447                      SET teampage_position = teampage_position' . (($move_up) ? ' + 1' : ' - 1') . '
 448                      WHERE teampage_position' . (($move_up) ? ' >= ' : ' <= ') . ($current_value - $delta) . '
 449                          AND teampage_position' . (($move_up) ? ' < ' : ' > ') . $current_value;
 450                  $this->db->sql_query($sql);
 451  
 452                  // And now finally, when we moved some other items and built a gap,
 453                  // we can move the desired item to it.
 454                  $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 455                      SET teampage_position = teampage_position ' . (($move_up) ? ' - ' : ' + ') . abs($delta) . '
 456                      WHERE group_id = ' . (int) $group_id;
 457                  $this->db->sql_query($sql);
 458  
 459                  $this->db->sql_transaction('commit');
 460                  $this->cache->destroy('sql', TEAMPAGE_TABLE);
 461  
 462                  return true;
 463              }
 464  
 465              $this->db->sql_transaction('commit');
 466          }
 467  
 468          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 469          return false;
 470      }
 471  
 472      /**
 473      * Moves an item up/down
 474      *
 475      * @param    int        $teampage_id    teampage_id of the item to be moved
 476      * @param    int        $delta        number of steps:
 477      *                                - positive = move up
 478      *                                - negative = move down
 479      * @return    bool        True if the group was moved successfully
 480      */
 481  	public function move_teampage($teampage_id, $delta)
 482      {
 483          $delta = (int) $delta;
 484          if (!$delta)
 485          {
 486              return false;
 487          }
 488  
 489          $move_up = ($delta > 0) ? true : false;
 490          $data = $this->get_teampage_values($teampage_id);
 491  
 492          $current_value = (int) $data['teampage_position'];
 493          if ($current_value != self::GROUP_DISABLED)
 494          {
 495              $this->db->sql_transaction('begin');
 496  
 497              if (!$move_up && $data['teampage_parent'] == self::NO_PARENT)
 498              {
 499                  // If we move items down, we need to grab the one sibling more,
 500                  // so we do not ignore the children of the previous sibling.
 501                  // We will remove the additional sibling later on.
 502                  $delta = abs($delta) + 1;
 503              }
 504  
 505              $sql = 'SELECT teampage_id, teampage_position
 506                  FROM ' . TEAMPAGE_TABLE . '
 507                  WHERE teampage_parent = ' . (int) $data['teampage_parent'] . '
 508                      AND teampage_position' . (($move_up) ? ' < ' : ' > ') . $current_value . '
 509                  ORDER BY teampage_position' . (($move_up) ? ' DESC' : ' ASC');
 510              $result = $this->db->sql_query_limit($sql, $delta);
 511  
 512              $sibling_count = 0;
 513              $sibling_limit = $delta;
 514  
 515              // Reset the delta, as we recalculate the new real delta
 516              $delta = 0;
 517              while ($row = $this->db->sql_fetchrow($result))
 518              {
 519                  $sibling_count++;
 520                  $delta = $current_value - $row['teampage_position'];
 521  
 522                  // Remove the additional sibling we added previously
 523                  // But only, if we included it, this is not be the case
 524                  // when we reached the end of our list
 525                  if (!$move_up && $data['teampage_parent'] == self::NO_PARENT && $sibling_count == $sibling_limit)
 526                  {
 527                      $delta++;
 528                  }
 529              }
 530              $this->db->sql_freeresult($result);
 531  
 532              if ($delta)
 533              {
 534                  $sql = 'SELECT COUNT(teampage_id) as num_items
 535                      FROM ' . TEAMPAGE_TABLE . '
 536                      WHERE teampage_id = ' . (int) $teampage_id . '
 537                          OR teampage_parent = ' . (int) $teampage_id;
 538                  $result = $this->db->sql_query($sql);
 539                  $num_items = (int) $this->db->sql_fetchfield('num_items');
 540                  $this->db->sql_freeresult($result);
 541  
 542                  // First we move all items between our current value and the target value up/down 1,
 543                  // so we have a gap for our item to move.
 544                  $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 545                      SET teampage_position = teampage_position' . (($move_up) ? ' + ' : ' - ') . $num_items . '
 546                      WHERE teampage_position' . (($move_up) ? ' >= ' : ' <= ') . ($current_value - $delta) . '
 547                          AND teampage_position' . (($move_up) ? ' < ' : ' > ') . $current_value . '
 548                          AND NOT (teampage_id = ' . (int) $teampage_id . '
 549                              OR teampage_parent = ' . (int) $teampage_id . ')';
 550                  $this->db->sql_query($sql);
 551  
 552                  $delta = (!$move_up && $data['teampage_parent'] == self::NO_PARENT) ? (abs($delta) - ($num_items - 1)) : abs($delta);
 553  
 554                  // And now finally, when we moved some other items and built a gap,
 555                  // we can move the desired item to it.
 556                  $sql = 'UPDATE ' . TEAMPAGE_TABLE . '
 557                      SET teampage_position = teampage_position ' . (($move_up) ? ' - ' : ' + ') . $delta . '
 558                      WHERE teampage_id = ' . (int) $teampage_id . '
 559                          OR teampage_parent = ' . (int) $teampage_id;
 560                  $this->db->sql_query($sql);
 561  
 562                  $this->db->sql_transaction('commit');
 563                  $this->cache->destroy('sql', TEAMPAGE_TABLE);
 564  
 565                  return true;
 566              }
 567  
 568              $this->db->sql_transaction('commit');
 569          }
 570  
 571          $this->cache->destroy('sql', TEAMPAGE_TABLE);
 572          return false;
 573      }
 574  
 575      /**
 576      * Get group type language var
 577      *
 578      * @param    int        $group_type    group_type from the groups-table
 579      * @return    string        name of the language variable for the given group-type.
 580      */
 581  	static public function group_type_language($group_type)
 582      {
 583          switch ($group_type)
 584          {
 585              case GROUP_OPEN:
 586                  return 'GROUP_REQUEST';
 587              case GROUP_CLOSED:
 588                  return 'GROUP_CLOSED';
 589              case GROUP_HIDDEN:
 590                  return 'GROUP_HIDDEN';
 591              case GROUP_SPECIAL:
 592                  return 'GROUP_SPECIAL';
 593              case GROUP_FREE:
 594                  return 'GROUP_OPEN';
 595          }
 596      }
 597  }