def update_actual_lyr_names(args):
    db_session = DBSession()
    transaction.manager.begin()

    # what update
    upd_real_layers = ['real_access_point', 'real_fosc', 'real_optical_cable', 'real_optical_cable_point',
                       'real_optical_cross', 'real_special_transition', 'real_special_transition_point']
    upd_real_lyr_names = {}

    # new names (already in templates!)
    real_layers_template_path = os.path.join(BASE_PATH, 'real_layers_templates/')
    for up_lyr_name in upd_real_layers:
        with codecs.open(os.path.join(real_layers_template_path, up_lyr_name + '.json'), encoding='utf-8') as json_file:
            json_layer_struct = json.load(json_file, encoding='utf-8')
            new_name = json_layer_struct['resource']['display_name']
            upd_real_lyr_names[up_lyr_name] = new_name

    # update now
    resources = db_session.query(VectorLayer).filter(VectorLayer.keyname.like('real_%')).all()

    for vec_layer in resources:
        lyr_name = vec_layer.keyname
        if not lyr_name:
            continue

        for up_lyr_name in upd_real_lyr_names.keys():
            if lyr_name.startswith(up_lyr_name) and not lyr_name.startswith(up_lyr_name + '_point'):  # ugly!
                vec_layer.display_name = upd_real_lyr_names[up_lyr_name]
                print '%s updated' % lyr_name
                break

    transaction.manager.commit()
    db_session.close()

def link_regions_to_fed(args):

    #try:
    db_session = DBSession()
    transaction.manager.begin()

    # 1. Get all federals
    fds = db_session.query(FederalDistrict).all()

    # 2. Get all regions
    regions = db_session.query(Region).all()

    # 3. Read csv file with federals (for get original fed id)
    csv_fds = get_from_csv(path.join(BASE_PATH, 'federal_districts.csv'))

    # 4. Read updated csv file with regions and federal ids
    csv_regions = get_from_csv(path.join(BASE_PATH, 'regions.csv'))

    # 5. Update regions in DB
    for region in regions:
        # get fed_id from csv by region_code
        orig_fed_id = next(ifilter(lambda x: x['region_code'] == str(region.region_code), csv_regions))['fed_id']
        # get original federal from csv
        orig_fed = next(ifilter(lambda x: x['id'] == orig_fed_id, csv_fds))
        # find federal in db by short_name
        db_fed = next(ifilter(lambda fed: fed.short_name == unicode(orig_fed['short_name'], 'utf8'), fds))
        # update db region
        region.federal_dist = db_fed

    transaction.manager.commit()
    db_session.close()

    print ('Region was linked with federal districts')

def add_federal_dist_id_field(args):

    try:
        db_session = DBSession()
        transaction.manager.begin()

        eng = db_session.get_bind()
        meta_data = Base.metadata
        real_table = Table(Region.__table__.name, meta_data, schema=Region.__table_args__['schema'], autoload=True, autoload_with=eng)

        if not Region.federal_dist_id.key in real_table.columns:
            StructUpdater.create_column(real_table, Region.federal_dist_id.key, Region.federal_dist_id.type)

            # it's super cool... SQL Migration!
            eng.execute('''ALTER TABLE compulink.region
                         ADD CONSTRAINT region_federal_dist_id_fkey FOREIGN KEY (federal_dist_id)
                         REFERENCES compulink.federal_district (id) MATCH SIMPLE
                         ON UPDATE NO ACTION ON DELETE NO ACTION;
                        ''')
        transaction.manager.commit()
        db_session.close()

        print ('Federal district id column added for ' + real_table.name)

    except Exception as ex:
        print('Error on adding field to Region table: %s' % (ex.message))

