import time
import sys
import os
import numpy as np
import pandas as pd
import win32com.client
import pythoncom


if len(sys.argv) < 2:
    print("Error: No file path provided.")
    sys.exit(1)

# Get the file path from the system argument
file_path = sys.argv[1]

# Check if the file exists
if not os.path.isfile(file_path):
    print(f"Error: The file '{file_path}' does not exist.")
    sys.exit(1)

    
print("Old File:")
print(file_path)

# Path to save the new .xlsx file
new_file_path = file_path+"x"

print("New File:")
print(new_file_path)
# Open the .xls file with Excel
print("Win Client Dispatch...")
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False  # Set to False to open in the background

# Open the file
print("Opening Excel in background...")
wb = excel.Workbooks.Open(file_path)

# Get the directory and file name (without extension)
directory, file_name = os.path.split(file_path)
file_name_without_ext = os.path.splitext(file_name)[0]

# Define the new file path with .xlsx extension
new_file_path = os.path.join(directory, f"{file_name_without_ext}.xlsx")
# Save as a new Excel Workbook (xlsx format)
print(f"Saving as: {new_file_path}")
wb.SaveAs(new_file_path, FileFormat=51)  # 51 = xlOpenXMLWorkbook (.xlsx)

time.sleep(5)
time.sleep(5)

# Close the workbook and quit Excel
wb.Close()
time.sleep(5)
time.sleep(5)

excel.Quit()

try:
    wb.Close()
except pythoncom.com_error as e:
    print(f"Error closing workbook: {e}")
    time.sleep(5)  # Wait for a few seconds before retrying
    try:
        wb.Close()
    except pythoncom.com_error as e:
        print(f"Failed to close workbook after retry: {e}")
        # Handle the error appropriately (e.g., log it, raise an exception, etc.)


