/** * 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; }