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