Generate invoice PDFs

2 files changed, 383 insertions, 0 deletions

diff --git a/internal/queries/models.go b/internal/queries/models.go
index 1257397..b42de02 100644
--- a/internal/queries/models.go
+++ b/internal/queries/models.go
@@ -17,6 +17,24 @@ type Client struct {
 	CreatedAt    sql.NullTime
 }
 
+type Contractor struct {
+	ID        int64
+	Name      string
+	Label     string
+	Email     string
+	CreatedAt sql.NullTime
+}
+
+type Invoice struct {
+	ID          int64
+	Year        int64
+	Month       int64
+	Number      int64
+	ClientID    int64
+	TotalAmount int64
+	CreatedAt   sql.NullTime
+}
+
 type Project struct {
 	ID           int64
 	Name         string
diff --git a/internal/queries/queries.sql.go b/internal/queries/queries.sql.go
index 1bd8ec1..e70de22 100644
--- a/internal/queries/queries.sql.go
+++ b/internal/queries/queries.sql.go
@@ -8,6 +8,7 @@ package queries
 import (
 	"context"
 	"database/sql"
+	"time"
 )
 
 const createClient = `-- name: CreateClient :one
@@ -35,6 +36,66 @@ func (q *Queries) CreateClient(ctx context.Context, arg CreateClientParams) (Cli
 	return i, err
 }
 
+const createContractor = `-- name: CreateContractor :one
+insert into contractor (name, label, email)
+values (?1, ?2, ?3)
+returning id, name, label, email, created_at
+`
+
+type CreateContractorParams struct {
+	Name  string
+	Label string
+	Email string
+}
+
+func (q *Queries) CreateContractor(ctx context.Context, arg CreateContractorParams) (Contractor, error) {
+	row := q.db.QueryRowContext(ctx, createContractor, arg.Name, arg.Label, arg.Email)
+	var i Contractor
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.Label,
+		&i.Email,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const createInvoice = `-- name: CreateInvoice :one
+insert into invoice (year, month, number, client_id, total_amount)
+values (?1, ?2, ?3, ?4, ?5)
+returning id, year, month, number, client_id, total_amount, created_at
+`
+
+type CreateInvoiceParams struct {
+	Year        int64
+	Month       int64
+	Number      int64
+	ClientID    int64
+	TotalAmount int64
+}
+
+func (q *Queries) CreateInvoice(ctx context.Context, arg CreateInvoiceParams) (Invoice, error) {
+	row := q.db.QueryRowContext(ctx, createInvoice,
+		arg.Year,
+		arg.Month,
+		arg.Number,
+		arg.ClientID,
+		arg.TotalAmount,
+	)
+	var i Invoice
+	err := row.Scan(
+		&i.ID,
+		&i.Year,
+		&i.Month,
+		&i.Number,
+		&i.ClientID,
+		&i.TotalAmount,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
 const createProject = `-- name: CreateProject :one
 insert into project (name, client_id, billable_rate)
 values (?1, ?2, ?3)
@@ -270,6 +331,226 @@ func (q *Queries) GetClientByName(ctx context.Context, name string) (Client, err
 	return i, err
 }
 
+const getContractor = `-- name: GetContractor :one
+select id, name, label, email, created_at from contractor 
+order by id 
+limit 1
+`
+
+func (q *Queries) GetContractor(ctx context.Context) (Contractor, error) {
+	row := q.db.QueryRowContext(ctx, getContractor)
+	var i Contractor
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.Label,
+		&i.Email,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const getHighestInvoiceNumber = `-- name: GetHighestInvoiceNumber :one
+select cast(coalesce(max(number), 0) as integer) as max_number
+from invoice
+where year = ?1 and month = ?2
+`
+
+type GetHighestInvoiceNumberParams struct {
+	Year  int64
+	Month int64
+}
+
+func (q *Queries) GetHighestInvoiceNumber(ctx context.Context, arg GetHighestInvoiceNumberParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, getHighestInvoiceNumber, arg.Year, arg.Month)
+	var max_number int64
+	err := row.Scan(&max_number)
+	return max_number, err
+}
+
+const getInvoiceDataByClient = `-- name: GetInvoiceDataByClient :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,
+    case
+        when te.billable_rate is not null then 'entry'
+        when p.billable_rate is not null then 'project'
+        else 'client'
+    end as rate_source
+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 = ?1
+    and te.start_time >= ?2
+    and te.start_time <= ?3
+    and te.end_time is not null
+order by te.start_time
+`
+
+type GetInvoiceDataByClientParams struct {
+	ClientID  int64
+	StartTime time.Time
+	EndTime   time.Time
+}
+
+type GetInvoiceDataByClientRow struct {
+	TimeEntryID         int64
+	StartTime           time.Time
+	EndTime             sql.NullTime
+	Description         sql.NullString
+	EntryBillableRate   sql.NullInt64
+	ClientID            int64
+	ClientName          string
+	ClientBillableRate  sql.NullInt64
+	ProjectID           sql.NullInt64
+	ProjectName         sql.NullString
+	ProjectBillableRate sql.NullInt64
+	DurationSeconds     int64
+	RateSource          string
+}
+
+func (q *Queries) GetInvoiceDataByClient(ctx context.Context, arg GetInvoiceDataByClientParams) ([]GetInvoiceDataByClientRow, error) {
+	rows, err := q.db.QueryContext(ctx, getInvoiceDataByClient, arg.ClientID, arg.StartTime, arg.EndTime)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetInvoiceDataByClientRow
+	for rows.Next() {
+		var i GetInvoiceDataByClientRow
+		if err := rows.Scan(
+			&i.TimeEntryID,
+			&i.StartTime,
+			&i.EndTime,
+			&i.Description,
+			&i.EntryBillableRate,
+			&i.ClientID,
+			&i.ClientName,
+			&i.ClientBillableRate,
+			&i.ProjectID,
+			&i.ProjectName,
+			&i.ProjectBillableRate,
+			&i.DurationSeconds,
+			&i.RateSource,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getInvoiceDataByProject = `-- name: GetInvoiceDataByProject :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,
+    case
+        when te.billable_rate is not null then 'entry'
+        when p.billable_rate is not null then 'project'
+        else 'client'
+    end as rate_source
+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 = ?1
+    and te.start_time >= ?2
+    and te.start_time <= ?3
+    and te.end_time is not null
+order by te.start_time
+`
+
+type GetInvoiceDataByProjectParams struct {
+	ProjectID int64
+	StartTime time.Time
+	EndTime   time.Time
+}
+
+type GetInvoiceDataByProjectRow struct {
+	TimeEntryID         int64
+	StartTime           time.Time
+	EndTime             sql.NullTime
+	Description         sql.NullString
+	EntryBillableRate   sql.NullInt64
+	ClientID            int64
+	ClientName          string
+	ClientBillableRate  sql.NullInt64
+	ProjectID           int64
+	ProjectName         string
+	ProjectBillableRate sql.NullInt64
+	DurationSeconds     int64
+	RateSource          string
+}
+
+func (q *Queries) GetInvoiceDataByProject(ctx context.Context, arg GetInvoiceDataByProjectParams) ([]GetInvoiceDataByProjectRow, error) {
+	rows, err := q.db.QueryContext(ctx, getInvoiceDataByProject, arg.ProjectID, arg.StartTime, arg.EndTime)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []GetInvoiceDataByProjectRow
+	for rows.Next() {
+		var i GetInvoiceDataByProjectRow
+		if err := rows.Scan(
+			&i.TimeEntryID,
+			&i.StartTime,
+			&i.EndTime,
+			&i.Description,
+			&i.EntryBillableRate,
+			&i.ClientID,
+			&i.ClientName,
+			&i.ClientBillableRate,
+			&i.ProjectID,
+			&i.ProjectName,
+			&i.ProjectBillableRate,
+			&i.DurationSeconds,
+			&i.RateSource,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
 const getMonthSummaryByProject = `-- name: GetMonthSummaryByProject :many
 select 
     p.id as project_id,
@@ -539,3 +820,87 @@ func (q *Queries) StopTimeEntry(ctx context.Context) (TimeEntry, error) {
 	)
 	return i, err
 }
+
+const updateClient = `-- name: UpdateClient :one
+update client
+set name = ?1, email = ?2, billable_rate = ?3
+where id = ?4
+returning id, name, email, billable_rate, created_at
+`
+
+type UpdateClientParams struct {
+	Name         string
+	Email        sql.NullString
+	BillableRate sql.NullInt64
+	ID           int64
+}
+
+func (q *Queries) UpdateClient(ctx context.Context, arg UpdateClientParams) (Client, error) {
+	row := q.db.QueryRowContext(ctx, updateClient,
+		arg.Name,
+		arg.Email,
+		arg.BillableRate,
+		arg.ID,
+	)
+	var i Client
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.Email,
+		&i.BillableRate,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const updateContractor = `-- name: UpdateContractor :one
+update contractor 
+set name = ?1, label = ?2, email = ?3
+where id = (select id from contractor order by id limit 1)
+returning id, name, label, email, created_at
+`
+
+type UpdateContractorParams struct {
+	Name  string
+	Label string
+	Email string
+}
+
+func (q *Queries) UpdateContractor(ctx context.Context, arg UpdateContractorParams) (Contractor, error) {
+	row := q.db.QueryRowContext(ctx, updateContractor, arg.Name, arg.Label, arg.Email)
+	var i Contractor
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.Label,
+		&i.Email,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const updateProject = `-- name: UpdateProject :one
+update project
+set name = ?1, billable_rate = ?2
+where id = ?3
+returning id, name, client_id, billable_rate, created_at
+`
+
+type UpdateProjectParams struct {
+	Name         string
+	BillableRate sql.NullInt64
+	ID           int64
+}
+
+func (q *Queries) UpdateProject(ctx context.Context, arg UpdateProjectParams) (Project, error) {
+	row := q.db.QueryRowContext(ctx, updateProject, arg.Name, arg.BillableRate, arg.ID)
+	var i Project
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.ClientID,
+		&i.BillableRate,
+		&i.CreatedAt,
+	)
+	return i, err
+}