Notes

# Aggregate quantities for each SKU in EMEA DSA
emeaDsaAggregated = {}
for index, row in emeaDsaData.iterrows():
sku = str(row.iloc[10]).strip() # Ensure we strip any whitespace
qty = row.iloc[13]

if pd.isna(sku) or sku.lower() in ["sku", "qty", "subtotal"]:
continue # Skip rows with irrelevant or missing SKU information

if sku in emeaDsaAggregated:
emeaDsaAggregated[sku] += qty
else:
emeaDsaAggregated[sku] = qty


notInEmeaDsa = []
notInEmeaQuote = []

# Compare SKUs and quantities
for sku, qty in emeaQuoteDict.items():
if sku in emeaDsaAggregated:
if emeaDsaAggregated[sku] != qty:
qtyMismatch.append((sku, qty, emeaDsaAggregated[sku]))
else:
notInEmeaQuote.append(sku)

for sku, qty in emeaDsaAggregated.items():
if sku not in emeaQuoteDict:
notInEmeaDsa.append(sku)

# Generate the report content
report_content = "Emea Gii vs. Emea Dsa Report:n"
report_content += f"Emea Gii Quote No.: {curEmeaQuote.quoteNumber}n"
report_content += f"Total: {curEmeaQuote.total}n"
report_content += f"Date: {curEmeaQuote.quoteDate}n"
report_content += f"Part Number: {curEmeaQuote.partNumber}n"
report_content += "-------------------------n"
# Add EMEA DSA quote details if available
report_content += "Emea Dsa details are not provided in the script.n"

report_content += "-------------------------n"
report_content += "The following items were not present in Emea Gii quote:n"
report_content += "n".join(notInEmeaDsa) + "n"
report_content += "The following items were present in Emea gii quote and not Emea Dsa quote:n"
report_content += "n".join(notInEmeaQuote) + "n"


# Initialize variables to hold the extracted details
quote_no_dsa = ""
total_usd_dsa = ""
quoted_on_dsa = ""

# Iterate over the specific rows and columns to extract the details
# Adjust the row and column indices based on the exact layout of your Excel file
for i in range(4, 8): # Assuming rows 4 to 7 contain the required details
row_values = [str(emeaDsaData.iloc[i, j]).strip() for j in range(3)] # Adjust column range if necessary
concatenated_value = " ".join(row_values)

# Extracting Quote No.
if "Quote No.:" in concatenated_value:
quote_no_dsa = concatenated_value.split("Quote No.:")[-1].strip()

# Extracting Total (USD)
if "Total(USD):" in concatenated_value:
total_usd_dsa = concatenated_value.split("Total(USD):")[-1].strip().replace("$", "").replace(",", "")

# Extracting Quoted On
if "Quoted On:" in concatenated_value:
quoted_on_dsa = concatenated_value.split("Quoted On:")[-1].strip()

# Converting total from string to float and then to string to exclude any non-numeric symbols
if total_usd_dsa:
try:
total_usd_dsa = str(float(total_usd_dsa))
except ValueError:
total_usd_dsa = "Invalid total value"

# Add EMEA DSA quote details to the report
report_content += "Emea Dsa Quote No.: " + quote_no_dsa + "n"
report_content += "Total(USD): " + total_usd_dsa + "n"
report_content += "Quoted On: " + quoted_on_dsa + "n"