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