def persons():
try:
print(request.method)
print(request.args.get('personID', None))
requestBody = utils.getJSONBody(request.get_data(as_text=True))
if (request.method in ('POST', 'PUT')):
if (len(requestBody) <= 0):
return "Error, you use POST or PATCH method but data empty. Check API documentation"
if request.method == 'POST':
if (requestBody.get('person_id', None)):
del requestBody['person_id']
data = json.dumps(postgres.upsertPerson(data=requestBody),
default=str)
else:
print(request.method)
personID = request.args.get('personID', None)
print(personID)
if (personID):
data = json.dumps(postgres.listPerson(personID=personID),
default=str)
else:
data = json.dumps(postgres.listPerson(), default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
except Exception as e:
errormessage = utils.customError("persons", e)
return errormessage
def convertTimeZone(timezone):
try:
mapTZ = {'EST': 'America/New_York', 'CET': 'Europe/Paris'}
return mapTZ.get(timezone, None)
except Exception as e:
errormessage = utils.customError("convertTimeZone", e)
print(errormessage)
raise
def get_pgcon():
try:
if 'pgcon' not in g:
g.pgcon = engine.connect()
return g.pgcon
except Exception as e:
errormessage = utils.customError("get_pgcon", e)
print(errormessage)
raise
def deleteCalendar(calendarID):
try:
service = get_calendar_service()
deleted_calendar_list_entry = service.calendars().delete(
calendarId=calendarID).execute()
return deleted_calendar_list_entry
except Exception as e:
errormessage = utils.customError("deleteCalendar", e)
print(errormessage)
raise
def updateCalendar(data):
try:
service = get_calendar_service()
updated_calendar = service.calendars().update(calendarId=data['id'],
body=data).execute()
return updated_calendar
except Exception as e:
errormessage = utils.customError("updateCalendar", e)
print(errormessage)
raise
def computeBussDayDiff(start, end):
try:
if (end > start):
daygenerator = (start + datetime.timedelta(x + 1) for x in range((end - start).days))
return sum(1 for day in daygenerator if day.weekday() < 5)
else:
print('Event date can not be the past')
return None
except Exception as e:
errormessage = utils.customError("computeBussDayDiff", e)
return errormessage
def createCalendar(summary, timeZone='America/New_York'):
try:
calendar = {'summary': summary, 'timeZone': timeZone}
service = get_calendar_service()
created_calendar_list_entry = service.calendars().insert(
body=calendar).execute()
return created_calendar_list_entry
except Exception as e:
errormessage = utils.customError("createCalendar", e)
print(errormessage)
raise
def upsertEventTagMulti(listdata):
try:
con = get_pgcon()
for data in listdata:
insert_stmt = insert(tblEventTag).values(data)
update_stmt = insert_stmt.on_conflict_do_update(
constraint='tbl_event_tag_pk', set_=data)
con.execute(update_stmt)
except Exception as e:
errormessage = utils.customError("Upsert Event Tag Multi", e)
print(errormessage)
raise
def calendars():
try:
requestBody = utils.getJSONBody(request.get_data(as_text=True))
if (request.method in ('POST', 'DELETE', 'PUT')):
if (len(requestBody) == 0):
return "Error, you use POST, DELETE or PUT method but data empty. Check API documentation"
else:
if (request.method == 'POST'):
summary = requestBody.get('summary', None)
if (summary):
timeZone = requestBody.get('time_zone', None)
if (timeZone):
created_calendar = googleCalendar.createCalendar(
summary, timeZone)
else:
created_calendar = googleCalendar.createCalendar(
summary)
data = {
'calendar_id': created_calendar['id'],
'calendar_name': created_calendar['summary'],
'platform_id': 'Google Calendar'
}
data = postgres.upsertCalendar(data)
data = json.dumps(created_calendar, default=str)
else:
return "Error, creating calendar need summary and timezone"
elif (request.method in ('DELETE', 'PUT')):
calendarID = requestBody.get('id', None)
if (calendarID):
if (request.method == 'DELETE'):
edited_calendar = googleCalendar.deleteCalendar(
calendarID)
edited_calendar = postgres.deleteCalendar(
calendarID)
elif (request.method == 'PUT'):
edited_calendar = googleCalendar.updateCalendar(
data=requestBody)
else:
return "Error, unrecognized request method"
data = json.dumps(edited_calendar, default=str)
else:
return "Error, deleting or updatingcalendar need calendar ID"
else:
data = json.dumps(googleCalendar.listCalendar(), default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
except Exception as e:
errormessage = utils.customError("calendars", e)
return errormessage
def getSheetValues(SpreadSheetID, SheetRange):
try:
print(SpreadSheetID, SheetRange)
service = getSheetService()
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SpreadSheetID,
range=SheetRange).execute()
values = result.get('values', [])
return values
except Exception as e:
errormessage = utils.customError("getSheetValues", e)
print(errormessage)
raise
def writeHistory(data, SPREADSHEET_ID, RANGE_NAME):
try:
service = getSheetService()
sheetRequest = service.spreadsheets().values().append(
spreadsheetId=SPREADSHEET_ID,
range=RANGE_NAME,
valueInputOption='RAW',
insertDataOption='INSERT_ROWS',
body=data)
response = sheetRequest.execute()
return response
except Exception as e:
errormessage = utils.customError("writeHistory", e)
print(errormessage)
raise
def getProgramList(SpreadSheetID, LiveSheet):
try:
liveValues = getSheetValues(SpreadSheetID, LiveSheet)
if (liveValues and len(liveValues) > 1):
keys = liveValues[0]
values = liveValues[1:]
values = [v for v in values if len(v) > 1 and len(v[1]) > 0]
programList = [{keys[i]: r[i]
for i in range(0, len(keys))} for r in values]
return programList
else:
return None
except Exception as e:
errormessage = utils.customError("getProgramList", e)
print(errormessage)
raise
def deleteCalendar(calendarId):
try:
con = get_pgcon()
if (calendarId):
data = con.execute(tblCalendar.delete().where(
tblCalendar.c.calendar_id == calendarId))
if (data):
return "Success to delete data calendar with ID: " + calendarId
else:
return "Failed to delete data calendar, check ID: " + calendarId + " is exist"
else:
return None
except Exception as e:
errormessage = utils.customError("deleteCalendar", e)
print(errormessage)
raise
def formatDataEvent(dataEvent):
try:
summary = dataEvent.get('event_name', 'TEST')
start = dataEvent.get('event_start', None)
duration = dataEvent.get('duration_minutes', None)
timeZone = dataEvent.get('time_zone', None)
if (not (start or duration or summary)):
return None
else:
ianaTimezone = convertTimeZone(timeZone)
if (not ianaTimezone):
ianaTimezone = 'America/New_York'
end = start + datetime.timedelta(minutes=duration)
start = start.strftime('%Y-%m-%dT%H:%M:%S')
end = end.strftime('%Y-%m-%dT%H:%M:%S')
formattedEventData = {
'summary': summary,
'start': {
'dateTime': start,
'timeZone': ianaTimezone,
},
'end': {
'dateTime': end,
'timeZone': ianaTimezone,
},
'reminders': {
'useDefault':
False,
'overrides': [
{
'method': 'email',
'minutes': 24 * 60
},
{
'method': 'popup',
'minutes': 30
},
]
},
'sendNotifications': True,
'sendUpdates': 'all'
}
return formattedEventData
except Exception as e:
errormessage = utils.customError("formatDataEvent", e)
print(errormessage)
raise
def retrieve_person(personID):
try:
if (personID):
data = postgres.listPerson(personID=personID)
else:
data = None
data = json.dumps(data, default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
except Exception as e:
errormessage = utils.customError("list-event", e)
return errormessage
def listCalendar():
try:
service = get_calendar_service()
listCalendar = []
page_token = None
while True:
calendars = service.calendarList().list(
pageToken=page_token).execute()
listCalendar.append(calendars)
page_token = calendars.get('nextPageToken')
if not page_token:
break
return listCalendar
except Exception as e:
errormessage = utils.customError("listCalendar", e)
print(errormessage)
raise
def getEventDetail(eventID):
try:
con = get_pgcon()
strsql = "select a.email, b.person_role, c.event_name, c.event_start, c.duration_minutes, c.time_zone from tbl_person a, tbl_event_person b, tbl_event c " \
"where b.person_role='Producer' and a.person_id=b.person_id and c.event_id=b.event_id and b.event_id=%s;"
data = con.execute(strsql, eventID).fetchall()
if (data):
if (isinstance(data, list)):
return [dict(row.items()) for row in data]
else:
return [dict(data.items())]
else:
return None
except Exception as e:
errormessage = utils.customError("getEventDetail", e)
print(errormessage)
raise
def get_calendar_service():
try:
if 'calservice' not in g:
token_path = os.getenv('GCALENDAR_TOKEN', '')
credential_path = os.getenv('GCALENDAR_CREDENTIALS', '')
store = file.Storage(token_path)
creds = store.get()
if not creds or creds.invalid:
flow = client.flow_from_clientsecrets(credential_path, SCOPES)
creds = tools.run_flow(flow, store)
g.calservice = build('calendar',
'v3',
http=creds.authorize(Http()))
return g.calservice
except Exception as e:
errormessage = utils.customError("get_calendar_service", e)
print(errormessage)
raise
def email_verification():
try:
data = utils.getJSONBody(request.get_data(as_text=True))
if (len(data) == 0):
return "Error, email data is empty."
eventType = data.get('event_type', None)
if (not eventType):
return "Error, event type (eod or webcast) should be set."
if (eventType == 'eod'):
spreadSheetID = os.getenv('GSHEET_EOD', '')
elif (eventType == 'webcast'):
spreadSheetID = os.getenv('GSHEET_WEBCAST', '')
else:
return "Error, event type should be in 'eod' or 'webcast'"
liveSheet = os.getenv('LIVE_SHEET', '')
userAgent = str(request.user_agent).lower()
if ('form.io' in userAgent):
data = data.get('request', None)
if (data):
data = data.get('data', None)
if (data):
result = googleSheet.emailVerification(
data=data, SPREADSHEET_ID=spreadSheetID, LIVE_SHEET=liveSheet)
print(userAgent, result)
data = json.dumps(result, default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
else:
response = flask.Response('Data request is not correct')
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
except Exception as e:
errormessage = utils.customError("email_verification", e)
print(errormessage)
return errormessage
def getCalendarID(eventID=None):
try:
con = get_pgcon()
if (eventID):
strsql = "select c.calendar_id from tbl_event a, tbl_project b, tbl_client c " \
"where b.project_id=a.project_id and c.client_id=b.client_id and a.event_id=%s;"
data = con.execute(strsql, eventID).fetchone()
else:
strsql = "select calendar_id from tbl_calendar where calendar_name='Producer Calendar';"
data = con.execute(strsql).fetchone()
if (data):
return dict(data.items())
else:
return None
except Exception as e:
errormessage = utils.customError("getCalendarID", e)
print(errormessage)
raise
def listPerson(personID=None):
try:
con = get_pgcon()
if (personID):
data = con.execute(tblPerson.select().where(
tblPerson.c.person_id == personID)).fetchone()
else:
data = con.execute(tblPerson.select()).fetchall()
if (data is not None):
if (isinstance(data, list)):
return [dict(row.items()) for row in data]
else:
return [dict(data.items())]
else:
return None
except Exception as e:
errormessage = utils.customError("listPerson", e)
print(errormessage)
raise
def listEventTag(eventID=None):
try:
con = get_pgcon()
if (eventID):
data = con.execute(tblEventTag.select().where(
tblEventTag.c.event_id == eventID)).fetchall()
else:
data = con.execute(tblEventTag.select()).fetchall()
if (data):
if (isinstance(data, list)):
return [dict(row.items()) for row in data]
else:
return [dict(data.items())]
else:
return None
except Exception as e:
errormessage = utils.customError("listEventTag", e)
print(errormessage)
raise
def listCalendar(calendarID=None):
try:
con = get_pgcon()
if (calendarID):
data = con.execute(tblCalendar.select().where(
tblCalendar.c.calendar_id == calendarID)).fetchone()
else:
data = con.execute(tblCalendar.select()).fetchall()
if (data):
if (isinstance(data, list)):
return [dict(row.items()) for row in data]
else:
return [dict(data.items())]
else:
return None
except Exception as e:
errormessage = utils.customError("listCalendar", e)
print(errormessage)
raise
def upsertCalendar(data):
try:
con = get_pgcon()
calendarId = data.get('calendar_id', None)
if (calendarId):
insert_stmt = insert(tblCalendar).values(data)
update_stmt = insert_stmt.on_conflict_do_update(
constraint='tbl_calendar_pk', set_=data)
con.execute(update_stmt)
data = con.execute(tblCalendar.select().where(
tblCalendar.c.calendar_id == calendarId)).fetchone()
if (data):
return [dict(data.items())]
else:
return None
else:
return None
except Exception as e:
errormessage = utils.customError("upsertCalendar", e)
print(errormessage)
raise
def clientStatus(eventDate):
try:
now = datetime.datetime.now().date()
eventDate = str(eventDate)
p = re.compile(r'\d{2}:\d{2}:\d{2}')
print('p', p, p.search(str(eventDate)))
if (p.search(eventDate)):
eventDate = eventDate[0:eventDate.rfind(":", 0, len(eventDate))]
eventDate = datetime.datetime.strptime(eventDate, '%Y-%m-%d %I:%M').date()
if(eventDate>now):
diff = computeBussDayDiff(now, eventDate)
if(diff<=2):
return "urgent"
else:
return "request"
else:
return "request"
except Exception as e:
errormessage = utils.customError("clientStatus", e)
print(errormessage)
return None
def scheduled_events():
try:
requestBody = utils.getJSONBody(request.get_data(as_text=True))
if (request.method in ('POST', 'DELETE', 'PUT')):
if (len(requestBody) == 0):
return "Error, you use POST, DELETE or PUT method but data empty. Check API documentation"
else:
eventID = requestBody.get('event_id', None)
if (eventID):
if (request.method == 'POST'):
data = googleCalendar.upsertEvent(requestBody)
data = json.dumps(data, default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
elif (request.method in ('PATCH', 'PUT')):
status = requestBody.get('status', None)
if (status in ('U', 'C')):
data = googleCalendar.upsertEvent(
requestBody, status)
data = json.dumps(data, default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
else:
return "Error, status update should be in 'U' for update and 'C' for cancel"
else:
return "Error, event ID is required."
else:
pass
except Exception as e:
errormessage = utils.customError("scheduled_events", e)
return errormessage
def upsertPerson(data):
try:
con = get_pgcon()
personId = data.get('person_id', None)
if (personId is None):
personId = str(uuid.uuid4())
data['person_id'] = personId
insert_stmt = insert(tblPerson).values(data)
update_stmt = insert_stmt.on_conflict_do_update(
constraint='tbl_person_pk', set_=data)
con.execute(update_stmt)
data = con.execute(tblPerson.select().where(
tblPerson.c.person_id == personId)).fetchone()
if (data is not None):
return [dict(data.items())]
else:
return None
except Exception as e:
errormessage = utils.customError("upsertPerson", e)
print(errormessage)
raise
def live_events(eventType='eod'):
print(eventType)
try:
liveSheet = os.getenv('LIVE_SHEET', '')
if (eventType == 'webcast'):
spreadSheetID = os.getenv('GSHEET_WEBCAST', '')
elif (eventType == 'eod'):
spreadSheetID = os.getenv('GSHEET_EOD', '')
else:
return "Error, worksheet is not recognized"
data = json.dumps(googleSheet.getProgramList(
SpreadSheetID=spreadSheetID, LiveSheet=liveSheet),
default=str)
response = flask.Response(data)
response.headers = {
'Access-Control-Allow-Origin': '*',
'Content-Type': 'application/json'
}
return response
except Exception as e:
errormessage = utils.customError("live_events", e)
print(errormessage)
return errormessage
def emailVerification(data, SPREADSHEET_ID, LIVE_SHEET):
try:
program_id = str(data.get('program_id', None)).strip()
email = str(data.get('email', None)).strip().lower()
first_name = str(data.get('first_name', ''))
last_name = str(data.get('last_name', ''))
now = datetime.datetime.utcnow()
now = now.strftime("%a, %d %b %Y %H:%M:%S")
status = 'N'
service = getSheetService()
if (program_id and email):
sheet = service.spreadsheets()
liveValues = sheet.values().get(spreadsheetId=SPREADSHEET_ID,
range=LIVE_SHEET).execute()
liveValues = liveValues.get('values', None)
if (liveValues):
liveValues = liveValues[1:]
liveValues = [
v for v in liveValues if len(v) > 1 and len(v[1]) > 0
]
record = [
l for l in liveValues
if (l[4].strip() == program_id.strip())
]
if (len(record) > 0):
#check email
emailValues = sheet.values().get(
spreadsheetId=SPREADSHEET_ID,
range=program_id).execute()
emailValues = emailValues.get('values', None)
if (emailValues):
emailValues = emailValues[1:]
emailValues = [e[0].lower() for e in emailValues]
if (email in emailValues):
acLink = str(record[0][2]).strip()
if (acLink and acLink.startswith("http")):
status = 'Y'
acLink = acLink + '?guestName=' + first_name + '%20' + last_name + '&proto=true'
msg = acLink
else:
msg = "Link EOD/Webcast is not found"
else:
msg = "This email address is not registered for this event. The email address must be entered exactly as it was upon registration."
else:
msg = "Incorrect Program ID, please check your email notification and try again."
else:
msg = "There is no LIVE information. Check LIVE sheet."
else:
msg = "There is no Program ID information in the data"
data = [now + " GMT", first_name, last_name, email, status, program_id]
data = {"values": [data]}
sheetRequest = service.spreadsheets().values().append(
spreadsheetId=SPREADSHEET_ID,
range='History',
valueInputOption='RAW',
insertDataOption='INSERT_ROWS',
body=data)
sheetRequest.execute()
return msg
except Exception as e:
errormessage = utils.customError("emailVerification", e)
print(errormessage)
raise
def upsertEvent(eventdata, status=None):
try:
eventID = eventdata.get('event_id', None)
added_attendee = eventdata.get('attendees', None)
producer_email = None
if (eventID):
details_event = postgres.getEventDetail(eventID)
service = get_calendar_service()
if (details_event and len(details_event) > 0):
producer_email = [{
'email': e.get('email', None)
} for e in details_event if e.get('email', None) is not None]
if (added_attendee and len(added_attendee) > 0):
producer_email.append(added_attendee)
formatted_event = formatDataEvent(details_event[0])
else:
return "Error, Event data or Producer data does not exist for that event ID. Contact developer."
clientCal = postgres.getCalendarID(eventID)
clientCal = clientCal.get('calendar_id', None)
dataTagEvent = []
if (clientCal):
clientEvent = formatted_event
producerCal = postgres.getCalendarID()
producerCal = producerCal.get('calendar_id', None)
print('producerCal', producerCal)
if (not status):
clientEvent['summary'] = "Attendees - " + details_event[
0].get('event_name')
clientEvent['description'] = os.getenv(
'GCALENDAR_ATTENDEES_LINK', '/') + eventID
if (added_attendee and len(added_attendee) > 0):
clientEvent['attendees'] = added_attendee
event_created = service.events().insert(
calendarId=clientCal,
body=clientEvent,
sendUpdates='all',
sendNotifications=True).execute()
if (event_created):
id_event_attendees = event_created.get('id', None)
dataTagEvent.append({
'event_id': eventID,
'tag_type': 'system',
'tag_name': 'Attendee invite',
'tag_value': id_event_attendees
})
print(event_created)
clientEvent['summary'] = "Speakers - " + details_event[
0].get('event_name')
clientEvent['description'] = os.getenv(
'GCALENDAR_SPEAKERS_LINK', '/') + eventID
event_created = service.events().insert(
calendarId=clientCal,
body=clientEvent,
sendUpdates='all',
sendNotifications=True).execute()
if (event_created):
id_event_speakers = event_created.get('id', None)
dataTagEvent.append({
'event_id': eventID,
'tag_type': 'system',
'tag_name': 'Speaker invite',
'tag_value': id_event_speakers
})
print(event_created)
if (producerCal):
prodEvent = formatted_event
if (producer_email):
prodEvent['attendees'] = producer_email
prodEvent['summary'] = "Producer - " + details_event[
0].get('event_name')
prodEvent['description'] = os.getenv(
'GCALENDAR_PRODUCERS_LINK', '/') + eventID
print('prodEvent', prodEvent)
event_created = service.events().insert(
calendarId=producerCal,
body=prodEvent,
sendUpdates='all',
sendNotifications=True).execute()
if (event_created):
id_event_producers = event_created.get('id', None)
dataTagEvent.append({
'event_id': eventID,
'tag_type': 'system',
'tag_name': 'Producer invite',
'tag_value': id_event_producers
})
print(event_created)
else:
return "Error, Calendar ID for Producer does not exist in database. Contact developer."
if (dataTagEvent and len(dataTagEvent) > 0):
print(len(dataTagEvent), dataTagEvent)
postgres.upsertEventTagMulti(dataTagEvent)
else:
eventIDs = postgres.listEventTag(eventID)
event_id_producer = None
if (eventIDs and len(eventIDs) > 0):
for e in eventIDs:
if (e.get('tag_type', '').lower() == 'system'):
if (e.get('tag_name',
'').lower() == 'attendee invite'):
event_id_attendee = e.get(
'tag_value', None)
elif (e.get('tag_name',
'').lower() == 'speaker invite'):
event_id_speaker = e.get('tag_value', None)
elif (e.get('tag_name',
'').lower() == 'producer invite'):
event_id_producer = e.get(
'tag_value', None)
if (clientCal and event_id_attendee and event_id_speaker):
clientEvent = formatted_event
if (status == 'U'):
clientEvent[
'summary'] = "[UPDATE] Attendees - " + details_event[
0].get('event_name')
else:
clientEvent[
'summary'] = "[CANCEL] Attendees - " + details_event[
0].get('event_name')
clientEvent['description'] = os.getenv(
'GCALENDAR_ATTENDEES_LINK', '/') + eventID
service.events().update(
calendarId=clientCal,
eventId=event_id_attendee,
body=clientEvent,
sendUpdates='all',
sendNotifications=True).execute()
if (status == 'U'):
clientEvent[
'summary'] = "[UPDATE] Speakers - " + details_event[
0].get('event_name')
else:
clientEvent[
'summary'] = "[CANCEL] Speakers - " + details_event[
0].get('event_name')
clientEvent['description'] = os.getenv(
'GCALENDAR_SPEAKERS_LINK', '/') + eventID
service.events().update(
calendarId=clientCal,
eventId=event_id_speaker,
body=clientEvent,
sendUpdates='all',
sendNotifications=True).execute()
print('producerCal', producerCal)
print('event_id_producer', event_id_producer)
if (producerCal and event_id_producer):
prodEvent = formatted_event
if (producer_email):
prodEvent['attendees'] = producer_email
if (status == 'U'):
prodEvent[
'summary'] = "[UPDATE] Producer - " + details_event[
0].get('event_name')
else:
prodEvent[
'summary'] = "[CANCEL] Producer - " + details_event[
0].get('event_name')
prodEvent['description'] = os.getenv(
'GCALENDAR_PRODUCERS_LINK', '/') + eventID
print('prodEvent', prodEvent)
service.events().update(
calendarId=producerCal,
eventId=event_id_producer,
body=prodEvent,
sendUpdates='all',
sendNotifications=True).execute()
else:
return "Error, Calendar ID for Producer does not exist in database. Contact developer."
else:
return "Error, Google event ID for that event ID does not exist. Contact developer."
else:
return "Error, Calendar ID for client does not exist in database. Contact developer"
return "OK"
else:
return None
except Exception as e:
errormessage = utils.customError("insert_event", e)
print(errormessage)
raise
