Пример #1
0
def polymorphic_union(table_map, typecolname, aliasname='p_union'):
    """create a UNION statement used by a polymorphic mapper.
    
    See the SQLAlchemy advanced mapping docs for an example of how this is used."""
    colnames = util.Set()
    colnamemaps = {}
    types = {}
    for key in table_map.keys():
        table = table_map[key]

        # mysql doesnt like selecting from a select; make it an alias of the select
        if isinstance(table, sql.Select):
            table = table.alias()
            table_map[key] = table

        m = {}
        for c in table.c:
            colnames.add(c.name)
            m[c.name] = c
            types[c.name] = c.type
        colnamemaps[table] = m

    def col(name, table):
        try:
            return colnamemaps[table][name]
        except KeyError:
            return sql.cast(sql.null(), types[name]).label(name)

    result = []
    for type, table in table_map.iteritems():
        if typecolname is not None:
            result.append(sql.select([col(name, table) for name in colnames] + [sql.literal_column("'%s'" % type).label(typecolname)], from_obj=[table]))
        else:
            result.append(sql.select([col(name, table) for name in colnames], from_obj=[table]))
    return sql.union_all(*result).alias(aliasname)
Пример #2
0
def polymorphic_union(table_map, typecolname, aliasname="p_union", cast_nulls=True):
    """Create a ``UNION`` statement used by a polymorphic mapper.

    See  :ref:`concrete_inheritance` for an example of how
    this is used.

    :param table_map: mapping of polymorphic identities to
     :class:`.Table` objects.
    :param typecolname: string name of a "discriminator" column, which will be
     derived from the query, producing the polymorphic identity for each row.  If
     ``None``, no polymorphic discriminator is generated.
    :param aliasname: name of the :func:`~sqlalchemy.sql.expression.alias()`
     construct generated.
    :param cast_nulls: if True, non-existent columns, which are represented as labeled
     NULLs, will be passed into CAST.   This is a legacy behavior that is problematic
     on some backends such as Oracle - in which case it can be set to False.

    """

    colnames = util.OrderedSet()
    colnamemaps = {}
    types = {}
    for key in table_map.keys():
        table = table_map[key]

        # mysql doesnt like selecting from a select;
        # make it an alias of the select
        if isinstance(table, sql.Select):
            table = table.alias()
            table_map[key] = table

        m = {}
        for c in table.c:
            colnames.add(c.key)
            m[c.key] = c
            types[c.key] = c.type
        colnamemaps[table] = m

    def col(name, table):
        try:
            return colnamemaps[table][name]
        except KeyError:
            if cast_nulls:
                return sql.cast(sql.null(), types[name]).label(name)
            else:
                return sql.type_coerce(sql.null(), types[name]).label(name)

    result = []
    for type, table in table_map.iteritems():
        if typecolname is not None:
            result.append(
                sql.select(
                    [col(name, table) for name in colnames]
                    + [sql.literal_column(sql_util._quote_ddl_expr(type)).label(typecolname)],
                    from_obj=[table],
                )
            )
        else:
            result.append(sql.select([col(name, table) for name in colnames], from_obj=[table]))
    return sql.union_all(*result).alias(aliasname)
Пример #3
0
def polymorphic_union(table_map, typecolname, aliasname='p_union'):
    colnames = util.Set()
    colnamemaps = {}
    types = {}
    for key in table_map.keys():
        table = table_map[key]

        # mysql doesnt like selecting from a select; make it an alias of the select
        if isinstance(table, sql.Select):
            table = table.alias()
            table_map[key] = table

        m = {}
        for c in table.c:
            colnames.add(c.name)
            m[c.name] = c
            types[c.name] = c.type
        colnamemaps[table] = m
        
    def col(name, table):
        try:
            return colnamemaps[table][name]
        except KeyError:
            return sql.cast(sql.null(), types[name]).label(name)

    result = []
    for type, table in table_map.iteritems():
        if typecolname is not None:
            result.append(sql.select([col(name, table) for name in colnames] + [sql.column("'%s'" % type).label(typecolname)], from_obj=[table]))
        else:
            result.append(sql.select([col(name, table) for name in colnames], from_obj=[table]))
    return sql.union_all(*result).alias(aliasname)
    
Пример #4
0
def get_activity_query(user_id=None, session_id=None, test_id=None):
    # pylint: disable=no-member
    from .models import Activity, Comment, User

    _filter = functools.partial(_apply_filters, user_id=user_id, session_id=session_id, test_id=test_id)

    comments = select([
            literal_column("('comment:' || comment.id)").label('id'),
            literal_column(str(ACTION_COMMENTED)).label('action'),
            Comment.user_id.label('user_id'),
            Comment.session_id.label('session_id'),
            Comment.test_id.label('test_id'),
            Comment.timestamp.label('timestamp'),
            Comment.comment.label('text'),
            User.email.label('user_email'),
        ]).select_from(Comment.__table__.join(User, User.id == Comment.user_id))

    comments = _filter(Comment, comments)

    activity = select([
            literal_column("('activity:' || activity.id)").label('id'),
            Activity.action.label('action'),
            Activity.user_id.label('user_id'),
            Activity.session_id.label('session_id'),
            Activity.test_id.label('test_id'),
            Activity.timestamp.label('timestamp'),
            literal_column("NULL").label('text'),
            User.email.label('user_email'),
        ]).select_from(Activity.__table__.join(User, User.id == Activity.user_id))

    activity = _filter(Activity, activity)

    u = union_all(comments, activity).alias('u')

    return select([u]).order_by(u.c.timestamp)
Пример #5
0
    def _generate_monthly_uniques(self, table, tables):
        idx = tables.index(table)

        # Join them all
        at = union_all(*[
            select([tbl]).where(tbl.c.message == "Ack")
            for tbl in tables[idx-29:idx+1]
        ])

        # Get uniques
        s = select([
            func.count(func.distinct(at.c.uaid_hash)).
            label("unique_count"),
            at.c.browser_os,
            at.c.browser_version,
        ]).\
            group_by(at.c.browser_os, at.c.browser_version)
        results = self._conn.execute(s).fetchall()
        if not results:
            return

        # Determine the date for this entry
        tname = table.name
        date_parts = [tname[-8:-4], tname[-4:-2], tname[-2:]]
        insert_date = "-".join(date_parts)

        self._conn.execute(monthly_rollup.insert(), [
            dict(date=insert_date,
                 count=x.unique_count,
                 browser_os=x.browser_os,
                 browser_version=x.browser_version)
            for x in results
        ])
