public async Task <ISearchTarget[]> GetObjectsByDb(int dbid)
{
var sql = @"SELECT * FROM 'SchemaSearch.DbObjects' WHERE dbid =@dbid";
var dbObjects = await _db.SelectAsync <DbObject>(sql, new { dbid = dbid });
sql = @"SELECT * FROM 'SchemaSearch.DbColumns' WHERE dbid =@dbid";
var dbColumns = await _db.SelectAsync <DbColumn>(sql, new { dbid = dbid });
sql = @"SELECT * FROM 'SchemaSearch.DbIndices' WHERE dbid =@dbid";
var dbIndices = await _db.SelectAsync <DbIndex>(sql, new { dbid = dbid });
sql = @"SELECT * FROM 'SchemaSearch.DbIndicesColumns' WHERE dbid =@dbid";
var dbIndicesColumns = await _db.SelectAsync <DbIndexColumn>(sql, new { dbid = dbid });
Dictionary <long, DbObject> dbobjecstByID = dbObjects.ToDictionary(p => p.ObjectId);
MapDbObjectParents(dbobjecstByID);
MapdbColumnsParents(dbobjecstByID, dbColumns);
MapdbIndicesParents(dbobjecstByID, dbIndices);
MapIndicesColumns(dbIndices, dbIndicesColumns);
var dbColumnsByTableId = dbColumns.ToLookup(p => p.TableId);
var dbObjectsTargets = CreateObjectBasedSearchTarget(dbObjects, dbColumnsByTableId);
var dbColumnsTargets = dbColumns.Select(p => new ColumnSearchTarget(p));
var dbIndicesTargets = dbIndices.Select(p => new IndexSearchTarget(p)).ToArray();
var list = new List <ISearchTarget>();
list.AddRange(dbObjectsTargets);
list.AddRange(dbColumnsTargets);
list.AddRange(dbIndicesTargets);
return(list.OrderByDescending(p => p.ModificationDateStr).ToArray());
}
public async Task <EstimateDetailsResponse> Get(EstimateFromAccessToken request)
{
var estimates = await Db.SelectAsync <Estimate>(e => e.AccessToken == request.AccessToken);
if (estimates == null || estimates.Count < 1)
{
return(new EstimateDetailsResponse());
}
//pull the first one (there should only be one...)
var estimate = estimates.FirstOrDefault();
if (estimate == null)
{
return(new EstimateDetailsResponse());
}
//pull pics
var pictures = await Db.SelectAsync <EstimatePicture>(p => p.EstimateId == estimate.Id);
estimate.Pictures = new List <PictureData>();
pictures.ForEach(ep => estimate.Pictures.Add(new PictureData {
URL = $"{AwsBaseUrl}{AwsBucket}/estimates/{ep.EstimateId}/{ep.PictureId:N}.jpg", PictureType = ep.Type
}));
//pull responses
AddResponsesToEstimate(estimate);
return(new EstimateDetailsResponse {
Estimate = estimate
});
}
public async Task <GetPostResponse> Get(GetPost request)
{
if (request.Id <= 0)
{
throw new ArgumentNullException(nameof(request.Id));
}
var user = SessionAs <AuthUserSession>();
var post = await Db.SingleByIdAsync <Post>(request.Id);
OrganizationMember groupMember = null;
if (post != null)
{
AssertCanViewOrganization(Db, post.OrganizationId, user, out _, out groupMember);
}
if (post == null || post.Deleted != null && !user.IsOrganizationModerator(groupMember))
{
throw HttpError.NotFound("Post does not exist");
}
var postComments = request.Include == "comments"
? await Db.SelectAsync <PostComment>(x => x.PostId == request.Id && x.Deleted == null)
: TypeConstants <PostComment> .EmptyList;
return(new GetPostResponse
{
Cache = Stopwatch.GetTimestamp(),
Post = post,
Comments = postComments,
});
}
public async Task <object> Get(GetTextileSample request)
{
return(new GetTextileSampleResponse()
{
Samples = await Db.SelectAsync <Sample>()
});
}
public async Task <QueryItem[]> FindItems(FilterContext filterContext)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine(@"SELECT * FROM 'History.QueryItem'
WHERE ExecutionDateUtc >= @FromUtc AND ExecutionDateUtc <= @ToUtc");
if (!string.IsNullOrEmpty(filterContext.QuerySearch))
{
stringBuilder.AppendLine("AND Query like @QuerySearch");
}
if (!string.IsNullOrEmpty(filterContext.DbSearch))
{
stringBuilder.AppendLine("AND Database like @DbSearch");
}
if (!string.IsNullOrEmpty(filterContext.ServerSearch))
{
stringBuilder.AppendLine("AND Server like @ServerSearch");
}
stringBuilder.AppendLine("ORDER BY Id DESC LIMIT 1000");
return(await _db.SelectAsync <QueryItem>(
stringBuilder.ToString(),
new
{
filterContext.FromUtc,
filterContext.ToUtc,
QuerySearch = "%" + filterContext.QuerySearch + "%",
DbSearch = "%" + filterContext.DbSearch + "%",
ServerSearch = "%" + filterContext.ServerSearch + "%",
}));
}
private async Task SendEmailsToRemainingUsers(Notification notification, EmailTemplate template)
{
var remainingUserIds = notification.UserIds.Where(x => !notification.EmailedUserIds.Contains(x)).ToList();
if (remainingUserIds.Count > 0)
{
var users = await Db.SelectAsync <UserEmailInfo>(Db.From <CustomUserAuth>()
.Where(x => remainingUserIds.Contains(x.Id)));
var userMap = users.ToDictionary(x => x.Id);
foreach (var userId in remainingUserIds)
{
var user = userMap[userId];
if (!string.IsNullOrEmpty(user.Email))
{
Email.Send(template.ToEmailMessage(user.Email, user.DisplayName ?? user.UserName));
}
await RecordEmailSentToUser(notification.Id, userId);
}
}
else
{
SendNotificationEmail(template, $"{notification.UserIds.Length} subscribers");
}
}
public async Task <object> Get(QueryTranslationsGroupedByLanguage request)
{
var filterTranslation = Db.From <Translation>();
if (request.ShowDeleted is false)
{
filterTranslation = filterTranslation.Where(t => t.DeletedDate == null);
}
if (!request.Key.IsNullOrEmpty())
{
filterTranslation = filterTranslation.And(t => t.Key == request.Key);
}
if (request.ApplicationId is int appId)
{
filterTranslation = filterTranslation.And(t => t.ApplicationId == appId);
}
var translations = await Db.LoadSelectAsync <Translation>(filterTranslation);
var languages = await Db.SelectAsync <Language>(l => l.DeletedDate == null);
var grouped = new List <TranslationsGroupedModel>();
foreach (var translationsForKey in translations.GroupBy(t => (key: t.Key.ToLowerInvariant(), appId: t.ApplicationId)))
{
var model = new TranslationsGroupedModel()
{
Key = translationsForKey.Key.key,
ApplicationId = translationsForKey.Key.appId,
Translations = translationsForKey.Select(t => new TranslationItemModel
{
Id = t.Id,
Value = t.Value,
LanguageId = t.LanguageId,
LanguageName = t.Language.Name,
IsDeleted = t.DeletedDate != null,
IsMissing = false,
}).ToList()
};
var missingLanguages = languages.Where(l => !model.Translations.Any(t => t.LanguageId == l.Id));
foreach (var missingLanguage in missingLanguages)
{
model.Translations.Add(new TranslationItemModel {
LanguageId = missingLanguage.Id,
LanguageName = missingLanguage.Name,
IsMissing = true
});
}
grouped.Add(model);
}
return(new QueryTranslationsGroupedByLanguageResponse
{
Results = grouped
});
}
public async Task <GetUserOrganizationsResponse> Get(GetUserOrganizations request)
{
var userId = GetUserId();
return(new GetUserOrganizationsResponse
{
Members = await Db.SelectAsync <OrganizationMember>(x => x.UserId == userId),
MemberInvites = await Db.SelectAsync <OrganizationMemberInvite>(x => x.UserId == userId && x.Approved == null && x.Dismissed == null),
Subscriptions = await Db.SelectAsync <OrganizationSubscription>(x => x.UserId == userId),
});
}
public async virtual Task <E> GetEntityAsync(int id)
{
var result = default(E);
if (id > 0)
{
result = await Db.SelectAsync <E>(QueryTemplate.CreateSelectQuery(), DbParameters.Create(new { id = id }));
}
return(result);
}
public async Task <List <User> > GetByRoleAsync(string Role)
{
var query = Db.From <User>()
.Where(u => u.Role == Role);
var users = await Db.SelectAsync(OnGetList(query));
AdapterOut(users.ToArray());
return(users);
}
public async Task <object> Get(GetOrganizationMemberInvites request)
{
var user = GetUser();
AssertOrganizationModerator(Db, request.OrganizationId, user, out var org, out var orgMember);
return(new GetOrganizationMemberInvitesResponse
{
Results = await Db.SelectAsync <OrganizationMemberInvite>(x => x.OrganizationId == request.OrganizationId),
});
}
public async Task <MessageResponse> Get(MessageUsersRequest request)
{
var user = ValidateAndGetCurrentUser();
if (user == null)
{
throw new UnauthorizedAccessException("Must be authenticated user.");
}
var messages = await Db.SelectAsync <ConversationMessage>(m => m.ToUserId == user.Id || m.FromUserId == user.Id);
var messagesChats = messages.Select(m => new ConversationChat {
UserId = m.FromUserId != user.Id ? m.FromUserId : m.ToUserId, EstimateId = m.PropertyId
}).GroupBy(m => new { m.EstimateId, m.UserId }).Select(m => m.First());
var userIds = messages.Select(m => m.FromUserId).Concat(messages.Select(m => m.ToUserId)).Distinct().Where(m => m != user.Id).AsEnumerable();
var users = await Db.SelectAsync <User>(u => userIds.Contains(u.Id));
var propIds = messages.Select(m => m.PropertyId).Distinct().AsEnumerable();
var properties = await Db.SelectAsync <Estimate>(e => propIds.Contains(e.Id));
var estimateIds = properties.Select(p => p.Id).Distinct().AsEnumerable();
var pictures = await Db.SelectAsync <EstimatePicture>(p => estimateIds.Contains(p.EstimateId));
messagesChats = messagesChats.Select(c =>
{
c.User = users.FirstOrDefault(u => u.Id == c.UserId);
c.Estimate = properties.FirstOrDefault(e => e.Id == c.EstimateId);
var pic = pictures.FirstOrDefault(x => x.EstimateId == c.EstimateId);
if (pic != null)
{
c.Estimate?.Pictures.Add(new PictureData
{
URL = $"{AwsBaseUrl}{AwsBucket}/estimates/{c.EstimateId}/{pic.PictureId:N}.jpg",
PictureType = pic.Type
});
}
return(c);
});
return(new MessageResponse {
Chats = messagesChats.ToList()
});
}
public async Task <GetOrganizationMembersResponse> Get(GetOrganizationMembers request)
{
if (request.Id <= 0)
{
throw new ArgumentNullException(nameof(request.Id));
}
PostServicesBase.AssertCanViewOrganization(Db, request.Id, SessionAs <AuthUserSession>(), out _, out _);
return(new GetOrganizationMembersResponse
{
OrganizationId = request.Id,
Results = await Db.SelectAsync <OrganizationMember>(x => x.OrganizationId == request.Id),
});
}
public async Task <EstimateDetailsResponse> Post(ClaimEstimateFromAccessToken request)
{
var user = ValidateAndGetCurrentUser();
var estimates = await Db.SelectAsync <Estimate>(e => e.AccessToken == request.AccessToken);
var estimate = estimates.FirstOrDefault();
estimate.UserId = user.Id;
Db.Update <Estimate>(estimate);
return(new EstimateDetailsResponse {
Estimate = estimate
});
}
public async Task <EstimateIsAnanymousResponse> Get(EstimateFromAccessTokenIsAnonymous request)
{
var estimatesCollection = await Db.SelectAsync <Estimate>(e => e.AccessToken == request.AccessToken);
var estimate = estimatesCollection.FirstOrDefault();
var anonymousCollection = await Db.SelectAsync <User>(u => u.Id == estimate.UserId);
var anonymous = anonymousCollection.FirstOrDefault();
//var owner = await Db.Select<User>(u => u.Email == user.Email && u.Id != user.Id).FirstOrDefault();
return(new EstimateIsAnanymousResponse {
IsAnonymous = anonymous.FirstName == "Anonymous" && anonymous.LastName == "Anonymous"
});
}
public async Task Query_Root_Org_Test()
{
var rootOrgs = await Db.SelectAsync <Organization>(x => x.ParentId == 0);
var orgs = await OrgManage.GetRootOrganizations();
Assert.AreEqual(rootOrgs.Count, orgs.Count);
rootOrgs.ForEach(org =>
{
var dbOrg = orgs.FirstOrDefault(x => x.Id == org.Id);
Assert.NotNull(dbOrg);
Assert.AreEqual(org.Name, dbOrg.Name);
Assert.AreEqual(org.ParentId, dbOrg.ParentId);
});
}
public async Task <MessageResponse> Get(MessageRequest request)
{
var user = ValidateAndGetCurrentUser();
if (user == null)
{
throw new UnauthorizedAccessException("Must be authenticated user.");
}
var messages = await Db.SelectAsync <ConversationMessage>(m => (m.ToUserId == user.Id && m.FromUserId == request.ToUserId) || (m.ToUserId == request.ToUserId && m.FromUserId == user.Id));
var propertyMessages = messages.Where(m => m.PropertyId == request.PropertyId).ToList();
return(new MessageResponse {
Message = propertyMessages
});
}
public async void NotificationMessage(ConversationMessage message)
{
var messages = await Db.SelectAsync <ConversationMessage>(m => (m.ToUserId == message.ToUserId || m.FromUserId == message.ToUserId) && m.PropertyId == message.PropertyId);
var user = await Db.SelectAsync <User>(u => u.Id == message.ToUserId);
var estimate = await Db.SelectAsync <Estimate>(e => e.Id == message.PropertyId);
var fromUser = ValidateAndGetCurrentUser();
var toUser = user.FirstOrDefault();
var property = estimate.FirstOrDefault();
if (toUser != null && toUser.CanReceiveEmail == true)
{
if (fromUser.IsAgent == true)
{
var fromUserMessagesCount = messages.Count(m => m.FromUserId == fromUser.Id);
if (fromUserMessagesCount == 1)
{
// Notify Homeowner if this is the first Agent response
SendNotificationMessageEmail(message.Body, property, fromUser, toUser);
}
if (messages.Count > 1)
{
var messageRepliedTo = messages[messages.Count - 2];
if (messageRepliedTo.FromUserId == message.ToUserId)
{
// Notify Homeowner if this is the Agent response on your message
SendNotificationMessageEmail(message.Body, property, fromUser, toUser);
}
}
}
if (toUser.IsAgent == true)
{
// Notify Agent about every message
SendNotificationMessageEmail(message.Body, property, fromUser, toUser);
}
}
}
public async Task <GetOrganizationAdminResponse> Get(GetOrganizationAdmin request)
{
var user = GetUser();
AssertOrganizationModerator(Db, request.Id, user, out var org, out var orgMember);
return(new GetOrganizationAdminResponse
{
Labels = await Db.SelectAsync(Db.From <OrganizationLabel>().Where(x => x.OrganizationId == org.Id)),
Members = await Db.SelectAsync(Db.From <OrganizationMember>().Where(x => x.OrganizationId == org.Id).OrderByDescending(x => new{ x.IsOwner, x.IsModerator })),
MemberInvites = await Db.SelectAsync <OrganizationMemberInvite>(x => x.OrganizationId == org.Id && x.Approved == null && x.OrganizationMemberId == null),
ReportedPosts = await Db.SelectAsync <PostReportInfo>(Db.From <PostReport>()
.Join <Post>().Where(x => x.Acknowledged == null && x.Dismissed == null)),
ReportedPostComments = await Db.SelectAsync <PostCommentReportInfo>(Db.From <PostCommentReport>()
.Join <PostComment>().Where(x => x.Acknowledged == null && x.Dismissed == null)),
});
}
private async Task <GetOrganizationResponse> GetOrganization(Organization organization)
{
PostServicesBase.AssertCanViewOrganization(Db, organization, SessionAs <AuthUserSession>(), out _);
var members = await Db.SelectAsync <OrganizationMember>(x => x.OrganizationId == organization.Id && (x.IsOwner || x.IsModerator));
return(new GetOrganizationResponse
{
Cache = Stopwatch.GetTimestamp(),
Id = organization.Id,
Slug = organization.Slug,
Organization = organization,
Labels = await Db.SelectAsync <OrganizationLabel>(x => x.OrganizationId == organization.Id),
Categories = (await Db.SelectAsync <Category>(x => x.OrganizationId == organization.Id && x.Deleted == null)).OrderBy(x => x.Name).ToList(),
Owners = members.Where(x => x.IsOwner).ToList(),
Moderators = members.Where(x => x.IsModerator).ToList(),
MembersCount = PostServicesBase.GetOrganizationMembersCount(organization.Id), //display only, can be stale
});
}
public async void Get(UnsubFromAccessToken request)
{
var estimates = await Db.SelectAsync <Estimate>(e => e.AccessToken == request.AccessToken);
if (estimates == null || estimates.Count < 1)
{
return;
}
//pull the first one (there should only be one...)
var estimate = estimates.FirstOrDefault();
if (estimate == null)
{
return;
}
estimate.AssociatedEmail = null;
Db.Save(estimate);
}
public async Task <HttpResult> Get(Statistics request)
{
var from = DateTime.SpecifyKind(Convert.ToDateTime(request.DateFrom), DateTimeKind.Utc);
var to = DateTime.SpecifyKind(Convert.ToDateTime(request.DateTo), DateTimeKind.Utc).EndOfDay();
var query = Db.From <Sms>()
.Join <Sms, Country>()
.Select <Sms, Country>((sms, country) => new
{
Mcc = country.Mcc,
Day = Sql.Custom("DATE(created_at)"),
Count = Sql.Count("*"),
PricePerSms = Sql.Max(country.PricePerSMS),
TotalPrice = Sql.Sum(sms.Price)
})
.Where <Sms>(sms => from <= sms.CreatedAt && sms.CreatedAt <= to)
.And <Country>(country => request.MccList.IsNullOrEmpty() || Sql.In(country.Mcc, request.MccList))
.GroupBy(sms => new { Day = Sql.Custom("DATE(created_at)"), sms.Country.Mcc });
return(Utils.UsingDateFormatOnly(await Db.SelectAsync <StatisticsRecord>(query)));
}
public async Task <GetUserInfoResponse> Any(GetUserInfo request)
{
var user = await Db.SingleAsync <CustomUserAuth>(x => x.UserName == request.UserName);
if (user == null)
{
throw HttpError.NotFound("User not found");
}
var techStacks = await Db.SelectAsync(Db.From <TechnologyStack>()
.Where(x => x.CreatedBy == request.UserName)
.OrderByDescending(x => x.Id));
var favStacks = await Db.SelectAsync(
Db.From <TechnologyStack>()
.Join <UserFavoriteTechnologyStack>()
.Where <UserFavoriteTechnologyStack>(u => u.UserId == user.Id.ToString()));
favStacks.Each(x => x.Details = null); //lighten payload
var favTechs = await Db.SelectAsync(
Db.From <Technology>()
.Join <UserFavoriteTechnology>()
.Where <UserFavoriteTechnology>(u => u.UserId == user.Id.ToString()));
var userActivity = await Db.SingleByIdAsync <UserActivity>(user.Id);
return(new GetUserInfoResponse
{
Created = DateTime.UtcNow,
Id = user.Id,
UserName = user.UserName,
AvatarUrl = user.DefaultProfileUrl ?? $"/users/{user.UserName}/avatar",
TechStacks = techStacks,
FavoriteTechStacks = favStacks,
FavoriteTechnologies = favTechs,
UserActivity = userActivity,
});
}
public async Task <GetSentSMSResponse> Get(GetSentSMS request)
{
var take = request.Take ?? 10;
var skip = request.Skip ?? 0;
var from = DateTime.SpecifyKind(Convert.ToDateTime(request.DateTimeFrom), DateTimeKind.Utc);
var to = DateTime.SpecifyKind(Convert.ToDateTime(request.DateTimeTo), DateTimeKind.Utc);
Expression <Func <Sms, bool> > dateTimeCondition = sms => from <= sms.CreatedAt && sms.CreatedAt <= to;
var totalSmsRecords = await Db.CountAsync <Sms>(dateTimeCondition);
var smsRecords = await Db.SelectAsync(Db.From <Sms>()
.Where(dateTimeCondition)
.Skip(skip)
.Limit(take)
);
return(new GetSentSMSResponse()
{
Items = smsRecords,
TotalCount = totalSmsRecords
});
}
public async Task <SendSMSResponse> Get(SendSMS request)
{
var receiverCountry = CountryLookup.ByPhoneNumber(
await Db.SelectAsync <Country>(),
request.To.ReplaceAll("+", ""));
var sentState = await SmsSender.Send(request.From, request.To, request.Text);
var sms = request.ConvertTo <Sms>();
sms.CreatedAt = DateTime.Now.ToUniversalTime();
sms.State = sentState;
sms.Price = receiverCountry.PricePerSMS;
sms.CountryId = receiverCountry.Id;
await Db.SaveAsync(sms);
return(new SendSMSResponse
{
State = sms.State
});
}
public async Task <bool> Post(CastAVoteRequest req)
{
try
{
// first we check if the optionId provided is valid
var options = await Db.SelectAsync <PollOption>(po => po.PollId == req.PollId);
if (!options.Select(o => o.Id).Contains(req.OptionId))
{
throw new System.Exception("Invalid Option");
}
// check if the client has voted on this poll already
var existingVote = await Db.SingleAsync <Vote>(v => v.PollId == req.PollId && v.ClientId == req.ClientId);
if (existingVote != null)
{
// Reject If the client has already voted
throw new System.Exception("You have already voted");
}
else
{
var newVote = new Vote()
{
ClientId = req.ClientId,
PollId = req.PollId,
OptionId = req.OptionId
};
await Db.InsertAsync(newVote);
return(true);
}
}
catch (System.Exception e)
{
throw new System.Exception(e.Message);
}
}
// This method "ensures" that there are at least n users left of a specific role.
// It's invoked to enforce certain constraints like "there must always be at least one SuperAdmin defined."
private async Task <int> EnsureRoleCount(User.Role role, int count)
{
// Counter placeholder variable.
var superAdmins = 0;
// Iterate over each user and count where superadmin.
foreach (var user in await Db.SelectAsync <User>())
{
if (user.HasRole(role))
{
superAdmins++;
}
}
// Check if there is enough superadmins.
Logger.LogDebug($"There must be at least {count} users with the {role} role, found: {superAdmins}");
if (superAdmins < count)
{
throw new Exception(string.Format("There must be at least {0} users of {1} role.", count, role));
}
return(count);
}
public async Task <object> Get(UsersRequest request)
{
var users = await Db.SelectAsync <UserAuth>(Db.From <UserAuth>().OrderBy(u => u.UserName).ThenBy(u => u.DisplayName));
return(users.Map(u => new UserInfo().PopulateWithNonDefaultValues(u)));
}
public async Task <List <Country> > Get(Countries request)
{
return(await Db.SelectAsync <Country>());
}
public virtual async Task <T> GetEntityAsync <T>(string qry, object param)
{
return(await Db.SelectAsync <T>(qry, DbParameters.Create(param)));
}