def execute(filters=None):
filters = filters or {}
# ----------------------------
# Columns
# ----------------------------
columns = [
{"label": "Project Name", "fieldname": "project_name", "fieldtype": "Data", "width": 180},
{"label": "Activity Type", "fieldname": "activity_type", "fieldtype": "Link", "options": "Activity Type", "width": 180},
{"label": "Start Date", "fieldname": "start_date", "fieldtype": "Date", "width": 120},
{"label": "End Date", "fieldname": "end_date", "fieldtype": "Date", "width": 120},
{"label": "Hours", "fieldname": "hours", "fieldtype": "Float", "width": 80},
{"label": "Timesheet", "fieldname": "timesheet", "fieldtype": "Link", "options": "Timesheet", "width": 160},
{"label": "Working Hours", "fieldname": "total_working_hours", "fieldtype": "Float", "width": 160},
{"label": "Billable Hours", "fieldname": "total_billable_hours", "fieldtype": "Float", "width": 160},
]
# ----------------------------
# Filters / Conditions
# ----------------------------
conditions = ["ts.docstatus < 2"]
values = {}
if filters.get("projects"):
p_val = filters.get("projects")
p_list = [v.strip() for v in p_val.split(",") if v.strip()] if isinstance(p_val, str) else p_val
if p_list:
conditions.append("COALESCE(NULLIF(ts.parent_project, ''), tsd.project) IN %(projects)s")
values["projects"] = tuple(p_list)
if filters.get("activities"):
a_val = filters.get("activities")
a_list = [v.strip() for v in a_val.split(",") if v.strip()] if isinstance(a_val, str) else a_val
if a_list:
conditions.append("tsd.activity_type IN %(activities)s")
values["activities"] = tuple(a_list)
if filters.get("employee"):
conditions.append("ts.employee = %(employee)s")
values["employee"] = filters.get("employee")
if filters.get("from_date"):
conditions.append("DATE(tsd.from_time) >= %(from_date)s")
values["from_date"] = filters.get("from_date")
if filters.get("to_date"):
conditions.append("DATE(tsd.to_time) <= %(to_date)s")
values["to_date"] = filters.get("to_date")
where_clause = " AND ".join(conditions)
# ----------------------------
# Data Query
# ----------------------------
sql = """
SELECT
COALESCE(NULLIF(ts.parent_project, ''), tsd.project) AS project,
COALESCE(NULLIF(p.project_name, ''), COALESCE(NULLIF(ts.parent_project, ''), tsd.project)) AS project_name,
tsd.activity_type AS activity_type,
COALESCE(p.actual_start_date, p.expected_start_date) AS start_date,
COALESCE(p.actual_end_date, p.expected_end_date) AS end_date,
tsd.hours AS hours,
COALESCE(tsd.billing_hours, 0) AS billable_hours,
ts.name AS timesheet,
tsd.from_time AS work_date
FROM `tabTimesheet` ts
INNER JOIN `tabTimesheet Detail` tsd
ON tsd.parent = ts.name
LEFT JOIN `tabProject` p
ON p.name = COALESCE(NULLIF(ts.parent_project, ''), tsd.project)
WHERE %s
ORDER BY tsd.from_time DESC
LIMIT 500
""" % where_clause
data = frappe.db.sql(sql, values, as_dict=True)
# ----------------------------
# Aggregations (NO +=)
# ----------------------------
project_totals = {}
project_billable_totals = {}
activity_totals = {}
month_totals = {}
total_hours = 0.0
for r in data:
p = r.get("project_name") or r.get("project") or "Not Set"
a = r.get("activity_type") or "Not Set"
h_raw = r.get("hours")
h = float(h_raw) if h_raw else 0.0
b_raw = r.get("billable_hours")
b = float(b_raw) if b_raw else 0.0
total_hours = total_hours + h
project_totals[p] = float(project_totals.get(p, 0.0)) + h
project_billable_totals[p] = float(project_billable_totals.get(p, 0.0)) + b
activity_totals[a] = float(activity_totals.get(a, 0.0)) + h
wd = r.get("work_date")
if wd:
wd_str = str(wd)
month_key = wd_str[:7]
month_totals[month_key] = float(month_totals.get(month_key, 0.0)) + h
for r in data:
p = r.get("project_name") or r.get("project") or "Not Set"
r["total_working_hours"] = round(float(project_totals.get(p, 0.0)), 2)
r["total_billable_hours"] = round(float(project_billable_totals.get(p, 0.0)), 2)
# ----------------------------
# Chart (Project-wise bar)
# ----------------------------
proj_pairs = sorted(project_totals.items(), key=lambda x: float(x[1] or 0.0), reverse=True)
proj_labels = []
proj_values = []
# top 20
i = 0
for item in proj_pairs:
if i >= 20:
break
proj_labels.append(item[0])
proj_values.append(round(float(item[1] or 0.0), 2))
i = i + 1
chart = None
# ----------------------------
# Activity Type x Hours (message chart for Python-only report script)
# ----------------------------
act_pairs = sorted(activity_totals.items(), key=lambda x: float(x[1] or 0.0), reverse=True)
act_top = act_pairs[:10]
act_max = float(act_top[0][1]) if act_top else 0.0
activity_lines = []
for name, val in act_top:
h_val = round(float(val or 0.0), 2)
pct = int((float(val or 0.0) / act_max) * 100) if act_max > 0 else 0
activity_lines.append(
"<div style='display:flex;align-items:center;gap:8px;'>"
"<div style='width:180px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;'>%s</div>"
"<div style='flex:1;background:#e5e7eb;height:36px;border-radius:6px;overflow:hidden;'>"
"<div style='height:36px;background:#3b82f6;width:%s%%;'></div>"
"</div>"
"<div style='width:84px;text-align:right;'>%s</div>"
"</div>"
% (name or "Not Set", pct, h_val)
)
project_limit = filters.get("project_limit")
if project_limit:
try:
project_limit = int(project_limit)
except Exception:
project_limit = 0
else:
project_limit = 0
if project_limit > 0:
proj_top = proj_pairs[:project_limit]
else:
proj_top = proj_pairs
proj_count = len(proj_top)
proj_max = max([float(x[1] or 0.0) for x in proj_top], default=0.0)
project_bars = []
for name, val in proj_top:
v = round(float(val or 0.0), 2)
pct = int((float(val or 0.0) / proj_max) * 100) if proj_max > 0 else 0
label_txt = (name or "Not Set")
if len(label_txt) > 12:
label_txt = label_txt[:11] + "."
project_bars.append(
"<div style='display:flex;flex-direction:column;align-items:center;gap:6px;'>"
"<div style='font-size:10px;color:#6b7280;'>%s</div>"
"<div style='height:210px;width:28px;background:#e5e7eb;border-radius:4px;position:relative;overflow:hidden;'>"
"<div style='position:absolute;left:0;right:0;bottom:0;background:#10b981;height:%s%%;'></div>"
"<div title='%s' style='position:absolute;left:50%%;bottom:4px;transform:translateX(-50%%) rotate(180deg);"
"writing-mode:vertical-rl;font-size:11px;line-height:11px;color:#111111;"
"white-space:nowrap;max-height:196px;overflow:hidden;'>%s</div>"
"</div>"
"</div>"
% (v, pct, name or "Not Set", label_txt)
)
project_chart_html = (
"<div style='height:280px;display:grid;grid-template-columns:repeat(%s, minmax(22px, 1fr));"
"column-gap:8px;align-items:end;overflow-x:auto;padding-bottom:2px;'>%s</div>"
% (proj_count if proj_count > 0 else 1, "".join(project_bars) or "<div>No project data.</div>")
)
# ----------------------------
# Summary + message
# ----------------------------
total_projects = len(list(project_totals.keys()))
total_months = len(list(month_totals.keys()))
avg_hours_per_project = (total_hours / float(total_projects)) if total_projects > 0 else 0.0
avg_hours_per_month = (total_hours / float(total_months)) if total_months > 0 else 0.0
report_summary = None
message = (
"<div style='display:grid;grid-template-columns:repeat(4, minmax(180px, 1fr));gap:10px;margin:8px 0 12px 0;'>"
"<div style='border:1px solid #e5e7eb;border-radius:8px;padding:10px;background:#f9fafb;'><div style='font-size:11px;color:#6b7280;'>Total Projects</div><div style='font-size:20px;font-weight:700;'>%s</div></div>"
"<div style='border:1px solid #e5e7eb;border-radius:8px;padding:10px;background:#f9fafb;'><div style='font-size:11px;color:#6b7280;'>Avg Working Hours / Project</div><div style='font-size:20px;font-weight:700;'>%s</div></div>"
"<div style='border:1px solid #e5e7eb;border-radius:8px;padding:10px;background:#f9fafb;'><div style='font-size:11px;color:#6b7280;'>Total Working Hours</div><div style='font-size:20px;font-weight:700;'>%s</div></div>"
"<div style='border:1px solid #e5e7eb;border-radius:8px;padding:10px;background:#f9fafb;'><div style='font-size:11px;color:#6b7280;'>Avg Working Hours / Month</div><div style='font-size:20px;font-weight:700;'>%s</div></div>"
"</div>"
"<div style='display:flex;gap:18px;align-items:stretch;flex-wrap:wrap;margin-top:4px;'>"
"<div style='flex:1;min-width:340px;height:360px;border:1px solid #e5e7eb;border-radius:8px;padding:10px;display:flex;flex-direction:column;'>"
"<div style='margin-bottom:8px;'><b>Project x Hours</b></div>"
"<div style='font-size:11px;color:#6b7280;margin-bottom:6px;'>Showing %s projects%s</div>"
"%s"
"</div>"
"<div style='flex:1;min-width:340px;height:360px;border:1px solid #e5e7eb;border-radius:8px;padding:10px;display:flex;flex-direction:column;'>"
"<div style='margin-bottom:8px;'><b>Activity Type x Hours</b></div>"
"<div style='height:280px;overflow-y:auto;display:flex;flex-direction:column;gap:10px;'>%s</div>"
"</div>"
"</div>"
% (
total_projects,
round(float(avg_hours_per_project), 2),
round(float(total_hours), 2),
round(float(avg_hours_per_month), 2),
proj_count,
(" (filtered from total " + str(total_projects) + ")" if project_limit > 0 else ""),
project_chart_html,
"".join(activity_lines) or "<div>No activity data.</div>",
)
)
return columns, data, message, chart, report_summary
data = execute()