Пример #6
0
    def AvailableWriteReferences(self, request, context):
        # can't write anything for ourselves, but let's return empty so this can be used generically on profile page
        if request.to_user_id == context.user_id:
            return references_pb2.AvailableWriteReferencesRes()

        with session_scope() as session:
            if not session.execute(
                    select(User).where_users_visible(context).where(
                        User.id == request.to_user_id)).scalar_one_or_none():
                context.abort(grpc.StatusCode.NOT_FOUND, errors.USER_NOT_FOUND)

            can_write_friend_reference = (session.execute(
                select(Reference).where(
                    Reference.from_user_id == context.user_id).where(
                        Reference.to_user_id == request.to_user_id).where(
                            Reference.reference_type == ReferenceType.friend)).
                                          scalar_one_or_none()) is None

            q1 = (select(literal(True), HostRequest).outerjoin(
                Reference,
                and_(
                    Reference.host_request_id == HostRequest.conversation_id,
                    Reference.from_user_id == context.user_id,
                ),
            ).where(Reference.id == None).where(
                HostRequest.can_write_reference).where(
                    HostRequest.surfer_user_id == context.user_id).where(
                        HostRequest.host_user_id == request.to_user_id))

            q2 = (select(literal(False), HostRequest).outerjoin(
                Reference,
                and_(
                    Reference.host_request_id == HostRequest.conversation_id,
                    Reference.from_user_id == context.user_id,
                ),
            ).where(Reference.id == None).where(
                HostRequest.can_write_reference).where(
                    HostRequest.surfer_user_id == request.to_user_id).where(
                        HostRequest.host_user_id == context.user_id))

            union = union_all(q1, q2).order_by(
                HostRequest.end_time_to_write_reference.asc()).subquery()
            union = select(union.c[0].label("surfed"),
                           aliased(HostRequest, union))
            host_request_references = session.execute(union).all()

            return references_pb2.AvailableWriteReferencesRes(
                can_write_friend_reference=can_write_friend_reference,
                available_write_references=[
                    references_pb2.AvailableWriteReferenceType(
                        host_request_id=host_request.conversation_id,
                        reference_type=reftype2api[
                            ReferenceType.surfed if surfed else ReferenceType.
                            hosted],
                        time_expires=Timestamp_from_datetime(
                            host_request.end_time_to_write_reference),
                    ) for surfed, host_request in host_request_references
                ],
            )
Пример #7
0
def polymorphic_union(table_map, typecolname, aliasname='p_union', cast_nulls=True):
    """Create a ``UNION`` statement used by a polymorphic mapper.

    See  :ref:`concrete_inheritance` for an example of how
    this is used.

    :param table_map: mapping of polymorphic identities to
     :class:`.Table` objects.
    :param typecolname: string name of a "discriminator" column, which will be
     derived from the query, producing the polymorphic identity for each row.  If
     ``None``, no polymorphic discriminator is generated.
    :param aliasname: name of the :func:`~sqlalchemy.sql.expression.alias()`
     construct generated.
    :param cast_nulls: if True, non-existent columns, which are represented as labeled
     NULLs, will be passed into CAST.   This is a legacy behavior that is problematic
     on some backends such as Oracle - in which case it can be set to False.

    """

    colnames = util.OrderedSet()
    colnamemaps = {}
    types = {}
    for key in list(table_map.keys()):
        table = table_map[key]

        # mysql doesnt like selecting from a select;
        # make it an alias of the select
        if isinstance(table, sql.Select):
            table = table.alias()
            table_map[key] = table

        m = {}
        for c in table.c:
            colnames.add(c.key)
            m[c.key] = c
            types[c.key] = c.type
        colnamemaps[table] = m

    def col(name, table):
        try:
            return colnamemaps[table][name]
        except KeyError:
            if cast_nulls:
                return sql.cast(sql.null(), types[name]).label(name)
            else:
                return sql.type_coerce(sql.null(), types[name]).label(name)

    result = []
    for type, table in table_map.items():
        if typecolname is not None:
            result.append(
                    sql.select([col(name, table) for name in colnames] +
                    [sql.literal_column(sql_util._quote_ddl_expr(type)).
                            label(typecolname)],
                             from_obj=[table]))
        else:
            result.append(sql.select([col(name, table) for name in colnames],
                                     from_obj=[table]))
    return sql.union_all(*result).alias(aliasname)
Пример #8
0
def auto_describes(docid):
    rty = db.relationship_types.alias()
    ide1 = db.identifiers.alias()
    ide2 = db.identifiers.alias()
    doc = db.documents.alias()
    pac = db.packages.alias()
    pfi = db.packages_files.alias()
    one = (
        select([
            doc.c.document_id,
            ide1.c.identifier_id        .label('left_identifier_id'),
            rty.c.relationship_type_id,
            ide2.c.identifier_id        .label('right_identifier_id'),
            ])
        .select_from(
            doc
            .join(pac, doc.c.package_id == pac.c.package_id)
            .join(ide1,
                (doc.c.document_id == ide1.c.document_id) &
                (doc.c.document_namespace_id == ide1.c.document_namespace_id)
                )
            .join(ide2,
                (pac.c.package_id == ide2.c.package_id) &
                (doc.c.document_namespace_id == ide2.c.document_namespace_id)
                )
            .join(rty, rty.c.name == 'DESCRIBES')
            )
        .where(doc.c.document_id == docid)
        )
    two = (
        select([
            doc.c.document_id,
            ide1.c.identifier_id        .label('left_identifier_id'),
            rty.c.relationship_type_id,
            ide2.c.identifier_id        .label('right_identifier_id'),
            ])
        .select_from(
            doc
            .join(pac, doc.c.package_id == pac.c.package_id)
            .join(pfi, pac.c.package_id == pfi.c.package_id, isouter=True)
            .join(ide1,
                (doc.c.document_id == ide1.c.document_id) &
                (doc.c.document_namespace_id == ide1.c.document_namespace_id)
                )
            .join(ide2,
                (pfi.c.package_file_id == ide2.c.package_file_id) &
                (doc.c.document_namespace_id == ide2.c.document_namespace_id)
                )
            .join(rty, rty.c.name == 'DESCRIBES')
            )
        .where(doc.c.document_id == docid)
        )
    return union_all(one, two)
Пример #9
0
 def getElementTable(self,
                     *,
                     dataId: Optional[DataId] = None) -> FromClause:
     # Docstring inherited from DimensionRecordStorage.getElementTable.
     tables = [
         link.getElementTable(dataId) for link in self._chain
         if link.matches(dataId)
     ]
     if len(tables) == 0:
         raise RuntimeError(
             f"No matching table for {self.element.name}, {dataId}.")
     elif len(tables) == 1:
         return tables[0]
     else:
         return union_all(*tables)
def polymorphic_union(table_map, typecolname, aliasname='p_union'):
    """Create a ``UNION`` statement used by a polymorphic mapper.

    See  :ref:`concrete_inheritance` for an example of how
    this is used.
    """

    colnames = set()
    colnamemaps = {}
    types = {}
    for key in table_map.keys():
        table = table_map[key]

        # mysql doesnt like selecting from a select;
        # make it an alias of the select
        if isinstance(table, sql.Select):
            table = table.alias()
            table_map[key] = table

        m = {}
        for c in table.c:
            colnames.add(c.key)
            m[c.key] = c
            types[c.key] = c.type
        colnamemaps[table] = m

    def col(name, table):
        try:
            return colnamemaps[table][name]
        except KeyError:
            return sql.cast(sql.null(), types[name]).label(name)

    result = []
    for type, table in table_map.iteritems():
        if typecolname is not None:
            result.append(
                sql.select([col(name, table) for name in colnames] + [
                    sql.literal_column(
                        sql_util._quote_ddl_expr(type)).label(typecolname)
                ],
                           from_obj=[table]))
        else:
            result.append(
                sql.select([col(name, table) for name in colnames],
                           from_obj=[table]))
    return sql.union_all(*result).alias(aliasname)
