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