From 399e9c6f4bbed7f9eac0a75242ec75e4b0d2bb59 Mon Sep 17 00:00:00 2001
From: =Corey Hulen <corey@hulen.com>
Date: Tue, 27 Oct 2015 09:55:19 -0700
Subject: PLT-25 fixing stats for postgres

---
 store/sql_post_store.go      | 40 +++++++++++++++++++++++-----------------
 store/sql_post_store_test.go | 41 +++++++++++++++++++----------------------
 2 files changed, 42 insertions(+), 39 deletions(-)

(limited to 'store')

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 {
diff --git a/store/sql_post_store_test.go b/store/sql_post_store_test.go
index 6795c0663..872423c5a 100644
--- a/store/sql_post_store_test.go
+++ b/store/sql_post_store_test.go
@@ -672,21 +672,22 @@ func TestPostCountsByDay(t *testing.T) {
 	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 * 2)
-	// 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 * 2)
-	// o2a.Message = "a" + model.NewId() + "b"
-	// o2a = Must(store.Post().Save(o2a)).(*model.Post)
+	o2 := &model.Post{}
+	o2.ChannelId = c1.Id
+	o2.UserId = model.NewId()
+	o2.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24 * 2)
+	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 * 2)
+	o2a.Message = "a" + model.NewId() + "b"
+	o2a = Must(store.Post().Save(o2a)).(*model.Post)
 
 	time.Sleep(1 * time.Second)
+	t.Log(t1.Id)
 
 	if r1 := <-store.Post().AnalyticsPostCountsByDay(t1.Id); r1.Err != nil {
 		t.Fatal(r1.Err)
@@ -697,20 +698,16 @@ func TestPostCountsByDay(t *testing.T) {
 			t.Fatal("wrong value")
 		}
 
-		// row2 := r1.Data.(model.AnalyticsRows)[1]
-		// if row2.Value != 2 {
-		// 	t.Fatal("wrong value")
-		// }
+		row2 := r1.Data.(model.AnalyticsRows)[1]
+		if row2.Value != 2 {
+			t.Fatal("wrong value")
+		}
 	}
 
 	if r1 := <-store.Post().AnalyticsPostCount(t1.Id); r1.Err != nil {
 		t.Fatal(r1.Err)
 	} else {
-		// if r1.Data.(int64) != 4 {
-		// 	t.Fatal("wrong value")
-		// }
-
-		if r1.Data.(int64) != 2 {
+		if r1.Data.(int64) != 4 {
 			t.Fatal("wrong value")
 		}
 	}
-- 
cgit v1.2.3-1-g7c22