Пример #11
0
    def ListPendingReferencesToWrite(self, request, context):
        with session_scope() as session:
            q1 = (select(literal(True), HostRequest).outerjoin(
                Reference,
                and_(
                    Reference.host_request_id == HostRequest.conversation_id,
                    Reference.from_user_id == context.user_id,
                ),
            ).where_users_column_visible(
                context,
                HostRequest.host_user_id).where(Reference.id == None).where(
                    HostRequest.can_write_reference).where(
                        HostRequest.surfer_user_id == context.user_id))

            q2 = (select(literal(False), HostRequest).outerjoin(
                Reference,
                and_(
                    Reference.host_request_id == HostRequest.conversation_id,
                    Reference.from_user_id == context.user_id,
                ),
            ).where_users_column_visible(
                context,
                HostRequest.surfer_user_id).where(Reference.id == None).where(
                    HostRequest.can_write_reference).where(
                        HostRequest.host_user_id == context.user_id))

            union = union_all(q1, q2).order_by(
                HostRequest.end_time_to_write_reference.asc()).subquery()
            union = select(union.c[0].label("surfed"),
                           aliased(HostRequest, union))
            host_request_references = session.execute(union).all()

            return references_pb2.ListPendingReferencesToWriteRes(
                pending_references=[
                    references_pb2.AvailableWriteReferenceType(
                        host_request_id=host_request.conversation_id,
                        reference_type=reftype2api[
                            ReferenceType.surfed if surfed else ReferenceType.
                            hosted],
                        time_expires=Timestamp_from_datetime(
                            host_request.end_time_to_write_reference),
                    ) for surfed, host_request in host_request_references
                ], )
Пример #12
0
    def _query_weekly_average(self, table, tables):
        # First see if we can find 6 days prior for a full week
        idx = tables.index(table)

        # For Python list math, 6 has 6 numbers before it as zero index
        # based, so 6 or larger is needed
        if idx < 6:
            return None

        # Get our weekly set together
        # Note that we add one to idx since list splicing needs one higher than
        # the index for right-side inclusive
        week_tables = union_all(*[
            select([tbl]).where(tbl.c.message == "Ack")
            for tbl in tables[idx-6:idx+1]
        ])

        # Calculate channels per user for the past week
        chans_per_user = select([
            week_tables.c.uaid_hash,
            func.count(func.distinct(week_tables.c.channel_id)).label("count")
        ]).\
            group_by(week_tables.c.uaid_hash)

        # Rank them into ntiles
        ranked = select([
            chans_per_user.c.uaid_hash,
            chans_per_user.c.count,
            func.ntile(100).over(order_by=text("count ASC")).label("rank"),
        ])

        # Remove the bottom/upper 5%, get sum/count for avg
        weekly_channels_stats = select([
            func.sum(ranked.c.count),
            func.count(ranked.c.uaid_hash),
        ]).\
            where(ranked.c.rank > 5).\
            where(ranked.c.rank <= 95)
        sums, count = self._conn.execute(weekly_channels_stats).fetchone()
        weekly_avg = Decimal(sums) / Decimal(count)
        return weekly_avg
Пример #13
0
def get_activity_query(user_id=None, session_id=None, test_id=None):
    # pylint: disable=no-member
    from .models import Activity, Comment, User

    _filter = functools.partial(_apply_filters, user_id=user_id, session_id=session_id, test_id=test_id)

    comments = select([
        literal_column("('comment:' || comment.id)").label('id'),
        literal_column(str(ACTION_COMMENTED)).label('action'),
        Comment.user_id.label('user_id'),
        Comment.session_id.label('session_id'),
        Comment.test_id.label('test_id'),
        Comment.timestamp.label('timestamp'),
        Comment.comment.label('text'),
        (User.first_name + ' ' + User.last_name).label('user_name'),
        User.email.label('user_email'),
    ]).select_from(Comment.__table__.join(User, User.id == Comment.user_id))

    comments = _filter(Comment, comments)

    activity = select([
        literal_column("('activity:' || activity.id)").label('id'),
        Activity.action.label('action'),
        Activity.user_id.label('user_id'),
        Activity.session_id.label('session_id'),
        Activity.test_id.label('test_id'),
        Activity.timestamp.label('timestamp'),
        literal_column("NULL").label('text'),
        (User.first_name + ' ' + User.last_name).label('user_name'),
        User.email.label('user_email'),
    ]).select_from(Activity.__table__.join(User, User.id == Activity.user_id))

    activity = _filter(Activity, activity)

    u = union_all(comments, activity).alias('u')

    return select([u]).order_by(u.c.timestamp)
