Group::get_groups() - Code Metrics - ghalusa/PHP-Skeleton-App - Measure and Improve Code Quality continuously with Scrutinizer

Group::get_groups() B
last analyzed 2017-10-22 20:34 UTC

↳ Parent: Group

Complexity

Conditions	6
Paths	3

Size

Total Lines	29
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	6
eloc	17
nc	3
nop	1
dl	0
loc	29
rs	8.439
c	0
b	0
f	0

<?php
/**
 * The PHP Skeleton App
 *
 * @author      Goran Halusa <[email protected]>
 * @copyright   2015 Goran Halusa
 * @link        https://github.com/ghalusa/PHP-Skeleton-App
 * @license     https://github.com/ghalusa/PHP-Skeleton-App/wiki/License
 * @version     0.1.1
 * @package     PHP Skeleton App
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

/**
 * Group
 *
 * Class for the Group module, providing methods for browsing and managing groups.
 *
 * @author      Goran Halusa <[email protected]>
 * @since       0.1.0
 */

namespace PHPSkeleton;
use PDO;

class Group
{
  /**
   * @var string|bool  $session_key    The session key
   */
  private $session_key = false;

  /**
   * @var object  $db   The database connection object
   */
  public $db;

  /**
   * Constructor
   * @param object   $db_connection   The database connection object
   * @param string   $session_key     The session key
   */
  public function __construct($db_connection = false, $session_key = false)
  {
      if ($db_connection && is_object($db_connection)) {
          $this->db = $db_connection;
      }
      $this->session_key = $session_key;
  }

