/**
* Select the given fields from the given table.
*
* @param String $table Unquoted table name (will be escaped automatically)
* @param Array $fields Database column names (will be escaped automatically)
*/
public function selectFromTable($table, $fields)
{
$table = Convert::raw2sql($table);
$fieldExpressions = array_map(create_function('$item', "return '\"{$table}\".\"' . Convert::raw2sql(\$item) . '\"';"), $fields);
$this->query->setSelect($fieldExpressions);
return $this;
}
function testSelectWithChainedFilterParameters()
{
$query = new SQLQuery();
$query->setSelect(array("Name", "Meta"))->setFrom("MyTable");
$query->setWhere("Name = 'Name'")->addWhere("Meta = 'Test'")->addWhere("Beta != 'Gamma'");
$this->assertEquals("SELECT Name, Meta FROM MyTable WHERE (Name = 'Name') AND (Meta = 'Test') AND (Beta != 'Gamma')", $query->sql());
}
public function __construct($controller, $name = "VariationForm")
{
parent::__construct($controller, $name);
$product = $controller->data();
$farray = array();
$requiredfields = array();
$attributes = $product->VariationAttributeTypes();
foreach ($attributes as $attribute) {
$attributeDropdown = $attribute->getDropDownField(_t('VariationForm.ChooseAttribute', "Choose {attribute} …", '', array('attribute' => $attribute->Label)), $product->possibleValuesForAttributeType($attribute));
if ($attributeDropdown) {
$farray[] = $attributeDropdown;
$requiredfields[] = "ProductAttributes[{$attribute->ID}]";
}
}
$fields = FieldList::create($farray);
if (self::$include_json) {
$vararray = array();
$query = $query2 = new SQLQuery();
$query->setSelect('ID')->setFrom('ProductVariation')->addWhere(array('ProductID' => $product->ID));
if (!Product::config()->allow_zero_price) {
$query->addWhere('"Price" > 0');
}
foreach ($query->execute()->column('ID') as $variationID) {
$query2->setSelect('ProductAttributeValueID')->setFrom('ProductVariation_AttributeValues')->setWhere(array('ProductVariationID' => $variationID));
$vararray[$variationID] = $query2->execute()->keyedColumn();
}
$fields->push(HiddenField::create('VariationOptions', 'VariationOptions', json_encode($vararray)));
}
$fields->merge($this->Fields());
$this->setFields($fields);
$requiredfields[] = 'Quantity';
$this->setValidator(VariationFormValidator::create($requiredfields));
$this->extend('updateVariationForm');
}
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;
}
/**
* Select the given fields from the given table.
*
* @param String $table Unquoted table name (will be escaped automatically)
* @param Array $fields Database column names (will be escaped automatically)
*/
public function selectFromTable($table, $fields)
{
$fieldExpressions = array_map(function ($item) use($table) {
return "\"{$table}\".\"{$item}\"";
}, $fields);
$this->query->setSelect($fieldExpressions);
return $this;
}
public function Vendors()
{
$query = new SQLQuery();
$result = new ArrayList();
$query->setSelect('"AddonVendor"."Name"')->selectField('COUNT("Addon"."ID")' . 'Count')->setFrom('"AddonVendor"')->addLeftJoin('Addon', '"Addon"."VendorID" = "AddonVendor"."ID"')->setGroupBy('"AddonVendor"."ID"')->setOrderBy(array('"Count"' => 'DESC', '"Name"' => 'ASC'));
foreach ($query->execute() as $row) {
$link = Controller::join_links(Director::baseURL(), 'add-ons', $row['Name']);
$result->push(new ArrayData($row + array('Link' => $link)));
}
return $result;
}
public function Tags()
{
$query = new SQLQuery();
$result = new ArrayList();
$query->setSelect('"AddonKeyword"."ID", "Name"')->selectField('COUNT("AddonKeywordID")', 'Count')->setFrom('AddonKeyword')->addLeftJoin('Addon_Keywords', '"AddonKeywordID" = "AddonKeyword"."ID"')->setGroupBy('"ID"')->setOrderBy(array('"Count"' => 'DESC', '"Name"' => 'ASC'));
foreach ($query->execute() as $row) {
$link = Controller::join_links(Director::baseURL(), 'add-ons', '?' . http_build_query(array('tags[]' => $row['Name'])));
$result->push(new ArrayData($row + array('Link' => $link)));
}
return $result;
}
public function DDLValues()
{
$selected_values = array();
$owner = $_REQUEST["SurveyQuestionTemplateID"];
if (isset($owner)) {
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect("ValueID");
$sqlQuery->setFrom("SurveyQuestionTemplate_DependsOn");
$sqlQuery->setWhere("SurveyQuestionTemplateID = {$owner} AND ChildID = {$this->ID}");
$selected_values = $sqlQuery->execute()->keyedColumn();
}
return new MultiDropdownField("Values_{$this->ID}", "Values_{$this->ID}", $this->Rows()->map("ID", "Value"), $selected_values);
}
/**
* @return Array Map of Snippet Folder IDs
*/
public function snippetFoldersIncluded()
{
$ids = array();
$q = new SQLQuery();
$q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
if (!empty($this->creator)) {
$q->setWhereAny(array("\"CreatorID\"=" . intval($this->creator)));
}
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['FolderID']);
}
return $ids;
}
/**
* @return Array Map of Snippet Folder IDs
*/
public function snippetFoldersIncluded()
{
$ids = array();
$q = new SQLQuery();
$q->setSelect(array('"Snippet"."ID"', '"Snippet"."FolderID"'))->setFrom('"Snippet"');
if (!empty($this->tag)) {
$SQL_val = Convert::raw2sql($this->tag);
$q->setWhereAny(array("\"Tags\" LIKE '{$SQL_val},%' OR \"Tags\" LIKE '%,{$SQL_val}' OR \"Tags\" LIKE '%,{$SQL_val},%' OR \"Tags\" LIKE '{$SQL_val}'"));
}
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['FolderID']);
}
return $ids;
}
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;
}
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;
}
public function getDDLLogoSize()
{
$size = null;
$pageId = Convert::raw2sql($_REQUEST["PageId"]);
if (isset($pageId)) {
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect("LogoSize");
$sqlQuery->setFrom("SummitSponsorPage_Companies");
$sqlQuery->setWhere("CompanyID={$this->ID} AND SummitSponsorPageID={$pageId}");
$size = $sqlQuery->execute()->value();
if (is_null($size)) {
$size = 'None';
}
}
$sizes = array('Small' => 'Small', 'Medium' => 'Medium', 'Large' => 'Large', 'Big' => 'Big', 'None' => '--NONE--');
return new DropdownField("LogoSize_{$this->ID}", "LogoSize_{$this->ID}", $sizes, $size);
}
/**
* Test passing in a LIMIT with OFFSET clause string.
*/
public function testLimitSetFromClauseString()
{
$query = new SQLQuery();
$query->setSelect('*');
$query->setFrom('"SQLQueryTest_DO"');
$query->setLimit('20 OFFSET 10');
$limit = $query->getLimit();
$this->assertEquals(20, $limit['limit']);
$this->assertEquals(10, $limit['start']);
}
/**
* 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->setSelect("count(*)");
$countQuery->setFrom('(' . $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();
}
/**
* 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.');
}
}
public function getDDLAdminSecurityGroup()
{
$groups = array();
$companyId = $_REQUEST["CompanyId"];
if (isset($companyId)) {
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect("GroupID");
$sqlQuery->setFrom("Company_Administrators");
$sqlQuery->setWhere("MemberID={$this->owner->ID} AND CompanyID={$companyId}");
$groups = $sqlQuery->execute()->keyedColumn();
}
$sql_query_groups = new SQLQuery();
$permissions = "'" . implode("', '", $this->getAdminPermissionSet()) . "'";
$sql_query_groups->setSelect(array('G.ID', 'G.Title'));
$sql_query_groups->setFrom("`Group` G INNER JOIN ( SELECT DISTINCT(GroupID) FROM `Permission` WHERE `Code` IN ({$permissions})) PG ON PG.GroupID=G.ID");
$company_security_groups = $sql_query_groups->execute()->map();
return new MultiDropdownField("AdminSecurityGroup_{$this->owner->ID}", "AdminSecurityGroup_{$this->owner->ID}", $company_security_groups, $groups);
}
/**
* @return DataList
*/
private function getAllowedDependants()
{
$steps_query = new SQLQuery();
$steps_query->setSelect("ID");
$steps_query->setFrom("SurveyStepTemplate");
$high_order = $this->Step()->order();
$current_survey_id = $this->Step()->SurveyTemplateID;
$steps_query->setWhere("SurveyTemplateID = {$current_survey_id} AND `Order` <= {$high_order} ");
$steps_query->setOrderBy('`Order`', 'ASC');
$current_step_ids = $steps_query->execute()->keyedColumn();
return SurveyQuestionTemplate::get()->filter(array('StepID' => $current_step_ids));
}
/**
* This is to migrate existing newsitems to the new release with the new relational method.
* It is forward-non-destructive.
* Only run if there is a column NewsHolderPageID
* @todo This needs a rewrite, could be done with less queries with an add Items to Page instead of the current situation.
*/
private function migratePages()
{
$existquery = "SHOW COLUMNS FROM `News` LIKE 'NewsHolderPageID';";
/** @var DB $exists */
$exists = DB::query($existquery);
if ($count = $exists->numRecords()) {
/** @var SQLQuery $query */
$query = new SQLQuery();
$query->setSelect(array('ID', 'NewsHolderPageID'))->setFrom('News');
$newsitems = $query->execute();
foreach ($newsitems as $newsitem) {
if ($newsitem['NewsHolderPageID'] && NewsHolderPage::get()->byID($newsitem['NewsHolderPageID'])) {
News::get()->byID($newsitem['ID'])->NewsHolderPages()->add($newsitem['NewsHolderPageID']);
}
}
}
}
/**
* 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->setSelect(array('"ID"', '"ParentID"'))->setFrom('"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
// Match against date
case 'LastEditedFrom':
if ($val) {
$where[] = "\"LastEdited\" >= '{$val}'";
}
break;
case 'LastEditedTo':
if ($val) {
$where[] = "\"LastEdited\" <= '{$val}'";
}
break;
// Match against exact ClassName
// 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->setWhere(empty($where) ? '' : '(' . implode(') AND (', $where) . ')');
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']);
}
return $ids;
}
function exportUsersPerRegion()
{
$params = $this->owner->getRequest()->getVars();
if (!isset($params['countries']) || empty($params['countries'])) {
return $this->owner->httpError('412', 'missing required param countries');
}
if (!isset($params['members']) || empty($params['members'])) {
return $this->owner->httpError('412', 'missing required param members');
}
$countries = $params['countries'];
$members = $params['members'];
$join_members = '';
$join_countries = '';
if (!count($countries)) {
return $this->owner->httpError('412', 'missing required param countries');
} else {
foreach ($countries as $country) {
$join_countries .= "'" . $country . "',";
}
}
$join_countries = rtrim($join_countries, ",");
if (!count($members)) {
return $this->owner->httpError('412', 'missing required param members');
} else {
foreach ($members as $member) {
$join_members .= "'" . $member . "',";
}
}
$join_members = rtrim($join_members, ",");
$query = new SQLQuery();
$select_fields = array('Member.FirstName', 'Member.Surname', 'Member.Email', 'Member.City', 'Member.State', 'Member.Country');
$query->setFrom('Member');
$query->setSelect($select_fields);
$query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
$query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code IN (" . $join_members . ")");
$query->setWhere("Member.Country IN (" . $join_countries . ")");
$query->setOrderBy('SurName,FirstName');
$result = $query->execute();
$data = array();
foreach ($result as $row) {
$member = array('FirstName' => $row['FirstName'], 'Surname' => $row['Surname'], 'Email' => $row['Email'], 'City' => $row['City'], 'State' => $row['State'], 'Country' => CountryCodes::$iso_3166_countryCodes[$row['Country']]);
array_push($data, $member);
}
$filename = "UsersPerCountry" . date('Ymd') . ".csv";
return CSVExporter::getInstance()->export($filename, $data, ',');
}
/**
* 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;
}
/**
* Test deprecation of SQLQuery::setDelete/getDelete
*/
public function testDeprecatedSetDelete()
{
// Temporarily disable deprecation
Deprecation::notification_version(null);
$query = new SQLQuery();
$query->setSelect(array('"SQLQueryTest_DO"."Name"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->setWhere(array('"SQLQueryTest_DO"."Name"' => 'Andrew'));
// Check SQL for select
$this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
// Check setDelete works
$query->setDelete(true);
$this->assertSQLEquals(<<<EOS
DELETE FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
// Check that setDelete back to false restores the state
$query->setDelete(false);
$this->assertSQLEquals(<<<EOS
SELECT "SQLQueryTest_DO"."Name" FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Name" = ?)
EOS
, $query->sql($parameters));
$this->assertEquals(array('Andrew'), $parameters);
}
/**
* Perform a search against the data table.
*
* @param array $where Array of strings to add into the WHERE clause
* @param array $orderby Array of column as key, to direction as value to add into the ORDER BY clause
* @param string|int $start Record to start at (for paging)
* @param string|int $pageLength Number of results per page (for paging)
* @param boolean $paged Paged results or not?
* @return ArrayList|PaginatedList
*/
protected function queryList($where = array(), $orderby = array(), $start, $pageLength, $paged = true)
{
$dataClass = $this->dataRecord->getDataClass();
if (!$dataClass) {
return new PaginatedList(new ArrayList());
}
$resultColumns = $this->dataRecord->getDataSingleton()->summaryFields();
$resultColumns['ID'] = 'ID';
$results = new ArrayList();
$query = new SQLQuery();
$query->setSelect($this->escapeSelect(array_keys($resultColumns)))->setFrom("\"{$dataClass}\"");
$query->addWhere($where);
$query->addOrderBy($orderby);
$query->setConnective('AND');
if ($paged) {
$query->setLimit($pageLength, $start);
}
foreach ($query->execute() as $record) {
$result = new $dataClass($record);
$result->Columns = $this->Columns($result);
// we attach Columns here so the template can loop through them on each result
$results->push($result);
}
if ($paged) {
$list = new PaginatedList($results);
$list->setPageStart($start);
$list->setPageLength($pageLength);
$list->setTotalItems($query->unlimitedRowCount());
$list->setLimitItems(false);
} else {
$list = $results;
}
return $list;
}
function pagesIncluded()
{
$ids = array();
$q = new SQLQuery();
$q->setSelect(array('"SiteTree"."ID"', '"SiteTree"."ParentID"'))->setFrom('"SiteTree"')->addLeftJoin('SiteTree_Live', '"SiteTree_Live"."ID" = "SiteTree"."ID"')->setWhere('"SiteTree"."Version" > "SiteTree_Live"."Version"');
foreach ($q->execute() as $row) {
$ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']);
}
return $ids;
}
public function testSelect()
{
$query = new SQLQuery('"Title"', '"MyTable"');
$query->addSelect('"TestField"');
$this->assertSQLEquals('SELECT "Title", "TestField" FROM "MyTable"', $query->sql());
// Test replacement of select
$query->setSelect(array('Field' => '"Field"', 'AnotherAlias' => '"AnotherField"'));
$this->assertSQLEquals('SELECT "Field", "AnotherField" AS "AnotherAlias" FROM "MyTable"', $query->sql());
// Check that ' as ' selects don't get mistaken as aliases
$query->addSelect(array('Relevance' => "MATCH (Title, MenuTitle) AGAINST ('Two as One')"));
$this->assertSQLEquals('SELECT "Field", "AnotherField" AS "AnotherAlias", MATCH (Title, MenuTitle) AGAINST (' . '\'Two as One\') AS "Relevance" FROM "MyTable"', $query->sql());
}
public function DDLVisibility()
{
$values = array('Visible' => 'Visible', 'Not-Visible' => 'Not-Visible');
$selected_value = '';
if (isset($_REQUEST["SurveyQuestionTemplateID"])) {
$owner = $_REQUEST["SurveyQuestionTemplateID"];
if (isset($owner)) {
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect("Visibility");
$sqlQuery->setFrom("SurveyQuestionTemplate_DependsOn");
$sqlQuery->setWhere("SurveyQuestionTemplateID = {$owner} AND ChildID = {$this->ID}");
$selected_value = current($sqlQuery->execute()->keyedColumn());
}
} else {
if (isset($_REQUEST["SurveyStepTemplateID"])) {
$owner = $_REQUEST["SurveyStepTemplateID"];
if (isset($owner)) {
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect("Visibility");
$sqlQuery->setFrom("SurveyStepTemplate_DependsOn");
$sqlQuery->setWhere("SurveyStepTemplateID = {$owner} AND SurveyQuestionTemplateID = {$this->ID}");
$selected_value = current($sqlQuery->execute()->keyedColumn());
}
}
}
return new DropdownField("Visibility_{$this->ID}", "Visibility_{$this->ID}", $values, $selected_value);
}
/**
* Returns all SilvercartProductIDs that have this group set as mirror
* group.
*
* @return array
*/
public function getMirroredProductIDs()
{
$mirroredProductIDs = array();
$translations = $this->getTranslations();
$translationProductGroupIDs = array($this->ID);
if ($translations && $translations->count() > 0) {
foreach ($translations as $translation) {
$translationProductGroupIDs[] = $translation->ID;
}
}
$translationProductGroupIDList = implode(',', $translationProductGroupIDs);
$sqlQuery = new SQLQuery();
$sqlQuery->setSelect('SP_SPGMP.SilvercartProductID');
$sqlQuery->addFrom('SilvercartProduct_SilvercartProductGroupMirrorPages SP_SPGMP');
$sqlQuery->addWhere(array(sprintf("SP_SPGMP.SilvercartProductGroupPageID IN (%s)", $translationProductGroupIDList)));
$result = $sqlQuery->execute();
foreach ($result as $row) {
$mirroredProductIDs[] = $row['SilvercartProductID'];
}
return $mirroredProductIDs;
}
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 testDbDatetimeDifference()
{
$offset = $this->checkPreconditions();
$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', $offset);
$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', $offset);
$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', $offset);
$query = new SQLQuery();
$query->setSelect(array());
$query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(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', $offset);
}
