public async Task <List <ParticipantTeamDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"where Name like '%{filterBy}%' or Description '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Title desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
;With
PT As (
Select T.Id, T.Name, T.Description, STRING_AGG(P.Email, ',') [Participants]
From Teams T
Left Join ParticipantTeams PT On T.Id = PT.TeamId
Left Join Participants P On P.Id = PT.TeamMemberId
Group By T.Id, Name, Description
)
Select Id, Name, Description, Participants, COUNT(*) OVER () as Total
From PT
{filterBy}
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <PendingSpeakerDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"WHERE Name LIKE '%{filterBy}%' or Email LIKE '%{filterBy}% or InterestInTopic LIKE '%{filterBy}% or RefferedBy LIKE '%{filterBy}%' or Phone LIKE '%{filterBy}%' or LinkedInUrl LIKE '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Name desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
With
PS As (
Select PS.Id, PS.Username, PS.FirstName + ' ' + PS.LastName Name, PS.Email, PS.InterestInTopic, P.Username ReferredBy, PS.Phone, PS.LinkedInUrl, PS.IsReferrer
From PendingSpeakers PS
Left Join Participants P On PS.ReferredBy = P.Id
WHERE PS.IsRejected = 0 And IsAccepted = 0
)
Select Id, Name, Email, InterestInTopic, ReferredBy,Phone, LinkedInUrl,IsReferrer
From PS
{filterBy}
Group By Id, Name, Email, InterestInTopic, ReferredBy,Phone, LinkedInUrl,IsReferrer
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <SpeakerDTO> > GetPagedAsync(int offset, int limit, string filterBy, string orderBy)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"Where FirstName like '%{filterBy}%' or LastName like '%{filterBy}%'
or Title like '%{filterBy}%' or CompanyName like '%{filterBy}%' or Website like '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by FirstName desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
Select Id, FirstName, LastName, Title, CompanyName, COUNT(*) OVER () as Total
From Speakers
{filterBy}
Group By Id, FirstName, LastName, Title, CompanyName
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <SponsorDTO> > GetPagedAsync(int offset, int limit, string filterBy, string orderBy)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"Where CompanyName like '%{filterBy}%' or ContactPerson like '%{filterBy}%' or ContactPersonEmail like '%{filterBy}%'
or ContactPersonPhone like '%{filterBy}%' or Website like '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by CompanyName desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
Select Id, CompanyName, ContactPerson, ContactPersonEmail, ContactPersonPhone
, Description, LogoUrl, Website, COUNT(*) OVER () as Total
From Sponsors
{filterBy}
Group By Id, CompanyName, ContactPerson, ContactPersonEmail, ContactPersonPhone
, Description, LogoUrl, Website
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <AnnouncementDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"where Title like '%{filterBy}%' or Description like '%{filterBy}%' or CallAction like '%{filterBy}%'
or LinkUrl like '%{filterBy}%' or Expiration like '{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Title desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
select Id, Title, Description, CallAction, LinkUrl, ImageUrl, Expiration, COUNT(*) OVER () as Total
from announcements
{filterBy}
Group By Id, Title, Description, CallAction, LinkUrl, ImageUrl, Expiration
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <CompanyDTO> > GetPagedAsync(int offset, int limit, string filterBy, string orderBy)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"Where Name like '%{filterBy}%' or Phone like '%{filterBy}%' or Website like '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Name desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
Select Id, Name, Address, Phone, LogoUrl, Website, COUNT(*) OVER () as Total
From Companies
{filterBy}
Group By Id, Name, Address, Phone, LogoUrl, Website
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <ParticipantDTO> > GetPagedAsync(int offset, int limit, string filterBy, string orderBy)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"Where Username like '%{filterBy}%' or FirstName like '%{filterBy}%' or LastName like '%{filterBy}%'
Or Email like '%{filterBy}%' or Title like '%{filterBy}%'
Or EmailCorp like '%{filterBy}%' or PhoneCorp like '%{filterBy}%'
Or CompanyName like '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Name desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
;With
P As (
Select P.Id, P.Username, P.FirstName, P.LastName, P.Email, P.Verified, P.Phone, P.Mobile, P.PlainPassword Password
, P.Title, P.Active, P.PhotoUrl, P.DateSuspended, P.EmailCorp, P.PhoneCorp, P.LinkedinUrl, P.CompanyName
From Participants P
)
Select Id, Username, FirstName, LastName, Email, Verified, Phone, Mobile, Title, Active, PhotoUrl, Password
,DateSuspended, EmailCorp, PhoneCorp, LinkedinUrl, CompanyName, COUNT(*) OVER () as Total
From P
{filterBy}
Group By Id, Username, FirstName, LastName, Email, Verified, Phone, Mobile, Title, Active, PhotoUrl, Password
,DateSuspended, EmailCorp, PhoneCorp, LinkedinUrl, CompanyName
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <EventDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"where Title like '%{filterBy}%' or Description like '%{filterBy}%'
or Speakers like '%{filterBy}%' or Sponsors like '%{filterBy}%' or EventDate like '{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Title desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
;With
Evts As (
Select E.Id, C.Name CohortName, E.Title, E.Description, E.MeetingId, E.MeetingPassword
, E.EventDate, STRING_AGG(SV.FirstName + ' ' + SV.LastName + ' (' + SV.Title + ')', ', ') [Speakers], STRING_AGG(SPV.CompanyName, ', ') [Sponsors]
From Events E
Inner Join Cohorts C On E.CohortId = C.Id
Left Join EventSpeakers S On E.Id = S.EventId
Left Join EventSponsors SP On E.Id = SP.EventId
Left Join Speakers SV On S.SpeakerId = SV.Id
Left Join Sponsors SPV On SP.SponsorId = SPV.Id
Group By E.Id, E.Title, E.EventDate, E.Description, E.MeetingId, E.MeetingPassword, C.Name
)
Select Id, Title, Description, EventDate, Speakers, Sponsors, MeetingId, MeetingPassword, COUNT(*) OVER () as Total
From Evts
{filterBy}
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <GuestDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"WHERE FirstName LIKE '%{filterBy}%' OR LastName LIKE '%{filterBy}%' OR Title LIKE '%{filterBy}%'
OR Email LIKE '%{filterBy}%' OR EmailPersonal LIKE '%{filterBy}%'
OR EmailCorp LIKE '%{filterBy}%' OR PhoneCorp LIKE '%{filterBy}%'
OR CompanyName LIKE '%{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "ORDER BY Id DESC" : orderBy;
var pageBy = $@"OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY";
var query = $@"
;WITH
G AS (
SELECT G.Id, G.FirstName, G.LastName, G.EmailPersonal, G.EmailCorp, G.PhonePersonal, G.PhoneCorp, G.LinkedinUrl
, G.CompanyName, G.Title, G.Email
From Guests G
)
SELECT Id, FirstName, LastName, EmailPersonal, EmailCorp, PhonePersonal, PhoneCorp, LinkedinUrl
, CompanyName, Title, Email, COUNT(*) OVER () as Total
FROM G
{filterBy}
GROUP By Id, FirstName, LastName, EmailPersonal, EmailCorp, PhonePersonal, PhoneCorp, LinkedinUrl
, CompanyName, Title, Email
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
public async Task <List <PollDTO> > GetPagedAsync(int offset = 0, int limit = 10, string filterBy = null, string orderBy = null)
{
if (filterBy.NotNullOrEmpty())
{
filterBy = $@"where Name like '%{filterBy}%' or Event like '%{filterBy}%'
or GraphType like '%{filterBy}%' or Cohort like '{filterBy}%'";
}
else
{
filterBy = "";
}
orderBy = string.IsNullOrEmpty(orderBy) ? "order by Name desc" : orderBy;
var pageBy = $@"Offset {offset} Rows Fetch Next {limit} Rows Only";
var query = $@"
With
P As(
Select P.Id, P.Name, E.Title [Event], Graph.Name GraphType, C.Name Cohort
From Polls P
Inner Join [Events] E On P.EventId = E.Id
Inner Join ValueFields Graph On P.GraphTypeId = Graph.Id
Inner Join Cohorts C On E.CohortId = C.Id
)
Select Id, Name, Event, GraphType, Cohort, COUNT(*) OVER () as Total
From P
{filterBy}
Group By Id, Name, Event, GraphType, Cohort
{orderBy}
{pageBy}";
var records = await _repository.RawSqlQueryAsync(query);
return(records);
}
