Untitled

import os
import pandas as pd
import shutil
import csv
from datetime import datetime, date
import win32com.client as win32
import re   

# Function to merge CSV files into a single XLSX file
def merge_csv_to_xlsx(folder_path, output_file, log_file, encoding):
    # Find all CSV files modified today in the specified folder
    csv_files = [f for f in os.listdir(folder_path) if f.endswith('.csv') and date.fromtimestamp(os.path.getmtime(os.path.join(folder_path, f))) == date.today()]

    # If no CSV files are found, print a message and return
    if not csv_files:
        print(f"No CSV files found in the specified folder: {folder_path}")
        return

    # Create an empty DataFrame to hold merged data
    merged_data = pd.DataFrame()

    # Loop through the found CSV files and concatenate them into the merged_data DataFrame
    for csv_file in csv_files:
        file_path = os.path.join(folder_path, csv_file)
        data = pd.read_csv(file_path, encoding=encoding)
        merged_data = pd.concat([merged_data, data], ignore_index=True, sort=False)

    # Define the output path and create any necessary directories
    output_path = os.path.join('Z:\\ADEN Delays\\CARRIER', output_file)
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    # Save the merged data to an Excel file
    merged_data.to_excel(output_path, index=False)

    # Write the merge operation details to the log file
    with open(log_file, 'a') as f:
        f.write(f"Merged these CSV files from \"{folder_path}\":\n")
        for csv_file in csv_files:
            f.write(f"\"{os.path.join(folder_path, csv_file)}\"\n")
        f.write(f"into \"{output_file}\" at \"{output_path}\"\n\n")



    print(f"Merged files successfully. Output saved to {output_path}")


def process_folder(folder_path, output_file, log_file, use_excel=False):
    # Find all files modified today in the specified folder
    files = [f for f in os.listdir(folder_path) if date.fromtimestamp(os.path.getmtime(os.path.join(folder_path, f))) == date.today()]

    # Loop through the found files
    for file in files:
        file_path = os.path.join(folder_path, file)
        output_path = os.path.join('Z:\\ADEN Delays\\CARRIER', output_file)
        # Create any necessary directories for the output path
        os.makedirs(os.path.dirname(output_path), exist_ok=True)

        # If the file is an Excel file, copy it to the output path
        if file_path.lower().endswith(('.xls', '.xlsx')):
            shutil.copy(file_path, output_path)
        elif file_path.lower().endswith('.csv') and use_excel:  # If the file is a CSV and we want to use Excel, convert it
            excel = win32.gencache.EnsureDispatch('Excel.Application')
            excel.Visible = False
            wb = excel.Workbooks.Open(file_path)
            wb.SaveAs(output_path, FileFormat=51)  # FileFormat=51 is for xlsx
            wb.Close()
            excel.Quit()

        # Write the file processing details to the log file
        with open(log_file, 'a') as f:
            f.write(f"Processed \"{file_path}\" to \"{output_path}\"\n")

        print(f"Processed {os.path.basename(folder_path)} folder. Latest file saved to {output_path}")


# Define the base folder path for carriers
folder_path_carriers = r"Z:\\ADEN Delays"

# Update the log file name to include the date and time
current_time = datetime.now().strftime("%Y%m%d_%H%M%S")
log_file = f"log_{current_time}.txt"

# Process the Cosco and OOCL files with Excel in the background
process_folder(os.path.join(folder_path_carriers, 'COSCO'), "COSCO.xlsx", log_file, use_excel=True)
process_folder(os.path.join(folder_path_carriers, 'OOCL'), "OOCL.xlsx", log_file, use_excel=True)

# Copy the XLSX files from ONE and CMA as they are
process_folder(os.path.join(folder_path_carriers, 'ONE'), "ONE.xlsx", log_file)
process_folder(os.path.join(folder_path_carriers, 'CMA'), "CMA.xlsx", log_file)

# Maersk is already being processed correctly with the existing merge_csv_to_xlsx function

# Function to send an email with the report in the email body formatted with HTML
def send_email_report(log_file, recipient_emails):
    # Initialize Outlook instance
    outlook = win32.Dispatch('outlook.application')
    mail = outlook.CreateItem(0)
    mail.To = recipient_emails  # Set the recipient email

    # Read the log file and format its contents with HTML
    with open(log_file, 'r') as f:
        lines = f.readlines()

    # Initialize HTML email body
    html_body = "<html><body><p style='font-family:Calibri; font-size:12pt; color:black;'>Report:</p>"
    for line in lines:
        # Use regex to find text within quotes
        quoted_text = re.findall(r'"([^"]+)"', line)
        for text in quoted_text:
            # Replace the quoted text with a styled version
            styled_text = f"<span style='font-family:Consolas, monospace; font-size:8pt; color:#5A1E02;'>{text}</span>"
            line = line.replace(f'"{text}"', styled_text)

        # Add the rest of the text with standard Outlook font
        html_body += f"<span style='font-family:Calibri; font-size:12pt; color:black;'>{line.strip()}</span><br>"
    
# ASCII Art placeholder
    ascii_art = r"""
<pre style='font-family:Consolas, monospace; font-size:10pt;'>
 ___  ___      _______       ___      ________       ________     
|\  \|\  \    |\  ___ \     |\  \    |\   ___  \    |\_____  \    
\ \  \\\  \   \ \   __/|    \ \  \   \ \  \\ \  \    \|___/  /|   
 \ \   __  \   \ \  \_|/__   \ \  \   \ \  \\ \  \       /  / /   
  \ \  \ \  \   \ \  \_|\ \   \ \  \   \ \  \\ \  \     /  /_/__  
   \ \__\ \__\   \ \_______\   \ \__\   \ \__\\ \__\   |\________\
    \|__|\|__|    \|_______|    \|__|    \|__| \|__|    \|_______|
</pre>
"""
    html_body += ascii_art  # Add the ASCII art to the email body

    html_body += "</body></html>"

    # Set the email body and subject
    mail.HTMLBody = html_body
    mail.Subject = 'Python log for Heinz carrier report conversion - test 2'

    # Display the email
    mail.Display(True)  # Set to False if you want to send the email directly



# Define the paths and output files
folder_path_maersk = r"Z:\\Maersk"
output_file_maersk = "MAERSK.xlsx"



# Merge CSV files to XLSX for Maersk
merge_csv_to_xlsx(folder_path_maersk, output_file_maersk, log_file, 'utf-8-sig')

# Remove COSCO and OOCL from the dictionary
carrier_encodings = {
    'CMA': None,  # Assuming no CSV files to decode or that they are already in utf-8
    'ONE': None,  # Assuming no CSV files to decode or that they are already in utf-8
}

folder_path_carriers = r"Z:\\ADEN Delays"
for carrier, encoding in carrier_encodings.items():
    carrier_folder_path = os.path.join(folder_path_carriers, carrier)
    output_file = f"{carrier}.xlsx"
    # Use a default encoding if none is specified
    encoding = encoding if encoding is not None else 'utf-8'
    process_folder(carrier_folder_path, output_file, log_file, encoding)

# Send an email with the report
recipient_emails = "   add emils here " 

send_email_report(log_file, recipient_emails)