public CategorialValue LikesRepostCommentDiagramData(int vkGroupId, AudienceFilteringResult filteringResult) { var result = new CategorialValue(); string userIds = filteringResult.IsAllMembers ? string.Empty : QueryArrayBuilder.GetString(filteringResult.Members.Select(m => m.VkMemberId).ToArray()); using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway()) { string creatorIdClause = filteringResult.IsAllMembers ? string.Empty : string.Format("AND creatorid = ANY({0})", userIds); string memberIdClause = filteringResult.IsAllMembers ? string.Empty : string.Format("AND vkmemberid = ANY({0})", userIds); string query = string.Format("SELECT COUNT(*)::integer FROM postcomment WHERE vkgroupid = {0} {1}", vkGroupId, creatorIdClause); result.CategoryA = dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COUNT(*)::integer FROM membershare WHERE vkgroupid = {0} {1} AND itemtype = {2}", vkGroupId, memberIdClause, (int)LikeShareType.Post); result.CategoryB = dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COUNT(*)::integer FROM memberlike WHERE vkgroupid = {0} {1} AND itemtype = {2}", vkGroupId, memberIdClause, (int)LikeShareType.Post); result.CategoryC = dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COUNT(*)::integer FROM post WHERE vkgroupid = {0} {1}", vkGroupId, creatorIdClause); result.CategoryD = dataGateway.Connection.Query <int>(query).SingleOrDefault(); } return(result); }
public CategorialValue GetTypeOfContentDiagramData(int vkGroupId, AudienceFilteringResult filteringResult) { var result = new CategorialValue(); string userIds = filteringResult.IsAllMembers ? string.Empty : QueryArrayBuilder.GetString(filteringResult.Members.Select(m => m.VkMemberId).ToArray()); int postCount; int photoCount; int videoCount; using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway()) { string creatorIdClause = filteringResult.IsAllMembers ? string.Empty : string.Format("AND creatorid = ANY({0})", userIds); string memberIdClause = filteringResult.IsAllMembers ? string.Empty : string.Format("AND vkmemberid = ANY({0})", userIds); string query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM postcomment WHERE vkgroupid = {0} {1} GROUP BY vkpostid) AS sub", vkGroupId, creatorIdClause); postCount = dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM memberlike WHERE vkgroupid = {0} {1} AND itemtype = 1 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); postCount += dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM membershare WHERE vkgroupid = {0} {1} AND itemtype = 1 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); postCount += dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM videocomment WHERE vkgroupid = {0} {1} GROUP BY vkvideoid) AS sub", vkGroupId, creatorIdClause); var nullableVideoCount = dataGateway.Connection.Query <int?>(query).SingleOrDefault(); videoCount = nullableVideoCount.HasValue ? (int)nullableVideoCount : 0; query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM memberlike WHERE vkgroupid = {0} {1} AND itemtype = 4 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); videoCount += dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM membershare WHERE vkgroupid = {0} {1} AND itemtype = 4 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); videoCount += dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM memberlike WHERE vkgroupid = {0} {1} AND itemtype = 3 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); photoCount = dataGateway.Connection.Query <int>(query).SingleOrDefault(); query = string.Format("SELECT COALESCE(SUM(sub.count)::integer, 0) FROM (SELECT COUNT(*) FROM membershare WHERE vkgroupid = {0} {1} AND itemtype = 3 GROUP BY itemid) AS sub", vkGroupId, memberIdClause); photoCount += dataGateway.Connection.Query <int>(query).SingleOrDefault(); } result.CategoryA = postCount; result.CategoryB = videoCount; result.CategoryC = photoCount; return(result); }
public IList <MemberSubscriptionInfo> GetMemberSubscriptionInfo(int vkGroupId, AudienceFilteringResult filteringResult, int limit = 15) { string userIds = filteringResult.IsAllMembers ? string.Empty : QueryArrayBuilder.GetString(filteringResult.Members.Select(m => m.VkMemberId).ToArray()); string memberIdClause = filteringResult.IsAllMembers ? string.Empty : string.Format("AND vkmemberid = ANY({0})", userIds); using (IDataGateway dataGateway = this.dataGatewayProvider.GetDataGateway()) { string query = string.Format( @"with populargroups AS ( SELECT subscribedvkgroupid, COUNT(*)::integer FROM membersubscriptions WHERE vkgroupid = {0} AND subscribedvkgroupid <> {0} {1} GROUP BY subscribedvkgroupid ORDER BY count DESC LIMIT {2} ), select distinct vkr.namegroup, vkr.screenname, vkr.vkgroupid, vkr.photo, pg.count from populargroups pg inner join vkgroupreference vkr on (pg.subscribedvkgroupid = vkr.vkgroupid)", vkGroupId, memberIdClause, limit); return(dataGateway.Connection.Query <MemberSubscriptionInfo>(query).ToList()); } }