function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['from'] .= ' JOIN service_component_tag sct ON sct.id = tagid '; $res['select'][] = 'sct.tag'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'cong.*'; $res['from'] .= ' JOIN congregation cong ON cong.id = congregation_service_component.congregationid'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['from'] .= "\n LEFT JOIN custom_field cf ON cf.id = note_template_field.customfieldid \n"; $res['select'][] = 'cf.name as customfieldname'; $res['select'][] = 'note_template_field.params'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'g.name as volunteer_group_name, c.name as congregation_name'; $res['from'] = '(' . $res['from'] . ') LEFT OUTER JOIN person_group g ON roster_role.volunteer_group = g.id LEFT OUTER JOIN congregation c ON roster_role.congregationid = c.id'; return $res; }
/** * Funny behaviour here: tagid and congregationid are always ANDed even if $logic=or */ function getInstancesQueryComps($params, $logic, $order) { $congid = array_get($params, 'congregationid'); unset($params['congregationid']); $tagid = array_get($params, 'tagid'); unset($params['tagid']); $keyword = array_get($params, 'keyword'); unset($params['keyword']); $res = parent::getInstancesQueryComps($params, $logic, $order); foreach ($res['select'] as $k => $v) { if (substr($v, -12) == 'content_html') { unset($res['select'][$k]); } } $res['select'][] = 'GROUP_CONCAT(DISTINCT cong.name SEPARATOR ", ") as congregations'; $res['from'] .= ' JOIN service_component_category cat ON cat.id = service_component.categoryid'; $res['from'] .= ' LEFT JOIN congregation_service_component csc ON csc.componentid = service_component.id '; $res['from'] .= ' LEFT JOIN congregation cong ON cong.id = csc.congregationid '; $res['from'] .= ' LEFT JOIN service_item si ON si.componentid = service_component.id '; $res['from'] .= ' LEFT JOIN service svc ON svc.id = si.serviceid AND svc.congregationid = cong.id '; $res['from'] .= ' LEFT JOIN service svc1m ON svc1m.id = svc.id AND svc1m.date > NOW() - INTERVAL 1 MONTH '; $res['from'] .= ' LEFT JOIN service svc12m ON svc12m.id = svc.id AND svc12m.date > NOW() - INTERVAL 12 MONTH '; $res['select'][] = 'IF (LENGTH(service_component.runsheet_title_format) = 0, cat.runsheet_title_format, service_component.runsheet_title_format) as runsheet_title_format '; $res['select'][] = 'IF (LENGTH(service_component.personnel) = 0, cat.personnel_default, service_component.personnel) as personnel '; $res['select'][] = 'COUNT(DISTINCT svc1m.id) AS usage_1m'; $res['select'][] = 'COUNT(DISTINCT svc12m.id) AS usage_12m'; $res['select'][] = 'MAX(svc.date) as lastused'; $res['group_by'] = 'service_component.id'; if ($res['where'] == '') { $res['where'] = '1=1'; } $res['where'] = '(' . $res['where'] . ') '; if ($congid === 0) { $res['where'] .= ' ' . $logic . ' cong.id IS NULL'; } else { if ($congid !== NULL) { $res['where'] .= ' ' . $logic . ' cong.id = ' . (int) $congid; } else { $res['where'] .= ' ' . $logic . ' cong.id IS NOT NULL'; } } if ($tagid) { $res['from'] .= ' LEFT JOIN service_component_tagging sct ON sct.componentid = service_component.id AND sct.tagid = ' . (int) $tagid; $res['where'] .= ' ' . $logic . ' sct.tagid IS NOT NULL'; } if ($keyword) { $qk = $GLOBALS['db']->quote("%{$keyword}%"); $res['where'] .= ' ' . $logic . ' (service_component.title LIKE ' . $qk . ' OR alt_title LIKE ' . $qk . ' OR content_html LIKE ' . $qk . ')'; } return $res; }
public function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'COUNT(p.id) AS member_count'; $res['from'] .= ' LEFT JOIN person p ON p.status <> "archived" AND p.congregationid = congregation.id '; $res['group_by'] = 'congregation.id'; $restrictions = array(); if (!empty($GLOBALS['user_system'])) { $restrictions = $GLOBALS['user_system']->getCurrentRestrictions(); } if (!empty($restrictions['congregation'])) { $oldwhere = $res['where']; $res['where'] = 'congregation.id IN (' . implode(',', array_map(array($GLOBALS['db'], 'quote'), $restrictions['congregation'])) . ') '; if ($oldwhere) { $res['where'] .= ' AND (' . $res['where'] . ')'; } } return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'members'; $res['from'] = '(SELECT roster_view_id, GROUP_CONCAT(member_name ORDER BY order_num SEPARATOR ", ") as members FROM ( ( SELECT rvrm.roster_view_id as roster_view_id, CONCAT(COALESCE(rrc.name, ""), " ", rr.title) as member_name, rvrm.order_num as order_num FROM roster_view_role_membership rvrm JOIN ( roster_role rr LEFT JOIN congregation rrc ON rr.congregationid = rrc.id ) ON rvrm.roster_role_id = rr.id ) UNION ( SELECT rvdi.roster_view_id as roster_view_id, CONCAT(dic.name, " ", COALESCE(scc.category_name, rvdi.service_field)) as member_name, rvdi.order_num as order_num FROM roster_view_service_field rvdi JOIN congregation dic ON rvdi.congregationid = dic.id LEFT JOIN service_component_category scc ON ((rvdi.service_field LIKE "comps_%") AND (scc.id = SUBSTRING(rvdi.service_field, 7))) ) ORDER BY order_num ) res GROUP BY roster_view_id ) m RIGHT OUTER JOIN roster_view on m.roster_view_id = roster_view.id'; return $res; }
/** * Get details of an SQL query to retrieve instances of this object type in bulk. * Overriden to add options to the results. * @param array $params * @param string $logic * @param string $order * @see DB_Object::getInstancesQueryComps() * @return array Details of how to construct an SQL query */ public function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['from'] .= ' LEFT JOIN custom_field_option cfo ON cfo.fieldid = custom_field.id'; $res['select'][] = 'GROUP_CONCAT(CONCAT(cfo.id, "__:__", cfo.value) ORDER BY cfo.rank SEPARATOR ";;;") as options'; $res['select'][] = 'params'; $res['group_by'] = 'custom_field.id'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['from'] = '(' . $res['from'] . ') LEFT OUTER JOIN person_group_membership gm ON gm.groupid = person_group.id'; $res['select'][] = 'COUNT(gm.personid) as member_count'; $res['group_by'] = 'person_group.id'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'GROUP_CONCAT(CONCAT(sbr.bible_ref, "=", sbr.to_read, "=", sbr.to_preach) ORDER BY sbr.order_num SEPARATOR ";") as readings'; $res['from'] .= ' LEFT JOIN service_bible_reading sbr ON service.id = sbr.service_id'; $res['select'][] = 'IF (si.id IS NULL, 0, 1) as has_items'; $res['from'] .= ' LEFT JOIN service_item si ON si.serviceid = service.id AND si.rank = 0 '; $res['group_by'] = 'service.id'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['from'] .= "\n LEFT JOIN person_group_membership gm ON gm.groupid = person_group.id "; $res['from'] .= "\n LEFT JOIN person_group_category pgc ON person_group.categoryid = pgc.id "; $res['select'][] = 'COUNT(gm.personid) as member_count'; $res['select'][] = 'pgc.name as category'; $res['group_by'] = 'person_group.id'; return $res; }
function getInstancesQueryComps($params, $logic, $order) { $res = parent::getInstancesQueryComps($params, $logic, $order); $res['select'][] = 'GROUP_CONCAT(p.first_name ORDER BY p.age_bracket ASC, p.gender DESC SEPARATOR \', \') as members'; if (array_get($params, '!status') == 'archived') { // If we are excluding archived families, exclude archived members too $res['from'] .= ' JOIN person p ON family.id = p.familyid AND p.status <> "archived"'; // Families with no visible members will be excluded } else { $res['from'] .= ' JOIN person p ON family.id = p.familyid'; // Families with no visible members will be excluded } $res['group_by'] = 'family.id'; return $res; }