def update_schedule(text):
if text.lower().startswith('update schedule'):
conn = sqlite3.connect(config.DB_NAME)
start_text = text.find(':') + 2
end_text = text.find(',')
plant_name = text[start_text:end_text]
schedule_in_days = text[end_text + 2:]
sql = "UPDATE watering_schedule SET schedule_in_days = " + str(
schedule_in_days) + " WHERE plant_name = '" + str(plant_name) + "'"
print(sql)
cursor = conn.execute(sql)
conn.commit()
email_subject = 'Schedule updated:'
email_body = "Updated " + plant_name + " to watering schedule of " + str(
schedule_in_days)
plant_functions.send_email(email_subject, email_body)
conn.close()
def get_score(text):
if text.strip().lower() == "get score":
score_list = []
score_last_30_days = []
sql_score_list = """SELECT e.name, count(sk.id)
FROM score_keeper sk
JOIN emails e ON sk.email = e.email
GROUP BY 1 ORDER BY 2 DESC"""
sql_last_30_list = """SELECT e.name, count(sk.id)
FROM score_keeper sk
JOIN emails e ON sk.email = e.email
WHERE timestamp >= date('now','-30 days')
GROUP BY 1 ORDER BY 2 DESC"""
sqls = [sql_score_list, sql_last_30_list]
lists = [score_list, score_last_30_days]
conn = sqlite3.connect(config.DB_NAME)
x = 0
for sql in sqls:
cursor = conn.execute(sql)
if x == 0:
print('Overall score:')
elif x == 1:
print('Last 30 days:')
for row in cursor:
print(str(row[0]) + ": " + str(row[1]))
lists[x].append(str(row[0]) + ": " + str(row[1]))
x += 1
email_body = 'Overall Score:\n' + str(
score_list) + '\n' + 'Last 30 Days:\n' + str(score_last_30_days)
email_subject = "Plant Bot Scores:"
plant_functions.send_email(email_subject, email_body)
conn.close()
def help(text):
if text.strip().lower() == "help":
email_body = '''
Add a picture of a plant by texting "Add Pic: plant_name"\n
Add a plant by texting "Add Plant: plant_name, schedule_in_days"\n
Check status of one plant by texting "plant_name status"\n
Check status of all plants by texting "All status" \n
Confirm a plant has been watered by texting "plant_name watered"\n
Get a picture of a plant texted to you by texting "Request Pic: plant_name"\n
Get list of plants that need to be watered in the next 7 days by texting "7 day status"\n
Get score of users (how many plants each user has watered) by texting "Get Score"\n
Update a plant's watering schedule by texting "Update Schedule: plant_name, schedule_in_days"'''
email_subject = "Available Commands:"
plant_functions.send_email(email_subject, email_body)
def add_plant(text):
if text.startswith('Add Plant:'):
conn = sqlite3.connect(config.DB_NAME)
start_text = text.find(':') + 2
end_text = text.find(',')
plant_name = text[start_text:end_text]
schedule_in_days = text[end_text + 2:]
sql = "INSERT INTO watering_schedule (plant_name, schedule_in_days, last_watered, days_since_last_water, need_water, ignore) VALUES ('" + str(plant_name + "','" + str(schedule_in_days) + "', (SELECT datetime('now','localtime')), 0, 0, 0)")
print (sql)
cursor = conn.execute(sql)
conn.commit()
email_subject = 'Plant Added:'
email_body = "Added " + plant_name + " with watering schedule of " + str(schedule_in_days) + " days to plants database!"
plant_functions.send_email(email_subject,email_body)
conn.close()
def plant_watered(text, email_from):
conn = sqlite3.connect(config.DB_NAME)
cursor = conn.execute(
"SELECT id FROM watering_schedule WHERE need_water = 1 AND ignore = 0")
id_list = []
for row in cursor:
id_list.append(row[0])
print("List of IDs that need water")
print(id_list)
if int(text) in id_list:
print(text + ' to be processed')
sql = "UPDATE watering_schedule SET last_watered = datetime('now'), days_since_last_water = 0, need_water = 0 WHERE id = " + text
print(sql)
conn.execute(sql)
conn.commit()
print(text + ' record updated')
score_keeper_sql = (
"INSERT INTO score_keeper (plant_id,email,timestamp) VALUES (" +
text + ",'" + email_from + "',date('now'))")
print(score_keeper_sql)
conn.execute(score_keeper_sql)
conn.commit()
email_subject = 'Updates:'
email_body = text + ' watered'
plant_functions.send_email(email_subject, email_body, row)
conn.close()
def send_text():
print ('Starting...')
# Add plants to output list if they need water. Send list of all plants in one email with this list
output = []
# Checks which plants have a need_water flag set to 1, an ignore flag set to 0, and then sends one text with
# a list of plant IDs and plant names that need to be watered
conn = sqlite3.connect(config.DB_NAME)
cursor = conn.execute("SELECT id, plant_name FROM watering_schedule WHERE need_water = 1 AND ignore = 0")
# Add items to dictionary
for row in cursor:
output.append(str(row[0]) + ': ' + row[1])
if len(output) > 0:
print (output)
email_subject = "Plants to water:"
email_body = ', '.join(output)
plant_functions.send_email(email_subject,email_body)
else:
print ('No plants need watering')
conn.close()
def check_status(text):
# exit_code is used to skip the send_email function if needed. 0 means normal and sends email. Anything else will skip that step
exit_code = 0
row = []
conn = sqlite3.connect(config.DB_NAME)
# Query to get plant names and days until next water
cursor = conn.execute(
"SELECT plant_name, schedule_in_days - days_since_last_water FROM watering_schedule WHERE ignore = 0"
)
# Hold query in dictionary
output = {}
# Add items to dictionary
for row in cursor:
output[str(row[0])] = row[1]
sorted_output = sorted(output.items(), key=operator.itemgetter(1))
# Create list of plant names from 'output' dictionary
plant_list = output.keys()
# Check the status of one plant
if any(plant in text for plant in plant_list):
# removed_string takes the text variable, which is the email attachment, and removes the ' status' portion in order to get the plant name
removed_string = text.find(' status')
plant = text[0:removed_string]
# status is the number of days until that plant needs to be watered (stored in the output dictionary)
status = output.get(plant)
# Create email subject to pass to plant_functions
email_subject = 'Status:'
email_body = "Water " + plant + " in " + str(status) + " days"
# Return all plants status
elif text.strip().lower() == "all status":
email_subject = 'Overall Status:'
email_body = 'Water:\n'
for plant in sorted_output:
email_body = email_body + str(plant[0]) + ' in ' + str(
plant[1]) + ' days\n'
# Return plants that need to be watering in the next 7 days
elif text.strip().lower() == "7 day status":
seven_day_dict = {}
for k, v in output.items():
if v <= 7:
seven_day_dict[k] = v
sorted_seven_day_dict = sorted(seven_day_dict.items(),
key=operator.itemgetter(1))
email_subject = 'Next 7 Days:'
email_body = 'Water:\n'
for plant in sorted_seven_day_dict:
email_body = email_body + str(plant[0]) + ' in ' + str(
plant[1]) + ' days\n'
else:
print("Pattern not recognized - deleting email!")
exit_code = 1
if exit_code == 0:
plant_functions.send_email(email_subject, email_body, row)
conn.close()