[Reprots][No Sales Order From Customer] and [Item Reorder Level]
diff --git a/selling/page/selling_home/selling_home.js b/selling/page/selling_home/selling_home.js
index 1f119e2..603bd3a 100644
--- a/selling/page/selling_home/selling_home.js
+++ b/selling/page/selling_home/selling_home.js
@@ -150,11 +150,6 @@
icon: "icon-list",
items: [
{
- "label":wn._("No Sales Order from Customers (Since 2 months)"),
- route: "query-report/No Sales Order from Customers (Since 2 months)",
- doctype: "Sales Order"
- },
- {
"label":wn._("Customer Addresses And Contacts"),
route: "query-report/Customer Addresses And Contacts"
},
@@ -170,6 +165,11 @@
"label":wn._("Item-wise Sales History"),
route: "query-report/Item-wise Sales History",
},
+ {
+ "label":wn._("No Sales Order from Customers (Since 2 months)"),
+ route: "query-report/No Sales Order from Customers",
+ doctype: "Sales Order"
+ },
]
}
diff --git "a/selling/report/no_sales_order_from_customers_\050since_2_months\051/__init__.py" b/selling/report/no_sales_order_from_customers/__init__.py
similarity index 100%
rename from "selling/report/no_sales_order_from_customers_\050since_2_months\051/__init__.py"
rename to selling/report/no_sales_order_from_customers/__init__.py
diff --git "a/selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.py" b/selling/report/no_sales_order_from_customers/no_sales_order_from_customers.py
similarity index 83%
rename from "selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.py"
rename to selling/report/no_sales_order_from_customers/no_sales_order_from_customers.py
index 43efb98..789e168 100644
--- "a/selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.py"
+++ b/selling/report/no_sales_order_from_customers/no_sales_order_from_customers.py
@@ -16,7 +16,6 @@
import webnotes
def execute(filters=None):
-
columns = get_columns()
customers = get_so_details()
@@ -25,7 +24,6 @@
if cust[8] >= 60: # days_since_last_order
cust.insert(7,get_last_so_amt(cust[0]))
data.append(cust)
-
return columns, data
def get_so_details():
@@ -40,19 +38,18 @@
so.net_total * so.per_delivered/100,
so.net_total)) as 'total_order_considered',
max(so.transaction_date) as 'last_sales_order_date',
- DATEDIFF(CURDATE(),max(so.transaction_date)) as 'days_since_last_order'
+ DATEDIFF(CURDATE(), max(so.transaction_date)) as 'days_since_last_order'
from `tabCustomer` cust, `tabSales Order` so
where cust.name = so.customer and so.docstatus = 1
group by cust.name
order by 'days_since_last_order' desc """,as_list=1)
def get_last_so_amt(customer):
- return webnotes.conn.sql("""select net_total from `tabSales Order`
- where customer ='%(customer)s' and docstatus = 1 and
- transaction_date = (select max(transaction_date)
- from `tabSales Order`
- where customer = '%(customer)s')
- """%{'customer':customer})
+ res = webnotes.conn.sql("""select net_total from `tabSales Order`
+ where customer ='%(customer)s' and docstatus = 1 order by transaction_date desc
+ limit 1""" % {'customer':customer})
+
+ return res and res[0][0] or 0
def get_columns():
return [
diff --git a/selling/report/no_sales_order_from_customers/no_sales_order_from_customers.txt b/selling/report/no_sales_order_from_customers/no_sales_order_from_customers.txt
new file mode 100644
index 0000000..32b38ae
--- /dev/null
+++ b/selling/report/no_sales_order_from_customers/no_sales_order_from_customers.txt
@@ -0,0 +1,21 @@
+[
+ {
+ "creation": "2013-06-06 19:15:50",
+ "docstatus": 0,
+ "modified": "2013-06-06 19:15:51",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Sales Order",
+ "report_name": "No Sales Order from Customers",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "No Sales Order from Customers"
+ }
+]
\ No newline at end of file
diff --git "a/selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.txt" "b/selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.txt"
deleted file mode 100644
index cd7b2b7..0000000
--- "a/selling/report/no_sales_order_from_customers_\050since_2_months\051/no_sales_order_from_customers_\050since_2_months\051.txt"
+++ /dev/null
@@ -1,21 +0,0 @@
-[
- {
- "creation": "2013-06-05 11:40:49",
- "docstatus": 0,
- "modified": "2013-06-05 11:40:49",
- "modified_by": "Administrator",
- "owner": "Administrator"
- },
- {
- "doctype": "Report",
- "is_standard": "Yes",
- "name": "__common__",
- "ref_doctype": "Sales Order",
- "report_name": "No Sales Order from Customers (Since 2 months)",
- "report_type": "Script Report"
- },
- {
- "doctype": "Report",
- "name": "No Sales Order from Customers (Since 2 months)"
- }
-]
\ No newline at end of file
diff --git a/stock/page/stock_home/stock_home.js b/stock/page/stock_home/stock_home.js
index ac3a096..ab3d325 100644
--- a/stock/page/stock_home/stock_home.js
+++ b/stock/page/stock_home/stock_home.js
@@ -129,11 +129,6 @@
icon: "icon-table",
items: [
{
- "label":wn._("Item Reorder Level"),
- route: "query-report/Item Reorder Level",
- doctype: "Item"
- },
- {
"label":wn._("Stock Ledger"),
page: "stock-ledger"
},
@@ -210,6 +205,11 @@
"label":wn._("Requested Items To Be Transferred"),
route: "query-report/Requested Items To Be Transferred",
},
+ {
+ "label":wn._("Item Reorder Level"),
+ route: "query-report/Item Reorder Level",
+ doctype: "Item"
+ },
]
}
]
diff --git a/stock/report/item_reorder_level/item_reorder_level.py b/stock/report/item_reorder_level/item_reorder_level.py
index d596961..10ee182 100644
--- a/stock/report/item_reorder_level/item_reorder_level.py
+++ b/stock/report/item_reorder_level/item_reorder_level.py
@@ -15,27 +15,30 @@
# along with this program. If not, see <http://www.gnu.org/licenses/>.
import webnotes
-from webnotes.utils import getdate, flt, cint
+from webnotes.utils import getdate, flt
def execute(filters=None):
if not filters: filters = {}
+ float_preceision = webnotes.conn.get_default("float_preceision")
+
+ condition =get_condition(filters)
+
+ avg_daily_outgoing = 0
+ diff = ((getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days)+1
+ if diff <= 0:
+ webnotes.msgprint("To Date should not be less than eual to From Date",raise_exception=1)
columns = get_columns()
items = get_item_info()
- consumed_item_map = get_consumed_items(filters)
- delivered_item_map = get_delivered_items(filters)
-
- avg_daily_outgoing = 0
- diff = (getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days
- if diff <= 0:
- webnotes.msgprint("To Date should not be less than eual to From Date",raise_exception=1)
+ consumed_item_map = get_consumed_items(condition)
+ delivered_item_map = get_delivered_items(condition)
data = []
for item in items:
total_outgoing = consumed_item_map.get(item.name, 0)+delivered_item_map.get(item.name,0)
- avg_daily_outgoing = cint(total_outgoing/diff)
- reorder_level = (avg_daily_outgoing * item.lead_time_days) + item.min_order_qty
+ avg_daily_outgoing = flt(total_outgoing/diff, float_preceision)
+ reorder_level = (avg_daily_outgoing * flt(item.lead_time_days)) + flt(item.min_order_qty)
data.append([item.name, item.item_name, item.description, item.min_order_qty, item.lead_time_days,
consumed_item_map.get(item.name, 0), delivered_item_map.get(item.name,0), total_outgoing,
@@ -44,20 +47,24 @@
return columns , data
def get_columns():
- return["Item:Link/Item:120", "Item name:Data:120", "description::160", "minimum inventory level::120",
- "lead time days::120", "consumed::120", "delivered::120", "total outgoing::120",
- "avg daily outgoing::120", "reorder level::120"]
+ return[
+ "Item:Link/Item:120", "Item name:Data:120", "Description::160",
+ "Minimum Inventory Level:Float:160", "Lead Time Days:Float:120", "Consumed:Float:120",
+ "Delivered:Float:120", "Total Outgoing:Float:120", "Avg Daily Outgoing:Float:160",
+ "Reorder Level:Float:120"
+ ]
def get_item_info():
- return webnotes.conn.sql("""select name, item_name, description, min_order_qty, lead_time_days
- from tabItem""",as_dict=1)
+ return webnotes.conn.sql("""select name, item_name, description, min_order_qty,
+ lead_time_days from tabItem""", as_dict=1)
-def get_consumed_items(filters):
- condition = get_condition(filters)
+def get_consumed_items(condition):
- cn_items = webnotes.conn.sql("""select se_item.item_code, sum(se_item.actual_qty) as 'consume_qty'
+ cn_items = webnotes.conn.sql("""select se_item.item_code,
+ sum(se_item.actual_qty) as 'consume_qty'
from `tabStock Entry` se, `tabStock Entry Detail` se_item
- where se.name = se_item.parent and se.docstatus = 1 and ifnull(se_item.t_warehouse, '') = '' %s
+ where se.name = se_item.parent and se.docstatus = 1
+ and ifnull(se_item.t_warehouse, '') = '' %s
group by se_item.item_code""" % (condition), as_dict=1)
cn_items_map = {}
@@ -66,18 +73,18 @@
return cn_items_map
-def get_delivered_items(filters):
- condition = get_condition(filters)
+def get_delivered_items(condition):
dn_items = webnotes.conn.sql("""select dn_item.item_code, sum(dn_item.qty) as dn_qty
from `tabDelivery Note` dn, `tabDelivery Note Item` dn_item
- where dn.name = dn_item.parent and dn.docstatus = 1 %s group by dn_item.item_code""" % (condition)
- , as_dict=1)
+ where dn.name = dn_item.parent and dn.docstatus = 1 %s
+ group by dn_item.item_code""" % (condition), as_dict=1)
si_items = webnotes.conn.sql("""select si_item.item_name, sum(si_item.qty) as si_qty
from `tabSales Invoice` si, `tabSales Invoice Item` si_item
- where si.name = si_item.parent and si.docstatus = 1 and ifnull(si.update_stock, 0) = 1
- and ifnull(si.is_pos, 0) = 1 %s group by si_item.item_name""" % (condition), as_dict=1)
+ where si.name = si_item.parent and si.docstatus = 1 and
+ ifnull(si.update_stock, 0) = 1 and ifnull(si.is_pos, 0) = 1 %s
+ group by si_item.item_name""" % (condition), as_dict=1)
dn_item_map = {}
for item in dn_items: