Import Purchase Order from Lazada to ERPNext

lazada.py

import local_config as config
import pandas as pd
import numpy as np
import requests

# ERPNext API credentials
# api_url = config.ERPNEXT_URL_15_ERP + "/api/resource/Sales Invoice" or
api_url = "https://your-erpnext-url/api/resource/Sales%20Invoice"
api_key = config.ERPNEXT_API_KEY_ADMIN_15_ERP
api_secret = config.ERPNEXT_API_SECRET_ADMIN_15_ERP

# Load the Excel file according to file's path
file_path = "./SaleJul2024.xlsx"
df = pd.read_excel(file_path)

# Display the first few rows of the dataframe
# print(df.head())

# Load the conversion mapping file => incase you need to change itemName to newName
conversion_file_path = './ConvertItemsName.xlsx'
conversion_df = pd.read_excel(conversion_file_path)

# Create a dictionary for item name conversion
conversion_dict = pd.Series(conversion_df.newName.values, index=conversion_df.itemName).to_dict()

# Convert createTime to datetime if it's not already
# df['createTime'] = pd.to_datetime(df['createTime'])

# Parse createTime correctly
df['createTime'] = pd.to_datetime(df['createTime'], format='%d %b %Y %H:%M', errors='coerce')

# Filter out rows where status is 'cancelled'
df_filtered = df[df['status'] != 'canceled']

# Apply the conversion mapping to item names
df_filtered['itemName'] = df_filtered['itemName'].map(conversion_dict).fillna(df_filtered['itemName'])

# Display the filtered data
# print(df_filtered.head())

# Replace out of range float values with NaN and then fill with 0
df_filtered = df_filtered.replace([np.inf, -np.inf], np.nan).fillna(0)

# Extract relevant columns from Lazada's exported file.
relevant_columns = [
    "createTime",
    "orderNumber",
    "customerName",
    "paidPrice",
    "unitPrice",
    "sellerDiscountTotal",
    "shippingFee",
    "itemName",
    "variation",
    "status",
]
data = df[relevant_columns]

# Display the extracted data
# print(data.head())

# Testing ERPNext's Credential
def get_logged_user():
    headers = {
        "Authorization": f"token {api_key}:{api_secret}",
        "Content-Type": "application/json",
    }
    response = requests.get(
        "https://your-erpnext-url/api/method/frappe.auth.get_logged_user",
        headers=headers,
    )
    print("headers", headers)
    return response.json()


result = get_logged_user()
for r in result.values():
    print("Account: ", r)


# Function to create a Sales Invoice
def create_sales_invoice(row):
    payload = {
        'naming_series': 'BL.6707-',  # Incase you have custom naming series, Specify your document series here
        'customer': row['customerName'],
        'posting_date': row['createTime'].strftime('%Y-%m-%d'),  # Formatting the date
        'po_no': row['orderNumber'],  # Add order number to Customer's Purchase Order field
        'items': [
            {
                'item_code': row['itemName'],  # Adjust item_code if applicable
                'item_name': row['itemName'],  # Using itemName as item name
                'description': row['variation'],  # Using variation as description
                'qty': 1,  # Assuming quantity as 1, adjust if needed
                'rate': row['paidPrice'],
                # 'amount': row['paidPrice']  # Assuming paidPrice is the amount
            }
        ],
        # 'additional_discount_percentage': (row['sellerDiscountTotal'] / row['unitPrice']) * 100 if row['unitPrice'] else 0,
        # 'taxes': [
        #     {
        #         'charge_type': 'Actual',
        #         'account_head': 'Shipping Charges',  # Adjust the account head as per your setup
        #         'description': 'Shipping Fee',
        #         'rate': 0,
        #         'tax_amount': row['shippingFee'],
        #         'include_in_print_rate': 1  # Setting this to true (1) to include tax in the basic rate
        #     }
        # ]
    }
    headers = {
        'Authorization': f'token {api_key}:{api_secret}',
        'Content-Type': 'application/json'
    }
    response = requests.post(api_url, json=payload, headers=headers)
    return response.json()

# Iterate over the filtered rows and create Sales Invoices
for index, row in df_filtered.iterrows():
    result = create_sales_invoice(row)
    print(result)

local_config.py

ERPNEXT_URL_15_ERP = "your-erpnext-url"

ERPNEXT_API_KEY_ADMIN_15_ERP = "your-erpnext-api-key"
ERPNEXT_API_SECRET_ADMIN_15_ERP = "your-erpnext-api-secret"