class Device(db.Model):
''' Class to represent guest's device, each guest can have multiple devices attached to his account
'''
id = db.Column(db.Integer, primary_key=True)
mac = db.Column(db.String(30), index=True)
hostname = db.Column(db.String(60), index=True)
state = db.Column(db.Integer)
created_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
guest_id = db.Column(db.Integer, db.ForeignKey('guest.id'))
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
sessions = db.relationship('Guestsession',
backref='device',
lazy='dynamic')
smsdatas = db.relationship('Smsdata', backref='device', lazy='dynamic')
expires_at = db.Column(db.DateTime) #Expiry time for last used voucher
demo = db.Column(db.Integer, default=0, index=True)
sms_confirm = db.Column(
db.Integer, default=0,
index=True) #used to verify if the device's phone number is confirmed
voucher_id = db.Column(db.Integer, index=True) #last used voucher id
def get_monthly_usage(self):
'''Returns the total monthly free data usage for this device
Excludes voucher usage
'''
firstday = arrow.now(self.site.timezone).floor('month').to('UTC').naive
sessions = Guestsession.query.filter(
and_(Guestsession.device_id == self.id,
Guestsession.starttime >= firstday)).all()
total_data = 0
for session in sessions:
if session.state != GUESTRACK_VOUCHER_AUTH and session.data_used:
total_data += int(session.data_used)
#convert bytes to Mb
data_mb = int(math.ceil((total_data / 1024000.0)))
return data_mb
def get_phonenumber(self):
'''Returns the phonenumber connected to this account
'''
return ';'.join([x.phonenumber for x in self.smsdatas])
def get_voucher(self):
'''Returns a valid voucher id if any associated with this device, if nothing found returns None
'''
for voucher in self.vouchers:
if voucher.check_validity():
return voucher.id
return None
class Guestsession(db.Model):
''' Class to represent guest session. Each session is associated to a Guest and will have a state associated with it.
'''
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
device_id = db.Column(db.Integer, db.ForeignKey('device.id'))
starttime = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
lastseen = db.Column(db.DateTime,
index=True,
default=datetime.datetime.utcnow)
stoptime = db.Column(
db.DateTime, index=True
) #Time at which session is stopped, to be filled by session updator
expiry = db.Column(db.DateTime,
index=True,
default=datetime.datetime.utcnow
) #predicted expiry time,default to 60 minutes
temp_login = db.Column(db.Integer, default=0)
duration = db.Column(db.Integer, default=60)
ban_ends = db.Column(db.DateTime, index=True)
data_used = db.Column(db.String(20)) #Data used up in this session
state = db.Column(db.Integer)
mac = db.Column(db.String(30), index=True)
d_updated = db.Column(db.String(20)) #data updated last
duration = db.Column(
db.Integer
) #Duration in seconds the session lasted, updated by session updator
guesttracks = db.relationship('Guesttrack',
backref='guestsession',
lazy='dynamic')
demo = db.Column(db.Integer, default=0, index=True)
class Facebookauth(db.Model):
''' Class to represent guest's Facebook connection, this is needed as one common APP is used for tracking guests in different sites.
'''
id = db.Column(db.Integer, primary_key=True)
profile_id = db.Column(db.String(200), nullable=False, index=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
token = db.Column(db.Text, nullable=False)
state = db.Column(db.Integer)
guests = db.relationship('Guest', backref='facebookauth', lazy='dynamic')
class Device(db.Model):
''' Class to represent guest's device, each guest can have multiple devices attached to his account
'''
id = db.Column(db.Integer, primary_key=True)
mac = db.Column(db.String(30), index=True)
hostname = db.Column(db.String(60), index=True)
state = db.Column(db.Integer)
created_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
guest_id = db.Column(db.Integer, db.ForeignKey('guest.id'))
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
sessions = db.relationship('Guestsession',
backref='device',
lazy='dynamic')
smsdatas = db.relationship('Smsdata', backref='device', lazy='dynamic')
expires_at = db.Column(
db.DateTime
) #Expiry time for last used voucher , valid only if state is Device_voucher_auth
demo = db.Column(db.Integer, default=0, index=True)
class Account(db.Model):
''' Class to represent accounts. Each account can have multiple clients and admins
'''
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60))
unifi_server = db.Column(db.String(255), index=True, default="localhost")
unifi_server_ip = db.Column(db.String(255),
index=True,
default="127.0.0.1")
unifi_user = db.Column(db.String(255), index=True, default="ubnt")
unifi_pass = db.Column(db.String(255), index=True, default="ubnt")
sites_allowed = db.Column(db.Integer, default=100)
account_type = db.Column(db.Integer, index=True, default=ACCOUNT_TYPE_FREE)
expiresat = db.Column(db.DateTime, index=True)
en_api_export = db.Column(db.Integer, default=1, index=True)
en_reporting = db.Column(db.Integer, default=1, index=True)
en_analytics = db.Column(db.Integer, default=1, index=True)
en_advertisement = db.Column(db.Integer, default=1, index=True)
en_newsletter = db.Column(db.Integer, default=0, index=True)
en_footer_change = db.Column(db.Integer, default=1, index=True)
en_fbauth_change = db.Column(db.Integer, default=1, index=True)
en_sms_preauth = db.Column(db.Integer, default=0, index=True)
logins_allowed = db.Column(db.Integer, default=1000, index=True)
firstrun = db.Column(db.Integer, default=1, index=True)
token = db.Column(db.String(20), index=True)
admins = db.relationship('Admin', backref='account', lazy='dynamic')
clients = db.relationship('Client', backref='account', lazy='dynamic')
sites = db.relationship('Wifisite', backref='account', lazy='dynamic')
notifications = db.relationship('Notification',
backref='account',
lazy='dynamic')
def populate_settings(self, form):
self.unifi_server = form.unifi_server.data
self.unifi_server_ip = form.unifi_server_ip.data
self.unifi_user = form.unifi_user.data
self.unifi_pass = form.unifi_pass.data
self.firstrun = 0
def get_settings(self):
return dict_normalise_values({ 'unifi_server':self.unifi_server,'unifi_user':self.unifi_user, 'id':self.id, \
'unifi_pass':self.unifi_pass,'unifi_server_ip':self.unifi_server_ip} )
def populate_from_form(self, form):
expiresat = datetime.datetime.strptime(form.expiresat.data,
"%m/%d/%Y").date()
self.unifi_server = form.unifi_server.data
self.unifi_server_ip = form.unifi_server_ip.data
self.unifi_user = form.unifi_user.data
self.unifi_pass = form.unifi_pass.data
self.name = form.name.data
self.sites_allowed = form.sites_allowed.data
self.account_type = form.account_type.data
self.expiresat = expiresat
self.en_api_export = form.en_api_export.data
self.en_reporting = form.en_reporting.data
self.en_analytics = form.en_analytics.data
self.en_advertisement = form.en_advertisement.data
self.en_footer_change = form.en_footer_change.data
self.en_fbauth_change = form.en_fbauth_change.data
self.logins_allowed = form.logins_allowed.data
def to_dict(self):
expiresat = self.expiresat.strftime(
"%m/%d/%Y") if self.expiresat else ''
return dict_normalise_values({ 'unifi_server':self.unifi_server,'unifi_user':self.unifi_user, 'id':self.id, \
'unifi_pass':self.unifi_pass,'name':self.name,'sites_allowed':self.sites_allowed,'account_type':self.account_type,\
'expiresat':expiresat,'en_api_export':self.en_api_export,'en_reporting':self.en_reporting,\
'en_analytics':self.en_analytics,'en_advertisement':self.en_advertisement,'en_footer_change':self.en_footer_change,\
'en_fbauth_change':self.en_fbauth_change,'logins_allowed':self.logins_allowed
} )
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Account.query
if term:
result_qry = main_query.filter(Account.name.like('%' + term + '%'))
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Account.name.desc())
else:
results_ord = result_qry.order_by(Account.name.asc())
if sort['column'] == "1":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Account.sites_allowed.desc())
else:
results_ord = result_qry.order_by(Account.sites_allowed.asc())
if sort['column'] == "2":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Account.account_type.desc())
else:
results_ord = result_qry.order_by(Account.account_type.asc())
if sort['column'] == "3":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Account.expiresat.desc())
else:
results_ord = result_qry.order_by(Account.expiresat.asc())
else:
results_ord = result_qry.order_by(Account.name.desc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
def to_table_row(self):
expiresat = self.expiresat.strftime(
"%m/%d/%Y") if self.expiresat else ''
if self.account_type == ACCOUNT_TYPE_FREE:
account_type = 'FREE'
elif self.account_type == ACCOUNT_TYPE_SILVER:
account_type = 'PERM'
elif self.account_type == ACCOUNT_TYPE_GOLD:
account_type = 'PERM'
elif self.account_type == ACCOUNT_TYPE_GOLD_PREM:
account_type = 'PERM'
return dict_normalise_values({'name':self.name,'sites_allowed':self.sites_allowed,'account_type':account_type,\
'expiresat':expiresat,'id':self.id} )
class Guestsession(db.Model):
''' Class to represent guest session. Each session is associated to a Guest and will have a state associated with it.
'''
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
device_id = db.Column(db.Integer, db.ForeignKey('device.id'))
voucher_id = db.Column(db.Integer, db.ForeignKey('voucher.id'))
starttime = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
lastseen = db.Column(db.DateTime,
index=True,
default=datetime.datetime.utcnow)
stoptime = db.Column(
db.DateTime, index=True
) #Time at which session is stopped, to be filled by session updator
expiry = db.Column(db.DateTime,
index=True,
default=datetime.datetime.utcnow
) #predicted expiry time,default to 60 minutes
temp_login = db.Column(db.Integer, default=0)
duration = db.Column(db.Integer, default=60)
ban_ends = db.Column(db.DateTime, index=True)
data_used = db.Column(db.String(20),
default=0) #Data used up in this session
state = db.Column(db.Integer)
mac = db.Column(db.String(30), index=True)
d_updated = db.Column(db.String(20)) #data updated last
guesttracks = db.relationship('Guesttrack',
backref='guestsession',
lazy='dynamic')
demo = db.Column(db.Integer, default=0, index=True)
obj_id = db.Column(
db.String(30),
index=True) #_id of document in guest collection of unifi
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Guestsession.query.filter(
and_(Guestsession.site_id == modal_filters['siteid'],
Guestsession.starttime >= modal_filters['startdate'],
Guestsession.starttime <= modal_filters['enddate'],
Guestsession.obj_id != None))
if term:
result_qry = main_query
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(
Guestsession.starttime.desc())
else:
results_ord = result_qry.order_by(Guestsession.starttime.asc())
elif sort['column'] == "1":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guestsession.stoptime.desc())
else:
results_ord = result_qry.order_by(Guestsession.stoptime.asc())
elif sort['column'] == "2":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guestsession.mac.desc())
else:
results_ord = result_qry.order_by(Guestsession.mac.asc())
elif sort['column'] == "3":
if sort['order'] == "desc":
results_ord = result_qry.order_by(
Guestsession.data_used.desc())
else:
results_ord = result_qry.order_by(Guestsession.data_used.asc())
else:
results_ord = result_qry.order_by(Guestsession.id.asc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
def to_table_row(self):
site = self.site
stoptime = 'N/A'
starttime = arrow.get(self.starttime).to(
site.timezone).format('DD-MM-YYYY HH:mm:ss')
if self.stoptime:
stoptime = arrow.get(self.stoptime).to(
site.timezone).format('DD-MM-YYYY HH:mm:ss')
#get data_used and convert to Mb
dta = int(self.data_used) if self.data_used else 0
data_used = str(math.ceil((dta / 1024000.0)))
return {
'id': self.id,
'stoptime': stoptime,
'starttime': starttime,
'mac': self.mac,
'data_used': data_used,
'phonenumber': self.device.get_phonenumber()
}
def to_list(self):
site = self.site
starttime = arrow.get(self.starttime).to(
site.timezone).format('DD-MM-YYYY HH:mm:ss')
stoptime = 'N/A'
if self.stoptime:
stoptime = arrow.get(self.stoptime).to(
site.timezone).format('DD-MM-YYYY HH:mm:ss')
#get data_used and convert to Mb
dta = int(self.data_used) if self.data_used else 0
data_used = str(math.ceil((dta / 1024000.0)))
list_to_clean = [
starttime, stoptime, self.mac, data_used,
self.device.get_phonenumber()
]
return [x if x else '' for x in list_to_clean]
class Guest(db.Model):
''' Class to represent guest profile, it will be filled fully/partially depending upon site configuration
'''
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
firstname = db.Column(db.String(60))
lastname = db.Column(db.String(60))
age = db.Column(db.Integer, index=True)
gender = db.Column(db.Integer, index=True)
state = db.Column(db.Integer, index=True)
email = db.Column(db.String(60))
phonenumber = db.Column(db.String(15))
agerange = db.Column(db.String(15))
devices = db.relationship('Device', backref='guest', lazy='dynamic')
fb_profile = db.Column(db.Integer, db.ForeignKey('facebookauth.id'))
fb_liked = db.Column(db.Integer)
fb_posted = db.Column(db.Integer)
created_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
apisync = db.Column(db.Integer,
index=False) #Flag to be set after syncing to API
synchedat = db.Column(db.DateTime, index=True) #synched time in UTC
demo = db.Column(db.Boolean(), default=0, index=True)
newsletter = db.Column(db.Boolean(), default=0, index=True)
dob = db.Column(db.String(15))
details = db.Column(db.Text)
def get_device_phonenumber(self):
for device in self.devices:
phonenumber = device.get_phonenumber()
if phonenumber:
return phonenumber
return ''
def get_gender(self):
if self.gender == 1:
return 'M'
elif self.gender == 2:
return 'F'
else:
return 'N/A'
def populate_from_email_form(self, form, form_fields):
details = ''
if hasattr(form, 'email'):
self.email = form.email.data
if hasattr(form, 'firstname'):
self.firstname = form.firstname.data
if hasattr(form, 'lastname'):
self.lastname = form.lastname.data
if hasattr(form, 'phonenumber'):
self.phonenumber = form.phonenumber.data
if hasattr(form, 'dob'):
self.dob = form.dob.data
if hasattr(form, 'newsletter'):
self.newsletter = form.newsletter.data
if hasattr(form, 'extra1'):
details += '%s:%s ' % (form.extra1.label.text, form.extra1.data)
if hasattr(form, 'extra2'):
details += '%s:%s' % (form.extra2.label.text, form.extra2.data)
self.details = details
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Guest.query.filter(
and_(Guest.site_id == modal_filters['siteid'], Guest.demo == 0,
Guest.created_at >= modal_filters['startdate'],
Guest.created_at <= modal_filters['enddate']))
if term:
result_qry = main_query
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.firstname.desc())
else:
results_ord = result_qry.order_by(Guest.firstname.asc())
elif sort['column'] == "1":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.lastname.desc())
else:
results_ord = result_qry.order_by(Guest.lastname.asc())
elif sort['column'] == "2":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.age.desc())
else:
results_ord = result_qry.order_by(Guest.age.asc())
elif sort['column'] == "3":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.gender.desc())
else:
results_ord = result_qry.order_by(Guest.gender.asc())
elif sort['column'] == "4":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.phonenumber.desc())
else:
results_ord = result_qry.order_by(Guest.phonenumber.asc())
elif sort['column'] == "5":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.email.desc())
else:
results_ord = result_qry.order_by(Guest.email.asc())
elif sort['column'] == "6":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.created_at.desc())
else:
results_ord = result_qry.order_by(Guest.created_at.asc())
else:
results_ord = result_qry.order_by(Guest.firstname.asc())
results = results_ord.offset(offset).limit(limit).all()
print {'total': total, 'results': results}
return {'total': total, 'results': results}
def to_table_row(self):
created_at = self.created_at.strftime(
"%d/%m/%Y") if self.created_at else ''
phonenumber = self.phonenumber if self.phonenumber else self.get_device_phonenumber(
)
newsletter = 'yes' if self.newsletter else 'no'
return {
'firstname': self.firstname,
'age': self.age,
'email': self.email,
'lastname': self.lastname,
'phonenumber': self.phonenumber,
'id': self.id,
'gender': self.get_gender(),
'created_at': created_at,
'site_id': self.site_id,
'newsletter': self.newsletter,
'dob': self.dob,
'details': self.details,
'agerange': self.agerange
}
def to_dict(self):
created_at = self.created_at.strftime(
"%d/%m/%Y") if self.created_at else ''
phonenumber = self.phonenumber if self.phonenumber else self.get_device_phonenumber(
)
newsletter = 'yes' if self.newsletter else 'no'
return {
'firstname': self.firstname,
'age': self.age,
'email': self.email,
'lastname': self.lastname,
'phonenumber': self.phonenumber,
'id': self.id,
'gender': self.get_gender(),
'created_at': created_at,
'site_id': self.site_id,
'newsletter': self.newsletter,
'dob': self.dob,
'details': self.details,
'agerange': self.agerange
}
def to_list(self):
created_at = self.created_at.strftime(
"%d/%m/%Y") if self.created_at else ''
phonenumber = self.phonenumber if self.phonenumber else self.get_device_phonenumber(
)
newsletter = 'yes' if self.newsletter else 'no'
#convert to list add replace None by ''
list_to_clean = [self.firstname,self.lastname,self.email,created_at,phonenumber,self.age,self.get_gender(),\
newsletter,self.dob,self.agerange,self.details]
return [
x.encode('ascii', 'ignore') if x else '' for x in list_to_clean
]
def get_csv_headers(self):
return [
'Firstname', 'Lastname', 'Email', 'Created On', 'Phone', 'Age',
'Gender', "Newsletter", 'DOB', 'Age Range', 'Details'
]
class Guest(db.Model):
''' Class to represent guest profile, it will be filled fully/partially depending upon site configuration
'''
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
firstname = db.Column(db.String(60))
lastname = db.Column(db.String(60))
age = db.Column(db.Integer, index=True)
gender = db.Column(db.Integer, index=True)
state = db.Column(db.Integer, index=True)
email = db.Column(db.String(60))
phonenumber = db.Column(db.String(15))
devices = db.relationship('Device', backref='guest', lazy='dynamic')
fb_profile = db.Column(db.Integer, db.ForeignKey('facebookauth.id'))
fb_liked = db.Column(db.Integer)
fb_posted = db.Column(db.Integer)
created_at = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
index=True)
apisync = db.Column(db.Integer,
index=False) #Flag to be set after syncing to API
synchedat = db.Column(db.DateTime, index=True) #synched time in UTC
demo = db.Column(db.Boolean(), default=0, index=True)
def populate_from_email_form(self, form, form_fields):
if hasattr(form, 'email'):
self.email = form.email.data
if hasattr(form, 'firstname'):
self.firstname = form.firstname.data
if hasattr(form, 'lastname'):
self.lastname = form.lastname.data
if hasattr(form, 'phonenumber'):
self.phonenumber = form.phonenumber.data
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Guest.query.filter(
and_(Guest.site_id == modal_filters['siteid'], Guest.demo == 0))
if term:
result_qry = main_query
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.firstname.desc())
else:
results_ord = result_qry.order_by(Guest.firstname.asc())
elif sort['column'] == "1":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.lastname.desc())
else:
results_ord = result_qry.order_by(Guest.lastname.asc())
elif sort['column'] == "2":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.age.desc())
else:
results_ord = result_qry.order_by(Guest.age.asc())
elif sort['column'] == "3":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.gender.desc())
else:
results_ord = result_qry.order_by(Guest.gender.asc())
elif sort['column'] == "4":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.phonenumber.desc())
else:
results_ord = result_qry.order_by(Guest.phonenumber.asc())
elif sort['column'] == "5":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Guest.email.desc())
else:
results_ord = result_qry.order_by(Guest.email.asc())
else:
results_ord = result_qry.order_by(Guest.firstname.asc())
results = results_ord.offset(offset).limit(limit).all()
print {'total': total, 'results': results}
return {'total': total, 'results': results}
def to_table_row(self):
return {
'firstname': self.firstname,
'age': self.age,
'email': self.email,
'lastname': self.lastname,
'phonenumber': self.phonenumber,
'id': self.id,
'gender': self.gender
}
class Admin(User):
''' Class to represent admin, each admin will be associated with a user of type admin
'''
id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
sites = db.relationship('Wifisite', backref='admin', lazy='dynamic')
account_id = db.Column(db.Integer, db.ForeignKey('account.id'))
__mapper_args__ = {'polymorphic_identity': 'admin'}
def check_admin(self):
return True
def get_user_type(self):
return ROLE_ADMIN
def get_admin_id(self):
return NotImplemented
def check_client(self):
return False
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
if modal_filters:
main_query = Admin.query.filter_by(
account_id=modal_filters['account_id'])
else:
main_query = Admin.query
if term:
result_qry = main_query.outerjoin(Admin.account).filter(or_( Admin.email.like('%'+term+'%'),\
Admin.displayname.like('%'+term+'%')))
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(User.email.desc())
else:
results_ord = result_qry.order_by(User.email.asc())
else:
results_ord = result_qry.order_by(User.id.asc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
def to_table_row(self):
return {
'displayname': self.displayname,
'email': self.email,
'id': self.id,
'account': self.account.name
}
def populate_from_form(self, form):
self.email = form.email.data
self.displayname = form.displayname.data
if form.password.data:
self.password = encrypt_password(form.password.data)
def to_dict(self):
return {
'displayname': self.displayname,
'email': self.email,
'id': self.id,
'account_id': self.account_id
}
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
roles = db.relationship('Role',
secondary=roles_users,
backref=db.backref('users', lazy='dynamic'))
displayname = db.Column(db.String(255))
last_login_at = db.Column(db.String(255))
current_login_at = db.Column(db.String(255))
last_login_ip = db.Column(db.String(255))
current_login_ip = db.Column(db.String(255))
login_count = db.Column(db.Integer)
confirmed_at = db.Column(db.String(255))
active = db.Column(db.Boolean())
type = db.Column(db.String(50))
__mapper_args__ = {'polymorphic_identity': 'user', 'polymorphic_on': type}
def populate_from_form(self, form):
self.email = form.email.data
self.displayname = form.displayname.data
if form.password.data:
self.password = encrypt_password(form.password.data)
def to_dict(self):
return dict_normalise_values({
'id': self.id,
'email': self.email,
'displayname': self.displayname
})
def __get_obj(self, obj, obj_id):
if obj_id:
obj_instance = obj.query.filter_by(id=obj_id).first()
if obj_instance:
return obj_instance
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = User.query.filter_by()
if term:
result_qry = main_query.filter(
or_(User.email.like('%' + term + '%'),
User.displayname.like('%' + term + '%')))
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(User.email.desc())
else:
results_ord = result_qry.order_by(User.email.asc())
else:
results_ord = result_qry.order_by(User.id.asc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
class Wifisite(db.Model):
''' Class to represent wifi sites. Each client can have multiple sites
'''
id = db.Column(db.Integer, primary_key=True)
client_id = db.Column(db.Integer, db.ForeignKey('client.id'))
admin_id = db.Column(db.Integer, db.ForeignKey('admin.id'))
account_id = db.Column(db.Integer, db.ForeignKey('account.id'))
name = db.Column(db.String(255), index=True, default="defaultsite")
default_landing = db.Column(db.Integer)
landingpages = db.relationship('Landingpage',
backref='site',
lazy='dynamic')
guests = db.relationship('Guest', backref='site', lazy='dynamic')
unifi_id = db.Column(db.String(50), index=True, default="default")
devices = db.relationship('Device', backref='site', lazy='dynamic')
sessions = db.relationship('Guestsession', backref='site', lazy='dynamic')
guesttracks = db.relationship('Guesttrack', backref='site', lazy='dynamic')
sitefiles = db.relationship('Sitefile', backref='site', lazy='dynamic')
facebookauths = db.relationship('Facebookauth',
backref='site',
lazy='dynamic')
vouchers = db.relationship('Voucher', backref='site', lazy='dynamic')
sitestats = db.relationship('Sitestat', backref='site', lazy='dynamic')
template = db.Column(db.String(50), default='template1')
emailformfields = db.Column(db.Integer,
default=(FORM_FIELD_LASTNAME +
FORM_FIELD_FIRSTNAME))
auth_method = db.Column(db.Integer, default=AUTH_TYPE_ALL)
auth_fb_like = db.Column(db.Integer, default=FACEBOOK_LIKE_OFF)
auth_fb_post = db.Column(db.Integer, default=FACEBOOK_POST_OFF)
redirect_method = db.Column(db.Integer, default=REDIRECT_ORIG_URL)
reports_type = db.Column(db.Integer, default=CLIENT_REPORT_WEEKLY)
reports_list = db.Column(db.String(400))
enable_redirect = db.Column(db.Boolean())
redirect_url = db.Column(db.String(200),
default='http://www.unifispot.com')
fb_appid = db.Column(db.String(200))
fb_app_secret = db.Column(db.String(200))
fb_page = db.Column(
db.Text, default='https://www.facebook.com/Unifispot-1652553388349756')
timezone = db.Column(db.String(20), default='UTC')
api_export = db.Column(db.Integer, default=API_END_POINT_NONE)
api_auth_field1 = db.Column(db.String(200))
api_auth_field2 = db.Column(db.String(200))
api_auth_field3 = db.Column(db.String(200))
daily_data_limit = db.Column(db.String(10))
monthly_data_limit = db.Column(db.String(10), default=1000)
session_timelimit = db.Column(db.Integer, default=60)
enable_session_limit = db.Column(db.Boolean())
smsauth = db.Column(db.Boolean())
email_field = db.Column(db.String(20), default='Email ID')
firstname_field = db.Column(db.String(20), default='Firstname')
lastname_field = db.Column(db.String(20), default='Lastname')
dob_field = db.Column(db.String(20), default='DOB')
extra1_field = db.Column(db.String(20), default='Extra1')
extra2_field = db.Column(db.String(20), default='Extra2')
mandatoryfields = db.Column(db.Integer,
default=(MANDATE_FIELD_FIRSTNAME +
MANDATE_FIELD_LASTNAME))
newsletter = db.Column(db.Boolean(), default=0, index=True)
newsletter_field = db.Column(
db.String(50), default='I agree to subscribe to the news letter')
newsletter_mandate = db.Column(db.Boolean(), default=0, index=True)
def populate_from_form(self, form):
self.name = form.name.data
self.unifi_id = form.unifi_id.data
self.template = form.template.data
self.enablehtml = form.enablehtml.data
self.auth_method = (form.auth_fb.data and AUTH_TYPE_SOCIAL) + (
form.auth_phone.data and
AUTH_TYPE_SMS) + (form.auth_voucher.data and AUTH_TYPE_VOUCHER) + (
form.auth_email.data and AUTH_TYPE_EMAIL)
self.auth_fb_like = form.auth_fb_like.data
self.auth_fb_post = form.auth_fb_post.data
self.daily_data_limit = form.daily_data_limit.data
self.enable_session_limit = form.enable_session_limit.data
self.monthly_data_limit = form.monthly_data_limit.data
self.session_timelimit = form.session_timelimit.data
self.smsauth = form.smsauth.data
self.email_field = form.email_field.data
self.firstname_field = form.firstname_field.data
self.lastname_field = form.lastname_field.data
self.dob_field = form.dob_field.data
self.extra1_field = form.extra1_field.data
self.extra2_field = form.extra2_field.data
if self.account.en_advertisement:
self.redirect_method = form.redirect_method.data
self.redirect_url = form.redirect_url.data
self.newsletter = form.newsletter.data
self.newsletter_field = form.newsletter_field.data
self.newsletter_mandate = form.newsletter_mandate.data
self.fb_page = form.fb_page.data
self.timezone = form.timezone.data
if self.account.en_fbauth_change:
self.fb_appid = form.fb_appid.data
self.fb_app_secret = form.fb_app_secret.data
if self.account.en_reporting:
self.reports_type = form.reports_type.data
self.reports_list = form.reports_list.data
self.emailformfields = (form.get_lastname.data and FORM_FIELD_LASTNAME) + (form.get_firstname.data and FORM_FIELD_FIRSTNAME) + \
(form.get_dob.data and FORM_FIELD_DOB) + (form.get_extra1.data and FORM_FIELD_EXTRA1 ) + (form.get_extra2.data and FORM_FIELD_EXTRA2)
self.mandatoryfields = (form.mandate_lastname.data and MANDATE_FIELD_LASTNAME) + \
(form.mandate_firstname.data and MANDATE_FIELD_FIRSTNAME) + \
(form.mandate_dob.data and MANDATE_FIELD_DOB) + (form.mandate_extra1.data and MANDATE_FIELD_EXTRA1 ) + \
(form.mandate_extra2.data and MANDATE_FIELD_EXTRA2)
if self.account.en_api_export:
self.api_export = form.api_export.data
self.api_auth_field1 = form.api_auth_field1.data
self.api_auth_field2 = form.api_auth_field2.data
self.api_auth_field3 = form.api_auth_field3.data
def fb_login_en(self):
return (self.auth_method & AUTH_TYPE_SOCIAL)
def phone_login_en(self):
return (self.auth_method & AUTH_TYPE_SMS)
def voucher_login_en(self):
return (self.auth_method & AUTH_TYPE_VOUCHER)
def email_login_en(self):
return (self.auth_method & AUTH_TYPE_EMAIL)
def to_dict(self):
reports_type = None
reports_list = None
fb_appid = None
fb_app_secret = None
redirect_method = None
redirect_url = None
api_export = None
api_auth_field1 = None
api_auth_field2 = None
api_auth_field3 = None
emailformfields = self.emailformfields if self.emailformfields else 0
mandatoryfields = self.mandatoryfields if self.mandatoryfields else 0
if self.account.en_reporting:
reports_type = self.reports_type
reports_list = self.reports_list
if self.account.en_fbauth_change:
fb_appid = self.fb_appid
fb_app_secret = self.fb_app_secret
if self.account.en_advertisement:
redirect_method = self.redirect_method
redirect_url = self.redirect_url
if self.account.en_api_export:
api_export = self.api_export
api_auth_field1 = self.api_auth_field1
api_auth_field2 = self.api_auth_field2
api_auth_field3 = self.api_auth_field3
return dict_normalise_values({ 'name':self.name,'unifi_id':self.unifi_id, 'id':self.id, \
'template':self.template,
'get_lastname': (emailformfields &FORM_FIELD_LASTNAME),\
'get_firstname': (emailformfields &FORM_FIELD_FIRSTNAME),\
'get_dob': (emailformfields &FORM_FIELD_DOB),\
'get_extra1': (emailformfields &FORM_FIELD_EXTRA1),\
'get_extra2': (emailformfields &FORM_FIELD_EXTRA2),\
'mandate_lastname': (mandatoryfields &MANDATE_FIELD_LASTNAME),\
'mandate_firstname': (mandatoryfields &MANDATE_FIELD_FIRSTNAME),\
'mandate_dob': (mandatoryfields &MANDATE_FIELD_DOB),\
'mandate_extra1': (mandatoryfields &MANDATE_FIELD_EXTRA1),\
'mandate_extra2': (mandatoryfields &MANDATE_FIELD_EXTRA2),\
'auth_fb':(self.auth_method &AUTH_TYPE_SOCIAL),'auth_email':(self.auth_method &AUTH_TYPE_EMAIL),\
'auth_phone':(self.auth_method &AUTH_TYPE_SMS),'auth_voucher':(self.auth_method &AUTH_TYPE_VOUCHER),\
'default_landing':self.default_landing,'reports_type':reports_type, \
'fb_page':self.fb_page,'auth_fb_like':self.auth_fb_like,'auth_fb_post':self.auth_fb_post,\
'fb_appid':fb_appid,'fb_app_secret':fb_app_secret,
'redirect_method':redirect_method,'redirect_url':redirect_url,'timezone':self.timezone,\
'emailformfields':emailformfields,'reports_list':reports_list,'client_id':self.client.id,\
'api_export':api_export,'api_auth_field1':api_auth_field1,'api_auth_field2':api_auth_field2,\
'api_auth_field3':api_auth_field3,'monthly_data_limit':self.monthly_data_limit,\
'daily_data_limit':self.daily_data_limit,\
'smsauth':self.smsauth,'email_field':self.email_field,'firstname_field':self.firstname_field,\
'lastname_field':self.lastname_field,'dob_field':self.dob_field,'extra1_field':self.extra1_field,\
'extra2_field':self.extra2_field,'newsletter':self.newsletter,\
'newsletter_field':self.newsletter_field,'newsletter_mandate':self.newsletter_mandate,
'session_timelimit':self.session_timelimit,'enable_session_limit':self.enable_session_limit})
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Wifisite.query.filter_by()
if term:
result_qry = main_query
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Wifisite.name.desc())
else:
results_ord = result_qry.order_by(Wifisite.name.asc())
else:
results_ord = result_qry.order_by(Landingpage.id.asc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
class Voucher(db.Model):
id = db.Column(db.Integer, primary_key=True)
batchid = db.Column(db.String(40), index=True)
voucher = db.Column(db.String(20), index=True)
notes = db.Column(db.String(50), index=True)
duration_t = db.Column(db.BigInteger())
bytes_t = db.Column(db.BigInteger())
used = db.Column(db.Boolean(), default=False, index=True)
site_id = db.Column(db.Integer, db.ForeignKey('wifisite.id'))
duration = db.Column(db.String(20), index=True)
used_at = db.Column(
db.DateTime, index=True) #used time in UTC,filled once voucher is used
device_id = db.Column(db.Integer, db.ForeignKey('device.id'))
sessions = db.relationship('Guestsession',
backref='voucher',
lazy='dynamic') #to track sessions
def populate_from_form(self, form):
self.notes = form.notes.data
self.bytes_t = form.bytes_t.data
#set duration accordingly
if form.duration_t.data == 1:
self.duration = form.duration.data + ' Hours'
self.duration_t = int(form.duration.data) * 60 * 60
elif form.duration_t.data == 2:
self.duration = form.duration.data + ' Days'
self.duration_t = int(form.duration.data) * 60 * 60 * 24
elif form.duration_t.data == 3:
self.duration = form.duration.data + ' Months'
self.duration_t = int(form.duration.data) * 60 * 60 * 24 * 30
def to_dict(self):
return {
'site':
self.site.name,
'duration':
self.duration,
'status':
'<span class="label label-danger">Used</span>' if self.used else
'<span class="label label-success">Initializing</span>',
'voucher':
self.voucher,
'note':
self.notes,
'bytes_t':
self.bytes_t,
'id':
self.id
}
return dict_server
#Search option with paging and sorting, uses LIKE on INDEXED fields
#and return num of total results as well as number of rows controlled by paging
def search_query(self,
term,
offset=0,
limit=10,
sort=None,
modal_filters=None):
main_query = Voucher.query.filter_by(site_id=modal_filters['siteid'])
if term:
result_qry = main_query.outerjoin(Voucher.site).filter(
or_(Wifisite.name.like('%' + term + '%'),
Voucher.voucher.like('%' + term + '%'),
Voucher.notes.like('%' + term + '%')))
else:
result_qry = main_query
result_qry = result_qry.distinct()
total = result_qry.count()
if sort['column'] == "0":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Wifisite.name.desc())
else:
results_ord = result_qry.order_by(Wifisite.name.asc())
elif sort['column'] == "1":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Voucher.voucher.desc())
else:
results_ord = result_qry.order_by(Voucher.voucher.desc())
elif sort['column'] == "2":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Voucher.duration.desc())
else:
results_ord = result_qry.order_by(Voucher.duration.desc())
elif sort['column'] == "3":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Voucher.used.desc())
else:
results_ord = result_qry.order_by(Voucher.used.desc())
elif sort['column'] == "4":
if sort['order'] == "desc":
results_ord = result_qry.order_by(Voucher.notes.desc())
else:
results_ord = result_qry.order_by(Voucher.notes.desc())
else:
results_ord = result_qry.order_by(Voucher.id.asc())
results = results_ord.offset(offset).limit(limit).all()
return {'total': total, 'results': results}
def to_table_row(self):
duration = '%s:%sMb ' % (self.duration, self.bytes_t)
return {
'site':
self.site.name,
'duration':
duration,
'status':
'<span class="label label-danger">Used</span>' if self.used else
'<span class="label label-primary">Available</span>',
'voucher':
self.voucher,
'note':
self.notes,
'id':
self.id
}
def check_validity(self):
#first check if voucher's expiry time is over
now = arrow.utcnow().timestamp
expiry = arrow.get(self.used_at).timestamp + self.duration_t
if now >= expiry:
return False
#check if data limit us expired
data_consumed = 0
for session in self.sessions:
data_consumed += int(session.data_used)
data_mb = int(math.ceil((data_consumed / 1024000.0)))
if data_mb > self.bytes_t:
return False
return True
def time_available(self):
'''Retuns remaining time in a voucher in minutes
'''
now = arrow.utcnow().timestamp
expiry = arrow.get(self.used_at).timestamp + self.duration_t
return int((expiry - now) / 60)
def data_available(self):
'''Retuns remaining data in a voucher in Mb
'''
data_consumed = 0
for session in self.sessions:
data_consumed += int(session.data_used)
data_mb = int(math.ceil((data_consumed / 1024000.0)))
return self.bytes_t - data_mb
