1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
-- name: CreateClient :one
insert into client (name, email, billable_rate)
values (@name, @email, @billable_rate)
returning *;
-- name: FindClient :many
select c1.id, c1.name, c1.email, c1.billable_rate, c1.created_at from client c1 where c1.id = cast(@id as integer)
union all
select c2.id, c2.name, c2.email, c2.billable_rate, c2.created_at from client c2 where c2.name = @name;
-- name: CreateProject :one
insert into project (name, client_id, billable_rate)
values (@name, @client_id, @billable_rate)
returning *;
-- name: FindProject :many
select p1.id, p1.name, p1.client_id, p1.billable_rate, p1.created_at from project p1 where p1.id = cast(@id as integer)
union all
select p2.id, p2.name, p2.client_id, p2.billable_rate, p2.created_at from project p2 where p2.name = @name;
-- name: CreateTimeEntry :one
insert into time_entry (start_time, description, client_id, project_id, billable_rate)
values (
datetime('now', 'utc'),
@description,
@client_id,
@project_id,
coalesce(
@billable_rate,
(select p.billable_rate from project p where p.id = @project_id),
(select c.billable_rate from client c where c.id = @client_id)
)
)
returning *;
-- name: GetActiveTimeEntry :one
select * from time_entry
where end_time is null
order by start_time desc
limit 1;
-- name: StopTimeEntry :one
update time_entry
set end_time = datetime('now', 'utc')
where id = (
select id
from time_entry
where end_time is null
order by start_time desc
limit 1
)
returning *;
-- name: GetMostRecentTimeEntry :one
select * from time_entry
order by start_time desc
limit 1;
-- name: ListAllClients :many
select * from client
order by name;
-- name: ListAllProjects :many
select p.*, c.name as client_name from project p
join client c on p.client_id = c.id
order by c.name, p.name;
-- name: GetWeekSummaryByProject :many
select
p.id as project_id,
p.name as project_name,
c.id as client_id,
c.name as client_name,
cast(sum(
case
when te.end_time is null then
(julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60
else
(julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60
end
) as integer) as total_seconds
from time_entry te
join client c on te.client_id = c.id
left join project p on te.project_id = p.id
where date(te.start_time) >= date('now', 'weekday 1', '-6 days')
and date(te.start_time) <= date('now')
group by p.id, p.name, c.id, c.name
order by c.name, p.name;
-- name: GetMonthSummaryByProject :many
select
p.id as project_id,
p.name as project_name,
c.id as client_id,
c.name as client_name,
cast(sum(
case
when te.end_time is null then
(julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60
else
(julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60
end
) as integer) as total_seconds
from time_entry te
join client c on te.client_id = c.id
left join project p on te.project_id = p.id
where date(te.start_time) >= date('now', 'start of month')
and date(te.start_time) <= date('now')
group by p.id, p.name, c.id, c.name
order by c.name, p.name;
-- name: GetClientByName :one
select * from client where name = @name limit 1;
-- name: GetProjectByNameAndClient :one
select * from project where name = @name and client_id = @client_id limit 1;
-- name: CreateTimeEntryWithTimes :one
insert into time_entry (start_time, end_time, description, client_id, project_id, billable_rate)
values (
@start_time,
@end_time,
@description,
@client_id,
@project_id,
coalesce(
@billable_rate,
(select p.billable_rate from project p where p.id = @project_id),
(select c.billable_rate from client c where c.id = @client_id)
)
)
returning *;
|
