def execute(): dataent.reload_doctype("Quotation") # update customer_name from Customer document if quotation_to is set to Customer dataent.db.sql(''' update tabQuotation, tabCustomer set tabQuotation.customer_name = tabCustomer.customer_name, tabQuotation.title = tabCustomer.customer_name where tabQuotation.customer_name is null and tabQuotation.party_name = tabCustomer.name and tabQuotation.quotation_to = 'Customer' ''') # update customer_name from Lead document if quotation_to is set to Lead dataent.db.sql(''' update tabQuotation, tabLead set tabQuotation.customer_name = case when ifnull(tabLead.company_name, '') != '' then tabLead.company_name else tabLead.lead_name end, tabQuotation.title = case when ifnull(tabLead.company_name, '') != '' then tabLead.company_name else tabLead.lead_name end where tabQuotation.customer_name is null and tabQuotation.party_name = tabLead.name and tabQuotation.quotation_to = 'Lead' ''')
def execute(): for doctype in ("Sales Order Item", "Bin"): dataent.reload_doctype(doctype) repost_for = dataent.db.sql(""" select distinct item_code, warehouse from ( ( select distinct item_code, warehouse from `tabSales Order Item` where docstatus=1 ) UNION ( select distinct item_code, warehouse from `tabPacked Item` where docstatus=1 and parenttype='Sales Order' ) ) so_item where exists(select name from tabItem where name=so_item.item_code and ifnull(is_stock_item, 0)=1) """) for item_code, warehouse in repost_for: update_bin_qty( item_code, warehouse, {"reserved_qty": get_reserved_qty(item_code, warehouse)}) dataent.db.sql("""delete from tabBin where exists( select name from tabItem where name=tabBin.item_code and ifnull(is_stock_item, 0) = 0 ) """)
def execute(): dataent.reload_doctype("Purchase Order") dataent.reload_doctype("Purchase Order Item") if not dataent.db.has_column("Purchase Order", "delivered_by_supplier"): return for po in dataent.get_all("Purchase Order", filters={"delivered_by_supplier": 1}, fields=["name"]): purchase_order = dataent.get_doc("Purchase Order", po) for item in purchase_order.items: if item.prevdoc_doctype == "Sales Order": delivered_by_supplier = dataent.get_value( "Sales Order Item", item.prevdoc_detail_docname, "delivered_by_supplier") if delivered_by_supplier: dataent.db.sql( """update `tabPurchase Order Item` set delivered_by_supplier=1, billed_amt=amount, received_qty=qty where name=%s """, item.name) update_per_received(purchase_order) update_per_billed(purchase_order)
def execute(): dataent.reload_doctype('Issue') dataent.reload_doctype('Opportunity') for doctype in ('Issue', 'Opportunity'): dataent.db.sql( 'update tab{0} set mins_to_first_response=0'.format(doctype)) for parent in dataent.get_all(doctype, order_by='creation desc', limit=500): parent_doc = dataent.get_doc(doctype, parent.name) for communication in dataent.get_all('Communication', filters={ 'reference_doctype': doctype, 'reference_name': parent.name, 'communication_medium': 'Email' }, order_by='creation asc', limit=2): communication_doc = dataent.get_doc('Communication', communication.name) update_mins_to_first_communication(parent_doc, communication_doc) if parent_doc.mins_to_first_response: continue
def execute(): dataent.reload_doctype('Custom Role') # set ref doctype in custom role for reports dataent.db.sql(""" update `tabCustom Role` set `tabCustom Role`.ref_doctype = (select ref_doctype from `tabReport` where name = `tabCustom Role`.report) where `tabCustom Role`.report is not null""")
def execute(): dataent.reload_doctype("Communication") for doctype, name in dataent.db.sql("""select distinct reference_doctype, reference_name from `tabCommunication` where (reference_doctype is not null and reference_doctype != '') and (reference_name is not null and reference_name != '') and (reference_owner is null or reference_owner = '') for update"""): owner = dataent.db.get_value(doctype, name, "owner") if not owner: continue dataent.db.sql("""update `tabCommunication` set reference_owner=%(owner)s where reference_doctype=%(doctype)s and reference_name=%(name)s and (reference_owner is null or reference_owner = '')""".format(doctype=doctype), { "doctype": doctype, "name": name, "owner": owner }) dataent.db.commit()
def execute(): dataent.reload_doctype("Email Digest") dataent.db.sql("""update `tabEmail Digest` set expense_year_to_date = income_year_to_date""") if dataent.db.exists("Email Digest", "Scheduler Errors"): dataent.delete_doc("Email Digest", "Scheduler Errors")
def execute(): item_details = dataent._dict() for d in dataent.db.sql("select name, description from `tabItem`", as_dict=1): description = cstr(d.description).strip() new_desc = extract_description(description) item_details.setdefault(d.name, dataent._dict({ "old_description": description, "new_description": new_desc })) dt_list= ["Purchase Order Item","Supplier Quotation Item", "BOM", "BOM Explosion Item" , \ "BOM Item", "Opportunity Item" , "Quotation Item" , "Sales Order Item" , "Delivery Note Item" , \ "Material Request Item" , "Purchase Receipt Item" , "Stock Entry Detail"] for dt in dt_list: dataent.reload_doctype(dt) records = dataent.db.sql("""select name, `{0}` as item_code, description from `tab{1}` where description is not null and description like '%%<table%%'""" .format("item" if dt=="BOM" else "item_code", dt), as_dict=1) count = 1 for d in records: if d.item_code and item_details.get(d.item_code) \ and cstr(d.description) == item_details.get(d.item_code).old_description: desc = item_details.get(d.item_code).new_description else: desc = extract_description(cstr(d.description)) dataent.db.sql("""update `tab{0}` set description = %s where name = %s """.format(dt), (desc, d.name)) count += 1 if count % 500 == 0: dataent.db.commit()
def execute(): for doctype in ("Material Request", "Purchase Order"): dataent.reload_doctype(doctype) dataent.reload_doctype(doctype + " Item") if not dataent.db.has_column(doctype, "schedule_date"): continue #Update only submitted MR for record in dataent.get_all(doctype, filters=[["docstatus", "=", 1]], fields=["name"]): doc = dataent.get_doc(doctype, record) if doc.items: if not doc.schedule_date: schedule_dates = [ d.schedule_date for d in doc.items if d.schedule_date ] if len(schedule_dates) > 0: min_schedule_date = min(schedule_dates) dataent.db.set_value(doctype, record, "schedule_date", min_schedule_date, update_modified=False)
def execute(): # stock reco now amendable dataent.db.sql("""update tabDocPerm set `amend` = 1 where parent='Stock Reconciliation' and submit = 1""") dataent.reload_doc("stock", "doctype", "stock_reconciliation_item") dataent.reload_doctype("Stock Reconciliation") if dataent.db.has_column("Stock Reconciliation", "reconciliation_json"): for sr in dataent.db.get_all("Stock Reconciliation", ["name"], {"reconciliation_json": ["!=", ""]}): start = False sr = dataent.get_doc("Stock Reconciliation", sr.name) for row in json.loads(sr.reconciliation_json): if start: sr.append("items", { "item_code": row[0], "warehouse": row[1], "qty": row[2] if len(row) > 2 else None, "valuation_rate": row[3] if len(row) > 3 else None }) elif row[0]=="Item Code": start = True for item in sr.items: item.db_update()
def execute(): dataent.reload_doctype('Stock Settings') stock_settings = dataent.get_doc('Stock Settings', 'Stock Settings') stock_settings.show_barcode_field = cint( dataent.db.get_value("Features Setup", None, "fs_item_barcode")) if not dataent.db.exists("UOM", stock_settings.stock_uom): stock_settings.stock_uom = None stock_settings.save() create_compact_item_print_custom_field() compact_item_print = dataent.db.get_value("Features Setup", None, "compact_item_print") dataent.db.set_value("Print Settings", None, "compact_item_print", compact_item_print) # remove defaults dataent.db.sql( """delete from tabDefaultValue where defkey in ('fs_item_serial_nos', 'fs_item_batch_nos', 'fs_brands', 'fs_item_barcode', 'fs_item_advanced', 'fs_packing_details', 'fs_item_group_in_details', 'fs_exports', 'fs_imports', 'fs_discounts', 'fs_purchase_discounts', 'fs_after_sales_installations', 'fs_projects', 'fs_sales_extras', 'fs_recurring_invoice', 'fs_pos', 'fs_manufacturing', 'fs_quality', 'fs_page_break', 'fs_more_info', 'fs_pos_view', 'compact_item_print')""") dataent.delete_doc('DocType', 'Features Setup')
def execute(): dataent.reload_doctype('Task') for t in dataent.get_all('Task', fields=['name']): task = dataent.get_doc('Task', t.name) task.update_depends_on() if task.depends_on_tasks: task.db_set('depends_on_tasks', task.depends_on_tasks, update_modified=False)
def execute(): from dataent.core.doctype.file.file import make_home_folder if not dataent.db.exists("DocType", "File"): dataent.rename_doc("DocType", "File Data", "File") dataent.reload_doctype("File") if not dataent.db.exists("File", {"is_home_folder": 1}): make_home_folder() # make missing folders and set parent folder for file in dataent.get_all("File", filters={"is_folder": 0}): file = dataent.get_doc("File", file.name) file.flags.ignore_folder_validate = True file.flags.ignore_file_validate = True file.flags.ignore_duplicate_entry_error = True file.flags.ignore_links = True file.set_folder_name() try: file.save() except: print(dataent.get_traceback()) raise from dataent.utils.nestedset import rebuild_tree rebuild_tree("File", "folder") # reset file size for folder in dataent.db.sql("""select name from tabFile f1 where is_folder = 1 and (select count(*) from tabFile f2 where f2.folder = f1.name and f2.is_folder = 1) = 0"""): folder = dataent.get_doc("File", folder[0]) folder.save()
def execute(): company_list = dataent.db.sql_list( """Select name from tabCompany where enable_perpetual_inventory = 1""") dataent.reload_doc('accounts', 'doctype', 'sales_invoice') dataent.reload_doctype("Purchase Invoice") wh_account = get_warehouse_account_map() for pi in dataent.get_all("Purchase Invoice", fields=["name", "company"], filters={ "docstatus": 1, "update_stock": 1 }): if pi.company in company_list: pi_doc = dataent.get_doc("Purchase Invoice", pi.name) items, warehouses = pi_doc.get_items_and_warehouses() update_gl_entries_after(pi_doc.posting_date, pi_doc.posting_time, warehouses, items, wh_account, company=pi.company) dataent.db.commit()
def execute(): dataent.reload_doctype("Currency Exchange") dataent.db.sql(""" update `tabCurrency Exchange` set `date` = '2010-01-01' where date is null or date = '0000-00-00' """)
def execute(): dataent.reload_doctype('Salary Slip', 'Salary Component') salary_components = [['Arrear', "ARR"], ['Leave Encashment', 'LENC']] for salary_component, salary_abbr in salary_components: if not dataent.db.exists('Salary Component', salary_component): sal_comp = dataent.get_doc({ "doctype": "Salary Component", "salary_component": salary_component, "type": "Earning", "salary_component_abbr": salary_abbr }).insert() salary_slips = dataent.db.sql( """select name, arrear_amount, leave_encashment_amount from `tabSalary Slip` where docstatus !=2 and (arrear_amount > 0 or leave_encashment_amount > 0)""", as_dict=True) for salary_slip in salary_slips: doc = dataent.get_doc('Salary Slip', salary_slip.name) if salary_slip.get("arrear_amount") > 0: r = doc.append('earnings', { 'salary_component': 'Arrear', 'amount': salary_slip.arrear_amount }) r.db_update() if salary_slip.get("leave_encashment_amount") > 0: r = doc.append( 'earnings', { 'salary_component': 'Leave Encashment', 'amount': salary_slip.leave_encashment_amount }) r.db_update()
def execute(): dataent.reload_doctype('Purchase Order Item') dataent.reload_doctype('Purchase Receipt Item') update_po_fields() update_prop_setters_reports_print_format_for_po() set_sales_order_field() rename_pr_fields()
def execute(): for doctype in ("Purchase Receipt Item", "Delivery Note Item"): dataent.reload_doctype(doctype) table_columns = dataent.db.get_table_columns(doctype) if "qa_no" in table_columns: rename_field(doctype, "qa_no", "quality_inspection") dataent.reload_doctype("Item") rename_field("Item", "inspection_required", "inspection_required_before_purchase") dataent.reload_doc('stock', 'doctype', 'quality_inspection') dataent.db.sql(""" update `tabQuality Inspection` set reference_type = 'Purchase Receipt', reference_name = purchase_receipt_no where ifnull(purchase_receipt_no, '') != '' and inspection_type = 'Incoming' """) dataent.db.sql(""" update `tabQuality Inspection` set reference_type = 'Delivery Note', reference_name = delivery_note_no where ifnull(delivery_note_no, '') != '' and inspection_type = 'Outgoing' """) for old_fieldname in ["purchase_receipt_no", "delivery_note_no"]: update_reports("Quality Inspection", old_fieldname, "reference_name") update_users_report_view_settings("Quality Inspection", old_fieldname, "reference_name") update_property_setters("Quality Inspection", old_fieldname, "reference_name")
def execute(): dataent.reload_doctype("Time Log") for d in dataent.get_all("Time Log"): time_log = dataent.get_doc("Time Log", d.name) time_log.set_title() dataent.db.set_value("Time Log", time_log.name, "title", time_log.title)
def execute(): dataent.reload_doctype('Company') enabled = dataent.db.get_single_value("Accounts Settings", "auto_accounting_for_stock") or 0 for data in dataent.get_all('Company', fields=["name"]): doc = dataent.get_doc('Company', data.name) doc.enable_perpetual_inventory = enabled doc.db_update()
def execute(): dataent.reload_doctype("Project") dataent.db.sql(''' UPDATE `tabProject` SET copied_from=name WHERE copied_from is NULL ''')
def execute(): '''Update company monthly sales history based on sales invoices''' dataent.reload_doctype("Company") companies = [d['name'] for d in dataent.get_list("Company")] for company in companies: update_company_current_month_sales(company) update_company_monthly_sales(company)
def execute(): dataent.reload_doctype("Account") dataent.reload_doctype("Cost Center") dataent.db.sql( "update tabAccount set is_group = if(group_or_ledger='Group', 1, 0)") dataent.db.sql( "update `tabCost Center` set is_group = if(group_or_ledger='Group', 1, 0)" )
def execute(): dataent.reload_doctype("Sales Order Item") for so_name in dataent.db.sql( """select distinct parent from `tabSales Order Item` where delivered_by_supplier=1 and docstatus=1"""): so = dataent.get_doc("Sales Order", so_name[0]) so.update_delivery_status() so.set_status(update=True, update_modified=False)
def execute(): dataent.reload_doctype('POS Profile') customer_group = dataent.db.get_single_value('Selling Settings', 'customer_group') if customer_group: dataent.db.sql( """ update `tabPOS Profile` set customer_group = %s where customer_group is null """, (customer_group))
def set_values(self): '''set values based on `data.set_value`''' if self.data.set_value: for args in self.data.set_value: dataent.reload_doctype(args[0]) doc = dataent.get_doc(args[0], args[1] or args[0]) doc.set(args[2], args[3]) doc.save()
def rename_and_reload_doctypes(): if "tabVariant Attribute" in dataent.db.get_tables(): dataent.rename_doc("DocType", "Variant Attribute", "Item Variant Attribute") dataent.reload_doctype("Item") dataent.reload_doc("Stock", "DocType", "Item Variant Attribute") dataent.reload_doc("Stock", "DocType", "Item Attribute Value") dataent.reload_doc("Stock", "DocType", "Item Attribute")
def execute(): dataent.reload_doctype('Timesheet') company = dataent.get_all('Company') #Check more than one company exists if len(company) > 1: dataent.db.sql(""" update `tabTimesheet` set `tabTimesheet`.company = (select company from `tabWork Order` where name = `tabTimesheet`.work_order) where workn_order is not null and work_order !=''""")
def execute(): dataent.reload_doctype("Warehouse") dataent.db.sql(""" update `tabWarehouse` set account = (select name from `tabAccount` where account_type = 'Stock' and warehouse = `tabWarehouse`.name and is_group = 0 limit 1)""")
def execute(): doc_list = ["Purchase Invoice Item", "Stock Entry Detail", "Delivery Note Item", "Purchase Receipt Item", "Sales Invoice Item"] for doctype in doc_list: dataent.reload_doctype(doctype) if "is_sample_item" in dataent.db.get_table_columns(doctype): rename_field(doctype, "is_sample_item", "allow_zero_valuation_rate")