SETUP TEMPLATE, PAGE
*********************************/
Pommo::requireOnce($pommo->_baseDir . 'inc/classes/template.php');
$smarty = new PommoTemplate();
$smarty->assign('returnStr', Pommo::_T('Groups Page'));
// Initialize page state with default values overriden by those held in $_REQUEST
$state =& PommoAPI::stateInit('groups_edit', array('group' => 0), $_REQUEST);
$groups =& PommoGroup::get();
$fields =& PommoField::get();
$group =& $groups[$state['group']];
if (empty($group)) {
Pommo::redirect('subscribers_groups.php');
}
$rules = PommoSQL::sortRules($group['rules']);
$rules['and'] = PommoSQL::sortLogic($rules['and']);
$rules['or'] = PommoSQL::sortLogic($rules['or']);
foreach ($rules as $key => $a) {
if ($key == 'include' || $key == 'exclude') {
foreach ($a as $k => $gid) {
$rules[$key][$k] = $groups[$gid]['name'];
}
}
}
$smarty->assign('fields', $fields);
$smarty->assign('legalFieldIDs', PommoRules::getLegal($group, $fields));
$smarty->assign('legalGroups', PommoRules::getLegalGroups($group, $groups));
$smarty->assign('group', $group);
$smarty->assign('logicNames', PommoRules::getEnglish());
$smarty->assign('rules', $rules);
$smarty->assign('tally', PommoGroup::tally($group));
$smarty->assign('ruleCount', count($rules['and']) + count($rules['or']) + count($rules['include']) + count($rules['exclude']));
function groupSQL(&$group, $tally = false, $status = 1, $filter = false)
{
// used to prevent against group include/exclude recursion
static $groups;
if (!isset($groups[$group['id']])) {
$groups[$group['id']] = TRUE;
}
global $pommo;
$dbo =& $pommo->_dbo;
/*
SELECT count(subscriber_id)
from subscribers
where
status ='1'
AND ( // base group
subscriber_id in
(select subscriber_id from subscriber_data where field_id =3 and value IN ('on'))
AND subscriber_id in
(select subscriber_id from subscriber_data where field_id =4 and value NOT IN ('lemur'))
OR subscriber_id in
(select subscriber_id from subscriber_data where field_id =5 and value NOT IN ('on'))
)
AND subscriber_ID NOT IN( // exclude group
SELECT subscriber_id from subscribers where status ='1' AND (
subscriber_id in
(select ... zzz)
AND subsriber_id in
(select ... zzz)
OR subscriber_id in
(select ... zzz)
)
)
OR subscriber_ID IN( // include group
SELECT subscriber_id from subscribers where status ='1' AND (
subscriber_id in
(select ... zzz)
AND subsriber_id in
(select ... zzz)
OR subscriber_id in
(select ... zzz)
)
)
*/
$rules = PommoSQL::sortRules($group['rules']);
$ands = PommoSQL::getSubQueries(PommoSQL::sortLogic($rules['and']));
$ors = empty($rules['or']) ? array() : PommoSQL::getSubQueries(PommoSQL::sortLogic($rules['or']));
$sql = $tally ? 'SELECT count(subscriber_id) ' : 'SELECT subscriber_id ';
$sql .= "\n\t\t\tFROM {$dbo->table['subscribers']}\n\t\t\tWHERE status=" . intval($status);
$q = FALSE;
if (!empty($ands)) {
$sql .= " AND (\n";
foreach ($ands as $k => $s) {
if ($k != 0) {
$sql .= "\n AND ";
}
$sql .= $s;
}
foreach ($ors as $s) {
$sql .= "\n OR {$s}";
}
$sql .= "\n)";
$q = TRUE;
}
foreach ($rules['exclude'] as $gid) {
if (!isset($groups[$gid])) {
$sql .= "\nAND subscriber_id NOT IN (\n";
$sql .= PommoSQL::groupSQL(current(PommoGroup::get(array('id' => $gid))));
$sql .= "\n)";
}
$q = TRUE;
}
foreach ($rules['include'] as $gid) {
if (!isset($groups[$gid])) {
$sql .= "\n" . ($q ? 'OR' : 'AND') . " subscriber_id IN (\n";
$sql .= PommoSQL::groupSQL(current(PommoGroup::get(array('id' => $gid))));
$sql .= "\n)";
}
$q = TRUE;
}
// If a filter/search is requested, perform a match
if (is_array($filter) && !empty($filter['field']) && !empty($filter['string'])) {
// make MySQL LIKE() compliant
$filter['string'] = mysql_real_escape_string(addcslashes($filter['string'], '%_'));
$sql .= is_numeric($filter['field']) ? "\n AND subscriber_id in (select subscriber_id from {$dbo->table['subscriber_data']} WHERE field_id = " . (int) $filter['field'] . " AND value LIKE '%{$filter['string']}%')" : "\n AND " . mysql_real_escape_string($filter['field']) . " LIKE '%{$filter['string']}%'";
}
return $sql;
}