def total_games_leader_board(won=True, points=None, global_stats=False):
query = db.session.query(
Player.id,
Player.name,
func.sum(func.coalesce(case([
(or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id),
case([((Game.team1_score >= 150) if won else (Game.team1_score < 150), 1)])),
(or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id),
case([((Game.team2_score >= 150) if won else (Game.team2_score < 150), 1)]))
]), 0)).label('count'),
). \
select_from(Game). \
join(Player,
or_(Game.team1_player1_id == Player.id,
Game.team1_player2_id == Player.id,
Game.team2_player1_id == Player.id,
Game.team2_player2_id == Player.id),
isouter=True)
if global_stats:
query = query.filter(Game.finished_at.isnot(None),
(Game.points == points) if points else True)
else:
today = datetime.now() - timedelta(hours=8)
beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC'))
query = query.filter(Game.finished_at.isnot(None),
Game.started_at.__gt__(beginning_of_year),
(Game.points == points) if points else True)
return query.group_by(Player.id). \
having(db.text('count > 0')). \
order_by(db.text('count DESC'))
class Test(db.Model):
# define table name
__tablename__ = 't_lessons'
id = db.Column(db.Integer, primary_key=True)
lesson_name = db.Column(db.String(32), unique=True, nullable=False)
lesson_info = db.Column(db.String(32), nullable=False)
status = db.Column(db.SmallInteger,
nullable=False,
default=1,
doc="用户状态，0-禁用，1-启动")
create_datetime = db.Column(db.DateTime,
server_default=db.text("CURRENT_TIMESTAMP"),
doc="创建时间")
update_datetime = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
"CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
doc="更新时间")
def __init__(self, **kwargs):
super(Test, self).__init__(**kwargs)
def __repr__(self):
return "User<name:%r>" % self.lesson_name
class CraftEssence(db.Model):
"""概念礼装"""
__tablename__ = 'craft_essences'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
star = db.Column(db.SmallInteger, nullable=False)
icon = db.Column(db.String(255), nullable=False)
image = db.Column(db.String(255), nullable=False)
atk = db.Column(db.Integer, nullable=False)
max_atk = db.Column(db.Integer, nullable=False)
hp = db.Column(db.Integer, nullable=False)
max_hp = db.Column(db.Integer, nullable=False)
effect = db.Column(db.Text, nullable=False)
max_effect = db.Column(db.Text, nullable=False)
effect_icon = db.Column(db.String(255), nullable=False)
description = db.Column(db.Text, nullable=False)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__,
'(%s)' % self.__doc__ if self.__doc__
is not None else str(), self.id, self.name)
class Chat(db.Model):
__tablename__ = 'chat'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
name = db.Column(db.String(100), nullable=True)
datetime = db.Column(db.DateTime, nullable=False)
content = db.Column(db.Text, nullable=False)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
user = db.relationship('User', backref='chat')
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' %
self.__doc__ if self.__doc__ is not None else
str(), self.id, self.content)
class SkillLv(db.Model):
"""技能升级材料"""
__tablename__ = 'skill_lv'
id = db.Column(db.Integer, primary_key=True)
servant_id = db.Column(db.Integer,
db.ForeignKey('servants.id'),
nullable=False)
level = db.Column(db.SmallInteger, nullable=False)
item = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False)
quantity = db.Column(db.Integer, nullable=False)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
servant = db.relationship('Servant', backref='skill_lv')
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__,
'(%s)' % self.__doc__ if self.__doc__
is not None else str(), self.id, '%s Lv %d' %
(self.servant.name, self.level))
class Alias(db.Model):
"""英灵别名"""
__tablename__ = 'servant_alias'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False, unique=True)
servant_id = db.Column(db.Integer,
db.ForeignKey('servants.id'),
nullable=False)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
servant = db.relationship('Servant', backref='alias')
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__,
'(%s)' % self.__doc__ if self.__doc__
is not None else str(), self.id, '%s -> %s' %
(self.name, self.servant.name))
class NpEffect(db.Model):
"""宝具效果"""
__tablename__ = 'np_effects'
id = db.Column(db.Integer, primary_key=True)
servant_id = db.Column(db.Integer,
db.ForeignKey('servants.id'),
nullable=False)
effect = db.Column(db.Text, nullable=False)
lv1 = db.Column(db.String(100), nullable=True)
lv2 = db.Column(db.String(100), nullable=True)
lv3 = db.Column(db.String(100), nullable=True)
lv4 = db.Column(db.String(100), nullable=True)
lv5 = db.Column(db.String(100), nullable=True)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
servant = db.relationship('Servant', backref='np_effects')
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__,
'(%s)' % self.__doc__ if self.__doc__
is not None else str(), self.id, self.effect)
class Booking(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
book_id = db.Column(db.String(64), index=True)
start_date = db.Column(db.Date)
end_date = db.Column(db.Date)
created_at = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text("CURRENT_TIMESTAMP"))
updated_at = db.Column(
db.TIMESTAMP,
nullable=False,
server_default=db.text(
"CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
def __repr__(self):
return '<Booking {}>'.format(self.book_id)
@property
def serialize(self):
return {
'user_id': self.user_id,
'book_id': self.book_id,
'start_date': dump_date(self.start_date),
'end_date': dump_date(self.end_date)
}
class XSKClass(db.Model):
# define table name
__tablename__ = 'xsk_class'
id = db.Column(db.Integer, primary_key=True)
class_name = db.Column(db.String(32),
unique=False,
nullable=False,
index=True)
class_info = db.Column(db.String(100), nullable=True)
class_period = db.Column(db.String(32), nullable=True)
class_start = db.Column(db.String(32), nullable=True)
status = db.Column(db.SmallInteger,
nullable=False,
default=1,
doc="课程状态，0-禁用，1-启动")
create_datetime = db.Column(db.DateTime,
server_default=db.text("CURRENT_TIMESTAMP"),
doc="创建时间")
update_datetime = db.Column(
db.DateTime,
nullable=False,
server_default=db.text(
"CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
doc="更新时间")
def __init__(self, **kwargs):
super(XSKClass, self).__init__(**kwargs)
def __repr__(self):
return "Class<name:%r>" % self.class_name
def versus_leader_board(global_stats=False):
players = aliased(Player)
other_players = aliased(Player)
query = db.session.query(
players.id,
players.name,
other_players.id,
other_players.name,
func.sum(func.coalesce(case([
(and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id),
not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))),
case([(Game.team1_score >= 150, Game.points)])),
(and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id),
not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))),
case([(Game.team2_score >= 150, Game.points)]))
]), 0)).label('wins_score'),
func.sum(func.coalesce(case([
(and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id),
not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))),
case([(Game.team1_score < 150, Game.points)])),
(and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id),
not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))),
case([(Game.team2_score < 150, Game.points)]))
]), 0)).label('loses_score'),
func.sum(func.coalesce(case([
(and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id),
not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))),
case([(Game.team1_score < 150, -1)], else_=1) * Game.points),
(and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id),
not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))),
case([(Game.team2_score < 150, -1)], else_=1) * Game.points)
]), 0)).label('balance'),
). \
select_from(Game). \
join(players,
or_(Game.team1_player1_id == players.id,
Game.team1_player2_id == players.id,
Game.team2_player1_id == players.id,
Game.team2_player2_id == players.id)). \
join(other_players,
or_(Game.team1_player1_id == other_players.id,
Game.team1_player2_id == other_players.id,
Game.team2_player1_id == other_players.id,
Game.team2_player2_id == other_players.id))
if global_stats:
query = query.filter(Game.finished_at.isnot(None))
else:
today = datetime.now() - timedelta(hours=8)
beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC'))
query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_year))
return query.group_by(players.id, other_players.id). \
having(or_(db.text('wins_score > 0'), db.text('loses_score > 0'))). \
order_by(db.text(request.args.get('versus_leader_board_order_by', 'balance DESC, wins_score DESC')))
def search_transcripts(search_term, current_page=1):
"""Searches comic transcripts and returns the dates for matching comics"""
if app.config['SQLALCHEMY_DATABASE_URI'].startswith('sqlite'):
results = SearchTranscripts.query.filter(db.text('transcript MATCH :search_term')).params(
search_term=search_term).paginate(page=current_page, per_page=10, error_out=False)
elif app.config['SQLALCHEMY_DATABASE_URI'].startswith('mysql'):
results = Comic.query.filter(db.text('MATCH(transcript) AGAINST(:search_term)')).params(
search_term=search_term).paginate(page=current_page, per_page=10, error_out=False)
else:
raise NotImplementedException
return [datetime.utcfromtimestamp(result.date).date() for result in results.items]
class TaskJobModel(db.Model):
__tablename__ = 'TaskJob'
ID = db.Column(db.Text, primary_key=True)
creation = db.Column(db.Text,
nullable=False,
default=datetime.datetime.utcnow)
ended = db.Column(db.Text, server_default=db.text("''"))
user = db.Column(db.Text, nullable=False, default="")
ejecutable = db.Column(db.Text, nullable=False, default="")
estado = db.Column(db.Integer,
nullable=False,
server_default=db.text("'En ejecución'"))
output = db.Column(db.Text, server_default=db.text("'En ejecución'"))
name = db.Column(db.Text, nullable=False)
def __init__(self, name):
self.ID = self.GetUniqueHash()
self.name = name
def __repr__(self):
return self.ToJson()
def GetUniqueHash(self):
hash = hashlib.sha1()
hash.update(str(time.time()).encode('utf-8'))
return hash.hexdigest()[:10]
def ToDict(self):
#print("taskdict-> ", self.__dict__)
return self.__dict__
def ToJson(self):
result = OrderedDict()
for key in self.__mapper__.c.keys():
result[key] = getattr(self, key)
return json.dumps(result)
@staticmethod
def getByIDJson(id):
task = TaskJobModel.query.get(id)
return task.ToJson()
@staticmethod
def GetAllTasksJson():
tasks = TaskJobModel.query.all()
data2 = []
for task in tasks:
d = {}
for column in task.__table__.columns:
d[column.name] = str(getattr(task, column.name))
data2.append(d)
return json.dumps(data2)
def leader_board(today=False, active=True, min_games_count=0, global_stats=False):
query = db.session.query(
Player.id,
Player.name,
(func.sum(func.coalesce(case([
(or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id),
case([(Game.team1_score >= 150, Game.points)])),
(or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id),
case([(Game.team2_score >= 150, Game.points)]))
]), 0)) + (0 if today else Player.manual_wins)).label('wins_score'),
(func.sum(func.coalesce(case([
(or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id),
case([(Game.team1_score < 150, Game.points)])),
(or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id),
case([(Game.team2_score < 150, Game.points)]))
]), 0)) + (0 if today else Player.manual_loses)).label('loses_score'),
(func.sum(func.coalesce(case([
(or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id),
case([(Game.team1_score < 150, -1)], else_=1) * Game.points),
(or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id),
case([(Game.team2_score < 150, -1)], else_=1) * Game.points)
]), 0)) + (0 if today else (Player.manual_wins - Player.manual_loses))).label('balance'),
func.sum(func.coalesce(case([
(or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id,
Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), 1)], else_=0),
0)).label('games_count')
). \
select_from(Player). \
join(Game,
or_(Game.team1_player1_id == Player.id,
Game.team1_player2_id == Player.id,
Game.team2_player1_id == Player.id,
Game.team2_player2_id == Player.id),
isouter=True)
if today:
today = datetime.now() - timedelta(hours=8)
beginning_of_today = datetime(today.year, today.month, today.day, 8, 0).astimezone(tz.gettz('UTC'))
query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_today))
else:
if global_stats:
query = query.filter(Game.finished_at.isnot(None)). \
having(db.text(f'games_count {">=" if active else "<"} {str(min_games_count)}'))
else:
today = datetime.now() - timedelta(hours=8)
beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC'))
query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_year)). \
having(db.text(f'games_count {">=" if active else "<"} {str(min_games_count)}'))
return query. \
group_by(Player.id). \
order_by(db.text(request.args.get('leader_board_order_by', 'balance DESC, wins_score DESC')))
class Deliver(db.Model):
__tablename__ = 'delivers'
id = db.Column(db.Integer, primary_key=True)
did = db.Column(db.String(20), nullable=False, unique=True)
oid = db.Column(db.String(20), nullable=False)
cid = db.Column(db.String(20), nullable=False) # 车id
mid = db.Column(db.String(20), nullable=False) # 驾驶员id
addr = db.Column(db.Text, nullable=False) # 目的地
status = db.Column(db.Text, nullable=False) # 目前位置
created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False)
updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'),
server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
class Car(db.Model):
__tablename__ = 'cars'
id = db.Column(db.Integer, primary_key=True)
cid = db.Column(db.String(20), unique=True, nullable=False)
mid = db.Column(db.String(20), nullable=False)
status = db.Column(db.Text, nullable=False) # 目前位置
active = db.Column(db.Integer) # 是否活跃
cplace = db.Column(db.String(4), nullable=False)
cnumber = db.Column(db.Integer, nullable=False)
created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False)
updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'),
server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
class Message(db.Model):
__tablename__ = 'message'
__bind_key__ = 'stock'
stock_name = db.Column(db.String(40), primary_key=True)
stock_id = db.Column(db.CHAR(10))
stock_price = db.Column(db.DECIMAL(7, 2))
up_confine = db.Column(db.DECIMAL(4, 2), server_default=db.text("'0.10'"))
down_confine = db.Column(db.DECIMAL(4, 2),
server_default=db.text("'0.10'"))
# continue_trans = db.Column ( db.TINYINT(1), server_default=db.text ( "'1'" ) )
def __init__(self, stock_name, stock_id=None, stock_price=1):
self.stock_name = stock_name
self.stock_price = stock_price
class Class(db.Model):
"""职介"""
__tablename__ = 'class'
Saber = 1
Lancer = 2
Archer = 3
Rider = 4
Caster = 5
Assassin = 6
Berserker = 7
Avenger = 8
Ruler = 9
Shielder = 10
Beast = 11
Alterego = 12
MoonCancer = 13
Foreigner = 14
id = db.Column(db.SmallInteger, primary_key=True)
name = db.Column(db.String(255), nullable=False, unique=True)
sort = db.Column(db.Integer, nullable=False, unique=True)
created = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
def __init__(self, name, sort=None):
self.name = name
self.sort = self.last_sort + 1
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__,
'(%s)' % self.__doc__ if self.__doc__
is not None else str(), self.id, self.name)
@property
def last_sort(self):
entity = self.__class__.query.order_by(desc('sort')).first()
if entity:
return entity.sort
else:
return 0
def index():
if request.method == "POST":
searchQuery = request.form.get("searchQuery")
print(searchQuery)
# Avoid SQL Injection Using Bindings
sql = "SELECT isbn, author, title \
FROM book \
WHERE isbn LIKE :x \
OR author LIKE :y \
OR title LIKE :z"
# I spent an hour wondering why I couldnt put the bindings inside the wildcard string...
# https://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like
matchString = "%{}%".format(searchQuery)
stmt = db.text(sql).bindparams(x=matchString, y=matchString, z=matchString)
results = db.session.execute(stmt).fetchall()
print(results)
session["books"] = []
for row in results:
# A row is not JSON serializable so we pull out the pieces
book = dict()
book["isbn"] = row[0]
book["author"] = row[1]
book["title"] = row[2]
session["books"].append(book)
return render_template("index.html", searchedFor=searchQuery, books=session["books"])
return render_template("index.html")
def pg_trgm():
try:
query = db.text("CREATE EXTENSION pg_trgm;")
db.session.execute(query)
db.session.commit()
except Exception:
pass
def get_posts_by_cat(cls, cat, limit=10, page=1, desc=True):
category = cls.query.filter_by(slug=cat).one_or_none()
if not category:
return None, None
posts = category.posts.order_by(db.text('id DESC')).limit(
limit).offset((page - 1) * limit)
return posts, category
class Request(Base):
__tablename__ = 'requests'
id = db.Column(db.Integer, primary_key=True)
request_type = db.Column(
db.String(64), index=True, nullable=False
) # change_organization_role | change_spec_definition | change_field_definition
namespace = db.Column(db.String(64), index=True) # organizations.namespace
resource = db.Column(db.String(64), index=True,
nullable=False) # users/fields/specs/organizations
resource_id = db.Column(db.Integer, index=True)
content = db.Column(db.JSON)
comments = db.Column(db.JSON) # comments
state = db.Column(db.Enum(RequestState),
nullable=False,
default=RequestState.reviewing,
index=True)
created_by = db.Column(db.Integer, index=True, nullable=False)
created_at = db.Column(db.TIMESTAMP,
server_default=db.text('CURRENT_TIMESTAMP'))
accepted_by = db.Column(db.Integer, index=True)
accepted_at = db.Column(db.TIMESTAMP)
def __repr__(self):
return '<Request {}@{}>'.format(self.request_type, self.created_by)
def delete(self):
self.state = RequestState.deleted
self.query.session.add(self)
self.query.session.commit()
return self
class Orders(db.Model):
"""
create a Orders table
"""
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
customer_id = db.Column(db.Integer, db.ForeignKey('customers.id'))
employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))
tablenumber = db.Column(db.Integer)
notes = db.Column(db.String(255))
orderstatus = db.Column(db.Integer, db.ForeignKey('status.id'))
email_sent = db.Column(
db.String(10)) #sent an email when the flag is set to 'S'
sms_sent = db.Column(
db.String(10)) #sent an sms when the flag is set to 'S'
processed = db.Column(db.String(10)) #flag 'N' or 'Y'
datecreated = db.Column(db.TIMESTAMP)
dateupdated = db.Column(
db.TIMESTAMP,
server_default=db.text(
'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
orders_list = db.relationship('OrdersList',
backref='order_items',
lazy='dynamic')
def __repr__(self):
return '<Orders: {}'.format(self.tablenumber)
def display_deploy_info():
counts = db.session.query(
db.func.count('*')).select_from(DeployInfo).scalar()
print counts
per_page = 10
search = False
q = request.args.get('q')
if q:
search = True
try:
page = int(request.args.get('page', 1))
except ValueError:
page = 1
offset = (page - 1) * per_page
sql = 'select * from deploy_info order by deploy_info.deploy_date desc limit {},{}'.format(
offset, per_page)
deploy = db.session.query(DeployInfo).from_statement(db.text(sql)).all()
pagination = Pagination(page=page,
link_size='sm',
total=counts,
record_name='deploy',
per_page=per_page,
bs_version=3,
format_total=True,
format_number=True,
show_single_page=False)
return render_template('list-deploy-log.html',
deploy=deploy,
pagination=pagination,
page=page,
per_page=per_page)
class Run(db.Model):
id = db.Column(db.Integer, primary_key = True, nullable=False)
started_at = db.Column(db.TIMESTAMP, nullable=True)
finished_at = db.Column(db.TIMESTAMP, nullable=True)
count = db.Column(db.Integer, nullable=False, server_default=db.text("0"))
tool_version = db.Column(db.Text, nullable=True)
wdtk_version = db.Column(db.Text, nullable=True)
dump_date = db.Column(db.Text, nullable=True)
@property
def estimated_factor(self):
expected_count = Run.query.filter(
Run.finished_at != None
).order_by(Run.finished_at.desc()).limit(1).one().count
return self.count / expected_count
@property
def estimated_percentage(self):
return int(self.estimated_factor * 100)
@property
def estimated_remaining(self):
current_seconds = (datetime.utcnow() - self.started_at).total_seconds()
if self.estimated_factor == 0:
r = Run.query.filter(Run.finished_at != None).order_by(Run.finished_at.desc()).limit(1).one()
return r.finished_at - r.started_at
return timedelta(seconds=current_seconds / self.estimated_factor - current_seconds)
def display_repos_info():
counts = db.session.query(
db.func.count('*')).select_from(RepoInfo).scalar()
per_page = 5
search = False
q = request.args.get('q')
if q:
search = True
try:
page = int(request.args.get('page', 1))
except ValueError:
page = 1
offset = (page - 1) * per_page
sql = 'select * from repos_info limit {},{}'.format(offset, per_page)
repos = db.session.query(RepoInfo).from_statement(db.text(sql)).all()
pagination = Pagination(page=page,
link_size='sm',
total=counts,
record_name='repos',
per_page=per_page,
bs_version=3,
format_total=True,
format_number=True,
show_single_page=False)
return render_template('list-repo.html',
repos=repos,
pagination=pagination,
page=page,
per_page=per_page)
def search_deploy_info():
if request.method=='POST' and request.form.get('envtype',None) :
args='%'+request.form.get('repo').encode('utf-8')+'%'
sql='select * from deploy_info where repo_name like "%'+request.form.get('repo').encode('utf-8')+'%" and deploy_env="'+request.form.get('envtype')+'" order by deploy_info.deploy_date desc'
deploy=db.session.query(DeployInfo).from_statement(db.text(sql)).all()
return render_template('deploy-search.html',deploys=deploy)
return redirect(url_for('display_deploy_info'))
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(64), index=True)
last_name = db.Column(db.String(64), index=True)
created_at = db.Column(db.TIMESTAMP,
nullable=False,
server_default=db.text("CURRENT_TIMESTAMP"))
updated_at = db.Column(
db.TIMESTAMP,
nullable=False,
server_default=db.text(
"CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
bookings = db.relationship('Booking', backref='booker', lazy='dynamic')
def __repr__(self):
return '<User {}>'.format(self.first_name)
class Order(db.Model):
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
oid = db.Column(db.String(20), unique=True, nullable=False) # 订单id
iid = db.Column(db.String(20), nullable=True)
uid = db.Column(db.String(20), nullable=False) # 用户id
aid = db.Column(db.String(20), nullable=False) # 商家id
sum = db.Column(db.Integer, nullable=False) # 数量
pid = db.Column(db.String(20), nullable=False) # 商品id
alevel = db.Column(db.Integer, nullable=False, default=0)
did = db.Column(db.String(20), nullable=True) # 最新的运输id
status = db.Column(db.Integer, default=0) # 0:未发货, 1:未收货, 2:已完成
addr = db.Column(db.Text, nullable=False)
created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False)
updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'),
server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
def map():
latlong = Metar.query.from_statement(
db.text(f"SELECT DISTINCT ON (station_id) station_id, * FROM metar;")
).all()
MTN_OBSCN = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'MTN OBSCN';"
))
IFR = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'IFR';"
))
TURB = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'TURB';"
))
ICE = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'ICE';"
))
pireps = Pirep.query.from_statement(
db.text(
"SELECT * FROM pirep WHERE observation_time >= NOW() - INTERVAL '1 HOUR';"
)).all()
CONVECTIVE = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'CONVECTIVE';"
))
ASH = Airsigmet.query.from_statement(
db.text(
"SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'ASH';"
))
airplane = os.path.join(app.root_path, 'static', 'styles', 'airplane.png')
print(airplane)
try:
url = f"http://api.ipstack.com/{request.headers['X-Real-IP']}?access_key={os.environ.get('IPSTACK')}"
r = requests.get(url)
j = json.loads(r.text)
latitude = j['latitude']
longitude = j['longitude']
except:
latitude = 44.967243
longitude = -103.771556
return render_template('map.html',
title='Follow',
latlong=latlong,
airplane=airplane,
MTN_OBSCN=MTN_OBSCN,
pireps=pireps,
IFR=IFR,
TURB=TURB,
ICE=ICE,
CONVECTIVE=CONVECTIVE,
ASH=ASH,
latitude=latitude,
longitude=longitude)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
created_at = db.Column(db.DateTime, server_default=db.text('NOW()'))
def __repr__(self):
return '<User %r>' % (self.username)
class Criterion(db.Model):
__tablename__ = 'criterions'
id = db.Column(
db.Integer,
primary_key=True,
server_default=db.text("nextval('criterias_id_seq'::regclass)"))
name = db.Column(db.String)
class StageLv(db.Model):
"""灵基再临材料"""
__tablename__ = 'stage_lv'
id = db.Column(db.Integer, primary_key=True)
servant_id = db.Column(db.Integer, db.ForeignKey('servants.id'), nullable=False)
stage = db.Column(db.SmallInteger, nullable=False)
item_id = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False)
quantity = db.Column(db.Integer, nullable=False)
note = db.Column(db.Text, nullable=True, server_default=None)
created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP'))
updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None)
servant = db.relationship('Servant', backref='stage_lv')
item = db.relationship('Item', backref='stage_lv')
def __repr__(self):
return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, '%s 阶段 %d' % (self.servant.name, self.stage))
@staticmethod
def validate(data):
"""
输入数据验证
:param dict data:
:return: bool, dict
"""
is_valid = True
errors = {}
return is_valid, errors
@classmethod
def edit(cls, data, servant_id):
for stage_lv_data in data:
stage_lv = \
cls.query.filter_by(servant_id=servant_id, stage=stage_lv_data['stage'], item_id=stage_lv_data['item_id']).first() \
or cls(servant_id=servant_id, stage=stage_lv_data['stage'], item_id=stage_lv_data['item_id'], quantity=stage_lv_data['quantity'])
stage_lv.quantity = stage_lv_data['quantity']
db.session.add(stage_lv)
db.session.commit()
def pagination(cls, limit=10, page=1, orderby='id', desc=True):
query = cls.query
count = query.count()
records = []
if count:
sort_by = '%s %s' % (orderby, 'DESC' if desc else 'ASC')
records = query.order_by(db.text(sort_by)).limit(
limit).offset((page - 1) * limit)
return records, count
def posts_by_user(cls, user_id, limit=10, page=1, orderby='id', desc=True):
query = cls.query.filter_by(user_id=user_id)
count = query.count()
records = []
if count:
sort_by = '%s %s' % (orderby, 'DESC' if desc else 'ASC')
records = query.order_by(db.text(sort_by)).limit(
limit).offset((page - 1) * limit)
return records, count
def display_repos_info():
counts=db.session.query(db.func.count('*')).select_from(RepoInfo).scalar()
per_page=5
search=False
q=request.args.get('q')
if q:
search=True
try:
page=int(request.args.get('page',1))
except ValueError:
page=1
offset=(page-1)*per_page
sql='select * from repos_info limit {},{}'.format(offset,per_page)
repos=db.session.query(RepoInfo).from_statement(db.text(sql)).all()
pagination = Pagination(page=page,link_size='sm',total=counts, record_name='repos',per_page=per_page,bs_version=3,format_total=True,format_number=True,show_single_page=False)
return render_template('list-repo.html',repos=repos,pagination=pagination,page=page,per_page=per_page)
def display_deploy_info():
counts=db.session.query(db.func.count('*')).select_from(DeployInfo).scalar()
print counts
per_page=10
search=False
q=request.args.get('q')
if q:
search=True
try:
page=int(request.args.get('page',1))
except ValueError:
page=1
offset=(page-1)*per_page
sql='select * from deploy_info order by deploy_info.deploy_date desc limit {},{}'.format(offset,per_page)
deploy=db.session.query(DeployInfo).from_statement(db.text(sql)).all()
pagination = Pagination(page=page,link_size='sm',total=counts, record_name='deploy',per_page=per_page,bs_version=3,format_total=True,format_number=True,show_single_page=False)
return render_template('list-deploy-log.html',deploy=deploy,pagination=pagination,page=page,per_page=per_page)
def get_last_replies(thread):
return db.session.query(Posts).filter_by(op_id = thread, deleted = 0).order_by(db.text('id desc')).limit(5)
def get_OPs_all():
return db.session.query(Posts).filter_by(op_id = '0' , deleted = 0).order_by(db.text('last_bump desc')).limit(10)
def get_user_posts(self, limit=10, page=1):
total = self.posts.count()
posts = self.posts.order_by(db.text("id DESC")).offset((page - 1) * limit).limit(limit)
return posts, total
def get_OPs_catalog(board):
return db.session.query(Posts).filter_by(op_id = '0', board = board, deleted = 0).order_by(db.text('last_bump desc')).limit(100)
def time_since_played(cls):
return db.func.cast(
db.func.extract(db.text('DAY'), db.func.now() - cls.played_at),
db.Float)
