function tally($group, $status = 1) { global $pommo; $dbo =& $pommo->_dbo; $pommo->requireOnce($pommo->_baseDir . 'inc/classes/sql.gen.php'); if (empty($group['rules'])) { return 0; } $query = PommoSQL::groupSQL($group, true, $status); return $dbo->query($query, 0); }
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; }
case 'not': case 'less': case 'greater': $values = array_unique($_REQUEST['match']); $type = $_REQUEST['type'] == 'or' ? 'or' : 'and'; PommoRules::addFieldRule($state['group'], $_REQUEST['field'], $_REQUEST['logic'], $values, $type); break; } $json->add('callbackFunction', 'redirect'); $json->add('callbackParams', $pommo->_baseUrl . 'admin/subscribers/groups_edit.php'); $json->serve(); break; case 'updateRule': Pommo::requireOnce($pommo->_baseDir . 'inc/classes/sql.gen.php'); $group =& current(PommoGroup::get(array('id' => $state['group']))); $rules = PommoSQL::sortRules($group['rules']); switch ($_REQUEST['request']) { case 'update': if ($_REQUEST['type'] == 'or' && count($rules['and']) < 2) { $json->add('callbackFunction', 'resume'); $json->success(Pommo::_T('At least 1 "and" rule must exist before an "or" rule takes effect.')); } PommoRules::changeType($group['id'], $_REQUEST['fieldID'], $_REQUEST['logic'], $_REQUEST['type']); break; case 'delete': PommoRules::deleteRule($group['id'], $_REQUEST['fieldID'], $_REQUEST['logic']); break; } $json->add('callbackFunction', 'redirect'); $json->add('callbackParams', $pommo->_baseUrl . 'admin/subscribers/groups_edit.php'); $json->serve();
/** EXAMPLE array( 'subscriber_pending' => array( 'pending_code' => array("not: 'abc1234'", "is: 'def123'", "is: '2234'"), 'pending_email' => array('not: NULL')), 'subscriber_data' => array( 12 => array("not: 'Milwaukee'"), // 12 is alias for field_id=12 ... 15 => array("greater: 15")), 'subscribers' => array( 'email' => "not: '*****@*****.**'"), 'status' => "equal: active" ); LEGAL LOGIC: (not|is|less|greater|true|false|equal) */ function &getIDByAttr($f = array('subscriber_pending' => array(), 'subscriber_data' => array(), 'subscribers' => array())) { global $pommo; $dbo =& $pommo->_dbo; $pommo->requireOnce($pommo->_baseDir . 'inc/classes/sql.gen.php'); $sql = array('where' => array(), 'join' => array()); if (!empty($f['subscribers'])) { $sql = array_merge_recursive($sql, PommoSQL::fromFilter($f['subscribers'], 's')); } if (!empty($f['subscriber_data'])) { $sql = array_merge_recursive($sql, PommoSQL::fromFilter($f['subscriber_data'], 'd')); } $p = null; if (!empty($f['subscriber_pending'])) { $p = 'p'; $sql = array_merge_recursive($sql, PommoSQL::fromFilter($f['subscriber_pending'], 'p')); } $joins = implode(' ', $sql['join']); $where = implode(' ', $sql['where']); $query = "\n\t\t\tSELECT DISTINCT s.subscriber_id\n\t\t\tFROM " . $dbo->table['subscribers'] . " s\n\t\t\t[LEFT JOIN " . $dbo->table['subscriber_pending'] . " %S\n\t\t\t\tON (s.subscriber_id = p.subscriber_id)]\n\t\t\t" . $joins . "\n\t\t\tWHERE 1 " . $where; $query = $dbo->prepare($query, array($p)); die($query); return $dbo->getAll($query, 'assoc', 'subscriber_id'); }