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)
