/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $current_date = @$params['name_pattern']; $date_filter = ""; if ($current_date) { $current_date = Convert::raw2sql($current_date); $date_filter = "AND (\n (\n (DATE('{$current_date}') < TrainingCourseScheduleTime.EndDate)\n OR\n (TrainingCourse.Online=1 AND TrainingCourseScheduleTime.StartDate IS NULL AND TrainingCourseScheduleTime.EndDate IS NULL)\n )\n )"; } $sql = <<<SQL SELECT C.Name AS CompanyName FROM TrainingCourse INNER JOIN CompanyService ON CompanyService.ID = TrainingCourse.TrainingServiceID AND CompanyService.ClassName='TrainingService' INNER JOIN Company C on C.ID = CompanyService.CompanyID INNER JOIN TrainingCourseSchedule ON TrainingCourseSchedule.CourseID = TrainingCourse.ID LEFT JOIN TrainingCourseScheduleTime ON TrainingCourseScheduleTime.LocationID = TrainingCourseSchedule.ID WHERE CompanyService.Active = 1 {$date_filter} GROUP BY C.Name ORDER BY C.Name ASC; SQL; $results = DB::query($sql); $companies = array(); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); $company = $record['CompanyName']; $value = sprintf('%s', $company); array_push($companies, new SearchDTO($value, $value)); } return new OpenStackImplementationNamesQueryResult($companies); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $term = @$params['name_pattern']; $term = Convert::raw2sql($term); $topics = array(); $sql = <<<SQL SELECT DISTINCT (Name) AS Value FROM CompanyService WHERE CompanyService.ClassName IN ('Appliance','Distribution') AND ( CompanyService.Name LIKE '%{$term}%' OR CompanyService.Overview LIKE '%{$term}%' ) LIMIT 20; SQL; $results = DB::query($sql); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); array_push($topics, new SearchDTO($record['Value'], $record['Value'])); } $sql2 = <<<SQL SELECT DISTINCT (Company.Name) AS Value FROM CompanyService INNER JOIN Company ON Company.ID = CompanyService.CompanyID WHERE CompanyService.ClassName IN ('Appliance','Distribution') AND ( Company.Name LIKE '%{$term}%' ) LIMIT 20; SQL; $results = DB::query($sql2); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); array_push($topics, new SearchDTO($record['Value'], $record['Value'])); } return new OpenStackImplementationNamesQueryResult($topics); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $current_date = @$params['name_pattern']; $current_date = Convert::raw2sql($current_date); $sql = <<<SQL SELECT TrainingCourseSchedule.City, TrainingCourseSchedule.State, TrainingCourseSchedule.Country FROM TrainingCourse INNER JOIN CompanyService ON CompanyService.ID = TrainingCourse.TrainingServiceID AND CompanyService.ClassName='TrainingService' INNER JOIN TrainingCourseSchedule ON TrainingCourseSchedule.CourseID = TrainingCourse.ID LEFT JOIN TrainingCourseScheduleTime ON TrainingCourseScheduleTime.LocationID = TrainingCourseSchedule.ID WHERE CompanyService.Active = 1 AND ( ((DATE('{$current_date}') < TrainingCourseScheduleTime.StartDate AND DATE('{$current_date}') < TrainingCourseScheduleTime.EndDate) OR (TrainingCourse.Online=1 AND TrainingCourseScheduleTime.StartDate IS NULL AND TrainingCourseScheduleTime.EndDate IS NULL)) ) GROUP BY TrainingCourseSchedule.City, TrainingCourseSchedule.State, TrainingCourseSchedule.Country ORDER BY TrainingCourseSchedule.City, TrainingCourseSchedule.State, TrainingCourseSchedule.Country ASC; SQL; $results = DB::query($sql); $locations = array(); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); $city = $record['City']; $state = $record['State']; $country = Geoip::countryCode2name($record['Country']); if (!empty($state)) { $value = sprintf('%s, %s, %s', $city, $state, $country); } else { $value = sprintf('%s, %s', $city, $country); } array_push($locations, new SearchDTO($value, $value)); } return new OpenStackImplementationNamesQueryResult($locations); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $selectedSummits = implode(',', $params["selectedSummits"]); $query = "SELECT * FROM (\n SELECT IF(S.FirstName IS NOT NULL,S.FirstName,M.FirstName) AS FirstName,\n IF(S.LastName IS NOT NULL,S.LastName,M.Surname) AS LastName, M.Email AS Email,\n S.MemberID, E.Title AS PresentationTitle, P.ID as PresentationID,\n PC.Title AS Track, SSP.`Order`, SSPL.ListType, PC.SessionCount, PC.AlternateCount\n FROM PresentationSpeaker S\n LEFT JOIN Member M ON S.MemberID = M.ID\n INNER JOIN Presentation_Speakers PS on PS.PresentationSpeakerID = S.ID\n INNER JOIN Presentation P ON P.ID = PS.PresentationID\n INNER JOIN SummitEvent E ON E.ID = P.ID\n INNER JOIN PresentationCategory PC ON PC.ID = P.CategoryID\n LEFT JOIN SummitSelectedPresentation SSP ON SSP.PresentationID = P.ID\n LEFT JOIN SummitSelectedPresentationList SSPL ON SSPL.ID = SSP.SummitSelectedPresentationListID\n WHERE E.SummitID IN ( {$selectedSummits} ) ORDER BY P.ID,S.MemberID,SSP.LastEdited DESC\n ) AS Q1 GROUP BY PresentationID,MemberID;"; $result = DB::query($query); return new AbstractQueryResult(array($result)); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $marketplace_type = $params[0]; $companies = array(); $current_user = Member::currentUser(); if ($current_user->isMarketPlaceSuperAdmin() || $current_user->isMarketPlaceTypeSuperAdmin($marketplace_type)) { /** * @todo: * company has a distribution admin manager * company has signed sponsorship contract * company has signed all contracts related to distribution * all contracts are valid */ $sql = <<<SQL \t\tSELECT C.ID,C.Name FROM Company C ORDER BY Name ASC; SQL; $results = DB::query($sql); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); array_push($companies, new CompanyDTO((int) $record['ID'], $record['Name'])); } } else { $res = array(); switch (strtolower(trim($marketplace_type))) { case 'implementation': $res = $current_user->getManagedMarketPlaceCompaniesByType(IAppliance::MarketPlaceGroupSlug); $res2 = $current_user->getManagedMarketPlaceCompaniesByType(IDistribution::MarketPlaceGroupSlug); if (count($res) > 0 && count($res2) > 0) { $res = array_merge($res, array_diff_assoc($res, $res2)); } else { if (count($res) == 0) { $res = $res2; } } break; case 'distribution': $res = $current_user->getManagedMarketPlaceCompaniesByType(IDistribution::MarketPlaceGroupSlug); break; case 'appliance': $res = $current_user->getManagedMarketPlaceCompaniesByType(IAppliance::MarketPlaceGroupSlug); break; case 'public cloud': $res = $current_user->getManagedMarketPlaceCompaniesByType(IPublicCloudService::MarketPlaceGroupSlug); break; case 'private cloud': $res = $current_user->getManagedMarketPlaceCompaniesByType(IPrivateCloudService::MarketPlaceGroupSlug); break; case 'consultant': $res = $current_user->getManagedMarketPlaceCompaniesByType(IConsultant::MarketPlaceGroupSlug); break; } foreach (array_values($res) as $company) { array_push($companies, new CompanyDTO((int) $company->ID, $company->Name)); } } return new CompaniesWithDistributionCreationRightsResult($companies); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $res = 0; if ($specification instanceof SummitQuerySpecification) { $params = $specification->getSpecificationParams(); $summit_id = $params[0]; $sql = <<<SQL \t\t\tSELECT COUNT(V.ID) FROM SpeakerVote V INNER JOIN Talk T ON T.ID = V.TalkID WHERE T.SummitID = {$summit_id}; SQL; $res = (int) DB::query($sql)->value(); } return new AbstractQueryResult(array($res)); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $filter = ''; if (!empty($term)) { $term = @$params['name_pattern']; $term = Convert::raw2sql($term); $countries = Geoip::getCountryDropDown(); $matched_countries = array_filter($countries, function ($el) use($term) { return strpos(strtolower($el), strtolower($term)) !== false; }); $country_filter = ''; if (count($matched_countries)) { foreach ($matched_countries as $code => $name) { $country_filter .= " OR Country = '{$code}' "; } } else { $country_filter = " OR Country LIKE '%{$term}%' "; } $filter = "AND City LIKE '%{$term}%' {$country_filter}"; } $locations = array(); $class_name = $this->getClassName(); $sql = <<<SQL SELECT City,Country,State FROM DataCenterLocation INNER JOIN CompanyService ON CompanyService.ID = DataCenterLocation.CloudServiceID \t\tWHERE CompanyService.ClassName = '{$class_name}' \t\t{$filter} \t\tGROUP BY City,Country,State \t\tORDER BY City,State, Country ASC SQL; $results = DB::query($sql); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); $city = $record['City']; $state = $record['State']; $country = Geoip::countryCode2name($record['Country']); if (!empty($state)) { $value = sprintf('%s, %s, %s', $city, $state, $country); } else { $value = sprintf('%s, %s', $city, $country); } array_push($locations, new SearchDTO($value, $value)); } return new OpenStackImplementationNamesQueryResult($locations); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $selectedSummits = $params["selectedSummits"]; $onlyApprovedSpeakers = $params["onlyApprovedSpeakers"]; $affiliation = $params["affiliation"]; if (count($selectedSummits)) { $inCondition = "("; $inConditionSeparator = ""; foreach ($selectedSummits as $summitId) { $inCondition .= "{$inConditionSeparator}{$summitId}"; $inConditionSeparator = ","; } $inCondition .= ")"; } $whereAdded = false; $whereClause = ""; if (count($selectedSummits)) { if (!$whereAdded) { $whereClause = "WHERE "; $whereAdded = true; } $whereClause .= "E.SummitID IN {$inCondition}"; } if ($onlyApprovedSpeakers) { if (!$whereAdded) { $whereClause = "WHERE "; $whereAdded = true; } else { $whereClause .= " AND "; } $whereClause .= "(PR.Progress BETWEEN 0 AND 2) AND (Status IS NULL)"; } if (isset($affiliation) && strlen($affiliation) > 0) { if (!$whereAdded) { $whereClause = "WHERE "; $whereAdded = true; } else { $whereClause .= " AND "; } $whereClause .= "OG.Name like '%{$affiliation}%'"; } $query = "SELECT DISTINCT\nSurname,\nFirstName,\nEmail,\nE.Title as PresentationTitle,\nProgress as PresentationProgress,\nStatus as PresentationStatus\nFROM Presentation PR\nLEFT JOIN SummitEvent AS E ON PR.ID = E.ID\nINNER JOIN Presentation_Speakers PS ON PR.Id = PS.PresentationID\nINNER JOIN Member ME ON ME.Id = PS.PresentationSpeakerID\nLEFT JOIN Affiliation AF ON AF.MemberID = ME.ID\nINNER JOIN Org OG ON OG.ID = AF.OrganizationID\n{$whereClause}\nORDER BY Surname, FirstName"; $result = DB::query($query); return new AbstractQueryResult(array($result)); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $term = @$params['name_pattern']; $term = Convert::raw2sql($term); $topics = array(); $sql = <<<SQL SELECT DISTINCT (Company.Name) AS Value, ID FROM Company WHERE Company.Name LIKE '%{$term}%' LIMIT 20; SQL; $results = DB::query($sql); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); array_push($topics, new SearchDTO($record['Value'], $record['Value'], $record['ID'])); } return new OpenStackImplementationNamesQueryResult($topics); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $res = 0; if ($specification instanceof FoundationMembersSubscribedToNewsLetterCountQuerySpecification) { $params = $specification->getSpecificationParams(); $filter = ''; if (!is_null($params[0])) { $filter = " AND M.Country != '" . $params[0] . "' "; } $sql = <<<SQL \t\t\tSELECT COUNT(M.ID) From Member M \t\t\tLEFT JOIN Group_Members GM ON GM.MemberID = M.ID \t\t\tINNER JOIN `Group` G on G.ID = GM.GroupID \t\t\tWHERE G.Code = 'foundation-members' AND M.SubscribedToNewsletter = 1 {$filter}; SQL; $res = (int) DB::query($sql)->value(); } return new AbstractQueryResult(array($res)); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $params = $specification->getSpecificationParams(); $topics = array(); $sql = <<<SQL SELECT ConsultantServiceOfferedType.Type AS Value FROM CompanyService INNER JOIN Consultant_ServicesOffered ON Consultant_ServicesOffered.ConsultantID = CompanyService.ID INNER JOIN ConsultantServiceOfferedType ON ConsultantServiceOfferedType.ID = Consultant_ServicesOffered.ConsultantServiceOfferedTypeID WHERE CompanyService.ClassName IN ('Consultant') \t\tGROUP BY ConsultantServiceOfferedType.Type SQL; $results = DB::query($sql); for ($i = 0; $i < $results->numRecords(); $i++) { $record = $results->nextRecord(); array_push($topics, new SearchDTO($record['Value'], $record['Value'])); } return new OpenStackImplementationNamesQueryResult($topics); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $res = 0; if ($specification instanceof UserStoriesCountQuerySpecification) { $params = $specification->getSpecificationParams(); $filter = ''; if ($params[0] == true) { $filter = ' AND FeaturedOnSite = 1 '; } $sql = <<<SQL \t\t\tSELECT COUNT(U.ID) FROM OpenstackUser U \t\t\tinner join Page P ON P.ID = U.ID \t\t\tinner join SiteTree ST on ST.ID = U.ID \t\t\tWHERE ListedOnSite = 1 AND Status in ('Published','Saved (update) ') {$filter} SQL; $res = (int) DB::query($sql)->value(); } return new AbstractQueryResult(array($res)); }
/** * @param IQuerySpecification $specification * @return IQueryResult */ public function handle(IQuerySpecification $specification) { $res = 0; if ($specification instanceof CompanyCountQuerySpecification) { $params = $specification->getSpecificationParams(); $filter = ''; if (!is_null($params[0])) { $filter .= " AND MemberLevel = '" . $params[0] . "' "; } if (!is_null($params[1])) { $filter .= " AND Country != 'NULL' and Country != '" . $params[1] . "' "; } $sql = <<<SQL \t\t\tSELECT COUNT(C.ID) FROM Company C WHERE DisplayOnSite = 1 {$filter} SQL; $res = (int) DB::query($sql)->value(); } return new AbstractQueryResult(array($res)); }