timesheet and unified reports

1 files changed, 53 insertions, 0 deletions

diff --git a/internal/database/queries.sql b/internal/database/queries.sql
index 32114a6..3a644b8 100644
--- a/internal/database/queries.sql
+++ b/internal/database/queries.sql
@@ -231,3 +231,56 @@ where year = @year and month = @month;
 insert into invoice (year, month, number, client_id, total_amount)
 values (@year, @month, @number, @client_id, @total_amount)
 returning *;
+
+-- name: GetTimesheetDataByClient :many
+select 
+    te.id as time_entry_id,
+    te.start_time,
+    te.end_time,
+    te.description,
+    te.billable_rate as entry_billable_rate,
+    c.id as client_id,
+    c.name as client_name,
+    c.billable_rate as client_billable_rate,
+    p.id as project_id,
+    p.name as project_name,
+    p.billable_rate as project_billable_rate,
+    cast(round((julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60) as integer) as duration_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 c.id = @client_id
+    and te.start_time >= @start_time
+    and te.start_time <= @end_time
+    and te.end_time is not null
+order by te.start_time;
+
+-- name: GetTimesheetDataByProject :many
+select 
+    te.id as time_entry_id,
+    te.start_time,
+    te.end_time,
+    te.description,
+    te.billable_rate as entry_billable_rate,
+    c.id as client_id,
+    c.name as client_name,
+    c.billable_rate as client_billable_rate,
+    p.id as project_id,
+    p.name as project_name,
+    p.billable_rate as project_billable_rate,
+    cast(
+        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 duration_seconds
+from time_entry te
+join client c on te.client_id = c.id
+join project p on te.project_id = p.id
+where p.id = @project_id
+    and te.start_time >= @start_time
+    and te.start_time <= @end_time
+    and te.end_time is not null
+order by te.start_time;