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