def get_district_extent(request):
    dist_id = request.matchdict["id"]
    if dist_id is None:
        return Response("[]")

    db_session = DBSession()

    try:
        distr_res = (
            db_session.query(Resource)
            .filter(Resource.keyname == Layers.DISTRICTS, Resource.cls == VectorLayer.identity)
            .one()
        )
    except NoResultFound:
        raise exc.HTTPInternalServerError("Публичная карта не настроена! Обратитесь к администратору сервера")

    try:
        query = distr_res.feature_query()
        query.geom()
        query.filter_by(id=int(dist_id))
        for feat in query():
            extent = feat.geom.bounds
            break
    except:
        raise exc.HTTPInternalServerError("Район с заданныи id не найден! Обратитесь к администратору сервера")

    resp = {"extent": extent}

    return Response(json.dumps(resp))

def get_resource_name(res_id):
    db_session = DBSession()
    fs_resource = db_session.query(FoclStruct).filter(FoclStruct.id == res_id).first()
    if fs_resource:
        return fs_resource.display_name
    else:
        return ''

def get_extent_by_resource_id(resource_id):
    session = DBSession()
    resource = session.query(Resource).filter(Resource.id == resource_id).first()

    extent = None
    for res in resource.children:
        if res.identity != VectorLayer.identity or (res.keyname and res.keyname.startswith('real_')):
            continue

        table_info = TableInfo.from_layer(res)
        table_info.setup_metadata(tablename=res._tablename)

        columns = [db.func.st_astext(db.func.st_extent(db.text('geom')).label('box'))]
        query = sql.select(columns=columns, from_obj=table_info.table)
        extent_str = session.connection().scalar(query)

        if extent_str:
            if not extent:
                extent = loads(extent_str).bounds
            else:
                new_extent = loads(extent_str).bounds
                extent = extent_union(extent, new_extent)

    session.close()

    return extent_buff(extent, 2000)

    def run(cls, env):
        return  # disabled by request of Sergey

        db_session = DBSession()
        transaction.manager.begin()

        LogEntry.info('InternalUpdateReactor started!', component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())

        fs_resources = db_session.query(FoclStruct).all()

        for fs in fs_resources:

            # get const obj
            try:
                const_obj = db_session.query(ConstructObject).filter(ConstructObject.resource_id == fs.id).one()
            except:
                LogEntry.info('Error on update const obj with id = ' + str(fs.id),
                              component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())
                continue

            # update from project vector layers
            const_obj.fosc_plan = cls.get_feat_count_for_layer(fs, 'fosc')
            const_obj.cross_plan = cls.get_feat_count_for_layer(fs, 'optical_cross')
            const_obj.spec_trans_plan = cls.get_feat_count_for_layer(fs, 'special_transition')


        db_session.flush()
        LogEntry.info('InternalUpdateReactor finished!', component=COMP_ID, group=InternalUpdateReactor.identity, append_dt=datetime.now())

        transaction.manager.commit()

def _get_years():
    # get min max from db
    db_session = DBSession()
    ucn_proj = db_session.query(Project).filter(Project.keyname==UCN_PROJECT_KEYNAME).one()

    min_start_date = db_session.query(func.min(ConstructObject.start_build_date)).filter(ConstructObject.project == ucn_proj).scalar()
    max_end_date = db_session.query(func.max(ConstructObject.end_build_date)).filter(ConstructObject.project == ucn_proj).scalar()

    # if null, set def values
    min_start_year = min_start_date.year if min_start_date else 2015
    max_end_year = max_end_date.year if max_end_date else 2020

    # check min max
    if min_start_year > max_end_year:
        min_start_year, max_end_year = max_end_year, min_start_year

    # create range
    years = list(range(min_start_year, max_end_year+1))

    # current and selected years
    current_year = date.today().year
    selected_year = current_year if current_year in years else years[0]

    years_view_model = [{'year': x, 'selected': x == selected_year} for x in years]
    return years_view_model

