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'
     }
Exemplo n.º 4
0
 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")
     )
Exemplo n.º 5
0
 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")
     )
Exemplo n.º 6
0
 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")
Exemplo n.º 7
0
 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)
Exemplo n.º 10
0
    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'
     }
Exemplo n.º 17
0
 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'}
Exemplo n.º 18
0
    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
        }
Exemplo n.º 19
0
    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'}
Exemplo n.º 20
0
    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'}
Exemplo n.º 21
0
 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'}
Exemplo n.º 22
0
    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]
        }
Exemplo n.º 23
0
 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'}
Exemplo n.º 24
0
    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'}
Exemplo n.º 25
0
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
    }
Exemplo n.º 26
0
 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'
     }
Exemplo n.º 27
0
    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}
Exemplo n.º 28
0
 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)
Exemplo n.º 29
0
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())
Exemplo n.º 30
0
 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'}
Exemplo n.º 31
0
 def exists(username):
     res = db.select('players',
                     where="username='******'".format(username),
                     limit=1)
     return hasattr(res, 'id')
Exemplo n.º 32
0
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(
Exemplo n.º 33
0
 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)
Exemplo n.º 34
0
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)
Exemplo n.º 35
0
 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)
Exemplo n.º 36
0
 def exist(self):
     stmt = db.select([run_well]).where(run_well.c.run_id == self.id)
     return len(db.session.execute(stmt).fetchall()) > 0
Exemplo n.º 37
0
Arquivo: dao.py Projeto: xchsp/Cooky
 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)
Exemplo n.º 38
0
Arquivo: dao.py Projeto: xchsp/Cooky
 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)
Exemplo n.º 39
0
Arquivo: dao.py Projeto: xchsp/Cooky
 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)
Exemplo n.º 40
0
 def vote_count(cls):
     return (db.select([db.func.count(User.id)
                        ]).where(Event.id == cls.id).label("votes"))
Exemplo n.º 41
0
 def all_chat_users(cid):
     return len(db.select('SELECT DISTINCT uid FROM messages WHERE cid = {}'.format(cid)))
Exemplo n.º 42
0
    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)))
Exemplo n.º 43
0
 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)
Exemplo n.º 44
0
 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)
Exemplo n.º 45
0
    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)))
Exemplo n.º 46
0
def get_dummy_user():
    return db.select("USER").where(id=1).execute()
Exemplo n.º 47
0
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('/')
Exemplo n.º 48
0
Arquivo: dao.py Projeto: xchsp/Cooky
 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)
Exemplo n.º 49
0
 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)
Exemplo n.º 50
0
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(
Exemplo n.º 51
0
    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']
Exemplo n.º 52
0
Arquivo: dao.py Projeto: xchsp/Cooky
 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)
Exemplo n.º 53
0
 def count_in_chat(cid):
     return db.select('SELECT COUNT(*) AS count FROM messages'
                      ' WHERE cid = {}'.format(cid))[0]['count']
Exemplo n.º 54
0
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()
Exemplo n.º 55
0
 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")