def table_view(request):
    can_i_edit = has_permission('edit', request.context, request)
    can_i_edit = isinstance(can_i_edit, ACLAllowed)
    user_id = authenticated_userid(request)

    dbsession = DBSession()
    card, user = None, None
    try:
        card = dbsession.query(Cards).filter_by(id=request.matchdict['id']).one()
        user = dbsession.query(User).filter_by(id=user_id).one() if can_i_edit else None
        result = card.as_json_dict()
    except NoResultFound:
        result = {'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено'}

    if not can_i_edit:
        # обнулим координаты перед показом
        result['lat'] = 0
        result['lon'] = 0

    if isinstance(has_permission('admin', request.context, request), ACLAllowed):
        is_editable = True
    else:
        is_editable = card.inserter == user.person_id if user else False

    dbsession.close()
    return {'data': result, 'editable': is_editable, 'success': True}

def taxon_tree(request):
    taxon_parent_id = request.matchdict['taxon_parent_id']

    parent_id = None
    if taxon_parent_id != 'root':
        parent_id = int(taxon_parent_id)

    with transaction.manager:
        dbsession = DBSession()
        parent_taxon = dbsession.query(Taxon).filter_by(id=parent_id).first()
        children_taxons = dbsession.query(Taxon).filter_by(parent_id=parent_id).all()

        if taxon_parent_id == 'root':
            parent_taxon_json = {
                'id': 'root',
                'name': 'Все таксоны'
            }
        else:
            parent_taxon_json = parent_taxon.as_json_dict()

        if taxon_parent_id == 'root':
            parent_taxon_json['id'] = 'root'

        children_taxons_json = []
        for taxon in children_taxons:
            children_taxons_json.append(_taxon_to_json(taxon))
        parent_taxon_json['children'] = children_taxons_json

    return parent_taxon_json

    def export_to_file(filename):
        from nextgisbio.utils.dump_to_file import dump

        dbsession = DBSession()
        redbook_species_db = dbsession.query(RedBook, RedBookSpecies, Taxon)\
            .join(RedBookSpecies, RedBook.id == RedBookSpecies.red_book_id)\
            .join(Taxon, RedBookSpecies.specie_id == Taxon.id)\
            .order_by(RedBook.id, RedBookSpecies.specie_id)\
            .all()
        dbsession.close()

        attribute_names = ['region', 'orig_name', 'lat_name', 'author', 'population', 'status', 'univ_status', 'year',
                           'bibl']

        objects_for_dump = [
            [
                o[1].region,
                o[1].orig_name,
                o[2].name,
                o[1].author,
                o[1].population,
                o[1].status,
                o[1].univ_status,
                o[1].year,
                o[0].name
            ] for o in redbook_species_db
        ]

        dump(filename, attribute_names, objects_for_dump, is_array=True)

def get_child_taxons_by_parent(request):
    parent_taxon_id = request.params['id']
    is_full_data = ('isFullData' in request.params) and request.params['isFullData'] == 'true'
    is_root_node_requsted = parent_taxon_id == '#'

    if is_root_node_requsted:
        parent_taxon_id = None
    else:
        parent_taxon_id = int(parent_taxon_id)

    dbsession = DBSession()
    children_taxons = dbsession.query(Taxon).filter_by(parent_id=parent_taxon_id).order_by(Taxon.name).all()
    dbsession.close()

    children_taxons_json = []
    for taxon in children_taxons:
        children_taxons_json.append(_taxon_to_jsTree_item(taxon, is_full_data))

    if is_root_node_requsted:
        result = _get_root_jsTree_item()
        result['children'] = children_taxons_json
    else:
        result = children_taxons_json

    return result

def s_ka_association_download(request):
    dbsession = DBSession()
    
    try:
        all = dbsession.query(square_keyarea_association).all()
    except DBAPIError:
        result = {'success': False, 'msg': 'Ошибка подключения к БД'}
    
    
    names = ['square_id', 'key_area_id']
    rows = [names, ]
    for row in all:
        data = []
        for name in names:
            data.append(try_encode(getattr(row, name)))
        rows.append(data)
        
    fname = tempfile.mktemp()
    try:
        file = open(fname, 'w')
        writer = csv.writer(file, delimiter = '\t')
        writer.writerows(rows)
        file.close()
        file = open(fname, 'r')
        data = file.read()
        resname = 'square_karea_association.csv'
    finally: # в любом случае удаляем файл
        os.remove(fname)

    dbsession.close()
    return Response(content_type="application/octet-stream", 
            content_disposition="attachment; filename=%s" % (resname, ), body=data)

