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)