def execute():
frappe.reload_doctype("Sales Order Item")
repost_for = frappe.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)
})
frappe.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():
item_details = frappe._dict()
for d in frappe.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, frappe._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:
frappe.reload_doctype(dt)
records = frappe.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))
frappe.db.sql("""update `tab{0}` set description = %s
where name = %s """.format(dt), (desc, d.name))
count += 1
if count % 500 == 0:
frappe.db.commit()
def execute():
item_details = frappe._dict()
for d in frappe.db.sql("select name, description, image from `tabItem`", as_dict=1):
description = cstr(d.description).strip()
item_details.setdefault(d.name, frappe._dict({
"description": description,
"image": d.image
}))
dt_list= ["Sales Invoice Item","Purchase Invoice Item"]
for dt in dt_list:
frappe.reload_doctype(dt)
records = frappe.db.sql("""select name, item_code, description from `tab{0}`
where ifnull(item_code, '') != '' and description is not null """.format(dt), as_dict=1)
count = 1
for d in records:
if item_details.get(d.item_code) and cstr(d.description) == item_details.get(d.item_code).description:
desc = item_details.get(d.item_code).description
image = item_details.get(d.item_code).image
else:
desc, image = extract_image_and_description(cstr(d.description))
if not image:
item_detail = item_details.get(d.item_code)
if item_detail:
image = item_detail.image
frappe.db.sql("""update `tab{0}` set description = %s, image = %s
where name = %s """.format(dt), (desc, image, d.name))
count += 1
if count % 500 == 0:
frappe.db.commit()
def execute():
if not frappe.db.get_single_value("Accounts Settings", "auto_accounting_for_stock"):
return
frappe.reload_doctype("Account")
warehouses = frappe.db.sql_list("""select name from tabAccount
where account_type = 'Stock' and is_group = 0
and (warehouse is null or warehouse = '')""")
if warehouses:
warehouses = set_warehouse_for_stock_account(warehouses)
stock_vouchers = frappe.db.sql("""select distinct sle.voucher_type, sle.voucher_no
from `tabStock Ledger Entry` sle
where sle.warehouse in (%s) and creation > '2016-05-01'
and not exists(select name from `tabGL Entry`
where account=sle.warehouse and voucher_type=sle.voucher_type and voucher_no=sle.voucher_no)
order by sle.posting_date""" %
', '.join(['%s']*len(warehouses)), tuple(warehouses))
rejected = []
for voucher_type, voucher_no in stock_vouchers:
try:
frappe.db.sql("""delete from `tabGL Entry`
where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
voucher = frappe.get_doc(voucher_type, voucher_no)
voucher.make_gl_entries()
frappe.db.commit()
except Exception, e:
print frappe.get_traceback()
rejected.append([voucher_type, voucher_no])
frappe.db.rollback()
print rejected
def execute():
frappe.reload_doctype("Email Digest")
frappe.db.sql("""update `tabEmail Digest` set expense_year_to_date =
income_year_to_date""")
if frappe.db.exists("Email Digest", "Scheduler Errors"):
frappe.delete_doc("Email Digest", "Scheduler Errors")
def execute():
frappe.reload_doctype('Purchase Order Item')
frappe.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"):
frappe.reload_doctype(doctype)
table_columns = frappe.db.get_table_columns(doctype)
if "qa_no" in table_columns:
rename_field(doctype, "qa_no", "quality_inspection")
frappe.reload_doctype("Item")
rename_field("Item", "inspection_required", "inspection_required_before_purchase")
frappe.reload_doc('stock', 'doctype', 'quality_inspection')
frappe.db.sql("""
update
`tabQuality Inspection`
set
reference_type = 'Purchase Receipt', reference_name = purchase_receipt_no
where
ifnull(purchase_receipt_no, '') != '' and inspection_type = 'Incoming'
""")
frappe.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():
frappe.reload_doctype("Sales Order Item")
for so_name in frappe.db.sql("""select distinct parent from `tabSales Order Item`
where delivered_by_supplier=1 and docstatus=1"""):
so = frappe.get_doc("Sales Order", so_name[0])
so.update_delivery_status()
so.set_status(update=True, update_modified=False)
def execute():
frappe.reload_doctype('Company')
enabled = frappe.db.get_single_value("Accounts Settings", "auto_accounting_for_stock") or 0
for data in frappe.get_all('Company', fields = ["name"]):
doc = frappe.get_doc('Company', data.name)
doc.enable_perpetual_inventory = enabled
doc.db_update()
def execute():
sync_fixtures("erpnext_shopify")
frappe.reload_doctype("Item")
shopify_settings = frappe.get_doc("Shopify Settings")
if not shopify_settings.enable_shopify:
return
try:
shopify_items = get_item_list()
except ShopifyError:
print "Could not run shopify patch 'set_variant_id' for site: {0}".format(frappe.local.site)
return
if shopify_settings.shopify_url and shopify_items:
for item in frappe.db.sql("""select name, item_code, shopify_id, has_variants, variant_of from tabItem
where sync_with_shopify=1 and shopify_id is not null""", as_dict=1):
if item.get("variant_of"):
frappe.db.sql(""" update tabItem set shopify_variant_id=shopify_id
where name = %s """, item.get("name"))
elif not item.get("has_variants"):
product = filter(lambda shopify_item: shopify_item['id'] == cint(item.get("shopify_id")), shopify_items)
if product:
frappe.db.sql(""" update tabItem set shopify_variant_id=%s
where name = %s """, (product[0]["variants"][0]["id"], item.get("name")))
def execute():
frappe.reload_doctype("Feed")
frappe.db.sql("update `tabFeed` set seen=1")
for doctype, name in frappe.db.sql("""select distinct doc_type, doc_name from `tabFeed`
where
(doc_type is not null and doc_type != '')
and (doc_name is not null and doc_name != '')
and doc_type != 'Feed'
for update"""):
owner = frappe.db.get_value(doctype, name, "owner")
if not owner:
continue
frappe.db.sql("""update `tabFeed`
set doc_owner=%(owner)s
where
doc_type=%(doctype)s
and doc_name=%(name)s
and (doc_owner is null or doc_owner = '')""".format(doctype=doctype), {
"doctype": doctype,
"name": name,
"owner": owner
})
frappe.db.commit()
def execute():
from frappe.core.doctype.file.file import make_home_folder
if not frappe.db.exists("DocType", "File"):
frappe.rename_doc("DocType", "File Data", "File")
frappe.reload_doctype("File")
if not frappe.db.exists("File", {"is_home_folder": 1}):
make_home_folder()
# make missing folders and set parent folder
for file in frappe.get_all("File", filters={"is_folder": 0}):
file = frappe.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 frappe.get_traceback()
raise
from frappe.utils.nestedset import rebuild_tree
rebuild_tree("File", "folder")
# reset file size
for folder in frappe.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 = frappe.get_doc("File", folder[0])
folder.save()
def execute():
frappe.reload_doctype('Custom Role')
# set ref doctype in custom role for reports
frappe.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():
frappe.reload_doctype("Production Order")
frappe.db.sql("""update `tabProduction Order` set material_transferred_for_manufacturing=
(select sum(fg_completed_qty) from `tabStock Entry`
where docstatus=1
and production_order=`tabProduction Order`.name
and purpose = "Material Transfer for Manufacture")""")
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 execute():
frappe.reload_doctype("Currency Exchange")
frappe.db.sql("""
update `tabCurrency Exchange`
set `date` = '2010-01-01'
where date is null or date = '' or date = '0000-00-00'
""")
def execute():
frappe.reload_doctype('Task')
for t in frappe.get_all('Task', fields=['name']):
task = frappe.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():
frappe.reload_doctype("Communication")
for doctype, name in frappe.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 = frappe.db.get_value(doctype, name, "owner")
if not owner:
continue
frappe.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
})
frappe.db.commit()
def execute():
# stock reco now amendable
frappe.db.sql("""update tabDocPerm set `amend` = 1 where parent='Stock Reconciliation' and submit = 1""")
frappe.reload_doc("stock", "doctype", "stock_reconciliation_item")
frappe.reload_doctype("Stock Reconciliation")
if frappe.db.has_column("Stock Reconciliation", "reconciliation_json"):
for sr in frappe.db.get_all("Stock Reconciliation", ["name"],
{"reconciliation_json": ["!=", ""]}):
start = False
sr = frappe.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():
frappe.reload_doctype("Purchase Order")
frappe.reload_doctype("Purchase Order Item")
if not frappe.db.has_column("Purchase Order", "delivered_by_supplier"):
return
for po in frappe.get_all("Purchase Order", filters={"delivered_by_supplier": 1}, fields=["name"]):
purchase_order = frappe.get_doc("Purchase Order", po)
for item in purchase_order.items:
if item.prevdoc_doctype == "Sales Order":
delivered_by_supplier = frappe.get_value(
"Sales Order Item", item.prevdoc_detail_docname, "delivered_by_supplier"
)
if delivered_by_supplier:
frappe.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():
frappe.reload_doctype('Salary Slip', 'Salary Component')
salary_components = [['Arrear', "ARR"], ['Leave Encashment', 'LENC']]
for salary_component, salary_abbr in salary_components:
if not frappe.db.exists('Salary Component', salary_component):
sal_comp = frappe.get_doc({
"doctype": "Salary Component",
"salary_component": salary_component,
"type": "Earning",
"salary_component_abbr": salary_abbr
}).insert()
salary_slips = frappe.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 = frappe.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():
frappe.reload_doctype("Web Form")
frappe.delete_doc("Web Form", "Issues")
frappe.delete_doc("Web Form", "Addresses")
from erpnext.setup.install import add_web_forms
add_web_forms()
def execute():
# Reload doctype
for dt in ("Account", "GL Entry", "Journal Entry",
"Journal Entry Account", "Sales Invoice", "Purchase Invoice", "Customer", "Supplier"):
frappe.reload_doctype(dt)
company_list = frappe.get_all("Company", fields=["name", "default_currency", "default_receivable_account"])
for company in company_list:
# update currency in account and gl entry as per company currency
frappe.db.sql("""update `tabAccount` set account_currency = %s
where ifnull(account_currency, '') = '' and company=%s""", (company.default_currency, company.name))
# update newly introduced field's value in sales / purchase invoice
frappe.db.sql("""
update
`tabSales Invoice`
set
base_paid_amount=paid_amount,
base_write_off_amount=write_off_amount,
party_account_currency=%s
where company=%s
""", (company.default_currency, company.name))
frappe.db.sql("""
update
`tabPurchase Invoice`
set
base_write_off_amount=write_off_amount,
party_account_currency=%s
where company=%s
""", (company.default_currency, company.name))
# update exchange rate, debit/credit in account currency in Journal Entry
frappe.db.sql("""
update `tabJournal Entry Account` jea
set exchange_rate=1,
debit_in_account_currency=debit,
credit_in_account_currency=credit,
account_type=(select account_type from `tabAccount` where name=jea.account)
""")
frappe.db.sql("""
update `tabJournal Entry Account` jea, `tabJournal Entry` je
set account_currency=%s
where jea.parent = je.name and je.company=%s
""", (company.default_currency, company.name))
# update debit/credit in account currency in GL Entry
frappe.db.sql("""
update
`tabGL Entry`
set
debit_in_account_currency=debit,
credit_in_account_currency=credit,
account_currency=%s
where
company=%s
""", (company.default_currency, company.name))
def execute():
frappe.reload_doctype('Student Attendance')
# frappe.reload_doc("schools", "doctype", "student_attendance")
frappe.db.sql('''
update `tabStudent Attendance` set
docstatus=0
where
docstatus=1''')
def execute():
frappe.reload_doctype("Quotation")
frappe.db.sql(""" UPDATE `tabQuotation` set party_name = lead WHERE quotation_to = 'Lead' """)
frappe.db.sql(""" UPDATE `tabQuotation` set party_name = customer WHERE quotation_to = 'Customer' """)
frappe.reload_doctype("Opportunity")
frappe.db.sql(""" UPDATE `tabOpportunity` set party_name = lead WHERE opportunity_from = 'Lead' """)
frappe.db.sql(""" UPDATE `tabOpportunity` set party_name = customer WHERE opportunity_from = 'Customer' """)
def execute():
for dt in ("Sales Invoice Advance", "Purchase Invoice Advance"):
frappe.reload_doctype(dt)
frappe.db.sql("update `tab{0}` set reference_type = 'Journal Entry'".format(dt))
rename_field(dt, "journal_entry", "reference_name")
rename_field(dt, "jv_detail_no", "reference_row")
def execute():
'''Update company monthly sales history based on sales invoices'''
frappe.reload_doctype("Company")
companies = [d['name'] for d in frappe.get_list("Company")]
for company in companies:
update_company_current_month_sales(company)
update_company_monthly_sales(company)
def execute(): doc_list = ["Purchase Invoice Item", "Stock Entry Detail", "Delivery Note Item", "Purchase Receipt Item", "Sales Invoice Item"] for doctype in doc_list: frappe.reload_doctype(doctype) rename_field(doctype, "is_sample_item", "allow_zero_valuation_rate")
def rename_and_reload_doctypes():
if "tabVariant Attribute" in frappe.db.get_tables():
frappe.rename_doc("DocType", "Variant Attribute", "Item Variant Attribute")
frappe.reload_doctype("Item")
frappe.reload_doc("Stock", "DocType", "Item Variant Attribute")
frappe.reload_doc("Stock", "DocType", "Item Attribute Value")
frappe.reload_doc("Stock", "DocType", "Item Attribute")
def set_values(self): '''set values based on `data.set_value`''' if self.data.set_value: for args in self.data.set_value: frappe.reload_doctype(args[0]) doc = frappe.get_doc(args[0], args[1] or args[0]) doc.set(args[2], args[3]) doc.save()
def execute():
frappe.reload_doctype('Employee')
frappe.db.sql('update tabEmployee set first_name = employee_name')
# update holiday list
frappe.reload_doctype('Holiday List')
for holiday_list in frappe.get_all('Holiday List'):
holiday_list = frappe.get_doc('Holiday List', holiday_list.name)
holiday_list.db_set('total_holidays',
len(holiday_list.holidays),
update_modified=False)
def execute():
frappe.reload_doctype("POS Profile")
frappe.reload_doctype("Sales Invoice Payment")
frappe.db.sql("""
update
`tabSales Invoice Payment`
set `tabSales Invoice Payment`.default = 1
where
`tabSales Invoice Payment`.parenttype = 'POS Profile'
and `tabSales Invoice Payment`.idx=1""")
def execute():
frappe.reload_doctype('Supplier Quotation Item')
for data in frappe.db.sql(
""" select prevdoc_docname, prevdoc_detail_docname, name
from `tabSupplier Quotation Item` where prevdoc_docname is not null""",
as_dict=True):
frappe.db.set_value("Supplier Quotation Item", data.name,
"material_request", data.prevdoc_docname)
frappe.db.set_value("Supplier Quotation Item", data.name,
"material_request_item",
data.prevdoc_detail_docname)
def execute():
frappe.reload_doctype('Salary Slip')
if not frappe.db.has_column('Salary Detail', 'abbr'):
return
salary_details = frappe.db.sql("""select abbr, salary_component, name from `tabSalary Detail`
where abbr is null or abbr = ''""", as_dict=True)
for salary_detail in salary_details:
salary_component_abbr = frappe.get_value("Salary Component", salary_detail.salary_component, "salary_component_abbr")
frappe.db.sql("""update `tabSalary Detail` set abbr = %s where name = %s""",(salary_component_abbr, salary_detail.name))
def execute():
for doctype in ('Sales Order', 'Purchase Order', 'Sales Invoice',
'Purchase Invoice'):
frappe.reload_doctype(doctype)
frappe.db.sql(
'''update `tab{0}` set submit_on_creation=1, notify_by_email=1
where is_recurring=1'''.format(doctype))
frappe.db.sql('''update `tab{0}` set notify_by_email=1
where is_recurring=1'''.format(doctype))
frappe.db.sql('''update `tab{0}` set recurring_id = name
where is_recurring=1 and ifnull(recurring_id, '') = "" '''.format(doctype))
def execute():
for doctype in ("Sales Order Item", "Bin"):
frappe.reload_doctype(doctype)
repost_for = frappe.db.sql("""select distinct item_code, warehouse
from `tabSales Order Item` where docstatus=1 and uom != stock_uom and
exists(select name from tabItem where name=`tabSales Order 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)
})
def execute():
frappe.reload_doctype("Notification")
for e in frappe.get_all("Notification"):
notification = frappe.get_doc("Notification", e.name)
if notification.event == "Date Change":
if notification.days_in_advance < 0:
notification.event = "Days After"
notification.days_in_advance = -email_alert.days_in_advance
else:
notification.event = "Days Before"
notification.save()
def execute():
frappe.reload_doctype("Serial No")
frappe.db.sql("""
update
`tabSerial No`
set
sales_invoice = NULL
where
sales_invoice in (select return_against from
`tabSales Invoice` where docstatus =1 and is_return=1)
and sales_invoice is not null and sales_invoice !='' """)
def execute():
doc_list = [
"Purchase Invoice Item", "Stock Entry Detail", "Delivery Note Item",
"Purchase Receipt Item", "Sales Invoice Item"
]
for doctype in doc_list:
frappe.reload_doctype(doctype)
if "is_sample_item" in frappe.db.get_table_columns(doctype):
rename_field(doctype, "is_sample_item",
"allow_zero_valuation_rate")
def execute():
frappe.reload_doctype('GL Entry')
frappe.db.sql("""
UPDATE
`tabGL Entry`
SET
against_voucher_type = 'Loan',
against_voucher = voucher_no
WHERE voucher_type = 'Loan'
AND against_voucher_type IS NULL
AND against_voucher IS NULL
""")
def execute():
for dt in ("Sales Invoice Advance", "Purchase Invoice Advance"):
frappe.reload_doctype(dt)
frappe.db.sql(
"update `tab{0}` set reference_type = 'Journal Entry'".format(dt))
if frappe.get_meta(dt).has_field('journal_entry'):
rename_field(dt, "journal_entry", "reference_name")
if frappe.get_meta(dt).has_field('jv_detail_no'):
rename_field(dt, "jv_detail_no", "reference_row")
def execute():
frappe.reload_doctype("Sales Invoice")
return_entries = frappe.get_list(
"Sales Invoice",
filters={
"is_return": 1,
"docstatus": 1
},
fields=["debit_to", "customer", "return_against"])
for d in return_entries:
update_outstanding_amt(d.debit_to, "Customer", d.customer,
"Sales Invoice", d.return_against)
def execute():
'''Get 'Disable CWIP Accounting value' from Asset Settings, set it in 'Enable Capital Work in Progress Accounting' field
in Company, delete Asset Settings '''
if frappe.db.exists("DocType", "Asset Settings"):
frappe.reload_doctype("Asset Category")
cwip_value = frappe.db.get_single_value("Asset Settings", "disable_cwip_accounting")
frappe.db.sql("""UPDATE `tabAsset Category` SET enable_cwip_accounting = %s""", cint(cwip_value))
frappe.db.sql("""DELETE FROM `tabSingles` where doctype = 'Asset Settings'""")
frappe.delete_doc_if_exists("DocType", "Asset Settings")
def execute():
frappe.reload_doctype("Pricing Rule")
currency = frappe.db.get_default("currency")
for doc in frappe.get_all('Pricing Rule', fields=["company", "name"]):
if doc.company:
currency = frappe.get_cached_value('Company', doc.company,
"default_currency")
frappe.db.sql(
"""update `tabPricing Rule` set currency = %s where name = %s""",
(currency, doc.name))
def execute():
frappe.reload_doctype("Shipping Rule")
# default "calculate_based_on"
frappe.db.sql('''update `tabShipping Rule`
set calculate_based_on = "Net Weight"
where ifnull(calculate_based_on, '') = '' ''')
# default "shipping_rule_type"
frappe.db.sql('''update `tabShipping Rule`
set shipping_rule_type = "Selling"
where ifnull(shipping_rule_type, '') = '' ''')
def reload_doctype(context, doctype):
"Reload schema for a DocType"
for site in context.sites:
try:
frappe.init(site=site)
frappe.connect()
frappe.reload_doctype(doctype, force=context.force)
frappe.db.commit()
finally:
frappe.destroy()
if not context.sites:
raise SiteNotSpecifiedError
def execute():
frappe.reload_doctype("Email Alert")
for e in frappe.get_all("Email Alert"):
email_alert = frappe.get_doc("Email Alert", e.name)
if email_alert.event == "Date Change":
if email_alert.days_in_advance < 0:
email_alert.event = "Days After"
email_alert.days_in_advance = -email_alert.days_in_advance
else:
email_alert.event = "Days Before"
email_alert.save()
def execute():
frappe.reload_doctype("Sales Order")
frappe.reload_doctype("Sales Order Item")
frappe.db.sql(
"""update `tabSales Order` set final_delivery_date = delivery_date where docstatus=1"""
)
frappe.db.sql("""
update `tabSales Order` so, `tabSales Order Item` so_item
set so_item.delivery_date = so.delivery_date
where so.name = so_item.parent
""")
def execute():
""" set status as Paid in Expense Claim if total_sactioned_amount
and total_amount_reimbursed is equal """
frappe.reload_doctype('Expense Claim')
frappe.db.sql("""
update
`tabExpense Claim`
set status = 'Paid'
where
total_sanctioned_amount = total_amount_reimbursed
""")
def execute():
frappe.reload_doctype('Role')
for dt in ("assessment", "course", "fees"):
frappe.reload_doc("schools", "doctype", dt)
frappe.reload_doc('website', 'doctype', 'portal_menu_item')
frappe.get_doc('Portal Settings').sync_menu()
if 'schools' in frappe.get_installed_apps():
domainify.setup_domain('Education')
else:
domainify.setup_sidebar_items(domainify.get_domain('Manufacturing'))
def execute():
frappe.reload_doctype('Sales Order Item')
frappe.reload_doctype('Sales Order')
for d in frappe.get_all('Work Order',
fields=['sales_order', 'sales_order_item'],
filters={
'sales_order': ('!=', ''),
'sales_order_item': ('!=', '')
}):
# update produced qty in sales order
update_produced_qty_in_so_item(d.sales_order, d.sales_order_item)
def execute():
frappe.reload_doctype("Selling Settings")
selling_settings = frappe.get_single("Selling Settings")
if selling_settings.customer_group in (_("All Customer Groups"),
"All Customer Groups"):
selling_settings.customer_group = None
if selling_settings.territory in (_("All Territories"), "All Territories"):
selling_settings.territory = None
selling_settings.flags.ignore_mandatory = True
selling_settings.save(ignore_permissions=True)
def execute():
if "purchase_receipt" not in frappe.db.get_table_columns(
"Landed Cost Purchase Receipt"):
return
frappe.reload_doctype("Landed Cost Purchase Receipt")
frappe.db.sql("""
update `tabLanded Cost Purchase Receipt`
set receipt_document_type = 'Purchase Receipt', receipt_document = purchase_receipt
where (receipt_document is null or receipt_document = '')
and (purchase_receipt is not null and purchase_receipt != '')
""")
def execute():
frappe.reload_doc("stock", "doctype", "manufacturer")
frappe.reload_doctype("Item")
for d in frappe.db.sql("""select distinct manufacturer from tabItem
where ifnull(manufacturer, '') != '' and disabled=0"""):
manufacturer_name = cstr(d[0]).strip()
if manufacturer_name and not frappe.db.exists("Manufacturer",
manufacturer_name):
man = frappe.new_doc("Manufacturer")
man.short_name = manufacturer_name
man.full_name = manufacturer_name
man.save()
def execute():
'''
Remove the doctype "Custom Link" that was used to add Custom Links to the
Dashboard since this is now managed by Customize Form.
Update `parent` property to the DocType and delte the doctype
'''
frappe.reload_doctype('DocType Link')
if frappe.db.has_table('Custom Link'):
for custom_link in frappe.get_all('Custom Link', ['name', 'document_type']):
frappe.db.sql('update `tabDocType Link` set custom=1, parent=%s where parent=%s',
(custom_link.document_type, custom_link.name))
frappe.delete_doc('DocType', 'Custom Link')
def execute():
frappe.reload_doctype('Document Naming Rule')
doc = frappe.get_doc({
'doctype': 'Document Naming Rule',
'document_type': 'Sales Invoice',
'priority': 1,
'prefix': 'Recibo No. ',
'prefix_digits': 6,
'counter': 0
})
doc.insert()
def execute():
frappe.reload_doctype('Task')
# add "Completed" if customized
property_setter_name = frappe.db.exists('Property Setter', dict(doc_type='Task', field_name = 'status', property = 'options'))
if property_setter_name:
property_setter = frappe.get_doc('Property Setter', property_setter_name)
if not "Completed" in property_setter.value:
property_setter.value = property_setter.value + '\nCompleted'
property_setter.save()
# renamed default status to Completed as status "Closed" is ambiguous
frappe.db.sql('update tabTask set status = "Completed" where status = "Closed"')
def execute():
frappe.reload_doctype("Purchase Invoice")
for pi in frappe.db.sql("""select name from `tabPurchase Invoice`
where company in(select name from tabCompany where enable_perpetual_inventory = 1) and
update_stock=1 and docstatus=1 order by posting_date asc""", as_dict=1):
frappe.db.sql("""delete from `tabGL Entry`
where voucher_type = 'Purchase Invoice' and voucher_no = %s""", pi.name)
pi_doc = frappe.get_doc("Purchase Invoice", pi.name)
pi_doc.make_gl_entries(repost_future_gle=False)
frappe.db.commit()
def test_index_and_unique_constraints(self):
doctype = "User"
frappe.reload_doctype("User", force=True)
frappe.model.meta.trim_tables("User")
make_property_setter(doctype, "restrict_ip", "unique", "1", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertTrue(restrict_ip_in_table.unique)
make_property_setter(doctype, "restrict_ip", "unique", "0", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertFalse(restrict_ip_in_table.unique)
make_property_setter(doctype, "restrict_ip", "search_index", "1", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertTrue(restrict_ip_in_table.index)
make_property_setter(doctype, "restrict_ip", "search_index", "0", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertFalse(restrict_ip_in_table.index)
make_property_setter(doctype, "restrict_ip", "search_index", "1", "Int")
make_property_setter(doctype, "restrict_ip", "unique", "1", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertTrue(restrict_ip_in_table.index)
self.assertTrue(restrict_ip_in_table.unique)
make_property_setter(doctype, "restrict_ip", "search_index", "1", "Int")
make_property_setter(doctype, "restrict_ip", "unique", "0", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertTrue(restrict_ip_in_table.index)
self.assertFalse(restrict_ip_in_table.unique)
make_property_setter(doctype, "restrict_ip", "search_index", "0", "Int")
make_property_setter(doctype, "restrict_ip", "unique", "1", "Int")
frappe.db.updatedb(doctype)
restrict_ip_in_table = get_table_column("User", "restrict_ip")
self.assertFalse(restrict_ip_in_table.index)
self.assertTrue(restrict_ip_in_table.unique)
# explicitly make a text index
frappe.db.add_index(doctype, ["email_signature(200)"])
frappe.db.updatedb(doctype)
email_sig_column = get_table_column("User", "email_signature")
self.assertEqual(email_sig_column.index, 1)
def execute():
if '__OldAuth' not in frappe.db.get_tables():
frappe.db.sql_ddl('''alter table `__Auth` rename `__OldAuth`''')
create_auth_table()
# user passwords
frappe.db.sql(
'''insert ignore into `__Auth` (doctype, name, fieldname, `password`)
(select 'User', user, 'password', `password` from `__OldAuth`)''')
frappe.db.commit()
# other password fields
for doctype in frappe.db.sql_list(
'''select distinct parent from `tabDocField`
where fieldtype="Password" and parent != "User"'''):
frappe.reload_doctype(doctype)
meta = frappe.get_meta(doctype)
for df in meta.get('fields', {'fieldtype': 'Password'}):
if meta.issingle:
password = frappe.db.get_value(doctype, doctype, df.fieldname)
if password:
set_encrypted_password(doctype,
doctype,
password,
fieldname=df.fieldname)
frappe.db.set_value(doctype, doctype, df.fieldname,
'*' * len(password))
else:
for d in frappe.db.sql(
'''select name, `{fieldname}` from `tab{doctype}`
where `{fieldname}` is not null'''.format(fieldname=df.fieldname,
doctype=doctype),
as_dict=True):
set_encrypted_password(doctype,
d.name,
d.get(df.fieldname),
fieldname=df.fieldname)
frappe.db.sql(
'''update `tab{doctype}` set `{fieldname}`=repeat("*", char_length(`{fieldname}`))'''
.format(doctype=doctype, fieldname=df.fieldname))
frappe.db.commit()
frappe.db.sql_ddl('''drop table `__OldAuth`''')