def redbook_filter(request):
    dbsession = DBSession()

    query_str = request.params['name'].encode('utf-8').decode('utf-8')
    start = int(request.params['start'])
    count = int(request.params['count'])

    try:
        query_str_upper = query_str.upper()
        aFilter = u"UPPER({0}) LIKE '%{1}%'".format('name', query_str_upper)
        order_by_clauses = []
        order_by_clauses = dojo.parse_sort(request)

        red_books = dbsession.query(RedBook.id, RedBook.name)\
            .filter(aFilter)\
            .order_by(order_by_clauses)\
            .all()

        itemsPage = red_books[start:start + count]

    except DBAPIError:
        return {'success': False, 'msg': 'Ошибка подключения к БД'}

    rows = [{'id': id, 'name': name} for id, name in itemsPage]

    dbsession.close()
    return {'items': rows, 'success': True, 'numRows': len(itemsPage), 'identity': 'id'}

    def add_from_file(filename):
        '''
        Добавить данные в таблицу из файла с разделителями filename.
        
        Файл filename в формате csv, колонки:
        id  name    fullname    speciality  degree  organization    position    email   phone   address
        '''
        import transaction
        with transaction.manager:
            dbsession = DBSession()

            reader = csv.reader(open(filename), delimiter='\t')
            row = reader.next() # пропускаем заголовки
            records = [line for line in reader]

            for (id, name, fullname, speciality, degree, organization, position, email, phone, address) in records:
                person = Person(
                    name=name,
                    fullname=fullname,
                    speciality=speciality,
                    degree=degree,
                    organization=organization,
                    position=position,
                    email=email,
                    phone=phone,
                    address=address
                )
                dbsession.add(person)

def table_view(request):
    can_i_edit = has_permission('edit', request.context, request)
    can_i_edit = isinstance(can_i_edit, ACLAllowed)
    user_id = authenticated_userid(request)

    try:
        model = table_by_name(request.matchdict['table'])
    except KeyError:
        return {'success': False, 'msg': 'Ошибка: отсутствует таблица с указанным именем'}
        
    dbsession = DBSession()
    try:
        entity = dbsession.query(model).filter_by(id=request.matchdict['id']).one()
        user = dbsession.query(User).filter_by(id=user_id).one() if can_i_edit else None
        result = {'data': entity.as_json_dict(), 'success': True}
    except NoResultFound:
        result = {'success': False, 'msg': 'Результатов, соответствующих запросу, не найдено'}

    if hasattr(entity, 'inserter'):
        if isinstance(has_permission('admin', request.context, request), ACLAllowed):
            is_editable = True
        else:
            is_editable = entity.inserter == user.person_id if user else False
    else:
        is_editable = True
    result['editable'] = is_editable

    dbsession.close()
    return result

    def parent_taxons(taxon_id):
        """
        Возвращает родительские таксоны данного таксона.
        """

        dbsession = DBSession()
        qs = """
        WITH RECURSIVE subtree AS
            (
              SELECT * FROM taxon WHERE id=%s
              UNION ALL
              SELECT t.*
              FROM
                taxon AS t, subtree AS st
              WHERE  (st.parent_id = t.id)
            )
            SELECT * FROM subtree ;
        """ % (
            taxon_id,
        )

        taxons = dbsession.query(Taxon).from_statement(qs).all()
        # Отсортируем таксоны так, чтобы на первом месте списка шли царства, на последнем -- виды.
        taxons.sort(key=lambda x: TAXON_TYPES.index(x.taxon_type))

        return taxons

    def add_from_file(filename):
        """
        Добавить данные в таблицу таксонов из файла filename (разделители - табуляция).
        
        Файл filename в формате csv, колонки:
        id  parent_id   old_id  taxon_type  name    russian_name    author  source
        """
        import transaction

        with transaction.manager:
            dbsession = DBSession()

            reader = csv.reader(open(filename), delimiter="\t")
            row = reader.next()  # пропускаем заголовки
            records = [line for line in reader]
            for row in records:
                id, parent_id, old_id, taxon_type, name, russian_name, author, source = [
                    None if x == "" else x for x in row
                ]
                taxon = Taxon(
                    parent_id=parent_id,
                    old_id=old_id,
                    taxon_type=taxon_type,
                    name=name,
                    russian_name=russian_name,
                    author=author,
                    source=source,
                )
                dbsession.add(taxon)

def square(request):
    dbsession = DBSession()
    id = request.matchdict['id']
    square = dbsession.query(Squares).filter_by(id=id).one()
    key_areas = [{'id': s.id, 'name': s.name} for s in square.key_areas]

    dbsession.close()
    return {'id': square.id, 'key_areas': key_areas }

def get_synonyms(request):
    sessions = DBSession()
    taxon_id = int(request.matchdict['taxon_id'])
    synonyms = sessions.query(Synonym).filter_by(species_id=taxon_id).all()
    synonyms_json = [synonym.as_json_dict() for synonym in synonyms]
    count_synonyms = len(synonyms_json)
    request.response.headerlist = [('Content-Range', '{0}-{1}/{2}'.format(0, count_synonyms, count_synonyms))]
    return synonyms_json

