function exportCLAUsers() { $params = $this->owner->getRequest()->getVars(); $fields = $params['fields']; $groups = $params['groups']; $ext = $params['ext']; if (!isset($params['fields']) || empty($params['fields'])) { return $this->owner->httpError('412', 'missing required param fields'); } if (!isset($params['groups']) || empty($params['groups'])) { return $this->owner->httpError('412', 'missing required param groups'); } if (!isset($params['ext']) || empty($params['ext'])) { return $this->owner->httpError('412', 'missing required param ext'); } if (!count($fields)) { return $this->httpError('412', 'missing required param fields'); } $query = new SQLQuery(); $query->setFrom('Member'); $query->addLeftJoin('Group_Members', 'GM.MemberID = Member.ID', 'GM'); $query->addLeftJoin('Group_Members', 'GM2.MemberID = Member.ID', 'GM2'); $query->addLeftJoin('Group', 'Group.ID = GM.GroupID'); $query->addWhere('Member.GerritID IS NOT NULL'); $query->addWhere('GM2.GroupID IN (' . implode(',', $groups) . ')'); $fields['Groups'] = "GROUP_CONCAT(Group.Code SEPARATOR ' | ')"; $query->setSelect($fields); $query->addGroupBy('Member.ID'); $query->addOrderBy(array('Member.Surname', 'Member.FirstName')); $result = $query->execute(); $filename = "MembersByRole-" . date('Ymd') . "." . $ext; $delimiter = $ext == 'xls' ? "\t" : ","; return CSVExporter::getInstance()->export($filename, $result, $delimiter); }
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; }
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; }
/** * Append a GROUP BY clause to this query. * * @param String $groupby Escaped SQL statement */ public function groupby($groupby) { $this->query->addGroupBy($groupby); return $this; }
/** * 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; }
/** * 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)); }