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))
