/**
* Return the {@link SQLQuery} that provides your report data.
*/
function sourceQuery($params)
{
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('CalendarEvent');
$sqlQuery->selectField('Date');
$sqlQuery->selectField('CalendarEvent.Title', 'Event');
$sqlQuery->selectField('StartTime');
$sqlQuery->selectField('EndTime');
$sqlQuery->addInnerJoin('CalendarEventDate', '"CalendarEventDate"."CalendarEventID" = "CalendarEvent"."ID"');
if (isset($params['DateFrom'])) {
$fromDate = new SS_DateTime('FromDate');
$fromDate->setValue($params['DateFrom']);
$sqlQuery->addWhere(array('Date >= ?' => $fromDate->Format("Y-m-d")));
}
if (isset($params['DateTo'])) {
$toDate = new SS_DateTime('ToDate');
$toDate->setValue($params['DateTo']);
$sqlQuery->addWhere(array('Date <= ?' => $toDate->Format("Y-m-d")));
}
if (isset($params['PrivateBookings'])) {
if ($params['PrivateBookings'] == 'Private') {
$sqlQuery->addWhere('Private = 1');
} elseif ($params['PrivateBookings'] == 'Public') {
$sqlQuery->addWhere('Private = 0');
}
}
$sqlQuery->addOrderBy('Date');
$sqlQuery->addOrderBy('Event');
$sqlQuery->addOrderBy('StartTime');
$sqlQuery->addOrderBy('EndTime');
return $sqlQuery;
}
public function augmentSQL(SQLQuery &$query)
{
if (empty($query->getSelect()) || $query->getDelete() || in_array("COUNT(*)", $query->getSelect()) || in_array("count(*)", $query->getSelect())) {
return;
}
if (!$query->getWhere() || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->getWhere()[0])) {
$c = Controller::curr();
if (!startsWith($c->class, "CMS") && !startsWith($c->class, "Model") && $c->getAction() != "update") {
$className = $this->owner->ClassName;
$query->addWhere("{$className}.Published=1");
// $query->addWhere( "DATE({$className}.PublicationDate)<=DATE(NOW())" );
$query->addWhere("({$className}.ExpirationDate IS NULL) OR ({$className}.ExpirationDate IS NOT NULL AND DATE({$className}.ExpirationDate)>=DATE(NOW()))");
}
}
}
private function getCSVColumns($flexi)
{
$columns = array('SubmittedBy' => 'Submitted By', 'IPAddress' => 'IP Address', 'Created' => 'Created');
$sql = new SQLQuery();
$sql->setFrom('FlexiFormSubmissionValue');
$sql->setSelect('"FlexiFormSubmissionValue"."Name"');
$sql->addLeftJoin('FlexiFormSubmission', '"FlexiFormSubmissionValue"."SubmissionID" = "FlexiFormSubmission"."ID"');
$sql->addWhere('"FlexiFormSubmission"."FlexiFormID" = ' . $flexi->ID);
$sql->addWhere('"FlexiFormSubmission"."FlexiFormClass" = \'' . $flexi->class . '\'');
$sql->setDistinct(true);
foreach ($sql->execute() as $row) {
$columns['Values.' . $row['Name']] = $row['Name'];
}
return $columns;
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
$where = $query->getWhere();
if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) {
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$query->getDelete() && !$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if ($query->filtersOnID()) {
return;
}
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be custommenu...
if (strpos($tableName, 'CustomMenu') === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
public function augmentSQL(SQLQuery &$query)
{
if (static::enabled()) {
$query->addWhere(self::VerificationDateName . ' is not null');
}
parent::augmentSQL($query);
}
public function ChartData()
{
$chartData = array();
$list = ArrayList::create(array());
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('Addon');
$sqlQuery->setSelect('Created');
$sqlQuery->selectField('COUNT(*)', 'CountInOneDay');
$sqlQuery->addWhere('"Created" >= DATE_SUB(NOW(), INTERVAL 30 DAY)');
$sqlQuery->addGroupBy('DATE(Created)');
$result = $sqlQuery->execute();
if (count($result)) {
foreach ($result as $row) {
$date = date('j M Y', strtotime($row['Created']));
if (!isset($chartData[$date])) {
$chartData[$date] = $row['CountInOneDay'];
}
}
}
if (count($chartData)) {
foreach ($chartData as $x => $y) {
$list->push(ArrayData::create(array('XValue' => $x, 'YValue' => $y)));
}
}
return $list;
}
/**
* Augment queries so that we don't fetch unpublished articles.
**/
public function augmentSQL(SQLQuery &$query)
{
$stage = Versioned::current_stage();
if ($stage == 'Live' || !Permission::check("VIEW_DRAFT_CONTENT")) {
$query->addWhere("PublishDate < '" . Convert::raw2sql(SS_Datetime::now()) . "'");
}
}
/**
* Update any requests to limit the results to the current site
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->where || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->where[0])) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be SiteTree or SiteTree_Live...
if (strpos($tableName, $this->owner->ClassName) === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
}
/**
* Append a WHERE clause to this query.
* There are two different ways of doing this:
*
* <code>
* // the entire predicate as a single string
* $query->where("\"Column\" = 'Value'");
*
* // multiple predicates as an array
* $query->where(array("\"Column\" = 'Value'", "\"Column\" != 'Value'"));
* </code>
*
* @param string|array $where Predicate(s) to set, as escaped SQL statements.
*/
public function where($filter)
{
if ($filter) {
$this->query->addWhere($filter);
}
return $this;
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$ctrl = null;
if (Controller::has_curr()) {
$ctrl = Controller::curr();
}
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
if ($ctrl && get_class(Controller::curr()) == 'Security') {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->filtersOnID()) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
$subsiteID = (int) Subsite::currentSubsiteID();
}
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
}
}
/**
* @param SQLQuery $query
* @param DataQuery $dataQuery
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
if (class_exists('Subsite')) {
$currentSubsiteID = Subsite::currentSubsiteID();
$query->addWhere("\"{$baseTable}\".\"SubsiteID\" = '{$currentSubsiteID}'");
}
}
/**
* Manipulates the SQL query
*
* @param SQLQuery &$query Query to manipulate
*
* @return void
*
* @author Sebastian Diel <*****@*****.**>
* @since 04.05.2012
*/
public function augmentSQL(SQLQuery &$query)
{
if (!$query->isJoinedTo($this->getLanguageClassName()) && !$query->getDelete()) {
$silvercartDefaultLocale = SilvercartConfig::Locale();
$query->addLeftJoin($this->getLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"%s\")", $this->getBaseClassName(), $this->getLanguageClassName(), $this->getRelationFieldName()));
$addToWhere = '';
if ($this->getBaseLanguageClassName() != $this->getLanguageClassName()) {
$query->addLeftJoin($this->getBaseLanguageClassName(), sprintf("(\"%s\".\"ID\" = \"%s\".\"ID\")", $this->getLanguageClassName(), $this->getBaseLanguageClassName()));
$addToWhere = sprintf("AND \"%s\".\"ID\" = \"%s\".\"ID\"", $this->getBaseLanguageClassName(), $this->getLanguageClassName());
}
if (SilvercartConfig::useDefaultLanguageAsFallback() && Translatable::get_current_locale() != $silvercartDefaultLocale && !empty($silvercartDefaultLocale)) {
$query->addWhere(sprintf("\"%s\".\"Locale\" = IFNULL((%s), (%s)) %s", $this->getBaseLanguageClassName(), $this->getLocaleDependantSelect(Translatable::get_current_locale()), $this->getLocaleDependantSelect($silvercartDefaultLocale), $addToWhere));
} elseif (!empty($silvercartDefaultLocale)) {
$query->addWhere(sprintf("\"%s\".\"Locale\" = '%s' %s", $this->getBaseLanguageClassName(), Translatable::get_current_locale(), $addToWhere));
}
}
}
public function testSelectWithWhereClauseFilter()
{
$query = new SQLQuery();
$query->setSelect(array("Name", "Meta"));
$query->setFrom("MyTable");
$query->setWhere("Name = 'Name'");
$query->addWhere("Meta = 'Test'");
$this->assertEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test')", $query->sql());
}
/**
* Augment queries so that we don't fetch unpublished articles.
*
* @param SQLQuery $query
*/
public function augmentSQL(SQLQuery &$query)
{
$stage = Versioned::current_stage();
if (Controller::curr() instanceof LeftAndMain) {
return;
}
if ($stage == 'Live' || !Permission::check('VIEW_DRAFT_CONTENT')) {
$query->addWhere(sprintf('"PublishDate" < \'%s\'', Convert::raw2sql(SS_Datetime::now())));
}
}
public function Tags()
{
$select = array('Title');
$query = new SQLQuery($select, 'PostTag');
$query->selectField('count(PostTag.ID)', 'Number');
$query->selectField('PostTag.ID');
$query->addInnerjoin('MicroPost_Tags', 'PostTag.ID = MicroPost_Tags.PostTagID');
$date = date('Y-m-d H:i:s', strtotime('-1 month'));
$query->addWhere("MicroPost_Tags.Tagged > '{$date}'");
$query->addWhere('"PostTag"."Title" NOT LIKE \'SELF_TAG%\'');
$query->addGroupBy('PostTag.ID');
$query->setLimit(20);
$rows = $query->execute();
$tags = ArrayList::create();
foreach ($rows as $row) {
$data = new ArrayData($row);
$data->Link = Controller::join_links(TimelineController::URL_SEGMENT, '?tags=' . urlencode($data->Title));
$tags->push($data);
}
return $tags;
}
public function augmentSQL(SQLQuery &$query, DataQuery $dataQuery = null)
{
if ($this->owner->ID && !empty($this->owner->Locale)) {
$locale = $this->owner->Locale;
} else {
$locale = Translatable::get_current_locale();
}
if ($locale && Translatable::locale_filter_enabled()) {
$qry = sprintf('"ContentModule"."Locale" = \'%s\'', Convert::raw2sql($locale));
$query->addWhere($qry);
}
}
public function Articles()
{
$filterID = $this->ExcludeTag()->ID;
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('HailTag_Articles');
$sqlQuery->setSelect('HailTag_Articles.HailArticleID');
//$sqlQuery->addLeftJoin('HailTag_Articles','HailTag_Articles.HailArticleID = HailArticleID');
//$sqlQuery->addWhere('HailTag_Articles.HailTagID = ' . intval($this->Tag()->ID));
$sqlQuery->addWhere('HailTagID = ' . intval($filterID));
$map = $sqlQuery->execute()->map();
$articles_ids = array_keys($map);
$List = parent::Articles()->exclude('ID', $articles_ids);
return $List;
}
public function importPass()
{
$query = $this->getRemoteObjectsQuery();
$items = $this->task->query($query);
$itemsCount = $items->numRecords();
$this->task->message(" * Found {$itemsCount} items to import");
$total = 0;
$updated = 0;
$inserted = 0;
foreach ($items as $item) {
$this->task->progress(++$total, $itemsCount);
// Build select query for existing object
$values = array();
$select = new SQLQuery("ID", "\"{$this->tableName}\"");
foreach ($this->fields as $field => $class) {
$value = intval($item[$field]);
$values[] = $value;
$select->addWhere(sprintf("\"{$field}\" = %d", $value));
}
$values[] = $item['ID'];
// Check for existing record
if ($localID = DB::query($select->sql())->value()) {
// Update local many_many record instead of making new row
DB::query(sprintf('UPDATE "%s" SET "LegacyID" = %d WHERE "ID" = %d', $this->tableName, $item['ID'], $localID));
$updated++;
} else {
// Insert mapping into the many_many table
$insert = "INSERT INTO \"{$this->tableName}\" (";
$insert .= '"' . implode('", "', array_keys($this->fields)) . '"';
$insert .= ', LegacyID';
$insert .= ') VALUES (';
$insert .= implode(', ', $values);
$insert .= ')';
DB::query($insert);
$localID = DB::getGeneratedID($this->tableName);
$inserted++;
}
// Mark this relation as imported in the remote table
$conn = $this->task->getRemoteConnection();
$conn->query(sprintf('UPDATE "%s" SET "_ImportedID" = %d, "_ImportedDate" = NOW() WHERE "ID" = %d', $this->tableName, $localID, $item['ID']));
}
// Done!
$this->task->message(" * Result: {$inserted} added, {$updated} updated");
}
function getAdminPermissionSet(array &$res)
{
$companyId = $_REQUEST["CompanyId"];
if (isset($companyId) && is_numeric($companyId) && $companyId > 0) {
// user could be ccla admin of only one company and company must have at least one team set
$ccla_group = Group::get()->filter('Code', ICLAMember::CCLAGroupSlug)->first();
if (!$ccla_group) {
return;
}
$query_groups = new SQLQuery();
$query_groups->addSelect("GroupID");
$query_groups->addFrom("Company_Administrators");
$query_groups->addWhere("MemberID = {$this->owner->ID} AND CompanyID <> {$companyId} AND GroupID = {$ccla_group->ID} ");
$groups = $query_groups->execute()->keyedColumn();
$company = Company::get()->byID($companyId);
if (count($groups) === 0 && $company->isICLASigned()) {
array_push($res, 'CCLA_ADMIN');
}
}
}
private function checkBlogEntryPermissions()
{
$authorsId = array();
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('SiteTree_versions');
$sqlQuery->selectField('AuthorID');
$sqlQuery->addWhere('RecordID = ' . $this->ID);
$sqlQuery->setOrderBy('ID DESC');
$rawSQL = $sqlQuery->sql();
$result = $sqlQuery->execute();
foreach ($result as $row) {
$authorsId[] = $row['AuthorID'];
}
$sqlQuery->setDelete(true);
if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
return true;
} else {
return false;
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Filters are disabled globally
if (self::$disable) {
return;
}
// Filters are disabled for this query
if ($dataQuery->getQueryParam('SoftDeletable.filter') === false) {
return;
}
// Don't run on delete queries, since they are always tied to a specific ID.
if ($query->getDelete()) {
return;
}
// Don't run if querying by ID
if ($query->filtersOnID()) {
return;
}
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
$query->addWhere("\"{$tableName}\".\"Deleted\" IS NULL");
}
public function augmentSQL(SQLQuery &$query)
{
if (Config::inst()->forClass('Post')->allow_reading_spam) {
return;
}
$member = Member::currentUser();
$forum = $this->owner->Forum();
// Do Status filtering
if ($member && is_numeric($forum->ID) && $member->ID == $forum->Moderator()->ID) {
$filter = "\"Post\".\"Status\" IN ('Moderated', 'Awaiting')";
} else {
$filter = "\"Post\".\"Status\" = 'Moderated'";
}
$query->addWhere($filter);
// Filter out posts where the author is in some sort of banned / suspended status
$query->addInnerJoin("Member", "\"AuthorStatusCheck\".\"ID\" = \"Post\".\"AuthorID\"", "AuthorStatusCheck");
$authorStatusFilter = array(array('"AuthorStatusCheck"."ForumStatus"' => 'Normal'));
if ($member && $member->ForumStatus === 'Ghost') {
$authorStatusFilter[] = array('"Post"."AuthorID" = ?', $member->ID);
}
$query->addWhereAny($authorStatusFilter);
$query->setDistinct(false);
}
function exportCompanyData()
{
$params = $this->owner->getRequest()->getVars();
if (!isset($params['report_name']) || empty($params['report_name']) || !count($params['report_name'])) {
return $this->owner->httpError('412', 'missing required param report_name');
}
if (!isset($params['extension']) || empty($params['extension'])) {
return $this->owner->httpError('412', 'missing required param extension');
}
$report_name = isset($params['report_name']) ? $params['report_name'] : '';
$fields = isset($params['fields']) ? $params['fields'] : array();
$ext = $params['extension'];
$query = new SQLQuery();
if ($report_name) {
switch ($report_name) {
case 'sponsorship_type':
$query->setFrom('Company');
$query->addLeftJoin('SummitSponsorPage_Companies', 'SummitSponsorPage_Companies.CompanyID = Company.ID');
$query->addLeftJoin('Summit', 'Summit.ID = SummitSponsorPage_Companies.SummitID');
$query->addWhere('Summit.Active', '1');
$fields = array_merge($fields, array('Sponsorship' => 'SummitSponsorPage_Companies.SponsorshipType', 'Summit ID' => 'Summit.ID'));
$query->setSelect($fields);
$query->addOrderBy('SummitSponsorPage_Companies.SponsorshipType');
$filename = "Sponsorship_Levels_" . date('Ymd') . "." . $ext;
break;
case 'member_level':
$query->setFrom('Company');
array_push($fields, 'Company.MemberLevel');
$query->setSelect($fields);
$filename = "Foundation_Levels_" . date('Ymd') . "." . $ext;
break;
case 'users_roles':
$query->setFrom('Company');
$query->addInnerJoin('Company_Administrators', 'Company_Administrators.CompanyID = Company.ID');
$query->addLeftJoin('Member', 'Member.ID = Company_Administrators.MemberID');
$query->addLeftJoin('Group', 'Group.ID = Company_Administrators.GroupID');
array_push($fields, 'Group.Title');
$query->setSelect($fields);
$query->addOrderBy('Company.Name');
$filename = "User_Roles_" . date('Ymd') . "." . $ext;
break;
case 'affiliates':
$query->setFrom('Org');
$query->addLeftJoin('Affiliation', 'Affiliation.OrganizationID = Org.ID');
$query->addLeftJoin('Member', 'Member.ID = Affiliation.MemberID');
$fields = array_merge($fields, array('Is Current' => 'Affiliation.Current', 'Job Title' => 'Affiliation.JobTitle'));
$query->setSelect($fields);
$query->addOrderBy('Org.Name');
$filename = "Employees_Affiliates_" . date('Ymd') . "." . $ext;
break;
case 'deployments':
$query->setFrom('Org');
$query->addInnerJoin('Deployment', 'Deployment.OrgID = Org.ID');
$custom_fields = array('Creation' => 'Deployment.Created', 'Edited' => 'Deployment.LastEdited', 'Label' => 'Deployment.Label', 'Is Public' => 'Deployment.IsPublic');
$fields = array_merge($fields, $custom_fields);
$query->setSelect($fields);
$query->selectField("CONCAT('http://openstack.org/sangria/DeploymentDetails/',Deployment.ID)", "Link");
$query->addOrderBy('Org.Name');
$filename = "Deployments_" . date('Ymd') . "." . $ext;
break;
case 'deployment_surveys':
$query->setFrom('Org');
$query->addLeftJoin('DeploymentSurvey', 'DeploymentSurvey.OrgID = Org.ID');
$query->addLeftJoin('Member', 'DeploymentSurvey.MemberID = Member.ID');
$custom_fields = array('Creation' => 'DeploymentSurvey.Created', 'Edited' => 'DeploymentSurvey.LastEdited', 'Title' => 'DeploymentSurvey.Title', 'City' => 'DeploymentSurvey.PrimaryCity', 'State' => 'DeploymentSurvey.PrimaryState', 'Country' => 'DeploymentSurvey.PrimaryCountry', 'Org Size' => 'DeploymentSurvey.OrgSize', 'Is Group Member' => 'DeploymentSurvey.UserGroupMember', 'Group Name' => 'DeploymentSurvey.UserGroupName', 'Ok to Contact' => 'DeploymentSurvey.OkToContact');
//insert custom fields after org fields
$pos = -1;
foreach ($fields as $field) {
$pos++;
if (strpos($field, 'Org') !== false) {
continue;
} else {
array_splice($fields, $pos, 0, $custom_fields);
break;
}
}
$query->setSelect($fields);
$query->selectField("CONCAT('http://openstack.org/sangria/SurveyDetails/',DeploymentSurvey.ID)", "Link");
$filename = "Deployment_Surveys" . date('Ymd') . "." . $ext;
break;
case 'speakers':
$query->setFrom('PresentationSpeaker');
$query->addLeftJoin('Affiliation', 'Affiliation.MemberID = PresentationSpeaker.MemberID');
$query->addLeftJoin('Org', 'Affiliation.OrganizationID = Org.ID');
$query->addLeftJoin('Summit', 'Summit.ID = PresentationSpeaker.SummitID');
$custom_fields = array('Speaker Name' => 'PresentationSpeaker.FirstName', 'Speaker Surname' => 'PresentationSpeaker.LastName', 'Summit' => 'Summit.Name');
$fields = array_merge($fields, $custom_fields);
$query->setSelect($fields);
$filename = "Speakers_" . date('Ymd') . "." . $ext;
break;
}
}
//die($query->sql());
$result = $query->execute();
$delimiter = $ext == 'xls' ? "\t" : ",";
return CSVExporter::getInstance()->export($filename, $result, $delimiter);
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (!FluentOldPageRedirectFix::$disableSkipIDFilter) {
// Skip ID based filters
if ($query->filtersOnID()) {
return;
}
}
// Skip filter in the CMS, unless filtering is explicitly turned on
$filterAdmin = $dataQuery->getQueryParam('Fluent.FilterAdmin');
if (!$filterAdmin) {
$isFrontend = $dataQuery->getQueryParam('Fluent.IsFrontend');
if ($isFrontend === null) {
$isFrontend = Fluent::is_frontend();
}
if (!$isFrontend) {
return;
}
}
// Add filter for locale
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
$query->addWhere("\"{$this->ownerBaseClass}\".\"LocaleFilter_{$locale}\" = 1");
}
/**
* 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->setSelect('SUM("Quantity")');
$taken->setFrom('EventRegistration_Tickets');
$taken->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
if ($excludeId) {
$taken->addWhere('"EventRegistration"."ID" <>' . (int) $excludeId);
}
$taken->addWhere('"Status" <> \'Canceled\'');
$taken->addWhere('"EventRegistration"."TimeID" =' . (int) $this->ID);
$taken = $taken->execute()->value();
return $this->Capacity >= $taken ? $this->Capacity - $taken : false;
}
public function augmentSQL(SQLQuery &$query)
{
$stage = Versioned::current_stage();
if (Controller::curr() instanceof LeftAndMain) {
return;
}
if ($stage == 'Live' || !Permission::check('VIEW_DRAFT_CONTENT')) {
$query->addWhere('("Embargo" IS NULL OR "Embargo" < NOW()) AND ("Expiry" IS NULL OR "Expiry" > NOW())');
}
}
/**
* Returns the number of tickets available for an event time.
*
* @param RegistrableDateTime $time
* @param int $excludeId A registration ID to exclude from calculations.
* @return array
*/
public function getAvailableForDateTime(RegistrableDateTime $time, $excludeId = null)
{
if ($this->StartType == 'Date') {
$start = strtotime($this->StartDate);
} else {
$start = $time->getStartDateTime()->getTimestamp();
$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->getStartDateTime()->getTimestamp();
$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->setSelect('SUM("Quantity")');
$booked->setFrom('"EventRegistration_Tickets"');
$booked->addLeftJoin('EventRegistration', '"EventRegistration"."ID" = "EventRegistrationID"');
if ($excludeId) {
$booked->addWhere('"EventRegistration"."ID"', '<>', $excludeId);
}
$booked->addWhere('"Status"', '<>', 'Canceled');
$booked->addWhere('"EventTicketID"', $this->ID);
$booked->addWhere('"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.');
}
}
/**
* Returns a number of topseller products.
*
* @return ArrayList
*
* @author Sebastian Diel <*****@*****.**>,
* Sascha Koehler <*****@*****.**>
* @since 03.02.2015
*/
public function Elements()
{
if (!$this->numberOfProductsToShow) {
$this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
}
$cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
$cache = SS_Cache::factory($cachekey);
$cachedResult = $cache->load($cachekey);
if ($cachedResult) {
$result = unserialize($result);
} else {
$products = array();
$sqlQuery = new SQLQuery();
$sqlQuery->selectField('SOP.SilvercartProductID');
$sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
$sqlQuery->addFrom('SilvercartOrderPosition SOP');
$sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
$sqlQuery->addWhere('SP.isActive = 1');
$sqlQuery->addGroupBy('SOP.SilvercartProductID');
$sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
$sqlQuery->setLimit($this->numberOfProductsToShow);
$sqlResult = $sqlQuery->execute();
foreach ($sqlResult as $row) {
$product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
$product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
$products[] = $product;
}
$result = new ArrayList($products);
}
return $result;
}
/**
* Filter out all unpublished items
* @param SQLQuery $query
*/
function augmentSQL(SQLQuery &$query)
{
if (Controller::curr() != 'CMSPageEditController') {
$query->addWhere("Published=1");
}
}
