update acordingly

[kevin373738]

import re
import requests
import pandas as pd
import urllib.parse

🔹 GitLab Access Token

GITLAB_TOKEN = ""
if not GITLAB_TOKEN:
raise ValueError("❌ ERROR: GitLab token is missing! Set it with 'export GITLAB_TOKEN=your_token_here'")

🔹 List of project paths (namespace/project-name)

PROJECTS = [
"pspl/poems-mobile3-ios",
"pspl/poems-mobile3-android"
]

🔹 Target branch

TARGET_BRANCH = "develop_phase_1"

🔹 Jira base URL

JIRA_BASE_URL = "https://poems3.atlassian.net/browse/"

🔹 GitLab API base URL

GITLAB_API = "https://git.ecomedic.vn/api/v4"

Extract Jira ticket number

def extract_jira_ticket(title):
match = re.search(r"[([A-Z]+-\d+)]", title)
return match.group(1) if match else None

Extract Type (Task/Bugfix) and Summary

def extract_type_and_summary(title):
task_match = re.search(r"\bTask\b", title, re.IGNORECASE)
bug_match = re.search(r"\bBug\b", title, re.IGNORECASE)

type_label = "Task" if task_match else "Bug" if bug_match else "Unknown"

# Extract text after the last []
summary_match = re.split(r"\[.*?\]\s*", title)
summary = summary_match[-1] if summary_match else title

return type_label, summary.strip()

Fetch open Merge Requests

def fetch_merge_requests(project_id, platform):
encoded_branch = urllib.parse.quote(TARGET_BRANCH, safe="")
url = f"{GITLAB_API}/projects/{project_id}/merge_requests?state=opened&target_branch={encoded_branch}"
headers = {"PRIVATE-TOKEN": GITLAB_TOKEN}

try:
    response = requests.get(url, headers=headers)
    response.raise_for_status()  # ✅ Ensures API request success
    merge_requests = response.json()

    mr_list = []
    for mr in merge_requests:
        jira_ticket = extract_jira_ticket(mr["title"])
        jira_ticket_link = f'=HYPERLINK("{JIRA_BASE_URL}{jira_ticket}", "{jira_ticket}")' if jira_ticket else "N/A"

        mr_list.append({
            "Platform": "iOS" if "ios" in platform.lower() else "Android",
            "Type": extract_type_and_summary(mr["title"])[0],
            "Jira Ticket": jira_ticket_link,
            "Summary": extract_type_and_summary(mr["title"])[1],
            "Status": "",
            "MR URL": mr["web_url"],
            "Assignee": mr["assignee"]["name"] if mr.get("assignee") and isinstance(mr["assignee"], dict) else "Unassigned"
        })
    return mr_list

except requests.exceptions.RequestException as e:
    print(f"❌ Error: Failed to fetch MRs for {platform}. Error: {e}")
    return []

Save MR details to an Excel file

def save_to_excel(mrs):
if not mrs:
print("⚠️ No open MRs found!")
return

df = pd.DataFrame(mrs)

Sort by Jira Ticket (placing "N/A" at the bottom)

df = df.sort_values(by=["Jira Ticket"], key=lambda x: x.fillna("ZZZ"))

# ✅ Extract numbers from Jira Ticket and sort numerically
df["Jira Number"] = df["Jira Ticket"].str.extract(r"(\d+)$").astype(float)
df = df.sort_values(by=["Jira Number"], na_position="last").drop(columns=["Jira Number"])

output_file = "merge_requests.xlsx"
df.to_excel(output_file, index=False)
print(f"✅ Excel file generated: {output_file}")

🔹 Function to get project ID dynamically

def get_project_id(project_path):
encoded_path = urllib.parse.quote(project_path, safe="")
url = f"{GITLAB_API}/projects/{encoded_path}"
headers = {"PRIVATE-TOKEN": GITLAB_TOKEN}

try:
    response = requests.get(url, headers=headers)
    response.raise_for_status()
    return response.json().get("id")

except requests.exceptions.RequestException as e:
    print(f"❌ Error: Unable to get project ID for {project_path}. Error: {e}")
    return None

Main execution

if name == "main":
all_merge_requests = []

for project in PROJECTS:
    project_id = get_project_id(project)
    if project_id:
        all_merge_requests.extend(fetch_merge_requests(project_id, project))

save_to_excel(all_merge_requests)