<reponame>hamzaali15/mjt
# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt

from __future__ import unicode_literals

import frappe
from frappe import _
from frappe.utils import cint, flt, getdate


def execute(filters=None):
	if not filters: filters = {}

	if filters.from_date > filters.to_date:
		frappe.throw(_("From Date must be before To Date"))

	float_precision = cint(frappe.db.get_default("float_precision")) or 3

	columns = get_columns(filters)
	item_map = get_item_details(filters)
	iwb_map = get_item_warehouse_batch_map(filters, float_precision)
	data = []
	for item in sorted(iwb_map):
		if item and (not filters.get("item") or filters.get("item") == item):
			for wh in sorted(iwb_map[item]):
				for design in sorted(iwb_map[item][wh]):
					qty_dict = iwb_map[item][wh][design]
					print()
					design_name = frappe.db.get_value('Design' , design , 'design')
					if qty_dict.opening_qty or qty_dict.in_qty or qty_dict.out_qty or qty_dict.bal_qty:
						data.append([item, item_map[item]["item_name"], item_map[item]["description"], wh, design, design_name,
							flt(qty_dict.opening_qty, float_precision), flt(qty_dict.in_qty, float_precision),
							flt(qty_dict.out_qty, float_precision), flt(qty_dict.bal_qty, float_precision),
							item_map[item]["stock_uom"]
						])
	lst = []
	# for res in data:
	# 	print("-----------------res",res)
	# 	try:
	# 		l = res
	# 		doc = frappe.get_doc("Batch",res[4])
	# 		if doc:
	# 			l.append(doc.supplier if doc.supplier else " ")
	# 			if doc.supplier:
	# 				sn = frappe.db.get_value("Supplier", doc.supplier, "supplier_name")
	# 				l.append(sn if sn else " ")
	# 			else:
	# 				l.append(" ")
	# 			l.append(doc.quality_code if doc.quality_code else " ")
	# 			l.append(doc.quality_name if doc.quality_name else " ")
	# 		lst.append(l)
	# 	except:
	# 		pass
	return columns, data

def get_columns(filters):
	"""return columns based on filters"""

	columns = [_("Item") + ":Link/Item:100"] + [_("Item Name") + "::150"] + [_("Description") + "::150"] + \
		[_("Warehouse") + ":Link/Warehouse:100"] + [_("Design") + ":Link/Design:100"] + [_("Design Name") + "::150"] + [_("Opening Qty") + ":Float:90"] + \
		[_("In Qty") + ":Float:80"] + [_("Out Qty") + ":Float:80"] + [_("Balance Qty") + ":Float:90"] + \
		[_("UOM") + "::90"]
		# + [_("Party Code") + ":Data:100"] + [_("Party Name") + ":Data:100"] + \
		# [_("Quality Code") + ":Data:100"] + [_("Quality Name") + ":Data:100"]

	return columns

def get_conditions(filters):
	conditions = ""
	if not filters.get("from_date"):
		frappe.throw(_("'From Date' is required"))

	if filters.get("to_date"):
		conditions += " and posting_date <= '%s'" % filters["to_date"]
	else:
		frappe.throw(_("'To Date' is required"))

	for field in ["item_code", "warehouse", "design_no", "company"]:
		if filters.get(field):
			conditions += " and {0} = {1}".format(field, frappe.db.escape(filters.get(field)))

	return conditions

# get all details
def get_stock_ledger_entries(filters):
	conditions = get_conditions(filters)
	result = frappe.db.sql("""
		select item_code, design_no, design_name, warehouse, posting_date, sum(actual_qty) as actual_qty
		from `tabStock Ledger Entry`
		where docstatus < 2 and design_no is not null %s
		group by voucher_no, design_no, item_code, warehouse
		order by item_code, warehouse""" %
		conditions, as_dict=1)
	return result

def get_item_warehouse_batch_map(filters, float_precision):
	sle = get_stock_ledger_entries(filters)
	iwb_map = {}

	from_date = getdate(filters["from_date"])
	to_date = getdate(filters["to_date"])

	for d in sle:
		iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, {})\
			.setdefault(d.design_no, frappe._dict({
				"opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
			}))
		qty_dict = iwb_map[d.item_code][d.warehouse][d.design_no]
		if d.posting_date < from_date:
			qty_dict.opening_qty = flt(qty_dict.opening_qty, float_precision) \
				+ flt(d.actual_qty, float_precision)
		elif d.posting_date >= from_date and d.posting_date <= to_date:
			if flt(d.actual_qty) > 0:
				qty_dict.in_qty = flt(qty_dict.in_qty, float_precision) + flt(d.actual_qty, float_precision)
			else:
				qty_dict.out_qty = flt(qty_dict.out_qty, float_precision) \
					+ abs(flt(d.actual_qty, float_precision))

		qty_dict.bal_qty = flt(qty_dict.bal_qty, float_precision) + flt(d.actual_qty, float_precision)

	return iwb_map


def get_item_details(filters):
	item_map = {}
	for d in frappe.db.sql("select name, item_name, description, stock_uom from tabItem", as_dict=1):
		item_map.setdefault(d.name, d)

	return item_map

# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt

from __future__ import unicode_literals

import frappe
from frappe import _
from frappe.utils import cint, flt, getdate


