Location: PHPKode > projects > Jethro Pastoral Ministry Manager > jethro-pmm/db_objects/person_query.class.php
<?php
include_once 'include/db_object.class.php';
class Person_Query extends DB_Object
{
	var $_query_fields = Array();
	var $_dummy_family = NULL;
	var $_dummy_person = NULL;

	function Person_Query($id=0)
	{
		if (!empty($GLOBALS['system'])) {
			$GLOBALS['system']->includeDBClass('person');
			$GLOBALS['system']->includeDBClass('family');
			$this->_dummy_person =& new Person();
			foreach ($this->_dummy_person->fields as $i => $v) {
				unset($this->_dummy_person->fields[$i]['readonly']);
			}
			$this->_dummy_family =& new Family();
			foreach ($this->_dummy_family->fields as $i => $v) {
				unset($this->_dummy_family->fields[$i]['readonly']);
			}
			$this->_query_fields = Array();
			foreach ($this->_dummy_person->fields as $i => $v) {
				if ($v['type'] == 'serialise') {
					continue;
				}
				if ($i == 'familyid') continue;
				if (empty($v['label'])) $v['label'] = $this->_dummy_person->getFieldLabel($i);
				$this->_query_fields['p.'.$i] = $v;
			}
			foreach ($this->_dummy_family->fields as $i => $v) {
				if ($v['type'] == 'serialise') {
					continue;
				}
				if (empty($v['label'])) $v['label'] = $this->_dummy_family->getFieldLabel($i);
				if (in_array($i, Array('status', 'created', 'creator'))) {
					$v['label'] = "Family's ".$v['label'];
				}
				$this->_query_fields['f.'.$i] = $v;
			}
		}
		return $this->DB_Object($id);
	}

