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