Getting price based on Pricing Rules in a custom script


I have created a custom document called “Customer Price List”. It fetches given product categories and their prices. The fetched prices are from a Price List, but we also have Pricing Rules set up for certain categories and items. Is there any possibility that by using a custom Python server script we can obtain pricing rules that apply for given item and customer, and calculate the correct price?

I would be grateful for any help!


import frappe

def calculate_price(doc, method):
# Fetch pricing rules based on item and customer
pricing_rules = frappe.get_all(“Pricing Rule”, filters={“item_code”: doc.item_code, “customer”: doc.customer}, fields=[“price_list_rate”, “discount_percentage”])

# If pricing rules are found, apply the first matching rule to calculate the price
if pricing_rules:
    price_list_rate = pricing_rules[0].get("price_list_rate")
    discount_percentage = pricing_rules[0].get("discount_percentage")

    # Calculate the price using pricing rules
    discounted_rate = price_list_rate - (price_list_rate * (discount_percentage / 100))
    doc.price = discounted_rate
1 Like

Thank for the script outline. I thought that maybe some already existing system function could be used. I ended up with script (below) that meets our needs. Note that it does not cover all possibilities but for us it is enough.

def calculate_dicount_for_item_code(item_code, item_group, customer, price_list):
    price_list_rate = frappe.db.get_value("Item Price", {
        "item_code": item_code, "price_list": price_list}, "price_list_rate")
    # Fetch pricing rules based on item and customer

    # Get discounts on item groups
    query_item_groups = """
    SELECT, pr.discount_percentage, pr.min_amt, pr.min_qty, pr.rate_or_discount, pr.apply_on
    FROM `tabPricing Rule` AS pr
    JOIN `tabPricing Rule Item Group` AS prig ON prig.parent =
    WHERE prig.item_group = %s AND pr.customer = %s
    pricing_rules = frappe.db.sql(query_item_groups, (item_group, customer), as_dict=True)

    # Get discounts on item code
    query_item_code = """
    SELECT, pr.discount_percentage, pr.min_amt, pr.min_qty, pr.rate_or_discount, pr.apply_on, pr.discount_amount, pr.rate
    FROM `tabPricing Rule` AS pr
    JOIN `tabPricing Rule Item Code` AS pric ON pric.parent =
    WHERE pric.item_code = %s AND pr.customer = %s
    pricing_rules_on_item_code = frappe.db.sql(query_item_code, (item_code, customer), as_dict=True)

    if pricing_rules and pricing_rules_on_item_code:
        if (len(pricing_rules) > 1 and len(pricing_rules_on_item_code) >1):
            frappe.throw(f'Error message {item_code}')

    price_list_rate_float = float(price_list_rate if price_list_rate is not None else 0)
    # If pricing rules are found for item groups
    # GROUPS
    if pricing_rules:
        if(len(pricing_rules) > 1):
            frappe.throw(f'Error message {item_group}')
        if (pricing_rules[0]["min_qty"] < 2):
            discount_percentage = pricing_rules[0][("discount_percentage")]
            # Calculate the price using pricing rules
            discount_percentage_float = float(discount_percentage)
            discounted_rate = price_list_rate_float - (price_list_rate_float * (discount_percentage_float / 100.0))
            discount_amount = price_list_rate_float - discounted_rate
            return {"discounted_rate": discounted_rate, "price_list_rate": price_list_rate, "discount_percentage": discount_percentage, "discount_amount": discount_amount}
    # If pricing rules are found for item code
    elif pricing_rules_on_item_code:
        if(len(pricing_rules_on_item_code) > 1):
            frappe.throw(f'Wiele Pricing Rule dla {item_code}')
        if (pricing_rules_on_item_code[0]["min_qty"] < 2):
        # RATES
            if (pricing_rules_on_item_code[0]["rate_or_discount"] == "Rate"):
                new_rate_float = float(pricing_rules_on_item_code[0][("rate")])
                discount_amount_float = price_list_rate_float - new_rate_float
                discounted_rate = price_list_rate_float - discount_amount_float
                return {"discounted_rate": new_rate_float, "price_list_rate": price_list_rate, "discount_percentage": 0, "discount_amount": discount_amount_float}
            # DISCOUNT AMOUNT
            elif (pricing_rules_on_item_code[0]["rate_or_discount"] == "Discount Amount"):
                discount_amount = pricing_rules_on_item_code[0][("discount_amount")]
                discount_amount_float = float(discount_amount)
                discounted_rate = price_list_rate_float - discount_amount_float
                return {"discounted_rate": discounted_rate, "price_list_rate": price_list_rate, "discount_percentage": 0, "discount_amount": discount_amount_float}
    return {"discounted_rate": price_list_rate, "price_list_rate": price_list_rate, "discount_percentage": 0, "discount_amount": 0}