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)