def append_picket_layer_to_wfs(args):
    db_session = DBSession()

    transaction.manager.begin()

    fs_resources = db_session.query(FoclStruct).all()

    # get struct from
    template_path = os.path.join(BASE_PATH, 'layers_templates/')

    vl_name = 'picket'

    for fs in fs_resources:
        # get picket layer
        picket_layer = [res for res in fs.children if res.keyname and vl_name in res.keyname]

        if picket_layer:
            # get wfs service
            wfs_services = [res for res in fs.children if res.cls == WfsServiceResource.identity]

            if wfs_services:
                for wfs_service in wfs_services:
                    already_added = bool([lyr for lyr in wfs_service.layers if lyr.keyname == vl_name])
                    if not already_added:
                        ModelsUtils.append_lyr_to_wfs(wfs_service, picket_layer[0], vl_name)
                        print 'Focl struct %s. Added to wfs service: %s' % (fs.display_name, vl_name)
                    else:
                        print 'Focl struct %s. Layer %s already added' % (fs.display_name, vl_name)
                    #wfs_service.persist()



    transaction.manager.commit()
    db_session.close()

def get_extent_by_objext_num(resource_id, object_type, object_num):
    extent = []

    session = DBSession()
    resource = session.query(Resource).filter(Resource.id == resource_id).first()

    if not resource:
        return extent

    layer_type = 'actual_real_' + object_type
    layer = None
    for res in resource.children:
        if get_layer_type(res) == layer_type:
            layer = res
            break

    if not layer:
        return extent

    query = layer.feature_query()
    query.box()

    query.filter_by(id=object_num)
    query.limit(1)

    obj = None
    for f in query():
        obj = f

    if not obj:
        return extent

    return obj.box.bounds

def editor_delete_geom(request):
    if request.user.keyname == 'guest':
        raise HTTPForbidden()
    if request.method != 'DELETE':
        return error_response(u'Метод не поддерживается! Необходим DELETE')
    try:
        deletes = request.json_body

        db_session = DBSession()
        transaction.manager.begin()

        for del_feat in deletes:
            res = db_session.query(VectorLayer)\
                .options(joinedload_all('parent'))\
                .filter(VectorLayer.id == del_feat['layer'])\
                .first()
            if not res:
                return error_response(u'Редактируемый слой не найден')
            parent_res = res.parent
            if not parent_res:
                return error_response(u'Редактируемый слой некорректный (Слой вне объекта строительства)')
            if not (request.user.is_administrator or parent_res.has_permission(FoclStructScope.edit_data, request.user)):
                return error_response(u'У вас недостаточно прав для редактирования данных')

            if IWritableFeatureLayer.providedBy(res):
                res.feature_delete(del_feat['id'])
            else:
                return error_response(u'Ресурс не поддерживает работу с геометриями')

        transaction.manager.commit()
    except Exception as ex:
        return error_response(ex.message)

    return success_response()

def append_accepted_part_layer(args):
    db_session = DBSession()

    transaction.manager.begin()

    fs_resources = db_session.query(FoclStruct).all()

    # get struct from
    additional_template_path = os.path.join(BASE_PATH, 'additional_layers_templates/')

    vl_name = 'accepted_part'

    for fs in fs_resources:
        fs_children_keys = [res.keyname for res in fs.children if res.keyname]

        # check exists
        found = [res_key for res_key in fs_children_keys if vl_name in res_key]
        if not found:
            try:
                with codecs.open(os.path.join(additional_template_path, vl_name + '.json'), encoding='utf-8') as json_file:
                    json_layer_struct = json.load(json_file, encoding='utf-8')
                    vector_layer = ModelsUtils.create_vector_layer(fs, json_layer_struct, vl_name)
                    mapserver_style = ModelsUtils.set_default_style(vector_layer, vl_name, 'default')
            except Exception, ex:
                print 'Error on append layer %s to %s: %s' % (vl_name, fs.display_name, ex.message)
                return
            print 'Focl struct %s. Added: %s' % (fs.display_name, vl_name)