print(f"File saved as: {new_file_path}")
# Dictionary mapping old column names to new column names
column_rename_mapping = {
    'REQUEST ID' : 'Request ID',
    'SEZ ID' : 'SEZ ID',
    'BOE TYPE':'Bill of Entry Type',
    'AUTHORISED DEALER CODE':'Authorised Dealer Code',
    'AUTHORISED DEALER NAME' : 'Authorised Dealer Name',
    'CLIENT ADDRESS' : 'Client Address(Joint Filing)',
    'CLIENT COUNTRY' : 'Client Country(Joint Filing)',
    'CLIENT GSTIN' : 'Client GSTIN(Joint Filing)',
    'CLIENT IEC' : 'Client IEC(Joint Filing)',
    'CLIENT TYPE' : 'Client Type',
    'COUNTRY OF CONSIGNMENT' : 'Country of Consignment',
    'ENTITY NAME [IMPORTERS NAME]' : 'Entity Name [Importers Name]',
    'ENTITY ID' : 'FTWZ Entity ID',
    'HAWB / HBL DATE 1' : 'HAWB / HBL Date (Shipment Details)',
    'HAWB / HBL DATE 2' : 'HAWB / HBL Date 2 (Shipment Details)',
    'HAWB / HBL NO 1' : 'HAWB / HBL No. (Shipment Details)',
    'HAWB / HBL NO 2' : 'HAWB / HBL No. 2 (Shipment Details)',
    'ENTITY STATUS AS ON PREVIOUS DAY' : 'Entity Status as on Previous Day',
    'IGM DATE 1' : 'IGM Date (Shipment Details)',
    'IGM DATE 2' : 'IGM Date 2 (Shipment Details)',
    'IGM NO 1' : 'IGM No. (Shipment Details)',
    'IGM NO 2' : 'IGM No. 2 (Shipment Details)',
    'MAWB / MBL DATE 1' : 'MAWB / MBL Date (Shipment Details)',
    'MAWB / MBL DATE 2' : 'MAWB / MBL Date 2 (Shipment Details)',
    'MAWB / MBL NO 1' : 'MAWB / MBL No. (Shipment Details)',
    'MAWB / MBL NO 2' : 'MAWB / MBL No. 2 (Shipment Details)',
    'IS OUTWARD REMITTANCE INVOLVED (Y/N)' : 'Outward Remittance Involved (Y/N)',
    'PORT OF DISCHARGE' : 'Port of Discharge',
    'PORT OF ORIGIN' : 'Port of Origin',
    'PORT OF SHIPMENT' : 'Port of Shipment',
    'PREVIOUS IMPORT [Y/N]' : 'Previous Import [Y/N]',
    'PURPOSE OF TRANSACTION' : 'Purpose of Transaction',
    'REIMPORT [Y/N]' : 'Reimport [Y/N]',
    'REQUEST ASSESSMENT DATE' : 'Request Assessment Date',
    'REQUEST CANCELLATION DATE' : 'Request Cancellation Date',
    'REQUEST OUT OF CHARGE DATE' : 'Request out of Charge Date',
    'REQUEST STATUS' : 'Request Status',
    'REQUEST SUBMISSION DATE' : 'Request Submission Date',
    'SEZ NAME' : 'SEZ Name',
    'SOURCE OF IMPORT' : 'Source of Import',
    'THOKA / NOTING NUMBER' : 'Thoka / Bill of Entry Number',
    'TOTAL NO OF PACKAGES 1' : 'Total Number of Packages (Shipment Details)',
    'TOTAL NO OF PACKAGES 2' : 'Total Number of Packages 2 (Shipment Details)',
    'UOM FOR GROSS WEIGHT 1' : 'UOM for Gross Weight (Shipment Details)',
    'UOM FOR GROSS WEIGHT 2' : 'UOM for Gross Weight 2 (Shipment Details)',
    'UOM FOR PACKAGES 1' : 'UOM for Packages (Shipment Details)',
    'UOM FOR PACKAGES 2' : 'UOM for Packages 2 (Shipment Details)',
    'GROSS WEIGHT 1' : 'Gross Weight (Shipment Details)',
    'GROSS WEIGHT 2' : 'GROSS WEIGHT 2 (Shipment Details)',
    'INVOICE NO' : 'Invoice No (Invoices)',
    'INVOICE DATE' : 'Invoice Date (Invoices)',
    'INVOICE TYPE' : 'Invoice Type (Invoices)',
    'SUPPLIER COUNTRY' : 'Supplier Country (Invoices)',
    'SUPPLIER NAME' : 'Supplier Name (Invoices)',
    'SUPPLIERS ADDRESS' : 'Suppliers Address (Invoices)',
    'ITEM DESCRIPTION' : 'Item Description (Items)',
    'AD UNDER SECTION 3(1) (ADE)' : 'AD under Section 3(1) (ADE) (Items)',
    'ADDITIONAL DUTY OF EXCISE (GSI)' : 'Additional Duty of Excise (GSI) (Items)',
    'ADDITIONAL DUTY OF EXCISE (NCCD OF EXCISE)' : 'Additional Duty of Excise (NCCD of Excise) (Items)',
    'ASSESSABLE VALUE FOR THE ITEM IN INR' : 'Assessable Value for the Item in INR (Items)',
    'BASIC CUSTOMS DUTY' : 'Basic Customs Duty (Items)',
    'BCD TO BE CONSIDERED FOR CALCULATING IGST' : 'BCD to be Considered for Calculating IGST (Items)',
    'CESS CUSTOMS' : 'Cess Customs (Items)',
    'CESS EXCISE' : 'Cess Excise (Items)',
    'CIF VALUE FOR THE ITEM IN INR' : 'CIF Value for the Item in INR (Items)',
    'COMPENSATION CESS AMOUNT' : 'Compensation Cess Amount (Items)',
    'Countervailing Duty (of Additional Customs Duty)' : 'Countervailing Duty (of Additional Customs Duty) (Items)',
    'COUNTRY OF ORIGIN' : 'Country of Origin (Items)',
    'CTH NO.' : 'CTH No. (Items)',
    'DUTY FORGONE / PAID AMOUNT' : 'Duty Forgone / Paid Amount (Items)',
    'DUTY FORGONE / PAID FLAG' : 'Duty Forgone / Paid Flag (Items)',
    'EDUCATION CESS CUSTOMS' : 'Education Cess Customs (Items)',
    'EDUCATION CESS EXCISE' : 'Education Cess Excise (Items)',
    'EXCHANGE RATE' : 'Exchange Rate (Items)',
    'EXIM SCHEME NAME / CODE' : 'Exim Scheme Name / Code (Items)',
    'FREIGHT IN INR ' : 'Freight In INR (Items)',
    'Goods Measurement Unit' : 'Goods Measurement Unit (Items)',
    'HEALTH CESS CUSTOMS' : 'Health Cess Customs (Items)',
    'HEALTH CESS EXCISE' : 'Health Cess Excise (Items)',
    'IGST AMOUNT' : 'IGST Amount (Items)',
    'ITEM TYPE' : 'Item Type (Items)',
    'JOINT FILING / CLIENT DETAILS' : 'Joint Filing / Client Details',
    'MODE OF TRANSPORT' : 'Mode of Transport (Items)',
    'NCCD' : 'NCCD (Items)',
    'OTHER ADDITIONAL CENTRAL EXCISE DUTY' : 'Other Additional Central Excise Duty (Items)',
    'OTHER ADDITIONAL CUSTOMS DUTY' : 'Other Additional Customs Duty (Items)',
    'PRODUCT VALUE (IN INVOICE CURRENCY)' : 'Product Value (In Invoice Currency) (Items)',
    'PROVISIONAL REQUEST [Y/N]' : 'Provisional Request [Y/N] (Items)',
    'QUANTITY' : 'Quantity (Items)',
    'UNIT PRICE (IN INVOICE CURRENCY)' : 'Unit Price (In Invoice Currency) (Items)',
    'CURRENCY':'Currency (Items)',
    'INSURANCE IN INR' : 'Insurance In INR (Items)',
    'OTHER CENTRAL EXCISE DUTIES' : 'Other Central Excise Duties (Items)',
    'OTHER CUSTOMS DUTIES' : 'Other Customs Duties (Items)',
    'ROAD AND INFRASTRUCTURE CESS ON CUSTOMS' : 'Road and Infrastructure Cess on Customs (Items)',
    'SAD' : 'SAD (Items)',
    'SAFEGUARD' : 'Safeguard (Items)',
    'SAPTA BCD' : 'SAPTA BCD (Items)',
    'SECTOR FINALIZED' : 'Sector Finalized (Items)',
    'SHE CESS CUSTOMS' : 'SHE Cess Customs (Items)',
    'SHE CESS EXCISE' : 'SHE Cess Excise (Items)',
    'SOCIAL WELFARE SURCHARGE ON CUSTOMS' : 'Social Welfare Surcharge on Customs (Items)',
    'SOCIAL WELFARE SURCHARGE ON GST' : 'Social Welfare Surcharge on GST (Items)',
    'SP EXCISE DUTY (SCHEDULE II)' : 'SP Excise Duty (Schedule II) (Items)',
    'SURCHARGE ON BCD' : 'Surcharge on BCD (Items)',
    'ADDITIONAL DUTY OF EXCISE (TTA)' : 'Additional Duty of Excise (TTA) (Items)',
    'ADDITIONAL DUTY OF EXCISE (TTW)' : 'Additional Duty of Excise (TTW) (Items)',
    'AIDC on Customs' : 'AIDC on Customs (Items)',
    'ANTI DUMPING' : 'Anti Dumping (Items)',
    'AIDC on Excise' : 'AIDC on Excise (Items)'
}

