def update_raw_materials(self, data, key):
self.index = 0
self.raw_materials_dict.get(key)
warehouses = self.mrp_warehouses or []
for d in self.raw_materials_dict.get(key):
if self.filters.based_on != "Work Order":
d.required_qty = d.required_qty_per_unit * data.qty_to_manufacture
if not warehouses:
warehouses = [data.warehouse]
if self.filters.based_on == "Work Order" and d.warehouse:
warehouses = [d.warehouse]
else:
item_details = self.item_details.get(d.item_code)
if item_details:
warehouses = [item_details["default_warehouse"]]
if self.filters.raw_material_warehouse:
warehouses = get_child_warehouses(
self.filters.raw_material_warehouse)
d.remaining_qty = d.required_qty
self.pick_materials_from_warehouses(d, data, warehouses)
if (d.remaining_qty and self.filters.raw_material_warehouse
and d.remaining_qty != d.required_qty):
row = self.get_args()
d.warehouse = self.filters.raw_material_warehouse
d.required_qty = d.remaining_qty
d.allotted_qty = 0
row.update(d)
self.data.append(row)
def get_bin_details(self):
if not (self.orders and self.raw_materials_dict):
return
self.bin_details = {}
self.mrp_warehouses = []
if self.filters.raw_material_warehouse:
self.mrp_warehouses.extend(
get_child_warehouses(self.filters.raw_material_warehouse))
self.warehouses.extend(self.mrp_warehouses)
for d in frappe.get_all(
"Bin",
fields=[
"warehouse", "item_code", "actual_qty", "ordered_qty",
"projected_qty"
],
filters={
"item_code": ("in", self.item_codes),
"warehouse": ("in", self.warehouses)
},
):
key = (d.item_code, d.warehouse)
if key not in self.bin_details:
self.bin_details.setdefault(key, d)
def get_data_for_forecast(self):
cond = ""
if self.filters.item_code:
cond = " AND soi.item_code = %s" % (frappe.db.escape(
self.filters.item_code))
warehouses = []
if self.filters.warehouse:
warehouses = get_child_warehouses(self.filters.warehouse)
cond += " AND soi.warehouse in ({})".format(','.join(
['%s'] * len(warehouses)))
input_data = [self.filters.from_date, self.filters.company]
if warehouses:
input_data.extend(warehouses)
date_field = "posting_date" if self.doctype == "Delivery Note" else "transaction_date"
return frappe.db.sql("""
SELECT
so.{date_field} as posting_date, soi.item_code, soi.warehouse,
soi.item_name, soi.stock_qty as qty, soi.base_amount as amount
FROM
`tab{doc}` so, `tab{child_doc}` soi
WHERE
so.docstatus = 1 AND so.name = soi.parent AND
so.{date_field} < %s AND so.company = %s {cond}
""".format(doc=self.doctype,
child_doc=self.child_doctype,
date_field=date_field,
cond=cond),
tuple(input_data),
as_dict=1)
def get_qty_amount_data_for_cumulative(pr_doc, doc, items=None):
if items is None:
items = []
sum_qty, sum_amt = [0, 0]
doctype = doc.get("parenttype") or doc.doctype
date_field = ("transaction_date"
if frappe.get_meta(doctype).has_field("transaction_date")
else "posting_date")
child_doctype = "{0} Item".format(doctype)
apply_on = frappe.scrub(pr_doc.get("apply_on"))
values = [pr_doc.valid_from, pr_doc.valid_upto]
condition = ""
if pr_doc.warehouse:
warehouses = get_child_warehouses(pr_doc.warehouse)
condition += """ and `tab{child_doc}`.warehouse in ({warehouses})
""".format(child_doc=child_doctype,
warehouses=",".join(["%s"] * len(warehouses)))
values.extend(warehouses)
if items:
condition = " and `tab{child_doc}`.{apply_on} in ({items})".format(
child_doc=child_doctype,
apply_on=apply_on,
items=",".join(["%s"] * len(items)))
values.extend(items)
data_set = frappe.db.sql(
""" SELECT `tab{child_doc}`.stock_qty,
`tab{child_doc}`.amount
FROM `tab{child_doc}`, `tab{parent_doc}`
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name and `tab{parent_doc}`.{date_field}
between %s and %s and `tab{parent_doc}`.docstatus = 1
{condition} group by `tab{child_doc}`.name
""".format(parent_doc=doctype,
child_doc=child_doctype,
condition=condition,
date_field=date_field),
tuple(values),
as_dict=1,
)
for data in data_set:
sum_qty += data.get("stock_qty")
sum_amt += data.get("amount")
return [sum_qty, sum_amt]
def get_qty_amount_data_for_cumulative(pr_doc, doc, items=[]):
sum_qty, sum_amt = [0, 0]
doctype = doc.get('parenttype') or doc.doctype
date_field = ('transaction_date'
if doc.get('transaction_date') else 'posting_date')
child_doctype = '{0} Item'.format(doctype)
apply_on = frappe.scrub(pr_doc.get('apply_on'))
values = [pr_doc.valid_from, pr_doc.valid_upto]
condition = ""
if pr_doc.warehouse:
warehouses = get_child_warehouses(pr_doc.warehouse)
condition += """ and `tab{child_doc}`.warehouse in ({warehouses})
""".format(child_doc=child_doctype,
warehouses=','.join(['%s'] * len(warehouses)))
values.extend(warehouses)
if items:
condition = " and `tab{child_doc}`.{apply_on} in ({items})".format(
child_doc=child_doctype,
apply_on=apply_on,
items=','.join(['%s'] * len(items)))
values.extend(items)
data_set = frappe.db.sql(""" SELECT `tab{child_doc}`.stock_qty,
`tab{child_doc}`.amount
FROM `tab{child_doc}`, `tab{parent_doc}`
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name and `tab{parent_doc}`.{date_field}
between %s and %s and `tab{parent_doc}`.docstatus = 1
{condition} group by `tab{child_doc}`.name
""".format(parent_doc=doctype,
child_doc=child_doctype,
condition=condition,
date_field=date_field),
tuple(values),
as_dict=1)
for data in data_set:
sum_qty += data.get('stock_qty')
sum_amt += data.get('amount')
return [sum_qty, sum_amt]
def get_qty_amount_data_for_cumulative(pr_doc, doc, items=[]):
sum_qty, sum_amt = [0, 0]
doctype = doc.get('parenttype') or doc.get('doctype') or "Sales Invoice"
date_field = date_field_map.get(doctype)
child_doctype = '{0} Item'.format(doctype)
apply_on = frappe.scrub(pr_doc.get('apply_on'))
values = [pr_doc.valid_from, pr_doc.valid_upto]
parent_condition = ""
if doc.get("name"):
parent_condition = " and `tab{parent_doc}`.name != '{parent_name}'".format(
parent_doc=doctype, parent_name=doc.get("name"))
party_condition = ""
if doc.get("customer") or doc.get("supplier"):
party_condition = " and `tab{parent_doc}`.{party_type} = '{party}'".format(
parent_doc=doctype,
party_type="customer" if doc.get("customer") else "supplier",
party=doc.get("customer") or doc.get("supplier"))
condition = ""
if pr_doc.warehouse:
warehouses = get_child_warehouses(pr_doc.warehouse)
condition += """ and `tab{child_doc}`.warehouse in ({warehouses})
""".format(child_doc=child_doctype,
warehouses=','.join(['%s'] * len(warehouses)))
values.extend(warehouses)
if items:
condition = " and `tab{child_doc}`.{apply_on} in ({items})".format(
child_doc=child_doctype,
apply_on=apply_on,
items=','.join(['%s'] * len(items)))
values.extend(items)
data_set = frappe.db.sql(""" SELECT `tab{child_doc}`.stock_qty,
`tab{child_doc}`.amount
FROM `tab{child_doc}`, `tab{parent_doc}`
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name
{parent_condition}
and `tab{parent_doc}`.{date_field}
between %s and %s and `tab{parent_doc}`.docstatus = 1
{party_condition} {condition} group by `tab{child_doc}`.name
""".format(parent_doc=doctype,
child_doc=child_doctype,
parent_condition=parent_condition,
party_condition=party_condition,
condition=condition,
date_field=date_field),
tuple(values),
as_dict=1)
for data in data_set:
sum_qty += data.get('stock_qty')
sum_amt += data.get('amount')
return [sum_qty, sum_amt]
