public function exportMarketplaceAdmins()
{
$params = $this->owner->getRequest()->getVars();
if (!isset($params['marketplace_type']) || empty($params['marketplace_type'])) {
return $this->owner->httpError('412', 'missing required param marketplace type');
}
$marketplace_type = $params['marketplace_type'];
$filters_string = implode("','", $marketplace_type);
$fileDate = date('Ymdhis');
SangriaPage_Controller::generateDateFilters('s');
$sql = <<<SQL
SELECT M.FirstName, M.Surname, M.Email, C.Name AS Company, GROUP_CONCAT(MT.Name ORDER BY MT.Name ASC SEPARATOR ' - ') AS Marketplace
FROM Member AS M
INNER JOIN ( SELECT MemberID, CompanyID, GroupID FROM Company_Administrators WHERE Company_Administrators.GroupID IN ('{$filters_string}') ) AS CA ON CA.MemberID = M.ID
INNER JOIN Company AS C ON C.ID = CA.CompanyID
INNER JOIN MarketPlaceType AS MT ON MT.AdminGroupID = CA.GroupID
GROUP BY M.FirstName, M.Surname, M.Email, C.Name
ORDER BY M.Email, C.Name ;
SQL;
$res = DB::query($sql);
$fields = array('FirstName', 'Surname', 'Email', 'Company', 'Marketplace');
$data = array();
foreach ($res as $row) {
$member = array();
foreach ($fields as $field) {
$member[$field] = str_replace(',', ' ', $row[$field]);
//commas tabs cell in excel
}
array_push($data, $member);
}
$filename = "Marketplace_Admins_" . $fileDate . ".csv";
return CSVExporter::getInstance()->export($filename, $data, ',');
}
/**
* @param string $survey_table_prefix
* @return array|null|Session|string
*/
private function generateFilters($survey_table_prefix = 'I')
{
$request = Controller::curr()->getRequest();
$from = $request->getVar('From');
$to = $request->getVar('To');
$template = $this->getCurrentSelectedSurveyTemplate();
$class_name = $this->getCurrentSelectedSurveyClassName();
$filters = Session::get(sprintf('SurveyBuilder.%sStatistics.Filters', Session::get('SurveyBuilder.Statistics.ClassName')));
$filter_query_tpl_int = <<<SQL
AND EXISTS
(
SELECT * FROM SurveyAnswer A2
INNER JOIN SurveyQuestionTemplate Q2 ON Q2.ID = A2.QuestionID
INNER JOIN SurveyStepTemplate STPL2 ON STPL2.ID = Q2.StepID
INNER JOIN SurveyTemplate SSTPL2 ON SSTPL2.ID = STPL2.SurveyTemplateID
INNER JOIN SurveyQuestionValueTemplate V2 ON V2.OwnerID = Q2.ID
INNER JOIN SurveyStep S2 ON S2.ID = A2.StepID
INNER JOIN Survey I2 ON I2.ID = S2.SurveyID
WHERE
I2.ClassName = '{$class_name}'
AND FIND_IN_SET(V2.ID, A2.Value) > 0
AND SSTPL2.ID = %s
AND Q2.ID = %s
AND V2.ID = %s
AND I2.ID = {$survey_table_prefix}.ID
)
SQL;
$filter_query_tpl_str = <<<SQL
AND EXISTS
(
SELECT * FROM SurveyAnswer A2
INNER JOIN SurveyQuestionTemplate Q2 ON Q2.ID = A2.QuestionID
INNER JOIN SurveyStepTemplate STPL2 ON STPL2.ID = Q2.StepID
INNER JOIN SurveyTemplate SSTPL2 ON SSTPL2.ID = STPL2.SurveyTemplateID
INNER JOIN SurveyStep S2 ON S2.ID = A2.StepID
INNER JOIN Survey I2 ON I2.ID = S2.SurveyID
WHERE
I2.ClassName = '{$class_name}'
AND SSTPL2.ID = %s
AND Q2.ID = %s
AND I2.ID = {$survey_table_prefix}.ID
AND FIND_IN_SET('%s', A2.Value) > 0
)
SQL;
$filters_where = '';
if (!empty($from) && !empty($to)) {
$filters_where = " AND " . SangriaPage_Controller::generateDateFilters($survey_table_prefix, "LastEdited");
}
if (!empty($filters)) {
$filters = trim($filters, ',');
$filters = explode(',', $filters);
foreach ($filters as $t) {
$t = explode(':', $t);
$qid = intval($t[0]);
$vid = is_int($t[1]) ? intval($t[1]) : $t[1];
if (count($t) === 3) {
$vid = sprintf('%s:%s', $t[1], $t[2]);
}
$filter_query_tpl = is_int($vid) ? $filter_query_tpl_int : $filter_query_tpl_str;
$filters_where .= sprintf($filter_query_tpl, $template->ID, $qid, $vid);
}
}
return $filters_where;
}
public static function generateDateFilters($table_prefix = '', $date_field = 'UpdateDate')
{
$where_query = '';
$start_date = Controller::curr()->request->getVar('From');
$end_date = Controller::curr()->request->getVar('To');
if (!empty($table_prefix)) {
$table_prefix .= '.';
}
if (isset($start_date) && isset($end_date)) {
$date_from = Convert::raw2sql(trim($start_date));
$date_to = Convert::raw2sql(trim($end_date));
$start = new \DateTime($date_from);
$start->setTime(00, 00, 00);
$end = new \DateTime($date_to);
$end->setTime(23, 59, 59);
$where_query .= " ( {$table_prefix}{$date_field} >= '{$start->format('Y-m-d H:i:s')}' AND {$table_prefix}{$date_field} <= '{$end->format('Y-m-d H:i:s')}' ) ";
} else {
$start_date = self::$default_start_date;
$end_date = self::$default_end_date;
$where_query .= " ( {$table_prefix}{$date_field} >= '{$start_date}' AND {$table_prefix}{$date_field} <= '{$end_date}' ) ";
}
self::$date_filter_query = $where_query;
return $where_query;
}
public function DeploymentMatchingOrgs()
{
$filterWhereClause = SangriaPage_Controller::generateFilterWhereClause();
$range = self::getSurveyRange('ViewDeploymentsPerRegion');
if ($range == SurveyType::MARCH_2015) {
$range_filter = "D.Created >= '" . SURVEY_START_DATE . "'";
} else {
$range_filter = "D.Created < '" . SURVEY_START_DATE . "'";
}
$date_filter = ' AND ' . SangriaPage_Controller::$date_filter_query;
$query = "SELECT DISTINCT O.Name FROM Deployment D JOIN DeploymentSurvey S ON (D.DeploymentSurveyID = S.ID) JOIN Org O on (S.OrgID = O.ID) WHERE " . $range_filter . $filterWhereClause . $date_filter;
$results = DB::query($query);
$list = new ArrayList();
foreach ($results as $row) {
$list->push(new Org($row));
}
return $list;
}
public function UsersWithCommits()
{
SangriaPage_Controller::generateDateFilters('', 'CreatedDate');
$date_filter = SangriaPage_Controller::$date_filter_query;
$sql = <<<SQL
SELECT COUNT(M.ID)
FROM ( SELECT COUNT(ID) Commits , OwnerID FROM GerritChangeInfo WHERE {$date_filter} GROUP BY OwnerID) AS C
INNER JOIN GerritUser M on M.ID = C.OwnerID;
SQL;
$res = DB::query($sql);
return $res->value();
}
function SpeakingSubmissions()
{
$submissions = DataObject::get("SpeakerSubmission", "Created > '2012-11-01'", "Created desc");
SangriaPage_Controller::$submissionsCount = $submissions->Count();
return $submissions;
}
