/**
* Execute this call
*
* @return void
* @access public
*/
function run()
{
$db = $GLOBALS['db'];
if (!empty($_REQUEST['personid'])) {
$obj = $GLOBALS['system']->getDBObject('person', (int) $_REQUEST['personid']);
$SQL = 'SELECT photodata FROM person_photo WHERE personid = ' . $obj->id;
} else {
if (!empty($_REQUEST['familyid'])) {
$obj = $GLOBALS['system']->getDBObject('family', (int) $_REQUEST['familyid']);
// for single-member families, treat person photo as family photo
$SQL = 'SELECT COALESCE(fp.photodata, IF(count(p.id) = 1, pp.photodata, NULL)) as photodata
FROM family f
LEFT JOIN family_photo fp ON fp.familyid = f.id
LEFT JOIN person p ON p.familyid = f.id
LEFT JOIN person_photo pp ON pp.personid = p.id
WHERE f.id = ' . (int) $obj->id . '
GROUP BY f.id';
}
}
if ($obj) {
$res = $db->queryRow($SQL);
check_db_result($res);
if ($res && $res['photodata']) {
header('Content-type: image/jpeg');
echo $res['photodata'];
return;
}
}
header('Content-type: image/gif');
$placeholder = !empty($_REQUEST['personid']) ? 'unknown.gif' : 'unknown_family.gif';
readfile(dirname(dirname(__FILE__)) . '/resources/img/' . $placeholder);
}
function processView()
{
if (!empty($_POST['datetypename'])) {
$to_add = $to_delete = $to_update = array();
foreach ($_POST['datetypename'] as $id => $name) {
if ($id == '_new_') {
foreach ($name as $n) {
if ($n) {
$to_add[] = $n;
}
}
} else {
if ($name) {
$to_update[$id] = $name;
}
}
}
foreach ($to_update as $id => $name) {
$SQL = 'UPDATE date_type
SET name = ' . $GLOBALS['db']->quote($name) . '
WHERE id = ' . (int) $id;
$res = $GLOBALS['db']->query($SQL);
check_db_result($res);
}
$res = $GLOBALS['db']->query('DELETE FROM date_type WHERE id NOT IN (' . implode(',', array_merge(array_keys($to_update))) . ')');
foreach ($to_add as $name) {
$SQL = 'INSERT INTO date_type (name)
VALUES (' . $GLOBALS['db']->quote($name) . ')';
$res = $GLOBALS['db']->query($SQL);
check_db_result($res);
}
add_message("Date types updated");
}
}
public static function getDataURL($type, $id)
{
$SQL = 'SELECT photodata FROM ' . $type . '_photo WHERE ' . $type . 'id = ' . (int) $id;
$res = $GLOBALS['db']->queryOne($SQL);
check_db_result($res);
return 'data:image/jpg;base64,' . base64_encode($res);
}
function delete()
{
$GLOBALS['system']->doTransaction('BEGIN');
parent::delete();
$sql = 'UPDATE person_group SET categoryid = 0 WHERE categoryid = ' . (int) $this->id;
$res = $GLOBALS['db']->query($sql);
check_db_result($res);
$GLOBALS['system']->doTransaction('COMMIT');
}
static function getCongregations()
{
$SQL = 'SELECT c.id, c.name
from congregation c
join member m on m.congregationid = c.id
group by c.id';
$res = $GLOBALS['db']->queryAll($SQL, null, null, true, false);
check_db_result($res);
return $res;
}
public static function fetchAverage($entitytype, $entityid, $fromDate, $toDate)
{
self::checkEntityType($entitytype);
$db = $GLOBALS['db'];
$SQL = 'SELECT AVG(number) FROM ' . $entitytype . '_headcount
WHERE (`date` BETWEEN ' . $db->quote($fromDate) . ' AND ' . $db->quote($toDate) . ')
AND ' . $entitytype . 'id = ' . $db->quote($entityid);
$res = $db->queryOne($SQL);
check_db_result($res);
return $res;
}
public function processView()
{
$db = $GLOBALS['db'];
if (!empty($_POST['group_membership_statuses_submitted'])) {
$i = 0;
$saved_default = false;
$rankMap = $_REQUEST['membership_status_ranking'];
foreach ($rankMap as $k => $v) {
if ($v == '') {
$rankMap[$k] = max($rankMap) + 1;
}
}
$ranks = array_flip($rankMap);
while (isset($_POST['membership_status_' . $i . '_label'])) {
$sql = null;
$is_default = (int) ($_POST['membership_status_default_rank'] == $i);
if (empty($_POST['membership_status_' . $i . '_id'])) {
if (!empty($_POST['membership_status_' . $i . '_label'])) {
$sql = 'INSERT INTO person_group_membership_status (label, rank, is_default)
VALUES (' . $db->quote($_POST['membership_status_' . $i . '_label']) . ', ' . (int) $ranks[$i] . ',' . $is_default . ')';
}
} else {
if (!in_array($_POST['membership_status_' . $i . '_id'], array_get($_POST, 'membership_status_delete', array()))) {
$sql = 'UPDATE person_group_membership_status
SET label = ' . $db->quote($_POST['membership_status_' . $i . '_label']) . ',
is_default = ' . $is_default . ',
rank = ' . (int) $ranks[$i] . '
WHERE id = ' . (int) $_POST['membership_status_' . $i . '_id'];
}
}
if ($sql) {
$res = $db->query($sql);
check_db_result($res);
if ($is_default) {
$saved_default = true;
}
}
$i++;
}
if (!empty($_POST['membership_status_delete'])) {
$sql = 'DELETE FROM person_group_membership_status WHERE id IN (' . implode(',', array_map(array($db, 'quote'), $_POST['membership_status_delete'])) . ')';
$res = $db->query($sql);
check_db_result($res);
}
if (!$saved_default) {
$db->query('UPDATE person_group_membership_status SET is_default = 1 ORDER BY label LIMIT 1');
check_db_result($res);
}
$db->query('UPDATE person_group_membership SET membership_status = (SELECT id FROM person_group_membership_status WHERE is_default) WHERE membership_status IS NULL');
check_db_result($res);
}
}
/**
* Execute this call
*
* @return void
* @access public
*/
function run()
{
$db = $GLOBALS['db'];
$person = $GLOBALS['system']->getDBObject('person', (int) $_REQUEST['personid']);
if ($person) {
$sql = 'SELECT * FROM person_photo WHERE personid = ' . (int) $person->id;
$res = $db->queryRow($sql);
check_db_result($res);
if ($res) {
header('Content-type: image/jpeg');
// FIXME
echo $res['photodata'];
return;
}
}
header('Content-type: image/gif');
readfile(dirname(dirname(dirname(__FILE__))) . '/resources/img/unknown.gif');
}
static function getUpcomingAssignments($personid, $timeframe = '4 weeks')
{
$end_date = date('Y-m-d', strtotime('+' . $timeframe));
$sql = 'SELECT rra.assignment_date, COALESCE(c.name, "") as cong, rr.title, rr.id
FROM roster_role_assignment rra
JOIN roster_role rr ON rra.roster_role_id = rr.id
LEFT OUTER JOIN congregation c ON rr.congregationid = c.id
WHERE rra.personid = ' . $GLOBALS['db']->quote($personid);
if (!empty($timeframe)) {
$sql .= '
AND rra.assignment_date BETWEEN DATE(NOW()) AND ' . $GLOBALS['db']->quote($end_date);
} else {
$sql .= '
AND rra.assignment_date >= DATE(NOW())';
}
$sql .= '
ORDER BY rra.assignment_date ASC, c.meeting_time';
$res = $GLOBALS['db']->queryAll($sql, NULL, NULL, true, false, true);
check_db_result($res);
return $res;
}
function _insertRestrictions()
{
if (empty($this->id)) {
trigger_error("Don't have an ID, can't insert restrictions", E_USER_ERROR);
}
foreach (array('congregation', 'group') as $type) {
if (!empty($this->_restrictions[$type])) {
$rows = array();
foreach ($this->_restrictions[$type] as $id) {
// TODO: only insert new restrictions!!!!
$rows[] = '(' . (int) $this->id . ',' . (int) $id . ')';
}
$res = $GLOBALS['db']->query('INSERT IGNORE INTO account_' . $type . '_restriction (personid, ' . $type . 'id) VALUES ' . implode(',', $rows));
check_db_result($res);
}
}
}
public function updateMembershipStatuses($vals)
{
$GLOBALS['system']->doTransaction('BEGIN');
list($options, $default) = self::getMembershipStatusOptionsAndDefault();
foreach ($vals as $personid => $status) {
if (!isset($options[$status])) {
trigger_error("Invalid person status {$status} not saved");
continue;
}
$res = $GLOBALS['db']->query('UPDATE person_group_membership
SET membership_status = ' . $GLOBALS['db']->quote($status) . '
WHERE groupid = ' . (int) $this->id . '
AND personid = ' . (int) $personid);
check_db_result($res);
}
$GLOBALS['system']->doTransaction('COMMIT');
return TRUE;
}
/**
* Get Attendance data for the specified criteria
* @param array $congregationids
* @param int $groupid
* @param array $params Parameters to restrict person records, eg age bracket and status
* @param string $start_date
* @param string $end_date
* @return array
*/
public static function getAttendances($congregationids, $groupid, $params, $start_date, $end_date)
{
$SQL = 'SELECT person.id, person.last_name, person.first_name, ' . ($groupid ? 'pgms.label AS membership_status, ' : '') . ' person.status, ar.date, ar.present
FROM person person
JOIN family f ON person.familyid = f.id
';
if ($groupid) {
$SQL .= '
JOIN person_group_membership pgm ON pgm.personid = person.id AND pgm.groupid = ' . (int) $groupid;
}
// restricting the attendance dates within a subquery improves performance significantly.
$SQL .= '
LEFT JOIN (
SELECT personid, date, present
FROM attendance_record ar
WHERE ar.date BETWEEN ' . $GLOBALS['db']->quote($start_date) . ' AND ' . $GLOBALS['db']->quote($end_date) . '
AND ar.groupid = ' . (int) $groupid . '
) ar ON ar.personid = person.id';
if ($groupid) {
$SQL .= '
LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status';
}
$SQL .= '
WHERE ((person.status <> "archived") OR (ar.present IS NOT NULL)) ';
if ($congregationids) {
$SQL .= '
AND person.congregationid IN (' . implode(', ', array_map(array($GLOBALS['db'], 'quote'), $congregationids)) . ') ';
}
if (!empty($params['(age_bracket'])) {
$SQL .= '
AND person.age_bracket IN (' . implode(',', array_map(array($GLOBALS['db'], 'quote'), $params['(age_bracket'])) . ')';
}
$statuses = array_get($params, '(status', array());
if (isset($params['status'])) {
$statuses[] = $params['status'];
}
$statusClauses = array();
foreach ($statuses as $status) {
if (strlen($status)) {
list($statusType, $statusID) = explode('-', $status);
if ($statusType == 'g' && empty($groupid)) {
trigger_error("Cannot filter by group membership status for congregational attendance");
return array(array(), array(), array());
}
switch ($statusType) {
case 'g':
$statusClauses[] = 'pgm.membership_status = ' . $GLOBALS['db']->quote($statusID);
break;
case 'p':
$statusClauses[] = 'person.status = ' . $GLOBALS['db']->quote($statusID);
break;
}
}
}
if ($statusClauses) {
$SQL .= 'AND ((' . implode(') OR (', $statusClauses) . '))';
}
$order = defined('ATTENDANCE_LIST_ORDER') ? constant('ATTENDANCE_LIST_ORDER') : self::LIST_ORDER_DEFAULT;
if ($congregationids) {
$order = preg_replace("/(^|[^.])status(\$| |,)/", '\\1person.status\\2', $order);
} else {
$order = preg_replace("/(^|[^.])status(\$| |,)/", '\\1pgms.rank\\2', $order);
}
$SQL .= '
ORDER BY ' . $order;
$dates = array();
$attendances = array();
$totals = array();
$res = $GLOBALS['db']->query($SQL);
check_db_result($res);
while ($row = $res->fetchRow()) {
if (!empty($row['date'])) {
$dates[$row['date']] = 1;
}
foreach (array('last_name', 'first_name', 'membership_status', 'status') as $f) {
if (array_key_exists($f, $row)) {
$attendances[$row['id']][$f] = $row[$f];
}
}
$attendances[$row['id']][$row['date']] = $row['present'];
if (!isset($totals[$row['date']]) || !isset($totals[$row['date']][$row['present']])) {
$totals[$row['date']][$row['present']] = 0;
}
$totals[$row['date']][$row['present']]++;
}
$dates = array_keys($dates);
sort($dates);
return array($dates, $attendances, $totals);
}
public function getItems($withContent = FALSE, $ofCategoryID = NULL)
{
$SQL = 'SELECT si.*, sc.title, sc.alt_title, sc.is_numbered, ' . ($withContent ? 'sc.content_html, sc.credits, ' : '') . '
IF(LENGTH(sc.runsheet_title_format) = 0, scc.runsheet_title_format, sc.runsheet_title_format) AS runsheet_title_format,
IF(LENGTH(sc.handout_title_format) = 0, scc.handout_title_format, sc.handout_title_format) AS handout_title_format
FROM service_item si
LEFT JOIN service_component sc ON si.componentid = sc.id
LEFT JOIN service_component_category scc ON sc.categoryid = scc.id
WHERE si.serviceid = ' . (int) $this->id . '
';
if (!empty($ofCategoryID)) {
$SQL .= ' AND sc.categoryid = ' . (int) $ofCategoryID . "\n";
}
$SQL .= ' ORDER BY rank';
$res = $GLOBALS['db']->queryAll($SQL);
check_db_result($res);
return $res;
}
protected function _getInstancesData($query_bits)
{
$db = $GLOBALS['db'];
$sql = 'SELECT ' . implode(', ', $query_bits['select']) . '
FROM ' . $query_bits['from'];
if (!empty($query_bits['where'])) {
$sql .= '
WHERE ' . $query_bits['where'];
}
if (!empty($query_bits['group_by'])) {
$sql .= '
GROUP BY ' . $query_bits['group_by'];
}
if (!empty($query_bits['order_by'])) {
$sql .= '
ORDER BY ' . $query_bits['order_by'];
}
$res = $db->queryAll($sql, null, null, true, true);
// 5th param forces array even if one col
check_db_result($res);
return $res;
}
static function getDateTypes()
{
$sql = 'SELECT id, name
FROM date_type
ORDER BY name';
$res = $GLOBALS['db']->queryAll($sql, NULL, NULL, true);
check_db_result($res);
return $res;
}
public function getItems($withContent = FALSE, $ofCategoryID = NULL)
{
$SQL = 'SELECT si.*,
IF (si.componentid IS NULL, si.title, sc.title) AS title,
sc.alt_title,
' . ($withContent ? 'sc.content_html, sc.credits, ' : '') . '
IFNULL(IF(LENGTH(sc.runsheet_title_format) = 0, scc.runsheet_title_format, sc.runsheet_title_format), "%title%") AS runsheet_title_format,
IFNULL(IF(LENGTH(sc.handout_title_format) = 0, scc.handout_title_format, sc.handout_title_format), "%title%") AS handout_title_format,
IF(LENGTH(si.personnel) = 0, sc.personnel, si.personnel) AS personnel
FROM service_item si
LEFT JOIN service_component sc ON si.componentid = sc.id
LEFT JOIN service_component_category scc ON sc.categoryid = scc.id
WHERE si.serviceid = ' . (int) $this->id . '
';
if (!empty($ofCategoryID)) {
$SQL .= ' AND sc.categoryid = ' . (int) $ofCategoryID . "\n";
}
$SQL .= ' ORDER BY rank';
$res = $GLOBALS['db']->queryAll($SQL);
check_db_result($res);
foreach ($res as $k => &$item) {
$item['personnel'] = $this->replaceKeywords($item['personnel']);
}
unset($item);
return $res;
}
function initDB()
{
$dh = opendir(dirname(dirname(__FILE__)) . '/db_objects');
while (FALSE !== ($filename = readdir($dh))) {
if ($filename[0] == '.' || is_dir($filename)) {
continue;
}
$filenames[] = $filename;
}
$fks = array();
sort($filenames);
foreach ($filenames as $filename) {
$classname = str_replace('.class.php', '', $filename);
require_once dirname(dirname(__FILE__)) . '/db_objects/' . $filename;
$data_obj = new $classname();
if (method_exists($data_obj, 'getInitSQL')) {
$sql = $data_obj->getInitSQL();
if (!empty($sql)) {
if (!is_array($sql)) {
$sql = array($sql);
}
foreach ($sql as $s) {
$r = $GLOBALS['db']->query($s);
check_db_result($r);
}
}
$f = $data_obj->getForeignKeys();
if ($f) {
$fks[$classname] = $f;
}
}
}
$sql = array("CREATE TABLE `db_object_lock` (\n\t\t\t `objectid` int(11) NOT NULL default '0',\n\t\t\t `userid` int(11) NOT NULL default '0',\n\t\t\t `lock_type` VARCHAR( 16 ) NOT NULL,\n\t\t\t `object_type` varchar(255) collate latin1_general_ci NOT NULL default '',\n\t\t\t `expires` datetime NOT NULL default '0000-00-00 00:00:00',\n\t\t\t KEY `objectid` (`objectid`),\n\t\t\t KEY `userid` (`userid`),\n\t\t\t KEY `object_type` (`object_type`)\n\t\t\t) ENGINE=InnoDB ;", "CREATE FUNCTION getCurrentUserID() RETURNS INTEGER NO SQL RETURN @current_user_id;", "CREATE TABLE account_group_restriction (\n\t\t\t personid INTEGER NOT NULL,\n\t\t\t groupid INTEGER NOT NULL,\n\t\t\t PRIMARY KEY (personid, groupid),\n\t\t\t CONSTRAINT account_group_restriction_personid FOREIGN KEY (personid) REFERENCES staff_member(id),\n\t\t\t CONSTRAINT account_group_restriction_groupid FOREIGN KEY (groupid) REFERENCES _person_group(id)\n\t\t\t) engine=innodb;", "CREATE TABLE account_congregation_restriction (\n\t\t\t personid INTEGER NOT NULL,\n\t\t\t congregationid INTEGER NOT NULL,\n\t\t\t PRIMARY KEY (personid, congregationid),\n\t\t\t CONSTRAINT account_congregation_restriction_personid FOREIGN KEY (personid) REFERENCES staff_member(id),\n\t\t\t CONSTRAINT account_group_restriction_congregationid FOREIGN KEY (congregationid) REFERENCES congregation(id)\n\t\t\t) engine=innodb;", "CREATE VIEW person AS\n\t\t\tSELECT * from _person p\n\t\t\tWHERE\n\t\t\t\tgetCurrentUserID() IS NOT NULL\n\t\t\t\tAND (\n\t\t\t\t\t(`p`.`id` = `getCurrentUserID`())\n\t\t\t\t\tOR (`getCurrentUserID`() = -(1))\n\t\t\t\t\tOR (\n\t\t\t\t\t\t(\n\t\t\t\t\t\t(not(exists(select 1 AS `Not_used` from `account_congregation_restriction` `cr` where (`cr`.`personid` = `getCurrentUserID`()))))\n\t\t\t\t\t\tOR `p`.`congregationid` in (select `cr`.`congregationid` AS `congregationid` from `account_congregation_restriction` `cr` where (`cr`.`personid` = `getCurrentUserID`()))\n\t\t\t\t\t\t)\n\t\t\t\t\t\tAND\n\t\t\t\t\t\t(\n\t\t\t\t\t\t(not(exists(select 1 AS `Not_used` from `account_group_restriction` `gr` where (`gr`.`personid` = `getCurrentUserID`()))))\n\t\t\t\t\t\tOR `p`.`id` in (select `m`.`personid` AS `personid` from (`person_group_membership` `m` join `account_group_restriction` `gr` on((`m`.`groupid` = `gr`.`groupid`))) where (`gr`.`personid` = `getCurrentUserID`()))\n\t\t\t\t\t\t)\n\t\t\t\t\t)\n\t\t\t\t);", "CREATE VIEW person_group AS\n\t\t\tSELECT * from _person_group g\n\t\t\tWHERE\n\t\t\t getCurrentUserID() IS NOT NULL\n\t\t\t AND\n\t\t\t (NOT EXISTS (SELECT * FROM account_group_restriction gr WHERE gr.personid = getCurrentUserID())\n\t\t\t\t OR g.id IN (SELECT groupid FROM account_group_restriction gr WHERE gr.personid = getCurrentUserID()))", 'CREATE VIEW member AS
SELECT mp.id, mp.first_name, mp.last_name, mp.gender, mp.age_bracket, mp.congregationid,
mp.email, mp.mobile_tel, mp.work_tel, mp.familyid,
mf.family_name, mf.address_street, mf.address_suburb, mf.address_state, mf.address_postcode, mf.home_tel
FROM _person mp
JOIN family mf ON mf.id = mp.familyid
JOIN person_group_membership pgm1 ON pgm1.personid = mp.id
JOIN _person_group pg ON pg.id = pgm1.groupid AND pg.share_member_details = 1
JOIN person_group_membership pgm2 ON pgm2.groupid = pg.id
JOIN _person up ON up.id = pgm2.personid
WHERE up.id = getCurrentUserID()
AND mp.status <> "archived"
AND mf.status <> "archived"
AND up.status <> "archived" /* archived persons cannot see members of any group */
UNION
SELECT mp.id, mp.first_name, mp.last_name, mp.gender, mp.age_bracket, mp.congregationid,
mp.email, mp.mobile_tel, mp.work_tel, mp.familyid,
mf.family_name, mf.address_street, mf.address_suburb, mf.address_state, mf.address_postcode, mf.home_tel
FROM _person mp
JOIN family mf ON mf.id = mp.familyid
JOIN _person self ON self.familyid = mp.familyid
WHERE
self.id = getCurrentUserID()
AND mp.status <> "archived"
AND mf.status <> "archived"
AND ((self.status <> "archived") OR (mp.id = self.id))
/* archived persons can only see themselves, not any family members */
;');
foreach ($sql as $s) {
$r = $GLOBALS['db']->query($s);
check_db_result($r);
}
foreach ($fks as $table => $keys) {
foreach ($keys as $from => $to) {
if (FALSE !== strpos($from, '.')) {
list($table, $from) = explode('.', $from);
}
$name = $from;
$SQL = 'ALTER TABLE ' . $table . '
ADD CONSTRAINT `' . $name . '`
FOREIGN KEY (' . $from . ') REFERENCES ' . $to;
$r = $GLOBALS['db']->query($SQL);
check_db_result($r);
}
}
}
function printResults($format = 'html')
{
$db =& $GLOBALS['db'];
$params = $this->getValue('params');
$sql = $this->getSQL();
if (is_null($sql)) {
return;
}
if ($format == 'html' && in_array('checkbox', $params['show_fields'])) {
echo '<form method="post" enctype="multipart/form-data" class="bulk-person-action">';
}
$grouping_field = $params['group_by'];
if (empty($grouping_field)) {
$res = $db->queryAll($sql, null, null, true, 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 ($res && $format == 'html' && in_array('checkbox', $params['show_fields'])) {
echo '<div class="no-print">';
include 'templates/bulk_actions.template.php';
echo '</div>';
echo '</form>';
}
}
$SQL .= '
FROM _person p
JOIN custom_field_value cfv ON cfv.personid = p.id AND cfv.fieldid = ' . (int) $ini['CUSTOM_FIELD_ID'];
if ($ini['CC_STATUS']) {
$map = array_flip(Person::getStatusOptions());
$SQL .= '
LEFT JOIN _person cc ON (
LENGTH(cc.email) > 0
AND cc.congregationid = p.congregationid
AND cc.status = ' . $GLOBALS['db']->quote($map[$ini['CC_STATUS']]) . '
)';
}
$SQL .= '
WHERE cfv.value_date = CURDATE() + INTERVAL ' . (int) $ini['REMINDER_OFFSET'] . ' DAY';
$res = $GLOBALS['db']->queryAll($SQL);
check_db_result($res);
foreach ($res as $row) {
send_reminder($row);
}
function send_reminder($person)
{
global $ini;
$toEmail = $person['email'];
if (!empty($ini['OVERRIDE_RECIPIENT'])) {
$toEmail = $ini['OVERRIDE_RECIPIENT'];
}
if (!strlen($person['email'])) {
if (!empty($ini['VERBOSE'])) {
echo $person['first_name'] . ' ' . $person['last_name'] . " has no email address - skipping \n";
}
return;
function printResults($with_links = FALSE)
{
$db = $GLOBALS['db'];
$groupid = (int) $_REQUEST['groupid'];
$all_member_details = array_get($_REQUEST, 'all_member_details', 0);
if (empty($groupid)) {
return;
}
$sql = '
select family.id as familyid, family.family_name, family.home_tel,
person.*, congregation.long_name as congname,
address_street, address_suburb, address_state, address_postcode
from family
join person on family.id = person.familyid
left join congregation on person.congregationid = congregation.id
where person.status <> "archived"
and family.id in
(select familyid
from person join person_group_membership pgm on person.id = pgm.personid
where pgm.groupid = ' . (int) $groupid;
if (!empty($_REQUEST['congregationid'])) {
$sql .= '
AND person.congregationid in (' . implode(',', array_map(array($db, 'quote'), $_REQUEST['congregationid'])) . ')';
}
$sql .= ')
order by family_name asc, age_bracket asc, gender desc
';
$res = $db->queryAll($sql, null, null, true, true, true);
check_db_result($res);
if (empty($res)) {
?>
<p><i>No families to show</i></p><?php
return;
}
$sql = '
select personid
from person_group_membership pgm
where pgm.groupid = ' . (int) $groupid;
$signups = $db->queryCol($sql);
check_db_result($signups);
$GLOBALS['system']->includeDBClass('family');
$GLOBALS['system']->includeDBClass('person');
$dummy_family = new Family();
$dummy_person = new Person();
?>
<table class="contact-list">
<?php
foreach ($res as $familyid => $family_members) {
$adults = array();
$children = array();
$adults_use_full = false;
$children_use_full = false;
foreach ($family_members as $member) {
if (empty($_REQUEST['age_bracket']) || in_array($member['age_bracket'], $_REQUEST['age_bracket'])) {
$adults[] = $member;
if ($member['last_name'] != $member['family_name']) {
$adults_use_full = true;
}
} else {
$children[] = $member;
if ($member['last_name'] != $member['family_name']) {
$children_use_full = true;
}
}
}
$first_member = reset($family_members);
?>
<tr><td colspan="4"><h2 style="margin-bottom: 0px"><?php
echo $first_member['family_name'];
?>
</h2></td></tr>
<?php
if ($first_member['home_tel']) {
$dummy_family->setValue('home_tel', $first_member['home_tel']);
echo '<tr><td colspan="4"><h3 style="border: 0px; margin: 0px; padding: 0px">';
echo ents($dummy_family->getFormattedValue('home_tel'));
echo '</h3></td></tr>';
}
if (!empty($_REQUEST['include_address']) && $first_member['address_street']) {
echo '<tr><td colspan="4">' . nl2br(ents($first_member['address_street'])) . '<br />';
echo ents($first_member['address_suburb'] . ' ' . $first_member['address_state'] . ' ' . $first_member['address_postcode']);
echo '</td></tr>';
}
$fn = $with_links ? 'printFieldValue' : 'getFormattedValue';
foreach ($adults as $adult) {
$dummy_person->populate($adult['id'], $adult);
?>
<tr>
<td><?php
echo ents($adults_use_full ? $adult['first_name'] . ' ' . $adult['last_name'] : $adult['first_name']);
?>
</td>
<td><?php
echo ents($adult['congname']);
?>
</td>
<td><?php
if ($all_member_details || in_array($adult['id'], $signups)) {
echo ents($dummy_person->getFormattedValue('mobile_tel'));
}
?>
</td>
<td><?php
if ($all_member_details || in_array($adult['id'], $signups)) {
echo ents($dummy_person->{$fn}('email'));
}
?>
</td>
</tr>
<?php
}
$child_names = array();
foreach ($children as $child) {
$child_names[] = $children_use_full ? $child['first_name'] . ' ' . $child['last_name'] : $child['first_name'];
}
if ($child_names) {
?>
<tr>
<td colspan="4"><?php
echo ents(implode(', ', $child_names));
?>
</td
</tr>
<?php
}
?>
<?php
}
?>
</table>
<?php
}
function getAttendances($congregationids, $groupid, $age_bracket, $start_date, $end_date)
{
$SQL = 'SELECT p.id, p.last_name, p.first_name, ' . ($groupid ? 'pgms.label AS membership_status, ' : '') . ' p.status, ar.date, ar.present
FROM person p
JOIN family f ON p.familyid = f.id
';
if ($groupid) {
$SQL .= '
JOIN person_group_membership pgm ON pgm.personid = p.id AND pgm.groupid = ' . (int) $groupid;
}
$SQL .= '
LEFT JOIN attendance_record ar ON ar.personid = p.id
AND ar.date BETWEEN ' . $GLOBALS['db']->quote($start_date) . ' AND ' . $GLOBALS['db']->quote($end_date);
if ($congregationids) {
$SQL .= ' AND ar.groupid = 0';
}
if ($groupid) {
$SQL .= ' AND ar.groupid = ' . (int) $groupid;
$SQL .= '
LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status';
}
$SQL .= '
WHERE ((p.status <> "archived") OR (ar.present IS NOT NULL)) ';
if ($congregationids) {
$SQL .= '
AND p.congregationid IN (' . implode(', ', array_map(array($GLOBALS['db'], 'quote'), $congregationids)) . ') ';
}
if ($age_bracket !== '') {
$SQL .= '
AND p.age_bracket = ' . $GLOBALS['db']->quote($age_bracket);
}
$order = defined('ATTENDANCE_LIST_ORDER') ? constant('ATTENDANCE_LIST_ORDER') : self::LIST_ORDER_DEFAULT;
$order = preg_replace("/(^|[^.])status(\$| |,)/", '\\1p.status\\2', $order);
$SQL .= '
ORDER BY ' . $order;
$dates = array();
$attendances = array();
$totals = array();
$res = $GLOBALS['db']->query($SQL);
check_db_result($res);
while ($row = $res->fetchRow()) {
if (!empty($row['date'])) {
$dates[$row['date']] = 1;
}
foreach (array('last_name', 'first_name', 'membership_status', 'status') as $f) {
if (array_key_exists($f, $row)) {
$attendances[$row['id']][$f] = $row[$f];
}
}
$attendances[$row['id']][$row['date']] = $row['present'];
if (!isset($totals[$row['date']]) || !isset($totals[$row['date']][$row['present']])) {
$totals[$row['date']][$row['present']] = 0;
}
$totals[$row['date']][$row['present']]++;
}
$dates = array_keys($dates);
sort($dates);
return array($dates, $attendances, $totals);
}
function delete()
{
if (!$this->canBeDeleted()) {
trigger_error("This note can not be deleted");
return FALSE;
}
if (!parent::delete()) {
return FALSE;
}
$db =& $GLOBALS['db'];
$sql = 'DELETE FROM note_comment WHERE noteid = ' . $db->quote($this->id);
$res = $db->query($sql);
check_db_result($res);
return TRUE;
}
if (!empty($missing_vars)) {
trigger_error("Your mailchimp list is missing the merge vars " . implode(', ', $missing_vars) . '. Set these up in Mailchimp then try again.', E_USER_ERROR);
}
// Check that we have a report
if (!(int) $report_id) {
trigger_error("No Report ID found - correct your config within " . __FILE__, E_USER_ERROR);
}
$report = $GLOBALS['system']->getDBObject('person_query', (int) $report_id);
if (empty($report)) {
trigger_error("Could not find report #{$report_id} - please check your config in " . __FILE__, E_USER_ERROR);
}
// BUSINESS TIME
$db =& $GLOBALS['db'];
$sql = $report->getSQL('LOWER(p.email) as loweremail, p.email, p.first_name, p.last_name, p.gender, p.age_bracket, p.status, p.congregationid');
$report_members = $db->queryAll($sql, null, null, true);
check_db_result($report_members);
unset($report_members['']);
// with no email.
if ($DEBUG > 1) {
bam("PERSONS FROM REPORT (excl email-less persons):");
bam($report_members);
bam("========================");
}
// For each chunk of report members, retrieve their details from mailchimp.
// If found, update details if needed
// If not found, add to "add" list
$to_add = array();
foreach (array_chunk($report_members, 49, true) as $chunk) {
$list_infos = $api->listMemberInfo($list_id, array_keys($chunk));
if (!empty($api->errorMessage)) {
trigger_error("Mailchimp API Error calling listMemberInfo(): " . $api->errorMessage, E_USER_ERROR);
public function doTransaction($operation)
{
switch (strtoupper($operation)) {
case 'BEGIN':
case 'COMMIT':
case 'ROLLBACK':
$r = $GLOBALS['db']->query(strtoupper($operation));
check_db_result($r);
}
}
private function _findUser($username, $password)
{
$db =& $GLOBALS['db'];
$sql = 'SELECT sm.*, p.*, GROUP_CONCAT(cr.congregationid) as congregation_restrictions, GROUP_CONCAT(gr.groupid) as group_restrictions
FROM staff_member sm
JOIN _person p ON sm.id = p.id
LEFT JOIN account_congregation_restriction cr ON cr.personid = sm.id
LEFT JOIN account_group_restriction gr ON gr.personid = sm.id
WHERE sm.username = '******'
AND active = 1
GROUP BY p.id';
$row = $db->queryRow($sql);
check_db_result($row);
if (!empty($row) && jethro_password_verify($password, $row['password'])) {
$row['congregation_restrictions'] = empty($row['congregation_restrictions']) ? array() : explode(',', $row['congregation_restrictions']);
$row['group_restrictions'] = empty($row['group_restrictions']) ? array() : explode(',', $row['group_restrictions']);
return $row;
}
return NULL;
}
function getFamilyDataByMemberIDs($member_ids)
{
$quoted_ids = implode(',', array_map(array($GLOBALS['db'], 'quote'), $member_ids));
$sql = '
SELECT f.*,
allmembers.names as members,
IFNULL(adultmembers.names, "") as adult_members,
GROUP_CONCAT(p.first_name ORDER BY p.age_bracket ASC, p.gender, p.id DESC SEPARATOR ",") as selected_firstnames,
GROUP_CONCAT(p.last_name ORDER BY p.age_bracket ASC, p.gender, p.id DESC SEPARATOR ",") as selected_lastnames
FROM family f
JOIN person p ON f.id= p.familyid
JOIN
(select f.id as familyid, GROUP_CONCAT(p.first_name ORDER BY p.age_bracket ASC, p.gender DESC SEPARATOR ", ") as names
FROM person p JOIN family f on p.familyid = f.id
WHERE p.status <> "archived"
GROUP BY f.id
) allmembers ON allmembers.familyid = f.id
LEFT JOIN
(select f.id as familyid, GROUP_CONCAT(p.first_name ORDER BY p.age_bracket ASC, p.gender DESC SEPARATOR ", ") as names
FROM person p JOIN family f on p.familyid = f.id
WHERE p.age_bracket = 0 and p.status <> "archived"
GROUP BY f.id) adultmembers ON adultmembers.familyid = f.id
WHERE p.id IN (' . $quoted_ids . ')
GROUP BY f.id
ORDER BY f.family_name';
$res = $GLOBALS['db']->queryAll($sql, NULL, NULL, TRUE);
check_db_result($res);
return $res;
}
/**
* Get all the roster views that should be shown on the run sheet for the specified congregation
* @param int $congregationid
* @return array
*/
static function getForRunSheet($congregationid)
{
$res = array();
$SQL = '
SELECT id
FROM roster_view
WHERE show_on_run_sheet = 1
AND id IN (
SELECT DISTINCT roster_view_id
FROM roster_view_service_field sf
WHERE sf.congregationid = ' . (int) $congregationid . '
UNION
SELECT DISTINCT roster_view_id
FROM roster_view_role_membership rm
JOIN roster_role rr ON rr.id = rm.roster_role_id
WHERE rr.congregationid = ' . (int) $congregationid . '
)';
$ids = $GLOBALS['db']->queryCol($SQL);
check_db_result($ids);
foreach ($ids as $id) {
$res[] = $GLOBALS['system']->getDBObject('roster_view', $id);
}
return $res;
}
private function _saveTags($deleteOld = FALSE)
{
if ($deleteOld) {
check_db_result($GLOBALS['db']->exec('DELETE FROM service_component_tagging WHERE componentid = ' . (int) $this->id));
}
$sets = array();
foreach (array_unique(array_get($this->_tmp, 'tagids', array())) as $tagid) {
$sets[] = '(' . (int) $this->id . ', ' . (int) $tagid . ')';
}
if (!empty($sets)) {
$SQL = 'INSERT INTO service_component_tagging
(componentid, tagid)
VALUES
' . implode(",\n", $sets);
$x = $GLOBALS['db']->exec($SQL);
check_db_result($x);
}
}
static function getStatusStats()
{
$dummy = new Person();
$status_options = $dummy->getStatusOptions();
$sql = 'SELECT status, count(id)
FROM person
GROUP BY status';
$res = $GLOBALS['db']->queryAll($sql, NULL, NULL, true);
check_db_result($res);
$out = array();
foreach ($status_options as $k => $v) {
$out[$v] = (int) array_get($res, $k, 0);
}
return $out;
}
/**
* Find a person record that matches the given email and password
* @param string $email Find a person with this record
* @param string $password Find a person with this member_password
* @return array Person details
*/
private function _findAuthMember($email, $password)
{
$db =& $GLOBALS['db'];
$sql = 'SELECT p.*
FROM _person p
WHERE p.email = ' . $db->quote($email) . ' AND member_password IS NOT NULL';
$res = $db->queryAll($sql);
check_db_result($res);
foreach ($res as $row) {
if (jethro_password_verify($password, $row['member_password'])) {
unset($row['member_password']);
unset($row['history']);
return $row;
}
}
return NULL;
}
