class ConfirmationModel(db.Model): __tablename__ = "confirmations" id = db.Column(db.String(50), primary_key=True) expire_at = db.Column(db.Integer, nullable=False) confirmed = db.Column(db.Boolean, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) user = db.relationship("UserModel") # not stored in db def __init__(self, user_id: int, **kwargs): super().__init__(**kwargs) self.user_id = user_id self.id = uuid4().hex self.expire_at = int(time()) + CONFIRMATION_EXPIRATION_DELTA self.confirmed = False @classmethod def find_by_id(cls, _id: str) -> "ConfirmationModel": return cls.query.filter_by(id=_id).first() @property # because it only returns value def expired(self) -> bool: return time() > self.expire_at def force_to_expire(self) -> None: if not self.expired: self.expire_at = int(time()) self.save_to_db() def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit()
from db import db post_tags = db.Table('post_tags', db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'), primary_key=True), db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True) ) class TagModel(db.Model): __tablename__ = 'tags' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False, unique=True) posts = db.relationship('PostModel',secondary=post_tags, back_populates="tags") def json(self): return { "id":self.id, "name":self.name }
from db import db book_tag_table = db.Table( 'book_tag', db.Model.metadata, db.Column('book_id', db.Integer, db.ForeignKey('book.id')), db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')))
from db import db from models.servicenote import ServiceNote language_association = db.Table( 'language_association', db.Column('person_id', db.Integer, db.ForeignKey('person.id'), primary_key=True), db.Column('language_id', db.Integer, db.ForeignKey('language.id'), primary_key=True), ) class Person(db.Model): id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String) last_name = db.Column(db.String) email = db.Column(db.String) phone = db.Column(db.String) origin_country = db.Column(db.String) languages = db.relationship('Language', secondary=language_association) def service_notes(self, service: int): return ServiceNote.query.filter_by(service_id=service).all()
class Match(db.Model): id = db.Column(db.Integer, primary_key=True) blue_offense_player = db.Column(db.String(32), db.ForeignKey('player.username')) blue_defense_player = db.Column(db.String(32), db.ForeignKey('player.username')) red_offense_player = db.Column(db.String(32), db.ForeignKey('player.username')) red_defense_player = db.Column(db.String(32), db.ForeignKey('player.username')) blue_offense_player_skill_gain_overall = db.Column(db.Float) blue_defense_player_skill_gain_overall = db.Column(db.Float) blue_offense_player_skill_gain_offense = db.Column(db.Float) blue_defense_player_skill_gain_defense = db.Column(db.Float) red_offense_player_skill_gain_overall = db.Column(db.Float) red_defense_player_skill_gain_overall = db.Column(db.Float) red_offense_player_skill_gain_offense = db.Column(db.Float) red_defense_player_skill_gain_defense = db.Column(db.Float) blue_points = db.Column(db.Integer) red_points = db.Column(db.Integer) predicted_win_prob_for_blue = db.Column(db.Float) match_balance = db.Column(db.Float) def winner(self): return "blue" if self.blue_points > self.red_points else "red" def serialize(self): return { "id": self.id, "players": { "blue": { "offense": { "name": self.blue_offense_player, "skill_gain_overall": self.blue_offense_player_skill_gain_overall, "skill_gain_offense": self.blue_offense_player_skill_gain_offense }, "defense": { "name": self.blue_defense_player, "skill_gain_overall": self.blue_defense_player_skill_gain_overall, "skill_gain_defense": self.blue_defense_player_skill_gain_defense } }, "red": { "offense": { "name": self.red_offense_player, "skill_gain_overall": self.red_offense_player_skill_gain_overall, "skill_gain_offense": self.red_offense_player_skill_gain_offense }, "defense": { "name": self.red_defense_player, "skill_gain_overall": self.red_defense_player_skill_gain_overall, "skill_gain_defense": self.red_defense_player_skill_gain_defense } } }, "points": { "blue": self.blue_points, "red": self.red_points }, "predicted_win_prob_for_blue": self.predicted_win_prob_for_blue, "match_balance": self.match_balance, "winner": self.winner() }
class HiddenFileModel(db.Model): __tablename__ = "hiddenfiles" id = db.Column(db.Integer, primary_key=True) email = db.Column(db.ForeignKey('users.email'), nullable=False) submitUUID = db.Column(db.CHAR(36), nullable=False) # upload froms submitName = db.Column(db.String(80), nullable=False) modelDesc = db.Column(db.Text()) huggingfaceOrganizationName = db.Column(db.String(80)) huggingfaceRepoName = db.Column(db.String(80)) huggingfaceCommonHash = db.Column(db.String(80)) paramShared = db.Column(db.String(80)) task = db.Column(db.Enum(Task), nullable=False) # others state = db.Column(db.Enum(Status), nullable=False, default=Status.UPLOADED) stateInfo = db.Column(db.String(80)) aoeTimeUpload = db.Column(db.DateTime, nullable=False) dateUpload = db.Column(db.DateTime, default=db.func.current_timestamp()) showOnLeaderboard = db.Column(db.Enum(Show), nullable=False, default=Show.NO) scores = db.relationship("HiddenScoreModel", backref="hiddenfiles") @classmethod def find_by_email(cls, email: str) -> "HiddenFileModel": return cls.query.filter_by(email=email) @classmethod def find_by_submitID(cls, submitUUID: str) -> "HiddenFileModel": return cls.query.filter_by(submitUUID=submitUUID).first() @classmethod def find_by_submitID_task_and_modity_score(cls, submitUUID: str, task: str, score) -> None: submission = cls.query.filter_by(submitUUID=submitUUID).first() setattr(submission.scores[0], task, float(score)) db.session.commit() @classmethod def reset_same_task_show_attribute(cls, email: str, task: enum.Enum) -> None: submissions = cls.query.filter_by(email=email, task=task).all() for submission in submissions: submission.showOnLeaderboard = Show.NO db.session.commit() @classmethod def set_show_attribute_by_submitID(cls, submitUUID) -> None: submission = cls.query.filter_by(submitUUID=submitUUID).first() submission.showOnLeaderboard = Show.YES db.session.commit() @classmethod def unset_show_attribute_by_submitID(cls, submitUUID) -> None: submission = cls.query.filter_by(submitUUID=submitUUID).first() submission.showOnLeaderboard = Show.NO db.session.commit() @classmethod def find_all(cls) -> List["HiddenFileModel"]: return cls.query.all() @classmethod def find_show_on_leaderboard(cls) -> List["HiddenFileModel"]: return cls.query.filter_by(showOnLeaderboard=Show.YES).all() @classmethod def get_upload_count_by_mail(cls, email: str) -> int: return cls.query.filter_by(email=email).count() @classmethod def get_interval_upload_count_by_mail(cls, email: str, AOEtime: datetime.datetime) -> int: return cls.query.filter_by(email=email).filter(cls.aoeTimeUpload >= AOEtime).count() def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit()
class Collect(db.Model): # 用户收藏 __tablename__ = 'collect' id = db.Column(INTEGER(unsigned=True), primary_key=True, autoincrement=True) # primary key category_id = db.Column(INTEGER(unsigned=True), db.ForeignKey('category.id')) feed_id = db.Column(INTEGER(unsigned=True), db.ForeignKey('feed.id')) create_time = db.Column(db.DateTime, default=datetime.utcnow)
class ItemModel(db.Model): # Set up schema for SQLAlchemy operations __tablename__ = "items" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(90)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) # join the tables store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return {'name': self.name, 'price': self.price} @classmethod def find_by_name(cls, name): # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "SELECT * FROM items WHERE name=?" # result = cursor.execute(query, (name,)) # row = result.fetchone() # connection.close() # if row: # return cls(*row) return cls.query.filter_by(name=name).first() def save_to_db(self): # # Add item to database # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "INSERT INTO items VALUES (?,?)" # cursor.execute(query, (self.name, self.price)) # # connection.commit() # connection.close() # SQLAlchemy does 'upserting' (insert or update) db.session.add(self) db.session.commit() # def update(self): # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # # query = "ITEMS items SET price=? WHERE name=?" # cursor.execute(query, (self.price, self.name)) # # connection.commit() # connection.close() # return {'message': 'Item updated.'} def delete_from_db(self): db.session.delete(self) db.session.commit()
import sqlite3 from sqlalchemy import Boolean, Column, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy import DateTime, Integer, String, Text, Float, Boolean from db import db from models.mysql.permiso import Permiso permisoXUsuario = db.Table('permisosxUsuarios', db.Column('usuario_id', db.Integer, db.ForeignKey('usuarios.id')), db.Column('permiso_id', db.Integer, db.ForeignKey('permisos.id_permiso')) ) class Usuario(db.Model): __tablename__ = 'usuarios' __table_args__ = {'mysql_engine':'InnoDB','mysql_charset':'utf8','mysql_collate':'utf8_general_ci'} id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(60)) nombre = db.Column(String(30)) password = db.Column(String(80)) habilitado = db.Column(db.Boolean, default = True, nullable=True) direccion = db.Column(String(50)) telefono = db.Column(String(120)) permisos = db.relationship('Permiso',secondary =permisoXUsuario, backref = db.backref('permisos'),lazy = 'dynamic') id_grupoDeTrabajo = db.Column(db.Integer,default = 0) esJefeDe = db.Column(db.Integer,default = 0) def __init__(self, nombre, email, password,direccion,telefono,permisos,id_grupoDeTrabajo,esJefeDe): from servicios.permisosService import PermisosService self.nombre = nombre self.email = email self.password = password
class Branch(ActiveModel, db.Model): __tablename__ = "branches" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) port = db.Column(db.Integer, unique=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) status = db.Column(db.String(20)) created = db.Column(db.DateTime) container_id = db.Column(db.String(12)) custom_config = db.Column(db.Text) user = db.relationship("User", backref="branches") BASE_PORT = 8000 END_PORT = 9000 DEFAULT_NUM_LINES = 1000 def run_container(self): if self.status != 'Running': self.port = self.get_available_port() self.created = datetime.utcnow() # the following need adequate permission self.start_container() if self.status == 'Running': self.add_vhost() self.reload_nginx() self.save() @classmethod def get_available_port(cls): used = {b.port for b in Branch.query.filter_by(status='Running').order_by(Branch.port).all()} port = randint(cls.BASE_PORT, cls.END_PORT) while port in used: port = randint(cls.BASE_PORT, cls.END_PORT) return port def add_vhost(self): template = self.get_vhost_template() with open("{}/{}.conf".format( current_app.config['NGINX_SITES_PATH'], slugify(self.name)), "wb") as fh: fh.write(template) def get_vhost_template(self): context = dict( branch=slugify(self.name), hostname=request.host.split(':')[0], port=self.port, app_path=current_app.root_path ) return render_template('vhost/branch.conf.j2', **context) def start_container(self): if not self.port: self.port = self.get_available_port() cmd = [ 'docker', 'run', '-d', '-p', '{}:{}'.format(self.port, 5000), ] if self.custom_config: # Replace Macros for k, v in self.get_macros().items(): self.custom_config = self.custom_config.replace(k, v) configs = yaml.safe_load(self.custom_config) for k, v in configs.items(): cmd.extend(['-e', "{}={}".format(k, v)]) cmd.extend([ current_app.config['CONTAINER_NAME'], self.name, "{}.{}".format(slugify(self.name), request.host.split(':')[0]) ]) container_id = subprocess.check_output(cmd) if container_id: self.container_id = container_id[:12] self.status = self.check_status(self.container_id) def stop_container(self): cmd = ['docker', 'stop', self.container_id] container_id = subprocess.check_output(cmd) if container_id: self.container_id = container_id[:12] self.status = self.check_status(self.container_id) self.port = None def rm_container(self): cmd = ['docker', 'rm', self.container_id] subprocess.check_output(cmd) @staticmethod def reload_nginx(): subprocess.call(current_app.config['NGINX_RELOAD_CMD'].split(' ')) @staticmethod def is_container_running(container_id): return container_id and container_id.encode('ascii') in subprocess.check_output(['docker', 'ps']) @staticmethod def check_status(container_id): if Branch.is_container_running(container_id): status = 'Running' else: status = 'Stopped' return status def get_logs(self, num_lines, reverse=False): try: num_lines = int(num_lines) except: num_lines = self.DEFAULT_NUM_LINES if self.is_container_running(self.container_id): logs = docker_client.containers.get(self.container_id).logs(tail=num_lines).splitlines() if reverse: logs = reversed(logs) return "\n".join(logs) @staticmethod def get_log_stream(container_id): return docker_client.containers.get(container_id).logs(stream=True) @property def hostname(self): return "{}.{}".format(slugify(self.name), request.host.split(':')[0]) def get_macros(self): return { '$host': self.hostname }
class ProductCommentModel(db.Model): __tablename__ = "product_comments" id = db.Column(db.Integer, primary_key=True) productId = db.Column(db.Integer, db.ForeignKey('products.id')) userName = db.Column(db.String(60)) userEmail = db.Column(db.String(60)) comment = db.Column(db.String(5000)) seen = db.Column(db.Boolean) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) #Reaction #1 - Liked #2 - Disliked product = db.relationship('ProductModel') stats = [] def __init__(self, productId, userName, userEmail, comment): self.productId = productId self.userName = userName self.userEmail = userEmail self.comment = comment self.seen = False def json(self): return { "id": self.id, "productId": self.productId, "userName": self.userName, "userEmail": self.userEmail, "comment": self.comment, "stats": [x.json() for x in self.stats], "created_at": self.created_at.isoformat() } @classmethod def find_by_id(cls, _id): return cls.query.get(_id) @classmethod def get_comments(cls, id, offset): comments = cls.query.filter_by(productId=id).order_by( cls.created_at).offset(offset).limit(30).all() cstats = ProductCommentStatModel.find_by_comments( [x.id for x in comments]) for c in comments: c.stats = [] for s in cstats: if s.commentId == c.id: c.stats.append(s) return comments def set_reaction(self, userid, reaction): try: user = UserModel.find_by_user(userid) if not user: user = UserModel(userid) user.save() stat = ProductCommentStatModel(self.id, user.id) else: stat = ProductCommentStatModel.find(self.id, user.id) if not stat: stat = ProductCommentStatModel(self.id, user.id) stat.reaction = reaction stat.save() return True except: return False @classmethod def delete_all_comments(cls, productId): try: stats = cls.query.filter_by(productId=productId) for s in stats: s.delete() return True except: return False def save(self): db.session.add(self) db.session.commit() def delete(self): ProductCommentStatModel.delete_all_comment_stats(self.id) db.session.delete(self) db.session.commit()
class PlanExclusion(db.Model): __tablename__ = 'plan_exclusion' id = db.Column(db.Integer, primary_key=True) plan_id = db.Column(db.Integer, db.ForeignKey('plan.id')) path = db.Column(db.String)
from config import Configuration from db import db from models.tag import TagModel from models.category import CategoryModel post_tags = db.Table( 'post_tags', db.Column('post_id', db.Integer, db.ForeignKey('posts.id')), db.Column('tag_id', db.Integer, db.ForeignKey(TagModel.id))) class PostModel(db.Model): __tablename__ = 'posts' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(Configuration.MAX_POST_TITLE_SIZE)) body = db.Column(db.Text) user_id = db.Column(db.Integer, nullable=False) is_published = db.Column(db.Boolean, nullable=False) tags = db.relationship('TagModel', secondary=post_tags, backref=db.backref('posts'), lazy='dynamic') category_id = db.Column('category_id', db.Integer, db.ForeignKey(CategoryModel.id)) comments = db.relationship('models.comment.CommentModel', backref=db.backref('posts'),
class SessionModel(db.Model): __tablename__ = "sessions" id = db.Column(db.Integer, primary_key=True) sessionName = db.Column(db.String(200)) sessionNumber = db.Column(db.Integer) active = db.Column(db.Integer) dateCreated = db.Column(db.DateTime) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship("UserModel") assets = db.relationship("AssetModel", secondary=session_asset, lazy="subquery", backref=db.backref("sessions", lazy=True)) def json(self): return { "SessionName": self.sessionName, "active": self.active, "assets": [asset.assetname for asset in self.assets], "user_id": self.user_id, "dateCreated": self.dateCreated.__str__() } def __init__(self): num = next_session_num() self.sessionName = "Untitled Session " + str(num) self.sessionNumber = num self.user_id = current_user.get_id() self.active = 1 self.dateCreated = datetime.now() def save_to_db(self): oldSession = SessionModel.query.filter_by(user_id=self.user_id, active=self.active).first() if (oldSession): oldSession.active = 0 db.session.add(self) db.session.commit() def update_name(self, name): self.sessionName = name db.session.add(self) db.session.commit() def remove_from_db(self): for asset in self.assets: for date in asset.get_dates_for_session(self.sessionNumber): db.session.delete(date) db.session.delete(self) db.session.commit() def add_asset(self, assetObj, tab): self.assets.append(assetObj) assetObj.add_to_session(self.id, tab) @classmethod def find_by_sessionId(cls, id): return cls.query.filter_by(id=id, user_id=current_user.get_id()).first() @classmethod def find_by_sessionNumber(cls, number): return cls.query.filter_by(sessionNumber=number, user_id=current_user.get_id()).first() @classmethod def find_active_session(cls): return cls.query.filter_by(active=1, user_id=current_user.get_id()).first() @classmethod def find_all_sessions(cls): return cls.query.filter_by(user_id=current_user.get_id())
from db import db from models.genre import GenreModel from models.actor import ActorModel from models.language import LanguageModel from models.country import CountryModel from models.rating import RatingModel genres = db.Table( 'movies_genres', db.Column('moviesId', db.Integer, db.ForeignKey('movies.id'), primary_key=True), db.Column('genresId', db.Integer, db.ForeignKey('genres.id'), primary_key=True)) actors = db.Table( 'movies_actors', db.Column('moviesId', db.Integer, db.ForeignKey('movies.id'), primary_key=True), db.Column('actorsId', db.Integer, db.ForeignKey('actors.id'), primary_key=True)) languages = db.Table(
class TaskModel(db.Model): __tablename__ = 'tasks' id = db.Column(db.Integer, primary_key=True) time_added = db.Column(db.DateTime, default=current_time) time_updated = db.Column(db.DateTime, default=current_time, onupdate=current_time) time_started = db.Column(db.DateTime, unique=False) time_completed = db.Column(db.DateTime, unique=False, onupdate=end_time) state = db.Column(db.Enum(TaskState), default=TaskState.open) type = db.Column(db.Enum(TaskType), nullable=False) progress = db.Column(db.Integer) host = db.Column(db.String(40)) title_id = db.Column(db.Integer, db.ForeignKey('titles.id', ondelete='CASCADE'), nullable=True) title = db.relationship('TitleModel', backref='tasks') @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_path(cls, path): return cls.query.filter_by(dest_path=path).first() @classmethod def find_by_state(cls, states): return cls.query.filter(cls.state.in_(states)).all() @classmethod def search(cls, params): q = db.session.query(cls) for key, value in params.items(): # Perform an exact match query on fields that require it if key == 'state' or key == 'id' or key == 'type': q = q.filter(getattr(cls, key) == value) # Perform a like query on everything else else: q = q.filter(getattr(cls, key).like("%%{}%%".format(value))) return q.all() @classmethod def count_by_state(cls): count_dict = dict() for type in TaskType: count_dict[type.value] = dict() for state in TaskState: count_dict[type.value][state.value] = 0 counts = cls.query.with_entities(cls.state, cls.type, func.count(cls.state).label('count')).group_by(cls.state, cls.type).all() for count in counts: count_dict[count.type][count.state] = count.count return count_dict @classmethod def next_task(cls, host): # Sequence of tasks with higher priority. Any types not listed are prioritized by earliest added task_priority = (TaskType.scan, TaskType.title_info, TaskType.preview) for task_type in task_priority: next_task = cls.query.filter(and_(cls.state == TaskState.open, cls.type == task_type)).first() if next_task is not None: break if next_task is None: next_task = cls.query.filter(cls.state == TaskState.open).order_by(cls.time_added).first() if next_task: next_task.host = host next_task.state = TaskState.active next_task.time_started = datetime.now(timezone.utc) next_task.save_to_db() return next_task def save_to_db(self): # self.time_updated = datetime.now(timezone.utc) db.session.add(self) db.session.commit()
from db import db author_works = db.Table( 'author_works', db.Column('author_id', db.Integer, db.ForeignKey('author.author_id')), db.Column('book_id', db.Integer, db.ForeignKey('book.book_id'))) class Author(db.Model): author_id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) works = db.relationship('Book', secondary=author_works, backref=db.backref('works')) class Book(db.Model): book_id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String)
from db import db b = db.Table( 'b', db.Column('wishlist_id', db.Integer, db.ForeignKey('wishlists.id')), db.Column('book_id', db.Integer, db.ForeignKey('books.id'))) class WishlistModel(db.Model): __tablename__ = 'wishlists' id = db.Column(db.Integer, primary_key=True) id_account = db.Column(db.Integer, db.ForeignKey('accounts.id'), nullable=False) # books = db.relationship('BookModel', backref='books_wl', lazy=True) books = db.relationship('BookModel', secondary=b, backref=db.backref('book_wl', lazy='dynamic')) def __init__(self, id_account): self.id_account = id_account @classmethod def find_by_id(cls, idd): return db.session.query(WishlistModel).filter_by(id=idd).first() @classmethod def find_by_account(cls, id_account): return db.session.query(WishlistModel).filter_by( id_account=id_account).first()
class AuthToken(db.Model, Base): __tablename__ = "AuthToken" id = db.Column(db.String(36), primary_key=True, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("User.id"), nullable=False) expiry = db.Column(db.DateTime, nullable=False) last_used = db.Column(db.DateTime, nullable=False) created = db.Column(db.DateTime, nullable=False) def __init__(self, user_id): self.user_id = user_id now = datetime.utcnow() self.id = str(uuid4()) self.expiry = now + timedelta(days=30) self.last_used = now self.created = now db.session.add(self) db.session.commit() @classmethod def get_active_by_id(cls, token): return cls.query.filter(cls.id == token, cls.expiry > datetime.utcnow()).first() @classmethod def authenticate_user_or_admin(cls, func): @wraps(func) def wrapped_func(*args, **kwargs): decl_admin_password = request.headers.get("x-admin") decl_token = request.headers.get("x-token") if decl_admin_password is None and decl_token is None: return api_message(lang.auth_token_missing, 401) # Authenticate Admin if decl_admin_password is not None: if check_admin_password(decl_admin_password) is False: return api_message(lang.admin_password_invalid, 401) kwargs["auth_user_id"] = 0 # Authenticate User else: authtoken = cls.get_active_by_id(decl_token) if authtoken is None: return api_message(lang.auth_token_invalid, 401) kwargs["auth_user_id"] = authtoken.user_id authtoken.last_used = datetime.utcnow() db.session.commit() return func(*args, **kwargs) return wrapped_func @classmethod def authenticate_user(cls, func): @wraps(func) def wrapped_func(*args, **kwargs): decl_token = request.headers.get("x-token") if decl_token is None: return api_message(lang.auth_token_missing, 401) authtoken = cls.get_active_by_id(decl_token) if authtoken is None: return api_message(lang.auth_token_invalid, 401) kwargs["auth_user_id"] = authtoken.user_id authtoken.last_used = datetime.utcnow() db.session.commit() return func(*args, **kwargs) return wrapped_func @classmethod def authenticate_admin(cls, func): @wraps(func) def wrapped_func(*args, **kwargs): decl_admin_password = request.headers.get("x-admin") if decl_admin_password is None: return api_message(lang.admin_password_missing, 401) if check_admin_password(decl_admin_password) is False: return api_message(lang.admin_password_invalid, 401) return func(*args, **kwargs) return wrapped_func @classmethod def delete_expired(cls): for authtoken in cls.query.filter(cls.expiry < datetime.utcnow()): authtoken.delete(commit=False) db.session.commit()
class EmailCampaign(db.Model): __tablename__ = 'email_campaign' id = db.Column('Id', db.Integer, primary_key=True) user_id = db.Column('UserId', db.BIGINT, db.ForeignKey('user.Id', ondelete='CASCADE')) base_campaign_id = db.Column( 'BaseCampaignId', db.BIGINT, db.ForeignKey('base_campaign.id', ondelete='CASCADE')) name = db.Column('Name', db.String(127), nullable=False) type = db.Column('Type', db.String(63)) is_hidden = db.Column('IsHidden', db.Boolean, default=False) subject = db.Column('emailSubject', db.String(127)) description = db.Column('Description', db.Text(65535)) _from = db.Column('emailFrom', db.String(127)) reply_to = db.Column('emailReplyTo', db.String(127)) is_email_open_tracking = db.Column('isEmailOpenTracking', db.Boolean, default=True) is_track_html_clicks = db.Column('isTrackHtmlClicks', db.Boolean, default=True) is_track_text_clicks = db.Column('isTrackTextClicks', db.Boolean, default=True) # body_html and body_text are deferred fields because they could be huge. Should really be stored in S3. body_html = db.deferred(db.Column('EmailBodyHtml', LONGTEXT), group='email_body') body_text = db.deferred(db.Column('EmailBodyText', LONGTEXT), group='email_body') is_personalized_to_field = db.Column('isPersonalizedToField', db.Boolean, default=False) frequency_id = db.Column('frequencyId', db.Integer, db.ForeignKey('frequency.id')) start_datetime = db.Column('SendTime', db.DateTime) end_datetime = db.Column('StopTime', db.DateTime) scheduler_task_id = db.Column('SchedulerTaskIds', db.String(255)) custom_html = db.Column('CustomHtml', LONGTEXT) custom_url_params_json = db.Column('CustomUrlParamsJson', db.String(512)) is_subscription = db.Column('isSubscription', db.Boolean, default=False) added_datetime = db.Column('addedTime', db.DateTime, default=datetime.utcnow) email_client_id = db.Column('EmailClientId', db.Integer, db.ForeignKey('email_client.id')) email_client_credentials_id = db.Column( 'EmailClientCredentialsId', db.Integer, db.ForeignKey('email_client_credentials.id')) # Relationships blasts = relationship('EmailCampaignBlast', lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True, backref='campaign') sends = relationship('EmailCampaignSend', cascade='all, delete-orphan', passive_deletes=True, lazy='dynamic', backref='campaign') smartlists = relationship('EmailCampaignSmartlist', cascade='all, delete-orphan', passive_deletes=True, backref='campaign') def to_dict(self, include_fields=None): """ This returns required fields when an email-campaign object is requested. :param list[str] | None include_fields: List of fields to include, or None for all. :rtype: dict[str, T] """ from smartlist import Smartlist smart_lists = Smartlist.query.join(EmailCampaignSmartlist).filter( EmailCampaignSmartlist.campaign_id == self.id).all() talent_pipelines = [{ "id": smart_list.talent_pipeline.id, "name": smart_list.talent_pipeline.name } for smart_list in smart_lists if smart_list.talent_pipeline] talent_pipelines = [ dict(tupleized) for tupleized in set( tuple(item.items()) for item in talent_pipelines) ] return_dict = { "id": self.id, "user_id": self.user_id, "name": self.name, "frequency": self.frequency.name if self.frequency else None, "subject": self.subject, "description": self.description, "from": self._from, "reply_to": self.reply_to, "start_datetime": DatetimeUtils.utc_isoformat(self.start_datetime) if self.start_datetime else None, "end_datetime": DatetimeUtils.utc_isoformat(self.end_datetime) if self.end_datetime else None, "added_datetime": DatetimeUtils.utc_isoformat(self.added_datetime) if self.added_datetime else None, # Conditionally include body_text and body_html because they are deferred fields "body_html": self.body_html if (include_fields and 'body_html' in include_fields) else None, "body_text": self.body_text if (include_fields and 'body_text' in include_fields) else None, "is_hidden": self.is_hidden, "talent_pipelines": talent_pipelines, "list_ids": [smart_list.id for smart_list in smart_lists], "scheduler_task_id": self.scheduler_task_id, "email_client_credentials_id": self.email_client_credentials_id, "base_campaign_id": self.base_campaign_id } # Only include the fields that are supposed to be included if include_fields: return { key: return_dict[key] for key in include_fields if key in return_dict } return return_dict def get_id(self): return unicode(self.id) @classmethod def get_by_domain_id(cls, domain_id, page_number=None): """ This methods returns list of email campaigns in a user's domain :param long|int domain_id: Domain Id :param None|long|int page_number: Page number for pagination purpose :rtype: list|flask_sqlalchemy.BaseQuery """ assert domain_id, 'domain_id not given' from user import User # This has to be here to avoid circular import common_query = cls.query.join(User).filter(User.domain_id == domain_id, cls.is_hidden == 0) if page_number is None: return common_query return get_paginated_objects(common_query, page_number) @classmethod def search_by_id_in_domain(cls, email_campaign_id, domain_id): """ This searches email-campaign by email_campaign_id in user's domain. It raises 404 error if campaign is not found in database. It raises 403 error if camapign does not belong to user's domian. :type email_campaign_id: int|long :type domain_id: int|long :rtype: EmailCampaign|None """ email_campaign = cls.get_by_id(email_campaign_id) if not email_campaign: raise ResourceNotFound( "Email campaign with id: %s does not exist" % email_campaign_id, error_code=EMAIL_CAMPAIGN_NOT_FOUND[1]) if not email_campaign.user.domain_id == domain_id: raise ForbiddenError(EMAIL_CAMPAIGN_FORBIDDEN[0], error_code=EMAIL_CAMPAIGN_FORBIDDEN[1]) return email_campaign @classmethod @contract def get_by_domain_id_and_filter_by_name(cls, domain_id, search_keyword, sort_by, sort_type, is_hidden, user_id=None): """ This filters records of database table EmailCampaign for given criteria. By default it returns all the campaigns in user's domain. If we provide user_id it will only return campaigns belonging to give user_id. :param positive domain_id: Id of domain :param string search_keyword: String with which we want to filter the records. e.g. filter campaigns containing word "Python" :param string sort_by: String by which we want to sort the records. e.g. Sort by Name OR Description etc :param string sort_type: String either "ASC" or "DESC" :param int is_hidden: Indicator to return archived or unarchived campaigns :param positive|None user_id: Id of user """ assert domain_id, 'domain_id not given' from user import User # This has to be here to avoid circular import if sort_by == 'name': sort_by_object = EmailCampaign.name else: sort_by_object = EmailCampaign.added_datetime if sort_type == 'ASC': sort_by_object = sort_by_object.asc() else: sort_by_object = sort_by_object.desc() is_hidden = True if is_hidden else False if user_id: assert str(user_id).isdigit() and int( user_id) > 0, 'positive user_id expected, given: {}'.format( user_id) sub_query = cls.query.filter_by(user_id=user_id) else: sub_query = cls.query.join(User).filter( User.domain_id == domain_id) return sub_query.filter( cls.name.ilike('%' + search_keyword + '%'), cls.is_hidden == is_hidden).order_by(sort_by_object) @classmethod @contract def get_by_user_id(cls, user_id, page_number=None): """ Returns EmailCampaigns against a User Id :param positive user_id: User Id :param positive|None page_number: Page number for returning limited number of records :rtype: list """ query_object = cls.query.filter(cls.user_id == user_id, cls.is_hidden == 0) if page_number is None: return query_object.all() return get_paginated_objects(query_object, page_number) @classmethod @contract def get_by_domain_id_and_name(cls, domain_id, name): """ Gets EmailCampaign against campaign name :param positive domain_id: User's domain Id :param string name: :rtype: list """ from user import User return cls.query.join(User).filter(cls.name == name, User.domain_id == domain_id, cls.is_hidden == 0).all() def __repr__(self): return "<EmailCampaign(name=' %r')>" % self.name @classmethod @contract def email_campaigns_in_user_group(cls, user_id): """ Gets EmailCampaigns in user's group against User Id :param positive user_id: User Id :rtype: list """ from user import User user = User.query.filter(User.id == user_id).first() if user: if user.user_group_id: return cls.query.join(User).filter( User.user_group_id == user.user_group_id, cls.is_hidden == 0).all() raise NotFoundError @classmethod @contract def email_campaigns_in_talent_pool(cls, user_id, scope, talentpool_names=None, page_number=None): """ Returns EmailCampaigns in talent pool :param positive|None page_number: Page number for returning limited number of records :param int scope: Number which determines weather user asking about all domain campaigns or only his campaigns :param positive user_id: User Id :param list|None talentpool_names: list of Talentpool names or None :rtype: list """ from smartlist import SmartlistCandidate # To avoid circular dependency this has to be here from user import User # To avoid circular dependency this has to be here smartlist_ids = SmartlistCandidate.get_smartlist_ids_in_talent_pools( user_id, talentpool_names) email_campaign_ids = EmailCampaignSmartlist.query.with_entities(EmailCampaignSmartlist.campaign_id).\ filter(EmailCampaignSmartlist.smartlist_id.in_(smartlist_ids)).all() email_campaign_ids = [ email_campaign_id[0] for email_campaign_id in email_campaign_ids ] scope_dependant_filter = cls.query.join(User).filter(cls.id.in_(email_campaign_ids), cls.is_hidden == 0, cls.user_id == user_id)\ if scope == OWNED else cls.query.filter(cls.id.in_(email_campaign_ids), cls.is_hidden == 0) if page_number is None: return scope_dependant_filter.all() return get_paginated_objects(scope_dependant_filter, page_number)
class ItemModel(db.Model): __tablename__ = "items" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.store_id = store_id self.name = name self.price = price def json(self): return { 'id': self.id, 'name': self.name, 'price': self.price, 'store_id': self.store_id } @classmethod def find_by_name(cls, name): return ItemModel.query.filter_by(name=name).first() # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # query = "SELECT * FROM items WHERE name=?" # result = cursor.execute(query, (name, )) # row = result.fetchone() # connection.close() # if row: # return cls(*row) def save_to_db(self): db.session.add(self) db.session.commit() # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # query = "INSERT INTO items VALUES (?,?)" # cursor.execute(query, (self.name, self.price)) # connection.commit() # connection.close() def delete_from_db(self): db.session.delete(self) db.session.commit() # connection = sqlite3.connect('data.db') # cursor = connection.cursor() # query = "UPDATE items SET price=? WHERE name=?" # cursor.execute(query, (self.price, self.name)) # connection.commit() # connection.close() # return {'message': 'Item deleted'} @classmethod def find_all(cls): return cls.query.all()
class EmailCampaignBlast(db.Model): __tablename__ = 'email_campaign_blast' id = db.Column(db.Integer, primary_key=True) campaign_id = db.Column( 'EmailCampaignId', db.Integer, db.ForeignKey('email_campaign.Id', ondelete='CASCADE')) sends = db.Column('Sends', db.Integer, default=0) html_clicks = db.Column('HtmlClicks', db.Integer, default=0) text_clicks = db.Column('TextClicks', db.Integer, default=0) opens = db.Column('Opens', db.Integer, default=0) bounces = db.Column('Bounces', db.Integer, default=0) complaints = db.Column('Complaints', db.Integer, default=0) unsubscribed_candidates = db.Column('UnsubscribedCandidates', db.Integer, default=0) sent_datetime = db.Column('SentTime', db.DateTime, default=datetime.utcnow) updated_datetime = db.Column('UpdatedTime', db.DateTime, default=datetime.utcnow) # Relationships blast_sends = relationship('EmailCampaignSend', cascade='all, delete-orphan', passive_deletes=True, backref='blast', lazy='dynamic') @classmethod def get_by_id(cls, _id): return cls.query.get(_id) @classmethod def get_latest_blast_by_campaign_id(cls, campaign_id): """ Method to get latest email campaign blast for campaign whose id is provided. Returns on the basis of most recent sent_datetime. :type campaign_id: int | long :rtype: EmailCampaignBlast """ assert campaign_id, "campaign_id not provided" return cls.query.filter(cls.campaign_id == campaign_id).order_by( desc(cls.sent_datetime)).first() @classmethod def get_by_send(cls, send_obj): """ This method takes EmailCampaignSend object as input and returns associated EmailCampaignBlast object. It first tries to retrieve blast from backref. If that is None, then get blast by matching sent_datetime :param EmailCampaignSend send_obj: campaign send object :rtype EmailCampaignBlast | None """ assert isinstance( send_obj, EmailCampaignSend ), 'send_obj must be EmailCampaignSend instance, found: %s, type: %s' % ( send_obj, type(send_obj)) blast = send_obj.blast if isinstance(blast, EmailCampaignBlast): return blast # if blast_id is not there, match by sent_datetime as we are doing in web2py app: # https://github.com/Veechi/Talent-Web/blob/master/web2py/applications/web/controllers/campaign.py#L63 return cls.query.filter_by( sent_datetime=send_obj.sent_datetime).first() def __repr__(self): return "<EmailCampaignBlast (Sends: %s, Opens: %s)>" % (self.sends, self.opens) @classmethod @contract def top_performing_email_campaign(cls, datetime_value, user_id): """ This method returns top performing email campaign from a specific datetime :param positive user_id: User Id :param string|datetime|None datetime_value: date during campaign started or updated :rtype: type(z) """ from .user import User # To avoid circular dependency this has to be here domain_id = User.get_domain_id(user_id) if isinstance(datetime_value, datetime): return cls.query.filter(or_(cls.updated_datetime >= datetime_value, cls.sent_datetime >= datetime_value)). \ filter(EmailCampaign.id == cls.campaign_id, EmailCampaign.is_hidden == 0). \ filter(cls.sends > 0).filter(and_(EmailCampaign.user_id == User.id, User.domain_id == domain_id)). \ order_by(desc(cls.opens/cls.sends)).first() if isinstance(datetime_value, basestring): return cls.query.filter(or_(extract("year", cls.updated_datetime) == datetime_value, extract("year", cls.sent_datetime) == datetime_value)). \ filter(EmailCampaign.id == cls.campaign_id, EmailCampaign.is_hidden == 0). \ filter(and_(EmailCampaign.user_id == User.id, User.domain_id == domain_id)). \ filter(cls.sends > 0). \ order_by(desc(cls.opens/cls.sends)).first() return cls.query.filter(EmailCampaign.id == cls.campaign_id, EmailCampaign.is_hidden == 0).\ filter(and_(EmailCampaign.user_id == User.id, User.domain_id == domain_id)).\ filter(cls.sends > 0).order_by(desc(cls.opens/cls.sends)).first()
class Education(db.Model): __tablename__ = 'EDUCATION' id = db.Column(db.Integer, db.Sequence('education_id_seq'), primary_key=True) school_name = db.Column(db.String, nullable=False) program = db.Column(db.String) location = db.Column(db.String) start_date = db.Column(db.Date) end_date = db.Column(db.Date) current = db.Column(db.Boolean) duration = db.Column(db.String) user_id = db.Column(db.String, db.ForeignKey('LINKEDINUSER.id')) user = db.relationship('LINKEDINUSER') def __init__(self, school_name, user_id, program, location, start_date, end_date, current, duration): self.school_name = school_name self.user_id = user_id self.program = program self.location = location self.start_date = start_date self.end_date = end_date self.current = current self.duration = duration def json(self): return { 'school_name': self.school_name, 'user_id': self.user_id, 'program': self.program, 'location': self.location, 'start_date': self.start_date, 'end_date': self.end_date, 'current': self.current 'duration': self.duration} @classmethod def find_by_school_name(cls, school_name): return cls.query.filter_by(school_name=school_name) @classmethod def find_by_program(cls, program): return cls.query.filter_by(program=program) @classmethod def find_by_location(cls, location): return cls.query.filter_by(location=location) @classmethod def find_by_user_id(cls, user_id): return cls.query.filter_by(user_id=user_id) @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class EmailCampaignSend(db.Model): __tablename__ = 'email_campaign_send' id = db.Column('Id', db.Integer, primary_key=True) campaign_id = db.Column( 'EmailCampaignId', db.Integer, db.ForeignKey('email_campaign.Id', ondelete='CASCADE')) blast_id = db.Column( 'EmailCampaignBlastId', db.Integer, db.ForeignKey('email_campaign_blast.id', ondelete='CASCADE')) candidate_id = db.Column('CandidateId', db.BIGINT, db.ForeignKey('candidate.Id', ondelete='CASCADE')) sent_datetime = db.Column('SentTime', db.DateTime, default=datetime.utcnow) ses_message_id = db.Column('sesMessageId', db.String(63)) ses_request_id = db.Column('sesRequestId', db.String(63)) is_ses_bounce = db.Column('isSesBounce', db.Boolean, default=False) is_ses_complaint = db.Column('isSesComplaint', db.Boolean, default=False) updated_datetime = db.Column('UpdatedTime', db.DateTime, default=datetime.utcnow) # Relationships email_campaign = relationship('EmailCampaign', backref="email_campaign") url_conversions = relationship('EmailCampaignSendUrlConversion', cascade='all,delete-orphan', passive_deletes=True, backref='send') associated_blast = relationship('EmailCampaignBlast', backref='blast') @classmethod def get_valid_send_object(cls, send_id, requested_campaign_id): """ This returns the send object for given id. If record is not found, it raises ResourceNotFound error. If send object is not associated with given campaign_id, it raises ForbiddenError :param send_id: id of email_campaign_send object :param requested_campaign_id: id of email-campaign object :type send_id: int | long :type requested_campaign_id: int | long :return: email_campaign_send object :rtype: EmailCampaignSend """ assert send_id, 'id of email-campaign-send obj not given' assert requested_campaign_id, 'id of email-campaign obj not given' send_obj = EmailCampaignSend.get_by_id(send_id) if not send_obj: raise ResourceNotFound( "Send object(id:%s) for email-campaign(id:%s) does not " "exist in database." % (send_id, requested_campaign_id), error_code=EMAIL_CAMPAIGN_SEND_NOT_FOUND[1]) if not send_obj.campaign_id == requested_campaign_id: raise ForbiddenError( "Send object(id:%s) is not associated with email-campaign(id:%s)." % (send_id, requested_campaign_id), error_code=EMAIL_CAMPAIGN_SEND_FORBIDDEN[1]) return send_obj @classmethod def get_by_amazon_ses_message_id(cls, message_id): """ Get send email object from given SES message id. :param message_id: Simple Email Service (SES) unique message id :type message_id: str :return: EmailCampaignSend object """ assert isinstance( message_id, basestring) and message_id, 'message_id should have a valid value.' return cls.query.filter_by(ses_message_id=message_id).first() @classmethod def get_already_emailed_candidates(cls, campaign): """ Get candidates to whom email for specified campaign has already been sent. :param campaign: Valid campaign object. :return: Ids of candidates to whom email for specified campaign has already being sent. """ if not isinstance(campaign, EmailCampaign): raise InternalServerError( error_message='Must provide valid EmailCampaign object.') already_emailed_candidates = cls.query.with_entities( cls.candidate_id).filter_by(campaign_id=campaign.id).all() emailed_candidate_ids = [ row.candidate_id for row in already_emailed_candidates ] return emailed_candidate_ids @classmethod def get_candidate_open_email_campaign_send(cls, candidate_id): """ Get opened email campaign sends of a candidate :param int candidate_id: Candidate id :return: Candidate's open email campaign sends """ # importing UrlConversion here due to import errors from ..models.misc import UrlConversion if not isinstance(candidate_id, int): raise InternalServerError( error_message='Candidate id must be an integer, given {}'. format(candidate_id)) return EmailCampaignSend.query.join(EmailCampaignSendUrlConversion).join(UrlConversion).\ filter(EmailCampaignSend.candidate_id == candidate_id). \ filter((EmailCampaignSendUrlConversion.type == TRACKING_URL_TYPE) | (EmailCampaignSendUrlConversion.type == TEXT_CLICK_URL_TYPE)).filter(UrlConversion.hit_count > 0)\ .all()
class UserModel(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True) password = db.Column(db.String(80)) invest_amt = db.Column(db.Integer) lend_amt = db.Column(db.Integer) borrow_amt = db.Column(db.Integer) interest_amt_L = db.Column(db.Integer) interest_amt_B = db.Column(db.Integer) Trx_id = db.Column(db.Integer, db.ForeignKey('mapping.Trx_id')) weight_id = db.Column(db.Integer) mappingModel = db.relationship('MappingModel') investRequestModel = db.relationship('InvestRequestModel') withdrawRequestModel = db.relationship('WithdrawRequestModel') BorrowRequestModel = db.relationship('BorrowRequestModel') def __init__(self, username, password, invest_amt=0, lend_amt=0, borrow_amt=0, weight_id=0, interest_amt_L=0, interest_amt_B=0): self.username = username self.password = password self.invest_amt = invest_amt self.lend_amt = lend_amt self.borrow_amt = borrow_amt self.weight_id = weight_id self.interest_amt_L = interest_amt_L self.interest_amt_B = interest_amt_B def save_to_db(self): db.session.add(self) db.session.commit() def json(self): return { 'id': self.id, 'username': self.username, 'invest_amt': self.invest_amt, 'lend_amt': self.lend_amt, 'borrow_amt': self.borrow_amt, 'interest_amt_L': self.interest_amt_L, 'interest_amt_B': self.interest_amt_B, 'transaction': self.Trx_id, 'weight_id': self.weight_id } @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_username(cls, username): return cls.query.filter_by(username=username).first() @classmethod def find_investor(cls, borrow_request, username): #more work needed req_investor = None #add code to take (snippet) from multiple users all_investor = cls.query.order_by(cls.weight_id).all() for each_investor in all_investor: if each_investor.username != username and each_investor.invest_amt > borrow_request: req_investor = each_investor break return req_investor @classmethod def find_all(cls): return cls.query.all()
class UserEmailTemplate(db.Model): __tablename__ = 'user_email_template' id = db.Column('Id', db.Integer, primary_key=True) user_id = db.Column('UserId', db.BIGINT, db.ForeignKey('user.Id'), index=True) type = db.Column('Type', db.Integer, default=0) name = db.Column('Name', db.String(255), nullable=False) body_html = db.Column('EmailBodyHtml', db.Text) body_text = db.Column('EmailBodyText', db.Text) template_folder_id = db.Column('EmailTemplateFolderId', db.Integer, db.ForeignKey('email_template_folder.id', ondelete=u'SET NULL'), index=True) is_immutable = db.Column('IsImmutable', db.Integer, nullable=False, default=0) updated_datetime = db.Column('UpdatedTime', db.DateTime, nullable=False, default=datetime.utcnow) # Relationships template_folder = relationship(u'EmailTemplateFolder', backref=db.backref( 'user_email_template', cascade="all, delete-orphan")) @classmethod def get_by_name_and_domain_id(cls, template_name, domain_id): """ This filters email-templates for given name and returns first object :param string template_name: Name of email-template :param int|long domain_id: Id of domain :rtype: UserEmailTemplate """ assert str(domain_id).isdigit() and int(domain_id) > 0, \ 'positive domain_id expected, given: {}'.format(domain_id) assert isinstance( template_name, basestring) and template_name, 'template_name not given' from user import User # This has to be here to avoid circular import return cls.query.join(User).filter(User.domain_id == domain_id, cls.name == template_name).first() @classmethod def query_by_domain_id(cls, domain_id): """ This returns query object to get email-templates in given domain_id. :param int|long domain_id: Id of domain of user :rtype: sqlalchemy.orm.query.Query """ assert domain_id, 'domain_id not given' from user import User # This has to be here to avoid circular import return cls.query.join(User).filter(User.domain_id == domain_id) @classmethod def get_valid_email_template(cls, email_template_id, user): """ This validates given email_template_id is int or long greater than 0. It raises Invalid Usage error in case of invalid email_template_id. It raises ResourceNotFound error if requested template is not found in database. It raises Forbidden error if requested template template does not belong to user's domain. It returns EmailTemplate object if above validation does not raise any error. :param int|long email_template_id: Id of email-template :param User user: User object of logged-in user :rtype: UserEmailTemplate """ # Had to be here to avoid circular import issue from ..campaign_services.validators import raise_if_dict_values_are_not_int_or_long raise_if_dict_values_are_not_int_or_long( dict(email_template_id=email_template_id)) # Get email-template object from database email_template = cls.get_by_id(email_template_id) if not email_template: raise ResourceNotFound( 'Email template(id:%d) not found' % email_template_id, EMAIL_TEMPLATE_NOT_FOUND[1]) # Verify owned by same domain template_owner_user = email_template.user if template_owner_user.domain_id != user.domain_id: raise ForbiddenError( 'Email template(id:%d) is not owned by domain(id:%d)' % (email_template_id, user.domain_id), error_code=EMAIL_TEMPLATE_FORBIDDEN[1]) return email_template
class UsuarioModel(db.Model): __tablename__ = "usuario" usuario_id = db.Column(db.Integer, primary_key=True) nome = db.Column(db.String(100)) telefone = db.Column(db.String(12)) email = db.Column(db.String(100)) password = db.Column(db.String(100)) perfil_id = db.Column(db.Integer, db.ForeignKey(PerfilModel.perfil_id)) valor_hora = db.Column(db.Float(precision=2)) timezone = db.Column(db.String(5)) ultimo_login = db.Column(db.Date()) confirmado = db.Column(db.Integer) criado_por = db.Column(db.String(100)) data_criacao = db.Column(db.Date()) atualizado_por = db.Column(db.String(100)) data_ultima_atualizacao = db.Column(db.Date()) ativo = db.Column(db.Integer) def __init__(self, usuario_id, nome, telefone, email, password, perfil_id, valor_hora, timezone, ultimo_login, confirmado, criado_por, data_criacao, atualizado_por, data_ultima_atualizacao, ativo): self.usuario_id = usuario_id self.nome = nome self.telefone = telefone self.email = email self.password = password self.perfil_id = perfil_id self.valor_hora = valor_hora self.timezone = timezone self.ultimo_login = ultimo_login self.confirmado = confirmado self.criado_por = criado_por self.data_criacao = data_criacao self.atualizado_por = atualizado_por self.data_ultima_atualizacao = data_ultima_atualizacao self.ativo = ativo def json(self): return { "usuario_id": self.usuario_id, "nome": self.nome, "telefone": self.telefone, "email": self.email, "password": self.password, "perfil_id": self.perfil_id, "valor_hora": self.valor_hora, "timezone": self.timezone, "ultimo_login": self.ultimo_login.strftime('%Y-%m-%d %H:%M:%S'), "criado_por": self.criado_por, "data_criacao": self.data_criacao.strftime('%Y-%m-%d %H:%M:%S'), "atualizado_por": self.atualizado_por, "data_ultima_atualizacao": self.data_ultima_atualizacao.strftime('%Y-%m-%d %H:%M:%S'), } @classmethod def find_by_email(cls, email): return UsuarioModel.query.filter_by(email=email, ativo=1).first() @classmethod def find_by_id(cls, usuario_id): return UsuarioModel.query.filter_by(usuario_id=usuario_id, ativo=1).first() @classmethod def update_last_login(cls, usuario_id): user = UsuarioModel.find_by_id(usuario_id) if user: user.ultimo_login = datetime.datetime.utcnow().strftime( '%Y-%m-%d %H:%M:%S') user.save_to_db() def save_to_db(self): db.session.add(self) db.session.commit()
class EmailTemplateFolder(db.Model): __tablename__ = 'email_template_folder' id = db.Column(db.Integer, primary_key=True) name = db.Column('Name', db.String(512)) parent_id = db.Column('ParentId', db.Integer, db.ForeignKey('email_template_folder.id', ondelete='CASCADE'), index=True) is_immutable = db.Column('IsImmutable', db.Integer, nullable=False, default=0) domain_id = db.Column('DomainId', db.Integer, db.ForeignKey('domain.Id', ondelete='CASCADE'), index=True) updated_datetime = db.Column('UpdatedTime', db.DateTime, nullable=False, default=datetime.utcnow) # Relationships parent = relationship('EmailTemplateFolder', remote_side=[id], backref=db.backref('email_template_folder', cascade="all, delete-orphan")) @classmethod def get_by_name_and_domain_id(cls, folder_name, domain_id): """ Method to get email template folder based on folder name and domain id. :type folder_name: string :type domain_id: int | long :rtype: EmailTemplateFolder """ assert folder_name, "folder_name not provided" assert domain_id, "domain_id not provided" return cls.query.filter_by(name=folder_name, domain_id=domain_id).first() @classmethod def get_valid_template_folder(cls, template_folder_id, domain_id): """ This validates given template_folder_id is int or long greater than 0. It raises Invalid Usage error in case of invalid template_folder_id. It raises ResourceNotFound error if requested folder is not found in database. It raises Forbidden error if requested template folder does not belong to user's domain. It returns EmailTemplateFolder object if above validation does not raise any error. :param int|long template_folder_id: Id of email-template-folder :param int|long domain_id: Id of domain of logged-in user :rtype: EmailTemplateFolder """ # Inline import to avoid circular dependency from ..campaign_services.validators import raise_if_dict_values_are_not_int_or_long raise_if_dict_values_are_not_int_or_long( dict(template_folder_id=template_folder_id)) assert domain_id and domain_id > 0, "Expecting positive value for folder_id. Found:{}".format( domain_id) # Get template-folder object from database template_folder = cls.get_by_id(template_folder_id) if not template_folder: raise ResourceNotFound('Email template folder(id:%s) not found' % template_folder_id, error_code=TEMPLATE_FOLDER_NOT_FOUND[1]) # Verify owned by same domain if not template_folder.domain_id == domain_id: raise ForbiddenError( "Email template folder does not belong to user's domain", error_code=TEMPLATE_FOLDER_FORBIDDEN[1]) return template_folder
class AddressModel(db.Model, BaseMethods): __tablename__ = 'addresses' id = db.Column(db.Integer, primary_key=True) # Relationships districtId = db.Column(db.Integer, db.ForeignKey('districts.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False) district = db.relationship('DistrictModel') doctors = db.relationship('DoctorModel', lazy='dynamic') street = db.Column(db.String(100)) neighborhood = db.Column(db.String(100)) complement = db.Column(db.String(100)) number = db.Column(db.String(5)) createdAt = db.Column(db.DateTime, nullable=False) updatedOn = db.Column(db.DateTime) status = db.Column(db.String(3), server_default='ACT') def __init__(self, district_id, street, neighborhood, complement, number, created_at, updated_on, status): super(AddressModel, self).__init__() self.districtId = district_id self.street = street self.neighborhood = neighborhood self.complement = complement self.number = number self.createdAt = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if ( created_at is None) else created_at self.updatedOn = datetime.now().strftime('%Y-%m-%d %H:%M:%S') if ( updated_on is None) else updated_on self.status = status def __repr__(self): return 'Address: %r' % self.street def json(self, doctors_list=bool): if doctors_list: return { 'doctors': list(map(lambda x: x.json(), self.doctors)), 'district': self.district.json(), 'street': self.street, 'neighborhood': self.neighborhood, 'complement': self.complement, 'number': self.number } else: return { 'district': self.district.json(), 'street': self.street, 'neighborhood': self.neighborhood, 'complement': self.complement, 'number': self.number } @classmethod def find_by_number(cls, number): return cls.query.filter_by(number=number).first()
class EmailClientCredentials(db.Model): __tablename__ = 'email_client_credentials' id = db.Column(db.Integer, primary_key=True) user_id = db.Column('user_id', db.BIGINT, db.ForeignKey('user.Id', ondelete='CASCADE')) host = db.Column('host', db.String(50), nullable=False) port = db.Column('port', db.String(5)) name = db.Column('name', db.String(20), nullable=False) email = db.Column('email', db.String(60), nullable=False) password = db.Column('password', db.String(512), nullable=False) updated_datetime = db.Column('updated_datetime', db.DateTime, nullable=False, default=datetime.utcnow) # Relationship email_campaign = relationship('EmailCampaign', cascade='all, delete-orphan', passive_deletes=True, backref='email_client_credentials') email_conversations = relationship('EmailConversations', lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True, backref='email_client_credentials') CLIENT_TYPES = {'incoming': 'incoming', 'outgoing': 'outgoing'} OUTGOING = ('smtp', ) INCOMING = ('imap', 'pop') def __repr__(self): return "<EmailClientCredentials (id:%s)>" % self.id @classmethod def get_by_user_id_host_and_email(cls, user_id, host, email): """ Method to get email_client_credentials objects for given params. :type user_id: int | long :type host: string :type email: string :rtype: list """ assert user_id, "user_id not provided" assert host, "host not provided" assert email, "email not provided" return cls.filter_by_keywords(user_id=user_id, host=host, email=email) @classmethod def get_by_user_id_and_filter_by_name(cls, user_id, search_keyword): """ This gets email-client-credentials for given user_id and search_keyword. Valid values of "search_keyword" are 'incoming' or 'outgoing'. :type user_id: int | long :type search_keyword: string :rtype: list """ assert isinstance(user_id, (int, long)) and user_id, 'user_id not given' assert isinstance( search_keyword, basestring) and search_keyword, 'search_keyword not given' search_keyword = search_keyword.strip() if search_keyword not in cls.CLIENT_TYPES: raise InvalidUsage('Invalid value of param `type` provided') client_types = getattr(cls, search_keyword.upper()) conditions = [] for client_type in client_types: conditions.append(cls.host.ilike('%{}%'.format(client_type))) return cls.query.filter(or_(*conditions), cls.user_id == user_id).all() @classmethod def get_by_client_type(cls, client_type): """ This gets email-client-credentials for given client type. Valid values of parameter are 'incoming' or 'outgoing'. :type client_type: string :rtype: list """ assert isinstance(client_type, basestring) and client_type, 'client_type not given' client_type = client_type.strip() if client_type not in cls.CLIENT_TYPES: raise InvalidUsage('Invalid value of param `client_type` provided') conditions = [] for client_type in cls.INCOMING: conditions.append(cls.host.ilike('%{}%'.format(client_type))) return cls.query.filter(or_(*conditions)).all()