Пример #14
0
def get_old_messages_backend(request, user_profile,
                             anchor = REQ(converter=int),
                             num_before = REQ(converter=to_non_negative_int),
                             num_after = REQ(converter=to_non_negative_int),
                             narrow = REQ('narrow', converter=narrow_parameter, default=None),
                             use_first_unread_anchor = REQ(default=False, converter=ujson.loads),
                             apply_markdown=REQ(default=True,
                                                converter=ujson.loads)):
    # type: (HttpRequest, UserProfile, int, int, int, Optional[List[Dict[str, Any]]], bool, bool) -> HttpResponse
    include_history = ok_to_include_history(narrow, user_profile.realm)

    if include_history and not use_first_unread_anchor:
        query = select([column("id").label("message_id")], None, table("zerver_message"))
        inner_msg_id_col = literal_column("zerver_message.id")
    elif narrow is None:
        query = select([column("message_id"), column("flags")],
                       column("user_profile_id") == literal(user_profile.id),
                       table("zerver_usermessage"))
        inner_msg_id_col = column("message_id")
    else:
        # TODO: Don't do this join if we're not doing a search
        query = select([column("message_id"), column("flags")],
                       column("user_profile_id") == literal(user_profile.id),
                       join(table("zerver_usermessage"), table("zerver_message"),
                            literal_column("zerver_usermessage.message_id") ==
                            literal_column("zerver_message.id")))
        inner_msg_id_col = column("message_id")

    num_extra_messages = 1
    is_search = False

    if narrow is not None:
        # Add some metadata to our logging data for narrows
        verbose_operators = []
        for term in narrow:
            if term['operator'] == "is":
                verbose_operators.append("is:" + term['operand'])
            else:
                verbose_operators.append(term['operator'])
        request._log_data['extra'] = "[%s]" % (",".join(verbose_operators),)

        # Build the query for the narrow
        num_extra_messages = 0
        builder = NarrowBuilder(user_profile, inner_msg_id_col)
        search_term = None # type: Optional[Dict[str, Any]]
        for term in narrow:
            if term['operator'] == 'search':
                if not is_search:
                    search_term = term
                    query = query.column(column("subject")).column(column("rendered_content"))
                    is_search = True
                else:
                    # Join the search operators if there are multiple of them
                    search_term['operand'] += ' ' + term['operand']
            else:
                query = builder.add_term(query, term)
        if is_search:
            query = builder.add_term(query, search_term)

    # We add 1 to the number of messages requested if no narrow was
    # specified to ensure that the resulting list always contains the
    # anchor message.  If a narrow was specified, the anchor message
    # might not match the narrow anyway.
    if num_after != 0:
        num_after += num_extra_messages
    else:
        num_before += num_extra_messages

    sa_conn = get_sqlalchemy_connection()
    if use_first_unread_anchor:
        condition = column("flags").op("&")(UserMessage.flags.read.mask) == 0

        # We exclude messages on muted topics when finding the first unread
        # message in this narrow
        muting_conditions = exclude_muting_conditions(user_profile, narrow)
        if muting_conditions:
            condition = and_(condition, *muting_conditions)

        first_unread_query = query.where(condition)
        first_unread_query = first_unread_query.order_by(inner_msg_id_col.asc()).limit(1)
        first_unread_result = list(sa_conn.execute(first_unread_query).fetchall())
        if len(first_unread_result) > 0:
            anchor = first_unread_result[0][0]
        else:
            anchor = LARGER_THAN_MAX_MESSAGE_ID

    before_query = None
    after_query = None
    if num_before != 0:
        before_anchor = anchor
        if num_after != 0:
            # Don't include the anchor in both the before query and the after query
            before_anchor = anchor - 1
        before_query = query.where(inner_msg_id_col <= before_anchor) \
                            .order_by(inner_msg_id_col.desc()).limit(num_before)
    if num_after != 0:
        after_query = query.where(inner_msg_id_col >= anchor) \
                           .order_by(inner_msg_id_col.asc()).limit(num_after)

    if anchor == LARGER_THAN_MAX_MESSAGE_ID:
        # There's no need for an after_query if we're targeting just the target message.
        after_query = None

    if before_query is not None:
        if after_query is not None:
            query = union_all(before_query.self_group(), after_query.self_group())
        else:
            query = before_query
    elif after_query is not None:
        query = after_query
    else:
        # This can happen when a narrow is specified.
        query = query.where(inner_msg_id_col == anchor)

    main_query = alias(query)
    query = select(main_query.c, None, main_query).order_by(column("message_id").asc())
    # This is a hack to tag the query we use for testing
    query = query.prefix_with("/* get_old_messages */")
    query_result = list(sa_conn.execute(query).fetchall())

    # The following is a little messy, but ensures that the code paths
    # are similar regardless of the value of include_history.  The
    # 'user_messages' dictionary maps each message to the user's
    # UserMessage object for that message, which we will attach to the
    # rendered message dict before returning it.  We attempt to
    # bulk-fetch rendered message dicts from remote cache using the
    # 'messages' list.
    search_fields = dict() # type: Dict[int, Dict[str, Text]]
    message_ids = [] # type: List[int]
    user_message_flags = {} # type: Dict[int, List[str]]
    if include_history:
        message_ids = [row[0] for row in query_result]

        # TODO: This could be done with an outer join instead of two queries
        user_message_flags = dict((user_message.message_id, user_message.flags_list()) for user_message in
                                  UserMessage.objects.filter(user_profile=user_profile,
                                                             message__id__in=message_ids))
        for row in query_result:
            message_id = row[0]
            if user_message_flags.get(message_id) is None:
                user_message_flags[message_id] = ["read", "historical"]
            if is_search:
                (_, subject, rendered_content, content_matches, subject_matches) = row
                search_fields[message_id] = get_search_fields(rendered_content, subject,
                                                              content_matches, subject_matches)
    else:
        for row in query_result:
            message_id = row[0]
            flags = row[1]
            user_message_flags[message_id] = parse_usermessage_flags(flags)

            message_ids.append(message_id)

            if is_search:
                (_, _, subject, rendered_content, content_matches, subject_matches) = row
                search_fields[message_id] = get_search_fields(rendered_content, subject,
                                                              content_matches, subject_matches)

    cache_transformer = lambda row: MessageDict.build_dict_from_raw_db_row(row, apply_markdown)
    id_fetcher = lambda row: row['id']

    message_dicts = generic_bulk_cached_fetch(lambda message_id: to_dict_cache_key_id(message_id, apply_markdown),
                                              Message.get_raw_db_rows,
                                              message_ids,
                                              id_fetcher=id_fetcher,
                                              cache_transformer=cache_transformer,
                                              extractor=extract_message_dict,
                                              setter=stringify_message_dict)

    message_list = []
    for message_id in message_ids:
        msg_dict = message_dicts[message_id]
        msg_dict.update({"flags": user_message_flags[message_id]})
        msg_dict.update(search_fields.get(message_id, {}))
        message_list.append(msg_dict)

    statsd.incr('loaded_old_messages', len(message_list))
    ret = {'messages': message_list,
           "result": "success",
           "msg": ""}
    return json_success(ret)
Пример #15
0
def process_send_reference_reminders(payload):
    """
    Sends out reminders to write references after hosting/staying
    """
    logger.info(f"Sending out reference reminder emails")

    # Keep this in chronological order!
    reference_reminder_schedule = [
        # (number, timedelta before we stop being able to write a ref, text for how long they have left to write the ref)
        # the end time to write a reference is supposed to be midnight in the host's timezone
        # 8 pm ish on the last day of the stay
        (1, timedelta(days=15) - timedelta(hours=20), "14 days"),
        # 2 pm ish a week after stay
        (2, timedelta(days=8) - timedelta(hours=14), "7 days"),
        # 10 am ish 3 days before end of time to write ref
        (3, timedelta(days=4) - timedelta(hours=10), "3 days"),
    ]

    with session_scope() as session:
        # iterate the reminders in backwards order, so if we missed out on one we don't send duplicates
        for reminder_no, reminder_time, reminder_text in reversed(reference_reminder_schedule):
            user = aliased(User)
            other_user = aliased(User)
            # surfers needing to write a ref
            q1 = (
                select(literal(True), HostRequest, user, other_user)
                .join(user, user.id == HostRequest.surfer_user_id)
                .join(other_user, other_user.id == HostRequest.host_user_id)
                .outerjoin(
                    Reference,
                    and_(
                        Reference.host_request_id == HostRequest.conversation_id,
                        # if no reference is found in this join, then the surfer has not written a ref
                        Reference.from_user_id == HostRequest.surfer_user_id,
                    ),
                )
                .where(user.is_visible)
                .where(other_user.is_visible)
                .where(Reference.id == None)
                .where(HostRequest.can_write_reference)
                .where(HostRequest.surfer_sent_reference_reminders < reminder_no)
                .where(HostRequest.end_time_to_write_reference - reminder_time < now())
            )

            # hosts needing to write a ref
            q2 = (
                select(literal(False), HostRequest, user, other_user)
                .join(user, user.id == HostRequest.host_user_id)
                .join(other_user, other_user.id == HostRequest.surfer_user_id)
                .outerjoin(
                    Reference,
                    and_(
                        Reference.host_request_id == HostRequest.conversation_id,
                        # if no reference is found in this join, then the host has not written a ref
                        Reference.from_user_id == HostRequest.host_user_id,
                    ),
                )
                .where(user.is_visible)
                .where(other_user.is_visible)
                .where(Reference.id == None)
                .where(HostRequest.can_write_reference)
                .where(HostRequest.host_sent_reference_reminders < reminder_no)
                .where(HostRequest.end_time_to_write_reference - reminder_time < now())
            )

            union = union_all(q1, q2).subquery()
            union = select(
                union.c[0].label("surfed"),
                aliased(HostRequest, union),
                aliased(user, union),
                aliased(other_user, union),
            )
            reference_reminders = session.execute(union).all()

            for surfed, host_request, user, other_user in reference_reminders:
                # checked in sql
                assert user.is_visible
                if not are_blocked(session, user.id, other_user.id):
                    send_reference_reminder_email(user, other_user, host_request, surfed, reminder_text)
                    if surfed:
                        host_request.surfer_sent_reference_reminders = reminder_no
                    else:
                        host_request.host_sent_reference_reminders = reminder_no
                    session.commit()
