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")
