Automating using Autogen AI to fetch azure migrate resport and generate PPT

Im new to autogen ai currently using autogen version 0.7 Automating using Autogen AI to fetch azure migrate assessment report and generating PPT using autogen agents group chat agent but some times all getting slides generated when i tried again getting only few slides getting generated. may i know the issue or any documents to follow that will be helpfull. below is the code

from autogen import AssistantAgent, UserProxyAgent, GroupChat, GroupChatManager
import os
from dotenv import load_dotenv
import pandas as pd
import json
import requests
from azure.storage.blob import BlobServiceClient
from azure.identity import ClientSecretCredential
import matplotlib.pyplot as plt

# Load environment variables
load_dotenv()

# Disable docker (optional)
os.environ["AUTOGEN_USE_DOCKER"] = "False"

# Azure OpenAI Config
config_list1 = {
    "model": os.getenv("AZURE_OPENAI_DEPLOYMENT"),
    "api_key": os.getenv("AZURE_OPENAI_API_KEY"),
    "base_url": os.getenv("AZURE_OPENAI_ENDPOINT"),
    "api_version": os.getenv("AZURE_OPENAI_API_VERSION"),
    "api_type": "azure",
    "temperature": 0.1
}

# Report and output paths
order_id = "f05qw5cqnu"
#f05qw5cqnu
directory = r"C:\Users\Administrator\Desktop\AzureMigrate-ppt\AzMigrate"
report_file = os.path.join(directory, "AssessmentReprt.xlsx")
#ppt_file_path = os.path.join(directory, "AzureMigrate_Strategy_Summary_{order_id}.pptx")
ppt_file_path = os.path.join(directory, f"AzureMigrate_Strategy_Summary_{order_id}.pptx")

summary_file_path = os.path.join(directory, f"migrate_Strategy_Summary_{order_id}.txt")
# -----------------------------------------
# Function to download Azure Migrate report
# -----------------------------------------
def download_azure_migrate_report():
    tenant_id = os.getenv("AZURE_TENANT_ID")
    client_id = os.getenv("AZURE_CLIENT_ID")
    client_secret = os.getenv("AZURE_CLIENT_SECRET")
    subscription_id = os.getenv("AZURE_SUBSCRIPTION_ID")
    resource_group = os.getenv("AZURE_RESOURCE_GROUP")
    assessment_project = os.getenv("AZURE_MIGRATE_PROJECT")
    group_name = os.getenv("AZURE_MIGRATE_GROUP")
    assessment_name = os.getenv("AZURE_MIGRATE_ASSESSMENT")

    try:
        credential = ClientSecretCredential(tenant_id, client_id, client_secret)
        token = credential.get_token("https://management.azure.com/.default")

        url = (
            f"https://management.azure.com/subscriptions/{subscription_id}/resourceGroups/{resource_group}"
            f"/providers/Microsoft.Migrate/assessmentProjects/{assessment_project}/groups/{group_name}"
            f"/assessments/{assessment_name}/downloadUrl?api-version=2019-10-01"
        )

        headers = {
            "Authorization": f"Bearer {token.token}",
            "Content-Type": "application/json"
        }

        response = requests.post(url, headers=headers)
        if response.status_code != 200:
            print(f" Failed to get download URL: {response.status_code}")
            print(response.text)
            return False

        report_url = response.json().get("assessmentReportUrl")
        if not report_url:
            print(" No download URL returned.")
            return False

        report_response = requests.get(report_url)
        if report_response.status_code == 200:
            with open(report_file, "wb") as f:
                f.write(report_response.content)
            print(f" Report downloaded and saved to: {report_file}")
            return True
        else:
            print(f" Failed to download report file. Status: {report_response.status_code}")
            return False

    except Exception as e:
        print(f" Exception occurred: {str(e)}")
        return False

# -----------------------------
# Trigger download if necessary
# -----------------------------
if not os.path.exists(report_file):
    print(" Report not found, downloading...")
    success = download_azure_migrate_report()
    if not success:
        raise Exception(" Failed to get the report. Cannot proceed.")
else:
    print(" Report already exists. Proceeding...")


xl = pd.ExcelFile(report_file)
data = {sheet: pd.read_excel(xl, sheet_name=sheet) for sheet in xl.sheet_names}
json_data = {sheet: df.to_dict(orient='records') for sheet, df in data.items()}
json_str = json.dumps(json_data)
#print(" Loaded Sheets:", list(data.keys()))

# -----------------------------
# Create agents
# -----------------------------
user = UserProxyAgent(
    name="User",
    system_message="You are a user agent.",
    human_input_mode="NEVER",
    code_execution_config={"use_docker": False}
)

assistant = AssistantAgent(
    name="Assistant",
    system_message="""You are a helpful assistant that analyzes Azure Migrate Excel data. 
If PowerPoint generation, chart creation, or visual formatting is needed, 
delegate that work to the SlideAgent.""",
    llm_config=config_list1,
    code_execution_config={"work_dir": directory, "use_docker": False},
    max_consecutive_auto_reply=8
)