Пример #16
0
    def get(self):
        parser = RequestParser(trim=True)
        parser.add_argument('page', type=int, default=DEFAULT_PAGE)
        parser.add_argument('pageSize', type=int, default=DEFAULT_PAGE_SIZE)

        parser.add_argument('id', type=str)
        parser.add_argument('accountChangeType', type=str)
        parser.add_argument('isAcdemen', type=int)
        parser.add_argument('timeLower', type=int)
        parser.add_argument('timeUpper', type=int)
        parser.add_argument('amountLower', type=float)
        parser.add_argument('amountUpper', type=float)
        parser.add_argument('rechargeid', type=str)
        parser.add_argument('orderId', type=str)
        parser.add_argument('memberUsername', type=str)
        parser.add_argument('memberLevelConfig', type=str)
        parser.add_argument('memberParentUsername', type=str)
        args = parser.parse_args(strict=True)
        args['accountChangeType'] = args['accountChangeType'].split(',')
        criterion = set()
        criterion.add(Member.isTsetPLay != 1)
        if args['id']:
            criterion.add(
                MemberAccountChangeRecord.id.in_(args['id'].split(',')))

        if args['isAcdemen'] is not None:
            if args['isAcdemen'] == 1:
                criterion.add(
                    MemberAccountChangeRecord.isAcdemen == args['isAcdemen'])
                if '6' in args['accountChangeType']:
                    args['accountChangeType'].remove('6')
            else:
                criterion.add(
                    or_(MemberAccountChangeRecord.isAcdemen.is_(None),
                        MemberAccountChangeRecord.isAcdemen == 0))
        if args['rechargeid']:
            criterion.add(
                MemberAccountChangeRecord.rechargeid == args['rechargeid'])
        if args['accountChangeType']:
            criterion.add(
                MemberAccountChangeRecord.accountChangeType.in_(
                    args['accountChangeType']))
        if args['timeLower']:
            criterion.add(MemberAccountChangeRecord.time >= args['timeLower'])
        if args['timeUpper']:
            criterion.add(MemberAccountChangeRecord.time <= args['timeUpper'] +
                          SECONDS_PER_DAY)
        # if args['amountUpper']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountUpper'])
        # if args['amountLower']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountLower'])
        if args['amountUpper'] is not None and args['amountLower'] is not None:
            if args['amountUpper'] >= args['amountLower']:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])
        elif args['amountUpper'] is not None:
            if args['amountUpper'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
        elif args['amountLower'] is not None:
            if args['amountLower'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])

        if args['orderId']:
            criterion.add(MemberAccountChangeRecord.orderId == args['orderId'])
        if args['memberUsername']:
            criterion.add(
                Member.username.in_(args['memberUsername'].split(',')))
        if args['memberLevelConfig']:
            criterion.add(
                Member.levelConfig.in_(args['memberLevelConfig'].split(',')))
        # 		if args['memberLevelConfig'] is None:
        # 			criterion.add(Member.levelConfig == '')
        if args['memberParentUsername']:
            parent = Member.query.filter(
                Member.username == args['memberParentUsername']).first()
            if parent:
                criterion.add(Member.parent == parent.id)
        query = db.session.query(
            func.sum(MemberAccountChangeRecord.amount).label('sumAmount'))

        query = query.outerjoin(
            Member, MemberAccountChangeRecord.memberId == Member.id)
        query = query.outerjoin(MemberLevel,
                                Member.levelConfig == MemberLevel.id)
        query = query.outerjoin(User,
                                User.id == MemberAccountChangeRecord.actionUID)
        result_query = []
        if args['accountChangeType']:
            if '6' in args['accountChangeType']:
                query_yule = db.session.query(
                    func.sum(
                        EntertainmentCityBetsDetail.Profit).label('sumAmount'))
                query_yule = query_yule.outerjoin(
                    Member,
                    EntertainmentCityBetsDetail.PlayerName == Member.username)
                query_yule = query_yule.outerjoin(
                    MemberLevel, Member.levelConfig == MemberLevel.id)
                criterion_query = set()
                if args['id']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.id.in_(
                            args['id'].split(',')))
                if args['rechargeid']:
                    criterion_query.add(EntertainmentCityBetsDetail.BillNo ==
                                        args['rechargeid'])
                if args['timeLower']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime >=
                                        args['timeLower'])
                if args['timeUpper']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime <=
                                        args['timeUpper'] + SECONDS_PER_DAY)
                # if args['amountLower']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount >= args['amountLower'])
                # if args['amountUpper']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount <= args['amountUpper'])
                if args['amountUpper'] is not None and args[
                        'amountLower'] is not None:
                    if args['amountUpper'] >= args['amountLower']:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])
                elif args['amountUpper'] is not None:
                    if args['amountUpper'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                elif args['amountLower'] is not None:
                    if args['amountLower'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])

                if args['orderId']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.BillNo == args['orderId'])
                if args['memberUsername']:
                    criterion_query.add(
                        Member.username.in_(args['memberUsername'].split(',')))
                if args['memberLevelConfig']:
                    criterion_query.add(
                        Member.levelConfig.in_(
                            args['memberLevelConfig'].split(',')))
                # 				if args['memberLevelConfig'] is None:
                # 					criterion_query.add(Member.levelConfig == '')
                if args['memberParentUsername']:
                    parent = Member.query.filter(
                        Member.username ==
                        args['memberParentUsername']).first()
                    if parent:
                        criterion_query.add(Member.parent == parent.id)
                query = query.filter(*criterion)
                query_yule = query_yule.filter(*criterion_query)
                # union_all 拼接数据
                result = union_all(query, query_yule)

                # 将拼接好的数据重新命名,变成一个新的查询表
                user_alias = aliased(result, name='user_alias')
                user_alias = db.session.query(func.sum(
                    user_alias.c.sumAmount)).all()[0][0]
                return {'success': True, 'data': user_alias}
        user_alias = query.filter(*criterion).all()[0][0]
        return {'success': True, 'data': user_alias}
Пример #17
0
    def get(self):
        parser = RequestParser(trim=True)
        parser.add_argument('page', type=int, default=DEFAULT_PAGE)
        parser.add_argument('pageSize', type=int, default=DEFAULT_PAGE_SIZE)

        parser.add_argument('id', type=str)
        parser.add_argument('accountChangeType', type=str)
        parser.add_argument('isAcdemen', type=int)
        parser.add_argument('timeLower', type=int)
        parser.add_argument('timeUpper', type=int)
        parser.add_argument('amountLower', type=float)
        parser.add_argument('amountUpper', type=float)
        parser.add_argument('rechargeid', type=str)
        parser.add_argument('orderId', type=str)
        parser.add_argument('memberUsername', type=str)
        parser.add_argument('memberLevelConfig', type=str)
        parser.add_argument('memberParentUsername', type=str)
        args = parser.parse_args(strict=True)
        args['accountChangeType'] = args['accountChangeType'].split(',')
        criterion = set()
        criterion.add(Member.isTsetPLay != 1)
        if args['id']:
            criterion.add(
                MemberAccountChangeRecord.id.in_(args['id'].split(',')))

        if args['isAcdemen'] is not None:
            if args['isAcdemen'] == 1:
                criterion.add(
                    MemberAccountChangeRecord.isAcdemen == args['isAcdemen'])
                if '6' in args['accountChangeType']:
                    args['accountChangeType'].remove('6')
            else:
                criterion.add(
                    or_(MemberAccountChangeRecord.isAcdemen.is_(None),
                        MemberAccountChangeRecord.isAcdemen == 0))
        if args['rechargeid']:
            criterion.add(
                MemberAccountChangeRecord.rechargeid == args['rechargeid'])
        if args['accountChangeType']:
            criterion.add(
                MemberAccountChangeRecord.accountChangeType.in_(
                    args['accountChangeType']))
        if args['timeLower']:
            criterion.add(MemberAccountChangeRecord.time >= args['timeLower'])
        if args['timeUpper']:
            criterion.add(MemberAccountChangeRecord.time <= args['timeUpper'] +
                          SECONDS_PER_DAY)
        # if args['amountUpper']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountUpper'])
        # if args['amountLower']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountLower'])
        if args['amountUpper'] is not None and args['amountLower'] is not None:
            if args['amountUpper'] >= args['amountLower']:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])
        elif args['amountUpper'] is not None:
            if args['amountUpper'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
        elif args['amountLower'] is not None:
            if args['amountLower'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])

        if args['orderId']:
            criterion.add(MemberAccountChangeRecord.orderId == args['orderId'])
        if args['memberUsername']:
            criterion.add(
                Member.username.in_(args['memberUsername'].split(',')))
        if args['memberLevelConfig']:
            criterion.add(
                Member.levelConfig.in_(args['memberLevelConfig'].split(',')))
