sms_handling_osd.py

#sms get redirected by the provider to this endpoint
@app.route('/sms', methods=['GET'])
def sms():
    if request.method == 'GET':
        sms_from = request.args.get("from")
        sms_message = request.args.get("message")
        sms_message = re.sub(' +',' ',sms_message)
        yes = 0 
        rush = 0 
        no = 0 
        subscribe = 0 
        email = None

        if 'beer no' in sms_message.lower():
            no = 1.
        elif 'beer yes' in sms_message.lower():
            yes = 1 
        elif 'beer rush' in sms_message.lower():
            rush = 1 

        if 'subscribe' in sms_message.lower():
            if 'subscribe sms' in sms_message.lower():
                subscribe = 1 

            match = re.search(r'[\w\.-]+@[\w\.-]+', sms_message)
            if match is not None:
                email = match.group(0)

            if len(email) > 4:
                email = email

        sms = SMS(sms_from=sms_from, sms_message=sms_message, yes=yes, rush=rush, no=no, subscribe=subscribe, email=email)
        db.session.add(sms)
        db.session.commit()


# displays sms from database
@app.route('/admin/sms', methods=['GET', 'POST'])
def display_sms():
    # counts the last message from each number for beer yes, beer no or beer rush
    sql = text('select SUM(yes) AS yes, SUM(no) AS no, SUM(rush) AS rush from (SELECT sms_from, row_number() over(partition by sms_from order by id desc), yes, rush, no FROM sms WHERE yes = 1 OR no = 1 OR rush = 1) x where row_number = 1;')
    yes_no_rush = db.engine.execute(sql).first()
    # gets all messages
    sms = db.session.query( SMS ).all()
    return render_template('sms.html', sms=sms, yes_no_rush=yes_no_rush)



# database model (using SQLAlchemy ORM layer)
class SMS(db.Model):           
    __tablename__="sms"        
  
    id = db.Column(db.Integer, primary_key=True)
    sms_from = db.Column(db.String) 
    sms_message = db.Column(db.String)
    yes = db.Column(db.Integer)
    rush = db.Column(db.Integer)    
    no = db.Column(db.Integer) 
    subscribe = db.Column(db.Integer)
    email = db.Column(db.String)                                                                                                                             

    def __init__(self, sms_from, sms_message, yes, rush, no, subscribe, email):                                                                              
        self.sms_from = sms_from    
        self.sms_message = sms_message  
        self.yes = yes
        self.rush = rush
        self.no = no
        self.subscribe = subscribe  
        self.email = email

    def __repr__(self):
        return "<Links(sms_from='%s', sms_message='%s', yes='%s', rush=%s, no='%s', subscribe='%s', email='%s')>" % (self.sms_from, self.sms_message, self.yes, self.rush, self.no, self.subscribe, self.email)