def set_available_qty(self):
for d in self.get("required_items"):
if d.source_warehouse:
d.available_qty_at_source_warehouse = get_latest_stock_qty(d.item_code, d.source_warehouse)
if self.wip_warehouse:
d.available_qty_at_wip_warehouse = get_latest_stock_qty(d.item_code, self.wip_warehouse)
def set_available_qty(self):
for d in self.get("required_items"):
if d.source_warehouse:
d.available_qty_at_source_warehouse = get_latest_stock_qty(d.item_code, d.source_warehouse)
if self.wip_warehouse:
d.available_qty_at_wip_warehouse = get_latest_stock_qty(d.item_code, self.wip_warehouse)
def get_requested_items_details(loggedInUser):
outerJson = []
record = frappe.db.sql("""select usr.role_profile_name from `tabUser` usr where usr.name = %s""",(loggedInUser))
if record:
cdrecord = frappe.db.sql("""select cd.name, cd.warehouse, cd.upstream_warehouse, cd.downstream_warehouse from `tabControlDocument` cd where cd.user =%s and cd.docstatus = 1 and cd.is_active = 1 and cd.is_default = 1""",(record[0]))
if cdrecord:
requestedItemRecords = frappe.db.sql(""" select ritms.item_code from `tabControlDocument Item` ritms where ritms.parent = %s""",(cdrecord[0][0]))
warehouse = cdrecord[0][1]
upstream_warehouse = cdrecord[0][2]
downstream_warehouse = cdrecord[0][3]
if requestedItemRecords:
for item in requestedItemRecords:
itemDetails = frappe.db.sql("""select itmtable.item_code, itmtable.stock_uom from `tabItem` itmtable where itmtable.item_code = %(itemCode)s""",{'itemCode': item[0]})
if itemDetails:
constructedJson = {}
availQnty = 0.0
constructedJson ['item_code'] = itemDetails[0][0]
constructedJson['uom'] = itemDetails[0][1]
if get_latest_stock_qty(itemDetails[0][0],upstream_warehouse):
availQnty = get_latest_stock_qty(itemDetails[0][0],upstream_warehouse)
constructedJson['available_qnty'] = availQnty
constructedJson['reqd_qnty'] = 0.0
outerJson.append(constructedJson)
else:
continue
if len(outerJson) == 0:
return None
else:
return outerJson
def check_stock_qty(self):
from erpnext.stock.stock_ledger import NegativeStockError
drug_availability = dict()
for d in self.medication_orders:
if not drug_availability.get(d.drug_code):
drug_availability[d.drug_code] = 0
drug_availability[d.drug_code] += flt(d.dosage)
for drug, dosage in drug_availability.items():
available_qty = get_latest_stock_qty(drug, self.warehouse)
# validate qty
if flt(available_qty) < flt(dosage):
frappe.throw(
_('Quantity not available for {0} in warehouse {1}').
format(frappe.bold(drug), frappe.bold(self.warehouse)) +
'<br><br>' +
_('Available quantity is {0}, you need {1}').format(
frappe.bold(available_qty), frappe.bold(dosage)) +
'<br><br>' +
_('Please enable Allow Negative Stock in Stock Settings or create Stock Entry to proceed.'
),
NegativeStockError,
title=_('Insufficient Stock'))
def map_paper_to_boxes(self):
self.paper_to_boxes = []
boxes = frappe.db.sql(
"""select box.name, bom.name from `tabCM Box` box left join `tabCM Box Description` bom on bom.box = box.name"""
)
paper_box_map = {}
for (box, box_desc) in boxes:
if (frappe.db.get_value("CM Box Description", box_desc) is None):
frappe.throw(
"Failed to find the description {0} for box {1}".format(
box_desc, box))
box_desc = frappe.get_doc("CM Box Description", box_desc)
for paper in box_desc.item_papers:
if (paper_box_map.get(paper.rm) is None):
paper_box_map[paper.rm] = set([box])
else:
paper_box_map[paper.rm].add(box)
for (paper, boxes) in paper_box_map.items():
paper_item = frappe.new_doc("CM PaperToBox Item")
paper_item.paper = paper
paper_item.box_count = len(boxes)
paper_item.paper_qty = get_latest_stock_qty(paper)
paper_item.boxes = ", ".join(boxes)
paper_item.box_desc = frappe.db.get_value(
"CM Box Description", filters={"box": next(iter(boxes))})
self.append("paper_to_boxes", paper_item)
self.sort_on_box_count()
def update_po_items(self, po):
from erpnext.stock.utils import get_latest_stock_qty
for row in self.items:
if row.s_warehouse and not row.t_warehouse:
item = [
d.name for d in po.required_items
if d.item_code == row.item_code
]
if not item:
po.append(
'required_items', {
'item_code':
row.item_code,
'item_name':
row.item_name,
'description':
row.description,
'source_warehouse':
row.s_warehouse,
'required_qty':
row.qty,
'transferred_qty':
row.quantity,
'valuation_rate':
row.valuation_rate,
'available_qty_at_source_warehouse':
get_latest_stock_qty(row.item_code, row.s_warehouse),
})
for child in po.required_items:
child.db_update()
def recalculate_items_balance():
items = frappe.get_all("Item")
for item in items:
frappe.db.set_value("Item",item.name,"balance",get_latest_stock_qty(item.name) or 0)
frappe.db.set_value("Item",item.name,"available_qty",get_item_available_qty(item.name) or 0)
return "Recalculate Items Balance is Done"
def get_maximum_production_capacity(paper_items): prod_qty = 50000 for paper_item in paper_items: max_qty = get_latest_stock_qty(paper_item.rm) if (max_qty is None): continue prod_qty = min(prod_qty, max_qty/paper_item.rm_weight) if (prod_qty == 50000): prod_qty = 0 return prod_qty
def validate(self):
items = []
user_remarks = "Special Closing Balance - {0}".format(self.name)
for item_row in self.closing_balance_details:
if not item_row.quantity:
item_row.quantity = 0
if item_row.item :
item_balance = get_latest_stock_qty(item_row.item, self.warehouse) or 0
item_row.item_balance = item_balance
def get_mapped_product(item_doc):
wc_product_category_id = frappe.db.get_value("Item Group",
item_doc.item_group,
"woocommerce_id_za")
woo_settings = frappe.get_doc("Woocommerce Settings")
shopping_cart_settings = frappe.get_doc("Shopping Cart Settings")
item_price = get_price(item_doc.item_code, woo_settings.price_list,
shopping_cart_settings.default_customer_group,
woo_settings.company)
if woo_settings.promo_price_list:
promo = get_price(item_doc.item_code, woo_settings.promo_price_list,
shopping_cart_settings.default_customer_group,
woo_settings.company)
print("settings : %s %s" % (woo_settings.warehouse or '', item_price))
warehouse = woo_settings.warehouse
qty = get_latest_stock_qty(item_doc.item_code, warehouse) or 0
product = {
"featured": item_doc.is_featured,
"type": "simple",
"weight": str(item_doc.weight_per_unit or "0"),
"sku": item_doc.ugs,
"manage_stock": item_doc.is_stock_item,
"stock_quantity": qty,
"regular_price": item_price and cstr(item_price["price_list_rate"])
or "",
"sale_price": promo and cstr(promo["price_list_rate"]) or "",
"description": item_doc.description,
"short_description": item_doc.description,
"name": item_doc.item_name,
"categories": [{
"id": wc_product_category_id
}],
#"images":{}
#"images": [
# {
# "src": "{}/{}".format(frappe.utils.get_url(), item_doc.image) if (item_doc.image and ' ' not in item_doc.image) else ""
# "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_front.jpg"
# }
#]
}
if item_doc.image and item_doc.send_product_image_again:
product['images'] = [{
"src":
"{}/{}".format(frappe.utils.get_url(), item_doc.image) if
(item_doc.image and ' ' not in item_doc.image) else ""
# "http://demo.woothemes.com/woocommerce/wp-content/uploads/sites/56/2013/06/T_2_front.jpg"
}]
frappe.db.set_value("Item", item_doc.item_code,
"send_product_image_again", 0)
if item_doc.woocommerce_id:
product["id"] = item_doc.woocommerce_id
return product
def update_board_name(self):
box_details = frappe.get_doc("CM Box Description", self.box_desc)
if (self.printed and self.layer_type == "Top"):
self.board_name = box_details.get_board_prefix(self.layer_type) + "-" + self.box
else:
papers = [(roll.rm_type, roll.paper_roll) for roll in self.paper_rolls]
self.board_name = box_details.get_board_name_from_papers(self.layer_type, papers)
box_details.create_board_item(self.board_name)
self.stock_qty = get_latest_stock_qty(self.board_name)
if (self.stock_qty is None): self.stock_qty = 0
def populate_order_items(self):
if (self.sales_order is None): return
order_items = frappe.db.sql("""select item_code, qty from `tabSales Order Item`
where parent='{0}'""".format(self.sales_order), as_dict=1);
for idx in range(0, len(order_items)):
self.box = order_items[idx].item_code
self.mfg_qty = self.order_qty = order_items[idx].qty
self.stock_qty = get_latest_stock_qty(self.box)
if (self.stock_qty is None or self.stock_qty == 0): break
self.populate_item_prod_info()
def get_last_selling_price_of_customer(item_code='', customer=''):
last_customer_price = frappe.db.sql(
"""select rate,posting_date from `tabSales Invoice Item` sid inner join `tabSales Invoice` si on sid.parent= si.name where si.customer = '{}' and sid.item_code = '{}' and si.docstatus != 2 order by si.posting_date DESC"""
.format(customer, item_code))
if last_customer_price:
last_customer_price = last_customer_price[0][0]
else:
last_customer_price = "Fist Sale"
current_stock = get_latest_stock_qty(item_code)
return {'item_price': last_customer_price, 'current_stock': current_stock}
def get_last_selling_prices_of_customer_delivery(item_code='', customer=''):
last_customer_prices = frappe.db.sql(
"""select rate,posting_date from `tabDelivery Note Item` sid inner join `tabDelivery Note` si on sid.parent= si.name where si.customer = '{}' and sid.item_code = '{}' and si.docstatus != 2 order by si.posting_date DESC limit 5"""
.format(customer, item_code))
if last_customer_prices:
last_customer_price = last_customer_prices
else:
last_customer_price = [0, '']
current_stock = get_latest_stock_qty(item_code)
return {'item_price': last_customer_price, 'current_stock': current_stock}
def set_available_qty(self):
quantity_list = []
for d in self.get("required_items"):
if d.source_warehouse:
d.available_qty_at_source_warehouse = get_latest_stock_qty(
d.item_code, d.source_warehouse)
if self.skip_transfer:
target_warehouse = self.fg_warehouse
else:
target_warehouse = self.wip_warehouse
if self.wip_warehouse:
d.available_qty_at_wip_warehouse = get_latest_stock_qty(
d.item_code, target_warehouse)
quantity_list.append(
flt(d.available_qty_at_source_warehouse) /
flt(d.required_qty / self.qty))
max_qty = min(quantity_list
or [0]) if min(quantity_list or [0]) >= 0 else 0
import math
self.max_qty = math.floor(max_qty)
def get_drug_shortage_map(medication_orders, warehouse):
"""
Returns a dict like { drug_code: shortage_qty }
"""
drug_requirement = dict()
for d in medication_orders:
if not drug_requirement.get(d.drug_code):
drug_requirement[d.drug_code] = 0
drug_requirement[d.drug_code] += flt(d.dosage)
drug_shortage = dict()
for drug, required_qty in drug_requirement.items():
available_qty = get_latest_stock_qty(drug, warehouse)
if flt(required_qty) > flt(available_qty):
drug_shortage[drug] = flt(flt(required_qty) - flt(available_qty))
return drug_shortage
def get_filtered_boards(txt, filters):
box_desc = frappe.get_doc("CM Box Description", filters["box_desc"])
layer_type = filters["layer_type"]
ignore_bom = filters["ignore_bom"]
deck_filter = ""
if (not ignore_bom):
deck_filter = "and item_code LIKE '{0}%%'".format(box_desc.get_board_prefix(layer_type))
filter_query = """select name from `tabItem`
where item_group='Board Layer'
{0}
and name LIKE %(txt)s
""".format(deck_filter)
#print "Searching boards matching {0} with query {1} and text {2}".format(box_desc.get_board_prefix(layer_type), filter_query, txt)
boards = frappe.db.sql(filter_query, {"txt": "%%%s%%" % txt})
filtered_boards = []
for (board, ) in boards:
qty = get_latest_stock_qty(board)
if (qty > 0):
filtered_boards.append((board, qty))
return filtered_boards
def on_submit(self):
items = []
user_remarks = "Special Closing Balance - {0}".format(self.name)
for item_row in self.closing_balance_details:
if item_row.item:
item_balance = get_latest_stock_qty(item_row.item,
self.warehouse) or 0
item_row.item_balance = item_balance
item_row.db_update()
if item_row.quantity != item_balance:
item_dict = dict(item_code=item_row.item,
qty=item_row.quantity - item_balance,
uom=item_row.uom,
s_warehouse=self.warehouse)
items.append(item_dict)
stock_entry_doc = frappe.get_doc(
dict(doctype="Stock Entry",
posting_date=self.posting_date,
posting_time=self.posting_time,
set_posting_time=1,
items=items,
stock_entry_type='Material Receipt',
purpose='Material Receipt',
to_warehouse=self.warehouse,
company=self.company,
remarks=user_remarks,
special_closing_balance=self.name)).insert(
ignore_permissions=True)
if stock_entry_doc:
frappe.flags.ignore_account_permission = True
stock_entry_doc.submit()
# return stock_entry_doc.name
self.stock_entry = stock_entry_doc.name
url = frappe.utils.get_url_to_form(stock_entry_doc.doctype,
stock_entry_doc.name)
frappe.msgprint("Stock Entry Created <a href='{0}'>{1}</a>".format(
url, stock_entry_doc.name))
def get_item_balance(item):
item_balance = get_latest_stock_qty(item)
return item_balance
def set_available_qty(self):
for d in self.get("items"):
if self.default_source_warehouse:
d.available_qty_at_source_warehouse = get_latest_stock_qty(d.item_code, self.default_source_warehouse)
def populate_item_prod_info(self):
if (not self.box): return
self.stock_qty = get_latest_stock_qty(self.box)
box_boms = frappe.get_all("CM Box Description", filters={'box': self.box})
self.box_desc = box_boms[0].name
def on_submit(self):
user_remarks = "Special Closing Balance - {0}".format(self.name)
csf_tz_settings = frappe.get_doc("CSF TZ Settings", "CSF TZ Settings")
if csf_tz_settings.is_manufacture:
for item_row in self.closing_balance_details:
if item_row.item :
item_bom_list = frappe.get_all("BOM", {"is_active": 1, "is_default": 1, "item": item_row.item, "docstatus": 1})
if len(item_bom_list) > 0:
item_bom = item_bom_list[0].name
frappe.msgprint(_("BOM no {0} for item {1} selected").format(item_bom, item_row.item), alert=True)
else:
frappe.throw(_("Default active BOM not found for item {0}").format(item_row.item))
item_balance = get_latest_stock_qty(item_row.item, self.warehouse) or 0
item_row.item_balance = item_balance
item_row.db_update()
if item_row.quantity != item_balance:
stock_entry_doc = frappe.get_doc(dict(
doctype="Stock Entry",
from_bom = 1,
bom_no = item_bom,
fg_completed_qty = item_row.quantity - item_balance,
posting_date=self.posting_date,
posting_time=self.posting_time,
set_posting_time = 1,
items = [],
stock_entry_type='Manufacture',
purpose='Manufacture',
to_warehouse=self.warehouse,
company=self.company,
remarks=user_remarks,
special_closing_balance=self.name
))
stock_entry_doc.get_items()
stock_entry_doc.insert(ignore_permissions=True)
if stock_entry_doc:
frappe.flags.ignore_account_permission = True
stock_entry_doc.submit()
# return stock_entry_doc.name
# self.stock_entry = stock_entry_doc.name
url = frappe.utils.get_url_to_form(stock_entry_doc.doctype, stock_entry_doc.name)
frappe.msgprint("Stock Entry Created <a href='{0}'>{1}</a>".format(url,stock_entry_doc.name))
else:
items = []
for item_row in self.closing_balance_details:
if item_row.item :
item_balance = get_latest_stock_qty(item_row.item, self.warehouse) or 0
item_row.item_balance = item_balance
item_row.db_update()
if item_row.quantity != item_balance:
item_dict = dict(
item_code=item_row.item,
qty=item_row.quantity - item_balance,
uom=item_row.uom,
s_warehouse=self.warehouse
)
items.append(item_dict)
if len(items) < 1:
return
stock_entry_doc = frappe.get_doc(dict(
doctype="Stock Entry",
posting_date=self.posting_date,
posting_time=self.posting_time,
set_posting_time = 1,
items=items,
stock_entry_type='Material Receipt',
purpose='Material Receipt',
to_warehouse=self.warehouse,
company=self.company,
remarks=user_remarks,
special_closing_balance=self.name
)).insert(ignore_permissions=True)
if stock_entry_doc:
frappe.flags.ignore_account_permission = True
stock_entry_doc.submit()
# return stock_entry_doc.name
self.stock_entry = stock_entry_doc.name
url = frappe.utils.get_url_to_form(stock_entry_doc.doctype, stock_entry_doc.name)
frappe.msgprint("Stock Entry Created <a href='{0}'>{1}</a>".format(url,stock_entry_doc.name))
def update_board_qty(self): for board in self.paper_boards: if (board.layer is None): continue board.stock_qty = get_latest_stock_qty(board.layer)
