veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "strings"
7 "time"
8)
9
10type Punch struct {
11 Did string
12 Date time.Time
13 Count int
14}
15
16// this adds to the existing count
17func AddPunch(e Execer, punch Punch) error {
18 _, err := e.Exec(`
19 insert into punchcard (did, date, count)
20 values (?, ?, ?)
21 on conflict(did, date) do update set
22 count = coalesce(punchcard.count, 0) + excluded.count;
23 `, punch.Did, punch.Date.Format(time.DateOnly), punch.Count)
24 return err
25}
26
27type Punchcard struct {
28 Total int
29 Punches []Punch
30}
31
32func MakePunchcard(e Execer, filters ...filter) (*Punchcard, error) {
33 punchcard := &Punchcard{}
34 now := time.Now()
35 startOfYear := time.Date(now.Year(), 1, 1, 0, 0, 0, 0, time.UTC)
36 endOfYear := time.Date(now.Year(), 12, 31, 0, 0, 0, 0, time.UTC)
37 for d := startOfYear; d.Before(endOfYear) || d.Equal(endOfYear); d = d.AddDate(0, 0, 1) {
38 punchcard.Punches = append(punchcard.Punches, Punch{
39 Date: d,
40 Count: 0,
41 })
42 }
43
44 var conditions []string
45 var args []any
46 for _, filter := range filters {
47 conditions = append(conditions, filter.Condition())
48 args = append(args, filter.Arg()...)
49 }
50
51 whereClause := ""
52 if conditions != nil {
53 whereClause = " where " + strings.Join(conditions, " and ")
54 }
55
56 query := fmt.Sprintf(`
57 select date, sum(count) as total_count
58 from punchcard
59 %s
60 group by date
61 order by date
62 `, whereClause)
63
64 rows, err := e.Query(query, args...)
65 if err != nil {
66 return nil, err
67 }
68 defer rows.Close()
69
70 for rows.Next() {
71 var punch Punch
72 var date string
73 var count sql.NullInt64
74 if err := rows.Scan(&date, &count); err != nil {
75 return nil, err
76 }
77
78 punch.Date, err = time.Parse(time.DateOnly, date)
79 if err != nil {
80 fmt.Println("invalid date")
81 // this punch is not recorded if date is invalid
82 continue
83 }
84
85 if count.Valid {
86 punch.Count = int(count.Int64)
87 }
88
89 punchcard.Punches[punch.Date.YearDay()] = punch
90 punchcard.Total += punch.Count
91 }
92
93 return punchcard, nil
94}