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));
}
