def execute():
for doctype in ['BOM Explosion Item', 'BOM Item', 'Work Order Item', 'Item']:
if frappe.db.has_column(doctype, 'allow_transfer_for_manufacture'):
if doctype != 'Item':
frappe.reload_doc('manufacturing', 'doctype', frappe.scrub(doctype))
else:
frappe.reload_doc('stock', 'doctype', frappe.scrub(doctype))
rename_field(doctype, "allow_transfer_for_manufacture", "include_item_in_manufacturing")
if frappe.db.has_column('BOM', 'allow_same_item_multiple_times'):
frappe.db.sql(""" UPDATE tabBOM
SET
allow_same_item_multiple_times = 0
WHERE
trim(coalesce(allow_same_item_multiple_times, '')) = '' """)
for doctype in ['BOM', 'Work Order']:
frappe.reload_doc('manufacturing', 'doctype', frappe.scrub(doctype))
if frappe.db.has_column(doctype, 'transfer_material_against_job_card'):
frappe.db.sql(""" UPDATE `tab%s`
SET transfer_material_against = CASE WHEN
transfer_material_against_job_card = 1 then 'Job Card' Else 'Work Order' END
WHERE docstatus < 2""" % (doctype))
else:
frappe.db.sql(""" UPDATE `tab%s`
SET transfer_material_against = 'Work Order'
WHERE docstatus < 2""" % (doctype))
def get_column(course_dict):
columns = [{
"fieldname": "student",
"label": _("Student ID"),
"fieldtype": "Link",
"options": "Student",
"width": 90
},
{
"fieldname": "student_name",
"label": _("Student Name"),
"fieldtype": "Data",
"width": 160
}]
for course in course_dict:
columns.append({
"fieldname": "grade_" + frappe.scrub(course),
"label": course,
"fieldtype": "Data",
"width": 110
})
columns.append({
"fieldname": "score_" + frappe.scrub(course),
"label": "Score(" + str(course_dict[course]) + ")",
"fieldtype": "Float",
"width": 100
})
return columns
def update_number_field(doctype_name, name, field_name, number_value, company): ''' doctype_name = Name of the DocType name = Docname being referred field_name = Name of the field thats holding the 'number' attribute number_value = Numeric value entered in field_name Stores the number entered in the dialog to the DocType's field. Renames the document by adding the number as a prefix to the current name and updates all transaction where it was present. ''' doc_title = frappe.db.get_value(doctype_name, name, frappe.scrub(doctype_name)+"_name") validate_field_number(doctype_name, name, number_value, company, field_name) frappe.db.set_value(doctype_name, name, field_name, number_value) if doc_title[0].isdigit(): separator = " - " if " - " in doc_title else " " doc_title = doc_title.split(separator, 1)[1] frappe.db.set_value(doctype_name, name, frappe.scrub(doctype_name)+"_name", doc_title) new_name = get_autoname_with_number(number_value, doc_title, name, company) if name != new_name: frappe.rename_doc(doctype_name, name, new_name) return new_name
def on_update(self):
"""
Writes the .txt for this page and if write_content is checked,
it will write out a .html file
"""
if not frappe.flags.in_import and getattr(frappe.get_conf(),'developer_mode', 0) and self.is_standard:
from frappe.modules.export_file import export_to_files
from frappe.modules import get_module_path, scrub
import os
# json
export_to_files(record_list=[['Web Form', self.name]])
# write files
path = os.path.join(get_module_path(self.module), 'web_form', scrub(self.name), scrub(self.name))
# js
if not os.path.exists(path + '.js'):
with open(path + '.js', 'w') as f:
f.write("""frappe.ready(function() {
// bind events here
})""")
# py
if not os.path.exists(path + '.py'):
with open(path + '.py', 'w') as f:
f.write("""from __future__ import unicode_literals
import frappe
def get_context(context):
# do your magic here
pass
""")
def get_column(assessment_criteria):
columns = [{
"fieldname": "student",
"label": _("Student ID"),
"fieldtype": "Link",
"options": "Student",
"width": 90
},
{
"fieldname": "student_name",
"label": _("Student Name"),
"fieldtype": "Data",
"width": 160
}]
for d in assessment_criteria:
columns.append({
"fieldname": frappe.scrub(d),
"label": d,
"fieldtype": "Data",
"width": 110
})
columns.append({
"fieldname": frappe.scrub(d) +"_score",
"label": "Score(" + str(int(assessment_criteria[d])) + ")",
"fieldtype": "Float",
"width": 100
})
return columns
def get_columns_dict(columns):
"""Returns a dict with column docfield values as dict
The keys for the dict are both idx and fieldname,
so either index or fieldname can be used to search for a column's docfield properties
"""
columns_dict = {}
for idx, col in enumerate(columns):
col_dict = {}
# string
if isinstance(col, basestring):
col = col.split(":")
if len(col) > 1:
if "/" in col[1]:
col_dict["fieldtype"], col_dict["options"] = col[1].split("/")
else:
col_dict["fieldtype"] = col[1]
col_dict["fieldname"] = frappe.scrub(col[0])
# dict
else:
col_dict.update(col)
if "fieldname" not in col_dict:
col_dict["fieldname"] = frappe.scrub(col_dict["label"])
columns_dict[idx] = col_dict
columns_dict[col_dict["fieldname"]] = col_dict
return columns_dict
def get_expense_cost_center(doctype, args):
if doctype == 'Item Group':
return frappe.db.get_value('Item Default',
{'parent': args.get(frappe.scrub(doctype)), 'company': args.get('company')},
['buying_cost_center', 'expense_account'])
else:
return frappe.db.get_value(doctype, args.get(frappe.scrub(doctype)),\
['cost_center', 'default_expense_account'])
def rename_inside_controller(self, new, old, new_path):
for fname in ('{}.js', '{}.py', '{}_list.js', '{}_calendar.js', 'test_{}.py', 'test_{}.js'):
fname = os.path.join(new_path, fname.format(frappe.scrub(new)))
if os.path.exists(fname):
with open(fname, 'r') as f:
code = f.read()
with open(fname, 'w') as f:
f.write(code.replace(frappe.scrub(old).replace(' ', ''), frappe.scrub(new).replace(' ', '')))
def get_web_template(self, suffix=''):
'''Returns the relative path of the row template for this doctype'''
module_name = frappe.scrub(self.module)
doctype = frappe.scrub(self.name)
template_path = frappe.get_module_path(module_name, 'doctype',
doctype, 'templates', doctype + suffix + '.html')
if os.path.exists(template_path):
return '{module_name}/doctype/{doctype_name}/templates/{doctype_name}{suffix}.html'.format(
module_name = module_name, doctype_name = doctype, suffix=suffix)
return None
def get_defaults(self, key=None, parent="__default"): """Get all defaults""" if key: defaults = frappe.defaults.get_defaults(parent) d = defaults.get(key, None) if(not d and key != frappe.scrub(key)): d = defaults.get(frappe.scrub(key), None) return d else: return frappe.defaults.get_defaults(parent)
def get_mapping_module(self, mapping_name):
try:
module_def = frappe.get_doc("Module Def", self.module)
module = frappe.get_module('{app}.{module}.data_migration_mapping.{mapping_name}'.format(
app= module_def.app_name,
module=frappe.scrub(self.module),
mapping_name=frappe.scrub(mapping_name)
))
return module
except ImportError:
return None
def cleanup_fields_value(self):
for logic_field in ["apply_on", "applicable_for", "price_or_discount"]:
fieldname = frappe.scrub(self.get(logic_field) or "")
# reset all values except for the logic field
options = (self.meta.get_options(logic_field) or "").split("\n")
for f in options:
if not f: continue
f = frappe.scrub(f)
if f!=fieldname:
self.set(f, None)
def rename_files_and_folders(self, old, new):
# move files
new_path = get_doc_path(self.module, 'doctype', new)
subprocess.check_output(['mv', get_doc_path(self.module, 'doctype', old), new_path])
# rename files
for fname in os.listdir(new_path):
if frappe.scrub(old) in fname:
subprocess.check_output(['mv', os.path.join(new_path, fname),
os.path.join(new_path, fname.replace(frappe.scrub(old), frappe.scrub(new)))])
self.rename_inside_controller(new, old, new_path)
frappe.msgprint('Renamed files and replaced code in controllers, please check!')
def get_user_default_as_list(key, user=None): user_defaults = get_defaults(user or frappe.session.user) d = user_defaults.get(key, None) if key != frappe.scrub(key): if d and isinstance(d, (list, tuple)) and len(d)==1: # Use User Permission value when only when it has a single value d = [d[0]] else: d = user_defaults.get(frappe.scrub(key), None) return (not isinstance(d, (list, tuple))) and [d] or d
def execute(filters=None):
data, chart, grades = [], [], []
args = frappe._dict()
grade_wise_analysis = defaultdict(dict)
args["academic_year"] = filters.get("academic_year")
args["course"] = filters.get("course")
args["assessment_group"] = filters.get("assessment_group")
args["academic_term"] = filters.get("academic_term")
args["student_group"] = filters.get("student_group")
if args["assessment_group"] == "All Assessment Groups":
frappe.throw(_("Please select the assessment group other than 'All Assessment Groups'"))
returned_values = get_formatted_result(args, get_assessment_criteria=True)
student_dict = returned_values["student_details"]
result_dict = returned_values["assessment_result"]
assessment_criteria_dict = returned_values["assessment_criteria"]
for student in result_dict:
student_row = {}
student_row["student"] = student
student_row["student_name"] = student_dict[student]
for criteria in assessment_criteria_dict:
scrub_criteria = frappe.scrub(criteria)
if criteria in result_dict[student][args.course][args.assessment_group]:
student_row[scrub_criteria] = result_dict[student][args.course][args.assessment_group][criteria]["grade"]
student_row[scrub_criteria + "_score"] = result_dict[student][args.course][args.assessment_group][criteria]["score"]
# create the list of possible grades
if student_row[scrub_criteria] not in grades:
grades.append(student_row[scrub_criteria])
# create the dict of for gradewise analysis
if student_row[scrub_criteria] not in grade_wise_analysis[criteria]:
grade_wise_analysis[criteria][student_row[scrub_criteria]] = 1
else:
grade_wise_analysis[criteria][student_row[scrub_criteria]] += 1
else:
student_row[frappe.scrub(criteria)] = ""
student_row[frappe.scrub(criteria)+ "_score"] = ""
data.append(student_row)
assessment_criteria_list = [d for d in assessment_criteria_dict]
columns = get_column(assessment_criteria_dict)
chart = get_chart_data(grades, assessment_criteria_list, grade_wise_analysis)
return columns, data, None, chart
def add_custom_context_and_script(self, context):
'''Update context from module if standard and append script'''
if self.web_form_module:
new_context = self.web_form_module.get_context(context)
if new_context:
context.update(new_context)
js_path = os.path.join(os.path.dirname(self.web_form_module.__file__), scrub(self.name) + '.js')
if os.path.exists(js_path):
context.script = frappe.render_template(open(js_path, 'r').read().decode('utf-8'), context)
css_path = os.path.join(os.path.dirname(self.web_form_module.__file__), scrub(self.name) + '.css')
if os.path.exists(css_path):
context.style = open(css_path, 'r').read()
def test_Custom_Script_fixture_multi_name_not_equal(self):
fixture = ["Custom Script", {"name":["Item-Client", "Customer-Client"],"op":"!="}]
path = frappe.scrub(fixture[0]) + "_multi_name_not_equal.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Custom_Script_fixture_rex_with_flags(self):
fixture = ["Custom Script", {"name":r"^[i|A]", "flags":"L,M"}]
path = frappe.scrub(fixture[0]) + "_rex_with_flags.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def get_party_account(party_type, party, company):
"""Returns the account for the given `party`.
Will first search in party (Customer / Supplier) record, if not found,
will search in group (Customer Group / Supplier Group),
finally will return default."""
if not company:
frappe.throw(_("Please select a Company"))
if not party:
return
account = frappe.db.get_value("Party Account",
{"parenttype": party_type, "parent": party, "company": company}, "account")
if not account and party_type in ['Customer', 'Supplier']:
party_group_doctype = "Customer Group" if party_type=="Customer" else "Supplier Group"
group = frappe.get_cached_value(party_type, party, scrub(party_group_doctype))
account = frappe.db.get_value("Party Account",
{"parenttype": party_group_doctype, "parent": group, "company": company}, "account")
if not account and party_type in ['Customer', 'Supplier']:
default_account_name = "default_receivable_account" \
if party_type=="Customer" else "default_payable_account"
account = frappe.get_cached_value('Company', company, default_account_name)
existing_gle_currency = get_party_gle_currency(party_type, party, company)
if existing_gle_currency:
if account:
account_currency = frappe.db.get_value("Account", account, "account_currency", cache=True)
if (account and account_currency != existing_gle_currency) or not account:
account = get_party_gle_account(party_type, party, company)
return account
def test_Doctype_fixture_rex_with_flags(self):
fixture = ["ToDo", {"name":r"^TDi", "flags":"L,M"}]
path = "Doctype_" + frappe.scrub(fixture[0]) + "_rex_with_flags_should_be_none.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Custom_Script_fixture_simple_name_at_least_equal(self):
fixture = ["Custom Script", {"name":"Item-Cli"}]
path = frappe.scrub(fixture[0]) + "_simple_name_at_least_equal.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Doctype_multi_name_not_equal(self):
fixture = ["ToDo", {"name":["TDI00000002", "TDI00000008"],"op":"!="}]
path = "Doctype_" + frappe.scrub(fixture[0]) + "_multi_name_not_equal.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Doctype_fixture_just_list(self):
fixture = ["ToDo"]
path = "Doctype_" + frappe.scrub(fixture[0]) + "_just_list_should_be_all.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Doctype_fixture_simple(self):
fixture = "ToDo"
path = "Doctype_" + frappe.scrub(fixture) + "_original_style_should_be_all.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Doctype_fixture_simple_name_at_least_equal(self):
fixture = ["ToDo", {"name":"TDI"}]
path = "Doctype_" + frappe.scrub(fixture[0]) + "_simple_name_at_least_equal.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Custom_Field_fixture_empty_object(self):
fixture = ["Custom Field", {}]
path = frappe.scrub(fixture[0]) + "_empty_object_should_be_all.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Custom_Script_fixture_just_list(self):
fixture = ["Custom Script"]
path = frappe.scrub(fixture[0]) + "_just_list_should_be_all.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def get_data(filters):
data = []
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
item_details = get_item_details(items, sle, filters)
periodic_data = get_periodic_data(sle, filters)
ranges = get_period_date_ranges(filters)
for dummy, item_data in iteritems(item_details):
row = {
"name": item_data.name,
"item_name": item_data.item_name,
"item_group": item_data.item_group,
"uom": item_data.stock_uom,
"brand": item_data.brand,
}
total = 0
for dummy, end_date in ranges:
period = get_period(end_date, filters)
amount = flt(periodic_data.get(item_data.name, {}).get(period))
row[scrub(period)] = amount
total += amount
row["total"] = total
data.append(row)
return data
def test_Custom_Field_fixture_rex_no_flags(self):
fixture = ["Custom Field", {"name":r"^[r|L]"}]
path = frappe.scrub(fixture[0]) + "_rex_no_flags.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def test_Custom_Script_fixture_simple(self):
fixture = "Custom Script"
path = frappe.scrub(fixture) + "_original_style.csv"
# print "teste done {}".format(path)
export_csv(fixture, path)
self.assertTrue(True)
os.remove(path)
def validate_mandatory(self):
for field in ["apply_on", "applicable_for"]:
tocheck = frappe.scrub(self.get(field) or "")
if tocheck and not self.get(tocheck):
throw(_("{0} is required").format(self.meta.get_label(tocheck)), frappe.MandatoryError)
def get_payment_entry(dt,
dn,
party_amount=None,
bank_account=None,
bank_amount=None):
doc = frappe.get_doc(dt, dn)
if dt in ("Sales Order", "Purchase Order") and flt(doc.per_billed, 2) > 0:
frappe.throw(
_("Can only make payment against unbilled {0}").format(dt))
party_type = "Customer" if dt in ("Sales Invoice",
"Sales Order") else "Supplier"
# party account
if dt == "Sales Invoice":
party_account = doc.debit_to
elif dt == "Purchase Invoice":
party_account = doc.credit_to
else:
party_account = get_party_account(party_type,
doc.get(party_type.lower()),
doc.company)
party_account_currency = doc.get(
"party_account_currency") or get_account_currency(party_account)
# payment type
if (dt == "Sales Order" or (dt=="Sales Invoice" and doc.outstanding_amount > 0)) \
or (dt=="Purchase Invoice" and doc.outstanding_amount < 0):
payment_type = "Receive"
else:
payment_type = "Pay"
# amounts
grand_total = outstanding_amount = 0
if party_amount:
grand_total = outstanding_amount = party_amount
elif dt in ("Sales Invoice", "Purchase Invoice"):
grand_total = doc.base_grand_total if party_account_currency == doc.company_currency else doc.grand_total
outstanding_amount = doc.outstanding_amount
else:
total_field = "base_grand_total" if party_account_currency == doc.company_currency else "grand_total"
grand_total = flt(doc.get(total_field))
outstanding_amount = grand_total - flt(doc.advance_paid)
# bank or cash
bank = get_default_bank_cash_account(
doc.company,
"Bank",
mode_of_payment=doc.get("mode_of_payment"),
account=bank_account)
paid_amount = received_amount = 0
if party_account_currency == bank.account_currency:
paid_amount = received_amount = abs(outstanding_amount)
elif payment_type == "Receive":
paid_amount = abs(outstanding_amount)
if bank_amount:
received_amount = bank_amount
else:
received_amount = abs(outstanding_amount)
if bank_amount:
paid_amount = bank_amount
pe = frappe.new_doc("Payment Entry")
pe.payment_type = payment_type
pe.company = doc.company
pe.posting_date = nowdate()
pe.mode_of_payment = doc.get("mode_of_payment")
pe.party_type = party_type
pe.party = doc.get(scrub(party_type))
pe.paid_from = party_account if payment_type == "Receive" else bank.account
pe.paid_to = party_account if payment_type == "Pay" else bank.account
pe.paid_from_account_currency = party_account_currency \
if payment_type=="Receive" else bank.account_currency
pe.paid_to_account_currency = party_account_currency if payment_type == "Pay" else bank.account_currency
pe.paid_amount = paid_amount
pe.received_amount = received_amount
pe.allocate_payment_amount = 1
pe.append(
"references", {
"reference_doctype": dt,
"reference_name": dn,
"due_date": doc.get("due_date"),
"total_amount": grand_total,
"outstanding_amount": outstanding_amount,
"allocated_amount": outstanding_amount
})
pe.setup_party_account_field()
pe.set_missing_values()
if party_account and bank:
pe.set_exchange_rate()
pe.set_amounts()
return pe
def get_columns(filters,currency_list):
if filters.get("presentation_currency"):
currency = filters["presentation_currency"]
else:
if filters.get("company"):
currency = get_company_currency(filters["company"])
else:
company = get_default_company()
currency = get_company_currency(company)
columns = [
{
"label": _("GL Entry"),
"fieldname": "gl_entry",
"fieldtype": "Link",
"options": "GL Entry",
"hidden": 1
},
{
"label": _("Posting Date"),
"fieldname": "posting_date",
"fieldtype": "Date",
"width": 90
},
{
"label": _("Account"),
"fieldname": "account",
"fieldtype": "Link",
"options": "Account",
"width": 180
},
{
"label": _("Debit ({0})".format(currency)),
"fieldname": "debit",
"fieldtype": "Float",
"width": 100
},
{
"label": _("Credit ({0})".format(currency)),
"fieldname": "credit",
"fieldtype": "Float",
"width": 100
},
{
"label": _("Balance ({0})".format(currency)),
"fieldname": "balance",
"fieldtype": "Float",
"width": 130
}
]
if len(currency_list)>1:
for curr in currency_list:
if curr!=currency:
columns.extend([
{
"label": _("Debit ({0})".format(curr)),
"fieldname": "debit_"+frappe.scrub(curr),
"fieldtype": "Float",
"width": 100
},
{
"label": _("Credit ({0})".format(curr)),
"fieldname": "credit_"+frappe.scrub(curr),
"fieldtype": "Float",
"width": 100
},
{
"label": _("Balance ({0})".format(curr)),
"fieldname": "balance_"+frappe.scrub(curr),
"fieldtype": "Float",
"width": 130
}])
columns.extend([
{
"label": _("Voucher Type"),
"fieldname": "voucher_type",
"width": 120
},
{
"label": _("Voucher No"),
"fieldname": "voucher_no",
"fieldtype": "Dynamic Link",
"options": "voucher_type",
"width": 180
},
{
"label": _("Against Account"),
"fieldname": "against",
"width": 120
},
{
"label": _("Party Type"),
"fieldname": "party_type",
"width": 100
},
{
"label": _("Party"),
"fieldname": "party",
"width": 100
},
{
"label": _("Project"),
"options": "Project",
"fieldname": "project",
"width": 100
},
{
"label": _("Cost Center"),
"options": "Cost Center",
"fieldname": "cost_center",
"width": 100
},
{
"label": _("Against Voucher Type"),
"fieldname": "against_voucher_type",
"width": 100
},
{
"label": _("Against Voucher"),
"fieldname": "against_voucher",
"fieldtype": "Dynamic Link",
"options": "against_voucher_type",
"width": 100
},
{
"label": _("Supplier Invoice No"),
"fieldname": "bill_no",
"fieldtype": "Data",
"width": 100
},
{
"label": _("Remarks"),
"fieldname": "remarks",
"width": 400
}
])
return columns
def get_payment_entry(dt,
dn,
party_amount=None,
bank_account=None,
bank_amount=None):
doc = frappe.get_doc(dt, dn)
if dt in ("Sales Order", "Purchase Order") and flt(doc.per_billed, 2) > 0:
frappe.throw(
_("Can only make payment against unbilled {0}").format(dt))
if dt in ("Sales Invoice", "Sales Order"):
party_type = "Customer"
elif dt in ("Purchase Invoice", "Purchase Order"):
party_type = "Supplier"
elif dt in ("Expense Claim", "Employee Advance"):
party_type = "Employee"
elif dt in ("Fees"):
party_type = "Student"
# party account
if dt == "Sales Invoice":
party_account = doc.debit_to
elif dt == "Purchase Invoice":
party_account = doc.credit_to
elif dt == "Fees":
party_account = doc.receivable_account
elif dt == "Employee Advance":
party_account = doc.advance_account
else:
party_account = get_party_account(party_type,
doc.get(party_type.lower()),
doc.company)
party_account_currency = doc.get(
"party_account_currency") or get_account_currency(party_account)
# payment type
if (dt == "Sales Order" or (dt in ("Sales Invoice", "Fees") and doc.outstanding_amount > 0)) \
or (dt=="Purchase Invoice" and doc.outstanding_amount < 0):
payment_type = "Receive"
else:
payment_type = "Pay"
# amounts
grand_total = outstanding_amount = 0
if party_amount:
grand_total = outstanding_amount = party_amount
elif dt in ("Sales Invoice", "Purchase Invoice"):
if party_account_currency == doc.company_currency:
grand_total = doc.base_rounded_total or doc.base_grand_total
else:
grand_total = doc.rounded_total or doc.grand_total
outstanding_amount = doc.outstanding_amount
elif dt in ("Expense Claim"):
grand_total = doc.total_sanctioned_amount
outstanding_amount = doc.total_sanctioned_amount \
- doc.total_amount_reimbursed - flt(doc.total_advance_amount)
elif dt == "Employee Advance":
grand_total = doc.advance_amount
outstanding_amount = flt(doc.advance_amount) - flt(doc.paid_amount)
elif dt == "Fees":
grand_total = doc.grand_total
outstanding_amount = doc.outstanding_amount
else:
if party_account_currency == doc.company_currency:
grand_total = flt(
doc.get("base_rounded_total") or doc.base_grand_total)
else:
grand_total = flt(doc.get("rounded_total") or doc.grand_total)
outstanding_amount = grand_total - flt(doc.advance_paid)
# bank or cash
bank = get_default_bank_cash_account(
doc.company,
"Bank",
mode_of_payment=doc.get("mode_of_payment"),
account=bank_account)
paid_amount = received_amount = 0
if party_account_currency == bank.account_currency:
paid_amount = received_amount = abs(outstanding_amount)
elif payment_type == "Receive":
paid_amount = abs(outstanding_amount)
if bank_amount:
received_amount = bank_amount
else:
received_amount = abs(outstanding_amount)
if bank_amount:
paid_amount = bank_amount
pe = frappe.new_doc("Payment Entry")
pe.payment_type = payment_type
pe.company = doc.company
pe.posting_date = nowdate()
pe.mode_of_payment = doc.get("mode_of_payment")
pe.party_type = party_type
pe.party = doc.get(scrub(party_type))
pe.paid_from = party_account if payment_type == "Receive" else bank.account
pe.paid_to = party_account if payment_type == "Pay" else bank.account
pe.paid_from_account_currency = party_account_currency \
if payment_type=="Receive" else bank.account_currency
pe.paid_to_account_currency = party_account_currency if payment_type == "Pay" else bank.account_currency
pe.paid_amount = paid_amount
pe.received_amount = received_amount
pe.allocate_payment_amount = 1
pe.letter_head = doc.get("letter_head")
pe.append(
"references", {
'reference_doctype': dt,
'reference_name': dn,
"bill_no": doc.get("bill_no"),
"due_date": doc.get("due_date"),
'total_amount': grand_total,
'outstanding_amount': outstanding_amount,
'allocated_amount': outstanding_amount
})
pe.setup_party_account_field()
pe.set_missing_values()
if party_account and bank:
pe.set_exchange_rate()
pe.set_amounts()
return pe
def validate_reference_documents(self):
if self.party_type == "Student":
valid_reference_doctypes = ("Fees")
elif self.party_type == "Customer":
valid_reference_doctypes = ("Sales Order", "Sales Invoice",
"Journal Entry")
elif self.party_type == "Supplier":
valid_reference_doctypes = ("Purchase Order", "Purchase Invoice",
"Journal Entry")
elif self.party_type == "Employee":
valid_reference_doctypes = ("Expense Claim", "Journal Entry",
"Employee Advance")
for d in self.get("references"):
if not d.allocated_amount:
continue
if d.reference_doctype not in valid_reference_doctypes:
frappe.throw(
_("Reference Doctype must be one of {0}").format(
comma_or(valid_reference_doctypes)))
elif d.reference_name:
if not frappe.db.exists(d.reference_doctype, d.reference_name):
frappe.throw(
_("{0} {1} does not exist").format(
d.reference_doctype, d.reference_name))
else:
ref_doc = frappe.get_doc(d.reference_doctype,
d.reference_name)
if d.reference_doctype != "Journal Entry":
if self.party != ref_doc.get(scrub(self.party_type)):
frappe.throw(
_("{0} {1} is not associated with {2} {3}").
format(d.reference_doctype, d.reference_name,
self.party_type, self.party))
else:
self.validate_journal_entry()
if d.reference_doctype in ("Sales Invoice",
"Purchase Invoice",
"Expense Claim", "Fees"):
if self.party_type == "Customer":
ref_party_account = ref_doc.debit_to
elif self.party_type == "Student":
ref_party_account = ref_doc.receivable_account
elif self.party_type == "Supplier":
ref_party_account = ref_doc.credit_to
elif self.party_type == "Employee":
ref_party_account = ref_doc.payable_account
if ref_party_account != self.party_account:
frappe.throw(
_("{0} {1} is associated with {2}, but Party Account is {3}"
).format(d.reference_doctype,
d.reference_name, ref_party_account,
self.party_account))
if ref_doc.docstatus != 1:
frappe.throw(
_("{0} {1} must be submitted").format(
d.reference_doctype, d.reference_name))
def execute():
for doctype in ("Salary Component", "Salary Detail"):
if "depends_on_lwp" in frappe.db.get_table_columns(doctype):
frappe.reload_doc("Payroll", "doctype", scrub(doctype))
rename_field(doctype, "depends_on_lwp", "depends_on_payment_days")
def filter_pricing_rules(args, pricing_rules, doc=None):
if not isinstance(pricing_rules, list):
pricing_rules = [pricing_rules]
original_pricing_rule = copy.copy(pricing_rules)
# filter for qty
if pricing_rules:
stock_qty = flt(args.get('stock_qty'))
amount = flt(args.get('price_list_rate')) * flt(args.get('qty'))
if pricing_rules[0].apply_rule_on_other:
field = frappe.scrub(pricing_rules[0].apply_rule_on_other)
if (field and
pricing_rules[0].get('other_' + field) != args.get(field)):
return
pr_doc = frappe.get_cached_doc('Pricing Rule', pricing_rules[0].name)
if pricing_rules[0].mixed_conditions and doc:
stock_qty, amount, items = get_qty_and_rate_for_mixed_conditions(
doc, pr_doc, args)
pricing_rules[0].apply_rule_on_other_items = items
elif pricing_rules[0].is_cumulative:
items = [args.get(frappe.scrub(pr_doc.get('apply_on')))]
data = get_qty_amount_data_for_cumulative(pr_doc, args, items)
if data:
stock_qty += data[0]
amount += data[1]
if pricing_rules[0].apply_rule_on_other and not pricing_rules[
0].mixed_conditions and doc:
pricing_rules = get_qty_and_rate_for_other_item(
doc, pr_doc, pricing_rules) or []
else:
pricing_rules = filter_pricing_rules_for_qty_amount(
stock_qty, amount, pricing_rules, args)
if not pricing_rules:
for d in original_pricing_rule:
if not d.threshold_percentage: continue
msg = validate_quantity_and_amount_for_suggestion(
d, stock_qty, amount, args.get('item_code'),
args.get('transaction_type'))
if msg:
return {
'suggestion': msg,
'item_code': args.get('item_code')
}
# add variant_of property in pricing rule
for p in pricing_rules:
if p.item_code and args.variant_of:
p.variant_of = args.variant_of
else:
p.variant_of = None
# find pricing rule with highest priority
if pricing_rules:
max_priority = max([cint(p.priority) for p in pricing_rules])
if max_priority:
pricing_rules = list(
filter(lambda x: cint(x.priority) == max_priority,
pricing_rules))
# apply internal priority
all_fields = [
"item_code", "item_group", "brand", "customer", "customer_group",
"territory", "supplier", "supplier_group", "campaign", "sales_partner",
"variant_of"
]
if len(pricing_rules) > 1:
for field_set in [["item_code", "variant_of", "item_group", "brand"],
["customer", "customer_group", "territory"],
["supplier", "supplier_group"]]:
remaining_fields = list(set(all_fields) - set(field_set))
if if_all_rules_same(pricing_rules, remaining_fields):
pricing_rules = apply_internal_priority(
pricing_rules, field_set, args)
break
if pricing_rules and not isinstance(pricing_rules, list):
pricing_rules = list(pricing_rules)
if len(pricing_rules) > 1:
rate_or_discount = list(
set([d.rate_or_discount for d in pricing_rules]))
if len(rate_or_discount
) == 1 and rate_or_discount[0] == "Discount Percentage":
pricing_rules = list(filter(lambda x: x.for_price_list==args.price_list, pricing_rules)) \
or pricing_rules
if len(pricing_rules) > 1 and not args.for_shopping_cart:
frappe.throw(
_("Multiple Price Rules exists with same criteria, please resolve conflict by assigning priority. Price Rules: {0}"
).format("\n".join([d.name for d in pricing_rules])),
MultiplePricingRuleConflict)
elif pricing_rules:
return pricing_rules[0]
def validate_reference_doc(self):
"""Validates reference document"""
field_dict = {
'Sales Invoice': ["Customer", "Debit To"],
'Purchase Invoice': ["Supplier", "Credit To"],
'Sales Order': ["Customer"],
'Purchase Order': ["Supplier"]
}
self.reference_totals = {}
self.reference_types = {}
self.reference_accounts = {}
for d in self.get("accounts"):
if not d.reference_type:
d.reference_name = None
if not d.reference_name:
d.reference_type = None
if d.reference_type and d.reference_name and (
d.reference_type in field_dict.keys()):
dr_or_cr = "credit_in_account_currency" \
if d.reference_type in ("Sales Order", "Sales Invoice") else "debit_in_account_currency"
# check debit or credit type Sales / Purchase Order
if d.reference_type == "Sales Order" and flt(d.debit) > 0:
frappe.throw(
_("Row {0}: Debit entry can not be linked with a {1}").
format(d.idx, d.reference_type))
if d.reference_type == "Purchase Order" and flt(d.credit) > 0:
frappe.throw(
_("Row {0}: Credit entry can not be linked with a {1}"
).format(d.idx, d.reference_type))
# set totals
if not d.reference_name in self.reference_totals:
self.reference_totals[d.reference_name] = 0.0
self.reference_totals[d.reference_name] += flt(d.get(dr_or_cr))
self.reference_types[d.reference_name] = d.reference_type
self.reference_accounts[d.reference_name] = d.account
against_voucher = frappe.db.get_value(
d.reference_type, d.reference_name,
[scrub(dt) for dt in field_dict.get(d.reference_type)])
if not against_voucher:
frappe.throw(
_("Row {0}: Invalid reference {1}").format(
d.idx, d.reference_name))
# check if party and account match
if d.reference_type in ("Sales Invoice", "Purchase Invoice"):
if (against_voucher[0] != d.party
or against_voucher[1] != d.account):
frappe.throw(
_("Row {0}: Party / Account does not match with {1} / {2} in {3} {4}"
).format(d.idx,
field_dict.get(d.reference_type)[0],
field_dict.get(d.reference_type)[1],
d.reference_type, d.reference_name))
# check if party matches for Sales / Purchase Order
if d.reference_type in ("Sales Order", "Purchase Order"):
# set totals
if against_voucher != d.party:
frappe.throw(_("Row {0}: {1} {2} does not match with {3}") \
.format(d.idx, d.party_type, d.party, d.reference_type))
self.validate_orders()
self.validate_invoices()
def skip_row(self, row, product_bundles):
if self.filters.get("group_by") != "Invoice" and not row.get(
scrub(self.filters.get("group_by"))):
return True
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters:
filters = {}
columns = get_columns(additional_table_columns, filters)
company_currency = frappe.get_cached_value("Company", filters.get("company"), "default_currency")
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
mode_of_payments = get_mode_of_payments(set(d.parent for d in item_list))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ""
if filters.get("group_by"):
grand_total = get_grand_total(filters, "Sales Invoice")
customer_details = get_customer_details()
item_details = get_item_details()
for d in item_list:
customer_record = customer_details.get(d.customer)
item_record = item_details.get(d.item_code)
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = {
"item_code": d.item_code,
"item_name": item_record.item_name if item_record else d.item_name,
"item_group": item_record.item_group if item_record else d.item_group,
"description": d.description,
"invoice": d.parent,
"posting_date": d.posting_date,
"customer": d.customer,
"customer_name": customer_record.customer_name,
"customer_group": customer_record.customer_group,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update(
{
"debit_to": d.debit_to,
"mode_of_payment": ", ".join(mode_of_payments.get(d.parent, [])),
"territory": d.territory,
"project": d.project,
"company": d.company,
"sales_order": d.sales_order,
"delivery_note": d.delivery_note,
"income_account": d.unrealized_profit_loss_account
if d.is_internal_customer == 1
else d.income_account,
"cost_center": d.cost_center,
"stock_qty": d.stock_qty,
"stock_uom": d.stock_uom,
}
)
if d.stock_uom != d.uom and d.stock_qty:
row.update({"rate": (d.base_net_rate * d.qty) / d.stock_qty, "amount": d.base_net_amount})
else:
row.update({"rate": d.base_net_rate, "amount": d.base_net_amount})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update(
{
frappe.scrub(tax + " Rate"): item_tax.get("tax_rate", 0),
frappe.scrub(tax + " Amount"): item_tax.get("tax_amount", 0),
}
)
total_tax += flt(item_tax.get("tax_amount"))
row.update(
{"total_tax": total_tax, "total": d.base_net_amount + total_tax, "currency": company_currency}
)
if filters.get("group_by"):
row.update({"percent_gt": flt(row["total"] / grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(filters)
data, prev_group_by_value = add_total_row(
data,
filters,
prev_group_by_value,
d,
total_row_map,
group_by_field,
subtotal_display_field,
grand_total,
tax_columns,
)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ""), tax_columns)
data.append(row)
if filters.get("group_by") and item_list:
total_row = total_row_map.get(prev_group_by_value or d.get("item_name"))
total_row["percent_gt"] = flt(total_row["total"] / grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, "total_row", tax_columns)
data.append(total_row_map.get("total_row"))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row
def get_columns(self):
columns = [{
"label": _(self.filters.party_type),
"fieldtype": "Link",
"fieldname": "party",
"options": self.filters.party_type,
"width": 200
}]
if self.party_naming_by == "Naming Series":
columns.append({
"label": _(self.filters.party_type + "Name"),
"fieldtype": "Data",
"fieldname": "party_name",
"width": 110
})
credit_or_debit_note = "Credit Note" if self.filters.party_type == "Customer" else "Debit Note"
columns += [
{
"label": _("Opening Balance"),
"fieldname": "opening_balance",
"fieldtype": "Currency",
"options": "currency",
"width": 120
},
{
"label": _("Invoiced Amount"),
"fieldname": "invoiced_amount",
"fieldtype": "Currency",
"options": "currency",
"width": 120
},
{
"label": _("Paid Amount"),
"fieldname": "paid_amount",
"fieldtype": "Currency",
"options": "currency",
"width": 120
},
{
"label": _(credit_or_debit_note),
"fieldname": "return_amount",
"fieldtype": "Currency",
"options": "currency",
"width": 120
},
]
for account in self.party_adjustment_accounts:
columns.append({
"label": account,
"fieldname": "adj_" + scrub(account),
"fieldtype": "Currency",
"options": "currency",
"width": 120,
"is_adjustment": 1
})
columns += [
{
"label": _("Closing Balance"),
"fieldname": "closing_balance",
"fieldtype": "Currency",
"options": "currency",
"width": 120
},
{
"label": _("Currency"),
"fieldname": "currency",
"fieldtype": "Link",
"options": "Currency",
"width": 50
}
]
return columns
def prepare_conditions(self, party_type):
conditions = [""]
values = [party_type]
party_type_field = scrub(party_type)
if self.filters.company:
conditions.append("company=%s")
values.append(self.filters.company)
company_finance_book = erpnext.get_default_finance_book(
self.filters.company)
if not self.filters.finance_book or (self.filters.finance_book
== company_finance_book):
conditions.append("ifnull(finance_book,'') in (%s, '')")
values.append(company_finance_book)
elif self.filters.finance_book:
conditions.append("ifnull(finance_book,'') = %s")
values.append(self.filters.finance_book)
if self.filters.get(party_type_field):
conditions.append("party=%s")
values.append(self.filters.get(party_type_field))
if party_type_field == "customer":
account_type = "Receivable"
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value(
"Customer Group", self.filters.get("customer_group"),
["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
and name=tabCustomer.customer_group))""".format(lft, rgt))
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory",
self.filters.get("territory"),
["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1}
and name=tabCustomer.territory))""".format(lft, rgt))
if self.filters.get("payment_terms_template"):
conditions.append(
"party in (select name from tabCustomer where payment_terms=%s)"
)
values.append(self.filters.get("payment_terms_template"))
if self.filters.get("sales_partner"):
conditions.append(
"party in (select name from tabCustomer where default_sales_partner=%s)"
)
values.append(self.filters.get("sales_partner"))
if self.filters.get("sales_person"):
lft, rgt = frappe.db.get_value(
"Sales Person", self.filters.get("sales_person"),
["lft", "rgt"])
conditions.append(
"""exists(select name from `tabSales Team` steam where
steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1})
and ((steam.parent = voucher_no and steam.parenttype = voucher_type)
or (steam.parent = against_voucher and steam.parenttype = against_voucher_type)
or (steam.parent = party and steam.parenttype = 'Customer')))""".format(
lft, rgt))
elif party_type_field == "supplier":
account_type = "Payable"
if self.filters.get("supplier_group"):
conditions.append("""party in (select name from tabSupplier
where supplier_group=%s)""")
values.append(self.filters.get("supplier_group"))
accounts = [
d.name for d in frappe.get_all("Account",
filters={
"account_type": account_type,
"company": self.filters.company
})
]
conditions.append("account in (%s)" % ','.join(['%s'] * len(accounts)))
values += accounts
return " and ".join(conditions), values
def get_columns(self, party_naming_by, args):
columns = []
columns.append({
"label": _("Posting Date"),
"fieldtype": "Date",
"fieldname": "posting_date",
"width": 90
})
columns += [
_(args.get("party_type")) + ":Link/" + args.get("party_type") +
":200"
]
if args.get("party_type") == 'Customer':
columns.append({
"label": _("Customer Contact"),
"fieldtype": "Link",
"fieldname": "contact",
"options": "Contact",
"width": 100
})
if party_naming_by == "Naming Series":
columns += [args.get("party_type") + " Name::110"]
columns.append({
"label": _("Voucher Type"),
"fieldtype": "Data",
"fieldname": "voucher_type",
"width": 110
})
columns.append({
"label": _("Voucher No"),
"fieldtype": "Dynamic Link",
"fieldname": "voucher_no",
"width": 110,
"options": "voucher_type",
})
columns += [_("Due Date") + ":Date:80"]
if args.get("party_type") == "Supplier":
columns += [_("Bill No") + "::80", _("Bill Date") + ":Date:80"]
credit_or_debit_note = "Credit Note" if args.get(
"party_type") == "Customer" else "Debit Note"
if self.filters.based_on_payment_terms:
columns.append({
"label": "Payment Term",
"fieldname": "payment_term",
"fieldtype": "Data",
"width": 120
})
columns.append({
"label": "Invoice Grand Total",
"fieldname": "invoice_grand_total",
"fieldtype": "Currency",
"options": "currency",
"width": 120
})
for label in ("Invoiced Amount", "Paid Amount", credit_or_debit_note,
"Outstanding Amount"):
columns.append({
"label": label,
"fieldname": frappe.scrub(label),
"fieldtype": "Currency",
"options": "currency",
"width": 120
})
columns += [_("Age (Days)") + ":Int:80"]
self.ageing_col_idx_start = len(columns)
if not "range1" in self.filters:
self.filters["range1"] = "30"
if not "range2" in self.filters:
self.filters["range2"] = "60"
if not "range3" in self.filters:
self.filters["range3"] = "90"
for label in ("0-{range1}".format(range1=self.filters["range1"]),
"{range1}-{range2}".format(
range1=cint(self.filters["range1"]) + 1,
range2=self.filters["range2"]),
"{range2}-{range3}".format(
range2=cint(self.filters["range2"]) + 1,
range3=self.filters["range3"]),
"{range3}-{above}".format(
range3=cint(self.filters["range3"]) + 1,
above=_("Above"))):
columns.append({
"label": label,
"fieldname": label,
"fieldtype": "Currency",
"options": "currency",
"width": 120
})
columns += [{
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Link",
"options": "Currency",
"width": 100
}, {
"fieldname": "pdc/lc_ref",
"label": _("PDC/LC Ref"),
"fieldtype": "Data",
"width": 110
}, {
"fieldname": "pdc/lc_amount",
"label": _("PDC/LC Amount"),
"fieldtype": "Currency",
"options": "currency",
"width": 130
}, {
"fieldname": "remaining_balance",
"label": _("Remaining Balance"),
"fieldtype": "Currency",
"options": "currency",
"width": 130
}]
if args.get('party_type') == 'Customer':
columns += [{
"label": _("Customer LPO"),
"fieldtype": "Data",
"fieldname": "po_no",
"width": 100,
},
_("Delivery Note") + ":Data:100",
_("Territory") + ":Link/Territory:80",
_("Customer Group") + ":Link/Customer Group:120", {
"label": _("Sales Person"),
"fieldtype": "Data",
"fieldname": "sales_person",
"width": 120,
}]
if args.get("party_type") == "Supplier":
columns += [_("Supplier Group") + ":Link/Supplier Group:80"]
columns.append(_("Remarks") + "::200")
return columns
def get_data(self, party_naming_by, args):
from erpnext.accounts.utils import get_currency_precision
currency_precision = get_currency_precision() or 2
dr_or_cr = "debit" if args.get(
"party_type") == "Customer" else "credit"
dn_details = get_dn_details(args.get("party_type"))
voucher_details = self.get_voucher_details(args.get("party_type"),
dn_details)
future_vouchers = self.get_entries_after(self.filters.report_date,
args.get("party_type"))
if not self.filters.get("company"):
self.filters["company"] = frappe.db.get_single_value(
'Global Defaults', 'default_company')
company_currency = frappe.db.get_value("Company",
self.filters.get("company"),
"default_currency")
return_entries = self.get_return_entries(args.get("party_type"))
data = []
pdc_details = get_pdc_details(args.get("party_type"))
for gle in self.get_entries_till(self.filters.report_date,
args.get("party_type")):
if self.is_receivable_or_payable(gle, dr_or_cr, future_vouchers):
outstanding_amount, credit_note_amount = self.get_outstanding_amount(
gle, self.filters.report_date, dr_or_cr, return_entries,
currency_precision)
if abs(outstanding_amount) > 0.1 / 10**currency_precision:
row = [gle.posting_date, gle.party]
# customer / supplier name
if party_naming_by == "Naming Series":
row += [self.get_party_name(gle.party_type, gle.party)]
# get due date
due_date = voucher_details.get(gle.voucher_no,
{}).get("due_date", "")
row += [gle.voucher_type, gle.voucher_no, due_date]
# get supplier bill details
if args.get("party_type") == "Supplier":
row += [
voucher_details.get(gle.voucher_no,
{}).get("bill_no", ""),
voucher_details.get(gle.voucher_no,
{}).get("bill_date", "")
]
# invoiced and paid amounts
invoiced_amount = gle.get(dr_or_cr) if (
gle.get(dr_or_cr) > 0) else 0
paid_amt = invoiced_amount - outstanding_amount - credit_note_amount
row += [
invoiced_amount, paid_amt, credit_note_amount,
outstanding_amount
]
# ageing data
entry_date = due_date if self.filters.ageing_based_on == "Due Date" else gle.posting_date
row += get_ageing_data(cint(self.filters.range1),
cint(self.filters.range2),
cint(self.filters.range3),
self.age_as_on, entry_date,
outstanding_amount)
# issue 6371-Ageing buckets should not have amounts if due date is not reached
if self.filters.ageing_based_on == "Due Date" \
and getdate(due_date) > getdate(self.filters.report_date):
row[-1] = row[-2] = row[-3] = row[-4] = 0
if self.filters.get(scrub(args.get("party_type"))):
row.append(gle.account_currency)
else:
row.append(company_currency)
pdc = pdc_details.get(gle.voucher_no, {})
remaining_balance = outstanding_amount - flt(
pdc.get("pdc_amount"))
row += [
pdc.get("pdc_date"),
pdc.get("pdc_ref"),
flt(pdc.get("pdc_amount")), remaining_balance
]
if args.get('party_type') == 'Customer':
# customer LPO
row += [
voucher_details.get(gle.voucher_no,
{}).get("po_no")
]
# Delivery Note
row += [
voucher_details.get(gle.voucher_no,
{}).get("delivery_note")
]
# customer territory / supplier type
if args.get("party_type") == "Customer":
row += [
self.get_territory(gle.party),
self.get_customer_group(gle.party)
]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_type(gle.party)]
row.append(gle.remarks)
data.append(row)
return data
def _execute(filters,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = frappe._dict({})
invoice_list = get_invoices(filters, additional_query_columns)
columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts = get_columns(
invoice_list, additional_table_columns)
if not invoice_list:
msgprint(_("No record found"))
return columns, invoice_list
invoice_income_map = get_invoice_income_map(invoice_list)
internal_invoice_map = get_internal_invoice_map(invoice_list)
invoice_income_map, invoice_tax_map = get_invoice_tax_map(
invoice_list, invoice_income_map, income_accounts)
#Cost Center & Warehouse Map
invoice_cc_wh_map = get_invoice_cc_wh_map(invoice_list)
invoice_so_dn_map = get_invoice_so_dn_map(invoice_list)
company_currency = frappe.get_cached_value('Company',
filters.get("company"),
"default_currency")
mode_of_payments = get_mode_of_payments([inv.name for inv in invoice_list])
data = []
for inv in invoice_list:
# invoice details
sales_order = list(
set(invoice_so_dn_map.get(inv.name, {}).get("sales_order", [])))
delivery_note = list(
set(invoice_so_dn_map.get(inv.name, {}).get("delivery_note", [])))
cost_center = list(
set(invoice_cc_wh_map.get(inv.name, {}).get("cost_center", [])))
warehouse = list(
set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
row = {
'invoice': inv.name,
'posting_date': inv.posting_date,
'customer': inv.customer,
'customer_name': inv.customer_name
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: inv.get(col)})
row.update({
'customer_group':
inv.get("customer_group"),
'territory':
inv.get("territory"),
'tax_id':
inv.get("tax_id"),
'receivable_account':
inv.debit_to,
'mode_of_payment':
", ".join(mode_of_payments.get(inv.name, [])),
'project':
inv.project,
'owner':
inv.owner,
'remarks':
inv.remarks,
'sales_order':
", ".join(sales_order),
'delivery_note':
", ".join(delivery_note),
'cost_center':
", ".join(cost_center),
'warehouse':
", ".join(warehouse),
'currency':
company_currency
})
# map income values
base_net_total = 0
for income_acc in income_accounts:
if inv.is_internal_customer and inv.company == inv.represents_company:
income_amount = 0
else:
income_amount = flt(
invoice_income_map.get(inv.name, {}).get(income_acc))
base_net_total += income_amount
row.update({frappe.scrub(income_acc): income_amount})
# Add amount in unrealized account
for account in unrealized_profit_loss_accounts:
row.update({
frappe.scrub(account):
flt(internal_invoice_map.get((inv.name, account)))
})
# net total
row.update({'net_total': base_net_total or inv.base_net_total})
# tax account
total_tax = 0
for tax_acc in tax_accounts:
if tax_acc not in income_accounts:
tax_amount_precision = get_field_precision(
frappe.get_meta("Sales Taxes and Charges").get_field(
"tax_amount"),
currency=company_currency) or 2
tax_amount = flt(
invoice_tax_map.get(inv.name, {}).get(tax_acc),
tax_amount_precision)
total_tax += tax_amount
row.update({frappe.scrub(tax_acc): tax_amount})
# total tax, grand total, outstanding amount & rounded total
row.update({
'tax_total': total_tax,
'grand_total': inv.base_grand_total,
'rounded_total': inv.base_rounded_total,
'outstanding_amount': inv.outstanding_amount
})
data.append(row)
return columns, data
def prepare_row(self,
party_naming_by,
args,
gle,
outstanding_amount,
credit_note_amount,
due_date=None,
paid_amt=None,
payment_term_amount=None,
payment_term=None,
pdc_amount=None,
pdc_details=None):
row = [gle.posting_date, gle.party]
# customer / supplier name
if party_naming_by == "Naming Series":
row += [self.get_party_name(gle.party_type, gle.party)]
if args.get("party_type") == 'Customer':
row += [self.get_customer_contact(gle.party_type, gle.party)]
# get due date
if not due_date:
due_date = self.voucher_details.get(gle.voucher_no,
{}).get("due_date", "")
bill_date = self.voucher_details.get(gle.voucher_no,
{}).get("bill_date", "")
row += [gle.voucher_type, gle.voucher_no, due_date]
# get supplier bill details
if args.get("party_type") == "Supplier":
row += [
self.voucher_details.get(gle.voucher_no,
{}).get("bill_no", ""),
self.voucher_details.get(gle.voucher_no,
{}).get("bill_date", "")
]
# invoiced and paid amounts
invoiced_amount = gle.get(
self.dr_or_cr) if (gle.get(self.dr_or_cr) > 0) else 0
if self.filters.based_on_payment_terms:
row += [payment_term, invoiced_amount]
if payment_term_amount:
invoiced_amount = payment_term_amount
if not payment_term_amount:
paid_amt = invoiced_amount - outstanding_amount - credit_note_amount
row += [
invoiced_amount, paid_amt, credit_note_amount, outstanding_amount
]
# ageing data
if self.filters.ageing_based_on == "Due Date":
entry_date = due_date
elif self.filters.ageing_based_on == "Supplier Invoice Date":
entry_date = bill_date
else:
entry_date = gle.posting_date
row += get_ageing_data(cint(self.filters.range1),
cint(self.filters.range2),
cint(self.filters.range3), self.age_as_on,
entry_date, outstanding_amount)
# issue 6371-Ageing buckets should not have amounts if due date is not reached
if self.filters.ageing_based_on == "Due Date" \
and getdate(due_date) > getdate(self.filters.report_date):
row[-1] = row[-2] = row[-3] = row[-4] = 0
if self.filters.ageing_based_on == "Supplier Invoice Date" \
and getdate(bill_date) > getdate(self.filters.report_date):
row[-1] = row[-2] = row[-3] = row[-4] = 0
if self.filters.get(scrub(args.get("party_type"))):
row.append(gle.account_currency)
else:
row.append(self.company_currency)
remaining_balance = outstanding_amount - flt(pdc_amount)
pdc_details = ", ".join(pdc_details)
row += [pdc_details, pdc_amount, remaining_balance]
if args.get('party_type') == 'Customer':
# customer LPO
row += [self.voucher_details.get(gle.voucher_no, {}).get("po_no")]
# Delivery Note
row += [
self.voucher_details.get(gle.voucher_no,
{}).get("delivery_note")
]
# customer territory / supplier group
if args.get("party_type") == "Customer":
row += [
self.get_territory(gle.party),
self.get_customer_group(gle.party),
self.voucher_details.get(gle.voucher_no,
{}).get("sales_person")
]
if args.get("party_type") == "Supplier":
row += [self.get_supplier_group(gle.party)]
row.append(gle.remarks)
return row
def validate_preferred_email(self):
if self.prefered_contact_email and not self.get(scrub(self.prefered_contact_email)):
frappe.msgprint(_("Please enter {0}").format(self.prefered_contact_email))
def validate_rate_or_discount(self):
for field in ["Rate"]:
if flt(self.get(frappe.scrub(field))) < 0:
throw(_("{0} can not be negative").format(field))
def execute(filters=None):
if not filters:
filters = {}
conditions = get_conditions(filters)
columns = get_column(filters, conditions)
data = []
master = get_master(conditions, filters)
# details = get_details(conditions,filters)
combo_dict = {}
total = 0
for i in master:
row = {}
row["ifw_retailskusuffix"] = i.get("ifw_retailskusuffix")
row["item_name"] = i.get("item_name")
row["item_code"] = i.get("item_code")
row["ifw_duty_rate"] = i.get("ifw_duty_rate")
row["ifw_discontinued"] = i.get("ifw_discontinued")
row["ifw_product_name_ci"] = i.get("ifw_product_name_ci")
row["ifw_item_notes"] = i.get("ifw_item_notes")
row["ifw_item_notes2"] = i.get("ifw_item_notes2")
row["ifw_po_notes"] = i.get("ifw_po_notes")
row["country_of_origin"] = i.get("country_of_origin")
row["customs_tariff_number"] = i.get("customs_tariff_number")
row["supplier_sku"] = i.get("supplier_part_no")
row["supplier_name"] = i.get("supplier")
row["barcode"] = frappe.db.get_value("Item Barcode",
{"parent": i.get("item_code")},
"barcode")
row["asi_item_class"] = i.get("asi_item_class")
row["item_image"] = "<a target=" + str("_blank") + " href = " + str(
i.get("image")) + "> " + str(i.get("image")) + " </a>"
row["rate"] = get_item_details(i.get("item_code"), "Selling")
# row["item_discontinued"] = i.get("disabled")
row["date_last_received"] = get_date_last_received(
i.get("item_code"), i.get("supplier"))
row["item_cost"] = get_item_details(i.get("item_code"), "Buying",
i.get("supplier"))
row["wh_whs"] = get_qty(i.get("item_code"),
"W01-WHS-Active Stock - ICL") or 0
row["wh_dtn"] = get_qty(i.get("item_code"),
"R05-DTN-Active Stock - ICL") or 0
row["wh_queen"] = get_qty(i.get("item_code"),
"R07-Queen-Active Stock - ICL") or 0
row["wh_amb"] = get_qty(i.get("item_code"),
"R06-AMB-Active Stock - ICL") or 0
row["wh_mon"] = get_qty(i.get("item_code"),
"R04-Mon-Active Stock - ICL") or 0
row["wh_vic"] = get_qty(i.get("item_code"),
"R03-Vic-Active Stock - ICL") or 0
row["wh_edm"] = get_qty(i.get("item_code"),
"R02-Edm-Active Stock - ICL") or 0
row["wh_gor"] = get_qty(i.get("item_code"),
"R01-Gor-Active Stock - ICL") or 0
row["total_actual_qty"] = (row.get("wh_whs") or 0) + (
row.get("wh_dtn") or 0) + (row.get("wh_queen") or 0) + (
row.get("wh_amb") or 0) + (row.get("wh_mon") or 0) + (
row.get("wh_vic") or 0) + (row.get("wh_edm")
or 0) + (row.get("wh_gor") or 0)
row["material_request"] = get_open_material_request(i.get("item_code"))
row["tag"] = get_tags(i.get("item_code"))
expected_pos = get_purchase_orders(i.get("item_code"),
i.get("supplier"))
row["expected_pos"] = expected_pos
row["po_eta"] = get_last_purchase_orders(i.get("item_code"),
i.get("supplier"))
ordered_qty = get_open_po_qty(i.get("item_code"), i.get("supplier"))
row["ordered_qty"] = ordered_qty or 0.0
row["last_sold_date"] = get_date_last_sold(i.get("item_code"))
sales_data = get_total_sold(i.get("item_code"))
row["previous_year_sale"] = 0
row["total"] = 0
row["last_twelve_months"] = 0
today = getdate(nowdate())
last_year = today.year - 1
current_year = today.year
last_month = getdate(str(datetime(today.year - 1, 1, 1)))
while last_month <= today:
month = last_month.strftime("%B")
row[frappe.scrub("sold" + month + str(last_month.year))] = 0
last_month = last_month + relativedelta(months=1)
row["sold_last_ten_days"] = 0
row["sold_last_thirty_days"] = 0
row["sold_last_sixty_days"] = 0
for d in sales_data:
posting_date = getdate(d.get("posting_date"))
qty = d.get("qty")
month = posting_date.strftime("%B")
if posting_date.year == last_year:
row["previous_year_sale"] += qty
row[frappe.scrub("sold" + month +
str(posting_date.year))] += qty
elif posting_date.year == current_year:
row["total"] += qty
row[frappe.scrub("sold" + month +
str(posting_date.year))] += qty
# if row.get(frappe.scrub("sold"+month+str(posting_date.year))):
# row[frappe.scrub("sold"+month+str(posting_date.year))] += qty
# row[frappe.scrub("soldjanuary2021")] += qty
last12_month_date = today - relativedelta(years=1)
if posting_date >= last12_month_date:
row["last_twelve_months"] += qty
sold_last_ten_days = today - timedelta(days=10)
sold_last_thirty_days = today - timedelta(days=30)
sold_last_sixty_days = today - timedelta(days=60)
if posting_date >= sold_last_sixty_days:
row["sold_last_sixty_days"] += qty
if posting_date >= sold_last_thirty_days:
row["sold_last_thirty_days"] += qty
if posting_date >= sold_last_ten_days:
row["sold_last_ten_days"] += qty
data.append(row)
return columns, data
def get_columns(invoice_list, additional_table_columns):
"""return columns based on filters"""
columns = [
{
'label': _("Invoice"),
'fieldname': 'invoice',
'fieldtype': 'Link',
'options': 'Sales Invoice',
'width': 120
},
{
'label': _("Posting Date"),
'fieldname': 'posting_date',
'fieldtype': 'Date',
'width': 80
},
{
'label': _("Customer"),
'fieldname': 'customer',
'fieldtype': 'Link',
'options': 'Customer',
'width': 120
},
{
'label': _("Customer Name"),
'fieldname': 'customer_name',
'fieldtype': 'Data',
'width': 120
},
]
if additional_table_columns:
columns += additional_table_columns
columns += [{
'label': _("Customer Group"),
'fieldname': 'customer_group',
'fieldtype': 'Link',
'options': 'Customer Group',
'width': 120
}, {
'label': _("Territory"),
'fieldname': 'territory',
'fieldtype': 'Link',
'options': 'Territory',
'width': 80
}, {
'label': _("Tax Id"),
'fieldname': 'tax_id',
'fieldtype': 'Data',
'width': 120
}, {
'label': _("Receivable Account"),
'fieldname': 'receivable_account',
'fieldtype': 'Link',
'options': 'Account',
'width': 80
}, {
'label': _("Mode Of Payment"),
'fieldname': 'mode_of_payment',
'fieldtype': 'Data',
'width': 120
}, {
'label': _("Project"),
'fieldname': 'project',
'fieldtype': 'Link',
'options': 'Project',
'width': 80
}, {
'label': _("Owner"),
'fieldname': 'owner',
'fieldtype': 'Data',
'width': 150
}, {
'label': _("Remarks"),
'fieldname': 'remarks',
'fieldtype': 'Data',
'width': 150
}, {
'label': _("Sales Order"),
'fieldname': 'sales_order',
'fieldtype': 'Link',
'options': 'Sales Order',
'width': 100
}, {
'label': _("Delivery Note"),
'fieldname': 'delivery_note',
'fieldtype': 'Link',
'options': 'Delivery Note',
'width': 100
}, {
'label': _("Cost Center"),
'fieldname': 'cost_center',
'fieldtype': 'Link',
'options': 'Cost Center',
'width': 100
}, {
'label': _("Warehouse"),
'fieldname': 'warehouse',
'fieldtype': 'Link',
'options': 'Warehouse',
'width': 100
}, {
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
}]
income_accounts = []
tax_accounts = []
income_columns = []
tax_columns = []
unrealized_profit_loss_accounts = []
unrealized_profit_loss_account_columns = []
if invoice_list:
income_accounts = frappe.db.sql_list(
"""select distinct income_account
from `tabSales Invoice Item` where docstatus = 1 and parent in (%s)
order by income_account""" % ', '.join(['%s'] * len(invoice_list)),
tuple([inv.name for inv in invoice_list]))
tax_accounts = frappe.db.sql_list(
"""select distinct account_head
from `tabSales Taxes and Charges` where parenttype = 'Sales Invoice'
and docstatus = 1 and base_tax_amount_after_discount_amount != 0
and parent in (%s) order by account_head""" %
', '.join(['%s'] * len(invoice_list)),
tuple([inv.name for inv in invoice_list]))
unrealized_profit_loss_accounts = frappe.db.sql_list(
"""SELECT distinct unrealized_profit_loss_account
from `tabSales Invoice` where docstatus = 1 and name in (%s)
and ifnull(unrealized_profit_loss_account, '') != ''
order by unrealized_profit_loss_account""" %
', '.join(['%s'] * len(invoice_list)),
tuple([inv.name for inv in invoice_list]))
for account in income_accounts:
income_columns.append({
"label": account,
"fieldname": frappe.scrub(account),
"fieldtype": "Currency",
"options": "currency",
"width": 120
})
for account in tax_accounts:
if account not in income_accounts:
tax_columns.append({
"label": account,
"fieldname": frappe.scrub(account),
"fieldtype": "Currency",
"options": "currency",
"width": 120
})
for account in unrealized_profit_loss_accounts:
unrealized_profit_loss_account_columns.append({
"label":
account,
"fieldname":
frappe.scrub(account),
"fieldtype":
"Currency",
"options":
"currency",
"width":
120
})
net_total_column = [{
"label": _("Net Total"),
"fieldname": "net_total",
"fieldtype": "Currency",
"options": "currency",
"width": 120
}]
total_columns = [{
"label": _("Tax Total"),
"fieldname": "tax_total",
"fieldtype": "Currency",
"options": 'currency',
"width": 120
}, {
"label": _("Grand Total"),
"fieldname": "grand_total",
"fieldtype": "Currency",
"options": 'currency',
"width": 120
}, {
"label": _("Rounded Total"),
"fieldname": "rounded_total",
"fieldtype": "Currency",
"options": 'currency',
"width": 120
}, {
"label": _("Outstanding Amount"),
"fieldname": "outstanding_amount",
"fieldtype": "Currency",
"options": 'currency',
"width": 120
}]
columns = columns + income_columns + unrealized_profit_loss_account_columns + \
net_total_column + tax_columns + total_columns
return columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts
def set_discount_amount(self):
if self.doc.additional_discount_percentage:
self.doc.discount_amount = flt(
flt(self.doc.get(scrub(self.doc.apply_discount_on))) *
self.doc.additional_discount_percentage / 100,
self.doc.precision("discount_amount"))
def set_preferred_email(self): preferred_email_field = frappe.scrub(self.prefered_contact_email) if preferred_email_field: preferred_email = self.get(preferred_email_field) self.prefered_email = preferred_email
def make_boilerplate(dest, app_name):
if not os.path.exists(dest):
print "Destination directory does not exist"
return
# app_name should be in snake_case
app_name = frappe.scrub(app_name)
hooks = frappe._dict()
hooks.app_name = app_name
app_title = hooks.app_name.replace("_", " ").title()
for key in ("App Title (default: {0})".format(app_title),
"App Description", "App Publisher", "App Email",
"App Icon (default 'octicon octicon-file-directory')",
"App Color (default 'grey')",
"App License (default 'MIT')"):
hook_key = key.split(" (")[0].lower().replace(" ", "_")
hook_val = None
while not hook_val:
hook_val = cstr(raw_input(key + ": "))
if not hook_val:
defaults = {
"app_title": app_title,
"app_icon": "octicon octicon-file-directory",
"app_color": "grey",
"app_license": "MIT"
}
if hook_key in defaults:
hook_val = defaults[hook_key]
if hook_key=="app_name" and hook_val.lower().replace(" ", "_") != hook_val:
print "App Name must be all lowercase and without spaces"
hook_val = ""
elif hook_key=="app_title" and not re.match("^(?![\W])[^\d_\s][\w -]+$", hook_val, re.UNICODE):
print "App Title should start with a letter and it can only consist of letters, numbers, spaces and underscores"
hook_val = ""
hooks[hook_key] = hook_val
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, frappe.scrub(hooks.app_title)),
with_init=True)
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "templates"), with_init=True)
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "www"))
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "templates",
"pages"), with_init=True)
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "templates",
"generators"), with_init=True)
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "templates",
"includes"))
frappe.create_folder(os.path.join(dest, hooks.app_name, hooks.app_name, "config"), with_init=True)
with open(os.path.join(dest, hooks.app_name, hooks.app_name, "__init__.py"), "w") as f:
f.write(encode(init_template))
with open(os.path.join(dest, hooks.app_name, "MANIFEST.in"), "w") as f:
f.write(encode(manifest_template.format(**hooks)))
with open(os.path.join(dest, hooks.app_name, ".gitignore"), "w") as f:
f.write(encode(gitignore_template.format(app_name = hooks.app_name)))
with open(os.path.join(dest, hooks.app_name, "setup.py"), "w") as f:
f.write(encode(setup_template.format(**hooks)))
with open(os.path.join(dest, hooks.app_name, "requirements.txt"), "w") as f:
f.write("frappe")
with open(os.path.join(dest, hooks.app_name, "README.md"), "w") as f:
f.write(encode("## {0}\n\n{1}\n\n#### License\n\n{2}".format(hooks.app_title,
hooks.app_description, hooks.app_license)))
with open(os.path.join(dest, hooks.app_name, "license.txt"), "w") as f:
f.write(encode("License: " + hooks.app_license))
with open(os.path.join(dest, hooks.app_name, hooks.app_name, "modules.txt"), "w") as f:
f.write(encode(hooks.app_title))
with open(os.path.join(dest, hooks.app_name, hooks.app_name, "hooks.py"), "w") as f:
f.write(encode(hooks_template.format(**hooks)))
touch_file(os.path.join(dest, hooks.app_name, hooks.app_name, "patches.txt"))
with open(os.path.join(dest, hooks.app_name, hooks.app_name, "config", "desktop.py"), "w") as f:
f.write(encode(desktop_template.format(**hooks)))
with open(os.path.join(dest, hooks.app_name, hooks.app_name, "config", "docs.py"), "w") as f:
f.write(encode(docs_template.format(**hooks)))
print "'{app}' created at {path}".format(app=app_name, path=os.path.join(dest, app_name))
def clear_doctype_map(doctype, name):
cache_key = frappe.scrub(doctype) + '_map'
frappe.cache().hdel(cache_key, name)
def get_column(filters, conditions):
columns = [
{
"label": _("RetailSkuSuffix"),
"fieldname": "ifw_retailskusuffix",
"fieldtype": "Data",
"width": 150,
},
{
"label": "ERPNextItemCode",
"options": "Item",
"fieldname": "item_code",
"fieldtype": "Link",
"width": 150,
"align": "left",
},
{
"label": _("Barcode"),
"fieldname": "barcode",
"fieldtype": "Data",
"width": 150,
"align": "left",
},
{
"label": _("ItemClass"),
"fieldname": "asi_item_class",
"fieldtype": "Data",
"width": 150,
"align": "left",
},
{
"label": _("ItemName"),
"fieldname": "item_name",
"fieldtype": "Data",
"width": 300,
},
{
"label": _("ItemImage"),
"fieldname": "item_image",
"fieldtype": "Data",
"width": 200,
},
{
"label": _("Duty rate"),
"fieldname": "ifw_duty_rate",
"fieldtype": "Float",
"width": 100,
},
{
"label": _("Discontinued"),
"fieldname": "ifw_discontinued",
"fieldtype": "Check",
"width": 100,
},
{
"label": _("ProductNameCI"),
"fieldname": "ifw_product_name_ci",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("Item Notes"),
"fieldname": "ifw_item_notes",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("Item Notes2"),
"fieldname": "ifw_item_notes2",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("PONotes"),
"fieldname": "ifw_po_notes",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("Country of Origin"),
"fieldname": "country_of_origin",
"fieldtype": "Link",
"options": "Country",
"width": 100,
},
{
"label": _("HS Code"),
"fieldname": "customs_tariff_number",
"fieldtype": "Link",
"options": "Customs Tariff Number",
"width": 100,
},
{
"label": _("Tags"),
"fieldname": "tag",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("Rate"),
"fieldname": "rate",
"fieldtype": "Currency",
"width": 100,
},
# {
# "label": _("Discointinued"),
# "fieldname": "item_discontinued",
# "fieldtype": "Boolean",
# "width": 100,
# "default": False,
# },
{
"label": _("ETA"),
"fieldname": "eta",
"fieldtype": "Date",
"width": 100,
},
{
"label": _("DateLastReceived"),
"fieldname": "date_last_received",
"fieldtype": "DateTime",
"width": 200,
},
{
"label": _("Cost"),
"fieldname": "item_cost",
"fieldtype": "Currency",
"width": 100,
},
{
"label": _("Suplier SKU"),
"fieldname": "supplier_sku",
"fieldtype": "Data",
"width": 100,
},
{
"label": _("Supplier Name"),
"fieldname": "supplier_name",
"fieldtype": "Link",
"options": "Supplier",
"width": 200,
},
{
"label": _("W01-WHS-Active Stock - ICL"),
"fieldname": "wh_whs",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R05-DTN-Active Stock - ICL"),
"fieldname": "wh_dtn",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R07-Queen-Active Stock - ICL"),
"fieldname": "wh_queen",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R06-AMB-Active Stock - ICL"),
"fieldname": "wh_amb",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R04-Mon-Active Stock - ICL"),
"fieldname": "wh_mon",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R03-Vic-Active Stock - ICL"),
"fieldname": "wh_vic",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R02-Edm-Active Stock - ICL"),
"fieldname": "wh_edm",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("R01-Gor-Active Stock - ICL"),
"fieldname": "wh_gor",
"fieldtype": "Int",
"width": 200,
},
{
"label": _("TotalQOH"),
"fieldname": "total_actual_qty",
"fieldtype": "Int",
"width": 140,
},
{
"label": _("Material Request"),
"fieldname": "material_request",
"fieldtype": "Data",
"width": 200,
},
{
"label": _("Expected PO Nos"),
"fieldname": "expected_pos",
"fieldtype": "Data",
"width": 240,
},
{
"label": _("ETA date PO"),
"fieldname": "po_eta",
"fieldtype": "Data",
"width": 200,
},
{
"label": _("OrderedQty"),
"fieldname": "ordered_qty",
"fieldtype": "Float",
"width": 120,
},
{
"label": _("PreviousYSale"),
"fieldname": "previous_year_sale",
"fieldtype": "Int",
"width": 140,
},
{
"label": _("CurrentYearSales"),
"fieldname": "total",
"fieldtype": "Int",
"width": 140,
},
{
"label": _("TotalSold12Months"),
"fieldname": "last_twelve_months",
"fieldtype": "Int",
"width": 140,
}
]
today = getdate(nowdate())
last_month = getdate(str(datetime(today.year - 1, today.month, 1)))
while last_month <= today:
month = last_month.strftime("%B")
columns.append({
"label":
_(str(last_month.year) + "_Sold" + month),
"fieldname":
frappe.scrub("sold" + month + str(last_month.year)),
"fieldtype":
"Int",
"width":
140,
})
last_month = last_month + relativedelta(months=1)
columns.extend([{
"label": _("SoldLast10Days"),
"fieldname": "sold_last_ten_days",
"fieldtype": "Int",
"width": 140,
}, {
"label": _("SoldLast30Days"),
"fieldname": "sold_last_thirty_days",
"fieldtype": "Int",
"width": 140,
}, {
"label": _("SoldLast60Days"),
"fieldname": "sold_last_sixty_days",
"fieldtype": "Int",
"width": 140,
"default": False,
}, {
"label": _("DateLastSold"),
"fieldname": "last_sold_date",
"fieldtype": "Data",
"width": 100,
}])
return columns
def get_columns(filters, period_list, partner_doctype):
fieldtype, options = "Currency", "currency"
if filters.get("target_on") == 'Quantity':
fieldtype, options = "Float", ""
columns = [{
"fieldname": frappe.scrub(partner_doctype),
"label": _(partner_doctype),
"fieldtype": "Link",
"options": partner_doctype,
"width": 100
}, {
"fieldname": "item_group",
"label": _("Item Group"),
"fieldtype": "Link",
"options": "Item Group",
"width": 100
}]
for period in period_list:
target_key = 'target_{}'.format(period.key)
variance_key = 'variance_{}'.format(period.key)
columns.extend([{
"fieldname": target_key,
"label": _("Target ({})").format(period.label),
"fieldtype": fieldtype,
"options": options,
"width": 100
}, {
"fieldname": period.key,
"label": _("Achieved ({})").format(period.label),
"fieldtype": fieldtype,
"options": options,
"width": 100
}, {
"fieldname": variance_key,
"label": _("Variance ({})").format(period.label),
"fieldtype": fieldtype,
"options": options,
"width": 100
}])
columns.extend([{
"fieldname": "total_target",
"label": _("Total Target"),
"fieldtype": fieldtype,
"options": options,
"width": 100
}, {
"fieldname": "total_achieved",
"label": _("Total Achieved"),
"fieldtype": fieldtype,
"options": options,
"width": 100
}, {
"fieldname": "total_variance",
"label": _("Total Variance"),
"fieldtype": fieldtype,
"options": options,
"width": 100
}])
return columns
def get_payment_entry(dt, dn, party_amount=None, bank_account=None, bank_amount=None):
doc = frappe.get_doc(dt, dn)
if dt in ("Sales Order", "Purchase Order") and flt(doc.per_billed, 2) > 0:
frappe.throw(_("Can only make payment against unbilled {0}").format(dt))
if dt in ("Sales Invoice", "Sales Order"):
party_type = "Customer"
elif dt in ("Purchase Invoice", "Purchase Order"):
party_type = "Supplier"
elif dt in ("Expense Claim", "Employee Advance"):
party_type = "Employee"
elif dt in ("Fees"):
party_type = "Student"
# party account
if dt == "Sales Invoice":
party_account = get_party_account_based_on_invoice_discounting(dn) or doc.debit_to
elif dt == "Purchase Invoice":
party_account = doc.credit_to
elif dt == "Fees":
party_account = doc.receivable_account
elif dt == "Employee Advance":
party_account = doc.advance_account
elif dt == "Expense Claim":
party_account = doc.payable_account
else:
party_account = get_party_account(party_type, doc.get(party_type.lower()), doc.company)
if dt not in ("Sales Invoice", "Purchase Invoice"):
party_account_currency = get_account_currency(party_account)
else:
party_account_currency = doc.get("party_account_currency") or get_account_currency(party_account)
# payment type
if (dt == "Sales Order" or (dt in ("Sales Invoice", "Fees") and doc.outstanding_amount > 0)) \
or (dt=="Purchase Invoice" and doc.outstanding_amount < 0):
payment_type = "Receive"
else:
payment_type = "Pay"
# amounts
grand_total = outstanding_amount = 0
if party_amount:
grand_total = outstanding_amount = party_amount
elif dt in ("Sales Invoice", "Purchase Invoice"):
if party_account_currency == doc.company_currency:
grand_total = doc.base_rounded_total or doc.base_grand_total
else:
grand_total = doc.rounded_total or doc.grand_total
outstanding_amount = doc.outstanding_amount
elif dt in ("Expense Claim"):
grand_total = doc.total_sanctioned_amount + doc.total_taxes_and_charges
outstanding_amount = doc.grand_total \
- doc.total_amount_reimbursed
elif dt == "Employee Advance":
grand_total = doc.advance_amount
outstanding_amount = flt(doc.advance_amount) - flt(doc.paid_amount)
elif dt == "Fees":
grand_total = doc.grand_total
outstanding_amount = doc.outstanding_amount
else:
if party_account_currency == doc.company_currency:
grand_total = flt(doc.get("base_rounded_total") or doc.base_grand_total)
else:
grand_total = flt(doc.get("rounded_total") or doc.grand_total)
outstanding_amount = grand_total - flt(doc.advance_paid)
# bank or cash
bank = get_default_bank_cash_account(doc.company, "Bank", mode_of_payment=doc.get("mode_of_payment"),
account=bank_account)
if not bank:
bank = get_default_bank_cash_account(doc.company, "Cash", mode_of_payment=doc.get("mode_of_payment"),
account=bank_account)
paid_amount = received_amount = 0
if party_account_currency == bank.account_currency:
paid_amount = received_amount = abs(outstanding_amount)
elif payment_type == "Receive":
paid_amount = abs(outstanding_amount)
if bank_amount:
received_amount = bank_amount
else:
received_amount = paid_amount * doc.conversion_rate
else:
received_amount = abs(outstanding_amount)
if bank_amount:
paid_amount = bank_amount
else:
# if party account currency and bank currency is different then populate paid amount as well
paid_amount = received_amount * doc.conversion_rate
pe = frappe.new_doc("Payment Entry")
pe.payment_type = payment_type
pe.company = doc.company
pe.cost_center = doc.get("cost_center")
pe.posting_date = nowdate()
pe.mode_of_payment = doc.get("mode_of_payment")
pe.party_type = party_type
pe.party = doc.get(scrub(party_type))
pe.contact_person = doc.get("contact_person")
pe.contact_email = doc.get("contact_email")
pe.ensure_supplier_is_not_blocked()
pe.paid_from = party_account if payment_type=="Receive" else bank.account
pe.paid_to = party_account if payment_type=="Pay" else bank.account
pe.paid_from_account_currency = party_account_currency \
if payment_type=="Receive" else bank.account_currency
pe.paid_to_account_currency = party_account_currency if payment_type=="Pay" else bank.account_currency
pe.paid_amount = paid_amount
pe.received_amount = received_amount
pe.letter_head = doc.get("letter_head")
if pe.party_type in ["Customer", "Supplier"]:
bank_account = get_party_bank_account(pe.party_type, pe.party)
pe.set("bank_account", bank_account)
pe.set_bank_account_data()
# only Purchase Invoice can be blocked individually
if doc.doctype == "Purchase Invoice" and doc.invoice_is_blocked():
frappe.msgprint(_('{0} is on hold till {1}').format(doc.name, doc.release_date))
else:
if (doc.doctype in ('Sales Invoice', 'Purchase Invoice')
and frappe.get_value('Payment Terms Template',
{'name': doc.payment_terms_template}, 'allocate_payment_based_on_payment_terms')):
for reference in get_reference_as_per_payment_terms(doc.payment_schedule, dt, dn, doc, grand_total, outstanding_amount):
pe.append('references', reference)
else:
pe.append("references", {
'reference_doctype': dt,
'reference_name': dn,
"bill_no": doc.get("bill_no"),
"due_date": doc.get("due_date"),
'total_amount': grand_total,
'outstanding_amount': outstanding_amount,
'allocated_amount': outstanding_amount
})
pe.setup_party_account_field()
pe.set_missing_values()
if party_account and bank:
pe.set_exchange_rate()
pe.set_amounts()
return pe
def get_columns(self, party_naming_by, args):
columns = [
_(args.get("party_type")) + ":Link/" + args.get("party_type") +
":200"
]
if party_naming_by == "Naming Series":
columns += [args.get("party_type") + " Name::140"]
credit_debit_label = "Credit Note Amt" if args.get(
'party_type') == 'Customer' else "Debit Note Amt"
columns += [{
"label": _("Advance Amount"),
"fieldname": "advance_amount",
"fieldtype": "Currency",
"options": "currency",
"width": 100
}, {
"label": _("Total Invoiced Amt"),
"fieldname": "total_invoiced_amt",
"fieldtype": "Currency",
"options": "currency",
"width": 100
}, {
"label": _("Total Paid Amt"),
"fieldname": "total_paid_amt",
"fieldtype": "Currency",
"options": "currency",
"width": 100
}]
columns += [{
"label": _(credit_debit_label),
"fieldname": scrub(credit_debit_label),
"fieldtype": "Currency",
"options": "currency",
"width": 140
}, {
"label": _("Total Outstanding Amt"),
"fieldname": "total_outstanding_amt",
"fieldtype": "Currency",
"options": "currency",
"width": 160
}, {
"label": _("0-" + str(self.filters.range1)),
"fieldname": scrub("0-" + str(self.filters.range1)),
"fieldtype": "Currency",
"options": "currency",
"width": 160
}, {
"label":
_(str(self.filters.range1) + "-" + str(self.filters.range2)),
"fieldname":
scrub(str(self.filters.range1) + "-" + str(self.filters.range2)),
"fieldtype":
"Currency",
"options":
"currency",
"width":
160
}, {
"label":
_(str(self.filters.range2) + "-" + str(self.filters.range3)),
"fieldname":
scrub(str(self.filters.range2) + "-" + str(self.filters.range3)),
"fieldtype":
"Currency",
"options":
"currency",
"width":
160
}, {
"label": _(str(self.filters.range3) + _("-Above")),
"fieldname": scrub(str(self.filters.range3) + _("-Above")),
"fieldtype": "Currency",
"options": "currency",
"width": 160
}]
if args.get("party_type") == "Customer":
columns += [{
"label": _("Territory"),
"fieldname": "territory",
"fieldtype": "Link",
"options": "Territory",
"width": 80
}, {
"label": _("Customer Group"),
"fieldname": "customer_group",
"fieldtype": "Link",
"options": "Customer Group",
"width": 80
}, {
"label": _("Sales Person"),
"fieldtype": "Data",
"fieldname": "sales_person",
"width": 120,
}]
if args.get("party_type") == "Supplier":
columns += [{
"label": _("Supplier Group"),
"fieldname": "supplier_group",
"fieldtype": "Link",
"options": "Supplier Group",
"width": 80
}]
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Link",
"options": "Currency",
"width": 80
})
return columns
def get_tax_accounts(
item_list,
columns,
company_currency,
doctype="Sales Invoice",
tax_doctype="Sales Taxes and Charges",
):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
tax_amount_precision = (
get_field_precision(
frappe.get_meta(tax_doctype).get_field("tax_amount"), currency=company_currency
)
or 2
)
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d)
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0"
deducted_tax = get_deducted_taxes()
tax_details = frappe.db.sql(
"""
select
name, parent, description, item_wise_tax_detail,
charge_type, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
"""
% (tax_doctype, "%s", ", ".join(["%s"] * len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)),
)
for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_rate, tax_amount = tax_data
else:
tax_rate = tax_data
tax_amount = 0
if charge_type == "Actual" and not tax_rate:
tax_rate = "NA"
item_net_amount = sum(
[flt(d.base_net_amount) for d in item_row_map.get(parent, {}).get(item_code, [])]
)
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = (
flt((tax_amount * d.base_net_amount) / item_net_amount) if item_net_amount else 0
)
if item_tax_amount:
tax_value = flt(item_tax_amount, tax_amount_precision)
tax_value = (
tax_value * -1 if (doctype == "Purchase Invoice" and name in deducted_tax) else tax_value
)
itemised_tax.setdefault(d.name, {})[description] = frappe._dict(
{"tax_rate": tax_rate, "tax_amount": tax_value}
)
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(d.name, {})[description] = frappe._dict(
{
"tax_rate": "NA",
"tax_amount": flt((tax_amount * d.base_net_amount) / d.base_net_total, tax_amount_precision),
}
)
tax_columns.sort()
for desc in tax_columns:
columns.append(
{
"label": _(desc + " Rate"),
"fieldname": frappe.scrub(desc + " Rate"),
"fieldtype": "Float",
"width": 100,
}
)
columns.append(
{
"label": _(desc + " Amount"),
"fieldname": frappe.scrub(desc + " Amount"),
"fieldtype": "Currency",
"options": "currency",
"width": 100,
}
)
columns += [
{
"label": _("Total Tax"),
"fieldname": "total_tax",
"fieldtype": "Currency",
"options": "currency",
"width": 100,
},
{
"label": _("Total"),
"fieldname": "total",
"fieldtype": "Currency",
"options": "currency",
"width": 100,
},
{
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Currency",
"width": 80,
"hidden": 1,
},
]
return itemised_tax, tax_columns
def get_report_module_dotted_path(module, report_name):
return frappe.local.module_app[scrub(module)] + "." + scrub(module) \
+ ".report." + scrub(report_name) + "." + scrub(report_name)