function testSelectWithChainedFilterParameters()
{
$query = new SQLQuery();
$query->select(array("Name", "Meta"))->from("MyTable");
$query->where("Name = 'Name'")->where("Meta", "Test")->where("Beta", "!=", "Gamma");
$this->assertEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test') AND (Beta != 'Gamma')", $query->sql());
}
/**
* Selects record based on WHERE Clause.
*
* @param array $where Parameters to use as WHERE clause condition
* @param array [$fields = array('*')] fields to select from the query
*
* @return array of calling class data type
*/
public static function selectWhere($where, $fields = array('*'))
{
//Initialize parameters
static::initialize();
//Run query
$result = SQLQuery::select(static::$tableName, static::$classNS, static::$DBH, $fields, $where);
//Return result
return $result;
}
function get_user($user_id) {
$DB = new SQLQuery();
$DB->chooseTable(DB_USERS_TABLE);
$user = $DB->select($user_id);
if (empty($user)) {
return NULL;
} else {
return $user[0]["users"];
}
}
function testSelectWithPredicateFilters()
{
$query = new SQLQuery();
$query->select(array("Name"))->from("SQLQueryTest_DO");
$match = new ExactMatchFilter("Name", "Value");
$match->setModel('SQLQueryTest_DO');
$match->apply($query);
$match = new PartialMatchFilter("Meta", "Value");
$match->setModel('SQLQueryTest_DO');
$match->apply($query);
$this->assertEquals("SELECT Name FROM SQLQueryTest_DO WHERE (`SQLQueryTest_DO`.`Name` = 'Value') AND (`SQLQueryTest_DO`.`Meta` LIKE '%Value%')", $query->sql());
}
protected function purgeUnconfirmedRegistrations()
{
$query = new SQLQuery();
$conn = DB::getConn();
$query->select('"EventRegistration"."ID"');
$query->from('"EventRegistration"');
$query->innerJoin('CalendarDateTime', '"TimeID" = "DateTime"."ID"', 'DateTime');
$query->innerJoin('CalendarEvent', '"DateTime"."EventID" = "Event"."ID"', 'Event');
$query->innerJoin('RegisterableEvent', '"Event"."ID" = "Registerable"."ID"', 'Registerable');
$query->where('"Registerable"."ConfirmTimeLimit" > 0');
$query->where('"Status"', 'Unconfirmed');
$created = $conn->formattedDatetimeClause('"EventRegistration"."Created"', '%U');
$query->where(sprintf('%s < %s', $created . ' + "Registerable"."ConfirmTimeLimit"', time()));
if ($ids = $query->execute()->column()) {
$count = count($ids);
DB::query(sprintf('UPDATE "EventRegistration" SET "Status" = \'Canceled\' WHERE "ID" IN (%s)', implode(', ', $ids)));
} else {
$count = 0;
}
echo "{$count} unconfirmed registrations were canceled.\n";
}
function testDbDatetimeDifference()
{
if ($this->supportDbDatetime) {
$clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec');
$clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -15, '15 seconds ago - now');
$clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead');
$query = new SQLQuery();
$query->select($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"') . ' AS "test"')->from('"DbDateTimeTest_Team"')->limit(1);
$result = $query->execute()->value();
$lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
$created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
$this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc');
}
}
/**
* Return the number of rows in this query if the limit were removed. Useful in paged data sets.
* @return int
*/
public function unlimitedRowCount($column = null)
{
// we can't clear the select if we're relying on its output by a HAVING clause
if (count($this->having)) {
$records = $this->execute();
return $records->numRecords();
}
$clone = clone $this;
$clone->limit = null;
$clone->orderby = null;
// Choose a default column
if ($column == null) {
if ($this->groupby) {
$countQuery = new SQLQuery();
$countQuery->select("count(*)");
$countQuery->from = array('(' . $clone->sql() . ') all_distinct');
return $countQuery->execute()->value();
} else {
$clone->setSelect(array("count(*)"));
}
} else {
$clone->setSelect(array("count({$column})"));
}
$clone->setGroupBy(array());
return $clone->execute()->value();
}
/**
* Get the latest members
*
* @param int $limit Number of members to return
* @return DataObjectSet
*/
function getLatestMembers($limit = 1)
{
$filter = '';
if ($forumGroup = DataObject::get_one('Group', "\"Code\" = 'forum-members'")) {
$filter = "\"GroupID\" = '" . $forumGroup->ID . "' ";
}
if ($adminGroup = DataObject::get_one('Group', "(\"Code\" = 'administrators' OR \"Code\" = 'Administrators')")) {
$filter .= $filter ? "OR \"GroupID\" = '" . $adminGroup->ID . "'" : "\"GroupID\" = '" . $adminGroup->ID . "'";
}
// do a lookup on the specific Group_Members table for the latest member ID
if ($filter) {
$limit = (int) $limit;
$query = new SQLQuery();
$query->select('"MemberID"')->from('"Group_Members"')->where($filter)->orderby('"ID" DESC')->limit($limit);
$latestMemberIDs = $query->execute()->column();
if ($latestMemberIDs) {
$members = new DataObjectSet();
foreach ($latestMemberIDs as $key => $id) {
$members->push(DataObject::get_by_id('Member', $id));
}
return $members;
}
}
return DataObject::get("Member", "", "ID DESC", "", $limit);
}
/**
* Returns the number of tickets available for an event time.
*
* @param RegisterableDateTime $time
* @param int $excludeId A registration ID to exclude from calculations.
* @return array
*/
public function getAvailableForDateTime(RegisterableDateTime $time, $excludeId = null)
{
if ($this->StartType == 'Date') {
$start = strtotime($this->StartDate);
} else {
$start = $time->getStartTimestamp();
$start = sfTime::subtract($start, $this->StartDays, sfTime::DAY);
$start = sfTime::subtract($start, $this->StartHours, sfTime::HOUR);
$start = sfTime::subtract($start, $this->StartMins, sfTime::MINUTE);
}
if ($start >= time()) {
return array('available' => false, 'reason' => 'Tickets are not yet available.', 'available_at' => $start);
}
if ($this->EndType == 'Date') {
$end = strtotime($this->EndDate);
} else {
$end = $time->getStartTimestamp();
$end = sfTime::subtract($end, $this->EndDays, sfTime::DAY);
$end = sfTime::subtract($end, $this->EndHours, sfTime::HOUR);
$end = sfTime::subtract($end, $this->EndMins, sfTime::MINUTE);
}
if (time() >= $end) {
return array('available' => false, 'reason' => 'Tickets are no longer available.');
}
if (!($quantity = $this->Available)) {
return array('available' => true);
}
$booked = new SQLQuery();
$booked->select('SUM("Quantity")');
$booked->from('"EventRegistration_Tickets"');
$booked->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
if ($excludeId) {
$booked->where('"EventRegistration"."ID"', '<>', $excludeId);
}
$booked->where('"Status"', '<>', 'Canceled');
$booked->where('"EventTicketID"', $this->ID);
$booked->where('"EventRegistration"."TimeID"', $time->ID);
$booked = $booked->execute()->value();
if ($booked < $quantity) {
return array('available' => $quantity - $booked);
} else {
return array('available' => false, 'reason' => 'All tickets have been booked.');
}
}
/**
* Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed
* in the search.
*
* @return Array
*/
function pagesIncluded() {
$ids = array();
$q = new SQLQuery();
$q->select(array('"ID"','"ParentID"'))
->from('"SiteTree"');
$where = array();
$SQL_params = Convert::raw2sql($this->params);
foreach($SQL_params as $name => $val) {
switch($name) {
// Match against URLSegment, Title, MenuTitle & Content
case 'Term':
if($val) $where[] = "\"URLSegment\" LIKE '%$val%' OR \"Title\" LIKE '%$val%' OR \"MenuTitle\" LIKE '%$val%' OR \"Content\" LIKE '%$val%'";
break;
// Match against date
case 'LastEditedFrom':
if($val) $where[] = "\"LastEdited\" >= '$val'";
break;
case 'LastEditedTo':
if($val) $where[] = "\"LastEdited\" <= '$val'";
break;
// Match against exact ClassName
case 'ClassName':
if($val && $val != 'All') {
$where[] = "\"ClassName\" = '$val'";
}
break;
default:
// Partial string match against a variety of fields
if(!empty($val) && singleton("SiteTree")->hasDatabaseField($name)) {
$where[] = "\"$name\" LIKE '%$val%'";
}
}
}
$q->where(empty($where) ? '' : '(' . implode(') AND (',$where) . ')');
foreach($q->execute() as $row) {
$ids[] = array('ID'=>$row['ID'],'ParentID'=>$row['ParentID']);
}
return $ids;
}
/**
* Returns the overall number of places remaining at this event, TRUE if
* there are unlimited places or FALSE if they are all taken.
*
* @param int $excludeId A registration ID to exclude from calculations.
* @return int|bool
*/
public function getRemainingCapacity($excludeId = null)
{
if (!$this->Capacity) {
return true;
}
$taken = new SQLQuery();
$taken->select('SUM("Quantity")');
$taken->from('EventRegistration_Tickets');
$taken->leftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
if ($excludeId) {
$taken->where('"EventRegistration"."ID"', '<>', $excludeId);
}
$taken->where('"Status"', '<>', 'Canceled');
$taken->where('"EventRegistration"."TimeID"', $this->ID);
$taken = $taken->execute()->value();
return $this->Capacity >= $taken ? $this->Capacity - $taken : false;
}
