from flask import Flask, render_template, request, send_file, redirect, url_for, session from authlib.integrations.flask_client import OAuth from flask import jsonify from io import BytesIO from dotenv import load_dotenv import os import requests import pandas as pd from calendar import monthrange from datetime import datetime # -------------------------- # Flask setup # -------------------------- app = Flask(__name__) app.secret_key = 'a4c9b5f3d82e4e1d3c71dff59e73b0b2b6a7f78a6cb5f4c9d21c93af71fda8c4' # OAuth setup oauth = OAuth(app) # -------------------------- # Load environment variables # -------------------------- load_dotenv(os.path.join(os.path.dirname(__file__), '.env')) JIRA_SERVER = os.getenv("JIRA_SERVER", "https://golsteam.atlassian.net") JIRA_EMAIL = os.getenv("JIRA_EMAIL") JIRA_API_TOKEN = os.getenv("JIRA_TOKEN") # -------------------------- # Assignee Mapping # -------------------------- assignee_map = { "Priyanka S": "712020:f5e4004d-0a88-455b-b6e7-a8673d0deb9b", "Bhupendra N": "712020:f47f67da-b21c-4424-9b71-1b8c0d3d0a58", "Shamsher S": "712020:b8f39c86-b3b3-4417-8802-65e422e7b1a1", "Awadhesh": "5daecffeb86cd40c2da5b814", "Monish B": "712020:dab8b27b-72b1-487b-8601-9891f845e1d7", "Nikhil B": "712020:b8137e7f-a9eb-4e2c-9334-c553234d7851", "Sakshi J": "712020:2055ac99-ebe7-4220-b4a6-0f00c31c794c", "Shambhu K": "712020:12c64083-13c9-4143-8c89-f2abdb6884f0", "Sufiyan A": "712020:adebb062-e0f1-4b12-be4a-23fa16640c67", "Ramkrishan D": "712020:6abe06b5-3b14-4c48-b7c1-0b6dbb0e8f9a" } month_name_map = { "January": 1, "February": 2, "March": 3, "April": 4, "May": 5, "June": 6, "July": 7, "August": 8, "September": 9, "October": 10, "November": 11, "December": 12 } google = oauth.register( name='google', client_id="YOUR_CLIENT_ID", client_secret="YOUR_CLIENT_SECRET", access_token_url='https://oauth2.googleapis.com/token', authorize_url='https://accounts.google.com/o/oauth2/auth', api_base_url='https://www.googleapis.com/oauth2/v1/', client_kwargs={ 'scope': 'openid email profile' } ) # -------------------------- # Jira Search Function # -------------------------- def search_jira_issues(jql): url = f"{JIRA_SERVER}/rest/api/3/search/jql" params = { "jql": jql, "maxResults": 1000, "fields": "summary" } response = requests.get( url, params=params, auth=(JIRA_EMAIL, JIRA_API_TOKEN), headers={"Accept": "application/json"} ) response.raise_for_status() return response.json()["issues"] # -------------------------- # Get Worklogs for Issue # -------------------------- def get_issue_worklogs(issue_key): url = f"{JIRA_SERVER}/rest/api/3/issue/{issue_key}/worklog" response = requests.get( url, auth=(JIRA_EMAIL, JIRA_API_TOKEN), headers={"Accept": "application/json"} ) response.raise_for_status() return response.json()["worklogs"] # -------------------------- # Main Report Generator # -------------------------- def generate_jira_worklog_report(assignee_name, selected_month, year): try: assignee_id = assignee_map.get(assignee_name) if not assignee_id: return None month = month_name_map.get(selected_month) if not month: return None # --- Dates --- start_date = f"{year}-{month:02d}-01" end_day = monthrange(year, month)[1] end_date = f"{year}-{month:02d}-{end_day}" all_dates = pd.date_range(start=start_date, end=end_date).strftime('%d-%b').tolist() # --- JQL --- jql = f''' assignee = {assignee_id} AND ( (created >= "{start_date}" AND created <= "{end_date}") OR (updated >= "{start_date}" AND updated <= "{end_date}") ) ORDER BY created ASC ''' issues = jira.search_issues(jql, maxResults=1000) data = [] for issue in issues: summary = issue.fields.summary project = issue.fields.project.name assignee = issue.fields.assignee.displayName if issue.fields.assignee else "Unassigned" key = issue.key worklogs = jira.worklogs(key) for log in worklogs: author = log.author.displayName started = log.started[:10] date_str = datetime.strptime(started, "%Y-%m-%d").strftime('%d-%b') time_spent_hrs = round(log.timeSpentSeconds / 3600, 2) # Filter only selected assignee logs if author != assignee_name: continue data.append({ 'Assignee': assignee, 'Project': project, 'Summary': summary, 'Date': date_str, 'Time Spent (h)': time_spent_hrs }) df = pd.DataFrame(data) if df.empty: return None # --- Pivot --- pivot = df.pivot_table( index=['Assignee', 'Project', 'Summary'], columns='Date', values='Time Spent (h)', aggfunc='sum', fill_value=0 ) pivot.columns.name = None pivot = pivot.reset_index() pivot = pivot.round(2) pivot = pivot.fillna('') # --- Ensure all dates --- for date in all_dates: if date not in pivot.columns: pivot[date] = '' pivot = pivot[['Assignee', 'Project', 'Summary'] + all_dates] # --- Totals --- pivot_for_sum = pivot.replace('', 0).infer_objects(copy=False) total_row_values = pivot_for_sum[all_dates].sum().round(1).tolist() total_row = ['Total (per day)', '', ''] + total_row_values total_row_df = pd.DataFrame([total_row], columns=pivot.columns) monthly_total_value = round(sum(total_row_values), 1) monthly_total_row = ['Total Monthly Hours', '', ''] + [''] * (len(all_dates) - 1) + [monthly_total_value] monthly_total_df = pd.DataFrame([monthly_total_row], columns=pivot.columns) pivot_display = pivot.replace(0.0, '') final_df = pd.concat([pivot_display, total_row_df, monthly_total_df], ignore_index=True) return final_df except Exception as e: print("Error:", e) return None #generate_jira_worklog_report("Shamsher S", "March", 2026) @app.route("/", methods=["GET", "POST"]) def index(): return render_template("index.html") @app.route("/get-timesheet", methods=["GET", "POST"]) def getDownload(): assignee_list = list(assignee_map.keys()) month_list = list(month_name_map.keys()) if request.method == "POST": selected_assignee = request.form.get("assignee") selected_month = request.form.get("month") selected_year = request.form.get("year") try: year_int = int(selected_year) if selected_year else 2026 df = generate_jira_worklog_report(selected_assignee, selected_month, year_int) if df is None or df.empty: return render_template( "form.html", assignees=assignee_list, months=month_list, error=f"No data found for {selected_assignee} ({selected_month} {year_int})", current_year=year_int ) headers = df.columns.tolist() table_data = df.values.tolist() return render_template( "form.html", assignees=assignee_list, months=month_list, headers=headers, table_data=table_data, current_year=year_int ) except Exception as e: return render_template( "form.html", assignees=assignee_list, months=month_list, error=f"System Error: {str(e)}", current_year=2026 ) return render_template( "form.html", assignees=assignee_list, months=month_list, current_year=2026 )