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