# 		if args['memberLevelConfig'] is None:
# 			criterion.add(Member.levelConfig == '')
        if args['memberParentUsername']:
            parent = Member.query.filter(
                Member.username == args['memberParentUsername']).first()
            if parent:
                criterion.add(Member.parent == parent.id)
        query = db.session.query(
            MemberAccountChangeRecord.id.label('id'),
            MemberAccountChangeRecord.orderId.label('orderId'),
            MemberAccountChangeRecord.time.label('time'),
            MemberAccountChangeRecord.accountChangeType.label(
                'accountChangeType'),
            MemberAccountChangeRecord.info.label('accountChangeTypesName'),
            MemberAccountChangeRecord.amount.label('amount'),
            MemberAccountChangeRecord.rechargeid.label('rechargeid'),
            MemberAccountChangeRecord.memberBalance.label('balanceAfter'),
            literal(0).label('balanceBefore'),
            MemberAccountChangeRecord.memberFrozenBalance.label(
                'frozenBalanceBefore'),
            literal('KK').label('eccode'), Member.id.label('memberId'),
            Member.username.label('username'), Member.type.label('memberType'),
            MemberLevel.levelName.label('levelName'),
            MemberLevel.id.label('levelId'),
            User.username.label('OperatorName')).order_by(
                MemberAccountChangeRecord.time.desc())

        query = query.outerjoin(
            Member, MemberAccountChangeRecord.memberId == Member.id)
        query = query.outerjoin(MemberLevel,
                                Member.levelConfig == MemberLevel.id)
        query = query.outerjoin(User,
                                User.id == MemberAccountChangeRecord.actionUID)
        result_query = []
        if args['accountChangeType']:
            if '6' in args['accountChangeType']:
                query_yule = db.session.query(
                    EntertainmentCityBetsDetail.id.label('id'),
                    EntertainmentCityBetsDetail.BillNo.label('orderId'),
                    EntertainmentCityBetsDetail.BetTime.label('time'),
                    literal(6).label('accountChangeType'),
                    EntertainmentCityBetsDetail.Remark.label(
                        'accountChangeTypesName'),
                    EntertainmentCityBetsDetail.Profit.label('amount'),
                    EntertainmentCityBetsDetail.BillNo.label('rechargeid'),
                    EntertainmentCityBetsDetail.Balance.label('balanceAfter'),
                    literal(0).label('balanceBefore'),
                    EntertainmentCityBetsDetail.CusAccount.label(
                        'frozenBalanceBefore'),
                    EntertainmentCityBetsDetail.ECCode.label('eccode'),
                    Member.id.label('memberId'),
                    Member.username.label('username'),
                    Member.type.label('memberType'),
                    MemberLevel.levelName.label('levelName'),
                    MemberLevel.id.label('levelId'),
                    literal('').label('OperatorName')).order_by(
                        EntertainmentCityBetsDetail.BetTime.desc())
                query_yule = query_yule.outerjoin(
                    Member,
                    EntertainmentCityBetsDetail.PlayerName == Member.username)
                query_yule = query_yule.outerjoin(
                    MemberLevel, Member.levelConfig == MemberLevel.id)
                criterion_query = set()
                if args['id']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.id.in_(
                            args['id'].split(',')))
                if args['rechargeid']:
                    criterion_query.add(EntertainmentCityBetsDetail.BillNo ==
                                        args['rechargeid'])
                if args['timeLower']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime >=
                                        args['timeLower'])
                if args['timeUpper']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime <=
                                        args['timeUpper'] + SECONDS_PER_DAY)
                # if args['amountLower']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount >= args['amountLower'])
                # if args['amountUpper']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount <= args['amountUpper'])
                if args['amountUpper'] is not None and args[
                        'amountLower'] is not None:
                    if args['amountUpper'] >= args['amountLower']:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])
                elif args['amountUpper'] is not None:
                    if args['amountUpper'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                elif args['amountLower'] is not None:
                    if args['amountLower'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])

                if args['orderId']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.BillNo == args['orderId'])
                if args['memberUsername']:
                    criterion_query.add(
                        Member.username.in_(args['memberUsername'].split(',')))
                if args['memberLevelConfig']:
                    criterion_query.add(
                        Member.levelConfig.in_(
                            args['memberLevelConfig'].split(',')))
# 				if args['memberLevelConfig'] is None:
# 					criterion_query.add(Member.levelConfig == '')
                if args['memberParentUsername']:
                    parent = Member.query.filter(
                        Member.username ==
                        args['memberParentUsername']).first()
                    if parent:
                        criterion_query.add(Member.parent == parent.id)
                query = query.filter(*criterion)
                query_yule = query_yule.filter(*criterion_query)
                # union_all 拼接数据
                result = union_all(query, query_yule)

                # 将拼接好的数据重新命名,变成一个新的查询表
                user_alias = aliased(result, name='user_alias')
                user_alias = db.session.query(user_alias).order_by(
                    user_alias.c.time.desc())

                pagination = paginate_one(user_alias, args['page'],
                                          args['pageSize'])
                pagination = convert_pagination(pagination)

                total_amount = 0
                for item in pagination.items:
                    total_amount += item['amount']
                    item[
                        'accountChangeTypeName'] = MemberAccountChangeTypes.d.get(
                            item['accountChangeType'])

# 					if item['accountChangeType'] in [100001, 100002]:
# 						item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore']
#
# 					if item['accountChangeType'] in [200001]:
# 						item['balanceAfter'] = item['balanceBefore'] - item['amount']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore'] + item['amount']
#
# 					if item['accountChangeType'] in [200002]:
# 						item['balanceAfter'] = item['balanceBefore']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore'] - item['amount']
#
# 					if item['accountChangeType'] in [200003]:
# 						item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore'] - item['amount']
#
# 					if item['accountChangeType'] in [900001]:
# 						item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore']
#
# 					if item['accountChangeType'] in [900002]:
# 						item['balanceAfter'] = item['balanceBefore'] - item['amount']
# 						item['frozenBalanceAfter'] = item['frozenBalanceBefore']
                return make_response_from_pagination(pagination,
                                                     totalAmount=total_amount)
        pagination = paginate(query, criterion, args['page'], args['pageSize'])
        pagination = convert_pagination(pagination)
        total_amount = 0
        for item in pagination.items:
            total_amount += item['amount']