columns_to_retrieve = list(column_rename_mapping.keys())
invoice_cols = [value for key, value in column_rename_mapping.items() if '(Invoices)' in value or value == 'Request ID']
item_col = [value for key, value in column_rename_mapping.items() if '(Items)' in value or value == 'Request ID']
shipment_cols = [value for key, value in column_rename_mapping.items() if '(Shipment Details)' in value or value == 'Request ID']
all_cols = list(column_rename_mapping.values())
header = set(all_cols) - set(invoice_cols) - set(item_col) - set(shipment_cols)
header_cols = list(header)
header_cols.append('Request ID')
item_col.append('Invoice No (Invoices)')


def parse_date(date_str):
    if isinstance(date_str, pd.Timestamp):
        # If date_str is already a datetime object, format it directly
        return date_str.strftime('%Y-%m-%d')
    
    # Convert to string if date_str is not already a string
    date_str = str(date_str)
    
    # Check the separator in the date string
    if '-' in date_str:
        # Format is yyyy-dd-mm or yyyy-mm-dd
        try:
            # Try parsing as yyyy-dd-mm
            dt = pd.to_datetime(date_str, format='%Y-%d-%m %H:%M:%S')
            return dt.strftime('%Y-%m-%d')
        except ValueError:
            try:
                # Try parsing as yyyy-mm-dd if the previous format fails
                dt = pd.to_datetime(date_str, format='%Y-%m-%d %H:%M:%S')
                return dt.strftime('%Y-%m-%d')
            except ValueError:
                return None
    elif '/' in date_str:
        # Format is mm/dd/yyyy or mm/dd/yyyy hh:mm:ss AM/PM
        try:
            dt = pd.to_datetime(date_str, format='%m/%d/%Y %I:%M:%S %p')
            return dt.strftime('%Y-%m-%d')
        except ValueError:
            try:
                dt = pd.to_datetime(date_str, format='%m/%d/%Y')
                return dt.strftime('%Y-%m-%d')
            except ValueError:
                return None
    return None  # Return None if format is unknown



