def test_orm_join(self):
from sqlalchemy.orm.util import join
j = join(User, Address, User.addresses)
j2 = serializer.loads(serializer.dumps(j, -1), users.metadata)
assert j2.left is j.left
assert j2.right is j.right
assert j2._target_adapter._next
def org_roles(self, org_id):
SchemaModel = get_model('rbacscheme')
#----check if the organization is a global organization
Org = self.model.get(org_id)
RoleModel = get_model('role')
RPRModel = get_model('Role_Perm_Rel')
if Org.rbacscheme:
query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).where(self.model.c.id == org_id)
OrgObj = do_(query).fetchone()
query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.rbacscheme)
else:
#----global organization
query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.id == SchemaModel.c.gorg)).where(self.model.c.id == org_id)
OrgObj = do_(query).fetchone()
query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.schema_id)
#----need to filter the rols which belone to this schema
roleList = do_(query)
roleDict = {}
for s in roleList:
roleDict[s.id] = s.name
return {'orgid':org_id, 'orgname':OrgObj.name, 'schemaname':OrgObj.schema_name, 'schema':OrgObj.rbacscheme , 'schema_id':OrgObj.schema_id, 'roleDict':roleDict}
def search_product(page=1):
name = request.args.get("name")
price = request.args.get("price")
company = request.args.get("company")
products = Product.query
if name:
products = products.filter(Product.name.like("%" + name + "%"))
if price:
products = products.filter(Product.price <= price)
if company:
products = products.select_from(join(Product, Company)).filter(
Company.name.like("%" + company + "%")
)
return render_template("catalog/products.html", products=products.paginate(page, 10))
def get_from_database():
session = DBSession()
query = session.query(cls)
if device is not None:
query = query.select_from(join(File, Device)). \
filter(Device.name == device)
if type is not None:
query = query.filter(cls.type == type)
# Limit the query and order it
query = query.order_by(desc(cls.date_created))[:20]
return query
def product_search(page=1):
name = request.args.get('name')
price = request.args.get('price')
company = request.args.get('company')
category = request.args.get('category')
products = Product.query
if name:
products = products.filter(Product.name.like('%' + name + '%'))
if price:
products = products.filter(Product.price == price)
if company:
products = products.filter(Product.company.like('%' + company + '%'))
if category:
products = products.select_from(join(Product, Category)).filter(Category.name.like('%' + category + '%'))
return render_template('products.html', products=products.paginate(page,10))
def product_search(page=1):
name = request.args.get('name')
price = request.args.get('price')
company = request.args.get('company')
category = request.args.get('category')
products = Product.query
if name:
products = products.filter(Product.name.like('%' + name + '%'))
if price:
products = products.filter(Product.price == price)
if company:
products = products.filter(Product.company.like('%' + company + '%'))
if category:
products = products.select_from(join(Product, Category)).filter(Category.name.like('%' + category + '%'))
return render_template('products.html', products=products.paginate(page, 10))
def get_from_database():
session = DBSession()
query = session.query(cls)
if device is not None:
query = query.select_from(join(File, Device)). \
filter(Device.name == device)
if type is not None:
query = query.filter(cls.type == type)
# Limit the query and order it
query = query.order_by(desc(cls.date_created))[:20]
return query
def _create_eager_join(self, context, entity, path, adapter, parentmapper, clauses):
if parentmapper is None:
localparent = entity.mapper
else:
localparent = parentmapper
# whether or not the Query will wrap the selectable in a subquery,
# and then attach eager load joins to that (i.e., in the case of
# LIMIT/OFFSET etc.)
should_nest_selectable = context.multi_row_eager_loaders and context.query._should_nest_selectable
entity_key = None
if entity not in context.eager_joins and not should_nest_selectable and context.from_clause:
index, clause = sql_util.find_join_source(context.from_clause, entity.selectable)
if clause is not None:
# join to an existing FROM clause on the query.
# key it to its list index in the eager_joins dict.
# Query._compile_context will adapt as needed and
# append to the FROM clause of the select().
entity_key, default_towrap = index, clause
if entity_key is None:
entity_key, default_towrap = entity, entity.selectable
towrap = context.eager_joins.setdefault(entity_key, default_towrap)
join_to_left = False
if adapter:
if getattr(adapter, "aliased_class", None):
onclause = getattr(adapter.aliased_class, self.key, self.parent_property)
else:
onclause = getattr(
mapperutil.AliasedClass(self.parent, adapter.selectable), self.key, self.parent_property
)
if onclause is self.parent_property:
# TODO: this is a temporary hack to
# account for polymorphic eager loads where
# the eagerload is referencing via of_type().
join_to_left = True
else:
onclause = self.parent_property
innerjoin = context.attributes.get(("eager_join_type", path), self.parent_property.innerjoin)
context.eager_joins[entity_key] = eagerjoin = mapperutil.join(
towrap, clauses.aliased_class, onclause, join_to_left=join_to_left, isouter=not innerjoin
)
# send a hint to the Query as to where it may "splice" this join
eagerjoin.stop_on = entity.selectable
if self.parent_property.secondary is None and not parentmapper:
# for parentclause that is the non-eager end of the join,
# ensure all the parent cols in the primaryjoin are actually
# in the
# columns clause (i.e. are not deferred), so that aliasing applied
# by the Query propagates those columns outward.
# This has the effect
# of "undefering" those columns.
for col in sql_util.find_columns(self.parent_property.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
if adapter:
col = adapter.columns[col]
context.primary_columns.append(col)
if self.parent_property.order_by:
context.eager_order_by += eagerjoin._target_adapter.copy_and_process(
util.to_list(self.parent_property.order_by)
)
def update_profile(id):
if 'username' not in session:
return redirect(url_for('catalog.login'))
person = Person.query.get_or_404(id)
form = UpdateForm(
request.form,
name=person.name,
surname=person.surname,
nickname=person.nickname,
pcode=person.pcode,
contract_nr=person.contract_nr,
birthdate=person.birthdate,
my_phone=person.my_phone,
email=person.email,
other_phone=person.other_phone,
home_address=person.home_address,
height=person.height,
foot_size=person.foot_size,
cloth_size=person.cloth_size,
voice=person.voice,
contact_lenses=person.contact_lenses,
be_dressed=person.be_dressed,
mother_phone_code=person.mother_phone_code,
mother_phone=person.mother_phone,
mother_name=person.mother_name,
father_phone_code=person.father_phone_code,
father_phone=person.father_phone,
father_name=person.father_name,
experience=person.experience,
current_occupation = person.current_occupation,
workplace = person.workplace,
play_age_from = person.play_age_from,
play_age_to = person.play_age_to
)
if person.species:
form.species.data=person.species
if person.speciality:
form.speciality.data=person.speciality
# Get all possible values(choices) from Classifier
choices = list()
city_box = Classifier.query.filter_by(category='city').all()
for city_box in city_box:
if city_box:
choices.append((city_box.tag_lv,city_box.tag_lv))
form.city.choices = choices
choices = []
haircolor = Classifier.query.filter_by(category='haircolor').all()
for haircolor in haircolor:
if haircolor:
choices.append((haircolor.tag_lv,haircolor.tag_lv))
form.haircolor.choices = choices
choices = []
eyecolor = Classifier.query.filter_by(category='eyecolor').all()
for eyecolor in eyecolor:
if eyecolor:
choices.append((eyecolor.tag_lv,eyecolor.tag_lv))
form.eyecolor.choices = choices
choices = []
voice = Classifier.query.filter_by(category='voice').all()
for voice in voice:
if voice:
choices.append((voice.tag_lv,voice.tag_lv))
form.voice.choices = choices
choices = []
co = Classifier.query.filter_by(category='current_occupation').all()
for co in co:
if co:
choices.append((co.tag_lv,co.tag_lv))
form.current_occupation.choices = choices
choices = []
for size in range(35,50,1): # filled clothe size range 35 to 49
choices.append((str(size),(str(size))))
form.foot_size.choices = choices
choices = []
for size in range(32,69,2): # filled clothe size range 32 to 68
choices.append((str(size),(str(size))))
form.cloth_size.choices = choices
# Get all assigned Skills for person and add them as selected in form.
classifiers = Classifier.query.select_from(join(Classifier, Skill)).filter(Skill.person_id == id)
skill_box = {}
for item in classifiers:
#flash('skill [%s] cat [%s] ' % (item.tag_lv, item.category), 'info')
if skill_box.get(item.category) is None:
skill_box[item.category] = [(item.tag_lv,item.tag_lv)]
else:
skill_box[item.category].append((item.tag_lv,item.tag_lv))
#flash('danceskilldanceskill [%s]' % skill_box.get('danceskill'), 'info')
if skill_box.get('city'):
for skill in skill_box.get('city'):
form.city.data= skill[0]
if skill_box.get('haircolor'):
for skill in skill_box.get('haircolor'):
form.haircolor.data= skill[0]
if skill_box.get('eyecolor'):
for skill in skill_box.get('eyecolor'):
form.eyecolor.data= skill[0]
if skill_box.get('subspeciality'):
form.subspeciality.choices = skill_box.get('subspeciality')
if skill_box.get('danceskill'):
form.danceskill.choices = skill_box.get('danceskill')
if skill_box.get('singskill'):
form.singskill.choices = skill_box.get('singskill')
if skill_box.get('musicskill'):
form.musicskill.choices = skill_box.get('musicskill')
if skill_box.get('sportskill'):
form.sportskill.choices = skill_box.get('sportskill')
if skill_box.get('swimskill'):
form.swimskill.choices = skill_box.get('swimskill')
if skill_box.get('driveskill'):
form.driveskill.choices = skill_box.get('driveskill')
if skill_box.get('languageskill'):
form.languageskill.choices = skill_box.get('languageskill')
if skill_box.get('otherskill'):
form.otherskill.choices = skill_box.get('otherskill')
if skill_box.get('want_participate'):
form.want_participate.choices = skill_box.get('want_participate')
if skill_box.get('dont_want_participate'):
form.dont_want_participate.choices = skill_box.get('dont_want_participate')
if skill_box.get('interested_in'):
form.interested_in.choices = skill_box.get('interested_in')
if skill_box.get('tattoo'):
form.tattoo.choices = skill_box.get('tattoo')
if skill_box.get('piercing'):
form.piercing.choices = skill_box.get('piercing')
if skill_box.get('afraidof'):
form.afraidof.choices = skill_box.get('afraidof')
if skill_box.get('religion'):
form.religion.choices = skill_box.get('religion')
if skill_box.get('educational_institution'):
form.educational_institution.choices = skill_box.get('educational_institution')
if skill_box.get('learned_profession'):
form.learned_profession.choices = skill_box.get('learned_profession')
if skill_box.get('degree'):
form.degree.choices = skill_box.get('degree')
if skill_box.get('current_occupation'):
form.current_occupation.choices = skill_box.get('current_occupation')
if skill_box.get('voice'):
form.current_occupation.choices = skill_box.get('voice')
if skill_box.get('cb_tags'):
form.cb_tags.choices = skill_box.get('cb_tags')
if skill_box.get('family_notes'):
form.family_notes.choices = skill_box.get('family_notes')
photos = Document.query.filter_by(person_id=id, type='photo').paginate(1,100,error_out=False)
videos = Document.query.filter_by(person_id=id, type='video').paginate(1,100,error_out=False)
#for photo in photos:
# flash("photo : [%s]" % photo.name, 'info')
if form.validate_on_submit():
name = form.name.data
surname = form.surname.data
nickname = form.nickname.data
pcode = form.pcode.data
contract_nr = form.contract_nr.data
birthdate = form.birthdate.data
my_phone = form.my_phone.data
email = form.email.data
other_phone = form.other_phone.data
home_address = form.home_address.data
height = form.height.data
foot_size = request.form['foot_size']
cloth_size = request.form['cloth_size']
voice = request.form['voice']
contact_lenses = form.contact_lenses.data
be_dressed = form.be_dressed.data
# field is set from request.form['species'], because form.species.data is alredy set to (old)value from db
species = request.form['species']
mother_phone_code = form.mother_phone_code.data
mother_phone = form.mother_phone.data
mother_name = form.mother_name.data
father_phone_code = form.father_phone_code.data
father_phone = form.father_phone.data
father_name = form.father_name.data
speciality = request.form['speciality']
experience = form.experience.data
city = request.form['city']
haircolor = request.form['haircolor']
eyecolor = request.form['eyecolor']
current_occupation = request.form['current_occupation']
workplace = form.workplace.data
play_age_from = form.play_age_from.data
play_age_to = form.play_age_to.data
Person.query.filter_by(id=id).update({
'modified': datetime.datetime.now(pytz.timezone("Europe/Riga")),
'name': name,
'surname': surname,
'nickname': nickname,
'pcode': pcode,
'contract_nr': contract_nr,
'birthdate': birthdate,
'my_phone': my_phone,
'email': email,
'other_phone': other_phone,
'home_address': home_address,
'height': height,
'foot_size': foot_size,
'cloth_size': cloth_size,
'voice': voice,
'contact_lenses': contact_lenses,
'be_dressed': be_dressed,
'species': species,
'mother_phone_code': mother_phone_code,
'mother_phone': mother_phone,
'mother_name': mother_name,
'father_phone_code': father_phone_code,
'father_phone': father_phone,
'father_name': father_name,
'speciality': speciality,
'experience': experience,
'current_occupation': current_occupation,
'workplace': workplace,
'play_age_from': play_age_from,
'play_age_to': play_age_to
})
skills = list()
if city:
skills.append(['city', city])
if haircolor:
skills.append(['haircolor', haircolor])
if eyecolor:
skills.append(['eyecolor', eyecolor])
for subspeciality in form.subspeciality.data:
skills.append(['subspeciality', subspeciality])
for danceskill in form.danceskill.data:
skills.append(['danceskill', danceskill])
for singskill in form.singskill.data:
skills.append(['singskill', singskill])
for musicskill in form.musicskill.data:
skills.append(['musicskill', musicskill])
for sportskill in form.sportskill.data:
skills.append(['sportskill', sportskill])
for swimskill in form.swimskill.data:
skills.append(['swimskill', swimskill])
for otherskill in form.otherskill.data:
skills.append(['otherskill', otherskill])
for driveskill in form.driveskill.data:
skills.append(['driveskill', driveskill])
for languageskill in form.languageskill.data:
skills.append(['languageskill', languageskill])
for want_participate in form.want_participate.data:
skills.append(['want_participate', want_participate])
for dont_want_participate in form.dont_want_participate.data:
skills.append(['dont_want_participate', dont_want_participate])
for interested_in in form.interested_in.data:
skills.append(['interested_in', interested_in])
for tattoo in form.tattoo.data:
skills.append(['tattoo', tattoo])
for piercing in form.piercing.data:
skills.append(['piercing', piercing])
for afraidof in form.afraidof.data:
skills.append(['afraidof', afraidof])
for religion in form.religion.data:
skills.append(['religion', religion])
for educational_institution in form.educational_institution.data:
skills.append(['educational_institution', educational_institution])
for learned_profession in form.learned_profession.data:
skills.append(['learned_profession', learned_profession])
for degree in form.degree.data:
skills.append(['degree', degree])
for cb_tags in form.cb_tags.data:
skills.append(['cb_tags', cb_tags])
for family_notes in form.family_notes.data:
skills.append(['family_notes', family_notes])
# Delete outdated skills
Skill.query.filter_by(person_id=id).delete()
for skill in skills:
#flash('Skills [%s] [%s]' % (skill[0], skill[1]), 'success')
item = Classifier.query.filter_by(category=skill[0], tag_lv = skill[1].capitalize()).first()
if item is None: # add new entry in Classifier
item = Classifier(category=skill[0], tag_lv=skill[1].capitalize())
db.session.add(item)
add_skill = Skill(person=person, classifier=item)
db.session.add(add_skill)
db.session.commit()
file_mask = helpers.make_file_mask(species, birthdate, speciality, height)
files = request.files.getlist('images[]')
for file in files:
#flash('file: [%s]' % file.filename, 'success')
filename = ''
if file and allowed_file(file.filename):
filename = str(person.id) + "_" + file_mask + secure_filename(file.filename)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
add_document = Document(datetime.datetime.now(pytz.timezone("Europe/Riga")), person.id, 'photo', filename)
db.session.add(add_document)
#helpers.file_upload('photo', 'image1', person.id)
#helpers.file_upload('photo', 'image2', person.id)
#helpers.file_upload('photo', 'image3', person.id)
#helpers.file_upload('photo', 'image4', person.id)
#helpers.file_upload('photo', 'image5', person.id)
helpers.file_upload('audio', 'audio', person.id)
helpers.file_upload('video', 'video', person.id)
profile_image = request.files['profile_image']
cv = request.files['cv']
filename = ''
if profile_image and helpers.allowed_file(profile_image.filename):
#flash('profile_image: [%s]' % profile_image, 'success')
filename, file_extension = os.path.splitext(secure_filename(profile_image.filename))
filename = str(person.id) + "_profile" + file_extension
profile_image.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
Person.query.filter_by(id=person.id).update({
'profile_image': filename
})
if cv and helpers.allowed_file(cv.filename):
#flash('profile_image: [%s]' % profile_image, 'success')
filename, file_extension = os.path.splitext(secure_filename(cv.filename))
filename = str(person.id) + "_cv" + file_extension
cv.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
Person.query.filter_by(id=person.id).update({
'cv': filename
})
db.session.commit()
flash('Profile updated.', 'info')
return redirect(url_for('catalog.profiles'))
if form.errors:
flash(form.errors, 'danger')
return render_template('profile_update.html', form=form, person=person, photos=photos, videos=videos )
def _create_eager_join(self, context, entity, path, adapter, parentmapper,
clauses, innerjoin):
if parentmapper is None:
localparent = entity.mapper
else:
localparent = parentmapper
# whether or not the Query will wrap the selectable in a subquery,
# and then attach eager load joins to that (i.e., in the case of
# LIMIT/OFFSET etc.)
should_nest_selectable = context.multi_row_eager_loaders and \
context.query._should_nest_selectable
entity_key = None
if entity not in context.eager_joins and \
not should_nest_selectable and \
context.from_clause:
index, clause = \
sql_util.find_join_source(
context.from_clause, entity.selectable)
if clause is not None:
# join to an existing FROM clause on the query.
# key it to its list index in the eager_joins dict.
# Query._compile_context will adapt as needed and
# append to the FROM clause of the select().
entity_key, default_towrap = index, clause
if entity_key is None:
entity_key, default_towrap = entity, entity.selectable
towrap = context.eager_joins.setdefault(entity_key, default_towrap)
join_to_left = False
if adapter:
if getattr(adapter, 'aliased_class', None):
onclause = getattr(adapter.aliased_class, self.key,
self.parent_property)
else:
onclause = getattr(
mapperutil.AliasedClass(self.parent, adapter.selectable),
self.key, self.parent_property)
if onclause is self.parent_property:
# TODO: this is a temporary hack to
# account for polymorphic eager loads where
# the eagerload is referencing via of_type().
join_to_left = True
else:
onclause = self.parent_property
context.eager_joins[entity_key] = eagerjoin = \
mapperutil.join(
towrap,
clauses.aliased_class,
onclause,
join_to_left=join_to_left,
isouter=not innerjoin
)
# send a hint to the Query as to where it may "splice" this join
eagerjoin.stop_on = entity.selectable
if self.parent_property.secondary is None and \
not parentmapper:
# for parentclause that is the non-eager end of the join,
# ensure all the parent cols in the primaryjoin are actually
# in the
# columns clause (i.e. are not deferred), so that aliasing applied
# by the Query propagates those columns outward.
# This has the effect
# of "undefering" those columns.
for col in sql_util.find_columns(self.parent_property.primaryjoin):
if localparent.mapped_table.c.contains_column(col):
if adapter:
col = adapter.columns[col]
context.primary_columns.append(col)
if self.parent_property.order_by:
context.eager_order_by += \
eagerjoin._target_adapter.\
copy_and_process(
util.to_list(
self.parent_property.order_by
)
)
def api_org_roles_load(self, org_id):
page = int(request.GET.get('iDisplayStart') or 0)
psize = int(request.GET.get('iDisplayLength') or 10)
RoleModel = get_model('role')
UserModel = get_model('user')
OrgRoleModel = get_model('orgrole')
UserGroupModel = get_model('usergroup')
totalRecords = OrgRoleModel.filter(OrgRoleModel.c.organization == org_id).count()
query = select([RoleModel.c.id.label('role_id'), RoleModel.c.name.label('role_name'), OrgRoleModel.c.organization, OrgRoleModel.c.id.label('orgrole_id')]).select_from(join(RoleModel.table, OrgRoleModel.table, RoleModel.c.id == OrgRoleModel.c.role)).where(OrgRoleModel.c.organization == org_id).offset(page).limit(psize)
result = do_(query)
ret = {}
def fetch_users(orgrole_id):
ret = []
userList = UserModel.filter(OrgRoleModel.users.join_filter(OrgRoleModel.c.id == orgrole_id))
for s in userList:
ret.append({s.id:s.username})
return ret
def fetch_usergroups(orgrole_id):
ret = []
userList = UserGroupModel.filter(OrgRoleModel.usergroups.join_filter(OrgRoleModel.c.id == orgrole_id))
for s in userList:
ret.append({s.id:s.name})
return ret
ret['aaData'] = [{'role_id':s['role_id'], 'role_name':s['role_name'], 'users':fetch_users(s.orgrole_id), 'usergroups':fetch_usergroups(s.orgrole_id), 'orgrole_id':s['orgrole_id']} for s in result]
ret['iTotalRecords'] = totalRecords
ret['iTotalDisplayRecords'] = totalRecords
return json(ret)
def api_list(self):
page = int(request.GET.get('iDisplayStart') or 0)
psize = int(request.GET.get('iDisplayLength') or 10)
search = request.GET.get('sSearch').strip()
SchemaModel = get_model('rbacscheme')
schemaObj = None
if search:
schemaObj = SchemaModel.filter(SchemaModel.c.name == search).one()
if schemaObj:
totalRecords = self.model.filter(self.model.c.rbacscheme == schemaObj.id).count()
query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).where(SchemaModel.c.id == schemaObj.id).offset((page)).limit(psize)
else:
totalRecords = self.model.filter(self.model.c.rbacscheme != None).count()
query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).offset((page)).limit(psize)
orgList = do_(query)
ret = {}
ret['aaData'] = [{'id':s['id'], 'name':s['name'], 'schema':s['schema_name'], 'schema_id':s['rbacscheme']} for s in orgList]
ret['iTotalRecords'] = totalRecords
ret['iTotalDisplayRecords'] = totalRecords
return json(ret)
def api_schema_perm_load(self, schema_id):
page = int(request.GET.get('iDisplayStart') or 0)
psize = int(request.GET.get('iDisplayLength') or 10)
RoleModel = get_model('role')
PermModel = get_model('Permission')
RPRModel = get_model('Role_Perm_Rel')
perm_rpr = select([PermModel.c.id.label('perm_id'), PermModel.c.name.label('perm_name'), RPRModel.c.scheme, RPRModel.c.role ]).select_from(join(PermModel.table, RPRModel.table, PermModel.c.id == RPRModel.c.permission)).alias()
query = select([perm_rpr.c.perm_id, perm_rpr.c.perm_name, perm_rpr.c.scheme, RoleModel.c.id.label('role_id'), RoleModel.c.name.label('role_name') ]).select_from(join(perm_rpr, RoleModel.table, RoleModel.c.id == perm_rpr.c.role)).where(perm_rpr.c.scheme == schema_id) # .offset((page)).limit(psize)
result = do_(query)
ret = {}
#----prepare dataset and do the pagnization in memery
dataset = {}
permDict = {}
for s in result:
if s['perm_name'] in dataset.keys():
t = dataset.get(s['perm_name'])
t.append({s['role_id']:s['role_name']})
else:
dataset[s['perm_name']] = [{s['role_id']:s['role_name']}]
permDict[s['perm_name']] = s['perm_id']
def fetch_role_id(rols):
ret = []
for s in rols:
for k in s:
ret.append(k)
return ret
#----pagination in memery
totalRecords = len(dataset)
dataset = OrderedDict(sorted(dataset.items(), key=lambda t: t[0]))
from itertools import islice
dataset = OrderedDict(islice(dataset.items(), page, psize))
ret['aaData'] = [{'perm':s, 'roles':dataset[s], 'role_id_list':fetch_role_id(dataset[s]), 'perm_id':permDict[s]} for s in dataset]
ret['iTotalRecords'] = totalRecords
ret['iTotalDisplayRecords'] = totalRecords
return json(ret)
def detail(self, schema_id):
OrgModel = get_model('rbacorg')
RoleModel = get_model('role')
PermModel = get_model('Permission')
RPRModel = get_model('Role_Perm_Rel')
query = select([self.model.c.id, self.model.c.name, self.model.c.gorg, self.model.c.description, OrgModel.c.name.label('org_name')]).select_from(join(self.model.table, OrgModel.table, self.model.c.gorg == OrgModel.c.id)).where(self.model.c.id == schema_id)
schemaList = do_(query)
return {'schemaid':schema_id, 'schema_obj':schemaList.fetchone()}