def invoice_qol(name, payments, loyalty_card_no, loyalty_program,
loyalty_points, cashback_receipt):
def set_cost_center(item):
if cost_center:
item.cost_center = cost_center
doc = make_sales_invoice(name)
cost_center = (frappe.db.get_value(
"Branch", doc.os_branch, "os_cost_center") if doc.os_branch else None)
mapf(set_cost_center, doc.items)
if loyalty_program and cint(loyalty_points):
doc.redeem_loyalty_points = 1
doc.os_loyalty_card_no = loyalty_card_no
doc.loyalty_program = loyalty_program
doc.loyalty_points = cint(loyalty_points)
doc.loyalty_redemption_cost_center = cost_center
if cashback_receipt:
doc.os_cashback_receipt = cashback_receipt
get_payments = compose(
partial(filterf, lambda x: x.get("amount") != 0),
partial(
map,
partial(keyfilter, lambda x: x in ["mode_of_payment", "amount"])),
json.loads,
)
payments_proc = get_payments(payments)
if payments_proc:
doc.is_pos = 1
mapf(lambda x: doc.append("payments", x), payments_proc)
doc.update_stock = 0
doc.insert(ignore_permissions=True)
doc.submit()
return doc.name
def update_sales_orders(sales_orders, action, lab_tech=None):
transition = compose(lambda doc: apply_workflow(doc, action),
partial(frappe.get_doc, "Sales Order"))
mapf(transition, json.loads(sales_orders))
if lab_tech and action == "Proceed to Deliver":
update = compose(lambda x: frappe.db.set_value(
"Sales Order", x, "os_lab_tech", lab_tech))
mapf(update, json.loads(sales_orders))
def update_sales_orders(sales_orders, action, lab_tech=None):
workflow_name = frappe.model.meta.get_workflow_name("Sales Order")
if not workflow_name:
frappe.throw(NO_WORKFLOW_MSG)
if workflow_name != "Optic Store Sales Order":
frappe.throw(
frappe._("Operation not allowed for Workflow: {}".format(
frappe.bold(workflow_name))))
transition = compose(lambda doc: apply_workflow(doc, action),
partial(frappe.get_doc, "Sales Order"))
mapf(transition, json.loads(sales_orders))
if lab_tech and action == "Proceed to Deliver":
update = compose(lambda x: frappe.db.set_value(
"Sales Order", x, "os_lab_tech", lab_tech))
mapf(update, json.loads(sales_orders))
def _get_customers_details(pos_profile, query_date):
customers = compose(list, partial(pluck, "name"),
get_customers_list)(pos_profile)
details = frappe.db.sql(
"""
SELECT
name,
old_customer_id,
customer_name,
os_loyalty_card_no,
loyalty_program,
{customer_details_fields}
FROM `tabCustomer` WHERE name IN %(customers)s
""".format(customer_details_fields=", ".join(CUSTOMER_DETAILS_FIELDS)),
values={"customers": customers},
as_dict=1,
)
def add_loyalty_points(customer):
loyalty_points = get_loyalty_details(
customer.get("name"),
customer.get("loyalty_program"),
expiry_date=query_date,
)
return merge(customer, pick(["loyalty_points"], loyalty_points))
return mapf(compose(add_loyalty_points, partial(valfilter, lambda x: x)),
details)
def _get_item_type(items, settings):
groups = mapf(lambda x: x.item_group, items)
if settings.special_order_item_group in groups:
return "Special"
if settings.standard_item_group in groups:
return "Standard"
return "Other"
def _get_data(clauses, values, keys):
items = frappe.db.sql(
"""
SELECT
i.brand AS brand,
i.item_code AS item_code,
i.item_group AS item_group,
i.item_name AS item_name,
ipss.price_list_rate AS standard_selling,
SUM(b.actual_qty) AS qty,
ipms.price_list_rate AS minimum_selling
FROM `tabItem` AS i
LEFT JOIN `tabBin` AS b ON {bin_clauses}
LEFT JOIN ({standard_selling_sq}) AS ipss ON ipss.item_code = i.item_code
LEFT JOIN ({minimum_selling_sq}) AS ipms ON ipms.item_code = i.item_code
WHERE {clauses}
GROUP BY i.item_code
""".format(
standard_selling_sq=price_sq("Standard Selling"),
minimum_selling_sq=price_sq("Minimum Selling"),
**clauses
),
values=values,
as_dict=1,
)
return mapf(partial(pick, keys), items)
def _get_data(clauses, values, keys, query):
rows = frappe.db.sql(query.format(clauses=clauses),
values=values,
as_dict=1)
make_row = partial(pick, keys)
return mapf(make_row, rows)
def _validate_spec_parts(items):
parts = mapf(lambda x: x.os_spec_part, items)
def count(part):
return compose(len, list, partial(filter, lambda x: x == part))
for part in ["Frame", "Lens Right", "Lens Left"]:
if count(part)(parts) > 1:
frappe.throw(_("There can only be one row for {}".format(part)))
def get_ref_doc(pf):
if not pf_settings.get(pf, {}).get("is_invoice_pf"):
return [
{"doctype": "Sales Order", "docname": sales_order, "print_format": pf}
]
return mapf(
lambda x: {"doctype": "Sales Invoice", "docname": x, "print_format": pf},
sales_invoices,
)
def _get_branch_collections(payments, yesterday, settings):
def make_aggregator(start, end):
return compose(
sum_by("amount"),
lambda x: filter(
lambda row: row.branch == x and
(start <= row.posting_date <= end),
payments,
),
partial(get, "branch"),
)
sum_today = make_aggregator(yesterday, yesterday)
sum_half_month = make_aggregator(*_get_half_month_dates(yesterday))
sum_month = make_aggregator(*_get_month_dates(yesterday))
sum_quarter = make_aggregator(*_get_quarter_dates(yesterday))
sum_half_year = make_aggregator(*_get_half_year_dates(yesterday))
sum_year = make_aggregator(*_get_year_dates(yesterday))
def set_amounts(x):
collected_mtd = sum_month(x)
return merge(
{
"collected_today":
sum_today(x),
"half_monthly_sales":
sum_half_month(x),
"collected_mtd":
collected_mtd,
"monthly_target_remaining":
get("monthly_target", x, 0) - collected_mtd,
},
{"quarterly_sales": sum_quarter(x)}
if settings.show_quarter else {},
{"half_yearly_sales": sum_half_year(x)}
if settings.show_half_year else {},
{"yearly_sales": sum_year(x)} if settings.show_year else {},
)
return mapf(
lambda x: merge(x, set_amounts(x)),
frappe.get_all(
"Branch",
fields=[
"name AS branch",
"os_half_monthly_target AS half_monthly_target",
"os_target AS monthly_target",
"os_quarterly_target AS quarterly_target",
"os_half_yearly_target AS half_yearly_target",
"os_yearly_target AS yearly_target",
],
filters=[[
"name", "in", (settings.branches_to_show or "").split("\n")
]],
),
)
def _get_mop_collections(payments, yesterday):
get_sum_today = compose(
sum_by("amount"),
lambda x: filter(
lambda row: row.mode_of_payment == x and row.posting_date == yesterday,
payments,
),
partial(get, "mop"),
)
return mapf(
lambda x: merge(x, {"collected_today": get_sum_today(x)}),
frappe.get_all("Mode of Payment", fields=["name AS mop"]),
)
def before_save(doc, method):
settings = frappe.get_single("Optical Store Settings")
frames = mapf(lambda x: x.item_group, settings.frames)
lenses = mapf(lambda x: x.item_group, settings.lens)
validate_item_group = _validate_item_group(frames, lenses)
frame, lens_right, lens_left = get_parts(doc.items)
for item in doc.items:
if item.os_spec_part:
validate_item_group(item)
else:
if not frame and item.item_group in frames:
item.os_spec_part = "Frame"
frame = item
elif not lens_right and item.item_group in lenses:
item.os_spec_part = "Lens Right"
lens_right = item
elif not lens_left and item.item_group in lenses:
item.os_spec_part = "Lens Left"
lens_left = item
_validate_spec_parts(doc.items)
def _get_data(data):
def get_reference_st(stock_entry):
return frappe.db.exists(
"Stock Transfer", {"outgoing_stock_entry": stock_entry}
) or frappe.db.exists("Stock Transfer", {"outgoing_stock_entry": stock_entry})
def add_fields(row):
if row.voucher_type == "Stock Entry":
purpose = frappe.db.get_value("Stock Entry", row.voucher_no, "purpose")
return frappe._dict(
merge(
row,
{
"purpose": purpose,
"reference_stock_transfer": get_reference_st(row.voucher_no)
if purpose == "Material Transfer"
else None,
},
)
)
return row
return mapf(add_fields, data)
def _get_data(clauses, values, keys):
items = frappe.db.sql(
"""
SELECT
si.name AS invoice_name,
sii.sales_order AS order_name,
si.posting_date AS invoice_date,
si.posting_time AS invoice_time,
sii.brand AS brand,
sii.item_code AS item_code,
sii.item_group AS item_group,
sii.description AS description,
bp.valuation_rate AS valuation_rate,
sii.price_list_rate AS selling_rate,
sii.rate AS rate,
sii.qty AS qty,
sii.qty * IFNULL(bp.valuation_rate, 0) AS valuation_amount,
IF(
sii.discount_percentage = 100,
sii.price_list_rate * sii.qty,
sii.amount * 100 / (100 - sii.discount_percentage)
) AS amount_before_discount,
IF(
sii.discount_percentage = 100,
sii.price_list_rate * sii.qty,
sii.amount * (100 / (100 - sii.discount_percentage) - 1)
) AS discount_amount,
sii.discount_percentage AS discount_percentage,
sii.amount AS amount_after_discount,
sii.os_minimum_selling_rate AS ms1,
IF(
ABS(sii.amount) < sii.os_minimum_selling_rate * sii.qty,
'Yes',
'No'
) AS below_ms1,
sii.os_minimum_selling_2_rate AS ms2,
IF(
ABS(sii.amount) < sii.os_minimum_selling_2_rate,
'Yes',
'No'
) AS below_ms2,
si.os_sales_person AS sales_person,
si.os_sales_person_name AS sales_person_name,
IF(
si.total = 0,
0,
si.total_commission * sii.amount / si.total
) AS commission_amount,
si.customer AS customer,
si.customer_name AS customer_name,
si.os_notes AS notes,
si.orx_dispensor AS dispensor,
si.os_branch AS branch,
IF(
si.update_stock = 1 OR so.workflow_state = 'Collected',
'Collected',
'Achieved'
) AS sales_status,
si.update_stock AS own_delivery,
si.is_return AS is_return,
dn.posting_date AS delivery_date
FROM `tabSales Invoice Item` AS sii
LEFT JOIN `tabSales Invoice` AS si ON
si.name = sii.parent
LEFT JOIN `tabSales Order` AS so ON
so.name = sii.sales_order
LEFT JOIN (
SELECT
idni.si_detail AS si_detail,
idn.is_return AS is_return,
idn.posting_date AS posting_date
FROM `tabDelivery Note Item` AS idni
LEFT JOIN `tabDelivery Note` AS idn ON
idn.name = idni.parent
) AS dn ON
dn.si_detail = sii.name AND
dn.is_return = si.is_return
LEFT JOIN `tabBin` AS bp ON
bp.item_code = sii.item_code AND
bp.warehouse = sii.warehouse
WHERE {clauses}
ORDER BY invoice_date
""".format(clauses=clauses),
values=values,
as_dict=1,
)
def add_collection_date(row):
def get_collection_date(x):
if x.sales_status == "Achieved":
return None
if x.own_delivery or x.is_return:
return x.invoice_date
return x.delivery_date
return merge(row, {"collection_date": get_collection_date(row)})
def add_payment_remarks(items):
payments = _get_payments(items)
def fn(row):
make_remark = compose(
lambda x: ", ".join(x),
partial(
map,
lambda x: "{mop}: {amount}".format(mop=x[0], amount=x[1])),
lambda x: x.items(),
lambda lines: reduceby(
"mode_of_payment",
lambda a, x: a + get("paid_amount", x, 0),
lines,
0,
),
lambda x: concatv(get(x.invoice_name, payments, []),
get(x.order_name, payments, [])),
frappe._dict,
)
return merge(row, {"remarks": make_remark(row)})
return fn
def set_null(k, v):
if v:
return k, v
if k not in [
"valuation_rate",
"selling_rate",
"rate",
"qty",
"valuation_amount",
"amount_before_discount",
"discount_amount",
"discount_percentage",
"amount_after_discount",
"ms1",
"ms2",
"commission_amount",
]:
return k, None
return k, 0
template = reduce(lambda a, x: merge(a, {x: None}), keys, {})
make_row = compose(
partial(pick, keys),
partial(itemmap, lambda x: set_null(*x)),
partial(merge, template),
add_payment_remarks(items),
add_collection_date,
)
return mapf(make_row, items)
def get_items(
start,
page_length,
price_list,
item_group,
search_value="",
pos_profile=None,
customer=None,
):
debug = frappe.db.get_single_value("Optical Store Settings", "debug_query")
search_data = (search_serial_or_batch_or_barcode_number(search_value)
if search_value else {})
clauses, values = _get_conditions(
merge(
{
"start": cint(start),
"page_length": cint(page_length),
"price_list": price_list,
"item_group": item_group or get_root_of("Item Group"),
"search_value": search_value,
"pos_profile": pos_profile,
"customer": customer,
},
search_data,
))
items = frappe.db.sql(
"""
SELECT
i.name AS item_code,
i.item_name AS item_name,
i.image AS item_image,
i.idx AS idx,
i.is_stock_item AS is_stock_item,
i.variant_of AS variant_of
FROM `tabItem` AS i
LEFT JOIN `tabItem Group` AS ig ON ig.name = i.item_group
{stock_clause}
WHERE {clauses}
ORDER BY i.idx
LIMIT %(start)s, %(page_length)s
""".format(**clauses),
values=values,
as_dict=1,
debug=debug,
)
def list_items(items):
make_list = compose(partial(filterf, lambda x: x), unique, concatv)
return make_list(pluck("item_code", items), pluck("variant_of", items))
make_prices = compose(partial(valmap, partial(groupby, "item_code")),
partial(groupby, "price_list"))
# better to use a second query to fetch prices because combining with items query
# takes considerably longer
prices = (make_prices(
frappe.db.sql(
"""
SELECT
i.name AS item_code,
ip.price_list AS price_list,
ip.price_list_rate AS price_list_rate,
ip.currency AS currency
FROM `tabItem Price` AS ip
LEFT JOIN `tabItem` AS i ON i.name = ip.item_code
WHERE
ip.selling = 1 AND
ip.item_code IN %(items)s AND
IFNULL(ip.uom, '') IN (i.stock_uom, '') AND
IFNULL(ip.min_qty, 0) <= 1 AND
IFNULL(ip.customer, '') IN (%(customer)s, '') AND
CURDATE() BETWEEN
IFNULL(ip.valid_from, '2000-01-01') AND
IFNULL(ip.valid_upto, '2500-12-31')
""",
values={
"items": list_items(items),
"customer": customer
},
as_dict=1,
debug=debug,
)) if items else [])
def add_price(prices):
def make_price(item_code):
return compose(
excepts(StopIteration, first, lambda x: {}),
partial(get, item_code, default=[]),
lambda x: get(x, prices, {}),
)
def fn(item):
item_price = make_price(item.item_code)
template_price = make_price(item.variant_of)
sp = item_price(price_list) or template_price(price_list)
ms1 = item_price("Minimum Selling") or template_price(
"Minimum Selling")
ms2 = item_price("Minimum Selling 2") or template_price(
"Minimum Selling 2")
return merge(
item,
{
"price_list_rate": get("price_list_rate", sp, 0),
"currency": get("currency", sp, 0),
"os_minimum_selling_rate": get("price_list_rate", ms1, 0),
"os_minimum_selling_2_rate": get("price_list_rate", ms2,
0),
},
)
return fn
make_item = compose(
partial(
pick,
[
"item_code",
"item_name",
"idx",
"is_stock_item",
"price_list_rate",
"currency",
"os_minimum_selling_rate",
"os_minimum_selling_2_rate",
],
),
add_price(prices),
)
return merge(
{"items": mapf(make_item, items)},
pick(["barcode", "serial_no", "batch_no"], search_data),
)
def update_prices(item_code, prices):
price_list_rates = json.loads(prices)
mapf(lambda x: _update_price(item_code, **x), price_list_rates)