header_cols


columns_to_retrieve


# Read the Excel file
#df_unfiltered = pd.read_excel(r"FTWZ Transformation/E+H Tests/Data Import - 12.08.2024/Trade data BE Import - July.xlsx", usecols=columns_to_retrieve,dtype='object')


try:
    df_unfiltered = pd.read_excel(new_file_path, usecols=columns_to_retrieve, dtype='object',skiprows=2)
    print("File successfully read!")
except Exception as e:
    print(f"Error reading the file: {e}")
    sys.exit(1)


df_unfiltered

 
# 1. Total Rows
# 2. Total E+H Rows
# 3. Unique Header Data Rows
# 4. Unique Invoices
# 5. Unique Item Rows
# 6. Sum of Qty[df vs import df]
# 7. Sum of BCD [df vs import df]
# 8. Sum of Ass Value[df vs import df]
# 9. Sum of Total Duty[df vs import df]
# 10. No. of total rows [df vs import df] should be same.


df_unfiltered['AUTHORISED DEALER CODE']

# Drop all rows where the column 'Client IEC(Joint Filing)' does not contain the value E+H IEC Code.
df = df_unfiltered[df_unfiltered['CLIENT IEC'] == 300014953]
print("Total Rows:")
print(len(df_unfiltered))
print("E+H(300014953) Records: ")
print(len(df))
# Rename columns using the dictionary
df = df.rename(columns=column_rename_mapping)


df

#df_header
header = df[header_cols]
#df_invoices
invoice = df[invoice_cols]
invoice.rename(columns={'Request ID': 'Request ID (Invoices)'}, inplace=True)

#df_items
item = df[item_col]
item.rename(columns={'Request ID': 'Request ID (Items)'}, inplace=True)
item.rename(columns={'Invoice No (Invoices)':'Invoice No (Items)'},inplace=True)
item = item.astype('object')
#df_shipments
shipment = df[shipment_cols]
shipment.rename(columns={'Request ID': 'Request ID (Shipment Details)'}, inplace=True)



shipment

#shipment split and append for each request ID
#DROP DUPLICATE SHIPMENTS
uni_shipments = shipment.drop_duplicates()
uni_shipments

cols_first_shipment = [
                        'Request ID (Shipment Details)',
                         'HAWB / HBL Date (Shipment Details)',
                         'HAWB / HBL No. (Shipment Details)',
                         'IGM Date (Shipment Details)',
                         'IGM No. (Shipment Details)',
                         'MAWB / MBL Date (Shipment Details)',
                         'MAWB / MBL No. (Shipment Details)',
                         'Total Number of Packages (Shipment Details)',
                         'UOM for Gross Weight (Shipment Details)',
                         'UOM for Packages (Shipment Details)',
                         'Gross Weight (Shipment Details)']

cols_second_shipment = [
                        'Request ID (Shipment Details)',
                        'HAWB / HBL Date 2 (Shipment Details)',
                        'HAWB / HBL No. 2 (Shipment Details)',
                        'IGM Date 2 (Shipment Details)',
                        'IGM No. 2 (Shipment Details)',
                        'MAWB / MBL Date 2 (Shipment Details)',
                        'MAWB / MBL No. 2 (Shipment Details)',
                        'Total Number of Packages 2 (Shipment Details)',
                        'UOM for Gross Weight 2 (Shipment Details)',
                        'UOM for Packages 2 (Shipment Details)',
                        'GROSS WEIGHT 2 (Shipment Details)'
]