def new_card(request):
    with transaction.manager:
        dbsession = DBSession()
        card = Cards()
        _update_card_attributes(card, dict(request.POST))
        card.added_date = datetime.now()
        card.edited_date = card.added_date
        dbsession.add(card)
    return {}

def squares_text(request):
    dbsession = DBSession()
    all = dbsession.query(Squares, sqlalchemy.func.st_asgeojson(Squares.geom.RAW)).all()
    squares = []
    for sq, geom in all:
        squares.append({'id': sq.id, 'geom': geom})

    dbsession.close()
    return {'squares' : squares}

def points_text(request):
    # Есть querystring, содержащее строку вида 'nodes=taxon_id1,taxon_id2').
    # Например, "nodes=taxon_1,taxon_5"
    # Это значит, что пользователь выбрал записи из таблицы taxon с id=1 и id=5.
    # Требуется вернуть карточки наблюдений соотв. таксонов
    # 
    # Граничный случай, когда нужно выбрать все карточки: nodes="root_"

    dbsession = DBSession()
    try:
        taxons = request.params['nodes']
    except KeyError:
        taxons = ''

    red_book_id = None
    if 'red_book' in request.params:
        red_book_id = int(request.params['red_book'])
        if red_book_id == -1:
            red_book_id = None

    can_i_edit = has_permission('edit', request.context, request)
    can_i_edit = isinstance(can_i_edit, ACLAllowed)

    if taxons:
        taxons = urllib.unquote(taxons)
        taxon_id = taxons.split(',')

        if 'root' in taxons:
            cards = dbsession.query(Cards, Taxon).join(Taxon).all()
        else:
            # Получим список видов-потомков выбранных таксонов и связанных с ними карточек
            subquery = TAXON_ID_QUERY % (", ".join([str(num) for num in taxon_id]), TAXON_TYPES[len(TAXON_TYPES) - 1])
            qs = """
            SELECT cards.id,cards.species,cards.lat,cards.lon, taxon.name FROM cards  
            INNER JOIN taxon ON cards.species = taxon.id
            %s WHERE """ % (
            'INNER JOIN red_books_species ON cards.species = red_books_species.specie_id' if red_book_id else '') \
                 + ((' red_books_species.red_book_id = ' + str(red_book_id) + ' AND ') if red_book_id else '') \
                 + ' cards.species IN (' + subquery + ');'
            cards = dbsession.query(Cards, Taxon).from_statement(qs).all()

        points = []
        for card, taxon in cards:
            id, spec_id, lat, lon = card.id, card.species, card.lat, card.lon
            name = taxon.name
            if lat and lon:
                if not can_i_edit:  # настоящие координаты показывать нельзя
                    # сдвинем координаты перед показом примерно на 10 км в случайном направлении
                    lat = lat + (random() - random()) / 7
                    lon = lon + (random() - random()) / 4

                points.append({'lat': lat, 'lon': lon, 'name': name, 'card_id': id, 'spec_id': spec_id})
    else:
        points = {}
    dbsession.close()
    return {'points': points}

 def put(self):
     new_synonym_dict = dict(self.request.POST)
     with transaction.manager:
         dbsession = DBSession()
         synonym = Synonym()
         for k, v in new_synonym_dict.items():
             if v == '': v = None
             if hasattr(synonym, k): setattr(synonym, k, v)
         synonym.species_id = int(self.request.matchdict['taxon_id'])
         dbsession.add(synonym)

def delete_anlist(request):
    annotation_id = request.matchdict['id']
    success = True
    try:
        with transaction.manager:
            dbsession = DBSession()
            annotation = dbsession.query(Annotation).filter_by(id=annotation_id).one()
            dbsession.delete(annotation)
    except:
        success = False
    return {'success': success}

def main(argv=sys.argv):
    if len(argv) != 2 and len(argv) != 3:
        usage(argv)
    config_uri = argv[1]
    setup_logging(config_uri)
    settings = get_appsettings(config_uri)
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    dir_name = dump_data()
    if len(argv) == 3 and argv[2] == '--make-id-start-0':
        id_verificator.start(dir_name)

def delete_card(request):
    card_id = request.matchdict['id']
    success = True
    try:
        with transaction.manager:
            dbsession = DBSession()
            card = dbsession.query(Cards).filter_by(id=card_id).one()
            dbsession.delete(card)
    except:
        success = False
    return {'success': success}

def taxon_type(request):
    taxon_id = request.matchdict['id']

    with transaction.manager:
        dbsession = DBSession()
        p = dbsession.query(Taxon).filter(Taxon.id == taxon_id).one()

        types = {'mammalia': p.is_mammalia(), 'aves': p.is_aves(), 'plantae': p.is_plantae(), 'ara': p.is_ara(),
                 'arthropoda': p.is_arthropoda(), 'moss': p.is_moss(), 'lichenes': p.is_lichenes()}

    return types