def register_new_person(fname="",
lname="",
salutation="",
email="",
title="",
organisation="",
function="",
check=""):
now = datetime.now()
status = "rejected"
if check == "{0:04d}{1:02d}{2:02d}".format(now.year, now.month, now.day):
new_person = create_person(company=organisation,
first_name=fname,
last_name=lname,
title=title,
salutation=salutation,
email=email,
phone=None,
function=function,
street=None,
pincode=None,
city=None,
source="from web form (newsletter)")
if new_person:
status = "completed - new person {0}".format(new_person)
else:
status = "completed - already in database"
add_log(
title=_("Web contact form received"),
message=(
_("Import of person ({0} {1} chk{3}) from web form {2}.")).format(
fname, lname, status, check),
topic="Web form (newsletter)")
return
def get_campaigns(list_id):
config = frappe.get_single("MailChimp Settings")
if not config.host or not config.api_key:
frappe.throw(
_("No configuration found. Please make sure that there is a MailChimpConnector configuration"
))
if config.verify_ssl != 1:
verify_ssl = False
else:
verify_ssl = True
url = "{0}/campaigns?fields=campaigns.id,campaigns.status,campaigns.settings.title".format(
config.host, list_id)
raw = execute(url, config.api_key, None, verify_ssl, method="GET")
results = json.loads(raw)
for campaign in results['campaigns']:
try:
erp_campaign = frappe.get_doc("Campaign",
campaign['settings']['title'])
# update if applicable
except:
# erp does not know this campaignyet, create
new_campaign = frappe.get_doc({'doctype': 'Campaign'})
new_campaign.campaign_name = campaign['settings']['title']
new_campaign.insert()
add_log(
title=_("Sync complete"),
message=(_("Sync of campaigns from {0} completed.")).format(list_id),
topic="MailChimp")
return {'campaigns': results['campaigns']}
def enqueue_get_campaigns(list_id):
add_log(title=_("Starting sync"),
message=(_("Starting to sync campaigns from {0}")).format(list_id),
topic="MailChimp")
kwargs = {'list_id': list_id}
enqueue("lifefair.lifefair.mailchimp.get_campaigns",
queue='long',
timeout=15000,
**kwargs)
frappe.msgprint(
_("Queued for syncing. It may take a few minutes to an hour."))
return
def enqueue_sync_contacts(list_id, type="Alle", meeting=None, owner=None):
add_log(title=_("Starting sync"),
message=(_("Starting to sync contacts to {0}")).format(list_id),
topic="MailChimp")
kwargs = {
'list_id': list_id,
'type': type,
'meeting': meeting,
'owner': owner
}
enqueue("lifefair.lifefair.mailchimp.sync_contacts",
queue='long',
timeout=15000,
**kwargs)
frappe.msgprint(
_("Queued for syncing. It may take a few minutes to an hour."))
return
def import_xing(content, meeting):
new_regs = []
new_pers = []
# field definition
TICKETNO = 0 # ticket number cell A
BARCODE = 1 # barcode cell B
REMARKS = 2 # remarks field ("Kategorie")
STATUS = 4 # status ['Bezahlt', 'Storniert', 'Versendet']
DATE = 5 # order date (13.07.2018, 16:56)
SALUTATION = 10
TITLE = 11
FIRST_NAME = 12
LAST_NAME = 13
EMAIL = 14
COMPANY = 15
PHONE = 50 # AY
STREET = 26 # AA
PINCODE = 28 # AC
CITY = 29 # AD
FUNCTION = 49 # AX
BLOCK = 52 # BA, contains IF.xx
TYPE = 3
PAYMENT = 44 # AS
INVOICENO = 43 # AR
CODE = 47 # AV, "Gutscheincode", e.g. "S18STAFF"
PARTICIPATION = 51 # AZ, "Ich nehme teil"
isfirst = True
# read csv
elements = csv.reader(content.splitlines(), dialect=csv.excel)
counter = 0
# process elements
for element in elements:
if isfirst:
isfirst = False
# find colums codes
i = 0
for column in element:
if column == "Ticketnummer":
TICKETNO = i
elif column == "Barcode":
BARCODE = i
elif column == "Kategorie":
REMARKS = i
elif column == "Status":
STATUS = i
elif column == "Bestelldatum":
DATE = i
elif column == "Anrede":
SALUTATION = i
elif column == "Titel":
TITLE = i
elif column == "Vorname":
FIRST_NAME = i
elif column == "Nachname":
LAST_NAME = i
elif column == "Ticket-Email":
EMAIL = i
elif column == "Firma":
COMPANY = i
elif column == "Telefon":
PHONE = i
elif column == "Strasse":
STREET = i
elif column == "Postleitzahl":
PINCODE = i
elif column == "Ort":
CITY = i
elif column == "Funktion":
FUNCTION = i
elif "Innovationsforen" in column:
BLOCK = i
elif column == "Ticketart":
TYPE = i
elif column == "Bezahlart":
PAYMENT = i
elif column == "Rechnungsnummer":
INVOICENO = i
elif column == "Gutscheincode":
CODE = i
elif "Ich nehme teil" in column:
PARTICIPATION = i
i += 1
continue
counter += 1
# check if the ticket is already in the database
db_regs = frappe.get_all("Registration",
filters={'ticket_number': element[TICKETNO]},
fields=['name'])
if db_regs:
# ticket is already in the database, update
reg = frappe.get_doc("Registration", db_regs[0]['name'])
status = parse_status(element[STATUS])
reg.status = status
reg.type = element[TYPE]
reg.payment = element[PAYMENT]
reg.invoice_number = element[INVOICENO]
reg.phone = element[PHONE]
reg.code = element[CODE]
reg.participation = element[PARTICIPATION]
# find block
block = find_block(element[BLOCK], meeting)
reg.block = block
try:
reg.save()
frappe.db.commit()
except Exception as e:
frappe.log_error("Import Xing Error",
"Update Registration failed. {0}".format(e))
else:
# ticket is not in the database, create
# check email address to find person
db_person = frappe.get_all("Person",
filters={'email': element[EMAIL]},
fields=['name'])
# iterate over email2 and email3 in case of no hit
if not db_person:
db_person = frappe.get_all("Person",
filters={'email2': element[EMAIL]},
fields=['name'])
if not db_person:
db_person = frappe.get_all(
"Person",
filters={'email3': element[EMAIL]},
fields=['name'])
if db_person:
person_name = db_person[0]['name']
# get person, check website_description and update if empty
person = frappe.get_doc("Person", db_person[0]['name'])
if not person.website_description:
person.website_description = "{0}, {1}".format(
element[FUNCTION], element[COMPANY])
person.save()
else:
# person not found, create new person
new_person = create_person(company=element[COMPANY],
first_name=element[FIRST_NAME],
last_name=element[LAST_NAME],
title=element[TITLE],
salutation=element[SALUTATION],
email=element[EMAIL],
phone=element[PHONE],
function=element[FUNCTION],
street=element[STREET],
pincode=element[PINCODE],
city=element[CITY],
source="from xing")
if new_person:
new_pers.append(new_person)
person_name = new_person
else:
frappe.log_error(
"Import Xing Error",
"Failed to insert person {0} {1} (Ticket: {2})".format(
element[FIRST_NAME], element[LAST_NAME],
element[TICKETNO]))
# create the new registration
# find block
block = find_block(element[BLOCK], meeting)
# parse date stamp (13.07.2018, 16:56)
date_fields = element[DATE].split(',')[0].split('.')
if len(date_fields) >= 3:
# proper date stamp
date = "{0}-{1}-{2}".format(date_fields[2], date_fields[1],
date_fields[0])
else:
# found float timestamp
zerodate = datetime.datetime(1899, 12, 30)
delta = datetime.timedelta(days=float(element[DATE]))
converted_date = zerodate + delta
date = "{year:04d}-{month:02d}-{day:02d}".format(
year=converted_date.year,
month=converted_date.month,
day=converted_date.day)
# parse status ['Bezahlt', 'Storniert', 'Versendet'] > [Tentative, Confirmed, Cancelled, Paid, Sent]
status = parse_status(element[STATUS])
try:
registration = frappe.get_doc({
'doctype':
"Registration",
'person':
person_name,
'meeting':
meeting,
'block':
block,
'date':
date,
'remarks':
element[REMARKS],
'ticket_number':
element[TICKETNO],
'barcode':
element[BARCODE],
'type':
element[TYPE],
'payment':
element[PAYMENT],
'invoice_number':
element[INVOICENO],
'phone':
element[PHONE],
'status':
status,
'code':
element[CODE],
'participation':
element[PARTICIPATION]
})
registration = registration.insert()
reg_name = registration.name
frappe.db.commit()
new_regs.append(reg_name)
except Exception as e:
frappe.log_error(
"Import Xing Error",
"Insert Registration failed. {0} (Ticket: {1})".format(
e, element[TICKETNO]))
add_log(
title=_("Xing Import complete"),
message=(_("Import of {0} registrations ({1}) and {2} contacts ({3}).")
).format(len(new_regs), new_regs, len(new_pers), new_pers),
topic="Xing")
return {'registrations': new_regs, 'people': new_pers}
def sync_contacts(list_id, type, meeting=None, owner=None):
# get settings
config = frappe.get_single("MailChimp Settings")
if not config.host or not config.api_key:
frappe.throw(
_("No configuration found. Please make sure that there is a MailChimp configuration"
))
if config.verify_ssl != 1:
verify_ssl = False
else:
verify_ssl = True
# get the ERP contact list
if type.lower() == "alle":
sql_query = """SELECT
`tabPerson`.`name` AS `name`,
`tabPerson`.`letter_salutation` AS `letter_salutation`,
`tabPerson`.`salutation` AS `salutation`,
`tabPerson`.`email` AS `email`,
`tabPerson`.`do_not_contact` AS `do_not_contact`,
`tabPerson`.`first_name` AS `first_name`,
`tabPerson`.`last_name` AS `last_name`,
`tabPerson`.`primary_organisation` AS `organisation`,
'Person' AS `doctype`
FROM
`tabPerson`
WHERE
`tabPerson`.`email` LIKE '%@%.%'
UNION SELECT
`tabPublic Mail`.`name` AS `name`,
`tabPublic Mail`.`letter_salutation` AS `letter_salutation`,
'' AS `salutation`,
`tabPublic Mail`.`email` AS `email`,
`tabPublic Mail`.`do_not_contact` AS `do_not_contact`,
'' AS `first_name`,
'' AS `last_name`,
`tabPublic Mail`.`organisation` AS `organisation`,
'Public Mail' AS `doctype`
FROM
`tabPublic Mail`
WHERE
`tabPublic Mail`.`email` LIKE '%@%.%'"""
else:
sql_query = """SELECT
`tabPerson`.`name` AS `name`,
`tabPerson`.`letter_salutation` AS `letter_salutation`,
`tabPerson`.`salutation` AS `salutation`,
`tabPerson`.`email` AS `email`,
`tabPerson`.`do_not_contact` AS `do_not_contact`,
`tabPerson`.`first_name` AS `first_name`,
`tabPerson`.`last_name` AS `last_name`,
`tabPerson`.`primary_organisation` AS `organisation`,
'Person' AS `doctype`
FROM
`tabPerson`
WHERE
`tabPerson`.`email` LIKE '%@%.%'
AND `tabPerson`.`is_vip` = 0
AND !( `tabPerson`.`name` IN (SELECT
`tabRegistration`.`person`
FROM `tabRegistration`
WHERE `tabRegistration`.`meeting` = '{meeting}'))
UNION SELECT
`tabPublic Mail`.`name` AS `name`,
`tabPublic Mail`.`letter_salutation` AS `letter_salutation`,
'' AS `salutation`,
`tabPublic Mail`.`email` AS `email`,
`tabPublic Mail`.`do_not_contact` AS `do_not_contact`,
'' AS `first_name`,
'' AS `last_name`,
`tabPublic Mail`.`organisation` AS `organisation`,
'Public Mail' AS `doctype`
FROM
`tabPublic Mail`
WHERE
`tabPublic Mail`.`email` LIKE '%@%.%'""".format(
meeting=meeting)
erp_members = frappe.db.sql(sql_query, as_dict=True)
# get all members from the MailChimp list
repeat = True
offset = 0
while repeat:
mc_members = get_members(list_id, count=1000, offset=offset)['members']
if len(mc_members) > 0:
for mc_member in mc_members:
# print(mc_member['email_address'])
check_mc_member_in_erp(mc_member, erp_members)
offset += 1000
else:
repeat = False
# now get all erp members to MailChmp
contact_written = []
for erp_member in erp_members:
# compute mailchimp id (md5 hash of lower-case email)
try:
mc_id = hashlib.md5(erp_member['email'].lower()).hexdigest()
except:
frappe.log_error(
"Invalid email address (unable to compute hash): {0}".format(
erp_member['email']), "Invalid email address")
# load subscription status from mailchimp if it is set as master
# default is unsubscribed
contact_status = "unsubscribed"
if erp_member['do_not_contact'] == 1:
contact_status = "unsubscribed"
else:
contact_status = "subscribed"
url = "{0}/lists/{1}/members/{2}".format(config.host, list_id, mc_id)
method = "PUT"
contact_object = {
"email_address": erp_member['email'],
"status": contact_status,
"merge_fields": {
"FNAME": erp_member['first_name'] or "",
"LNAME": erp_member['last_name'] or "",
"TITEL": erp_member['salutation'] or "",
"ANREDE": erp_member['letter_salutation'] or ""
}
}
raw = execute(host=url,
api_token=config.api_key,
payload=contact_object,
verify_ssl=verify_ssl,
method=method)
print("Updated to MailChimp {0}: {1}".format(erp_member['email'], raw))
contact_written.append(erp_member['email'])
url = "{0}/lists/{1}/members?fields=members.id,members.email_address,members.status".format(
config.host, list_id)
raw = execute(url, config.api_key, None, verify_ssl)
results = json.loads(raw)
if owner:
frappe.publish_realtime(
event='msgprint',
message=_("Synchronisation to MailChimp complete"),
user=owner)
add_log(title=_("Sync complete"),
message=(_("Sync to {0} completed, written {1} contacts.")).format(
list_id, len(contact_written)),
topic="MailChimp")
return {'members': results['members']}