  /**
   * Browse Groups
   *
   * Run a query to retreive all groups in the database.
   *
   * @param   string  $sort_field     The data value
   * @param   string  $sort_order     The data value
   * @param   int  $start_record      The data value
   * @param   int  $stop_record       The data value
   * @param   string  $search         The data value
   * @return  array|bool              The query result
   */
  public function browse_groups(
    $sort_field = false, $sort_order = 'DESC', $start_record = 0, $stop_record = 20, $search = false
  ) {
      $sort = "";
      $search_sql = "";
      $pdo_params = array();
      $data = array();

      $limit_sql = " LIMIT {$start_record}, {$stop_record} ";

      if ($sort_field) {
''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
          switch ($sort_field) {
        case 'last_modified':
          $sort = " ORDER BY group.last_modified {$sort_order} ";
          break;
        default:
          $sort = " ORDER BY {$sort_field} {$sort_order} ";
        }
      }

      if ($search) {
''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
          $pdo_params[] = '%'.$search.'%';
          $pdo_params[] = '%'.$search.'%';
          $pdo_params[] = '%'.$search.'%';
          $search_sql = "
        AND (
          `group`.name LIKE ?
          OR `group`.abbreviation LIKE ?
          OR `group`.description LIKE ?
        ) ";
      }

      $statement = $this->db->prepare("
      SELECT SQL_CALC_FOUND_ROWS
        `group`.group_id AS manage
        ,`group`.group_id
        ,`group`.name
        ,`group`.abbreviation
        ,`group`.description
        ,`group`.address_1 AS address
        ,`group`.city
        ,DATE_FORMAT(`group`.last_modified,'%m/%d/%Y') AS last_modified
        ,`group`.group_id AS DT_RowId
      FROM `group`
      WHERE `group`.active = 1
      {$search_sql}
      GROUP BY `group`.group_id
      HAVING 1=1
      {$sort}
      {$limit_sql}");
      $statement->execute($pdo_params);
      $data["aaData"] = $statement->fetchAll(PDO::FETCH_ASSOC);

      $statement = $this->db->prepare("SELECT FOUND_ROWS()");
      $statement->execute();
      $count = $statement->fetch(PDO::FETCH_ASSOC);
      $data["iTotalRecords"] = $count["FOUND_ROWS()"];
      $data["iTotalDisplayRecords"] = $count["FOUND_ROWS()"];
      return $data;
  }

  /**
   * Delete Group
   *
   * Run a query to remove a group from the database.
   *
   * @param       int $group_id      The data value
   * @return      void
   */
  public function delete_group($group_id)
  {
      $statement = $this->db->prepare("
      UPDATE `group`
      SET active = 0
      WHERE group_id = :group_id");
      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
      $statement->execute();
  }

  /**
   * Flatten Group Hierarchy
   *
   * Flatten the group array, returning a single level array.
   *
   * @param       object $group_hierarchy    The multidimensional array
   * @return      array                      The single level array
   */
  public function flatten_group_hierarchy($group_hierarchy)
  {
      $single_level_array = array();
      foreach ($group_hierarchy as $single_node) {
          $descendants = false;
          if (isset($single_node["descendants"]) && $single_node["descendants"]) {
              $descendants = $single_node["descendants"];
              unset($single_node["descendants"]);
          }
          $single_level_array[] = $single_node;
          if ($descendants) {
              $single_level_array = array_merge($single_level_array, $this->flatten_group_hierarchy($descendants));
          }
      }
      return $single_level_array;
  }

  /**
   * Get Descendants
   *
   * Query the database for a group's descendants.
   *
   * @param       object $groups             The array
   * @param       int $level                 The level
   * @param       string $indent_char        The character used to indent in the resulting list
   * @return      array|bool                 The single level array
   */
  public function get_descendants(&$groups, $level = 0, $indent_char = "-")
  {
      $level += 1;
      $indent_string = "";
      for ($i=1;$i<=$level;$i++) {
          $indent_string .= $indent_char;
      }
      foreach ($groups as &$single_group) {
          $statement = $this->db->prepare("
        SELECT descendant AS group_id
          ,name
          ,abbreviation
          ,'{$indent_string}' AS indent
        FROM group_closure_table
        LEFT JOIN `group` ON `group`.group_id = group_closure_table.descendant
        WHERE ancestor = :group_id
        AND ancestor != descendant
        AND pathlength = 1
        GROUP BY descendant
        ORDER BY name ASC");
          $statement->bindValue(":group_id", $single_group["group_id"], PDO::PARAM_INT);
          $statement->execute();
          $descendants = $statement->fetchAll(PDO::FETCH_ASSOC);

          if ($descendants) {
              $single_group["descendants"] = $descendants;
              $this->get_descendants($single_group["descendants"], $level, $indent_char);
          }
      }
  }

  /**
   * Get Group Hierarchy
   *
   * Query the database for a group's hierarchy.
   *
   * @param       string $indent_char        The character used to indent in the resulting list
   * @return      array|bool                 The query result
   */
  public function get_group_hierarchy($indent_char = "-")
  {
      // Get the root nodes.
    $statement = $this->db->prepare("
      SELECT descendant AS group_id
        ,COUNT(ancestor) AS total_parents
        ,name
        ,abbreviation
      FROM group_closure_table
      LEFT JOIN `group` ON `group`.group_id = group_closure_table.descendant
      WHERE active = 1
      GROUP BY descendant
      HAVING total_parents = 1
      ORDER BY name ASC");
      $statement->execute();
      $root_nodes = $statement->fetchAll(PDO::FETCH_ASSOC);
      $this->get_descendants($root_nodes, 0, $indent_char);
      return $root_nodes;
  }

  /**
   * Get Group Record
   *
   * Query the database for one group and its parent group
   *
   * @param       int $group_id        The data value
   * @return      array|bool           The guery result
   */
  public function get_group_record($group_id)
  {
      $statement = $this->db->prepare("
      SELECT *
      FROM `group`
      WHERE active = 1
      AND group_id = :group_id");
      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
      $statement->execute();
      $data = $statement->fetch(PDO::FETCH_ASSOC);

    // Get the parent group.
    $statement = $this->db->prepare("
      SELECT ancestor
      FROM group_closure_table
      WHERE descendant = :group_id
      AND pathlength = 1");
      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
      $statement->execute();
      $parent_group = $statement->fetch(PDO::FETCH_ASSOC);
      $data["group_parent"] = $parent_group["ancestor"];

      return $data;
  }

  /**
   * Get Groups
   *
   * Query the database for one group and its parent group
   *
   * @param       array|int $group_ids    The data value
   * @return      array|bool              The query result
   */
  public function get_groups($group_ids = false)
  {
      $pdo_params = array(
      1 //active
    );
      $group_sql = "";
      if ($group_ids && is_array($group_ids)) {
          $question_marks = array();
          foreach ($group_ids as $single_group_id) {
              $pdo_params[] = $single_group_id;
              $question_marks[] = "?";
          }
          $group_sql = " AND group_id IN (" . implode(",", $question_marks) . ") ";
      } elseif ($group_ids && is_numeric($group_ids)) {
          $pdo_params[] = $group_ids;
          $group_sql = " AND group_id = ? ";
      }

      $statement = $this->db->prepare("
      SELECT group_id
        ,abbreviation
        ,name
      FROM `group`
      WHERE active = ?
      {$group_sql}
      ORDER BY name");
      $statement->execute($pdo_params);
      return $statement->fetchAll(PDO::FETCH_ASSOC);
  }

  /**
   * Insert/Update Group
   *
   * Run a queries to insert and update groups in the database.
   *
   * @param       array $data       The data array
   * @param       array $group_id   The data value
   * @return      array|bool        The group id
   */
  public function insert_update_group($data, $group_id = false)
  {
      $pdo_params = array(
        $data["name"]
        ,$data["abbreviation"]
        ,$data["description"]
        ,$data["address_1"]
        ,$data["address_2"]
        ,$data["city"]
        ,$data["state"]
        ,$data["zip"]
        ,$_SESSION[$this->session_key]["user_account_id"]
        ,1
      );

      if ($group_id) {
          $pdo_params[] = $group_id;
          $statement = $this->db->prepare("
          UPDATE `group`
          SET name = ?
            ,abbreviation = ?
            ,description = ?
            ,address_1 = ?
            ,address_2 = ?
            ,city = ?
            ,state = ?
            ,zip = ?
            ,last_modified_user_account_id = ?
            ,active = ?
            ,last_modified = NOW()
          WHERE group_id = ?");
          $statement->execute($pdo_params);
      } else {
          $pdo_params[] = $_SESSION[$this->session_key]["user_account_id"];
          $statement = $this->db->prepare("
          INSERT INTO `group`
          (name
          ,abbreviation
          ,description
          ,address_1
          ,address_2
          ,city
          ,state
          ,zip
          ,last_modified_user_account_id
          ,active
          ,created_by_user_account_id
          ,last_modified
          ,date_created)
          VALUES
          (?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())");
          $statement->execute($pdo_params);
          $group_id = $this->db->lastInsertId();
      }

      // Update the groups closure table per Bill Karwin's SQL Antipatterns, Chapter 3.
      // The pathlengh column refers to the jumps in between the ancestor and descendant -
      // self-reference = 0, first child = 1, and so forth...

      // First, check to see if we need to update or insert records.
      $group_parent = (isset($data["group_parent"]) && $data["group_parent"]) ? $data["group_parent"] : false;
      $statement = $this->db->prepare("
        SELECT *
        FROM group_closure_table
        WHERE descendant = :group_id");
      $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
      $statement->execute();
      $closure_check = $statement->fetchAll(PDO::FETCH_ASSOC);

      if ($closure_check) {
          // We need to move everything under it as well.
        // First, detatch the node subtree...
        $statement = $this->db->prepare("
          DELETE FROM group_closure_table
          WHERE descendant IN (
            SELECT tmpdescendant.d FROM (
              SELECT descendant AS d FROM group_closure_table WHERE ancestor = :group_id
            ) AS tmpdescendant
          )
          AND ancestor IN (
          SELECT tmpancestor.a FROM (
            SELECT ancestor AS a FROM group_closure_table WHERE descendant = :group_id2 AND ancestor != descendant
          ) AS tmpancestor
        )");
          $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
          $statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
          $statement->execute();

        // Now, attach the subtree under the updated group.
        $statement = $this->db->prepare("
          INSERT INTO group_closure_table
            (ancestor, descendant, pathlength)
          SELECT supertree.ancestor, subtree.descendant, subtree.pathlength+1
          FROM group_closure_table AS supertree
          CROSS JOIN group_closure_table AS subtree
          WHERE supertree.descendant = :new_parent
          AND subtree.ancestor = :group_id");
          $statement->bindValue(":new_parent", $group_parent, PDO::PARAM_INT);
          $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
          $statement->execute();
      } else {
          // Just insert the leaf node.
        $statement = $this->db->prepare("
          INSERT INTO group_closure_table
            (ancestor, descendant, pathlength)
          SELECT gct.ancestor, :group_id, pathlength+1
          FROM group_closure_table AS gct
          WHERE gct.descendant = :parent_group
          UNION ALL
          SELECT :group_id2, :group_id3,0");
          $statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
          $statement->bindValue(":parent_group", $group_parent, PDO::PARAM_INT);
          $statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
          $statement->bindValue(":group_id3", $group_id, PDO::PARAM_INT);
          $statement->execute();
      }

      return $group_id;
  }

  /**
   * Get Admin Info From Group List
   *
   * Query for all of the admins and editors for the supplied groups,
   * as well as all of the admin/editors for their parent groups.
   *
   * @param       array $group_list    The data array
   * @return      array|bool           The group id
   */
  public function get_admin_info_from_group_list($group_list)
  {
      $statement = $this->db->prepare("
      SELECT user_account.user_account_email
          ,user_account.first_name
          ,user_account.last_name
      FROM user_account
      LEFT JOIN user_account_groups ON user_account_groups.user_account_id = user_account.user_account_id
      LEFT JOIN group_closure_table ON group_closure_table.ancestor = user_account_groups.group_id
      WHERE user_account_groups.role_id IN (4,1)
      AND (user_account_groups.group_id IN($group_list)
        OR group_closure_table.descendant IN (" . $group_list . "))
      GROUP BY user_account.user_account_email");
      $statement->execute();
      return $statement->fetchAll(PDO::FETCH_ASSOC);
  }
}


1		<?php
2		/**
3		* The PHP Skeleton App
4		*
5		* @author Goran Halusa <[email protected]>
6		* @copyright 2015 Goran Halusa
7		* @link https://github.com/ghalusa/PHP-Skeleton-App
8		* @license https://github.com/ghalusa/PHP-Skeleton-App/wiki/License
9		* @version 0.1.1
10		* @package PHP Skeleton App
11		*
12		* For the full copyright and license information, please view the LICENSE
13		* file that was distributed with this source code.
14		*/
15
16		/**
17		* Group
18		*
19		* Class for the Group module, providing methods for browsing and managing groups.
20		*
21		* @author Goran Halusa <[email protected]>
22		* @since 0.1.0
23		*/
24
25		namespace PHPSkeleton;
26		use PDO;
27
28		class Group
29		{
30		/**
31		* @var string\|bool $session_key The session key
32		*/
33		private $session_key = false;
34
35		/**
36		* @var object $db The database connection object
37		*/
38		public $db;
39
40		/**
41		* Constructor
42		* @param object $db_connection The database connection object
43		* @param string $session_key The session key
44		*/
45	View Code Duplication	public function __construct($db_connection = false, $session_key = false)
46		{
47		if ($db_connection && is_object($db_connection)) {
48		$this->db = $db_connection;
49		}
50		$this->session_key = $session_key;
51		}
52
53		/**
54		* Browse Groups
55		*
56		* Run a query to retreive all groups in the database.
57		*
58		* @param string $sort_field The data value
59		* @param string $sort_order The data value
60		* @param int $start_record The data value
61		* @param int $stop_record The data value
62		* @param string $search The data value
63		* @return array\|bool The query result
64		*/
65		public function browse_groups(
66		$sort_field = false, $sort_order = 'DESC', $start_record = 0, $stop_record = 20, $search = false
67		) {
68		$sort = "";
69		$search_sql = "";
70		$pdo_params = array();
71		$data = array();
72
73		$limit_sql = " LIMIT {$start_record}, {$stop_record} ";
74
75	View Code Duplication	if ($sort_field) {
		0 ignored issues – show Bug Best Practice introduced 2017-09-10 15:54 UTC by Report Bug Copy Issue Report The expression `$sort_field` of type `false\|string` is loosely compared to `true`; this is ambiguous if the string can be empty. You might want to explicitly use `!== false` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
76		switch ($sort_field) {
77		case 'last_modified':
78		$sort = " ORDER BY group.last_modified {$sort_order} ";
79		break;
80		default:
81		$sort = " ORDER BY {$sort_field} {$sort_order} ";
82		}
83		}
84
85		if ($search) {
		0 ignored issues – show Bug Best Practice introduced 2017-09-10 15:54 UTC by Report Bug Copy Issue Report The expression `$search` of type `false\|string` is loosely compared to `true`; this is ambiguous if the string can be empty. You might want to explicitly use `!== false` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
86		$pdo_params[] = '%'.$search.'%';
87		$pdo_params[] = '%'.$search.'%';
88		$pdo_params[] = '%'.$search.'%';
89		$search_sql = "
90		AND (
91		`group`.name LIKE ?
92		OR `group`.abbreviation LIKE ?
93		OR `group`.description LIKE ?
94		) ";
95		}
96
97		$statement = $this->db->prepare("
98		SELECT SQL_CALC_FOUND_ROWS
99		`group`.group_id AS manage
100		,`group`.group_id
101		,`group`.name
102		,`group`.abbreviation
103		,`group`.description
104		,`group`.address_1 AS address
105		,`group`.city
106		,DATE_FORMAT(`group`.last_modified,'%m/%d/%Y') AS last_modified
107		,`group`.group_id AS DT_RowId
108		FROM `group`
109		WHERE `group`.active = 1
110		{$search_sql}
111		GROUP BY `group`.group_id
112		HAVING 1=1
113		{$sort}
114		{$limit_sql}");
115		$statement->execute($pdo_params);
116		$data["aaData"] = $statement->fetchAll(PDO::FETCH_ASSOC);
117
118		$statement = $this->db->prepare("SELECT FOUND_ROWS()");
119		$statement->execute();
120		$count = $statement->fetch(PDO::FETCH_ASSOC);
121		$data["iTotalRecords"] = $count["FOUND_ROWS()"];
122		$data["iTotalDisplayRecords"] = $count["FOUND_ROWS()"];
123		return $data;
124		}
125
126		/**
127		* Delete Group
128		*
129		* Run a query to remove a group from the database.
130		*
131		* @param int $group_id The data value
132		* @return void
133		*/
134		public function delete_group($group_id)
135		{
136		$statement = $this->db->prepare("
137		UPDATE `group`
138		SET active = 0
139		WHERE group_id = :group_id");
140		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
141		$statement->execute();
142		}
143
144		/**
145		* Flatten Group Hierarchy
146		*
147		* Flatten the group array, returning a single level array.
148		*
149		* @param object $group_hierarchy The multidimensional array
150		* @return array The single level array
151		*/
152		public function flatten_group_hierarchy($group_hierarchy)
153		{
154		$single_level_array = array();
155		foreach ($group_hierarchy as $single_node) {
156		$descendants = false;
157		if (isset($single_node["descendants"]) && $single_node["descendants"]) {
158		$descendants = $single_node["descendants"];
159		unset($single_node["descendants"]);
160		}
161		$single_level_array[] = $single_node;
162		if ($descendants) {
163		$single_level_array = array_merge($single_level_array, $this->flatten_group_hierarchy($descendants));
164		}
165		}
166		return $single_level_array;
167		}
168
169		/**
170		* Get Descendants
171		*
172		* Query the database for a group's descendants.
173		*
174		* @param object $groups The array
175		* @param int $level The level
176		* @param string $indent_char The character used to indent in the resulting list
177		* @return array\|bool The single level array
178		*/
179		public function get_descendants(&$groups, $level = 0, $indent_char = "-")
180		{
181		$level += 1;
182		$indent_string = "";
183		for ($i=1;$i<=$level;$i++) {
184		$indent_string .= $indent_char;
185		}
186		foreach ($groups as &$single_group) {
187		$statement = $this->db->prepare("
188		SELECT descendant AS group_id
189		,name
190		,abbreviation
191		,'{$indent_string}' AS indent
192		FROM group_closure_table
193		LEFT JOIN `group` ON `group`.group_id = group_closure_table.descendant
194		WHERE ancestor = :group_id
195		AND ancestor != descendant
196		AND pathlength = 1
197		GROUP BY descendant
198		ORDER BY name ASC");
199		$statement->bindValue(":group_id", $single_group["group_id"], PDO::PARAM_INT);
200		$statement->execute();
201		$descendants = $statement->fetchAll(PDO::FETCH_ASSOC);
202
203		if ($descendants) {
204		$single_group["descendants"] = $descendants;
205		$this->get_descendants($single_group["descendants"], $level, $indent_char);
206		}
207		}
208		}
209
210		/**
211		* Get Group Hierarchy
212		*
213		* Query the database for a group's hierarchy.
214		*
215		* @param string $indent_char The character used to indent in the resulting list
216		* @return array\|bool The query result
217		*/
218		public function get_group_hierarchy($indent_char = "-")
219		{
220		// Get the root nodes.
221		$statement = $this->db->prepare("
222		SELECT descendant AS group_id
223		,COUNT(ancestor) AS total_parents
224		,name
225		,abbreviation
226		FROM group_closure_table
227		LEFT JOIN `group` ON `group`.group_id = group_closure_table.descendant
228		WHERE active = 1
229		GROUP BY descendant
230		HAVING total_parents = 1
231		ORDER BY name ASC");
232		$statement->execute();
233		$root_nodes = $statement->fetchAll(PDO::FETCH_ASSOC);
234		$this->get_descendants($root_nodes, 0, $indent_char);
235		return $root_nodes;
236		}
237
238		/**
239		* Get Group Record
240		*
241		* Query the database for one group and its parent group
242		*
243		* @param int $group_id The data value
244		* @return array\|bool The guery result
245		*/
246		public function get_group_record($group_id)
247		{
248		$statement = $this->db->prepare("
249		SELECT *
250		FROM `group`
251		WHERE active = 1
252		AND group_id = :group_id");
253		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
254		$statement->execute();
255		$data = $statement->fetch(PDO::FETCH_ASSOC);
256
257		// Get the parent group.
258		$statement = $this->db->prepare("
259		SELECT ancestor
260		FROM group_closure_table
261		WHERE descendant = :group_id
262		AND pathlength = 1");
263		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
264		$statement->execute();
265		$parent_group = $statement->fetch(PDO::FETCH_ASSOC);
266		$data["group_parent"] = $parent_group["ancestor"];
267
268		return $data;
269		}
270
271		/**
272		* Get Groups
273		*
274		* Query the database for one group and its parent group
275		*
276		* @param array\|int $group_ids The data value
277		* @return array\|bool The query result
278		*/
279		public function get_groups($group_ids = false)
280		{
281		$pdo_params = array(
282		1 //active
283		);
284		$group_sql = "";
285		if ($group_ids && is_array($group_ids)) {
286		$question_marks = array();
287		foreach ($group_ids as $single_group_id) {
288		$pdo_params[] = $single_group_id;
289		$question_marks[] = "?";
290		}
291		$group_sql = " AND group_id IN (" . implode(",", $question_marks) . ") ";
292		} elseif ($group_ids && is_numeric($group_ids)) {
293		$pdo_params[] = $group_ids;
294		$group_sql = " AND group_id = ? ";
295		}
296
297		$statement = $this->db->prepare("
298		SELECT group_id
299		,abbreviation
300		,name
301		FROM `group`
302		WHERE active = ?
303		{$group_sql}
304		ORDER BY name");
305		$statement->execute($pdo_params);
306		return $statement->fetchAll(PDO::FETCH_ASSOC);
307		}
308
309		/**
310		* Insert/Update Group
311		*
312		* Run a queries to insert and update groups in the database.
313		*
314		* @param array $data The data array
315		* @param array $group_id The data value
316		* @return array\|bool The group id
317		*/
318		public function insert_update_group($data, $group_id = false)
319		{
320		$pdo_params = array(
321		$data["name"]
322		,$data["abbreviation"]
323		,$data["description"]
324		,$data["address_1"]
325		,$data["address_2"]
326		,$data["city"]
327		,$data["state"]
328		,$data["zip"]
329		,$_SESSION[$this->session_key]["user_account_id"]
330		,1
331		);
332
333		if ($group_id) {
334		$pdo_params[] = $group_id;
335		$statement = $this->db->prepare("
336		UPDATE `group`
337		SET name = ?
338		,abbreviation = ?
339		,description = ?
340		,address_1 = ?
341		,address_2 = ?
342		,city = ?
343		,state = ?
344		,zip = ?
345		,last_modified_user_account_id = ?
346		,active = ?
347		,last_modified = NOW()
348		WHERE group_id = ?");
349		$statement->execute($pdo_params);
350		} else {
351		$pdo_params[] = $_SESSION[$this->session_key]["user_account_id"];
352		$statement = $this->db->prepare("
353		INSERT INTO `group`
354		(name
355		,abbreviation
356		,description
357		,address_1
358		,address_2
359		,city
360		,state
361		,zip
362		,last_modified_user_account_id
363		,active
364		,created_by_user_account_id
365		,last_modified
366		,date_created)
367		VALUES
368		(?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())");
369		$statement->execute($pdo_params);
370		$group_id = $this->db->lastInsertId();
371		}
372
373		// Update the groups closure table per Bill Karwin's SQL Antipatterns, Chapter 3.
374		// The pathlengh column refers to the jumps in between the ancestor and descendant -
375		// self-reference = 0, first child = 1, and so forth...
376
377		// First, check to see if we need to update or insert records.
378		$group_parent = (isset($data["group_parent"]) && $data["group_parent"]) ? $data["group_parent"] : false;
379		$statement = $this->db->prepare("
380		SELECT *
381		FROM group_closure_table
382		WHERE descendant = :group_id");
383		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
384		$statement->execute();
385		$closure_check = $statement->fetchAll(PDO::FETCH_ASSOC);
386
387		if ($closure_check) {
388		// We need to move everything under it as well.
389		// First, detatch the node subtree...
390		$statement = $this->db->prepare("
391		DELETE FROM group_closure_table
392		WHERE descendant IN (
393		SELECT tmpdescendant.d FROM (
394		SELECT descendant AS d FROM group_closure_table WHERE ancestor = :group_id
395		) AS tmpdescendant
396		)
397		AND ancestor IN (
398		SELECT tmpancestor.a FROM (
399		SELECT ancestor AS a FROM group_closure_table WHERE descendant = :group_id2 AND ancestor != descendant
400		) AS tmpancestor
401		)");
402		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
403		$statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
404		$statement->execute();
405
406		// Now, attach the subtree under the updated group.
407		$statement = $this->db->prepare("
408		INSERT INTO group_closure_table
409		(ancestor, descendant, pathlength)
410		SELECT supertree.ancestor, subtree.descendant, subtree.pathlength+1
411		FROM group_closure_table AS supertree
412		CROSS JOIN group_closure_table AS subtree
413		WHERE supertree.descendant = :new_parent
414		AND subtree.ancestor = :group_id");
415		$statement->bindValue(":new_parent", $group_parent, PDO::PARAM_INT);
416		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
417		$statement->execute();
418		} else {
419		// Just insert the leaf node.
420		$statement = $this->db->prepare("
421		INSERT INTO group_closure_table
422		(ancestor, descendant, pathlength)
423		SELECT gct.ancestor, :group_id, pathlength+1
424		FROM group_closure_table AS gct
425		WHERE gct.descendant = :parent_group
426		UNION ALL
427		SELECT :group_id2, :group_id3,0");
428		$statement->bindValue(":group_id", $group_id, PDO::PARAM_INT);
429		$statement->bindValue(":parent_group", $group_parent, PDO::PARAM_INT);
430		$statement->bindValue(":group_id2", $group_id, PDO::PARAM_INT);
431		$statement->bindValue(":group_id3", $group_id, PDO::PARAM_INT);
432		$statement->execute();
433		}
434
435		return $group_id;
436		}
437
438		/**
439		* Get Admin Info From Group List
440		*
441		* Query for all of the admins and editors for the supplied groups,
442		* as well as all of the admin/editors for their parent groups.
443		*
444		* @param array $group_list The data array
445		* @return array\|bool The group id
446		*/
447		public function get_admin_info_from_group_list($group_list)
448		{
449		$statement = $this->db->prepare("
450		SELECT user_account.user_account_email
451		,user_account.first_name
452		,user_account.last_name
453		FROM user_account
454		LEFT JOIN user_account_groups ON user_account_groups.user_account_id = user_account.user_account_id
455		LEFT JOIN group_closure_table ON group_closure_table.ancestor = user_account_groups.group_id
456		WHERE user_account_groups.role_id IN (4,1)
457		AND (user_account_groups.group_id IN($group_list)
458		OR group_closure_table.descendant IN (" . $group_list . "))
459		GROUP BY user_account.user_account_email");
460		$statement->execute();
461		return $statement->fetchAll(PDO::FETCH_ASSOC);
462		}
463		}
464

ghalusa / PHP-Skeleton-App

Group::get_groups() B last analyzed 2017-10-22 20:34 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

Group::get_groups() B
last analyzed 2017-10-22 20:34 UTC