def setup_charts(delete_existing=True):
frappe.local.flags.allow_unverified_charts = True
# delete
if delete_existing:
for company in frappe.get_all("Company"):
if company.name not in ("Wind Power LLC", "Test Company"):
print "deleting {0}".format(company.name)
frappe.delete_doc("Company", company.name)
frappe.db.commit()
print "-"*40
for country in frappe.get_all("Country", fields=["name", "code"]):
charts = get_charts_for_country(country.name)
for i, chart in enumerate(charts):
if (chart != "Standard" or country.name == "United States"):
if not frappe.db.exists("Company", chart):
print chart.encode('utf-8')
company = frappe.new_doc("Company")
company.company_name = chart
company.country = country.name
company.chart_of_accounts = chart
company.abbr = country.code + str(i+1)
company.default_currency = "USD"
company.insert()
frappe.db.commit()
def get_filter_dashboard_data(stats, doctype, filters=[]):
"""get tags info"""
import json
tags = json.loads(stats)
if filters:
filters = json.loads(filters)
stats = {}
columns = frappe.db.get_table_columns(doctype)
for tag in tags:
if not tag["name"] in columns: continue
tagcount = []
if tag["type"] not in ['Date', 'Datetime']:
tagcount = frappe.get_all(doctype,
fields=[tag["name"], "count(*)"],
filters = filters + ["ifnull(`%s`,'')!=''" % tag["name"]],
group_by = tag["name"],
as_list = True)
if tag["type"] not in ['Check','Select','Date','Datetime','Int',
'Float','Currency','Percent'] and tag['name'] not in ['docstatus']:
stats[tag["name"]] = list(tagcount)
if stats[tag["name"]]:
data =["No Data", frappe.get_all(doctype,
fields=[tag["name"], "count(*)"],
filters=filters + ["({0} = '' or {0} is null)".format(tag["name"])],
as_list=True)[0][1]]
if data and data[1]!=0:
stats[tag["name"]].append(data)
else:
stats[tag["name"]] = tagcount
return stats
def notify_unreplied():
"""Sends email notifications if there are unreplied Communications
and `notify_if_unreplied` is set as true."""
for email_account in frappe.get_all("Email Account", "name", filters={"enable_incoming": 1, "notify_if_unreplied": 1}):
email_account = frappe.get_doc("Email Account", email_account.name)
if email_account.append_to:
# get open communications younger than x mins, for given doctype
for comm in frappe.get_all("Communication", "name", filters={
"sent_or_received": "Received",
"reference_doctype": email_account.append_to,
"unread_notification_sent": 0,
"creation": ("<", datetime.now() - timedelta(seconds = (email_account.unreplied_for_mins or 30) * 60)),
"creation": (">", datetime.now() - timedelta(seconds = (email_account.unreplied_for_mins or 30) * 60 * 3))
}):
comm = frappe.get_doc("Communication", comm.name)
if frappe.db.get_value(comm.reference_doctype, comm.reference_name, "status")=="Open":
# if status is still open
frappe.sendmail(recipients=email_account.get_unreplied_notification_emails(),
content=comm.content, subject=comm.subject, doctype= comm.reference_doctype,
name=comm.reference_name, bulk=True)
# update flag
comm.db_set("unread_notification_sent", 1)
def load_address_and_contact(doc, key=None):
"""Loads address list and contact list in `__onload`"""
from frappe.contacts.doctype.address.address import get_address_display
filters = [
["Dynamic Link", "link_doctype", "=", doc.doctype],
["Dynamic Link", "link_name", "=", doc.name],
["Dynamic Link", "parenttype", "=", "Address"],
]
address_list = frappe.get_all("Address", filters=filters, fields=["*"])
address_list = [a.update({"display": get_address_display(a)})
for a in address_list]
address_list = sorted(address_list,
lambda a, b:
(int(a.is_primary_address - b.is_primary_address)) or
(1 if a.modified - b.modified else 0), reverse=True)
doc.set_onload('addr_list', address_list)
contact_list = []
filters = [
["Dynamic Link", "link_doctype", "=", doc.doctype],
["Dynamic Link", "link_name", "=", doc.name],
["Dynamic Link", "parenttype", "=", "Contact"],
]
contact_list = frappe.get_all("Contact", filters=filters, fields=["*"])
contact_list = sorted(contact_list,
lambda a, b:
(int(a.is_primary_contact - b.is_primary_contact)) or
(1 if a.modified - b.modified else 0), reverse=True)
doc.set_onload('contact_list', contact_list)
def execute():
'''
Fields to move from the item to item defaults child table
[ default_warehouse, buying_cost_center, expense_account, selling_cost_center, income_account ]
'''
if not frappe.db.has_column('Item', 'default_warehouse'):
return
frappe.reload_doc('stock', 'doctype', 'item_default')
frappe.reload_doc('stock', 'doctype', 'item')
if frappe.db.a_row_exists('Item Default'): return
companies = frappe.get_all("Company")
if len(companies) == 1:
try:
frappe.db.sql('''
INSERT INTO `tabItem Default`
(name, parent, parenttype, parentfield, idx, company, default_warehouse,
buying_cost_center, selling_cost_center, expense_account, income_account, default_supplier)
SELECT
SUBSTRING(SHA2(name,224), 1, 10) as name, name as parent, 'Item' as parenttype,
'item_defaults' as parentfield, 1 as idx, %s as company, default_warehouse,
buying_cost_center, selling_cost_center, expense_account, income_account, default_supplier
FROM `tabItem`;
''', companies[0].name)
except:
pass
else:
item_details = frappe.get_all("Item", fields=["name", "default_warehouse", "buying_cost_center",
"expense_account", "selling_cost_center", "income_account"], limit=100)
for item in item_details:
item_defaults = []
def insert_into_item_defaults(doc_field_name, doc_field_value, company):
for d in item_defaults:
if d.get("company") == company:
d[doc_field_name] = doc_field_value
return
item_defaults.append({
"company": company,
doc_field_name: doc_field_value
})
for d in [
["default_warehouse", "Warehouse"], ["expense_account", "Account"], ["income_account", "Account"],
["buying_cost_center", "Cost Center"], ["selling_cost_center", "Cost Center"]
]:
if item.get(d[0]):
company = frappe.get_value(d[1], item.get(d[0]), "company", cache=True)
insert_into_item_defaults(d[0], item.get(d[0]), company)
doc = frappe.get_doc("Item", item.name)
doc.extend("item_defaults", item_defaults)
for child_doc in doc.item_defaults:
child_doc.db_insert()
def get_open_count(doctype, name):
'''Get open count for given transactions and filters
:param doctype: Reference DocType
:param name: Reference Name
:param transactions: List of transactions (json/dict)
:param filters: optional filters (json/list)'''
links = frappe.get_meta(doctype).get_links_setup()
# compile all items in a list
items = []
for group in links.transactions:
items.extend(group.get('items'))
out = []
for doctype in items:
filters = get_filters_for(doctype)
fieldname = links.get('non_standard_fieldnames', {}).get(doctype, links.fieldname)
data = {'name': doctype}
if filters:
# get the fieldname for the current document
# we only need open documents related to the current document
filters[fieldname] = name
total = len(frappe.get_all(doctype, fields='name',
filters=filters, limit=6, distinct=True, ignore_ifnull=True))
data['open_count'] = total
total = len(frappe.get_all(doctype, fields='name',
filters={fieldname: name}, limit=10, distinct=True, ignore_ifnull=True))
data['count'] = total
out.append(data)
return out
def execute():
for table in frappe.db.get_tables():
doctype = table[3:]
if frappe.db.exists("DocType", doctype):
fieldnames = [df["fieldname"] for df in
frappe.get_all("DocField", fields=["fieldname"], filters={"parent": doctype})]
custom_fieldnames = [df["fieldname"] for df in
frappe.get_all("Custom Field", fields=["fieldname"], filters={"dt": doctype})]
else:
fieldnames = custom_fieldnames = []
for column in frappe.db.sql("""desc `{0}`""".format(table), as_dict=True):
if column["Type"]=="int(1)":
fieldname = column["Field"]
# only change for defined fields, ignore old fields that don't exist in meta
if not (fieldname in default_fields or fieldname in fieldnames or fieldname in custom_fieldnames):
continue
# set 0
frappe.db.sql("""update `{table}` set `{column}`=0 where `{column}` is null"""\
.format(table=table, column=fieldname))
frappe.db.commit()
# change definition
frappe.db.sql_ddl("""alter table `{table}`
modify `{column}` int(1) not null default {default}"""\
.format(table=table, column=fieldname, default=cint(column["Default"])))
def get_tasks(project, start=0, search=None, item_status=None):
filters = {"project": project}
if search:
filters["subject"] = ("like", "%{0}%".format(search))
# if item_status:
# filters["status"] = item_status
tasks = frappe.get_all("Task", filters=filters,
fields=["name", "subject", "status", "_seen", "_comments", "modified", "description"],
limit_start=start, limit_page_length=10)
for task in tasks:
task.todo = frappe.get_all('ToDo',filters={'reference_name':task.name, 'reference_type':'Task'},
fields=["assigned_by", "owner", "modified", "modified_by"])
if task.todo:
task.todo=task.todo[0]
task.todo.user_image = frappe.db.get_value('User', task.todo.owner, 'user_image')
task.comment_count = len(json.loads(task._comments or "[]"))
task.css_seen = ''
if task._seen:
if frappe.session.user in json.loads(task._seen):
task.css_seen = 'seen'
return tasks
def test_production_plan(self):
pln = create_production_plan(item_code='Test Production Item 1')
self.assertTrue(len(pln.mr_items), 2)
pln.make_material_request()
pln = frappe.get_doc('Production Plan', pln.name)
self.assertTrue(pln.status, 'Material Requested')
material_requests = frappe.get_all('Material Request Item', fields = ['distinct parent'],
filters = {'production_plan': pln.name}, as_list=1)
self.assertTrue(len(material_requests), 2)
pln.make_work_order()
work_orders = frappe.get_all('Work Order', fields = ['name'],
filters = {'production_plan': pln.name}, as_list=1)
self.assertTrue(len(work_orders), len(pln.po_items))
for name in material_requests:
mr = frappe.get_doc('Material Request', name[0])
mr.cancel()
for name in work_orders:
mr = frappe.delete_doc('Work Order', name[0])
pln = frappe.get_doc('Production Plan', pln.name)
pln.cancel()
def setup_charts():
frappe.local.flags.allow_unverified_charts = True
# delete
for company in frappe.get_all("Company"):
if company.name != "Wind Power LLC":
print "deleting {0}".format(company.name)
frappe.delete_doc("Company", company.name)
frappe.db.commit()
for country in frappe.get_all("Country", fields=["name", "code"]):
if country.name in countries:
print "creating for {0}".format(country.name)
charts = get_charts_for_country(country.name)
for i, chart in enumerate(charts):
company = frappe.new_doc("Company")
company.company_name = "{0} - {1}".format(country.name, chart)
company.country = country.name
company.chart_of_accounts = chart
company.abbr = country.code + str(i)
company.default_currency = "USD"
company.insert()
frappe.db.commit()
def migrate_item_variants():
for item in frappe.get_all("Item", filters={"has_variants": 1}):
all_variants = frappe.get_all("Item", filters={"variant_of": item.name}, fields=["name", "description"])
item_attributes = frappe.db.sql("""select distinct item_attribute, item_attribute_value
from `tabItem Variant` where parent=%s""", item.name)
if not item_attributes and not all_variants:
item = frappe.get_doc("Item", item.name)
item.has_variants = 0
item.save()
continue
attribute_value_options = {}
for attribute, value in item_attributes:
attribute_value_options.setdefault(attribute, []).append(value)
possible_combinations = get_possible_combinations(attribute_value_options)
for variant in all_variants:
for combination in possible_combinations:
match = True
for attribute, value in combination.items():
if "{0}: {1}".format(attribute, value) not in variant.description:
match = False
break
if match:
# found the right variant
save_attributes_in_variant(variant, combination)
break
save_attributes_in_template(item, attribute_value_options)
frappe.delete_doc("DocType", "Item Variant")
def get_linked_fields(doctype, without_ignore_user_permissions_enabled=False):
filters=[['fieldtype','=', 'Link'], ['options', '=', doctype]]
if without_ignore_user_permissions_enabled: filters.append(['ignore_user_permissions', '!=', 1])
# find links of parents
links = frappe.get_all("DocField", fields=["parent", "fieldname"], filters=filters, as_list=1)
links+= frappe.get_all("Custom Field", fields=["dt as parent", "fieldname"], filters=filters, as_list=1)
ret = {}
if not links: return ret
links_dict = defaultdict(list)
for doctype, fieldname in links:
links_dict[doctype].append(fieldname)
for doctype_name in links_dict:
ret[doctype_name] = { "fieldname": links_dict.get(doctype_name) }
table_doctypes = frappe.get_all("DocType", filters=[["istable", "=", "1"], ["name", "in", tuple(links_dict)]])
child_filters = [['fieldtype','=', 'Table'], ['options', 'in', tuple(doctype.name for doctype in table_doctypes)]]
if without_ignore_user_permissions_enabled: child_filters.append(['ignore_user_permissions', '!=', 1])
# find out if linked in a child table
for parent, options in frappe.get_all("DocField", fields=["parent", "options"], filters=child_filters, as_list=1):
ret[parent] = { "child_doctype": options, "fieldname": links_dict[options]}
if options in ret: del ret[options]
return ret
def get_columns_from_docfields(self):
"""
get columns from docfields and custom fields
"""
fl = frappe.db.sql("SELECT * FROM tabDocField WHERE parent = %s", self.doctype, as_dict = 1)
precisions = {}
uniques = {}
if not frappe.flags.in_install_db and frappe.flags.in_install != "frappe":
custom_fl = frappe.db.sql("""\
SELECT * FROM `tabCustom Field`
WHERE dt = %s AND docstatus < 2""", (self.doctype,), as_dict=1)
if custom_fl: fl += custom_fl
# get precision from property setters
for ps in frappe.get_all("Property Setter", fields=["field_name", "value"],
filters={"doc_type": self.doctype, "doctype_or_field": "DocField", "property": "precision"}):
precisions[ps.field_name] = ps.value
# apply unique from property setters
for ps in frappe.get_all("Property Setter", fields=["field_name", "value"],
filters={"doc_type": self.doctype, "doctype_or_field": "DocField", "property": "unique"}):
uniques[ps.field_name] = cint(ps.value)
for f in fl:
self.columns[f['fieldname']] = DbColumn(self, f['fieldname'],
f['fieldtype'], f.get('length'), f.get('default'), f.get('search_index'),
f.get('options'), uniques.get(f["fieldname"], f.get('unique')), precisions.get(f['fieldname']) or f.get('precision'))
def execute():
"""Fix relative urls for image src="files/" to src="/files/" in DocTypes with text editor fields"""
doctypes_with_text_fields = frappe.get_all("DocField", fields=["parent", "fieldname"],
filters={"fieldtype": "Text Editor"})
done = []
for opts in doctypes_with_text_fields:
if opts in done:
continue
try:
result = frappe.get_all(opts.parent, fields=["name", opts.fieldname])
except frappe.SQLError, e:
# bypass single tables
continue
for data in result:
old_value = data[opts.fieldname]
if not old_value:
continue
html = scrub_relative_urls(old_value)
if html != old_value:
# print_diff(html, old_value)
frappe.db.set_value(opts.parent, data.name, opts.fieldname, html, update_modified=False)
done.append(opts)
def get_fields(doctype):
table_fields = frappe.get_all("DocField", fields=["options", "fieldname"], filters=[
["DocField", "fieldtype", "=", "Table"],
["DocField", "parent", "=", doctype]
])
table_fields = dict([(t.fieldname, t.options) for t in table_fields])
doc_fields = frappe.get_all("DocField", fields=["label", "fieldname", "fieldtype", "parent", "options", "reqd"], filters=[
["DocField", "parent", "in", list(set(table_fields.values()+[doctype]))],
["DocField", "fieldtype", "not in", display_fieldtypes]
])
res = []
pop = ["fieldname", "options"]
for f in filter(lambda x: x.parent==doctype and x.fieldtype != "Table", doc_fields):
fcopy = f.copy()
fcopy.name = "{parent}.{fieldname}".format(**f)
map(fcopy.pop, pop)
res.append(fcopy)
for b in filter(lambda x: x.parent==doctype and x.fieldtype == "Table", doc_fields):
for f in filter(lambda x: x.parent==b.options, doc_fields):
fcopy = f.copy()
fcopy.name = "{doctype}.{field}.{parent}.{fieldname}".format(
doctype=doctype,
field=b.fieldname,
parent=f.parent,
fieldname=f.fieldname
)
fcopy.parent_label = b.label or b.parent
map(fcopy.pop, pop)
res.append(fcopy)
return res
def collect_project_status():
for data in frappe.get_all("Project Update",
{'date': today(), 'sent': 0}):
replies = frappe.get_all('Communication',
fields=['content', 'text_content', 'sender'],
filters=dict(reference_doctype="Project Update",
reference_name=data.name,
communication_type='Communication',
sent_or_received='Received'),
order_by='creation asc')
for d in replies:
doc = frappe.get_doc("Project Update", data.name)
user_data = frappe.db.get_values("User", {"email": d.sender},
["full_name", "user_image", "name"], as_dict=True)[0]
doc.append("users", {
'user': user_data.name,
'full_name': user_data.full_name,
'image': user_data.user_image,
'project_status': frappe.utils.md_to_html(
EmailReplyParser.parse_reply(d.text_content) or d.content
)
})
doc.save(ignore_permissions=True)
def get_price(item_code, template_item_code, price_list, qty=1):
if price_list:
cart_settings = get_shopping_cart_settings()
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": item_code})
if not price:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": template_item_code})
if price:
pricing_rule = get_pricing_rule_for_item(frappe._dict({
"item_code": item_code,
"qty": qty,
"transaction_type": "selling",
"price_list": price_list,
"customer_group": cart_settings.default_customer_group,
"company": cart_settings.company,
"conversion_rate": 1,
"for_shopping_cart": True
}))
if pricing_rule:
if pricing_rule.pricing_rule_for == "Discount Percentage":
price[0].price_list_rate = flt(price[0].price_list_rate * (1.0 - (pricing_rule.discount_percentage / 100.0)))
if pricing_rule.pricing_rule_for == "Price":
price[0].price_list_rate = pricing_rule.price_list_rate
return price[0]
def execute():
from_date = "2015-05-01"
for doc in frappe.get_all("Journal Entry",
filters={"creation": (">", from_date), "docstatus": "1"}):
# update in gl_entry
update_against_account("Journal Entry", doc.name)
# update in jv
doc = frappe.get_doc("Journal Entry", doc.name)
doc.set_against_account()
doc.db_update()
for doc in frappe.get_all("Sales Invoice",
filters={"creation": (">", from_date), "docstatus": "1"},
fields=["name", "customer"]):
frappe.db.sql("""update `tabGL Entry` set against=%s
where voucher_type='Sales Invoice' and voucher_no=%s
and credit > 0 and ifnull(party, '')=''""",
(doc.customer, doc.name))
for doc in frappe.get_all("Purchase Invoice",
filters={"creation": (">", from_date), "docstatus": "1"},
fields=["name", "supplier"]):
frappe.db.sql("""update `tabGL Entry` set against=%s
where voucher_type='Purchase Invoice' and voucher_no=%s
and debit > 0 and ifnull(party, '')=''""",
(doc.supplier, doc.name))
def execute():
# convert milestones to tasks
frappe.reload_doctype("Project")
frappe.reload_doc("projects", "doctype", "project_task")
frappe.reload_doctype("Task")
frappe.reload_doc("projects", "doctype", "task_depends_on")
for m in frappe.get_all("Project Milestone", "*"):
if (m.milestone and m.milestone_date
and frappe.db.exists("Project", m.parent)):
task = frappe.get_doc({
"doctype": "Task",
"subject": m.milestone,
"expected_start_date": m.milestone_date,
"status": "Open" if m.status=="Pending" else "Closed",
"project": m.parent,
})
task.flags.ignore_mandatory = True
task.insert(ignore_permissions=True)
# remove project milestone
frappe.delete_doc("DocType", "Project Milestone")
# remove calendar events for milestone
for e in frappe.get_all("Event", ["name"], {"ref_type": "Project"}):
frappe.delete_doc("Event", e.name)
def hourly():
""" Expires unused points of the customers s after specified time"""
customers=frappe.get_all("Customer",fields=["customer_id"])
for customer in customers:
customer_id=customer.get("customer_id")
doc=frappe.get_doc("Customer",customer_id)
if doc.get("points_table")!=None:
for raw in doc.get("points_table"):
startdate=data.getdate(raw.purchase_date)
enddate=data.getdate(datetime.datetime.now())
print data.date_diff(enddate,startdate)
rule_engine=frappe.get_all("Rule Engine",fields=['rule_type','points_expiry_duration'], filters={"status":"Active","docstatus":1})
for rule in rule_engine:
if rule.get('points_expiry_duration')=="1 Year":
if data.date_diff(enddate,startdate)==365 or data.date_diff(enddate,startdate)>365:
raw.status="Expired"
print raw.status
doc.save()
if rule.get('points_expiry_duration')=="3 Months":
if data.date_diff(enddate,startdate)==90 or data.date_diff(enddate,startdate)>90:
raw.status="Expired"
print raw.status
doc.save()
if rule.get('points_expiry_duration')=="6 Months":
if data.date_diff(enddate,startdate)==180 or data.date_diff(enddate,startdate)>180:
raw.status="Expired"
print raw.status
doc.save()
def get_roles_and_doctypes():
frappe.only_for("System Manager")
send_translations(frappe.get_lang_dict("doctype", "DocPerm"))
active_domains = frappe.get_active_domains()
doctypes = frappe.get_all("DocType", filters={
"istable": 0,
"name": ("not in", ",".join(not_allowed_in_permission_manager)),
}, or_filters={
"ifnull(restrict_to_domain, '')": "",
"restrict_to_domain": ("in", active_domains)
}, fields=["name"])
roles = frappe.get_all("Role", filters={
"name": ("not in", "Administrator"),
"disabled": 0,
}, or_filters={
"ifnull(restrict_to_domain, '')": "",
"restrict_to_domain": ("in", active_domains)
}, fields=["name"])
doctypes_list = [ {"label":_(d.get("name")), "value":d.get("name")} for d in doctypes]
roles_list = [ {"label":_(d.get("name")), "value":d.get("name")} for d in roles]
return {
"doctypes": sorted(doctypes_list, key=lambda d: d['label']),
"roles": sorted(roles_list, key=lambda d: d['label'])
}
def remove_like(doctype, name):
"""Remove previous Like"""
# remove Comment
frappe.delete_doc(
"Comment",
[
c.name
for c in frappe.get_all(
"Comment",
filters={
"comment_doctype": doctype,
"comment_docname": name,
"comment_by": frappe.session.user,
"comment_type": "Like",
},
)
],
ignore_permissions=True,
)
# remove Feed
frappe.delete_doc(
"Feed",
[
c.name
for c in frappe.get_all(
"Feed",
filters={"doc_type": doctype, "doc_name": name, "owner": frappe.session.user, "feed_type": "Like"},
)
],
ignore_permissions=True,
)
def get_dynamic_linked_fields(doctype, without_ignore_user_permissions_enabled=False):
ret = {}
filters=[['fieldtype','=', 'Dynamic Link']]
if without_ignore_user_permissions_enabled: filters.append(['ignore_user_permissions', '!=', 1])
# find dynamic links of parents
links = frappe.get_all("DocField", fields=["parent as doctype", "fieldname", "options as doctype_fieldname"], filters=filters)
links+= frappe.get_all("Custom Field", fields=["dt as doctype", "fieldname", "options as doctype_fieldname"], filters=filters)
for df in links:
if is_single(df.doctype): continue
# optimized to get both link exists and parenttype
possible_link = frappe.db.sql("""select distinct `{doctype_fieldname}`, parenttype
from `tab{doctype}` where `{doctype_fieldname}`=%s""".format(**df), doctype, as_dict=True)
if not possible_link: continue
for d in possible_link:
# is child
if d.parenttype:
ret[d.parenttype] = {
"child_doctype": df.doctype,
"fieldname": [df.fieldname],
"doctype_fieldname": df.doctype_fieldname
}
else:
ret[df.doctype] = {
"fieldname": [df.fieldname],
"doctype_fieldname": df.doctype_fieldname
}
return ret
def monthly_auto_repeat(self, doctype, docname, start_date, end_date):
def get_months(start, end):
diff = (12 * end.year + end.month) - (12 * start.year + start.month)
return diff + 1
doc = make_auto_repeat(
reference_doctype=doctype, frequency='Monthly', reference_document=docname, start_date=start_date,
end_date=end_date)
disable_auto_repeat(doc)
for data in get_auto_repeat_entries(today()):
create_repeated_entries(data)
docnames = frappe.get_all(doc.reference_doctype, {'auto_repeat': doc.name})
self.assertEqual(len(docnames), 1)
doc = frappe.get_doc('Auto Repeat', doc.name)
doc.db_set('disabled', 0)
months = get_months(getdate(start_date), getdate(today()))
for data in get_auto_repeat_entries(today()):
create_repeated_entries(data)
docnames = frappe.get_all(doc.reference_doctype, {'auto_repeat': doc.name})
self.assertEqual(len(docnames), months)
def _get_linked_doctypes(doctype, without_ignore_user_permissions_enabled=False):
ret = {}
# find fields where this doctype is linked
ret.update(get_linked_fields(doctype, without_ignore_user_permissions_enabled))
ret.update(get_dynamic_linked_fields(doctype, without_ignore_user_permissions_enabled))
filters=[['fieldtype','=','Table'], ['options', '=', doctype]]
if without_ignore_user_permissions_enabled: filters.append(['ignore_user_permissions', '!=', 1])
# find links of parents
links = frappe.get_all("DocField", fields=["parent as dt"], filters=filters)
links+= frappe.get_all("Custom Field", fields=["dt"], filters=filters)
for dt, in links:
if dt in ret: continue
ret[dt] = {"get_parent": True}
for dt in list(ret):
try:
doctype_module = load_doctype_module(dt)
except (ImportError, KeyError):
# in case of Custom DocType
# or in case of module rename eg. (Schools -> Education)
continue
if getattr(doctype_module, "exclude_from_linked_with", False):
del ret[dt]
return ret
def execute():
"""
Structure History:
1. Item and Item Variant
2. Item, Variant Attribute, Manage Variants and Manage Variant Items
3. Item, Item Variant Attribute, Item Attribute and Item Attribute Type (latest)
"""
rename_and_reload_doctypes()
variant_templates = frappe.get_all("Item", filters={"has_variants": 1}, limit_page_length=1)
if not variant_templates:
# database does not have items that have variants
# so no point in running the patch
return
variant_attributes = frappe.get_all("Item Variant Attribute", fields=["*"], limit_page_length=1)
if variant_attributes:
# manage variant patch is already applied
migrate_manage_variants()
else:
# old structure based on "Item Variant" table
try:
migrate_item_variants()
except MySQLdb.ProgrammingError:
print("`tabItem Variant` not found")
def make_budget(budget_against=None, cost_center=None):
if budget_against == "Project":
budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", "_Test Project/_Test Fiscal Year 2013%")})
else:
cost_center_name = "{0}%".format(cost_center or "_Test Cost Center - _TC/_Test Fiscal Year 2013")
budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", cost_center_name)})
for d in budget_list:
frappe.db.sql("delete from `tabBudget` where name = %(name)s", d)
frappe.db.sql("delete from `tabBudget Account` where parent = %(name)s", d)
budget = frappe.new_doc("Budget")
if budget_against == "Project":
budget.project = "_Test Project"
else:
budget.cost_center =cost_center or "_Test Cost Center - _TC"
budget.fiscal_year = "_Test Fiscal Year 2013"
budget.monthly_distribution = "_Test Distribution"
budget.company = "_Test Company"
budget.action_if_annual_budget_exceeded = "Stop"
budget.action_if_accumulated_monthly_budget_exceeded = "Ignore"
budget.budget_against = budget_against
budget.append("accounts", {
"account": "_Test Account Cost for Goods Sold - _TC",
"budget_amount": 100000
})
budget.insert()
budget.submit()
return budget
def execute():
frappe.reload_doc("core", "doctype", "docperm")
frappe.reload_doc("core", "doctype", "docshare")
frappe.reload_doc('email', 'doctype', 'email_account')
# default share to all writes
frappe.db.sql("""update tabDocPerm set `share`=1 where ifnull(`write`,0)=1 and ifnull(`permlevel`,0)=0""")
# every user must have access to his / her own detail
users = frappe.get_all("User", filters={"user_type": "System User"})
usernames = [user.name for user in users]
for user in usernames:
frappe.share.add("User", user, user, share=1)
# move event user to shared
if frappe.db.exists("DocType", "Event User"):
for event in frappe.get_all("Event User", fields=["parent", "person"]):
if event.person in usernames:
if not frappe.db.exists("Event", event.parent):
frappe.db.sql("delete from `tabEvent User` where parent = %s",event.parent)
else:
frappe.share.add("Event", event.parent, event.person, write=1)
frappe.delete_doc("DocType", "Event User")
# move note user to shared
if frappe.db.exists("DocType", "Note User"):
for note in frappe.get_all("Note User", fields=["parent", "user", "permission"]):
perm = {"read": 1} if note.permission=="Read" else {"write": 1}
if note.user in usernames:
frappe.share.add("Note", note.parent, note.user, **perm)
frappe.delete_doc("DocType", "Note User")
def get_price(item_code, price_list, customer_group, company, qty=1):
template_item_code = frappe.db.get_value("Item", item_code, "variant_of")
if price_list:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": item_code})
if template_item_code and not price:
price = frappe.get_all("Item Price", fields=["price_list_rate", "currency"],
filters={"price_list": price_list, "item_code": template_item_code})
if price:
pricing_rule = get_pricing_rule_for_item(frappe._dict({
"item_code": item_code,
"qty": qty,
"transaction_type": "selling",
"price_list": price_list,
"customer_group": customer_group,
"company": company,
"conversion_rate": 1,
"for_shopping_cart": True,
"currency": frappe.db.get_value("Price List", price_list, "currency")
}))
if pricing_rule:
if pricing_rule.pricing_rule_for == "Discount Percentage":
price[0].price_list_rate = flt(price[0].price_list_rate * (1.0 - (flt(pricing_rule.discount_percentage) / 100.0)))
if pricing_rule.pricing_rule_for == "Rate":
price[0].price_list_rate = pricing_rule.price_list_rate
price_obj = price[0]
if price_obj:
price_obj["formatted_price"] = fmt_money(price_obj["price_list_rate"], currency=price_obj["currency"])
price_obj["currency_symbol"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price_obj.currency, "symbol", cache=True) or price_obj.currency) \
or ""
uom_conversion_factor = frappe.db.sql("""select C.conversion_factor
from `tabUOM Conversion Detail` C
inner join `tabItem` I on C.parent = I.name and C.uom = I.sales_uom
where I.name = %s""", item_code)
uom_conversion_factor = uom_conversion_factor[0][0] if uom_conversion_factor else 1
price_obj["formatted_price_sales_uom"] = fmt_money(price_obj["price_list_rate"] * uom_conversion_factor, currency=price_obj["currency"])
if not price_obj["price_list_rate"]:
price_obj["price_list_rate"] = 0
if not price_obj["currency"]:
price_obj["currency"] = ""
if not price_obj["formatted_price"]:
price_obj["formatted_price"] = ""
return price_obj
def work():
frappe.set_user(frappe.db.get_global('demo_manufacturing_user'))
from erpnext.projects.doctype.timesheet.timesheet import OverlapError
ppt = frappe.get_doc("Production Planning Tool", "Production Planning Tool")
ppt.company = erpnext.get_default_company()
ppt.use_multi_level_bom = 1
ppt.get_items_from = "Sales Order"
ppt.purchase_request_for_warehouse = "Stores - WPL"
ppt.run_method("get_open_sales_orders")
ppt.run_method("get_items")
ppt.run_method("raise_production_orders")
ppt.run_method("raise_material_requests")
frappe.db.commit()
# submit production orders
for pro in frappe.db.get_values("Production Order", {"docstatus": 0}, "name"):
b = frappe.get_doc("Production Order", pro[0])
b.wip_warehouse = "Work in Progress - WPL"
b.submit()
frappe.db.commit()
# submit material requests
for pro in frappe.db.get_values("Material Request", {"docstatus": 0}, "name"):
b = frappe.get_doc("Material Request", pro[0])
b.submit()
frappe.db.commit()
# stores -> wip
if random.random() < 0.3:
for pro in query_report.run("Open Production Orders")["result"][:how_many("Stock Entry for WIP")]:
make_stock_entry_from_pro(pro[0], "Material Transfer for Manufacture")
# wip -> fg
if random.random() < 0.3:
for pro in query_report.run("Production Orders in Progress")["result"][:how_many("Stock Entry for FG")]:
make_stock_entry_from_pro(pro[0], "Manufacture")
for bom in frappe.get_all('BOM', fields=['item'], filters = {'with_operations': 1}):
pro_order = make_prod_order_test_record(item=bom.item, qty=2,
source_warehouse="Stores - WPL", wip_warehouse = "Work in Progress - WPL",
fg_warehouse = "Stores - WPL", company = erpnext.get_default_company(),
stock_uom = frappe.db.get_value('Item', bom.item, 'stock_uom'),
planned_start_date = frappe.flags.current_date)
# submit time logs
for timesheet in frappe.get_all("Timesheet", ["name"], {"docstatus": 0,
"production_order": ("!=", ""), "to_time": ("<", frappe.flags.current_date)}):
timesheet = frappe.get_doc("Timesheet", timesheet.name)
try:
timesheet.submit()
frappe.db.commit()
except OverlapError:
pass
except WorkstationHolidayError:
pass
def install_defaults(args=None):
records = [
# Price Lists
{ "doctype": "Price List", "price_list_name": _("Standard Buying"), "enabled": 1, "buying": 1, "selling": 0, "currency": args.currency },
{ "doctype": "Price List", "price_list_name": _("Standard Selling"), "enabled": 1, "buying": 0, "selling": 1, "currency": args.currency },
]
make_records(records)
# enable default currency
frappe.db.set_value("Currency", args.get("currency"), "enabled", 1)
global_defaults = frappe.get_doc("Global Defaults", "Global Defaults")
current_fiscal_year = frappe.get_all("Fiscal Year")[0]
global_defaults.update({
'current_fiscal_year': current_fiscal_year.name,
'default_currency': args.get('currency'),
'default_company':args.get('company_name') ,
"country": args.get("country"),
})
global_defaults.save()
system_settings = frappe.get_doc("System Settings")
system_settings.email_footer_address = args.get("company_name")
system_settings.save()
domain_settings = frappe.get_single('Domain Settings')
domain_settings.set_active_domains(args.get('domains'))
stock_settings = frappe.get_doc("Stock Settings")
stock_settings.item_naming_by = "Item Code"
stock_settings.valuation_method = "FIFO"
stock_settings.default_warehouse = frappe.db.get_value('Warehouse', {'warehouse_name': _('Stores')})
stock_settings.stock_uom = _("Nos")
stock_settings.auto_indent = 1
stock_settings.auto_insert_price_list_rate_if_missing = 1
stock_settings.automatically_set_serial_nos_based_on_fifo = 1
stock_settings.set_qty_in_transactions_based_on_serial_no_input = 1
stock_settings.save()
if args.bank_account:
company_name = args.company_name
bank_account_group = frappe.db.get_value("Account",
{"account_type": "Bank", "is_group": 1, "root_type": "Asset",
"company": company_name})
if bank_account_group:
bank_account = frappe.get_doc({
"doctype": "Account",
'account_name': args.bank_account,
'parent_account': bank_account_group,
'is_group':0,
'company': company_name,
"account_type": "Bank",
})
try:
doc = bank_account.insert()
frappe.db.set_value("Company", args.company_name, "default_bank_account", bank_account.name, update_modified=False)
return doc
except RootNotEditable:
frappe.throw(_("Bank account cannot be named as {0}").format(args.bank_account))
except frappe.DuplicateEntryError:
# bank account same as a CoA entry
pass
# Now, with fixtures out of the way, onto concrete stuff
records = [
# Shopping cart: needs price lists
{
"doctype": "Shopping Cart Settings",
"enabled": 1,
'company': args.company_name,
# uh oh
'price_list': frappe.db.get_value("Price List", {"selling": 1}),
'default_customer_group': _("Individual"),
'quotation_series': "QTN-",
},
]
make_records(records)
def fetch_transactions(fetch_all_check):
list_of_transactions = []
nat_list_transaction_id = []
today = datetime.today()
midnight = datetime.combine(today, datetime.min.time())
# # 1. get all AR City Ledger Invoice - Change
# acli_list = frappe.get_all('AR City Ledger Invoice',
# filters={'creation': ['>=', midnight], 'change_amount': ['>', 0],
# 'change_journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'rounded_change_amount', 'customer_id'])
# # 2. get all AR City Ledger Invoice Payment
# aclip_list = frappe.get_all('AR City Ledger Invoice Payments',
# filters={'creation': ['>=', midnight], 'journal_entry_id': ['=', '']},
# fields=['name', 'parent', 'creation', 'mode_of_payment', 'payment_amount'])
# # 3. get all Hotel Bill Breakdown - Item and Tax
# hbb_list = frappe.get_all('Hotel Bill Breakdown',
# filters={'creation': ['>=', midnight], 'journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'parent', 'breakdown_net_total', 'breakdown_grand_total',
# 'breakdown_description', 'breakdown_account', 'breakdown_account_against'])
# # 4. get all Hotel Bill Refund
# hbr_list = frappe.get_all('Hotel Bill Refund',
# filters={'creation': ['>=', midnight], 'journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'parent', 'refund_amount', 'refund_description', 'account',
# 'account_against'])
# # 5. get all Hotel Bill Payments
# hbp_list = frappe.get_all('Hotel Bill Payments',
# filters={'creation': ['>=', midnight], 'journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'parent', 'payment_amount', 'mode_of_payment'])
# # 6. get all Hotel Bill - Use Deposit
# hb_depo_list = frappe.get_all('Hotel Bill',
# filters={'creation': ['>=', midnight], 'is_paid': ['=', 1], 'use_deposit': ['=', 1],
# 'use_deposit_journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'reservation_id', 'bill_deposit_amount', 'customer_id'])
# # 7. get all Hotel BIll - Cash Change
# hb_change_list = frappe.get_all('Hotel Bill',
# filters={'creation': ['>=', midnight], 'is_paid': ['=', 1],
# 'bill_change_amount': ['>', 0], 'change_journal_entry_id': ['=', '']},
# fields=['name', 'creation', 'reservation_id', 'bill_rounded_change_amount', 'customer_id'])
# 8. get all Folio Transaction
if fetch_all_check == 0:
ft_list = frappe.get_all('Folio Transaction', filters={'journal_entry_id': ['=', '']})
else:
ft_list = frappe.get_all('Folio Transaction',
filters={'creation': ['>=', midnight], 'journal_entry_id': ['=', '']})
# 9. get all Night Audit Transaction
if fetch_all_check == 0:
nat_list = frappe.get_all('Night Audit Transaction', fields=['transaction_id'])
else:
nat_list = frappe.get_all('Night Audit Transaction', filters={'creation': ['>=', midnight]},
fields=['transaction_id'])
for nat_item in nat_list:
nat_list_transaction_id.append(nat_item.transaction_id)
# FOLIO TRANSACTIONS - START
if len(ft_list) > 0:
for ft_item in ft_list:
if ft_item.name not in nat_list_transaction_id:
doc_ft = frappe.get_doc('Folio Transaction', ft_item.name)
customer_name = frappe.get_doc('Customer', frappe.get_doc('Reservation', frappe.db.get_value('Folio', {
'name': doc_ft.parent}, "reservation_id")).customer_id).name
if ' - Additional Charge ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Additional Charge'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = doc_ft.remark
doc_nat.remark = doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Early Check In Room ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Room Stay Early Checkin'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = "JE " + doc_ft.name
doc_nat.remark = doc_ft.name + "-" + doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Late Check Out Room ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Room Stay Late Checkout'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = "JE " + doc_ft.name
doc_nat.remark = doc_ft.name + "-" + doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Deposit ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Reservation Deposit'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = "JE " + doc_ft.name
doc_nat.remark = doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Room Bill Payment: ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Room Bill Payment'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = "JE " + doc_ft.name
doc_nat.remark = doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Change from ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Room Bill Payment Change'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = "JE " + doc_ft.name
doc_nat.remark = doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
elif 'Cancellation Fee - Reservation: ' in doc_ft.remark:
doc_nat = frappe.new_doc('Night Audit Transaction')
doc_nat.transaction_type = 'Folio - Reservation Cancellation Fee'
doc_nat.transaction_id = ft_item.name
doc_nat.reservation_id = frappe.db.get_value('Folio', {'name': doc_ft.parent}, "reservation_id")
doc_nat.folio_id = doc_ft.parent
doc_nat.amount = doc_ft.amount
doc_nat.title = doc_ft.name + ' ' + doc_ft.remark
doc_nat.remark = doc_ft.remark
doc_nat.debit_account = doc_ft.account_id
doc_nat.credit_account = doc_ft.against_account_id
doc_nat.customer_id = customer_name
list_of_transactions.append(doc_nat)
# FOLIO TRANSACTIONS - END
# # HOTEL BILL - START
# if len(hbb_list) > 0:
# for hbb_item in hbb_list:
# doc_hbb = frappe.get_doc('Hotel Bill Breakdown', hbb_item.name)
# if doc_hbb.is_excluded == 0 and doc_hbb.is_tax_item == 0 and doc_hbb.is_folio_trx_pairing == 0 and doc_hbb.breakdown_tax_id is not None:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill Breakdown - Item'
# doc_nat.transaction_id = hbb_item.name
# doc_nat.reservation_id = frappe.db.get_value('Hotel Bill', {'name': hbb_item.parent}, "reservation_id")
# doc_nat.amount = hbb_item.breakdown_net_total
# doc_nat.title = 'Hotel Bill Breakdown: ' + hbb_item.breakdown_description + ' - ' + hbb_item.name
# doc_nat.remark = doc_nat.title + '. @ ' + str(hbb_item.creation)
# doc_nat.debit_account = hbb_item.breakdown_account_against
# doc_nat.credit_account = hbb_item.breakdown_account
# doc_nat.customer_id = frappe.db.get_value('Hotel Bill', {'name': hbb_item.parent}, "customer_id")
# list_of_transactions.append(doc_nat)
# elif doc_hbb.is_excluded == 0 and doc_hbb.is_tax_item == 1:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill Breakdown - Tax'
# doc_nat.transaction_id = hbb_item.name
# doc_nat.reservation_id = frappe.db.get_value('Hotel Bill', {'name': hbb_item.parent}, "reservation_id")
# doc_nat.amount = hbb_item.breakdown_grand_total
# doc_nat.title = 'Hotel Bill Breakdown Tax: ' + hbb_item.breakdown_description + ' - ' + hbb_item.name
# doc_nat.remark = doc_nat.title + '. @ ' + str(hbb_item.creation)
# doc_nat.debit_account = hbb_item.breakdown_account_against
# doc_nat.credit_account = hbb_item.breakdown_account
# doc_nat.customer_id = frappe.db.get_value('Hotel Bill', {'name': hbb_item.parent}, "customer_id")
# list_of_transactions.append(doc_nat)
#
# if len(hbr_list) > 0:
# for hbr_item in hbr_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill Refund'
# doc_nat.transaction_id = hbr_item.name
# doc_nat.reservation_id = frappe.db.get_value('Hotel Bill', {'name': hbr_item.parent}, "reservation_id")
# doc_nat.amount = hbr_item.refund_amount
# doc_nat.title = 'Hotel Bill Refund: ' + hbr_item.refund_description + ' - ' + hbr_item.name
# doc_nat.remark = doc_nat.title + '. @ ' + str(hbr_item.creation)
# doc_nat.debit_account = hbr_item.account_against
# doc_nat.credit_account = hbr_item.account
# doc_nat.customer_id = frappe.db.get_value('Hotel Bill', {'name': hbb_item.parent}, "customer_id")
# list_of_transactions.append(doc_nat)
#
# if len(hbp_list) > 0:
# for hbp_item in hbp_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill Payments'
# doc_nat.transaction_id = hbp_item.name
# doc_nat.reservation_id = frappe.db.get_value('Hotel Bill', {'name': hbp_item.parent}, "reservation_id")
# doc_nat.amount = hbp_item.payment_amount
# doc_nat.title = 'Hotel Bill Payment (' + str(hbp_item.mode_of_payment) + '): ' + str(
# hbp_item.parent) + ' - ' + hbp_item.name
# doc_nat.remark = doc_nat.title + ' - @' + str(hbp_item.creation)
# doc_nat.debit_account = get_mode_of_payment_account(hbp_item.mode_of_payment,
# frappe.get_doc("Global Defaults").default_company)
# doc_nat.credit_account = frappe.db.get_list('Account', filters={'account_number': '4210.001'})[0].name
# doc_nat.customer_id = frappe.db.get_value('Hotel Bill', {'name': hbp_item.parent}, "customer_id")
# list_of_transactions.append(doc_nat)
#
# if len(hb_depo_list) > 0:
# for hb_depo_item in hb_depo_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill - Use Deposit'
# doc_nat.transaction_id = hb_depo_item.name
# doc_nat.reservation_id = hb_depo_item.reservation_id
# doc_nat.amount = hb_depo_item.bill_deposit_amount
# doc_nat.title = 'Hotel Bill Payment (Deposit): ' + hb_depo_item.name
# doc_nat.remark = doc_nat.title + ' - @' + str(hb_depo_item.creation)
# doc_nat.debit_account = frappe.db.get_list('Account', filters={'account_number': '2110.005'})[0].name
# doc_nat.credit_account = frappe.db.get_list('Account', filters={'account_number': '1133.002'})[0].name
# doc_nat.customer_id = hb_depo_item.customer_id
# list_of_transactions.append(doc_nat)
#
# if len(hb_change_list) > 0:
# for hb_change_item in hb_change_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'Hotel Bill - Change'
# doc_nat.transaction_id = hb_change_item.name
# doc_nat.reservation_id = hb_change_item.reservation_id
# doc_nat.amount = hb_change_item.bill_rounded_change_amount
# doc_nat.title = 'Hotel Bill Change: ' + hb_change_item.name
# doc_nat.remark = doc_nat.title + ' - @' + str(hb_change_item.creation)
# doc_nat.debit_account = frappe.db.get_list('Account', filters={'account_number': '1133.002'})[0].name
# doc_nat.credit_account = frappe.db.get_list('Account', filters={'account_number': '1111.003'})[0].name
# doc_nat.customer_id = hb_change_item.customer_id
# list_of_transactions.append(doc_nat)
# # HOTEL BILL - END
#
# # AR CITY LEDGER - START
# if len(acli_list) > 0:
# for acli_item in acli_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'AR City Ledger Invoice - Change'
# doc_nat.transaction_id = acli_item.name
# doc_nat.amount = acli_item.rounded_change_amount
# doc_nat.title = 'AR City Ledger Change: ' + acli_item.name
# doc_nat.remark = doc_nat.title + ' -@' + str(acli_item.creation)
# doc_nat.debit_account = frappe.db.get_list('Account', filters={'account_number': '1133.001'})[0].name
# doc_nat.credit_account = frappe.db.get_list('Account', filters={'account_number': '1111.003'})[0].name
# doc_nat.customer_id = acli_item.customer_id
# list_of_transactions.append(doc_nat)
#
# if len(aclip_list) > 0:
# for aclip_item in aclip_list:
# doc_nat = frappe.new_doc('Night Audit Transaction')
# doc_nat.transaction_type = 'AR City Ledger Invoice Payments'
# doc_nat.transaction_id = aclip_item.name
# doc_nat.amount = aclip_item.payment_amount
# doc_nat.title = 'AR City Ledger Payment (' + str(aclip_item.mode_of_payment) + '): ' + aclip_item.name
# doc_nat.remark = doc_nat.title + ' -@' + str(aclip_item.creation)
# doc_nat.debit_account = get_mode_of_payment_account(aclip_item.mode_of_payment,
# frappe.get_doc("Global Defaults").default_company)
# doc_nat.credit_account = frappe.db.get_list('Account', filters={'account_number': '1133.001'})[0].name
# doc_nat.customer_id = frappe.get_doc('AR City Ledger Invoice', aclip_item.parent).customer_id
# list_of_transactions.append(doc_nat)
# # AR CITY LEDGER - END
return list_of_transactions
def get_cashiers(doctype, txt, searchfield, start, page_len, filters):
cashiers_list = frappe.get_all("POS Profile User",
filters=filters,
fields=['user'],
as_list=1)
return [c for c in cashiers_list]
def onload(self):
job_offer = frappe.get_all("Job Offer",
filters={"job_applicant": self.name})
if job_offer:
self.get("__onload").job_offer = job_offer[0].name
def submit_section(user_feedback_name, question_response_pairs, scores,
current_section, current_section_name):
current_section = int(current_section)
question_response_pairs = json.loads(question_response_pairs)
scores = json.loads(scores)
for i, qtn in enumerate(question_response_pairs):
resp = question_response_pairs[qtn]
existing_qp_pair = frappe.get_all(
"User Response",
filters=[["User Response", "parent", "=", user_feedback_name],
["User Response", "question", "=", qtn]],
fields=["name", "question", "response"])
if existing_qp_pair[0][
"response"] == "" or existing_qp_pair[0]["response"] is None:
for r in resp:
r["version"] = 0
resp_link = frappe.get_doc({
"doctype": "Response Value Link",
"response_values": resp,
"latest_version": 0,
"score": scores[i]
}).insert()
qp_pair_doc = frappe.get_doc("User Response",
existing_qp_pair[0]["name"])
qp_pair_doc.response = resp_link.name
qp_pair_doc.save()
action = "Created Responses"
else:
resp_link = frappe.get_doc("Response Value Link",
existing_qp_pair[0]["response"])
current_version = resp_link.latest_version
for r in resp:
r["version"] = current_version + 1
resp_link.append('response_values', r)
resp_link.latest_version = current_version + 1
resp_link.score = scores[i]
resp_link.save()
action = "Updated Responses"
next_section, end_survey = calculate_next_section(current_section,
current_section_name,
question_response_pairs)
user_feedback_doc = frappe.get_doc("User Feedback", user_feedback_name)
if current_section == 0:
user_feedback_doc.workflow_state = "Partially Completed"
if end_survey:
user_feedback_doc.workflow_state = "Completed"
survey_doc = frappe.get_doc("Survey", user_feedback_doc.survey)
if survey_doc.send_email_alert:
recepients = [r.email for r in survey_doc.email_recepients]
email_args = {
"recipients":
recepients,
"message":
"""
A response for the {0} has been recieved from {1}. Please go to the following link for results.
https://{2}/desk#query-report/User Latest Feedback
""".format(survey_doc.name, frappe.session.user,
survey_doc.email_message_callback_url),
"subject":
'Survey {0} Response from {1}'.format(survey_doc.name,
frappe.session.user),
"reference_doctype":
"User Feedback",
"reference_name":
user_feedback_name
}
enqueue(method=frappe.sendmail,
queue='short',
timeout=300,
**email_args)
user_feedback_doc.current_section = next_section
user_feedback_doc.save()
return next_section
def get_student(student_email):
student_id = frappe.get_all("Student", {"student_email_id": student_email},
["name"])[0].name
return frappe.get_doc("Student", student_id)
def get_bank_accounts():
accounts = [d.name for d in get_all("Account", filters={"account_type": "Bank"})]
return accounts
def create_inspection(batch_no,
item_code,
warehouse,
qty,
new_batch_id=None): #pr, doc, method
# batch = frappe.get_doc(dict(doctype='Batch', item=item_code, batch_id=new_batch_id)).insert()
ql_settings = frappe.get_doc('QL Settings')
company = frappe.db.get_value(
'Stock Ledger Entry',
dict(item_code=item_code, batch_no=batch_no, warehouse=warehouse),
['company'])
stock_entry_out = frappe.get_doc(
dict(doctype='Stock Entry',
purpose='Material Transfer',
stock_entry_type="Material Transfer",
company=company,
to_warehouse=ql_settings.qi_warehouse,
items=[
dict(item_code=item_code,
qty=float(qty or 0),
s_warehouse=warehouse,
t_warehouse=ql_settings.qi_warehouse,
batch_no=batch_no)
]))
qi_doc = frappe.get_all('Quality Inspection',
filters={'batch_no': batch_no},
fields='*',
order_by='modified')
cnt = len(qi_doc)
if cnt > 0:
new_qi_doc = frappe.copy_doc(
frappe.get_doc('Quality Inspection', qi_doc[0].name))
new_qi_doc.update({
"name": batch_no + '_D' + str(cnt),
"completion_status": "Not Started",
"status": "Rejected",
"report_date": datetime.now().date(),
"received_qty": float(qty or 0),
"inspected_by": ql_settings.qi_inspected_by_default
})
else:
new_qi_doc = frappe.get_doc(
dict(doctype='Quality Inspection',
name=batch_no + '_D' + str(cnt),
completion_status="Not Started",
status="Rejected",
received_qty=float(qty or 0),
inspected_by=ql_settings.qi_inspected_by_default,
report_date=datetime.now().date()))
new_qi_doc.insert()
stock_entry_in = frappe.get_doc(
dict(doctype='Stock Entry',
purpose='Material Transfer',
stock_entry_type="Material Transfer",
inspection_required=1,
company=company,
to_warehouse=warehouse,
items=[
dict(item_code=item_code,
qty=float(qty or 0),
s_warehouse=ql_settings.qi_warehouse,
t_warehouse=warehouse,
quality_inspection=new_qi_doc.name,
batch_no=batch_no)
]))
try:
stock_entry_out.insert()
stock_entry_out.submit()
stock_entry_in.insert()
frappe.db.commit()
except Exception:
frappe.db.rollback()
return new_qi_doc
def insert_communication(self, msg, args=None):
if isinstance(msg, list):
raw, uid, seen = msg
else:
raw = msg
uid = -1
seen = 0
if isinstance(args, dict):
if args.get("uid", -1): uid = args.get("uid", -1)
if args.get("seen", 0): seen = args.get("seen", 0)
email = Email(raw)
if email.from_email == self.email_id and not email.mail.get(
"Reply-To"):
# gmail shows sent emails in inbox
# and we don't want emails sent by us to be pulled back into the system again
# dont count emails sent by the system get those
if frappe.flags.in_test:
print(
'WARN: Cannot pull email. Sender sames as recipient inbox')
raise SentEmailInInbox
if email.message_id:
# https://stackoverflow.com/a/18367248
names = frappe.db.sql(
"""SELECT DISTINCT `name`, `creation` FROM `tabCommunication`
WHERE `message_id`='{message_id}'
ORDER BY `creation` DESC LIMIT 1""".format(message_id=email.message_id),
as_dict=True)
if names:
name = names[0].get("name")
# email is already available update communication uid instead
frappe.db.set_value("Communication",
name,
"uid",
uid,
update_modified=False)
self.flags.notify = False
return frappe.get_doc("Communication", name)
if email.content_type == 'text/html':
email.content = clean_email_html(email.content)
communication = frappe.get_doc({
"doctype":
"Communication",
"subject":
email.subject,
"content":
email.content,
'text_content':
email.text_content,
"sent_or_received":
"Received",
"sender_full_name":
email.from_real_name,
"sender":
email.from_email,
"recipients":
email.mail.get("To"),
"cc":
email.mail.get("CC"),
"email_account":
self.name,
"communication_medium":
"Email",
"uid":
int(uid or -1),
"message_id":
email.message_id,
"communication_date":
email.date,
"has_attachment":
1 if email.attachments else 0,
"seen":
seen or 0
})
self.set_thread(communication, email)
if communication.seen:
# get email account user and set communication as seen
users = frappe.get_all("User Email",
filters={"email_account": self.name},
fields=["parent"])
users = list(set([user.get("parent") for user in users]))
communication._seen = json.dumps(users)
communication.flags.in_receive = True
communication.insert(ignore_permissions=True)
# save attachments
communication._attachments = email.save_attachments_in_doc(
communication)
# replace inline images
dirty = False
for file in communication._attachments:
if file.name in email.cid_map and email.cid_map[file.name]:
dirty = True
email.content = email.content.replace(
"cid:{0}".format(email.cid_map[file.name]), file.file_url)
if dirty:
# not sure if using save() will trigger anything
communication.db_set("content", sanitize_html(email.content))
# notify all participants of this thread
if self.enable_auto_reply and getattr(communication, "is_first",
False):
self.send_auto_reply(communication, email)
return communication
def get_data(filters, show_party_name):
party_name_field = "{0}_name".format(
frappe.scrub(filters.get('party_type')))
if filters.get('party_type') == 'Student':
party_name_field = 'first_name'
party_filters = {
"name": filters.get("party")
} if filters.get("party") else {}
parties = frappe.get_all(filters.get("party_type"),
fields=["name", party_name_field],
filters=party_filters,
order_by="name")
company_currency = frappe.get_cached_value('Company', filters.company,
"default_currency")
opening_balances = get_opening_balances(filters)
balances_within_period = get_balances_within_period(filters)
data = []
# total_debit, total_credit = 0, 0
total_row = frappe._dict({
"opening_debit": 0,
"opening_credit": 0,
"debit": 0,
"credit": 0,
"closing_debit": 0,
"closing_credit": 0
})
for party in parties:
row = {"party": party.name}
if show_party_name:
row["party_name"] = party.get(party_name_field)
# opening
opening_debit, opening_credit = opening_balances.get(
party.name, [0, 0])
row.update({
"opening_debit": opening_debit,
"opening_credit": opening_credit
})
# within period
debit, credit = balances_within_period.get(party.name, [0, 0])
row.update({"debit": debit, "credit": credit})
# closing
closing_debit, closing_credit = toggle_debit_credit(
opening_debit + debit, opening_credit + credit)
row.update({
"closing_debit": closing_debit,
"closing_credit": closing_credit
})
# totals
for col in total_row:
total_row[col] += row.get(col)
row.update({"currency": company_currency})
has_value = False
if (opening_debit or opening_credit or debit or credit or closing_debit
or closing_credit):
has_value = True
if cint(filters.show_zero_values) or has_value:
data.append(row)
# Add total row
total_row.update({
"party": "'" + _("Totals") + "'",
"currency": company_currency
})
data.append(total_row)
return data
def create_sales_order(woocommerce_order, woocommerce_settings, company=None):
id = str(woocommerce_order.get("customer_id"))
customer = frappe.get_all("Customer",
filters=[["woocommerce_customer_id", "=", id]],
fields=['name'])
backup_customer = frappe.get_all("Customer",
filters=[[
"woocommerce_customer_id", "=",
"Guest of Order-ID: {0}".format(
woocommerce_order.get("id"))
]],
fields=['name'])
if customer:
customer = customer[0]['name']
elif backup_customer:
customer = backup_customer[0]['name']
else:
frappe.log_error("No customer found. This should never happen.")
so = frappe.db.get_value(
"Sales Order", {"woocommerce_order_id": woocommerce_order.get("id")},
"name")
if not so:
# get applicable tax rule from configuration
tax_rules = frappe.get_all(
"WooCommerce Tax Rule",
filters={'currency': woocommerce_order.get("currency")},
fields=['tax_rule'])
if not tax_rules:
# fallback: currency has no tax rule, try catch-all
tax_rules = frappe.get_all("WooCommerce Tax Rule",
filters={'currency': "%"},
fields=['tax_rule'])
if tax_rules:
tax_rules = tax_rules[0]['tax_rule']
else:
tax_rules = ""
so = frappe.get_doc({
"doctype":
"Sales Order",
"naming_series":
woocommerce_settings.sales_order_series or "SO-woocommerce-",
"woocommerce_order_id":
woocommerce_order.get("id"),
"customer":
customer,
"customer_group":
woocommerce_settings.
customer_group, # hard code group, as this was missing since v12
"delivery_date":
nowdate(),
"company":
woocommerce_settings.company,
"selling_price_list":
woocommerce_settings.price_list,
"ignore_pricing_rule":
1,
"items":
get_order_items(woocommerce_order.get("line_items"),
woocommerce_settings),
"taxes":
get_order_taxes(woocommerce_order, woocommerce_settings),
# disabled discount as WooCommerce will send this both in the item rate and as discount
#"apply_discount_on": "Net Total",
#"discount_amount": flt(woocommerce_order.get("discount_total") or 0),
"woocommerce_payment_method":
woocommerce_order.get("payment_method_title"),
"currency":
woocommerce_order.get("currency"),
"taxes_and_charges":
tax_rules
})
so.flags.ignore_mandatory = True
# alle orders in ERP = submitted
so.save(ignore_permissions=True)
so.submit()
#if woocommerce_order.get("status") == "on-hold":
# so.save(ignore_permissions=True)
#elif woocommerce_order.get("status") in ("cancelled", "refunded", "failed"):
# so.save(ignore_permissions=True)
# so.submit()
# so.cancel()
#else:
# so.save(ignore_permissions=True)
# so.submit()
else:
so = frappe.get_doc("Sales Order", so)
frappe.db.commit()
make_woocommerce_log(title="create sales order",
status="Success",
method="create_sales_order",
message="create sales_order",
request_data=woocommerce_order,
exception=False)
return so
def _get_filters(doctype, filters):
is_include = filters.vat_type not in ["Standard Rated", "Zero Rated"]
vat_exempt_accounts = [
x[0] for x in frappe.get_all(
"POS Bahrain Settings Tax Category",
filters={"category": filters.vat_type} if is_include else {},
fields=["account"],
as_list=1,
)
]
if not vat_exempt_accounts:
msg = "Please setup {}: <em>VAT Tax Categories</em>".format(
frappe.get_desk_link("POS Bahrain Settings", ""))
if filters.get("hide_error_message"):
raise VatCategoryNotFound(msg)
else:
frappe.throw(msg, exc=VatCategoryNotFound)
inv_clauses = list(
concatv(
["d.docstatus = 1"],
["d.posting_date BETWEEN %(from_date)s AND %(to_date)s"],
["IFNULL(dt.account_head, '') != ''"],
[
"dt.account_head {} %(tax_accounts)s".format(
"IN" if is_include else "NOT IN")
],
["d.company = %(company)s"] if filters.get('company') else [],
["d.cost_center = %(cost_center)s"]
if filters.get('cost_center') else [],
["d.set_warehouse = %(warehouse)s"]
if filters.get('warehouse') else [],
))
glp_clauses = concatv(
inv_clauses,
["d.payment_type IN %(payment_types)s", "a.account_type = 'Tax'"])
values = merge(
pick(["vat_type"], filters),
{
"from_date":
filters.from_date,
"to_date":
filters.to_date,
"tax_accounts":
vat_exempt_accounts,
"payment_types": ["Incoming"] if doctype == "Sales Invoice" else
["Outgoing", "Internal Transfer"],
},
pick(["company", "cost_center", "warehouse"], filters),
)
return (
{
"doctype":
doctype,
"item_doctype":
"{} Item".format(doctype),
"tax_doctype":
"{} Taxes and Charges".format("Sales" if doctype ==
"Sales Invoice" else "Purchase"),
"party_name":
"{}_name".format("customer" if doctype ==
"Sales Invoice" else "supplier"),
"invoice_clauses":
" AND ".join(inv_clauses),
"glp_clauses":
" AND ".join(glp_clauses),
},
values,
)
def work():
if random.random() < 0.3: return
frappe.set_user(frappe.db.get_global('demo_manufacturing_user'))
frappe.set_user_lang(frappe.db.get_global('demo_manufacturing_user'))
if not frappe.get_all('Sales Order'): return
from erpnext.projects.doctype.timesheet.timesheet import OverlapError
ppt = frappe.new_doc("Production Plan")
ppt.company = erpnext.get_default_company()
# ppt.use_multi_level_bom = 1 #refactored
ppt.get_items_from = "Sales Order"
# ppt.purchase_request_for_warehouse = "Stores - WP" # refactored
ppt.run_method("get_open_sales_orders")
if not ppt.get("sales_orders"): return
ppt.run_method("get_items")
ppt.run_method("raise_material_requests")
ppt.save()
ppt.submit()
ppt.run_method("raise_work_orders")
frappe.db.commit()
# submit work orders
for pro in frappe.db.get_values("Work Order", {"docstatus": 0}, "name"):
b = frappe.get_doc("Work Order", pro[0])
b.wip_warehouse = "Work in Progress - WP"
b.submit()
frappe.db.commit()
# submit material requests
for pro in frappe.db.get_values("Material Request", {"docstatus": 0},
"name"):
b = frappe.get_doc("Material Request", pro[0])
b.submit()
frappe.db.commit()
# stores -> wip
if random.random() < 0.4:
for pro in query_report.run("Open Work Orders")[
"result"][:how_many("Stock Entry for WIP")]:
make_stock_entry_from_pro(pro[0],
"Material Transfer for Manufacture")
# wip -> fg
if random.random() < 0.4:
for pro in query_report.run("Work Orders in Progress")[
"result"][:how_many("Stock Entry for FG")]:
make_stock_entry_from_pro(pro[0], "Manufacture")
for bom in frappe.get_all('BOM',
fields=['item'],
filters={'with_operations': 1}):
pro_order = make_wo_order_test_record(
item=bom.item,
qty=2,
source_warehouse=_("Stores") + " - WP",
wip_warehouse=_("Work in Progress") + " - WP",
fg_warehouse=_("Stores") + " - WP",
company=erpnext.get_default_company(),
stock_uom=frappe.db.get_value('Item', bom.item, 'stock_uom'),
planned_start_date=get_datetime(frappe.flags.current_date))
# submit job card
if random.random() < 0.4:
submit_job_cards()
def get_bom_operations(doctype, txt, searchfield, start, page_len, filters):
if txt:
filters['operation'] = ('like', '%%%s%%' % txt)
return frappe.get_all('BOM Operation',
filters=filters, fields=['operation'], as_list=1)
def tearDown(self):
for bt in frappe.get_all("Payment Order"):
doc = frappe.get_doc("Payment Order", bt.name)
doc.cancel()
doc.delete()
def delete_job_card(self):
for d in frappe.get_all("Job Card", ["name"], {"work_order": self.name}):
frappe.delete_doc("Job Card", d.name)
def get_items(start, page_length, price_list, item_group, pos_profile, search_value=""):
data = dict()
result = []
allow_negative_stock = frappe.db.get_single_value(
'Stock Settings', 'allow_negative_stock')
warehouse, hide_unavailable_items = frappe.db.get_value(
'POS Profile', pos_profile, ['warehouse', 'hide_unavailable_items'])
if not frappe.db.exists('Item Group', item_group):
item_group = get_root_of('Item Group')
if search_value:
data = search_serial_or_batch_or_barcode_number(search_value)
item_code = data.get("item_code") if data.get(
"item_code") else search_value
serial_no = data.get("serial_no") if data.get("serial_no") else ""
batch_no = data.get("batch_no") if data.get("batch_no") else ""
barcode = data.get("barcode") if data.get("barcode") else ""
if data:
item_info = frappe.db.get_value(
"Item", data.get("item_code"),
["name as item_code", "item_name", "description", "stock_uom", "image as item_image", "is_stock_item"], as_dict=1)
item_info.setdefault('serial_no', serial_no)
item_info.setdefault('batch_no', batch_no)
item_info.setdefault('barcode', barcode)
return {'items': [item_info]}
condition = get_conditions(item_code, serial_no, batch_no, barcode)
condition += get_item_group_condition(pos_profile)
lft, rgt = frappe.db.get_value('Item Group', item_group, ['lft', 'rgt'])
bin_join_selection, bin_join_condition = "", ""
if hide_unavailable_items:
bin_join_selection = ", `tabBin` bin"
bin_join_condition = "AND bin.warehouse = %(warehouse)s AND bin.item_code = item.name AND bin.actual_qty > 0"
items_data = frappe.db.sql("""
SELECT
item.name AS item_code,
item.item_name,
item.description,
item.stock_uom,
item.image AS item_image,
item.is_stock_item
FROM
`tabItem` item {bin_join_selection}
WHERE
item.disabled = 0
AND item.is_stock_item = 1
AND item.has_variants = 0
AND item.is_sales_item = 1
AND item.is_fixed_asset = 0
AND item.item_group in (SELECT name FROM `tabItem Group` WHERE lft >= {lft} AND rgt <= {rgt})
AND {condition}
{bin_join_condition}
ORDER BY
item.name asc
LIMIT
{start}, {page_length}"""
.format(
start=start,
page_length=page_length,
lft=lft,
rgt=rgt,
condition=condition,
bin_join_selection=bin_join_selection,
bin_join_condition=bin_join_condition
), {'warehouse': warehouse}, as_dict=1)
if items_data:
items = [d.item_code for d in items_data]
item_prices_data = frappe.get_all("Item Price",
fields=[
"item_code", "price_list_rate", "currency"],
filters={'price_list': price_list, 'item_code': ['in', items]})
item_prices = {}
for d in item_prices_data:
item_prices[d.item_code] = d
for item in items_data:
item_code = item.item_code
item_price = item_prices.get(item_code) or {}
if allow_negative_stock:
item_stock_qty = frappe.db.sql(
"""select ifnull(sum(actual_qty), 0) from `tabBin` where item_code = %s""", item_code)[0][0]
else:
item_stock_qty = get_stock_availability(item_code, warehouse)
row = {}
row.update(item)
row.update({
'price_list_rate': item_price.get('price_list_rate'),
'currency': item_price.get('currency'),
'actual_qty': item_stock_qty,
})
result.append(row)
res = {
'items': result
}
return res
def get_due_advance_amount(employee, posting_date):
employee_due_amount = frappe.get_all("Employee Advance", \
filters = {"employee":employee, "docstatus":1, "posting_date":("<=", posting_date)}, \
fields = ["advance_amount", "paid_amount"])
return sum([(emp.advance_amount - emp.paid_amount)
for emp in employee_due_amount])
def get_active_surveys():
active_surveys = frappe.get_all(
"User Feedback",
filters=[["User Feedback", "user", "=", frappe.session.user]],
fields=["name", "survey", "workflow_state"])
return active_surveys
def get_tds_amount(suppliers,
net_total,
company,
tax_details,
fiscal_year_details,
posting_date,
pan_no=None):
fiscal_year, year_start_date, year_end_date = fiscal_year_details
tds_amount = 0
tds_deducted = 0
def _get_tds(amount, rate):
if amount <= 0:
return 0
return amount * rate / 100
ldc_name = frappe.db.get_value('Lower Deduction Certificate', {
'pan_no': pan_no,
'fiscal_year': fiscal_year
}, 'name')
ldc = ''
if ldc_name:
ldc = frappe.get_doc('Lower Deduction Certificate', ldc_name)
entries = frappe.db.sql("""
select voucher_no, credit
from `tabGL Entry`
where company = %s and
party in %s and fiscal_year=%s and credit > 0
and is_opening = 'No'
""", (company, tuple(suppliers), fiscal_year),
as_dict=1)
vouchers = [d.voucher_no for d in entries]
advance_vouchers = get_advance_vouchers(suppliers,
fiscal_year=fiscal_year,
company=company)
tds_vouchers = vouchers + advance_vouchers
if tds_vouchers:
tds_deducted = frappe.db.sql(
"""
SELECT sum(credit) FROM `tabGL Entry`
WHERE
account=%s and fiscal_year=%s and credit > 0
and voucher_no in ({0})""".format(','.join(['%s'] * len(tds_vouchers))),
((tax_details.account_head, fiscal_year) + tuple(tds_vouchers)))
tds_deducted = tds_deducted[0][
0] if tds_deducted and tds_deducted[0][0] else 0
if tds_deducted:
if ldc:
limit_consumed = frappe.db.get_value('Purchase Invoice', {
'supplier': ('in', suppliers),
'apply_tds': 1,
'docstatus': 1
}, 'sum(net_total)')
if ldc and is_valid_certificate(ldc.valid_from, ldc.valid_upto,
posting_date, limit_consumed,
net_total, ldc.certificate_limit):
tds_amount = get_ltds_amount(net_total, limit_consumed,
ldc.certificate_limit, ldc.rate,
tax_details)
else:
tds_amount = _get_tds(net_total, tax_details.rate)
else:
supplier_credit_amount = frappe.get_all('Purchase Invoice Item',
fields=['sum(net_amount)'],
filters={
'parent': ('in', vouchers),
'docstatus': 1
},
as_list=1)
supplier_credit_amount = (supplier_credit_amount[0][0]
if supplier_credit_amount
and supplier_credit_amount[0][0] else 0)
jv_supplier_credit_amt = frappe.get_all(
'Journal Entry Account',
fields=['sum(credit_in_account_currency)'],
filters={
'parent': ('in', vouchers),
'docstatus': 1,
'party': ('in', suppliers),
'reference_type': ('not in', ['Purchase Invoice'])
},
as_list=1)
supplier_credit_amount += (jv_supplier_credit_amt[0][0]
if jv_supplier_credit_amt
and jv_supplier_credit_amt[0][0] else 0)
supplier_credit_amount += net_total
debit_note_amount = get_debit_note_amount(suppliers, year_start_date,
year_end_date)
supplier_credit_amount -= debit_note_amount
if ((tax_details.get('threshold', 0)
and supplier_credit_amount >= tax_details.threshold) or
(tax_details.get('cumulative_threshold', 0)
and supplier_credit_amount >= tax_details.cumulative_threshold)):
if ldc and is_valid_certificate(ldc.valid_from, ldc.valid_upto,
posting_date, tds_deducted,
net_total, ldc.certificate_limit):
tds_amount = get_ltds_amount(supplier_credit_amount, 0,
ldc.certificate_limit, ldc.rate,
tax_details)
else:
tds_amount = _get_tds(supplier_credit_amount, tax_details.rate)
return tds_amount
def check_matching_amount(bank_account, company, transaction):
payments = []
amount = transaction.credit if transaction.credit > 0 else transaction.debit
payment_type = "Receive" if transaction.credit > 0 else "Pay"
account_from_to = "paid_to" if transaction.credit > 0 else "paid_from"
currency_field = "paid_to_account_currency as currency" if transaction.credit > 0 else "paid_from_account_currency as currency"
payment_entries = frappe.get_all(
"Payment Entry",
fields=[
"'Payment Entry' as doctype", "name", "paid_amount",
"payment_type", "reference_no", "reference_date", "party",
"party_type", "posting_date", "{0}".format(currency_field)
],
filters=[["paid_amount", "like", "{0}%".format(amount)],
["docstatus", "=", "1"], ["payment_type", "=", payment_type],
["ifnull(clearance_date, '')", "=", ""],
[
"{0}".format(account_from_to), "=",
"{0}".format(bank_account)
]])
if transaction.credit > 0:
journal_entries = frappe.db.sql("""
SELECT
'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
je.pay_to_recd_from as party, je.cheque_date as reference_date, jea.debit_in_account_currency as paid_amount
FROM
`tabJournal Entry Account` as jea
JOIN
`tabJournal Entry` as je
ON
jea.parent = je.name
WHERE
(je.clearance_date is null or je.clearance_date='0000-00-00')
AND
jea.account = %s
AND
jea.debit_in_account_currency like %s
AND
je.docstatus = 1
""", (bank_account, amount),
as_dict=True)
else:
journal_entries = frappe.db.sql("""
SELECT
'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
je.pay_to_recd_from as party, je.cheque_date as reference_date, jea.credit_in_account_currency as paid_amount
FROM
`tabJournal Entry Account` as jea
JOIN
`tabJournal Entry` as je
ON
jea.parent = je.name
WHERE
(je.clearance_date is null or je.clearance_date='0000-00-00')
AND
jea.account = %s
AND
jea.credit_in_account_currency like %s
AND
je.docstatus = 1
""", (bank_account, amount),
as_dict=True)
if transaction.credit > 0:
sales_invoices = frappe.db.sql("""
SELECT
'Sales Invoice' as doctype, si.name, si.customer as party,
si.posting_date, sip.amount as paid_amount
FROM
`tabSales Invoice Payment` as sip
JOIN
`tabSales Invoice` as si
ON
sip.parent = si.name
WHERE
(sip.clearance_date is null or sip.clearance_date='0000-00-00')
AND
sip.account = %s
AND
sip.amount like %s
AND
si.docstatus = 1
""", (bank_account, amount),
as_dict=True)
else:
sales_invoices = []
if transaction.debit > 0:
purchase_invoices = frappe.get_all(
"Purchase Invoice",
fields=[
"'Purchase Invoice' as doctype", "name", "paid_amount",
"supplier as party", "posting_date", "currency"
],
filters=[["paid_amount", "like", "{0}%".format(amount)],
["docstatus", "=", "1"], ["is_paid", "=", "1"],
["ifnull(clearance_date, '')", "=", ""],
["cash_bank_account", "=", "{0}".format(bank_account)]])
mode_of_payments = [
x["parent"] for x in frappe.db.get_list(
"Mode of Payment Account",
filters={"default_account": bank_account},
fields=["parent"])
]
company_currency = get_company_currency(company)
expense_claims = frappe.get_all(
"Expense Claim",
fields=[
"'Expense Claim' as doctype", "name",
"total_sanctioned_amount as paid_amount", "employee as party",
"posting_date", "'{0}' as currency".format(company_currency)
],
filters=[[
"total_sanctioned_amount", "like", "{0}%".format(amount)
], ["docstatus", "=", "1"], ["is_paid", "=", "1"],
["ifnull(clearance_date, '')", "=", ""],
[
"mode_of_payment", "in",
"{0}".format(tuple(mode_of_payments))
]])
else:
purchase_invoices = expense_claims = []
for data in [
payment_entries, journal_entries, sales_invoices,
purchase_invoices, expense_claims
]:
if data:
payments.extend(data)
return payments
def delete_draft_work_order(self):
for d in frappe.get_all('Work Order', fields = ["name"],
filters = {'docstatus': 0, 'production_plan': ("=", self.name)}):
frappe.delete_doc('Work Order', d.name)
def check_item_is_booked(self, delivery_date, return_date, item):
if delivery_date:
filters = {}
filters = {'item': item, 'docstatus': 1}
filters.update({'delivery_date': ['<=', delivery_date]})
filters.update({'return_date': ['>=', delivery_date]})
bookings = frappe.get_all(
'Book Service Item',
filters=filters,
fields=['name', 'delivery_date', 'return_date', 'parent'])
for booking in bookings:
return "Already Booked: ID " + booking.parent
if return_date:
filters = {}
filters = {'item': item, 'docstatus': 1}
filters.update({'delivery_date': ['<=', return_date]})
filters.update({'return_date': ['>=', return_date]})
bookings = frappe.get_all(
'Book Service Item',
filters=filters,
fields=['name', 'delivery_date', 'return_date', 'parent'])
for booking in bookings:
return "Already Booked: ID " + booking.parent
if delivery_date and return_date:
filters = {}
filters = {'item': item, 'docstatus': 1}
filters.update({
'delivery_date':
['between', delivery_date + " and " + return_date]
})
bookings = frappe.get_all(
'Book Service Item',
filters=filters,
fields=['name', 'delivery_date', 'return_date', 'parent'])
for booking in bookings:
return "3 Already Booked: ID " + booking.parent + " \n" + str(
filters)
filters = {}
filters = {'item': item, 'docstatus': 1}
filters.update({
'return_date':
['between', delivery_date + " and " + return_date]
})
bookings = frappe.get_all(
'Book Service Item',
filters=filters,
fields=['name', 'delivery_date', 'return_date', 'parent'])
for booking in bookings:
return "Already Booked: ID " + booking.parent + " \n" + str(
filters)
if return_date < delivery_date:
return "Return date should be after delivery date"
return False
def get_matching_descriptions_data(company, transaction):
if not transaction.description:
return []
bank_transactions = frappe.db.sql("""
SELECT
bt.name, bt.description, bt.date, btp.payment_document, btp.payment_entry
FROM
`tabBank Transaction` as bt
LEFT JOIN
`tabBank Transaction Payments` as btp
ON
bt.name = btp.parent
WHERE
bt.allocated_amount > 0
AND
bt.docstatus = 1
""",
as_dict=True)
selection = []
for bank_transaction in bank_transactions:
if bank_transaction.description:
seq = difflib.SequenceMatcher(lambda x: x == " ",
transaction.description,
bank_transaction.description)
if seq.ratio() > 0.6:
bank_transaction["ratio"] = seq.ratio()
selection.append(bank_transaction)
document_types = set([x["payment_document"] for x in selection])
links = {}
for document_type in document_types:
links[document_type] = [
x["payment_entry"] for x in selection
if x["payment_document"] == document_type
]
data = []
company_currency = get_company_currency(company)
for key, value in iteritems(links):
if key == "Payment Entry":
data.extend(
frappe.get_all("Payment Entry",
filters=[["name", "in", value]],
fields=[
"'Payment Entry' as doctype",
"posting_date", "party", "reference_no",
"reference_date", "paid_amount",
"paid_to_account_currency as currency"
]))
if key == "Journal Entry":
journal_entries = frappe.get_all(
"Journal Entry",
filters=[["name", "in", value]],
fields=[
"name", "'Journal Entry' as doctype", "posting_date",
"paid_to_recd_from as party", "cheque_no as reference_no",
"cheque_date as reference_date",
"total_credit as paid_amount"
])
for journal_entry in journal_entries:
journal_entry_accounts = frappe.get_all(
"Journal Entry Account",
filters={
"parenttype": journal_entry["doctype"],
"parent": journal_entry["name"]
},
fields=["account_currency"])
journal_entry["currency"] = journal_entry_accounts[0][
"account_currency"] if journal_entry_accounts else company_currency
data.extend(journal_entries)
if key == "Sales Invoice":
data.extend(
frappe.get_all("Sales Invoice",
filters=[["name", "in", value]],
fields=[
"'Sales Invoice' as doctype",
"posting_date", "customer_name as party",
"paid_amount", "currency"
]))
if key == "Purchase Invoice":
data.extend(
frappe.get_all("Purchase Invoice",
filters=[["name", "in", value]],
fields=[
"'Purchase Invoice' as doctype",
"posting_date", "supplier_name as party",
"paid_amount", "currency"
]))
if key == "Expense Claim":
expense_claims = frappe.get_all(
"Expense Claim",
filters=[["name", "in", value]],
fields=[
"'Expense Claim' as doctype", "posting_date",
"employee_name as party",
"total_amount_reimbursed as paid_amount"
])
data.extend([
dict(x, **{"currency": company_currency})
for x in expense_claims
])
return data
def get_listas(tipo):
if tipo == "selling":
return frappe.get_all('Price List', {'selling': 1}, ['name'])
elif tipo == "buying":
return frappe.get_all('Price List', {'buying': 1}, ['name'])
return frappe.get_all('Price List', ['name'])
def tearDown(self):
for doc in frappe.get_all("Auto Repeat"):
frappe.delete_doc("Auto Repeat", doc.name, force=True)
def get_all(self, *args, **kwargs): return frappe.get_all(*args, **kwargs)
def get_team_data():
return frappe.get_all('Team Member', fields=['*'], order_by='joining_date')
def get_price(item_code, price_list, customer_group, company, qty=1):
template_item_code = frappe.db.get_value("Item", item_code, "variant_of")
if price_list:
price = frappe.get_all("Item Price",
fields=["price_list_rate", "currency"],
filters={
"price_list": price_list,
"item_code": item_code
})
if template_item_code and not price:
price = frappe.get_all("Item Price",
fields=["price_list_rate", "currency"],
filters={
"price_list": price_list,
"item_code": template_item_code
})
if price:
pricing_rule = get_pricing_rule_for_item(
frappe._dict({
"item_code": item_code,
"qty": qty,
"transaction_type": "selling",
"price_list": price_list,
"customer_group": customer_group,
"company": company,
"conversion_rate": 1,
"for_shopping_cart": True
}))
if pricing_rule:
if pricing_rule.pricing_rule_for == "Discount Percentage":
price[0].price_list_rate = flt(
price[0].price_list_rate *
(1.0 -
(flt(pricing_rule.discount_percentage) / 100.0)))
if pricing_rule.pricing_rule_for == "Price":
price[0].price_list_rate = pricing_rule.price_list_rate
price_obj = price[0]
if price_obj:
price_obj["formatted_price"] = fmt_money(
price_obj["price_list_rate"],
currency=price_obj["currency"])
price_obj["currency_symbol"] = not cint(frappe.db.get_default("hide_currency_symbol")) \
and (frappe.db.get_value("Currency", price_obj.currency, "symbol") or price_obj.currency) \
or ""
if not price_obj["price_list_rate"]:
price_obj["price_list_rate"] = 0
if not price_obj["currency"]:
price_obj["currency"] = ""
if not price_obj["formatted_price"]:
price_obj["formatted_price"] = ""
return price_obj
def check_warehouse_is_set_for_stock_item(self):
if self.is_stock_item == 1 and not self.default_warehouse and frappe.get_all(
"Warehouse"):
frappe.msgprint(
_("Default Warehouse is mandatory for stock Item."),
raise_exception=WarehouseNotSet)
