/**
* @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));
}
