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