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]