shipment_col_rename = {
    'Request ID (Shipment Details)':'Request ID (Shipment Details)',
    'HAWB / HBL Date 2 (Shipment Details)':'HAWB / HBL Date (Shipment Details)',
    'HAWB / HBL No. 2 (Shipment Details)':'HAWB / HBL No. (Shipment Details)',
    'IGM Date 2 (Shipment Details)':'IGM Date (Shipment Details)',
    'IGM No. 2 (Shipment Details)':'IGM No. (Shipment Details)',
    'MAWB / MBL Date 2 (Shipment Details)': 'MAWB / MBL Date (Shipment Details)',
    'MAWB / MBL No. 2 (Shipment Details)':'MAWB / MBL No. (Shipment Details)',
    'Total Number of Packages 2 (Shipment Details)':'Total Number of Packages (Shipment Details)',
    'UOM for Gross Weight 2 (Shipment Details)' : 'UOM for Gross Weight (Shipment Details)',
    'UOM for Packages 2 (Shipment Details)':'UOM for Packages (Shipment Details)',
    'GROSS WEIGHT 2 (Shipment Details)':'Gross Weight (Shipment Details)'

}




first_shipment = uni_shipments[cols_first_shipment]
second_shipment = uni_shipments[cols_second_shipment]



first_shipment.head()


second_shipment = second_shipment.rename(columns=shipment_col_rename)
second_shipment.head()


shipment_consolidated = pd.concat([first_shipment,second_shipment], ignore_index=False,)
shipment_consolidated.dropna(subset=['HAWB / HBL No. (Shipment Details)'], inplace=True)




shipment_consolidated


header


uni_header = header.drop_duplicates()
uni_header = uni_header.astype('object')
uni_inv = invoice.drop_duplicates()
uni_inv = uni_inv.astype('object')
shipment_consolidated = shipment_consolidated.drop_duplicates()
shipment_consolidated = shipment_consolidated.astype('object')



len(shipment_consolidated)
len(df['HAWB / HBL No. (Shipment Details)'].unique())

 
# 172400992002
# 172400921022
# 172400964514
# 

#header[header["Request ID"]==172400992002].to_csv("header_drop_duplicates.csv")
#shipments_for_header

#shipments_for_header = shipment_consolidated[shipment_consolidated["Request ID (Shipment Details)"]==172400964514]
#shipments_for_header


import_df = pd.DataFrame()
for i in uni_header["Request ID"]:
    print("Request ID:\n")
    print(i)
    this_header = uni_header[uni_header["Request ID"]==i].reset_index(drop=True)
    inv_for_header = uni_inv[uni_inv["Request ID (Invoices)"]==i].reset_index(drop=True)
    item_for_header = item[item["Request ID (Items)"]==i].reset_index(drop=True)
    shipments_for_header = shipment_consolidated[shipment_consolidated["Request ID (Shipment Details)"]==i].reset_index()
    #print("Invoices:\n")
    #print(inv_for_header)
    #print("item:\n")
    print(shipments_for_header)
    df_concatenated = pd.concat([this_header,shipments_for_header,inv_for_header, item_for_header], axis=1).astype('object')
    import_df = pd.concat([import_df,df_concatenated], ignore_index=True)
    if(len(inv_for_header)>1 and len(item_for_header)>1):
        print("Test Point")
        continue

 
# 


df_concatenated['Authorised Dealer Code']


datatypes = import_df.dtypes


dtypes = import_df.dtypes
dtypes

 
# Functions for Date format conversion and Zero Fillings

#Function to convert date format
def convert_to_date_format(df, fields):
    for field in fields:
        try:
            df[field] = pd.to_datetime(df[field], errors='raise').dt.strftime('%Y-%m-%d') #here's the date format
        except Exception as e:
            print(f"Error converting field {field}: {e}")
            raise

# Function to fill NaN, null, blank, or empty entries with zero
def fill_empty_with_zero(df, fields):
    for field in fields:
        # Replace empty strings with zero
        df[field] = df[field].replace('', 0)
        # Replace None with zero
        df[field] = df[field].replace(None, 0)
        # Replace NaN with zero
        df[field] = df[field].replace(np.nan, 0)

def replace_empty_with_zero(value):
    if pd.isnull(value) or value == '':
        return 0
    return value


import_df['Old Request out of Charge Date'] = import_df['Request out of Charge Date']
import_df['Request out of Charge Date'] = import_df['Request out of Charge Date'].apply(parse_date)



test_dates = import_df[['Old Request out of Charge Date', 'Request out of Charge Date']]


test_dates.drop_duplicates(inplace=True)

 
# Check Date Conversion
# 


test_dates


import_df['SHE Cess Customs (Items)']