	function getInitSQL()
	{
		return "
			CREATE TABLE `person_query` (
			  `id` int(11) NOT NULL auto_increment,
			  `name` varchar(255) collate latin1_general_ci NOT NULL default '',
			  `creator` int(11) NOT NULL default '0',
			  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
			  `params` text collate latin1_general_ci NOT NULL,
			  PRIMARY KEY  (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
		";
	}


	function _getFields()
	{
		$default_params = Array(
							'rules'			=> Array('p.status' => Array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', 'contact')),
							'show_fields'	=> Array('p.first_name', 'p.last_name', 'view_link', 'checkbox'),
							'group_by'		=> '',
							'sort_by'		=> 'p.last_name',
							'include_groups'	=> Array(),
							'exclude_groups'	=> Array(),
						  );
		return Array(
			'name'	=> Array(
									'type'		=> 'text',
									'width'		=> 30,
									'maxlength'	=> 128,
									'allow_empty'	=> false,
									'initial_cap'	=> true,
								   ),
			'created'			=> Array(
									'type'			=> 'datetime',
									'readonly'		=> true,
									'show_in_summary'	=> false,
								   ),
			'creator'			=> Array(
									'type'			=> 'reference',
									'editable'		=> false,
									'references'	=> 'staff_member',
									'show_in_summary'	=> false,
								   ),
			'params'			=> Array(
									'type'			=> 'serialise',
									'editable'		=> false,
									'show_in_summary'	=> false,
									'default'		=> $default_params,

								   ),

		);
	}

	function toString()
	{
		return $this->values['name'];
	}


	function printForm()
	{
		$params = $this->getValue('params');
		?>
		<h3>Find me people...</h3>

		<h4>whose person/family record matches these rules:</h4>
		<table class="standard indent-left">
		<?php
		foreach ($this->_query_fields as $i => $v) {
			if (in_array($v['type'], Array('select', 'reference', 'datetime'))) {
				?>
				<tr>
					<td>
						<input type="checkbox" name="enable_rule[]" value="<?php echo $i; ?>" id="enable_rule_<?php echo $i; ?>" class="select-rule-toggle" <?php if (isset($params['rules'][$i])) echo 'checked="checked" '; ?>/>
						<label for="enable_rule_<?php echo $i; ?>">
							<strong><?php echo $v['label']; ?></strong>
							<?php
							if ($v['type'] == 'datetime') {
								echo 'is between...';
							} else {
								echo 'matches...';
							}
							?>
						</label>
					</td>
					<td>
						<div class="select-rule-options" <?php if (!isset($params['rules'][$i])) echo 'style="display: none" '; ?>>
							<?php
							$key = str_replace('.', '_', $i);
							if ($v['type'] == 'datetime') {
								$value = array_get($params['rules'], $i, Array('from' => '2000-01-01', 'to' => date('Y-m-d')));
								print_widget('params_'.$key.'_from', Array('type' => 'date'), $value['from']);
								echo ' and ';
								print_widget('params_'.$i.'_to', Array('type' => 'date'), $value['to']);
							} else {
								$v['allow_multiple'] = TRUE;
								print_widget('params_'.$key, $v, array_get($params['rules'], $i, Array()));
							}
							?>
						</div>
					</td>
				</tr>
				<?php
			}
		}
		?>
		</table>

		<h4>who <strong>are</strong> in one or more of these groups:</h4>
		<?php
		$this->_printGroupList('include_groupids', array_get($params, 'include_groups', Array()));
		?>


		<h4>who are <strong>not</strong> in any of these groups:</h4>
		<?php
		$this->_printGroupList('exclude_groupids', array_get($params, 'exclude_groups', Array()));
		?>


		<h3>For each person found, show me...</h3>
		<?php
		$show_fields = array_get($params, 'show_fields', Array());
		$show_fields[] = '';
		?>

		<table class="expandable indent-left">
		<?php
		foreach ($show_fields as $chosen_field) {
			?>
			<tr>
				<td>
					<?php
					$options = Array(
								''			=> '',
								'groups'	=> 'Which of the selected groups they are in',
							   );
					foreach ($this->_query_fields as $i => $v) {
						$options[$i] = $v['label'];
					}
					$options['view_link'] = 'A link to view their person record';
					$options['edit_link'] = 'A link to edit their person record';
					$options['checkbox'] = 'A checkbox for bulk actions';
					print_widget('show_fields[]', Array('options' => $options, 'type' => 'select'), $chosen_field);
					?>
				</td>
			</tr>
			<?php
		}
		?>
		</table>


		<h3>Group the results...</h3>
		<?php
		$gb = array_get($params, 'group_by', '');
		?>
		<div class="indent-left">
			<select name="group_by">
				<option value=""<?php if ($gb == '') echo ' selected="selected"'; ?>>all together</option>
				<option value="groupid"<?php if ($gb == 'groupid') echo ' selected="selected"'; ?>>by group membership</option>
			<?php
			foreach ($this->_query_fields as $i => $v) {
				if (!in_array($v['type'], Array('select', 'reference'))) continue;
				?>
				<option value="<?php echo $i; ?>"<?php if ($gb == $i) echo ' selected="selected"'; ?>>by <?php echo $v['label']; ?></option>
				<?php
			}
			?>
			</select>
			<p class="smallprint">Note: Result groups that do not contain any persons will not be shown</p>
		</div>

		<h3>Sort the results by...</h3>

		<select name="sort_by" class="indent-left">
		<?php
		$sb = array_get($params, 'sort_by');
		foreach ($this->_query_fields as $name => $field) {
			?>
			<option value="<?php echo $name; ?>"<?php if ($sb == $name) echo ' selected="selected"'; ?>><?php echo htmlentities($field['label']); ?></option>
			<?php
		}
		?>
		</select>


		<h3>I want to save this report...</h3>
		<div class="indent-left">
			<input type="radio" name="save_option" value="new" id="save_option_new" <?php if (empty($this->id)) echo 'checked="checked"'; ?>>
			<label for="save_option_new">
				as a new query called
			</label>
			<input type="text" name="new_query_name" />
			<br />

			<input type="radio" name="save_option" value="replace" id="save_option_replace" <?php if ($this->id && ($this->id != 'TEMP')) echo 'checked="checked"'; ?>>
			<label for="save_option_replace">
				in place of an existing query
			</label>
			<?php print_widget('replace_query_id', Array('type' => 'reference', 'references' => 'person_query'), $this->id); ?>
			<br />

			<input type="radio" name="save_option" value="temp" id="save_option_temp"<?php if (empty($this->id) || $this->id == 'TEMP') echo ' checked="checked"'; ?>>
			<label for="save_option_temp">only temporarily as an ad-hoc report - I won't need it after today</label>
			<br />
		</div>

		<?php
	}


	function _printGroupList($name, $value)
	{
		?>
		<table class="expandable indent-left">
		<?php
		foreach ($value as $id) {
			?>
			<tr>
				<td>
					<?php
					print_widget($name.'[]', Array('type' => 'reference', 'references' => 'person_group', 'allow_empty' => TRUE, 'empty_text' => '', 'order_by' => 'name'), $id);
					?>
				</td>
			</tr>
			<?php
		}
		?>
			<tr>
				<td>
					<?php
					print_widget($name.'[]', Array('type' => 'reference', 'references' => 'person_group', 'allow_empty' => TRUE, 'empty_text' => '', 'order_by' => 'name'), 0);
					?>
				</td>
			</tr>
		</table>
		<?php
	}

	function processForm()
	{
		switch ($_POST['save_option']) {
			case 'new':
				$this->populate(0, Array());
				$this->setValue('name', $_POST['new_query_name']);
				break;
			case 'replace':
				$this->load((int)$_POST['replace_query_id']);
				break;
			case 'temp':
				$this->id = 'TEMP';
			break;
		}

		$params = $this->getValue('params');

		// FIELD RULES
		$rules = Array();
		if (!empty($_POST['enable_rule'])) {
			foreach ($_POST['enable_rule'] as $field) {
				$rules[$field] = $this->_processRuleDetails($field);
			}
		}
		$params['rules'] = $rules;

		// GROUP RULES
		$params['include_groups'] = $this->_removeEmpties($_POST['include_groupids']);
		$params['exclude_groups'] = $this->_removeEmpties($_POST['exclude_groupids']);

		// SHOW FIELDS
		$params['show_fields'] = $this->_removeEmpties($_POST['show_fields']);

		// GROUP BY
		$params['group_by'] = $_POST['group_by'];

		// SORT BY
		$params['sort_by'] = $_POST['sort_by'];
		$this->setValue('params', $params);
	}

	function _processRuleDetails($field)
	{
		$res = Array();
		switch ($this->_query_fields[$field]['type']) {
			case 'datetime':
				$res['from'] = process_widget('params_'.str_replace('.', '_', $field).'_from', Array('type' => 'date'));
				$res['to'] = process_widget('params_'.str_replace('.', '_', $field).'_to', Array('type' => 'date'));
				break;
			case 'select':
			case 'reference':
				$res = $this->_removeEmpties(array_get($_POST, 'params_'.str_replace('.', '_', $field), Array()));
				break;
		}
		return $res;
	}

	function _removeEmpties($ar)
	{
		$res = Array();
		foreach ($ar as $x) {
			if (($x != '')) {
				$res[] = $x;
			}
		}
		return $res;
	}


	function execute($format='html')
	{

		$db =& $GLOBALS['db'];
		$params = $this->getValue('params');
		$query = Array();
		$query['from'] = 'person p JOIN family f ON p.familyid = f.id';
		$query['order_by'] = $params['sort_by'];
		$query['where'] = Array();

		// BASIC FILTERS
		foreach ($params['rules'] as $field => $values) {
			if (isset($values['from'])) {
				if (($this->_query_fields[$field]['type'] == 'datetime') && (strlen($values['from']) == 10)) {
					// we're searching on a datetime field using only date values
					// so extend them to prevent boundary errors
					$values['from'] .= ' 00:00';
					$values['to'] .= ' 23:59';
				}
				$query['where'][] = $field.' BETWEEN '.$db->quote($values['from']).' AND '.$db->quote($values['to']);
			} else {
				switch (count($values)) {
					case 0:
						$query['where'][] = $field.' = 0';
					case 1:
						$query['where'][] = $field.' = '.$db->quote(reset($values));
						break;
					default:
						$quoted_vals = Array();
						foreach ($values as $val) {
							$quoted_vals[] = $db->quote($val);
						}
						$query['where'][] = $field.' IN ('.implode(', ', $quoted_vals).')';
				}
			}
		}

		// GROUP MEMBERSHIP FILTERS
		if (!empty($params['include_groups'])) {
			$quoted_inc_groupids = Array();
			foreach ($params['include_groups'] as $groupid) {
				$quoted_inc_groupids[] = $db->quote($groupid);
			}
			$query['where'][] = 'p.id IN (SELECT personid FROM person_group_membership WHERE groupid IN ('.implode(', ', $quoted_inc_groupids).'))';
		}
		if (!empty($params['exclude_groups'])) {
			$quoted_ex_groupids = Array();
			foreach ($params['exclude_groups'] as $groupid) {
				$quoted_ex_groupids[] = $db->quote($groupid);
			}
			$query['where'][] = 'p.id NOT IN (SELECT personid FROM person_group_membership WHERE groupid IN ('.implode(', ', $quoted_ex_groupids).'))';
		}

		// GROUPING
		if (empty($params['group_by'])) {
			$grouping_field = '';
		} else if ($params['group_by'] == 'groupid') {
			if (!empty($params['include_groups'])) {
				$grouping_field = 'CONCAT(pg.name, '.$db->quote(' (#').', pg.id, '.$db->quote(')').'), ';
				$query['from'] .= ' JOIN person_group_membership pgm ON p.id = pgm.personid
									JOIN person_group pg ON pg.id = pgm.groupid';
				$query['where'][] = 'pg.id IN ('.implode(', ', $quoted_inc_groupids).')';
			} else {
				$grouping_field = '';
			}
			$query['order_by'] = 'pg.name, '.$query['order_by'];
		} else {
			$grouping_field = $params['group_by'].', ';
			if (FALSE !== ($key = array_search($params['group_by'], $params['show_fields']))) {
				unset($params['show_fields'][$key]);
			}
			$query['order_by'] = $grouping_field.$query['order_by'];
		}

		// DISPLAY FIELDS
		foreach ($params['show_fields'] as $field) {
			switch ($field) {
				case 'groups':
					if (($params['group_by'] != 'groupid') && !empty($params['include_groups'])) {
						$query['select'][] = 'GROUP_CONCAT(pg.name ORDER BY pg.name SEPARATOR '.$db->quote('<br />').') as person_groups';
						$query['from'] .= ' LEFT JOIN person_group_membership pgm ON p.id = pgm.personid
											JOIN person_group pg ON pg.id = pgm.groupid';
						$quoted_groupids = Array();
						foreach ($params['include_groups'] as $groupid) {
							$quoted_groupids[] = $db->quote($groupid);
						}
						$query['where'][] = 'pgm.groupid IN ('.implode(', ', $quoted_groupids).')';
						$query['group_by'][] = 'p.id';
					}
					break;
				case 'view_link':
				case 'edit_link':
				case 'checkbox':
					$query['select'][] = 'p.id as '.$field;
					break;
				default:
					$query['select'][] = $field.' as '.$db->quote($field);
			}
		}

		// Build SQL
		$sql = 'SELECT '.$grouping_field.'p.id as ID, '.implode(', ', $query['select']).'
				FROM '.$query['from'].'
				';
		if (!empty($query['where'])) {
			$sql .= 'WHERE
					('.implode(")\n\tAND (", $query['where']).')
				';
		}
		if (!empty($query['group_by'])) {
			$sql .= 'GROUP BY '.implode(', ', $query['group_by']).'
			';
		}
		$sql .= 'ORDER BY '.$query['order_by'];

		if ($format == 'html' && in_array('checkbox', $params['show_fields'])) {
			echo '<form method="post" class="bulk-person-action">';
		}

		if (empty($grouping_field)) {
			$res = $db->queryAll($sql, null, null, true);
			check_db_result($res);
			$this->_printResultSet($res, $format);
		} else {
			$res = $db->queryAll($sql, null, null, true, false, true);
			check_db_result($res);
			$this->_printResultGroups($res, $params, $format);
		}

		if (($format == 'html') && in_array('checkbox', $params['show_fields'])) {
			echo '<div class="no-print">';
			include 'templates/bulk_actions.template.php';
			echo '</div>';
			echo '</form>';
		}
	}

	function _printResultGroups($res, $params, $format)
	{
		foreach ($res as $i => $v) {
			if ($format == 'html') {
				echo '<h3>';
				if ($params['group_by'] != 'groupid') {
						$var = $params['group_by'][0] == 'p' ? '_dummy_person' : '_dummy_family';
						$fieldname = substr($params['group_by'], 2);
						$this->$var->setValue($fieldname, $i);
						$this->$var->printFieldValue($fieldname);
				} else {
						echo $i;
				}
				echo '</h3>';
			}
			$this->_printResultSet($v, $format);
		}
	}


	function _printResultSet($x, $format)
	{
		if (empty($x)) return;
		if ($format == 'csv') {
			$this->_printResultSetCsv($x);
		} else {
			$this->_printResultSetHtml($x);
		}
	}

	function _printResultSetCsv($x)
	{
		foreach (array_keys(reset($x)) as $heading) {
	              echo '"';
                      switch($heading) {
                          case 'person_groups':
                               echo 'Groups';
                               break;
                          case 'edit_link':
                          case 'checkbox':
                               break;
                          default:
                               if (isset($this->_query_fields[$heading])) {
                                    echo $this->_query_fields[$heading]['label'];
                               } else {
                                    echo $heading;
                               }
                       }
		       echo '",';
                 }
		echo "\r\n";
		foreach ($x as $row) {
			foreach ($row as $label => $val) {
				echo '"';
	                       if (isset($this->_query_fields[$label])) {
	                            $var = $label[0] == 'p' ? '_dummy_person' : '_dummy_family';
	                            $fieldname = substr($label, 2);
	                            $this->$var->setValue($fieldname, $val);
	                            echo str_replace('"', '\\"', $this->$var->getFormattedValue($fieldname));
	                       } else {
	                            echo str_replace('"', '\\"', $val);
	                       }
				echo '",';
			}
			echo "\r\n";
		}
	}

	function _printResultSetHtml($x)
	{
		?>
		<table class="standard">
			<thead>
				<tr>
				<?php
				foreach (array_keys(reset($x)) as $heading) {
					?>
					<th<?php echo $this->_getColClasses($heading); ?>>
						<?php
						switch($heading) {
							case 'person_groups':
								echo 'Groups';
								break;
							case 'edit_link':
							case 'view_link':
								break;
							case 'checkbox':
								echo 'Select<br /><input type="checkbox" class="select-all" title="Select all" />';
								break;
							default:
								if (isset($this->_query_fields[$heading])) {
									echo $this->_query_fields[$heading]['label'];
								} else {
									echo $heading;
								}
						}
						?>
					</th>
					<?php
				}
				?>
				</tr>
			</thead>
			<tbody>
			<?php
			foreach ($x as $row) {
				?>
				<tr>
				<?php
				foreach ($row as $label => $val) {
					?>
					<td<?php echo $this->_getColClasses($label); ?>>
						<?php
						switch ($label) {
							case 'edit_link':
								?>
								<a class="med-popup no-print" href="?view=_edit_person&personid=<?php echo $row[$label]; ?>">Edit</a>
								<?php
								break;
							case 'view_link':
								?>
								<a class="med-popup no-print" href="?view=persons&personid=<?php echo $row[$label]; ?>">View</a>
								<?php
								break;
							case 'checkbox':
								?>
								<input name="personid[]" type="checkbox" value="<?php echo $row[$label]; ?>" class="no-print" />
								<?php
								break;
							default:
								if (isset($this->_query_fields[$label])) {
									$var = $label[0] == 'p' ? '_dummy_person' : '_dummy_family';
									$fieldname = substr($label, 2);
									$this->$var->setValue($fieldname, $val);
									$this->$var->printFieldValue($fieldname);
								} else {
									echo $val;
								}
						}
						?>
					</td>
					<?php
				}
				?>
				</tr>
				<?php
			}
			?>
			</tbody>
		</table>
		<p><strong><?php echo count($x); ?> persons listed</strong></p>
		<?php
	}


	function validateFields()
	{
		if (!parent::validateFields()) return FALSE;

		return TRUE;
	}


	function save()
	{
		if ($this->id == 'TEMP') {
			$_SESSION['saved_query'] = serialize($this);
			return TRUE;
		} else {
			return parent::save();
		}
	}

	function load($id)
	{
		if ($id == 'TEMP') {
			if (!empty($_SESSION['saved_query'])) {
				$x = unserialize($_SESSION['saved_query']);
				$this->populate($x->id, $x->values);
			}
			return TRUE;
		} else {
			return parent::load($id);
		}
	}

	function _getColClasses($heading)
	{
		$class_list = '';
		if (in_array($heading, Array('edit_link', 'view_link', 'checkbox'))) {
			$class_list[] = 'no-print';
		}
		if ($heading == 'checkbox') {
			$class_list[] = 'selector';
		}
		$classes = empty($class_list) ? '' : ' class="'.implode(' ', $class_list).'"';
		return $classes;
	}


}
?>
Return current item: Jethro Pastoral Ministry Manager