public static 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;
$rules = Pommo_Sql::sortRules($group['rules']);
$ands = Pommo_Sql::getSubQueries(Pommo_Sql::sortLogic($rules['and']));
$ors = empty($rules['or']) ? array() : Pommo_Sql::getSubQueries(Pommo_Sql::sortLogic($rules['or']));
$sql = $tally ? 'SELECT count(subscriber_id) ' : 'SELECT subscriber_id ';
$sql .= "\n FROM {$dbo->table['subscribers']}\n WHERE 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 .= Pommo_Sql::groupSQL(current(Pommo_Groups::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 .= Pommo_Sql::groupSQL(current(Pommo_Groups::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;
}
public static function tally($group, $status = 1)
{
$dbo =& Pommo::$_dbo;
require_once Pommo::$_baseDir . 'classes/Pommo_Sql.php';
if (empty($group['rules'])) {
return 0;
}
$query = Pommo_Sql::groupSQL($group, true, $status);
return $dbo->query($query, 0);
}
/** 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()))
{
$dbo = Pommo::$_dbo;
require_once Pommo::$_baseDir . 'classes/Pommo_Sql.php';
$sql = array('where' => array(), 'join' => array());
if (!empty($f['subscribers'])) {
$sql = array_merge_recursive($sql, Pommo_Sql::fromFilter($f['subscribers'], 's'));
}
if (!empty($f['subscriber_data'])) {
$sql = array_merge_recursive($sql, Pommo_Sql::fromFilter($f['subscriber_data'], 'd'));
}
$p = null;
if (!empty($f['subscriber_pending'])) {
$p = 'p';
$sql = array_merge_recursive($sql, Pommo_Sql::fromFilter($f['subscriber_pending'], 'p'));
}
$joins = implode(' ', $sql['join']);
$where = implode(' ', $sql['where']);
$query = "\n SELECT DISTINCT s.subscriber_id\n FROM " . $dbo->table['subscribers'] . " s\n [LEFT JOIN " . $dbo->table['subscriber_pending'] . " %S\n ON (s.subscriber_id = p.subscriber_id)]\n " . $joins . "\n WHERE 1 " . $where;
$query = $dbo->prepare($query, array($p));
die($query);
return $dbo->getAll($query, 'assoc', 'subscriber_id');
}
SETUP TEMPLATE, PAGE
*********************************/
require_once Pommo::$_baseDir . 'classes/Pommo_Template.php';
$view = new Pommo_Template();
$view->assign('returnStr', _('Groups Page'));
// Initialize page state with default values overriden by those held in $_REQUEST
$state =& Pommo_Api::stateInit('groups_edit', array('group' => 0), $_REQUEST);
$groups = Pommo_Groups::get();
$fields = Pommo_Fields::get();
$group =& $groups[$state['group']];
if (empty($group)) {
Pommo::redirect('subscribers_groups.php');
}
$rules = Pommo_Sql::sortRules($group['rules']);
$rules['and'] = Pommo_Sql::sortLogic($rules['and']);
$rules['or'] = Pommo_Sql::sortLogic($rules['or']);
foreach ($rules as $key => $a) {
if ($key == 'include' || $key == 'exclude') {
foreach ($a as $k => $gid) {
$rules[$key][$k] = $groups[$gid]['name'];
}
}
}
$view->assign('fields', $fields);
$view->assign('legalFieldIDs', Pommo_Rules::getLegal($group, $fields));
$view->assign('legalGroups', Pommo_Rules::getLegalGroups($group, $groups));
$view->assign('group', $group);
$view->assign('logicNames', Pommo_Rules::getEnglish());
$view->assign('rules', $rules);
$view->assign('tally', Pommo_Groups::tally($group));
$view->assign('ruleCount', count($rules['and']) + count($rules['or']) + count($rules['include']) + count($rules['exclude']));
case 'not':
case 'less':
case 'greater':
$values = array_unique($_REQUEST['match']);
$type = $_REQUEST['type'] == 'or' ? 'or' : 'and';
Pommo_Rules::addFieldRule($state['group'], $_REQUEST['field'], $_REQUEST['logic'], $values, $type);
break;
}
$json->add('callbackFunction', 'redirect');
$json->add('callbackParams', Pommo::$_baseUrl . 'groups_edit.php');
$json->serve();
break;
case 'updateRule':
require_once Pommo::$_baseDir . 'classes/Pommo_Sql.php';
$group =& current(Pommo_Groups::get(array('id' => $state['group'])));
$rules = Pommo_Sql::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.'));
}
Pommo_Rules::changeType($group['id'], $_REQUEST['fieldID'], $_REQUEST['logic'], $_REQUEST['type']);
break;
case 'delete':
Pommo_Rules::deleteRule($group['id'], $_REQUEST['fieldID'], $_REQUEST['logic']);
break;
}
$json->add('callbackFunction', 'redirect');
$json->add('callbackParams', Pommo::$_baseUrl . 'groups_edit.php');
$json->serve();