def _get_layers_styles_items(request, resource_id):
    layers_styles = []
    dbsession = DBSession()

    resource = dbsession.query(Resource).filter(Resource.id == resource_id).first()

    editable_layers_styles = get_editable_layers_styles(request)
    player_layers_styles = get_playable_layers_styles(request)

    for child_resource in resource.children:
        if child_resource.identity != VectorLayer.identity:
            continue
        if len(child_resource.keyname) < (GUID_LENGTH + 1):
            continue
        layer_keyname_without_guid = child_resource.keyname[0:-(GUID_LENGTH + 1)]
        if layer_keyname_without_guid not in editable_layers_styles:
            continue
        layers_styles.append({
            'resource': child_resource,
            'layer_keyname': layer_keyname_without_guid,
            'editor_styles': editable_layers_styles[layer_keyname_without_guid],
            'player_styles': player_layers_styles[layer_keyname_without_guid]
        })

    dbsession.close()

    return layers_styles

def _get_values_for_display(request, permission=FoclStructScope.edit_data):
    resource_id = int(request.GET['resource_id'])
    dbsession = DBSession()
    resource = dbsession.query(Resource).filter(Resource.id == resource_id).first()

    # checks
    if request.user.keyname == 'guest':
        raise HTTPForbidden()

    if not(request.user.is_administrator or resource.has_permission(permission, request.user)):
        raise HTTPForbidden()

    extent3857 = get_extent_by_resource_id(resource_id)
    extent4326 = _extent_3857_to_4326(extent3857)

    focl_layers = get_focl_layers_list()
    sit_plan_layers_type = get_sit_plan_layers_list()

    layers_styles = _get_layers_styles_items(request, resource_id)
    editable_layers_view_model = _create_editable_layers_view_model(layers_styles)
    playable_layers_view_model = _create_playable_layers_view_model(layers_styles)

    values = dict(
        resource_display_name=resource.display_name,
        show_header=True,
        focl_layers_type=focl_layers['focl'],
        objects_layers_type=focl_layers['objects'],
        real_layers_type=focl_layers['real'],
        sit_plan_layers_type=sit_plan_layers_type,
        extent=extent4326,
        editable_layers_info=editable_layers_view_model,
        playable_layers_info=playable_layers_view_model
    )

    return values

    def update_all_styles(cls, new_styles):
        dbsession = DBSession()

        ms_styles_resources = dbsession.query(MapserverStyle).options(joinedload_all('parent')).all()

        new_styles_keys = new_styles.keys()
        new_styles_keys.sort(reverse=True)

        for ms_style_res in ms_styles_resources:
            vector_layer_key = ms_style_res.parent.keyname
            if not vector_layer_key:
                print "!!!! %s was not updated! No parent keyname or keyname is invalid!" % (ms_style_res.display_name)
                continue

            v_vector_layer_key = '_'.join(vector_layer_key.rsplit('_')[:-1])

            updated = False
            for style_name in new_styles_keys:
                if style_name == v_vector_layer_key:
                    ms_style_res.xml = new_styles[style_name]
                    ms_style_res.persist()
                    print "!!!! %s was updated!" % vector_layer_key
                    updated = True
                    break
            if not updated:
                print "%s for %s was not updated! Style not found!" % (ms_style_res.display_name, vector_layer_key)
        transaction.manager.commit()
        dbsession.close()

def _get_co(user, project_filter, dist_id=None, reg_id=None):
    # --- attribute parts
    dbsession = DBSession()

    # filter by rights
    allowed_res_ids = None
    if not user.is_administrator:
        allowed_res_ids = get_user_writable_focls(user)
    # filter by struct
    project_res_ids = None
    if project_filter and project_filter != 'root':
        project_res_ids = get_project_focls(project_filter)

    co_query = dbsession.query(ConstructObject.resource_id)
    if allowed_res_ids:
        co_query = co_query.filter(ConstructObject.resource_id.in_(allowed_res_ids))
    if project_res_ids:
        co_query = co_query.filter(ConstructObject.resource_id.in_(project_res_ids))
    if dist_id is not None:
        co_query = co_query.filter(ConstructObject.district_id==dist_id)
    if reg_id is not None:
        co_query = co_query.filter(ConstructObject.region_id==reg_id)

    result = co_query.all()
    result = list(f[0] for f in result)

    return Response(
        json.dumps(result, ensure_ascii=False, cls=ComplexEncoder),
        content_type=b'application/json',
        charset='utf-8'
    )

