Hello,
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!
Example:
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
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.name, 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 = pr.name
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.name, 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 = pr.name
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}