function &fromFilter(&$in, $p = null)
{
global $pommo;
$dbo =& Pommo::$_dbo;
$where = $joins = array();
// parse column => logic => value from array
$filters = array();
foreach ($in as $col => $val) {
Pommo_Sql::getLogic($col, $val, $filters);
}
// get where &/or joins
foreach ($filters as $col => $l) {
if (is_numeric($col)) {
// "likely" encountered a field_id in subscriber_data...
foreach ($l as $logic => $vals) {
$i = count($joins);
$join = "LEFT JOIN {$dbo->table['subscriber_data']} {$p}{$i} ON (s.subscriber_id = {$p}{$i}.subscriber_id AND {$p}{$i}.field_id={$col} AND ";
switch ($logic) {
case "is":
$joins[] = $dbo->prepare("[" . $join . "{$p}{$i}.value IN (%Q))]", array($vals));
break;
case "not":
$joins[] = $dbo->prepare("[" . $join . "{$p}{$i}.value NOT IN (%Q))]", array($vals));
break;
case "less":
$joins[] = $dbo->prepare("[" . $join . "{$p}{$i}.value < %I)]", array($vals[0]));
break;
case "greater":
$joins[] = $dbo->prepare("[" . $join . "{$p}{$i}.value > %I)]", array($vals[0]));
break;
case "true":
$joins[] = $join . "{$p}{$i}.value = 'on')";
break;
case "false":
$joins[] = $join . "{$p}{$i}.value != 'on')";
break;
case "like":
$joins[] = $dbo->prepare("[" . $join . "{$p}{$i}.value LIKE '%%S%']", array($vals[0]));
break;
}
}
} else {
foreach ($l as $logic => $vals) {
switch ($logic) {
case "is":
$where[] = $dbo->prepare("[AND {$p}.{$col} IN (%Q)]", array($vals));
break;
case "not":
$where[] = $dbo->prepare("[AND {$p}.{$col} NOT IN (%Q)]", array($vals));
break;
case "less":
$where[] = $dbo->prepare("[AND {$p}.{$col} < %I]", array($vals));
break;
case "greater":
$where[] = $dbo->prepare("[AND {$p}.{$col} > %I]", array($vals));
break;
case "true":
$where[] = "AND {$p}.{$col} = 'on'";
break;
case "false":
$where[] = "AND {$p}.{$col} != 'on'";
break;
case "equal":
$where[] = $dbo->prepare("[AND {$p}.{$col} = '%S']", array($vals[0]));
break;
case "like":
$where[] = $dbo->prepare("[AND {$p}.{$col} LIKE '%%S%']", array($vals[0]));
break;
}
}
}
}
// add joins to where clause -- TODO: this is where OR filtering can be looked up!
$c = count($joins);
for ($i = 0; $i < $c; $i++) {
$where[] = "AND {$p}{$i}.subscriber_id IS NOT NULL";
}
// for an "or", this could be left out!
return array('where' => $where, 'join' => $joins);
}
/** 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'); }