func TestUserCountsWithPostsByDay(t *testing.T) {
Setup()
t1 := &model.Team{}
t1.DisplayName = "DisplayName"
t1.Name = "a" + model.NewId() + "b"
t1.Email = model.NewId() + "@nowhere.com"
t1.Type = model.TEAM_OPEN
t1 = Must(store.Team().Save(t1)).(*model.Team)
c1 := &model.Channel{}
c1.TeamId = t1.Id
c1.DisplayName = "Channel2"
c1.Name = "a" + model.NewId() + "b"
c1.Type = model.CHANNEL_OPEN
c1 = Must(store.Channel().Save(c1)).(*model.Channel)
o1 := &model.Post{}
o1.ChannelId = c1.Id
o1.UserId = model.NewId()
o1.CreateAt = utils.MillisFromTime(utils.Yesterday())
o1.Message = "a" + model.NewId() + "b"
o1 = Must(store.Post().Save(o1)).(*model.Post)
o1a := &model.Post{}
o1a.ChannelId = c1.Id
o1a.UserId = model.NewId()
o1a.CreateAt = o1.CreateAt
o1a.Message = "a" + model.NewId() + "b"
o1a = Must(store.Post().Save(o1a)).(*model.Post)
o2 := &model.Post{}
o2.ChannelId = c1.Id
o2.UserId = model.NewId()
o2.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24)
o2.Message = "a" + model.NewId() + "b"
o2 = Must(store.Post().Save(o2)).(*model.Post)
o2a := &model.Post{}
o2a.ChannelId = c1.Id
o2a.UserId = o2.UserId
o2a.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24)
o2a.Message = "a" + model.NewId() + "b"
o2a = Must(store.Post().Save(o2a)).(*model.Post)
if r1 := <-store.Post().AnalyticsUserCountsWithPostsByDay(t1.Id); r1.Err != nil {
t.Fatal(r1.Err)
} else {
row1 := r1.Data.(model.AnalyticsRows)[0]
if row1.Value != 2 {
t.Fatal("wrong value")
}
row2 := r1.Data.(model.AnalyticsRows)[1]
if row2.Value != 1 {
t.Fatal("wrong value")
}
}
}
func TestUserCountsWithPostsByDay(t *testing.T) {
Setup()
team := &model.Team{DisplayName: "Name", Name: "z-z-" + model.NewId() + "a", Email: "*****@*****.**", Type: model.TEAM_OPEN}
team = Client.Must(Client.CreateTeam(team)).Data.(*model.Team)
user := &model.User{TeamId: team.Id, Email: model.NewId() + "*****@*****.**", Nickname: "Corey Hulen", Password: "******"}
user = Client.Must(Client.CreateUser(user, "")).Data.(*model.User)
store.Must(Srv.Store.User().VerifyEmail(user.Id))
Client.LoginByEmail(team.Name, user.Email, "pwd")
channel1 := &model.Channel{DisplayName: "TestGetPosts", Name: "a" + model.NewId() + "a", Type: model.CHANNEL_PRIVATE, TeamId: team.Id}
channel1 = Client.Must(Client.CreateChannel(channel1)).Data.(*model.Channel)
post1 := &model.Post{ChannelId: channel1.Id, Message: "a" + model.NewId() + "a"}
post1 = Client.Must(Client.CreatePost(post1)).Data.(*model.Post)
// manually update creation time, since it's always set to 0 upon saving and we only retrieve posts < today
Srv.Store.(*store.SqlStore).GetMaster().Exec("UPDATE Posts SET CreateAt = :CreateAt WHERE ChannelId = :ChannelId",
map[string]interface{}{"ChannelId": channel1.Id, "CreateAt": utils.MillisFromTime(utils.Yesterday())})
if _, err := Client.GetTeamAnalytics(team.Id, "user_counts_with_posts_day"); err == nil {
t.Fatal("Shouldn't have permissions")
}
c := &Context{}
c.RequestId = model.NewId()
c.IpAddress = "cmd_line"
UpdateRoles(c, user, model.ROLE_SYSTEM_ADMIN)
Client.LoginByEmail(team.Name, user.Email, "pwd")
if result, err := Client.GetTeamAnalytics(team.Id, "user_counts_with_posts_day"); err != nil {
t.Fatal(err)
} else {
rows := result.Data.(model.AnalyticsRows)
if rows[0].Value != 1 {
t.Log(rows.ToJson())
t.Fatal()
}
}
}
func TestUserCountsWithPostsByDay(t *testing.T) {
th := Setup().InitBasic().InitSystemAdmin()
// manually update creation time, since it's always set to 0 upon saving and we only retrieve posts < today
Srv.Store.(*store.SqlStore).GetMaster().Exec("UPDATE Posts SET CreateAt = :CreateAt WHERE ChannelId = :ChannelId",
map[string]interface{}{"ChannelId": th.BasicChannel.Id, "CreateAt": utils.MillisFromTime(utils.Yesterday())})
if _, err := th.BasicClient.GetTeamAnalytics(th.BasicTeam.Id, "user_counts_with_posts_day"); err == nil {
t.Fatal("Shouldn't have permissions")
}
if result, err := th.SystemAdminClient.GetTeamAnalytics(th.BasicTeam.Id, "user_counts_with_posts_day"); err != nil {
t.Fatal(err)
} else {
rows := result.Data.(model.AnalyticsRows)
if rows[0].Value != 1 {
t.Log(rows.ToJson())
t.Fatal()
}
}
}
func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel {
storeChannel := make(StoreChannel)
go func() {
result := StoreResult{}
query :=
`SELECT
Name, COUNT(Value) AS Value
FROM
(SELECT
DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
'1' AS Value
FROM
Posts, Channels
WHERE
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
LIMIT 30`
if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
query =
`SELECT
Name, COUNT(Value) AS Value
FROM
(SELECT
TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name,
'1' AS Value
FROM
Posts, Channels
WHERE
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
LIMIT 30`
}
end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
start := utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -31)))
var rows model.AnalyticsRows
_, err := s.GetReplica().Select(
&rows,
query,
map[string]interface{}{"TeamId": teamId, "StartTime": start, "EndTime": end})
if err != nil {
result.Err = model.NewLocAppError("SqlPostStore.AnalyticsPostCountsByDay", "store.sql_post.analytics_posts_count_by_day.app_error", nil, err.Error())
} else {
result.Data = rows
}
storeChannel <- result
close(storeChannel)
}()
return storeChannel
}
func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChannel {
storeChannel := make(StoreChannel)
go func() {
result := StoreResult{}
query :=
`SELECT
t1.Name, COUNT(t1.UserId) AS Value
FROM
(SELECT DISTINCT
DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
Posts.UserId
FROM
Posts, Channels
WHERE
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
LIMIT 30`
if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
query =
`SELECT
TO_CHAR(t1.Name, 'YYYY-MM-DD') AS Name, COUNT(t1.UserId) AS Value
FROM
(SELECT DISTINCT
DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name,
Posts.UserId
FROM
Posts, Channels
WHERE
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
LIMIT 30`
}
end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
var rows model.AnalyticsRows
_, err := s.GetReplica().Select(
&rows,
query,
map[string]interface{}{"TeamId": teamId, "EndTime": end})
if err != nil {
result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "We couldn't get user counts with posts", err.Error())
} else {
result.Data = rows
}
storeChannel <- result
close(storeChannel)
}()
return storeChannel
}