class Worker:
unique = 0
isAuth = False
userInfoTable = Airtable('appytOreQAh0kwjlt', "User Info",
"key1B3UuMtQpUkWQS")
designProjectTable = Airtable('appytOreQAh0kwjlt', "Design projects",
"key1B3UuMtQpUkWQS")
def export_to_airtable(data_type, obj_id):
"""
type: (int) -> Dict[str, Any]
Export Django model data to Airtable so administraters can analyze data.
"""
try:
if data_type == 'strikecircle':
# Passing strike_circle as list because serialization only works for iterable objects: https://code.djangoproject.com/ticket/11244
strike_circle = StrikeCircle.objects.get(pk=obj_id)
serialized_record = serialize("json", [strike_circle])
record = json.loads(serialized_record)[0]
if record["model"] == "strikecircle.strikecircle":
table_name = "Strike Circle"
airtable = Airtable(BASE_KEY, table_name, api_key=API_KEY)
return airtable.insert(record["fields"])
elif data_type == 'pledge':
pledge = Pledge.objects.get(pk=obj_id)
# Passing pledge as list because serialization only works for iterable objects: https://code.djangoproject.com/ticket/11244
serialized_record = serialize("json", [pledge])
record = json.loads(serialized_record)[0]
if record["model"] == "strikecircle.pledge":
table_name = "Pledge"
airtable = Airtable(BASE_KEY, table_name, api_key=API_KEY)
return airtable.insert(record["fields"])
except Exception as e:
logger.error(e)
def main():
# get auth keys from environment
omdb_api_key = os.environ['OMDB_API_KEY']
airtable_api_key = os.environ['AIRTABLE_API_KEY']
airtable_base_movies = os.environ['AIRTABLE_BASE_MOVIES']
# get arguments
argparser = init_argparse()
args = argparser.parse_args()
# set up OMDb connection
omdb.set_default('apikey', omdb_api_key)
# set up Airtable connections
movies_table = Airtable(airtable_base_movies,
'Movies',
api_key=airtable_api_key)
directors_table = Airtable(airtable_base_movies,
'Directors',
api_key=airtable_api_key)
actors_table = Airtable(airtable_base_movies,
'Actors',
api_key=airtable_api_key)
genres_table = Airtable(airtable_base_movies,
'Genres',
api_key=airtable_api_key)
# read movie names, insert movies
for movie_name in load_movie_names(args.movies):
movie_name = movie_name.rstrip()
print(movie_name)
movie_ids = get_movie_ids(movie_name.rstrip())
# make sure --set-field and --append-field are lists, even if they're empty (for upsert_movie())
if args.set_field is None:
args.set_field = []
if args.append_field is None:
args.append_field = []
count = 0
for movie_id in movie_ids['included']:
movie_data = get_movie_data(movie_id)
if movie_data['genre'] == 'Adult' and not args.include_adult:
continue
print(' {} ({})'.format(movie_data['title'], movie_data['year']))
movie_data['directors'] = get_multiple_records(
directors_table, 'Name', movie_data['director'])
movie_data['actors'] = get_multiple_records(
actors_table, 'Name', movie_data['actors'])
movie_data['genres'] = get_multiple_records(
genres_table, 'Name', movie_data['genre'])
data_transformed = transform_data(args, movie_data)
if args.verbose:
print(data_transformed)
upsert_movie(args, movies_table, data_transformed)
count += 1
if count == 0:
print(" NOT FOUND: {}".format(movie_name))
if args.verbose:
for movie_id in movie_ids['excluded']:
print(' excluding {}'.format(movie_id))
def __init__(self, *, config=None):
api_key = config['airtable_api_key']
base_key = config['airtable_base_key']
table_name_state = config['table_name_state']
table_name_timesheet = config['table_name_timesheet']
self.pricipal = config['principal']
self.lunch_break = config['lunch_break']
self.state = Airtable(base_key, table_name_state, api_key)
self.timesheet = Airtable(base_key, table_name_timesheet, api_key)
def __init__(self, base_key, api_key):
self.intakes = Airtable(base_key=base_key,
table_name='intakes',
api_key=api_key)
self.cases = Airtable(base_key=base_key,
table_name='cases',
api_key=api_key)
self.charges = Airtable(base_key=base_key,
table_name='charges',
api_key=api_key)
def __init__(self, bot: commands.Bot, config: helpers.Config,
log: logging.Logger, airtable_api_key: str):
self.bot = bot
self.log = log
self.config = config
self.log.info("Loaded Cog MostActive")
self.pug_participants = Airtable(
self.config.most_active["airtable_base"],
"PUG Participants",
api_key=airtable_api_key)
self.pug_list = Airtable(self.config.most_active["airtable_base"],
"PUG List",
api_key=airtable_api_key)
def __init__(self, bot: commands.Bot, config: helpers.Config,
log: logging.Logger, airtable_api_key: str):
self.bot = bot
self.log = log
self.config = config
self.log.info("Loaded Cog PUG Points")
self.pug_captains = Airtable(self.config.pug_points["airtable_base"],
"Captains",
api_key=airtable_api_key)
self.pug_point_transactions = Airtable(
self.config.pug_points["airtable_base"],
"Point Transactions",
api_key=airtable_api_key)
self.initialize.start()
def main():
# get run time
run_time = pendulum.now('UTC').to_w3c_string()
# get API key from environment
airtable_api_key = os.environ['AIRTABLE_API_KEY']
# get arguments
argparser = init_argparse()
args = argparser.parse_args()
# set up Airtable connections
bases_table = Airtable(args.destination, 'Bases', api_key=airtable_api_key)
tables_table = Airtable(args.destination,
'Tables',
api_key=airtable_api_key)
fields_table = Airtable(args.destination,
'Fields',
api_key=airtable_api_key)
# create base record
data = {
'Name': args.name,
'Base ID': args.id,
'Base URL': 'https://airtable.com/' + args.id,
'Last Imported': run_time
}
bases_table.insert(data)
# parse JSON, write to base
with open(args.json) as json_file:
schema = json.load(json_file)
for table in schema:
# write table record
table = schema[table]
data = {
'Name': table['name'],
'Base': args.name,
'Last Imported': run_time
}
tables_table.insert(data)
for field in table['columns']:
# write field record
data = {
'Name': field['name'],
'Table': table['name'],
'Type': field['type'],
'Last Imported': run_time
}
fields_table.insert(data)
def cfgToDict():
f = open("clientsettings.cfg", "r")
s = f.read().strip()
contents = dict(item.split("=") for item in s.split("\n"))
if 'UW_Owner_or_Fiscal_Group' in contents:
airtable = Airtable('app9og4P1Z4iet5fT', 'Departments',
'keybFjYyk9LuWpxNw')
res = airtable.search('Name',
contents['UW_Owner_or_Fiscal_Group'],
fields=['Name'])
print(res)
if len(res) == 0:
del contents['UW_Owner_or_Fiscal_Group']
else:
contents['Department'] = [res['id']]
if 'UW_Location' in contents:
contents['Location'] = contents['UW_Location']
del contents['UW_Location']
if 'UW_NetID' in contents:
contents['NetID'] = contents['UW_NetID']
del contents['UW_NetID']
if 'UW_PURCHASE_DATE' in contents:
contents['Purchased'] = contents['UW_PURCHASE_DATE']
del contents['UW_PURCHASE_DATE']
return contents
def get_email_and_phone_number_from_airtable(app_id, secret_key, record_id):
try:
print('##### Getting email and phone number from airtable started #####')
# initialize airtable tables
tbl_uploads = Airtable(app_id, 'Uploads', secret_key)
upload = tbl_uploads.get(record_id)
if 'Customer Email ID' in upload['fields']:
email = upload['fields']['Customer Email ID']
else:
print('Customer doesn\'t have email.')
raise ValueError('Customer doesn\'t have email.')
if 'Phone Number' in upload['fields']:
phone_number = upload['fields']['Phone Number']
else:
phone_number = None
print('##### Getting email and phone number from airtable finished #####')
print('email:', email, 'phone_number:', phone_number)
return email, phone_number
except Exception as e:
print('Error getting customer information from Airtable: ' + str(e))
raise ValueError('Error getting customer information from Airtable: ' + str(e))
def getcleandata(apikey,base,table,view,field_coname,field_jobsurl,
field_searchcount):
# Load Airtable class and pass args
airtable = Airtable(base, table, api_key=apikey)
raw_records = airtable.get_all(view=view)
clean_records = []
# print(raw_records)
for record in raw_records:
try:
data = {
'recordid': record['id'],
'coname': record['fields'][field_coname][0],
'jobsurl': record['fields'][field_jobsurl],
'searchcount': record['fields'][field_searchcount]
}
clean_records.append(data)
except KeyError:
print(
'A KeyError has ocurred. Skipping record.',
'A mapping key is empty for the record with Company Name:',
record['fields'][field_coname][0]+'.'
)
return clean_records
def to_airtable(df_data_xpt, table_name, apikey, base_key, view):
prefix = '!k4p4D4T4+'
if prefix in apikey:
enc_token = apikey.replace(prefix, "")
apikey = base64.b64decode(enc_token).decode("utf-8")
airtable = Airtable(base_key, table_name,
api_key=apikey) # connect to table
recs = airtable.get_all(view=view) # get all records in table
#print(recs)
if recs != []:
df_tbl = create_dataframe(recs) # dataframe of records in the table
remove_list = list(df_tbl['KEY'])
df_cln = df_data_xpt[~df_data_xpt['KEY'].isin(remove_list)]
#df_cln.fillna("", inplace=True)
else:
df_cln = df_data_xpt
for i in df_cln.index.values:
for col in df_cln.columns:
value = df_cln.at[i, col]
if date_check(value) == True and is_number(value) == False:
value = timezone_sast(value)
df_cln.at[i, col] = value
records = df_cln.loc[[i]].dropna(axis=1).to_dict(orient='records')[0]
#print(records)
airtable.insert(records, typecast=True)
def applications():
applications_table = Airtable(AIRTABLE_APP,
APPLICATIONS_TABLE,
api_key=AIRTABLE_API_KEY)
j = request.json
for event in j['event']:
fields = {
'applied_name': j['applied_name'],
'email': j['email'],
'Event': [event['id']],
'Agree to Attend': j['agree_to_attend'],
'Agree to Rate': j['agree_to_rate'],
'Agree to Pay Taxes': j['agree_to_pay_taxes'],
'Agree to Follow Instructions': j['agree_to_follow_instructions'],
'Assignment Type': event['assignment_type']
}
app.logger.debug('creating assignment application on Airtable: %s' %
str(fields))
try:
applications_table.insert(fields)
except HTTPError as error:
app.logger.error(error)
app.logger.error(error.response.text)
return jsonify({'message': 'error'}), 500
return jsonify({'message': 'ok'}), 201
def updateOrderStatus():
airtable = Airtable(base_key, 'Order Status', api_key)
records = airtable.get_all()
airOrders = []
counter = 2
for rec in records:
print(rec['fields'].keys())
def gettodayevent():
airtable = Airtable(BASE_KEY, 'events', api_key=API_KEY)
records = airtable.get_all(
filterByFormula="IS_SAME({Date}, TODAY(), 'day')")
return records
def handleRouteDelete(request):
""" Handler for Salesforce JSON request to delete route in Salesforce,
returns None.
Argument request of the form:
request = {
"Method" : "ROUTE_DELETE",
"Date" : Date,
"Base Name" : Base_Name
}
"""
# Retrieves all records associated with the Base for the date.
routeStops = AirtableEntry.objects.filter(
base_name__exact=request["Base Name"],
stop_date__exact=request["Date"])
# Deletes the records from the Django Database.
updateDatabase(request["Base Name"],
jsonize(routeStops, method="Stop Removed"))
# Deletes the records from Airtable.
if datetime.strptime(request["Date"], "%Y-%m-%d").date() == datetime.now(
pytz.timezone("US/Eastern")).date():
air = Airtable(request["Base Name"], "Table 1",
os.environ['AIRTABLE_APIKEY'])
for stop in air.get_all():
deleteStopInAirtable(air, stop["id"])
return True
def hello(short_url=None):
domain_url = os.getenv('DOMAIN')
if not short_url:
# data = create_line_items(request)
# total_lines = len(data['line_items'])
return render_template('success.html', **locals())
else:
try:
air_base = os.getenv('AIR_TABLE_BASE')
air_api_key = os.getenv('AIR_TABLE_API')
air_table_name = os.getenv('AIR_PROTOCOLO_TABLE_NAME')
at = Airtable(air_base, air_table_name, api_key=air_api_key)
lookup_record = at.search('short_url', short_url)
text_qs = lookup_record[0]['fields']['query_string']
visits = int(lookup_record[0]['fields']['visits']) + 1 if lookup_record[0]['fields']['visits'] >= 0 else 0
dict_qs = dict(parse_qsl(text_qs))
data = create_line_items(dict_qs)
total_lines = len(data['line_items'])
view_counter = {'visits': visits}
at.update(lookup_record[0]['id'], view_counter)
if 'imagen_protocolo' in lookup_record[0]['fields']:
images = [image['url'] for image in lookup_record[0]['fields']['imagen_protocolo']]
images = images[:3]
else:
images = []
except Exception as e:
return jsonify(error=str(e)), 403
return render_template('index.html', **locals())
def GetAirtableClient(table_key, table_name, secret_client):
# Load SQL and airtable credentials from secretmanager
secret_path = f'projects/{PROJECT_ID}/secrets/airtable-api-key/versions/latest'
AIRTABLE_API_KEY = secret_client.access_secret_version(request={
"name": secret_path
}).payload.data.decode('UTF-8')
return Airtable(table_key, table_name, api_key=AIRTABLE_API_KEY)
def main():
if not os.path.isfile(client_secret):
print('json file not found')
sys.exit(1)
volunteers, matchRequests = getVolMatchRequestDicts()
for matchAddr in matchRequests:
closest_volunteers = getClosestThreeVolunteers(matchAddr, volunteers)
airtableClients = Airtable('appFCRjbeCiFyMZNX', 'Clients')
clients = get_all(airtableClients)
id = matchRequests[matchAddr][0]
matchName = ''
for client in clients:
if client['id'] == str(id):
matchName = client['fields']['Full Name']
print('Text to: ' + matchName)
for vol in closest_volunteers:
volName = vol[0][0].split()[0].title()
distance = vol[1]
phone_num = vol[0][1]
text(
'+1' + phone_num, 'Hi ' + volName +
'! This is [insert your name] from All Together LA. Thanks so much for signing up to volunteer with us! We have a senior who lives '
+ str(distance) +
' minutes from you that needs some help getting groceries, would you be able to help? If so, let me know and I\'ll send over their contact info :)'
)
def mark_posted():
at_orange = Airtable(os.getenv('AT_APP_KEY_ORANGE'), 'Session hosting',
os.getenv('AT_API_KEY'))
for row in at_orange.get_all():
for num in range(3):
col = f'videoid_{num}'
if col in row['fields']:
if checked(f'posted_{num}', row['fields']):
continue
url = "https://youtube.googleapis.com/youtube/v3/videos"
url += f'?part=snippet,status,recordingDetails'
url += f"&id={row['fields'][col]}"
response = requests.get(url,
headers=get_auth_header(access_token))
assert response.status_code == 200
body = response.json()
assert len(body['items']) == 1
item = body['items'][0]
if item['status']['privacyStatus'] == 'public':
at_orange.update(row['id'], {f'posted_{num}': True})
def get_emails(base, table):
airtable = Airtable(base, table)
airtable_emails = airtable.get_all(fields='Email')
emails = []
for email in airtable_emails:
emails.append(email['fields']['Email'])
return emails
def __init__(self, session=None):
if session is None:
session = requests.Session()
self.session = session
self.client = MapzenAPI(os.environ.get('MAPZEN_API_KEY'))
self.geocode_database = Airtable(AIRTABLE_BASE_KEY,
AIRTABLE_GEOCODE_TABLE)
def authenticate():
api_key = 'keyS8sIcmUsk7To9K'
base_key = 'appdqzfZoeTcXC7VD'
table_name = 'Config'
airtable = Airtable(base_key, table_name, api_key=api_key)
return airtable
def create_link():
domain_url = os.getenv('DOMAIN')
air_base = os.getenv('AIR_TABLE_BASE')
air_api_key = os.getenv('AIR_TABLE_API')
air_table_name = os.getenv('AIR_PROTOCOLO_TABLE_NAME')
try:
at = Airtable(air_base, air_table_name, api_key=air_api_key)
new_record_content = dict(request.args) if getattr(request, 'args') else dict(request.form)
new_record_content['query_string'] = urlencode(request.args) if getattr(request, 'args') else urlencode(request.form)
if '_createdTime' in new_record_content:
del new_record_content['_createdTime']
if 'date_created' in new_record_content:
del new_record_content['date_created']
if 'date_modified' in new_record_content:
del new_record_content['date_modified']
if 'id' in new_record_content:
del new_record_content['id']
new_record = at.insert(new_record_content)
short_url = {'short_url': new_record['id'].split('rec')[1],
'airtableID': new_record['id'], 'visits': 0}
at.update(new_record['id'], short_url)
return jsonify({'link_url': domain_url + '/' + short_url['short_url']})
except Exception as e:
return jsonify(error=str(e)), 403
def handleRouteUpdate(request):
air = Airtable(request["Base Name"], "Table 1",
os.environ['AIRTABLE_APIKEY'])
(routeStops := list(
AirtableEntry.objects.filter(
base_name__exact=request["Base Name"],
stop_date__exact=datetime.now(
pytz.timezone("US/Eastern")).date()))).sort(
key=lambda x: x.stop_number)
# Retrieves all records from the database associated with the table and
# the route date.
for stop in air.get_all():
match = next(
(route_stop
for route_stop in routeStops if route_stop.stop_salesforce_id ==
stop["fields"]["Salesforce Id"]), None)
stop["fields"]["Airtable Id"] = stop["id"]
stop = stop["fields"]
if match:
updateEntryInDatabaseFromMatch(stop, match)
deleteStopInAirtable(air, stop["Airtable Id"])
print(routeStops)
for routeStop in jsonize(routeStops):
addStopInAirtable(air, routeStop)
return True
def fetch_all_records(keys_only: bool = False) -> Dict[str, ATRecord]:
"""
Get all records from Airtable, returning a map from key to record.
If multiple records are found for a given key, the preferred record
(as determined by the formats module) is kept,
and all the non-preferred records are deleted from Airtable.
"""
context = MC.get()
objects = "keys" if keys_only else "records"
prinl(f"Loading all {context} {objects} from Airtable...")
at_key, at_base, at_table, _ = MC.at_connect_info()
at = Airtable(at_base, at_table, api_key=at_key)
if keys_only:
field_map = MC.core_field_map()
fields = [field_map[MC.an_key_field()], field_map["Timestamp (EST)"]]
all_records = at.get_all(fields=fields)
else:
all_records = at.get_all()
prinlv(
f"Found {len(all_records)} {objects}; looking for duplicates...")
results: Dict[str, ATRecord] = {}
to_delete = []
for record_dict in all_records:
record = ATRecord.from_record(record_dict)
if record:
if not keys_only and (existing := results.get(record.key)):
if record.is_preferred_to(existing):
results[record.key] = record
to_delete.append(existing.record_id)
else:
to_delete.append(record.record_id)
else:
results[record.key] = record
def getagentinfo_byrecordid(recordid):
airtable = Airtable(BASE_KEY, 'member', api_key=API_KEY)
if isinstance(recordid, list):
recordlist = airtable.get_all()
agentlist = [
i['fields']['Agent'] for i in recordlist if i['id'] in recordid
]
agent = (', ').join(agentlist)
agent_with_at = '@{}'.format((', @').join(agentlist))
usernamelist = [
i['fields']['User name'] for i in recordlist if i['id'] in recordid
]
username = (', ').join(usernamelist)
username_with_at = '@{}'.format((', @').join(usernamelist))
combinelist = [
'{} (@{})'.format(agentlist[i], usernamelist[i])
for i in range(0, len(agentlist))
]
combine = (', ').join(combinelist)
else:
record = airtable.get(recordid)
agent = record['fields']['Agent']
agent_with_at = '@{}'.format(agent)
username = record['fields']['User name']
username_with_at = '@{}'.format(username)
combine = '{}({})'.format(agent, username_with_at)
return agent, agent_with_at, username, username_with_at, combine, combinelist
def events():
# if ENV != 'production':
# with open('fixtures/events.json', 'r') as json_file:
# json = json_file.read()
# return json
events_table = Airtable(AIRTABLE_APP,
EVENTS_TABLE,
api_key=AIRTABLE_API_KEY)
fields = [
'id', 'assignment_status', 'assignment', 'name', 'agency_name',
'classification', 'description', 'start_time', 'end_time',
'location_address', 'status', 'community_area', 'Custom Start Time',
'Custom End Time', 'url'
]
event_rows = events_table.search('assignment_status',
'Open Assignment',
fields=fields,
sort="start_time")
print(event_rows)
# handle custom start and end times :/
for row in event_rows:
fields = row['fields']
if 'Custom Start Time' in fields:
fields['start_time'] = fields['Custom Start Time']
del fields['Custom Start Time']
if 'Custom End Time' in fields:
fields['end_time'] = fields['Custom End Time']
del fields['Custom End Time']
return jsonify(event_rows)
def get_group_link(event):
airtable = Airtable(BASE_KEY, 'events', api_key=API_KEY)
eventrecord = airtable.match('Event', event)
group_link = eventrecord['fields']['Group link']
return group_link
def init(request):
# connection with Airtable
airtable = Airtable(base_key, 'previous_courses_data', api_key)
# data processing
template = get_template('diploma.html')
for script in airtable.get_all():
is_project = script['fields'].get('is_project', None)
current_course = script['fields'].get('course', None)
if is_project == True and current_course == '16':
name = script['fields']['first_name']
surname = script['fields']['last_name']
date = script['createdTime']
date = datetime.strptime(date, "%Y-%m-%dT%H:%M:%S.%fZ")
locale.setlocale(locale.LC_TIME, "ru_RU")
date = datetime.strftime(date, "%d %B %Y")
html = render(request, 'diploma.html', {'name': name, 'surname': surname, 'date': date})
result = (html.content).decode('utf-8')
config = pdfkit.configuration(wkhtmltopdf='/usr/local/bin/wkhtmltopdf')
options = {
'dpi': 300,
'page-size': 'A4',
'orientation': 'landscape',
'margin-top': '0',
'margin-right': '0.',
'margin-bottom': '0.',
'margin-left': '0',
'encoding': "UTF-8",
'custom-header': [
('Accept-Encoding', 'gzip')
],
'no-outline': None
}
pdfkit.from_string(result, 'result/' + '{}-{}.pdf'.format(name, surname), configuration=config, options=options)
return render(request, 'diploma.html')