def execute(filters=None):
	if not filters: filters = {}

	if filters.from_date > filters.to_date:
		frappe.throw(_("From Date must be before To Date"))

	float_precision = cint(frappe.db.get_default("float_precision")) or 3

	columns = get_columns(filters)
	item_map = get_item_details(filters)
	iwb_map = get_item_warehouse_batch_map(filters, float_precision)
	data = []
	for item in sorted(iwb_map):
		if item and (not filters.get("item") or filters.get("item") == item):
			for wh in sorted(iwb_map[item]):
				for design in sorted(iwb_map[item][wh]):
					qty_dict = iwb_map[item][wh][design]
					print()
					design_name = frappe.db.get_value('Design' , design , 'design')
					if qty_dict.opening_qty or qty_dict.in_qty or qty_dict.out_qty or qty_dict.bal_qty:
						data.append([item, item_map[item]["item_name"], item_map[item]["description"], wh, design, design_name,
							flt(qty_dict.opening_qty, float_precision), flt(qty_dict.in_qty, float_precision),
							flt(qty_dict.out_qty, float_precision), flt(qty_dict.bal_qty, float_precision),
							item_map[item]["stock_uom"]
						])
	lst = []
	# for res in data:
	# 	print("-----------------res",res)
	# 	try:
	# 		l = res
	# 		doc = frappe.get_doc("Batch",res[4])
	# 		if doc:
	# 			l.append(doc.supplier if doc.supplier else " ")
	# 			if doc.supplier:
	# 				sn = frappe.db.get_value("Supplier", doc.supplier, "supplier_name")
	# 				l.append(sn if sn else " ")
	# 			else:
	# 				l.append(" ")
	# 			l.append(doc.quality_code if doc.quality_code else " ")
	# 			l.append(doc.quality_name if doc.quality_name else " ")
	# 		lst.append(l)
	# 	except:
	# 		pass
	return columns, data

def get_columns(filters):
	"""return columns based on filters"""

	columns = [_("Item") + ":Link/Item:100"] + [_("Item Name") + "::150"] + [_("Description") + "::150"] + \
		[_("Warehouse") + ":Link/Warehouse:100"] + [_("Design") + ":Link/Design:100"] + [_("Design Name") + "::150"] + [_("Opening Qty") + ":Float:90"] + \
		[_("In Qty") + ":Float:80"] + [_("Out Qty") + ":Float:80"] + [_("Balance Qty") + ":Float:90"] + \
		[_("UOM") + "::90"]
		# + [_("Party Code") + ":Data:100"] + [_("Party Name") + ":Data:100"] + \
		# [_("Quality Code") + ":Data:100"] + [_("Quality Name") + ":Data:100"]

	return columns

def get_conditions(filters):
	conditions = ""
	if not filters.get("from_date"):
		frappe.throw(_("'From Date' is required"))

	if filters.get("to_date"):
		conditions += " and posting_date <= '%s'" % filters["to_date"]
	else:
		frappe.throw(_("'To Date' is required"))

	for field in ["item_code", "warehouse", "design_no", "company"]:
		if filters.get(field):
			conditions += " and {0} = {1}".format(field, frappe.db.escape(filters.get(field)))

	return conditions

# get all details
def get_stock_ledger_entries(filters):
	conditions = get_conditions(filters)
	result = frappe.db.sql("""
		select item_code, design_no, design_name, warehouse, posting_date, sum(actual_qty) as actual_qty
		from `tabStock Ledger Entry`
		where docstatus < 2 and design_no is not null %s
		group by voucher_no, design_no, item_code, warehouse
		order by item_code, warehouse""" %
		conditions, as_dict=1)
	return result

def get_item_warehouse_batch_map(filters, float_precision):
	sle = get_stock_ledger_entries(filters)
	iwb_map = {}

	from_date = getdate(filters["from_date"])
	to_date = getdate(filters["to_date"])

	for d in sle:
		iwb_map.setdefault(d.item_code, {}).setdefault(d.warehouse, {})\
			.setdefault(d.design_no, frappe._dict({
				"opening_qty": 0.0, "in_qty": 0.0, "out_qty": 0.0, "bal_qty": 0.0
			}))
		qty_dict = iwb_map[d.item_code][d.warehouse][d.design_no]
		if d.posting_date < from_date:
			qty_dict.opening_qty = flt(qty_dict.opening_qty, float_precision) \
				+ flt(d.actual_qty, float_precision)
		elif d.posting_date >= from_date and d.posting_date <= to_date:
			if flt(d.actual_qty) > 0:
				qty_dict.in_qty = flt(qty_dict.in_qty, float_precision) + flt(d.actual_qty, float_precision)
			else:
				qty_dict.out_qty = flt(qty_dict.out_qty, float_precision) \
					+ abs(flt(d.actual_qty, float_precision))

		qty_dict.bal_qty = flt(qty_dict.bal_qty, float_precision) + flt(d.actual_qty, float_precision)

	return iwb_map


def get_item_details(filters):
	item_map = {}
	for d in frappe.db.sql("select name, item_name, description, stock_uom from tabItem", as_dict=1):
		item_map.setdefault(d.name, d)

	return item_map
# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors # License: GNU General Public License v3. See license.txt # for res in data: # 	print("-----------------res",res) # 	try: # 		l = res # 		doc = frappe.get_doc("Batch",res[4]) # 		if doc: # 			l.append(doc.supplier if doc.supplier else " ") # 			if doc.supplier: # 				sn = frappe.db.get_value("Supplier", doc.supplier, "supplier_name") # 				l.append(sn if sn else " ") # 			else: # 				l.append(" ") # 			l.append(doc.quality_code if doc.quality_code else " ") # 			l.append(doc.quality_name if doc.quality_name else " ") # 		lst.append(l) # 	except: # 		pass return columns based on filters # + [_("Party Code") + ":Data:100"] + [_("Party Name") + ":Data:100"] + \ # [_("Quality Code") + ":Data:100"] + [_("Quality Name") + ":Data:100"] # get all details select item_code, design_no, design_name, warehouse, posting_date, sum(actual_qty) as actual_qty 		from `tabStock Ledger Entry` 		where docstatus < 2 and design_no is not null %s 		group by voucher_no, design_no, item_code, warehouse 		order by item_code, warehouse