Show total TrackedTime on issue/pull/milestone lists (#26672)

TODOs:
- [x] write test for `GetIssueTotalTrackedTime`
- [x] frontport kitharas template changes and make them mobile-friendly

---

![image](https://github.com/go-gitea/gitea/assets/24977596/6713da97-201f-4217-8588-4c4cec157171)

![image](https://github.com/go-gitea/gitea/assets/24977596/3a45aba8-26b5-4e6a-b97d-68bfc2bf9024)

---
*Sponsored by Kithara Software GmbH*

8 files changed, 129 insertions, 36 deletions

diff --git a/models/issues/issue_test.go b/models/issues/issue_test.go
index f554820964..4393d18bcf 100644
--- a/models/issues/issue_test.go
+++ b/models/issues/issue_test.go
@@ -191,6 +191,12 @@ func TestIssues(t *testing.T) {
 			},
 			[]int64{}, // issues with **both** label 1 and 2, none of these issues matches, TODO: add more tests
 		},
+		{
+			issues_model.IssuesOptions{
+				MilestoneIDs: []int64{1},
+			},
+			[]int64{2},
+		},
 	} {
 		issues, err := issues_model.Issues(db.DefaultContext, &test.Opts)
 		assert.NoError(t, err)
diff --git a/models/issues/tracked_time.go b/models/issues/tracked_time.go
index 89c99c5cd1..795bddeb34 100644
--- a/models/issues/tracked_time.go
+++ b/models/issues/tracked_time.go
@@ -15,6 +15,7 @@ import (
 	"code.gitea.io/gitea/modules/util"
 
 	"xorm.io/builder"
+	"xorm.io/xorm"
 )
 
 // TrackedTime represents a time that was spent for a specific issue.
@@ -325,3 +326,46 @@ func GetTrackedTimeByID(ctx context.Context, id int64) (*TrackedTime, error) {
 	}
 	return time, nil
 }
+
+// GetIssueTotalTrackedTime returns the total tracked time for issues by given conditions.
+func GetIssueTotalTrackedTime(ctx context.Context, opts *IssuesOptions, isClosed bool) (int64, error) {
+	if len(opts.IssueIDs) <= MaxQueryParameters {
+		return getIssueTotalTrackedTimeChunk(ctx, opts, isClosed, opts.IssueIDs)
+	}
+
+	// If too long a list of IDs is provided,
+	// we get the statistics in smaller chunks and get accumulates
+	var accum int64
+	for i := 0; i < len(opts.IssueIDs); {
+		chunk := i + MaxQueryParameters
+		if chunk > len(opts.IssueIDs) {
+			chunk = len(opts.IssueIDs)
+		}
+		time, err := getIssueTotalTrackedTimeChunk(ctx, opts, isClosed, opts.IssueIDs[i:chunk])
+		if err != nil {
+			return 0, err
+		}
+		accum += time
+		i = chunk
+	}
+	return accum, nil
+}
+
+func getIssueTotalTrackedTimeChunk(ctx context.Context, opts *IssuesOptions, isClosed bool, issueIDs []int64) (int64, error) {
+	sumSession := func(opts *IssuesOptions, issueIDs []int64) *xorm.Session {
+		sess := db.GetEngine(ctx).
+			Table("tracked_time").
+			Where("tracked_time.deleted = ?", false).
+			Join("INNER", "issue", "tracked_time.issue_id = issue.id")
+
+		return applyIssuesOptions(sess, opts, issueIDs)
+	}
+
+	type trackedTime struct {
+		Time int64
+	}
+
+	return sumSession(opts, issueIDs).
+		And("issue.is_closed = ?", isClosed).
+		SumInt(new(trackedTime), "tracked_time.time")
+}
diff --git a/models/issues/tracked_time_test.go b/models/issues/tracked_time_test.go
index cc2cb918e0..2774234e7b 100644
--- a/models/issues/tracked_time_test.go
+++ b/models/issues/tracked_time_test.go
@@ -115,3 +115,15 @@ func TestTotalTimesForEachUser(t *testing.T) {
 	assert.NoError(t, err)
 	assert.Len(t, total, 2)
 }
+
+func TestGetIssueTotalTrackedTime(t *testing.T) {
+	assert.NoError(t, unittest.PrepareTestDatabase())
+
+	ttt, err := issues_model.GetIssueTotalTrackedTime(db.DefaultContext, &issues_model.IssuesOptions{MilestoneIDs: []int64{1}}, false)
+	assert.NoError(t, err)
+	assert.EqualValues(t, 3682, ttt)
+
+	ttt, err = issues_model.GetIssueTotalTrackedTime(db.DefaultContext, &issues_model.IssuesOptions{MilestoneIDs: []int64{1}}, true)
+	assert.NoError(t, err)
+	assert.EqualValues(t, 0, ttt)
+}
diff --git a/options/locale/locale_en-US.ini b/options/locale/locale_en-US.ini
index 46138fad54..84c457e9e8 100644
--- a/options/locale/locale_en-US.ini
+++ b/options/locale/locale_en-US.ini
@@ -17,6 +17,7 @@ template = Template
 language = Language
 notifications = Notifications
 active_stopwatch = Active Time Tracker
+tracked_time_summary = Summary of tracked time based on filters of issue list
 create_new = Create…
 user_profile_and_more = Profile and Settings…
 signed_in_as = Signed in as
diff --git a/routers/web/repo/issue.go b/routers/web/repo/issue.go
index 3fd25f81fb..96fce48878 100644
--- a/routers/web/repo/issue.go
+++ b/routers/web/repo/issue.go
@@ -198,46 +198,43 @@ func issues(ctx *context.Context, milestoneID, projectID int64, isPullOption uti
 	}
 
 	var issueStats *issues_model.IssueStats
-	{
-		statsOpts := &issues_model.IssuesOptions{
-			RepoIDs:           []int64{repo.ID},
-			LabelIDs:          labelIDs,
-			MilestoneIDs:      mileIDs,
-			ProjectID:         projectID,
-			AssigneeID:        assigneeID,
-			MentionedID:       mentionedID,
-			PosterID:          posterID,
-			ReviewRequestedID: reviewRequestedID,
-			ReviewedID:        reviewedID,
-			IsPull:            isPullOption,
-			IssueIDs:          nil,
-		}
-		if keyword != "" {
-			allIssueIDs, err := issueIDsFromSearch(ctx, keyword, statsOpts)
-			if err != nil {
-				if issue_indexer.IsAvailable(ctx) {
-					ctx.ServerError("issueIDsFromSearch", err)
-					return
-				}
-				ctx.Data["IssueIndexerUnavailable"] = true
+	statsOpts := &issues_model.IssuesOptions{
+		RepoIDs:           []int64{repo.ID},
+		LabelIDs:          labelIDs,
+		MilestoneIDs:      mileIDs,
+		ProjectID:         projectID,
+		AssigneeID:        assigneeID,
+		MentionedID:       mentionedID,
+		PosterID:          posterID,
+		ReviewRequestedID: reviewRequestedID,
+		ReviewedID:        reviewedID,
+		IsPull:            isPullOption,
+		IssueIDs:          nil,
+	}
+	if keyword != "" {
+		allIssueIDs, err := issueIDsFromSearch(ctx, keyword, statsOpts)
+		if err != nil {
+			if issue_indexer.IsAvailable(ctx) {
+				ctx.ServerError("issueIDsFromSearch", err)
 				return
 			}
-			statsOpts.IssueIDs = allIssueIDs
+			ctx.Data["IssueIndexerUnavailable"] = true
+			return
 		}
-		if keyword != "" && len(statsOpts.IssueIDs) == 0 {
-			// So it did search with the keyword, but no issue found.
-			// Just set issueStats to empty.
-			issueStats = &issues_model.IssueStats{}
-		} else {
-			// So it did search with the keyword, and found some issues. It needs to get issueStats of these issues.
-			// Or the keyword is empty, so it doesn't need issueIDs as filter, just get issueStats with statsOpts.
-			issueStats, err = issues_model.GetIssueStats(ctx, statsOpts)
-			if err != nil {
-				ctx.ServerError("GetIssueStats", err)
-				return
-			}
+		statsOpts.IssueIDs = allIssueIDs
+	}
+	if keyword != "" && len(statsOpts.IssueIDs) == 0 {
+		// So it did search with the keyword, but no issue found.
+		// Just set issueStats to empty.
+		issueStats = &issues_model.IssueStats{}
+	} else {
+		// So it did search with the keyword, and found some issues. It needs to get issueStats of these issues.
+		// Or the keyword is empty, so it doesn't need issueIDs as filter, just get issueStats with statsOpts.
+		issueStats, err = issues_model.GetIssueStats(ctx, statsOpts)
+		if err != nil {
+			ctx.ServerError("GetIssueStats", err)
+			return
 		}
-
 	}
 
 	isShowClosed := ctx.FormString("state") == "closed"
@@ -246,6 +243,15 @@ func issues(ctx *context.Context, milestoneID, projectID int64, isPullOption uti
 		isShowClosed = true
 	}
 
+	if repo.IsTimetrackerEnabled(ctx) {
+		totalTrackedTime, err := issues_model.GetIssueTotalTrackedTime(ctx, statsOpts, isShowClosed)
+		if err != nil {
+			ctx.ServerError("GetIssueTotalTrackedTime", err)
+			return
+		}
+		ctx.Data["TotalTrackedTime"] = totalTrackedTime
+	}
+
 	archived := ctx.FormBool("archived")
 
 	page := ctx.FormInt("page")
diff --git a/templates/repo/issue/filters.tmpl b/templates/repo/issue/filters.tmpl
index 8645ff9d50..1d200e23b7 100644
--- a/templates/repo/issue/filters.tmpl
+++ b/templates/repo/issue/filters.tmpl
@@ -4,6 +4,15 @@
 			<input type="checkbox" autocomplete="off" class="issue-checkbox-all gt-mr-4" title="{{ctx.Locale.Tr "repo.issues.action_check_all"}}">
 		{{end}}
 		{{template "repo/issue/openclose" .}}
+		<!-- Total Tracked Time -->
+		{{if .TotalTrackedTime}}
+			<div class="ui compact tiny secondary menu">
+				<span class="item" data-tooltip-content='{{ctx.Locale.Tr "tracked_time_summary"}}'>
+					{{svg "octicon-clock"}}
+					{{.TotalTrackedTime | Sec2Time}}
+				</span>
+			</div>
+		{{end}}
 	</div>
 	<div class="issue-list-toolbar-right">
 		<div class="ui secondary filter menu labels">
diff --git a/templates/repo/issue/list.tmpl b/templates/repo/issue/list.tmpl
index 038526f424..012b613fbf 100644
--- a/templates/repo/issue/list.tmpl
+++ b/templates/repo/issue/list.tmpl
@@ -34,6 +34,15 @@
 		<div id="issue-actions" class="issue-list-toolbar gt-hidden">
 			<div class="issue-list-toolbar-left">
 				{{template "repo/issue/openclose" .}}
+				<!-- Total Tracked Time -->
+				{{if .TotalTrackedTime}}
+					<div class="ui compact tiny secondary menu">
+						<span class="item" data-tooltip-content='{{ctx.Locale.Tr "tracked_time_summary"}}'>
+							{{svg "octicon-clock"}}
+							{{.TotalTrackedTime | Sec2Time}}
+						</span>
+					</div>
+				{{end}}
 			</div>
 			<div class="issue-list-toolbar-right">
 				{{template "repo/issue/filter_actions" .}}
diff --git a/templates/repo/issue/milestone_issues.tmpl b/templates/repo/issue/milestone_issues.tmpl
index 9d5da13b5d..ea19518efa 100644
--- a/templates/repo/issue/milestone_issues.tmpl
+++ b/templates/repo/issue/milestone_issues.tmpl
@@ -46,6 +46,12 @@
 					{{end}}
 				</div>
 				<div class="gt-mr-3">{{ctx.Locale.Tr "repo.milestones.completeness" .Milestone.Completeness | Safe}}</div>
+				{{if .TotalTrackedTime}}
+					<div data-tooltip-content='{{ctx.Locale.Tr "tracked_time_summary"}}'>
+						{{svg "octicon-clock"}}
+						{{.TotalTrackedTime | Sec2Time}}
+					</div>
+				{{end}}
 			</div>
 		</div>
 		<div class="divider"></div>