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 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_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 _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 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 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 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 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 fill_construct_obj_12_10(args): db_session = DBSession() transaction.manager.begin() # remove all existing ConstructObjects db_session.query(ConstructObject).delete() region_dict = get_regions_from_resource(as_dict=True) district_dict = get_districts_from_resource(as_dict=True) # fill resources = db_session.query(FoclStruct) for focl_struct in resources: co = ConstructObject() co.name = focl_struct.display_name co.resource = focl_struct co.external_id = focl_struct.external_id # try to get region if focl_struct.region: if focl_struct.region in region_dict.keys(): name = region_dict[focl_struct.region] try: region = db_session.query(Region).filter(Region.name == name).one() co.region = region except: print 'Region name not found in regions table! Resource %s, region name = %s' % (focl_struct.id, name) else: print 'Region id not found in layer! Resource %s' % focl_struct.id # try to get district if focl_struct.district: if focl_struct.district in district_dict.keys(): name = district_dict[focl_struct.district] try: dist_query = db_session.query(District).filter(District.name == name) if co.region: dist_query = dist_query.filter(District.region==co.region) dist = dist_query.one() co.district = dist except: print 'District name not found in district table! Resource %s, district name = %s' % (focl_struct.id, name) else: print 'District id not found in layer! Resource %s' % focl_struct.id #try to get project co.project = ModelsUtils.get_project_by_resource(focl_struct) co.persist() transaction.manager.commit() db_session.close()
def get_all_dicts(): dbsession = DBSession() dicts_resources = dbsession.query(LookupTable).all() dicts = {} for dict_res in dicts_resources: dicts[dict_res.keyname] = dict_res.val dbsession.close() return dicts
def link_shp_to_dict(args): #try: db_session = DBSession() transaction.manager.begin() # 1. Get all dicts fds = db_session.query(FederalDistrict).all() regions = db_session.query(Region).all() districts = db_session.query(District).all() # 2. Read shp dicts (for link districts) res_fds = db_session.query(VectorLayer).filter(VectorLayer.keyname == FEDERAL_KEYNAME).one() res_regions = db_session.query(VectorLayer).filter(VectorLayer.keyname == REGIONS_KEYNAME).one() res_district = db_session.query(VectorLayer).filter(VectorLayer.keyname == DISTRICT_KEYNAME).one() fds_shp = list(res_fds.feature_query()()) regions_shp = list(res_regions.feature_query()()) districts_shp = list(res_district.feature_query()()) # 3. Update shp-districts for district_shp in districts_shp: # get parent region_shp region_shp = next(ifilter(lambda f: f.fields['reg_id'] == district_shp.fields['parent_id'], regions_shp)) # get region from dict region = next(ifilter(lambda f: f.name == region_shp.fields['name'], regions)) # get district from dict district = next( ifilter(lambda f: f.region == region and f.name == district_shp.fields['name'], districts) ) # set and save district_shp.fields['dist_id'] = district.id res_district.feature_put(district_shp) # 4. Update shp-regions for region_shp in regions_shp: region = next(ifilter(lambda f: f.name == region_shp.fields['name'], regions)) region_shp.fields['reg_id'] = region.id res_regions.feature_put(region_shp) # 5. Update shp-federal for fd_shp in fds_shp: fd = next(ifilter(lambda f: f.name == fd_shp.fields['name'], fds)) fd_shp.fields['fed_id'] = fd.id res_fds.feature_put(fd_shp) transaction.manager.commit() db_session.close() print ('Shp was linked with dictionaries')
def check_focl_status(cls): db_session = DBSession() transaction.manager.begin() # check and update resources = db_session.query(FoclStruct).filter(FoclStruct.status == _PROJECT_STATUS_FINISHED).all() for focl_struct in resources: focl_struct.status = PROJECT_STATUS_PROJECT print 'Status changed for ' + focl_struct.display_name transaction.manager.commit() db_session.close()
def append_status_dt(cls): db_session = DBSession() transaction.manager.begin() eng = db_session.get_bind() meta_data = Base.metadata real_table = Table(FoclStruct.__table__.name, meta_data, autoload=True, autoload_with=eng) if not FoclStruct.status_upd_dt.key in real_table.columns: StructUpdater.create_column(real_table, FoclStruct.status_upd_dt.key, FoclStruct.status_upd_dt.type) print 'Status DT column added for ' + real_table.name transaction.manager.commit() db_session.close()
def get_layers_by_type(request): if request.user.keyname == 'guest': raise HTTPForbidden() # TODO: optimize this!!! group_res_ids = request.POST.getall('resources') layer_types = request.POST.getall('types') if not group_res_ids or not layer_types: return Response("[]") layer_types.sort(reverse=True) resp_list = [] dbsession = DBSession() # все ВОСЛ и СИТ планы для присланных ид group_resources = dbsession.query(Resource)\ .options(joinedload_all('children.children'))\ .filter(Resource.id.in_(group_res_ids))\ .all() for group_res in group_resources: for child_res in group_res.children: # Если не векторный слой или не имеет кейнейма - не подходит if child_res.identity != VectorLayer.identity or not child_res.keyname: continue lyr_type = _get_layer_type_by_name(layer_types, child_res.keyname) # Тип векторного слоя не подходит по присланному набору if not lyr_type: continue style_resorces = child_res.children # Если нет стилей - не подходит if not style_resorces: continue resp_list.append({ 'vector_id': child_res.id, 'style_id': style_resorces[0].id, 'res_id': group_res.id, 'type': lyr_type, 'res_type': group_res.identity }) dbsession.close() return Response(json.dumps(resp_list))
def update_statuses_05_11(cls): db_session = DBSession() transaction.manager.begin() resources = db_session.query(FoclStruct).filter(FoclStruct.status == PROJECT_STATUS_PROJECT) for focl_struct in resources: # search in all real layers for ch_resource in focl_struct.children: if ch_resource.keyname and ch_resource.keyname.startswith('real_'): #get feat count query = ch_resource.feature_query() result = query() count = result.total_count or 0 if count > 0: focl_struct.status = PROJECT_STATUS_IN_PROGRESS print 'Status changed for ' + focl_struct.display_name break transaction.manager.commit() db_session.close()
def get_regions_tree(request): if request.user.keyname == 'guest': raise HTTPForbidden() parent_region_id = request.params.get('id', None) if parent_region_id is None: raise HTTPBadRequest('Set "id" param!') else: parent_region_id = parent_region_id.replace('reg_', '') is_root_node_requsted = parent_region_id == '#' dbsession = DBSession() is_region = False if is_root_node_requsted: is_region = True children = dbsession.query(Region).order_by(Region.name).all() else: children = dbsession.query(District)\ .filter(District.region_id == parent_region_id)\ .order_by(District.name)\ .all() child_json = [] for child in children: has_children = type(child) is Region is_need_checkbox = False child_json.append({ 'id': ('reg_' if is_region else 'distr_') + str(child.id), 'text': child.name, 'children': has_children, 'has_children': has_children, # 'icon': child_resource.identity, # 'res_type': child_resource.identity, 'a_attr': {'chb': is_need_checkbox} }) dbsession.close() return Response(json.dumps(child_json))
def get_focl_info(request): if request.user.keyname == 'guest': raise HTTPForbidden() res_ids = request.POST.getall('ids') if not res_ids: return Response('[]') now_dt = date.today() dbsession = DBSession() resources = dbsession.query(Resource).filter(Resource.id.in_(res_ids)).all() # get rows from registry focl_info_rows = { row.resource_id: row for row in dbsession.query(ConstructObject).filter(ConstructObject.resource_id.in_(res_ids)).all() } # get rows from reporting reporting_rows = { row.focl_res_id: row for row in dbsession.query(ConstructionStatusReport).filter(ConstructionStatusReport.focl_res_id.in_(res_ids)).all() } resp = [] #dicts regions = get_regions_from_resource(as_dict=True) districts = get_districts_from_resource(as_dict=True) statuses = get_project_statuses(as_dict=True) for res in resources: if res.identity not in (FoclStruct.identity, SituationPlan.identity): continue # try to get data from status report if res.id in reporting_rows.keys() and res.id in focl_info_rows.keys(): report_row = reporting_rows[res.id] focl_info = focl_info_rows[res.id] resp.append( { 'id': res.id, 'display_name': focl_info.name, 'district': districts.get(focl_info.district_id, focl_info.district_id), 'region': regions.get(focl_info.region_id, focl_info.region_id), 'status': statuses.get(res.status, res.status), 'cabling_plan': focl_info.cabling_plan, 'cabling_fact': report_row.cabling_fact, 'cabling_percent': report_row.cabling_percent, 'start_build_time': focl_info.start_build_date.strftime('%d.%m.%Y') if focl_info.start_build_date else '', 'end_build_time': focl_info.end_build_date.strftime('%d.%m.%Y') if focl_info.end_build_date else '', 'start_deliver_time': focl_info.start_deliver_date.strftime('%d.%m.%Y') if focl_info.start_deliver_date else '', 'end_deliver_time': focl_info.end_deliver_date.strftime('%d.%m.%Y') if focl_info.end_deliver_date else '', 'subcontr': focl_info.subcontr_name, 'is_overdue': OverdueStatusCalculator.overdue_status(focl_info.end_build_date, res.status), 'is_month_overdue': OverdueStatusCalculator.month_overdue_status(focl_info.end_build_date, res.status), 'is_focl_delivered': res.status == PROJECT_STATUS_DELIVERED, 'cabling_plan_today': (focl_info.cabling_plan * (float((now_dt - focl_info.start_build_date).days)/((focl_info.end_build_date - focl_info.start_build_date).days))) if (focl_info.end_build_date and focl_info.start_build_date and focl_info.cabling_plan and (focl_info.end_build_date - focl_info.start_build_date).days !=0) else None, 'status_row': res.status, 'editable': request.user.is_administrator or res.has_permission(FoclStructScope.edit_prop, request.user) } ) # else get from registry elif res.id in focl_info_rows.keys(): focl_info = focl_info_rows[res.id] resp.append( { 'id': res.id, 'display_name': focl_info.name, 'district': districts.get(focl_info.district_id, focl_info.district_id), 'region': regions.get(focl_info.region_id, focl_info.region_id), 'status': statuses.get(res.status, res.status), 'cabling_plan': focl_info.cabling_plan, 'cabling_fact': None, 'cabling_percent': None, 'start_build_time': focl_info.start_build_date.strftime('%d.%m.%Y') if focl_info.start_build_date else '', 'end_build_time': focl_info.end_build_date.strftime('%d.%m.%Y') if focl_info.end_build_date else '', 'start_deliver_time': focl_info.start_deliver_date.strftime('%d.%m.%Y') if focl_info.start_deliver_date else '', 'end_deliver_time': focl_info.end_deliver_date.strftime('%d.%m.%Y') if focl_info.end_deliver_date else '', 'subcontr': focl_info.subcontr_name, 'is_overdue': OverdueStatusCalculator.overdue_status(focl_info.end_build_date, res.status), 'is_month_overdue': OverdueStatusCalculator.month_overdue_status(focl_info.end_build_date, res.status), 'is_focl_delivered': res.status == PROJECT_STATUS_DELIVERED, 'cabling_plan_today': (focl_info.cabling_plan * (float((now_dt - focl_info.start_build_date).days)/((focl_info.end_build_date - focl_info.start_build_date).days))) if (focl_info.end_build_date and focl_info.start_build_date and focl_info.cabling_plan and (focl_info.end_build_date - focl_info.start_build_date).days !=0) else None, 'status_row': res.status, 'editable': request.user.is_administrator or res.has_permission(FoclStructScope.edit_prop, request.user) } ) else: # else get from resource resp.append( { 'id': res.id, 'display_name': res.display_name, 'district': districts.get(res.district, res.district), 'region': regions.get(res.region, res.region), 'status': statuses.get(res.status, res.status), 'cabling_plan': None, 'cabling_fact': None, 'cabling_percent': None, 'start_build_time': None, 'end_build_time': None, 'start_deliver_time': None, 'end_deliver_time': None, 'subcontr': None, 'is_overdue': False, 'is_month_overdue': False, 'is_focl_delivered': res.status == PROJECT_STATUS_DELIVERED, 'cabling_plan_today': None, 'status_row': res.status, 'editable': request.user.is_administrator or res.has_permission(FoclStructScope.edit_prop, request.user) } ) dbsession.close() return Response(json.dumps(resp))