public double GetInvolmentRate(int projectId, DateRange dateRange)
{
var vkGroup = this.projectRepository.GetVkGroup(projectId);
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
double postsCount = dataGateway.GetEntities <Post>().Count(x => x.VkGroupId == vkGroup.Id);
var usersInfo = dataGateway.GetEntities <MembersMetaInfo>().OrderByDescending(u => u.Id).Take(1).SingleOrDefault();
return(usersInfo != null && usersInfo.Count > 0 ? postsCount / usersInfo.Count : 0);
}
}
public double GetUgcRate(int projectId, DateRange dateRange)
{
var vkGroup = this.projectRepository.GetVkGroup(projectId);
IList <long> adminIds = this.groupRepository.GetAdministratorIds(vkGroup.Id, true);
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
double postsCount = dataGateway.GetEntities <Post>().Count(x => x.VkGroupId == vkGroup.Id);
int adminPostsCount = dataGateway.GetEntities <Post>().Count(x => x.VkGroupId == vkGroup.Id && adminIds.Contains(x.CreatorId));
double userPosts = postsCount - adminPostsCount;
return(postsCount > 0 ? userPosts / postsCount : 0);
}
}
public PostComment GetPostComment(int vkGroupId, string vkId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <PostComment>().SingleOrDefault(p => p.VkGroupId == vkGroupId && p.VkId == vkId));
}
}
public API.User GetUserById(int userId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.User>().FirstOrDefault(u => u.Id == userId));
}
}
public IAccount GetAccount(int accountId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.IAccount>().FirstOrDefault(x => x.Id == accountId));
}
}
public IList <IAccount> GetAccounts()
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.IAccount>().ToList());
}
}
public VkGroup GetGroupById(int id)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <VkGroup>().SingleOrDefault(g => g.Id == id));
}
}
public IList <Project> GetByAccountId(int accountId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Project>().Where(p => p.AccountId == accountId).ToList());
}
}
public Topic GetTopic(int vkGroupId, string vkId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Topic>().SingleOrDefault(p => p.VkGroupId == vkGroupId && p.VkId == vkId));
}
}
public IList <Topic> GetTopicsByVkGroupId(int vkGroupId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Topic>().Where(p => p.VkGroupId == vkGroupId).ToList());
}
}
public VideoComment GetVideoCommentByVkGroupId(int vkGroupId, string vkId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <VideoComment>().SingleOrDefault(vc => vc.VkGroupId == vkGroupId && vc.VkId == vkId));
}
}
public IList <API.User> GetUsers(int accountId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.User>().Where(u => u.Account.Id == accountId).ToList());
}
}
public IList <API.User> GetUsers()
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.User>().ToList());
}
}
public IList <PostComment> GetPostCommentsByVkGroupId(int vkGroupId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <PostComment>().Where(p => p.VkGroupId == vkGroupId).ToList());
}
}
public IList <VkGroup> GetGroups()
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <VkGroup>().ToList());
}
}
public IEnumerable <Project> GetAll()
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Project>());
}
}
public IList <Administrator> GetAdminstrators(int id)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Administrator>().Where(a => a.VkGroupId == id).ToList());
}
}
public Photo GetPhotoByIdInAlbum(int vkGroupId, string vkAlbumId, string vkId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <Photo>().SingleOrDefault(p => p.VkGroupId == vkGroupId && p.AlbumId == vkAlbumId && p.VkId == vkId));
}
}
public VkGroup GetVkGroup(int projectId)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
var project = dataGateway.GetEntities <Project>().SingleOrDefault(p => p.Id == projectId);
return(project != null ? project.VkGroup : null);
}
}
public DateTime?GetGroupCreationDate(int id)
{
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
var firstPost = dataGateway.GetEntities <Post>().Where(p => p.VkGroupId == id).OrderBy(p => p.VkId).Take(1).SingleOrDefault();
return(firstPost == null ? (DateTime?)null : firstPost.PostedDate);
}
}
public API.User GetUserByEmail(string email)
{
string loweredEmail = email.ToLower();
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
return(dataGateway.GetEntities <API.User>().FirstOrDefault(u => loweredEmail == u.Email.ToLower()));
}
}
public IList <long> GetAdministratorIds(int vkGroupId, bool appendGroupId = false)
{
IList <long> adminIds;
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
adminIds = dataGateway.GetEntities <Administrator>().Where(x => x.VkGroupId == vkGroupId).Select(a => a.UserId).ToList();
}
if (appendGroupId)
{
adminIds.Add(-vkGroupId);
}
return(adminIds);
}
public double GetResponseRate(int projectId, DateRange dateRange)
{
var vkGroup = this.projectRepository.GetVkGroup(projectId);
IList <long> adminIds = this.groupRepository.GetAdministratorIds(vkGroup.Id, true);
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
const string Query = @"
select count(distinct pc.vkpostid) from postcomment pc
where
pc.vkgroupid = @vkgroupid
AND pc.creatorid in @adminids
AND pc.vkpostid in (select p.vkid from post p where p.vkgroupid = @vkgroupid AND p.creatorid not in @adminids)
";
double postsCount = dataGateway.GetEntities <Post>().Count(x => x.VkGroupId == vkGroup.Id);
long postsWithAdminCommentsCount = dataGateway.Connection.Query <long>(Query, new { vkgroupid = vkGroup.Id, adminids = adminIds }).Single();
return(postsCount > 0 ? postsWithAdminCommentsCount / postsCount : 0);
}
}
private double GetPostsForAnalogPeriod(IDataGateway dataGateway, int vkGroupId, DateRange range, Expression <Func <Post, bool> > additionalCondition)
{
var query = dataGateway.GetEntities <Post>().Where(x => x.VkGroupId == vkGroupId && x.PostedDate.Date < DateTime.Now.Date);
var anyPost = query.OrderBy(x => x.PostedDate).FirstOrDefault();
if (anyPost != null)
{
var firstPostDate = anyPost.PostedDate.Date;
var totalPosts = query.Count(additionalCondition);
var days = (DateTime.Now.Date - firstPostDate).Days;
if (days >= range.DaysInRange)
{
var postsPerDay = ((double)totalPosts / days) * range.DaysInRange;
return(postsPerDay);
}
// N/A
return(-1);
}
return(0);
}
public IEnumerable <IEnumerable <PointInTime> > GetInteractionRate(int vkGroupId, DateRange range, Periodicity periodicity)
{
var creationDate = this.groupRepository.GetGroupCreationDate(vkGroupId);
if (!creationDate.HasValue)
{
return(null);
}
using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway())
{
var posts = dataGateway.GetEntities <Post>()
.Where(
x =>
x.VkGroupId == vkGroupId && x.PostedDate >= range.From && x.PostedDate <= range.To)
.ToList();
var membersCount = dataGateway.GetEntities <MembersMetaInfo>()
.Where(
x =>
x.VkGroupId == vkGroupId && x.PostedDate >= range.From && x.PostedDate <= range.To)
.ToList();
var share = new List <TempShareModel>();
var video = dataGateway.GetEntities <Video>()
.Where(
x =>
x.VkGroupId == vkGroupId && x.PostedDate >= range.From && x.PostedDate <= range.To)
.ToList();
var photo = dataGateway.GetEntities <Photo>()
.Where(
x =>
x.VkGroupId == vkGroupId && x.PostedDate >= range.From && x.PostedDate <= range.To)
.ToList();
string str = @"SELECT * FROM membershare WHERE vkgroupid = @groupId AND itemid = ANY(SELECT vkid::integer FROM post WHERE vkgroupid = @groupId AND posteddate BETWEEN @from AND @to)";
var postShare = dataGateway.Connection.Query <MemberShare>(str, new { groupId = vkGroupId, from = range.From, to = range.To }).ToList();
str = @"SELECT * FROM membershare WHERE vkgroupid = @groupId AND itemid = ANY(SELECT vkid::integer FROM video WHERE vkgroupid = @groupId AND posteddate BETWEEN @from AND @to)";
var videoShare = dataGateway.Connection.Query <MemberShare>(str, new { groupId = vkGroupId, from = range.From, to = range.To }).ToList();
str = @"SELECT * FROM membershare WHERE vkgroupid = @groupId AND itemid = ANY(SELECT vkid::integer FROM photo WHERE vkgroupid = @groupId AND posteddate BETWEEN @from AND @to)";
var photoShare = dataGateway.Connection.Query <MemberShare>(str, new { groupId = vkGroupId, from = range.From, to = range.To }).ToList();
for (int i = 0; i < posts.Count; i++)
{
Post post = posts[i];
var ps = postShare.Where(x => x.ItemId == int.Parse(post.VkId)).ToList();
if (ps.Count > 0)
{
share.AddRange(ps.Select(x => new TempShareModel()
{
Id = x.Id, PostedDate = post.PostedDate
}));
post.SharesCount++;
}
}
for (int i = 0; i < video.Count; i++)
{
var vp = videoShare.Where(x => x.ItemId == int.Parse(video[i].VkId)).ToList();
if (vp.Count > 0)
{
share.AddRange(vp.Select(x => new TempShareModel()
{
Id = x.Id, PostedDate = video[i].PostedDate
}));
video[i].ShareCount++;
}
}
for (int i = 0; i < photo.Count; i++)
{
var phs = photoShare.Where(x => x.ItemId == int.Parse(photo[i].VkId)).ToList();
if (phs.Count > 0)
{
share.AddRange(phs.Select(x => new TempShareModel()
{
Id = x.Id, PostedDate = photo[i].PostedDate
}));
photo[i].ShareCount++;
}
}
int count = membersCount.Count > 0
? membersCount.Last().Count
: this.rawDataProvider.GetMembersCount(vkGroupId);
IEnumerable <PointInTime> ir = this.GetIrEntityForChart(posts, count, range, periodicity);
IEnumerable <PointInTime> comments = this.entitiesForChartProvider.GetEntitiesForChart(posts, range, periodicity, e => e.Sum(x => x.CommentsCount));
IEnumerable <PointInTime> likes = this.entitiesForChartProvider.GetEntitiesForChart(posts, range, periodicity, e => e.Sum(x => x.LikesCount));
IEnumerable <PointInTime> shares = this.entitiesForChartProvider.GetEntitiesForChart(share, range, periodicity, e => e.Count());
return(new List <IEnumerable <PointInTime> > {
ir, comments, likes, shares
});
}
}
