function &get($p = array(), $search = array('field' => null, 'string' => null))
{
$defaults = array('status' => 'all', 'email' => null, 'sort' => null, 'order' => null, 'limit' => null, 'offset' => null, 'id' => null);
$p = PommoAPI::getParams($defaults, $p);
global $pommo;
$dbo =& $pommo->_dbo;
if ($p['status'] == 'all') {
$p['status'] = null;
}
if (is_numeric($p['limit']) && !is_numeric($p['offset'])) {
$p['offset'] = 0;
}
$o = array();
$query = "\n\t\t\tSELECT\n\t\t\t\ts.subscriber_id,\n\t\t\t\ts.email,\n\t\t\t\ts.time_touched,\n\t\t\t\ts.time_registered,\n\t\t\t\ts.flag,\n\t\t\t\tINET_NTOA(s.ip) ip,\n\t\t\t\ts.status,\n\t\t\t\tp.pending_code,\n\t\t\t\tp.pending_array,\n\t\t\t\tp.pending_type" . (is_numeric($p['sort']) ? ", d.value" : '') . (is_numeric($search['field']) ? ", search.value" : '') . " FROM " . $dbo->table['subscribers'] . " s\n\t\t\tLEFT JOIN " . $dbo->table['subscriber_pending'] . " p ON (s.subscriber_id = p.subscriber_id) " . (is_numeric($p['sort']) ? "LEFT JOIN (SELECT * FROM " . $dbo->table['subscriber_data'] . " WHERE field_id = " . (int) $p['sort'] . " ) AS d" . " ON (s.subscriber_id = d.subscriber_id)" : '') . (is_numeric($search['field']) ? "LEFT JOIN (SELECT value FROM " . $dbo->table['subscriber_data'] . " WHERE field_id = " . (int) $search['field'] . " ) AS search" . " ON (s.subscriber_id = search.subscriber_id)" : '') . " WHERE\n\t\t\t\t1\n\t\t\t\t[AND s.subscriber_id IN(%C)]\n\t\t\t\t[AND s.status=%I]\n\t\t\t\t[AND s.email IN (%Q)]\n\t\t\t\t[AND %S LIKE '%%S%']\n\t\t\t\t[ORDER BY %S] [%S]\n\t\t\t\t[LIMIT %I, %I]";
// Check if we're sorting against a field.
// If so, sort against the "value" column select.
// If it's a numeric field, cast the value (string) as an Integer by the DBE for proper sorting.
if (is_numeric($p['sort'])) {
Pommo::requireOnce($pommo->_baseDir . 'inc/helpers/fields.php');
$numericFields = PommoField::getByType(array('date', 'number'));
$p['sort'] = in_array($p['sort'], $numericFields) ? 'CAST(value as SIGNED)' : 'value';
}
// If we're searching/filtering, generate the proper SQL
$searchSQL = NULL;
if (!empty($search['field']) && !empty($search['string'])) {
// make MySQL LIKE() compliant
$search['string'] = addcslashes($search['string'], '%_');
$search['field'] = is_numeric($search['field']) ? 'search.value' : 's.' . $search['field'];
}
$query = $dbo->prepare($query, array($p['id'], $p['status'], $p['email'], $search['field'], $search['string'], $p['sort'], $p['order'], $p['offset'], $p['limit']));
while ($row = $dbo->getRows($query)) {
$o[$row['subscriber_id']] = empty($row['pending_code']) ? PommoSubscriber::makeDB($row) : PommoSubscriber::makeDB($row, TRUE);
}
// fetch data
if (!empty($o)) {
// get any date fields for conversion. We can't use the MySQL 4.1/5
// engine, as it doesn't support negative timestamps... !!!
Pommo::requireOnce($pommo->_baseDir . 'inc/helpers/fields.php');
$dates = PommoField::getByType('date');
$query = "\n\t\t\t\tSELECT\n\t\t\t\t\tfield_id,\n\t\t\t\t\tvalue,\n\t\t\t\t\tsubscriber_id\n\t\t\t\tFROM\n\t\t\t\t\t" . $dbo->table['subscriber_data'] . "\n\t\t\t\tWHERE\n\t\t\t\t\tsubscriber_id IN(%c)";
$query = $dbo->prepare($query, array(array_keys($o)));
while ($row = $dbo->getRows($query)) {
$o[$row['subscriber_id']]['data'][$row['field_id']] = in_array($row['field_id'], $dates) ? PommoHelper::timeToStr($row['value']) : $row['value'];
}
}
return $o;
}
