public function load($id)
{
parent::load($id);
// Load custom values
$SQL = 'SELECT v.fieldid, ' . Custom_Field::getRawValueSQLExpr('v') . ' as value
FROM custom_field_value v
WHERE personid = ' . (int) $this->id;
$res = $GLOBALS['db']->queryAll($SQL, NULL, NULL, true, FALSE, TRUE);
check_db_result($res);
$this->_custom_values = $res;
}
function getSQL($select_fields = NULL)
{
$db =& $GLOBALS['db'];
$params = $this->_convertParams($this->getValue('params'));
if (empty($params)) {
return null;
}
$query = array();
$query['from'] = 'person p
JOIN family f ON p.familyid = f.id
';
$query['where'] = array();
// BASIC FILTERS
foreach ($params['rules'] as $field => $values) {
if ($field == 'date') {
continue;
} else {
if (is_array($values) && isset($values['from'])) {
if ($this->_field_details[$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 {
$values = (array) $values;
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) . ')';
}
}
}
}
// CUSTOM FIELD FILTERS
$customFieldWheres = array();
foreach (array_get($params, 'custom_fields', array()) as $fieldid => $values) {
$query['from'] .= ' LEFT JOIN custom_field_value pd' . $fieldid . ' ON pd' . $fieldid . '.personid = p.id AND pd' . $fieldid . '.fieldid = ' . (int) $fieldid . "\n";
switch ($this->_custom_fields[$fieldid]['type']) {
case 'date':
if ($values['criteria'] == 'between') {
$values['criteria'] = $values['anniversary'] ? 'anniversary' : 'exact';
}
switch ($values['criteria']) {
case 'any':
$customFieldWheres[] = 'pd' . $fieldid . '.`personid` IS NOT NULL';
break;
case 'empty':
$customFieldWheres[] = 'pd' . $fieldid . '.personid IS NULL';
break;
case 'exact':
case 'anniversary':
if (array_get($values, 'periodtype') == 'relative') {
$length = $values['periodlength'];
if (!preg_match('/^[0-9]+$/', $length)) {
$length = 0;
}
$offsets = array('before' => array(-$length - 1, -1), 'ending' => array(-$length, 0), 'starting' => array(0, $length), 'after' => array(1, $length + 1));
list($so, $eo) = $offsets[$values['periodanchor']];
if ($so > 0) {
$so = "+{$so}";
}
if ($eo > 0) {
$eo = "+{$eo}";
}
$from = date('Y-m-d', strtotime("{$so} days"));
$to = date('Y-m-d', strtotime("{$eo} days"));
} else {
$from = $values['from'];
$to = $values['to'];
}
$betweenExp = 'BETWEEN ' . $db->quote($from) . ' AND ' . $db->quote($to);
$valExp = 'pd' . $fieldid . '.value_date';
$w = array();
$w[] = "{$valExp} NOT LIKE '-%' AND {$valExp} {$betweenExp}";
if ($values['criteria'] == 'anniversary') {
$qFromYear = $db->quote(substr($from, 0, 4));
$qToYear = $db->quote(substr($to, 0, 4));
$w[] = "{$valExp} LIKE '-%' AND (\n\t\t\t\t\t\t\t\t\t\t\tCONCAT({$qFromYear}, {$valExp}) {$betweenExp}\n\t\t\t\t\t\t\t\t\t\t\tOR CONCAT({$qToYear}, {$valExp}) {$betweenExp}\n\t\t\t\t\t\t\t\t\t\t)";
$w[] = "{$valExp} NOT LIKE '-%' AND (\n\t\t\t\t\t\t\t\t\t\t\tCONCAT({$qFromYear}, RIGHT({$valExp}, 6)) {$betweenExp}\n\t\t\t\t\t\t\t\t\t\t\tOR CONCAT({$qToYear}, RIGHT({$valExp}, 6)) {$betweenExp}\n\t\t\t\t\t\t\t\t\t\t)";
}
$customFieldWheres[] = '((' . implode("\n) OR (\n", $w) . '))';
break;
}
break;
case 'select':
switch (array_get($values, 'criteria', 'contains')) {
case 'contains':
$ids = implode(',', array_map(array($db, 'quote'), $values['val']));
$customFieldWheres[] = '(pd' . $fieldid . '.value_optionid IN (' . $ids . '))';
break;
case 'any':
$customFieldWheres[] = '(pd' . $fieldid . '.value_optionid IS NOT NULL)';
break;
case 'empty':
$customFieldWheres[] = '(pd' . $fieldid . '.value_optionid IS NULL)';
break;
}
break;
case 'text':
switch (array_get($values, 'criteria', 'equals')) {
case 'equal':
$customFieldWheres[] = '(pd' . $fieldid . '.value_text = ' . $db->quote($values['val']) . ')';
break;
case 'any':
$customFieldWheres[] = '(pd' . $fieldid . '.value_text IS NOT NULL)';
break;
case 'empty':
$customFieldWheres[] = '(pd' . $fieldid . '.value_text IS NULL)';
break;
}
break;
break;
}
}
if (!empty($customFieldWheres)) {
$logic = array_get($params, 'custom_field_logic', 'AND');
$query['where'][] = '((' . implode(') ' . $logic . ' (', $customFieldWheres) . '))';
}
// GROUP MEMBERSHIP FILTERS
if (!empty($params['include_groups'])) {
$include_groupids_clause = $this->_getGroupAndCategoryRestrictionSQL($params['include_groups'], array_get($params, 'group_join_date_from'), array_get($params, 'group_join_date_to'), array_get($params, 'group_membership_status'));
$group_members_sql = 'SELECT personid
FROM person_group_membership pgm
JOIN person_group pg ON pgm.groupid = pg.id
WHERE (' . $include_groupids_clause . ')';
$query['where'][] = 'p.id IN (' . $group_members_sql . ')';
}
if (!empty($params['exclude_groups'])) {
$exclude_groupids_clause = $this->_getGroupAndCategoryRestrictionSQL($params['exclude_groups']);
$query['where'][] = 'p.id NOT IN (
SELECT personid
FROM person_group_membership pgm
JOIN person_group pg ON pgm.groupid = pg.id
WHERE (' . $exclude_groupids_clause . ')
)';
}
//NOTE FILTERS
if (!empty($params['note_phrase'])) {
$note_sql = 'SELECT pn.personid, GROUP_CONCAT(an.Subject) as subjects
FROM person_note pn
JOIN abstract_note an ON an.id = pn.id
WHERE an.details LIKE ' . $GLOBALS['db']->quote('%' . $params['note_phrase'] . '%') . '
OR an.subject LIKE ' . $GLOBALS['db']->quote('%' . $params['note_phrase'] . '%') . '
GROUP BY pn.personid';
$query['from'] .= ' JOIN (' . $note_sql . ') notes ON notes.personid = p.id ';
}
// ATTENDANCE FILTERS
if (!empty($params['attendance_groupid'])) {
$groupid = $params['attendance_groupid'] == '__cong__' ? 0 : $params['attendance_groupid'];
$min_date = date('Y-m-d', strtotime('-' . (int) $params['attendance_weeks'] . ' weeks'));
$operator = $params['attendance_operator'] == '>' ? '>' : '<';
// nb whitelist because it will be used in the query directly
$query['where'][] = '(SELECT SUM(present)/COUNT(*)*100
FROM attendance_record
WHERE date >= ' . $GLOBALS['db']->quote($min_date) . '
AND groupid = ' . (int) $groupid . '
AND personid = p.id) ' . $operator . ' ' . (int) $params['attendance_percent'];
}
// GROUPING
$grouping_order = '';
$grouping_field = '';
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'][] = $this->_getGroupAndCategoryRestrictionSQL($params['include_groups'], array_get($params, 'group_join_date_from'), array_get($params, 'group_join_date_to'), array_get($params, 'group_membership_status'));
$grouping_order = 'pg.name, ';
} else {
$grouping_field = '';
}
} else {
$grouping_order = $grouping_field = $params['group_by'] . ', ';
}
}
// DISPLAY FIELDS
$joined_groups = FALSE;
if (empty($select_fields)) {
/*
* If the user chose to sort by Attendance or Absences but didn't
* include them in the list of required columns, just add them to the
* results. There is client-side code to deal with this,
* but this check here is for extra robustness.
*/
if ($params['sort_by'] == 'attendance_percent' && !in_array('attendance_percent', $params['show_fields'])) {
array_push($params['show_fields'], 'attendance_percent');
} else {
if ($params['sort_by'] == 'attendance_numabsences' && !in_array('attendance_numabsences', $params['show_fields'])) {
array_push($params['show_fields'], 'attendance_numabsences');
}
}
if (empty($params['show_fields'])) {
$params['show_fields'] = array('p.first_name', 'p.last_name');
}
foreach ($params['show_fields'] as $field) {
if (substr($field, 0, 2) == '--') {
continue;
}
// they selected a separator
switch ($field) {
case 'groups':
case 'membershipstatus':
if (empty($params['include_groups'])) {
continue;
}
if ($params['group_by'] == 'groupid') {
/* pg and pgm already joined for grouping purposes */
if ($field == 'groups') {
$query['select'][] = 'GROUP_CONCAT(pg.name ORDER BY pg.name SEPARATOR "\\n") as person_groups';
} else {
if ($field == 'membershipstatus') {
$query['from'] .= ' LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status';
$query['select'][] = 'pgms.label as `Membership Status`';
}
}
} else {
if (!$joined_groups) {
$query['from'] .= ' LEFT JOIN person_group_membership pgm ON p.id = pgm.personid
JOIN person_group pg ON pg.id = pgm.groupid
';
$query['where'][] = $this->_getGroupAndCategoryRestrictionSQL($params['include_groups'], array_get($params, 'group_join_date_from'), array_get($params, 'group_join_date_to'));
$joined_groups = TRUE;
}
if ($field == 'groups') {
$query['select'][] = 'GROUP_CONCAT(pg.name ORDER BY pg.name SEPARATOR "\\n") as person_groups';
} else {
if ($field == 'membershipstatus') {
$query['from'] .= ' LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status';
$query['select'][] = 'GROUP_CONCAT(pgms.label ORDER BY pg.name SEPARATOR "\\n") as `Membership Status`';
}
}
}
break;
case 'view_link':
case 'edit_link':
case 'checkbox':
case 'photo':
$query['select'][] = 'p.id as ' . $field;
break;
case 'all_members':
$query['from'] .= '
JOIN (
SELECT familyid, IF (
GROUP_CONCAT(DISTINCT last_name) = ff.family_name,
GROUP_CONCAT(first_name ORDER BY age_bracket, gender DESC SEPARATOR ", "),
GROUP_CONCAT(CONCAT(first_name, " ", last_name) ORDER BY age_bracket, gender DESC SEPARATOR ", ")
) AS `names`
FROM person pp
JOIN family ff ON pp.familyid = ff.id
WHERE pp.status <> "archived"
GROUP BY familyid
) all_members ON all_members.familyid = p.familyid
';
$query['select'][] = 'all_members.names as `All Family Members`';
break;
case 'adult_members':
/*
* For a left join to be efficient we need to
* create a temp table with an index rather than
* just joining a subquery.
*/
$r1 = $GLOBALS['db']->query('CREATE TEMPORARY TABLE _family_adults' . $this->id . ' (
familyid int(10) not null primary key,
names varchar(512) not null
)');
check_db_result($r1);
$r2 = $GLOBALS['db']->query('INSERT INTO _family_adults' . $this->id . ' (familyid, names)
SELECT familyid, IF (
GROUP_CONCAT(DISTINCT last_name) = ff.family_name,
GROUP_CONCAT(first_name ORDER BY age_bracket, gender DESC SEPARATOR ", "),
GROUP_CONCAT(CONCAT(first_name, " ", last_name) ORDER BY age_bracket, gender DESC SEPARATOR ", ")
)
FROM person pp
JOIN family ff ON pp.familyid = ff.id
WHERE pp.status <> "archived" AND pp.age_bracket = 0
GROUP BY familyid');
check_db_result($r2);
$query['from'] .= 'LEFT JOIN _family_adults' . $this->id . ' ON _family_adults' . $this->id . '.familyid = p.familyid
';
$query['select'][] = '_family_adults' . $this->id . '.names as `Adult Family Members`';
break;
case 'attendance_percent':
$groupid = $params['attendance_groupid'] == '__cong__' ? 0 : $params['attendance_groupid'];
$min_date = date('Y-m-d', strtotime('-' . (int) $params['attendance_weeks'] . ' weeks'));
$query['select'][] = '(SELECT CONCAT(ROUND(SUM(present)/COUNT(*)*100), "%")
FROM attendance_record
WHERE date >= ' . $GLOBALS['db']->quote($min_date) . '
AND groupid = ' . (int) $groupid . '
AND personid = p.id) AS `Attendance`';
break;
case 'attendance_numabsences':
/* The number of "absents" recorded since the last "present".*/
$groupid = $params['attendance_groupid'] == '__cong__' ? 0 : $params['attendance_groupid'];
$query['select'][] = '(SELECT COUNT(*)
FROM attendance_record ar
WHERE groupid = ' . (int) $groupid . '
AND personid = p.id
AND date > (SELECT COALESCE(MAX(date), "2000-01-01") FROM attendance_record ar2 WHERE ar2.personid = ar.personid AND present = 1)) AS `Running Absences`';
break;
case 'actionnotes.subjects':
$query['select'][] = '(SELECT GROUP_CONCAT(subject SEPARATOR ", ")
FROM abstract_note an
JOIN person_note pn ON an.id = pn.id
WHERE pn.personid = p.id
AND an.status = "pending"
AND an.action_date <= NOW()) AS `Notes`';
break;
case 'notes.subjects':
if (empty($params['note_phrase'])) {
$query['select'][] = '"" AS subjects';
break;
}
// else deliberate fallthrough...
// else deliberate fallthrough...
default:
$customFieldID = NULL;
if (substr($field, 0, 7) == 'date---') {
// backwards compat
$customFieldID = substr($field, 7);
} else {
if (0 === strpos($field, self::CUSTOMFIELD_PREFIX)) {
$customFieldID = substr($field, 14);
}
}
if ($customFieldID) {
if (isset($this->_custom_fields[$customFieldID])) {
$field = new Custom_Field();
$field->populate($customFieldID, $this->_custom_fields[$customFieldID]);
$query['from'] .= 'LEFT JOIN custom_field_value cfv' . $customFieldID . ' ON cfv' . $customFieldID . '.personid = p.id AND cfv' . $customFieldID . '.fieldid = ' . $db->quote($customFieldID) . "\n";
$query['select'][] = 'GROUP_CONCAT(DISTINCT ' . Custom_Field::getRawValueSQLExpr('cfv' . $customFieldID) . ' ORDER BY ' . Custom_Field::getRawValueSQLExpr('cfv' . $customFieldID) . ' SEPARATOR "' . self::CUSTOMFIELDVAL_SEP . '") as ' . $db->quote(self::CUSTOMFIELD_PREFIX . $customFieldID) . "\n";
}
} else {
$query['select'][] = $this->_quoteAliasAndColumn($field) . ' AS ' . $db->quote($field);
}
}
}
$select_fields = $grouping_field . 'p.id as ID, ' . implode(', ', $query['select']);
}
// ORDER BY
$customOrder = NULL;
if (substr($params['sort_by'], 0, 7) == 'date---') {
// backwards compatibility
$customOrder = substr($params['sort_by'], 8);
} else {
if (0 === strpos($params['sort_by'], self::CUSTOMFIELD_PREFIX)) {
$customOrder = substr($params['sort_by'], 14);
}
}
if ($customOrder) {
$query['from'] .= 'LEFT JOIN custom_field_value cfvorder ON cfvorder.personid = p.id AND cfvorder.fieldid = ' . $db->quote($customOrder) . "\n";
$query['from'] .= "LEFT JOIN custom_field_option cfoorder ON cfoorder.id = cfvorder.value_optionid \n";
$order = array();
$order[] = 'IF(cfvorder.personid IS NULL, 1, 0)';
// put those without a value last
if ($this->_custom_fields[$customOrder]['type'] == 'date') {
$order[] = 'IF(cfvorder.value_date LIKE "-%", 1, 0)';
// put full dates before partial dates
}
$order[] = 'GROUP_CONCAT(' . Custom_Field::getSortValueSQLExpr('cfvorder', 'cfoorder') . ')';
$query['order_by'] = implode(', ', $order);
} else {
if ($params['sort_by'] == 'p.congregationid') {
// Order by congregation meeting time then congregation name
$query['from'] .= '
LEFT JOIN congregation cord ON p.congregationid = cord.id ';
$query['order_by'] = 'IF(cord.id IS NULL, 1, 0), IF(LENGTH(cord.meeting_time)>0, 0, 1), cord.meeting_time, cord.name';
} else {
$query['order_by'] = $this->_quoteAliasAndColumn($params['sort_by']);
}
}
if ($grouping_order) {
$query['order_by'] = $grouping_order . $query['order_by'];
}
if ($params['sort_by'] == 'f.family_name') {
// Stop members of identically-named families from being intermingled
$query['order_by'] .= ', f.id';
}
/*
* We can order by attendances or absences safely,
* because we have already ensured they will appear
* the select clause.
*/
$rewrites = array('`attendance_percent`' => '`Attendance` ASC', '`attendance_numabsences`' => '`Running Absences` DESC', '`membershipstatus`' => 'pgms.rank');
$query['order_by'] = str_replace(array_keys($rewrites), array_values($rewrites), $query['order_by']);
if (!strlen(trim($query['order_by'], '`'))) {
$query['order_by'] = 1;
}
// Build SQL
$sql = 'SELECT ' . $select_fields . '
FROM ' . $query['from'] . '
';
if (!empty($query['where'])) {
$sql .= 'WHERE
(' . implode(")\n\tAND (", $query['where']) . ')
';
}
$sql .= 'GROUP BY p.id ';
if (array_get($params, 'group_by') == 'groupid') {
$sql .= ', pg.id ';
}
$sql .= 'ORDER BY ' . $query['order_by'] . ', p.last_name, p.first_name';
return $sql;
}
