def import_crm_file(file_name): LOGGER.info("Loading data from " + file_name) workbook = load_workbook(file_name) sheet = workbook.get_sheet_by_name('email list') row_index = 1 # Reading header header = [] for column_index in range(1, sheet.get_highest_column() + 1): value = sheet.cell(row = row_index, column=column_index).value if value!=None: header.append(value.strip() if value!='' else header[-1]) else: break LOGGER.info('Using header:' + str(header)) LOGGER.info('Rows:' + str(sheet.get_highest_row())) LOGGER.info('Columns:' + str(sheet.get_highest_column())) company_type = Attributes.objects.get( active=True, type='container_type', identifier='CONT_COMPANY') person_type = Attributes.objects.get( active=True, type='container_type', identifier='CONT_PERSON') active = Attributes.objects.get(active=True, type='status', identifier='STATUS_ACTIVE') closed = Attributes.objects.get(active=True, type='status', identifier='STATUS_CLOSED') main_address = Attributes.objects.get(active=True, type='address_type', identifier='ADR_MAIN_OFFICE') secondary_address = Attributes.objects.get(active=True, type='address_type', identifier='ADR_SUBSI_OFFICE') web_address = Attributes.objects.get(active=True, type='address_type', identifier='ADR_WEB') mobile_work_phone = Attributes.objects.get(active=True, type='phone_type', identifier='PHONE_MOB_WORK') land_work_phone = Attributes.objects.get(active=True, type='phone_type', identifier='PHONE_LAND_WORK') email_work = Attributes.objects.get(active=True, type='email_type', identifier='EMAIL_WORK') email_contact = Attributes.objects.get(active=True, type='email_type', identifier='EMAIL_CONTACT') unknown_title = Attributes.objects.get(Q(identifier='CMR_UNKNOWN'), Q(type='company_member_role'), Q(active=True)) row_index += 1 company_name_index = header.index('Company') + 1 description_index = header.index('Company Info') + 1 active_index = header.index('Dead/Alive') + 1 address_line_1_index = header.index('Address') + 1 address_zip_index = header.index('Zip Code') + 1 address_city_index = header.index('City') + 1 address_country_index = header.index('Country') + 1 website_index = header.index('Internet site') + 1 person_first_index = header.index('First name') + 1 person_last_index = header.index('Family name') + 1 person_title_index = header.index('Title') + 1 person_office_phone_index = header.index('Office phone') + 1 person_mobile_phone_index = header.index('Mobile') + 1 person_email_index = header.index('Email') + 1 universe = Universe() universe.name = 'CRM Universe ' + str(datetime.datetime.now()) universe.short_name = str(datetime.datetime.now()) universe.type = Attributes.objects.get(identifier='CONT_UNIVERSE') universe.inception_date = datetime.date.today() universe.closed_date = None universe.status = Attributes.objects.get(identifier='STATUS_ACTIVE') universe.public = True universe.owner = User.objects.get(id=1) universe.description = "This universe contains all CRM related companies as of " + str(datetime.datetime.now()) + "." universe.save() treated_company = [] while row_index<=sheet.get_highest_row(): LOGGER.info("Working on row:" + str(row_index)) if sheet.cell(row = row_index, column=company_name_index).value==None or sheet.cell(row = row_index, column=company_name_index).value=='': person_first = sheet.cell(row = row_index, column=person_first_index).value.strip() if sheet.cell(row = row_index, column=person_first_index).value!=None and sheet.cell(row = row_index, column=person_first_index).value!='' else None person_last = sheet.cell(row = row_index, column=person_last_index).value.strip().upper() if sheet.cell(row = row_index, column=person_last_index).value!=None and sheet.cell(row = row_index, column=person_last_index).value!='' else None if person_first!=None and person_last!=None: company_name = person_last + ' ' + person_first company = CompanyContainer.objects.filter(Q(name=company_name) | Q(short_name=company_name)) else: row_index += 1 continue else: company_name = sheet.cell(row = row_index, column=company_name_index).value.strip() company = CompanyContainer.objects.filter(Q(name=company_name) | Q(short_name=company_name)) is_active = sheet.cell(row = row_index, column=active_index).value.strip().upper()!='DEAD' if sheet.cell(row = row_index, column=active_index).value!=None else True if company.exists(): company = company[0] LOGGER.info("Company " + company_name + " already exists, updating.") if company_name not in treated_company: for phone in company.phones.all(): company.phones.remove(phone) company.save() phone.delete() for address in company.addresses.all(): company.addresses.remove(address) company.save() address.delete() for member in company.members.all(): company.members.remove(member) company.save() member.delete() else: LOGGER.info("Company " + company_name + " is being created.") company = CompanyContainer() company.type = company_type company.name = company_name company.short_name = company_name company.status = active if is_active else closed company.save() company.short_description = sheet.cell(row = row_index, column=description_index).value.strip() if sheet.cell(row = row_index, column=description_index).value!=None else None country_name = sheet.cell(row = row_index, column=address_country_index).value.strip().upper() if sheet.cell(row = row_index, column=address_country_index).value!=None else '' country_attribute = Attributes.objects.filter(active=True, type='country_iso2', name__iexact=country_name) if country_attribute.exists(): country_attribute = country_attribute[0] else: country_attribute = None if country_attribute!=None \ or sheet.cell(row = row_index, column=address_line_1_index).value!=None \ or sheet.cell(row = row_index, column=address_zip_index).value!=None \ or sheet.cell(row = row_index, column=address_city_index).value!=None: line_1_value = sheet.cell(row = row_index, column=address_line_1_index).value.strip().upper() if sheet.cell(row = row_index, column=address_line_1_index).value!=None else None zip_code_value = str(sheet.cell(row = row_index, column=address_zip_index).value).strip().upper() if sheet.cell(row = row_index, column=address_zip_index).value!=None else None city_value = sheet.cell(row = row_index, column=address_city_index).value.strip().upper() if sheet.cell(row = row_index, column=address_city_index).value!=None else None addresses = company.addresses.filter(line_1=line_1_value, zip_code=zip_code_value, city=city_value, country=country_attribute) if not addresses.exists(): company.save() address = Address() address.address_type = main_address if len(addresses)==0 else secondary_address address.line_1 = line_1_value address.line_2 = None address.zip_code = zip_code_value address.city = city_value address.country = country_attribute address.save() company.addresses.add(address) company.save() if sheet.cell(row = row_index, column=website_index).value!=None and sheet.cell(row = row_index, column=website_index).value!='': website_url = str(sheet.cell(row = row_index, column=website_index).value).strip() website = company.addresses.filter(address_type=web_address, line_1=website_url) if not website.exists() and website_url!='' and website_url!=None: website = Address() website.address_type = web_address website.line_1 = website_url website.line_2 = None website.zip_code = None website.city = None website.country = None website.save() company.addresses.add(website) company.save() person_first = sheet.cell(row = row_index, column=person_first_index).value.strip() if sheet.cell(row = row_index, column=person_first_index).value!=None and sheet.cell(row = row_index, column=person_first_index).value!='' else None person_last = sheet.cell(row = row_index, column=person_last_index).value.strip().upper() if sheet.cell(row = row_index, column=person_last_index).value!=None and sheet.cell(row = row_index, column=person_last_index).value!='' else None if person_first!=None and person_last==None: person_last = company_name if person_first!=None and person_last!=None: persons = PersonContainer.objects.filter(last_name=person_last, first_name=person_first) if not persons.exists(): LOGGER.info("Person " + person_last + ' ' + person_first + " is being created.") person = PersonContainer() person.first_name = person_first person.last_name = person_last person.birth_date = None person.name = person_last + ' ' + person_first person.type = person_type person.status = active if is_active else closed person.save() else: LOGGER.info("Person " + person_last + ' ' + person_first + " already exists, updating.") person = persons[0] for phone in person.phones.all(): person.phones.remove(phone) person.save() phone.delete() for email in person.emails.all(): person.emails.remove(email) person.save() email.delete() if sheet.cell(row = row_index, column=person_title_index).value!=None and sheet.cell(row = row_index, column=person_title_index).value!='': titles = sheet.cell(row = row_index, column=person_title_index).value.split('/') for title in titles: current_title = Attributes.objects.filter(Q(short_name__iexact=title.strip()) | Q(name__iexact=title.strip()), Q(type='company_member_role'), Q(active=True)) LOGGER.info("Person " + person_last + ' ' + person_first + " is assigned as " + title + ".") member = CompanyMember() member.person = person if current_title.exists(): member.role = current_title[0] else: LOGGER.warn("Title [" + title + "] doesn't exists please correct your data or add that role to the system." ) member.role = unknown_title member.save() company.members.add(member) else: LOGGER.warn("Person " + person_last + ' ' + person_first + " has no title within the company.") member = CompanyMember() member.person = person member.role = unknown_title member.save() company.members.add(member) if sheet.cell(row = row_index, column=person_mobile_phone_index).value!=None and sheet.cell(row = row_index, column=person_mobile_phone_index).value!='': mobile = Phone() mobile.phone_number = sheet.cell(row = row_index, column=person_mobile_phone_index).value.strip() mobile.line_type = mobile_work_phone mobile.save() person.phones.add(mobile) person.save() if sheet.cell(row = row_index, column=person_office_phone_index).value!=None and sheet.cell(row = row_index, column=person_office_phone_index).value!='': land = Phone() land.phone_number = sheet.cell(row = row_index, column=person_office_phone_index).value.strip() land.line_type = land_work_phone land.save() person.phones.add(land) person.save() if sheet.cell(row = row_index, column=person_email_index).value!=None and sheet.cell(row = row_index, column=person_email_index).value!='': email = Email() email.email_address = sheet.cell(row = row_index, column=person_email_index).value.strip() email.address_type = email_work email.save() person.emails.add(email) person.save() else: if sheet.cell(row = row_index, column=person_email_index).value!=None and sheet.cell(row = row_index, column=person_email_index).value!='': for email in company.emails.all(): company.emails.remove(email) company.save() email.delete() email = Email() email.email_address = sheet.cell(row = row_index, column=person_email_index).value.strip() email.address_type = email_contact email.save() company.emails.add(email) company.save() universe.members.add(company) if company_name not in treated_company: treated_company.append(company_name) row_index += 1 universe.save()
def load_swm_map_file(path_to_file): workbook = load_workbook(path_to_file) sheet = workbook.get_sheet_by_name(name='SWM') row_index = 1 sequoia_map = external_content.get_sequoia_map() # Reading header header = [] for column_index in range(1, sheet.get_highest_column() + 1): value = sheet.cell(row = row_index, column=column_index).value header.append(value if value!='' and value!=None else header[-1]) row_index = row_index + 1 LOGGER.info("Using the following header: " + str(header)) while row_index<=sheet.get_highest_row(): LOGGER.info("Working on " + sheet.cell(row = row_index, column=1).value) current_name = sheet.cell(row = row_index, column=1).value portfolio = PortfolioContainer.objects.filter(Q(name=current_name) | Q(short_name=current_name)) if portfolio.exists(): portfolio = portfolio[0] else: portfolio = PortfolioContainer() portfolio.name = current_name portfolio.short_name = current_name portfolio.inception_date = sheet.cell(row = row_index, column=2).value portfolio.currency = Attributes.objects.get(short_name__iexact=sheet.cell(row = row_index, column=3).value, type='currency', active=True) portfolio.status = Attributes.objects.get(type='status', identifier='STATUS_ACTIVE', active=True) portfolio.type = Attributes.objects.get(type='container_type', identifier='CONT_PORTFOLIO', active=True) portfolio.save() associated_bank = portfolio.associated_companies.filter(Q(company__name__iexact=sheet.cell(row = row_index, column=7).value) | Q(company__short_name__iexact=sheet.cell(row = row_index, column=7).value), role__identifier='SCR_BANK') if not associated_bank.exists(): current_bank = portfolio.associated_companies.filter(role__identifier='SCR_BANK') if current_bank.exists(): current_bank = current_bank[0] portfolio.associated_companies.remove(current_bank) associated_bank = CompanyContainer.objects.filter(Q(name__iexact=sheet.cell(row = row_index, column=7).value) | Q(short_name__iexact=sheet.cell(row = row_index, column=7).value)) if associated_bank.exists(): associated_bank = associated_bank[0] else: associated_bank = CompanyContainer() associated_bank.name = sheet.cell(row = row_index, column=7).value associated_bank.short_name = sheet.cell(row = row_index, column=7).value associated_bank.status = Attributes.objects.get(type='status', identifier='STATUS_TO_BE_VALIDATED', active=True) associated_bank.type = Attributes.objects.get(type='container_type', identifier='CONT_COMPANY', active=True) associated_bank.save() current_bank = RelatedCompany() current_bank.role = Attributes.objects.get(identifier='SCR_BANK', active=True) current_bank.company = associated_bank current_bank.save() portfolio.associated_companies.add(current_bank) else: associated_bank = associated_bank[0].company portfolio_id = str(portfolio.id) sequoia_map[portfolio_id] = external_content.create_sequoia_map_entry(portfolio) strategy_profile = Attributes.objects.get(Q(short_name__iexact=sheet.cell(row = row_index, column=4).value) | Q(name__iexact=sheet.cell(row = row_index, column=4).value), Q(type='sequoia_strategy'), Q(active=True)) risk_profile = Attributes.objects.get(Q(short_name__iexact=sheet.cell(row = row_index, column=5).value) | Q(name__iexact=sheet.cell(row = row_index, column=5).value), Q(type='sequoia_risk'), Q(active=True)) jurisdiction = Attributes.objects.filter(Q(short_name__iexact=sheet.cell(row = row_index, column=6).value) | Q(name__iexact=sheet.cell(row = row_index, column=6).value), Q(type='country_iso2'), Q(active=True)) if jurisdiction.exists(): jurisdiction = jurisdiction[0] else: LOGGER.warn("\tJurisdiction not found [" + sheet.cell(row = row_index, column=6).value + "]") jurisdiction = None LOGGER.info("\tUsing key:" + portfolio_id) sequoia_map[portfolio_id]['strategy_profile'] = strategy_profile.identifier sequoia_map[portfolio_id]['risk_profile'] = risk_profile.identifier sequoia_map[portfolio_id]['jurisdiction'] = jurisdiction.identifier if jurisdiction!=None else None sequoia_map[portfolio_id]['bank'] = associated_bank.name # Cleaning fees setup for fee in fees_set: sequoia_map[portfolio_id][fee] = {} # Parsing ratios structure_ratio = sheet.cell(row = row_index, column=8).value sequoia_map[portfolio_id]['structure_ratio'] = structure_ratio for index in xrange(10,sheet.get_highest_column() - 3,3): if sheet.cell(row = row_index, column=index).value!=None and sheet.cell(row = row_index, column=index).value!='': key = header[index-1].upper().split(' ') charge = sequoia_dictionary[key[0]].identifier fee = sequoia_dictionary[key[1]].identifier if not sequoia_map[portfolio_id][fee].has_key(charge): sequoia_map[portfolio_id][fee][charge] = [] bud = Attributes.objects.filter(active=True, type='sequoia_bud', short_name=sheet.cell(row = row_index, column=index).value) if bud.exists(): bud = bud[0].identifier sequoia_map[portfolio_id][fee][charge].append({'rate':sheet.cell(row = row_index, column=index + 1).value * 100.0, 'bud': bud}) else: LOGGER.warn("\tBUD not found [" + str(sheet.cell(row = row_index, column=index).value) + "]") row_index = row_index + 1 external_content.set_sequoia_map(sequoia_map)