def get_child_resx_by_parent(request):
    if request.user.keyname == 'guest':
        raise HTTPForbidden()

    parent_resource_id = request.params.get('id', None)
    if parent_resource_id is None:
        raise HTTPBadRequest('Set "id" param!')
    else:
        parent_resource_id = parent_resource_id.replace('res_', '')
    is_root_node_requsted = parent_resource_id == '#'

    type_filter = request.params.get('type', None)

    dbsession = DBSession()
    if is_root_node_requsted:
        parent_resource_id = dbsession.query(Resource).filter(Resource.parent==None).all()[0].id

    parent_resource = dbsession.query(Resource).get(parent_resource_id)
    children = parent_resource.children

    suitable_types = [
        ResourceGroup.identity,
        FoclProject.identity,
        ]
    if type_filter == 'vols' or not type_filter:
        suitable_types.append(FoclStruct.identity)
    if type_filter == 'sit' or not type_filter:
        suitable_types.append(SituationPlan.identity)

    if not request.user.is_administrator:
        allowed_res_list = _get_user_resources_tree(request.user)

    child_resources_json = []
    for child_resource in children:
        if child_resource.identity in suitable_types:
            # remove system folders
            if child_resource.identity == ResourceGroup.identity and child_resource.keyname == DICTIONARY_GROUP_KEYNAME:
                continue
            # check permissions
            if not request.user.is_administrator and child_resource.id not in allowed_res_list:
                continue
            is_need_checkbox = child_resource.identity in (FoclProject.identity, SituationPlan.identity, FoclStruct.identity)
            has_children = child_resource.identity in (ResourceGroup.identity, FoclProject.identity)
            child_resources_json.append({
                'id': 'res_' + str(child_resource.id),
                'text': child_resource.display_name,
                'children': has_children,
                'has_children': has_children,
                'icon': child_resource.identity,
                'res_type': child_resource.identity,
                'a_attr': {'chb': is_need_checkbox}
            })

            if not is_need_checkbox:
                child_resources_json[-1]['state'] = {'disabled': True}

    dbsession.close()

    return Response(json.dumps(child_resources_json))

    def create_column(cls, table_object, field_name, field_type):
        db_session = DBSession()
        engine = db_session.get_bind()

        column = Column(field_name, field_type)
        column_name = column.compile(dialect=engine.dialect)
        column_type = column.type.compile(engine.dialect)
        engine.execute('ALTER TABLE "%s"."%s" ADD COLUMN %s %s' % (table_object.schema or 'public', table_object.name, column_name, column_type))

    def __drop_column(table_uid, field_uid):
        # еще не юзал!
        db_session = DBSession()
        engine = db_session.get_bind()

        column = Column('fld_%s' % field_uid)
        column_name = column.compile(dialect=engine.dialect)
        engine.execute('ALTER TABLE "vector_layer"."layer_%s" DROP COLUMN %s' % (table_uid, column_name))

    def __change_column_datatype(table_uid, field_uid, new_column_type):
        db_session = DBSession()
        engine = db_session.get_bind()

        column = Column('fld_%s' % field_uid, new_column_type)
        column_name = column.compile(dialect=engine.dialect)
        column_type = column.type.compile(engine.dialect)
        engine.execute('ALTER TABLE "vector_layer"."layer_%s" ALTER COLUMN %s TYPE %s' % (table_uid, column_name, column_type))