From ae5d1898037be4f59bf6517ad76b13cc16f595ce Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Thu, 22 Oct 2015 18:04:06 -0700 Subject: Adding analytics tab --- store/sql_post_store.go | 102 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 102 insertions(+) (limited to 'store/sql_post_store.go') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index 6971de9d7..19a4e0adb 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -571,3 +571,105 @@ func (s SqlPostStore) GetForExport(channelId string) StoreChannel { return storeChannel } + +func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChannel { + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + `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 + AND Channels.TeamId = :TeamId + ORDER BY Name DESC) AS t1 + GROUP BY Name + ORDER BY Name DESC + LIMIT 30`, + map[string]interface{}{"TeamId": teamId}) + 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 +} + +func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + `SELECT + DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, + COUNT(Posts.Id) AS Value + FROM + Posts, + Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId + GROUP BY Name + ORDER BY Name DESC + LIMIT 30`, + map[string]interface{}{"TeamId": teamId}) + if err != nil { + result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "We couldn't get post counts by day", err.Error()) + } else { + result.Data = rows + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + +func (s SqlPostStore) AnalyticsPostCount(teamId string) StoreChannel { + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + v, err := s.GetReplica().SelectInt( + `SELECT + COUNT(Posts.Id) AS Value + FROM + Posts, + Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId`, + map[string]interface{}{"TeamId": teamId}) + if err != nil { + result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCount", "We couldn't get post counts", err.Error()) + } else { + result.Data = v + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} -- cgit v1.2.3-1-g7c22 From d53de8421421f4251cc4cff2118814246548d687 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Mon, 26 Oct 2015 22:46:19 -0700 Subject: Fixing postgres --- store/sql_post_store.go | 57 ++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 49 insertions(+), 8 deletions(-) (limited to 'store/sql_post_store.go') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index d1f308b5a..f21bbee7a 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -610,9 +610,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan go func() { result := StoreResult{} - var rows model.AnalyticsRows - _, err := s.GetReplica().Select( - &rows, + query := `SELECT t1.Name, COUNT(t1.UserId) AS Value FROM @@ -627,7 +625,31 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan ORDER BY Name DESC) AS t1 GROUP BY Name ORDER BY Name DESC - LIMIT 30`, + LIMIT 30` + + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + query = + `SELECT + t1.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 + AND Channels.TeamId = :TeamId + ORDER BY Name DESC) AS t1 + GROUP BY Name + ORDER BY Name DESC + LIMIT 30` + } + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + query, map[string]interface{}{"TeamId": teamId}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "We couldn't get user counts with posts", err.Error()) @@ -648,9 +670,7 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { go func() { result := StoreResult{} - var rows model.AnalyticsRows - _, err := s.GetReplica().Select( - &rows, + query := `SELECT DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, COUNT(Posts.Id) AS Value @@ -662,7 +682,28 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { AND Channels.TeamId = :TeamId GROUP BY Name ORDER BY Name DESC - LIMIT 30`, + LIMIT 30` + + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + query = + `SELECT + DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name, + COUNT(Posts.Id) AS Value + FROM + Posts, + Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId + GROUP BY Name + ORDER BY Name DESC + LIMIT 30` + } + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + query, map[string]interface{}{"TeamId": teamId}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "We couldn't get post counts by day", err.Error()) -- cgit v1.2.3-1-g7c22 From 399e9c6f4bbed7f9eac0a75242ec75e4b0d2bb59 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Tue, 27 Oct 2015 09:55:19 -0700 Subject: PLT-25 fixing stats for postgres --- store/sql_post_store.go | 40 +++++++++++++++++++++++----------------- 1 file changed, 23 insertions(+), 17 deletions(-) (limited to 'store/sql_post_store.go') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index f21bbee7a..7894ff488 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -630,7 +630,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = `SELECT - t1.Name, COUNT(t1.UserId) AS Value + 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, @@ -650,7 +650,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan _, err := s.GetReplica().Select( &rows, query, - map[string]interface{}{"TeamId": teamId}) + map[string]interface{}{"TeamId": teamId, "Time": model.GetMillis() - 1000*60*60*24*31}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "We couldn't get user counts with posts", err.Error()) } else { @@ -672,14 +672,17 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { query := `SELECT - DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, - COUNT(Posts.Id) AS Value + Name, COUNT(Value) AS Value FROM - Posts, - Channels - WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId + (SELECT + DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, + '1' AS Value + FROM + Posts, Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId + AND Posts.CreateAt >:Time) AS t1 GROUP BY Name ORDER BY Name DESC LIMIT 30` @@ -687,14 +690,17 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = `SELECT - DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name, - COUNT(Posts.Id) AS Value + Name, COUNT(Value) AS Value FROM - Posts, - Channels - WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId + (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 + AND Channels.TeamId = :TeamId + AND Posts.CreateAt > :Time) AS t1 GROUP BY Name ORDER BY Name DESC LIMIT 30` @@ -704,7 +710,7 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { _, err := s.GetReplica().Select( &rows, query, - map[string]interface{}{"TeamId": teamId}) + map[string]interface{}{"TeamId": teamId, "Time": model.GetMillis() - 1000*60*60*24*31}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "We couldn't get post counts by day", err.Error()) } else { -- cgit v1.2.3-1-g7c22