# 			item['accountChangeTypeName'] = MemberAccountChangeTypes.d.get(item['accountChangeType'])
#
# 			if item['accountChangeType'] in [100001, 100002]:
# 				item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore']
#
# 			if item['accountChangeType'] in [200001]:
# 				item['balanceAfter'] = item['balanceBefore'] - item['amount']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore'] + item['amount']
#
# 			if item['accountChangeType'] in [200002]:
# 				item['balanceAfter'] = item['balanceBefore']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore'] - item['amount']
#
# 			if item['accountChangeType'] in [200003]:
# 				item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore'] - item['amount']
#
# 			if item['accountChangeType'] in [900001]:
# 				item['balanceAfter'] = item['balanceBefore'] + item['amount']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore']
#
# 			if item['accountChangeType'] in [900002]:
# 				item['balanceAfter'] = item['balanceBefore'] - item['amount']
# 				item['frozenBalanceAfter'] = item['frozenBalanceBefore']
        return make_response_from_pagination(pagination,
                                             totalAmount=total_amount)
Пример #18
0
    def get(self):
        parser = RequestParser(trim=True)
        parser.add_argument('page', type=int, default=DEFAULT_PAGE)
        parser.add_argument('pageSize', type=int, default=DEFAULT_PAGE_SIZE)

        parser.add_argument('id', type=str)
        parser.add_argument('accountChangeType', type=str)
        parser.add_argument('isAcdemen', type=int)
        parser.add_argument('timeLower', type=int)
        parser.add_argument('timeUpper', type=int)
        parser.add_argument('amountLower', type=float)
        parser.add_argument('amountUpper', type=float)
        parser.add_argument('rechargeid', type=str)
        parser.add_argument('orderId', type=str)
        parser.add_argument('memberUsername', type=str)
        parser.add_argument('memberLevelConfig', type=str)
        parser.add_argument('memberParentUsername', type=str)
        args = parser.parse_args(strict=True)
        args['accountChangeType'] = args['accountChangeType'].split(',')
        criterion = set()
        criterion.add(Member.isTsetPLay != 1)
        if args['id']:
            criterion.add(
                MemberAccountChangeRecord.id.in_(args['id'].split(',')))

        if args['isAcdemen'] is not None:
            if args['isAcdemen'] == 1:
                criterion.add(
                    MemberAccountChangeRecord.isAcdemen == args['isAcdemen'])
                if '6' in args['accountChangeType']:
                    args['accountChangeType'].remove('6')
            else:
                criterion.add(
                    or_(MemberAccountChangeRecord.isAcdemen.is_(None),
                        MemberAccountChangeRecord.isAcdemen == 0))
        if args['rechargeid']:
            criterion.add(
                MemberAccountChangeRecord.rechargeid == args['rechargeid'])
        if args['accountChangeType']:
            criterion.add(
                MemberAccountChangeRecord.accountChangeType.in_(
                    args['accountChangeType']))
        if args['timeLower']:
            criterion.add(MemberAccountChangeRecord.time >= args['timeLower'])
        if args['timeUpper']:
            criterion.add(MemberAccountChangeRecord.time <= args['timeUpper'] +
                          SECONDS_PER_DAY)
        # if args['amountUpper']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountUpper'])
        # if args['amountLower']:
        # 	criterion.add(MemberAccountChangeRecord.amount <= args['amountLower'])
        if args['amountUpper'] is not None and args['amountLower'] is not None:
            if args['amountUpper'] >= args['amountLower']:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])
        elif args['amountUpper'] is not None:
            if args['amountUpper'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountUpper'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountUpper'])
        elif args['amountLower'] is not None:
            if args['amountLower'] >= 0:
                criterion.add(
                    MemberAccountChangeRecord.amount >= args['amountLower'])
            else:
                criterion.add(
                    MemberAccountChangeRecord.amount <= args['amountLower'])

        if args['orderId']:
            criterion.add(MemberAccountChangeRecord.orderId == args['orderId'])
        if args['memberUsername']:
            criterion.add(
                Member.username.in_(args['memberUsername'].split(',')))
        if args['memberLevelConfig']:
            criterion.add(
                Member.levelConfig.in_(args['memberLevelConfig'].split(',')))
        # 		if args['memberLevelConfig'] is None:
        # 			criterion.add(Member.levelConfig == '')
        if args['memberParentUsername']:
            parent = Member.query.filter(
                Member.username == args['memberParentUsername']).first()
            if parent:
                criterion.add(Member.parent == parent.id)
        query = db.session.query(
            MemberAccountChangeRecord.orderId.label('orderId'),
            Member.username.label('username'),
            MemberLevel.levelName.label('levelName'),
            MemberAccountChangeRecord.time.label('time'),
            MemberAccountChangeRecord.info.label('accountChangeTypesName'),
            MemberAccountChangeRecord.amount.label('amount'),
            MemberAccountChangeRecord.memberBalance.label('memberBalance'),
            User.username.label('OperatorName'),
            User.username.label('dealName'),
        ).order_by(MemberAccountChangeRecord.time.desc())
        query = query.outerjoin(
            Member, MemberAccountChangeRecord.memberId == Member.id)
        query = query.outerjoin(MemberLevel,
                                Member.levelConfig == MemberLevel.id)
        query = query.outerjoin(User,
                                User.id == MemberAccountChangeRecord.actionUID)
        result_query = []
        if args['accountChangeType']:
            if '6' in args['accountChangeType']:
                query_yule = db.session.query(
                    EntertainmentCityBetsDetail.BillNo.label('orderId'),
                    Member.username.label('username'),
                    MemberLevel.levelName.label('levelName'),
                    EntertainmentCityBetsDetail.BetTime.label('time'),
                    func.concat(EntertainmentCityBetsDetail.ECCode,
                                '-派彩').label('accountChangeTypesName'),
                    EntertainmentCityBetsDetail.Profit.label('amount'),
                    EntertainmentCityBetsDetail.Balance.label('memberBalance'),
                    literal('').label('OperatorName'),
                    literal('').label('dealName'),
                ).order_by(EntertainmentCityBetsDetail.BetTime.desc())
                query_yule = query_yule.outerjoin(
                    Member,
                    EntertainmentCityBetsDetail.PlayerName == Member.username)
                query_yule = query_yule.outerjoin(
                    MemberLevel, Member.levelConfig == MemberLevel.id)
                criterion_query = set()
                if args['id']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.id.in_(
                            args['id'].split(',')))
                if args['rechargeid']:
                    criterion_query.add(EntertainmentCityBetsDetail.BillNo ==
                                        args['rechargeid'])
                if args['timeLower']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime >=
                                        args['timeLower'])
                if args['timeUpper']:
                    criterion_query.add(EntertainmentCityBetsDetail.BetTime <=
                                        args['timeUpper'] + SECONDS_PER_DAY)
                # if args['amountLower']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount >= args['amountLower'])
                # if args['amountUpper']:
                # 	criterion_query.add(EntertainmentCityBetsDetail.ValidBetAmount <= args['amountUpper'])
                if args['amountUpper'] is not None and args[
                        'amountLower'] is not None:
                    if args['amountUpper'] >= args['amountLower']:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])
                elif args['amountUpper'] is not None:
                    if args['amountUpper'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountUpper'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountUpper'])
                elif args['amountLower'] is not None:
                    if args['amountLower'] >= 0:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount >=
                            args['amountLower'])
                    else:
                        criterion_query.add(
                            EntertainmentCityBetsDetail.ValidBetAmount <=
                            args['amountLower'])

                if args['orderId']:
                    criterion_query.add(
                        EntertainmentCityBetsDetail.BillNo == args['orderId'])
                if args['memberUsername']:
                    criterion_query.add(
                        Member.username.in_(args['memberUsername'].split(',')))
                if args['memberLevelConfig']:
                    criterion_query.add(
                        Member.levelConfig.in_(
                            args['memberLevelConfig'].split(',')))
                # 				if args['memberLevelConfig'] is None:
                # 					criterion_query.add(Member.levelConfig == '')
                if args['memberParentUsername']:
                    parent = Member.query.filter(
                        Member.username ==
                        args['memberParentUsername']).first()
                    if parent:
                        criterion_query.add(Member.parent == parent.id)
                query = query.filter(*criterion)
                query_yule = query_yule.filter(*criterion_query)
                # union_all 拼接数据
                result = union_all(query, query_yule)

                # 将拼接好的数据重新命名,变成一个新的查询表
                user_alias = aliased(result, name='user_alias')
                user_alias = db.session.query(user_alias).order_by(
                    user_alias.c.time.desc()).all()
                results = []
                for item in user_alias:
                    results.append(
                        (item[0], item[1], item[2], changeData_str(item[3]),
                         item[4], item[5], item[6], item[7], item[8]))
                title = [
                    '单号', '帐号', '会员等级', '操作时间', '信息', '金额', '小计', '操作人员',
                    '处理人员', '其他'
                ]
                workbook = Workbook()
                worksheet = workbook.active

                worksheet.append(title)
                worksheet.column_dimensions['A'].width = 20
                worksheet.column_dimensions['B'].width = 10
                worksheet.column_dimensions['C'].width = 18
                worksheet.column_dimensions['D'].width = 20
                worksheet.column_dimensions['E'].width = 15
                worksheet.column_dimensions['F'].width = 8
                worksheet.column_dimensions['G'].width = 8
                worksheet.column_dimensions['H'].width = 8
                worksheet.column_dimensions['I'].width = 8
                worksheet.column_dimensions['J'].width = 8
                for result in results:
                    worksheet.append(result)
                filename = '交易记录-' + str(int(time.time())) + '.xlsx'
                workbook.save(os.path.join(current_app.static_folder,
                                           filename))
                return make_response([{
                    'success': True,
                    'resultFilename': filename,
                }])

        query = query.filter(*criterion).all()
        results = []
        for item in query:
            results.append((item[0], item[1], item[2], changeData_str(item[3]),
                            item[4], item[5], item[6], item[7], item[8]))
        title = [
            '单号', '帐号', '会员等级', '操作时间', '信息', '金额', '小计', '操作人员', '处理人员', '其他'
        ]
        workbook = Workbook()
        worksheet = workbook.active

        worksheet.append(title)
        worksheet.column_dimensions['A'].width = 20
        worksheet.column_dimensions['B'].width = 10
        worksheet.column_dimensions['C'].width = 18
        worksheet.column_dimensions['D'].width = 20
        worksheet.column_dimensions['E'].width = 15
        worksheet.column_dimensions['F'].width = 8
        worksheet.column_dimensions['G'].width = 8
        worksheet.column_dimensions['H'].width = 8
        worksheet.column_dimensions['I'].width = 8
        worksheet.column_dimensions['J'].width = 8
        for result in results:
            worksheet.append(result)
        filename = '交易记录-' + str(int(time.time())) + '.xlsx'
        workbook.save(os.path.join(current_app.static_folder, filename))
        return make_response([{
            'success': True,
            'resultFilename': filename,
        }])