date_fields = [
               "HAWB / HBL Date (Shipment Details)",
               "MAWB / MBL Date (Shipment Details)",
               "Request Assessment Date",
               "Request Cancellation Date",
               "Invoice Date (Invoices)",
               "Request out of Charge Date",
               "Request Submission Date",
               "IGM Date (Shipment Details)"
               ]

convert_to_date_format(import_df, date_fields)

fields_to_fill = [
'AD under Section 3(1) (ADE) (Items)',
'Additional Duty of Excise (GSI) (Items)',
'Additional Duty of Excise (NCCD of Excise) (Items)',
'Assessable Value for the Item in INR (Items)',
'Basic Customs Duty (Items)',
'BCD to be Considered for Calculating IGST (Items)',
'Cess Customs (Items)',
'Cess Excise (Items)',
'CIF Value for the Item in INR (Items)',
'Compensation Cess Amount (Items)',
'Countervailing Duty (of Additional Customs Duty) (Items)',
'Duty Forgone / Paid Amount (Items)',
'Duty Forgone / Paid Flag (Items)',
'Education Cess Customs (Items)',
'Education Cess Excise (Items)',
'Freight In INR (Items)',
'Health Cess Customs (Items)',
'Health Cess Excise (Items)',
'IGST Amount (Items)',
'NCCD (Items)',
'Other Additional Central Excise Duty (Items)',
'Other Additional Customs Duty (Items)',
'Insurance In INR (Items)',
'Other Central Excise Duties (Items)',
'Other Customs Duties (Items)',
'Road and Infrastructure Cess on Customs (Items)',
'SAD (Items)',
'Safeguard (Items)',
'SAPTA BCD (Items)',
'SHE Cess Customs (Items)',
'SHE Cess Excise (Items)',
'Social Welfare Surcharge on Customs (Items)',
'Social Welfare Surcharge on GST (Items)',
'SP Excise Duty (Schedule II) (Items)',
'Surcharge on BCD (Items)',
'Additional Duty of Excise (TTA) (Items)',
'Additional Duty of Excise (TTW) (Items)',
'AIDC on Customs (Items)',
'Anti Dumping (Items)',
'AIDC on Excise (Items)'
]

# Apply the custom function to the specified fields
for field in fields_to_fill:
    import_df[field]=import_df[field].apply(replace_empty_with_zero)

#drop Request ID Duplicate Columns from Invoice, Item and Shipment
columns_to_drop = ["Request ID (Invoices)","Request ID (Items)","Request ID (Shipment Details)","index"]
import_df.drop(columns=columns_to_drop,inplace=True)


import_df

#import_df["Invoice Date (Invoices)"]


import_df

#new_col_dtypes = import_df.dtypes.rename("dtype")
#old_col_dtypes = df_concatenated.dtypes.rename("dtype")
#compare = pd.merge(new_col_dtypes, old_col_dtypes, left_index=True, right_index=True, how='inner')
#excel_dtypes = df_unfiltered.dtypes

#excel_dtypes

 
# Import file - Validations with Input Data
# 1. Total Rows
# 2. Total E+H Rows
# 3. Unique Header Data Rows
# 4. Unique Invoices
# 5. Unique Item Rows
# 6. Sum of Qty[df vs import df]
# 7. Sum of BCD [df vs import df]
# 8. Sum of Ass Value[df vs import df]
# 9. Sum of Total Duty[df vs import df]
# 10. No. of total rows [df vs import df] should be same.

print("BE DATA Validations Starts Here :-")
print("Excel Rows:")
print(len(df_unfiltered))

print("E+H Rows:")
print(len(df))

status = True
#Header QC
print("Unique Header Rows:")
count_header_uni = len(uni_header)
print(count_header_uni)

print("Unique Output Header Count:")
count_output_header = import_df["Request ID"].count()
print(count_output_header)

if(count_header_uni==count_output_header):
    print("Header Count Checked")
else:
    status = False

#Invoice QC
print("Unique Invoice Rows:")
count_invoice_uni = len(uni_inv)
print(count_invoice_uni)

print("Output Invoice No. Count:")
count_invoice_header = import_df["Invoice No (Invoices)"].count()
print(count_invoice_header)

if(count_invoice_uni==count_invoice_header):
    print("Invoice Count Checked")
else:
    status = False


#Item QC
print("Item Rows:")
count_item_uni = len(item)
print(count_item_uni)

print("Output Rows:")
count_output_rows = len(import_df)
print(count_output_rows)

if(count_item_uni==count_output_rows):
    print("Total No. of Rows Checked")
