Python DBSession.connection方法代码示例

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_shots_children_task_type(request):
    """returns the Task Types defined under the Shot container
    """

    sql_query = """select
        "SimpleEntities".id as type_id,
        "SimpleEntities".name as type_name
    from "SimpleEntities"
    join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
    join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
    join "Shots" on "Tasks".parent_id = "Shots".id
    group by "SimpleEntities".id, "SimpleEntities".name
    order by "SimpleEntities".name"""

    result = DBSession.connection().execute(sql_query)

    return_data = [{"id": r[0], "name": r[1]} for r in result.fetchall()]

    content_range = "%s-%s/%s"

    type_count = len(return_data)
    content_range = content_range % (0, type_count - 1, type_count)

    logger.debug("content_range : %s" % content_range)

    resp = Response(json_body=return_data)
    resp.content_range = content_range
    return resp

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_assets_type_task_types(request):
    """returns the Task Types defined under the Asset container
    """

    type_id = request.matchdict.get('t_id', None)

    logger.debug('type_id %s'% type_id)


    sql_query = """select
        "SimpleEntities".id as type_id,
        "SimpleEntities".name as type_name
    from "SimpleEntities"
    join "SimpleEntities" as "Task_SimpleEntities" on "SimpleEntities".id = "Task_SimpleEntities".type_id
    join "Tasks" on "Task_SimpleEntities".id = "Tasks".id
    join "Assets" on "Tasks".parent_id = "Assets".id
    join "SimpleEntities" as "Assets_SimpleEntities" on "Assets_SimpleEntities".id = "Assets".id

    %(where_condition)s

    group by "SimpleEntities".id, "SimpleEntities".name
    order by "SimpleEntities".name"""


    where_condition = ''

    if type_id:
        where_condition = 'where "Assets_SimpleEntities".type_id = %(type_id)s'%{'type_id': type_id}

    sql_query = sql_query %{'where_condition':where_condition}



    result = DBSession.connection().execute(sql_query)

    return_data = [
        {
            'id': r[0],
            'name': r[1]

        }
        for r in result.fetchall()
    ]

    content_range = '%s-%s/%s'

    type_count = len(return_data)
    content_range = content_range % (0, type_count - 1, type_count)

    logger.debug('content_range : %s' % content_range)

    resp = Response(
        json_body=return_data
    )
    resp.content_range = content_range
    return resp

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_entity_notes(request):
    """RESTful version of getting all notes of a task
    """
    logger.debug('get_entity_notes is running')

    entity_id = request.matchdict.get('id', -1)
    entity = Entity.query.filter(Entity.id == entity_id).first()

    if not entity:
        transaction.abort()
        return Response('There is no entity with id: %s' % entity_id, 500)


    sql_query = """select  "User_SimpleEntities".id as user_id,
                "User_SimpleEntities".name,
                "Users_Thumbnail_Links".full_path,
                "Notes_SimpleEntities".id as note_id,
                "Notes_SimpleEntities".description,
                "Notes_SimpleEntities".date_created,
                "Notes_Types_SimpleEntities".id,
                "Notes_Types_SimpleEntities".name,
                "Notes_Types_SimpleEntities".html_class

        from "Notes"
        join "SimpleEntities" as "Notes_SimpleEntities" on "Notes_SimpleEntities".id = "Notes".id
        left outer join "SimpleEntities" as "Notes_Types_SimpleEntities" on "Notes_Types_SimpleEntities".id = "Notes_SimpleEntities".type_id
        join "SimpleEntities" as "User_SimpleEntities" on "Notes_SimpleEntities".created_by_id = "User_SimpleEntities".id
        join "Links" as "Users_Thumbnail_Links" on "Users_Thumbnail_Links".id = "User_SimpleEntities".thumbnail_id
        where "Notes".entity_id = %(entity_id)s
        order by "Notes_SimpleEntities".date_created desc"""


    sql_query = sql_query % {'entity_id': entity_id}

    result = DBSession.connection().execute(sql_query)

    return_data = [
        {
            'created_by_id': r[0],
            'created_by_name': r[1],
            'created_by_thumbnail': r[2],
            'note_id': r[3],
            'content': r[4],
            'created_date': milliseconds_since_epoch(r[5]),
            'note_type_id': r[6],
            'note_type_name': r[7],
            'note_type_color': r[8]
        }
        for r in result.fetchall()
    ]



    return return_data

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_project_sequences_count(request):
    """returns the count of sequences in a project
    """
    project_id = request.matchdict.get('id', -1)

    sql_query = """select
        count(1)
    from "Sequences"
        join "Tasks" on "Sequences".id = "Tasks".id
    where "Tasks".project_id = %s""" % project_id

    return DBSession.connection().execute(sql_query).fetchone()[0]

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_shots_count(request):
    """returns the count of Shots in the given Project
    """
    project_id = request.matchdict.get("id", -1)

    sql_query = (
        """select
        count(1)
    from "Shots"
        join "Tasks" on "Shots".id = "Tasks".id
    where "Tasks".project_id = %s"""
        % project_id
    )

    return DBSession.connection().execute(sql_query).fetchone()[0]

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_entity_references_count(request):
    """called when the count of references to Project/Task/Asset/Shot/Sequence
    is requested
    """
    entity_id = request.matchdict.get('id', -1)
    entity = Entity.query.filter(Entity.id == entity_id).first()
    logger.debug('asking references for entity: %s' % entity)

    # using Raw SQL queries here to fasten things up quite a bit and also do
    # some fancy queries like getting all the references of tasks of a project
    # also with their tags
    sql_query = """
    select count(*) from (
        select
            "Links".id
        from "Task_References"
        join (
            with recursive parent_ids(id, parent_id, project_id) as (
                select task.id, task.parent_id, task.project_id from "Tasks" task
            union all
                select task.id, parent.parent_id, task.project_id
                from "Tasks" task, parent_ids parent
                where task.parent_id = parent.id
            )
            select
                distinct parent_ids.id as id --, coalesce(parent_ids.parent_id, parent_ids.project_id) as parent_id
                from parent_ids

                where parent_ids.id = %(id)s or parent_ids.parent_id = %(id)s or parent_ids.project_id = %(id)s -- show also children references

                group by parent_ids.id, parent_id, project_id
                order by parent_ids.id
        ) as child_tasks on child_tasks.id = "Task_References".task_id
        join "Links" on "Task_References".link_id = "Links".id
        join "SimpleEntities" on "Links".id = "SimpleEntities".id
        join "Links" as "Thumbnails" on "SimpleEntities".thumbnail_id = "Thumbnails".id
        join "Entity_Tags" on "Links".id = "Entity_Tags".entity_id
        join "Tags" on "Entity_Tags".tag_id = "Tags".id
        join "SimpleEntities" as "SimpleEntities_Tags" on "Tags".id = "SimpleEntities_Tags".id
        join "SimpleEntities" as "SimpleEntities_Tasks" on "Task_References".task_id = "SimpleEntities_Tasks".id
        group by "Links".id
    ) as data
    """ % {'id': entity_id}

    result = DBSession.connection().execute(sql_query)
    return result.fetchone()[0]

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_task_reviewers(request):
    """RESTful version of getting all reviews of a task
    """
    logger.debug('get_task_reviewers is running')

    task_id = request.matchdict.get('id', -1)
    task = Task.query.filter(Task.id == task_id).first()

    if not task:
        transaction.abort()
        return Response('There is no task with id: %s' % task_id, 500)

    sql_query = """
        select
            "Reviewers".name as reviewers_name,
            "Reviewers".id as reviewers_id

        from "Reviews"
            join "Tasks" as "Review_Tasks" on "Review_Tasks".id = "Reviews".task_id
            join "SimpleEntities" as "Reviewers" on "Reviewers".id = "Reviews".reviewer_id

        %(where_conditions)s

        group by "Reviewers".id, "Reviewers".name
    """

    where_conditions = """where "Review_Tasks".id = %(task_id)s""" % {
        'task_id': task.id
    }

    logger.debug('where_conditions %s ' % where_conditions)

    sql_query = sql_query % {'where_conditions': where_conditions}

    result = DBSession.connection().execute(sql_query)

    return_data = [
        {
            'reviewer_name': r[0],
            'reviewer_id': r[1]
        }
        for r in result.fetchall()
    ]

    return return_data

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_assets_types(request):
    """returns the Asset Types
    """


    sql_query ="""select
     "Assets_Types_SimpleEntities".id,
     "Assets_Types_SimpleEntities".name

     from "Assets"

     join "SimpleEntities" as "Assets_SimpleEntities" on "Assets_SimpleEntities".id = "Assets".id
     join "SimpleEntities" as "Assets_Types_SimpleEntities" on "Assets_Types_SimpleEntities".id = "Assets_SimpleEntities".type_id

     group by
        "Assets_Types_SimpleEntities".name,
        "Assets_Types_SimpleEntities".id
     order by "Assets_Types_SimpleEntities".name
     """


    result = DBSession.connection().execute(sql_query)

    return_data = [
        {
            'asset_type_id': r[0],
            'asset_type_name': r[1]

        }
        for r in result.fetchall()
    ]

    content_range = '%s-%s/%s'

    type_count = len(return_data)
    content_range = content_range % (0, type_count - 1, type_count)

    logger.debug('content_range : %s' % content_range)

    resp = Response(
        json_body=return_data
    )
    resp.content_range = content_range
    return resp

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_project_tasks_today(request):
    """returns the project lead as a json data
    """
    project_id = request.matchdict.get('id', -1)
    action = request.matchdict.get('action', -1)

    today = datetime.date.today()
    start = datetime.time(0, 0)
    end = datetime.time(23, 59, 59)

    start_of_today = datetime.datetime.combine(today, start)
    end_of_today = datetime.datetime.combine(today, end)

    start = time.time()

    sql_query = """select "Tasks".id,
           "SimpleEntities".name,
           array_agg(distinct("SimpleEntities_Resource".id)),
           array_agg(distinct("SimpleEntities_Resource".name)),
           "SimpleEntities_Status".name,
           "SimpleEntities_Status".html_class,
           (coalesce("Task_TimeLogs".duration, 0.0))::float /
           ("Tasks".schedule_timing * (case "Tasks".schedule_unit
                when 'min' then 60
                when 'h' then %(working_seconds_per_hour)s
                when 'd' then %(working_seconds_per_day)s
                when 'w' then %(working_seconds_per_week)s
                when 'm' then %(working_seconds_per_month)s
                when 'y' then %(working_seconds_per_year)s
                else 0
            end)) * 100.0 as percent_complete
    from "Tasks"
        join "SimpleEntities" on "Tasks".id = "SimpleEntities".id
        join "Task_Resources" on "Tasks".id = "Task_Resources".task_id
        join "SimpleEntities" as "SimpleEntities_Resource" on "Task_Resources".resource_id = "SimpleEntities_Resource".id
        join "Statuses" on "Tasks".status_id = "Statuses".id
        join "SimpleEntities" as "SimpleEntities_Status" on "Statuses".id = "SimpleEntities_Status".id
        left outer join (
            select
                "TimeLogs".task_id,
                extract(epoch from sum("TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC')) as duration
            from "TimeLogs"
            group by task_id
        ) as "Task_TimeLogs" on "Task_TimeLogs".task_id = "Tasks".id
        left outer join "TimeLogs" on "Tasks".id = "TimeLogs".task_id
        """

    if action == 'progress':
        sql_query += """where
            "Tasks".computed_start::timestamp AT TIME ZONE 'UTC' < '%(end_of_today)s' and
            "Tasks".computed_end::timestamp AT TIME ZONE 'UTC' > '%(start_of_today)s'"""
    elif action == 'end':
        sql_query += """where
            "Tasks".computed_end::timestamp AT TIME ZONE 'UTC' > '%(start_of_today)s' and
            "Tasks".computed_end::timestamp AT TIME ZONE 'UTC' <= '%(end_of_today)s'
            """

    sql_query += """   and "Tasks".project_id = %(project_id)s
        group by "Tasks".id,
             "SimpleEntities".name,
             "SimpleEntities_Status".name,
             "SimpleEntities_Status".html_class,
             "Task_TimeLogs".duration,
             "Tasks".schedule_timing,
             "Tasks".schedule_unit
        """

    studio = Studio.query.first()
    assert isinstance(studio, Studio)

    ws_per_hour = 3600
    ws_per_day = studio.daily_working_hours * ws_per_hour
    ws_per_week = studio.weekly_working_days * ws_per_day
    ws_per_month = ws_per_week * 4
    ws_per_year = studio.yearly_working_days * ws_per_day

    sql_query = sql_query % {
        'project_id': project_id,
        'start_of_today': start_of_today.strftime('%Y-%m-%d %H:%M:%S'),
        'end_of_today': end_of_today.strftime('%Y-%m-%d %H:%M:%S'),
        'working_seconds_per_hour': ws_per_hour,
        'working_seconds_per_day': ws_per_day,
        'working_seconds_per_week': ws_per_week,
        'working_seconds_per_month': ws_per_month,
        'working_seconds_per_year': ws_per_year
    }

    logger.debug('sql_query : %s' % sql_query)

    result = DBSession.connection().execute(sql_query)

    data = [
        {
            'task_id': r[0],
            'task_name': r[1],
            'resources': [
                '<a href="/users/%(id)s/view">%(name)s</a>' % {
                    'id': r[2][i],
                    'name': r[3][i]
                } for i in range(len(r[2]))
#.........这里部分代码省略.........

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_entity_references(request):
    """called when the references to Project/Task/Asset/Shot/Sequence is
    requested
    """
    entity_id = request.matchdict.get('id', -1)
    entity = Entity.query.filter(Entity.id == entity_id).first()
    logger.debug('asking references for entity: %s' % entity)

    offset = request.params.get('offset')
    limit = request.params.get('limit')

    # using Raw SQL queries here to fasten things up quite a bit and also do
    # some fancy queries like getting all the references of tasks of a project
    # also with their tags
    sql_query = """
    -- select all links assigned to a project tasks or assigned to a task and its children
    select
        "Links".id,
        "Links".full_path,
        "Links".original_filename,
        "Thumbnails".full_path as "thumbnail_full_path",
        array_agg("SimpleEntities_Tags".name) as tags,
        array_agg("Task_References".task_id) as entity_id,
        array_agg("SimpleEntities_Tasks".name) as task_name,
        array_agg("SimpleEntities_Tasks".entity_type) as entity_type
    from "Task_References"
    join (
        with recursive parent_ids(id, parent_id, project_id) as (
            select task.id, task.parent_id, task.project_id from "Tasks" task
        union all
            select task.id, parent.parent_id, task.project_id
            from "Tasks" task, parent_ids parent
            where task.parent_id = parent.id
        )
        select
            distinct parent_ids.id as id
            from parent_ids

            where parent_ids.id = %(id)s or parent_ids.parent_id = %(id)s or parent_ids.project_id = %(id)s -- show also children references

            group by parent_ids.id, parent_id, project_id
            order by parent_ids.id
    ) as child_tasks on child_tasks.id = "Task_References".task_id
    join "Links" on "Task_References".link_id = "Links".id
    join "SimpleEntities" on "Links".id = "SimpleEntities".id
    join "Links" as "Thumbnails" on "SimpleEntities".thumbnail_id = "Thumbnails".id
    join "Entity_Tags" on "Links".id = "Entity_Tags".entity_id
    join "Tags" on "Entity_Tags".tag_id = "Tags".id
    join "SimpleEntities" as "SimpleEntities_Tags" on "Tags".id = "SimpleEntities_Tags".id
    join "SimpleEntities" as "SimpleEntities_Tasks" on "Task_References".task_id = "SimpleEntities_Tasks".id
    group by "Links".id, "Thumbnails".full_path, "Links".full_path,
             "Links".original_filename
    order by "Links".id
    """ % {'id': entity_id}

    if offset and limit:
        sql_query += "offset %s limit %s" % (offset, limit)

    time_time = time.time
    db_start = time_time()
    result = DBSession.connection().execute(sql_query)
    db_end = time_time()
    db_time = db_end - db_start

    python_start = time_time()
    return_val = [
        {
            'id': r[0],
            'full_path': r[1],
            'original_filename': r[2],
            'thumbnail_full_path': r[3],
            'tags': r[4],
            'entity_ids': r[5],
            'entity_names': r[6],
            'entity_types': r[7]
        } for r in result.fetchall()
    ]
    python_end = time_time()
    python_time = python_end - python_start

    logger.debug('get_entity_references took: %s seconds for %s rows' %
                 (python_end - db_start , len(return_val)))
    return return_val

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]

#.........这里部分代码省略.........
                        "TimeLogs".task_id,
                        extract(epoch from sum("TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC')) as duration
                    from "TimeLogs"
                    group by task_id
                ) as "Task_TimeLogs" on "Task_TimeLogs".task_id = "Tasks".id
        where "Tasks".project_id = %(project_id)s  %(where_conditions)s
        group by
            "Assets".id,
            "Asset_SimpleEntities".name,
            "Asset_SimpleEntities".description,
            "Links".full_path,
            "Distinct_Asset_Statuses".asset_status_code,
            "Distinct_Asset_Statuses".asset_status_html_class,
            "Assets_Types_SimpleEntities".name
        order by "Asset_SimpleEntities".name

    """

    where_conditions = ''

    if asset_type_id:
        where_conditions = """and "Assets_Types_SimpleEntities".id = %(asset_type_id)s""" %({'asset_type_id':asset_type_id})
    if asset_id:
        where_conditions = """and "Assets".id = %(asset_id)s""" %({'asset_id':asset_id})


    sql_query = sql_query % {'where_conditions':where_conditions, 'project_id':project_id}

    update_asset_permission = \
        PermissionChecker(request)('Update_Asset')
    delete_asset_permission = \
        PermissionChecker(request)('Delete_Asset')

    result = DBSession.connection().execute(sql_query)

    return_data = []

    for r in result.fetchall():
        r_data = {
            'id': r[0],
            'name': r[1],
            'description': r[2],
            'thumbnail_full_path': r[3] if r[3] else None,
            'status': r[4],
            'status_color': r[5],
            'update_asset_action': '/tasks/%s/update/dialog' % r[0]
                if update_asset_permission else None,
            'delete_asset_action': '/tasks/%s/delete/dialog' % r[0]
                if delete_asset_permission else None
        }
        task_types_names = r[6]
        task_ids = r[7]
        task_names = r[8]
        task_statuses = r[9]
        task_percent_complete = r[11]

        logger.debug('task_types_names %s ' % task_types_names)
        r_data['nulls'] = []

        for index1 in range(len(task_types_names)):

            if task_types_names[index1]:

                r_data[task_types_names[index1]]= []

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_users_count(request):
    """returns all users or one particular user from database
    """
    # if there is a simple flag, just return ids and names and login
    #simple = request.params.get('simple')

    # if there is an id it is probably a project
    entity_id = request.matchdict.get('id')

    entity_type = None
    if entity_id:
        sql_query = \
            'select entity_type from "SimpleEntities" where id=%s' % entity_id
        data = DBSession.connection().execute(sql_query).fetchone()
        entity_type = data[0] if data else None

    logger.debug('entity_id  : %s' % entity_id)
    logger.debug('entity_type: %s' % entity_type)

    if entity_id and entity_type not in ['Project', 'Department', 'Group', 'Task', 'User']:
        # there is no entity_type for that entity
        return []

    start = time.time()
    sql_query = """select
        count("Users".id)
    from "SimpleEntities"
    join "Users" on "SimpleEntities".id = "Users".id
    left outer join (
        select
            uid,
            array_agg(did) as dep_ids,
            array_agg(name) as dep_names
        from "User_Departments"
        join "SimpleEntities" on "User_Departments".did = "SimpleEntities".id
        group by uid
    ) as user_departments on user_departments.uid = "Users".id
    left outer join (
        select
            uid,
            array_agg(gid) as group_ids,
            array_agg(name) as group_names
        from "User_Groups"
        join "SimpleEntities" on "User_Groups".gid = "SimpleEntities".id
        group by uid
    ) as user_groups on user_groups.uid = "Users".id
    left outer join (
        select resource_id, count(task_id) as task_count from "Task_Resources" group by resource_id
    ) as tasks on tasks.resource_id = "Users".id
    left outer join (
        select
            owner_id,
            count("Tickets".id) as ticket_count
        from "Tickets"
        join "SimpleEntities" on "Tickets".status_id = "SimpleEntities".id
        where "SimpleEntities".name = 'New'
        group by owner_id, name
    ) as tickets on tickets.owner_id = "Users".id
    left outer join "Links" on "SimpleEntities".thumbnail_id = "Links".id
    """

    if entity_type == "Project":
        sql_query += """join "Project_Users" on "Users".id = "Project_Users".user_id
        where "Project_Users".project_id = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Department":
        sql_query += """join "User_Departments" on "Users".id = "User_Departments".uid
        where "User_Departments".did = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Group":
        sql_query += """join "User_Groups" on "Users".id = "User_Groups".uid
        where "User_Groups".gid = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Task":
        sql_query += """join "Task_Resources" on "Users".id = "Task_Resources".resource_id
        where "Task_Resources".task_id = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "User":
        sql_query += 'where "Users".id = %s' % entity_id

    return DBSession.connection().execute(sql_query).fetchone()[0]

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_time_logs(request):
    """returns all the Shots of the given Project
    """
    logger.debug('get_time_logs is running')
    entity_id = request.matchdict.get('id', -1)
    logger.debug('entity_id : %s' % entity_id)

    data = DBSession.connection().execute(
        'select entity_type from "SimpleEntities" where id=%s' % entity_id
    ).fetchone()

    entity_type = None
    if len(data):
        entity_type = data[0]

    logger.debug('entity_type : %s' % entity_type)

    sql_query = """select
        "TimeLogs".id,
        "TimeLogs".task_id,
        "SimpleEntities_Task".name,
        "SimpleEntities_Status".name,
        parent_names.parent_name,
        "TimeLogs".resource_id,
        "SimpleEntities_Resource".name,
        extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC' - "TimeLogs".start::timestamp AT TIME ZONE 'UTC') as total_seconds,
        extract(epoch from "TimeLogs".start::timestamp AT TIME ZONE 'UTC') * 1000 as start,
        extract(epoch from "TimeLogs".end::timestamp AT TIME ZONE 'UTC') * 1000 as end
    from "TimeLogs"
    join "Tasks" on "TimeLogs".task_id = "Tasks".id
    join "SimpleEntities" as "SimpleEntities_Task" on "Tasks".id = "SimpleEntities_Task".id
    join "SimpleEntities" as "SimpleEntities_Status" on "Tasks".status_id = "SimpleEntities_Status".id
    join "SimpleEntities" as "SimpleEntities_Resource" on "TimeLogs".resource_id = "SimpleEntities_Resource".id
    join (
        select
            parent_data.id,
            "SimpleEntities".name,
            array_to_string(array_agg(
                case
                    when "SimpleEntities_parent".entity_type = 'Project'
                    then "Projects".code
                    else "SimpleEntities_parent".name
                end),
                ' | '
            ) as parent_name
            from (
                with recursive parent_ids(id, parent_id, n) as (
                        select task.id, coalesce(task.parent_id, task.project_id), 0
                        from "Tasks" task
                    union
                        select task.id, parent.parent_id, parent.n + 1
                        from "Tasks" task, parent_ids parent
                        where task.parent_id = parent.id
                )
                select
                    parent_ids.id, parent_id as parent_id, parent_ids.n
                    from parent_ids
                    order by id, parent_ids.n desc
            ) as parent_data
            join "SimpleEntities" on "SimpleEntities".id = parent_data.id
            join "SimpleEntities" as "SimpleEntities_parent" on "SimpleEntities_parent".id = parent_data.parent_id
            left outer join "Projects" on parent_data.parent_id = "Projects".id
            group by parent_data.id, "SimpleEntities".name
    ) as parent_names on "TimeLogs".task_id = parent_names.id
    """

    if entity_type == u'User':
        sql_query += 'where "TimeLogs".resource_id = %s' % entity_id
    elif entity_type == u'Task':
        sql_query += 'where "TimeLogs".task_id = %s' % entity_id
    elif entity_type is None:
        return []

    result = DBSession.connection().execute(sql_query)

    start = time.time()
    data = [
        {
            'id': r[0],
            'entity_type': 'timelogs',
            'task_id': r[1],
            'task_name': r[2],
            'task_status': r[3],
            'parent_name': r[4],
            'resource_id': r[5],
            'resource_name': r[6],
            'duration': r[7],
            'start': r[8],
            'end': r[9],
            'className': 'label-important',
            'allDay': '0'
        } for r in result.fetchall()
    ]
    end = time.time()
    logger.debug('get_entity_time_logs took: %s seconds' % (end - start))
    return data

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_users(request):
    """returns all users or one particular user from database
    """
    # if there is a simple flag, just return ids and names and login
    #simple = request.params.get('simple')

    # if there is an id it is probably a project
    entity_id = request.matchdict.get('id')

    entity_type = None


    update_user_permission = PermissionChecker(request)('Update_User')
    delete_user_permission = PermissionChecker(request)('Delete_User')

    delete_user_action ='/users/%(id)s/delete/dialog'


    if entity_id:
        sql_query = \
            'select entity_type from "SimpleEntities" where id=%s' % entity_id
        data = DBSession.connection().execute(sql_query).fetchone()
        entity_type = data[0] if data else None
        delete_user_action ='/entities/%(id)s/%(entity_id)s/remove/dialog'

    logger.debug('entity_id  : %s' % entity_id)
    logger.debug('entity_type: %s' % entity_type)

    if entity_id and entity_type not in ['Project', 'Department', 'Group', 'Task', 'User']:
        # there is no entity_type for that entity
        return []

    start = time.time()
    sql_query = """select
        "Users".id,
        "SimpleEntities".name,
        "Users".login,
        "Users".email,
        user_departments."dep_ids",
        user_departments."dep_names",
        user_groups."group_ids",
        user_groups."group_names",
        tasks.task_count,
        tickets.ticket_count,
        "Links".full_path
    from "SimpleEntities"
    join "Users" on "SimpleEntities".id = "Users".id
    left outer join (
        select
            uid,
            array_agg(did) as dep_ids,
            array_agg(name) as dep_names
        from "User_Departments"
        join "SimpleEntities" on "User_Departments".did = "SimpleEntities".id
        group by uid
    ) as user_departments on user_departments.uid = "Users".id
    left outer join (
        select
            uid,
            array_agg(gid) as group_ids,
            array_agg(name) as group_names
        from "User_Groups"
        join "SimpleEntities" on "User_Groups".gid = "SimpleEntities".id
        group by uid
    ) as user_groups on user_groups.uid = "Users".id
    left outer join (
        select resource_id, count(task_id) as task_count from "Task_Resources" group by resource_id
    ) as tasks on tasks.resource_id = "Users".id
    left outer join (
        select
            owner_id,
            count("Tickets".id) as ticket_count
        from "Tickets"
        join "SimpleEntities" on "Tickets".status_id = "SimpleEntities".id
        where "SimpleEntities".name = 'New'
        group by owner_id, name
    ) as tickets on tickets.owner_id = "Users".id
    left outer join "Links" on "SimpleEntities".thumbnail_id = "Links".id
    """

    if entity_type == "Project":
        sql_query += """join "Project_Users" on "Users".id = "Project_Users".user_id
        where "Project_Users".project_id = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Department":
        sql_query += """join "User_Departments" on "Users".id = "User_Departments".uid
        where "User_Departments".did = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Group":
        sql_query += """join "User_Groups" on "Users".id = "User_Groups".uid
        where "User_Groups".gid = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "Task":
        sql_query += """join "Task_Resources" on "Users".id = "Task_Resources".resource_id
        where "Task_Resources".task_id = %(id)s
        """ % {'id': entity_id}
    elif entity_type == "User":
        sql_query += 'where "Users".id = %s' % entity_id

    sql_query += 'order by "SimpleEntities".name'
#.........这里部分代码省略.........

# 需要导入模块: from stalker.db import DBSession [as 别名]
# 或者: from stalker.db.DBSession import connection [as 别名]
def get_reviews(request, where_conditions):
    """TODO: add docstring
    """
    logger.debug('get_reviews is running')

    logged_in_user = get_logged_in_user(request)

    sql_query = """
    select
        "Reviews".review_number as review_number,
        "Reviews".id as review_id,
        "Reviews_Statuses".code as review_status_code,
        "Statuses_Simple_Entities".name as review_status_name,
        "Statuses_Simple_Entities".html_class as review_status_color,
        "Reviews".task_id as task_id,
        "ParentTasks".parent_names as task_name,
        "Review_Tasks".review_number as task_review_number,
        "Reviews".reviewer_id as reviewer_id,
        "Reviewers_SimpleEntities".name as reviewer_name,
        "Reviewers_SimpleEntities_Links".full_path as reviewer_thumbnail_path,
        array_agg("Reviewer_Departments_SimpleEntities".name) as reviewer_departments,
        extract(epoch from"Reviews_Simple_Entities".date_created::timestamp AT TIME ZONE 'UTC') * 1000 as date_created

    from "Reviews"
        join "SimpleEntities" as "Reviews_Simple_Entities" on "Reviews_Simple_Entities".id = "Reviews".id
        join "Tasks" as "Review_Tasks" on "Review_Tasks".id = "Reviews".task_id
        join "Statuses" as "Reviews_Statuses" on "Reviews_Statuses".id = "Reviews".status_id
        join "SimpleEntities" as "Statuses_Simple_Entities" on "Statuses_Simple_Entities".id = "Reviews".status_id
        join "SimpleEntities" as "Reviewers_SimpleEntities" on "Reviewers_SimpleEntities".id = "Reviews".reviewer_id
        join "User_Departments" as "Reviewers_Departments" on "Reviewers_Departments".uid = "Reviews".reviewer_id
        join "SimpleEntities" as "Reviewer_Departments_SimpleEntities" on "Reviewer_Departments_SimpleEntities".id = "Reviewers_Departments".did
        left join (%(tasks_hierarchical_name_table)s) as "ParentTasks" on "Review_Tasks".id = "ParentTasks".id

        left outer join "Links" as "Reviewers_SimpleEntities_Links" on "Reviewers_SimpleEntities_Links".id = "Reviewers_SimpleEntities".thumbnail_id

    %(where_conditions)s

    group by

        "Reviews".review_number,
        "Reviews".id,
        "Reviews_Statuses".code,
        "Reviews_Simple_Entities".date_created,
        "Statuses_Simple_Entities".name,
        "Statuses_Simple_Entities".html_class,
        "Reviews".task_id,
        "ParentTasks".parent_names,
        "Review_Tasks".review_number,
        "Reviews".reviewer_id,
        "Reviewers_SimpleEntities".name,
        "Reviewers_SimpleEntities_Links".full_path

    order by "Reviews_Simple_Entities".date_created desc
    """

    logger.debug('where_conditions: %s ' % where_conditions)

    sql_query = sql_query % {'where_conditions': where_conditions, 'tasks_hierarchical_name_table': query_of_tasks_hierarchical_name_table()}

    result = DBSession.connection().execute(sql_query)

    return_data = [
        {
            'review_number': r[0],
            'review_id': r[1],
            'review_status_code': r[2],
            'review_status_name': r[3],
            'review_status_color': r[4],
            'task_id': r[5],
            'task_name': r[6],
            'task_review_number': r[7],
            'reviewer_id': r[8],
            'reviewer_name': r[9],
            'reviewer_thumbnail_full_path':r[10],
            'reviewer_department':r[11],
            'date_created':r[12],
            'is_reviewer':'1' if logged_in_user.id == r[8] else None
        }
        for r in result.fetchall()
    ]




    return return_data