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()