slide_agent = AssistantAgent(
    name="SlideAgent",
    system_message="""You are an expert Python developer responsible for generating PowerPoint slides using python-pptx and matplotlib.

Your task is to:
- Use a pre-styled PowerPoint template file from the following path:
  `C:\\Users\\Administrator\\Desktop\\AzureMigrate-ppt\\AzMigrate\\template_base.pptx`
- Always begin by loading the template using:
    from pptx import Presentation
    prs = Presentation("C:\\\\Users\\\\Administrator\\\\Desktop\\\\AzureMigrate-ppt\\\\AzMigrate\\\\template_base.pptx")
- **After loading**, remove all existing slides while keeping the theme, using:
    ```python
    for i in range(len(prs.slides) - 1, -1, -1):
        rId = prs.slides._sldIdLst[i].rId
        prs.part.drop_rel(rId)
        del prs.slides._sldIdLst[i]
    ```
- Now use this `prs` object to add new slides — all slides will use the theme, font, background, and layout from the template.

Slide generation guidelines:
- Use the cleaned `prs` for every new slide.
- All new blank slides must follow the layout and theme from the loaded template.
- Do not use default layouts — match fonts, background, margins, and alignment from the template.
- For charts: generate visuals using `matplotlib`, save as images, and insert into slides.
- For tables: align them properly and keep styling consistent with the template.
- For slides with both chart and table: place side-by-side with proper spacing and no overlap.
- Ensure readability and consistent spacing, and create exactly **one slide for each of the seven** report sections.

- Do not skip any slides. Maintain a professional and cohesive design across all slides.
""",
    llm_config=config_list1,
    code_execution_config={"work_dir": directory, "use_docker": False},
    max_consecutive_auto_reply=25
)


groupchat = GroupChat(
    agents=[user, assistant, slide_agent],
    messages=[],
    max_round=30
)

manager = GroupChatManager(groupchat=groupchat, llm_config=config_list1)


message = f"""Analyze this Azure Migrate Assessment data from multiple sheets and extract key insights. Source:
{json_str}

Return ONLY the below details in a **concise and structured manner**, no extra commentary
1. **Migration Summary**: Total number of servers, Databases, Applications, Windows Servers, Linux Servers and Count of each OS flavor
2. **Migration Strategy – 6R Approach**: Count how many servers fall under each of the 6Rs.
3. **Migration Strategy for Storage System**: For any server or VM where the storage size is more than 100 GB, recommend an appropriate Microsoft or third-party migration tool.
4. **Database Server Migration Strategy**: For any server running SQL Server, suggest a suitable Azure migration strategy.
5. **Network Recommendations for Migration**: Provide network-related recommendations for Azure migration.
6. **Cost Recommendation**: Monthly and yearly cost estimation for all servers.
7. **Application Complexity Analysis**: Provide the number of applications categorized by complexity level specifically, how many fall under Low, Medium, and High complexity.

After completing the summary:
- Save the summary to a text file at `{directory}\\migrate_Strategy_Summary_{order_id}.txt`.
- Generate a professional PowerPoint presentation (`.pptx`) at `{directory}\\AzureMigrate_Strategy_Summary_{order_id}.pptx`:
    - You must generate **exactly 7 content slides** plus title and agenda
    - Use a **new blank slide**
    - Use a **title slide** with the heading: "Migration Strategy Overview"
    - Include an **Agenda** slide listing all seven topics
    - Apply a **professional background theme** consistently across all slides
    - Use uniform spacing, margins, and layout throughout the deck
    - Use **uniform font size** and padding on every slide
    - Avoid **overlapping elements**: If a slide includes both a table and a chart, place them side-by-side with proper margins
    - Never skip any slide

For each of the seven content slides:

- **Migration Summary**:
    - Use a **new blank slide layout**
    - Insert a **table format** showing total number of servers, Databases, Applications, Windows Servers, Linux Servers and Count of each OS flavor

- **Migration Strategy – 6R Approach**:
    - Use a **new blank slide layout**
    - On the *left half*, Include a Table list of server names and counts as per 6R azure migration strategy
    - On the *right half*, Include and generate a pie chart using `matplotlib` and insert it using `python-pptx`

- **Migration Strategy for Storage System**:
    - Use a **new blank slide layout**
    - Insert a **table** showing servers/VMs with storage >100GB and the recommended migration tool

- **Database Server Migration Strategy**:
    - Use a **new blank slide layout**
    - Insert a **table** listing SQL Server instances and the corresponding recommended Azure migration strategy

- **Network Recommendations for Migration**:
    - Use a **new blank slide layout**
    - Present findings in a **structured table** Bandwidth requirements and connectivity solutions, For each application group, list firewalls and open ports

- **Cost Recommendation**:
    - Use a **new blank slide layout**
    - Include a **summary table** with monthly and yearly cost estimates for each server

- **Application Complexity Analysis**:
    - Use a **new blank slide layout**
    - Include a **table** with number of applications categorized by complexity level how many fall under Low, Medium, and High complexity

Use `python-pptx` and `matplotlib` to generate and format this PowerPoint directly. Ensure readability, alignment, spacing, and visual clarity across all slides.
"""


user.initiate_chat(manager, message=message)

# ----------------------------------
# Upload PPT and Summary to Azure Blob
# ----------------------------------
def upload_files_to_blob(file_paths):
    try:
        
        connect_str = os.getenv("connect_str")
        container_name = os.getenv("container_name")

        blob_service_client = BlobServiceClient.from_connection_string(connect_str)
        container_client = blob_service_client.get_container_client(container_name)

        # Create container if not exists
        if not container_client.exists():
            container_client.create_container()

        for file_path in file_paths:
            blob_client = container_client.get_blob_client(os.path.basename(file_path))
            with open(file_path, "rb") as data:
                blob_client.upload_blob(data, overwrite=True)
                print(f" Uploaded: {file_path}")

    except Exception as e:
        print(f" Upload error: {e}")

# Upload the generated files
file_paths = [ppt_file_path, summary_file_path]
upload_files_to_blob(file_paths)

print("PPT File:", os.path.basename(ppt_file_path))
print("Summary File:", os.path.basename(summary_file_path))

print(" PPT and summary file uploaded to Azure Blob Storage.")```