def universe_mass_mail_execute(request): user = User.objects.get(id=request.user.id) universe_id = request.POST['universe_id'] mail_subject = request.POST['mail_subject'] mail_campaign_id = request.POST['mail_campaign_id'] mail_tags = request.POST['mail_tags'] mail_test = request.POST['mail_test'] mail_content = request.POST['mail_content'] mail_attachments = [os.path.join(WORKING_PATH, attachment) for attachment in eval(request.POST['mail_attachments'].replace('"',''))] mail_ids = eval(request.POST['mail_ids']) treated_addresses = [] email_test = Email() email_test.email_address = '*****@*****.**' addressees = [] for mail_id in mail_ids: keys = mail_id.split("_") try: contact = PersonContainer.objects.get(id=keys[0]) except: contact = CompanyContainer.objects.get(id=keys[0]) email = Email.objects.get(id=keys[1]) if email.email_address not in treated_addresses or mail_test=='True': addressees.append((contact, email if mail_test!='True' else email_test)) treated_addresses.append(email.email_address) mailgun.send_message(mail_subject, mail_content, addressees, mail_attachments, mail_campaign_id, mail_tags) return HttpResponse('{"result": true, "status_message": "Mails sent"}',"json")
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()