Merge pull request #3465 from neilLasrado/po
Po
diff --git a/erpnext/__version__.py b/erpnext/__version__.py
index c067798..18ebfcc 100644
--- a/erpnext/__version__.py
+++ b/erpnext/__version__.py
@@ -1,2 +1,2 @@
from __future__ import unicode_literals
-__version__ = '5.0.22'
+__version__ = '5.0.23'
diff --git a/erpnext/accounts/doctype/account/account.json b/erpnext/accounts/doctype/account/account.json
index fa737a7..8146c63 100644
--- a/erpnext/accounts/doctype/account/account.json
+++ b/erpnext/accounts/doctype/account/account.json
@@ -35,7 +35,7 @@
"permlevel": 0,
"read_only": 1,
"reqd": 1,
- "search_index": 1
+ "search_index": 0
},
{
"default": "0",
@@ -44,7 +44,7 @@
"label": "Is Group",
"permlevel": 0,
"precision": "",
- "search_index": 1
+ "search_index": 0
},
{
"fieldname": "company",
@@ -57,7 +57,7 @@
"permlevel": 0,
"read_only": 1,
"reqd": 1,
- "search_index": 1
+ "search_index": 0
},
{
"fieldname": "root_type",
@@ -147,7 +147,8 @@
"label": "Lft",
"permlevel": 0,
"print_hide": 1,
- "read_only": 1
+ "read_only": 1,
+ "search_index": 1
},
{
"fieldname": "rgt",
@@ -156,7 +157,8 @@
"label": "Rgt",
"permlevel": 0,
"print_hide": 1,
- "read_only": 1
+ "read_only": 1,
+ "search_index": 1
},
{
"fieldname": "old_parent",
@@ -171,7 +173,7 @@
"icon": "icon-money",
"idx": 1,
"in_create": 0,
- "modified": "2015-05-28 14:10:40.606010",
+ "modified": "2015-06-14 20:57:55.471334",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Account",
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.json b/erpnext/accounts/doctype/gl_entry/gl_entry.json
index 54c7dd6..6d772a6 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.json
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.json
@@ -48,7 +48,8 @@
"fieldtype": "Dynamic Link",
"label": "Party",
"options": "party_type",
- "permlevel": 0
+ "permlevel": 0,
+ "search_index": 1
},
{
"fieldname": "cost_center",
@@ -192,7 +193,7 @@
"icon": "icon-list",
"idx": 1,
"in_create": 1,
- "modified": "2015-04-27 20:32:48.246818",
+ "modified": "2015-06-14 20:57:19.800276",
"modified_by": "Administrator",
"module": "Accounts",
"name": "GL Entry",
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index 831fcd7..753fcc3 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -544,7 +544,7 @@
and (ifnull(jv_detail.against_invoice, '') = '' and ifnull(jv_detail.against_voucher, '') = ''
and ifnull(jv_detail.against_jv, '') = '' )
and jv.docstatus = 1 and jv.{0} like %s order by jv.name desc limit %s, %s""".format(searchfield),
- (filters["account"], cstr(filters["party"]), "%{0}%".format(txt), start, page_len))
+ (filters.get("account"), cstr(filters.get("party")), "%{0}%".format(txt), start, page_len))
@frappe.whitelist()
def get_outstanding(args):
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 52c9760..bdfdf0e 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -51,7 +51,7 @@
currency_precision = get_currency_precision() or 2
dr_or_cr = "debit" if args.get("party_type") == "Customer" else "credit"
- voucher_details = self.get_voucher_details()
+ voucher_details = self.get_voucher_details(args.get("party_type"))
future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
@@ -153,23 +153,26 @@
return self.party_map
- def get_voucher_details(self):
+ def get_voucher_details(self, party_type):
voucher_details = frappe._dict()
+
+ if party_type == "Customer":
+ for si in frappe.db.sql("""select name, due_date
+ from `tabSales Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(si.name, si)
- for si in frappe.db.sql("""select name, due_date
- from `tabSales Invoice` where docstatus=1""", as_dict=1):
- voucher_details.setdefault(si.name, si)
-
- for pi in frappe.db.sql("""select name, due_date, bill_no, bill_date
- from `tabPurchase Invoice` where docstatus=1""", as_dict=1):
- voucher_details.setdefault(pi.name, pi)
+ if party_type == "Supplier":
+ for pi in frappe.db.sql("""select name, due_date, bill_no, bill_date
+ from `tabPurchase Invoice` where docstatus=1""", as_dict=1):
+ voucher_details.setdefault(pi.name, pi)
return voucher_details
def get_gl_entries(self, party_type):
if not hasattr(self, "gl_entries"):
conditions, values = self.prepare_conditions(party_type)
- self.gl_entries = frappe.db.sql("""select * from `tabGL Entry`
+ self.gl_entries = frappe.db.sql("""select posting_date, account, party_type, party, debit, credit,
+ voucher_type, voucher_no, against_voucher_type, against_voucher from `tabGL Entry`
where docstatus < 2 and party_type=%s {0} order by posting_date, party"""
.format(conditions), values, as_dict=True)
@@ -187,7 +190,7 @@
if self.filters.get(party_type_field):
conditions.append("party=%s")
- values.append(self.filters.get(party_type_field))
+ values.append(self.filters.get(party_type_field))
return " and ".join(conditions), values
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index c763778..b4fec98 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -83,7 +83,7 @@
gl_entries_by_account = get_gl_entries(company, period_list[0]["from_date"], period_list[-1]["to_date"],
accounts[0].lft, accounts[0].rgt, ignore_closing_entries=ignore_closing_entries)
- calculate_values(accounts, gl_entries_by_account, period_list)
+ calculate_values(accounts_by_name, gl_entries_by_account, period_list)
accumulate_values_into_parents(accounts, accounts_by_name, period_list)
out = prepare_data(accounts, balance_must_be, period_list)
@@ -92,16 +92,14 @@
return out
-def calculate_values(accounts, gl_entries_by_account, period_list):
- for d in accounts:
- for name in ([d.name] + (d.collapsed_children or [])):
- for entry in gl_entries_by_account.get(name, []):
- for period in period_list:
- entry.posting_date = getdate(entry.posting_date)
-
- # check if posting date is within the period
- if entry.posting_date <= period.to_date:
- d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
+def calculate_values(accounts_by_name, gl_entries_by_account, period_list):
+ for entries in gl_entries_by_account.values():
+ for entry in entries:
+ d = accounts_by_name.get(entry.account)
+ for period in period_list:
+ # check if posting date is within the period
+ if entry.posting_date <= period.to_date:
+ d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
def accumulate_values_into_parents(accounts, accounts_by_name, period_list):
@@ -159,22 +157,8 @@
out.append({})
def get_accounts(company, root_type):
- # root lft, rgt
- root_account = frappe.db.sql("""select lft, rgt from `tabAccount`
- where company=%s and root_type=%s and ifnull(parent_account, '') = ''
- order by lft limit 1""",
- (company, root_type), as_dict=True)
-
- if not root_account:
- return None
-
- lft, rgt = root_account[0].lft, root_account[0].rgt
-
- accounts = frappe.db.sql("""select * from `tabAccount`
- where company=%(company)s and lft >= %(lft)s and rgt <= %(rgt)s order by lft""",
- { "company": company, "lft": lft, "rgt": rgt }, as_dict=True)
-
- return accounts
+ return frappe.db.sql("""select name, parent_account, lft, rgt, root_type, report_type, account_name from `tabAccount`
+ where company=%s and root_type=%s order by lft""", (company, root_type), as_dict=True)
def filter_accounts(accounts, depth=10):
parent_children_map = {}
@@ -196,14 +180,6 @@
filtered_accounts.append(child)
add_to_list(child.name, level + 1)
- else:
- # include all children at level lower than the depth
- parent_account = accounts_by_name[parent]
- parent_account["collapsed_children"] = []
- for d in accounts:
- if d.lft > parent_account.lft and d.rgt < parent_account.rgt:
- parent_account["collapsed_children"].append(d.name)
-
add_to_list(None, 0)
return filtered_accounts, accounts_by_name
@@ -234,7 +210,7 @@
if from_date:
additional_conditions.append("and posting_date >= %(from_date)s")
- gl_entries = frappe.db.sql("""select * from `tabGL Entry`
+ gl_entries = frappe.db.sql("""select posting_date, account, debit, credit from `tabGL Entry`
where company=%(company)s
{additional_conditions}
and posting_date <= %(to_date)s
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index fcce345..b268156 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -66,7 +66,7 @@
gl_entries = frappe.db.sql("""select posting_date, account, party_type, party,
sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit,
- voucher_type, voucher_no, cost_center, remarks, is_opening, against
+ voucher_type, voucher_no, cost_center, remarks, against
from `tabGL Entry`
where company=%(company)s {conditions}
{group_by_condition}
@@ -91,6 +91,9 @@
if filters.get("party"):
conditions.append("party=%(party)s")
+
+ if not (filters.get("account") or filters.get("party") or filters.get("group_by_account")):
+ conditions.append("posting_date >=%(from_date)s")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
@@ -148,14 +151,15 @@
def get_accountwise_gle(filters, gl_entries, gle_map):
opening, total_debit, total_credit = 0, 0, 0
-
+ from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
for gle in gl_entries:
amount = flt(gle.debit, 3) - flt(gle.credit, 3)
- if gle.posting_date < getdate(filters.from_date):
+ if (filters.get("account") or filters.get("party") or filters.get("group_by_account")) \
+ and gle.posting_date < from_date:
gle_map[gle.account].opening += amount
if filters.get("account") or filters.get("party"):
opening += amount
- elif gle.posting_date <= getdate(filters.to_date):
+ elif gle.posting_date <= to_date:
gle_map[gle.account].entries.append(gle)
gle_map[gle.account].total_debit += flt(gle.debit, 3)
gle_map[gle.account].total_credit += flt(gle.credit, 3)
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 761d038..8187601 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -9,7 +9,7 @@
value_fields = ("opening_debit", "opening_credit", "debit", "credit", "closing_debit", "closing_credit")
-def execute(filters):
+def execute(filters=None):
validate_filters(filters)
data = get_data(filters)
columns = get_columns()
@@ -45,8 +45,8 @@
filters.to_date = filters.year_end_date
def get_data(filters):
- accounts = frappe.db.sql("""select * from `tabAccount` where company=%s order by lft""",
- filters.company, as_dict=True)
+ accounts = frappe.db.sql("""select name, parent_account, account_name, root_type, report_type, lft, rgt
+ from `tabAccount` where company=%s order by lft""", filters.company, as_dict=True)
if not accounts:
return None
@@ -56,17 +56,58 @@
min_lft, max_rgt = frappe.db.sql("""select min(lft), max(rgt) from `tabAccount`
where company=%s""", (filters.company,))[0]
- gl_entries_by_account = get_gl_entries(filters.company, None, filters.to_date, min_lft, max_rgt,
+ gl_entries_by_account = get_gl_entries(filters.company, filters.from_date, filters.to_date, min_lft, max_rgt,
ignore_closing_entries=not flt(filters.with_period_closing_entry))
- total_row = calculate_values(accounts, gl_entries_by_account, filters)
+ opening_balances = get_opening_balances(filters)
+
+ total_row = calculate_values(accounts, gl_entries_by_account, opening_balances, filters)
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row)
return data
+
+def get_opening_balances(filters):
+ balance_sheet_opening = get_rootwise_opening_balances(filters, "Balance Sheet")
+ pl_opening = get_rootwise_opening_balances(filters, "Profit and Loss")
+
+ balance_sheet_opening.update(pl_opening)
+ return balance_sheet_opening
+
+
+def get_rootwise_opening_balances(filters, report_type):
+ additional_conditions = " and posting_date >= %(year_start_date)s" \
+ if report_type == "Profit and Loss" else ""
+
+ if not flt(filters.with_period_closing_entry):
+ additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'"
+
+ gle = frappe.db.sql("""
+ select
+ account, sum(ifnull(debit, 0)) as opening_debit, sum(ifnull(credit, 0)) as opening_credit
+ from `tabGL Entry`
+ where
+ company=%(company)s
+ {additional_conditions}
+ and posting_date < %(from_date)s
+ and account in (select name from `tabAccount` where report_type=%(report_type)s)
+ group by account""".format(additional_conditions=additional_conditions),
+ {
+ "company": filters.company,
+ "from_date": filters.from_date,
+ "report_type": report_type,
+ "year_start_date": filters.year_start_date
+ },
+ as_dict=True)
+
+ opening = frappe._dict()
+ for d in gle:
+ opening.setdefault(d.account, d)
+
+ return opening
-def calculate_values(accounts, gl_entries_by_account, filters):
+def calculate_values(accounts, gl_entries_by_account, opening_balances, filters):
init = {
"opening_debit": 0.0,
"opening_credit": 0.0,
@@ -88,29 +129,15 @@
d.update(init.copy())
for entry in gl_entries_by_account.get(d.name, []):
- posting_date = getdate(entry.posting_date)
-
- # opening
- if posting_date < filters.from_date:
- is_valid_opening = (d.root_type in ("Asset", "Liability", "Equity") or
- (filters.year_start_date <= posting_date < filters.from_date))
-
- if is_valid_opening:
- d["opening_debit"] += flt(entry.debit)
- d["opening_credit"] += flt(entry.credit)
-
- elif posting_date <= filters.to_date:
-
- if entry.is_opening == "Yes" and d.root_type in ("Asset", "Liability", "Equity"):
- d["opening_debit"] += flt(entry.debit)
- d["opening_credit"] += flt(entry.credit)
-
- else:
- d["debit"] += flt(entry.debit)
- d["credit"] += flt(entry.credit)
+ d["debit"] += flt(entry.debit)
+ d["credit"] += flt(entry.credit)
total_row["debit"] += d["debit"]
total_row["credit"] += d["credit"]
+
+ # add opening
+ d["opening_debit"] = opening_balances.get(d.name, {}).get("opening_debit", 0)
+ d["opening_credit"] = opening_balances.get(d.name, {}).get("opening_credit", 0)
return total_row
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index b5056d7..298ff8e 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -91,7 +91,7 @@
# different filter for group and ledger - improved performance
if acc.is_group:
cond.append("""exists (
- select * from `tabAccount` ac where ac.name = gle.account
+ select name from `tabAccount` ac where ac.name = gle.account
and ac.lft >= %s and ac.rgt <= %s
)""" % (acc.lft, acc.rgt))
else:
@@ -397,7 +397,7 @@
for d in outstanding_voucher_list:
payment_amount = frappe.db.sql("""
- select ifnull(sum(ifnull({amount_query}, 0)), 0)
+ select ifnull(sum({amount_query}), 0)
from
`tabGL Entry`
where
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index 1f0b547..1e9e5c8 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -5,7 +5,7 @@
app_description = "Open Source Enterprise Resource Planning for Small and Midsized Organizations"
app_icon = "icon-th"
app_color = "#e74c3c"
-app_version = "5.0.22"
+app_version = "5.0.23"
error_report_email = "support@erpnext.com"
diff --git a/erpnext/manufacturing/doctype/production_order/production_order.py b/erpnext/manufacturing/doctype/production_order/production_order.py
index 6de0d7b..ca5f6d9 100644
--- a/erpnext/manufacturing/doctype/production_order/production_order.py
+++ b/erpnext/manufacturing/doctype/production_order/production_order.py
@@ -217,12 +217,14 @@
for i, d in enumerate(self.operations):
self.set_operation_start_end_time(i, d)
+ if not d.workstation:
+ continue
+
time_log = make_time_log(self.name, d.operation, d.planned_start_time, d.planned_end_time,
flt(self.qty) - flt(d.completed_qty), self.project_name, d.workstation, operation_id=d.name)
- if d.workstation:
- # validate operating hours if workstation [not mandatory] is specified
- self.check_operation_fits_in_working_hours(d)
+ # validate operating hours if workstation [not mandatory] is specified
+ self.check_operation_fits_in_working_hours(d)
original_start_time = time_log.from_time
while True:
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 0d1d0de..c647210 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -165,4 +165,5 @@
erpnext.patches.v5_0.portal_fixes
erpnext.patches.v5_0.reset_values_in_tools
execute:frappe.delete_doc("Page", "users")
-erpnext.patches.v5_0.update_material_transferred_for_manufacturing_again
\ No newline at end of file
+erpnext.patches.v5_0.update_material_transferred_for_manufacturing_again
+erpnext.patches.v5_0.index_on_account_and_gl_entry
\ No newline at end of file
diff --git a/erpnext/patches/v5_0/index_on_account_and_gl_entry.py b/erpnext/patches/v5_0/index_on_account_and_gl_entry.py
new file mode 100644
index 0000000..b03b27a
--- /dev/null
+++ b/erpnext/patches/v5_0/index_on_account_and_gl_entry.py
@@ -0,0 +1,22 @@
+from __future__ import unicode_literals
+
+import frappe
+
+def execute():
+ index_map = {
+ "Account": ["parent_account", "lft", "rgt"],
+ "GL Entry": ["posting_date", "account", 'party', "voucher_no"]
+ }
+
+ for dt, indexes in index_map.items():
+ existing_indexes = [d.Key_name for d in frappe.db.sql("""show index from `tab{0}`
+ where Column_name != 'name'""".format(dt), as_dict=1)]
+
+ for old in existing_indexes:
+ if old in ("parent", "group_or_ledger", "is_pl_account", "debit_or_credit", "account_name", "company"):
+ frappe.db.sql("alter table `tab{0}` drop index {1}".format(dt, old))
+ existing_indexes.remove(old)
+
+ for new in indexes:
+ if new not in existing_indexes:
+ frappe.db.sql("alter table `tab{0}` add index ({1})".format(dt, new))
\ No newline at end of file
diff --git a/erpnext/projects/doctype/time_log/time_log.py b/erpnext/projects/doctype/time_log/time_log.py
index a0fa23b..aa5647e 100644
--- a/erpnext/projects/doctype/time_log/time_log.py
+++ b/erpnext/projects/doctype/time_log/time_log.py
@@ -128,7 +128,7 @@
def update_production_order(self):
"""Updates `start_date`, `end_date`, `status` for operation in Production Order."""
-
+
if self.production_order and self.for_manufacturing:
if not self.operation_id:
frappe.throw(_("Operation ID not set"))
@@ -208,22 +208,23 @@
self.production_order = None
self.operation = None
self.quantity = None
-
+
def update_cost(self):
rate = get_activity_cost(self.employee, self.activity_type)
if rate:
self.costing_rate = rate.get('costing_rate')
- self.billing_rate = rate.get('billing_rate')
+ self.billing_rate = rate.get('billing_rate')
self.costing_amount = self.costing_rate * self.hours
if self.billable:
self.billing_amount = self.billing_rate * self.hours
else:
self.billing_amount = 0
-
+
def validate_task(self):
- if self.project and not self.task:
+ # if a time log is being created against a project without production order
+ if (self.project and not self.production_order) and not self.task:
frappe.throw(_("Task is Mandatory if Time Log is against a project"))
-
+
def update_task(self):
if self.task and frappe.db.exists("Task", self.task):
task = frappe.get_doc("Task", self.task)
@@ -266,7 +267,7 @@
d.title += " for Project: " + d.project
return data
-
+
@frappe.whitelist()
def get_activity_cost(employee=None, activity_type=None):
rate = frappe.db.sql("""select costing_rate, billing_rate from `tabActivity Cost` where employee= %s
diff --git a/setup.py b/setup.py
index 2a8f024..16213d1 100644
--- a/setup.py
+++ b/setup.py
@@ -1,6 +1,6 @@
from setuptools import setup, find_packages
-version = "5.0.22"
+version = "5.0.23"
with open("requirements.txt", "r") as f:
install_requires = f.readlines()