def verify_unsubscribe(confcode):
logging.debug({'func': 'verify_unsubscribe', 'confcode': confcode})
conn, cur = u_db.get_db_conn_and_cursor(app.config)
cur.execute("SELECT (phone) FROM unsubscribe WHERE confcode=%s",
[confcode])
d = cur.fetchone()
if d:
cur.execute('DELETE FROM verified WHERE phone=%s', [d[0]])
cur.execute('DELETE FROM unsubscribe WHERE phone=%s', [d[0]])
conn.commit()
logging.info({
'func': 'verify_unsubscribe',
'phone': d[0],
'msg': 'successfully unsubscribed'
})
msg = "You have been successfully unsubscribed."
else:
logging.info({
'func': 'verify_unsubscribe',
'confcode': confcode,
'msg': 'invalid unsubscribe code'
})
msg = "ERROR: The supplied confirmation code is not valid."
cur.close()
conn.close()
return render_template("unsubscribe.html", form=UnsubscribeForm(), msg=msg)
def unsubscribe():
"""
Process a request to unsubscribe and return the template
"""
form = UnsubscribeForm()
# If the form has been submitted
if form.validate_on_submit():
# Open db connection
conn, cur = u_db.get_db_conn_and_cursor(app.config)
# If all data is valid
phone = u_views.is_valid_number(form.phone.data)
print(phone)
if phone:
# If phone number does not already exist as a verified user
cur.execute(
'SELECT provider FROM verified WHERE phone=%s AND fname=%s and lname=%s;',
[
phone,
form.fname.data.upper().rstrip(),
form.lname.data.upper().rstrip()
])
user = cur.fetchone()
if user:
# Process unsubscribe request
confcode = random.getrandbits(16)
flashmsg = u_views.unsubscribe_user(cur, phone, confcode)
subject = 'VT-3 Notifications'
smstxt = "Click the link to unsubscribe. %s%s%d" \
% (app.config['BASE_URL'], '/unsubscribe/', confcode)
# Send Text Message
tc = TextClient(debug=app.config['DEBUG'])
response = tc.send_message(phone, subject, smstxt, user[0])
#flash(flashmsg)
#return redirect('/')
else:
# Alert user this was an invalid unsubscribe request
flashmsg = 'Data does not match anyone in our database. Please confirm name spelling and phone number.'
else:
flashmsg = 'Invalid phone number. Please try again.'
form.phone.errors.append("Invalid format.")
flash(flashmsg)
conn.commit()
print('committing')
cur.close()
conn.close()
return render_template("unsubscribe.html", form=form)
def run_conf_code_update():
"""
Deletes confirmation codes older than 24 hours
"""
logging.info({'func': 'run_conf_code_update', 'msg': 'starting'})
conn, cur = get_db_conn_and_cursor()
cur.execute(
"DELETE FROM unverified WHERE current_timestamp - datetime > '24 hours';"
)
conn.commit()
cur.close()
conn.close()
logging.info({'func': 'run_conf_code_update', 'msg': 'returning'})
def setUp(self):
self.today = datetime.now()
self.yesterday = self.today - timedelta(days=1)
self.vt3url = "https://www.cnatra.navy.mil/scheds/schedule_data.aspx?sq=vt-3"
self.conn, self.cur = u_db.get_db_conn_and_cursor()
try:
# TODO Is this needed now that I have an actual test database setup?
self.cur.execute(
"INSERT INTO schedule (type, brief, edt, rtb, instructor, student,"\
" event, remarks, location, date) VALUES ('T-6B Flight', "\
"'07:00', '08:45', '10:15', 'TEST, INSRUCTOR', 'TEST, STUDENT'"\
", 'C4101', 'OnlineScheduleTestCase', 'NASWF', 'January 1');")
self.conn.commit()
except IntegrityError as e:
self.conn.rollback()
def verify(confcode):
logging.debug({'func': 'verify', 'confcode': confcode})
conn, cur = u_db.get_db_conn_and_cursor(app.config)
cur.execute(
"SELECT (phone, lname, fname, provider) FROM unverified WHERE confcode=%s",
[confcode])
d = cur.fetchone()
if d:
#TODO Why am I stripping and splitting? cur.fetchone returns a tuple,
# not a string. ref: http://initd.org/psycopg/docs/cursor.html#fetch
d = d[0].lstrip('(').rstrip(')').split(',')
cur.execute("SELECT phone FROM verified WHERE phone = %s", [d[0]])
already_added = cur.fetchone()
if not already_added:
cur.execute(
'INSERT INTO verified (phone, lname, fname, provider) VALUES (%s, %s, %s, %s)',
[d[0], d[1], d[2], d[3]])
# TODO Make an initial push message when confirmed (e.g. what if they
# sign up at night and need tomorrows schedule)
msg = "Congratulations! You have successfully been signed up. You " \
"will begin receiving messages at the next run."
logging.info({
'func': 'verify',
'fname': d[2],
'lname': d[1],
'phone': d[0],
'msg': 'signup confirmation successful'
})
else:
logging.info({
'func': 'verify',
'confcode': confcode,
'msg': 'invalide confirmation code'
})
msg = "ERROR: The supplied confirmation code is not valid."
conn.commit()
cur.close()
conn.close()
#TODO: This is a shitty fix for redirecting and flashing the message
# after a successful signup
return redirect('/')
def run_signup_form(form):
# Open db connection
conn, cur = u_db.get_db_conn_and_cursor(app.config)
# FIXME: What do we do if user selects invalid phone provider?
# FIXME: How will this fix play into the eventual switch to TWILIO?
# If all data is valid
phone = is_valid_number(form.phone.data)
if phone:
# If phone number does not already exist as a verified user
cur.execute('SELECT * FROM verified WHERE phone=%s;', [phone])
if not cur.fetchone():
logging.info({
'func': 'run_signup_form',
'fname': form.fname.data,
'lname': form.lname.data,
'phone': phone,
'provider': form.provider.data,
'msg': 'user signup'
})
# If phone number does not already exist as an unverified user
cur.execute('SELECT confcode FROM unverified WHERE phone=%s;',
[phone])
unverified_user = cur.fetchone()
if not unverified_user:
# Add user to unverified signups table
flashmsg = sign_up_user(cur, conn, phone, form.provider.data,
form.lname.data.upper().rstrip(),
form.fname.data.upper().rstrip())
else:
confcode = int(unverified_user[0])
send_conf_code(phone, form.provider.data,
'VT-3 Notifications Signup', confcode)
flashmsg = 'This phone number has already signed up but has not been verified. We have re-sent your confirmation code.'
else:
flashmsg = 'This phone number has already been signed up.'
flash(flashmsg)
else:
flash('Invalid phone number. Please try again.')
form.phone.errors.append("Invalid format.")
cur.close()
conn.close()
def from_email_address(phone, provider):
"""
http://www.howtogeek.com/howto/27051/use-email-to-send-text-messages-sms-to-mobile-phones-for-free/
Params:
phone: (str) format is 2225559999
provider: (str) mobile provider
Returns: (str) email address as [email protected]
"""
if phone[:2] == '+1':
phone = phone[2:]
assert len(phone) == 10, 'phone is not lenght 10'
assert re.match('\d{10}', phone), 'phone is not all digits'
assert provider is not None, 'No wireless provider given'
conn, cur = u_db.get_db_conn_and_cursor()
cur.execute('SELECT gateway FROM smsgateways WHERE name = %s;',
[provider])
#TODO Is this really the best behavior I can come up with?
# A better solution might be to force usage with Twilio, however if
# Twilio is setup, then why don't I just use twilio for everything in
# the first place? For now, I am simply going to leave this as the state
# of things until I complete the implementation of Twilio. At that point
# This entire function should be removed.
try:
gateway_address = cur.fetchone()[0]
except TypeError:
gateway_address = 'invalidprovider.com'
try:
email = phone + '@' + gateway_address
return email
except KeyError:
logging.error({
'func':
'TextClient:from_email_address',
'msg':
'KeyError: %r is not a valid mobile provider' % provider
})
return '*****@*****.**' # TODO Setup email address for error
def setUp(self):
self.conn, self.cur = u_db.get_db_conn_and_cursor()
try:
self.cur.execute(
"INSERT INTO unverified (phone, provider, lname, fname, confcode, "\
"datetime) VALUES (%s, %s, %s, %s, %s, current_timestamp);",
['+18505551111', 'verizon', 'CONFCODETEST1', 'TEST', '1111']
)
self.cur.execute(
"INSERT INTO unverified (phone, provider, lname, fname, confcode, "\
"datetime) VALUES (%s, %s, %s, %s, %s, current_timestamp - "\
"INTERVAL '1 day' - INTERVAL '5 minutes');",
['+18505552222', 'verizon', 'CONFCODETEST2', 'TEST', '2222']
)
self.conn.commit()
except IntegrityError as e:
# Lines already in database
self.conn.rollback()
self.cur.execute("SELECT * FROM unverified WHERE fname LIKE 'TEST';")
self.assertIsNotNone(self.cur.fetchone())
def run_online_schedule():
logging.basicConfig(level=logging.DEBUG)
logging.info({'func': 'run_online_schedule',
'msg': "Starting run_online_schedule()"})
# Define Vars
conn, cur = u_db.get_db_conn_and_cursor()
url = 'https://www.cnatra.navy.mil/scheds/schedule_data.aspx?sq=tw-5+fitu'
tomorrow = datetime.now() - timedelta(hours=5) + timedelta(
days=1) # adjust timezone
if tomorrow.weekday() == 5: # If it is Friday and we're looking for Sat's sched
dates = (
tomorrow, tomorrow + timedelta(days=1),
tomorrow + timedelta(days=2))
else:
dates = (tomorrow,)
logging.info({'func': 'run_online_schedule', 'dates': dates,
'msg': 'Dates being processed by run_online_schedule()'})
for dt in dates:
logging.info(
{'func': 'run_online_schedule',
'msg': "Checking schedule for %r" % tomorrow})
# Download Schedule
try:
sched = process_raw_schedule(get_schedule_page(url, dt))
# If schedule has been posted on cnatra or uploaded to postgress yet
if sched and not sched_uploaded(cur, dt):
insert_in_pg(cur, sched, dt)
if dt.weekday() == 1:
logging.debug({'func': 'run_online_schedule',
'msg': 'Deleting last weeks schedule from database'})
delete_old_sched(cur, dt - timedelta(days=2))
delete_old_sched(cur, dt - timedelta(days=3))
delete_old_sched(cur, dt - timedelta(days=4))
delete_old_sched(cur, dt - timedelta(days=5))
delete_old_sched(cur, dt - timedelta(days=6))
delete_old_sched(cur, dt - timedelta(days=7))
delete_old_sched(cur, dt - timedelta(days=8))
conn.commit()
send_all_texts(cur, dt)
send_squadron_notes(url, dt, cur)
# If it gets too late and the schedule hasn't been published, send out
# a text. But only do this once, so let's use 1930L == 0030UTC
# 1/4/2018 Wow, so here is a fun issue. This current code will keep
# looking for the schedule after it hasn't been published, just in
# case it does end up getting published late. It just sends a
# message out at the specified time, but still keeps looking.
# On the date above, that message was sent out 3x despite the
# scheduler being set to run every 30 minutes. Not a f*****g clue
# how that ended up happening. The logs indicate it ran at 16:31,
# 16:43, and 16:58 despite the scheduler being set to run on the
# hour and on the half hour.
#
# Jan 03 16:31:33 vt3 app/scheduler.3386: WARNING:root:{'msg': 'Schedule was not published by 0100Z', 'func': 'run_online_schedule'}
# Jan 03 16:43:09 vt3 app/worker.1: WARNING:root:{'func': 'run_online_schedule', 'msg': 'Schedule was not published by 0100Z'}
# Jan 03 16:58:40 vt3 app/worker.1: WARNING:root:{'func': 'run_online_schedule', 'msg': 'Schedule was not published by 0100Z'}
# TODO: What about when DST ends?
elif not sched and time(0, 29, 0) < datetime.now().time() < time(0,
59,
0):
logging.warning({'func': 'run_online_schedule',
'msg': 'Schedule was not published by 0100Z'})
# TODO Only instantiate one TextClient in main.py
client = TextClient()
cur.execute("SELECT phone, provider FROM verified;")
msg = "The schedule has not been published yet. Please call the " \
"SDO at (850)623-7323 for tomorrow's schedule."
for phone, provider in cur.fetchall():
client.send_message(phone, dt.strftime('%B %-d'), msg, provider)
except AttributeError as e:
logging.debug({'func': 'run_online_schedule', 'error': e})
logging.info({'func': 'run_online_schedule',
'msg': "Schedule not yet published"})
cur.close()
conn.close()
logging.info(
{'func': 'run_online_schedule', 'msg': "run_online_schedule() exiting"})
def test_get_db_conn_and_cursor(self):
a = get_db_conn_and_cursor()
def setUp(self):
self.conn, self.cur = u_db.get_db_conn_and_cursor(app.config)
self.phone = '+16665551111'
self.provider = 'verizon'
