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, ',');
}
function ViewDeploymentStatistics()
{
$range = self::getSurveyRange('ViewDeploymentStatistics');
if (intval($range) > 0) {
return Controller::curr()->redirect(Controller::curr()->Link("ViewDeploymentStatisticsSurveyBuilder"));
}
SangriaPage_Controller::generateDateFilters('D');
Requirements::css("themes/openstack/javascript/datetimepicker/jquery.datetimepicker.css");
Requirements::javascript("themes/openstack/javascript/datetimepicker/jquery.datetimepicker.js");
Requirements::css("themes/openstack/css/deployment.survey.page.css");
Requirements::javascript("themes/openstack/javascript/deployment.survey.filters.js");
Requirements::javascript('themes/openstack/javascript/sangria/sangria.page.viewdeploymentstatistics.js');
return $this->owner->Customise(array())->renderWith(array('SangriaPage_ViewDeploymentStatistics', 'SangriaPage', 'SangriaPage'));
}
/**
* @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 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();
}
