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