- commit
- d08b816
- parent
- fd3f672
- author
- Eric Bower
- date
- 2024-11-30 18:11:18 +0000 UTC
chore(analytics): 404 table fix(analytics): `stats` command
4 files changed,
+58,
-11
M
db/db.go
+4,
-3
1@@ -164,9 +164,10 @@ type SummaryOpts struct {
2 }
3
4 type SummaryVisits struct {
5- Intervals []*VisitInterval `json:"intervals"`
6- TopUrls []*VisitUrl `json:"top_urls"`
7- TopReferers []*VisitUrl `json:"top_referers"`
8+ Intervals []*VisitInterval `json:"intervals"`
9+ TopUrls []*VisitUrl `json:"top_urls"`
10+ NotFoundUrls []*VisitUrl `json:"not_found_urls"`
11+ TopReferers []*VisitUrl `json:"top_referers"`
12 }
13
14 type PostAnalytics struct {
+46,
-6
1@@ -1119,15 +1119,49 @@ func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
2 return intervals, nil
3 }
4
5+func (me *PsqlDB) visitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
6+ where, with := visitFilterBy(opts)
7+ topUrls := fmt.Sprintf(`SELECT
8+ path,
9+ count(DISTINCT ip_address) as path_count
10+ FROM analytics_visits
11+ WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status = 404
12+ GROUP BY path
13+ ORDER BY path_count DESC
14+ LIMIT 10`, where)
15+
16+ intervals := []*db.VisitUrl{}
17+ rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
18+ if err != nil {
19+ return nil, err
20+ }
21+
22+ for rs.Next() {
23+ interval := &db.VisitUrl{}
24+ err := rs.Scan(
25+ &interval.Url,
26+ &interval.Count,
27+ )
28+ if err != nil {
29+ return nil, err
30+ }
31+
32+ intervals = append(intervals, interval)
33+ }
34+ if rs.Err() != nil {
35+ return nil, rs.Err()
36+ }
37+ return intervals, nil
38+}
39+
40 func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
41 topUrls := `SELECT
42 host,
43 count(DISTINCT ip_address) as host_count
44 FROM analytics_visits
45- WHERE user_id = $1 AND path <> '' AND status <> 404
46+ WHERE user_id = $1 AND host <> ''
47 GROUP BY host
48- ORDER BY host_count DESC
49- LIMIT 10`
50+ ORDER BY host_count DESC`
51
52 intervals := []*db.VisitUrl{}
53 rs, err := me.Db.Query(topUrls, opts.UserID)
54@@ -1164,15 +1198,21 @@ func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error)
55 return nil, err
56 }
57
58+ notFound, err := me.visitUrlNotFound(opts)
59+ if err != nil {
60+ return nil, err
61+ }
62+
63 refs, err := me.visitReferer(opts)
64 if err != nil {
65 return nil, err
66 }
67
68 return &db.SummaryVisits{
69- Intervals: visitors,
70- TopUrls: urls,
71- TopReferers: refs,
72+ Intervals: visitors,
73+ TopUrls: urls,
74+ TopReferers: refs,
75+ NotFoundUrls: notFound,
76 }, nil
77 }
78
+6,
-2
1@@ -275,9 +275,13 @@ func (m Model) renderSiteStats(summary *db.SummaryVisits) string {
2 str := m.shared.Styles.Label.SetString(fmt.Sprintf("%s by %s\n", name, by)).String()
3
4 if !strings.HasPrefix(m.repl, "post") {
5- str += "Top URLs\n"
6+ str += "\nTop URLs\n"
7 topUrlsTbl := common.VisitUrlsTbl(summary.TopUrls, m.shared.Styles.Renderer, m.viewport.Width)
8 str += topUrlsTbl.String()
9+
10+ str += "\nTop Not Found URLs\n"
11+ notFoundUrlsTbl := common.VisitUrlsTbl(summary.NotFoundUrls, m.shared.Styles.Renderer, m.viewport.Width)
12+ str += notFoundUrlsTbl.String()
13 }
14
15 str += "\nTop Referers\n"
16@@ -349,7 +353,7 @@ func (m Model) fetchPostStats(raw string, interval string) tea.Cmd {
17
18 func (m Model) renderSiteList() string {
19 tbl := common.VisitUrlsTbl(m.siteList, m.shared.Styles.Renderer, m.viewport.Width)
20- str := "Sites: Unique Visitors this Month\n"
21+ str := "Sites: Total Unique Visitors\n"
22 str += tbl.String()
23 return str
24 }
+2,
-0
1@@ -4,6 +4,7 @@ import (
2 tea "github.com/charmbracelet/bubbletea"
3 "github.com/charmbracelet/ssh"
4 bm "github.com/charmbracelet/wish/bubbletea"
5+ "github.com/muesli/termenv"
6 "github.com/picosh/pico/db/postgres"
7 "github.com/picosh/pico/shared"
8 "github.com/picosh/pico/tui/common"
9@@ -21,6 +22,7 @@ func CmsMiddleware(cfg *shared.ConfigSite) bm.Handler {
10
11 dbpool := postgres.NewDB(cfg.DbURL, cfg.Logger)
12 renderer := bm.MakeRenderer(sesh)
13+ renderer.SetColorProfile(termenv.TrueColor)
14 styles := common.DefaultStyles(renderer)
15
16 shrd := &common.SharedModel{