From 2a26d857574f2160e3ee5538ad3a84ec47082f86 Mon Sep 17 00:00:00 2001 From: JoramWilander Date: Thu, 21 Jan 2016 12:14:17 -0500 Subject: Generalize analytics server functions and begin componentizing client analytics controls --- store/sql_channel_store.go | 16 +++++++------- store/sql_post_store.go | 52 ++++++++++++++++++++++++++++++++-------------- store/sql_user_store.go | 27 ++++++++++++++++++++++++ store/store.go | 1 + 4 files changed, 71 insertions(+), 25 deletions(-) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index 4585647de..336398ae7 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -869,15 +869,13 @@ func (s SqlChannelStore) AnalyticsTypeCount(teamId string, channelType string) S go func() { result := StoreResult{} - v, err := s.GetReplica().SelectInt( - `SELECT - COUNT(Id) AS Value - FROM - Channels - WHERE - TeamId = :TeamId - AND Type = :ChannelType`, - map[string]interface{}{"TeamId": teamId, "ChannelType": channelType}) + query := "SELECT COUNT(Id) AS Value FROM Channels WHERE Type = :ChannelType" + + if len(teamId) > 0 { + query += " AND TeamId = :TeamId" + } + + v, err := s.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId, "ChannelType": channelType}) if err != nil { result.Err = model.NewAppError("SqlChannelStore.AnalyticsTypeCount", "We couldn't get channel type counts", err.Error()) } else { diff --git a/store/sql_post_store.go b/store/sql_post_store.go index 40dca9930..e332858e4 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -805,9 +805,13 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan FROM Posts, Channels WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId - AND Posts.CreateAt <= :EndTime + Posts.ChannelId = Channels.Id` + + if len(teamId) > 0 { + query += " AND Channels.TeamId = :TeamId" + } + + query += ` AND Posts.CreateAt <= :EndTime ORDER BY Name DESC) AS t1 GROUP BY Name ORDER BY Name DESC @@ -824,9 +828,13 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan FROM Posts, Channels WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId - AND Posts.CreateAt <= :EndTime + Posts.ChannelId = Channels.Id` + + if len(teamId) > 0 { + query += " AND Channels.TeamId = :TeamId" + } + + query += ` AND Posts.CreateAt <= :EndTime ORDER BY Name DESC) AS t1 GROUP BY Name ORDER BY Name DESC @@ -869,9 +877,13 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { FROM Posts, Channels WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId - AND Posts.CreateAt <= :EndTime + Posts.ChannelId = Channels.Id` + + if len(teamId) > 0 { + query += " AND Channels.TeamId = :TeamId" + } + + query += ` AND Posts.CreateAt <= :EndTime AND Posts.CreateAt >= :StartTime) AS t1 GROUP BY Name ORDER BY Name DESC @@ -888,9 +900,13 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { FROM Posts, Channels WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId - AND Posts.CreateAt <= :EndTime + Posts.ChannelId = Channels.Id` + + if len(teamId) > 0 { + query += " AND Channels.TeamId = :TeamId" + } + + query += ` AND Posts.CreateAt <= :EndTime AND Posts.CreateAt >= :StartTime) AS t1 GROUP BY Name ORDER BY Name DESC @@ -924,16 +940,20 @@ func (s SqlPostStore) AnalyticsPostCount(teamId string) StoreChannel { go func() { result := StoreResult{} - v, err := s.GetReplica().SelectInt( + query := `SELECT COUNT(Posts.Id) AS Value FROM Posts, Channels WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId`, - map[string]interface{}{"TeamId": teamId}) + Posts.ChannelId = Channels.Id` + + if len(teamId) > 0 { + query += " AND Channels.TeamId = :TeamId" + } + + v, err := s.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCount", "We couldn't get post counts", err.Error()) } else { diff --git a/store/sql_user_store.go b/store/sql_user_store.go index 0f73f73c3..efd8b7f33 100644 --- a/store/sql_user_store.go +++ b/store/sql_user_store.go @@ -600,3 +600,30 @@ func (us SqlUserStore) PermanentDelete(userId string) StoreChannel { return storeChannel } + +func (us SqlUserStore) AnalyticsUniqueUserCount(teamId string) StoreChannel { + + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + query := "SELECT COUNT(DISTINCT Email) FROM Users" + + if len(teamId) > 0 { + query += " WHERE TeamId = :TeamId" + } + + v, err := us.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId}) + if err != nil { + result.Err = model.NewAppError("SqlUserStore.AnalyticsUniqueUserCount", "We couldn't get the unique user count", err.Error()) + } else { + result.Data = v + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} diff --git a/store/store.go b/store/store.go index 179cfecd7..b91b08f27 100644 --- a/store/store.go +++ b/store/store.go @@ -125,6 +125,7 @@ type UserStore interface { GetTotalActiveUsersCount() StoreChannel GetSystemAdminProfiles() StoreChannel PermanentDelete(userId string) StoreChannel + AnalyticsUniqueUserCount(teamId string) StoreChannel } type SessionStore interface { -- cgit v1.2.3-1-g7c22 From fc0b422c37f719a350b65e064cde7b506a88052e Mon Sep 17 00:00:00 2001 From: hmhealey Date: Sat, 23 Jan 2016 13:13:34 -0500 Subject: Changed GetMemberCount to stop including inactive users --- store/sql_channel_store.go | 11 ++++- store/sql_channel_store_test.go | 106 ++++++++++++++++++++++++++++++++++++++++ 2 files changed, 116 insertions(+), 1 deletion(-) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index 336398ae7..7400df8d2 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -582,7 +582,16 @@ func (s SqlChannelStore) GetMemberCount(channelId string) StoreChannel { go func() { result := StoreResult{} - count, err := s.GetReplica().SelectInt("SELECT count(*) FROM ChannelMembers WHERE ChannelId = :ChannelId", map[string]interface{}{"ChannelId": channelId}) + count, err := s.GetReplica().SelectInt(` + SELECT + count(*) + FROM + ChannelMembers, + Users + WHERE + ChannelMembers.UserId = Users.Id + AND ChannelMembers.ChannelId = :ChannelId + AND Users.DeleteAt = 0`, map[string]interface{}{"ChannelId": channelId}) if err != nil { result.Err = model.NewAppError("SqlChannelStore.GetMemberCount", "We couldn't get the channel member count", "channel_id="+channelId+", "+err.Error()) } else { diff --git a/store/sql_channel_store_test.go b/store/sql_channel_store_test.go index 8b22fbb7a..a3b0c2286 100644 --- a/store/sql_channel_store_test.go +++ b/store/sql_channel_store_test.go @@ -750,3 +750,109 @@ func TestChannelStoreIncrementMentionCount(t *testing.T) { t.Fatal("failed to update") } } + +func TestGetMemberCount(t *testing.T) { + Setup() + + teamId := model.NewId() + + c1 := model.Channel{ + TeamId: teamId, + DisplayName: "Channel1", + Name: "a" + model.NewId() + "b", + Type: model.CHANNEL_OPEN, + } + Must(store.Channel().Save(&c1)) + + c2 := model.Channel{ + TeamId: teamId, + DisplayName: "Channel2", + Name: "a" + model.NewId() + "b", + Type: model.CHANNEL_OPEN, + } + Must(store.Channel().Save(&c2)) + + t.Logf("c1.Id = %v", c1.Id) + + u1 := model.User{ + TeamId: teamId, + Email: model.NewId(), + DeleteAt: 0, + } + Must(store.User().Save(&u1)) + + m1 := model.ChannelMember{ + ChannelId: c1.Id, + UserId: u1.Id, + NotifyProps: model.GetDefaultChannelNotifyProps(), + } + Must(store.Channel().SaveMember(&m1)) + + if result := <-store.Channel().GetMemberCount(c1.Id); result.Err != nil { + t.Fatal("failed to get member count: %v", result.Err) + } else if result.Data.(int64) != 1 { + t.Fatal("got incorrect member count %v", result.Data) + } + + u2 := model.User{ + TeamId: teamId, + Email: model.NewId(), + DeleteAt: 0, + } + Must(store.User().Save(&u2)) + + m2 := model.ChannelMember{ + ChannelId: c1.Id, + UserId: u2.Id, + NotifyProps: model.GetDefaultChannelNotifyProps(), + } + Must(store.Channel().SaveMember(&m2)) + + if result := <-store.Channel().GetMemberCount(c1.Id); result.Err != nil { + t.Fatal("failed to get member count: %v", result.Err) + } else if result.Data.(int64) != 2 { + t.Fatal("got incorrect member count %v", result.Data) + } + + // make sure members of other channels aren't counted + u3 := model.User{ + TeamId: teamId, + Email: model.NewId(), + DeleteAt: 0, + } + Must(store.User().Save(&u3)) + + m3 := model.ChannelMember{ + ChannelId: c2.Id, + UserId: u3.Id, + NotifyProps: model.GetDefaultChannelNotifyProps(), + } + Must(store.Channel().SaveMember(&m3)) + + if result := <-store.Channel().GetMemberCount(c1.Id); result.Err != nil { + t.Fatal("failed to get member count: %v", result.Err) + } else if result.Data.(int64) != 2 { + t.Fatal("got incorrect member count %v", result.Data) + } + + // make sure inactive users aren't counted + u4 := model.User{ + TeamId: teamId, + Email: model.NewId(), + DeleteAt: 10000, + } + Must(store.User().Save(&u4)) + + m4 := model.ChannelMember{ + ChannelId: c1.Id, + UserId: u4.Id, + NotifyProps: model.GetDefaultChannelNotifyProps(), + } + Must(store.Channel().SaveMember(&m4)) + + if result := <-store.Channel().GetMemberCount(c1.Id); result.Err != nil { + t.Fatal("failed to get member count: %v", result.Err) + } else if result.Data.(int64) != 2 { + t.Fatal("got incorrect member count %v", result.Data) + } +} -- cgit v1.2.3-1-g7c22