def get(self):
u = authorize(request)
u_id = request.args.get('id',None)
username = request.args.get('username',None)
# extract information from paramtaters
if u_id or username:
if u_id and db.exists("USER").where(id=u_id):
u_id = int(u_id)
elif username and db.exists("USER").where(username=username):
u_id = int(db.select("USER").where(username=username).execute()[0])
else:
abort(400,'Malformed Request')
else:
u_id = int(u[0])
# get information
u = db.select('USER').where(id=u_id).execute()
u_username = u[1]
follow_list = text_list_to_set(u[4])
posts_raw = db.select_all('POST').where(author=u_username).execute()
posts = [post[0] for post in posts_raw]
return {
'username': u[1],
'name': u[2],
'id' : int(u[0]),
'email': u[3],
'following': [int(x) for x in follow_list],
'followed_num': u[5],
'posts': posts
}
def put(self):
u = authorize(request)
j = request.json
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
if not j:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
(comment,) = unpack(j,'comment')
if comment == "":
abort(400, 'Malformed request')
comment_id = db.insert('COMMENT').with_values(
comment=comment,
author=u[1],
published=str(time.time())
).execute()
p = db.select('POST').where(id=id).execute()
comment_list = text_list_to_set(p[7],process_f=lambda x: int(x))
comment_list.add(comment_id)
comment_list = set_to_text_list(comment_list)
db.update('POST').set(comments=comment_list).where(id=id).execute()
return {
'message': 'success'
}
def put(self):
j = request.json
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
u = authorize(request)
u_username = u[1]
if not j or not id:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
# check the logged in user made this post
post_author = db.select('POST').where(id=id).execute()[1]
if u[1] != post_author:
# exposing what post id's are valid and unvalid
# may be a security issue lol
abort(403, 'You Are Unauthorized To Edit That Post')
(desc,src) = unpack(j,'description_text','src',required=False)
if desc == None and src == None:
abort(400, 'Malformed Request')
updated = {}
if desc:
updated['description'] = desc
if src:
updated['src'] = src
db.update('POST').set(**updated).where(id=id).execute()
return {
'message': 'success'
}
def order_qty(cls):
return (db.select([db.func.coalesce(
db.func.sum(
db.func.coalesce(
OrderedProducts.quantity - OrderedProducts.qty_delivered, 0)
), 0)])
.where(OrderedProducts.product_id == cls.id)
.label("order_qty")
)
def request_qty(cls):
return (db.select([db.func.coalesce(
db.func.sum(
db.func.coalesce(
RequestedProducts.quantity - RequestedProducts.qty_supplied, 0)
), 0)])
.where(RequestedProducts.product_id == cls.id)
.label("request_qty")
)
def id_string(cls):
""" IN SQL:
SELECT CONCAT(CONCAT(CONCAT(LEFT((SELECT card_types.type_name FROM card_types WHERE card_types.id = cards.card_type_id),1),letter),RIGHT(CONCAT('000000',cards.id),6)),"C") as nid FROM cards;
"""
return func.concat(func.concat(
func.concat(func.left(
db.select([Card_Type.type_name]).where(Card_Type.id == cls.card_type_id).limit(1).as_scalar(), 1),
cls.letter),
func.right(func.concat('000000', cls.id), 6)), "C")
def available_qty(cls):
return (db.select([Product.qty_stock - db.func.coalesce(
db.func.sum(
db.func.coalesce(
RequestedProducts.quantity - RequestedProducts.qty_supplied, 0)
), 0)])
.where(Product.id == cls.id)
.where(RequestedProducts.product_id == cls.id)
.label("available_qty")
)
def get(self):
u = get_dummy_user()
id = request.args.get('id',None)
if not id:
abort(400,'Malformed Request')
id =int(id)
p = db.select('POST').where(id=id).execute()
if not p:
abort(400,'Malformed Request')
return format_post(p)
def get(self):
u = authorize(request)
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
p = db.select('POST').where(id=id).execute()
if not p:
abort(400,'Malformed Request')
return format_post(p)
def last_chats_list():
chats = []
_chats = db.select('SELECT DISTINCT cid FROM messages ORDER BY id DESC LIMIT 9')
for chat in _chats:
current = Chat.get(int(chat['cid']))
last_date = db.select('SELECT date FROM messages WHERE cid = {} '
'ORDER BY id DESC LIMIT 1'.format(current.cid))[0]['date']
chats.append(
{
'hash': current.hash,
'title': current.title,
'msg_count': Message.count_in_chat(current.cid),
'last_date': last_date
}
)
return chats
def authorize(r):
t = r.headers.get('Authorization',None)
if not t:
abort(403,'Unsupplied Authorization Token')
try:
t = t.split(" ")[1]
except:
abort(403,'Invalid Authorization Token')
if not db.exists("USER").where(curr_token=t):
abort(403,'Invalid Authorization Token')
return db.select("USER").where(curr_token=t).execute()
def put(self):
u = authorize(request)
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
p = db.select('POST').where(id=id).execute()
likes = text_list_to_set(p[4],process_f=lambda x: int(x))
likes.discard(u[0])
likes = set_to_text_list(likes)
db.update('POST').set(likes=likes).where(id=id).execute()
return {
'message': 'success'
}
def get(self):
u = authorize(request)
n = request.args.get('n',10)
p = request.args.get('p',0)
following = text_list_to_set(u[4],process_f=lambda x:int(x))
following = [db.select('USER').where(id=int(id)).execute()[1] for id in following]
wildcards = ','.join(['?']*len(following))
q = 'SELECT * FROM POSTS WHERE author in ({})'.format(wildcards)
q+=' LIMIT ? OFFSET ?'
following.append(n)
following.append(p)
all_posts = db.raw(q,following)
all_posts = [format_post(row) for row in all_posts]
all_posts.sort(reverse=True,key=lambda x: float(x["meta"]["published"]))
return {
'posts': all_posts
}
def delete(self):
u = get_dummy_user()
id = request.args.get('id',None)
if not id:
abort(400,'Malformed Request')
id = int(id)
if not db.exists('POST').where(id=id):
abort(400,'Malformed Request')
p = db.select('POST').where(id=id).execute()
if p[1] != u[1]:
abort(403,'You Are Unauthorized To Make That Request')
comment_list = text_list_to_set(p[7])
[db.delete('COMMENT').where(id=c_id).execute() for c_id in comment_list]
db.delete('POST').where(id=id).execute()
return {
'message': 'success'
}
def put(self):
u = get_dummy_user()
id = request.args.get('id',None)
if not id:
abort(400, 'Malformed request')
id = int(id)
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
p = db.select('POST').where(id=id).execute()
likes = text_list_to_set(p[4],process_f=lambda x:int(x))
likes.add(u[0])
likes = set_to_text_list(likes)
db.update('POST').set(likes=likes).where(id=id).execute()
return {
'message': 'success'
}
def put(self):
u = authorize(request)
u_id = int(u[0])
follow_list = text_list_to_set(u[4],process_f=lambda x: int(x))
to_follow = request.args.get('username',None)
if to_follow == None or not db.exists('USER').where(username=to_follow):
abort(400,'Malformed Request')
if to_follow == u[1]:
abort(400,'Malformed Request')
to_follow = db.select('USER').where(username=to_follow).execute()[0]
if to_follow not in follow_list:
db.raw('UPDATE USERS SET FOLLOWED_NUM = FOLLOWED_NUM + 1 WHERE ID = ?',[to_follow])
follow_list.add(to_follow)
db.update('USER').set(following=set_to_text_list(follow_list)).where(id=u_id).execute()
return {
'message': 'success'
}
def delete(self):
u = authorize(request)
try:
id = int(request.args.get('id', None))
except:
abort(400, 'Malformed request')
if not id:
abort(400, 'Malformed Request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed Request')
p = db.select('POST').where(id=id).execute()
if p[1] != u[1]:
abort(403, 'You Are Unauthorized To Make That Request')
comment_list = text_list_to_set(p[7])
[
db.delete('COMMENT').where(id=c_id).execute()
for c_id in comment_list
]
db.delete('POST').where(id=id).execute()
return {'message': 'success'}
def get(self):
u = get_dummy_user()
u_id = int(request.args.get('id', u[0]))
if not db.exists('USER').where(id=u_id):
abort(400, 'Malformed Request')
u = db.select('USER').where(id=u_id).execute()
u_username = u[1]
follow_list = text_list_to_set(u[4])
posts_raw = db.select_all('POST').where(author=u_username).execute()
posts = [post[0] for post in posts_raw]
return {
'username': u[1],
'name': u[2],
'id': int(u[0]),
'email': u[3],
'following': [int(x) for x in follow_list],
'followed_num': u[5],
'posts': posts
}
def put(self):
u = get_dummy_user()
u_id = int(u[0])
following = text_list_to_set(u[4])
to_follow = request.args.get('username', None)
if to_follow == u[1]:
abort(400, 'Malformed Request')
if to_follow == None or not db.exists('USER').where(
username=to_follow):
abort(400, 'Malformed Request Or Unknown username')
to_follow = db.select('USER').where(username=to_follow).execute()[0]
if to_follow in following:
db.raw(
'UPDATE USERS SET FOLLOWED_NUM = FOLLOWED_NUM - 1 WHERE ID = ?',
[to_follow])
following.discard(to_follow)
db.update('USER').set(following=set_to_text_list(following)).where(
id=u_id).execute()
return {'message': 'success'}
def put(self):
u = authorize(request)
u_id = int(u[0])
follow_list = text_list_to_set(u[4], process_f=lambda x: int(x))
to_follow = request.args.get('username', None)
if to_follow == None or not db.exists('USER').where(
username=to_follow):
abort(400, 'Malformed Request')
if to_follow == u[1]:
abort(400, 'Malformed Request')
to_follow = db.select('USER').where(username=to_follow).execute()[0]
if to_follow not in follow_list:
db.raw(
'UPDATE USERS SET FOLLOWED_NUM = FOLLOWED_NUM + 1 WHERE ID = ?',
[to_follow])
follow_list.add(to_follow)
db.update('USER').set(following=set_to_text_list(follow_list)).where(
id=u_id).execute()
return {'message': 'success'}
def put(self):
j = request.json
try:
id = int(request.args.get('id', None))
except:
abort(400, 'Malformed request')
u = authorize(request)
u_username = u[1]
if not j or not id:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
# check the logged in user made this post
post_author = db.select('POST').where(id=id).execute()[1]
if u[1] != post_author:
# exposing what post id's are valid and unvalid
# may be a security issue lol
abort(403, 'You Are Unauthorized To Edit That Post')
(desc, title, src) = unpack(j,
'text',
'title',
'image',
required=False)
if desc == None and src == None and title == None:
abort(400, 'Malformed Request')
if desc != None and desc == '':
abort(400, 'Malformed Request')
if src != None and src == '':
abort(400, 'Malformed Request')
if title != None and title == '':
abort(400, 'Malformed Request')
updated = {}
if desc:
updated['description'] = desc
if src:
updated['src'] = src
updated['thumbnail'] = shrink(src)
if title:
updated['title'] = title
db.update('POST').set(**updated).where(id=id).execute()
return {'message': 'success'}
def get(self):
u = authorize(request)
try:
n = int(request.args.get('n',10))
p = int(request.args.get('p',0))
except:
abort(400, 'Malformed Request')
if n <= 0 or p < 0:
abort(400, 'Malformed Request')
following = text_list_to_set(u[4],process_f=lambda x:int(x))
following = [db.select('USER').where(id=int(id)).execute()[1] for id in following]
wildcards = ','.join(['?']*len(following))
q = 'SELECT * FROM POSTS WHERE author in ({})'.format(wildcards)
all_posts = db.raw(q,following)
all_posts = [format_post(row) for row in all_posts]
all_posts.sort(reverse=True,key=lambda x: int(x["meta"]["published"]))
return {
'posts': all_posts[p:p+n]
}
def put(self):
u = get_dummy_user()
j = request.json
id = request.args.get('id', None)
if not id or not j:
abort(400, 'Malformed request')
id = int(id)
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
(comment, ) = unpack(j, 'comment')
if comment == "":
abort(400, 'Malformed request')
comment_id = db.insert('COMMENT').with_values(
comment=comment, author=u[1],
published=str(time.time())).execute()
p = db.select('POST').where(id=id).execute()
comment_list = text_list_to_set(p[7], process_f=lambda x: int(x))
comment_list.add(comment_id)
comment_list = set_to_text_list(comment_list)
db.update('POST').set(comments=comment_list).where(id=id).execute()
return {'message': 'success'}
def put(self):
u = authorize(request)
u_id = int(u[0])
following = text_list_to_set(u[4], process_f=lambda x: int(x))
to_follow = get_request_arg('username', required=True)
if to_follow == u[1]:
abort(400, "You can't unfollow yourself either.")
if to_follow == None:
abort(400, "Expected 'username' query parameter")
if not db.exists('USER').where(username=to_follow):
abort(404, 'User Not Found')
to_follow = db.select('USER').where(username=to_follow).execute()[0]
if to_follow in following:
db.raw(
'UPDATE USERS SET FOLLOWED_NUM = FOLLOWED_NUM - 1 WHERE ID = ?',
[to_follow])
following.discard(to_follow)
db.update('USER').set(following=set_to_text_list(following)).where(
id=u_id).execute()
return {'message': 'success'}
def format_post(post):
comments = []
for c_id in text_list_to_set(post[7],process_f=lambda x:int(x)):
comment = db.select("COMMENT").where(id=c_id).execute()
comments.append({
"author": comment[1],
"published": comment[2],
"comment": comment[3]
})
return {
"id": post[0],
"meta": {
"author": post[1],
"description_text": post[2],
"published": post[3],
"likes": list(text_list_to_set(post[4],process_f=lambda x:int(x)))
},
"thumbnail": post[5],
"src": post[6],
"comments": comments
}
def put(self):
u = authorize(request)
j = get_request_json()
id = get_request_arg('id', int, required=True)
if not db.exists('POST').where(id=id):
abort(404, 'Post Not Found')
(comment,) = unpack(j,'comment')
if comment == "":
abort(400, 'Comment cannot be empty')
comment_id = db.insert('COMMENT').with_values(
comment=comment,
author=u[1],
published=str(time.time())
).execute()
p = db.select('POST').where(id=id).execute()
comment_list = text_list_to_set(p[7],process_f=lambda x: int(x))
comment_list.add(comment_id)
comment_list = set_to_text_list(comment_list)
db.update('POST').set(comments=comment_list).where(id=id).execute()
return {
'message': 'success'
}
def get(self):
u = authorize(request)
n = get_request_arg('n', int, default=10)
p = get_request_arg('p', int, default=0)
following = text_list_to_set(u[4], process_f=lambda x: int(x))
following = [
db.select('USER').where(id=int(id)).execute()[1]
for id in following
]
wildcards = ','.join(['?'] * len(following))
# very inefficent but it'll work
q = 'SELECT * FROM POSTS WHERE author in ({})'.format(wildcards)
all_posts = db.raw(q, following)
all_posts = [format_post(row) for row in all_posts]
all_posts.sort(reverse=True,
key=lambda x: float(x["meta"]["published"]))
if p > len(all_posts) - 1:
all_posts = []
else:
all_posts = all_posts[p:p + n]
return {'posts': all_posts}
def __init__(self, id=0, ip=0, dummy=False):
if dummy == True:
return
if ip != 0 and id == 0:
self.ip = ip
self.first_login = get_timestamp()
self.id = make_hash(ip, self.first_login)
db.insert(table='users',
values={
'id': self.id,
'first_login': self.first_login
})
self.fng = 1
return
res = db.select(table='users',
fields=['banned', 'first_login', 'admin'],
params={'id': id})
self.id = id
self.ip = ip
self.first_login = res.first_login
self.banned = int(res.banned)
self.admin = int(res.admin)
class Tag(db.Model, dbFunctions):
__tablename__ = "tags"
query_class = TagQuery
id = db.Column(db.Integer, primary_key=True)
slug = db.Column(db.String(80), unique=True)
posts = db.dynamic_loader(Post,
secondary=post_tags,
query_class=PostQuery,
cascade="all, delete")
#posts = db.relationship(Post, secondary='post_tags', lazy='dynamic',
# backref=db.backref('tags', lazy='dynamic'), cascade="all, delete")
_name = db.Column("name", db.String(80), unique=True)
def __str__(self):
return self.name
def _get_name(self):
return self._name
def _set_name(self, name):
self._name = name.lower().strip()
self.slug = slugify(name)
name = db.synonym("_name", descriptor=property(_get_name, _set_name))
@cached_property
def url(self):
return url_for("home.tag", slug=self.slug)
num_posts = db.column_property(
db.select([db.func.count(post_tags.c.post_id)]).\
where(db.and_(post_tags.c.tag_id==id,
Post.id==post_tags.c.post_id,
Post.access==Post.PUBLIC)).as_scalar())
def put(self):
u = authorize(request)
u_username = u[1]
j = get_request_json()
id = get_request_arg('id', int, required=True)
if not db.exists('POST').where(id=id):
abort(404, 'Post Not Found')
# check the logged in user made this post
post_author = db.select('POST').where(id=id).execute()[1]
if u[1] != post_author:
# exposing what post id's are valid and unvalid
# may be a security issue lol
abort(403, 'You Are Unauthorized To Edit That Post')
(desc, src) = unpack(j, 'description_text', 'src', required=False)
if desc == None and src == None:
abort(400, "Expected at least 'description_text' or 'src'")
updated = {}
if desc:
updated['description'] = desc
if src:
updated['src'] = src
updated['thumbnail'] = self._gen_thumbnail(src)
db.update('POST').set(**updated).where(id=id).execute()
return {'message': 'success'}
def exists(username):
res = db.select('players',
where="username='******'".format(username),
limit=1)
return hasattr(res, 'id')
def install_trigram_indices():
import sys, inspect
classes = inspect.getmembers(sys.modules[__name__], lambda member: inspect.isclass(member) and member.__module__ == __name__ )
for classname, classtype in classes:
if hasattr(classtype, "__searchable__") and issubclass(classtype, db.Model):
for column in classtype.__searchable__:
install_trigram_indice_on_column(classtype, column)
tags_mv_name = "common_tags_mv"
tags_mv_selectable = db.select(
[
db.func.min(Tags.id).label('id'),
Tags.tag.label('tag'),
db.func.count(Tags.tag).label('tag_instances'),
]
).group_by(Tags.tag)
genre_mv_name = "common_genre_mv"
genre_mv_selectable = db.select(
[
db.func.min(Genres.id).label('id'),
Genres.genre.label('genre'),
db.func.count(Genres.genre).label('genre_instances'),
]
).group_by(Genres.genre)
class CommonTags(util.materialized_view_factory.MaterializedView):
__table__ = util.materialized_view_factory.create_mat_view(
def getUserCommands(self, id_User):
query = 'SELECT C.id, C.id_Cart, C.creationDate, C.arrivalDate FROM Command C, Cart WHERE Cart.id = C.id_Cart AND Cart.id_User = %(id_User)s'
results = db.select(query, {'id_User': id_User})
return self._mapper.from_tuples(results)
def select_ints():
azses = AZS.query.all()
rus = RU.query.all()
# ips = Ip.query.all()
pageType = 'Controller'
# выбор интерфейсов из списка азс
# select ip.interface from ip join azs on azs.id=ip.azs_id where azs.id in (3,4,5,6,7);
# выбор уникальных из определённого региона
# select distinct ip.interface from ip join azs on azs.id=ip.azs_id where azs.ru=3;
# print(dir(request))
# print('>>> откуда пришёл?', request.referrer)
chose = [] # сюда будем складывать ид АЗСок, что нам передали
ints_ru_chosen = {} # сюда будем складывать выбранные для РУ интерфейсы
ints_azs_chosen = {} # сюда будем складывать выбранные для АЗС интерфейсы
ints_ru_clear = [] # здесь будет список для очистки
gen_subnets = False # этот флаг показывает, что пользователь хочет скачать xls
# print('>>> Ints loaded')
if request.method == 'POST':
print('>>> POST')
for selected in request.form:
# print(selected)
if selected == 'gen_subnets':
gen_subnets = True
elif '#' in selected:
ru, eth = selected.split('#')
if eth == 'Clear': # нажата кнопка очистки
ints_ru_clear.append(ru)
else:
ints_ru_chosen[
ru] = eth # здесь выбранные интерфейсы для РУ вида {'1': 'eth0.12'}
elif '=' in selected: # здесь выбранные интерфейсы для АЗС вида {'1': 'eth0.12'}
azs, eth = selected.split('=')
# print(ints_azs_chosen)
if azs in ints_azs_chosen:
ints_azs_chosen[azs].append(
eth
) # если уже есть интерфейсы для этой азс - добавим новый
else:
ints_azs_chosen[azs] = [
eth
] # если нет - создадим список из одного
else:
chose.append(
selected
) # теперь chose содержит список АЗС.id вида [1,2,5,12]
# print(ints_azs_chosen)
# print(ints_ru_chosen)
# print(chose)
ints_from_chose = []
join = db.join(Ip, AZS, AZS.id == Ip.azs_id)
select_ints = db.select([Ip.interface, Ip.azs_id, AZS.ru
]).select_from(join).where(AZS.id.in_(chose))
for elem in db.session.execute(select_ints):
# print(elem)
temp_list = [x for x in elem]
if (str(temp_list[2]) not in ints_ru_clear) and \
((str(temp_list[2]) in ints_ru_chosen) and (ints_ru_chosen[str(temp_list[2])] == str(temp_list[0])) or \
((str(temp_list[1]) in ints_azs_chosen) and (str(temp_list[0]) in ints_azs_chosen[str(temp_list[1])]))): # выбран такой интерфейс для региона
temp_list.append(True) # нашли интерфейс в помеченных
# print('>>> FIND CHOSEN!')
else:
# if (temp_list[2] in ints_azs_chosen) and (ints_azs_chosen[temp_list[2]] == temp_list[0]):
temp_list.append(False)
ints_from_chose.append(temp_list)
ints_from_chose.sort()
# print(ints_from_chose)
# интерфейсы выбранных азс вида
# [['eth0.5', 48, 2, False], ['eth0.2', 48, 2, False], ['eth0.12', 48, 2, False],
# ['eth0.1', 48, 2, False], ['eth0.7', 48, 2, False], ['eth0.6', 48, 2, False],
# ['eth0.3', 48, 2, False], ['eth0.5', 69, 1, True], ['eth0.11', 69, 1, False]]
azses_from_chose = []
select_azses = db.select([AZS.id, AZS.sixdign,
AZS.ru]).where(AZS.id.in_(chose))
# azses_from_chose = [x for x in db.session.execute(select_azses)]
for elem in db.session.execute(select_azses):
temp_list = [x for x in elem]
# if (temp_list[2] in ints_ru_chosen) and ():
temp_list.append(False)
azses_from_chose.append(temp_list)
# здесь теперь расширенный список АЗС, в нём теперь есть ид, код, РУ, отметка выбранного
# [[37, '766325', 2, False], [67, '793805', 1, False], [166, '068101', 1, False], [191, '481729', 1, False]]
# print(azses_from_chose)
ru_from_chose = {}
join = db.join(RU, AZS, AZS.ru == RU.id)
select_uniq_ru = db.select([AZS.ru, RU.name]).select_from(join).where(
AZS.id.in_(chose)).distinct()
# ru_from_chose = [x for x in db.session.execute(select_uniq_ru)]
for elem in db.session.execute(select_uniq_ru):
ru_from_chose[elem[0]] = elem[1]
# уникальные РУ вида {2: 'MSK', 1: 'SPB'}
# print(ru_from_chose)
uniq_ints_from_ru = {}
for ru in ru_from_chose:
l = list(set([x[0] for x in ints_from_chose if x[2] == ru]))
l.sort()
uniq_ints_from_ru[ru] = l
# print(uniq_ints_from_ru)
# теперь тут есть для каждого РУ свой набор интерфейсов, которые есть в выбранных азс, вида:
# {1: ['eth0.1', 'eth0.8', 'eth0.10', 'eth0.6', 'eth0.10', 'eth0.6', 'eth0.4', 'eth0.2', 'eth0.5',
# 'eth0.12', 'eth0.3', 'eth0.2', 'eth0.8', 'eth0.9'], 2: ['eth0.11', 'eth0.2', 'eth0.12', 'eth0.7',
# 'eth0.3', 'eth0.3', 'eth0.12']}
# если пользователь нажал скачать!
if gen_subnets is True:
to_xls_list = []
join = db.join(Ip, AZS, AZS.id == Ip.azs_id)
select_nets = db.select([
AZS.id, AZS.sixdign, AZS.num, AZS.ru, AZS.address,
Ip.interface, Ip.net, Ip.description
]).select_from(join).where(AZS.id.in_(ints_azs_chosen.keys()))
for elem in db.session.execute(select_nets):
azs_id, six, num, ru, addr, eth, net, desc = elem
# elem[0], elem[1], helpers.eth_to_vlan
for interface in ints_from_chose:
# [['eth0.5', 48, 2, False], ['eth0.2', 48, 2, False], ['eth0.12', 48, 2, False],
# смотрим в списке отмеченных интерфейсов, если находим наши - помещаем в список
# возможно был способ сделать хитрый запрос SQL, но мне не удалось с ходу, может в будущем
if (azs_id == interface[1]) and \
(eth == interface[0]) and \
(interface[3] is True):
to_xls_list.append({
'azs_id': azs_id,
'sixdign': six,
'num': num,
'ru': ru_from_chose[ru],
'addr': addr,
'int': eth,
'desc': desc,
'subnet': net
})
continue
to_xls_list = sorted(
to_xls_list,
key=lambda elem: elem['sixdign']) # сортируем по коду
# print(to_xls_list)
# [{'azs_id': 127, 'sixdign': '031871', 'num': 871, 'ru': 'KMR', 'addr': 'DIFFERENT_address-031871',
# 'int': 'eth0.10', 'desc': 'пояснения', 'subnet': '10.7.252.245/30'},
# создаём буффер, заполняемый экселем
output = BytesIO()
row_counter = 0
interface_cols = {}
with xlsxwriter.Workbook(output) as book:
sheet = book.add_worksheet('Выборка' +
str(datetime.utcnow()).split()[0])
cell_bold = book.add_format({'bold': True})
sheet.set_row(0, 30, cell_bold)
sheet.write_string(0, 0, '№\nп/п')
sheet.set_column(0, 0, 3)
sheet.write_string(0, 1, 'Код')
sheet.set_column(1, 1, 8)
sheet.write_string(0, 2, 'Отд.')
sheet.set_column(2, 2, 6)
sheet.write_string(0, 3, '№')
sheet.set_column(3, 3, 4)
sheet.write_string(0, 4, 'Адрес')
sheet.set_column(4, 4, 24)
# заполняем строки с данными АЗС
for azs in to_xls_list:
row_counter += 1
net_desc = helpers.eth_to_vlan(azs['int']) + '\n' + azs[
'desc'] # здесь будет номер влана и подпись
sheet.write_number(row_counter, 0,
row_counter) # номер пункта
sheet.write_string(row_counter, 1,
azs['sixdign']) # шестизначный код
sheet.write_string(row_counter, 2, azs['ru']) # РУ
sheet.write_number(row_counter, 3, azs['num']) # номер
sheet.write_string(row_counter, 4, azs['addr']) # адрес
if net_desc not in interface_cols.keys(
): # если в титуле таблицы нет подсети
interface_cols[net_desc] = len(interface_cols) + 5
sheet.write_string(0, interface_cols[net_desc],
net_desc)
sheet.set_column(interface_cols[net_desc],
interface_cols[net_desc], 16)
sheet.write_string(row_counter, interface_cols[net_desc],
azs['subnet'])
output.seek(0)
helpers.add_log(
current_user.id,
'Скачал отчёт с количеством АЗС в {} шт.'.format(
str(row_counter)))
db.session.commit()
return send_file(output,
attachment_filename='azs_subnets.xlsx',
as_attachment=True)
# 55, '216977', 'DIFFERENT_address-216977', 'eth0.7', '10.14.249.74/30', None)
# (55, '216977', 'DIFFERENT_address-216977', 'eth0.8', '10.3.50.94/30', None)
# print(ints_from_chose)
# print(ru_from_chose)
# print(uniq_ints_from_ru)
# return render_template('select_ints.html', title='Выберите интерфейсы', chose=chose, rus=rus, azses=azses, pageType=pageType)
return render_template('select_ints.html',
title='Выберите интерфейсы',
pageType=pageType,
rus=ru_from_chose,
ints=ints_from_chose,
azses=azses_from_chose,
ru_ints=uniq_ints_from_ru)
def getIngredientsByRecipe(self, id_Recipe):
querry = 'SELECT * FROM RecipeIngredient WHERE id_Recipe = %(id_Recipe)s'
results = db.select(querry, {'id_Recipe': id_Recipe})
return self._mapper.from_tuples(results)
def exist(self):
stmt = db.select([run_well]).where(run_well.c.run_id == self.id)
return len(db.session.execute(stmt).fetchall()) > 0
def getRatingsByUser(self, id_User):
query = 'SELECT * FROM Rating WHERE Rating.id_User = %(id_User)s'
results = db.select(query, {'id_User': id_User})
return self._mapper.from_tuples(results)
def getRecipeComments(self, id_Recipe):
query = 'SELECT * FROM Comment WHERE Comment.id_Recipe = %(id_Recipe)s'
results = db.select(query, { 'id_Recipe': id_Recipe })
return self._mapper.from_tuples(results)
def getRecipesByName(self, name):
query = 'SELECT * FROM Recipe WHERE LOWER(name) LIKE LOWER(%(name)s)'
results = db.select(query, {'name': '%{}%'.format(name)})
return self._mapper.from_tuples(results)
def vote_count(cls):
return (db.select([db.func.count(User.id)
]).where(Event.id == cls.id).label("votes"))
def all_chat_users(cid):
return len(db.select('SELECT DISTINCT uid FROM messages WHERE cid = {}'.format(cid)))
def today_chat_users(cid):
t = datetime.today()
today = int(datetime(t.year, t.month, t.day, 0).timestamp())
return len(db.select('SELECT DISTINCT uid FROM messages '
'WHERE cid = {} AND date >= {}'.format(cid, today)))
def max_played(cls):
return db.func.cast(
db.select([db.func.greatest(db.func.max(cls.count_played),
1)]).label('max_played'), db.Float)
def getAccountByUserId(self, id_User):
query = 'SELECT * FROM Account WHERE id_User = %(id_User)s'
result = db.select(query, {'id_User': id_User}, limit=1)
return self._mapper.from_tuple(result)
def today_all_active_users():
t = datetime.today()
today = int(datetime(t.year, t.month, t.day, 0).timestamp())
return len(db.select('SELECT DISTINCT uid FROM messages '
'WHERE date >= {}'.format(today)))
def get_dummy_user():
return db.select("USER").where(id=1).execute()
def group(chat_hash):
chat = Chat.where('hash', chat_hash).first()
if chat:
cid = chat.cid
# Get chat statistics
chat_stats = db.select('(SELECT * FROM chat_stats '
'WHERE cid = "{}" '
'ORDER BY id DESC LIMIT 21) '
'ORDER BY id ASC'.format(cid))
# Chat title
chat_title = chat.title
# Bot add date, dd.mm.yy
add_date = datetime.fromtimestamp(chat.add_time).strftime('%d.%m.%y')
# Today messages NOT USED YET
today_messages = Message.today_chat_count(cid)
# All number of users
all_users = Chat.all_chat_users(cid)
# Today active users
active_users = Chat.today_chat_users(cid)
# Last update
last_update = datetime.fromtimestamp(chat_stats[-1].last_time).strftime('%d.%m.%y (%H:%M)')
# Link for public chats
public_link = chat.public_link
average_users = 0
chart = {'labels': [], 'msg_values': [], 'users_values': []}
# Charts generator
i = 0
for chat in chat_stats:
average_users += chat.users_count
# Dates, dd/mm
d = datetime.fromtimestamp(chat.last_time).strftime('%d')
chart['labels'].append(str(d))
chart['msg_values'].append(chat.msg_count)
chart['users_values'].append(chat.users_count)
i += 1
# Average number of users
average_users = round(average_users / i)
# Generating user list
users = []
user_stats = UserStat.where('cid', cid).order_by('msg_count', 'desc').limit(50).get().all()
for ustat in user_stats:
user = User.get(ustat.uid)
users.append({'name': user.fullname,
'msg_count': ustat.msg_count,
'uid': ustat.uid,
'public': user.public})
# Generating entities
entities = Entity.generate_list(cid)
return render_template('group.html',
page_title='{} - Confstat'.format(chat_title),
chat_title=chat_title,
add_date=add_date,
today_messages=today_messages,
all_users=all_users,
active_users=active_users,
average_users=average_users,
chart=chart,
users=users,
entities=entities[0],
urls=entities[1],
last_update=last_update,
public_link=public_link)
else:
return redirect('/')
def getCurrentUserCart(self, userId):
query = 'SELECT * FROM Cart WHERE Cart.id_User = %(userId)s AND Cart.id NOT IN (SELECT Command.id_Cart FROM Command)'
result = db.select(query, {'userId': userId}, 1)
return self._mapper.from_tuple(result)
def max_played(cls):
return db.func.cast(
db.select([
db.func.greatest(db.func.max(cls.count_played), 1)
]).label('max_played'),
db.Float)
def install_trigram_indices():
import sys, inspect
classes = inspect.getmembers(sys.modules[__name__], lambda member: inspect.isclass(member) and member.__module__ == __name__ )
for classname, classtype in classes:
if hasattr(classtype, "__searchable__") and issubclass(classtype, db.Model):
for column in classtype.__searchable__:
install_trigram_indice_on_column(classtype, column)
tags_mv_name = "common_tags_mv"
tags_mv_selectable = db.select(
[
db.func.min(Tags.id).label('id'),
Tags.tag.label('tag'),
db.func.count(Tags.tag).label('tag_instances'),
]
).group_by(Tags.tag)
genre_mv_name = "common_genre_mv"
genre_mv_selectable = db.select(
[
db.func.min(Genres.id).label('id'),
Genres.genre.label('genre'),
db.func.count(Genres.genre).label('genre_instances'),
]
).group_by(Genres.genre)
class CommonTags(util.materialized_view_factory.MaterializedView):
__table__ = util.materialized_view_factory.create_mat_view(
def today_all_count():
t = datetime.today()
today = int(datetime(t.year, t.month, t.day, 0).timestamp())
return db.select('SELECT COUNT(*) AS count FROM messages'
' WHERE date >= {}'.format(today))[0]['count']
def getLikeRecipeByUser(self, id_User):
query = 'SELECT * FROM LikeRecipe WHERE id_User = %(id_User)s'
results = db.select(query, {'id_User': id_User})
return self._mapper.from_tuples(results)
def count_in_chat(cid):
return db.select('SELECT COUNT(*) AS count FROM messages'
' WHERE cid = {}'.format(cid))[0]['count']
def delete_from_account(account_id):
account = Account.query.get(account_id)
if account.next_delete > datetime.now(timezone.utc):
return
latest_n_posts = (Post.query.with_parent(account, 'posts').order_by(
db.desc(Post.created_at)).limit(
account.policy_keep_latest).cte(name='latest'))
posts = (Post.query.with_parent(account, 'posts').filter(
Post.created_at + account.policy_keep_younger <= db.func.now()).filter(
~Post.id.in_(db.select((latest_n_posts.c.id, )))))
if (account.policy_keep_favourites != 'none'):
posts = posts.filter(
db.or_(
Post.favourite == (
account.policy_keep_favourites == 'deleteonly'),
Post.is_reblog))
if (account.policy_keep_media != 'none'):
posts = posts.filter(
db.or_(
Post.has_media == (account.policy_keep_media == 'deleteonly'),
Post.is_reblog))
if (account.policy_keep_direct):
posts = posts.filter(~Post.direct)
limit = 100
if account.service == 'mastodon':
limit = 10
posts = posts.order_by(db.func.random()).limit(limit).all()
to_delete = None
def is_eligible(post):
return (post.is_reblog or (
(account.policy_keep_favourites == 'none' or
(account.policy_keep_favourites == 'keeponly'
and not post.favourite) or
(account.policy_keep_favourites == 'deleteonly'
and post.favourite)) and
(account.policy_keep_media == 'none' or
(account.policy_keep_media == 'keeponly' and not post.has_media)
or (account.policy_keep_media == 'deleteonly' and post.has_media))
and (not account.policy_keep_direct or not post.direct)))
try:
action = noop
if account.service == 'twitter':
action = libforget.twitter.delete
posts = refresh_posts(posts)
to_delete = next(filter(is_eligible, posts), None)
elif account.service == 'mastodon':
action = libforget.mastodon.delete
for post in posts:
refreshed = refresh_posts((post, ))
if refreshed and is_eligible(refreshed[0]):
to_delete = refreshed[0]
break
if to_delete:
print("Deleting {}".format(to_delete))
account.touch_delete()
action(to_delete)
account.reset_backoff()
else:
account.next_delete = db.func.now() + timedelta(minutes=3)
except TemporaryError:
db.session.rollback()
account.backoff()
finally:
db.session.commit()
def id_string(cls):
return func.concat(func.concat(
func.concat(func.left(
db.select([Question_Type.type_name]).where(Question_Type.id == cls.question_type_id).limit(1).as_scalar(),
1), cls.letter),
func.right(func.concat('000000', cls.id), 6)), "Q")