Пример #19
0
def limit_query_to_range(query: Query,
                         num_before: int,
                         num_after: int,
                         anchor: int,
                         anchored_to_left: bool,
                         anchored_to_right: bool,
                         id_col: ColumnElement,
                         first_visible_message_id: int) -> Query:
    '''
    This code is actually generic enough that we could move it to a
    library, but our only caller for now is message search.
    '''
    need_before_query = (not anchored_to_left) and (num_before > 0)
    need_after_query = (not anchored_to_right) and (num_after > 0)

    need_both_sides = need_before_query and need_after_query

    # The semantics of our flags are as follows:
    #
    # num_after = number of rows < anchor
    # num_after = number of rows > anchor
    #
    # But we also want the row where id == anchor (if it exists),
    # and we don't want to union up to 3 queries.  So in some cases
    # we do things like `after_limit = num_after + 1` to grab the
    # anchor row in the "after" query.
    #
    # Note that in some cases, if the anchor row isn't found, we
    # actually may fetch an extra row at one of the extremes.
    if need_both_sides:
        before_anchor = anchor - 1
        after_anchor = max(anchor, first_visible_message_id)
        before_limit = num_before
        after_limit = num_after + 1
    elif need_before_query:
        before_anchor = anchor
        before_limit = num_before
        if not anchored_to_right:
            before_limit += 1
    elif need_after_query:
        after_anchor = max(anchor, first_visible_message_id)
        after_limit = num_after + 1

    if need_before_query:
        before_query = query

        if not anchored_to_right:
            before_query = before_query.where(id_col <= before_anchor)

        before_query = before_query.order_by(id_col.desc())
        before_query = before_query.limit(before_limit)

    if need_after_query:
        after_query = query

        if not anchored_to_left:
            after_query = after_query.where(id_col >= after_anchor)

        after_query = after_query.order_by(id_col.asc())
        after_query = after_query.limit(after_limit)

    if need_both_sides:
        query = union_all(before_query.self_group(), after_query.self_group())
    elif need_before_query:
        query = before_query
    elif need_after_query:
        query = after_query
    else:
        # If we don't have either a before_query or after_query, it's because
        # some combination of num_before/num_after/anchor are zero or
        # use_first_unread_anchor logic found no unread messages.
        #
        # The most likely reason is somebody is doing an id search, so searching
        # for something like `message_id = 42` is exactly what we want.  In other
        # cases, which could possibly be buggy API clients, at least we will
        # return at most one row here.
        query = query.where(id_col == anchor)

    return query
Пример #20
0
cn.execute(select([func.count(students.c.id)])).fetchall()

# The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained −
cn.execute(select([func.max(students.c.id)])).fetchall()
#find min value
cn.execute(select([func.min(students.c.id)])).fetchall()
#  find Average
cn.execute(select([func.avg(students.c.id)])).fetchall()


# =========================SQLAlchemy Core - Using Set Operations==========================
'''union()''':
from sqlalchemy.sql import union,union_all,except_,intersect
u1 = union(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
conn.execute(u1).fetchone()
ua = union_all(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.email_add.like('*****@*****.**')))
conn.execute(ua).fetchall()
ue = except_(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.postal_add.like('%Pune')))
result = conn.execute(ue)
ints = intersect(addresses.select().where(addresses.c.email_add.like('*****@*****.**')), addresses.select().where(addresses.c.postal_add.like('%Pune')))
result = conn.execute(ints)

#sum of febbonacy
a ,b = 0,1
max_value = 4000000
fib_value = []
sum_value = 0

for i in range(100000):

    if b !=1 and sum(fib_value) < max_value :