timesheet and unified reports

1 files changed, 185 insertions, 0 deletions

diff --git a/internal/reports/timesheet.go b/internal/reports/timesheet.go
new file mode 100644
index 0000000..a40d8ae
--- /dev/null
+++ b/internal/reports/timesheet.go
@@ -0,0 +1,185 @@
+package reports
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"punchcard/internal/queries"
+)
+
+type TimesheetData struct {
+	ClientID        int64
+	ClientName      string
+	ProjectName     string
+	ContractorName  string
+	ContractorLabel string
+	ContractorEmail string
+	DateRange       DateRange
+	Entries         []TimesheetEntry
+	TotalHours      float64
+	GeneratedDate   time.Time
+	Timezone        string
+}
+
+type TimesheetEntry struct {
+	Date        string  `json:"date"`
+	StartTime   string  `json:"start_time"`
+	EndTime     string  `json:"end_time"`
+	Duration    string  `json:"duration"`
+	Hours       float64 `json:"hours"`
+	ProjectName string  `json:"project_name"`
+	Description string  `json:"description"`
+}
+
+type timesheetEntryData struct {
+	TimeEntryID   int64
+	StartTime     time.Time
+	EndTime       sql.NullTime
+	Description   sql.NullString
+	ClientID      int64
+	ClientName    string
+	ProjectID     sql.NullInt64
+	ProjectName   sql.NullString
+	DurationSeconds int64
+}
+
+func GenerateTimesheetData(
+	entries interface{},
+	clientID int64,
+	clientName,
+	projectName string,
+	contractor queries.Contractor,
+	dateRange DateRange,
+	loc *time.Location,
+) (*TimesheetData, error) {
+	var timeEntries []timesheetEntryData
+
+	switch e := entries.(type) {
+	case []queries.GetTimesheetDataByClientRow:
+		for _, entry := range e {
+			timeEntries = append(timeEntries, timesheetEntryData{
+				TimeEntryID:   entry.TimeEntryID,
+				StartTime:     entry.StartTime,
+				EndTime:       entry.EndTime,
+				Description:   entry.Description,
+				ClientID:      entry.ClientID,
+				ClientName:    entry.ClientName,
+				ProjectID:     entry.ProjectID,
+				ProjectName:   entry.ProjectName,
+				DurationSeconds: entry.DurationSeconds,
+			})
+		}
+	case []queries.GetTimesheetDataByProjectRow:
+		for _, entry := range e {
+			timeEntries = append(timeEntries, timesheetEntryData{
+				TimeEntryID:   entry.TimeEntryID,
+				StartTime:     entry.StartTime,
+				EndTime:       entry.EndTime,
+				Description:   entry.Description,
+				ClientID:      entry.ClientID,
+				ClientName:    entry.ClientName,
+				ProjectID:     sql.NullInt64{Int64: entry.ProjectID, Valid: true},
+				ProjectName:   sql.NullString{String: entry.ProjectName, Valid: true},
+				DurationSeconds: entry.DurationSeconds,
+			})
+		}
+	default:
+		return nil, fmt.Errorf("unsupported entry type")
+	}
+
+	timesheetEntries := convertToTimesheetEntries(timeEntries, loc)
+
+	// Calculate total hours from raw seconds (same method as invoices)
+	totalSeconds := int64(0)
+	for _, entry := range timeEntries {
+		totalSeconds += entry.DurationSeconds
+	}
+	totalHours := float64(totalSeconds) / 3600.0
+
+	// Get the actual timezone name (not "Local" but the real zone name)
+	timezoneName := loc.String()
+	if timezoneName == "Local" {
+		// Get the actual local timezone name by checking current time
+		now := time.Now()
+		zone, _ := now.Zone()
+		timezoneName = zone
+	}
+
+	timesheet := &TimesheetData{
+		ClientID:        clientID,
+		ClientName:      clientName,
+		ProjectName:     projectName,
+		ContractorName:  contractor.Name,
+		ContractorLabel: contractor.Label,
+		ContractorEmail: contractor.Email,
+		DateRange:       dateRange,
+		Entries:         timesheetEntries,
+		TotalHours:      totalHours,
+		GeneratedDate:   time.Now().UTC(),
+		Timezone:        timezoneName,
+	}
+
+	return timesheet, nil
+}
+
+func convertToTimesheetEntries(entries []timesheetEntryData, loc *time.Location) []TimesheetEntry {
+	var timesheetEntries []TimesheetEntry
+
+	for _, entry := range entries {
+		if !entry.EndTime.Valid {
+			continue // Skip entries without end time
+		}
+
+		// Convert UTC times to specified timezone
+		localStartTime := entry.StartTime.In(loc)
+		localEndTime := entry.EndTime.Time.In(loc)
+		
+		// Format date as YYYY-MM-DD
+		date := localStartTime.Format("2006-01-02")
+		
+		// Format times as HH:MM
+		startTime := localStartTime.Format("15:04")
+		endTime := localEndTime.Format("15:04")
+		
+		// Format duration as HH:MM
+		duration := formatDuration(entry.DurationSeconds)
+		
+		// Calculate hours as decimal, rounded to nearest minute
+		totalMinutes := (entry.DurationSeconds + 30) / 60  // Round to nearest minute
+		hours := float64(totalMinutes) / 60.0
+		
+		// Get project name
+		projectName := ""
+		if entry.ProjectName.Valid {
+			projectName = entry.ProjectName.String
+		}
+		
+		// Get description
+		description := ""
+		if entry.Description.Valid {
+			description = entry.Description.String
+		}
+
+		timesheetEntries = append(timesheetEntries, TimesheetEntry{
+			Date:        date,
+			StartTime:   startTime,
+			EndTime:     endTime,
+			Duration:    duration,
+			Hours:       hours,
+			ProjectName: projectName,
+			Description: description,
+		})
+	}
+
+	return timesheetEntries
+}
+
+func formatDuration(seconds int64) string {
+	// Round to nearest minute
+	totalMinutes := (seconds + 30) / 60  // Add 30 seconds for rounding
+	hours := totalMinutes / 60
+	minutes := totalMinutes % 60
+	return fmt.Sprintf("%d:%02d", hours, minutes)
+}
+