<?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;
}
}
?>