def generate_data_from_excel(file_doc, extension, as_dict=False):
content = file_doc.get_content()
if extension == "xlsx":
rows = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
rows = read_xls_file_from_attached_file(content)
data = []
headers = rows[0]
del rows[0]
for row in rows:
if as_dict:
data.append({
frappe.scrub(header): row[index]
for index, header in enumerate(headers)
})
else:
if not row[1]:
row[1] = row[0]
row[3] = row[2]
data.append(row)
return data
def upload_csv_bank_statement():
if frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"csv".encode("utf-8")):
from frappe.utils.csvutils import read_csv_content
rows = read_csv_content(frappe.local.uploaded_file)
elif frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"xlsx".encode("utf-8")):
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
rows = read_xlsx_file_from_attached_file(
fcontent=frappe.local.uploaded_file)
elif frappe.safe_encode(frappe.local.uploaded_filename).lower().endswith(
"xls".encode("utf-8")):
from frappe.utils.xlsxutils import read_xls_file_from_attached_file
rows = read_xls_file_from_attached_file(frappe.local.uploaded_file)
else:
frappe.throw(_("Please upload a csv, xls or xlsx file"))
column_row = rows[0]
columns = [{"field": x, "label": x} for x in column_row]
rows.pop(0)
data = []
for row in rows:
data.append(dict(zip(column_row, row)))
return {"columns": columns, "data": data}
def setup_warehouses():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_warehouses:
delete_nongroup_warehouse_groups()
if not frappe.db.exists("Warehouse", 'General Warehouse - ' + abbr):
doc = frappe.new_doc("Warehouse")
doc.warehouse_name = 'General Warehouse'
doc.parent_warehouse = frappe.db.sql(
"select name from `tabWarehouse` where is_group = 1 order by creation asc limit 1"
)[0][0]
doc.insert()
elif system_doc.warehouses_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.warehouses_attachment})
filename = file.get_full_path()
company = frappe.db.get_single_value('Global Defaults',
'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_warehouse_groups()
for index, row in enumerate(rows):
if index != 0:
if not frappe.db.exists("Warehouse",
row[0] + ' - ' + abbr):
doc = frappe.new_doc("Warehouse")
doc.warehouse_name = row[0]
doc.parent_warehouse = frappe.db.sql(
"select name from `tabWarehouse` where is_group = 1 order by creation asc limit 1"
)[0][0]
doc.insert()
def setup_suppliers():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_suppliers:
delete_nongroup_supplier_groups()
insert_suppliers_group(company, "General Group")
if not frappe.db.exists("Supplier", "General Supplier"):
doc = frappe.new_doc("Supplier")
doc.supplier_name = "General Supplier"
doc.supplier_group = "General Group"
doc.supplier_type = "Individual"
doc.insert()
elif system_doc.suppliers_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.suppliers_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_supplier_groups()
for index, row in enumerate(rows):
if index != 0:
insert_suppliers_group(company, row[1])
if not frappe.db.exists("Supplier", row[0]):
doc = frappe.new_doc("Supplier")
doc.supplier_name = row[0]
doc.supplier_group = row[1]
if row[2].lower() == "individual":
doc.supplier_type = "Individual"
elif row[20].lower() == "company":
doc.supplier_type = "Company"
else:
frappe.throw(
_("Supplier Type column values must be Company or Individual"
))
doc.insert()
def read_content(self, content, extension): if extension == "csv": data = read_csv_content(content) elif extension == "xlsx": data = read_xlsx_file_from_attached_file(fcontent=content) elif extension == "xls": data = read_xls_file_from_attached_file(content) self.header_row = data[0] self.data = data[1:]
def setup_customers(self):
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company", filters = {'name': company}, fieldname = 'abbr')
if self.no_customers:
self.delete_nongroup_customer_groups()
self.delete_nongroup_territories()
self.insert_customers_group(company, "General Group")
self.insert_territories("General Territory")
if not frappe.db.exists("Customer", "General Customer"):
doc = frappe.new_doc("Customer")
doc.customer_name = "General Customer"
doc.customer_group = "General Group"
doc.territory = "General Territory"
doc.insert()
elif self.customers_attachment:
file = frappe.get_doc("File", {"file_url": self.customers_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding = "utf8") as infile:
if frappe.safe_encode(filename).lower().endswith("csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith("xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith("xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"))
self.delete_nongroup_customer_groups()
self.delete_nongroup_territories()
for index, row in enumerate(rows):
if index != 0:
self.insert_customers_group(company, row[1])
self.insert_territories(row[2])
if not frappe.db.exists("Customer", row[0]):
doc = frappe.new_doc("Customer")
doc.customer_name = row[0]
doc.customer_group = row[1]
doc.territory = row[2]
doc.insert()
else:
frappe.throw(_("Please attach a file"))
def read_content(self, content, extension):
error_title = _("Template Error")
if extension not in ("csv", "xlsx", "xls"):
frappe.throw(
_("Import template should be of type .csv, .xlsx or .xls"),
title=error_title)
if extension == "csv":
data = read_csv_content(content)
elif extension == "xlsx":
data = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
data = read_xls_file_from_attached_file(content)
return data
def read_content(self, content, extension):
error_title = _("Template Error")
if extension not in ("csv", "xlsx", "xls"):
frappe.throw(
_("Import template should be of type .csv, .xlsx or .xls"), title=error_title
)
if extension == "csv":
data = read_csv_content(content)
elif extension == "xlsx":
data = read_xlsx_file_from_attached_file(fcontent=content)
elif extension == "xls":
data = read_xls_file_from_attached_file(content)
if len(data) <= 1:
frappe.throw(
_("Import template should contain a Header and atleast one row."), title=error_title
)
self.header_row = data[0]
self.data = data[1:]
def setup_items():
system_doc = frappe.get_doc("System Setup")
company = frappe.db.get_single_value('Global Defaults', 'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
if system_doc.no_items:
delete_nongroup_item_groups()
#delete items also
insert_items_group(company, "General Group")
if not frappe.db.exists("Item", {"item_code": "General Stock"}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = "General Stock"
i_doc.description = "General Stock"
i_doc.is_stock_item = 1
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = "General Group"
i_doc.insert()
if not frappe.db.exists("Item", {"item_code": "General Service"}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = "General Service"
i_doc.description = "General Service"
i_doc.is_stock_item = 0
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = "General Group"
i_doc.insert()
elif system_doc.items_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.items_attachment})
filename = file.get_full_path()
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_item_groups()
for index, row in enumerate(rows):
if index != 0:
insert_items_group(company, row[3])
if not frappe.db.exists("Item", {"item_code": row[0]}):
i_doc = frappe.new_doc("Item")
i_doc.item_code = row[0]
i_doc.description = row[1]
if row[2].lower() == "service":
i_doc.is_stock_item = 0
elif row[2].lower() == "stock":
i_doc.is_stock_item = 1
else:
frappe.throw(
_("Item Type must be Service or Stock"))
i_doc.include_item_in_manufacturing = 0
i_doc.item_group = row[3]
i_doc.insert()
def setup_employees_and_users():
system_doc = frappe.get_doc("System Setup")
if system_doc.employees_attachment:
file = frappe.get_doc("File",
{"file_url": system_doc.employees_attachment})
filename = file.get_full_path()
company = frappe.db.get_single_value('Global Defaults',
'default_company')
abbr = frappe.get_value("Company",
filters={'name': company},
fieldname='abbr')
with open(filename, "r", encoding="utf8") as infile:
if frappe.safe_encode(filename).lower().endswith(
"csv".encode('utf-8')):
rows = read_csv_content(infile.read())
elif frappe.safe_encode(filename).lower().endswith(
"xls".encode('utf-8')):
content = file.get_content()
rows = read_xls_file_from_attached_file(fcontent=content)
elif frappe.safe_encode(filename).lower().endswith(
"xlsx".encode('utf-8')):
content = file.get_content()
rows = read_xlsx_file_from_attached_file(fcontent=content)
else:
frappe.throw(
_("Only CSV and Excel files can be used to for importing data. Please check the file format you are trying to upload"
))
delete_nongroup_departments()
delete_employment_types()
delete_genders()
delete_designations()
delete_leave_types()
delete_salary_components()
insert_salary_components()
insert_leave_types(system_doc.annual_leave_type)
insert_leave_policy()
for index, row in enumerate(rows):
if index != 0:
if not row[0]:
frappe.throw(_("Employee Number column is mandatory"))
if not row[1]:
frappe.throw(_("Password column is mandatory"))
if not row[2]:
frappe.throw(_("First Name column is mandatory"))
user_email = row[0] + '@' + company + '.com'
insert_user(user_email, row[2], row[1], last_name=row[3])
if row[9]:
insert_department(company, abbr, row[9])
else:
frappe.throw(_("Department column is mandatory"))
if row[10]:
insert_designation(row[10])
else:
frappe.throw(_("Designation column is mandatory"))
if row[10]:
insert_employment_type(row[8])
if row[4]:
insert_gender(row[4])
if not row[4]:
frappe.throw(_("Gender column is mandatory"))
if not row[6]:
frappe.throw(_("Date of Birth column is mandatory"))
if not row[7]:
frappe.throw(_("Date of Joining column is mandatory"))
set_employee_as_employee_number()
if not frappe.db.exists("Employee", row[0]):
doc = frappe.new_doc("Employee")
doc.employee_number = row[0]
doc.first_name = row[2]
doc.last_name = row[3]
doc.company = company
doc.gender = row[4]
doc.country = row[5]
doc.date_of_birth = row[6]
doc.date_of_joining = row[7]
doc.employment_type = row[8]
doc.department = row[9] + ' - ' + abbr
doc.designation = row[10]
doc.cell_number = row[11]
doc.user_id = row[0] + '@' + company + '.com'
doc.leave_policy = frappe.db.sql(
"select name from `tabLeave Policy` order by creation desc limit 1"
)[0][0]
doc.insert(ignore_permissions=True)
frappe.db.commit()
allocate_leaves(system_doc.annual_leave_type, row[0],
row[18])
if not row[12]:
frappe.throw(_("Basic Salary column is mandatory"))
else:
basic = row[12]
doj = row[7]
transportation = row[13]
housing = row[14]
mobile = row[15]
others = row[16]
gosi = row[17]
if not transportation:
transportation = 0
if not housing:
housing = 0
if not mobile:
mobile = 0
if not others:
others = 0
if not gosi:
gosi = "No"
insert_allowances(row[0], basic, doj, transportation,
housing, mobile, others, gosi)
