/**
* 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;
}
/**
* @see DataExtension::augmentSQL()
*/
public function augmentSQL(SQLQuery &$query)
{
$select = $query->getSelect();
if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) {
return;
}
if (!isset(self::$sortTables[$this->owner->class])) {
$classes = array_reverse(ClassInfo::dataClassesFor($this->owner->class));
$class = null;
foreach ($classes as $cls) {
if (DataObject::has_own_table($cls) && ($fields = DataObject::database_fields($cls)) && isset($fields['SortOrder'])) {
$class = $cls;
break;
}
}
self::$sortTables[$this->owner->class] = $class;
} else {
$class = self::$sortTables[$this->owner->class];
}
if ($class) {
$query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir);
} else {
$query->addOrderBy("\"SortOrder\" " . self::$sort_dir);
}
}
/**
* Set the ORDER BY clause of this query
*
* @see SQLQuery::orderby()
*
* @param String $sort Column to sort on (escaped SQL statement)
* @param String $direction Direction ("ASC" or "DESC", escaped SQL statement)
* @param Boolean $clear Clear existing values
* @return DataQuery
*/
public function sort($sort = null, $direction = null, $clear = true)
{
if ($clear) {
$this->query->setOrderBy($sort, $direction);
} else {
$this->query->addOrderBy($sort, $direction);
}
return $this;
}
/**
* @see DataExtension::augmentSQL()
*/
public function augmentSQL(SQLQuery &$query)
{
$select = $query->getSelect();
if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) {
return;
}
if (!isset(self::$sortTables[$this->owner->class])) {
// look up the table that has the SortOrder field
$class = ClassInfo::table_for_object_field($this->owner->class, 'SortOrder');
self::$sortTables[$this->owner->class] = $class;
} else {
$class = self::$sortTables[$this->owner->class];
}
if ($class) {
$query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir);
} else {
$query->addOrderBy("\"SortOrder\" " . self::$sort_dir);
}
}
/**
* Polls the message queue. Will return a map of senders with the lastest message ID
* @see http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
* @param SS_HTTPRequest $request
*/
public function get_openchats(SS_HTTPRequest $request)
{
if (!Permission::checkMember(Member::currentUser(), "CMS_ACCESS_LiveChatAdmin")) {
header("HTTP/1.0 403 Forbidden");
die('You do not have permission to use the live chat module');
}
// find the messages that have been sent to you
$query1 = new SQLQuery("*", "LiveChatMessage", "ToID = " . (int) Member::currentUserID());
$query1->addOrderBy("ID DESC");
$query1->addWhere("ClassName = 'LiveChatMessage'");
$query2 = new SQLQuery("ID, FromID, FromName", '(' . $query1->sql() . ') x');
$query2->addGroupBy("FromID, FromName");
// only select the largest ID, unique for each sender
$result = $query2->execute();
$returnar = array();
// add the names to the array
foreach ($result as $id) {
$member = Member::get()->byID($id['FromID']);
$returnar[$id['ID']] = array("Name" => $member ? $member->getName() : $id['FromName'], "FromID" => $id['FromID'] ? $id['FromID'] : md5($id['FromName']));
}
header('Content-Type: application/json');
die(json_encode($returnar));
}
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);
}
/**
* 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;
}
/**
* 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;
}
/**
* 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\"");
}
}
}