else:
    status = False

#Shipment QC
print("Total Shipment Rows:")
count_shipments = shipment_consolidated["IGM No. (Shipment Details)"].count()
print(count_shipments)

print("Total Shipments in Output:")
count_output_shipments = import_df["IGM No. (Shipment Details)"].count()
print(count_output_shipments)


if(count_shipments==count_output_shipments):
    print("Total No. of Shipments Same")
else:
    status = False

#Sum Checks

sum_qty_input = df["Quantity (Items)"].sum()
sum_qty_output = import_df["Quantity (Items)"].sum()


sum_ass_val_input = df["Assessable Value for the Item in INR (Items)"].sum()
sum_ass_val_output = import_df["Assessable Value for the Item in INR (Items)"].sum()
print(f'ASS VALUE MATCHING - {sum_ass_val_output} {sum_ass_val_input}')

sum_bcd_input = df["Basic Customs Duty (Items)"].sum()
sum_bcd_output = import_df["Basic Customs Duty (Items)"].sum()

if (sum_qty_input==sum_qty_output):
    print("INPUT-OUTPUT QUANTITY matched!")
    if(sum_bcd_input==sum_bcd_output):
        print(f"INPUT-{sum_bcd_input} and OUTPUT BCD {sum_bcd_output} matched!")
        if(True):
            print("TEST IF STATEMENT")
        else:
            print("TEST ELSE STATEMENT")
            print(sum_ass_val_input)
            print(sum_ass_val_output)
            status=False
    else:
        print(f"INPUT-{sum_bcd_input} and OUTPUT BCD {sum_bcd_output} matched!")
        status=False        
else:
    print("INPUT-OUTPUT Quantity (Items) does not match!")
    status=False



print("BE Validations Ends Here.")

import_df

#purchase_details_df = pd.read_excel(r"C:\Users\Admin\Downloads\Consolidated Trade Data Apr May Jun 2024(Only BE IMP).xlsx",dtype='object')


#purchase_details_df['PO number']

#purchase_details_df = purchase_details_df[['REQUEST ID','ITEM DESCRIPTION','PO number','PO Position']]


#print(len(purchase_details_df))
#purchase_details_df.drop_duplicates(inplace=True)

#len(purchase_details_df)

#purchase_details_df

# Extract value between "^" characters
import_df['Item Subtext (Items)'] = import_df['Item Description (Items)'].str.extract(r'\^([^^]*)\^', expand=False)

# Check if extraction worked and fill with empty string if not
import_df['Item Subtext (Items)'].fillna('', inplace=True)

# Split the 'Item Subtext (DTA Sale Items)' column into two new columns safely
subtext_split = import_df['Item Subtext (Items)'].str.split('/', expand=True)

# Add the split columns to the DataFrame
import_df['Purchase Order Number (Items)'] = subtext_split[0]
import_df['Purchase Order Item No. (Items)'] = subtext_split[1]



test_po_det_extraction = import_df[['Item Subtext (Items)','Purchase Order Number (Items)', 'Purchase Order Item No. (Items)']]


test_po_det_extraction


print(len(import_df))
#final_df = pd.merge(import_df, purchase_details_df, left_on=['Item Description (Items)'], right_on=['ITEM DESCRIPTION'], how='left')
final_df = import_df
#merged_df.drop('Key2', axis=1, inplace=True)
print("After Join")
print(len(final_df))

#final_df['Purchase Order Number (Items)'] = final_df['PO number']
#final_df['Purchase Order Item No. (Items)'] = final_df['PO Position']
#columns_to_drop = ['PO number','PO Position','ITEM DESCRIPTION','REQUEST ID']
#final_df.drop(columns=columns_to_drop,axis=1,inplace=True)

final_df
print(final_df['Request out of Charge Date'].dtype)
ooc_date = pd.to_datetime(final_df['Request out of Charge Date'], errors='coerce')
start_date = ooc_date.min().strftime('%Y-%m-%d')
end_date = ooc_date.max().strftime('%Y-%m-%d')
print(f"From:{start_date}")
print(f"End:{end_date}")

# Create the filename with prefix and date range
prefix = "FTWZ-BE-Canofy-DataImport"
output_folder = os.path.dirname(file_path)
filename = os.path.join(output_folder, f"{prefix}_{start_date}_to_{end_date}.xlsx")
print(filename)
final_df.to_excel(filename,index=False)

print("Validations Ends")